/ Check-in [88a19a43]
Login
Home Timeline Branches Tags Tickets Wiki More...

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 88a19a4386708c3c10448740d2bbe149055bcc6e
User & Date: drh 2004-07-26 23:07:52
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: 76fe68cf user: drh tags: trunk
23:07
Get VACUUM working with UNIQUE indices. Ticket #829. (CVS 1870) check-in: 88a19a43 user: drh tags: trunk
15:31
add sqlite3_get_auxdata sqlite3_set_auxdata C API exports (CVS 1869) check-in: 15bfb2d1 user: dougcurrie tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vacuum.c. 

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
156
157
158
159
160
161
162
163
164








165
166
167
168
169
170
171
172
173
174
...
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

**
*************************************************************************
** 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.
................................................................................
  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.
  */
................................................................................

  /* 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








|







 







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







 







|
<
|
<








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
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
...
188
189
190
191
192
193
194
195

196

197
198
199
200
201
202
203

**
*************************************************************************
** 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.
................................................................................
  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.
  */
................................................................................

  /* 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. 

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
..
40
41
42
43
44
45
46

47
48
49
50
51
52
53

#    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;
................................................................................
    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!=""}








|




>







|


>
>
>
>
>
>
>







 







>








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
..
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

#    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;
................................................................................
    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!=""}

This page was generated in about 0.01s by Fossil 2.10 [1b63ab701b] 2019-07-14 21:24:14