Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add the -groupbyparent option to the ".fkey_missing_indexes" command. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | fkey-missing-indexes |
| Files: | files | file ages | folders |
| SHA1: |
976c51b4836dfba2ce9b246334a85bda |
| User & Date: | dan 2016-12-15 06:01:40 |
Context
|
2016-12-16
| ||
| 16:13 | Merge the pragma-as-vtab change into this branch. check-in: 4ba45e72 user: dan tags: fkey-missing-indexes | |
|
2016-12-15
| ||
| 06:01 | Add the -groupbyparent option to the ".fkey_missing_indexes" command. check-in: 976c51b4 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: 7df23aca user: dan tags: fkey-missing-indexes | |
Changes
Changes to src/shell.c.
3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 .... 3615 3616 3617 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 .... 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 |
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) || '=?' || "
................................................................................
" )) || ')'"
", "
" '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,
................................................................................
);
}
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 ){
|
| > > > > > | > < > > > > > > > > > > | > | < > > > > > > > > > > > > > > > > | | | | | | | | > > |
3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 3606 3607 3608 .... 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 .... 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 3704 3705 3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 |
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; /* If -verbose is present */
int bGroupByParent = 0; /* If -groupbyparent is present */
int i;
const char *zIndent = "";
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) || '=?' || "
................................................................................
" )) || ')'"
", "
" '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), ', ')"
" || ');'"
", "
" parent "
"FROM pragma_foreign_key_list GROUP BY child, id "
"ORDER BY (CASE WHEN ? THEN parent ELSE CHILD END)"
;
for(i=1; i<nArg; i++){
int n = strlen(azArg[i]);
if( n>1 && sqlite3_strnicmp("-verbose", azArg[i], n)==0 ){
bVerbose = 1;
}
else if( n>1 && sqlite3_strnicmp("-groupbyparent", azArg[i], n)==0 ){
bGroupByParent = 1;
zIndent = " ";
}
else{
raw_printf(stderr, "Usage: .fkey_lint ?-verbose? ?-groupbyparent?\n");
return SQLITE_ERROR;
}
}
/* 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,
................................................................................
);
}
if( rc==SQLITE_OK ){
rc = sqlite3_prepare_v2(db, zSql, -1, &pSql, 0);
}
if( rc==SQLITE_OK ){
sqlite3_bind_int(pSql, 1, bGroupByParent);
}
if( rc==SQLITE_OK ){
int rc2;
char *zPrev = 0;
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);
const char *zParent = (const char*)sqlite3_column_text(pSql, 5);
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( bGroupByParent
&& (bVerbose || res==0)
&& (zPrev==0 || sqlite3_stricmp(zParent, zPrev))
){
raw_printf(out, "-- Parent table %s\n", zParent);
sqlite3_free(zPrev);
zPrev = sqlite3_mprintf("%s", zParent);
}
if( res==0 ){
raw_printf(out, "%s%s --> %s\n", zIndent, zCI, zTarget);
}else if( bVerbose ){
raw_printf(out, "%s/* no extra indexes required for %s -> %s */\n",
zIndent, zFrom, zTarget
);
}
}
}
sqlite3_free(zPrev);
if( rc!=SQLITE_OK ){
raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
}
rc2 = sqlite3_finalize(pSql);
if( rc==SQLITE_OK && rc2!=SQLITE_OK ){
|