SQLite

Check-in [6e948d9aae]
Login

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

Overview
Comment:The VACUUM command is now functioning (again). Need to do more testing. (CVS 916)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6e948d9aaea109c683ac4fcc4714e335b545d22b
User & Date: drh 2003-04-18 02:31:04.000
Context
2003-04-18
17:45
Fix for ticket #297 - bug in sqliteSortCompare(). (CVS 917) (check-in: 4ded1965eb user: drh tags: trunk)
02:31
The VACUUM command is now functioning (again). Need to do more testing. (CVS 916) (check-in: 6e948d9aae user: drh tags: trunk)
2003-04-17
22:57
Fix triggers to work in an ATTACHed database. Ticket #295. (CVS 915) (check-in: 1e5e00fb73 user: drh 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
25
26
27
28
29
30
31
32
33












34
35
36
37
38
39
40
41
**
*************************************************************************
** 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.2 2003/04/15 01:19:49 drh Exp $
*/
#include "sqliteInt.h"


#define SQLITE_OMIT_VACUUM 1

/*
** A structure for holding a dynamic string - a string that can grow
** without bound.
*/
typedef struct dynStr dynStr;
struct dynStr {
  char *z;        /* Text of the string in space obtained from sqliteMalloc() */
  int nAlloc;     /* Amount of space allocated to z[] */
  int nUsed;      /* Next unused slot in z[] */
};













#ifndef SQLITE_OMIT_VACUUM
/*
** Append text to a dynamic string
*/
static void appendText(dynStr *p, const char *zText, int nText){
  if( nText<0 ) nText = strlen(zText);
  if( p->z==0 || p->nUsed + nText + 1 >= p->nAlloc ){
    char *zNew;







|


>





|








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







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
**
*************************************************************************
** 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.3 2003/04/18 02:31:04 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

#define SQLITE_OMIT_VACUUM 1

/*
** A structure for holding a dynamic string - a string that can grow
** without bound. 
*/
typedef struct dynStr dynStr;
struct dynStr {
  char *z;        /* Text of the string in space obtained from sqliteMalloc() */
  int nAlloc;     /* Amount of space allocated to z[] */
  int nUsed;      /* Next unused slot in z[] */
};

/*
** A structure that holds the vacuum context
*/
typedef struct vacuumStruct vacuumStruct;
struct vacuumStruct {
  sqlite *dbOld;       /* Original database */
  sqlite *dbNew;       /* New database */
  Parse *pParse;       /* The parser context */
  const char *zTable;  /* Name of a table being copied */
  dynStr s1, s2;       /* Two dynamic strings */
};

#ifdef SQLITE_OMIT_VACUUM
/*
** Append text to a dynamic string
*/
static void appendText(dynStr *p, const char *zText, int nText){
  if( nText<0 ) nText = strlen(zText);
  if( p->z==0 || p->nUsed + nText + 1 >= p->nAlloc ){
    char *zNew;
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
/*
** Append text to a dynamic string, having first put the text in quotes.
*/
static void appendQuoted(dynStr *p, const char *zText){
  int i, j;
  appendText(p, "'", 1);
  for(i=j=0; zText[i]; i++){
    if( zText[i]='\'' ){
      appendText(p, &zText[j], i-j+1);
      j = i + 1;
      appendText(p, "'", 1);
    }
  }
  if( j<i ){
    appendText(p, &zText[j], i-j);
  }
  appendText(p, "'", 1);
}

/*
** This is an SQLite callback that is invoked once for each row in
** the SQLITE_MASTER table of the database being vacuumed.  The three




** parameters are the type of entry, the name of the entry, and the SQL








** text for the entry.




**





** Append SQL text to the dynStr that will make a copy of the structure

















** identified by this row.







*/
static int vacuumCallback(void *pArg, int argc, char **argv, char **NotUsed){
  dynStr *p = (dynStr*)pArg;

  assert( argc==3 );
  assert( argv[0]!=0 );
  assert( argv[1]!=0 );
  assert( argv[2]!=0 );
  appendText(p, argv[2], -1);
  appendText(p, ";\n", 2);
  if( strcmp(argv[0],"table")==0 ){


    appendText(p, "INSERT INTO ", -1);
    appendQuoted(p, argv[1]);
    appendText(p, " SELECT * FROM ", -1);
    appendQuoted(p, argv[1]);


    appendText(p, ";\n");
  }

  return 0;
}

/*
** Generate a random name of 20 character in length.
*/
static void randomName(char *zBuf){
  static const char zChars[] =







|












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

|
|
>




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







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
135
136
137
138
139
140
141
142
143
144
145
146
147
148

149
150
151
152
153

154
155
156
157
158
159
160
161
162
163
164
165
166
167
/*
** Append text to a dynamic string, having first put the text in quotes.
*/
static void appendQuoted(dynStr *p, const char *zText){
  int i, j;
  appendText(p, "'", 1);
  for(i=j=0; zText[i]; i++){
    if( zText[i]=='\'' ){
      appendText(p, &zText[j], i-j+1);
      j = i + 1;
      appendText(p, "'", 1);
    }
  }
  if( j<i ){
    appendText(p, &zText[j], i-j);
  }
  appendText(p, "'", 1);
}

/*
** Execute statements of SQL.  If an error occurs, write the error
** message into pParse->zErrMsg and return non-zero.
*/
static int execsql(Parse *pParse, sqlite *db, const char *zSql){ 
  int rc;
  char *zErrMsg = 0;

  /* printf("***** executing *****\n%s\n", zSql); */
  rc = sqlite_exec(db, zSql, 0, 0, &zErrMsg);
  if( rc ){
    sqliteErrorMsg(pParse, "%s", zErrMsg);
    sqlite_freemem(zErrMsg);
  }
  return rc;
}

/*
** This is the second stage callback.  Each invocation contains all the
** data for a single row of a single table in the original database.  This
** routine must write that information into the new database.
*/
static int vacuumCallback2(void *pArg, int argc, char **argv, char **NotUsed){
  vacuumStruct *p = (vacuumStruct*)pArg;
  int rc = 0;
  const char *zSep = "(";
  int i;

  p->s2.nUsed = 0;
  appendText(&p->s2, "INSERT INTO ", -1);
  appendQuoted(&p->s2, p->zTable);
  appendText(&p->s2, " VALUES", -1);
  for(i=0; i<argc; i++){
    appendText(&p->s2, zSep, 1);
    zSep = ",";
    if( argv[i]==0 ){
      appendText(&p->s2, "NULL", 4);
    }else{
      appendQuoted(&p->s2, argv[i]);
    }
  }
  appendText(&p->s2,")", 1);
  rc = execsql(p->pParse, p->dbNew, p->s2.z);
  return rc;
}

/*
** This is the first stage callback.  Each invocation contains three
** arguments where are taken from the SQLITE_MASTER table of the original
** database:  (1) the entry type, (2) the entry name, and (3) the SQL for
** the entry.  In all cases, execute the SQL of the third argument.
** For tables, run a query to select all entries in that table and 
** transfer them to the second-stage callback.
*/
static int vacuumCallback1(void *pArg, int argc, char **argv, char **NotUsed){
  vacuumStruct *p = (vacuumStruct*)pArg;
  int rc = 0;
  assert( argc==3 );
  assert( argv[0]!=0 );
  assert( argv[1]!=0 );
  assert( argv[2]!=0 );
  rc = execsql(p->pParse, p->dbNew, argv[2]);

  if( rc==SQLITE_OK && strcmp(argv[0],"table")==0 ){
    char *zErrMsg = 0;
    p->s1.nUsed = 0;
    appendText(&p->s1, "SELECT * FROM ", -1);
    appendQuoted(&p->s1, argv[1]);

    p->zTable = argv[1];
    rc = sqlite_exec(p->dbOld, p->s1.z, vacuumCallback2, p, &zErrMsg);
    if( rc && p->pParse->zErrMsg==0 ){
      sqliteErrorMsg(p->pParse, "%s", zErrMsg);
    }
  }
  return rc;
}

/*
** Generate a random name of 20 character in length.
*/
static void randomName(char *zBuf){
  static const char zChars[] =
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
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
181
182
183
184
185
186

187


188
189
190


191
192
193

194

195
196
197









198



199





200
201
202






203
204
205

206
207
208
209
**
** In version 1.0.x of SQLite, the VACUUM command would call
** gdbm_reorganize() on all the database tables.  But beginning
** with 2.0.0, SQLite no longer uses GDBM so this command has
** become a no-op.
*/
void sqliteVacuum(Parse *pParse, Token *pTableName){
#ifndef SQLITE_OMIT_VACUUM
  const char *zFilename;  /* full pathname of the database file */
  int nFilename;          /* number of characters  in zFilename[] */
  char *zTemp = 0;        /* a temporary file in same directory as zFilename */
  char *zTemp2;           /* Another temp file in the same directory */
  sqlite *dbNew = 0;      /* The new vacuumed database */
  sqlite *dbOld = 0;      /* Alternative connection to original database */
  sqlite *db;             /* The original database */
  int rc;
  char *zErrMsg = 0;
  char *zSql = 0;

  dynStr sStr;

  /* Initial error checks
  */
  if( pParse->explain ){
    return;
  }
  db = pParse->db;
  if( db->flags & SQLITE_InTrans ){
    sqliteErrorMsg(pParse, "cannot VACUUM from within a transaction");
    return;
  }
  memset(&sStr, 0, sizeof(sStr));

  /* Get the full pathname of the database file and create two
  ** temporary filenames in the same directory as the original file.
  */
  zFilename = sqliteBtreeGetFilename(db->aDb[0].pBt);
  if( zFilename==0 ){
    /* This only happens with the in-memory database.  VACUUM is a no-op
    ** there, so just return */
    return;
  }
  nFilename = strlen(zFilename);
  zTemp = sqliteMalloc( 2*(nFilename+40) );
  if( zTemp==0 ) return;
  zTemp2 = &zTemp[nFilename+40];
  strcpy(zTemp, zFilename);
  strcpy(zTemp2, zFilename);
  for(i=0; i<10; i++){
    zTemp[nFilename] = '-';
    randomName(&zTemp[nFilename+1]);

    randomName(&zTemp2[nFilename+1]);
    if( !sqliteOsFileExists(zTemp) && !sqliteOsFileExists(zTemp2) ) break;
  }
  if( i>=10 ){
    sqliteErrorMsg(pParse, "unable to create a temporary database files "
       "in the same directory as the original database");
    goto end_of_vacuum;
  }

  
  dbNew = sqlite_open(zTemp, 0, &zErrMsg);
  if( dbNew==0 ){
    sqliteErrorMsg(pParse, "unable to open a temporary database at %s - %s",
       zTemp, zErrMsg);
    goto end_of_vacuum;
  }
  appendText(&sStr, "ATTACH DATABASE ", -1);

  appendQuoted(&sStr, zFilename);


  appendText(&sStr, " AS orig;\nBEGIN;\n", -1);
  if( execsql(pParse, dbNew, sStr.z) ) goto end_of_vacuum;
  sStr.nUsed = 0;


  rc = sqlite_exec(dbNew, "SELECT type, name, sql FROM sqlite_master "
           "WHERE sql NOT NULL", vacuumCallback, &sStr, &zErrMsg);
  if( rc ){

    sqliteErrorMsg(pParse, "unable to vacuum database - %s", zErrMsg);

    goto end_of_vacuum;
  }
  appendText(&sStr, "COMMIT;\n", -1);









  if( execsql(pParse, dbNew, sStr.z) ) goto end_of_vacuum;










  
end_of_vacuum:






  sqliteFree(zTemp);
  sqliteFree(zSql);
  sqliteFree(sStr.z);

  if( zErrMsg ) sqlite_freemem(zErrMsg);
  if( dbNew ) sqlite_close(dbNew);
#endif
}







|





<

|


>
|











|



















>
















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

>
|
>


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

>
>
>
>
>
>


|
>

<


182
183
184
185
186
187
188
189
190
191
192
193
194

195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
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
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300

301
302
**
** In version 1.0.x of SQLite, the VACUUM command would call
** gdbm_reorganize() on all the database tables.  But beginning
** with 2.0.0, SQLite no longer uses GDBM so this command has
** become a no-op.
*/
void sqliteVacuum(Parse *pParse, Token *pTableName){
#ifdef SQLITE_OMIT_VACUUM
  const char *zFilename;  /* full pathname of the database file */
  int nFilename;          /* number of characters  in zFilename[] */
  char *zTemp = 0;        /* a temporary file in same directory as zFilename */
  char *zTemp2;           /* Another temp file in the same directory */
  sqlite *dbNew = 0;      /* The new vacuumed database */

  sqlite *db;             /* The original database */
  int rc, i;
  char *zErrMsg = 0;
  char *zSql = 0;
  int safety = 0;
  vacuumStruct sVac;

  /* Initial error checks
  */
  if( pParse->explain ){
    return;
  }
  db = pParse->db;
  if( db->flags & SQLITE_InTrans ){
    sqliteErrorMsg(pParse, "cannot VACUUM from within a transaction");
    return;
  }
  memset(&sVac, 0, sizeof(sVac));

  /* Get the full pathname of the database file and create two
  ** temporary filenames in the same directory as the original file.
  */
  zFilename = sqliteBtreeGetFilename(db->aDb[0].pBt);
  if( zFilename==0 ){
    /* This only happens with the in-memory database.  VACUUM is a no-op
    ** there, so just return */
    return;
  }
  nFilename = strlen(zFilename);
  zTemp = sqliteMalloc( 2*(nFilename+40) );
  if( zTemp==0 ) return;
  zTemp2 = &zTemp[nFilename+40];
  strcpy(zTemp, zFilename);
  strcpy(zTemp2, zFilename);
  for(i=0; i<10; i++){
    zTemp[nFilename] = '-';
    randomName(&zTemp[nFilename+1]);
    zTemp2[nFilename] = '-';
    randomName(&zTemp2[nFilename+1]);
    if( !sqliteOsFileExists(zTemp) && !sqliteOsFileExists(zTemp2) ) break;
  }
  if( i>=10 ){
    sqliteErrorMsg(pParse, "unable to create a temporary database files "
       "in the same directory as the original database");
    goto end_of_vacuum;
  }

  
  dbNew = sqlite_open(zTemp, 0, &zErrMsg);
  if( dbNew==0 ){
    sqliteErrorMsg(pParse, "unable to open a temporary database at %s - %s",
       zTemp, zErrMsg);
    goto end_of_vacuum;
  }
  if( sqliteSafetyOff(db) ){
    sqliteErrorMsg(pParse, "library routines called out of sequence");
    goto end_of_vacuum;
  }
  safety = 1;
  if( execsql(pParse, db, "BEGIN") ) goto end_of_vacuum;
  if( execsql(pParse, dbNew, "BEGIN") ) goto end_of_vacuum;
  sVac.dbOld = db;
  sVac.dbNew = dbNew;
  sVac.pParse = pParse;
  rc = sqlite_exec(db, "SELECT type, name, sql FROM sqlite_master "
           "WHERE sql NOT NULL", vacuumCallback1, &sVac, &zErrMsg);
  if( rc ){
    if( pParse->zErrMsg==0 ){
      sqliteErrorMsg(pParse, "unable to vacuum database - %s", zErrMsg);
    }
    goto end_of_vacuum;
  }

  if( sqliteOsFileRename(zFilename, zTemp2) ){
    sqliteErrorMsg(pParse, "unable to rename database file");
    goto end_of_vacuum;
  }
  if( sqliteOsFileRename(zTemp, zFilename) ){
    sqliteOsFileRename(zTemp2, zFilename);
    sqliteErrorMsg(pParse, "unable to rename database file");
    goto end_of_vacuum;
  }
  if( execsql(pParse, dbNew, "COMMIT;") ){
    sqliteOsDelete(zFilename);
    sqliteOsFileRename(zTemp2, zFilename);
    goto end_of_vacuum;
  }
  execsql(pParse, db, "COMMIT;");  /* Nothing was written so its gotta work */
  sqlite_close(dbNew);
  dbNew = 0;
  if( sqliteOsDelete(zTemp2) ){
    sqliteErrorMsg(pParse, "unable to delete old database: %s", zTemp2);
  }

end_of_vacuum:
  sqlite_exec(db, "COMMIT", 0, 0, 0);
  if( safety) {
    sqliteSafetyOn(db);
  }
  if( dbNew ) sqlite_close(dbNew);
  sqliteOsDelete(zTemp);
  sqliteFree(zTemp);
  sqliteFree(zSql);
  sqliteFree(sVac.s1.z);
  sqliteFree(sVac.s2.z);
  if( zErrMsg ) sqlite_freemem(zErrMsg);

#endif
}