Re: [sqlite] Infinite loop in sqlite3VdbeSorterWrite when sorting big data

On 11/25/2014 02:53 AM, Marcin Sobieszczanski wrote: Hi I work with sqlite files that have a few gigabytes of simple data. Almost all of the data sits in one table that has 9 non-null integer columns (including row_id, and one int64 column) plus 3 additional string or int columns (additional

Re: [sqlite] Null rowid when using CTEs

On 11/14/2014 08:33 AM, RP McMurphy wrote: When using CTEs the rowid is null. The following returns "--NULL--" .null --NULL-- with c(x) as (select 1) select rowid from c; I guess that's expected. The same behaviour as for reading the rowid of a view or sub-select. It

Re: [sqlite] sliteonline or sqlite in js

On 11/14/2014 03:37 PM, Noel Frankinet wrote: Impressive ! It is that. Perhaps not as functional as a native app (so far), but looking really good! And you can't beat the deployment. Dan. On 12 November 2014 12:08, Kirill wrote: Good day, Full line manager to

Re: [sqlite] Using sqlite3_backup* while the destination connection has "busy" read-only statements.

On 11/13/2014 05:26 AM, Shaun Seckman (Firaxis) wrote: Greetings all, I'm running into a situation in where our application is crashing during a call to sqlite_backup_finish inside of btreeParseCellPtr because some of the structure is corrupted. Both the source and destination database are

Re: [sqlite] [RFE bug] Improve error reporting for foreign keys

On 10/27/2014 01:49 PM, Tristan Van Berkom wrote: This is just a request-for-enhancement bug report, I've went to the trouble or reproducing this problem in a simple test case and while I probably wont be able to immediately benefit from an upstream fix for this, I hope that this bug report will

Re: [sqlite] Installing 3.8.7 gives 3.8.6

On 10/25/2014 09:36 PM, Arnaud Meuret wrote: Hi everyone, On a fresh CentOS 5, compiling SQLite using the [current tarball][1] for 3.8.7, the version being installed ends up in `/usr/local/lib` as **3.8.6**: bash-3.2# make install make[1]: Entering directory

Re: [sqlite] Bug 993556 - SQLite crash in walIndexTryHdr due to Windows EXCEPTION_IN_PAGE_ERROR exception

On 10/18/2014 05:45 AM, Deon Brewis wrote: I'm trying to follow Richard's advise to work around this issue, which is: "Is that database ever used by more than a single process. (Use by multiple threads using separate connections does not count - I mean really used by multiple processes with

Re: [sqlite] INSERT OR REPLACE in a virtual table implementation....

On 10/18/2014 01:07 AM, dave wrote: I have a virtual table implementation, and I would like to use the INSERT OR REPLACE syntax to simplify actions for the user. In my xUpdate method, for the case where insertion is occuring, else if ( argc > 1 && SQLITE_NULL == sqlite3_value_type ( argv[0] )

Re: [sqlite] Unable to prepare a statement

On 10/15/2014 07:19 AM, Sam Carleton wrote: When I use the SQLite Manager, I am able to run this query just fine: UPDATE EventNode SET IsActive = 1 WHERE EventNodeId IN (SELECT w.EventNodeId FROM EventNode as w, EventNode as m on m.objectId =

Re: [sqlite] Lets try this again. Sqlite Python libraries throwing exception on unique constraint.

On 10/13/2014 11:44 PM, Jeffrey Parker wrote: Hello, I am working with sqlite3 in python 2.7.8 and I am running into a strange error where I get the below exception when running an insert into statement on an empty table. I know this is probably more to do with the python libraries but I

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

On 10/09/2014 07:23 PM, Sohail Somani wrote: On 2014-10-09, 7:32 AM, Dan Kennedy wrote: Got it, thanks for the explanation. Just to make sure that I understand you correctly, is the clause MATCH '*l0l* *h4x*' getting translated to MATCH 'l0l* h4x*'? Yes, that's right. Dan. In that case

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

On 10/09/2014 01:13 AM, Sohail Somani wrote: On 2014-10-07, 4:04 PM, Dan Kennedy wrote: On 10/08/2014 01:52 AM, Sohail Somani wrote: Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*", though it did work as expected with the older version. I'd suggest keepi

Re: [sqlite] 50% faster than 3.7.17

On 10/09/2014 04:38 PM, David Woodhouse wrote: On Thu, 2014-09-25 at 11:13 +0100, David Woodhouse wrote: I suggested a couple of specific optimisations which the query planner might be able to make, which should hopefully have benefits wider than just my own use case. Are those not viable? I'm

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

On 10/08/2014 01:52 AM, Sohail Somani wrote: Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*", though it did work as expected with the older version. I'd suggest keeping the old behaviour unless there is a performance-based reason not to. On 2014-10-07, 2:49 PM, Sohail

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

On 09/20/2014 10:59 AM, Yuanzhong Xu wrote: I think this is related to a check for restriction (18) in subquery flattening. (18) If the sub-query is a compound select, then all terms of the ORDER by clause of the parent must be simple references to columns of the sub-query. Quite correct.

Re: [sqlite] Atomic database structure initialization

On 09/19/2014 02:53 PM, Paul wrote: Paul wrote: My goal is to make structure initialization of an *abstract* database atomic. [...] if database file is missing it must be created and initialized. Just do the check for the database structure and the

Re: [sqlite] write internal blob vs external file

On 09/15/2014 06:19 PM, Dominique Devienne wrote: On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy <danielk1...@gmail.com> wrote: On 09/15/2014 03:18 PM, Dominique Devienne wrote: On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith <eas@gmail.com> wrote: Looking at the sql

Re: [sqlite] write internal blob vs external file

On 09/15/2014 03:18 PM, Dominique Devienne wrote: On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith wrote: Looking at the sqlite web site and mailing lists shows that the SQLite team has taken a stab at answering the question, "is it faster to read a blob out of sqlite or

Re: [sqlite] fts5

On 09/11/2014 02:51 AM, Kyle Shannon wrote: I was browsing the source on the fossil repository, and I noticed that there were files for fts5 in the trunk. I've been playing with fts3/4 and I'm curious about what new features/changes are incorporated into fts5. Can anyone clarify that for me?

Re: [sqlite] unixGetPageSize needed but undefined

On 07/27/2014 09:30 PM, Alan Hourihane wrote: Hi all, I logged this bug via Gentoo, but it should be done here... https://bugs.gentoo.org/show_bug.cgi?id=517110 Is this still present on the trunk? It may have been fixed here: http://www.sqlite.org/src/info/612b6d1b1f Dan. " This

Re: [sqlite] [bug] sqlite memory corruption (use by digikam)

On 07/16/2014 03:22 AM, Dan Kennedy wrote: On 07/15/2014 09:06 PM, Mathieu Clabaut wrote: Hello, Digikam bug https://bugs.kde.org/show_bug.cgi?id=323888#c89 seems to be caused by a sqlite memory leak It looks very much like the program is not calling sqlite3_close(). If you have a small

Re: [sqlite] [bug] sqlite memory corruption (use by digikam)

On 07/15/2014 09:06 PM, Mathieu Clabaut wrote: Hello, Digikam bug https://bugs.kde.org/show_bug.cgi?id=323888#c89 seems to be caused by a sqlite memory leak It looks very much like the program is not calling sqlite3_close(). If you have a small program that shows SQLite leaking memory

Re: [sqlite] WITH syntax error

/67bfd59d9087a987 http://www.sqlite.org/src/info/31a19d11b97088296a The fix appeared in 3.8.4. If you upgrade, the statement will work. You'll note that I said the statement "should" work in 3.8.3. Not that it does. :) Dan. On Sat, Jul 12, 2014 at 8:06 PM, Dan Kennedy

Re: [sqlite] WITH syntax error

On 07/13/2014 12:29 AM, Staffan Tylen wrote: The following statement is flagged as invalid, so what's the correct way of coding it? WITH A AS (SELECT 'A'), B AS (SELECT 'B') SELECT * FROM A UNION SELECT * FROM B ; This statement should work in SQLite 3.8.3 or

Re: [sqlite] pragma and prepare statement

On 07/10/2014 08:44 PM, Baptiste Daroussin wrote: Hi, We are using sqlite intensively in out developement and we discovered that apparently we cannot create a statement with a pragma Is there a reason why it is not possible? is it a bug or a per design The docs feature the following two

Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?

On 07/10/2014 04:45 PM, Tim Streater wrote: I have just noticed this syntax which will simplify some table creation for me. However in some instances where I want to use it, I have a handy SELECT available, but I don't want to actually insert a row at that time. Testing with the shell, the

Re: [sqlite] documentation bugs

On 07/09/2014 01:45 PM, Laurent Dami wrote: * 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

Re: [sqlite] Planner chooses incorrect index

On 06/25/2014 04:44 PM, João Ramos wrote: The sample column has some sensitive data that I can't share, so I'll have to change it and then try to reproduce the problem (I removed it before because I assumed that it wouldn't be important). On Mon, Jun 23, 2014 at 3:26 PM, Dan Kennedy <danie

Re: [sqlite] sqlite-3.8.5: is auto index created multiple times for a single statement?

On 07/07/2014 03:12 AM, Nissl Reinhard wrote: Hi, while preparing this statement create table gpBestellvorschlagInfo as select GanttPlanID , BestellterminRaw , case when not ( select max(HinweisCodiert) from Bestellvorschläge where ArtikelOID = o.ArtikelOID and

Re: [sqlite] Multiple reads and writes to a single DB connection from multiple threads

On 06/02/2014 08:36 PM, Hick Gunter wrote: If you compile with SQLITE_THREADSAFE=1 then multiple calls from different threads will be serialized by SQLite. "Serialized" means that only one thread at a time will be allowed to run within SQLite; API calls from other threads will block until the

Re: [sqlite] Fwd: signal 6 during sqlite_step in WAL mode

On 07/01/2014 12:07 PM, Mattan Shalev wrote: Hey guys, I'm getting signal 6 during sqlite_step in WAL mode. Working on Ubuntu 12.04, sqlite3 3.7.9. One process is the writing continuously, while other process reads from the DB in a multi threaded access. I made sure that sqlite is configured to

Re: [sqlite] hoe to create index to a big table?

On 06/30/2014 03:30 PM, Hadashi, Rinat wrote: I work with a very big table, on Linux. I fail to create index getting the following error: SQL error near line 1: database or disk is full A CREATE INDEX on a large table uses temporary files to sort the data before creating the actual index

Re: [sqlite] Planner chooses incorrect index

On 06/23/2014 05:48 AM, João Ramos wrote: Here you go: sqlite_stat1 (before - good planning) HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5 HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2 HistoryEntry idx_HistoryEntry_sourceType_sourceId 14992 2999 2

Re: [sqlite] R-Tree Storage Optimization for Points

ROM abc; 1|10.0|20.0 The other columns are pairs, one pair per dimension, containing the minimum and maximum values for that dimension, respectively. Alysson Gonçalves de Azevedo "Anarcho-syndicalism is a way of preserving freedom." - Monty Python 2014-06-19 14:06 GMT-03

Re: [sqlite] R-Tree Storage Optimization for Points

On 06/19/2014 11:57 PM, Mohit Sindhwani wrote: Hi Wolfgang, On 19/6/2014 11:54 PM, Wolfgang Enzinger wrote: Not sure why you think you have to store those point coordinates twice. This works: sqlite> CREATE VIRTUAL TABLE abc USING rtree(id,x,y); sqlite> INSERT INTO abc VALUES(1,20,30);

Re: [sqlite] Proximity ranking with FTS

On 06/17/2014 10:48 AM, Josh Wilson wrote: Yeah I had thought about using the byte distance between words but you get these instances: [Example A] |word1|10charword|word2| [Example B] |word1|3charword|4charword|3charword|word2| By using byte distances, both of these score the same, where

Re: [sqlite] shell core dumps on incomplete init file

On 06/04/2014 05:06 PM, Rob Golsteijn wrote: Hi List, I noticed that the sqlite shell core dumps when it is started with an init file that ends with an incomplete statement. Example: Init file called "my_init.sql" with the following contents: -- note that the line below is NOT

Re: [sqlite] Comments loss in sqlite_master table

On 05/29/2014 03:42 PM, big stone wrote: Hello, I miss the functionnality of some other sql motors that keep the comments inside an object definition, like a table. Example : (with sqlite.exe 3.8.3) create table /* This table has an educative purpose */ toto(x); create table /* This table has

Re: [sqlite] vtable vs. OR condition

On 05/26/2014 01:45 AM, András Kardos wrote: Hello, As an expoeriment I'd like to create various virtual tables (vtable) for SQLite. Like filysystem, process list, mailbox - all local or remote. The idea is to use SQLite as an unified API and query processor to access or join data from various

Re: [sqlite] Pre-preparing querys

On 05/20/2014 07:30 AM, James K. Lowden wrote: On Mon, 19 May 2014 22:26:29 +0100 Simon Slavin wrote: On 19 May 2014, at 10:21pm, Roger Binns wrote: It seems like most language wrappers for SQLite include some sort of statement cache because it

Re: [sqlite] More LSM leak

On 05/08/2014 06:59 AM, Charles Samuels wrote: This leak cursor leak can be consistently reproduced by my test program, but it doesn't occur every time you create and delete the cursor. The files you'll need are: http://www.derkarl.org/~charles/lsm/smaller.trace.bz2

Re: [sqlite] LSM Leaks memory

On 05/07/2014 04:51 AM, sql...@charles.derkarl.org wrote: I In a more complex program, lsm seems to leak memory to no bounds, causing my application. Are bug reports against LSM even helpful? I think they are. Thanks for the report. Now fixed here:

Re: [sqlite] FTS3/4 merge function behaviour when deleting rows

possible for the same reasons. Ben Am 02.05.14 11:22 schrieb "Dan Kennedy": So I'm thinking a solution might be: * Fix FTS so that it picks this case - when a merge includes so many delete markers that the output is small enough to be deemed a level-N b-tree, not

Re: [sqlite] FTS3/4 merge function behaviour when deleting rows

On 05/02/2014 04:13 PM, Andrew Moss wrote: On 2 May 2014 07:57, Dan Kennedy <danielk1...@gmail.com> wrote: On 05/01/2014 03:30 PM, andrewmo wrote: We are using the FTS3 extension to sqlite to store large numbers of short (~300 byte) documents. This is working very well and provid

Re: [sqlite] FTS3/4 merge function behaviour when deleting rows

On 05/01/2014 03:30 PM, andrewmo wrote: We are using the FTS3 extension to sqlite to store large numbers of short (~300 byte) documents. This is working very well and providing us with very fast text search, but the behaviour around deletion of documents has me confused. Our system must control

Re: [sqlite] LSM bug

On 04/29/2014 03:53 AM, sql...@charles.derkarl.org wrote: I didn't know this list strips attachments, so the source file is here: http://derkarl.org/~charles/runlsm.cpp Thanks for this. It is a problem. LSM accumulates data in a tree structure in shared-memory until there is "enough" (~1-2

Re: [sqlite] problem with INSERT after ALTER TABLE ... ADD COLUMN ... DEFAULT ... performed on source table

On 04/26/2014 05:23 AM, Hinrichsen, John wrote: Default non-NULL values copied from a column that was added using "ALTER TABLE ... ADD COLUMN ... DEFAULT ..." are inserted into another table as NULLs when copied using "INSERT INTO ... SELECT * FROM ..." However, the same values are propagated

Re: [sqlite] Trigger cascade/nesting

On 04/23/2014 05:05 PM, Peter Hardman wrote: Hi, Is there a way of preventing triggers cascading? I have a table where I need to set a timestamp field when a new record is INSERTED. The table uses an auto-increment key field so a BEFORE INSERT trigger is ruled out. An AFTER INSERT trigger

Re: [sqlite] Sqlite Instead of Trigger on Views does not always fire..

On 04/02/2014 06:08 PM, Engin Guelen wrote: Hello, i noticed that Instead of Update Triggers on Views do not fire as soon as the Views get a bit more involved. The Following View and Trigger Code compiles w/o Errors. But when changing the SYNC column nothing happens to REF_TAB, that

Re: [sqlite] Using a customized collate function to mimic integer array type.

On 03/21/2014 10:33 PM, Ben Peng wrote: Hi, Tristan, Your solution definitely works (we have defined a few custom functions) but our application hides databases from users but allows users to use simple conditions to retrieve results. To use this function, we would have to 1. teach users use

Re: [sqlite] Using ICU case folding support

On 03/19/2014 09:44 PM, Aleksey Tulinov wrote: I've created test database: sqlite> CREATE TABLE test (x COLLATE NOCASE); sqlite> INSERT INTO test VALUES ('s'); sqlite> INSERT INTO test VALUES ('S'); sqlite> INSERT INTO test VALUES ('ё'); -- Russian e with diacritic sqlite> INSERT INTO test

Re: [sqlite] Using ICU case folding support

On 03/19/2014 07:55 PM, Aleksey Tulinov wrote: On 03/18/2014 10:02 PM, Alex Loukissas wrote: Alex, I suppose I can declare the column as BINARY and use LOWER( ) in my select statements. Browsing through the code though, I do see uses of u_foldCase in certain places, which leads me to

Re: [sqlite] Opening and closing an SQLITE3 db in PHP

On 03/19/2014 06:49 AM, Tim Streater wrote: Part of my app will, at user request, read some data from an SQLite db and also some files from disk, and send it all out on the network. This may in some cases take several minutes, at the end of which the db gets updated here and there. While this

Re: [sqlite] Using ICU case folding support

On 03/19/2014 01:22 AM, Alex Loukissas wrote: Hello, I'm trying to use the ICU extension and it looks like what I want to do is not very clear from the documentation. I would like to switch from my current usage, where my column declaration is as follows: CREATE TABLE demo("name text not null

Re: [sqlite] Error "Database or disk if full" shown when indexing for 2 billion records.

On 03/17/2014 08:32 PM, Yi Wang wrote: I inserted 2 billion records with only 1 column with name of "str_md5", the value is the MD5 encrypted value of "mm"+rowid(such like MD5(mm121212...). I didn't not create primary key for the only column b/c i am not sure it would slow the speed of insert

Re: [sqlite] very slow fdsync() calls

On 03/11/2014 07:01 AM, Jono Poff wrote: Hi, I have an application that uses sqlite3. Investigating a problem with the app stalling occasionally I found that (every hour or two) an fdsync() system call from sqlite3_step() was taking over 3 seconds to return. On closer investigation, the

Re: [sqlite] Fwd: Exception when querying a range index

On 03/11/2014 04:17 AM, St. B. wrote: Hi, On Mon, Mar 10, 2014 at 6:37 PM, Dan Kennedy <danielk1...@gmail.com> wrote: On 03/10/2014 06:49 PM, St. B. wrote: Hi, I posted this question last year, but did not get any feed back. Is this something that can be handled on the ML, or should

Re: [sqlite] Fwd: Exception when querying a range index

On 03/10/2014 06:49 PM, St. B. wrote: Hi, I posted this question last year, but did not get any feed back. Is this something that can be handled on the ML, or should I ask in another place? Regards, -- Forwarded message -- From: St. B. Date: Sun, Jan 6,

Re: [sqlite] Out of memory error for SELECT char();

On 03/09/2014 01:25 AM, Zsbán Ambrus wrote: In the sqlite3 console, the following very simple statement gives "Error: out of memory": SELECT char(); I think this is a bug. It is. Thanks for the report. Now fixed here: http://www.sqlite.org/src/info/ba39df9d4f Dan. This query

Re: [sqlite] SQL quine using with

On 03/08/2014 08:53 PM, Kees Nuyt wrote: Someone called zzo38 posted a quine (self-replicating program) on Internet Relay Chat in network: Freenode, channel: #sqlite [2014-03-08 11:01:59] < zzo38> I made a quine program in SQL. [2014-03-08 11:02:10] < zzo38> with q(q) as (select 'with q(q) as

Re: [sqlite] Weird out of memory problem a prepare

On 03/05/2014 03:05 AM, Eduardo Morras wrote: On Tue, 4 Mar 2014 15:19:24 + Simon Slavin wrote: On 4 Mar 2014, at 3:15pm, Simon Slavin wrote: On 4 Mar 2014, at 3:09pm, Eduardo Morras wrote: zSql= "SELECT r.name,

Re: [sqlite] Unnecessary implicit conversion (may lead to a bug)

On 02/24/2014 05:54 AM, skywind mailing lists wrote: In afpUnlock(sqlite3_file *, int) the sharedLockByte is defined as an int (int sharedLockByte = SHARED_FIRST+pInode->sharedByte;) although all other related variables and the following function parameters are defined as unsigned long

Re: [sqlite] How to Troubleshoot Disk I/O Error

On 02/20/2014 09:29 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: Our client got the following print-out. First hint of failure is the line "TRUNCATE 48 32768 failed". Any insights into what the root cause might be ? The operation that is failing is (probaby) an attempt to use

Re: [sqlite] help needed for major SQLite problem

On 02/08/2014 03:00 AM, C M wrote: This is a follow-up to a question I asked on this list on Sep 1st, 2013, about an error that I was randomly getting with disk-based SQLite database in a Python desktop application. I now have more info to provide about the error...such as what was asked for at

Re: [sqlite] Virtual Table xRowid

On 02/06/2014 01:22 AM, Kevin Martin wrote: Hi, My questions are basically: 1) What causes sqlite3 to call xRowid 2) If I don't want to update my virtual table, or do a select ROWID, can I just use an incremental counter, increased on every call to xNext (bearing in mind the order of my data

Re: [sqlite] Understanding transactions

On 02/04/2014 10:12 PM, Igor Tandetnik wrote: On 2/4/2014 5:23 AM, Yuriy Kaminskiy wrote: How sqlite is supposed to behave when *) there are read-only transaction; *) there are update transaction on other connection; *) cache space is exhausted by update transaction; *) sqlite was not able to

Re: [sqlite] possible bug 3.8.1 /3.8.3

On 02/04/2014 12:25 AM, Adam Devita wrote: Good day, I'm debugging some code that uses 3.8.1, and I've tried just upgrading to 3.8.3, which didn't work. The observation is that This query: SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp) FROM data WHERE stream_num = ?)

Re: [sqlite] Fine tuning table indexes

On 02/02/2014 11:16 PM, James K. Lowden wrote: On Sat, 1 Feb 2014 11:21:45 -0500 Stephen Chrzanowski wrote: Would be interesting to see when and where that single index comes into play when multiple indexes are defined. create table T (t int primary key, a int , b int);

Re: [sqlite] wal_autocheckpoint

On 02/01/2014 12:13 AM, veeresh kumar wrote: Hi All, Below is my Sqlite settings. The current database size is ~ 8GB PRAGMA journal_mode = WAL PRAGMA synchronous = NORMAL PRAGMA wal_autocheckpoint = 50" With the above check point, the time taken to commit transaction to actual

Re: [sqlite] too many SQL variables

On 01/31/2014 01:01 AM, E. Timothy Uy wrote: Just for my edification, what is the limit on the number of SQL parameters? Today I hit "too may SQL variables" with about 1400... 999. http://www.sqlite.org/limits.html#max_variable_number ___

Re: [sqlite] How to Troubleshoot Disk I/O Error

On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: Hi, We have one client that recently upgraded from WinXP SP3 to Win7 sp1 (Japanese Language). With new OS, attempts to access read from our sqlite database fails with disk I/O error. Can anyone help with suggestions on

Re: [sqlite] How to Troubleshoot Disk I/O Error

On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: Hi, We have one client that recently upgraded from WinXP SP3 to Win7 sp1 (Japanese Language). With new OS, attempts to access read from our sqlite database fails with disk I/O error. Can anyone help with suggestions on

Re: [sqlite] "Common Table Expression"

On 01/25/2014 01:00 AM, big stone wrote: AND NOT EXISTS ( SELECT 1 FROM ok AS lp WHERE ind=lp.c and z.z = substr(s, n, 1) ) s/ok/goods/ and s/lp.c/lp.r/ Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLITE 3.8.3 beta: SQLITE_OMIT_CTE build doesn't work

On 01/20/2014 08:36 PM, Jan Nijtmans wrote: $ gcc -c -Wall -o sqlite3.o -DSQLITE_OMIT_CTE sqlite3.c sqlite3.c: In function ‘yy_reduce’: sqlite3.c:117782:3: warning: implicit declaration of function ‘sqlite3WithAdd’ [-Wimplicit-function-declaration] yygotominor.yy59 = sqlite3WithAdd(pParse,

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

Let me try again. Some spaces were missing from the input in the parent post: /* The input suduko. */ WITH RECURSIVE input(sud) AS ( VALUES( '53..76..195986.8...6...34..8.3..17...2...6.628419..58..79' ) ), /* A table filled with digits 1..9, inclusive. */ digits(z,

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

Beaten by minutes! /* The input suduko. */ WITH RECURSIVE input(sud) AS ( VALUES( '53 76 195986 8 6 34 8 3 17 2 6 6 28 419 58 79' ) ), /* A table filled with digits 1..9, inclusive. */ digits(z, lp) AS ( VALUES('1', 1) UNION ALL SELECT

Re: [sqlite] Promoting a read-only db connection to read-write

On 01/16/2014 10:46 PM, Mcdonald, Brett wrote: Can an existing sqlite read-only connection be promoted to a read-write connection, perhaps using sqlite3_file_control() and sqlite3_io_methods? Or do previously executed 'read-only' sql statements make promotion less desirable then simply

Re: [sqlite] Lightweight integrity_check?

On 01/16/2014 02:20 AM, Steven Fisher wrote: After opening, I want to do some basic checking of my database. I’m finding integrity_check much too slow for some of my users (especially with large amounts of data) but I don’t want to just blindly trust the database either. A few options: -

Re: [sqlite] Behavior change in group by in 3.8.2 when compared with 3.7.7

On 01/09/2014 03:38 PM, Dominique Devienne wrote: On Thu, Jan 9, 2014 at 7:41 AM, Navaneeth K N wrote: I am seeing a change in the results returned for a query in SQLIte 3.8.2 version. The query used to return expected results with 3.7.7 and when I updated to 3.8.2, it

Re: [sqlite] sqlite3session_changeset API oddity

On 01/06/2014 03:40 PM, Simon Slavin wrote: On 6 Jan 2014, at 5:18am, Dan Kennedy <danielk1...@gmail.com> wrote: sqlite3session_changeset() returns a blob containing the differences between the current database and the database as it was when the session object was first attached.

Re: [sqlite] sqlite4 completion status

On 01/06/2014 02:27 PM, big stone wrote: Hello, Is there a completion status for the sqlite4 project somewhere ? An estimate Year/Quarter for a first downloadable version ? Currently there is not. Dan. ___ sqlite-users mailing list

Re: [sqlite] sqlite3session_changeset API oddity

On 01/05/2014 11:23 PM, Marco Bambini wrote: I am using sqlite3_changeset API to add undo/redo capabilities to an app. I record sqlite operations and I store all of them in a sqlite3_session object. When I need to UNDO an operation I simply do something like: rc =

Re: [sqlite] unable to use function MATCH in the requested context

On 01/04/2014 03:23 PM, E. Timothy Uy wrote: This is a class of problems I started seeing around 3.8.x. Any insights would be much appreciated. Sometimes it happens when there are more than a few INNER JOINS, others, in the case, happen when I ORDER BY the virtual table's docid. I feel like this

Re: [sqlite] Apply a changeset

On 01/03/2014 10:54 PM, Marco Bambini wrote: Documentation is not very clear about this point… in order to apply a change set starting from a session should just I write something like: int rc= sqlite3session_changeset(session, , ); rc = sqlite3changeset_apply (db, pnChangeset, ppChangeset,

Re: [sqlite] SQLite3 temporary filename collision in worker processes

On 12/31/2013 09:25 PM, Török Edwin wrote: Hi, I am using SQLite 3.8.1 compiled from the amalgamation on Debian GNU/Linux "wheezy", amd64. I have set a logging callback with sqlite3_config(SQLITE_CONFIG_LOG, qlog, NULL), and I see the following error sometimes: Query "INSERT INTO topush

Re: [sqlite] Android SQLite 3.8.2 issue with triggers & constraints

On 12/31/2013 10:03 PM, Douglas Orr wrote: Hi, I have run into an issue when trying to use triggers with column constraints on Android (using our build of SQLite 3.8.2, not Android's built-in version from native code.) Executing the following on a file-backed database fails: CREATE TABLE

Re: [sqlite] Will It be a Problem that link to 3.7.17 but run on 3.8.2

On 12/30/2013 10:43 PM, narkewo...@gmail.com wrote: Hi, If I have my code compiled/linked agaist a 3.7.17 library on my development host but run on a target that in fact installed with 3.8.2 library on my target, will it be a problem? More specifically, will it lead to database file corruption?

Re: [sqlite] Does not detect invalid column name when subexpression optimized away

On 12/20/2013 11:11 AM, David Bicking wrote: But isn't NULL and 0 a NULL? I don't think it is. NULL really means unknown. The result of "0 AND unknown" is not "unknown", it is 0. By contrast the result of "0 OR unknown" really is unknown: sqlite> SELECT (0 OR NULL); null "1 OR unknown"

Re: [sqlite] Bug where query does not give a result while it should

On 12/18/2013 10:10 PM, Harmen de Jong - CoachR Group B.V. wrote: We have found a query that does not give a result, while it should. The query is: select id from productionentry where id NOT IN (select productionentryid from zentry) AND examid=12581; There are rows in the table

Re: [sqlite] General R*Tree query

On 12/18/2013 12:49 AM, Brian T. Carcich wrote: I'm working on an SQLite solution to get at star catalogs; they are usually searched via Right Ascension (RA), Declination (DEC), and magnitude (mag). RA,DEC is a spherical coordinate system to specify a star position on-sky; magnitude is related

Re: [sqlite] [BUG; 3.7.13] ANALYZE leads to a wrong query plan for RTrees

On 12/10/2013 02:44 PM, skywind mailing lists wrote: This is an example that the ANALYZE command leads to a wrong query plan for RTrees: CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude); CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude,

Re: [sqlite] Failed test on aarch64

On 11/27/2013 04:26 PM, Zsbán Ambrus wrote: On Tue, Nov 26, 2013 at 5:01 PM, Richard Hipp wrote: The check-in above changes the behavior of REAL-to-INTEGER casts where the REAL value is larger than the largest possible integer. For example: SELECT

Re: [sqlite] WAL and long-lived prepared statements

On 11/22/2013 02:47 PM, Dan Kennedy wrote: On 11/22/2013 02:39 PM, Sascha Sertel wrote: Hello everybody, the app I'm working on heavily relies on use and reuse of prepared statements, many of them are reset many times but not finalized until the end of the application lifetime. We also

Re: [sqlite] Enable WAL on a QNX system

On 11/20/2013 12:20 AM, Sandu Buraga wrote: It's locked into EXCLUSIVE mode if it was in EXCLUSIVE mode when you switched to WAL. But > there's nothing requiring it. I was not referring to the locking_mode, I was writing about the lock itself over the database (iNode structure). On QNX the

Re: [sqlite] Handling move/rename of an SQLite database file

On 11/09/2013 06:47 PM, Simon Slavin wrote: On 9 Nov 2013, at 10:07am, Dan Kennedy <danielk1...@gmail.com> wrote: On 11/09/2013 04:58 PM, L. Wood wrote: Richard Hipp <d...@sqlite.org> wrote: Rollback journal files might be closed and reopened. [snip] [snip]* But what about t

Re: [sqlite] Handling move/rename of an SQLite database file

On 11/09/2013 04:58 PM, L. Wood wrote: Richard Hipp wrote: Rollback journal files might be closed and reopened. But the main database file is opened once and held open until sqlite3_close() (or DETACH if the file was originally opened using ATTACH). Thanks for this

Re: [sqlite] MATCH with punctuation in parenthesis causing error

On 11/04/2013 01:58 PM, David de Regt wrote: I realize that the query is being parsed with the enhanced query syntax since I added parenthesis (and have that compile flag enabled), but why does the exclamation point at the end cause an error? It seems like it should be just ignored, given

Re: [sqlite] Trigger slows down application start-up

On 10/31/2013 06:20 AM, Igor Korot wrote: Hi, Clemens et al, On Wed, Oct 30, 2013 at 1:11 AM, Igor Korot wrote: Clemens, On Wed, Oct 30, 2013 at 12:47 AM, Igor Korot wrote: On Wed, Oct 30, 2013 at 12:36 AM, Clemens Ladisch wrote:

Re: [sqlite] SQLite "Frontend"?

On 10/28/2013 02:19 AM, Tim Streater wrote: On 27 Oct 2013 at 18:29, Ulrich Goebel wrote: What I'm looking for is a bash-like frontend for SQLite. The sqlite3 does in principle what I want, but to test queries it would be very nice to have a history of the commands

Re: [sqlite] FTS4 + spellfix1 with multiple languages

On 10/15/2013 08:13 PM, Raf Geens wrote: Hi, I have a FTS4 table that contains entries in multiple languages (using the languageid option). I also have a spellfix1 table that I use to search with misspelled words on the FTS4 table. In the spellfix1 documentation a fts4aux table is used to

<    1   2   3   4   5   6   7   8   9   10   >