Re: [sqlite] Updating Local and SharedDBs
"jose isaias cabrera" wrote...
>
> Greetings. My apologies for the lengthiness...
>
> We are running an utility with about 5 clients using two DBs:
> 1. PrimaryPC
> 2. Shared folder
>
> The PrimaryPC contains the original data and changes, while the SharedDB
> in
> the Shared Folder is the one that provides original indexing of those
> records. There are no duplicate indexes. When a new records is open, the
> SharedDB provides the index and so, no two users will ever have the same
> record and will never update the same record.
>
> The SharedDB is the one that provides information about other users'
> records
> to the other folks and so, an UpdateDB function was created to push
> updates
> to the SharedDB from the PrimaryPC.
>
> I will throw one more wrench in the motor... We have SharedPCs that when
> users login will bring the data of that users from the SharedDB to this
> SharedPC and thus the user can see his/her records and work without any
> problem. However, the UpdateDB function does not work from a SharedPC
> because it is not the PrimaryPC of the user.
>
> The Update was working fine (and works fine this way) because I would
> attach
> to the SharedDB and push all the records of the user to the SharedDB.
> But,
> as the number of records grew, the it would take 20-30 seconds and
> sometimes
> we find ourselves locking the SharedDB longer than needed.
>
> I added a new column, XtraB, (Don-t ask), which changes every time a
> record
> is edited and saved with the value '-mm-dd hh:mm:ss'. I am trying to
> change the UpdateDB function to only push the records that have been saved
> in the PrimaryPC. Here is my programming steps that are not working:
>
> dba = new SqliteDatabase(sqldba); //connect to the SharedDB. Works. :-)
>
> q = "ATTACH '" ~ sqldb ~ "' AS client; "; // sqldb is the path to the
> local
> PC. Works.
>
> q = "BEGIN;";
> q ~= "REPLACE INTO LSOpenProjects
> SELECT * FROM client.LSOpenProjects
>WHERE login)='" ~ pm["login"] ~ "' AND XtraB < client.XtraB; ";
> q ~= "COMMIT;";
>
> The above is not working... Yes, I am a newbie. Well, kinda,
> nonetheless,
> yes.
>
> What I would like to do is to push all the records that have a higher date
> ('-mm-dd hh:mm:ss') in the local DB than the SharedDB. They should
> really be equal, if no changes have been made.
>
> And then, the wrench in the motor is to bring from the sharedDB to the
> SharedPC the records that are also with newer dates then the previously
> brought from the SharedDB. I have something like above,
>
>SqliteDatabase dbu = new SqliteDatabase(sqldb);
>
>q = "ATTACH '" ~ sqldba ~ "' AS c; "; // works
>
>q = "BEGIN;";
>q ~= "INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM c.LSOpenProjects
>WHERE login='" ~ pm["login"] ~ "' AND c.XtraB > XtraB; ";
>q ~= "COMMIT;";
>
> Not working either. Any help would be greatly appreciated.
>
> thanks,
>
> josé
Never mind about this... I worked around it.
thanks,
josé
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT : Seems don't work
On Feb 25, 2009, at 2:31 AM, REPKA_Maxime_NeufBox wrote: > I try for test as follow : > > on DOS : sqlite3.exe Database > > then type : SAVEPOINT spoint; > Error message is : 'SQL error :near "savepoint": syntax error' Probably you need to upgrade. SAVEPOINT first appeared in 3.6.8. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: no such function: replace
Ah... its version 3.3.5 Unfortunately though I don't have the option of updating it, since I am using it on a very widespread set of machines which I don't have root privileges on... Is there another way I can achieve the same effect without this function? Thankyou both for your awesomely fast replies by the way - really appreciated. Ben John Machin wrote: > > On 25/02/2009 1:13 PM, BenJones12345 wrote: >> Hi all >> >> I'm very much a beginner with sqlite3 and and I'm completely stumped with >> using the replace function. >> >> What I have is a field (TheOldField) with values like: >> >> HM1 >> HP4 >> HM3 >> HM2 >> >> and I need to replace all "P" with "+" and all "M" with "-", and put the >> result into a new field (TheNewField). All the documentation everywhere >> suggests I should use the replace function. So what I'm trying to do is >> (just for the P's at first): >> >> UPDATE TheTable >> set TheNewField = replace(TheOldField, "P", "+"); >> >> but doing this I get the error message >> >> "SQL error: no such function: replace" >> >> Which I dont understand. Can anybody tell me what I'm doing wrong or >> offer >> an alternative solution? > > Check your SQLite3 version. > Latest release in 3.6.11. > Latest I have is 3.6.10 which includes replace() > However an old 3.3.6 command-line executable reproduces your problem. > > HTH, > John > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/SQL-error%3A-no-such-function%3A-replace-tp22194822p22195132.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: no such function: replace
On 25/02/2009 1:13 PM, BenJones12345 wrote: > Hi all > > I'm very much a beginner with sqlite3 and and I'm completely stumped with > using the replace function. > > What I have is a field (TheOldField) with values like: > > HM1 > HP4 > HM3 > HM2 > > and I need to replace all "P" with "+" and all "M" with "-", and put the > result into a new field (TheNewField). All the documentation everywhere > suggests I should use the replace function. So what I'm trying to do is > (just for the P's at first): > > UPDATE TheTable > set TheNewField = replace(TheOldField, "P", "+"); > > but doing this I get the error message > > "SQL error: no such function: replace" > > Which I dont understand. Can anybody tell me what I'm doing wrong or offer > an alternative solution? Check your SQLite3 version. Latest release in 3.6.11. Latest I have is 3.6.10 which includes replace() However an old 3.3.6 command-line executable reproduces your problem. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: no such function: replace
On Tue, Feb 24, 2009 at 8:13 PM, BenJones12345
wrote:
>
> Hi all
>
> I'm very much a beginner with sqlite3 and and I'm completely stumped with
> using the replace function.
>
> What I have is a field (TheOldField) with values like:
>
> HM1
> HP4
> HM3
> HM2
>
> and I need to replace all "P" with "+" and all "M" with "-", and put the
> result into a new field (TheNewField). All the documentation everywhere
> suggests I should use the replace function. So what I'm trying to do is
> (just for the P's at first):
>
> UPDATE TheTable
> set TheNewField = replace(TheOldField, "P", "+");
>
> but doing this I get the error message
>
> "SQL error: no such function: replace"
>
> Which I dont understand. Can anybody tell me what I'm doing wrong or offer
> an alternative solution?
>
dunno, but it works for me. In any case, do use single quotes for literals.
sqlite> CREATE TABLE foo (a, b);
sqlite> INSERT INTO foo (a) VALUES ('HM1');
sqlite> INSERT INTO foo (a) VALUES ('HP4');
sqlite> INSERT INTO foo (a) VALUES ('HM3');
sqlite> INSERT INTO foo (a) VALUES ('HM2');
sqlite> .m col
sqlite> .h on
sqlite> SELECT * FROM foo;
a b
-- --
HM1
HP4
HM3
HM2
sqlite> UPDATE foo SET b = Replace(a, 'P', '+') WHERE a LIKE '%P%';
sqlite> UPDATE foo SET b = Replace(a, 'M', '-') WHERE a LIKE '%M%';
sqlite> SELECT * FROM foo;
a b
-- --
HM1 H-1
HP4 H+4
HM3 H-3
HM2 H-2
--
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/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL error: no such function: replace
Hi all I'm very much a beginner with sqlite3 and and I'm completely stumped with using the replace function. What I have is a field (TheOldField) with values like: HM1 HP4 HM3 HM2 and I need to replace all "P" with "+" and all "M" with "-", and put the result into a new field (TheNewField). All the documentation everywhere suggests I should use the replace function. So what I'm trying to do is (just for the P's at first): UPDATE TheTable set TheNewField = replace(TheOldField, "P", "+"); but doing this I get the error message "SQL error: no such function: replace" Which I dont understand. Can anybody tell me what I'm doing wrong or offer an alternative solution? Thanks, Ben -- View this message in context: http://www.nabble.com/SQL-error%3A-no-such-function%3A-replace-tp22194822p22194822.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...
On 25/02/2009 11:34 AM, P Kishor wrote:
> On Tue, Feb 24, 2009 at 6:12 PM, John Machin wrote:
>> On 25/02/2009 10:30 AM, P Kishor wrote:
>>> On Tue, Feb 24, 2009 at 5:19 PM, Leo Freitag
>>> wrote:
Hallo,
I'm trying to insert the highest value of tblName into tblZO.
There fore I followed the hints in this article ...
http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--td19085514.html#a19085514
... but I got a error (see below)
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> create 'tblName' ('id' integer primary key, 'text' text);
SQL error: near "'tblName'": syntax error
sqlite> create table 'tblName' ('id' integer primary key, 'text' text);
sqlite> insert into 'tblName' Values (NULL, 'one');
sqlite> insert into 'tblName' Values (NULL, 'two');
sqlite> insert into 'tblName' Values (NULL, 'three');
sqlite> insert into 'tblName' Values (NULL, 'four');
sqlite> insert into 'tblName' Values (NULL, 'five');
sqlite> create table 'tblRefMaxName' ('ref' integer, 'nn' text);
sqlite> insert into 'tblRefMaxName' Values (select max(id) from tblName,
'eins')
>>> select max(id) from tblName, 'eins' is not a valid select statement.
>> That is not the problem. Consider this:
>
> actually it is the problem. I wasn't clear in my explanation so that
> is my fault.
>
> the following is a valid statement: SELECT Max(id) FROM tbl
>
> but the following is problematic: SELECT Max(id) FROM tbl, 'eins'
>
> because the SQL parser is looking for a table called 'eins'
I don't believe it is looking for a table at all. After parsing "VALUES"
and "(", the next construct expected is an "expr". The next available
token is the keyword "SELECT". Look through the railroad syntax diagram
for "expr". None of the possibilities can start with a keyword "SELECT".
A sensibly written parser can stop right there, and emit an error
message, something like ... it
is not going to try mucking about parsing a full select statement when
one can not legally exist at that position.
I can't imagine ever having to suspect that any part of SQLite is not
sensibly written :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a question on the callback function's return values of sqlite3_exec()
Thank you very much! this is my first time to use sqlite3. using prepared statements is a little difficult. Could I avert to another way(more simple way) to achieve the same thing? Or where could I get some example codes on the method of using prepared statements? Igor Tandetnik wrote: > > "liubin liu" <7101...@sina.com> wrote in message > news:22176984.p...@talk.nabble.com >> the question is on the callback function's return values of >> sqlite3_exec() >> >> when using sqlite3_exec() to do "select * from ...", how to get all >> the return values by using the callback function? >> >> it could print the result, but couldn't return the values. If do like >> so, just one value could be get. >> how to get all the values? > > Memory allocated for value[] strings is valid only inside the callback. > It is deallocated or reused as soon as the callback returns. So, you > can't just store a pointer you receive - it'll soon become invalid. You > need to allocate your own memory and make a copy of string contents. > > Also, consider using prepared statements instead of sqlite3_exec - see > sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_column_*. For > one thing, values of numeric fields could be retrieved directly as > integers, rather than converted to strings and then converted back. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/a-question-on-the-callback-function%27s-return-values-of-sqlite3_exec%28%29-tp22176984p22194312.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...
On Tue, Feb 24, 2009 at 6:12 PM, John Machin wrote:
> On 25/02/2009 10:30 AM, P Kishor wrote:
>>
>> On Tue, Feb 24, 2009 at 5:19 PM, Leo Freitag
>> wrote:
>>>
>>> Hallo,
>>>
>>> I'm trying to insert the highest value of tblName into tblZO.
>>>
>>> There fore I followed the hints in this article ...
>>>
>>> http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--td19085514.html#a19085514
>>>
>>> ... but I got a error (see below)
>>>
>>> SQLite version 3.3.13
>>> Enter ".help" for instructions
>>> sqlite> create 'tblName' ('id' integer primary key, 'text' text);
>>> SQL error: near "'tblName'": syntax error
>>> sqlite> create table 'tblName' ('id' integer primary key, 'text' text);
>>> sqlite> insert into 'tblName' Values (NULL, 'one');
>>> sqlite> insert into 'tblName' Values (NULL, 'two');
>>> sqlite> insert into 'tblName' Values (NULL, 'three');
>>> sqlite> insert into 'tblName' Values (NULL, 'four');
>>> sqlite> insert into 'tblName' Values (NULL, 'five');
>>> sqlite> create table 'tblRefMaxName' ('ref' integer, 'nn' text);
>>> sqlite> insert into 'tblRefMaxName' Values (select max(id) from tblName,
>>> 'eins')
>>
>> select max(id) from tblName, 'eins' is not a valid select statement.
>
> That is not the problem. Consider this:
actually it is the problem. I wasn't clear in my explanation so that
is my fault.
the following is a valid statement: SELECT Max(id) FROM tbl
but the following is problematic: SELECT Max(id) FROM tbl, 'eins'
because the SQL parser is looking for a table called 'eins'
Wrapping the first part in parens removes that ambiguity.
>
> sqlite> insert into 'tblRefMaxName' Values (NULL, select max(text) from
> tblName);
> SQL error: near "select": syntax error
>
> select max(text) from tblName *IS* a valid select statement. Having a valid
> select statement is of course necessary but it is not sufficient. At this
> stage an expr is expected, and the only way the syntax railroad diagram
> allows you to get a select statement in there is:
> --- ( select-stmt ) ---
>
> i.e. like this:
>
> sqlite> insert into 'tblRefMaxName' Values (NULL, (select max(text) from
> tblName));
> sqlite> select * from tblRefMaxName;
> 5|eins
> |two
> sqlite>
>
> Note that if the table had only one column you would still need the
> parentheses:
>
> insert into 'tblRefMaxName' Values ((select max(text) from tblName));
>
> GENERAL RULE: always wrap an inner select in parentheses, whether it's being
> used as an expression or as a join-source.
>
> HTH,
> John
>
--
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/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...
On 25/02/2009 10:30 AM, P Kishor wrote:
> On Tue, Feb 24, 2009 at 5:19 PM, Leo Freitag wrote:
>> Hallo,
>>
>> I'm trying to insert the highest value of tblName into tblZO.
>>
>> There fore I followed the hints in this article ...
>> http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--td19085514.html#a19085514
>>
>> ... but I got a error (see below)
>>
>> SQLite version 3.3.13
>> Enter ".help" for instructions
>> sqlite> create 'tblName' ('id' integer primary key, 'text' text);
>> SQL error: near "'tblName'": syntax error
>> sqlite> create table 'tblName' ('id' integer primary key, 'text' text);
>> sqlite> insert into 'tblName' Values (NULL, 'one');
>> sqlite> insert into 'tblName' Values (NULL, 'two');
>> sqlite> insert into 'tblName' Values (NULL, 'three');
>> sqlite> insert into 'tblName' Values (NULL, 'four');
>> sqlite> insert into 'tblName' Values (NULL, 'five');
>> sqlite> create table 'tblRefMaxName' ('ref' integer, 'nn' text);
>> sqlite> insert into 'tblRefMaxName' Values (select max(id) from tblName,
>> 'eins')
>
> select max(id) from tblName, 'eins' is not a valid select statement.
That is not the problem. Consider this:
sqlite> insert into 'tblRefMaxName' Values (NULL, select max(text) from
tblName);
SQL error: near "select": syntax error
select max(text) from tblName *IS* a valid select statement. Having a
valid select statement is of course necessary but it is not sufficient.
At this stage an expr is expected, and the only way the syntax railroad
diagram allows you to get a select statement in there is:
--- ( select-stmt ) ---
i.e. like this:
sqlite> insert into 'tblRefMaxName' Values (NULL, (select max(text) from
tblName));
sqlite> select * from tblRefMaxName;
5|eins
|two
sqlite>
Note that if the table had only one column you would still need the
parentheses:
insert into 'tblRefMaxName' Values ((select max(text) from tblName));
GENERAL RULE: always wrap an inner select in parentheses, whether it's
being used as an expression or as a join-source.
HTH,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Updating Local and SharedDBs
Greetings. My apologies for the lengthiness...
We are running an utility with about 5 clients using two DBs:
1. PrimaryPC
2. Shared folder
The PrimaryPC contains the original data and changes, while the SharedDB in
the Shared Folder is the one that provides original indexing of those
records. There are no duplicate indexes. When a new records is open, the
SharedDB provides the index and so, no two users will ever have the same
record and will never update the same record.
The SharedDB is the one that provides information about other users' records
to the other folks and so, an UpdateDB function was created to push updates
to the SharedDB from the PrimaryPC.
I will throw one more wrench in the motor... We have SharedPCs that when
users login will bring the data of that users from the SharedDB to this
SharedPC and thus the user can see his/her records and work without any
problem. However, the UpdateDB function does not work from a SharedPC
because it is not the PrimaryPC of the user.
The Update was working fine (and works fine this way) because I would attach
to the SharedDB and push all the records of the user to the SharedDB. But,
as the number of records grew, the it would take 20-30 seconds and sometimes
we find ourselves locking the SharedDB longer than needed.
I added a new column, XtraB, (Don-t ask), which changes every time a record
is edited and saved with the value '-mm-dd hh:mm:ss'. I am trying to
change the UpdateDB function to only push the records that have been saved
in the PrimaryPC. Here is my programming steps that are not working:
dba = new SqliteDatabase(sqldba); //connect to the SharedDB. Works. :-)
q = "ATTACH '" ~ sqldb ~ "' AS client; "; // sqldb is the path to the local
PC. Works.
q = "BEGIN;";
q ~= "REPLACE INTO LSOpenProjects
SELECT * FROM client.LSOpenProjects
WHERE login)='" ~ pm["login"] ~ "' AND XtraB < client.XtraB; ";
q ~= "COMMIT;";
The above is not working... Yes, I am a newbie. Well, kinda, nonetheless,
yes.
What I would like to do is to push all the records that have a higher date
('-mm-dd hh:mm:ss') in the local DB than the SharedDB. They should
really be equal, if no changes have been made.
And then, the wrench in the motor is to bring from the sharedDB to the
SharedPC the records that are also with newer dates then the previously
brought from the SharedDB. I have something like above,
SqliteDatabase dbu = new SqliteDatabase(sqldb);
q = "ATTACH '" ~ sqldba ~ "' AS c; "; // works
q = "BEGIN;";
q ~= "INSERT OR REPLACE INTO LSOpenProjects
SELECT * FROM c.LSOpenProjects
WHERE login='" ~ pm["login"] ~ "' AND c.XtraB > XtraB; ";
q ~= "COMMIT;";
Not working either. Any help would be greatly appreciated.
thanks,
josé
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...
On Tue, Feb 24, 2009 at 5:35 PM, John Machin wrote:
> On 25/02/2009 10:19 AM, Leo Freitag wrote:
>> Hallo,
>>
>> I'm trying to insert the highest value of tblName into tblZO.
>>
>> There fore I followed the hints in this article ...
>> http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--td19085514.html#a19085514
>>
>> ... but I got a error (see below)
>>
>> SQLite version 3.3.13
>> Enter ".help" for instructions
>> sqlite> create 'tblName' ('id' integer primary key, 'text' text);
>> SQL error: near "'tblName'": syntax error
>> sqlite> create table 'tblName' ('id' integer primary key, 'text' text);
>> sqlite> insert into 'tblName' Values (NULL, 'one');
>> sqlite> insert into 'tblName' Values (NULL, 'two');
>> sqlite> insert into 'tblName' Values (NULL, 'three');
>> sqlite> insert into 'tblName' Values (NULL, 'four');
>> sqlite> insert into 'tblName' Values (NULL, 'five');
>> sqlite> create table 'tblRefMaxName' ('ref' integer, 'nn' text);
>> sqlite> insert into 'tblRefMaxName' Values (select max(id) from tblName,
>> 'eins')
>> ;
>> SQL error: near "select": syntax error
>> sqlite>
>
> sqlite> insert into 'tblRefMaxName' Values ((select max(id) from
> tblName),'eins'
> );
> sqlite> select * from tblRefMaxName;
> 5|eins
> sqlite>
>
> BTW, don't you find all those '' around your table and column names
> rtaher irritating?
> ___
I've often wondered why folks put [] or '', or even "" around their
column/table names. I guess it is for compatibility with other
software, or perhaps they are used doing so in other software
packages, and those habits continue here.
It is, no doubt, very hassly for me to even read all those extra
sigils. I can't imagine how it must be for them to use those in their
code over and over again.
--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...
On 25/02/2009 10:19 AM, Leo Freitag wrote:
> Hallo,
>
> I'm trying to insert the highest value of tblName into tblZO.
>
> There fore I followed the hints in this article ...
> http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--td19085514.html#a19085514
>
> ... but I got a error (see below)
>
> SQLite version 3.3.13
> Enter ".help" for instructions
> sqlite> create 'tblName' ('id' integer primary key, 'text' text);
> SQL error: near "'tblName'": syntax error
> sqlite> create table 'tblName' ('id' integer primary key, 'text' text);
> sqlite> insert into 'tblName' Values (NULL, 'one');
> sqlite> insert into 'tblName' Values (NULL, 'two');
> sqlite> insert into 'tblName' Values (NULL, 'three');
> sqlite> insert into 'tblName' Values (NULL, 'four');
> sqlite> insert into 'tblName' Values (NULL, 'five');
> sqlite> create table 'tblRefMaxName' ('ref' integer, 'nn' text);
> sqlite> insert into 'tblRefMaxName' Values (select max(id) from tblName,
> 'eins')
> ;
> SQL error: near "select": syntax error
> sqlite>
sqlite> insert into 'tblRefMaxName' Values ((select max(id) from
tblName),'eins'
);
sqlite> select * from tblRefMaxName;
5|eins
sqlite>
BTW, don't you find all those '' around your table and column names
rtaher irritating?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...
Leo Freitag wrote: > sqlite> insert into 'tblRefMaxName' Values (select max(id) from > tblName, 'eins') > ; insert into tblRefMaxName select max(id), 'eins' from tblName; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...
On Tue, Feb 24, 2009 at 5:19 PM, Leo Freitag wrote:
>
> Hallo,
>
> I'm trying to insert the highest value of tblName into tblZO.
>
> There fore I followed the hints in this article ...
> http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--td19085514.html#a19085514
>
> ... but I got a error (see below)
>
> SQLite version 3.3.13
> Enter ".help" for instructions
> sqlite> create 'tblName' ('id' integer primary key, 'text' text);
> SQL error: near "'tblName'": syntax error
> sqlite> create table 'tblName' ('id' integer primary key, 'text' text);
> sqlite> insert into 'tblName' Values (NULL, 'one');
> sqlite> insert into 'tblName' Values (NULL, 'two');
> sqlite> insert into 'tblName' Values (NULL, 'three');
> sqlite> insert into 'tblName' Values (NULL, 'four');
> sqlite> insert into 'tblName' Values (NULL, 'five');
> sqlite> create table 'tblRefMaxName' ('ref' integer, 'nn' text);
> sqlite> insert into 'tblRefMaxName' Values (select max(id) from tblName,
> 'eins')
select max(id) from tblName, 'eins' is not a valid select statement.
Do the following instead --
insert into tblRefMaxName Values ((select max(id) from tblName), 'eins')
by putting parens around the select, you are now correctly passing two
values that tblRefMaxName expects.
> ;
> SQL error: near "select": syntax error
> sqlite>
>
> Can anybody help?
>
> Leo
> --
> View this message in context:
> http://www.nabble.com/Error-on-INSERT-with-SELECT-Max%28id%29-FROM-...-tp22192949p22192949.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
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
[sqlite] Error on INSERT with SELECT Max(id) FROM ...
Hallo,
I'm trying to insert the highest value of tblName into tblZO.
There fore I followed the hints in this article ...
http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--td19085514.html#a19085514
... but I got a error (see below)
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> create 'tblName' ('id' integer primary key, 'text' text);
SQL error: near "'tblName'": syntax error
sqlite> create table 'tblName' ('id' integer primary key, 'text' text);
sqlite> insert into 'tblName' Values (NULL, 'one');
sqlite> insert into 'tblName' Values (NULL, 'two');
sqlite> insert into 'tblName' Values (NULL, 'three');
sqlite> insert into 'tblName' Values (NULL, 'four');
sqlite> insert into 'tblName' Values (NULL, 'five');
sqlite> create table 'tblRefMaxName' ('ref' integer, 'nn' text);
sqlite> insert into 'tblRefMaxName' Values (select max(id) from tblName,
'eins')
;
SQL error: near "select": syntax error
sqlite>
Can anybody help?
Leo
--
View this message in context:
http://www.nabble.com/Error-on-INSERT-with-SELECT-Max%28id%29-FROM-...-tp22192949p22192949.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Hello! On Monday 23 February 2009 23:54:56 pyt...@bdurham.com wrote: > Are there any plans to enhance SQLite to support some of Oracle's > parallel processing or partitioning capabilities? But Oracle does not database for "cloud computing". You can't get no-installable and no-administrate Oracle instance. You can't use in-memory Oracle databases. You can't easy add new functions to Oracle. You can't [effectively] use Oracle on single SATA disk and common CPU. You can't have hundreds of Oracle databases on single host. You can't create mobile replica of oracle dataset for smartphone or PDA. ... And do you like Oracle? :-) Multi-core processor can perform operations with multiple instances of SQLite in parallel. For common web application there are hundreds of read operations on each write operation and selects performance is more important than locks on parallel write operations. For very fast write operations you must use key- value databases such as berkeleydb or tokyocabinet. But SQLite have problems with long transactions. Oracle is good for this job. Dataflow processing applications prefer in-memory databases and can be SQLite- driven. May be this is not task for SQL-databases but SQL is very useful and comfortable. May be Oracle is good for mainframes but non-administrate clusters are [more] popular now. For example mnesia database (writed on erlang) can be good for this job by it's very complex solution. So I think Oracle may be used for applications with long transactions and only if you have high-powered servers. SQLite can be used for many other causes. This preference may be lost in case SQLite will like Oracle . Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using result of subquery both as value and conditional test...
use the coalesce function coalesce(xxx,'A') returns 'A' if xxx is null, otherwise returns the value of xxx (but remember that '' is NOT null in SQLite). coalesce((select y_value from y where y_id = x_id),'darn') replaces your case statement -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin Sent: Tuesday, February 24, 2009 3:01 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Using result of subquery both as value and conditional test... On 25/02/2009 6:15 AM, John Elrick wrote: > I may be overlooking something obvious, however, I cannot discern from > the documentation if this is possible. > > given a simple example: > > create table x (x_id integer); > create table y (y_id integer, y_value varchar); > > insert into x values (1); > insert into x values (2); > insert into y values (1, 'Hello world'); > > select case when > (select y_value from y where y_id = x_id) > is null then > 'darn' > else > (select y_value from y where y_id = x_id) > end > from x > > > Is there any way to eliminate the second (select y_value from y where > y_id = x_id)? If so, what would the query look like? > The bog-standard should-work-everywhere SQL way is to use the COALESCE function; see http://en.wikipedia.org/wiki/Null_(SQL) Sqlite has COALESCE and also IFNULL; see http://www.sqlite.org/lang_corefunc.html sqlite> create table foo (id integer, data text); sqlite> insert into foo values(5, 'bar'); sqlite> insert into foo values(6, null); sqlite> select * from foo; 5|bar 6| sqlite> select coalesce((select data from foo where id = 6), 'darn'); darn sqlite> select coalesce((select data from foo where id = 5), 'darn'); bar sqlite> HTH, John ___ 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] Using result of subquery both as value and conditional test...
On 25/02/2009 6:15 AM, John Elrick wrote: > I may be overlooking something obvious, however, I cannot discern from > the documentation if this is possible. > > given a simple example: > > create table x (x_id integer); > create table y (y_id integer, y_value varchar); > > insert into x values (1); > insert into x values (2); > insert into y values (1, 'Hello world'); > > select case when > (select y_value from y where y_id = x_id) > is null then > 'darn' > else > (select y_value from y where y_id = x_id) > end > from x > > > Is there any way to eliminate the second (select y_value from y where > y_id = x_id)? If so, what would the query look like? > The bog-standard should-work-everywhere SQL way is to use the COALESCE function; see http://en.wikipedia.org/wiki/Null_(SQL) Sqlite has COALESCE and also IFNULL; see http://www.sqlite.org/lang_corefunc.html sqlite> create table foo (id integer, data text); sqlite> insert into foo values(5, 'bar'); sqlite> insert into foo values(6, null); sqlite> select * from foo; 5|bar 6| sqlite> select coalesce((select data from foo where id = 6), 'darn'); darn sqlite> select coalesce((select data from foo where id = 5), 'darn'); bar sqlite> HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SAVEPOINT : Seems don't work
I try for test as follow :
on DOS : sqlite3.exe Database
then type : SAVEPOINT spoint;
Error message is : 'SQL error :near "savepoint": syntax error'
I tried 'spoint', "spoint", (spoint), ('spoint'), ("spoint") : no syntax
works
With same test it works with Begin, commit and rollback
-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]de la part de Dan
Envoye : mardi 24 fevrier 2009 05:23
A : General Discussion of SQLite Database
Objet : Re: [sqlite] SAVEPOINT : Seems don't work
On Feb 24, 2009, at 3:02 AM, REPKA_Maxime_NeufBox wrote:
> Hello,
>
> I am quite new to use SQLite
>
> I tried to use SAVEPOINT command but didn't succeed
>
> Does this command work ? how ?
We hope so. What happened to indicate it did not succeed? In what
way did the SAVEPOINT command malfunction?
Dan.
___
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] Using result of subquery both as value and conditional test...
D. Richard Hipp wrote: > On Feb 24, 2009, at 2:15 PM, John Elrick wrote: > > SNIP >> >> Is there any way to eliminate the second (select y_value from y where >> y_id = x_id)? If so, what would the query look like? >> >> > > SELECT coalesce((SELECT y_value FROM y WHERE y_id=x_id), 'darn') FROM x; > > Thank you. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using result of subquery both as value and conditional test...
On Feb 24, 2009, at 2:15 PM, John Elrick wrote: > I may be overlooking something obvious, however, I cannot discern from > the documentation if this is possible. > > given a simple example: > > create table x (x_id integer); > create table y (y_id integer, y_value varchar); > > insert into x values (1); > insert into x values (2); > insert into y values (1, 'Hello world'); > > select case when > (select y_value from y where y_id = x_id) > is null then > 'darn' > else > (select y_value from y where y_id = x_id) > end > from x > > > Is there any way to eliminate the second (select y_value from y where > y_id = x_id)? If so, what would the query look like? > SELECT coalesce((SELECT y_value FROM y WHERE y_id=x_id), 'darn') FROM x; > > John Elrick > Fenestra Technologies > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using result of subquery both as value and conditional test...
I may be overlooking something obvious, however, I cannot discern from the documentation if this is possible. given a simple example: create table x (x_id integer); create table y (y_id integer, y_value varchar); insert into x values (1); insert into x values (2); insert into y values (1, 'Hello world'); select case when (select y_value from y where y_id = x_id) is null then 'darn' else (select y_value from y where y_id = x_id) end from x Is there any way to eliminate the second (select y_value from y where y_id = x_id)? If so, what would the query look like? John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question on creating/populating table with varchar/clob data types
> Richard, > > This is fascinating. To quote a bit from "The Definitive Guide to SQLite" by Michael Owens: SQLite was conceived on a battleship... well, sort of. SQLite's author, D. Richard Hipp, was working for General Dynamics on a program for the U.S. Navy developing software for use on board guided missile destroyers. The program originally ran on Hewlett-Packard Unix (HPUX) and used an Informix database as the back-end. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Mon, Feb 23, 2009 at 10:34:50PM -0500, pyt...@bdurham.com scratched on the wall: > Hi Billy, > > >> Are there any plans to enhance SQLite to support some of Oracle's > >> parallel processing or partitioning capabilities? > > > I realized that you're asking Richard, and not the peanut gallery, but > > I figured I might as well ask out of curiosity: why do you want to > > see these features in SQLite? > It would be great to see SQLite be able to exploit the extra processing > power of multiple cores. This is not a request for handling multiple > simultaneous transactions - it is a request to have single transactions > be processed across multiple cores. The only way to go multi-core is multi-process and/or multi-thread. Multi-process is pretty much out of the question, given the design goals of SQLite. That leaves multi-threaded. Ask Dr. Hipp about his feelings** on the efficiency and elegance of heavily threaded programming, and in specific how easy they are to test and verify. Go ahead... It's a rough week at work and I could use the entertainment. ** with which I agree. > Another interesting Oracle feature is compression. Oracle's compression > techniques not only compress data, but also speed up many types of > selects. This is one area I do think SQLite might benefit from. As processors get much faster and storage only gets a little faster, it can often be faster to read smaller chunks off disk and decompress them than it is to read the uncompressed chunk. It gets tricky with small page sizes, but on some platforms-- especially those doing direct access to slower flash devices, it might be worth it. There are a number of factors to balance, but it might be an interesting exercise, especially if you're not interested in saving file space, only improving I/O speeds. Hwaci, Inc. (the commercial side of SQLite) does offer a read-only compressed file solution. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Tue, Feb 24, 2009 at 8:19 AM, Allan Edwards wrote: > I must trade my works for currency. "Will Code for FOOD" : - ) I > consult and architect systems professionally and really can't afford > to do much free work. As much as I love to develop solutions, we are > not in the Star Trek age of we just live to better others and > ourselves! If we were you would find me saying "Computer, lets work > up a new piece of software on Sqlite... etc. etc. etc." I am not sure what to make of this. I thought you wrote that you had already "written a socket based server," so that would not mean any further work for you other than putting it on the sqlite.org website and in public domain. On the other hand, I completely understand if your hands are tied with either client-worker confidentiality, work-for-hire agreements, or even the exigencies of trying to monetize your earlier work. In open source, we have to depend on the largesse of others, who, just like us, have exactly the same number of hours in a day, and pretty much exactly the same kind of need for making ends meet. Someone has to, somewhere, decide to give away something. At some point, Richard did that with Sqlite, and we are all here as a result. Even here, many people give generously of their time and knowledge -- most of us on this list would get nowhere without Dan Kennedy, Dennis Cote, Igor Tantednik, Roger Binns and the like. The beauty of open source is that if others can't give away, we do have the freedom of trying to meet our own needs. If we don't have the technical ability to meet our own need, then we can trade our money or other resources with someone who does have that ability. Barring all of that, I would say, we are a noop. Maybe someday someone will feel a burning need for a massively parallelized, multi-core, full CPU utilizing SQLiteHeavy version of this little database, and will have the time or means to make it happen. If that happens, maybe that person will then put this SQLiteHeavy into public domain, and maybe it will become highly popular. We can only hope. > > > > On Tue, Feb 24, 2009 at 7:55 AM, P Kishor wrote: >> On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards >> wrote: >> .. >>> >>> I have personally written a socket based server on top of the database >>> and it works very well. So I have actually scaled the database myself >>> as I preached in this email. Don't be a WIMP and map shares to share >>> a database... write a socket based beauty like YOURS TRULY! hehe For >>> "most" solutions it is wonderful. After years and building millions >>> of lines of code keeping the business delivery requirements fulfilled >>> in the most simple manner seems to be the best approach for me. If >>> you are the same, stay agnostic to all solutions available and run up >>> a strategy that will give you the best of all worlds. And yes, at >>> times you have to write a little EXTRA code to get there! : - ) >>> >>> Allan >>> >>> P.S. If somebody does decide to build enterprise Sqlite, I would love >>> to throw in my 2 cents on how to write the stuff on the outside to add >>> in the big dog features. I was working out tonight and while thinking >>> about this I believe you could actually maintain the wonderfulness of >>> the core engine, then scale the library from an outside piece of code. >>> Then you can keep integrity on both sides of the fence and not make >>> sqlite into sqlitetoomuch. >>> >> .. >> >> Have you considered taking the "socket based server" that you wrote, I >> am assuming, on top of SQLite, and donating it to public domain/open >> source, putting it on the sqlite wiki, so others may benefit? >> >> Who knows, with a seed like that, someone may well build a >> SQLiteEnterprise (as much an oxymoron as that might be). >> >> -- >> 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/ >> Sent from: Madison WI United States. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > W Allan Edwards > 214-289-2959 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to size and position a scrollbar within a virtual listview
On Tue, Feb 24, 2009 at 3:16 AM, Mail.sqlite wrote:
>
> Hi All,
> I searched trough many messages and docs but did not find a solution to this
> trivial looking problem. It would be really nice if someone could point me
> into the right direction.
>
> my ToDo:
> I have to use a virtual listview for a database with some medium and large
> tables. I would give the user the opportunity to select the active displayed
> sorting order with a click on the corresponding listview header.
> To get the necessary number of records for the table with high performance,
> even for large files, this can be done with a trigger that updates a record
> elsewhere with every delete or insert operation.
>
> My Problem:
> how to get the relative position for the selected row depending on the
> selected “order by” clause as fast as possible? An estimate should be enough
> to position the scrollbar.
>
If you had sequential IDs of some sort in your returned result set,
this would be a trivial problem.
Of course, you are likely to not have a continuous sequence in a
database table because some rows may have been deleted. But, you can
fool it by creating a temp table to store your result set... consider
the following --
sqlite> create table foo (a integer primary key, b text);
sqlite> insert into foo (b) values ('blah');
sqlite> insert into foo (b) values ('grop');
sqlite> insert into foo (b) values ('farc');
sqlite> insert into foo (b) values ('drat');
sqlite> select rowid, a, b from foo;
1|1|blah
2|2|grop
3|3|farc
4|4|drat
sqlite> delete from foo where a = 3;
sqlite> select rowid, a, b from foo;
1|1|blah
2|2|grop
4|4|drat
sqlite> create temp table bar as select a, b from foo;
sqlite> select rowid, a, b from bar;
1|1|blah
2|2|grop
3|4|drat
sqlite>
Now we are back to where your problem is trivial. You can use a
rowid/count(*) value to determine the "position" of a row in an
ordered set.
> Thanks for any hints.
>
> George
>
--
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/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards wrote: .. > > I have personally written a socket based server on top of the database > and it works very well. So I have actually scaled the database myself > as I preached in this email. Don't be a WIMP and map shares to share > a database... write a socket based beauty like YOURS TRULY! hehe For > "most" solutions it is wonderful. After years and building millions > of lines of code keeping the business delivery requirements fulfilled > in the most simple manner seems to be the best approach for me. If > you are the same, stay agnostic to all solutions available and run up > a strategy that will give you the best of all worlds. And yes, at > times you have to write a little EXTRA code to get there! : - ) > > Allan > > P.S. If somebody does decide to build enterprise Sqlite, I would love > to throw in my 2 cents on how to write the stuff on the outside to add > in the big dog features. I was working out tonight and while thinking > about this I believe you could actually maintain the wonderfulness of > the core engine, then scale the library from an outside piece of code. > Then you can keep integrity on both sides of the fence and not make > sqlite into sqlitetoomuch. > .. Have you considered taking the "socket based server" that you wrote, I am assuming, on top of SQLite, and donating it to public domain/open source, putting it on the sqlite wiki, so others may benefit? Who knows, with a seed like that, someone may well build a SQLiteEnterprise (as much an oxymoron as that might be). -- 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/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite question: group by column with multiple tags?
On Tue, Feb 24, 2009 at 7:44 AM, Yuzem wrote: > > > > P Kishor-3 wrote: >> Google for "normalizing a database" and then read up on it. It will help. >> > > Ok, thanks, thats the solution. Now there is another problem that arise. > Lets say I have a table called movies with 3 columns (id, titles, keywords): > > 1|title1|keyword1 > 1|title1|keyword2 > 1|title1|keyword3 > 2|title2|keyword1 > 2|title2|keyword2 > > The id column is unique, the same from imdb so if a want to add twice the > same movie it doesn't let me. > > After normalization I have two tables (movies (id, titles) and keywords (id, > keywords)): > movies: > 1|title1 > 2|title2 > keywords: > 1|keyword1 > 1|keyword2 > 1|keyword3 > 2|keyword1 > 2|keyword2 > > How do I prevent inserting the same keyword for the same movie? Make a primary key out of the combo of keyword_id and keyword_name. Add a constraint to the table to throw and error. Oh, be sure to read up on a normalization tutorial as well as the docs on sqlite.org. My advice here is hardly a substitute for either of those. Good luck. -- 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/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite question: group by column with multiple tags?
P Kishor-3 wrote: > Google for "normalizing a database" and then read up on it. It will help. > Ok, thanks, thats the solution. Now there is another problem that arise. Lets say I have a table called movies with 3 columns (id, titles, keywords): 1|title1|keyword1 1|title1|keyword2 1|title1|keyword3 2|title2|keyword1 2|title2|keyword2 The id column is unique, the same from imdb so if a want to add twice the same movie it doesn't let me. After normalization I have two tables (movies (id, titles) and keywords (id, keywords)): movies: 1|title1 2|title2 keywords: 1|keyword1 1|keyword2 1|keyword3 2|keyword1 2|keyword2 How do I prevent inserting the same keyword for the same movie? -- View this message in context: http://www.nabble.com/Sqlite-question%3A-group-by-column-with-multiple-tags--tp22153722p22180987.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to delete the rows which is the result of Select operation of another table?
On Tue, Feb 24, 2009 at 7:23 AM, Pramoda M. A wrote: > Dear All, > > > > I have to delete the rows of one table table but key is the result of > select operation of another table? > > > > Delete from table1 where ID = ( > > > Select ID from table2 where name like '%sqlite%'); > > > > Is it ok? > did you try it? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to delete the rows which is the result of Selectoperation of another table?
"Pramoda M. A" wrote in message news:f7846b8f3c78c049b6a1dff861f6c16f0362f...@kcinblrexb01.kpit.com > I have to delete the rows of one table table but key is the result of > select operation of another table? > > > > Delete from table1 where ID = ( > > > Select ID from table2 where name like '%sqlite%'); If the inner select can ever return more than one row, you'd want delete from table1 where ID in ( select ID from table2 where name like '%sqlite%'); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to delete the rows which is the result of Select operation of another table?
Dear All, I have to delete the rows of one table table but key is the result of select operation of another table? Delete from table1 where ID = ( Select ID from table2 where name like '%sqlite%'); Is it ok? With Regards Pramoda.M.A KPIT Cummins Infosystems Limited | Bengaluru | Board: +91 80 30783905 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a question on the callback function's return values of sqlite3_exec()
"liubin liu" <7101...@sina.com> wrote in message news:22176984.p...@talk.nabble.com > the question is on the callback function's return values of > sqlite3_exec() > > when using sqlite3_exec() to do "select * from ...", how to get all > the return values by using the callback function? > > it could print the result, but couldn't return the values. If do like > so, just one value could be get. > how to get all the values? Memory allocated for value[] strings is valid only inside the callback. It is deallocated or reused as soon as the callback returns. So, you can't just store a pointer you receive - it'll soon become invalid. You need to allocate your own memory and make a copy of string contents. Also, consider using prepared statements instead of sqlite3_exec - see sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_column_*. For one thing, values of numeric fields could be retrieved directly as integers, rather than converted to strings and then converted back. 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 using Wild Characters?
"Pramoda M. A" wrote in message news:f7846b8f3c78c049b6a1dff861f6c16f0362e...@kcinblrexb01.kpit.com > I have to select using wild charcters? How to do it? > > For eg: I have to select field which should contain "sqlite"... Then > *sqlite* is not working... select * from mytable where somefield like '%sqlite%'; -- or select * from mytable where somefield glob '*sqlite*'; http://sqlite.org/lang_expr.html#like 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 using Wild Characters?
Use % (percent instead of * and use _ (underscore) instead of ? Pramoda M. A wrote: > Hi All, > > > > I have to select using wild charcters? How to do it? > > For eg: I have to select field which should contain "sqlite"... Then > *sqlite* is not working... > > Pleas help me. > > > > With Regards > > Pramoda.M.A > > KPIT Cummins Infosystems Limited | Bengaluru | Board: +91 80 30783905 > > > > > > > ___ > 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 using Wild Characters?
Hi All, I have to select using wild charcters? How to do it? For eg: I have to select field which should contain "sqlite"... Then *sqlite* is not working... Pleas help me. With Regards Pramoda.M.A KPIT Cummins Infosystems Limited | Bengaluru | Board: +91 80 30783905 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
I wanted to throw out a few more points about being parallel and scalable in terms of data storage? What is the reason we want to be this way? Well, for tons of users and to mitigate risk across lots of machines. Yes, out of the box the value that Oracle provides is of a designation that satisfies these ideals for large scale systems. YET, as memory, hard drive space, processors with more cores, etc. come online, how could you get Sqlite to be SQLiteIKickOraclesButt? Well, I although I am pressed for time in development of a number of systems I don't have time to try this out but what Sqlite would need is a broker like server that could utilize it's existing features. The broker server could provide the same features as an Oracle by splitting inserts across multiple sqlites that hit multiple disks on multiple systems. The reality is that Sqlite has taken care of the nasty details of organizing data into a binary file cross platform, but you could literally take the database and write code above the engine itself that leveraged it's capabilities in such a way that it worked like a large scale database. Back years ago I was the head architect on a C based system in semiconductor. We had a crazy requirement to bust out hundreds to thousands of grey scale images from a camera source in a real time manufacturing solution. We had to store this data on the drive quickly. Before I took charge of the project we failed to get the necessary performance needed from an RDBMS on our first project time delivery limits. On the second round we bench marked all system components and figured out how we needed to architect a system from every hardware dependency. We ended up writing a buffer management library that basically was optimized to pre allocate memory of specific sizes for specific image sizes. This pre allocation allowed for the most optimal approach in moving data into memory. We then had writer threads that had thread synced access into that buffer and eventually the images would get written to an RDBMS. The reason I shot you the example above is to give you food for thought on how you might still utilize the great power of Sqlite if you don't need the massive cost or overhead of an Oracle. Sqlite is a beautiful thing due to it's simplicity. It is the most powerful database management solution on the planet for it's size (this is TOTALLY OBJECTIVE). Installing Oracle on a computer, being force fed crappy performing and unrobust java (the enterprise Oracle manager crashes on me with HUGE stack traces) too butt with Oracle makes for one nasty user experience. Yet with sexy Sqlite you can "xcopy" install that puppy and go to town. So in your thought on your desired solutions, IT MIGHT be good to think of what Sqlite offers and consider writing a separate library that could fulfill your extrea RDBMS requirements so you can take advantage of the sweetness this little data storage gym offers. I have personally written a socket based server on top of the database and it works very well. So I have actually scaled the database myself as I preached in this email. Don't be a WIMP and map shares to share a database... write a socket based beauty like YOURS TRULY! hehe For "most" solutions it is wonderful. After years and building millions of lines of code keeping the business delivery requirements fulfilled in the most simple manner seems to be the best approach for me. If you are the same, stay agnostic to all solutions available and run up a strategy that will give you the best of all worlds. And yes, at times you have to write a little EXTRA code to get there! : - ) Allan P.S. If somebody does decide to build enterprise Sqlite, I would love to throw in my 2 cents on how to write the stuff on the outside to add in the big dog features. I was working out tonight and while thinking about this I believe you could actually maintain the wonderfulness of the core engine, then scale the library from an outside piece of code. Then you can keep integrity on both sides of the fence and not make sqlite into sqlitetoomuch. On Mon, Feb 23, 2009 at 9:49 PM, wrote: > P Kishor, > >>> Most computers these days are multi-core. .. > >> One of things easy to overlook is that SQLite is not a PC-exclusive > software. About 10 million copies of SQLite run on iPhone. Who knows > how many run on other handhelds, embedded platforms, Vxworks, the > like. SQLite used to fit on a floppy and probably still does. > > Good point! > > I lost my perspective on SQLite's intended audience. > > Regards, > Malcolm > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- W Allan Edwards 214-289-2959 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Actually, Oracle boots pretty fast and once it is booted up, it is SUPER fast on insertion. I have used the following databases professionally for years * MS SQL Server * MS SQL Server Embedded * MS Access * Oracle (numerous versions) * MySQL (multiple versions) * Objectivity * PostgreSQL * Sqlite * DB2 When you talk about performance comparisons your question really needs to be squared up to what the database is used for. Are you on a server, workstation, or embedded? How large in the database? How are the internal algos of the database engine lined up? What is the memory footprint? How did you configure what is configurable in the database? The reality is that Sqlite is slower on 1 insert per transaction, but if you start a transaction, insert a slew of records, then commit, the database is very fast. The major platforms that are typically server oriented like Oracle pre allocate memory and file space so they can "cheat" for a while to be optimized. Oh wait, and index space is pre allocated. Sqlite appears to be optimized for single file access and as the docs on the website say, no server full of memory to buffer and give the appearance that it is faster than it really is on inserts in terms of hard drive write speed. The reality is I BET and I would love to know myself Sqlite is just as fast on inserts to the same hard drive as oracle. People that are political in slant toward a specific thing tend to like to make a blanket statement like Oracle is faster than Sqlite but not inform everyone else as to a specific comparison which makes the statement full of it! : - ) Are you some kind of political Oracle covering biggot? hehehe Or, do you want another chance to post something that states you are trying to find the best data storage solution to deliver sincere value to your client in terms of a database choice? Sometimes that choice is Sqlite, but in some cases it is Oracle instead. Sqlite is a very Hipp database. Allan On Mon, Feb 23, 2009 at 3:28 PM, D. Richard Hipp wrote: > > On Feb 23, 2009, at 3:54 PM, pyt...@bdurham.com wrote: > >> Dr. Hipp, >> >> When you say "SQLite is way faster than Oracle in a single-user >> applications" do you mean that SQLite can be faster than Oracle even >> when Oracle's parallel processing features are being used? For example >> Oracle's support for parallelization can speed up table loading from >> an >> external data source, certain SQL selects, and certain indexing >> operations. > > I don't run Oracle and have no way of verifying the following. But I > conjecture that from a cold start, you and launch an application that > uses SQLite, have it do a dozen or so queries, print out the answer, > and shut down, all before the Oracle server has even booted up to the > point where it will accept connections. Correct me if I am wrong. > > Perhaps Oracle will run a gazillion more transactions per second, > given enough memory and CPUs, and once you get it up and going. I > have no way of knowing. But then again, that isn't really the point > of SQLite. > > >> >> >> Are there any plans to enhance SQLite to support some of Oracle's >> parallel processing or partitioning capabilities? > > Remember: SQLite is not trying to replace Oracle. SQLite is trying > to replace fopen(). > > For people who are using Oracle as a replacement for fopen() (as > apparently Angela is) they will likely find that SQLite makes a far > superior replacement. Or to put it another way, people who are using > Oracle for a single-user application (low concurrency) will likely > find that SQLite works much better for them. It has been my > experience that old-time Oracle users are incredulous at this > statement, until they actually see a live demonstration. So I won't > try to argue the point. It is merely my observation. > > On the other hand, nobody things that SQLite is a suitable database > when you have 1000 separate connections beating on the database all at > once. > > > >> >> >> Thank you, >> Malcolm >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- W Allan Edwards 214-289-2959 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite optim
> On Monday 23 February 2009 13:09:58 Jérôme Loyet wrote: >> My questions: >> 1- Is there a better way to populate the `siblings` table ? (optimize >> my sql request) > > You may use compose index on (cookie,referer,date) and REAL datatype for > dates. I choosed integer for the date as everything is stored in unix timestamp, so it's just about comparing integers. But maybe I'm wrong. > >> 2- What can I do to optimize the all process ? (play with some PRAGMA >> parameters maybe) > > pragma page_size=4096; > pragma default_cache_size=20; > pragma cache_size=20; > > (cache with this options is 200 000 * 4k=800 Mb). > >> 3- Is sqlite a good choice for this case ? Would mysql or postgresql a >> better choice ? > > SQLite is good choice but data flow parser is more better then sql queries for > realtime analyze. You can write demon for prepare data to load in database and > inserting data periodically with transactions. i'm not planning to do real time analysis. I want to do some stats after (several days or month) to study the users behaviours on the website. > > Best regards. Thx a lot ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to size and position a scrollbar within a virtual listview
Hi All, I searched trough many messages and docs but did not find a solution to this trivial looking problem. It would be really nice if someone could point me into the right direction. my ToDo: I have to use a virtual listview for a database with some medium and large tables. I would give the user the opportunity to select the active displayed sorting order with a click on the corresponding listview header. To get the necessary number of records for the table with high performance, even for large files, this can be done with a trigger that updates a record elsewhere with every delete or insert operation. My Problem: how to get the relative position for the selected row depending on the selected “order by” clause as fast as possible? An estimate should be enough to position the scrollbar. Thanks for any hints. George ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
