Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | In checkindex.c, use C code instead of SQL/group_concat() to compose various SQL clauses. This is to make it easier to support indexes on expressions. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | checkindex |
Files: | files | file ages | folders |
SHA3-256: |
940606b3af059eb3f79d71fec871ea88 |
User & Date: | dan 2017-10-30 17:05:18.290 |
Context
2017-10-30
| ||
19:38 | Add support for indexes on expressions to incremental_index_check. (check-in: 8c1c701fdb user: dan tags: checkindex) | |
17:05 | In checkindex.c, use C code instead of SQL/group_concat() to compose various SQL clauses. This is to make it easier to support indexes on expressions. (check-in: 940606b3af user: dan tags: checkindex) | |
08:04 | Fix an issue in incremental_index_check with indexes that use non-default collation sequences. (check-in: 3ebb2351e2 user: dan tags: checkindex) | |
Changes
Changes to ext/repair/checkindex.c.
︙ | ︙ | |||
43 44 45 46 47 48 49 | struct CidxCursor { sqlite3_vtab_cursor base; /* Base class. Must be first */ sqlite3_stmt *pStmt; }; typedef struct CidxColumn CidxColumn; struct CidxColumn { | | > > > | > | > > | 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | struct CidxCursor { sqlite3_vtab_cursor base; /* Base class. Must be first */ sqlite3_stmt *pStmt; }; typedef struct CidxColumn CidxColumn; struct CidxColumn { char *zExpr; /* Text for indexed expression */ int bDesc; /* True for DESC columns, otherwise false */ int bKey; /* Part of index, not PK */ }; typedef struct CidxIndex CidxIndex; struct CidxIndex { int nCol; /* Elements in aCol[] array */ CidxColumn aCol[1]; /* Array of indexed columns */ }; static void *cidxMalloc(int *pRc, int n){ void *pRet = 0; assert( n!=0 ); if( *pRc==SQLITE_OK ){ pRet = sqlite3_malloc(n); |
︙ | ︙ | |||
253 254 255 256 257 258 259 260 261 262 263 | if( *pRc==SQLITE_OK ){ int n = strlen(zStr); zRet = cidxMalloc(pRc, n+1); if( zRet ) memcpy(zRet, zStr, n+1); } return zRet; } static int cidxLookupIndex( CidxCursor *pCsr, /* Cursor object */ const char *zIdx, /* Name of index to look up */ | > > > > > > > > > > < | | < < < < < | < < < | | | < < < < < < < < < < < < < < < < < < < < < < < < | > > | | | | | < < | < | | < < < < < < < < < < | < < < < < < < < | < < < | < | < < < | | | | < | < < < < | < < < < | | 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 | if( *pRc==SQLITE_OK ){ int n = strlen(zStr); zRet = cidxMalloc(pRc, n+1); if( zRet ) memcpy(zRet, zStr, n+1); } return zRet; } static void cidxFreeIndex(CidxIndex *pIdx){ if( pIdx ){ int i; for(i=0; i<pIdx->nCol; i++){ sqlite3_free(pIdx->aCol[i].zExpr); } sqlite3_free(pIdx); } } static int cidxLookupIndex( CidxCursor *pCsr, /* Cursor object */ const char *zIdx, /* Name of index to look up */ CidxIndex **ppIdx, /* OUT: Description of columns */ char **pzTab /* OUT: Table name */ ){ int rc = SQLITE_OK; char *zTab = 0; CidxIndex *pIdx = 0; sqlite3_stmt *pFindTab = 0; sqlite3_stmt *pInfo = 0; /* Find the table for this index. */ pFindTab = cidxPrepare(&rc, pCsr, "SELECT tbl_name FROM sqlite_master WHERE name=%Q AND type='index'", zIdx ); if( rc==SQLITE_OK && sqlite3_step(pFindTab)==SQLITE_ROW ){ zTab = cidxStrdup(&rc, (const char*)sqlite3_column_text(pFindTab, 0)); } cidxFinalize(&rc, pFindTab); if( rc==SQLITE_OK && zTab==0 ){ rc = SQLITE_ERROR; } pInfo = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx); if( rc==SQLITE_OK ){ int nAlloc = 0; int iCol = 0; while( sqlite3_step(pInfo)==SQLITE_ROW ){ const char *zName = (const char*)sqlite3_column_text(pInfo, 2); const char *zColl = (const char*)sqlite3_column_text(pInfo, 4); CidxColumn *p; if( zName==0 ) zName = "rowid"; if( iCol==nAlloc ){ int nByte = sizeof(CidxIndex) + sizeof(CidxColumn)*(nAlloc+8); pIdx = (CidxIndex*)sqlite3_realloc(pIdx, nByte); } p = &pIdx->aCol[iCol++]; p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl); p->bDesc = sqlite3_column_int(pInfo, 3); p->bKey = sqlite3_column_int(pInfo, 5); pIdx->nCol = iCol; } cidxFinalize(&rc, pInfo); } if( rc!=SQLITE_OK ){ sqlite3_free(zTab); cidxFreeIndex(pIdx); }else{ *pzTab = zTab; *ppIdx = pIdx; } return rc; } static int cidxDecodeAfter( CidxCursor *pCsr, |
︙ | ︙ | |||
459 460 461 462 463 464 465 | int *pRc, CidxColumn *aCol, char **azAfter, int iGt, int bLastIsNull ){ char *zRet = 0; const char *zSep = ""; int i; for(i=0; i<iGt; i++){ | | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < < < < < < < < < < | 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 | int *pRc, CidxColumn *aCol, char **azAfter, int iGt, int bLastIsNull ){ char *zRet = 0; const char *zSep = ""; int i; for(i=0; i<iGt; i++){ zRet = cidxMprintf(pRc, "%z%s%s IS %s", zRet, zSep, aCol[i].zExpr, (azAfter[i] ? azAfter[i] : "NULL") ); zSep = " AND "; } if( bLastIsNull ){ zRet = cidxMprintf(pRc, "%z%s%s IS NULL", zRet, zSep, aCol[iGt].zExpr); } else if( azAfter[iGt] ){ zRet = cidxMprintf(pRc, "%z%s%s %s %s", zRet, zSep, aCol[iGt].zExpr, (aCol[iGt].bDesc ? "<" : ">"), azAfter[iGt] ); }else{ zRet = cidxMprintf(pRc, "%z%s%s IS NOT NULL", zRet, zSep, aCol[iGt].zExpr); } return zRet; } #define CIDX_CLIST_ALL 0 #define CIDX_CLIST_ORDERBY 1 #define CIDX_CLIST_CURRENT_KEY 2 #define CIDX_CLIST_SUBWHERE 3 #define CIDX_CLIST_SUBEXPR 4 /* ** This function returns various strings based on the contents of the ** CidxIndex structure and the eType parameter. */ static char *cidxColumnList( int *pRc, /* IN/OUT: Error code */ const char *zIdx, CidxIndex *pIdx, /* Indexed columns */ int eType /* True to include ASC/DESC */ ){ char *zRet = 0; if( *pRc==SQLITE_OK ){ const char *aDir[2] = {" ASC", " DESC"}; int i; const char *zSep = ""; for(i=0; i<pIdx->nCol; i++){ CidxColumn *p = &pIdx->aCol[i]; assert( pIdx->aCol[i].bDesc==0 || pIdx->aCol[i].bDesc==1 ); switch( eType ){ case CIDX_CLIST_ORDERBY: zRet = cidxMprintf(pRc, "%z%s%s%s",zRet,zSep,p->zExpr,aDir[p->bDesc]); zSep = ","; break; case CIDX_CLIST_CURRENT_KEY: zRet = cidxMprintf(pRc, "%z%squote(%s)", zRet, zSep, p->zExpr); zSep = "||','||"; break; case CIDX_CLIST_SUBWHERE: if( p->bKey==0 ){ zRet = cidxMprintf(pRc, "%z%s%s IS \"%w\".%s", zRet, zSep, p->zExpr, zIdx, p->zExpr ); zSep = " AND "; } break; case CIDX_CLIST_SUBEXPR: if( p->bKey==1 ){ zRet = cidxMprintf(pRc, "%z%s%s IS \"%w\".%s", zRet, zSep, p->zExpr, zIdx, p->zExpr ); zSep = " AND "; } break; default: assert( eType==CIDX_CLIST_ALL ); zRet = cidxMprintf(pRc, "%z%s%s", zRet, zSep, p->zExpr); zSep = ","; break; } } } return zRet; } /* ** Position a cursor back to the beginning. */ static int cidxFilter( |
︙ | ︙ | |||
512 513 514 515 516 517 518 | zIdxName = (const char*)sqlite3_value_text(argv[0]); if( argc>1 ){ zAfterKey = (const char*)sqlite3_value_text(argv[1]); } } if( zIdxName ){ | < > > | | | < > > > > > | | | | | | | | > | > | 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 | zIdxName = (const char*)sqlite3_value_text(argv[0]); if( argc>1 ){ zAfterKey = (const char*)sqlite3_value_text(argv[1]); } } if( zIdxName ){ char *zTab = 0; char *zCurrentKey = 0; char *zOrderBy = 0; char *zSubWhere = 0; char *zSubExpr = 0; char *zSrcList = 0; char **azAfter = 0; CidxIndex *pIdx = 0; rc = cidxLookupIndex(pCsr, zIdxName, &pIdx, &zTab); zOrderBy = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ORDERBY); zCurrentKey = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_CURRENT_KEY); zSubWhere = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBWHERE); zSubExpr = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBEXPR); /* zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL); */ if( rc==SQLITE_OK && zAfterKey ){ rc = cidxDecodeAfter(pCsr, pIdx->nCol, zAfterKey, &azAfter); } if( rc || zAfterKey==0 ){ pCsr->pStmt = cidxPrepare(&rc, pCsr, "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM %Q AS %Q ORDER BY %s", zSubExpr, zTab, zSubWhere, zCurrentKey, zTab, zIdxName, zOrderBy ); /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */ }else{ char *zList = cidxColumnList(&rc, zIdxName, pIdx, 0); const char *zSep = ""; char *zSql; int i; zSql = cidxMprintf(&rc, "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (", zSubExpr, zTab, zSubWhere, zCurrentKey ); for(i=pIdx->nCol-1; i>=0; i--){ int j; if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue; for(j=0; j<2; j++){ char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j); zSql = cidxMprintf(&rc, "%z%s SELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)", zSql, zSep, zList, zTab, zWhere, zOrderBy ); zSep = " UNION ALL "; if( pIdx->aCol[i].bDesc==0 ) break; } } zSql = cidxMprintf(&rc, "%z) AS %Q", zSql, zIdxName); sqlite3_free(zList); /* printf("SQL: %s\n", zSql); */ pCsr->pStmt = cidxPrepare(&rc, pCsr, "%z", zSql); } sqlite3_free(zTab); sqlite3_free(zCurrentKey); sqlite3_free(zOrderBy); sqlite3_free(zSubWhere); sqlite3_free(zSubExpr); cidxFreeIndex(pIdx); sqlite3_free(azAfter); } if( pCsr->pStmt ){ assert( rc==SQLITE_OK ); rc = cidxNext(pCursor); } return rc; } /* ** Return a column value. */ static int cidxColumn( sqlite3_vtab_cursor *pCursor, sqlite3_context *ctx, int iCol ){ CidxCursor *pCsr = (CidxCursor*)pCursor; assert( iCol==0 || iCol==1 ); |
︙ | ︙ |
Changes to test/checkindex.test.
︙ | ︙ | |||
63 64 65 66 67 68 69 | proc do_index_check_test {tn idx res} { uplevel [list do_execsql_test $tn.1 " SELECT errmsg, current_key FROM incremental_index_check('$idx'); " $res] uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]] | | | > | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | proc do_index_check_test {tn idx res} { uplevel [list do_execsql_test $tn.1 " SELECT errmsg, current_key FROM incremental_index_check('$idx'); " $res] uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]] uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]] uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]] } do_execsql_test 1.2 { SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1'); } { 1 'five',5 1 'four',4 1 'one',1 |
︙ | ︙ |