/ Check-in [47d8ebda]
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:Use sqlite3NestedParse to implement CREATE INDEX. (CVS 2070)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 47d8ebdaaddcb7a05e1917dd1dee2029c34228a4
User & Date: drh 2004-11-05 20:58:40
Context
2004-11-05
22:18
Use sqlite3NestedParse to implement DROP INDEX. (CVS 2071) check-in: 0f81aa5b user: drh tags: trunk
20:58
Use sqlite3NestedParse to implement CREATE INDEX. (CVS 2070) check-in: 47d8ebda user: drh tags: trunk
17:17
Create table now works with sqlite3NestedParse. This changed uncovered a latent bug in xprintf which is also fixed. (CVS 2069) check-in: b0506bdd user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
....
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
....
1993
1994
1995
1996
1997
1998
1999












































2000
2001
2002
2003
2004
2005
2006
....
2273
2274
2275
2276
2277
2278
2279
2280
2281

2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293



2294
2295
2296
2297
2298
2299
2300
2301



2302


2303
2304


2305
2306


2307
2308



2309
2310





2311

2312
2313
2314


2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
<<<<<<< build.c
** $Id: build.c,v 1.268 2004/11/05 17:17:50 drh Exp $
=======
** $Id: build.c,v 1.268 2004/11/05 17:17:50 drh Exp $
>>>>>>> 1.262
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
................................................................................
        p->aCol = pSelTab->aCol;
        pSelTab->nCol = 0;
        pSelTab->aCol = 0;
        sqlite3DeleteTable(0, pSelTab);
      }
    }

#if 0  
    sqlite3OpenMasterTable(v, p->iDb);

    sqlite3VdbeOp3(v, OP_String8, 0, 0, p->pSelect==0?"table":"view",P3_STATIC);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, p->zName, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, p->zName, 0);
    sqlite3VdbeAddOp(v, OP_Pull, 3, 0);

    if( pSelect ){
      char *z = createTableStmt(p);
      n = z ? strlen(z) : 0;
      sqlite3VdbeAddOp(v, OP_String8, 0, 0);
      sqlite3VdbeChangeP3(v, -1, z, n);
      sqliteFree(z);
    }else{
      if( p->pSelect ){
        sqlite3VdbeOp3(v, OP_String8, 0, 0, "CREATE VIEW ", P3_STATIC);
      }else{
        sqlite3VdbeOp3(v, OP_String8, 0, 0, "CREATE TABLE ", P3_STATIC);
      }
      assert( pEnd!=0 );
      n = Addr(pEnd->z) - Addr(pParse->sNameToken.z) + 1;
      sqlite3VdbeAddOp(v, OP_String8, 0, 0);
      sqlite3VdbeChangeP3(v, -1, pParse->sNameToken.z, n);
      sqlite3VdbeAddOp(v, OP_Concat, 0, 0);
    }
    sqlite3VdbeOp3(v, OP_MakeRecord, 5, 0, "tttit", P3_STATIC);
    sqlite3VdbeAddOp(v, OP_PutIntKey, 0, 0);
    sqlite3ChangeCookie(db, v, p->iDb);
    sqlite3VdbeAddOp(v, OP_Close, 0, 0);
#endif

    /* Compute the complete text of the CREATE statement */
    if( pSelect ){
      zStmt = createTableStmt(p);
    }else{
      n = Addr(pEnd->z) - Addr(pParse->sNameToken.z) + 1;
      zStmt = sqlite3MPrintf("CREATE %s %.*s", zType2, n, pParse->sNameToken.z);
    }
................................................................................
#ifndef SQLITE_OMIT_FOREIGN_KEY
  Table *pTab;
  FKey *pFKey;
  if( (pTab = pParse->pNewTable)==0 || (pFKey = pTab->pFKey)==0 ) return;
  pFKey->isDeferred = isDeferred;
#endif
}













































/*
** Create a new index for an SQL table.  pIndex is the name of the index 
** and pTable is the name of the table that is to be indexed.  Both will 
** be NULL for a primary key or an index that is created to satisfy a
** UNIQUE constraint.  If pTable and pIndex are NULL, use pParse->pNewTable
** as the table to be indexed.  pParse->pNewTable is a table that is
................................................................................
  **
  ** If pTblName==0 it means this index is generated as a primary key
  ** or UNIQUE constraint of a CREATE TABLE statement.  Since the table
  ** has just been created, it contains no data and the index initialization
  ** step can be skipped.
  */
  else if( db->init.busy==0 ){
    int n;
    Vdbe *v;

    int lbl1, lbl2;

    v = sqlite3GetVdbe(pParse);
    if( v==0 ) goto exit_create_index;
    if( pTblName!=0 ){
      sqlite3BeginWriteOperation(pParse, 0, iDb);
      sqlite3OpenMasterTable(v, iDb);
    }
    sqlite3VdbeAddOp(v, OP_NewRecno, 0, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, "index", P3_STATIC);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, pIndex->zName, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);



    sqlite3VdbeAddOp(v, OP_CreateIndex, iDb, 0);
    if( pTblName ){
      sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
      sqlite3VdbeAddOp(v, OP_Integer, iDb, 0);
      sqlite3VdbeOp3(v, OP_OpenWrite, 1, 0,
                     (char*)&pIndex->keyInfo, P3_KEYINFO);
    }
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);



    if( pStart && pEnd ){


      if( onError==OE_None ){
        sqlite3VdbeChangeP3(v, -1, "CREATE INDEX ", P3_STATIC);


      }else{
        sqlite3VdbeChangeP3(v, -1, "CREATE UNIQUE INDEX ", P3_STATIC);


      }
      sqlite3VdbeAddOp(v, OP_String8, 0, 0);



      n = Addr(pEnd->z) - Addr(pName->z) + 1;
      sqlite3VdbeChangeP3(v, -1, pName->z, n);





      sqlite3VdbeAddOp(v, OP_Concat, 0, 0);

    }
    sqlite3VdbeOp3(v, OP_MakeRecord, 5, 0, "tttit", P3_STATIC);
    sqlite3VdbeAddOp(v, OP_PutIntKey, 0, 0);


    if( pTblName ){
      sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0);
      sqlite3VdbeAddOp(v, OP_OpenRead, 2, pTab->tnum);
      /* VdbeComment((v, "%s", pTab->zName)); */
      sqlite3VdbeAddOp(v, OP_SetNumColumns, 2, pTab->nCol);
      lbl2 = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp(v, OP_Rewind, 2, lbl2);
      lbl1 = sqlite3VdbeCurrentAddr(v);
      sqlite3GenerateIndexKey(v, pIndex, 2);
      sqlite3VdbeOp3(v, OP_IdxPut, 1, pIndex->onError!=OE_None,
                      "indexed columns are not unique", P3_STATIC);
      sqlite3VdbeAddOp(v, OP_Next, 2, lbl1);
      sqlite3VdbeResolveLabel(v, lbl2);
      sqlite3VdbeAddOp(v, OP_Close, 2, 0);
      sqlite3VdbeAddOp(v, OP_Close, 1, 0);
      sqlite3ChangeCookie(db, v, iDb);
      sqlite3VdbeAddOp(v, OP_Close, 0, 0);
      sqlite3VdbeOp3(v, OP_ParseSchema, iDb, 0,
         sqlite3MPrintf("name='%q'", pIndex->zName), P3_DYNAMIC);
    }
  }

  /* When adding an index to the list of indices for a table, make
  ** sure all indices labeled OE_Replace come after all those labeled







|

|







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







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







 







<

>
|



<
<
<
|
<
<
<
<
>
>
>

<
|
<
<
<
|
<
>
>
>

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

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

<







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
....
1416
1417
1418
1419
1420
1421
1422
































1423
1424
1425
1426
1427
1428
1429
....
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
....
2285
2286
2287
2288
2289
2290
2291

2292
2293
2294
2295
2296
2297



2298




2299
2300
2301
2302

2303



2304

2305
2306
2307
2308
2309
2310
2311

2312
2313
2314

2315
2316
2317

2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330


2331
2332
2333






2334







2335

2336
2337
2338
2339
2340
2341
2342
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
<<<<<<< build.c
** $Id: build.c,v 1.269 2004/11/05 20:58:40 drh Exp $
=======
** $Id: build.c,v 1.269 2004/11/05 20:58:40 drh Exp $
>>>>>>> 1.262
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
................................................................................
        p->aCol = pSelTab->aCol;
        pSelTab->nCol = 0;
        pSelTab->aCol = 0;
        sqlite3DeleteTable(0, pSelTab);
      }
    }

































    /* Compute the complete text of the CREATE statement */
    if( pSelect ){
      zStmt = createTableStmt(p);
    }else{
      n = Addr(pEnd->z) - Addr(pParse->sNameToken.z) + 1;
      zStmt = sqlite3MPrintf("CREATE %s %.*s", zType2, n, pParse->sNameToken.z);
    }
................................................................................
#ifndef SQLITE_OMIT_FOREIGN_KEY
  Table *pTab;
  FKey *pFKey;
  if( (pTab = pParse->pNewTable)==0 || (pFKey = pTab->pFKey)==0 ) return;
  pFKey->isDeferred = isDeferred;
#endif
}

/*
** Generate code that will erase and refill index *pIdx.  This is
** used to initialize a newly created index or to recompute the
** content of an index in response to a REINDEX command.
**
** if memRootPage is not negative, it means that the index is newly
** created.  The memory cell specified by memRootPage contains the
** root page number of the index.  If memRootPage is negative, then
** the index already exists and must be cleared before being refilled and
** the root page number of the index is taken from pIndex->tnum.
*/
static void sqlite3RefillIndex(Parse *pParse, Index *pIndex, int memRootPage){
  Table *pTab = pIndex->pTable;  /* The table that is indexed */
  int iTab = pParse->nTab;       /* Btree cursor used for pTab */
  int iIdx = pParse->nTab+1;     /* Btree cursor used for pIndex */
  int addr1;                     /* Address of top of loop */
  int tnum;                      /* Root page of index */
  Vdbe *v;                       /* Generate code into this virtual machine */

  v = sqlite3GetVdbe(pParse);
  if( v==0 ) return;
  if( memRootPage>=0 ){
    sqlite3VdbeAddOp(v, OP_MemLoad, memRootPage, 0);
    tnum = 0;
  }else{
    tnum = pIndex->tnum;
    sqlite3VdbeAddOp(v, OP_Clear, tnum, pIndex->iDb);
  }
  sqlite3VdbeAddOp(v, OP_Integer, pIndex->iDb, 0);
  sqlite3VdbeOp3(v, OP_OpenWrite, iIdx, tnum,
                    (char*)&pIndex->keyInfo, P3_KEYINFO);
  sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0);
  sqlite3VdbeAddOp(v, OP_OpenRead, iTab, pTab->tnum);
  sqlite3VdbeAddOp(v, OP_SetNumColumns, iTab, pTab->nCol);
  addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iTab, 0);
  sqlite3GenerateIndexKey(v, pIndex, iTab);
  sqlite3VdbeOp3(v, OP_IdxPut, iIdx, pIndex->onError!=OE_None,
                      "indexed columns are not unique", P3_STATIC);
  sqlite3VdbeAddOp(v, OP_Next, iTab, addr1+1);
  sqlite3VdbeChangeP2(v, addr1, sqlite3VdbeCurrentAddr(v));
  sqlite3VdbeAddOp(v, OP_Close, iTab, 0);
  sqlite3VdbeAddOp(v, OP_Close, iIdx, 0);
}

/*
** Create a new index for an SQL table.  pIndex is the name of the index 
** and pTable is the name of the table that is to be indexed.  Both will 
** be NULL for a primary key or an index that is created to satisfy a
** UNIQUE constraint.  If pTable and pIndex are NULL, use pParse->pNewTable
** as the table to be indexed.  pParse->pNewTable is a table that is
................................................................................
  **
  ** If pTblName==0 it means this index is generated as a primary key
  ** or UNIQUE constraint of a CREATE TABLE statement.  Since the table
  ** has just been created, it contains no data and the index initialization
  ** step can be skipped.
  */
  else if( db->init.busy==0 ){

    Vdbe *v;
    char *zStmt;
    int iMem = pParse->nMem++;

    v = sqlite3GetVdbe(pParse);
    if( v==0 ) goto exit_create_index;








    /* Create the rootpage for the index
    */
    sqlite3BeginWriteOperation(pParse, 0, iDb);
    sqlite3VdbeAddOp(v, OP_CreateIndex, iDb, 0);

    sqlite3VdbeAddOp(v, OP_MemStore, iMem, 0);





    /* Gather the complete text of the CREATE INDEX statement into
    ** the zStmt variable
    */
    if( pStart && pEnd ){
      /* A named index with an explicit CREATE INDEX statement */
      zStmt = sqlite3MPrintf("CREATE%s INDEX %.*q",
        onError==OE_None ? "" : " UNIQUE",

        Addr(pEnd->z) - Addr(pName->z) + 1,
        pName->z);
    }else{

      /* An automatic index created by a PRIMARY KEY or UNIQUE constraint */
      zStmt = sqlite3MPrintf("");
    }


    /* Add an entry in sqlite_master for this index
    */
    sqlite3NestedParse(pParse, 
        "INSERT INTO %Q.%s VALUES('index',%Q,%Q,#0,'%s');",
        db->aDb[iDb].zName, SCHEMA_TABLE(iDb),
        pIndex->zName,
        pTab->zName,
        zStmt
    );
    sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
    sqliteFree(zStmt);



    /* Fill the index with data and reparse the schema
    */
    if( pTblName ){






      sqlite3RefillIndex(pParse, pIndex, iMem);







      sqlite3ChangeCookie(db, v, iDb);

      sqlite3VdbeOp3(v, OP_ParseSchema, iDb, 0,
         sqlite3MPrintf("name='%q'", pIndex->zName), P3_DYNAMIC);
    }
  }

  /* When adding an index to the list of indices for a table, make
  ** sure all indices labeled OE_Replace come after all those labeled