[sqlite] Remarks about vtab generate_series.

Hello, I have two mini minor remarks about the series.c example as of 2015-08-21 and referred to from the Table-Valued Functions paragraph of https://www.sqlite .org/vtab.html#tabfunc2 1. comment above seriesDisconnect < ** This method is the destructor for series_cursor objects. > ** This

[sqlite] Virtual tables and table-valued functions

6 dec 2015, Charles Leifer: > In working on a Python wrapper around virtual tables, I thought it > might be > beneficial if SQLite provided an official C API for creating simple > table-valued functions. The wrapper could build on the existing > virtual > table APIs and would consist of: > >

[sqlite] Problem with accumulating decimal values

16 dec 2015, James K. Lowden: > On Fri, 11 Dec 2015 16:21:30 +0200 > "Frank Millman" wrote: > >> sqlite> UPDATE fmtemp SET balance = balance + 123.45; >> sqlite> SELECT bal FROM fmtemp; >> 5925.599 > > To a question like that you'll receive a lot of answers about > numerical >

[sqlite] Problem with accumulating decimal values

e of numbers. But a solution with integers neither does E.Pasma

[sqlite] Problem with accumulating decimal values

16 dec 2015, 16:17, Bernardo Sulzbach: > > On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma wrote: > >> Ok this does not work of any scale of numbers. But a solution with >> integers >> neither does >> >> E.Pasma >> > ...I like integer better than floati

[sqlite] {Spam?} SQLite take lower performance while using shared cache on iOS/Mac

her thing: I found that sqlite3_open takes significant time when connecting to a shared cache that is in use. Therefore the Python test measures the overall elapsed time. Python offers a thread.join method to know exactly when a thread is finished. Tnanks, E.Pasma import random, os, sys,

[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac

20 dec 2015, 14:29, sanhua.zh: > Here is the test result for selecting 100,000 items in original test > case. > > > shared cache mode > 2015-12-20 21:24:58.714 Test[1126:11609] cost 2.173480 > 2015-12-20 21:24:58.714 Test[1126:11610] cost 2.173449 > 2015-12-20 21:24:58.714 Test[1126:11608] cost

[sqlite] Optimization Opportunity?

Op 7 mrt 2015, om 19:24 heeft Wolfgang Enzinger het volgende geschreven: > Hi dev team, > > not sure if this is actually a useful hint, but ... > > CREATE TABLE a(a1 INTEGER PRIMARY KEY); > INSERT INTO a VALUES (1),(2),(3); > CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY); >

[sqlite] Optimization Opportunity?

Op 13 mrt 2015, om 00:03 heeft Wolfgang Enzinger het volgende geschreven: > Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma: > >> Actually query one appears slightly faster, >> Searching the PK index is faster as that is always a COVERING index. > > I

Re: [sqlite] Bug in SQLITE regarding HAVING?

Op 23 dec 2013, om 14:32 heeft Simon Slavin het volgende geschreven: General note: when making up a name for a calculation like 'depth', try to make sure it's not the name of any of the columns in the tables mentioned in your SELECT. This avoids ambiguity. . See if you can make the

Re: [sqlite] Saw something interesting in the debugger...

Op 9 jan 2014, om 19:50 heeft Ward Willats het volgende geschreven: BUT, I was wondering if there are scenarios where only two threads can bounce each other into busy sleep like two bocci balls colliding? (one thread wanted a read lock, the other a BEGIN EXCLUSIVE write lock) On Jan 9,

Re: [sqlite] "Common Table Expression"

Op 24 jan 2014, om 14:31 heeft Richard Hipp het volgende geschreven: Please see http://www.sqlite.org/draft/lang_with.html for draft documentation of the new Common Table Expression implementation for SQLite 3.8.3. Comments, criticism, and typo-corrections are appreciated. -- D. Richard

Re: [sqlite] "Common Table Expression"

Op 24 jan 2014, om 17:31 heeft E.Pasma het volgende geschreven: Op 24 jan 2014, om 14:31 heeft Richard Hipp het volgende geschreven: Please see http://www.sqlite.org/draft/lang_with.html for draft documentation of the new Common Table Expression implementation for SQLite 3.8.3. Comments

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

Op 1 feb 2014, om 08:29 heeft big stone het volgende geschreven: Maybe Chrismas will be in february this year : http://bugs.python.org/issue20465 Whatever will be the answer from the python team, THANKS a lot Mr Hipp, Mr Kennedy, and Mr Mistachkin for making it possible !

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

Op 6 feb 2014, om 16:46 heeft Simon Slavin het volgende geschreven: On 6 Feb 2014, at 7:15am, big stone wrote: If we wish to have SQLite + Python combination to become "reference choice" in education, I would think that priority list should be : Just to remind you

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

Op 9 feb 2014, om 13:19 heeft big stone het volgende geschreven: Congratulations, *E.Pasma* ! You indeed won the "brute force sudoku speed" competition for sqlite 3.8.3 by a factor of x2.5 on my PC ! D. Richard Hipp is still tuning the CTE implementation, so competition ma

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

Op 9 feb 2014, om 20:50 heeft big stone het volgende geschreven: Hi, You're right,' recommands' is not in the code, but was not run anymore either. The "x" table cte definition should be removed, as the final request use only "q". Anyway, after all this algorithmic surgery, Postgresql

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

Hello The SQLite version is 3.8.4 by now and this is stil about the sudoku solving. I studied Norvigs algorithm (Python) http://norvig.com/sudoku.html . It is possible to achieve this method in SQL. A very important difference between Norvigs programme and the SQLite example is however

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

Op 16 mrt 2014, om 16:45 heeft big stone het volgende geschreven: Hi Edzard, I just reproduced your test. Indeed : - you probably blew-up everything running SQL sudoku on this planet : . 'hardest1' in under 2 seconds on my machine, . 'eastermonster1' in 43ms. - with Norvig's method and

Re: [sqlite] Help on forming the correct aggregation statement with a Union clause

Op 14 mei 2014, om 21:44 heeft Wendy het volgende geschreven: Hi, Wondering if anyone can help me with the following: - Does anyone know how I can get the SUM() aggregate function within this SQLite statement? SELECT AwayTeam As 'Team', CASE WHEN AwayTeamScore > HomeTeamScore

Re: [sqlite] Help on forming the correct aggregation statement with a Union clause

Op 15 mei 2014, om 12:09 heeft E.Pasma het volgende geschreven: Op 14 mei 2014, om 21:44 heeft Wendy het volgende geschreven: Hi, Wondering if anyone can help me with the following: - Does anyone know how I can get the SUM() aggregate function within this SQLite statement? SELECT

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

Op 29 mei 2014, om 11:59 heeft big stone het volgende geschreven: Hello, I would like to save my sqlite ':memory:' database into a sql command text file. With sqlite.dll, what is the procedure/algorithm ? Is it already explained somewhere on Internet ? At first look, I may imagine that I

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

Op 29 mei 2014, om 15:38 heeft Domingo Alvarez Duarte het volgende geschreven: On Thu, May 29, 2014 at 2:25 PM, E.Pasma <pasm...@concepts.nl> wrote: Op 29 mei 2014, om 11:59 heeft big stone het volgende geschreven: Hello, I would like to save my sqlite ':memory:' database into

Re: [sqlite] Sequential numbers

Op 26 jun 2014, om 10:32 heeft Rob Golsteijn het volgende geschreven: Hi Dave, You can of course also calculate a new sequence number based on the row ids. Just count the number of records with a smaller or equal rowid. This way it doesn't matter if rowid starts at 1 or if there are any

Re: [sqlite] SQLite version 3.8.6 coming soon

condition involves BETWEEN Hope that this contributes to the final release. Thanks, E.Pasma .version CREATE TABLE node (node INTEGER PRIMARY KEY, minchild INTEGER, maxchild INTEGER, status CHAR NOT NULL) ; explain query plan SELECT s2.status FROMnode s0 JOINnode s1 ON s1

[sqlite] Array or set type or some other indexable data?

12 nov 2015, om 07:02, J Decker: > So I've used CTE to solve a simple problem... I'm tempted to use it to > fix more problems... but I'm wondering how to select different values > at different levels. I know there's like 'select * from table where > column in ( set,of,things) ' but can I index

[sqlite] Array or set type or some other indexable data?

op 12-11-2015 17:35 schreef J Decker op d3ck0r at gmail.com: > On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma wrote: >> 12 nov 2015, om 07:02, J Decker: >> >>> So I've used CTE to solve a simple problem... I'm tempted to use it to >>> fix more problems... but I'm

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

op 18-11-2015 21:23 schreef Richard Hipp op drh at sqlite.org: > On 11/18/15, Nico Williams wrote: >> Consider two tables with the same columns and primary keys, and then >> consider this query: >> >> SELECT 'added., a.* FROM >> (SELECT a.* FROM a EXCEPT SELECT b.* FROM b) a >> UNION ALL >>

[sqlite] Select values from a time series spaced at least a mininum distance apart

19 nov 2015, 11:20 Ilja Heckmann: > I have a dataset of events with timestamps, and want to extract a > subset of them so that there is at least, say, an hour between items > in the result. It would be trivial to solve this in an imperative > language, e.g. the solution in Python would be:

[sqlite] Select values from a time series spaced at least a mininum distance apart

20 nov 2015, 09:19, Clemens Ladisch: > E.Pasma wrote: >> An aggregate function can still be used in a sub-query for a column >> value. For the example with integers: >> >> WITH RECURSIVE >> breaks(t) AS ( >> SELECT 1 >> UNION >> SELECT

[sqlite] optimization for outer join with most simple views

Hello, I like to post this remark again as it seems closely related to "Query flattening for left joins involving subqueries on the right- hand side". I have a complete different reason though. For playing with sudoku solving, I have a table representing the digits 1..9: CREATE TABLE

[sqlite] trying a recursive view with version 3.9.0

Hello, One of the expected changes in 3..9.0 is: A view may now reference undefined tables and functions when initially created. Missing tables and functions are reported when the VIEW is used in a query. I could not resist trying a recursive view like in a CTE: SQLite version 3.8.12

[sqlite] trying a recursive view with version 3.9.0

11 okt 2015, om 15:27, R.Smith: > > On 2015-10-11 03:14 PM, E.Pasma wrote: >> Hello, >> >> One of the expected changes in 3..9.0 is: >> A view may now reference undefined tables and functions when >> initially created. Missing tables and functions are

[sqlite] trying a recursive view with version 3.9.0

11 okt 2015, 17:41, R.Smith: > On 2015-10-11 05:23 PM, E.Pasma wrote: >> 11 okt 2015, om 15:27, R.Smith: >>> >>> You cannot reference a View within itself - this is what >>> circularly means. This is very different to being able to >>

[sqlite] crash

12 okt 2015, om 20:22, R.Smith: > > On 2015-10-12 07:40 PM, Richard Hipp wrote: >> On 10/12/15, R.Smith wrote: >>> More explicitly - would these be valid queries: >>> >>> SELECT props.* >>>FROM ( >>> SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION >>> ALL >>> SELECT

[sqlite] Performance issue with CTE

Op 1 okt 2015, om 04:10 heeft Philippe Riand wrote: > I have a table with 500,000+ records. The table has a date column, > that I?m using to sort my queries (the columns has an index). Simple > queries on the table work very well, using ORDER BY, LIMIT & OFFSET. > I?m actually extracting

[sqlite] Third test of json and index expressions, now it works

Op 6 sep 2015, om 03:17 heeft Scott Robison het volgende geschreven: > > select * from (select a as b from c) where b is something > Still column expressions are called mutiple times. SQLite 3.8.11 2015-07-27 13:49:41 b8e92227a469de677a66da62e4361f099c0b79d0 create table c(a) ; explain query

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

Op 12 sep 2015, om 13:48 heeft Aurel Wisse het volgende geschreven: > > > Here is the example. It doesn't use an actual aggregation (sum, > count), but > the > > "min" aggregate function. > > > From a list of dates, create a lookup table with two > > columns: the original date (calc_date) and

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

> > On 2015-09-13 12:40 AM, Aurel Wisse wrote: >> @R.Smith >> I wasn't aware that recursive aggregate queries give wrong answers >> in 99% >> of all general use cases//... > > I apologize - I thought Richard had made that much clear early on in > the exchange and you were simply not

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

The rowcounts were: 1) 999 - aurel's original version is 3.8.9 2) 1000 - Luuks non-recursive version, same for my own trial 3) 837 -the version with temp table 4) 838 - DRH;s version and there is nothing to worry here. The main difference is explained by a mistake in the format parameter to

[sqlite] [OT] Re: Worked in 3.8.9, but now "recursive aggregate queries not supported"

Op 13 sep 2015, om 15:17 heeft Luuk het volgende geschreven: > > > On 13-09-15 14:57, E.Pasma wrote: >> The rowcounts were: >> >> 1) 999 - aurel's original version is 3.8.9 >> 2) 1000 - Luuks non-recursive version, same for my own trial >> 3) 837 -the ve

[sqlite] using mprint as a global variable inside a query

Here is still a follow up of last weeks discussion "Worked in 3.8.9 but no longer in .." which is about the following case: a large table includes a datetime field. From the points in time one wants to report time intervals (from - to). The datetime field is indexed. The fastest solution that

[sqlite] Is it possible that dropping a big table takes very long

21 apr 2016, Cecil Westerhof: > > ?I think it is an edge case. On my real system I only got this when > there > where 1E8 records. I am now testing on very old (8 year) hardware to > and > from work. Hello, the answer to Cecils question is YES here. I tested on a computer with just 512 Mb

[sqlite] Is it possible that dropping a big table takes very long

22 apr 2016, Cecil Westerhof: > > ?With createBigTable.sh ... Can you paste the svript in the message? Attachments are not sent. Regards, E.Pasma

[sqlite] Is it possible that dropping a big table takes very long

Hello, I tried the scripts but.. createBigTable.sh is beyond the capacity of my system. Instead I used SQL script like in www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg08044.html My point is that the definition of the table is a waste of capacity, even though it serves on

[sqlite] Is it possible that dropping a big table takes very long

23 apr 2016, E.Pasma: > Hello, > I tried the scripts but.. > > createBigTable.sh is beyond the capacity of my system. Instead I > used SQL script like in > www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg08044.html > > My point is that the definition

[sqlite] User-defined SQL functions

21 feb 2016, Igor Tandetnik: > On 2/21/2016 12:09 PM, Olivier Mascia wrote: >> Is it possible to implement a SQL function >> (https://www.sqlite.org/c3ref/create_function.html >> ), which implementation would be able to return the same value for >> the duration of the current transaction? >>

[sqlite] User-defined SQL functions

Hi, forget to mention that a function like this was earlier considered as being tricky and living dangerously. Also we found a bug (missing break) between line 80 and 81 which will lead to a memory leak every time a text value is stored ... switch (pval->t) { case

[sqlite] User-defined SQL functions

22 feb 2016, Dan Kennedy: > On 02/23/2016 01:33 AM, E.Pasma wrote: >> >> I reproduced the memory leak and added a test in the sql script. >> An alternative fix, instead of adding the missing break, is: >> >> case SQLITE_TEXT: >> case SQLITE_BLOB:

[sqlite] User-defined SQL functions

23 feb 2016, Dan Kennedy: > On 02/23/2016 07:36 PM, E.Pasma wrote: >> 22 feb 2016, Dan Kennedy: >> >>> On 02/23/2016 01:33 AM, E.Pasma wrote: >>>> >>>> I reproduced the memory leak and added a test in the sql script. >>>

[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac

06-01-2016, Scott Perry: The SQLite built into OS X does not support cache sharing for performance reasons?, which is probably why your results are statistically identical and the OP's results are wildly different. You can verify this by checking the return value of sqlite3_enable_shared_cache;

[sqlite] Best way to store only date

The diagram got broken in my email and here is another try: Needs to be light | Needs to be| Needs to do | (small footprint) | Human-Readable | calculations | - | ---| | YES | YES| NO | Integer as

[sqlite] Best way to store only date

30-01-2016 14:59, R Smith: > > > On 2016/01/30 3:22 PM, E.Pasma wrote: >> The diagram got broken in my email and here is another try: >> >> Needs to be light | Needs to be| Needs to do | >> (small footprint

[sqlite] Good way for CEIL, or is there a better way

09-05-2016, OBones: > Isn't Ceil(Value) simply Round(Value + 0.5) ? But Round(0.5) = 1 May be Round(Value+0.4) is good enough?

Re: [sqlite] select max(x), y from table

Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven: On 2/2/2013 6:46 PM, Gabor Grothendieck wrote: In 3.7.11 there was a change to support the feature in the subject which refers to guaranteeing that y comes from the same row having maximum x.. See:

Re: [sqlite] select max(x), y from table

Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma <pasm...@concepts.nl> wrote: Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven: On 2/2/2013 6:46 PM, Gabor Grothendieck wrote: In

Re: [sqlite] select max(x), y from table

Op 3 feb 2013, om 20:01 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 1:40 PM, E.Pasma <pasm...@concepts.nl> wrote: Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma <pasm...@co

Re: [sqlite] select max(x), y from table

Op 3 feb 2013, om 22:06 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 4:00 PM, Petite Abeille wrote: On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck wrote: What is the SQLite consortium?

Re: [sqlite] Question about aggregate returning empty row

Hi, you may add a dummy GROUP BY clause, to return no row if the table is empty: select min (x) from tbl group by null; By the way, the good reasons for the standard behaviour may be the use of other aggregate functions like COUNT (*). Op 23 feb 2013, om 11:51 heeft Pierre Chatelier

Re: [sqlite] Updating a table from itself

Op 9 jun 2013, om 16:35 heeft Igor Tandetnik het volgende geschreven: On 6/9/2013 4:55 AM, Dave Wellman wrote: For this example I'm updating a single column (c2). If I needed to update multiple columns in the table would I need to use the SELECT construct for each column? Yes,

[sqlite] optimization for outer join with most simple views

Hello, Suppose one has an expression on the columns of a single table, say x +y, and that this expression occurs in multiple queries. Then it is attractive to define it at a single place, using a view: create view v as select *, x+y as a from t; I had hoped that substituting such a

Re: [sqlite] The next-generation query planner

Op 26 jun 2013, om 16:08 heeft Richard Hipp het volgende geschreven: The next-generation query planner (NGQP) is a rewrite of the query planner for SQLite that is faster (reduced run-time for sqlite3_prepare()) and generates better plans for queries (reducing the run-time for

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven: On 7/20/2013 9:54 AM, Mikael wrote: So again, SELECT id, (SELECT [very complex subselect here, that uses categories.id as input]) AS a, (SELECT [another very complex subselect here, that uses categories.id as input])

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven: Hi Igor, Ah I just noticed how you wrote your query and it delivers for it indeed. Here's an arbitrary example verifying its works. Neat - thanks! sqlite3 test.sqlite create table categories (id number); insert into

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 15:43 heeft Clemens Ladisch het volgende geschreven: RSmith wrote: On 2013/07/21 12:01, E.Pasma wrote: Only the execution plan of this query is not optimal: 0|0|0|SCAN TABLE categories (~100 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SCAN TABLE ot AS ot1

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 18:06 heeft Simon Slavin het volgende geschreven: On 21 Jul 2013, at 4:41pm, E.Pasma <pasm...@concepts.nl> wrote: Is a change in SQLite imaginable such that column expressions are not re-evaluated with each reference to the column alias? ... ... This is partly b

[sqlite] Different result from experimental query

An experimantal query, involving OUTER JOIN with BETWEEN and JOIN with a combined OR and AND expression, does not return all expected rows. I tried this just after SQLite 3.8.0. was released and found that the issue is particular to this version. At least it is alright in version 3.7.17.

Re: [sqlite] help needed for major SQLite problem

Op 2 sep 2013, om 17:58 heeft C M het volgende geschreven: ... Is setting up APSW and making the transition from pysqlite2's wrapper fairly straightforward, or would I have to re-do all the database queries? (Even so, I do them all in one utilities library, so perhaps it would be not too

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

The developers choose the C type 'int' to represent the hash value. Possibly this is too small for your case? Op 6 sep 2013, om 22:00 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: On 6 sep. 2013, at 20:09, "Kevin Benson" wrote: Dr. Hipp does

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Op 9 sep 2013, om 10:06 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: Our table and column names are not too long either as E.Pasma suggests. Ha, I did not mean the length of the names but the length of the hash table (NL: klutstabel), That is the number of buckets

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Op 10 sep 2013, om 11:37 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: On 9 sep. 2013, at 22:11, "E.Pasma" <pasm...@concepts.nl> wrote: Ha, I did not mean the length of the names but the length of the hash table (NL: klutstabel), That is the number

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Op 10 sep 2013, om 11:37 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: I included 5 databases that we used for testing in this link: http://wikisend.com/download/570088/test_databases.zip The query performed on these databases is: delete from A where id=1; I could not

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Op 10 sep 2013, om 16:36 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: On 10 sep. 2013, at 16:16, "E.Pasma" <pasm...@concepts.nl> wrote: Op 10 sep 2013, om 11:37 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: I included 5 databa

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Op 10 sep 2013, om 19:48 heeft Simon Slavin het volgende geschreven: On 10 Sep 2013, at 4:15pm, Harmen de Jong - CoachR Group B.V. wrote: That is something we suspected too. We already made some tests where we timed the time needed for all memory allocations executed

Re: [sqlite] racing with date('now') (was: Select with dates)

Op 15 sep 2013, om 14:05 heeft Stephan Beal het volgende geschreven: On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy wrote: Sure, there can be several way to interpret CURRENT_* and *('now'). However, some of them can be useful (transaction, statement), and others

Re: [sqlite] group_replace

11 aug 2016, Dominique Devienne: On Thu, Aug 11, 2016 at 1:10 PM, Anthony Lansbergen wrote: Hello, I needed a way to make parameterized texts for logging in my current project. For this purpose I missed a function in sqlite: group_replace, so I made an extension. The

Re: [sqlite] thousand separator for printing large numbers

10 feb 2017, Dominique Devienne: There's http://sqlite.1065341.n5.nabble.com/printf-with-thousands-separator-td85022.html And my feeble attempt below. But there's got to be a better way, no? What would be the shortest and/or most efficient way to do this in SQL? .. sqlite> with s(v) as (

Re: [sqlite] Group contiguous rows (islands)

Jean-Luc Hainaut: On 15/02/2017 18:34, E.Pasma wrote: Hello, the query below is simpler. May be slower. But looks pretty relational. Thanks, E Pasma. create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'), (13,'clim'),(15

Re: [sqlite] Group contiguous rows (islands)

15 feb 2017, Jean-Luc Hainaut: You could try this, inspired by classic algorithms of temporal databases: create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'), (13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

[sqlite] can not use row values from inside a trigger

I'm using SQLite only for private purpose. This also allows me to try incredably complex queries. And find that SQLite is reliable indeed and fast. Currently I try to execute a script of 30 statements from inside a trigger. And amazed to not have any error. Except one, when using the new

Re: [sqlite] table-naming-expression impact on sqlite3_prepare

27 mrt 2017, petern: In general I've been thinking about materializing data dependent temporary tables and even using them in CTE's. The tremendous expressive economy of TCL and somewhat built-in support within SQLite got me thinking. Consider the problem of pivot table function for

Re: [sqlite] table-naming-expression impact on sqlite3_prepare

26-03-2017 petern : > The table-naming-expression, if > normal expressions are allowed, would obviously require sqlite3_prepare to > consult the database in situations where the name string expression depended > on a SQL statement being evaluated. Is this the main problem with allowing >

[sqlite] Slow query, with correlated sub-sub-query

Thanks David for the alernative solution, that is 500 times faster or any times faster depending on the number of rows. I hope this does not derive attention from my point that the original construction is not very well delt with. It was only a theoretical query derived from a more complex

Re: [sqlite] extension to query/set environment variables?

Nelson, Erik - 2 wrote: > Hello, I could imagine a virtual table that held all the environment > variables, or a user-defined function to get or set environment variables. > Does anyone know of anything in the wild along these lines? I know how I > would implement it, wanted to see if someone

[sqlite] Slow query, with correlated sub-sub-query

Hello, below is a theoretical query that becomes slow when the number of rows increases. What it does is: - scan input cases in table a - for each input case: -- determine the smallest value of attribute size of elements in table ab -- count the number of elements having this smallest size

Re: [sqlite] Version 3.20.0 coming soon... _rl_completion_matches undefined on old iMac

Hello, when building the pre-release snapshot an error occurs. It is likely due to my outdated Mac OS version, 10.5.8. shell.c: In function ‘readline_completion’: shell.c:4286: warning: return makes pointer from integer without a cast ... Undefined symbols: "_rl_completion_matches",

Re: [sqlite] Version 3.20.0 coming soon... _rl_completion_matches undefined on old iMac

Richard Hipp wrote: E.Pasma wrote: Is there any otrher choice except ./configure --disable-readline. Other options: (1) You can upgrade the readline library on your PPC to something more recent that supports tab completion. (2) You can compile the shell using linenoise instead of readline

Re: [sqlite] Generalized SQLite stored procedure style pivot table exhibit.

15 mei 2017, 07:34 petern: Here I revisit the pivot table problem using the SQLite stored procedure pattern and pure unmodified SQLite. I am seeking feedback for improving the brevity of the stored procedure presented here. Hi, initially I got: near "eval": syntax error. This is after

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Thomas Flemming Tue, 30 May 2017 09:43:15 -0700 >> Try putting a "+" symbol before "styleid". Like this: >> >> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) > THATS IT !! :-))) > > 50ms with +, and 15000ms without the + > > How is that possible? Hello, best

Re: [sqlite] Article about using sqlite3 in Python

22 okt 2017, 18:47, Simon Slavin: I don’t know enough about Python to evaluate this, but the sqlite3 side is sound, and some readers might find it useful. Simon. It is written very well. However for readers with an

Re: [sqlite] Getting an advance list of RowIDs for a query result set

So if I build a view that includes look-ups in other tables, the optimizer may skip these at places where not selected. However only if the look-ups are written as outer joins. Then it may be good practice allways doing that. For instance: create view vtrack as select trackname,

Re: [sqlite] Getting an advance list of RowIDs for a query result set

Op 27 nov 2017, om 20:51 heeft x het volgende geschreven: So if I build a view that includes look-ups in other tables, the optimizer may skip these at places where not selected. However only if the look-ups are written as outer joins. Then it may be good practice allways doing that. For

Re: [sqlite] Getting an advance list of RowIDs for a query result set

x wrote: I proceed as follows 1. Omit a table join from the SQL and try preparing it. 2. If it prepares OK then the table isn’t involved in the WHERE or ORDER BY. 3. If it’s joined to the BaseTbl by an integer primary key or FULLY joined by a unique index then the table is

Re: [sqlite] Getting an advance list of RowIDs for a query result set

x wrote: From: E.Pasma<mailto:pasm...@concepts.nl> Sent: 26 November 2017 17:30 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set If step 3 is xxx-ed and only left-

[sqlite] View is not flattened when inside an IN sub-query

Hello, below are two equivalent delete statements. The difference is that the second version uses views, actually sub-queries, on the base tables. These are simple one to one views that could be flattened out, as in http://www.sqlite.org/optoverview.html#flattening The second query plan

Re: [sqlite] View is not flattened when inside an IN sub-query

Thanks very much for finding this worth a change. I found that in the SQLite3 timeline and I tested the change. As written in an other topic: .. Just sit tight and again wait and see if Dr Hipp agrees the behavior should change or not. It is comforting that this is even true for an

Re: [sqlite] probably recursive?

OM points GROUP BY x HAVING COUNT(*)<(SELECT nx FROM params) ) OR y IN ( SELECT y FROM points GROUP BY y HAVING COUNT(*)<(SELECT ny FROM params) ) ; } if {![db changes]} break } ;# end loop E.Pasma ___ sqlit

Re: [sqlite] probably recursive?

Cezary H. Noweta wrote: At the beginning I would like to agree with that the problem is iterative rather then recursive one. However R. Smith wrote: LOL, that might be the hackiest query I ever seen, but kudos mate, that's bloody marvellous! Cezary, thanks for the diverting

Re: [sqlite] Database file with a nonstandard sqlite_seuence schema causes crash.

This crash (bus error) also occurs when the experiment is done the other way around. A standard sqlite3_sequence table is then renamed and no longer exists as such. And it will not reappear.. It is generous that a pragma writable_schema lets us do these experiments. $ rm test.db; sqlite3

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

op 05-01-2018 17:23 schreef David Raymond op david.raym...@tomtom.com: >> Anyway the two queries return the same set of rows. > >> This test also show a small semantic difference in the two queries. >> The set of rows is the same but the second query leaves certain >> details null if only one of

  1   2   >