On 2016/07/23 3:06 AM, Smith, Randall wrote:
Hi.
I'm creating a specialized index table to a persistent table that I need to
speed up a one-time operation. This seems like a great application for a
TEMPORARY table in SQLite, so the index will always be reliably cleaned up.
However, I can't find a way to have REFERENCES to the main table appear in the
temp table. I want, e.g.
CREATE TEMPORARY TABLE MySpecializedIndex
(
-- etc.
Symbol INTEGER REFERENCES Symbols(rowid)
)
For which I get an operational error "no such table: temp.Symbols".
Using "REFERENCES main.Symbols" appears to be a syntax error.
Is it impossible to have references from temp tables to main tables? If so,
aren't TEMPORARY tables largely useless?
That is a very unimportant very non-consequential little detail you are
basing a very large assumption on. It's akin to asking "Is a car without
a roof-light largely useless?"
To answer the question though, No, it's not possible to forge permanent
references to/from temporary tables - it defies the objective of being
"temporary". I will in fact be surprised to find out you can even
reference another temp table... perhaps this might be possible since
both tables will definitely cease to exist upon connection closure.
Either way, your plan of making an index table is, well, why not just
use an index? It will take the same (if not less) effort to make as a
temp table, and it can be dropped after without much fuss should you no
longer need it. You can even make conditional, compound and concatenated
indices in SQLite if this was your motivation for the table-idea.
Perhaps the "feature request" I'm seeing in this thread is "Temporary
Indices"... which the use cases for are minuscule, though the effort to
implement it and code added overall is probably equally minuscule - so
it might be a good idea still.
Another option is to just make a normal table to achieve your goal, but
I see you have already thought of that, hence the following question:
Is there another idiom in SQLite for managing tables that are intended to have
a short life or which should be reliably cleaned up when the DB closes?
to which the answer is "No - but..."
You could do what needs doing inside a transaction, that will reliably
ensure the last bit of the transaction (where you get rid of any
temporary added objects) also completes (or any errors get reverted back
so the added objects are again removed - either way, no added object
will remain after). Alternatively, you can issue "DROP TABLE xxx IF
EXISTS..." as part of your connection start-up scripts to ensure "the
one that got away" is dealt with.
The only situation I can fathom where this might not work for you is
where you want to have the table "in-use" by clients for a period of
varying use (so not a transaction) while the added index table does its
thing, until the connection is terminated - but if this is the case, I
can't find an argument for not using a real index, or at a minimum, a
more permanent table solution - so I don't think this is your use case.
In conclusion - I'm sorry there is no way to do specifically what you
want, but you are obviously not a beginner at this, so if you share more
details of what you wish to achieve, some of the people here have
extensive experience in tuning DB operations via SQLite and someone
might have already done more-or-less what you are attempting and can
share possible outcomes of their experiences and tests.
Good luck,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
