SQLite: Check-in [a21fefb7]

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 \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| ota-update
Files:	files \| file ages \| folders
SHA1:	a21fefb79a161e6bb057ae4f6ba554f659706ac1
User & Date:	dan 2015-02-03 18:43:42

Context

2015-02-04
11:08		Fix a memory leak that could follow an OOM error in ota. (check-in: 0d5415f2 user: dan tags: ota-update)
2015-02-03
18:43		Fix some problems surrounding WITHOUT ROWID tables with DESC primary key indexes . (check-in: a21fefb7 user: dan tags: ota-update)
15:56		Remove "PRAGMA ota_mode". (check-in: 1c111447 user: dan tags: ota-update)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/ota/ota1.test.

      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
................................................................................
      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 {








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









|
>
>
>
>
|
|


>
>
>

>


|







 







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

      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
................................................................................
      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.

    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 ){
................................................................................
    }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 = ", ";
................................................................................
        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.
................................................................................
    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);








<

>
>







 







>
|







 







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







 







>



>



>
>
>
>
>
>
|
|




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

    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 ){
................................................................................
    }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 = ", ";
................................................................................
        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.
................................................................................
    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.

  }
  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);








>
>
>
>

  }
  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);