Re: [sqlite] New word to replace "serverless"

I thougth about self-service, self-serve or self-served. Thanks, E. Pasma ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing a statement cache

> Op 16 dec. 2019, om 22:38 heeft carsten.muencheberg > het volgende geschreven: > > Hi, > I am working on a generic cache for prepared statements and would like to > make sure that I am not overlooking anything important. > > The cache is a simple map from an SQL string to a statement

[sqlite] Documentation issue: carray

Hello, on the page https://www.sqlite.org/carray.html This query gives the same result: --> This query gives the same results, uniquely ordered. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] [EXTERNAL] Fastest way to SELECT on a set of keys?

Keith, The final script produces corresponding results here, only a constant factor slower (minimal CPU). The rows per second is useful to summarize the tests for various keyset sizes. Below is the average per method with input parameter 5. meth|rps|note 1|149431|Individual Row

Re: [sqlite] [EXTERNAL] Fastest way to SELECT on a set of keys?

Stop stop stop > create table x > ( >id integer primay key, >datablob > ); I did not see this until searching for the word PRIMARY and not finding it. Thus id is not a primary key at all. Probably it is a good habit to always add WITHOUT ROWID when there is an explicit primary

Re: [sqlite] Differentiate between an empty result set and an error using Python3

> Op 5 sep. 2019, om 00:10 heeft Keith Medcalf het > volgende geschreven: > > > On Wednesday, 4 September, 2019 12:18, Rob Sciuk wrote: > >> Forgive me if this is an FAQ, but in looking over the python3 interface to >> SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an

Re: [sqlite] Understanding the WITH clause

> Op 15 jun. 2019, om 19:20 heeft Sam Carleton het > volgende geschreven: > > I have kept reading and the next section of the book does away with the > update and simply creates a recursive function. The goal of the function > is to determine the 'weight' of a part by adding up all the

Re: [sqlite] Optimising multiple group by clauses

Hello, > explain query plan select > prod, > per, > min(val) > from > (select >prod, >per, >mar, >sum(val) as val > from >data > group by >prod, >per, >mar) > group by > prod, > per > ; > QUERY PLAN > |--CO-ROUTINE 1 > | `--SCAN TABLE data USING INDEX

Re: [sqlite] Row is not fetched with PRAGMA reverse_unordered_selects=true

> Op 9 mei 2019, om 00:07 heeft Manuel Rigger het > volgende geschreven: > Hi, > > I discovered another bug that is triggered when "PRAGMA > reverse_unordered_selects=true" is used. It's similar to a previous bug > that I reported [1], but the statement triggering the bug has a compound >

Re: [sqlite] Difference between ".import" and tcl/sqlite3 "copy"

> Op 11 mrt. 2019, om 13:20 heeft Graham Holden het > volgende geschreven: > > I'm using SQLite through Tcl, and am having a problem with the > sqlite3/Tcl "copy" command (similar to the shell's ".import" command). > > Given "test.csv" > 1,"aaa","bbb ccc" > > Using the shell, I get the

Re: [sqlite] Using sqlite3_interrupt with a timeout

A question was if a sort is also programmatically interruptable. With the knowledge that the command tool calls sqlite3_interrupt upon the first CONTROL-C, this is easily tested. From the timings below it appears to be so. $ sqlite3 SQLite version 3.25.2 2018-09-25 19:08:10 Enter ".help" for

Re: [sqlite] Suitability for Macintosh OS 9.2?

Hello, I use SQLite on a Macintosh (snow white) with OS 9.2. However SQLite actually runs on a new mac mini and is accessed via virtual network computing, using VNCthing 2.2. On the MacOS side, only desktop sharing must be switched on. I thought I should mention it here as it is exclusively

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

> 2 dec. 2018, Keith Medcalf: > > > Well if it is unique and not null, then why not just make it the rowid? In > either case, you would still have to permute the storage tree at insert time > if the inserts were not in-order. So let us compare them shall we: > > sqlite> create table

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

> 2 dec. 2018, E.Pasma: > >> 30 nov. 2018, AJ Miles: >> >> Ah, this tool seems very handy. For those curious, I'll paste the results >> below. The index approximately doubles the storage size, but I am >> intentionally making that tradeoff to avoid the

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

> 30 nov. 2018, AJ Miles: > > Ah, this tool seems very handy. For those curious, I'll paste the results > below. The index approximately doubles the storage size, but I am > intentionally making that tradeoff to avoid the slow down when enforcing a > unique/primary key on the Reference table

Re: [sqlite] [EXTERNAL] Bug? Confused data entry with column name

and the confusing behaviour is admitted to be a "quirk" in SQLite: https://sqlite.org/quirks.html#double_quoted_string_literals_are_accepted ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

> 25 nov. 2018, 20:14 Digital Dog wrote: > > I may have phrased the subject in a wrong way which misguided you. Sorry, I was misguided by my limited experience with PRIMARY KEY's. I use to combine these with WITHOUT ROWID. As you referred to and quoted from the documentation, it is only in

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

Digital Dog wrote: > > PRAGMA default_without_rowid = on > To make all tables created while the directive is in use the WITHOUT ROWID > tables. .. > > PRAGMA enforce_not_null_on_primary_key = on > For WITHOUT ROWID tables it would be a no-op, but for rowid tables it would > restore correct

Re: [sqlite] Displaying row count

> R Smith: > > Simply add a column to any select like this: et voila... I tried without "partition by 1" and that works as well: "row_number() OVER ()" ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Optmize queries on ranges

> Keith Medcalf wrote: > .. Am I doing something wrong here .. No! The query with order by + limit 1 is superior, also in my test. Still I am surprised that the rtree extension is available by default (at least in the sqlite version 3.25 command line)

Re: [sqlite] Optmize queries on ranges

About the rtree extension, which was the first idea. The extension appears available without any special installation option. This is easier than what is mentioned in https://sqlite.org/rtree.html chapter 2: "Compiling The R*Tree Module". This chapter may as

Re: [sqlite] Filtering groups by non-grouped field.

Clemens Ladisch wrote: > > John Found wrote: >> Also, it seems max(b = ?1) will do the trick as well as count(b = ?1) >> >> And here another question appears. What is more efficient? > > In SQLite, both are equally efficient. > > Use whatever makes the query easier to understand. Clemens, I

Re: [sqlite] Filtering groups by non-grouped field.

> Op 12 okt. 2018, om 11:23 heeft Clemens Ladisch het > volgende geschreven: > > E.Pasma wrote: >> select group_concat(b) as list >> from t >> group by a >> having count(b=?1) >> ; > > In SQLite, a boolean expression returns 0 when false,

Re: [sqlite] Filtering groups by non-grouped field.

> John Found : > > The following code does not work, but gives an idea what I want to do: > >create table t (a, b); > >select > group_concat(b) as list >from t >group by a >having ?1 in (list); > > Clemens Ladisch : > > select >group_concat(b) as list >

Re: [sqlite] [EXTERNAL] shell csv import

> Rowan Worth wrote: > > You can also filter out specific messages at the shell level: > > sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the > rest with NULL' >&2) > > But note that the >() syntax is not a POSIX sh feature, and will not work > in a script using a shebang

Re: [sqlite] [EXTERNAL] shell csv import

> I have a script that loads csv into an existing table. > > I get this message on stderr for each row imported: > > "... expected 7 columns but found 6 - filling the rest with NULL" > > > We have the means to send stdout to /dev/null using the .once or .output > > Is there a way to send

Re: [sqlite] Is there permanent link to the latest SQLite amalgamation source?

John Found wrote: > In order to write an autoupdater, I need to download the latest SQLite > amalgamation. > Is there a permanent link to the subject, or the only way is to parse the > download page > for links to "sqlite-amalgamation-*.zip" or to build it from the fossil > checkout? The apsw

Re: [sqlite] Default Values Pragma bug

Hello Ryan, Your already moderate complaint needs further moderation After reading https://www.sqlite.org/lang_createtable.html#dfltval I see that the default value may be a function name (when written inside parenthesis) or a special

[sqlite] idea for joining a complex view

Hello, I still want to mention an idea for joining a complex view that I used to for the sudoku solver https://www.sqlite.org/lang_with.html#sudoku It is a virtual table with a single row and a column that just echos the value it gets passed in. It is named magnet here. The view is as

Re: [sqlite] Idea: defining table-valued functions directly in SQL

Hello, The new example is clear, and therefore also raises a question: > CREATE TABLE people (name, age); > INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30); > > CREATE VIEW older PARAMETERS (name, otherName) AS > SELECT t1.age > t2.age AS older > FROM people AS t1 WHERE t1.name

Re: [sqlite] Idea: defining table-valued functions directly in SQL

ammed. The outcomes above are equivalent to what generate_series does with these sort of predicates. Not sure how bad this is when achievable in plain SQL. I leave out the remainder of the message and give some personal motivation instead. I want to make a view of the sudoku solver https://www.sqlite.org/la

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] 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] CLI thoughts

Martin wrote: ... Example (maybe via .read): .once .dat select date('now'); .let f system echo "words-`cat .dat`.txt" .once -let f select word from words order by 1; .. Sorry for replying only to this single point. The proposed ".let" command is not

[sqlite] An artificial query whose outcome is changed after left join reduction

Hello, the nature of this case is purely artificial and I thought it is worth considering in the light of real world problem as reported by Raphael Michel. E Pasma .version SQLite 3.23.0 2018-03-24 13:24:02 cf171abe954a5f25262161dd69f2e8cecdbf9446c3f6b298201507dbc743567e zlib version

Re: [sqlite] generate_series can theoretically behave differently in SQLite 3.23.0

Petern wrote: I think your left join reduction regression change happens on any vtable hidden column filter reference to an outer scope column. A CTE duplicates your finding below... Hello Peter, from your message I realize that generate_series is no longer essentiall since SQLite offers

Re: [sqlite] generate_series can theoretically behave differently in SQLite 3.23.0

Richard Hipp wrote: ... I'm testing a patch now that causes the LEFT JOIN strength reduction optimization to assume that NULL arguments to a virtual table constraint can return a TRUE result. But I'm wondering, since this is really a work-around to problems in virtual table implementations, if

Re: [sqlite] non-returned column aliases for repeating expressions?

24 mrt 2018, Wout Mertens: ... SELECT "id" AS _1,"json" AS _2 FROM "testing" WHERE json_extract(json, '$.foo') < 50 ORDER BY json_extract(json, '$.foo') DESC,"id" ... SELECT _1, _2 FROM ( SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM "testing" WHERE _3 < 50 ORDER BY _3

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

Hello Adrián, as you say (I wonder whether the performance is very different from what one gets by manually inserting the WHERE clause in the base case of the recursive CTE.) I wonder too. Still the trick is meant to make a view (without manually inserted predicates inside) Thanks for

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

Adrián Medraño Calvo wrote: The following SQL script shows a query selecting data from a recursive CTE and filtering it. I expected the optimizer to apply the filter to the recursive CTE directly, and indeed the documentation of pushDownWhereTerms (src/select.c:3833) indicates this

Re: [sqlite] missing subquery flattening

Mark Brand wrote: On 26/01/18 19:35, Clemens Ladisch wrote: Mark Brand wrote: Shouldn't we expect subquery flattening to happen in V2 below? -- no flattening CREATE VIEW V2 AS SELECT * FROM X LEFT JOIN ( SELECT * FROM X LEFT JOIN Y ON Y.a = X.a ) Z ON Z.a =

Re: [sqlite] SQLite 3.22.0 coming soon

Dear SQLite developers and eventual PowerPC users, using a likely outdated compiler: powerpc-apple-darwin9-gcc-4.0.1 a compile error occors: shell.c:10062: error: conflicting types for ‘integerValue’ shell.c:9169: error: previous implicit declaration of ‘integerValue’ was here

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

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

Dinu wrote: Hi all, I've ran into an optimisation problem with a double-left join that works as an "either" clause. The query is as follows: SELECT * FROM a LEFT JOIN b ON LEFT JOIN c ON WHERE b.someId IN (1,2,3) OR c.someId IN (4,5) This results in a bloated execution plan: SEARCH

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Clemens Ladisch wrote: Luuk wrote: On 01-01-18 03:14, Shane Dev wrote: select * from nodes where not exists (select * from edges where child=nodes.id); Changing this to: select * from nodes where not exists (select 1 from edges where child=nodes.id); saved in my test about 10% of time

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Shane Dev wrote: Hi Clemens, Your query is much faster on my system - thanks! Apart from visual inspection and testing, is there anyway to be sure your query selects the same results as my query? From https://sqlite.org/queryplanner.html "When programming in SQL you tell the system what

Re: [sqlite] How to prevent the insertion of cycles into a hierarchical table?

On 24/12/2017 11:56, Shane Dev wrote: Related to my previous question https://www.mail-archive.com/sqlite-users@mailinglists.sqlit e.org/msg107527.html, I want to prevent the client from inserting a cycle. For example - sqlite> .sch edges CREATE TABLE edges(parent integer not null, child

Re: [sqlite] How to detect cycles in a hierarchical table?

Lifepillar wrote: On 20/12/2017 22:31, Shane Dev wrote: Hello, I have an edges table - sqlite> .sch edges CREATE TABLE edges(parent, child); sqlite> select * from edges; parent child 1 2 1 3 2 4 3 1 4 5 5 2 Here we have two cycles - 1) 1 => 3 => 1 (length

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

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

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-

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

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

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

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

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');

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 (

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

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

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

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

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

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

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] 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] 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] {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] {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] {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] 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] 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, 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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

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

  1   2   >