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 Side-by-Side Diffs Ignore Whitespace Patch

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

   283    283     [DefaultProperty("From")]
   284    284     internal class ForeignKey : IHaveConnection, ICloneable
   285    285     {
   286    286       internal Table _table;
   287    287       internal ForeignKeyFromItem _from;
   288    288       internal ForeignKeyToItem _to;
   289    289       internal string _name;
          290  +    internal string _onUpdate;
          291  +    internal string _onDelete;
          292  +    internal string _match;
   290    293       private bool _dirty;
   291    294   
   292    295       private ForeignKey(ForeignKey source)
   293    296       {
   294    297         _table = source._table;
   295    298         _from = new ForeignKeyFromItem(this, source._from.Column);
   296    299         _to = new ForeignKeyToItem(this, source._to.Catalog, source._to.Table, source._to.Column);
   297    300         _name = source._name;
          301  +      _onUpdate = source._onUpdate;
          302  +      _onDelete = source._onDelete;
          303  +      _match = source._match;
   298    304         _dirty = source._dirty;
   299    305       }
   300    306   
   301    307       internal void MakeDirty()
   302    308       {
   303    309         _dirty = true;
   304    310       }
................................................................................
   318    324       {
   319    325         _table = table;
   320    326         if (row != null)
   321    327         {
   322    328           _from = new ForeignKeyFromItem(this, row["FKEY_FROM_COLUMN"].ToString());
   323    329           _to = new ForeignKeyToItem(this, row["FKEY_TO_CATALOG"].ToString(), row["FKEY_TO_TABLE"].ToString(), row["FKEY_TO_COLUMN"].ToString());
   324    330           _name = row["CONSTRAINT_NAME"].ToString();
          331  +        _onUpdate = row["FKEY_ON_UPDATE"].ToString();
          332  +        _onDelete = row["FKEY_ON_DELETE"].ToString();
          333  +        _match = row["FKEY_MATCH"].ToString();
   325    334         }
   326    335         else
   327    336         {
   328    337           _from = new ForeignKeyFromItem(this, "");
   329    338           _to = new ForeignKeyToItem(this, _table.Catalog, "", "");
   330    339         }
   331    340       }
................................................................................
   386    395       [DisplayName("To Key")]
   387    396       [Category("To")]
   388    397       [Description("The table and column to which the specified from column is related.")]
   389    398       public ForeignKeyToItem To
   390    399       {
   391    400         get { return _to; }
   392    401       }
          402  +
          403  +    [DisplayName("On Update")]
          404  +    [Category("Action")]
          405  +    [Description("The action to take when modifying the parent key values of an existing row.")]
          406  +    public string OnUpdate
          407  +    {
          408  +        get { return _onUpdate; }
          409  +    }
          410  +
          411  +    [DisplayName("On Delete")]
          412  +    [Category("Action")]
          413  +    [Description("The action to take when deleting a row from the parent table.")]
          414  +    public string OnDelete
          415  +    {
          416  +        get { return _onDelete; }
          417  +    }
          418  +
          419  +    [DisplayName("Match")]
          420  +    [Category("Match")]
          421  +    [Description("Used with composite foreign key definitions to modify the way NULL values that occur in child keys are handled.  Not currently supported.")]
          422  +    public string Match
          423  +    {
          424  +        get { return _match; }
          425  +    }
   393    426   
   394    427       #region ICloneable Members
   395    428   
   396    429       public object Clone()
   397    430       {
   398    431         return new ForeignKey(this);
   399    432       }
   400    433   
   401    434       #endregion
   402    435     }
   403    436   }

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

   514    514         separator = "";
   515    515         foreach (ForeignKey key in keys)
   516    516         {
   517    517           builder.AppendFormat("{0}[{1}]", separator, key.To.Column);
   518    518           separator = ", ";
   519    519         }
   520    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);
   521    530       }
   522    531   
   523    532       [Browsable(false)]
   524    533       public override ViewTableBase DesignTable
   525    534       {
   526    535         get { return this; }
   527    536       }

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

  2150   2150         tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool));
  2151   2151         tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string));
  2152   2152         tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int));
  2153   2153         tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string));
  2154   2154         tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string));
  2155   2155         tbl.Columns.Add("FKEY_TO_TABLE", typeof(string));
  2156   2156         tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string));
         2157  +      tbl.Columns.Add("FKEY_ON_UPDATE", typeof(string));
         2158  +      tbl.Columns.Add("FKEY_ON_DELETE", typeof(string));
         2159  +      tbl.Columns.Add("FKEY_MATCH", typeof(string));
  2157   2160   
  2158   2161         if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
  2159   2162   
  2160   2163         string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb;
  2161   2164   
  2162   2165         tbl.BeginLoadData();
  2163   2166   
................................................................................
  2187   2190                     row["IS_DEFERRABLE"] = false;
  2188   2191                     row["INITIALLY_DEFERRED"] = false;
  2189   2192                     row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString());
  2190   2193                     row["FKEY_TO_CATALOG"] = strCatalog;
  2191   2194                     row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString());
  2192   2195                     row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString());
  2193   2196                     row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1];
         2197  +                  row["FKEY_ON_UPDATE"] = (rdKey.FieldCount > 5) ? rdKey[5] : String.Empty;
         2198  +                  row["FKEY_ON_DELETE"] = (rdKey.FieldCount > 6) ? rdKey[6] : String.Empty;
         2199  +                  row["FKEY_MATCH"] = (rdKey.FieldCount > 7) ? rdKey[7] : String.Empty;
  2194   2200   
  2195   2201                     if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), StringComparison.OrdinalIgnoreCase) == 0)
  2196   2202                       tbl.Rows.Add(row);
  2197   2203                   }
  2198   2204                 }
  2199   2205               }
  2200   2206               catch (SQLiteException)

Changes to Tests/basic.eagle.

   134    134   
   135    135   ###############################################################################
   136    136   
   137    137   runTest {test basic-1.4 {GetSchema with ReservedWords} -setup {
   138    138     setupDb [set fileName basic-1.4.db]
   139    139   } -body {
   140    140     set id [object invoke Interpreter.GetActive NextId]
   141         -
   142    141     set dataSource [file join [getTemporaryPath] basic-1.4.db]
   143    142   
   144    143     unset -nocomplain results errors
   145    144   
   146    145     set code [compileCSharpWith [subst {
   147    146       using System.Data;
   148    147       using System.Data.SQLite;
................................................................................
   177    176         } result] : [set result ""]}] $result
   178    177   } -cleanup {
   179    178     cleanupDb $fileName
   180    179     unset -nocomplain result results errors code dataSource id db fileName
   181    180   } -constraints {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} \
   182    181   -match regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0\
   183    182   System#Data#DataTable#\d+$}}
          183  +
          184  +###############################################################################
          185  +
          186  +runTest {test basic-1.5 {GetSchema with ForeignKeys} -setup {
          187  +  setupDb [set fileName basic-1.5.db]
          188  +} -body {
          189  +  sql execute $db {
          190  +    CREATE TABLE t1(
          191  +      x INTEGER REFERENCES t2 MATCH FULL
          192  +      ON UPDATE SET DEFAULT ON DELETE CASCADE
          193  +      DEFAULT 1
          194  +    );
          195  +  }
          196  +
          197  +  sql execute $db "CREATE TABLE t2(x INTEGER REFERENCES t3);"
          198  +
          199  +  set id [object invoke Interpreter.GetActive NextId]
          200  +  set dataSource [file join [getTemporaryPath] basic-1.5.db]
          201  +
          202  +  unset -nocomplain results errors
          203  +
          204  +  set code [compileCSharpWith [subst {
          205  +    using System.Data;
          206  +    using System.Data.SQLite;
          207  +
          208  +    namespace _Dynamic${id}
          209  +    {
          210  +      public class Test${id}
          211  +      {
          212  +        public static DataRowCollection GetForeignKeys()
          213  +        {
          214  +          using (SQLiteConnection connection = new SQLiteConnection(
          215  +              "Data Source=${dataSource};"))
          216  +          {
          217  +            connection.Open();
          218  +
          219  +            return connection.GetSchema("ForeignKeys").Rows;
          220  +          }
          221  +        }
          222  +
          223  +        public static void Main()
          224  +        {
          225  +          // do nothing.
          226  +        }
          227  +      }
          228  +    }
          229  +  }] results errors System.Data.SQLite.dll]
          230  +
          231  +  list $code $results \
          232  +      [expr {[info exists errors] ? $errors : ""}] \
          233  +      [expr {$code eq "Ok" ? [catch {
          234  +        set rows [list]
          235  +        set foreignKeys [object invoke _Dynamic${id}.Test${id} GetForeignKeys]
          236  +
          237  +        object foreach -alias foreignKey $foreignKeys {
          238  +          lappend rows [list \
          239  +              [$foreignKey Item CONSTRAINT_CATALOG] \
          240  +              [$foreignKey Item CONSTRAINT_NAME] \
          241  +              [$foreignKey Item TABLE_CATALOG] \
          242  +              [$foreignKey Item TABLE_NAME] \
          243  +              [$foreignKey Item CONSTRAINT_TYPE] \
          244  +              [$foreignKey Item IS_DEFERRABLE] \
          245  +              [$foreignKey Item INITIALLY_DEFERRED] \
          246  +              [$foreignKey Item FKEY_FROM_COLUMN] \
          247  +              [$foreignKey Item FKEY_TO_CATALOG] \
          248  +              [$foreignKey Item FKEY_TO_TABLE] \
          249  +              [$foreignKey Item FKEY_TO_COLUMN] \
          250  +              [$foreignKey Item FKEY_FROM_ORDINAL_POSITION] \
          251  +              [$foreignKey Item FKEY_ON_UPDATE] \
          252  +              [$foreignKey Item FKEY_ON_DELETE] \
          253  +              [$foreignKey Item FKEY_MATCH]]
          254  +        }
          255  +
          256  +        set rows
          257  +      } result] : [set result ""]}] $result
          258  +} -cleanup {
          259  +  cleanupDb $fileName
          260  +
          261  +  unset -nocomplain result rows foreignKey foreignKeys results errors code \
          262  +      dataSource id db fileName
          263  +} -constraints {eagle monoBug28 command.sql compile.DATA System.Data.SQLite} \
          264  +-match regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0\
          265  +\{\{main FK_t1_0 main t1 \{FOREIGN KEY\} False False x main t2 \{\} 0 \{SET\
          266  +DEFAULT\} CASCADE NONE\} \{main FK_t2_0 main t2 \{FOREIGN KEY\} False False x\
          267  +main t3 \{\} 0 \{NO ACTION\} \{NO ACTION\} NONE\}\}$}}
   184    268   
   185    269   ###############################################################################
   186    270   
   187    271   unset -nocomplain testExeFile testLinqExeFile northwindEfDbFile testLinqOutFile
   188    272   
   189    273   ###############################################################################
   190    274   
   191    275   runSQLiteTestEpilogue
   192    276   runTestEpilogue

Changes to Tests/common.eagle.

   120    120         # NOTE: Return the full path of the loaded file.
   121    121         #
   122    122         return $fileName
   123    123       }
   124    124   
   125    125       return ""
   126    126     }
          127  +
          128  +  proc enumerableToString { enumerable } {
          129  +    set result [list]
          130  +
          131  +    if {[string length $enumerable] == 0 || $enumerable eq "null"} then {
          132  +      return $result
          133  +    }
          134  +
          135  +    object foreach -alias item $enumerable {
          136  +      if {[string length $item] > 0} then {
          137  +        lappend result [$item ToString]
          138  +      }
          139  +    }
          140  +
          141  +    return $result
          142  +  }
   127    143   
   128    144     proc compileCSharpWith { text resultsVarName errorsVarName fileNames args } {
   129    145       #
   130    146       # NOTE: Create the base command to evaluate and add the property settings
   131    147       #       that are almost always needed by our unit tests (i.e. the System
   132    148       #       and System.Data assembly references).
   133    149       #