Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the experimental ".fkey_missing_indexes" command to the shell tool. To identify indexes that should be created on child keys if FK processing is to be enabled. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | fkey-missing-indexes |
Files: | files | file ages | folders |
SHA1: |
7df23aca1f7c7b769d614d740b3fda30 |
User & Date: | dan 2016-12-14 19:28:27.708 |
Context
2016-12-15
| ||
06:01 | Add the -groupbyparent option to the ".fkey_missing_indexes" command. (check-in: 976c51b483 user: dan tags: fkey-missing-indexes) | |
2016-12-14
| ||
19:28 | Add the experimental ".fkey_missing_indexes" command to the shell tool. To identify indexes that should be created on child keys if FK processing is to be enabled. (check-in: 7df23aca1f user: dan tags: fkey-missing-indexes) | |
14:07 | Refactor the Table.nRef field as Table.nTabRef for easier grepping. (check-in: 9cae4c2e30 user: drh tags: trunk) | |
Changes
Changes to src/shell.c.
︙ | ︙ | |||
2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 | ".dump ?TABLE? ... Dump the database in an SQL text format\n" " If TABLE specified, only dump tables matching\n" " LIKE pattern TABLE.\n" ".echo on|off Turn command echo on or off\n" ".eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN\n" ".exit Exit this program\n" ".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n" ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n" ".headers 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" #ifndef SQLITE_OMIT_TEST_CONTROL ".imposter INDEX TABLE Create imposter table TABLE on index INDEX\n" #endif | > | 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 | ".dump ?TABLE? ... Dump the database in an SQL text format\n" " If TABLE specified, only dump tables matching\n" " LIKE pattern TABLE.\n" ".echo on|off Turn command echo on or off\n" ".eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN\n" ".exit Exit this program\n" ".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n" ".fkey_missing_indexes Find indexes to make FK processing more efficient\n" ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n" ".headers 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" #ifndef SQLITE_OMIT_TEST_CONTROL ".imposter INDEX TABLE Create imposter table TABLE on index INDEX\n" #endif |
︙ | ︙ | |||
3249 3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 | sqlite3_free(z); #else rc = unlink(zFilename); #endif return rc; } /* ** If an input line begins with "." then invoke this routine to ** process that line. ** ** Return 1 on error, 2 to exit, and 0 otherwise. */ static int do_meta_command(char *zLine, ShellState *p){ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 3309 3310 3311 3312 3313 3314 3315 3316 3317 3318 3319 3320 3321 3322 3323 3324 3325 3326 3327 3328 3329 3330 3331 3332 3333 3334 3335 3336 3337 3338 3339 3340 3341 3342 3343 3344 3345 3346 3347 3348 3349 3350 3351 3352 3353 3354 3355 3356 3357 3358 3359 3360 3361 3362 3363 3364 3365 3366 3367 3368 3369 3370 3371 3372 3373 3374 3375 3376 3377 3378 3379 3380 3381 3382 3383 3384 3385 3386 3387 3388 3389 3390 3391 3392 3393 3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 3409 3410 3411 3412 3413 3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 3445 3446 3447 3448 3449 3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 3463 3464 3465 3466 3467 3468 3469 3470 3471 3472 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 3514 3515 3516 3517 3518 3519 3520 3521 3522 3523 3524 3525 3526 3527 3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 3538 3539 3540 3541 3542 3543 3544 3545 3546 3547 3548 3549 3550 3551 3552 3553 3554 3555 3556 3557 3558 3559 3560 3561 3562 3563 3564 3565 3566 3567 3568 3569 3570 3571 3572 3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 3587 3588 3589 3590 3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 3606 3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645 3646 3647 3648 3649 3650 3651 3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 3664 3665 3666 3667 3668 3669 3670 3671 3672 3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 3685 3686 3687 3688 3689 3690 3691 3692 3693 3694 3695 3696 3697 3698 3699 3700 3701 3702 3703 | sqlite3_free(z); #else rc = unlink(zFilename); #endif return rc; } /************************************************************************** ** Beginning of implementation of .fkey_missing_indexes */ typedef struct PragmaVtab PragmaVtab; typedef struct PragmaCursor PragmaCursor; typedef struct PragmaConfig PragmaConfig; /* ** Table structure for "pragma" eponymous virtual tables. */ struct PragmaVtab { sqlite3_vtab base; PragmaConfig *pConfig; sqlite3 *db; }; /* ** Cursor structure for "pragma" eponymous virtual tables. */ struct PragmaCursor { sqlite3_vtab_cursor base; sqlite3_stmt *pSelect; sqlite3_stmt *pPragma; sqlite_int64 iRowid; }; struct PragmaConfig { const char *zModule; const char *zSchema; const char *zPragma; }; /* ** Pragma virtual table module xConnect method. */ static int shellPragmaConnect( sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr ){ PragmaConfig *pConfig = (PragmaConfig*)pAux; PragmaVtab *pTab = 0; int rc; rc = sqlite3_declare_vtab(db, pConfig->zSchema); if( rc==SQLITE_OK ){ pTab = (PragmaVtab*)sqlite3_malloc(sizeof(PragmaVtab)); if( pTab==0 ){ rc = SQLITE_NOMEM; }else{ memset(pTab, 0, sizeof(PragmaVtab)); pTab->db = db; pTab->pConfig = pConfig; } }else{ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db)); } *ppVtab = (sqlite3_vtab*)pTab; return rc; } /* ** Pragma virtual table module xDisconnect method. */ static int shellPragmaDisconnect(sqlite3_vtab *pVtab){ PragmaVtab *pTab = (PragmaVtab*)pVtab; sqlite3_free(pTab); return SQLITE_OK; } /* ** Pragma virtual table module xBestIndex method. */ static int shellPragmaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ return SQLITE_OK; } /* ** Pragma virtual table module xOpen method. */ static int shellPragmaOpen(sqlite3_vtab *pVtab, sqlite3_vtab_cursor **ppCursor){ PragmaCursor *pCsr; pCsr = (PragmaCursor*)sqlite3_malloc(sizeof(PragmaCursor)); if( pCsr==0 ) return SQLITE_NOMEM; memset(pCsr, 0, sizeof(PragmaCursor)); pCsr->base.pVtab = pVtab; *ppCursor = (sqlite3_vtab_cursor*)pCsr; return SQLITE_OK; } /* ** Pragma virtual table module xClose method. */ static int shellPragmaClose(sqlite3_vtab_cursor *cur){ return SQLITE_OK; } /* ** Pragma virtual table module xNext method. */ static int shellPragmaNext(sqlite3_vtab_cursor *pVtabCursor){ PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor; PragmaVtab *pTab = (PragmaVtab*)(pVtabCursor->pVtab); sqlite3 *db = pTab->db; int rc = SQLITE_OK; /* Increment the xRowid value */ pCsr->iRowid++; while( 1 ){ assert( rc==SQLITE_OK ); if( pCsr->pPragma ){ if( SQLITE_ROW!=sqlite3_step(pCsr->pPragma) ){ rc = sqlite3_finalize(pCsr->pPragma); pCsr->pPragma = 0; } } if( rc==SQLITE_OK && pCsr->pPragma==0 ){ if( SQLITE_ROW!=sqlite3_step(pCsr->pSelect) ){ rc = sqlite3_finalize(pCsr->pSelect); pCsr->pSelect = 0; }else{ const char *zName = (const char*)sqlite3_column_text(pCsr->pSelect, 0); char *zSql = sqlite3_mprintf(pTab->pConfig->zPragma, zName); if( zSql==0 ){ rc = SQLITE_NOMEM; }else{ rc = sqlite3_prepare_v2(db, zSql, -1, &pCsr->pPragma, 0); if( rc!=SQLITE_OK ){ pTab->base.zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); }else{ continue; } } } } break; } return rc; } /* ** Pragma virtual table module xFilter method. */ static int shellPragmaFilter( sqlite3_vtab_cursor *pVtabCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor; PragmaVtab *pTab = (PragmaVtab*)(pVtabCursor->pVtab); int rc; sqlite3_finalize(pCsr->pSelect); sqlite3_finalize(pCsr->pPragma); pCsr->pSelect = 0; pCsr->pPragma = 0; pCsr->iRowid = 0; rc = sqlite3_prepare_v2(pTab->db, "SELECT name FROM sqlite_master WHERE type = 'table' AND rootpage>0", -1, &pCsr->pSelect, 0 ); if( rc!=SQLITE_OK ){ pTab->base.zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db)); return rc; } return shellPragmaNext(pVtabCursor); } /* ** Pragma virtual table module xEof method. */ static int shellPragmaEof(sqlite3_vtab_cursor *pVtabCursor){ PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor; return (pCsr->pSelect==0); } /* ** Pragma virtual table module xColumn method. */ static int shellPragmaColumn( sqlite3_vtab_cursor *pVtabCursor, sqlite3_context *ctx, int i ){ PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor; switch( i ){ case 0: /* "database" */ sqlite3_result_text(ctx, "main", -1, SQLITE_STATIC); break; case 1: /* "child"/"table" */ sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pSelect, 0)); break; default: sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pPragma, i-2)); break; } return SQLITE_OK; } /* ** Pragma virtual table module xRowid method. */ static int shellPragmaRowid(sqlite3_vtab_cursor *pVtabCursor, sqlite_int64 *p){ PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor; *p = pCsr->iRowid; return SQLITE_OK; } /* ** Register the virtual table module with the supplied database handle. */ static int shellPragmaRegister(sqlite3 *db){ static sqlite3_module shellPragmaModule = { 0, /* iVersion */ shellPragmaConnect, /* xCreate - create a table */ shellPragmaConnect, /* xConnect - connect to an existing table */ shellPragmaBestIndex, /* xBestIndex - Determine search strategy */ shellPragmaDisconnect, /* xDisconnect - Disconnect from a table */ shellPragmaDisconnect, /* xDestroy - Drop a table */ shellPragmaOpen, /* xOpen - open a cursor */ shellPragmaClose, /* xClose - close a cursor */ shellPragmaFilter, /* xFilter - configure scan constraints */ shellPragmaNext, /* xNext - advance a cursor */ shellPragmaEof, /* xEof */ shellPragmaColumn, /* xColumn - read data */ shellPragmaRowid, /* xRowid - read data */ 0, /* xUpdate - write data */ 0, /* xBegin - begin transaction */ 0, /* xSync - sync transaction */ 0, /* xCommit - commit transaction */ 0, /* xRollback - rollback transaction */ 0, /* xFindFunction - function overloading */ 0, /* xRename - rename the table */ 0, /* xSavepoint */ 0, /* xRelease */ 0 /* xRollbackTo */ }; int rc = SQLITE_OK; int i; static PragmaConfig aConfig[] = { { "pragma_foreign_key_list", "CREATE TABLE x(database, child, " "id, seq, parent, child_col, parent_col, on_update, on_delete, match)", "PRAGMA foreign_key_list = %Q" }, { "pragma_table_info", "CREATE TABLE x(database, tbl, " "cid, name, type, not_null, dflt_value, pk)", "PRAGMA table_info = %Q" } }; for(i=0; rc==SQLITE_OK && i<sizeof(aConfig)/sizeof(aConfig[0]); i++){ rc = sqlite3_create_module_v2( db, aConfig[i].zModule, &shellPragmaModule, (void*)&aConfig[i], 0 ); } return rc; } /* ** The implementation of SQL scalar function fkey_collate_clause(). This ** scalar function is always called with four arguments - the parent ** table name, the parent column name, the child table name and the child ** column name. ** ** fkey_collate_clause('parent-tab', 'parent-col', 'child-tab', 'child-col') ** ** If either of the named tables or columns do not exist, this function ** returns an empty string. An empty string is also returned if both tables ** and columns exist but have the same default collation sequence. Or, ** if both exist but the default collation sequences are different, this ** function returns the string " COLLATE <parent-collation>", where ** <parent-collation> is the default collation sequence of the parent column. */ static void shellFkeyCollateClause( sqlite3_context *pCtx, int nVal, sqlite3_value **apVal ){ sqlite3 *db = sqlite3_context_db_handle(pCtx); const char *zParent; const char *zParentCol; const char *zParentSeq; const char *zChild; const char *zChildCol; const char *zChildSeq; int rc; assert( nVal==4 ); zParent = (const char*)sqlite3_value_text(apVal[0]); zParentCol = (const char*)sqlite3_value_text(apVal[1]); zChild = (const char*)sqlite3_value_text(apVal[2]); zChildCol = (const char*)sqlite3_value_text(apVal[3]); sqlite3_result_text(pCtx, "", -1, SQLITE_STATIC); rc = sqlite3_table_column_metadata( db, "main", zParent, zParentCol, 0, &zParentSeq, 0, 0, 0 ); if( rc==SQLITE_OK ){ rc = sqlite3_table_column_metadata( db, "main", zChild, zChildCol, 0, &zChildSeq, 0, 0, 0 ); } if( rc==SQLITE_OK && sqlite3_stricmp(zParentSeq, zChildSeq) ){ char *z = sqlite3_mprintf(" COLLATE %s", zParentSeq); sqlite3_result_text(pCtx, z, -1, SQLITE_TRANSIENT); sqlite3_free(z); } } /* ** The implementation of dot-command ".fkey_missing_indexes". */ static int shellFkeyMissingIndexes( ShellState *pState, /* Current shell tool state */ char **azArg, /* Array of arguments passed to dot command */ int nArg /* Number of entries in azArg[] */ ){ sqlite3 *db = pState->db; FILE *out = pState->out; int bVerbose = 0; int rc; sqlite3_stmt *pSql = 0; const char *zSql = "SELECT " " 'EXPLAIN QUERY PLAN SELECT rowid FROM ' || quote(child) || ' WHERE ' " " || group_concat(quote(child) || '.' || quote(child_col) || '=?' || " " fkey_collate_clause(parent, parent_col, child, child_col),' AND ')" ", " " 'SEARCH TABLE ' || child || ' USING COVERING INDEX*('" " || group_concat('*=?', ' AND ') || ')'" ", " " child || '(' || group_concat(child_col, ', ') || ')'" ", " " parent || '(' || group_concat(COALESCE(parent_col, " " (SELECT name FROM pragma_table_info WHERE tbl=parent AND pk=seq+1)" " )) || ')'" ", " " 'CREATE INDEX ' || quote(child ||'_'|| group_concat(child_col, '_'))" " || ' ON ' || quote(child) || '('" " || group_concat(quote(child_col) ||" " fkey_collate_clause(parent, parent_col, child, child_col), ', ')" " || ');'" "FROM pragma_foreign_key_list AS o GROUP BY child, id" ; if( nArg>2 ){ raw_printf(stderr, "Usage: .fkey_lint ?verbose-flag?\n"); } if( nArg==2 ) bVerbose = booleanValue(azArg[1]); /* Register the pragma eponymous virtual tables */ rc = shellPragmaRegister(db); /* Register the fkey_collate_clause() SQL function */ if( rc==SQLITE_OK ){ rc = sqlite3_create_function(db, "fkey_collate_clause", 4, SQLITE_UTF8, 0, shellFkeyCollateClause, 0, 0 ); } if( rc==SQLITE_OK ){ rc = sqlite3_prepare_v2(db, zSql, -1, &pSql, 0); } if( rc==SQLITE_OK ){ int rc2; while( SQLITE_ROW==sqlite3_step(pSql) ){ int res = -1; sqlite3_stmt *pExplain = 0; const char *zEQP = (const char*)sqlite3_column_text(pSql, 0); const char *zGlob = (const char*)sqlite3_column_text(pSql, 1); const char *zFrom = (const char*)sqlite3_column_text(pSql, 2); const char *zTarget = (const char*)sqlite3_column_text(pSql, 3); const char *zCI = (const char*)sqlite3_column_text(pSql, 4); rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0); if( rc!=SQLITE_OK ) break; if( SQLITE_ROW==sqlite3_step(pExplain) ){ const char *zPlan = (const char*)sqlite3_column_text(pExplain, 3); res = (0==sqlite3_strglob(zGlob, zPlan)); } rc = sqlite3_finalize(pExplain); if( rc!=SQLITE_OK ) break; if( res<0 ){ raw_printf(stderr, "Error: internal error"); break; }else if( res==0 ){ raw_printf(out, "%s --> %s\n", zCI, zTarget); }else if( bVerbose ){ raw_printf(out, "/* no extra indexes required for %s -> %s */\n", zFrom, zTarget ); } } if( rc!=SQLITE_OK ){ raw_printf(stderr, "%s\n", sqlite3_errmsg(db)); } rc2 = sqlite3_finalize(pSql); if( rc==SQLITE_OK && rc2!=SQLITE_OK ){ rc = rc2; raw_printf(stderr, "%s\n", sqlite3_errmsg(db)); } }else{ raw_printf(stderr, "%s\n", sqlite3_errmsg(db)); } return rc; } /* ** End of implementation of .fkey_missing_indexes **************************************************************************/ /* ** If an input line begins with "." then invoke this routine to ** process that line. ** ** Return 1 on error, 2 to exit, and 0 otherwise. */ static int do_meta_command(char *zLine, ShellState *p){ |
︙ | ︙ | |||
3561 3562 3563 3564 3565 3566 3567 3568 3569 3570 3571 3572 3573 3574 | if( p->mode==MODE_Explain ) p->mode = p->normalMode; p->autoExplain = 0; }else if( val==99 ){ if( p->mode==MODE_Explain ) p->mode = p->normalMode; p->autoExplain = 1; } }else if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){ ShellState data; char *zErrMsg = 0; int doStats = 0; memcpy(&data, p, sizeof(data)); data.showHeader = 0; | > > > > | 4002 4003 4004 4005 4006 4007 4008 4009 4010 4011 4012 4013 4014 4015 4016 4017 4018 4019 | if( p->mode==MODE_Explain ) p->mode = p->normalMode; p->autoExplain = 0; }else if( val==99 ){ if( p->mode==MODE_Explain ) p->mode = p->normalMode; p->autoExplain = 1; } }else if( c=='f' && strncmp(azArg[0], "fkey_missing_indexes", n)==0 ){ shellFkeyMissingIndexes(p, azArg, nArg); }else if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){ ShellState data; char *zErrMsg = 0; int doStats = 0; memcpy(&data, p, sizeof(data)); data.showHeader = 0; |
︙ | ︙ |
Added test/shell6.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 | # 2016 December 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix shell6 set CLI [test_find_cli] db close forcedelete test.db test.db-journal test.db-wal foreach {tn schema output} { 1 { CREATE TABLE p1(a PRIMARY KEY, b); CREATE TABLE c1(x, y REFERENCES p1); } { CREATE INDEX 'c1_y' ON 'c1'('y'); --> p1(a) } 2 { CREATE TABLE p1(a PRIMARY KEY, b); CREATE TABLE c2(x REFERENCES p1, y REFERENCES p1); } { CREATE INDEX 'c2_y' ON 'c2'('y'); --> p1(a) CREATE INDEX 'c2_x' ON 'c2'('x'); --> p1(a) } 3 { CREATE TABLE 'p 1'(a, b, c, PRIMARY KEY(c, b)); CREATE TABLE 'c 1'(x, y, z, FOREIGN KEY (z, y) REFERENCES 'p 1'); } { CREATE INDEX 'c 1_z_y' ON 'c 1'('z', 'y'); --> p 1(c,b) } 4 { CREATE TABLE p1(a, 'b b b' PRIMARY KEY); CREATE TABLE c1('x y z' REFERENCES p1); CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT NULL; } { } 5 { CREATE TABLE p1(a, 'b b b' PRIMARY KEY); CREATE TABLE c1('x y z' REFERENCES p1); CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT 12; } { CREATE INDEX 'c1_x y z' ON 'c1'('x y z'); --> p1(b b b) } 6 { CREATE TABLE x1(a, b, c, UNIQUE(a, b)); CREATE TABLE y1(a, b, c, FOREIGN KEY(b, a) REFERENCES x1(a, b)); CREATE INDEX y1i ON y1(a, c, b); } { CREATE INDEX 'y1_b_a' ON 'y1'('b', 'a'); --> x1(a,b) } 6 { CREATE TABLE x1(a COLLATE nocase, b, UNIQUE(a)); CREATE TABLE y1(a COLLATE rtrim REFERENCES x1(a)); } { CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a) } } { forcedelete test.db sqlite3 db test.db execsql $schema set expected "" foreach line [split $output "\n"] { set line [string trim $line] if {$line!=""} { append expected "$line\n" } } do_test 1.$tn.1 { set RES [catchcmd test.db .fkey_missing_indexes] } [list 0 [string trim $expected]] do_test 1.$tn.2 { execsql [lindex $RES 1] catchcmd test.db .fkey_missing_indexes } {0 {}} db close } finish_test |