Re: [sqlite] Pivot Sequential Data into Dynamic Groups

[Petite Abeille]

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

[Jim Mellander]

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

[Drake Wilson]

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

[Simon Slavin]

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

[Jim Mellander]

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

[Richard Hipp]

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

[Jim Mellander]

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

[Wolfgang Enzinger]

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

[Drake Wilson]

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

[Jim Mellander]

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

[Jim Mellander]

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

[Dave Dyer]

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

[Igor Tandetnik]

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

[Nicolas Williams]

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

[Drake Wilson]

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

[Adam DeVita]

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

[Andy Chambers]

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

[Oliver Peters]

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

[Oliver Peters]

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

[Drake Wilson]

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

[Alessandro Merolli]

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

[Igor Tandetnik]

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

[Greg Bryant]

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

[John Drescher]

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

[Simon Slavin]

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

[Brian Curley]

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

[Black, Michael (IS)]

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

[Igor Tandetnik]

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?

[Andrew Davison]

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

[Raj, Praveen]

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