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

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

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

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:

Re: [sqlite] Foreign keys question

> 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

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

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

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

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.

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

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). >> >

[sqlite] sqlite 3.6.19 missing FTS3?

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

Re: [sqlite] Foreign keys question

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

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

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

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

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

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

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

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

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

Re: [sqlite] regular expression search

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

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

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

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

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

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:

Re: [sqlite] regular expression search

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:

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

[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

Re: [sqlite] Detect nullable fields?

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

[sqlite] Detect nullable fields?

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

Re: [sqlite] Foreign keys question

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". >

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

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

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

-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

Re: [sqlite] Replacing a table

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

Re: [sqlite] Replacing a table

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

Re: [sqlite] Replacing a table

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:

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

Re: [sqlite] Replacing a table

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

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 -

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

Re: [sqlite] Replacing a table

> 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

[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

Re: [sqlite] group_concat optimization

"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

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

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

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

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

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

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

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?

Re: [sqlite] group_concat optimization

> 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

Re: [sqlite] Tackling the Date Scan SQL

> 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

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

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

Re: [sqlite] Foreign keys question

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

Re: [sqlite] SQLite Database connection timeout

-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

[sqlite] SQLite Database connection timeout

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.

[sqlite] Exception writing to database from multiple processes

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

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

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

[sqlite] Why FTS3 has the limitations it does

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