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: |
9eb2e8161179d718d237c50a1d572229 |
| 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
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 #