Re: [sqlite] why is count of rows in LEFT JOIN more than the rows in the left table

On 02/20/2012 10:16 PM, Puneet Kishor wrote: Could someone please shed some light on this mystery for me? 1. SELECT Count(*) FROM a; 329686 2. SELECT Count(*) FROM a WHERE a.col IS NULL; 3 3. SELECT Count(*) FROM b;

Re: [sqlite] undefined reference to `sqlite3_db_release_memory'

On 02/17/2012 03:10 PM, bhaskarReddy wrote: Hi Friends, While i am trying to use `sqlite3_db_release_memory'. I am getting error and warning as, createTable.c: In function ‘db_create_table’: createTable.c:98: warning: implicit declaration of function ‘sqlite3_db_release_memory’

Re: [sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT

On 02/10/2012 11:06 PM, Ralf Junker wrote: On 07.02.2012 12:28, Ralf Junker wrote: The new feature to insert multiple rows of VALUES in a single INSERT http://www.sqlite.org/src/info/eb3b6a0ceb gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT. Has the team seen

Re: [sqlite] Diacritics handling in FTS with a custom tokenizer

On 02/09/2012 12:49 AM, George Ionescu wrote: Hello Dan, yes, I thought of that. But wouldn't this break the snippet's function? If the tokenizer will return text without diacritics, wouldn't the snippet return the same? Should be Ok. Snippet should be based on the original content.

Re: [sqlite] Diacritics handling in FTS with a custom tokenizer

On 02/08/2012 11:34 PM, George Ionescu wrote: Hello all, I would like to know how are diacritics handled in FTS, specifically if I can index text with diacritics and search for terms without them. For example, given the queries CREATE VIRTUAL TABLE fts_pages USING fts4(tokenize=snowball

Re: [sqlite] Connect As400/Iseries to SQLite .db

On 02/08/2012 10:35 PM, Tim Leland wrote: Hey all, Im trying to find a way to connect an as400 to a sqlite database and import the data into db2/physical files. Does anyone have any experience with something similar to this. I found this site and not sure if this will do what Im looking for.

Re: [sqlite] Efficiency of concatenation on UPDATE

On 02/06/2012 05:54 AM, Hamish Allan wrote: On 5 February 2012 21:20, Roger Binns wrote: SQLite doesn't work on rows - it works on pages. A row will be contained within one or more pages. FWIW, I inspected the source for OP_Concat and found that it can sometimes

Re: [sqlite] Error in FTS documentation?

On 02/05/2012 09:07 AM, Nils D. wrote: Maybe this is old, but there is - as far as I understand the descriptions and the results - an error in the fts documentation. In section 4.3 http://www.sqlite.org/fts3.html#matchinfo in the example it says: "-- The format string for this query is "nl".

Re: [sqlite] "database is locked" on WAL with large number of reader connections

On 01/26/2012 09:05 AM, Erik Fears wrote: I have a writer, but the thread is not writing during this test. This is built by me. This is single process, one connection per thread, 100 threads. The threads are being created/destroyed often, though, and each time one is created it creates a new

Re: [sqlite] Found it

On 01/25/2012 12:15 AM, John Elrick wrote: On Tue, Jan 24, 2012 at 12:06 PM, Richard Hipp wrote: On Tue, Jan 24, 2012 at 11:59 AM, John Elrick

Re: [sqlite] Another 3.7.10 question

On 01/21/2012 02:00 PM, Alexandr Němec wrote: Dear all, we have another question regarding the 3.7.10 version. We have a database with cca 10 tables. After creating a new database in 3.7.10 and filling each table with about 10 - 100 data rows, we noticed that the size of the database is 2x -

Re: [sqlite] 3.7.10

On 01/20/2012 06:16 PM, Alexandr Němec wrote: Dear all, we have two short questions regarding the new 3.7.10 SQLite release. 1. We read about the new "powersafe overwrite" feature in 3.7.10. We would like to go on (for a while) without this. If we set SQLITE_POWERSAFE_OVERWRITE to 0 in the

Re: [sqlite] Memory exception in sqlite3DbFree when called from sqlite3VdbeMemReleaseExternal as SQLITE_DYNAMIC

On 01/19/2012 04:20 AM, Donald Bryant-Rich wrote: In the function sqlite3VdbeMemReleaseExternal calls sqlite3DbFree which is passed into the function as the xDel component of the Mem argument to the function (p): if( p->flags_Dyn&& p->xDel ){ assert( (p->flags_RowSet)==0 );

Re: [sqlite] R*Tree insert error ...

On 01/19/2012 12:42 AM, g...@novadsp.com wrote: On 18/01/2012 16:41, Richard Hipp wrote: What does sqlite3_errmsg() say? 'SQL logic error or missing database' Are you using sqlite3_prepare() or sqlite3_prepare_v2()? If the former, try switching to prepare_v2() then checking the error

Re: [sqlite] makefile for c

On 01/16/2012 04:17 AM, Bill McCormick wrote: Tim Streater wrote, On 1/15/2012 3:00 PM: On 15 Jan 2012 at 20:44, Bill McCormick wrote: What is the problem with the shared lib stuff? Thanks!! Black, Michael (IS) wrote, On 1/15/2012 2:27 PM: A simple one -- and please

Re: [sqlite] savepoint

On 01/16/2012 03:31 PM, Csaba Jeney wrote: Is there any way to query the valid savepoints? At least their names? No way to do that at present. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Some pieces of the puzzle are coming together

On 01/14/2012 04:49 AM, John Elrick wrote: So, obviously the problem is that _malloc is being called a much larger number of times by 3.7.9 in this particular circumstance -- roughly 70 times as often. _memcpy demonstrates roughly 6 times as many calls I ran a test with a much smaller

Re: [sqlite] Resetting virtual table

On 01/13/2012 03:31 PM, Max Vlasov wrote: Hi, As I see sqlite can accept the schema of a virtual table (Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if I want to refresh not only data, but schema also, is it possible? For example, I already did a query for my virtual

Re: [sqlite] Performing Backup via TCL, receiving error

On 01/04/2012 08:53 PM, Tilsley, Jerry M. wrote: I did manage to get the new version installed on the system, but the issue persists. But I did create a new database file under the 3.7.9 version and can back this up with no problem, but the database files under the 3.6.4 version crash. How

Re: [sqlite] sqlite3_bind_text(): unable to insert "const char*" data into the database.

On 01/04/2012 05:02 PM, Phong Cao wrote: Hello everybody, I am trying to develop a small music manager using Gtkmm, GStreamermm& SQLite3 in Linux (Fedora 16 - 64bit). It is able to read metadata (track, title, artist, album, etc...) of all the files using GStreamermm& inserts these metadata

Re: [sqlite] Performing Backup via TCL, receiving error

On 01/04/2012 06:55 AM, Tilsley, Jerry M. wrote: All, I am attempting to generate a backup script written in TCL that will backup all the databases in a given directory. These databases were all created with SQLite version 3.6.4. I have the tea package for version 3.7.9 which is what I am

Re: [sqlite] library routine called out of sequence

On 12/23/2011 09:06 PM, Sreekumar TP wrote: Hi, yes, I know. I have a multithreaded app. One db conection.I will try to reproduce the problem with a simple app. However, the difference between the working and non working case is that in the working case statements are prepared each time. In

Re: [sqlite] what is the unit of SQLITE_STATUS_MEMORY_USED?

On 12/17/2011 06:16 AM, smallboat wrote: Hi, I am using sqlite_status = sqlite3_status(SQLITE_STATUS_MEMORY_USED,,, resetFlag); to get the memory used by sqlite. What is the memory unit for pCurrent (the max sqlite memory used)? Is it in bytes, bit or something else? Bytes.

Re: [sqlite] Collation with concatenation

On 12/15/2011 11:59 PM, Jeff Matthews wrote: This is in the documentation: CREATE TABLE t1( x INTEGER PRIMARY KEY, a, /* collating sequence BINARY */ b COLLATE BINARY, /* collating sequence BINARY */ c COLLATE RTRIM, /* collating sequence RTRIM */

Re: [sqlite] Fts */or. Inconsistencies

On 12/13/2011 02:29 AM, Ephraim Stevens wrote: I'm using a custom tokenizer in each scenario (yes it works and the proof is enclosed). In the first dataset, the data was tokenized such that any alphanumeric character qualifies as part of a token. In the second dataset, the data was tokenized

Re: [sqlite] Error: disk I/O error while creating an index

On 12/10/2011 06:01 AM, Tal Tabakman wrote: Hi, thanks for the reply. so, I don't have TMPDIR defined in my env. what is the behaviour of sqlite in such cases ? is there a default ? By default it will try /var/tmp, then /usr/tmp, then /tmp. SQLite creates temporary files there that it uses

Re: [sqlite] Error: disk I/O error while creating an index

On 12/09/2011 04:02 PM, Tal Tabakman wrote: Hi Guys, I have an SQLITE database of 1.5 million rows in a single table each raw looks like: 149|25|439198507|-1|0|1|44954|24|17|31|9|9|-1|-1| now, from the sqlite command line interface I am creating an on a sincle integer column CREATE

Re: [sqlite] Cache design using sqlite3...

On 12/07/2011 12:25 PM, Dan Kennedy wrote: On 12/07/2011 01:35 AM, Alejandro Martínez wrote: I'm trying to use sqlite3 as a cache layer for queries on other database. Actually, replace an existing layer cache which is very adhoc and propietary (and that sucks), and i'd like to replace

Re: [sqlite] Cache design using sqlite3...

On 12/07/2011 01:35 AM, Alejandro Martínez wrote: I'm trying to use sqlite3 as a cache layer for queries on other database. Actually, replace an existing layer cache which is very adhoc and propietary (and that sucks), and i'd like to replace it with something peer reviewed / open source, etc

Re: [sqlite] Unexplained minor regression (bug) 3.7.8 up

On 12/06/2011 03:28 PM, Max Vlasov wrote: Hi, Noticed a strange regression after 3.7.7 (in 3.7.8 and inherited in 3.7.9) I have a Russian morphology database and different queries working with it. I narrowed it to the following case and populated with a couple of English words (just to make

Re: [sqlite] mandatory locks versus advisory locks

On 11/30/2011 06:55 AM, Andy Spencer wrote: I have seen documentation that SQLite uses POSIX advisory locks to implement locking on Unix. I have an issue in that attempts to operate on a sqlite database on a Linux file system that has setgid bit (set group ID on execution) set on the database

Re: [sqlite] Convert data to binary

On 11/26/2011 05:02 AM, Nico Williams wrote: You can CAST TEXT to BLOB, and you can use x'' for literal BLOBs. You can. The result of which is a blob that corresponds to either the utf-8 or utf-16 (big or little endian) encoding of the text, depending on the encoding used by the database file.

Re: [sqlite] Transactions for SELECT

, of any type, in an explicit transaction. On Tue, Nov 22, 2011 at 11:41 AM, Dan Kennedy<danielk1...@gmail.com> wrote: On 11/22/2011 04:34 PM, Baruch Burstein wrote: Do transactions speed up SELECT statements? They can a bit. If you put 10 SELECT statements in a transaction SQLite on

Re: [sqlite] Transactions for SELECT

On 11/22/2011 04:34 PM, Baruch Burstein wrote: Do transactions speed up SELECT statements? They can a bit. If you put 10 SELECT statements in a transaction SQLite only has to lock and unlock the database file once. If you run them outside of a transaction the db is locked and unlocked 10

Re: [sqlite] disk I/O error

On 11/22/2011 04:14 PM, Tal Tabakman wrote: Thanks for the reply, this thing is that I get this while doing a select operation, now, I know that I get a lot of results from this select, but I am not writing anything to my DB explicitly. what am I missing here ? Some SELECT need to write to

Re: [sqlite] SQLite: Database or disk full

On 11/22/2011 09:48 AM, gavyas wrote: I checked the code again and debugged it. The code gave error when I am passing the querry "SELECT * FROM households ORDER BY zone_id LIMIT 1 OFFSET 0" but it ran successfully for LIMIT 5000. I dont understand if there is an upper limit on LIMIT or

Re: [sqlite] Proper way to change temp directory

On 11/20/2011 04:00 PM, Yang Zhang wrote: Out of curiosity, what's the proper way to change the temp directory (say, to avoid "Error: database or disk full" errors on vacuum, which I ran into)? temp_store_directory has been working for me but it's deprecated and may be elided from builds. Is

Re: [sqlite] Release 3.7.8 CREATE INDEX slower

On 11/17/2011 03:22 AM, Faught, Bill wrote: I have a table of 3M rows. I add all the rows to the table, then call CREATE INDEX. Release 3.7.8 is about 40% slower than the previous release doing the CREATE INDEX. Has anyone else experienced this? 3.7.8 builds indexes differently. It uses a

Re: [sqlite] SQLITE_FCNTL_CHUNK_SIZE + wal files

On 11/11/2011 01:01 AM, George Pee wrote: Is there a way to make the wal files honor the chunk size specified by sqlite3_file_control and SQLITE_FCNTL_CHUNK_SIZE ? Not currently. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Progress callback and nested queries

On 11/03/2011 01:11 AM, Duquette, William H (318K) wrote: I'm pretty sure I know the answer to this. Sqlite3 allows you to define a "progress" callback, which will be called every so many byte-code instructions during a long-running query, so that you can update a progress bar or like that.

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

On 11/02/2011 08:13 PM, Black, Michael (IS) wrote: Maybe my memory is fading but this is the first time I've heard anybody say the wrapping a BEBIN around a SELECT was needed. I'd swear it was always said it wasn't ever needed. From the docs http://www.sqlite.org/lang_transaction.html

Re: [sqlite] Disk I/O Error

On 11/02/2011 12:37 AM, Korey Calmettes wrote: I don't think the file is truncated. What is the size of the file on disk? Interesting stuff here when I run pragma integrity_check: /data # sqlite3 test.db "pragma integrity_check;" *** in database main *** Page 480: unable to get the page.

Re: [sqlite] [Regression?] FTS function crashes since 3.7.7

On 10/29/2011 03:00 PM, Alexandre Courbot wrote: Hi everybody, While testing some database upgrade function of mine, I ran into a segmentation fault. I started looking at the possible cause in my code, but I soon realized the crash would only happen using SQLite 3.7.7 or 3.7.8. If I use 3.7.6

Re: [sqlite] Error messages from user-defined functions calling sqlite3_result_error() requires sqlite3_finalize?

On 10/27/2011 07:12 AM, Peter Aronson wrote: This may be a known thing, but I can't find anything on it on-line. I finally figured out a solution by examining the code to shell.c. OK, I have a user-defined function in an extension that calls sqlite3_result_error() when an out-of-bounds

Re: [sqlite] Virtual Tables and tcl / apsw

On 10/25/2011 04:28 PM, Alexey Pechnikov wrote: 2011/10/25 Dan Kennedy<danielk1...@gmail.com>: Not possible. The Tcl interface has no bindings for either the virtual table or VFS interfaces. But why? Is there any technical/ideological problems? None that are insurmountable, I would

Re: [sqlite] Virtual Tables and tcl / apsw

On 10/25/2011 02:31 PM, sqlite-us...@h-rd.org wrote: Hi, I have some questions on virtual tables and tcl compared to perl and python/apsw. As I understand you can build your own virtual table implementation with apsw (and also with perl). Is this also possible with tclsqlite? Any pointers

Re: [sqlite] Problem with FTS4 - Floating point error.

On 10/25/2011 12:49 PM, Mohd Radzi Ibrahim wrote: On 18-Oct-2011, at 6:52 PM, Dan Kennedy wrote: On 10/18/2011 05:02 PM, Mohd Radzi Ibrahim wrote: Hi, This is my table schema: CREATE VIRTUAL TABLE LocationFTS using FTS4 ( name text, address text, email text

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

On 10/24/2011 09:20 PM, Bo Peng wrote: Other than using a SSD to speed up random access, I hope a VACUUM operation would copy tables one by one so content of the tables would not scatter around the whole database. If this is the case, disk caching should work much better after VACUUM... fingers

Re: [sqlite] How is the amalgamation produced?

On 10/24/2011 08:32 PM, Baruch Burstein wrote: I found what seem to be two options for producing an amalgamation: 1. "./configure" and "make sqlite3.c" (something like that, it is not in front of me at the moment, but I have done this and know it works) 2. There is a Tcl script in the tool

Re: [sqlite] Problem with FTS4 - Floating point error.

nTC) Line 52634 + 0x2a bytesC Does anybody know what's going on here? Can you send me the database by email? Not via the list, as it will strip the attachment. Dan Kennedy. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:

Re: [sqlite] Very slow processing of some SELECT statements

On 10/18/2011 03:17 AM, Nick Gammon wrote: On 17/10/2011, at 9:55 PM, Dan Kennedy wrote: Did you download the binary from the website or build it yourself? If the latter, which compiler are you using? And what level of optimization is enabled? I initially observed the problem with version

Re: [sqlite] Very slow processing of some SELECT statements

On 10/17/2011 04:33 PM, Nick Gammon wrote: Hello, Running under Windows XP, using sqlite3.exe version: 3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177 ... Trying under Mac OS/X (Lion) does not appear to exhibit this problem. No problem with that version on Linux either.

Re: [sqlite] sqlite3 query really slow with version > 3.7.5

On 10/17/2011 11:05 AM, Owen Kaluza wrote: Hi, After upgrading my OS I noticed a huge delay loading my application, I narrowed it down to my system libsqlite3 being upgraded from 3.7.4 to 3.7.7 I did some quick testing with different versions and it seems the change comes about between 3.7.5

Re: [sqlite] Changing the data type of an existing table

On 10/16/2011 02:37 PM, Frank Missel wrote: When I first learned about SQLite I never like the relaxed handling of data where column data types from the schema are not enforced strictly on the actual data in the tables. This felt instinctively wrong as it is very contrary to other databases and

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 11:23 PM, Simon Slavin wrote: On 14 Oct 2011, at 5:12pm, Fabian wrote: Is this the expected output? If so, ANALYZE was to blame. The query containing 'ORDER BY rowid DESC' is still slower than the one which doesn't specify any order, but the results are closer to eachother now.

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 10:13 PM, Fabian wrote: 2011/10/14 Dan Kennedy<danielk1...@gmail.com> Good question. Can you enter the following commands into the shell tool and post the complete output (no "QUERY PLAN" this time): .version .schema .explain EXPLAIN SELECT * FROM tabl

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 09:34 PM, Fabian wrote: 2011/10/14 Dan Kennedy<danielk1...@gmail.com> If SQLite cannot use an index to for an ORDER BY in a SELECT query and has to do an external sort, the EXPLAIN QUERY PLAN output will have something like this in it: 0|0|0|USE TEMP B-TREE FOR

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 07:40 PM, Simon Slavin wrote: On 14 Oct 2011, at 1:36pm, David Bicking wrote: On 10/14/2011 06:39 AM, Fabian wrote: When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower (10 times) when I add an ORDER BY clause,

Re: [sqlite] Can DBI supply a more specific foreign key mismatch error?

On 10/14/2011 01:29 AM, fe...@crowfix.com wrote: I'm working on a project which generates tables from a config file, and it seems to be happy on a Mac OSX running 3.4.0, but Linux running 3.7.8 complains about an insert with the unhelpful message (foreign key mismatch) It means there is

Re: [sqlite] Malformed database error when using FTS3/4

On 10/13/2011 10:45 PM, Wendland, Michael wrote: I've encountered an error running 3.7.8 and 3.7.7.1 that seems rather strange. CREATE VIRTUAL TABLE fts USING fts3( tags TEXT); INSERT INTO fts (tags) VALUES ('tag1'); SELECT * FROM fts WHERE tags MATCH 'tag1'; Now close the database and reopen

Re: [sqlite] FTS4: OFFSET Performance

On 10/12/2011 09:39 PM, Fabian wrote: 2011/10/12 Dan Kennedy<danielk1...@gmail.com> Can we see the output of EXPLAIN for this query? Without selecting table2.data2: 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows) Including selecting table2.data2: 0 0 0

Re: [sqlite] FTS4: OFFSET Performance

On 10/12/2011 04:36 PM, Fabian wrote: I have two tables, one normal table (table1), and one FTS4 table (table2). The ROWID's between the tables are in sync. I often run queries on table1, but every row returned must also include a value from table2 (data2), so I create a query like this: SELECT

Re: [sqlite] Wrong default locking style on NFS server not supporting locking via fcntl()

On 10/10/2011 08:37 PM, Tobias Mueller wrote: Hello, I'm trying to compile SQLite on a GNU/Linux system for use in an NFS environment. The NFS server doesn't support locks via fcntl(), i.e.: access("/homedir//.mozilla/firefox/51jxlyk4.default/places.sqlite", F_OK) = 0

Re: [sqlite] Meaning of sqlite3_wal_checkpoint_v2 parameters

On 10/06/2011 02:29 PM, David Barrett wrote: Hi! Can you help me understand more exactly what the output parameters are from sqlite3_wal_checkpoint_v2()? Specifically: 1) What is the relationship between pnLog and pnCkpt: is pnLog>=pnCkpt always true? Yes. Always true. 2) Under what

Re: [sqlite] Crashes in sqlite3RunParser

On 10/01/2011 07:20 PM, Matthew Wild wrote: Hi, We support SQLite for data storage in Prosody, an XMPP server. Recently someone reported a number of crashes happening in SQLite on their server: http://prosody.im/bugs/269 The first 2 crashes both happen on the same line:

Re: [sqlite] WAL checkpointing

On 09/29/2011 05:20 PM, Mikael wrote: 2011/9/29 Simon Slavin<slav...@bigfraud.org> On 29 Sep 2011, at 10:38am, Dan Kennedy wrote: On 09/29/2011 03:17 PM, Jaco Breitenbach wrote: In the WAL documentation (http://www.sqlite.org/wal.html) it is hinted that the checkpoint operation,

Re: [sqlite] WAL checkpointing

On 09/29/2011 03:17 PM, Jaco Breitenbach wrote: Dear all, In the WAL documentation (http://www.sqlite.org/wal.html) it is hinted that the checkpoint operation, which is normally in the same thread if execution as the database commit, could be moved to a separate thread or process. If I were to

Re: [sqlite] LEFT JOIN optimization

On 09/23/2011 04:01 AM, Mira Suk wrote: EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106) selectidorderfromdetail 000SCAN

Re: [sqlite] Permissions issue with SQLite

On 09/23/2011 03:09 AM, Magnus Thor Torfason wrote: On 9/22/2011 10:25, Dan Kennedy wrote: For new versions, new db files are created with the permissions specified by compilation option SQLITE_DEFAULT_FILE_PERMISSIONS. Subject to umask of course. http://www.sqlite.org/compile.html

Re: [sqlite] Permissions issue with SQLite

On 09/22/2011 09:20 PM, Stephan Beal wrote: On Thu, Sep 22, 2011 at 4:16 PM, Magnus Thor Torfason< zulutime@gmail.com> wrote: SQLite version 3.3.6 Just to preempt the inevitable request to try it on a current version: this is reproducible on 3.7.2 (Ubuntu 10.10). For new versions,

Re: [sqlite] SQLITE issues - IPAD development

On 09/22/2011 01:30 AM, ecky wrote: Howdy I have a SQLITE database I created using the firefox plugin (SQLITE manager). I'm using that database in my IPAD app and I can read and write to it. However... I'm trying to debug some issue with my app so I copy the database file off my IPAD back

Re: [sqlite] Disk I/O Error on Ext3/write-back/barrier FS.

On 09/20/2011 08:58 PM, Berthier, Emmanuel wrote: From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Can you post the hex of the first 28 bytes of the journal file? 000 d5d9 f905 a120 d763 0400 a15c eae6 010 6300

Re: [sqlite] Disk I/O Error on Ext3/write-back/barrier FS.

On 09/20/2011 03:43 PM, Berthier, Emmanuel wrote: Hi, I'm using Sqlite 3.6.22 Froyo Branch on Android 2.3.4 distribution on a handset with eMMC storage memory. In case of power-cut (battery removal or kernel panic), we encounter some rare but critical errors during database rollback sequence:

Re: [sqlite] OMIT_VIRTUALTABLE

On 09/14/2011 03:38 PM, Baruch Burstein wrote: When using OMIT_VIRTUALTABLE, I get a warning at line 11688 (3.7.7.1 amalgamation) that sqlite3GetVTable is used but never defined. If I add #ifndef SQLITE_OMIT_VIRTUALTABLE #endif around it, I get an error at line 81609, because this function is

Re: [sqlite] Strange performance problem

On 09/06/2011 12:04 AM, Stephan Beal wrote: On Mon, Sep 5, 2011 at 6:59 PM, Dan Kennedy<danielk1...@gmail.com> wrote: You could get the same effect by wrapping your loop in a BEGIN/COMMIT block. Out of curiosity: would a BEGIN/ROLLBACK be equivalent for this case (where only S

Re: [sqlite] Strange performance problem

On 09/05/2011 10:47 PM, Rado Rado wrote: I'm running simple prepared SELECT statement in loop ( about 3000 times ). It is something like "SELECT value FROM t WHERE t_id=? AND name=?". For most calls the row does not exist, step() returns SQLITE_DONE so I call reset after that(). The loop takes

Re: [sqlite] Speeding up index creation

On 09/02/2011 07:32 PM, Filip Navara wrote: On Fri, Sep 2, 2011 at 11:04 AM, Filip Navara wrote: *snip* The time to create an index on my 266 Mb experimental database is more than 9 minutes. *snip* I erroneously measured the time with DEBUG build, so I've rerun the

Re: [sqlite] Speeding up index creation

On 09/02/2011 09:24 PM, Simon Slavin wrote: On 2 Sep 2011, at 10:04am, Filip Navara wrote: The time to create an index on my 266 Mb experimental database is more than 9 minutes. The database is available at http://www.emclient.com/temp/mail_index.zip and the command I use to create the index

Re: [sqlite] Clarification about Triggers

On 08/31/2011 06:34 PM, Black, Michael (IS) wrote: Doohyes "we" missed that. But shouldn't new.rowid be undefined then rather than return -1? Much like old.rowid is undefined? That might have helped "us" in recognizing "our" mistake. Fair enough. Sounded pompous. I say "we" because I

Re: [sqlite] Clarification about Triggers

On 08/30/2011 10:48 PM, Black, Michael (IS) wrote: I found that if you used the default rowid it always gave -1 for the value. That's why I put in it's own key. I don't understand why this doesn't work...perhaps somebody can point out the error here...new.rowid contains -1. I would think that

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

On 08/20/2011 12:50 AM, Duquette, William H (318K) wrote: > > On 8/19/11 10:44 AM, "Boris Kolpackov" wrote: > >> Hi William, >> >> "Duquette, William H (318K)" writes: >> >>> On 8/19/11 10:18 AM, "Boris Kolpackov"

Re: [sqlite] fts3 table name cannot be the same as column name

On 08/17/2011 10:10 PM, George Brink wrote: > I think this is a bug... If you trying to create FTS3/FTS4 table, make > sure that column name in FTS table is not the same as the table name. > > C:\Projects>sqlite3 aaa > SQLite version 3.7.7.1 2011-06-28 17:39:05 > Enter ".help" for instructions >

Re: [sqlite] tcl incrblob interface

On 08/10/2011 11:24 PM, Victor Mayevski wrote: > Hello, > > I am trying to learn how to use the "incrblob" command in the Tcl > interface and I can't get it to work. I create a one column table "t", > insert one empty value into it, than do "db incrblob t a 1", which > works fine, I get a file

Re: [sqlite] Possible bug in FTS3 or Parametrization?

On 08/05/2011 05:44 PM, Martin Gill wrote: > On 5 August 2011 10:55, Dan Kennedy<danielk1...@gmail.com> wrote: >> >> On 08/05/2011 02:58 PM, Martin Gill wrote: >>> >>> CREATE VIRTUAL TABLE full_text USING FTS3 (id,text) >>> INSERT INTO fu

Re: [sqlite] Possible bug in FTS3 or Parametrization?

On 08/05/2011 02:58 PM, Martin Gill wrote: > Hi all > > I have discovered what may be a bug in either SQLLite or System.Data.SQLite > (v 1.0.66.0 using SQLite 3.6.23.1). I certainly don't understand what's > happening and I'd apprecite it if someone could help me out; perhaps I'm > missing

[sqlite] Fwd: Re: assert() in debug build when running query

Original Message Subject: Re: assert() in debug build when running query Date: Wed, 03 Aug 2011 15:00:46 +0200 From: Jens Miltner To: General Discussion of SQLite Database Sorry, should have mentioned that this happens with version

Re: [sqlite] Deep copy of 2 sqlite3*?

On 08/03/2011 09:20 AM, Nikki Tan wrote: > Hi sqlite-users! > It's just that I'm writing a copy constructor of my own mini sqlite > wrapper, and I ended up having to do a deep copy of a sqlite3* points to a > SQLite connection. And I'm just wondering is it okay that I just do it with >

Re: [sqlite] LevelDB benchmark

On 07/30/2011 12:19 PM, Dan Kennedy wrote: > On 07/30/2011 04:45 AM, Gabor Cselle wrote: >> Just a heads-up that we just posted an update to the benchmarks: >> - No more superfluous index on the primary key >> - WAL turned on with auto-checkpointing every

Re: [sqlite] LevelDB benchmark

On 07/30/2011 04:45 AM, Gabor Cselle wrote: > Just a heads-up that we just posted an update to the benchmarks: > - No more superfluous index on the primary key > - WAL turned on with auto-checkpointing every 4096 pages > > http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html > > A diff of

Re: [sqlite] [FTS3] Understanding the Flow of data through the tokenizer

On 07/24/2011 08:16 PM, Abhinav Upadhyay wrote: > Hi, > > I am trying to write my own custom tokenizer to filter stopwords apart > from doing normalization and stemming. I have gone through the > comments in fts3_tokenizer.h and also read the implementation of the > simple tokenizer. While overall

Re: [sqlite] FTS3: synonyms dictionary and tokens length

On 07/15/2011 01:10 PM, Alexey Pechnikov wrote: > 2011/7/15 Dan Kennedy<danielk1...@gmail.com>: >> I think you could just have the tokenizer return "dbms" whenever >> it sees "sqlite" in the input. > > But queries to original text will not work:

Re: [sqlite] FTS3: synonyms dictionary and tokens length

On 07/15/2011 03:28 AM, Alexey Pechnikov wrote: > I want to add the table CREATE TABLE ext_fts_synonyms(word text not > null unique, synonym text not null); insert into > ext_fts_synonyms('sqlite','sqlite dbms'); > > And replace in tokenizer the term 'sqlite' to 2 terms 'sqlite' and > 'dbms' for

Re: [sqlite] caveat on blanket UPDATE triggers

On 07/14/2011 10:42 PM, Ben Harper wrote: > This may be specific to RTree indexes - I haven't taken the time to fully > understand it. > > Dangerous: > CREATE TRIGGER ON UPDATE ON tablename { SET RTREE MIN/MAX } > > Fine: > CREATE TRIGGER ON UPDATE OF geometry OF tablename { SET RTREE MIN/MAX } >

Re: [sqlite] FTS3: synonyms dictionary and tokens length

On 07/14/2011 07:42 PM, Alexey Pechnikov wrote: >> No. Don't return anything for a stop word. Just advance to the next >> non stop-word token and return it. > > Thanks, I did and it's work. > > And another question... Is there any way to use multi-word synonyms? Like to: > sqlite -> Open Source

Re: [sqlite] FTS3: synonyms dictionary and tokens length

On 07/14/2011 05:29 PM, Alexey Pechnikov wrote: > With 0-length token in icuNext there is the error: > Error: SQL logic error or missing database > > May xNext returns 0 length when the token is stopword? No. Don't return anything for a stop word. Just advance to the next non stop-word token and

Re: [sqlite] Attach file/disk DB to memory DB

On 07/13/2011 10:18 PM, Alexandre G wrote: > > If I was to attach a database located in a file on disk to a database located > in memory, would the file DB be loaded in memory too, or would its usage > remain disk-based? It remains on disk. Whether or not the main database is in-memory does not

Re: [sqlite] FTS3: synonyms dictionary and tokens length

On 07/13/2011 05:05 PM, Alexey Pechnikov wrote: > With synonyms dictionary the result token length can be more then > original token length. > Is it problem for current realization of FTS? I don't think so. If it is, it's a bug. Dan. ___ sqlite-users

Re: [sqlite] Data type of the blob returned by matchinfo()

On 07/13/2011 04:57 AM, Abhinav Upadhyay wrote: > Hi, > > Quoting the ranking function given in the appendix of the FTS3 > documentation page (http://www.sqlite.org/fts3.html#appendix_a) > > static void rankfunc(sqlite3_context *pCtx, int nVal, sqlite3_value **apVal){ >int *aMatchinfo;

Re: [sqlite] possible bug "foreign key mismatch" in TCL

On 07/13/2011 07:18 AM, Victor Mayevski wrote: > I am getting a "foreign key mismatch" in TCL in the following situation: > #The system is Ubuntu 10.10 32bit, using ActiveState 8.6 32 bit TCL binaries > > % package require sqlite3 > 3.7.6.3 > > > #Two example tables: > create table users (name

Re: [sqlite] FTS4 code from the website

On 06/30/2011 08:55 PM, Ryan Henrie wrote: > Even if I use the stock example from the web page, with only 2 columns, > and the exact schema from the example, I get the same result. > > I'm wondering if it is a bug in the example code on the website (ie the > source code has moved on, invalidating

<    4   5   6   7   8   9   10   11   12   13   >