protected override void CompilerImplementation(TEntity Entity)
{
DBTableField PrimaryField = Manager.Cache.GetFields <TEntity>().SingleOrDefault(x => x.IsPrimaryKey);
SingleCondition = (PrimaryField.Name, new MySqlParameter
{
ParameterName = "@0",
Value = PrimaryField.Property.GetValue(Entity)
});
}
public async Task Insert <TEntity>(TEntity Item) where TEntity : TableEntity
{
InsertCompiler <TEntity> Compiler = new InsertCompiler <TEntity>();
Compiler.Compile(Item);
int ScalarResult = await InsertAsync(QueryBuilder.Insert(Compiler), Compiler.GetParameters());
DBTableField AutoIncrementField = Manager.Cache.GetFields <TEntity>().SingleOrDefault(x => x.IsAutoIncrement);
if (AutoIncrementField != null)
{
AutoIncrementField.Property.SetValue(Item, ScalarResult);
}
}
/// <summary>
/// Retrieves the table- and field meta data for the tables which names are in the passed in elementNames and which are in the schema specified.
/// </summary>
/// <param name="schemaToFill">The schema to fill.</param>
/// <param name="elementNames">The element names.</param>
/// <remarks>Implementers should add DBTable instances with the DBTableField instances to the DBSchema instance specified.
/// Default implementation is a no-op</remarks>
protected override void RetrieveTableAndFieldMetaData(DBSchema schemaToFill, IEnumerable <DBElementName> elementNames)
{
#region Description of queries used
//field query:
//select tc.*, d.domain_name, d.[precision], r.remarks from systabcol tc
//inner join sysdomain d on tc.domain_id = d.domain_id left join sysremark r on tc.object_id = r.object_id
//where table_id =
//(
// select table_id from systab where table_name =?
// and creator =
// (
// select user_id from sysuser where user_name='<schemaname>'
// )
//)
//
// uc query
//select i.index_name, tc.column_name
//from sysidx i inner join sysidxcol ic
//on i.table_id=ic.table_id
//and i.index_id=ic.index_id
//inner join systabcol tc
//on ic.table_id = tc.table_id
//and ic.column_id = tc.column_id
//where i.table_id in
//(
// select table_id from systab
// where creator =
// (
// select user_id from sysuser where user_name='<schemaname>'
// )
// and table_name=?
//)
// AND i.[unique] in (1, 2)
// AND i.index_category=3
#endregion
DbConnection connection = this.DriverToUse.CreateConnection();
DbCommand fieldCommand = this.DriverToUse.CreateCommand(connection, string.Format("select tc.*, d.domain_name, d.[precision], r.remarks from systabcol tc inner join sysdomain d on tc.domain_id = d.domain_id left join sysremark r on tc.object_id = r.object_id where table_id = (select table_id from systab where table_name = ? and creator = (select user_id from sysuser where user_name='{0}')) order by tc.column_id asc", schemaToFill.SchemaOwner));
DbParameter tableNameParameter = this.DriverToUse.CreateParameter(fieldCommand, "@table_name", string.Empty);
DbDataAdapter fieldAdapter = this.DriverToUse.CreateDataAdapter(fieldCommand);
DbCommand pkCommand = this.DriverToUse.CreateStoredProcCallCommand(connection, "sp_pkeys");
DbParameter pkRetrievalTableNameParameter = this.DriverToUse.CreateParameter(pkCommand, "@table_name", string.Empty);
this.DriverToUse.CreateParameter(pkCommand, "@table_owner", schemaToFill.SchemaOwner);
DbDataAdapter pkRetrievalAdapter = this.DriverToUse.CreateDataAdapter(pkCommand);
DbCommand ucCommand = this.DriverToUse.CreateCommand(connection, string.Format("select i.index_name, tc.column_name from sysidx i inner join sysidxcol ic on i.table_id=ic.table_id and i.index_id=ic.index_id inner join systabcol tc on ic.table_id = tc.table_id and ic.column_id = tc.column_id where i.table_id in ( select table_id from systab where creator = (select user_id from sysuser where user_name='{0}' ) and table_name=? ) AND i.[unique] in (1, 2) AND i.index_category=3", schemaToFill.SchemaOwner));
DbParameter ucRetrievalTableNameParameter = this.DriverToUse.CreateParameter(ucCommand, "@table_name", string.Empty);
DbDataAdapter ucRetrievalAdapter = this.DriverToUse.CreateDataAdapter(ucCommand);
DataTable ucFieldsInTable = new DataTable();
DataTable fieldsInTable = new DataTable();
DataTable pkFieldsInTable = new DataTable();
List <DBTable> tablesToRemove = new List <DBTable>();
try
{
connection.Open();
fieldCommand.Prepare();
pkCommand.Prepare();
ucCommand.Prepare();
foreach (DBElementName tableName in elementNames)
{
DBTable currentTable = new DBTable(schemaToFill, tableName);
schemaToFill.Tables.Add(currentTable);
tableNameParameter.Value = currentTable.Name;
// get the fields.
fieldsInTable.Clear();
fieldAdapter.Fill(fieldsInTable);
try
{
int ordinalPosition = 1;
var fields = from row in fieldsInTable.AsEnumerable()
let typeDefinition = CreateTypeDefinition(row, "domain_name", "width")
let defaultValue = row.Value <string>("default") ?? string.Empty
let isIdentity = ((defaultValue == "autoincrement") || (row.Value <int>("max_identity") > 0))
select new DBTableField(row.Value <string>("column_name"), typeDefinition, row.Value <string>("remarks") ?? string.Empty)
{
OrdinalPosition = ordinalPosition++,
DefaultValue = defaultValue,
IsIdentity = isIdentity,
IsComputed = (row.Value <string>("column_type") == "C"),
IsNullable = (row.Value <string>("nulls") == "Y"),
IsTimeStamp = (typeDefinition.DBType == (int)AsaDbTypes.TimeStamp),
ParentTable = currentTable
};
currentTable.Fields.AddRange(fields);
// get Primary Key fields for this table
pkRetrievalTableNameParameter.Value = currentTable.Name;
pkFieldsInTable.Clear();
pkRetrievalAdapter.Fill(pkFieldsInTable);
foreach (DataRow row in pkFieldsInTable.AsEnumerable())
{
string columnName = row.Value <string>("column_name");
DBTableField primaryKeyField = currentTable.FindFieldByName(columnName);
if (primaryKeyField != null)
{
primaryKeyField.IsPrimaryKey = true;
// PrimaryKeyConstraintName is not set, as ASA allows dropping and creating PKs without names. It's therefore not obtained (and not available either)
}
}
// get UC fields for this table
ucRetrievalTableNameParameter.Value = currentTable.Name;
ucFieldsInTable.Clear();
ucRetrievalAdapter.Fill(ucFieldsInTable);
var ucFieldsPerUc = from row in ucFieldsInTable.AsEnumerable()
group row by row.Value <string>("index_name") into g
select g;
foreach (IGrouping <string, DataRow> ucFields in ucFieldsPerUc)
{
DBUniqueConstraint currentUC = new DBUniqueConstraint(ucFields.Key)
{
AppliesToTable = currentTable
};
bool addUc = true;
foreach (DataRow row in ucFields)
{
DBTableField currentField = currentTable.FindFieldByName(row.Value <string>("column_name"));
if (currentField == null)
{
continue;
}
currentUC.Fields.Add(currentField);
}
addUc &= (currentUC.Fields.Count > 0);
if (addUc)
{
currentTable.UniqueConstraints.Add(currentUC);
currentUC.AppliesToTable = currentTable;
}
}
}
catch (ApplicationException ex)
{
// non fatal error, remove the table, proceed
schemaToFill.LogError(ex, "Table '" + currentTable.Name + "' removed from list due to an internal exception in Field population: " + ex.Message, "SybaseAsaSchemaRetriever::RetrieveTableAndFieldMetaData");
tablesToRemove.Add(currentTable);
}
catch (InvalidCastException ex)
{
// non fatal error, remove the table, proceed
schemaToFill.LogError(ex, "Table '" + currentTable.Name + "' removed from list due to cast exception in Field population.", "SybaseAsaSchemaRetriever::RetrieveTableAndFieldMetaData");
tablesToRemove.Add(currentTable);
}
}
}
finally
{
connection.SafeClose(true);
}
foreach (DBTable toRemove in tablesToRemove)
{
schemaToFill.Tables.Remove(toRemove);
}
}
/// <summary>
/// Retrieves all Foreign keys.
/// </summary>
/// <param name="catalogMetaData">The catalog meta data.</param>
private void RetrieveForeignKeys(DBCatalog catalogMetaData)
{
#region Description of query used
//select fks.user_name AS FK_SCHEMA, fkt.table_name as FK_TABLE_NAME, fcol.column_name AS FK_COLUMN_NAME,
// pks.user_name AS PK_SCHEMA, pkt.table_name as PK_TABLE_NAME, pcol.column_name AS PK_COLUMN_NAME, sc.constraint_name as FK_NAME
//from sysfkey fk inner join systab fkt on fk.foreign_table_id = fkt.table_id
// inner join systab pkt on fk.primary_table_id = pkt.table_id
// inner join sysuser fks on fkt.creator = fks.user_id
// inner join sysuser pks on pkt.creator = pks.user_id
// inner join sysidxcol fic on fic.table_id = fk.foreign_table_id and fic.index_id = fk.foreign_index_id
// inner join sysidxcol pic on pic.table_id = fk.primary_table_id and pic.index_id = fk.primary_index_id
// and fic.primary_column_id = pic.column_id
// inner join syscolumn fcol on fic.table_id=fcol.table_id and fic.column_id = fcol.column_id
// inner join syscolumn pcol on pic.table_id=pcol.table_id and pic.column_id = pcol.column_id
// inner join sysidx si on si.table_id = fk.foreign_table_id and si.index_id = fk.foreign_index_id and si.index_category=2
// inner join sysconstraint sc on sc.ref_object_id=si.object_id
//where fks.user_name in (<schema list>) and pks.user_name in (<schema list>)
//order BY fks.user_name ASC, fkt.table_name ASC, fic.column_id ASC
#endregion
string inClause = String.Join(", ", catalogMetaData.Schemas.Select(s => string.Format("'{0}'", s.SchemaOwner)).ToArray());
string query = string.Format("select fks.user_name AS FK_SCHEMA, fkt.table_name as FK_TABLE_NAME, fcol.column_name AS FK_COLUMN_NAME, pks.user_name AS PK_SCHEMA, pkt.table_name as PK_TABLE_NAME, pcol.column_name AS PK_COLUMN_NAME, sc.constraint_name as FK_NAME from sysfkey fk inner join systab fkt on fk.foreign_table_id = fkt.table_id inner join systab pkt on fk.primary_table_id = pkt.table_id inner join sysuser fks on fkt.creator = fks.user_id inner join sysuser pks on pkt.creator = pks.user_id inner join sysidxcol fic on fic.table_id = fk.foreign_table_id and fic.index_id = fk.foreign_index_id inner join sysidxcol pic on pic.table_id = fk.primary_table_id and pic.index_id = fk.primary_index_id and fic.primary_column_id = pic.column_id inner join syscolumn fcol on fic.table_id=fcol.table_id and fic.column_id = fcol.column_id inner join syscolumn pcol on pic.table_id=pcol.table_id and pic.column_id = pcol.column_id inner join sysidx si on si.table_id = fk.foreign_table_id and si.index_id = fk.foreign_index_id and si.index_category=2 inner join sysconstraint sc on sc.ref_object_id=si.object_id where fks.user_name in ({0}) and pks.user_name in ({0}) order BY fks.user_name ASC, fkt.table_name ASC, fic.column_id ASC", inClause);
DbDataAdapter adapter = this.DriverToUse.CreateDataAdapter(query);
DataTable foreignKeys = new DataTable();
adapter.Fill(foreignKeys);
string currentPKTableName = string.Empty;
string currentFKTableName = string.Empty;
string currentFKName = string.Empty;
// traverse per FK table name the fields which are stored in an FK constraint per different PK table name.
DBForeignKeyConstraint newForeignKeyConstraint = new DBForeignKeyConstraint();
DBTable tableForeignKey = null;
DBTable tablePrimaryKey = null;
bool fkValid = false;
foreach (DataRow row in foreignKeys.AsEnumerable())
{
string previousPKTableName = currentPKTableName;
currentPKTableName = row.Value <string>("PK_SCHEMA") + row.Value <string>("PK_TABLE_NAME");
string previousFKTableName = currentFKTableName;
currentFKTableName = row.Value <string>("FK_SCHEMA") + row.Value <string>("FK_TABLE_NAME");
string previousFKName = currentFKName;
currentFKName = row.Value <string>("FK_NAME");
// if this is a new FK table, we've to start from scratch with a new FK constraint. If this isn't a new FK table, we've to check if this is a new
// PK table. if so, we've also to start from scratch with a new FK constraint. If this isn't a new PK table, we've to check whether the FK name
// changed. If so, we're dealing with a new FK constraint. Otherwise it's the same FK.
if ((previousFKTableName != currentFKTableName) || (previousPKTableName != currentPKTableName) || (previousFKName != currentFKName))
{
// create a new FK
fkValid = true;
newForeignKeyConstraint = new DBForeignKeyConstraint();
newForeignKeyConstraint.ConstraintName = "FK_" + Guid.NewGuid().ToString("N");
DBSchema schemaForeignKey = catalogMetaData.FindSchemaByName(row.Value <string>("FK_SCHEMA"));
if (schemaForeignKey == null)
{
fkValid = false;
continue;
}
tableForeignKey = schemaForeignKey.FindTableByName(row.Value <string>("FK_TABLE_NAME"));
// Get Primary Key Table, first get the schema, has to be there
DBSchema schemaPrimaryKey = catalogMetaData.FindSchemaByName(row.Value <string>("PK_SCHEMA"));
if (schemaPrimaryKey == null)
{
fkValid = false;
continue;
}
tablePrimaryKey = schemaPrimaryKey.FindTableByName(row.Value <string>("PK_TABLE_NAME"));
if ((tableForeignKey == null) || (tablePrimaryKey == null))
{
// not found. next
fkValid = false;
continue;
}
// Add to Foreign Key table.
tableForeignKey.ForeignKeyConstraints.Add(newForeignKeyConstraint);
}
// test again, if the FK is based on 2 or more fields, this test is required.
if (!fkValid)
{
// not valid, skip
if (tableForeignKey != null)
{
tableForeignKey.ForeignKeyConstraints.Remove(newForeignKeyConstraint);
}
continue;
}
newForeignKeyConstraint.AppliesToTable = tableForeignKey;
DBTableField foreignKeyField = tableForeignKey.FindFieldByName(row.Value <string>("FK_COLUMN_NAME"));
DBTableField primaryKeyField = tablePrimaryKey.FindFieldByName(row.Value <string>("PK_COLUMN_NAME"));
if ((foreignKeyField == null) || (primaryKeyField == null))
{
tableForeignKey.ForeignKeyConstraints.Remove(newForeignKeyConstraint);
fkValid = false;
continue;
}
newForeignKeyConstraint.PrimaryKeyFields.Add(primaryKeyField);
newForeignKeyConstraint.ForeignKeyFields.Add(foreignKeyField);
}
}