Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[nomad]

On Fri Mar 13, 2020 at 11:22:46AM -0400, Richard Hipp wrote:
> On 3/13/20, Huỳnh Trần Khanh  wrote:
> > [On a mailing nlist] I can
> > filter the posts, sort them, search through them, archive them,
> > forward them to a friend, 
> 
> You can do all of that with the SQLite Forum.  Remember, all content
> is still delivered directly to your in-box, just like with a mailing
> list, so anything you can do with content received from a mailing list
> can also be done with content from the forum.  But there are many

By chance or by design most emails to this list, like yours above, came
wrapped at a length suitable for text-based readers. Yet every message
I've received from the forum is a shocking cut-words-in-half affair in
my terminal.  There have been several other topics raised already on
the forum about the email format. Your statements above and below imply
that the only thing list users will miss is composing, but that is a
clearly premature position to take.

> things that the forum provides that a mailing list does now.  For
> example, if you want an archive of the forum activity, you can clone
> the entire history with one command:
> ..
> Then periodically "sync" to keep your private archive up-to-date.  Now
> you have all historical content, neatly packaged in an SQL database.
> You can extract and search and manage the content in this archive in
> any way you want.

Not something I've ever needed or would want to dedicate disk space
towards. Web-indexable contents enables search engines do a better job
of categorizing and linking than I could do, and don't want to spend
the time doing. What is the typical use case here?

> use the web interface in order to post a message.  In my experience,
> this forces people to take a little extra time to think about what
> they are saying, and to format and arrange their thoughts for clarity,
> and hence results in a better experience for the readers.

Posting too early is not something I can say I've noticed many people
doing.

> There are other important features that the forum provides that
> mailinglists typically do not:
> 
> 1.  You can format your postings using Markdown

Useful to those who prefer to read messages in a browser, I guess. Not
much of an advantage to those who prefer to read text. My client
doesn't seem to mangle EXPLAIN output like yours does.

> 2.  You can add hyperlinks to your postings that are consistently
> displayed and are not dependent on the idiosyncrasies of various
> email clients.

I don't quite understand this one.

> 3.  You can edit prior posts to fix typos or mistakes.

Fair enough.

> 4.  Your email address is never displayed, even to subscribers.

That is a mailing list configuration item, not a core difference
between a list and a forum.

> 5.  It is much easier to contribute anonymously to a web-based forum
> than it is to contribute on a mailing list.  There is no verification
> process to go through.  You just type in what you want to say and
> press "Submit".

Fair enough, if having an open mailing list comes with too many other
costs.

> 6.  Moderators have much better control over spam and other malicious
> content.

Ok.

> The first point (use of Markdown) is the killer feature for me.  There
> was a recent thread on this mailing list that involved people posting
> EXPLAIN output.  That text gets hopelessly jumbled on most email
> readers.  If those messages had been formatted with Markdown, they
> would have been much easier to read and understand.
> 
> I've been using both this mailing list and the Forum on Fossil
> regularly for two years now.  The forum is so much nicer that I have
> come to dread having to work with the legacy mailing list, at least
> for complex subjects.  It is time for a switch to better technology.

It is your project and your time spent managing the list (for which I
and many others are grateful) so your choice to make. I do however
think your approach was a little too fast and somewhat dismissive of
the concerns of subscribers and the cost to the community.

I would estimate that more around half of the value of this mailing
list come from discussion of topics outside of SQLite, and from several
key individuals willing to share their advanced knowledge and ideas.
That we will likely lose some of them, and/or the spontaneous
discussions that the mix of beginner and advanced, is a shame, and
could have been avoided.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[nomad]

On Thu Mar 12, 2020 at 04:17:59PM -0400, Richard Hipp wrote:
> I have set up an on-line forum as a replacement for this mailing list:
> 
> https://sqlite.org/forum
> https://www.sqlite.org/forum/forumpost/a6a27d79ac

I know this topic has already been discussed deeply on this list, but I
would like to add my resistance "vote."

For experienced vim/emacs/$EDITOR users, Email composition via web
browser is one of the most debilitating experiences that can be
imposed.  I have kept well away from Gmail-like platforms for this
reason. In the other direction I also fear the loss of reading quality
in my email client as Markdown-isms or html content take hold.

On a purely technical note, although I haven't looked at the web
interface in detail, I am wondering what (apparently invisible)
anti-spam features are present. It seems rather easy to Preview and
Submit as anonymous.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inconsistency of CREATE/DROP TABLE with attached DBs

[nomad]

I ran into an inconsistency? between CREATE and DROP TABLE today:

# Pipe this example through sed -e 's/^ *//' before running through
# a shell
#
# 1. Set up table a.t1
rm -f a.sqlite b.sqlite c.sqlite

cat 

Re: [sqlite] Shell Feature Request: spaces before dot commands

[nomad]

On Wed Mar 04, 2020 at 12:44:09PM +, Simon Slavin wrote:
> On 4 Mar 2020, at 12:19pm, no...@null.net wrote:
> 
> >I like to sometimes indent a block of SQL and change
> > settings or run dot commands within a transaction, e.g.:
> > 
> >BEGIN
> >.mode csv
> >.import ...
> >COMMIT;

> Dot commands are not SQL commands, they are instructions to the shell
> application.  Transactions, CASE structures, WITH structures, and
> other such things do not affect them.

Well commands like .import create a table. Since SQLite supports
transactional DDL there is certainly a relevance between BEGIN/COMMIT
and a call to .import, no?

But that is all kind of beside my point: I do not see a technical
necessity for dot commands to have no leading space, and my programming
experience would be better of without that requirement.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Shell Feature Request: spaces before dot commands

[nomad]

[SQLite version 3.22.0 2017-11-27 17:56:14]

The SQLite shell only recognizes .dot commands without leading spaces.
For clarity I like to sometimes indent a block of SQL and change
settings or run dot commands within a transaction, e.g.:

BEGIN
.mode csv
.import ...
COMMIT;

Unfortunately at present the above are parsed as SQL statements and
don't fail until the next ";".

I would like to request a change to the parsing behaviour to recognize 
".command" the same as ".command".

Thanks,
Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

[nomad]

On Tue Mar 03, 2020 at 05:12:17PM +0800, suanzi wrote:
> Thank you,you let me know what happened.
> 
> About your answer,I try it,but it can't work,maybe because can't have two
> zlib.
> 
> apt-get could not find zlib:i386.

Did you specifically try "zlib1g"? I don't think the "zlib" package exists.

mark@b:~$ uname -a
Linux b 4.19.0-8-amd64 #1 SMP Debian 4.19.98-1 (2020-01-26) x86_64
GNU/Linux

mark@b:~$ dpkg -S libz.so.1
zlib1g:amd64: /lib/x86_64-linux-gnu/libz.so.1
zlib1g:amd64: /lib/x86_64-linux-gnu/libz.so.1.2.11

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT INTO table AS alias" is invalid inside triggers

[nomad]

On Mon Feb 10, 2020 at 01:34:12AM +0100, no...@null.net wrote:
> I suspect I have found a parsing error in SQLite 3.30.1. Given the
> ...
> However if I wrap it inside a trigger:
> 
> CREATE TABLE t2(b INTEGER);
> 
> CREATE TRIGGER t2_ai
> AFTER INSERT ON t2
> FOR EACH ROW BEGIN
> 
> INSERT INTO t1 AS original (a) VALUES(NEW.b)
>   ON CONFLICT DO NOTHING;
> 
> END;
> 
> Then SQLite fails to prepare: near "AS": syntax error [for Statement
> "CREATE TRIGGER"]

I see the same behaviour with 3.31.1. Could I ask the devs if this is
likely to be fixed at some point or will remain as is?

On a related note and perhaps more generally interesting, I find the
UPSERT mechanism quite useful for updating specific multiple rows in a
specific order:

INSERT INTO
table
SELECT
columns
FROM
table
LEFT JOIN
other_table
ON
join_condition
WHERE
where_condition
ORDER BY
order_condition
ON CONFLICT DO UPDATE SET
x,y,z = (values or some other query)

Previously I have used temporary tables and complicated recursive
triggers to achieve what the above appears to do. I would be very
interested in hearing from those that know better if the above is
reliable and functions the way I think it does.  It is certainly much
easier to understand and I assume more efficient. 

Some systems apparently support an UPDATE ... JOIN syntax but I find
the SQLite UPSERT implementation more powerful because of the ORDER BY
possibility.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation error

[nomad]

The page https://sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md
contains raw html:

INSERT INTO t1(b) VALUES(blob-value>);

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[nomad]

On Mon Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote:
> I should get:
> 
> 127/81
> 132/82
> 141/85
> 143/94
> 
> What should be the SQL to group like this?

Here is one way that appears to generate the correct result.

CREATE TABLE pressure(
id INTEGER PRIMARY KEY,
userid INTEGER NOT NULL,
date TEXT NOT NULL,
term text NOT NULL,
reading INTEGER NOT NULL
);

INSERT INTO
pressure(userid,date,term,reading,id)
VALUES
(1308,'15/Mar/2013','Systolic',127,701559),
(1308,'15/Mar/2013','Diastolic',81,701568),
(1308,'27/Jun/2013','Systolic',132,701562),
(1308,'27/Jun/2013','Systolic',141,701563),
(1308,'27/Jun/2013','Systolic',143,701564),
(1308,'27/Jun/2013','Diastolic',82,701571),
(1308,'27/Jun/2013','Diastolic',85,701572),
(1308,'27/Jun/2013','Diastolic',94,701573),
(278975701,'08/Mar/2018','Systolic',136,1583551),
(278975701,'08/Mar/2018','Diastolic',99,1583591),
(278975701,'04/Apr/2018','Systolic',119,1583552),
(278975701,'04/Apr/2018','Systolic',124,1583553),
(278975701,'04/Apr/2018','Systolic',130,1583554),
(278975701,'04/Apr/2018','Diastolic',74,1583592),
(278975701,'04/Apr/2018','Diastolic',75,1583593),
(278975701,'04/Apr/2018','Diastolic',85,1583594);

WITH
systolic AS (
SELECT
row_number() OVER (order by id) AS row,
*
FROM
pressure
WHERE
term='Systolic'
),
diastolic AS (
SELECT
row_number() OVER (order by id) AS row,
*
FROM
pressure
WHERE
term='Diastolic'
)
SELECT
d.userid AS User,
d.date AS Date,
s.reading || '/' || d.reading AS Reading
FROM
systolic s
LEFT JOIN
diastolic d
ON
d.row=s.row
ORDER BY
d.userid,
d.date,
d.id
;

I'm sure others will produce more elegant solutions - I was just using
your question as an exercise to learn about window functions. The query
plan of the above looks a little scary though:

 id   parent  notused  detail
 20   0CO-ROUTINE 1
 92   0CO-ROUTINE 4
 12   9   0SCAN TABLE pressure
 26   2   0SCAN SUBQUERY 4
 72   0   0MATERIALIZE 2
 79   72  0CO-ROUTINE 5
 82   79  0SCAN TABLE pressure
 97   72  0SCAN SUBQUERY 5
 144  0   0SCAN SUBQUERY 1 AS s
 159  0   0SEARCH SUBQUERY 2 AS d USING AUTOMATIC COVERING INDEX
   (row=?)
 180  0   0USE TEMP B-TREE FOR ORDER BY

Presumably if you index the date/userid and filter the CTE tables it
gets a little better.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "INSERT INTO table AS alias" is invalid inside triggers

[nomad]

I suspect I have found a parsing error in SQLite 3.30.1. Given the
following:

CREATE TABLE t1(a INTEGER PRIMARY KEY);

The following statement is accepted by the parser:

INSERT INTO t1 AS original (a) VALUES(1)
  ON CONFLICT DO NOTHING;

However if I wrap it inside a trigger:

CREATE TABLE t2(b INTEGER);

CREATE TRIGGER t2_ai
AFTER INSERT ON t2
FOR EACH ROW BEGIN

INSERT INTO t1 AS original (a) VALUES(NEW.b)
  ON CONFLICT DO NOTHING;

END;

Then SQLite fails to prepare: near "AS": syntax error [for Statement
"CREATE TRIGGER"]

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT documentation question

[nomad]

I should perhaps point out that the issue has been solved - the page
has been adjusted. Thanks devs.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT documentation question

[nomad]

On Fri Feb 07, 2020 at 01:45:53PM +, David Raymond wrote:
> > CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT
> > 1); INSERT INTO vocabulary(word) VALUES('jovial') ON CONFLICT(word)
> > DO UPDATE SET count=count+1;
> > 
> > Shouldn't that actually be written as "vocabulary.count+1"?
> 
> Nope. Unqualified names there refer to the one and only record that's
> getting updated.

Your edit of my email broke some context. I was actually referring to
the paragraph after the example SQL, where "vocabularly.count" was
given as being equivalent to "count+1".

> Similar to how in an blanket update statement you would do:
> update vocabulary set count = count + 1;
> ...and not:
> update vocabulary set vocabulary.count = vocabulary.count + 1;
> 
> I mean, it might still work, but it's not needed, no.

It is needed if you are have a correllated subquery in the UPDATE
statement and want to refer to the original row.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT documentation question

[nomad]

The page https://sqlite.org/lang_UPSERT.html includes the following
text:

Some examples will help illustrate the difference:

CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
  ON CONFLICT(word) DO UPDATE SET count=count+1;

The upsert above inserts the new vocabulary word "jovial" if that
word is not already in the dictionary, or if it is already in the
dictionary, it increments the counter. The "count+1" expression
could also be written as "vocabulary.count". ...

Shouldn't that actually be written as "vocabulary.count+1"?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT/UPDATE generated columns?

[nomad]

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?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] mailinglists.sqlite.org

[nomad]

I observe the following on the mailinglists.sqlite.org web server:

1. http requests are responded to normally instead of redirecting
to https, which leaves users passwords exposed when modifying
subscription options.

2. https requests are served up with a TLS certficate for
sqlite.org, resulting in warnings from browsers.

Perhaps the devs feel like tidying these items up

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] specify CHECK clause to date text field

[nomad]

On Wed Aug 14, 2019 at 09:08:26PM +0800, Adrian Ho wrote:
> On 14/8/19 8:47 PM, no...@null.net wrote:
> >
> > CREATE TABLE table_a(
> > dt TEXT -- NOT NULL if you like
> > CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
> > );
> 
> Sorry, that 'localtime' qualifier is a non-starter; that will throw a
> "non-deterministic function in index expression or CHECK constraint"
> error
> (https://www.sqlite.org/deterministic.html#special_case_processing_for_date_time_functions):

Right you are. I should have checked that properly. Looks like '+0
days' is the right way to go then, still using the IS operator to
handle NULLs.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] specify CHECK clause to date text field

[nomad]

> It seems the date function does not check that the date is valid,
> only the format.

I've run into the same issue. Don't remember if it has been raised on
the list, but I have a vague memory that it fell into the WONTFIX
category :-(

> Consequently, I would appreciate any advice on the preferred way to
> specify a CREATE TABLE .. CHECK clause to guard inserting a
> -mm-dd date into a text field.

The following might be instructive:

.version
.width 10 10 2 15 2 21 2
WITH
x(dt)
AS (
VALUES
(NULL),
('2019-02-00'),
('2019-02-01'),
('2019-02-1'),
('2019-02-29'),
('2019-02-30'),
('2019-02-31'),
('2019-02-32')
)
SELECT
x.dt AS "str",
date(x.dt) AS "date(str)",
x.dt IS date(x.dt) AS "IS",
date(x.dt,'utc') AS "date(str,'utc')",
x.dt IS date(x.dt,'utc') AS "IS",
date(x.dt,'localtime') AS "date(str,'localtime')",
x.dt IS date(x.dt,'localtime') AS "IS"
FROM
x
;

-- Output

SQLite 3.22.0 2017-11-27 17:56:14
465350e55ddaf30cfba7874653301de7238a9dc2bc5e1f800fc95de9360679f6
str date(str)   IS  date(str,'utc')  IS  date(str,'localtime')  IS
--  --  --  ---  --  -  --
NULLNULL1   NULL 1   NULL   1
2019-02-00  NULL0   NULL 0   NULL   0
2019-02-01  2019-02-01  1   2019-01-31   0   2019-02-01 1
2019-02-1   NULL0   NULL 0   NULL   0
2019-02-29  2019-02-29  1   2019-02-28   0   2019-03-01 0
2019-02-30  2019-02-30  1   2019-03-01   0   2019-03-02 0
2019-02-31  2019-02-31  1   2019-03-02   0   2019-03-03 0
2019-02-32  NULL0   NULL 0   NULL   0

I think the above means you can compare the original value with the
localtime equivalent:

CREATE TABLE table_a(
dt TEXT -- NOT NULL if you like
CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
);

Regards,
Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unable to use date fields in sqlite

[nomad]

On Tue Jul 23, 2019 at 04:41:59PM +0200, Andreas Kretzer wrote:
> Am 23.07.2019 um 16:22 schrieb Steve Leonard:
> >
> > 4)I have tried several combinations of creating a new field
> > of type numeric, blob, real, and integer and then tried to update

If you want to work with date *strings* of the form '-mm-dd' you
probably want to use TEXT.

> > 3) I want to select all records where myfdt is between 11/05/2011 and
> > 12/30/2011 have had no success
> > Could someone please post the correct sql statement to do this?
> >
> > queries like this give no results:
> >  select * from mytable
> > where myfdt > '11/06/2011'
> > and myfdt < '04/13/2018'

If you have modified your data to be -mm-dd then your queries
should also be in that format:

SELECT * FROM mytable
WHERE myfdt > '2011-11-06'
AND myfdt < '2018-04-13'

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple way to import GPX file?

[nomad]

On Sun Dec 09, 2018 at 03:16:15PM -0700, Winfried wrote:
> Good call, thank you.
> 
> For others' benefit:
> 
> 1. Copy the file, open the copy in a text editor, use a regex to turn the
> data into tab-separated columns

If you are running some kind of unix-like environment this is something
Perl can be quite useful for:

grep '^(.*)!$1\t$2\t$3!' \
   > waypoints.tsv

> 2. Create a new file, and create the table:
> sqlite3 waypoints.sqlite
> 
> sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id
> INTEGER PRIMARY KEY);
> 
> 3. Import data:
> sqlite> .separator "\t"
> sqlite> .import waypoints.tsv waypoints
> select * from waypoints where id=1;

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] dbhash collision

[nomad]

On Tue Sep 25, 2018 at 09:48:27AM -0400, Richard Hipp wrote:
> On 9/25/18, Nathan Wagner  wrote:
> > I am working up code to calculate a hash over parts of the data in an sqlite
> > database, and as a start looked at the dbhash.c code found at
> 
> Consider instead using one of these:
> 
> https://www.sqlite.org/src/file/ext/misc/sha1.c
> https://www.sqlite.org/src/file/ext/misc/shathree.c

The first comment line in the sha1three.c file mentions SHA1 instead of
SHA3 (and has a grammar issue):

This SQLite extension implements a functions that compute SHA1
hashes.

Perhaps "implements functions" that "compute SHA1 hashes"?  It was
obviously copied from the sha1.c file which has the same grammar issue.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Draft: Window Functions

[nomad]

In https://www.sqlite.org/draft/windowfunctions.html:

"Window functions may only appears in the result set and..."

s/appears/appear/


"If default is also provided, then it is returned instead of NULL
if row identified by offset does not exist."

s/if row/if the row/# 2 places - both lead() and lag() functions

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use WHERE clause in UPSERT's conflict target

[nomad]

On Wed Jun 20, 2018 at 05:26:19PM -0400, Richard Hipp wrote:
> On 6/20/18, Jonathan Koren  wrote:
> >
> > The grammar & documentation
> >  shows a WHERE
> > clause can be given in the "conflict target" of the UPSERT, but the
> > documentation does not explain how the result of this clause
> > impacts the statement. As a test, I tried the following:
> 
> The WHERE clause on the conflict-target is only used for partial
> indexes.

It would be useful and sensible for SQLite to complain loudly when it
parses a query with a conflict target that doesn't exactly match a
known constraint. Otherwise it is clearly easy for authors to write
legal but misleading queries with actions quite different to the
obvious intention.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor

[nomad]

I can't speak to your other questions, but I have a comment on your
first thoughts:

> but have prior mobile application development experience. My first
> thought is to store the data in a SQLite database table and include
> one column called "IsSynchronized" that can store a boolean value to
> indicate which datapoints have been synced. When the mobile
> application requests data, the records where "IsSynchronized ==
> false" will be transmitted to the mobile app. Once successful, the
> mobile app will let the data logger know that the transmission
> succeeded and set the IsSynchronized column to true for each
> synchronized record. This is how I would do it if the data were
> traveling from a mobile app to a server, but I don't know if this is
> a good idea for an embedded database to a mobile application.

The above implies something like the following:

CREATE TABLE events(
idINTEGER NOT NULL PRIMARY KEY,
epoch INTEGER NOT NULL, -- timestamp
data  BLOB NOT NULL,-- logged data
IsSynchronized BOOLEAN NOT NULL DEFAULT 0
);

Each time you get transmission confirmation you would be performing a:

UPDATE
events
SET
IsSynchronized = 1
WHERE
id = $ID

Which is a whole lot of updates (CPU/flash events) for your little
device. If you know that transmissions only happen chronologically then
I would recommend no IsSynchronized column, but instead to store the
latest successfully transmitted ID and update based on that:

CREATE TABLE meta(
latest_id INTEGER NOT NULL
FOREIGN KEY REFERENCES events(ID)
);

-- Items to transmitt:
SELECT
e.epoch,
e.data
FROM
meta m
INNER JOIN
events e
ON
e.id > m.latest_id
ORDER BY
e.epoch
;

-- When the above is successfullly transmitted then record latest ID
UPDATE
meta
SET
latest_id = (
SELECT
MAX(e.id)
FROM
events e
)
;

Both of the above queries would use an index and be quite fast.  If you
know that your "epoch" timestamps are unique you could make them the
primary key instead of the "id" column I have above, to save even more
space.

Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[nomad]

On Mon Dec 18, 2017 at 04:48:27PM +, Nelson, Erik - 2 wrote:
> Nomad Sent: Sunday, December 17, 2017 4:11 PM
> >On Sat Dec 16, 2017 at 07:53:22PM +, Nelson, Erik - 2 wrote:
>  
> >> Select 1 as value from (insert into table1 values(a, b, c)) I've
> >> tried a number of options but haven't been able to get anything
> ...snip...
> >> to work.  Is it possible?
> 
> >How about a user defined function that does the insert in the
> >background?
> 
> >SELECT my_insert(table_name, val1, val2);
> 
> That's a good suggestion- the only input control I have is sql
> statements that must return at least one row.  AFAIK there's no way
> to make UDFs within that constraint?

That is quite a constraint. Unfortunately then the UDF option is not
available to you.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[nomad]

On Sat Dec 16, 2017 at 07:53:22PM +, Nelson, Erik - 2 wrote:
> For unfortunate reasons, I need a query that does an insert and also
> returns at least one row... for example, something along the lines of
> 
> Select 1 as value from (insert into table1 values(a, b, c))
> 
> Or
> 
> Select coalesce((insert into table1 values(a, b, c)), 1) as value
> 
> I've tried a number of options but haven't been able to get anything
> to work.  Is it possible?

How about a user defined function that does the insert in the
background?

SELECT my_insert(table_name, val1, val2);

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[nomad]

On Tue Nov 28, 2017 at 03:30:54PM +, David Raymond wrote:
> With an integer primary key, not just any primary key. Probably
> something to do with the deterministic flag as well. Looks like in
> checking if it's gonna be a good integer for a rowid it calls it
> twice. Below you can see where random() gets called twice, so what
> the before trigger inserts into another table is different than what
> gets put into the original table. If you have a deterministic
> function like abs() it just calls it once, or if you have a
> non-integer-primary-key table it just calls it once.
> 
> 
> sqlite> create table d (id integer primary key);
> 
> sqlite> create table d2 (id int primary key);
> 
> sqlite> create table t (tableName text, id int);
> 
> sqlite> create trigger bi_d before insert on d begin insert into t values 
> ('d', new.id); end;
> 
> sqlite> create trigger bi_d2 before insert on d2 begin insert into t values 
> ('d2', new.id); end;
> 
> sqlite> insert into d values (random());
> 
> sqlite> insert into d2 values (random());
> 
> sqlite> select * from t;
> tableName|id
> d|-5810358455625904630
> d2|-3456845157187719103
> 
> sqlite> select * from d;
> id
> 6606271909038536929
> 
> sqlite> select * from d2;
> id
> -3456845157187719103

That looks like a good test case to me. This is certainly a regression
as earlier versions of SQLite don't show the same behaviour.

SQLite developers, do you recognise this thread as an issue? I've seen
other topics come and go and be fixed in the meantime, but no statement
on this one.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

[nomad]

On Wed Nov 29, 2017 at 01:57:29PM +, David Raymond wrote:
> http://www.sqlite.org/compile.html
> 
> SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION

Exactly what I was looking for, just in the wrong places. Thanks David.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

[nomad]

On Wed Nov 29, 2017 at 09:21:47AM +, Hick Gunter wrote:
> What about loading your UDF in the CLI?

Do you mean with a .so/.dll? Most of my UDFs are written in Perl, as is
much of the rest of my code, and are not standalone compiled objects.

It would be sufficent if there was a way to write stub UDFs that didn't
actually do anything. For example:

sqlite> select create_udf_stub('my_udf', 1)
sqlite> explain select my_udf('arg');   # OK
sqlite> select my_udf('arg');   # Errors out..

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ignore missing UDFs for command-line EXPLAIN?

[nomad]

The SQLite CLI is a handy tool for explaining query plans and virtual
machine opcodes. Unfortunately it mostly doesn't do me any good because
of user-defined functions in triggers:

sqlite> explain insert into changes default values;
Error: no such function: debug

It would be quite useful if there was a mode/pragma/feature to ignore
these types of errors for explain [query plan].

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime / Transactions / CLI

[nomad]

On Tue Nov 28, 2017 at 10:34:03AM -0700, Keith Medcalf wrote:
> 
> Datetime functions (that is, what constitutes "now") was, by default,
> step-stable.  The value is cached within the VDBE (statement object)
> on its first use per-step and retains the same value until the VDBE
> code yields a row.  Re-entry on the next step used to reset "now".
> 
> This was changed to per-statement stability and not per-step
> stability, though exactly when this change occurred I do not recall
> exactly (I believe it was made a SLOCHNG function when indexes on
> functions were added).
> 
> As I posted earlier you can patch the VDBE code to ensure that "now"
> is always statement stable rather than step stable by only resetting
> "now" on initial entry to the VDBE program (program counter == 0)
> rather than unconditionally.  Of course, the change that made "now"
> statement stable renders this patch moot.
> 
> In no case is now "transaction stable" as the value of now is cached
> within the statement object (VDBE program) and not in the connection
> object.

Thanks, that is quite informative. My original observations
(transaction stable times) were wrong - I was looking at a bunch of
trigger actions and forgot they were all part of one statement.

My original comments/requests to the developers still apply though:

- Can the documentation be updated to include something like
  Keith's description; and
- Can we have some kind of control over whether 'now' is
  step-/statement-/real-time

Regards,
Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Datetime / Transactions / CLI

[nomad]

Can someone point me to the documentation for behaviour of date/time
functions inside transactions? In my code it appears time is frozen.
The command-line client on the other hand doesn't behave the same way:

sqlite> begin immediate;
sqlite> select julianday();
julianday()

2458086.15509343
sqlite> select julianday();
julianday()

2458086.15511422

I'm guessing that the CLI is kind of broken, given that the in-code
version matches with what PostgreSQL does.

On a related note, is a user-defined function the only way to get the
real system time within a transaction? Would the developers consider an
additional "real" (or similar) datetime modifier?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible User Defined Function (UDF) Bug?

[nomad]

Here is a trimmed-down test case for my issue:

CREATE TABLE d (
id INTEGER NOT NULL PRIMARY KEY
);


CREATE TRIGGER
bi_d
BEFORE INSERT ON
d
FOR EACH ROW
BEGIN
select 1;
END;


INSERT INTO
d
VALUES(
udf(1)
);

VDBE Program Listing:
   0 Init 0   200   00 Start at 20
   1 OpenWrite020 1 00 root=2 iDb=0; d
   2 Function0153 udf(1)01 r[3]=func(r[5])
   3 NotNull  350   00 if r[3]!=NULL goto 5
   4 Integer -130   00 r[3]=-1
   5 MustBeInt300   00
   6 Function0164 udf(1)01 r[4]=func(r[6])
   7 Copy 470   00 r[7]=r[4]
   8 Affinity 410 D 00 affinity(r[4])
   9 Program  1   198 program   00 Call: bi_d.default
  10 Copy 710   00 r[1]=r[7]
  11 NotNull  1   130   00 if r[1]!=NULL goto 13
  12 NewRowid 010   00 r[1]=rowid
  13 MustBeInt100   00
  14 SoftNull 200   00 r[2]=NULL
  15 NotExists0   171   00 intkey=r[1]
  16 Halt  155520 d.id  02
  17 MakeRecord   219 D 00 r[9]=mkrec(r[2])
  18 Insert   091 d 31 intkey=r[1] data=r[9]
  19 Halt 000   00
  20 Transaction  012 0 01 usesStmtJournal=1
  21 TableLock021 d 00 iDb=0 root=2 write=1
  22 Integer  150   00 r[5]=1
  23 Integer  160   00 r[6]=1
  24 Goto 010   00

Note the two calls to Function0 in the above. If you create a udf()
function that prints a message you will see that it does in fact get
called twice.

I only see this happening when all three of the following statements
are true:

- The UDF is used in a VALUES() statement
- The destination table "d" has a PRIMARY KEY
- There is a BEFORE INSERT trigger on "d"

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive aggregate query?

[nomad]

On Tue Nov 28, 2017 at 10:13:56AM +0100, no...@null.net wrote:
> I don't understand the error message generated by the following
> schema/query:
> 
> CREATE TABLE x(
> id integer
> );
> 
...
> 
> WITH
> x
> AS

Woops - I just realized the CTE uses a name already defined as a table.
Sorry for the noise.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Recursive aggregate query?

[nomad]

I don't understand the error message generated by the following
schema/query:

CREATE TABLE x(
id integer
);

CREATE TABLE y(
id integer
);

CREATE TABLE y_sequence (
seq INTEGER PRIMARY KEY AUTOINCREMENT
);


WITH
x
AS
(SELECT
0 AS name,
0 as rows

  UNION ALL

SELECT
'sqlite_sequence' AS "name",
COUNT(*) AS rows
FROM
sqlite_sequence

  UNION ALL

SELECT
'y_sequence' AS "name",
COUNT(*) AS rows
FROM
y_sequence

  UNION ALL

SELECT
'y' AS "name",
COUNT(*) AS rows
FROM
y

  UNION ALL

SELECT
'x' AS "name",
COUNT(*) AS rows
FROM
x
)
SELECT
sm.name AS name,
x.rows AS rows,
SUM(CASE
WHEN
sm2.type="index"
THEN
1
ELSE
0
END) AS "indexes",
SUM(CASE
WHEN
sm2.type="trigger"
THEN
1
ELSE
0
END) AS "triggers",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE INSERT%"
THEN
1
ELSE
0
END) AS "bi",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%AFTER INSERT%"
THEN
1
ELSE
0
END) AS "ai",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE UPDATE%"
THEN
1
ELSE
0
END) AS "bu",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE
"%AFTER UPDATE%"
THEN
1
ELSE
0
END) AS "au",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE DELETE%"
THEN
1
ELSE
0
END) AS "bd",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%AFTER DELETE%"
THEN
1
ELSE
0
END) AS "ad"
FROM
sqlite_master sm
  LEFT JOIN
sqlite_master sm2
  ON
sm2.tbl_name = sm.tbl_name
  LEFT JOIN
x
  ON
x.name = sm.tbl_name
WHERE
sm.tbl_name NOT LIKE ? AND sm.type = ?
GROUP BY
sm.name
ORDER BY
sm.name
;

DBD::SQLite::db prepare failed: recursive aggregate queries not supported 
at /usr/lib/perl5/site_perl/5.22/DBIx/ThinSQL.pm line 250.


I don't quite see where the recursive aggregate is. Can anyone
enlighten me?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Assert failure line 72132, function: valueFromExpr

[nomad]

On Mon Nov 27, 2017 at 10:53:50AM -0500, Richard Hipp wrote:
> On 11/27/17, Richard Hipp  wrote:
> 
> > I am unable to reproduce the problem.
> 
> Dan suggested that I needed to enable foreign keys, and that did
> indeed enable me to repro the problem.

Glad to hear that. I was scratching my head wondering how I was going
to come up with a better test case.

> > assertion "0" failed: file "sqlite3.c", line 72132, function:
> > valueFromExpr
> >
> 
> This should be fixed in the latest Pre-release Snapshot, uploaded
> moments ago.

I can confirm that the latest pre-release version no longer generates
the error for me.

Thanks for the quick response.

Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Assert failure line 72132, function: valueFromExpr

[nomad]

[version sqlite-snapshot-201711181730]

I am seeing an error when attempting to insert a row with
SQLITE_DEBUG enabled:

assertion "0" failed: file "sqlite3.c", line 72132, function: valueFromExpr

Running "PRAGMA vdbe_addoptrace=ON" immediately before the insert
produces the following 

   1 Expire   000   00 
   2 Halt 000   00 
   0 Init 010   00 Start at 1
   1 OpenWrite040   00 root=4 iDb=0
   2 NewRowid 010   00 r[1]=rowid
   3 Variable 120   00 r[2]=parameter(1,)
   4 Variable 230   00 r[3]=parameter(2,)
   5 Variable 340   00 r[4]=parameter(3,)
   6 Variable 450   00 r[5]=parameter(4,)
   7 Variable 560   00 r[6]=parameter(5,)
   8 Variable 670   00 r[7]=parameter(6,)
   9 Variable 790   00 r[9]=parameter(7,)
  10 Variable 8   100   00 r[10]=parameter(8,)
  11 Variable 9   110   00 r[11]=parameter(9,)
  12 HaltIfNull129922   00 if r[2]=null halt
  13 HaltIfNull129923   00 if r[3]=null halt
  14 HaltIfNull129924   00 if r[4]=null halt
  15 HaltIfNull129925   00 if r[5]=null halt
  16 HaltIfNull129926   00 if r[6]=null halt
  17 HaltIfNull129927   00 if r[7]=null halt
  18 HaltIfNull129929   00 if r[9]=null halt
  19 HaltIfNull12992   10   00 if r[10]=null halt
  20 MakeRecord   2   10   12   00 r[12]=mkrec(r[2..11])
  21 Insert   0   121   00 intkey=r[1] data=r[12]
   0 Init 010   00 Start at 1
CLEAR
PUSH to 1
   1 Param   1620   00 
   2 Param   2130   00 
   3 Param   1240   00 
   4 Param   1350   00 
   5 Param   1460   00 
   6 Param   1570   00 
   7 Param   1780   00 
   8 Param   1990   00 
   9 Param   20   100   00 
POP  to 0
  10 Function0  51121   00 r[1]=func(r[2])
CLEAR
  11 OpenWrite0  1110   00 root=111 iDb=0
  12 OpenWrite1  1150   00 root=115 iDb=0
  13 Param   16   110   00 
  14 NotNull 1100   00 if r[11]!=NULL goto 0
  15 NewRowid 0   110   00 r[11]=rowid
  16 MustBeInt   1100   00 
  17 SoftNull1200   00 r[12]=NULL
  18 String8  0   130   00 r[13]=''
  19 Param   21   140   00 
  20 Param   18   150   00 
  21 NotNull 15   -40   00 if r[15]!=NULL goto -4
PUSH to 1
PUSH to 2
  22 String8  0   340   00 r[34]=''
  23 String8  0   350   00 r[35]=''
POP  to 1
  24 Function03   34   15   00 r[15]=func(r[34])
POP  to 0
  25 Param   19   160   00 
  26 Param   20   170   00 
PUSH to 1
  27 String8  0   360   00 r[36]=''
  28 Param   20   400   00 
  29 Integer   1000   410   00 r[41]=1000
  30 Divide  41   40   39   00 r[39]=r[40]/r[41]
  31 NotNull 39   -50   00 if r[39]!=NULL goto -5
PUSH to 2
PUSH to 3
  32 String8  0   420   00 r[42]=''
  33 String8  0   430   00 r[43]=''
  34 String8  0   440   00 r[44]=''
POP  to 2
  35 Function07   42   41   00 r[41]=func(r[42])
PUSH to 3
  36 String8  0   450   00 r[45]=''
  37 String8  0   460   00 r[46]=''
POP  to 2
  38 Function03   45   40   00 r[40]=func(r[45])
  39 Subtract40   41   39   00 r[39]=r[41]-r[40]
POP  to 1
  40 Integer   3600   400   00 r[40]=3600
  41 Divide  40   39   37   00 r[37]=r[39]/r[40]
  42 Cast37   680   00 affinity(r[37])
PUSH to 2
  43 Param   20   410   00 
  44 Integer   1000   480   00 r[48]=1000
  45 Divide  48   41   47   00 r[47]=r[41]/r[48]
  46 NotNull 47   -60   00 if r[47]!=NULL goto -6
PUSH to 3
PUSH to 4
  47 String8  0   490   00 r[49]=''
  48 

Re: [sqlite] [EXTERNAL] Possible User Defined Function (UDF) Bug?

[nomad]

My previous explain outputs were probably not quite right. With the
following code inside the previously posted trigger:

INSERT INTO deltas(
id,
change_id,
function
)
VALUES(
nextval('deltas'),
NEW.change_id,
   'update_project'
);

I now see the below explain output with its two Function0 optcodes
where I believe there should only be one.


 140   Insert 0 26   15bifcodes
05  
 141   Program12142  56program 
00  
 142   ResetCount 0 00 
00  
 143   OpenWrite  4 124  0 3   
00  
 144   OpenWrite  5 125  0 k(2,,)  
00  
 145   String80 68   0 deltas  
00  
 146   Function0  1 68   64nextval(1)  
01  
 147   NotNull64149  0 
00  
 148   Integer-164   0 
00  
 149   MustBeInt  6400 
00  
 150   String80 69   0 deltas  
00  
 151   Function0  1 69   65nextval(1)  
01  
 152   Copy   6570   0 
00  
 153   Param  8 66   0 
00  
 154   Copy   6671   0 
00  
 155   String80 67   0 update_project  
00  
 156   Copy   6772   0 
00  
 157   Affinity   6530 DDB 
00  
 158   Program60247  73program 
00  

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Possible User Defined Function (UDF) Bug?

[nomad]

On Mon Nov 20, 2017 at 11:04:01AM +, Hick Gunter wrote:
> 
> Can you provide the original SQL (both for the INSERT and the CREATE
> TRIGGER) and the explain output (SQLite byte code, i.e .explain
> followed by explain )?

Here is the trigger code:

CREATE TABLE func_update_project(
change_id INTEGER NOT NULL,
id INTEGER NOT NULL,
name VARCHAR(40),
parent_id INTEGER,
project_status_id INTEGER,
title VARCHAR
);

CREATE TRIGGER
func_update_project_ai_1
AFTER INSERT ON
func_update_project
FOR EACH ROW
BEGIN

--SELECT debug(
--' change_id: ' || NEW.change_id,
--' id: ' || NEW.id,
--' name: ' || COALESCE(NEW.name,''),
--' parent_id: ' || COALESCE(NEW.parent_id,''),
--' project_status_id: ' || COALESCE(NEW.project_status_id,''),
--' title: ' || COALESCE(NEW.title,'')
--);

SELECT
RAISE(ABORT, 'Bif::Error::InvalidName')
WHERE
CAST(NEW.name AS INTEGER) = NEW.name
;

UPDATE
bifcodes
SET
bifcode = bifcode || (
SELECT
'{U1:_,U14:update_project,'
|| 'U4:name,' ||
CASE WHEN
NEW.name IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(NEW.name AS BLOB)),
CAST(NEW.name AS BLOB))
ELSE
'~'
END
|| 'U11:parent_uuid,' ||
CASE WHEN
pp.uuid IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(pp.uuid AS BLOB)),
CAST(pp.uuid AS BLOB))
ELSE
'~'
END
|| 'U19:project_status_uuid,' ||
CASE WHEN
ps.uuid IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(ps.uuid AS BLOB)),
CAST(ps.uuid AS BLOB))
ELSE
'~'
END
|| 'U5:title,' ||
CASE WHEN
NEW.title IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(NEW.title AS BLOB)),
CAST(NEW.title AS BLOB))
ELSE
'~'
END
|| 'U4:uuid,'
|| printf('U%d:%s,', LENGTH(p.uuid), p.uuid)
|| '}'
FROM
nodes p
LEFT JOIN
nodes pp
ON
pp.id = NEW.parent_id
LEFT JOIN
nodes ps
ON
ps.id = NEW.project_status_id
WHERE
p.id = NEW.id
)
WHERE
change_id = NEW.change_id
;


INSERT INTO deltas(
id,
change_id,
function
)
SELECT
nextval('deltas'),
NEW.change_id,
'update_project'
;


INSERT INTO
node_deltas(
delta_id,
change_id,
node_id,
parent_id,
name
)
SELECT
currval('deltas'),
NEW.change_id,
NEW.id,
NEW.parent_id,
NEW.name
;


INSERT INTO
project_deltas(
delta_id,
change_id,
project_id,
title,
project_status_id
)
SELECT
currval('deltas'),
NEW.change_id,
NEW.id,
NEW.title,
NEW.project_status_id
WHERE
COALESCE(NEW.title,NEW.project_status_id) IS NOT NULL
;

DELETE FROM func_update_project;
END;

The udf that is called twice is the "nextval()" function.

The insert is straightfoward:

INSERT INTO
func_update_project(
change_id,
id,
project_status_id,
title
)
VALUES
(
49,
3,
NULL,
NULL
)
;

I can't use .explain from the sqlite command-line on my database
because of the UDFs, but I can print the formatted output of the
explain command. The VALUES case and the SELECT 

[sqlite] Possible User Defined Function (UDF) Bug?

[nomad]

[ version: sqlite-snapshot-201711181730.tar.gz embedded in Perl's
DBD::SQLite module. ]

I have a user-defined function used as follows:

CREATE TRIGGER
after_insert_x
AFTER INSERT ON
x
FOR EACH ROW
BEGIN

INSERT INTO
y(id)
VALUES(
udf()
);

END;

What I am seeing is that for a row inserted into table y the udf() is
called *twice*. This behaviour only seems to occur with the
INSERT/VALUES combination. If I change the trigger contents to be the
following then the udf is only called once:

INSERT INTO
y(id)
SELECT
udf()
;

I'm having a hard time duplicating this with a standalone test case,
and I'm also having difficulty with EXPLAIN QUERY PLAN returning no
data for some statements... so I have a bit of a rabbit hole to explore
before I can come back with more information. But perhaps someone else 
knows what might be going on?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Typos in the documentation

[nomad]

On Thu Nov 09, 2017 at 09:53:45PM +0100, Philip Newton wrote:
> 
> https://www.sqlite.org/inmemorydb.html under "Testing Services" near
> the end has this as its final sentence:
> 
> "Hardware or system manufactures who want to have TH3 test run on
> their systems can negotiation a service agreement to have the SQLite
> Developers run those tests."

Possibly "manufacturers" above is also missing an extra "r."

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

[nomad]

On Thu Nov 02, 2017 at 10:18:41AM -0400, Richard Hipp wrote:
> On 11/2/17, David Raymond  wrote:
> > For basic level you can use a check constraint
> >
> > create table Table1 (
> >   TestDate DATETIME
> > check (TestDate like '-__-__ __:__:__'
> >and datetime(TestDate) is not null)
> > );
> >
> > That should check that it's in the correct format, and the second part
> > should check that there's no garbage in there.
> 
> How about this approach:
> 
>   CREATE TABLE table1(
>   TestDate DATETIME NOT NULL
>   CHECK(julianday(TestDate) IS NOT NULL)
>   )
> 
> All of the date/time functions return a NULL if you give them an
> invalid date/time as input.  So checking for a NULL result is a good
> way to validate the date/time.  This goes further than the LIKE
> pattern above, as it inhibits nonsense dates such as 'abcd-ef-gh
> ij:kl:mn' which the LIKE pattern would apparently accept.

That approach is also not quite sufficient to ensure that the input is
actually a -mm-dd hh:mm::ss format, because the SQLite datetime
functions also accept HH:MM, 'now', integers/float...

sqlite> select julianday(2017323.32);
julianday(2017323.32)
-
2017323.32

To be really sure the input conforms to a particular date/time format
you need to round-trip it:

CREATE TABLE Table1(
TestDate DATETIME NOT NULL,
CONSTRAINT valid_datetime CHECK(
TestDate = COALESCE(
datetime( julianday(TestDate) ),
TestDate || x'
)
)
);

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Grouping and grabbing one item

[nomad]

On Wed Oct 18, 2017 at 07:57:24PM +0200, Darko Volaric wrote:

> select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1),
> max(ed),target, sum(amt) from Tasks where Pid=1 group by target
> HAVING amt > 0;

When I first read that query I wondered if putting a query inside a
select expression would execute it for each row. I don't know if that
is the case, but here is the query plan:

explain query plan select (SELECT ed from Tasks where task =
'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where
Pid=1 group by target HAVING amt > 0;

0,0,0,"SCAN TABLE Tasks"
0,0,0,"USE TEMP B-TREE FOR GROUP BY"
0,0,0,"EXECUTE SCALAR SUBQUERY 1"
1,0,0,"SCAN TABLE Tasks"

It would appear that moving the subquery down into a FROM clause makes the
query plan look slightly better. In my humble opinion it also makes the
query easier to understand.

explain query plan
select
q.ed,
max(tasks.ed),
target,
sum(amt)
from
(SELECT ed from Tasks where task = 'QUOTE' and Pid = 1) q
inner join
Tasks
on
Pid=1
group by
q.ed,
target
HAVING
amt > 0;

0,0,0,"SCAN TABLE Tasks"
0,1,1,"SEARCH TABLE Tasks USING AUTOMATIC PARTIAL COVERING INDEX 
(Pid=?)"
0,0,0,"USE TEMP B-TREE FOR GROUP BY"

The above is the case with version 3.16.2.


-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] XOR operator

[nomad]

On Fri Oct 06, 2017 at 09:28:08AM +0200, Clemens Ladisch wrote:
> 
> For boolean values, "a XOR b" = "a <> b".

Is the <> operator documented somewhere? I can't find it in either of
these places:

https://sqlite.org/search?s=d=%3C%3E
https://sqlite.org/datatype3.html#comparison_expressions

> For binary values, "a XOR b" = "(a | b) - (a & b)".

The intention of the XOR is much clearer than the expansion, and easier
to write because of the precedence of '-', and ... I can only second
the feature request.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

[nomad]

On Fri Sep 15, 2017 at 09:55:40AM +0200, Dominique Devienne wrote:
> On Thu, Sep 14, 2017 at 11:43 PM, Nico Williams 
> wrote:
> 
> > [...] I would much prefer to be able to specify which CTEs must be
> > materialized,
> > and which may be left as internal views.  That would give the user a great
> > deal of control.  WITH x AS () MATERIALIZED ... .  Can we get that?
> >
> 
> +1 --DD

I would also like to see such a feature. I don't know if the syntax
above is standard SQL. If not, I would suggest an alternative position
for the extra keyword:

WITH MATERIALIZED
x
AS
( SELECT ... )
SELECT
a
FROM
x

That fits in better with the existing RECURSIVE keyword position, looks
better to my eyes, and would make my life as the author of an SQLite
wrapper easier :-)

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting data from WITH clause is very slow

[nomad]

On Mon Sep 11, 2017 at 11:15:22AM +0200, no...@null.net wrote:
> SELECT
> acc_no,
> acc_name,
> SUM(i_90.invoice_bal) AS 90_days,
> SUM(i_current.invoice_bal) AS current
> FROM
>debtors_table
> LEFT JOIN
> invoices i_90
> ON
> i_90.debtor_id = debtors_table.id AND
> i_90.invoice_date > date_5 AND
> i_90.invoice_date <= date_4
> LEFT JOIN
> invoices i_current
> ON
> i_current.debtor_id = debtors_table.id AND
> i_current.invoice_date > date_2 AND
> i_current.invoice_date <= date_1

I should also mention for completeness the need for:

GROUP BY
acc_no,
acc_name

> ORDER BY
> ...

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting data from WITH clause is very slow

[nomad]

On Mon Sep 11, 2017 at 08:40:41AM +0200, Frank Millman wrote:
> I could structure it like this (pseudo code) -
> 
> SELECT acc_no, acc_name,
> (SELECT SUM(invoice_bal) FROM invoices
>   WHERE invoice_date <= date_5) AS 120_days, 
> (SELECT SUM(invoice_bal) FROM invoices
>   WHERE invoice_date > date_5 AND invoice_date <= date_4) AS 90_days, 
> (SELECT SUM(invoice_bal) FROM invoices
>   WHERE invoice_date > date_4 AND invoice_date <= date_3) AS 60_days, 
> (SELECT SUM(invoice_bal) FROM invoices
>   WHERE invoice_date > date_3 AND invoice_date <= date_2) AS 30_days, 
> (SELECT SUM(invoice_bal) FROM invoices
>   WHERE invoice_date > date_2 AND invoice_date <= date_1) AS current
> FROM debtors_table
> 
> This works, but it requires 5 separate scans of the invoice table,
> which is inefficient.

You can achieve the same result with a single table scan using CASE
statements:

SELECT
acc_no,
acc_name,
SUM(
CASE WHEN
   invoice_date <= date_5
THEN
invoice_bal
ELSE
0
END
) AS 120_days, 
SUM(
CASE WHEN
invoice_date > date_5 AND invoice_date <= date_4
THEN
invoice_bal
ELSE
0
END
) AS 90_days, 
 
-- ...
 
SUM(
CASE WHEN
invoice_date > date_2 AND invoice_date <= date_1
THEN
invoice_bal
ELSE
0
END
) AS current
FROM
   debtors_table
LEFT JOIN
invoices
ON
invoices.debtor_id = debtors_table.id

If your time periods are not exhaustive (covering all time) then you
can perhaps be more efficient again by moving the filtering into the
join clause:

SELECT
acc_no,
acc_name,
SUM(i_90.invoice_bal) AS 90_days,
SUM(i_current.invoice_bal) AS current
FROM
   debtors_table
LEFT JOIN
invoices i_90
ON
i_90.debtor_id = debtors_table.id AND
i_90.invoice_date > date_5 AND
i_90.invoice_date <= date_4
LEFT JOIN
invoices i_current
ON
i_current.debtor_id = debtors_table.id AND
i_current.invoice_date > date_2 AND
i_current.invoice_date <= date_1
ORDER BY
...

For the above an index on invoices(debtor_id,invoice_date) would be
appropriate.

Also in case you weren't aware, the SUM() function in my second example
could produce NULL values. You may be better served in that instance by
CAST(TOTAL(i_current.invoice_bal) AS INTEGER).

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE database using parameters

[nomad]

On Fri Jul 21, 2017 at 06:33:55AM +, Edmondo Borasio wrote:
> I am updating a record of a SQLite database as follows:
> 
> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
> 
> but instead of using name and ID I want to use some variables, $NewItemName
>  and $hId.
>
> Entering the variables as they are won't work. Neither using escape
> characters like \"$DbItemName\" and \"$hId\".
> 
> Any idea of the right syntax pls?

It appears that you are using something like Perl or Ruby to access
your database?  If that is the case, then you will find that the
concept of "placeholders" is what you are looking for.

Typically you specify a "?" (without the quotes) where you want your
variables to be, and then "bind" the variables to those positions
either before or during the exec call.

Here is the Perl documentation on that topic:

https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values

I suggest you look up the equivalent in whatever language you are
using.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Request for ISO Week in strftime()

[nomad]

The current '%W' week substitution appears to be US-specific. I would
like to make a feature request for a '%V' (or similar) substitution
that inserts the ISO-8601 week number.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation of valid ORDER BY terms after UNION?

[nomad]

On Mon May 15, 2017 at 09:58:31PM +0100, Simon Slavin wrote:
> 
> On 15 May 2017, at 9:30pm, no...@null.net wrote:
> 
> >SELECT
> >1 AS col
> >UNION ALL
> >SELECT
> >0 AS col
> >ORDER BY
> >col > 0 DESC;
> 
> Out of interest, intuitively rather than reading documentation, which
> do you think should be done first ?  Should SQL do both SELECTs and
> the UNION ALL, then ORDER the result ?  Or should SQL apply the ORDER
> BY to the second SELECT only ?

Intuitively (or according the union knowledge I can usually hold in my
brain) I think of the above as follows, purely because I know there can
only be one ORDER by statement:

(
SELECT
1 AS col
UNION ALL
SELECT
0 AS col
)
ORDER BY
col > 0 DESC;

> > I've read "The ORDER BY clause" of [1] and I *think* that the following
> > is what I am running into:
> > 
> >"However, if the SELECT is a compound SELECT, then ORDER BY
> >expressions that are not aliases to output columns must be exactly
> >the same as an expression used as an output column. "
> 
> Right.  So the problem is that the "AS col" clauses apply to the
> individual SELECT queries, not to the results of the UNION.  The
> UNION command unites the two individual queries but SQL doesn’t
> provide any way to name the resulting column(s).

Except that you *can* use the first SELECT aliases on their own...
which shouldn't be possible at all if SQL doesn't provide a way to name
the resulting columns.

> Here’s another question about intuition, rather than reading
> documentation.  How many columns should this query return ?  Or
> should it result in an error ?
> 
> >SELECT
> >1 AS betty
> >UNION ALL
> >SELECT
> >0 AS carlos;

Ideally this would be an error, but I already know that it isn't so I
can't really say what my intuition thinks :-)

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation of valid ORDER BY terms after UNION?

[nomad]

This works:

SELECT
1 AS col
ORDER BY
col > 0 DESC;

The following fails with "Error: 1st ORDER BY term does not match any
column in the result set."

SELECT
1 AS col
UNION ALL
SELECT
0 AS col
ORDER BY
col > 0 DESC;

I've read "The ORDER BY clause" of [1] and I *think* that the following
is what I am running into:

"However, if the SELECT is a compound SELECT, then ORDER BY
expressions that are not aliases to output columns must be exactly
the same as an expression used as an output column. "

If so, that paragraph could perhaps be written differently, more
clearly indicating that ORDER BY terms can *only* be raw column names
with no complex expressions. One could also perhaps explicitly suggest
what appears to be a valid workaround:

SELECT
*
FROM
(SELECT
1 AS col,
1 > 0 AS truth
UNION ALL
SELECT
0 AS col,
0 > 0 AS truth
) x
ORDER BY
x.col > 0 DESC;

[1] https://www.sqlite.org/lang_select.html

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key constraint failure

[nomad]

On Wed May 10, 2017 at 08:34:42AM +0200, Clemens Ladisch wrote:
> Mark Wagner wrote:
> > Is there a way to get sqlite to tell which foreign key constraint is
> > causing a failure?
> 
> No; to make the implementation of deferred constraints easier, it keeps
> track only of the number of remaining foreign key failures, not of their
> origin.

Maybe; If you are developing and can afford to reload/reset invalid
data you can turn off foreign key support and do the DELETE, and then
run the foreign_key_check pragma:

CREATE TABLE a(
id INTEGER PRIMARY KEY
);

CREATE TABLE b(
id INTEGER,
FOREIGN KEY(id) REFERENCES a(id)
);

INSERT INTO a VALUES(1);
INSERT INTO b VALUES(1);
DELETE FROM a;
-- Error: FOREIGN KEY constraint failed

PRAGMA foreign_keys=0;
DELETE FROM a;
-- No Error, but now your data is invalid

PRAGMA foreign_key_check;
--  table   rowid   parent  fkid
--  --  --  --  --
--  b   1   a   0

You could perhaps even run the DELETE inside a transaction and rollback
once you have obtained the information you need, to keep your data
integrity.

Regards,
Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

[nomad]

On Wed Apr 19, 2017 at 09:53:07AM -0700, petern wrote:
> My sense from these replies is that nobody bothers to try using
> triggers to store their SQLite procedural code within the DB.  I was
> skeptical when I first learned of the technique but the trigger
> syntax is very computationally permissive.  Frankly, I'm still
> surprised by what one is allowed to do in a trigger.

Just to provide at least one data point in the other direction, I use
triggers quite heavily as a kind of stored procedure.

Instead of basing them on views however I use real tables and AFTER
INSERT triggers whose final statement deletes the NEW row just
inserted.

I see two benefits to the use of AFTER INSERT triggers:

* Constraints are enforced so SQLite catches invalid
"procedure calls."
* Default values for columns (or "arguments") can be defined. This
is very useful if you want to use the incoming value in multiple
statements - you don't have to hardcode a bunch of
COALESCE(NEW.col, $DEFAULT) values everywhere.

Because the INSERT/TRIGGER/DELETE happens within a transaction I expect
the data never to hit the disk. I haven't measured it but I guess the
performance would not be too far off the INSTEAD-OF/VIEW trigger.

> CREATE VIEW my_sproc_caller_view as SELECT (33)a, ('some_param')b,
> (55)c, * from my_sproc_worktable;

My own naming convention uses tables like "func_action_name".

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Second beta for SQLite 3.18.0.

[nomad]

On Fri Mar 24, 2017 at 08:31:13AM +0100, no...@null.net wrote:
> On Thu Mar 23, 2017 at 07:50:34PM -0400, Richard Hipp wrote:
> > The second beta release for SQLite 3.18.0 is now available on the website:
> 
> I see the following issue:
> 
> Program terminated with signal SIGSEGV, Segmentation fault.
> #0  sqlite3VdbeExec (p=p@entry=0x9ca2558) at sqlite3.c:83010
> 83010 pCrsr = pC->uc.pCursor;

If I turn on SQLITE_DEBUG the assert on line 83090 fails:

perl: sqlite3.c:83008: sqlite3VdbeExec: Assertion `pC!=0' failed.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Second beta for SQLite 3.18.0.

[nomad]

On Thu Mar 23, 2017 at 07:50:34PM -0400, Richard Hipp wrote:
> The second beta release for SQLite 3.18.0 is now available on the website:
> 
>https://www.sqlite.org/download.html

I see the following issue:

Program terminated with signal SIGSEGV, Segmentation fault.
#0  sqlite3VdbeExec (p=p@entry=0x9ca2558) at sqlite3.c:83010
83010 pCrsr = pC->uc.pCursor;
(gdb) print pC
$1 = (VdbeCursor *) 0x0
(gdb) backtrace
#0  sqlite3VdbeExec (p=p@entry=0x9ca2558) at sqlite3.c:83010
#1  0xb71b56a2 in sqlite3Step (p=0x9ca2558) at sqlite3.c:76605
#2  sqlite3_step (pStmt=) at sqlite3.c:11130
#3  0xb713069b in sqlite_st_execute (sth=0xa2b1dc0, imp_sth=0xa331238)
at dbdimp.c:1038
#4  0xb7121b1d in XS_DBD__SQLite__st_execute (cv=0x98f34d8) at 
./SQLite.xsi:614
#5  0xb7254e8d in XS_DBI_dispatch ()
   from 
/home/mark/src/bif/.direnv/perl5/lib/perl5/i686-linux/auto/DBI/DBI.so
#6  0x080edcb7 in Perl_pp_entersub ()
#7  0x080e7393 in Perl_runops_standard ()
#8  0x08083f12 in perl_run ()
#9  0x0806227d in main ()

The segfault seems to occur during COMMIT.

Versions 3.16 and 3.17 show the same behaviour but 3.13 does not
(changing nothing else other than sqlite3.[ch] and sqlite3ext.h).

Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Beginning of release testing for version 3.17.0

[nomad]

On Tue Feb 07, 2017 at 09:32:18AM -0500, Richard Hipp wrote:
> 
> A draft change log can be seen at
> https://www.sqlite.org/draft/releaselog/3_17_0.html

Nice to see a sha1 extension included with SQLite now. I don't see a
matching SQLITE_ENABLE_SHA1 to add it statically (if that is what the
SQLITE_ENABLE_* compile options do).

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

[nomad]

On Fri Dec 23, 2016 at 02:25:29PM +0100, Clemens Ladisch wrote:
> Lukasz.Stela wrote:
> >The following query returns an error  "1st ORDER BY term does not match
> >any column in the result set".
> 
> This restriction comes from the SQL standard.
> 
> >When I replace the UPPER (Name) by Name - everything works correctly.
> 
> In theory, it would be possible to sort by something that can be derived
> from some column in the result set. But that is not implemented
> in SQLite.

It seems the restriction in SQLite only applies in certain
circumstances (when it is sorting a UNION?):

sqlite> with a as
(select 'A' as name union select 'a' as name)
 select a.name from a order by upper(a.name) asc;
name  
--
A 
a 

sqlite> with a as
 (select 'A' as name union select 'a' as name)
select a.name from a
union
 select a.name from a order by upper(a.name) asc;
Error: 1st ORDER BY term does not match any column in the result set


-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A total with a GROUP BY

[nomad]

On Thu Dec 01, 2016 at 07:57:06PM +0100, Cecil Westerhof wrote:
> At the moment I have the following code:
> SELECT totalUsed, COUNT(*) AS Count
> FROM tips
> GROUP BY totalUsed
> 
> This shows the total number of records for every value of totalUsed.
> Would it be possible to get the total number of records also. (Sum of
> all the Count's.)

Here is one way which I find easy to read:

create table tips(
totalUsed integer,
item  varchar
);

insert into tips values(10,'item');
insert into tips values(10,'item2');
insert into tips values(12,'item3');
insert into tips values(12,'item4');
insert into tips values(12,'item5');

with source(totalUsed,Counts) as (
select
totalUsed, COUNT(*)
from
tips
group by
totalUsed
)
select
totalUsed,Counts
from
source
union all select
'Total:', sum(Counts)
from
source
order by
1
;

Result:

totalUsed   Counts
--  --
10  2
12  3
Total:  5

Unfortunately it is not very efficient because SQLite executes
(expands?) the "source" select twice.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite comes bundled with the Python programming language?

[nomad]

On Sun Nov 13, 2016 at 10:31:09AM -0700, Keith Medcalf wrote:
> 
> (1) is correct.  SQLite is included in the Python distribution.
> (2) is incorrect.  SQLite distribution files do not include Python.

I would say that the sentence as constructed is ambiguous and could be
interpreted both ways. When I first read the statements I matched them
the same way Cecil did. Perhaps:

SQLite was first distributed with the Python programming language
version 2.5.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Partial Indexes and use of LIKE

[nomad]

On Tue Nov 01, 2016 at 10:19:24PM -0400, Richard Hipp wrote:
> On 11/1/16, Mark Lawrence  wrote:
> > Hello all,
> >
> > The documentation for partial indexes (section 3.2) lists "=, <, >, <=,
> >  >=, <>, or IN" as operators that will trigger the use of an
> > index WHERE c IS NOT NULL.
> >
> > It seems to me that LIKE should also be in that list, given that it
> > will also only match when c is not null.
> 
> LIKE and GLOB are added by check-in
> https://www.sqlite.org/src/info/9b3a3b41b59a7ce7

Thanks for that. I don't know if the draft website is in the same
repository (or how often it gets updated) but I don't see a matching
change to the documentation in this patch...

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined symbol: Tcl_TranslateFileName

[nomad]

On Wed Nov 02, 2016 at 02:56:01PM +0530, ravi.shan...@cellworksgroup.com wrote:
> load a tclsqlite3.5.3.so file it throws an error like this.

Probably released on 2007-11-27 - nearly 9 years ago.

> http://search.cpan.org/~vkon/Tcl-1.05/Tcl.pm

Released on 2016-06-28 - 4 months ago

> Perl verison - 5.8.8

Released on 2006-01-31 - nearly 11 years ago

> Tcl version - 8.5

Released on 2007-12-20 - nearly 9 years ago

I suspect that the Perl Tcl module is using a (Tcl,Perl,SQLite) API
that is much newer than the rest of your system. The usual solution to
that issue is to upgrade (or downgrade) things until they are
compatible.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation fault on OpenBSD

[nomad]

On Tue Nov 01, 2016 at 03:15:43PM +, Simon Slavin wrote:
> 
> On 1 Nov 2016, at 3:14pm, Rob Willett 
> wrote:
> 
> > We use the Perl DBD module all the time. What I would expect to see
> > is [... good stuff ...]
> 
> Ah, thanks for the explanation.  I always had trouble understanding
> Perl's approach to anything.  Hope the OP's problem gets solved.

For trivial value types (e.g. obvious strings and/or numbers) Rob's
explaination is usually all one needs to know:

my $sth = $dbh->prepare('statement with ? and ?');
$sth->execute("some","value");

One can even combine prepare, execute & fetch all in one go:

my @array = $dbh->selectrow_array($statement, \%attr, @bind_values);

Unfortunately I can't always do the above steps due to Perl or DBI or
DBD::SQLite making wrong assumptions about the type of the value being
bound.  If I wanted to store say a phone number string (for example
"+417612341234") or a string composed of integers with a leading zero
(012) and just pass it in naively it could get converted to a plain
number (417612341234 or 12) which is not what I want.

So in those cases I have to bind each value separately, specifying the
type. [ This is usually more of an issue with databases that are more
strongly typed than SQLite. ]

my $sth = $db->prepare('statement with ? and ?');
$sth->bind_param(1, '2016-08-01', SQL_DATETIME);
$sth->bind_param(1, '012', SQL_VARCHAR);
$sth->execute;

In fact my code is always doing the bind_param calls individually
because I have an abstraction layer that does it that way whether I
need it or not. There is probably a performance hit but it is somewhere
below the noise floor.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bus Error on OpenBSD

[nomad]

Hi Rowan,

On Fri Oct 28, 2016 at 06:19:59PM +0800, Rowan Worth wrote:
> 
> Every sqlite_stmt you use *must* be finalized via sqlite3_finalize.
> I'm not exactly sure what that looks like from the other side of DBD,
> but I would be checking your perl code for a statement/resultset
> object which outlives the database connection itself.

Some of my new debug statements appear to confirm that: database
handles are being cleaned up before statement handles, even though
presumably the statement handle still has a reference back to the
database.

So I also did some googling on that topic, and it appears that during
Perl's global destruction phase objects may not necessarily be
destroyed in the right order.  That is something I unfortunately don't
have any easy control over :-( Perhaps I can be more explicit
somewhere...

In any event this is probably not an sqlite issue.

Thanks for commenting.

Mark.
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bus Error on OpenBSD

[nomad]

I am seeing a Bus Error at the end of a program that to my
inexperienced eye appears to have something to do with SQLite:

This GDB was configured as "amd64-unknown-openbsd6.0"...
Core was generated by `bif'.
Program terminated with signal 10, Bus error.
Loaded symbols for /mark/src/bif/static/bif
Reading symbols from /usr/lib/libm.so.9.0...done.
Loaded symbols for /usr/lib/libm.so.9.0
Reading symbols from /usr/lib/libc.so.88.0...done.
Loaded symbols for /usr/lib/libc.so.88.0
Reading symbols from /usr/libexec/ld.so...done.
Loaded symbols for /usr/libexec/ld.so
#0  releaseMemArray (p=0xdfdfdfdfdfdfdfdf, N=286555) at sqlite3.c:71943
71943   sqlite3 *db = p->db;

(gdb) backtrace
#0  releaseMemArray (p=0xdfdfdfdfdfdfdfdf, N=286555) at sqlite3.c:71943
#1  0x1285167d22e4 in sqlite3VdbeClearObject (db=0xdfdfdfdfdfdfdfdf, 
p=0x12873d135408) at sqlite3.c:73412
#2  0x1285167d23d2 in sqlite3VdbeDelete (p=0x12873d135408) at 
sqlite3.c:73444
#3  0x128516815191 in sqlite3VdbeFinalize (p=0x12873d135408) at 
sqlite3.c:73362
#4  0x1285168152bd in sqlite3_finalize (pStmt=0x12873d135408) at 
sqlite3.c:75209
#5  0x1285167b5ef5 in sqlite_st_destroy (sth=0x1287bf19a198, 
imp_sth=0x128791361b00) at dbdimp.c:1256
#6  0x1285167ab7b3 in XS_DBD__SQLite__st_DESTROY (cv=Variable "cv" is 
not available.) at SQLite.xsi:799
#7  0x12851685e60a in XS_DBI_dispatch (cv=0x12878d7123c8) at DBI.xs:3781
#8  0x1285168e8ab7 in Perl_pp_entersub () at pp_hot.c:2794
#9  0x128516884036 in Perl_call_sv (sv=0x12878d7123c8, flags=45) at 
perl.c:2775
#10 0x1285168f0c21 in S_curse (sv=0x1287b8a0f1a8, check_refcnt=true) at 
sv.c:6704
#11 0x1285168f0e07 in Perl_sv_clear (orig_sv=0x1287b8a0f1a8) at 
sv.c:6326
#12 0x1285168f15b9 in Perl_sv_free2 (sv=0x1287b8a0f1a8, rc=Variable 
"rc" is not available.) at sv.c:6805
#13 0x1285168e8bc3 in S_visit (f=0x1285168f181c , 
flags=2048, mask=2048) at sv.c:485
#14 0x1285168f1ab0 in Perl_sv_clean_objs () at sv.c:640
#15 0x128516886bc3 in perl_destruct (my_perl=Variable "my_perl" is not 
available.) at perl.c:804
#16 0x128516742a66 in main (argc=5, argv=0x7f7bef58) at 
bundle.c:15988

I find the pointer address 0xdfdfdfdfdfdfdfdf to be a little
suspicious.

The program is a static build of Perl that embeds DBD::SQLite which
embeds sqlite.  I have seen the error with sqlite version 3.10.2 and
3.15.0. I have only seen the error on OpenBSD - my Linux builds seem to
have no problem.

Any ideas how I could debug this further?

Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[nomad]

On Thu Jun 30, 2016 at 09:24:36AM +0200, Olivier Mascia wrote:

> I'd love to have some equivalent to the UPDATE OR INSERT statement
> (or variation on it) that some other engines expose.  But clearly

Does the "INSERT OR REPLACE" syntax not provide what you are looking
for?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql

[nomad]

On Thu May 26, 2016 at 02:29:50PM +0800, Jim Wang wrote:
> hi all
>a table as follow:
>  id   score
>  210
>  3 20
>  5 10
>  3 20
>  2 30
>  2 30

Converting that into SQL we have:

CREATE TABLE a(
id INTEGER,
score INTEGER
);

INSERT INTO a VALUES(2,10);
INSERT INTO a VALUES(3,20);
INSERT INTO a VALUES(5,10);
INSERT INTO a VALUES(3,20);
INSERT INTO a VALUES(2,30);
INSERT INTO a VALUES(2,30);

> how could I select the table as follow  and the count can tell me:
> the id 2 hava 3,the id 3 have 2 the id 5 have 1.

To calculate the number of occurances of each id you can use the
following:

SELECT
id,
COUNT(*) AS id_count
FROM
a
GROUP BY
id
ORDER BY
id
;

id  id_count  
--  --
2   3 
3   2 
5   1 

>   count  id   score
>  3210
>  2320
>  1510
>  2320
>  3230
>  3230

I do not see any possibility of ordering rows the same way you have.
However to obtain the equivalent set you could use a sub query or a
common table expression (CTE):

CTE:

WITH x
AS (
SELECT
a.id,
COUNT(*) AS id_count
FROM
a
GROUP BY
a.id
)
SELECT
x.id_count,
a.id,
a.score
FROM
a
INNER JOIN
x
ON
x.id = a.id
ORDER BY
a.id,
a.score
;

id_countid  score 
--  --  --
3   2   10
3   2   30
3   2   30
2   3   20
2   3   20
1   5   10

Sub query:

SELECT
x.id_count,
a.id,
a.score
FROM
a
INNER JOIN
(SELECT
a.id,
COUNT(*) AS id_count
FROM
a
GROUP BY
a.id
) x
ON
x.id = a.id
ORDER BY
a.id,
a.score
;

Someone with greater knowledge than mine could probably provide a
correlated sub-query as well.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users