Re: [sqlite] JSON indexing

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

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

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

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

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)

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

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

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

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(*)

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

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

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?

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?

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

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

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

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

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

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

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

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

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

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

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

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

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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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?

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?]

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'

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'

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'

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

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?

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

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

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

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] Groups and members

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

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

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'?

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

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'?

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]

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

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

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)

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)

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

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

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

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

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

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

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

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

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

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

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

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?

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

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

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?

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"

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

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

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

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

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

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

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 >&

Re: [sqlite] RPAD/LPAD

On Fri, Mar 7, 2014 at 5:27 PM, Eleytherios Stamatogiannakis wrote: > IMHO, SQLite is targeted towards being a relational "core" with very wide > extensibility. Adding specific scripting engines to it is would be > detrimental to its main purpose (being a very good relational

Re: [sqlite] RPAD/LPAD

On Fri, Mar 7, 2014 at 7:37 PM, Max Vlasov wrote: > On Fri, Mar 7, 2014 at 6:39 PM, Clemens Ladisch wrote: >> Max Vlasov wrote: >>> >>> Nice suggestion. This probably falls into case when a small new part >>> needed on sqlite side >> >> Actually, no

  1   2   3   4   5   6   7   8   >