[sqlite] C++ ORM

[Darren Spruell]

On Mon, Mar 9, 2015 at 3:25 PM, Simon Slavin  wrote:
>
> On 9 Mar 2015, at 9:38pm, Scott Robison  wrote:
>
>> A co-worker who is working on a project is interested in finding out if
>> there is an effective ORM for C++ / SQLite. I've not used one so I'm
>> turning to the list to see if anyone has a recommendation.
>
> For those playing along at home, ORM == Object Relational Mapping.  In other 
> words you do Object-oriented programming in C++ and the objects are stored in 
> a SQLite database.
>
> I'm not aware that this problem has been solved well in any language or with 
> any database engine.  It might seem like a terrific oppotunity to write a 
> demonstration library, but differences in how languages do OO and how 
> databases store data seem to make this a difficult problem to crack.  I'd be 
> interested in any solutions that use SQLite with any popular OO language.

You did qualify it with "solved well", so I can only comment that
depending on your requirements various ORMs may work well enough for
the job. I've done projects in Python with Django's ORM (MVC web
apps), SQLAlchemy, and am finding peewee [1] to work well for a small
project I'm working on right now.

Most OO language ORMs work about like this:

Model Definition:

Model classes, fields and model instances all map to database concepts:

Thing :: Corresponds to...
Model class :: Database table
Field instance :: Column on a table
Model instance :: Row in a database table

All that said, I'm not familiar with C++ options myself.

[1] http://peewee.readthedocs.org/en/latest/index.html

-- 
Darren Spruell
phatbuckett at gmail.com

[sqlite] sqlite3_config via the PHP interface to sqlite3

[Tim Streater]

On 11 Mar 2015 at 17:46, Richard Hipp  wrote:

> Am I correct in understanding that you have enabled the error and
> warning log (https://www.sqlite.org/errlog.html) and it is not showing
> anything unusual?

The page at the link above references this call via the C interface:

  sqlite3_config(SQLITE_CONFIG_LOG, errLogCallback, pData);

Is this available somehow via the PHP sqlite3 interface (not PDO)?

--
Cheers  --  Tim

[sqlite] PRAGMA Synchronous safety

[Mario M. Westphal]

I don?t run pragma quick_check in my application ? and I don?t have any 
corrupted database here. I only ever see the log files when my application 
reports a damaged application, and there I see the ?disk image malformed? error 
message.



The diagnosis routine included in my application and which runs weekly by 
default performs logical tests on the data stored in the database, but also 
low-level tests like pragma integrity_check to ensure the SQLite database is 
physically correct. Either the damage is discovered here (in these cases I dump 
the first 100 rows returned by integrity_check into the log file) or during 
normal operation, e.g. while opening a database.

[sqlite] PRAGMA Synchronous safety

[Simon Slavin]

On 11 Mar 2015, at 5:33pm, Mario M. Westphal  wrote:

> All recent occurrences of the problem where on local hard disks / SSD disks. 
> It even affects the small configuration databases my application maintains in 
> SQLite, with the FULL synch mode for maximum safety. These also use WAL / 
> shared_cache but are only updated a couple of times per minute and have to 
> endure much less stress by concurrent multi-thread read/write.

If those databases are small then running "PRAGMA integrity_check" on them 
should be very fast.  Can you have your app run that PRAGMA each time it starts 
up and quits and show a warning if it finds anything ?

Simon.

[sqlite] PRAGMA Synchronous safety

[Mario M. Westphal]

Sorry, typing on a handheld with too thick thumbs :)



The database files can be on remote storage (e.g. Windows server or NAS) (I 
know that this may be a factor and we discussed this already and I know the 
texts on your web site about false/incomplete locking etc). I just mentioned 
that here to show that using pragma quick_check is not practicable in a 
production environment when you deal with larger databases.



All recent occurrences of the problem where on local hard disks / SSD disks. It 
even affects the small configuration databases my application maintains in 
SQLite, with the FULL synch mode for maximum safety. These also use WAL / 
shared_cache but are only updated a couple of times per minute and have to 
endure much less stress by concurrent multi-thread read/write.

[sqlite] Libstringmetrics

[Simon Slavin]

> On 11 Mar 2015, at 4:56pm, Milan Roubal  wrote:
> 
> I am also facing problem with sqlite closed by windows because of crash. On 
> same data this query crash:
> 
> select load_extension("libstringmetrics.dll");
> select a.vorname, b.vorname, a.nachname, b.nachname,
> stringmetrics("qgrams_distance","similarity",a.nachname, b.nachname, "") 
> nach_dist
> from r2 a, allUsers b
> where
> a.vorname like 'r%' and b.vorname like 'r%' and
> round(nach_dist) between 50 and 100
> ;
> 
> The first query is able to pass on some small sample, but not on all my data. 
> I am trying to find what concrete string comparison is crashing it, but it is 
> slow with crashes. Is there any way how to start logging the calls of the 
> library to find the exact data that are causing the crash?

Try this command

select a.vorname, b.vorname, a.nachname, b.nachname,
55 nach_dist
from r2 a, allUsers b
where
a.vorname like 'r%' and b.vorname like 'r%' and
round(nach_dist) between 50 and 100

If it does not crash then the problem is in your stringmetrics library.

Simon.

[sqlite] PRAGMA Synchronous safety

[Mario M. Westphal]

One process only.

[sqlite] UPDATE Problem

[Keith Medcalf]

When you store something that "looks like a number" in a column with numeric 
affinity in the table declaration, the value is converted to a numeric type.  
That is, if the "something that looks like a number" can be stored as an 
integer, then it is stored as an integer (with no decimal point and no decimal 
places).  If the "something that looks like a number" cannot be stored as an 
integer but can be stored as a floating point number, then that is how it is 
stored.  So, if you store the string '10.000' in a numeric affinity column, 
the integer 10 is stored.  If you store the string '25.10' in a numeric 
affinity column, then it is stored as the floating point value 25.1.

So your statement:

> UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'

does nothing other than convert a floating point value (say 10.1) to a text 
representation, then appending a '0' to that text so it is now '10.10'.  When 
that text value is stored back into the numeric affinity column it is converted 
back to the floating point value 10.1.

The select statement:

>SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.'

is simply showing you the text values before they are converted back into the 
original floating point value.  To see what will be stored in the database 
itself you probably need something like:

>SELECT cast(Value||0 as numeric) FROM Transactions WHERE substr(Value,-2,1)='.'

Displaying values with 2 decimal places is an "output to the user by the 
application" problem and you need to address it there (in the application -- 
right before displaying the value).  For example, you can use printf("%.2f", 
value) or its equivalent in your application language to format the output to 
your requirements for viewing.

SQLite itself is working as designed.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of Peter Haworth
>Sent: Wednesday, 11 March, 2015 17:28
>To: sqlite-users at mailinglists.sqlite.org
>Subject: [sqlite] UPDATE Problem
>
>I have a table, Transactions, with a column , Value, of type NUMERIC.
>The
>Value column is supposed to have 2 decimal places in all rows but some
>have
>only one.
>
>To correct this I issued the following command:
>
>UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'
>
>No errors on execution but nothing was changed.
>
>To help me figure out why that didn't work, I issued the following
>command:
>
>SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.'
>
>This time, all the values with only 1 decimal place were listed with a
>trailing zero added.
>
>On the face of it, the WHERE statement works fine in a SELECT statement
>but
>does not find any rows in an UPDATE statement.  Either that or the SET is
>not calculating the correct value.
>
>Is this a bug or am I missing something?
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Libstringmetrics

[Milan Roubal]

Dear Andrea,
I am also facing problem with sqlite closed by windows because of crash. 
On same data this query crash:

select load_extension("libstringmetrics.dll");
select a.vorname, b.vorname, a.nachname, b.nachname,
stringmetrics("qgrams_distance","similarity",a.nachname, b.nachname, "") 
nach_dist
from r2 a, allUsers b
where
a.vorname like 'r%' and b.vorname like 'r%' and
round(nach_dist) between 50 and 100
;

but this query pass:

select load_extension("libstringmetrics.dll");
select a.vorname, b.vorname, a.nachname, b.nachname,
stringmetrics("qgrams_distance_custom","similarity",a.nachname, 
b.nachname, "") nach_dist
from r2 a, allUsers b
where
a.vorname like 'r%' and b.vorname like 'r%' and
round(nach_dist) between 50 and 100
;

The first query is able to pass on some small sample, but not on all my 
data. I am trying to find what concrete string comparison is crashing 
it, but it is slow with crashes. Is there any way how to start logging 
the calls of the library to find the exact data that are causing the 
crash?
Thank you
Best Regards
Milan




On 2015-03-11 16:07, Milan Roubal wrote:
> Dear Andrea,
> thank you for the answer. The only idea I have so far is the line 452
> in file wrapper_functions.c
> sqlite3_result_text(context, metrics, strlen(metrics)+1, 
> SQLITE_STATIC);
> I would change that to
> sqlite3_result_text(context, metrics, strlen(metrics)+1, 
> SQLITE_TRANSIENT);
> but I don't have compiler to verify if this idea makes sense.
>   Thank you
>   Best Regards
>   Milan
> 
> On 2015-03-11 00:15, aperi2007 wrote:
>> Hi Milan,
>> 
>> thx for your report.
>> 
>> I try to see a check, and effectively I notice the problem is in the
>> qgrams_distance when used the metrics option.
>> 
>> As reported in the readme, the stringmetrics extension is based on the
>> https://github.com/jokillsya/libsimmetrics
>> library.
>> The code on the metrics is integrally take from the sample of that 
>> library.
>> 
>> I guess the problem you report is due to a static allocation (or
>> similar effect) that is not appropriated when in the same query the
>> same function is called more than one time.
>> 
>> Actually I dont know if the problem is in the sqlite side of my code
>> or in the original code of the symmetrics library.
>> 
>> I try to resolve this firstly question when and if I find a few of 
>> time.
>> 
>> However patches are welcomes.
>> :)
>> 
>> Regards,
>> 
>> Andrea Peri.
>> 
>> Il 06/03/2015 17:00, Milan Roubal ha scritto:
>>> Dear all,
>>> I have some problems with 
>>> https://github.com/aperi2007/libstringmetrics . For example:
>>> When I use "similarity" in qgrams_distance, I get good results. But 
>>> when I use "metric", it works only if it is only once in the query. 
>>> When there are 2 different usages, they somehow interfere together. 
>>> Is this the right place where to report such problem?
>>> 
 sqlite3
>>> SQLite version 3.8.8.3 2015-02-25 13:29:11
>>> Enter ".help" for usage hints.
>>> Connected to a transient in-memory database.
>>> Use ".open FILENAME" to reopen on a persistent database.
>>> 
>>> sqlite> select load_extension("libstringmetrics.dll");
>>> 
>>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>>...> stringmetrics("qgrams_distance","similarity",a.firstname, 
>>> b.firstname,"") first_dist,
>>>...> stringmetrics("qgrams_distance","similarity",a.lastname, 
>>> b.lastname,"") last_dist
>>>...> from
>>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>>...> ;
>>> Milan|Milan|Roubal|roubal|100.0|62.5
>>> 
>>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>>...> stringmetrics("qgrams_distance","metric",a.firstname, 
>>> b.firstname,"") first_dist,
>>>...> stringmetrics("qgrams_distance","metric",a.lastname, 
>>> b.lastname,"") last_dist
>>>...> from
>>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>>...> ;
>>> Milan|Milan|Roubal|roubal|6|6
>>> 
>>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>>...> stringmetrics("qgrams_distance","metric",a.firstname, 
>>> b.firstname,"") first_dist
>>>...> from
>>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>>...> ;
>>> Milan|Milan|Roubal|roubal|0
>>> 
>>>   Thank you
>>>   Best Regards
>>>   Milan
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users 

[sqlite] PRAGMA Synchronous safety

[Keith Medcalf]

>All recent occurrences of the problem where on local hard disks / SSD
>disks. It even affects the small configuration databases my application
>maintains in SQLite, with the FULL synch mode for maximum safety. These
>also use WAL / shared_cache but are only updated a couple of times per
>minute and have to endure much less stress by concurrent multi-thread
>read/write.

Why are you using shared cache, especially with WAL?  Are your devices memory 
constrained?

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

[sqlite] want to specify DB.TABLE with ".import" and ".mode insert"

[Carl Edquist]

Hello,

I observe the following two issues with the command-line sqlite3, both 
with version 3.6.20 (on RHEL6) and the latest 3.8.8.3 compiled from 
source.

It appears that the '.import FILE TABLE' command doesn't support 
specifying the database name (db.table) -- I get a "no such table" error. 
(I'd guess that it treats "db.table" as the table name rather than 
recognizing the db part, as you can do with sql commands.)

Similarly (but maybe a separate issue), the '.mode insert TABLE' output 
mode doesn't work with a database name either -- but this is a little more 
clear that it's just single-quoting the whole TABLE string, rather than 
recognizing the '.' as the db.table separator.

Hopefully the example output log below demonstrates this adequately.

A side nit-- if the table name *does* need to be quoted for '.mode 
insert', it should really be with double-quotes, since it's an identifier, 
right? (https://www.sqlite.org/lang_keywords.html)

So I would expect/suggest this output:

> .mode insert sdf.x
> select 'hi';
INSERT INTO sdf.x VALUES('hi');

> .mode insert sdf-x
> select 'hi';
INSERT INTO "sdf-x" VALUES('hi');

> .mode insert sdf-x.asd-y
> select 'hi';
INSERT INTO "sdf-x"."asd-y" VALUES('hi');


(Note that even when double-quotes are necessary, the dot should remain 
unquoted!)


But here is what I actually get:

$ ./sqlite3
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> attach ':memory:' as sdf;
sqlite> create table sdf.t1 (x);
sqlite> select * from sdf.t1;
sqlite> .mode list
sqlite> select 'hi';
hi
sqlite> .output t1.dat
sqlite> select 'hi';
sqlite> .output stdout
sqlite> -- here's the first error
sqlite> .import t1.dat sdf.t1
Error: no such table: sdf.t1
sqlite> -- only works with unqualified table name
sqlite> .import t1.dat t1
sqlite> -- but, the table is there
sqlite> select * from sdf.t1;
hi
sqlite> .mode insert sdf.t1
sqlite> select 'bye';
INSERT INTO 'sdf.t1' VALUES('bye');
sqlite> .output t1.sql
sqlite> select 'bye';
sqlite> .output stdout
sqlite> -- the 'db.table' name is broken when quoted
sqlite> .read t1.sql
Error: near line 1: no such table: sdf.t1

sqlite> -- again by hand
sqlite> INSERT INTO 'sdf.t1' VALUES('bye');
Error: no such table: sdf.t1
sqlite> -- works as it should when db.table is unquoted
sqlite> INSERT INTO sdf.t1 VALUES('bye');
sqlite> .mode list
sqlite> select * from sdf.t1;
hi
bye
sqlite>


Is this intentional?  If not, would it be easy to fix?


Thanks!
Carl

[sqlite] Weird (slow) TEMP B-TREE ORDER BY

[Dinu Marina]

Hi guys,
Sorry to be a pain, but does anyone have an idea about this issue? I 
have to recommend switching DBs this week based on this, and I am 
reluctant to do so, but we haven't been able to figure a fix either.

Thanks,
Dinu

[sqlite] Libstringmetrics

[Milan Roubal]

Dear Andrea,
thank you for the answer. The only idea I have so far is the line 452 in 
file wrapper_functions.c
sqlite3_result_text(context, metrics, strlen(metrics)+1, SQLITE_STATIC);
I would change that to
sqlite3_result_text(context, metrics, strlen(metrics)+1, 
SQLITE_TRANSIENT);
but I don't have compiler to verify if this idea makes sense.
   Thank you
   Best Regards
   Milan

On 2015-03-11 00:15, aperi2007 wrote:
> Hi Milan,
> 
> thx for your report.
> 
> I try to see a check, and effectively I notice the problem is in the
> qgrams_distance when used the metrics option.
> 
> As reported in the readme, the stringmetrics extension is based on the
> https://github.com/jokillsya/libsimmetrics
> library.
> The code on the metrics is integrally take from the sample of that 
> library.
> 
> I guess the problem you report is due to a static allocation (or
> similar effect) that is not appropriated when in the same query the
> same function is called more than one time.
> 
> Actually I dont know if the problem is in the sqlite side of my code
> or in the original code of the symmetrics library.
> 
> I try to resolve this firstly question when and if I find a few of 
> time.
> 
> However patches are welcomes.
> :)
> 
> Regards,
> 
> Andrea Peri.
> 
> Il 06/03/2015 17:00, Milan Roubal ha scritto:
>> Dear all,
>> I have some problems with 
>> https://github.com/aperi2007/libstringmetrics . For example:
>> When I use "similarity" in qgrams_distance, I get good results. But 
>> when I use "metric", it works only if it is only once in the query. 
>> When there are 2 different usages, they somehow interfere together. Is 
>> this the right place where to report such problem?
>> 
>>> sqlite3
>> SQLite version 3.8.8.3 2015-02-25 13:29:11
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> 
>> sqlite> select load_extension("libstringmetrics.dll");
>> 
>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>...> stringmetrics("qgrams_distance","similarity",a.firstname, 
>> b.firstname,"") first_dist,
>>...> stringmetrics("qgrams_distance","similarity",a.lastname, 
>> b.lastname,"") last_dist
>>...> from
>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>...> ;
>> Milan|Milan|Roubal|roubal|100.0|62.5
>> 
>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>...> stringmetrics("qgrams_distance","metric",a.firstname, 
>> b.firstname,"") first_dist,
>>...> stringmetrics("qgrams_distance","metric",a.lastname, 
>> b.lastname,"") last_dist
>>...> from
>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>...> ;
>> Milan|Milan|Roubal|roubal|6|6
>> 
>> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>>...> stringmetrics("qgrams_distance","metric",a.firstname, 
>> b.firstname,"") first_dist
>>...> from
>>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>>...> (select "Milan" as firstname, "roubal" as lastname ) b
>>...> ;
>> Milan|Milan|Roubal|roubal|0
>> 
>>   Thank you
>>   Best Regards
>>   Milan
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected behaviour when use "IN" clause and double parentheses

[Federico Fernandez Beltran]

My opinion is that the double parentheses should be ignored.

I've tested this in other engines like H2, Apache Derby or MySQL and all of
them simply ignores the double parentheses and returns the three rows.

On Wed, Mar 11, 2015 at 2:57 PM, Hick Gunter  wrote:

> The syntax diagram mandates 1 set of parentheses around the select for the
> IN operator.
>
> Putting a SELECT statement inside parentheses makes it a SCALAR SUBQUERY
> that returns (at most) 1 row with 1 column. Any extra data is ignored.
>
> Works as specified.
>
> -Urspr?ngliche Nachricht-
> Von: Federico Fernandez Beltran [mailto:federico.f.b at 47deg.com]
> Gesendet: Mittwoch, 11. M?rz 2015 14:36
> An: sqlite-users
> Betreff: [sqlite] Unexpected behaviour when use "IN" clause and double
> parentheses
>
> Hi,
>
> First of all thanks for your effort creating this database engine.
>
> I've encountered a problem adapting an ORM library to use with SQLite. The
> good news is that is easily reproducible with the command line client.
>
> The problem is when I use double parentheses in a expression with "IN"
> clause like this:
>
> SELECT * FROM tableA WHERE id IN ((SELECT foreignId FROM tableB));
>
> The above query return always only one row even if there are more rows
> that satisfy the clause.
>
> The steps to reproduce the problem are:
>
> sqlite> CREATE TABLE tableA (`id` INTEGER); CREATE TABLE tableB (`id`
> sqlite> INTEGER, `foreignId` INTEGER); INSERT INTO tableA VALUES(1);
> sqlite> INSERT INTO tableA VALUES(2); INSERT INTO tableA VALUES(3);
> sqlite> INSERT INTO tableA VALUES(4); INSERT INTO tableA VALUES(5);
> sqlite> INSERT INTO tableB VALUES(1, 1); INSERT INTO tableB VALUES(2,
> sqlite> 2); INSERT INTO tableB VALUES(3, 3); SELECT * FROM tableA WHERE
> sqlite> id IN (SELECT foreignId FROM tableB);
> 1
> 2
> 3
> sqlite> SELECT * FROM tableA WHERE id IN ((SELECT foreignId FROM
> sqlite> tableB));
> 1
> sqlite>
>
> A solution could be not putting this double parentheses but I'm using an
> ORM library and I can't change it. On the other hand others database
> engines doesn't have this behaviour and the tests pass successfully.
>
> Hope this helps to identify the problem.
>
> Best
>
> --
> Fede Fern?ndez
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Fede Fern?ndez

[sqlite] PRAGMA Synchronous safety

[Mario M. Westphal]

The problem is that the databases usually are several gigabytes in size, which 
make them too large for the users to send them to me.

A pragma quick_check takes one to two minutes, if the database is on remove 
storage even more?



I currently only have a small 30 MB database which reports ?ok? for the pragma 
quick_check but

?row 2481 missing from index idx_settings_sndidmnun? for pragma integrity_check.



This is only a small settings database, and shows none of the disk image 
malformed problems. I can send you that DB if that helps. Let me know where to.



A while ago I had a damaged database, but I have deleted for space reasons. 
Sorry.

I will try to get a damaged database here as soon as a customer reports another 
damage.

[sqlite] (no subject)

[djamel slim]

Hello,
I would like know the difference between SQLite and SQLite Manager,
and how many bytes i can register in SQLite and SQLite Manager.
Thanks.

[sqlite] (no subject)

[Simon Slavin]

On 11 Mar 2015, at 2:18pm, djamel slim  wrote:

> I would like know the difference between SQLite and SQLite Manager,

SQLite is a tool for programmers to use.  It allows for easy and flexible 
storage and retrieval of data.  If you're just someone who uses programs, and 
not a programmer, you'll never use SQLite.

SQLite Manager is one of the many programs which uses SQLite.  It was not 
written by, and is not supported by, the team that wrote SQLite.

> and how many bytes i can register in SQLite and SQLite Manager.

I can't think of any feature of SQLite which 'registers' bytes.  SQLite can 
store bytes and there are limits to the number of bytes it can store, but they 
are so high you're unlikely to ever run into them.  If you want to know the 
details you can probably find what you want here:



If not, post again with a clearer question.

Simon.

[sqlite] PRAGMA Synchronous safety

[Mario M. Westphal]

PRAGMA quick_check



Talks ~ 120s on an average size (4 GB) database. Database is on a SSD. Cold 
database, right after open, nothing in the Windows file cache yet. Running it 
again takes about 100 seconds, not much faster. Nothing you can run very often 
or in the background because it keeps the disk busy and blocks other 
transactions and database access.

[sqlite] PRAGMA Synchronous safety

[Simon Slavin]

On 11 Mar 2015, at 2:21pm, Mario M. Westphal  wrote:

> I currently only have a small 30 MB database which reports ?ok? for the 
> pragma quick_check but
> 
> ?row 2481 missing from index idx_settings_sndidmnun? for pragma 
> integrity_check.

If some of the corruption you're seeing can't be spotted by "PRAGMA 
quick_check" then don't worry about implementing it.

Simon.

[sqlite] PRAGMA Synchronous safety

[Mario M. Westphal]

I use 3.8.8.1, source id is 2015-01-20 16:51:25 
f73337e3e289915a76ca96e7a05a1a8d4e890d55

I compile the amalgamation using Visual Studio 2012.



The options I use are:


encoding='UTF-16le';

journal_mode=WAL;

wal_autocheckpoint=2; // better bulk speed inserts

locking_mode=EXCLUSIVE;

synchronous=NORMAL;



page_size=4096;

cache_size=16384;

auto_vacuum=NONE;

temp_store=MEMORY;

foreign_keys=ON;



I implement nested transactions using checkpoints, with an outer BEGIN 
TRANSACTION.

10 or more threads may have a database connection open, but each uses its own 
sqlite3 connection created via sqlite3_open_v2().
There are typically many readers, but only one writer (BEGIN IMMEDIATE) is used 
explicitly or implicity.



Unfortunately, the database damage seems to happen in rarely used sections of 
the database file so the problem is only detected during the (usually) weekly 
diagnosis runs. I?m waiting for a ticket where the user encounters the damage 
problem and still has all the log files available. Since my application logs 
all non-OK SQLite return codes plus has an error callback, such a log file may 
provide additional info about how and when the damage happened.

[sqlite] Unexpected behaviour when use "IN" clause and double parentheses

[Federico Fernandez Beltran]

Hi,

First of all thanks for your effort creating this database engine.

I've encountered a problem adapting an ORM library to use with SQLite. The
good news is that is easily reproducible with the command line client.

The problem is when I use double parentheses in a expression with "IN"
clause like this:

SELECT * FROM tableA WHERE id IN ((SELECT foreignId FROM tableB));

The above query return always only one row even if there are more rows that
satisfy the clause.

The steps to reproduce the problem are:

sqlite> CREATE TABLE tableA (`id` INTEGER);
sqlite> CREATE TABLE tableB (`id` INTEGER, `foreignId` INTEGER);
sqlite> INSERT INTO tableA VALUES(1);
sqlite> INSERT INTO tableA VALUES(2);
sqlite> INSERT INTO tableA VALUES(3);
sqlite> INSERT INTO tableA VALUES(4);
sqlite> INSERT INTO tableA VALUES(5);
sqlite> INSERT INTO tableB VALUES(1, 1);
sqlite> INSERT INTO tableB VALUES(2, 2);
sqlite> INSERT INTO tableB VALUES(3, 3);
sqlite> SELECT * FROM tableA WHERE id IN (SELECT foreignId FROM tableB);
1
2
3
sqlite> SELECT * FROM tableA WHERE id IN ((SELECT foreignId FROM tableB));
1
sqlite>

A solution could be not putting this double parentheses but I'm using an
ORM library and I can't change it. On the other hand others database
engines doesn't have this behaviour and the tests pass successfully.

Hope this helps to identify the problem.

Best

-- 
Fede Fern?ndez

[sqlite] PRAGMA Synchronous safety

[Simon Slavin]

On 11 Mar 2015, at 1:56pm, Mario M. Westphal  wrote:

> PRAGMA quick_check
> 
> Talks ~ 120s on an average size (4 GB) database.

Can you just verify for us that that PRAGMA does spot the corruption you've 
mentioned ? It may not be practical to build it into your app but knowing 
whether the corruption is spotted or not tells us useful things.

Simon.

[sqlite] Unexpected behaviour when use "IN" clause and double parentheses

[Hick Gunter]

The syntax diagram mandates 1 set of parentheses around the select for the IN 
operator.

Putting a SELECT statement inside parentheses makes it a SCALAR SUBQUERY that 
returns (at most) 1 row with 1 column. Any extra data is ignored.

Works as specified.

-Urspr?ngliche Nachricht-
Von: Federico Fernandez Beltran [mailto:federico.f.b at 47deg.com]
Gesendet: Mittwoch, 11. M?rz 2015 14:36
An: sqlite-users
Betreff: [sqlite] Unexpected behaviour when use "IN" clause and double 
parentheses

Hi,

First of all thanks for your effort creating this database engine.

I've encountered a problem adapting an ORM library to use with SQLite. The good 
news is that is easily reproducible with the command line client.

The problem is when I use double parentheses in a expression with "IN"
clause like this:

SELECT * FROM tableA WHERE id IN ((SELECT foreignId FROM tableB));

The above query return always only one row even if there are more rows that 
satisfy the clause.

The steps to reproduce the problem are:

sqlite> CREATE TABLE tableA (`id` INTEGER); CREATE TABLE tableB (`id`
sqlite> INTEGER, `foreignId` INTEGER); INSERT INTO tableA VALUES(1);
sqlite> INSERT INTO tableA VALUES(2); INSERT INTO tableA VALUES(3);
sqlite> INSERT INTO tableA VALUES(4); INSERT INTO tableA VALUES(5);
sqlite> INSERT INTO tableB VALUES(1, 1); INSERT INTO tableB VALUES(2,
sqlite> 2); INSERT INTO tableB VALUES(3, 3); SELECT * FROM tableA WHERE
sqlite> id IN (SELECT foreignId FROM tableB);
1
2
3
sqlite> SELECT * FROM tableA WHERE id IN ((SELECT foreignId FROM
sqlite> tableB));
1
sqlite>

A solution could be not putting this double parentheses but I'm using an ORM 
library and I can't change it. On the other hand others database engines 
doesn't have this behaviour and the tests pass successfully.

Hope this helps to identify the problem.

Best

--
Fede Fern?ndez
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] PRAGMA Synchronous safety

[Richard Hipp]

On 3/11/15, Mario M. Westphal  wrote:
> I don?t run pragma quick_check in my application ? and I don?t have any
> corrupted database here. I only ever see the log files when my application
> reports a damaged application, and there I see the ?disk image malformed?
> error message.
>
>
>
> The diagnosis routine included in my application and which runs weekly by
> default performs logical tests on the data stored in the database, but also
> low-level tests like pragma integrity_check to ensure the SQLite database is
> physically correct. Either the damage is discovered here (in these cases I
> dump the first 100 rows returned by integrity_check into the log file) or
> during normal operation, e.g. while opening a database.
>

OK.  As you no doubt recognize, the more information you can feed us
the better.  Like, maybe capturing the smaller configuration database
when it fails and sending it too us.

Am I correct in understanding that you have enabled the error and
warning log (https://www.sqlite.org/errlog.html) and it is not showing
anything unusual?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] data which when inserted into a table cannot be queried - a bug ?

[Martin Engelschalk]

Hi,

SELECT oid, ip, name FROM hosts ;
1|a.proper.host.name|2886748296

shows that ip = 'a.proper.host.name ' and name = 2886748296, so of 
course your queries give no data.

The problem is that your insert statement does not name the columns, 
which is never a good idea.

Martin
Am 11.03.2015 um 13:08 schrieb Jason Vas Dias:
> Good day -
> This is the first problem I've encountered with SQLite having
> used it trouble free for a number of years, so I was surprised
> when I discovered I can insert data into a table that then cannot
> be queried :
>
> I have a table:
>
> CREATE TABLE hosts
> (
>  ip  INTEGER  NOT NULL ,
>  nameTEXT NOT NULL
> );
>
> My application does:
>
> BEGIN TRANSACTION;
> INSERT INTO hosts
> VALUES ( "a.proper.host.name", 2886748296)
> ; COMMIT;
>
> And then the data cannot be queried with sqlite :
>
> $ sqlite3 my.db
> SQLite version 3.8.8.1 2015-01-20 16:51:25
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT oid, ip, name FROM hosts ;
> 1|a.proper.host.name|2886748296
> sqlite> SELECT oid, ip, name FROM hosts WHERE ip == 2886748296;
> sqlite>
>
> Note : no result found ! Why ?
> No good to query by name either :
>
> sqlite> SELECT oid, ip, name FROM hosts WHERE name == "a.proper.host.name" ;
> sqlite>
>
> Selecting by oid==1 does work, but the whole point of this table
> for my application is to map host names and addresses to an
> unique integer OID which is the key referenced in many other
> tables:
> CREATE TABLE ...
> host INTEGER REFERENCES (hosts.oid) ON DELETE CASCADE ,
> ...
>
> Please could anyone suggest why sqlite is failing to select record 1 by
> the values of any of its fields except oid (ROWID) ?
>
> sqlite> EXPLAIN SELECT oid, ip, name FROM hosts
> WHERE ip == 2886748296;
> 0|Trace|0|0|0||00|
> 1|Goto|0|16|0||00|
> 2|OpenRead|0|11|0|2|00|
> 3|OpenRead|1|12|0|k(1,B)|00|
> 4|Int64|0|1|0|2886748296|00|
> 5|SeekGe|1|13|1|1|00|
> 6|IdxGE|1|13|1|1|01|
> 7|IdxRowid|1|2|0||00|
> 8|Seek|0|2|0||00|
> 9|IdxRowid|1|3|0||00|
> 10|Column|1|0|4||00|
> 11|Column|0|1|5||00|
> 12|ResultRow|3|3|0||00|
> 13|Close|0|0|0||00|
> 14|Close|1|0|0||00|
> 15|Halt|0|0|0||00|
> 16|Transaction|0|0|0||00|
> 17|VerifyCookie|0|25|0||00|
> 18|TableLock|0|11|0|hosts|00|
> 19|Goto|0|2|0||00|
> sqlite>
>
> Any ideas anyone ? Any responses gratefully received.
>
> I'm running sqlite built from source tarball: sqlite-autoconf-3080801.tar.gz
> on an Ubuntu 14.04.02 x86_64 8-core Intel i7 (Haswell) system with gcc-4.8.2.
> I built it from source because the Ubuntu sqlite 3.8.2 does not
> support the printf() function - but the same problem happens
> when the 3.8.2 version accesses the same database file - it cannot
> select any data from the 'hosts' table by non-OID field values.
>
> Thanks & Regards,
> Jason
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

[sqlite] data which when inserted into a table cannot be queried - a bug ?

[Jason Vas Dias]

Aargh!  Thank You! Someone else's eyes do help sometimes...

On 11/03/2015, Hick Gunter  wrote:
> You have swapped data and field names in the insert.
>
> -Urspr?ngliche Nachricht-
> Von: Jason Vas Dias [mailto:jason.vas.dias at gmail.com]
> Gesendet: Mittwoch, 11. M?rz 2015 13:08
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] data which when inserted into a table cannot be queried -
> a bug ?
>
> Good day -
> This is the first problem I've encountered with SQLite having used it
> trouble free for a number of years, so I was surprised when I discovered I
> can insert data into a table that then cannot be queried :
>
> I have a table:
>
> CREATE TABLE hosts
> (
> ip  INTEGER  NOT NULL ,
> nameTEXT NOT NULL
> );
>
> My application does:
>
> BEGIN TRANSACTION;
> INSERT INTO hosts
>VALUES ( "a.proper.host.name", 2886748296) ; COMMIT;
>
> And then the data cannot be queried with sqlite :
>
> $ sqlite3 my.db
> SQLite version 3.8.8.1 2015-01-20 16:51:25 Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT oid, ip, name FROM hosts ;
> 1|a.proper.host.name|2886748296
> sqlite> SELECT oid, ip, name FROM hosts WHERE ip == 2886748296;
> sqlite>
>
> Note : no result found ! Why ?
> No good to query by name either :
>
> sqlite> SELECT oid, ip, name FROM hosts WHERE name ==
> sqlite> "a.proper.host.name" ;
> sqlite>
>
> Selecting by oid==1 does work, but the whole point of this table for my
> application is to map host names and addresses to an unique integer OID
> which is the key referenced in many other
> tables:
>CREATE TABLE ...
>host INTEGER REFERENCES (hosts.oid) ON DELETE CASCADE ,
>...
>
> Please could anyone suggest why sqlite is failing to select record 1 by the
> values of any of its fields except oid (ROWID) ?
>
> sqlite> EXPLAIN SELECT oid, ip, name FROM hosts
>WHERE ip == 2886748296;
> 0|Trace|0|0|0||00|
> 1|Goto|0|16|0||00|
> 2|OpenRead|0|11|0|2|00|
> 3|OpenRead|1|12|0|k(1,B)|00|
> 4|Int64|0|1|0|2886748296|00|
> 5|SeekGe|1|13|1|1|00|
> 6|IdxGE|1|13|1|1|01|
> 7|IdxRowid|1|2|0||00|
> 8|Seek|0|2|0||00|
> 9|IdxRowid|1|3|0||00|
> 10|Column|1|0|4||00|
> 11|Column|0|1|5||00|
> 12|ResultRow|3|3|0||00|
> 13|Close|0|0|0||00|
> 14|Close|1|0|0||00|
> 15|Halt|0|0|0||00|
> 16|Transaction|0|0|0||00|
> 17|VerifyCookie|0|25|0||00|
> 18|TableLock|0|11|0|hosts|00|
> 19|Goto|0|2|0||00|
> sqlite>
>
> Any ideas anyone ? Any responses gratefully received.
>
> I'm running sqlite built from source tarball: sqlite-autoconf-3080801.tar.gz
> on an Ubuntu 14.04.02 x86_64 8-core Intel i7 (Haswell) system with
> gcc-4.8.2.
> I built it from source because the Ubuntu sqlite 3.8.2 does not support the
> printf() function - but the same problem happens when the 3.8.2 version
> accesses the same database file - it cannot select any data from the 'hosts'
> table by non-OID field values.
>
> Thanks & Regards,
> Jason
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] data which when inserted into a table cannot be queried - a bug ?

[Simon Slavin]

> On 11 Mar 2015, at 12:10pm, Jason Vas Dias  
> wrote:
> 
> I have a table:
> 
> CREATE TABLE hosts
> (
>ip  INTEGER  NOT NULL ,
>nameTEXT NOT NULL
> );
> 
> My application does:
> 
> BEGIN TRANSACTION;
> INSERT INTO hosts
>   VALUES ( "a.proper.host.name", 2886748296)
> ; COMMIT;

Erm ... aren't those the wrong way around ?  Is that just an error in your 
email message or is it in your code ?

Simon.

[sqlite] data which when inserted into a table cannot be queried - a bug ?

[Hick Gunter]

You have swapped data and field names in the insert.

-Urspr?ngliche Nachricht-
Von: Jason Vas Dias [mailto:jason.vas.dias at gmail.com]
Gesendet: Mittwoch, 11. M?rz 2015 13:08
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] data which when inserted into a table cannot be queried - a 
bug ?

Good day -
This is the first problem I've encountered with SQLite having used it trouble 
free for a number of years, so I was surprised when I discovered I can insert 
data into a table that then cannot be queried :

I have a table:

CREATE TABLE hosts
(
ip  INTEGER  NOT NULL ,
nameTEXT NOT NULL
);

My application does:

BEGIN TRANSACTION;
INSERT INTO hosts
   VALUES ( "a.proper.host.name", 2886748296) ; COMMIT;

And then the data cannot be queried with sqlite :

$ sqlite3 my.db
SQLite version 3.8.8.1 2015-01-20 16:51:25 Enter ".help" for instructions Enter 
SQL statements terminated with a ";"
sqlite> SELECT oid, ip, name FROM hosts ;
1|a.proper.host.name|2886748296
sqlite> SELECT oid, ip, name FROM hosts WHERE ip == 2886748296;
sqlite>

Note : no result found ! Why ?
No good to query by name either :

sqlite> SELECT oid, ip, name FROM hosts WHERE name ==
sqlite> "a.proper.host.name" ;
sqlite>

Selecting by oid==1 does work, but the whole point of this table for my 
application is to map host names and addresses to an unique integer OID which 
is the key referenced in many other
tables:
   CREATE TABLE ...
   host INTEGER REFERENCES (hosts.oid) ON DELETE CASCADE ,
   ...

Please could anyone suggest why sqlite is failing to select record 1 by the 
values of any of its fields except oid (ROWID) ?

sqlite> EXPLAIN SELECT oid, ip, name FROM hosts
   WHERE ip == 2886748296;
0|Trace|0|0|0||00|
1|Goto|0|16|0||00|
2|OpenRead|0|11|0|2|00|
3|OpenRead|1|12|0|k(1,B)|00|
4|Int64|0|1|0|2886748296|00|
5|SeekGe|1|13|1|1|00|
6|IdxGE|1|13|1|1|01|
7|IdxRowid|1|2|0||00|
8|Seek|0|2|0||00|
9|IdxRowid|1|3|0||00|
10|Column|1|0|4||00|
11|Column|0|1|5||00|
12|ResultRow|3|3|0||00|
13|Close|0|0|0||00|
14|Close|1|0|0||00|
15|Halt|0|0|0||00|
16|Transaction|0|0|0||00|
17|VerifyCookie|0|25|0||00|
18|TableLock|0|11|0|hosts|00|
19|Goto|0|2|0||00|
sqlite>

Any ideas anyone ? Any responses gratefully received.

I'm running sqlite built from source tarball: sqlite-autoconf-3080801.tar.gz on 
an Ubuntu 14.04.02 x86_64 8-core Intel i7 (Haswell) system with gcc-4.8.2.
I built it from source because the Ubuntu sqlite 3.8.2 does not support the 
printf() function - but the same problem happens when the 3.8.2 version 
accesses the same database file - it cannot select any data from the 'hosts' 
table by non-OID field values.

Thanks & Regards,
Jason
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] data which when inserted into a table cannot be queried - a bug ?

[Jason Vas Dias]

Good day -

This is the first problem I've encountered with SQLite having
used it trouble free for a number of years, so I was surprised
when I discovered I can insert data into a table that then cannot
be queried :

I have a table:

CREATE TABLE hosts
(
ip  INTEGER  NOT NULL ,
nameTEXT NOT NULL
);

My application does:

BEGIN TRANSACTION;
INSERT INTO hosts
   VALUES ( "a.proper.host.name", 2886748296)
; COMMIT;

And then the data cannot be queried with sqlite :

$ sqlite3 my.db
SQLite version 3.8.8.1 2015-01-20 16:51:25
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT oid, ip, name FROM hosts ;
1|a.proper.host.name|2886748296
sqlite> SELECT oid, ip, name FROM hosts WHERE ip == 2886748296;
sqlite>

Note : no result found ! Why ?
No good to query by name either :

sqlite> SELECT oid, ip, name FROM hosts WHERE name == "a.proper.host.name" ;
sqlite>

Selecting by oid==1 does work, but the whole point of this table
for my application is to map host names and addresses to an
unique integer OID which is the key referenced in many other
tables:
   CREATE TABLE ...
   host INTEGER REFERENCES (hosts.oid) ON DELETE CASCADE ,
   ...

Please could anyone suggest why sqlite is failing to select record 1 by
the values of any of its fields except oid (ROWID) ?

sqlite> EXPLAIN SELECT oid, ip, name FROM hosts
   WHERE ip == 2886748296;
0|Trace|0|0|0||00|
1|Goto|0|16|0||00|
2|OpenRead|0|11|0|2|00|
3|OpenRead|1|12|0|k(1,B)|00|
4|Int64|0|1|0|2886748296|00|
5|SeekGe|1|13|1|1|00|
6|IdxGE|1|13|1|1|01|
7|IdxRowid|1|2|0||00|
8|Seek|0|2|0||00|
9|IdxRowid|1|3|0||00|
10|Column|1|0|4||00|
11|Column|0|1|5||00|
12|ResultRow|3|3|0||00|
13|Close|0|0|0||00|
14|Close|1|0|0||00|
15|Halt|0|0|0||00|
16|Transaction|0|0|0||00|
17|VerifyCookie|0|25|0||00|
18|TableLock|0|11|0|hosts|00|
19|Goto|0|2|0||00|
sqlite>

Any ideas anyone ? Any responses gratefully received.

I'm running sqlite built from source tarball: sqlite-autoconf-3080801.tar.gz
on an Ubuntu 14.04.02 x86_64 8-core Intel i7 (Haswell) system with gcc-4.8.2.
I built it from source because the Ubuntu sqlite 3.8.2 does not
support the printf() function - but the same problem happens
when the 3.8.2 version accesses the same database file - it cannot
select any data from the 'hosts' table by non-OID field values.

Thanks & Regards,
Jason

[sqlite] data which when inserted into a table cannot be queried - a bug ?

[Jason Vas Dias]

Good day -
This is the first problem I've encountered with SQLite having
used it trouble free for a number of years, so I was surprised
when I discovered I can insert data into a table that then cannot
be queried :

I have a table:

CREATE TABLE hosts
(
ip  INTEGER  NOT NULL ,
nameTEXT NOT NULL
);

My application does:

BEGIN TRANSACTION;
INSERT INTO hosts
   VALUES ( "a.proper.host.name", 2886748296)
; COMMIT;

And then the data cannot be queried with sqlite :

$ sqlite3 my.db
SQLite version 3.8.8.1 2015-01-20 16:51:25
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT oid, ip, name FROM hosts ;
1|a.proper.host.name|2886748296
sqlite> SELECT oid, ip, name FROM hosts WHERE ip == 2886748296;
sqlite>

Note : no result found ! Why ?
No good to query by name either :

sqlite> SELECT oid, ip, name FROM hosts WHERE name == "a.proper.host.name" ;
sqlite>

Selecting by oid==1 does work, but the whole point of this table
for my application is to map host names and addresses to an
unique integer OID which is the key referenced in many other
tables:
   CREATE TABLE ...
   host INTEGER REFERENCES (hosts.oid) ON DELETE CASCADE ,
   ...

Please could anyone suggest why sqlite is failing to select record 1 by
the values of any of its fields except oid (ROWID) ?

sqlite> EXPLAIN SELECT oid, ip, name FROM hosts
   WHERE ip == 2886748296;
0|Trace|0|0|0||00|
1|Goto|0|16|0||00|
2|OpenRead|0|11|0|2|00|
3|OpenRead|1|12|0|k(1,B)|00|
4|Int64|0|1|0|2886748296|00|
5|SeekGe|1|13|1|1|00|
6|IdxGE|1|13|1|1|01|
7|IdxRowid|1|2|0||00|
8|Seek|0|2|0||00|
9|IdxRowid|1|3|0||00|
10|Column|1|0|4||00|
11|Column|0|1|5||00|
12|ResultRow|3|3|0||00|
13|Close|0|0|0||00|
14|Close|1|0|0||00|
15|Halt|0|0|0||00|
16|Transaction|0|0|0||00|
17|VerifyCookie|0|25|0||00|
18|TableLock|0|11|0|hosts|00|
19|Goto|0|2|0||00|
sqlite>

Any ideas anyone ? Any responses gratefully received.

I'm running sqlite built from source tarball: sqlite-autoconf-3080801.tar.gz
on an Ubuntu 14.04.02 x86_64 8-core Intel i7 (Haswell) system with gcc-4.8.2.
I built it from source because the Ubuntu sqlite 3.8.2 does not
support the printf() function - but the same problem happens
when the 3.8.2 version accesses the same database file - it cannot
select any data from the 'hosts' table by non-OID field values.

Thanks & Regards,
Jason

[sqlite] Attach via prepared statement / Multiple attach

[Fabian Stumpf]

Thanks for your reply, James!

> Parameterized queries  in general let you replace any data value
> (and not metadata).  For instance, you can replace a column value, but
> not a column name.  Using that as a guide, I'm a little suprised that
>
> ATTACH DATABASE ? AS ?
>
> works, because the second parameter is metadata.

To be honest, by 'works' I currently mean "The statement prepares
alright, I can bind both parameters and execute it without ever being
presented with anything but SQLITE_OK or SQLITE_DONE". I have not
actually yet checked if the database was attached using the bound
name, so it might just have been attached with a name of '?'. I'll
check ASAP.

As for my second question (Attaching the same file multiple times
under different names), I found the answer in the documentation for
the DETACH statement[0].
It might be useful to also include that fact in the ATTACH documentation :)

Thanks for your time!

[0] https://www.sqlite.org/lang_detach.html

--
FJS

[sqlite] PRAGMA Synchronous safety

[Simon Slavin]

On 11 Mar 2015, at 10:35am, Mario M. Westphal  wrote:

> My application logs all error codes from SQLite but I have not seen anything 
> unusual in the logs provided to me by customers. I have even added an error 
> callback for SQLite (as per our recent discussion), to get more info about 
> the error. In the log files I have seem, this handler was not called and 
> there are no unusual errors. But the database corruption may be detected days 
> after the actual error happened, and then the original logs are gone. I 
> depend on my users to get logs and that does not work too well.

Please take one of the databases which has been corrupted and see what the 
results of

PRAGMA quick_check

on it are.  The intention is to find out whether you have a fast method of 
knowing that your database is corrupt.

If the above does reliably spot the type of corruption you're seeing, you can 
incorporate it in some operation your app does frequently.  Like opening or 
closing the database.  This might give you a way to spot corruption much sooner 
after it occurs than to wait for SQLite to try to use the bit of the database 
which is corrupt.

Simon.

[sqlite] PRAGMA Synchronous safety

[Mario M. Westphal]

I?m also concerned about this.



I have used SQLite since around 2008 with great success.



For the latest version of my application I decided to switch to using WAL mode 
and shared cache, to gain better performance.



I use pragma synchronous=NORMAL and for a highly-critical (yet small, 2 MB) 
database even synchronous=FULL.



My application is multi-threaded, but each thread uses a separate instance of 
SQLite. 

Typical database sizes are from 2 MB to 8 GB.



Recently I get an alarming large number of reports about databases with the 
?database disk image is malformed?.

Pragma integrity_check() reports a variety of errors in these cases. 



The typical scenario is: Windows 7 or 8. One user. Database on a local hard 
disk or SSD. No power failure. No blue-screen or other issue from the ?How to 
damage your SQLite database? help topic. The error just happens. It may cause 
SQLite to refuse loading the database, or it is discovered when my users run 
the routine diagnosis, which includes a pragma integrity_check() and if it 
returns anything other than ?OK? it flags the database as defect. Users then 
need to restore the database from the backup. A database once marked as defect 
will always stay in that state.



My application logs all error codes from SQLite but I have not seen anything 
unusual in the logs provided to me by customers. I have even added an error 
callback for SQLite (as per our recent discussion), to get more info about the 
error. In the log files I have seem, this handler was not called and there are 
no unusual errors. But the database corruption may be detected days after the 
actual error happened, and then the original logs are gone. I depend on my 
users to get logs and that does not work too well.



Questions:



Is there a higher risk in using WAL and/or shared_cache? In a mulit-threaded 
environment?



Is it safe to switch existing databases back from WAL to non-wall mode? Is 
there a specific workflow?



SQLite was always so reliable and I had maybe one report about a damaged 
database in 3 months (and usually it was a hard defect or a power failure). But 
now I get reports about damaged databases every week, sometimes even for new 
databases which have been created an hour ago. 



I fear that by using WAL/shared_cache with multiple SQLIte instances in 
multiple threads I somehow stress out SQLite, causing database damage under 
some conditions.

I would go back to the tried-and-true non-WAL no shared-cache mode and let a 
few hundred users test that for a while to see if this causes a drop in damaged 
databases.

[sqlite] (no subject)

[Adam Devita]

from
http://sourceforge.net/projects/sqlitemanager/

"SQLiteManager is a multilingual web based tool to manage SQLite
database. The programming language used is: PHP4, but work fine with
PHP5. Work just as well on a platform Linux as on Windows or MAC."

from
http://www.sqlite.org/
"SQLite is a software library that implements a self-contained,
serverless, zero-configuration, transactional SQL database engine."

One is a db engine, the other is a user interface that uses it.



On Wed, Mar 11, 2015 at 10:18 AM, djamel slim  
wrote:
> Hello,
> I would like know the difference between SQLite and SQLite Manager,
> and how many bytes i can register in SQLite and SQLite Manager.
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Weird (slow) TEMP B-TREE ORDER BY

[Richard Hipp]

On 3/11/15, Dinu Marina  wrote:
>
> Sorry to be a pain, but does anyone have an idea about this issue?
>


Maybe try:

   CREATE TEMP TABLE xyz AS SELECT  -- query without the ORDER BY clause;

Followed by:

   SELECT * FROM xyz ORDER BY ;
   DROP TABLE xyz;

does doing it that way run faster?



-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] UNION with ORDER BY collate gives 'Error: no such column:'

[Jerry Stralko]

Hello,

With the latest version of sqlite 3.8.5 and above we have notice some
of our existing queries are failing.  We noticed this with the iOS 8.2
release.

The schema example we are using for our test case

CREATE TABLE Table2
(
id INTEGER PRIMARY KEY,
name TEXT
);

CREATE TABLE Table3
(
id INTEGER,
foo_id INTEGER,
name TEXT,
PRIMARY KEY (id,foo_id)
);

Running the query below fails (using newer version of sqlite)

sqlite> select id from Table2 union select id from Table3 order by
Table2.id collate nocase asc;
Error: no such column: Table2.id

However changing the query to this works:

sqlite> select id table2_id from Table2 union select id from Table3
order by table2_id collate nocase asc;

And this works (removing the collate nocase):

sqlite> select id from Table2 union select id from Table3 order by Table2.id

Doing a fossil bisect between version 3.7.13 (good) and version 3.8.5 (bad)
$ fossil bisect log
  1 BAD 2014-06-04 14:06:34 b1ed4f2a34ba66c2
  2 GOOD2012-06-11 02:05:22 f5b5a13f7394dc14
  3 BAD 2013-08-29 23:36:49 30d38cc44904d935
  4 GOOD2013-03-05 01:46:26 8e0ced1af78d8c25
  5 BAD 2013-06-03 12:47:43 3bd5ad095b23102d
  6 GOOD2013-04-19 12:32:52 514adbbd8cf3e296
  7 BAD 2013-05-09 14:20:11 1128575d0ab24f70
  8 GOOD2013-04-29 07:01:23 e81e9ca11db09424
  9 GOOD2013-05-03 20:08:16 9314b08099e7ac99
 10 GOOD2013-05-07 12:16:48 0ba67b64de258883
 11 BAD 2013-05-08 17:06:28 1fa8c457394c9486
 12 BAD 2013-05-08 16:57:48 ce853a75068073d6
 13 GOOD2013-05-08 14:20:28 7227b61512a53464
 14 CURRENT 2013-05-08 14:20:28 7227b61512a53464

The commit that broke the query was ce853a75068073d6:

$ fossil info ce853a75068073d6
uuid: ce853a75068073d6acc8bec0819505a22c4c7e69 2013-05-08 16:57:48 UTC
parent:   7227b61512a534645c73883c2e37b4e647592e5d 2013-05-08 14:20:28 UTC
merged-from:  fc3630cdef6e2cdbfb4e7b373d1a094753e55016 2013-05-07 17:49:08 UTC
child:1fa8c457394c94864f7584e4c893ec09e685fba4 2013-05-08 17:06:28 UTC
leaf: no
tags: trunk
comment:  Make sure the ORDER BY collating sequences are
compatible with the comparison collations before using the merge
algorithm for compound SELECT statements. Fix for
  ticket [6709574d2a8d8]. (user: drh)

So is my question is: Is it my query that is broken or is this a bug?

Thanks,

Jerry

[sqlite] PRAGMA Synchronous safety

[Richard Hipp]

On 3/11/15, Mario M. Westphal  wrote:
>
> I use pragma synchronous=NORMAL and for a highly-critical (yet small, 2 MB)
> database even synchronous=FULL.

synchronous should not matter, except following a power-outage.

>
> My application is multi-threaded, but each thread uses a separate instance
> of SQLite.
>

Are there ever multiple processes writing to the database?  Or is it
always just one process at a time?
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] PRAGMA Synchronous safety

[Richard Hipp]

On 3/11/15, Mario M. Westphal  wrote:
>
> A pragma quick_check takes one to two minutes, if the database is on remove
> storage even more?
>

In your original email, you said that all database files were on a
local disk.  This message implies that sometimes they are on a remote
filesystem.  (I'm guessing that "remove storage" above is a typo for
"remote storage".)  As this is an important factor in tracking down
the problem, please let us know which it is.  Thanks.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] PRAGMA Synchronous safety

[Richard Hipp]

On 3/11/15, Mario M. Westphal  wrote:
>
> The typical scenario is: Windows 7 or 8. One user. Database on a local hard
> disk or SSD. No power failure. No blue-screen or other issue from the ?How
> to damage your SQLite database? help topic. The error just happens. It may
> cause SQLite to refuse loading the database, or it is discovered when my
> users run the routine diagnosis, which includes a pragma integrity_check()
> and if it returns anything other than ?OK? it flags the database as defect.
> Users then need to restore the database from the backup. A database once
> marked as defect will always stay in that state.

Do you have some example corrupt database files that you can share?

Can you show the complete output of "PRAGMA integrity_check"?

Can you alter your application so that it invokes "PRAGMA quick_check"
more frequently, so that the problem is detected sooner?

Can you provide us more detail on your work-load.  How many separate
threads are accessing the database from within a single process?  How
many processes are accessing the database?  Are they all writing, or
are some just reading?  What are your typical transactions?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] PRAGMA Synchronous safety

[Richard Hipp]

On 3/11/15, Mario M. Westphal  wrote:
>
> Is there a higher risk in using WAL and/or shared_cache? In a mulit-threaded
> environment?
>

No.  WAL should be "safer" than rollback as it is less prone to
problems due to malfunctioning FlushFileBuffers() system calls.
Multithreading should not be a problem.

>
>
> Is it safe to switch existing databases back from WAL to non-wall mode? Is
> there a specific workflow?

Simply run "PRAGMA journal_mode=DELETE;"  But you have to do that when
only a single database connection has the database open, or else it
will refuse.


>
>
>
> SQLite was always so reliable and I had maybe one report about a damaged
> database in 3 months (and usually it was a hard defect or a power failure).
> But now I get reports about damaged databases every week, sometimes even for
> new databases which have been created an hour ago.
>
>
>
> I fear that by using WAL/shared_cache with multiple SQLIte instances in
> multiple threads I somehow stress out SQLite, causing database damage under
> some conditions.

It should just work.  It should not be possible to "stress out"
SQLite.  We also would like to understand what is going on.

What version of SQLite are you using?  And what compiler are you using?


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] data which when inserted into a table cannot be queried - a bug ?

[John McKown]

On Wed, Mar 11, 2015 at 7:08 AM, Jason Vas Dias
 wrote:
> Good day -
> This is the first problem I've encountered with SQLite having
> used it trouble free for a number of years, so I was surprised
> when I discovered I can insert data into a table that then cannot
> be queried :
>
> I have a table:
>
> CREATE TABLE hosts
> (
> ip  INTEGER  NOT NULL ,
> nameTEXT NOT NULL
> );
>
> My application does:
>
> BEGIN TRANSACTION;
> INSERT INTO hosts
>VALUES ( "a.proper.host.name", 2886748296)
> ; COMMIT;

Looks wrong to me. The first value should be the "ip" and the second
the "name". You have them in the opposite order.

 BEGIN TRANSACTION;
 INSERT INTO hosts
VALUES ( 2886748296, "a.proper.host.name" )
 ; COMMIT;


or

 BEGIN TRANSACTION;
 INSERT INTO hosts(name,ip)
VALUES ( "a.proper.host.name", 2886748296)
 ; COMMIT;


Despite the fact that "ip" is an INTEGER, SQLite is happy to save a
character string into it. I guess this is part of the "lite".

>
> And then the data cannot be queried with sqlite :
>
> $ sqlite3 my.db
> SQLite version 3.8.8.1 2015-01-20 16:51:25
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT oid, ip, name FROM hosts ;
> 1|a.proper.host.name|2886748296
> sqlite> SELECT oid, ip, name FROM hosts WHERE ip == 2886748296;
> sqlite>
>
> Note : no result found ! Why ?
> No good to query by name either :
>
> sqlite> SELECT oid, ip, name FROM hosts WHERE name == "a.proper.host.name" ;
> sqlite>
>
> Selecting by oid==1 does work, but the whole point of this table
> for my application is to map host names and addresses to an
> unique integer OID which is the key referenced in many other
> tables:
>CREATE TABLE ...
>host INTEGER REFERENCES (hosts.oid) ON DELETE CASCADE ,
>...
>
> Please could anyone suggest why sqlite is failing to select record 1 by
> the values of any of its fields except oid (ROWID) ?
>
> sqlite> EXPLAIN SELECT oid, ip, name FROM hosts
>WHERE ip == 2886748296;
> 0|Trace|0|0|0||00|
> 1|Goto|0|16|0||00|
> 2|OpenRead|0|11|0|2|00|
> 3|OpenRead|1|12|0|k(1,B)|00|
> 4|Int64|0|1|0|2886748296|00|
> 5|SeekGe|1|13|1|1|00|
> 6|IdxGE|1|13|1|1|01|
> 7|IdxRowid|1|2|0||00|
> 8|Seek|0|2|0||00|
> 9|IdxRowid|1|3|0||00|
> 10|Column|1|0|4||00|
> 11|Column|0|1|5||00|
> 12|ResultRow|3|3|0||00|
> 13|Close|0|0|0||00|
> 14|Close|1|0|0||00|
> 15|Halt|0|0|0||00|
> 16|Transaction|0|0|0||00|
> 17|VerifyCookie|0|25|0||00|
> 18|TableLock|0|11|0|hosts|00|
> 19|Goto|0|2|0||00|
> sqlite>
>
> Any ideas anyone ? Any responses gratefully received.
>
> I'm running sqlite built from source tarball: sqlite-autoconf-3080801.tar.gz
> on an Ubuntu 14.04.02 x86_64 8-core Intel i7 (Haswell) system with gcc-4.8.2.
> I built it from source because the Ubuntu sqlite 3.8.2 does not
> support the printf() function - but the same problem happens
> when the 3.8.2 version accesses the same database file - it cannot
> select any data from the 'hosts' table by non-OID field values.
>
> Thanks & Regards,
> Jason
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] sqlite3_exec ROLLBACK command

[R.Smith]

On 2015-03-10 10:44 PM, Mayank Kumar (mayankum) wrote:
> Thanks Richard, but we call rollback only when my earlier sqlite3_step has 
> failed for some reason. Can it still undo some inserts/updates/deletes from a 
> previously executed but not yet commited tx ?
>
> Does completion of sqlite3_step with success , means a transaction was 
> successfully commited ?
>
> Are there other scenarios where , even though I am calling rollback when 
> sqlite3_step returns failed, it can undo previous txs.

Hi Mayan,

A transaction has a precise start and an end. It is started explicitly 
with "BEGIN" or "BEGIN TRANSACTION" followed by the optional "IMMEDIATE" 
etc. Once it has started, any and all following SQL statements belong to 
the transaction right up to the point it ends by issuing either "END 
TRANSACTION" or "COMMIT" (which is just another way of saying END 
TRANSACTION) or indeed you roll it back with "ROLLBACK".

I hope this clarifies your question - but to answer precisely: ANY and 
ALL previously executed but uncommitted statements that occurred after 
the last BEGIN TRANSACTION /will/ be rolled back when you issue a 
"ROLLBACK" and will otherwise only ever be committed once you issue 
"COMMIT" or indeed "END TRANSACTION".

This is the entire point of having a transactional system.

This means also that: If you've never issued a "BEGIN TRANSACTION", then 
any call to END TRANSACTION or ROLLBACK will fail but be otherwise 
harmless and have no effect. Beware that many wrappers start 
transactions automatically for you, which can cause unexpected results.

Also see:
https://www.sqlite.org/lang_transaction.html

Note: There are other ways of committing clustered sections of 
transactions using the SAVEPOINT / RELEASE SAVEPOINT mechanic.

[sqlite] Libstringmetrics

[aperi2007]

Hi Milan,

thx for your report.

I try to see a check, and effectively I notice the problem is in the 
qgrams_distance when used the metrics option.

As reported in the readme, the stringmetrics extension is based on the
https://github.com/jokillsya/libsimmetrics
library.
The code on the metrics is integrally take from the sample of that library.

I guess the problem you report is due to a static allocation (or similar 
effect) that is not appropriated when in the same query the same 
function is called more than one time.

Actually I dont know if the problem is in the sqlite side of my code or 
in the original code of the symmetrics library.

I try to resolve this firstly question when and if I find a few of time.

However patches are welcomes.
:)

Regards,

Andrea Peri.

Il 06/03/2015 17:00, Milan Roubal ha scritto:
> Dear all,
> I have some problems with 
> https://github.com/aperi2007/libstringmetrics . For example:
> When I use "similarity" in qgrams_distance, I get good results. But 
> when I use "metric", it works only if it is only once in the query. 
> When there are 2 different usages, they somehow interfere together. Is 
> this the right place where to report such problem?
>
>> sqlite3
> SQLite version 3.8.8.3 2015-02-25 13:29:11
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
>
> sqlite> select load_extension("libstringmetrics.dll");
>
> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>...> stringmetrics("qgrams_distance","similarity",a.firstname, 
> b.firstname,"") first_dist,
>...> stringmetrics("qgrams_distance","similarity",a.lastname, 
> b.lastname,"") last_dist
>...> from
>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>...> (select "Milan" as firstname, "roubal" as lastname ) b
>...> ;
> Milan|Milan|Roubal|roubal|100.0|62.5
>
> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>...> stringmetrics("qgrams_distance","metric",a.firstname, 
> b.firstname,"") first_dist,
>...> stringmetrics("qgrams_distance","metric",a.lastname, 
> b.lastname,"") last_dist
>...> from
>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>...> (select "Milan" as firstname, "roubal" as lastname ) b
>...> ;
> Milan|Milan|Roubal|roubal|6|6
>
> sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
>...> stringmetrics("qgrams_distance","metric",a.firstname, 
> b.firstname,"") first_dist
>...> from
>...> (select "Milan" as firstname, "Roubal" as lastname ) a,
>...> (select "Milan" as firstname, "roubal" as lastname ) b
>...> ;
> Milan|Milan|Roubal|roubal|0
>
>   Thank you
>   Best Regards
>   Milan
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attach via prepared statement / Multiple attach

[James K. Lowden]

On Mon, 9 Mar 2015 17:59:33 +0100
Fabian Stumpf  wrote:

> I am currently using
> > ATTACH DATABASE ? AS ?;
> to dynamically attach databases to a connection.
> In some cases, the same physical database file is attached under
> different names.
> 
> This all seems to work perfectly, although the documentation for the
> ATTACH statement specifies the name as type "database-name", not as
> "expr", so I'm not sure if specifying a bind parameter is "legal". 

I don't have a definitive answer for you, just a way to think about
it.  

Parameterized queries  in general let you replace any data value
(and not metadata).  For instance, you can replace a column value, but
not a column name.  Using that as a guide, I'm a little suprised that 

ATTACH DATABASE ? AS ?

works, because the second parameter is metadata.  But I would expect

ATTACH DATABASE ? AS db;

to be valid.  

So IMO you've been lucky so far, but the filename is fair game for
parameterization.  

--jkl