internal ForeignKeyToItem(ForeignKey fkey, string catalog, string table, string column)
: base(fkey, catalog, table, column)
{
}
private ForeignKey(ForeignKey source)
{
_table = source._table;
_id = source._id;
_ordinal = source._ordinal;
_from = new ForeignKeyFromItem(this, source._from.Column);
_to = new ForeignKeyToItem(this, source._to.Catalog, source._to.Table, source._to.Column);
_name = source._name;
_onUpdate = source._onUpdate;
_onDelete = source._onDelete;
_match = source._match;
_dirty = source._dirty;
}
internal ForeignKeyItem(ForeignKey fkey, string catalog, string table, string column)
{
_catalog = catalog;
_table = table;
_column = column;
_fkey = fkey;
}
internal ForeignKeyFromItem(ForeignKey fkey, string column)
: base(fkey, fkey._table.Catalog, fkey._table.Name, column)
{
}
private void WriteFKeys(List <ForeignKey> keys, StringBuilder builder)
{
for (int index = 0; index < keys.Count;)
{
ForeignKey key = keys[index];
if (index > 0)
{
builder.Append(",\r\n ");
}
builder.AppendFormat(
"CONSTRAINT [{0}] FOREIGN KEY (", key.Name);
int startIndex = index;
do
{
builder.AppendFormat("{0}[{1}]",
index > startIndex ? ", " : String.Empty,
keys[index].From.Column);
index++;
} while (index < keys.Count && keys[index].Id == key.Id);
builder.AppendFormat(") REFERENCES [{0}]", key.To.Table);
if (!String.IsNullOrEmpty(key.To.Column))
{
builder.Append(" (");
index = startIndex;
do
{
builder.AppendFormat("{0}[{1}]",
index > startIndex ? ", " : String.Empty,
keys[index].To.Column);
index++;
} while (index < keys.Count && keys[index].Id == key.Id);
builder.Append(')');
}
if (!String.IsNullOrEmpty(key.Match))
{
builder.AppendFormat(" MATCH {0}", key.Match);
}
if (!String.IsNullOrEmpty(key.OnUpdate))
{
builder.AppendFormat(" ON UPDATE {0}", key.OnUpdate);
}
if (!String.IsNullOrEmpty(key.OnDelete))
{
builder.AppendFormat(" ON DELETE {0}", key.OnDelete);
}
if (index == startIndex)
{
index++;
}
}
}
internal ForeignKeyToItem(ForeignKey fkey, string catalog, string table, string column)
: base(fkey, catalog, table, column)
{
}
internal ForeignKeyFromItem(ForeignKey fkey, string column)
: base(fkey, fkey._table.Catalog, fkey._table.Name, column)
{
}
public string GetSql()
{
StringBuilder builder = new StringBuilder();
string altName = null;
if (_exists)
{
Guid g = Guid.NewGuid();
altName = String.Format(CultureInfo.InvariantCulture, "{0}_{1}", Name, g.ToString("N"));
if (_oldindexes.Count > 0)
{
builder.Append("-- Drop previous indexes on the table\r\n");
foreach (Index idx in _oldindexes)
{
builder.AppendFormat("DROP INDEX [{0}].[{1}];\r\n", _catalog, idx.Name);
}
builder.AppendLine();
}
if (_oldtriggers.Count > 0)
{
builder.Append("-- Drop previous triggers on the table\r\n");
foreach (Trigger trig in _oldtriggers)
{
builder.AppendFormat("DROP TRIGGER [{0}].[{1}];\r\n", _catalog, trig.Name);
}
builder.AppendLine();
}
builder.Append("-- Rename the old table\r\n");
builder.AppendFormat("ALTER TABLE [{0}].[{1}] RENAME TO [{2}];\r\n\r\n", _catalog, _oldname, altName);
}
builder.Append("-- Create the new table\r\n");
builder.AppendFormat("CREATE TABLE [{0}].[{1}] (\r\n", _catalog, Name);
string separator = " ";
foreach (Column c in Columns)
{
if (String.IsNullOrEmpty(c.ColumnName) == false)
{
builder.Append(separator);
c.WriteSql(builder);
separator = ",\r\n ";
}
}
if (_key.Columns.Count > 1)
{
string innersep = "";
builder.AppendFormat(CultureInfo.InvariantCulture, "{0}CONSTRAINT [PK_{1}] PRIMARY KEY (", separator, Name);
foreach (IndexColumn c in _key.Columns)
{
builder.AppendFormat(CultureInfo.InvariantCulture, "{0}[{1}]", innersep, c.Column);
if (String.IsNullOrEmpty(c.Collate) == false && String.Compare(c.Collate, "BINARY", StringComparison.OrdinalIgnoreCase) != 0)
{
builder.AppendFormat(CultureInfo.InvariantCulture, " COLLATE {0}", c.Collate.ToUpperInvariant());
}
if (c.SortMode != ColumnSortMode.Ascending)
{
builder.Append(" DESC");
}
innersep = ", ";
}
builder.Append(")");
if (_key.Conflict != ConflictEnum.Abort)
{
builder.AppendFormat(CultureInfo.InvariantCulture, " ON CONFLICT {0}", _key.Conflict.ToString().ToUpperInvariant());
}
}
for (int n = 0; n < Check.Count; n++)
{
string check = Check[n];
if (String.IsNullOrEmpty(check) == true)
{
continue;
}
SimpleTokenizer.StringParts[] arr = SimpleTokenizer.BreakString(check);
for (int x = 0; x < arr.Length; x++)
{
if (arr[x].depth == 0)
{
check = String.Format(CultureInfo.InvariantCulture, "({0})", check);
break;
}
}
builder.Append(separator);
builder.AppendFormat("CONSTRAINT [CK_{0}_{1}] CHECK {2}", Name, n + 1, check);
}
List <ForeignKey> keys = new List <ForeignKey>();
for (int x = 0; x < ForeignKeys.Count; x++)
{
ForeignKey key = ForeignKeys[x];
if (String.IsNullOrEmpty(key.From.Column) == true || String.IsNullOrEmpty(key.From.Catalog) == true ||
String.IsNullOrEmpty(key.To.Table) == true || String.IsNullOrEmpty(key.To.Column) == true)
{
continue;
}
if (keys.Count > 0)
{
if (keys[0].Name == key.Name && keys[0].To.Catalog == key.To.Catalog && keys[0].To.Table == key.To.Table)
{
keys.Add(key);
continue;
}
builder.Append(separator);
WriteFKeys(keys, builder);
keys.Clear();
}
keys.Add(key);
}
if (keys.Count > 0)
{
builder.Append(separator);
WriteFKeys(keys, builder);
}
builder.Append("\r\n);\r\n");
// Rebuilding an existing table
if (altName != null)
{
separator = "";
builder.Append("\r\n-- Copy the contents of the old table into the new table\r\n");
builder.AppendFormat("INSERT INTO [{0}].[{1}] (", _catalog, Name);
foreach (Column c in Columns)
{
if (String.IsNullOrEmpty(c.OriginalName) == false)
{
builder.AppendFormat("{1}[{0}]", c.ColumnName, separator);
separator = ", ";
}
}
builder.Append(")\r\n SELECT ");
separator = "";
foreach (Column c in Columns)
{
if (String.IsNullOrEmpty(c.OriginalName) == false)
{
builder.AppendFormat("{1}[{0}]", c.OriginalName, separator);
separator = ", ";
}
}
builder.AppendFormat("\r\n FROM [{0}].[{1}];\r\n\r\n", _catalog, altName);
builder.Append("-- Drop the old table\r\n");
builder.AppendFormat("DROP TABLE [{0}].[{1}];\r\n", _catalog, altName);
}
separator = "\r\n";
if (_indexes.Count > 0)
{
builder.Append("\r\n-- Create the new indexes");
foreach (Index idx in _indexes)
{
builder.Append(separator);
idx.WriteSql(builder);
}
builder.AppendLine();
}
if (_triggers.Count > 0)
{
builder.Append("\r\n-- Create the new triggers");
foreach (Trigger trig in _triggers)
{
builder.Append(separator);
trig.WriteSql(builder);
separator = "\r\n";
}
builder.AppendLine();
}
return(builder.ToString());
}
private ForeignKey(ForeignKey source)
{
_table = source._table;
_from = new ForeignKeyFromItem(this, source._from.Column);
_to = new ForeignKeyToItem(this, source._to.Catalog, source._to.Table, source._to.Column);
_name = source._name;
_dirty = source._dirty;
}
