Re: [sqlite] how can we solve IF EXIST in SQLite

[Asif Lodhi]

Sorry, I don't see EXISTS in SQLite documentation.

On 8/20/09, Asif Lodhi  wrote:
> Hi,
>
> Perhaps you can do this in TWO SQL STATEMENTS - see below. However,
> this way you inefficiently check the existence twice:
>
> On 6/2/09, robinsmathew  wrote:
>> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
>> prod_batch_code=1000)
>> UPDATE stock_tab
>> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
>> WHERE
>> oduct_batch_code=1000 )
>> WHERE prod_batch_code=1000
>> ELSE
>> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>> stock_date) values (20009, 1003, 200,
>> DATETIME('NOW') );
>
> UPDATE stock_tab
>   SET stock_qty=stock_qty+(SELECT purchase_qty
>FROMpurchase_tab
>WHERE prod_batch_code=1000)
> WHERE prod_batch_code=1000
> AND  EXISTS (SELECT prod_batch_code
>   FROMstock_tab
>   WHERE prod_batch_code=1000);
> INSERT INTO stock_tab (stock_id, prod_batch_code, stock_qty, stock_date)
> VALUES   (20009, 1003, 200, DATETIME('NOW') )
> WHERE NOT EXISTS (SELECT prod_batch_code
>   FROMstock_tab
>   WHERE prod_batch_code=1000);
>
> -Asif
> PS: List users, is there any way we can cache the result of the EXISTS
> clause above in order to avoid having to execute it twice using the
> above approach?
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how can we solve IF EXIST in SQLite

[Asif Lodhi]

Hi,

Perhaps you can do this in TWO SQL STATEMENTS - see below. However,
this way you inefficiently check the existence twice:

On 6/2/09, robinsmathew  wrote:
> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE prod_batch_code=1000)
> UPDATE stock_tab
> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab WHERE
> oduct_batch_code=1000 )
> WHERE prod_batch_code=1000
> ELSE
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date) values (20009, 1003, 200,
> DATETIME('NOW') );

UPDATE stock_tab
  SET stock_qty=stock_qty+(SELECT purchase_qty
   FROMpurchase_tab
   WHERE prod_batch_code=1000)
WHERE prod_batch_code=1000
AND  EXISTS (SELECT prod_batch_code
  FROMstock_tab
  WHERE prod_batch_code=1000);
INSERT INTO stock_tab (stock_id, prod_batch_code, stock_qty, stock_date)
VALUES   (20009, 1003, 200, DATETIME('NOW') )
WHERE NOT EXISTS (SELECT prod_batch_code
  FROMstock_tab
  WHERE prod_batch_code=1000);

-Asif
PS: List users, is there any way we can cache the result of the EXISTS
clause above in order to avoid having to execute it twice using the
above approach?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Huge numbers of save points.

[Chris Dew]

http://www.sqlite.org/lang_savepoint.html
I'm looking for a datastore with the following properties:
 * I need to 'mark' the state frequently (sub second interval).
 * I need to be able to revert the datastore to a previous mark (with no
appreciable delay).
 * I only need to keep the last few hundred 'marks'. (i.e. I never need to
revert to a datastore marked more than a few minutes ago.)

The savepoint functionality in sqlite looks to almost fit the bill, but its
savepoints seem to be nested - i.e. you cannot 'forget about' old savepoints
while retaining recent savepoints. Is my understanding correct here? I'm
concerned that this would cause a performance issue when millions of nested
savepoints have accumulated.

Obviously I can roll my own data structure here, but is sqlite was feasible,
it would be good.

Does anyone have any suggestions?

Thanks,

Chris.


-- 

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

[sqlite] SUGGESTION: sqlite3 and TMP environment variable

[Roger Paratore Bock]

If I set

PRAGMA temp_store = MEMORY;

and

PRAGMA temp_store_directory = 'C:\scratch';

but TMP is set to an invalid directory, I get the following error

sqlite3.OperationalError: unable to open database file

Two suggestions:

1) If temp_store_directory is set, sqlite should not care what TMP is set to.

2) The sqlite3.OperationalError message should tell you the path to the file it 
is unable to open.

Thank you,
Roger


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

[sqlite] Huge numbers of savepoints.

[Chris Dew]

http://www.sqlite.org/lang_savepoint.html
I'm looking for a datastore with the following properties:
 * I need to 'mark' the state frequently (sub second interval).
 * I need to be able to revert the datastore to a previous mark (with no
appreciable delay).
 * I only need to keep the last few hundred 'marks'.  (i.e. I never need to
revert to a datastore marked more than a few minutes ago.)

The savepoint functionality in sqlite looks to almost fit the bill, but its
savepoints seem to be nested - i.e. you cannot 'forget about' old savepoints
while retaining recent savepoints.  Is my understanding correct here?  I'm
concerned that this would cause a performance issue when millions of nested
savepoints have accumulated.

Obviously I can roll my own data structure here, but is sqlite was feasible,
it would be good.

Does anyone have any suggestions?

Thanks,

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[sub sk79]

Hi,

You might want to check-out StepSqlite PL/SQL compiler for SQLite at
http://www.metatranz.com/stepsqlite
Using it you can write the trigger (as part of a package body) almost the
way you wrote in original post. StepSqlite compiles the PL/SQL code to a
linux x86 shared library which can be linked in to your C++ app and used as
the database interface to SQLite. There is an easy-to-follow  tutorial here:
 http://www.metatranz.com/stepsqlite/tutorial.html

*If you do not use C++*, wait till StepSqlite adds support for creating a
loadable SQLite extension for SQLite and then you can simply load the
compiled shared library into your SQLite database.

(NOTE: below given is not pseudo code - this is actual code which you need
to write and leave the rest to StepSqlite):

===
CREATE TABLE stock_tab(stock_id NUMBER(5), prod_batch_code NUMBER(5),
stock_qty NUMBER(5), stock_date date);
CREATE TABLE purchase_tab(product_batch_code NUMBER(5), purchase_qty
NUMBER(5));

PACKAGE BODY MyDBinterface IS
BEGIN
CREATE TRIGGER insert_stock_from_product
AFTER INSERT ON purchase_tab
DECLARE
count NUMBER(5);
BEGIN
SELECT count(prod_batch_code) INTO count FROM stock_tab WHERE
prod_batch_code= :new.product_batch_code;
IF count > 0  THEN
UPDATE stock_tab
SET stock_qty = stock_qty + :new.purchase_qty
WHERE prod_batch_code= :new.product_batch_code
ELSE
INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
stock_date)
VALUES (20009, :new.product_batch_code, :new.purchase_qty,
sysdate() );
END IF;
END;
END;

===

-SK

On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew wrote:

>
> guys i ll clarify the problem
> this is the purchase table here purchase id is PK
>
> purchase_id  prod_batch_code  vendor_type_code  purchase_qty  purchase_date
> ---  ---    
> ---
> 11000 1 100 2009-05-26
> 18:19:27
> 21001 1 100 2009-05-26
> 18:19:31
> 31002 1 100 2009-05-26
> 18:19:35
> 41003 1 100 2009-05-26
> 18:19:49
>
> this is the stock table here stock_id is PK and prod_batch_code is FK
>
> stock_idprod_batch_code  stock_qty   stock_date
> --  ---  --  ---
> 20001   1001 105 2009-05-26 18:19:27
> 20002   1002 100ps   2009-05-26 18:19:31
> 20003   1003 100ps   2009-05-26 18:19:35
> 20004   1003 100ps   2009-05-26 18:19:43
> 20005   1002 100ps   2009-05-26 18:19:44
> 20006   1001 100ps   2009-05-26 18:19:49
> 20007   1000 85  2009-05-26 18:19:50
> 20008   1000 85  2009-05-26 18:19:51
>
> i wrote a trigger
> CREATE TRIGGER insert_stock_from_product
>AFTER INSERT ON purchase_tab
>BEGIN
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date)
> values (new.purchase_id+2, new.prod_batch_code,
> new.purchase_qty,
> new.purchase_date );
>END;
>
> instead of inserting the same products repeatedly in the stock table i jus
> want the quantity as well as the dates to be updated . and wen i insert a
> new product_batch_code to the purchase table its shuld be inserted in the
> stock table also...
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] building an extension

[P Kishor]

are the following instructions at
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions for building a
loadable extension still valid?

How To Build a Loadable Extension Shared Library on Linux

0. untar latest sqlite3 source code in a new directory
1. cd to the newly untarred sqlite directory
2. Comment out the line in Makefile.in to enable loadable extensions:

 # TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1

3. ./configure LIBS=-ldl && make sqlite3
4. export LD_LIBRARY_PATH="`pwd`:$LD_LIBRARY_PATH"
5. gcc -I`pwd` -shared src/test_loadext.c -o half.so
6. ./sqlite3

My Makefile.in seems to not have the above line '#TCC += ..."

What is the obvious that I am missing?


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

Re: [sqlite] How to select data from 2 lines in one line?

[Kit]

2009/8/19 Mário Anselmo Scandelari Bussmann :
> Both work for me, Kit solution is very fast, but I think John is right. In
> my case, the tables have sequencial rowid. If I delete some row, then will
> not work anymore.

When you create an index of column data, John's solution will be fast too.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[Mário Anselmo Scandelari Bussmann]

Both work for me, Kit solution is very fast, but I think John is right. In
my case, the tables have sequencial rowid. If I delete some row, then will
not work anymore.


On Wed, Aug 19, 2009 at 12:44 PM, John Machin  wrote:

> On 20/08/2009 12:57 AM, Kit wrote:
> > Right form (tested):
> >
> > SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
> > previous_data,temp.preult AS previous_preult
> >FROM petr4,petr4 AS temp
> >WHERE petr4.rowid=temp.rowid+1;
>
> Don't you think that relying on (a) rowid being consecutive (b) rowid
> order being identical to date order is just a little bit dodgy? What if
> the table has been created by a bulk load, not necessarily in ascending
> date order? What if some rows have been deleted?
> ___
> 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] Increment a value if constraint violated

[Pavel Ivanov]

> Are you sure that was the OP's intention?

No, I'm not sure, of course, because OP didn't answer yet. But words
"First row with id 3" and "Third row with id 3" suggested to me that
they are different rows. And mentioning SQLITE_CONSTRAINT I've
interpreted as "unique constraint on id-count pair".
So let's wait for the answer. :)

Pavel

On Wed, Aug 19, 2009 at 12:05 PM, Igor Tandetnik wrote:
> Pavel Ivanov  wrote:
>>> insert or replace into mytable(id, count)
>>> values (:id, coalesce((select count from mytable where id=:id), 0) +
>>> 1);
>>
>> I guess it doesn't work as OP intended:
>>
>> sqlite> select * from t;
>> id|count
>> 1|1
>> 1|2
>> 1|3
>> 1|4
>
> Are you sure that was the OP's intention? His pseudocode suggests to me
> that id is supposed to be unique, and the count should reflect how many
> times this particular id was inserted. He definitely mentions
> SQLITE_CONSTRAINT, while your table doesn't appear to be constrained in
> any way.
>
> 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] How to select data from 2 lines in one line?

[Kit]

2009/8/19 John Machin :
> On 20/08/2009 12:57 AM, Kit wrote:
>> Right form (tested):
>>
>> SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
>> previous_data,temp.preult AS previous_preult
>>FROM petr4,petr4 AS temp
>>WHERE petr4.rowid=temp.rowid+1;
>
> Don't you think that relying on (a) rowid being consecutive (b) rowid
> order being identical to date order is just a little bit dodgy? What if
> the table has been created by a bulk load, not necessarily in ascending
> date order? What if some rows have been deleted?

OK, you found more better solution.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Increment a value if constraint violated

[Igor Tandetnik]

Pavel Ivanov  wrote:
>> insert or replace into mytable(id, count)
>> values (:id, coalesce((select count from mytable where id=:id), 0) +
>> 1);
>
> I guess it doesn't work as OP intended:
>
> sqlite> select * from t;
> id|count
> 1|1
> 1|2
> 1|3
> 1|4

Are you sure that was the OP's intention? His pseudocode suggests to me 
that id is supposed to be unique, and the count should reflect how many 
times this particular id was inserted. He definitely mentions 
SQLITE_CONSTRAINT, while your table doesn't appear to be constrained in 
any way.

Igor Tandetnik



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

Re: [sqlite] How to select data from 2 lines in one line?

[John Machin]

On 20/08/2009 12:57 AM, Kit wrote:
> Right form (tested):
> 
> SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
> previous_data,temp.preult AS previous_preult
>FROM petr4,petr4 AS temp
>WHERE petr4.rowid=temp.rowid+1;

Don't you think that relying on (a) rowid being consecutive (b) rowid 
order being identical to date order is just a little bit dodgy? What if 
the table has been created by a bulk load, not necessarily in ascending 
date order? What if some rows have been deleted?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[Mário Anselmo Scandelari Bussmann]

I said its slow but I forget index. Now is as fast as a lightningbolt!!
Thanks again!

On Wed, Aug 19, 2009 at 12:23 PM, Mário Anselmo Scandelari Bussmann <
mario.bussm...@gmail.com> wrote:

> Bingo! This works very well (a little bit slow, since I have a 30
> rows). Thank you all!!!
>
>
> On Wed, Aug 19, 2009 at 11:42 AM, John Machin wrote:
>
>> On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote:
>> > I have a table like this:
>> >
>> > petr4
>> > ---
>> > rowid|data|preabe|premax|premin|preult|voltot
>> > 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
>> > 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
>> [snip]
>> > 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
>> > 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
>> >
>> > I need a select that returns data,preult,previous data and previous
>> preult:
>> >
>> > 2007-01-03|48.7|2007-01-02|50.45
>> > 2007-01-04|47.65|2007-01-03|48.7
>> [snip]
>> > 2007-01-12|45.15|2007-01-11|45.21
>> > 2007-01-15|44.89|2007-01-12|45.15
>> >
>> > How can I do that using only sql (no python, c or perl, no cursor)?
>>
>> No Python? How cruel :-)
>>
>> This works but you'd better have an index on 'data', and it looks like
>> at least O(N**2) OTTOMH:
>>
>> sqlite> create table x (data,preabe,premax,premin,preult,voltot);
>> sqlite> insert into x values
>> ('2007-01-02',50.0,50.45,49.76,50.45,256115409.0);
>> /* etc etc*/
>> sqlite> select a.data, a.preult, b.data, b.preult from x a, x b
>>...> where b.data = (select max(c.data) from x c where c.data < a.data)
>>...> order by a.data;
>> 2007-01-03|48.7|2007-01-02|50.45
>> 2007-01-04|47.65|2007-01-03|48.7
>> [snip]
>> 2007-01-12|45.15|2007-01-11|45.21
>> 2007-01-15|44.89|2007-01-12|45.15
>> sqlite>
>>
>> ___
>> 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] How to select data from 2 lines in one line?

[Mário Anselmo Scandelari Bussmann]

Bingo! This works very well (a little bit slow, since I have a 30 rows).
Thank you all!!!

On Wed, Aug 19, 2009 at 11:42 AM, John Machin  wrote:

> On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote:
> > I have a table like this:
> >
> > petr4
> > ---
> > rowid|data|preabe|premax|premin|preult|voltot
> > 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> > 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
> [snip]
> > 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> > 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
> >
> > I need a select that returns data,preult,previous data and previous
> preult:
> >
> > 2007-01-03|48.7|2007-01-02|50.45
> > 2007-01-04|47.65|2007-01-03|48.7
> [snip]
> > 2007-01-12|45.15|2007-01-11|45.21
> > 2007-01-15|44.89|2007-01-12|45.15
> >
> > How can I do that using only sql (no python, c or perl, no cursor)?
>
> No Python? How cruel :-)
>
> This works but you'd better have an index on 'data', and it looks like
> at least O(N**2) OTTOMH:
>
> sqlite> create table x (data,preabe,premax,premin,preult,voltot);
> sqlite> insert into x values
> ('2007-01-02',50.0,50.45,49.76,50.45,256115409.0);
> /* etc etc*/
> sqlite> select a.data, a.preult, b.data, b.preult from x a, x b
>...> where b.data = (select max(c.data) from x c where c.data < a.data)
>...> order by a.data;
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
> [snip]
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15
> sqlite>
>
> ___
> 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] How to select data from 2 lines in one line?

[Kit]

Right form (tested):

SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
previous_data,temp.preult AS previous_preult
   FROM petr4,petr4 AS temp
   WHERE petr4.rowid=temp.rowid+1;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[John Machin]

On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote:
> I have a table like this:
> 
> petr4
> ---
> rowid|data|preabe|premax|premin|preult|voltot
> 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
[snip]
> 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
> 
> I need a select that returns data,preult,previous data and previous preult:
> 
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
[snip]
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15
> 
> How can I do that using only sql (no python, c or perl, no cursor)?

No Python? How cruel :-)

This works but you'd better have an index on 'data', and it looks like 
at least O(N**2) OTTOMH:

sqlite> create table x (data,preabe,premax,premin,preult,voltot);
sqlite> insert into x values 
('2007-01-02',50.0,50.45,49.76,50.45,256115409.0);
/* etc etc*/
sqlite> select a.data, a.preult, b.data, b.preult from x a, x b
...> where b.data = (select max(c.data) from x c where c.data < a.data)
...> order by a.data;
2007-01-03|48.7|2007-01-02|50.45
2007-01-04|47.65|2007-01-03|48.7
[snip]
2007-01-12|45.15|2007-01-11|45.21
2007-01-15|44.89|2007-01-12|45.15
sqlite>

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

Re: [sqlite] How to select data from 2 lines in one line?

[Kit]

2009/8/19 Mário Anselmo Scandelari Bussmann :
> I have a table like this:
>
> petr4
> ---
> rowid|data|preabe|premax|premin|preult|voltot
> 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
> 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0
> 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0
> 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0
> 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0
> 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0
> 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0
> 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
>
> I need a select that returns data,preult,previous data and previous preult:
>
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
> 2007-01-05|46.19|2007-01-04|47.65
> 2007-01-08|46.59|2007-01-05|46.19
> 2007-01-09|45.52|2007-01-08|46.59
> 2007-01-10|45.25|2007-01-09|45.52
> 2007-01-11|45.21|2007-01-10|45.25
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15

SELECT data,preult,temp.data,temp.preult FROM petr4,petr4 AS temp
WHERE petr4.rowid=temp.rowid-1;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[Pavel Ivanov]

Without looking at your select statement it's very hard to help. But
general suggestion is insert your results into temporary table and
then issue a select on that table joined with itself with condition
like t.rowid = prev.rowid + 1.

Pavel

On Wed, Aug 19, 2009 at 10:10 AM, Mário Anselmo Scandelari
Bussmann wrote:
> I have a table like this:
>
> petr4
> ---
> rowid|data|preabe|premax|premin|preult|voltot
> 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
> 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0
> 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0
> 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0
> 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0
> 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0
> 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0
> 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
>
> I need a select that returns data,preult,previous data and previous preult:
>
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
> 2007-01-05|46.19|2007-01-04|47.65
> 2007-01-08|46.59|2007-01-05|46.19
> 2007-01-09|45.52|2007-01-08|46.59
> 2007-01-10|45.25|2007-01-09|45.52
> 2007-01-11|45.21|2007-01-10|45.25
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15
>
> How can I do that using only sql (no python, c or perl, no cursor)?
> ___
> 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] How to select data from 2 lines in one line?

[Mário Anselmo Scandelari Bussmann]

I have a table like this:

petr4
---
rowid|data|preabe|premax|premin|preult|voltot
1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0
4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0
5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0
6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0
7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0
8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0
9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0

I need a select that returns data,preult,previous data and previous preult:

2007-01-03|48.7|2007-01-02|50.45
2007-01-04|47.65|2007-01-03|48.7
2007-01-05|46.19|2007-01-04|47.65
2007-01-08|46.59|2007-01-05|46.19
2007-01-09|45.52|2007-01-08|46.59
2007-01-10|45.25|2007-01-09|45.52
2007-01-11|45.21|2007-01-10|45.25
2007-01-12|45.15|2007-01-11|45.21
2007-01-15|44.89|2007-01-12|45.15

How can I do that using only sql (no python, c or perl, no cursor)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA locking_mode could stand a rewrite

[Pavel Ivanov]

>   1. When is a lock released during a SELECT? Is it after the first
>  call to step()? The last call to step()? The call to finalize()?
>  Yes, I'm using the C API.

Lock is released during the call to finalize() or reset().

>   2. If while in the step() process of a SELECT, there is a change made
>  to the db, will those changes affect SELECT's result set? In MySQL
>  I'm used to the result set of a SELECT reflecting the state of a
>  db at an instant, and not subject to change by any subsequent
>  INSERTs and UPDATEs, but I don't know if it's the same w/SQLite.

There cannot be any changes in database while you're in process of
stepping through select statement. Of course unless you're doing
changes through the same SQLite connection.

>   3. If I have a permanent write lock, and I go to
>  "locking_mode=NORMAL" will a SELECT remove the permanent write lock?

Yes. Documentation seems to clearly state that.

> Will what I'm trying to do work?

Probably not like you intended. There cannot be any INSERT made while
you're doing your d) step, so with high probability e) step will
return no rows. It will return something only if somebody will quick
enough to lock the database for insertion before you began to step
through e).


Pavel

On Wed, Aug 19, 2009 at 9:07 AM, Angus March wrote:
> For one thing, they shouldn't be using the word "exclusive" to mean two
> different things. There's "locking_mode=EXCLUSIVE" meaning "permanent"
> and "exclusive lock" meaning "write lock". At least I think that's what
> they mean.
>    But my problem is understanding exactly when a lock is released
> during a SELECT and if a SELECT will release a write lock after going
> "locking_mode=NORMAL":
>
>   1. When is a lock released during a SELECT? Is it after the first
>      call to step()? The last call to step()? The call to finalize()?
>      Yes, I'm using the C API.
>   2. If while in the step() process of a SELECT, there is a change made
>      to the db, will those changes affect SELECT's result set? In MySQL
>      I'm used to the result set of a SELECT reflecting the state of a
>      db at an instant, and not subject to change by any subsequent
>      INSERTs and UPDATEs, but I don't know if it's the same w/SQLite.
>   3. If I have a permanent write lock, and I go to
>      "locking_mode=NORMAL" will a SELECT remove the permanent write lock?
>
> If the minutia of the above is confusing, here's what I want to do. I
> have a column defined with
>    IsNew INTEGER NOT NULL DEFAULT 1
> INSERTs made to this table do not mention IsNew so the column indicates
> which rows have recently been added. I want to process all the rows in
> the db, then go back and process any rows that were added during the
> first phase of processing. Here's what I imagined doing:
>
> a) PRAGMA locking_mode=EXCLUSIVE;//this is to cause atomicity between b)
> and d)
> b) UPDATE the_table SET IsNew=0;
> c) PRAGMA locking_mode=NORMAL;
> d) SELECT * FROM the_table;//at this point I would want all rows, and
> only those rows, affected in b) to be returned here, INSERTs to
> the_table to be allowed, and those INSERTs *not* to affect the result
> set returned
> e) SELECT * FROM the_table WHERE IsNew=1;//don't worry about locking
> here, I'm going to be performing some extra-SQLite locking to ensure
> that no other operations are performed on the db at this point
>
> Will what I'm trying to do work?
>
> ___
> 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] Huge numbers of savepoints.

[Pavel Ivanov]

But how do you expect your application to deal with restarts and/or OS
crashes? Do you want to still be able to revert to "marks" set in
previous application run or not? And what about accessing to the data
stored between "marks" from other processes?

Pavel

On Wed, Aug 19, 2009 at 4:07 AM, Chris Dew wrote:
> http://www.sqlite.org/lang_savepoint.html
> I'm looking for a datastore with the following properties:
>  * I need to 'mark' the state frequently (sub second interval).
>  * I need to be able to revert the datastore to a previous mark (with no
> appreciable delay).
>  * I only need to keep the last few hundred 'marks'. (i.e. I never need to
> revert to a datastore marked more than a few minutes ago.)
>
> The savepoint functionality in sqlite looks to almost fit the bill, but its
> savepoints seem to be nested - i.e. you cannot 'forget about' old savepoints
> while retaining recent savepoints. Is my understanding correct here? I'm
> concerned that this would cause a performance issue when millions of nested
> savepoints have accumulated.
>
> Obviously I can roll my own data structure here, but is sqlite was feasible,
> it would be good.
>
> Does anyone have any suggestions?
>
> Thanks,
>
> Chris.
>
> --
>
> http://www.finalcog.com/
> ___
> 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] SQL documentation

[Pavel Ivanov]

Personally for me the current documentation style is more
understandable at a glance. Looking at it it's easier for me to
understand the sequence of terms I should use, what can be omitted,
what terms cannot be used together and so on. Old style looks for me
more like list of requirements for programmer who will implement
parser than user-readable documentation...

So apparently it's the matter of taste. :)

And it looks like this documentation style is not proprietary to
SQLite. Look for example here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2201774.
But unfortunately to me this style is not so popular as I've expected:
http://msdn.microsoft.com/en-us/library/ms174979.aspx
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

The only issue on which I can agree with you (and maybe something
should be done about it) is SQLite has no searchable text version
(Oracle has it though also not very convenient I think). I'm not sure
though how severe this issue is.


Pavel

On Wed, Aug 19, 2009 at 3:01 AM, Glenn Maynard wrote:
> SQLite had extremely readable SQL documentation:
> http://www.3rd-impact.net/Document/SQLite/Translation/Current/Original/lang_createtable.html.
>  It was clear and intuitive; I can understand it at a glance.
>
> At some point, it was replaced with
> http://sqlite.org/lang_createtable.html.  It's closer to a rendering
> of a parse tree than human documentation; I find it nearly unreadable.
>  With the text all baked into an image, it's also not searchable, and
> I have to scroll up and down to read what used to fit in half a page.
>
> Is the text SQL documentation available for current SQLite versions?
>
> --
> Glenn Maynard
> ___
> 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] Increment a value if constraint violated

[Pavel Ivanov]

> insert or replace into mytable(id, count)
> values (:id, coalesce((select count from mytable where id=:id), 0) + 1);

I guess it doesn't work as OP intended:

sqlite> select * from t;
id|count
1|1
1|2
1|3
1|4
sqlite> insert or replace into t(id, count) values (1,
coalesce((select count from t where id=1), 0) + 1);
sqlite> select * from t;
id|count
1|1
1|2
1|3
1|4
1|2

But this does:

sqlite> insert or replace into t(id, count) values (1,
coalesce((select count from t where id=1 order by count desc), 0) +
1);
sqlite> select * from t;
id|count
1|1
1|2
1|3
1|4
1|5
sqlite> delete from t;
sqlite> insert or replace into t(id, count) values (1,
coalesce((select count from t where id=1 order by count desc), 0) +
1);
sqlite> select * from t;
id|count
1|1


Pavel

On Wed, Aug 19, 2009 at 12:14 AM, Igor Tandetnik wrote:
> andrew fabbro wrote:
>> What is the most efficient way to code either a trigger or the app so
>> that it increments a "count" field if the "id" field exists?
>>
>> For example, say you have a table with fields of "id" and "count".
>>
>> First row is an id of 3, so "count" is set to 1.
>> Next row is an id of 4, so "count" is set to 1.
>> Third row is an id of 3 again, so "count" is incremented to 2.
>
> insert or replace into mytable(id, count)
> values (:id, coalesce((select count from mytable where id=:id), 0) + 1);
>
> 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

[sqlite] PRAGMA locking_mode could stand a rewrite

[Angus March]

For one thing, they shouldn't be using the word "exclusive" to mean two
different things. There's "locking_mode=EXCLUSIVE" meaning "permanent"
and "exclusive lock" meaning "write lock". At least I think that's what
they mean.
But my problem is understanding exactly when a lock is released
during a SELECT and if a SELECT will release a write lock after going
"locking_mode=NORMAL":

   1. When is a lock released during a SELECT? Is it after the first
  call to step()? The last call to step()? The call to finalize()?
  Yes, I'm using the C API.
   2. If while in the step() process of a SELECT, there is a change made
  to the db, will those changes affect SELECT's result set? In MySQL
  I'm used to the result set of a SELECT reflecting the state of a
  db at an instant, and not subject to change by any subsequent
  INSERTs and UPDATEs, but I don't know if it's the same w/SQLite.
   3. If I have a permanent write lock, and I go to
  "locking_mode=NORMAL" will a SELECT remove the permanent write lock?

If the minutia of the above is confusing, here's what I want to do. I
have a column defined with
IsNew INTEGER NOT NULL DEFAULT 1
INSERTs made to this table do not mention IsNew so the column indicates
which rows have recently been added. I want to process all the rows in
the db, then go back and process any rows that were added during the
first phase of processing. Here's what I imagined doing:

a) PRAGMA locking_mode=EXCLUSIVE;//this is to cause atomicity between b)
and d)
b) UPDATE the_table SET IsNew=0;
c) PRAGMA locking_mode=NORMAL;
d) SELECT * FROM the_table;//at this point I would want all rows, and
only those rows, affected in b) to be returned here, INSERTs to
the_table to be allowed, and those INSERTs *not* to affect the result
set returned
e) SELECT * FROM the_table WHERE IsNew=1;//don't worry about locking
here, I'm going to be performing some extra-SQLite locking to ensure
that no other operations are performed on the db at this point

Will what I'm trying to do work?

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

Re: [sqlite] Huge numbers of savepoints.

[Dan Kennedy]

On Aug 19, 2009, at 3:07 PM, Chris Dew wrote:

> http://www.sqlite.org/lang_savepoint.html
> I'm looking for a datastore with the following properties:
> * I need to 'mark' the state frequently (sub second interval).
> * I need to be able to revert the datastore to a previous mark (with  
> no
> appreciable delay).
> * I only need to keep the last few hundred 'marks'. (i.e. I never  
> need to
> revert to a datastore marked more than a few minutes ago.)
>
> The savepoint functionality in sqlite looks to almost fit the bill,  
> but its
> savepoints seem to be nested - i.e. you cannot 'forget about' old  
> savepoints
> while retaining recent savepoints. Is my understanding correct here?

That's correct. "Savepoint" is just another name for "nested  
transaction".

Dan.

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

[sqlite] Huge numbers of savepoints.

[Chris Dew]

http://www.sqlite.org/lang_savepoint.html
I'm looking for a datastore with the following properties:
 * I need to 'mark' the state frequently (sub second interval).
 * I need to be able to revert the datastore to a previous mark (with no
appreciable delay).
 * I only need to keep the last few hundred 'marks'. (i.e. I never need to
revert to a datastore marked more than a few minutes ago.)

The savepoint functionality in sqlite looks to almost fit the bill, but its
savepoints seem to be nested - i.e. you cannot 'forget about' old savepoints
while retaining recent savepoints. Is my understanding correct here? I'm
concerned that this would cause a performance issue when millions of nested
savepoints have accumulated.

Obviously I can roll my own data structure here, but is sqlite was feasible,
it would be good.

Does anyone have any suggestions?

Thanks,

Chris.

-- 

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

[sqlite] SQL documentation

[Glenn Maynard]

SQLite had extremely readable SQL documentation:
http://www.3rd-impact.net/Document/SQLite/Translation/Current/Original/lang_createtable.html.
 It was clear and intuitive; I can understand it at a glance.

At some point, it was replaced with
http://sqlite.org/lang_createtable.html.  It's closer to a rendering
of a parse tree than human documentation; I find it nearly unreadable.
 With the text all baked into an image, it's also not searchable, and
I have to scroll up and down to read what used to fit in half a page.

Is the text SQL documentation available for current SQLite versions?

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

[sqlite] SQLite in Citrix Lab Manager

[Ahmed Adil Huq]

I have recently found that Citrix Lab Manager has embeded SQLite database.  
This can be added to bigname users list in the sqlite.org website.

Google cache link:

http://209.85.229.132/search?q=cache:9Xe1I5zCKsQJ:support.citrix.com/article/CTX120223+citrix+lab+manager+sqlite=1=en=clnk=uk


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