[sqlite] Include a double quote inside a string-literal
Hi! Is it possible to include a \" (double quote) inside a string-literal? I wanted to use a string-literal like "this is \"not working\"" but sqlite's SQL parser doesn't seem to accept this. Is it the intended behaviour or did I do something wrong? Thanks for your help. Aladdin _ Inédit ! Des Emoticônes Déjantées! Installez les dans votre Messenger ! http://www.ilovemessenger.fr/Emoticones/EmoticonesDejantees.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3 file format
Hi! I remember a message from DRH about SQLite's roadmap for 2008, stating that the file format specification would be explained and released in itself. Are you still working on that? Thank you and have a nice day, Aladdin _ Caroline vient de mettre à jour son profil Messenger ! Connectez-vous ! http://login.live.com/login.srf?wa=wsignin1.0=10=1198837564=4.0.1534.0=MBI=http:%2F%2Fhome.services.spaces.live.com%2F=1036=73625 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
> From: [EMAIL PROTECTED] > Date: Fri, 30 May 2008 20:26:14 +0200 > Would sqlite3_analyzer work for you? > It produces both a human readable report as well as a table > definition and insert statements to feed to sqlite3 command > line tool. Where can we download the source of this tool "sqlite3_analyser"? (The precompiled binary is on the sqlite3 web site, download section). It seems to be an interesting reading to understand the sqlite3 file format. Thanks, Aladdin _ Caroline vient de mettre à jour son profil Messenger ! Connectez-vous ! http://login.live.com/login.srf?wa=wsignin1.0=10=1198837564=4.0.1534.0=MBI=http:%2F%2Fhome.services.spaces.live.com%2F=1036=73625 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3Atoi64() and input string "0"
Hi! Just wanted to say that the sqlite3Atoi64() function doesn't seem to work
properly when zNum = "0", because the while( zNum[0]=='0' ){ zNum++; } skips
it, leading to an empty string and i == 0. Then, the test "if( c!=0 || i==0 ||
i>19 )" always return 0 (false), meaning that the conversion did not succeed...
Is that the intended behaviour?
Bye,
Aladdin
SQLITE_PRIVATE int sqlite3Atoi64(const char *zNum, i64 *pNum){
i64 v = 0;
int neg;
int i, c;
while( isspace(*(u8*)zNum) ) zNum++;
if( *zNum=='-' ){
neg = 1;
zNum++;
}else if( *zNum=='+' ){
neg = 0;
zNum++;
}else{
neg = 0;
}
while( zNum[0]=='0' ){ zNum++; } /* Skip over leading zeros. Ticket #2454 */
for(i=0; (c=zNum[i])>='0' && c<='9'; i++){
v = v*10 + c - '0';
}
*pNum = neg ? -v : v;
if( c!=0 || i==0 || i>19 ){
/* zNum is empty or contains non-numeric text or is longer
** than 19 digits (thus guaranting that it is too large) */
return 0;
}else if( i
_
Retouchez, classez et partagez vos photos gratuitement avec le logiciel Galerie
de Photos !
http://www.windowslive.fr/galerie/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_mprintf() best practice
Peeking at the SQLite source code, I see different usage pattern or the
sqlite3_mprintf() function:
- sqlite3_mprintf("direct static string without %");
- sqlite3_mprintf("%s", zString);
What's the recommended usage and best practice for this function?
I think that using the sqlite3_mprintf(zString) function on an untrusted
string, could lead to a security problem (buffer overflow) in case zString
*could* contain some "%..." format strings, and the - normal - practice should
be:
- use sqlite3_mprintf("%s", zString); when the string could be provided by user
code (and may contain format strings)
- use sqlite3_mprintf("direct static string without %"); when we are absolutely
sure that the string cannot, in any situation, contain format strings.
Is that all or are there other considerations to take into account?
Thanks and have a nice day,
Aladdin
_
Caroline vient de mettre à jour son profil Messenger ! Connectez-vous !
http://login.live.com/login.srf?wa=wsignin1.0=10=1198837564=4.0.1534.0=MBI=http:%2F%2Fhome.services.spaces.live.com%2F=1036=73625
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing virtual tables
Nobody? Did I make myself clear or do you need more (or maybe less!) explanations? Thanks, Aladdin > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Sat, 17 May 2008 16:41:49 +0200 > Subject: [sqlite] Indexing virtual tables > > > Hi! Here is what I'm still trying to achieve: > - I have a custom file format having "records" and file offsets. > - Each record in that custom file format has the same number of fields, but > the records itself are variable length, that's why I need a file offset to > quickly locate a record. One other way (if you can wait for a very long > time...) is to walk sequentially the records list to get the desired record. > - I've implemented a working SQLite "virtual table" in order to be able to > read and query my custom file format through SQLite. > - Now, basically what I'd like would be to "CREATE INDEX" on a field of my > virtual table to take advantage of it in the xBestIndex callback. But the > documentation says that we cannot use "CREATE INDEX" on virtual tables. > > Let's say the data in the field "F1" of my virtual table "VFILE", and the > file offsets are the following: > F1 fileoffset > -- > a 10 > b 21 > z 34 > x 45 > a 51 > x 69 > z 73 > a 88 > x 94 > > I want to index the column F1, to be able to have a quick response to queries > like: > select * from VFILE where F1='x' > > At this point, I think I have only 3 possible strategies: > 1. Use SQLite tables to "fake" a standard index using SQLite tables > 2. Use internal SQLite B-Tree routines to implement my index > (sqlite3BtreeCreateTable and stuff) > 3. Implement my own B-Tree and sort algorithms to achieve this, externally to > SQLite > > Strategy 3 is precisely what I'm trying to avoid (too much work and testing > :-) ). > Strategy 2 is strongly discouraged by DRH. > > Then strategy 1 seems to be (like you've just said) the only way to go: > > a) Duplicate the data to be indexed (and the file offsets to use) > > create table INDEX1_SORT as select F1, fileoffset from VFILE order by F1, > fileoffset > > INDEX1_SORT: physical table > F1 fileoffset > -- > a 10 > a 51 > a 88 > b 21 > x 45 > x 69 > x 94 > z 34 > z 73 > > b) Create an index on that data > > OPTION 1: Use SQLite CREATE INDEX at this point. > b.1.1) create index on INDEX1_SORT(F1) > > OPTION 2: Fake index with custom tables > b.2.1) create table INDEX2_SUM as select F1, min(INDEX1_SORT.rowid) as > 'minrow', max(INDEX1_SORT.rowid) as 'maxrow' from INDEX1_SORT group by F1 > > INDEX2_SUM: physical table > F1 minrow maxrow > --- > a 1 3 > b 4 4 > x 5 7 > z 8 9 > > b.2.2) create index on INDEX_2_SUM(F1) > > > * > > Usage for option 2: > - Use INDEX2_SUM to fetch the requested value in the initial query (select * > from VFILE where F1='x') > - Get data from table INDEX1_SORT between rowid "minrow" (5) and "maxrow" (7) > - For each line, use the given file offset to locate the real data in the > custom file format file. > - Read 3 records at fileoffet = 45,69,94 and return them to SQLite. > > I really feel like all this is not very optimal. > What is the best strategy to achieve optimal speed and needed storage? > Am I missing a trivial point? > > Thank you for any help on that! > Aladdin > >> Date: Mon, 12 May 2008 15:37:22 -0700 >> From: [EMAIL PROTECTED] >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Indexing virtual tables >> >> I'm not quite clear on your question - why wouldn't you just create >> any indices you need within the virtual-table implementation itself? >> Sort of like how fts uses SQLite tables to implement data-storage for >> the full-text index. >> >> -scott >> >> >> On Mon, May 5, 2008 at 10:13 AM, Aladdin Lampé wrote: >>> >>> Just thinking again about indexing strategies on virtual tables, I'm >>> wondering why virtual tables could not be indexed using the "normal" SQLite >>> command "INDEX". Indeed, I just expected that the data inside the column of >>> the virtual table could be sequentially scanned (using the "xColumn" >>> callback), producing the same result as if it were a real table. Is that >>> way of seeing things flawed? >>> >>> Any hook allowing to use SQLite internal indexing techniques for virtual >>> tables? Maybe using direct b-tree manipulatio
[sqlite] Indexing virtual tables
Hi! Here is what I'm still trying to achieve: - I have a custom file format having "records" and file offsets. - Each record in that custom file format has the same number of fields, but the records itself are variable length, that's why I need a file offset to quickly locate a record. One other way (if you can wait for a very long time...) is to walk sequentially the records list to get the desired record. - I've implemented a working SQLite "virtual table" in order to be able to read and query my custom file format through SQLite. - Now, basically what I'd like would be to "CREATE INDEX" on a field of my virtual table to take advantage of it in the xBestIndex callback. But the documentation says that we cannot use "CREATE INDEX" on virtual tables. Let's say the data in the field "F1" of my virtual table "VFILE", and the file offsets are the following: F1 fileoffset -- a 10 b 21 z 34 x 45 a 51 x 69 z 73 a 88 x 94 I want to index the column F1, to be able to have a quick response to queries like: select * from VFILE where F1='x' At this point, I think I have only 3 possible strategies: 1. Use SQLite tables to "fake" a standard index using SQLite tables 2. Use internal SQLite B-Tree routines to implement my index (sqlite3BtreeCreateTable and stuff) 3. Implement my own B-Tree and sort algorithms to achieve this, externally to SQLite Strategy 3 is precisely what I'm trying to avoid (too much work and testing :-) ). Strategy 2 is strongly discouraged by DRH. Then strategy 1 seems to be (like you've just said) the only way to go: a) Duplicate the data to be indexed (and the file offsets to use) create table INDEX1_SORT as select F1, fileoffset from VFILE order by F1, fileoffset INDEX1_SORT: physical table F1 fileoffset -- a 10 a 51 a 88 b 21 x 45 x 69 x 94 z 34 z 73 b) Create an index on that data OPTION 1: Use SQLite CREATE INDEX at this point. b.1.1) create index on INDEX1_SORT(F1) OPTION 2: Fake index with custom tables b.2.1) create table INDEX2_SUM as select F1, min(INDEX1_SORT.rowid) as 'minrow', max(INDEX1_SORT.rowid) as 'maxrow' from INDEX1_SORT group by F1 INDEX2_SUM: physical table F1 minrow maxrow --- a 1 3 b 4 4 x 5 7 z 8 9 b.2.2) create index on INDEX_2_SUM(F1) * Usage for option 2: - Use INDEX2_SUM to fetch the requested value in the initial query (select * from VFILE where F1='x') - Get data from table INDEX1_SORT between rowid "minrow" (5) and "maxrow" (7) - For each line, use the given file offset to locate the real data in the custom file format file. - Read 3 records at fileoffet = 45,69,94 and return them to SQLite. I really feel like all this is not very optimal. What is the best strategy to achieve optimal speed and needed storage? Am I missing a trivial point? Thank you for any help on that! Aladdin > Date: Mon, 12 May 2008 15:37:22 -0700 > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Indexing virtual tables > > I'm not quite clear on your question - why wouldn't you just create > any indices you need within the virtual-table implementation itself? > Sort of like how fts uses SQLite tables to implement data-storage for > the full-text index. > > -scott > > > On Mon, May 5, 2008 at 10:13 AM, Aladdin Lampé wrote: >> >> Just thinking again about indexing strategies on virtual tables, I'm >> wondering why virtual tables could not be indexed using the "normal" SQLite >> command "INDEX". Indeed, I just expected that the data inside the column of >> the virtual table could be sequentially scanned (using the "xColumn" >> callback), producing the same result as if it were a real table. Is that way >> of seeing things flawed? >> >> Any hook allowing to use SQLite internal indexing techniques for virtual >> tables? Maybe using direct b-tree manipulation (even if I know it's not >> recommended)? I'm not very keen on developing my own from stratch. Dealing >> with "big" tables that don't fit into memory does not seem so easy because >> I'll have to use a temporary disk file... >> >> Some help would be greatly appreciated! >> Aladdin >> >> __
[sqlite] Indexing virtual tables
Just thinking again about indexing strategies on virtual tables, I'm wondering why virtual tables could not be indexed using the "normal" SQLite command "INDEX". Indeed, I just expected that the data inside the column of the virtual table could be sequentially scanned (using the "xColumn" callback), producing the same result as if it were a real table. Is that way of seeing things flawed? Any hook allowing to use SQLite internal indexing techniques for virtual tables? Maybe using direct b-tree manipulation (even if I know it's not recommended)? I'm not very keen on developing my own from stratch. Dealing with "big" tables that don't fit into memory does not seem so easy because I'll have to use a temporary disk file... Some help would be greatly appreciated! Aladdin _ Faites vous de nouveaux amis grâce à l'annuaire des profils Messenger ! http://home.services.spaces.live.com/search/?page=searchresults=true=AdvPeopleSearch=SPXFRM=3=2=0=Nom+public===Pr%C3%A9nom=Nom=Lieu=Profession=amis=Rechercher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual tables declaration statements
Hi Fred, I agree with you but I think that a specific rule could be added in the parser in order to deal with an identifier containing dots only when it is used after a "using" in a "create virtual table" statement... Am I wrong? Anyway, this is not a very big issue :-) Aladdin > Date: Mon, 5 May 2008 09:59:52 -0500 > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Virtual tables declaration statements > > Don't think that will happen. "Dot" notation is used as in > "databasename.tablename" and is therefore a restricted use notation. > > Fred > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Aladdin Lampe > Sent: Monday, May 05, 2008 8:57 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Virtual tables declaration statements > > > > Hi list! > > I've just finished a set of sqlite3 virtual tables and I would like to name > them with a "dot notation", ie "data.source1", "data.source2" created like > that: > sqlite3_create_module(db, "data.source1", , 0); > sqlite3_create_module(db, "data.source2", , 0); > > ... and then use the following calling convention: > create virtual table tab1 using data.source1(arg1, arg2); > create virtual table tab2 using data.source2(arg1, arg2, arg3); > > ... but the parser claims a syntax error near "." :-( > > Note that the following three notations just work, but I find them a bit > weird and not very user-friendly: > create virtual table tab1 using 'data.source1'(arg1, arg2); > create virtual table tab1 using "data.source1"(arg1, arg2); > create virtual table tab1 using [data.source1](arg1, arg2); > > Is there any chance that this would be solved in a future version of SQLite, > ie considering adding the "." to the authorized characters in an identifier > following the key word "using"? > > BTW, I do not see lots of virtual tables out there. I find it's a really > great and distinctive feature and I think that, following the recent > tutorial ideas, our SQLite community should set up a sharepoint with users > contributions (just like the "/contrib" area, but in a more user friendly, > collaborative and "visible" way). Same thing for user-defined functions, > aggregates, collations... > That would help people starting with sqlite and allow us to build some > useful tools/examples that everybody could use or inspire of... > > Have a nice day, > Aladdin > > _ > Retouchez, classez et partagez vos photos gratuitement avec le logiciel > Galerie de Photos ! > http://www.windowslive.fr/galerie/ > ___ > 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 _ Avec Hotmail, vos e-mails vous suivent partout ! Mettez Hotmail sur votre mobile ! http://www.messengersurvotremobile.com/?d=hotmail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual tables declaration statements
Hi list! I've just finished a set of sqlite3 virtual tables and I would like to name them with a "dot notation", ie "data.source1", "data.source2" created like that: sqlite3_create_module(db, "data.source1", , 0); sqlite3_create_module(db, "data.source2", , 0); ... and then use the following calling convention: create virtual table tab1 using data.source1(arg1, arg2); create virtual table tab2 using data.source2(arg1, arg2, arg3); ... but the parser claims a syntax error near "." :-( Note that the following three notations just work, but I find them a bit weird and not very user-friendly: create virtual table tab1 using 'data.source1'(arg1, arg2); create virtual table tab1 using "data.source1"(arg1, arg2); create virtual table tab1 using [data.source1](arg1, arg2); Is there any chance that this would be solved in a future version of SQLite, ie considering adding the "." to the authorized characters in an identifier following the key word "using"? BTW, I do not see lots of virtual tables out there. I find it's a really great and distinctive feature and I think that, following the recent tutorial ideas, our SQLite community should set up a sharepoint with users contributions (just like the "/contrib" area, but in a more user friendly, collaborative and "visible" way). Same thing for user-defined functions, aggregates, collations... That would help people starting with sqlite and allow us to build some useful tools/examples that everybody could use or inspire of... Have a nice day, Aladdin _ Retouchez, classez et partagez vos photos gratuitement avec le logiciel Galerie de Photos ! http://www.windowslive.fr/galerie/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] NUMERIC affinity and unwanted conversions
Hi! I've just created my own "DECIMAL" set of user functions using the IBM decNumber library. I would like to assign my decimal columns the type "DECIMAL([precision])", but they are given the NUMERIC affinity, and numbers are converted when inserted in TEXT format... That's exactly what I'm trying to prevent because this would mess the DECIMAL results! I've thought to name my decimal type "DECIMAL_TEXT([precision])" in order to force the TEXT affinity (no conversion) but I'm not very pleased with this solution. Does somebody have a better one? Could somebody point me where to patch SQLite in order to avoid that? Thank you, Aladdin _ Découvrez les profils Messenger de vos amis ! http://home.services.spaces.live.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
Thank you Nicolas for your answer. I understand that an int64 certainly gives me enough precision in the general case. Now what am I supposed to do if the user decides to add a virtual 4 decimal digits number to another number which has only 2 decimal digits? I should first identify this and then multiply by 100 the second number in order to be able to use sqlite built-in operators and functions... Not so simple, I think... And in my case, it is the user who determines the precision he desires for each number within the application, so I cannot have the precision fixed once for all like you suggest. Aladdin > Date: Sun, 13 Apr 2008 13:41:46 -0500 > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Dealing with monetary huge values in sqlite > > On Sun, Apr 13, 2008 at 07:37:33PM +0200, Aladdin Lampé wrote: >> In my opinion (please tell me if I'm wrong), your method only works if >> you want to *display* the values in your column, and if the decimal >> precision doesn't change form line to line. >> >> I would like to be able to perform operations (+, /, %, etc.) and to >> store intermediary results into other columns (such as x% of the >> value, etc.), which may have an arbitrary precision, and - only at the >> end - round the result to cents/pennies etc. This is required into the >> technical specifications provided by my client, because high precision >> matters for them. Indeed, an error of 0.01$ on 10,000,000 lines would >> lead to a significant error at the end... > > The proposed method allows you do perform arithmetic operations using > SQLite's built-in operators and functions. > > If the greatest error your customer is willing to accept is, say, one > part in a million (that is, a millionth of a cent), then using 64-bit > would allow you to represent values up to ~ 10e13 -- 10 trillion, not so > much, but perhaps good enough for you. > > If the tolerance is more like one in 10,000 ($0.001), then you can > represent up to ~ 1,000 trillion as the largest value. That's still > pretty small, IMO, but almost certainly good enough for you. > > You can adjust where you put the virtual fixed point. If you can't find > a suitable break then you should consider your arbitrary precision > extended function function scheme (or a database engine that provides > the features you need). > > Nico > -- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Créez votre disque dur virtuel Windows Live SkyDrive, 5Go de stockage gratuit ! http://www.windowslive.fr/skydrive/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with monetary huge values in sqlite
In my opinion (please tell me if I'm wrong), your method only works if you want to *display* the values in your column, and if the decimal precision doesn't change form line to line. I would like to be able to perform operations (+, /, %, etc.) and to store intermediary results into other columns (such as x% of the value, etc.), which may have an arbitrary precision, and - only at the end - round the result to cents/pennies etc. This is required into the technical specifications provided by my client, because high precision matters for them. Indeed, an error of 0.01$ on 10,000,000 lines would lead to a significant error at the end... Any help appreciated. I really wouldn't have to propose another software than SQLite for this job... even if I have to reimplement from scratch BCD numbers for the sqlite engine! ;-) Aladdin > Date: Sun, 13 Apr 2008 17:20:03 +0100 > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Dealing with monetary huge values in sqlite > > On Sun, Apr 13, 2008 at 06:13:04PM +0200, Aladdin Lampé wrote: >> Maybe I could also implement my own floating point format, based for >> instance on an int64 and a way to tell where the decimal point is. But in >> this case I would need to reimplement addition, division, etc. to deal >> with the position of the decimal point, and this may have a negative >> impact on performance and a storage overhead. > > Why not just express all money values in terms of the smallest division of > the currency? For example, for dollars, use cents, for pounds use pennies. > > Sqlite has up to 8 byte integers which would allow for all reasonable > values. > > -- > Two hands working can do more than a thousand clasped in prayer > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Recevez tous vos e-mails sur un seul compte ! Créez une adresse Hotmail ! http://www.windowslive.fr/hotmail/default.asp ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Dealing with monetary huge values in sqlite
Hi all! In the application I'm developing, I'm dealing with monetary values that may become *very* huge (but with few decimals), and I have the feeling that the sqlite representation of "REAL" numbers with 8 bytes doubles may not be enough in my case, and I may get overflows (which would obviously be unacceptable in my case :-) ). Following the recent thread "BCD representation of float" ( http://thread.gmane.org/gmane.comp.db.sqlite.general/36787/focus=36803 ), I thought that maybe a custom implementation of BCD would be a possible solution in my case. Maybe I could also implement my own floating point format, based for instance on an int64 and a way to tell where the decimal point is. But in this case I would need to reimplement addition, division, etc. to deal with the position of the decimal point, and this may have a negative impact on performance and a storage overhead. Do you think BCD is the way to go for me? Maybe somebody has already solved this problem (John Stanton?) and could share a possible solution? Thank you for any insight. I'm very interested in pointers or source code dealing about that... :-) Aladdin _ Votre contact a choisi Hotmail, l'e-mail ultra sécurisé. Créez un compte gratuitement ! http://www.windowslive.fr/hotmail/default.asp ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to disable journaling & rollback?
Phil, In order to disable totally journaling, I think (to be confirmed by real sqlite experts though) that you could patch each 4 calls to the function: int sqlite3BtreeFactory(const sqlite3 *db, const char *zFilename, int omitJournal, int nCache, int flags, Btree **ppBtree); using always 1 as the third parameter (omitJournal) instead of 0 in the following files: - attach.c (line 136) - build.c (line 3212) - main.c (line 1025) (- and in vdbe.c (line 2611) but omitJournal argument already equals 1 in this file) Keep us informed if this works. Aladdin > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Fri, 11 Apr 2008 17:45:35 -0500 > Subject: Re: [sqlite] Any way to disable journaling & rollback? > > Holding commits with a timeout is a feasible solution. However, in my > application it is somewhat complex to implement. Multiple threads are > accessing the database, and read requests usually run in a different thread > than writes. I don't want reads to be blocked while a commit timeout waits, > so a read would have to force a commit. I don't think one thread can commit > transactions for another thread, so I would have to set up an inter-thread > queueing system to allow readers to notify writers in different threads that > commits need to be done. Since commits are done in multiple places, this > approach will get messy fast. > > I think removing the FlushFileBuffers call (which is normally done on every > commit) is the best solution. It is not as fast as totally turning off > journaling, but it allows my program to run 20 times faster without the > complexity of trying to hold commitments. Also, journaling _is_ being done > (just not forced to the disk). So if my application crashes but Windows > continues to run, the journaling will eventually get flushed from Windows > cache to disk, and it should be available for a rollback. > > If I can figure out how to totally turn off journaling, I will do some > timing tests to see how much that speeds things up. It certainly won't be a > factor of 20, but a factor of 2 is possible. > > A new pragma "journaling=[off|on]" would be nice. > > Key wrote: > > Removing the journalling will certainly cause you lots of grief in the event > of a "crash"... > > You could do the following, > The write code (inserts) will queue incoming data into an > "array/storage in memory etc..." > When the first row is captured set a timer. > When either the timer expires or you reach a row limit threshold, > write the data to sqlite in a batched transaction. > > This way you get good performance and reliablity! But if you code crashes > and > you don't keep persistent what was in memory you'll loose that data. > You'll probably need to do some tuning of the timer/row limits to get a > balance between performance > and potential data loss. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Avec Hotmail, vos e-mails vous suivent partout ! Mettez Hotmail sur votre mobile ! http://www.messengersurvotremobile.com/?d=hotmail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is performance of v3.5.7 improved with new bitvec?
Hi all! Following the recent thread "Virtual tables used to query big external database", and the discussion with Mike Owens and Jay A. Kreibich, it seems that : - The "old" way of dealing with dirty pages with bitmaps limited SQLite to an approximate maximal capacity of 10s of GBs, as opposed to therical TBs, because it imposed to malloc 256 bytes for every 1Mb of database during each transaction. - The "new" way of dealing with dirty pages with a bitvec structure (introduced in SQLite v3.5.7) allows for sparse bitmaps and is then supposed to push away the "10s of GBs" limit. Now the questions are: 1) What are the new practical limits with SQLite v3.5.7? 2) Does somebody have any real-life experience (or home-made tests and figures) on SQLite v3.5.7 and really big tables? (say 100 000 000 lines). 3) Does the new "bitvec" algorithm really help with such a big table? I am mainly interested in performance of INSERTs (for creating the big table) and SELECTs (for queries). UPDATEs, DROPs, TRIGGERs etc. have a lower priority in my case. Those questions are really important for me because if SQLite is now able to handle really big tables, I no longer need to implement my own "virtual table" in order to link SQLite to a "big external database"... because I could directly use SQLite itself for the whole application! (no virtual table and no "external" database needed). Thank you for any help about that subject. Have a nice day, Aladdin _ Découvrez les profils Messenger de vos amis ! http://home.services.spaces.live.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access to Btree routines in SQLite
Hi Phil and list!
Thank you for this very instructive post about SQLite's internals and btrees.
I'm just curious about what could be real-life use cases of having direct
access to the btree stuff. As I understand your example, you store (key,value)
pairs inside the btree and then get them back. Then, what's the purpose of not
using a normal SQLite table to do so?
On the other hand, since those "direct btree information" are necessarily
stored inside a reguar SQLite file, doesn't this introduce possible side
effects with other functions of the sqlite library, which would not be aware
that some btree roots inside the file are neither a table nor an index?
(vacuum, etc.)
Last but not least, I am currently developping a virtual table and I've just
realized that I could use SQLite btrees for indexing data coming from an
"external database" (cf recent thread about virtual tables and access to big
external databases).
According to you, would it be a good idea to use this technique in order to
implement an alternative indexing technique (for "external tables") based on
SQLite btrees?
Thanks a lot for sharing about that, any help would be greatly appreciated,
Aladdin
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Mon, 7 Apr 2008 19:28:30 -0500
> Subject: [sqlite] Direct access to Btree routines in SQLite
>
> I am developing an application that needs both SQL capability and also
> simple Btree functions. I searched the SQLite FAQ for information about
> direct access to the Btree level, but was unable to find any help or
> examples. I have successfully gained access to the Btree routines after
> making minor changes to the amalgamation source which I then compiled with
> MS Visual Studio and linked with my application.
>
> Note: Since the Btree routines are only semi-documented, and my procedure
> requires changes to the SQLite source, there is no guarantee that this will
> work with future versions.
>
> Changes to SQLite amalgamation code:
>
> 1. Insert the following definition to make the routines externally callable
> (rather than static):
>
> #define SQLITE_PRIVATE
>
> 2. Extract the embedded Btree.h header file from the amalgamation and create
> a Btree.h file. The embedded section to be extracted is enclosed in:
> _BTREE_H_ Use the Btree.h file with your application along with sqlite3.h.
>
> 3. Add the following routine which returns a pointer to the Btree structure
> connected to a specified database handle:
>
> /*--
>
> * Get a pointer to the Btree structure associated with an entry in the
> database table.
> */
> int sqlite3GetBtreePointer(sqlite3 *db, int dbIndex, Btree **pBt) {
> Db *pDb;
> /*
> * Get the Btree handle out of the database table.
> */
> pDb = >aDb[dbIndex];
> *pBt = pDb->pBt;
> /*
> * Finished
> */
> return(SQLITE_OK);
>
> -- This is all the changes required to SQLite --
>
> Here is an example program that creates a database with a Btree table,
> writes a couple of records to it and then verifies that it can be accessed.
> The routine can be called to either create a new database or open and check
> an existing one.
>
> /*---
> * Btree test.
> *
> * Input arguments:
> * Create = true to create a new database with a Btree table.
> * Create = false to open an existing database and check it.
> */
> void BtreeTest(bool Create)
> {
> static char *FileName = "C:\\Test\\Btree.db";
> int status,Result;
> unsigned int DataSize;
> Btree *bt;
> sqlite3 *db;
> BtCursor *BtCursor;
> sqlite3_stmt *pStmt;
> int BtPage = -1;
> char *Key1 = "1";
> char *Data1 = "Record 1";
> char *Key2 = "2";
> char *Data2 = "Record 2";
> char buf[100],Command[200];
>
> /*
> * Decide if the test run should create the database and table or open an
> existing one.
> */
> if (Create) {
> /*
> * Create a database with a Btree table.
> */
> /* Start with a new database */
> DeleteFile(FileName);
> /* Create a new database */
> status =
> sqlite3_open_v2(FileName,,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,0);
> /* Get a pointer to the Btree for the primary database (# 0) */
> status = sqlite3GetBtreePointer(db,0,);
> /* Create a Btree table within the database. We get back the root
> page in BtPage */
> status = sqlite3BtreeBeginTrans(bt,1);
> status = sqlite3BtreeCreateTable(bt,,0);
> status = sqlite3BtreeCommit(bt);
> /* Write the root page number of the Btree to a table so that we can
> find it later */
> status = sqlite3_exec(db,"CREATE TABLE Configuration (BtreeName TEXT
> UNIQUE, RootPage INTEGER)",0,0,0);
> sprintf(Command,"INSERT INTO Configuration VALUES
> (\'MyBtree\',%d)",BtPage);
> status = sqlite3_exec(db,Command,0,0,0);
> /* Create a cursor to go with the Btree (BtPage is the root page
> index #) */
> status = sqlite3BtreeCursor(bt,BtPage,1,0,0,);
> /* Write a couple of records to the Btree table */
> status =
Re: [sqlite] Virtual table used to query big external database
Thank you. That's exactly the mistake I did :-)> Date: Wed, 2 Apr 2008 09:11:44 -0400> From: [EMAIL PROTECTED]> To: sqlite-users@sqlite.org> Subject: Re: [sqlite] Virtual table used to query big external database> > Hi, Aladin,> > Regarding: > "Do you know where I can get the files "fs.c" and "example.c" you> mention? [ http://www.ddj.com/database/202802959 ] I've been on the FTP> (available from www.ddj.com/code/) but I was not able to locate..."> > > > The code appears to be in the December 2007 (0712.zip) file, and not the> 0711.zip as one might think.> > > > > > This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. > This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person.> ___> sqlite-users mailing list> sqlite-users@sqlite.org> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Découvrez les profils Messenger de vos amis ! http://home.services.spaces.live.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual table used to query big external database
Thanks a lot Mike for your detailed answer! I've just read your acticle. Do you know where I can get the files "fs.c" and "example.c" you mention? I've been on the FTP (available from www.ddj.com/code/) but I was not able to locate any file related to your article :( Could you please send them to me by email if they are no longer available online? (genio570 -AT- hotmail DOT fr). It seems like I will be able to further develop the system I described in my previous post with the information you mentioned. Another quick question: I planned to develop my own indexing algorithm on the "external database" side. But do you think I could also consider (as another option) using the "CREATE INDEX" sqlite command (involving of course a complete scan of the external table's column and then storing the index inside the sqlite file)? What would you recommend me to do? In a more general way, I wonder which sqlite SQL commands can involve virtual tables and which cannot or should not (for instance for performance reasons, etc.). Thanks again for your help, aladdin> Date: Tue, 1 Apr 2008 20:17:54 -0500> From: [EMAIL PROTECTED]> To: sqlite-users@sqlite.org> Subject: Re: [sqlite] Virtual table used to query big external database> > The main reason why SQLite's practical limit is in the 10s of GBs as> opposed to TBs (theoretical) is due to how it tracks dirty pages. This> is described in the "Appropriate Uses" page> (http://www.sqlite.org/whentouse.html) but I'll rehash it here for> convenience. SQLite tracks dirty pages with a bitmap which is> allocated before each transaction. The size of the bitmap is> proportional to the size (not in rows but in pages) of the database> (256 bytes for every 1Mb of database), so as the database grows, the> amount of memory allocated before each transaction grows. When you get> into the GB range, you are starting to allocate in the MB range of> dirty page map memory per transaction, which starts to take its toll> on performance.> > I could be wrong, but from what I know about virtual tables, there is> no such correlation between virtual table size and the dirty page> bitmap, as SQLite has no idea how big a virtual table is, nor does it> manage the data within the vtable. Furthermore, all SQLite really does> in a SELECT statement on a vtable is call your code to iterate over> it. So really the only performance issue is how long it takes your> code to iterate over your vtable. Thus, your table could be in the TB> range, and as long as you are fine with iterating over its contents,> there is no additional performance issues to speak of. There are ways> to implement virtual tables such that you can limit how much of the> table is scanned for certain queries, avoiding having to scan the> whole thing every time. I wrote an article that touches on this using> the match() function. Its available online:> > http://www.ddj.com/database/202802959> > IMO, virtual tables are one of the most powerful and unique features> of SQLite. There is a bit of a learning curve, but it's amazing what> you can do with them. It sounds like you going to have to iterate over> your external table one way or the other. I see no reason why the> vtable approach would be any slower than any other approach that> iterates over the data.> > Having said that, while iterating over a large vtable is not a big> deal (as your program will just step through it one row at a time),> you need to be careful about getting too fancy with your SQL as you> may end up triggering a lot of background IO. For example, if you tack> on an ORDER BY which sorts one of the columns of your vtable, SQLite> will end up essentially copying the vtable contents into a temporary> file and sorting it, which may or may not be a strain on your system> depending on how big your table is (e.g. your vtable is 30Gb and your> /tmp folder is on a 10Gb partition). So think through what you are> doing when going beyond a simple SELECT * from big_vtable.> > -- Mike> > On Tue, Apr 1, 2008 at 3:12 PM, Aladdin Lampé <[EMAIL PROTECTED]> wrote:> >> > Hi all!> >> > Very often, when people ask this list why they have trouble managing in sqlite a "big table" (50 million lines or more than 10 Go), they are told that sqlite is an embedded database and is not meant to be used for very big databases/tables.> >> > I'm currently in the process of designing a specific, read-only, sqlite "virtual table" in order to enable sqlite to access data stored in an external database which is specially designed to handle very big tables.> >> > My final objective is to be able to easily query a big external table (stored in another database) throu
[sqlite] Virtual table used to query big external database
Hi all! Very often, when people ask this list why they have trouble managing in sqlite a "big table" (50 million lines or more than 10 Go), they are told that sqlite is an embedded database and is not meant to be used for very big databases/tables. I'm currently in the process of designing a specific, read-only, sqlite "virtual table" in order to enable sqlite to access data stored in an external database which is specially designed to handle very big tables. My final objective is to be able to easily query a big external table (stored in another database) through the - excellent - sqlite interface. Now I have this terrible doubt: will the existing sqlite "limitations" for big sqlite tables also apply to my read-only virtual tables? Thus... am I currently losing my time developing such a "virtual table" with this objective in mind? Or is there a better way to achieve my objective? Thank you for your help! _ Votre contact a choisi Hotmail, l'e-mail ultra sécurisé. Créez un compte gratuitement ! http://www.windowslive.fr/hotmail/default.asp ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Which built-in functions are mandatory?
Hi sqlite-users! In other terms, which built-in functions can I safely remove (or rename) without getting internal errors? I hope I can remove or rename all of them without compilation or execution errors... What do you think? Thanks a lot! Here's the list! ;-) min FUNCTION max FUNCTION typeof FUNCTION length FUNCTION substr FUNCTION abs FUNCTION round FUNCTION upper FUNCTION lower FUNCTION coalesce FUNCTION hex FUNCTION ifnull FUNCTION random FUNCTION randomblob FUNCTION nullif FUNCTION sqlite_version FUNCTION quote FUNCTION last_insert_rowid FUNCTION changes FUNCTION total_changes FUNCTION replace FUNCTION ltrim FUNCTION rtrim FUNCTION trim FUNCTION zeroblob FUNCTION soundex FUNCTION load_extension FUNCTION min AGGREGATE max AGGREGATE sum AGGREGATE total AGGREGATE avg AGGREGATE count AGGREGATE _ Vous êtes plutôt Desperate ou LOST ? Personnalisez votre PC avec votre série TV préférée ! http://specials.divertissements.fr.msn.com/SeriesTV.aspx - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Change the concatenation operator
[Sorry for the formatting problem of my previous message, here is another try
with no "rich-text"]
Hi all, I've got 3 little questions for sqlite experts!
1. How can I change the SQL concatenation operator '||' to '..' in order to
have a better integration with the Lua programming language?
2. Is it possible to change only the function "static int getToken(const
unsigned char *z, int *tokenType)" in "tokenize.c"? or, even better, in the
amalgamation file? I presume it's not enough (parse.y and stuff?) but I'm
searching for a very simple solution...
(contents of function getToken in tokenize.c, untested patch with "removed" and
"added" tags)
[...]
case '|': {
if( z[1]!='|' ){
*tokenType = TK_BITOR;
return 1;
}
// I may not remove those lines in order to keep the default behaviour...
//removed else{
//removed*tokenType = TK_CONCAT;
//removedreturn 2;
//removed }
}
[...]
case '.': {
if( z[1]=='.' ){ //added
*tokenType = TK_CONCAT; //added
return 2; //added
} else //added
#ifndef SQLITE_OMIT_FLOATING_POINT
if( !isdigit(z[1]) )
#endif
{
*tokenType = TK_DOT;
return 1;
}
/* If the next character is a digit, this is a floating point
** number that begins with ".". Fall thru into the next case */
}
3. May this modification introduce parse errors I can't even imagine right now?
Thanks a lot if you can help me!
Aladdin
_
Retrouvez Windows Live Messenger sur votre mobile !
http://www.messengersurvotremobile.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Change the concatenation operator
Hi all, I've got 3 little questions for sqlite experts! 1. How can I change the
SQL concatenation operator '||' to '..' in order to have a better integration
with the Lua programming language? 2. Is it possible to change only the
function "static int getToken(const unsigned char *z, int *tokenType)" in
"tokenize.c"? or, even better, in the amalgamation file? I presume it's not
enough (parse.y and stuff?) but I'm searching for a very simple solution...
(contents of function getToken in tokenize.c, untested patch with "removed" and
"added" tags)[...]case '|': { if( z[1]!='|' ){*tokenType =
TK_BITOR;return 1; }// I may not remove those lines in order to
keep the default behaviour...//removed else{//removed*tokenType =
TK_CONCAT;//removedreturn 2;//removed }}[...]case '.':
{if( z[1]=='.' ){ //added*tokenType = TK_CONCAT;
//addedreturn 2; //added} else
//added#ifndef SQLITE_OMIT_FLOATING_POINT if( !isdigit(z[1]) )#endif
{*tokenType = TK_DOT;return 1; } /* If the next
character is a digit, this is a floating point ** number that begins with
".". Fall thru into the next case */}3. May this modification introduce
parse errors I can't even imagine right now? Thanks a lot if you can help
me!Aladdin
_
Vous êtes plutôt Desperate ou LOST ? Personnalisez votre PC avec votre série TV
préférée !
http://specials.divertissements.fr.msn.com/SeriesTV.aspx
