/ Check-in [236a54d2]
Login

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

Overview
Comment:Added support for the INTEGER PRIMARY KEY column type. (CVS 333)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 236a54d289e858a1e0505a20d907a2a40c01b521
User & Date: drh 2001-12-21 14:30:43
Context
2001-12-22
14:49
Bug fixing in the new integer primary key code. (CVS 334) check-in: 29cab124 user: drh tags: trunk
2001-12-21
14:30
Added support for the INTEGER PRIMARY KEY column type. (CVS 333) check-in: 236a54d2 user: drh tags: trunk
2001-12-16
20:05
Added the ability to say things like "SELECT rowid, * FROM table1;" (CVS 332) check-in: ffbdd43f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -2.1.8
            1  +2.2.0

Changes to src/build.c.

    21     21   **     COPY
    22     22   **     VACUUM
    23     23   **     BEGIN TRANSACTION
    24     24   **     COMMIT
    25     25   **     ROLLBACK
    26     26   **     PRAGMA
    27     27   **
    28         -** $Id: build.c,v 1.59 2001/12/15 02:35:59 drh Exp $
           28  +** $Id: build.c,v 1.60 2001/12/21 14:30:43 drh Exp $
    29     29   */
    30     30   #include "sqliteInt.h"
    31     31   #include <ctype.h>
    32     32   
    33     33   /*
    34     34   ** This routine is called after a single SQL statement has been
    35     35   ** parsed and we want to execute the VDBE code to implement 
................................................................................
   264    264     }
   265    265     for(pElem=sqliteHashFirst(&toDelete); pElem; pElem=sqliteHashNext(pElem)){
   266    266       Table *pTable = sqliteHashData(pElem);
   267    267       sqliteUnlinkAndDeleteTable(db, pTable);
   268    268     }
   269    269     sqliteHashClear(&toDelete);
   270    270     for(pElem=sqliteHashFirst(&db->idxHash); pElem; pElem=sqliteHashNext(pElem)){
   271         -    Table *pIndex = sqliteHashData(pElem);
          271  +    Index *pIndex = sqliteHashData(pElem);
   272    272       if( pIndex->isDelete ){
   273    273         sqliteHashInsert(&toDelete, pIndex, 0, pIndex);
   274    274       }else{
   275    275         pIndex->isCommit = 1;
   276    276       }
   277    277     }
   278    278     for(pElem=sqliteHashFirst(&toDelete); pElem; pElem=sqliteHashNext(pElem)){
................................................................................
   307    307     }
   308    308     for(pElem=sqliteHashFirst(&toDelete); pElem; pElem=sqliteHashNext(pElem)){
   309    309       Table *pTable = sqliteHashData(pElem);
   310    310       sqliteUnlinkAndDeleteTable(db, pTable);
   311    311     }
   312    312     sqliteHashClear(&toDelete);
   313    313     for(pElem=sqliteHashFirst(&db->idxHash); pElem; pElem=sqliteHashNext(pElem)){
   314         -    Table *pIndex = sqliteHashData(pElem);
          314  +    Index *pIndex = sqliteHashData(pElem);
   315    315       if( !pIndex->isCommit ){
   316    316         sqliteHashInsert(&toDelete, pIndex, 0, pIndex);
   317    317       }else{
   318    318         pIndex->isDelete = 0;
   319    319       }
   320    320     }
   321    321     for(pElem=sqliteHashFirst(&toDelete); pElem; pElem=sqliteHashNext(pElem)){
................................................................................
   421    421     if( pTable==0 ){
   422    422       sqliteFree(zName);
   423    423       return;
   424    424     }
   425    425     pTable->zName = zName;
   426    426     pTable->nCol = 0;
   427    427     pTable->aCol = 0;
          428  +  pTable->iPKey = -1;
   428    429     pTable->pIndex = 0;
   429    430     pTable->isTemp = isTemp;
   430    431     if( pParse->pNewTable ) sqliteDeleteTable(db, pParse->pNewTable);
   431    432     pParse->pNewTable = pTable;
   432    433     if( !pParse->initFlag && (v = sqliteGetVdbe(pParse))!=0 ){
   433    434       if( (db->flags & SQLITE_InTrans)==0 ){
   434    435         sqliteVdbeAddOp(v, OP_Transaction, 0, 0);
   435    436         sqliteVdbeAddOp(v, OP_VerifyCookie, db->schema_cookie, 0);
   436    437         pParse->schemaVerified = 1;
   437    438       }
   438    439       if( !isTemp ){
          440  +      sqliteVdbeAddOp(v, OP_SetCookie, db->file_format, 1);
   439    441         sqliteVdbeAddOp(v, OP_OpenWrite, 0, 2);
   440    442         sqliteVdbeChangeP3(v, -1, MASTER_NAME, P3_STATIC);
   441    443       }
   442    444     }
   443    445   }
   444    446   
   445    447   /*
................................................................................
   529    531     if( minusFlag ){
   530    532       sqliteSetNString(pz, "-", 1, pVal->z, pVal->n, 0);
   531    533     }else{
   532    534       sqliteSetNString(pz, pVal->z, pVal->n, 0);
   533    535     }
   534    536     sqliteDequote(*pz);
   535    537   }
          538  +
          539  +/*
          540  +** Designate the PRIMARY KEY for the table.  pList is a list of names 
          541  +** of columns that form the primary key.  If pList is NULL, then the
          542  +** most recently added column of the table is the primary key.
          543  +**
          544  +** A table can have at most one primary key.  If the table already has
          545  +** a primary key (and this is the second primary key) then create an
          546  +** error.
          547  +**
          548  +** If the PRIMARY KEY is on a single column whose datatype is INTEGER,
          549  +** then we will try to use that column as the row id.  (Exception:
          550  +** For backwards compatibility with older databases, do not do this
          551  +** if the file format version number is less than 1.)  Set the Table.iPKey
          552  +** field of the table under construction to be the index of the
          553  +** INTEGER PRIMARY KEY column.  Table.iPKey is set to -1 if there is
          554  +** no INTEGER PRIMARY KEY.
          555  +**
          556  +** If the key is not an INTEGER PRIMARY KEY, then create a unique
          557  +** index for the key.  No index is created for INTEGER PRIMARY KEYs.
          558  +*/
          559  +void sqliteAddPrimaryKey(Parse *pParse, IdList *pList){
          560  +  Table *pTab = pParse->pNewTable;
          561  +  char *zType = 0;
          562  +  int iCol = -1;
          563  +  if( pTab==0 ) return;
          564  +  if( pTab->hasPrimKey ){
          565  +    sqliteSetString(&pParse->zErrMsg, "table \"", pTab->zName, 
          566  +        "\" has more than one primary key", 0);
          567  +    pParse->nErr++;
          568  +    return;
          569  +  }
          570  +  pTab->hasPrimKey = 1;
          571  +  if( pList==0 ){
          572  +    iCol = pTab->nCol - 1;
          573  +  }else if( pList->nId==1 ){
          574  +    for(iCol=0; iCol<pTab->nCol; iCol++){
          575  +      if( sqliteStrICmp(pList->a[0].zName, pTab->aCol[iCol].zName)==0 ) break;
          576  +    }
          577  +  }
          578  +  if( iCol>=0 && iCol<pTab->nCol ){
          579  +    zType = pTab->aCol[iCol].zType;
          580  +  }
          581  +  if( pParse->db->file_format>=1 && 
          582  +           zType && sqliteStrICmp(zType, "INTEGER")==0 ){
          583  +    pTab->iPKey = iCol;
          584  +  }else{
          585  +    sqliteCreateIndex(pParse, 0, 0, pList, 1, 0, 0);
          586  +  }
          587  +}
   536    588   
   537    589   /*
   538    590   ** Come up with a new random value for the schema cookie.  Make sure
   539    591   ** the new value is different from the old.
   540    592   **
   541    593   ** The schema cookie is used to determine when the schema for the
   542    594   ** database changes.  After each schema change, the cookie value
................................................................................
   783    835         " may not have new indices added", 0);
   784    836       pParse->nErr++;
   785    837       goto exit_create_index;
   786    838     }
   787    839   
   788    840     /* If this index is created while re-reading the schema from sqlite_master
   789    841     ** but the table associated with this index is a temporary table, it can
   790         -  ** only mean that the table this index is really associated with is one 
   791         -  ** whose name is hidden behind a temporary table with the same name.
          842  +  ** only mean that the table that this index is really associated with is
          843  +  ** one whose name is hidden behind a temporary table with the same name.
   792    844     ** Since its table has been suppressed, we need to also suppress the
   793    845     ** index.
   794    846     */
   795    847     if( pParse->initFlag && pTab->isTemp ){
   796    848       goto exit_create_index;
   797    849     }
   798    850   

Changes to src/delete.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle DELETE FROM statements.
    14     14   **
    15         -** $Id: delete.c,v 1.21 2001/11/07 16:48:27 drh Exp $
           15  +** $Id: delete.c,v 1.22 2001/12/21 14:30:43 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Process a DELETE FROM statement.
    21     21   */
    22     22   void sqliteDeleteFrom(
................................................................................
   153    153       addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
   154    154       sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
   155    155       if( pTab->pIndex ){
   156    156         for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
   157    157           int j;
   158    158           sqliteVdbeAddOp(v, OP_Recno, base, 0);
   159    159           for(j=0; j<pIdx->nColumn; j++){
   160         -          sqliteVdbeAddOp(v, OP_Column, base, pIdx->aiColumn[j]);
          160  +          int idx = pIdx->aiColumn[j];
          161  +          if( idx==pTab->iPKey ){
          162  +            sqliteVdbeAddOp(v, OP_Dup, j, 0);
          163  +          }else{
          164  +            sqliteVdbeAddOp(v, OP_Column, base, idx);
          165  +          }
   161    166           }
   162    167           sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
   163    168           sqliteVdbeAddOp(v, OP_IdxDelete, base+i, 0);
   164    169         }
   165    170       }
   166    171       sqliteVdbeAddOp(v, OP_Delete, base, 0);
   167    172       sqliteVdbeAddOp(v, OP_Goto, 0, addr);

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.34 2001/11/24 00:31:46 drh Exp $
           15  +** $Id: expr.c,v 1.35 2001/12/21 14:30:43 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Walk an expression tree.  Return 1 if the expression is constant
    21     21   ** and 0 if it involves variables.
    22     22   */
................................................................................
   123    123           int j;
   124    124           Table *pTab = pTabList->a[i].pTab;
   125    125           if( pTab==0 ) continue;
   126    126           for(j=0; j<pTab->nCol; j++){
   127    127             if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
   128    128               cnt++;
   129    129               pExpr->iTable = i + pParse->nTab;
   130         -            pExpr->iColumn = j;
          130  +            if( j==pTab->iPKey ){
          131  +              /* Substitute the record number for the INTEGER PRIMARY KEY */
          132  +              pExpr->iColumn = -1;
          133  +            }else{
          134  +              pExpr->iColumn = j;
          135  +            }
   131    136             }
   132    137           }
   133    138         }
   134    139         if( cnt==0 && sqliteIsRowid(z) ){
   135    140           pExpr->iColumn = -1;
   136    141           pExpr->iTable = pParse->nTab;
   137    142           cnt = 1 + (pTabList->nId>1);
................................................................................
   186    191           }
   187    192           if( sqliteStrICmp(zTab, zLeft)!=0 ) continue;
   188    193           if( 0==(cntTab++) ) pExpr->iTable = i + pParse->nTab;
   189    194           for(j=0; j<pTab->nCol; j++){
   190    195             if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
   191    196               cnt++;
   192    197               pExpr->iTable = i + pParse->nTab;
   193         -            pExpr->iColumn = j;
          198  +            if( j==pTab->iPKey ){
          199  +              /* Substitute the record number for the INTEGER PRIMARY KEY */
          200  +              pExpr->iColumn = -1;
          201  +            }else{
          202  +              pExpr->iColumn = j;
          203  +            }
   194    204             }
   195    205           }
   196    206         }
   197    207         if( cnt==0 && cntTab==1 && sqliteIsRowid(zRight) ){
   198    208           cnt = 1;
   199    209           pExpr->iColumn = -1;
   200    210         }

Changes to src/insert.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.26 2001/11/07 16:48:27 drh Exp $
           15  +** $Id: insert.c,v 1.27 2001/12/21 14:30:43 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** This routine is call to handle SQL of the following forms:
    21     21   **
    22     22   **    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    45     45     Index *pIdx;          /* For looping over indices of the table */
    46     46     int srcTab;           /* Date comes from this temporary cursor if >=0 */
    47     47     int nColumn;          /* Number of columns in the data */
    48     48     int base;             /* First available cursor */
    49     49     int iCont, iBreak;    /* Beginning and end of the loop over srcTab */
    50     50     sqlite *db;           /* The main database structure */
    51     51     int openOp;           /* Opcode used to open cursors */
           52  +  int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
    52     53   
    53     54     if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
    54     55     db = pParse->db;
    55     56   
    56     57     /* Locate the table into which we will be inserting new information.
    57     58     */
    58     59     zTab = sqliteTableNameFromToken(pTableName);
................................................................................
   136    137       for(i=0; i<pColumn->nId; i++){
   137    138         pColumn->a[i].idx = -1;
   138    139       }
   139    140       for(i=0; i<pColumn->nId; i++){
   140    141         for(j=0; j<pTab->nCol; j++){
   141    142           if( sqliteStrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
   142    143             pColumn->a[i].idx = j;
          144  +          if( j==pTab->iPKey ){
          145  +            keyColumn = j;
          146  +          }
   143    147             break;
   144    148           }
   145    149         }
   146    150         if( j>=pTab->nCol ){
   147    151           sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
   148    152              " has no column named ", pColumn->a[i].zName, 0);
   149    153           pParse->nErr++;
   150    154           goto insert_cleanup;
   151    155         }
   152    156       }
   153    157     }
          158  +
          159  +  /* If there is not IDLIST term but the table has an integer primary
          160  +  ** key, the set the keyColumn variable to the primary key column.
          161  +  */
          162  +  if( pColumn==0 ){
          163  +    keyColumn = pTab->iPKey;
          164  +  }
   154    165   
   155    166     /* Open cursors into the table that is received the new data and
   156    167     ** all indices of that table.
   157    168     */
   158    169     base = pParse->nTab;
   159    170     openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
   160    171     sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
................................................................................
   174    185         sqliteVdbeAddOp(v, OP_Integer, 0, 0);  /* Initialize the row count */
   175    186       }
   176    187       iBreak = sqliteVdbeMakeLabel(v);
   177    188       sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
   178    189       iCont = sqliteVdbeCurrentAddr(v);
   179    190     }
   180    191   
   181         -  /* Create a new entry in the table and fill it with data.
          192  +  /* Push the record number for the new entry onto the stack.  The
          193  +  ** record number is a randomly generate integer created by NewRecno
          194  +  ** except when the table has an INTEGER PRIMARY KEY column, in which
          195  +  ** case the record number is the same as that column.
   182    196     */
   183         -  sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
          197  +  if( keyColumn>=0 ){
          198  +    if( srcTab>=0 ){
          199  +      sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
          200  +    }else{
          201  +      sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
          202  +    }
          203  +    sqliteVdbeAddOp(v, OP_AddImm, 0, 0);  /* Make sure ROWID is an integer */
          204  +  }else{
          205  +    sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
          206  +  }
          207  +
          208  +  /* If there are indices, we'll need this record number again, so make
          209  +  ** a copy.
          210  +  */
   184    211     if( pTab->pIndex ){
   185    212       sqliteVdbeAddOp(v, OP_Dup, 0, 0);
   186    213     }
          214  +
          215  +  /* Push onto the stack data for all columns of the new entry, beginning
          216  +  ** with the first column.
          217  +  */
   187    218     for(i=0; i<pTab->nCol; i++){
          219  +    if( i==pTab->iPKey ){
          220  +      /* The value of the INTEGER PRIMARY KEY column is always a NULL.
          221  +      ** Whenever this column is used, the record number will be substituted
          222  +      ** in its place, so there is no point it it taking up space in
          223  +      ** the data record. */
          224  +      sqliteVdbeAddOp(v, OP_String, 0, 0);
          225  +      continue;
          226  +    }
   188    227       if( pColumn==0 ){
   189    228         j = i;
   190    229       }else{
   191    230         for(j=0; j<pColumn->nId; j++){
   192    231           if( pColumn->a[j].idx==i ) break;
   193    232         }
   194    233       }
................................................................................
   197    236         sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
   198    237       }else if( srcTab>=0 ){
   199    238         sqliteVdbeAddOp(v, OP_Column, srcTab, i); 
   200    239       }else{
   201    240         sqliteExprCode(pParse, pList->a[j].pExpr);
   202    241       }
   203    242     }
   204         -  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
   205         -  sqliteVdbeAddOp(v, OP_Put, base, 0);
   206         -  
   207    243   
          244  +  /* Create the new record and put it into the database.
          245  +  */
          246  +  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
          247  +  sqliteVdbeAddOp(v, OP_Put, base, keyColumn>=0);
          248  +  
   208    249     /* Create appropriate entries for the new data row in all indices
   209    250     ** of the table.
   210    251     */
   211    252     for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
   212    253       if( pIdx->pNext ){
   213    254         sqliteVdbeAddOp(v, OP_Dup, 0, 0);
   214    255       }
   215    256       for(i=0; i<pIdx->nColumn; i++){
   216    257         int idx = pIdx->aiColumn[i];
          258  +      if( idx==pTab->iPKey ){
          259  +        /* Copy the record number in place of the INTEGER PRIMARY KEY column */
          260  +        sqliteVdbeAddOp(v, OP_Dup, i, 0);
          261  +        continue;
          262  +      }
   217    263         if( pColumn==0 ){
   218    264           j = idx;
   219    265         }else{
   220    266           for(j=0; j<pColumn->nId; j++){
   221    267             if( pColumn->a[j].idx==idx ) break;
   222    268           }
   223    269         }

Changes to src/main.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** Main file for the SQLite library.  The routines in this file
    13     13   ** implement the programmer interface to the library.  Routines in
    14     14   ** other files are for internal use by SQLite and should not be
    15     15   ** accessed by users of the library.
    16     16   **
    17         -** $Id: main.c,v 1.51 2001/12/05 00:21:20 drh Exp $
           17  +** $Id: main.c,v 1.52 2001/12/21 14:30:43 drh Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "os.h"
    21     21   
    22     22   /*
    23     23   ** This is the callback routine for the code that initializes the
    24     24   ** database.  See sqliteInit() below for additional information.
    25     25   **
    26     26   ** Each callback contains the following information:
    27     27   **
    28         -**     argv[0] = "meta" or "table" or "index"
           28  +**     argv[0] = "file-format" or "schema-cookie" or "table" or "index"
    29     29   **     argv[1] = table or index name or meta statement type.
    30     30   **     argv[2] = root page number for table or index.  NULL for meta.
    31     31   **     argv[3] = SQL create statement for the table or index
    32     32   **
    33     33   */
    34     34   static int sqliteOpenCb(void *pDb, int argc, char **argv, char **azColName){
    35     35     sqlite *db = (sqlite*)pDb;
................................................................................
    38     38   
    39     39     /* TODO: Do some validity checks on all fields.  In particular,
    40     40     ** make sure fields do not contain NULLs. Otherwise we might core
    41     41     ** when attempting to initialize from a corrupt database file. */
    42     42   
    43     43     assert( argc==4 );
    44     44     switch( argv[0][0] ){
    45         -    case 'm': {  /* Meta information */
    46         -      if( strcmp(argv[1],"file-format")==0 ){
    47         -        db->file_format = atoi(argv[3]);
    48         -      }else if( strcmp(argv[1],"schema-cookie")==0 ){
    49         -        db->schema_cookie = atoi(argv[3]);
    50         -        db->next_cookie = db->schema_cookie;
    51         -      }
           45  +    case 'f': {  /* File format */
           46  +      db->file_format = atoi(argv[3]);
           47  +      break;
           48  +    }
           49  +    case 's': { /* Schema cookie */
           50  +      db->schema_cookie = atoi(argv[3]);
           51  +      db->next_cookie = db->schema_cookie;
    52     52         break;
    53     53       }
    54     54       case 'i':
    55     55       case 't': {  /* CREATE TABLE  and CREATE INDEX statements */
    56     56         if( argv[3] && argv[3][0] ){
    57     57           /* Call the parser to process a CREATE TABLE or CREATE INDEX statement.
    58     58           ** But because sParse.initFlag is set to 1, no VDBE code is generated
................................................................................
   152    152     ** The following program invokes its callback on the SQL for each
   153    153     ** table then goes back and invokes the callback on the
   154    154     ** SQL for each index.  The callback will invoke the
   155    155     ** parser to build the internal representation of the
   156    156     ** database scheme.
   157    157     */
   158    158     static VdbeOp initProg[] = {
   159         -    { OP_Open,     0, 2,  0},
   160         -    { OP_Rewind,   0, 31, 0},
   161         -    { OP_Column,   0, 0,  0},           /* 2 */
   162         -    { OP_String,   0, 0,  "meta"},
   163         -    { OP_Ne,       0, 10, 0},
   164         -    { OP_Column,   0, 0,  0},
   165         -    { OP_Column,   0, 1,  0},
   166         -    { OP_Column,   0, 3,  0},
   167         -    { OP_Column,   0, 4,  0},
   168         -    { OP_Callback, 4, 0,  0},
   169         -    { OP_Next,     0, 2,  0},           /* 10 */
   170         -    { OP_Rewind,   0, 31, 0},           /* 11 */
   171         -    { OP_Column,   0, 0,  0},           /* 12 */
   172         -    { OP_String,   0, 0,  "table"},
   173         -    { OP_Ne,       0, 20, 0},
   174         -    { OP_Column,   0, 0,  0},
   175         -    { OP_Column,   0, 1,  0},
   176         -    { OP_Column,   0, 3,  0},
   177         -    { OP_Column,   0, 4,  0},
   178         -    { OP_Callback, 4, 0,  0},
   179         -    { OP_Next,     0, 12, 0},           /* 20 */
   180         -    { OP_Rewind,   0, 31, 0},           /* 21 */
   181         -    { OP_Column,   0, 0,  0},           /* 22 */
   182         -    { OP_String,   0, 0,  "index"},
   183         -    { OP_Ne,       0, 30, 0},
   184         -    { OP_Column,   0, 0,  0},
   185         -    { OP_Column,   0, 1,  0},
   186         -    { OP_Column,   0, 3,  0},
   187         -    { OP_Column,   0, 4,  0},
   188         -    { OP_Callback, 4, 0,  0},
   189         -    { OP_Next,     0, 22, 0},           /* 30 */
   190         -    { OP_String,   0, 0,  "meta"},      /* 31 */
   191         -    { OP_String,   0, 0,  "schema-cookie"},
   192         -    { OP_String,   0, 0,  0},
   193         -    { OP_ReadCookie,0,0,  0},
   194         -    { OP_Callback, 4, 0,  0},
   195         -    { OP_Close,    0, 0,  0},
   196         -    { OP_Halt,     0, 0,  0},
          159  +    { OP_Open,       0, 2,  0},
          160  +    { OP_String,     0, 0,  "file-format"},
          161  +    { OP_String,     0, 0,  0},
          162  +    { OP_String,     0, 0,  0},
          163  +    { OP_ReadCookie, 0, 1,  0},
          164  +    { OP_Callback,   4, 0,  0},
          165  +    { OP_String,     0, 0,  "schema_cookie"},
          166  +    { OP_String,     0, 0,  0},
          167  +    { OP_String,     0, 0,  0},
          168  +    { OP_ReadCookie, 0, 0,  0},
          169  +    { OP_Callback,   4, 0,  0},
          170  +    { OP_Rewind,     0, 31, 0},
          171  +    { OP_Column,     0, 0,  0},           /* 12 */
          172  +    { OP_String,     0, 0,  "table"},
          173  +    { OP_Ne,         0, 20, 0},
          174  +    { OP_Column,     0, 0,  0},
          175  +    { OP_Column,     0, 1,  0},
          176  +    { OP_Column,     0, 3,  0},
          177  +    { OP_Column,     0, 4,  0},
          178  +    { OP_Callback,   4, 0,  0},
          179  +    { OP_Next,       0, 12, 0},           /* 20 */
          180  +    { OP_Rewind,     0, 31, 0},           /* 21 */
          181  +    { OP_Column,     0, 0,  0},           /* 22 */
          182  +    { OP_String,     0, 0,  "index"},
          183  +    { OP_Ne,         0, 30, 0},
          184  +    { OP_Column,     0, 0,  0},
          185  +    { OP_Column,     0, 1,  0},
          186  +    { OP_Column,     0, 3,  0},
          187  +    { OP_Column,     0, 4,  0},
          188  +    { OP_Callback,   4, 0,  0},
          189  +    { OP_Next,       0, 22, 0},           /* 30 */
          190  +    { OP_Close,      0, 0,  0},           /* 31 */
          191  +    { OP_Halt,       0, 0,  0},
   197    192     };
   198    193   
   199    194     /* Create a virtual machine to run the initialization program.  Run
   200    195     ** the program.  Then delete the virtual machine.
   201    196     */
   202    197     vdbe = sqliteVdbeCreate(db);
   203    198     if( vdbe==0 ){
................................................................................
   204    199       sqliteSetString(pzErrMsg, "out of memory", 0);
   205    200       return SQLITE_NOMEM;
   206    201     }
   207    202     sqliteVdbeAddOpList(vdbe, sizeof(initProg)/sizeof(initProg[0]), initProg);
   208    203     rc = sqliteVdbeExec(vdbe, sqliteOpenCb, db, pzErrMsg, 
   209    204                         db->pBusyArg, db->xBusyCallback);
   210    205     sqliteVdbeDelete(vdbe);
   211         -  if( rc==SQLITE_OK && db->file_format>1 && db->nTable>0 ){
          206  +  if( rc==SQLITE_OK && db->nTable==0 ){
          207  +    db->file_format = FILE_FORMAT;
          208  +  }
          209  +  if( rc==SQLITE_OK && db->file_format>FILE_FORMAT ){
   212    210       sqliteSetString(pzErrMsg, "unsupported file format", 0);
   213    211       rc = SQLITE_ERROR;
   214    212     }
   215    213     if( rc==SQLITE_OK ){
   216    214       Table *pTab;
   217    215       char *azArg[6];
   218    216       azArg[0] = "table";
................................................................................
   278    276         }
   279    277       }
   280    278       sqliteFree(db);
   281    279       sqliteStrRealloc(pzErrMsg);
   282    280       return 0;
   283    281     }
   284    282   
   285         -  /* Assume file format 1 unless the database says otherwise */
   286         -  db->file_format = 1;
   287         -
   288    283     /* Attempt to read the schema */
   289    284     rc = sqliteInit(db, pzErrMsg);
   290    285     if( sqlite_malloc_failed ){
   291    286       sqlite_close(db);
   292    287       goto no_mem_on_open;
   293    288     }else if( rc!=SQLITE_OK && rc!=SQLITE_BUSY ){
   294    289       sqlite_close(db);

Changes to src/parse.y.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains SQLite's grammar for SQL.  Process this file
    13     13   ** using the lemon parser generator to generate C code that runs
    14     14   ** the parser.  Lemon will also generate a header file containing
    15     15   ** numeric codes for all of the tokens.
    16     16   **
    17         -** @(#) $Id: parse.y,v 1.39 2001/12/16 20:05:06 drh Exp $
           17  +** @(#) $Id: parse.y,v 1.40 2001/12/21 14:30:43 drh Exp $
    18     18   */
    19     19   %token_prefix TK_
    20     20   %token_type {Token}
    21     21   %default_type {Token}
    22     22   %extra_argument {Parse *pParse}
    23     23   %syntax_error {
    24     24     sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
   134    134   carg ::= DEFAULT MINUS FLOAT(X).     {sqliteAddDefaultValue(pParse,&X,1);}
   135    135   carg ::= DEFAULT NULL. 
   136    136   
   137    137   // In addition to the type name, we also care about the primary key and
   138    138   // UNIQUE constraints.
   139    139   //
   140    140   ccons ::= NOT NULL.                  {sqliteAddNotNull(pParse);}
   141         -ccons ::= PRIMARY KEY sortorder.     {sqliteCreateIndex(pParse,0,0,0,1,0,0);}
          141  +ccons ::= PRIMARY KEY sortorder.     {sqliteAddPrimaryKey(pParse, 0);}
   142    142   ccons ::= UNIQUE.                    {sqliteCreateIndex(pParse,0,0,0,1,0,0);}
   143    143   ccons ::= CHECK LP expr RP.
   144    144   
   145    145   // For the time being, the only constraint we care about is the primary
   146    146   // key and UNIQUE.  Both create indices.
   147    147   //
   148    148   conslist_opt ::= .
   149    149   conslist_opt ::= COMMA conslist.
   150    150   conslist ::= conslist COMMA tcons.
   151    151   conslist ::= conslist tcons.
   152    152   conslist ::= tcons.
   153    153   tcons ::= CONSTRAINT ids.
   154         -tcons ::= PRIMARY KEY LP idxlist(X) RP. {sqliteCreateIndex(pParse,0,0,X,1,0,0);}
          154  +tcons ::= PRIMARY KEY LP idxlist(X) RP. {sqliteAddPrimaryKey(pParse,X);}
   155    155   tcons ::= UNIQUE LP idxlist(X) RP.      {sqliteCreateIndex(pParse,0,0,X,1,0,0);}
   156    156   tcons ::= CHECK expr.
   157    157   
   158    158   ////////////////////////// The DROP TABLE /////////////////////////////////////
   159    159   //
   160    160   cmd ::= DROP TABLE ids(X).          {sqliteDropTable(pParse,&X);}
   161    161   

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.72 2001/12/05 00:21:20 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.73 2001/12/21 14:30:43 drh Exp $
    15     15   */
    16     16   #include "sqlite.h"
    17     17   #include "hash.h"
    18     18   #include "vdbe.h"
    19     19   #include "parse.h"
    20     20   #include "btree.h"
    21     21   #include <stdio.h>
................................................................................
    26     26   /*
    27     27   ** The maximum number of in-memory pages to use for the main database
    28     28   ** table and for temporary tables.
    29     29   */
    30     30   #define MAX_PAGES   100
    31     31   #define TEMP_PAGES   25
    32     32   
           33  +/*
           34  +** File format version number
           35  +*/
           36  +#define FILE_FORMAT 1
           37  +
    33     38   /*
    34     39   ** Integers of known sizes.  These typedefs might change for architectures
    35     40   ** where the sizes very.  Preprocessor macros are available so that the
    36     41   ** types can be conveniently redefined at compile-type.  Like this:
    37     42   **
    38     43   **         cc '-DUINTPTR_TYPE=long long int' ...
    39     44   */
................................................................................
   209    214   ** information about each column of an SQL table is held in an instance
   210    215   ** of this structure.
   211    216   */
   212    217   struct Column {
   213    218     char *zName;     /* Name of this column */
   214    219     char *zDflt;     /* Default value of this column */
   215    220     char *zType;     /* Data type for this column */
   216         -  int notNull;     /* True if there is a NOT NULL constraint */
          221  +  u8 notNull;      /* True if there is a NOT NULL constraint */
          222  +  u8 isPrimKey;    /* True if this column is an INTEGER PRIMARY KEY */
   217    223   };
   218    224   
   219    225   /*
   220    226   ** Each SQL table is represented in memory by
   221    227   ** an instance of the following structure.
   222    228   */
   223    229   struct Table {
   224    230     char *zName;     /* Name of the table */
   225    231     int nCol;        /* Number of columns in this table */
   226    232     Column *aCol;    /* Information about each column */
          233  +  int iPKey;       /* Use this column as the record-number for each row */
   227    234     Index *pIndex;   /* List of SQL indexes on this table. */
   228    235     int tnum;        /* Page containing root for this table */
   229    236     u8 readOnly;     /* True if this table should not be written by the user */
   230    237     u8 isCommit;     /* True if creation of this table has been committed */
   231    238     u8 isDelete;     /* True if this table is being deleted */
   232    239     u8 isTemp;       /* True if stored in db->pBeTemp instead of db->pBe */
          240  +  u8 hasPrimKey;   /* True if there exists a primary key */
   233    241   };
   234    242   
   235    243   /*
   236    244   ** Each SQL index is represented in memory by an
   237    245   ** instance of the following structure.
   238    246   **
   239    247   ** The columns of the table that are to be indexed are described

Changes to src/update.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle UPDATE statements.
    14     14   **
    15         -** $Id: update.c,v 1.22 2001/11/21 02:21:12 drh Exp $
           15  +** $Id: update.c,v 1.23 2001/12/21 14:30:43 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Process an UPDATE statement.
    21     21   */
    22     22   void sqliteUpdate(
................................................................................
    36     36     int base;              /* Index of first available table cursor */
    37     37     sqlite *db;            /* The database structure */
    38     38     Index **apIdx = 0;     /* An array of indices that need updating too */
    39     39     int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
    40     40                            ** an expression for the i-th column of the table.
    41     41                            ** aXRef[i]==-1 if the i-th column is not changed. */
    42     42     int openOp;            /* Opcode used to open tables */
           43  +  int chngRecno;         /* True if the record number is being changed */
           44  +  Expr *pRecnoExpr;      /* Expression defining the new record number */
    43     45   
    44     46     if( pParse->nErr || sqlite_malloc_failed ) goto update_cleanup;
    45     47     db = pParse->db;
    46     48   
    47     49     /* Locate the table which we want to update.  This table has to be
    48     50     ** put in an IdList structure because some of the subroutines we
    49     51     ** will be calling are designed to work with multiple tables and expect
................................................................................
    85     87       if( sqliteExprResolveIds(pParse, pTabList, pWhere) ){
    86     88         goto update_cleanup;
    87     89       }
    88     90       if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
    89     91         goto update_cleanup;
    90     92       }
    91     93     }
           94  +  chngRecno = 0;
    92     95     for(i=0; i<pChanges->nExpr; i++){
    93     96       if( sqliteExprResolveIds(pParse, pTabList, pChanges->a[i].pExpr) ){
    94     97         goto update_cleanup;
    95     98       }
    96     99       if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){
    97    100         goto update_cleanup;
    98    101       }
    99    102       for(j=0; j<pTab->nCol; j++){
   100    103         if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
          104  +        if( i==pTab->iPKey ){
          105  +          chngRecno = 1;
          106  +          pRecnoExpr = pChanges->a[i].pExpr;
          107  +        }
   101    108           aXRef[j] = i;
   102    109           break;
   103    110         }
   104    111       }
   105    112       if( j>=pTab->nCol ){
   106    113         sqliteSetString(&pParse->zErrMsg, "no such column: ", 
   107    114            pChanges->a[i].zName, 0);
................................................................................
   111    118     }
   112    119   
   113    120     /* Allocate memory for the array apIdx[] and fill it with pointers to every
   114    121     ** index that needs to be updated.  Indices only need updating if their
   115    122     ** key includes one of the columns named in pChanges.
   116    123     */
   117    124     for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   118         -    for(i=0; i<pIdx->nColumn; i++){
   119         -      if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
          125  +    if( chngRecno ){
          126  +      i = 0;
          127  +    }else {
          128  +      for(i=0; i<pIdx->nColumn; i++){
          129  +        if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
          130  +      }
   120    131       }
   121    132       if( i<pIdx->nColumn ) nIdx++;
   122    133     }
   123    134     if( nIdx>0 ){
   124    135       apIdx = sqliteMalloc( sizeof(Index*) * nIdx );
   125    136       if( apIdx==0 ) goto update_cleanup;
   126    137     }
   127    138     for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   128         -    for(i=0; i<pIdx->nColumn; i++){
   129         -      if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
          139  +    if( chngRecno ){
          140  +      i = 0;
          141  +    }else{
          142  +      for(i=0; i<pIdx->nColumn; i++){
          143  +        if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
          144  +      }
   130    145       }
   131    146       if( i<pIdx->nColumn ) apIdx[nIdx++] = pIdx;
   132    147     }
   133    148   
   134    149     /* Begin generating code.
   135    150     */
   136    151     v = sqliteGetVdbe(pParse);
................................................................................
   171    186       sqliteVdbeAddOp(v, openOp, base+i+1, apIdx[i]->tnum);
   172    187     }
   173    188   
   174    189     /* Loop over every record that needs updating.  We have to load
   175    190     ** the old data for each record to be updated because some columns
   176    191     ** might not change and we will need to copy the old value.
   177    192     ** Also, the old data is needed to delete the old index entires.
          193  +  ** So make the cursor point at the old record.
   178    194     */
   179    195     end = sqliteVdbeMakeLabel(v);
   180    196     addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
   181    197     sqliteVdbeAddOp(v, OP_Dup, 0, 0);
   182    198     sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
   183    199   
   184    200     /* Delete the old indices for the current record.
................................................................................
   188    204       pIdx = apIdx[i];
   189    205       for(j=0; j<pIdx->nColumn; j++){
   190    206         sqliteVdbeAddOp(v, OP_Column, base, pIdx->aiColumn[j]);
   191    207       }
   192    208       sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
   193    209       sqliteVdbeAddOp(v, OP_IdxDelete, base+i+1, 0);
   194    210     }
          211  +
          212  +  /* If changing the record number, remove the old record number
          213  +  ** from the top of the stack and replace it with the new one.
          214  +  */
          215  +  if( chngRecno ){
          216  +    sqliteVdbeAddOp(v, OP_Pop, 1, 0);
          217  +    sqliteExprCode(pParse, pRecnoExpr);
          218  +    sqliteVdbeAddOp(v, OP_AddImm, 0, 0);
          219  +  }
   195    220   
   196    221     /* Compute new data for this record.  
   197    222     */
   198    223     for(i=0; i<pTab->nCol; i++){
          224  +    if( i==pTab->iPKey ){
          225  +      sqliteVdbeAddOp(v, OP_Dup, i, 0);
          226  +      continue;
          227  +    }
   199    228       j = aXRef[i];
   200    229       if( j<0 ){
   201    230         sqliteVdbeAddOp(v, OP_Column, base, i);
   202    231       }else{
   203    232         sqliteExprCode(pParse, pChanges->a[j].pExpr);
   204    233       }
   205    234     }
          235  +
          236  +  /* If changing the record number, delete the hold record.
          237  +  */
          238  +  if( chngRecno ){
          239  +    sqliteVdbeAddOp(v, OP_Delete, 0, 0);
          240  +  }
   206    241   
   207    242     /* Insert new index entries that correspond to the new data
   208    243     */
   209    244     for(i=0; i<nIdx; i++){
   210    245       sqliteVdbeAddOp(v, OP_Dup, pTab->nCol, 0); /* The KEY */
   211    246       pIdx = apIdx[i];
   212    247       for(j=0; j<pIdx->nColumn; j++){
   213         -      sqliteVdbeAddOp(v, OP_Dup, j+pTab->nCol-pIdx->aiColumn[j], 0);
          248  +      int idx = pIdx->aiColumn[j];
          249  +      if( idx==pTab->iPKey ){
          250  +        sqliteVdbeAddOp(v, OP_Dup, j, 0);
          251  +      }else{
          252  +        sqliteVdbeAddOp(v, OP_Dup, j+pTab->nCol-idx, 0);
          253  +      }
   214    254       }
   215    255       sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
   216    256       sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, pIdx->isUnique);
   217    257     }
   218    258   
   219    259     /* Write the new data back into the database.
   220    260     */

Changes to src/vdbe.c.

    26     26   ** type to the other occurs as necessary.
    27     27   ** 
    28     28   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    29     29   ** function which does the work of interpreting a VDBE program.
    30     30   ** But other routines are also provided to help in building up
    31     31   ** a program instruction by instruction.
    32     32   **
    33         -** $Id: vdbe.c,v 1.100 2001/11/13 19:35:15 drh Exp $
           33  +** $Id: vdbe.c,v 1.101 2001/12/21 14:30:43 drh Exp $
    34     34   */
    35     35   #include "sqliteInt.h"
    36     36   #include <ctype.h>
    37     37   
    38     38   /*
    39     39   ** The following global variable is incremented every time a cursor
    40     40   ** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
  1650   1650     aStack[nos].i = a;
  1651   1651     aStack[nos].flags = STK_Int;
  1652   1652     break;
  1653   1653   }
  1654   1654   
  1655   1655   /* Opcode: AddImm  P1 * *
  1656   1656   ** 
  1657         -** Add the value P1 to whatever is on top of the stack.
         1657  +** Add the value P1 to whatever is on top of the stack.  The result
         1658  +** is always an integer.
         1659  +**
         1660  +** To force the top of the stack to be an integer, just add 0.
  1658   1661   */
  1659   1662   case OP_AddImm: {
  1660   1663     int tos = p->tos;
  1661   1664     VERIFY( if( tos<0 ) goto not_enough_stack; )
  1662   1665     Integerify(p, tos);
  1663   1666     aStack[tos].i += pOp->p1;
  1664   1667     break;
................................................................................
  2265   2268       sqliteBtreeRollback(db->pBeTemp);
  2266   2269     }
  2267   2270     rc = sqliteBtreeRollback(pBt);
  2268   2271     sqliteRollbackInternalChanges(db);
  2269   2272     break;
  2270   2273   }
  2271   2274   
  2272         -/* Opcode: ReadCookie * * *
         2275  +/* Opcode: ReadCookie * P2 *
  2273   2276   **
  2274         -** Read the schema cookie from the database file and push it onto the
         2277  +** When P2==0, 
         2278  +** read the schema cookie from the database file and push it onto the
  2275   2279   ** stack.  The schema cookie is an integer that is used like a version
  2276   2280   ** number for the database schema.  Everytime the schema changes, the
  2277   2281   ** cookie changes to a new random value.  This opcode is used during
  2278   2282   ** initialization to read the initial cookie value so that subsequent
  2279   2283   ** database accesses can verify that the cookie has not changed.
         2284  +**
         2285  +** If P2>0, then read global database parameter number P2.  There is
         2286  +** a small fixed number of global database parameters.  P2==1 is the
         2287  +** database version number.  Other parameters are currently unused.
  2280   2288   **
  2281   2289   ** There must be a read-lock on the database (either a transaction
  2282   2290   ** must be started or there must be an open cursor) before
  2283   2291   ** executing this instruction.
  2284   2292   */
  2285   2293   case OP_ReadCookie: {
  2286   2294     int i = ++p->tos;
  2287   2295     int aMeta[SQLITE_N_BTREE_META];
         2296  +  assert( pOp->p2<SQLITE_N_BTREE_META );
  2288   2297     VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  2289   2298     rc = sqliteBtreeGetMeta(pBt, aMeta);
  2290         -  aStack[i].i = aMeta[1];
         2299  +  aStack[i].i = aMeta[1+pOp->p2];
  2291   2300     aStack[i].flags = STK_Int;
  2292   2301     break;
  2293   2302   }
  2294   2303   
  2295         -/* Opcode: SetCookie P1 * *
         2304  +/* Opcode: SetCookie P1 P2 *
  2296   2305   **
  2297         -** This operation changes the value of the schema cookie on the database.
  2298         -** The new value is P1.
         2306  +** When P2==0,
         2307  +** this operation changes the value of the schema cookie on the database.
         2308  +** The new value is P1.  When P2>0, the value of global database parameter
         2309  +** number P2 is changed.  See ReadCookie for more information about
         2310  +** global database parametes.
  2299   2311   **
  2300   2312   ** The schema cookie changes its value whenever the database schema changes.
  2301   2313   ** That way, other processes can recognize when the schema has changed
  2302   2314   ** and reread it.
  2303   2315   **
  2304   2316   ** A transaction must be started before executing this opcode.
  2305   2317   */
  2306   2318   case OP_SetCookie: {
  2307   2319     int aMeta[SQLITE_N_BTREE_META];
         2320  +  assert( pOp->p2<SQLITE_N_BTREE_META );
  2308   2321     rc = sqliteBtreeGetMeta(pBt, aMeta);
  2309   2322     if( rc==SQLITE_OK ){
  2310         -    aMeta[1] = pOp->p1;
         2323  +    aMeta[1+pOp->p2] = pOp->p1;
  2311   2324       rc = sqliteBtreeUpdateMeta(pBt, aMeta);
  2312   2325     }
  2313   2326     break;
  2314   2327   }
  2315   2328   
  2316         -/* Opcode: VerifyCookie P1 * *
         2329  +/* Opcode: VerifyCookie P1 P2 *
  2317   2330   **
  2318         -** Check the current value of the schema cookie and make sure it is
  2319         -** equal to P1.  If it is not, abort with an SQLITE_SCHEMA error.
         2331  +** Check the value of global database parameter number P2 and make
         2332  +** sure it is equal to P1.  P2==0 is the schema cookie.  P1==1 is
         2333  +** the database version.  If the values do not match, abort with
         2334  +** an SQLITE_SCHEMA error.
  2320   2335   **
  2321   2336   ** The cookie changes its value whenever the database schema changes.
  2322   2337   ** This operation is used to detect when that the cookie has changed
  2323   2338   ** and that the current process needs to reread the schema.
  2324   2339   **
  2325   2340   ** Either a transaction needs to have been started or an OP_Open needs
  2326   2341   ** to be executed (to establish a read lock) before this opcode is
  2327   2342   ** invoked.
  2328   2343   */
  2329   2344   case OP_VerifyCookie: {
  2330   2345     int aMeta[SQLITE_N_BTREE_META];
         2346  +  assert( pOp->p2<SQLITE_N_BTREE_META );
  2331   2347     rc = sqliteBtreeGetMeta(pBt, aMeta);
  2332         -  if( rc==SQLITE_OK && aMeta[1]!=pOp->p1 ){
         2348  +  if( rc==SQLITE_OK && aMeta[1+pOp->p2]!=pOp->p1 ){
  2333   2349       sqliteSetString(pzErrMsg, "database schema has changed", 0);
  2334   2350       rc = SQLITE_SCHEMA;
  2335   2351     }
  2336   2352     break;
  2337   2353   }
  2338   2354   
  2339   2355   /* Opcode: Open P1 P2 P3
................................................................................
  2609   2625     break;
  2610   2626   }
  2611   2627   
  2612   2628   /* Opcode: NewRecno P1 * *
  2613   2629   **
  2614   2630   ** Get a new integer record number used as the key to a table.
  2615   2631   ** The record number is not previously used as a key in the database
  2616         -** table that cursor P1 points to.  The new record number pushed 
         2632  +** table that cursor P1 points to.  The new record number is pushed 
  2617   2633   ** onto the stack.
  2618   2634   */
  2619   2635   case OP_NewRecno: {
  2620   2636     int i = pOp->p1;
  2621   2637     int v = 0;
  2622   2638     Cursor *pC;
  2623   2639     if( VERIFY( i<0 || i>=p->nCursor || ) (pC = &p->aCsr[i])->pCursor==0 ){
................................................................................
  2662   2678     VERIFY( NeedStack(p, p->tos+1); )
  2663   2679     p->tos++;
  2664   2680     aStack[p->tos].i = v;
  2665   2681     aStack[p->tos].flags = STK_Int;
  2666   2682     break;
  2667   2683   }
  2668   2684   
  2669         -/* Opcode: Put P1 * *
         2685  +/* Opcode: Put P1 P2 *
  2670   2686   **
  2671   2687   ** Write an entry into the database file P1.  A new entry is
  2672   2688   ** created if it doesn't already exist or the data for an existing
  2673   2689   ** entry is overwritten.  The data is the value on the top of the
  2674   2690   ** stack.  The key is the next value down on the stack.  The stack
  2675   2691   ** is popped twice by this instruction.
         2692  +**
         2693  +** If P2==1 then overwriting is prohibited.  If a prior entry with
         2694  +** the same key exists, an SQLITE_CONSTRAINT exception is raised.
  2676   2695   */
  2677   2696   case OP_Put: {
  2678   2697     int tos = p->tos;
  2679   2698     int nos = p->tos-1;
  2680   2699     int i = pOp->p1;
  2681   2700     VERIFY( if( nos<0 ) goto not_enough_stack; )
  2682   2701     if( VERIFY( i>=0 && i<p->nCursor && ) p->aCsr[i].pCursor!=0 ){
................................................................................
  2686   2705         if( Stringify(p, nos) ) goto no_mem;
  2687   2706         nKey = aStack[nos].n;
  2688   2707         zKey = zStack[nos];
  2689   2708       }else{
  2690   2709         nKey = sizeof(int);
  2691   2710         iKey = bigEndian(aStack[nos].i);
  2692   2711         zKey = (char*)&iKey;
         2712  +    }
         2713  +    if( pOp->p2 ){
         2714  +      int res;
         2715  +      rc = sqliteBtreeMoveto(p->aCsr[i].pCursor, zKey, nKey, &res);
         2716  +      if( res==0 && rc==SQLITE_OK ){
         2717  +        rc = SQLITE_CONSTRAINT;
         2718  +      }
         2719  +      if( rc!=SQLITE_OK ){
         2720  +        goto abort_due_to_error;
         2721  +      }
  2693   2722       }
  2694   2723       rc = sqliteBtreeInsert(p->aCsr[i].pCursor, zKey, nKey,
  2695   2724                           zStack[tos], aStack[tos].n);
  2696   2725     }
  2697   2726     POPSTACK;
  2698   2727     POPSTACK;
  2699   2728     break;

Added test/intpkey.test.

            1  +# 2001 September 15
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests for the special processing associated
           14  +# with INTEGER PRIMARY KEY columns.
           15  +#
           16  +# $Id: intpkey.test,v 1.1 2001/12/21 14:30:44 drh Exp $
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +# Create a table with a primary key and a datatype other than
           22  +# integer
           23  +#
           24  +do_test intpkey-1.0 {
           25  +  execsql {
           26  +    CREATE TABLE t1(a TEXT PRIMARY KEY, b, c);
           27  +  }
           28  +} {}
           29  +
           30  +# There should be an index associated with the primary key
           31  +#
           32  +do_test intpkey-1.1 {
           33  +  execsql {
           34  +    SELECT name FROM sqlite_master
           35  +    WHERE type='index' AND tbl_name='t1';
           36  +  }
           37  +} {{(t1 autoindex 1)}}
           38  +
           39  +# Now create a table with an integer primary key and verify that
           40  +# there is no associated index.
           41  +#
           42  +do_test intpkey-1.2 {
           43  +  execsql {
           44  +    DROP TABLE t1;
           45  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           46  +    SELECT name FROM sqlite_master
           47  +      WHERE type='index' AND tbl_name='t1';
           48  +  }
           49  +} {}
           50  +
           51  +# Insert some records into the new table.  Specify the primary key
           52  +# and verify that the key is used as the record number.
           53  +#
           54  +do_test intpkey-1.3 {
           55  +  execsql {
           56  +    INSERT INTO t1 VALUES(5,'hello','world');
           57  +  }
           58  +} {}
           59  +do_test intpkey-1.4 {
           60  +  execsql {
           61  +    SELECT * FROM t1;
           62  +  }
           63  +} {5 hello world}
           64  +do_test intpkey-1.5 {
           65  +  execsql {
           66  +    SELECT rowid, * FROM t1;
           67  +  }
           68  +} {5 5 hello world}
           69  +
           70  +# Attempting to insert a duplicate primary key should give a constraint
           71  +# failure.
           72  +#
           73  +do_test intpkey-1.6 {
           74  +  set r [catch {execsql {
           75  +     INSERT INTO t1 VALUES(5,'second','entry');
           76  +  }} msg]
           77  +  lappend r $msg
           78  +} {1 {constraint failed}}
           79  +do_test intpkey-1.7 {
           80  +  execsql {
           81  +    SELECT rowid, * FROM t1;
           82  +  }
           83  +} {5 5 hello world}
           84  +do_test intpkey-1.8 {
           85  +  set r [catch {execsql {
           86  +     INSERT INTO t1 VALUES(6,'second','entry');
           87  +  }} msg]
           88  +  lappend r $msg
           89  +} {0 {}}
           90  +do_test intpkey-1.9 {
           91  +  execsql {
           92  +    SELECT rowid, * FROM t1;
           93  +  }
           94  +} {5 5 hello world 6 6 second entry}
           95  +
           96  +# A ROWID is automatically generated for new records that do not specify
           97  +# the integer primary key.
           98  +#
           99  +do_test intpkey-1.10 {
          100  +  execsql {
          101  +    INSERT INTO t1(b,c) VALUES('one','two');
          102  +    SELECT b FROM t1 ORDER BY b;
          103  +  }
          104  +} {hello one second}
          105  +
          106  +# Try to change the ROWID for the new entry.
          107  +#
          108  +do_test intpkey-1.11 {
          109  +  execsql {
          110  +    UPDATE t1 SET a=7 WHERE b='one';
          111  +    SELECT * FROM t1;
          112  +  }
          113  +} {5 hello world 6 second entry 7 one two}
          114  +
          115  +# Make sure SELECT statements are able to use the primary key column
          116  +# as an index.
          117  +#
          118  +do_test intpkey-1.12 {
          119  +  execsql {
          120  +    SELECT * FROM t1 WHERE a==7;
          121  +  }
          122  +} {7 one two}
          123  +
          124  +
          125  +finish_test

Changes to test/unique.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the CREATE UNIQUE INDEX statement,
    13     13   # and primary keys, and the UNIQUE constraint on table columns
    14     14   #
    15         -# $Id: unique.test,v 1.2 2001/09/27 23:57:06 drh Exp $
           15  +# $Id: unique.test,v 1.3 2001/12/21 14:30:44 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Try to create a table with two primary keys.
    21     21   # (This is allowed in SQLite even that it is not valid SQL)
    22     22   #
................................................................................
    23     23   do_test unique-1.1 {
    24     24     catchsql {
    25     25       CREATE TABLE t1(
    26     26          a int PRIMARY KEY,
    27     27          b int PRIMARY KEY,
    28     28          c text
    29     29       );
           30  +  }
           31  +} {1 {table "t1" has more than one primary key}}
           32  +do_test unique-1.1b {
           33  +  catchsql {
           34  +    CREATE TABLE t1(
           35  +       a int PRIMARY KEY,
           36  +       b int UNIQUE,
           37  +       c text
           38  +    );
    30     39     }
    31     40   } {0 {}}
    32     41   do_test unique-1.2 {
    33     42     catchsql {
    34     43       INSERT INTO t1(a,b,c) VALUES(1,2,3)
    35     44     }
    36     45   } {0 {}}