Re: [sqlite] Pivot Sequential Data into Dynamic Groups
On Oct 1, 2010, at 2:41 PM, Igor Tandetnik wrote: > Rather than hardcoding table range, you can synthesize it directly from > measurement. Something like this: Yes, very nice. That said, not all problems deserve sophisticated solutions. > Note also that 'end' and 'temp' are keywords in SQL, you'll need to use > different identifiers. Similarly, identifier 'speed' (and others) is used > both as table alias and the column alias - this most likely won't fly. I > didn't fix these issues in the example above, so as not to distract from the > logic, but it needs these corrections before it can actually run. I'll leave > this as an exercise for the reader. Yes, good advise as SQL engines tend to reserve half of the English language for their exclusive use. In practice, SQLite doesn't mind and execute the statement properly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] update a record just using a trigger
I appreciate the prompt response to my prior question about triggers, and have another one. I'm using a python program to query an SQLite database from realtime data (IDS data actually). Every time I get a hit in the database, I want to record in that record an updated hitcount, and the timestamp that it occured, something like: cursor.execute("UPDATE detail SET lastaccessed=datetime('now'), hitcount=? WHERE ROWID=?", [ row["hitcount"]+1, row["ROWID"] ]) where row[] is the row that was read in from the database. However, I'm wondering if I can just do this via a trigger in the database, so that I just tell sqlite something like: cursor.execute("UPDATE detail WHERE ROWID=?", [row["ROWID"] ]); Can I have a trigger do all the work, or do I need to SET something? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger conditionally causing delete
Quoth Jim Mellander , on 2010-10-01 16:04:57 -0700: > I have another question on this, if you don't mind (please excuse my > ignorance of SQL). I tried your trigger: > > CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail > FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) > BEGIN > DELETE FROM summary WHERE key = OLD.key; > END; > > and it works as advertised, but I would like to understand why. I'm a > bit hazy on why the FOR EACH ROW statement works. Does FOR EACH ROW > refer to each row that is being removed from 'detail'? Is it that the > statements between BEGIN and END run when the condition 'NOT EXISTS > (SELECT * FROM detail WHERE key = OLD.key)' is fulfilled ? (Note that I'm not sure the above is the best way for various values of "best", just a reasonably simple way that does what you said.) The WHEN and the FOR EACH ROW are separate. The WHEN clause causes the trigger to only run for some rows rather than for all rows, so the body does not execute unless the NOT EXISTS ... part is true. The FOR EACH ROW applies to the trigger as a whole, and indicates how many invocations are generated: one trigger activation per relevant row, with the WHEN clause limiting which rows are considered relevant. In SQLite, the FOR EACH ROW is a syntactic salt no-op, because all triggers act that way, but some database engines also support FOR EACH STATEMENT triggers, which run only once when triggered by a statement that alters multiple rows. I prefer to use the explicit form, but omitting it would change nothing. The full CREATE TRIGGER syntax is of course part of the documentation, at http://sqlite.org/lang_createtrigger.html. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger conditionally causing delete
On 2 Oct 2010, at 12:04am, Jim Mellander wrote: > I tried your trigger: > > CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail > FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) > BEGIN > DELETE FROM summary WHERE key = OLD.key; > END; > > and it works as advertised, but I would like to understand why. I'm a > bit hazy on why the FOR EACH ROW statement works. Does FOR EACH ROW > refer to each row that is being removed from 'detail'? 'FOR EACH ROW' reflects other possibilities defined in SQL which SQLite doesn't support right now. This page http://www.sqlite.org/lang_createtrigger.html describes what it means. The alternative would be 'FOR EACH STATEMENT' and the two would be different if one UPDATE command would make a change in more than one row in the database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger conditionally causing delete
I have another question on this, if you don't mind (please excuse my ignorance of SQL). I tried your trigger: CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) BEGIN DELETE FROM summary WHERE key = OLD.key; END; and it works as advertised, but I would like to understand why. I'm a bit hazy on why the FOR EACH ROW statement works. Does FOR EACH ROW refer to each row that is being removed from 'detail'? Is it that the statements between BEGIN and END run when the condition 'NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key)' is fulfilled ? Once I grok SQL, I'm sure this will all seem like old hat, but I would appreciate any further clarifications. Thanks in advance. On Fri, Oct 1, 2010 at 3:30 PM, Jim Mellander wrote: > Thanks, this is great - I'll read up on expression syntax and usage. > > On Fri, Oct 1, 2010 at 3:11 PM, Drake Wilson wrote: >> Quoth Jim Mellander , on 2010-10-01 14:38:03 -0700: >>> Hi: >>> >>> I want to use to trigger on deletion of a detail record to >>> automatically delete a summary record, if not more detail records >>> exist, something like: >>> >>> CREATE TRIGGER detail_delete AFTER DELETE ON detail >>> BEGIN >>> -- here I don't know syntax >>> IF COUNT(detail records with key) = 0 DELETE summary WHERE >>> key=old.key; >>> END; >> >> SQLite triggers don't use an imperative language with full control >> structures, just basic SQL. However, in this case you can use a WHEN >> clause: >> >> CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail >> FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) >> BEGIN >> DELETE FROM summary WHERE key = OLD.key; >> END; >> >> ---> Drake Wilson >> ___ >> 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] errors after sqlite3_step
On Fri, Oct 1, 2010 at 6:24 PM, Wolfgang Enzinger wrote: > Am Fri, 01 Oct 2010 14:11:51 -0700 schrieb Dave Dyer: > > > There ought to be a definitive list of what errors can occur > > after sqlite3_step, and which (if any) are recoverable. > > IMHO that's impossible, at least because SQLite allows the definition of > user defined functions. Now such a function may process the first few rows > quite fine, but will raise an error with the next result row because it > encounters a NULL value where an integer is expected, for instance. > > At the time sqlite_prepare() is executed, such things cannot be foreseen. > Wolfgang is correct. An application-defined function can invoke sqlite3_result_error_code() in order to return any error it wants. So the definitive list of errors that can be returned from sqlite_step() is the universal set. We actually use that trick in testing, for example to verify that an SQLITE_SCHEMA error will only provoke a re-prepare a limited number of times before giving up and returning the error back up to the application. > > Wolfgang > > ___ > 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] Trigger conditionally causing delete
Thanks, this is great - I'll read up on expression syntax and usage. On Fri, Oct 1, 2010 at 3:11 PM, Drake Wilson wrote: > Quoth Jim Mellander , on 2010-10-01 14:38:03 -0700: >> Hi: >> >> I want to use to trigger on deletion of a detail record to >> automatically delete a summary record, if not more detail records >> exist, something like: >> >> CREATE TRIGGER detail_delete AFTER DELETE ON detail >> BEGIN >> -- here I don't know syntax >> IF COUNT(detail records with key) = 0 DELETE summary WHERE >> key=old.key; >> END; > > SQLite triggers don't use an imperative language with full control > structures, just basic SQL. However, in this case you can use a WHEN > clause: > > CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail > FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) > BEGIN > DELETE FROM summary WHERE key = OLD.key; > END; > > ---> Drake Wilson > ___ > 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] errors after sqlite3_step
Am Fri, 01 Oct 2010 14:11:51 -0700 schrieb Dave Dyer: > There ought to be a definitive list of what errors can occur > after sqlite3_step, and which (if any) are recoverable. IMHO that's impossible, at least because SQLite allows the definition of user defined functions. Now such a function may process the first few rows quite fine, but will raise an error with the next result row because it encounters a NULL value where an integer is expected, for instance. At the time sqlite_prepare() is executed, such things cannot be foreseen. Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger conditionally causing delete
Quoth Jim Mellander , on 2010-10-01 14:38:03 -0700: > Hi: > > I want to use to trigger on deletion of a detail record to > automatically delete a summary record, if not more detail records > exist, something like: > > CREATE TRIGGER detail_delete AFTER DELETE ON detail > BEGIN > -- here I don't know syntax >IF COUNT(detail records with key) = 0 DELETE summary WHERE key=old.key; > END; SQLite triggers don't use an imperative language with full control structures, just basic SQL. However, in this case you can use a WHEN clause: CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) BEGIN DELETE FROM summary WHERE key = OLD.key; END; ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger conditionally causing delete
As a newbie just learning SQL, this looks like a foreign key situation - unfortunately I'm using the standard snowleopard sqlite which is 3.6.12, which appears to not support foreign key constraints. Assuming I upgrade, I presume I can add the summary record with a BEFORE trigger if it isn't there, and attempt delete of the summary record with an AFTER trigger, which will not occur if the foreign key constraint is set correctly. Would this be an accurate understanding? On Fri, Oct 1, 2010 at 2:38 PM, Jim Mellander wrote: > Hi: > > I want to use to trigger on deletion of a detail record to > automatically delete a summary record, if not more detail records > exist, something like: > > CREATE TRIGGER detail_delete AFTER DELETE ON detail > BEGIN > -- here I don't know syntax > IF COUNT(detail records with key) = 0 DELETE summary WHERE key=old.key; > END; > > > Is something like this possible to do? Any suggestions. > > Thanks in advance > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger conditionally causing delete
Hi: I want to use to trigger on deletion of a detail record to automatically delete a summary record, if not more detail records exist, something like: CREATE TRIGGER detail_delete AFTER DELETE ON detail BEGIN -- here I don't know syntax IF COUNT(detail records with key) = 0 DELETE summary WHERE key=old.key; END; Is something like this possible to do? Any suggestions. Thanks in advance ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] errors after sqlite3_step
There ought to be a definitive list of what errors can occur after sqlite3_step, and which (if any) are recoverable. In the case in point, I sqlite_prepare_v2 returns sqlite_ok, and the initial call to sqlite_step returns sqlite_cantopen This doesn't make any sense to me, and certainly isn't mentioned as a possibility in the C api. I seems to be fatal though. This error is associated with a lock contention scenario. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
Andy Chambers wrote: > Given the following > > create table events ( > id, > date, > status > ); > insert into events values ('001','a','N'); > insert into events values ('001','b','N'); > insert into events values ('001','c','Y'); > insert into events values ('001','d','N'); > insert into events values ('001','e','Y'); > insert into events values ('001','f','Y'); > insert into events values ('001','g','N'); > > Is it possible, using plain SQL, to derive the following > > 001,c,d > 001,e,g > > i.e. an "N" in the third column means event "001" has stopped, and a > "Y" means it > has started back up again. Note that because the status immediately > preceding "f" > is also "Y", there is no corresponding row in the output select Start.id, Start.date, Stop.date from events Start join events Stop on (Start.id = Stop.id and Start.date < Stop.date) where Start.status = 'Y' and Stop.status = 'N' and not exists (select 1 from events Middle where Middle.date > Start.date and Middle.date < Stop.date and Middle.status = 'N'); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
On Fri, Oct 01, 2010 at 08:37:07PM +0100, Andy Chambers wrote: > Given the following > > create table events ( > id, > date, > status > ); > insert into events values ('001','a','N'); > insert into events values ('001','b','N'); > insert into events values ('001','c','Y'); > insert into events values ('001','d','N'); > insert into events values ('001','e','Y'); > insert into events values ('001','f','Y'); > insert into events values ('001','g','N'); > > Is it possible, using plain SQL, to derive the following > > 001,c,d > 001,e,g It'd be easier, I think, if you had a monotonically increasing (always by one) unique numeric column. The you could do a select for rows in events where the value for that column is between two values which are sub-queries for immediately preceding and following rows in the same table and where the status differs from that of the row in the top-level query. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
Quoth Andy Chambers , on 2010-10-01 20:37:07 +0100: > Is it possible, using plain SQL, to derive the following > > 001,c,d > 001,e,g In other words, you're comparing rows with "adjacent" ones? Yes, it's probably possible, but it would be very awkward and possibly slow. You'd join the table to itself or use fancy subqueries, depending on the specifics. (In particular, pairing the "transitions" to get the pairs back as single rows would be extra-awkward without temporary tables, I think.) Is there a reason you're trying to do this with SQL? Why not just do it in plain application logic? In the absence of more information, that would seem a more natural way to go about it. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
Why would you want to do this in plane sql, as opposed to using the API to go through the list and derive it? On Fri, Oct 1, 2010 at 3:37 PM, Andy Chambers wrote: > Given the following > > create table events ( > id, > date, > status > ); > insert into events values ('001','a','N'); > insert into events values ('001','b','N'); > insert into events values ('001','c','Y'); > insert into events values ('001','d','N'); > insert into events values ('001','e','Y'); > insert into events values ('001','f','Y'); > insert into events values ('001','g','N'); > > Is it possible, using plain SQL, to derive the following > > 001,c,d > 001,e,g > > i.e. an "N" in the third column means event "001" has stopped, and a > "Y" means it > has started back up again. Note that because the status immediately > preceding "f" > is also "Y", there is no corresponding row in the output > > Cheers, > Andy > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tricky grouping query
Given the following create table events ( id, date, status ); insert into events values ('001','a','N'); insert into events values ('001','b','N'); insert into events values ('001','c','Y'); insert into events values ('001','d','N'); insert into events values ('001','e','Y'); insert into events values ('001','f','Y'); insert into events values ('001','g','N'); Is it possible, using plain SQL, to derive the following 001,c,d 001,e,g i.e. an "N" in the third column means event "001" has stopped, and a "Y" means it has started back up again. Note that because the status immediately preceding "f" is also "Y", there is no corresponding row in the output Cheers, Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accented characters and ODBC
Greg Bryant writes: just saw that you usually should install 3 different ODBC-drivers and 1 of them is called SQLite ODBC (UTF-8) Driver. Maybe you should give it a try before asking the programmer greetings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accented characters and ODBC
Greg Bryant writes: [...] > Does anyone have any pointers on where I can look to figure > this out? [...] maybe the friendly driver programmer is willing to help you if you send a bug report http://www.ch-werner.de/sqliteodbc/html/index.html greetings Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accented characters and ODBC
Quoth Greg Bryant , on 2010-10-01 12:51:10 -0500: > I'm using current SQLite (3.7.2) via a visual c++ app. We're connecting a > sqlite3 database via ODBC (driver from Driver from where again? > When I go to do a select via the ODBC connection, however, I get back > garbage for the accented character (specifically, I get André). You're getting back UTF-8 and then (somewhere) interpreting it as Latin-1. I'm not too familiar with ODBC; is there a way you can specify the connection's text encoding? SQLite uses UTF-8 or UTF-16 for text. (You can choose which only at database creation time.) Similarly, make sure that you actually give it Unicode strings in the target encoding; there may be some autocorrection going on if you try to feed it Latin-1 characters, but I wouldn't rely on it. > Thanks, > Greg ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Temporary views across attached databases
I've being using this feature massively since version 3.6.7. Now I'm working (production) with 3.6.22. It works just fine. I've being testing 3.7.x and it seems OK too. Best regards. Alessandro Merolli. On 01/10/2010, at 14:26, John Drescher wrote: > I see that normal views across attached databases do not work but it > looks like I can create a temporary view across attached databases. Is > this a supported configuration? I want to make sure before I put this > in my application. > > -- > John M. Drescher > ___ > 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] Trigger mask entities...
Brian Curley wrote: > In reading through the code as a non-C kind of guy, it looks like there's an > array of columnar values lumped together as old.* and new.* would need them, > but nothing that contains the individual field or column names. I'm not sure I understand. A trigger is tied to a particular table. Surely you know you own table's schema, don't you? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accented characters and ODBC
Not sure if ODBC questions belong here, feel free to point me to a better forum. I'm using current SQLite (3.7.2) via a visual c++ app. We're connecting a sqlite3 database via ODBC (driver from , also current version - 0.87). If I do either an insert or update using an accented character (e.g. André), it works fine, and I can check it using anything that directly connects to the database (I've been using SQLiteStudio and SQLiteAdmin) and it looks fine. When I go to do a select via the ODBC connection, however, I get back garbage for the accented character (specifically, I get André). I don't think the app settings are a problem, since the accent works fine through the insert, and step into doesn't get me past the SQLFetch from Microsoft's odbc layer, and I don't think it's sqlite, since it appears to be correct when viewed through a direct connection, which just leaves the odbc layer. Does anyone have any pointers on where I can look to figure this out? Thanks, Greg ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Temporary views across attached databases
I see that normal views across attached databases do not work but it looks like I can create a temporary view across attached databases. Is this a supported configuration? I want to make sure before I put this in my application. -- John M. Drescher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger mask entities...
On 1 Oct 2010, at 4:44pm, Brian Curley wrote: > My business case would be in the realm of logging, for example. If I'm > using an audit-centric trigger, I'd probably want to store field and value, Don't forget that one 'UPDATE' command can result in many different fields being changed. So that should be something like 'fields and values'. > as opposed to the entire row as only one or two fields might change and to > conserve space. I could probably rig a rube goldberg series of triggers to > store and compare old.* and new.* values to reinsert elsewhere but if the > column names are already present it'd be a nice little shortcut. My primary > use would be via the shell... My reading of that suggests you want to use triggers to create a log of all changes made to a table (or possibly database). This will result in a very slow system since any change which would normally result in a single change to the database would result in many calculations and rows written. You would also have to take your schema apart and make it apparent inside your SQLite code ... if the field 'address' changed (i.e. old.address != new.address), note this in the log, if the field 'phoneNumber' changed, note that in the log, ... which tends to be a recipe for disaster when you want to change your schema later. Rather than use triggers for this, you might come up with your own routine to call to make changes. For example, where you'd normally pass an 'UPDATE' command directly to SQLite, you pass it to your own routine, which first logs the command sent, then passes it to SQLite. So rather than log the result of each command executed, you simply log the command itself. This requires no analysis, and only one extra write per write, so it results in a faster system, and doesn't require you to change your triggers when you change your schema. Another way to do it would be to use sqlite3_update_hook() or sqlite3_trace(), and have them write your own log file for you. I have no real idea how this would work but perhaps someone else does. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger mask entities...
Looking for some guidance or enlightenment on the available fields within a given trigger. In reading through the code as a non-C kind of guy, it looks like there's an array of columnar values lumped together as old.* and new.* would need them, but nothing that contains the individual field or column names. Am I missing something or is this deemed too specific/non-generic for the trigger purposes? My business case would be in the realm of logging, for example. If I'm using an audit-centric trigger, I'd probably want to store field and value, as opposed to the entire row as only one or two fields might change and to conserve space. I could probably rig a rube goldberg series of triggers to store and compare old.* and new.* values to reinsert elsewhere but if the column names are already present it'd be a nice little shortcut. My primary use would be via the shell... Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS Question
I'm experimenting with the FTS capability and have a question How do I enter a value in the database that is hyphenated? Seems to be backwards form what I would expectif the content contains a hyphen then only non-hyphenated query returns the correct value whereas the hyphenated one returns the wrong one. This seems to not work correctly...or am I missing a concept here? SQLite version 3.7.2 sqlite> create virtual table dict using fts3(content); sqlite> insert into dict values('play-off'); sqlite> insert into dict values('play-back'); sqlite> select * from dict where content match 'play-back'; play-off sqlite> select * from dict where content match 'play-off'; play-back sqlite> select * from dict where content match 'play off'; play-off sqlite> select * from dict where content match 'play back'; play-back Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pivot Sequential Data into Dynamic Groups
Petite Abeille wrote: > On Sep 30, 2010, at 11:44 PM, Jordan Dahlke wrote: > >> Is there a good way to do this with Select and Group By statement? > > For a given definition of "good" :P > > create temporary table range as > select 0 as start, 24 as end union all > select 25 as start, 49 as end union all > select 50 as start, 74 as end union all > select 75 as start, max( value ) as end from measurement; > > > > select range.start || ' - ' || range.end as time, >sum( coalesce( speed.value, 0 ) ) as speed, >sum( coalesce( direction.value, 0 ) ) as direction, >sum( coalesce( temp.value, 0 ) ) as temp > fromrange > > left join measurement speed > on speed.variable = 'Speed' > and speed.time between range.start and range.end > > left join measurement direction > on direction.variable = 'Direction' > and direction.time between range.start and range.end > > left join measurement temp > on temp.variable = 'Temp' > and temp.time between range.start and range.end > > group byrange.start, >range.end > > order byrange.start Rather than hardcoding table range, you can synthesize it directly from measurement. Something like this: select range.start || ' - ' || range.end as time, avg(speed.value) as speed, avg(direction.value) as direction, avg(temp.value) as temp from (select range_num*25 as start, (range_num+1)*25 as end from (select cast(time / 25 as integer) range_num from measurement group by range_num)) as range left join measurement speed on speed.variable = 'Speed' and speed.time >= range.start and speed.time < range.end left join measurement direction on direction.variable = 'Direction' and direction.time >= range.start and direction.time < range.end left join measurement temp on temp.variable = 'Temp' and temp.time >= range.start and temp.time < range.end group byrange.start order byrange.start; Note also that 'end' and 'temp' are keywords in SQL, you'll need to use different identifiers. Similarly, identifier 'speed' (and others) is used both as table alias and the column alias - this most likely won't fly. I didn't fix these issues in the example above, so as not to distract from the logic, but it needs these corrections before it can actually run. I'll leave this as an exercise for the reader. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?
I've used SQLite for exactly this. I have no gripes with it at all. I ended up using just one table in one DB as indexing makes things simple and fast. WAL i've been trying these last few days and seems trouble-free. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS
Hi, I'm validating SQLite test suite version 3.7.2 on QNX operating system. I have built the testfixture using SQLite amalgation file and other related files/libraries. I ran the full test suite and most of the test cases are passing, except some run in journal_mode = WAL. Test cases whose autocheckpoint is turned off and large amount of data is being inserted without checkpointing are failing. Other WAL tests are passing. I tried increasing the stack size of the testfixture process, but that didn't help. If i reduce the number of inserts or increase the WAL page size, test cases are passing. I believe there is some corruption happening with the WAL index while inserting large number of rows. Here is an example (from SQLite test suite) of the failure (Purposefully added "PRAGMA integrity_check" at end of wal3-1.0) set a_string_counter 1 proc a_string {n} { global a_string_counter incr a_string_counter string range [string repeat "${a_string_counter}." $n] 1 $n } db func a_string a_string do_test wal3-1.0 { execsql { PRAGMA cache_size = 2000; PRAGMA page_size = 1024; PRAGMA auto_vacuum = off; PRAGMA synchronous = normal; PRAGMA journal_mode = WAL; PRAGMA wal_autocheckpoint = 0; BEGIN; CREATE TABLE t1(x); INSERT INTO t1 VALUES( a_string(800) ); /*1 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /*2 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /*4 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /*8 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 64 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 128*/ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 256 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 512 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 1024 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2048 */ INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4018 */ COMMIT; PRAGMA cache_size = 10; PRAGMA integrity_check; } } {ok} for {set i 1} {$i < 50} {incr i} { do_test wal3-1.$i.1 { set str [a_string 800] execsql { UPDATE t1 SET x = $str WHERE rowid = $i } lappend L [wal_frame_count test.db-wal 1024] execsql { BEGIN; INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 100; ROLLBACK; PRAGMA integrity_check; } } {ok} # Check that everything looks OK from the point of view of an # external connection. # sqlite3 db2 test.db do_test wal3-1.$i.2 { execsql { SELECT count(*) FROM t1 } db2 } 4018 do_test wal3-1.$i.3 { execsql { SELECT x FROM t1 WHERE rowid = $i } } $str do_test wal3-1.$i.4 { execsql { PRAGMA integrity_check } db2 } {ok} db2 close # Check that the file-system in its current state can be recovered. # file copy -force test.db test2.db file copy -force test.db-wal test2.db-wal file delete -force test2.db-journal sqlite3 db2 test2.db do_test wal3-1.$i.5 { execsql { SELECT count(*) FROM t1 } db2 } 4018 do_test wal3-1.$i.6 { execsql { SELECT x FROM t1 WHERE rowid = $i } } $str do_test wal3-1.$i.7 { execsql { PRAGMA integrity_check } db2 } {ok} db2 close } The result of above test: wal3-1.0... Expected: [ok] Got: [wal 0 {*** in database main *** Page 2: btreeInitPage() returns error code 11 Page 3 is never used Page 4 is never used Page 5 is never used Page 99 is never used Page 100 is never used Page 101 is never used Page 102 is never used}] wal3-1.1.1... Error: database disk image is malformed wal3-1.1.2... Error: no such table: t1 wal3-1.1.3... Error: database disk image is malformed wal3-1.1.4... Ok wal3-1.1.5... Error: no such table: t1 wal3-1.1.6... Error: database disk image is malformed wal3-1.1.7... Ok The above error repeats for all iterations. Any help/suggestions for the above issue will be highly appreciated. Other tests which fail include: walfault-6-pre-1 walfault-11-pre-1 wal-13.$tn.$ii.a - wal-13.$tn.$ii.d (after 12th iteration) wal-20.2 - wal-20.4 Thanks. The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org