/ Check-in [bc14e64b]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Try to reuse sorter statements in fts5. Does not work due to circular references on VTable object.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | save_sorter_stmt
Files: files | file ages | folders
SHA1: bc14e64bdffb165fc5468339a7eaa5f6707537bf
User & Date: dan 2014-07-31 17:47:04
Context
2014-07-31
17:47
Try to reuse sorter statements in fts5. Does not work due to circular references on VTable object. Leaf check-in: bc14e64b user: dan tags: save_sorter_stmt
11:57
Add further tests for the extension APIs with "ORDER BY rank" queries. check-in: 37a417d2 user: dan tags: fts5
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/fts5/fts5.c.

70
71
72
73
74
75
76

77
78
79
80
81
82
83
...
372
373
374
375
376
377
378
379










380
381
382
383
384
385
386
387
388
...
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
506
507
508
509
510
**
** aIdx[]:
**   There is one entry in the aIdx[] array for each phrase in the query,
**   the value of which is the offset within aPoslist[] following the last 
**   byte of the position list for the corresponding phrase.
*/
struct Fts5Sorter {

  sqlite3_stmt *pStmt;
  i64 iRowid;                     /* Current rowid */
  const u8 *aPoslist;             /* Position lists for current row */
  int nIdx;                       /* Number of entries in aIdx[] */
  int aIdx[0];                    /* Offsets into aPoslist for current row */
};

................................................................................
  Fts5Auxdata *pNext;

  if( pCsr->pStmt ){
    int eStmt = fts5StmtType(pCsr->idxNum);
    sqlite3Fts5StorageStmtRelease(pTab->pStorage, eStmt, pCsr->pStmt);
  }
  if( pCsr->pSorter ){
    Fts5Sorter *pSorter = pCsr->pSorter;










    sqlite3_finalize(pSorter->pStmt);
    sqlite3_free(pSorter);
  }
  
  if( pCsr->idxNum!=FTS5_PLAN_SOURCE ){
    sqlite3Fts5ExprFree(pCsr->pExpr);
  }

  for(pData=pCsr->pAuxdata; pData; pData=pNext){
................................................................................
}

static int fts5CursorFirstSorted(Fts5Table *pTab, Fts5Cursor *pCsr, int bAsc){
  Fts5Config *pConfig = pTab->pConfig;
  Fts5Sorter *pSorter;
  int nPhrase;
  int nByte;

  int rc = SQLITE_OK;
  char *zSql;
  
  nPhrase = sqlite3Fts5ExprPhraseCount(pCsr->pExpr);
  nByte = sizeof(Fts5Sorter) + sizeof(int) * nPhrase;
  pSorter = (Fts5Sorter*)sqlite3_malloc(nByte);
  if( pSorter==0 ) return SQLITE_NOMEM;
  memset(pSorter, 0, nByte);
  pSorter->nIdx = nPhrase;

  zSql = sqlite3_mprintf("SELECT rowid, %s FROM %Q.%Q ORDER BY +%s %s",
      pConfig->zName, pConfig->zDb, pConfig->zName, FTS5_RANK_NAME,
      bAsc ? "ASC" : "DESC"
  );
  if( zSql==0 ){
    rc = SQLITE_NOMEM;
  }else{
    rc = sqlite3_prepare_v2(pConfig->db, zSql, -1, &pSorter->pStmt, 0);
    sqlite3_free(zSql);
  }



  pCsr->pSorter = pSorter;
  if( rc==SQLITE_OK ){
    assert( pTab->pSortCsr==0 );
    pTab->pSortCsr = pCsr;
    rc = fts5SorterNext(pCsr);
    pTab->pSortCsr = 0;







>







 







|
>
>
>
>
>
>
>
>
>
>
|
|







 







>










|
|
<
<
<
<
<
<
<
|
>
>







70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
...
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
...
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507







508
509
510
511
512
513
514
515
516
517
**
** aIdx[]:
**   There is one entry in the aIdx[] array for each phrase in the query,
**   the value of which is the offset within aPoslist[] following the last 
**   byte of the position list for the corresponding phrase.
*/
struct Fts5Sorter {
  int eStmt;
  sqlite3_stmt *pStmt;
  i64 iRowid;                     /* Current rowid */
  const u8 *aPoslist;             /* Position lists for current row */
  int nIdx;                       /* Number of entries in aIdx[] */
  int aIdx[0];                    /* Offsets into aPoslist for current row */
};

................................................................................
  Fts5Auxdata *pNext;

  if( pCsr->pStmt ){
    int eStmt = fts5StmtType(pCsr->idxNum);
    sqlite3Fts5StorageStmtRelease(pTab->pStorage, eStmt, pCsr->pStmt);
  }
  if( pCsr->pSorter ){
    Fts5Sorter *p = pCsr->pSorter;

    /* TODO: It would be better here to use sqlite3Fts5StorageStmtRelease() 
    ** so that the statement may be reused by subsequent queries. But that 
    ** is not possible as SQLite reference counts the virtual table objects.
    ** And since pStmt reads from this very virtual table, saving it here
    ** creates a circular reference.
    **
    ** We wouldn't worry so much if SQLite had a built-in statement cache.
    */
    /* sqlite3Fts5StorageStmtRelease(pTab->pStorage, p->eStmt, p->pStmt); */
    sqlite3_finalize(p->pStmt);
    sqlite3_free(p);
  }
  
  if( pCsr->idxNum!=FTS5_PLAN_SOURCE ){
    sqlite3Fts5ExprFree(pCsr->pExpr);
  }

  for(pData=pCsr->pAuxdata; pData; pData=pNext){
................................................................................
}

static int fts5CursorFirstSorted(Fts5Table *pTab, Fts5Cursor *pCsr, int bAsc){
  Fts5Config *pConfig = pTab->pConfig;
  Fts5Sorter *pSorter;
  int nPhrase;
  int nByte;
  int eStmt;
  int rc = SQLITE_OK;
  char *zSql;
  
  nPhrase = sqlite3Fts5ExprPhraseCount(pCsr->pExpr);
  nByte = sizeof(Fts5Sorter) + sizeof(int) * nPhrase;
  pSorter = (Fts5Sorter*)sqlite3_malloc(nByte);
  if( pSorter==0 ) return SQLITE_NOMEM;
  memset(pSorter, 0, nByte);
  pSorter->nIdx = nPhrase;

  assert( FTS5_STMT_SORTER_ASC==1+FTS5_STMT_SORTER_DESC );
  assert( bAsc==0 || bAsc==1 );








  pSorter->eStmt = FTS5_STMT_SORTER_DESC+bAsc;
  rc = sqlite3Fts5StorageStmt(pTab->pStorage, pSorter->eStmt, &pSorter->pStmt);

  pCsr->pSorter = pSorter;
  if( rc==SQLITE_OK ){
    assert( pTab->pSortCsr==0 );
    pTab->pSortCsr = pCsr;
    rc = fts5SorterNext(pCsr);
    pTab->pSortCsr = 0;

Changes to ext/fts5/fts5Int.h.

278
279
280
281
282
283
284
285
286
287


288
289
290
291
292
293
294
**************************************************************************/

/**************************************************************************
** Interface to code in fts5_storage.c. fts5_storage.c contains contains 
** code to access the data stored in the %_content and %_docsize tables.
*/

#define FTS5_STMT_SCAN_ASC  0     /* SELECT rowid, * FROM ... ORDER BY 1 ASC */
#define FTS5_STMT_SCAN_DESC 1     /* SELECT rowid, * FROM ... ORDER BY 1 DESC */
#define FTS5_STMT_LOOKUP    2     /* SELECT rowid, * FROM ... WHERE rowid=? */



typedef struct Fts5Storage Fts5Storage;

int sqlite3Fts5StorageOpen(Fts5Config*, Fts5Index*, int, Fts5Storage**, char**);
int sqlite3Fts5StorageClose(Fts5Storage *p, int bDestroy);

int sqlite3Fts5DropTable(Fts5Config*, const char *zPost);







|
|
|
>
>







278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
**************************************************************************/

/**************************************************************************
** Interface to code in fts5_storage.c. fts5_storage.c contains contains 
** code to access the data stored in the %_content and %_docsize tables.
*/

#define FTS5_STMT_SCAN_ASC   0    /* SELECT rowid, * FROM ... ORDER BY 1 ASC */
#define FTS5_STMT_SCAN_DESC  1    /* SELECT rowid, * FROM ... ORDER BY 1 DESC */
#define FTS5_STMT_LOOKUP     2    /* SELECT rowid, * FROM ... WHERE rowid=? */
#define FTS5_STMT_SORTER_DESC 3   /* SELECT ... ORDER BY rank ASC */
#define FTS5_STMT_SORTER_ASC  4   /* SELECT ... ORDER BY rank ASC */

typedef struct Fts5Storage Fts5Storage;

int sqlite3Fts5StorageOpen(Fts5Config*, Fts5Index*, int, Fts5Storage**, char**);
int sqlite3Fts5StorageClose(Fts5Storage *p, int bDestroy);

int sqlite3Fts5DropTable(Fts5Config*, const char *zPost);

Changes to ext/fts5/fts5_storage.c.

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
..
58
59
60
61
62
63
64




65
66
67
68
69
70
71
..
84
85
86
87
88
89
90




91
92
93
94
95
96
97
...
627
628
629
630
631
632
633


634
635
636
637
638
639
640
...
649
650
651
652
653
654
655


656
657
658
659
660
661
662
#include "fts5Int.h"

struct Fts5Storage {
  Fts5Config *pConfig;
  Fts5Index *pIndex;
  i64 nTotalRow;                  /* Total number of rows in FTS table */
  i64 *aTotalSize;                /* Total sizes of each column */ 
  sqlite3_stmt *aStmt[9];
};


#if FTS5_STMT_SCAN_ASC!=0 
# error "FTS5_STMT_SCAN_ASC mismatch" 
#endif
#if FTS5_STMT_SCAN_DESC!=1 
# error "FTS5_STMT_SCAN_DESC mismatch" 
#endif
#if FTS5_STMT_LOOKUP!=2
# error "FTS5_STMT_LOOKUP mismatch" 
#endif







#define FTS5_STMT_INSERT_CONTENT  3
#define FTS5_STMT_REPLACE_CONTENT 4

#define FTS5_STMT_DELETE_CONTENT  5
#define FTS5_STMT_REPLACE_DOCSIZE  6
#define FTS5_STMT_DELETE_DOCSIZE  7

#define FTS5_STMT_LOOKUP_DOCSIZE  8

/*
** Prepare the two insert statements - Fts5Storage.pInsertContent and
** Fts5Storage.pInsertDocsize - if they have not already been prepared.
** Return SQLITE_OK if successful, or an SQLite error code if an error
** occurs.
*/
................................................................................
  assert( eStmt>=0 && eStmt<ArraySize(p->aStmt) );
  if( p->aStmt[eStmt]==0 ){
    const char *azStmt[] = {
      "SELECT * FROM %Q.'%q_content' ORDER BY id ASC",  /* SCAN_ASC */
      "SELECT * FROM %Q.'%q_content' ORDER BY id DESC", /* SCAN_DESC */
      "SELECT * FROM %Q.'%q_content' WHERE rowid=?",    /* LOOKUP  */





      "INSERT INTO %Q.'%q_content' VALUES(%s)",         /* INSERT_CONTENT  */
      "REPLACE INTO %Q.'%q_content' VALUES(%s)",        /* REPLACE_CONTENT */
      "DELETE FROM %Q.'%q_content' WHERE id=?",         /* DELETE_CONTENT  */
      "REPLACE INTO %Q.'%q_docsize' VALUES(?,?)",       /* REPLACE_DOCSIZE  */
      "DELETE FROM %Q.'%q_docsize' WHERE id=?",         /* DELETE_DOCSIZE  */

      "SELECT sz FROM %Q.'%q_docsize' WHERE id=?",      /* LOOKUP_DOCSIZE  */
................................................................................
          zBind[i*2] = '?';
          zBind[i*2 + 1] = ',';
        }
        zBind[i*2-1] = '\0';
        zSql = sqlite3_mprintf(azStmt[eStmt],pConfig->zDb,pConfig->zName,zBind);
        sqlite3_free(zBind);
      }




    }else{
      zSql = sqlite3_mprintf(azStmt[eStmt], pConfig->zDb, pConfig->zName);
    }

    if( zSql==0 ){
      rc = SQLITE_NOMEM;
    }else{
................................................................................
** %_content table.
*/
int sqlite3Fts5StorageStmt(Fts5Storage *p, int eStmt, sqlite3_stmt **pp){
  int rc;
  assert( eStmt==FTS5_STMT_SCAN_ASC 
       || eStmt==FTS5_STMT_SCAN_DESC
       || eStmt==FTS5_STMT_LOOKUP


  );
  rc = fts5StorageGetStmt(p, eStmt, pp);
  if( rc==SQLITE_OK ){
    assert( p->aStmt[eStmt]==*pp );
    p->aStmt[eStmt] = 0;
  }
  return rc;
................................................................................
  Fts5Storage *p, 
  int eStmt, 
  sqlite3_stmt *pStmt
){
  assert( eStmt==FTS5_STMT_SCAN_ASC
       || eStmt==FTS5_STMT_SCAN_DESC
       || eStmt==FTS5_STMT_LOOKUP


  );
  if( p->aStmt[eStmt]==0 ){
    sqlite3_reset(pStmt);
    p->aStmt[eStmt] = pStmt;
  }else{
    sqlite3_finalize(pStmt);
  }







|












>
>
>
>
>
>

|
|

|
|
|
<
|







 







>
>
>
>







 







>
>
>
>







 







>
>







 







>
>







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
..
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
..
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
...
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
...
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
#include "fts5Int.h"

struct Fts5Storage {
  Fts5Config *pConfig;
  Fts5Index *pIndex;
  i64 nTotalRow;                  /* Total number of rows in FTS table */
  i64 *aTotalSize;                /* Total sizes of each column */ 
  sqlite3_stmt *aStmt[11];
};


#if FTS5_STMT_SCAN_ASC!=0 
# error "FTS5_STMT_SCAN_ASC mismatch" 
#endif
#if FTS5_STMT_SCAN_DESC!=1 
# error "FTS5_STMT_SCAN_DESC mismatch" 
#endif
#if FTS5_STMT_LOOKUP!=2
# error "FTS5_STMT_LOOKUP mismatch" 
#endif
#if FTS5_STMT_SORTER_DESC!=3
# error "FTS5_STMT_SORTER_DESC mismatch" 
#endif
#if FTS5_STMT_SORTER_ASC!=4
# error "FTS5_STMT_SORTER_ASC mismatch" 
#endif

#define FTS5_STMT_INSERT_CONTENT  5
#define FTS5_STMT_REPLACE_CONTENT 6

#define FTS5_STMT_DELETE_CONTENT  7
#define FTS5_STMT_REPLACE_DOCSIZE 8
#define FTS5_STMT_DELETE_DOCSIZE  9

#define FTS5_STMT_LOOKUP_DOCSIZE  10

/*
** Prepare the two insert statements - Fts5Storage.pInsertContent and
** Fts5Storage.pInsertDocsize - if they have not already been prepared.
** Return SQLITE_OK if successful, or an SQLite error code if an error
** occurs.
*/
................................................................................
  assert( eStmt>=0 && eStmt<ArraySize(p->aStmt) );
  if( p->aStmt[eStmt]==0 ){
    const char *azStmt[] = {
      "SELECT * FROM %Q.'%q_content' ORDER BY id ASC",  /* SCAN_ASC */
      "SELECT * FROM %Q.'%q_content' ORDER BY id DESC", /* SCAN_DESC */
      "SELECT * FROM %Q.'%q_content' WHERE rowid=?",    /* LOOKUP  */

      /* SORTER_DESC and SORTER_ASC: */
      "SELECT rowid, \"%s\" FROM %Q.%Q ORDER BY +" FTS5_RANK_NAME " DESC",
      "SELECT rowid, \"%s\" FROM %Q.%Q ORDER BY +" FTS5_RANK_NAME " ASC",

      "INSERT INTO %Q.'%q_content' VALUES(%s)",         /* INSERT_CONTENT  */
      "REPLACE INTO %Q.'%q_content' VALUES(%s)",        /* REPLACE_CONTENT */
      "DELETE FROM %Q.'%q_content' WHERE id=?",         /* DELETE_CONTENT  */
      "REPLACE INTO %Q.'%q_docsize' VALUES(?,?)",       /* REPLACE_DOCSIZE  */
      "DELETE FROM %Q.'%q_docsize' WHERE id=?",         /* DELETE_DOCSIZE  */

      "SELECT sz FROM %Q.'%q_docsize' WHERE id=?",      /* LOOKUP_DOCSIZE  */
................................................................................
          zBind[i*2] = '?';
          zBind[i*2 + 1] = ',';
        }
        zBind[i*2-1] = '\0';
        zSql = sqlite3_mprintf(azStmt[eStmt],pConfig->zDb,pConfig->zName,zBind);
        sqlite3_free(zBind);
      }
    }else if( eStmt==FTS5_STMT_SORTER_ASC || eStmt==FTS5_STMT_SORTER_DESC ){
      zSql = sqlite3_mprintf(azStmt[eStmt], 
          pConfig->zName, pConfig->zDb, pConfig->zName
      );
    }else{
      zSql = sqlite3_mprintf(azStmt[eStmt], pConfig->zDb, pConfig->zName);
    }

    if( zSql==0 ){
      rc = SQLITE_NOMEM;
    }else{
................................................................................
** %_content table.
*/
int sqlite3Fts5StorageStmt(Fts5Storage *p, int eStmt, sqlite3_stmt **pp){
  int rc;
  assert( eStmt==FTS5_STMT_SCAN_ASC 
       || eStmt==FTS5_STMT_SCAN_DESC
       || eStmt==FTS5_STMT_LOOKUP
       || eStmt==FTS5_STMT_SORTER_DESC
       || eStmt==FTS5_STMT_SORTER_ASC
  );
  rc = fts5StorageGetStmt(p, eStmt, pp);
  if( rc==SQLITE_OK ){
    assert( p->aStmt[eStmt]==*pp );
    p->aStmt[eStmt] = 0;
  }
  return rc;
................................................................................
  Fts5Storage *p, 
  int eStmt, 
  sqlite3_stmt *pStmt
){
  assert( eStmt==FTS5_STMT_SCAN_ASC
       || eStmt==FTS5_STMT_SCAN_DESC
       || eStmt==FTS5_STMT_LOOKUP
       || eStmt==FTS5_STMT_SORTER_DESC
       || eStmt==FTS5_STMT_SORTER_ASC
  );
  if( p->aStmt[eStmt]==0 ){
    sqlite3_reset(pStmt);
    p->aStmt[eStmt] = pStmt;
  }else{
    sqlite3_finalize(pStmt);
  }