public PersistenceController()
{
schema = new CatalogSchema();
engine = new SQLiteEngine();
repo = new Repository(schema, engine);
mediaItemSingleton = schema.GetAggregate <IMediaItem>("MediaItemSingleton");
mediaSetWithAllChildren = schema.GetAggregate <IMediaSet>("MediaSetWithAllChildren");
mappingRepo = new Telesophy.Alexandria.Model.Mapping.Repository();
}
protected override CatalogSchema OnGetSchema(DbConnection connection)
{
var tables = connection.GetSchema("Tables");
var columns = connection.GetSchema("AllColumns");
var foreignKeys = connection.GetSchema("ForeignKeys");
var indexColumns = connection.GetSchema("IndexColumns");
var tableRows = tables.Rows.Cast <DataRow>().ToArray();
var catalog = new CatalogSchema
{
ConnectionString = connection.ConnectionString,
Name = (string)tableRows.FirstOrDefault()?["TABLE_CATALOG"],
AssemblyQualifiedName = typeof(SqlServerDataAccess).AssemblyQualifiedName
};
#region Create Tables
foreach (DataRow column in columns.Rows)
{
var tableSchema = (string)column["TABLE_SCHEMA"];
var tableName = (string)column["TABLE_NAME"];
var columnName = (string)column["COLUMN_NAME"];
var ordinalPosition = (int)column["ORDINAL_POSITION"];
var isNullable = (string)column["IS_NULLABLE"];
var dataType = (string)column["DATA_TYPE"];
var maxCharsLength = (int?)(column["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? column["CHARACTER_MAXIMUM_LENGTH"] : null);
var maxBytesLength = (int?)(column["CHARACTER_OCTET_LENGTH"] != DBNull.Value ? column["CHARACTER_OCTET_LENGTH"] : null);
var numericPrecision = (byte?)(column["NUMERIC_PRECISION"] != DBNull.Value ? column["NUMERIC_PRECISION"] : null);
var numericPrecisionRadix = (short?)(column["NUMERIC_PRECISION_RADIX"] != DBNull.Value ? column["NUMERIC_PRECISION_RADIX"] : null);
var tableRow = tableRows.FirstOrDefault((dRow, tName) => (string)dRow["TABLE_NAME"] == tName, tableName);
var table = catalog.Tables.FirstOrDefault((t, vTuple) => t.Name == vTuple.tableName && t.Schema == vTuple.tableSchema, (tableName, tableSchema));
if (table == null)
{
table = new TableSchema {
Name = tableName, Schema = tableSchema
};
switch ((string)tableRow?["TABLE_TYPE"])
{
case "BASE TABLE":
table.Type = TableType.Table;
break;
case "VIEW":
table.Type = TableType.View;
break;
}
catalog.Tables.Add(table);
}
var tableColumn = table.Columns.FirstOrDefault((c, cName) => c.Name == cName, columnName);
if (tableColumn == null)
{
tableColumn = new TableColumnSchema
{
Name = columnName,
Position = ordinalPosition,
IsNullable = isNullable == "YES",
NumericPrecision = numericPrecision,
NumericPrecisionRadix = numericPrecisionRadix
};
#region DataType Switch
switch (dataType)
{
case "uniqueidentifier":
tableColumn.DataType = (tableColumn.IsNullable ? "Guid?" : "Guid");
break;
case "char":
case "nchar":
case "varchar":
case "nvarchar":
tableColumn.DataType = "string";
tableColumn.MaxLength = maxCharsLength;
break;
case "varbinary":
case "binary":
case "rowversion":
tableColumn.DataType = "byte[]";
tableColumn.MaxLength = maxBytesLength;
break;
case "bigint":
tableColumn.DataType = (tableColumn.IsNullable ? "long?" : "long");
break;
case "int":
tableColumn.DataType = (tableColumn.IsNullable ? "int?" : "int");
break;
case "smallint":
tableColumn.DataType = (tableColumn.IsNullable ? "short?" : "short");
break;
case "tinyint":
tableColumn.DataType = (tableColumn.IsNullable ? "byte?" : "byte");
break;
case "bit":
tableColumn.DataType = (tableColumn.IsNullable ? "bool?" : "bool");
break;
case "real":
tableColumn.DataType = (tableColumn.IsNullable ? "float?" : "float");
break;
case "float":
tableColumn.DataType = (tableColumn.IsNullable ? "double?" : "double");
break;
case "smallmoney":
case "money":
case "numeric":
case "decimal":
tableColumn.DataType = (tableColumn.IsNullable ? "decimal?" : "decimal");
break;
case "date":
case "smalldatetime":
case "datetime":
tableColumn.DataType = (tableColumn.IsNullable ? "DateTime?" : "DateTime");
break;
case "geography":
tableColumn.DataType = "object";
break;
}
#endregion
table.Columns.Add(tableColumn);
}
}
#endregion
#region Create Indexes
foreach (DataRow indexColumn in indexColumns.Rows)
{
var constraintName = (string)indexColumn["constraint_name"];
var tableSchema = (string)indexColumn["table_schema"];
var tableName = (string)indexColumn["table_name"];
var columnName = (string)indexColumn["column_name"];
var ordinalPosition = (int)indexColumn["ordinal_position"];
var keyType = (byte)indexColumn["KeyType"];
var indexName = (string)indexColumn["index_name"];
var table = catalog.Tables.FirstOrDefault((t, vTuple) => t.Name == vTuple.tableName && t.Schema == vTuple.tableSchema, (tableName, tableSchema));
if (table == null)
{
continue;
}
var tIndex = table.Indexes.FirstOrDefault((i, iName) => i.Name == iName, indexName);
if (tIndex == null)
{
tIndex = new TableIndexSchema {
Name = indexName, ConstraintName = constraintName
};
if (indexName.StartsWith("PK_"))
{
tIndex.Type = IndexType.PrimaryKey;
}
else if (indexName.StartsWith("UK_"))
{
tIndex.Type = IndexType.UniqueKey;
}
else if (indexName.StartsWith("IX_"))
{
tIndex.Type = IndexType.NonClusteredNonUniqueIndex;
}
else if (indexName.StartsWith("UX_"))
{
tIndex.Type = IndexType.UniqueIndex;
}
else if (indexName.StartsWith("AK_"))
{
tIndex.Type = IndexType.AlternateIndex;
}
else if (indexName.StartsWith("CLIX_"))
{
tIndex.Type = IndexType.ClusteredIndex;
}
else if (indexName.StartsWith("COVIX_"))
{
tIndex.Type = IndexType.CoveringIndex;
}
else if (indexName.StartsWith("UC_"))
{
tIndex.Type = IndexType.UniqueClusteredIndex;
}
table.Indexes.Add(tIndex);
}
var tColumn = new TableIndexColumnSchema
{
ColumnName = columnName,
ColumnPosition = ordinalPosition,
KeyType = keyType
};
tIndex.Columns.Add(tColumn);
var column = table.Columns.FirstOrDefault((cName, colName) => cName.Name == colName, columnName);
if (column != null && !column.IndexesName.Contains(indexName))
{
column.IndexesName.Add(indexName);
}
}
#endregion
#region Create ForeignKeys
foreach (DataRow foreignKey in foreignKeys.Rows)
{
var constraintName = (string)foreignKey["CONSTRAINT_NAME"];
var tableSchema = (string)foreignKey["TABLE_SCHEMA"];
var tableName = (string)foreignKey["TABLE_NAME"];
var table = catalog.Tables.FirstOrDefault((t, vTuple) => t.Name == vTuple.tableName && t.Schema == vTuple.tableSchema, (tableName, tableSchema));
if (table == null)
{
continue;
}
var fkTable = constraintName.Replace(tableName, string.Empty);
fkTable = fkTable.Substring(4);
table.ForeignKeys.Add(new ForeignKeySchema
{
ConstraintName = constraintName,
ForeignTable = fkTable
});
}
#endregion
return(catalog);
}
/// <summary>
/// Dal Generator
/// </summary>
/// <param name="schema">Catalog schema</param>
/// <param name="namespace">Namespace name</param>
public DalGenerator(CatalogSchema schema, string @namespace)
{
_schema = schema;
_namespace = @namespace;
}
