Index: SQLite.Designer/Design/ForeignKey.cs ================================================================== --- SQLite.Designer/Design/ForeignKey.cs +++ SQLite.Designer/Design/ForeignKey.cs @@ -284,19 +284,25 @@ internal class ForeignKey : IHaveConnection, ICloneable { internal Table _table; internal ForeignKeyFromItem _from; internal ForeignKeyToItem _to; - internal string _name; + 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; + _name = source._name; + _onUpdate = source._onUpdate; + _onDelete = source._onDelete; + _match = source._match; _dirty = source._dirty; } internal void MakeDirty() { @@ -318,12 +324,15 @@ { _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(); + _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, "", ""); @@ -387,10 +396,34 @@ [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() Index: SQLite.Designer/Design/Table.cs ================================================================== --- SQLite.Designer/Design/Table.cs +++ SQLite.Designer/Design/Table.cs @@ -515,11 +515,20 @@ foreach (ForeignKey key in keys) { builder.AppendFormat("{0}[{1}]", separator, key.To.Column); separator = ", "; } - builder.Append(")"); + 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 { Index: System.Data.SQLite/SQLiteConnection.cs ================================================================== --- System.Data.SQLite/SQLiteConnection.cs +++ System.Data.SQLite/SQLiteConnection.cs @@ -2152,10 +2152,13 @@ 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; @@ -2189,10 +2192,13 @@ 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); } } Index: Tests/basic.eagle ================================================================== --- Tests/basic.eagle +++ Tests/basic.eagle @@ -136,11 +136,10 @@ 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 { @@ -179,14 +178,99 @@ 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 Index: Tests/common.eagle ================================================================== --- Tests/common.eagle +++ Tests/common.eagle @@ -122,10 +122,26 @@ 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