/ Check-in [0a59fb28]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the 'genfkey' functionality to the sqlite3 shell. Accessed using a new dot-command - ".genfkey". (CVS 6325)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0a59fb28b46e5d85c850d1dfa1385a4656e4dda5
User & Date: danielk1977 2009-02-25 15:22:03
Context
2009-02-25
15:43
Remove genfkey.c. Change genfkey.README to talk about the new shell dot-command, not the old standalone genfkey program. (CVS 6326) check-in: 36d69966 user: danielk1977 tags: trunk
15:22
Add the 'genfkey' functionality to the sqlite3 shell. Accessed using a new dot-command - ".genfkey". (CVS 6325) check-in: 0a59fb28 user: danielk1977 tags: trunk
08:56
Minor changes and coverge tests for "SELECT count(*)" optimization. (CVS 6324) check-in: a3695b98 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains code to implement the "sqlite" command line
    13     13   ** utility for accessing SQLite databases.
    14     14   **
    15         -** $Id: shell.c,v 1.201 2009/02/04 22:46:47 drh Exp $
           15  +** $Id: shell.c,v 1.202 2009/02/25 15:22:03 danielk1977 Exp $
    16     16   */
    17     17   #if defined(_WIN32) || defined(WIN32)
    18     18   /* This needs to come before any includes for MSVC compiler */
    19     19   #define _CRT_SECURE_NO_WARNINGS
    20     20   #endif
    21     21   
    22     22   #include <stdlib.h>
................................................................................
    69     69   #define isatty(x) 1
    70     70   #endif
    71     71   
    72     72   #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) && !defined(__RTP__) && !defined(_WRS_KERNEL)
    73     73   #include <sys/time.h>
    74     74   #include <sys/resource.h>
    75     75   
           76  +
           77  +/**************************************************************************
           78  +***************************************************************************
           79  +** Begin genfkey logic.
           80  +*/
           81  +#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined SQLITE_OMIT_SUBQUERY
           82  +
           83  +#define GENFKEY_ERROR         1
           84  +#define GENFKEY_DROPTRIGGER   2
           85  +#define GENFKEY_CREATETRIGGER 3
           86  +static int genfkey_create_triggers(sqlite3 *, const char *, void *,
           87  +  int (*)(void *, int, const char *)
           88  +);
           89  +
           90  +struct GenfkeyCb {
           91  +  void *pCtx;
           92  +  int eType;
           93  +  int (*xData)(void *, int, const char *);
           94  +};
           95  +typedef struct GenfkeyCb GenfkeyCb;
           96  +
           97  +/* The code in this file defines a sqlite3 virtual-table module that
           98  +** provides a read-only view of the current database schema. There is one
           99  +** row in the schema table for each column in the database schema.
          100  +*/
          101  +#define SCHEMA \
          102  +"CREATE TABLE x("                                                            \
          103  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
          104  +  "tablename,"         /* Name of table */                                   \
          105  +  "cid,"               /* Column number (from left-to-right, 0 upward) */    \
          106  +  "name,"              /* Column name */                                     \
          107  +  "type,"              /* Specified type (i.e. VARCHAR(32)) */               \
          108  +  "not_null,"          /* Boolean. True if NOT NULL was specified */         \
          109  +  "dflt_value,"        /* Default value for this column */                   \
          110  +  "pk"                 /* True if this column is part of the primary key */  \
          111  +")"
          112  +
          113  +#define SCHEMA2 \
          114  +"CREATE TABLE x("                                                            \
          115  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
          116  +  "from_tbl,"          /* Name of table */                                   \
          117  +  "fkid,"                                                                    \
          118  +  "seq,"                                                                     \
          119  +  "to_tbl,"                                                                  \
          120  +  "from_col,"                                                                \
          121  +  "to_col,"                                                                  \
          122  +  "on_update,"                                                               \
          123  +  "on_delete,"                                                               \
          124  +  "match"                                                                    \
          125  +")"
          126  +
          127  +#define SCHEMA3 \
          128  +"CREATE TABLE x("                                                            \
          129  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
          130  +  "tablename,"         /* Name of table */                                   \
          131  +  "seq,"                                                                     \
          132  +  "name,"                                                                    \
          133  +  "isunique"                                                                 \
          134  +")"
          135  +
          136  +#define SCHEMA4 \
          137  +"CREATE TABLE x("                                                            \
          138  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
          139  +  "indexname,"         /* Name of table */                                   \
          140  +  "seqno,"                                                                   \
          141  +  "cid,"                                                                     \
          142  +  "name"                                                                     \
          143  +")"
          144  +
          145  +#define SCHEMA5 \
          146  +"CREATE TABLE x("                                                            \
          147  +  "database,"          /* Name of database (i.e. main, temp etc.) */         \
          148  +  "triggername,"       /* Name of trigger */                                 \
          149  +  "dummy"              /* Unused */                                          \
          150  +")"
          151  +
          152  +typedef struct SchemaTable SchemaTable;
          153  +struct SchemaTable {
          154  +  const char *zName;
          155  +  const char *zObject;
          156  +  const char *zPragma;
          157  +  const char *zSchema;
          158  +} aSchemaTable[] = {
          159  +  { "table_info",       "table", "PRAGMA %Q.table_info(%Q)",       SCHEMA },
          160  +  { "foreign_key_list", "table", "PRAGMA %Q.foreign_key_list(%Q)", SCHEMA2 },
          161  +  { "index_list",       "table", "PRAGMA %Q.index_list(%Q)",       SCHEMA3 },
          162  +  { "index_info",       "index", "PRAGMA %Q.index_info(%Q)",       SCHEMA4 },
          163  +  { "trigger_list",     "trigger", "SELECT 1",                     SCHEMA5 },
          164  +  { 0, 0, 0, 0 }
          165  +};
          166  +
          167  +typedef struct schema_vtab schema_vtab;
          168  +typedef struct schema_cursor schema_cursor;
          169  +
          170  +/* A schema table object */
          171  +struct schema_vtab {
          172  +  sqlite3_vtab base;
          173  +  sqlite3 *db;
          174  +  SchemaTable *pType;
          175  +};
          176  +
          177  +/* A schema table cursor object */
          178  +struct schema_cursor {
          179  +  sqlite3_vtab_cursor base;
          180  +  sqlite3_stmt *pDbList;
          181  +  sqlite3_stmt *pTableList;
          182  +  sqlite3_stmt *pColumnList;
          183  +  int rowid;
          184  +};
          185  +
          186  +/*
          187  +** Table destructor for the schema module.
          188  +*/
          189  +static int schemaDestroy(sqlite3_vtab *pVtab){
          190  +  sqlite3_free(pVtab);
          191  +  return 0;
          192  +}
          193  +
          194  +/*
          195  +** Table constructor for the schema module.
          196  +*/
          197  +static int schemaCreate(
          198  +  sqlite3 *db,
          199  +  void *pAux,
          200  +  int argc, const char *const*argv,
          201  +  sqlite3_vtab **ppVtab,
          202  +  char **pzErr
          203  +){
          204  +  int rc = SQLITE_NOMEM;
          205  +  schema_vtab *pVtab;
          206  +  SchemaTable *pType = &aSchemaTable[0];
          207  +
          208  +  if( argc>3 ){
          209  +    int i;
          210  +    pType = 0;
          211  +    for(i=0; aSchemaTable[i].zName; i++){ 
          212  +      if( 0==strcmp(argv[3], aSchemaTable[i].zName) ){
          213  +        pType = &aSchemaTable[i];
          214  +      }
          215  +    }
          216  +    if( !pType ){
          217  +      return SQLITE_ERROR;
          218  +    }
          219  +  }
          220  +
          221  +  pVtab = sqlite3_malloc(sizeof(schema_vtab));
          222  +  if( pVtab ){
          223  +    memset(pVtab, 0, sizeof(schema_vtab));
          224  +    pVtab->db = (sqlite3 *)pAux;
          225  +    pVtab->pType = pType;
          226  +    rc = sqlite3_declare_vtab(db, pType->zSchema);
          227  +  }
          228  +  *ppVtab = (sqlite3_vtab *)pVtab;
          229  +  return rc;
          230  +}
          231  +
          232  +/*
          233  +** Open a new cursor on the schema table.
          234  +*/
          235  +static int schemaOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          236  +  int rc = SQLITE_NOMEM;
          237  +  schema_cursor *pCur;
          238  +  pCur = sqlite3_malloc(sizeof(schema_cursor));
          239  +  if( pCur ){
          240  +    memset(pCur, 0, sizeof(schema_cursor));
          241  +    *ppCursor = (sqlite3_vtab_cursor *)pCur;
          242  +    rc = SQLITE_OK;
          243  +  }
          244  +  return rc;
          245  +}
          246  +
          247  +/*
          248  +** Close a schema table cursor.
          249  +*/
          250  +static int schemaClose(sqlite3_vtab_cursor *cur){
          251  +  schema_cursor *pCur = (schema_cursor *)cur;
          252  +  sqlite3_finalize(pCur->pDbList);
          253  +  sqlite3_finalize(pCur->pTableList);
          254  +  sqlite3_finalize(pCur->pColumnList);
          255  +  sqlite3_free(pCur);
          256  +  return SQLITE_OK;
          257  +}
          258  +
          259  +static void columnToResult(sqlite3_context *ctx, sqlite3_stmt *pStmt, int iCol){
          260  +  switch( sqlite3_column_type(pStmt, iCol) ){
          261  +    case SQLITE_NULL:
          262  +      sqlite3_result_null(ctx);
          263  +      break;
          264  +    case SQLITE_INTEGER:
          265  +      sqlite3_result_int64(ctx, sqlite3_column_int64(pStmt, iCol));
          266  +      break;
          267  +    case SQLITE_FLOAT:
          268  +      sqlite3_result_double(ctx, sqlite3_column_double(pStmt, iCol));
          269  +      break;
          270  +    case SQLITE_TEXT: {
          271  +      const char *z = (const char *)sqlite3_column_text(pStmt, iCol);
          272  +      sqlite3_result_text(ctx, z, -1, SQLITE_TRANSIENT);
          273  +      break;
          274  +    }
          275  +  }
          276  +}
          277  +
          278  +/*
          279  +** Retrieve a column of data.
          280  +*/
          281  +static int schemaColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
          282  +  schema_cursor *pCur = (schema_cursor *)cur;
          283  +  switch( i ){
          284  +    case 0:
          285  +      columnToResult(ctx, pCur->pDbList, 1);
          286  +      break;
          287  +    case 1:
          288  +      columnToResult(ctx, pCur->pTableList, 0);
          289  +      break;
          290  +    default:
          291  +      columnToResult(ctx, pCur->pColumnList, i-2);
          292  +      break;
          293  +  }
          294  +  return SQLITE_OK;
          295  +}
          296  +
          297  +/*
          298  +** Retrieve the current rowid.
          299  +*/
          300  +static int schemaRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          301  +  schema_cursor *pCur = (schema_cursor *)cur;
          302  +  *pRowid = pCur->rowid;
          303  +  return SQLITE_OK;
          304  +}
          305  +
          306  +static int finalize(sqlite3_stmt **ppStmt){
          307  +  int rc = sqlite3_finalize(*ppStmt);
          308  +  *ppStmt = 0;
          309  +  return rc;
          310  +}
          311  +
          312  +static int schemaEof(sqlite3_vtab_cursor *cur){
          313  +  schema_cursor *pCur = (schema_cursor *)cur;
          314  +  return (pCur->pDbList ? 0 : 1);
          315  +}
          316  +
          317  +/*
          318  +** Advance the cursor to the next row.
          319  +*/
          320  +static int schemaNext(sqlite3_vtab_cursor *cur){
          321  +  int rc = SQLITE_OK;
          322  +  schema_cursor *pCur = (schema_cursor *)cur;
          323  +  schema_vtab *pVtab = (schema_vtab *)(cur->pVtab);
          324  +  char *zSql = 0;
          325  +
          326  +  while( !pCur->pColumnList || SQLITE_ROW!=sqlite3_step(pCur->pColumnList) ){
          327  +    if( SQLITE_OK!=(rc = finalize(&pCur->pColumnList)) ) goto next_exit;
          328  +
          329  +    while( !pCur->pTableList || SQLITE_ROW!=sqlite3_step(pCur->pTableList) ){
          330  +      if( SQLITE_OK!=(rc = finalize(&pCur->pTableList)) ) goto next_exit;
          331  +
          332  +      assert(pCur->pDbList);
          333  +      while( SQLITE_ROW!=sqlite3_step(pCur->pDbList) ){
          334  +        rc = finalize(&pCur->pDbList);
          335  +        goto next_exit;
          336  +      }
          337  +
          338  +      /* Set zSql to the SQL to pull the list of tables from the 
          339  +      ** sqlite_master (or sqlite_temp_master) table of the database
          340  +      ** identfied by the row pointed to by the SQL statement pCur->pDbList
          341  +      ** (iterating through a "PRAGMA database_list;" statement).
          342  +      */
          343  +      if( sqlite3_column_int(pCur->pDbList, 0)==1 ){
          344  +        zSql = sqlite3_mprintf(
          345  +            "SELECT name FROM sqlite_temp_master WHERE type=%Q",
          346  +            pVtab->pType->zObject
          347  +        );
          348  +      }else{
          349  +        sqlite3_stmt *pDbList = pCur->pDbList;
          350  +        zSql = sqlite3_mprintf(
          351  +            "SELECT name FROM %Q.sqlite_master WHERE type=%Q",
          352  +             sqlite3_column_text(pDbList, 1), pVtab->pType->zObject
          353  +        );
          354  +      }
          355  +      if( !zSql ){
          356  +        rc = SQLITE_NOMEM;
          357  +        goto next_exit;
          358  +      }
          359  +
          360  +      rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pTableList, 0);
          361  +      sqlite3_free(zSql);
          362  +      if( rc!=SQLITE_OK ) goto next_exit;
          363  +    }
          364  +
          365  +    /* Set zSql to the SQL to the table_info pragma for the table currently
          366  +    ** identified by the rows pointed to by statements pCur->pDbList and
          367  +    ** pCur->pTableList.
          368  +    */
          369  +    zSql = sqlite3_mprintf(pVtab->pType->zPragma,
          370  +        sqlite3_column_text(pCur->pDbList, 1),
          371  +        sqlite3_column_text(pCur->pTableList, 0)
          372  +    );
          373  +
          374  +    if( !zSql ){
          375  +      rc = SQLITE_NOMEM;
          376  +      goto next_exit;
          377  +    }
          378  +    rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pColumnList, 0);
          379  +    sqlite3_free(zSql);
          380  +    if( rc!=SQLITE_OK ) goto next_exit;
          381  +  }
          382  +  pCur->rowid++;
          383  +
          384  +next_exit:
          385  +  /* TODO: Handle rc */
          386  +  return rc;
          387  +}
          388  +
          389  +/*
          390  +** Reset a schema table cursor.
          391  +*/
          392  +static int schemaFilter(
          393  +  sqlite3_vtab_cursor *pVtabCursor, 
          394  +  int idxNum, const char *idxStr,
          395  +  int argc, sqlite3_value **argv
          396  +){
          397  +  int rc;
          398  +  schema_vtab *pVtab = (schema_vtab *)(pVtabCursor->pVtab);
          399  +  schema_cursor *pCur = (schema_cursor *)pVtabCursor;
          400  +  pCur->rowid = 0;
          401  +  finalize(&pCur->pTableList);
          402  +  finalize(&pCur->pColumnList);
          403  +  finalize(&pCur->pDbList);
          404  +  rc = sqlite3_prepare(pVtab->db,"SELECT 0, 'main'", -1, &pCur->pDbList, 0);
          405  +  return (rc==SQLITE_OK ? schemaNext(pVtabCursor) : rc);
          406  +}
          407  +
          408  +/*
          409  +** Analyse the WHERE condition.
          410  +*/
          411  +static int schemaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
          412  +  return SQLITE_OK;
          413  +}
          414  +
          415  +/*
          416  +** A virtual table module that merely echos method calls into TCL
          417  +** variables.
          418  +*/
          419  +static sqlite3_module schemaModule = {
          420  +  0,                           /* iVersion */
          421  +  schemaCreate,
          422  +  schemaCreate,
          423  +  schemaBestIndex,
          424  +  schemaDestroy,
          425  +  schemaDestroy,
          426  +  schemaOpen,                  /* xOpen - open a cursor */
          427  +  schemaClose,                 /* xClose - close a cursor */
          428  +  schemaFilter,                /* xFilter - configure scan constraints */
          429  +  schemaNext,                  /* xNext - advance a cursor */
          430  +  schemaEof,                   /* xEof */
          431  +  schemaColumn,                /* xColumn - read data */
          432  +  schemaRowid,                 /* xRowid - read data */
          433  +  0,                           /* xUpdate */
          434  +  0,                           /* xBegin */
          435  +  0,                           /* xSync */
          436  +  0,                           /* xCommit */
          437  +  0,                           /* xRollback */
          438  +  0,                           /* xFindMethod */
          439  +  0,                           /* xRename */
          440  +};
          441  +
          442  +/*
          443  +** Extension load function.
          444  +*/
          445  +static int installSchemaModule(sqlite3 *db, sqlite3 *sdb){
          446  +  sqlite3_create_module(db, "schema", &schemaModule, (void *)sdb);
          447  +  return 0;
          448  +}
          449  +
          450  +/*
          451  +**   sj(zValue, zJoin)
          452  +**
          453  +** The following block contains the implementation of an aggregate 
          454  +** function that returns a string. Each time the function is stepped, 
          455  +** it appends data to an internal buffer. When the aggregate is finalized,
          456  +** the contents of the buffer are returned.
          457  +**
          458  +** The first time the aggregate is stepped the buffer is set to a copy
          459  +** of the first argument. The second time and subsequent times it is
          460  +** stepped a copy of the second argument is appended to the buffer, then
          461  +** a copy of the first.
          462  +**
          463  +** Example:
          464  +**
          465  +**   INSERT INTO t1(a) VALUES('1');
          466  +**   INSERT INTO t1(a) VALUES('2');
          467  +**   INSERT INTO t1(a) VALUES('3');
          468  +**   SELECT sj(a, ', ') FROM t1;
          469  +**
          470  +**     =>  "1, 2, 3"
          471  +**
          472  +*/
          473  +struct StrBuffer {
          474  +  char *zBuf;
          475  +};
          476  +typedef struct StrBuffer StrBuffer;
          477  +static void joinFinalize(sqlite3_context *context){
          478  +  StrBuffer *p;
          479  +  p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer));
          480  +  sqlite3_result_text(context, p->zBuf, -1, SQLITE_TRANSIENT);
          481  +  sqlite3_free(p->zBuf);
          482  +}
          483  +static void joinStep(
          484  +  sqlite3_context *context,
          485  +  int argc,
          486  +  sqlite3_value **argv
          487  +){
          488  +  StrBuffer *p;
          489  +  p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer));
          490  +  if( p->zBuf==0 ){
          491  +    p->zBuf = sqlite3_mprintf("%s", sqlite3_value_text(argv[0]));
          492  +  }else{
          493  +    char *zTmp = p->zBuf;
          494  +    p->zBuf = sqlite3_mprintf("%s%s%s", 
          495  +        zTmp, sqlite3_value_text(argv[1]), sqlite3_value_text(argv[0])
          496  +    );
          497  +    sqlite3_free(zTmp);
          498  +  }
          499  +}
          500  +
          501  +/*
          502  +**   dq(zString)
          503  +**
          504  +** This scalar function accepts a single argument and interprets it as
          505  +** a text value. The return value is the argument enclosed in double
          506  +** quotes. If any double quote characters are present in the argument, 
          507  +** these are escaped.
          508  +**
          509  +**   dq('the raven "Nevermore."') == '"the raven ""Nevermore."""'
          510  +*/
          511  +static void doublequote(
          512  +  sqlite3_context *context, 
          513  +  int argc, 
          514  +  sqlite3_value **argv
          515  +){
          516  +  int ii;
          517  +  char *zOut;
          518  +  char *zCsr;
          519  +  const char *zIn = (const char *)sqlite3_value_text(argv[0]);
          520  +  int nIn = sqlite3_value_bytes(argv[0]);
          521  +
          522  +  zOut = sqlite3_malloc(nIn*2+3);
          523  +  zCsr = zOut;
          524  +  *zCsr++ = '"';
          525  +  for(ii=0; ii<nIn; ii++){
          526  +    *zCsr++ = zIn[ii];
          527  +    if( zIn[ii]=='"' ){
          528  +      *zCsr++ = '"';
          529  +    }
          530  +  }
          531  +  *zCsr++ = '"';
          532  +  *zCsr++ = '\0';
          533  +
          534  +  sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT);
          535  +  sqlite3_free(zOut);
          536  +}
          537  +
          538  +/*
          539  +**   multireplace(zString, zSearch1, zReplace1, ...)
          540  +*/
          541  +static void multireplace(
          542  +  sqlite3_context *context, 
          543  +  int argc, 
          544  +  sqlite3_value **argv
          545  +){
          546  +  int i = 0;
          547  +  char *zOut = 0;
          548  +  int nOut = 0;
          549  +  int nMalloc = 0;
          550  +  const char *zIn = (const char *)sqlite3_value_text(argv[0]);
          551  +  int nIn = sqlite3_value_bytes(argv[0]);
          552  +
          553  +  while( i<nIn ){
          554  +    const char *zCopy = &zIn[i];
          555  +    int nCopy = 1;
          556  +    int nReplace = 1;
          557  +    int j;
          558  +    for(j=1; j<(argc-1); j+=2){
          559  +      const char *z = (const char *)sqlite3_value_text(argv[j]);
          560  +      int n = sqlite3_value_bytes(argv[j]);
          561  +      if( n<=(nIn-i) && 0==strncmp(z, zCopy, n) ){
          562  +        zCopy = (const char *)sqlite3_value_text(argv[j+1]);
          563  +        nCopy = sqlite3_value_bytes(argv[j+1]);
          564  +        nReplace = n;
          565  +        break;
          566  +      }
          567  +    }
          568  +    if( (nOut+nCopy)>nMalloc ){
          569  +      nMalloc += (nMalloc + 16);
          570  +      zOut = (char *)sqlite3_realloc(zOut, nMalloc);
          571  +    }
          572  +    memcpy(&zOut[nOut], zCopy, nCopy);
          573  +    i += nReplace;
          574  +    nOut += nCopy;
          575  +  }
          576  +
          577  +  sqlite3_result_text(context, zOut, nOut, SQLITE_TRANSIENT);
          578  +  sqlite3_free(zOut);
          579  +}
          580  +
          581  +/*
          582  +** A callback for sqlite3_exec() invokes the callback specified by the
          583  +** GenfkeyCb structure pointed to by the void* passed as the first argument.
          584  +*/
          585  +static int invokeCallback(void *p, int nArg, char **azArg, char **azCol){
          586  +  GenfkeyCb *pCb = (GenfkeyCb *)p;
          587  +  return pCb->xData(pCb->pCtx, pCb->eType, azArg[0]);
          588  +}
          589  +
          590  +int detectSchemaProblem(
          591  +  sqlite3 *db,                   /* Database connection */
          592  +  const char *zMessage,          /* English language error message */
          593  +  const char *zSql,              /* SQL statement to run */
          594  +  GenfkeyCb *pCb
          595  +){
          596  +  sqlite3_stmt *pStmt;
          597  +  int rc;
          598  +  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          599  +  if( rc!=SQLITE_OK ){
          600  +    return rc;
          601  +  }
          602  +  while( SQLITE_ROW==sqlite3_step(pStmt) ){
          603  +    char *zDel;
          604  +    int iFk = sqlite3_column_int(pStmt, 0);
          605  +    const char *zTab = (const char *)sqlite3_column_text(pStmt, 1);
          606  +    zDel = sqlite3_mprintf("Error in table %s: %s", zTab, zMessage);
          607  +    rc = pCb->xData(pCb->pCtx, pCb->eType, zDel);
          608  +    sqlite3_free(zDel);
          609  +    if( rc!=SQLITE_OK ) return rc;
          610  +    zDel = sqlite3_mprintf(
          611  +        "DELETE FROM temp.fkey WHERE from_tbl = %Q AND fkid = %d"
          612  +        , zTab, iFk
          613  +    );
          614  +    sqlite3_exec(db, zDel, 0, 0, 0);
          615  +    sqlite3_free(zDel);
          616  +  }
          617  +  sqlite3_finalize(pStmt);
          618  +  return SQLITE_OK;
          619  +}
          620  +
          621  +/*
          622  +** Create and populate temporary table "fkey".
          623  +*/
          624  +static int populateTempTable(sqlite3 *db, GenfkeyCb *pCallback){
          625  +  int rc;
          626  +  
          627  +  rc = sqlite3_exec(db, 
          628  +      "CREATE VIRTUAL TABLE temp.v_fkey USING schema(foreign_key_list);"
          629  +      "CREATE VIRTUAL TABLE temp.v_col USING schema(table_info);"
          630  +      "CREATE VIRTUAL TABLE temp.v_idxlist USING schema(index_list);"
          631  +      "CREATE VIRTUAL TABLE temp.v_idxinfo USING schema(index_info);"
          632  +      "CREATE VIRTUAL TABLE temp.v_triggers USING schema(trigger_list);"
          633  +      "CREATE TABLE temp.fkey AS "
          634  +        "SELECT from_tbl, to_tbl, fkid, from_col, to_col, on_update, on_delete "
          635  +        "FROM temp.v_fkey WHERE database = 'main';"
          636  +      , 0, 0, 0
          637  +  );
          638  +  if( rc!=SQLITE_OK ) return rc;
          639  +
          640  +  rc = detectSchemaProblem(db, "foreign key columns do not exist",
          641  +    "SELECT fkid, from_tbl "
          642  +    "FROM temp.fkey "
          643  +    "WHERE to_col IS NOT NULL AND NOT EXISTS (SELECT 1 "
          644  +        "FROM temp.v_col WHERE tablename=to_tbl AND name==to_col"
          645  +    ")", pCallback
          646  +  );
          647  +  if( rc!=SQLITE_OK ) return rc;
          648  +
          649  +  /* At this point the temp.fkey table is mostly populated. If any foreign
          650  +  ** keys were specified so that they implicitly refer to they primary
          651  +  ** key of the parent table, the "to_col" values of the temp.fkey rows
          652  +  ** are still set to NULL.
          653  +  **
          654  +  ** This is easily fixed for single column primary keys, but not for
          655  +  ** composites. With a composite primary key, there is no way to reliably
          656  +  ** query sqlite for the order in which the columns that make up the
          657  +  ** composite key were declared i.e. there is no way to tell if the
          658  +  ** schema actually contains "PRIMARY KEY(a, b)" or "PRIMARY KEY(b, a)".
          659  +  ** Therefore, this case is not handled. The following function call
          660  +  ** detects instances of this case.
          661  +  */
          662  +  rc = detectSchemaProblem(db, "implicit mapping to composite primary key",
          663  +    "SELECT fkid, from_tbl "
          664  +    "FROM temp.fkey "
          665  +    "WHERE to_col IS NULL "
          666  +    "GROUP BY fkid, from_tbl HAVING count(*) > 1", pCallback
          667  +  );
          668  +  if( rc!=SQLITE_OK ) return rc;
          669  +
          670  +  /* Detect attempts to implicitly map to the primary key of a table 
          671  +  ** that has no primary key column.
          672  +  */
          673  +  rc = detectSchemaProblem(db, "implicit mapping to non-existant primary key",
          674  +    "SELECT fkid, from_tbl "
          675  +    "FROM temp.fkey "
          676  +    "WHERE to_col IS NULL AND NOT EXISTS "
          677  +      "(SELECT 1 FROM temp.v_col WHERE pk AND tablename = temp.fkey.to_tbl)"
          678  +    , pCallback
          679  +  );
          680  +  if( rc!=SQLITE_OK ) return rc;
          681  +
          682  +  /* Fix all the implicit primary key mappings in the temp.fkey table. */
          683  +  rc = sqlite3_exec(db, 
          684  +    "UPDATE temp.fkey SET to_col = "
          685  +      "(SELECT name FROM temp.v_col WHERE pk AND tablename=temp.fkey.to_tbl)"
          686  +    " WHERE to_col IS NULL;"
          687  +    , 0, 0, 0
          688  +  );
          689  +  if( rc!=SQLITE_OK ) return rc;
          690  +
          691  +  /* Now check that all all parent keys are either primary keys or 
          692  +  ** subject to a unique constraint.
          693  +  */
          694  +  rc = sqlite3_exec(db, 
          695  +    "CREATE TABLE temp.idx2 AS SELECT "
          696  +      "il.tablename AS tablename,"
          697  +      "ii.indexname AS indexname,"
          698  +      "ii.name AS col "
          699  +      "FROM temp.v_idxlist AS il, temp.v_idxinfo AS ii "
          700  +      "WHERE il.isunique AND il.database='main' AND ii.indexname = il.name;"
          701  +    "INSERT INTO temp.idx2 "
          702  +      "SELECT tablename, 'pk', name FROM temp.v_col WHERE pk;"
          703  +
          704  +    "CREATE TABLE temp.idx AS SELECT "
          705  +      "tablename, indexname, sj(dq(col),',') AS cols "
          706  +      "FROM (SELECT * FROM temp.idx2 ORDER BY col) " 
          707  +      "GROUP BY tablename, indexname;"
          708  +
          709  +    "CREATE TABLE temp.fkey2 AS SELECT "
          710  +        "fkid, from_tbl, to_tbl, sj(dq(to_col),',') AS cols "
          711  +        "FROM (SELECT * FROM temp.fkey ORDER BY to_col) " 
          712  +        "GROUP BY fkid, from_tbl;"
          713  +
          714  +    "CREATE TABLE temp.triggers AS SELECT "
          715  +        "triggername FROM temp.v_triggers WHERE database='main' AND "
          716  +        "triggername LIKE 'genfkey%';"
          717  +    , 0, 0, 0
          718  +  );
          719  +  if( rc!=SQLITE_OK ) return rc;
          720  +  rc = detectSchemaProblem(db, "foreign key is not unique",
          721  +    "SELECT fkid, from_tbl "
          722  +    "FROM temp.fkey2 "
          723  +    "WHERE NOT EXISTS (SELECT 1 "
          724  +        "FROM temp.idx WHERE tablename=to_tbl AND fkey2.cols==idx.cols"
          725  +    ")", pCallback
          726  +  );
          727  +  if( rc!=SQLITE_OK ) return rc;
          728  +
          729  +  return rc;
          730  +}
          731  +
          732  +#define GENFKEY_ERROR         1
          733  +#define GENFKEY_DROPTRIGGER   2
          734  +#define GENFKEY_CREATETRIGGER 3
          735  +static int genfkey_create_triggers(
          736  +  sqlite3 *sdb,                        /* Connection to read schema from */
          737  +  const char *zDb,                     /* Name of db to read ("main", "temp") */
          738  +  void *pCtx,                          /* Context pointer to pass to xData */
          739  +  int (*xData)(void *, int, const char *)
          740  +){
          741  +  const char *zSql =
          742  +    "SELECT multireplace('"
          743  +
          744  +      "-- Triggers for foreign key mapping:\n"
          745  +      "--\n"
          746  +      "--     /from_readable/ REFERENCES /to_readable/\n"
          747  +      "--     on delete /on_delete/\n"
          748  +      "--     on update /on_update/\n"
          749  +      "--\n"
          750  +
          751  +      /* The "BEFORE INSERT ON <referencing>" trigger. This trigger's job is to
          752  +      ** throw an exception if the user tries to insert a row into the
          753  +      ** referencing table for which there is no corresponding row in
          754  +      ** the referenced table.
          755  +      */
          756  +      "CREATE TRIGGER /name/_insert_referencing BEFORE INSERT ON /tbl/ WHEN \n"
          757  +      "    /key_notnull/ AND NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" 
          758  +      "BEGIN\n"
          759  +        "  SELECT RAISE(ABORT, ''constraint failed'');\n"
          760  +      "END;\n"
          761  +
          762  +      /* The "BEFORE UPDATE ON <referencing>" trigger. This trigger's job 
          763  +      ** is to throw an exception if the user tries to update a row in the
          764  +      ** referencing table causing it to correspond to no row in the
          765  +      ** referenced table.
          766  +      */
          767  +      "CREATE TRIGGER /name/_update_referencing BEFORE\n"
          768  +      "    UPDATE OF /rkey_list/ ON /tbl/ WHEN \n"
          769  +      "    /key_notnull/ AND \n"
          770  +      "    NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" 
          771  +      "BEGIN\n"
          772  +        "  SELECT RAISE(ABORT, ''constraint failed'');\n"
          773  +      "END;\n"
          774  +
          775  +
          776  +      /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job 
          777  +      ** is to detect when a row is deleted from the referenced table to 
          778  +      ** which rows in the referencing table correspond. The action taken
          779  +      ** depends on the value of the 'ON DELETE' clause.
          780  +      */
          781  +      "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n"
          782  +      "    EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n"
          783  +      "BEGIN\n"
          784  +      "  /delete_action/\n"
          785  +      "END;\n"
          786  +
          787  +      /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job 
          788  +      ** is to detect when the key columns of a row in the referenced table 
          789  +      ** to which one or more rows in the referencing table correspond are
          790  +      ** updated. The action taken depends on the value of the 'ON UPDATE' 
          791  +      ** clause.
          792  +      */
          793  +      "CREATE TRIGGER /name/_update_referenced AFTER\n"
          794  +      "    UPDATE OF /fkey_list/ ON /ref/ WHEN \n"
          795  +      "    EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n"
          796  +      "BEGIN\n"
          797  +      "  /update_action/\n"
          798  +      "END;\n"
          799  +    "'"
          800  +
          801  +    /* These are used in the SQL comment written above each set of triggers */
          802  +    ", '/from_readable/',  from_tbl || '(' || sj(from_col, ', ') || ')'"
          803  +    ", '/to_readable/',    to_tbl || '(' || sj(to_col, ', ') || ')'"
          804  +    ", '/on_delete/', on_delete"
          805  +    ", '/on_update/', on_update"
          806  +
          807  +    ", '/name/',   'genfkey' || min(rowid)"
          808  +    ", '/tbl/',    dq(from_tbl)"
          809  +    ", '/ref/',    dq(to_tbl)"
          810  +    ", '/key_notnull/', sj('new.' || dq(from_col) || ' IS NOT NULL', ' AND ')"
          811  +
          812  +    ", '/fkey_list/', sj(to_col, ', ')"
          813  +    ", '/rkey_list/', sj(from_col, ', ')"
          814  +
          815  +    ", '/cond1/',  sj(multireplace('new./from/ == /to/'"
          816  +                   ", '/from/', dq(from_col)"
          817  +                   ", '/to/',   dq(to_col)"
          818  +                   "), ' AND ')"
          819  +    ", '/cond2/',  sj(multireplace('old./to/ == /from/'"
          820  +                   ", '/from/', dq(from_col)"
          821  +                   ", '/to/',   dq(to_col)"
          822  +                   "), ' AND ')"
          823  +
          824  +    ", '/update_action/', CASE on_update "
          825  +      "WHEN 'SET NULL' THEN "
          826  +        "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
          827  +        ", '/setlist/', sj(from_col||' = NULL',', ')"
          828  +        ", '/tbl/',     dq(from_tbl)"
          829  +        ", '/where/',   sj(from_col||' = old.'||dq(to_col),' AND ')"
          830  +        ")"
          831  +      "WHEN 'CASCADE' THEN "
          832  +        "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
          833  +        ", '/setlist/', sj(dq(from_col)||' = new.'||dq(to_col),', ')"
          834  +        ", '/tbl/',     dq(from_tbl)"
          835  +        ", '/where/',   sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
          836  +        ")"
          837  +      "ELSE "
          838  +      "  'SELECT RAISE(ABORT, ''constraint failed'');'"
          839  +      "END "
          840  +
          841  +    ", '/delete_action/', CASE on_delete "
          842  +      "WHEN 'SET NULL' THEN "
          843  +        "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' "
          844  +        ", '/setlist/', sj(from_col||' = NULL',', ')"
          845  +        ", '/tbl/',     dq(from_tbl)"
          846  +        ", '/where/',   sj(from_col||' = old.'||dq(to_col),' AND ')"
          847  +        ")"
          848  +      "WHEN 'CASCADE' THEN "
          849  +        "multireplace('DELETE FROM /tbl/ WHERE /where/;' "
          850  +        ", '/tbl/',     dq(from_tbl)"
          851  +        ", '/where/',   sj(dq(from_col)||' = old.'||dq(to_col),' AND ')"
          852  +        ")"
          853  +      "ELSE "
          854  +      "  'SELECT RAISE(ABORT, ''constraint failed'');'"
          855  +      "END "
          856  +
          857  +    ") FROM temp.fkey "
          858  +    "GROUP BY from_tbl, fkid"
          859  +  ;
          860  +
          861  +  int rc;
          862  +  const int enc = SQLITE_UTF8;
          863  +  sqlite3 *db = 0;
          864  +
          865  +  GenfkeyCb cb;
          866  +  cb.xData = xData;
          867  +  cb.pCtx = pCtx;
          868  +
          869  +  /* Open the working database handle. */
          870  +  rc = sqlite3_open(":memory:", &db);
          871  +  if( rc!=SQLITE_OK ) goto genfkey_exit;
          872  +
          873  +  /* Create the special scalar and aggregate functions used by this program. */
          874  +  sqlite3_create_function(db, "dq", 1, enc, 0, doublequote, 0, 0);
          875  +  sqlite3_create_function(db, "multireplace", -1, enc, db, multireplace, 0, 0);
          876  +  sqlite3_create_function(db, "sj", 2, enc, 0, 0, joinStep, joinFinalize);
          877  +
          878  +  /* Install the "schema" virtual table module */
          879  +  installSchemaModule(db, sdb);
          880  +
          881  +  /* Create and populate a temp table with the information required to
          882  +  ** build the foreign key triggers. See function populateTempTable()
          883  +  ** for details.
          884  +  */
          885  +  cb.eType = GENFKEY_ERROR;
          886  +  rc = populateTempTable(db, &cb);
          887  +  if( rc!=SQLITE_OK ) goto genfkey_exit;
          888  +
          889  +  /* Unless the --no-drop option was specified, generate DROP TRIGGER
          890  +  ** statements to drop any triggers in the database generated by a
          891  +  ** previous run of this program.
          892  +  */
          893  +  cb.eType = GENFKEY_DROPTRIGGER;
          894  +  rc = sqlite3_exec(db, 
          895  +    "SELECT 'DROP TRIGGER main.' || dq(triggername) || ';' FROM triggers"
          896  +    ,invokeCallback, (void *)&cb, 0
          897  +  );
          898  +  if( rc!=SQLITE_OK ) goto genfkey_exit;
          899  +
          900  +  /* Run the main query to create the trigger definitions. */
          901  +  cb.eType = GENFKEY_CREATETRIGGER;
          902  +  rc = sqlite3_exec(db, zSql, invokeCallback, (void *)&cb, 0);
          903  +  if( rc!=SQLITE_OK ) goto genfkey_exit;
          904  +
          905  +genfkey_exit:
          906  +  sqlite3_close(db);
          907  +  return rc;
          908  +}
          909  +
          910  +
          911  +#endif
          912  +/* End genfkey logic. */
          913  +/*************************************************************************/
          914  +/*************************************************************************/
          915  +
    76    916   /* Saved resource information for the beginning of an operation */
    77    917   static struct rusage sBegin;
    78    918   
    79    919   /* True if the timer is enabled */
    80    920   static int enableTimer = 0;
    81    921   
    82    922   /*
................................................................................
   922   1762       sqlite3_snprintf(sizeof(zQ2), zQ2, "%s ORDER BY rowid DESC", zQuery);
   923   1763       rc = sqlite3_exec(p->db, zQ2, dump_callback, p, pzErrMsg);
   924   1764       free(zQ2);
   925   1765     }
   926   1766     return rc;
   927   1767   }
   928   1768   
         1769  +
         1770  +#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY)
         1771  +struct GenfkeyCmd {
         1772  +  sqlite3 *db;                   /* Database handle */
         1773  +  struct callback_data *pCb;     /* Callback data */
         1774  +  int isIgnoreErrors;            /* True for --ignore-errors */
         1775  +  int isExec;                    /* True for --exec */
         1776  +  int isNoDrop;                  /* True for --no-drop */
         1777  +  int nErr;                      /* Number of errors seen so far */
         1778  +};
         1779  +typedef struct GenfkeyCmd GenfkeyCmd;
         1780  +
         1781  +static int genfkeyParseArgs(GenfkeyCmd *p, char **azArg, int nArg){
         1782  +  int ii;
         1783  +  memset(p, 0, sizeof(GenfkeyCmd));
         1784  +
         1785  +  for(ii=0; ii<nArg; ii++){
         1786  +    int n = strlen(azArg[ii]);
         1787  +
         1788  +    if( n>2 && n<10 && 0==strncmp(azArg[ii], "--no-drop", n) ){
         1789  +      p->isNoDrop = 1;
         1790  +    }else if( n>2 && n<16 && 0==strncmp(azArg[ii], "--ignore-errors", n) ){
         1791  +      p->isIgnoreErrors = 1;
         1792  +    }else if( n>2 && n<7 && 0==strncmp(azArg[ii], "--exec", n) ){
         1793  +      p->isExec = 1;
         1794  +    }else{
         1795  +      fprintf(stderr, "unknown option: %s\n", azArg[ii]);
         1796  +      return -1;
         1797  +    }
         1798  +  }
         1799  +
         1800  +  return SQLITE_OK;
         1801  +}
         1802  +
         1803  +static int genfkeyCmdCb(void *pCtx, int eType, const char *z){
         1804  +  GenfkeyCmd *p = (GenfkeyCmd *)pCtx;
         1805  +  if( eType==GENFKEY_ERROR && !p->isIgnoreErrors ){
         1806  +    p->nErr++;
         1807  +    fprintf(stderr, "%s\n", z);
         1808  +  } 
         1809  +
         1810  +  if( p->nErr==0 && (
         1811  +        (eType==GENFKEY_CREATETRIGGER)
         1812  +     || (eType==GENFKEY_DROPTRIGGER && !p->isNoDrop)
         1813  +  )){
         1814  +    if( p->isExec ){
         1815  +      sqlite3_exec(p->db, z, 0, 0, 0);
         1816  +    }else{
         1817  +      char *zCol = "sql";
         1818  +      callback((void *)p->pCb, 1, (char **)&z, (char **)&zCol);
         1819  +    }
         1820  +  }
         1821  +
         1822  +  return SQLITE_OK;
         1823  +}
         1824  +#endif
         1825  +
   929   1826   /*
   930   1827   ** Text of a help message
   931   1828   */
   932   1829   static char zHelp[] =
   933   1830     ".backup ?DB? FILE      Backup DB (default \"main\") to FILE\n"
   934   1831     ".bail ON|OFF           Stop after hitting an error.  Default OFF\n"
   935   1832     ".databases             List names and files of attached databases\n"
   936   1833     ".dump ?TABLE? ...      Dump the database in an SQL text format\n"
   937   1834     ".echo ON|OFF           Turn command echo on or off\n"
   938   1835     ".exit                  Exit this program\n"
   939   1836     ".explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.\n"
         1837  +#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY)
         1838  +  ".genfkey ?OPTIONS?     Options are:\n"
         1839  +  "                         --no-drop: Do not drop old fkey triggers.\n"
         1840  +  "                         --ignore-errors: Ignore tables with fkey errors\n"
         1841  +  "                         --exec: Execute generated SQL immediately\n"
         1842  +  "                         --help: Print help message for .genfkey\n"
         1843  +#endif
   940   1844     ".header(s) ON|OFF      Turn display of headers on or off\n"
   941   1845     ".help                  Show this message\n"
   942   1846     ".import FILE TABLE     Import data from FILE into TABLE\n"
   943   1847     ".indices TABLE         Show names of all indices on TABLE\n"
   944   1848   #ifdef SQLITE_ENABLE_IOTRACE
   945   1849     ".iotrace FILE          Enable I/O diagnostic logging to FILE\n"
   946   1850   #endif
................................................................................
  1235   2139       }else if (p->explainPrev.valid) {
  1236   2140         p->explainPrev.valid = 0;
  1237   2141         p->mode = p->explainPrev.mode;
  1238   2142         p->showHeader = p->explainPrev.showHeader;
  1239   2143         memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
  1240   2144       }
  1241   2145     }else
         2146  +
         2147  +#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY)
         2148  +  if( c=='g' && strncmp(azArg[0], "genfkey", n)==0 ){
         2149  +    GenfkeyCmd cmd;
         2150  +    if( 0==genfkeyParseArgs(&cmd, &azArg[1], nArg-1) ){
         2151  +      cmd.db = p->db;
         2152  +      cmd.pCb = p;
         2153  +      genfkey_create_triggers(p->db, "main", (void *)&cmd, genfkeyCmdCb);
         2154  +    }
         2155  +  }else
         2156  +#endif
  1242   2157   
  1243   2158     if( c=='h' && (strncmp(azArg[0], "header", n)==0 ||
  1244   2159                    strncmp(azArg[0], "headers", n)==0 )&& nArg>1 ){
  1245   2160       p->showHeader = booleanValue(azArg[1]);
  1246   2161     }else
  1247   2162   
  1248   2163     if( c=='h' && strncmp(azArg[0], "help", n)==0 ){

Changes to tool/genfkey.test.

    32     32     execsql {
    33     33       CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
    34     34       CREATE TABLE t2(e REFERENCES t1, f);
    35     35       CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
    36     36     }
    37     37   } {}
    38     38   do_test genfkey-1.2 {
    39         -  execsql [exec ./genfkey test.db]
           39  +  execsql [exec ./sqlite3 test.db .genfkey]
    40     40   } {}
    41     41   do_test genfkey-1.3 {
    42     42     catchsql { INSERT INTO t2 VALUES(1, 2) }
    43     43   } {1 {constraint failed}}
    44     44   do_test genfkey-1.4 {
    45     45     execsql {
    46     46       INSERT INTO t1 VALUES(1, 2, 3);
................................................................................
   125    125       CREATE TABLE t3(g, h, i, 
   126    126           FOREIGN KEY (h, i) 
   127    127           REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
   128    128       );
   129    129     }
   130    130   } {}
   131    131   do_test genfkey-2.2 {
   132         -  execsql [exec ./genfkey test.db]
          132  +  execsql [exec ./sqlite3 test.db .genfkey]
   133    133   } {}
   134    134   do_test genfkey-2.3 {
   135    135     execsql {
   136    136       INSERT INTO t1 VALUES(1, 2, 3);
   137    137       INSERT INTO t1 VALUES(4, 5, 6);
   138    138       INSERT INTO t2 VALUES(1, 'one');
   139    139       INSERT INTO t2 VALUES(4, 'four');
................................................................................
   182    182       CREATE TABLE t3(g, h, i, 
   183    183           FOREIGN KEY (h, i) 
   184    184           REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
   185    185       );
   186    186     }
   187    187   } {}
   188    188   do_test genfkey-3.2 {
   189         -  execsql [exec ./genfkey test.db]
          189  +  execsql [exec ./sqlite3 test.db .genfkey]
   190    190   } {}
   191    191   do_test genfkey-3.3 {
   192    192     execsql {
   193    193       INSERT INTO t1 VALUES(1, 2, 3);
   194    194       INSERT INTO t1 VALUES(4, 5, 6);
   195    195       INSERT INTO t2 VALUES(1, 'one');
   196    196       INSERT INTO t2 VALUES(4, 'four');
................................................................................
   245    245       CREATE TABLE t6(a REFERENCES t4(a), b, c);
   246    246       CREATE TABLE t7(a REFERENCES t3(a), b, c);
   247    247       CREATE TABLE t8(a REFERENCES nosuchtable(a), b, c);
   248    248     }
   249    249   } {}
   250    250   
   251    251   do_test genfkey-4.X {
   252         -  set rc [catch {exec ./genfkey test.db} msg]
          252  +  set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
   253    253     list $rc $msg
   254    254   } "1 {[string trim {
   255    255   Error in table t5: foreign key columns do not exist
   256    256   Error in table t8: foreign key columns do not exist
   257    257   Error in table t4: implicit mapping to composite primary key
   258    258   Error in table t1: implicit mapping to non-existant primary key
   259    259   Error in table t2: implicit mapping to non-existant primary key
   260    260   Error in table t6: foreign key is not unique
   261    261   Error in table t7: foreign key is not unique
   262    262   }]}"
   263    263