/******************************************************** * ADO.NET 2.0 Data Provider for SQLite Version 3.X * Written by Robert Simpson (robert@blackcastlesoft.com) * * Released to the public domain, use at your own risk! ********************************************************/ namespace SQLite.Designer.Design { using System; using System.Data.Common; using System.ComponentModel.Design; using System.ComponentModel; using System.Drawing.Design; using System.Collections.Generic; using System.Data; using System.Text; using System.Globalization; using SQLite.Designer.Editors; internal abstract class ViewTableBase: IHaveConnection { public abstract string OldName { get; } public abstract string Name { get; set; } public abstract string Catalog { get; } public abstract object Triggers { get; } public abstract void MakeDirty(); public abstract DbConnection GetConnection(); public abstract ViewTableBase DesignTable { get; } } internal class Table : ViewTableBase, ICustomTypeDescriptor { private string _name; private string _oldname; private string _catalog; private List _columns = new List(); private bool _exists; private string _origSql = String.Empty; private List _indexes = new List(); private List _oldindexes = new List(); private List _fkeys = new List(); private List _oldfkeys = new List(); private List _check = new List(); private List _triggers = new List(); private List _oldtriggers = new List(); private PrimaryKey _key; internal TableDesignerDoc _owner; internal DbConnection _connection; internal Table(string tableName, DbConnection connection, TableDesignerDoc owner) { _owner = owner; _oldname = tableName; _connection = connection; _name = tableName; _owner.Name = _name; _catalog = _connection.Database; // main ReloadDefinition(); if (_key == null) _key = new PrimaryKey(_connection, this, null); if (_exists) { using (DataTable tbl = connection.GetSchema("ForeignKeys", new string[] { Catalog, null, Name })) { foreach (DataRow row in tbl.Rows) { _fkeys.Add(new ForeignKey(connection, this, row)); _oldfkeys.Add(new ForeignKey(connection, this, row)); } } } using (DataTable tbl = connection.GetSchema("Columns", new string[] { Catalog, null, Name })) { foreach (DataRow row in tbl.Rows) { _columns.Add(new Column(row, this)); } } } public override void MakeDirty() { _owner.MakeDirty(); } 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(); } internal void Committed() { _exists = true; ReloadDefinition(); foreach (Column c in Columns) c.Committed(); foreach (ForeignKey key in ForeignKeys) key.ClearDirty(); foreach (Index idx in Indexes) idx.ClearDirty(); if (PrimaryKey != null) PrimaryKey.ClearDirty(); } [Browsable(false)] public List Indexes { get { return _indexes; } } [Browsable(false)] public PrimaryKey PrimaryKey { get { return _key; } set { _key = value; _owner.Invalidate(); } } [Browsable(false)] public List ForeignKeys { get { return _fkeys; } } [Browsable(false)] public List Check { get { return _check; } } [Browsable(false)] public override object Triggers { get { return _triggers; } } [Browsable(false)] public string OriginalSql { get { return _origSql; } } [Category("Storage")] [RefreshProperties(RefreshProperties.All)] [ParenthesizePropertyName(true)] [NotifyParentProperty(true)] public override string Name { get { return _name; } set { if (_name != value) { _name = value; _owner.Name = value; _owner.MakeDirty(); } } } [Browsable(false)] public override string OldName { get { return _oldname; } } public override string ToString() { return String.Format(CultureInfo.InvariantCulture, "[{0}].[{1}]", Catalog, Name); } [Category("Storage")] [Editor(typeof(CatalogTypeEditor), typeof(UITypeEditor))] [DefaultValue("main")] [RefreshProperties(RefreshProperties.All)] public override string Catalog { get { return _catalog; } } [Category("Storage")] public string Database { get { return _connection.DataSource; } } [Browsable(false)] public List Columns { get { return _columns; } } 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 = String.Empty; 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); } if (ForeignKeys.Count > 0) { builder.Append(separator); WriteFKeys(ForeignKeys, builder); } builder.Append("\r\n);\r\n"); // Rebuilding an existing table if (altName != null) { separator = String.Empty; 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 = String.Empty; 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 void WriteFKeys(List 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++; } } [Browsable(false)] public override ViewTableBase DesignTable { get { return this; } } public override DbConnection GetConnection() { return _connection; } #region ICustomTypeDescriptor Members AttributeCollection ICustomTypeDescriptor.GetAttributes() { return TypeDescriptor.GetAttributes(GetType()); } string ICustomTypeDescriptor.GetClassName() { return "Table Design"; } string ICustomTypeDescriptor.GetComponentName() { return ToString(); } TypeConverter ICustomTypeDescriptor.GetConverter() { return TypeDescriptor.GetConverter(GetType()); } EventDescriptor ICustomTypeDescriptor.GetDefaultEvent() { return TypeDescriptor.GetDefaultEvent(GetType()); } PropertyDescriptor ICustomTypeDescriptor.GetDefaultProperty() { return TypeDescriptor.GetDefaultProperty(GetType()); } object ICustomTypeDescriptor.GetEditor(Type editorBaseType) { return TypeDescriptor.GetEditor(GetType(), editorBaseType); } EventDescriptorCollection ICustomTypeDescriptor.GetEvents(Attribute[] attributes) { return TypeDescriptor.GetEvents(GetType(), attributes); } EventDescriptorCollection ICustomTypeDescriptor.GetEvents() { return TypeDescriptor.GetEvents(GetType()); } PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties(Attribute[] attributes) { return TypeDescriptor.GetProperties(GetType(), attributes); } PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties() { return TypeDescriptor.GetProperties(GetType()); } object ICustomTypeDescriptor.GetPropertyOwner(PropertyDescriptor pd) { return this; } #endregion } internal interface IHaveConnection { DbConnection GetConnection(); [Browsable(false)] ViewTableBase DesignTable { get; } } internal interface IHaveConnectionScope : IHaveConnection { [Browsable(false)] string CatalogScope { get; } [Browsable(false)] string TableScope { get; } } internal class CollationTypeEditor : ObjectSelectorEditor { public override bool IsDropDownResizable { get { return true; } } public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context) { return UITypeEditorEditStyle.DropDown; } protected override void FillTreeWithData(Selector selector, ITypeDescriptorContext context, IServiceProvider provider) { base.FillTreeWithData(selector, context, provider); selector.AddNode("BINARY", "BINARY", null); selector.AddNode("NOCASE", "NOCASE", null); } } internal class CatalogTypeEditor : ObjectSelectorEditor { public override bool IsDropDownResizable { get { return true; } } public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context) { return UITypeEditorEditStyle.DropDown; } protected override void FillTreeWithData(Selector selector, ITypeDescriptorContext context, IServiceProvider provider) { base.FillTreeWithData(selector, context, provider); IHaveConnection source = context.Instance as IHaveConnection; if (source == null) return; using (DataTable table = source.GetConnection().GetSchema("Catalogs")) { foreach (DataRow row in table.Rows) { selector.AddNode(row[0].ToString(), row[0], null); } } } } }