public DbSchema(string name, DbTable[] tables)
{
if (name == null) throw new ArgumentNullException(nameof(name));
if (tables == null) throw new ArgumentNullException(nameof(tables));
if (tables.Length == 0) throw new ArgumentOutOfRangeException(nameof(tables));
this.Name = name;
this.Tables = tables;
}
public Entity(ClrClass @class, DbTable table, DbTable inverseTable = null)
{
if (@class == null) throw new ArgumentNullException(nameof(@class));
if (table == null) throw new ArgumentNullException(nameof(table));
this.Class = @class;
this.Table = table;
this.InverseTable = inverseTable;
}
public static void AppendSelect(StringBuilder buffer, DbTable table)
{
if (buffer == null) throw new ArgumentNullException(nameof(buffer));
if (table == null) throw new ArgumentNullException(nameof(table));
buffer.Append(@"SELECT");
buffer.Append(' ');
AppendColumns(buffer, table.Columns, AppendColumnName);
buffer.Append(' ');
buffer.Append(@"FROM");
buffer.Append(' ');
buffer.Append(table.Name);
}
/// <summary>
/// Create a CREATE TABLE statement
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static string CreateTable(DbTable table)
{
if (table == null) throw new ArgumentNullException(nameof(table));
var name = table.Name;
var columns = table.Columns;
var buffer = new StringBuilder(32 + name.Length + (64 * columns.Count));
// Table
buffer.Append(@"CREATE");
buffer.Append(' ');
buffer.Append(@"TABLE");
buffer.Append(' ');
buffer.Append('[');
buffer.Append(name);
buffer.Append(']');
buffer.AppendLine();
buffer.Append('(');
buffer.AppendLine();
// Columns
foreach (var column in columns)
{
buffer.Append('\t');
AppendColumnDefinition(buffer, column);
buffer.Append(',');
buffer.AppendLine();
}
// Foreign Keys
foreach (var column in columns)
{
var foreignKey = column.DbForeignKey;
if (foreignKey != null)
{
buffer.Append('\t');
AppendForeignKeyDefinition(buffer, foreignKey);
buffer.Append(',');
buffer.AppendLine();
}
}
// Remove the last comma
buffer.Remove(buffer.Length - (Environment.NewLine.Length + 1), 1);
buffer.Append(')');
buffer.Append(';');
buffer.AppendLine();
return buffer.ToString();
}
/// <summary>
/// Create a CREATE TABLE script
/// </summary>
/// <param name="tables"></param>
/// <returns></returns>
public static string CreateTables(DbTable[] tables)
{
if (tables == null) throw new ArgumentNullException(nameof(tables));
if (tables.Length == 0) throw new ArgumentOutOfRangeException(nameof(tables));
var buffer = new StringBuilder(256 * tables.Length);
foreach (var table in GetTablesSorted(tables))
{
buffer.AppendLine(CreateTable(table));
}
return buffer.ToString();
}
public static void AppendDelete(StringBuilder buffer, DbTable table)
{
if (buffer == null) throw new ArgumentNullException(nameof(buffer));
if (table == null) throw new ArgumentNullException(nameof(table));
buffer.Append(@"DELETE");
buffer.Append(' ');
buffer.Append(@"FROM");
buffer.Append(' ');
buffer.Append(table.Name);
buffer.Append(' ');
buffer.Append(@"WHERE");
buffer.Append(' ');
AppendColumns(buffer, IdColumn, AppendColumnParameterAssignment, true);
}
public void AttachInverseTable(DbTable table)
{
if (table == null) throw new ArgumentNullException(nameof(table));
foreach (var t in this.Schema.Tables)
{
if (table != t)
{
foreach (var column in t.Columns)
{
if (column.DbForeignKey != null && column.DbForeignKey.Table == table)
{
this.InverseTables.Add(table.Name, t);
return;
}
}
}
}
throw new ArgumentOutOfRangeException(nameof(table));
}
public static void AppendInsert(StringBuilder buffer, DbTable table)
{
if (buffer == null) throw new ArgumentNullException(nameof(buffer));
if (table == null) throw new ArgumentNullException(nameof(table));
buffer.Append(@"INSERT");
buffer.Append(' ');
buffer.Append(@"INTO");
buffer.Append(' ');
buffer.Append(table.Name);
buffer.Append(' ');
buffer.Append('(');
AppendColumns(buffer, table.Columns, AppendColumnName, true);
buffer.Append(')');
buffer.Append(' ');
buffer.Append(@"VALUES");
buffer.Append(' ');
buffer.Append('(');
AppendColumns(buffer, table.Columns, AppendParameterName, true);
buffer.Append(')');
}
private static DbTable[] GetTablesSorted(DbTable[] tables)
{
var sortedTables = new DbTable[tables.Length];
Array.Copy(tables, sortedTables, tables.Length);
Array.Sort(sortedTables, (x, y) =>
{
var a = x.Columns.Any(v => v.DbForeignKey != null);
var b = y.Columns.Any(v => v.DbForeignKey != null);
var cmp = a.CompareTo(b);
if (cmp == 0)
{
cmp = string.Compare(x.Name, y.Name, StringComparison.Ordinal);
}
return cmp;
});
return sortedTables;
}
/// <summary>
/// Create DROP TABLE script
/// </summary>
/// <param name="tables"></param>
/// <returns></returns>
public static string DropTables(DbTable[] tables)
{
if (tables == null) throw new ArgumentNullException(nameof(tables));
if (tables.Length == 0) throw new ArgumentOutOfRangeException(nameof(tables));
var buffer = new StringBuilder(32 * tables.Length);
var tablesSorted = GetTablesSorted(tables);
Array.Reverse(tablesSorted);
foreach (var table in tablesSorted)
{
AppendDropTable(buffer, table.Name);
buffer.AppendLine();
}
return buffer.ToString();
}
public DbForeignKey(DbTable table)
{
if (table == null) throw new ArgumentNullException(nameof(table));
this.Table = table;
}
public static void AppendSelectJoin(StringBuilder buffer, DbTable a, DbTable b)
{
if (buffer == null) throw new ArgumentNullException(nameof(buffer));
if (a == null) throw new ArgumentNullException(nameof(a));
if (b == null) throw new ArgumentNullException(nameof(b));
var aPrefix = GetPrefix(a.Name);
var bPrefix = GetPrefix(b.Name);
if (aPrefix == bPrefix)
{
bPrefix = @"_" + bPrefix;
}
buffer.Append(@"SELECT");
buffer.Append(' ');
var index = 0;
foreach (var column in a.Columns)
{
if (index++ > 0)
{
buffer.Append(',');
buffer.Append(' ');
}
buffer.Append(aPrefix);
buffer.Append('.');
buffer.Append(column.Name);
}
buffer.Append(',');
buffer.Append(' ');
index = 0;
foreach (var column in b.Columns)
{
if (column.DbForeignKey != null && column.DbForeignKey.Table == a)
{
continue;
}
if (index++ > 0)
{
buffer.Append(',');
buffer.Append(' ');
}
buffer.Append(bPrefix);
buffer.Append('.');
buffer.Append(column.Name);
}
buffer.Append(' ');
buffer.Append(@"FROM");
buffer.Append(' ');
buffer.Append(a.Name);
buffer.Append(' ');
buffer.Append(aPrefix);
buffer.Append(' ');
buffer.Append(@"LEFT");
buffer.Append(' ');
buffer.Append(@"JOIN");
buffer.Append(' ');
buffer.Append(b.Name);
buffer.Append(' ');
buffer.Append(bPrefix);
buffer.Append(' ');
buffer.Append(@"ON");
buffer.Append(' ');
buffer.Append(aPrefix);
buffer.Append('.');
// Add Primary Key
foreach (var column in a.Columns)
{
if (column.IsPrimaryKey)
{
buffer.Append(column.Name);
break;
}
}
buffer.Append(' ');
buffer.Append('=');
buffer.Append(' ');
buffer.Append(bPrefix);
buffer.Append('.');
// Add Foreign key to the PK
foreach (var column in b.Columns)
{
var foreignKey = column.DbForeignKey;
if (foreignKey != null && foreignKey.Table == a)
{
buffer.Append(column.Name);
break;
}
}
}
public Entity CreateEntity(DbTable table)
{
if (table == null) throw new ArgumentNullException(nameof(table));
DbTable inverseTable;
this.InverseTables.TryGetValue(table.Name, out inverseTable);
return new Entity(DbTableConverter.ConvertToClrClass(table, inverseTable), table, inverseTable);
}
public void MarkModifiable(DbTable table)
{
if (table == null) throw new ArgumentNullException(nameof(table));
this.ModifiableTables.Add(table.Name, table);
}
public void MarkHidden(DbTable table)
{
if (table == null) throw new ArgumentNullException(nameof(table));
this.HiddenTables.Add(table.Name, table);
}
public bool IsModifiable(DbTable table)
{
if (table == null) throw new ArgumentNullException(nameof(table));
return this.ModifiableTables.ContainsKey(table.Name);
}
/// <summary>
/// Create a DROP TABLE statement
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static string DropTable(DbTable table)
{
if (table == null) throw new ArgumentNullException(nameof(table));
var name = table.Name;
var buffer = new StringBuilder(16 + name.Length);
AppendDropTable(buffer, name);
return buffer.ToString();
}
public static DbColumn ForeignKey(DbTable table, bool isNullable = false)
{
if (table == null) throw new ArgumentNullException(nameof(table));
return new DbColumn(table.ClassName + IdName, DbColumnType.Integer, isNullable, false, new DbForeignKey(table));
}