SQLite Forum

Timeline
Login

50 most recent forum posts by user mark

2021-11-11
10:51 Reply: time difference between datetime value (artifact: 212a141a24 user: mark)

I should clarify that the above dur result is in days. Multiply by 24*60*60 to get seconds.

I would also recommend learning about "bound values" in whatever language you are using to query SQLite with, instead of interpolating variables into your query string, to avoid some common security pitfalls.

10:42 Reply: time difference between datetime value (artifact: 42c0821c7a user: mark)

Here is one possibility, using the lag() window function and the julianday() datetime function:

CREATE TABLE t1(
    time TEXT
);

INSERT INTO t1(time) VALUES
    ('11-08-2021 15:16:44'),
    ('11-08-2021 17:09:22'),
    ('11-09-2021 17:20:39'),
    ('11-11-2021 09:31:54'),
    ('11-11-2021 10:35:37'),
    ('11-11-2021 10:45:11'),
    ('11-11-2021 11:54:28')
;

WITH normal AS (
    SELECT substr(time,7,4) || '-'
        || substr(time,4,2) || '-'
        || substr(time,1,2) || ' '
        || substr(time,12) AS time
    FROM t1
)
SELECT
    time,
    lag(time, 1) OVER (ORDER BY time),
    julianday(time) - (julianday(lag(time, 1) OVER (ORDER BY time))) AS dur
FROM normal
ORDER BY time;

Which results in the following:

time                 lag(time, 1) OVER (ORDER BY time)  dur
-------------------  ---------------------------------  -------------------
2021-08-11 15:16:44  NULL                               NULL
2021-08-11 17:09:22  2021-08-11 15:16:44                0.0782175925560296
2021-09-11 17:20:39  2021-08-11 17:09:22                31.0078356484883
2021-11-11 09:31:54  2021-09-11 17:20:39                60.6744791665114
2021-11-11 10:35:37  2021-11-11 09:31:54                0.0442476850003004
2021-11-11 10:45:11  2021-11-11 10:35:37                0.00664351880550385
2021-11-11 11:54:28  2021-11-11 10:45:11                0.0481134257279336

2021-05-31
10:12 Post: UINT Collation not well linked in documentation (artifact: e8d1f87895 user: mark)

The UINT collation is enabled by default in the CLI. However searching for UINT on sqlite.org only returns a single document[1]. Perhaps it could be linked to from [2] for better visibility.

2021-05-21
07:55 Reply: Query triggers Segmentation Fault (artifact: 74307a2bf2 user: mark)

Richard, Your use of a CTE in the trigger above appears to contradict chapter 5 of https://sqlite.org/lang_with.html. Has something changed or do I misunderstand the documentation?

2021-05-17
10:39 Reply: Help calculating percentage in SQL (artifact: 484e7a264b user: mark)

As RobMan suggested a window function can give you the sum of all the counts:

    100 * cast(count(EventCode) AS REAL)
        / sum(count(EventCode)) over () as Percent
2021-04-27
07:45 Reply: Week numbers in queries (artifact: 3ce9d0ed96 user: mark)

Thanks for that Harald. I have needed a few of these over the years and it has always been a pain that SQLite hasn't supported them.

I can only encourage the developers to incorporate these for easier international date presentations.

2021-03-31
05:55 Reply: What is the point of a lone SELECT statement in an SQLite trigger? (artifact: 7a21e8ff38 user: mark)

You might want to read up on comparing values against NULL. Your first comparison:

WHEN NEW.LOC_DTS_CREATED != NULL

Will never evaluate to true. You want to use something like this instead:

WHEN NEW.LOC_DTS_CREATED IS NOT NULL
2021-02-23
12:04 Reply: DROP COLUMN feature with indexes (artifact: 0c3a6a63e8 user: mark)

What Would PostgreSQL Do (WWPD)?

DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT CASCADE ]

This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well. Multivariate statistics referencing the dropped column will also be removed if the removal of the column would cause the statistics to contain data for only a single column. You will need to say CASCADE if anything outside the table depends on the column, for example, foreign key references or views. If IF EXISTS is specified and the column does not exist, no error is thrown. In this case a notice is issued instead.

From https://www.postgresql.org/docs/current/sql-altertable.html

10:59 Reply: DROP COLUMN feature with indexes (artifact: c38f475f0a user: mark)

Take the following scenario, where there is no explicit index to drop:

CREATE TABLE t1(
    a INTEGER primary key,
    b INTEGER,
    c INTEGER,
    UNIQUE(b,c)
);

ALTER TABLE t1 DROP COLUMN c;

If the DROP COLUMN fails instead of removing the automatic index, then the caller has no option but to re-create the table completely. This reduces the useful scope of DROP COLUMN.

2021-02-22
21:02 Reply: DROP COLUMN feature with indexes (artifact: 6a26ccca9a user: mark)

Do you have a rationale for why you prefer fail to drop? If the user intends to drop a column and SQLite says you can't because of x,y and z, then the user has to manually do that work anyway. Why make them jump through the extra hoops? To me that is like asking the user to manually delete triggers before dropping a table.

There is however at least one situation today where DDL does result in an inconsistent schema: the first table of a circular foreign key reference. Ideally that would only be allowed inside a transaction and rejected at COMMIT time unless resolved by additional DDL statements. But that doesn't seem to be the case.

Similarly, perhaps one could imagine wanting to change a column type, leaving the rest of the schema alone:

BEGIN;
ALTER TABLE t ADD COLUMN x1 $NEWTYPE;
UPDATE t SET x1=x;
ALTER TABLE t DROP COLUMN x;
ALTER TABLE t RENAME COLUMN x1 TO x;
COMMIT;

It would possibly be annoying to have to recreate all views and triggers here. In that case an immediate dropping of related views and triggers would not be so useful. But then neither should the DROP COLUMN command fail before the surrounding transaction commits.

18:23 Reply: DROP COLUMN feature with indexes (artifact: d1f9c4a2be user: mark)

I vote for dropping them. It would be consistent with DROP TABLE behaviour for indexes.

On a related note, I'm not sure how much schema checking SQLite does for triggers, but CREATE TRIGGER x BEFORE UPDATE OF z ON c1 also remains unmodified after a column is dropped.

16:28 Post: DROP COLUMN feature with indexes (artifact: 90601f7c66 user: mark)

The DROP COLUMN feature (from the development branch) doesn't handle indexes:

CREATE TABLE p1(a PRIMARY KEY, b UNIQUE);
CREATE TABLE c1(x, y, z REFERENCES p1(c));
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
CREATE INDEX i_cz ON c1(z);
CREATE INDEX i_cxy ON c2(x,z);
ALTER TABLE c1 DROP COLUMN z;
ALTER TABLE c2 DROP COLUMN z;
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');

The above results in:

Error: near line 6: error in index i_cz after drop column: no such column: z
Error: near line 7: error in index i_cxy after drop column: no such column: z
2021-02-10
13:13 Reply: SAVEPOINTS (artifact: d3cdd6f050 user: mark)

Nothing to do with your SAVEPOINT query, but I just wanted to point out that you can INSERT ... SELECT directly without the extra VALUES and subquery:

INSERT INTO AUDIT(EMP_ID, NOTICE)
SELECT
    NEW.ID,
    CASE 1
        WHEN EMP_ID < 10
        THEN NEW.NAME || ' is too young to be an employee.'
        ELSE 'OK.'
      END;
09:03 Reply: Split an Extract column (artifact: 50ea50f36e user: mark)

Because I find it challenging to keep track of deeply nested function calls I'll offer the following riff based on Keith's solution:

WITH the_table(id,this_column) AS (
    VALUES
        (1, '{1,1,20210206000704,,,,,1772285065,10,(10)4045420,100230,N,1}'),
        (2, '{1,1,20210126033937,,,,,1772285065,10,(317)5757554,100236 ,N,1}'),
        (3, '{1,1,20210202030039,,,,,1772285065,10,(325)4092770,100208 ,N,1}'),
        (4, '{1,1,20210202170400,,,,,1772285065,10,(377)4040420,100230 ,N,1}'),
        (5, '{1,1,20210203031334,,,,,1772285065,10,(45)4098070,100208, N,1}')
  ), front(id,a) AS (
    SELECT id, substr(this_column, instr(this_column, '(') + 1)
    FROM the_table
  ), back(id,b) AS (
    SELECT id, substr(a, 1, instr(a, ',') - 1)
    FROM front
  ), clean(id, c) AS (
    SELECT id, replace(b, ')', '')
    FROM back
  )
SELECT
    the_table.this_column,
    clean.c
FROM clean
INNER JOIN the_table
ON the_table.id = clean.id;

It requires SQLite to perform an extra scan, but it saves me from having to perform multiple scans over the SQL.

2021-02-08
11:48 Reply: Changes are missing after savepoint release (artifact: 26eb676d66 user: mark)

I would check with a later version of SQLite. For me with a relatively recent version it works:

-- Loading resources from /home/mark/.sqliterc
SQLite version 3.34.0 2020-09-01 00:26:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t1 (
   ...>   id integer primary key,
   ...>   name text,
   ...>   age integer
   ...> );
sqlite> savepoint sp1;
sqlite> insert into t1 (name, age) values('item1', 1);
sqlite> rollback to sp1;
sqlite> savepoint sp2;
sqlite> insert into t1 (name, age) values('item2', 2);
sqlite> release sp2;
sqlite> select * from t1;
id  name   age
--  -----  ---
1   item2  2
sqlite>
2021-02-05
08:49 Reply: Use of RETURNING clause as a subquery (artifact: 7aae5339cd user: mark)

The following in PostgreSQL only inserts a single row:

CREATE TABLE t1(a serial PRIMARY KEY);

WITH x AS (
    INSERT INTO t1 DEFAULT VALUES
    RETURNING a
  )
SELECT a
FROM x
UNION ALL SELECT a
FROM x;

If I recall correctly, SQLite "embeds" the WITH table as a subquery inside each use within the main statement. Would this result in the insertion of two rows?

08:36 Post: RETURNING clause and TRIGGERS (artifact: 4c4e3c6612 user: mark)

The 5th limitation for the RETURNING clause says:

"The values emitted by the RETURNING clause are the values as seen by the top-level DELETE, INSERT, or UPDATE statement and do not reflect any subsequent value changes made by triggers. Thus, if the database includes AFTER triggers that modifies some of the values of each row inserted or updated, the RETURNING clause emits the original values that are computed before those triggers run."

The 4th limitation includes an example with a subquery to extract values from another table. My questions are:

  1. Given the naming restriction of the subquery (i.e. the need to reference the outside table explicitly) can the outside table be SELECTed from again[1] with an alias?

  2. If so, do the table values selected from in the subquery include modifications by AFTER triggers (even though the parameters in the query do not)?

  3. Perhaps more generally, do other tables in the subquery also reflect triggered changes?

[1]

    UPDATE t1
    SET
        b = b + 1
    RETURNING (
        SELECT alias.b
        FROM t1 alias
        WHERE alias.b = t1.a
      );

2021-02-02
10:55 Reply: SQLite3 Array? (artifact: 13178cb477 user: mark)

I compiled this version myself (from trunk?) while tracking down an issue somewhere.

10:46 Reply: SQLite3 Array? (artifact: 8f34e3725d user: mark)

I have SQLite version 3.34.0 2020-09-01 00:26:21 3ca0b7d54d73d... which when running your example says Error: near line 27: circular reference: paths. Am I missing a feature or later commit?

2021-01-31
13:23 Reply: Suggestion a new dot command (.map) (artifact: 1b143a1b11 user: mark)

I find the table you have created valuable. Would you mind sharing the SQL you generate it with?

2021-01-11
14:07 Reply: Syntax Error - Why? (artifact: b314a59f75 user: mark)

Perhaps what you are trying to do is actually the following:

INSERT INTO tblaplwincf(name)
SELECT 'aa'
WHERE changes() = 0;
2021-01-05
15:36 Reply: Can not drop a table... (artifact: 3f69ebbe21 user: mark)

That is a helpful pragma for circular references, but it doesn't change the behaviour around this particular issue (at least not with my testing).

2021-01-04
15:46 Edit reply: Can not drop a table... (artifact: 17ed075924 user: mark)

I'm aware of what the situation is, and following the FK dependencies eventually sorted that out. But having lots of references makes that a very manual task because SQLite provides no context in its error message.

My original comment can be enhanced to be more direct:

  1. Can the error reporting be made clearer? I.e. point to the table that has the dangling reference, not just to the missing target table.

  2. Could the "integrity_check" or "foreign_key_check" pragmas pick up and report on this type of broken schema?

  3. [Comment edited to add this 3rd item] Perhaps SQLite could even set an internal flag whenever a DDL command is issued during a transaction, and check for an invalid schema just before COMMIT? It would be nice not to put a database into this situation at all.

[Edited a 2nd time to say] Sometimes you can't avoid the creation or deletion of tables in the "wrong" order - I regularly make use of circular references, and make schema changes to those tables, so one or the other of CREATE TABLE or DROP TABLE is potentially going to have this issue.

15:40 Edit reply: Can not drop a table... (artifact: 11ad9211dc user: mark)

I'm aware of what the situation is, and following the FK dependencies eventually sorted that out. But having lots of references makes that a very manual task because SQLite provides no context in its error message.

My original comment can be enhanced to be more direct:

  1. Can the error reporting be made clearer? I.e. point to the table that has the dangling reference, not just to the missing target table.

  2. Could the "integrity_check" or "foreign_key_check" pragmas pick up and report on this type of broken schema?

  3. [Comment edited to add this 3rd item] Perhaps SQLite could even set an internal flag whenever a DDL command is issued during a transaction, and check for an invalid schema just before COMMIT? It would be nice not to put a database into this situation at all.

15:27 Reply: Can not drop a table... (artifact: 56b4d1303c user: mark)

I'm aware of what the situation is, and following the FK dependencies eventually sorted that out. But having lots of references makes that a very manual task because SQLite provides no context in its error message.

My original comment can be enhanced to be more direct:

  1. Can the error reporting be made clearer? I.e. point to the table that has the dangling reference, not just to the missing target table.

  2. Could the "integrity_check" or "foreign_key_check" pragmas pick up and report on this type of broken schema?

14:50 Reply: Can not drop a table... (artifact: c476ce393a user: mark)

I don't see what you mean by second "sqlite3" command. What you wrote appears to be a 3rd argument to the original "sqlite3" command(?) which results in this as expected:

Error: near "sqlite3": syntax error

I've managed a minimal test case though:

DROP TABLE IF EXISTS a;
CREATE TABLE a(ID INTEGER NOT NULL PRIMARY KEY);

DROP TABLE IF EXISTS b;
CREATE TABLE b(
    ID INTEGER NOT NULL PRIMARY KEY,
    c_ID INTEGER REFERENCES c(ID),
    a_ID INTEGER NOT NULL REFERENCES a(ID) ON DELETE CASCADE
  );

DROP TABLE a;

The above produces the following:

Error: near line 11: no such table: main.c

If you remove the "ON DELETE CASCADE" then the error goes away.

11:55 Post: Can not drop a table... (artifact: e7cb140bd9 user: mark)

I have a database, which appears to be ok:

sqlite> pragma integrity_check;
integrity_check
---------------
ok

And pragma foreign_key_check also returns no errors, yet I am unable to drop a table due to some schema issue:

sqlite> drop table countries;
Error: no such table: main.Service_Catalog

I'll be able to trace the various schema statements to work out what my issue is[1] so I'm not asking for help. But perhaps there is a different pragma that reports on such conditions, and in more detail?

I don't know how I got the current schema (lots of fast-paced development) but it would also be nice if SQLite prevented me from creating it.

[1] Unfortunately I am unable to share the schema publically.

2020-12-11
15:50 Reply: SQLite with Perl / DBI database file not updated (artifact: d02fb5e54a user: mark)

You can set that if you wish. From the DBD::SQLite documentation:

   Processing Multiple Statements At A Time
       DBI's statement handle is not supposed to process multiple statements
       at a time. So if you pass a string that contains multiple statements (a
       "dump") to a statement handle (via "prepare" or "do"), DBD::SQLite only
       processes the first statement, and discards the rest.

       If you need to process multiple statements at a time, set a
       "sqlite_allow_multiple_statements" attribute of a database handle to
       true when you connect to a database, and "do" method takes care of the
       rest (since 1.30_01, and without creating DBI's statement handles
       internally since 1.47_01). If you do need to use "prepare" or
       "prepare_cached" (which I don't recommend in this case, because
       typically there's no placeholder nor reusable part in a dump), you can
       look at << $sth->{sqlite_unprepared_statements} >> to retrieve what's
       left, though it usually contains nothing but white spaces.
2020-12-03
16:00 Reply: Is typeof() and hex() unique ? (artifact: 8e02c90a5b user: mark)

I didn't know that CAST x AS BLOB had anything to do with strings, which apparently it does as detailed by Richard below, and as also indicated all over the documentation (which I hadn't actually read/remembered).

I find that rather strange behaviour.

15:31 Delete reply: Is typeof() and hex() unique ? (artifact: cfbd197dcf user: mark)
Deleted
15:26 Reply: Is typeof() and hex() unique ? (artifact: 2405c78a35 user: mark)

Sorry that was meant to be with a hex() (which still gives the same strange result)

SELECT hex(CAST(x AS BLOB)) = hex(CAST(y AS BLOB)) AS blob_compare FROM t1;
blob_compare
------------
1
15:23 Reply: Is typeof() and hex() unique ? (artifact: e2da85e8ea user: mark)

So why does CASTing into BLOBs first not work?

CREATE TABLE t1(x,y);
INSERT INTO t1 VALUES (
    ieee754(7205759403792793,-51),
    ieee754(3602879701896397,-50)
);
SELECT ieee754(x) = ieee754(y) AS ieee754_compare FROM t1;

ieee754_compare
---------------
0

SELECT CAST(x AS BLOB) = CAST(y AS BLOB) AS blob_compare FROM t1;

blob_compare
------------
1
2020-11-29
19:38 Post: Use of `.excel` under Cygwin (artifact: b38951a384 user: mark)

With sqlite3.exe under Cygwin I am unable to use the .excel command. There are apparently two separate issues on a default installation.

The first is that as Cygwin is identified as a Unix-style environment, SQLite attempts to call xdg-open, which does not exist unless the user has installed the xdg-utils package. Perhaps SQLite could point this particular package out with a more helpful error message.

The second (actually the only real) issue is that the path given to xdg-open (when it is installed) appears to be invalid, missing backslashes:

Unable to start 'C:cygwin64tmpetilqs_VRZheZvmh6MvCTU.csv': The specified file was not found.
Failed: [xdg-open C:\cygwin64\tmp\etilqs_VRZheZvmh6MvCTU.csv]

Any chance someone else could confirm the same behaviour on another Cygwin installation? Maybe the devs or other interested parties feel like investigating...

Thanks, Mark

2020-09-01
09:57 Post: UPDATE x AS y: alias invalid in "SET" expr (artifact: 04f6b63a9e user: mark)

The following fails to parse:

CREATE TABLE atable(id INTEGER);

UPDATE atable AS a
SET a.id = 2  -- HERE
WHERE a.id = 1;

The "a.id" is not accepted in a SET expression even though it works in a WHERE expression. For consistency it would be nice if it did so. Has only become interesting to me since the UPDATE ... FROM support was added - I wanted to qualify all column names.

Version 3.34.0 2020-09-01 00:26:21 3ca0b7d54d73d07cd6b32e650a809174bb1cd66ce5ecdb36f65b70899ea05824

2020-08-26
03:58 Reply: Validating dates for YYYY-MM-DD format (artifact: a1414bf570 user: mark)

The short version looks like this:

CREATE TABLE a(
    dt TEXT -- Can be NOT NULL if you like
    CONSTRAINT valid_date CHECK(dt IS date(dt,'+0 days'))
);

See [1] for a deeper discussion.

Perhaps this question could go in a FAQ somewhere?

[1] https://marc.info/?l=sqlite-users&m=156578125918594&w=2

2020-07-08
06:59 Reply: table locked when dropping a temp table (artifact: 520e817c52 user: mark)

Ah yes, you are right, and that can be a bit of a drawback if most of the columns have NOT NULL constraints. But I think ON CONFLICT still beats the alternatives...

06:25 Reply: table locked when dropping a temp table (artifact: 73b086755d user: mark)

The drawback you describe does not actually exist. The only columns you need to include in the INSERT statement are those sufficient to cover the ON CONFLICT.

INSERT INTO variables(id)
SELECT id FROM variables WHERE orden >= 6 ORDER BY orden DESC
ON CONFLICT(id) DO UPDATE SET orden = excluded.orden + 1;

The above should work just as well as your example code with all columns.

2020-06-16
19:24 Reply: Updating unique columns feature request (artifact: fae910011b user: mark)

You can use UPSERT[1] to force the order in which UPDATES are performed.

CREATE TABLE t1(id INTEGER);
INSERT INTO t1 VALUES (1), (2), (3);
SELECT * FROM t1;
-- 	id
-- 	--
-- 	1
-- 	2
-- 	3

INSERT INTO t1( rowid, id)
SELECT rowid, id + 1
FROM t1
WHERE 1      		-- Some kind of WHERE needed for UPSERT parsing
ORDER BY id DESC	-- INSERT and therefore also UPDATE order
ON CONFLICT(rowid) DO UPDATE SET id = excluded.id;

SELECT * FROM t1;
-- id
-- --
-- 2
-- 3
-- 4

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

2020-05-21
14:12 Reply: Feature Request: consistent .dump order (artifact: ef31ec8564 user: mark)

Actually, the above does NOT do the trick, but results in a corrupted database:

Error: malformed database schema (sqlite_autoindex_countries_1) - orphan index

Back to the drawing board :-(

12:57 Reply: Feature Request: consistent .dump order (artifact: 6d9c2075e5 user: mark)

I have a (dangerous) user-space workaround for the .dump order requirement:

CREATE TEMPORARY TABLE _master AS select * from sqlite_master;
PRAGMA writable_schema=ON;
DELETE FROM sqlite_master;
INSERT INTO sqlite_master SELECT * from _master ORDER BY tbl_name,type,name;
PRAGMA writable_schema=OFF;
DROP TABLE temp._master;

Running the above before .dump seems to do the trick, without having to modify and build from source. Would still be nice to have it by default or as an --ordered option to .dump.

2020-05-20
16:49 Reply: Overriding collation of IN expressions (artifact: 4c7504e3b5 user: mark)

Thanks Richard. That makes it clear why it works (or doesn't) the way it does.

New syntax I learned today:

    sqlite> select 'b' collate nocase = 'B';
    'b' collate nocase = 'B'
    ------------------------
    1

This positioning of collate is new to me... Can I suggest an additional sentence in datatype3.html:

    The collating sequence used for expressions of the form
    "x IN (y, z, ...)" is the collating sequence of x. If an explicit
    collating sequence is required it should be applied to the left hand
    side of the expression: "x COLLATE nocase IN (y,z, ...)"
11:04 Post: Overriding collation of IN expressions (artifact: e82f164c0b user: mark)

The documentation at [1] on collation says:

 The rules for determining which collating function to use for a
 binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT)
 are as follows:

    1. If either operand has an explicit collating function
    assignment using the postfix COLLATE operator, then the
    explicit collating function is used for comparison, with
    precedence to the collating function of the left operand.

It also mentions the following:

The expression "x IN (SELECT y ...)" is handled in the same way as
the expression "x = y" for the purposes of determining the
collating sequence. The collating sequence used for expressions of
the form "x IN (y, z, ...)" is the collating sequence of x.

Given the above I would have expected that SELECT with an IN expression to behave the same as a SELECT with an '=' expression when COLLATE NOCASE is added to either one. But that does not seem to be the case:

-- 3.32.0 2020-04-18 14:12:00 d5b0def96ba6d90f47bc96fab1ccf9c501d84885d086744035b16fd96f3e248c

CREATE TABLE a(b TEXT);
INSERT INTO a
VALUES
    ('one'),
    ('ONE');

SELECT *
FROM a
WHERE b = 'one' COLLATE nocase;

-- b
-- ----------
-- one
-- ONE

SELECT *
FROM a
WHERE b IN ('one') COLLATE nocase;

-- b
-- ----------
-- one

This appear to be an oversight in (hopefully) the implementation or the documentation.

[1] https://www.sqlite.org/datatype3.html#assigning_collating_sequences_from_sql
2020-05-16
13:50 Reply: Feature request for command shell: External editing of schema (artifact: 3d5f764ab7 user: mark)

( By the way, I am anonymous above - philosophize on that as you will :-)

You are right - my proposed .edit command does not answer your feature request exactly. So I would ammend my syntax proposal to the following:

.edit [--table|-t NAME] [FILENAME]

Where --table NAME dumps the matching begin/table-or-view+triggers/commit definitions (into FILENAME if given or a tmpfile otherwise) to be externally $EDITOR'd and .read. Now that I have described it I can say I would have used this about 50 times today already if it existed.

p.s. There is also a similar \ef in psql for editing function definitions

2020-05-13
06:30 Reply: Can this simple query be optimized? (artifact: 7a1def47f4 user: mark)

I just realized my suggestion may or may not be sufficient for the original poster. Their original union query and the union query above always produce two rows for each group. My version only produces one row when y min and y max are the same.

06:22 Reply: Can this simple query be optimized? (artifact: 75b5ed8a7d user: mark)

Your window function version has the following query plan:

QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  |--CO-ROUTINE 1
   |  |  |--CO-ROUTINE 4
   |  |  |  |--SCAN TABLE foo
   |  |  |  `--USE TEMP B-TREE FOR ORDER BY
   |  |  `--SCAN SUBQUERY 4
   |  `--SCAN SUBQUERY 1
   `--UNION USING TEMP B-TREE
      |--CO-ROUTINE 1
      |  |--CO-ROUTINE 5
      |  |  |--SCAN TABLE foo
      |  |  `--USE TEMP B-TREE FOR ORDER BY
      |  `--SCAN SUBQUERY 5
      `--SCAN SUBQUERY 1

I don't know how much of a performance difference it makes in practice, but the following query plan has fewer co-routines and subqueries:

WITH M(x, y, rmin, rmax) AS (
    SELECT x, y,
           rank() OVER (PARTITION BY (X/10) ORDER BY y ASC),
           rank() OVER (PARTITION BY (X/10) ORDER BY y DESC)
      FROM foo
)
SELECT x,y
FROM M
WHERE rmin=1 OR rmax=1;

--  QUERY PLAN
--  |--CO-ROUTINE 1
--  |  |--CO-ROUTINE 3
--  |  |  |--CO-ROUTINE 4
--  |  |  |  |--SCAN TABLE foo
--  |  |  |  `--USE TEMP B-TREE FOR ORDER BY
--  |  |  |--SCAN SUBQUERY 4
--  |  |  `--USE TEMP B-TREE FOR ORDER BY
--  |  `--SCAN SUBQUERY 3
--  `--SCAN SUBQUERY 1
2020-04-30
18:21 Post: Renaming TABLE *into* existing VIEW definition? (artifact: db1ff37c1b user: mark)

Don't know how to describe my issue better than this:

CREATE VIEW v1 AS
    SELECT * FROM x;

CREATE TABLE y(id INTEGER);

ALTER TABLE y RENAME TO x;
-- Error: near line 6: error in view v1: no such table: main.x

I don't know why the rename concerns itself with table 'x'. It feels to me like that error is raised a little too early.

https://sqlite.org/lang_altertable.html says:

If the table being renamed has triggers or indices,
then these remain attached to the table after it has been renamed. 

That doesn't seem to cover this situation. If this is not a bug per se then perhaps the documentation could at explain the background actions (and constraints) in more detail.

2020-04-23
05:43 Reply: create trigger (artifact: 96b1f49986 user: mark)

Keith has explained about the need for the WHERE clause in the UPDATE statements.

But you also need to keep in mind that statements within a trigger can make other triggers fire[1].

So for your examples above when the UPDATE inside insert_process_order runs it may cause the update_process_order trigger to fire as well, updating the same row again.

You can add a WHEN clause to make triggers conditional (the below is untested):

CREATE TRIGGER
    update_process_order
AFTER UPDATE ON
    ProcessingOrders
FOR EACH ROW WHEN
    OLD.modified != DATETIME('NOW','localtime')
BEGIN
    UPDATE ProcessingOrders
    SET Modified = DATETIME ('NOW', 'localtime')
    WHERE ROWID = new.ROWID;
END;

[1] I can't find a better documentation source than this at the moment: https://www.sqlite.org/pragma.html#pragma_recursive_triggers

2020-04-22
15:42 Reply: Feature Request: consistent .dump order (artifact: ce7da63107 user: mark)

That helps to a degree. Unfortunately triggers and indexes are not explicitly ordered either, so the issue is still the same (within a single call to .dump).

12:06 Post: Feature Request: consistent .dump order (artifact: 2e5883207f user: mark)

It appears that the .dump shell command selects tables from sqlite_master in rowid or insertion order:

> CREATE TABLE a(id INTEGER);
> CREATE TABLE b(id INTEGER);
> .dump

CREATE TABLE a(id INTEGER);
CREATE TABLE b(id INTEGER);

> select * from sqlite_master;
type        name        tbl_name    rootpage    sql
----------  ----------  ----------  ----------  --------------------------
table       a           a           2           CREATE TABLE a(id INTEGER)
table       b           b           3           CREATE TABLE b(id INTEGER)

> DROP TABLE a;
> CREATE TABLE a(id INTEGER);
> .dump

CREATE TABLE b(id INTEGER); -- REVERSE of previous ordering
CREATE TABLE a(id INTEGER);

> select * from sqlite_master;
type        name        tbl_name    rootpage    sql
----------  ----------  ----------  ----------  --------------------------
table       b           b           3           CREATE TABLE b(id INTEGER)
table       a           a           2           CREATE TABLE a(id INTEGER)

I store some SQLite databases in Git using a filter that calls .dump. If I happen to change a table or view definition the resulting diff is unecessarily large and confusing because of the change in output order.

Would it be possible to modify .dump (and .schema) to produce a consistent output ordering?

Thanks.

2020-04-20
05:51 Reply: Is there a non-trigger way to format data on INSERT/UPDATE? (artifact: f89515ee00 user: mark)

If space is not an issue then on recent versions of SQLite you could use generated columns[1] to achieve the equivalent.

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

More ↓