[sqlite] sqlite FS question
i like sqlite so far. my data is utf8 and has all the nightmarish junk chars in it, (quotes,!,line_feeds, CR so on.), currently i use mysql and i load data from a file I painstakingly prepare using: load data local infile '/tmp/p.data' into table tableone fields terminated by "^^^--&&&" LINES TERMINATED BY '\n' (columna,columnb,columnc,columnd); What's the equivalent I can use in sqlite(3), so that I can import the file into my db ? I shud be able to specify FS of "^^^--&&&" many thx in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Converting BLOB Data type to String
Hello Buddies, I have written a string into database by converting into "BLOB Data Type".writing into database is Ok I got a problem when reading from the database to read a blob from the database I am using the function "const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);" this functions returns me const void * where I want to convert this into "CString".please any one of you can help me in converting const void * to CString. Regards, G.Satish. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple example for dummy user writing C code
I'm having hard time to store and retrieve data with SQLite. Let's
assume I have this structure in my C code to hold my data
struct foo {
long a;
float b;
char c[1024];
int d;
}
so the SQL definition would be
CREATE TABLE foo
(
a LONG;
b FLOAT;
c VARCHAR(1024);
d INT;
);
In real life c[1024] does not hold a printable string but variable
length binary data and d tells the data length. Let's also assume I
have N records where some of the fields can be same.
{ 1, 1.0, "data1", 5 }
{ 1, 2.0, "data2", 5 }
{ 2, 1.0, "data3", 5 }
{ 2, 2.0, "data4", 5 }
{ 5, 6.0, "data5", 5 }
And here's the "dummy user" part, how should I read from and write to
the database? I want to execute
DELETE FROM foo WHERE b < ...
INSERT INTO foo VALUE (..)
SELECT * FROM foo WHERE a=... AND b=...
SELECT c,d FROM foo WHERE a=... AND b=...
I didn't find a _simple_ example for C to do all this...
PS. What is the best way to store IPv6 addresses?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving query performance
On Apr 1, 2009, at 2:00 PM, John Elrick wrote: > > explain query plan > select DISTINCT RESPONSES.RESPONSE_OID > from DATA_ELEMENTS, RESPONSES, SEQUENCE_ELEMENTS > where > SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_NAME = :sequence_element_name and > DATA_ELEMENTS.DATA_ELEMENT_NAME = :data_element_name and > RESPONSES.instance_parent = SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and > RESPONSES.definition_parent = DATA_ELEMENTS.DATA_ELEMENT_OID > > orderfromdetail > 00TABLE DATA_ELEMENTS WITH INDEX data_element_name_idx > 12TABLE SEQUENCE_ELEMENTS WITH INDEX sequence_element_name_idx > 21TABLE RESPONSES The index is not being used on the RESPONSES table because your WHERE clause constraint is comparing a TEXT column (instance_parent) against an INTEGER column (sequence_element_oid). The rules of SQLite are that this requires a NUMERIC comparison, but the index is constructed using a TEXT collation and so the index cannot be used. Various workarounds: (1) redefine RESPONSES.INSTANCE_PARENT to be type INTEGER. (Do the same with RESPONSES.definition_parent). (2) Add a "+" sign in front of sequence_element_oid in the where clause: ... instance_parent = +sequence_element_oid... This will force the RHS of the expression to be an expression rather than a column name. That will force the use of TEXT collating for the comparison, and thus make the index usable. (3) Case the integer on the RHS to text: ... instance_parent = CASE(seqence_element_oid AS varchar) ... Seems like (1) is probably the right fix, but any of these three will work. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select the first 2 rows
Thanks Eric. Joanne From: Eric Minbiole To: General Discussion of SQLite Database Sent: Wednesday, April 1, 2009 12:02:18 PM Subject: Re: [sqlite] select the first 2 rows > Hi all, > I have a big table and I want only select the first 2 rows. > I have tried this : > select top 2 from table; > but it doesn't work! Any help please. > JP Use a LIMIT clause instead of TOP: SELECT * FROM table LIMIT 2; http://www.sqlite.org/lang_select.html ___ 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] General SQL question...
John Elrick wrote: > The following two queries appear to be functionally equivalent...that > is to say the results they produce are identical. Is there any > intrinsic advantage to one over the other? If so, what is that > advantage? The difference is purely stylistical. According to http://sqlite.org/optoverview.html, all conditions in ON clauses are logically moved to WHERE clause before query plan is determined. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] General SQL question...
While experimenting with several different ways of structuring the query referenced in "Improving Query Performance", I mentally raised a question I hope someone can answer. The following two queries appear to be functionally equivalent...that is to say the results they produce are identical. Is there any intrinsic advantage to one over the other? If so, what is that advantage? select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where RESPONSES.definition_parent = DATA_ELEMENTS.DATA_ELEMENT_OID and RESPONSES.instance_parent = SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_NAME = :sequence_element_name and DATA_ELEMENTS.DATA_ELEMENT_NAME = :data_element_name select distinct RESPONSES.RESPONSE_OID from RESPONSES join SEQUENCE_ELEMENTS on (SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID = RESPONSES.instance_parent) join DATA_ELEMENTS on (DATA_ELEMENTS.DATA_ELEMENT_OID = RESPONSES.definition_parent) where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_NAME = :sequence_element_name and DATA_ELEMENTS.DATA_ELEMENT_NAME = :data_element_name ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2038 year problem
Interesting. I suppose these variable results are because of each system's
localtime() function?
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select datetime('2038-12-31 00:00:00');
2038-12-31 00:00:00
sqlite> select strftime('%s', '2038-12-31');
-2147483648
sqlite> select date(-2147483648, 'unixepoch');
1901-12-13
sqlite>
Platform is Linux (Ubuntu Feisty x86 32-bit);
Same results for Win32;
On Solaris, it appears to work differently, getting a positive result from
strftime(), and the correct year, but the month, day, and time are off:
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> select datetime('2038-12-31 00:00:00');
2038-12-31 00:00:00
sqlite> select strftime('%s', '2038-12-31 00:00:00');
2147483647
sqlite> select datetime(2147483647, 'unixepoch');
2038-01-19 03:14:07
sqlite>
-Clark
- Original Message
From: Igor Tandetnik
To: sqlite-users@sqlite.org
Sent: Wednesday, April 1, 2009 11:42:30 AM
Subject: Re: [sqlite] 2038 year problem
denisgolovan
wrote:
> I'd like to ask a question about 2038 year unix problem.
> I've found a mention about it on on
> http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions wiki
> page.
> Though I cannot grasp the idea of the following phase:
>
> "Also, the localtime() C function normally only works for years
> between 1970 and 2037. For dates outside this range, SQLite attempts
> to map the year into an equivalent year within this range, do the
> calculation, then map the year back. "
>
> Does it mean 2038 must be a problem or not?
When you do something like
select datetime('2038-12-31T00:00:00', 'localtime');
SQLite has to convert the time you gave from UTC to local time. It uses
localtime() C function for this. On many systems, this function only
accepts dates between 1970 and 2037. To work around this, SQLite
actually passes a different date to the function: it has the same month,
day and time as the one you specify, but different year (adjusted so
that it falls into an accepted range). After localtime() call, it
adjusts the year back by the same amount.
> Now I'm trying to deal with 2038-2039 year dates, but function date
> just returns nulls for all such date strings. Maybe I miss something?
This works for me
select date('2038-12-31T00:00:00');
and returns the expected value of '2038-12-31'. Show the exact code you
have a problem with.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2038 year problem
01.04.09, 22:42, "Igor Tandetnik" :
> select date('2038-12-31T00:00:00');
> and returns the expected value of '2038-12-31'. Show the exact code you
> have a problem with.
> Igor Tandetnik
Yes. I'm sorry. My fault. Wrong format. I used /MM/DD.
All works :)
--
Regards,
Denis Golovan aka MageSlayer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select the first 2 rows
> Hi all, > I have a big table and I want only select the first 2 rows. > I have tried this : > select top 2 from table; > but it doesn't work! Any help please. > JP Use a LIMIT clause instead of TOP: SELECT * FROM table LIMIT 2; http://www.sqlite.org/lang_select.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select the first 2 rows
Hi all, I have a big table and I want only select the first 2 rows. I have tried this : select top 2 from table; but it doesn't work! Any help please. JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2038 year problem
denisgolovan
wrote:
> I'd like to ask a question about 2038 year unix problem.
> I've found a mention about it on on
> http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions wiki
> page.
> Though I cannot grasp the idea of the following phase:
>
> "Also, the localtime() C function normally only works for years
> between 1970 and 2037. For dates outside this range, SQLite attempts
> to map the year into an equivalent year within this range, do the
> calculation, then map the year back. "
>
> Does it mean 2038 must be a problem or not?
When you do something like
select datetime('2038-12-31T00:00:00', 'localtime');
SQLite has to convert the time you gave from UTC to local time. It uses
localtime() C function for this. On many systems, this function only
accepts dates between 1970 and 2037. To work around this, SQLite
actually passes a different date to the function: it has the same month,
day and time as the one you specify, but different year (adjusted so
that it falls into an accepted range). After localtime() call, it
adjusts the year back by the same amount.
> Now I'm trying to deal with 2038-2039 year dates, but function date
> just returns nulls for all such date strings. Maybe I miss something?
This works for me
select date('2038-12-31T00:00:00');
and returns the expected value of '2038-12-31'. Show the exact code you
have a problem with.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to call c/c++ function in trigger
Thanks, Igor! You're awesome... Igor Tandetnik wrote: > > Simon Chen wrote: >> I just realized that I need to something a bit more complicated. >> Basically, I need myfunction() to take parameters. The parameters >> should be generated based on the entry inserted, like something below: >> >> create trigger triggerName before insert on tableName1 >> when not myfunction(tableName1.name, tableName2.name (where >> tableName2.id == tableName1.parent)) >> begin >> select RAISE(ROLLBACK, 'error message'); >> end; > > when not myfunction(new.name, (select name from tableName2 where > id=new.parent)) > > 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/how-to-call-c-c%2B%2B-function-in-trigger-tp22815938p22832754.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 call c/c++ function in trigger
Simon Chen wrote: > I just realized that I need to something a bit more complicated. > Basically, I need myfunction() to take parameters. The parameters > should be generated based on the entry inserted, like something below: > > create trigger triggerName before insert on tableName1 > when not myfunction(tableName1.name, tableName2.name (where > tableName2.id == tableName1.parent)) > begin > select RAISE(ROLLBACK, 'error message'); > end; when not myfunction(new.name, (select name from tableName2 where id=new.parent)) Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 2038 year problem
Hi I'd like to ask a question about 2038 year unix problem. I've found a mention about it on on http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions wiki page. Though I cannot grasp the idea of the following phase: "Also, the localtime() C function normally only works for years between 1970 and 2037. For dates outside this range, SQLite attempts to map the year into an equivalent year within this range, do the calculation, then map the year back. " Does it mean 2038 must be a problem or not? What does mapping a year back and forth actually mean? Now I'm trying to deal with 2038-2039 year dates, but function date just returns nulls for all such date strings. Maybe I miss something? Thanks -- Regards, Denis Golovan aka MageSlayer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Improving query performance
Sqlite 3.6.10 Background I have the following abbreviated case: CREATE TABLE sequence_elements ( sequence_element_oid integer primary key autoincrement, sequence_element_name varchar, definition_parent varchar, instance_parent varchar, soft_deleted_char varchar default 'F' ) CREATE UNIQUE INDEX sequence_element_name_idx on sequence_elements (sequence_element_name, instance_parent) CREATE INDEX sequence_element_definition_parent_idx on sequence_elements (definition_parent) CREATE INDEX sequence_element_instance_parent_idx on sequence_elements (instance_parent) CREATE TABLE data_elements ( data_element_oid integer primary key autoincrement, data_element_name varchar, definition_parent varchar, attribute_group varchar, hint_one varchar, hint_two varchar, hint_three varchar, options varchar, read_only_behavior_oid integer, soft_deleted_char varchar default 'F' ) CREATE INDEX data_element_name_idx on data_elements (data_element_name) CREATE INDEX data_element_definition_parent_idx on data_elements (definition_parent) CREATE TABLE responses ( response_oid integer primary key autoincrement, response_name varchar, definition_parent varchar, instance_parent varchar, value varchar, prelisted_value varchar, override_behavior_oid integer, soft_deleted_char varchar default 'F' ) CREATE INDEX response_definition_parent_idx on responses (definition_parent) CREATE UNIQUE INDEX response_instance_parent_idx on responses (instance_parent, definition_parent) The following query generates this query plan: explain query plan select DISTINCT RESPONSES.RESPONSE_OID from DATA_ELEMENTS, RESPONSES, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_NAME = :sequence_element_name and DATA_ELEMENTS.DATA_ELEMENT_NAME = :data_element_name and RESPONSES.instance_parent = SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent = DATA_ELEMENTS.DATA_ELEMENT_OID orderfromdetail 00TABLE DATA_ELEMENTS WITH INDEX data_element_name_idx 12TABLE SEQUENCE_ELEMENTS WITH INDEX sequence_element_name_idx 21TABLE RESPONSES Problem == Apparently, responses is unable to use any indices which results in an O(n) table scan for the final step in the query. These queries are autogenerated, so hand tweaking them will be difficult. I have also tried this with CREATE INDEX response_instance_parent_idx on responses (instance_parent) with no change in EXPLAIN QUERY PLAN output. Any recommendations? John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to call c/c++ function in trigger
Just throwing this out there ... how about the 'try / catch' construct in 'c++' ??? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Simon Chen Sent: Wednesday, April 01, 2009 12:40 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to call c/c++ function in trigger I just realized that I need to something a bit more complicated. Basically, I need myfunction() to take parameters. The parameters should be generated based on the entry inserted, like something below: create trigger triggerName before insert on tableName1 when not myfunction(tableName1.name, tableName2.name (where tableName2.id == tableName1.parent)) begin select RAISE(ROLLBACK, 'error message'); end; Is it possible to do this in sqlite? I've been reading this document, and it doesn't look good: http://www.sqlite.org/lang_createtrigger.html It seems doable in other systems: http://www.sqlteam.com/article/an-introduction-to-triggers-part-i Thanks! Igor Tandetnik wrote: > > Simon Chen wrote: >> What I want is: >> >> - whenever I insert/update/delete a table entry, the specified c/c++ >> function is called >> - if the function returns true, the db action can proceed; otherwise, >> the db action should be rolled-back. > > create trigger triggerName before insert on tableName > when not myfunction() > begin > select RAISE(ROLLBACK, 'error message'); > end; > > -- or > > create trigger triggerName before insert on tableName > begin > select RAISE(ROLLBACK, 'error message') > where not myfunction(); > end; > > Similarly for update and delete. > > 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/how-to-call-c-c%2B%2B-function-in-trigger-tp22815938p2 2831836.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to call c/c++ function in trigger
I just realized that I need to something a bit more complicated. Basically, I need myfunction() to take parameters. The parameters should be generated based on the entry inserted, like something below: create trigger triggerName before insert on tableName1 when not myfunction(tableName1.name, tableName2.name (where tableName2.id == tableName1.parent)) begin select RAISE(ROLLBACK, 'error message'); end; Is it possible to do this in sqlite? I've been reading this document, and it doesn't look good: http://www.sqlite.org/lang_createtrigger.html It seems doable in other systems: http://www.sqlteam.com/article/an-introduction-to-triggers-part-i Thanks! Igor Tandetnik wrote: > > Simon Chen wrote: >> What I want is: >> >> - whenever I insert/update/delete a table entry, the specified c/c++ >> function is called >> - if the function returns true, the db action can proceed; otherwise, >> the db action should be rolled-back. > > create trigger triggerName before insert on tableName > when not myfunction() > begin > select RAISE(ROLLBACK, 'error message'); > end; > > -- or > > create trigger triggerName before insert on tableName > begin > select RAISE(ROLLBACK, 'error message') > where not myfunction(); > end; > > Similarly for update and delete. > > 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/how-to-call-c-c%2B%2B-function-in-trigger-tp22815938p22831836.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] Binary Format
thank you so much, that's really helpful. Best Martin Von: D. Richard Hipp An: General Discussion of SQLite Database Gesendet: Mittwoch, den 1. April 2009, 19:11:00 Uhr Betreff: Re: [sqlite] Binary Format On Apr 1, 2009, at 4:24 AM, Martin Pfeifle wrote: > Hi, > we do use SQLite in a standardisation initiative and have to state > which binary file-format of sqlite is used. > Up to now, I was of the opinion that all sqlite versions 3.x use the > same binary sqlite file > format but only differ in the library functionality. > Can somebody confirm that the binary disk format does not change in > 3.x or can it change and > we have to say, we use the binary format of sqlite 3.5.4 for > instance, or is it enough to say > that we use 3.x as binary file format? > Best Martin Martin, http://www.sqlite.org/fileformat.html is still a work in progress, but it was written with you and your standardization effort in mind. 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binary Format
On Apr 1, 2009, at 4:24 AM, Martin Pfeifle wrote: > Hi, > we do use SQLite in a standardisation initiative and have to state > which binary file-format of sqlite is used. > Up to now, I was of the opinion that all sqlite versions 3.x use the > same binary sqlite file > format but only differ in the library functionality. > Can somebody confirm that the binary disk format does not change in > 3.x or can it change and > we have to say, we use the binary format of sqlite 3.5.4 for > instance, or is it enough to say > that we use 3.x as binary file format? > Best Martin Martin, http://www.sqlite.org/fileformat.html is still a work in progress, but it was written with you and your standardization effort in mind. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binary Format
thank you. Best Martin Von: Jay A. Kreibich An: General Discussion of SQLite Database Gesendet: Mittwoch, den 1. April 2009, 15:52:08 Uhr Betreff: Re: [sqlite] Binary Format On Wed, Apr 01, 2009 at 08:24:29AM +, Martin Pfeifle scratched on the wall: > Hi, > we do use SQLite in a standardisation initiative and have to?state > which?binary?file-format of sqlite is used. > Up to now, I was of the opinion that all sqlite versions 3.x use the same > binary sqlite file > format but only differ in the library functionality. > Can somebody confirm that the binary disk format does not change in 3.x or > can it change and > we have to say, we use the binary format of sqlite 3.5.4 for instance, or is > it enough to say > that we use 3.x as binary file format? There are two SQLite3 file formats: v1 and v4. Version 4 supports descending indexes and a more compact Boolean storage format. v4 support was originally added in version 3.3.0 and was used as the default file format for a very short time. As of 3.3.7, things went back to using the v1 format by default. As far as I know, that's still true all the way up to the current 3.6.12 release. All SQLite3 versions can read/write v1. Everything after 3.3.0 can read/write v1 and v4. Versions prior to 3.3 cannot read/write v4. The command "PRAGMA legacy_file_format = [ON|OFF]" can be used to force the use of the v1 file format. It's default value is defined by the SQLITE_DEFAULT_FILE_FORMAT compile time option, which currently defaults to ON/v1. That is, by default current builds of SQLite3 use the v1 file format (although they still support both versions). The SQLite development team has indicated a desire to change this default, although nothing as been said about an expected timeline. Generally the larger issue with library versions is the SQL used within the database. If you use a specific language feature in a table, index, or view definition, then you're obviously limiting the file to a specific version of the SQLite library, even if the file format is technically version compatible with earlier versions. -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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert performance in 3.6.11 vs. 3.5.5
On Wed, 1 Apr 2009 06:08:47 +0200, Günter Obiltschnig wrote: >Well, seems that was a false alarm. We were not able to reproduce this >on other systems - there the 3.6.11 release even performed slightly >better than 3.5.5. Still no idea what caused this, as now even the >original system no longer shows this effect, but it's very probably >not SQLite. > >Best regards, > >Günter Ok, I'm glad it works for you. Thanks for letting us know. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to call c/c++ function in trigger
Simon Chen wrote: > Another question is, if the c/c++ function takes 10 seconds to > finish, when another process queries the exact entry being modified, > what will be returned? Either the old data, or the busy error - depending on whether your writing connection had to spill from in-memory cache to disk yet. > Is it possible to lock > this entry so that either the new value (when check passes) or old > value (when check fails) will be returned AFTER the c function is > done? Open an exclusive transaction with BEGIN EXCLUSIVE. For more details, see http://sqlite.org/lockingv3.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to call c/c++ function in trigger
Another question is, if the c/c++ function takes 10 seconds to finish, when another process queries the exact entry being modified, what will be returned? Maybe the old data? Is it possible to lock this entry so that either the new value (when check passes) or old value (when check fails) will be returned AFTER the c function is done? Thanks! Igor Tandetnik wrote: > > Simon Chen wrote: >> What I want is: >> >> - whenever I insert/update/delete a table entry, the specified c/c++ >> function is called >> - if the function returns true, the db action can proceed; otherwise, >> the db action should be rolled-back. > > create trigger triggerName before insert on tableName > when not myfunction() > begin > select RAISE(ROLLBACK, 'error message'); > end; > > -- or > > create trigger triggerName before insert on tableName > begin > select RAISE(ROLLBACK, 'error message') > where not myfunction(); > end; > > Similarly for update and delete. > > 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/how-to-call-c-c%2B%2B-function-in-trigger-tp22815938p22827535.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] Binary Format
On Wed, Apr 01, 2009 at 08:24:29AM +, Martin Pfeifle scratched on the wall: > Hi, > we do use SQLite in a standardisation initiative and have to?state > which?binary?file-format of sqlite is used. > Up to now, I was of the opinion that all sqlite versions 3.x use the same > binary sqlite file > format but only differ in the library functionality. > Can somebody confirm that the binary disk format does not change in 3.x or > can it change and > we have to say, we use the binary format of sqlite 3.5.4 for instance, or is > it enough to say > that we use 3.x as binary file format? There are two SQLite3 file formats: v1 and v4. Version 4 supports descending indexes and a more compact Boolean storage format. v4 support was originally added in version 3.3.0 and was used as the default file format for a very short time. As of 3.3.7, things went back to using the v1 format by default. As far as I know, that's still true all the way up to the current 3.6.12 release. All SQLite3 versions can read/write v1. Everything after 3.3.0 can read/write v1 and v4. Versions prior to 3.3 cannot read/write v4. The command "PRAGMA legacy_file_format = [ON|OFF]" can be used to force the use of the v1 file format. It's default value is defined by the SQLITE_DEFAULT_FILE_FORMAT compile time option, which currently defaults to ON/v1. That is, by default current builds of SQLite3 use the v1 file format (although they still support both versions). The SQLite development team has indicated a desire to change this default, although nothing as been said about an expected timeline. Generally the larger issue with library versions is the SQL used within the database. If you use a specific language feature in a table, index, or view definition, then you're obviously limiting the file to a specific version of the SQLite library, even if the file format is technically version compatible with earlier versions. -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] .genfkey in 3.6.12
Hi, > > I noticed that the (recommended) amalgation version 3.6.12 does not > > contain > > the new .genfkey functionality, while the (not recommended) full > > version does. > > > > Is this on purpose? > > No, that was a mistake. It has now been fixed. Please download the > sqlite-amalgamation-3.6.12.tar.gz again and rebuild. Yep, works like a charm, tnx! -- Best, Frank. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .genfkey in 3.6.12
On Apr 1, 2009, at 4:50 AM, Frank van Vugt wrote: > Hi, > > I noticed that the (recommended) amalgation version 3.6.12 does not > contain > the new .genfkey functionality, while the (not recommended) full > version does. > > Is this on purpose? No, that was a mistake. It has now been fixed. Please download the sqlite-amalgamation-3.6.12.tar.gz again and rebuild. 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] Email address to post on mailing list
Hi. -- Email - nishshanka...@gmail.com Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .genfkey in 3.6.12
Hi, I noticed that the (recommended) amalgation version 3.6.12 does not contain the new .genfkey functionality, while the (not recommended) full version does. Is this on purpose? -- Best, Frank. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binary Format
Hi, from the website http://www.sqlite.org/oldnews.html: The file format for version 3.3.0 has changed slightly to support descending indices and a more efficient encoding of boolean values. SQLite 3.3.0 will read and write legacy databases created with any prior version of SQLite 3. But databases created by version 3.3.0 will not be readable or writable by earlier versions of the SQLite. The older file format can be specified at compile-time for those rare cases where it is needed. This semms the only change since 3.0 Martin Martin Pfeifle schrieb: > Hi, > we do use SQLite in a standardisation initiative and have to state > which binary file-format of sqlite is used. > Up to now, I was of the opinion that all sqlite versions 3.x use the same > binary sqlite file > format but only differ in the library functionality. > Can somebody confirm that the binary disk format does not change in 3.x or > can it change and > we have to say, we use the binary format of sqlite 3.5.4 for instance, or is > it enough to say > that we use 3.x as binary file format? > Best Martin > > > > ___ > 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] Binary Format
Hi, we do use SQLite in a standardisation initiative and have to state which binary file-format of sqlite is used. Up to now, I was of the opinion that all sqlite versions 3.x use the same binary sqlite file format but only differ in the library functionality. Can somebody confirm that the binary disk format does not change in 3.x or can it change and we have to say, we use the binary format of sqlite 3.5.4 for instance, or is it enough to say that we use 3.x as binary file format? Best Martin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
