[sqlite] DBD::SQLite with FTS3 finally works. here is how...

[P Kishor]

I started with DBD::SQLite 1.26_05 and went back as much as
DBD::SQLite 1.21. Kept on getting segfaults. Any further back, and I
got DBD::SQLite 1.14 which bundled FTS2, so that wasn't good as well.

Finally, the amazing Audrey Tang to rescue. I downloaded the classic
DBD::SQLite::Amalgamation 3.6.12
(http://search.cpan.org/~audreyt/DBD-SQLite-Amalgamation-3.6.1.2/),
replaced the .c and .h files in it with those from the latest tarball
of original source 3.6.19, built it, and it works just fine, thank you
very much.

Of course, now my problem is that I have to somehow convince my shared
web host provider to also do the above rigmaroo or wait until the
default version from CPAN starts working again.

FTS3, in my personal view, is far more useful and important than
getting Foreign Keys support. That is because the latter can be
emulated in the app code, but the former can't. Will be grateful when
FTS3 is working again flawlessly in the publicly available CPAN
version.

-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] test DBD::SQLite 1.26_05 - foreign keys!

[P Kishor]

On Thu, Oct 15, 2009 at 11:49 PM, P Kishor  wrote:
> On Thu, Oct 15, 2009 at 11:37 PM, Darren Duncan  
> wrote:
>> P Kishor wrote [on sqlite-us...@sqlite.org]:
>>>
>>> On Thu, Oct 15, 2009 at 1:39 AM, Darren Duncan 
>>> wrote:

 All,

 I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a Perl
 DBI
 Driver) version 1.26_05 has been released on CPAN (by Adam Kennedy).

>>> ..

 P.S.  DBD::SQLite has at least 1 known bug, also in version 1.25, with
 regard to
 full-text search (FTS3); there is an included new failing test, which
 currently
 is set to skip so the CPAN testers don't issue fails,
>>>
>>> I tried to create a db with FTS3 and got the following
>>>
>>> perl(12546) malloc: *** error for object 0x1eb160: Non-aligned pointer
>>> being freed (2)
>>> *** set a breakpoint in malloc_error_break to debug
>>> perl(12546) malloc: *** error for object 0x870200: double free
>>> *** set a breakpoint in malloc_error_break to debug
>>> perl(12546) malloc: *** error for object 0x1ea668: Non-aligned pointer
>>> being freed
>>> *** set a breakpoint in malloc_error_break to debug
>>> Segmentation fault
>>>
>>> Is the above the FTS3 related error?
>>
>> Looks similar.  This is the official bug report that we already have, which
>> was reported on Oct 14, and on which the failing/skipping test is based:
>>
>>  http://rt.cpan.org/Public/Bug/Display.html?id=50503
>>
>>  Subject: Segfault with fts3
>>
>> A copy of the ticket text is displayed below the dashed line in this email
>> also.
>>
>> The bug report was against DBD::SQLite versions that bundled SQLite 3.6.13
>> and 3.6.18 respectively, for both of which the bug manifests.
>>
>> As of yet the bug is unfixed.
>>
>> If you have any new information, it would be useful for you to add it to the
>> above RT ticket.
>>
>
>
> Well, the only additional information I have is that I went back to
> DBD::SQLite 1.21, and that compiled fine and also was able to create
> and populate FTS3 tables. However, and this is weird, it keeps on
> periodically choking up on MATCH queries. I have a web app on my
> laptop, and when I query the db using FTS3, I get back an "Internal
> Server Error" ("Premature end of script headers" in the Apache error
> log), but if I reload the page a few times, I will suddenly get the
> search result back. Reload, and the process repeats. Error a couple,
> three times, and then, get the correct results.
>
> This is all very mysterious, and very disheartening.
>
>
>

Ok. Perhaps a bit more on the mystery I was experiencing with
DBD::SQLite 1.21. I was getting periodic 500 errors, and nothing
illuminating in the Apache logs as well. I finally figured out from
the command line... I am still getting occasional segmentation fault
with DBD::SQLite 1.21. Will roll back even more and see what I get.



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys question

[Dennis Volodomanov]

> No performance gain for joins or anything like that. Using FK
> constraints does not affect SELECT performance. They slow down
> some INSERT/UPDATE/DELETE operations though.

Thank you! I've done some timings as well (it takes a few hours to build a 
sizeable database) and found out pretty much the same, so I've rolled back FK, 
as the system works well without them based on triggers and other checks.

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] test DBD::SQLite 1.26_05 - foreign keys!

[P Kishor]

On Thu, Oct 15, 2009 at 11:37 PM, Darren Duncan  wrote:
> P Kishor wrote [on sqlite-us...@sqlite.org]:
>>
>> On Thu, Oct 15, 2009 at 1:39 AM, Darren Duncan 
>> wrote:
>>>
>>> All,
>>>
>>> I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a Perl
>>> DBI
>>> Driver) version 1.26_05 has been released on CPAN (by Adam Kennedy).
>>>
>> ..
>>>
>>> P.S.  DBD::SQLite has at least 1 known bug, also in version 1.25, with
>>> regard to
>>> full-text search (FTS3); there is an included new failing test, which
>>> currently
>>> is set to skip so the CPAN testers don't issue fails,
>>
>> I tried to create a db with FTS3 and got the following
>>
>> perl(12546) malloc: *** error for object 0x1eb160: Non-aligned pointer
>> being freed (2)
>> *** set a breakpoint in malloc_error_break to debug
>> perl(12546) malloc: *** error for object 0x870200: double free
>> *** set a breakpoint in malloc_error_break to debug
>> perl(12546) malloc: *** error for object 0x1ea668: Non-aligned pointer
>> being freed
>> *** set a breakpoint in malloc_error_break to debug
>> Segmentation fault
>>
>> Is the above the FTS3 related error?
>
> Looks similar.  This is the official bug report that we already have, which
> was reported on Oct 14, and on which the failing/skipping test is based:
>
>  http://rt.cpan.org/Public/Bug/Display.html?id=50503
>
>  Subject: Segfault with fts3
>
> A copy of the ticket text is displayed below the dashed line in this email
> also.
>
> The bug report was against DBD::SQLite versions that bundled SQLite 3.6.13
> and 3.6.18 respectively, for both of which the bug manifests.
>
> As of yet the bug is unfixed.
>
> If you have any new information, it would be useful for you to add it to the
> above RT ticket.
>


Well, the only additional information I have is that I went back to
DBD::SQLite 1.21, and that compiled fine and also was able to create
and populate FTS3 tables. However, and this is weird, it keeps on
periodically choking up on MATCH queries. I have a web app on my
laptop, and when I query the db using FTS3, I get back an "Internal
Server Error" ("Premature end of script headers" in the Apache error
log), but if I reload the page a few times, I will suddenly get the
search result back. Reload, and the process repeats. Error a couple,
three times, and then, get the correct results.

This is all very mysterious, and very disheartening.



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exception writing to database from multiple processes

[Jay A. Kreibich]

On Thu, Oct 15, 2009 at 10:57:11PM -0400, Pavel Ivanov scratched on the wall:
> You're definitely talking about some bug in your application or some
> misunderstanding about how SQLite should work. SQLite by itself never
> causes any deadlocks.

  As I understand it, that's not exactly true.  SQLite can and does
  deadlock.  It also tries to detect when this is happening and
  encourage the application to break the deadlock.  This depends on the
  applications' cooperation, however.

  From  :

 Consider a scenario where one process is holding a read lock that
 it is trying to promote to a reserved lock and a second process is
 holding a reserved lock that it is trying to promote to an
 exclusive lock. The first process cannot proceed because it is
 blocked by the second and the second process cannot proceed
 because it is blocked by the first. If both processes invoke the
 busy handlers, neither will make any progress. Therefore, SQLite
 returns SQLITE_BUSY for the first process, hoping that this will
 induce the first process to release its read lock and allow the
 second process to proceed.

  This implies that SQLite can figure out what is going on, but will
  not automatically rollback a transaction and break the deadlock by
  itself.  That also implies that if an application goes into an infinite
  "try again" loop whenever it gets an SQLITE_BUSY return code, the
  deadlock may persist.

  The page  is clear that
  this is the expected behavior, on behalf of the application:  "It is
  recommended that applications respond to the errors listed above
  [including SQLITE_BUSY] by explicitly issuing a ROLLBACK command."

  In short, you can poke at a SQLITE_BUSY state for a bit, but fairly
  soon you should give up and back all the way out.  If you don't, a
  deadlock is possible.  But that would be considered an application
  bug, not an SQLite bug.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] test DBD::SQLite 1.26_05 - foreign keys!

[Darren Duncan]

P Kishor wrote [on sqlite-us...@sqlite.org]:
> On Thu, Oct 15, 2009 at 1:39 AM, Darren Duncan  
> wrote:
>> All,
>>
>> I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a Perl DBI
>> Driver) version 1.26_05 has been released on CPAN (by Adam Kennedy).
>>
> ..
>> P.S.  DBD::SQLite has at least 1 known bug, also in version 1.25, with 
>> regard to
>> full-text search (FTS3); there is an included new failing test, which 
>> currently
>> is set to skip so the CPAN testers don't issue fails,
> 
> I tried to create a db with FTS3 and got the following
> 
> perl(12546) malloc: *** error for object 0x1eb160: Non-aligned pointer
> being freed (2)
> *** set a breakpoint in malloc_error_break to debug
> perl(12546) malloc: *** error for object 0x870200: double free
> *** set a breakpoint in malloc_error_break to debug
> perl(12546) malloc: *** error for object 0x1ea668: Non-aligned pointer
> being freed
> *** set a breakpoint in malloc_error_break to debug
> Segmentation fault
> 
> Is the above the FTS3 related error?

Looks similar.  This is the official bug report that we already have, which was 
reported on Oct 14, and on which the failing/skipping test is based:

   http://rt.cpan.org/Public/Bug/Display.html?id=50503

   Subject: Segfault with fts3

A copy of the ticket text is displayed below the dashed line in this email also.

The bug report was against DBD::SQLite versions that bundled SQLite 3.6.13 and 
3.6.18 respectively, for both of which the bug manifests.

As of yet the bug is unfixed.

If you have any new information, it would be useful for you to add it to the 
above RT ticket.

-- Darren Duncan



[text/plain 790b]
The attached script fails on 1.25 and 1.26_04. It does one of three things:

- segfaults
- gives a double free error
- hangs without any cpu activity

Which of the above three it does depends on the exact script (adding or
removing a print line can change its behavior), but it always fails.

Here are a couple sample error messages:
*** glibc detected *** double free or corruption (!prev):
0x0083c480 ***
Abort (core dumped)

Segmentation fault (core dumped)

The database doesn't appear to be corrupted after the failure -- running
an integrity check with the sqlite3 command line binary says the db is okay.

The equivalent code without the fts3 module works fine.

I did a quick test with the DBD::SQLite packaged with the latest Ubuntu
(1.14), and that version seems to work.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite 3.6.19 missing FTS3?

[P Kishor]

This is getting weirder by the minute. I just downloaded sqlite 3.6.19
and built it like I have previous versions. But, I didn't get FTS3? I
get the following error --

SQL error: no such module: fts3

Did the economic recession affect this? Is this a result of the
cutbacks in spending? Where is my free FTS3?


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys question

[Dan Kennedy]

On Oct 16, 2009, at 3:08 AM, Dennis Volodomanov wrote:

> Dan Kennedy wrote:
>> I don't think the triggers you are using can be implemented using
>> foreign keys. Your triggers are basically reference counting (or
>> garbage collecting, whatever you want to call it) - "when the
>> number of references to a data item drops to zero, delete the
>> data item".
>>
> Yes, exactly.
>> You could add a foreign key to this schema to prevent the DataID
>> column of TableA from being populated with an invalid id (one
>> that does not refer to any id of the TableData table).
>>
> I do that check when I add entries to TableA in any case, so probably
> that's best left as-is.
>
> Is there any gain in terms of performance when doing lookups on TableA
> with JOINs with TableData when there're FOREIGN KEYs defined? Does
> SQLite internally optimize queries better in such cases?

No performance gain for joins or anything like that. Using FK
constraints does not affect SELECT performance. They slow down
some INSERT/UPDATE/DELETE operations though.

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exception writing to database from multiple processes

[John Crenshaw]

I can empathize with this problem, having just worked through this recently. 
The bottom line is if you need concurrency, you're going to have to structure 
your code appropriately. Here are some things I found helpful:

1. ENCAPSULATE! You'll want to encapsulate your handling of queries so that you 
only have to wrap things for LOCKED and BUSY handling in one place.
2. Shared cache mode (sqlite3_enable_shared_cache()) has a better locking style 
for concurrency within a single process (it uses table level locking). This is 
almost a necessity if you need concurrent access between threads.
3. If you can possibly get away with it, use "PRAGMA read_uncommitted = true". 
THIS WILL MAKE YOUR READS NON ACID, but it greatly reduces contention. 
Generally speaking, a little care in your code should keep the non-acid reads 
from being a problem.
4. An open VDBE (sqlite3_stmt*) in the middle of returning rows will hold a 
read lock on its table. While that read lock is open, other threads will be 
unable to write to that table. Beware long time consuming loops that hold a 
read lock on a table another thread might want to write to.
5. Keep as much writing as possible in one thread (all of it, if you can 
manage.)
6. If you have to break rule 5, try to make sure that the different threads use 
different tables.
7. If any thread has a long running operation, make sure that it won't block 
any important tables for the whole time
8. If you have to break rule 7, make it possible to detect when another thread 
has been blocked, and yield to that thread by committing the transaction and 
releasing any open cursors.
9. Every query needs to happen in a loop. This loop needs to check for LOCKED 
(and perhaps BUSY) conditions. Handle LOCKED using sqlite3_unlock_notify(). 
This loop is half the reason for encapsulating.

This isn't everything, but the list is long already, and that should get you 
past most of the hard stuff. Best luck.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Thursday, October 15, 2009 10:57 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Exception writing to database from multiple processes

You're definitely talking about some bug in your application or some
misunderstanding about how SQLite should work. SQLite by itself never
causes any deadlocks. So I guess in order to be able to help you we
need to know more about what you're doing. Maybe for example you're
forgetting to commit/rollback transaction somewhere, maybe you're
dead-locking on your own mutexes. Are you able to look at the stack
traces where your workers hang?

Pavel

On Thu, Oct 15, 2009 at 10:40 PM, David Carter  wrote:
> Yes, that's correct. I also tried using BEGIN EXCLUSIVE instead of BEGIN 
> IMMEDIATE.  This results in only one worker process being able to write to 
> the database, while the other worker processes continually get SQLITE_BUSY 
> when trying to write.
>
> David
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Thursday, 15 October 2009 9:53 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Exception writing to database from multiple processes
>
>> However, when it is run
>> inside an Application Pool with multiple worker processes, the database
>> soon becomes locked and cannot be written to by any of the worker
>> processes.
>
> You mean your application hangs? None of workers can write to database
> and nothing else happens in application? Nobody's reading database at
> the same time, nobody connecting to database via command line tool,
> nothing happens at all?
>
> Pavel
>
> On Thu, Oct 15, 2009 at 2:40 AM, David Carter  wrote:
>> Hello,
>>
>>
>>
>> I am currently using the SQLite Amalgamation v3.6.19 from
>> http://www.sqlite.org/download.html in an ISAPI Extension to write out
>> usage statistics to an SQLite database.
>>
>>
>>
>> When the ISAPI extension is running inside an Application Pool with a
>> single worker process, everything works fine.  However, when it is run
>> inside an Application Pool with multiple worker processes, the database
>> soon becomes locked and cannot be written to by any of the worker
>> processes.
>>
>>
>>
>> Each worker process has a separate background thread which writes to the
>> database every 5 seconds.  Each write is performed as a single
>> transaction starting with "BEGIN IMMEDIATE".
>>
>>
>>
>> Any help you can provide would be greatly appreciated.
>>
>>
>>
>> Thanks,
>>
>>
>>
>> David
>>
>> ___
>> 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
> 

Re: [sqlite] DBD::SQLite 1.23 fails with bus error

[P Kishor]

On Thu, Oct 15, 2009 at 10:59 PM, P Kishor  wrote:
> On Thu, Oct 15, 2009 at 10:52 PM, P Kishor  wrote:
>> Because of FTS3 problems with DBD::SQLite 1.26_05, I downgraded to
>> 1.23, but now I get a bus error/segmentation fault, once again, on
>> FTS3 operations.
>>
>> Is there a version of DBD::SQLite that is known to have a working
>> implementation of FTS3?
>>
>>
>
>
> Ok. It seems that I have to go back to DBD::SQLite 1.14 in order to
> get FTS3 to work. That version was released in Sep 2007, and contains
> SQLite 3.4.2. Boy, that FTS3 error seems to have lasted a long time.
>
>
>


Jeebus, I am having poor luck with DBD::SQLite today. Turns out
version 1.14 doesn't even have FTS3. Instead, it is still using FTS2.
Back to the quest.

Would any of the DBD::SQLite maintainers on this list kindly guide me
as to which version I can use with a working implementation of FTS3?

Many thanks,



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DBD::SQLite 1.23 fails with bus error

[P Kishor]

On Thu, Oct 15, 2009 at 10:52 PM, P Kishor  wrote:
> Because of FTS3 problems with DBD::SQLite 1.26_05, I downgraded to
> 1.23, but now I get a bus error/segmentation fault, once again, on
> FTS3 operations.
>
> Is there a version of DBD::SQLite that is known to have a working
> implementation of FTS3?
>
>


Ok. It seems that I have to go back to DBD::SQLite 1.14 in order to
get FTS3 to work. That version was released in Sep 2007, and contains
SQLite 3.4.2. Boy, that FTS3 error seems to have lasted a long time.



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DBD::SQLite 1.23 fails with bus error

[P Kishor]

Because of FTS3 problems with DBD::SQLite 1.26_05, I downgraded to
1.23, but now I get a bus error/segmentation fault, once again, on
FTS3 operations.

Is there a version of DBD::SQLite that is known to have a working
implementation of FTS3?


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regular expression search

[Clark Christensen]

The current dev branch of DBD-SQLite (1.26_05) includes an implementation of 
SQLite's unimplemented REGEX function 
(http://search.cpan.org/~adamk/DBD-SQLite/lib/DBD/SQLite.pm#REGEXP_function).  
Presumably, this will survive to the next production release.

Otherwise, DBD-SQLite offers a custom function interface where you can write 
custom SQL functions in Perl.  Perhaps something like


$dbh->func( 'pattern_match', 2, sub { my ($input, $regex) = @_; return ($input 
=~ m/$regex/); }, 'create_function' );

and your SQL might be

select foo from mytable where pattern_match(foo, 'A[BCD]D[AD]BB') > 0;

Sorry, it's untested, off the top of my head.

 -Clark

- Original Message 
From: "Farkas, Illes" 
To: sqlite-users@sqlite.org
Sent: Thu, October 15, 2009 2:24:16 PM
Subject: [sqlite] regular expression search

Hi,

I have strings in a database and I would like to find all of them
matching a pattern that is 5-10 characters long. In each position of
the pattern up to three different characters may be allowed. This
would be a typical regular expression that I'd like to find:

A (B | C | D ) D ( A | D ) B B

(I use the Perl DBI with sqlite3)

Any help would be greatly appreciated. Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] test DBD::SQLite 1.26_05 - foreign keys!

[P Kishor]

On Thu, Oct 15, 2009 at 1:39 AM, Darren Duncan  wrote:
> All,
>
> I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a Perl DBI
> Driver) version 1.26_05 has been released on CPAN (by Adam Kennedy).
>
..
>
> P.S.  DBD::SQLite has at least 1 known bug, also in version 1.25, with regard 
> to
> full-text search (FTS3); there is an included new failing test, which 
> currently
> is set to skip so the CPAN testers don't issue fails,

I tried to create a db with FTS3 and got the following

perl(12546) malloc: *** error for object 0x1eb160: Non-aligned pointer
being freed (2)
*** set a breakpoint in malloc_error_break to debug
perl(12546) malloc: *** error for object 0x870200: double free
*** set a breakpoint in malloc_error_break to debug
perl(12546) malloc: *** error for object 0x1ea668: Non-aligned pointer
being freed
*** set a breakpoint in malloc_error_break to debug
Segmentation fault


Is the above the FTS3 related error?


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exception writing to database from multiple processes

[Pavel Ivanov]

You're definitely talking about some bug in your application or some
misunderstanding about how SQLite should work. SQLite by itself never
causes any deadlocks. So I guess in order to be able to help you we
need to know more about what you're doing. Maybe for example you're
forgetting to commit/rollback transaction somewhere, maybe you're
dead-locking on your own mutexes. Are you able to look at the stack
traces where your workers hang?

Pavel

On Thu, Oct 15, 2009 at 10:40 PM, David Carter  wrote:
> Yes, that's correct. I also tried using BEGIN EXCLUSIVE instead of BEGIN 
> IMMEDIATE.  This results in only one worker process being able to write to 
> the database, while the other worker processes continually get SQLITE_BUSY 
> when trying to write.
>
> David
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Thursday, 15 October 2009 9:53 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Exception writing to database from multiple processes
>
>> However, when it is run
>> inside an Application Pool with multiple worker processes, the database
>> soon becomes locked and cannot be written to by any of the worker
>> processes.
>
> You mean your application hangs? None of workers can write to database
> and nothing else happens in application? Nobody's reading database at
> the same time, nobody connecting to database via command line tool,
> nothing happens at all?
>
> Pavel
>
> On Thu, Oct 15, 2009 at 2:40 AM, David Carter  wrote:
>> Hello,
>>
>>
>>
>> I am currently using the SQLite Amalgamation v3.6.19 from
>> http://www.sqlite.org/download.html in an ISAPI Extension to write out
>> usage statistics to an SQLite database.
>>
>>
>>
>> When the ISAPI extension is running inside an Application Pool with a
>> single worker process, everything works fine.  However, when it is run
>> inside an Application Pool with multiple worker processes, the database
>> soon becomes locked and cannot be written to by any of the worker
>> processes.
>>
>>
>>
>> Each worker process has a separate background thread which writes to the
>> database every 5 seconds.  Each write is performed as a single
>> transaction starting with "BEGIN IMMEDIATE".
>>
>>
>>
>> Any help you can provide would be greatly appreciated.
>>
>>
>>
>> Thanks,
>>
>>
>>
>> David
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exception writing to database from multiple processes

[David Carter]

Yes, that's correct. I also tried using BEGIN EXCLUSIVE instead of BEGIN 
IMMEDIATE.  This results in only one worker process being able to write to the 
database, while the other worker processes continually get SQLITE_BUSY when 
trying to write.  

David 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Thursday, 15 October 2009 9:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Exception writing to database from multiple processes

> However, when it is run
> inside an Application Pool with multiple worker processes, the database
> soon becomes locked and cannot be written to by any of the worker
> processes.

You mean your application hangs? None of workers can write to database
and nothing else happens in application? Nobody's reading database at
the same time, nobody connecting to database via command line tool,
nothing happens at all?

Pavel

On Thu, Oct 15, 2009 at 2:40 AM, David Carter  wrote:
> Hello,
>
>
>
> I am currently using the SQLite Amalgamation v3.6.19 from
> http://www.sqlite.org/download.html in an ISAPI Extension to write out
> usage statistics to an SQLite database.
>
>
>
> When the ISAPI extension is running inside an Application Pool with a
> single worker process, everything works fine.  However, when it is run
> inside an Application Pool with multiple worker processes, the database
> soon becomes locked and cannot be written to by any of the worker
> processes.
>
>
>
> Each worker process has a separate background thread which writes to the
> database every 5 seconds.  Each write is performed as a single
> transaction starting with "BEGIN IMMEDIATE".
>
>
>
> Any help you can provide would be greatly appreciated.
>
>
>
> Thanks,
>
>
>
> David
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regular expression search

[John Crenshaw]

If you need more advanced matching (I.E. full regex, beyond what GLOB
can do) you could implement a custom function. A regex search is always
going to have to resort to a full table scan anyway, so it won't hurt
performance any.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Thursday, October 15, 2009 6:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] regular expression search


On 15 Oct 2009, at 10:24pm, Farkas, Illes wrote:

> I have strings in a database and I would like to find all of them
> matching a pattern that is 5-10 characters long. In each position of
> the pattern up to three different characters may be allowed. This
> would be a typical regular expression that I'd like to find:
>
> A (B | C | D ) D ( A | D ) B B

First guess would be to use GLOB:

http://www.sqlite.org/lang_corefunc.html#glob

Either as an infix operator, or as a function:

http://www.sqlite.org/lang_corefunc.html#glob

I cannot find a page which gives SQLite examples using GLOB, but this  
page

http://en.wikipedia.org/wiki/Glob_(programming)

gives examples showing the use of square brackets, which appears to be  
what you want.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regular expression search

[Simon Slavin]

On 15 Oct 2009, at 10:24pm, Farkas, Illes wrote:

> I have strings in a database and I would like to find all of them
> matching a pattern that is 5-10 characters long. In each position of
> the pattern up to three different characters may be allowed. This
> would be a typical regular expression that I'd like to find:
>
> A (B | C | D ) D ( A | D ) B B

First guess would be to use GLOB:

http://www.sqlite.org/lang_corefunc.html#glob

Either as an infix operator, or as a function:

http://www.sqlite.org/lang_corefunc.html#glob

I cannot find a page which gives SQLite examples using GLOB, but this  
page

http://en.wikipedia.org/wiki/Glob_(programming)

gives examples showing the use of square brackets, which appears to be  
what you want.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] regular expression search

[Farkas, Illes]

Hi,

I have strings in a database and I would like to find all of them
matching a pattern that is 5-10 characters long. In each position of
the pattern up to three different characters may be allowed. This
would be a typical regular expression that I'd like to find:

A (B | C | D ) D ( A | D ) B B

(I use the Perl DBI with sqlite3)

Any help would be greatly appreciated. Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Detect nullable fields?

[Kees Nuyt]

On Thu, 15 Oct 2009 22:13:22 +0200, Lothar Behrens
 wrote:

>Hi,
>
>is there a function to detect nullable fields?

.headers on
PRAGMA table_info(yourtablename);

>Thanks
>
>Lothar
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Detect nullable fields?

[Lothar Behrens]

Hi,

is there a function to detect nullable fields?

Thanks

Lothar

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys question

[Dennis Volodomanov]

Dan Kennedy wrote:
> I don't think the triggers you are using can be implemented using
> foreign keys. Your triggers are basically reference counting (or
> garbage collecting, whatever you want to call it) - "when the
> number of references to a data item drops to zero, delete the
> data item".
>   
Yes, exactly.
> You could add a foreign key to this schema to prevent the DataID
> column of TableA from being populated with an invalid id (one
> that does not refer to any id of the TableData table).
>   
I do that check when I add entries to TableA in any case, so probably 
that's best left as-is.

Is there any gain in terms of performance when doing lookups on TableA 
with JOINs with TableData when there're FOREIGN KEYs defined? Does 
SQLite internally optimize queries better in such cases?

Thank you,

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to represent a tree in SQL

[Michael Chen]

Dear Cariotoglou Mike, are you referring to an mysql link in the previous
post? thanks, Michael

On Wed, Oct 14, 2009 at 11:33 AM, Cariotoglou Mike  wrote:

> just to throw in my two bits:
>
> I have done a lot of work with trees in SQL, and IMHO, the best method BY
> FAR is the one described in the link below (mysql article), mainly due to
> its capability to handle siblings and descendants.
> for example, the self-join, parent_node method described elsewhere in this
> list is failr ok for simple requirements, but is completely useles in the
> following cases:
>
> select all_descendans_on_any depth for a particular node
>
> find out if a node "belongs" to a parent which is not its immediate parent.
>
> in other words, SET operations are quite difficult in the node-parent
> relation, but very easy and efficient in the adjacent list model.
>
> I personally stopped looking for a better solution once I came across and
> comprehended the power of this method...
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
> > Sebastian Bermudez
> > Sent: Wednesday, October 14, 2009 5:10 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] how to represent a tree in SQL
> >
> >
> >
> > look this:
> >
> > http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
> >
> > is for mysql but aplies to every sql DB
> >
> >
> > - Mensaje original 
> > De: Michael Chen 
> > Para: sqlite-users@sqlite.org
> > Enviado: mié, octubre 14, 2009 10:40:45 AM
> > Asunto: [sqlite] how to represent a tree in SQL
> >
> > Dear there,
> >
> > I am developing a numerical application, where a single
> > rooted dynamic tree is the main data structure. I intended to
> > use SQLite for this purpose and also for other data as well.
> > However I have no reference how to represent a tree using
> > tables. I need these functionalities:
> > (1) basic tree: single root, multi-levels, arbitrary number
> > of branches, index each node, index each path (from root to a
> > leaf), lookup parent, lookup descendants
> > (2) dynamics: delete a path, add a path; maintain parent and
> > descendants table; maintain history of tree; lookup history
> > (3) each node has lots of  matrix and vectors, which will be
> > updated with dynamics, and should be tracked
> >
> > As you see, it is nontrivial to write a tree structure to
> > support all these functions, while keep the code clean and
> > neat. That's why I want to use SQLite to keep things
> > straight. Is there a good reference on this?
> >
> > Michael Chen
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> >   Yahoo! Cocina
> >
> > Encontra las mejores recetas con Yahoo! Cocina.
> >
> >
> > http://ar.mujer.yahoo.com/cocina/
> > ___
> > 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
>



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite .dump does not save PRAGMA user_version

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Schubert wrote:
> When creating a dump with sqlite .dump, it will not save the user_version.

The problem is that the usage of the user_version is not known.  It could be
harmless to dump or it could cause problems on a restore.

> PS: please CC me on replies since I'm not subscribed to the mailing list

http://catb.org/~esr/faqs/smart-questions.html#noprivate

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrXVkYACgkQmOOfHg372QQ/+gCfd62kflIPGGwmZQ55sOWs4Eig
OR8AnjE+E7MgpTWabKTpQmC29xrtXHf6
=d+CQ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replacing a table

[P Kishor]

On Thu, Oct 15, 2009 at 10:37 AM, Kavita Raghunathan
 wrote:
> Thanks much Pavel.
>
> No you are right I'm more of a L3 protocols
> person with some user interfaces and network security,
> somehow ended up trying to use sqlite now. I'll look through
> your link on SQL.

My advice -- since you are now working with a SQL database, you would
become way more efficient if you followed Pavel's advice and learned
SQL basics. The basics are really not that hard, but your questions
display that you are missing that knowledge. Once you know the basics,
you will be well on your way, and will really be encountering
SQL-specific and sqlite-specific hurdles. That is where this list
comes in to help and becomes useful.


>
> Regards,
> Kavita
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Cc: "Kelvin Xu" 
> Sent: Thursday, October 15, 2009 10:28:23 AM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Replacing a table
>
> I guess you didn't work with SQL anywhere in your developer life,
> right? Read some books or internet pages about it. You can start from
> here http://en.wikipedia.org/wiki/SQL and follow any links there.
>
> To update column in all rows of the table you need to issue the
> following statement:
>
> UPDATE table_name SET column_name = value
>
> It doesn't require you to loop through all rows although DBMS will
> iterate all of them for you.
>
>
> Pavel
>
> On Thu, Oct 15, 2009 at 11:22 AM, Kavita Raghunathan
>  wrote:
>> Thanks Pavel and Owen. This is very useful information.
>>
>> Also how can we change a whole column at a time ?
>> In otherwords, the entire column needing to be changed would
>> involve looping through each entry and changing that value,
>> instead i want to substitute a whole column.
>>
>> Thanks!
>> Kavita
>> - Original Message -
>> From: "Owen O'Neill" 
>> To: "General Discussion of SQLite Database" 
>> Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central
>> Subject: Re: [sqlite] Replacing a table
>>
>>
>> Run the sql
>> 'delete from "tablename";'
>>
>> if the table definition is different (different column names or data
>> types ) then you will need to drop the table and create a new one.
>> 'drop table "tablename";'
>>
>> http://www.sqlite.org/lang_createtable.html
>>
>> if the table is huge you might get different performance depending on
>> whether your journal settings are to truncate or delete or pad etc.
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
>> Sent: Thursday, October 15, 2009 3:51 PM
>> To: General Discussion of SQLite Database
>> Cc: Kelvin Xu
>> Subject: [sqlite] Replacing a table
>>
>> Hi,
>>
>> Is there a way to use the sqlite wrappers to "replace" or delete a table
>> completely ?
>> (without looping through and deleting each row and column)
>> The number of columns and rows of the new table is identical to the
>> number
>> of columns and rows of the old table being replaced. Is there a quick
>> way
>> to do that?
>>
>> Thanks,
>> Kavita
>> ___
>> 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-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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replacing a table

[Gerry Snyder]

Pavel Ivanov wrote:
> 
>
> To update column in all rows of the table you need to issue the
> following statement:
>
> UPDATE table_name SET column_name = value
>   

And note that the "value" above does not have to be a constant. It can, 
for instance, depend on other values in the row being updated.


Gerry

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replacing a table

[Kavita Raghunathan]

Thanks much Pavel. 

No you are right I'm more of a L3 protocols
person with some user interfaces and network security, 
somehow ended up trying to use sqlite now. I'll look through
your link on SQL. 

Regards,
Kavita
- Original Message -
From: "Pavel Ivanov" 
To: "General Discussion of SQLite Database" 
Cc: "Kelvin Xu" 
Sent: Thursday, October 15, 2009 10:28:23 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Replacing a table

I guess you didn't work with SQL anywhere in your developer life,
right? Read some books or internet pages about it. You can start from
here http://en.wikipedia.org/wiki/SQL and follow any links there.

To update column in all rows of the table you need to issue the
following statement:

UPDATE table_name SET column_name = value

It doesn't require you to loop through all rows although DBMS will
iterate all of them for you.


Pavel

On Thu, Oct 15, 2009 at 11:22 AM, Kavita Raghunathan
 wrote:
> Thanks Pavel and Owen. This is very useful information.
>
> Also how can we change a whole column at a time ?
> In otherwords, the entire column needing to be changed would
> involve looping through each entry and changing that value,
> instead i want to substitute a whole column.
>
> Thanks!
> Kavita
> - Original Message -
> From: "Owen O'Neill" 
> To: "General Discussion of SQLite Database" 
> Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Replacing a table
>
>
> Run the sql
> 'delete from "tablename";'
>
> if the table definition is different (different column names or data
> types ) then you will need to drop the table and create a new one.
> 'drop table "tablename";'
>
> http://www.sqlite.org/lang_createtable.html
>
> if the table is huge you might get different performance depending on
> whether your journal settings are to truncate or delete or pad etc.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
> Sent: Thursday, October 15, 2009 3:51 PM
> To: General Discussion of SQLite Database
> Cc: Kelvin Xu
> Subject: [sqlite] Replacing a table
>
> Hi,
>
> Is there a way to use the sqlite wrappers to "replace" or delete a table
> completely ?
> (without looping through and deleting each row and column)
> The number of columns and rows of the new table is identical to the
> number
> of columns and rows of the old table being replaced. Is there a quick
> way
> to do that?
>
> Thanks,
> Kavita
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replacing a table

[Pavel Ivanov]

I guess you didn't work with SQL anywhere in your developer life,
right? Read some books or internet pages about it. You can start from
here http://en.wikipedia.org/wiki/SQL and follow any links there.

To update column in all rows of the table you need to issue the
following statement:

UPDATE table_name SET column_name = value

It doesn't require you to loop through all rows although DBMS will
iterate all of them for you.


Pavel

On Thu, Oct 15, 2009 at 11:22 AM, Kavita Raghunathan
 wrote:
> Thanks Pavel and Owen. This is very useful information.
>
> Also how can we change a whole column at a time ?
> In otherwords, the entire column needing to be changed would
> involve looping through each entry and changing that value,
> instead i want to substitute a whole column.
>
> Thanks!
> Kavita
> - Original Message -
> From: "Owen O'Neill" 
> To: "General Discussion of SQLite Database" 
> Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Replacing a table
>
>
> Run the sql
> 'delete from "tablename";'
>
> if the table definition is different (different column names or data
> types ) then you will need to drop the table and create a new one.
> 'drop table "tablename";'
>
> http://www.sqlite.org/lang_createtable.html
>
> if the table is huge you might get different performance depending on
> whether your journal settings are to truncate or delete or pad etc.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
> Sent: Thursday, October 15, 2009 3:51 PM
> To: General Discussion of SQLite Database
> Cc: Kelvin Xu
> Subject: [sqlite] Replacing a table
>
> Hi,
>
> Is there a way to use the sqlite wrappers to "replace" or delete a table
> completely ?
> (without looping through and deleting each row and column)
> The number of columns and rows of the new table is identical to the
> number
> of columns and rows of the old table being replaced. Is there a quick
> way
> to do that?
>
> Thanks,
> Kavita
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replacing a table

[O'Neill, Owen]

No problem,

Update "table" set "columnname"='newvalue';

Time to learn some sql basics and discover the 'where' clause :-)

http://www.sqlite.org/lang_update.html


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
Sent: Thursday, October 15, 2009 4:22 PM
To: General Discussion of SQLite Database
Cc: Kelvin Xu
Subject: Re: [sqlite] Replacing a table

Thanks Pavel and Owen. This is very useful information.

Also how can we change a whole column at a time ?
In otherwords, the entire column needing to be changed would
involve looping through each entry and changing that value,
instead i want to substitute a whole column.

Thanks!
Kavita
- Original Message -
From: "Owen O'Neill" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Replacing a table


Run the sql
'delete from "tablename";'

if the table definition is different (different column names or data
types ) then you will need to drop the table and create a new one.
'drop table "tablename";'

http://www.sqlite.org/lang_createtable.html

if the table is huge you might get different performance depending on
whether your journal settings are to truncate or delete or pad etc.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
Sent: Thursday, October 15, 2009 3:51 PM
To: General Discussion of SQLite Database
Cc: Kelvin Xu
Subject: [sqlite] Replacing a table

Hi, 

Is there a way to use the sqlite wrappers to "replace" or delete a table
completely ?
(without looping through and deleting each row and column)
The number of columns and rows of the new table is identical to the
number
of columns and rows of the old table being replaced. Is there a quick
way 
to do that?

Thanks,
Kavita
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replacing a table

[Kavita Raghunathan]

Thanks Pavel and Owen. This is very useful information.

Also how can we change a whole column at a time ?
In otherwords, the entire column needing to be changed would
involve looping through each entry and changing that value,
instead i want to substitute a whole column.

Thanks!
Kavita
- Original Message -
From: "Owen O'Neill" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Replacing a table


Run the sql
'delete from "tablename";'

if the table definition is different (different column names or data
types ) then you will need to drop the table and create a new one.
'drop table "tablename";'

http://www.sqlite.org/lang_createtable.html

if the table is huge you might get different performance depending on
whether your journal settings are to truncate or delete or pad etc.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
Sent: Thursday, October 15, 2009 3:51 PM
To: General Discussion of SQLite Database
Cc: Kelvin Xu
Subject: [sqlite] Replacing a table

Hi, 

Is there a way to use the sqlite wrappers to "replace" or delete a table
completely ?
(without looping through and deleting each row and column)
The number of columns and rows of the new table is identical to the
number
of columns and rows of the old table being replaced. Is there a quick
way 
to do that?

Thanks,
Kavita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replacing a table

[O'Neill, Owen]

Run the sql
'delete from "tablename";'

if the table definition is different (different column names or data
types ) then you will need to drop the table and create a new one.
'drop table "tablename";'

http://www.sqlite.org/lang_createtable.html

if the table is huge you might get different performance depending on
whether your journal settings are to truncate or delete or pad etc.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
Sent: Thursday, October 15, 2009 3:51 PM
To: General Discussion of SQLite Database
Cc: Kelvin Xu
Subject: [sqlite] Replacing a table

Hi, 

Is there a way to use the sqlite wrappers to "replace" or delete a table
completely ?
(without looping through and deleting each row and column)
The number of columns and rows of the new table is identical to the
number
of columns and rows of the old table being replaced. Is there a quick
way 
to do that?

Thanks,
Kavita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replacing a table

[Pavel Ivanov]

> Is there a quick way to do that?

DROP TABLE table1;
ALTER TABLE table2 RENAME TO table1;

And doesn't matter how many rows and columns have each of the tables.

Hope I've understood your question correctly.
Pavel

On Thu, Oct 15, 2009 at 10:51 AM, Kavita Raghunathan
 wrote:
> Hi,
>
> Is there a way to use the sqlite wrappers to "replace" or delete a table 
> completely ?
> (without looping through and deleting each row and column)
> The number of columns and rows of the new table is identical to the number
> of columns and rows of the old table being replaced. Is there a quick way
> to do that?
>
> Thanks,
> Kavita
> ___
> 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] Replacing a table

[Kavita Raghunathan]

Hi, 

Is there a way to use the sqlite wrappers to "replace" or delete a table 
completely ?
(without looping through and deleting each row and column)
The number of columns and rows of the new table is identical to the number
of columns and rows of the old table being replaced. Is there a quick way 
to do that?

Thanks,
Kavita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat optimization

[Olaf Schmidt]

"Pavel Ivanov"  schrieb im
Newsbeitrag
news:f3d9d2130910150647k5e28d8aan81d60fad8e71e...@mail.gmail.com...
> > Would the authors be kind to implement such optimization?
>
> I'm not author but I believe the answer to this question is No.
> Because this fix is good enough for you but may be not good for
> others. Your fix gets more memory than is really needed and it
> can be a problem for embedded devices.

But that would end up with only twice the allocated Bufferspace
than before (for the GroupConcat-String(s)) - and that only in the
worst-case.
Maybe a factor 1.5 (instead of the 2) would be a better
compromise between "wasted buffer-memory" and significantly
reduced realloc-calls in these typical "growing-append-space"
scenarios.


So, as long as sqlite3StrAccumAppend() is *not* used
"pretty much everywhere, blowing up *total* mem-usage
of a running SQLite-engine-instance by on average 25%",
I'd vote for the change... ;-)

Olaf Schmidt




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Conflict with SQLite ODBC Driver and MS Office 2007 ?

[Mohit Sindhwani]

Shane Harrelson wrote:
> I know of no conflicts.  I regularly test against SQL Server 2005,
> MySQL, OracleXE, and SQLite all via ODBC interfaces (specifically
> Christian Werner's ODBC interface for SQLite).  The various ODBC
> interfaces all play happily.
>   

...and numerous applications include SQLite built in - Firefox is one!

Cheers,
Mohit.
10/15/2009 | 10:31 PM.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Conflict with SQLite ODBC Driver and MS Office 2007 ?

[Shane Harrelson]

I know of no conflicts.  I regularly test against SQL Server 2005,
MySQL, OracleXE, and SQLite all via ODBC interfaces (specifically
Christian Werner's ODBC interface for SQLite).  The various ODBC
interfaces all play happily.

HTH
-Shane


On Wed, Oct 14, 2009 at 4:15 PM, Jack Ort  wrote:
> Hello!  Not sure where to ask this question, so I apologize if this is not
> appropriate.  I want to use SQLite as the basis for a new project where I
> work.  I'm new to SQLite - this would be my first use of it beyond some
> simple test applications.  Plan to use REBOL to develop a GUI frontend.  For
> reporting, I thought I might use MS Access called by REBOL code to provide
> canned reports against the SQLite database.  I believe I need to use the
> SQLite ODBC driver (http://www.ch-werner.de/sqliteodbc/) for Access to link
> to the SQLite db.
>
> Now my question: my IT Manager is witholding approval of using SQLite
> because he thinks he's heard of a conflict between the SQLite ODBC driver
> and the SQL Server 2005 "stuff" that gets installed as part of a MS Office
> 2007 installation.  (Some users have Office 2007 - I have Office 2003 so I
> cannot test.)
>
> I suspect someone meant to refer to a conflict with SQL Server Express
> instead of SQLite.  I cannot find anything in Google searches.
>
> Does anyone here know of any conflicts I should be concerned about?  This
> would be in a XP SP2 environment.
>
> Thanks in advance!
> -Jack
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cannot ATTACH a database that contains a VIEW

[Wolfgang Enzinger]

Pavel Ivanov  writes:

> Are you referencing "main" in your view explicitly?

Bingo, that's in fact what I did.

> If so then don't do it.

Followed your advice and it works now, thanks!

Looks like I was a bit *too* explicit here ... ;-)

Wolfgang

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exception writing to database from multiple processes

[Pavel Ivanov]

> However, when it is run
> inside an Application Pool with multiple worker processes, the database
> soon becomes locked and cannot be written to by any of the worker
> processes.

You mean your application hangs? None of workers can write to database
and nothing else happens in application? Nobody's reading database at
the same time, nobody connecting to database via command line tool,
nothing happens at all?

Pavel

On Thu, Oct 15, 2009 at 2:40 AM, David Carter  wrote:
> Hello,
>
>
>
> I am currently using the SQLite Amalgamation v3.6.19 from
> http://www.sqlite.org/download.html in an ISAPI Extension to write out
> usage statistics to an SQLite database.
>
>
>
> When the ISAPI extension is running inside an Application Pool with a
> single worker process, everything works fine.  However, when it is run
> inside an Application Pool with multiple worker processes, the database
> soon becomes locked and cannot be written to by any of the worker
> processes.
>
>
>
> Each worker process has a separate background thread which writes to the
> database every 5 seconds.  Each write is performed as a single
> transaction starting with "BEGIN IMMEDIATE".
>
>
>
> Any help you can provide would be greatly appreciated.
>
>
>
> Thanks,
>
>
>
> David
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat optimization

[Pavel Ivanov]

> Would the authors be kind to implement such optimization?

I'm not author but I believe the answer to this question is No.
Because this fix is good enough for you but may be not good for
others. Your fix gets more memory than is really needed and it can be
a problem for embedded devices.
But you're free to use your fixed version of SQLite or make your own
implementation of group_concat (by copying SQLite's code and applying
your changes), register it in your application and use it.

Pavel

On Thu, Oct 15, 2009 at 12:35 AM, ???   wrote:
>
>
> I make use of group_concat aggregate function and I found
> it very slow, especially when there are thousands of lines per group.
> This is because it reallocates memory on each processed row.
> I changed just one line in sqlite3StrAccumAppend():
>
>  szNew += N + 1
>
> to something like this:
>
>  do{ szNew = szNew*2 + 1; } while (szNew <= p->nChar + N);
>
> and now group_concat works fine.
> Would the authors be kind to implement such optimization?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tackling the Date Scan SQL

[Pavel Ivanov]

> Why do you expect 1988-01-04 et al to be excluded? These dates meet your 
> condition of falling between 09/01 and 04/01 - they should appear _somewhere_ 
> in the resultset.

I second that. Your question in the first place says about order of
records, but if you indeed for some reason want to exclude data at the
beginning of the first year you have to add to your WHERE clause
something like this:

AND (Year > (SELECT MIN(Year) FROM table_name) OR Month > start_month
OR Day >= start_day)


Pavel

On Wed, Oct 14, 2009 at 11:59 PM, Igor Tandetnik  wrote:
> Rick Ratchford wrote:
>> Tried that. It doesn't work.
>>
>> For example, when I used 09/01 as my start and 04/01 as my ending, what I
>> got back was:
>>
>> 1988  1  4
>> 1988  1  5
>> 1988  1  6
>> ...
>> ...
>>
>> Instead of:
>>
>> 1988 9  1
>> 1988 9  2
>
> Why do you expect 1988-01-04 et al to be excluded? These dates meet your 
> condition of falling between 09/01 and 04/01 - they should appear _somewhere_ 
> in the resultset.
>
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [DBD-SQLite] test DBD::SQLite 1.26_05 - foreign keys!

[Adam Kennedy]

Please note the following correction to the announcement.

Whining is also welcome. :)

Adam K

2009/10/15 Darren Duncan :
> Patches welcome.  Ideas welcome.  Testing welcome.  Whining to /dev/null.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys question

[Dan Kennedy]

On Oct 15, 2009, at 10:30 AM, Dennis Volodomanov wrote:

> Hello all,
>
> I'm implementing the new FOREIGN KEY support in our database and I  
> have this small problem/question.
>
> Before I had triggers to take care of maintaining deletion of data  
> that's not referenced by any records, but I can't seem to reproduce  
> the same behavior with just the foreign key commands. Maybe I'm  
> missing something.
>
> So, what I had was:
>
> CREATE TABLE TableA(ID INTEGER PRIMARY KEY, SomeData, DataID INTEGER);
> CREATE TABLE TableData(ID INTEGER PRIMARY KEY, Data UNIQUE);
>
> Where DataID in TableA is ID in TableData.
>
> I also had the following triggers:
>
> CREATE TRIGGER TriggerADelete AFTER DELETE ON TableA WHEN  
> OLD.DataID<>0
> BEGIN
>   DELETE FROM TableData WHERE TableData.ID=OLD.DataID AND OLD.DataID  
> NOT IN (SELECT DataID FROM TableA WHERE DataID=OLD.DataID LIMIT 1);
> END;
>
> CREATE TRIGGER TriggerAUpdate AFTER UPDATE ON TableA WHEN  
> OLD.DataID<>0
> BEGIN
>   DELETE FROM TableData WHERE TableData.ID=OLD.DataID AND OLD.DataID  
> NOT IN (SELECT DataID FROM TableA WHERE DataID=OLD.DataID LIMIT 1);
> END;
>
> That kept the TableData empty of any non-referenced values.
>
> What I've got now is (which doesn't work as I expected it to):
>
> CREATE TABLE TableA(ID INTEGER PRIMARY KEY, SomeData, DataID  
> INTEGER, FOREIGN KEY(DataID) REFERENCES TableData(ID) ON DELETE  
> CASCADE ON UPDATE CASCADE);
> CREATE TABLE TableData(ID INTEGER PRIMARY KEY, Data UNIQUE);
>
> Is there a way to reproduce the same without using triggers? If not,  
> I'm not sure what the advantage of using foreign keys is in my case...

I don't think the triggers you are using can be implemented using
foreign keys. Your triggers are basically reference counting (or
garbage collecting, whatever you want to call it) - "when the
number of references to a data item drops to zero, delete the
data item".

You could add a foreign key to this schema to prevent the DataID
column of TableA from being populated with an invalid id (one
that does not refer to any id of the TableData table).

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database connection timeout

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Andi wrote:
> MySQL has 8 hours connection timeout, is there a connection timeout after 
> open SQLite database ?

What makes you think SQLite has connection timeouts?  Hint: read the web
site to learn more about SQLite.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrW0NYACgkQmOOfHg372QQn+gCcD5GVDGIeFmVvJ+lQ9m54Oq2e
eB8AoI5o3LTguohK0VnHPS6xfHqoVyd2
=JbNs
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Database connection timeout

[Andi]

Hi all,

MySQL has 8 hours connection timeout, is there a connection timeout after open 
SQLite database ?

Andi




__ Information from ESET NOD32 Antivirus, version of virus signature 
database 4508 (20091014) __

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


__ Information from ESET NOD32 Antivirus, version of virus signature 
database 4508 (20091014) __

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com



__ Information from ESET NOD32 Antivirus, version of virus signature 
database 4508 (20091014) __

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Exception writing to database from multiple processes

[David Carter]

Hello, 

 

I am currently using the SQLite Amalgamation v3.6.19 from
http://www.sqlite.org/download.html in an ISAPI Extension to write out
usage statistics to an SQLite database.  

 

When the ISAPI extension is running inside an Application Pool with a
single worker process, everything works fine.  However, when it is run
inside an Application Pool with multiple worker processes, the database
soon becomes locked and cannot be written to by any of the worker
processes.  

 

Each worker process has a separate background thread which writes to the
database every 5 seconds.  Each write is performed as a single
transaction starting with "BEGIN IMMEDIATE".  

 

Any help you can provide would be greatly appreciated.

 

Thanks, 

 

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test DBD::SQLite 1.26_05 - foreign keys!

[Darren Duncan]

All,

I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a Perl DBI 
Driver) version 1.26_05 has been released on CPAN (by Adam Kennedy).

   http://search.cpan.org/~adamk/DBD-SQLite-1.26_05/

This developer release bundles the brand-new SQLite version 3.6.19, which adds 
support for enforcing SQL foreign keys.  See http://sqlite.org/foreignkeys.html 
for the details of the foreign key support that SQLite now has.

Also be sure to look at the section 
http://sqlite.org/foreignkeys.html#fk_enable 
, because you have to enable a pragma on each connect to use the foreign keys 
feature; it isn't yet on by default for backwards compatibility purposes.

As I imagine many of you have been pining away for SQLite to support this 
feature for a long while, you'll want to dig in right away.

TESTING NEEDED!

Please bash the hell out of the latest DBD::SQLite and report any outstanding
bugs on RT.  Test your dependent or compatible projects with it, which includes
any DBMS-wrapping or object persistence modules, and applications.

And especially try actually using foreign keys with SQLite.

As the official release announcement says:  "This release has been extensively 
tested (we still have 100% branch test coverage).  [The SQLite developers] 
consider this release to be production ready.  Nevertheless, testing can only 
prove the presence of bugs, not their absence.  So if you encounter problems, 
please let us know."

See also http://www.sqlite.org/changes.html for a list of everything else that 
changed in SQLite itself over the last few months.

If you want in to DBD::SQLite development, then join the following email/IRC
forums which MST created (the mailing list, I am administrating):

   http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite

   #dbd-sqlite on irc.perl.org

And the canonical version control is at:

   http://svn.ali.as/cpan/trunk/DBD-SQLite/

Patches welcome.  Ideas welcome.  Testing welcome.  Whining to /dev/null.

If you feel that a bug you find is in SQLite itself rather than the Perl DBI 
driver for it, the main users email forum for SQLite in general is at:

   http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

... where you can report it as an appropriate list post (the SQLite issue 
tracking system is no longer updateable by the public; posting in the list can 
cause an update there by a registered SQLite developer).

Please do not reply to me directly with your responses.  Instead send them to
the forums or file with RT as is appropriate.

Thank you. -- Darren Duncan

P.S.  DBD::SQLite has at least 1 known bug, also in version 1.25, with regard 
to 
full-text search (FTS3); there is an included new failing test, which currently 
is set to skip so the CPAN testers don't issue fails, but the issue behind it 
should hopefully be fixed before the next DBD::SQLite release.  We decided that 
shipping DBD::SQLite now with the skipping test was preferable to waiting for 
that fix so you could get the new foreign keys feature the soonest.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why FTS3 has the limitations it does

[John Crenshaw]

The severe limitations on FTS3 seemed odd to me, but I figured I could
live with them. Then I starting finding that various queries were giving
strange "out of context" errors with the MATCH operator, even though I
was following all the documented rules. As a result I started looking
deeply into what is going on with FTS3 and I found something that
bothers me.

 

These limitations are really completely arbitrary. They should be
removable.

 

You can only use a single index to query a table, after that everything
else has to be done with a scan of the results, fair enough. But with
FTS3, the match operator works ONLY when the match expression is
selected for the index. This means that if a query could allow a row to
be selected by either rowid, or a MATCH expression, you can have a
problem. If the rowid is selected for use as the index, the MATCH won't
be used as the index, and you get errors. Similarly, a query with two
MATCH expressions will only be able to use one as the index, so you get
errors from the second.

 

Now, the reason this is arbitrary is that the MATCH expressions not used
for the index are STILL given a chance to work at the function level. If
a MATCH function were implemented, these limitations would disappear.
Oddly enough, FTS3 doesn't expose a function for MATCH. Unfortunately,
there is a good reason. It turns out that, as currently designed,
testing an expression against a single known row requires a full table
scan for every test. Inside my match function I would know the rowid
(docid) for a record. While this can quickly look up the content, it is
impossible to look up segments by document id, so checking the match on
that row requires a lookup of all possible docids for the match
expression, and a full scan of those results. Clearly this makes a
function level match utter nonsense.

 

My first question is, why was FTS designed like this in the first place?
Surely this was clear during the design stage, when the design could
have been easily changed to accommodate the lookups required for a MATCH
function. Is there some compelling performance benefit? Something I
missed?

 

My second question is, can we expect this to change at some point? Just
adding the MATCH function would eliminate virtually every remaining FTS
limitation. All that is needed is the ability to lookup by a combination
of docid and term. Isn't a hash already built while creating a list of
terms for storage? What if that hash were stored, indexed by docid?

 

For now I've modified my code to always index on the MATCH expression,
if there is any. This at least eliminates the random errors, but does
nothing wonderful for performance.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users