Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add support for indexes on expressions to incremental_index_check. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | checkindex |
| Files: | files | file ages | folders |
| SHA3-256: |
8c1c701fdbe0d56ee7f6f7d7b583aafd |
| User & Date: | dan 2017-10-30 19:38:41 |
Context
|
2017-10-31
| ||
| 12:01 | Fix a couple of issues in incremental_index_check to do with CREATE INDEX statements that contain embedded SQL comments. (check-in: 2aef4181 user: dan tags: checkindex) | |
|
2017-10-30
| ||
| 19:38 | Add support for indexes on expressions to incremental_index_check. (check-in: 8c1c701f 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: 940606b3 user: dan tags: checkindex) | |
Changes
Changes to ext/repair/checkindex.c.
269 269 int i; 270 270 for(i=0; i<pIdx->nCol; i++){ 271 271 sqlite3_free(pIdx->aCol[i].zExpr); 272 272 } 273 273 sqlite3_free(pIdx); 274 274 } 275 275 } 276 + 277 +#define CIDX_PARSE_EOF 0 278 +#define CIDX_PARSE_COMMA 1 /* "," */ 279 +#define CIDX_PARSE_OPEN 2 /* "(" */ 280 +#define CIDX_PARSE_CLOSE 3 /* ")" */ 281 + 282 +static int cidxFindNext(const char *zIn, const char **pzOut){ 283 + const char *z = zIn; 284 + 285 + while( 1 ){ 286 + *pzOut = z; 287 + switch( *z ){ 288 + case '\0': 289 + return CIDX_PARSE_EOF; 290 + case '(': 291 + return CIDX_PARSE_OPEN; 292 + case ')': 293 + return CIDX_PARSE_CLOSE; 294 + case ',': 295 + return CIDX_PARSE_COMMA; 296 + 297 + case '"': 298 + case '\'': 299 + case '`': { 300 + char q = *z; 301 + z++; 302 + while( *z ){ 303 + if( *z==q ){ 304 + z++; 305 + if( *z!=q ) break; 306 + } 307 + z++; 308 + } 309 + break; 310 + } 311 + 312 + case '[': 313 + while( *z++!=']' ); 314 + break; 315 + 316 + default: 317 + z++; 318 + } 319 + } 320 + 321 + assert( 0 ); 322 + return -1; 323 +} 324 + 325 +static int cidx_isspace(char c){ 326 + return c==' ' || c=='\t' || c=='\r' || c=='\n'; 327 +} 328 + 329 +static int cidx_isident(char c){ 330 + return c<0 331 + || (c>='0' && c<='9') || (c>='a' && c<='z') 332 + || (c>='A' && c<='Z') || c=='_'; 333 +} 334 + 335 +static int cidxParseSQL(CidxCursor *pCsr, CidxIndex *pIdx, const char *zSql){ 336 + const char *z = zSql; 337 + const char *z1; 338 + int e; 339 + int rc = SQLITE_OK; 340 + int nParen = 1; 341 + CidxColumn *pCol = pIdx->aCol; 342 + 343 + e = cidxFindNext(z, &z); 344 + if( e!=CIDX_PARSE_OPEN ) goto parse_error; 345 + z1 = z+1; 346 + z++; 347 + while( nParen>0 ){ 348 + e = cidxFindNext(z, &z); 349 + if( e==CIDX_PARSE_EOF ) goto parse_error; 350 + if( (e==CIDX_PARSE_COMMA || e==CIDX_PARSE_CLOSE) && nParen==1 ){ 351 + const char *z2 = z; 352 + if( pCol->zExpr ) goto parse_error; 353 + 354 + while( cidx_isspace(z[-1]) ) z--; 355 + if( 0==sqlite3_strnicmp(&z[-3], "asc", 3) && 0==cidx_isident(z[-4]) ){ 356 + z -= 3; 357 + while( cidx_isspace(z[-1]) ) z--; 358 + }else 359 + if( 0==sqlite3_strnicmp(&z[-4], "desc", 4) && 0==cidx_isident(z[-5]) ){ 360 + z -= 4; 361 + while( cidx_isspace(z[-1]) ) z--; 362 + } 363 + 364 + while( cidx_isspace(z1[0]) ) z1++; 365 + pCol->zExpr = cidxMprintf(&rc, "%.*s", z-z1, z1); 366 + pCol++; 367 + z = z1 = z2+1; 368 + } 369 + if( e==CIDX_PARSE_OPEN ) nParen++; 370 + if( e==CIDX_PARSE_CLOSE ) nParen--; 371 + z++; 372 + } 373 + 374 + return rc; 375 + 376 + parse_error: 377 + cidxCursorError(pCsr, "Parse error in: %s", zSql); 378 + return SQLITE_ERROR; 379 +} 276 380 277 381 static int cidxLookupIndex( 278 382 CidxCursor *pCsr, /* Cursor object */ 279 383 const char *zIdx, /* Name of index to look up */ 280 384 CidxIndex **ppIdx, /* OUT: Description of columns */ 281 385 char **pzTab /* OUT: Table name */ 282 386 ){ ................................................................................ 285 389 CidxIndex *pIdx = 0; 286 390 287 391 sqlite3_stmt *pFindTab = 0; 288 392 sqlite3_stmt *pInfo = 0; 289 393 290 394 /* Find the table for this index. */ 291 395 pFindTab = cidxPrepare(&rc, pCsr, 292 - "SELECT tbl_name FROM sqlite_master WHERE name=%Q AND type='index'", 396 + "SELECT tbl_name, sql FROM sqlite_master WHERE name=%Q AND type='index'", 293 397 zIdx 294 398 ); 295 399 if( rc==SQLITE_OK && sqlite3_step(pFindTab)==SQLITE_ROW ){ 400 + const char *zSql = (const char*)sqlite3_column_text(pFindTab, 1); 296 401 zTab = cidxStrdup(&rc, (const char*)sqlite3_column_text(pFindTab, 0)); 402 + 403 + pInfo = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx); 404 + if( rc==SQLITE_OK ){ 405 + int nAlloc = 0; 406 + int iCol = 0; 407 + 408 + while( sqlite3_step(pInfo)==SQLITE_ROW ){ 409 + const char *zName = (const char*)sqlite3_column_text(pInfo, 2); 410 + const char *zColl = (const char*)sqlite3_column_text(pInfo, 4); 411 + CidxColumn *p; 412 + if( zName==0 ) zName = "rowid"; 413 + if( iCol==nAlloc ){ 414 + int nByte = sizeof(CidxIndex) + sizeof(CidxColumn)*(nAlloc+8); 415 + pIdx = (CidxIndex*)sqlite3_realloc(pIdx, nByte); 416 + nAlloc += 8; 417 + } 418 + p = &pIdx->aCol[iCol++]; 419 + p->bDesc = sqlite3_column_int(pInfo, 3); 420 + p->bKey = sqlite3_column_int(pInfo, 5); 421 + if( zSql==0 || p->bKey==0 ){ 422 + p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl); 423 + }else{ 424 + p->zExpr = 0; 425 + } 426 + pIdx->nCol = iCol; 427 + } 428 + cidxFinalize(&rc, pInfo); 429 + } 430 + 431 + if( rc==SQLITE_OK && zSql ){ 432 + rc = cidxParseSQL(pCsr, pIdx, zSql); 433 + } 297 434 } 435 + 298 436 cidxFinalize(&rc, pFindTab); 299 437 if( rc==SQLITE_OK && zTab==0 ){ 300 438 rc = SQLITE_ERROR; 301 439 } 302 - 303 - pInfo = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx); 304 - if( rc==SQLITE_OK ){ 305 - int nAlloc = 0; 306 - int iCol = 0; 307 - 308 - while( sqlite3_step(pInfo)==SQLITE_ROW ){ 309 - const char *zName = (const char*)sqlite3_column_text(pInfo, 2); 310 - const char *zColl = (const char*)sqlite3_column_text(pInfo, 4); 311 - CidxColumn *p; 312 - if( zName==0 ) zName = "rowid"; 313 - if( iCol==nAlloc ){ 314 - int nByte = sizeof(CidxIndex) + sizeof(CidxColumn)*(nAlloc+8); 315 - pIdx = (CidxIndex*)sqlite3_realloc(pIdx, nByte); 316 - } 317 - p = &pIdx->aCol[iCol++]; 318 - p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl); 319 - p->bDesc = sqlite3_column_int(pInfo, 3); 320 - p->bKey = sqlite3_column_int(pInfo, 5); 321 - pIdx->nCol = iCol; 322 - } 323 - cidxFinalize(&rc, pInfo); 324 - } 325 440 326 441 if( rc!=SQLITE_OK ){ 327 442 sqlite3_free(zTab); 328 443 cidxFreeIndex(pIdx); 329 444 }else{ 330 445 *pzTab = zTab; 331 446 *ppIdx = pIdx; ................................................................................ 407 522 int *pRc, CidxColumn *aCol, char **azAfter, int iGt, int bLastIsNull 408 523 ){ 409 524 char *zRet = 0; 410 525 const char *zSep = ""; 411 526 int i; 412 527 413 528 for(i=0; i<iGt; i++){ 414 - zRet = cidxMprintf(pRc, "%z%s%s IS %s", zRet, 529 + zRet = cidxMprintf(pRc, "%z%s(%s) IS %s", zRet, 415 530 zSep, aCol[i].zExpr, (azAfter[i] ? azAfter[i] : "NULL") 416 531 ); 417 532 zSep = " AND "; 418 533 } 419 534 420 535 if( bLastIsNull ){ 421 - zRet = cidxMprintf(pRc, "%z%s%s IS NULL", zRet, zSep, aCol[iGt].zExpr); 536 + zRet = cidxMprintf(pRc, "%z%s(%s) IS NULL", zRet, zSep, aCol[iGt].zExpr); 422 537 } 423 538 else if( azAfter[iGt] ){ 424 - zRet = cidxMprintf(pRc, "%z%s%s %s %s", zRet, 539 + zRet = cidxMprintf(pRc, "%z%s(%s) %s %s", zRet, 425 540 zSep, aCol[iGt].zExpr, (aCol[iGt].bDesc ? "<" : ">"), 426 541 azAfter[iGt] 427 542 ); 428 543 }else{ 429 - zRet = cidxMprintf(pRc, "%z%s%s IS NOT NULL", zRet, zSep, aCol[iGt].zExpr); 544 + zRet = cidxMprintf(pRc, "%z%s(%s) IS NOT NULL", zRet, zSep,aCol[iGt].zExpr); 430 545 } 431 546 432 547 return zRet; 433 548 } 434 549 435 550 #define CIDX_CLIST_ALL 0 436 551 #define CIDX_CLIST_ORDERBY 1 ................................................................................ 446 561 int *pRc, /* IN/OUT: Error code */ 447 562 const char *zIdx, 448 563 CidxIndex *pIdx, /* Indexed columns */ 449 564 int eType /* True to include ASC/DESC */ 450 565 ){ 451 566 char *zRet = 0; 452 567 if( *pRc==SQLITE_OK ){ 453 - const char *aDir[2] = {" ASC", " DESC"}; 568 + const char *aDir[2] = {"", " DESC"}; 454 569 int i; 455 570 const char *zSep = ""; 456 571 457 572 for(i=0; i<pIdx->nCol; i++){ 458 573 CidxColumn *p = &pIdx->aCol[i]; 459 574 assert( pIdx->aCol[i].bDesc==0 || pIdx->aCol[i].bDesc==1 ); 460 575 switch( eType ){ 461 576 462 577 case CIDX_CLIST_ORDERBY: 463 - zRet = cidxMprintf(pRc, "%z%s%s%s",zRet,zSep,p->zExpr,aDir[p->bDesc]); 578 + zRet = cidxMprintf(pRc, "%z%s%d%s", zRet, zSep, i+1, aDir[p->bDesc]); 464 579 zSep = ","; 465 580 break; 466 581 467 582 case CIDX_CLIST_CURRENT_KEY: 468 - zRet = cidxMprintf(pRc, "%z%squote(%s)", zRet, zSep, p->zExpr); 583 + zRet = cidxMprintf(pRc, "%z%squote(i%d)", zRet, zSep, i); 469 584 zSep = "||','||"; 470 585 break; 471 586 472 587 case CIDX_CLIST_SUBWHERE: 473 588 if( p->bKey==0 ){ 474 - zRet = cidxMprintf(pRc, "%z%s%s IS \"%w\".%s", zRet, 475 - zSep, p->zExpr, zIdx, p->zExpr 589 + zRet = cidxMprintf(pRc, "%z%s%s IS i.i%d", zRet, 590 + zSep, p->zExpr, i 476 591 ); 477 592 zSep = " AND "; 478 593 } 479 594 break; 480 595 481 596 case CIDX_CLIST_SUBEXPR: 482 597 if( p->bKey==1 ){ 483 - zRet = cidxMprintf(pRc, "%z%s%s IS \"%w\".%s", zRet, 484 - zSep, p->zExpr, zIdx, p->zExpr 598 + zRet = cidxMprintf(pRc, "%z%s%s IS i.i%d", zRet, 599 + zSep, p->zExpr, i 485 600 ); 486 601 zSep = " AND "; 487 602 } 488 603 break; 489 604 490 605 default: 491 606 assert( eType==CIDX_CLIST_ALL ); 492 - zRet = cidxMprintf(pRc, "%z%s%s", zRet, zSep, p->zExpr); 493 - zSep = ","; 607 + zRet = cidxMprintf(pRc, "%z%s(%s) AS i%d", zRet, zSep, p->zExpr, i); 608 + zSep = ", "; 494 609 break; 495 610 } 496 611 } 497 612 } 498 613 499 614 return zRet; 500 615 } ................................................................................ 532 647 533 648 rc = cidxLookupIndex(pCsr, zIdxName, &pIdx, &zTab); 534 649 535 650 zOrderBy = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ORDERBY); 536 651 zCurrentKey = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_CURRENT_KEY); 537 652 zSubWhere = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBWHERE); 538 653 zSubExpr = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBEXPR); 539 - /* zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL); */ 654 + zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL); 540 655 541 656 if( rc==SQLITE_OK && zAfterKey ){ 542 657 rc = cidxDecodeAfter(pCsr, pIdx->nCol, zAfterKey, &azAfter); 543 658 } 544 659 545 660 if( rc || zAfterKey==0 ){ 546 661 pCsr->pStmt = cidxPrepare(&rc, pCsr, 547 - "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM %Q AS %Q ORDER BY %s", 548 - zSubExpr, zTab, zSubWhere, zCurrentKey, zTab, zIdxName, zOrderBy 662 + "SELECT (SELECT %s FROM %Q AS t WHERE %s), %s " 663 + "FROM (SELECT %s FROM %Q ORDER BY %s) AS i", 664 + zSubExpr, zTab, zSubWhere, zCurrentKey, 665 + zSrcList, zTab, zOrderBy 549 666 ); 550 - /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */ 667 + /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */ 551 668 }else{ 552 - char *zList = cidxColumnList(&rc, zIdxName, pIdx, 0); 553 669 const char *zSep = ""; 554 670 char *zSql; 555 671 int i; 556 672 557 - zSql = cidxMprintf(&rc, "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (", 673 + zSql = cidxMprintf(&rc, 674 + "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (", 558 675 zSubExpr, zTab, zSubWhere, zCurrentKey 559 676 ); 560 677 for(i=pIdx->nCol-1; i>=0; i--){ 561 678 int j; 562 679 if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue; 563 680 for(j=0; j<2; j++){ 564 681 char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j); 565 - zSql = cidxMprintf(&rc, 566 - "%z%s SELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)", 567 - zSql, zSep, zList, zTab, zWhere, zOrderBy 568 - ); 682 + zSql = cidxMprintf(&rc, "%z" 683 + "%sSELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)", 684 + zSql, zSep, zSrcList, zTab, zWhere, zOrderBy 685 + ); 569 686 zSep = " UNION ALL "; 570 687 if( pIdx->aCol[i].bDesc==0 ) break; 571 688 } 572 689 } 573 - zSql = cidxMprintf(&rc, "%z) AS %Q", zSql, zIdxName); 574 - sqlite3_free(zList); 690 + zSql = cidxMprintf(&rc, "%z) AS i", zSql); 575 691 576 692 /* printf("SQL: %s\n", zSql); */ 577 693 pCsr->pStmt = cidxPrepare(&rc, pCsr, "%z", zSql); 578 694 } 579 695 580 696 sqlite3_free(zTab); 581 697 sqlite3_free(zCurrentKey); 582 698 sqlite3_free(zOrderBy); 583 699 sqlite3_free(zSubWhere); 584 700 sqlite3_free(zSubExpr); 701 + sqlite3_free(zSrcList); 585 702 cidxFreeIndex(pIdx); 586 703 sqlite3_free(azAfter); 587 704 } 588 705 589 706 if( pCsr->pStmt ){ 590 707 assert( rc==SQLITE_OK ); 591 708 rc = cidxNext(pCursor);
Changes to test/checkindex.test.
255 255 do_index_check_test 4.3 t4cc { 256 256 {} 'aaa','bbb',1 257 257 {row data mismatch} 'AAA','CCC',2 258 258 {row missing} 'aab','ddd',3 259 259 {} 'AAB','EEE',4 260 260 } 261 261 262 +#-------------------------------------------------------------------------- 263 +# Test an index on an expression. 264 +# 265 +do_execsql_test 5.0 { 266 + CREATE TABLE t5(x INTEGER PRIMARY KEY, y TEXT, UNIQUE(y)); 267 + INSERT INTO t5 VALUES(1, '{"x":1, "y":1}'); 268 + INSERT INTO t5 VALUES(2, '{"x":2, "y":2}'); 269 + INSERT INTO t5 VALUES(3, '{"x":3, "y":3}'); 270 + INSERT INTO t5 VALUES(4, '{"w":4, "z":4}'); 271 + INSERT INTO t5 VALUES(5, '{"x":5, "y":5}'); 272 + 273 + CREATE INDEX t5x ON t5( json_extract(y, '$.x') ); 274 + CREATE INDEX t5y ON t5( json_extract(y, '$.y') DESC ); 275 +} 276 + 277 +do_index_check_test 5.1.1 t5x { 278 + {} NULL,4 {} 1,1 {} 2,2 {} 3,3 {} 5,5 279 +} 280 + 281 +do_index_check_test 5.1.2 t5y { 282 + {} 5,5 {} 3,3 {} 2,2 {} 1,1 {} NULL,4 283 +} 284 + 285 +do_index_check_test 5.1.3 sqlite_autoindex_t5_1 { 286 + {} {'{"w":4, "z":4}',4} 287 + {} {'{"x":1, "y":1}',1} 288 + {} {'{"x":2, "y":2}',2} 289 + {} {'{"x":3, "y":3}',3} 290 + {} {'{"x":5, "y":5}',5} 291 +} 292 + 293 +do_test 5.2 { 294 + set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t5' }] 295 + sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot 296 + db eval {CREATE TABLE xt5(a INTEGER PRIMARY KEY, c1 TEXT);} 297 + sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0 298 + execsql { 299 + UPDATE xt5 SET c1='{"x":22, "y":11}' WHERE rowid=1; 300 + DELETE FROM xt5 WHERE rowid = 4; 301 + } 302 + sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1 303 +} {} 304 + 305 +do_index_check_test 5.3.1 t5x { 306 + {row missing} NULL,4 307 + {row data mismatch} 1,1 308 + {} 2,2 309 + {} 3,3 310 + {} 5,5 311 +} 312 + 313 +do_index_check_test 5.3.2 sqlite_autoindex_t5_1 { 314 + {row missing} {'{"w":4, "z":4}',4} 315 + {row data mismatch} {'{"x":1, "y":1}',1} 316 + {} {'{"x":2, "y":2}',2} 317 + {} {'{"x":3, "y":3}',3} 318 + {} {'{"x":5, "y":5}',5} 319 +} 262 320 263 321 264 322 finish_test 265 323