/ Check-in [772e22cb]
Login

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

Overview
Comment:Add PRAGMA 'temp_store_directory'. Added os_*.c function sqlite3OsIsDirWritable(), split pragma.c changeTempStorage() function into invalidateTempStorage(). (CVS 2171)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 772e22cbd69463be41c2e73b4fd4eb33946193c4
User & Date: tpoindex 2004-12-20 19:01:32
Context
2004-12-25
01:03
Fix ticket #1046 by removing code and simplifying the query optimizer. Remarkably, this simplification also makes the optimizer do a better job. Ticket #1051 was fixed as a side-effect. (CVS 2172) check-in: 5fd1f471 user: drh tags: trunk
2004-12-20
19:01
Add PRAGMA 'temp_store_directory'. Added os_*.c function sqlite3OsIsDirWritable(), split pragma.c changeTempStorage() function into invalidateTempStorage(). (CVS 2171) check-in: 772e22cb user: tpoindex tags: trunk
2004-12-19
00:11
The optimizer now uses only the index and ignores the table if it can get away with doing so, thus saving a single BTree search per row of result. This could potentially double the speed of certain queries. The code passes all regression tests but new tests to exercise the new functionality are yet to be added. (CVS 2170) check-in: e5aa4894 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to Makefile.in.

509
510
511
512
513
514
515



516
517
518
519
520
521
522

index.html:	$(TOP)/www/index.tcl last_change
	tclsh $(TOP)/www/index.tcl >index.html

lang.html:	$(TOP)/www/lang.tcl
	tclsh $(TOP)/www/lang.tcl >lang.html




lockingv3.html:	$(TOP)/www/lockingv3.tcl
	tclsh $(TOP)/www/lockingv3.tcl >lockingv3.html

oldnews.html:	$(TOP)/www/oldnews.tcl
	tclsh $(TOP)/www/oldnews.tcl >oldnews.html

omitted.html:	$(TOP)/www/omitted.tcl







>
>
>







509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525

index.html:	$(TOP)/www/index.tcl last_change
	tclsh $(TOP)/www/index.tcl >index.html

lang.html:	$(TOP)/www/lang.tcl
	tclsh $(TOP)/www/lang.tcl >lang.html

pragma.html:	$(TOP)/www/pragma.tcl
	tclsh $(TOP)/www/pragma.tcl >pragma.html

lockingv3.html:	$(TOP)/www/lockingv3.tcl
	tclsh $(TOP)/www/lockingv3.tcl >lockingv3.html

oldnews.html:	$(TOP)/www/oldnews.tcl
	tclsh $(TOP)/www/oldnews.tcl >oldnews.html

omitted.html:	$(TOP)/www/omitted.tcl

Changes to src/os.h.

172
173
174
175
176
177
178

179
180
181
182
183
184
185
int sqlite3OsFileExists(const char*);
int sqlite3OsOpenReadWrite(const char*, OsFile*, int*);
int sqlite3OsOpenExclusive(const char*, OsFile*, int);
int sqlite3OsOpenReadOnly(const char*, OsFile*);
int sqlite3OsOpenDirectory(const char*, OsFile*);
int sqlite3OsSyncDirectory(const char*);
int sqlite3OsTempFileName(char*);

int sqlite3OsClose(OsFile*);
int sqlite3OsRead(OsFile*, void*, int amt);
int sqlite3OsWrite(OsFile*, const void*, int amt);
int sqlite3OsSeek(OsFile*, i64 offset);
int sqlite3OsSync(OsFile*);
int sqlite3OsTruncate(OsFile*, i64 size);
int sqlite3OsFileSize(OsFile*, i64 *pSize);







>







172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
int sqlite3OsFileExists(const char*);
int sqlite3OsOpenReadWrite(const char*, OsFile*, int*);
int sqlite3OsOpenExclusive(const char*, OsFile*, int);
int sqlite3OsOpenReadOnly(const char*, OsFile*);
int sqlite3OsOpenDirectory(const char*, OsFile*);
int sqlite3OsSyncDirectory(const char*);
int sqlite3OsTempFileName(char*);
int sqlite3OsIsDirWritable(char*);
int sqlite3OsClose(OsFile*);
int sqlite3OsRead(OsFile*, void*, int amt);
int sqlite3OsWrite(OsFile*, const void*, int amt);
int sqlite3OsSeek(OsFile*, i64 offset);
int sqlite3OsSync(OsFile*);
int sqlite3OsTruncate(OsFile*, i64 size);
int sqlite3OsFileSize(OsFile*, i64 *pSize);

Changes to src/os_mac.c.

267
268
269
270
271
272
273
















274
275
276
277
278
279
280
      zBuf[j] = (char)zChars[ ((unsigned char)zBuf[j])%(sizeof(zChars)-1) ];
    }
    zBuf[j] = 0;
    if( !sqlite3OsFileExists(zBuf) ) break;
  }
  return SQLITE_OK; 
}

















/*
** Close a file.
*/
int sqlite3OsClose(OsFile *id){
  if( id->refNumRF!=-1 )
    FSClose(id->refNumRF);







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







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
      zBuf[j] = (char)zChars[ ((unsigned char)zBuf[j])%(sizeof(zChars)-1) ];
    }
    zBuf[j] = 0;
    if( !sqlite3OsFileExists(zBuf) ) break;
  }
  return SQLITE_OK; 
}

/*
** If the following global variable points to a string which is the
** name of a directory, then that directory will be used to store
** temporary files.
*/
char *sqlite3_temp_directory = 0;

/*
** Check that a given pathname is a directory and is writable 
** Just return false, as module is deprecated.
*/
int sqlite3OsIsDirWritable(char *zBuf){
  return 0;
}


/*
** Close a file.
*/
int sqlite3OsClose(OsFile *id){
  if( id->refNumRF!=-1 )
    FSClose(id->refNumRF);

Changes to src/os_unix.c.

570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
...
611
612
613
614
615
616
617














618
619
620
621
622
623
624
}

/*
** If the following global variable points to a string which is the
** name of a directory, then that directory will be used to store
** temporary files.
*/
const char *sqlite3_temp_directory = 0;

/*
** Create a temporary file name in zBuf.  zBuf must be big enough to
** hold at least SQLITE_TEMPNAME_SIZE characters.
*/
int sqlite3OsTempFileName(char *zBuf){
  static const char *azDirs[] = {
................................................................................
    for(i=0; i<15; i++, j++){
      zBuf[j] = (char)zChars[ ((unsigned char)zBuf[j])%(sizeof(zChars)-1) ];
    }
    zBuf[j] = 0;
  }while( access(zBuf,0)==0 );
  return SQLITE_OK; 
}















/*
** Read data from a file into a buffer.  Return SQLITE_OK if all
** bytes were read successfully and SQLITE_IOERR if anything goes
** wrong.
*/
int sqlite3OsRead(OsFile *id, void *pBuf, int amt){







|







 







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







570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
...
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
}

/*
** If the following global variable points to a string which is the
** name of a directory, then that directory will be used to store
** temporary files.
*/
char *sqlite3_temp_directory = 0;

/*
** Create a temporary file name in zBuf.  zBuf must be big enough to
** hold at least SQLITE_TEMPNAME_SIZE characters.
*/
int sqlite3OsTempFileName(char *zBuf){
  static const char *azDirs[] = {
................................................................................
    for(i=0; i<15; i++, j++){
      zBuf[j] = (char)zChars[ ((unsigned char)zBuf[j])%(sizeof(zChars)-1) ];
    }
    zBuf[j] = 0;
  }while( access(zBuf,0)==0 );
  return SQLITE_OK; 
}

/*
** Check that a given pathname is a directory and is writable 
**
*/
int sqlite3OsIsDirWritable(char *zBuf){
  struct stat buf;
  if( zBuf==0 ) return 0;
  if( strlen(zBuf)==0 ) return 0;
  if( stat(zBuf, &buf) ) return 0;
  if( !S_ISDIR(buf.st_mode) ) return 0;
  if( access(zBuf, 07) ) return 0;
  return 1;
}

/*
** Read data from a file into a buffer.  Return SQLITE_OK if all
** bytes were read successfully and SQLITE_IOERR if anything goes
** wrong.
*/
int sqlite3OsRead(OsFile *id, void *pBuf, int amt){

Changes to src/os_win.c.

198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
...
404
405
406
407
408
409
410















411
412
413
414
415
416
417
}

/*
** If the following global variable points to a string which is the
** name of a directory, then that directory will be used to store
** temporary files.
*/
const char *sqlite3_temp_directory = 0;

/*
** Create a temporary file name in zBuf.  zBuf must be big enough to
** hold at least SQLITE_TEMPNAME_SIZE characters.
*/
int sqlite3OsTempFileName(char *zBuf){
  static char zChars[] =
................................................................................
  if( isNT() ){
    res = UnlockFile(id->h, SHARED_FIRST, 0, SHARED_SIZE, 0);
  }else{
    res = UnlockFile(id->h, SHARED_FIRST + id->sharedLockByte, 0, 1, 0);
  }
  return res;
}
















/*
** Lock the file with the lock specified by parameter locktype - one
** of the following:
**
**     (1) SHARED_LOCK
**     (2) RESERVED_LOCK







|







 







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







198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
...
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
}

/*
** If the following global variable points to a string which is the
** name of a directory, then that directory will be used to store
** temporary files.
*/
char *sqlite3_temp_directory = 0;

/*
** Create a temporary file name in zBuf.  zBuf must be big enough to
** hold at least SQLITE_TEMPNAME_SIZE characters.
*/
int sqlite3OsTempFileName(char *zBuf){
  static char zChars[] =
................................................................................
  if( isNT() ){
    res = UnlockFile(id->h, SHARED_FIRST, 0, SHARED_SIZE, 0);
  }else{
    res = UnlockFile(id->h, SHARED_FIRST + id->sharedLockByte, 0, 1, 0);
  }
  return res;
}

/*
** Check that a given pathname is a directory and is writable 
**
*/
int sqlite3OsIsDirWritable(char *zBuf){
  int fileAttr;
  if(! zBuf ) return 0;
  if(! isNT() && strlen(zBuf) > MAX_PATH ) return 0;
  fileAttr = GetFileAttributesA(zBuf);
  if( fileAttr == 0xffffffff ) return 0;
  if( (fileAttr & FILE_ATTRIBUTE_DIRECTORY) != FILE_ATTRIBUTE_DIRECTORY ) return 0;
  return 1;
}


/*
** Lock the file with the lock specified by parameter locktype - one
** of the following:
**
**     (1) SHARED_LOCK
**     (2) RESERVED_LOCK

Changes to src/pragma.c.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
...
335
336
337
338
339
340
341






























































342
343
344
345
346
347
348
**    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 contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.79 2004/11/22 19:12:21 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/* Ignore this whole file if pragmas are disabled
*/
#ifndef SQLITE_OMIT_PRAGMA
................................................................................
    return 2;
  }else{
    return 0;
  }
}

/*
** If the TEMP database is open, close it and mark the database schema
** as needing reloading.  This must be done when using the TEMP_STORE
** or DEFAULT_TEMP_STORE pragmas.
*/
static int changeTempStorage(Parse *pParse, const char *zStorageType){
  int ts = getTempStore(zStorageType);
  sqlite3 *db = pParse->db;
  if( db->temp_store==ts ) return SQLITE_OK;
  if( db->aDb[1].pBt!=0 ){
    if( db->flags & SQLITE_InTrans ){
      sqlite3ErrorMsg(pParse, "temporary storage cannot be changed "
        "from within a transaction");
      return SQLITE_ERROR;
    }
    sqlite3BtreeClose(db->aDb[1].pBt);
    db->aDb[1].pBt = 0;
    sqlite3ResetInternalSchema(db, 0);
  }















  db->temp_store = ts;
  return SQLITE_OK;
}

/*
** Generate code to return a single integer value.
*/
................................................................................
  ** override this setting
  */
  if( sqlite3StrICmp(zLeft, "temp_store")==0 ){
    if( !zRight ){
      returnSingleInt(pParse, "temp_store", db->temp_store);
    }else{
      changeTempStorage(pParse, zRight);






























































    }
  }else

  /*
  **   PRAGMA [database.]synchronous
  **   PRAGMA [database.]synchronous=OFF|ON|NORMAL|FULL
  **







|







 







|
|
<

|
<

<










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







 







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







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
...
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
**    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 contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.80 2004/12/20 19:01:33 tpoindex Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/* Ignore this whole file if pragmas are disabled
*/
#ifndef SQLITE_OMIT_PRAGMA
................................................................................
    return 2;
  }else{
    return 0;
  }
}

/*
** Invalidate temp storage, either when the temp storage is changed
** from default, or when 'file' and the temp_store_directory has changed

*/
static int invalidateTempStorage(Parse *pParse){

  sqlite3 *db = pParse->db;

  if( db->aDb[1].pBt!=0 ){
    if( db->flags & SQLITE_InTrans ){
      sqlite3ErrorMsg(pParse, "temporary storage cannot be changed "
        "from within a transaction");
      return SQLITE_ERROR;
    }
    sqlite3BtreeClose(db->aDb[1].pBt);
    db->aDb[1].pBt = 0;
    sqlite3ResetInternalSchema(db, 0);
  }
  return SQLITE_OK;
}

/*
** If the TEMP database is open, close it and mark the database schema
** as needing reloading.  This must be done when using the TEMP_STORE
** or DEFAULT_TEMP_STORE pragmas.
*/
static int changeTempStorage(Parse *pParse, const char *zStorageType){
  int ts = getTempStore(zStorageType);
  sqlite3 *db = pParse->db;
  if( db->temp_store==ts ) return SQLITE_OK;
  if( invalidateTempStorage( pParse ) != SQLITE_OK ){
    return SQLITE_ERROR;
  }
  db->temp_store = ts;
  return SQLITE_OK;
}

/*
** Generate code to return a single integer value.
*/
................................................................................
  ** override this setting
  */
  if( sqlite3StrICmp(zLeft, "temp_store")==0 ){
    if( !zRight ){
      returnSingleInt(pParse, "temp_store", db->temp_store);
    }else{
      changeTempStorage(pParse, zRight);
    }
  }else

  /*
  **   PRAGMA temp_store_directory
  **   PRAGMA temp_store_directory = ""|"directory_name"
  **
  ** Return or set the local value of the temp_store_directory flag.  Changing
  ** the value sets a specific directory to be used for temporary files.
  ** Setting to a null string reverts to the default temporary directory search.
  ** If temporary directory is changed, then invalidateTempStorage.
  **
  */
  if( sqlite3StrICmp(zLeft, "temp_store_directory")==0 ){
    if( !zRight ){
      if( sqlite3_temp_directory ){
        sqlite3VdbeSetNumCols(v, 1);
        sqlite3VdbeSetColName(v, 0, "temp_store_directory", P3_STATIC);
        sqlite3VdbeOp3(v, OP_String8, 0, 0, sqlite3_temp_directory, 0);
        sqlite3VdbeAddOp(v, OP_Callback, 1, 0);
      }
    }else{
      if( strlen(zRight)==0 ){
        /* empty path, set to default. allows os_{unix,win}.c to choose directory */
        if( sqlite3_temp_directory ){
          /* previous temp_store_directory defined, free and invalidate */
          sqlite3FreeX(sqlite3_temp_directory);
          if( db->temp_store==1 ) {
            /* temp storage is "file", so invalidate temp */
            invalidateTempStorage( pParse );
          }
        }
        sqlite3_temp_directory = 0;
      }else{
        /* check if previous directory defined, free and alloc if needed */
        if( sqlite3_temp_directory ){
          if( strlen(sqlite3_temp_directory) < strlen(zRight) + 1){
            sqlite3FreeX(sqlite3_temp_directory);
            sqlite3_temp_directory = sqlite3Malloc( strlen(zRight) + 1 );
            if( sqlite3_temp_directory==0 ){
              goto pragma_out;
            }
            sqlite3_temp_directory[0] = '\0';
          }
        }else{
          sqlite3_temp_directory = sqlite3Malloc( strlen(zRight) + 1 );
          if( sqlite3_temp_directory==0 ){
            goto pragma_out;
          }
          sqlite3_temp_directory[0] = '\0';
        }
        /* check that directory exists and is writable */
        if( sqlite3OsIsDirWritable( zRight ) ){
          strcpy(sqlite3_temp_directory, zRight);
          if( db->temp_store==1 ) {
            /* temp storage is "file", so invalidate temp */
            invalidateTempStorage( pParse );
          }
        }else{
          sqlite3ErrorMsg(pParse, "not a directory, or not writable");
        }
      }
    }
  }else

  /*
  **   PRAGMA [database.]synchronous
  **   PRAGMA [database.]synchronous=OFF|ON|NORMAL|FULL
  **

Changes to src/sqlite.h.in.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166

1167
1168
1169
1170
1171
1172
1173
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.125 2004/12/07 02:14:51 drh Exp $
*/
#ifndef _SQLITE3_H_
#define _SQLITE3_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.
................................................................................
*/
int sqlite3_rekey(
  sqlite3 *db,                   /* Database to be rekeyed */
  const void *pKey, int nKey     /* The new key */
);

/*
** If the following global variable is made to point to a constant
** string which is the name of a directory, then all temporary files
** created by SQLite will be placed in that directory.  If this variable
** is NULL pointer, then SQLite does a search for an appropriate temporary
** file directory.
**
** This variable should only be changed when there are no open databases.
** Once sqlite3_open() has been called, this variable should not be changed
** until all database connections are closed.

*/
extern const char *sqlite3_temp_directory;

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif







|







 







|





<
|
<
>

|





8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163

1164

1165
1166
1167
1168
1169
1170
1171
1172
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.126 2004/12/20 19:01:33 tpoindex Exp $
*/
#ifndef _SQLITE3_H_
#define _SQLITE3_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.
................................................................................
*/
int sqlite3_rekey(
  sqlite3 *db,                   /* Database to be rekeyed */
  const void *pKey, int nKey     /* The new key */
);

/*
** If the following global variable is made to point to a
** string which is the name of a directory, then all temporary files
** created by SQLite will be placed in that directory.  If this variable
** is NULL pointer, then SQLite does a search for an appropriate temporary
** file directory.
**

** Once sqlite3_open() has been called, changing this variable will invalidate the

** current temporary database, if any.
*/
extern char *sqlite3_temp_directory;

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif

Changes to test/pragma.test.

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
...
581
582
583
584
585
586
587
588










































































589
590
591
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.27 2004/11/23 11:16:42 danielk1977 Exp $

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

# Test organization:
#
# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.
# pragma-2.*: Test synchronous on attached db.
# pragma-3.*: Test detection of table/index inconsistency by integrity_check.
# pragma-4.*: Test cache_size and default_cache_size on attached db.
# pragma-5.*: Test that pragma synchronous may not be used inside of a
#             transaction.

#

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
db close
file delete test.db
................................................................................
  }
} {}
do_test pragma-8.2.15 {
  execsql {
    PRAGMA user_version;
  }
} {-450}











































































} ; # ifcapable schema_version

finish_test







|












>







 








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



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
...
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
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.28 2004/12/20 19:01:33 tpoindex Exp $

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

# Test organization:
#
# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.
# pragma-2.*: Test synchronous on attached db.
# pragma-3.*: Test detection of table/index inconsistency by integrity_check.
# pragma-4.*: Test cache_size and default_cache_size on attached db.
# pragma-5.*: Test that pragma synchronous may not be used inside of a
#             transaction.
# pragma-9.*: Test temp_store and temp_store_directory.
#

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
db close
file delete test.db
................................................................................
  }
} {}
do_test pragma-8.2.15 {
  execsql {
    PRAGMA user_version;
  }
} {-450}


# Test temp_store and temp_store_directory pragmas
#

do_test pragma-9.1 {
  db close
  sqlite3 db test.db
  execsql {
    PRAGMA temp_store;
  }
} {0}
do_test pragma-9.2 {
  execsql {
    PRAGMA temp_store=file;
    PRAGMA temp_store;
  }
} {1}
do_test pragma-9.3 {
  execsql {
    PRAGMA temp_store=memory;
    PRAGMA temp_store;
  }
} {2}
do_test pragma-9.4 {
  execsql {
    PRAGMA temp_store_directory;
  }
} {}
do_test pragma-9.5 {
  execsql " \
    PRAGMA temp_store_directory='[pwd]'; \
  "
} {}
do_test pragma-9.6 {
  execsql { 
    PRAGMA temp_store_directory;
  }
} [pwd]
do_test pragma-9.7 {
  set result ""
  catch {
    execsql { 
      PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
    }
  } result
  set result
} {not a directory, or not writable}
do_test pragma-9.8 {
  execsql { 
    PRAGMA temp_store_directory='';
  }
} {}
do_test pragma-9.9 {
  execsql { 
    PRAGMA temp_store_directory;
    PRAGMA temp_store=FILE;
    CREATE TEMP TABLE temp_store_directory_test(a integer);
    INSERT INTO temp_store_directory_test values (2);
    SELECT * FROM temp_store_directory_test;
  }
} {2}
do_test pragma-9.10 {
  set result ""
  catch {
    execsql " \
      PRAGMA temp_store_directory='[pwd]'; \
      SELECT * FROM temp_store_directory_test;
    "
  } result
  set result
} {no such table: temp_store_directory_test}



} ; # ifcapable schema_version

finish_test

Changes to www/pragma.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
207
208
209
210
211
212
213
214


215
216
217
218
219





































220
221
222
223
224
225
226
#
# Run this Tcl script to generate the pragma.html file.
#
set rcsid {$Id: pragma.tcl,v 1.6 2004/11/20 08:17:18 danielk1977 Exp $}
source common.tcl
header {Pragma statements supported by SQLite}

proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"
................................................................................
       <br>PRAGMA temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "temp_store" flag affecting
    the database for the duration of the current database connection.
    The temp_store flag reverts to its default value when the database
    is closed and reopened.  For additional information on the temp_store
    flag, see the description of the <a href="#pragma_default_temp_store">
    <b>default_temp_store</b></a> pragma.  Note that it is possible for 
    the library compile-time options to override this setting. </p>



    <p>When the temp_store setting is changed, all existing temporary
    tables, indices, triggers, and viewers are immediately deleted.
    </p>
    </li>





































</ul>
}

Section {Pragmas to query the database schema} schema

puts {
<ul>



|







 







|
>
>





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







1
2
3
4
5
6
7
8
9
10
11
...
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
#
# Run this Tcl script to generate the pragma.html file.
#
set rcsid {$Id: pragma.tcl,v 1.7 2004/12/20 19:01:34 tpoindex Exp $}
source common.tcl
header {Pragma statements supported by SQLite}

proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"
................................................................................
       <br>PRAGMA temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "temp_store" flag affecting
    the database for the duration of the current database connection.
    The temp_store flag reverts to its default value when the database
    is closed and reopened.  For additional information on the temp_store
    flag, see the description of the <a href="#pragma_default_temp_store">
    <b>default_temp_store</b></a> pragma.  Note that it is possible for 
    the library compile-time options to override this setting.  See
    PRAGMA <a href="#pragma_temp_store_directory">temp_store_directory</a>
    for further temporary storage options when <b>FILE</b> is specified.</p>

    <p>When the temp_store setting is changed, all existing temporary
    tables, indices, triggers, and viewers are immediately deleted.
    </p>
    </li>

<a name="pragma_temp_store_directory"></a>
<li><p><b>PRAGMA temp_store_directory;
       <br>PRAGMA temp_store_directory = 'directory-name';</b></p>
    <p>Query or change the setting of the "temp_store_directory" flag affecting
    the database for the duration of the current database connection.
    The temp_store_directory flag reverts to its default value when the database
    is closed and reopened.  Setting temp_store_directory allows control of the
    placement of temporary files created by SQLite when PRAGMA
    <a href="#pragma_temp_store">temp_store</a> is <b>FILE</b> (1),
    or when the compile time default temporary store is FILE.
    Otherwise, when the temp_store (or default) setting is 
    <b>MEMORY</b> (2), setting temp_store_directory has no effect.</p>

    <p>When the temp_store_directory setting is changed, all existing temporary
    tables, indices, triggers, and viewers are immediately deleted.  In
    practice, temp_store_directory should be set immediately after the 
    database is opened.  </p>

    <p>The value <i>directory-name</i> should be enclosed in single quotes.
    To revert the directory to the default, set the <i>directory-name</i> to
    a null string, e.g., <i>PRAGMA temp_store_directory = ''</i>.  An
    error is raised if <i>directory-name</i> is not found or is not
    writable. </p>

    <p>The default directory for temporary files depends on the OS.  For
    Unix/Linux/OSX, the default is the is the first writable directory found
    in the list of: <b>/var/tmp, /usr/tmp, /tmp,</b> and <b>
    <i>current-directory</i></b>.  For Windows NT, the default 
    directory is determined by Windows, generally
    <b>C:\Documents and Settings\<i>user-name</i>\Local Settings\Temp\</b>. 
    Temporary files created by SQLite are unlinked immediately after
    opening, so that the operating system can automatically delete the
    files when the SQLite process exits.  Thus, temporary files are not
    normally visible through <i>ls</i> or <i>dir</i> commands.</p>
 
    </li>
</ul>
}

Section {Pragmas to query the database schema} schema

puts {
<ul>