Re: [sqlite] Sqlite,'C' & FTS - reg
On 6/25/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: sqlite3_enable_load_extension(db, 1); sqlite3_load_extension(db, "./libfts1.so", 0, 0); thanks, it works fine -- Regards, Zoobave A http://zoobave.blogspot.com/
RE: [sqlite] Trouble compiling with MSVC++ 6.0
> > The problem is actually linked to the optimizations in the release code. > If you compile it without optimizations (for example, in Debug mode), it > compiles successfully. Testing ... Without -O2 it compiles ok, -O1, -O2 fails, -O3 is ok. > I made some tests, and you can compile all the rest of the files (if you > don't use the amalgamation file) I am using the TEA tarball, which is based on the amalgamation. >with release flags; and you can even > compile btree.c if you do the following: > - Select btree.c > - Right-click the file in the FileView and select 'Settings' from the menu > - Select the 'C/C++' tab on the right > - Select 'Customize' in the Optimizations box > - Mark all the check boxes in the list, EXCEPT for: > Assume Aliasing Across Function Calls* > Favor Small Code > - Select the Online _inline option in the inline functions box > - Click OK > > Now it should compile (my VC6 with SP6 compiler reads Version 12.00.8804). My cl (with SP6 installed) gives me the same version, i.e. 12.00.8804. so we are on the same page there. > As this is just an optimization that fails, the code should work. > > * The actual problem is with this option. It can probably be turned of > specifically for this file using a VC6 macro Ok... Will see if I can find pragmas to set the opt level for parts of a single file ... And find out what O1/2/3 mean, too. Thanks for that information. -- Andreas Kupries <[EMAIL PROTECTED]> Developer @ http://www.ActiveState.com Tel: +1 778-786-1122 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: Re[2]: [sqlite] Trouble compiling with MSVC++ 6.0
I dod not believe that I am using pre-compiled headers at all. The compile command is, nicely broken across lines cl -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" \ -DPACKAGE_VERSION=\"3.4.0\" -DPACKAGE_STRING=\"sqlite\ 3.4.0\" \ -DPACKAGE_BUGREPORT=\"\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 \ -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 \ -DHAVE_MEMORY_H=1 -DBUILD_sqlite=1 -DTCL_THREADS=1 \ -DTHREADSAFE=1 -DUSE_TCL_STUBS=1 -Dfdatasync=fsync \ -DSQLITE_ENABLE_FTS2=1 \ -I"../generic" \ -I"/e/Home/dbn/lba/night/builds/win32-ix86/tcl/generic" \ -nologo -O2 -W2 -MD \ -c `cygpath -w ../generic/tclsqlite3.c` -o tclsqlite3.obj Per google I should have a -XY there to activate PCH -- Andreas Kupries <[EMAIL PROTECTED]> Developer @ http://www.ActiveState.com Tel: +1 778-786-1122 > -Original Message- > From: Teg [mailto:[EMAIL PROTECTED] > Sent: Monday, June 25, 2007 3:38 PM > To: Andreas Kupries > Subject: Re[2]: [sqlite] Trouble compiling with MSVC++ 6.0 > > > Hello Andreas, > > Try turning off pre-compiled headers for that module. > > C > > Monday, June 25, 2007, 6:29:40 PM, you wrote: > > >> > Ticket #2457 reports difficulty building SQLite version 3.4.0 > >> > using MSVC++ 6.0. This appears to be a bug in MSVC++. See the > >> > comments on the ticket for details: > >> > > >> >http://www.sqlite.org/cvstrac/tktview?tn=2457 > >> > > >> > I do not own MSVC++ (and have no intention of acquiring a copy) > >> > so I am unable to reproduce. On the other hand, Version 3.4.0 > >> > has been out for a week and there have been no other reports > >> > of problems, so I am somewhat suspicious that this problem is > >> > specific to the specific installation. > >> > > >> > Can anybody shed any light on ticket #2457? Can anybody else > >> > reproduce the problem or suggest a work-around? > >> > >> Error in MSVC 6.0 -- the user who wrote the ticket hasn't installed the > >> latest service pack of VC6. > >> > >> http://support.microsoft.com/kb/890892 > > AK> The article sounds like a very likely match (large macros). > > AK> Just retrieved and installed VC6 SP6, rebooted the machine > ... The compiler > AK> now reports a different build number, higher than before, so > I guess really > AK> did not have that ServicePack before ... > > AK> Compiling the original sources (not the ones I messed with) > ... I however > AK> still run into the same internal compiler error, in the same > line of code. > > AK> :( > > AK> -- > AK> Andreas Kupries <[EMAIL PROTECTED]> > AK> Developer @ http://www.ActiveState.com > AK> Tel: +1 778-786-1122 > > > > AK> > -- > --- > AK> To unsubscribe, send email to [EMAIL PROTECTED] > AK> > -- > --- > > > > > -- > Best regards, > Tegmailto:[EMAIL PROTECTED] > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Trouble compiling with MSVC++ 6.0
Andreas Kupries wrote: Ticket #2457 reports difficulty building SQLite version 3.4.0 using MSVC++ 6.0. This appears to be a bug in MSVC++. See the comments on the ticket for details: http://www.sqlite.org/cvstrac/tktview?tn=2457 I do not own MSVC++ (and have no intention of acquiring a copy) so I am unable to reproduce. On the other hand, Version 3.4.0 has been out for a week and there have been no other reports of problems, so I am somewhat suspicious that this problem is specific to the specific installation. Can anybody shed any light on ticket #2457? Can anybody else reproduce the problem or suggest a work-around? Error in MSVC 6.0 -- the user who wrote the ticket hasn't installed the latest service pack of VC6. http://support.microsoft.com/kb/890892 The article sounds like a very likely match (large macros). Just retrieved and installed VC6 SP6, rebooted the machine ... The compiler now reports a different build number, higher than before, so I guess really did not have that ServicePack before ... Compiling the original sources (not the ones I messed with) ... I however still run into the same internal compiler error, in the same line of code. The problem is actually linked to the optimizations in the release code. If you compile it without optimizations (for example, in Debug mode), it compiles successfully. I made some tests, and you can compile all the rest of the files (if you don't use the amalgamation file) with release flags; and you can even compile btree.c if you do the following: - Select btree.c - Right-click the file in the FileView and select 'Settings' from the menu - Select the 'C/C++' tab on the right - Select 'Customize' in the Optimizations box - Mark all the check boxes in the list, EXCEPT for: Assume Aliasing Across Function Calls* Favor Small Code - Select the Online _inline option in the inline functions box - Click OK Now it should compile (my VC6 with SP6 compiler reads Version 12.00.8804). As this is just an optimization that fails, the code should work. * The actual problem is with this option. It can probably be turned of specifically for this file using a VC6 macro - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Full Text Search
On 6/10/07, Mark Gilbert <[EMAIL PROTECTED]> wrote:
I have just started learning about Full Text search in SQlite, and I
have some questions
Sorry for the delayed response, I just noticed this email waiting for
an answer, and nobody has ...
1) With the Amalgamated Sqlite, I guess to enable FTS I just #define
SQLITE_ENABLE_FTS2 at the start of sqlite3.c
Is there anything else I have to do to switch it on ?
I don't think FTS2 is in the amalgamation. You can either build it
separately and manually hook it in (after sqlite3_open(), call
sqlite3Fts2Init(), like is done in main.c), or you could perhaps
define SQLITE_ENABLE_FTS2, and compile sqlite3.c plus the .c files in
ext/fts2.
2) If I want to do a full text (google style) search across 6 columns
in my database, what is the procedure?Currently I conjor up a
long SQL query with lots of ands and ors. What is to be gained
using FTS ?
CREATE TABLE x USING fts2(a, b, c, d, e, f);
INSERT INTO x (a, b, c, d, e, f) VALUES ('hello', 'world', 'this',
'is', 'a', 'test');
SELECT rowid FROM x WHERE a MATCH 'this'; -- Look for 'this' in column a.
SELECT rowid FROM x WHERE x MATCH 'this'; -- Look for 'this' in any column.
SELECT rowid FROM x WHERE x MATCH 'a:this'; -- Again, only column a.
SELECT rowid FROM x WHERE x MATCH 'this b:that'; -- require 'that'
in column b.
Note the use of a table-named column in the second and later MATCH
calls. This sets the default column specifier for words in the query
to "all".
3) It seems that FTS is basically a special table. Do I have to fill
this table in parallel with my regular columns ? (so its in sync) or
do I recreate the FTS table on every search ? Or do I just fill the
FTS table on its own, and stop using my regular tables altogether ?
Depends entirely on what you're trying to accomplish. FTS is a
separate table which stores TEXT columns, and provides a word-level
index on them. If your current data is just a table of TEXT columns,
you can probably just store it directly in FTS and be done with it.
If it's a mixture of TEXT and non-TEXT, or has other constraints you
might want to have a pair of tables joined on rowid. Sometimes you
want both. Say you want a table where a msgid should be unique, and
other columns should be searchable. You might have something like:
CREATE TABLE a (msgid TEXT PRIMARY KEY);
CREATE TABLE a_fts (subject, body);
You select things using a join on the implicit rowid column (an SQLite
concept which fts abides by):
SELECT msgid, subject, body FROM a JOIN a_fts USING(rowid) WHERE
subject MATCH ?;
To update, make sure you use a transaction to keep things consistent:
BEGIN;
INSERT INTO a (msgid) VALUES (?);
INSERT INTO a_fts (rowid, subject, body) VALUES (last_insert_rowid(), ?, ?);
COMMIT;
BEGIN;
DELETE FROM a_fts WHERE rowid IN (SELECT rowid FROM a WHERE msgid = ?);
DELETE FROM a WHERE msgid = ?;
END;
BEGIN;
UPDATE a_fts SET subject = ? WHERE rowid IN (SELECT rowid FROM a
WHERE msgid = ?);
UPDATE a SET msgid = ? WHERE msgid = ?;
END;
If you wanted msgid to also be searchable, just duplicate it in a_fts.
-scott
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re[2]: [sqlite] Trouble compiling with MSVC++ 6.0
Hello Andreas, Try turning off pre-compiled headers for that module. C Monday, June 25, 2007, 6:29:40 PM, you wrote: >> > Ticket #2457 reports difficulty building SQLite version 3.4.0 >> > using MSVC++ 6.0. This appears to be a bug in MSVC++. See the >> > comments on the ticket for details: >> > >> >http://www.sqlite.org/cvstrac/tktview?tn=2457 >> > >> > I do not own MSVC++ (and have no intention of acquiring a copy) >> > so I am unable to reproduce. On the other hand, Version 3.4.0 >> > has been out for a week and there have been no other reports >> > of problems, so I am somewhat suspicious that this problem is >> > specific to the specific installation. >> > >> > Can anybody shed any light on ticket #2457? Can anybody else >> > reproduce the problem or suggest a work-around? >> >> Error in MSVC 6.0 -- the user who wrote the ticket hasn't installed the >> latest service pack of VC6. >> >> http://support.microsoft.com/kb/890892 AK> The article sounds like a very likely match (large macros). AK> Just retrieved and installed VC6 SP6, rebooted the machine ... The compiler AK> now reports a different build number, higher than before, so I guess really AK> did not have that ServicePack before ... AK> Compiling the original sources (not the ones I messed with) ... I however AK> still run into the same internal compiler error, in the same line of code. AK> :( AK> -- AK> Andreas Kupries <[EMAIL PROTECTED]> AK> Developer @ http://www.ActiveState.com AK> Tel: +1 778-786-1122 AK> - AK> To unsubscribe, send email to [EMAIL PROTECTED] AK> - -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Trouble compiling with MSVC++ 6.0
> I know VC++ 8.0 (2005) works like a charm! I do not believe that I will have the option to upgrade. -- Andreas Kupries <[EMAIL PROTECTED]> Developer @ http://www.ActiveState.com Tel: +1 778-786-1122 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Trouble compiling with MSVC++ 6.0
> > Ticket #2457 reports difficulty building SQLite version 3.4.0 > > using MSVC++ 6.0. This appears to be a bug in MSVC++. See the > > comments on the ticket for details: > > > >http://www.sqlite.org/cvstrac/tktview?tn=2457 > > > > I do not own MSVC++ (and have no intention of acquiring a copy) > > so I am unable to reproduce. On the other hand, Version 3.4.0 > > has been out for a week and there have been no other reports > > of problems, so I am somewhat suspicious that this problem is > > specific to the specific installation. > > > > Can anybody shed any light on ticket #2457? Can anybody else > > reproduce the problem or suggest a work-around? > > Error in MSVC 6.0 -- the user who wrote the ticket hasn't installed the > latest service pack of VC6. > > http://support.microsoft.com/kb/890892 The article sounds like a very likely match (large macros). Just retrieved and installed VC6 SP6, rebooted the machine ... The compiler now reports a different build number, higher than before, so I guess really did not have that ServicePack before ... Compiling the original sources (not the ones I messed with) ... I however still run into the same internal compiler error, in the same line of code. :( -- Andreas Kupries <[EMAIL PROTECTED]> Developer @ http://www.ActiveState.com Tel: +1 778-786-1122 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Introducing... ManagedSQLite
Sam:
The main advantage of mine is that it is lightweight and easy to use
without using ADO.NET.
Daniel A. White
{ Kent State University: Computer Science major }
{ JMC TechHelp: Taylor Hall, server techie }
{ E-mail: [EMAIL PROTECTED] }
{ Colossians 3:17 }
-Original Message-
From: Samuel R. Neff [mailto:[EMAIL PROTECTED]
Sent: Monday, June 25, 2007 10:51 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Introducing... ManagedSQLite
Daniel,
We already have an excellent ADO.NET compliant provider for SQLite that
Robert mentioned. What advantages does your wrapper provide of the
existing one? SQLite.NET already provides full ADO.NET support
including custom functions and collation sequences written in any .NET
languages and built-in encryption.
Thanks,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: WHITE, DANIEL [mailto:[EMAIL PROTECTED]
Sent: Saturday, June 23, 2007 10:29 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Introducing... ManagedSQLite
Howdy all!
I am just writing tonight to let you know that a project of mine has
opened up to being open source -- ManagedSQLite. It is a light wrapper
around SQLite 3.4.0 that was originally written by Rob Groves. I have
added support for Unicode to his wrapper, then added my Managed (.NET)
port to the mix. One DLL file unlike others out there. Supports FTS1
and FTS2 out of the box!!!
I am looking for help with this little project. I think we could
eventually make an entire ADO.NET wrapper with this thing. Thanks.
http://code.google.com/p/managed-sqlite/
Thanks for reading.
Daniel A. White
{ Kent State University: Computer Science major } { JMC TechHelp: Taylor
Hall, server techie } { E-mail: [EMAIL PROTECTED] } { Colossians 3:17 }
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Trouble compiling with MSVC++ 6.0
I know VC++ 8.0 (2005) works like a charm!
Daniel A. White
{ Kent State University: Computer Science major }
{ JMC TechHelp: Taylor Hall, server techie }
{ E-mail: [EMAIL PROTECTED] }
{ Colossians 3:17 }
-Original Message-
From: Robert Simpson [mailto:[EMAIL PROTECTED]
Sent: Monday, June 25, 2007 5:57 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Trouble compiling with MSVC++ 6.0
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, June 25, 2007 2:39 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Trouble compiling with MSVC++ 6.0
>
> Ticket #2457 reports difficulty building SQLite version 3.4.0 using
> MSVC++ 6.0. This appears to be a bug in MSVC++. See the comments on
> the ticket for details:
>
>http://www.sqlite.org/cvstrac/tktview?tn=2457
>
> I do not own MSVC++ (and have no intention of acquiring a copy) so I
> am unable to reproduce. On the other hand, Version 3.4.0 has been out
> for a week and there have been no other reports of problems, so I am
> somewhat suspicious that this problem is specific to the specific
> installation.
>
> Can anybody shed any light on ticket #2457? Can anybody else
> reproduce the problem or suggest a work-around?
Error in MSVC 6.0 -- the user who wrote the ticket hasn't installed the
latest service pack of VC6.
http://support.microsoft.com/kb/890892
Robert
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] pragma page_count
How easy would it be for me to implement a "pragma page_count;" statement which returns the CURRENT page count of the database, and is much more cross-platform than my attempt to just "check the file size". --andy
RE: [sqlite] Trouble compiling with MSVC++ 6.0
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, June 25, 2007 2:39 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Trouble compiling with MSVC++ 6.0 > > Ticket #2457 reports difficulty building SQLite version 3.4.0 > using MSVC++ 6.0. This appears to be a bug in MSVC++. See the > comments on the ticket for details: > >http://www.sqlite.org/cvstrac/tktview?tn=2457 > > I do not own MSVC++ (and have no intention of acquiring a copy) > so I am unable to reproduce. On the other hand, Version 3.4.0 > has been out for a week and there have been no other reports > of problems, so I am somewhat suspicious that this problem is > specific to the specific installation. > > Can anybody shed any light on ticket #2457? Can anybody else > reproduce the problem or suggest a work-around? Error in MSVC 6.0 -- the user who wrote the ticket hasn't installed the latest service pack of VC6. http://support.microsoft.com/kb/890892 Robert - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Trouble compiling with MSVC++ 6.0
Ticket #2457 reports difficulty building SQLite version 3.4.0 using MSVC++ 6.0. This appears to be a bug in MSVC++. See the comments on the ticket for details: http://www.sqlite.org/cvstrac/tktview?tn=2457 I do not own MSVC++ (and have no intention of acquiring a copy) so I am unable to reproduce. On the other hand, Version 3.4.0 has been out for a week and there have been no other reports of problems, so I am somewhat suspicious that this problem is specific to the specific installation. Can anybody shed any light on ticket #2457? Can anybody else reproduce the problem or suggest a work-around? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: progress handler code changed 3.4.0
On Mon, 25 Jun 2007 18:38:30 +, D. Richard Hipp wrote: >"John Belli" <[EMAIL PROTECTED]> wrote: >> If the progress handler returns non-zero, sqlite_exec is returning >> SQLITE_INTERRUPT instead of SQLITE_ABORT. Was this intentional? The >> comment says SQLITE_ABORT, but the return is being set to >> SQLITE_INTERRUPT. >> (vdbe.c line 553) >> > >This change is part of the fix for the corruption bug in ticket #2409. >We didn't think anybody would notice. Is it causing problems for you? Hah, no, not really. I just changed my code to look for either return value, so I can display the proper message. I was testing the return from a sqlite3_exec() for an error message display, and displaying a "canceled" message if it was SQLITE_ABORT. JAB -- John A. Belli Software Engineer Refrigerated Transport Electronics, Inc. http://www.rtelectronics.com - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to sort not binary?
Since you use C# (mentioned in a different message) you can easily write a custom collation function with SQLite.NET. http://sqlite.phxsoftware.com/ Look in the help for SQLiteFunction and particularly FunctionType.Collation. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Yves Goergen [mailto:[EMAIL PROTECTED] Sent: Saturday, May 12, 2007 11:24 AM To: sqlite-users@sqlite.org Subject: [sqlite] How to sort not binary? Hi, I'm trying to get my table sorted the way how for example Windows Explorer or other file managers are sorting names. Most of all, accented characters should not be listed at the end of the list but near their non-accented character instead. I can only see the built-in collations BINARY and NOCASE. Is there something else? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Loose auto_vacuum.
--- Scott Hess <[EMAIL PROTECTED]> wrote: > Recently, various incremental vacuum features have been added, so you can do: > > -- Turn on incremental vacuum. > PRAGMA auto_vacuum = incremental; > > -- Release 100 pages from the freelist, or all if there are fewer. > PRAGMA incremental_vacuum(100); Wouldn't it be more useful if specify the number of pages that incremental_vacuum should _keep_ rather than release? This way you would not care how many free pages there are. > > -- Return the number of items on the freelist. > PRAGMA freelist_count; > > Based on this, I find myself wondering if there could be something like: > > -- Vacuum up 10% of the freelist when it exceeds 20% of the total space. > PRAGMA auto_vacuum = incremental(20, 10); > > The basic goal would be to leave some slack in the system so that free > pages can be allocated nearby to related pages, but kick in with some > cleanup when the amount of unused space exceeds a threshold. A > secondary goal is to not get too tied up with freelist-clearing - in > the above, if half the pages were releases, then it would take four or > five transactions to get the freelist back under control. I like this idea. Maybe some sort of hint to have sqlite prefer to retain free pages near certain tables or indexes would be useful. > > One could do this manually if there were a PRAGMA returning the total > number of pages. Just wondering if there are plans to make it > automagic. > > Thanks, > scott Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to sort not binary?
On 25.06.2007 00:57 CE(S)T, [EMAIL PROTECTED] wrote: > Yves Goergen <[EMAIL PROTECTED]> wrote: >> Oh, well, I just realised that this will only sort numbers "naturally" >> at the beginning of strings, but not in the middle or at the end. It >> will be a bit more complex to do that. Maybe I find a sort/compare >> algorithm for it. But at least I know now that it's easy to use. :) > Check out the "sortStrCmp()" function in older versions of > SQLite version 2 in the util.c source file. See, for example, > >http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/util.c=1.42 > > I think the sortStrCmp() function does what you are after. Okay, first thank you for pointing me on this. But even with the good documentation about the state machine implemented, this surely is highly optimised C "hack"* code that I probably won't understand so soon. Another problem is that I can't easily translate this into C# because there's no such thing as pointers to strings (character arrays). I'll keep on searching for a function for that. Maybe, given enough time, I'll come up with my own one... Some day... ;-) *) I mean, come on, using negative array indices on pointers... cb = b[-1]; ;-) -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unicode collation
--- [EMAIL PROTECTED] wrote: > OK. Here is a crazy idea for consideration: > > You know that you can create a custom collating function as a > DLL or shared library and load it at runtime, right? This has > been a capability since version 3.3.7. Suppose we define a > special table in the database file that is designed to hold > DLLs and/or shared libraries. Perhaps something like this: > > CREATE TABLE sqlite_shared_libs( >architecture TEXT, >library BLOB > ); > > You can put DLLs or shared libraries for any custom collating > functions (also any custom SQL functions or virtual table > implementations) into this table and SQLite will load them > automatically which is starts up. This allows you to create > any non-standard database extensions you want and make them > completely portable to any database editor or other tool that > wants to access the database. Christian Werner's SQLiteODBC driver does something similar to your proposal except it compiles C function extensions on the fly using TCC: http://www.mail-archive.com/sqlite-users@sqlite.org/msg21945.html http://sqlite.org/contrib/download/sqlite%2Btcc.tar.gz?get=13 The idea could be generalized to run any arbitrary binary and/or script to make a shared library. See also the sqlite_extensions proposal at the bottom of this page: http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions It takes into account extension library load/unload order as well as the name the names of the function entry points. Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite Project. A contribution to manking
I know that this post is not a query about SQLite DB, and I am sorry for that. First to all, congratulation to D. Richard Hipp and this wonderful community. And I know Mr. Hipp that SQLite is open source, public domain, but I think you deserve to make money with that quantity of money. I won't be and I think the community angry if you put Google Ads Sense in your page, for you to get some money for such great contribution to the world, I think SQLite is great. And Also, congratulation because SQLite will be spread around the world with Google Gears. I was surfing and I saw that sqlite website is very busy with a hight band width traffic (http://alexa.com/data/details/traffic_details?url=sqlite.org) and I was reading about the SQLite has an Server which is not the normal apache or other known webserver, is that right? I am interesting to know more about this server, and configurations about know more about the SQLite server configuration, what do you do Mr. Hipp for handle a great quantity of traffic (server configuration, your wserver code (if you can give it away) and a network connection)? If you can give that information will be great, coz I think is better the Practice (and your site is very very busy) than theory. -- Cesar D. Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [sqlite] progress handler code changed 3.4.0
"John Belli" <[EMAIL PROTECTED]> wrote: > If the progress handler returns non-zero, sqlite_exec is returning > SQLITE_INTERRUPT instead of SQLITE_ABORT. Was this intentional? The > comment says SQLITE_ABORT, but the return is being set to > SQLITE_INTERRUPT. > (vdbe.c line 553) > This change is part of the fix for the corruption bug in ticket #2409. We didn't think anybody would notice. Is it causing problems for you? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] progress handler code changed 3.4.0
If the progress handler returns non-zero, sqlite_exec is returning SQLITE_INTERRUPT instead of SQLITE_ABORT. Was this intentional? The comment says SQLITE_ABORT, but the return is being set to SQLITE_INTERRUPT. (vdbe.c line 553) JAB
[sqlite] Loose auto_vacuum.
Recently, various incremental vacuum features have been added, so you can do: -- Turn on incremental vacuum. PRAGMA auto_vacuum = incremental; -- Release 100 pages from the freelist, or all if there are fewer. PRAGMA incremental_vacuum(100); -- Return the number of items on the freelist. PRAGMA freelist_count; Based on this, I find myself wondering if there could be something like: -- Vacuum up 10% of the freelist when it exceeds 20% of the total space. PRAGMA auto_vacuum = incremental(20, 10); The basic goal would be to leave some slack in the system so that free pages can be allocated nearby to related pages, but kick in with some cleanup when the amount of unused space exceeds a threshold. A secondary goal is to not get too tied up with freelist-clearing - in the above, if half the pages were releases, then it would take four or five transactions to get the freelist back under control. One could do this manually if there were a PRAGMA returning the total number of pages. Just wondering if there are plans to make it automagic. Thanks, scott - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Unicode collation
> -Original Message- > From: Jiri Hajek [mailto:[EMAIL PROTECTED] > Sent: Monday, June 25, 2007 8:14 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Unicode collation > > > So the choices seem to be: > > > > (1) Databases that corrupt if you move across platforms. > > (2) A 10MB database engine > > (3) Leave things as they are > > > > I prefer to go with (3) > > Yes, based on the facts I agree that (3) is the best way to go - a > developer can decide whether to use Windows methods only, or be > cross-platform and use ICU or anything else. > > However, it isn't what I mean. I suggest to publish some kind of a > standard that database developers would use. I.e. SQLite web would > define, that if you want to make a Unicode database that can be > openned by other applications, you should define collation e.g. as > 'en_AU' for Australian English. This way, if I want let users of my > application to open DB in e.g. in some database editor, I can use this > standard and users will be able to open it in many applications > following the standard. > > So, what I propose aren't actually any changes in SQLite, but rather > publishing something that would guide all SQLite developers. I thought that was what was being responded to; publishing such a recommendation leads to problem (1), because the collation rules will not match in different environments. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure for versioned data
Not quite the same issue, but I've set up triggers to generate a journal record whenever a record is added/changed/deleted from a table. This mechanism (triggers) could easily be used to generate a 'version' record. *** Doug F. John Stanton wrote: > We perform some versioning by holding column material in XML and using > RCS to maintain reverse deltas and versions. > > Samuel R. Neff wrote: >> Not specific to SQLite, but we're working on an app that needs to keep >> versioned data (i.e., the current values plus all previous values). The >> versioning is integral to the app so it's more than just an audit >> trail or >> history. >> >> Can anyone share experiences with the database structure for this >> type of >> requirement or point me to helpful resources? >> >> Thanks, >> >> Sam >> >> >> >> --- >> We're Hiring! Seeking a passionate developer to join our team building >> products. Position is in the Washington D.C. metro area. If interested >> contact [EMAIL PROTECTED] >> >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS2 Module
Andre du Plessis wrote: I saw this post on FTS2, and looked on the site and saw the FTS2 binary, where can I find more information on this The wiki has a lot of info. Check out: http://www.sqlite.org/cvstrac/wiki?p=FtsOne http://www.sqlite.org/cvstrac/wiki?p=FtsTwo http://www.sqlite.org/cvstrac/wiki?p=FtsTwoNotes http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex HTH, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unicode collation
"Jiri Hajek" <[EMAIL PROTECTED]> wrote: > > So the choices seem to be: > > > > (1) Databases that corrupt if you move across platforms. > > (2) A 10MB database engine > > (3) Leave things as they are > > > > I prefer to go with (3) > > Yes, based on the facts I agree that (3) is the best way to go - a > developer can decide whether to use Windows methods only, or be > cross-platform and use ICU or anything else. > > However, it isn't what I mean. I suggest to publish some kind of a > standard that database developers would use. I.e. SQLite web would > define, that if you want to make a Unicode database that can be > openned by other applications, you should define collation e.g. as > 'en_AU' for Australian English. This way, if I want let users of my > application to open DB in e.g. in some database editor, I can use this > standard and users will be able to open it in many applications > following the standard. > > So, what I propose aren't actually any changes in SQLite, but rather > publishing something that would guide all SQLite developers. > OK. Here is a crazy idea for consideration: You know that you can create a custom collating function as a DLL or shared library and load it at runtime, right? This has been a capability since version 3.3.7. Suppose we define a special table in the database file that is designed to hold DLLs and/or shared libraries. Perhaps something like this: CREATE TABLE sqlite_shared_libs( architecture TEXT, library BLOB ); You can put DLLs or shared libraries for any custom collating functions (also any custom SQL functions or virtual table implementations) into this table and SQLite will load them automatically which is starts up. This allows you to create any non-standard database extensions you want and make them completely portable to any database editor or other tool that wants to access the database. A moments reflection convinces me that this is not a good idea as stated. But perhaps it can be the seed for a idea that will actually work. Is there some way of adding customizations to the database file itself, or perhaps to a separate file in a standard place the SQLite always knows to look, so that custom enhancements and extensions to SQLite can be accomodated in standard tools? Worth thinking about, perhaps -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] In Mem Query Performance
Don't use sqlite_get_table. Use sqlite3_prepare(_v2), sqlite3_step and sqlite3_finalize/sqlite3_reset. Mike -Ursprüngliche Nachricht- Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Gesendet: Montag, 25. Juni 2007 13:48 An: sqlite-users@sqlite.org Betreff: [sqlite] In Mem Query Performance Hi, We are using Sqlite in "in Memory Mode" and we have around 200 tables. Each table has 10 columns of type text. Each table has around 1 records each column has around 128bytes data. Select performance is around 2000records/sec. Pls suggest if there is a way to improve further. Table structure,Query style is as below, create table test1 ...200 ( key0 text, key1 text, key2 text, key3 text, key4 text, nonKey0 text, nonKey1 text, nonKey2 text, nonKey3 text, nonKey4 text, primary key(key0,key1,key2,key3,key4,key5) ); Query Used.. SELECT * FROM TABLE136 WHERE key0='kk kk490' AND key1='kk kk491' AND key2='kk kk492' AND key3='kk kk493' AND key4='kk kk494' API used sqlite_get_table... regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unicode collation
So the choices seem to be: (1) Databases that corrupt if you move across platforms. (2) A 10MB database engine (3) Leave things as they are I prefer to go with (3) Yes, based on the facts I agree that (3) is the best way to go - a developer can decide whether to use Windows methods only, or be cross-platform and use ICU or anything else. However, it isn't what I mean. I suggest to publish some kind of a standard that database developers would use. I.e. SQLite web would define, that if you want to make a Unicode database that can be openned by other applications, you should define collation e.g. as 'en_AU' for Australian English. This way, if I want let users of my application to open DB in e.g. in some database editor, I can use this standard and users will be able to open it in many applications following the standard. So, what I propose aren't actually any changes in SQLite, but rather publishing something that would guide all SQLite developers. Jiri - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Introducing... ManagedSQLite
Daniel,
We already have an excellent ADO.NET compliant provider for SQLite that
Robert mentioned. What advantages does your wrapper provide of the existing
one? SQLite.NET already provides full ADO.NET support including custom
functions and collation sequences written in any .NET languages and built-in
encryption.
Thanks,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: WHITE, DANIEL [mailto:[EMAIL PROTECTED]
Sent: Saturday, June 23, 2007 10:29 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Introducing... ManagedSQLite
Howdy all!
I am just writing tonight to let you know that a project of mine has
opened up to being open source -- ManagedSQLite. It is a light wrapper
around SQLite 3.4.0 that was originally written by Rob Groves. I have
added support for Unicode to his wrapper, then added my Managed (.NET)
port to the mix. One DLL file unlike others out there. Supports FTS1
and FTS2 out of the box!!!
I am looking for help with this little project. I think we could
eventually make an entire ADO.NET wrapper with this thing. Thanks.
http://code.google.com/p/managed-sqlite/
Thanks for reading.
Daniel A. White
{ Kent State University: Computer Science major }
{ JMC TechHelp: Taylor Hall, server techie }
{ E-mail: [EMAIL PROTECTED] }
{ Colossians 3:17 }
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Sqlite,'C' & FTS - reg
Zoobave <[EMAIL PROTECTED]> wrote:
> Hello group,
>
> In sqlite, how to load fts1 module in 'C'? I couldn't execute the
> *"select load_extension('libfts1.so');" * query using "sqlite3_exec"
> function. How can i execute the above query to load fts1 module, in order
> to use virtual tables?
>
sqlite3_enable_load_extension(db, 1);
sqlite3_load_extension(db, "./libfts1.so", 0, 0);
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Sqlite,'C' & FTS - reg
Hello group,
In sqlite, how to load fts1 module in 'C'? I couldn't execute the
*"select load_extension('libfts1.so');" * query using "sqlite3_exec"
function. How can i execute the above query to load fts1 module, in order
to use virtual tables?
by
zoobave
http://zoobave.blogspot.com/
[sqlite] In Mem Query Performance
Hi, We are using Sqlite in "in Memory Mode" and we have around 200 tables. Each table has 10 columns of type text. Each table has around 1 records each column has around 128bytes data. Select performance is around 2000records/sec. Pls suggest if there is a way to improve further. Table structure,Query style is as below, create table test1 ...200 ( key0 text, key1 text, key2 text, key3 text, key4 text, nonKey0 text, nonKey1 text, nonKey2 text, nonKey3 text, nonKey4 text, primary key(key0,key1,key2,key3,key4,key5) ); Query Used.. SELECT * FROM TABLE136 WHERE key0='490' AND key1='491' AND key2='492' AND key3='493' AND key4='494' API used sqlite_get_table... regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Fwd: In Mem Query Performance
Hi, We are using Sqlite in "in Memory Mode" and we have around 200 tables. Each table has 10 columns of type text. Each table has around 1 records each column has around 128bytes data. Select performance is around 2000records/sec. Pls suggest if there is a way to improve further. Table structure,Query style is as below, create table test1 ...200 ( key0 text, key1 text, key2 text, key3 text, key4 text, nonKey0 text, nonKey1 text, nonKey2 text, nonKey3 text, nonKey4 text, primary key(key0,key1,key2,key3,key4,key5) ); Query Used.. SELECT * FROM TABLE136 WHERE key0='490' AND key1='491' AND key2='492' AND key3='493' AND key4='494' API used sqlite_get_table... regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * --- Begin Message --- Hi, When the DB is opened in "in Memory mode",performance of query does not improve. For table which has 10 columns of type Text and each column having 128bytes data and having a total of 1 records. Performance is around 2400 records/sec. Any ways to improve it. Thanks in advance. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * --- End Message --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unicode collation
"Jiri Hajek" <[EMAIL PROTECTED]> wrote: > Ok, drh, could you share your thoughts about this. Could something > like this, i.e. some kind of recommendation be created, so that all > applications are consistent in Unicode support? > Dan's observation is correct. It seems to me that the collating sequences generated by version editions of Windows are unlikely to be identical. Almost certainly the collating sequence for Windows will be different from ICU. So if we were to make any of the collating sequences standard, then suddenly SQLite databases are no longer cross-platform. We could build in our own collating functions. But the collation tables are huge. The 250KB SQLite library would mushroom into the 10MB range. So the choices seem to be: (1) Databases that corrupt if you move across platforms. (2) A 10MB database engine (3) Leave things as they are I prefer to go with (3) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unicode collation
Ok, drh, could you share your thoughts about this. Could something like this, i.e. some kind of recommendation be created, so that all applications are consistent in Unicode support? Thanks, Jiri - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Index size
"Andre du Plessis" <[EMAIL PROTECTED]> wrote: > I have a question on index size, I have a table that by doing some > testing the DB have grown to 250MB, might not seem too large, as the > table have 4million records in it. But for this DB this table would grow > a lot larger than that. So I did some tests with a simple table 3 fields > > FIELD1 is a integer with autogenerated primary key, the other two fields > are integers, inserting 1 million records the DB ended up being around > 12mb which seems to be correct, by adding indexes to the other 2 fields > the db has grown to about 27mb, so my question is this. > Each index entry contains both the integer being indexed and the primary key of the original table. That's 2/3rds of the information in the original table. So you would expect the index to be about 2/3rds the size of the original table. That is about 8mb per index. 12 + 8 + 8 = 28. So our back-of-the-envelop size computation agrees very closely with what you observe in practice. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tomcat crashes with SQLite
"Frederic de la Goublaye" <[EMAIL PROTECTED]> wrote: > My server is under FreeBSD 5.4 > It is ok ? > I am not aware of any problems with FreeBSD 5.4. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In Mem Query Performance
RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > Hi, > > When the DB is opened in "in Memory mode",performance of query does not > improve. For table which has 10 columns of type Text and each column having > 128bytes data and having a total of 1 records. Performance is around 2400 > records/sec. Any ways to improve it. > You should be getting something well in excess of 1 million records per second. Probably you are structuring your query in a way that is very inefficient. Please provide your schema and the exact text of your query and perhaps someone can help you. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Index size
I have a question on index size, I have a table that by doing some testing the DB have grown to 250MB, might not seem too large, as the table have 4million records in it. But for this DB this table would grow a lot larger than that. So I did some tests with a simple table 3 fields FIELD1 is a integer with autogenerated primary key, the other two fields are integers, inserting 1 million records the DB ended up being around 12mb which seems to be correct, by adding indexes to the other 2 fields the db has grown to about 27mb, so my question is this. First what makes the indexes so large and second Is there any way to reduce the size of the indexes? My sql looks like this: CREATE INDEX IDX_FIELD2 ON TEST(FIELD2 ASC); Thanks.
[sqlite] FTS2 Module
I saw this post on FTS2, and looked on the site and saw the FTS2 binary, where can I find more information on this I searched the documentation and can't find anything, it would be appreciated if someone can point me to a doc that explains what it is and how to use it. Thank you.
Re: [sqlite] Tomcat crashes with SQLite
My server is under FreeBSD 5.4 It is ok ? Cheers Frederic On 6/25/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Frederic de la Goublaye" <[EMAIL PROTECTED]> wrote: > My server crashed again. > > I recompiled sqlite3-3-5 with this configure option > --enable-threadsafe =yes > > But I read that sqlite has somebug under Unix with the management of the > THREADS. > Some version so of Linux have a bug. Upgrade to linux kernel series 2.6 and you should be OK. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] In Mem Query Performance
Hi, When the DB is opened in "in Memory mode",performance of query does not improve. For table which has 10 columns of type Text and each column having 128bytes data and having a total of 1 records. Performance is around 2400 records/sec. Any ways to improve it. Thanks in advance. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
