public static DataAssembler FromQuery(DataDomain dataDomain, string query)
{
ISqlScript sqlScript = SqlParserService.Parse(query);
if (sqlScript.Statements != null && sqlScript.Statements.Count > 0)
{
ISqlStatement statement = sqlScript.Statements[0];
if (statement is ISelectStatement)
{
ISelectStatement selectStatement = statement as ISelectStatement;
if (selectStatement.Tables != null && selectStatement.Tables.Count > 0)
{
SqlTable sqlTable = selectStatement.Tables[0];
Table table = dataDomain[sqlTable.Name];
DataAssembler dataAssembler = new DataAssembler(dataDomain.DomainUrl.Url, table, query);
return dataAssembler;
}
}
}
return null;
}
public void AddDataDomain(DataDomain dataDomain)
{
if( !Contains(dataDomain) )
{
_dataDomains = ArrayUtils.Resize(_dataDomains, _dataDomains.Length + 1) as DataDomain[];
_dataDomains[_dataDomains.Length-1] = dataDomain;
}
}
public override DataDomain BuildDomain()
{
DataDomain domain = new DataDomain(this.Driver.DomainUrl);
using(IDbConnection connection = this.Driver.OpenConnection() )
{
DataTable schemaTable = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[0]);
DataColumn tableTypeColumn = schemaTable.Columns["TABLE_TYPE"];
DataColumn tableNameColumn = schemaTable.Columns["TABLE_NAME"];
foreach(DataRow schemaRowTable in schemaTable.Rows)
{
if (string.Compare(schemaRowTable[tableTypeColumn].ToString(), "TABLE") == 0)
{
string tableName = schemaRowTable[tableNameColumn].ToString();
Table table = new Table(tableName);
DataTable schemaColumns = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName });
DataColumn ordinalPosition = schemaColumns.Columns["ORDINAL_POSITION"];
DataColumn dataType = schemaColumns.Columns["DATA_TYPE"];
DataColumn columnNameColumn = schemaColumns.Columns["COLUMN_NAME"];
DataColumn numericPrecisionColumn = schemaColumns.Columns["NUMERIC_PRECISION"];
DataColumn allowDBNull = schemaColumns.Columns["IS_NULLABLE"];
DataColumn columnSize = schemaColumns.Columns["CHARACTER_MAXIMUM_LENGTH"];
DataColumn numericScale = schemaColumns.Columns["NUMERIC_SCALE"];
DataColumn descriptionColumn = schemaColumns.Columns["DESCRIPTION"];
schemaColumns.DefaultView.Sort = ordinalPosition.ColumnName;
foreach (DataRowView schemaRowColumn in schemaColumns.DefaultView)
{
string columnName = schemaRowColumn[columnNameColumn.Ordinal].ToString();
Column column = new Column(columnName);
column.IsNullable = (bool)schemaRowColumn[allowDBNull.Ordinal];
OleDbType oleDbType = (OleDbType)schemaRowColumn[dataType.Ordinal];
column.OriginalSQLType = oleDbType.ToString();
if(_sqlTypesDictionary.Contains(oleDbType))
column.SqlType = (SqlType)_sqlTypesDictionary[oleDbType];
else
column.SqlType = SqlType.Unknown;
if (_netDataTypes.ContainsKey(column.SqlType))
{
column.NetDataType = (string)_netDataTypes[column.SqlType];
column.ActionScriptType = TypeMapper.GetActionScriptType(_netDataTypes[column.SqlType] as string);
}
else
column.NetDataType = "unknown";
if( schemaRowColumn[numericPrecisionColumn.Ordinal] != DBNull.Value )
column.Length = Convert.ToInt32(schemaRowColumn[numericPrecisionColumn.Ordinal]);
if((column.SqlType == SqlType.VarChar) || (column.SqlType == SqlType.VarBinary) || (column.SqlType == SqlType.Binary) )
{
column.Length = Convert.ToInt32(schemaRowColumn[columnSize.Ordinal]);
if (column.SqlType == SqlType.VarChar)
{
column.SqlType = SqlType.Text;
//column.Length = 0;
}
else
column.IsBlob = true;
}
else if (column.SqlType == SqlType.Decimal)
{
column.Length = Convert.ToInt32(schemaRowColumn[numericPrecisionColumn.Ordinal]);
column.Scale = Convert.ToInt32(schemaRowColumn[numericScale.Ordinal]);
}
if (schemaRowColumn[descriptionColumn.Ordinal] != DBNull.Value)
{
string description = schemaRowColumn[descriptionColumn.Ordinal] as string;
if (description == "AutoNumber")
column.IsIdentity = true;
}
table.AddColumn(column);
}
DataTable schemaPrimaryKeys = ((OleDbConnection)connection).GetOleDbSchemaTable( OleDbSchemaGuid.Primary_Keys, new object [] { null, null, tableName});
DataColumn pkColumnNameColumn = schemaColumns.Columns["COLUMN_NAME"];
foreach (DataRowView schemaRowPK in schemaPrimaryKeys.DefaultView)
{
string columnName = (string)schemaRowPK[pkColumnNameColumn.Ordinal];
Column column = table[ columnName ];
if( column != null )
column.IsPrimaryKey = true;
}
domain.AddTable(table);
}
}
foreach(DataRow schemaRowTable in schemaTable.Rows)
{
if (string.Compare(schemaRowTable[tableTypeColumn].ToString(), "TABLE") == 0)
{
string tableName = schemaRowTable[tableNameColumn].ToString();
DataTable schemaForeignKeys = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[0]);
DataColumn constraintName = schemaForeignKeys.Columns["FK_NAME"];
DataColumn columnOrdinal = schemaForeignKeys.Columns["ORDINAL"];
DataColumn childTableName = schemaForeignKeys.Columns["FK_TABLE_NAME"];
DataColumn parentColumnName = schemaForeignKeys.Columns["FK_COLUMN_NAME"];
DataColumn updateRule = schemaForeignKeys.Columns["UPDATE_RULE"];
DataColumn deleteRule = schemaForeignKeys.Columns["DELETE_RULE"];
DataColumn parentTableName = schemaForeignKeys.Columns["PK_TABLE_NAME"];
DataColumn childColumnName = schemaForeignKeys.Columns["PK_COLUMN_NAME"];
schemaForeignKeys.DefaultView.Sort = constraintName + "," + columnOrdinal.ColumnName;
schemaForeignKeys.DefaultView.RowFilter = childTableName.ColumnName + " = '" + tableName + "'";
foreach (DataRowView schemaRowFK in schemaForeignKeys.DefaultView)
{
string parentTable = schemaRowFK[parentTableName.Ordinal].ToString();
string primaryKeyColumnName = schemaRowFK[childColumnName.Ordinal].ToString();
}
}
}
}
return domain;
}
public bool Contains(DataDomain dataDomain)
{
foreach(DataDomain dataDomainTmp in _dataDomains)
{
if( dataDomainTmp.DomainUrl.Url == dataDomain.DomainUrl.Url )
return true;
}
return false;
}