SQLite

Check-in [5b4bf1fce4]
Login

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

Overview
Comment:Allow CREATE TABLE to occur while other queries are running. DROP TABLE is still prohibited, however, since we do not want to delete a table out from under an running query. (CVS 3902)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5b4bf1fce4d589337cfb1b46d75c751bbdc607d5
User & Date: drh 2007-05-02 17:54:56.000
Context
2007-05-03
11:43
Minor bugfixes for incrblob mode. (CVS 3903) (check-in: db54a9466e user: danielk1977 tags: trunk)
2007-05-02
17:54
Allow CREATE TABLE to occur while other queries are running. DROP TABLE is still prohibited, however, since we do not want to delete a table out from under an running query. (CVS 3902) (check-in: 5b4bf1fce4 user: drh tags: trunk)
17:48
Combine the internal btree functions BtreePutData() and getPayload(). (CVS 3901) (check-in: a100a5304b 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.367 2007/05/02 17:48:46 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.368 2007/05/02 17:54:56 drh 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.
5751
5752
5753
5754
5755
5756
5757
5758
5759
5760
5761
5762
5763
5764
5765
5766
5767
5768
5769
5770
5771
5772
5773
  int rc;
  if( pBt->inTransaction!=TRANS_WRITE ){
    /* Must start a transaction first */
    return pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR;
  }
  assert( !pBt->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 = allocateBtreePage(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. */







<
<
<
<
<
<
<
<
<







5751
5752
5753
5754
5755
5756
5757









5758
5759
5760
5761
5762
5763
5764
  int rc;
  if( pBt->inTransaction!=TRANS_WRITE ){
    /* Must start a transaction first */
    return pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR;
  }
  assert( !pBt->readOnly );










#ifdef SQLITE_OMIT_AUTOVACUUM
  rc = allocateBtreePage(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. */
Added test/createtab.test.












































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
# 2007 May 02
#
# 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 that it is OK to create new tables
# and indices while creating existing tables and indices.
#
# $Id: createtab.test,v 1.1 2007/05/02 17:54:56 drh Exp $

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

# Run these tests for all possible values of autovacuum.
#
for {set av 0} {$av<=2} {incr av} {
  db close
  file delete -force test.db test.db-journal
  sqlite3 db test.db

  # Create a table that spans multiple pages.  It is important
  # that part of the database be in pages beyond the root page.
  #
  do_test createtab-$av.1 {
    execsql "PRAGMA auto_vacuum=$av"
    execsql {
      PRAGMA page_size=1024;
      CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
      INSERT INTO t1 VALUES(1, hex(randomblob(200)));
      INSERT INTO t1 VALUES(2, hex(randomblob(200)));
      INSERT INTO t1 VALUES(3, hex(randomblob(200)));
      INSERT INTO t1 VALUES(4, hex(randomblob(200)));
      SELECT count(*) FROM t1;
    }
  } {4}
  do_test createtab-$av.2 {
    file size test.db
  } [expr {1024*(4+($av!=0)+([execsql {PRAGMA encoding}]!="UTF-8")*2)}]
  
  # Start reading the table
  #
  do_test createtab-$av.3 {
    set STMT [sqlite3_prepare db {SELECT x FROM t1} -1 TAIL]
    sqlite3_step $STMT
  } {SQLITE_ROW}
  do_test createtab-$av.4 {
    sqlite3_column_int $STMT 0
  } {1}
  
  # While still reading the table, create a new table.
  #
  do_test createtab-$av.5 {
    execsql {
      CREATE TABLE t2(a,b);
      INSERT INTO t2 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {1 2}
  
  # Continue reading the original table.
  #
  do_test createtab-$av.6 {
    sqlite3_column_int $STMT 0
  } {1}
  do_test createtab-$av.7 {
    sqlite3_step $STMT
  } {SQLITE_ROW}
  do_test createtab-$av.8 {
    sqlite3_column_int $STMT 0
  } {2}
  
  # Do another cycle of creating a new database table while contining
  # to read the original table.
  #
  do_test createtab-$av.11 {
    execsql {
      CREATE TABLE t3(a,b);
      INSERT INTO t3 VALUES(4,5);
      SELECT * FROM t3;
    }
  } {4 5}
  do_test createtab-$av.12 {
    sqlite3_column_int $STMT 0
  } {2}
  do_test createtab-$av.13 {
    sqlite3_step $STMT
  } {SQLITE_ROW}
  do_test createtab-$av.14 {
    sqlite3_column_int $STMT 0
  } {3}
  
  # One more cycle.
  #
  do_test createtab-$av.21 {
    execsql {
      CREATE TABLE t4(a,b);
      INSERT INTO t4 VALUES('abc','xyz');
      SELECT * FROM t4;
    }
  } {abc xyz}
  do_test createtab-$av.22 {
    sqlite3_column_int $STMT 0
  } {3}
  do_test createtab-$av.23 {
    sqlite3_step $STMT
  } {SQLITE_ROW}
  do_test createtab-$av.24 {
    sqlite3_column_int $STMT 0
  } {4}
  
  # Finish reading.  Do an integrity check on the database.
  #
  do_test createtab-$av.30 {
    sqlite3_step $STMT
  } {SQLITE_DONE}
  do_test createtab-$av.31 {
    sqlite3_finalize $STMT
  } {SQLITE_OK}
  do_test createtab-$av.32 {
    execsql {
      SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1
    }
  } {t1 t2 t3 t4}
  integrity_check createtab-$av.40

}
  
finish_test
Changes to test/incrvacuum.test.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the incremental vacuum feature.
#
# Note: There are also some tests for incremental vacuum and IO 
# errors in incrvacuum_ioerr.test.
#
# $Id: incrvacuum.test,v 1.4 2007/04/28 15:47:44 danielk1977 Exp $

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

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum || !pragma} {







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the incremental vacuum feature.
#
# Note: There are also some tests for incremental vacuum and IO 
# errors in incrvacuum_ioerr.test.
#
# $Id: incrvacuum.test,v 1.5 2007/05/02 17:54:56 drh Exp $

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

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum || !pragma} {
84
85
86
87
88
89
90







91
92
93
94
95
96
97
  }
} {}
do_test incrvacuum-2.2 {
  execsql {
    pragma auto_vacuum = 'none';
    pragma auto_vacuum;
  }







} {1}
do_test incrvacuum-2.3 {
  execsql {
    pragma auto_vacuum = 'incremental';
    pragma auto_vacuum;
  }
} {2}







>
>
>
>
>
>
>







84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
  }
} {}
do_test incrvacuum-2.2 {
  execsql {
    pragma auto_vacuum = 'none';
    pragma auto_vacuum;
  }
} {1}
do_test incrvacuum-2.2.1 {
  db close
  sqlite3 db test.db
  execsql {
    pragma auto_vacuum;
  }
} {1}
do_test incrvacuum-2.3 {
  execsql {
    pragma auto_vacuum = 'incremental';
    pragma auto_vacuum;
  }
} {2}
426
427
428
429
430
431
432
433
  } {hello world}

  if {$::nRow == $::iWrite} break
  incr ::iWrite
}

finish_test








<
433
434
435
436
437
438
439

  } {hello world}

  if {$::nRow == $::iWrite} break
  incr ::iWrite
}

finish_test

Changes to test/schema.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the various conditions under which an SQLITE_SCHEMA
# error should be returned.
#
# $Id: schema.test,v 1.5 2005/12/06 12:53:01 danielk1977 Exp $

#---------------------------------------------------------------------
# When any of the following types of SQL statements or actions are 
# executed, all pre-compiled statements are invalidated. An attempt
# to execute an invalidated statement always returns SQLITE_SCHEMA.
#
# CREATE/DROP TABLE...................................schema-1.*







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the various conditions under which an SQLITE_SCHEMA
# error should be returned.
#
# $Id: schema.test,v 1.6 2007/05/02 17:54:56 drh Exp $

#---------------------------------------------------------------------
# When any of the following types of SQL statements or actions are 
# executed, all pre-compiled statements are invalidated. An attempt
# to execute an invalidated statement always returns SQLITE_SCHEMA.
#
# CREATE/DROP TABLE...................................schema-1.*
255
256
257
258
259
260
261




262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
}

#---------------------------------------------------------------------
# Test that if a CREATE TABLE statement fails because there are other
# btree cursors open on the same database file it does not corrupt
# the sqlite_master table.
#




do_test schema-10.1 {
  execsql {
    INSERT INTO abc VALUES(1, 2, 3);
  }
  set sql {SELECT * FROM abc}
  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
  sqlite3_step $::STMT
} {SQLITE_ROW}
do_test schema-10.2 {
  catchsql {
    CREATE TABLE t2(a, b, c);
  }
} {1 {database table is locked}}
do_test schema-10.3 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}
do_test schema-10.4 {
  sqlite3 db2 test.db
  execsql {
    SELECT * FROM abc







>
>
>
>












|







255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
}

#---------------------------------------------------------------------
# Test that if a CREATE TABLE statement fails because there are other
# btree cursors open on the same database file it does not corrupt
# the sqlite_master table.
#
# 2007-05-02: These tests have been overcome by events.  Open btree
# cursors no longer block CREATE TABLE.  But there is no reason not
# to keep the tests in the test suite.
#
do_test schema-10.1 {
  execsql {
    INSERT INTO abc VALUES(1, 2, 3);
  }
  set sql {SELECT * FROM abc}
  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
  sqlite3_step $::STMT
} {SQLITE_ROW}
do_test schema-10.2 {
  catchsql {
    CREATE TABLE t2(a, b, c);
  }
} {0 {}}
do_test schema-10.3 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}
do_test schema-10.4 {
  sqlite3 db2 test.db
  execsql {
    SELECT * FROM abc
326
327
328
329
330
331
332
333
  list $rc $msg
} {1 {Unable to delete/modify collation sequence due to active statements}}
do_test schema-11.8 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}

finish_test








<
330
331
332
333
334
335
336

  list $rc $msg
} {1 {Unable to delete/modify collation sequence due to active statements}}
do_test schema-11.8 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}

finish_test

Changes to test/schema2.test.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the various conditions under which an SQLITE_SCHEMA
# error should be returned.  This is a copy of schema.test that
# has been altered to use sqlite3_prepare_v2 instead of sqlite3_prepare
#
# $Id: schema2.test,v 1.1 2006/11/09 00:24:55 drh Exp $

#---------------------------------------------------------------------
# When any of the following types of SQL statements or actions are 
# executed, all pre-compiled statements are invalidated. An attempt
# to execute an invalidated statement always returns SQLITE_SCHEMA.
#
# CREATE/DROP TABLE...................................schema2-1.*







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the various conditions under which an SQLITE_SCHEMA
# error should be returned.  This is a copy of schema.test that
# has been altered to use sqlite3_prepare_v2 instead of sqlite3_prepare
#
# $Id: schema2.test,v 1.2 2007/05/02 17:54:56 drh Exp $

#---------------------------------------------------------------------
# When any of the following types of SQL statements or actions are 
# executed, all pre-compiled statements are invalidated. An attempt
# to execute an invalidated statement always returns SQLITE_SCHEMA.
#
# CREATE/DROP TABLE...................................schema2-1.*
257
258
259
260
261
262
263




264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
}

#---------------------------------------------------------------------
# Test that if a CREATE TABLE statement fails because there are other
# btree cursors open on the same database file it does not corrupt
# the sqlite_master table.
#




do_test schema2-10.1 {
  execsql {
    INSERT INTO abc VALUES(1, 2, 3);
  }
  set sql {SELECT * FROM abc}
  set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
  sqlite3_step $::STMT
} {SQLITE_ROW}
do_test schema2-10.2 {
  catchsql {
    CREATE TABLE t2(a, b, c);
  }
} {1 {database table is locked}}
do_test schema2-10.3 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}
do_test schema2-10.4 {
  sqlite3 db2 test.db
  execsql {
    SELECT * FROM abc







>
>
>
>












|







257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
}

#---------------------------------------------------------------------
# Test that if a CREATE TABLE statement fails because there are other
# btree cursors open on the same database file it does not corrupt
# the sqlite_master table.
#
# 2007-05-02: These tests have been overcome by events.  Open btree
# cursors no longer block CREATE TABLE.  But there is no reason not
# to keep the tests in the test suite.
#
do_test schema2-10.1 {
  execsql {
    INSERT INTO abc VALUES(1, 2, 3);
  }
  set sql {SELECT * FROM abc}
  set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
  sqlite3_step $::STMT
} {SQLITE_ROW}
do_test schema2-10.2 {
  catchsql {
    CREATE TABLE t2(a, b, c);
  }
} {0 {}}
do_test schema2-10.3 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}
do_test schema2-10.4 {
  sqlite3 db2 test.db
  execsql {
    SELECT * FROM abc
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.46 2006/09/01 15:49:06 drh 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.47 2007/05/02 17:54:56 drh 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 {
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
# 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.
#





# db eval {
#   pragma vdbe_trace = 0;
# }
# Try to create a table from within a callback:
unset -nocomplain result
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;}







>
>
>
>















<
<
<
<
<
<
|







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
# 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.
#
# 2007-05-02:  A open btree cursor no longer blocks CREATE TABLE.
# But DROP TABLE is still prohibited because we do not want to
# delete a table out from under a running query.
#

# db eval {
#   pragma vdbe_trace = 0;
# }
# Try to create a table from within a callback:
unset -nocomplain result
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]






} {0 {}}

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