Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

2019-11-08 Thread Keith Medcalf
SQLITE_DETERMINISTIC does not mean that the function is only called once for each unique set of arguments, only that when called with a unique set of arguments that it returns the same result. This means that if it is a constant it can be factored out of being called more than once. In your

Re: [sqlite] Feature request: (VALUES (1), (2), (3)) AS t(n)

2019-11-07 Thread Keith Medcalf
How about something like: with t(a, b) as (values (1, 1), (2, 2)) select a, b from t; -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Ainar Garipov >Sent:

Re: [sqlite] Deterministic random sampling via SELECT

2019-11-07 Thread Keith Medcalf
On Thursday, 7 November, 2019 04:55, Merijn Verstraaten wrote: >I'm trying sample a (deterministically) random subset of a SELECT query, You cannot have something that is both RANDOM and DETERMINISTIC at the same time. >the most common solution on the internet to get random samples seems to

Re: [sqlite] Find stmt that's stopping a DB from closing

2019-11-05 Thread Keith Medcalf
>The first statement associated with an open connection is pointed to by >the pStmt of that connection. Once you've found the first statement call >sqlite3_next_stmt() >on it to find the next one. > > >Unfortunately the database connection is

Re: [sqlite] Rounding Error

2019-11-04 Thread Keith Medcalf
On Monday, 4 November, 2019 02:10, Jay Kreibich wrote: >OK, no, I’m wrong. Because 1.05 rounds to 1.1, even though the >representation is 1.049523162841796875. The representation of 1.05 is 1.0500444089209 and the next closest representable number is

Re: [sqlite] Rounding Error

2019-11-04 Thread Keith Medcalf
On Monday, 4 November, 2019 02:16, Graham Holden wrote: >This is almost certainly because (according to >https://www.h-schmidt.net/FloatConverter/IEEE754.html) the number >"1.15" cannot be expressed exactly in floating-point; the nearest >expressible number being 1.1497615814208984375, and

Re: [sqlite] Rounding Error

2019-11-04 Thread Keith Medcalf
On Monday, 4 November, 2019 02:10, Jay Kreibich wrote: >> On Nov 4, 2019, at 2:59 AM, Jay Kreibich wrote: >>> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin wrote: >>> The simplest example I have found with x=1 is: >>> "select round(1.15,1)" >>> Result: "1.1" (should be 1.2) >> SQLite uses the

Re: [sqlite] DELETE extremely slow (.expert command)

2019-11-02 Thread Keith Medcalf
e> create table y(b,c); sqlite> .expert sqlite> select a, x.b, c from x,y where x.b == y.b; CREATE INDEX y_idx_0062 ON y(b); SCAN TABLE x (~1048576 rows) SEARCH TABLE y USING INDEX y_idx_0062 (b=?) (~10 rows) sqlite> >- Original Message ----- >From: Keith Medcal

Re: [sqlite] DELETE extremely slow

2019-11-01 Thread Keith Medcalf
On Friday, 1 November, 2019 16:04, Thomas Kurtz wrote: > The database schema is not a secret. If it helps, I can post it, that's > no problem. Is it enough to run ".dump" on a database without data? Just run ".schema" or ".schema --indent" which will output only the schema definitions and not

Re: [sqlite] DELETE extremely slow

2019-11-01 Thread Keith Medcalf
On Friday, 1 November, 2019 15:12, Simon Slavin wrote: >So the slow-down in the DELETE FROM command is caused by a TRIGGER, but >there are no TRIGGERs on DELETE ? I don't understand that. Can someone >explain, please ? The code indicates that they are AFTER DELETE so presumably they are

Re: [sqlite] DELETE extremely slow

2019-11-01 Thread Keith Medcalf
On Thursday, 31 October, 2019 16:54, Thomas Kurz : >I did it again, same file: >SQLite version 3.30.1 2019-10-10 20:19:45 >Enter ".help" for usage hints. >sqlite> pragma foreign_keys=on; >sqlite> .timer on >sqlite> delete from dataset; >Run Time: real 5249.891 user 2412.812500 sys 2606.531250

Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL

2019-11-01 Thread Keith Medcalf
On Friday, 1 November, 2019 10:20, Simon Slavin wrote: >On 1 Nov 2019, at 4:17pm, Simon Slavin wrote: >WHERE (c1 IS NULL) OR (C1 != 2) > which could quite reasonably return rows. However, the NULL possibility > may be redundant. I can't tell without tests. The expression NOT (c1 IS

Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL

2019-11-01 Thread Keith Medcalf
On Friday, 1 November, 2019 09:12, And Clover wrote: > CREATE TABLE t0 (c0); > CREATE TABLE t1 (c1); > INSERT INTO t0 VALUES (1); > SELECT c0 > FROM t0 LEFT JOIN t1 ON c1=c0 > WHERE NOT (c1 IS NOT NULL AND c1=2); >Expected result: (1) >Actual result: no rows

Re: [sqlite] DELETE extremely slow

2019-11-01 Thread Keith Medcalf
One of your triggers requires and index on item(nameid) and there is no index on item(nameid). Hence it is doing a table scan to find the rows matching this trigger. That is why the plan has multiple "SCAN item" in it. -- The fact that there's a Highway to Hell but only a Stairway to

Re: [sqlite] DELETE extremely slow

2019-11-01 Thread Keith Medcalf
One of your delete triggers is causing a table scan of table item. Multiple scans of table item, for each row of dataset. From the code it looks like it is scanning for a match on "nameid". And there is not index on item(nameid ...) ... -- The fact that there's a Highway to Hell but only

[sqlite] mkkeywordhash crashes with MSVC works with GCC commit f12e743e

2019-10-31 Thread Keith Medcalf
Crashes in reorder function when it tries to recurse when using MSVC compiler. GCC (MingW) works fine. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users

Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Keith Medcalf
On Thursday, 31 October, 2019 03:51, Thomas Kurz wrote: >I experimentally imported the same data into a MariaDB database and tried >the same operation there (without paying attention to creating any >indexes, etc.). It takes only a few seconds there. According to the MariaDB reference manual,

Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Keith Medcalf
On Thursday, 31 October, 2019 10:52, Simon Slavin wrote: >On 31 Oct 2019, at 4:29pm, Keith Medcalf wrote: >> If the elapsed time is much greater than the sum of user+sys time then >> I would suspect it is still I/O thrashing (or the machine is really busy >> doing so

Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Keith Medcalf
On Thursday, 31 October, 2019 10:01, Dominique Devienne wrote: >On Thu, Oct 31, 2019 at 4:10 PM Thomas Kurz wrote: >> Yes, but I'd expect that MariaDB has to do the same, but takes clearly >> less than 1 minute instead of 88 minutes... :confused: >Are we comparing apples to oranges here?

Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Keith Medcalf
>pragma foreign_keys=on; >pragma journal_mode=wal; >.timer on >delete from dataset; >--> Run Time: real 197993.218 user 53015.593750 sys 54056.546875 Wow. That is 14 hours each of System and User time and then and additional 24 hours of "waiting for something to happen" time. Do you have

Re: [sqlite] [sqlite-dev] Error 25: column index out of range when binding for a DATETIME

2019-10-31 Thread Keith Medcalf
On Thursday, 31 October, 2019 07:17, Jeffrey Walton wrote: First, your question should go to the sqlite-users mailing list, not the developers mailing list. >I'm having trouble binding a parameter for a DELETE. I am trying to >delete records older than 120 days. Below, I have a table with a

Re: [sqlite] Multiple files for a single SQLite database

2019-10-31 Thread Keith Medcalf
See https://sqlite.org/limits.html 11. Maximum Number Of Attached Databases The ATTACH statement is an SQLite extension that allows two or more databases to be associated to the same database connection and to operate as if they were a single database. The number of simultaneously attached

Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Keith Medcalf
If you are deleting all the rows of a table, then you can simply truncate the table (SQLite will do this). However if the table you are deleting all the rows from have dependent (child) tables *and* foreign key enforcement is turned on, then the rows have to be deleted on at a time so that

Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Keith Medcalf
You have no index on trace(datasetid) ... You have no index on metadata(datasetid) though the compound indexes in which datasetid is the first element *should* be sufficent. .eqp on or .eqp full before issuing the delete command will tell you what the plan is. -- The fact that there's a

Re: [sqlite] SQLITE_ERROR instead of SQLITE_BUSY or clarification of busy state errors

2019-10-30 Thread Keith Medcalf
On Wednesday, 30 October, 2019 16:33, mailing lists wrote: >I face the following issue: >1) SQLite has been compiled with SQLITE_THREADSAFE=1 and >SQLITE_DEFAULT_SYNCHRONOUS=3 >2) I am opening in a thread a new database (standard journal mode) and >creating some tables, indices etc. (explicit

Re: [sqlite] Generated columns in primary keys, indexes, and constraints

2019-10-30 Thread Keith Medcalf
On Wednesday, 30 October, 2019 13:23, Darren Duncan wrote: >On 2019-10-30 12:02 p.m., Simon Slavin wrote: >> On 30 Oct 2019, at 6:56pm, Darren Duncan wrote: >>> "Generated columns may not be used as part of the PRIMARY KEY. (Future >>> versions of SQLite might relax this constraint for

[sqlite] PureFunc Detection Fix

2019-10-30 Thread Keith Medcalf
The "not working" PureFunc detection is because the sqlite3NotPureFunc needs to be checked in isDate if it is called with 0 arguments (in which a single parameter of 'now' is assumed). This should fix the purefunc detection for CHECK constraints and Indexes. I still think that OP_Function

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

2019-10-30 Thread Keith Medcalf
On Wednesday, 30 October, 2019 06:41, Dominique Devienne wrote: >On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp wrote: >> On 10/29/19, Keith Medcalf wrote: >> > Before you change anything, I think that is incorrect for the various >> > datetime functions. I thi

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

2019-10-30 Thread Keith Medcalf
On Tuesday, 29 October, 2019 23:05, Simon Slavin wrote: >On 30 Oct 2019, at 3:58am, Keith Medcalf wrote: >> Before you change anything, I think that is incorrect for the various >> datetime functions. I think they SHOULD be permitted in CHECK >> constraints and in gen

Re: [sqlite] INSERT/UPDATE generated columns?

2019-10-29 Thread Keith Medcalf
On Tuesday, 29 October, 2019 20:48, no...@null.net wrote: >The gencol.html document does not describe the result of attempting to >INSERT INTO or UPDATE a generated column. Does this raise an error (my >preference) or is it simply ignored? Could the behaviour be added to >the documentation?

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

2019-10-29 Thread Keith Medcalf
-- >From: sqlite-users On >Behalf Of Richard Hipp >Sent: Tuesday, 29 October, 2019 19:48 >To: SQLite mailing list >Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have >SQLITE_FUNC_CONSTANT ? > >On 10/29/19, Keith Medcalf wrote: >> Can the >

[sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

2019-10-29 Thread Keith Medcalf
>sqlite3 SQLite version 3.31.0 2019-10-29 16:18:45 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table x(data, createdon default (current_timestamp), updatedon as (current_timestamp) stored);

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

2019-10-29 Thread Keith Medcalf
On Tuesday, 29 October, 2019 16:17, Warren Young wrote: >On Oct 29, 2019, at 7:20 AM, Simon Slavin wrote: >One question I had after reading the draft doc is whether an application- >defined SQLITE_DETERMINISTIC function can be used to compute a generated >column. My immediate use case for

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

2019-10-29 Thread Keith Medcalf
On Tuesday, 29 October, 2019 12:25, Doug wondered: >The draft says "Nor may a generated column depend on the ROWID." >If my table uses ROWID by default: >CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a)); >where id is ROWID by default, is the generated column disallowed

[sqlite] https://www.sqlite.org/draft/gencol.html Typo

2019-10-29 Thread Keith Medcalf
At the end of the second paragraph of section 2.1: Only VIRTUAL tables can be added using ALTER TABLE. should be Only VIRTUAL columns can be added using ALTER TABLE. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

Re: [sqlite] LEFT JOIN: ON vs. WHERE

2019-10-28 Thread Keith Medcalf
On Monday, 28 October, 2019 11:19, James K. Lowden wrote: >When wrestling with this topic, it's useful to remember that ON doesn't >constrain the outer table: > > FROM R LEFT OUTER JOIN S > ON R.key = S.key AND R.col = 'foo' >Here, > AND R.col = 'foo' >while valid syntax

Re: [sqlite] LEFT JOIN: ON vs. WHERE

2019-10-27 Thread Keith Medcalf
On Sunday, 27 October, 2019 16:09, Benjamin Asher wrote: > Is there an advantage to putting WHERE-type filtering in the > ON constraint vs leaving it in the WHERE clause for LEFT JOINs? The ON clause is merely syntactic sugar for a parenthesized AND clause appended to the parenthesized WHERE

Re: [sqlite] Roadmap?

2019-10-27 Thread Keith Medcalf
On Sunday, 27 October, 2019 13:30, Richard Damon wrote: >On 10/27/19 10:24 AM, Simon Slavin wrote: >> On 27 Oct 2019, at 9:12am, Thomas Kurz wrote: >>> the discussion standard deviation has shown that at least STDEV and >>> POWER would be very helpful if they part of SQLite core. >> These

Re: [sqlite] Stream loading SQL script

2019-10-27 Thread Keith Medcalf
m: sqlite-users on >behalf of František Kučera >Sent: Saturday, October 26, 2019 4:49:26 PM >To: sqlite-users@mailinglists.sqlite.org us...@mailinglists.sqlite.org> >Subject: Re: [sqlite] Stream loading SQL script > >Dne 25. 10. 19 v 21:41 Keith Medcalf napsal(a): >> Th

Re: [sqlite] Bug: Infite loop on SELECT with .explain on

2019-10-26 Thread Keith Medcalf
While on errata for the shell, there is a little cosmetic bugaboo with the output of .eqp full: (1) When .mode col is in effect the "explain" column widths are used, not the .width or the defaults used when .eqp full is not in effect. (2) .head on is ignored -- table output column headers are

Re: [sqlite] Single or double quotes when defining alias?

2019-10-25 Thread Keith Medcalf
On Friday, 25 October, 2019 20:45, Simon Slavin wrote: >On 25 Oct 2019, at 10:55pm, Thomas Kurz wrote: >> SELECT column2 AS "d" >If you want to do it, do it like that. Double quotes indicate an entity >name. Single quotes indicate a string of characters. >However, almost nobody quotes

Re: [sqlite] What is the default value of isolation_level?

2019-10-25 Thread Keith Medcalf
On Friday, 25 October, 2019 13:49, Peng Yu wrote: >isolation_level is an empty string by default as shown below. But the >python manual does not say that it can take an empty string. What does >an empty string mean? Is it equivalent to None? Thanks. No. It means use the default. And sqlite3

Re: [sqlite] What does commit actually do?

2019-10-25 Thread Keith Medcalf
You can tell if you are in a transaction by the in_transaction property of the connection. This tracks whether or not a BEGIN of some kind has been issued by the wrapper (either through magic or because you explicitly issued a command to BEGIN a transaction). If in_transaction is True, then

Re: [sqlite] Stream loading SQL script

2019-10-25 Thread Keith Medcalf
The sqlite3 command line shell already does this. see function process_input -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of František Kucera >Sent: Friday, 25

Re: [sqlite] What is the default value of isolation_level?

2019-10-25 Thread Keith Medcalf
On Friday, 25 October, 2019 14:02, Peng Yu wrote: >So basically an empty string in isolation_level is the same as >"DEFERRED"? This is confusing. I think it should have been implemented >so that the value of the isolation_level attribute is DEFERRED when it >is not specified in .connect(). But

Re: [sqlite] What does commit actually do?

2019-10-25 Thread Keith Medcalf
On Friday, 25 October, 2019 10:44, Peng Yu wrote: >The python manual just tell me what I should do but it is not very >clear what commit() actually does under the hood. >https://docs.python.org/2/library/sqlite3.html >""" >commit() >This method commits the current transaction. If you

Re: [sqlite] Can SQLite do this in a single query?

2019-10-24 Thread Keith Medcalf
Well, "paint" is to draw your output. So, for example, you can output an arbitrary matrix like this: select columnHeader from theColumns order by columnHeader; from which you can output "" then for each row retrieved output "" columnHeader "" and when you run out of rows "" and keep track of

Re: [sqlite] Optimizing `SELECT a, max(b) GROUP BY a`

2019-10-24 Thread Keith Medcalf
It appears that the optimizer will not utilize a skip-scan *AND* apply the max optimization concurrently. select name, ( select max(timestamp) from table where name=outer.name ) from ( select distinct name from table ); The

Re: [sqlite] Different column items to fields (transpose)

2019-10-23 Thread Keith Medcalf
create table num_values ( id integer, entry_date integer, term_text text, numeric_value Real ); insert into num_values values (2, 40100, 'Weight', 80); insert into num_values values (2, 40100, 'Height', 170); insert into num_values values (2, 40100, 'BMI', 27.7); insert into

Re: [sqlite] Can SQLite do this in a single query?

2019-10-22 Thread Keith Medcalf
On Tuesday, 22 October, 2019 23:24, Doug wrote: >Please explain one phrase in the select: > total(1) over (partition by city_txt, mode_txt) >Is "total" a function and does "1" refer to city_txt? >I can't wrap my head around what's going on with this phrase. total() is a built-in aggregate

Re: [sqlite] Theoretical write performance for low-throughput devices

2019-10-22 Thread Keith Medcalf
Ok, skip that. locking_mode=EXCLUSIVE (1) takes care of that. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Tuesday, 22 Octo

Re: [sqlite] Theoretical write performance for low-throughput devices

2019-10-22 Thread Keith Medcalf
>and only one thread accesses that connection at a time. The target >lacks mmap(), posix file locks, and multiple address spaces, so this >seemed like the right settings to use. So what happens to the shm file? Is in not normally just an ordinary file that is mmap'ped? What would be the

Re: [sqlite] Can SQLite do this in a single query?

2019-10-22 Thread Keith Medcalf
y city_txt; This is "standard SQL" and should work with just about any RDBMS using SQL of any variation. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users O

Re: [sqlite] Can SQLite do this in a single query?

2019-10-22 Thread Keith Medcalf
a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Tuesday, 22 October, 2019 08:11 >To: SQLite mailing list >Subject: Re: [sqlite] Can SQLite do this in a single query? > >CREATE T

Re: [sqlite] Can SQLite do this in a single query?

2019-10-22 Thread Keith Medcalf
CREATE TABLE employees(employee_id, city_id, mode_id); CREATE TABLE citys(city_id, city_txt); CREATE TABLE modes(mode_id, mode_txt); select city_txt, mode_txt, total(1) over (partition by city_txt, mode_txt) / total(1) over (partition by city_txt) as percentage from

Re: [sqlite] Can SQLite do this in a single query?

2019-10-21 Thread Keith Medcalf
2019 at 3:44 PM Keith Medcalf wrote: >> On Monday, 21 October, 2019 08:31, Winfried wrote: >> >> >Using the following tables, I need to find how employees from each >> city come to work. >> >> >== Employees table: >> >EMPLOYEE_ID | CITY_ID &

Re: [sqlite] Can SQLite do this in a single query?

2019-10-21 Thread Keith Medcalf
On Monday, 21 October, 2019 08:31, Winfried wrote: >Using the following tables, I need to find how employees from each city >come to work. >== Employees table: >EMPLOYEE_ID | CITY_ID >Cities table: >CITY_ID | CITY_TXT >Mode table: >MODE_ID | MODE_TXT > >This is the type of output I need to

Re: [sqlite] [EXTERNAL] Re: select for power-meter accumulated total readings

2019-10-21 Thread Keith Medcalf
On Monday, 21 October, 2019 00:49, Hick Gunter wrote: >The desire to charge consumers more for "peak power" (just like utilities >have to pay more for "peak power" and less for "base load") is the >driving force behind the installation of "smart meters". This is the propaganda behind "smart

Re: [sqlite] select for power-meter accumulated total readings

2019-10-20 Thread Keith Medcalf
On Sunday, 20 October, 2019 06:58, Petr Jakeš wrote: >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf wrote: >> On Saturday, 19 October, 2019 18:26, Petr Jakeš >> wrote: >>> After long time I have set up development environment properly and I >>> am ab

Re: [sqlite] Why sqlite3 running at different time can have very different speed?

2019-10-19 Thread Keith Medcalf
o this). The Operating System, however, may "help" you by storing files in RAM assuming that you have enough RAM and that it is not needed for something else. >On Sat, Oct 19, 2019 at 7:29 PM Keith Medcalf >wrote: >> >> >> On Saturday, 19 October, 2019 18:05, P

Re: [sqlite] Standard deviation last x entries

2019-10-19 Thread Keith Medcalf
Here is a recursive CTE that will calculate the square root to the best precision your processor math implementation is capable of. It limits the recursion by prohibiting the insertion of duplicate guesses by using UNION rather than UNION ALL, which will cause pathological cases that

Re: [sqlite] select for power-meter accumulated total readings

2019-10-19 Thread Keith Medcalf
r by julianday(p.timestamp) desc > limit 1 >) > from power as c > order by julianday(timestamp) > ) >select * from pwr > >On Tue, Sep 3, 2019 at 1:11 AM Keith Medcalf wrote: > >> >> On Monday, 2 September, 2019 12:26, Petr Jakeš >

Re: [sqlite] Why sqlite3 running at different time can have very different speed?

2019-10-19 Thread Keith Medcalf
On Saturday, 19 October, 2019 18:05, Peng Yu wrote: Looks like the difference between reading from disk and reading from cache. >I see that sqlite3 can be very different in terms of run time. >$ time sqlite3 file.sqa -Atv > /dev/null > >real0m3.259s >user0m0.193s >sys0m0.704s >$

Re: [sqlite] Standard deviation last x entries

2019-10-18 Thread Keith Medcalf
ber, 2019 16:48 >To: SQLite mailing list >Subject: Re: [sqlite] Standard deviation last x entries > >> However, to optimize the calculation the following is more efficient > >I tested it, but didn't find it any faster. >Naming makes it a lot clearer though. > >RBS

Re: [sqlite] Standard deviation last x entries

2019-10-18 Thread Keith Medcalf
We are calculating the square root using Newtons Method of successive approximation https://pages.mtu.edu/~shene/COURSES/cs201/NOTES/chap04/sqrt.html (x>0)*(y+x/y)/2 yi is computing the new guess of the square root of x based on the last guess y and calling that calculation yi (the AS

Re: [sqlite] Last record

2019-10-15 Thread Keith Medcalf
On Tuesday, 15 October, 2019 13:52, Don V Nielsen wrote: >Keith, what if one has a peanut allergy? Well, if one were allergic to beans (colloquially called peanuts for some reason, even though they are not nuts) then I suppose real nuts would do. I have a big tin of mixed Honey Roasted Nuts

Re: [sqlite] Last record

2019-10-15 Thread Keith Medcalf
On Tuesday, 15 October, 2019 09:35, Philippe RIO <51...@protonmail.ch> wrote: >A short question : how could I know if I am reading the last record with >sqlite (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a >function for that case which returns SQLITE_DONE? A function which is

Re: [sqlite] rationale for private archives of the SQLite mailing list and suppressed reply-to

2019-10-14 Thread Keith Medcalf
On Monday, 14 October, 2019 14:18, Warren Young wrote: >Fossil Forums allow you to subscribe to email notifications. From the >reader’s perspective, it’s really very little different from the current >Mailman based scheme. The preceding paragraph is completely at odds with the following

Re: [sqlite] Possible bug in storing text values in numeric columns

2019-10-13 Thread Keith Medcalf
sqlite> create table x(x numeric); sqlite> insert into x values ('0012'); sqlite> select typeof(x), x from x; integer|12 sqlite> select printf('%04d', x) from x; 0012 Presentation is a user/application problem. Not a database data problem. -- The fact that there's a Highway to Hell but only a

Re: [sqlite] Ensure a snapshot remains readable

2019-10-13 Thread Keith Medcalf
On Sunday, 13 October, 2019 15:23, Adam Levy wrote: >My application requires a way to build a "pending state" in the database >while allowing users to query data from both the "official state" and the >"pending state". I am achieving this using sessions and snapshots. SQLite3 does not do

Re: [sqlite] Ensure WAL file for sqlite3_snapshot_get

2019-10-13 Thread Keith Medcalf
On Sunday, 13 October, 2019 01:41, Adam Levy wrote: >One of the requirements for sqlite3_snapshot_get is that "One or more >transactions must have been written to the current wal file since it was >created on disk (by any connection). This means that a snapshot cannot be >taken on a wal mode

Re: [sqlite] Standard deviation last x entries

2019-10-12 Thread Keith Medcalf
#!python3 from __future__ import absolute_import, division, print_function, unicode_literals import apsw db = apsw.Connection() db.executescript(""" create table x ( value integer not null ); insert into x values (120), (130), (140), (110); """) # Method 1: Using partial Running calc

Re: [sqlite] Standard deviation last x entries

2019-10-12 Thread Keith Medcalf
The Standard Deviation of the Population is the Square Root of the mean of the second order differences. For your input values, you calculate the mean. Then you calculate the mean of the square of the difference between each value and the mean of the values. Then you take the square root of

Re: [sqlite] Date time input

2019-10-09 Thread Keith Medcalf
On Tuesday, 8 October, 2019 12:40, James K. Lowden wrote: >OK, but it needs a better name. What better place than here to debate >that? ;-) >What the opposite of "Lite"? I believe the PC euphemism is Big and Tall. SQLBAT3? -- The fact that there's a Highway to Hell but only a Stairway

Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

2019-10-09 Thread Keith Medcalf
a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Wednesday, 9 October, 2019 13:04 >To: SQLite mailing list >Subject: Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't &g

Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

2019-10-09 Thread Keith Medcalf
On Wednesday, 9 October, 2019 12:01, Jens Alfke said: >BETWEEN doesn't work well because it's inclusive, i.e. `BETWEEN 'foo' and >'fop'` doesn't work because it matches 'fop'. Coming up with the upper >end of a string prefix match is super annoying — `BETWEEN 'foo' and >'foo\xff' only works

Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

2019-10-09 Thread Keith Medcalf
Unable to reproduce. In particular: >SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ? >tells me that it can and will use the (primary key) index on the name >column. will not use the index. I can make it use an index by doctoring the table data to make the index scan cheaper than a table

Re: [sqlite] Date time input

2019-10-07 Thread Keith Medcalf
>Because that's locale-dependent. Some countries, like the US, use >month/day/year; most other countries use day/month/year. Maybe. Canada supposedly uses the day/month/year format, or so I suddenly became aware of in 1998 when I was in my mid 30's. Prior to that day it was m/d/y. Then

Re: [sqlite] [patch] avoid dynamic alloc in vdbeSorterSort(...)

2019-10-07 Thread Keith Medcalf
On Monday, 7 October, 2019 14:58, Dominique Pellé wrote: >Here the allocated size is fixed (always 64 pointers), so alloca does >not seem needed. >I wonder how many other functions could avoid dynamic allocation >like this one (either with a stack array or alloca). Probably a lot. I would

Re: [sqlite] Date time input

2019-10-07 Thread Keith Medcalf
On Monday, 7 October, 2019 07:17, Jose Isaias Cabrera wrote: >I have to ask this question: Why is it that the date function does not >take a '4/5/2019' and returns '2019-04-05'? Because it does not. Humans read things in big endian order. For example, in the common base 10 system used

Re: [sqlite] Copy-on-write VFS

2019-10-07 Thread Keith Medcalf
On Monday, 7 October, 2019 03:16, Simon Slavin wrote: >> Given that we disable ceckpointing, can we assume that the main >> database-file will never be modified, and therefor could potentially be >> mounted read-only? No, and No. >You are correct that the WAL file contains database pages,

Re: [sqlite] Online backup of in memory database

2019-10-06 Thread Keith Medcalf
On Sunday, 6 October, 2019 13:03, Kadirk wrote: >We already have an application specific WAL file, sqlite updates + >application data is in this WAL file. We are taking snapshot of sqlite + >application data to the disk to truncate WAL file, then we can rebuild >latest state whenever needed

Re: [sqlite] Online backup of in memory database

2019-10-06 Thread Keith Medcalf
On Saturday, 5 October, 2019 15:44, Kadirk wrote: >How to do online backup of an in memory database (to disk)? An in memory database is a transient object. Why would you want to back it up? >Planning to use in memory database with 10 gb+ data, there are queries >continuously so stopping

Re: [sqlite] Error 19 with Caret ^ INSERT

2019-10-06 Thread Keith Medcalf
How do you know that the "error" is related to the use of a caret? Perhaps your .net wrapper is diddling text strings that contain carets for some reason known only to the wrapper writer? Have you checked the wrapper documentation to see if such diddling is documented, and if so, how to turn

Re: [sqlite] Copy-on-write VFS

2019-10-04 Thread Keith Medcalf
You still have not explained what you think COW is and of what possible use it could be. If you want a "snapshot + changes" then why not just enable WAL mode and disable checkpointing? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated

Re: [sqlite] Copy-on-write VFS

2019-10-04 Thread Keith Medcalf
On Friday, 4 October, 2019 05:18, Fredrik Larsen wrote: >A copy-on-write IO-path where data is split into static and dynamic parts >(think snapshots for storage) would be very helpful for our project, . What do you mean? Useful how? >This would simplify backups, testing, moving data around in

Re: [sqlite] sqlite database changes not committed immediately

2019-10-03 Thread Keith Medcalf
Those examples appear to be using a database resident on a remote filesystem. Is the database located on a remote filesystem? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users

Re: [sqlite] Lookup join

2019-10-01 Thread Keith Medcalf
On Tuesday, 1 October, 2019 11:58, Fredrik Larsen wrote: >Thanks Keith! I have spent several days trying to tune my query towards >expected performance, without luck. I somehow missed your fairly straight >forward solution. I still have some problems making sqlite use the >correct indexes, but

Re: [sqlite] Newbie Issues with COLLATE

2019-10-01 Thread Keith Medcalf
On Tuesday, 1 October, 2019 11:22, Jim and Carol Ingram wrote: >I'm trying to use SQLite3.exe (the command-line executable) to do some >data mining of a database generated by a commercial software product that >incorporates the .dll version (I can see sqlite3.dll in its Program Files >folder).

Re: [sqlite] Lookup join

2019-09-30 Thread Keith Medcalf
ays a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Monday, 30 September, 2019 19:31 >To: SQLite mailing list >Subject: Re: [sqlite] Lookup join > > >You mean something like this: &

Re: [sqlite] Lookup join

2019-09-30 Thread Keith Medcalf
You mean something like this: select key, maxrev, data from ( select key, null as maxrev, null as data from t1 where key not in (select key from t2

Re: [sqlite] Conflict between snapshots and checkpoints

2019-09-30 Thread Keith Medcalf
On Monday, 30 September, 2019 02:06, Dominique Devienne wrote: >On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf wrote: >> On Sunday, 29 September, 2019 01:28, Gwendal Roué >> wrote: >> >Those N reader connections allow concurrent database reads. Those >>

Re: [sqlite] Conflict between snapshots and checkpoints

2019-09-29 Thread Keith Medcalf
On Sunday, 29 September, 2019 01:28, Gwendal Roué wrote: >But now I fail to understand the indented use case of sqlite3 snapshots.. >Why allow to reuse snapshots with several calls to open()? Why do they >exist at all, since we can already profit from snapshot isolation with >one transaction

Re: [sqlite] disable file locking mechanism over the network

2019-09-27 Thread Keith Medcalf
On Friday, 27 September, 2019 22:11, Roman Fleysher wrote: >Based on the link you provide, it looks like I need unix-none VFS and >specify it as: >ATTACH 'file:demo2.db?vfs=unix-none' AS demo2; Yup. I think you can also do that when opening the file on the sqlite3_open_v2 call by using the

Re: [sqlite] disable file locking mechanism over the network

2019-09-27 Thread Keith Medcalf
_ >From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on >behalf of Keith Medcalf [kmedc...@dessus.com] >Sent: Friday, September 27, 2019 11:12 PM >To: SQLite mailing list >Subject: Re: [sqlite] disable file locking mechanism over the network > >On Friday, 27 Septemb

Re: [sqlite] disable file locking mechanism over the network

2019-09-27 Thread Keith Medcalf
Yeah, WAL mode cannot work over a remote connection as the WAL index is a shared memory file -- which can only be accessed by processes on a single computer. If you are using FLoM to co-ordinate your "application" use of SQLite3, then maybe you want to look at using the dotfile VFS which uses

Re: [sqlite] disable file locking mechanism over the network

2019-09-27 Thread Keith Medcalf
On Friday, 27 September, 2019 17:00, Roman Fleysher wrote: >I am using SQLite over GPFS distributed file system. I was told it >honestly implements file locking. I never experienced corruption. But it >is slow in the sense that when many jobs from many compute nodes try to >access the same

Re: [sqlite] LIKE optimization when the LHS is an indexed expression?

2019-09-27 Thread Keith Medcalf
IF the LIKE optimization applied where the LHS of the like operator were an expression, then the index on that expression would have to have the appropriate collation. In otherwords for a case_insensitive_like (the default) the index would have to be collate nocase in order to be useable, and

Re: [sqlite] localtime on current_time differs from localtime on current_timestamp

2019-09-26 Thread Keith Medcalf
On Thursday, 26 September, 2019 05:33, PALAMARA Alain wrote: >I'm quite new to sqlite and I'm surprised about the result I got from >executing time(current_time, 'localtime') function. >I use the sqlite3 command line (version 3.29.0) on Windows 7 and I tried >to get my local time using this

Re: [sqlite] Safe to use SQLite over a sketchy network?

2019-09-25 Thread Keith Medcalf
-Original Message- >From: sqlite-users On >Behalf Of Gary R. Schmidt >Sent: Wednesday, 25 September, 2019 23:13 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Safe to use SQLite over a sketchy network? > >On 26/09/2019 15:00, Jens Alfke wrote: >>

<    1   2   3   4   5   6   7   8   9   10   >