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()); }
internal Trigger(ViewTableBase parent, DataRow row) { _table = parent; if (row != null) { _name = row["TRIGGER_NAME"].ToString(); string sql = row["TRIGGER_DEFINITION"].ToString(); _origsql = sql; SimpleTokenizer.StringParts[] arr = SimpleTokenizer.BreakString(sql); int x = 3; switch (arr[x].keyword) { case "BEFORE": _triggerOccurs = 0; break; case "AFTER": _triggerOccurs = 1; break; case "INSTEAD": _triggerOccurs = 2; x++; break; default: x--; break; } x++; switch (arr[x].keyword) { case "UPDATE": _type = TriggerType.Update; if (arr[x + 1].keyword == "OF") { x++; StringBuilder builder = new StringBuilder(); string separator = ""; while (arr[x + 1].keyword != "ON") { builder.AppendFormat("{0}[{1}]", separator, arr[x + 1].value); separator = ", "; x++; } _columns = builder.ToString(); } break; case "INSERT": _type = TriggerType.Insert; break; case "DELETE": _type = TriggerType.Delete; break; } x++; while (arr[x].keyword != "BEGIN") { if (arr[x].keyword == "FOR" && arr[x + 1].keyword == "EACH" && arr[x + 1].keyword == "ROW") { //_eachRow = true; x += 3; continue; } if (arr[x].keyword == "WHEN") { x++; int depth = 0; StringBuilder builder = new StringBuilder(); while (arr[x].keyword != "BEGIN") { if (builder.Length > 0) { builder.Append(" "); } while (depth < arr[x].depth) { depth++; builder.Append("("); } while (depth > arr[x].depth) { depth--; builder.Append(")"); } if (String.IsNullOrEmpty(arr[x].quote) == false) { builder.Append(arr[x].quote[0]); } builder.Append(arr[x].value); if (String.IsNullOrEmpty(arr[x].quote) == false) { builder.Append(arr[x].quote[1]); } x++; } while (depth > 0) { depth--; builder.Append(")"); } _when = builder.ToString(); break; } x++; } int startpos = arr[x].position + arr[x].value.Length; x = arr.Length - 1; while (arr[x].keyword != "END") { x--; } int endpos = arr[x].position; _action = sql.Substring(startpos, endpos - startpos); _action = _action.TrimStart('\r').TrimStart('\n').TrimEnd('\n').TrimEnd('\r').Trim(); } }
private void ReloadDefinition() { using (DataTable tbl = _connection.GetSchema("Tables", new string[] { Catalog, null, Name })) { if (tbl.Rows.Count > 0) { _exists = true; _origSql = tbl.Rows[0]["TABLE_DEFINITION"].ToString().Trim().TrimEnd(';'); _oldname = Name; } else { _exists = false; return; } } _indexes.Clear(); _oldindexes.Clear(); using (DataTable tbl = _connection.GetSchema("Indexes", new string[] { Catalog, null, Name })) { foreach (DataRow row in tbl.Rows) { if ((bool)row["PRIMARY_KEY"] == false) { if (row["INDEX_NAME"].ToString().StartsWith("sqlite_", StringComparison.OrdinalIgnoreCase) == false) { _indexes.Add(new Index(_connection, this, row)); _oldindexes.Add(new Index(_connection, this, row)); } } else if (_key == null) { _key = new PrimaryKey(_connection, this, row); } } } _check.Clear(); StringBuilder builder = new StringBuilder(); SimpleTokenizer.StringParts[] arr = SimpleTokenizer.BreakString(_origSql); for (int n = 0; n < arr.Length - 3; n++) { if (arr[n].keyword == "CONSTRAINT") { builder.Length = 0; int x; for (x = 1; x < 3; x++) { if (arr[n + x].keyword == "CHECK") { break; } } if (x == 3) { n += 2; continue; } x += n + 1; int depth = arr[n].depth; int basedepth = arr[x].depth; for (; x < arr.Length; x++) { if (arr[x].depth < basedepth) { break; } if (builder.Length > 0) { builder.Append(" "); } while (depth < arr[x].depth) { builder.Append("("); depth++; } while (depth > arr[x].depth) { builder.Append(")"); depth--; } if (String.IsNullOrEmpty(arr[x].quote) == false) { builder.Append(arr[x].quote[0]); } builder.Append(arr[x].value); if (String.IsNullOrEmpty(arr[x].quote) == false) { builder.Append(arr[x].quote[1]); } if (arr[x].sep == true) { break; } } while (depth > arr[n].depth) { builder.Append(")"); depth--; } n = x; _check.Add(builder.ToString()); } } builder.Length = 0; builder.AppendLine("-- Original table schema"); builder.Append(_origSql); builder.AppendLine(";"); foreach (Index idx in _oldindexes) { builder.AppendFormat("{0};\r\n", idx.OriginalSql); } _triggers.Clear(); _oldtriggers.Clear(); using (DataTable tbl = _connection.GetSchema("Triggers", new string[] { Catalog, null, Name })) { foreach (DataRow row in tbl.Rows) { Trigger t = new Trigger(this, row); _triggers.Add(t); _oldtriggers.Add(((ICloneable)t).Clone() as Trigger); builder.AppendFormat("{0};\r\n", t.OriginalSql); } } _origSql = builder.ToString(); }