Setting temp_store_directory on Windows in future proof way
In order to control where files are written by SQLite, I have a requirement to be able to specify on Windows where any temporary files are written in a future proof way. PRAGMA temp_store_directory is deprecated and so should not be used..... but there does not seem to be any alternative way specified in the documentation in order to specify the location for temporary files for SQLite on Windows. What is the correct (non-deprecated) method to use on Windows to specify this?
SQLite3 uses the Windows API that retrieves the path of the temp directory from the Operating System.
In short, the temp directory is specified in the process environment and looks for the value of the TMP variable, then the TEMP variable, then the USERPROFILE variable, then the Windows System directory; the first one found wins.
Many thanks for the speedy and clear reply. My understanding from this is that there is basically no (non-deprecated) way on Windows of programmatically specifying to SQLite the specific location to use for the temporary files that it uses and that the default Windows mechanisms/locations are used.
That is correct. In theory one could programmatically push a new TMP variable into the current process environment block using the appropriate Windows API.
This would change the current process' copy of the environment and if one were to specify a value for the variable TMP then that would become the new temp file location for the entire process (and any sub-process created after the change were made that inherited the current process environment) provided that the call to the OS to get the temp file location occurred after the change was made.
getenv() might use a cached copy of the environment variables. So you must use
_putenv() (which is not a standard function).
These are C Runtime library functions which do God only knows what (and not even she knows without reading the source code).
Using the SetEnvironmentVariable API does indeed modify the current process environment at the Operating System level as seen by subsequent calls to the GetTempPath API. The runtime functions do not appear to actually modify the process environment, but rather the runtime library's copy of that environment -- though results may vary depending on the particular implementation, compiler, and runtime.
Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec 7 2020, 17:08:21) [MSC v.1927 64 bit (AMD64)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import win32api >>> import os >>> os.getenv('TMP') 'C:\\Users\\KMedcalf\\AppData\\Local\\Temp' >>> win32api.GetTempPath() 'C:\\Users\\KMedcalf\\AppData\\Local\\Temp\\' >>> win32api.SetEnvironmentVariable('TMP', r'D:\Temp') >>> os.getenv('TMP') 'C:\\Users\\KMedcalf\\AppData\\Local\\Temp' >>> win32api.GetTempPath() 'D:\\Temp\\' >>> ^Z