/ Check-in [84e73fe8]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:84e73fe8a6f538c4b1bb4f641a661d6fafb60c76
User & Date: danielk1977 2008-10-10 17:58:27
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: 282c6a46 user: shane tags: trunk
17:58
Add the genfkey program to the tool/ directory. (CVS 5796) check-in: 84e73fe8 user: danielk1977 tags: trunk
17:47
Change 'pragma foreign_key_list' to return some extra information. (CVS 5795) check-in: 3bb33cf5 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added tool/genfkey.README.

            1  +
            2  +OVERVIEW
            3  +
            4  +  The SQLite library is capable of parsing SQL foreign key constraints 
            5  +  supplied as part of CREATE TABLE statements, but it does not actually 
            6  +  implement them. However, most of the features of foreign keys may be
            7  +  implemented using SQL triggers, which SQLite does support. This program 
            8  +  extracts foreign key definitions from an existing SQLite database and
            9  +  outputs the set of CREATE TRIGGER statements required to implement
           10  +  the foreign key constraints.
           11  +
           12  +CAPABILITIES
           13  +
           14  +  An SQL foreign key is a constraint that requires that each row in
           15  +  the "child" table corresponds to a row in the "parent" table. For
           16  +  example, the following schema:
           17  +
           18  +    CREATE TABLE parent(a, b, c, PRIMARY KEY(a, b));
           19  +    CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent(a, b));
           20  +
           21  +  implies that for each row in table "child", there must be a row in
           22  +  "parent" for which the expression (child.d==parent.a AND child.e==parent.b) 
           23  +  is true. The columns in the parent table are required to be either the
           24  +  primary key columns or subject to a UNIQUE constraint. There is no such
           25  +  requirement for the columns of the child table.
           26  +
           27  +  At this time, all foreign keys are implemented as if they were 
           28  +  "MATCH NONE", even if the declaration specified "MATCH PARTIAL" or 
           29  +  "MATCH FULL". "MATCH NONE" means that if any of the key columns in
           30  +  the child table are NULL, then there is no requirement for a corresponding
           31  +  row in the parent table. So, taking this into account, the expression that
           32  +  must be true for every row of the child table in the above example is
           33  +  actually:
           34  +
           35  +      (child.d IS NULL) OR 
           36  +      (child.e IS NULL) OR 
           37  +      (child.d==parent.a AND child.e==parent.b)
           38  +
           39  +  Attempting to insert or update a row in the child table so that the 
           40  +  affected row violates this constraint results in an exception being 
           41  +  thrown.
           42  +
           43  +  The effect of attempting to delete or update a row in the parent table 
           44  +  so that the constraint becomes untrue for one or more rows in the child
           45  +  table depends on the "ON DELETE" or "ON UPDATE" actions specified as
           46  +  part of the foreign key definition, respectively. Three different actions
           47  +  are supported: "RESTRICT" (the default), "CASCADE" and "SET NULL". SQLite
           48  +  will also parse the "SET DEFAULT" action, but this is not implemented
           49  +  and "RESTRICT" is used instead.
           50  +
           51  +    RESTRICT: Attempting to update or delete a row in the parent table so
           52  +              that the constraint becomes untrue for one or more rows in
           53  +              the child table is not allowed. An exception is thrown.
           54  +
           55  +    CASCADE:  Instead of throwing an exception, all corresponding child table
           56  +              rows are either deleted (if the parent row is being deleted)
           57  +              or updated to match the new parent key values (if the parent 
           58  +              row is being updated).
           59  +
           60  +    SET NULL: Instead of throwing an exception, the foreign key fields of
           61  +              all corresponding child table rows are set to NULL.
           62  +
           63  +LIMITATIONS
           64  +
           65  +  Apart from those limitiations described above:
           66  +
           67  +    * Implicit mapping to composite primary keys is not supported. If
           68  +      a parent table has a composite primary key, then any child table
           69  +      that refers to it must explicitly map each column. For example, given
           70  +      the following definition of table "parent":
           71  +
           72  +        CREATE TABLE parent(a, b, c, PRIMARY KEY(a, b));
           73  +
           74  +      only the first of the following two definitions of table "child"
           75  +      is supported:
           76  +
           77  +        CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent(a, b));
           78  +        CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent);
           79  +
           80  +      An implicit reference to a composite primary key is detected as an
           81  +      error when the program is run (see below).
           82  +
           83  +    * SQLite does not support recursive triggers, and therefore this program
           84  +      does not support recursive CASCADE or SET NULL foreign key 
           85  +      relationships. If the parent and the child tables of a CASCADE or
           86  +      SET NULL foreign key are the same table, the generated triggers will
           87  +      malfunction. This is also true if the recursive foreign key constraint
           88  +      is indirect (for example if table A references table B which references
           89  +      table A with a CASCADE or SET NULL foreign key constraint).
           90  +
           91  +      Recursive CASCADE or SET NULL foreign key relationships are *not*
           92  +      detected as errors when the program is run. Buyer beware.
           93  +      
           94  +COMPILATION
           95  +
           96  +  The source code for this program consists of a single C file - genfkey.c.
           97  +  The only dependency is sqlite itself. Using gcc and the sqlite amalgamation
           98  +  source code, it may be compiled using the following command:
           99  +
          100  +    gcc genfkey.c sqlite3.c -o genfkey
          101  +
          102  +  If compiled/linked against an SQLite version earlier than 3.6.4, then
          103  +  all foreign key constraints are assumed to be "ON UPDATE RESTRICT" and
          104  +  "ON DELETE RESTRICT". If linked against 3.6.4 or newer, "CASCADE" and
          105  +  "SET NULL" are supported as well as "RESTRICT". All 3.x versions of SQLite
          106  +  may use the created triggers definitions.
          107  +
          108  +USAGE
          109  +
          110  +    genfkey ?--no-drop? ?--ignore-errors? <sqlite database>
          111  +
          112  +  When this program is run, it first checks the schema of the supplied SQLite
          113  +  database for foreign key related errors or inconsistencies. For example,
          114  +  a foreign key that refers to a parent table that does not exist, or
          115  +  a foreign key that refers to columns in a parent table that are not
          116  +  guaranteed to be unique. If such errors are found, a message for each
          117  +  one is printed to stderr.
          118  +
          119  +  If errors are found and the --ignore-errors option was not passed, the
          120  +  program exits. Otherwise, a series of SQL trigger definitions (CREATE 
          121  +  TRIGGER statements) that implement the foreign key constraints found
          122  +  in the database schema are written to stdout. If any errors were 
          123  +  found in the schema, no triggers for the problematic constraints are
          124  +  output. The output CREATE TRIGGER statements should be run against the
          125  +  database to enable enforcement of the foreign key constraints. For
          126  +  example, for a database named "test.db" in the current working directory:
          127  +
          128  +    $ genfkey ./test.db | sqlite3 ./test.db
          129  +
          130  +  All triggers generated by this program have names that match the pattern
          131  +  "genfkey*". Unless the --no-drop option is specified, then the program
          132  +  also outputs a "DROP TRIGGER" statement for each trigger that exists
          133  +  in the database with a name that matches this pattern. This allows the
          134  +  program to be used to upgrade a database schema for which foreign key
          135  +  triggers have already been installed (i.e. after new tables are created
          136  +  or existing tables dropped).
          137  +  
          138  +

Added tool/genfkey.c.

            1  +/*
            2  +** 2008 October 10
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +** This file contains C code for 'genfkey', a program to generate trigger
           13  +** definitions that emulate foreign keys. See genfkey.README for details.
           14  +**
           15  +** $Id: genfkey.c,v 1.1 2008/10/10 17:58:27 danielk1977 Exp $
           16  +*/
           17  +
           18  +#include "sqlite3.h"
           19  +#include <stdio.h>
           20  +#include <assert.h>
           21  +#include <stdlib.h>
           22  +#include <string.h>
           23  +
           24  +/**************************************************************************
           25  +***************************************************************************
           26  +** Start of virtual table implementations.
           27  +**************************************************************************/
           28  +
           29  +/* The code in this file defines a sqlite3 virtual-table module that
           30  +** provides a read-only view of the current database schema. There is one
           31  +** row in the schema table for each column in the database schema.
           32  +*/
           33  +#define SCHEMA \
           34  +"CREATE TABLE x("                                                            \
           35  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
           36  +  "tablename,"         /* Name of table */                                   \
           37  +  "cid,"               /* Column number (from left-to-right, 0 upward) */    \
           38  +  "name,"              /* Column name */                                     \
           39  +  "type,"              /* Specified type (i.e. VARCHAR(32)) */               \
           40  +  "not_null,"          /* Boolean. True if NOT NULL was specified */         \
           41  +  "dflt_value,"        /* Default value for this column */                   \
           42  +  "pk"                 /* True if this column is part of the primary key */  \
           43  +")"
           44  +
           45  +#define SCHEMA2 \
           46  +"CREATE TABLE x("                                                            \
           47  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
           48  +  "from_tbl,"          /* Name of table */                                   \
           49  +  "fkid,"                                                                    \
           50  +  "seq,"                                                                     \
           51  +  "to_tbl,"                                                                  \
           52  +  "from_col,"                                                                \
           53  +  "to_col,"                                                                  \
           54  +  "on_update,"                                                               \
           55  +  "on_delete,"                                                               \
           56  +  "match"                                                                    \
           57  +")"
           58  +
           59  +#define SCHEMA3 \
           60  +"CREATE TABLE x("                                                            \
           61  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
           62  +  "tablename,"         /* Name of table */                                   \
           63  +  "seq,"                                                                     \
           64  +  "name,"                                                                    \
           65  +  "isunique"                                                                 \
           66  +")"
           67  +
           68  +#define SCHEMA4 \
           69  +"CREATE TABLE x("                                                            \
           70  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
           71  +  "indexname,"         /* Name of table */                                   \
           72  +  "seqno,"                                                                   \
           73  +  "cid,"                                                                     \
           74  +  "name"                                                                     \
           75  +")"
           76  +
           77  +typedef struct SchemaTable SchemaTable;
           78  +struct SchemaTable {
           79  +  const char *zName;
           80  +  const char *zObject;
           81  +  const char *zPragma;
           82  +  const char *zSchema;
           83  +} aSchemaTable[] = {
           84  +  { "table_info",       "table", "PRAGMA %Q.table_info(%Q)",       SCHEMA },
           85  +  { "foreign_key_list", "table", "PRAGMA %Q.foreign_key_list(%Q)", SCHEMA2 },
           86  +  { "index_list",       "table", "PRAGMA %Q.index_list(%Q)",       SCHEMA3 },
           87  +  { "index_info",       "index", "PRAGMA %Q.index_info(%Q)",       SCHEMA4 },
           88  +  { 0, 0, 0, 0 }
           89  +};
           90  +
           91  +typedef struct schema_vtab schema_vtab;
           92  +typedef struct schema_cursor schema_cursor;
           93  +
           94  +/* A schema table object */
           95  +struct schema_vtab {
           96  +  sqlite3_vtab base;
           97  +  sqlite3 *db;
           98  +  SchemaTable *pType;
           99  +};
          100  +
          101  +/* A schema table cursor object */
          102  +struct schema_cursor {
          103  +  sqlite3_vtab_cursor base;
          104  +  sqlite3_stmt *pDbList;
          105  +  sqlite3_stmt *pTableList;
          106  +  sqlite3_stmt *pColumnList;
          107  +  int rowid;
          108  +};
          109  +
          110  +/*
          111  +** Table destructor for the schema module.
          112  +*/
          113  +static int schemaDestroy(sqlite3_vtab *pVtab){
          114  +  sqlite3_free(pVtab);
          115  +  return 0;
          116  +}
          117  +
          118  +/*
          119  +** Table constructor for the schema module.
          120  +*/
          121  +static int schemaCreate(
          122  +  sqlite3 *db,
          123  +  void *pAux,
          124  +  int argc, const char *const*argv,
          125  +  sqlite3_vtab **ppVtab,
          126  +  char **pzErr
          127  +){
          128  +  int rc = SQLITE_NOMEM;
          129  +  schema_vtab *pVtab;
          130  +  SchemaTable *pType = &aSchemaTable[0];
          131  +
          132  +  if( argc>3 ){
          133  +    int i;
          134  +    pType = 0;
          135  +    for(i=0; aSchemaTable[i].zName; i++){ 
          136  +      if( 0==strcmp(argv[3], aSchemaTable[i].zName) ){
          137  +        pType = &aSchemaTable[i];
          138  +      }
          139  +    }
          140  +    if( !pType ){
          141  +      return SQLITE_ERROR;
          142  +    }
          143  +  }
          144  +
          145  +  pVtab = sqlite3_malloc(sizeof(schema_vtab));
          146  +  if( pVtab ){
          147  +    memset(pVtab, 0, sizeof(schema_vtab));
          148  +    pVtab->db = db;
          149  +    pVtab->pType = pType;
          150  +    rc = sqlite3_declare_vtab(db, pType->zSchema);
          151  +  }
          152  +  *ppVtab = (sqlite3_vtab *)pVtab;
          153  +  return rc;
          154  +}
          155  +
          156  +/*
          157  +** Open a new cursor on the schema table.
          158  +*/
          159  +static int schemaOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          160  +  int rc = SQLITE_NOMEM;
          161  +  schema_cursor *pCur;
          162  +  pCur = sqlite3_malloc(sizeof(schema_cursor));
          163  +  if( pCur ){
          164  +    memset(pCur, 0, sizeof(schema_cursor));
          165  +    *ppCursor = (sqlite3_vtab_cursor *)pCur;
          166  +    rc = SQLITE_OK;
          167  +  }
          168  +  return rc;
          169  +}
          170  +
          171  +/*
          172  +** Close a schema table cursor.
          173  +*/
          174  +static int schemaClose(sqlite3_vtab_cursor *cur){
          175  +  schema_cursor *pCur = (schema_cursor *)cur;
          176  +  sqlite3_finalize(pCur->pDbList);
          177  +  sqlite3_finalize(pCur->pTableList);
          178  +  sqlite3_finalize(pCur->pColumnList);
          179  +  sqlite3_free(pCur);
          180  +  return SQLITE_OK;
          181  +}
          182  +
          183  +/*
          184  +** Retrieve a column of data.
          185  +*/
          186  +static int schemaColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
          187  +  schema_cursor *pCur = (schema_cursor *)cur;
          188  +  switch( i ){
          189  +    case 0:
          190  +      sqlite3_result_value(ctx, sqlite3_column_value(pCur->pDbList, 1));
          191  +      break;
          192  +    case 1:
          193  +      sqlite3_result_value(ctx, sqlite3_column_value(pCur->pTableList, 0));
          194  +      break;
          195  +    default:
          196  +      sqlite3_result_value(ctx, sqlite3_column_value(pCur->pColumnList, i-2));
          197  +      break;
          198  +  }
          199  +  return SQLITE_OK;
          200  +}
          201  +
          202  +/*
          203  +** Retrieve the current rowid.
          204  +*/
          205  +static int schemaRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          206  +  schema_cursor *pCur = (schema_cursor *)cur;
          207  +  *pRowid = pCur->rowid;
          208  +  return SQLITE_OK;
          209  +}
          210  +
          211  +static int finalize(sqlite3_stmt **ppStmt){
          212  +  int rc = sqlite3_finalize(*ppStmt);
          213  +  *ppStmt = 0;
          214  +  return rc;
          215  +}
          216  +
          217  +static int schemaEof(sqlite3_vtab_cursor *cur){
          218  +  schema_cursor *pCur = (schema_cursor *)cur;
          219  +  return (pCur->pDbList ? 0 : 1);
          220  +}
          221  +
          222  +/*
          223  +** Advance the cursor to the next row.
          224  +*/
          225  +static int schemaNext(sqlite3_vtab_cursor *cur){
          226  +  int rc = SQLITE_OK;
          227  +  schema_cursor *pCur = (schema_cursor *)cur;
          228  +  schema_vtab *pVtab = (schema_vtab *)(cur->pVtab);
          229  +  char *zSql = 0;
          230  +
          231  +  while( !pCur->pColumnList || SQLITE_ROW!=sqlite3_step(pCur->pColumnList) ){
          232  +    if( SQLITE_OK!=(rc = finalize(&pCur->pColumnList)) ) goto next_exit;
          233  +
          234  +    while( !pCur->pTableList || SQLITE_ROW!=sqlite3_step(pCur->pTableList) ){
          235  +      if( SQLITE_OK!=(rc = finalize(&pCur->pTableList)) ) goto next_exit;
          236  +
          237  +      assert(pCur->pDbList);
          238  +      while( SQLITE_ROW!=sqlite3_step(pCur->pDbList) ){
          239  +        rc = finalize(&pCur->pDbList);
          240  +        goto next_exit;
          241  +      }
          242  +
          243  +      /* Set zSql to the SQL to pull the list of tables from the 
          244  +      ** sqlite_master (or sqlite_temp_master) table of the database
          245  +      ** identfied by the row pointed to by the SQL statement pCur->pDbList
          246  +      ** (iterating through a "PRAGMA database_list;" statement).
          247  +      */
          248  +      if( sqlite3_column_int(pCur->pDbList, 0)==1 ){
          249  +        zSql = sqlite3_mprintf(
          250  +            "SELECT name FROM sqlite_temp_master WHERE type=%Q",
          251  +            pVtab->pType->zObject
          252  +        );
          253  +      }else{
          254  +        sqlite3_stmt *pDbList = pCur->pDbList;
          255  +        zSql = sqlite3_mprintf(
          256  +            "SELECT name FROM %Q.sqlite_master WHERE type=%Q",
          257  +             sqlite3_column_text(pDbList, 1), pVtab->pType->zObject
          258  +        );
          259  +      }
          260  +      if( !zSql ){
          261  +        rc = SQLITE_NOMEM;
          262  +        goto next_exit;
          263  +      }
          264  +
          265  +      rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pTableList, 0);
          266  +      sqlite3_free(zSql);
          267  +      if( rc!=SQLITE_OK ) goto next_exit;
          268  +    }
          269  +
          270  +    /* Set zSql to the SQL to the table_info pragma for the table currently
          271  +    ** identified by the rows pointed to by statements pCur->pDbList and
          272  +    ** pCur->pTableList.
          273  +    */
          274  +    zSql = sqlite3_mprintf(pVtab->pType->zPragma,
          275  +        sqlite3_column_text(pCur->pDbList, 1),
          276  +        sqlite3_column_text(pCur->pTableList, 0)
          277  +    );
          278  +
          279  +    if( !zSql ){
          280  +      rc = SQLITE_NOMEM;
          281  +      goto next_exit;
          282  +    }
          283  +    rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pColumnList, 0);
          284  +    sqlite3_free(zSql);
          285  +    if( rc!=SQLITE_OK ) goto next_exit;
          286  +  }
          287  +  pCur->rowid++;
          288  +
          289  +next_exit:
          290  +  /* TODO: Handle rc */
          291  +  return rc;
          292  +}
          293  +
          294  +/*
          295  +** Reset a schema table cursor.
          296  +*/
          297  +static int schemaFilter(
          298  +  sqlite3_vtab_cursor *pVtabCursor, 
          299  +  int idxNum, const char *idxStr,
          300  +  int argc, sqlite3_value **argv
          301  +){
          302  +  int rc;
          303  +  schema_vtab *pVtab = (schema_vtab *)(pVtabCursor->pVtab);
          304  +  schema_cursor *pCur = (schema_cursor *)pVtabCursor;
          305  +  pCur->rowid = 0;
          306  +  finalize(&pCur->pTableList);
          307  +  finalize(&pCur->pColumnList);
          308  +  finalize(&pCur->pDbList);
          309  +  rc = sqlite3_prepare(pVtab->db,"SELECT 0, 'main'", -1, &pCur->pDbList, 0);
          310  +  return (rc==SQLITE_OK ? schemaNext(pVtabCursor) : rc);
          311  +}
          312  +
          313  +/*
          314  +** Analyse the WHERE condition.
          315  +*/
          316  +static int schemaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
          317  +  return SQLITE_OK;
          318  +}
          319  +
          320  +/*
          321  +** A virtual table module that merely echos method calls into TCL
          322  +** variables.
          323  +*/
          324  +static sqlite3_module schemaModule = {
          325  +  0,                           /* iVersion */
          326  +  schemaCreate,
          327  +  schemaCreate,
          328  +  schemaBestIndex,
          329  +  schemaDestroy,
          330  +  schemaDestroy,
          331  +  schemaOpen,                  /* xOpen - open a cursor */
          332  +  schemaClose,                 /* xClose - close a cursor */
          333  +  schemaFilter,                /* xFilter - configure scan constraints */
          334  +  schemaNext,                  /* xNext - advance a cursor */
          335  +  schemaEof,                   /* xEof */
          336  +  schemaColumn,                /* xColumn - read data */
          337  +  schemaRowid,                 /* xRowid - read data */
          338  +  0,                           /* xUpdate */
          339  +  0,                           /* xBegin */
          340  +  0,                           /* xSync */
          341  +  0,                           /* xCommit */
          342  +  0,                           /* xRollback */
          343  +  0,                           /* xFindMethod */
          344  +  0,                           /* xRename */
          345  +};
          346  +
          347  +/*
          348  +** Extension load function.
          349  +*/
          350  +static int installSchemaModule(sqlite3 *db){
          351  +  sqlite3_create_module(db, "schema", &schemaModule, 0);
          352  +  return 0;
          353  +}
          354  +
          355  +/**************************************************************************
          356  +***************************************************************************
          357  +** End of virtual table implementations.
          358  +** Start of SQL user function implementations.
          359  +*/
          360  +
          361  +/*
          362  +**   sj(zValue, zJoin)
          363  +**
          364  +** The following block contains the implementation of an aggregate 
          365  +** function that returns a string. Each time the function is stepped, 
          366  +** it appends data to an internal buffer. When the aggregate is finalized,
          367  +** the contents of the buffer are returned.
          368  +**
          369  +** The first time the aggregate is stepped the buffer is set to a copy
          370  +** of the first argument. The second time and subsequent times it is
          371  +** stepped a copy of the second argument is appended to the buffer, then
          372  +** a copy of the first.
          373  +**
          374  +** Example:
          375  +**
          376  +**   INSERT INTO t1(a) VALUES('1');
          377  +**   INSERT INTO t1(a) VALUES('2');
          378  +**   INSERT INTO t1(a) VALUES('3');
          379  +**   SELECT sj(a, ', ') FROM t1;
          380  +**
          381  +**     =>  "1, 2, 3"
          382  +**
          383  +*/
          384  +struct StrBuffer {
          385  +  char *zBuf;
          386  +};
          387  +typedef struct StrBuffer StrBuffer;
          388  +static void joinFinalize(sqlite3_context *context){
          389  +  StrBuffer *p;
          390  +  p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer));
          391  +  sqlite3_result_text(context, p->zBuf, -1, SQLITE_TRANSIENT);
          392  +  sqlite3_free(p->zBuf);
          393  +}
          394  +static void joinStep(
          395  +  sqlite3_context *context,
          396  +  int argc,
          397  +  sqlite3_value **argv
          398  +){
          399  +  StrBuffer *p;
          400  +  p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer));
          401  +  if( p->zBuf==0 ){
          402  +    p->zBuf = sqlite3_mprintf("%s", sqlite3_value_text(argv[0]));
          403  +  }else{
          404  +    char *zTmp = p->zBuf;
          405  +    p->zBuf = sqlite3_mprintf("%s%s%s", 
          406  +        zTmp, sqlite3_value_text(argv[1]), sqlite3_value_text(argv[0])
          407  +    );
          408  +    sqlite3_free(zTmp);
          409  +  }
          410  +}
          411  +
          412  +/*
          413  +**   dq(zString)
          414  +**
          415  +** This scalar function accepts a single argument and interprets it as
          416  +** a text value. The return value is the argument enclosed in double
          417  +** quotes. If any double quote characters are present in the argument, 
          418  +** these are escaped.
          419  +**
          420  +**   dq('the raven "Nevermore."') == '"the raven ""Nevermore."""'
          421  +*/
          422  +static void doublequote(
          423  +  sqlite3_context *context, 
          424  +  int argc, 
          425  +  sqlite3_value **argv
          426  +){
          427  +  int ii;
          428  +  char *zOut;
          429  +  char *zCsr;
          430  +  const char *zIn = (const char *)sqlite3_value_text(argv[0]);
          431  +  int nIn = sqlite3_value_bytes(argv[0]);
          432  +
          433  +  zOut = sqlite3_malloc(nIn*2+3);
          434  +  zCsr = zOut;
          435  +  *zCsr++ = '"';
          436  +  for(ii=0; ii<nIn; ii++){
          437  +    *zCsr++ = zIn[ii];
          438  +    if( zIn[ii]=='"' ){
          439  +      *zCsr++ = '"';
          440  +    }
          441  +  }
          442  +  *zCsr++ = '"';
          443  +  *zCsr++ = '\0';
          444  +
          445  +  sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT);
          446  +  sqlite3_free(zOut);
          447  +}
          448  +
          449  +/*
          450  +**   multireplace(zString, zSearch1, zReplace1, ...)
          451  +*/
          452  +static void multireplace(
          453  +  sqlite3_context *context, 
          454  +  int argc, 
          455  +  sqlite3_value **argv
          456  +){
          457  +  int i = 0;
          458  +  char *zOut = 0;
          459  +  int nOut = 0;
          460  +  int nMalloc = 0;
          461  +  const char *zIn = (const char *)sqlite3_value_text(argv[0]);
          462  +  int nIn = sqlite3_value_bytes(argv[0]);
          463  +
          464  +  while( i<nIn ){
          465  +    const char *zCopy = &zIn[i];
          466  +    int nCopy = 1;
          467  +    int nReplace = 1;
          468  +    int j;
          469  +    for(j=1; j<(argc-1); j+=2){
          470  +      const char *z = (const char *)sqlite3_value_text(argv[j]);
          471  +      int n = sqlite3_value_bytes(argv[j]);
          472  +      if( n<=(nIn-i) && 0==strncmp(z, zCopy, n) ){
          473  +        zCopy = (const char *)sqlite3_value_text(argv[j+1]);
          474  +        nCopy = sqlite3_value_bytes(argv[j+1]);
          475  +        nReplace = n;
          476  +        break;
          477  +      }
          478  +    }
          479  +    if( (nOut+nCopy)>nMalloc ){
          480  +      nMalloc += (nMalloc + 16);
          481  +      zOut = (char *)sqlite3_realloc(zOut, nMalloc);
          482  +    }
          483  +    memcpy(&zOut[nOut], zCopy, nCopy);
          484  +    i += nReplace;
          485  +    nOut += nCopy;
          486  +  }
          487  +
          488  +  sqlite3_result_text(context, zOut, nOut, SQLITE_TRANSIENT);
          489  +  sqlite3_free(zOut);
          490  +}
          491  +
          492  +/**************************************************************************
          493  +***************************************************************************
          494  +** End of SQL user function implementations.
          495  +** Start of application implementation.
          496  +*/
          497  +
          498  +typedef struct Options Options;
          499  +struct Options {
          500  +  char *zDb;
          501  +  int ignoreErrors;
          502  +  int noDrop;
          503  +};
          504  +
          505  +/*
          506  +** Print out a usage message for the command line and exit. This is
          507  +** called from processCmdLine() if the program is invoked incorrectly.
          508  +*/
          509  +static int usage(char *zProgram){
          510  +  fprintf(stderr, 
          511  +      "Usage: %s ?--ignore-errors? ?--no-drop? <database file>\n", zProgram
          512  +  );
          513  +  exit(-1);
          514  +}
          515  +
          516  +static void processCmdLine(int nArg, char **azArg, Options *p){
          517  +  int i;
          518  +  assert( nArg>0 );
          519  +  if( nArg<2 ){
          520  +    usage(azArg[0]);
          521  +  }
          522  +  for(i=1; i<(nArg-1); i++){
          523  +    char *z = azArg[i];
          524  +    if( 0==strcmp(z, "--ignore-errors") ){
          525  +      p->ignoreErrors = 1;
          526  +    }
          527  +    else if( 0==strcmp(z, "--no-drop") ){
          528  +      p->noDrop = 1;
          529  +    }
          530  +    else usage(azArg[0]);
          531  +  }
          532  +  p->zDb = azArg[nArg-1];
          533  +}
          534  +
          535  +/*
          536  +** A callback for sqlite3_exec() that prints its first argument to
          537  +** stdout followed by a newline.
          538  +*/
          539  +static int printString(void *p, int nArg, char **azArg, char **azCol){
          540  +  printf("%s\n", azArg[0]);
          541  +  return SQLITE_OK;
          542  +}
          543  +
          544  +int detectSchemaProblem(
          545  +  sqlite3 *db,                   /* Database connection */
          546  +  const char *zMessage,          /* English language error message */
          547  +  const char *zSql,              /* SQL statement to run */
          548  +  int *pHasErrors                /* Set *pHasErrors==1 if errors found */
          549  +){
          550  +  sqlite3_stmt *pStmt;
          551  +  int rc;
          552  +  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          553  +  if( rc!=SQLITE_OK ){
          554  +    return rc;
          555  +  }
          556  +  while( SQLITE_ROW==sqlite3_step(pStmt) ){
          557  +    char *zDel;
          558  +    int iFk = sqlite3_column_int(pStmt, 0);
          559  +    const char *zTab = (const char *)sqlite3_column_text(pStmt, 1);
          560  +    fprintf(stderr, "Error in table %s: %s\n", zTab, zMessage);
          561  +    zDel = sqlite3_mprintf(
          562  +        "DELETE FROM temp.fkey WHERE from_tbl = %Q AND fkid = %d"
          563  +        , zTab, iFk
          564  +    );
          565  +    sqlite3_exec(db, zDel, 0, 0, 0);
          566  +    sqlite3_free(zDel);
          567  +    *pHasErrors = 1;
          568  +  }
          569  +  sqlite3_finalize(pStmt);
          570  +  return SQLITE_OK;
          571  +}
          572  +
          573  +/*
          574  +** Create and populate temporary table "fkey".
          575  +*/
          576  +static int populateTempTable(sqlite3 *db, char **pzErr, int *pHasErrors){
          577  +  int rc;
          578  +
          579  +  rc = sqlite3_exec(db, 
          580  +      "CREATE VIRTUAL TABLE temp.v_fkey USING schema(foreign_key_list);"
          581  +      "CREATE VIRTUAL TABLE temp.v_col USING schema(table_info);"
          582  +      "CREATE VIRTUAL TABLE temp.v_idxlist USING schema(index_list);"
          583  +      "CREATE VIRTUAL TABLE temp.v_idxinfo USING schema(index_info);"
          584  +
          585  +      "CREATE TABLE temp.fkey AS "
          586  +        "SELECT from_tbl, to_tbl, fkid, from_col, to_col, on_update, on_delete "
          587  +        "FROM temp.v_fkey WHERE database = 'main';"
          588  +      "CREATE TABLE temp.col AS "
          589  +        "SELECT * FROM temp.v_col WHERE database = 'main';"
          590  +
          591  +      , 0, 0, pzErr
          592  +  );
          593  +  if( rc!=SQLITE_OK ) return rc;
          594  +
          595  +  rc = detectSchemaProblem(db, "foreign key columns do not exist",
          596  +    "SELECT fkid, from_tbl "
          597  +    "FROM temp.fkey "
          598  +    "WHERE to_col IS NOT NULL AND NOT EXISTS (SELECT 1 "
          599  +        "FROM temp.col WHERE tablename=to_tbl AND name==to_col"
          600  +    ")", pHasErrors
          601  +  );
          602  +  if( rc!=SQLITE_OK ) return rc;
          603  +
          604  +  /* At this point the temp.fkey table is mostly populated. If any foreign
          605  +  ** keys were specified so that they implicitly refer to they primary
          606  +  ** key of the parent table, the "to_col" values of the temp.fkey rows
          607  +  ** are still set to NULL.
          608  +  **
          609  +  ** This is easily fixed for single column primary keys, but not for
          610  +  ** composites. With a composite primary key, there is no way to reliably
          611  +  ** query sqlite for the order in which the columns that make up the
          612  +  ** composite key were declared i.e. there is no way to tell if the
          613  +  ** schema actually contains "PRIMARY KEY(a, b)" or "PRIMARY KEY(b, a)".
          614  +  ** Therefore, this case is not handled. The following function call
          615  +  ** detects instances of this case.
          616  +  */
          617  +  rc = detectSchemaProblem(db, "implicit mapping to composite primary key",
          618  +    "SELECT fkid, from_tbl "
          619  +    "FROM temp.fkey "
          620  +    "WHERE to_col IS NULL "
          621  +    "GROUP BY fkid, from_tbl HAVING count(*) > 1", pHasErrors
          622  +  );
          623  +  if( rc!=SQLITE_OK ) return rc;
          624  +
          625  +  /* Detect attempts to implicitly map to the primary key of a table 
          626  +  ** that has no primary key column.
          627  +  */
          628  +  rc = detectSchemaProblem(db, "implicit mapping to non-existant primary key",
          629  +    "SELECT fkid, from_tbl "
          630  +    "FROM temp.fkey "
          631  +    "WHERE to_col IS NULL AND NOT EXISTS "
          632  +      "(SELECT 1 FROM temp.col WHERE pk AND tablename = temp.fkey.to_tbl)"
          633  +    , pHasErrors
          634  +  );
          635  +  if( rc!=SQLITE_OK ) return rc;
          636  +
          637  +  /* Fix all the implicit primary key mappings in the temp.fkey table. */
          638  +  rc = sqlite3_exec(db, 
          639  +    "UPDATE temp.fkey SET to_col = "
          640  +      "(SELECT name FROM temp.col WHERE pk AND tablename=temp.fkey.to_tbl)"
          641  +    " WHERE to_col IS NULL;"
          642  +    , 0, 0, pzErr
          643  +  );
          644  +  if( rc!=SQLITE_OK ) return rc;
          645  +
          646  +  /* Now check that all all parent keys are either primary keys or 
          647  +  ** subject to a unique constraint.
          648  +  */
          649  +  rc = sqlite3_exec(db, 
          650  +    "CREATE TABLE temp.idx2 AS SELECT "
          651  +      "il.tablename AS tablename,"
          652  +      "ii.indexname AS indexname,"
          653  +      "ii.name AS col "
          654  +      "FROM temp.v_idxlist AS il, temp.v_idxinfo AS ii "
          655  +      "WHERE il.isunique AND il.database='main' AND ii.indexname = il.name;"
          656  +    "INSERT INTO temp.idx2 SELECT tablename, 'pk', name FROM temp.col WHERE pk;"
          657  +
          658  +    "CREATE TABLE temp.idx AS SELECT "
          659  +      "tablename, indexname, sj(dq(col),',') AS cols "
          660  +      "FROM (SELECT * FROM temp.idx2 ORDER BY col) " 
          661  +      "GROUP BY tablename, indexname;"
          662  +
          663  +    "CREATE TABLE temp.fkey2 AS SELECT "
          664  +        "fkid, from_tbl, to_tbl, sj(dq(to_col),',') AS cols "
          665  +        "FROM (SELECT * FROM temp.fkey ORDER BY to_col) " 
          666  +        "GROUP BY fkid, from_tbl;"
          667  +    , 0, 0, pzErr
          668  +  );
          669  +  if( rc!=SQLITE_OK ) return rc;
          670  +  rc = detectSchemaProblem(db, "foreign key is not unique",
          671  +    "SELECT fkid, from_tbl "
          672  +    "FROM temp.fkey2 "
          673  +    "WHERE NOT EXISTS (SELECT 1 "
          674  +        "FROM temp.idx WHERE tablename=to_tbl AND fkey2.cols==idx.cols"
          675  +    ")", pHasErrors
          676  +  );
          677  +  if( rc!=SQLITE_OK ) return rc;
          678  +
          679  +  return rc;
          680  +}
          681  +
          682  +int main(int argc, char **argv){
          683  +  sqlite3 *db;
          684  +  Options opt = {0, 0, 0};
          685  +  int rc;
          686  +  int hasErrors = 0;
          687  +  char *zErr = 0;
          688  +  const int enc = SQLITE_UTF8;
          689  +
          690  +  const char *zSql =
          691  +    "SELECT multireplace('"
          692  +
          693  +      "-- Triggers for foreign key mapping:\n"
          694  +      "--\n"
          695  +      "--     /from_readable/ REFERENCES /to_readable/\n"
          696  +      "--     on delete /on_delete/\n"
          697  +      "--     on update /on_update/\n"
          698  +      "--\n"
          699  +
          700  +      /* The "BEFORE INSERT ON <referencing>" trigger. This trigger's job is to
          701  +      ** throw an exception if the user tries to insert a row into the
          702  +      ** referencing table for which there is no corresponding row in
          703  +      ** the referenced table.
          704  +      */
          705  +      "CREATE TRIGGER /name/_insert_referencing BEFORE INSERT ON /tbl/ WHEN \n"
          706  +      "    /key_notnull/ AND NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" 
          707  +      "BEGIN\n"
          708  +        "  SELECT RAISE(ABORT, ''constraint failed'');\n"
          709  +      "END;\n"
          710  +
          711  +      /* The "BEFORE UPDATE ON <referencing>" trigger. This trigger's job 
          712  +      ** is to throw an exception if the user tries to update a row in the
          713  +      ** referencing table causing it to correspond to no row in the
          714  +      ** referenced table.
          715  +      */
          716  +      "CREATE TRIGGER /name/_update_referencing BEFORE\n"
          717  +      "    UPDATE OF /rkey_list/ ON /tbl/ WHEN \n"
          718  +      "    /key_notnull/ AND \n"
          719  +      "    NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" 
          720  +      "BEGIN\n"
          721  +        "  SELECT RAISE(ABORT, ''constraint failed'');\n"
          722  +      "END;\n"
          723  +
          724  +
          725  +      /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job 
          726  +      ** is to detect when a row is deleted from the referenced table to 
          727  +      ** which rows in the referencing table correspond. The action taken
          728  +      ** depends on the value of the 'ON DELETE' clause.
          729  +      */
          730  +      "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n"
          731  +      "    EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n"
          732  +      "BEGIN\n"
          733  +      "  /delete_action/\n"
          734  +      "END;\n"
          735  +
          736  +      /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job 
          737  +      ** is to detect when the key columns of a row in the referenced table 
          738  +      ** to which one or more rows in the referencing table correspond are
          739  +      ** updated. The action taken depends on the value of the 'ON UPDATE' 
          740  +      ** clause.
          741  +      */
          742  +      "CREATE TRIGGER /name/_update_referenced AFTER\n"
          743  +      "    UPDATE OF /fkey_list/ ON /ref/ WHEN \n"
          744  +      "    EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n"
          745  +      "BEGIN\n"
          746  +      "  /update_action/\n"
          747  +      "END;\n"
          748  +    "'"
          749  +
          750  +    /* These are used in the SQL comment written above each set of triggers */
          751  +    ", '/from_readable/',  from_tbl || '(' || sj(from_col, ', ') || ')'"
          752  +    ", '/to_readable/',    to_tbl || '(' || sj(to_col, ', ') || ')'"
          753  +    ", '/on_delete/', on_delete"
          754  +    ", '/on_update/', on_update"
          755  +
          756  +    ", '/name/',   'genfkey' || min(rowid)"
          757  +    ", '/tbl/',    dq(from_tbl)"
          758  +    ", '/ref/',    dq(to_tbl)"
          759  +    ", '/key_notnull/', sj('new.' || dq(from_col) || ' IS NOT NULL', ' AND ')"
          760  +
          761  +    ", '/fkey_list/', sj(to_col, ', ')"
          762  +    ", '/rkey_list/', sj(from_col, ', ')"
          763  +
          764  +    ", '/cond1/',  sj(multireplace('new./from/ == /to/'"
          765  +                   ", '/from/', dq(from_col)"
          766  +                   ", '/to/',   dq(to_col)"
          767  +                   "), ' AND ')"
          768  +    ", '/cond2/',  sj(multireplace('old./to/ == /from/'"
          769  +                   ", '/from/', dq(from_col)"
          770  +                   ", '/to/',   dq(to_col)"
          771  +                   "), ' AND ')"
          772  +
          773  +    ", '/update_action/', CASE on_update "
          774  +      "WHEN 'SET NULL' THEN "
          775  +        "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
          776  +        ", '/setlist/', sj(from_col||' = NULL',', ')"
          777  +        ", '/tbl/',     dq(from_tbl)"
          778  +        ", '/where/',   sj(from_col||' = old.'||dq(to_col),' AND ')"
          779  +        ")"
          780  +      "WHEN 'CASCADE' THEN "
          781  +        "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
          782  +        ", '/setlist/', sj(dq(from_col)||' = new.'||dq(to_col),', ')"
          783  +        ", '/tbl/',     dq(from_tbl)"
          784  +        ", '/where/',   sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
          785  +        ")"
          786  +      "ELSE "
          787  +      "  'SELECT RAISE(ABORT, ''constraint failed'');'"
          788  +      "END "
          789  +
          790  +    ", '/delete_action/', CASE on_delete "
          791  +      "WHEN 'SET NULL' THEN "
          792  +        "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
          793  +        ", '/setlist/', sj(from_col||' = NULL',', ')"
          794  +        ", '/tbl/',     dq(from_tbl)"
          795  +        ", '/where/',   sj(from_col||' = old.'||dq(to_col),' AND ')"
          796  +        ")"
          797  +      "WHEN 'CASCADE' THEN "
          798  +        "multireplace('DELETE FROM /tbl/ WHERE /where/;' "
          799  +        ", '/tbl/',     dq(from_tbl)"
          800  +        ", '/where/',   sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
          801  +        ")"
          802  +      "ELSE "
          803  +      "  'SELECT RAISE(ABORT, ''constraint failed'');'"
          804  +      "END "
          805  +
          806  +    ") FROM temp.fkey "
          807  +    "GROUP BY from_tbl, fkid"
          808  +  ;
          809  +
          810  +  processCmdLine(argc, argv, &opt);
          811  +
          812  +  /* Open the database handle. */
          813  +  rc = sqlite3_open_v2(opt.zDb, &db, SQLITE_OPEN_READONLY, 0);
          814  +  if( rc!=SQLITE_OK ){
          815  +    fprintf(stderr, "Error opening database file: %s\n", sqlite3_errmsg(db));
          816  +    return -1;
          817  +  }
          818  +
          819  +  /* Create the special scalar and aggregate functions used by this program. */
          820  +  sqlite3_create_function(db, "dq", 1, enc, 0, doublequote, 0, 0);
          821  +  sqlite3_create_function(db, "multireplace", -1, enc, db, multireplace, 0, 0);
          822  +  sqlite3_create_function(db, "sj", 2, enc, 0, 0, joinStep, joinFinalize);
          823  +
          824  +  /* Install the "schema" virtual table module */
          825  +  installSchemaModule(db);
          826  +
          827  +  /* Create and populate a temp table with the information required to
          828  +  ** build the foreign key triggers. See function populateTempTable()
          829  +  ** for details.
          830  +  */
          831  +  rc = populateTempTable(db, &zErr, &hasErrors);
          832  +  if( rc!=SQLITE_OK ){
          833  +    fprintf(stderr, "Error reading database: %s\n", zErr);
          834  +    return -1;
          835  +  }
          836  +  if( hasErrors && opt.ignoreErrors==0 ){
          837  +    return -1;
          838  +  }
          839  +
          840  +  printf("BEGIN;\n");
          841  +
          842  +  /* Unless the --no-drop option was specified, generate DROP TRIGGER
          843  +  ** statements to drop any triggers in the database generated by a
          844  +  ** previous run of this program.
          845  +  */
          846  +  if( opt.noDrop==0 ){
          847  +    rc = sqlite3_exec(db, 
          848  +      "SELECT 'DROP TRIGGER' || ' ' || dq(name) || ';'"
          849  +      "FROM sqlite_master "
          850  +      "WHERE type='trigger' AND substr(name, 0, 7) == 'genfkey'"
          851  +      , printString, 0, 0
          852  +    );
          853  +    if( rc!=SQLITE_OK ){
          854  +      const char *zMsg = sqlite3_errmsg(db);
          855  +      fprintf(stderr, "Generating drop triggers failed: %s\n", zMsg);
          856  +      return -1;
          857  +    }
          858  +  }
          859  +
          860  +  /* Run the main query to create the trigger definitions. */
          861  +  rc = sqlite3_exec(db, zSql, printString, 0, 0);
          862  +  if( rc!=SQLITE_OK ){
          863  +    fprintf(stderr, "Generating triggers failed: %s\n", sqlite3_errmsg(db));
          864  +    return -1;
          865  +  }
          866  +
          867  +  printf("COMMIT;\n");
          868  +  return 0;
          869  +}
          870  +

Added tool/genfkey.test.

            1  +
            2  +package require sqlite3
            3  +
            4  +proc do_test {name cmd expected} {
            5  +  puts -nonewline "$name ..."
            6  +  set res [uplevel $cmd]
            7  +  if {$res eq $expected} {
            8  +    puts Ok
            9  +  } else {
           10  +    puts Error
           11  +    puts "  Got: $res"
           12  +    puts "  Expected: $expected"
           13  +    exit
           14  +  }
           15  +}
           16  +
           17  +proc execsql {sql} {
           18  +  uplevel [list db eval $sql]
           19  +}
           20  +
           21  +proc catchsql {sql} {
           22  +  set rc [catch {uplevel [list db eval $sql]} msg]
           23  +  list $rc $msg
           24  +}
           25  +
           26  +file delete -force test.db test.db.journal
           27  +sqlite3 db test.db
           28  +
           29  +# The following tests - genfkey-1.* - test RESTRICT foreign keys.
           30  +#
           31  +do_test genfkey-1.1 {
           32  +  execsql {
           33  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
           34  +    CREATE TABLE t2(e REFERENCES t1, f);
           35  +    CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
           36  +  }
           37  +} {}
           38  +do_test genfkey-1.2 {
           39  +  execsql [exec ./genfkey test.db]
           40  +} {}
           41  +do_test genfkey-1.3 {
           42  +  catchsql { INSERT INTO t2 VALUES(1, 2) }
           43  +} {1 {constraint failed}}
           44  +do_test genfkey-1.4 {
           45  +  execsql {
           46  +    INSERT INTO t1 VALUES(1, 2, 3);
           47  +    INSERT INTO t2 VALUES(1, 2);
           48  +  }
           49  +} {}
           50  +do_test genfkey-1.5 {
           51  +  execsql { INSERT INTO t2 VALUES(NULL, 3) }
           52  +} {}
           53  +do_test genfkey-1.6 {
           54  +  catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
           55  +} {1 {constraint failed}}
           56  +do_test genfkey-1.7 {
           57  +  execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
           58  +} {}
           59  +do_test genfkey-1.8 {
           60  +  execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
           61  +} {}
           62  +do_test genfkey-1.9 {
           63  +  catchsql { UPDATE t1 SET a = 10 }
           64  +} {1 {constraint failed}}
           65  +do_test genfkey-1.9a {
           66  +  catchsql { UPDATE t1 SET a = NULL }
           67  +} {1 {datatype mismatch}}
           68  +do_test genfkey-1.10 {
           69  +  catchsql { DELETE FROM t1 }
           70  +} {1 {constraint failed}}
           71  +do_test genfkey-1.11 {
           72  +  execsql { UPDATE t2 SET e = NULL }
           73  +} {}
           74  +do_test genfkey-1.12 {
           75  +  execsql { 
           76  +    UPDATE t1 SET a = 10 ;
           77  +    DELETE FROM t1;
           78  +    DELETE FROM t2;
           79  +  }
           80  +} {}
           81  +
           82  +do_test genfkey-1.13 {
           83  +  execsql {
           84  +    INSERT INTO t3 VALUES(1, NULL, NULL);
           85  +    INSERT INTO t3 VALUES(1, 2, NULL);
           86  +    INSERT INTO t3 VALUES(1, NULL, 3);
           87  +  }
           88  +} {}
           89  +do_test genfkey-1.14 {
           90  +  catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
           91  +} {1 {constraint failed}}
           92  +do_test genfkey-1.15 {
           93  +  execsql { 
           94  +    INSERT INTO t1 VALUES(1, 1, 4);
           95  +    INSERT INTO t3 VALUES(3, 1, 4);
           96  +  }
           97  +} {}
           98  +do_test genfkey-1.16 {
           99  +  catchsql { DELETE FROM t1 }
          100  +} {1 {constraint failed}}
          101  +do_test genfkey-1.17 {
          102  +  catchsql { UPDATE t1 SET b = 10}
          103  +} {1 {constraint failed}}
          104  +do_test genfkey-1.18 {
          105  +  execsql { UPDATE t1 SET a = 10}
          106  +} {}
          107  +do_test genfkey-1.19 {
          108  +  catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
          109  +} {1 {constraint failed}}
          110  +
          111  +do_test genfkey-1.X {
          112  +  execsql {
          113  +    DROP TABLE t1;
          114  +    DROP TABLE t2;
          115  +    DROP TABLE t3;
          116  +  }
          117  +} {}
          118  +
          119  +# The following tests - genfkey-2.* - test CASCADE foreign keys.
          120  +#
          121  +do_test genfkey-2.1 {
          122  +  execsql {
          123  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
          124  +    CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
          125  +    CREATE TABLE t3(g, h, i, 
          126  +        FOREIGN KEY (h, i) 
          127  +        REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
          128  +    );
          129  +  }
          130  +} {}
          131  +do_test genfkey-2.2 {
          132  +  execsql [exec ./genfkey test.db]
          133  +} {}
          134  +do_test genfkey-2.3 {
          135  +  execsql {
          136  +    INSERT INTO t1 VALUES(1, 2, 3);
          137  +    INSERT INTO t1 VALUES(4, 5, 6);
          138  +    INSERT INTO t2 VALUES(1, 'one');
          139  +    INSERT INTO t2 VALUES(4, 'four');
          140  +  }
          141  +} {}
          142  +do_test genfkey-2.4 {
          143  +  execsql {
          144  +    UPDATE t1 SET a = 2 WHERE a = 1;
          145  +    SELECT * FROM t2;
          146  +  }
          147  +} {2 one 4 four}
          148  +do_test genfkey-2.5 {
          149  +  execsql {
          150  +    DELETE FROM t1 WHERE a = 4;
          151  +    SELECT * FROM t2;
          152  +  }
          153  +} {2 one}
          154  +do_test genfkey-2.6 {
          155  +  execsql {
          156  +    INSERT INTO t3 VALUES('hello', 2, 3);
          157  +    UPDATE t1 SET c = 2;
          158  +    SELECT * FROM t3;
          159  +  }
          160  +} {hello 2 2}
          161  +do_test genfkey-2.7 {
          162  +  execsql {
          163  +    DELETE FROM t1;
          164  +    SELECT * FROM t3;
          165  +  }
          166  +} {}
          167  +do_test genfkey-2.X {
          168  +  execsql {
          169  +    DROP TABLE t1;
          170  +    DROP TABLE t2;
          171  +    DROP TABLE t3;
          172  +  }
          173  +} {}
          174  +
          175  +
          176  +# The following tests - genfkey-3.* - test SET NULL foreign keys.
          177  +#
          178  +do_test genfkey-3.1 {
          179  +  execsql {
          180  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
          181  +    CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
          182  +    CREATE TABLE t3(g, h, i, 
          183  +        FOREIGN KEY (h, i) 
          184  +        REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
          185  +    );
          186  +  }
          187  +} {}
          188  +do_test genfkey-3.2 {
          189  +  execsql [exec ./genfkey test.db]
          190  +} {}
          191  +do_test genfkey-3.3 {
          192  +  execsql {
          193  +    INSERT INTO t1 VALUES(1, 2, 3);
          194  +    INSERT INTO t1 VALUES(4, 5, 6);
          195  +    INSERT INTO t2 VALUES(1, 'one');
          196  +    INSERT INTO t2 VALUES(4, 'four');
          197  +  }
          198  +} {}
          199  +do_test genfkey-3.4 {
          200  +  execsql {
          201  +    UPDATE t1 SET a = 2 WHERE a = 1;
          202  +    SELECT * FROM t2;
          203  +  }
          204  +} {{} one 4 four}
          205  +do_test genfkey-3.5 {
          206  +  execsql {
          207  +    DELETE FROM t1 WHERE a = 4;
          208  +    SELECT * FROM t2;
          209  +  }
          210  +} {{} one {} four}
          211  +do_test genfkey-3.6 {
          212  +  execsql {
          213  +    INSERT INTO t3 VALUES('hello', 2, 3);
          214  +    UPDATE t1 SET c = 2;
          215  +    SELECT * FROM t3;
          216  +  }
          217  +} {hello {} {}}
          218  +do_test genfkey-2.7 {
          219  +  execsql {
          220  +    UPDATE t3 SET h = 2, i = 2;
          221  +    DELETE FROM t1;
          222  +    SELECT * FROM t3;
          223  +  }
          224  +} {hello {} {}}
          225  +do_test genfkey-3.X {
          226  +  execsql {
          227  +    DROP TABLE t1;
          228  +    DROP TABLE t2;
          229  +    DROP TABLE t3;
          230  +  }
          231  +} {}
          232  +
          233  +# The following tests - genfkey-4.* - test that errors in the schema
          234  +# are detected correctly.
          235  +#
          236  +do_test genfkey-4.1 {
          237  +  execsql {
          238  +    CREATE TABLE t1(a REFERENCES nosuchtable, b);
          239  +    CREATE TABLE t2(a REFERENCES t1, b);
          240  +
          241  +    CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b));
          242  +    CREATE TABLE t4(a, b, c, FOREIGN KEY(c, b) REFERENCES t3);
          243  +
          244  +    CREATE TABLE t5(a REFERENCES t4(d), b, c);
          245  +    CREATE TABLE t6(a REFERENCES t4(a), b, c);
          246  +    CREATE TABLE t7(a REFERENCES t3(a), b, c);
          247  +    CREATE TABLE t8(a REFERENCES nosuchtable(a), b, c);
          248  +  }
          249  +} {}
          250  +
          251  +do_test genfkey-4.X {
          252  +  set rc [catch {exec ./genfkey test.db} msg]
          253  +  list $rc $msg
          254  +} "1 {[string trim {
          255  +Error in table t5: foreign key columns do not exist
          256  +Error in table t8: foreign key columns do not exist
          257  +Error in table t4: implicit mapping to composite primary key
          258  +Error in table t1: implicit mapping to non-existant primary key
          259  +Error in table t2: implicit mapping to non-existant primary key
          260  +Error in table t6: foreign key is not unique
          261  +Error in table t7: foreign key is not unique
          262  +}]}"
          263  +