From c8c7069035a460a043f8101275cd2b1c05cbf6a2 Mon Sep 17 00:00:00 2001 From: danielk1977 Date: Wed, 25 Feb 2009 15:22:02 +0000 Subject: [PATCH] Add the 'genfkey' functionality to the sqlite3 shell. Accessed using a new dot-command - ".genfkey". (CVS 6325) FossilOrigin-Name: 0a59fb28b46e5d85c850d1dfa1385a4656e4dda5 --- manifest | 14 +- manifest.uuid | 2 +- src/shell.c | 917 +++++++++++++++++++++++++++++++++++++++++++++- tool/genfkey.test | 8 +- 4 files changed, 928 insertions(+), 13 deletions(-) diff --git a/manifest b/manifest index e18d2155df..f4d5fde854 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Minor\schanges\sand\scoverge\stests\sfor\s"SELECT\scount(*)"\soptimization.\s(CVS\s6324) -D 2009-02-25T08:56:47 +C Add\sthe\s'genfkey'\sfunctionality\sto\sthe\ssqlite3\sshell.\sAccessed\susing\sa\snew\sdot-command\s-\s".genfkey".\s(CVS\s6325) +D 2009-02-25T15:22:03 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0 F Makefile.in d64baddbf55cdf33ff030e14da837324711a4ef7 F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654 @@ -155,7 +155,7 @@ F src/random.c 676b9d7ac820fe81e6fb2394ac8c10cff7f38628 F src/resolve.c dea005135845acbbfae1f2968c0deb6b2e3d580c F src/rowset.c ba9375f37053d422dd76965a9c370a13b6e1aac4 F src/select.c 4d0b77fd76ff80f09a798ee98953e344c9de8fbb -F src/shell.c f109ebbb50132926ebbc173a6c2d8838d5d78527 +F src/shell.c 95ca116d855f912960326054275ec21429b074ca F src/sqlite.h.in 14f4d065bafed8500ea558a75a8e2be89c784d61 F src/sqlite3ext.h 1db7d63ab5de4b3e6b83dd03d1a4e64fef6d2a17 F src/sqliteInt.h e717d30828600865b8f74456fd461e6a52ead6df @@ -682,7 +682,7 @@ F tool/diffdb.c 7524b1b5df217c20cd0431f6789851a4e0cb191b F tool/fragck.tcl 5265a95126abcf6ab357f7efa544787e5963f439 F tool/genfkey.README 080ca2269f727fcf14c8e76d52e85d157e3a8e9a F tool/genfkey.c e1b9c93da828db10c1109c7b4fa611aec8adc407 -F tool/genfkey.test 182829596fb15785b94b2493c5f735b847d91076 +F tool/genfkey.test eda48d8258f5ad7b3b258ca3323c3c4246a846f2 F tool/lemon.c 0f65442d1c99a865525658a47ddf292f4ac2ec54 F tool/lempar.c aeba88b8566ff66f8a67c96b3eb2dd95e7d8908d F tool/mkkeywordhash.c 8e57fbe8c4fe2f1800f9190fd361231cb8558407 @@ -702,7 +702,7 @@ F tool/speedtest16.c c8a9c793df96db7e4933f0852abb7a03d48f2e81 F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e -P 91d9d51e03657e7492dd7b93e66c82b152abec3b -R c65d7c9d041d481539ec6e65a8bbfcc4 +P a3695b98f63fb776c3b7f77f0553e8a38bcc6f78 +R 63fcdff1c89d9f14e0a70bd84c7c7dfe U danielk1977 -Z f03589b6049282aa23f55a52edc62f1d +Z 1a6ade2e3ad1357e1b9f98d287dfc555 diff --git a/manifest.uuid b/manifest.uuid index 0b49b47423..3c47500082 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -a3695b98f63fb776c3b7f77f0553e8a38bcc6f78 \ No newline at end of file +0a59fb28b46e5d85c850d1dfa1385a4656e4dda5 \ No newline at end of file diff --git a/src/shell.c b/src/shell.c index 93e4816e78..ce3c4eaaca 100644 --- a/src/shell.c +++ b/src/shell.c @@ -12,7 +12,7 @@ ** This file contains code to implement the "sqlite" command line ** utility for accessing SQLite databases. ** -** $Id: shell.c,v 1.201 2009/02/04 22:46:47 drh Exp $ +** $Id: shell.c,v 1.202 2009/02/25 15:22:03 danielk1977 Exp $ */ #if defined(_WIN32) || defined(WIN32) /* This needs to come before any includes for MSVC compiler */ @@ -73,6 +73,846 @@ extern int isatty(); #include #include + +/************************************************************************** +*************************************************************************** +** Begin genfkey logic. +*/ +#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined SQLITE_OMIT_SUBQUERY + +#define GENFKEY_ERROR 1 +#define GENFKEY_DROPTRIGGER 2 +#define GENFKEY_CREATETRIGGER 3 +static int genfkey_create_triggers(sqlite3 *, const char *, void *, + int (*)(void *, int, const char *) +); + +struct GenfkeyCb { + void *pCtx; + int eType; + int (*xData)(void *, int, const char *); +}; +typedef struct GenfkeyCb GenfkeyCb; + +/* The code in this file defines a sqlite3 virtual-table module that +** provides a read-only view of the current database schema. There is one +** row in the schema table for each column in the database schema. +*/ +#define SCHEMA \ +"CREATE TABLE x(" \ + "database," /* Name of database (i.e. main, temp etc.) */ \ + "tablename," /* Name of table */ \ + "cid," /* Column number (from left-to-right, 0 upward) */ \ + "name," /* Column name */ \ + "type," /* Specified type (i.e. VARCHAR(32)) */ \ + "not_null," /* Boolean. True if NOT NULL was specified */ \ + "dflt_value," /* Default value for this column */ \ + "pk" /* True if this column is part of the primary key */ \ +")" + +#define SCHEMA2 \ +"CREATE TABLE x(" \ + "database," /* Name of database (i.e. main, temp etc.) */ \ + "from_tbl," /* Name of table */ \ + "fkid," \ + "seq," \ + "to_tbl," \ + "from_col," \ + "to_col," \ + "on_update," \ + "on_delete," \ + "match" \ +")" + +#define SCHEMA3 \ +"CREATE TABLE x(" \ + "database," /* Name of database (i.e. main, temp etc.) */ \ + "tablename," /* Name of table */ \ + "seq," \ + "name," \ + "isunique" \ +")" + +#define SCHEMA4 \ +"CREATE TABLE x(" \ + "database," /* Name of database (i.e. main, temp etc.) */ \ + "indexname," /* Name of table */ \ + "seqno," \ + "cid," \ + "name" \ +")" + +#define SCHEMA5 \ +"CREATE TABLE x(" \ + "database," /* Name of database (i.e. main, temp etc.) */ \ + "triggername," /* Name of trigger */ \ + "dummy" /* Unused */ \ +")" + +typedef struct SchemaTable SchemaTable; +struct SchemaTable { + const char *zName; + const char *zObject; + const char *zPragma; + const char *zSchema; +} aSchemaTable[] = { + { "table_info", "table", "PRAGMA %Q.table_info(%Q)", SCHEMA }, + { "foreign_key_list", "table", "PRAGMA %Q.foreign_key_list(%Q)", SCHEMA2 }, + { "index_list", "table", "PRAGMA %Q.index_list(%Q)", SCHEMA3 }, + { "index_info", "index", "PRAGMA %Q.index_info(%Q)", SCHEMA4 }, + { "trigger_list", "trigger", "SELECT 1", SCHEMA5 }, + { 0, 0, 0, 0 } +}; + +typedef struct schema_vtab schema_vtab; +typedef struct schema_cursor schema_cursor; + +/* A schema table object */ +struct schema_vtab { + sqlite3_vtab base; + sqlite3 *db; + SchemaTable *pType; +}; + +/* A schema table cursor object */ +struct schema_cursor { + sqlite3_vtab_cursor base; + sqlite3_stmt *pDbList; + sqlite3_stmt *pTableList; + sqlite3_stmt *pColumnList; + int rowid; +}; + +/* +** Table destructor for the schema module. +*/ +static int schemaDestroy(sqlite3_vtab *pVtab){ + sqlite3_free(pVtab); + return 0; +} + +/* +** Table constructor for the schema module. +*/ +static int schemaCreate( + sqlite3 *db, + void *pAux, + int argc, const char *const*argv, + sqlite3_vtab **ppVtab, + char **pzErr +){ + int rc = SQLITE_NOMEM; + schema_vtab *pVtab; + SchemaTable *pType = &aSchemaTable[0]; + + if( argc>3 ){ + int i; + pType = 0; + for(i=0; aSchemaTable[i].zName; i++){ + if( 0==strcmp(argv[3], aSchemaTable[i].zName) ){ + pType = &aSchemaTable[i]; + } + } + if( !pType ){ + return SQLITE_ERROR; + } + } + + pVtab = sqlite3_malloc(sizeof(schema_vtab)); + if( pVtab ){ + memset(pVtab, 0, sizeof(schema_vtab)); + pVtab->db = (sqlite3 *)pAux; + pVtab->pType = pType; + rc = sqlite3_declare_vtab(db, pType->zSchema); + } + *ppVtab = (sqlite3_vtab *)pVtab; + return rc; +} + +/* +** Open a new cursor on the schema table. +*/ +static int schemaOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ + int rc = SQLITE_NOMEM; + schema_cursor *pCur; + pCur = sqlite3_malloc(sizeof(schema_cursor)); + if( pCur ){ + memset(pCur, 0, sizeof(schema_cursor)); + *ppCursor = (sqlite3_vtab_cursor *)pCur; + rc = SQLITE_OK; + } + return rc; +} + +/* +** Close a schema table cursor. +*/ +static int schemaClose(sqlite3_vtab_cursor *cur){ + schema_cursor *pCur = (schema_cursor *)cur; + sqlite3_finalize(pCur->pDbList); + sqlite3_finalize(pCur->pTableList); + sqlite3_finalize(pCur->pColumnList); + sqlite3_free(pCur); + return SQLITE_OK; +} + +static void columnToResult(sqlite3_context *ctx, sqlite3_stmt *pStmt, int iCol){ + switch( sqlite3_column_type(pStmt, iCol) ){ + case SQLITE_NULL: + sqlite3_result_null(ctx); + break; + case SQLITE_INTEGER: + sqlite3_result_int64(ctx, sqlite3_column_int64(pStmt, iCol)); + break; + case SQLITE_FLOAT: + sqlite3_result_double(ctx, sqlite3_column_double(pStmt, iCol)); + break; + case SQLITE_TEXT: { + const char *z = (const char *)sqlite3_column_text(pStmt, iCol); + sqlite3_result_text(ctx, z, -1, SQLITE_TRANSIENT); + break; + } + } +} + +/* +** Retrieve a column of data. +*/ +static int schemaColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){ + schema_cursor *pCur = (schema_cursor *)cur; + switch( i ){ + case 0: + columnToResult(ctx, pCur->pDbList, 1); + break; + case 1: + columnToResult(ctx, pCur->pTableList, 0); + break; + default: + columnToResult(ctx, pCur->pColumnList, i-2); + break; + } + return SQLITE_OK; +} + +/* +** Retrieve the current rowid. +*/ +static int schemaRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ + schema_cursor *pCur = (schema_cursor *)cur; + *pRowid = pCur->rowid; + return SQLITE_OK; +} + +static int finalize(sqlite3_stmt **ppStmt){ + int rc = sqlite3_finalize(*ppStmt); + *ppStmt = 0; + return rc; +} + +static int schemaEof(sqlite3_vtab_cursor *cur){ + schema_cursor *pCur = (schema_cursor *)cur; + return (pCur->pDbList ? 0 : 1); +} + +/* +** Advance the cursor to the next row. +*/ +static int schemaNext(sqlite3_vtab_cursor *cur){ + int rc = SQLITE_OK; + schema_cursor *pCur = (schema_cursor *)cur; + schema_vtab *pVtab = (schema_vtab *)(cur->pVtab); + char *zSql = 0; + + while( !pCur->pColumnList || SQLITE_ROW!=sqlite3_step(pCur->pColumnList) ){ + if( SQLITE_OK!=(rc = finalize(&pCur->pColumnList)) ) goto next_exit; + + while( !pCur->pTableList || SQLITE_ROW!=sqlite3_step(pCur->pTableList) ){ + if( SQLITE_OK!=(rc = finalize(&pCur->pTableList)) ) goto next_exit; + + assert(pCur->pDbList); + while( SQLITE_ROW!=sqlite3_step(pCur->pDbList) ){ + rc = finalize(&pCur->pDbList); + goto next_exit; + } + + /* Set zSql to the SQL to pull the list of tables from the + ** sqlite_master (or sqlite_temp_master) table of the database + ** identfied by the row pointed to by the SQL statement pCur->pDbList + ** (iterating through a "PRAGMA database_list;" statement). + */ + if( sqlite3_column_int(pCur->pDbList, 0)==1 ){ + zSql = sqlite3_mprintf( + "SELECT name FROM sqlite_temp_master WHERE type=%Q", + pVtab->pType->zObject + ); + }else{ + sqlite3_stmt *pDbList = pCur->pDbList; + zSql = sqlite3_mprintf( + "SELECT name FROM %Q.sqlite_master WHERE type=%Q", + sqlite3_column_text(pDbList, 1), pVtab->pType->zObject + ); + } + if( !zSql ){ + rc = SQLITE_NOMEM; + goto next_exit; + } + + rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pTableList, 0); + sqlite3_free(zSql); + if( rc!=SQLITE_OK ) goto next_exit; + } + + /* Set zSql to the SQL to the table_info pragma for the table currently + ** identified by the rows pointed to by statements pCur->pDbList and + ** pCur->pTableList. + */ + zSql = sqlite3_mprintf(pVtab->pType->zPragma, + sqlite3_column_text(pCur->pDbList, 1), + sqlite3_column_text(pCur->pTableList, 0) + ); + + if( !zSql ){ + rc = SQLITE_NOMEM; + goto next_exit; + } + rc = sqlite3_prepare(pVtab->db, zSql, -1, &pCur->pColumnList, 0); + sqlite3_free(zSql); + if( rc!=SQLITE_OK ) goto next_exit; + } + pCur->rowid++; + +next_exit: + /* TODO: Handle rc */ + return rc; +} + +/* +** Reset a schema table cursor. +*/ +static int schemaFilter( + sqlite3_vtab_cursor *pVtabCursor, + int idxNum, const char *idxStr, + int argc, sqlite3_value **argv +){ + int rc; + schema_vtab *pVtab = (schema_vtab *)(pVtabCursor->pVtab); + schema_cursor *pCur = (schema_cursor *)pVtabCursor; + pCur->rowid = 0; + finalize(&pCur->pTableList); + finalize(&pCur->pColumnList); + finalize(&pCur->pDbList); + rc = sqlite3_prepare(pVtab->db,"SELECT 0, 'main'", -1, &pCur->pDbList, 0); + return (rc==SQLITE_OK ? schemaNext(pVtabCursor) : rc); +} + +/* +** Analyse the WHERE condition. +*/ +static int schemaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ + return SQLITE_OK; +} + +/* +** A virtual table module that merely echos method calls into TCL +** variables. +*/ +static sqlite3_module schemaModule = { + 0, /* iVersion */ + schemaCreate, + schemaCreate, + schemaBestIndex, + schemaDestroy, + schemaDestroy, + schemaOpen, /* xOpen - open a cursor */ + schemaClose, /* xClose - close a cursor */ + schemaFilter, /* xFilter - configure scan constraints */ + schemaNext, /* xNext - advance a cursor */ + schemaEof, /* xEof */ + schemaColumn, /* xColumn - read data */ + schemaRowid, /* xRowid - read data */ + 0, /* xUpdate */ + 0, /* xBegin */ + 0, /* xSync */ + 0, /* xCommit */ + 0, /* xRollback */ + 0, /* xFindMethod */ + 0, /* xRename */ +}; + +/* +** Extension load function. +*/ +static int installSchemaModule(sqlite3 *db, sqlite3 *sdb){ + sqlite3_create_module(db, "schema", &schemaModule, (void *)sdb); + return 0; +} + +/* +** sj(zValue, zJoin) +** +** The following block contains the implementation of an aggregate +** function that returns a string. Each time the function is stepped, +** it appends data to an internal buffer. When the aggregate is finalized, +** the contents of the buffer are returned. +** +** The first time the aggregate is stepped the buffer is set to a copy +** of the first argument. The second time and subsequent times it is +** stepped a copy of the second argument is appended to the buffer, then +** a copy of the first. +** +** Example: +** +** INSERT INTO t1(a) VALUES('1'); +** INSERT INTO t1(a) VALUES('2'); +** INSERT INTO t1(a) VALUES('3'); +** SELECT sj(a, ', ') FROM t1; +** +** => "1, 2, 3" +** +*/ +struct StrBuffer { + char *zBuf; +}; +typedef struct StrBuffer StrBuffer; +static void joinFinalize(sqlite3_context *context){ + StrBuffer *p; + p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer)); + sqlite3_result_text(context, p->zBuf, -1, SQLITE_TRANSIENT); + sqlite3_free(p->zBuf); +} +static void joinStep( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + StrBuffer *p; + p = (StrBuffer *)sqlite3_aggregate_context(context, sizeof(StrBuffer)); + if( p->zBuf==0 ){ + p->zBuf = sqlite3_mprintf("%s", sqlite3_value_text(argv[0])); + }else{ + char *zTmp = p->zBuf; + p->zBuf = sqlite3_mprintf("%s%s%s", + zTmp, sqlite3_value_text(argv[1]), sqlite3_value_text(argv[0]) + ); + sqlite3_free(zTmp); + } +} + +/* +** dq(zString) +** +** This scalar function accepts a single argument and interprets it as +** a text value. The return value is the argument enclosed in double +** quotes. If any double quote characters are present in the argument, +** these are escaped. +** +** dq('the raven "Nevermore."') == '"the raven ""Nevermore."""' +*/ +static void doublequote( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + int ii; + char *zOut; + char *zCsr; + const char *zIn = (const char *)sqlite3_value_text(argv[0]); + int nIn = sqlite3_value_bytes(argv[0]); + + zOut = sqlite3_malloc(nIn*2+3); + zCsr = zOut; + *zCsr++ = '"'; + for(ii=0; iinMalloc ){ + nMalloc += (nMalloc + 16); + zOut = (char *)sqlite3_realloc(zOut, nMalloc); + } + memcpy(&zOut[nOut], zCopy, nCopy); + i += nReplace; + nOut += nCopy; + } + + sqlite3_result_text(context, zOut, nOut, SQLITE_TRANSIENT); + sqlite3_free(zOut); +} + +/* +** A callback for sqlite3_exec() invokes the callback specified by the +** GenfkeyCb structure pointed to by the void* passed as the first argument. +*/ +static int invokeCallback(void *p, int nArg, char **azArg, char **azCol){ + GenfkeyCb *pCb = (GenfkeyCb *)p; + return pCb->xData(pCb->pCtx, pCb->eType, azArg[0]); +} + +int detectSchemaProblem( + sqlite3 *db, /* Database connection */ + const char *zMessage, /* English language error message */ + const char *zSql, /* SQL statement to run */ + GenfkeyCb *pCb +){ + sqlite3_stmt *pStmt; + int rc; + rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); + if( rc!=SQLITE_OK ){ + return rc; + } + while( SQLITE_ROW==sqlite3_step(pStmt) ){ + char *zDel; + int iFk = sqlite3_column_int(pStmt, 0); + const char *zTab = (const char *)sqlite3_column_text(pStmt, 1); + zDel = sqlite3_mprintf("Error in table %s: %s", zTab, zMessage); + rc = pCb->xData(pCb->pCtx, pCb->eType, zDel); + sqlite3_free(zDel); + if( rc!=SQLITE_OK ) return rc; + zDel = sqlite3_mprintf( + "DELETE FROM temp.fkey WHERE from_tbl = %Q AND fkid = %d" + , zTab, iFk + ); + sqlite3_exec(db, zDel, 0, 0, 0); + sqlite3_free(zDel); + } + sqlite3_finalize(pStmt); + return SQLITE_OK; +} + +/* +** Create and populate temporary table "fkey". +*/ +static int populateTempTable(sqlite3 *db, GenfkeyCb *pCallback){ + int rc; + + rc = sqlite3_exec(db, + "CREATE VIRTUAL TABLE temp.v_fkey USING schema(foreign_key_list);" + "CREATE VIRTUAL TABLE temp.v_col USING schema(table_info);" + "CREATE VIRTUAL TABLE temp.v_idxlist USING schema(index_list);" + "CREATE VIRTUAL TABLE temp.v_idxinfo USING schema(index_info);" + "CREATE VIRTUAL TABLE temp.v_triggers USING schema(trigger_list);" + "CREATE TABLE temp.fkey AS " + "SELECT from_tbl, to_tbl, fkid, from_col, to_col, on_update, on_delete " + "FROM temp.v_fkey WHERE database = 'main';" + , 0, 0, 0 + ); + if( rc!=SQLITE_OK ) return rc; + + rc = detectSchemaProblem(db, "foreign key columns do not exist", + "SELECT fkid, from_tbl " + "FROM temp.fkey " + "WHERE to_col IS NOT NULL AND NOT EXISTS (SELECT 1 " + "FROM temp.v_col WHERE tablename=to_tbl AND name==to_col" + ")", pCallback + ); + if( rc!=SQLITE_OK ) return rc; + + /* At this point the temp.fkey table is mostly populated. If any foreign + ** keys were specified so that they implicitly refer to they primary + ** key of the parent table, the "to_col" values of the temp.fkey rows + ** are still set to NULL. + ** + ** This is easily fixed for single column primary keys, but not for + ** composites. With a composite primary key, there is no way to reliably + ** query sqlite for the order in which the columns that make up the + ** composite key were declared i.e. there is no way to tell if the + ** schema actually contains "PRIMARY KEY(a, b)" or "PRIMARY KEY(b, a)". + ** Therefore, this case is not handled. The following function call + ** detects instances of this case. + */ + rc = detectSchemaProblem(db, "implicit mapping to composite primary key", + "SELECT fkid, from_tbl " + "FROM temp.fkey " + "WHERE to_col IS NULL " + "GROUP BY fkid, from_tbl HAVING count(*) > 1", pCallback + ); + if( rc!=SQLITE_OK ) return rc; + + /* Detect attempts to implicitly map to the primary key of a table + ** that has no primary key column. + */ + rc = detectSchemaProblem(db, "implicit mapping to non-existant primary key", + "SELECT fkid, from_tbl " + "FROM temp.fkey " + "WHERE to_col IS NULL AND NOT EXISTS " + "(SELECT 1 FROM temp.v_col WHERE pk AND tablename = temp.fkey.to_tbl)" + , pCallback + ); + if( rc!=SQLITE_OK ) return rc; + + /* Fix all the implicit primary key mappings in the temp.fkey table. */ + rc = sqlite3_exec(db, + "UPDATE temp.fkey SET to_col = " + "(SELECT name FROM temp.v_col WHERE pk AND tablename=temp.fkey.to_tbl)" + " WHERE to_col IS NULL;" + , 0, 0, 0 + ); + if( rc!=SQLITE_OK ) return rc; + + /* Now check that all all parent keys are either primary keys or + ** subject to a unique constraint. + */ + rc = sqlite3_exec(db, + "CREATE TABLE temp.idx2 AS SELECT " + "il.tablename AS tablename," + "ii.indexname AS indexname," + "ii.name AS col " + "FROM temp.v_idxlist AS il, temp.v_idxinfo AS ii " + "WHERE il.isunique AND il.database='main' AND ii.indexname = il.name;" + "INSERT INTO temp.idx2 " + "SELECT tablename, 'pk', name FROM temp.v_col WHERE pk;" + + "CREATE TABLE temp.idx AS SELECT " + "tablename, indexname, sj(dq(col),',') AS cols " + "FROM (SELECT * FROM temp.idx2 ORDER BY col) " + "GROUP BY tablename, indexname;" + + "CREATE TABLE temp.fkey2 AS SELECT " + "fkid, from_tbl, to_tbl, sj(dq(to_col),',') AS cols " + "FROM (SELECT * FROM temp.fkey ORDER BY to_col) " + "GROUP BY fkid, from_tbl;" + + "CREATE TABLE temp.triggers AS SELECT " + "triggername FROM temp.v_triggers WHERE database='main' AND " + "triggername LIKE 'genfkey%';" + , 0, 0, 0 + ); + if( rc!=SQLITE_OK ) return rc; + rc = detectSchemaProblem(db, "foreign key is not unique", + "SELECT fkid, from_tbl " + "FROM temp.fkey2 " + "WHERE NOT EXISTS (SELECT 1 " + "FROM temp.idx WHERE tablename=to_tbl AND fkey2.cols==idx.cols" + ")", pCallback + ); + if( rc!=SQLITE_OK ) return rc; + + return rc; +} + +#define GENFKEY_ERROR 1 +#define GENFKEY_DROPTRIGGER 2 +#define GENFKEY_CREATETRIGGER 3 +static int genfkey_create_triggers( + sqlite3 *sdb, /* Connection to read schema from */ + const char *zDb, /* Name of db to read ("main", "temp") */ + void *pCtx, /* Context pointer to pass to xData */ + int (*xData)(void *, int, const char *) +){ + const char *zSql = + "SELECT multireplace('" + + "-- Triggers for foreign key mapping:\n" + "--\n" + "-- /from_readable/ REFERENCES /to_readable/\n" + "-- on delete /on_delete/\n" + "-- on update /on_update/\n" + "--\n" + + /* The "BEFORE INSERT ON " trigger. This trigger's job is to + ** throw an exception if the user tries to insert a row into the + ** referencing table for which there is no corresponding row in + ** the referenced table. + */ + "CREATE TRIGGER /name/_insert_referencing BEFORE INSERT ON /tbl/ WHEN \n" + " /key_notnull/ AND NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" + "BEGIN\n" + " SELECT RAISE(ABORT, ''constraint failed'');\n" + "END;\n" + + /* The "BEFORE UPDATE ON " trigger. This trigger's job + ** is to throw an exception if the user tries to update a row in the + ** referencing table causing it to correspond to no row in the + ** referenced table. + */ + "CREATE TRIGGER /name/_update_referencing BEFORE\n" + " UPDATE OF /rkey_list/ ON /tbl/ WHEN \n" + " /key_notnull/ AND \n" + " NOT EXISTS (SELECT 1 FROM /ref/ WHERE /cond1/)\n" + "BEGIN\n" + " SELECT RAISE(ABORT, ''constraint failed'');\n" + "END;\n" + + + /* The "BEFORE DELETE ON " trigger. This trigger's job + ** is to detect when a row is deleted from the referenced table to + ** which rows in the referencing table correspond. The action taken + ** depends on the value of the 'ON DELETE' clause. + */ + "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n" + " EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n" + "BEGIN\n" + " /delete_action/\n" + "END;\n" + + /* The "BEFORE DELETE ON " trigger. This trigger's job + ** is to detect when the key columns of a row in the referenced table + ** to which one or more rows in the referencing table correspond are + ** updated. The action taken depends on the value of the 'ON UPDATE' + ** clause. + */ + "CREATE TRIGGER /name/_update_referenced AFTER\n" + " UPDATE OF /fkey_list/ ON /ref/ WHEN \n" + " EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n" + "BEGIN\n" + " /update_action/\n" + "END;\n" + "'" + + /* These are used in the SQL comment written above each set of triggers */ + ", '/from_readable/', from_tbl || '(' || sj(from_col, ', ') || ')'" + ", '/to_readable/', to_tbl || '(' || sj(to_col, ', ') || ')'" + ", '/on_delete/', on_delete" + ", '/on_update/', on_update" + + ", '/name/', 'genfkey' || min(rowid)" + ", '/tbl/', dq(from_tbl)" + ", '/ref/', dq(to_tbl)" + ", '/key_notnull/', sj('new.' || dq(from_col) || ' IS NOT NULL', ' AND ')" + + ", '/fkey_list/', sj(to_col, ', ')" + ", '/rkey_list/', sj(from_col, ', ')" + + ", '/cond1/', sj(multireplace('new./from/ == /to/'" + ", '/from/', dq(from_col)" + ", '/to/', dq(to_col)" + "), ' AND ')" + ", '/cond2/', sj(multireplace('old./to/ == /from/'" + ", '/from/', dq(from_col)" + ", '/to/', dq(to_col)" + "), ' AND ')" + + ", '/update_action/', CASE on_update " + "WHEN 'SET NULL' THEN " + "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " + ", '/setlist/', sj(from_col||' = NULL',', ')" + ", '/tbl/', dq(from_tbl)" + ", '/where/', sj(from_col||' = old.'||dq(to_col),' AND ')" + ")" + "WHEN 'CASCADE' THEN " + "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " + ", '/setlist/', sj(dq(from_col)||' = new.'||dq(to_col),', ')" + ", '/tbl/', dq(from_tbl)" + ", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')" + ")" + "ELSE " + " 'SELECT RAISE(ABORT, ''constraint failed'');'" + "END " + + ", '/delete_action/', CASE on_delete " + "WHEN 'SET NULL' THEN " + "multireplace('UPDATE /tbl/ SET /setlist/ WHERE /where/;' " + ", '/setlist/', sj(from_col||' = NULL',', ')" + ", '/tbl/', dq(from_tbl)" + ", '/where/', sj(from_col||' = old.'||dq(to_col),' AND ')" + ")" + "WHEN 'CASCADE' THEN " + "multireplace('DELETE FROM /tbl/ WHERE /where/;' " + ", '/tbl/', dq(from_tbl)" + ", '/where/', sj(dq(from_col)||' = old.'||dq(to_col),' AND ')" + ")" + "ELSE " + " 'SELECT RAISE(ABORT, ''constraint failed'');'" + "END " + + ") FROM temp.fkey " + "GROUP BY from_tbl, fkid" + ; + + int rc; + const int enc = SQLITE_UTF8; + sqlite3 *db = 0; + + GenfkeyCb cb; + cb.xData = xData; + cb.pCtx = pCtx; + + /* Open the working database handle. */ + rc = sqlite3_open(":memory:", &db); + if( rc!=SQLITE_OK ) goto genfkey_exit; + + /* Create the special scalar and aggregate functions used by this program. */ + sqlite3_create_function(db, "dq", 1, enc, 0, doublequote, 0, 0); + sqlite3_create_function(db, "multireplace", -1, enc, db, multireplace, 0, 0); + sqlite3_create_function(db, "sj", 2, enc, 0, 0, joinStep, joinFinalize); + + /* Install the "schema" virtual table module */ + installSchemaModule(db, sdb); + + /* Create and populate a temp table with the information required to + ** build the foreign key triggers. See function populateTempTable() + ** for details. + */ + cb.eType = GENFKEY_ERROR; + rc = populateTempTable(db, &cb); + if( rc!=SQLITE_OK ) goto genfkey_exit; + + /* Unless the --no-drop option was specified, generate DROP TRIGGER + ** statements to drop any triggers in the database generated by a + ** previous run of this program. + */ + cb.eType = GENFKEY_DROPTRIGGER; + rc = sqlite3_exec(db, + "SELECT 'DROP TRIGGER main.' || dq(triggername) || ';' FROM triggers" + ,invokeCallback, (void *)&cb, 0 + ); + if( rc!=SQLITE_OK ) goto genfkey_exit; + + /* Run the main query to create the trigger definitions. */ + cb.eType = GENFKEY_CREATETRIGGER; + rc = sqlite3_exec(db, zSql, invokeCallback, (void *)&cb, 0); + if( rc!=SQLITE_OK ) goto genfkey_exit; + +genfkey_exit: + sqlite3_close(db); + return rc; +} + + +#endif +/* End genfkey logic. */ +/*************************************************************************/ +/*************************************************************************/ + /* Saved resource information for the beginning of an operation */ static struct rusage sBegin; @@ -926,6 +1766,63 @@ static int run_schema_dump_query( return rc; } + +#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY) +struct GenfkeyCmd { + sqlite3 *db; /* Database handle */ + struct callback_data *pCb; /* Callback data */ + int isIgnoreErrors; /* True for --ignore-errors */ + int isExec; /* True for --exec */ + int isNoDrop; /* True for --no-drop */ + int nErr; /* Number of errors seen so far */ +}; +typedef struct GenfkeyCmd GenfkeyCmd; + +static int genfkeyParseArgs(GenfkeyCmd *p, char **azArg, int nArg){ + int ii; + memset(p, 0, sizeof(GenfkeyCmd)); + + for(ii=0; ii2 && n<10 && 0==strncmp(azArg[ii], "--no-drop", n) ){ + p->isNoDrop = 1; + }else if( n>2 && n<16 && 0==strncmp(azArg[ii], "--ignore-errors", n) ){ + p->isIgnoreErrors = 1; + }else if( n>2 && n<7 && 0==strncmp(azArg[ii], "--exec", n) ){ + p->isExec = 1; + }else{ + fprintf(stderr, "unknown option: %s\n", azArg[ii]); + return -1; + } + } + + return SQLITE_OK; +} + +static int genfkeyCmdCb(void *pCtx, int eType, const char *z){ + GenfkeyCmd *p = (GenfkeyCmd *)pCtx; + if( eType==GENFKEY_ERROR && !p->isIgnoreErrors ){ + p->nErr++; + fprintf(stderr, "%s\n", z); + } + + if( p->nErr==0 && ( + (eType==GENFKEY_CREATETRIGGER) + || (eType==GENFKEY_DROPTRIGGER && !p->isNoDrop) + )){ + if( p->isExec ){ + sqlite3_exec(p->db, z, 0, 0, 0); + }else{ + char *zCol = "sql"; + callback((void *)p->pCb, 1, (char **)&z, (char **)&zCol); + } + } + + return SQLITE_OK; +} +#endif + /* ** Text of a help message */ @@ -937,6 +1834,13 @@ static char zHelp[] = ".echo ON|OFF Turn command echo on or off\n" ".exit Exit this program\n" ".explain ON|OFF Turn output mode suitable for EXPLAIN on or off.\n" +#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY) + ".genfkey ?OPTIONS? Options are:\n" + " --no-drop: Do not drop old fkey triggers.\n" + " --ignore-errors: Ignore tables with fkey errors\n" + " --exec: Execute generated SQL immediately\n" + " --help: Print help message for .genfkey\n" +#endif ".header(s) ON|OFF Turn display of headers on or off\n" ".help Show this message\n" ".import FILE TABLE Import data from FILE into TABLE\n" @@ -1240,6 +2144,17 @@ static int do_meta_command(char *zLine, struct callback_data *p){ } }else +#if !defined(SQLITE_OMIT_VIRTUALTABLE) && !defined(SQLITE_OMIT_SUBQUERY) + if( c=='g' && strncmp(azArg[0], "genfkey", n)==0 ){ + GenfkeyCmd cmd; + if( 0==genfkeyParseArgs(&cmd, &azArg[1], nArg-1) ){ + cmd.db = p->db; + cmd.pCb = p; + genfkey_create_triggers(p->db, "main", (void *)&cmd, genfkeyCmdCb); + } + }else +#endif + if( c=='h' && (strncmp(azArg[0], "header", n)==0 || strncmp(azArg[0], "headers", n)==0 )&& nArg>1 ){ p->showHeader = booleanValue(azArg[1]); diff --git a/tool/genfkey.test b/tool/genfkey.test index c55511f63f..422de838af 100644 --- a/tool/genfkey.test +++ b/tool/genfkey.test @@ -36,7 +36,7 @@ do_test genfkey-1.1 { } } {} do_test genfkey-1.2 { - execsql [exec ./genfkey test.db] + execsql [exec ./sqlite3 test.db .genfkey] } {} do_test genfkey-1.3 { catchsql { INSERT INTO t2 VALUES(1, 2) } @@ -129,7 +129,7 @@ do_test genfkey-2.1 { } } {} do_test genfkey-2.2 { - execsql [exec ./genfkey test.db] + execsql [exec ./sqlite3 test.db .genfkey] } {} do_test genfkey-2.3 { execsql { @@ -186,7 +186,7 @@ do_test genfkey-3.1 { } } {} do_test genfkey-3.2 { - execsql [exec ./genfkey test.db] + execsql [exec ./sqlite3 test.db .genfkey] } {} do_test genfkey-3.3 { execsql { @@ -249,7 +249,7 @@ do_test genfkey-4.1 { } {} do_test genfkey-4.X { - set rc [catch {exec ./genfkey test.db} msg] + set rc [catch {exec ./sqlite3 test.db .genfkey} msg] list $rc $msg } "1 {[string trim { Error in table t5: foreign key columns do not exist -- 2.47.2