Re: [sqlite] Feature Request: Binding Arrays

On Wed, Oct 16, 2013 at 1:28 PM, Richard Hipp wrote: > Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 Thanks. Being familiar with vtables, I had imagined as much, even though stopped short of doing it in practice. This takes care of hiding the DML statements from auth/trace h

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 I want to debug into i

Re: [sqlite] Feature Request: Binding Arrays

On Tue, Oct 29, 2013 at 2:06 AM, Olaf Schmidt 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 binding. >>

Re: [sqlite] Feature Request: Binding Arrays

On Thu, Oct 31, 2013 at 2:45 AM, Olaf Schmidt 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 good. >> <&l

Re: [sqlite] Feature Request: Binding Arrays

On Sat, Nov 2, 2013 at 4:59 AM, Olaf Schmidt 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)] > > [...] With a function bas

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 http://en.wikipedia.org/wiki/Join_(SQL)

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 posts crossed, and you'r

Re: [sqlite] Groups and members

On Wed, Nov 6, 2013 at 3:53 PM, Igor Tandetnik wrote: > On 11/6/2013 8:50 AM, Dominique Devienne wrote: > >> 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' el

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), and is equivalent to Ora

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 reading http://www.sqlit

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: > > http://www.postgres

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 http://sqlite

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 off. > > If IEEE double

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 numbers and > > clients do not want to round off. >

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 communicates such a val

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, or it somehow doesn't m

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. UBSan, Undefined Behavior Sanitizer (f

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 trouble with. I can loop

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 > > TableName, TableRowId, Fi

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 >> matters a lot in the general case.

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 utility that connects to Orac

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 vary from run to run and (at least in my > > testing

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 tables and data are dumped

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

On Fri, Jan 9, 2015 at 11:57 AM, Dominique Devienne wrote: > 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

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()? Perhaps a concre

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 functions. --DD

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, and that indeed > > the set of all 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 "constant" in the parsing o

Re: [sqlite] aggregate functions with DISTINCT

On Mon, Jan 12, 2015 at 4:13 PM, Igor Tandetnik 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... >> > > Simple

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. Plus DRH already wrot

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 interesting. A couple ty

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 account when > there was als

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 -> > https://github.com/book-templates/schema

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 table functions, but in a ve

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. > Adding PRAGMAs is not

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? > >>> > > These approaches use stock capabilit

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 > >> if( pTerm->wtFlags & TERM_VNULL ) continue;

[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 proto

[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 as

[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, &db); > rc = sqlite3_exec(db, "PRAGMA journal_mode=WAL;", testCallbackPrint, 0, > &zErrMsg); > [?] > sqlite3_close(db); > > Unfo

[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 https://www.sqlite.org/howtocompile.html,

[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 in

[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 DBMS

[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 pa

[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 hier

[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 extensions

[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 yet.

[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 show

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

[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 the

[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)tables

[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 EX

[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 Forei

[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 t

[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 SQLi

[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 sets

[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 BestIndex

[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 the

[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 dec

[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 s

Re: [sqlite] Double CTRL-C in shell get you completely out

On Wed, Jul 31, 2019 at 8:37 PM Tony Papadimitriou wrote: > Recently CTRL-C was improved to abort the query and stay in the CLI. This > is very good. > FWIW, SQliteSpy uses the escape key (ESC) to abort a run-away query (Hello incorrect recursive CTE!) I'd prefer CTRL-C to keep killing the curr

Re: [sqlite] Large database backup

On Thu, Aug 1, 2019 at 5:02 PM Olivier Mascia wrote: > > Le 1 août 2019 à 14:49, Tammisalo Toni a > écrit : > > I have an application which is using sqlite database in WAL mode. There > is a need for > > periodic backups to a remote site without obstructing the normal > operation. Both read > >

Re: [sqlite] [EXTERNAL] storing blobs in a separate table

On Thu, Aug 1, 2019 at 5:44 PM Jens Alfke wrote: > > On Jul 31, 2019, at 5:02 AM, Hick Gunter wrote: > > SQLite stores rows in a compressed format that requires decoding. To > access the nth field, all the fields that come before it need to be decoded. > > My understanding is that it’s just a ma

[sqlite] Doc about sqlite_master

First, is sqlite_master part of the "public API" of SQLite? I would think so, since many example refer to it. I was thus looking for a Doc page about it, but a quick Google search didn't turn up a "direct" result. For example, the values sqlite_master.type can take, I found only outside sqlite.or

[sqlite] Information Schema enhancement request

(Not sure it's strictly information schema related, but here goes anyway). SQLite has grown in the recent months better support for extracting an information schema, thanks to new pragmas and especially their eponymous vtable versions, allowing them to be mixed in queries with sqlite_master. But

Re: [sqlite] Doc about sqlite_master

On Mon, Aug 5, 2019 at 10:37 AM Simon Slavin wrote: > On 5 Aug 2019, at 9:25am, Dominique Devienne wrote: > < > https://www.sqlite.org/fileformat2.html#storage_of_the_sql_database_schema > > > <https://sqlite.org/faq.html#q7> > I think the first of those could be

Re: [sqlite] Doc about sqlite_master

On Mon, Aug 5, 2019 at 11:37 AM Simon Slavin wrote: > On 5 Aug 2019, at 10:34am, Dominique Devienne wrote: > > > Thus my suggestion that an explicit page of its own is > warranted, properly indexed then. > Given that there are other sqlite_* tables which are documented, per

Re: [sqlite] Doc about sqlite_master

On Mon, Aug 5, 2019 at 2:03 PM Simon Slavin wrote: > On 5 Aug 2019, at 11:54am, Dominique Devienne wrote: > > > What other sqlite_* tables are you talking about? > > sqlite_master is AFAIK the only table with a "fixed-name", that's part > of the "public

[sqlite] Enhance CLI with ability to assert version and compile option of sqlite3.so/.dll

Hi Richard, and others, A neat little program with added in the context of Fossil SCM, which asserts the version of the SQLite library used, and which compile time option were used to compile it (statically or not, for that matter). See https://fossil-scm.org/fossil/info/350c627a52908458 I think

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

On Tue, Aug 13, 2019 at 10:58 AM Ling, Andy wrote: > > This is what I would call "forward compatibility": You expect an old > application > > to be able to read file formats of a future version. Do you have an > example > > where there is really required? > > I have an Android app that lets you s

Re: [sqlite] Query for Many to Many

On Tue, Aug 27, 2019 at 3:38 PM dboland9 wrote: > I need some help writing some queries for a MTM relationship. The example > tables are: > > author table books table author_books table > author_id PKbook_isbn PKa_b_id PK > author_fnamebook_title

Re: [sqlite] Query for Many to Many

On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne wrote: > select author.*, books.* > from author_books > join author on author.author_id = author_books.author_id > join books on books.book_isbn = author_books.book_isbn > Which can also be written: select author.*,

Re: [sqlite] database like file archive

https://sqlite.org/sqlar/doc/trunk/README.md On Tue, Aug 27, 2019 at 3:57 PM Peng Yu wrote: > Hi, > > I haven't found an archive format that allows in-place delete (I know > that .zip, .7z and .tar don't). This means that whenever delete is > needed, the original archive must be copied first. Th

Re: [sqlite] strip off file metadata in sqlar

On Tue, Aug 27, 2019 at 11:46 PM Peng Yu wrote: > I just need the return status of sqldiff (as `cmp -s`). Is there an > option to suppress all the screen output? Thanks. > https://www.sqlite.org/cgi/src/artifact/7b9b7238284f0213 Doesn't look like it does. But redirecting to /dev/null or NUL wou

Re: [sqlite] dqlite 1.0.0 - SQLite replication and failover library

On Thu, Aug 29, 2019 at 2:35 PM Jose Isaias Cabrera wrote: > Free Ekanayaka, on Thursday, August 29, 2019 06:40 AM, wrote... > > See https://dqlite.io for more details. > > Can dsqlite be installed on Windows? I went to the site, read the > README.md file, and could not find any reference of it.

Re: [sqlite] Tracking item history using SQLite

On Sat, Aug 31, 2019 at 12:24 PM Rob Willett wrote: > 5. SQLite seems to be able to do anything we want it to. [...] > Other people seem worried about the 'lack' of some datatypes, we do > masses of data and date conversations as needed and it's never been a > speed issue or any issue. (since

Re: [sqlite] http://roaringbitmap.org/

On Mon, Sep 2, 2019 at 8:06 AM Robert M. Münch wrote: > Hi, I think that SQLite use some bitmap indexes Not that I know of, but I don't know the full source code. Maybe FTS[345] do/es, but SQLite itself only uses BTree-indexes AFAIK. > and this here might be of interest if not already used/kn

Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/

On Mon, Sep 2, 2019 at 12:08 PM Hick Gunter wrote: > Back in 2011 I implemented a virtual table using the "fastbit" library by > John Wu of the Lawrence Berekely National Laboratory. This allowed selects > of the form > > SELECT ... FROM WHERE rowid IN (SELECT rowid FROM > WHERE ); > Did it wo

Re: [sqlite] [EXTERNAL] char(0) with SQLite

On Mon, Sep 2, 2019 at 12:12 PM Hick Gunter wrote: > Dimensions are ignored by SQLite. A field defined CHAR(0) can hold any > length (up to the internal limit) of string. SQlite will only store the > actual length of the string plus its contents, no space is wasted. And Gunter wrote "string" a

Re: [sqlite] INSERT vs BEGIN

On Mon, Sep 2, 2019 at 12:04 PM Grincheux <51...@protonmail.ch> wrote: > What is the best ? > > INSERT INTO artists (name) VALUES > ("Gene Vincent") ... > ("Moi _ Me"); > You're missing commas. And you should not use double-quotes but single-quotes for string-literals. > I want to insert 1 000

Re: [sqlite] INSERT vs BEGIN

On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin wrote: > > One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" > because others threads needs to access to tables. > SQLite copes very well when you have one connection writing to the > database and other connections reading. The probl

Re: [sqlite] INSERT vs BEGIN

On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson wrote: > I didn't know it is possible to insert multiple rows into a table using a > command like this. Added over 7 years ago: See https://www.sqlite.org/changes.html#version_3_7_11 #1 > Is this just an SQLite feature, or is this part of the SQL

[sqlite] Why aren't there date/time parsing built-in functions in SQLite

Hi, There are functions to format numbers as text times and/or dates, but I can't find equivalent ones to parse those text times/dates back to numbers. I wanted to sum durations expressed as HH:MM:SS.SS strings, and I was expecting a function parsing such a string into a number of seconds for exa

[sqlite] Enhancement request: scanf built-in function

In the same vein as my previous post about lack of function acting as the "opposite" of strftime(), I'm wondering if it's not time to have a scanf() function as the opposite of printf(). In the olden days, SQLite didn't have table-valued eponymous-vtable "functions", so there was a technical reao

Re: [sqlite] Why aren't there date/time parsing built-in functions in SQLite

On Tue, Sep 10, 2019 at 1:05 PM Keith Medcalf wrote: > insert into test values ('00:00:07.86'); > select value, >round((julianday(value) - julianday('00:00:00')) * 86400.0, 3) > from test; > Thanks! As I guessed, I was indeed missing something. But IMHO that something is definitely not

Re: [sqlite] FW: Why aren't there date/time parsing built-in functions in SQLite

On Tue, Sep 10, 2019 at 1:27 PM Keith Medcalf wrote: > Also, note that you have to use the 'unixepoch' modifier with the time > function so that it knows the value is seconds, not days, since floats are by default days and integers are by default > seconds. [...] In my quick reading of the doc

Re: [sqlite] insert: how to force application to provide value for int primary key?

On Tue, Sep 10, 2019 at 2:20 PM Jose Isaias Cabrera wrote: > Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote... > > > Make it a WITHOUT ROWID table: > > > > > > CREATE TABLE example_table ( > > >id INTEGER PRIMARY KEY, > > >description TEXT NOT NULL > > >)

Re: [sqlite] insert: how to force application to provide value for int primary key?

On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski wrote: > Yes, indeed works. Great, thank you! > Note though that it has performance implications perhaps. This changes to physical structure of the table, to be stored as an index basically. So if you do lots of insertions "in the middle", you c

Re: [sqlite] insert: how to force application to provide value for int primary key?

On Tue, Sep 10, 2019 at 4:32 PM R Smith wrote: > > So "fat" tables, with large rows, and lost of inserts/updates, is > basically > > the worse case > > scenario for such WITHOUT ROWID tables. It works, no issue there, and as > > typical of > > SQLite is often fast enough for most DB sizes, but it

<    2   3   4   5   6   7   8   >