SQLite

Check-in [88a19a4386]
Login

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

Overview
Comment:Get VACUUM working with UNIQUE indices. Ticket #829. (CVS 1870)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 88a19a4386708c3c10448740d2bbe149055bcc6e
User & Date: drh 2004-07-26 23:07:52.000
Context
2004-07-26
23:32
Honor the ORDER BY clause in a subquery that is the right-hand side of an IN operator. Ticket #827. (CVS 1871) (check-in: 76fe68cff6 user: drh tags: trunk)
23:07
Get VACUUM working with UNIQUE indices. Ticket #829. (CVS 1870) (check-in: 88a19a4386 user: drh tags: trunk)
15:31
add sqlite3_get_auxdata sqlite3_set_auxdata C API exports (CVS 1869) (check-in: 15bfb2d153 user: dougcurrie tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/vacuum.c.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains code used to implement the VACUUM command.
**
** Most of the code in this file may be omitted by defining the
** SQLITE_OMIT_VACUUM macro.
**
** $Id: vacuum.c,v 1.27 2004/07/24 14:35:59 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

#if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM
/*
** Generate a random name of 20 character in length.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains code used to implement the VACUUM command.
**
** Most of the code in this file may be omitted by defining the
** SQLITE_OMIT_VACUUM macro.
**
** $Id: vacuum.c,v 1.28 2004/07/26 23:07:52 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

#if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM
/*
** Generate a random name of 20 character in length.
156
157
158
159
160
161
162
163


164
165





166
167
168
169
170
171
172
173
174
  rc = execSql(db, "BEGIN;");
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Query the schema of the main database. Create a mirror schema
  ** in the temporary database.
  */
  rc = execExecSql(db, 
      "SELECT 'CREATE ' || type || ' vacuum_db.' || "


      "substr(sql, length(type)+9, 1000000) "
      "FROM sqlite_master "





      "WHERE type != 'trigger' AND sql IS NOT NULL "
      "ORDER BY (type != 'table');" 
  );
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Loop through the tables in the main database. For each, do
  ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy
  ** the contents to the temporary database.
  */







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







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173

174
175
176
177
178
179
180
  rc = execSql(db, "BEGIN;");
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Query the schema of the main database. Create a mirror schema
  ** in the temporary database.
  */
  rc = execExecSql(db, 
      "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14,100000000) "
      "  FROM sqlite_master WHERE type='table' "
      "UNION ALL "
      "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14,100000000) "
      "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' "
      "UNION ALL "
      "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) "
      "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"
      "UNION ALL "
      "SELECT 'CREATE VIEW vacuum_db.' || substr(sql,13,100000000) "
      "  FROM sqlite_master WHERE type='view'"

  );
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Loop through the tables in the main database. For each, do
  ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy
  ** the contents to the temporary database.
  */
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

  /* Copy the triggers from the main database to the temporary database.
  ** This was deferred before in case the triggers interfered with copying
  ** the data. It's possible the indices should be deferred until this
  ** point also.
  */
  rc = execExecSql(db, 
      "SELECT 'CREATE ' || type || ' vacuum_db.' || "
      "substr(sql, length(type)+9, 1000000) "
      "FROM sqlite_master "
      "WHERE type = 'trigger' AND sql IS NOT NULL;"
  );
  if( rc!=SQLITE_OK ) goto end_of_vacuum;


  /* At this point, unless the main db was completely empty, there is now a
  ** transaction open on the vacuum database, but not on the main database.
  ** Open a btree level transaction on the main database. This allows a







|
<
|
<







188
189
190
191
192
193
194
195

196

197
198
199
200
201
202
203

  /* Copy the triggers from the main database to the temporary database.
  ** This was deferred before in case the triggers interfered with copying
  ** the data. It's possible the indices should be deferred until this
  ** point also.
  */
  rc = execExecSql(db, 
      "SELECT 'CREATE TRIGGER  vacuum_db.' || substr(sql, 16, 1000000) "

      "FROM sqlite_master WHERE type='trigger'"

  );
  if( rc!=SQLITE_OK ) goto end_of_vacuum;


  /* At this point, unless the main db was completely empty, there is now a
  ** transaction open on the vacuum database, but not on the main database.
  ** Open a btree level transaction on the main database. This allows a
Changes to test/vacuum.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
# 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 VACUUM statement.
#
# $Id: vacuum.test,v 1.23 2004/06/30 10:54:30 danielk1977 Exp $

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


proc cksum {{db db}} {
  set sql "SELECT name, type, sql FROM sqlite_master ORDER BY name, type"
  set txt [$db eval $sql]\n
  set sql "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
  foreach tbl [$db eval $sql] {
    append txt [$db eval "SELECT * FROM $tbl"]\n
  }
  foreach prag {default_synchronous default_cache_size} {
    append txt $prag-[$db eval "PRAGMA $prag"]\n
  }







  set cksum [string length $txt]-[md5 $txt]
  # puts $cksum-[file size test.db]
  return $cksum
}
do_test vacuum-1.1 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
    INSERT INTO t1 VALUES(NULL,randstr(10,100),randstr(5,50));
    INSERT INTO t1 VALUES(123456,randstr(10,100),randstr(5,50));
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    CREATE INDEX i1 ON t1(b,c);

    CREATE TABLE t2 AS SELECT * FROM t1;
    COMMIT;
    DROP TABLE t2;
  }
  set ::size1 [file size test.db]
  set ::cksum [cksum]
  expr {$::cksum!=""}













|




>







|


>
>
>
>
>
>
>


















>







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
# 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 VACUUM statement.
#
# $Id: vacuum.test,v 1.24 2004/07/26 23:07:52 drh Exp $

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

set fcnt 1
proc cksum {{db db}} {
  set sql "SELECT name, type, sql FROM sqlite_master ORDER BY name, type"
  set txt [$db eval $sql]\n
  set sql "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
  foreach tbl [$db eval $sql] {
    append txt [$db eval "SELECT * FROM $tbl"]\n
  }
  foreach prag {default_cache_size} {
    append txt $prag-[$db eval "PRAGMA $prag"]\n
  }
  if 1 {
    global fcnt
    set fd [open dump$fcnt.txt w]
    puts -nonewline $fd $txt
    close $fd
    incr fcnt
  }
  set cksum [string length $txt]-[md5 $txt]
  # puts $cksum-[file size test.db]
  return $cksum
}
do_test vacuum-1.1 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
    INSERT INTO t1 VALUES(NULL,randstr(10,100),randstr(5,50));
    INSERT INTO t1 VALUES(123456,randstr(10,100),randstr(5,50));
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
    CREATE INDEX i1 ON t1(b,c);
    CREATE UNIQUE INDEX i2 ON t1(c,a);
    CREATE TABLE t2 AS SELECT * FROM t1;
    COMMIT;
    DROP TABLE t2;
  }
  set ::size1 [file size test.db]
  set ::cksum [cksum]
  expr {$::cksum!=""}