System.Data.SQLite: Check-in [38cd99987c]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	When emitting SQL for foreign keys in the VS designer, be sure to take all returned rows into account. Fix for [b226147b37].
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	38cd99987c29a163edf1419ce4aceab5eac8665a
User & Date:	mistachkin 2012-01-25 18:17:06

References

2012-01-25
18:17		• Closed ticket [b226147b37]: Designer deletes foreign key constraints plus 2 other changes artifact: 6dedf5cf1e user: mistachkin

Context

2012-01-25
18:24		Document recent changes in version history. check-in: db337c534c user: mistachkin tags: trunk
18:17		When emitting SQL for foreign keys in the VS designer, be sure to take all returned rows into account. Fix for [b226147b37]. check-in: 38cd99987c user: mistachkin tags: trunk
18:10		Remove unnecessary loop and fix indentation of foreign key SQL fragments. Closed-Leaf check-in: 1119da4703 user: mistachkin tags: VsDesignerFKey
17:37		Fix the default value of the connection flags for the setupDb unit test infrastructure script library procedure. check-in: 96c981c40b user: mistachkin tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to SQLite.Designer/Design/ForeignKey.cs.

    }
  }

  [DefaultProperty("From")]
  internal class ForeignKey : IHaveConnection, ICloneable
  {
    internal Table _table;


    internal ForeignKeyFromItem _from;
    internal ForeignKeyToItem _to;
    internal string _name;
    internal string _onUpdate;
    internal string _onDelete;
    internal string _match;
    private bool _dirty;

    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;
      _onUpdate = source._onUpdate;
      _onDelete = source._onDelete;
      _match = source._match;
      _dirty = source._dirty;
................................................................................
    }

    internal ForeignKey(DbConnection cnn, Table table, DataRow row)
    {
      _table = table;
      if (row != null)
      {


        _from = new ForeignKeyFromItem(this, row["FKEY_FROM_COLUMN"].ToString());
        _to = new ForeignKeyToItem(this, row["FKEY_TO_CATALOG"].ToString(), row["FKEY_TO_TABLE"].ToString(), row["FKEY_TO_COLUMN"].ToString());
        _name = row["CONSTRAINT_NAME"].ToString();
        _onUpdate = row["FKEY_ON_UPDATE"].ToString();
        _onDelete = row["FKEY_ON_DELETE"].ToString();
        _match = row["FKEY_MATCH"].ToString();
      }
      else
      {


        _from = new ForeignKeyFromItem(this, "");
        _to = new ForeignKeyToItem(this, _table.Catalog, "", "");
      }
    }

    //internal void WriteSql(StringBuilder builder)
    //{
................................................................................
    [Description("The name of the foreign key.")]
    public string Name
    {
      get
      {
        if (String.IsNullOrEmpty(_name) == false) return _name;

        return String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}_{3}", _from.Table, _from.Column, _to.Table, _to.Column);
      }
      set
      {
        if (_name != value)
        {
          _name = value;
          MakeDirty();
................................................................................

    public DbConnection GetConnection()
    {
      return ((IHaveConnection)_table).GetConnection();
    }

    #endregion

















    [DisplayName("From Key")]
    [Category("From")]
    [Description("The source column in the current table that refers to the foreign key.")]
    public ForeignKeyFromItem From
    {
      get { return _from; }








>
>











>
>







 







>
>









>
>







 







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

    }
  }

  [DefaultProperty("From")]
  internal class ForeignKey : IHaveConnection, ICloneable
  {
    internal Table _table;
    internal int _id;
    internal int _ordinal;
    internal ForeignKeyFromItem _from;
    internal ForeignKeyToItem _to;
    internal string _name;
    internal string _onUpdate;
    internal string _onDelete;
    internal string _match;
    private bool _dirty;

    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 ForeignKey(DbConnection cnn, Table table, DataRow row)
    {
      _table = table;
      if (row != null)
      {
        _id = (int)row["FKEY_ID"];
        _ordinal = (int)row["FKEY_FROM_ORDINAL_POSITION"];
        _from = new ForeignKeyFromItem(this, row["FKEY_FROM_COLUMN"].ToString());
        _to = new ForeignKeyToItem(this, row["FKEY_TO_CATALOG"].ToString(), row["FKEY_TO_TABLE"].ToString(), row["FKEY_TO_COLUMN"].ToString());
        _name = row["CONSTRAINT_NAME"].ToString();
        _onUpdate = row["FKEY_ON_UPDATE"].ToString();
        _onDelete = row["FKEY_ON_DELETE"].ToString();
        _match = row["FKEY_MATCH"].ToString();
      }
      else
      {
        _id = -1;
        _ordinal = -1;
        _from = new ForeignKeyFromItem(this, "");
        _to = new ForeignKeyToItem(this, _table.Catalog, "", "");
      }
    }

    //internal void WriteSql(StringBuilder builder)
    //{
................................................................................
    [Description("The name of the foreign key.")]
    public string Name
    {
      get
      {
        if (String.IsNullOrEmpty(_name) == false) return _name;

        return String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}", _from.Table, _id, _ordinal);
      }
      set
      {
        if (_name != value)
        {
          _name = value;
          MakeDirty();
................................................................................

    public DbConnection GetConnection()
    {
      return ((IHaveConnection)_table).GetConnection();
    }

    #endregion

    [DisplayName("Id")]
    [Category("Id")]
    [Description("The identifier of this foreign key.")]
    public int Id
    {
      get { return _id; }
    }

    [DisplayName("Ordinal")]
    [Category("Ordinal")]
    [Description("The column ordinal of this foreign key.")]
    public int Ordinal
    {
      get { return _ordinal; }
    }

    [DisplayName("From Key")]
    [Category("From")]
    [Description("The source column in the current table that refers to the foreign key.")]
    public ForeignKeyFromItem From
    {
      get { return _from; }

Changes to SQLite.Designer/Design/Table.cs.

            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)
      {
................................................................................
      }

      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(")");


      if (!String.IsNullOrEmpty(keys[0].Match))
          builder.AppendFormat(" MATCH {0}", keys[0].Match);

      if (!String.IsNullOrEmpty(keys[0].OnUpdate))
          builder.AppendFormat(" ON UPDATE {0}", keys[0].OnUpdate);

      if (!String.IsNullOrEmpty(keys[0].OnDelete))
          builder.AppendFormat(" ON DELETE {0}", keys[0].OnDelete);




    }

    [Browsable(false)]
    public override ViewTableBase DesignTable
    {
      get { return this; }
    }








<
<
<
<
<
<
<
<
<
<
|
|
<
<
<
<
<
|
|
<
<
<
<
<
<
<
<
<







 







>
>
>
>
>
>
>
>
|
<
|
<
>
|
>
>
|
<
>
>
|
>
>
|
|
|
<
<
>
|
>
>
>
>
>
|
<
>
>
|
>
>
>
|
|
>
|
|

|
|
|
|
|
>
>
>
>

            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)
      {
................................................................................
      }

      return builder.ToString();
    }

    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++;
        }
    }

    [Browsable(false)]
    public override ViewTableBase DesignTable
    {
      get { return this; }
    }

Changes to System.Data.SQLite/SQLiteConnection.cs.

      tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
      tbl.Columns.Add("TABLE_CATALOG", typeof(string));
      tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
      tbl.Columns.Add("TABLE_NAME", typeof(string));
      tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string));
      tbl.Columns.Add("IS_DEFERRABLE", typeof(bool));
      tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool));

      tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string));
      tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int));
      tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string));
      tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string));
      tbl.Columns.Add("FKEY_TO_TABLE", typeof(string));
      tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string));
      tbl.Columns.Add("FKEY_ON_UPDATE", typeof(string));
................................................................................
              using (SQLiteCommand cmdKey = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
              using (SQLiteDataReader rdKey = cmdKey.ExecuteReader())
              {
                while (rdKey.Read())
                {
                  row = tbl.NewRow();
                  row["CONSTRAINT_CATALOG"] = strCatalog;
                  row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}", rdTables[2], rdKey.GetInt32(0));
                  row["TABLE_CATALOG"] = strCatalog;
                  row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2));
                  row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
                  row["IS_DEFERRABLE"] = false;
                  row["INITIALLY_DEFERRED"] = false;

                  row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString());
                  row["FKEY_TO_CATALOG"] = strCatalog;
                  row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString());
                  row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString());
                  row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1];
                  row["FKEY_ON_UPDATE"] = (rdKey.FieldCount > 5) ? rdKey[5] : String.Empty;
                  row["FKEY_ON_DELETE"] = (rdKey.FieldCount > 6) ? rdKey[6] : String.Empty;

      tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
      tbl.Columns.Add("TABLE_CATALOG", typeof(string));
      tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
      tbl.Columns.Add("TABLE_NAME", typeof(string));
      tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string));
      tbl.Columns.Add("IS_DEFERRABLE", typeof(bool));
      tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool));
      tbl.Columns.Add("FKEY_ID", typeof(int));
      tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string));
      tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int));
      tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string));
      tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string));
      tbl.Columns.Add("FKEY_TO_TABLE", typeof(string));
      tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string));
      tbl.Columns.Add("FKEY_ON_UPDATE", typeof(string));
................................................................................
              using (SQLiteCommand cmdKey = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
              using (SQLiteDataReader rdKey = cmdKey.ExecuteReader())
              {
                while (rdKey.Read())
                {
                  row = tbl.NewRow();
                  row["CONSTRAINT_CATALOG"] = strCatalog;
                  row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}", rdTables[2], rdKey.GetInt32(0), rdKey.GetInt32(1));
                  row["TABLE_CATALOG"] = strCatalog;
                  row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2));
                  row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
                  row["IS_DEFERRABLE"] = false;
                  row["INITIALLY_DEFERRED"] = false;
                  row["FKEY_ID"] = rdKey[0];
                  row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString());
                  row["FKEY_TO_CATALOG"] = strCatalog;
                  row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString());
                  row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString());
                  row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1];
                  row["FKEY_ON_UPDATE"] = (rdKey.FieldCount > 5) ? rdKey[5] : String.Empty;
                  row["FKEY_ON_DELETE"] = (rdKey.FieldCount > 6) ? rdKey[6] : String.Empty;

Changes to Tests/basic.eagle.

              [$foreignKey Item CONSTRAINT_CATALOG] \
              [$foreignKey Item CONSTRAINT_NAME] \
              [$foreignKey Item TABLE_CATALOG] \
              [$foreignKey Item TABLE_NAME] \
              [$foreignKey Item CONSTRAINT_TYPE] \
              [$foreignKey Item IS_DEFERRABLE] \
              [$foreignKey Item INITIALLY_DEFERRED] \

              [$foreignKey Item FKEY_FROM_COLUMN] \
              [$foreignKey Item FKEY_TO_CATALOG] \
              [$foreignKey Item FKEY_TO_TABLE] \
              [$foreignKey Item FKEY_TO_COLUMN] \
              [$foreignKey Item FKEY_FROM_ORDINAL_POSITION] \
              [$foreignKey Item FKEY_ON_UPDATE] \
              [$foreignKey Item FKEY_ON_DELETE] \
................................................................................
  cleanupDb $fileName

  unset -nocomplain result rows foreignKey foreignKeys results errors code \
      dataSource id db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
-match regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0\
\{\{main FK_t1_0 main t1 \{FOREIGN KEY\} False False x main t2 \{\} 0 \{SET\
DEFAULT\} CASCADE NONE\} \{main FK_t2_0 main t2 \{FOREIGN KEY\} False False x\
main t3 \{\} 0 \{NO ACTION\} \{NO ACTION\} NONE\}\}$}}

###############################################################################

runTest {test data-1.6 {SQLITE_FCNTL_WIN32_AV_RETRY} -setup {
  setupDb [set fileName data-1.6.db]
} -body {
  set id [object invoke Interpreter.GetActive NextId]

              [$foreignKey Item CONSTRAINT_CATALOG] \
              [$foreignKey Item CONSTRAINT_NAME] \
              [$foreignKey Item TABLE_CATALOG] \
              [$foreignKey Item TABLE_NAME] \
              [$foreignKey Item CONSTRAINT_TYPE] \
              [$foreignKey Item IS_DEFERRABLE] \
              [$foreignKey Item INITIALLY_DEFERRED] \
              [$foreignKey Item FKEY_ID] \
              [$foreignKey Item FKEY_FROM_COLUMN] \
              [$foreignKey Item FKEY_TO_CATALOG] \
              [$foreignKey Item FKEY_TO_TABLE] \
              [$foreignKey Item FKEY_TO_COLUMN] \
              [$foreignKey Item FKEY_FROM_ORDINAL_POSITION] \
              [$foreignKey Item FKEY_ON_UPDATE] \
              [$foreignKey Item FKEY_ON_DELETE] \
................................................................................
  cleanupDb $fileName

  unset -nocomplain result rows foreignKey foreignKeys results errors code \
      dataSource id db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
-match regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0\
\{\{main FK_t1_0_0 main t1 \{FOREIGN KEY\} False False 0 x main t2 \{\} 0 \{SET\
DEFAULT\} CASCADE NONE\} \{main FK_t2_0_0 main t2 \{FOREIGN KEY\} False False 0\
x main t3 \{\} 0 \{NO ACTION\} \{NO ACTION\} NONE\}\}$}}

###############################################################################

runTest {test data-1.6 {SQLITE_FCNTL_WIN32_AV_RETRY} -setup {
  setupDb [set fileName data-1.6.db]
} -body {
  set id [object invoke Interpreter.GetActive NextId]