[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
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.0rpsnv=10ct=1198837564rver=4.0.1534.0wp=MBIwreply=http:%2F%2Fhome.services.spaces.live.com%2Flc=1036id=73625 ___ 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.0rpsnv=10ct=1198837564rver=4.0.1534.0wp=MBIwreply=http:%2F%2Fhome.services.spaces.live.com%2Flc=1036id=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 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=searchresultsss=trueFormId=AdvPeopleSearchform=SPXFRMtp=3sc=2pg=0Search.DisplayName=Nom+publicsearch.gender=search.age=Search.FirstName=Pr%C3%A9nomSearch.LastName=Nomsearch.location=Lieusearch.occupation=Professionsearch.interests=amissubmit=Rechercher ___ sqlite-users mailing
[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.0rpsnv=10ct=1198837564rver=4.0.1534.0wp=MBIwreply=http:%2F%2Fhome.services.spaces.live.com%2Flc=1036id=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 ||
i19 ) 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 || i19 ){
/* 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] 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 _ Faites vous de nouveaux amis grâce à l'annuaire des profils Messenger ! http://home.services.spaces.live.com/search/?page=searchresultsss=trueFormId=AdvPeopleSearchform=SPXFRMtp=3sc=2pg=0Search.DisplayName=Nom+publicsearch.gender=search.age=Search.FirstName=Pr%C3%A9nomSearch.LastName=Nomsearch.location=Lieusearch.occupation=Professionsearch.interests=amissubmit
[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, dataModule1, 0); sqlite3_create_module(db, data.source2, dataModule2, 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
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, dataModule1, 0); sqlite3_create_module(db, data.source2, dataModule2, 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] 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=searchresultsss=trueFormId=AdvPeopleSearchform=SPXFRMtp=3sc=2pg=0Search.DisplayName=Nom+publicsearch.gender=search.age=Search.FirstName=Pr%C3%A9nomSearch.LastName=Nomsearch.location=Lieusearch.occupation=Professionsearch.interests=amissubmit=Rechercher ___ 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
[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] 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
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] 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
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 = db-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,db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,0);
/* Get a pointer to the Btree for the primary database (# 0) */
status = sqlite3GetBtreePointer(db,0,bt);
/* Create a Btree table within the database. We get back the root
page in BtPage */
status = sqlite3BtreeBeginTrans(bt,1);
status = sqlite3BtreeCreateTable(bt,BtPage,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,BtCursor);
/* Write a couple of records to the Btree table */
status = sqlite3BtreeBeginTrans(bt,1);
status =
sqlite3BtreeInsert(BtCursor,Key1,1,Data1,strlen(Data1)+1,0,0);
status =
[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] 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) 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
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
[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
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
[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]
-
