Re: [sqlite] New

[Kees Nuyt]

On Fri, 28 Feb 2014 18:54:34 -0600, Ashleigh
 wrote:

> I'm trying to view files from my iphone backup I'm not
> sure which program it is it says sqlite it is a black box
> like the windows command

That would be the sqlite command line tool, sqlite3.exe .

If you start a MS Windows command window (CMD.EXE), 
then type 
sqlite3 
, sqlite will open that file (if it really 
is a sqlite database).

Then type .h for help.

If you prefer a graphical user interface, I can recommend
the sqlite manager plugin in the Firefox web browser.

> If any one knows a better way to read and understand the files I would 
> greatly appreciate it 
>I think the file ext. is a plist. 
>Live, love & laugh. 
>

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] New

[Simon Slavin]

On 1 Mar 2014, at 12:54am, Ashleigh  wrote:

> I'm trying to view files from my iphone backup I'm not sure which program it 
> is it says sqlite it is a black box like the windows command

Sorry, your question is about the program you're using and not about SQLite.  
Please ask somewhere else.

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

Re: [sqlite] Transactions

[Igor Tandetnik]

On 2/28/2014 5:48 PM, L. Wood wrote:

Is this legal?

1) Create "INSERT" prepared statement with parameters.
2) Create a second non-parameter "BEGIN TRANSACTION" prepared statement, 
execute it with _step(), and finalize it.
3) Attach values to the parameters of the first "INSERT" prepared statement, 
_step() it, _reset() it.

Repeat 3) many times.

4) Create a third non-parameter "END TRANSACTION" prepared statement, execute 
it with _step(), and finalize it.
5) Finalize the original "INSERT" prepared statement.


Looks perfectly fine to me. That's pretty much the standard operating 
procedure.



Could these prepared statements conflict with each other?   



I don't see why they would.
--
Igor Tandetnik

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

Re: [sqlite] New

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/02/14 16:54, Ashleigh wrote:
> I'm trying to view files from my iphone backup I'm not sure which
> program it is it says sqlite it is a black box like the windows command
> If any one knows a better way to read and understand the files I would
> greatly appreciate it I think the file ext. is a plist. Live, love &
> laugh.

In addition to those, read this:

  http://catb.org/~esr/faqs/smart-questions.html

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlMRNzgACgkQmOOfHg372QTAWACglwFrY79O3Z8U0Hz7xCv3B8VM
Xv8Anjm//0wqI5eBrJ08EIFB4/OdixcU
=DOm0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New

[Ashleigh]

I'm trying to view files from my iphone backup I'm not sure which program it is 
it says sqlite it is a black box like the windows command If any one knows a 
better way to read and understand the files I would greatly appreciate it 
I think the file ext. is a plist. 
Live, love & laugh. 

On Feb 28, 2014, at 10:35 AM, RSmith  wrote:

> On 2014/02/28 17:13, Ashleigh wrote:
>> Nothing will load in SQLite just the command box
> 
> Not sure if this is a prophecy, a problem, a proposition or a premonition, 
> but I am pretty confident that it isn't an SQLite process problem.
> 
> Might you give us some more information please?
> 
> What command box opens, when you do what? How did you attempt to "load" 
> something, and which something did you try to load on which platform using 
> which SQLite tool/version/library, and when the something didn't load, is 
> there an error, an indication or just emptiness all around?
> 
> Our psychic abilities have not matured, please be explicit.
> 
>> Live, love & laugh.
> Maybe one should add: "pay attention" and "worship curiosity" to those... :)
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Transactions

[L. Wood]

Is this legal?

1) Create "INSERT" prepared statement with parameters.
2) Create a second non-parameter "BEGIN TRANSACTION" prepared statement, 
execute it with _step(), and finalize it.
3) Attach values to the parameters of the first "INSERT" prepared statement, 
_step() it, _reset() it.

Repeat 3) many times.

4) Create a third non-parameter "END TRANSACTION" prepared statement, execute 
it with _step(), and finalize it.
5) Finalize the original "INSERT" prepared statement.

Could these prepared statements conflict with each other?   
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why SQLITE_FLOAT instead of SQLITE_REAL?

[Richard Hipp]

On Fri, Feb 28, 2014 at 4:36 PM, L. Wood  wrote:

> SQLite has the REAL data type:
>
> https://www.sqlite.org/datatype3.html
>
> Then why do we have SQLITE_FLOAT instead of SQLITE_REAL? All the other
> data types (INTEGER, BLOB, TEXT, NULL) match with the SQLITE_ constants.
>
> Is this just a historical quirk that stuck, or something else?
>

Historical quirk


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



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

[sqlite] Why SQLITE_FLOAT instead of SQLITE_REAL?

[L. Wood]

SQLite has the REAL data type:

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

Then why do we have SQLITE_FLOAT instead of SQLITE_REAL? All the other data 
types (INTEGER, BLOB, TEXT, NULL) match with the SQLITE_ constants.

Is this just a historical quirk that stuck, or something else?  
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Table "Functions"

[Dominique Devienne]

Can someone tell me how the statement below works?

> From Eleytherios Stamatogiannakis :
> create table newtable as select * from READCOMPRESSEDFILE('ctable.rc');

I'm using virtual tables extensively in my application, to expose
runtime C++ objects, and I'm declaring them as shown in
http://www.sqlite.org/vtab.html 1.1 Usage, i.e. using

create virtual table vt using module(args...)

How does one create such Virtual Table *Functions*? It looks like it
creates a temporary table, but I don't see how to achieve the above
using registered custom SQL functions API, nor the VTable API.

Can those functions be used in joins?

And if so, can the arguments fed to the VTable "Function" be columns
from the preceding "tables" participating in the join?

Thanks for any help on this. This is really puzzling to me. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recommended way to delete rows

[Simon Slavin]

On 28 Feb 2014, at 5:18pm, L. Wood  wrote:

> If I do this, would you expect _step() for the "BEGIN TRANSACTION" query and 
> _step() for each "DELETE" query to be very fast, but the _step() for the "END 
> TRANSACTION" query to take most (99%) of the time?
> 
> Would you expect a similar speed boost for "INSERT"? Is one by one "INSERT" 
> in a similar way slow as molasses, and wrapping many inserts in a transaction 
> recommended?

The way SQL works is that theoretically you cannot do any command outside a 
transaction.  No changes are actually made to the database except when a 
transaction ends.  Therefore if you issue "BEGIN TRANSACTION" all the work is 
actually done when you execute "END TRANSACTION".  Until then, SQL is just 
making a list of the changes you may want to do sometime in the future.  
Therefore the "END TRANSACTION" is the command that takes all the time.

However, SQLite tries to be useful to you.  If you forget to do "BEGIN 
TRANSACTION" before you execute an INSERT or a DELETE, instead of returning an 
error message it helpfully wraps that single command inside a transaction for 
you.  Therefore that single command makes changes to the database.  So it takes 
a long time.

This is why issuing many INSERT or DELETE commands with no transaction declared 
takes so long.  For each separate command SQLite has to do make the change you 
asked for then do lots of work to make sure that the database file is 
synchronised with the journal file and the disk is updated to reflect both 
changes.  If you wrap lots of commands inside one transaction SQLite only has 
to do this synchronization once.

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

Re: [sqlite] Recommended way to delete rows

[Adam Devita]

Yes.


On Fri, Feb 28, 2014 at 12:18 PM, L. Wood  wrote:

> > I expect #2 to work best. Make sure to enclose the whole thing in an
> > explicit transaction (or at least, run large batches within explicit
> > transactions; one implicit transaction per deleted row will be slow as
> > molasses).
>
> If I do this, would you expect _step() for the "BEGIN TRANSACTION" query
> and _step() for each "DELETE" query to be very fast, but the _step() for
> the "END TRANSACTION" query to take most (99%) of the time?
>
> Would you expect a similar speed boost for "INSERT"? Is one by one
> "INSERT" in a similar way slow as molasses, and wrapping many inserts in a
> transaction recommended?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recommended way to delete rows

[L. Wood]

> I expect #2 to work best. Make sure to enclose the whole thing in an
> explicit transaction (or at least, run large batches within explicit
> transactions; one implicit transaction per deleted row will be slow as
> molasses).

If I do this, would you expect _step() for the "BEGIN TRANSACTION" query and 
_step() for each "DELETE" query to be very fast, but the _step() for the "END 
TRANSACTION" query to take most (99%) of the time?

Would you expect a similar speed boost for "INSERT"? Is one by one "INSERT" in 
a similar way slow as molasses, and wrapping many inserts in a transaction 
recommended?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New

[RSmith]

On 2014/02/28 17:13, Ashleigh wrote:

Nothing will load in SQLite just the command box


Not sure if this is a prophecy, a problem, a proposition or a premonition, but I am pretty confident that it isn't an SQLite process 
problem.


Might you give us some more information please?

What command box opens, when you do what? How did you attempt to "load" something, and which something did you try to load on which 
platform using which SQLite tool/version/library, and when the something didn't load, is there an error, an indication or just 
emptiness all around?


Our psychic abilities have not matured, please be explicit.


Live, love & laugh.

Maybe one should add: "pay attention" and "worship curiosity" to those... :)



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

[sqlite] Virtual table API performance

[Eleytherios Stamatogiannakis]

Hello,

First of all, i would like to say that, IMHO, SQLite's overall design 
and code quality is top notch. Having said that, SQLite's virtual table 
API, while beautifully designed, it is not very efficient.


We have been hitting our heads for the last few years, on the virtual 
table API, to make it work efficiently. During that time, we were 
thinking that the problem was on our side, in the code that feeds the 
virtual table API. It turns out that the virtual table API isn't very 
efficient.


The main cause of inefficiency is that it is extremely "chatty". For an 
external stream that contains many columns, "xColumn" can be called-back 
hundreds of millions of times for the stream to be consumed by SQLite. 
These callbacks have a very big cost. Let me describe a test that we did.


For our work, we use compressed streams that are being fed in SQLite 
through the virtual table API.


If we load into SQLite, the external compressed stream (containing 3M 
rows) through the virtual table API:


create table newtable as select * from READCOMPRESSEDFILE('ctable.rc');

it takes: 55 sec


If we create an external program that inserts into SQLite, the rows in 
the compressed stream one by one, using "insert into newtable values 
" and the SQLite bind API:


it takes: 19 sec (~3x faster than using the virtual table API)


Another problem with the virtual table API, is that it wrecks havok with 
VM JIT engines. Especially for tracing JIT engines, the many "small" per 
column callbacks do not permit them to specialize at all, compared to 
the specialization that a tracing JIT could achieve with one "big" 
get_an_entire_row callback.


A suggestion for improving the efficiency of the virtual table API 
naturally arises when we look at all the virtual table functions that we 
have already created. We have ~15 VT functions dealing with importing 
"all" from external sources (files, http streams, xml, clipboard, etc), 
and only one filtering VT function (a multidimensional index) "picking" 
columns to return.


So most of our queries that use VTs look like this:

create table cliptab as select * from clipboard();

, these queries most of the time select all columns from an external stream.

Based on above, an addition that improves the efficiency of the VT API 
would be an "xRow" function that the SQLite could call to get an entire 
row back (like the bind API).


Even better, and to reduce even more the callback count, would be a 
"xNextRow" function that returns the contents of the next row or EOF.


Regards,

estama.

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

Re: [sqlite] Recommended way to delete rows

[Igor Tandetnik]

On 2/28/2014 9:59 AM, L. Wood wrote:

* Prepare query containing single ID with _prepare(). Execute it with _step(). 
Repeat for each ID.
* Call _prepare() with a query with single parameter for the ID. _bind(), 
_step(), and _reset() - repeat for each ID.
* Call _prepare() with a query containing every single one of the IDs. Then 
_step() on it once. Done. Is this even possible? Since there can be a million 
IDs, I'm not sure if the query can be so long.


I expect #2 to work best. Make sure to enclose the whole thing in an 
explicit transaction (or at least, run large batches within explicit 
transactions; one implicit transaction per deleted row will be slow as 
molasses).

--
Igor Tandetnik

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

Re: [sqlite] New

[Clemens Ladisch]

Ashleigh wrote:
> Nothing will load in SQLite just the command box

I guess you clicked on "sqlite3.exe"?

What exactly are you trying to do?


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

Re: [sqlite] System.Data.SQLite fails to load

[Drago, William @ MWG - NARDAEAST]

Thanks for the reply.

Everything seems to be working fine, the only thing is I don't see SQLite in 
VEE's drop down box for "Available .NET Assemblies." I'm not sure where that 
list comes from, and I thought that even though SQLite is working, that I might 
not have installed it properly with gacutil.

-Bill

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: Friday, February 28, 2014 10:24 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite fails to load


Drago, William @ MWG - NARDAEAST wrote:
>
> I solved this problem by deploying the "System.Data.SQLite.dll"
> mixed-mode
assembly to
> the GAC. I know this is not recommended, but it's the only thing that
works.
>

It should be OK, the mixed-mode assembly is designed for these types of cases, 
when an application or environment cannot make use of app-local deployment for 
some reason.

>
> My question now is, I used gacutil.exe instead of the installer that
> comes
with SQLite.
> Was that a mistake? Should I have used the installer? I hate running
installers unless
> I know exactly what they're going to do, that's why I'm asking.
>

Using "gacutil" is fine.  That's more-or-less what the setup packages do.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recommended way to delete rows

[Clemens Ladisch]

Stephan Beal wrote:
> On Fri, Feb 28, 2014 at 3:59 PM, L. Wood  wrote:
>> I can think of three ways:
>> * Call _prepare() with a query with single parameter for the ID.
>>   _bind(), _step(), and _reset() - repeat for each ID.

This is the obvious method to use.

>> Are there other ways?
>
> i don't know that this would be better, but it's a different way:
>
> collect the list into a temp table with one field (the to-delete id), then
> do a DELETE FROM x WHERE id IN temptablename

In this case, SQLite will construct a temporary list of values
to hold all the rowids before doing the actual deletions.

The overhead of constructing this list might or might not be larger than
the overhead of executing already-prepared statements.  However, if
there are too many IDs, that temporay list will affect the caches, or
might even need to be stored on disk.


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

Re: [sqlite] System.Data.SQLite fails to load

[Joe Mistachkin]

Drago, William @ MWG - NARDAEAST wrote:
>
> I solved this problem by deploying the "System.Data.SQLite.dll" mixed-mode
assembly to
> the GAC. I know this is not recommended, but it's the only thing that
works.
> 

It should be OK, the mixed-mode assembly is designed for these types of
cases, when an
application or environment cannot make use of app-local deployment for some
reason.

>
> My question now is, I used gacutil.exe instead of the installer that comes
with SQLite.
> Was that a mistake? Should I have used the installer? I hate running
installers unless
> I know exactly what they're going to do, that's why I'm asking.
> 

Using "gacutil" is fine.  That's more-or-less what the setup packages do.

--
Joe Mistachkin

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

Re: [sqlite] Recommended way to delete rows

[Stephan Beal]

On Fri, Feb 28, 2014 at 3:59 PM, L. Wood  wrote:

> I can think of three ways:
> Which way do you recommend? Are there other ways?
>

i don't know that this would be better, but it's a different way:

collect the list into a temp table with one field (the to-delete id), then
do a DELETE FROM x WHERE id IN temptablename

That might solve your list-length problem (can't say how performantly).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] New

[Ashleigh]

Nothing will load in SQLite just the command box 

Live, love & laugh. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite fails to load

[Drago, William @ MWG - NARDAEAST]

I solved this problem by deploying the "System.Data.SQLite.dll" mixed-mode 
assembly to the GAC. I know this is not recommended, but it's the only thing 
that works.

My question now is, I used gacutil.exe instead of the installer that comes with 
SQLite. Was that a mistake? Should I have used the installer? I hate running 
installers unless I know exactly what they're going to do, that's why I'm 
asking.

Thanks,
-Bill


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Incongruous
Sent: Wednesday, February 26, 2014 9:15 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite fails to load

You've sparked my curiosity, what is this VEE programming language? Is there a 
web site I can go to read more about it?

-Original Message-
From: Joe Mistachkin
Sent: Tuesday, February 25, 2014 3:44 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite fails to load


William Drago wrote:
>
> I am using System.Data.SQLite with a relatively uncommon
> language called VEE. This is an interpreted language that
> runs in a 32bit development/runtime environment.
>

I've never heard of this language before; however, it sounds
like it hosts the CLR within its process?

>
> My application works fine when run on an internal or USB
> thumb drive. However, if I try running it from a network
> drive SQLite fails to load.
>

For the 2.0 .NET Framework, loading assemblies from a network
share can be complicated by trust issues.  Since I do not see
any network share paths in your trace output, I'm not sure if
that is the case here.

One thing that I'm noticing is that the successful load uses
the "LoadFrom context" and the failed load uses the default
context.  I'm not sure how the VEE code loads the
System.Data.SQLite assembly (or other assemblies); however,
maybe try using the "LoadFrom context" when loading from the
network share as well.

--
Joe Mistachkin

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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Recommended way to delete rows

[L. Wood]

If I already have a collection of row IDs of rows I wish to delete from a 
table, what is a recommended/fast way to delete them from the table?

The collection is just a set/array of integers, not necessarily contiguous.

I can think of three ways:

* Prepare query containing single ID with _prepare(). Execute it with _step(). 
Repeat for each ID.
* Call _prepare() with a query with single parameter for the ID. _bind(), 
_step(), and _reset() - repeat for each ID.
* Call _prepare() with a query containing every single one of the IDs. Then 
_step() on it once. Done. Is this even possible? Since there can be a million 
IDs, I'm not sure if the query can be so long.

Which way do you recommend? Are there other ways?   
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: datatype mismatch

[Richard Hipp]

On Fri, Feb 28, 2014 at 8:00 AM, Dominique Devienne wrote:

> Ran into this [datatype mismatch] error, which surprised me since I
> thought SQLite's
> dynamic typing allowed any value type to be stored in any column.
>

The exception to that rule is an INTEGER PRIMARY KEY column, which is only
allowed to store an integer.



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

Re: [sqlite] Why does type affinity declared on a foreign key column affect join speed?

[Dominique Devienne]

On Fri, Feb 28, 2014 at 6:38 AM, Bruce Sutherland
 wrote:
> We are tracking manufactured components with an alphanumeric serial number,
> which gives us a natural key. Naturally we set type affinity TEXT on the key
> column. There are many tables linked through foreign key relationships on
> this serial number.

I just posted for info on this, in SO:
http://stackoverflow.com/questions/22060197 :)

My own question is more why is it asymmetrical, i.e. depending on
which side of the join one adds a WHERE clause, the plan is indexed on
both sides, or not.

--DD

C:\Users\DDevienne>sqlite3
SQLite version 3.8.3.1 2014-02-11 14:52:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table parent (sn text primary key, data text);
sqlite> create table child_int (sn integer references parent(sn), data
text, unique(sn));
sqlite> create table child_txt (sn textreferences parent(sn), data
text, unique(sn));
sqlite> insert into parent values ('x', '1'), ('y', '2'), ('z', '3');
sqlite> insert into child_int values ('x', 'one'), ('y', 'two'), ('z', 'three');
sqlite> insert into child_txt values ('x', 'one'), ('y', 'two'), ('z', 'three');
sqlite> select p.data, c.data from parent p, child_int c on p.sn =
c.sn where p.sn = 'y';
2|two
sqlite> select p.data, c.data from parent p, child_int c on p.sn =
c.sn where c.sn = 'y';
2|two
sqlite> select p.data, c.data from parent p, child_txt c on p.sn =
c.sn where p.sn = 'y';
2|two
sqlite> select p.data, c.data from parent p, child_txt c on p.sn =
c.sn where c.sn = 'y';
2|two
sqlite> explain query plan select p.data, c.data from parent p,
child_int c on p.sn = c.sn where p.sn = 'y';
0|0|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?)
0|1|1|SEARCH TABLE child_int AS c USING INDEX
sqlite_autoindex_child_int_1 (sn=?)
sqlite> explain query plan select p.data, c.data from parent p,
child_int c on p.sn = c.sn where c.sn = 'y';
0|0|1|SEARCH TABLE child_int AS c USING INDEX
sqlite_autoindex_child_int_1 (sn=?)
0|1|0|SCAN TABLE parent AS p
sqlite> explain query plan select p.data, c.data from parent p,
child_txt c on p.sn = c.sn where p.sn = 'y';
0|0|1|SEARCH TABLE child_txt AS c USING INDEX
sqlite_autoindex_child_txt_1 (sn=?)
0|1|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?)
sqlite> explain query plan select p.data, c.data from parent p,
child_txt c on p.sn = c.sn where c.sn = 'y';
0|0|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?)
0|1|1|SEARCH TABLE child_txt AS c USING INDEX
sqlite_autoindex_child_txt_1 (sn=?)
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error: datatype mismatch

[Dominique Devienne]

Ran into this error, which surprised me since I thought SQLite's
dynamic typing allowed any value type to be stored in any column.
Either the PK or the FK seem to have some influence (with pragma
foreign_keys on or off), which I didn't expect, since the entered FK
value does match the parent row's PK.

Can anyone shed some light on this error please? --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.8.3.1 2014-02-11 14:52:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table parent (sn text primary key, data text);
sqlite> create table child_int (sn integer primary key references
parent(sn), data text);
sqlite> create table child_txt (sn textprimary key references
parent(sn), data text);
sqlite> insert into parent values ('x', '1'), ('y', '2'), ('z', '3');
sqlite> insert into child_int values ('x', 'one'), ('y', 'two'), ('z', 'three');
Error: datatype mismatch
sqlite> insert into child_txt values ('x', 'one'), ('y', 'two'), ('z', 'three');
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why does type affinity declared on a foreign key column affect join speed?

[Bruce Sutherland]

We are tracking manufactured components with an alphanumeric serial 
number, which gives us a natural key. Naturally we set type affinity 
TEXT on the key column. There are many tables linked through foreign key 
relationships on this serial number.


On one table, the type affinity of the serial number FOREIGN KEY column 
was incorrectly declared as INTEGER. Due to the flexibility of the type 
affinity system, this never showed up as an issue. We were able to 
INSERT valid TEXT values into the column with no problem. Everything 
seemed to be working fine.


As the database grew modestly, some queries became very slow. After 
making sure that all relevant columns were already automatically indexed 
due to FOREIGN KEY and PRIMARY KEY declarations, I found the culprit. 
After changing the type affinity on the bad table from INTEGER to TEXT, 
the slow query which joined on that table sped up by two orders of 
magnitude.


I'm happy enough with the end result, but I'd quite like to know why 
this happened? Was SQLite building a different type of index under the 
hood due to the declared type affinity? Did the difference in type 
affinity of the compared columns prevent the query from using the index? 
Was there some unnecessary type conversion going on which slowed things 
down?



Here is an cut down example:

CREATE TABLE IF NOT EXISTS pcb_units (
serial_no TEXT NOT NULL,
PRIMARY KEY (serial_no),
-- Constraints enforcing serial number validity.
CONSTRAINT family_check
CHECK (SUBSTR(serial_no, 6, 1) BETWEEN 'A' AND 'Z' OR 
SUBSTR(serial_no, 6, 1) BETWEEN '0' AND '9'),

CONSTRAINT model_check
CHECK (SUBSTR(serial_no, 7, 1) BETWEEN 'A' AND 'Z' OR 
SUBSTR(serial_no, 7, 1) BETWEEN '0' AND '9'),

CONSTRAINT reserved_check
CHECK (SUBSTR(serial_no, 8, 1) == '0'),
CONSTRAINT unit_number_check
CHECK (CAST(SUBSTR(serial_no, 9, 4) AS INTEGER) BETWEEN 0 AND )
);

CREATE TABLE assembly_pcb_units (
pcb_serial_no INTEGER NOT NULL,-- This is the bad column
assembly_serial_no TEXT NOT NULL,
added_time TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
added_by TEXT NOT NULL,
CONSTRAINT pcb_in_one_assembly_only
UNIQUE(pcb_serial_no),
FOREIGN KEY (assembly_serial_no)
REFERENCES assembly_units(assembly_serial_no),
FOREIGN KEY (pcb_serial_no)
REFERENCES pcb_units(serial_no),
FOREIGN KEY (added_by)
REFERENCES sti_users(user_id)
);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'Select' queries via pdo_sqlite are slow

[Simon Slavin]

On 28 Feb 2014, at 9:48am, pihu...@free.fr wrote:

> I did a fourth one with a '+' sign before the "NomJob = 'NSAVBASE'" clause 
> :[snip]
> This one is way quicker (0.055 second instead of 2.235 seconds in my latest 
> test).

That is a good experiment.  Unfortunately it depends on you knowing something 
special about SQLite you shouldn't have to know.

> Is there a better way to optimize all my queries instead of checking them one 
> by one ?

The honest answer to this is to learn how indexes work and are used.  
Theoretically the way to use SQL is to create one ideal index for each SELECT, 
DELETE and UPDATE command.  Of course, it will turn out that many commands have 
the same 'ideal' index, or one index will make them both fast enough even if it 
is not the abolute fastest.  Or that the best index for them is the one SQLite 
automatically creates on the TABLE's primary key.  So in real life you don't 
end up with that many indexes.

> I heard indexes aren't useful for a database with a single table. What do you 
> suggest ?

Sorry, but what you heard is nonsense.  Indexes operate /only/ on a single 
table.  There's no way to put data from two tables in one index (assuming the 
documentation is correct and you can't create an index on a VIEW).

I will try to give an answer useful for many people and I have to simplify this 
a little to make it fit in an email message.  To learn to optimize a command 
like

select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 
'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20

guess at many indexes like the following:

CREATE INDEX ti1 ON ReportJobs (NomJob, NomChaine, DateMonteeAuPlan)
CREATE INDEX ti2 ON ReportJobs (NomChaine, NomJob, DateMonteeAuPlan)
ANALYZE
EXPLAIN QUERY PLAN select DateMonteeAuPlan, [...]

This would give SQLite its best possible chance of having a very good index to 
use.  Then look at the output of the EXPLAIN QUERY PLAN command, see which 
index SQLite decided to use, and delete the others.

(just to explain: ANALYZE analyses how useful each index is, so if you create 
or delete indexes it can be useful to do ANALYZE again.)

Once you have done this for a few projects you will learn how indexes work and 
you will just naturally be able to guess at a good index for each command, and 
also decide whether the increase in file size makes it worth creating an index 
when it would speed up a command by only 20 or 30 milliseconds.  Of course, 
creating an index may speed up a search but it slows down changes made to a 
table (on each change, each index must be updated).  So the correct thing to do 
depends on the nature of your program: whether you want input and output to be 
faster.

And lastly the standard warning about over optimization: Remember that if you 
find yourself saying "My program must do everything as fast as possible." you 
are doing the wrong thing.  It is better to say "My program must do everything 
fast enough.".

Hope this helps.

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

Re: [sqlite] 'Select' queries via pdo_sqlite are slow

[pihug12]

Thanks Simon for your reply.

An ANALYZE; in the SQLite shell tool did nothing performance-wise.

Here are the EXPLAIN QUERY PLAN results :

sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from 
ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' limit 20;
0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 
(ANY(NomChaine) AND ANY(DateMonteeAuPlan) AND NomJob=?)
--> pdo_sqlite query() : 0 second


sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from 
ReportJobs where NomJob = 'NSAVBASE' and NomChaine = 'DCLC25736' order by 
DateMonteeAuPlan DESC limit 20;
0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 
(NomChaine=?)
--> pdo_sqlite query() : 0.001 second


sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from 
ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by 
DateMonteeAuPlan DESC limit 20;
0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 
(ANY(NomChaine) AND ANY(DateMonteeAuPlan) AND NomJob=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY
--> pdo_sqlite query() : 2.235 seconds


I did a fourth one with a '+' sign before the "NomJob = 'NSAVBASE'" clause :

sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from 
ReportJobs where +NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by 
DateMonteeAuPlan DESC limit 20;
0|0|0|SCAN TABLE ReportJobs
0|0|0|USE TEMP B-TREE FOR ORDER BY

This one is way quicker (0.055 second instead of 2.235 seconds in my latest 
test).


Is there a better way to optimize all my queries instead of checking them one 
by one ?
I heard indexes aren't useful for a database with a single table. What do you 
suggest ?

Thanks again.


- Mail original -
De: "Simon Slavin" 
À: "General Discussion of SQLite Database" 
Envoyé: Jeudi 27 Février 2014 14:08:24
Objet: Re: [sqlite] 'Select' queries via pdo_sqlite are slow


On 26 Feb 2014, at 9:09pm, pihu...@free.fr wrote:

> Benchmark (bench.php) on the « $bdd->query(...); » instruction :
> Query 2 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where 
> NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' limit 20;
> => 0.0002752075195 seconde(s)
> 
> Query 3 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where 
> NomJob = 'NSAVBASE' and NomChaine = 'DCLC25736' order by DateMonteeAuPlan 
> DESC limit 20;
> => 0 seconde(s)
> 
> Query 1 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where 
> NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan 
> DESC limit 20;
> => 1.862160767 seconde(s)
> 
> 
> Why is there so much differences between two query quasi-identical?

My guess is that withthe combination of a LIKE and another condition, the 
optimizer can't figure out which approach will give you the fastest result.

First, do an ANALYZE on that database.  You can do it inside your own software 
or just open the database with the SQLite shell tool.  The results of the 
ANALYZE are saved in the database for later use.

If that doesn't improve things try using the command

EXPLAIN QUERY PLAN [select command goes here]

on each of those SELECT statements.  You'll see which indexes the statement is 
trying to use.  Again you can execute this command in your own software or in 
the shell tool.  The results are returned as if you had done a SELECT command.

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