Setting sqlite temp store directory

Question

I have a Python binary which uses SQLite as its backend database. SQLite's documentation and the code suggests that setting any of the following 3 environment variables should work:

export TMP=/var/tmp/sqlite/
export TEMP=/var/tmp/sqlite/
export TEMPDIR=/var/tmp/sqlite/

If I export the above variables in my bash script just before I start my Python binary, it does not help.

Another option I tried is calling putenv() by setting os.environ:

os.environ['TMP'] = /var/tmp/sqlite/
os.environ['TEMP'] = /var/tmp/sqlite/
os.environ['TEMPDIR'] = /var/tmp/sqlite/

None of above options has helped in persuading SQLite to use /var/tmp/sqlite as its temp store directory. The only option that has worked - which SQLite's documentation says is deprecated - is setting the temp_store_directory pragma statement:

PRAGMA temp_store_directory = '/egnyte/.work/sqlite_temp'

Since using the pragma statement is not the choice I would like to make, is there any other trick?

Answer 1

For version 3.8.1+ (released October 2013), it's cleaner to use the new SQLITE_TMPDIR environment variable instead of TMPDIR, since the latter is used by Unix software other than SQLite.

From the release notes:

The directory used to hold temporary files on unix can now be set using the SQLITE_TMPDIR environment variable, which takes precedence over the TMPDIR environment variable. The sqlite3_temp_directory global variable still has higher precedence than both environment variables, however.

Answer 2

The environment variables you are referring to are indeed what sqlite looks for but in Windows, not UNIX.

In Unix, the environment variable you need to set is TMPDIR as shown in the sources:

static const char *unixTempFileDir(void){
  static const char *azDirs[] = {
     0,
     0,
     "/var/tmp",
     "/usr/tmp",
     "/tmp",
     0        /* List terminator */
  };
  unsigned int i;
  struct stat buf;
  const char *zDir = 0;

  azDirs[0] = sqlite3_temp_directory;
  if( !azDirs[1] ) azDirs[1] = getenv("TMPDIR");
  for(i=0; i<sizeof(azDirs)/sizeof(azDirs[0]); zDir=azDirs[i++]){
    if( zDir==0 ) continue;
    if( osStat(zDir, &buf) ) continue;
    if( !S_ISDIR(buf.st_mode) ) continue;
    if( osAccess(zDir, 07) ) continue;
    break;
  }
  return zDir;
}