Temporary table creation doesn't work

[mlhetland]

The relevant parts of the docs, which form the basis for what I think the behavior should have been:

Load a Tables.jl input source into an SQLite table that will be named tablename (will be auto-generated if not specified).

• temp=true will create a temporary SQLite table that will be destroyed automatically when the database is closed

I.e., it seems to me that the table should be created, whether one uses temp=true or not, the only difference being whether the created table will be temporary or not. However, this is not what I observe. If I try to load data into a non-existent table, the table is created if I use temp=false (or don't supply it), but if I supply temp=true, I get the following error (with the appropriate table, columns and values):

ERROR: LoadError: SQLiteException("no such table: temp_table on statement \"INSERT INTO … VALUES …\"")

It seems the issue is that the table is auto-deleted before the INSERT is executed. This behavior can be seen with the following example (which emulates the relevant parts of load!):

using SQLite
using Tables

db = SQLite.DB("createtest.sqlite")

schema = Tables.Schema([:x], [Int])
SQLite.createtable!(db, "temp_table", schema, temp=true)

stmt = SQLite.Stmt(
    db,
    "INSERT INTO temp_table (x) VALUES (1)";
    register = false,
)

DBInterface.transaction(db) do
    SQLite.execute(stmt)
end

Now, the problem persists in the following very stripped-down version:

using SQLite

db = SQLite.DB("createtest.sqlite")

# begin load!
SQLite.execute(db, "CREATE TEMP TABLE temp_table (x INT)")
# begin transaction
SQLite.execute(db, "PRAGMA temp_store=MEMORY")
SQLite.execute(db, "INSERT INTO temp_table (x) VALUES (1)")
# end transaction
# end load!

This has the same issue. However, if we set temp_store before creating the temporary table, things work out just fine:

using SQLite

db = SQLite.DB("createtest.sqlite")

SQLite.execute(db, "CREATE TEMP TABLE temp_table (x INT)")
SQLite.execute(db, "PRAGMA temp_store=MEMORY")
SQLite.execute(db, "INSERT INTO temp_table (x) VALUES (1)")

Swapping these is a bit tough, if we want to have the temp_store inside transaction, and create the table outside it. One option would be to simply add another instance of this pragma – though I'm not sure if it's a good idea, since there's no scoping of it, then? I.e., we could add the duplicate PRAGMA statement before creating the table:

using SQLite

db = SQLite.DB("createtest.sqlite")

# begin load!
SQLite.execute(db, "PRAGMA temp_store=MEMORY") # <-- Added
SQLite.execute(db, "CREATE TEMP TABLE temp_table (x INT)")
# begin transaction
SQLite.execute(db, "PRAGMA temp_store=MEMORY")
SQLite.execute(db, "INSERT INTO temp_table (x) VALUES (1)")
# end transaction
# end load!

I suppose the pragma could be added just before the call to createtable!, maybe? At least that would seem to work -- though it might not be the best solution.

Minimal example for reproducing original error

Here's a minimal example for reproducing this behavior:

using CSV
using SQLite

db = SQLite.DB("temp.sqlite")

SQLite.load!(CSV.File("temp.csv"), db, "temp_table", temp=true)

If true is replaced with false, it works.

Example CSV file (temp.csv), for completeness:

x,y
1,2

[mlhetland]

As a workaround, for now: It seems that adding

SQLite.execute(db, "PRAGMA temp_store=MEMORY")

at any point in one's code before the call to createtable! or load! with temp=true avoids the issue.

(This would, of course, happen automatically if the load! was executed inside a call to transaction, which maybe it should, in many cases.)