[sqlite] Question on Indexing
Hello, [>> ] considering a m:n relation a.id <- a.id,b.id -> b.id, is it due to performance, advisable to put an index on a.id,b.id ? Thanks Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with -order by- clause
Hello, Ive got a problem with sorting german Umlaute eg. äöü (ae,oe,ue) Usually they are sorted prior to the corresponding Letter: ü before u In SqLite with Collate Locale these letters are sorted at the end after z Is there a solution or do I have to live with it? Cheers Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Inner Join Performance Issue
Hello Forum, [>> ] I have a select that joins 15 Tables the where clause consist of 8 like relations (all fields are indexed), this is to implement a sort of "search engine". The performance is awful. It takes around 10sec. Is this how it should be or is there anything I can do? If you need more infos pls. let me know Thx Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inner Join Performance Issue
I just ran EXPLAIN, how can I tell if the Indexes are used? I just read, that with an operator "like '%a%'" SQLite won't use an Index. Is this the case? Thanks Ralf > -Ursprüngliche Nachricht- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von John Crenshaw > Gesendet: Donnerstag, 22. Oktober 2009 05:53 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] Inner Join Performance Issue > > Try to EXPLAIN the query and verify that the index is actually used. > There are a lot of reasons why this query would probably NOT be using > the index. > > John > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf > Sent: Wednesday, October 21, 2009 5:50 PM > To: 'General Discussion of SQLite Database' > Subject: [sqlite] Inner Join Performance Issue > > Hello Forum, > [>> ] > I have a select that joins 15 Tables the where clause consist of 8 like > relations (all fields are indexed), this is to implement a sort of > "search > engine". > The performance is awful. It takes around 10sec. > Is this how it should be or is there anything I can do? > > If you need more infos pls. let me know > > Thx > Ralf > > ___ > 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
[sqlite] Problem with Inner Join
Hello, I'm having problems with Inner Joins on m:n relations book <--> bookauthor <--> author SELECT authorname FROM author INNER JOIN book INNER JOIN bookauthor ON book.Id_book = bookauthor.Id_book ON author.Id_author = bookauthor.Id_author WHERE bookltitle='title' I receive an error when I execute this Query saying "SQL Error" Is it me or is it SQLite? Or a bit of both? Pls help me Thanks Ralf Virus checked by G DATA AntiVirusKit Version: AVK 18.4478 from 13.07.2008 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] tclsqlite Precompiled binary for tcl
Hi, is the precompiled binary for tcl not longer available? Best Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with executereader in System.Data.SQLite
On 15. April 2014 01:55:40 MESZ, Joe Mistachkin wrote: > >Ralf Jantschek wrote: >> >> The connection as such is ok. Updates are working. It is only the >> executereader that is troubling me >> > >For database files that need a UNC path, four leading backslashes are >required. > >Without them, some things may appear to work correctly; however, it >might >also >exhibit the type of behavior you are seeing. > >Please try using the four leading backslashes and let us know the >outcome. > >-- >Joe Mistachkin > >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users I just tried... No change :-( -- Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with executereader in System.Data.SQLite
On 15. April 2014 09:46:43 MESZ, Joe Mistachkin wrote: > >Ralf wrote: >> >> I just tried... No change :-( >> > >Have you looked at the contents of the database using the SQLite >command >line tool for Windows? > > https://www.sqlite.org/2014/sqlite-shell-win32-x86-3080403.zip > >-- >Joe Mistachkin > >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Yes. Everything is Working as expected when i access the DB with any Browser. It is just the powershell environment -- Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using SQLite's VFS in C++
Hello, I would like to use SQLite in my C++(11) project. Assume that there is a class File which is able to store arbitrary data. It provides basic IO functionality read() write() truncate() and sync(). Now I would like to use my File to store SQLite databases (persistently). To understand my motivation, let me explain in short words what exactly File does: A "File" splits its content into several fixed-sized encrypted blocks and stores those blocks on some kind of storage. At any point in time, it is guaranteed, that no other process will interfere and use those blocks, so we don't need any locking mechanisms. The easiest way to implement this would be to store the whole database in a local temporary folder using unix vfs, close it, read all its content and call my file->write() routine. This strategy contains some ugliness as I would like to avoid using temporary files. So I had a deeper look into SQLite's VFS[1] interface and I think that it could possibly offer a better solution. Now my problem: There may be several File objects, each representing a SQLite database. Those objects exist before SQLite accesses them - so there is no need, that SQLite generates those objects. How can I use VFS to work with those objects? Generally sqlite3_open*() is used to generate a struct sqlite3* regarding a certain kind of VFS type. But I can not use sqlite3_open*() in order to "open" a File as sqlite3_open*() makes use of a const char* filename that obviously indicates the filename :-) In my case, there is nothing like a filename but there is already an object on which SQLite shall work with. So I would need sth. like: File *f = ...; sqlite3_open(f, myOwnVFSDescriptor); Are there any possibilities to solve this problem? Or would it be better to create a temporary SQLite database file and copy its content afterwards? One last question: I also read about in memory and temporary databases [2]. Do in memory databases have the same structure as database files? If so, is it possible to dump and load in memory databases? (this could also offer a nice solution) [1] http://sqlite.org/vfs.html [2] http://sqlite.org/inmemorydb.html Regards and thanks! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite's VFS in C++
Thanks for this tip, this could possibly work! On 05/14/2014 06:18 AM, J Decker wrote: > the name that gets passed is the one you pass to sqlite_open... so just use > that as an indicator of which object to use and in the open callback, > result with the appropriate object... or don't use the name and ignore > it? snprintf( somebuf, sizeof( somebuf ) / sizeof( somebuf[0] ), "%p", > your_file_descriptor ); sqlite3_open( somebuf ) ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] autoincrement and fts2?
>The rowid is the standard SQLite rowid, so it does provide an INTEGER >PRIMARY KEY AUTOINCREMENT column. > >The standard way to have non-TEXT information associated with rows in >an fts table would be a separate table which joins with the fts table >on rowid. I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I believe that it will be affected by VACUUM change of rowids recently reported on this list? If so, could this be fixed? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
>>The standard way to have non-TEXT information associated with rows in
>>an fts table would be a separate table which joins with the fts table
>>on rowid.
>
>I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I
>believe that it will be affected by VACUUM change of rowids recently reported
>on this list? If so, could this be fixed?
VACUUM does modify FTS2 rowids. Here is the test:
drop table if exists a;
create virtual table a using fts2 (t);
insert into a (t) values ('one');
insert into a (t) values ('two');
insert into a (t) values ('three');
select rowid, * from a;
delete from a where t = 'two';
vacuum;
select rowid, * from a;
Unfortunately there is no workaround since table a is auto-generated by the
FTS2 module. Created ticket #2510.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Hello Henrik Ræder, >I'm trying to load the FTS2 extension in Delphi, using the Aducom >components. Am really close, but still stuck, and thinking it's a problem >with the parameter to sqlite3_enable_load_extension(). DISQLite3 (http://www.yunqa.de/delphi/sqlite3/) does not expose such problems. Look at the full text search demo project which incorporates both FTS1 and FTS2 into a single *.exe application, with _no_ DLLs or external files needed. The new customizable tokenizer interface will be demonstrated by a Unicode-aware Pascal tokenizer with the upcoming version of DISQLite3 as soon as the FTS vacuum fix is official released. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Hello Roberto, >Might be a typo, but your declaration defines the calling convention as >'stdcall' (instead of 'cdecl'), was your sqlite3.dll custom built with this >calling convention? I don't think much of sqlite would work with stdcall. DISQLite3 intentionally uses the 'register' calling convention, internally and externally. Delphi prefers 'register' over 'stdcall' or 'cdecl' as the most efficient, since it usually avoids creation of a stack frame. This results in a measurable performance improvement compared to sqlite3.dll. >The problem with DISQLite3 is that it is not free and the sources for the >component is not available. DISQLite3 Personal is free for non-commercial use. Source code is available after registering DISQLite3 Pro. Both editions benefit from 'register' calling conventions, include full text search (FTS1 and FTS2) as well as ample Delphi additions like class wrappers and convenience functions. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Hello Joe Wilson, >Does it support external sqlite loadable extensions? Loadable extensions are currently omitted. FTS1 and FTS2 extensions are provided as built-in modules. User-aware collations sequences using the Windows sorting functions are provided in place of the ICU extension. Full functionality is therefore available. >The register calling convention may be a problem there. It can remapped so cdecl extensions could be used. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Hello Joe Wilson, >Your product is not useful to a few users like me who require custom >sqlite functions for their databases. I am not sure I understand currectly. Only loadable extensions are currently omited from DISQLite3. sqlite3_create_function() is very well available in DISQLite3 Pro to create custom SQL functions. DISQLite3 also includes units with ready-made function extensions: * REGEXP regular expression support provided by DIRegEx in DISQLite3RegExp.pas. * Mathematical utility functions [(acos(), asin(), atan(), atan(), atan2(), ceil(), ceiling(), cos(), cot(), degrees(), exp(), floor(), ln(), log(), log(), log2(), log10(), mod(), pi(), pow(), radians(), sign(), sin(), sqrt(), tan(), truncate()] in DISQLite3Functions.pas. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DISQLite FTS
>Does DISQLite have its own implementation of FTS, so not using FTS2 at >all? DISQLite3 uses the original full text search modules, adapted to Delphi. Both FTS1 and FTS2 are already compiled in, and can both be used by the same application (like SQLiteSpy does). >Does it use the same mechanism as FTS2 with virtual tables? Yes. >And have you compared speed and functionality to FTS2, Speed is likely to be a little faster than external FTS2, resulting from register calling conventions. DISQLite3's embedded FTS features are identical to external FTS. Just today I uploaded a new version which adds a Unicode Pascal tokenizer written in Delphi which you can use out of the box or as a basis for your own customized tokenizer (see demo). >I guess what it comes down to is to know options available, however I >think the FTS2 project is great and hopes that it continues to grow, as >it can be used on all platforms. FTS in DISQLite3 is cross-platform database file compatabile, just as the entire library. However, if you use custom tokenizers, user functions or collation sequences with your Delphi application you need to replicate them on other platforms. >I guess what might be a problem is that I would not be able to use >DISQLite's FTS implementation in Python or .net for example, or would I? No, this is not a problem. Database files created by DISQLite3 can be read and modified by Python, .net, or any other SQLite3 compatible applications. If in doubt, run some test with SQLiteSpy: It uses DISQLite3 as its build-in SQLite3 implementation. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
>I assumed that people would want to load their custom functions from sqlite >extension shared libraries. But if your customers get source code, I suppose >they can work around this. A key feature of DISQLite3 is that it can be fully embedded into applications with minimum footprint only. You can of course also compile DISQLite3 as an external library, be it a regular DLL or a Delphi BPL runtime package. >I prefer to have a separate sqlite3 shared library so it can be customized >and upgraded independently of the host application. Certainly. With DISQLite3 you are free to create your separate libraries and runtime packages just as you need them: With or without wrapper classes, data cache, regular expression support, etc. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Hello Zlatko Matic, >How about Lazarus version of DISQLite3? :) DISQLite3 is Delphi only at the moment, maybe later! ;-) Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Problem loading extension in Delphi (FTS2)
DISQLite3 does not use SQLite.NET. As I read Sam, he did not say so. He just compared the two to support his argument that "loadable extensions are not required to create custom functions and having access to source is not required for custom functions either". Ralf >I was not aware that DISQLite3 uses SQLite.NET. > >--- "Samuel R. Neff" <[EMAIL PROTECTED]> wrote: >> >> loadable extensions are not required to create custom functions, and having >> access to source is not required for custom functions either. SQLite.NET >> provides very clean support for custom functions written in any .NET >> language and they are loaded automatically by the wrapper from any DLL >> present in the application--they don't have to be added to the SQLite.NET >> codebase. >> >> Sam - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] rowid versus docid for fts3.
This one just came to my mind: CREATE TABLE (rowid INTEGER PRIMARY KEY, t TEXT); This promotes "rowid" to a visible column "rowid" which does not change during a VACUUM. "rowid" is already a reserved word in SQLite. Maybe this option is even compatible to FTS2? Ralf >ext/fts3.c in the current code fixes the fts2-vs-vacuum problem by >adding "docid INTEGER PRIMARY KEY" to the %_content table. This >becomes an alias for rowid, and thus causes vacuum to not renumber >rowids. It is safe to add that column because the other columns in >%_content are constructed such that even the following: > >CREATE VIRTUAL TABLE t USING fts3(docid); > >will work fine. > >I'm considering whether I should take it one step further, and make >docid a reserved column name for fts3 tables. My rational is that >fts3 rowids are not quite the same as the rowids of regular tables - >in fact, some use-cases would encourage users of fts3 to use rowids in >exactly the way that fts2 was inappropriately using them! > >docid would be a hidden column, like rowid. That means that you'll >only see the column in SELECT and INSERT statements if you explicitly >reference it. It would operate WRT rowid exactly as an INTEGER >PRIMARY KEY column would. > >Opinions? > >-scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] rowid versus docid for fts3.
Scott Hess wrote: >Unfortunately, the reason fts2 couldn't be "fixed" was because you >can't perform the necessary ALTER TABLE if the column you're adding is >a primary key. Sure, I was aware of this problem. >Since the only alternative would be to build a new >table and copy everything over, it seemed more reasonable to just let >the app developer do that, rather than forcing it on them under the >covers. True also. I know that my "compatible" proposal would not update existing FTS2.0 tables (with the vacuum bug) to FTS2.1 (fixed vacuum bug with rowid INTEGER PRIMARY key). But it should at least be possible to continue using old FTS2.0 tables with this new FTS2.1. It should also be possible (untested and highly speculative) for FTS2.0 to read tables generated by the new FTS2.1. Old FTS2.0 will just not be able to write to or update tables created by FTS2.1. However, since reading should work well, it update existing tables can be updated with the FTS2.1 module only, alleviating the need for a 2nd FTS modules just for updating. To sum up, I expect these benefits from my "rowid INTEGER PRIMARY KEY" suggestion: Reading: Fully upward and backward compatible. Not at all with FTS3. Writing: Upward compatible. Not with FTS3. Updating: Possible within the same FTS2 module. Requires extra FTS3 module otherwise. I have not written any code to test if all this does indeed make sense. Is anyone aware of any fallbacks, before I try? Regards, Ralf >On 8/31/07, Ralf Junker <[EMAIL PROTECTED]> wrote: >> This one just came to my mind: >> >> CREATE TABLE (rowid INTEGER PRIMARY KEY, t TEXT); >> >> This promotes "rowid" to a visible column "rowid" which does not change >> during a VACUUM. "rowid" is already a reserved word in SQLite. Maybe this >> option is even compatible to FTS2? >> >> Ralf >> >> >ext/fts3.c in the current code fixes the fts2-vs-vacuum problem by >> >adding "docid INTEGER PRIMARY KEY" to the %_content table. This >> >becomes an alias for rowid, and thus causes vacuum to not renumber >> >rowids. It is safe to add that column because the other columns in >> >%_content are constructed such that even the following: >> > >> >CREATE VIRTUAL TABLE t USING fts3(docid); >> > >> >will work fine. >> > >> >I'm considering whether I should take it one step further, and make >> >docid a reserved column name for fts3 tables. My rational is that >> >fts3 rowids are not quite the same as the rowids of regular tables - >> >in fact, some use-cases would encourage users of fts3 to use rowids in >> >exactly the way that fts2 was inappropriately using them! >> > >> >docid would be a hidden column, like rowid. That means that you'll >> >only see the column in SELECT and INSERT statements if you explicitly >> >reference it. It would operate WRT rowid exactly as an INTEGER >> >PRIMARY KEY column would. >> > >> >Opinions? >> > >> >-scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] 3.5.0 alpha TryEnterCriticalSection linker error
>Isn't it time to drop the Win9X support from the default build?
I do not believe that just because Win9x is missing a single required call
justifies dropping support for it altogether!
>I'm thinking that any optimization should be enabled for the majority of
>users. Or if it's not really an optimization, why keeping it in the code then?
If possible, please keep the optimization.
>An alternative is to call this function when available using "GetProcAddress"
>(this is the case for a lot of other modern calls that cannot be done right
>now).
I second this alternative.
According to http://msdn2.microsoft.com/en-us/library/ms686857.aspx,
TryEnterCriticalSection() is available on all Windows NT sytems. Therefore an
option to "GetProcAddress()" is checking for such OSes. The isNT() routine is
already part of os_win.c and is used there frequently:
static int isNT(void){
if( sqlite3_os_type==0 ){
OSVERSIONINFO sInfo;
sInfo.dwOSVersionInfoSize = sizeof(sInfo);
GetVersionEx(&sInfo);
sqlite3_os_type = sInfo.dwPlatformId==VER_PLATFORM_WIN32_NT ? 2 : 1;
}
return sqlite3_os_type==2;
}
sqlite3_mutex_try() would then extend to something like this (untested!):
int sqlite3_mutex_try(sqlite3_mutex *p){
int rc;
assert( p );
assert( p->id==SQLITE_MUTEX_RECURSIVE || sqlite3_mutex_notheld(p) );
if( isNT() && TryEnterCriticalSection(&p->mutex) ){
p->owner = GetCurrentThreadId();
p->nRef++;
rc = SQLITE_OK;
}else{
rc = SQLITE_BUSY;
}
return rc;
}
Ralf
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Opinions about per-row tokenizers for fts?
Hello Scott Hess, >In the interests of not committing something that people won't like, my >current proposal would be to add an implicit TOKENIZER column, which will >override the table's default tokenizer for that row. There are a few things I am worried about with this approach: 1. FTS storage size Will the TOKENIZER column not add to the overall size of the FTS storage, even if the default tokenizer is used? As FTS requires to store all text, its storage requirements are quite high already and did put people of SQLite as their full text search implementation. 2. Potential incompatability with query parser tokenizer The table's text tokenizer is used to tokenize the query string as well. AFAIK, both must be identical. I can not see how this single query tokenizer can then cooperate with a potentially unlmited number of incompatible row tokenizers. Reparsing the query for each row is, it guess, out of the question for performance reasons. * Alternative suggestion Offer a per COLUMN tokenizer option instead of a per ROW one. This would get rid of problem 1 because the tokenizer can be stored with the column definition. The COLUMN tokenizer option would also help with problem 2: The engine can then parse the query according to each column's tokenizer setting. Not all queries might make sense with all columns, but at least the engine would guarantee that both are using the identical tokenizer. It would be up to the application to search certain columns for a particular language query only. I also find the per column tokenizer override easier to grasp (like for translations, for that purpose), because one can different language columns with different tokenizers: Content_EN, Content_KR, and so on. This of course assumes that the number of supported languages is limited. New languages can be added with ALTER TABLE, but an application with support for an infinite number of langages would probably opt for the one table per language option. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Opinions about per-row tokenizers for fts?
Hello Scott Hess, >I think that if you do not need the ability to customize tokenizer on >a per-row basis, there should be no storage cost compared to the >current implementation. Glad to read this! > > >Regarding per-row versus per-column tokenizers, your suggesting to >have something like 'content_kr TOKENIZER icu_kr' for each variant is >reasonable, but I'm not certain what gain it would have over simply >having separate tables for each variant. Since data about records is >pushed into the schema itself, you potentially have to generate fairly >intricate queries, probably dynamically. Queries like this select * from recipe where recipe match 'pie'; would not need dynamic creation. >This implementation also suffers from the query-tokenization problem you >mention. I suggested column-tokenizers to work around the query-tokenization problem. The column-tokenizer definition would enable FTS to parse the query using the appropriate tokenizer for each row. Users would be guaranteed that each column would be searched with its matching query tokenizer. This would of course mean that, for multiple columns of different tokenizers, FTS would have to search each column individually. >To be clear on the type of problem my proposal was targetted at, say >you have something like Google Reader, where there are a bunch of >articles that you want to search over. Each article generally doesn't >have multiple translated variants, instead the language is a piece of >data about the article. For this kind of system, pre-defining columns >for every language the system may encounter might result in dozens of >columns, with exactly one column used for any particular row. > >NOTE: I think that your idea of per-column default tokenizers may be a >good idea for fts to have. I'm just questioning whether it is >targetted at the same problem my proposal is. Both approaches certainly have somewhat different targets from a user's perspective. Form a storage perspective, the column approach is reasonable if most columns contain non-null values or if storage space for a null value is negligable. > > >Regarding query tokenization, yes, the query must be parsed using a >tokenizer appropriate to the query. That was where the suggested >change to the query syntax came from: > > SELECT rowid FROM t WHERE t MATCH 'tokenizer: ...'; If the tokenizer must be explicitly stated, wouldn't this require to construct queries danymically? In addition to that, must not the user take care that the tokenizer properly matches the row? My column-tokenizer suggestion (as I immagine it) would release users from this responsibility. >Tokenizing the query using every possible tokenizer may result in more >results, but is unlikely to result in higher quality of results. Besides this, I am concerned that results might be missed because the the query tokenizer does not match the text tokenizer. -- A final thought which occurred to me last night: How to handle tables with unregistered tokenizers? The column-tokenizer approach has all the information to throw an error at sqlite3_prepare(). For tokenizers defined at the row level, the error will only be available at sqlite3_step(). In other words: A prepared statement is not guaranteed to run successfully. AFAIK, this behaviour is as yet nowhere present in SQLite. FTS could of course just return NULL for rows with unavailable tokenizers, but this would clearly exclude potential matches and is, IMO, undesirable. Well, enough said. Thanks for your feedback, I just hope mine makes some sense, too ;-) Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS3 where ?
Hello Andre du Plessis, If you are using Delphi, FTS3 is already included in the latest DISQLite3 (Pro and Personal). Download is available from http://www.yunqa.de/delphi/. The source code is available from CVS. You will find FTS3 in the /ext/ directory. Ralf >Fts3 which everyone is talking about, I cannot see any mention of it on >the download page, does it mean that its just the development sourcecode >which people are compiling at this point or are there some prebuilt >dll's available, or has it not been officially released yet? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Lemon: Help on conflic resolution?
I am writing to ask for help about how to solve The Lemon parser conflicts. As part of a larger grammar, I am need to implement this regular expression in Lemon: (.+|'.+')+ I tried lots of grammars, but all of them generated Lemon warnings. Maybe someone could have a look at the grammar below and let me know how the conflicts can be solved, and why they are generated in the first place? Many thanks, Ralf -- %left CHAR. // Any character, except for apostrophe. %left APOS. // Apostrophe only. doc ::= inline. // One ore more CHARs. chars ::= CHAR. chars ::= chars CHAR. // Any sequence of just CHARs and 'CHARs' (surrounded by apostrophes). inline ::= chars. inline ::= APOS chars APOS. // The repeat. This causes conflicts. Isn't it allowed? Workarounds? inline ::= inline inline. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon: Help on conflic resolution?
Richard, this helped me greatly! I also derived from your example that I can use multiple characters without conflicts like this: --- doc ::= inline_list. // List of allowed characters. Add more as you like. c ::= CHAR. c ::= SPACE. // The c character repeat. chars ::= c. chars ::= chars CHAR. // Any sequence of just c and 'c' (c surrounded by apostrophes). inline ::= c. inline ::= APOS chars APOS. // The inline repeat. inline_list ::= inline. inline_list ::= inline_list inline. - Many thanks! Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon: Help on conflic resolution?
Richard, this helped me greatly! I also derived from your example that I can use multiple characters without conflicts like this: --- doc ::= inline_list. // List of allowed characters. Add more as you like. c ::= CHAR. c ::= SPACE. // The c character repeat. chars ::= c. chars ::= chars CHAR. // Any sequence of just c and 'c' (c surrounded by apostrophes). inline ::= c. inline ::= APOS chars APOS. // The inline repeat. inline_list ::= inline. inline_list ::= inline_list inline. - Many thanks! Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Documentation - Downloadable?
Hello Olaf Beckman Lapré, >Aren't there any source documents? I assume the documentation wasn't written >in HTML originally. All documentation is available via CVS and can be build using the provided TCL scripts. The DISQLite3 HTML Help (http://www.yunqa.de/delphi/) contains the full SQLite3 API reference plus a few wiki pages in HTML Help format for Win32 systems, with full text search. DISQLite3 is a Delphi wrapper of SQLite3, but the SQLite3 API is included unchanged for reference. I am sure it will serve you well if you simply ignore the Delphi related information - unless you are using Delphi, of course ;-) Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Retrieve bound host parameters from statement?
Hello, I wonder if it is possible to retrieve bound host parameters from a prepared SQL statement? I am thinking of the opposite of the sqlite3_bind... family of functions like: int sqlite3_bound_int (sqlite3_stmt*, int*); int sqlite3_bound_double (sqlite3_stmt*, double*); They would be usefull to work around the sqlite3_trace() limitation which does not replace host parameters in the SQL. With the sqlite3_bound... functions, the trace callback would be able retrieve the parameter values from the statement and replace them or log them separately. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieve bound host parameters from statement?
>> I wonder if it is possible to retrieve bound host parameters from a prepared >> SQL statement? I am >> thinking of the opposite of the sqlite3_bind... family of functions like: >> >> int sqlite3_bound_int (sqlite3_stmt*, int*); >> int sqlite3_bound_double (sqlite3_stmt*, double*); > >You'd also need to specify the index of the ? parameter you're seeking. Certainly. Sorry for the ommission, glad you pointed this out. >> They would be usefull to work around the sqlite3_trace() limitation which >> does not replace host >> parameters in the SQL. With the sqlite3_bound... functions, the trace >> callback would be able >> retrieve the parameter values from the statement and replace them or log >> them separately. > >You could create all this functionality in your wrapper level above >the sqlite3 API. > >It would be easy enough for you to modify the sqlite3 sources to add >such functions to fish the values out of the internal Vdbe.aVar Mem >array of the sqlite3_stmt. If the type does not match what is stored >internally, or something was not previously bound or out of range, I >imagine an SQLITE_ERROR could be returned. Or maybe you want your >bound* functions to coerce the bound value to the type you specify. True, but we would need to access unsupported API to do so. And as we know only too well, unsupported API is subject to change without notice any time ;-). Therefore I would rather not write these myself but ask for the possibility to add them to the library officially, even if "experimental" only. >Another complementary function, say sqlite3_bound_type, could >return the type(s) of the bound field. (I say types plural because >sometimes a value can be a combination of types at the same time - >i.e., MEM_Real|MEM_Int). These internal types would have to be >exposed if you required such functionality. > >#define MEM_Null 0x0001 /* Value is NULL */ >#define MEM_Str 0x0002 /* Value is a string */ >#define MEM_Int 0x0004 /* Value is an integer */ >#define MEM_Real 0x0008 /* Value is a real number */ >#define MEM_Blob 0x0010 /* Value is a BLOB */ Indeed very much agreed to! Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieve bound host parameters from statement?
Hello Dan Kennedy,
>> True, but we would need to access unsupported API to do so.
>> And as we know only too well, unsupported API is subject to
>> change without notice any time ;-). Therefore I would rather
>> not write these myself but ask for the possibility to add them
>> to the library officially, even if "experimental" only.
>
>Depends how desperate you are. Say you want to query statement
>object X that has 4 variables, you could do this:
>
> pTmp = sqlite3_prepare("SELECT ?, ?, ?, ?");
> sqlite3_transfer_bindings(X, pTmp);
> /* Use sqlite3_step() etc. to fish values out of pTmp */
> sqlite3_transfer_bindings(pTmp, X);
> sqlite3_finalize(pTmp);
Smart, many thanks!
Still, I believe that faster sqlite3_bound... functions would be a useful
addition to the official API. It seems only logical to have corresponding read
functions to complement the write functions.
They would, for example, help to fill the gap of the unresolved host parameters
if the trace callback was changed to carry along the prepared statement being
executed.
Ralf
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Retrieve bound host parameters from statement?
Hello Joe Wilson, >> True, but we would need to access unsupported API to do so. And as we know >> only too well, >> unsupported API is subject to change without notice any time ;-). Therefore >> I would rather not >> write these myself but ask for the possibility to add them to the library >> officially, even if >> "experimental" only. > >Then you'll be waiting forever. > >If you post a patch implementing the feature, at least some >other like-minded programmers might get some benefit from it, >or at least generate some feedback. The simple patch is not enough. To have any value, it must be supported and tested for upcomming versions of SQLite. Using undocumented APIs always carries the risk that the patch will break in the future, possibly staying unnoticed until it caused serious problems to someone. Dan's solution is safe in this regard, and should be preferred - even if using unofficial API calls will certainly be faster. Maybe we'll be lucky and will not be waiting forever!? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
>But to do so seems likely to require an incompatible change to the >virtual-table interface. Could I kindly request an addition to the incompatible change to the virtual-table interface? I would very much appreciate a corresponding function to function xRowID( pCursor: psqlite3_vtab_cursor; pRowID: PInt64): Integer; which would notify virtual table implementations that the the SQLite engine no longer uses this particular RowID like, for example: function xRowID_Done( pCursor: psqlite3_vtab_cursor; pRowID: PInt64): Integer; The reason behind this is that some DB engines store RowIDs / BookMarks in malloced memory structures. Obviously, they have to be freed when no longer in use. Unfortunately, the current VT interface does not give notice when this is the case. With xRowID_Done, implementations will be able to free malloced memory when no longer needed by SQLite and thus avoid accumulating malloced RowIDs until the table is closed. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Can sqlite3_reset() ever fail?
Hello, I wonder if sqlite3_reset() can ever fail. In particular, does sqlite3_reset() always reset the statement even if it returns an error code? >From the documentation and mailing list, my understanding is that >sqlite3_reset() errors always relate to the latest (or possibly ongoing) VM >execution triggered by sqlite3_step(). Whatever VM error returned, the >statement itself will nevertheless be reset after the call. I mostly conclude this reasoning from the sqlite3_finalize() documentation and source code. However, since this is not explicitly spelled out for sqlite3_reset(), I would like to ask if sqlite3_reset() * can also be called at any point during the execution of the virtual machine? * will also result in an error or interrupt if the virtual machine has not completed execution, roll back or cancel transactions, and return SQLITE_ABORT? For the new sqlit3_prepare_v2 API, can sqlite3_reset() and sqlite3_finalize() return any error codes except for SQLITE_ABORT that indicate incomplete DB operations not already indicated by sqlite3_step()? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Undefined collation: Peculiar observations ...
Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Now some peculiar observations: 1. Issue "PRAGMA collation_list;" and notice that "unknow" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI: sqlite> PRAGMA collation_list; 0|unknown 1|NOCASE 2|BINARY Question 1: Is this the expected behaviour, or should not "PRAGMA collation_list;" rather list registered collations only? 2. Running simple queries like "SELECT * FROM a;" work fine. But subselects, in their most basic form and with no sorting or comparisons, do not: sqlite> SELECT * FROM a, (SELECT * FROM a); SQL error: no such collation sequence: unknown This is surprising to me because I do not see where the collation sequence should matter to this query. To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here: sqlite> CREATE TABLE b (b collate nocase); sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); 0|Goto|0|17| 1|Integer|0|0| 2|OpenRead|0|3| 3|SetNumColumns|0|1| 4|Integer|0|0| 5|OpenRead|2|3| 6|SetNumColumns|2|1| 7|Rewind|0|14| 8|Rewind|2|13| 9|Column|0|0| 10|Column|2|0| 11|Callback|2|0| 12|Next|2|9| 13|Next|0|8| 14|Close|0|0| 15|Close|2|0| 16|Halt|0|0| 17|Transaction|0|0| 18|VerifyCookie|0|4| 19|TableLock|0|3|b 20|Goto|0|1| 21|Noop|0|0| Question 2: Why does this happen, and is there a way to work around the problem by issuing explicit collation sequences? Thanks, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
Are there any opinions on this list, or should I just post a bug ticket? Ralf >Imagine that a SQLite3 database opened in a custom application with a >registered a collation sequence named "unknown" has created the following >table: > > CREATE TABLE a (b COLLATE unknown); > >Now open this table in the default SQLite3 CLI. Up to here, everything works >as expected. > >Now some peculiar observations: > > >1. Issue "PRAGMA collation_list;" and notice that "unknow" lists next to the >other registered collations, even though "unknown" is not registered with the >default SQLite3 CLI: > >sqlite> PRAGMA collation_list; >0|unknown >1|NOCASE >2|BINARY > >Question 1: Is this the expected behaviour, or should not "PRAGMA >collation_list;" rather list registered collations only? > > >2. Running simple queries like "SELECT * FROM a;" work fine. But subselects, >in their most basic form and with no sorting or comparisons, do not: > >sqlite> SELECT * FROM a, (SELECT * FROM a); >SQL error: no such collation sequence: unknown > >This is surprising to me because I do not see where the collation sequence >should matter to this query. > >To demonstrate, here is the explain output of a table with a registered >collation sequence. No mention of the collation name here: > >sqlite> CREATE TABLE b (b collate nocase); >sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); >0|Goto|0|17| >1|Integer|0|0| >2|OpenRead|0|3| >3|SetNumColumns|0|1| >4|Integer|0|0| >5|OpenRead|2|3| >6|SetNumColumns|2|1| >7|Rewind|0|14| >8|Rewind|2|13| >9|Column|0|0| >10|Column|2|0| >11|Callback|2|0| >12|Next|2|9| >13|Next|0|8| >14|Close|0|0| >15|Close|2|0| >16|Halt|0|0| >17|Transaction|0|0| >18|VerifyCookie|0|4| >19|TableLock|0|3|b >20|Goto|0|1| >21|Noop|0|0| > >Question 2: Why does this happen, and is there a way to work around the >problem by issuing explicit collation sequences? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
>> Imagine that a SQLite3 database opened in a custom application with a
>> registered a collation sequence named "unknown" has created the following
>> table:
>>
>> CREATE TABLE a (b COLLATE unknown);
>>
>> Now open this table in the default SQLite3 CLI. Up to here, everything works
>> as expected.
>>
>> Now some peculiar observations:
>
>> 2. Running simple queries like "SELECT * FROM a;" work fine. But subselects,
>> in their most basic form and with no sorting or comparisons, do not:
>>
>> sqlite> SELECT * FROM a, (SELECT * FROM a);
>
>That's not just a subselect, it's also a join. Does a subselect on
>its own have the same behavior?
Thanks all for the feedback.
Trevor, I am not sure what you mean by "subselect on its own". Is this what you
are looking for?
sqlite> INSERT INTO a VALUES ('one');
sqlite> SELECT * FROM (SELECT * FROM a);
SQL error: no such collation sequence: unknown
sqlite> SELECT *, * FROM a;
one|one
Ralf
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Lemon: Conflicts with repeated TERMINALS
I am trying to write a Wiki parser with Lemon. The Lemon features suite my needs perfectly, but I am unfortunately stuck with the problem of parsing conflicts. All conflicts seem caused by repeat constructs like this: text ::= textpiece. text ::= text textpiece. The complete grammar follows below and results in 10 conflicts. I have read the manual, looked at tutorials, and searched the mailing list, but nothing helped me to reduce the number of conflicts. Changing token order even tends cause more of them. Reading similar grammars for Bison makes me wonder why Bison apparently has no problems with them but Lemon does. Am I doing something wrong or is this simply not possible with Lemon? Ralf --- article ::= blocks. blocks ::= block. blocks ::= blocks block. block ::= heading. block ::= paragraph. heading ::= HEADING_START text HEADING_END. heading ::= HEADING_START text. heading ::= HEADING_START. paragraph ::= text NEWLINE. paragraph ::= paragraph text NEWLINE. paragraph ::= text. paragraph ::= paragraph text. text ::= textpiece. text ::= text textpiece. textpiece ::= TEXT. textpiece ::= LINK. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon: Conflicts with repeated TERMINALS
Many thanks, Joe, >Your grammar is ambiguous. The text tokens run together for >various rules because the grammar lacks clear separators between >them. OK, I begin to understand. The "clear separators" need to be TERMINALs, right? I believed that these were imlicit because there are TEXT and LINK after all text tokens are fully expanded. Therefore I thought that the grammar would not be ambiguous. >You can fix it a million ways by altering your grammar. Thanks for the suggestions - I can see that they do not generate conflicts, but they certainly alter the grammar. >Here is one way: > > article ::= blocks. > > blocks ::= block. > blocks ::= blocks block. > > block ::= heading. > block ::= paragraph. > > heading ::= HEADING_START text HEADING_END. > heading ::= HEADING_START text. > heading ::= HEADING_START. > > paragraph ::= PARA text. > > text ::= textpiece. > text ::= text textpiece. > > textpiece ::= TEXT. > textpiece ::= LINK. I observed the new PARA terminal token (the clear separator!?). Unfortunately the lexer does not generate such a token. Paragraph repeats are also removed. >Here's another: > > article ::= blocks. > > blocks ::= block. > blocks ::= blocks block. > > block ::= heading NEWLINE. > block ::= paragraph NEWLINE. > > heading ::= HEADING_START text HEADING_END. > heading ::= HEADING_START text. > heading ::= HEADING_START. > > paragraph ::= text. > > text ::= textpiece. > text ::= text textpiece. > > textpiece ::= TEXT. > textpiece ::= LINK. This one also removes paragraph repeats, doesn't it? Unfortunately paragraphs need to repeat for my grammar. Is there a way to achieve this without conflicts? >Lemon generates an .out file for the .y file processed. >You can examine it for errors. I have tried to make sense of the .out file before. It tells me where to look for the problem, but not how to fix it ... I am sorry to appear stupid, but I still can not make sense of it all. Can someone still help, please? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
Hello Trevor Talbot, >> Trevor, I am not sure what you mean by "subselect on its own". Is this what >> you are looking for? > >> sqlite> SELECT * FROM (SELECT * FROM a); >> SQL error: no such collation sequence: unknown > >Yes, exactly. I was curious to see if it made any kind of difference. >Unfortunately I don't have an explanation/fix for you though. I created two tickets about these collation peculiarities yesterday. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon: Conflicts with repeated TERMINALS
Joe Wilson <[EMAIL PROTECTED]> wrote: >The following grammar may be clearer to you: Yes, it is many thanks! I believe I am making progress! At least I can see the picture much clearer now and was able to come up with the following grammar with just one conflict unsolved: %left NEWLINE. /* Do these matter here at all? */ %nonassoc TEXT LINK. %left HEADING_START. %left HEADING_END. article ::= blocks. blocks ::= block. /* EOF */ blocks ::= blocks NEWLINE./* EOF */ blocks ::= blocks NEWLINE NEWLINE block. block ::= . /* EOF */ block ::= paragraph. block ::= heading. heading ::= HEADING_START text HEADING_END. paragraph ::= line. paragraph ::= paragraph NEWLINE line. line ::= text. text ::= textpiece. text ::= text textpiece. textpiece ::= TEXT. textpiece ::= LINK. I of course appreciate any comments ;-) My idea is that * A block can be either a paragraph or a heading. Multiple blocks are separated by two NEWLINEs. * A paragraph is made up of n >= 1 lines. Each line within a paragraph ends with a single NEWLINE. Two NEWLINEs start a new block (see above). * A line consists of text, which can be TEXT or LINK. Not all works well with the grammer, and unfortunately I do not understand why. Given this input, for example: TEXT, NEWLINE the parser gets stuck at paragraph ::= paragraph NEWLINE line. instead of falling back to the line above paragraph ::= line. to find the conditions of a paragraph fulfilled. Why does it not try the other alternatives? Or are there none in the grammar? >Try reading some papers on parsing or search for the book >"Compilers: Principles, Techniques, and Tools" (a.k.a. >the dragon book). I certainly will. >Also try writing on paper random sequences of tokens and >manually parse your grammar to see the conflicts firsthand. As I throw different token sequences to my experimental parser I am slowly starting to make sense of the debugger output. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed change to sqlite3_trace()
DRH wrote: >Legacy applications should continue to work. You might get a >compiler warning about a type mismatch on the 2nd parameter to >sqlite3_trace(). But calling a 2-parameter function with 3 >parameters is suppose to be harmless in C. Harmless in C, but not so in other languages. I therefore suppose that the change will break compatability for quite a few non-C applications. Given that it does break backward compatability for many (I personally would not mind given the feature enhancement), I also would not mind to see further enhancements along the line suggested by Roger. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed change to sqlite3_trace()
Roger Binns wrote: >The biggest problem with the trace api is that there is no way to find >out what the bound parameters were. If an application follows best >practise using bound parameters all over then the trace api is rather >useless. Thanks for bringing this up again. There was a thread about how to retrieve bound parameters from a prepared (and bound) statement some time ago. Responses quite some interest in such functionality. Dan finally came up with a very smart, but unfortunately slow, workaround-solution. Search the archive for "sqlite3_bound_int" to find it. I agree with your proposal and believe that it would make a nice addition to the profile callback. More specifically, I would like to see in the callback a pointer to the currently running statement, plus its origin as proposed by Richard (SQL, Trigger, etc). I believe that the statement contains almost all information ever required, like SQL, bound parameters, and future additions. It only needs a few access functions to make use of them, but they will not require additional changes to the profile API. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
Hello Bharath Booshan L, yes, with SQLiteSpy you can do this: drop table if exists t; create table t (id integer primary key, filepath text); insert into t values (1, '/Volumes/Backup/MyMovies/MyMovie.mp4'); insert into t values (2, '/Volumes/Backup/MyMovies/Hello.mp4'); insert into t values (3, '/Volumes/Tiger/MyMovie.mov'); select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; Ralf > I have to perform a search something similar to this > > ID FilePath > 1 /Volumes/Backup/MyMovies/MyMovie.mp4 > 2 /Volumes/Backup/MyMovies/Hello.mp4 > 3 /Volumes/Tiger/MyMovie.mov > > >Search for file name MyMovie should retrieve > > ID FilePath > 1 /Volumes/Backup/MyMovies/MyMovie.mp4 > 3 /Volumes/Tiger/MyMovie.mov > > >To simplify, I am searching for a file name from a collection of absolute >file paths. > >How can I achieve this in SQLite? > >Is there anyways I can use regular expression in a query to perform string >matching. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
Hello Nicolas Williams, >I suppose that to make this generic so that users can replace the >regexp, like, and glob functions would require some new interfaces. I believe so, too. The like and glob optimization is part of where.c and outside the reach of sqlite3_create_function(). >SQLite would have to be able to extract a constant prefix from the >pattern in order to be able to use an idex in this case. The regular expression engine I use is able to tell if a pattern is anchored at the beginning and which letter starts the pattern. I believe that this information is sufficient for an index to narrow down the search. SQLite just needs to provide the API to pass the prefix plus, possibly, which index to use. I believe that this API would also ease implementations of Unicode LIKE and GLOB. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
Hello Bharath Booshan L, >>select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; > >Will this query use index, if we had one, on filepath? No. It will do a full table scan. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unrecognized "Z" UTC time zone signifier
SQLite does not recognize "Z" as the zero offset time zone specifier. "Z" (for
'Zulu time', an alternative name for UTC) is part of the ISO 8601 standard for
date and time representations. See http://en.wikipedia.org/wiki/ISO_8601 for
details.
In this regard, SQLite does not currently follow the standard and rejects the
following valid ISO 8601 dates as NULL:
select datetime ('1981-04-06T14:45:15Z');
select datetime ('14:45:15Z');
As far as I can tell, "Z" support only requires a very minor change in date.c,
parseTimezone() to recognize the if the "Z" character is present. No further
timezone modification is necessray.
A test case scenario would be
datetest 5.8 {datetime('1994-04-16 14:00:00Z')} {1994-04-16 14:00:00}
# According to Wikipedia, timezone should directly follow time.
# SQLite, however, allows whitespace inbetween.
# Question: Does this conform to ISO 8601?
datetest 5.9 {datetime('1994-04-16 14:00:00 Z')} {1994-04-16 14:00:00}
# Whitespace after Z.
datetest 5.10 {datetime('1994-04-16 14:00:00Z ')} {1994-04-16 14:00:00}
# Whitespace before and after Z.
datetest 5.11 {datetime('1994-04-16 14:00:00 Z ')} {1994-04-16 14:00:00}
If "Z" timezone support was implemented, I would volunteer to update the date
time function documentation in the SQLite Wiki.
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Columns for PRAGMA table_info and index_info I'd like to see
Hello, PRAGMA table_info and PRAGMA index_info are a useful source of information, and I wish they would show even more details. For PRAGMA table_info: * The column's collation sequence, if specified For PRAGMA index_info: * The column's collation sequence, if specified * The column's sort order, if specified Little changes only and very useful for SQLite manager applications, but they might open the floodgates. What do others think? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unrecognized "Z" UTC time zone signifier
[EMAIL PROTECTED] wrote:
>Ralf Junker <[EMAIL PROTECTED]> wrote:
>> SQLite does not recognize "Z" as the zero offset time zone specifier.
>
>SQLite does not currently accept any timezone specifiers, other
>than a hard-coded timezone offset:
>
> 1981-04-06T14:45:15+01:00
>
>If we start accepting any symbolic timezone names, seems like we
>would then need to start accepting them all. If am reluctant to
>open the floodgates
Yes, I know about your strict policy of adding new features to SQLite, and
please know that I do appreciate it. But this does not mean we have go give up
easily on new features, but provide better arguments instead. Let my try:
1. "Z" is part of the ISO standard and therefore used with external date and
time data. Supporting it makes it much easier to import such data into SQLite.
2. "Z" is not a soft-coded timezone specifier like "CET" or similar. It is just
a special case of the hard-coded "1981-04-06T14:45:15+00:00".
3. Using "Z" explictly distinguises UTC from local time zones and avoids
disambiguities.
4. Asking for "Z" I do not want to open any floodgates. If this was my
intention, I would have asked for
* ±[hh][mm] and ±[hh] -- currently missing but nice to have, IMHO
* named timezones ('MET' or 'Europe/Moscow') -- just a joke
* daylight saving time support -- kidding only
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unrecognized "Z" UTC time zone signifier
Aristotle Pagaltzis wrote: >* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2008-02-21 13:45]: >> Ralf Junker <[EMAIL PROTECTED]> wrote: >> > SQLite does not recognize "Z" as the zero offset time zone >> > specifier. >> >> If we start accepting any symbolic timezone names, seems like >> we would then need to start accepting them all. >Not hardly. FWIW, the IETF recommendation for timestamps in >any new internet standards is to use the format specified in >RFCÂ 3339, which is based on codified experience. For time zones, >it prescribes that they be given as either a numeric offset or >`Z` a shortcut for `+00`; no provision is made for other symbolic >names as those only cause trouble. So you should have no trouble >refusing requests to support those. Richard did it, and it works like a charm: http://www.sqlite.org/cvstrac/chngview?cn=4805 Many thanks! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to set memory usage as high as possible -- but not too high?
I need to create a huge database (about 6 GB, more than 6 mio records, blobs, and FTS text) in as little time as possible. Since memory is the key to speed, I try to use as much memory as is available. However, there is the danger of running out of memory. This is where memory usage control comes into play. I can see there are two options: * OPTION 1: PRAGMA cache_size = 1000; Advantage: SQLite will use ample memory, but no more than that. Disadvantage: Difficulty to establish exact memory requirements in advance. The help states that "Each page uses about 1.5K of memory.", but I found this to be wrong. Memory usage obviously depends on the page size, and my measurement shows that there is an additional small overhead of undocumented size. Is there a formula to calculate the required memory for a cache_size of x? * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes); Advantage: Memory limit can be set to a known value (amount of free memory as returned from the OS). Disadvantage: My tests indicate that SQLite slows down drastically when it hits the memory limit. Inserts drop from a few hundred per second to just one or two per sec. * OPTION 3: Catch out-of-memory errors and reduce cache_size accordingly (untested scenario). Advantage: Use memory up to the least bits available. Disadvantage: How to avoid data loss after the out-of-memory error. Can I just call sqlite3_release_memory(some_bytes) and sqlite3_step again and again until it passes without the out-of-memory error? This raises a few questions: * Do sqlite3_soft_heap_limit(), or "PRAGMA cache_size=x;", or both establish SQLite's upper memory limit? Do they work independently of each other, i.e. does the lower limit always kick in first? * Does "PRAGMA cache_size=some_smaller_value;" cause SQLite to free used pages and release their memory straight away? * Is there another runtime -- important! -- setting to establish a maximum memory limit, possibly undocumented? In the end this boils down to a simple problem: * Wow to keep SQLite's memory usage as close to, but not exceeding the memory available to applications? I will be very grateful for any suggestion! Many thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set memory usage as high as possible -- but not too high?
Roger Binns wrote: >Are you using a 32 bit or 64 bit process. 32, but it does not matter to the problem. >Also is there a requirement to create the database in the filesystem? Yes. >If not you could ensure your swap is sufficiently large (I use a mininmum of >16GB on my machines :-) and create in a tmpfs filesystem, and then copy the >database to >persistent storage when you are done. The aim is to avoid slow swap memory but use fast RAM only. >You also didn't list turning off synchronous etc while creating the database >and turning it back on when done. Performance settings are: * PRAGMA locking_mode=exclusive; * PRAGMA synchronous=off; * Disable journal file :-) >I am curious why you think memory is the bottleneck anyway! It has often been pointed out on this list that inserts into indexed tables (regular or FTS) run faster with a high page cache. My own tests 2nd this. A few 100 MB more or less can make an difference of more than 100%. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set memory usage as high as possible -- but not too high?
Dan, many thanks for the quick response and detailed answers. However, a question or two still puzzle me. >> * OPTION 1: PRAGMA cache_size = 1000; >> >> Advantage: SQLite will use ample memory, but no more than that. >> >> Disadvantage: Difficulty to establish exact memory requirements in >> advance. The help states that "Each page uses about 1.5K of >> memory.", but I found this to be wrong. Memory usage obviously >> depends on the page size, and my measurement shows that there is an >> additional small overhead of undocumented size. Is there a formula >> to calculate the required memory for a cache_size of x? I'd be curious if you know an answer to this, too? >> * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes); >> >> Disadvantage: My tests indicate that SQLite slows down drastically >> when it hits the memory limit. Inserts drop from a few hundred per >> second to just one or two per sec. > >That is an odd result. How did you test it? I set up a high cache_size and a lower sqlite3_soft_heap_limit() and started inserting blobs. >What was the memory limit? Any chance the machine started using swap space? I will test again and let you know. >> * Does "PRAGMA cache_size=some_smaller_value;" cause SQLite to free >> used pages and release their memory straight away? > >No. If the cache_size parameter is set to a value that >is less than the number of pages currently allocated for the >cache, no more pages will be allocated. But no existing >pages will be freed. Good to know. So I would reduce the cache_size and then use sqlite3_release_memory() to free memory, right? Maybe this is worth documenting? >Does SQLite really run faster with 1GB available than it would with 100MB? Yes. If there are indexes (regular or FTS) on the table, SQLite needs quick access to lots of pages for searching and rearranging b-tree entries. My timings show that 100MB or 500MB can sometimes make a difference of more than 100%. Richard recently talked about upcoming indexing performance improvements. I wonder if they are part of the performance refactoring due with the next release? :-) Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob api
>Ok, I was kinda hoping for a more "permanent" solution such as: Did you consider creating a view? >int sqlite3_cast_column_type(sqlite3* db, const char* zDBname, const char* >zTable, const char* zColumn, int newColumnType); > >that would cast the column into the desired type, returning SQLITE3_ERROR if >the cast is invalid (like from double to integer, or text to numeric). You can use a CASE statement for this: select case typeof (a) when 'real' then a else 'Invalid type: ' || typeof (a) end from my_table; Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob api
>> I'm using the sqlite3_blob_* api to write a larger text stream >> incrementally. Works a charm, but is there a way to >> change the datatype of the blob to text afterwards ? I'd like to see >> the text easily in f.i. SQLiteSpy. > >Perhaps: SELECT CAST(b AS TEXT) FROM table Yes, this works well in SQLiteSpy. >Really I suppose it depends on what SQLiteSpy is using to >determine that the column type is BLOB. SQLiteSpy determines the type of each record cell individually, just as it is returned by sqlite3_column_type(). So casting a BLOB to text will display it as such in SQLiteSpy. In fact, SQLiteSpy colors the cell backgrounds by data type: White: Text Green: Integer Violet: Float Blue: Blob Red : Null Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to retrieve number of cached pages in memory?
I need to retrieve the number of pages a SQLite database connection has currently allocated in memory. The documentation unfortunately turned up no results. I know about "PRAGMA cache_size", but this returns the maximum number of pages possibly allowed in the cache, not the actual number of pages currently cached. My aim is to calculate the accurate number of bytes actually consumed by a single cached page. This figure will then allow to set PRAGMA cache_size to a more precise value in order to limit memory usage. I do mind using undocumented APIs and will not cry tears if they change without notice, so any pointers are welcome! Many thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve number of cached pages in memory?
> See the "Pager" data structure and associated variables and functions > in "sqlite-3.5.x/src/pager.c". OK, it seems that the number I am interested in is stored as part of the Pager struct: int nPage; /* Total number of in-memory pages */ Now I just have to figure out how to access this number reliably. Many thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set memory usage as high as possible -- but not too high?
Jay A. Kreibich wrote: >> >Are you using a 32 bit or 64 bit process. >> >> 32, but it does not matter to the problem. > > When you give examples like "PRAGMA cache_size = 1000;", or 10M > pages which would take something on the order of 15 GB of memory to > service with the default 1K page size, a few of us start to wonder. Good point. But I believe you misunderstood my intention. I was not interested in absolute but in relative numbers, regardless of a 32 or 64 bit system. All I need to know is related to the amount of RAM available when the application starts. The aim is to use as much RAM as possible, but never more than reasonably available. The last bit is the difficult one. >> >I am curious why you think memory is the bottleneck anyway! >> >> It has often been pointed out on this list that inserts into >> indexed tables (regular or FTS) run faster with a high page cache. >> My own tests 2nd this. A few 100 MB more or less can make an >> difference of more than 100%. > > Given that the default page cache is 2000 pages, or on the order of > 3MB, it seems that you're hitting some serious limits. If hundreds > of megabytes (!) is giving you a return on the order of 2x, then there > is no magic bullet-- you aren't going to find a setting that suddenly > gives you a 10x speedup. You're hitting diminishing returns in a > serious kind of way. Some numbers: 32 bit app, inserting 6 million records into 2 tables each (text, fts, and blob). The database finally grows to over 6 GB in size. As the last step, a simple index is created on one text field. With the default 2000 pages cache size (1 KB page size), this takes about a full day or more. Raising the page cache to some 18 pages uses about 270 MB of memory but brings the timing down to less than one hour. My testing shows that inserts with lots of random disk searches (indexes, fts) hugely benefit from a large cache size for the simple reason that it reduces disk IO. > Personally, I'd pick a number, like half your RAM size or ~1.5GB* > (whichever is smaller), set the page cache, and be done with it. That's what I ended up doing. In addition, I regularly check sqlite3_memory_used() and reduce the page cache if it exceeds the limit. Then I call sqlite3_release_memory() repeatedly until the memory usage has dropped sufficiently. > It sounds like you've already found most of the other PRAGMAs that > are going to get you something. You might be able to tune the size > of your INSERT transactions, but if you're around 1000 or so, going > higher isn't likely to buy you too much. Currently I use just a single transaction for all inserts into a newly created database. This reduces the number of cache flushes to a single time when all data is inserted and just the used memory is being freed. As another optimization option I am looking forward for the new journal pragma and will hopefully not need to use journal file after all. Thanks for the feedback and my apologies for the late response, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View bindings for a statement
Cole Tuininga wrote: >The question is, is there an easy way to extract the actual query >(with the bound variable set) from the statement handle? This topic has already been discussed in length some time ago: http://www.mail-archive.com/sqlite-users@sqlite.org/msg28610.html It would be beautiful if some of the interfaces suggested there would one day make it into SQLite! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS search negative term syntax
Hello,
I have a small concern about the FTS negative term search syntax. Currently,
all terms following any minus sign ("-") are excluded from the search. This is
a very welcome feature, but consider searching for these hyphenated words:
Coca-Cola -> FTS finds Coca, but never Cola
low-budget -> FTS finds low, but never budget
twelve-year-old -> FTS finds twelve, but never year and never old
part-time -> FTS finds part, but never time
full-time -> FTS finds full, but never time
These results do not match what most users will expect. Well, one can ask them
to leave out the minus sign, but users will habitually leave it in because they
learned from major search engines that it is the intended behavior. Consider
Google, which explicitly states:
"Note: when you include a negative term in your search, be sure to include a
space before the minus sign."
Source:
http://www.google.com/support/bin/static.py?page=searchguides.html&ctx=basics
Therefore I would like to consider adding these search syntax rules:
1. A minus sign excludes a search term only when located at the beginning of
the search query or after a white space (space, tab, etc.):
"low-budget" -> Find both low and budet.
"low -budget" -> Find low, but not budget.
"-low budget" -> Do not find low, but find budget.
"-low-budget" -> Do not find the "low budget" phrase.
2. In case the minus sign is a term separator and two or more search terms are
separated by sisngle minus signs only, they constitue a phrase search:
"twelve-year-old" -> "twelve year old" (phrase search)
"part-time" -> "part time" (phrase search)
I believe that these changes would make the FTS search syntax more intuitive to
use and more conformant to major search engines.
Would there be a chance that they could be implemented in current FTS3 and/or
the upcomming FTS4?
Any thoughts?
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS search negative term syntax
>Fair warning, though: It's not entirely clear that the fts search >syntax should aim to hew too closely to consumer-oriented search >syntax. Interesting point, too. Up to now, I always perceived the FTS search syntax to be very much consumer-oriented. It it just too similar to major search engines to be primarily machine-oriented. As it stands now, FTS syntax can of course be machine generated, if that is what you are aiming at. I believe that this should remain easy to do. And my suggested minus sign modification would not change this, would it? >It's sort of in a strange place, most people would think it a >poor idea (indeed, dangerous!) to put user-entered expressions in >their WHERE clauses. I am not sure I understand the danger. Say I sqlite3_bind() the FTS match query, do you see this as a serious security risk (FTS injection) or a potential performance jeopardy, or something else? >Caveat for the above: I've spent all of five minutes thinking about >your posting, and I was interrupted in the middle. But I'll try to >factor it in to future thinking. Thanks for your time and your thoughts! Ralf PS: I see little traffic on <[EMAIL PROTECTED]>. Is this intentional, or should this and simliar topics better be discussed there? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_mode not implemented?
>In PERSIST mode, you have two files associated with your database >instead of one. Whenever you move, copy, or rename the database file >you *must* also move, copy or rename the journal file to prevent >database corruption. > >The persistent journal file uses disk space that might otherwise have >been returned to the operating system and made available to other >programs. Say I have a database open in PERSIST mode for fast operation. Before the app shuts down, I would like to detach the database of its journal file (to free some storage space and guarantee a self contained, single file database). Will a simple "PRAGMA journal_mode=DELETE" do this for me and automatically delete the journal file when (or even before) I close the database? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
>> Drat. It doesn't look like there's a way to see what's already been >> bound to a statement either, correct? See this thread for a previous disuccsion of the problem: http://www.mail-archive.com/sqlite-users@sqlite.org/msg28610.html Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ticket 3128: DISTINCT -> GROUP BY can lead to performance degradation
Correcting myself: This should NOT happen as SQLite usually rejects duplicate rowids with a constraint error. >This should happen as SQLite usually rejects duplicate rowids with a >constraint error. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ticket 3128: DISTINCT -> GROUP BY can lead to performance degradation
I looked at the database attached to the ticked and noticed that the table contains NULL rowids as well duplicate rowids. This should happen as SQLite usually rejects duplicate rowids with a constraint error. When I run this query: select rowid, count() from ndxparamvalues_localizedstring group by rowid order by 2 desc I get the following results: rowid count () NULL 1759 10 1601 20 1341 30 1281 40 1254 50 1200 ... more results follow, 3161 in total. So I wonder how you managed to fill your database with duplicate rowids? I also wonder if this is somehow related to your "problem"? Ralf >I've only found one reference to slower queries with the DISTINCT/GROUP >BY optimization that went in back in November for 3.5.3 and later. I >would have expected more given the number of our queries causing problems. > >The problem as I wrote in ticket 3128 ><http://www.sqlite.org/cvstrac/tktview?tn=3128> appears to be with >queries that use DISTINCT and LIMIT. If the query is somewhat slow (in >my example, it's joining several large tables), the new DISTINCT >(implemented in the code as GROUP BY) can be much, much slower because >it (apparently) collects all or most of the rows before applying GROUP >BY, even when there's a LIMIT. Before the change (3.5.2 and earlier), >DISTINCT was aided by the LIMIT quite a bit. In my example, the query >takes around 0.02 seconds in 3.5.2 and earlier, 3.1 seconds in 3.5.3 and >later. > >Has anyone else seen similar behavior? If so, have you found a >workaround? My workaround is actually a patch to the source to disable >the optimization when a LIMIT is given, though there might be cases >where this is undesirable (like maybe cases where the table has indices >that can be used by GROUP BY and the LIMIT is sufficiently high). > >Brad Town ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ticket 3128: DISTINCT -> GROUP BY can lead to performance degradation
>> This should NOT happen as SQLite usually rejects duplicate rowids with a >constraint error. > >For this table, the badly-named column "RowID" is actually a text field >that's allowed to be null, not a primary key. > >-- Describe NDXPARAMVALUES_LOCALIZEDSTRING >CREATE TABLE 'ndxParamValues_LocalizedString' >( >'FamilyId' INTEGER NOT NULL , >'ParamId' INTEGER NOT NULL , >'RowID' TEXT COLLATE NOCASE, >'Index' INTEGER , >'Value' INTEGER NOT NULL >) My oversight, sorry for that. I never questioned that SQLite would reject reserved word column names, but I now see that this is not so. This alerts me to a potential danger for SQLite managers which must rely on some means to retrieve THE RowID which uniquely identifies a record for in-grid table editing. If the "RowID" name can be hijacked by other columns and given another purpose, it poses the danger that wrong wrong columns are updated and data is corrupted. How can I access the "RowID" given the above table declaration? I know about the "OID" and "_ROWID_" synonyms, but searching the documentation I find that they, too, can be used by other columns. I can therefore not see any non-ambiguous, reserved column name or API call to retrieve the implicit RowID value in such cases, especially if no primary key has been set like in the above schema. Any thoughts, especially from the SQLite developers? Thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Bradley A. Town wrote: >Ralf Junker wrote: > >> This alerts me to a potential danger for SQLite managers which must rely on >> some means to retrieve THE RowID which uniquely identifies a record for >> in-grid table editing. If the "RowID" name can be hijacked by other columns >> and given another purpose, it poses the danger that wrong wrong columns are >> updated and data is corrupted. >> >> How can I access the "RowID" given the above table declaration? I know about >> the "OID" and "_ROWID_" synonyms, but searching the documentation I find >> that they, too, can be used by other columns. >> >> I can therefore not see any non-ambiguous, reserved column name or API call >> to retrieve the implicit RowID value in such cases, especially if no primary >> key has been set like in the above schema. >> >> Any thoughts, especially from the SQLite developers? >> >> Thanks, Ralf >> >Creating another thread for this to avoid thread hijacking. Thanks, I did not mean to hijack the thread. But thinking more about hijacking "RowID" I am glad this is now a separate thread. Lack of a reseverd "RowID" column name to guarantee unambiguous record operations by general SQLite tools is a potential thread to data security IMO. I would very much appreciate if this could be addressed in a future version of SQLite! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Federico Granata wrote: >have you seen here http://www.sqlite.org/autoinc.html ? Yes, I did. This documentation actually made me realize that the problem is not an implementation flaw but a design error, IMO. See my other answer in this thread for more rationale. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Darren Duncan wrote: >I think the real problem here is that SQL allows you to have non-distinct >rows in a table, when all rows should be distinct. SQLite's implicit "RowID" does not allow non-distinct values (except for NULL, but this is documented behavior and only maintained for backwards compatability. It might change in a future version. The sooner, the better, IMHO). >Working within SQL's flaws, the solution here is for every table to have a >unique constraint on one or more table columns. Then applications just use >that to uniquely identify the row. This is exactly the concept of "RowID". Nothing wrong with that. My only criticism is that this concept can be rendered non-functional by redefining the "RowID" so that it violates the uniqueness constraint. Example: CREATE TABLE x ( RowID TEXT); Now the implicit unique RowID is no longer accessible via the "RowID" column. Workarounds are "_rowid_" or "OID", but they can be overwritten as well: CREATE TABLE x ( RowID TEXT, _rowid_ text, oid text); For this table, it is no longer possible to access the implicit, unique RowID. General database applications (GUI managers, for example) can no longer (re-) identify a particular record! >Rows should be identifiable by user-visible data, not hidden data, since a >database is supposed to model reality and people identify things based on >their someway-visible attributes. This is what INTEGER PRIMARY KEY is for: It works as a duplicate for the implicity "RowID": CREATE TABLE x ( ID INTEGER PRIMARY KEY); For this table, the visible "ID" and the implicit "RowID" access the same unique data. This is the recommended usage and poses no problems. Problems only arise if "RowID" is re-defined differently as demonstrated above! >On a separate note, it is best for one to be able to name a table or column >et al anything one wants, with all the choice of names as you can store in >a text column for user data. Reserved words aren't an issue as long as >entity names are referred to with an unambiguously different syntax, such >as quoted identifiers as SQL does support. Then database users don't have >to worry about implementation details and can name tables and columns >whatever they want; saying they can't name their column "RowID" is a leaky >abstraction. Sure we all dislike restrictions. Can you suggest an alternative to a single reserved name to represent the column which uniquely identifies a database record under any and all circumstances? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Darren Duncan wrote: >Ralf Junker wrote: > >>Can you suggest an alternative to a single reserved name to represent the >>column which uniquely identifies a database record under any and all >>circumstances? > >Yes, change the interface to RowID into a routine call rather than a column >name; eg use "RowID()" rather than "RowID". I can not see how this would actually work with SQLite. Any use-created RowID column would override and hide the implicit rowid column even for the RowID() function, would it not? >Then when using it in a SELECT, you can say "RowID() as foo" in the select >list where "foo" is different than a normal table field. Such is how >'standard' SQL does it. What is 'standard" SQL? Can you give an example how this is used with other DB engines? I am not familiar with MySQL, but searching the documentation I could not find that it supports this concept. Maybe others do? >Any manager app can read the database schema first and generate a name "foo" >that is distinct. As things are at the moment, the implicit, unambigous RowID can not be retrieved from the database schema if all three "RowID", "_rowid_", and "OId" column names are overridden. This applies to SQL as well as to user-defined functions. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unrevolved _sqlite3ExprCodeAndCache with SQLITE_OMIT_TRIGGER
Lattest cvs sqlite version: If SQLITE_OMIT_TRIGGER is set, linker complains about an unresolved _sqlite3ExprCodeAndCache symbol. sqlite3ExprCodeAndCache is defined in expr.c and wrapped with #ifndef SQLITE_OMIT_TRIGGER. However, references in insert.c, line 536 update.c, line 348 and 362 are not wrapped with #ifndef SQLITE_OMIT_TRIGGER. I followed the suggestion quoted below (posted earlier to this list) without avail. Is it safe (or even required?) to change sqliteInt.h to #ifndef SQLITE_OMIT_TRIGGER void sqlite3ExprCodeAndCache(Parse*, Expr*); #else # define sqlite3ExprCodeAndCache(A,B) #endif Ralf >> parse.obj : error unresolved external symbol _sqlite3VtabArgExtend in >> function _yy_reduce >> parse.obj : error unresolved external symbol _sqlite3VtabArgInit in function >> _yy_reduce >> parse.obj : error unresolved external symbol _sqlite3VtabBeginParse in >> function _yy_reduce >> parse.obj : error unresolved external symbol _sqlite3VtabFinishParse in >> function _yy_reduce >> >> These functions are declared in sqliteint.h line 1863-1866. >> These functions are implemented in vtab.c but enclosed in: >> #ifndef SQLITE_OMIT_VIRTUALTABLE >> >> #endif >> >> And of course, these functions are used in function reduce without any >> SQLITE_OMIT_VIRTUALTABLE check. >> > >You must be trying to use the "parse.c" source file that >is found in sqlite-source-3_3_7.zip. "parse.c" is not really >source code. It is generated code. The source code is >"parse.y". The -DSQLITE_OMIT_VIRTUALTABLE=1 comes into play >when compiling parse.y into parse.c. So if you want to use >-DSQLITE_OMIT_VIRTUALTABLE=1, you have to compile beginning >back at parse.y. > >To do that, you will need some kind of Unix. Make a copy of >Makefile.gcc-linux, rename it as just "Makefile", edit the >setup lines at the top to suite your particular situation, >(for example, add lines that say "OPTS += -DSQLITE_OMIT_VIRTUALTABLE=1") >then type "make". - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unrevolved _sqlite3ExprCodeAndCache with SQLITE_OMIT_TRIGGER
>> Is it safe (or even required?) to change sqliteInt.h to >> >> #ifndef SQLITE_OMIT_TRIGGER >> void sqlite3ExprCodeAndCache(Parse*, Expr*); >> #else >> # define sqlite3ExprCodeAndCache(A,B) >> #endif >> > >The suggested change will probably fail. You can find out >by running: > > make fulltest I can do this (sidenote: many of the vtab tests fail). I use cygwin on Win32 and run: sh configure makek fulltest Question: Where do I introduce SQLITE_OMIT_TRIGGER? >A safer fix would be to remove the #ifndef SQLITE_OMIT_TRIGGER >from around the sqlite3ExprCodeAndCache function. Thanks, will do. >You should also file a bug report. Done. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Error retrieving FTS1 offsets for single MATCH only
I want to use this SQL query to retrieve the offsets information for a single FTS1 result: select rowid, offsets (x) from x where Content match 'search' and rowid = 1; Surprisingly, the query fails due to Error 1: unable to use function MATCH in the requested context. It seems that MATCH can not be combined with other conditions in a WHERE clause. I then tried the query as a subquery, but this also produces the same error. Is there a workaround? I would prefer not to store all offsets from all results in order to keep memory requirements low. Thanks for any suggestion, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: problem with tilde
Hello [EMAIL PROTECTED], > I am using SQliteSpy 1.5.5 for to execute de sql statement for fill the >table. SQLiteSpy is fully Unicode enabled, including the SQL editor. Hence, it correctly stores text in whatever UTF format your database uses. This includes tilde as well as French accented characters, German umlauts, Russian cyrillic characters, and more. > In the menu Execute of SQliteSpy exist the element "text to unicode >conversion", i convert the database to UTF-8 but the problem continue. This is a convenience function if you want to convert non-Unicode text in your database to to Unicode. If you have used SQLiteSpy only to add your data, you will never need this function. The function was added because many applications wrongly pass non-UTF-8 text to SQLite functions and can help to convert those databases to Unicode. The Conversion will remedy display problems of non-Unicode characters (which show as an empty boxs with most fonts) in SQLiteSpy and other Unicode enabled software. However, you will receive display problems if you use the converted database in your old, non-Unicode application. In particular, I suspect that your SQLiteTable3 unit is not Unicode aware. You need to add UTF-8 conversion to your application. Alternatively, you can use Unicode enabled Delphi wrappers like DISQLite3 (http://www.yunqa.de/delphi/) instead which supports Delphi WideStrings with functions like: function sqlite3_bind_str16(const Stmt: TDISQLite3StatementHandle; const ParamIdx: Integer; const Data: WideString ): Integer; function sqlite3_column_str16(const Stmt: TDISQLite3StatementHandle; const Col: Integer ): WideString; which take care of the conversion for you automatically. Btw: SQLiteSpy internally uses the DISQLite3 for all its Unicode functions. Regards, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Format change to fts2 module.
Hello Scott Hess, >http://www.sqlite.org/cvstrac/tktview?tn=2046 should fix this for fts1 and >fts2. I have just tested them in both fts1 and fts2 and the reported problems no longer show! Many thanks for the fixes! Please allow me to report some (compiler-independent) compiler warnings about fts: * fts1.c: Variable i is never used in function static char *firstToken(char *zIn, char **pzTail). Variable j is never used in function static int parseSpec(TableSpec *pSpec, int argc, const char *const*argv, char**pzErr) * fts2.c: Both warnings above also apply to fts2. * SQLITE_EXTENSION_INIT1 If I compile both fts1 and fts2 into the same executable with -DSQLITE_CORE=1 -DSQLITE_ENABLE_FTS1=1 -DSQLITE_ENABLE_FTS2=1 I receive a linker warning that sqlite3_api is defined in both fts1.c and fts2.c. The warning goes away if I remove SQLITE_EXTENSION_INIT1 from both units. I might be wrong, but maybe this is not necessary if the library is compiled with SQLITE_CORE=1? Regards, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Unicode Help
Hello Nicolas Williams, >No, but having built-in functions that can do codeset conversion would >be nice. SQLiteSpy can do this: http://www.yunqa.de/delphi/sqlitespy/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Interbase to SQLite
>Were you saying there was a driver to connect to both Interbase dbExpress. The Delphi help has all the details. >and SQLite like an ODBC driver? DISQLite3 (http://www.yunqa.de/delphi/sqlite3/) gives direct access to SQLite, using the original SQLite API as you know it. Component wrappers or TDataSet descendants are available, too. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Using sqlite.exe
Hello Michael Hooker, >I shall try using a Delphi wrapper later on, DiSQLite3 will probably be the >one I will choose, but so far I'm struggling to understand the examples >because the author has chosen to use a maze of separate units and an >unfamiliar set of external third party components to illustrate what it does >instead of just showing in a straightforward manner how to get data into a >standard Delphi record structure, stringlist, string array or whatever. As the author of DISQLite3: The library now contains 17 example projects for using SQLite with Delphi, even including full text search (FTS1 and FTS2). I designed them to serve two purposes for both beginners and advanced users: * Explain basic and advanced usage of DISQLite3. * Show the power of SQLite with semi real world applications. I agree that the examples use two sets of 3rd party components. I felt they are necessary to overcome some of Delphi's limitations, most notably the missing Unicode controls. Both packages (TNT Unicode Controls and VirtualTrees) are freeware, highly recognized for their outstanding quality and widely distributed. Thanks for letting me know that some aspect of DISQLite3 are apparently still missing from the demos. Regarding record structures, TStringList and string arrays: I did not cover these because of their potentially huge memory requirements. Instead, I demonstrated an intelligent buffering mechanism. However, given the need for it, I will be glad cover these in the demos as well. What exactly do you want to achieve, and what kind of example project are you looking for? >The documentation is very comprehensive but starts half-way through the >film, as far as I'm concerned. Did you read the chapter labeled "Overview"? Again, if you let me know what you are looking for I will be glad to add the missing information. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] cvs checkout: dying gasps from www.sqlite.org unexpected
I recently receive this error message when checking out from CVS: cvs checkout: dying gasps from www.sqlite.org unexpected I am using the latest stable CVS.exe for Windows from http://ftp.gnu.org/non-gnu/cvs/binary/stable/x86-woe/cvs-1-11-22.zip I am running these commands: cvs -d :pserver:[EMAIL PROTECTED]:/sqlite login cvs -z9 -d :pserver:[EMAIL PROTECTED]:/sqlite checkout -P sqlite The CVS help at http://ximbiot.com/cvs/manual/cvs-1.11.22/cvs_21.html#SEC188 reads: "There is a known bug in the server for CVS 1.9.18 and older which can cause this. Is this anything I should worry about? Has anyone else seen the same message when checking out SQLite recently? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] cvs checkout: dying gasps from www.sqlite.org unexpected
>> I recently receive this error message when checking out from CVS: >> >> cvs checkout: dying gasps from www.sqlite.org unexpected >> >SQLite CVS is working fine for me. The server is not showing >any unusual load. It is running a much more recent version of >CVS than 1.9.18. I do not know why you are having problems. I found that the problem isi in CVS.exe. It is present in some versions only (stable ones as well), but I was able to find a fairly recent version which works just fine. The problem is also mentioned in the CVS bug list, but has apparently not yet been fixed. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single-character pathnames in win2k
>> Can somebody who understands or regularly uses windows please >> look into it for me. > >It seems like changing >nByte = GetFullPathNameW(zWide, 0, 0, &zNotUsedW) + 1; >to >nByte = GetFullPathNameW(zWide, 0, 0, &zNotUsedW) + 3; >corrects the problem. Not a solution to the problem, but a small optimization suggestion: According to MSDN (http://msdn2.microsoft.com/en-us/library/aa364963.aspx) there is no need for zNotUsed and zNotUsedW. Both can be replaced by NULL. Here is the relevant quote: lpFilePart [out] A pointer to a buffer that receives the address (in lpBuffer) of the final file name component in the path. Specify NULL if you do not need to receive this information. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite3.3.11 - No differences except for sqlite3.h and os_win.c
Hello Scott Hess, >BTW, http://www.sqlite.org/cvstrac/chngview?cn=3596 fixed a sort of >nasty fts1/2 bug. Just in case you were looking for something else to >pick up :-). Thanks for those fixes, they really work - as tested with sensitive German umlauts like 'ÄÖÜäöü'. Btw: Are there any chances that ticket #2183 could make it into 3.3.12? It is about a nasty crash which happens to FTS2 compiled with SQLITE_OMIT_SHARED_CACHE when many records are inserted. Even though FTS2 is not yet included in any release, the problem actually surfaces in btree.c which might justify it for fix? Also, ticket #2139 should be an easy one to fix for the next version. Once more many thanks for giving us FTS! Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Another (possibly dumb) question
>For select, update, insert, or delete is there a way to get the number >of rows affected? http://www.sqlite.org/capi3ref.html#sqlite3_changes Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Last call for bugs...
>I plan to release 3.3.12 later today or tomorrow. >If you know about any unreported problems, please >get those bug reports in quickly. Tnx. My vote for ticket #2183: It causes SQLite to crash with an access violation. I am keeping my fingers crossed ... Regards, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
Hello Rob Richardson, >The only tool I have for examining and manipulating SQLite databases is >SQLiteSpy. If I want to change data in that program, I have to write an >SQL statement. Every other database editor I've seen lets a use do >simple things from a grid. As the author of SQLiteSpy: There are indeed DB managers which _allow_ cell editing for all SQL SELECTs. Some are able to commit changes to single table SELECTs. However, I have experienced frequent failure updating multiple table, nested, and aggregate SELECTs. There are reason why such updates must indeed fail: The SQLite library lacks certain API required to implement cell editing: Most notably, there is no functionality to retrieve the exact origin (table, row and column) of a cell, which must be known to store back the modified value. These issues have been discussed and acknowledged on this list. I am hopeful that we will eventually see them implemented. Until then, however, there are theoretical limits to grid editing. They could be worked around by parsing the SQL, but this would be overkill for a simple DB editor like SQLiteSpy. I therefore decided to disable cell editing altogether in the first version of SQLiteSpy. > Open a table and data is loaded into a grid, >click on a cell and type in a value and it gets written to the table, >select a row and click a Delete button and the row is deleted from the >table, and so on. There must be a tool somewhere that will do that for >a SQLite database, or there's some feature of SQLiteSpy I don't know >about. Can somebody please point me to one or the other? The upcoming version of SQLiteSpy will see support for table cell editing: The grid will internally work in table mode for tables selected from the schema treeview. Such table SELECTs will be editable, use an improved buffer mechanism, execute faster, and use less memory. Result sets from queries executed from the SQL editor will be read-only, even if they are just simple table selects. I hope to release within the next few days. Regards, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
>> The SQLite library lacks certain API required to implement cell >> editing: Most notably, there is no functionality to retrieve >> the exact origin (table, row and column) of a cell, which must >> be known to store back the modified value. > >See: > > http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name > http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name > http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name I know, and these functions were much appreciated when they entered SQLite. However, they do not cover the table row (RowID). Here is the discussion about what's still missing. The thread also points out a few inconsistencies in the implementation of the above functions, IIRC: http://thread.gmane.org/gmane.comp.db.sqlite.general/19323 Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
Hello Robert Simpson, >For the SQLite ADO.NET wrapper, I actually wrote two functions into the core >engine to help me retrieve key information. My requirements were a little >more complex than just getting rowid, but here's what I did to get me that >far at least: > >Given a sqlite_stmt pointer consisting of a SELECT on one or more tables, > 1. For each table, fetch the internal cursor for that table in the >statement > 2. For each cursor, fetch the rowid > >That was the API modification part. Caveats are that this information is >not always available. So if its not, I just return a failure indicator. Thanks for backing me up on my problem. However, unlike you I am very reluctant to hack the SQLite because of the concerns you mentioned. I would love to see something similar to what you have done officially supported by SQLite. >The next part involved querying the schema of each table, looking up indexes >and finding the most logical primary key definitions for the table(s) involved >in the select, and compiling a side-by-side SELECT clause returning the user's >defined primary keys using a WHERE clause with the above fetched rowid's. For cell updates, SQLiteSpy would be happy to use the RowID index, which is auto-generated and always available. Hence a function like sqlite3_column_rowid() would just fill the gap perfectly. Let's see what the future brings ... Regards, Ralf PS: For anyone interested: The new SQLiteSpy with table cell editing is now available at http://www.yunqa.de/delphi/sqlitespy/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
Hello Nemanja Corlija, >>PS: For anyone interested: The new SQLiteSpy with table cell editing is now >>available at http://www.yunqa.de/delphi/sqlitespy/ >Ralf, thanks SQLiteSpy. Download link is not working though. Thanks for reporting the problem. The link is now fixed and working all right. Regards, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extension functions for SQLite in C for free
Hello Mikey C, >If anyone is having problems downloading the file (which is large as it >contains debug & release binaries and all the obj files), please email me at >[EMAIL PROTECTED] and I'll email just the raw source code only. Would it be possible to upload just the raw source code as a separate archive? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Precision of dates stores as Julian "real"
>> > So make the wiki available for download. ;) >> >> I would like this too. ;) >> >> Often I'm working without an internet connection and a having a local >> copy of the Wiki would be extremely useful. >> > >Been working on this for years. Literally. I just never seem to >find the time to complete the project. When we talked about Wiki vs. Source Tree documentation on this list some time ago there was a desire to have some "Reference" or "Feature" documentation bundled with each release. IMO, the wiki can add to this but should not replace it. This feature / version / reference documentation is especially usefull for working with older versions when the wiki does fails to record when new features entered the library. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] custom collation problem with delphi
Hello jp, with DISQLite3, I use the the following colation callback function without problems: function SQLite3_Compare_System_NoCase_Ansi( UserData: Pointer; l1: Integer; const s1: Pointer; l2: Integer; const s2: Pointer): Integer; begin Result := CompareStringA(LOCALE_SYSTEM_DEFAULT, NORM_IGNORECASE, s1, l1, s2, l2) - 2; end; This function should be functionally equivalent to your implementation, but accesses the Win32 API directly. Also, it does not implicitly convert the PChar pointers to AnsiStrings like your call "copy(B,1,lenB);" implicitly does. Notes: * The above function is not declared as "cdecl" because DISQLite3 uses the faster "register" calling convention instead. * The above function (just as yours) does not treat UTF-8 sequences properly. You might want to consider a WideString function instead, depending on the data you are processing. Regards, Ralf >I have a custom collation which worked well in 3.3.6, >but now gives random errors on 3.3.13. Might just be >coincidence but wanted to get feedback from the >community. > >Under random circumstances, my Delphi function >(compare function defined in sqlite3_create_collation) >doesn't seem to receive the right parameters. After >debugging, the error... > >"Access violation at address 00405190 in module >'app.exe'. Read of address 016D000" > >...happens in line #15, like if the pointer to the >second variable (B) is invalid: > > 1 FUNCTION fnComp(user:pointer; > 2 lenA:integer; A:pChar; > 3 lenB:integer; B:pChar > 4 ):integer; cdecl; > 5 > 6 VAR S1,S2 :string; > 7 i:integer; > 8 BEGIN > 9 S1 := ''; >10 S2 := ''; >11 IF lenA>0 THEN >12S1 := copy(A,1,lenA); >13 >14 IF lenB>0 THEN >15S2 := copy(B,1,lenB); // error happens here! >16 >17 i := ansiCompareText(S1,S2); >18 >19 IF (i=0) THEN >20IF (lena21 i := -1 >22ELSE >23 i := 1 >24 ELSE ; >25 >26 Result := i; >27 END; >... >The collation is created using: > >sqlResult:=sqlite3_create_collation(db3, > 'myCollate', > SQLITE_UTF8, > self, > fnComp); > >- - - - >No error happens when using the BINARY and NOCASE >collation. > >Does anybody have similar experiences or have a clue >of what might be going on? The error happens after >processing several thousand records, but sometimes >happens on an INSERT, other times during a SELECT, and >never in exactly the same place (even with the same >set of data/database). > >jp > > > > > > >It's here! Your new message! >Get new email alerts with the free Yahoo! Toolbar. >http://tools.search.yahoo.com/toolbar/features/mail/ > >- >To unsubscribe, send email to [EMAIL PROTECTED] >- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] custom collation problem with delphi
Hello jp, >Thanks Ralf, that seems to be more stable - the process is not crashing >anymore (ran it twice, no errors). Glad to read this! >Pardon my ignorance - I am still using cdecl, how can I use 'register'? >Doesn't sqlite expects cdecl? There is no ignorance involved: The sqlite3.dll of course expects cdecl. It is only DISQLite3 which does not require cdecl but allows to use Delphi's default register calling convention. DISQLite3 is a special build of sqlite3 specially targeted at Delphi. Since it does not use the sqlite3.dll (it compiles straight into applications), it was possible to apply register calling conventions throughout (internally as well as for the external API). Register was choosen because according to the Delphi help "The default register convention is the most efficient, since it usually avoids creation of a stack frame." My tests have confirmed this to be true for DISQLite3. It is available from http://www.yunqa.de/delphi/sqlite3/. Regards, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] FTS: Custom Tokenizer / Stop Words
I wonder if there is some effort already under way to allow custom tokenizers by SQLite's full text search? I know that custom tokenizers are already on the developer's todo-list, but I would be interested to know if some progess has already been made. Custom tokenizers would be able solve a couple of the current limitations to FTS: * Caseless searching for full Unicode range or characters (currently limited to ASCII only). * Stop Words - the tokenizer would ignore them and the FTS engine could remain unchanged. * Improve reading of meta text formats (OpenOffice, Word, HTML). I can imagine that SQLite would quickly see a bunch of user contributed tokenizers for these formats. Not directly related to tokenizers: I am very interested to know if it would be possible to use an FTS indexing module to store the inverted index only, but not the document's text. This would safe disk space if the text to index is stored on disk rather than inside the database. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words
>But what about:
>
>I am very interested to know if it would be possible to use an FTS indexing
>module to store the inverted index only, but
>not the document's text. This would safe disk space if the text to index is
>stored on disk rather than inside the database.
This is possible with just minor modifications to fts2.c (below). I commented
out the instructions responsible for inserting and updating the text body into
the %_content table. As a side effect, the offsets() and snippet() functions
stopped working, as they seem to rely on the presence of the full document text
in the current implementation. Neverthelses, I ran FTS2 over a collection of
source code files, and the results are astonishing:
With the original fts2.c, the database figures are as follows:
Number of documents:10739 Files
Total size of document text stored: 234 MB
Total size of database: ===> 295 MB <===
Size of index within database: 61 MB
Index / Text ratio:26 Percent
With the modified fts2.c (no text stored), the database size was obviously much
smaller:
Number of documents:10739 Files
Total size of document text stored: 234 MB
Total size of database: ===> 61 MB <===
Index / Text ratio:26 Percent
I addition to the database size savings, I can think of a number of other
benefits in separating text and reverted index storage:
1. Indexing docuements stored in another database would not need to duplicate
storage. A small "FTS database" could be attached to the "Data database" if
necessary, so the "data" database stays smaller without the index. Deleting
the "FTS database" would leave the the data untouched.
2. Point 1 from above would allow to distribute CDs without FTS and let the
user create a small FTS index on local storage to speed up searching. This way
more data can be shipped on single CD volumes.
3. Indexing compressed text would become possible. The current implementation
does not allow text compression because the FTS tables always store
uncompressed.
4. Ease maintainance and consistency of data as long as FTS is experimental. If
data and FTS are separated, only the FTS index must be rebuild if FTS changes,
while the current implementation potentially requires to upgrade entire tables
to yet unknown formats.
5. FTS could be removed from a database without touching the data: Only the FTS
tables would have to be deleted.
Concluding: Given the great database size savings possible by separating full
text index from data storage, I wish that developers would consider adding such
an option to the SQLite FTS interface.
Finally, here are the changes I applied to fts2.c as proof of concept:
/* insert into %_content (rowid, ...) values ([rowid], [pValues]) */
static int content_insert(fulltext_vtab *v, sqlite3_value *rowid,
sqlite3_value **pValues){
sqlite3_stmt *s;
int i;
int rc = sql_get_statement(v, CONTENT_INSERT_STMT, &s);
if( rc!=SQLITE_OK ) return rc;
rc = sqlite3_bind_value(s, 1, rowid);
if( rc!=SQLITE_OK ) return rc;
/* for(i=0; inColumn; ++i){
rc = sqlite3_bind_value(s, 2+i, pValues[i]);
if( rc!=SQLITE_OK ) return rc;
} */
return sql_single_step_statement(v, CONTENT_INSERT_STMT, &s);
}
/* update %_content set col0 = pValues[0], col1 = pValues[1], ...
* where rowid = [iRowid] */
static int content_update(fulltext_vtab *v, sqlite3_value **pValues,
sqlite_int64 iRowid){
sqlite3_stmt *s;
int i;
int rc = sql_get_statement(v, CONTENT_UPDATE_STMT, &s);
if( rc!=SQLITE_OK ) return rc;
/* for(i=0; inColumn; ++i){
rc = sqlite3_bind_value(s, 1+i, pValues[i]);
if( rc!=SQLITE_OK ) return rc;
} */
rc = sqlite3_bind_int64(s, 1+v->nColumn, iRowid);
if( rc!=SQLITE_OK ) return rc;
return sql_single_step_statement(v, CONTENT_UPDATE_STMT, &s);
}
Ralf
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words
Ion Silvestru wrote: >Just a question: did you eliminated stop-words in your tests? No, I did not eliminate any stop-words. The two test runs were equal except for the small changes in FTS 2. My stop words question was not intended for source code but for human language texts. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words
Hello Scott, I was hoping that you would read my message, many thanks for your reply! >UPDATE and DELETE need to have the previous document text, because the >docids are embedded in the index, and there is no docid->term index >(or, put another way, the previous document text _is_ the docid->term >index). This is very understandable given the present design. >Keeping track of that information would probably double the >size of the index. With your estimate, the SQLite full text index (without document storage) would still take up only 50% of the documents' size. In my opinion, this is still a very good ratio, even if some specialized full text search engines apparently get away with less than 30%. I think you have done an enourmous job on FTS2! I am optimistic that the proper implementation will use even less than 50%: My modifications are completely rudimentary and not at all optimized - the column to store the document text still exists. The only difference is that it is not used - it stores a null value which could be saved. In fact, the entire FTS table (the one without the suffixes) would not be needed and cut down storage space. >A thing I've considered doing is to keep deletions >as a special index to the side, Would this open the door to "insert only, but no-modify and no-delete" indexes? I am sure users would like pay this cost for the benefit of even smaller FTS indexes! >which would allow older data to be >deleted during segment merges. Unfortunately, I suspect that this >would slow things down by introducing another bit of data which needs >to be considered during merges. I found that _not_ adding the original text turned out to be a great time saver. This makes sense if we know that the original text is about 4 times the size of the index. Storing lots of text by itself is already quite time consuming even without creating a FTS index. So I do not expect really bad slow downs by adding a docid->term index. >Of course, there's no way the current system could generate snippets >without the original text, because doclists don't record the set of >adjacent terms. That information could be recorded, but it's doubtful >that doing so would be an improvement on simply storing the original >text in the first place. The current system _does_ have everything >needed to generate the offsets to hits even without the original text, >so the client application could generate snippets, though the code is >not currently in place to expose this information. Snippets are of course nice to have out of the box as it is right now. But even without storing the original text, snippets could be created by 1. supplying the text through other means (additional parameter or callback function), so that not FTS but the application would read it from a disk file or decompress it from a database field. 2. constructing token-only snippets from the document tokens and offsets. This would of course exclude all non-word characters, but would still return legible information. >Being able to have an index without storing the original data was a >weak goal when fts1 was being developed, but every time we visitted >it, we found that the negatives of that approach were substantial >enough to discourage us for a time. [The "we" in that sentence means >"me and the various people I run wacky ideas past."] I'm keeping an >eye out for interesting implementation strategies and the time to >explore them, though. Maybe my arguments could influence the opinion of "we"? I would love to see FTS without text storage, especially since I just lost a project to another FTS product because duplicating data was unfortunately "out of disk space". All the best and keep up your good work, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words
ementation, so please excuse if I am arguing from a user's perspective. For users, I can see the following benefits in separating FTS index and original text: * Space savings when indexing external documents not stored in the database. * Possibility to add FTS to text stored in compressed format in the database. * Possibility to mix FTS text rows with numeric or blob rows in a single table. The current implementation does not allow INTEGERs or BLOBs in FTS virtual tables. * FTS indexs could be easily deleted without touching the real data. * FTS indexes could be maintained outside the main data database, for example in an attached databases. * If there was a FTS API, it could be used to add full text search to other VIRTUAL TABLEs, like to provide FTS to *.dbf databases, etc. That's my list for the moment. But many ideas around a new technology emerge only after it is available (take the laser, or even SQLite, for an example). So if you can see at least some benetif in index-data-separation, I would be glad if you could persue this idea further. I might not be of great help in this right now, but would be willing to learn ;-) Regards, Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Japanese-Korean characters
Hello Pavan, >Can we store/retrieve Japanese/korean characters in sqlite db ? Yes, you can well do so, as others have already pointed out. If you are also looking for a Unicode GUI SQLite database manager to display and edit Japanese / Korean character databases, you might want to have a look at SQLiteSpy, freeware from http://www.yunqa.de/delphi/sqlitespy/ . SQLiteSpy is designed to support any language when run on a Windows NT 4 and later operating system (Win2K, WinXp, Vista, etc.). I have received positive reports that it works well with German, French, Eastern European, Greek and Cyrillic characters. Given that your system fonts supports Japanese and Koean characters, they should work just as well. If you are experiencing problems, please contact me via e-mail and I will see what I can do. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
