Re: [pysqlite] database locked

Rich Shepard wrote:
> On Tue, 27 May 2008, Eric S. Johansson wrote:
> 
>> (Question: what's the better solution for setting up databases when you
>> have two tables, 150,000 entries or more and the other with potentially
>> millions.  Should each table have its own set of files or should I put
>> both tables in one file?)
> 
>    SQLite keeps all tables and other information in a single file. Relational
> database management systems have tables; programming languages and operating
> systems have files. They are not the same. Size limits are a function of
> your OS and hard drives. Use indices to speed data access.

Sorry, I didn't express myself clearly.  I know that SQL light can have multiple 
tables in one file which contains the database.  The question  should have been, 
if I have relatively large data sets,  would I be better off having all of my 
data sets in one database or, having one data set per database?  These two data 
sets may or may not be used in the same program instance.  For example,  the 
meta data instance would be used in each program instance but the reputation 
database could be accessed through a demon.  this option also raises the 
question of which form is better.

>> (Question: is there any relationship between the number of records returned in
>> time the database is locked?  There is one point in my application where I query
>> for a "screens" worth of data and get far more than I need because I need to
>> sort it and massage it for presentation.  Would there be value in leading SQL
>> light sort and then return only the first X elements?)
> 
>    The set of records returned by a query is determined by both how you write
> the query and how you access the returned set for display. Sorting is an
> independent of the query, but can be included in the query using the SQL
> command of ORDERED BY.

I understand that.  I have implemented it on various occasions.  The question 
is, is there any performance/locking advantage in letting SQL light do the 
sorting and narrowing of the data returned as compared to a relatively simple 
selection and letting the application program do the sorting and choosing the 
first N records.

>    From your questions it appears that you might benefit from reading a book
> on SQL and (or in addition to) Mike Owen's excellent book, "The Definitive
> Guide to SQLite."

I haven't read the SQL light book but the other SQL books I have read, at best, 
put me the Way back machine and send me to the 1970s.  I'm serious, most SQL 
books are just horrific.  As a rule, they contain the muddiest explanations and 
worst writing I've ever seen.  I think COBOL books were clearer than SQL books.