[sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.
Hi,
While working on the Perl DBD:SQLite driver, I found the following bug
in sqlite : the last_insert_rowid() method (or SQL function) returns the
constant value 10 after any insert into a fts5 virtual table. This bug
is new in fts5 : previous versions fts4 and fts3 returned the correct
rowid value.
Below is a transcript from the sqlite3 shell that illustrates the
problem (but the same bug also happens when using the API).
$ ./sqlite3
SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create virtual table f5 using fts5(c);
sqlite> insert into f5(c) values('foobar');
sqlite> select last_insert_rowid();
10
sqlite> select rowid, c from f5;
1|foobar
[run on a Windows7 machine]
Best regards, Laurent Dami
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to use fts5 ?
Hi there, I'm interested in testing the fts5 extension, already documented (https://www.sqlite.org/fts5.html) , but I didn't find any instructions on how to compile it. I found the sources in ext/fts5, but apparently there is a piece of yacc grammar that doesn't compile under yacc/bison. Did I miss some documentation ? Or is it not ready yet for use ? Thanks in advance, Laurent Dami
Re: [sqlite] Possible to get list of terms in the Full Text Index?
Le 02.02.2015 18:00, sqlite-users-requ...@sqlite.org a écrit : Message: 1 Date: Sun, 01 Feb 2015 23:28:33 +0200 From: Rael Bauer <supp...@bauerapps.com> To: sqlite-users@sqlite.org Subject: [sqlite] Possible to get list of terms in the Full Text Index? Message-ID: <54ce9a81.2010...@bauerapps.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Hi, Is it at all possible to get a listing of all terms in the Full Text Index and which rowid's they were found in? Yes it is possible, look at http://www.sqlite.org/fts3.html#fts4aux Laurent Dami ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] virtual tables (was : SQLite as a meta database)
Hi there, Since virtual tables were recenty mentioned in the thread "SQLite as a meta database" , I take this opportunity to announce that the lastest version of the DBD::SQLite driver for Perl now has support for virtual tables (see https://metacpan.org/pod/DBD::SQLite ). This means that you can implement virtual tables through Perl classes instead of writing C code, which (at least in my view) is much easier to write and to debug. Laurent Dami ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] new support for virtual tables written in Perl
Hi, This is to announce that next version of DBD::SQLite (the Perl driver for accessing SQLite databases) will include support for virtual tables written in Perl. This is easier than writing virtual table extensions in C code, so I hope to see creative uses of this feature from fellow Perl programmers. To anybody interested : the dev release is at https://metacpan.org/pod/release/ISHIGAKI/DBD-SQLite-1.43_05/lib/DBD/SQLite.pm and the source repository is at https://github.com/DBD-SQLite/DBD-SQLite . The distribution includes two builtin virtual tables : * FileContent (https://metacpan.org/pod/release/ISHIGAKI/DBD-SQLite-1.43_05/lib/DBD/SQLite/VirtualTable/FileContent.pm) : a variation on test_fs.c from the sqlite source, useful for using FTS4 indices on content stored in the filesystem rather than in sqlite tables. * PerlData (https://metacpan.org/pod/release/ISHIGAKI/DBD-SQLite-1.43_05/lib/DBD/SQLite/VirtualTable/PerlData.pm) : a variation and generalization on test_intarray.c from the sqlite source. Various kinds of Perl datastructures can be viewed as SQLite tables, both as input and as output, and this can be an easy way to communicate between a Perl program and a sqlite database, or for joining data from various sources. The stable release should be published in next September or October. Comments / remarks are welcome. Cheers, Laurent Dami ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] documentation bugs
* In http://www.sqlite.org/src/wiki?name=Bug+Reports , the href to the bug list is http://www.sqlite.org/src/report; should be http://www.sqlite.org/src/reportlist * In vtab.hml: the description of sqlite3_index_info is not up to date (missing estimatedRows) * In compile.html : the compile option SQLITE_ENABLE_FTS4_UNICODE61 is missing ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] missing doc for FTS feature "^"
Hi, I accidentally found the following interesting feature in the Change history : 2011-11-01 (3.7.9) If a search token (on the right-hand side of the MATCH operator) in FTS4 begins with "^" then that token must be the first in its field of the document. ** Potentially Incompatible Change ** But this is not mentioned in the fts3.html document, so most users probably never heard about this feature. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] a few documentation bugs
Hi all, Thanks for sqlite. I noticed a couple of documentation bugs. In compile.html: -DSQLITE_DISABLE_FTS3_UNICODE is documented but obsolete -DSQLITE_ENABLE_FTS4_UNICODE61 is not documented In vtab.html - typo "must should" in "The xCreate must should return SQLITE_OK if ..." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] using SQLite with mod_perl
>-Message d'origine- >De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] De la part de P Kishor >Envoyé : samedi, 9. octobre 2010 17:10 >I am just trying to solve the above. It may well be that sqlite and >mod_perl may not be good companions (in which case, I am up a >creek without a db, and will have to look for an alternative storage >solution). > Sqlite and mod_perl are definitely good companions, as long as one doesn't mess up with transactions. Upon startup, Apache starts a collection of servers, which may be either processes or threads, depending on the MPM (multi-process module, see http://httpd.apache.org/docs/2.2/mpm.html ); but this doesn't matter much. Under mod_perl, each of those servers has an embedded perl interpreter, so indeed they work concurrently. Each server has a loop, listening for requests, and then producing the answer ... but the server may hold data that is persistent between requests, and within such data it is often a very good idea to keep a persistent connection to the database, avoiding the cost of opening/closing the connection at each request. Therefore we may have a collection of concurrent Apache/mod_perl servers, where each server has an open connection to the database, and that is not a problem. The important point, however, is to properly open and close a transaction whenever updating the database, using the begin_work(), commit() and rollback() methods (see L). This sequence should ALWAYS happen within a single http request, i.e. do not start the transaction in one request, expecting the next request to close that transaction : this will lock your database, because the next request might never arrive, or it might be served by a different process or thread than the one that served the initial request. Good luck, Laurent Dami ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
