Re: [sqlite] Simple SQL question?
Bart Smissaert wrote: > Have (simplified) a table like this: > > CREATE TABLE TABLE1( > [PATIENT_ID] INTEGER PRIMARY KEY, > [ADDRESS] TEXT, > [DATE_OF_BIRTH] TEXT) > > DATE_OF_BIRTH is in the ISO8601 format -mm-dd > > Now I need a SQL to find the oldest patients living at all the > different (unique) addresses, so this will be > the patient with the lowest DATE_OF_BIRTH. I will need the PATIENT_ID > of that patient and nil else. select (select PATIENT_ID from TABLE1 t1 where t1.ADDRESS = t2.ADDRESS order by DATE_OF_BIRTH limit 1) from (select distinct ADDRESS from TABLE1) t2; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote: > This seems to work fine, Then you are golden :) > but I am not sure if this SQL is correct and > if the results will always be correct and have a feeling > that there must be a better construction. > Any suggestions? Nothing very meaningful, but you could rewrite the 'in' clause as a 'join' to avoid all these concatenations, e.g.: select t1.patient_id fromtable1 t1 join( select table1.address, min( table1.date_of_birth ) as date_of_birth fromtable1 group bytable1.address ) as t2 joint2.address = t1.address and t2.date_of_birth = t1.date_of_birth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple SQL question?
Have (simplified) a table like this: CREATE TABLE TABLE1( [PATIENT_ID] INTEGER PRIMARY KEY, [ADDRESS] TEXT, [DATE_OF_BIRTH] TEXT) DATE_OF_BIRTH is in the ISO8601 format -mm-dd Now I need a SQL to find the oldest patients living at all the different (unique) addresses, so this will be the patient with the lowest DATE_OF_BIRTH. I will need the PATIENT_ID of that patient and nil else. Seems simple, but I had trouble getting this and eventually came up with: SELECT T1.PATIENT_ID FROM TABLE1 T1 WHERE T1.DATE_OF_BIRTH || T1.ADDRESS IN (SELECT MIN(T2.DATE_OF_BIRTH) || T2.ADDRESS FROM TABLE1 T2 GROUP BY T2.ADDRESS) This seems to work fine, but I am not sure if this SQL is correct and if the results will always be correct and have a feeling that there must be a better construction. Any suggestions? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_sequence table
See my previous e-mail: I was querying temp.sqlite_sequence when I thought I was querying main.sqlite_sequence. Will On 11/16/10 2:22 PM, "Richard Hipp" wrote: On Tue, Nov 16, 2010 at 4:31 PM, Duquette, William H (316H) < william.h.duque...@jpl.nasa.gov> wrote: > Yes. I've done a couple of inserts into the table, interspersed with > queries to the > sqlite_sequence table; they show the sqlite_sequence table as being empty. > Test case: CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); SELECT 111, * FROM sqlite_sequence; INSERT INTO t1 VALUES(10,11); SELECT 222, * FROM sqlite_sequence; The result comes back: 222|t1|10 What are your inputs and outputs? > > Will > > > On 11/16/10 1:29 PM, "Gerry Snyder" wrote: > > On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote: > > Howdy! > > > > According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY > AUTOINCREMENT" gets an entry in the sqlite_sequence table. I've got some > code that contains such a table; but if I query the sqlite_sequence table I > don't see it being updated; it's always empty. Anyone have any idea what's > going on? > > > > Have you put anything in the table that is > > INTEGER PRIMARY KEY AUTOINCREMENT > > so there is a sequence number to store in sqlite_sequence? > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- > Will Duquette -- william.h.duque...@jpl.nasa.gov > Athena Development Lead -- Jet Propulsion Laboratory > "It's amazing what you can do with the right tools." > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Resursive trigger not doing full recursion...
Hi Igor, Thank you for the qick response. Of course, this PRAGMA solved the entire issue, once I got a never version of SQLite installed. Thank you! /Fredrik On Tue, Nov 16, 2010 at 10:58 PM, Igor Tandetnik wrote: > Fredrik Karlsson wrote: >> I'm probably doing something really stupid here, but I feel I need to >> ask you anyway to see id there is something that I've missed. >> Is it not possible for a trigger to trigger itself? > > Recursive triggers have to be explicitly turned on: > > http://sqlite.org/pragma.html#pragma_recursive_triggers > >> Second question is, of course, can I depend on this behaviour? Or, is >> it just because of the version of SQLite I am currently using >> (3.6.16)? > > Recursive triggers are supported beginning with 3.6.18. > -- > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- "Life is like a trumpet - if you don't put anything into it, you don't get anything out of it." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_sequence table
On Tue, Nov 16, 2010 at 4:31 PM, Duquette, William H (316H) < william.h.duque...@jpl.nasa.gov> wrote: > Yes. I've done a couple of inserts into the table, interspersed with > queries to the > sqlite_sequence table; they show the sqlite_sequence table as being empty. > Test case: CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); SELECT 111, * FROM sqlite_sequence; INSERT INTO t1 VALUES(10,11); SELECT 222, * FROM sqlite_sequence; The result comes back: 222|t1|10 What are your inputs and outputs? > > Will > > > On 11/16/10 1:29 PM, "Gerry Snyder" wrote: > > On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote: > > Howdy! > > > > According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY > AUTOINCREMENT" gets an entry in the sqlite_sequence table. I've got some > code that contains such a table; but if I query the sqlite_sequence table I > don't see it being updated; it's always empty. Anyone have any idea what's > going on? > > > > Have you put anything in the table that is > > INTEGER PRIMARY KEY AUTOINCREMENT > > so there is a sequence number to store in sqlite_sequence? > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- > Will Duquette -- william.h.duque...@jpl.nasa.gov > Athena Development Lead -- Jet Propulsion Laboratory > "It's amazing what you can do with the right tools." > > ___ > 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
Re: [sqlite] Trouble with TRIGGERS
On 16 Nov 2010, at 10:18pm, Bernard Ertl wrote: > Kees Nuyt wrote: >> > >>> Nicolas Williams-2 wrote: Do you have recursive triggers enabled? >>> >>> I'm not sure. How do I check? >> >> http://www.sqlite.org/pragma.html#pragma_recursive_triggers > > I'm not using any pragma commands, so no, I'm not using recursive triggers. In that case, although you can define a trigger that would trigger itself, SQLite will silently drop all such situations: the trigger will be fired once, but attempts to fire itself will be ignored. You can use that PRAGMA to change this behaviour. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with TRIGGERS
Kees Nuyt wrote: > >> Nicolas Williams-2 wrote: >> > Do you have recursive triggers enabled? >> >> I'm not sure. How do I check? > > http://www.sqlite.org/pragma.html#pragma_recursive_triggers > I'm not using any pragma commands, so no, I'm not using recursive triggers. -- View this message in context: http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30233595.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE 2.3 and SQLITE3 3.7.x SHELL interoperability.
On 16 Nov 2010, at 9:41pm, Piszcz, Al wrote: > If a database file is created with 2.3.2 is it 'safe' to use with SQLITE3 > 3.7.x shell? > When VACUUM is performed on 2.3.2 database file is with SQLITE 3.7.x are > there any side effects? THe database formats of SQLite version 2 and version 3 are not compatible. You should not try to do anything with a 3.x library on a 2.x database. Sorry. The format and libraries for version 3 were launched in 2004 so anything done with version 2 is now at least 5 years old. While I have never seen anyone say that all support for version 2 will be discontinued, your format hasn't been worked on for 5 years and you might consider moving up to version 3 sometime. The conversion is not too difficult: use a version 2 shell tool to dump the file as SQL commands and use a version 3 shell tool to start an empty database and execute those commands to fill it with tables of data. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with TRIGGERS
On Tue, 16 Nov 2010 14:07:34 -0800 (PST), Bernard Ertl wrote: > > > Nicolas Williams-2 wrote: > > Do you have recursive triggers enabled? > > I'm not sure. How do I check? http://www.sqlite.org/pragma.html#pragma_recursive_triggers -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with TRIGGERS
Nicolas Williams-2 wrote: > Do you have recursive triggers enabled? I'm not sure. How do I check? -- View this message in context: http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30233496.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with TRIGGERS
Do you have recursive triggers enabled? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Resursive trigger not doing full recursion...
Fredrik Karlsson wrote: > I'm probably doing something really stupid here, but I feel I need to > ask you anyway to see id there is something that I've missed. > Is it not possible for a trigger to trigger itself? Recursive triggers have to be explicitly turned on: http://sqlite.org/pragma.html#pragma_recursive_triggers > Second question is, of course, can I depend on this behaviour? Or, is > it just because of the version of SQLite I am currently using > (3.6.16)? Recursive triggers are supported beginning with 3.6.18. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Resursive trigger not doing full recursion...
Dear list, I'm probably doing something really stupid here, but I feel I need to ask you anyway to see id there is something that I've missed. Is it not possible for a trigger to trigger itself? I get triggers that trigger triggers, but so far not triggers that trigger themselves... (yes, many triggers.. :-) ) This is what I am doing: I need to compute the transitive closure of a graph: Tables I am using are: --- This table stores the connections--- CREATE TABLE level_level ( parent_id INTEGER REFERENCES levels(id), child_id INTEGER REFERENCES levels(id), PRIMARY KEY(parent_id,child_id) ); --- This table stores the TC CREATE TABLE tc_level_level ( ancestor_id INTEGER REFERENCES levels(id), descendant_id INTEGER REFERENCES levels(id), depth INTEGER, path TEXT, PRIMARY KEY(ancestor_id,descendant_id,depth) ON CONFLICT IGNORE ); Now, I have two triggers: --- This trigger inserts the simple connections into the TC table CREATE TRIGGER ll_tcll_insert after insert on level_level for each row begin delete from tc_level_level where depth > 1; insert into tc_level_level select NEW.parent_id ancestor_id,NEW.child_id descendant_id,1 depth,NEW.parent_id || ',' ||NEW.child_id path from level_level; end; --- This table stores the TC CREATE TRIGGER tcll_tcll_insert after insert on tc_level_level for each row begin insert into tc_level_level select tc1.ancestor_id, tc2.descendant_id, tc1.depth + tc2.depth,tc1.path || substr(tc2.path, length(tc2.ancestor_id)+1) from tc_level_level tc1, tc_level_level tc2 where tc1.descendant_id = tc2.ancestor_id; end; A sample run looks like this: > delete from level_level; > delete from tc_level_level; > INSERT INTO "level_level" VALUES(1,2); > INSERT INTO "level_level" VALUES(1,3); > INSERT INTO "level_level" VALUES(3,4); > INSERT INTO "level_level" VALUES(4,5); > INSERT INTO "level_level" VALUES(2,5); > select * from level_level; parent_id child_id -- -- 1 2 1 3 3 4 4 5 2 5 > select * from tc_level_level; ancestor_id descendant_id depth path --- - -- -- 12 1 1,2 13 1 1,3 34 1 3,4 45 1 4,5 25 1 2,5 15 2 1,2,5 14 2 1,3,4 35 2 3,4,5 So, why is there not a 15 3 1,3,4,5 row? Second question is, of course, can I depend on this behaviour? Or, is it just because of the version of SQLite I am currently using (3.6.16)? Thanks! /Fredrik -- "Life is like a trumpet - if you don't put anything into it, you don't get anything out of it." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE 2.3 and SQLITE3 3.7.x SHELL interoperability.
If a database file is created with 2.3.2 is it 'safe' to use with SQLITE3 3.7.x shell? When VACUUM is performed on 2.3.2 database file is with SQLITE 3.7.x are there any side effects? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_sequence table
Aha! I've got AUTOINCREMENT keys in both regular and temporary tables. This results in two sqlite_sequence tables, one in sqlite_master and one in sqlite_temp_master. And evidently, if I have a permanent and a temporary table with the same name, a query that doesn't specify gets the temporary table. If I do "SELECT * FROM main.sqlite_sequence" I see what I'm looking for. Will On 11/16/10 1:31 PM, "Will Duquette" wrote: Yes. I've done a couple of inserts into the table, interspersed with queries to the sqlite_sequence table; they show the sqlite_sequence table as being empty. Will On 11/16/10 1:29 PM, "Gerry Snyder" wrote: On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote: > Howdy! > > According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY > AUTOINCREMENT" gets an entry in the sqlite_sequence table. I've got some > code that contains such a table; but if I query the sqlite_sequence table I > don't see it being updated; it's always empty. Anyone have any idea what's > going on? > Have you put anything in the table that is INTEGER PRIMARY KEY AUTOINCREMENT so there is a sequence number to store in sqlite_sequence? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_sequence table
Yes. I've done a couple of inserts into the table, interspersed with queries to the sqlite_sequence table; they show the sqlite_sequence table as being empty. Will On 11/16/10 1:29 PM, "Gerry Snyder" wrote: On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote: > Howdy! > > According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY > AUTOINCREMENT" gets an entry in the sqlite_sequence table. I've got some > code that contains such a table; but if I query the sqlite_sequence table I > don't see it being updated; it's always empty. Anyone have any idea what's > going on? > Have you put anything in the table that is INTEGER PRIMARY KEY AUTOINCREMENT so there is a sequence number to store in sqlite_sequence? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_sequence table
On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote: > Howdy! > > According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY > AUTOINCREMENT" gets an entry in the sqlite_sequence table. I've got some > code that contains such a table; but if I query the sqlite_sequence table I > don't see it being updated; it's always empty. Anyone have any idea what's > going on? > Have you put anything in the table that is INTEGER PRIMARY KEY AUTOINCREMENT so there is a sequence number to store in sqlite_sequence? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite_sequence table
Howdy! According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY AUTOINCREMENT" gets an entry in the sqlite_sequence table. I've got some code that contains such a table; but if I query the sqlite_sequence table I don't see it being updated; it's always empty. Anyone have any idea what's going on? (I'm using SQLite3 3.6.23.) Will -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with TRIGGERS
Dan Kennedy-4 wrote: > > Sounds like it. > > Calling sqlite3_prepare_v2() generates the VM code for all > triggers that could possibly be invoked by your statement. > All it considers when determining which triggers might be > needed is the type of statement (UPDATE, DELETE, INSERT) and > for UPDATES, the columns updated. > Thanks Dan. However, I think it's more correct to say that it generates the VM code for all triggers that could possibly be invoked by the statement *and any related triggers*. It appears to expand the pool of possible triggers on the fly based upon the content of each trigger that it's queuing up. For example, this trigger: CREATE TRIGGER fki_Tasks_PerComp_Range AFTER INSERT ON Tasks FOR EACH ROW WHEN NEW.PerComp IS NULL BEGIN UPDATE Tasks SET PerComp = 0 WHERE RowID = NEW.RowID; END; Is causing all my triggers related to an update on the Tasks table to be queued up when I execute an INSERT on Tasks *with* PerComp = 0 (not null). I guess I was expecting a short circuit evaluation on the FOR EACH ROW WHEN conditions. -- View this message in context: http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30232856.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and multiple writers?
Hi there, Well I'm sure I was, all I did was add a "busy handler" and it seems to work 100% now ;-) Lynton -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau Sent: 16 November 2010 06:09 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] WAL and multiple writers? Are you sure that you are not keeping a transaction in the message sender? ___ 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] curent_timestamp value
Hi, Could you please tip me why the CURRENT_TIMESTAMP value would always get set to "1999-21-31 12:00:00"? Any SQLITE_OMIT flags may contribute to this? Thanks in advance, Mark Ps. building the sqlite: v.3.6.23.1 for linux (kernel 2.6.31.8) on arm. My omits: -DSQLITE_OMIT_ALTERTABLE \ -DSQLITE_OMIT_ANALYZE \ -DSQLITE_OMIT_AUTHORIZATION \ -DSQLITE_OMIT_BETWEEN_OPTIMIZATION \ -DSQLITE_OMIT_BUILTIN_TEST \ -DSQLITE_OMIT_COMPILEOPTION_DIAGS \ -DSQLITE_OMIT_DECLTYPE \ -DSQLITE_OMIT_DEPRECATED \ -DSQLITE_OMIT_EXPLAIN \ -DSQLITE_OMIT_FLAG_PRAGMAS \ -DSQLITE_OMIT_INCRBLOB \ -DSQLITE_OMIT_INTEGRITY_CHECK \ -DSQLITE_OMIT_LOAD_EXTENSION \ -DSQLITE_OMIT_PROGRESS_CALLBACK \ -DSQLITE_OMIT_QUICKBALANCE \ -DSQLITE_OMIT_REINDEX \ -DSQLITE_OMIT_SCHEMA_PRAGMAS \ -DSQLITE_OMIT_TRACE \ -DSQLITE_OMIT_TRUNCATE_OPTIMIZATION \ -DSQLITE_OMIT_UTF16 \ -DSQLITE_OMIT_XFER_OPT \ -DSQLITE_OMIT_TCL_VARIABLE \ -DSQLITE_OMIT_FLOATING_POINT ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
On Tue, Nov 16, 2010 at 11:38 AM, Ruslan Mullakhmetov < r.mullakhme...@tools4brokers.com> wrote: > > Understand this: Any programming language that is built around threads > is > > inherently broken and should be avoided. (I will not name names - you > know > > the languages I'm talking about.) And any operating system that depends > > upon threads for performance is equality busted and should also be > shunned. > > so you advise to change language? to some functional or logic? i got no > more > idea how to avoid threads and evils connected with them. > > unfortunately i'm not free in language choosing cause i develop extensions > which has api in language that "is inherently broken and should be > avoided". > I recognize this sad reality, which is why SQLite is "threadsafe" even though we actively discourage the use of threads. Sometimes you just have no choice. Unchangeable external constraints impose a programming language or platform on you. And for those occasions, you just have to VERY VERY CAREFUL. > > > ___ > > 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 > -- 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] Attach database problem
It solves the problem. I put all in one connection, made attach and
everything is fine.
Thanks Igor, you save me a hours!
2010/11/16 Igor Tandetnik :
> Mihailo wrote:
>> I have one database1 with dataTable1 and other database2 with dataTable2.
>> conn1 and conn2.
>>
>> conn1->exec("UPDATE dataTable1 set spt_activtrigger = 3 where sim_id
>> in ( select sim_id from dataTable2 where ));
>> conn2->exec("UPDATE dataTable2 set spt_activtrigger = 3 where sim_id
>> in ( select sim_id from dataTable1 where ));
>
> It seems you have two connections, both of which refer to both databases. Why
> do you think you need sepearate connections in the first place? Why can't you
> just have one connection execute both these statements?
> --
> 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] threads and last_insert_rowid()
> Understand this: Any programming language that is built around threads is > inherently broken and should be avoided. (I will not name names - you know > the languages I'm talking about.) And any operating system that depends > upon threads for performance is equality busted and should also be shunned. so you advise to change language? to some functional or logic? i got no more idea how to avoid threads and evils connected with them. unfortunately i'm not free in language choosing cause i develop extensions which has api in language that "is inherently broken and should be avoided". > ___ > 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] Attach database problem
Mihailo wrote:
> I have one database1 with dataTable1 and other database2 with dataTable2.
> conn1 and conn2.
>
> conn1->exec("UPDATE dataTable1 set spt_activtrigger = 3 where sim_id
> in ( select sim_id from dataTable2 where ));
> conn2->exec("UPDATE dataTable2 set spt_activtrigger = 3 where sim_id
> in ( select sim_id from dataTable1 where ));
It seems you have two connections, both of which refer to both databases. Why
do you think you need sepearate connections in the first place? Why can't you
just have one connection execute both these statements?
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
On Tue, Nov 16, 2010 at 10:29 AM, Ruslan Mullakhmetov < r.mullakhme...@tools4brokers.com> wrote: > > as you see in _serialized_ mode "SQLite can be safely used by multiple > threads with no restriction". > > do i misunderstand something? > With serialized threading, SQLite is perfectly "safe" to use. But if you change a value (the last-insert-rowid in this case) then that change will appear in other threads. There is nothing "unsafe" about that - you just need to be aware that it happens. "Safe" does not mean "atomic". If nothing else, this entire discussion should make it abundantly clear that threads are a very dangerous construct and should be actively avoided in all software that strives to be robust and/or reliable. People rant and rail about the evils of "goto" or pointers, and to be fair, those constructs are easily misused. But threads are a far more insidious evil in that they are non-deterministic: the failures happen differently on each run, or perhaps not at all until the product goes into production. Understand this: Any programming language that is built around threads is inherently broken and should be avoided. (I will not name names - you know the languages I'm talking about.) And any operating system that depends upon threads for performance is equality busted and should also be shunned. "Threadsafe" is an oxymoron. Nothing is safe when threads are in play. There are merely differing levels of hazard. So the best solution to your problem, Ruslan, is to not use threads. Failing that, allocate a separate database connection for each thread. If you can't do that, then you are going to have concurrency issues to deal with, regardless of what libraries you use. -- 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] WAL and multiple writers?
Are you sure that you are not keeping a transaction in the message sender? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Attach database problem
I have one database1 with dataTable1 and other database2 with dataTable2.
conn1 and conn2.
conn2->query("attach '".$_SESSION['QPO_dataBase1Path']."' as dst1");
//$_SESSION['QPO_dataBase1Path'] is path to database1, adding
database1 to database2
conn1.beginTransaction();
conn2.beginTransaction();
//problem area
conn1->query("SELECT * ");
conn2->exec("UPDATE dataTable2 set spt_activtrigger = 3 where sim_id
in ( select sim_id from dataTable1 where ));
.
.
.
.
conn1->exec("UPDATE dataTable1 set spt_activtrigger = 3 where sim_id
in ( select sim_id from dataTable2 where ));
conn2->exec("UPDATE dataTable2 set spt_activtrigger = 3 where sim_id
in ( select sim_id from dataTable1 where ));
.
.
// I have a lot of commands
conn1->commit();//problem database locked
conn2->commit();//problem database locked
How I can avoid this problem, and when database is locked
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
thanks everybody, especially Pavel Ivanov who did not give himself a trouble to look at the source though i did. but as far as i understand this is only applicable to current version of sqlite and future releases may change things. Jay A. Kreibich wrote: > If each thread is using its own, private database connection, then > you're safe. If multiple threads are sharing a database connection > and using the "serialized" thread mode, then you have a race > condition as it is possible for the database connection to be > handed off between statements. The value returned by > last_insert_rowid() may be invalid by the time it is called. Yes, i share one database handle between multiple threads. actually i have some class Storage which is accessible from multiple threads. I defined SQLITE_TREADSAFE and thought that there could be no racing. as for http://cvs.hwaci.com/threadsafe.html > SQLite support three different threading modes: > > 1. Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once. > 2. Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. > 3. Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction. as you see in _serialized_ mode "SQLite can be safely used by multiple threads with no restriction". do i misunderstand something? BR, RM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
> whom to trust? Trust Kees. Borgan's thought about keeping the lock and transaction management inside sqlite3_exec is incorrect. > as for Kees Nuyt reply, did you toke int account that "select > last_insert_rowid() " > and insert query combined in single query and executed via single call > of sqlite3_exec()? Actually it depends on particular implementation of sqlite3_exec. Kees' reply is general and it would stand if sqlite3_exec didn't acquire connection's mutex and let individual statements acquire mutex as necessary. But as I see in SQLite sources sqlite3_exec does acquire connection's mutex, so nothing can be executed in between statements. Thus if you execute this line as one call to sqlite3_exec then it won't suffer from threading. Pavel On Tue, Nov 16, 2010 at 8:08 AM, Ruslan Mullakhmetov wrote: > i got following contradictory replies to my question > >> if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select >> last_insert_rowid() as li;" would be it atomic? or it anyway would suffer >> from threads? > > from borgan: >> Hi, i think this will probably be "atomic". >> What i mean is the transaction will aquire EXCLUSIVE lock (because of >> the insert command) and it will keep this lock until commit, which >> takes place after the last select and last semicolon. >> >> So i believe no other threads should be able to do anything with your >> db until the whole query finishes. > and from Kees Nuyt: >> The last_insert_rowid() function returns the ROWID of the last row >> insert from the database connection which invoked the function. >> So, yes, it suffers from threads if the threads use the same >> database connection. > > whom to trust? > > as for Kees Nuyt reply, did you toke int account that "select > last_insert_rowid() " > and insert query combined in single query and executed via single call > of sqlite3_exec()? > > BR, RM > ___ > 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] Strange Corruption
.dump (3.6.23.1) and afterwards "sqlite3 /tmp/new.db < /tmp/dump.sql" with 3.7.2 worked and fixed the .backup problem (as expected). As I've already downgraded sqlite3 in our new firmware and patched the live-systems that were running with the new firmware I'll only have one machine to check whether the error comes back or not... So maybe I'll not be able to give feedback for a few weeks (as I can not enforce errors). However: if the error will not come back and could have to do with an already existing error in the database it would be quite interesting to know why integrity_check doesn't find the error before making a backup. (What means that a bug exists in any case: either in PRAGMA integrity_check or in the backup function). --- Pirmin Walthert Am 16.11.2010 14:22, schrieb Black, Michael (IS): > Sorry, I meant .dump > > Given what you're describing I think it's worth finding out if you've found > some bug in 3.7.2. > The docs say 3.7.2 fixed a long-standing corruption bug. I don't know if > that's related to this or not but sounds suspiciously close. > > So... > > #1 .dump the database > #2 .import in into 3.7.2 > #3 Run for a few days and see if you still get your backup problem. > > If still corrupt try 3.7.3 > > If it works then it sounds like the database was corrupt already and 3.7.2 > just hits it. > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From:sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert > Sent: Tue 11/16/2010 7:09 AM > To:sqlite-users@sqlite.org > Subject: Re: [sqlite] EXTERNAL:Re: Strange Corruption > > > > Well indeed it wasn't 3.7.X that created the database originally. But it > was always 3.7.2 that made the INSERTS/UPDATES that lead to the state in > which the database couldn't be backed up anymore. So what do you mean in > fact: 3.7.X maybe can't handle database structures created with older > versions?! > > Even after doing a vacuum which fixed the bug I had the same errors > again on the machines with 3.7.2 after a few days (after other > INSERTS/UPDATES). > > About the thing I should test: > > There is no command called ".export" it seems?! > > But I think that I don't even have to test the thing you propose, as it > will work almost for sure => like already stated several times one > little tiny tiny tiny change already fixes the error. As an > .export/.import will change some bits for sure this will already change > the situation! > > Am 16.11.2010 13:53, schrieb Black, Michael (IS): >> I thought of another test you should try. >> >> Do an .export of your original database using 3.6.23.1 and .import it >> (constructing a new database). Then try your backup. >> >> If that works then you're just seeing corruption in the original database >> that 3.6.23.1 handles (since it created it). >> >> If it doesn't work import into 3.7.3 and test backup again. >> >> If it doesn't work then try cutting the SQL in half until it does work. >> Maybe you'll finally get a small enough size you can post. >> >> >> >> Michael D. Black >> Senior Scientist >> Advanced Analytics Directorate >> Northrop Grumman Information Systems >> >> >> >> >> From:sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert >> Sent: Tue 11/16/2010 6:27 AM >> To:sqlite-users@sqlite.org >> Subject: EXTERNAL:Re: [sqlite] Strange Corruption >> >> >> >> No, this is definitely not the reason in my case as I can reproduce this >> issue on every 3.7.2/3.7.3 machine I've tested after copying the >> database file (and only the database file) to these machines. >> >> >> >> >> >> ___ >> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help - two similar queries
This also works...a little mod to Igor's... You need to ensure that the players are always listed in the same 1,2 order though. Otherwise the group by won't work. .mode column .width 8 create table Games(id,player1,player2,score); insert into Games values (1,1,2,1); insert into Games values (2,1,2,-1); insert into Games values (3,1,3,1); insert into Games values (4,1,3,1); insert into Games values (5,2,3,-1); insert into Games values (6,2,3,-1); select player1,player2,count(*) TotalGames, sum(score > 0) GamesWonByPlayer1, sum(score < 0) GamesWonByPlayer2, sum(score = 0) Draws from Games group by player1,player2; player1 player2 TotalGames GamesWonByPlayer1 GamesWonByPlayer2 Draws -- -- - - -- 1 2 2 1 1 0 1 3 2 2 0 0 2 3 2 0 2 0 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Ian Hardingham Sent: Tue 11/16/2010 7:31 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Query help - two similar queries Many thanks again Igor. On 16/11/2010 13:15, Igor Tandetnik wrote: > Ian Hardingham wrote: >> Thanks Igor. Can i get custom results like >> >> GamesWonByPlayer1 >> >> By using getColumn in the normal way? > I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a > column alias which a) is completely optional, you could safely drop it from > the query (I've just included it for clarity), and b) does not in any way > interfere with sqlite3_column_* API (on which, I guess, getColumn is based in > whatever language binding you are using). > >> That may be a stupid question - I guess what I mean is, are those custom >> identifiers treated as column names when reading back from the select? > Basically, yes. With SQLite, you could address a column by name or by > position (numbered left to right starting from 0). Column alias in the query > makes it more convenient to do the former. ___ 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] Query help - two similar queries
Many thanks again Igor. On 16/11/2010 13:15, Igor Tandetnik wrote: > Ian Hardingham wrote: >> Thanks Igor. Can i get custom results like >> >> GamesWonByPlayer1 >> >> By using getColumn in the normal way? > I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a > column alias which a) is completely optional, you could safely drop it from > the query (I've just included it for clarity), and b) does not in any way > interfere with sqlite3_column_* API (on which, I guess, getColumn is based in > whatever language binding you are using). > >> That may be a stupid question - I guess what I mean is, are those custom >> identifiers treated as column names when reading back from the select? > Basically, yes. With SQLite, you could address a column by name or by > position (numbered left to right starting from 0). Column alias in the query > makes it more convenient to do the former. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange Corruption
Sorry, I meant .dump Given what you're describing I think it's worth finding out if you've found some bug in 3.7.2. The docs say 3.7.2 fixed a long-standing corruption bug. I don't know if that's related to this or not but sounds suspiciously close. So... #1 .dump the database #2 .import in into 3.7.2 #3 Run for a few days and see if you still get your backup problem. If still corrupt try 3.7.3 If it works then it sounds like the database was corrupt already and 3.7.2 just hits it. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010 7:09 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] EXTERNAL:Re: Strange Corruption Well indeed it wasn't 3.7.X that created the database originally. But it was always 3.7.2 that made the INSERTS/UPDATES that lead to the state in which the database couldn't be backed up anymore. So what do you mean in fact: 3.7.X maybe can't handle database structures created with older versions?! Even after doing a vacuum which fixed the bug I had the same errors again on the machines with 3.7.2 after a few days (after other INSERTS/UPDATES). About the thing I should test: There is no command called ".export" it seems?! But I think that I don't even have to test the thing you propose, as it will work almost for sure => like already stated several times one little tiny tiny tiny change already fixes the error. As an .export/.import will change some bits for sure this will already change the situation! Am 16.11.2010 13:53, schrieb Black, Michael (IS): > I thought of another test you should try. > > Do an .export of your original database using 3.6.23.1 and .import it > (constructing a new database). Then try your backup. > > If that works then you're just seeing corruption in the original database > that 3.6.23.1 handles (since it created it). > > If it doesn't work import into 3.7.3 and test backup again. > > If it doesn't work then try cutting the SQL in half until it does work. > Maybe you'll finally get a small enough size you can post. > > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert > Sent: Tue 11/16/2010 6:27 AM > To: sqlite-users@sqlite.org > Subject: EXTERNAL:Re: [sqlite] Strange Corruption > > > > No, this is definitely not the reason in my case as I can reproduce this > issue on every 3.7.2/3.7.3 machine I've tested after copying the > database file (and only the database file) to these machines. > > > > > > ___ > 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] Query help - two similar queries
Ian Hardingham wrote: > Thanks Igor. Can i get custom results like > > GamesWonByPlayer1 > > By using getColumn in the normal way? I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a column alias which a) is completely optional, you could safely drop it from the query (I've just included it for clarity), and b) does not in any way interfere with sqlite3_column_* API (on which, I guess, getColumn is based in whatever language binding you are using). > That may be a stupid question - I guess what I mean is, are those custom > identifiers treated as column names when reading back from the select? Basically, yes. With SQLite, you could address a column by name or by position (numbered left to right starting from 0). Column alias in the query makes it more convenient to do the former. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL:Re: Strange Corruption
Well indeed it wasn't 3.7.X that created the database originally. But it was always 3.7.2 that made the INSERTS/UPDATES that lead to the state in which the database couldn't be backed up anymore. So what do you mean in fact: 3.7.X maybe can't handle database structures created with older versions?! Even after doing a vacuum which fixed the bug I had the same errors again on the machines with 3.7.2 after a few days (after other INSERTS/UPDATES). About the thing I should test: There is no command called ".export" it seems?! But I think that I don't even have to test the thing you propose, as it will work almost for sure => like already stated several times one little tiny tiny tiny change already fixes the error. As an .export/.import will change some bits for sure this will already change the situation! Am 16.11.2010 13:53, schrieb Black, Michael (IS): > I thought of another test you should try. > > Do an .export of your original database using 3.6.23.1 and .import it > (constructing a new database). Then try your backup. > > If that works then you're just seeing corruption in the original database > that 3.6.23.1 handles (since it created it). > > If it doesn't work import into 3.7.3 and test backup again. > > If it doesn't work then try cutting the SQL in half until it does work. > Maybe you'll finally get a small enough size you can post. > > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert > Sent: Tue 11/16/2010 6:27 AM > To: sqlite-users@sqlite.org > Subject: EXTERNAL:Re: [sqlite] Strange Corruption > > > > No, this is definitely not the reason in my case as I can reproduce this > issue on every 3.7.2/3.7.3 machine I've tested after copying the > database file (and only the database file) to these machines. > > > > > > ___ > 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] threads and last_insert_rowid()
i got following contradictory replies to my question > if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select > last_insert_rowid() as li;" would be it atomic? or it anyway would suffer > from threads? from borgan: > Hi, i think this will probably be "atomic". > What i mean is the transaction will aquire EXCLUSIVE lock (because of > the insert command) and it will keep this lock until commit, which > takes place after the last select and last semicolon. > > So i believe no other threads should be able to do anything with your > db until the whole query finishes. and from Kees Nuyt: > The last_insert_rowid() function returns the ROWID of the last row > insert from the database connection which invoked the function. > So, yes, it suffers from threads if the threads use the same > database connection. whom to trust? as for Kees Nuyt reply, did you toke int account that "select last_insert_rowid() " and insert query combined in single query and executed via single call of sqlite3_exec()? BR, RM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help - two similar queries
Thanks Igor. Can i get custom results like GamesWonByPlayer1 By using getColumn in the normal way? That may be a stupid question - I guess what I mean is, are those custom identifiers treated as column names when reading back from the select? Thanks, Ian On 16/11/2010 13:04, Igor Tandetnik wrote: > Ian Hardingham wrote: >> I have a badly designed structure for a table which records /games >> played/ by people. It looks like: >> >> id >> player1 >> player2 >> score >> >> If score> 0, player 1 won the game. If score< 0, player 2 won it. >> (Score of 0 is a draw). >> >> I wish to find the total record in games between two specific players. >> It seems to me that I would like to do the following: >> >> Select all games between the two players >> take a count of this >> Select from within the first select all games won by a certain player > I'm not quite sure what you are trying to achieve, but see if this helps, at > least as a starting point: > > select > count(*) TotalGames, > sum(score> 0) GamesWonByPlayer1, > sum(score< 0) GamesWonByPlayer2, > sum(score = 0) Draws > from Games where player1 = ? and player2 = ?; > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help - two similar queries
Ian Hardingham wrote: > I have a badly designed structure for a table which records /games > played/ by people. It looks like: > > id > player1 > player2 > score > > If score > 0, player 1 won the game. If score < 0, player 2 won it. > (Score of 0 is a draw). > > I wish to find the total record in games between two specific players. > It seems to me that I would like to do the following: > > Select all games between the two players > take a count of this > Select from within the first select all games won by a certain player I'm not quite sure what you are trying to achieve, but see if this helps, at least as a starting point: select count(*) TotalGames, sum(score > 0) GamesWonByPlayer1, sum(score < 0) GamesWonByPlayer2, sum(score = 0) Draws from Games where player1 = ? and player2 = ?; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query help - two similar queries
Hey guys. I have a badly designed structure for a table which records /games played/ by people. It looks like: id player1 player2 score If score > 0, player 1 won the game. If score < 0, player 2 won it. (Score of 0 is a draw). I wish to find the total record in games between two specific players. It seems to me that I would like to do the following: Select all games between the two players take a count of this Select from within the first select all games won by a certain player Is this kind of sub-selection possible? Should I really consider making another table which just records player wins and losses? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL:Re: Strange Corruption
I thought of another test you should try. Do an .export of your original database using 3.6.23.1 and .import it (constructing a new database). Then try your backup. If that works then you're just seeing corruption in the original database that 3.6.23.1 handles (since it created it). If it doesn't work import into 3.7.3 and test backup again. If it doesn't work then try cutting the SQL in half until it does work. Maybe you'll finally get a small enough size you can post. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010 6:27 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange Corruption No, this is definitely not the reason in my case as I can reproduce this issue on every 3.7.2/3.7.3 machine I've tested after copying the database file (and only the database file) to these machines. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange Corruption
No, this is definitely not the reason in my case as I can reproduce this issue on every 3.7.2/3.7.3 machine I've tested after copying the database file (and only the database file) to these machines. Am 15.11.2010 15:41, schrieb Kirk Clemons: > Not sure if it helps but I would see this quite frequently when an old > journal file would be left behind in the same directory as the backup > database. This could be why making a change to the database such as vacuum > would prevent the corruption. > ___ > 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] Trouble with TRIGGERS
On 11/16/2010 06:25 AM, Bernard Ertl wrote: > Hi, > > I'm experiencing some performance issues with triggers at the moment and > hoping someone can help shed some light on what is happening. > > I have a database with ~20 tables and>100 triggers. I noticed a severe > performance degradation after adding the last few triggers and it puzzled me > because the triggers I added should not be executed by the statements I was > testing. > > In a nutshell, I am inserting a record into a table. The last few triggers > causing the severe performance degradation should only be triggered if there > is an update to the table. > > I tried capturing the output from the EXPLAIN statement and, as best as I can > tell, SQLite is queing up triggers if there is a possibility that they will > be needed (but before an evaluation confirms it). > > For example, I have a trigger that, upon an insert, tests a condition and > possibly performs an update pending the results of the condition. As a > result, a whole slew of triggers conditioned to an update on the table are > showing up in the EXPLAIN output (immediately after the insert trigger) even > though the result of the initial condition is false and the update is not > executed. From what I'm seeing there is a huge cascade of triggers listed in > the EXPLAIN output that should never be executed because the conditions for > executing them are never met. > > Can anyone confirm how SQLite processes triggers? Am I interpreting the > EXPLAIN results correctly? Sounds like it. Calling sqlite3_prepare_v2() generates the VM code for all triggers that could possibly be invoked by your statement. All it considers when determining which triggers might be needed is the type of statement (UPDATE, DELETE, INSERT) and for UPDATES, the columns updated. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trouble with TRIGGERS
Hi, I'm experiencing some performance issues with triggers at the moment and hoping someone can help shed some light on what is happening. I have a database with ~20 tables and >100 triggers. I noticed a severe performance degradation after adding the last few triggers and it puzzled me because the triggers I added should not be executed by the statements I was testing. In a nutshell, I am inserting a record into a table. The last few triggers causing the severe performance degradation should only be triggered if there is an update to the table. I tried capturing the output from the EXPLAIN statement and, as best as I can tell, SQLite is queing up triggers if there is a possibility that they will be needed (but before an evaluation confirms it). For example, I have a trigger that, upon an insert, tests a condition and possibly performs an update pending the results of the condition. As a result, a whole slew of triggers conditioned to an update on the table are showing up in the EXPLAIN output (immediately after the insert trigger) even though the result of the initial condition is false and the update is not executed. From what I'm seeing there is a huge cascade of triggers listed in the EXPLAIN output that should never be executed because the conditions for executing them are never met. Can anyone confirm how SQLite processes triggers? Am I interpreting the EXPLAIN results correctly? Cordially, Bernard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange Corruption
Not sure if it helps but I would see this quite frequently when an old journal file would be left behind in the same directory as the backup database. This could be why making a change to the database such as vacuum would prevent the corruption. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
