[sqlite] Hello
Hello this Testing for cuenta Gerard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding how SQLite works
<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I understand that the SQLite database resides in memory. Not usually, no. There's an in-memory cache of recently accessed pages, but most of the database (especially a large database) remains on disk. > Now lets say a database is 10Gb in size and it is written to disk. > Would not writing a 10Gb file to disk take a very long time? Probably. > Now perhaps SQLite can just write the part that has changed to disk. It can. > If this is the case, then how does it know which sectors on the hard > drive to update since it isn't writing the entire file to disk Well, that's the job of a DBMS - to know which rows have changed, and where in the file they should be stored. That's the whole point of the exercise. > Can someone explain to me how all of this work? See if this helps: http://sqlite.org/arch.html . And if you really want to know how _all_ of this works, you can always study the source code. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding how SQLite works
I understand that the SQLite database resides in memory. I understand that the information in memory gets written to disk, ie saving parts that have been updated/whole database. I have read that SQLite has been known to support up 100,000 concurrent read connections and can support several terabytes of data. Now lets say a database is 10Gb in size and it is written to disk. Would not writing a 10Gb file to disk take a very long time? Now perhaps SQLite can just write the part that has changed to disk. If this is the case, then how does it know which sectors on the hard drive to update since it isn't writing the entire file to disk Can someone explain to me how all of this work? Thanks, TD mail2web.com - Microsoft® Exchange solutions from a leading provider - http://link.mail2web.com/Business/Exchange ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Hello Word
Hello My Name is gerardo Cabero from Argetina.. im Aministrator for Sqlite - Latino [1] Saludos Gerardo Cabero [1] = sqlite-latino.blogspot.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
On Wed, Aug 27, 2008 at 03:50:56PM +0100, Hardy, Andrew wrote: > Is there any way to log conversions to highlite any issues that would > have been hilighted by failure with strict typing? If the conversions are nicely isolated into functions (static and otherwise) could use the DTrace 'pid' provider on Solaris, and probably on FreeBSD/MacOS X as well. If the conversions are not nicely isolated into functions then SQLite3 could be modified to define DTrace USDT probes that could then be used on Solaris/FreeBSD/MacOS X. I think it'd be cool to modify SQLite3 to define [unstable] USDT probes for all VM opcodes, at the very least, and stable USDT probes probes for statement compilation, execution, ... (If I needed these I'd contribute the code. But I don't, and don't have the time.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
The Sqlite manifest typing integrates nicely with scripting languages which use similar strategies. Where such integration is not required and there is a well defined application such as an embedded system, particularly with a slower processor, a static typing model would be advantageous in many cases. It would integrate cleanly with a strongly typed language. In a strongly typed environment type checking at run time is avoided and much overhead abolished. It would be Sqextralite to be embedded in the Algol/Pascal or C family languages. Dennis Cote wrote: > D. Richard Hipp wrote:> > >>I was going to guess the opposite - that manifest typing reduces >>overhead. (But as Dan pointed out - nobody will know until somebody >>generates a version of SQLite that uses static typing and compares the >>performance.) >> > > > I agree with Dan on this point. > > >>The reason I think static typing would make things slower is that with >>static typing, there has to be a bunch of checking during processing >>to verify the specified datatype is in use. With the current database >>file format, this checking must be done at query run-time. And there >>is no savings in not having to track the types of each data item at >>run-time because the current file format allows dynamic typing. So >>any "strict affinity" mode would likely be slower than the current >>SQLite. >> >>If you designed a new file format that did not allow dynamic typing at >>the file format layer, then you could perhaps do away with tracking of >>types at query run-time. But if you go with a completely new file >>format, you really wouldn't be dealing with SQLite any more. So I'm >>not sure the comparison would be valid. >> >>Note that if you really, really want to do static typing in SQLite you >>can implement it using CHECK constraints: >> >> CREATE TABLE ex(a INTEGER CHECK( typeof(a)='integer' )); >> >>A "strict affinity" mode in SQLite would amount to adding these check >>constraints automatically. If you look at it from this point of view, >>it seems likely that strict affinity would slow down performance due >>to the added cost of checking type constraints at each step. >> > > > I think the benefit of a static typing system is that those checks are > not done at all at run time. They are done once when the statement is > compiled. After that the code can be execute many millions of times > (i.e. for millions of rows) without the need for any type checking at > runtime because the compiler did the necessary checks. There is no need > for a check constraint as you have shown, since the compiler would only > generate code to insert integer values into integer columns. If only > integer values can be inserted, there is no need to check the type of > the values when they are retrieved (even if the file format supports > dynamic typing). Now, data pulled from the tables can be assumed to be > of the expected type and used directly. This may simplify subsequent > processing. > > The trade off is that the compiler may become more complex and the > compilation step may take longer. There may still be a net benefit if > the compilation time is only a small percentage of the statement's > execution time (i.e complex long running queries on large tables). > > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
When I modified Sqlite to block type conversions my memory of it is that the changes were quite simple and easy to implement. You could log the change events with a handful of patches to Sqlite. It would be an interesting exercise to analyze the impact of the conversions. On an aside I discovered a long time ago that analyzing radix changes in commercial type software indicated an enormous overhead which was fairly easily removed by holding numbers in display format whereever possible. Now faster processors make such optimizations less significant. Hardy, Andrew wrote: > Is there any way to log conversions to highlite any issues that would > have been hilighted by failure with strict typing? > > It is my plan to match the column type & data stored type, but clearly > if any conversions are occurring I will be unaware. > > Kind Regards > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote > Sent: 27 August 2008 15:45 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Manifest Typing performance impact? > > Hardy, Andrew wrote: > >> >>Is there a performance hit assosiated with manifest typing? >> > > > I'm sure there is since sqlite must track the type of each data item as > well as its value. But in reality this overhead is quite small. > > >>Is it right that although info on the sqlite site suggests there is an > > >>avilable mode that supports strict typring, this is not infact the > > case? > >> > > > That is correct, the strict affinity mode does not exist. > > >>If there is a performance hit, what are the best ways to minimise > > this? > >>And is there any way to at least log conversions to highlite any >>issues that would have been hilighted by failure with strict typing? >> > > > The best way to minimize the conversion overhead is to store the data in > suitably typed columns (i.e. that match the type of the data stored in > the column). This will avoid any unnecessary conversions when storing, > loading, or comparing the values. > > See http://www.sqlite.org/datatype3.html for the column type affinity > deduction rules. > > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
I made some changes to Sqlite to stop the automatic type conversions. The performance improvement was not significant. My reason was not to avoid the performance overhead but to prevent the use of floating point where it would raise precision problems. My suggestion would be not to worry about manifest typing as a perfomance drag. Dan wrote: > On Aug 27, 2008, at 4:03 PM, Hardy, Andrew wrote: > > >>Is there a performance hit assosiated with manifest typing? > > > Yes. No. Possibly. Difficult to tell unless somebody creates > and optimizes a version of sqlite that does not do manifest > typing. > > >>Is it right that although info on the sqlite site suggests there is an >>avilable mode that supports strict typring, this is not infact the >>case? > > > Correct. > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN
Brown, Daniel wrote: > Thanks for the clarification Roger, I guess it looks like I will need to > modify the compiler settings locally then. If you can modify the amalgamation source code, I would try updating sqlite3IsNan() to use the standard C isnan() macro. DRH commented in one of the tickets that isnan() is not used by default since it is not available on all platforms. (In addition, the custom IsNan removes a dependency on the standard math library.) Assuming that isnan() is available to you (and assuming that it works with --fast-math), you may be able to trade a tricky build-script change for a quick source code change. ~Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index memory usage in SQLite
On Wed, Aug 27, 2008 at 06:05:46PM +0100, Dave Toll scratched on the wall: > I'm currently using an 8000 page cache, and as far as I know my DB > should have been fully-cached before I created the indices (it was built > from scratch and queried several times). Ahh... that does change things a bit. > Does creating an index use extra pages on top of the > fully-cached DB and the index representation itself? I'm not sure. I'd guess not, but I'm not sure if the shifting of the b-tree might cause the page count to peak a bit higher than the final representation. I would suspect that would be possible but unlikely to be large, if anything. Perhaps the memory is some kind of temp store for the sort algorithm or something. Other can answer this better than I can. > I'll have to read up on VACUUM... There is cost associated with recovering free pages since they may not be at the end of the file. Releasing the pages requires "defragging" the database before truncating the file. Hence vacuum. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index memory usage in SQLite
Thanks Jay I'm currently using an 8000 page cache, and as far as I know my DB should have been fully-cached before I created the indices (it was built from scratch and queried several times). Disk usage is pretty much as I expected. Does creating an index use extra pages on top of the fully-cached DB and the index representation itself? I'll have to read up on VACUUM... Cheers, Dave. -Original Message- From: Jay A. Kreibich [mailto:[EMAIL PROTECTED] Sent: 27 August 2008 06:52 To: General Discussion of SQLite Database Subject: Re: [sqlite] Index memory usage in SQLite On Tue, Aug 26, 2008 at 06:13:29PM +0100, Dave Toll scratched on the wall: > I'm running some general performance tests on SQLite 3.5.9 (embedded C > platform), and I noticed that creating an index seems to use a lot more > memory than I expected. Creating an index requires reading and sorting the original table. It tends to beat the page cache fairly hard. This is a known condition. In fact, the first bit of advice to increase the speed of index creation is to make the page cache larger. > An index on an integer column (30495 rows) uses 1,011,560 bytes. > > DB file size increases by 311,296 bytes. Or about 10.2 bytes per item, which sounds pretty reasonable. An index in SQLite contains a full copy of the indexed data, so that plus a rowid reference back to the original table and other metadata makes 10 bytes sound about right, especially if most of the integers are smallish (SQLite uses var-length integers). > An index on a varchar column (average null-terminated text length 18 > bytes, 30495 rows) uses 2,180,040 bytes. > > DB file size increases by 856,064 bytes. Same thing. 28 bytes per item sounds reasonable, especially if the average data item is 18 bytes. Strings won't pack into pages quite as efficiently, so I'd expect a higher overhead. > I'm using the static memory allocator (mem3.c), page size 4096, and I > compiled with SQLITE_32BIT_ROWID. I measured the difference in memory > reported by sqlite3_memory_used(). > Dropping the index does not return any of the memory used, If you're measuring the process memory usage, that's normal. Free pages aren't returned to the OS. If you're measuring the allocator, chances are most of that memory is in the page cache, and will not be returned. The default page size is 1K and the default cache size is 2K with about 0.5K of overhead per page, meaning the default system expects about 3MB worth of cache. Neither of the examples you've given go over that, so unless you can figure out otherwise, I'd assume most of that is cache allocation. The cache will grow until it hits its max size but won't be returned. > and does not reduce the DB file size. That's normal. As with memory systems, free pages are not released unless you vacuum the database file. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
D. Richard Hipp wrote:> > I was going to guess the opposite - that manifest typing reduces > overhead. (But as Dan pointed out - nobody will know until somebody > generates a version of SQLite that uses static typing and compares the > performance.) > I agree with Dan on this point. > The reason I think static typing would make things slower is that with > static typing, there has to be a bunch of checking during processing > to verify the specified datatype is in use. With the current database > file format, this checking must be done at query run-time. And there > is no savings in not having to track the types of each data item at > run-time because the current file format allows dynamic typing. So > any "strict affinity" mode would likely be slower than the current > SQLite. > > If you designed a new file format that did not allow dynamic typing at > the file format layer, then you could perhaps do away with tracking of > types at query run-time. But if you go with a completely new file > format, you really wouldn't be dealing with SQLite any more. So I'm > not sure the comparison would be valid. > > Note that if you really, really want to do static typing in SQLite you > can implement it using CHECK constraints: > > CREATE TABLE ex(a INTEGER CHECK( typeof(a)='integer' )); > > A "strict affinity" mode in SQLite would amount to adding these check > constraints automatically. If you look at it from this point of view, > it seems likely that strict affinity would slow down performance due > to the added cost of checking type constraints at each step. > I think the benefit of a static typing system is that those checks are not done at all at run time. They are done once when the statement is compiled. After that the code can be execute many millions of times (i.e. for millions of rows) without the need for any type checking at runtime because the compiler did the necessary checks. There is no need for a check constraint as you have shown, since the compiler would only generate code to insert integer values into integer columns. If only integer values can be inserted, there is no need to check the type of the values when they are retrieved (even if the file format supports dynamic typing). Now, data pulled from the tables can be assumed to be of the expected type and used directly. This may simplify subsequent processing. The trade off is that the compiler may become more complex and the compilation step may take longer. There may still be a net benefit if the compilation time is only a small percentage of the statement's execution time (i.e complex long running queries on large tables). Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS, snippet & Unicode?
On Aug 27, 2008, at 4:52 AM, Alexandre Courbot wrote: > I know there is a patch at > http://www.sqlite.org/cvstrac/tktview?tn=3140,38 that is supposed to > improve Unicode support in FTS3. I suspect it to turn any Unicode > character into a token - however maybe you can use it as a basis to > implement what you need. Thanks for the pointer. WIll give it a try. Cheers, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table if not exists & virtual table?
Hello, On Aug 26, 2008, at 11:34 PM, Dennis Cote wrote: > Petite Abeille wrote: >> >> Is it possible to use 'if not exists' in conjunction with the >> creation >> DDL for a virtual table? >> > > No, its not possible. > > The syntax of a "create table" statement is shown here > http://www.sqlite.org/lang_createtable.html and that for a "create > virtual table" statement is shown here > http://www.sqlite.org/lang_createvtab.html. The virtual table > statement > does not allow the optional "if not exists" clause. Hmmm... bummer... any reason for such discrepancy? This 'if not exists' clause is rather handy :) Is there a place where one could raise a feature request for such functionality? Thanks in advance. Cheers, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN
Thanks for the clarification Roger, I guess it looks like I will need to modify the compiler settings locally then. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns Sent: Tuesday, August 26, 2008 5:00 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brown, Daniel wrote: > In the process of upgrading to 3.6.1 I've run into the error on line 46 > of util.c about int sqlite3IsNaN(double x) not behaving consistently > with the GCC -ffast-math compiler option (which we have enabled), is > there any alternative function I could use that would be compatible with > GCC -ffast-math? Removing the -ffast-math option from our projects > would be highly undesirable for us, as performance is paramount. If you don't use any floating point with SQLite then just remove the #error. However if you do use floating point with SQLite then you can't use fast math. See the following tickets which show a variety of problematic behaviour with -ffast-math: http://www.sqlite.org/cvstrac/tktview?tn=3101 http://www.sqlite.org/cvstrac/tktview?tn=3186 http://www.sqlite.org/cvstrac/tktview?tn=3194 http://www.sqlite.org/cvstrac/tktview?tn=3202 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFItJj7mOOfHg372QQRAlXDAJ9n+/Xe1E/1DszYXxCcVPjb+pxHOwCfcB5J XkV7LD9lbEv59oK9WS+r174= =8d1g -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
On 8/27/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > P Kishor wrote: > > > > > only badly written Perl, or Perl specifically written to be obfuscated > > such as in Perl golf, is inscrutable. Well written Perl (I practice as > > close to literal programming in Perl as possible) is quite pleasant > > and easy to read. > > > > > > This quote is from O'Reilly's Learning Perl (the Llama book) by Randal > Schwartz and Tom Phoenix (experts in Perl I believe). > > "Yes, sometimes Perl looks like line noise to the uninitiated, but to the > seasoned Perl programmer, it looks like checksummed line noise with a > mission in life." > > So I'm not the only one who thinks the syntax has room for improvement. :-) The keyword in the quote above is "sometimes" which is the same as "unintentionally badly written or intentionally obfuscated" -- here is the quote through a filter -- "Yes, unintentionally badly written or intentionally obfuscated Perl looks like line noise to the uninitiated, but to the seasoned Perl programmer..." usually this kind of code is produced by those new, trying to show off, or being competitive on purpose (as in a Perl golf competition), or just for kicks. It doesn't have any place in serious, production code, so there is much Perl code that is very lovely to read. But, my apologies for the small digression -- on to more pertinent SQLite-related discussions. > > Dennis Cote > > > -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
Is there any way to log conversions to highlite any issues that would have been hilighted by failure with strict typing? It is my plan to match the column type & data stored type, but clearly if any conversions are occurring I will be unaware. Kind Regards -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: 27 August 2008 15:45 To: General Discussion of SQLite Database Subject: Re: [sqlite] Manifest Typing performance impact? Hardy, Andrew wrote: > > Is there a performance hit assosiated with manifest typing? > I'm sure there is since sqlite must track the type of each data item as well as its value. But in reality this overhead is quite small. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the case? > That is correct, the strict affinity mode does not exist. > If there is a performance hit, what are the best ways to minimise this? > And is there any way to at least log conversions to highlite any > issues that would have been hilighted by failure with strict typing? > The best way to minimize the conversion overhead is to store the data in suitably typed columns (i.e. that match the type of the data stored in the column). This will avoid any unnecessary conversions when storing, loading, or comparing the values. See http://www.sqlite.org/datatype3.html for the column type affinity deduction rules. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
On Aug 27, 2008, at 10:45 AM, Dennis Cote wrote: > Hardy, Andrew wrote: >> >> Is there a performance hit assosiated with manifest typing? >> > > I'm sure there is since sqlite must track the type of each data item > as > well as its value. But in reality this overhead is quite small. >> I was going to guess the opposite - that manifest typing reduces overhead. (But as Dan pointed out - nobody will know until somebody generates a version of SQLite that uses static typing and compares the performance.) The reason I think static typing would make things slower is that with static typing, there has to be a bunch of checking during processing to verify the specified datatype is in use. With the current database file format, this checking must be done at query run-time. And there is no savings in not having to track the types of each data item at run-time because the current file format allows dynamic typing. So any "strict affinity" mode would likely be slower than the current SQLite. If you designed a new file format that did not allow dynamic typing at the file format layer, then you could perhaps do away with tracking of types at query run-time. But if you go with a completely new file format, you really wouldn't be dealing with SQLite any more. So I'm not sure the comparison would be valid. Note that if you really, really want to do static typing in SQLite you can implement it using CHECK constraints: CREATE TABLE ex(a INTEGER CHECK( typeof(a)='integer' )); A "strict affinity" mode in SQLite would amount to adding these check constraints automatically. If you look at it from this point of view, it seems likely that strict affinity would slow down performance due to the added cost of checking type constraints at each step. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
Hardy, Andrew wrote: > Is there any way to log conversions to highlite any issues that would > have been hilighted by failure with strict typing? > Not that I am aware of. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
Hardy, Andrew wrote: > > Is there a performance hit assosiated with manifest typing? > I'm sure there is since sqlite must track the type of each data item as well as its value. But in reality this overhead is quite small. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the case? > That is correct, the strict affinity mode does not exist. > If there is a performance hit, what are the best ways to minimise this? > And is there any way to at least log conversions to highlite any issues > that would have been hilighted by failure with strict typing? > The best way to minimize the conversion overhead is to store the data in suitably typed columns (i.e. that match the type of the data stored in the column). This will avoid any unnecessary conversions when storing, loading, or comparing the values. See http://www.sqlite.org/datatype3.html for the column type affinity deduction rules. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
On Aug 27, 2008, at 4:03 PM, Hardy, Andrew wrote: > Is there a performance hit assosiated with manifest typing? Yes. No. Possibly. Difficult to tell unless somebody creates and optimizes a version of sqlite that does not do manifest typing. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the > case? Correct. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
On 8/27/08, Andreas Ntaflos <[EMAIL PROTECTED]> wrote: > On Wednesday 27 August 2008 16:11:28 P Kishor wrote: > > See my notes at > > > > http://www.punkish.org/?p=Why_File_When_You_Can_Full-Text_Search > > > > I should get down to write a new and more detailed version and then > > putting it on the SQLite wiki. FTS documentation is woefully > > dismembered and scattered. > > > Puneet, > > thank you very much for the quick and most valuable reply. Your notes seem to > be exactly what I need to start (and even finish) incorporating FTS into my > application. > > Again, thank you! So, now help me and the rest of the community making a nice set of FTS notes that contain all the info, from compilation to usage. ;-) Hopefully we can convince DRH to include it somewhere prominent in the SQLite website instead of the scattered pieces on the wiki. > > Andreas > > -- > > Andreas Ntaflos > Vienna, Austria > > GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
lovely discourse, but although I feel bad disagreeing (on a tiny point) with the otherwise very agreeable Mr. Cote... On 8/27/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > Derek Developer wrote: > > Thanks for the link. Unfortunatly its a little expensive and probably > > 50% slower than my implementation. > > > > > On what basis do you make that claim? > > > > No offense, but C is a language that a lot of us tolerate and is not > > the panacea that some C developers like to believe it is. Reading C > > is like reading Chinese. ASM may not be a high level language, but it > > certainly is fast. > > > > > C is the lingua franca of the computer world. Every programmer should be > able to read it comfortably. I find reading well written C code, such as > SQLite, to be pleasant, and certainly much easier than assembler code. > Some languages, such as APL (which I liked) and Perl (which I really > don't know very well) are inscrutable to the casual reader. only badly written Perl, or Perl specifically written to be obfuscated such as in Perl golf, is inscrutable. Well written Perl (I practice as close to literal programming in Perl as possible) is quite pleasant and easy to read. Other than that, yes, agree with everything else you say. > C is not and > should not be in that category. > > Assembly language has two major drawbacks, it is not portable and it is > very verbose. The first means that any code you write for one platform > has to be completely rewritten for another. The second often leads users > to adopt the shortest, simplest, code sequence to accomplish their goal. > This is often not the fastest way to accomplish that task. Usually, > selecting a better algorithm will do far more to speed up code than > rewriting it in assembler. > > Studies have consistently shown that a good compiler can produce code > that is nearly as good as the best hand crafted assembly. There is > almost never a reason to write anything except the core inner loops of a > CPU intensive operation (such as encryption or decryption) in assembler. > It is quite simply a waste of time to do otherwise. > > The only effective way to write assembly code is in conjunction with > good measurement tools. Write the code in a high level language with a > good optimizing compiler, like C. Then measure the code to determine > where the program actually spends its time. Next, review the code > generated by the compiler for the inner most loops in those sections, > and replace with hand written assembly code only if you believe your > assembly code will be faster than that produced by the compiler. > Finally, measure the resulting code and see if it is in fact any faster > than the code the compiler generated. > > In this day of out of order and speculative execution of instructions, > and the critical dependency of the CPU on the performance of the memory > caching system, it is very difficult to guesstimate the execution speed > of a sequence of code especially assembly code. Modern compilers often > do a much better job of this than any developer can. > > Assembly can be used to write faster code snippets, but it is often > slower when used to write large applications because the difficulty in > writing higher level, more complex, algorithms in assembler often leads > to the use of simpler slower algorithms. > > In short, writing in assembly language does not guarantee that the > resulting program will be fast. Assembler can be fast, but it is by no > means certain that it is fast. > > > Dennis Cote > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
On Wednesday 27 August 2008 16:11:28 P Kishor wrote: > See my notes at > > http://www.punkish.org/?p=Why_File_When_You_Can_Full-Text_Search > > I should get down to write a new and more detailed version and then > putting it on the SQLite wiki. FTS documentation is woefully > dismembered and scattered. Puneet, thank you very much for the quick and most valuable reply. Your notes seem to be exactly what I need to start (and even finish) incorporating FTS into my application. Again, thank you! Andreas -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Derek Developer wrote: > Thanks for the link. Unfortunatly its a little expensive and probably > 50% slower than my implementation. > On what basis do you make that claim? > No offense, but C is a language that a lot of us tolerate and is not > the panacea that some C developers like to believe it is. Reading C > is like reading Chinese. ASM may not be a high level language, but it > certainly is fast. > C is the lingua franca of the computer world. Every programmer should be able to read it comfortably. I find reading well written C code, such as SQLite, to be pleasant, and certainly much easier than assembler code. Some languages, such as APL (which I liked) and Perl (which I really don't know very well) are inscrutable to the casual reader. C is not and should not be in that category. Assembly language has two major drawbacks, it is not portable and it is very verbose. The first means that any code you write for one platform has to be completely rewritten for another. The second often leads users to adopt the shortest, simplest, code sequence to accomplish their goal. This is often not the fastest way to accomplish that task. Usually, selecting a better algorithm will do far more to speed up code than rewriting it in assembler. Studies have consistently shown that a good compiler can produce code that is nearly as good as the best hand crafted assembly. There is almost never a reason to write anything except the core inner loops of a CPU intensive operation (such as encryption or decryption) in assembler. It is quite simply a waste of time to do otherwise. The only effective way to write assembly code is in conjunction with good measurement tools. Write the code in a high level language with a good optimizing compiler, like C. Then measure the code to determine where the program actually spends its time. Next, review the code generated by the compiler for the inner most loops in those sections, and replace with hand written assembly code only if you believe your assembly code will be faster than that produced by the compiler. Finally, measure the resulting code and see if it is in fact any faster than the code the compiler generated. In this day of out of order and speculative execution of instructions, and the critical dependency of the CPU on the performance of the memory caching system, it is very difficult to guesstimate the execution speed of a sequence of code especially assembly code. Modern compilers often do a much better job of this than any developer can. Assembly can be used to write faster code snippets, but it is often slower when used to write large applications because the difficulty in writing higher level, more complex, algorithms in assembler often leads to the use of simpler slower algorithms. In short, writing in assembly language does not guarantee that the resulting program will be fast. Assembler can be fast, but it is by no means certain that it is fast. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
On 8/18/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > P Kishor wrote: > > > > > I would like to generate Snippets from MATCHes in two columns, > > however, I get the following error: "unable to use function MATCH in > > the requested context" with the following query -- > > > > SELECT poem_id, context > > FROM poems a JOIN ( > >SELECT > >rowid, > >Snippet(fts_poems, '', '', > '') AS context > >FROM fts_poems > >WHERE poem MATCH ? OR history MATCH ? > > ) b ON a.poem_id = b.rowid > > > > > > Does this work for you? > > SELECT poem_id, context > FROM poems as a > JOIN ( > SELECT > rowid, > Snippet(fts_poems, '', '', '') AS > context > FROM fts_poems > WHERE rowid in > ( > select rowid from fts_poems where poem MATCH ? > union > select rowid from fts_poems where history MATCH ? > ) > ) as b ON a.poem_id = b.rowid; > > It runs each match in a separate subquery and doesn't generate an error > when prepared by sqlite. Sorry for the tardy response. Unfortunately, your suggestion does not work. I believe, as pointed out in another email by Nicholas Brandon, your suggestion is incorrect. On the other hand, the following worked -- SELECT poem_id, context FROM poems a JOIN ( SELECT rowid, Snippet(fts_poems, '', '', '') AS context FROM fts_poems WHERE fts_poems MATCH ? ) b ON a.poem_id = b.rowid In other words, I have to match on the table name, which seems really counter-intuitive. And, while it works for me in this instance, because I have only two columns FTS-indexed, and I am trying to match on those two columns, I can't imagine how the above would work if I had, say, 3 columns indexed and wanted to search in only two of them. I guess I would have to use the col:term kind of syntax. FTS is really a brilliant addition to SQLite, and it really needs to be documented more comprehensively and clearly, all the way from compiling, setting up, indexing, and searching. The bits and pieces are there, but they need to be brought together. I will try to do my bit by improving the documentation and put it up there -- hopefully it will be of help. > > HTH > Dennis Cote > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
On 8/27/08, Andreas Ntaflos <[EMAIL PROTECTED]> wrote: > Hi list, > > this is my first post to this list and the first time I am using SQLite > (3.6.1 > from source on Debian and Ubuntu) in a program of mine so I obviously don't > have much experience with it. I am glad for the documentation (otherwise I > would have missed that FOREIGN KEY constraints are ignored and have to be > added using triggers) but I am unclear on a few things regarding FTS3 (or > full text search in general). > > In particular I'd like to know the best practise on incorporating FTS3 into > an > existing database schema, possibly already populated with content. There are > some fields of some of the tables in the database that should be be full text > searchable. How to go about that? > > Obviously I have to create some virtual tables that hold the text that will > be > searched. But how do I link the virtual tables to the "real" tables so that I > know the table and the row in which a field with matching content was found? > Do I, every time I insert text into a field, or change the text in a field of > the real table, have to programmatically or manually update or change the > text in the corresponding virtual table as well? > > As you can see I am not very familiar with how these things are supposed to > work. The documentation [1, 2] on the website is fine for explaining FTS > itself but I couldn't find anything on how to incorporate FTS into a real > application. > > Any advice will be greatly appreciated. If there is any FM I should R kindly > point me to it :) See my notes at http://www.punkish.org/?p=Why_File_When_You_Can_Full-Text_Search I should get down to write a new and more detailed version and then putting it on the SQLite wiki. FTS documentation is woefully dismembered and scattered. > > Thanks in advance, > > Andreas > > [1] http://www.sqlite.org/cvstrac/wiki?p=FtsUsage > [2] http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex > -- > Andreas Ntaflos > Vienna, Austria > > GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index memory usage in SQLite
On Aug 26, 2008, at 1:13 PM, Dave Toll wrote: > Hello all > > > > I'm running some general performance tests on SQLite 3.5.9 (embedded C > platform), and I noticed that creating an index seems to use a lot > more > memory than I expected. > > > > An index on an integer column (30495 rows) uses 1,011,560 bytes. > > DB file size increases by 311,296 bytes. Perhaps the extra memory is used by additional cache space. What is your cache size set to? (The default is 2000 pages.) Have you read http://www.sqlite.org/malloc.html yet? > > > > > An index on a varchar column (average null-terminated text length 18 > bytes, 30495 rows) uses 2,180,040 bytes. > > DB file size increases by 856,064 bytes. > > > > I'm using the static memory allocator (mem3.c), page size 4096, and I > compiled with SQLITE_32BIT_ROWID. I measured the difference in memory > reported by sqlite3_memory_used(). Dropping the index does not return > any of the memory used, and does not reduce the DB file size. Are > these > results normal, or is some optimisation possible? > The mem3.c allocator is deprecated and will likely go away in a future release. mem5.c is preferred. The SQLITE_32BIT_ROWID option has not been tested by me since I can remember. I have no idea if it really works in all cases or not. For all I know it causes a memory leak. Dropping a table or index from a database causes the freed disk space to go onto a freelist to be used on the next INSERT. The space is not returned to the OS and the file size is not reduced. To reduce the database file size run VACUUM or enable auto_vacuum. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index memory usage in SQLite
On Tue, Aug 26, 2008 at 06:13:29PM +0100, Dave Toll scratched on the wall: > I'm running some general performance tests on SQLite 3.5.9 (embedded C > platform), and I noticed that creating an index seems to use a lot more > memory than I expected. Creating an index requires reading and sorting the original table. It tends to beat the page cache fairly hard. This is a known condition. In fact, the first bit of advice to increase the speed of index creation is to make the page cache larger. > An index on an integer column (30495 rows) uses 1,011,560 bytes. > > DB file size increases by 311,296 bytes. Or about 10.2 bytes per item, which sounds pretty reasonable. An index in SQLite contains a full copy of the indexed data, so that plus a rowid reference back to the original table and other metadata makes 10 bytes sound about right, especially if most of the integers are smallish (SQLite uses var-length integers). > An index on a varchar column (average null-terminated text length 18 > bytes, 30495 rows) uses 2,180,040 bytes. > > DB file size increases by 856,064 bytes. Same thing. 28 bytes per item sounds reasonable, especially if the average data item is 18 bytes. Strings won't pack into pages quite as efficiently, so I'd expect a higher overhead. > I'm using the static memory allocator (mem3.c), page size 4096, and I > compiled with SQLITE_32BIT_ROWID. I measured the difference in memory > reported by sqlite3_memory_used(). > Dropping the index does not return any of the memory used, If you're measuring the process memory usage, that's normal. Free pages aren't returned to the OS. If you're measuring the allocator, chances are most of that memory is in the page cache, and will not be returned. The default page size is 1K and the default cache size is 2K with about 0.5K of overhead per page, meaning the default system expects about 3MB worth of cache. Neither of the examples you've given go over that, so unless you can figure out otherwise, I'd assume most of that is cache allocation. The cache will grow until it hits its max size but won't be returned. > and does not reduce the DB file size. That's normal. As with memory systems, free pages are not released unless you vacuum the database file. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Incorporating FTS into existing database schema
Hi list, this is my first post to this list and the first time I am using SQLite (3.6.1 from source on Debian and Ubuntu) in a program of mine so I obviously don't have much experience with it. I am glad for the documentation (otherwise I would have missed that FOREIGN KEY constraints are ignored and have to be added using triggers) but I am unclear on a few things regarding FTS3 (or full text search in general). In particular I'd like to know the best practise on incorporating FTS3 into an existing database schema, possibly already populated with content. There are some fields of some of the tables in the database that should be be full text searchable. How to go about that? Obviously I have to create some virtual tables that hold the text that will be searched. But how do I link the virtual tables to the "real" tables so that I know the table and the row in which a field with matching content was found? Do I, every time I insert text into a field, or change the text in a field of the real table, have to programmatically or manually update or change the text in the corresponding virtual table as well? As you can see I am not very familiar with how these things are supposed to work. The documentation [1, 2] on the website is fine for explaining FTS itself but I couldn't find anything on how to incorporate FTS into a real application. Any advice will be greatly appreciated. If there is any FM I should R kindly point me to it :) Thanks in advance, Andreas [1] http://www.sqlite.org/cvstrac/wiki?p=FtsUsage [2] http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Manifest Typing performance impact?
Is there a performance hit assosiated with manifest typing? Is it right that although info on the sqlite site suggests there is an avilable mode that supports strict typring, this is not infact the case? If there is a performance hit, what are the best ways to minimise this? And is there any way to at least log conversions to highlite any issues that would have been hilighted by failure with strict typing? Hope this makes sense. Newbie to sqlite, any hep or advice greatly appreciated. Best Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS, snippet & Unicode?
Alexey Pechnikov wrote: > > Is it included to 3.6.1 or 3.6.2 version? > No, it is not included in either version. The patch was submitted by the mozilla group, but it has not been checked in to SQLite. You can of course apply the patch to your own customized version of SQLite. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Anyone seen problems with SQLite 3.6.1/Tcl8.5.4/FreeBSD 7.0R?
Note this has been sent to the SQLITE Users digest, as well as the comp.lang.tcl newsgroup. I'm getting a core dump at the end of execution of a script which opens an SQLite database, and performs a query. The gdb stacktrace is as follows: #0 0x284099c0 in ?? () #1 0x2804f918 in dlopen () from /libexec/ld-elf.so.1 #2 0x2804e520 in dlclose () from /libexec/ld-elf.so.1 #3 0x2814a05d in TclpUnloadFile () from /usr/local/lib/libtcl8.5.so #4 0x2810aaef in TclFinalizeLoad () from /usr/local/lib/libtcl8.5.so #5 0x280dc705 in Tcl_Finalize () from /usr/local/lib/libtcl8.5.so #6 0x280dc816 in Tcl_Exit () from /usr/local/lib/libtcl8.5.so #7 0x280a8b56 in Tcl_ExitObjCmd () from /usr/local/lib/libtcl8.5.so #8 0x2809c54d in TclEvalObjvInternal () from /usr/local/lib/libtcl8.5.so #9 0x2809d0c4 in TclEvalEx () from /usr/local/lib/libtcl8.5.so #10 0x2809d3fe in Tcl_EvalEx () from /usr/local/lib/libtcl8.5.so #11 0x281068e0 in Tcl_FSEvalFileEx () from /usr/local/lib/libtcl8.5.so #12 0x2810cbc0 in Tcl_Main () from /usr/local/lib/libtcl8.5.so #13 0x0804869f in main () I get the required output, as the script actually runs to completion, and apparently correctly -- but dumping core is not a good way to end a script (IMHO). Does this signature remind anyone of similar behaviours? I've tried commenting out the close at the end of the script, thinking that the core was happening at the unload of the libtclsqlite3.so code, but I'm not entirely sure what's happening here ... I did previously have a threads related problem with SQLite on FreeBSD, but that was worked around within the mutex code of the SQLite library. Hmmm ... any pointers appreciated. Cheers, Rob Sciuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index memory usage in SQLite
Hello all I'm running some general performance tests on SQLite 3.5.9 (embedded C platform), and I noticed that creating an index seems to use a lot more memory than I expected. An index on an integer column (30495 rows) uses 1,011,560 bytes. DB file size increases by 311,296 bytes. An index on a varchar column (average null-terminated text length 18 bytes, 30495 rows) uses 2,180,040 bytes. DB file size increases by 856,064 bytes. I'm using the static memory allocator (mem3.c), page size 4096, and I compiled with SQLITE_32BIT_ROWID. I measured the difference in memory reported by sqlite3_memory_used(). Dropping the index does not return any of the memory used, and does not reduce the DB file size. Are these results normal, or is some optimisation possible? Cheers, Dave Toll. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN
Brown, Daniel wrote: >Unfortunately our build system is automated and not particularly agile, it can >be done but it would be preferred not to have to do that and to replace the >offending function instead but I've never had to write a IsNaN test. > > > I had a look at the sourcecode for sqlite3IsNaN(): SQLITE_PRIVATE int sqlite3IsNaN(double x){ /* This NaN test sometimes fails if compiled on GCC with -ffast-math. ** On the other hand, the use of -ffast-math comes with the following ** warning: ** ** This option [-ffast-math] should never be turned on by any ** -O option since it can result in incorrect output for programs ** which depend on an exact implementation of IEEE or ISO ** rules/specifications for math functions. ** ** Under MSVC, this NaN test may fail if compiled with a floating- ** point precision mode other than /fp:precise. From the MSDN ** documentation: ** ** The compiler [with /fp:precise] will properly handle comparisons ** involving NaN. For example, x != x evaluates to true if x is NaN ** ... */ #ifdef __FAST_MATH__ # error SQLite will not work correctly with the -ffast-math option of GCC. #endif volatile double y = x; volatile double z = y; return y!=z; } So it looks as if you have little choice. I'd say, Mike's suggestion is the least painful. Regards, Arjen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table Locked Error Recovery
Does anyone know what the course of action is for recovering from a table locked error? I dropped a table and I got the error code 6 (Table Locked Error). After two weeks of pulling my hair I guessed that the error was not for my table being dropped. It was for the sqlite_master table being modified by another thread. If this is the case then I have two questions: 1) sqlite_master table does not belong to the application (for writing) it belongs to the sqlite library. So sqlite should take care of this recovery not the application. The application should not even get this error because the application had nothing to do with the sqlite_master table directly. 2) The sqlite3_errmsg(db) should mention which table is locked (if it is possible). So that the next person does not spend two weeks on this. Thanks, -Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS, snippet & Unicode?
Hello! В сообщении от Wednesday 27 August 2008 06:52:09 Alexandre Courbot написал(а): > I know there is a patch at > http://www.sqlite.org/cvstrac/tktview?tn=3140,38 that is supposed to > improve Unicode support in FTS3. I suspect it to turn any Unicode > character into a token - however maybe you can use it as a basis to > implement what you need. Is it included to 3.6.1 or 3.6.2 version? Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Manifest Typing performance impact?
Is there a performance hit assosiated with manifest typing? Is it right that although info on the sqlite site suggests there is an avilable mode that supports strict typring, this is not infact the case? If there is a performance hit, what are the best ways to minimise this? And is there any way to at least log conversions to highlite any issues that would have been hilighted by failure with strict typing? Hope this makes sense. Newbie to sqlite, any hep or advice greatly appreciated. Best Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users