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 Unified Diffs Show Whitespace Changes Patch

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

280
281
282
283
284
285
286


287
288
289
290
291
292
293
294
295
296
297


298
299
300
301
302
303
304
...
321
322
323
324
325
326
327


328
329
330
331
332
333
334
335
336


337
338
339
340
341
342
343
...
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
...
379
380
381
382
383
384
385
















386
387
388
389
390
391
392
    }
  }

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







>
>











>
>







 







>
>









>
>







 







|







 







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







280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
...
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
...
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
...
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
    }
  }

  [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.

410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
...
496
497
498
499
500
501
502








503
504
505
506

507


508
509


510


511
512
513
514
515

516





517
518


519



520
521

522
523
524
525
526
527
528
529




530
531
532
533
534
535
536
      }

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

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







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







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

|
|
|
|
|
>
>
>
>







410
411
412
413
414
415
416
















417
418
419
420
421
422
423
...
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495

496

497
498
499
500
501

502
503
504
505
506
507
508
509


510
511
512
513
514
515
516
517

518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
      }

      List<ForeignKey> keys = new List<ForeignKey>();

      for (int x = 0; x < ForeignKeys.Count; x++)
      {
        ForeignKey key = ForeignKeys[x];
















        keys.Add(key);
      }

      if (keys.Count > 0)
      {
        builder.Append(separator);
        WriteFKeys(keys, builder);
................................................................................
      }

      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.

2346
2347
2348
2349
2350
2351
2352

2353
2354
2355
2356
2357
2358
2359
....
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393

2394
2395
2396
2397
2398
2399
2400
      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;







>







 







|





>







2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
....
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
      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.

257
258
259
260
261
262
263

264
265
266
267
268
269
270
...
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
              [$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]







>







 







|
|
|







257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
              [$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]