Re: [sqlite] JSON indexing

2014-10-21 Thread Dominique Devienne
On Tue, Oct 21, 2014 at 3:38 PM, Mike Jarmy wrote: > Has there been any discussion of adding JSON indexing to sqlite, similar to > the way Full Text Search and R-Tree are available? > > Postgres 9.4 beta has a very nice facility for creating indexes on json > column types: > >

Re: [sqlite] Index without backing table

2014-10-31 Thread Dominique Devienne
On Fri, Oct 31, 2014 at 1:35 PM, Alessandro Marzocchi < alessandro.marzoc...@gmail.com> wrote: > I think what he wants is a way to create an index on a virtual table. > +1 to that. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Dominique Devienne
On Thu, Nov 13, 2014 at 2:33 PM, RSmith wrote: > On 2014/11/13 15:01, Dinesh Navsupe wrote: > >> My need is 23 decimal digits of precision. We work on complex payout >> calculation engine where in formula outputs are quite large numbers and >> clients do not want to round

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-13 Thread Dominique Devienne
On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin wrote: > On 13 Nov 2014, at 1:01pm, Dinesh Navsupe > wrote: > > > My need is 23 decimal digits of precision. We work on complex payout > > calculation engine where in formula outputs are quite large

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-14 Thread Dominique Devienne
On Thu, Nov 13, 2014 at 6:53 PM, RSmith wrote: > By the way, my Oracle friends should intersect here if need be, but I > believe the oracle method of /decimal(n,m)/ is simply a representation > directive and constraint, there is no native datatype that actually stores > or

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

2014-11-27 Thread Dominique Devienne
On Thu, Nov 27, 2014 at 12:57 PM, Dan Kennedy wrote: > Thanks for tracking this down.Should be fixed here: > > http://www.sqlite.org/src/info/f095cde579e7417306 Dan, the test reads: ifcapable fts3 { ... } yet both the comments and code use fts4. Should that be fts4,

Re: [sqlite] Bug report: USBAN failure

2014-12-02 Thread Dominique Devienne
On Tue, Dec 2, 2014 at 2:47 PM, Richard Hipp wrote: > On Mon, Dec 1, 2014 at 5:46 PM, Hadley Wickham > wrote: > > [...] has started running all R packages with USBAN. This reveals a > problem in sqlite.c > > I'm not sure what USBAN is > Most likely a typo.

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Dominique Devienne
On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein wrote: > On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson < > > If you are a coder then it is a relatively straight forward process > > Loop through each table > >Loop through each column > > This is the part I am having

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Dominique Devienne
On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov wrote: > Not particularity the answer to your question, but rather a method you > or others might use. > I once implemented a virtual table "allvalues" that outputs all > database values with (hope self-explaining) fields > >

Re: [sqlite] Select count(*)

2014-12-11 Thread Dominique Devienne
On Thu, Dec 11, 2014 at 5:03 PM, Gabriel Corneanu wrote: > I asked a similar question some time ago... > See here: > https://www.mail-archive.com/sqlite-users@sqlite.org/msg77488.html >> not a problem for rowid/pk (which are not allowed to be NULL), but it >>

Re: [sqlite] Counting rows

2014-12-11 Thread Dominique Devienne
On Thu, Dec 11, 2014 at 4:19 PM, Simon Slavin wrote: > In my table which had about 300 million (sic.) rows I did this > SELECT count(*) FROM myTable; > to count the number of rows. After half an hour it was still processing > and I had to kill it. > I have a little

Re: [sqlite] '.timer on' in the shell tool

2014-12-15 Thread Dominique Devienne
On Mon, Dec 15, 2014 at 10:24 AM, Simon Slavin wrote: > > On 15 Dec 2014, at 9:20am, Donald Shepherd > wrote: > > - Units are seconds. > > - IIRC user time is time spent in SQLite code, sys time is time spent in > > system (OS) calls. Both can

Re: [sqlite] Should .dump preserve foreign_keys pragma?

2015-01-09 Thread Dominique Devienne
On Fri, Jan 9, 2015 at 11:35 AM, Niall O'Reilly wrote: > At Thu, 08 Jan 2015 15:55:00 -0700, > Keith Medcalf wrote: > > > > when you load a dump file you need to have that foreign > > key enforcement off in order to be able to load the database. This > > is because the

Re: [sqlite] Should .dump preserve foreign_keys pragma?

2015-01-09 Thread Dominique Devienne
On Fri, Jan 9, 2015 at 11:57 AM, Dominique Devienne <ddevie...@gmail.com> wrote: > On Fri, Jan 9, 2015 at 11:35 AM, Niall O'Reilly <niall.orei...@ucd.ie> > wrote: > >> At Thu, 08 Jan 2015 15:55:00 -0700, >> Keith Medcalf wrote: >> > >> > when

Re: [sqlite] SQLite 3.8.8 expected in January

2015-01-09 Thread Dominique Devienne
On Fri, Jan 9, 2015 at 6:23 PM, Richard Hipp wrote: > We hope to release SQLite version 3.8.8 sometime later this month > (January). A change-log is available at > https://www.sqlite.org/draft/releaselog/current.html Could you please provide more info about stmt_scanstatus()?

Re: [sqlite] aggregate functions with DISTINCT

2015-01-12 Thread Dominique Devienne
On Mon, Jan 12, 2015 at 2:30 PM, Richard Hipp wrote: > Are there any other SQL database engines that allow multi-argument > aggregate functions? What do those systems do? The "that take a single argument" fragment seems to imply DISTINCT is not allowed my multi-args aggregate

Re: [sqlite] aggregate functions with DISTINCT

2015-01-12 Thread Dominique Devienne
On Mon, Jan 12, 2015 at 3:36 PM, Staffan Tylen wrote: > On Mon, Jan 12, 2015 at 3:25 PM, Clemens Ladisch > wrote: > > > Testing shows that the DISTINCT > > applies to the function, not to individual parameters,

Re: [sqlite] aggregate functions with DISTINCT

2015-01-12 Thread Dominique Devienne
On Mon, Jan 12, 2015 at 2:30 PM, Richard Hipp wrote: > Certainly the group_concat() case you cite, with a constant second > term makes sense. But any code I write needs to work *in general*, > and not for just that one special case. > Is it not possible to detect the

Re: [sqlite] aggregate functions with DISTINCT

2015-01-12 Thread Dominique Devienne
On Mon, Jan 12, 2015 at 4:13 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 1/12/2015 9:53 AM, Dominique Devienne wrote: > >> My little brain has no idea how the "a;b:c/c,d" came about from the input >> rows, so I don't find it logical at all myself... &

Re: [sqlite] Whish List for 2015

2015-01-21 Thread Dominique Devienne
On Mon, Jan 19, 2015 at 2:01 AM, Stefan Keller wrote: > Adding JSON to SQLite (like in PostgreSQL) ultimately needs a > JavaScript parser - and that seems to be against the goal of SQLite > being slim. > It needs a JSON parser, not a JavaScript parser. That's much smaller.

Re: [sqlite] Huge WAL log

2015-01-23 Thread Dominique Devienne
On Thu, Jan 22, 2015 at 6:49 PM, Richard Hipp wrote: > Let me know if that helps. Note that I have only quickly read over my > writing so there is a high probability of typos, which I will be happy > to correct when brought to my attention. Thanks for the new doc. Very

Re: [sqlite] ROWID-based queries slow in FTS db

2015-01-30 Thread Dominique Devienne
On Fri, Jan 30, 2015 at 4:38 PM, Dan Kennedy wrote: > On 01/29/2015 02:29 AM, farkas andras wrote: > >> [...] but searches based on ROWID are atrociously slow and hog massive >> amounts of memory [...] >> > > Looks like range constraints on rowids were only taken into

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

2015-02-04 Thread Dominique Devienne
On Wed, Feb 4, 2015 at 9:18 AM, Gerald Bauer wrote: > [1a] New schemadoc Ruby Gem -> https://github.com/rubylibs/schemadoc > [DD] 404 for me > [1b ] Templates -> >

Re: [sqlite] equivalent for JOIN LATERAL

2015-02-09 Thread Dominique Devienne
On Mon, Feb 9, 2015 at 6:12 AM, James K. Lowden wrote: > > If not, would it be possible too much effort ? > > I'm guessing the answer is No because the prerequisites are missing. > [DD] And that's the crux of it. SQLite has virtual tables, and as such can approximate

Re: [sqlite] SQLCipher - Full Database Encryption for SQLite

2015-02-09 Thread Dominique Devienne
On Mon, Feb 9, 2015 at 11:34 AM, Simon Slavin wrote: > SQLCipher is an open source extension to SQLite that provides transparent > 256-bit AES encryption of database files. [...] > > The API extends SQLite by adding PRAGMAs and C functions to configure the > encryption. >

Re: [sqlite] binding multiple values in a query

2015-02-12 Thread Dominique Devienne
On Thu, Feb 12, 2015 at 2:04 AM, Richard Hipp wrote: > On 2/11/15, Igor Tandetnik wrote: > > On 2/11/2015 5:46 PM, Jono Poff wrote: > >> I wonder if anybody could give me a simple example in C to bind an array > >> of values to a prepared statement? > >>> >

Re: [sqlite] unreached code in sqlite3.c?

2015-02-12 Thread Dominique Devienne
On Thu, Feb 12, 2015 at 8:39 PM, Tim Streater wrote: > On 12 Feb 2015 at 19:14, R.Smith wrote: > > On 2/12/2015 8:50 PM, Doug Currie wrote: > >> It's easy enough to fix if you want 0 to be a valid value for > TERM_VNULL: > >> > >> #if TERM_VNULL > >>

[sqlite] Performance issue

2015-04-08 Thread Dominique Devienne
On Wed, Apr 8, 2015 at 11:16 AM, Jeff Roux wrote: > # For mysql, I use: > /etc/init.d/mysql stop; /etc/init.d/mysql start; \ > time echo 'SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan \ > as item FROM flows WHERE timestamp>=1383770600 AND \ > timestamp<=1384770600 AND

[sqlite] Performance issue

2015-04-08 Thread Dominique Devienne
On Wed, Apr 8, 2015 at 1:24 PM, R.Smith wrote: > On 2015-04-08 11:35 AM, Dominique Devienne wrote: > >> On Wed, Apr 8, 2015 at 11:16 AM, Jeff Roux wrote: >> >>> time echo 'SELECT ... FROM flows WHERE timestamp>=1383770600 AND \ >>> timestamp<

[sqlite] Performance issue

2015-04-08 Thread Dominique Devienne
On Wed, Apr 8, 2015 at 2:09 PM, Richard Hipp wrote: > On 4/8/15, Dominique Devienne wrote: > > With a LIMIT clause, in > > such a GROUP BY ORDER BY returning a large result set, would SQLite: > > 1) sort the whole result-set and then keep only the first top-N rows?

[sqlite] Request: Metadata about C API constants and functions

2015-04-10 Thread Dominique Devienne
On Fri, Apr 10, 2015 at 2:42 AM, Richard Hipp wrote: > On 4/9/15, Roger Binns wrote: > > For each function, its name... > > As it happens, the SQLite documentation build process already parses > out most of this and puts it into a database already. If you > check-out the documentation sources

[sqlite] Request: Metadata about C API constants and functions

2015-04-10 Thread Dominique Devienne
On Fri, Apr 10, 2015 at 9:48 AM, Zsb?n Ambrus wrote: > On 4/10/15, Dominique Devienne wrote: > > But that's build time. There is no way (i.e. pragma) to list registered > SQL functions at runtime. > > Um, you're talking about SQL functions. But I think Roger asked

[sqlite] Problems with pragma journal_mode

2015-04-16 Thread Dominique Devienne
On Thu, Apr 16, 2015 at 11:10 AM, Janke, Julian wrote: > I want to change the journal mode of a database to WAL. So I did the > following: > > rc = sqlite3_open(dbPath, ); > rc = sqlite3_exec(db, "PRAGMA journal_mode=WAL;", testCallbackPrint, 0, > ); > [?] > sqlite3_close(db); > > Unfortunately,

[sqlite] Problems with pragma journal_mode

2015-04-17 Thread Dominique Devienne
On Fri, Apr 17, 2015 at 12:59 PM, Janke, Julian wrote: > Unfortunately, I can't run the shell tool on the es and try the same > sequence of commands. > Why? Don't you have some kind of shell on that embedded system that can run executables? According to

[sqlite] Thoughts on storing arrays of complex numbers

2015-04-24 Thread Dominique Devienne
On Fri, Apr 24, 2015 at 3:37 PM, Drago, William @ CSG - NARDA-MITEQ < William.Drago at l-3com.com> wrote: > I'm trying to avoid re-inventing the wheel. Is there a best or generally > accept way to store arrays of complex numbers? I'm considering the > following: > > I could have two blob fields

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

2015-04-28 Thread Dominique Devienne
On Tue, Apr 28, 2015 at 4:16 AM, James K. Lowden wrote: > A major hurdle is the > memory model: because array-programming libraries normally mandate the > data be in contiguous memory, there's a cost to converting to/from the > DBMS's B+ tree. The more array-like the physical storage of the

[sqlite] DB Browser for SQLite 3.6.0 released

2015-04-29 Thread Dominique Devienne
On Tue, Apr 28, 2015 at 12:44 AM, wrote: > Quick FYI. We've just released DB Browser for SQLite, version 3.6.0. > I tried pragma threads (Win64 build), and it returns nothing (unlike pragma page_size for example), while sqlite3.exe returns 0 (the default). Doesn't support worker threads for

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

2015-04-30 Thread Dominique Devienne
On Thu, Apr 30, 2015 at 2:52 AM, James K. Lowden wrote: > On Tue, 28 Apr 2015 09:24:56 +0200 Dominique Devienne > wrote: > > On Tue, Apr 28, 2015 at 4:16 AM, James K. Lowden < > jklowden at schemamania.org> wrote: > > > A major hurdle is the memory mo

[sqlite] Regarding root in transitive closure extension of SQLite

2015-08-06 Thread Dominique Devienne
On Thu, Aug 6, 2015 at 8:55 AM, Sairam Gaddam wrote: > I have enabled the transitive closure extension and I followed the steps > regarding querying the virtual table mentioned in closure.c file as follows > Given that SQLite supports CTE's now, why use that extension? AFAIK, it was to add

[sqlite] Database sybchronisation

2015-08-13 Thread Dominique Devienne
On Thu, Aug 13, 2015 at 4:52 AM, Chris Parsonson wrote: > I need to be able to synchronise some fairly simple table in two databases. > Can someone help please > The new RBU [1] extension, coupled with the new sqldiff utility, might be of interest too.--DD [1] http://www.sqlite.org/rbu.html

[sqlite] Database sybchronisation

2015-08-13 Thread Dominique Devienne
On Thu, Aug 13, 2015 at 11:12 AM, wrote: > On Thu Aug 13, 2015 at 10:06:44AM +0200, Dominique Devienne wrote: > > The new RBU [1] extension, coupled with the new sqldiff utility, might > be of interest too.--DD > > Is there a table of contents or index of SQLite ex

[sqlite] Lua inside SQLite

2015-08-24 Thread Dominique Devienne
On Sun, Aug 23, 2015 at 10:55 PM, Richard Hipp wrote: > Regular SQL functions always return scalars in SQLite. > > See https://www.sqlite.org/src/artifact/b8fb7befd85b3a9b for an > example of how to implement table-valued functions. This is a new > feature so there is no documentation on it

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

2015-08-27 Thread Dominique Devienne
Recently upgraded, and we ran into failures because references to vtables (via opcode VOpen's p4 column) changed from vtab:module_ptr:vtable_ptr to just vtab:vtable_ptr. What was the intent behind this change? How can code one determine the module the vtable is coming from now? This is not a

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

2015-08-27 Thread Dominique Devienne
On Thu, Aug 27, 2015 at 1:08 PM, Richard Hipp wrote: > On 8/27/15, Dominique Devienne wrote: > > > > I understand that https://www.sqlite.org/opcode.html doesn't explicitly > say > > what VOpen's p4 column will contain, so I guess one could argue this is > > u

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

2015-08-27 Thread Dominique Devienne
On Thu, Aug 27, 2015 at 3:37 PM, Richard Hipp wrote: > On 8/27/15, Clemens Ladisch wrote: > > Dominique Devienne wrote: > >> how can we programatically reliably discover which (v)tables a view > >> accesses, staying in documented behavior land? > > > >

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

2015-08-27 Thread Dominique Devienne
On Thu, Aug 27, 2015 at 4:20 PM, Hick Gunter wrote: > But you have to run the query as opposed to just parsing EXPLAIN > The doc says: The authorizer callback is invoked as SQL statements are being compiled by sqlite3_prepare() [and co.] So the query to prepare is different indeed (drop

[sqlite] Version Control

2015-08-28 Thread Dominique Devienne
On Fri, Aug 28, 2015 at 9:56 AM, Scott Doctor wrote: > Slightly off topic, but I am looking at version control systems. SQLite > looks like it is using Fossil. How does Fossil compare to using Git, > Mercurial, or Subversion? > http://fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.wiki ? --DD

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

2015-08-31 Thread Dominique Devienne
On Thu, Aug 27, 2015 at 4:21 PM, Dominique Devienne wrote: > On Thu, Aug 27, 2015 at 3:37 PM, Richard Hipp wrote: > >> On 8/27/15, Clemens Ladisch wrote: >> > Dominique Devienne wrote: >> >> how can we programatically reliably discover which (v)tabl

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Dominique Devienne
On Wed, Dec 9, 2015 at 3:50 AM, Richard Hipp wrote: > On 12/8/15, Bart Smissaert wrote: > > So, what/where is that standard output channel? > SQLite does not have any facilities for debugging in a GUI on Windows. Any chance to add a way to redirect debugging / troubleshooting output to a file

[sqlite] How to see SQLite debugging information

2015-12-09 Thread Dominique Devienne
On Wed, Dec 9, 2015 at 12:59 PM, Graham Holden wrote: > If you have, or create, a VB6 standalone EXE that calls SQLite, you should > be possible to get it to show stdout/stderr. > OP already explained VB code is wrapped in an ActiveX dynamically used/loaded by MS Excel. So no user-controlled

[sqlite] Checking Foreign Keys

2015-12-13 Thread Dominique Devienne
On Sun, Dec 13, 2015 at 1:17 PM, Cecil Westerhof wrote: > At the moment I am working with Foreign Keys. They need to be enabled. When > you do not do this it is possible to enter records that break the Foreign > Key rules. Is there a way to check for this. > > For example in a session where

[sqlite] Using incremental BLOB functions against a BLOB column in a virtual table

2015-02-25 Thread Dominique Devienne
On Wed, Feb 25, 2015 at 7:52 AM, Hick Gunter wrote: > And the "explain" output will contain virtual table opcodes > I use this technique to discover which tables and virtual tables a view accesses (instead of trying to parse the query.) I execute "explain select rowid from $viewname", and look

[sqlite] User-defined types

2015-06-04 Thread Dominique Devienne
On Thu, Jun 4, 2015 at 3:04 AM, Darko Volaric wrote: > In my case I'm already modifying and maintaining my own version of SQLite. > [...]. The last time I brought these ideas up I was > practically chased off by a mob waving pitchforks and torches. Apparently > almost no-one thinks user defined

[sqlite] User-defined types

2015-06-04 Thread Dominique Devienne
On Thu, Jun 4, 2015 at 10:20 AM, Christopher Vance wrote: > If you really want your own types, you could always bundle with ASN.1 and > store the result as a blob. > Or Protobuf, or ... But you're back to option 1, you must store somewhere that knowledge, and it's an app-convention, SQL and

[sqlite] User-defined types -- in Andl

2015-06-09 Thread Dominique Devienne
On Tue, Jun 9, 2015 at 1:50 PM, wrote: > Thank you. Exactly so. One of the problems with this kind of project is > finding 'good enough' challenges to tackle. > See also from the CTE doc: - https://www.sqlite.org/lang_with.html#sudoku - https://www.sqlite.org/lang_with.html#mandelbrot Thanks,

[sqlite] full table scan ignores PK sort order?

2015-03-03 Thread Dominique Devienne
On Tue, Mar 3, 2015 at 11:27 AM, Jan Asselman wrote: > - the full table scan returns rows in rowID order, which is the order in > which the rows were added to the table > You cannot rely on that (the "the order in which rows were added" part). At best it's an implementation detail. If you want

[sqlite] FastBit-based vtable impl [WAS: Multi-table index ersatz?]

2015-03-04 Thread Dominique Devienne
On Wed, Mar 4, 2015 at 10:08 AM, Hick Gunter wrote: > Properly implemented virtual tables do support indexing, but you have to > write the code to support that yourself. > > I have personally implemented an index based on the fastbit package which > is ideally suited to retrieving large data

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

2015-03-06 Thread Dominique Devienne
On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: > On 3/5/15, Mike Nicolino wrote: > > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to > be a > > bug with Virtual Tables. Queries using 'like' in the where clause are > not > > getting the like clause passed to

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

2015-03-06 Thread Dominique Devienne
On Fri, Mar 6, 2015 at 10:12 AM, R.Smith wrote: > On 2015-03-06 09:42 AM, Dominique Devienne wrote: > >> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: >> >> >>> The LIKE operator can be overridden by the application to mean >>> anything t

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

2015-03-06 Thread Dominique Devienne
On Fri, Mar 6, 2015 at 1:21 PM, Hick Gunter wrote: > And then there remain to be considered the effects of the pragma > CASE_SENSITIVE_LIKE > Good point. But that's no different from the case when an application overrides the LIKE behavior via a custom function, and the vtable can similarly

[sqlite] SQL Syntax To Copy A Table

2015-03-30 Thread Dominique Devienne
On Thu, Mar 26, 2015 at 4:29 PM, Nigel Verity wrote: > My requirement is to take periodic snapshots of a names and addresses > table, to be stored in the same database as the master. > Perhaps also look into https://www.sqlite.org/backup.html, which doesn't qualify for "the same database", but

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

2015-05-15 Thread Dominique Devienne
On Fri, May 15, 2015 at 8:34 PM, Eric Hill wrote: > Is there something I can do to make the debugger work? > See this thread: http://sqlite.1065341.n5.nabble.com/Windows-Specific-2-c-files-Amalgamation-td67626.html Basically, use a multi-file amalgamation, that uses files with fewer than 64K

Re: [sqlite] compiling the non-AMALGAMATION on visual studio

2013-10-29 Thread Dominique Devienne
On Mon, Oct 28, 2013 at 10:38 PM, David Clark wrote: > I am trying to compile the source tree on visual studio with makefile.msc > and I get the following: > [...] > Why am I doing this: > I have a database lock error coming from a update query and I am not sure > why. So

Re: [sqlite] Feature Request: Binding Arrays

2013-10-29 Thread Dominique Devienne
On Tue, Oct 29, 2013 at 2:06 AM, Olaf Schmidt <n...@vbrichclient.com> wrote: > Am 16.10.2013 10:40, schrieb Dominique Devienne: > If I somehow missed a better work-around to this lack of array-binding, I'm > >> also interested of course, but obviously I'd prefer real array

Re: [sqlite] Feature Request: Binding Arrays

2013-10-31 Thread Dominique Devienne
On Thu, Oct 31, 2013 at 2:45 AM, Olaf Schmidt <n...@vbrichclient.com> wrote: > Am 29.10.2013 13:19, schrieb Dominique Devienne: > >> [...] >> First off, when you use functions like this, you basically rule >> out index use, even if some_column is indexed. That's not

Re: [sqlite] Feature Request: Binding Arrays

2013-11-03 Thread Dominique Devienne
On Sat, Nov 2, 2013 at 4:59 AM, Olaf Schmidt <n...@vbrichclient.com> wrote: > Am 31.10.2013 14:09, schrieb Dominique Devienne: > > [Userdefined functions in conjunction with fast Exists-checks > in "Userland" - vs. SQLites built-in indexing in case of In (List)] >

Re: [sqlite] Groups and members

2013-11-06 Thread Dominique Devienne
On Wed, Nov 6, 2013 at 12:55 PM, Ulrich Goebel wrote: > Is it a matter of a "simple" SELECTs with joined tables? Have I to think > about subqueries? Or even Compund SELECTs (UNION, INTERSECT)? > > It would be great to get some hints! Sounds like a job for

Re: [sqlite] Groups and members

2013-11-06 Thread Dominique Devienne
On Wed, Nov 6, 2013 at 2:45 PM, Igor Tandetnik wrote: > select g_id, :mypid, > (case when r.p_id is null then 'no' else 'yes' end) is_member > from g left join r on (g.g_id = r.g_id and r.m_id = :mypid); > order by (r.p_id is null), g_id; > That's what I said! :) (our

Re: [sqlite] Groups and members

2013-11-06 Thread Dominique Devienne
On Wed, Nov 6, 2013 at 3:53 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 11/6/2013 8:50 AM, Dominique Devienne wrote: > >> On Wed, Nov 6, 2013 at 2:45 PM, Igor Tandetnik <i...@tandetnik.org> >> wrote: >> >> select g_id, :mypid, >>>

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Dominique Devienne
On Fri, Nov 15, 2013 at 12:47 PM, Luís Simão wrote: > SQLite answers those question in: > http://www.sqlite.org/draft/withoutrowid.html Interesting, thanks. This was discussed early this year I recall ( http://www.mail-archive.com/sqlite-users@sqlite.org/msg75669.html),

Re: [sqlite] Index performance

2013-11-15 Thread Dominique Devienne
On Fri, Nov 15, 2013 at 1:55 PM, L. Wood wrote: > Suppose I have a table Foo with two columns: state, company_stock_symbol. > (There are other columns but let's ignore them.) > > The states are relatively few (50). There are tens of thousands of > companies. > I was just

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Dominique Devienne
On Fri, Nov 15, 2013 at 3:52 PM, Jean-Christophe Deschamps wrote: > At 15:45 15/11/2013, you wrote: > > last_insert_rowid() is needed for autoincremented keys. >> >> WITHOUT ROWID tables do not have an autoincrementing key. Your program >> has to generate or get the key in

Re: [sqlite] Using EXPLAIN to validate vtable xFilter [WAS: EXPLAIN QUERY PLAN]

2013-11-18 Thread Dominique Devienne
On Mon, Nov 18, 2013 at 8:46 AM, Hick Gunter wrote: > I use EXPLAIN to make sure that my virtual tables' xFilter methods get > called with the proper parameters. > Hi Hick, Would you mind sharing some details on this please? As a heavy vtable user, and those vtables

Re: [sqlite] Using EXPLAIN to validate vtable xFilter [WAS: EXPLAIN QUERY PLAN]

2013-11-18 Thread Dominique Devienne
On Mon, Nov 18, 2013 at 11:45 AM, Hick Gunter wrote: > This is an EXPLAIN QUERY PLAN of one of the statements used to generate a > report: > [...] > There are several CASE...END clauses and a UNION (of transaction data) > cross joined to in-memory tables (of configuration

Re: [sqlite] count (*) performance

2013-11-27 Thread Dominique Devienne
On Wed, Nov 27, 2013 at 9:35 AM, Clemens Ladisch wrote: > veeresh kumar wrote: > > I see that in release history for 3.8.1 below item. > > "Estimate the sizes of table and index rows and use the smallest > applicable B-Tree for full scans and "count(*)" operations." > > > >

Re: [sqlite] SQLITE_ENABLE_STAT3/4 causing expensive statement recompilation

2013-12-12 Thread Dominique Devienne
On Thu, Dec 12, 2013 at 4:19 PM, Richard Hipp wrote: > On Wed, Dec 11, 2013 at 3:00 PM, Nick Hutchinson >wrote: > > > I ran my test app under Visual Studio's profiler, and saw that a > > substantial amount of time is spent in calls to

[sqlite] order by col desc limit 1 logically equivalent to max(col)

2013-12-19 Thread Dominique Devienne
In the two queries below, there's a 5x performance difference. But logically, it seems to me that the limit 1 on the order by is logically equivalent to a min or max depending on ascending or descending ordering, and is "optimize-able" by using an appropriate transformation. This use case is not

Re: [sqlite] order by col desc limit 1 logically equivalent to max(col)

2013-12-19 Thread Dominique Devienne
On Thu, Dec 19, 2013 at 12:34 PM, Clemens Ladisch <clem...@ladisch.de>wrote: > Dominique Devienne wrote: > > In the two queries below, there's a 5x performance difference. > > > select max(id) from t100m limit 1; > > select id from t100m order by id desc limit 1;

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

2014-01-09 Thread Dominique Devienne
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 changed the results. > > Here is a test case: > >

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

2014-01-09 Thread Dominique Devienne
On Thu, Jan 9, 2014 at 10:02 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 01/09/2014 03:38 PM, Dominique Devienne wrote: > >> Someone will soon point out exactly what commit/bug was fixed in SQLite >> I'm sure. >> > > Probably this: > http://www

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

2014-01-09 Thread Dominique Devienne
On Thu, Jan 9, 2014 at 10:18 AM, Dominique Devienne <ddevie...@gmail.com>wrote: > On Thu, Jan 9, 2014 at 10:02 AM, Dan Kennedy <danielk1...@gmail.com>wrote: > >> On 01/09/2014 03:38 PM, Dominique Devienne wrote: >> >>> Someone will soon point out exac

Re: [sqlite] Dramatic performance difference between different "PRAGMA synchronous" settings for bulk inserts

2014-01-20 Thread Dominique Devienne
On Sun, Jan 19, 2014 at 10:59 PM, Mario M. Westphal wrote: > If I set wal_autocheckpoint=1, I will get 1/10 of the synchs and WAL > file of about 10 MB, correct? http://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint states it's a page count, so that depends on

Re: [sqlite] Database Grammar 101

2014-02-05 Thread Dominique Devienne
On Sat, Feb 1, 2014 at 3:09 PM, Simon Slavin wrote: > But over all, English is an acquisitive (unlike German) evolving (unlike > French) language. > Hundreds of new words in French dictionaries for 2014 (links to other recent years at the bottom):

Re: [sqlite] Database Grammar 101

2014-02-05 Thread Dominique Devienne
On Wed, Feb 5, 2014 at 1:27 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 5 Feb 2014, at 12:08pm, Dominique Devienne <ddevie...@gmail.com> wrote: > > Hundreds of new words in French dictionaries for 2014 > > Apologies. Technical distinction due to French be

Re: [sqlite] Send Mail from sqlite

2014-02-07 Thread Dominique Devienne
On Fri, Feb 7, 2014 at 11:06 AM, Simon Slavin wrote: > On 7 Feb 2014, at 9:59am, Vairamuthu wrote: > > > Thanks for your response, it will be great help if you can get me some > > sample code or algorithms, on that. > > That would depend on what

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Dominique Devienne
On Mon, Feb 10, 2014 at 10:13 AM, Hick Gunter wrote: > You can choose the source of fragmentation: loosing close to 1 row per > page (better in bigger pages) or having ununsed space due to nonadjacent > deletes (better in smaller pages) > For the latter, you do have

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Dominique Devienne
On Mon, Feb 10, 2014 at 4:23 PM, Richard Hipp wrote: > Proposed Change To Address The Problem: > > When launching sqlite3.exe with a double-click, have it open a standard > database in a standard place instead of an in-memory database as you would > get when launching

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Dominique Devienne
On Mon, Feb 10, 2014 at 5:15 PM, Richard Hipp wrote: > What if, instead of opening a standard database, the sqlite3.exe > command-line shell just issued a warning message reminding the user that > they are working on a transient in-memory database and suggesting the use > of the

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-11 Thread Dominique Devienne
On Mon, Feb 10, 2014 at 9:23 PM, Richard Hipp wrote: > (1) Reword the banner to make it more terse and to try to avoid > "banner fatigue". > Thanks. > (2) If opened with no command-line arguments (and hence on an in-memory > database) output a warning in the banner. > >

Re: [sqlite] When is the decltype recognized?

2014-02-13 Thread Dominique Devienne
On Thu, Feb 13, 2014 at 11:44 AM, Clemens Ladisch wrote: > By design, SQLite uses dynamic typing, and keeps only the actual type of > the value. The declared type is a property of the *column*, not of the > value itself, so it is lost as soon as the value is no longer

[sqlite] Explain Query Plan with virtual tables involved

2014-02-17 Thread Dominique Devienne
Would it be possible to have plans for virtual tables more in line with plans for regular tables? With virtual tables, the plan is always a SCAN TABLE, while for regular tables it's either a SCAN TABLE for full scans, and a SEARCH TABLE for an indexed plan. Because I don't yet use idxStr but

[sqlite] Error: datatype mismatch

2014-02-28 Thread Dominique Devienne
Ran into this error, which surprised me since I thought SQLite's dynamic typing allowed any value type to be stored in any column. Either the PK or the FK seem to have some influence (with pragma foreign_keys on or off), which I didn't expect, since the entered FK value does match the parent row's

Re: [sqlite] Why does type affinity declared on a foreign key column affect join speed?

2014-02-28 Thread Dominique Devienne
On Fri, Feb 28, 2014 at 6:38 AM, Bruce Sutherland wrote: > We are tracking manufactured components with an alphanumeric serial number, > which gives us a natural key. Naturally we set type affinity TEXT on the key > column. There are many tables linked

[sqlite] Virtual Table "Functions"

2014-02-28 Thread Dominique Devienne
Can someone tell me how the statement below works? > From Eleytherios Stamatogiannakis : > create table newtable as select * from READCOMPRESSEDFILE('ctable.rc'); I'm using virtual tables extensively in my application, to expose runtime C++ objects, and I'm declaring them as shown in

Re: [sqlite] Virtual table API performance

2014-03-05 Thread Dominique Devienne
On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis wrote: > [...] Nevertheless, having people do crazy/clever hacks like that to avoid the > inefficiencies of an API, gives a hint that something might be problematic. > > In a previous email of mine, i had said half

[sqlite] 4-component Version Number

2014-03-06 Thread Dominique Devienne
http://www.sqlite.org/c3ref/c_source_id.html says [...] SQLite version in the format "X.Y.Z" [...] yet shows "3.8.3.1" and later says that SQLITE_VERSION_NUMBER is computed using (X*100 + Y*1000 + Z), thus ignoring the last version component. (SQLITE_VERSION_NUMBER = 3008003 is compatible

Re: [sqlite] RPAD/LPAD

2014-03-07 Thread Dominique Devienne
On Fri, Mar 7, 2014 at 7:38 AM, Zsbán Ambrus wrote: > On 3/7/14, RSmith wrote: >> Add to this the fact that you can - through SQL's ability to add >> user-defined functions (an almost unique ability among SQL engines) > > Is that really so? I thought most

Re: [sqlite] RPAD/LPAD

2014-03-07 Thread Dominique Devienne
On Fri, Mar 7, 2014 at 10:07 AM, Eduardo Morras wrote: > Creating extensions in SQLite is not difficult nor hard. You can define yours > with this line: It's no hard, no, if you're already a C developer. But it's hardly convenient when you need something quickly. Between

Re: [sqlite] RPAD/LPAD

2014-03-07 Thread Dominique Devienne
On Fri, Mar 7, 2014 at 11:29 AM, Max Vlasov <max.vla...@gmail.com> wrote: > On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne <ddevie...@gmail.com> > wrote: >> I think what SQLite lacks is a syntax to define custom function like >> it does for virtual tables

Re: [sqlite] RPAD/LPAD

2014-03-07 Thread Dominique Devienne
On Fri, Mar 7, 2014 at 3:39 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Max Vlasov wrote: >> On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne <ddevie...@gmail.com> >> wrote: >>> I think what SQLite lacks is a syntax to define custom function like >&

  1   2   3   4   5   6   >