Re: [sqlite] Registering a custom tokenizer per database rather than per connection

Quoth Travis Orr , on 2010-10-08 13:37:25 -0700: > Is it possible to register a custom FTS3 tokenizer to be persistent in > the database so that it is available every time a connection is opened? Not really. How would this work? You open a database, and the function pointer for

Re: [sqlite] Confitional IF in triggers

Hi Igor I'm converting an Interbase DB for use in a smaller application, so there are many different examples. I've included one particular example below, where I have converted a single Interbase trigger (which used IF statements), into 4 separate SQLite triggers. Any advice

Re: [sqlite] Speed up DELETE of a lot of records

On Fri, Oct 08, 2010 at 05:49:18PM +0100, Simon Slavin wrote: > On 8 Oct 2010, at 5:48pm, Stephan Wehner wrote: > > On Fri, Oct 8, 2010 at 7:14 AM, Michele Pradella > > wrote: > >> "science fiction?" was a rhetorically question. I'm only wondering > >> about what is

[sqlite] Registering a custom tokenizer per database rather than per connection

Is it possible to register a custom FTS3 tokenizer to be persistent in the database so that it is available every time a connection is opened? Or do I need to reregister the tokenizer every time I open a new connection to the database? Thanks Travis Orr IVL Audio Inc #3-6703 Rajpur

Re: [sqlite] Porting SQLite 3.7.2 to vxWorks 6.7

Thank you. Those changes (modified slightly for 3.7.2) allowed me to create a DKM project that compiles sqlite3.c to libSQLite3.a. However, when I link my main VIP project, I get the following unresolved errors: dld: warning: Undefined symbol 'rtpVerifyAndLock' in file 'partialImage.o' dld:

Re: [sqlite] Speed up DELETE of a lot of records

On Fri, Oct 8, 2010 at 9:49 AM, Simon Slavin wrote: > > On 8 Oct 2010, at 5:48pm, Stephan Wehner wrote: > >> On Fri, Oct 8, 2010 at 7:14 AM, Michele Pradella >> wrote: >>>  "science fiction?" was a rhetorically question. I'm only wondering >>>

Re: [sqlite] Speed up DELETE of a lot of records

On 8 Oct 2010, at 5:48pm, Stephan Wehner wrote: > On Fri, Oct 8, 2010 at 7:14 AM, Michele Pradella > wrote: >> "science fiction?" was a rhetorically question. I'm only wondering >> about what is the best and fastest way to DELETE a lot of records from >> huge DB. I

Re: [sqlite] Speed up DELETE of a lot of records

On Fri, Oct 8, 2010 at 7:14 AM, Michele Pradella wrote: >  "science fiction?" was a rhetorically question. I'm only wondering > about what is the best and fastest way to DELETE a lot of records from > huge DB. I know and understand physical limit of data moving: anyway

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

On Oct 8, 2010, at 9:44 PM, Raj, Praveen wrote: > Hello, > > I debugged the SQLite functions and here is my finding: > > The call to "mmap" in the function "unixShmMap" is causing the issue. > void *pMem = mmap(0, szRegion, PROT_READ|PROT_WRITE, > MAP_SHARED, pShmNode->h,

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

According to the QNX mmap page http://www.qnx.com/developers/docs/6.3.0SP3/neutrino/lib_ref/m/mmap.html MAP_NOINIT When specified, the POSIX requirement that the memory be zeroed is relaxed. The physical memory being used for this allocation must have been previously freed with

Re: [sqlite] Compiling dll 3.7.3 issue was [BUG] JOIN subquery in FROM with FTS3 table

You'll get this if your .DEF file includes any APIs that are not compiled into your build. A few sources are: SQLITE_OMIT_LOAD_EXTENSION=1 SQLITE_ENABLE_COLUMN_METADATA=1 SQLITE_ENABLE_STAT2=1 SQLITE_ENABLE_FTS3=1 SQLITE_ENABLE_RTREE=1 Depending on how you're compiling, you have a couple of

Re: [sqlite] Involving in sqlite development

On Fri, Oct 8, 2010 at 15:28, sjtirtha wrote: > 1. setup an development environment for SQLite (I'm using Ubuntu) >Do you have any preference which editor or IDE should I use? It's very easy. Download the sources, compile the "standard" way (./configure; make). Any

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

Hello, I debugged the SQLite functions and here is my finding: The call to "mmap" in the function "unixShmMap" is causing the issue. void *pMem = mmap(0, szRegion, PROT_READ|PROT_WRITE, MAP_SHARED, pShmNode->h, iRegion*szRegion); It is setting the previous memory region/regions to

Re: [sqlite] Involving in sqlite development

On Thu, Oct 7, 2010 at 5:26 PM, sjtirtha wrote: > Hi, > > I'm interested involving in sqlite development. > How can I start it? > If I can add to this question is there a posted wishlist, todo list or roadmap? Something like this: http://www.h2database.com/html/roadmap.html

Re: [sqlite] Speed up DELETE of a lot of records

"science fiction?" was a rhetorically question. I'm only wondering about what is the best and fastest way to DELETE a lot of records from huge DB. I know and understand physical limit of data moving: anyway for now I'm trying to split the BIG DELETE in some smaller DELETE to spread the time

Re: [sqlite] Involving in sqlite development

On 8 Oct 2010, at 2:28pm, sjtirtha wrote: > I do not have really C programming experience. But I have 8 years > programming experience in > several languages: PHP, JavaScript, Java, C++, ABAP > And I'm willing to learn C. Of all of those, if you can run Apache, the easiest one for you to start

[sqlite] Compiling dll 3.7.3 issue was [BUG] JOIN subquery in FROM with FTS3 table

Hi, Thanks Dan, Richard and Max for the (ultra) fast answer and Dan and Richard for the fix, because, as Max says, the bug is fixed now in 3.7.3. But I have a problem compiling sqlite with VC++. I was able to compile 3.7.2 without issues. With 3.7.3 The linker says: 'error LNK2001: unresolved

Re: [sqlite] Speed up DELETE of a lot of records

On Fri, Oct 08, 2010 at 09:09:09AM +0200, Michele Pradella scratched on the wall: > I was thinking this too, but I take this for last chance: my hope is I > can delete 5 millions of records in few seconds, science fiction? :) Science fiction of the worst B-grade sort. Think about the

Re: [sqlite] Involving in sqlite development

why don't you start with... On Fri, Oct 8, 2010 at 8:28 AM, sjtirtha wrote: > Hi, > > Here is what I want to do: > 1. I want to learn more about SQL Database implementation the above. You will be quite busy doing the above. When you are good at the above, you can definitely

Re: [sqlite] Involving in sqlite development

Hi, Here is what I want to do: 1. I want to learn more about SQL Database implementation 2. During the learn I want to give back what I get to the community I do not have really C programming experience. But I have 8 years programming experience in several languages: PHP, JavaScript, Java, C++,

Re: [sqlite] Confitional IF in triggers

Russell A wrote: > This may be a really dumb question, but I've searched and can't find an > answer. > Do SQLite triggers support any conditional expressions, like IF, or is there > only the WHEN statement? If the latter, does that > mean that multiple conditions must be

Re: [sqlite] Speed up DELETE of a lot of records

On Thu, Oct 7, 2010 at 8:05 PM, Michele Pradella wrote: > Hi all, I have a question about how to speed up a DELETE statement. > I have a DB of about 3GB: the DB has about 23 millions of records. > The DB is indexed by a DateTime column (is a 64 bit integer), and >

Re: [sqlite] Tcl bindins problem, once again

On Fri, Oct 8, 2010 at 2:55 AM, Paweł Salawa wrote: > Hi, > > I mentioned the problem before but it seems to be ignored for now. I'd > really appreciate some response, at least confirmation or denial that > I'm right about the problem. > > You are correct - duplicate

Re: [sqlite] Speed up DELETE of a lot of records

Rather than doing batch deletes why don't you add a date/time constraint to your selects? Then, you can have a thread which does a lazy delete on the old data. Or, you can create a trigger on inserts which deletes anything older than N-days or such. Or...if all you want is an interruptable

Re: [sqlite] Porting SQLite 3.7.2 to vxWorks 6.7

Hello Scott, Below is my patch on the latest SQLite 3.7.3. Please notice that I only verify it with GCC 4.1.2 compiler in VxWorks 6.6/6.7/6.8(I have not verify it with my real target machine yet). *** sqlite3.c.orig2010-10-08 10:42:22.0 +0800 --- sqlite3.c2010-10-08

Re: [sqlite] Confitional IF in triggers

On a field by field basis: CASE..WHEN [condition 1]..THEN [result 1]..WHEN [condition n]..THEN [result n]..ELSE..END - Original Message - From: "Russell A" To: Sent: Friday, October 08, 2010 01:24 a Subject: [sqlite] Confitional IF

Re: [sqlite] Speed up DELETE of a lot of records

I try 32 and it takes same time. Moreover the RAM used is about 500MB Even with "PRAGMA synchronous = OFF;" operation time is the same. I think the only way for now is to make more DELETE with less element, but it's not the best way Il 08/10/2010 10.41, Marcus Grimm ha scritto: > Michele

Re: [sqlite] Speed up DELETE of a lot of records

DELETE on PrimaryKey instead of DateTime index takes same time Il 08/10/2010 10.30, Michele Pradella ha scritto: >ok I'll try with 3.7.3 > DELETE is a little bit faster, and the -wal is reintegrated when I close > the connection. > Changing cache_size (I try 1) DELETE takes same time to

Re: [sqlite] Speed up DELETE of a lot of records

Michele Pradella wrote: > ok I'll try with 3.7.3 > DELETE is a little bit faster, and the -wal is reintegrated when I close > the connection. > Changing cache_size (I try 1) DELETE takes same time to complete. 1 doesn't sounds very big, I used to define it to e.g. 32 instead when

Re: [sqlite] Speed up DELETE of a lot of records

ok I'll try with 3.7.3 DELETE is a little bit faster, and the -wal is reintegrated when I close the connection. Changing cache_size (I try 1) DELETE takes same time to complete. Was my fault, because to close the shell connection I used Ctrl+C but this leave the -wal file. If I close with

Re: [sqlite] how do I load a csv file or what is the simplest text file to load into sqlite3?

joshua wojnas writes: > > how do I load a csv file or what is the simplest text file to load > into sqlite3? 1. sqlite3 yoursqlitedatabasename 2. .import yourtextfile yourtablename (see .h for documentation) ready but what you need is: - an existing sqlitedb (step1 already

Re: [sqlite] Speed up DELETE of a lot of records

I'll try to increase cache size, and I'll try operation on my Db with the 3.7.3 anyway I already ported the Fix of the WAL issue from recent snapshot. I'll try and let you know Il 08/10/2010 9.52, Marcus Grimm ha scritto: > Michele Pradella wrote: >>As I explain in previews email, I think

Re: [sqlite] Speed up DELETE of a lot of records

Michele Pradella wrote: > As I explain in previews email, I think that recreating the index is > the slowest operation I can do on my DB. > Anyway in my first email I ask another question about -wal file > Tryin to DELETE the (5 millions) records with the shell SQLITE interface > I can see

Re: [sqlite] Speed up DELETE of a lot of records

I was thinking this too, but I take this for last chance: my hope is I can delete 5 millions of records in few seconds, science fiction? :) Il 08/10/2010 9.00, Aldes Rossi ha scritto: >Il 10/08/2010 08:30 AM, Michele Pradella ha scritto: >> I don't know if could be faster to do more Delete

Re: [sqlite] Speed up DELETE of a lot of records

Il 10/08/2010 08:30 AM, Michele Pradella ha scritto: > I don't know if could be faster to do more Delete of less records, or > perhaps making a VIEW and than deleting all the records matching the > VIEW. I'm thinking about this to find the fastest solution, because the > problem is that when

[sqlite] Tcl bindins problem, once again

Hi, I mentioned the problem before but it seems to be ignored for now. I'd really appreciate some response, at least confirmation or denial that I'm right about the problem. Quoting from previous thread: --- QUOTE --- select a1.txt, a2.txt from a a1 join a a2 using (ref)

Re: [sqlite] Speed up DELETE of a lot of records

As I explain in previews email, I think that recreating the index is the slowest operation I can do on my DB. Anyway in my first email I ask another question about -wal file Tryin to DELETE the (5 millions) records with the shell SQLITE interface I can see the -wal grow up till 600MB. I can

Re: [sqlite] Speed up DELETE of a lot of records

I think that if the total records are 23 millions and the record to delete is 5 millions, creating a temp table of 23-5=18millions records is slower than deleting directly 5millions records. Anyway, the table has got only 1 index on the DateTime column that is INTEGER. I dropping all index