SQLite

Check-in [a21fefb79a]
Login

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

Overview
Comment:Fix some problems surrounding WITHOUT ROWID tables with DESC primary key indexes .
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: a21fefb79a161e6bb057ae4f6ba554f659706ac1
User & Date: dan 2015-02-03 18:43:42.428
Context
2015-02-04
11:08
Fix a memory leak that could follow an OOM error in ota. (check-in: 0d5415f264 user: dan tags: ota-update)
2015-02-03
18:43
Fix some problems surrounding WITHOUT ROWID tables with DESC primary key indexes . (check-in: a21fefb79a user: dan tags: ota-update)
15:56
Remove "PRAGMA ota_mode". (check-in: 1c111447a0 user: dan tags: ota-update)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/ota/ota1.test.
144
145
146
147
148
149
150

































151
152
153
154
155
156
157
158
159
160




161
162
163
164



165

166
167
168
169
170
171
172
173
174
175
      CREATE INDEX i3 ON t1(a, b, c, a, b, c);
    }

    9 { 
      CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c));
      CREATE INDEX i1 ON t1(b);
    }

































  } {
    reset_db
    execsql $schema

    do_test 1.$tn2.$tn.1 {
      create_ota1 ota.db
      $cmd test.db ota.db
    } {SQLITE_DONE}

    do_execsql_test 1.$tn2.$tn.2 {




      SELECT * FROM t1 ORDER BY a ASC;
    } {
      1 2 3 
      2 two three 



      3 {} 8.2

    }
 
    do_execsql_test 1.$tn2.$tn.3 { PRAGMA integrity_check } ok
  }
}

#-------------------------------------------------------------------------
# Check that an OTA cannot be applied to a table that has no PK.
#
# UPDATE: At one point OTA required that all tables featured either







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









|
>
>
>
>
|
|


>
>
>

>


|







144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
      CREATE INDEX i3 ON t1(a, b, c, a, b, c);
    }

    9 { 
      CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c));
      CREATE INDEX i1 ON t1(b);
    }

    10 { 
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      CREATE INDEX i1 ON t1(b DESC);
    }

    11 { 
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      CREATE INDEX i1 ON t1(b DESC, a ASC, c DESC);
    }

    12 { 
      CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; 
    }

    13 { 
      CREATE TABLE t1(a INT, b, c, PRIMARY KEY(a DESC)) WITHOUT ROWID; 
    }

    14 { 
      CREATE TABLE t1(a, b, c, PRIMARY KEY(a DESC, c)) WITHOUT ROWID;
      CREATE INDEX i1 ON t1(b);
    }

    15 { 
      CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c DESC)) WITHOUT ROWID;
      CREATE INDEX i1 ON t1(b);
    }

    16 { 
      CREATE TABLE t1(a, b, c, PRIMARY KEY(c DESC, a)) WITHOUT ROWID;
      CREATE INDEX i1 ON t1(b DESC, c, a);
    }
  } {
    reset_db
    execsql $schema

    do_test 1.$tn2.$tn.1 {
      create_ota1 ota.db
      $cmd test.db ota.db
    } {SQLITE_DONE}

    do_execsql_test 1.$tn2.$tn.2 { SELECT * FROM t1 ORDER BY a ASC } {
      1 2 3 
      2 two three 
      3 {} 8.2
    }
    do_execsql_test 1.$tn2.$tn.3 { SELECT * FROM t1 ORDER BY b ASC } {
      3 {} 8.2
      1 2 3 
      2 two three 
    }
    do_execsql_test 1.$tn2.$tn.4 { SELECT * FROM t1 ORDER BY c ASC } {
      1 2 3 
      3 {} 8.2
      2 two three 
    }
 
    do_execsql_test 1.$tn2.$tn.5 { PRAGMA integrity_check } ok
  }
}

#-------------------------------------------------------------------------
# Check that an OTA cannot be applied to a table that has no PK.
#
# UPDATE: At one point OTA required that all tables featured either
270
271
272
273
274
275
276













277
278
279
280
281
282
283
      CREATE INDEX i1 ON t1(b);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c, b, c);
    }
    5 {
      CREATE TABLE t1(a INT PRIMARY KEY, b, c);
      CREATE INDEX i1 ON t1(b);













      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c, b, c);
    }
  } {
    reset_db
    execsql $schema
    execsql {







>
>
>
>
>
>
>
>
>
>
>
>
>







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
      CREATE INDEX i1 ON t1(b);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c, b, c);
    }
    5 {
      CREATE TABLE t1(a INT PRIMARY KEY, b, c);
      CREATE INDEX i1 ON t1(b);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c, b, c);
    }

    6 {
      CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c);
      CREATE INDEX i1 ON t1(b DESC);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c DESC, b, c);
    }
    7 {
      CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID;
      CREATE INDEX i1 ON t1(b);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c, b, c);
    }
  } {
    reset_db
    execsql $schema
    execsql {
Changes to ext/ota/sqlite3ota.c.
641
642
643
644
645
646
647
648
649


650
651
652
653
654
655
656
    assert( p->zErrmsg==0 );
    rc = prepareFreeAndCollectError(p->db, &pXInfo, &p->zErrmsg,
        sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", pIter->zIdx)
    );
  }

  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){
    const char *zCollate = (const char*)sqlite3_column_text(pXInfo, 4);
    int iCid = sqlite3_column_int(pXInfo, 1);


    const char *zCol;
    const char *zType;

    if( iCid<0 ){
      /* An integer primary key. If the table has an explicit IPK, use
      ** its name. Otherwise, use "ota_rowid".  */
      if( pIter->eType==OTA_PK_IPK ){







<

>
>







641
642
643
644
645
646
647

648
649
650
651
652
653
654
655
656
657
    assert( p->zErrmsg==0 );
    rc = prepareFreeAndCollectError(p->db, &pXInfo, &p->zErrmsg,
        sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", pIter->zIdx)
    );
  }

  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){

    int iCid = sqlite3_column_int(pXInfo, 1);
    int bDesc = sqlite3_column_int(pXInfo, 3);
    const char *zCollate = (const char*)sqlite3_column_text(pXInfo, 4);
    const char *zCol;
    const char *zType;

    if( iCid<0 ){
      /* An integer primary key. If the table has an explicit IPK, use
      ** its name. Otherwise, use "ota_rowid".  */
      if( pIter->eType==OTA_PK_IPK ){
665
666
667
668
669
670
671

672
673
674
675
676
677
678
679
    }else{
      zCol = pIter->azTblCol[iCid];
      zType = pIter->azTblType[iCid];
    }

    zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom, zCol, zCollate);
    if( pIter->bUnique==0 || sqlite3_column_int(pXInfo, 5) ){

      zImpPK = sqlite3_mprintf("%z%sc%d", zImpPK, zCom, nBind);
    }
    zImpCols = sqlite3_mprintf(
        "%z%sc%d %s COLLATE %Q", zImpCols, zCom, nBind, zType, zCollate
    );
    zWhere = sqlite3_mprintf("%z%sc%d IS ?", zWhere, zAnd, nBind);
    if( zRet==0 || zImpPK==0 || zImpCols==0 || zWhere==0 ) rc = SQLITE_NOMEM;
    zCom = ", ";







>
|







666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
    }else{
      zCol = pIter->azTblCol[iCid];
      zType = pIter->azTblType[iCid];
    }

    zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom, zCol, zCollate);
    if( pIter->bUnique==0 || sqlite3_column_int(pXInfo, 5) ){
      const char *zOrder = (bDesc ? " DESC" : "");
      zImpPK = sqlite3_mprintf("%z%sc%d%s", zImpPK, zCom, nBind, zOrder);
    }
    zImpCols = sqlite3_mprintf(
        "%z%sc%d %s COLLATE %Q", zImpCols, zCom, nBind, zType, zCollate
    );
    zWhere = sqlite3_mprintf("%z%sc%d IS ?", zWhere, zAnd, nBind);
    if( zRet==0 || zImpPK==0 || zImpCols==0 || zWhere==0 ) rc = SQLITE_NOMEM;
    zCom = ", ";
834
835
836
837
838
839
840








































841
842
843
844
845
846
847
        zRet[i*2] = '?';
        zRet[i*2+1] = (i+1==nBind) ? '\0' : ',';
      }
    }
  }
  return zRet;
}









































/*
** If an error has already occurred when this function is called, it 
** immediately returns zero (without doing any work). Or, if an error
** occurs during the execution of this function, it sets the error code
** in the sqlite3ota object indicated by the first argument and returns
** zero.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
        zRet[i*2] = '?';
        zRet[i*2+1] = (i+1==nBind) ? '\0' : ',';
      }
    }
  }
  return zRet;
}

/*
** The iterator currently points to a table (not index) of type 
** OTA_PK_WITHOUT_ROWID. This function creates the PRIMARY KEY 
** declaration for the corresponding imposter table. For example,
** if the iterator points to a table created as:
**
**   CREATE TABLE t1(a, b, c, PRIMARY KEY(b, a DESC)) WITHOUT ROWID
**
** this function returns:
**
**   PRIMARY KEY("b", "a" DESC)
*/
static char *otaWithoutRowidPK(sqlite3ota *p, OtaObjIter *pIter){
  char *z = 0;
  assert( pIter->zIdx==0 );
  if( p->rc==SQLITE_OK ){
    const char *zSep = "PRIMARY KEY(";
    sqlite3_stmt *pXInfo = 0;     /* PRAGMA index_xinfo = (pIter->zTbl) */
    int rc;                       /* sqlite3_finalize() return code */

    p->rc = prepareFreeAndCollectError(p->db, &pXInfo, &p->zErrmsg,
        sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", pIter->zTbl)
    );
    while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){
      if( sqlite3_column_int(pXInfo, 5) ){
        /* int iCid = sqlite3_column_int(pXInfo, 0); */
        const char *zCol = (const char*)sqlite3_column_text(pXInfo, 2);
        const char *zDesc = sqlite3_column_int(pXInfo, 3) ? " DESC" : "";
        z = otaMPrintfAndCollectError(p, "%z%s\"%w\"%s", z, zSep, zCol, zDesc);
        zSep = ", ";
      }
    }
    z = otaMPrintfAndCollectError(p, "%z)", z);

    rc = sqlite3_finalize(pXInfo);
    if( p->rc==SQLITE_OK ) p->rc = rc;
  }
  return z;
}

/*
** If an error has already occurred when this function is called, it 
** immediately returns zero (without doing any work). Or, if an error
** occurs during the execution of this function, it sets the error code
** in the sqlite3ota object indicated by the first argument and returns
** zero.
872
873
874
875
876
877
878

879
880
881

882
883
884






885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
    int tnum = pIter->tnum;
    const char *zComma = "";
    char *zSql = 0;
    int iCol;
    sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->db, "main", 0, 1);

    for(iCol=0; p->rc==SQLITE_OK && iCol<pIter->nTblCol; iCol++){

      int iDataCol = pIter->aiTblOrder[iCol];
      const char *zCol = pIter->azTblCol[iDataCol];
      const char *zColl = 0;

      p->rc = sqlite3_table_column_metadata(
          p->db, "main", pIter->zTbl, zCol, 0, &zColl, 0, 0, 0
      );






      zSql = otaMPrintfAndCollectError(p, "%z%s\"%w\" %s COLLATE %s", 
          zSql, zComma, zCol, pIter->azTblType[iDataCol], zColl
      );
      zComma = ", ";
    }

    if( pIter->eType==OTA_PK_IPK || pIter->eType==OTA_PK_WITHOUT_ROWID ){
      zSql = otaMPrintfAndCollectError(p, "%z, PRIMARY KEY(", zSql);
      zComma = "";
      for(iCol=0; iCol<pIter->nTblCol; iCol++){
        if( pIter->abTblPk[iCol] ){
          zSql = otaMPrintfAndCollectError(p, "%z%s\"%w\"", 
              zSql, zComma, pIter->azTblCol[iCol]
          );
          zComma = ", ";
        }
      }
      zSql = otaMPrintfAndCollectError(p, "%z)", zSql);
    }

    zSql = otaMPrintfAndCollectError(p, "CREATE TABLE ota_imposter(%z)%s", 
        zSql, (pIter->eType==OTA_PK_WITHOUT_ROWID ? " WITHOUT ROWID" : "")
    );
    if( p->rc==SQLITE_OK ){
      sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->db, "main", 1, tnum);







>



>



>
>
>
>
>
>
|
|




|
|
<
<
|
|
<
<
<
|
<
<







914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942


943
944



945


946
947
948
949
950
951
952
    int tnum = pIter->tnum;
    const char *zComma = "";
    char *zSql = 0;
    int iCol;
    sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->db, "main", 0, 1);

    for(iCol=0; p->rc==SQLITE_OK && iCol<pIter->nTblCol; iCol++){
      const char *zPk = "";
      int iDataCol = pIter->aiTblOrder[iCol];
      const char *zCol = pIter->azTblCol[iDataCol];
      const char *zColl = 0;

      p->rc = sqlite3_table_column_metadata(
          p->db, "main", pIter->zTbl, zCol, 0, &zColl, 0, 0, 0
      );

      if( pIter->eType==OTA_PK_IPK && pIter->abTblPk[iCol] ){
        /* If the target table column is an "INTEGER PRIMARY KEY", add
        ** "PRIMARY KEY" to the imposter table column declaration. */
        zPk = "PRIMARY KEY ";
      }
      zSql = otaMPrintfAndCollectError(p, "%z%s\"%w\" %s %sCOLLATE %s", 
          zSql, zComma, zCol, pIter->azTblType[iDataCol], zPk, zColl
      );
      zComma = ", ";
    }

    if( pIter->eType==OTA_PK_WITHOUT_ROWID ){
      char *zPk = otaWithoutRowidPK(p, pIter);


      if( zPk ){
        zSql = otaMPrintfAndCollectError(p, "%z, %z", zSql, zPk);



      }


    }

    zSql = otaMPrintfAndCollectError(p, "CREATE TABLE ota_imposter(%z)%s", 
        zSql, (pIter->eType==OTA_PK_WITHOUT_ROWID ? " WITHOUT ROWID" : "")
    );
    if( p->rc==SQLITE_OK ){
      sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->db, "main", 1, tnum);
Changes to src/pragma.c.
1577
1578
1579
1580
1581
1582
1583




1584
1585
1586
1587
1588
1589
1590
  }
  break;

  case PragTyp_INDEX_INFO: if( zRight ){
    Index *pIdx;
    Table *pTab;
    pIdx = sqlite3FindIndex(db, zRight, zDb);




    if( pIdx ){
      int i;
      int mx = pPragma->iArg ? pIdx->nColumn : pIdx->nKeyCol;
      pTab = pIdx->pTable;
      sqlite3VdbeSetNumCols(v, 6);
      pParse->nMem = 6;
      sqlite3CodeVerifySchema(pParse, iDb);







>
>
>
>







1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
  }
  break;

  case PragTyp_INDEX_INFO: if( zRight ){
    Index *pIdx;
    Table *pTab;
    pIdx = sqlite3FindIndex(db, zRight, zDb);
    if( pIdx==0 ){
      pTab = sqlite3FindTable(db, zRight, zDb);
      if( pTab && !HasRowid(pTab) ) pIdx = sqlite3PrimaryKeyIndex(pTab);
    }
    if( pIdx ){
      int i;
      int mx = pPragma->iArg ? pIdx->nColumn : pIdx->nKeyCol;
      pTab = pIdx->pTable;
      sqlite3VdbeSetNumCols(v, 6);
      pParse->nMem = 6;
      sqlite3CodeVerifySchema(pParse, iDb);