Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Raymond]

> Alternatively, you can use the new GENERATED ALWAYS AS (...) STORED to make 
> it an automatically updated stored field and you do not need triggers at all, 
> just a version of SQLite3 that does generated columns (version 3.31.0 from 
> 2020-01-22 or later).
> 
> create table MyData
> (
>   id   integer primary key,
>   data,
>   lastupdate real as (julianday()) stored
> );

I thought that generated columns could only use deterministic functions?
https://www.sqlite.org/gencol.html
"2.3. Limitations
...
3. The expression of a generated column may only reference constant literals 
and columns within the same row, and may only use scalar deterministic 
functions. The expression may not use subqueries, aggregate functions, window 
functions, or table-valued functions.
..."


https://www.sqlite.org/deterministic.html
"3. Special-case Processing For Date/Time Functions

The built-in date and time functions of SQLite are a special case. These 
functions are usually considered deterministic. However, if these functions use 
the string "now" as the date, or if they use the localtime modifier or the utc 
modifier, then they are considered non-deterministic. Because the function 
inputs are not necessarily known until run-time, the date/time functions will 
throw an exception if they encounter any of the non-deterministic features in a 
context where only deterministic functions are allowed."


So is "julianday('now')" non-deterministic while "julianday()" _is_ 
deterministic? That seems a little weird considering they're the same thing... 
right?

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

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Raymond]

> What stops the
> UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
> from also triggering the AFTER UPDATE ON recursively?
> 
> Perhaps a pragma or inSQLite are  triggers non-recursive by default?
> 
> I am using (now I have by semi-colons right)
> CREATE TRIGGER tgrUpdate AFTER UPDATE ON myTable FOR EACH ROW
> WHEN NEW.LastUpdate <= OLD. LastUpdate
> BEGIN
> UPDATE myTable SET  LastUpdate = DATETIME('now') WHERE id = OLD.id;
> END
> 
> My intention is for the when to avoid infinite calls, but maybe I am
> fooling myself.

Recursive triggers are off by default.
Otherwise you could always add checks into the WHEN clause for seeing if any of 
the other fields was actually updated.

WHEN NEW.LastUpdate <= OLD.LastUpdate
 AND
 (   --Something actually changed
 NEW.Field1 is not OLD.Field1
 OR
 NEW.Field2 is not OLD.Field2
 OR ...
 --Think you want to exclude LastUpdate from this OR'd list of changed 
fields to check
 )

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

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[David Raymond]

I see this. What does it mean? Does it mean even when the sqlite3
session is closed there is still -wal and -shm left on the disk?

"""
There is an additional quasi-persistent "-wal" file and "-shm" shared
memory file associated with each database, which can make SQLite less
appealing for use as an application file-format.
"""


In rollback journal mode the -journal file only shows up when you're making 
changes. In wal mode the -wal and -shm files are there for the entire life of 
the connection, as long as there's anything even reading the file, they're 
there.

In general I believe the last connection tries to do a complete checkpoint when 
it closes, and if it succeeds then it'll delete the -wal and -shm files. If you 
have automatic checkpointing turned off (maybe you're doing regular checkpoints 
from a separate dedicated process) then it's possible for all connections to 
finish and close the database without error, but still have those files sitting 
around because they haven't been checkpointed yet.

Some combination of those two is what is being referred to there I believe.

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

Re: [sqlite] After column add, what should be done to update the schema?

[David Raymond]

"I read somewhere that the DDL is really just a copy of the text when teh table 
was created"
It starts that way, but the ALTER TABLE command will update that string when it 
runs so that it reflects the new column.

Did you commit your changes after the alter table and before looking for the 
change in Sqlite Expert?

"However, when I use the Sqlite Expert, the DDL is not reflected. Even though 
the column is created."
Never having used Sqlite Expert, how are you determining that it was created? 
Further queries using the column work? Some GUI showing fields has been 
updated? Some other means?
When you say "the DDL is not reflected" how are you looking at the DDL? Is it 
some special window in Sqlite Expert? Are you explicitly running a select 
statement on sqlite_master? Some other method?

If you close Sqlite Expert and re-open it does it show the changes now?

At the moment it kind of sounds like Sqlite Expert is doing some sort of 
non-expert caching that it isn't updating the way it should.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: inserting new data only

[David Raymond]

Awesome---exactly what's needed.
The monotonicity of the time key variable is assured by how the data
is collected---but  is there a way to express that in sqlite?
create table data (
  key text primary key check
(julianday(key) > julianday(select max(key) from data),
  data integer not null);


That would/should be done in a trigger, and not a check constraint. A check 
constraint is only supposed to be something which will _always_ be true about 
that one and only record, and which only needs the contents of that 1 record to 
determine, and not something that might change depending on... anything else.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: inserting new data only

[David Raymond]

A before trigger which uses the raise function would stop it from getting 
inserted in the first place. 

create trigger cull
before insert on tbl
when new.value = (select value from tbl order by time desc limit 1)
begin
select raise(ignore);
end;

Or if you want it to actually return an error to let you know what happened you 
could make it
select raise(abort, 'Repeated entry');



-Original Message-
From: sqlite-users  On Behalf Of 
Przemek Klosowski
Sent: Tuesday, February 25, 2020 12:02 PM
To: SQLite mailing list 
Subject: [sqlite] Fwd: inserting new data only

I am storing time series data arriving from a sensor into (time,value)
records, like so:
10:32  12
10:35  15
10:37  15
10:39  13
10:43  13
10:46  18

and I want to avoid storing repetitive data, so that the database should contain
10:32  12
10:35  15
10:39  13
10:46  18
where only the earliest time with the unchanging value is stored.

I don't see how INSERT could be conditional on e.g.  value != (select
value from tbl order by time descending limit 1), so I thought I'd use
triggers. The only way I could think of was to delete the new
duplicate record after it has been inserted:

create trigger cull after insert on tbl when
 (select value-lead(value) over (order by time desc) from a limit 1) = 0
begin
   delete from a where time like new.time;
end;

Is there a simpler way?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

[David Raymond]

Not necessarily related to the question itself, but how did the attachments 
actually come through with this mail? Every single other person to try and 
attach something to this list has had it stripped off.


-Original Message-
From: sqlite-users  On Behalf Of 
 ?
Sent: Tuesday, February 11, 2020 1:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] JSON_EACH + recursive query = unexpected performance 
degradation

I have a dataset of about 300 rows which have parent-child relations. 
Due to factors unrelated to the issue I build the rows by zipping JSON 
arrays with values from each column.
Then I run a simplest recursive query on it to get the whole tree(ends 
up being ~4 levels).

Problem: the query takes 3000 ms (3 seconds) on my machine to complete. 
If I create a real table with the SAME structure, insert the SAME data 
into it, and run the SAME query, get the SAME result back, it takes 
10-15 ms (200-300 TIMES faster).


I attached both queries(don't require schema to run)

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

Re: [sqlite] UPSERT documentation question

[David Raymond]

> CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
> INSERT INTO vocabulary(word) VALUES('jovial')
> ON CONFLICT(word) DO UPDATE SET count=count+1;
> 
> Shouldn't that actually be written as "vocabulary.count+1"?

Nope. Unqualified names there refer to the one and only record that's getting 
updated.

Similar to how in an blanket update statement you would do:
update vocabulary set count = count + 1;
...and not:
update vocabulary set vocabulary.count = vocabulary.count + 1;

I mean, it might still work, but it's not needed, no.

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

Re: [sqlite] Unsubscribe from Sqlite users list

[David Raymond]

Follow the link at the bottom of every email. The bottom box/button on the page 
is for unsubscribing.
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-Original Message-
From: sqlite-users  On Behalf Of 
Ainhoa B
Sent: Wednesday, February 5, 2020 9:11 AM
To: SQLite mailing list 
Subject: [sqlite] Unsubscribe from Sqlite users list

Hi,

I would like to unsubscribe from this list and stop receiving messages, but
I do not know how to do it.

Can someone help me?

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

Re: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

[David Raymond]

They shouldn't be different, no. Do you have a copy of the weird version of the 
database still, or have a copy of the explain text and the sqlite_master 
contents? Was there another index or table whose root page is what was listed 
in the explain output? For example, were you expecting it to use the table, but 
it used a covering index instead?


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Monday, February 3, 2020 1:07 PM
To: Discussion of SQLite Database 
Subject: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

Differing by 1. I noticed this today for 1 table only but not always - 
sometimes they were equal. I’ve been unable to reproduce it after a vacuum. 
Does this indicate a corrupt db or is it a case of it can happen for some 
reason I’ve missed?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] log() in sqlite3

[David Raymond]

The core functions of the library are fairly unlikely to get expanded upon at 
this point, so it's up to the application using the SQLite library to expand on 
them.

You can load an extension which has those functions. Or the C API gives you 
means to add your own functions.

For Python, the built in sqlite3 module has the create_function() method for a 
connection which will register a function.
https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function

Once you register the function you can use it right in your sql text like any 
other function,
"update tbl set field2 = log(field1);"

And you don't need to do the more tedious:
a) Get original data with statement 1
b) Do calculations in the outside program
c) Use the result in statement 2


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Friday, January 31, 2020 12:42 PM
To: SQLite mailing list 
Subject: [sqlite] log() in sqlite3

Hi,

I see that many math functions (like log()) are not in sqlite3. It
seems that SQL standard doesn't have them.

https://www.sqlite.org/lang_corefunc.html

But since sqlite3 contains non-standard functions anyway. Would it be
considered to add those functions?

Given the current version of sqlite3, is the only choice of computing
log() to get the data into another language (e.g., python) and compute
the log over there? Thanks.

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

Re: [sqlite] Default isolation_level for sqlite3.connect?

[David Raymond]

> Is there a complete list that defines what are "the statements that
> modify the database"? What is the difference between "the statements
> that modify the database" and "Data Modification Language (DML)
> statement"? Are they the same?

If you dig into their code you can attempt to find what it is actually doing, 
but the documentation is somewhat ambiguous. (Keith included his findings from 
their code in one of his responses)


> Whether or not they are the same or not, providing a complete list of
> such statements for each case should be far less confusing.

Fully agreed.


You're basically discovering for yourself firsthand why we recommend using 
autocommit/setting isolation_level = None when opening a connection in Python. 
It basically comes down to "ambiguity is frustrating and prone to error, so do 
it yourself explicitly to be clear and safe."


> BTW, who is maintaining the python doc? Is it somebody from the
> sqlite3 community or the python community? I hope that someone will go
> over the document and resolve all the inconsistencies and excessive
> "referring to other places references".

That's the Python folks, since it's their wrapper. You'll have to dig a little 
to find whichever is the correct mailing list to raise your concerns on over 
there.



-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Friday, January 31, 2020 9:11 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Default isolation_level for sqlite3.connect?

The following is still very confusing.

https://docs.python.org/3.8/library/sqlite3.html#controlling-transactions

"""
autocommit mode means that statements that modify the database take
effect immediately."

...

The Python sqlite3 module by default issues a BEGIN statement
implicitly before a Data Modification Language (DML) statement (i.e.
INSERT/UPDATE/DELETE/REPLACE).
"""

Is there a complete list that defines what are "the statements that
modify the database"? What is the difference between "the statements
that modify the database" and "Data Modification Language (DML)
statement"? Are they the same?

Whether or not they are the same or not, providing a complete list of
such statements for each case should be far less confusing.

BTW, who is maintaining the python doc? Is it somebody from the
sqlite3 community or the python community? I hope that someone will go
over the document and resolve all the inconsistencies and excessive
"referring to other places references".

> This operates in "auto-commit" mode. When a statement is executed that
> is not already inside an EXPLICIT (=user-created) transaction, then it
> is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore:
>
>INSERT INTO TABLE_A ...
>BEGIN 
>INSERT INTO TABLE_B ...
>DELETE FROM TABLE_C ...
>COMMIT
>SELECT ... FROM TABLE_D
>
> will essentially be turned into:
>
>BEGIN
>INSERT INTO TABLE_A ...
>COMMIT
>BEGIN 
>INSERT INTO TABLE_B ...
>DELETE FROM TABLE_C ...
>COMMIT
>BEGIN
>SELECT ... FROM TABLE_D
>COMMIT
>
> where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED
> (the SQLite engine's default if you just use BEGIN).

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

Re: [sqlite] conditional insert operations

[David Raymond]

"What is v?"
That's the name you gave your third field in your example
> Suppose the table is this (the first line is just header)
>
> h1,h2,v


/*
Ensure there is an actual explicitly defined unique constraint on h1, h2
*/
create unique index tbl_uidx_h1_h2 on tbl (h1, h2);


/*
Attempt to insert the new data
*/
insert into tbl (h1, h2, v) values ('a', '', 'X')

/*
If you hit a unique/primary key constraint on (h1, h2)...
*/
on conflict (h1, h2)

/*
then instead of inserting the new record, update the existing record that 
caused the unique violation,
*/
do update

/*
and update its "v" field to be the value you were trying to insert
*/
set v = excluded.v

/*
But only bother doing this is the new value for "v" is different than the old 
one.
i.e. Don't bother going through the motions of updating the record if you're 
not actually going to change any values.
(I used "is not" rather than != so it would work with nulls (or at least, that 
was my intent))
*/
where v is not excluded.v;



-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Thursday, January 30, 2020 8:21 PM
To: SQLite mailing list 
Subject: Re: [sqlite] conditional insert operations

h1, h2 pair are unique.

I don't quite understand your example. Could you explain what it does
in plain English so that I can be sure it does what I want? (What is
v?)

On 1/30/20, David Raymond  wrote:
> Is the combo of h1, h2 unique? If so you could do an upsert
> (https://www.sqlite.org/lang_UPSERT.html)
>
> create unique index tbl_uidx_h1_h2 on tbl (h1, h2);
>
> insert into tbl values ('a', '', 'X')
> on conflict (h1, h2)
> do update set v = excluded.v
> where v is not excluded.v;
>
>
> -Original Message-
> From: sqlite-users  On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 12:02 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] conditional insert operations
>
> Suppose the table is this (the first line is just header)
>
> h1,h2,v
> a,,Y
> a,C,3
>
> Since v of h1=a and h2="" is Y which is not X, the table should be updated
> to
>
> h1,h2,v
> a,,X
> a,A,1
> a,B,2
> ...
>
>
> Suppose the table is this, as v of h1=a and h2="" is X, the table is
> not changed.
>
> h1,h2,v
> a,,X
> a,C,3
>
> Suppose the table is this.
>
> h1,h2,v
> a,C,3
>
> since there is no row with h1=a and h2="", the table is updated to
>
> h1,h2,v
> a,,X
> a,A,1
> a,B,2
> ...
>
>
> On 1/30/20, David Raymond  wrote:
>> I'm not quite following what you're trying to do here. Could you provide
>> a
>> few examples of "here's what used to be in there", "here's what I want to
>> insert", "here's what it should like in the end"
>>
>>
>>
>> -Original Message-
>> From: sqlite-users  On
>> Behalf
>> Of Peng Yu
>> Sent: Thursday, January 30, 2020 11:27 AM
>> To: SQLite mailing list 
>> Subject: [sqlite] conditional insert operations
>>
>> Hi,
>>
>> Suppose that I have a table with three columns h1, h2, v. I want to
>> delete all rows with h1=a, and insert rows like the following (data
>> shown in TSV format), only if there is not an entry with h1=a and
>> h2="" (empty), it exists but its v is not equal to a value X.
>>
>> a,A,v1
>> a,B,v2
>> ...
>>
>> https://www.sqlite.org/lang_insert.html
>>
>> I am not sure if there is a way to check an entry to know whether new
>> entries can be inserted. Could anybody show me an example if this can
>> be done?
>>
>> --
>> Regards,
>> Peng
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Is Comment column in 'EXPLAIN' blank?

[David Raymond]

To get the comments the library needs to have been compiled with 
SQLITE_ENABLE_EXPLAIN_COMMENTS flag
https://www.sqlite.org/compile.html#_options_to_enable_features_normally_turned_off

That setting is off by default, but for the pre-compiled CLI they do include it.

I'm not seeing it in the list when I do a "pragma compile_options;" though. Is 
that setting one that's not reported by that pragma?


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Friday, January 31, 2020 6:22 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Is Comment column in 'EXPLAIN' blank?

I get the comments using the shell but running an explain query in c++ using 
sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is 
something up or is it my code?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] conditional insert operations

[David Raymond]

Is the combo of h1, h2 unique? If so you could do an upsert 
(https://www.sqlite.org/lang_UPSERT.html)

create unique index tbl_uidx_h1_h2 on tbl (h1, h2);

insert into tbl values ('a', '', 'X')
on conflict (h1, h2)
do update set v = excluded.v
where v is not excluded.v;


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Thursday, January 30, 2020 12:02 PM
To: SQLite mailing list 
Subject: Re: [sqlite] conditional insert operations

Suppose the table is this (the first line is just header)

h1,h2,v
a,,Y
a,C,3

Since v of h1=a and h2="" is Y which is not X, the table should be updated to

h1,h2,v
a,,X
a,A,1
a,B,2
...


Suppose the table is this, as v of h1=a and h2="" is X, the table is
not changed.

h1,h2,v
a,,X
a,C,3

Suppose the table is this.

h1,h2,v
a,C,3

since there is no row with h1=a and h2="", the table is updated to

h1,h2,v
a,,X
a,A,1
a,B,2
...


On 1/30/20, David Raymond  wrote:
> I'm not quite following what you're trying to do here. Could you provide a
> few examples of "here's what used to be in there", "here's what I want to
> insert", "here's what it should like in the end"
>
>
>
> -Original Message-
> From: sqlite-users  On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 11:27 AM
> To: SQLite mailing list 
> Subject: [sqlite] conditional insert operations
>
> Hi,
>
> Suppose that I have a table with three columns h1, h2, v. I want to
> delete all rows with h1=a, and insert rows like the following (data
> shown in TSV format), only if there is not an entry with h1=a and
> h2="" (empty), it exists but its v is not equal to a value X.
>
> a,A,v1
> a,B,v2
> ...
>
> https://www.sqlite.org/lang_insert.html
>
> I am not sure if there is a way to check an entry to know whether new
> entries can be inserted. Could anybody show me an example if this can
> be done?
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Generated columns and COLLATE in the AS parens

[David Raymond]

From a Windows installation of 12.0


testing=> select version();
  version

 PostgreSQL 12.0, compiled by Visual C++ build 1914, 64-bit
(1 row)

Time: 0.283 ms
testing=> create table foo (
testing(> a text,
testing(> b text collate "C",
testing(> c text collate "en-US-x-icu",
testing(> d text generated always as (a) stored,
testing(> e text generated always as (b) stored,
testing(> f text generated always as (c) stored,
testing(> g text collate "C" generated always as (c) stored,
testing(> h text generated always as (c collate "C") stored,
testing(> i text collate "C" generated always as (c collate "en-US-x-icu") 
stored
testing(> );
CREATE TABLE
Time: 81.025 ms
testing=> \d foo
  Table "public.foo"
 Column | Type |  Collation  | Nullable |   Default
+--+-+--+--
 a  | text | |  |
 b  | text | C   |  |
 c  | text | en-US-x-icu |  |
 d  | text | |  | generated always as (a) stored
 e  | text | |  | generated always as (b) stored
 f  | text | |  | generated always as (c) stored
 g  | text | C   |  | generated always as (c) stored
 h  | text | |  | generated always as (c COLLATE "C") 
stored
 i  | text | C   |  | generated always as (c COLLATE 
"en-US-x-icu") stored

testing=> select attnum, attname, attgenerated, collname from pg_attribute left 
outer join pg_collation on pg_attribute.attcollation = pg_collation.oid where 
attrelid = 'foo'::regclass and attnum > 0 order by attnum;
 attnum | attname | attgenerated |  collname
+-+--+-
  1 | a   |  | default
  2 | b   |  | C
  3 | c   |  | en-US-x-icu
  4 | d   | s| default
  5 | e   | s| default
  6 | f   | s| default
  7 | g   | s| C
  8 | h   | s| default
  9 | i   | s| C
(9 rows)

Time: 0.590 ms
testing=>


-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Thursday, January 30, 2020 12:21 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Generated columns and COLLATE in the AS parens

On 1/30/20, Markus Winand  wrote:
>
> Unfortunately, the “what would PostgreSQL do” approach doesn’t provide
> guidance here.

Maybe it does.  PostgreSQL doesn't allow typeless columns, but it does
allow columns with unspecified collating sequences, does it not?  What
if you have a normal column X with some collating sequence C and then
a generated column Y that as just "AS(X)".  If you do comparisons on
column Y, which collating sequence does it use - the default or C?
Can you run that experiment for us?

Or maybe you are thinking the collating sequence of the expression in
the AS clause should only be carried through into the generated column
if it is explicitly stated, and not implied?

What happens if there is a collating sequence specified in the AS
clause and also another collating sequence on the column definitions?

  CREATE TABLE t1(x TEXT, y TEXT AS (x COLLATE nocase) COLLATE rtree);

Which collating sequence should be used for "SELECT * FROM t1 WHERE y='xyz';"?

Regarding WWPD: If you can convince Tom Lane and/or Bruce Momjian to
send me an email that says "An explicit collating sequence at the
top-level of a GENERATED ALWAYS AS clause should be carried through as
the default collating sequence of the generated column itself", then
I'll change it.  :-)

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

Re: [sqlite] conditional insert operations

[David Raymond]

I'm not quite following what you're trying to do here. Could you provide a few 
examples of "here's what used to be in there", "here's what I want to insert", 
"here's what it should like in the end"



-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Thursday, January 30, 2020 11:27 AM
To: SQLite mailing list 
Subject: [sqlite] conditional insert operations

Hi,

Suppose that I have a table with three columns h1, h2, v. I want to
delete all rows with h1=a, and insert rows like the following (data
shown in TSV format), only if there is not an entry with h1=a and
h2="" (empty), it exists but its v is not equal to a value X.

a,A,v1
a,B,v2
...

https://www.sqlite.org/lang_insert.html

I am not sure if there is a way to check an entry to know whether new
entries can be inserted. Could anybody show me an example if this can
be done?

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

Re: [sqlite] Default isolation_level for sqlite3.connect?

[David Raymond]

Incorrect.

"Not specifying one" is
sqlite3.connect(fi)
And the connection will still start implicit transactions for you. (with 
"begin;")

"Setting it to None" is
sqlite3.connect(fi, isolation_level = None)
Which will turn off all implicit transactions, put it in autocommit mode, and 
you have to do all transaction management yourself.


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Thursday, January 30, 2020 11:29 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Default isolation_level for sqlite3.connect?

> and if you don't specify one it issues a plain "begin;"

So that is basically isolation_level = None? Thanks.

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

Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

[David Raymond]

That's just my own personal paranoia wanting to make sure the cursor always 
gets closed, even on an exception. As I don't think the normal context manager 
on a cursor closes it when it exits.

In the real world it's probably overkill as
a) The destructors probably take care of that
b) It's the connection you're more worried about and not the cursor
c) SQLite is designed to handle crashes, etc.


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Thursday, January 30, 2020 11:02 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Does .commit() ensure the .execute()'s and 
.executemany()'s called before are run atomically?

Thanks. What is the purpose of contextlib.

If I just use `cur = conn.cursor()`, what problems it will cause?

> with contextlib.closing(conn.cursor()) as cur:

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

Re: [sqlite] Default isolation_level for sqlite3.connect?

[David Raymond]

https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling-transactions

"You can control which kind of BEGIN statements sqlite3 implicitly executes via 
the isolation_level parameter to the connect() call, or via the isolation_level 
property of connections. If you specify no isolation_level, a plain BEGIN is 
used, which is equivalent to specifying DEFERRED. Other possible values are 
IMMEDIATE and EXCLUSIVE."

https://www.sqlite.org/lang_transaction.html
So basically if you explicitly specify one it will issue either a "begin 
deferred;", "begin immediate;", or "begin exclusive;",
and if you don't specify one it issues a plain "begin;"


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Thursday, January 30, 2020 10:59 AM
To: SQLite mailing list 
Subject: [sqlite] Default isolation_level for sqlite3.connect?

Hi,

I don't see what is the default isolation_level here. Is it None? Thanks.

https://docs.python.org/3/library/sqlite3.html#module-functions-and-constants
sqlite3.connect(database[, timeout, detect_types, isolation_level,
check_same_thread, factory, cached_statements, uri])¶

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

Re: [sqlite] Either a bug or I don't understand SQL update

[David Raymond]

The mailing list strips all attachments, so you'll have to either provide a 
link to it on some external source, or give more create table/insert statements 
like at the bottom.


Without having all the data I'll say: Remember to use single quotes for text 
literals. You have
...and taxitem2.taxrate = "M"...
which with the double quotes means "equals the field named M", and hey, the 
citytax table just so happens to have a field with the name of "m", so my guess 
is that's your problem.


-Original Message-
From: sqlite-users  On Behalf Of 
Paul Ausbeck
Sent: Wednesday, January 29, 2020 6:11 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Either a bug or I don't understand SQL update

I've been using sqlite for some time but haven't used SQL update until 
recently. On my first real use, I've encountered a problem that I can't 
understand at all.

To pare this down, I have two tables, citytax and taxitems2, and I've attached 
a database with just these two tables, total size ~12Kbytes.

I perform the following update using the sqlite3 command line interface:

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and taxitems2.taxrate = "M");

The result is that the salesm  column is set to NULL for all citytax rows. 
However, I would expect that rows 9 and 22 would be non-NULL. I get the the 
same all-NULL result for a taxrate of "G" as well, though I would expect that 
rows 14, 19, and 58 would be non-NULL.

The strangest part is that if I specify taxrates of "P", "R", or "", I get the 
result that I expect. I've included a listing of the taxitems2 table below. As 
one can see, there are rows where the second column, taxrate, is all of "", 
"P", "R", "G", and "M". The last column is the amount column, non-null for all 
rows. I must admit that I don't understand what is going on here.

Perhaps even stranger is if I extract the select from the update and give it an 
appropriate row number, I get the expected non-NULL result:

sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and 
taxitems2.taxrate = "M";
1176.72

I've included the contents of the taxitems2 table as well as the schema for 
tables taxitems2 and citytax below. One the one hand, this seems almost 
certainly to be a bug, but on the other, it is so basic, that I can't believe 
that I'm the first to encounter it.

Any assistance that anyone can provide would of course be much appreciated. I'm 
using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both 
environments behave the same in this regard.

As I was writing I had yet another test idea:

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* 
taxrate = "M" */

and

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* 
taxrate = "G" */

Both of these updates perform as expected.

Regards,

Paul Ausbeck

sqlite> select * from taxitems2;
WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5
SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0
USX,P,58,14,1,0,0,0,0,0,0,7104.0
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
USX,P,58,14,1,0,0,0,0,0,0,5446.4
USX,P,58,14,1,0,0,0,0,0,0,1657.6
AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3
PEM,G,58,10,2,0,0,0.75,2,1.5,4,8
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
HVY,R,58,14,1,0,0,0,0,0,0,72.5
HVY,R,58,14,1,0,0,0,0,0,0,176.4
VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,168
CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2
CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,65
WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9
WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4
WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72
WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3
ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394
DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4
DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8
DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7

sqlite> .schema taxitems2
CREATE TABLE taxitems2(
   code TEXT,
   taxrate TEXT,
   citynum INT,
   countynum INT,
   statenum INT,
   citym NUM,
   cityg NUM,
   countym NUM,
   countyg NUM,
   statem NUM,
   stateg NUM,
   amount
);
sqlite> .schema citytax
CREATE TABLE citytax(
   number INT,
   code INT,
   g NUM,
   m NUM,
   comment TEXT,
   taxg NUM,
   taxm NUM,
   salesg NUM,
   salesm NUM,
   

Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

[David Raymond]

-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Thursday, January 30, 2020 5:16 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Does .commit() ensure the .execute()'s and 
.executemany()'s called before are run atomically?

Could you show a python example on how to make multiple entries either
being all inserted (each entry done by an insert statement) or none on any
error (e.g. ctrl-c)? I also want want to make sure no two python processes
simultaneously editing these entries.

I am not sure I fully understand how to implement this correctly.



Using the manual transactions (isolation_level = None) it might look something 
along the lines of this:



import sqlite3
import contextlib

conn = sqlite3.connect(myFile, isolation_level = None)
try:
with contextlib.closing(conn.cursor()) as cur:
#stuff
cur.execute("begin;")
try:
for thing in otherThing:
cur.execute("insert into myTable values (?,?,?);", thing)
except:
conn.rollback()
print("Error message here")
return someCode #or re-raise the exception
else:
conn.commit()
finally:
conn.close()
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite3 smart enough to remove redundant command?

[David Raymond]

It's going to run both since you asked it to. Even if it was in a compiled 
language where the compiler could look ahead and was looking to implement that 
sort of optimization, then for example there still might be triggers on the 
table which would need to be run, or other constraints on the fields which 
might get triggered by the second statement and not the first. There's no way 
it's going to know that without actually running them both.


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Wednesday, January 29, 2020 12:04 AM
To: SQLite mailing list 
Subject: [sqlite] Is sqlite3 smart enough to remove redundant command?

Suppose that I have the following command, which writes two entries
with the same key. So the 1st entry will be overwritten by the 2nd
entry. Therefore, there is no need to write the 1st entry. Is sqlite3
smart enough to not to write the 1st entry? Or it will write both the
1st entry and the 2nd entry? Thanks.

conn=sqlite3.connect("my.db")
c=conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS sqlar(
name TEXT PRIMARY KEY
, mode INT
, mtime INT
, sz INT
, data BLOB)
''')
c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', ["a", 0, 0, 1, "1"])
c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', ["a", 0, 0, 1, "2"])
conn.commit()

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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[David Raymond]

This is technically valid CASE syntax which is why you're not getting an error, 
it's just not what you're looking for.
...
CASE
(SELECT c.WYear FROM t2 WHERE pid = a.a)
WHEN c.WYear = 2020 THEN “YES” ELSE “NO” END
) AS DIGITAL
...

What that is saying is take the value you get from this:

(SELECT c.WYear FROM t2 WHERE pid = a.a)

and compare it to the value you get from this:

(c.WYear = 2020)

and if those two values match, then return the value of the field that's named 
"YES" (I'm assuming you wanted 'YES' there)

(c.WYear = 2020) is a perfectly valid expression... that's returning a boolean 
(well, int)
So you're comparing c.WYear (from the subquery) against a boolean.


(Others have replied with improved versions of the query, but for people 
following at home I figured I'd try to point out why the original version 
parsed ok and ran, just wasn't what you intended)

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

Re: [sqlite] Capturing the changes in columns in a table

[David Raymond]

A note and a question on subqueries. On reading:

select
...
(select b from t where a == new.a and idate < new.idate order by idate desc) as 
oldv,
...

My brain started yelling that that needed a "limit 1" on the subquery so that 
it would only return 1 row.

I looked in the SQLite docs though and it seems like I'm wrong.
https://www.sqlite.org/lang_expr.html
"The value of a subquery expression is the first row of the result from the 
enclosed SELECT statement."

Then to make sure my brain was remembering correctly I checked Postgres and 
found this.
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
"It is an error to use a query that returns more than one row or more than one 
column as a scalar subquery."


So my questions then are:

How is that handled by other databases?

Is that something worthy of being on the quirks page 
(https://www.sqlite.org/quirks.html), or is it just in the realm of non-quirky 
normal-level differences?


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

Re: [sqlite] Unexplained table bloat

[David Raymond]

Well, I believe this is the relevant bit from the docs for binding:
https://www.sqlite.org/c3ref/bind_blob.html

"If a non-negative fourth parameter is provided to sqlite3_bind_text() or 
sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter must be the 
byte offset where the NUL terminator would occur assuming the string were NUL 
terminated. If any NUL characters occur at byte offsets less than the value of 
the fourth parameter then the resulting string value will contain embedded 
NULs. The result of expressions involving strings with embedded NULs is 
undefined. "


-Original Message-
From: sqlite-users  On Behalf Of 
Barry Smith
Sent: Monday, January 13, 2020 1:54 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Unexplained table bloat

On the original topic...

How does one end up with a database in this state? I.e with a binary value that 
contains 0x00 bytes followed by other bytes but a type of TEXT?

If the definition of a text string in SQLite is that it ends at the first 0x00 
byte, then it seems that anything stored as a text string should adhere to that.

So a database with a TEXT value that contains characters after the first 0x00 
should be considered corrupt. Given that to retrieve the actual contents of the 
cell it must be cast to BLOB, why not force the storage of any string that 
contains 0x00 as a BLOB in the first place?

What am I missing here?

On 13 Jan 2020, at 6:02 am, Simon Slavin  wrote:
> 
> On 13 Jan 2020, at 9:26am, Dominique Devienne  wrote:
> 
>> Which implies length(text_val) is O(N), while
>> length(blob_val) is O(1),
>> something I never quite realized.
> 
> For this reason, and others discussed downthread, some languages which store 
> Unicode strings store the number of graphemes as well as its contents.  So 
> functions which care about the … let's call it "width" … just retrieve that 
> number rather than having to parse the string to figure out the length.
> 
> In a Unicode string 'length' can mean
> 
> 1) octet count (number of 8-bit bytes used to store the string)
> 2) number of code points (basic unicode unit)
> 3) number of code units (how code points get arranged in UTF8, UTF16, etc., 
> not as simple as it looks)
> 4) length in graphemes (space-using units)
> 5) length in glyphs (font-rendering units)
> 
> and probably others I've forgotten.  Not to mention that I simplified the 
> definitions of the above and may have got them wrong.
> 
> An application centred around rendering text (e.g. vector graphics drawing 
> apps) might have each piece of text stored with all five of those numbers, 
> just to save it from having to constantly recalculate them.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexplained table bloat

[David Raymond]

Well something's weird anyway. When I open it with the command line tool it 
queries it just fine. I tried to open it in Python to check all the characters 
in the strings and got this:

Traceback (most recent call last):
  File "...\Testing4.py", line 8, in 
cur.execute("select * from copied;")
sqlite3.OperationalError: Could not decode to UTF-8 column 'LUTFullString' with 
text ','


Which I suppose it just as likely to be my own problem though.


-Original Message-
From: sqlite-users  On Behalf Of 
Ryan Mack
Sent: Friday, January 10, 2020 8:48 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Unexplained table bloat

Hi list,

I'm trying to understand unexplained table bloat I found in what should be
a very small table in an old database file. If you dump/restore the
database, the problem goes away. If you duplicate the table, the problem
propagates. Schema:

CREATE TABLE copied(
  id_local INT,
  LUTFullString,
  LUTHash
);

Test Data:
85960605|,|0DE19F8AA100D2962FF22C60B3FA8940
85960465|,|A44615408E8B3E48A684C60CA2967031
85960451|,|33C8804354984071A740788AD308B279

In my test database file, each of these 3 rows is allocating about 40 4k
overflow pages for a total database size of about 500k. The full database
has about 4MB of actual data which takes up over 500MB on disk. If you want
to see/reproduce the problem you'll need my test database file which I've
uploaded here: https://mackman.net/200k-per-row.sqlite.zip (500kb
download). I don't know why it doesn't compress better, those extra
overflow pages must be getting filled with random garbage.

My uninformed guess is there was a bug in the version of sqlite used at
database creation time that computed an incorrect overflow threshold and is
storing each byte of the row to its own page. Since the problem goes away
with a dump/restore, I'm considering releasing a script to do that and
mitigate the problem for affected users. Before doing that I would like to
understand the problem better.

Thanks for reading, Ryan

PS: Here's some output from my debug session showing the 123 bytes of data
is occupying 582k of space on disk in a freshly created table.

% sqlite3 200k-per-row.sqlite
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
sqlite> .tables
copied

sqlite> .schema copied
CREATE TABLE copied(
  id_local INT,
  LUTFullString,
  LUTHash
);

sqlite> select * from copied;
85960605|,|0DE19F8AA100D2962FF22C60B3FA8940
85960465|,|A44615408E8B3E48A684C60CA2967031
85960451|,|33C8804354984071A740788AD308B279

sqlite> select sum(length(id_local) + length(lutfullstring) +
length(luthash)) from copied;
123

sqlite> create table copied2 as select * from copied;
sqlite> SELECT name, SUM("pgsize") as sz FROM dbstat group by name;
copied|581632
copied2|581632
sqlite_master|4096
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How create database, tables,indices...

[David Raymond]

To create a new database you just open the file name you want, and if it 
doesn't exist it will be created.

The actual new file creation is slightly delayed until it has to write 
something to the disk. There are a couple of options that can only be set at 
file creation time like page size, text encoding, file format, etc. So if you 
wanted any non-default settings for those you would set those first thing after 
opening it and before doing anything else, so that they are set _before_ the 
actual file itself is created.


-Original Message-
From: sqlite-users  On Behalf Of 
Andy
Sent: Tuesday, January 7, 2020 9:23 AM
To: SQLite mailing list 
Subject: Re: [sqlite] How create database, tables,indices...

I see: http://zetcode.com/db/sqlitec/
New database is simply open not existing file in path?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size limits

[David Raymond]

Along with the other replies already mentioned I'll pipe in with a reminder 
that any large blob fields should be the final field in a table's definition. 
Due to how the data is stored, to get the data for any particular field, SQLite 
has to go through/decode the data for all previous fields in a record.

So if you have a layout of...

id integer primary key,
big_blob_field blob,
some_field_you_want text

... then if you "select some_field_you_want from table;" and a record has a 1GB 
blob in big_blob_field, then it'll have to go through 1GB of linked list 
overflow pages to get the value for some_field_you_want. (Some optimizations 
may apply)

So be sure to define it as

id integer primary key,
little_field_1 text,
little_field_2 int,
little_field_3 float,
big_blob_at_the_end blob



-Original Message-
From: sqlite-users  On Behalf Of 
Andy
Sent: Tuesday, January 7, 2020 5:30 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Size limits

What are reasonable limits for size Sqlite3 database file and large blobs?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between hex notation and string notation

[David Raymond]

Run...
pragma compile_options;
...and see if LIKE_DOESNT_MATCH_BLOBS is in the list that comes up.

If it is NOT in the list then both rows will show up. This is the case for the 
precompiled Windows cli for example.

If it IS in the list, then only the second one that was inserted as text will 
show up, and the blob will not be selected.

https://www.sqlite.org/compile.html#like_doesnt_match_blobs

SQLITE_LIKE_DOESNT_MATCH_BLOBS

This compile-time option causes the LIKE operator to always return False if 
either operand is a BLOB. The default behavior of LIKE is that BLOB operands 
are cast to TEXT before the comparison is done.

This compile-time option makes SQLite run more efficiently when processing 
queries that use the LIKE operator, at the expense of breaking backwards 
compatibility. However, the backwards compatibility break may be only a 
technicality. There was a long-standing bug in the LIKE processing logic (see 
https://www.sqlite.org/src/info/05f43be8fdda9f) that caused it to misbehavior 
for BLOB operands and nobody observed that bug in nearly 10 years of active 
use. So for more users, it is probably safe to enable this compile-time option 
and thereby save a little CPU time on LIKE queries.

This compile-time option affects the SQL LIKE operator only and has no 
impact on the sqlite3_strlike() C-language interface.


Since I just pasted it there's typo in there. "caused it to misbehavior" should 
be "caused it to misbehave"


-Original Message-
From: sqlite-users  On Behalf Of 
Jose Isaias Cabrera
Sent: Friday, December 13, 2019 2:58 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Difference between hex notation and string notation


Sascha Ziemann, on Friday, December 13, 2019 04:16 AM, wrote...
>
> I have a problem to find rows in a database when I write in hex notation:
>
> CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
> INSERT INTO LOG VALUES
>
> (X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
> INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
> down');
> SELECT ROWID,MSG FROM LOG; --
> returns both rows
> SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down';  --
> returns just the second
> SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; --
> returns both rows
>
> This looks like a bug to me.

Update to 3.30.0 or higher.  It works fine for me...
14:55:34.46>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
sqlite> INSERT INTO LOG VALUES
   ...> 
(X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
sqlite> INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
   ...> down');
sqlite> SELECT ROWID,MSG FROM LOG;
1|facility=daemon;component=named;text=shutting down
2|facility=daemon;component=named;text=shutting
down
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] wal mode

[David Raymond]

"So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the
sqlite3 cli, for all future connections from any tool will use WAL mode for
this database file?"


Yup, the journal mode is stored in the database header. So the pragma will 
update the file's header, and any new connection will read the header and find 
out the mode to use from there.

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

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[David Raymond]

Those lines on their own aren't any significantly slower or faster than each 
other.

What's happening is that when you add in a function the planner has a harder 
time determining if it can use certain optimizations. The "is not null" version 
is simple enough where the planner says "ohh, I'm ok to do something nifty here 
if I think it will help". And that "something nifty" is what results in the 
join order being reversed. (Because it doesn't have any info on the sizes of 
the tables to make a more informed decision with)

In your case for example the two lines are not equivalent. If R.ID_ARGDAJ is 0, 
then the "is not null" version would include it, but the "ifnull" version would 
not. In general though the query planner "does not do algebra" to see if two 
things are functionally equivalent even if they're written slightly differently.

Some similar situation have come up here where the solution was simply to 
replace a reference of "foo" with "+foo" because simply adding the unary plus 
operator disables some optimizations.


-Original Message-
From: sqlite-users  On Behalf Of 
radovan5
Sent: Friday, December 6, 2019 10:16 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Changes in version 3.28.0 makes sql run slower

Can you explain why workaround is fast. Why one version is fast and
other slow. I'm talking about this change in where:

"and R.ID_ARHDAJ is not null"

to

"and ifnull(R.ID_ARHDAJ,0) <> 0"

For me analyze is no improvement because data is loaded from RDBMS
and would have to run always after load.

Regards Radovan

On 06.12.2019 14:20, Richard Hipp wrote:
> On 12/6/19, Keith Medcalf  wrote:
>> Perhaps the optimizer could make a "left join" that is not actually an outer
>> join into a "cross join" and preserve the nesting order ... ?
>>
> It could do that, but that would kind of defeat the whole purpose of
> the optimization, which was to free up the planner to choose a
> different nesting order in cases where the LEFT JOIN didn't really
> matter.
>
> I suspect that ANALYZE might also solve the OP's problem.

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

Re: [sqlite] Query freezing on latest SQLite 3.30.1 build

[David Raymond]

The mailing list strips off all attachments, so you'll have to provide another 
place to get that.

Alternatively could you post the schema here as text, along with the explain 
query plan output from the slow version and from a fast version?

-Original Message-
From: sqlite-users  On Behalf Of 
Clovis Ribeiro,MyABCM
Sent: Thursday, December 5, 2019 2:04 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Query freezing on latest SQLite 3.30.1 build

Folks,

The following query, when executed against the attached database using SQLite 
3.30.1 (running on Windows OS) will take forever to execute. If we remove all 
columns from both tables that are not actually used in the query, it is 
executed in milliseconds.

SELECT COUNT(*) FROM
(SELECT
 src.member_id src_id,
 dst.member_id dst_id,
 asg.contribution_percentage
FROM
 mdl_assignments asg
INNER JOIN
 mdl_member_instances src ON asg.source_mbi_id = src.id
INNER JOIN
 mdl_member_instances dst ON asg.destination_mbi_id = dst.id
WHERE
 src.period_scenario_id = 1 AND dst.period_scenario_id = 1) T

When testing the same query with older versions of SQLite we used in the past 
(more than 6 years ago), the query also executed in milliseconds.

We have executed several different tests but could not figure out why this 
query hangs on the latest version of SQLite but runs fast in older versions or 
when we remove columns from the tables in the database.

Hope this can help you improve SQLite.

Thanks

Clovis Ribeiro
MyABCM

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

Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?

[David Raymond]

SQLite is pretty good at using free space inside the file. So if inserting 
something is going to run you out of space, then it's going to run you out of 
space whether the file was previously vacuumed or not.

Also reminder that when vacuum is run, SQLite makes a brand new copy of the 
database, then goes through and updates the pages of the original file, which 
requires writes to the rollback journal. So if your database is size n. Then 
the worst case scenario is that vacuum will peak out at using 3n worth of disk 
space. (Original file, copy, journal) So if your database is already 90% of 
your storage, then you're gonna have a hard time vacuuming it anyway.

You could consider using incremental vacuum to clean up free space without 
re-creating the whole file, but that has to be enabled when the database file 
is created. Again though, that only frees up unused space. If an insert is 
making your database size bigger, then you don't have any unused space to clean 
up.


-Original Message-
From: sqlite-users  On Behalf Of 
Zhu, Liang [AUTOSOL/ASSY/US]
Sent: Thursday, December 5, 2019 1:32 PM
To: SQLite mailing list 
Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?

If I do not do Vacuum,  my database size just keep raising,  eventually the 
database size gets to  over 90% of storage size,  I can save data to the 
database any more.

Thank you,
Liang

-Original Message-
From: sqlite-users  On Behalf Of 
Gerry Snyder
Sent: Thursday, December 5, 2019 12:12 AM
To: SQLite mailing list 
Subject: [EXTERNAL] Re: [sqlite] what is the optimized way to do the vaccum?

On Wed, Dec 4, 2019, 9:28 PM Zhu, Liang [AUTOSOL/ASSY/US] < 
liang@emerson.com> wrote:

> All Sqlite Expert,
>
> I have one table,  I am inserting and deleting record to and from this 
> table very 250ms.  I always maintain 1000 rows in this table.  I have 
> another table,  I am inserting and deleting data to and from this 
> table every 1s.  The data record in this table maintains at 200,000 rows.
> Can I get some recommendation on what is optimized technique to do the 
> vaccum for my database?
>
> Thank you,
> Liang
>

Why do you think that you need to vacuum at all?


> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit
> e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=jOURTkCZzT8
> tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DF
> EINZ4E6I=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M=1HGpuZT8Hu2Bp
> siRzJ8yujtxh3m_XyAXLThncurjn-M=
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M=1HGpuZT8Hu2BpsiRzJ8yujtxh3m_XyAXLThncurjn-M=
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA for .load

[David Raymond]

I _think_ the load_extension() function is what you'll be looking for, though I 
could be wrong.

https://www.sqlite.org/lang_corefunc.html#load_extension


-Original Message-
From: sqlite-users  On Behalf Of 
Jose Isaias Cabrera
Sent: Tuesday, December 3, 2019 3:38 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] PRAGMA for .load


Greetings.  Quick question...

I was looking into the pragmas page, and I don't see one that would do the 
function to load other libraries, such as the .load function of the CLI.  Is 
there one?  Thanks.

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

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[David Raymond]

Not the reason for the slowdown, but note that both of these are redundant:

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
 data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
 ignored_id
);

...because you declared them as the primary keys in the table creation. So you 
now have 2 different indexes on the exact same data for each of those.


The rest of it looks fine to me anyway, and I'm not sure why you'd be seeing 
such slow times. Old slow hard disk?

If you analyze and vacuum it does it get any better?

I think the CLI has something like ".scanstats on" to get a little more info, 
but I'm not sure how much more info it'll provide.


-Original Message-
From: sqlite-users  On Behalf Of 
Hick Gunter
Sent: Tuesday, November 26, 2019 4:57 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

You are using text columns as primary keys and referencing them directly in 
foreign keys. This is probably not what you want, because it duplicates the 
text key. Also, with foreign keys enabled, your join is not accomplishing 
anything more than a direct select from joining_table, just with more effort 
(and circumventing the count() optimization).

SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the 
table. This is what you should be using as a foreign key, because it is twice 
as fast as using an index.

OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those 
too

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jonathan Moules
Gesendet: Dienstag, 26. November 2019 10:25
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes

Hi List,
I have a relational table setup where I've built indexes but I'm still seeing 
very slow join times on middling amounts of data. I'm guessing I'm doing 
something wrong but I can't see what. (SQLite: 3.24.0)

Simplified schema as below.
The ids are 16 character hex strings. I've included the ignore_me table only 
because it's relevant to the indexes.
Note: I can *guarantee* that the data inserted into `data_table` and 
`ignore_me` is ordered by their respective primary keys ASC. Entries in 
joining_table are ordered by one of either data_id ASC or ignored_id ASC 
depending on creation method.

--==

-- 1.7 million items
CREATE TABLE data_table (
 data_idTEXT PRIMARY KEY
 NOT NULL
 COLLATE NOCASE,
 data_1TEXT,
 data_2 TEXT );

-- 1.9 million items
CREATE TABLE joining_table (
 data_id TEXT REFERENCES data_table (data_id)
 NOT NULL
 COLLATE NOCASE,
 ignored_id TEXTREFERENCES ignore_me (ignored_id)
 NOT NULL
 COLLATE NOCASE,
 misc_col_1TEXT,
 misc_col_2TEXT
);

-- ~200,000 items
CREATE TABLE ignore_me (
 ignored_idTEXT PRIMARY KEY
 NOT NULL
 COLLATE NOCASE );

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
 data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
 ignored_id
);

-- Allow quick joining from data_table to ignore_me CREATE INDEX IF NOT EXISTS 
joining_table__data_ignored_id__fk_idx ON joining_table (
 data_id ASC,
 ignored_id ASC
);
-- Allow quick joining from ignore_me to data_table CREATE INDEX IF NOT EXISTS 
joining_table__ignored_data_id__fk_idx ON joining_table (
 ignored_id ASC,
 data_id ASC
);

-- Example data:

INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6'); INSERT INTO 
data_table (data_id) VALUES ('579c57f1268c0f5c');

INSERT INTO ignore_me VALUES ('c402eb3f05d433f2'); INSERT INTO ignore_me VALUES 
('d827e58953265f63'); INSERT INTO ignore_me VALUES ('ec1d2e817f55b249');

INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 
'c402eb3f05d433f2'); INSERT INTO joining_table (data_id, ignored_id) VALUES 
('00196a21e8c0f9f6', 'd827e58953265f63'); INSERT INTO joining_table (data_id, 
ignored_id) VALUES ('579c57f1268c0f5c', 'ec1d2e817f55b249');




-- Then to test the speed I'm simply doing:
 SELECT
 count(1)
 FROM
 data_table
 JOIN joining_table USING (data_id);

--==

The query plan says it's using the indexes:
 SCAN TABLE joining_table USING COVERING INDEX 
joining_table__ignored_data_id__fk_idx
 SEARCH TABLE data_table USING COVERING INDEX data_table__data_id__pk_idx 
(data_id=?)

But it takes about 20 seconds to do that count on 

Re: [sqlite] Shell commands for controlling headers

[David Raymond]

Dr Hipp replied to this 2 days ago with this:


Documentation fix https://www.sqlite.org/docsrc/info/a2762f031964e774
will appears in the next release.

".header" is an abbreviation for ".headers" and does exactly the same thing.


-Original Message-
From: sqlite-users  On Behalf Of 
John McKown
Sent: Monday, November 25, 2019 9:51 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Shell commands for controlling headers

On Mon, Nov 25, 2019 at 8:42 AM Craig Maynard  wrote:

> All,
>
> Could someone clarify the difference between the two sqlite3 shell
> commands .header and .headers?
>
> The relevant documentation page: https://www.sqlite.org/cli.html
>
> On the cli page, .header is discussed in section 5 but does not appear in
> Section 3.
>
> Thanks,
> Craig
>
> --
> Craig H Maynard
> Rhode Island, USA
>
>
In the sqlite cli itself, doing an ".help", I see:

.header(s)

So I am guessing that they are the same things, perhaps for compatibility
with something in the past.

-- 
People in sleeping bags are the soft tacos of the bear world.
Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE IN

[David Raymond]

There'll be a few differences.

The JOIN version will return 1 row for every item in queries which matches, and 
it will test every single one every time. So if you have in the queries table 
both 'Alex' and 'Alexand' then 'Alexander' and 'Alexandra' will each show up 
twice, once for 'Alex' and once for 'Alexand'. Depending on what you're doing 
this may be what you want.

The EXISTS version will only ever return one row for each record in the names 
table, and it will stop checking other patterns once it finds one that matches.

So if you want any info from the queries table then go with the join route, if 
you only care if yes/no there's anything at all that matches, then go with 
exists.



-Original Message-
From: sqlite-users  On Behalf Of 
Gert Van Assche
Sent: Saturday, November 23, 2019 5:43 AM
To: SQLite mailing list 
Subject: Re: [sqlite] LIKE IN

Both queries will work like this:

DROP TABLE names;
CREATE TABLE names (name TEXT);
INSERT INTO names VALUES ('Alex');
INSERT INTO names VALUES ('Alexander');
INSERT INTO names VALUES ('Alexandra');
INSERT INTO names VALUES ('Rob');
INSERT INTO names VALUES ('Rhobin'); -- should not match
INSERT INTO names VALUES ('Robert');

CREATE TABLE queries (query TEXT);
INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';

SELECT name from names
where exists (
select query from queries
where names.name like '%'||query||'%'
);


On Sat, 23 Nov 2019 at 11:34, Gert Van Assche  wrote:

> I think this will work:
>
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> select * from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
> On Fri, 22 Nov 2019 at 15:19, David Raymond 
> wrote:
>
>> Or alternatively something like:
>>
>> select * from table
>> where exists (
>> select query from queries
>> where table.name like query
>> );
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE IN

[David Raymond]

Or alternatively something like:

select * from table
where exists (
select query from queries
where table.name like query
);

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

Re: [sqlite] Optimising query with aggregate in subselect.

[David Raymond]

"There's a small sidenote (that I'm too lazy too find right now) in the select 
docs that mentions that, in case of using min or max as aggregate, the 
non-aggregate columns will come from the row that held the min/max value."


Look in
https://www.sqlite.org/quirks.html
under "6. Aggregate Queries Can Contain Non-Aggregate Result Columns That Are 
Not In The GROUP BY Clause"

and also in
https://www.sqlite.org/lang_select.html
In section 3 search for: "Side note: Bare columns in an aggregate queries."
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use CASE statement to SUM() some numbers

[David Raymond]

So why do you need a case? What will not work with the simple:

select sum(Expense)
from Expenses
where Date between date('now', '-1 months') and date('2019-11-04', '-1 days');

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

Re: [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions

[David Raymond]

Apparently it got smarter about "primary key unique" in 3.20.0 and stopped 
making the extra index when it's a without rowid table. Don't see anything 
about it in the release notes though.

Even on the current release "primary key unique" will still make an extra index 
for the unique if it's a rowid table.



-Original Message-
From: sqlite-users  On Behalf Of 
Vincas Dargis
Sent: Thursday, November 14, 2019 4:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions

Hi list,

Accidentally, when performing VACUUM using rather old SQLite 3.16.2
(from Debian 8 stretch) binary on some database file created with more
recent SQLite (like 3.29.0 available in Qt 5.13.2 or a bit older),
I've discovered that database now has a few more `sqlite_autodinex_*`
entries in `sqlite_master` table (checked by test suite).

I was puzzled at the beginning, but after some fiddling I see that
creating this table:
```
CREATE TABLE "equipment_type"(
  id INTEGER NOT NULL PRIMARY KEY UNIQUE,
  name TEXT NOT NULL UNIQUE
) WITHOUT ROWID;
```
with recent SQLite, we get this set (only one) of internal indexes:
```
SELECT * from sqlite_master WHERE name LIKE 'sqlite_autoindex_eq%'
index sqlite_autoindex_equipment_type_2 equipment_type 3
```
Meanwhile, if I create same table using older 3.16.2 (on Debian 9
stretch), I get indexes for two fields:
```
index|sqlite_autoindex_equipment_type_1|equipment_type|3|
index|sqlite_autoindex_equipment_type_2|equipment_type|4|
```

It feels strange if UNIQUE and PRIMARY KEY did not provoke creating
`sqlite_autoindex`, but maybe we are missing something? Is this
behavior change expected?

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

Re: [sqlite] Why do these 2 updates give different results?

[David Raymond]

Why not change it to something along the lines of:

UPDATE QR3PARAMS
SET ED = 1
WHERE
ED is not 1
AND EXISTS (
  SELECT 1
  FROM CURRENT_MED
  WHERE
  ID = QR3PARAMS.ID
  AND (
TERM_TEXT GLOB 'Sildenafil*'
OR
TERM_TEXT GLOB 'Tadalafil*'
OR
TERM_TEXT GLOB 'Vardenafil*'
  )
);

which would leave ED alone if it didn't find anything,
or the following, which would always set it to true or false

UPDATE QR3PARAMS
SET ED = EXISTS (
  SELECT 1
  FROM CURRENT_MED
  WHERE
  ID = QR3PARAMS.ID
  AND (
TERM_TEXT GLOB 'Sildenafil*'
OR
TERM_TEXT GLOB 'Tadalafil*'
OR
TERM_TEXT GLOB 'Vardenafil*'
  )
)
WHERE
ED is not 1
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why do these 2 updates give different results?

[David Raymond]

"Why is ED changed to '' or NULL for ID 5?"

When you update to a subquery which returns no rows, then the field gets 
updated to null.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deterministic random sampling via SELECT

[David Raymond]

We went off on a tangent, apologies.

If you have contiguous integer primary keys, you could randomly sample that 
range of integers, then pull the records with those keys.

Or in your external language of choice, sample the integers from 1 to the 
record count deterministically, select ordered by the primary key, and take the 
ones with the sampled offsets. (Stepping through 1 query and NOT doing a bunch 
of ...order by pk limit 1 offset n... queries)

Making something quick in Python I might do something like:


import random
import sqlite3

conn = sqlite3.connect(dbFile, isolation_level = None)
cur = conn.cursor()
cur.execute("select count(*) from foo;")
numRecords = cur.fetchone()[0]
sampleSize = 10
random.seed(5) #Your deterministic seed here
SampleOffsets = random.sample(range(1, numRecords + 1), sampleSize)
SampleOffsets.sort()
cur.execute("select * from foo order by primary_key;")
currentOffset = 0
for selectedOffset in SampleOffsets:
for _ in range(selectedOffset - currentOffset - 1):
cur.fetchone()
nextSampleRecord = cur.fetchone()
currentOffset = selectedOffset
doSomethingWithSample(nextSampleRecord)



-Original Message-
From: sqlite-users  On Behalf Of 
Merijn Verstraaten
Sent: Thursday, November 7, 2019 2:16 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Deterministic random sampling via SELECT


> On 7 Nov 2019, at 19:16, David Raymond  wrote:
> 
> Along those lines SQLite includes the reverse_unordered_selects pragma
> https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects
> which will flip the order it sends rows in queries that don't explicitly 
> specify an ordering. It's there to assist you in finding spots in your code 
> where you might be relying on implicit ordering when you really shouldn't be.

Like the rest of this threads, this is just pointing out why the things in my 
initial email don't work, but I already knew that. Which is why I asked for 
help to see if there is a way to do what I want that *does* work. I don't care 
particularly about the details of "can I control the order the condition is 
evaluated", it's just that all reasonable ways to sample large streams that I 
know would require a deterministic order.

If someone has a different/better idea on how to return just a random sample 
from a query in a repeatable way, I'm all ears.

So far the only suggestion was "use some non-deterministic random sampling 
method and store the result", but since my samples are large and I have lots of 
them, this would balloon my storage by >100x and I don't have the available 
storage to make that work.

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

Re: [sqlite] Deterministic random sampling via SELECT

[David Raymond]

Along those lines SQLite includes the reverse_unordered_selects pragma
https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects
which will flip the order it sends rows in queries that don't explicitly 
specify an ordering. It's there to assist you in finding spots in your code 
where you might be relying on implicit ordering when you really shouldn't be.

Also available as a compile time option: SQLITE_REVERSE_UNORDERED_SELECTS


-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Thursday, November 7, 2019 12:16 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Deterministic random sampling via SELECT

On 7 Nov 2019, at 1:56pm, David Raymond  wrote:

> Others will correct me if I'm wrong on that.

No correction, but I wanted to add something.

According to the theory of how SQL (not just SQLite, SQL) works, tables have no 
order.  You can, in theory, query a table of 100 rows with

SELECT a,b FROM MyTable LIMIT 5

ten times and get ten different answers, including different rows and/or the 
same rows in different orders.  Given some of the text in the post that started 
this thread, I just wanted to make sure this was understood.

In practise I have never seen a SQL engine which does this.  Each SQL 
implementation seems to return the same result every time you repeat the same 
query.  Though different SQL implementations can return different results.

You'd have thought that at least one server/client system would return five 
rows which happened to be in the cache, but I've never seen this.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deterministic random sampling via SELECT

[David Raymond]

"So, is this behaviour documented/guaranteed somewhere?"
Short version is: Nope. The engine is free to do whatever it wants as long as 
it gives the correct result in the end.

Consider a simple
select * from foo where predicate order by non_indexed_field;
Since there is no nice ordering of the data already it's going to have to sort 
it. In which case it's probably going to check the predicate against records on 
the way _in_ to the sorter rather than _after_ sorting. Think "I might only 
have to sort 5 things instead of 5 million, so let's filter out as much as we 
can as soon as possible." And since the same data could be on the disk with its 
pages in any order you could conceive a situation where the same data could be 
processed differently depending on the specific file that it's in. The same 
query could process the data in a different order before and after a vacuum for 
example.

Or maybe it does sort first then check. But that's an internal detail which 
could change every version. And all that is all considered fine, as the end 
result of the query will still be correct and in the order specified.

The closest thing you can do is limit a query to using a specific index during 
a query, but even then you're basically relying on implementation details, and 
not a guarantee.

Others will correct me if I'm wrong on that.



-Original Message-
From: sqlite-users  On Behalf Of 
Merijn Verstraaten
Sent: Thursday, November 7, 2019 6:55 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Deterministic random sampling via SELECT

I'm trying sample a (deterministically) random subset of a SELECT query, the 
most common solution on the internet to get random samples seems to be "SELECT 
* FROM (...) ORDER BY RANDOM() LIMIT n;" (this already has some question marks, 
since it relies on seeding RANDOM and knowing the RANDOM function is always 
evaluated in the same order every query run), but looking at the query plans 
this materialises the entire result set in memory for the sort (no surprise, I 
can't think of anyway that could work otherwise) which is rather undesirable if 
the sample size becomes large (i.e. several million rows).

Now, I already know different ways to implement a predicate function that can 
deterministically keep elements from a stream, however that relies on having a 
deterministic order for the stream. Which brings us to SQLite. I can easily 
write something like:

SELECT *
FROM (...)
WHERE my_predicate_fun()
ORDER BY column1, column2,...

And this *seems* to evaluate the where clause for each row in the order 
determined by ORDER BY, but this doesn't seem at all guaranteed by the SQL 
spec. So, is this behaviour documented/guaranteed somewhere? If not, is there 
some way to guarantee my where clause is evaluated for each row in a 
deterministic order?

In the simple case like above I could always just evaluate the query without 
the ORDER BY, step through the entire query, and evaluate the predicate in the 
application, but if I want to use this random selection as a subquery, then 
that doesn't work.

And while I'm asking questions: What if I want to do the above, but selecting 
groups of rows? So, sort of like:

SELECT *
FROM (...)
GROUP BY groupColumn
HAVING my_predicate_fun();

But where I want to return all rows in the group, rather than an aggregate.

Thanks in advance,
Merijn
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find stmt that's stopping a DB from closing

[David Raymond]

I think you're referring to "The SQLITE_STMT Virtual Table":
https://www.sqlite.org/stmt.html


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Tuesday, November 5, 2019 2:27 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Find stmt that's stopping a DB from closing

I’m sure the team added a fct that listed the offenders but I can’t find it in 
the documentation. I would’ve thought it would have been mentioned on the 
sqlite3_close page but no sign of it. Am I imagining things?



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

Re: [sqlite] DELETE extremely slow

[David Raymond]

Looks like you have triggers going on there. You only gave us the table and 
index definitions. What are the on delete triggers you have?


-Original Message-
From: sqlite-users  On Behalf Of 
Thomas Kurz
Sent: Thursday, October 31, 2019 6:54 PM
To: SQLite mailing list 
Subject: Re: [sqlite] DELETE extremely slow

>  Do you have memory to run this in?  Have you increased the sqlite cache size 
> because that looks (to me) an awful lot like I/O thrashing ...

Sorry to disappoint you, Keith and Simon, but in all cases the database file 
has been located on a ramdisk. It's only about 50 MB in size, btw. 

> SQLite runs on the local machine. While MariaDB is client-server, so the 
> delete effectively runs on the server.

Yes and no. Of curse, I had MariaDB run on the same machine, and its data files 
had been stored on the same ramdisk.

> How much of the 88 minutes is "waiting" time?

I did it again, same file:

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> pragma foreign_keys=on;
sqlite> .timer on
sqlite> delete from dataset;
Run Time: real 5249.891 user 2412.812500 sys 2606.531250

> You haven't shown the "explain query plan" Keith asked for

Is the beginning of it enough or do I have to repeat the entire DELETE? Here is 
the output which I canceled after some seconds:

QUERY PLAN
|--SCAN TABLE dataset
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?)
|--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?)
|--SCAN TABLE item
|--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?)
|--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?)
|--SCAN TABLE item
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?)
|--SCAN TABLE item
|--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?)
`--SCAN TABLE item
addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Init   0 30000  Start at 30  
1 Null   0 1 000  r[1]=NULL
2 OpenRead   0 7 0 0  00  root=7 iDb=0; dataset
3 Rewind 0 7 000   
4   Rowid  0 2 000  r[2]=rowid   
5   RowSetAdd  1 2 000  rowset(1)=r[2]
6 Next   0 4 001   
7 OpenWrite  0 7 0 3  00  root=7 iDb=0; dataset
8   RowSetRead 1 29200  r[2]=rowset(1)
9   NotExists  0 282 1  00  intkey=r[2]  
10  Copy   2 3 000  r[3]=r[2]
11  OpenRead   2 170 k(3,,,)02  root=17 iDb=0; 
metadata_idx_04
12  IsNull 3 18000  if r[3]==NULL goto 
18
13  Affinity   3 1 0 C  00  affinity(r[3])
14  SeekGE 2 183 1  00  key=r[3] 
15IdxGT  2 183 1  00  key=r[3] 
16FkCounter  0 1 000  fkctr[0]+=1  
17  Next   2 15000   
18  OpenRead   4 13869  0 k(2,,) 02  root=13869 iDb=0; 
trace_idx_03
19  IsNull 3 25000  if r[3]==NULL goto 
25
20  Affinity   3 1 0 C  00  affinity(r[3])
21  SeekGE 4 253 1  00  key=r[3] 
22IdxGT  4 253 1  00  key=r[3] 
23FkCounter  0 1 000  fkctr[0]+=1  
24  Next   4 22100   
25  Delete 0 1 0 dataset00   
26  Program3 0 9 program00  Call: fkey.abort
27  Program3 0 10program00  Call: fkey.abort
28Goto   0 8 000   
29Halt   0 0 000   
30Transaction0 1 400  01  usesStmtJournal=0
31Goto   0 1 000   
0 Init   0 1 000  Start at 1; Start: 
.abort (AFTER DELETE ON dataset)
1 Null   0 1 000  r[1]=NULL
2 OpenRead   11170 k(3,,,)02  root=17 

Re: [sqlite] DELETE extremely slow

[David Raymond]

It's not gonna account for 88 minutes, but out of curiosity is there a way to 
separately report the "delete and commit" time from the "dang this WAL needs to 
be checkpointed now" time?


-Original Message-
From: sqlite-users  On Behalf Of 
Thomas Kurz
Sent: Thursday, October 31, 2019 11:10 AM
To: SQLite mailing list 
Subject: Re: [sqlite] DELETE extremely slow

> Something is wrong.  If you did multiple commands like
>
> DELETE FROM MyTable;
>
> to your child tables, they should be fast.  Have you run an integrity check ?

I created a new database now, added the missing index "trace(datasetid)" as 
suggested by Keith.

The result of "DELETE FROM dataset" is now 88 minutes, which of course is 
better than before where it took hours, but not nearly as quick as I'd expect...

@Warren:
> Is that command representative of actual use, or are you deleting all rows 
> just for the purpose of benchmarking?

Usually I want to delete only several datasets, but not all. I left out the 
where-clause for simplification now.

@Keith:
> and in the face of enforced foreign key constraints will always delete the 
> rows one by each even if dependent (child) tables have no rows.

Yes, but I'd expect that MariaDB has to do the same, but takes clearly less 
than 1 minute instead of 88 minutes... :confused:

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

Re: [sqlite] DELETE extremely slow

[David Raymond]

"Four observations that may (or may not) explain the problem:
- dataset.id declared "unique": useless since the primary key is unique 
by definition but it may create an index (not checked). A PK is not 
"more unique" if an additional "unique" constraint is declared."

Declaring it as both "primary key" and "unique" makes an extra (duplicate) 
index, yes. With the added uniqueness checking on the duplicate index as well.



SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo1 (pk integer primary key);

sqlite> create table foo2(pk integer primary key unique);

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|foo1|foo1|2|CREATE TABLE foo1 (pk integer primary key)
table|foo2|foo2|3|CREATE TABLE foo2(pk integer primary key unique)
index|sqlite_autoindex_foo2_1|foo2|4|

sqlite> explain insert into foo1 values (?);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 13000  Start at 13
1 OpenWrite  0 2 0 1  00  root=2 iDb=0; foo1
2 Variable   1 1 000  r[1]=parameter(1,)
3 NotNull1 5 000  if r[1]!=NULL goto 5
4 NewRowid   0 1 000  r[1]=rowid
5 MustBeInt  1 0 000
6 SoftNull   2 0 000  r[2]=NULL
7 Noop   0 0 000  uniqueness check for 
ROWID
8 NotExists  0 10100  intkey=r[1]
9 Halt   1555  2 0 foo1.pk02
10MakeRecord 2 1 3 D  00  r[3]=mkrec(r[2])
11Insert 0 3 1 foo1   31  intkey=r[1] data=r[3]
12Halt   0 0 000
13Transaction0 1 2 0  01  usesStmtJournal=0
14Goto   0 1 000

sqlite> explain insert into foo2 values (?);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 22000  Start at 22
1 OpenWrite  0 3 0 1  00  root=3 iDb=0; foo2
2 OpenWrite  1 4 0 k(1,)  00  root=4 iDb=0; 
sqlite_autoindex_foo2_1
3 Variable   1 1 000  r[1]=parameter(1,)
4 NotNull1 6 000  if r[1]!=NULL goto 6
5 NewRowid   0 1 000  r[1]=rowid
6 MustBeInt  1 0 000
7 SoftNull   2 0 000  r[2]=NULL
8 Noop   0 0 000  uniqueness check for 
ROWID
9 NotExists  0 11100  intkey=r[1]
10Halt   1555  2 0 foo2.pk02
11Affinity   2 1 0 D  00  affinity(r[2])
12Noop   0 0 000  uniqueness check for 
sqlite_autoindex_foo2_1
13SCopy  1 4 000  r[4]=r[1]; pk
14IntCopy1 5 000  r[5]=r[1]; rowid
15MakeRecord 4 2 300  r[3]=mkrec(r[4..5]); 
for sqlite_autoindex_foo2_1
16NoConflict 1 184 1  00  key=r[4]
17Halt   2067  2 0 foo2.pk02
18MakeRecord 2 1 600  r[6]=mkrec(r[2])
19IdxInsert  1 3 4 1  10  key=r[3]
20Insert 0 6 1 foo2   31  intkey=r[1] data=r[6]
21Halt   0 0 000
22Transaction0 1 2 0  01  usesStmtJournal=0
23Goto   0 1 000

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

Re: [sqlite] Multiple files for a single SQLite database

[David Raymond]

"There's a limit, somewhere around ten, I think.  Though you can increase it by 
explicitly messing with limits and compilation options."

Default is 10, can be raised to 125.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the default value of isolation_level?

[David Raymond]

https://docs.python.org/3.7/library/sqlite3.html#controlling-transactions

"If you specify no isolation_level, a plain BEGIN is used, which is equivalent 
to specifying DEFERRED."

I believe the empty string qualifies as "no isolation_level" for this.



-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Friday, October 25, 2019 3:49 PM
To: SQLite mailing list 
Subject: [sqlite] What is the default value of isolation_level?

Hi,

isolation_level is an empty string by default as shown below. But the
python manual does not say that it can take an empty string. What does
an empty string mean? Is it equivalent to None? Thanks.

https://docs.python.org/3/library/sqlite3.html#connection-objects

"""
isolation_level

Get or set the current default isolation level. None for
autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See
section Controlling Transactions for a more detailed explanation.
"""

$ cat main.py
#!/usr/bin/env python2
import sqlite3
import sys
conn=sqlite3.connect(sys.argv[1])
print(repr(conn.isolation_level))

$ cat main.sh
#!/usr/bin/env bash
dbfile=$(mktemp -u)
./main.py "$dbfile"

$ ./main.sh
''

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

Re: [sqlite] What does commit actually do?

[David Raymond]

"I just use the default .connect() without specifying isolation_level
explicitly. Then I am in a transaction?"
E, umm, well.
Even regular users may not know the answer to that do to "automatic stuff 
behind the scenes" which may have corner cases or maybe bad documentation. 
Which is why we prefer to use the "isolation_level = None" at connection time 
to be absolutely sure.
If you don't want to deal with that you can always just run .commit() anyway 
just to make sure. If you weren't in a transaction it won't complain, and 
you'll be sure to be no longer in a transaction immediately afterwards.


"Is there any .commit() implied in the SELECT statement?"
Nope.


"So I should close the `conn` immediately after `c.fetchall()` to
release resources used by sqlite3?"
If you're done reading from the database then it never hurts. If you're gonna 
go back and do more queries then you can keep the connection live which will 
keep things cached. Just don't be in a transaction or you'll be blocking anyone 
else trying to write.


If you're the only one ever using that file then all this is a moot point 
though as there's no one else to block.


"In the above example, should I use WAL mode or not?"
If you're just getting started with SQLite then probably don't worry about it 
for now, especially if everything is working fine with the default mode. You 
can look at it later for fun.
https://www.sqlite.org/wal.html

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

Re: [sqlite] What is the default value of isolation_level?

[David Raymond]

https://www.sqlite.org/lang_transaction.html
Depending on how they're implementing it, one could argue that they're just 
copying the specs for SQLite and saying "if you're not gonna specify it, then 
we're not gonna specify it, and we're just gonna let the SQLite library do with 
it as it pleases without that keyword"


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Friday, October 25, 2019 4:02 PM
To: SQLite mailing list 
Subject: Re: [sqlite] What is the default value of isolation_level?

So basically an empty string in isolation_level is the same as
"DEFERRED"? This is confusing. I think it should have been implemented
so that the value of the isolation_level attribute is DEFERRED when it
is not specified in .connect().

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

Re: [sqlite] What does commit actually do?

[David Raymond]

"https://docs.python.org/2/library/sqlite3.html;

Also, please consider going to Python 3 instead of 2.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does commit actually do?

[David Raymond]

This question could span the entire range of experience level from beginner to 
expert.

When you're in a transaction, .commit() says "make everything done in this 
transaction on this connection permanent." If there are any crashes or improper 
disconnections in the middle of a transaction then nothing you did in the 
transaction will persist in the database, it will all be rolled back.
If you haven't made any changes then it's still fine to run, it'll just say 
"that was easy, nothing for me to save."

Are you in a transaction? (Python library specific question)
See Keith's message for a longer version. But in short...
If you did NOT specify "isolation_level = None" in the .connect() call then you 
probably ARE in a transaction if you've been running queries.
If you DID then you're usually NOT in a transaction, unless you explicitly 
started one (in which case you probably know about transactions).

But why do I need to commit my transaction if I'm just reading?
If you're just reading then you can commit or rollback. Either is fine. Just 
don't leave the transaction lingering around still open because someone else 
may want to write to the database, or to do a checkpoint.
If the database is not in WAL mode then their attempt to write will have to 
wait for your read transaction to finish before it can do any writing.
If the database IS in WAL mode, then writers can commit ok, but checkpointing 
of the WAL will be blocked from the point you started your transaction. You 
also will not see in your own queries any of those changes from writers until 
you start a new transaction. (Which may or may not be what you want)

Under the hood in the Python library I think all it does is issue a "commit;" 
command, and suppress any exception raised because you weren't in a transaction 
already.



-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Friday, October 25, 2019 12:44 PM
To: SQLite mailing list 
Subject: [sqlite] What does commit actually do?

The python manual just tell me what I should do but it is not very
clear what commit() actually does under the hood.

https://docs.python.org/2/library/sqlite3.html

"""
commit()

This method commits the current transaction. If you don’t call
this method, anything you did since the last call to commit() is not
visible from other database connections. If you wonder why you don’t
see the data you’ve written to the database, please check you didn’t
forget to call this method.
"""

So, only if I want to write something to the db, I need to call
commit()? If I just read something from the db, there is no need to
call commit()?

Thanks.

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

Re: [sqlite] ALTER TABLE ADD COLUMN

[David Raymond]

On disk a record basically looks like:
Here are 5 values: value1, value2, value3, value4, value5

If your query is looking for the 6th, 7th or 8th field and the record on the 
disk only has 5, then it goes " I guess they should be the default 
values for the missing fields." What that means is that when you add a new 
field it doesn't have to re-write the table because it handles the "missing on 
disk" fields just fine.

There's actually a compile option for SQLite to intentionally do this all the 
time and leave out as many trailing NULL fields as it can to save space. I 
think it's SQLITE_ENABLE_NULL_TRIM, which is disabled by default.


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Wednesday, October 23, 2019 8:53 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] ALTER TABLE ADD COLUMN

From the documentation

“A record might have fewer values than the number of columns in the 
corresponding table. This can happen, for example, after an ALTER TABLE ... ADD 
COLUMN SQL statement has increased the number of columns in the table schema 
without modifying preexisting rows in the table. Missing values at the end of 
the record are filled in using the default value for the corresponding columns 
defined in the table schema.”

Suppose you have a table with say 5 columns that are almost always the default 
value (probably zero or null). Does the above suggest you should make them the 
last 5 columns in the table as the last n columns that are the default value 
won’t take up space? Or does this state just exist after ADD COLUMN but any 
rows added thereafter use the space?
*Assume the 5 columns are little used so it doesn’t matter that they are the 
last named columns.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[David Raymond]

Or something like this using the new filter clause from 3.30. As written it 
should show it with two decimal points.
(not tested for typos)

select
CITY_TXT as CITY,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'WALKING') / 
total(Employee_Count), 2) as WALKING,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'CYCLING') / 
total(Employee_Count), 2) as CYCLING,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'PUBLIC TRANSIT') 
/ total(Employee_Count), 2) as PUBLIC_TRANSIT,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'CAR') / 
total(Employee_Count), 2) as CAR,
round(100.0 * total(Employee_Count) filter (where MODE_TXT not in ('WALKING', 
'CYCLING', 'PUBLIC TRANSIT', 'CAR')) / total(Employee_Count), 2) as OTHER,
from
(
select
CITY_TXT,
MODE_TXT,
count(*) as Employee_Count
from
Employees
inner join Cities
using (CITY_ID)
inner join Mode
using (MODE_ID)
group by CITY_TXT, MODE_TXT
)
group by CITY_TXT
order by CITY;


-Original Message-
From: sqlite-users  On Behalf Of 
Keith Medcalf
Sent: Tuesday, October 22, 2019 10:11 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Can SQLite do this in a single query?

CREATE TABLE employees(employee_id, city_id, mode_id);
CREATE TABLE citys(city_id, city_txt);
CREATE TABLE modes(mode_id, mode_txt);

  select city_txt, 
 mode_txt, 
 total(1) over (partition by city_txt, mode_txt) / total(1) over 
(partition by city_txt) as percentage
from employees, citys, modes 
   where employees.city_id == citys.city_id 
 and employees.mode_id == modes.mode_id 
group by city_txt, mode_txt
group by city_txt, mode_txt;

You have to paint the output table yourself.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Winfried
>Sent: Tuesday, 22 October, 2019 07:23
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>Yes, I forgot the column mode_id in the Employee's table.
>
>Thanks, I'll read up on the features SQLite's SELECT has to offer. At
>worst,
>I'll just run a simpler query multiple times.
>
>https://www.sqlite.org/lang_select.html
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

[David Raymond]

What language/library are you using?

In Python for example there's .fetchone() to get just the next result row, 
.fetchmany(n) to get the next n rows, or .fetchall() to go get them all.

In general though at its core SQLite will get and return one row at a time. 
Though if there's grouping or sorting it may have to collect a whole bunch of 
underlying rows first while it's doing the aggregation or before it can sort 
them, and only then once they're sorted will it be able to give you the first 
row, or the next one, etc. So in the worst case, even if you're saying "give me 
just the first one" you might have to have the whole result in memory to find 
out which one is "first".



-Original Message-
From: sqlite-users  On Behalf Of 
Randall Smith
Sent: Wednesday, October 16, 2019 1:38 PM
To: sqlite-users@mailinglists.sqlite.org
Cc: Randall Smith 
Subject: [sqlite] Getting "chunked" output from a large SELECT operation.

I'm having a situation where the results of a large SELECT operation are 
apparently too big to fit in memory.

Obviously I could jerry-rig something to work around this, but I have a vague 
recollection that SQLite provides a nice way to get the results of a query in 
"chunks" so that the memory demands can be reduced as much as needed by going 
back to the well a (potentially large) number of times.

Am I remembering this right?  Can anyone refresh my memory on how to do it if 
so?

Randall.

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

Re: [sqlite] Last record

[David Raymond]

"Keith, what if one has a peanut allergy?"

Well, the maid dutifully logs the changes she makes to the tin, so that in the 
event of an anaphylactic crash the tin can be returned to its original state. 
This helps ensure we have ACID peanuts.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insertion of a single record taking more than 200 msec some times

[David Raymond]

Without more info it's impossible to say.

The immediate possibility coming to mind would be if you're running in WAL 
mode, and that happened to be the commit that triggered a checkpoint, meaning 
you have both the commit time and the checkpoint time in there. Depending on 
how much there was to be checkpointed that could add up.


-Original Message-
From: sqlite-users  On Behalf Of 
GopiKrishna Parisa
Sent: Thursday, October 10, 2019 9:35 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] insertion of a single record taking more than 200 msec some 
times

Hi,
i have a requirement to store logs while my app is running.
i am using sqlite3 to store logs.
i was trying to get some benchmark data by inserting logs on top of 6
records.
One weird thing is for some insertions, it's taking more than 200 msec for
single record where as others takes around 20 to 40 (avag 27 msec).
May i know what is the reason for this?

Thank you.
Regards,
Gopikrishna P.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opposite of SQLite

[David Raymond]

SQLephantine


-Original Message-
From: sqlite-users  On Behalf Of 
Ned Fleming
Sent: Thursday, October 10, 2019 2:55 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Opposite of SQLite


> Someone asked:
> 
>>> What the opposite of "Lite”?
> 

SQLessLite

-- 
Ned

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

Re: [sqlite] SQLite version 3.30.0 in about two weeks.

[David Raymond]

Looks good.

One request that popped to mind while looking at this: Would you expand the 
documentation on the index_info and index_xinfo pragmas to include what they 
return when run on an expression index?


-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Thursday, September 26, 2019 2:26 PM
To: General Discussion of SQLite Database 
; sqlite-dev 

Subject: [sqlite] SQLite version 3.30.0 in about two weeks.

Our plan is to release SQLite version 3.30.0 in about two weeks - on
or about 2019-10-10.  Please review the change log

https://www.sqlite.org/draft/releaselog/3_30_0.html

And perhaps download, build, and test the latest snapshot.  Please let
us know if you encounter any problems or concerns.

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

Re: [sqlite] Documentation bug: CREATE TABLE diagram misplaces comma

[David Raymond]

It is comma then table-constraint. Check the direction of the arrow on that 
loop. You go past it on the main line, go down into the ,-circle, then follow 
the arrow left into the table-constraint box, then continue the loop clockwise 
to meet back up with the main line.


-Original Message-
From: sqlite-users  On Behalf Of 
Tim McCormack
Sent: Wednesday, September 18, 2019 9:14 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Documentation bug: CREATE TABLE diagram misplaces comma

In https://www.sqlite.org/lang_createtable.html the diagram for
create-table-stmt contains a parenthesized list of column-def and
table-constraint, which I'll represent in a pseudo-grammar here:

"("  ("," )* ( ",")* ")"

However, that would forbid (foo, bar, PRIMARY_KEY foo) and require
instead something like (foo, bar PRIMARY_KEY foo,).

The fix is to change the  "," loop to instead be
"," .

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

Re: [sqlite] Differentiate between an empty result set and an error using Python3

[David Raymond]

If you run a query that returns no results, then cursor.fetchone() will return 
None, or cursor.fetchall() will return an empty list. If there is an error 
during the processing then some sort of exception should be raised.


-Original Message-
From: sqlite-users  On Behalf Of 
Rob Sciuk
Sent: Wednesday, September 04, 2019 2:18 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Differentiate between an empty result set and an error using 
Python3


Forgive me if this is an FAQ, but in looking over the python3 interface to 
SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
execute() command.

My use case is to differentiate between an empty row set (OK) vs an error 
of some kind in the query.

Anyone figured this out?

Cheers,
Rob.

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Robert S. Sciuk r...@controlq.com
Principal Consultant905.706.1354
Control-Q Research  97 Village Rd. Wellesley, ON N0B 2T0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

[David Raymond]

> If you delete the database file then make sure you also delete any other
> files that might have been associated with it, such as left over journals
> and so forth.

I never see those extra files in practice. Are they guaranteed to be
deleted automatically once an SQLite session is finished?


Nope. If there was a problem in closing down they can hang around (which is 
their whole point for recovery). Also if a journal mode of "persit" was used. 
But mostly from incorrect closure.

So check for any -journal, -wal, or -shm files of the same name if you want to 
obliterate a database.
(Am I missing any others?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions

[David Raymond]

Kind of annoying that when the author shows a screenshot of the sample data 
he's using for his queries that he doesn't include 2 of the fields that are in 
the queries. Makes it harder to "play along at home"

For their ntile example (on page2) I don't think I've seen a window function 
used with a "group by". Does the ntile un-group the groups? Something just 
looks wrong there between the query and the results shown below it. But like 
you I don't know enough to say if that's right or if it's on crack.


-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Wednesday, September 04, 2019 12:15 PM
To: SQLite mailing list 
Subject: [sqlite] Window functions

I ran into this two-part article, probably on Hacker News:



I tried comparing it with



but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error: unable to read database header

[David Raymond]

When you run ".open test.db" if the database doesn't already exist, then the 
file is created. However *no data is written yet* as there are a few permanent 
things you could change at the very start (like page size, encoding, etc), so 
it holds off on writing the first page with the header until you issue some 
sort of statement that isn't setting those "new database options" So since the 
file is still 0 size at this point, there is no header for .dbinfo to read 
from. If you create a table first for example, then it will populate the header 
and actually write to the file, at which point there will be a header for 
.dbinfo to read.


-Original Message-
From: sqlite-users  On Behalf Of 
Alexander Gabriel
Sent: Friday, August 30, 2019 10:54 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] error: unable to read database header

Hi

I downloaded the files for v3.29.0 into a folder.
Then double clicked sqlite3.exe to open a command shell.
Then typed `.open test.db`
Then typed `.dbinfo`
And got: `unable to read database header`

What am I doing wrong?
Have I created a broken database?

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

Re: [sqlite] .save always errors: database is locked

[David Raymond]

Running sqlite3 will open up an in-memory database to start with.
".open test" will either open the file "test" if it exists, or create the file 
if it doesn't. You will then be working on that file called "test" which is 
your database.

The help line for .save is:
.save FILE   Write in-memory database into FILE

".save test" tries to save the current database to the file "test". Since you 
already have the file "test" open in the CLI tool, when it tries to open it a 
second time to run the .save command it's getting the "database is locked" 
error. In fact it seems weird it lets you try at all since at that point you 
don't have an in-memory database open, but have an actual file open.

Once you do the ".open test" anything you do is done on the "test" 
file/database. There is no need to save it with .save. "saving" is all through 
normal database transactions at that point.


-Original Message-
From: sqlite-users  On Behalf Of 
Alexander Gabriel
Sent: Friday, August 30, 2019 12:11 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] .save always errors: database is locked

Hi

I do this:

   - restart windows10 or macOS and log in (to guarantee no other process
   is using sqlite3)
   - cd to the folder where sqlite3 v3.29.0 is contained
   - type `sqlite3` (windows 10) or `./sqlite3` (macOS) to start sqlite3
   - type `.open test`, followed by `.save test`

Result: `Error: database is locked`

What am I doing wrong?
How can I change configuration if I can never save changes?

The only time I can save without an error is when I `.save test2` without
having opened it before.

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

Re: [sqlite] Unexpected REINDEX behavior.

[David Raymond]

Sorry if my mind is working slowly today, but why are those showing up as a 
difference when they're exactly the same line?


The only difference between both runs:
> $ diff run1 run2
> 1260d1259
> < INSERT INTO space_used 
> VALUES('idx_words1','words',1,0,551407,550067,3,3826907,0,0,7,6,1341,0,5816,14208,0,1282,5517312);
> 1270a1270
>> INSERT INTO space_used 
>> VALUES('idx_words1','words',1,0,551407,550067,3,3826907,0,0,7,6,1341,0,5816,14208,0,1282,5517312);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query for Many to Many

[David Raymond]

It does support natural joins. 

USING needs parenthesis around the column list: ...using (author_id)...using 
(book_isbn)...


-Original Message-
From: sqlite-users  On Behalf Of 
Dominique Devienne
Sent: Tuesday, August 27, 2019 10:08 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Query for Many to Many

On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne 
wrote:

> select author.*, books.*
>   from author_books
>   join author on author.author_id  = author_books.author_id
>   join books  on books.book_isbn   = author_books.book_isbn
>

Which can also be written:

select author.*, books.*
  from author_books
  join author using author_id
  join books  using book_isbn

Or even:

select author.*, books.*
  from author_books
  natural join author
  natural join books

All of the above untested of course :).
Not even sure SQLite supports natural join or not (I'd guess it does). --DD

https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on
https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query for Many to Many

[David Raymond]

The basic query is going to be the below

select stuff
from

books
inner join author_books
on author_books.book_isbn = books.book_isbn
inner join author
on author_books.author_id = author.author_id

where things;



-Original Message-
From: sqlite-users  On Behalf Of 
dboland9
Sent: Tuesday, August 27, 2019 9:38 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Query for Many to Many

All,

I need some help writing some queries for a MTM relationship.  The example 
tables are:

author table books table author_books table
author_id PKbook_isbn PKa_b_id PK
author_fnamebook_title  author_id FK
author_lnamebook_pub_date   book_isbn FK
author_minit


Listings desired:
    book_isbn   book_title  book_pub_date   author
    --++--+---
   
    author book_isbn    Book_title
    +-+

Would appreciate the query (inner join - that I do know), and why so I can 
learn something from them.  Please keep them simple (no alias or other 
shortcuts) so I can easily follow what you are doing.  Thanks in advance.

I assume the query will be something like:
  SELECT
books.book_isbn, books.book_title, books.book_pub_date,
author.author_fname, author.author_minit,
author.author_lname
  FROM books
  JOIN
author_books ON (something )

Dave,



Sent with ProtonMail Secure Email.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached databases and union view.

[David Raymond]

I don't know how smart the planner is, but as a thought, would UNION ALL make 
any improvement over just UNION? With just UNION it has to de-duplicate all the 
subquery results whereas with UNION ALL it would be free to separate all the 
various subqueries from each other.

Or do you actually need the UNION to de-dupe stuff?


-Original Message-
From: sqlite-users  On Behalf Of 
Peter da Silva
Sent: Thursday, August 22, 2019 11:28 AM
To: SQLite mailing list 
Subject: [sqlite] Attached databases and union view.

Have an existing application that's pushing the limit on how fast it can
read data and add it to the database, and thinking of sharding the database
file so I can have multiple writers writing to shards of the main tables.

ATTACH DATABASE 'shard0.sqlite' as shard0;
ATTACH DATABASE 'shard1.sqlite' as shard1;
...

CREATE TEMPORARY VIEW sharded_main_table AS
SELECT col,col,col...,all_columns_basically FROM shard0.main_table
UNION
SELECT col,col,col...,all_columns_basically FROM shard1.main_table
...;

What's the best way to construct this union view so the query optimizer
won't be horribly confused? If I run something like "SELECT count(*) FROM
sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
than the same query against the original main_table. Running the query
against each shardN.main_table it's actually faster (in total time for all
queries in sequence) than running it against the original table.

Is there a better way to construct the view, or am I going to get best
query performance by making my code shard-aware?

All the original indexes on main_table have been copied to the shard
databases.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] conditional trigger

[David Raymond]

Your create view had 2 typos in it by the way. Here's a fixed version for those 
copying and pasting:

CREATE VIEW select_file_dirs AS
   SELECT   dir_paths.dir_path AS dir_path,
files.f_name
   FROM dir_paths
   INNER JOIN
files ON files.dir_id = dir_paths.id;


I think you're also going to want a unique constraint on files (dir_id, f_name) 
to avoid duplicate file names

What you're looking for is this I believe:


create trigger trg_select_file_dirs_insert
instead of insert on select_file_dirs
begin
  insert into dir_paths (dir_path) values (new.dir_path) on conflict (dir_path) 
do nothing;
  insert into files (f_name, dir_id) values (new.f_name, (select id from 
dir_paths where dir_path = new.dir_path));
end;




-Original Message-
From: sqlite-users  On Behalf Of 
InAsset.Michele Petrazzo
Sent: Thursday, August 22, 2019 6:30 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] conditional trigger

Hi all,
I'm trying to create a conditional trigger for add some data to my
sqlite db only when it isn't inside (yet).

My tables:
CREATE TABLE dir_paths (
 id integer PRIMARY KEY,
 dir_path TEXT NOT NULL UNIQUE
);
CREATE TABLE files (
 id INTEGER PRIMARY KEY,
 f_name TEXT NOT NULL,
 dir_id integer NOT NULL,
 FOREIGN KEY (dir_id) REFERENCES dir_paths (id)
);

My need: I would like to create a trigger (or a view + a trigger
associate) that check if dir_path (and of course dir_paths.id) is
present and if not, create it into a single INSERT transition.
If yes, SELECT the dir_path.id and pass it to the INSERT into files
statement.
On the other hand, I have already a function that select the id of
dir_paths if present, and if not insert into it. But for performances
reasons, I need a single transition.

I tried with view+trigger:
CREATE VIEW select_file_dirs AS
   SELECT   dir_paths.dir_path AS dir_path,
files.f_name,
   FROM dir_paths
   INNER JOIN
files ON files.dir_id = dir_path.id;
+
CREATE TRIGGER check_dir_present INSTEAD OF INSERT ON select_file_dirs
BEGIN
CASE WHEN ((SELECT id FROM dir_paths WHERE id = NEW.dir_id ) ISNULL)
THEN
 INSERT INTO dir_paths (directory) VALUES (NEW.dir_path)
END

but I receive:
Error: near "CASE": syntax error

on the other side, I create a trigger before the insert into files, but
In the "INSERT INTO files" I haven't the "dir_path" info...

CREATE TRIGGER check_dir_present BEFORE INSERT ON files
  WHEN ((SELECT id FROM dir_paths WHERE id = NEW.dir_id ) ISNULL)
BEGIN
INSERT INTO dir_paths (dir_path) VALUES (NEW.dir_path);
END;

Some may help me?

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

Re: [sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?

[David Raymond]

>> A connection holds a SHARED lock. It did start a DEFERRED transaction then
>> read something. Now it executes COMMIT.

>You are one of the rollback journaling modes, not WAL mode, right?
>Different rules apply for WAL mode.
>
>In rollback mode, the connection automatically promotes to EXCLUSIVE
>while committing the transaction.  This is necessary to make sure no
>other connections are simultaneously reading the database, because it
>would not work for the committer to change content out from under the
>other readers.  After the COMMIT, it falls back to unlocked.
>-- 
>D. Richard Hipp
>d...@sqlite.org


I'm pretty sure you missed the bit where he said that there was only a read, 
and no changes were made or requested.

Otherwise, as Igor said, there's be deadlocks with any concurrent access.

(Or my brain is broken on a Friday, which has been known to happen)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[David Raymond]

>"But you do need WAL to achieve multiple readers concurrency..."

Nope, you can have concurrent readers with rollback journal mode. You just 
can't have anyone writing while they're all reading.

(Or I may just be misunderstanding what you meant)

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

Re: [sqlite] [EXTERNAL] storing blobs in a separate table

[David Raymond]

https://www.sqlite.org/fileformat2.html
is the page with the nitty gritty for the file format.

Overflow pages are stored in a singly linked list of pages, so you have to 
traverse through all of the pages to get to the end. So while you may know 
right away that you want the 20th overflow page for example, you still have to 
load all 19 pages in between to find out what page that is.


[DD] There's a special mode where SQLite keeps extra pages to keep track of 
pages, and thus can potentially avoid that "page-chain", but it's not often 
used I believe.

I think you're referring to the Pointer Map pages which are used for 
incremental vacuum. If used, then for every single page in the database it 
stores the "parent" page number. Ie backwards up the btree or backwards in the 
overflow page list. It's basically there so that if you want to move the 
contents of page X to somewhere else in the file it gives you the page number Y 
which has the pointer to it that will need to be changed to the new page 
number. But it doesn't let you skip ahead in the overflow page chain.

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

Re: [sqlite] Quick way to determine optimal page size?

[David Raymond]

Not that I'm aware of no. How much of a difference are you seeing for your 
database size depending on the page size you try?


-Original Message-
From: sqlite-users  On Behalf Of 
Tony Papadimitriou
Sent: Wednesday, July 31, 2019 3:29 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] Quick way to determine optimal page size?

Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and 
each database to determine which one produces the smallest file, is there some 
quicker way?

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

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[David Raymond]

https://www.sqlite.org/wal.html#concurrency

"But for any particular reader, the end mark is unchanged for the duration of 
the transaction, thus ensuring that a single read transaction only sees the 
database content as it existed at a single point in time."

Read transactions see one version of the entire database for their whole 
transaction. They won't see any changes made by other transactions after they 
start.

I refer you again to the previously linked transactions page 
https://www.sqlite.org/lang_transaction.html
"begin;" or "begin deferred;" doesn't do anything until you next access a file, 
at which point it will get a lock on the file. So if you want to control 
specifically when your read transaction _effectively_ starts you have to 
actually read something from the file to start it, at which point you'll get 
your lock, get your end mark in the WAL, and effectively freeze your view of 
the database.

And a point that this is of course with an explicitly declared transaction 
started with a begin statement. If you're in autocommit mode then each of your 
selects is its own little transaction separate from any other statements before 
it or after it.


-Original Message-
From: sqlite-users  On Behalf Of 
test user
Sent: Tuesday, July 30, 2019 3:01 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

Thanks David,

`SELECT 1` = rows 0 was a mistake in the example.

How sure are you that "any SELECT that reads from the DB file starts a read
transaction"?

Does the read transaction read from a snapshot of the entire DB, or are
only specific tables in the read snapshot?






On Tue, Jul 30, 2019 at 7:14 PM David Raymond 
wrote:

> To get the read lock you're going to need to read something from the
> database file.
>
> I think this page is your best bet:
> https://www.sqlite.org/lang_transaction.html
>
> "Transactions can be deferred, immediate, or exclusive. The default
> transaction behavior is deferred. Deferred means that no locks are acquired
> on the database until the database is first accessed. Thus with a deferred
> transaction, the BEGIN statement itself does nothing to the filesystem.
> Locks are not acquired until the first read or write operation. The first
> read operation against a database creates a SHARED lock and the first write
> operation creates a RESERVED lock. Because the acquisition of locks is
> deferred until they are needed, it is possible that another thread or
> process could create a separate transaction and write to the database after
> the BEGIN on the current thread has executed..."
>
> So after a "begin deferred" you have to actually do something that
> requires file access in order to get the shared lock/start your read
> snapshot on the file. So if you want to get that shared lock/read snapshot
> you can always do something like a select from sqlite_master. "select 1;"
> doesn't need to access the file to complete, so it doesn't take out the
> shared lock (though it should be returning 1 row, are you sure it's 0?)
>
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of test user
> Sent: Tuesday, July 30, 2019 1:45 PM
> To: SQLite mailing list 
> Subject: [sqlite] Explicit "read transaction" with journal_mode=WAL.
>
> Hello,
>
> How can I start a "read transaction" from BEGIN?
>
>
> I am using `journal_mode=WAL`.
>
> What I am trying to do:
>
> From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the
> same snapshot/point in time.
>
>
> The issue is that its hard to tell if I reading from a read snapshot (where
> any successful commit on other connections since the reads BEGIN are
> ignored).
>
> When is a read transaction started?
>
>
> As an example, connection A and B:
>
> ```
> run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");
>
> run(B, "BEGIN");
> run(B, "SELECT * FROM t1"); = 0 rows
>
> run(A, "BEGIN");
> run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
> run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
> run(A, "COMMIT");
>
> run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started
> ```
>
> ```
> run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");
>
> run(B, "BEGIN");
> run(B, "SELECT 1"); = 0 rows
>
> run(A, "BEGIN");
> run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
> run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
> run(A, "COMMIT");
>
> run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
> ```
>
>
> ```
&g

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[David Raymond]

To get the read lock you're going to need to read something from the database 
file.

I think this page is your best bet: https://www.sqlite.org/lang_transaction.html

"Transactions can be deferred, immediate, or exclusive. The default transaction 
behavior is deferred. Deferred means that no locks are acquired on the database 
until the database is first accessed. Thus with a deferred transaction, the 
BEGIN statement itself does nothing to the filesystem. Locks are not acquired 
until the first read or write operation. The first read operation against a 
database creates a SHARED lock and the first write operation creates a RESERVED 
lock. Because the acquisition of locks is deferred until they are needed, it is 
possible that another thread or process could create a separate transaction and 
write to the database after the BEGIN on the current thread has executed..."

So after a "begin deferred" you have to actually do something that requires 
file access in order to get the shared lock/start your read snapshot on the 
file. So if you want to get that shared lock/read snapshot you can always do 
something like a select from sqlite_master. "select 1;" doesn't need to access 
the file to complete, so it doesn't take out the shared lock (though it should 
be returning 1 row, are you sure it's 0?)



-Original Message-
From: sqlite-users  On Behalf Of 
test user
Sent: Tuesday, July 30, 2019 1:45 PM
To: SQLite mailing list 
Subject: [sqlite] Explicit "read transaction" with journal_mode=WAL.

Hello,

How can I start a "read transaction" from BEGIN?


I am using `journal_mode=WAL`.

What I am trying to do:

From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the
same snapshot/point in time.


The issue is that its hard to tell if I reading from a read snapshot (where
any successful commit on other connections since the reads BEGIN are
ignored).

When is a read transaction started?


As an example, connection A and B:

```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");
run(B, "SELECT * FROM t1"); = 0 rows

run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started
```

```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");
run(B, "SELECT 1"); = 0 rows

run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
```


```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");


run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
```



https://www.sqlite.org/isolation.html

Quote: "BEGIN IMMEDIATE command goes ahead and starts a write transaction"

This is the only page where I can find a mention of the idea of "read
transaction" and "write transaction".


BEGIN IMMEDIATE allows the explicit start of a "write transaction".

Does an API exist for a "read transaction"?

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

Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

[David Raymond]

Well, yes and no. I see that as more of a generic question of "why is some 
rogue process accessing and changing your database?" rather than a problem 
specific to SQLite. If your data needs foreign keys, or some extension like 
FTS, R-Tree, etc. then you're going to be controlling what's accessing and 
changing your database and make sure it knows what's in there. If some other 
process is bludgeoning its way through your data without respect, then that's 
another whole issue that would be there no matter how you chose to store your 
data.


-Original Message-
From: sqlite-users  On Behalf Of 
Richard Damon
Sent: Friday, July 19, 2019 2:46 PM
To: SQLite mailing list 
Subject: Re: [sqlite] I can insert multiple rows with the same primary key when 
one of the value of the PK is NULL ...

One big issue is that in general (as I remember right) pragmas generally affect 
the connection, not the database itself, so shouldn’t change how the schema is 
interpreted, or another connection (or before issuing the pragma) might 
interpret things differently and possibly see the database as somehow corrupt 
or be able to corrupt the database.

> On Jul 19, 2019, at 1:44 PM, Thomas Kurz  wrote:
> 
> Imho it would be helpful (especially for newbies that don't know the full 
> history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all 
> kinds of historical bugs. They might be relevant for existing applications 
> but in no way for newly created ones. Among the things to consider should be:
> 
> - PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*)
> - enable FOREIGN KEY constraints (I know there is already a pragma, but imho 
> it should be included)
> - strict type enforcement
> - disable the use of double quotes for strings
> - default to WITHOUT ROWID
> 
> ...and probably many more I don't know about ;-)
> 
> 

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

Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

[David Raymond]

"integer primary key"s cannot contain a null since they're an alias for the 
rowid. So when you insert a null into them they act similar to autoincrement 
and automatically fill it in with an unused id. (Current implementation is 1 
more than the _current_ highest rowid. Subject to change)

If you made it just an int primary key it shows what you want.


D:\Programs\PostgreSQL\11\bin>sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table TheTable (ID int primary key, Info text);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)

sqlite> insert into theTable values (1, 'Test');

sqlite> insert into theTable values (2, 'Test2');

sqlite> insert into theTable values (null, 'TestNull1');

sqlite> insert into theTable values (null, 'TestNull2');

sqlite> insert into theTable values (null, 'TestNull3');

sqlite> insert into theTable values (2, 'Test2.1');
Error: UNIQUE constraint failed: TheTable.ID

sqlite> select * from TheTable;
QUERY PLAN
`--SCAN TABLE TheTable
ID|Info
1|Test
2|Test2
|TestNull1
|TestNull2
|TestNull3

sqlite> select count(distinct id) from theTable;
QUERY PLAN
`--SCAN TABLE theTable USING COVERING INDEX sqlite_autoindex_TheTable_1
count(distinct id)
2

sqlite> select id, count(*) from theTable group by id order by id;
QUERY PLAN
`--SCAN TABLE theTable USING COVERING INDEX sqlite_autoindex_TheTable_1
ID|count(*)
|3
1|1
2|1

sqlite>


-Original Message-
From: sqlite-users  On Behalf Of 
Stephen Chrzanowski
Sent: Thursday, July 18, 2019 1:18 PM
To: SQLite mailing list 
Subject: Re: [sqlite] I can insert multiple rows with the same primary key when 
one of the value of the PK is NULL ...

Actually, I take that back...

sqlite> select * from TheTable;
1|Test
2|Test2
3|TestNull1
4|TestNull2
5|TestNull3

So the inserting of NULL in a primary key (Single instance maybe?) will
insert the new rowid.

Try running a SELECT against your table and see what kind of results you're
obtaining.


On Thu, Jul 18, 2019 at 1:13 PM Stephen Chrzanowski 
wrote:

> NULL is a special thing.  It's never considered unique.
>
> SQLite version 3.20.0 2017-08-01 13:24:15
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table TheTable (ID Integer, Info Text, PRIMARY KEY (ID));
> sqlite> insert into TheTable (1,"Test");
> Error: near "1": syntax error
> sqlite> insert into TheTable values (1,"Test");
> sqlite> insert into TheTable values (2,"Test2");
> sqlite> insert into TheTable values (null,"TestNull1");
> sqlite> insert into TheTable values (null,"TestNull2");
> sqlite> insert into TheTable values (null,"TestNull3");
> sqlite> insert into TheTable values (2,"Test2.1");
> Error: UNIQUE constraint failed: TheTable.ID
> sqlite> select count(*) from TheTable;
> 5
> sqlite>
>
>
> On Thu, Jul 18, 2019 at 12:02 PM Alexandre Billon 
> wrote:
>
>> Hello,
>>
>> I have created a table.
>>
>> CREATE TABLE "sales" (
>> "client"TEXT,
>> "salesman"  TEXT,
>> "revenue"   REAL,
>> PRIMARY KEY("client","salesman")
>> );
>>
>>
>> I can run the query below mutliple times without any error :
>>
>> INSERT INTO sales ("client", "salesman", "revenue")
>> VALUES ('C1', NULL, 10.0);
>>
>>
>> Have I missed something in the CREATE instruction ?
>> Is this a normal behaviour ?
>>
>> I have tried to read https://sqlite.org/lang_createtable.html#constraints
>> and https://www.sqlite.org/nulls.html but I don't really have found why
>> I can insert 2 records that have the same primary key.
>>
>> SQLite version 3.27.2 2019-02-25 16:06:06
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .headers on
>> sqlite> .mode column
>> sqlite> .nullvalue null
>> sqlite>
>> sqlite> CREATE TABLE "sales" (
>>...> "client"TEXT,
>>...> "salesman"TEXT,
>>...> "revenue"REAL,
>>...> PRIMARY KEY("client","salesman")
>>...> );
>> sqlite>
>> sqlite>
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', NULL, 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', NULL, 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', '', 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', '', 10.0);
>> Error: UNIQUE constraint failed: 

Re: [sqlite] how to delete BLOB object from the data base

[David Raymond]

The questions coming to mind at the moment:

What is the schema of the table holding the BLOBs?
What is the normal size for the blobs?
How are you doing the inserts and deletes?
What journal mode are you using?


I would think normal way to delete a record is the simple
delete from blob_table where primary_key_id = ?;


My understanding is that incremental vacuum basically says: "I want to shrink 
the file size by filling free pages at the front with data from the back to be 
able to truncate the file." It doesn't do any re-ordering or sorting, it just 
moves data from the end of the file into any free space closer to the front.
Since you're adding every 250ms and only deleting every 600, then the file size 
should be progressively increasing, and any free pages should be used up 
relatively quickly, so I don't think incremental vacuum would do a lot.



-Original Message-
From: sqlite-users  On Behalf Of 
Zhu, Liang [AUTOSOL/ASSY/US]
Sent: Monday, July 15, 2019 3:39 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] how to delete BLOB object from the data base


Sqlite Experts,

I have a table contains the BLOB object,  I am inserting to the BLOB  data into 
the table at every 250ms,  I delete the oldest row at every 600ms, also I am 
reading the data from the database at every 10ms.  After almost of 100,000 
insert, delete and select operations,   I am getting the  SQLite_locked error 
on delete,  and my data from the select statement are junk.To prevent the 
database fermentation,  I tried PRAGMA incremental_vacuum(1000) and PRAGMA 
incremental_vacuum;
The performance improved some when I starting using PRAGMA incremental_vacuum 
but I am still getting the junk data.  Am I using the incremental_vacuum 
correctly?  And is there any optimal way to delete the BLOB object in the 
database table

Thank you,

Liang Zhu | Lead Software Engineer | Branson Ultrasonics
Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA
T (203) 796-2235 | F (203) 796-0380
liang@emerson.com

The information contained in this message is confidential or protected by law. 
If you are not the intended recipient, please contact the sender and delete 
this message. Any unauthorized copying of this message or unauthorized 
distribution of the information contained herein is prohibited.

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

Re: [sqlite] Grammar police

[David Raymond]

Other small ones from the Quirks page:

Section 2:
"to realize the SQLite is not intended as"
 to realize [that] SQLite is not intended as

Section 3.2:
"SQLite as no DATETIME datatype."
 SQLite [has] no DATETIME datatype

Section 5:
"Due to an historical oversight"
 Due to [a] historical oversight

Section 6:
"each output row might be composed from two more more rows"
 each output row might be composed from two [or] more rows

"then the one of the rows is chosen arbitrarily"
 then one of the rows is chosen arbitrarily

Section 8:
   "into bad habit of"
into [the] bad habit of
or  into bad habit[s] of


(I always feel a little weird when pointing out typos as the meaning is usually 
perfectly fine the way it is, it feels like I'm being overly critical, and I 
worry my "corrections" are also not quite right)


-Original Message-
From: sqlite-users  On Behalf Of 
Don V Nielsen
Sent: Thursday, July 11, 2019 10:58 AM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Grammar police

Sorry. This was in the Quirks, Caveats page, #2.

On Thu, Jul 11, 2019 at 9:57 AM Don V Nielsen  wrote:

> " An application interact with the database engine using function calls,
> not be sending messages to a separate process or thread."
>
> "An applications [interacts] ..., [not by]...
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FW: [sqlite-announce] Version 3.29.0

[David Raymond]

And there was great rejoicing:

"1. Added the SQLITE_DBCONFIG_DQS_DML and SQLITE_DBCONFIG_DQS_DDL actions to 
sqlite3_db_config() for activating and deactivating the double-quoted string 
literal misfeature. Both default to "on" for legacy compatibility, but 
developers are encouraged to turn them "off", perhaps using the -DSQLITE_DQS=0 
compile-time option."


Also, I don't think I'd ever seen the quirks page 
(https://sqlite.org/quirks.html) before. Is that new-ish? I don't see it 
anywhere on https://sqlite.org/docs.html , maybe add it to the "Overview 
Documents" section?



-Original Message-
From: sqlite-announce  On Behalf Of Richard 
Hipp
Sent: Wednesday, July 10, 2019 4:20 PM
To: sqlite-announce 
Subject: [sqlite-announce] Version 3.29.0

SQLite version 3.29.0 is now available on the SQLite website:

https://sqlite.org/
https://sqlite.org/releaselog/3_29_0.html

Version 3.29.0 is a routine maintenance release with some small
performance enhancements and fixes for various obscure bugs.  See the
release notes above for details.

If you have any problems, please report them to the
sqlite-users@mailinglists.sqlite.org mailing list or directly to me.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-announce mailing list
sqlite-annou...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How lock is implemented upon write?

[David Raymond]

I don't have good answers for you as I'm not familiar with locking, but I'd 
suggest reading the comments in SQLite's os.h file (starting around line 91 at 
the moment) which I found interesting.

For Python it looks like it'd be something involving the fcntl module for Unix 
or the msvcrt module for Windows.


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Tuesday, July 02, 2019 3:26 PM
To: SQLite mailing list 
Subject: Re: [sqlite] How lock is implemented upon write?

I not sure how to use os_unix.c. Are there any easy to follow examples in
python?

On Mon, Jul 1, 2019 at 9:08 PM Simon Slavin  wrote:

> You might want to take a look at the standard VFSen:
>
> 
>
> At a low level, SQLite depends on the VFS for reliable locking.  The main
> parts of SQLite call a routine supplied by the VFS.  The VFS does the
> actual locking.
>
> If we didn't answer your question, feel free to post again telling us what
> you're looking for.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode much slower in certain use cases

[David Raymond]

When you're inserting in WAL mode everything will basically have to be written 
twice. First the new page data will be written to the WAL, and then when a 
checkpoint happens the WAL file will be copied over to the main database file.

With rollback journal mode the contents of the original page are written to the 
rollback journal, then the data is written to the main database file. But if 
the page being written to is brand new (because the database size in 
increasing), then there is nothing to be written to the rollback journal, and 
only 1 write needs to happen. (Same if the page being written used to be a free 
page... I think)

So if there's unused room in the file for the inserts, then the two modes 
should be similar, but if it's a brand new database file, or your insert is 
going to grow the file size, then rollback mode will result in fewer writes 
being needed.

So technically the "commit" times are probably similar, but it's probably the 
"checkpoint" time that's killing you. Though I _am_ still curious as to why 
your insert times are twice as long.

(Someone correct me if I'm off on this)


-Original Message-
From: sqlite-users  On Behalf Of 
Andrew Cunningham
Sent: Monday, July 01, 2019 2:04 PM
To: SQLite Maillist 
Subject: [sqlite] WAL mode much slower in certain use cases

I am using SQLite 3.24.0, as a single user persistent data store for
storing simulation data.

The database can grow to many gigabytes as the software can ingest a lot of
binary data which  I store as multiple BLOBs.

In the following example I am reading several 22GB of data into the DB.

Times in seconds
In WAL mode
Read data & creating DB records 503.227
Commit of transaction,close database  334

In non-WAL mode
Read data & creating DB records 244
Commit of transaction ,close database   4

WAL mode is 80-100x slower to commit to the database.

I am probably abusing WAL mode, but I do not see anything in the
documentation which says "don't use WAL mode in the following use case".

Full disclosure, I am using ODB ( C++ ORM) that hides what is going on
under the hood, but my experience is that in a simple use case like this
ODB is not the problem.

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

Re: [sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...

[David Raymond]

On the documentation page (https://www.sqlite.org/docs.html) under "Overview 
Documents" is "Books About SQLite" https://www.sqlite.org/books.html


> SQLite has, AFAIK, _two_ employees (Richard and Dan)

See:  https://www.sqlite.org/crew.html


-Original Message-
From: sqlite-users  On Behalf Of 
Jens Alfke
Sent: Thursday, June 27, 2019 12:31 PM
To: SQLite mailing list 
Subject: Re: [sqlite] the sqlite3 documentation would be pretty good if it 
wasn't tragic...



> On Jun 26, 2019, at 3:39 PM, Warren Young  wrote:
> 
> Arguably, K is a bound book of examples for the AT Unix C compiler.  

It was also the _first_ book on the C language.

> Where is the K of SQLite?  I don’t necessarily mean a bound book, but 
> something that’s comprehensive, concise, and tutorial in nature.  I want it.

There are several books focusing on SQLite. I have a pretty good one from 
O'Reilly somewhere. There is probably a website somewhere that has a search 
field where you can type "SQLite" and find a list of books. Maybe such a site 
could even ship you a book.

> Microsoft has done a great job with its MSDN site, with an example on almost 
> every function’s page, often in multiple programming languages.  SQLite’s 
> docs aren’t quite at the MSDN level.

Microsoft is one of the largest companies in the world, and must certainly have 
thousands of tech writers and editors.

SQLite has, AFAIK, _two_ employees (Richard and Dan). Both of them are coders. 
Asking them to put part of their time into writing SQL examples would be 
tragic. And they probably don't have the money to hire tech writers.

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

Re: [sqlite] select within transaction

[David Raymond]

How are you sending the commands to the cli?

If you're doing...

sqlite3 myfile.sqlite ".read somefile.sql"

...then you can start the sql file with...

.bail on

...and as soon as it hits an error it will stop there and not continue 
processing lines. So if you get rid of the "or rollback" then you'll get the 
error message and won't have to worry about it continuing on to the next lines 
in the input file despite there having been an error. And since you explicitly 
started a transaction it will leave the transaction open, and then when the CLI 
closes it will rollback the uncommitted transaction.


-Original Message-
From: sqlite-users  On Behalf Of 
Roman Fleysher
Sent: Friday, June 14, 2019 2:23 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] select within transaction

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is 
to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. 
If update failed, then c will not be 5 (it will be old value, different from 5) 
and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update 
actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one 
transaction and thus I need some indicator if SELECT was after successful 
update, not rollback.

Is this what changes() is for?

Thank you,

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

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[David Raymond]

https://www.sqlite.org/fileformat2.html#record_format

The storage type of each record is given by an integer. And in the current 
format, all non-negative integers are used.

To me of course that begs the question: Are negative serial types an option? 
That would of course mean a full 9 bytes per field just to hold the type (it's 
a varint) so it's an annoying overhead, but is it an option?

Versions earlier than the one that implements this wouldn't be able to read it. 
But then we've already had additions like "without rowid" where any old version 
of the library isn't going be able to understand a new database with a without 
rowid table. And if a new database doesn't use the new negative serial types 
then the resulting file is still perfectly readable by older versions.

I suppose the issue though is that rules are already in place for determining 
the affinity of a column and those rules currently give a value for , 
so if you create a new type of "decimal(a, b)" then an old version, instead of 
saying "I don't know what that is" will say "ok, that's numeric and I 
internally store it as a float" and give bad results instead of no results.

I suppose then you could then expand on the "without rowid" model, and 
designate new features at the end of the create table text so that old versions 
don't  they know what to do and would give an error...

create table foo (field1 numeric(5, 2) primary key) without rowid with numeric 
with someNewFeature;

An old database where someone had already declared something as "decimal(5,2)" 
wouldn't have the "with decimal" in the create table text, so you could still 
keep the historical affinity and not mix it up with something intended for the 
new "decimal" type.

...but that gets ugly and complicated quick.

Anyway, sorry for my rambling, I'll go hide in a corner now.


-Original Message-
From: sqlite-users  On Behalf Of 
Thomas Kurz
Sent: Wednesday, June 12, 2019 1:05 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

> It would also be a file format change, rendering about 1e12 existing
database files obsolete.

Maybe, but maybe there could be some clever implementation which doesn't break 
compatibility. I don't know about the exact internals of how SQlite stores 
values in the file. But I think there must be some identifier that tells 
whether a value is binary, integer, or float. Wouldn't it be possible to store 
both values, binary float and decimal float, in such a way that older versions 
would just read the binary float and ignore the additional data? Then, newer 
versions could read either, according on whether PRAGMA DECIMAL_MATH=TRUE is 
set or not.

Just an idea, don't know whether this would be feasible or not.

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

Re: [sqlite] readfile() enhancement request

[David Raymond]

Are you on an OS with a working edit() function?
https://www.sqlite.org/cli.html#the_edit_sql_function

In Windows using notepad I can do this for example:

insert into t values ('simple field', edit('', 'notepad'));

You can even use it for multiple fields and it'll open one at a time

insert into t (field1, field2) values (edit('This is for field1', 'notepad'), 
edit('This is for field2', 'notepad'));


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tony Papadimitriou
Sent: Friday, May 17, 2019 12:34 PM
To: SQLite mailing list
Subject: [sqlite] readfile() enhancement request

It’s quite often (for me, at least) the case I need to do something like this 
from the command line:

>sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text copied 
>from some other app’)

The problem is the multi-line text cannot be copy-pasted directly into the 
command line as the first newline will terminate the command.  So, I’ve been 
using readline() like so:

First, save the copied text into some arbitrary file (e.g., xxx), and then do

>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘xxx’))

What would be much easier is for readfile to accept standard input when 
provided with no empty name,
i.e., readfile(‘’).

Then, it would read standard input (i.e., wait for me to type/paste my text) 
until CTRL-Z/D.

---
>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘’))
My text typed/pasted here
...
...
CTRL-Z/D
---

This could also be used to capture directly into the db the output of some 
other program without first having to save it to a file.

I’m not sure how multiple readfile(‘’) on the same command should be handled, 
either not allowed, or they all get a copy of the same input.

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

Re: [sqlite] SETting a value to a field with multiple conditions

[David Raymond]

When in doubt tinker. In this case it looks like it's the dot in "set tt.b = 
'z'" that it's complaining about. Its thinking is probably "if you're updating 
a table you can't set a field from a different table, so no qualified field 
names there"


sqlite> UPDATE t as tt set tt.b = 'z' WHERE tt.a in ('p001', 'p002', 'p003', 
'p004') AND tt.idate = (SELECT max(idate) from t where a = tt.a);
Error: near ".": syntax error

sqlite> UPDATE t as tt set b = 'z' WHERE tt.a in ('p001', 'p002', 'p003', 
'p004') AND tt.idate = (SELECT max(idate) from t where a = tt.a);
changes:   4   total_changes: 23

sqlite> select * from t order by a, idate;
n   a   b   c   d   e   idate
--  --  --  --  --  --  
--
1   p001a   1   n   4   
2019-02-11
6   p001a   4   n   4   
2019-02-12
11  p001z   3   n   4   
2019-02-13
2   p002a   1   n   4   
2019-02-11
7   p002a   5   n   4   
2019-02-12
12  p002z   4   n   4   
2019-02-13
3   p003a   2   n   4   
2019-02-11
8   p003a   6   n   4   
2019-02-12
13  p003z   5   n   4   
2019-02-13
4   p004a   2   y   4   
2019-02-11
9   p004a   7   y   4   
2019-02-12
14  p004z   6   y   4   
2019-02-13
5   p005a   3   y   4   
2019-02-11
10  p005a   8   y   4   
2019-02-12
15  p005a   7   y   4   
2019-02-13

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jose Isaias Cabrera
Sent: Wednesday, May 15, 2019 10:23 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] SETting a value to a field with multiple conditions


Hi.  I know this has been probably asked before by someone, but imagine the 
following scenario:

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, 
'2019-02-13');

select * from t;

I would like to change field b to 'z' for all records in ('p001', 'p002', 
'p003', 'p004') but to the latest idate.  I know I can do multiple single 
statements, ie,

UPDATE t set b = 'z' WHERE a = 'p001' AND idate = (SELECT max(idate) FROM t 
WHERE a = 'p001');
...
UPDATE t set b = 'z' WHERE a = 'p004' AND idate = (SELECT max(idate) FROM t 
WHERE a = 'p004');

but is there a much nicer way of doing it in one call? I was thinking something 
like,

UPDATE t SET b = 'z' WHERE a IN ('p001', 'p002', 'p003', 'p004') AND idate = 
(SELECT max(idate) WHERE a = ?);

I don't know how to do the last part.  I was trying things like,

UPDATE t as tt set tt.b = 'z' WHERE tt.a in ('p001', 'p002', 'p003', 'p004') 
AND tt.idate = (SELECT max(idate) from t where a = tt.a);

This one gives errors out with,

Error: near ".": syntax error

It would be nice to know which . is the problem. :-) Any thoughts?  Thanks.

josé

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

Re: [sqlite] Instr(x, y, z) ?

[David Raymond]

Well, one way is that you can make a sub-query that uses a recursive CTE to 
find it. I've got something that appears to work, but it seems overly 
convoluted, so I'm going to feel embarrassed when someone posts a simple 
elegant version in a couple minutes.


Say for example you have

create table stuff (full_str text);
insert into stuff values ('Here kitty kitty kitty');

Then if you're looking for 'kitty' and want the start of instance #2 you could 
do this... (Using binding methods of your platform where appropriate)


select full_str, 'kitty' as looking_for, 2 as instance_no, 
coalesce(
  (with recursive foo
  (instance_no, instance_start, remaining_str, remaining_str_pos)
  as (values (0, 0, stuff.full_str, 0)
  union all
  select instance_no + 1,
  remaining_str_pos + instr(remaining_str, 'kitty'),
  substr(remaining_str, instr(remaining_str, 'kitty') + length('kitty')),
  remaining_str_pos + instr(remaining_str, 'kitty') - 1 + length('kitty')
  from foo where instr(remaining_str, 'kitty'))
  select instance_start from foo where instance_no = 2),
  0) as instance_start
from stuff;


sqlite> ...
QUERY PLAN
|--SCAN TABLE stuff
`--CORRELATED SCALAR SUBQUERY 3
   |--CO-ROUTINE 2
   |  |--SETUP
   |  |  `--SCAN CONSTANT ROW
   |  `--RECURSIVE STEP
   | `--SCAN TABLE foo
   `--SCAN SUBQUERY 2
full_strlooking_for  instance_no  instance_start
--  ---  ---  --
Here kitty kitty kitty  kitty212


Or if you're looking for an instance that's more than the number that there 
actually is, this returns 0. Or whatever value you want, just make it the 
second part of the coalesce.

full_strlooking_for  instance_no  instance_start
--  ---  ---  --
Here kitty kitty kitty  kitty40




-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Bart Smissaert
Sent: Tuesday, May 14, 2019 3:02 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Instr(x, y, z) ?

Is it possible with the existing SQL core string functions to find the
position of occurrence z of string y in string x?
The standard Instr function only does this for the first occurrence, but I
would like to specify the second, third, fourth etc. occurrence of the
specified string.
As this is with Android coding I can't make a UDF for this.

Alternatively, I could try something similar to InstrRev (as in VB6),
finding the last occurrence of string y in string x.

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

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

[David Raymond]

Ok, well that's weird. The pre-compiled version is following your results. The 
version I compiled for myself is doing the right thing.


With pre-compiled version:

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer off
sqlite> .eqp on
sqlite> .nullvalue NuLL
sqlite> .version
SQLite 3.28.0 2019-04-16 19:49:53 
884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
zlib version 1.2.11
gcc-5.2.0
sqlite> CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT 
ROWID;
sqlite> INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
QUERY PLAN
`--SCAN 5 CONSTANT ROWS
sqlite> REINDEX;
sqlite> SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 ISNULL;
QUERY PLAN
|--SEARCH TABLE t0 USING INDEX sqlite_autoindex_t0_2 (c1=? AND c0=?)
`--USING INDEX sqlite_autoindex_t0_1 FOR IN-OPERATOR
5|NuLL
sqlite> .exit


With my compiled version:

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .timer off

sqlite> .eqp on

sqlite> .nullvalue NuLL

sqlite> .version
SQLite 3.28.0 2019-04-16 19:49:53 
884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
gcc-8.1.0

sqlite> CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT 
ROWID;
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)

sqlite> INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
QUERY PLAN
`--SCAN 5 CONSTANT ROWS

sqlite> REINDEX;

sqlite> SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 ISNULL;
QUERY PLAN
|--SEARCH TABLE t0 USING INDEX sqlite_autoindex_t0_2 (c1=? AND c0=?)
`--USING INDEX sqlite_autoindex_t0_1 FOR IN-OPERATOR
c0|c1
1|NuLL
2|NuLL
3|NuLL
4|NuLL
5|NuLL

sqlite> pragma compile_options;
compile_options
ALLOW_COVERING_INDEX_SCAN
COMPILER=gcc-8.1.0
DEFAULT_AUTOMATIC_INDEX
DEFAULT_CACHE_SIZE=-65536
DEFAULT_FILE_FORMAT=4
DEFAULT_FOREIGN_KEYS
DEFAULT_JOURNAL_SIZE_LIMIT=0
DEFAULT_LOCKING_MODE=0
DEFAULT_MEMSTATUS
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_SYNCHRONOUS=0
DEFAULT_WAL_AUTOCHECKPOINT=1
DEFAULT_WAL_SYNCHRONOUS=0
DEFAULT_WORKER_THREADS=4
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_MEMORY_MANAGEMENT
ENABLE_RTREE
ENABLE_STMT_SCANSTATUS
ENABLE_UNKNOWN_SQL_FUNCTION
LIKE_DOESNT_MATCH_BLOBS
MAX_ATTACHED=125
MAX_EXPR_DEPTH=0
MAX_MMAP_SIZE=0
MAX_WORKER_THREADS=4
OMIT_SHARED_CACHE
STMTJRNL_SPILL=4194304
THREADSAFE=0
USE_ALLOCA

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Luuk
Sent: Monday, May 13, 2019 2:11 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs 
table and PRIMARY KEY DESC


On 13-5-2019 20:06, David Raymond wrote:
> What version are you using? Because it seems to be working fine for me on 
> 3.28.0 Windows CLI.
>
D:\TEMP>sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.


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

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

[David Raymond]

What version are you using? Because it seems to be working fine for me on 
3.28.0 Windows CLI.


SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table t0 (c0 primary key desc, c1 unique default null) without 
rowid;
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)

sqlite> insert into t0 (c0) values (1), (2), (3), (4), (5);
QUERY PLAN
`--SCAN 5 CONSTANT ROWS

sqlite> reindex;

sqlite> select * from t0 where t0.c0 in (select c0 from t0) and t0.c1 isnull;
QUERY PLAN
|--SEARCH TABLE t0 USING INDEX sqlite_autoindex_t0_2 (c1=? AND c0=?)
`--USING INDEX sqlite_autoindex_t0_1 FOR IN-OPERATOR
c0|c1
1|
2|
3|
4|
5|

sqlite>




-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Luuk
Sent: Monday, May 13, 2019 1:53 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs 
table and PRIMARY KEY DESC


On 13-5-2019 19:36, Manuel Rigger wrote:
> Hi everyone,
>
> Consider the following test case:
>
> CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID;
> INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
> REINDEX;
> SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 ISNULL; --
> returns 5|
>
> I would expect that the SELECT statement fetches the five rows, however,
> only one is fetched. Only when omitting the REINDEX are all five rows
> fetched.
>
> Best,
> Manuel
> ___

sqlite> SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 
ISNULL;
c0|c1
5|
sqlite> SELECT * FROM t0 WHERE (1 or t0.c0 IN (SELECT c0 FROM t0)) AND 
(1 or t0.c1 ISNULL);
c0|c1
5|
4|
3|
2|
1|
sqlite> SELECT * FROM t0 WHERE (0 or t0.c0 IN (SELECT c0 FROM t0)) AND 
(0 or t0.c1 ISNULL);
c0|c1
5|
4|
3|
2|
1|
sqlite>


The first attempt (adding '1 or') seems to be tooo obvious, but the 
second ... ;-)


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

Re: [sqlite] Series of statements results in a malformed database disk image

[David Raymond]

So it happens _before_ the update or replace? That is weird indeed.

Using "indexed by" still returns 2 rows from the index, but integrity check 
reports 1 missing, so I'm curious as to what part of the integrity got broken.



SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table t1 (c0, c1 real primary key);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)

sqlite> insert into t1 (c0, c1) values (0, 9223372036854775807), (0, 0);
QUERY PLAN
`--SCAN 2 CONSTANT ROWS

sqlite> select * from t1;
QUERY PLAN
`--SCAN TABLE t1
c0|c1
0|9.22337203685478e+18
0|0.0

sqlite> select c1 from t1 indexed by sqlite_autoindex_t1_1;
QUERY PLAN
`--SCAN TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1
c1
0.0
9.22337203685478e+18

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> update t1 set c0 = null;
QUERY PLAN
`--SCAN TABLE t1

sqlite> select * from t1;
QUERY PLAN
`--SCAN TABLE t1
c0|c1
|9.22337203685478e+18
|0.0

sqlite> select c1 from t1 indexed by sqlite_autoindex_t1_1;
QUERY PLAN
`--SCAN TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1
c1
0.0
9.22337203685478e+18

sqlite> pragma integrity_check;
integrity_check
row 1 missing from index sqlite_autoindex_t1_1

sqlite>



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, May 09, 2019 11:08 AM
To: SQLite mailing list
Subject: Re: [sqlite] Series of statements results in a malformed database disk 
image

On 5/9/19, Manuel Rigger  wrote:
>
> I discovered a sequence of statements that results in a malformed database
> disk image:
>
> CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
> INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
> UPDATE t1 SET c0 = NULL;
> UPDATE OR REPLACE t1 SET c1 = 1;
> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>
> The last statement returns the following:
> |1.0
> Error: near line 5: database disk image is malformed

Ticket here: https://www.sqlite.org/src/tktview/6c1d3febc00b22d457c7

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

Re: [sqlite] What could happen if an app crashes while calling sqlite3_rekey_v2()?

[David Raymond]

Random question from a non-C person: What is sqlite3_rekey_v2()?

I was curious, so looked for it in the docs and don't see it listed in the C 
reference...
https://www.sqlite.org/c3ref/funclist.html
...and it doesn't get any hits when put into the search box for the web page.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, May 03, 2019 11:38 AM
To: SQLite mailing list
Subject: Re: [sqlite] What could happen if an app crashes while calling 
sqlite3_rekey_v2()?

On 3 May 2019, at 4:15pm, Pasin Suriyentrakorn  wrote:

> What could happen if an app crashes while calling sqlite3_rekey_v2()? Is 
> there best practice to safely call sqlite3_rekey_v2()?

While rekey is working, the database temporarily contains both sets of some 
data.  Automatic crash recovery will see, this, recover the unconverted set, 
and wipe the converted set.  You do not need to take special precautions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: seeking information on the throughput requirement using Sqlite

[David Raymond]

Which again goes to the hardware question. If your database is on "spinning 
rust" as people call it, you can't commit a transaction (that changed the data) 
any quicker than 2 rotations of the physical disk, because the data needs to 
get synced to the disk before the next write transaction can start. So a 7,200 
rpm drive is hard limited to what, 60 commits per second? (Someone will correct 
me if I'm wrong)

Inside a transaction you can work on the cache and go as fast as you want, it's 
just when you go to save your work at the end that you have to wait.

There is a pragma (synchronous) where you can set it to not wait for a sync to 
complete and to just keep going if you're ok with something being told that it 
committed despite it potentially not actually being saved to disk yet. (Which 
is fine for a lot of things) I _think_ then that there's no delay between write 
transaction A committing and write transaction B starting. (Again, someone will 
correct me if I'm wrong)



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, May 03, 2019 11:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] Re: seeking information on the throughput 
requirement using Sqlite

On 3 May 2019, at 4:26pm, Zhu, Liang [AUTOSOL/ASSY/US]  
wrote:

> The planning transition rate is 1ms per 34-40Kb data,  we only have one 
> connection trying to write to the database.   If we have two connection, will 
> we running to database concurrency issue?

If you have normal hardware, using one connection to do all your writing will 
be simpler and allow you to use faster settings.  You can have one writing 
connection and many reading connections without slowing down access.  If you 
want to use two writing connections they may have to wait for one-another.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] seeking information on the throughput requirement using Sqlite

[David Raymond]

Within a single transaction SQLite can do things very quickly. But a reminder 
that there can be only 1 write transaction happening at a time. So my questions 
are: What is the planned _transaction_ rate? And how many different connections 
will be trying to write at once?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Zhu, Liang [AUTOSOL/ASSY/US]
Sent: Friday, May 03, 2019 10:01 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] seeking information on the throughput requirement using Sqlite

Hi Sqlite experts,

In my current project, we have the need to inserting and deleting data to and 
from the database between 1 and 10 ms for 24/7.   I am seeking the 
clarification on the following questions


  1.  what is the throughput requirements are possible using Sqlite API?, in 
other words, what is the max  speed can my inserting and deleting operation be?
  2.  When we inserting and deleting data at the speed mentioned above, what 
kind database maintenance do we need to do to maintain the performance of the 
database?
  3.  How is constant deleting and insert effect the database performance?


Thank you,

Liang Zhu | Lead Software Engineer | Branson Ultrasonics
Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA
T (203) 796-2235 | F (203) 796-0380
liang@emerson.com

The information contained in this message is confidential or protected by law. 
If you are not the intended recipient, please contact the sender and delete 
this message. Any unauthorized copying of this message or unauthorized 
distribution of the information contained herein is prohibited.

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