Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | When emitting SQL for foreign keys in the VS designer, be sure to take all returned rows into account. Fix for [b226147b37]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
38cd99987c29a163edf1419ce4aceab5 |
User & Date: | mistachkin 2012-01-25 18:17:06.486 |
References
2012-01-25
| ||
18:17 | • Closed ticket [b226147b37]: Designer deletes foreign key constraints plus 2 other changes artifact: 6dedf5cf1e user: mistachkin | |
Context
2012-01-25
| ||
18:24 | Document recent changes in version history. check-in: db337c534c user: mistachkin tags: trunk | |
18:17 | When emitting SQL for foreign keys in the VS designer, be sure to take all returned rows into account. Fix for [b226147b37]. check-in: 38cd99987c user: mistachkin tags: trunk | |
18:10 | Remove unnecessary loop and fix indentation of foreign key SQL fragments. Closed-Leaf check-in: 1119da4703 user: mistachkin tags: VsDesignerFKey | |
17:37 | Fix the default value of the connection flags for the setupDb unit test infrastructure script library procedure. check-in: 96c981c40b user: mistachkin tags: trunk | |
Changes
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 | } } [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; | > > > > | 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 | } } [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; |
︙ | ︙ | |||
321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 | } 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) //{ | > > > > | 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 | } 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) //{ |
︙ | ︙ | |||
353 354 355 356 357 358 359 | [Description("The name of the foreign key.")] public string Name { get { if (String.IsNullOrEmpty(_name) == false) return _name; | | | 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 | [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(); |
︙ | ︙ | |||
379 380 381 382 383 384 385 386 387 388 389 390 391 392 | 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; } | > > > > > > > > > > > > > > > > | 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 | 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.
︙ | ︙ | |||
405 406 407 408 409 410 411 | break; } } builder.Append(separator); builder.AppendFormat("CONSTRAINT [CK_{0}_{1}] CHECK {2}", Name, n + 1, check); } | < < < < < < < < < < | | < < < < < | | < < < < < < < < < | 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 | break; } } builder.Append(separator); builder.AppendFormat("CONSTRAINT [CK_{0}_{1}] CHECK {2}", Name, n + 1, check); } if (ForeignKeys.Count > 0) { builder.Append(separator); WriteFKeys(ForeignKeys, builder); } builder.Append("\r\n);\r\n"); // Rebuilding an existing table if (altName != null) { |
︙ | ︙ | |||
496 497 498 499 500 501 502 | } return builder.ToString(); } private void WriteFKeys(List<ForeignKey> keys, StringBuilder builder) { | > > > > > > > | > | > < > | | | > | > > | > > > > > < < > | | > > | > > | | | > | | | | | | > > > > | 472 473 474 475 476 477 478 479 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 | } 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.
︙ | ︙ | |||
2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 | 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)); | > | 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 | 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)); |
︙ | ︙ | |||
2407 2408 2409 2410 2411 2412 2413 | 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; | | > | 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 | 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 271 272 273 274 275 276 277 278 279 280 281 282 283 | [$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 SQLite System.Data.SQLite} \ -match regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0\ | > | | | | 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 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] \ [$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 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] |
︙ | ︙ |