Re: [sqlite] How often is xDisconnect called? (Was: Suggestion for syntax enhancement for virtual tables)

is accessed. Dan. Peter On 1/2/2015 3:00 PM, Peter Aronson wrote: If only the xDisconnect method is called on a virtual table create in the temp database at disconnect time, is that the only time xDisconnect will be called? The documentation at sqlite.org doesn't seem to say. Jay

Re: [sqlite] VACUUM requires 6.7 times space ?

On 01/05/2015 06:22 PM, Simon Slavin wrote: I have a database file which is 120GB in size. It consists of two huge tables and an index. Its journal_mode is DELETE. It is on a partition with 803GB of free space. By my calculations I have 6.7 times the amount of free space as the database is

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

On 01/05/2015 02:52 PM, Dan Kennedy wrote: On 01/05/2015 01:39 PM, Hick Gunter wrote: This is completely legal and well defined. HAVING is applied to the RESULT set of a SELECT. The select asks to count the "distinct kontrola" in each group of kvadrat and datum, the HAVING clause

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

te> INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 1, 3); sqlite> SELECT count(*) AS b FROM t1 GROUP BY b HAVING b=2; 1 sqlite> SELECT count(*) AS d FROM t1 GROUP BY b HAVING d=2; 2 Showing that given a choice, MySQL picks an original column over an alias within the HAVING clause

Re: [sqlite] Getting SQLITE_IOERR_WRITE when running sqlite

parameters to be passed to one of these system calls. Running under [strace] or equivalent might help to figure out why the system call is failing. Dan. Thanks and Regards Aswin Waiba === Please access

Re: [sqlite] Suggestion for syntax enhancement for virtual tables

On 01/02/2015 01:58 PM, Hick Gunter wrote: Temporary virtual tables sounds like an interesting concept. Does the xDestroy() function get called on such a beast (as opposed to xDisconnect() when the connection is closed)? Just xDisconnect(). Dan. Should that function delete the backing

Re: [sqlite] journal file is not removed when ATOMIC WRITE is enabled

rder to determine that it is not a hot-journal, then close it and proceed to open the db in wal mode. Once the db has been opened in wal mode, the cold journal file will be ignored completely. So the net effect will be a slight overhead when a connection opens its first read transaction on the db. Dan.

Re: [sqlite] FTS4 Problem

em as well. What am I missing? Creating an FTS4 table that uses the "content=" option does not automatically populate the FTS index. It just creates an empty FTS index that SQLite assumes the user will somehow take care of populating. Dan. Thanks, Peter On Dec 24, 2014

Re: [sqlite] SQLITE wal file size keeps growing

. Logging this information along with the checkpoint attempts and return codes might help to shed light on the problem. Dan. sqlite3_wal_checkpoint_v2() returns SQLITE_OK, and pnLog and Ckpt as equal(around 4000), indicating complete wal file has been synced with main db file. So next

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

On 12/18/2014 04:16 PM, Paul wrote: Hi, Dan. On 12/18/2014 02:41 PM, Paul wrote: I want to confirm that issue is fixed for me. Thanks again, Dan! Please ignore this update, patch fixes this problem as well. I want to add even more input for this issue. I understand why there is implicit

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

On 12/18/2014 02:41 PM, Paul wrote: I want to confirm that issue is fixed for me. Thanks again, Dan! Please ignore this update, patch fixes this problem as well. I want to add even more input for this issue. I understand why there is implicit savepoint, when I remove row from 'parent' table

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

M foo WHERE id IN (...)? There is now an update on the fossil trunk that should fix the problem with ON DELETE CASCADE: http://www.sqlite.org/src/info/8c5dd6cc259e0cdaaddaa52ccfa96fee6b166906 Dan. Best regards, Paul ___ sqlite-users mailing l

Re: [sqlite] SQLITE_FTS3_MAX_EXPR_DEPTH and upgrading sqlite

is to prevent stack overflow. I guess to get the old behavior, set it to a very large value. Are you able to post the FTS query that caused a problem with the default settings? Just the MATCH expression will be enough, we don't need the table schema or contents. Dan

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

On 12/15/2014 11:59 PM, Dan Kennedy wrote: On 12/15/2014 11:11 PM, Paul wrote: Hello, dear developers Recently I've stumbled upon a very rare and strange bug. The result of this is abnormal memory usage, that does not allow us to remove fair number of rows from a table due to the limit

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

lete == 1.2GiB. Or without the child records, just 1 page modified per delete, so closer to 400MiB of memory. Without the sub-transaction, the implicit sub-transaction created by each DELETE becomes the outermost and so the statement journal doesn't grow much at all. So not much memory used i

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

time. If the xBestIndex only uses one of the constraints, the planner should do the right thing. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 12/12/2014 03:31 AM, Nick wrote: On 11 Dec 2014, at 10:08, Dan Kennedy wrote: On 12/11/2014 05:49 AM, Nick wrote: On 10 Dec 2014, at 07:35, Dan Kennedy wrote: Strictly speaking the database file may not be well-formed even if there is no ongoing checkpoint. If: a) process A opens

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 12/11/2014 05:49 AM, Nick wrote: On 10 Dec 2014, at 07:35, Dan Kennedy wrote: Strictly speaking the database file may not be well-formed even if there is no ongoing checkpoint. If: a) process A opens a read transaction, b) process B opens and commits a write transaction

Re: [sqlite] Online/Hot backup of WAL journalling mode database

a frame in B's transaction that corresponds to the same database page. So it effectively copis only a subset of the modifications made by earlier transactions into the db file - not necessarily creating a valid db file. Dan. A) Ensure all processes besides the backup process have the databa

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 12/08/2014 09:55 PM, Nico Williams wrote: Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You could hack SQLite to do enforce unique constraints the same way as FKs. When adding an entry to a UNIQUE index b-tree, you check for a duplicate.

Re: [sqlite] Read overflow in CREATE VIRTUAL TABLE query (formerly Corrupting pointers to the lookaside smallacator)

r overread - there is no chance of an overwrite or database corruption. Easiest workaround is to append "()" to your CREATE VIRTUAL TABLE statement. i.e. CREATE VIRTUAL TABLE temp.vtb_enyqkyxs USING vtable_module_343() Dan. Thanks. On 11/27/2014 03:20 PM, Paul wrote: Here is how

Re: [sqlite] Corrupting pointers to the lookaside smallacator

see, token size is invalid: 41. This causes memcpy() down at #0 to access invalid, unmapped address. Hopefully it is only read overflow so the only consequence is just an occasional Segmentation fault when allocated piece of string is at the specific place: near the end of the page in front of

Re: [sqlite] Corrupting pointers to the lookaside smallacator

ect buffer overruns for all regular malloc/free allocations. If lookaside is omitted these fencepost checks will be done for small allocations as well, which may reveal the source of the memory corruption. An assert() will fail if any of the fencepost checks indicate a buffer overwrite.

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

On 11/26/2014 12:41 AM, Marcin Sobieszczanski wrote: Do you have a large cache-size configured? Yes: PRAGMA cache_size = 10 PRAGMA page_size = 16384 Thanks for reporting this. I think it should be fixed here: http://www.sqlite.org/src/info/623827192532f08b Dan

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

bytes (2147487744=2^31+4Ki, extra 4Ki from glibc), I guess, but int is not capable to hold more than 2Gi. Is this a known problem? It is now. Do you have a large cache-size configured? Thanks, Dan. ___ sqlite-users mailing list sqlite-users@sqli

Re: [sqlite] Null rowid when using CTEs

sub-select. It should probably be documented though. Dan. RP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sq

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 <kir...@aidagw.com> wrote: Good day, Full line m

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

(at least in debug) and return NULL if any statements are busy? I think we likely should. In both debug and non-debug builds. Thanks for reporting this. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-b

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

e has been improved since then. It's not quite as detailed as your proposal, but it is much better than it was. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing 3.8.7 gives 3.8.6

find libsqlite3.so.0.8.7. It's normal. I think that number is only supposed to change if the new release is not ABI compatible with the previous. It's a coincidence that "8.6" matches the tail of the previous release number. Earlier releas

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

l" causes a similar effect. Second section here: http://www.sqlite.org/vfs.html Dan. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Bug-993556-SQLite-crash-in-walIndexTryHdr-due-to-Windows-EXCEPTION-IN-PAGE-ERROR-exception-tp78695.html Sent from the S

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

on virtual tables? I don't think so. See the final paragraph here: http://sqlite.org/c3ref/c_vtab_constraint_support.html Looks like you have to implement the "OR REPLACE" support in the xUpdate method. Dan. Thanks, -dave ___ sqli

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.

could temporarily add a SELECT statement before the INSERT to check if a record with the specified "versionName" is already present in the table. Dan. There are multiple insert statements and multiple tables. The insert statements look like below. params = versionName, ver

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

se a patch and they turn out to be simpler than they look. Regards, Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

r query is now equivalent to "MATCH '101* h4x*'". This should only affect FTS tables that use custom tokenizers (not the default simple or porter tokenizers). Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8

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

UNION ALL SELECT id FROM t2 WHERE id=10 ORDER BY data is not a valid statement in SQLite. Dan. Thanks, Yuanzhong On Fri, Sep 19, 2014 at 8:16 PM, Yuanzhong Xu <y...@utexas.edu> wrote: Hi, In SQLite 3.8.6, suppose I have two tables in the same format: CREATE TABLE t1 (id I

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

actice. I think the only exception to this is if you use the incremental-blob API: http://www.sqlite.org/c3ref/blob_open.html Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fts5

of problems with FTS4 that could be fixed in FTS5, don't keep them to yourself! Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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)

to sqlite3_bind_text(), bind_result() or similar? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

or not the pragma runs during sqlite3_prepare() or sqlite3_step() depends on the pragma and on the specific release of SQLite. Do either of them explain what you are seeing? https://www.sqlite.org/pragma.html Dan. ___ sqlite-users mailing list sqlite-user

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

way or the other. I'd like to take advantage of this, but not if it is undefined behaviour that just happens to work. I don't see why that would be undefined behaviour. Adding "WHERE 0" to the SELECT would also work. Dan. ___ sqlite-use

Re: [sqlite] documentation bugs

tml http://www.sqlite.org/draft/fts3.html FTS4 has been updated to match the documentation. So that "unicode61" is now enabled by default or disabled by SQLITE_DISABLE_FTS3_UNICODE. As of 3.8.6, SQLITE_ENABLE_FTS4_UNICODE61 will no longer exist. http://www.sqlite.org/src/info/0cc023

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

h FTS4 and Rtree do this, and both work with SQLite connections in serialized mode. SQLite is re-entrant. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

to serialised mode. Looks like a corrupted heap. If you run your app under valgrind it will probably point you to the problem. Dan. Here is the backtrace: #0 0x7f4f78f0d08d in nanosleep () from /lib/x86_64-linux-gnu/libc.so.6 #1 0x7f4f78f0cf2c in sleep () from /lib/x86_64-linux

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

on Linux, set the TMPDIR (or SQLITE_TMPDIR) environment variable. Dan. Any advice? Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use

Re: [sqlite] Planner chooses incorrect index

because it contains embedded 0x00 bytes. And without the sqlite_stat4 data, it seems that SQLite picks the "good" query plan in either case. Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell ".dump" command instead? Thanks, Dan. sqlite_stat4 (be

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

FTS has a list of matches within the current row for each phrase in the query. Each match is stored as a column and token offset - the number of tokens that precede the match within the column text. Is that what you need? Do you have any ideas for an fts4 interface it? Dan. I implemented

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

t the output? Thanks. Dan. Result: -- Loading resources from my_init.sql Error: incomplete SQL: #.headers on *** glibc detected *** ./bin/sqlite3: double free or corruption (fasttop): 0x035ecf80 *** Same thing happens when typing an incomplete sql st

Re: [sqlite] Comments loss in sqlite_master table

ot;main." in the sqlite_master file. So I'd say if the comment appears inside the parenthesis it will probably be preserved. Of course, even if that is correct, it's just an artifact of the implementation, not part of a published interface that one would expect

Re: [sqlite] vtable vs. OR condition

for each vtab.*=? condition) and merges the results before returning them to the caller. Dan. Any experiences with this issue? Or any suggestions for other query processing engines I might use? Thanks, Andris ___ sqlite-users mailing list sqlite

Re: [sqlite] Pre-preparing querys

complicated queries. But I suspect it's very easy to find cases where the prepare() is at least as heavy as the step(). Dan. In theory very complex queries would be the exception, except that query planners long ago developed heuristic shortcuts. Mathematically, an N-way join is a c

Re: [sqlite] More LSM leak

east might be a real leak. I'm guessing the smaller.trace script is somehow created automatically. If it's easy enough to add the missing "close" commands (assuming they really are missing), I'd like to see if it still leaks. Thanks, Dan. $ cat smaller.trace | valgrind --leak-check=yes ~/a.o

Re: [sqlite] LSM Leaks memory

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

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

On 05/02/2014 07:30 PM, Stadin, Benjamin wrote: Is the delete marker also set on old keys on UPDATE? Or just DELETE -> INSERT? Internally, FTS implements an UPDATE as a DELETE followed by an INSERT. Dan. I ran into the ever-growing FTS index issue last year. I’m creating DB diffs wh

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

size (instead of 16 times). It is an interesting problem. And the above is just guesswork... It would be good to verify experimentally that the index really does grow indefinitely with this kind of input before trying to "fix" anything. Dan. __

Re: [sqlite] LSM bug

be changed to return LSM_FULL for any transaction too large to handle. But the real fix should be to change things so that LSM can begin flushing data to the database file mid-transaction. Dan. On Monday, April 28, 2014 01:41:02 PM sql...@charles.derkarl.org wrote: Hi, I'm not ex

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

OM a; sqlite> sqlite> SELECT * FROM a; a b -- -- 1 2 sqlite> SELECT * FROM b; a b -- -- 1 2 1 sqlite> Thanks for reporting this. Now fixed on the trunk. Ticket is here: http://www.sqlite.org/src/info/f67b41381acce

Re: [sqlite] Trigger cascade/nesting

You could try: CREATE TRIGGER audit_test_a_audit_update AFTER UPDATE OF (all-columns-except-last_changed) ON ... Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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.

that uses it. Various other problems can likely arise if the optimizer starts commuting expressions and so on, which it sometimes does. Of course I don't know exactly what kinds of queries you are expecting, but this seems like the kind of thing that will come back and bite you to me. Dan

Re: [sqlite] Using ICU case folding support

ible system. Instead, we would name it "unicode62" or similar, to be sure that databases created using the old version continue to use it. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using ICU case folding support

nsion does not automatically override the "NOCASE" collation sequence the same way it overrides the built-in upper() and lower() functions. If we were to add case-folding to ICU collations, we would have to use a different name. It does look li

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

ng the entire database. 4) Would it just be simpler to switch to WAL mode? There's a good chance it will be, yes. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using ICU case folding support

null COLLATE NOCASE"); As noted in the documentation, this is not Unicode-aware, which is why I turned to the ICU extension. What should be the equivalent usage where sqlite does case folding using ICU? I don't think there is a way to do that with the current SQLite ICU extens

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

space required to store the index itself in the database, SQLite requires roughly 2N bytes of free space wherever temp files are created on your system to work with. It uses this space as temporary storage to sort the index entries. Dan. ___ sql

Re: [sqlite] very slow fdsync() calls

al_mode=PERSIST;", if there will only ever be a single connection to the database. Or you could build with SQLITE_DISABLE_DIRSYNC, which omits all syncs on directories. Of course, that will slightly increase the chances of a power failure causing datab

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

given point. During run time I find that the following exception occurs. vtable constructor failed: MapLevel_Idx3 How is virtual table MapLevel_Idx3 created? Is it an rtree table? SQLite version? Dan. Occurence ratio is about 1 in 1 queries on the range index. Am I doing something

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.

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

data. The query didn't reach the step call. Trying simple "SELECT r.name FROM resource AS r WHERE r.name = ?" gets "Out of memory" too calling preparev2. Does the "out of memory" error come from sqlite3_prepare_v2() directly or f

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

change either of these without creating either an incompatible file-format or a broken build, it's probably Ok. Does look a bit odd though. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/

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

mpt to use SetEndOfFile() to extend the size of the *-shm file to 32KB (from 0KB). I have no idea why it is failing. What version of SQLite is this? Newer versions provide more detail for debugging, particularly if you also turn on sqlite3_log() logging: http://sqlite.org/c3ref/log.html Dan. OPEN

Re: [sqlite] help needed for major SQLite problem

I can fix this problem? Looks like GetFileAttributesEx() might be throwing an ERROR_ACCESS_DENIED exception. Maybe a virus scanner or some other background process had temporarily locked the database file. Dan. ___ sqlite-users mailing list sqlite

Re: [sqlite] Virtual Table xRowid

if rowids are inconsistent. Dan. The problem itself is detailed below. Thanks, Kevin --- I am creating virtual table that unpacks the contents of another table/view. As a simple example, consider the following: create table t(week, performance_csv

Re: [sqlite] Understanding transactions

to the caller. And since SQLITE_NOMEM also automatically rolls back the transaction, no chance of corruption in this case either. http://www.sqlite.org/src/info/cb494e10d7 Does that match everyone's results? Dan. ___ sqlite-users mailing list

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

New writes are possible while a checkpoint is ongoing. Although, if you are using synchronous=FULL, they might be really slow, depending on the file-system. synchronous=NORMAL or OFF should be Ok. Something to test if it matters to you. Dan. ___ sqlite-

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

iled, which can shed light on the problem. Another thing to do is to try compiling with the following defines: -DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1 This causes the Windows VFS module to print various messages to standard output that should help to figure out what is happening.

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

iled, which can shed light on the problem. Another thing to do is to try compiling with the following defines: -DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1 This causes the Windows VFS module to print various messages to standard output that should help to figure out what is happening.

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 http

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

with full source builds, not the amalgamation. As it causes code to be omitted from parse.y. There are a few others this is true of as well - OMIT_VIRTUALTABLE, OMIT_TRIGGER etc. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http

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

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