System.Data.SQLite
Check-in [e67a436c40]
Not logged in

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

Overview
Comment:More work on fixing ticket [b226147b37].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | VsDesignerFKey
Files: files | file ages | folders
SHA1: e67a436c40c98dfa8f0d06736ba158292617d466
User & Date: mistachkin 2012-01-25 13:37:32
Context
2012-01-25
18:10
Remove unnecessary loop and fix indentation of foreign key SQL fragments. Closed-Leaf check-in: 1119da4703 user: mistachkin tags: VsDesignerFKey
13:37
More work on fixing ticket [b226147b37]. check-in: e67a436c40 user: mistachkin tags: VsDesignerFKey
09:27
Update MSVC redists in Externals to the latest ones from Microsoft. check-in: bbec0d9d38 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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

   280    280       }
   281    281     }
   282    282   
   283    283     [DefaultProperty("From")]
   284    284     internal class ForeignKey : IHaveConnection, ICloneable
   285    285     {
   286    286       internal Table _table;
          287  +    internal int _id;
          288  +    internal int _ordinal;
   287    289       internal ForeignKeyFromItem _from;
   288    290       internal ForeignKeyToItem _to;
   289    291       internal string _name;
   290    292       internal string _onUpdate;
   291    293       internal string _onDelete;
   292    294       internal string _match;
   293    295       private bool _dirty;
   294    296   
   295    297       private ForeignKey(ForeignKey source)
   296    298       {
   297    299         _table = source._table;
          300  +      _id = source._id;
          301  +      _ordinal = source._ordinal;
   298    302         _from = new ForeignKeyFromItem(this, source._from.Column);
   299    303         _to = new ForeignKeyToItem(this, source._to.Catalog, source._to.Table, source._to.Column);
   300    304         _name = source._name;
   301    305         _onUpdate = source._onUpdate;
   302    306         _onDelete = source._onDelete;
   303    307         _match = source._match;
   304    308         _dirty = source._dirty;
................................................................................
   321    325       }
   322    326   
   323    327       internal ForeignKey(DbConnection cnn, Table table, DataRow row)
   324    328       {
   325    329         _table = table;
   326    330         if (row != null)
   327    331         {
          332  +        _id = (int)row["FKEY_ID"];
          333  +        _ordinal = (int)row["FKEY_FROM_ORDINAL_POSITION"];
   328    334           _from = new ForeignKeyFromItem(this, row["FKEY_FROM_COLUMN"].ToString());
   329    335           _to = new ForeignKeyToItem(this, row["FKEY_TO_CATALOG"].ToString(), row["FKEY_TO_TABLE"].ToString(), row["FKEY_TO_COLUMN"].ToString());
   330    336           _name = row["CONSTRAINT_NAME"].ToString();
   331    337           _onUpdate = row["FKEY_ON_UPDATE"].ToString();
   332    338           _onDelete = row["FKEY_ON_DELETE"].ToString();
   333    339           _match = row["FKEY_MATCH"].ToString();
   334    340         }
   335    341         else
   336    342         {
          343  +        _id = -1;
          344  +        _ordinal = -1;
   337    345           _from = new ForeignKeyFromItem(this, "");
   338    346           _to = new ForeignKeyToItem(this, _table.Catalog, "", "");
   339    347         }
   340    348       }
   341    349   
   342    350       //internal void WriteSql(StringBuilder builder)
   343    351       //{
................................................................................
   353    361       [Description("The name of the foreign key.")]
   354    362       public string Name
   355    363       {
   356    364         get
   357    365         {
   358    366           if (String.IsNullOrEmpty(_name) == false) return _name;
   359    367   
   360         -        return String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}_{3}", _from.Table, _from.Column, _to.Table, _to.Column);
          368  +        return String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}", _from.Table, _id, _ordinal);
   361    369         }
   362    370         set
   363    371         {
   364    372           if (_name != value)
   365    373           {
   366    374             _name = value;
   367    375             MakeDirty();
................................................................................
   379    387   
   380    388       public DbConnection GetConnection()
   381    389       {
   382    390         return ((IHaveConnection)_table).GetConnection();
   383    391       }
   384    392   
   385    393       #endregion
          394  +
          395  +    [DisplayName("Id")]
          396  +    [Category("Id")]
          397  +    [Description("The identifier of this foreign key.")]
          398  +    public int Id
          399  +    {
          400  +      get { return _id; }
          401  +    }
          402  +
          403  +    [DisplayName("Ordinal")]
          404  +    [Category("Ordinal")]
          405  +    [Description("The column ordinal of this foreign key.")]
          406  +    public int Ordinal
          407  +    {
          408  +      get { return _ordinal; }
          409  +    }
   386    410   
   387    411       [DisplayName("From Key")]
   388    412       [Category("From")]
   389    413       [Description("The source column in the current table that refers to the foreign key.")]
   390    414       public ForeignKeyFromItem From
   391    415       {
   392    416         get { return _from; }

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

   410    410         }
   411    411   
   412    412         List<ForeignKey> keys = new List<ForeignKey>();
   413    413   
   414    414         for (int x = 0; x < ForeignKeys.Count; x++)
   415    415         {
   416    416           ForeignKey key = ForeignKeys[x];
   417         -
   418         -        if (String.IsNullOrEmpty(key.From.Column) == true || String.IsNullOrEmpty(key.From.Catalog) == true ||
   419         -          String.IsNullOrEmpty(key.To.Table) == true || String.IsNullOrEmpty(key.To.Column) == true)
   420         -          continue;
   421         -
   422         -        if (keys.Count > 0)
   423         -        {
   424         -          if (keys[0].Name == key.Name && keys[0].To.Catalog == key.To.Catalog && keys[0].To.Table == key.To.Table)
   425         -          {
   426         -            keys.Add(key);
   427         -            continue;
   428         -          }
   429         -          builder.Append(separator);
   430         -          WriteFKeys(keys, builder);
   431         -          keys.Clear();
   432         -        }
   433    417           keys.Add(key);
   434    418         }
   435    419   
   436    420         if (keys.Count > 0)
   437    421         {
   438    422           builder.Append(separator);
   439    423           WriteFKeys(keys, builder);
................................................................................
   496    480         }
   497    481   
   498    482         return builder.ToString();
   499    483       }
   500    484   
   501    485       private void WriteFKeys(List<ForeignKey> keys, StringBuilder builder)
   502    486       {
   503         -      builder.AppendFormat("CONSTRAINT [{0}] FOREIGN KEY (", keys[0].Name);
   504         -      string separator = "";
   505         -
   506         -      foreach (ForeignKey key in keys)
   507         -      {
   508         -        builder.AppendFormat("{0}[{1}]", separator, key.From.Column);
   509         -        separator = ", ";
   510         -      }
   511         -
   512         -      builder.AppendFormat(") REFERENCES [{0}] (", keys[0].To.Table);
   513         -
   514         -      separator = "";
   515         -      foreach (ForeignKey key in keys)
   516         -      {
   517         -        builder.AppendFormat("{0}[{1}]", separator, key.To.Column);
   518         -        separator = ", ";
   519         -      }
   520         -      builder.Append(")");
   521         -
   522         -      if (!String.IsNullOrEmpty(keys[0].Match))
   523         -          builder.AppendFormat(" MATCH {0}", keys[0].Match);
   524         -
   525         -      if (!String.IsNullOrEmpty(keys[0].OnUpdate))
   526         -          builder.AppendFormat(" ON UPDATE {0}", keys[0].OnUpdate);
   527         -
   528         -      if (!String.IsNullOrEmpty(keys[0].OnDelete))
   529         -          builder.AppendFormat(" ON DELETE {0}", keys[0].OnDelete);
          487  +        for (int index = 0; index < keys.Count; )
          488  +        {
          489  +            ForeignKey key = keys[index];
          490  +
          491  +            if (index > 0)
          492  +                builder.Append(",\r\n");
          493  +
          494  +            builder.AppendFormat(
          495  +                "CONSTRAINT [{0}] FOREIGN KEY (", key.Name);
          496  +
          497  +            int startIndex = index;
          498  +
          499  +            do
          500  +            {
          501  +                builder.AppendFormat("{0}[{1}]",
          502  +                    index > startIndex ? ", " : String.Empty,
          503  +                    keys[index].From.Column);
          504  +
          505  +                index++;
          506  +            } while (index < keys.Count && keys[index].Id == key.Id);
          507  +
          508  +            builder.AppendFormat(") REFERENCES [{0}]", key.To.Table);
          509  +
          510  +            if (!String.IsNullOrEmpty(key.To.Column))
          511  +            {
          512  +                builder.Append(" (");
          513  +                index = startIndex;
          514  +
          515  +                do
          516  +                {
          517  +                    builder.AppendFormat("{0}[{1}]",
          518  +                        index > startIndex ? ", " : String.Empty,
          519  +                        keys[index].To.Column);
          520  +
          521  +                    index++;
          522  +                } while (index < keys.Count && keys[index].Id == key.Id);
          523  +
          524  +                builder.Append(')');
          525  +            }
          526  +
          527  +            if (!String.IsNullOrEmpty(key.Match))
          528  +                builder.AppendFormat(" MATCH {0}", key.Match);
          529  +
          530  +            if (!String.IsNullOrEmpty(key.OnUpdate))
          531  +                builder.AppendFormat(" ON UPDATE {0}", key.OnUpdate);
          532  +
          533  +            if (!String.IsNullOrEmpty(key.OnDelete))
          534  +                builder.AppendFormat(" ON DELETE {0}", key.OnDelete);
          535  +
          536  +            if (index == startIndex)
          537  +                index++;
          538  +        }
   530    539       }
   531    540   
   532    541       [Browsable(false)]
   533    542       public override ViewTableBase DesignTable
   534    543       {
   535    544         get { return this; }
   536    545       }

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

  2346   2346         tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
  2347   2347         tbl.Columns.Add("TABLE_CATALOG", typeof(string));
  2348   2348         tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
  2349   2349         tbl.Columns.Add("TABLE_NAME", typeof(string));
  2350   2350         tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string));
  2351   2351         tbl.Columns.Add("IS_DEFERRABLE", typeof(bool));
  2352   2352         tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool));
         2353  +      tbl.Columns.Add("FKEY_ID", typeof(int));
  2353   2354         tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string));
  2354   2355         tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int));
  2355   2356         tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string));
  2356   2357         tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string));
  2357   2358         tbl.Columns.Add("FKEY_TO_TABLE", typeof(string));
  2358   2359         tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string));
  2359   2360         tbl.Columns.Add("FKEY_ON_UPDATE", typeof(string));
................................................................................
  2381   2382                 using (SQLiteCommand cmdKey = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
  2382   2383                 using (SQLiteDataReader rdKey = cmdKey.ExecuteReader())
  2383   2384                 {
  2384   2385                   while (rdKey.Read())
  2385   2386                   {
  2386   2387                     row = tbl.NewRow();
  2387   2388                     row["CONSTRAINT_CATALOG"] = strCatalog;
  2388         -                  row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}", rdTables[2], rdKey.GetInt32(0));
         2389  +                  row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}", rdTables[2], rdKey.GetInt32(0), rdKey.GetInt32(1));
  2389   2390                     row["TABLE_CATALOG"] = strCatalog;
  2390   2391                     row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2));
  2391   2392                     row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
  2392   2393                     row["IS_DEFERRABLE"] = false;
  2393   2394                     row["INITIALLY_DEFERRED"] = false;
         2395  +                  row["FKEY_ID"] = rdKey[0];
  2394   2396                     row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString());
  2395   2397                     row["FKEY_TO_CATALOG"] = strCatalog;
  2396   2398                     row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString());
  2397   2399                     row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString());
  2398   2400                     row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1];
  2399   2401                     row["FKEY_ON_UPDATE"] = (rdKey.FieldCount > 5) ? rdKey[5] : String.Empty;
  2400   2402                     row["FKEY_ON_DELETE"] = (rdKey.FieldCount > 6) ? rdKey[6] : String.Empty;

Changes to Tests/basic.eagle.

   257    257                 [$foreignKey Item CONSTRAINT_CATALOG] \
   258    258                 [$foreignKey Item CONSTRAINT_NAME] \
   259    259                 [$foreignKey Item TABLE_CATALOG] \
   260    260                 [$foreignKey Item TABLE_NAME] \
   261    261                 [$foreignKey Item CONSTRAINT_TYPE] \
   262    262                 [$foreignKey Item IS_DEFERRABLE] \
   263    263                 [$foreignKey Item INITIALLY_DEFERRED] \
          264  +              [$foreignKey Item FKEY_ID] \
   264    265                 [$foreignKey Item FKEY_FROM_COLUMN] \
   265    266                 [$foreignKey Item FKEY_TO_CATALOG] \
   266    267                 [$foreignKey Item FKEY_TO_TABLE] \
   267    268                 [$foreignKey Item FKEY_TO_COLUMN] \
   268    269                 [$foreignKey Item FKEY_FROM_ORDINAL_POSITION] \
   269    270                 [$foreignKey Item FKEY_ON_UPDATE] \
   270    271                 [$foreignKey Item FKEY_ON_DELETE] \
................................................................................
   277    278     cleanupDb $fileName
   278    279   
   279    280     unset -nocomplain result rows foreignKey foreignKeys results errors code \
   280    281         dataSource id db fileName
   281    282   } -constraints \
   282    283   {eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} \
   283    284   -match regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0\
   284         -\{\{main FK_t1_0 main t1 \{FOREIGN KEY\} False False x main t2 \{\} 0 \{SET\
   285         -DEFAULT\} CASCADE NONE\} \{main FK_t2_0 main t2 \{FOREIGN KEY\} False False x\
   286         -main t3 \{\} 0 \{NO ACTION\} \{NO ACTION\} NONE\}\}$}}
          285  +\{\{main FK_t1_0_0 main t1 \{FOREIGN KEY\} False False 0 x main t2 \{\} 0 \{SET\
          286  +DEFAULT\} CASCADE NONE\} \{main FK_t2_0_0 main t2 \{FOREIGN KEY\} False False 0\
          287  +x main t3 \{\} 0 \{NO ACTION\} \{NO ACTION\} NONE\}\}$}}
   287    288   
   288    289   ###############################################################################
   289    290   
   290    291   runTest {test data-1.6 {SQLITE_FCNTL_WIN32_AV_RETRY} -setup {
   291    292     setupDb [set fileName data-1.6.db]
   292    293   } -body {
   293    294     set id [object invoke Interpreter.GetActive NextId]