[sqlite] GROUP BY

[Giuseppe Costanzi]

hi to everybody,
you excuse for the preceding mails but I have had problems with this and I
have had to change provider.
However I propose my question.
I have this query, that you also see in attachment file.
SELECT
orders.order_id AS order_id,
strftime('%d-%m-%Y', orders.issued) AS issued,
suppliers.company AS company,
departments.department_id AS dep_id,
order_details.quantity AS qty,

SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
dispatch,

order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
THEN 1  ELSE 0 END) AS surplus

FROM orders
INNER JOIN departments ON (departments.department_id =
orders.department_id)

INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id)
INNER JOIN order_details ON (orders.order_id = order_details.order_id)
INNER JOIN transactions ON order_details.order_detail_id =
transactions.order_detail_id

WHERE  orders.state = 0 AND orders.enable =1
GROUP BY order_details.order_detail_id

that return such as

order_idissuedcompany   dep_id  qty,dispatch   surplus
"1""12-11-2013"  "Siemens"  "1" "6""4""2"
"1""12-11-2013"  "Siemens"  "1" "2""2""0"
"2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
"3""13-11-2013"  "Siemens"  "8" "3""3""0"

How I can group by order_id? In the example I'would return on order_id
=1:

order_idissuedcompany   dep_id  qty,dispatch   surplus
"1""12-11-2013"  "Siemens"  "1" "8""6""2"

any suggestions?

regards beppe
SELECT 
orders.order_id AS order_id,
strftime('%d-%m-%Y', orders.issued) AS issued,
suppliers.company AS company,
departments.department_id AS dep_id,
order_details.quantity AS qty,

SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
dispatch,

order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
THEN 1  ELSE 0 END) AS surplus

FROM orders
INNER JOIN departments ON (departments.department_id =
orders.department_id) 

INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id) 
INNER JOIN order_details ON (orders.order_id = order_details.order_id)
INNER JOIN transactions ON order_details.order_detail_id =
transactions.order_detail_id

WHERE  orders.state = 0 AND orders.enable =1
GROUP BY order_details.order_detail_id

that return such as

order_idissuedcompany   dep_id  qty,dispatch   surplus
"1""12-11-2013"  "Siemens"  "1" "6""4""2"
"1""12-11-2013"  "Siemens"  "1" "2""2""0"
"2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
"3""13-11-2013"  "Siemens"  "8" "3""3""0"

How I can group by order_id? In the example I'would return on order_id
=1:

order_idissuedcompany   dep_id  qty,dispatch   surplus
"1""12-11-2013"  "Siemens"  "1" "8""6""2"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Could not load file or assembly'System.Data.SQLite.dll 'or one of its dependencies on WindowsXP. Ideas?

[Joe Mistachkin]

Andreas Hofmann wrote:
>
> Anyone know why this is happening?
>

The system might not have the required Visual C++ Runtime Libraries
installed.
However, this can be done manually via one of the downloads at the following
link:

https://support.microsoft.com/kb/2019667

--
Joe Mistachkin

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

Re: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll'or one of its dependencies on Windows XP. Ideas?

[Andreas Hofmann]

Even more information. I used fuslogvw.exe to see the binding errors.  It
seems that the assembly of System.SQLite.Data.dll was loaded fine:

LOG: GAC Lookup was unsuccessful.
LOG: Attempting download of new URL file:///C:/Program
Files/N1MMLogger+/System.Data.SQLite.DLL.
LOG: Assembly download was successful. Attempting setup of file: C:\Program
Files\N1MMLogger+\System.Data.SQLite.dll
LOG: Entering run-from-source setup phase.
LOG: Assembly Name is: System.Data.SQLite, Version=1.0.86.0,
Culture=neutral, PublicKeyToken=db937bc2d44ff139
LOG: Binding succeeds. Returns assembly from C:\Program
Files\N1MMLogger+\System.Data.SQLite.dll.
LOG: Assembly is loaded in default load context.

 But the native image was not:

OG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = N1MMLogger.net.exe
Calling assembly : N1MMLogger.net, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=null.
===
LOG: Start binding of native image System.Data.SQLite, Version=1.0.86.0,
Culture=neutral, PublicKeyToken=db937bc2d44ff139.
WRN: No matching native image found.
LOG: IL assembly loaded from C:\Program
Files\N1MMLogger+\System.Data.SQLite.dll.


Anyone know why this is happening?

Thanks
Andreas


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andreas Hofmann
Sent: Wednesday, November 13, 2013 7:59 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Could not load file or assembly
'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas?

As a followup question:

Should Windows XP be supported?  Is there anything special that is needed?
As I understand it, the dll is supposed to have .net interop and sqlite all
inside. It works fine in W7, but not in XP... Any way to troubleshoot what
is missing?  Would ildasm tell me?

Thanks
Andreas


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andreas Hofmann
Sent: Wednesday, November 13, 2013 5:19 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll'
or one of its dependencies on Windows XP. Ideas?

Deploying the same app on W7 32, W7 64, and XP 32.  Using XCOPY deployment.
W7 are fine, in XP it complains about this:

 

System.IO.FileNotFoundException: Could not load file or assembly
'System.Data.SQLite.dll' or one of its dependencies. The specified module
could not be found.

File name: 'System.Data.SQLite.dll'

   at N1MMLogger.Net.DBInterface..ctor()

   at N1MMLogger.Net.AdminDatabase..ctor(Boolean IsReorganize) in
C:\Users\andreash\Downloads\N1MMSource\N1MM Logger on .NET\N1MM
Logger.Net\Classes\DBInterface.vb:line 270

 

 

However the file is there. 

 

907,264 System.Data.SQLite.dll,  verion 1.0.86.0, SQLite version: 3.7.17. I
assume this is the interop file that also has the native sqlite inside.

 

 

As I mentioned, I deployed the same folder (also after deploying .NET 4.0).

 

Any ideas?

 

Thanks

Andreas

 

___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key vs index (continue)

[Igor Korot]

Igor,

On Wed, Nov 13, 2013 at 7:34 PM, Igor Tandetnik  wrote:
> On 11/13/2013 10:13 PM, Igor Korot wrote:
>>
>> What I don't understand is:
>>
>> All fields in WHERE clause are declared as primary/foreign keys. And
>> it still gives full table scan on the first iteration.
>
>
> Defining a foreign key doesn't create any indexes, and doesn't in any way
> affect the behavior of SELECT statements...

But then in order to speed up this query I need to create an index
leaguescorehitter(scoreid), right?

Thank you.

>
>
>> Also is it checking conditions from left to right or right to left?
>
>
> Who is checking which conditions?
>
>
>> Meaning the first row in the plan indicate equality against
>> scorehits/scorepitch.scoreid, right?
>
>
> The first row in the plan is SCAN TABLE. It visits every row in the table,
> regardless of any equality of anything to anything else.
> --
> Igor Tandetnik
>
> ___
> 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] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas?

[Andreas Hofmann]

As a followup question:

Should Windows XP be supported?  Is there anything special that is needed?
As I understand it, the dll is supposed to have .net interop and sqlite all
inside. It works fine in W7, but not in XP... Any way to troubleshoot what
is missing?  Would ildasm tell me?

Thanks
Andreas


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andreas Hofmann
Sent: Wednesday, November 13, 2013 5:19 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll'
or one of its dependencies on Windows XP. Ideas?

Deploying the same app on W7 32, W7 64, and XP 32.  Using XCOPY deployment.
W7 are fine, in XP it complains about this:

 

System.IO.FileNotFoundException: Could not load file or assembly
'System.Data.SQLite.dll' or one of its dependencies. The specified module
could not be found.

File name: 'System.Data.SQLite.dll'

   at N1MMLogger.Net.DBInterface..ctor()

   at N1MMLogger.Net.AdminDatabase..ctor(Boolean IsReorganize) in
C:\Users\andreash\Downloads\N1MMSource\N1MM Logger on .NET\N1MM
Logger.Net\Classes\DBInterface.vb:line 270

 

 

However the file is there. 

 

907,264 System.Data.SQLite.dll,  verion 1.0.86.0, SQLite version: 3.7.17. I
assume this is the interop file that also has the native sqlite inside.

 

 

As I mentioned, I deployed the same folder (also after deploying .NET 4.0).

 

Any ideas?

 

Thanks

Andreas

 

___
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] Foreign key vs index (continue)

[Keith Medcalf]

>What I don't understand is:
>
>All fields in WHERE clause are declared as primary/foreign keys. And
>it still gives full table scan on the first iteration.
>

Foreign Key declarations specify referential integrity constraints.  Primary 
Key declarations necessarily create an index to enforce uniqueness (and so that 
the value can be used to find the row referenced by the key).  Similarly a 
UNIQUE constraint also creates an index to enforce uniqueness.  Query plan 
optimization is based on choosing the lowest cost nested loop order because 
*appropriate indexes exist* which can be used to find the data of interest more 
quickly than a sequential scan of the entire table looking for rows which 
satisfy the selection constraints (WHERE clauses) -- or which create visitation 
order that might partially satisfy an ORDER BY or GROUP BY ...

Have you declared useful indexes so that the data you are looking for can be 
found without having to resort to a table scan?  You need to manually create 
indexes on *both* the source and target of a foreign key if they are not 
otherwise created by a constraint which does create an index, such as "primary 
key" or "unique" constraints -- they are not created for you.

If you have created appropriate indexes, have you run the ANALYZE command to 
gather index distribution statistics for the query optimizer?




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

Re: [sqlite] Foreign key vs index (continue)

[Igor Tandetnik]

On 11/13/2013 10:13 PM, Igor Korot wrote:

What I don't understand is:

All fields in WHERE clause are declared as primary/foreign keys. And
it still gives full table scan on the first iteration.


Defining a foreign key doesn't create any indexes, and doesn't in any 
way affect the behavior of SELECT statements.



Also is it checking conditions from left to right or right to left?


Who is checking which conditions?


Meaning the first row in the plan indicate equality against
scorehits/scorepitch.scoreid, right?


The first row in the plan is SCAN TABLE. It visits every row in the 
table, regardless of any equality of anything to anything else.

--
Igor Tandetnik

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

[sqlite] Foreign key vs index (continue)

[Igor Korot]

Hi, ALL,
Now that the loop in the query is fixed I want to come back to this topic.

Here is modified query plan:

sqlite> EXPLAIN QUERY PLAN SELECT playersinleague.playerid,scorehits.scorename,l
eaguescorehitter.value FROM playersinleague,scorehits,leaguescorehitter WHERE sc
orehits.scoreid = leaguescorehitter.scoreid AND playersinleague.playerid = leagu
escorehitter.playerid AND playersinleague.playerid = 1 AND playersinleague.id =
1 UNION ALL SELECT playersinleague.playerid,scorepitch.scorename,leaguescorepitc
her.value FROM playersinleague,scorepitch,leaguescorepitcher WHERE playersinleag
ue.playerid = leaguescorepitcher.playerid AND leaguescorepitcher.scoreid = score
pitch.scoreid AND playersinleague.playerid = 1 AND playersinleague.id = 1;
1|0|2|SCAN TABLE leaguescorehitter (~100 rows)
1|1|0|SEARCH TABLE playersinleague USING COVERING INDEX sqlite_autoindex_players
inleague_1 (id=? AND playerid=?) (~1 rows)
1|2|1|SEARCH TABLE scorehits USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
2|0|2|SCAN TABLE leaguescorepitcher (~100 rows)
2|1|0|SEARCH TABLE playersinleague USING COVERING INDEX sqlite_autoindex_players
inleague_1 (id=? AND playerid=?) (~1 rows)
2|2|1|SEARCH TABLE scorepitch USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

What I don't understand is:

All fields in WHERE clause are declared as primary/foreign keys. And
it still gives full table scan on the first iteration.

Can someone please help to solve it?
Also is it checking conditions from left to right or right to left?
Meaning the first row in the plan indicate equality against
scorehits/scorepitch.scoreid, right?

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

Re: [sqlite] Can I use primary key field as a foreign key?

[Simon Slavin]

On 14 Nov 2013, at 2:16am, Igor Korot  wrote:

> Simon,
> 
> On Wed, Nov 13, 2013 at 5:53 PM, Simon Slavin  wrote:
>> 
>> On 14 Nov 2013, at 12:53am, Igor Korot  wrote:
>> 
>>> Can I use the same field as both primary key and foreign key?
>> 
>> Yes.  Good idea, in fact.
> 
> Is it documented anywhere - either on SQLite documentation or in a standard?

Not that I know of.

A foreign key involves two fields in different tables.  I've done one way 
times.  In fact almost all foreign keys relate to a primary key.  The other way 
I don't think I've used but I see no reason why it shouldn't work.

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

Re: [sqlite] Can I use primary key field as a foreign key?

[Igor Korot]

Simon,

On Wed, Nov 13, 2013 at 5:53 PM, Simon Slavin  wrote:
>
> On 14 Nov 2013, at 12:53am, Igor Korot  wrote:
>
>> Can I use the same field as both primary key and foreign key?
>
> Yes.  Good idea, in fact.

Is it documented anywhere - either on SQLite documentation or in a standard?

Thank you for confirming.

>
> 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

Re: [sqlite] Can I use primary key field as a foreign key?

[Simon Slavin]

On 14 Nov 2013, at 12:53am, Igor Korot  wrote:

> Can I use the same field as both primary key and foreign key?

Yes.  Good idea, in fact.

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

Re: [sqlite] syncing two sqlite databases and db corruption

[Simon Slavin]

On 14 Nov 2013, at 12:56am, Mayank Kumar (mayankum)  wrote:

> When rsyncing , we sync both the db file and the journal file(we sync the 
> complete folder).
> 
> 
> My question , if primary is in the middle of a transaction

Wait.  You're syncing by copying files, when one or other file may be open ?  
Worse still, while one of them might be in the middle of a transaction ?  No, 
that's just not going to work.  All sorts of issues, including corruption and 
forgetting the most recent transaction.

If you want to sync by copying files, make sure none of them are open while 
you're copying.
If you need to sync while the primary file may be being written, use the SQLite 
backup API:

http://www.sqlite.org/backup.html

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

[sqlite] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas?

[Andreas Hofmann]

Deploying the same app on W7 32, W7 64, and XP 32.  Using XCOPY deployment.
W7 are fine, in XP it complains about this:

 

System.IO.FileNotFoundException: Could not load file or assembly
'System.Data.SQLite.dll' or one of its dependencies. The specified module
could not be found.

File name: 'System.Data.SQLite.dll'

   at N1MMLogger.Net.DBInterface..ctor()

   at N1MMLogger.Net.AdminDatabase..ctor(Boolean IsReorganize) in
C:\Users\andreash\Downloads\N1MMSource\N1MM Logger on .NET\N1MM
Logger.Net\Classes\DBInterface.vb:line 270

 

 

However the file is there. 

 

907,264 System.Data.SQLite.dll,  verion 1.0.86.0, SQLite version: 3.7.17. I
assume this is the interop file that also has the native sqlite inside.

 

 

As I mentioned, I deployed the same folder (also after deploying .NET 4.0).

 

Any ideas?

 

Thanks

Andreas

 

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

[sqlite] syncing two sqlite databases and db corruption

[Mayank Kumar (mayankum)]

Hi

This is a follow up questions related to the db corruption question I asked a 
few days earlier. Our architecture is such that we have two roles primary and 
secondary and the secondary syncs(rsyncs) the sqlite db from the primary from 
time to time. Also the primary sometime sends records to secondary to 
write(incremental syncs) to its db using sqlite commands.


When rsyncing , we sync both the db file and the journal file(we sync the 
complete folder).


My question , if primary is in the middle of a transaction and the secondary 
syncs both the db and the journal file using rsync, can this corrupt the sqlite 
db on the secondary side or when the next write happens on the secondary side, 
the sqlite will detect the presence of the journal (although its copied from 
the primary) and revert the last transaction before doing any new writes ?

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

[sqlite] Can I use primary key field as a foreign key?

[Igor Korot]

Hi, ALL,
Can I use the same field as both primary key and foreign key?

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

Re: [sqlite] SQL 2003 in sqlite

[Alek Paunov]

On 12.11.2013 10:45, Sylvain Pointeau wrote:

The merge statement is really missing in sqlite...


Definitely it is missing ... for maybe 0.05% of the (advanced) SQLite 
users :-). Much large group missing UPDATE and DELETE statements over 
joins at first place.



Is there any plan to integrate this SQL 2003 syntax in sqlite?


Your question is already 36+ hours old. Because the SQLite[*] core team 
(consisting of *3 developers* including the leading architect) is 
usually very responsive when the subject is considered important (we 
often have seen bugfixes and improvements done literally over the 
night), the answer is obviously "No" - at least from the core team side.


But ... SQLite is very simple and smart architecture. Almost every part 
is plugable and the interfaces between the moving parts are rigorously 
documented.


http://www.sqlite.org/arch.html

For the MERGE RFE implementation you need just an extension of the SQL 
frontend (first tier of the architecture) which translates SQL to the 
simple and well evolved bytecode.


http://www.sqlite.org/opcode.html
sqlite3 :memory: 'explain select name from sqlite_master'

Actually SQLite is close to MERGE support in the sense that hypothetical 
MERGE VDBE bytecode is relatively simple function of the bytecodes of 
the three "elementary" statements which MERGE combines (insert, update, 
delete).


Naturally, SQLite already generates MERGE "sub" statements bytecode for 
every version of the engine. I.e. you have valid input to the bytecode 
morphing transformation at hand.


So, if you are really like MERGE, and you are hacker with few dozens of 
free hours - give it a go, many people here will (at least) follow with 
interest your experiment.


If you are not - help the listening hackers (they are many here, but 
believe me - 2 .. 5 max of them are regular MERGE users :-) ) to 
understand the benefits of your RFE.


Cheers,
Alek

[*] The most used DB in the world

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

Re: [sqlite] What takes the most time

[Simon Slavin]

On 13 Nov 2013, at 10:45pm, L. Wood  wrote:

> If a single _step() call takes a long time to execute (a few minutes),

A few minutes is extremely unlikely.  You'd have to be doing OFFSET 50 or 
doing an ad hoc request which needs to make up its own index for a table of a 
few million rows.  If none of your tables are longer than 10 rows and you 
have half-way decent indexes just don't worry about it.

> is my only option to just wait for it?

Don't forget step is spending most of its time doing calculations and accessing 
disk cache.  You're probably not going to save much time if your computer has 
to multitask.

Write your code first.  Run it.  Only then if it's too slow worry about 
optimization.

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

Re: [sqlite] What takes the most time

[Igor Tandetnik]

On 11/13/2013 5:45 PM, L. Wood wrote:

If a single _step() call takes a long time to execute (a few minutes), is my 
only option to just wait for it? Does SQLite not allow any kind of callback 
mechanism for each _step() to indicate how many percentages are done (or how 
many bytes have been read/written), and allow for cancellation of the process?  
  


http://www.sqlite.org/c3ref/progress_handler.html

--
Igor Tandetnik

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

Re: [sqlite] What takes the most time

[David de Regt]

You'll need to use threading if you want to make queries abortable.  Another 
thread will need to call sqlite3_interrupt(handle) to abort it.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of L. Wood
Sent: Wednesday, November 13, 2013 2:45 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What takes the most time

> Yes, _step would generally take the most time, as that's where the 
> actual work happens.
>
> Yes, you would call _step once for every row produced by SELECT (as 
> well as certain PRAGMAs), and only once for other statements that 
> don't produce a resultset.

Great, thank you. Another question:

If a single _step() call takes a long time to execute (a few minutes), is my 
only option to just wait for it? Does SQLite not allow any kind of callback 
mechanism for each _step() to indicate how many percentages are done (or how 
many bytes have been read/written), and allow for cancellation of the process?  

___
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] What takes the most time

[L. Wood]

> Yes, _step would generally take the most time, as that's where the
> actual work happens.
>
> Yes, you would call _step once for every row produced by SELECT (as well
> as certain PRAGMAs), and only once for other statements that don't
> produce a resultset.

Great, thank you. Another question:

If a single _step() call takes a long time to execute (a few minutes), is my 
only option to just wait for it? Does SQLite not allow any kind of callback 
mechanism for each _step() to indicate how many percentages are done (or how 
many bytes have been read/written), and allow for cancellation of the process?  

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

Re: [sqlite] What takes the most time

[Igor Tandetnik]

Yes, _step would generally take the most time, as that's where the 
actual work happens.


Yes, you would call _step once for every row produced by SELECT (as well 
as certain PRAGMAs), and only once for other statements that don't 
produce a resultset.


Igor Tandetnik

On 11/13/2013 4:42 PM, L. Wood wrote:

These are the main SQLite functions:

_open()
_prepare_v2()
_step()
_column()
_finalize()
_close()

Suppose I'm calling these once to execute a single SQL statement.

Can you give me a feel for which of these functions takes the longest to 
execute? There are two cases I'm interested in:


1.
"Read statements" like this: "SELECT * FROM SomeHugeTable;"

I guess that _step() will take most of the time, and that we will have to call 
it multiple times for each row in the table? Correct me if I'm wrong.


2.
"Write statements" like these:

"INSERT INTO SomeHugeTable (col1,col2) VALUES (val1,val2);"
or
"UPDATE SomeHugeTable SET col1='whatever' WHERE rowid=10566;"

Here I would also guess that _step() would take the longest? Also, am I correct 
in assuming that both of these statements would only need a single _step() call 
(since there are no results returned)?  




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

[sqlite] What takes the most time

[L. Wood]

These are the main SQLite functions:

_open()
_prepare_v2()
_step()
_column()
_finalize()
_close()

Suppose I'm calling these once to execute a single SQL statement.

Can you give me a feel for which of these functions takes the longest to 
execute? There are two cases I'm interested in:


1.
"Read statements" like this: "SELECT * FROM SomeHugeTable;"

I guess that _step() will take most of the time, and that we will have to call 
it multiple times for each row in the table? Correct me if I'm wrong.


2.
"Write statements" like these:

"INSERT INTO SomeHugeTable (col1,col2) VALUES (val1,val2);"
or
"UPDATE SomeHugeTable SET col1='whatever' WHERE rowid=10566;"

Here I would also guess that _step() would take the longest? Also, am I correct 
in assuming that both of these statements would only need a single _step() call 
(since there are no results returned)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL 2003 in sqlite

[Petite Abeille]

On Nov 13, 2013, at 8:03 PM, Simon Slavin  wrote:

> or do they need some specific functionality which MERGE has and SQLite 
> doesn’t.

There are no equivalent in SQLite at all. One cannot do even the most basic of 
upsert with the SQL available. The functionality is just not there. In other 
words, one cannot perform an insert or update combo without resorting to 
procedural programming outside of SQLite.

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

Re: [sqlite] SQL 2003 in sqlite

[Simon Slavin]

On 13 Nov 2013, at 6:51pm, Petite Abeille  wrote:

> 
> On Nov 13, 2013, at 4:38 PM, Simon Slavin  wrote:
> 
>> I still don't know what function that MERGE does that you can't do in SQLite.
> 
> Are you asking what MERGE does?

I'm wondering what particular thing MERGE does that this person needs, which 
doesn't happen if they use just the single commands INSERT OR REPLACE or UPDATE 
OR REPLACE.

In other words, whether they want the MERGE command just because it exists in a 
later spec (and if they get it are they going to ask for all the other new 
commands one by one), or do they need some specific functionality which MERGE 
has and SQLite doesn't.

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

Re: [sqlite] SQL 2003 in sqlite

[Petite Abeille]

On Nov 13, 2013, at 4:38 PM, Simon Slavin  wrote:

> I still don't know what function that MERGE does that you can't do in SQLite.

Are you asking what MERGE does? Or if it’s possible to somehow emulate that 
functionality in SQLite? If the later, then yes,  surely one could patch 
various statements and procedural logic to achieve the same effect. But see 
Truth № 3 as per RFC 1925.

[1] http://tools.ietf.org/html/rfc1925



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

Re: [sqlite] GROUP BY on SUM CASE

[Igor Tandetnik]

On 11/13/2013 9:51 AM, g.costa...@email.it wrote:

I've this


SELECT ...
GROUP BY order_details.order_detail_id

How I can group by order_id?


"GROUP BY orders.order_id", I suppose. What exactly is the nature of the 
problem?

--
Igor Tandetnik

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

Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)

[Simon Slavin]

On 13 Nov 2013, at 4:04pm, Sandu Buraga  wrote:

> But, after I enable the WAL mode the
> consequent transactions (in any other threads) will go in database lock.

WAL mode is saved with the database.  Once you have enabled WAL mode, that 
setting is stored with the database for all time until you use another PRAGMA 
command to change it.  Anything else opening the database will notice that it's 
already in WAL mode.

> PRAGMA locking_mode=EXCLUSIVE

Setting the journal mode to WAL while the locking-mode is EXCLUSIVE locks the 
current locking mode.  You cannot change it again afterwards without switching 
back out of WAL mode.

As a consequence of the above neither commands 4.b nor 4.c have any effect.

I suspect that it's your use of EXCLUSIVE mode that's causing your problem.  So 
please remove this setting, and make sure it's removed by checking

PRAGMA locking_mode;

and then try your test again and see if you're still getting unwanted locking.

> The code used to open the the database is:
> 
> int res = sqlite3_open_v2( m_FilePath.getBuffer(), ,
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_WAL|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_PRIVATECACHE,
> NULL );

According to the documentation the SQLITE_OPEN_WAL constant is for the VFS 
only.  I don't know it'll do any harm but I don't know it does anything.

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

Re: [sqlite] Enable WAL on a QNX system (Richard Hipp)

[Sandu Buraga]

Hi Richard,

Yes, only one thread is working on the database first time. So even if I am
opening more handles, I work only with one of the handles to run some
PRAGMA statements and to check the structural integrity of database. If
tables, or columns are missing I will run some DROP TABLE IF EXISTS
statements, and CREATE TABLE within a transaction. The pseudo-code would be:

1. open 1 handle
2. on handle 1 - perform one SQL SELECT statement
3. on handle 1 - start a transaction and create some tables
4.a. On handle 1 - PRAGMA temp_store=MEMORY
4.b. On handle 1 - PRAGMA locking_mode=EXCLUSIVE
4.c. On handle 1 - PRAGMA main.journal_mode=WAL
5. open another 4 db connections

If step 4 would be executed before step 2, I have immediately after BEGIN
TRANSACTION a database lock. But, after I enable the WAL mode the
consequent transactions (in any other threads) will go in database lock. I
can include the SQLite traces.

The code used to open the the database is:

int res = sqlite3_open_v2( m_FilePath.getBuffer(), ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_WAL|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_PRIVATECACHE,
NULL );

and for the next four handles:

res = sqlite3_open_v2( m_FilePath.getBuffer(), ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_PRIVATECACHE, NULL
);


Regards,
Sandu Buraga

>> On Mon, Nov 11, 2013 at 12:33 PM, Sandu Buraga wrote:
>>
>>> Hi,
>>>
>>> I took your advice and now I am setting only once the journal_mode=WAL,
>>> also locking_mode=EXCLUSIVE and temp_store=MEMORY. But still it doesn't
>>> work, and I get DB locks immediately. I enabled SQLite traces, and I also
>>> added some supplementary traces when the WAL is set. Even if the
>>> journal_mode=WAL pragma seems to be successful, I don't think that the WAL
>>> works because I see no wal file on the disk, also I should have some WAL
>>> related traces, which are not present.
>>>
>>
>> There can only be a single connection open on the database file when you
>> change it to WAL mode.  Did you try to change to WAL mode while holding
>> multiple connections open?
>>
>>
>The above is not quite correct.

>There can be multiple connections open, but none of the other connections
>can have any kind of lock.  Are you sure that you don't have another thread
>trying to read the database file when you enter WAL mode?  The easiest way
>to ensure this is to put the database in WAL mode when the first thread
>connects, and before any other threads have even opened.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation addition request

[Richard Hipp]

On Wed, Nov 13, 2013 at 10:41 AM, Simon Slavin  wrote:

> Can the text on these pages
>
> 
> 
>
> have added a brief description of what differences are between all the
> alternatives
>
> OR ROLLBACK/ABORT/REPLACE/FAIL/IGNORE
>
> are, including which one is the default ?  Thanks.
>

http://www.sqlite.org/lang_conflict.html

ABORT is always the default.


-- 
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] GROUP BY on SUM CASE

[Simon Slavin]

On 13 Nov 2013, at 2:51pm, g.costa...@email.it wrote:

> I've this 

First, I can't read your post, it has too much HTML mixed up in it.  Second, 
anything this complicated should probably be done in software, not by trying to 
use features of SQL.

Simon.

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

[sqlite] Documentation addition request

[Simon Slavin]

Can the text on these pages




have added a brief description of what differences are between all the 
alternatives

OR ROLLBACK/ABORT/REPLACE/FAIL/IGNORE

are, including which one is the default ?  Thanks.

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

Re: [sqlite] SQL 2003 in sqlite

[Simon Slavin]

On 13 Nov 2013, at 2:15pm, Sylvain Pointeau  wrote:

> Hi,
> 
>> INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2
>> Do you have specific requirements for the UPDATE features of MERGE ?
> 
> the issue with "insert or replace" is that it will match on primary keys,
> additionally (I am not 100% sure) it deletes first the row to replace it.
> we cannot update then only 1 column.

It's possible you should be using UPDATE OR REPLACE then.

> furthermore, the merge can delete as well, it does the join on the fields
> we want (not only PK), it is really wonderful.

I still don't know what function that MERGE does that you can't do in SQLite.

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

[sqlite] GROUP BY on SUM CASE

[g . costanzi]

hi,

I've this 


SELECT 

orders.order_id AS order_id,

strftime('%d-%m-%Y', orders.issued) AS issued,

suppliers.company AS company,

departments.department_id AS dep_id,

order_details.quantity AS qty,


SUM(CASE WHEN transactions.category_id = 1 THEN 1 ELSE 0
END) AS dispatch,


order_details.quantity - SUM(CASE WHEN transactions.category_id
= 1 THEN 1 ELSE 0 END) AS surplus


FROM orders

INNER JOIN departments ON (departments.department_id =

orders.department_id) 


INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id) 

INNER JOIN order_details ON (orders.order_id = order_details.order_id)

INNER JOIN transactions ON order_details.order_detail_id =

transactions.order_detail_id


WHERE orders.state = 0 AND orders.enable =1

GROUP BY order_details.order_detail_id


that return such as


order_id issued
company dep_id qty, dispatch
surplus

"1" "12-11-2013"
"Siemens" "1"
"6"   
"4""2"

"1" "12-11-2013"
"Siemens" "1"
"2"   
"2" "0"

"2" "13-11-2013"
"Siemens" "2"
"10" "10"
"0"

"3" "13-11-2013"
"Siemens" "8"
"3"
"3" "0"


How I can group by order_id? In the example I'would return on order_id

=1:


order_id issued
company dep_id qty, dispatch
surplus

"1" "12-11-2013"
"Siemens" "1"   "8"
 "6"  
 "2"





suggest?





regards


beppe


 
 --
 Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP
autenticato? GRATIS solo con Email.it: http://www.email.it/f
 
 Sponsor:
 LOONEY TUNES: Acquista Peluche, Gadget e Abbigliamento Originale su
mistercupido.com
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12902=20131113


 
 
 --
 Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP 
autenticato? GRATIS solo con Email.it http://www.email.it/f
 
 Sponsor:
 BARBAPAPA': Acquista i Peluche Originali su mistercupido.com
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12898=13-11
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL 2003 in sqlite

[Hick Gunter]

Actually, replace will delete any and all currently present rows that cause a 
UNIQUE violation.

asql> create temp table x (prim integer UNIQUE, seco integer UNIQUE, val 
integer);
asql> insert into x values (1,1,1);
rows inserted
-
1
asql> insert into x values (2,2,2);
rows inserted
-
1
asql> insert into x values (1,2,3);
Error: column seco is not unique
asql> replace into x values (1,2,3);
rows inserted
-
1
asql> select * from x;
primsecoval
--  --  --
1   2   3

Gunter

-Ursprüngliche Nachricht-
Von: Sylvain Pointeau [mailto:sylvain.point...@gmail.com]
Gesendet: Mittwoch, 13. November 2013 15:16
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] SQL 2003 in sqlite

Hi,

> INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2 Do you
> have specific requirements for the UPDATE features of MERGE ?

the issue with "insert or replace" is that it will match on primary keys, 
additionally (I am not 100% sure) it deletes first the row to replace it.
we cannot update then only 1 column.

furthermore, the merge can delete as well, it does the join on the fields we 
want (not only PK), it is really wonderful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL 2003 in sqlite

[Sylvain Pointeau]

Hi,

> INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2
> Do you have specific requirements for the UPDATE features of MERGE ?

the issue with "insert or replace" is that it will match on primary keys,
additionally (I am not 100% sure) it deletes first the row to replace it.
we cannot update then only 1 column.

furthermore, the merge can delete as well, it does the join on the fields
we want (not only PK), it is really wonderful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can i change the primary key?

[David M. Cotter]

ah!  this was my answer! thanks!

On Nov 11, 2013, at 6:03 AM, Igor Tandetnik  wrote:

> On 11/10/2013 8:12 PM, David M. Cotter wrote:
>> what i did before SQL was to just tell the new song (which may have updated 
>> / corrected meta data) to have the old song ID (and tell the old one to have 
>> the new one), then i'd just delete all the "new" song ids (since they had 
>> been swapped, i'd be really deleting all the old songs).
>> 
>> so, can i do this with SQLite?
> 
> You can INSERT and UPDATE primary key fields the same way you can any other 
> field - subject to uniqueness constraint, of course (at no point in time 
> could there be two records with the same key, no matter how this key was 
> arrived at).

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

Re: [sqlite] How to fix sqlite issue

[Jan Nijtmans]

2013/11/13 Sqlite Dog :
> 1) download zip with latest SQLite DLL from http://sqlite.org/download.html;

I downloaded the latest iTunes, and had a look. The SQLite dll is here:
   C:\Program Files (x86)\Common Files\Apple\Apple Application
Support\SQLite3.dll
But there is also an iTunes directory:
   C:\Program Files (x86)\iTunes\
which contain dll's and exe's which depend on SQLite3.dll.
Coping the SQLite3.dll from the Apple directory to the iTunes directory should
fix your problem. There is a zlib1.dll there too which isn't the
official version
either. BAD! I would copy this one too, in order to prevent any problem with
iTunes. I think this should be reported to Apple!

2013/11/13 Richard Hipp :
> Note to the community at large:  THIS IS WHY YOU SHOULD STATICALLY LINK
> YOUR APPLICATIONS AGAINST SQLITE!
This is what I always recommend too. But it can be relaxed a little bit:
- If your system provides a sqlite dll/so file (e.g. Cygwin/ubuntu)
you can use it,
  but then you need to test your application on the lowest system version you
  intend to support. Then don't distribute your own DLL!
- If you choose to compile your own DLL and distribute it, NEVER give it the
  same name as a system/official DLL if the DLL is not the same.

However the rule is, it's clear that iTunes violates it.

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

Re: [sqlite] What is the best index to create?

[Igor Korot]

Anyone?

I need to understand which index to create and how to fix the query.

Thank you.

On Tue, Nov 12, 2013 at 6:17 PM, Igor Korot  wrote:
> Hi, Simon,
>
> On Tue, Nov 12, 2013 at 2:18 PM, Simon Slavin  wrote:
>>
>> On 12 Nov 2013, at 9:41pm, Igor Korot  wrote:
>>
>>> I don't see what is wrong. It should be using foreign key constraints
>>> on the last two searches, but it looks like it does not.
>>
>> Did you actually create the indexes which would be useful for those 
>> constraints, or did you just specify the constraints ?
>>
>> 
>
> I just specify the constraints.
> But I may have a bigger problem. Running this query gives an endless
> loop. So there is some kind of logic error in this query.
>
> Basically this is what I am looking for:
>
> There is a league with assigned players. And there are 2 kind of
> players: hitters and pitchers (baseball game).
> Those kind is identified by the ishitter field in the playersinleague table.
>
> Each kind have different score types and each player assigned their
> own score according to the score types assigned to the kind.
>
> What I want is to retrieve the score for each player no matter whether
> it's a hitter or a pitcher.
>
> Can I do it in one query or it has to be done in two?
>
> Thank you.
>
> P.S.: If you need to see a DB schema - just ask and I will provide it.
>
>>
>> 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