Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the genfkey program to the tool/ directory. (CVS 5796) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
84e73fe8a6f538c4b1bb4f641a661d6f |
User & Date: | danielk1977 2008-10-10 17:58:27.000 |
Context
2008-10-10
| ||
18:25 | Further simplifications of the code for the LIMIT clause on an UPDATE or DELETE. Added a few test cases to wherelimit.test. (CVS 5797) (check-in: 282c6a46b2 user: shane tags: trunk) | |
17:58 | Add the genfkey program to the tool/ directory. (CVS 5796) (check-in: 84e73fe8a6 user: danielk1977 tags: trunk) | |
17:47 | Change 'pragma foreign_key_list' to return some extra information. (CVS 5795) (check-in: 3bb33cf59d user: danielk1977 tags: trunk) | |
Changes
Added tool/genfkey.README.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 | OVERVIEW The SQLite library is capable of parsing SQL foreign key constraints supplied as part of CREATE TABLE statements, but it does not actually implement them. However, most of the features of foreign keys may be implemented using SQL triggers, which SQLite does support. This program extracts foreign key definitions from an existing SQLite database and outputs the set of CREATE TRIGGER statements required to implement the foreign key constraints. CAPABILITIES An SQL foreign key is a constraint that requires that each row in the "child" table corresponds to a row in the "parent" table. For example, the following schema: CREATE TABLE parent(a, b, c, PRIMARY KEY(a, b)); CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent(a, b)); implies that for each row in table "child", there must be a row in "parent" for which the expression (child.d==parent.a AND child.e==parent.b) is true. The columns in the parent table are required to be either the primary key columns or subject to a UNIQUE constraint. There is no such requirement for the columns of the child table. At this time, all foreign keys are implemented as if they were "MATCH NONE", even if the declaration specified "MATCH PARTIAL" or "MATCH FULL". "MATCH NONE" means that if any of the key columns in the child table are NULL, then there is no requirement for a corresponding row in the parent table. So, taking this into account, the expression that must be true for every row of the child table in the above example is actually: (child.d IS NULL) OR (child.e IS NULL) OR (child.d==parent.a AND child.e==parent.b) Attempting to insert or update a row in the child table so that the affected row violates this constraint results in an exception being thrown. The effect of attempting to delete or update a row in the parent table so that the constraint becomes untrue for one or more rows in the child table depends on the "ON DELETE" or "ON UPDATE" actions specified as part of the foreign key definition, respectively. Three different actions are supported: "RESTRICT" (the default), "CASCADE" and "SET NULL". SQLite will also parse the "SET DEFAULT" action, but this is not implemented and "RESTRICT" is used instead. RESTRICT: Attempting to update or delete a row in the parent table so that the constraint becomes untrue for one or more rows in the child table is not allowed. An exception is thrown. CASCADE: Instead of throwing an exception, all corresponding child table rows are either deleted (if the parent row is being deleted) or updated to match the new parent key values (if the parent row is being updated). SET NULL: Instead of throwing an exception, the foreign key fields of all corresponding child table rows are set to NULL. LIMITATIONS Apart from those limitiations described above: * Implicit mapping to composite primary keys is not supported. If a parent table has a composite primary key, then any child table that refers to it must explicitly map each column. For example, given the following definition of table "parent": CREATE TABLE parent(a, b, c, PRIMARY KEY(a, b)); only the first of the following two definitions of table "child" is supported: CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent(a, b)); CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent); An implicit reference to a composite primary key is detected as an error when the program is run (see below). * SQLite does not support recursive triggers, and therefore this program does not support recursive CASCADE or SET NULL foreign key relationships. If the parent and the child tables of a CASCADE or SET NULL foreign key are the same table, the generated triggers will malfunction. This is also true if the recursive foreign key constraint is indirect (for example if table A references table B which references table A with a CASCADE or SET NULL foreign key constraint). Recursive CASCADE or SET NULL foreign key relationships are *not* detected as errors when the program is run. Buyer beware. COMPILATION The source code for this program consists of a single C file - genfkey.c. The only dependency is sqlite itself. Using gcc and the sqlite amalgamation source code, it may be compiled using the following command: gcc genfkey.c sqlite3.c -o genfkey If compiled/linked against an SQLite version earlier than 3.6.4, then all foreign key constraints are assumed to be "ON UPDATE RESTRICT" and "ON DELETE RESTRICT". If linked against 3.6.4 or newer, "CASCADE" and "SET NULL" are supported as well as "RESTRICT". All 3.x versions of SQLite may use the created triggers definitions. USAGE genfkey ?--no-drop? ?--ignore-errors? <sqlite database> When this program is run, it first checks the schema of the supplied SQLite database for foreign key related errors or inconsistencies. For example, a foreign key that refers to a parent table that does not exist, or a foreign key that refers to columns in a parent table that are not guaranteed to be unique. If such errors are found, a message for each one is printed to stderr. If errors are found and the --ignore-errors option was not passed, the program exits. Otherwise, a series of SQL trigger definitions (CREATE TRIGGER statements) that implement the foreign key constraints found in the database schema are written to stdout. If any errors were found in the schema, no triggers for the problematic constraints are output. The output CREATE TRIGGER statements should be run against the database to enable enforcement of the foreign key constraints. For example, for a database named "test.db" in the current working directory: $ genfkey ./test.db | sqlite3 ./test.db All triggers generated by this program have names that match the pattern "genfkey*". Unless the --no-drop option is specified, then the program also outputs a "DROP TRIGGER" statement for each trigger that exists in the database with a name that matches this pattern. This allows the program to be used to upgrade a database schema for which foreign key triggers have already been installed (i.e. after new tables are created or existing tables dropped). |
Added tool/genfkey.c.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 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 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 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 277 278 279 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 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 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 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 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 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 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 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 | /* ** 2008 October 10 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code for 'genfkey', a program to generate trigger ** definitions that emulate foreign keys. See genfkey.README for details. ** ** $Id: genfkey.c,v 1.1 2008/10/10 17:58:27 danielk1977 Exp $ */ #include "sqlite3.h" #include <stdio.h> #include <assert.h> #include <stdlib.h> #include <string.h> /************************************************************************** *************************************************************************** ** Start of virtual table implementations. **************************************************************************/ /* The code in this file defines a sqlite3 virtual-table module that ** provides a read-only view of the current database schema. There is one ** row in the schema table for each column in the database schema. */ #define SCHEMA \ "CREATE TABLE x(" \ "database," /* Name of database (i.e. main, temp etc.) */ \ "tablename," /* Name of table */ \ "cid," /* Column number (from left-to-right, 0 upward) */ \ "name," /* Column name */ \ "type," /* Specified type (i.e. VARCHAR(32)) */ \ "not_null," /* Boolean. True if NOT NULL was specified */ \ "dflt_value," /* Default value for this column */ \ "pk" /* True if this column is part of the primary key */ \ ")" #define SCHEMA2 \ "CREATE TABLE x(" \ "database," /* Name of database (i.e. main, temp etc.) */ \ "from_tbl," /* Name of table */ \ "fkid," \ "seq," \ "to_tbl," \ "from_col," \ "to_col," \ "on_update," \ "on_delete," \ "match" \ ")" #define SCHEMA3 \ "CREATE TABLE x(" \ "database," /* Name of database (i.e. main, temp etc.) */ \ "tablename," /* Name of table */ \ "seq," \ "name," \ "isunique" \ ")" #define SCHEMA4 \ "CREATE TABLE x(" \ "database," /* Name of database (i.e. main, temp etc.) */ \ "indexname," /* Name of table */ \ "seqno," \ "cid," \ "name" \ ")" typedef struct SchemaTable SchemaTable; struct SchemaTable { const char *zName; const char *zObject; const char *zPragma; const char *zSchema; } aSchemaTable[] = { { "table_info", "table", "PRAGMA %Q.table_info(%Q)", SCHEMA }, { "foreign_key_list", "table", "PRAGMA %Q.foreign_key_list(%Q)", SCHEMA2 }, { "index_list", "table", "PRAGMA %Q.index_list(%Q)", SCHEMA3 }, { "index_info", "index", "PRAGMA %Q.index_info(%Q)", SCHEMA4 }, { 0, 0, 0, 0 } }; typedef struct schema_vtab schema_vtab; typedef struct schema_cursor schema_cursor; /* A schema table object */ struct schema_vtab { sqlite3_vtab base; sqlite3 *db; SchemaTable *pType; }; /* A schema table cursor object */ struct schema_cursor { sqlite3_vtab_cursor base; sqlite3_stmt *pDbList; sqlite3_stmt *pTableList; sqlite3_stmt *pColumnList; int rowid; }; /* ** Table destructor for the schema module. */ static int schemaDestroy(sqlite3_vtab *pVtab){ sqlite3_free(pVtab); return 0; } /* ** Table constructor for the schema module. */ static int schemaCreate( sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr ){ int rc = SQLITE_NOMEM; schema_vtab *pVtab; SchemaTable *pType = &aSchemaTable[0]; if( argc>3 ){ int i; pType = 0; for(i=0; aSchemaTable[i].zName; i++){ if( 0==strcmp(argv[3], aSchemaTable[i].zName) ){ pType = &aSchemaTable[i]; } } if( !pType ){ return SQLITE_ERROR; } } pVtab = sqlite3_malloc(sizeof(schema_vtab)); if( pVtab ){ memset(pVtab, 0, sizeof(schema_vtab)); pVtab->db = db; pVtab->pType = pType; rc = sqlite3_declare_vtab(db, pType->zSchema); } *ppVtab = (sqlite3_vtab *)pVtab; return rc; } /* ** Open a new cursor on the schema table. */ static int schemaOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ int rc = SQLITE_NOMEM; schema_cursor *pCur; pCur = sqlite3_malloc(sizeof(schema_cursor)); if( pCur ){ memset(pCur, 0, sizeof(schema_cursor)); *ppCursor = (sqlite3_vtab_cursor *)pCur; rc = SQLITE_OK; } return rc; } /* ** Close a schema table cursor. */ static int schemaClose(sqlite3_vtab_cursor *cur){ schema_cursor *pCur = (schema_cursor *)cur; sqlite3_finalize(pCur->pDbList); sqlite3_finalize(pCur->pTableList); sqlite3_finalize(pCur->pColumnList); sqlite3_free(pCur); return SQLITE_OK; } /* ** Retrieve a column of data. */ static int schemaColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){ schema_cursor *pCur = (schema_cursor *)cur; switch( i ){ case 0: sqlite3_result_value(ctx, sqlite3_column_value(pCur->pDbList, 1)); break; case 1: sqlite3_result_value(ctx, sqlite3_column_value(pCur->pTableList, 0)); break; default: sqlite3_result_value(ctx, sqlite3_column_value(pCur->pColumnList, i-2)); break; } return SQLITE_OK; } /* ** Retrieve the current rowid. */ static int schemaRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ schema_cursor *pCur = (schema_cursor *)cur; *pRowid = pCur->rowid; return SQLITE_OK; } static int finalize(sqlite3_stmt **ppStmt){ int rc = sqlite3_finalize(*ppStmt); *ppStmt = 0; return rc; } static int schemaEof(sqlite3_vtab_cursor *cur){ schema_cursor *pCur = (schema_cursor *)cur; return (pCur->pDbList ? 0 : 1); } /* ** Advance the cursor to the next row. */ static int schemaNext(sqlite3_vtab_cursor *cur){ int rc = SQLITE_OK; schema_cursor *pCur = (schema_cursor *)cur; schema_vtab *pVtab = (schema_vtab *)(cur->pVtab); char *zSql = 0; while( !pCur->pColumnList || SQLITE_ROW!=sqlite3_step(pCur->pColumnList) ){ if( SQLITE_OK!=(rc = finalize(&pCur->pColumnList)) ) goto next_exit; while( !pCur->pTableList || SQLITE_ROW!=sqlite3_step(pCur->pTableList) ){ if( SQLITE_OK!=(rc = finalize(&pCur->pTableList)) ) goto next_exit; assert(pCur->pDbList); while( SQLITE_ROW!=sqlite3_step(pCur->pDbList) ){ rc = finalize(&pCur->pDbList); goto next_exit; } /* Set zSql to the SQL to pull the list of tables from the ** sqlite_master (or sqlite_temp_master) table of the database ** identfied by the row pointed to by the SQL statement pCur->pDbList ** (iterating through a "PRAGMA database_list;" statement). */ if( sqlite3_column_int(pCur->pDbList, 0)==1 ){ zSql = sqlite3_mprintf( "SELECT name FROM sqlite_temp_master WHERE type=%Q", pVtab->pType->zObject ); }else{ sqlite3_stmt *pDbList = pCur->pDbList; zSql = sqlite3_mprintf( "SELECT name FROM %Q.sqlite_master WHERE type=%Q", sqlite3_column_text(pDbList, 1), pVtab->pType->zObject ); } if( !zSql ){ rc = SQLITE_NOMEM; goto next_exit; } rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pTableList, 0); sqlite3_free(zSql); if( rc!=SQLITE_OK ) goto next_exit; } /* Set zSql to the SQL to the table_info pragma for the table currently ** identified by the rows pointed to by statements pCur->pDbList and ** pCur->pTableList. */ zSql = sqlite3_mprintf(pVtab->pType->zPragma, sqlite3_column_text(pCur->pDbList, 1), sqlite3_column_text(pCur->pTableList, 0) ); if( !zSql ){ rc = SQLITE_NOMEM; goto next_exit; } rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pColumnList, 0); sqlite3_free(zSql); if( rc!=SQLITE_OK ) goto next_exit; } pCur->rowid++; next_exit: /* TODO: Handle rc */ return rc; } /* ** Reset a schema table cursor. */ static int schemaFilter( sqlite3_vtab_cursor *pVtabCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ int rc; schema_vtab *pVtab = (schema_vtab *)(pVtabCursor->pVtab); schema_cursor *pCur = (schema_cursor *)pVtabCursor; pCur->rowid = 0; finalize(&pCur->pTableList); finalize(&pCur->pColumnList); finalize(&pCur->pDbList); rc = sqlite3_prepare(pVtab->db,"SELECT 0, 'main'", -1, &pCur->pDbList, 0); return (rc==SQLITE_OK ? schemaNext(pVtabCursor) : rc); } /* ** Analyse the WHERE condition. */ static int schemaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ return SQLITE_OK; } /* ** A virtual table module that merely echos method calls into TCL ** variables. */ static sqlite3_module schemaModule = { 0, /* iVersion */ schemaCreate, schemaCreate, schemaBestIndex, schemaDestroy, schemaDestroy, schemaOpen, /* xOpen - open a cursor */ schemaClose, /* xClose - close a cursor */ schemaFilter, /* xFilter - configure scan constraints */ schemaNext, /* xNext - advance a cursor */ schemaEof, /* xEof */ schemaColumn, /* xColumn - read data */ schemaRowid, /* xRowid - read data */ 0, /* xUpdate */ 0, /* xBegin */ 0, /* xSync */ 0, /* xCommit */ 0, /* xRollback */ 0, /* xFindMethod */ 0, /* xRename */ }; /* ** Extension load function. */ static int installSchemaModule(sqlite3 *db){ sqlite3_create_module(db, "schema", &schemaModule, 0); return 0; } /************************************************************************** *************************************************************************** ** End of virtual table implementations. ** Start of SQL user function implementations. */ /* ** sj(zValue, zJoin) ** ** The following block contains the implementation of an aggregate ** function that returns a string. Each time the function is stepped, ** it appends data to an internal buffer. When the aggregate is finalized, ** the contents of the buffer are returned. ** ** The first time the aggregate is stepped the buffer is set to a copy ** of the first argument. The second time and subsequent times it is ** stepped a copy of the second argument is appended to the buffer, then ** a copy of the first. ** ** Example: ** ** INSERT INTO t1(a) VALUES('1'); ** INSERT INTO t1(a) VALUES('2'); ** INSERT INTO t1(a) VALUES('3'); ** SELECT sj(a, ', ') FROM t1; ** ** => "1, 2, 3" ** */ struct StrBuffer { char *zBuf; }; typedef struct StrBuffer StrBuffer; static void joinFinalize(sqlite3_context *context){ StrBuffer *p; p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer)); sqlite3_result_text(context, p->zBuf, -1, SQLITE_TRANSIENT); sqlite3_free(p->zBuf); } static void joinStep( sqlite3_context *context, int argc, sqlite3_value **argv ){ StrBuffer *p; p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer)); if( p->zBuf==0 ){ p->zBuf = sqlite3_mprintf("%s", sqlite3_value_text(argv[0])); }else{ char *zTmp = p->zBuf; p->zBuf = sqlite3_mprintf("%s%s%s", zTmp, sqlite3_value_text(argv[1]), sqlite3_value_text(argv[0]) ); sqlite3_free(zTmp); } } /* ** dq(zString) ** ** This scalar function accepts a single argument and interprets it as ** a text value. The return value is the argument enclosed in double ** quotes. If any double quote characters are present in the argument, ** these are escaped. ** ** dq('the raven "Nevermore."') == '"the raven ""Nevermore."""' */ static void doublequote( sqlite3_context *context, int argc, sqlite3_value **argv ){ int ii; char *zOut; char *zCsr; const char *zIn = (const char *)sqlite3_value_text(argv[0]); int nIn = sqlite3_value_bytes(argv[0]); zOut = sqlite3_malloc(nIn*2+3); zCsr = zOut; *zCsr++ = '"'; for(ii=0; ii<nIn; ii++){ *zCsr++ = zIn[ii]; if( zIn[ii]=='"' ){ *zCsr++ = '"'; } } *zCsr++ = '"'; *zCsr++ = '\0'; sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT); sqlite3_free(zOut); } /* ** multireplace(zString, zSearch1, zReplace1, ...) */ static void multireplace( sqlite3_context *context, int argc, sqlite3_value **argv ){ int i = 0; char *zOut = 0; int nOut = 0; int nMalloc = 0; const char *zIn = (const char *)sqlite3_value_text(argv[0]); int nIn = sqlite3_value_bytes(argv[0]); while( i<nIn ){ const char *zCopy = &zIn[i]; int nCopy = 1; int nReplace = 1; int j; for(j=1; j<(argc-1); j+=2){ const char *z = (const char *)sqlite3_value_text(argv[j]); int n = sqlite3_value_bytes(argv[j]); if( n<=(nIn-i) && 0==strncmp(z, zCopy, n) ){ zCopy = (const char *)sqlite3_value_text(argv[j+1]); nCopy = sqlite3_value_bytes(argv[j+1]); nReplace = n; break; } } if( (nOut+nCopy)>nMalloc ){ nMalloc += (nMalloc + 16); zOut = (char *)sqlite3_realloc(zOut, nMalloc); } memcpy(&zOut[nOut], zCopy, nCopy); i += nReplace; nOut += nCopy; } sqlite3_result_text(context, zOut, nOut, SQLITE_TRANSIENT); sqlite3_free(zOut); } /************************************************************************** *************************************************************************** ** End of SQL user function implementations. ** Start of application implementation. */ typedef struct Options Options; struct Options { char *zDb; int ignoreErrors; int noDrop; }; /* ** Print out a usage message for the command line and exit. This is ** called from processCmdLine() if the program is invoked incorrectly. */ static int usage(char *zProgram){ fprintf(stderr, "Usage: %s ?--ignore-errors? ?--no-drop? <database file>\n", zProgram ); exit(-1); } static void processCmdLine(int nArg, char **azArg, Options *p){ int i; assert( nArg>0 ); if( nArg<2 ){ usage(azArg[0]); } for(i=1; i<(nArg-1); i++){ char *z = azArg[i]; if( 0==strcmp(z, "--ignore-errors") ){ p->ignoreErrors = 1; } else if( 0==strcmp(z, "--no-drop") ){ p->noDrop = 1; } else usage(azArg[0]); } p->zDb = azArg[nArg-1]; } /* ** A callback for sqlite3_exec() that prints its first argument to ** stdout followed by a newline. */ static int printString(void *p, int nArg, char **azArg, char **azCol){ printf("%s\n", azArg[0]); return SQLITE_OK; } int detectSchemaProblem( sqlite3 *db, /* Database connection */ const char *zMessage, /* English language error message */ const char *zSql, /* SQL statement to run */ int *pHasErrors /* Set *pHasErrors==1 if errors found */ ){ sqlite3_stmt *pStmt; int rc; rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); if( rc!=SQLITE_OK ){ return rc; } while( SQLITE_ROW==sqlite3_step(pStmt) ){ char *zDel; int iFk = sqlite3_column_int(pStmt, 0); const char *zTab = (const char *)sqlite3_column_text(pStmt, 1); fprintf(stderr, "Error in table %s: %s\n", zTab, zMessage); zDel = sqlite3_mprintf( "DELETE FROM temp.fkey WHERE from_tbl = %Q AND fkid = %d" , zTab, iFk ); sqlite3_exec(db, zDel, 0, 0, 0); sqlite3_free(zDel); *pHasErrors = 1; } sqlite3_finalize(pStmt); return SQLITE_OK; } /* ** Create and populate temporary table "fkey". */ static int populateTempTable(sqlite3 *db, char **pzErr, int *pHasErrors){ int rc; rc = sqlite3_exec(db, "CREATE VIRTUAL TABLE temp.v_fkey USING schema(foreign_key_list);" "CREATE VIRTUAL TABLE temp.v_col USING schema(table_info);" "CREATE VIRTUAL TABLE temp.v_idxlist USING schema(index_list);" "CREATE VIRTUAL TABLE temp.v_idxinfo USING schema(index_info);" "CREATE TABLE temp.fkey AS " "SELECT from_tbl, to_tbl, fkid, from_col, to_col, on_update, on_delete " "FROM temp.v_fkey WHERE database = 'main';" "CREATE TABLE temp.col AS " "SELECT * FROM temp.v_col WHERE database = 'main';" , 0, 0, pzErr ); if( rc!=SQLITE_OK ) return rc; rc = detectSchemaProblem(db, "foreign key columns do not exist", "SELECT fkid, from_tbl " "FROM temp.fkey " "WHERE to_col IS NOT NULL AND NOT EXISTS (SELECT 1 " "FROM temp.col WHERE tablename=to_tbl AND name==to_col" ")", pHasErrors ); if( rc!=SQLITE_OK ) return rc; /* At this point the temp.fkey table is mostly populated. If any foreign ** keys were specified so that they implicitly refer to they primary ** key of the parent table, the "to_col" values of the temp.fkey rows ** are still set to NULL. ** ** This is easily fixed for single column primary keys, but not for ** composites. With a composite primary key, there is no way to reliably ** query sqlite for the order in which the columns that make up the ** composite key were declared i.e. there is no way to tell if the ** schema actually contains "PRIMARY KEY(a, b)" or "PRIMARY KEY(b, a)". ** Therefore, this case is not handled. The following function call ** detects instances of this case. */ rc = detectSchemaProblem(db, "implicit mapping to composite primary key", "SELECT fkid, from_tbl " "FROM temp.fkey " "WHERE to_col IS NULL " "GROUP BY fkid, from_tbl HAVING count(*) > 1", pHasErrors ); if( rc!=SQLITE_OK ) return rc; /* Detect attempts to implicitly map to the primary key of a table ** that has no primary key column. */ rc = detectSchemaProblem(db, "implicit mapping to non-existant primary key", "SELECT fkid, from_tbl " "FROM temp.fkey " "WHERE to_col IS NULL AND NOT EXISTS " "(SELECT 1 FROM temp.col WHERE pk AND tablename = temp.fkey.to_tbl)" , pHasErrors ); if( rc!=SQLITE_OK ) return rc; /* Fix all the implicit primary key mappings in the temp.fkey table. */ rc = sqlite3_exec(db, "UPDATE temp.fkey SET to_col = " "(SELECT name FROM temp.col WHERE pk AND tablename=temp.fkey.to_tbl)" " WHERE to_col IS NULL;" , 0, 0, pzErr ); if( rc!=SQLITE_OK ) return rc; /* Now check that all all parent keys are either primary keys or ** subject to a unique constraint. */ rc = sqlite3_exec(db, "CREATE TABLE temp.idx2 AS SELECT " "il.tablename AS tablename," "ii.indexname AS indexname," "ii.name AS col " "FROM temp.v_idxlist AS il, temp.v_idxinfo AS ii " "WHERE il.isunique AND il.database='main' AND ii.indexname = il.name;" "INSERT INTO temp.idx2 SELECT tablename, 'pk', name FROM temp.col WHERE pk;" "CREATE TABLE temp.idx AS SELECT " "tablename, indexname, sj(dq(col),',') AS cols " "FROM (SELECT * FROM temp.idx2 ORDER BY col) " "GROUP BY tablename, indexname;" "CREATE TABLE temp.fkey2 AS SELECT " "fkid, from_tbl, to_tbl, sj(dq(to_col),',') AS cols " "FROM (SELECT * FROM temp.fkey ORDER BY to_col) " "GROUP BY fkid, from_tbl;" , 0, 0, pzErr ); if( rc!=SQLITE_OK ) return rc; rc = detectSchemaProblem(db, "foreign key is not unique", "SELECT fkid, from_tbl " "FROM temp.fkey2 " "WHERE NOT EXISTS (SELECT 1 " "FROM temp.idx WHERE tablename=to_tbl AND fkey2.cols==idx.cols" ")", pHasErrors ); if( rc!=SQLITE_OK ) return rc; return rc; } int main(int argc, char **argv){ sqlite3 *db; Options opt = {0, 0, 0}; int rc; int hasErrors = 0; char *zErr = 0; const int enc = SQLITE_UTF8; const char *zSql = "SELECT multireplace('" "-- Triggers for foreign key mapping:\n" "--\n" "-- /from_readable/ REFERENCES /to_readable/\n" "-- on delete /on_delete/\n" "-- on update /on_update/\n" "--\n" /* The "BEFORE INSERT ON <referencing>" trigger. This trigger's job is to ** throw an exception if the user tries to insert a row into the ** referencing table for which there is no corresponding row in ** the referenced table. */ "CREATE TRIGGER /name/_insert_referencing BEFORE INSERT ON /tbl/ WHEN \n" " /key_notnull/ AND NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" "BEGIN\n" " SELECT RAISE(ABORT, ''constraint failed'');\n" "END;\n" /* The "BEFORE UPDATE ON <referencing>" trigger. This trigger's job ** is to throw an exception if the user tries to update a row in the ** referencing table causing it to correspond to no row in the ** referenced table. */ "CREATE TRIGGER /name/_update_referencing BEFORE\n" " UPDATE OF /rkey_list/ ON /tbl/ WHEN \n" " /key_notnull/ AND \n" " NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" "BEGIN\n" " SELECT RAISE(ABORT, ''constraint failed'');\n" "END;\n" /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job ** is to detect when a row is deleted from the referenced table to ** which rows in the referencing table correspond. The action taken ** depends on the value of the 'ON DELETE' clause. */ "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n" " EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n" "BEGIN\n" " /delete_action/\n" "END;\n" /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job ** is to detect when the key columns of a row in the referenced table ** to which one or more rows in the referencing table correspond are ** updated. The action taken depends on the value of the 'ON UPDATE' ** clause. */ "CREATE TRIGGER /name/_update_referenced AFTER\n" " UPDATE OF /fkey_list/ ON /ref/ WHEN \n" " EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n" "BEGIN\n" " /update_action/\n" "END;\n" "'" /* These are used in the SQL comment written above each set of triggers */ ", '/from_readable/', from_tbl || '(' || sj(from_col, ', ') || ')'" ", '/to_readable/', to_tbl || '(' || sj(to_col, ', ') || ')'" ", '/on_delete/', on_delete" ", '/on_update/', on_update" ", '/name/', 'genfkey' || min(rowid)" ", '/tbl/', dq(from_tbl)" ", '/ref/', dq(to_tbl)" ", '/key_notnull/', sj('new.' || dq(from_col) || ' IS NOT NULL', ' AND ')" ", '/fkey_list/', sj(to_col, ', ')" ", '/rkey_list/', sj(from_col, ', ')" ", '/cond1/', sj(multireplace('new./from/ == /to/'" ", '/from/', dq(from_col)" ", '/to/', dq(to_col)" "), ' AND ')" ", '/cond2/', sj(multireplace('old./to/ == /from/'" ", '/from/', dq(from_col)" ", '/to/', dq(to_col)" "), ' AND ')" ", '/update_action/', CASE on_update " "WHEN 'SET NULL' THEN " "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " ", '/setlist/', sj(from_col||' = NULL',', ')" ", '/tbl/', dq(from_tbl)" ", '/where/', sj(from_col||' = old.'||dq(to_col),' AND ')" ")" "WHEN 'CASCADE' THEN " "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " ", '/setlist/', sj(dq(from_col)||' = new.'||dq(to_col),', ')" ", '/tbl/', dq(from_tbl)" ", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')" ")" "ELSE " " 'SELECT RAISE(ABORT, ''constraint failed'');'" "END " ", '/delete_action/', CASE on_delete " "WHEN 'SET NULL' THEN " "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " ", '/setlist/', sj(from_col||' = NULL',', ')" ", '/tbl/', dq(from_tbl)" ", '/where/', sj(from_col||' = old.'||dq(to_col),' AND ')" ")" "WHEN 'CASCADE' THEN " "multireplace('DELETE FROM /tbl/ WHERE /where/;' " ", '/tbl/', dq(from_tbl)" ", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')" ")" "ELSE " " 'SELECT RAISE(ABORT, ''constraint failed'');'" "END " ") FROM temp.fkey " "GROUP BY from_tbl, fkid" ; processCmdLine(argc, argv, &opt); /* Open the database handle. */ rc = sqlite3_open_v2(opt.zDb, &db, SQLITE_OPEN_READONLY, 0); if( rc!=SQLITE_OK ){ fprintf(stderr, "Error opening database file: %s\n", sqlite3_errmsg(db)); return -1; } /* Create the special scalar and aggregate functions used by this program. */ sqlite3_create_function(db, "dq", 1, enc, 0, doublequote, 0, 0); sqlite3_create_function(db, "multireplace", -1, enc, db, multireplace, 0, 0); sqlite3_create_function(db, "sj", 2, enc, 0, 0, joinStep, joinFinalize); /* Install the "schema" virtual table module */ installSchemaModule(db); /* Create and populate a temp table with the information required to ** build the foreign key triggers. See function populateTempTable() ** for details. */ rc = populateTempTable(db, &zErr, &hasErrors); if( rc!=SQLITE_OK ){ fprintf(stderr, "Error reading database: %s\n", zErr); return -1; } if( hasErrors && opt.ignoreErrors==0 ){ return -1; } printf("BEGIN;\n"); /* Unless the --no-drop option was specified, generate DROP TRIGGER ** statements to drop any triggers in the database generated by a ** previous run of this program. */ if( opt.noDrop==0 ){ rc = sqlite3_exec(db, "SELECT 'DROP TRIGGER' || ' ' || dq(name) || ';'" "FROM sqlite_master " "WHERE type='trigger' AND substr(name, 0, 7) == 'genfkey'" , printString, 0, 0 ); if( rc!=SQLITE_OK ){ const char *zMsg = sqlite3_errmsg(db); fprintf(stderr, "Generating drop triggers failed: %s\n", zMsg); return -1; } } /* Run the main query to create the trigger definitions. */ rc = sqlite3_exec(db, zSql, printString, 0, 0); if( rc!=SQLITE_OK ){ fprintf(stderr, "Generating triggers failed: %s\n", sqlite3_errmsg(db)); return -1; } printf("COMMIT;\n"); return 0; } |
Added tool/genfkey.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 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 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 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 | package require sqlite3 proc do_test {name cmd expected} { puts -nonewline "$name ..." set res [uplevel $cmd] if {$res eq $expected} { puts Ok } else { puts Error puts " Got: $res" puts " Expected: $expected" exit } } proc execsql {sql} { uplevel [list db eval $sql] } proc catchsql {sql} { set rc [catch {uplevel [list db eval $sql]} msg] list $rc $msg } file delete -force test.db test.db.journal sqlite3 db test.db # The following tests - genfkey-1.* - test RESTRICT foreign keys. # do_test genfkey-1.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); CREATE TABLE t2(e REFERENCES t1, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); } } {} do_test genfkey-1.2 { execsql [exec ./genfkey test.db] } {} do_test genfkey-1.3 { catchsql { INSERT INTO t2 VALUES(1, 2) } } {1 {constraint failed}} do_test genfkey-1.4 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t2 VALUES(1, 2); } } {} do_test genfkey-1.5 { execsql { INSERT INTO t2 VALUES(NULL, 3) } } {} do_test genfkey-1.6 { catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } } {1 {constraint failed}} do_test genfkey-1.7 { execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } } {} do_test genfkey-1.8 { execsql { UPDATE t2 SET e = NULL WHERE f = 3 } } {} do_test genfkey-1.9 { catchsql { UPDATE t1 SET a = 10 } } {1 {constraint failed}} do_test genfkey-1.9a { catchsql { UPDATE t1 SET a = NULL } } {1 {datatype mismatch}} do_test genfkey-1.10 { catchsql { DELETE FROM t1 } } {1 {constraint failed}} do_test genfkey-1.11 { execsql { UPDATE t2 SET e = NULL } } {} do_test genfkey-1.12 { execsql { UPDATE t1 SET a = 10 ; DELETE FROM t1; DELETE FROM t2; } } {} do_test genfkey-1.13 { execsql { INSERT INTO t3 VALUES(1, NULL, NULL); INSERT INTO t3 VALUES(1, 2, NULL); INSERT INTO t3 VALUES(1, NULL, 3); } } {} do_test genfkey-1.14 { catchsql { INSERT INTO t3 VALUES(3, 1, 4) } } {1 {constraint failed}} do_test genfkey-1.15 { execsql { INSERT INTO t1 VALUES(1, 1, 4); INSERT INTO t3 VALUES(3, 1, 4); } } {} do_test genfkey-1.16 { catchsql { DELETE FROM t1 } } {1 {constraint failed}} do_test genfkey-1.17 { catchsql { UPDATE t1 SET b = 10} } {1 {constraint failed}} do_test genfkey-1.18 { execsql { UPDATE t1 SET a = 10} } {} do_test genfkey-1.19 { catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} } {1 {constraint failed}} do_test genfkey-1.X { execsql { DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; } } {} # The following tests - genfkey-2.* - test CASCADE foreign keys. # do_test genfkey-2.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE ); } } {} do_test genfkey-2.2 { execsql [exec ./genfkey test.db] } {} do_test genfkey-2.3 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(4, 'four'); } } {} do_test genfkey-2.4 { execsql { UPDATE t1 SET a = 2 WHERE a = 1; SELECT * FROM t2; } } {2 one 4 four} do_test genfkey-2.5 { execsql { DELETE FROM t1 WHERE a = 4; SELECT * FROM t2; } } {2 one} do_test genfkey-2.6 { execsql { INSERT INTO t3 VALUES('hello', 2, 3); UPDATE t1 SET c = 2; SELECT * FROM t3; } } {hello 2 2} do_test genfkey-2.7 { execsql { DELETE FROM t1; SELECT * FROM t3; } } {} do_test genfkey-2.X { execsql { DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; } } {} # The following tests - genfkey-3.* - test SET NULL foreign keys. # do_test genfkey-3.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL ); } } {} do_test genfkey-3.2 { execsql [exec ./genfkey test.db] } {} do_test genfkey-3.3 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(4, 'four'); } } {} do_test genfkey-3.4 { execsql { UPDATE t1 SET a = 2 WHERE a = 1; SELECT * FROM t2; } } {{} one 4 four} do_test genfkey-3.5 { execsql { DELETE FROM t1 WHERE a = 4; SELECT * FROM t2; } } {{} one {} four} do_test genfkey-3.6 { execsql { INSERT INTO t3 VALUES('hello', 2, 3); UPDATE t1 SET c = 2; SELECT * FROM t3; } } {hello {} {}} do_test genfkey-2.7 { execsql { UPDATE t3 SET h = 2, i = 2; DELETE FROM t1; SELECT * FROM t3; } } {hello {} {}} do_test genfkey-3.X { execsql { DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; } } {} # The following tests - genfkey-4.* - test that errors in the schema # are detected correctly. # do_test genfkey-4.1 { execsql { CREATE TABLE t1(a REFERENCES nosuchtable, b); CREATE TABLE t2(a REFERENCES t1, b); CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); CREATE TABLE t4(a, b, c, FOREIGN KEY(c, b) REFERENCES t3); CREATE TABLE t5(a REFERENCES t4(d), b, c); CREATE TABLE t6(a REFERENCES t4(a), b, c); CREATE TABLE t7(a REFERENCES t3(a), b, c); CREATE TABLE t8(a REFERENCES nosuchtable(a), b, c); } } {} do_test genfkey-4.X { set rc [catch {exec ./genfkey test.db} msg] list $rc $msg } "1 {[string trim { Error in table t5: foreign key columns do not exist Error in table t8: foreign key columns do not exist Error in table t4: implicit mapping to composite primary key Error in table t1: implicit mapping to non-existant primary key Error in table t2: implicit mapping to non-existant primary key Error in table t6: foreign key is not unique Error in table t7: foreign key is not unique }]}" |