date:20081220

Re: [sqlite] Math Update with two tables

2008-12-20 Thread Igor Tandetnik

"aj"  wrote in
message
news:95d3c7350812201131l1cd87998k55595f77fecc0...@mail.gmail.com
> I would like to use math with more then one table, for example
> I know how to do this
>
>
> (Factors & Potential = columns)
>
> UPDATE Records
> SET  Factors= (Factors * Potential)
>
> but i don't know how with an additional table, i came up with
>
>
> UPDATE Records,Table2
> SET  Factors= (Factors * Potential Table2.Field23)

This last statement makes no sense ot me. Could you describe, in plain 
English, what it was supposed to achieve?

Igor Tandetnik 



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

Re: [sqlite] Trigger UPDATE based on a different row

2008-12-20 Thread P Kishor

On 12/20/08, jose isaias cabrera  wrote:
>
>  Greetings!
>
>  Imagine these rows in a table named LSOpenJobs:
>
>  id, PID,subject, bdate, edate, lang,job
>  1, 232,2008-01-01,2008-01-10,es,trans
>  2, 232,2008-01-01,2008-01-10,fr,trans
>  3, 232,2008-01-01,2008-01-10,it,trans
>  4, 232,2008-01-01,2008-01-10,es,val
>  5, 232,2008-01-01,2008-01-10,fr,val
>  6, 232,2008-01-01,2008-01-10,it,val
>
>  What I would like to do is to create a trigger to update the bdate for the
>  'val' job of the same lang and same PID, with the edate of the 'trans' job
>  of same lang and same PID.  For example, in this case above, let's take id
>  1; the bdate for the 'val' job with the 'es' lang with the same PID, id 4,
>  should be updated with the edate of id 1.  So, the trigger should UPDATE the
>  table to this,
>
>  id, PID,subject, bdate, edate, lang,job
>  1, 232,2008-01-01,2008-01-10,es,trans
>  2, 232,2008-01-01,2008-01-10,fr,trans
>  3, 232,2008-01-01,2008-01-10,it,trans
>  4, 232,2008-01-10,2008-01-10,es,val
>  5, 232,2008-01-10,2008-01-10,fr,val
>  6, 232,2008-01-10,2008-01-10,it,val
>
>  The trigger example in the site expects to change the same row.  This UDPATE
>  is based on other rows of the same table and same PID.
>
>  Any help is greatly appreciated.
>


José,

Consider the following

[04:43 PM] ~$sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> CREATE TABLE foo (id, PID, bdate, edate, lang,job);
sqlite> INSERT INTO foo VALUES (1, 232,'2008-01-01','2008-01-10','es','trans');
sqlite> INSERT INTO foo VALUES (2, 232,'2008-01-01','2008-01-10','fr','trans');
sqlite> INSERT INTO foo VALUES (3, 232,'2008-01-01','2008-01-10','it','trans');
sqlite> INSERT INTO foo VALUES (4, 232,'2008-01-01','2008-01-10','es','val');
sqlite> INSERT INTO foo VALUES (5, 232,'2008-01-01','2008-01-10','fr','val');
sqlite> INSERT INTO foo VALUES (6, 232,'2008-01-01','2008-01-10','it','val');
sqlite> UPDATE foo SET bdate = (SELECT f2.edate FROM foo f1 JOIN foo
f2 ON f1.PID = f2.PID AND f1.lang = f2.lang WHERE f1.job = 'val' AND
f2.job = 'trans' AND f1.id = foo.id) WHERE foo.job = 'val';
sqlite> SELECT * FROM foo;
1|232|2008-01-01|2008-01-10|es|trans
2|232|2008-01-01|2008-01-10|fr|trans
3|232|2008-01-01|2008-01-10|it|trans
4|232|2008-01-10|2008-01-10|es|val
5|232|2008-01-10|2008-01-10|fr|val
6|232|2008-01-10|2008-01-10|it|val
sqlite>

The UPDATE statement above seems to do what you want. Convert that to
a TRIGGER if you so want, but realize that the TRIGGER is supposed to,
well, trigger on some event such as UPDATE or INSERT or DELETE. Other
than that, the above should get you going.


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sharing a database / Replication

2008-12-20 Thread Kees Nuyt

On Sat, 20 Dec 2008 07:03:48 -0500, Simon
 wrote in General Discussion of SQLite
Database :

>> Use transactions, ref:
>> http://www.sqlite.org/lang_transaction.html
>> and program proper lock/error handling. The archives of this
>> mailing list contain several good examples.
>
>Yes, ok, I'll have to work that way... 
>I believe there should also be some time spent 
>on a good design for these locks... 
>like to avoid congestion.  

SQLite does the locking for you, you just have to handle the
locked or busy status.

>But it'll be on my own for that one, shouldn't be difficult
>and it's not sqlite specific anyway. 
>(However, i you do have a good url to such design, please tell me!)

Just browse the two or three most recent weeks in the
mailing list archives, then get your hands dirty using the
SQL language- and C interface docs as a reference. 

>> You can ATTACH a second database (actually several databases
>> at the same time) to the same process. Then CREATE your
>> tables and use INSERT INTO  (SELECT FROM ...) syntax to
>> populate tables in one database with the contents of another
>> one.
>> http://www.sqlite.org/lang_insert.html
>> 
>> You even can CREATE tables semi-automatically using the
>> CREATE TABLE  SELECT ... syntax, but this has the
>> disadvantage of not creating any indexes.
>> http://www.sqlite.org/lang_createtable.html
>
>I've read on ATTACH and it does seem to help a lot for my project.  But if I 
>understand right, if I'm copying data from db1 to db2 and the data is actually 
>a 
>blob...  I would have to make my SQL QUERY, then bind the data in db1 to the 
>query and then step through to actually copy it...  right?  The same method 
>could be used regardless of datatype actually to make something simple.

There is not much need to prepare/bind/step in this case,
because you just pump data from one table into another one,
without the data being returned to your application.

Most you need to know about locking is found in:
http://www.sqlite.org/lang_transaction.html

sqlite3_exec() these statements one by one:
ATTACH 'filename' as db2;
BEGIN IMMEDIATE; -- or EXCLUSIVE
(error handling/retry) 

-- this assumes table1 has the exact 
-- same definition in both db1 and db2
INSERT INTO db1.table1 SELECT * FROM db2.table1;
(error handling)

COMMIT; -- or ROLLBACK


>Thanks,
>   Simon
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger UPDATE based on a different row

2008-12-20 Thread jose isaias cabrera


Greetings!

Imagine these rows in a table named LSOpenJobs:

id, PID,subject, bdate, edate, lang,job
1, 232,2008-01-01,2008-01-10,es,trans
2, 232,2008-01-01,2008-01-10,fr,trans
3, 232,2008-01-01,2008-01-10,it,trans
4, 232,2008-01-01,2008-01-10,es,val
5, 232,2008-01-01,2008-01-10,fr,val
6, 232,2008-01-01,2008-01-10,it,val

What I would like to do is to create a trigger to update the bdate for the 
'val' job of the same lang and same PID, with the edate of the 'trans' job 
of same lang and same PID.  For example, in this case above, let's take id 
1; the bdate for the 'val' job with the 'es' lang with the same PID, id 4, 
should be updated with the edate of id 1.  So, the trigger should UPDATE the 
table to this,

id, PID,subject, bdate, edate, lang,job
1, 232,2008-01-01,2008-01-10,es,trans
2, 232,2008-01-01,2008-01-10,fr,trans
3, 232,2008-01-01,2008-01-10,it,trans
4, 232,2008-01-10,2008-01-10,es,val
5, 232,2008-01-10,2008-01-10,fr,val
6, 232,2008-01-10,2008-01-10,it,val

The trigger example in the site expects to change the same row.  This UDPATE 
is based on other rows of the same table and same PID.

Any help is greatly appreciated.

thanks,

josé 

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

[sqlite] Math Update with two tables

2008-12-20 Thread aj

I would like to use math with more then one table, for example
I know how to do this


(Factors & Potential = columns)

UPDATE Records
SET  Factors= (Factors * Potential)

but i don't know how with an additional table, i came up with


UPDATE Records,Table2
SET  Factors= (Factors * Potential Table2.Field23)

Any help is greatly appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] confusing with how to to this in sqlite

2008-12-20 Thread P Kishor

On 12/20/08, Simon Davies  wrote:
> 2008/12/20 Rachmat Febfauza :
>  >
>
> > i have to change 'FOOD ' to 'FOOD', but the result is not that i hope.
>  >
>  > Rachmat:  If what Simon says is correct (and I have no reason to doubt
>  > it) you might also get your query to work by specifying a collating
>  > sequence of RTRIM on the category column.  
> http://www.sqlite.org/datatype3.html#collation
>  >
>  > i also spesifiy collate in create table awal1 and akhir1 like this :
>  >
>  > CREATE TABLE awal1(Code char(5) collate RTRIM,Level varchar(8) collate 
> RTRIM, Category varchar(50) collate RTRIM, Product varchar(60) collate RTRIM, 
> Location varchar(50) collate RTRIM, "Begin" datetime);
>  >
>  > CREATE TABLE akhir1(Code char(5) collate RTRIM,Level varchar(8) collate 
> RTRIM,Category varchar(50) collate RTRIM,Product varchar(60) collate RTRIM, 
> Location varchar(50) collate RTRIM,"End" datetime);
>  >
>  > but the result is not my hope.
>  >
>  > the results are :
>  >
>  > A1236|MEDIUM|FOOD|SNACK|HOMS 1|2007-05-06 10:48:57|2007-05-06 11:19:25|1828
>  > A1236|MEDIUM|FOOD|SNACK|HOMS 2|2007-05-06 10:48:57|2007-05-06 11:19:21|1824
>  > A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:20:34|2007-05-06 11:42:46|1332
>  > A1222|SMALL|FOOD|WAFER|HOMS 2|2007-05-06 11:20:34|2007-05-06 11:31:57|683
>  > A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649
>  > A1221|SMALL|FOOD|CAKE|HOMS 2|2007-05-06 11:31:57|2007-05-06 11:31:57|0
>  > A1220|SMALL|FOOD|MARGARINE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
>  > A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
>  > A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
>  > A1269|SMALL|CLOTHES|BELT|HOMS 3|2007-05-07 17:28:25|2007-05-07 17:28:27|2
>  >
>  > take a look at this different
>  > A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 
> (this is done with sqlite)
>  >
>  > A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
> 11:31:570 (this is done with mysql)
>  >
>  > how to solve this?
>
>
> It looks like you are using the sqlite3 shell, so experiment with .separator
>
>  Have you used .help?
>


seems like Rachmat is having a problem with the time difference being
calculated with

strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference
from awal1

MySQL is giving 0 for A1221 while SQLite is giving 649.

Rachmat, you are specifying

akhir1."End" >= awal1."Begin"

in your WHERE clause, so why are you surprised at getting the following row?

A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649

The above row satisfies your constraint. Change the constraint to

akhir1."End" = awal1."Begin"

and you will get only the following row in your result

A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:31:57|0

just like in MySQL


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


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] confusing with how to to this in sqlite

2008-12-20 Thread Simon Davies

2008/12/20 Rachmat Febfauza :
>
> i have to change 'FOOD ' to 'FOOD', but the result is not that i hope.
>
> Rachmat:  If what Simon says is correct (and I have no reason to doubt
> it) you might also get your query to work by specifying a collating
> sequence of RTRIM on the category column.  
> http://www.sqlite.org/datatype3.html#collation
>
> i also spesifiy collate in create table awal1 and akhir1 like this :
>
> CREATE TABLE awal1(Code char(5) collate RTRIM,Level varchar(8) collate RTRIM, 
> Category varchar(50) collate RTRIM, Product varchar(60) collate RTRIM, 
> Location varchar(50) collate RTRIM, "Begin" datetime);
>
> CREATE TABLE akhir1(Code char(5) collate RTRIM,Level varchar(8) collate 
> RTRIM,Category varchar(50) collate RTRIM,Product varchar(60) collate RTRIM, 
> Location varchar(50) collate RTRIM,"End" datetime);
>
> but the result is not my hope.
>
> the results are :
>
> A1236|MEDIUM|FOOD|SNACK|HOMS 1|2007-05-06 10:48:57|2007-05-06 11:19:25|1828
> A1236|MEDIUM|FOOD|SNACK|HOMS 2|2007-05-06 10:48:57|2007-05-06 11:19:21|1824
> A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:20:34|2007-05-06 11:42:46|1332
> A1222|SMALL|FOOD|WAFER|HOMS 2|2007-05-06 11:20:34|2007-05-06 11:31:57|683
> A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649
> A1221|SMALL|FOOD|CAKE|HOMS 2|2007-05-06 11:31:57|2007-05-06 11:31:57|0
> A1220|SMALL|FOOD|MARGARINE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
> A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
> A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
> A1269|SMALL|CLOTHES|BELT|HOMS 3|2007-05-07 17:28:25|2007-05-07 17:28:27|2
>
> take a look at this different
> A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 
> (this is done with sqlite)
>
> A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
> 11:31:570 (this is done with mysql)
>
> how to solve this?

It looks like you are using the sqlite3 shell, so experiment with .separator

Have you used .help?

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

Re: [sqlite] Sharing a database / Replication

2008-12-20 Thread Simon

> Use transactions, ref:
> http://www.sqlite.org/lang_transaction.html
> and program proper lock/error handling. The archives of this
> mailing list contain several good examples.

Yes, ok, I'll have to work that way...  I believe there should also be some 
time 
spent on a good design for these locks... like to avoid congestion.  But it'll 
be on my own for that one, shouldn't be difficult and it's not sqlite specific 
anyway. (However, i you do have a good url to such design, please tell me!)

> You can ATTACH a second database (actually several databases
> at the same time) to the same process. Then CREATE your
> tables and use INSERT INTO  (SELECT FROM ...) syntax to
> populate tables in one database with the contents of another
> one.
> http://www.sqlite.org/lang_insert.html
> 
> You even can CREATE tables semi-automatically using the
> CREATE TABLE  SELECT ... syntax, but this has the
> disadvantage of not creating any indexes.
> http://www.sqlite.org/lang_createtable.html

I've read on ATTACH and it does seem to help a lot for my project.  But if I 
understand right, if I'm copying data from db1 to db2 and the data is actually 
a 
blob...  I would have to make my SQL QUERY, then bind the data in db1 to the 
query and then step through to actually copy it...  right?  The same method 
could be used regardless of datatype actually to make something simple.

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

Re: [sqlite] Math Update with two tables

Re: [sqlite] Trigger UPDATE based on a different row

Re: [sqlite] Sharing a database / Replication

[sqlite] Trigger UPDATE based on a different row

[sqlite] Math Update with two tables

Re: [sqlite] confusing with how to to this in sqlite

Re: [sqlite] confusing with how to to this in sqlite

Re: [sqlite] Sharing a database / Replication

8 matches

Site Navigation

Mail list logo

Footer information