/// <summary>
/// Returns the full schema of the database including tables, indexes, foreign keys, etc.
/// </summary>
/// <remarks>
/// It's very slow for large databases
/// </remarks>
public static DataBaseSchema Load(DataBase database, string schemaProvider)
{
DataBaseSchema schema = new DataBaseSchema();
DatabaseSchema schemaReader;
using (var dbReader = new DatabaseSchemaReader.DatabaseReader(database.ConnectionString, schemaProvider))
{
dbReader.AllTables();
dbReader.AllViews();
try
{
dbReader.AllStoredProcedures();
}
catch { }
try
{
dbReader.AllUsers();
}
catch { }
schemaReader = dbReader.DatabaseSchema;
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
dbt.PrimaryKeyColumn.AddIdentity();
var table = new Table()
{
Name = dbt.Name,
DataBase = schema,
IdentityIncrement = dbt.PrimaryKeyColumn.IdentityDefinition.IdentityIncrement,
IdentitySeed = dbt.PrimaryKeyColumn.IdentityDefinition.IdentitySeed,
};
schema.Tables.Add(table);
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
var table = schema[dbt.Name];
foreach (DatabaseColumn dbc in dbt.Columns)
{
Column column = new Column()
{
Name = dbc.Name,
Table = table,
Description = dbc.Description,
IsNullable = dbc.Nullable,
IsAutoNumber = dbc.IsAutoNumber,
ComputedDefinition = dbc.ComputedDefinition,
DefaultValue = dbc.DefaultValue,
IsPrimaryKey = dbc.IsPrimaryKey,
Length = (uint?) dbc.Length,
Ordinal = dbc.Ordinal,
Precision = dbc.Precision,
Scale = dbc.Scale,
};
if (dbc.DataType != null)
{
column.DbType = DbTypeMapper.Parse(dbc.DataType.GetNetType());
}
else
{
if(dbc.DbDataType.StartsWith("varchar"))
{
column.DbType = DbType.AnsiString;
}
else if (dbc.DbDataType.StartsWith("int"))
{
column.DbType = DbType.Int32;
}
else if (dbc.DbDataType.StartsWith("decimal"))
{
column.DbType = DbType.Decimal;
}
else if (dbc.DbDataType.StartsWith("datetime"))
{
column.DbType = DbType.DateTime;
}
else if (dbc.DbDataType.StartsWith("money"))
{
column.DbType = DbType.Currency;
}
else if (dbc.DbDataType.StartsWith("char"))
{
column.DbType = DbType.AnsiStringFixedLength;
}
else if (dbc.DbDataType.StartsWith("text"))
{
column.DbType = DbType.AnsiString;
}
}
table.Columns.Add(column);
}
foreach (DatabaseIndex dbi in dbt.Indexes)
{
Index index = new Index()
{
Name = dbi.Name,
Table = table,
Direction = SortDirection.Ascending,
Unique = dbi.IsUnique,
};
foreach (DatabaseColumn dbc in dbi.Columns)
{
index.Columns.Add(table[dbc.Name]);
}
table.Indexes.Add(index);
}
foreach (DatabaseTrigger dbtr in dbt.Triggers)
{
DataBaseOperation operation = DataBaseOperation.Insert;
Enum.TryParse<DataBaseOperation>(dbtr.TriggerEvent, true, out operation);
Trigger trigger = new Trigger()
{
TriggerBody = dbtr.TriggerBody,
TriggerEvent = operation,
Table = table,
};
table.Triggers.Add(trigger);
}
foreach (DatabaseConstraint dbcons in dbt.CheckConstraints)
{
if (dbcons.ConstraintType == ConstraintType.Check)
{
CheckConstraint constraint = new CheckConstraint()
{
Expression = dbcons.Expression,
Table = table,
};
table.CheckConstraints.Add(constraint);
}
else if (dbcons.ConstraintType == ConstraintType.ForeignKey)
{
ForeignKey foreignKey = new ForeignKey()
{
Name= dbcons.Name,
DeleteAction = schema.ParseConstraintAction(dbcons.DeleteRule),
UpdateAction =schema.ParseConstraintAction(dbcons.UpdateRule),
RemoteTable = schema[dbcons.RefersToTable],
Table = table,
};
var referencedColumns = dbcons.ReferencedColumns(schemaReader).ToArray();
for (int i = 0; i < dbcons.Columns.Count; i++)
{
foreignKey.Columns.Add(new Tuple<Column,Column>(table[dbcons.Columns[i]], foreignKey.RemoteTable[referencedColumns[i]]));
}
table.ForeignKeys.Add(foreignKey);
}
}
}
foreach (DatabaseView dbv in schemaReader.Views)
{
View view = new View()
{
Name = dbv.Name,
Command = dbv.Sql,
Description = dbv.Description,
DataBase = schema,
};
schema.Views.Add(view);
}
foreach (DatabaseUser dbu in schemaReader.Users)
{
User user = new User()
{
Name = dbu.Name,
DataBase = schema,
};
schema.Users.Add(user);
}
return schema;
}
/// <summary>
/// Returns the Sql sentence for creating an index in the specified TypeMap<T>. Can be used when creatingt a table or after creation, for adding an index
/// </summary>
/// <param name="index">Index that will be added</param>
protected override Command IndexDefinition(Index index)
{
//local vars
Command sql = new Command();
//Creating the sql
sql +=
"CONSTRAINT " +
EncloseName(index.Name) +
" UNIQUE (";
//add columns to sql
foreach (var column in index.Columns)
{
sql += this.EncloseName(column.Name) + ", ";
}
//Enclosing the field lists
sql.Script = sql.Script.TrimEnd(',', ' ');
sql += ")";
return sql;
}
/// <summary>
/// Returns the Sql sentence for dropping an index in the specified Table
/// </summary>
/// <param name="index">Index that will be dropped</param>
/// <returns>Sql query for dropping the index</returns>
public virtual Command Drop(Index index)
{
return "ALTER TABLE " + EncloseName(index.Table.Name) + " DROP INDEX " + EncloseName(index.Name);
}
/// <summary>
/// Returns the Sql sentence for adding an index in the specified Table
/// </summary>
/// <param name="index">Index that will be added</param>
/// <returns>Sql query for adding the index</returns>
public virtual Command Create(Index index)
{
return "ALTER TABLE " + EncloseName(index.Table.Name) + " ADD " + IndexDefinition(index);
}
/// <summary>
/// Returns the Sql sentence for creating an index in the specified Table. Can be used when creating a table or after creation, for adding an index
/// </summary>
/// <param name="index">Index that will be added</param>
protected virtual Command IndexDefinition(Index index)
{
//local vars
string sql = string.Empty;
//Creating the sql
sql +=
(index.Unique ? "UNIQUE " : string.Empty) +
"INDEX " +
EncloseName(index.Name) +
"(";
//add columns to sql
foreach (Column column in index.Columns)
{
sql += EncloseName(column.Name) + ", ";
}
//Enclosing the field lists
sql = sql.TrimEnd(',', ' ');
sql += ")";
return sql;
}
/// <summary>
/// Returns the Sql sentence for creating an index in the specified TypeMap<T>. Can be used when creating a table or after creation, for adding an index
/// </summary>
/// <param name="index">Index that will be added</param>
protected override Command IndexDefinition(Index index)
{
//this method should not be used on Access databases
return string.Empty;
}
/// <summary>
/// Returns all the scripts necesary to create all indexes in a datatype
/// </summary>
/// <param name="dtype">TypeMap<T> which indexes will be generated</param>
/// <param name="inherits">If true, scripts for all base DataTypes indexes are generated too</param>
/// <returns>Sql script for creating indexes</returns>
public override Command Create(Index index)
{
string sql = string.Empty;
sql +=
"CREATE " +
(index.Unique ? "UNIQUE " : string.Empty) +
"INDEX " +
this.EncloseName(index.Name) +
" ON " +
EncloseName(index.Table.Name) +
"(";
//add columns to sql
foreach (var c in index.Columns)
{
sql += this.EncloseName(c.Name) + ", ";
}
//Enclosing the field lists
sql = sql.TrimEnd(',', ' ');
sql += ")";
return sql;
}
