/// <summary>
/// The method loads structure of the specified table and stores it as a set in this schema.
///
/// The method loops through all columns and for each of them creates or finds existing three elements in the schema:
/// a complex path from this set to the primtiive domain, a simple FK dimension from this set to the target FK set, and
/// a complex path from the target FK set to the primitive domain. The complex path corresponding to the column will
/// contain only two segments and this path definition has to be corrected later.
/// </summary>
/// <param name="tableName"></param>
/*
* private void ImportPaths(string tableName)
* {
* // Find set corresonding to this table
* ComTable tableSet = Root.FindSubset(tableName);
*
* Debug.Assert(!tableSet.IsPrimitive, "Wrong use: cannot load paths into a primitive set.");
*
* DataTable pks = connection.conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, tableName });
* DataTable fks = null;
* try
* {
* fks = connection.conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, null, null, tableName });
* }
* catch (Exception e) // For csv, foreign keys are not supported exception is raised
* {
* fks = new DataTable();
* }
*
* DataTable columns = connection.conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
* foreach (DataRow col in columns.Rows) // Process all columns of the table (correspond to primitive paths of the set)
* {
* string columnName = col["COLUMN_NAME"].ToString();
* string columnType = ((OleDbType)col["DATA_TYPE"]).ToString();
*
* DimAttribute path = tableSet.GetGreaterPathByColumnName(columnName); // It might have been already created (when processing other tables)
* if (path == null)
* {
* path = new DimAttribute(columnName);
* path.RelationalColumnName = columnName;
* path.Input = tableSet; // Assign domain set give the table name
* path.Output = Top.GetPrimitiveSubset(columnType);
* tableSet.AddGreaterPath(path); // We do not know if it is FK or simple dimensin. It will be determined later.
* }
*
* // Find PKs this attribute belongs to (among all PKs of this table)
* foreach (DataRow pk in pks.Rows)
* {
* string pkColumnName = (string)pk["COLUMN_NAME"];
* if (!columnName.Equals(pkColumnName, StringComparison.InvariantCultureIgnoreCase)) continue;
* string pkName = (string)pk["PK_NAME"];
*
* // Found PK this column belongs to
* path.RelationalPkName = pkName;
* path.Input.RelationalPkName = pkName; // OPTIMIZE: try to do it only once rather than for each attribute and try to identify and exclude multiple PKs (error)
* path.IsIdentity = true;
* break; // Assume that a column can belong to only one PK
* }
*
* // Find FKs this attribute belongs to (among all FKs of this table)
* foreach (DataRow fk in fks.Rows)
* {
* if (!columnName.Equals((string)fk["FK_COLUMN_NAME"], StringComparison.InvariantCultureIgnoreCase)) continue;
*
* // Target PK name fk["PK_NAME"] is not stored and is not used because we assume that there is only one PK
*
* //
* // Step 1. Add FK-segment (as a dimension)
* //
*
* string fkName = (string)fk["FK_NAME"];
* path.RelationalFkName = fkName;
* Debug.Assert(tableSet.GetGreaterDim(fkName) == null, "A dimension already exists.");
*
* Dim fkSegment = new Dim(fkName); // It is the first segment in the path representing this FK
* fkSegment.RelationalFkName = fkName;
* fkSegment.IsIdentity = path.IsIdentity;
* fkSegment.RelationalPkName = path.RelationalPkName; // We assume if one column belongs t PK then the whole FK (this column belongs to) belongs to the same PK
*
* fkSegment.Input = tableSet;
*
* string fkTargetTableName = (string)fk["PK_TABLE_NAME"]; // Name of the target set of the simple dimension (first segment of this complex path)
* Set fkTargetSet = Root.FindSubset(fkTargetTableName);
* fkSegment.Output = fkTargetSet;
* fkSegment.Add(); // Add this FK-dimension to the set (it is always new)
*
* if (path.Path.Count == 0)
* {
* path.Path.Add(fkSegment);
* }
* else
* {
* path.Path[0] = fkSegment; // Or we can insert it before all other segments
* Debug.Assert(true, "Wrong use: A primary key dimension must be inserted only as the very first segment - not the second.");
* }
*
* //
* // Step 2. Add rest of the path (as a path)
* //
*
* string fkTargetColumnName = (string)fk["PK_COLUMN_NAME"]; // Next path name belonging to the target set
* DimPath fkTargetPath = fkTargetSet.GetGreaterPathByColumnName(fkTargetColumnName); // This column might have been created
* if (fkTargetPath == null)
* {
* fkTargetPath = new DimPath(fkTargetColumnName);
* fkTargetPath.RelationalColumnName = fkTargetColumnName;
* fkTargetPath.IsIdentity = path.IsIdentity;
* fkTargetPath.Input = fkTargetSet;
* fkTargetPath.Output = path.Output;
* fkTargetSet.AddGreaterPath(fkTargetPath); // We do not know if it is really a FK or simple dimension so this needs to be fixed later
* }
*
* if (path.Path.Count == 0)
* {
* Debug.Assert(true, "Wrong use: A target path must be inserted only as the second segment - not any other.");
* }
* else if (path.Path.Count == 1)
* {
* path.Path.Add(fkTargetPath);
* }
* else
* {
* path.Path[1] = fkTargetPath;
* Debug.Assert(true, "Wrong use: A target path must be inserted only as the second segment - not any other.");
* }
*
* break; // We assume that a column can belong to only one FK and do not continue with the rest of the FK-loop
* }
*
* if (path.Path.Count == 0) // Not FK - just normal column
* {
* Dim dim = new Dim(path.Name);
* dim.RelationalColumnName = path.RelationalColumnName;
* dim.RelationalPkName = path.RelationalPkName;
* dim.Input = path.Input;
* dim.Output = path.Output;
* dim.IsIdentity = path.IsIdentity;
* dim.Add();
*
* path.Path.Add(dim); // The path will consist of a single segment
* }
*
* }
*
* }
*/
#endregion
#region Data methods
/// <summary>
/// Load data corresponding to the specified set from the underlying database.
/// </summary>
/// <returns></returns>
public DataTable LoadTable(DcTable table) // Load data for only this table (without greater tables connected via FKs)
{
TableRel tab = (TableRel)table;
string select = "";
List <ColumnAtt> attributes = tab.GreaterPaths;
foreach (ColumnAtt att in attributes)
{
select += "[" + att.RelationalColumnName + "]" + ", ";
}
select = select.Substring(0, select.Length - 2);
string from = "[" + tab.RelationalTableName + "]";
string where = ""; // tab.WhereExpression
string orderby = ""; // tab.OrderbyExpression
// Send query to the remote database for execution
string query = "SELECT " + select + " FROM " + from + " ";
query += String.IsNullOrEmpty(where) ? "" : "WHERE " + where + " ";
query += String.IsNullOrEmpty(orderby) ? "" : "ORDER BY " + orderby + " ";
connection.Open();
DataTable dataTable = connection.ExecuteSelect(query);
connection.Close();
return(dataTable);
}
public ConnectionOledb connection; // Connection object for access to the native engine functions
// Use name of the connection for setting schema name
#endregion
#region Schema methods
public List <DcTable> LoadTables(List <string> tableNames = null)
{
if (tableNames == null)
{
tableNames = connection.ReadTables();
}
//
// Create all sets
//
List <DcTable> tables = new List <DcTable>();
foreach (string tableName in tableNames)
{
TableRel tab = (TableRel)Space.CreateTable(DcSchemaKind.Rel, tableName, Root); // Create a table
tab.RelationalTableName = tableName;
// Relational PK name will be set during column loading
tables.Add(tab);
}
return(tables);
}
//
// What is Set::AddAllNonStoredPaths() ???
//
/*
* public void ImportSchema(List<string> tableNames = null)
* {
* if (tableNames == null)
* {
* tableNames = connection.ReadTables();
* }
*
* // Create all sets
* foreach (string tableName in tableNames)
* {
* Set set = new Set(tableName); // Create a set
* set.RelationalTableName = tableName;
* this.AddTable(set, null);
* }
*
* // Load columns and FKs as (complex) paths and (simple) FK-dimensions
* foreach (string tableName in tableNames)
* {
* ImportPaths(tableName);
* }
*
* List<ComTable> sets = Root.GetAllSubsets();
* foreach (ComTable set in sets)
* {
* foreach (DimPath path in set.GreaterPaths)
* {
* path.ExpandPath();
* }
* }
*
* foreach (Set set in sets)
* {
* set.AddAllNonStoredPaths();
* }
* }
*/
protected List <ColumnAtt> LoadAttributes(TableRel table)
{
// The created paths will be not be added to the schema (should be added manually)
// The created paths are empty and do not store any dimensions (should be done/expanded separately by using the meta-data about PKs, FKs etc.)
Debug.Assert(!table.IsPrimitive, "Wrong use: cannot load structure for primitive set.");
List <ColumnAtt> attributes = new List <ColumnAtt>();
string tableName = table.RelationalTableName;
DataTable pks = connection.GetPks(tableName);
DataTable fks = connection.GetFks(tableName);
DataTable columns = connection.GetColumns(tableName);
foreach (DataRow col in columns.Rows) // Process all columns of the table (correspond to primitive paths of the set)
{
string columnName = col["COLUMN_NAME"].ToString();
string columnType = ((OleDbType)col["DATA_TYPE"]).ToString();
DcTable typeTable = Schema.GetPrimitiveType(columnType);
//
// Create an attribute object representing this column
//
ColumnAtt path = table.GetGreaterPathByColumnName(columnName); // It might have been already created (when processing other tables)
if (path != null)
{
continue;
}
path = new ColumnAtt(columnName, table, typeTable);
//
// Set relational attribute of the object
//
path.RelationalColumnName = columnName;
// Find PKs this attribute belongs to (among all PKs of this table)
foreach (DataRow pk in pks.Rows)
{
if (!StringSimilarity.SameColumnName(columnName, (string)pk["COLUMN_NAME"]))
{
continue;
}
// Found PK this column belongs to
path.RelationalPkName = (string)pk["PK_NAME"];
table.RelationalPkName = path.RelationalPkName; // OPTIMIZE: try to do it only once rather than for each attribute and try to identify and exclude multiple PKs (error)
//path.IsIdentity = true; // We simply have to override this property as "RelationalPkName != null" or "RelationalPkName == table.RelationalPkName"
break; // Assume that a column can belong to only one PK
}
// Find FKs this attribute belongs to (among all FKs of this table)
foreach (DataRow fk in fks.Rows)
{
if (!StringSimilarity.SameColumnName(columnName, (string)fk["FK_COLUMN_NAME"]))
{
continue;
}
// Target PK name fk["PK_NAME"] is not stored and is not used because we assume that there is only one PK
path.RelationalFkName = (string)fk["FK_NAME"];
path.RelationalTargetTableName = (string)fk["PK_TABLE_NAME"]; // Name of the target set of the simple dimension (first segment of this complex path)
path.RelationalTargetColumnName = (string)fk["PK_COLUMN_NAME"]; // Next path/attribute name belonging to the target set
break; // We assume that a column can belong to only one FK and do not continue with the rest of the FK-loop
}
attributes.Add(path);
}
return(attributes);
}
