SQLite

Check-in [8e5c2e5df8]
Login

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

Overview
Comment:Ensure tables cannot be created/dropped when btree cursors are open. (CVS 2085)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8e5c2e5df8b824f7efb27e776240f005c6f1f0ff
User & Date: danielk1977 2004-11-10 11:55:11.000
Context
2004-11-10
12:34
Fix expr.test so that it works when the date-time functions are compiled out of the library. (CVS 2086) (check-in: 540ce7de1b user: danielk1977 tags: trunk)
11:55
Ensure tables cannot be created/dropped when btree cursors are open. (CVS 2085) (check-in: 8e5c2e5df8 user: danielk1977 tags: trunk)
05:48
Add user documentation for the "pragma auto_vacuum" command. (CVS 2084) (check-in: fe200eaf37 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/btree.c.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
** 2004 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** $Id: btree.c,v 1.215 2004/11/08 09:26:09 danielk1977 Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.











|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
** 2004 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** $Id: btree.c,v 1.216 2004/11/10 11:55:11 danielk1977 Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
4300
4301
4302
4303
4304
4305
4306





4307
4308
4309
4310
4311

4312
4313
4314
4315
4316
4317
4318
    return SQLITE_PERM;   /* Did not open this cursor for writing */
  }
  if( checkReadLocks(pBt, pCur->pgnoRoot, pCur) ){
    return SQLITE_LOCKED; /* The table pCur points to has a read lock */
  }
  rc = sqlite3pager_write(pPage->aData);
  if( rc ) return rc;





  pCell = findCell(pPage, pCur->idx);
  if( !pPage->leaf ){
    pgnoChild = get4byte(pCell);
  }
  clearCell(pPage, pCell);

  if( !pPage->leaf ){
    /*
    ** The entry we are about to delete is not a leaf so if we do not
    ** do something we will leave a hole on an internal page.
    ** We have to fill the hole by moving in a cell from a leaf.  The
    ** next Cell after the one to be deleted is guaranteed to exist and
    ** to be a leaf so we can use it.







>
>
>
>
>





>







4300
4301
4302
4303
4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
4321
4322
4323
4324
    return SQLITE_PERM;   /* Did not open this cursor for writing */
  }
  if( checkReadLocks(pBt, pCur->pgnoRoot, pCur) ){
    return SQLITE_LOCKED; /* The table pCur points to has a read lock */
  }
  rc = sqlite3pager_write(pPage->aData);
  if( rc ) return rc;

  /* Locate the cell within it's page and leave pCell pointing to the
  ** data. The clearCell() call frees any overflow pages associated with the
  ** cell. The cell itself is still intact.
  */
  pCell = findCell(pPage, pCur->idx);
  if( !pPage->leaf ){
    pgnoChild = get4byte(pCell);
  }
  clearCell(pPage, pCell);

  if( !pPage->leaf ){
    /*
    ** The entry we are about to delete is not a leaf so if we do not
    ** do something we will leave a hole on an internal page.
    ** We have to fill the hole by moving in a cell from a leaf.  The
    ** next Cell after the one to be deleted is guaranteed to exist and
    ** to be a leaf so we can use it.
4371
4372
4373
4374
4375
4376
4377
4378
4379
4380
4381
4382
4383
4384
4385










4386
4387
4388
4389
4390
4391
4392
**     BTREE_INTKEY|BTREE_LEAFDATA     Used for SQL tables with rowid keys
**     BTREE_ZERODATA                  Used for SQL indices
*/
int sqlite3BtreeCreateTable(Btree *pBt, int *piTable, int flags){
  MemPage *pRoot;
  Pgno pgnoRoot;
  int rc;
/* TODO: Disallow schema modifications if there are open cursors */
  if( pBt->inTrans!=TRANS_WRITE ){
    /* Must start a transaction first */
    return pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR;
  }
  if( pBt->readOnly ){
    return SQLITE_READONLY;
  }










#ifdef SQLITE_OMIT_AUTOVACUUM
  rc = allocatePage(pBt, &pRoot, &pgnoRoot, 1, 0);
  if( rc ) return rc;
#else
  if( pBt->autoVacuum ){
    Pgno pgnoMove;      /* Move a page here to make room for the root-page */
    MemPage *pPageMove; /* The page to move to. */







<







>
>
>
>
>
>
>
>
>
>







4377
4378
4379
4380
4381
4382
4383

4384
4385
4386
4387
4388
4389
4390
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402
4403
4404
4405
4406
4407
**     BTREE_INTKEY|BTREE_LEAFDATA     Used for SQL tables with rowid keys
**     BTREE_ZERODATA                  Used for SQL indices
*/
int sqlite3BtreeCreateTable(Btree *pBt, int *piTable, int flags){
  MemPage *pRoot;
  Pgno pgnoRoot;
  int rc;

  if( pBt->inTrans!=TRANS_WRITE ){
    /* Must start a transaction first */
    return pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR;
  }
  if( pBt->readOnly ){
    return SQLITE_READONLY;
  }

  /* It is illegal to create a table if any cursors are open on the
  ** database. This is because in auto-vacuum mode the backend may
  ** need to move a database page to make room for the new root-page.
  ** If an open cursor was using the page a problem would occur.
  */
  if( pBt->pCursor ){
    return SQLITE_LOCKED;
  }

#ifdef SQLITE_OMIT_AUTOVACUUM
  rc = allocatePage(pBt, &pRoot, &pgnoRoot, 1, 0);
  if( rc ) return rc;
#else
  if( pBt->autoVacuum ){
    Pgno pgnoMove;      /* Move a page here to make room for the root-page */
    MemPage *pPageMove; /* The page to move to. */
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574





4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
4585
** the move.  If no page gets moved, *piMoved is set to 0.
** The last root page is recorded in meta[3] and the value of
** meta[3] is updated by this procedure.
*/
int sqlite3BtreeDropTable(Btree *pBt, int iTable, int *piMoved){
  int rc;
  MemPage *pPage = 0;
  BtCursor *pCur;

  if( pBt->inTrans!=TRANS_WRITE ){
    return pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR;
  }

/* TODO: Disallow schema modifications if there are open cursors */





  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( pCur->pgnoRoot==(Pgno)iTable ){
      return SQLITE_LOCKED;  /* Cannot drop a table that has a cursor */
    }
  }

  rc = getPage(pBt, (Pgno)iTable, &pPage);
  if( rc ) return rc;
  rc = sqlite3BtreeClearTable(pBt, iTable);
  if( rc ) return rc;








<





|
>
>
>
>
>
|
<
|
<







4576
4577
4578
4579
4580
4581
4582

4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594

4595

4596
4597
4598
4599
4600
4601
4602
** the move.  If no page gets moved, *piMoved is set to 0.
** The last root page is recorded in meta[3] and the value of
** meta[3] is updated by this procedure.
*/
int sqlite3BtreeDropTable(Btree *pBt, int iTable, int *piMoved){
  int rc;
  MemPage *pPage = 0;


  if( pBt->inTrans!=TRANS_WRITE ){
    return pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR;
  }

  /* It is illegal to drop a table if any cursors are open on the
  ** database. This is because in auto-vacuum mode the backend may
  ** need to move another root-page to fill a gap left by the deleted
  ** root page. If an open cursor was using this page a problem would 
  ** occur.
  */
  if( pBt->pCursor ){

    return SQLITE_LOCKED;

  }

  rc = getPage(pBt, (Pgno)iTable, &pPage);
  if( rc ) return rc;
  rc = sqlite3BtreeClearTable(pBt, iTable);
  if( rc ) return rc;

Changes to src/build.c.
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.274 2004/11/09 12:44:38 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.275 2004/11/10 11:55:12 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
754
755
756
757
758
759
760

761
762
763
764
765
766
767
    ** generate.
    */
    sqlite3OpenMasterTable(v, iDb);
    sqlite3VdbeAddOp(v, OP_NewRecno, 0, 0);
    sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);
    sqlite3VdbeAddOp(v, OP_PutIntKey, 0, 0);

  }
}

/*
** Add a new column to the table currently being constructed.
**
** The parser calls this routine once for each column declaration







>







754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
    ** generate.
    */
    sqlite3OpenMasterTable(v, iDb);
    sqlite3VdbeAddOp(v, OP_NewRecno, 0, 0);
    sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);
    sqlite3VdbeAddOp(v, OP_PutIntKey, 0, 0);
    sqlite3VdbeAddOp(v, OP_Close, 0, 0);
  }
}

/*
** Add a new column to the table currently being constructed.
**
** The parser calls this routine once for each column declaration
1375
1376
1377
1378
1379
1380
1381


1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
    Vdbe *v;
    char *zType;    /* "view" or "table" */
    char *zType2;   /* "VIEW" or "TABLE" */
    char *zStmt;    /* Text of the CREATE TABLE or CREATE VIEW statement */

    v = sqlite3GetVdbe(pParse);
    if( v==0 ) return;



    /* Create the rootpage for the new table and push it onto the stack.
    ** A view has no rootpage, so just push a zero onto the stack for
    ** views.  Initialize zType at the same time.
    */
    if( p->pSelect==0 ){
      /* A regular table */
      sqlite3VdbeAddOp(v, OP_CreateTable, p->iDb, 0);
      zType = "table";
      zType2 = "TABLE";
    }else{
      /* A view */
      sqlite3VdbeAddOp(v, OP_Integer, 0, 0);
      zType = "view";
      zType2 = "VIEW";
    }

    sqlite3VdbeAddOp(v, OP_Close, 0, 0);

    /* If this is a CREATE TABLE xx AS SELECT ..., execute the SELECT
    ** statement to populate the new table. The root-page number for the
    ** new table is on the top of the vdbe stack.
    **
    ** Once the SELECT has been coded by sqlite3Select(), it is in a
    ** suitable state to query for the column names and types to be used
    ** by the new table.







>
>

















<
<







1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401


1402
1403
1404
1405
1406
1407
1408
    Vdbe *v;
    char *zType;    /* "view" or "table" */
    char *zType2;   /* "VIEW" or "TABLE" */
    char *zStmt;    /* Text of the CREATE TABLE or CREATE VIEW statement */

    v = sqlite3GetVdbe(pParse);
    if( v==0 ) return;

    sqlite3VdbeAddOp(v, OP_Close, 0, 0);

    /* Create the rootpage for the new table and push it onto the stack.
    ** A view has no rootpage, so just push a zero onto the stack for
    ** views.  Initialize zType at the same time.
    */
    if( p->pSelect==0 ){
      /* A regular table */
      sqlite3VdbeAddOp(v, OP_CreateTable, p->iDb, 0);
      zType = "table";
      zType2 = "TABLE";
    }else{
      /* A view */
      sqlite3VdbeAddOp(v, OP_Integer, 0, 0);
      zType = "view";
      zType2 = "VIEW";
    }



    /* If this is a CREATE TABLE xx AS SELECT ..., execute the SELECT
    ** statement to populate the new table. The root-page number for the
    ** new table is on the top of the vdbe stack.
    **
    ** Once the SELECT has been coded by sqlite3Select(), it is in a
    ** suitable state to query for the column names and types to be used
    ** by the new table.
Changes to src/date.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This file contains the C functions that implement date and time
** functions for SQLite.  
**
** There is only one exported symbol in this file - the function
** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.39 2004/11/09 16:13:33 danielk1977 Exp $
**
** NOTES:
**
** SQLite processes all times and dates as Julian Day numbers.  The
** dates and times are stored as the number of days since noon
** in Greenwich on November 24, 4714 B.C. according to the Gregorian
** calendar system.







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This file contains the C functions that implement date and time
** functions for SQLite.  
**
** There is only one exported symbol in this file - the function
** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.40 2004/11/10 11:55:12 danielk1977 Exp $
**
** NOTES:
**
** SQLite processes all times and dates as Julian Day numbers.  The
** dates and times are stored as the number of days since noon
** in Greenwich on November 24, 4714 B.C. according to the Gregorian
** calendar system.
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953

954


955
956
957
958
959
960
961
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  time_t t;
  char *zFormat = (char *)sqlite3_user_data(context);
  char zBuf[20];
  struct tm now;

#ifdef SQLITE_TEST
  /* This test variable is located in os_XXX.c */
extern int sqlite3_current_time;
#endif
  time(&t);
#ifdef SQLITE_TEST
  if( sqlite3_current_time ){
    t = sqlite3_current_time;
  }
#endif
  localtime_r(&t, &now);

  strftime(zBuf, 20, zFormat, &now);


  sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
}
#endif

/*
** This function registered all of the above C functions as SQL
** functions.  This should be the only routine in this file with







<











|
>
|
>
>







934
935
936
937
938
939
940

941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  time_t t;
  char *zFormat = (char *)sqlite3_user_data(context);
  char zBuf[20];


#ifdef SQLITE_TEST
  /* This test variable is located in os_XXX.c */
extern int sqlite3_current_time;
#endif
  time(&t);
#ifdef SQLITE_TEST
  if( sqlite3_current_time ){
    t = sqlite3_current_time;
  }
#endif

  sqlite3OsEnterMutex();
  strftime(zBuf, 20, zFormat, gmtime(&t));
  sqlite3OsLeaveMutex();

  sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
}
#endif

/*
** This function registered all of the above C functions as SQL
** functions.  This should be the only routine in this file with
Changes to src/test3.c.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the btree.c module in SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test3.c,v 1.56 2004/11/05 00:43:12 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "btree.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the btree.c module in SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test3.c,v 1.57 2004/11/10 11:55:12 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "btree.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>
40
41
42
43
44
45
46

47
48
49
50
51
52
53
    case SQLITE_IOERR:      zName = "SQLITE_IOERR";       break;
    case SQLITE_CORRUPT:    zName = "SQLITE_CORRUPT";     break;
    case SQLITE_NOTFOUND:   zName = "SQLITE_NOTFOUND";    break;
    case SQLITE_FULL:       zName = "SQLITE_FULL";        break;
    case SQLITE_CANTOPEN:   zName = "SQLITE_CANTOPEN";    break;
    case SQLITE_PROTOCOL:   zName = "SQLITE_PROTOCOL";    break;
    case SQLITE_EMPTY:      zName = "SQLITE_EMPTY";       break;

    default:                zName = "SQLITE_Unknown";     break;
  }
  return zName;
}

/*
** Usage:   btree_open FILENAME NCACHE FLAGS







>







40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
    case SQLITE_IOERR:      zName = "SQLITE_IOERR";       break;
    case SQLITE_CORRUPT:    zName = "SQLITE_CORRUPT";     break;
    case SQLITE_NOTFOUND:   zName = "SQLITE_NOTFOUND";    break;
    case SQLITE_FULL:       zName = "SQLITE_FULL";        break;
    case SQLITE_CANTOPEN:   zName = "SQLITE_CANTOPEN";    break;
    case SQLITE_PROTOCOL:   zName = "SQLITE_PROTOCOL";    break;
    case SQLITE_EMPTY:      zName = "SQLITE_EMPTY";       break;
    case SQLITE_LOCKED:     zName = "SQLITE_LOCKED";      break;
    default:                zName = "SQLITE_Unknown";     break;
  }
  return zName;
}

/*
** Usage:   btree_open FILENAME NCACHE FLAGS
Changes to src/vdbe.c.
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.424 2004/11/05 00:43:12 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.425 2004/11/10 11:55:13 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
3642
3643
3644
3645
3646
3647
3648




3649
3650
3651
3652
3653
3654
3655
3656
3657

3658
3659
3660
3661
3662
3663
3664
** a zero is pushed onto the stack.  If AUTOVACUUM is disabled at 
** then a zero is pushed onto the stack.
**
** See also: Clear
*/
case OP_Destroy: {
  int iMoved;




  rc = sqlite3BtreeDropTable(db->aDb[pOp->p2].pBt, pOp->p1, &iMoved);
  pTos++;
  pTos->flags = MEM_Int;
  pTos->i = iMoved;
#ifndef SQLITE_OMIT_AUTOVACUUM
  if( iMoved!=0 ){
    sqlite3RootPageMoved(&db->aDb[pOp->p2], iMoved, pOp->p1);
  }
#endif

  break;
}

/* Opcode: Clear P1 P2 *
**
** Delete all contents of the database table or index whose root page
** in the database file is given by P1.  But, unlike Destroy, do not







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







3642
3643
3644
3645
3646
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
** a zero is pushed onto the stack.  If AUTOVACUUM is disabled at 
** then a zero is pushed onto the stack.
**
** See also: Clear
*/
case OP_Destroy: {
  int iMoved;
  if( db->activeVdbeCnt>1 ){
    rc = SQLITE_LOCKED;
  }else{
    assert( db->activeVdbeCnt==1 );
    rc = sqlite3BtreeDropTable(db->aDb[pOp->p2].pBt, pOp->p1, &iMoved);
    pTos++;
    pTos->flags = MEM_Int;
    pTos->i = iMoved;
  #ifndef SQLITE_OMIT_AUTOVACUUM
    if( rc==SQLITE_OK && iMoved!=0 ){
      sqlite3RootPageMoved(&db->aDb[pOp->p2], iMoved, pOp->p1);
    }
  #endif
  }
  break;
}

/* Opcode: Clear P1 P2 *
**
** Delete all contents of the database table or index whose root page
** in the database file is given by P1.  But, unlike Destroy, do not
Changes to test/btree.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is btree database backend
#
# $Id: btree.test,v 1.31 2004/10/18 21:34:47 drh Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Basic functionality.  Open and close a database.
#













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is btree database backend
#
# $Id: btree.test,v 1.32 2004/11/10 11:55:14 danielk1977 Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Basic functionality.  Open and close a database.
#
463
464
465
466
467
468
469

470
471
472
473
474
475
476
do_test btree-6.6 {
  btree_close_cursor $::c2
} {}
do_test btree-6.6.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-6.7 {

  btree_drop_table $::b1 $::t2
} {}
do_test btree-6.7.1 {
  lindex [btree_get_meta $::b1] 0
} {1}
do_test btree-6.8 {
  set ::t2 [btree_create_table $::b1 0]







>







463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
do_test btree-6.6 {
  btree_close_cursor $::c2
} {}
do_test btree-6.6.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-6.7 {
  btree_close_cursor $::c1
  btree_drop_table $::b1 $::t2
} {}
do_test btree-6.7.1 {
  lindex [btree_get_meta $::b1] 0
} {1}
do_test btree-6.8 {
  set ::t2 [btree_create_table $::b1 0]
487
488
489
490
491
492
493
494
495

496
497
498
499
500
501
502
  btree_move_to $::c2 {}
  btree_key $::c2
} {}

# If we drop table 1 it just clears the table.  Table 1 always exists.
#
do_test btree-6.10 {
  btree_close_cursor $::c1
  btree_drop_table $::b1 1

  set ::c1 [btree_cursor $::b1 1 1]
  btree_first $::c1
  btree_eof $::c1
} {1}
do_test btree-6.11 {
  btree_commit $::b1
  select_all $::c1







|

>







488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
  btree_move_to $::c2 {}
  btree_key $::c2
} {}

# If we drop table 1 it just clears the table.  Table 1 always exists.
#
do_test btree-6.10 {
  btree_close_cursor $::c2
  btree_drop_table $::b1 1
  set ::c2 [btree_cursor $::b1 $::t2 1]
  set ::c1 [btree_cursor $::b1 1 1]
  btree_first $::c1
  btree_eof $::c1
} {1}
do_test btree-6.11 {
  btree_commit $::b1
  select_all $::c1
Changes to test/table.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.32 2004/11/09 16:13:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.33 2004/11/10 11:55:15 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {
520
521
522
523
524
525
526



527
528
529
530
531
532
533
} {}
do_test table-12.2 {
  execsql {
    SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
  }
} {{CREATE TABLE t8(b number(5,10),h,i integer,j BLOB)}}




# Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
# and CURRENT_TIMESTAMP.
#
do_test table-13.1 {
  execsql {
    CREATE TABLE tablet8(
       a integer primary key,







>
>
>







520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
} {}
do_test table-12.2 {
  execsql {
    SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
  }
} {{CREATE TABLE t8(b number(5,10),h,i integer,j BLOB)}}

#--------------------------------------------------------------------
# Test cases table-13.*
#
# Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
# and CURRENT_TIMESTAMP.
#
do_test table-13.1 {
  execsql {
    CREATE TABLE tablet8(
       a integer primary key,
542
543
544
545
546
547
548

549
550
551
552
553
554
555
556
557
558
559



































































560

foreach {date time} {
  1976-07-04 12:00:00
  1994-04-16 14:00:00
  2000-01-01 00:00:00
  2003-12-31 12:34:56
} {
  incr i

#  set sqlite_current_time [execsql "SELECT strftime('%s','$date $time')"]
  set sqlite_current_time [clock scan "$date $time"]
  do_test table-13.2.$i {
    execsql "
      INSERT INTO tablet8(a) VALUES($i);
      SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;
    "
  } [list $time $date [list $date $time]]
}
set sqlite_current_time 0




































































finish_test








>
|
<









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

>
545
546
547
548
549
550
551
552
553

554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
foreach {date time} {
  1976-07-04 12:00:00
  1994-04-16 14:00:00
  2000-01-01 00:00:00
  2003-12-31 12:34:56
} {
  incr i
  set sqlite_current_time [clock scan "$date $time" -gmt 1]
  # set sqlite_current_time [execsql "SELECT strftime('%s','$date $time')"]

  do_test table-13.2.$i {
    execsql "
      INSERT INTO tablet8(a) VALUES($i);
      SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;
    "
  } [list $time $date [list $date $time]]
}
set sqlite_current_time 0

#--------------------------------------------------------------------
# Test cases table-14.*
#
# Test that a table cannot be created or dropped while other virtual
# machines are active. This is required because otherwise when in 
# auto-vacuum mode the btree-layer may need to move the root-pages of 
# a table for which there is an open cursor.
#

# Try to create a table from within a callback:
do_test table-14.1 {
  set rc [
    catch {
      db eval {SELECT * FROM tablet8 LIMIT 1} {} {
        db eval {CREATE TABLE t9(a, b, c)}
      }
    } msg
  ]
  set result [list $rc $msg]
} {1 {database table is locked}}

do_test table-14.2 {
  execsql {
    CREATE TABLE t9(a, b, c)
  }
} {}

# Try to drop a table from within a callback:
do_test table-14.3 {
  set rc [
    catch {
      db eval {SELECT * FROM tablet8 LIMIT 1} {} {
        db eval {DROP TABLE t9;}
      }
    } msg
  ] 
  set result [list $rc $msg]
} {1 {database table is locked}}

# Now attach a database and ensure that a table can be created in the 
# attached database whilst in a callback from a query on the main database.
do_test table-14.4 {
  file delete -force test2.db
  file delete -force test2.db-journal
  execsql {
    attach 'test2.db' as aux;
  }
  db eval {SELECT * FROM tablet8 LIMIT 1} {} {
    db eval {CREATE TABLE aux.t1(a, b, c)}
  }
} {}

# On the other hand, it should be impossible to drop a table when any VMs 
# are active. This is because VerifyCookie instructions may have already
# been executed, and btree root-pages may not move after this (which a
# delete table might do).
do_test table-14.4 {
  set rc [
    catch {
      db eval {SELECT * FROM tablet8 LIMIT 1} {} {
        db eval {DROP TABLE aux.t1;}
      }
    } msg
  ] 
  set result [list $rc $msg]
} {1 {database table is locked}}

finish_test