[sqlite] famous users of SQLite

[Simon Slavin]

You can add Sony to the list on



(presuming they don't object).

While diagnosing a problem with a Sony Bravia I found it necessary to dump the 
information stored on the internal Flash storage.  There are files there which 
have extension .sdb and have the expected SQLite header.  I didn't delve into 
the content since those files weren't relevant to the problem I was trying to 
fix but they're obviously SQLite.

Simon.

[sqlite] Optimization Opportunity?

[E.Pasma]

Op 7 mrt 2015, om 19:24 heeft Wolfgang Enzinger het volgende geschreven:

> Hi dev team,
>
> not sure if this is actually a useful hint, but ...
>
> CREATE TABLE a(a1 INTEGER PRIMARY KEY);
> INSERT INTO a VALUES (1),(2),(3);
> CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY);
> INSERT INTO b VALUES (1,11),(2,22),(3,33);
> CREATE UNIQUE INDEX b_ui ON b(a1,b1);
> CREATE TABLE c(b1 INTEGER REFERENCES b(b1),c1 INTEGER PRIMARY KEY,c2  
> TEXT);
> INSERT INTO c VALUES (11,111,'a'),(22,222,'b'),(33,333,'c');
> CREATE UNIQUE INDEX c_ui ON c(b1,c1);
> ANALYZE;
>
> Query 1:
>
> EXPLAIN QUERY PLAN
> SELECT a1 FROM a WHERE EXISTS(SELECT * FROM b INNER JOIN c USING(b1)  
> WHERE
> b.a1=a.a1 AND c.c1=222);
>
> selectid  order   fromdetail
> 0 0   0   SCAN TABLE a
> 0 0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
> 1 0   1   SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
> 1 1   0   SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
>
> Query 2:
>
> EXPLAIN QUERY PLAN
> SELECT a1 FROM a WHERE EXISTS(SELECT c1 FROM b INNER JOIN c  
> USING(b1) WHERE
> b.a1=a.a1 AND c.c1=222);
>
> selectid  order   fromdetail
> 0 0   0   SCAN TABLE a
> 0 0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
> 1 0   0   SEARCH TABLE b USING COVERING INDEX b_ui (a1=?)
> 1 1   1   SEARCH TABLE c USING COVERING INDEX c_ui (b1=?)
>
> Note that the only difference between the two is "SELECT *" vs.  
> "SELECT c1"
> within the EXISTS-block. The result is the same in both cases,  
> however the
> second query uses COVERING INDEXes which should be more efficient  
> (as far
> as I know).
>
> HTH; and sorry for the noise if not.
>
> Wolfgang
Hello, as yiou gave a very clear example of the case, I dare to reply.

Actually query one appears slightly faster,
Searching the PK index is faster as that is always a COVERING index.
 From the secunsary indexes only a part oh the key is used.
Note there is not much use on adding PK as second column in the  
additional indexes. It is there anyway a a pointer to the row.

I agree that it is strange that the execution plan for the two queries  
is different, After EXISTS the optimizer might ignore the expression  
in the select part of the sub-query. And Query one looks better as it  
soes not mention any column names. Personally I'd write SELECT NULL  
instead of SELECT *.

If speed matters instead of EXIST you can use IN and a list sub-query.  
This is superfast now:

SELECT a1 FROM a WHERE a1 in (SELECT b.a1 FROM b INNER JOIN c  
USING(b1) WHERE c.c1=222);

0|0|0|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|1|SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
1|1|0|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)

Edzard Pasma

[sqlite] restructuring databases (was Re: Can I copy one column of data to another table?)

[Dave]

Jim,
   My data used to be hard coded into my app but now I am porting to 
Android and the rules have changed. I need smaller subs so the database 
fills the need to make my large subs smaller due to hard coded data. So 
in reality I just need a flat file or as you as say, a denormalized 
database. I figured I would learn the normalized way as years ago I was 
trying to figure that out with Access (with no success). But I am also 
learning Android programming (very early stage), and see that it will be 
easier for me to have a denormalized db for now. My db will be "read 
only" too. So that makes its use more like hard coded data which I want 
and need. When I learned VB years ago I got stumped often but over time 
things started to "click". I am waiting to hear those clicks with SQLite 
soon. :)
Thanks,
Dave
(also in Florida)

On 3/7/2015 8:03 PM, Jim Callahan wrote:
> The appropriate structure of the database depends on whether you need the
> tables spread out into multiple tables for consistency ("one fact in one
> location") -- a process called "normalization or whether you want all the
> data in one table for ease of querying ("denormalization").
>
> Transactional systems are usually designed to be normalized; while decision
> support tables may be more de-normalized (for ease of querying).
>
> If you want to combine your tables along the primary key, the way to do it
> in SQL is to create SELECT ...JOIN queries and then when you have a working
> join query that has the struture you want you have a choice of using the
> SELECT...JOIN query as if it was table or converting the query to a table
> with a CREATE TABLE FROM SELECT ...JOIN query.
>
> Jim Callahan
> Orlando, FL
>
> On Sat, Mar 7, 2015 at 8:18 PM, Darren Duncan 
> wrote:
>

[sqlite] restructuring databases (was Re: Can I copy one column of data to another table?)

[Dave]

Duncan,
   Thanks for your comments. My db is small and the records are at less 
than 200. The tables number 7 and the colums 18 not counting the primary 
auto increment. The UPDATE method I ended up using took about 1 second 
for the one colum I moved or copied. I am just using this db as "read 
only" so my needs are quite different from the norm probably. The SQL 
commands will eventually get sorted out and the usual rules apply to how 
fast I learn. :-) First the word games. Sometimes a command or syntax is 
not intuitive so even if I looked at the word "Update" it didn't sound 
like what I was looking for at the time. I was thinking "copy" or "cut 
and paste" as it seemed feasible because a spreadsheet can do that so 
why couldn't a db app? :-) It will all be water under the bridge some 
day...and I will be smarter too.
Thanks,
schemer

On 3/7/2015 7:18 PM, Darren Duncan wrote:
> On 2015-03-07 9:59 AM, Simon Slavin wrote:
>> On 7 Mar 2015, at 4:42pm, Dave  wrote:
>>
>>> I am fairly new at this although I have wanted to learn and tried 
>>> again and again...But I have a problem. I created a database and 
>>> probably did it wrong and I am trying to fix it. I made a database 
>>> with 7 tables in it all with a primary key and a record ID that 
>>> matches the primary key. Now when trying to use the database I see 
>>> that I should have made 1 table with all the related data (I think) 
>>> and am trying to copy one column of data at a time to the "main" 
>>> table. Can that be done and if so how?
>>
>> Without going into your situation in detail, I have a suggestion 
>> which may help you approach the problem another way.  The SQLite 
>> shell tool has a '.dump' command which turns a database into SQL 
>> commands, and a '.read' command which uses the commands to create 
>> schema and data in a new database.
>>
>> So dump the database into a text file.  Then you can use editing 
>> tools (usually global find-and-replace) mess with the text file so 
>> that all the inserting is done to the same table.  Then you can 
>> create your new database by reading the altered text file.
>
> Frankly the idea (proposed by Simon here) of solving this by dumping 
> everything to a text file and manipulating it there with editing tools 
> sounds abysmal to me.
>
> The only time one might consider that reasonable is if the total 
> number of records is just a handful and you're essentially just 
> re-entering them from scratch.
>
> Once you've already got your data in SQLite, the best general solution 
> by far is to use SQL to manipulate it; if you can't, you've already lost.
>
> What you want to do is create new table(s) with the new format you 
> want, and then do INSERT INTO  SELECT FROM  such that the 
> SELECT easily and reliably does all the hard work of collecting up all 
> the data from the old tables and rearranging it into the new format.  
> Depending on the complexity of the task, you may also create temporary 
> tables for intermediate stages of the processing.
>
> Solving the problem with the likes of SQL UPDATE is hard, but using 
> SELECT is easy.
>
> By a similar token, I believe SQL is often the best place to clean up 
> data from external sources.  Create temporary tables that are very lax 
> in format and constraints that take the external data as pristine as 
> possible, load into those, and then use SELECTs/etc to derive cleaner 
> versions from those into the final tables (or other intermediaries), 
> and you can use the SQL powers to filter or compensate for dirty data 
> etc.  Especially useful for dealing with duplicate data in the source, 
> find or handle with SELECT GROUP BY etc rather than trying conditional 
> INSERT logic or what have you.
>
> -- Darren Duncan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite vfs and wal mode

[J Decker]

I was trying to figure out what to do about this WAL journal_mode option...
1) my vfs does not (and cannot) have a shared memory interface... so those
routines are NULL (so I would think WAL logic should prohibit flagging the
database as wal...)
2) I tried to return error on the pragma, but returning "SQLITE_NOTFOUND"
results in a recordset with 'delete' as the first field... (used to be the
journal mode such as 'WAL' )... returning SQLITE_ERROR results in a parsing
error that also does not indicate my result error of something like
'journal mode not supported' so when I started doing this, sqlite was
still attemping to delete the -wal 3 times at startup and then a lot of
tests if it exists... but it never does... (and never has? isn't journal
mode recorded in the database?)

I'm using a 3.8.7.4 amalgamation.

The database seemed to be working before with much fewer operations; was
just annoyed that it was constantly trying to find a file it never
created... (2? times per statement)
And actually even without ever issuing a wal journal mode it keeps testing
for existence...