[sqlite] Extra functions for portability
Hi, My database layer needs to support PostgreSQL, MS SQL Server and SQLite and as much as possible I try to use the same SQL statements without modification. I found that for some of my uses, I needed extra functions or aliases to builtin sqlite functions and so I wrote them. Since others may find it useful, I have released it for others to use at http://code.jellycan.com/files/sqlite3_extra.c It piggybacks onto the sqlite3.c amalgamation file so that I can avoid recreating code that already exists in sqlite - if you are not using the amalgamation then you may need to modify it to suit. Hope it is useful to someone. Regards, Brodie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
"Paul Hilton" <[EMAIL PROTECTED]> wrote: > > Here is the problem: I want Slot created to disambiguate the Primary Key, So > that for every value of Group the value of Slot starts at 1 and counts up. These are two different things: (1) Slot needs to disambiguate the PRIMARY KEY (2) Slot needs to start at 1 and count upwards Do you really need (2)? If not - if (1) is all you really need - then one approach is to fill the slot with a random number. Depending on how many slots you have and how often you fill them, you might be able to get away with always using a random 64-bit integer and never checking for collisions because collisions will be much so rare that random computer explosions are much more likely. Whether or not this is true depends on your application, the reliability of your hardware, and the consequences of a collision. Do the math. If in doubt, you might use a 128-bit or longer random blob instead of an integer. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cross DB triggers?
On Mon, Mar 3, 2008 at 3:09 PM, Jeff Hamilton <[EMAIL PROTECTED]> wrote: > I have a setup with two databases that have tables which refer to each > other. I'd like to create triggers to handle cleanup when items are > deleted from one database that are referred to from the second > database. I'm pretty sure this is not allowed, because SQLite can't be sure you'll have both databases attached with the appropriate logical names. What you probably could do would be to write a custom function to implement the mirroring, and then have the trigger call that. Might then look like: CREATE TRIGGER cleanup AFTER DELETE ON data BEGIN SELECT temp_db_delete_fn(OLD.id); END; -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
Paul Hilton wrote:
>
> Thanks for the suggestion,
>
> However it doesn't solve my problem, perhaps because of something I failed
> to say.
>
> I am intending to use these 'Slot' numbers to schedule experiments between
> talkers and listeners.
> The 'Slot' refers to a time slot.
> I would like the experiments within each group to go on simultaneously.
> So the first experiment in group 1 occurs in the same time slot as the first
> experiment in all the other groups.
>
Then you need to get fancier. :-)
The following SQL shows how to use a trigger to update a separate table
that stores the slot values to be inserted into your communications
table. The trace at the end shows my results.
create table talker (id integer primary key, "group" integer);
create table listener (id integer primary key, "group" integer);
create table communications (id integer primary key,
"group" integer, slot integer, talker integer, listener integer);
create table slots ("group" integer primary key, slot integer);
create trigger in_trig after insert on communications
begin
insert or replace into slots select new."group",
coalesce((select slot from slots where "group" = new."group") +
1, 1);
update communications
set slot = (select slot from slots where "group" = new."group")
where id = new.id;
end;
insert into talker values (11, 1);
insert into talker values (22, 1);
insert into talker values (34, 1);
insert into talker values (47, 2);
insert into talker values (15, 2);
insert into talker values (37, 2);
insert into listener values (11, 1);
insert into listener values (12, 1);
insert into listener values (22, 1);
insert into listener values (47, 2);
insert into listener values (15, 2);
delete from slots;
delete from communications;
insert into communications
select null, t."group", 0, t.id, l.id
from talker as t
join listener as l
where l."group" = t."group"
and l.id != t.id
order by t."group", t.id, l.id;
.mode column
.header on
select * from communications;
select slot, "group", talker, listener
from communications order by slot, "group";
SQLite version 3.5.6
sqlite> create table talker (id integer primary key, "group" integer);
snip...
sqlite> select * from communications;
id group slottalker listener
-- -- -- -- --
1 1 1 11 12
2 1 2 11 22
3 1 3 22 11
4 1 4 22 12
5 1 5 34 11
6 1 6 34 12
7 1 7 34 22
8 2 1 15 47
9 2 2 37 15
10 2 3 37 47
11 2 4 47 15
sqlite> select slot, "group", talker, listener
...> from communications order by slot, "group";
slotgroup talker listener
-- -- -- --
1 1 11 12
1 2 15 47
2 1 11 22
2 2 37 15
3 1 22 11
3 2 37 47
4 1 22 12
4 2 47 15
5 1 34 11
6 1 34 12
7 1 34 22
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Queries
Thanks for everybody's input, I will test these things out tonight... On Mon, Mar 3, 2008 at 3:53 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > > SELECT data FROM LIST l > > INNER JOIN MAIN m ON l.mid = m.id > > WHERE m.name = "something"; > > The two statements are not equivalent: they produce different results if > there's more than one record in MAIN with name='something' > I guess this was one criteria that was not specified. 'name' is intended to be unique as well. Should I redeclare my table to reflect as much? Would that improve performance any? Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cross DB triggers?
I don't believe that this type of trigger is allowed. Jeff Hamilton <[EMAIL PROTECTED]> wrote: Hi all, I have a setup with two databases that have tables which refer to each other. I'd like to create triggers to handle cleanup when items are deleted from one database that are referred to from the second database. My attempts at doing this have all failed, either with SQL parse errors or with runtime errors from the triggers. For example: sqlite> CREATE TABLE data (id INTEGER PRIMARY KEY, data TEXT); sqlite> ATTACH DATABASE '/tmp/temp.db' AS temp_db; sqlite> CREATE TABLE temp_db.status (data_id INTEGER REFERENCES data(id)); sqlite> CREATE TRIGGER cleanup AFTER DELETE ON data ...> BEGIN ...>DELETE FROM temp_db.status WHERE data_id = OLD.id; ...> END; SQL error: near ".": syntax error Then I tried: sqlite> CREATE TRIGGER cleanup AFTER DELETE ON data ...> BEGIN ...>DELETE FROM status WHERE data_id = OLD.id; ...> END; which was allowed, but when deleting an item from data I get: sqlite> DELETE FROM data WHERE id=1; SQL error: no such table: main.status Any ideas on how to do the cleanup across attached databases? -Jeff ___ 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] Cross DB triggers?
Hi all, I have a setup with two databases that have tables which refer to each other. I'd like to create triggers to handle cleanup when items are deleted from one database that are referred to from the second database. My attempts at doing this have all failed, either with SQL parse errors or with runtime errors from the triggers. For example: sqlite> CREATE TABLE data (id INTEGER PRIMARY KEY, data TEXT); sqlite> ATTACH DATABASE '/tmp/temp.db' AS temp_db; sqlite> CREATE TABLE temp_db.status (data_id INTEGER REFERENCES data(id)); sqlite> CREATE TRIGGER cleanup AFTER DELETE ON data ...> BEGIN ...>DELETE FROM temp_db.status WHERE data_id = OLD.id; ...> END; SQL error: near ".": syntax error Then I tried: sqlite> CREATE TRIGGER cleanup AFTER DELETE ON data ...> BEGIN ...>DELETE FROM status WHERE data_id = OLD.id; ...> END; which was allowed, but when deleting an item from data I get: sqlite> DELETE FROM data WHERE id=1; SQL error: no such table: main.status Any ideas on how to do the cleanup across attached databases? -Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Queries
Scott Baker <[EMAIL PROTECTED]> wrote: > Mike McGonagle wrote: >>> -- Compound Query >>> SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = >>> "something") ORDER BY ord; >>> >>> -- Individual Queries >>> SELECT id FROM MAIN WHERE name = "something"; >>> SELECT data FROM LIST WHERE mid = id_as_returned_above; > > This just screams inner join. > > SELECT data FROM LIST l > INNER JOIN MAIN m ON l.mid = m.id > WHERE m.name = "something"; The two statements are not equivalent: they produce different results if there's more than one record in MAIN with name='something' Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Queries
I'm sure the real experts will chime-in, but it looks like you might be executing the subquery once for every row in main. Maybe if you use a join, it would go faster select L.data from list L, main m where m.name='something' and L.mid = m.id; Or, maybe you could just use in() rather than =. -- Compound Query SELECT data FROM LIST WHERE mid in (SELECT id FROM MAIN WHERE name = "something") ORDER BY ord; -Clark - Original Message From: Mike McGonagle <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Monday, March 3, 2008 1:32:45 PM Subject: [sqlite] Question on Queries Hello all, I was working with some queries last night, and ran accross something that I don't quite understand. Basically, this is what I have... *** CREATE TABLE MAIN ( id integer primary key autoincrement not null, name varchar(30), [other fields left out, as they are not used] ); CREATE TABLE LIST ( mid integer, ord integer, data float ); -- Compound Query SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = "something") ORDER BY ord; -- Individual Queries SELECT id FROM MAIN WHERE name = "something"; SELECT data FROM LIST WHERE mid = id_as_returned_above; *** So, what is happening is when I run the first query, it takes about 45 seconds for the data to be returned. It is correct and everything, just takes a long time. But, when I run the queries in two passes, it comes back pretty quickly, nowhere near the 45 seconds it takes for the first compound query. Is this something that is unique to SQLITE? Or would any database engine choke on these sorts of queries? Would this go faster if I create an index on 'name'? I believe that the version of SQLITE that I am running is 3.1.3 (I am not on the machine that I was running this on). Is there something out there (on the net) that I should read that explains these sorts of things? Thanks, Mike ___ 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] Question on Queries
Mike McGonagle wrote: > Oh, I forgot to mention (if it matters), the "MAIN" table has about 3000 > rows in it, while the "LIST" table has about 6 rows. > Mike > > > On Mon, Mar 3, 2008 at 3:32 PM, Mike McGonagle <[EMAIL PROTECTED]> wrote: > >> Hello all, >> I was working with some queries last night, and ran accross something that >> I don't quite understand. Basically, this is what I have... >> >> *** >> >> CREATE TABLE MAIN ( >> id integer primary key autoincrement not null, >> name varchar(30), >> [other fields left out, as they are not used] >> ); >> >> CREATE TABLE LIST ( >> mid integer, >> ord integer, >> data float >> ); >> >> -- Compound Query >> SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = >> "something") ORDER BY ord; >> >> -- Individual Queries >> SELECT id FROM MAIN WHERE name = "something"; >> SELECT data FROM LIST WHERE mid = id_as_returned_above; This just screams inner join. SELECT data FROM LIST l INNER JOIN MAIN m ON l.mid = m.id WHERE m.name = "something"; My advice is ALWAYS to avoid subselects unless you ABSOLUTELY have to use them. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "database is locked" on clean install, empty database?
On Mon, Mar 3, 2008 at 12:59 PM, Andreas Kupries <[EMAIL PROTECTED]> wrote: > Is the database file by chance in a NFS mounted directory ? That was it. Thanks! Sean -- "Humanity has advanced, when it has advanced, not because it has been sober, responsible, and cautious, but because it has been playful, rebellious, and immature." -- Tom Robbins ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Monday, March 03, 2008 4:17 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key? Paul Hilton wrote: > Hello, > > I have source tables Talker and Listener, each with fields ID (PK, Integer) > and Group (Integer): > > CREATE TABLE Talker (ID INTEGER, Group INTEGER, Primary Key (ID)); > Ditto Listener > > I would like to make a table Communications with fields Group (PK, Integer), > Slot(PK, Integer) TalkerID (Integer), ListenerID (Integer): > > CREATE TABLE Communications (Group INTEGER, Slot INTEGER, TalkerID INTEGER, > ListenerID INTEGER, PRIMARY KEY (Group, Slot)); > > I want all combinations of Talker and Listener where > Talker.Group=Listener.Group and Talker.ID!=ListenerID > > Here is the problem: I want Slot created to disambiguate the Primary Key, So > that for every value of Group the value of Slot starts at 1 and counts up. I > don't actually care which TalkerID / ListenerID which value of Slot > corresponds to. > > E.g. > INSERT INTO Communications (Group, TalkerID, ListenerID) > SELECT Talker.Group, Talker.ID, Listener.ID > FROM Talker, Listener > WHERE Talker.Group=Listener.Group AND Talker.ID!=Listener.ID; > > Tries to insert all the records, but doesn't make a Slot value to > disambiguate. > > A numerical illustration of what I want: > > Talker > ID Group > 11 1 > 22 1 > 34 1 > 47 2 > 15 2 > 37 2 > > Listener > ID Group > 11 1 > 12 1 > 22 1 > 47 2 > 15 2 > > Should Yield Communications > Group SlotTalkerIDListenerID > 1 1 11 12 > 1 2 11 22 > 1 3 22 11 > 1 4 22 12 > 1 5 34 11 > 1 6 34 12 > 1 7 34 22 > 2 1 47 15 > 2 2 15 47 > 2 3 37 47 > 2 4 37 15 > > Thanks for any help. > Paul Hilton > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > It's not quite the same but you could try this instead. create table talker (id integer primary key, group integer); create table listener (id integer primary key, group integer); create table communications (id integer primary key, group integer, talker integer, listener integer); After you insert your data into talker and listener. insert into talker ... insert into listener ... You run the following commands to build the communications table. delete from communications; insert into communications select null, t.group, t.id, l.id from talker as t join lister as l where l.group = t.group and l.id != t.id order by t.group, t.id, l.id; This should produce the following table with a unique unambiguous id for each communication. id group talker listener 11 11 12 21 11 22 31 22 11 41 22 12 51 34 11 61 34 12 71 34 22 82 15 47 92 37 15 10 2 37 47 11 2 47 15 HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Thanks for the suggestion, However it doesn't solve my problem, perhaps because of something I failed to say. I am intending to use these 'Slot' numbers to schedule experiments between talkers and listeners. The 'Slot' refers to a time slot. I would like the experiments within each group to go on simultaneously. So the first experiment in group 1 occurs in the same time slot as the first experiment in all the other groups. Paul Hilton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Queries
Oh, I forgot to mention (if it matters), the "MAIN" table has about 3000 rows in it, while the "LIST" table has about 6 rows. Mike On Mon, Mar 3, 2008 at 3:32 PM, Mike McGonagle <[EMAIL PROTECTED]> wrote: > Hello all, > I was working with some queries last night, and ran accross something that > I don't quite understand. Basically, this is what I have... > > *** > > CREATE TABLE MAIN ( > id integer primary key autoincrement not null, > name varchar(30), > [other fields left out, as they are not used] > ); > > CREATE TABLE LIST ( > mid integer, > ord integer, > data float > ); > > -- Compound Query > SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = > "something") ORDER BY ord; > > -- Individual Queries > SELECT id FROM MAIN WHERE name = "something"; > SELECT data FROM LIST WHERE mid = id_as_returned_above; > > *** > > So, what is happening is when I run the first query, it takes about 45 > seconds for the data to be returned. It is correct and everything, just > takes a long time. > > But, when I run the queries in two passes, it comes back pretty quickly, > nowhere near the 45 seconds it takes for the first compound query. > > Is this something that is unique to SQLITE? Or would any database engine > choke on these sorts of queries? Would this go faster if I create an index > on 'name'? > > I believe that the version of SQLITE that I am running is 3.1.3 (I am not > on the machine that I was running this on). Is there something out there (on > the net) that I should read that explains these sorts of things? > > Thanks, > > Mike > > -- Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal. —Yehudi Menuhin (1916–1999), musician ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on Queries
Hello all, I was working with some queries last night, and ran accross something that I don't quite understand. Basically, this is what I have... *** CREATE TABLE MAIN ( id integer primary key autoincrement not null, name varchar(30), [other fields left out, as they are not used] ); CREATE TABLE LIST ( mid integer, ord integer, data float ); -- Compound Query SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = "something") ORDER BY ord; -- Individual Queries SELECT id FROM MAIN WHERE name = "something"; SELECT data FROM LIST WHERE mid = id_as_returned_above; *** So, what is happening is when I run the first query, it takes about 45 seconds for the data to be returned. It is correct and everything, just takes a long time. But, when I run the queries in two passes, it comes back pretty quickly, nowhere near the 45 seconds it takes for the first compound query. Is this something that is unique to SQLITE? Or would any database engine choke on these sorts of queries? Would this go faster if I create an index on 'name'? I believe that the version of SQLITE that I am running is 3.1.3 (I am not on the machine that I was running this on). Is there something out there (on the net) that I should read that explains these sorts of things? Thanks, Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "database is locked" on clean install, empty database?
Sean Rhea wrote: > sqlite> create table foo (node_id, timestamp, tput); > SQL error: database is locked > > What am I doing wrong? > Sean, Ensure that you have write privileges for the database file and the directory it is in. SQLite needs to create a journal file in the same directory for you to make any changes. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
Paul Hilton wrote: > Hello, > > I have source tables Talker and Listener, each with fields ID (PK, Integer) > and Group (Integer): > > CREATE TABLE Talker (ID INTEGER, Group INTEGER, Primary Key (ID)); > Ditto Listener > > I would like to make a table Communications with fields Group (PK, Integer), > Slot(PK, Integer) TalkerID (Integer), ListenerID (Integer): > > CREATE TABLE Communications (Group INTEGER, Slot INTEGER, TalkerID INTEGER, > ListenerID INTEGER, PRIMARY KEY (Group, Slot)); > > I want all combinations of Talker and Listener where > Talker.Group=Listener.Group and Talker.ID!=ListenerID > > Here is the problem: I want Slot created to disambiguate the Primary Key, So > that for every value of Group the value of Slot starts at 1 and counts up. I > don't actually care which TalkerID / ListenerID which value of Slot > corresponds to. > > E.g. > INSERT INTO Communications (Group, TalkerID, ListenerID) > SELECT Talker.Group, Talker.ID, Listener.ID > FROM Talker, Listener > WHERE Talker.Group=Listener.Group AND Talker.ID!=Listener.ID; > > Tries to insert all the records, but doesn't make a Slot value to > disambiguate. > > A numerical illustration of what I want: > > Talker > ID Group > 11 1 > 22 1 > 34 1 > 47 2 > 15 2 > 37 2 > > Listener > ID Group > 11 1 > 12 1 > 22 1 > 47 2 > 15 2 > > Should Yield Communications > Group SlotTalkerIDListenerID > 1 1 11 12 > 1 2 11 22 > 1 3 22 11 > 1 4 22 12 > 1 5 34 11 > 1 6 34 12 > 1 7 34 22 > 2 1 47 15 > 2 2 15 47 > 2 3 37 47 > 2 4 37 15 > > Thanks for any help. > Paul Hilton > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > It's not quite the same but you could try this instead. create table talker (id integer primary key, group integer); create table listener (id integer primary key, group integer); create table communications (id integer primary key, group integer, talker integer, listener integer); After you insert your data into talker and listener. insert into talker ... insert into listener ... You run the following commands to build the communications table. delete from communications; insert into communications select null, t.group, t.id, l.id from talker as t join lister as l where l.group = t.group and l.id != t.id order by t.group, t.id, l.id; This should produce the following table with a unique unambiguous id for each communication. id group talker listener 11 11 12 21 11 22 31 22 11 41 22 12 51 34 11 61 34 12 71 34 22 82 15 47 92 37 15 10 2 37 47 11 2 47 15 HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "database is locked" on clean install, empty database?
> > I must be missing something: > > $ sudo apt-get install sqlite3 > ... > Selecting previously deselected package sqlite3. > ... > Setting up sqlite3 (3.3.8-1.1) ... > $ ls -l test.db > ls: test.db: No such file or directory > $ sqlite3 test.db > SQLite version 3.3.8 > Enter ".help" for instructions > sqlite> create table foo (node_id, timestamp, tput); > SQL error: database is locked > What am I doing wrong? Is the database file by chance in a NFS mounted directory ? -- Andreas Kupries <[EMAIL PROTECTED]> Developer @ http://www.ActiveState.com Tel: +1 778-786-1122 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "database is locked" on clean install, empty database?
I must be missing something: $ sudo apt-get install sqlite3 ... Selecting previously deselected package sqlite3. ... Setting up sqlite3 (3.3.8-1.1) ... $ ls -l test.db ls: test.db: No such file or directory $ sqlite3 test.db SQLite version 3.3.8 Enter ".help" for instructions sqlite> create table foo (node_id, timestamp, tput); SQL error: database is locked sqlite> .databases Error: database is locked sqlite> .quit $ ls -l test.db -rw-r--r-- 1 srhea srhea 0 Mar 3 12:38 test.db Searching the web for "database is locked" pulls up a lot of hits, but none this simple. What am I doing wrong? Thanks in advance, Sean -- "Humanity has advanced, when it has advanced, not because it has been sober, responsible, and cautious, but because it has been playful, rebellious, and immature." -- Tom Robbins ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
Hello, I have source tables Talker and Listener, each with fields ID (PK, Integer) and Group (Integer): CREATE TABLE Talker (ID INTEGER, Group INTEGER, Primary Key (ID)); Ditto Listener I would like to make a table Communications with fields Group (PK, Integer), Slot(PK, Integer) TalkerID (Integer), ListenerID (Integer): CREATE TABLE Communications (Group INTEGER, Slot INTEGER, TalkerID INTEGER, ListenerID INTEGER, PRIMARY KEY (Group, Slot)); I want all combinations of Talker and Listener where Talker.Group=Listener.Group and Talker.ID!=ListenerID Here is the problem: I want Slot created to disambiguate the Primary Key, So that for every value of Group the value of Slot starts at 1 and counts up. I don't actually care which TalkerID / ListenerID which value of Slot corresponds to. E.g. INSERT INTO Communications (Group, TalkerID, ListenerID) SELECT Talker.Group, Talker.ID, Listener.ID FROM Talker, Listener WHERE Talker.Group=Listener.Group AND Talker.ID!=Listener.ID; Tries to insert all the records, but doesn't make a Slot value to disambiguate. A numerical illustration of what I want: Talker ID Group 11 1 22 1 34 1 47 2 15 2 37 2 Listener ID Group 11 1 12 1 22 1 47 2 15 2 Should Yield Communications Group SlotTalkerIDListenerID 1 1 11 12 1 2 11 22 1 3 22 11 1 4 22 12 1 5 34 11 1 6 34 12 1 7 34 22 2 1 47 15 2 2 15 47 2 3 37 47 2 4 37 15 Thanks for any help. Paul Hilton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recover from database corruption? (and why does it happen?)
En/na Luca Olivetti ha escrit: > En/na Luca Olivetti ha escrit: > >> Hello, >> I'm using sqlite 3.3.8 under linux (mandriva 2007.1). > > [...] > >> 1) is sqlite suitable when you have multiple threads accessing the same >> database? Or should I delegate the access in a single thread and >> serialize the queries from the various threads? 1a) and what about more than one open connection in the same thread? >> 2) is sqlite suitable when you access the database from multiple programs? >> 3) why did the first error (rowid missing, wrong # of entries) occur? > > Is it possible that the problem is due to the fact that apparently 3.3.8 > didn't --enable-threadsafe by default (and neither did the distro > provided package)? > > Bye Bye -- Luca Olivetti Wetron Automatización S.A. http://www.wetron.es/ Tel. +34 93 5883004 Fax +34 93 5883007 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite3_prepare() clean up after itself if itfails?
"Scott Hess" <[EMAIL PROTECTED]> wrote: > > True, but my code snippet didn't check for NULL. If, for some reason, > SQLite returned a partial statement handle with an error code, then > I'd expect you would want to pass it back to sqlite3_finalize(). > Since sqlite3_finalize() explicitly handles NULL, I think you can > safely just pump the statement handle from sqlite3_prepare() back to > sqlite3_finalize(), regardless of what it is. > I have modified the documentation so that SQLite now guarantees that it will never require a call to sqlite3_finalize() if sqlite3_prepare() returns anything other than SQLITE_OK. See the latest CVS check-in. http://www.sqlite.org/cvstrac/timeline So, Scott, your extra sqlite3_prepare() call is harmless and probably a good safety precaution. But as of the latest check-in it is no longer necessary. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite3_prepare() clean up after itself if itfails?
On Mon, Mar 3, 2008 at 10:47 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Scott Hess <[EMAIL PROTECTED]> wrote: > > On Mon, Mar 3, 2008 at 10:16 AM, Igor Tandetnik > > <[EMAIL PROTECTED]> wrote: > >> Jerry Krinock <[EMAIL PROTECTED]> wrote: > >> > The Blob Example [1] contains code [2] in which, if > >> sqlite3_prepare() > fails, the subsequent call to > >> sqlite3_finalize() is skipped. Is this > OK? > >> > >> If sqlite3_prepare fails, you don't get a valid sqlite3_stmt > >> handle, so there's nothing to call sqlite3_finalize on. > > > > sqlite3_finalize() returns SQLITE_OK when you pass NULL. > > Perhaps, but does it actually do anything useful when passed NULL? I > don't quite see how it can. True, but my code snippet didn't check for NULL. If, for some reason, SQLite returned a partial statement handle with an error code, then I'd expect you would want to pass it back to sqlite3_finalize(). Since sqlite3_finalize() explicitly handles NULL, I think you can safely just pump the statement handle from sqlite3_prepare() back to sqlite3_finalize(), regardless of what it is. This is how sqlite3_open()/sqlite3_close() work. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite3_prepare() clean up after itself if itfails?
Scott Hess <[EMAIL PROTECTED]> wrote: > On Mon, Mar 3, 2008 at 10:16 AM, Igor Tandetnik > <[EMAIL PROTECTED]> wrote: >> Jerry Krinock <[EMAIL PROTECTED]> wrote: >> > The Blob Example [1] contains code [2] in which, if >> sqlite3_prepare() > fails, the subsequent call to >> sqlite3_finalize() is skipped. Is this > OK? >> >> If sqlite3_prepare fails, you don't get a valid sqlite3_stmt >> handle, so there's nothing to call sqlite3_finalize on. > > sqlite3_finalize() returns SQLITE_OK when you pass NULL. Perhaps, but does it actually do anything useful when passed NULL? I don't quite see how it can. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite3_prepare() clean up after itself if it fails?
On Mon, Mar 3, 2008 at 10:16 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Jerry Krinock <[EMAIL PROTECTED]> wrote:
> > The Blob Example [1] contains code [2] in which, if sqlite3_prepare()
> > fails, the subsequent call to sqlite3_finalize() is skipped. Is this
> > OK?
>
> If sqlite3_prepare fails, you don't get a valid sqlite3_stmt handle, so
> there's nothing to call sqlite3_finalize on.
sqlite3_finalize() returns SQLITE_OK when you pass NULL. So I'd
expect something like the following to be safe:
sqlite3_stmt *s = NULL;
int rc = sqlite3_prepare(db, zSql, nBytes, &s, NULL);
if( rc!=SQLITE_OK ){
sqlite3_finalize(s);
/* Handle error. */
}
-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite3_prepare() clean up after itself if it fails?
Jerry Krinock <[EMAIL PROTECTED]> wrote: > The Blob Example [1] contains code [2] in which, if sqlite3_prepare() > fails, the subsequent call to sqlite3_finalize() is skipped. Is this > OK? If sqlite3_prepare fails, you don't get a valid sqlite3_stmt handle, so there's nothing to call sqlite3_finalize on. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does sqlite3_prepare() clean up after itself if it fails?
The Blob Example [1] contains code [2] in which, if sqlite3_prepare()
fails, the subsequent call to sqlite3_finalize() is skipped. Is this
OK? Does sqlite3_prepare() free up any memory it may have allocated
if it fails?
I've read the documentation for these two functions but still don't
know.
Thanks,
Jerry Krinock
[1] http://www.sqlite.org/cvstrac/wiki?p=BlobExample
[2]:
static int writeBlob(
sqlite3 *db,
const char *zKey,
const unsigned char *zBlob,
int nBlob) {
const char *zSql = "INSERT INTO blobs(key, value) VALUES(?, ?)";
sqlite3_stmt *pStmt;
int rc;
do {
rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
if( rc!=SQLITE_OK ){
return rc;
}
sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC);
sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC);
rc = sqlite3_step(pStmt);
assert( rc!=SQLITE_ROW );
rc = sqlite3_finalize(pStmt);
} while( rc==SQLITE_SCHEMA );
return rc;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does or will Sqlite provide a DUMP mechanism?
It should be easy to write your own dump feature. The table create statements are saved in sqlite_master, and likewise for the schema. Without looking at the code for sqlite3 (the command line utility) or tksqlite, I would bet that is how they implement their dump feature. On Mon, Mar 3, 2008 at 11:53 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Abshagen, Martin RD-AS2 wrote: > > Can a backup mechanism be implemented by means of the current Sqlite-API? > > Well, no, but the database is a single file, so you can back it up by > copying the file. > > If you are concerned about other processes accessing the database while > you are copying it, have your program start an exclusive transaction > before the copy,and roll it back after the copy. > > HTH > Dennis Cote > > > ___ > 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] Does or will Sqlite provide a DUMP mechanism?
Abshagen, Martin RD-AS2 wrote: > Can a backup mechanism be implemented by means of the current Sqlite-API? Well, no, but the database is a single file, so you can back it up by copying the file. If you are concerned about other processes accessing the database while you are copying it, have your program start an exclusive transaction before the copy,and roll it back after the copy. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] SQLite and VB.Net?
ADO.NET is part of the .NET framework. There are no additional depencencies and with SQLite there is nothing to setup outside your own application. The performance impact of using ADO.NET vs direct is miniscule and greatly outweighed by the improved efficiency in development. Sam On Sun, Mar 2, 2008 at 10:36 PM, Gilles Ganault <[EMAIL PROTECTED]> wrote: > On Sun, 2 Mar 2008 21:07:03 -0500, "Samuel Neff" > <[EMAIL PROTECTED]> wrote: > > I would go the ADO.NET route 'cause it'll fit into your .NET application > much > >better. The overhead is minimal compared to the normal cost of running > >database queries (in any database). > > The reason I'm concerned about using ADO.Net instead of hitting the > SQLite library directly, is that this adds dependencies in addition to > the .Net framework. Our customers are anything but computer-savvy, and > most don't have anyone technical around in case things don't work, so > that I'd like to minimize dependencies as much as possible. > > Also, what about performance when using the SQLite library directly > vs. going through ADO.Net? > > Hopefully, I'll have VS2005/2008 and SQLite up and running by the end > of the week, so I can check for myself. > > Thank you. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- - We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer. Position is in the Washington D.C. metro area. Contact [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA auto_vacuum
Raviv Shasha wrote: > > Sqlite_exec (pDB, "PRAGMA auto_vacuum = 1", NULL, 0, &errMsg); > Raviv, That is correct, but you should not the following detail from http://www.sqlite.org/pragma.html > Therefore, auto-vacuuming must be turned on > before any tables are created. It is not possible to enable or > disable auto-vacuum after a table has been created. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error downloading extensions / contributions
Paulo van Breugel wrote:
>
> I have two problems concerning the contributed file
> extension-function.c. First is that I can't download it from the
> 'Contributed files' page on www.sqlite.org. More in general, when trying
> to download a contributed file (extension-functions.c) from the
> Contributed files page (http://www.sqlite.org/contrib/download/), I am
> getting the following error for files other than *.zip, *.gz files :
>
> ERROR: attempt to write a readonly database
>
Paulo,
That is a bug and you could report it at
http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew
>
> Second question is how can I enable this. Can I simply use the statement
> 'SELECT sqlite3_load_extension('extension-fuction.c')'. I have Window XP
> and I use SQLite mostly together with R (http://www.r-project.org/)
> through the package RSQLite.
>
Not quite. First you have to compile the functions into a dll library,
then use the command you showed to load that library.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] since when was fts3 included in binary?
Rael Bauer wrote: >> According to the web page, 3.5.3. > > Which web page is that? > Probably http://www.sqlite.org/changes.html HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ api - callbacks problem
Toby Roworth wrote: > > Looking at the API reference. it would apear you can send an extra > "custom" argument to the callback fro sqlite3_exec, using the 4th > parameter - how does this work, It works well. :-) > and inperticular, could I pass an object > through to the call back, Yes. > and if so, how? > You need to cast a pointer to your object to a void pointer in order to pass it to sqlite3_exec(). Sqlite3_exec() will pass this void pointer as the first argument to your callback function. Inside the callback function you need to cast the void pointer argument back to an object pointer. Now you can use the object pointer to call object methods etc. Note, the callback function itself can be an object method, but if so, it must be a static method (i.e. no this pointer). You can effectively pass the object's instance pointer through to the callback method using the context argument above. Then you can call the instance methods for that particular object. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieve Rownumber in sqlite
Kalyani Phadke wrote: > In SQL Server2005, Row_number() function is used to retrieve the > sequential number of a row within a partition of a result set, starting > at 1 for the first row in each partition. Which is very useful when > implementing paging through a large number records in Table. Is there > any function available in SQLite similar to this. > See http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor for a discussion of paging results that is fast and does not require a row_number for the entries. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
2008/2/22, Dennis Cote <[EMAIL PROTECTED]>: http://article.gmane.org/gmane.comp.db.sqlite.general/17286/match=managing+trees+database > for a method I use to manage trees in SQLite that works very well. another method is to implement it like this (or similar): http://www.codeproject.com/KB/database/Trees_in_SQL_databases.aspx it should be faster for huge database tables in comparision to the slower LIKE comparision. Klemens Friedl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
[EMAIL PROTECTED] wrote: > > When I execute the code, sqlraw > function print the pSql string, and this is the same I pass. > The > Database descriptor is the same returned from open function, and status > code is OK!!! > > But table value isn't updated. > The code looks OK except for the typo (i.e. sPre[2048[ should be sPre[2048]). I assume that your real table isn't named "table" since that is a keyword. Can you open the database file using the sqlite3 command line utility and execute a select query to return or count the number of rows that match your update condition? select count(*) from your_table where Address=7 and Port=1 If that gives a zero result you have your answer. You might also want to show the create statement you used for the table you are trying to update. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
Thufir wrote: > > But isn't recursion, for better or worse, part of the SQL:2003 standard? > It's an optional part of the SQL:1999 standard that is not widely implemented. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does or will Sqlite provide a DUMP mechanism?
Hello all, I am new to Sqlite and I would like to backup Sqlite databases at runtime. Appearently Sqlite does not (yet?) provide a functionality such as mysqlhotcopy. My questions are: Can a backup mechanism be implemented by means of the current Sqlite-API? And if not so: Does anybody plan to add a dump functionality within the next one or two years? Best regards, Martin Abshagen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Crashes
> I put a pointer to the mozilla bug report here:
I probably should have mentioned our bug report as well. It's bug 408518 [1].
> These stack traces don't make any sense to me either. The definition
> of sqlite3_enable_shared_cache() in SQLite cvs is:
That, and as far as I can tell it's ever called by any other sqlite code.
>int sqlite3_enable_shared_cache(int enable){
> sqlite3SharedCacheEnabled = enable;
> return SQLITE_OK;
>}
>
> sqlite3SharedCacheEnable is a file scoped int.
hmm, are there some threadsafty issues there with setting and reading
that value from (possibly) multiple threads? I don't think it's
related to this (I don't think anything in core code in mozilla
actually toggles those - but add-ons can do it) however.
> Stack overflow possibly? Will keep thinking this.
Someone mentioned in the mozilla bug that the new allocator we
switched to may have landed around the time we started seeing this.
I'll look into it further.
Cheers,
Shawn Wilsher
Mozilla Developer
[1] https://bugzilla.mozilla.org/show_bug.cgi?id=408518
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF...THEN constructs
Hi Jason, > I'm used to doing stored procedures for web apps, which > conditionally execute statements based on state and/or the > presence of variables. As others have pointed out, SQLite doesn't (currently at least) offer stored procedures, so no branching in SQL such as if/then etc. But SQL (in SQLite or SQL Server or whatever) is a language for describing sets, rather than procedures anyway, so I think it's usually a case of changing your thinking to deal with sets and subsets, rather than thinking of procedures. The most basic example of this is rather than thinking of "step through each row, if condition then insert", you need to instead think "insert all the new rows where condition". > Consider this construct, which I built recently to populate a table > with URL for a web spider bot I built: I considered it. It seems to use a procedure to do in a convoluted way a task that is better described in terms of sets and SQL: In pseudo code: insert (or ignore if already existing) a new row into SpiderBot_ContentProviders; insert (or ignore if already existing) a new row into SpiderBot, looking up the ContentProviderID from SpiderBot_ContentProviders; > How would I got about re-writing something like this in SQLite? If I'm interpreting your structure correctly, you just need to define your tables to have the necessary unique (or primary key) columns and use "insert or ignore" to skip the insertion of any rows that already exist. So, assuming that your schema is something like: create table SpiderBot ( ContentProviderID integer primary key , LinkPath text collate nocase unique , LinkText text ); create table SpiderBot_ContentProviders ( ContentProviderID integer primary key , ProviderName text collate nocase unique ); create table AddLinkInfo -- containing all the data that you want to import into your other tables ( ProviderName text , LinkPath text , LinkText text ); Then I think your SQL set based solution is simply something like: -- is this a known provider? if not, add it into the DB: insert or ignore into SpiderBot_ContentProviders( ProviderName ) select ProviderName from AddLinkInfo ; -- do the main content insertion and assign its new ID: insert or ignore into SpiderBot (ContentProviderID, LinkPath, LinkText) select (select ContentProviderID from SpiderBot_ContentProviders where ProviderName = AddLinkInfo.ProviderName) , LinkPath , LinkText from AddLinkInfo ; That's just two steps, without nested if/then etc. It should process a lot faster than the procedure you have and certainly much faster than moving the if/then logic out of SQL into your program, which would add a whole lot of delay accessing the database multiple times. It's well worth moving your mindset out of the procedural approach and into the more natural dealing with sets which achieves more natural SQL syntax and faster results. I've had to interpret your purpose, so forgive me if I've missed something here. I hope this helps, Tom BareFeet -- Widest range of Macs and accessories in Australia http://www.tandb.com.au/forsale/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA auto_vacuum
Hi all, I'll appreciate if anyone can send me the correct syntax for executing PRAGMA auto_vacuum = 1 command. In the sqlite documentation there is only a list of all available PRAGMA commands, but I didn't find any code examples. Do I need to execute the PRAGMA auto_vacuum via sqlite_exec as shown as follows: Sqlite_exec (pDB, "PRAGMA auto_vacuum = 1", NULL, 0, &errMsg); Thanks a lot, -Raviv. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] error downloading extensions / contributions
Hi,
I have two problems concerning the contributed file
extension-function.c. First is that I can't download it from the
'Contributed files' page on www.sqlite.org. More in general, when trying
to download a contributed file (extension-functions.c) from the
Contributed files page (http://www.sqlite.org/contrib/download/), I am
getting the following error for files other than *.zip, *.gz files :
ERROR: attempt to write a readonly database
attempt to write a readonly database
while executing
"db eval {UPDATE file SET cnt=cnt+1 WHERE rowid=$Q(get)}"
invoked from within
"if {[info exists Q(get)]} {
db eval {UPDATE file SET cnt=cnt+1 WHERE rowid=$Q(get)}
content-type application/binary
reply-content [db one {SELEC..."
invoked from within
"::tws::eval [read $fd [file size $argv1]]"
invoked from within
"reply-content [::tws::eval [read $fd [file size $argv1]]]"
invoked from within
"evalfile main.tcl"
Second question is how can I enable this. Can I simply use the statement
'SELECT sqlite3_load_extension('extension-fuction.c')'. I have Window XP
and I use SQLite mostly together with R (http://www.r-project.org/)
through the package RSQLite.
Paulo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bus error with SQLite 3.5.6
On Mar 3, 2008, at 12:03 PM, P Kishor wrote:
> Dan,
>
>
> On 3/2/08, Dan <[EMAIL PROTECTED]> wrote:
>>
>> Hi,
>>
>> I tried this script here with the latest CVS version and
>> it didn't crash:
>>
>>CREATE TABLE pages(page_id INTEGER PRIMARY KEY, page_name TEXT,
>> page_text TEXT);
>>CREATE VIRTUAL TABLE fts_pages USING fts3(page_name, page_text);
>>
>>
>>CREATE TRIGGER delete_fts
>>AFTER DELETE ON pages
>>BEGIN
>> DELETE FROM fts_pages WHERE rowid = old.page_id;
>>END;
>>
>>CREATE TRIGGER insert_fts
>>AFTER INSERT ON pages
>>BEGIN
>> INSERT INTO fts_pages (rowid, page_text)
>> VALUES (new.page_id, new.page_text);
>>END;
>>
>>CREATE TRIGGER update_fts
>>AFTER UPDATE OF page_text ON pages
>>BEGIN
>> UPDATE fts_pages
>> SET page_text = new.page_text
>> WHERE rowid = old.page_id;
>>END;
>>
>>
>>INSERT INTO pages (page_name, page_text) VALUES ('foo', 'bar');
>>
>>
>> Can you test this in your environment?
>
> I am not sure what I should test in my environment. Did you mean to
> send me some script? Or, are you asking me to try the latest version
> from CVS?
I mean the set of SQL statements above. Create the schema and
execute a single INSERT statement. Does starting with an empty
database and feeding them to the sqlite shell cause a crash?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why do I get disk error?
Hi, I have a question about an error message which I cannot explain: SQL error (10): disk I/O error insert into merged_devices_lay (cell_num_sch, cell_num_lay, inst_num, sub_inst_num, sub_inst_name) values (57451, 218, 26396, 26396, 'X6/X7/X11/M107'); Under which circumstances does such an error appear? I have enough diskspace, all permissions are set correctly, I use Linux 64 bit, the database itself is about 111 MB. The error seems to appear only with large datasets, but I don't see which limit is hit here. Any help is very appreciated. Thanks and best Regards, Anton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] since when was fts3 included in binary?
> According to the web page, 3.5.3. Which web page is that? -Rael - Never miss a thing. Make Yahoo your homepage. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hierarchical Deletion via a Trigger?
On Fri, 22 Feb 2008 10:46:00 -0500, Igor Tandetnik wrote: > It's impossible in pure SQL, unless the DBMS supports special syntax for > recursive queries, and/or recursive triggers. But isn't recursion, for better or worse, part of the SQL:2003 standard? -Thufir ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
