[sqlite] Quest for "scratch table" implementation in SQLite.

[Smith, Randall]

I'm still trying to figure out a graceful way to implement a "scratch" database 
table that has the following properties:

o Can be created on demand while a database connection is open.
o Can include references to other material in the database to ensure 
integrity.
o Will be automatically deleted when the database is closed.

TEMPORARY tables can't do the second bullet, as I understand it.

Right now I have application code that manages such tables, but it seems clunky 
and error-prone.  Am I missing any SQLite feature that would let me do this 
more easily?

Thanks for any suggestions.

Randall.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign keys & TEMPORARY tables.

[Smith, Randall]

It's quite common to create some kind of large database table or constellation 
of tables that will be used for intermediate work product, and which will be 
deleted when an operation, or the database session, is complete.  This kind of 
thing is an obvious candidate for TEMPORARY tables in SQLite, not only because 
they will be reliably cleaned up, but because they can be located in RAM for 
faster operation.

However, my understanding is that TEMPORARY tables are effectively in their own 
database and (thus?) cannot have foreign key relationships to other databases 
(in this case, the database holding the persistent data).  This seems 
unfortunate since it prevents using TEMPORARY tables if you need or want the 
consistency checking provided by foreign keys, and also seems unnecessary since 
foreign key relationships pointing from a TEMP table to a persistent table will 
never outlive the session, so the argument against allowing against foreign 
keys between databases doesn't (I claim) really apply.

Am I getting the situation right?  Is there any way to allow foreign keys from 
TEMP tables to persistent tables, or can this be considered a feature request?

Randall Smith
Senior Staff Engineer
Qualcomm, Inc.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using a "custom" version of SQLite under Python in Ubuntu.

[Smith, Randall]

This question may be off-topic for this list, since it deals with using SQLite 
in a specific setting rather than SQLite itself.  Apologies if so.  Thanks very 
much if anyone can provide any insight.

I am one of the hapless souls who need access to SQLite's CTE capabilities 
(introduced in v3.8.3) while using Ubuntu 14.04 (which includes SQLite 3.8.2 by 
default).  Our IT people are not thrilled about the idea of upgrading the core 
libraries for fear of messing something else up in Linux-land.

I'm wondering if it's possible to build and use a "private" SQLite library 
based on some other version, and limit its use to a specific application.  My 
particular situation is python scripts -> sqlite3 adaptor -> underlying SQLite 
lib.  So I guess I'm asking if it's possible to bind a particular Python 
interpreter and the libraries it uses to a particular SQLite lib that is not 
used by the rest of the system.

I realize this is a bit vague; I would greatly appreciate any pointers, ideas, 
or food for thought.

Randall.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REFERENCES from TEMPORARY table to main database table?

[Smith, Randall]

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?

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?

Thanks for any information and suggestions!

Randall.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Managing SQLite indices.

[Smith, Randall]

Thanks for the ideas, Simon.  Already good on the general principles.  The 
approach of just periodically deleting all the indices and starting over from 
scratch with a massive, comprehensive re-profiling effort might work on a small 
project, an overstaffed one, one that doesn't change much, or one that is not 
in a high-pressure environment, but is definitely not practical on mine where 
none of these conditions apply.

One thing I've been curious about is why SQLite doesn't provide more powerful 
help here.  A few things I can think of that would be useful would be:

o Some kind of "gee, I sure wish I had this index" info from the query 
planner.

o Index utilization statistics, so little-used or unused indices could 
be identified and eliminated.

o Timing info for each step of the query plan (and for that matter the 
overall query), so query profiling would be easier and you could quickly 
identify the problem spot in a complicated query instead of having to 
constantly play find-the-peanut.

I'm no database engineer, so I have no idea how easy or hard these would be!

Randall.


> From: Simon Slavin 

> Has anyone figured out a good system for managing indices in a smooth, 
> efficient, and reliable way in a non-trivial SQLite application?

Sure.  But you're not going to like it.

General principles:

A) Plan your schema properly.  Tables represent things.  Think through COLLATE 
for every column, especially key columns.  Use foreign keys rather than copying 
data into other tables.  No need to be obsessive about it but "that's how it 
was done when I started here" is not good enough.  Work out your tables, your 
primary keys and your views and your indexes will take care of themselves.

B) If a query runs fast enough, it runs fast enough.  Don't mess with "as fast 
as possible".  That way lies madness.

C) Don't index a column just because it looks important.  You create an index, 
when you create an index, for a particular statement. You look at the "WHERE" 
and "ORDER BY" clauses and figure it out from there.  It's always possible to 
create the best possible index for a statement by inspecting those two clauses 
and thinking about how "chunky" each column is.  You may not need the full 
index -- the rightmost column(s) may be unnecessary -- but it's a good starting 
point.

Got the principles ?  Right.  Now here's the procedure:

1) Delete all indexes.
2) Run ANALYZE.
3) Run your application.
4) Note the SQLite command which takes the most annoyingly long time.
5) Work out a good index which will fix the problem.
6) Create the index.
7) Repeat from step 2.

When your application runs fast enough not to annoy you, you're done.  If 
you're not willing to do step (1), don't bother with anything else.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Managing SQLite indices.

[Smith, Randall]

I'm developing a medium-sized SQLite-based app (maybe 50 tables, 400 query, 
insert, and update routines).  As we all know, having the right set of table 
indices is vital for getting decent performance from SQL.  However, I'm getting 
to the point on this project where this is getting to be very difficult.
 
Some issues (at least for me) are:
 
. Table schema tend to evolve over time.
. Queries are added and removed.
. Queries, table definitions, and index creation are often in different places 
in the code.
. There's no obvious way to identify indices that are no longer needed.
. Indices are confusing at the best of times, and the info from EXPLAIN QUERY 
PLAN and from reading the query itself don't always lead to an obvious 
statement of what indices are needed.
 
Has anyone figured out a good system for managing indices in a smooth, 
efficient, and reliable way in a non-trivial SQLite application?
 
(Apologies if this subject has already been covered; I just joined the list.)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users