Re: [sqlite] Can it (should it) be done in SQL?

2020-01-18 Thread Keith Medcalf
Mayhaps like this? CREATE TABLE Goals ( period integer primary key, amount integer not null ); CREATE TABLE Data ( period integer not null references Goals(period), type text not null, amount integer not null ); create index Data_Period on Data (period); INSERT INTO Goals

Re: [sqlite] How can I detect rows with non-ASCII values?

2020-01-18 Thread Keith Medcalf
the actual encoding. -- 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: Saturday, 18 January, 2020 05:38 >To: SQLite mailing lis

Re: [sqlite] How can I detect rows with non-ASCII values?

2020-01-18 Thread Keith Medcalf
On Saturday, 18 January, 2020 05:21, Rocky Ji wrote: >> > GLOB supports character classes >thanks for teaching new keyword and its use. >My first attempt was very similar to what you suggest, except I used >sqlite3 and re from inside Python. >But as you see, I can't reliably seprate

Re: [sqlite] How can I detect rows with non-ASCII values?

2020-01-18 Thread Keith Medcalf
On Saturday, 18 January, 2020 05:13, Rocky Ji wrote: >Sorry for lack of clarity. >By question marks, I meant- that some text, like Dutch programmers names, >and address in Nordic locations, have accents and umaults and other such >modifications done to English-alphabets. These get displayed as

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

2020-01-15 Thread Keith Medcalf
What did you define SQLITE_MAX_ATTACHED as when you compiled the DLL? The default limit is 10. You can dynamically decrease the limit to be less than the compile time limit, but you cannot increase it beyond the maximum set when you compiled the library. https://sqlite.org/limits.html 11.

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-15 Thread Keith Medcalf
On Wednesday, 15 January, 2020 02:06, Jean-Baptiste Gardette wrote: > Just to be sure, is it unsafe to write a non agregate SELECT with GROUP > BY and HAVING clauses (without sub-SELECT) for the sole prupose > explained before (even if the approache is discutable) ? Presently, yes it is. >I

Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

2020-01-14 Thread Keith Medcalf
On Tuesday, 14 January, 2020 16:04, Simon Slavin wrote: >On 14 Jan 2020, at 10:56pm, Alexandre Doubov >wrote: >> 1) Does the act of bumping this limit up have an effect on memory at >all (assuming that no more than 999 arguments are passed into >statements)? >Section 9:

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Keith Medcalf
On Tuesday, 14 January, 2020 09:23, Simon Slavin wrote: >Would it be possible to phrase your SELECT as a SELECT with a sub-SELECT >? Have the sub-SELECT figure out which rows you want in which order, >then use a SELECT to apply your UDF to them ? It is guaranteed that the >sub-SELECT is

Re: [sqlite] Capturing the changes in columns in a table

2020-01-14 Thread Keith Medcalf
On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera wrote: >That is an idea I have not thought about, Neal. Thanks. The boss comes >up with lots of requests, and these have to be done yesterday. I will >have to look into triggers. Have not used them yet. :-( Here is some sample triggers

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Keith Medcalf
On Tuesday, 14 January, 2020 06:58, Jean-Baptiste Gardette wrote: >Consider the following exemple : >CREATE TABLE t1 ( >a TEXT PRIMARY KEY, >b INTEGER); >SELECT * >FROM t1 >GROUP BY a >HAVING b > 1; >Will the GROUP BY clause be supressed and HAVING clause be rewritten in >WHERE clause by

Re: [sqlite] Capturing the changes in columns in a table

2020-01-14 Thread Keith Medcalf
>From: sqlite-users On >Behalf Of Jose Isaias Cabrera >Sent: Tuesday, 14 January, 2020 06:19 >To: SQLite mailing list >Subject: Re: [sqlite] Capturing the changes in columns in a table > > > >Keith Medcalf, on Monday, January 13, 2020 08:03 PM, wrote... >> >&

Re: [sqlite] Capturing the changes in columns in a table

2020-01-13 Thread Keith Medcalf
ic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Monday, 13 January, 2020 17:04 >To: SQLite mailing list >Subject: Re: [sqlite] Capturing the changes in columns in a table > > >Note this only requires that "idate"

Re: [sqlite] Capturing the changes in columns in a table

2020-01-13 Thread Keith Medcalf
irway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Monday, 13 January, 2020 16:51 >To: SQLite mailing list >Subject: Re: [sqlite] Capturing the changes in columns in a table > > >cr

Re: [sqlite] Capturing the changes in columns in a table

2020-01-13 Thread Keith Medcalf
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4,

Re: [sqlite] Bug fixes only branch.

2020-01-13 Thread Keith Medcalf
On Monday, 13 January, 2020 15:00, Donald Griggs wrote: >On Mon, Jan 13, 2020 at 11:34 AM Syed Ahmad >wrote: >> We are at 3.14.2 Date : 2016-09-12 >> how can i take latest stable branch which include only bug fixes . no >> new features. >> Is there any way? > I may well not be

Re: [sqlite] Unexplained table bloat

2020-01-13 Thread Keith Medcalf
On Monday, 13 January, 2020 06:36, Dominique Devienne wrote: > Please remind me, is the encoding a "client-side" setting, or also a > "server-side" (i.e. stored) setting? "pragma encoding " sets the internal storage format for text encoding in a database (on the "inside SQLite" side of the

Re: [sqlite] Unexplained table bloat

2020-01-13 Thread Keith Medcalf
If the register object contains "text" and you cast it to a blob (remove the text affinity) you are left with just the bag-o-bytes, and length() will return the size of the bag encoded in the register. If the data in the register is other than type "text" then it must be converted to text

Re: [sqlite] Unexplained table bloat

2020-01-13 Thread Keith Medcalf
On Monday, 13 January, 2020 02:27, Dominique Devienne wrote: >On Fri, Jan 10, 2020 at 7:03 PM Richard Hipp wrote: >> On 1/10/20, Dominique Devienne wrote: >>> There's no way at all, to know the length of a text column with >>> embedded NULLs? >> You can find the true length of a string in

Re: [sqlite] how to disable dot commands?

2020-01-12 Thread Keith Medcalf
On Sunday, 12 January, 2020 18:44, Xingwei Lin wrote: >Is there any way can we disable the dot commands feature in sqlite? SQLite does not process dot commands, they are commands to the shell.c SQLite Application program. The current shell.c application currently does not have a way to omit

Re: [sqlite] Unexplained table bloat

2020-01-12 Thread Keith Medcalf
On Sunday, 12 January, 2020 15:31, Simon Slavin wrote: >You're generally right. SQLite always uses affinities (more or less >'weak typing') rather than strong typing. I don't know of any other SQL >implementations which allow this without explicit declaration, and most >don't allow it at all.

Re: [sqlite] Unexplained table bloat

2020-01-12 Thread Keith Medcalf
On Sunday, 12 January, 2020 15:29, Richard Damon wrote: >On 1/12/20 5:25 PM, Tom Browder wrote: >> On Sun, Jan 12, 2020 at 14:05 Keith Medcalf wrote: >>> On Sunday, 12 January, 2020 09:03, Tom Browder >>> wrote: >>>> Am I missing something? I

Re: [sqlite] Unexplained table bloat

2020-01-12 Thread Keith Medcalf
On Sunday, 12 January, 2020 09:03, Tom Browder wrote: >Am I missing something? I thought every column has to have a type? Close, but no banana. Every value has a type. A column may contain multiple values (as in one per row). Therefore each of those values has a type, which may be

Re: [sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE

2020-01-10 Thread Keith Medcalf
to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Friday, 10 January, 2020 18:07 >To: SQLite mailing list >Subject: Re: [sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE > &g

Re: [sqlite] JSON_GROUP_ARRAY unexpected misuse error in UPDATE

2020-01-10 Thread Keith Medcalf
On Friday, 10 January, 2020 14:35, Jason Dora wrote: >I have a workflow where I would like to push an item onto a JSON array, >while ensuring the items on the array are unique. And I'm able to write a >working statement in a SELECT, but the same logic fails in a UPDATE. You need to define what

[sqlite] TRUSTED_SCHEMA and .param init in CLI

2020-01-10 Thread Keith Medcalf
The .param commands seem to have difficulty with the SQLITE_DEFAULT_DEFENSIVE. If you define it as in #define SQLITE_DEFAULT_DEFENSIVE (to either 1 or 0) then the .param init does not create the temp.sqlite_parameters table. pragma trusted_schema=1; then allows the .param init to work, but

Re: [sqlite] Unexplained table bloat

2020-01-10 Thread Keith Medcalf
On Friday, 10 January, 2020 11:44, Tim Streater wrote: >On 10 Jan 2020, at 18:03, Richard Hipp wrote: >> On 1/10/20, Dominique Devienne wrote: >>> There's no way at all, to know the length of a text column with >>> embedded NULLs? >> You can find the true length of a string in bytes from

Re: [sqlite] Unexplained table bloat

2020-01-10 Thread Keith Medcalf
On Friday, 10 January, 2020 10:50, Dominique Devienne : >On Fri, Jan 10, 2020 at 4:30 PM Richard Hipp wrote: >> length() on a BLOB should show the number of bytes in the BLOB. >> length() on a string should show the number of *characters* (not >> bytes) in the string up through but not

[sqlite] Bad Declaration in fossildelta.c

2020-01-09 Thread Keith Medcalf
static const enc = SQLITE_UTF8|SQLITE_INNOCUOUS; should that be static const int enc = SQLITE_UTF8|SQLITE_INNOCUOUS; gcc (MinGW) 8.1 complains but assumes that was what was meant... -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated

Re: [sqlite] NOTNULL

2020-01-08 Thread Keith Medcalf
On Wednesday, 8 January, 2020 04:16, R Smith wrote: >I find the keyword NOTNULL listed among known SQLite keywords - >no. 88 on this page: https://sqlite.org/lang_keywords.html >But cannot find a single mention of it or place to use it in SQLite, nor >get any hit on the sqlite.org search

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert (Docs Update?)

2020-01-07 Thread Keith Medcalf
On Tuesday, 7 January, 2020 17:05, Simon Slavin wrote: >On 8 Jan 2020, at 12:00am, Michael Kappert wrote: >> REPLACE INTO >REPLACE INTO is an alias for INSERT OR REPLACE. So you should assume >that the command will do either an INSERT or a REPLACE. >See the notes about REPLACE on this

Re: [sqlite] Update SQLITE

2020-01-05 Thread Keith Medcalf
On Sunday, 5 January, 2020 16:39, gideo...@lutzvillevineyards.com wrote: >I have the following SQLITE query : > >SELECT BlokkeklaarAliasnaam, BlokkeklaarKultivar, sum(BlokkeklaarSkatting) >FROM Blokkeklaar >GROUP BY BlokkeklaarAliasnaam, BlokkeklaarKultivar; >

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

2020-01-05 Thread Keith Medcalf
On Sunday, 5 January, 2020 04:42, Richard Hipp wrote: >On 1/5/20, Keith Medcalf wrote: >> Hrm. Inconsistent/incorrect results. Consider: >> create table a(id integer primary key, a); >> insert into a values (1,1), (2,1), (3,1); >> create table b(id integer primar

[sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

2020-01-04 Thread Keith Medcalf
bles This is with the current development release (and as far as I can tell, all prior versions). -- 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 M

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result

2020-01-04 Thread Keith Medcalf
-- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Saturday, 4 January, 2020 18:31, Amer Neely wrote: >I'm fairly new to SQLite, but have been using MySQL / mariadb in a local >and web-based environment for several

[sqlite] Performance

2020-01-04 Thread Keith Medcalf
So here is another (this time real world) example using program I wrote which runs ~11 million initial connection packets against ~1800 firewall rules. It is written in Python and is inherently single-threaded. The only multithreading is SQLite3's internal threaded sorting. SINGLETHREAD

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

2020-01-04 Thread Keith Medcalf
Well, actually, no. It was a single process that spins up 64 threads each of which accesses its own per-thread in-memory database using an in-that-thread-only-in-thread-database-connection-in-that-thread. Making some simple modifications (changing the number of threads to 6 and the

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

2020-01-03 Thread Keith Medcalf
On Friday, 3 January, 2020 11:32, sky5w...@gmail.com wrote: > Is there a query function for these and other config settings? > I see no sqlite3_config_get() in sqlite3.h. No. There are config options to get specific config data where that might be useful. Otherwise, you simply set the

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

2020-01-03 Thread Keith Medcalf
On Friday, 3 January, 2020 09:30, sky5w...@gmail.com wrote: >I get SQLITE_MISUSE when attempting >sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0); >immediately after opening a db connection? >My connection has THREADSAFE = 1. That is correct. You must configure the library before it is initialized,

Re: [sqlite] Question about passwords in System.Data.Sqlite

2020-01-03 Thread Keith Medcalf
On Thursday, 2 January, 2020 15:48, Mike King wrote: >I'm porting some code from .Net 4.8 to .Net Core 3.1 using the latest >System.Data.Sqlite. How do I change / set a database password if my >password is a byte array? It looks like I can use Pragma Key= if my >password is text but I use hex

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

2020-01-02 Thread Keith Medcalf
Indeed turning off memstatus leads to a 500% (from ~3s to ~0.5s) performance increase. Changing the threading mode or the indirection level of the mutexes calls seems to have no significant effect. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about

Re: [sqlite] 18 minutes 41 seconds

2019-12-30 Thread Keith Medcalf
On Monday, 30 December, 2019 19:29, Michael Falconer wrote: > As we approach the end of yet another year ( and indeed decade ). Technically, every year is the end of a decade, if one means the immediately preceding ten years. However, if you mean the end of the second decade of the 21st

Re: [sqlite] last_value() without nulls?

2019-12-29 Thread Keith Medcalf
See last sentence of paragraph 3 of https://www.sqlite.org/windowfunctions.html#built_in_window_functions Perhaps the following? with s1(t, v) as (values (1, 's1-a'), (3, 's1-c')), -- series 1 s2(t, v) as (values (1, 's2-a'), (4, 's2-d')),

Re: [sqlite] AVG Function HowTo

2019-12-28 Thread Keith Medcalf
m: sqlite-users On >Behalf Of Keith Medcalf >Sent: Saturday, 28 December, 2019 23:53 >To: SQLite mailing list >Subject: Re: [sqlite] AVG Function HowTo > > >The description makes no sense. > >So lets say on the 1st of a Month you have an "Earnings" of $4,000.

Re: [sqlite] AVG Function HowTo

2019-12-28 Thread Keith Medcalf
The description makes no sense. So lets say on the 1st of a Month you have an "Earnings" of $4,000. On the 2nd of the month you have an "expense" of $2,500. On the 5th of the month you have an "expense" of $2.00. On the 15th of the month you have an "Earnings" of $4000. So the "average"

Re: [sqlite] When not using threads: should I switch to single-thread mode

2019-12-27 Thread Keith Medcalf
On Friday, 27 December, 2019 16:37, Simon Slavin wrote: >On 27 Dec 2019, at 9:57pm, Keith Medcalf wrote: >> Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. >> When in one of the multithreaded modes, that query utilizes an average of >>

Re: [sqlite] When not using threads: should I switch to single-thread mode

2019-12-27 Thread Keith Medcalf
On Friday, 27 December, 2019 14:19, Simon Slavin wrote: >On 27 Dec 2019, at 7:46pm, Keith Medcalf wrote: >> Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 >minutes. (Perhaps it disables some of the internal multithreaded sorters >-- I don't kn

Re: [sqlite] Bug Report

2019-12-27 Thread Keith Medcalf
On Friday, 27 December, 2019 12:50, Igor Korot wrote: >On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do wrote: >> We met an accidental crash in sqlite with the following sample: >> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) >> SELECT col2 FROM table1 ORDER BY 1 ;

Re: [sqlite] When not using threads: should I switch to single-thread mode

2019-12-27 Thread Keith Medcalf
On Friday, 27 December, 2019 10:29, Cecil Westerhof wrote: >Op vr 27 dec. 2019 om 17:01 schreef Simon Slavin : >> On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: >>> My applications only use one thread (for the db stuff). Would it be a >>> good idea to switch to single-thread mode, or

Re: [sqlite] No such column

2019-12-22 Thread Keith Medcalf
On Sunday, 22 December, 2019 23:20, Aydin Ozgur Yagmur wrote: >I have experienced a weird problem. I have been using sqlite database in >linux by mounting. >Nearly all times it works well. But when testing with customer, I >encounter >"No such column" error. After restarting system, it works

Re: [sqlite] RBU fails as of checkin f84a1539 - fixed

2019-12-22 Thread Keith Medcalf
I get: RBU error: near ")": syntax error ERROR 1, expected 101 Done - Press ENTER to exit. with the current trunk ... -- 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

Re: [sqlite] problem with URI mode=ro

2019-12-21 Thread Keith Medcalf
On Saturday, 21 December, 2019 03:27, Thomas Kurz wrote: >I have a problem when opening a read-only database, which is a WAL-mode >database. >When trying to open it in read-only mode, i.e. using >file:test.sqlite?mode=ro, SHM and WAL file are created. That's >unpleasant, but the actual

[sqlite] Makefile.msc omits rbu exports

2019-12-20 Thread Keith Medcalf
When the sqlite3.def is made, the rbu exports are not included. That is, this # <> sqlite3.def:libsqlite3.lib echo EXPORTS > sqlite3.def dumpbin /all libsqlite3.lib \ | $(TCLSH_CMD) $(TOP)\tool\replace.tcl include "^\s+1

Re: [sqlite] sqlite3_interrupt and explicit transactions

2019-12-16 Thread Keith Medcalf
On Monday, 16 December, 2019 12:17, Jesse Rittner wrote: > I have a few questions about how sqlite3_interrupt interacts with > explicit transaction operations. The docs say that "If the interrupted > SQL operation is an INSERT, UPDATE, or DELETE that is inside an > explicit transaction, then

Re: [sqlite] [EXTERNAL] Difference between hex notation and string notation

2019-12-16 Thread Keith Medcalf
You will also note that the bytes in the blob must be the bytes in the underlying database text encoding in order for a cast to text to produce expected output (assuming that expected means valid text): sqlite> pragma encoding; UTF-8 sqlite> select x'414243'; ABC sqlite> pragma

Re: [sqlite] How to determine the column type?

2019-12-13 Thread Keith Medcalf
On Friday, 13 December, 2019 18:35, Richard Damon wrote: >One big thing to watch out is that columns of NUMERIC type can easily >return values of either INTEGER or REAL type. Your single type >expectation is easily broken here. I also don't know if >9223372036854775807 (the biggest integer

Re: [sqlite] How to determine the column type?

2019-12-13 Thread Keith Medcalf
On Friday, 13 December, 2019 15:49, František Kučera wrote: >I know that SQLite uses dynamic types, so it is not easy… But what is the >best way to determine the column type of a result set? Result sets do not have "column types". Each result value (the intersection of row and column) has a

Re: [sqlite] Difference between hex notation and string notation

2019-12-13 Thread Keith Medcalf
On Friday, 13 December, 2019 02:16, Sascha Ziemann wrote: >I have a problem to find rows in a database when I write in hex notation: >CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL); >INSERT INTO LOG VALUES

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

2019-12-09 Thread Keith Medcalf
On Monday, 9 December, 2019 20:02, Richard Damon wrote: >On 12/9/19 4:25 PM, Keith Medcalf wrote: >>> You could still have fast forking without overcommitting, you’d just >>> pay the cost in unreachable RAM. >>> If I have 4 GB of RAM in the system, and the ke

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

2019-12-09 Thread Keith Medcalf
>You could still have fast forking without overcommitting, you’d just pay >the cost in unreachable RAM. > >If I have 4 GB of RAM in the system, and the kernel takes 1 GB of that, I >start a 2.5 GB user space process, and my process forks itself with the >intent of starting an 0.1 GB process, that

Re: [sqlite] Result set column names

2019-12-08 Thread Keith Medcalf
On Saturday, 7 December, 2019 16:05, Tim Streater wrote: >At various times in various threads on this list it has been stated that >the column name in a result set is not guaranteed unless one uses AS. >IOW, one should say > > select abc as abc from mytable where i=23; > >rather than just: > >

Re: [sqlite] .expert disables loaded extensions

2019-12-06 Thread Keith Medcalf
On Friday, 6 December, 2019 07:49. Jose Isaias Cabrera wrote: >please observe the following: > 9:45:49.39>sqlite3 >SQLite version 3.30.0 2019-10-04 15:03:17 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database.

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

2019-12-06 Thread Keith Medcalf
On Friday, 6 December, 2019 08:17, radovan5 wrote: >ANALYZE dosn't help because data is loaded from RDBMS >for processing every time. From this I take is that you are loading the data from somewhere else and then running this one query and you do not want to run ANALYZE. If that is the case

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

2019-12-06 Thread Keith Medcalf
<> 0" > >For me analyze is no improvement because data is loaded from RDBMS >and would have to run always after load. > >Regards Radovan > >On 06.12.2019 14:20, Richard Hipp wrote: >> On 12/6/19, Keith Medcalf wrote: >>> Perhaps the o

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

2019-12-06 Thread Keith Medcalf
The join that you are using is not an outer join because you have constrained R.ID_ARHDAJ (which cannot be null in the table) to not be null, and the only way it can be null is if the left join is a "miss", meaning that it is really an inner join, not an outer join). The optimizer spots this

Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?

2019-12-05 Thread Keith Medcalf
That does not make any sense at all. How are you deleting old rows? The easiest way is to use the table rowid ... delete from data where rowid < (select max(rowid) - 20 from data); insert into data (... data but not rowid ...) values (...); This will explode after you have inserted

Re: [sqlite] Enable Or Disable Extension Loading

2019-12-05 Thread Keith Medcalf
On Thursday, 5 December, 2019 11:39, Jose Isaias Cabrera asked: >Just to be sure... > >The function, > >int sqlite3_enable_load_extension(sqlite3 *db, int onoff); > >enables or disables a database to allow or disallow the loading of >extensions[1]. Once it's set, will it stay on? Or does one

Re: [sqlite] SQL to SQLite

2019-12-03 Thread Keith Medcalf
On Tuesday, 3 December, 2019 05:39, gideo...@lutzvillevineyards.com wrote: >My query is : >UPDATE wbridge_history >SET yearclass = >( >SELECT D.wynklas >FROM >( >SELECT LidNo, PlaasNo, BlokNo, oesjaar, wynklas, >ROW_NUMBER() OVER (PARTITION BY LidNo, PlaasNo, BlokNo, oesjaar ORDER BY >COUNT(*)

Re: [sqlite] Triggers for generated columns?

2019-12-02 Thread Keith Medcalf
On Monday, 2 December, 2019 17:10, Stephen F. Booth wrote: >I have been trying out generated column support in the 3.31.0 prerelease. >Thank you for adding such a useful feature! >When I create a trigger for an update of a generated column the trigger >is successfully created but it never

Re: [sqlite] built-in printf() not supporting \n or \t ?

2019-11-28 Thread Keith Medcalf
On Thursday, 28 November, 2019 09:21, Richard Damon wrote: >\n and \t are not 'printf' features, but C string features, that \ is an >escape introducer for compiling a string, and if followed by a letter >like n or t it builds a string with the special value represented by >that function. The

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

2019-11-26 Thread Keith Medcalf
saias Cabrera >Sent: Tuesday, 26 November, 2019 13:45 >To: SQLite mailing list >Subject: Re: [sqlite] Passing a path to sqlite3.exe to load a dll > >> >> > >Keith Medcalf, on Tuesday, November 26, 2019 03:38 PM, wrote... >> >> >> What is the c

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

2019-11-26 Thread Keith Medcalf
;From: sqlite-users On >Behalf Of Jose Isaias Cabrera >Sent: Tuesday, 26 November, 2019 13:28 >To: SQLite mailing list >Subject: Re: [sqlite] Passing a path to sqlite3.exe to load a dll > > >Keith Medcalf, on Tuesday, November 26, 2019 02:57 PM, wrote... >> >> >>

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

2019-11-26 Thread Keith Medcalf
Escape the reverse solstice with a duplicate reverse solstice (\ -> \\) or replace them with normal solstice (\ -> /) since Windows recognizes either as the path separator. The CLI, like most things, parses escape sequences on input. Same applies to the C API function. You specify the full

[sqlite] Checkin 5d9a369301 Cannot Link - sqlite3Fts3Corrupt undefined without SQLITE_DEBUG

2019-11-25 Thread Keith Medcalf
fts3_write.c line 1241 calls sqlite3Fts3Corrupt but function is only defined if SQLITE_DEBUG is defined when fts3.c is compiled. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

Re: [sqlite] wrong timestamp using strftime('%s')

2019-11-25 Thread Keith Medcalf
also tried without milliseconds - same result. > > >-Ursprüngliche Nachricht- >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >Im Auftrag von Keith Medcalf >Gesendet: Montag, 25. November 2019 22:41 >An: SQLite mailing list >Betreff: Re: [sqlit

Re: [sqlite] wrong timestamp using strftime('%s')

2019-11-25 Thread Keith Medcalf
-- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Sunday, 24 November, 2019 13:21, Dominik Ohnezeit wrote: >I am trying to convert a date to timestamp, but after the conversion with >strftime('%s') the integer result

Re: [sqlite] Mixed ASC and DESC in single column

2019-11-21 Thread Keith Medcalf
with a (identifier, prefix, suffix) as ( select identifier, substr(info, 1, instr(info, '/') - 1), substr(info, instr(info, '/') + 1) from data where instr(info, '/') > 1 ), b (identifier, bar, foo, baz) as ( select identifier, max(case prefix when 'bar' then suffix end),

Re: [sqlite] Optimising query with aggregate in subselect.

2019-11-20 Thread Keith Medcalf
Yes. See under item #3 in the Side note on https://sqlite.org/lang_select.html Special processing occurs when the aggregate function is either min() or max(). Example: SELECT a, b, max(c) FROM tab1 GROUP BY a; When the min() or max() aggregate functions are used in an aggregate query,

Re: [sqlite] Optimising query with aggregate in subselect.

2019-11-20 Thread Keith Medcalf
Did you try retrieving the data "directly" or do you need the subselect in order to maintain compatibility with other SQL dialects that are no longer able to retrieve data from the row on which the max was found? CREATE TABLE entrys ( logid INTEGER NOT NULL, entrynumber INTEGER

Re: [sqlite] What is the C language standard to which sqlite conforms ?

2019-11-19 Thread Keith Medcalf
If using a GCC compiler, the dialect is -std=gnuXX where XX is the latest year supported by the compiler. In order these are: gnu89 gnu90, gnu9x, gnu99, gnu11, gnu1x, gnu17, gnu18, gnu19, gnu20, gnu2x This also happens to be the default if you do not specify -std The Source Code does NOT

Re: [sqlite] Question about: Adding a record to a table with select failure

2019-11-18 Thread Keith Medcalf
On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera wrote: >Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote... >> >> This relies on two implementation details particular to SQLite3 which >> hold at present, but may of course change at any time: >&g

Re: [sqlite] Question about: Adding a record to a table with select failure

2019-11-18 Thread Keith Medcalf
No. This is an aggregate query that relies on the fact that SQLite3 will choose the values from (one of) the row(s) containing the aggregate to satisfy select scalars that are not aggregates. Consider the query: select a, max(idate), b from t where a == 'p006'; This will return the maximum

Re: [sqlite] BUG and WORKAROUND sqlite3 shell .parameter command evaluating arguments when it shouldn't.

2019-11-15 Thread Keith Medcalf
I think this is a bug. However, looking at the code the way to achieve that is to surround the string in double quotes which will cause exactly what appears between the double-quotes to be stored. I think it is because of the way the parsing and mprintf function works ... sqlite> .param

Re: [sqlite] How to update plural rows from one table to another.

2019-11-15 Thread Keith Medcalf
On Friday, 15 November, 2019 15:22, Gan Uesli Starling wrote: >In the following update query, I had expected for the integer values >"rowid" from the table "info" to project copies of themselves singly and >separately into the integer cells "info_id" of table "qso", row-by-row, >where the

Re: [sqlite] Adding a record to a table with one value change

2019-11-15 Thread Keith Medcalf
to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Jose Isaias Cabrera >Sent: Friday, 15 November, 2019 06:20 >To: SQLite mailing list >Subject: Re: [sqlite] Adding a record to a table with one value change

Re: [sqlite] Adding a record to a table with one value change

2019-11-14 Thread Keith Medcalf
On Thursday, 14 November, 2019 15:27, Jake Thaw wrote: >Why not like this? >insert into t (a, b, c, d, e, idate) >SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY >idate desc limit 1; Or, if using bound paramaters (and you should be): insert into t (a, b, c, d, e, idate)

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

2019-11-14 Thread Keith Medcalf
the cached result, and ?99 and vdata are the parameters if calculation is required. -- 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:

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

2019-11-14 Thread Keith Medcalf
>On Thursday, 14 November, 2019 03:52, Dominique Devienne >wrote: >>On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: >>> Thanks to all the friendly people who commented on my question. Much >>> appreciated :-) >>> I was able to solve this with a small trick: >>> I created a small

Re: [sqlite] Things you shouldn't assume when you store names

2019-11-14 Thread Keith Medcalf
On Thursday, 14 November, 2019 09:35, Eric wrote: >On Thu, 14 Nov 2019 00:24:09 + SQLite mailing list >sqlite-users@mailinglists.sqlite.org said >> A growing number of organisations now ask me for my DOB or my postcode, >> rather than my name, when looking me up. I think you just

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

2019-11-14 Thread Keith Medcalf
-- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Thursday, 14 November, 2019 03:52, Dominique Devienne wrote: >On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: >> Thanks to all the friendly people who

Re: [sqlite] Things you shouldn't assume when you store names

2019-11-13 Thread Keith Medcalf
On Wednesday, 13 November, 2019 17:18, Warren Young wrote: >On Nov 13, 2019, at 11:31 AM, Simon Slavin wrote: >> Don't substring searches help you more than sorted lists ? >There’s a relevant question for this list: how do we do this efficiently? >The naive solution involves a table scan.

Re: [sqlite] Things you shouldn't assume when you store names

2019-11-13 Thread Keith Medcalf
>"Please fix your last name: No spaces allowed!">By the way, Keith, 'Medcalf' >is possibly the oldest known English surname >(13th Century) and indicates that one of your ancestors raised or >slaughtered cows. Aye, but over here the apparent only version that anyone kn

Re: [sqlite] Things you shouldn't assume when you store names

2019-11-13 Thread Keith Medcalf
On Wednesday, 13 November, 2019 14:56, Jose Isaias Cabrera wrote: >Peter da Silva, on Wednesday, November 13, 2019 04:37 PM, wrote... >> My last name has a space in it. It's been less than a month since the >> last time it was rejected by a form. One of my oldest online friends has >> only

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Keith Medcalf
On Wednesday, 13 November, 2019 13:26, Bart Smissaert wrote: >Thanks, the second one does the job as I need 1 or 0 and no nulls. >It saves me running 2 queries as before had: >UPDATE QR3PARAMS SET ED = CASE WHEN ED = 1 THEN 1 ELSE >(SELECT 1 FROM CURRENT_MED WHERE >(TERM_TEXT GLOB

Re: [sqlite] Why do these 2 updates give different results?

2019-11-13 Thread Keith Medcalf
Both queries update all rows in QR3PARAMS since there is no WHERE clause to limit which rows are updated, so when you say "does less updates" what do you mean, since it is manifestly impossible for one to do less updates than the other -- both update every row or the table. Secondly, in the

[sqlite] Checkin df51ae19c1 pager.c line 4866 nUri undefined

2019-11-12 Thread Keith Medcalf
nUri is only defined if SQLITE_DEBUG is defined. Should this line be #ifndef SQLITE_DEBUG? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list

Re: [sqlite] Why does Python turn UTF8 data in SQLite into Latin1?

2019-11-11 Thread Keith Medcalf
What version of Python and what Operating System, and what is the locale applicable to the process instance? -- 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

Re: [sqlite] Why does Python turn UTF8 data in SQLite into Latin1?

2019-11-11 Thread Keith Medcalf
Yes you did. What version of Python are you using? -- 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 Winfried >Sent: Monday, 11 November, 2019 17:42 >To:

Re: [sqlite] Things you shouldn't assume when you store names

2019-11-11 Thread Keith Medcalf
On Monday, 11 November, 2019 14:34, Richard Damon wrote: >Unicode has decreed that the highest code-point that can be called a >code-point is 0x10 because to go higher breaks UTF-16, so there >isn't as much room as you might think. >This give us 1,114,112 possible code points. >There are

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

2019-11-08 Thread Keith Medcalf
>But this makes me think of the upcoming virtual column feature. If you >define a virtual table column whose value is equal to >EXPENSIVE_FUNCTION(), do multiple references to that column in a query >cause multiple calls to the function, or is it computed only once per >row? In the present case

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

2019-11-08 Thread Keith Medcalf
where some_table.rowid in (select rowid from temp.u) drop table temp.u; -- 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: Keith Medcalf >Sent: Friday, 8 November, 2019 13:21 >

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