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

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

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

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

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

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

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

[sqlite] Shell Feature Request: spaces before dot commands

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

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

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

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

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 >

[sqlite] Documentation error

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

Re: [sqlite] How to group this?

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,

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

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:

Re: [sqlite] UPSERT documentation question

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

Re: [sqlite] UPSERT documentation question

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

[sqlite] UPSERT documentation question

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

[sqlite] INSERT/UPDATE generated columns?

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] mailinglists.sqlite.org

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

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

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

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

> 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

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

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

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

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

Re: [sqlite] dbhash collision

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

[sqlite] Draft: Window Functions

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 -

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

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

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

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 >

Re: [sqlite] Odd question

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

Re: [sqlite] Odd question

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

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

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

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

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

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

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

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

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

Re: [sqlite] Datetime / Transactions / CLI

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.

[sqlite] Datetime / Transactions / CLI

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()

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

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;

Re: [sqlite] Recursive aggregate query?

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

[sqlite] Recursive aggregate query?

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

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

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

[sqlite] Assert failure line 72132, function: valueFromExpr

[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

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

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

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

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(

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

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

Re: [sqlite] Typos in the documentation

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

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

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

Re: [sqlite] Grouping and grabbing one item

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

Re: [sqlite] XOR operator

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

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

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

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

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 >

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

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

Re: [sqlite] UPDATE database using parameters

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

[sqlite] Request for ISO Week in strftime()

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

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

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

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

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

Re: [sqlite] foreign key constraint failure

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

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

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

Re: [sqlite] Second beta for SQLite 3.18.0.

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

Re: [sqlite] Second beta for SQLite 3.18.0.

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

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

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

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

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 -

Re: [sqlite] A total with a GROUP BY

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

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

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.

Re: [sqlite] Partial Indexes and use of LIKE

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

Re: [sqlite] undefined symbol: Tcl_TranslateFileName

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

Re: [sqlite] Segmentation fault on OpenBSD

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

Re: [sqlite] Bus Error on OpenBSD

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

[sqlite] Bus Error on OpenBSD

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] sql

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: