System.Data.SQLite
Artifact Content
Not logged in

Artifact 754bfd5ccf0269cdd0e55b9aaea01b50f5f6e04b:


/********************************************************
 * 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 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<Column> _columns = new List<Column>();
    private bool _exists = false;
    private string _origSql = String.Empty;
    private List<Index> _indexes = new List<Index>();
    private List<Index> _oldindexes = new List<Index>();
    private List<ForeignKey> _fkeys = new List<ForeignKey>();
    private List<ForeignKey> _oldfkeys = new List<ForeignKey>();
    private List<string> _check = new List<string>();
    private List<Trigger> _triggers = new List<Trigger>();
    private List<Trigger> _oldtriggers = new List<Trigger>();
    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(';');
        }
        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<Index> Indexes
    {
      get { return _indexes; }
    }

    [Browsable(false)]
    public PrimaryKey PrimaryKey
    {
      get { return _key; }
      set
      {
        _key = value;
        _owner.Invalidate();
      }
    }

    [Browsable(false)]
    public List<ForeignKey> ForeignKeys
    {
      get { return _fkeys; }
    }

    [Browsable(false)]
    public List<string> 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("[{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<Column> Columns
    {
      get { return _columns; }
    }

    public string GetSql()
    {
      StringBuilder builder = new StringBuilder();
      string altName = null;

      if (_exists)
      {
        Guid g = Guid.NewGuid();
        altName = String.Format("{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("{0}CONSTRAINT [PK_{1}] PRIMARY KEY (", separator, Name);
        foreach (IndexColumn c in _key.Columns)
        {
          builder.AppendFormat("{0}[{1}]", innersep, c.Column);
          if (String.IsNullOrEmpty(c.Collate) == false && String.Compare(c.Collate, "BINARY", StringComparison.OrdinalIgnoreCase) != 0)
            builder.AppendFormat(" COLLATE {0}", c.Collate.ToUpperInvariant());

          if (c.SortMode != ColumnSortMode.Ascending)
            builder.AppendFormat(" DESC");

          innersep = ", ";
        }
        builder.Append(")");

        if (_key.Conflict != ConflictEnum.Abort)
          builder.AppendFormat(" 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("({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 void WriteFKeys(List<ForeignKey> keys, StringBuilder builder)
    {
      builder.AppendFormat("CONSTRAINT [{0}] FOREIGN KEY (", keys[0].Name);
      string separator = "";

      foreach (ForeignKey key in keys)
      {
        builder.AppendFormat("{0}[{1}]", separator, key.From.Column);
        separator = ", ";
      }

      builder.AppendFormat(") REFERENCES [{0}] (", keys[0].To.Table);

      separator = "";
      foreach (ForeignKey key in keys)
      {
        builder.AppendFormat("{0}[{1}]", separator, key.To.Column);
        separator = ", ";
      }
      builder.Append(")");
    }

    [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 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 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);
        }
      }
    }
  }
}