private void SetupTables(ISourceInfo Source, Database Temp)
{
Contract.Requires<ArgumentNullException>(Temp != null, "Temp");
Contract.Requires<ArgumentNullException>(Temp.Tables != null, "Temp.Tables");
foreach (Utilities.ORM.Manager.Schema.Default.Database.Table Table in Temp.Tables)
{
IEnumerable<dynamic> Values = Provider.Batch(Source)
.AddCommand(null, null, @"SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN_TYPE],
sys.columns.max_length as [MAX_LENGTH], sys.columns.is_nullable as [IS_NULLABLE],
sys.columns.is_identity as [IS_IDENTITY], sys.index_columns.index_id as [IS_INDEX],
key_constraints.name as [PRIMARY_KEY], key_constraints_1.name as [UNIQUE],
tables_1.name as [FOREIGN_KEY_TABLE], columns_1.name as [FOREIGN_KEY_COLUMN],
sys.default_constraints.definition as [DEFAULT_VALUE]
FROM sys.tables
INNER JOIN sys.columns on sys.columns.object_id=sys.tables.object_id
INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id
LEFT OUTER JOIN sys.index_columns on sys.index_columns.object_id=sys.tables.object_id and sys.index_columns.column_id=sys.columns.column_id
LEFT OUTER JOIN sys.key_constraints on sys.key_constraints.parent_object_id=sys.tables.object_id and sys.key_constraints.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=sys.key_constraints.unique_index_id and sys.key_constraints.type='PK'
LEFT OUTER JOIN sys.foreign_key_columns on sys.foreign_key_columns.parent_object_id=sys.tables.object_id and sys.foreign_key_columns.parent_column_id=sys.columns.column_id
LEFT OUTER JOIN sys.tables as tables_1 on tables_1.object_id=sys.foreign_key_columns.referenced_object_id
LEFT OUTER JOIN sys.columns as columns_1 on columns_1.column_id=sys.foreign_key_columns.referenced_column_id and columns_1.object_id=tables_1.object_id
LEFT OUTER JOIN sys.key_constraints as key_constraints_1 on key_constraints_1.parent_object_id=sys.tables.object_id and key_constraints_1.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=key_constraints_1.unique_index_id and key_constraints_1.type='UQ'
LEFT OUTER JOIN sys.default_constraints on sys.default_constraints.object_id=sys.columns.default_object_id
WHERE (sys.tables.name = @0) AND (sys.systypes.xusertype <> 256)",
CommandType.Text,
Table.Name)
.Execute()[0];
SetupColumns(Table, Values);
SetupTriggers(Source, Table, Values);
}
foreach (Utilities.ORM.Manager.Schema.Default.Database.Table Table in Temp.Tables)
{
Table.SetupForeignKeys();
}
}
private void SetupViews(ISourceInfo Source, Database Temp)
{
Contract.Requires<ArgumentNullException>(Temp != null, "Temp");
Contract.Requires<ArgumentNullException>(Temp.Views != null, "Temp.Views");
foreach (View View in Temp.Views)
{
IEnumerable<dynamic> Values = Provider.Batch(Source)
.AddCommand(null, null, @"SELECT OBJECT_DEFINITION(sys.views.object_id) as Definition FROM sys.views WHERE sys.views.name=@0",
CommandType.Text,
View.Name)
.Execute()[0];
View.Definition = Values.First().Definition;
Values = Provider.Batch(Source)
.AddCommand(null, null, @"SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN_TYPE],
sys.columns.max_length as [MAX_LENGTH], sys.columns.is_nullable as [IS_NULLABLE]
FROM sys.views
INNER JOIN sys.columns on sys.columns.object_id=sys.views.object_id
INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id
WHERE (sys.views.name = @0) AND (sys.systypes.xusertype <> 256)",
CommandType.Text,
View.Name)
.Execute()[0];
foreach (dynamic Item in Values)
{
string ColumnName = Item.Column;
string ColumnType = Item.COLUMN_TYPE;
int MaxLength = Item.MAX_LENGTH;
if (ColumnType == "nvarchar")
MaxLength /= 2;
bool Nullable = Item.IS_NULLABLE;
View.AddColumn<string>(ColumnName, ColumnType.To<string, SqlDbType>().To(DbType.Int32), MaxLength, Nullable);
}
}
}
private void SetupStoredProcedures(ISourceInfo Source, Database Temp)
{
Contract.Requires<ArgumentNullException>(Source != null, "Source");
Contract.Requires<NullReferenceException>(Provider != null, "Provider");
IEnumerable<dynamic> Values = Provider.Batch(Source)
.AddCommand(null, null, CommandType.Text,
"SELECT sys.procedures.name as NAME,OBJECT_DEFINITION(sys.procedures.object_id) as DEFINITION FROM sys.procedures")
.Execute()[0];
foreach (dynamic Item in Values)
{
Temp.AddStoredProcedure(Item.NAME, Item.DEFINITION);
}
foreach (StoredProcedure Procedure in Temp.StoredProcedures)
{
Values = Provider.Batch(Source)
.AddCommand(null, null, @"SELECT sys.systypes.name as TYPE,sys.parameters.name as NAME,sys.parameters.max_length as LENGTH,sys.parameters.default_value as [DEFAULT VALUE] FROM sys.procedures INNER JOIN sys.parameters on sys.procedures.object_id=sys.parameters.object_id INNER JOIN sys.systypes on sys.systypes.xusertype=sys.parameters.system_type_id WHERE sys.procedures.name=@0 AND (sys.systypes.xusertype <> 256)",
CommandType.Text,
Procedure.Name)
.Execute()[0];
foreach (dynamic Item in Values)
{
string Type = Item.TYPE;
string Name = Item.NAME;
int Length = Item.LENGTH;
if (Type == "nvarchar")
Length /= 2;
string Default = Item.DEFAULT_VALUE;
Procedure.AddColumn<string>(Name, Type.To<string, SqlDbType>().To(DbType.Int32), Length, DefaultValue: Default);
}
}
}
private void SetupFunctions(ISourceInfo Source, Database Temp)
{
Contract.Requires<ArgumentNullException>(Source != null, "Source");
Contract.Requires<NullReferenceException>(Provider != null, "Provider");
IEnumerable<dynamic> Values = Provider.Batch(Source)
.AddCommand(null, null, CommandType.Text,
"SELECT SPECIFIC_NAME as NAME,ROUTINE_DEFINITION as DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE INFORMATION_SCHEMA.ROUTINES.ROUTINE_TYPE='FUNCTION'")
.Execute()[0];
foreach (dynamic Item in Values)
{
Temp.AddFunction(Item.NAME, Item.DEFINITION);
}
}
private void GetTables(ISourceInfo Source, Database Temp)
{
Contract.Requires<ArgumentNullException>(Source != null, "Source");
Contract.Requires<NullReferenceException>(Provider != null, "Provider");
IEnumerable<dynamic> Values = Provider.Batch(Source)
.AddCommand(null, null, CommandType.Text, "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES")
.Execute()[0];
foreach (dynamic Item in Values)
{
string TableName = Item.TABLE_NAME;
string TableType = Item.TABLE_TYPE;
if (TableType == "BASE TABLE")
Temp.AddTable(TableName);
else if (TableType == "VIEW")
Temp.AddView(TableName);
}
}
/// <summary>
/// Sets up the specified database schema
/// </summary>
/// <param name="Mappings">The mappings.</param>
/// <param name="Database">The database.</param>
/// <param name="QueryProvider">The query provider.</param>
public void Setup(ListMapping<IDatabase, IMapping> Mappings, IDatabase Database, Utilities.ORM.Manager.QueryProvider.Manager QueryProvider, Graph<IMapping> Structure)
{
var TempSource = SourceProvider.GetSource(Database.Name);
var TempDatabase = new Utilities.ORM.Manager.Schema.Default.Database.Database(Regex.Match(TempSource.Connection, "Initial Catalog=(.*?;)").Value.Replace("Initial Catalog=", "").Replace(";", ""));
SetupTables(Mappings, Database, TempDatabase);
SetupJoiningTables(Mappings, Database, TempDatabase);
SetupAuditTables(Database, TempDatabase);
foreach (ITable Table in TempDatabase.Tables)
{
Table.SetupForeignKeys();
}
var Commands = GenerateSchema(TempDatabase, SourceProvider.GetSource(Database.Name)).ToList();
var Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name));
for (int x = 0; x < Commands.Count; ++x)
{
if (Commands[x].ToUpperInvariant().Contains("CREATE DATABASE"))
{
QueryProvider.Batch(SourceProvider.GetSource(Regex.Replace(SourceProvider.GetSource(Database.Name).Connection, "Initial Catalog=(.*?;)", ""))).AddCommand(null, null, CommandType.Text, Commands[x]).Execute();
}
else if (Commands[x].Contains("CREATE TRIGGER") || Commands[x].Contains("CREATE FUNCTION"))
{
if (Batch.CommandCount > 0)
{
Batch.Execute();
Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name));
}
Batch.AddCommand(null, null, CommandType.Text, Commands[x]);
if (x < Commands.Count - 1)
{
Batch.Execute();
Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name));
}
}
else
{
Batch.AddCommand(null, null, CommandType.Text, Commands[x]);
}
}
Batch.Execute();
}
/// <summary>
/// Gets the structure of a source
/// </summary>
/// <param name="Source">Source to use</param>
/// <returns>The source structure</returns>
public ISource GetSourceStructure(ISourceInfo Source)
{
var DatabaseName = Regex.Match(Source.Connection, "Initial Catalog=(.*?;)").Value.Replace("Initial Catalog=", "").Replace(";", "");
var DatabaseSource = SourceProvider.GetSource(Regex.Replace(Source.Connection, "Initial Catalog=(.*?;)", ""));
if (!SourceExists(DatabaseName, DatabaseSource))
return null;
var Temp = new Database(DatabaseName);
GetTables(Source, Temp);
SetupTables(Source, Temp);
SetupViews(Source, Temp);
SetupStoredProcedures(Source, Temp);
SetupFunctions(Source, Temp);
return Temp;
}
