public static void test()
{
// Get the relations for this table
DataTable relations = MappingEngine.getRelationsForDataSource(dataSources["PatientMealChoices"].getID());
Program.DisplayDataTable(relations);
}
static void testSystem()
{
// Create a new SQLSERVER wrapper
WrapperSQLSERVER SuperViewTest = new WrapperSQLSERVER("PatientMealChoices", "Data Source=(local);Initial Catalog=PatientMealChoices;User id=sa;Password=Pa55w0rd;");
// Attempt a connection to the database
if (SuperViewTest.connect())
{
Console.WriteLine("Connection successful \n");
SuperViewTest.disconnect();
}
// QUERY TEST
Console.WriteLine("QUERY TEST");
string query = "SELECT * FROM Patients;";
Console.WriteLine("Query: " + query);
// Query the database
DataTable results = SuperViewTest.query(query);
// Output the results of the query
DisplayDataTable(results);
// END QUERY TEST
Console.WriteLine("");
// GET SCHEMA TEST
Console.WriteLine("GET SCHEMA TEST");
IList <string> tables = SuperViewTest.getTables();
// Display the list
DisplayIList(tables);
// END GET SCHEMA TEST
Console.WriteLine("");
// GET COLUMNS TEST
Console.WriteLine("GET COLUMNS TEST");
Dictionary <string, Dictionary <string, string> > columns = SuperViewTest.getColumns("Test");
// Display the columns
foreach (KeyValuePair <string, Dictionary <string, string> > column in columns)
{
Console.WriteLine("Column Name: " + column.Key.ToString() + " Data Type: " + column.Value["data_type"].ToString());
}
//DisplayDataTable(columns);
// END GET COLUMNS TEST
Console.WriteLine("");
//MAPPING ENGINE TEST
Console.WriteLine("MAPPING ENGINE TEST");
results = MappingEngine.queryWrapper(SuperViewTest, "SELECT * FROM Patients WHERE NHSNumber = '1111111111'");
DisplayDataTable(results);
//END MAPPING ENGINE TEST
}
// Gets the data source of a mapping
public static Wrapper getMappingDataSource(string targetField)
{
//Find the mapping, get the ID of the first source data source
DataTable mapping = MappingEngine.getMappingSource(targetField);
int sourceDataSource = (int)mapping.Rows[0]["SourceDataSource"];
return(getDataSourceWrapper(sourceDataSource));
}
// Function to use SuperView once initialised
public static void useSuperView()
{
Console.WriteLine("");
// Get all of the mappings we have produced
DataTable mappings = MappingEngine.getMappings();
// Display the mappings
foreach (DataRow mapping in mappings.Rows)
{
string dataSourceName = Utilities.getDataSourceName((int)mapping["sourceDataSource"]);
string tableName = Utilities.getTableName((int)mapping["sourceTable"]);
Console.WriteLine(mapping["targetName"].ToString() + " (Data Source: " + dataSourceName + "| Table: " + tableName + ")");
}
Console.WriteLine("");
Console.Write("Enter query: ");
Dictionary <Wrapper, DataTable> results = QueryEngine.query(Console.ReadLine().ToString());
DataTable output = QueryEngine.joinData(results);
}
public static DataTable joinData(Dictionary <Wrapper, DataTable> results)
{
// Get all of the relations needed to join the data we have
//DataTable relations = MappingEngine.getRelationsForTable(
// Empty data table to hold our results
DataTable resultsTable = new DataTable();
// Loop
foreach (KeyValuePair <Wrapper, DataTable> data in results)
{
// Get the relations for this table
DataTable relations = MappingEngine.getRelationsForDataSource(data.Key.getID());
// Variable to make our table easier to access
DataTable table = data.Value;
resultsTable = joinDataTables(resultsTable, table, "NHSNumber", "NHSNumber");
}
Program.DisplayDataTable(resultsTable);
return(new DataTable());
}
//Function to display all the mappings
public static void showMappings()
{
//Loop through all of the wrappers and output their names
Console.WriteLine("Wrappers...");
foreach (KeyValuePair <string, Wrapper> dataSource in dataSources)
{
Console.WriteLine(dataSource.Key.ToString());
}
Console.WriteLine("");
Console.WriteLine("Mappings...");
// Get all of the mappings we have produced
DataTable mappings = MappingEngine.getMappings();
// Display the mappings (along with their ID)
foreach (DataRow mapping in mappings.Rows)
{
string dataSourceName = Utilities.getDataSourceName((int)mapping["sourceDataSource"]);
string tableName = Utilities.getTableName((int)mapping["sourceTable"]);
Console.WriteLine("ID: " + mapping["ID"].ToString() + " Name: " + mapping["targetName"].ToString() + " (Data Source: " + dataSourceName + "| Table: " + tableName + " Table ID: " + mapping["sourceTable"] + ")");
}
}
//Query the virtual data source
public static Dictionary <Wrapper, DataTable> query(string query)
{
// Basic implementation
// Get the WHERE clause (this is all we are passing in for now)
List <string> qWhere = query.Split(' ').ToList <string>();
string qWhereField = qWhere[0];
string qWhereOperator = qWhere[1];
string qWhereValue = qWhere[2];
// Find the mapping for the WHERE field
DataTable mapping = MappingEngine.getMappingSource(qWhereField);
// Store the table ID
int whereSourceTable = (int)mapping.Rows[0]["SourceTable"];
// Store the datas source ID
int whereDataSource = (int)mapping.Rows[0]["SourceDataSource"];
// Build a dictionary to hold our results
Dictionary <Wrapper, DataTable> results = new Dictionary <Wrapper, DataTable>();
// We must now loop through all of our data sources and query them for their data
foreach (KeyValuePair <string, Wrapper> dataSource in Program.dataSources)
{
// Store the wrapper so it's easier to reference
Wrapper wrapper = dataSource.Value;
Dictionary <string, Dictionary <string, string> > where = null;
// Check if our mapping table contains this data source's ID
bool contains = mapping.AsEnumerable().Any(row => dataSource.Value.getID() == row.Field <int>("SourceDataSource"));
Console.WriteLine(contains);
// Does our WHERE apply to this data source?
if (dataSource.Value.getID() == whereDataSource)
{
// We need to build a WHERE clause for this data source
// Get the name of the table the WHERE applies to
string whereTableName = MappingEngine.getTableName(whereSourceTable);
// Build the WHERE clause dictionary
where = new Dictionary <string, Dictionary <string, string> >();
// Add the field name to the dictionary
where.Add(qWhereField, new Dictionary <string, string>());
// Add the operator and value to the dictionary for that field
where[qWhereField].Add("operator", qWhereOperator);
where[qWhereField].Add("value", qWhereValue);
}
// Generate a list of columns that we want from this data source, for now we just get them all
// TODO - Only get the columns we actually need (can we do this?)
List <string> columns = new List <string> {
"*"
};
// Generate a list of tables that we want for this data source, for now we just get all the mapped tables
// TODO - Only get tables we actually need (can we do this?)
DataTable mappedTables = MappingEngine.getMappedTables(wrapper);
List <string> tables = mappedTables.AsEnumerable().Select(row => row.Field <string>("Name")).ToList();
results[Program.dataSources[dataSource.Key]] = wrapper.queryWithWhere(columns, tables, where);
}
return(results);
}
// Function to start the SuperView system including initialising and mapping all defined data sources
static void startSuperView()
{
string response;
Console.WriteLine("Loading data source configurations...");
// Load the data source definitions and create the required objects
loadDataSourceDefinitions();
createDataSourceObjects();
Console.WriteLine(dataSources.Count().ToString() + " data source(s) loaded");
Console.WriteLine("");
// Loop through each of the data sources
foreach (KeyValuePair <string, Wrapper> dataSource in dataSources)
{
Wrapper wrapper = dataSource.Value;
Console.WriteLine("Data Source: " + wrapper.getName() + " (ID: " + wrapper.getID().ToString() + ")");
Console.Write("Would you like to configure mappings for this data source? (Y/N): ");
string reponse = Console.ReadLine();
// Perform the mappings
if (reponse.ToString().ToLower() == "y")
{
// Map the tables
Console.WriteLine("Mapping tables...");
// Give the user the option to preserve table mappings
if (MappingEngine.getMappedTables(wrapper).Rows.Count > 0)
{
Console.WriteLine("Table mappings already exist for '" + wrapper.getName() + "'");
}
else
{
// Loop through the tables
foreach (string table in wrapper.getTables())
{
// Ask the user if they want to map the table
Console.Write("Would you like to map the '" + table + "' table? (Y/N): ");
response = Console.ReadLine().ToString();
// If they do then map the table
if (response.ToLower() == "y")
{
MappingEngine.mapTable(wrapper, table);
Console.WriteLine(table);
}
}
// Display how many tables we have mapped in this data source
Console.WriteLine(MappingEngine.getMappedTables(wrapper).Rows.Count.ToString() + " table(s) mapped");
}
Console.WriteLine("");
// Map the fields
Console.WriteLine("Mapping fields...");
// Loop through every table that we have mapped for this data source
foreach (DataRow mapping in MappingEngine.getMappedTables(wrapper).Rows)
{
int tableID = (int)mapping["ID"];
string table = mapping["Name"].ToString();
// Check if there are any mappings for this table, if there are then we can't change them
if (MappingEngine.getMappings(wrapper, tableID).Rows.Count > 0)
{
Console.WriteLine("Mappings already exist for the '" + table + "' table.");
}
else
{
// Loop through each column in the data source
foreach (KeyValuePair <string, Dictionary <string, string> > column in wrapper.getColumns(table))
{
Console.WriteLine("Column Name: " + column.Key.ToString() + " | Data Type: " + column.Value["data_type"].ToString());
Console.Write("Enter a mapping name for this column (leave blank to ignore): ");
string mappingName = Console.ReadLine().ToString();
// If we entered a mapping name then add the mapping to the database
if (mappingName != "")
{
MappingEngine.mapField(wrapper, table, column.Key.ToString(), column.Value.ToString(), mappingName);
}
Console.WriteLine("");
}
Console.WriteLine(MappingEngine.getMappings(wrapper, tableID).Rows.Count.ToString() + " field(s) mapped");
}
}
}
}
Console.WriteLine("");
// Set up relations
Console.WriteLine("Creating relations...");
Console.Write("Would you like to add a relation? (Y/N): ");
response = Console.ReadLine();
// While the user wants to add more relations
while (response.ToString().ToLower() == "y")
{
// Get all of the mappings we have produced
DataTable mappings = MappingEngine.getMappings();
// Display the mappings (along with their ID)
foreach (DataRow mapping in mappings.Rows)
{
string dataSourceName = Utilities.getDataSourceName((int)mapping["sourceDataSource"]);
string tableName = Utilities.getTableName((int)mapping["sourceTable"]);
Console.WriteLine("ID: " + mapping["ID"].ToString() + " Name: " + mapping["targetName"].ToString() + " (Data Source: " + dataSourceName + "| Table: " + tableName + " Table ID: " + mapping["sourceTable"] + ")");
}
Console.Write("Enter table 1 ID: ");
int table1 = Int32.Parse(Console.ReadLine().ToString());
Console.Write("Enter table 2 ID: ");
int table2 = Int32.Parse(Console.ReadLine().ToString());
Console.Write("Enter relation for column 1 (E.g. {ID}): ");
string column1 = Console.ReadLine().ToString();
Console.Write("Enter relation for column 2 (E.g. {ID}): ");
string column2 = Console.ReadLine().ToString();
//Store the relation
MappingEngine.storeRelation(table1, table2, column1, column2);
// Check if the user wants to make another mapping
Console.Write("Would you like to add a relation? (Y/N): ");
response = Console.ReadLine();
}
}
