[sqlite] GROUP BY

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,

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

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

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

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

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

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

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

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

>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

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

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

[sqlite] Foreign key vs index (continue)

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 =

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

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

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

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

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

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

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

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

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

[sqlite] syncing two sqlite databases and db corruption

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

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

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

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

Re: [sqlite] What takes the most time

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

Re: [sqlite] What takes the most time

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

Re: [sqlite] What takes the most time

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

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:

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. Igor Tandetnik On 11/13/2013 4:42 PM, L.

[sqlite] What takes the most time

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.

Re: [sqlite] SQL 2003 in sqlite

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

Re: [sqlite] SQL 2003 in sqlite

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

Re: [sqlite] SQL 2003 in sqlite

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

Re: [sqlite] GROUP BY on SUM CASE

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 ___

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

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

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

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

Re: [sqlite] Documentation addition request

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

Re: [sqlite] GROUP BY on SUM CASE

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] Documentation addition request

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.

Re: [sqlite] SQL 2003 in sqlite

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

[sqlite] GROUP BY on SUM CASE

quot;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 Sponso

Re: [sqlite] SQL 2003 in sqlite

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

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

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

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

Re: [sqlite] How to fix sqlite issue

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

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

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