System.Data.SQLite
Check-in [9eb2e81611]
Not logged in

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

Overview
Comment:Support the ON UPDATE, ON DELETE, and MATCH clause information when generating schema metadata for foreign keys. Partial fix for [b226147b37]. VS designer changes are not yet tested.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9eb2e8161179d718d237c50a1d5722293688d192
User & Date: mistachkin 2011-07-10 10:39:56
References
2011-07-10
10:45 Pending ticket [b226147b37]: Designer deletes foreign key constraints plus 1 other change artifact: 1292caf419 user: mistachkin
Context
2011-07-10
13:42
Make build commands easier to read. check-in: 2aac150c82 user: mistachkin tags: trunk
10:39
Support the ON UPDATE, ON DELETE, and MATCH clause information when generating schema metadata for foreign keys. Partial fix for [b226147b37]. VS designer changes are not yet tested. check-in: 9eb2e81611 user: mistachkin tags: trunk
01:48
Updates to unit test infrastructure. check-in: 1f8786cc73 user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

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

283
284
285
286
287
288
289



290
291
292
293
294
295
296
297



298
299
300
301
302
303
304
...
318
319
320
321
322
323
324



325
326
327
328
329
330
331
...
386
387
388
389
390
391
392
























393
394
395
396
397
398
399
400
401
402
403
  [DefaultProperty("From")]
  internal class ForeignKey : IHaveConnection, ICloneable
  {
    internal Table _table;
    internal ForeignKeyFromItem _from;
    internal ForeignKeyToItem _to;
    internal string _name;



    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;



      _dirty = source._dirty;
    }

    internal void MakeDirty()
    {
      _dirty = true;
    }
................................................................................
    {
      _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();



      }
      else
      {
        _from = new ForeignKeyFromItem(this, "");
        _to = new ForeignKeyToItem(this, _table.Catalog, "", "");
      }
    }
................................................................................
    [DisplayName("To Key")]
    [Category("To")]
    [Description("The table and column to which the specified from column is related.")]
    public ForeignKeyToItem To
    {
      get { return _to; }
    }

























    #region ICloneable Members

    public object Clone()
    {
      return new ForeignKey(this);
    }

    #endregion
  }
}







>
>
>








>
>
>







 







>
>
>







 







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











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
309
310
...
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
...
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
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
  [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 void MakeDirty()
    {
      _dirty = true;
    }
................................................................................
    {
      _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, "", "");
      }
    }
................................................................................
    [DisplayName("To Key")]
    [Category("To")]
    [Description("The table and column to which the specified from column is related.")]
    public ForeignKeyToItem To
    {
      get { return _to; }
    }

    [DisplayName("On Update")]
    [Category("Action")]
    [Description("The action to take when modifying the parent key values of an existing row.")]
    public string OnUpdate
    {
        get { return _onUpdate; }
    }

    [DisplayName("On Delete")]
    [Category("Action")]
    [Description("The action to take when deleting a row from the parent table.")]
    public string OnDelete
    {
        get { return _onDelete; }
    }

    [DisplayName("Match")]
    [Category("Match")]
    [Description("Used with composite foreign key definitions to modify the way NULL values that occur in child keys are handled.  Not currently supported.")]
    public string Match
    {
        get { return _match; }
    }

    #region ICloneable Members

    public object Clone()
    {
      return new ForeignKey(this);
    }

    #endregion
  }
}

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

514
515
516
517
518
519
520









521
522
523
524
525
526
527
      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; }
    }







>
>
>
>
>
>
>
>
>







514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
      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; }
    }

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

2150
2151
2152
2153
2154
2155
2156



2157
2158
2159
2160
2161
2162
2163
....
2187
2188
2189
2190
2191
2192
2193



2194
2195
2196
2197
2198
2199
2200
      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));




      if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";

      string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb;

      tbl.BeginLoadData();

................................................................................
                  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];




                  if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), StringComparison.OrdinalIgnoreCase) == 0)
                    tbl.Rows.Add(row);
                }
              }
            }
            catch (SQLiteException)







>
>
>







 







>
>
>







2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
....
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
      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));
      tbl.Columns.Add("FKEY_ON_DELETE", typeof(string));
      tbl.Columns.Add("FKEY_MATCH", typeof(string));

      if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";

      string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb;

      tbl.BeginLoadData();

................................................................................
                  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;
                  row["FKEY_MATCH"] = (rdKey.FieldCount > 7) ? rdKey[7] : String.Empty;

                  if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), StringComparison.OrdinalIgnoreCase) == 0)
                    tbl.Rows.Add(row);
                }
              }
            }
            catch (SQLiteException)

Changes to Tests/basic.eagle.

134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
...
177
178
179
180
181
182
183
184
185
186





















































































187
188
189
190
191
192

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

runTest {test basic-1.4 {GetSchema with ReservedWords} -setup {
  setupDb [set fileName basic-1.4.db]
} -body {
  set id [object invoke Interpreter.GetActive NextId]

  set dataSource [file join [getTemporaryPath] basic-1.4.db]

  unset -nocomplain results errors

  set code [compileCSharpWith [subst {
    using System.Data;
    using System.Data.SQLite;
................................................................................
      } result] : [set result ""]}] $result
} -cleanup {
  cleanupDb $fileName
  unset -nocomplain result results errors code dataSource id db fileName
} -constraints {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} \
-match regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0\
System#Data#DataTable#\d+$}}

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






















































































unset -nocomplain testExeFile testLinqExeFile northwindEfDbFile testLinqOutFile

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

runSQLiteTestEpilogue
runTestEpilogue







<







 










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






134
135
136
137
138
139
140

141
142
143
144
145
146
147
...
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276

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

runTest {test basic-1.4 {GetSchema with ReservedWords} -setup {
  setupDb [set fileName basic-1.4.db]
} -body {
  set id [object invoke Interpreter.GetActive NextId]

  set dataSource [file join [getTemporaryPath] basic-1.4.db]

  unset -nocomplain results errors

  set code [compileCSharpWith [subst {
    using System.Data;
    using System.Data.SQLite;
................................................................................
      } result] : [set result ""]}] $result
} -cleanup {
  cleanupDb $fileName
  unset -nocomplain result results errors code dataSource id db fileName
} -constraints {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} \
-match regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0\
System#Data#DataTable#\d+$}}

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

runTest {test basic-1.5 {GetSchema with ForeignKeys} -setup {
  setupDb [set fileName basic-1.5.db]
} -body {
  sql execute $db {
    CREATE TABLE t1(
      x INTEGER REFERENCES t2 MATCH FULL
      ON UPDATE SET DEFAULT ON DELETE CASCADE
      DEFAULT 1
    );
  }

  sql execute $db "CREATE TABLE t2(x INTEGER REFERENCES t3);"

  set id [object invoke Interpreter.GetActive NextId]
  set dataSource [file join [getTemporaryPath] basic-1.5.db]

  unset -nocomplain results errors

  set code [compileCSharpWith [subst {
    using System.Data;
    using System.Data.SQLite;

    namespace _Dynamic${id}
    {
      public class Test${id}
      {
        public static DataRowCollection GetForeignKeys()
        {
          using (SQLiteConnection connection = new SQLiteConnection(
              "Data Source=${dataSource};"))
          {
            connection.Open();

            return connection.GetSchema("ForeignKeys").Rows;
          }
        }

        public static void Main()
        {
          // do nothing.
        }
      }
    }
  }] results errors System.Data.SQLite.dll]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        set rows [list]
        set foreignKeys [object invoke _Dynamic${id}.Test${id} GetForeignKeys]

        object foreach -alias foreignKey $foreignKeys {
          lappend rows [list \
              [$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] \
              [$foreignKey Item FKEY_MATCH]]
        }

        set rows
      } result] : [set result ""]}] $result
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result rows foreignKey foreignKeys results errors code \
      dataSource id db fileName
} -constraints {eagle monoBug28 command.sql compile.DATA 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\}\}$}}

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

unset -nocomplain testExeFile testLinqExeFile northwindEfDbFile testLinqOutFile

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

runSQLiteTestEpilogue
runTestEpilogue

Changes to Tests/common.eagle.

120
121
122
123
124
125
126
















127
128
129
130
131
132
133
      # NOTE: Return the full path of the loaded file.
      #
      return $fileName
    }

    return ""
  }
















 
  proc compileCSharpWith { text resultsVarName errorsVarName fileNames args } {
    #
    # NOTE: Create the base command to evaluate and add the property settings
    #       that are almost always needed by our unit tests (i.e. the System
    #       and System.Data assembly references).
    #







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







120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
      # NOTE: Return the full path of the loaded file.
      #
      return $fileName
    }

    return ""
  }
 
  proc enumerableToString { enumerable } {
    set result [list]

    if {[string length $enumerable] == 0 || $enumerable eq "null"} then {
      return $result
    }

    object foreach -alias item $enumerable {
      if {[string length $item] > 0} then {
        lappend result [$item ToString]
      }
    }

    return $result
  }
 
  proc compileCSharpWith { text resultsVarName errorsVarName fileNames args } {
    #
    # NOTE: Create the base command to evaluate and add the property settings
    #       that are almost always needed by our unit tests (i.e. the System
    #       and System.Data assembly references).
    #