SQLite Forum

Timeline
Login

50 most recent forum posts by user hanche

2021-11-26
21:20 Reply: Существует ли справочник синтетических ошибок?Где искать исправления error near? (artifact: dfc92ea200 user: hanche)

I think that is what he was showing: He tried to execute sql code from a file simply by entering the filename from the interactive prompt.

To the OP: You might try .read c:/sqlite/my1st.sql instead.

2021-11-16
19:17 Reply: How to address a variable within a WITH statement (artifact: e1761e5eed user: hanche)

I imagine something along the lines of

WITH
  pi (projid) AS (VALUES('PR0000019191')),
  LastEntries(projid,ml_insert)
  AS ( 
    SELECT projid max(insertdate)
    FROM pi
    JOIN Project_Keytask_and_Milestones
    USING(projid)
  )
SELECT ...;

might do the trick?

2021-10-28
16:09 Edit reply: --skip option does not work correctly. (artifact: cc0732b51a user: hanche)

I ran a little experiment. There is indeed an anomaly there:

▶▶▶ .shell cat a.csv
,,,
1,2,4,8
42,,,
,9,3,1
▶▶▶ create table a(x,y,z,w);
▶▶▶ .import -csv -skip 1 a.csv a
a.csv:1: expected 4 columns but found 3 - filling the rest with NULL
▶▶▶ .nullvalue ø
▶▶▶ select * from a;
x   y  z  w
--  -  -  -
          ø
1   2  4  8
42
    9  3  1

Notice that the first line got imported with three blanks and a null.

If I add an extra character to the first line, either a comma or something else, the error goes away.

Additionally, the error is still there if I replace the first line with ,a,b,c. So it appears to be justy the initial comma that is problematic.

16:06 Reply: --skip option does not work correctly. (artifact: 2fdd8b114f user: hanche)

I ran a little experiment. There is indeed an anomaly there:

▶▶▶ .shell cat a.csv
,,,
1,2,4,8
42,,,
,9,3,1
▶▶▶ create table a(x,y,z,w);
▶▶▶ .import -csv -skip 1 a.csv a
a.csv:1: expected 4 columns but found 3 - filling the rest with NULL
▶▶▶ .nullvalue ø
▶▶▶ select * from a;
x   y  z  w
--  -  -  -
          ø
1   2  4  8
42
    9  3  1

Notice that the first line got imported with three blanks and a null.

If I add an extra character to the first line, either a comma or something else, the error goes away.

2021-10-15
16:41 Reply: Week numbers in queries (artifact: cb8923cb4d user: hanche)

The patch I posted here was broken by the refactoring in checkin e548e9299d. Here is a new patch, in case anyone is interested.

Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -989,17 +989,20 @@
 **
 ** Return a string described by FORMAT.  Conversions as follows:
 **
 **   %d  day of month
 **   %f  ** fractional seconds  SS.SSS
+**   %G  year 0000-9999 of current week's Thursday
 **   %H  hour 00-24
 **   %j  day of year 000-366
 **   %J  ** julian day number
 **   %m  month 01-12
 **   %M  minute 00-59
 **   %s  seconds since 1970-01-01
 **   %S  seconds 00-59
+**   %u  day of week 1-7  monday==1
+**   %V  week of year 01-53  week belongs to year with most of its days
 **   %w  day of week 0-6  sunday==0
 **   %W  week of year 00-53
 **   %Y  year 0000-9999
 **   %%  %
 */
@@ -1079,15 +1082,44 @@
         break;
       }
       case 'S': {
         sqlite3_str_appendf(&sRes,"%02d",(int)x.s);
         break;
+      }
+      case 'u': {
+        sqlite3_str_appendchar(&sRes, 1,
+                       (char)(((x.iJD+43200000)/86400000) % 7) + '1');
+        break;
       }
       case 'w': {
         sqlite3_str_appendchar(&sRes, 1,
                        (char)(((x.iJD+129600000)/86400000) % 7) + '0');
         break;
+      }
+      case 'V':  /* Fall thru */
+      case 'G': {
+        DateTime y = x;
+        computeJD(&y);
+        y.validYMD = 0;
+        /* Adjust date to Thursday this week:
+           The number in parentheses is 0 for Monday, 3 for Thursday */
+        y.iJD += (3 - (((y.iJD+43200000)/86400000) % 7))*86400000;
+        computeYMD(&y);
+        if( zFmt[i]=='G' ){
+          sqlite3_str_appendf(&sRes,"%04d",y.Y);
+        }else{
+          /* borrowed code from case 'j' */
+          sqlite3_int64 tJD = y.iJD;
+          int nDay;
+          y.validJD = 0;
+          y.M = 1;
+          y.D = 1;
+          computeJD(&y);
+          nDay = (int)((tJD-y.iJD+43200000)/86400000);
+          sqlite3_str_appendf(&sRes,"%02d",nDay/7+1);
+        }
+        break;
       }
       case 'Y': {
         sqlite3_str_appendf(&sRes,"%04d",x.Y);
         break;
       }

2021-10-03
18:39 Reply: Strange parsing problem in the shell (artifact: b08a53de1a user: hanche)

That seems to have done the trick! (And a one line fix, to boot.)

2021-10-02
09:55 Edit reply: Strange parsing problem in the shell (artifact: c0671bd90a user: hanche)

I have been bisecting a bit, using my last example in the quoted post:

bisect complete
  1 BAD     2021-09-22 14:26:22 96610cc8251ad4ff
  3 BAD     2021-09-21 20:03:05 4a3f98ad0dd2a8c0
  5 BAD     2021-09-21 19:19:28 a1c7f7f8e1b46440 CURRENT
  4 GOOD    2021-09-21 17:26:23 2c0e7ae541e9ecb8
  2 GOOD    2021-09-18 16:15:54 99d6bb22e8735681

From my brief reading of the code, I can't for the life of me see where the number of escaped double quotes in the file argument can have any impact.

Replacing \" by \042 does seem to help, though.

09:51 Reply: Strange parsing problem in the shell (artifact: 54aec8e636 user: hanche)

I have been bisecting a bit, using my last example in the quoted post:

bisect complete
  1 BAD     2021-09-22 14:26:22 96610cc8251ad4ff
  3 BAD     2021-09-21 20:03:05 4a3f98ad0dd2a8c0
  5 BAD     2021-09-21 19:19:28 a1c7f7f8e1b46440 CURRENT
  4 GOOD    2021-09-21 17:26:23 2c0e7ae541e9ecb8
  2 GOOD    2021-09-18 16:15:54 99d6bb22e8735681

From my brief reading of the code, I can't for the life of me see where the number of escaped double quotes in the file argument can have any impact.

07:56 Edit reply: Strange parsing problem in the shell (artifact: 1e35a873e3 user: hanche)

Thanks; I will experiment a bit further. Just one quick note, though: tail -r works fine on macOS. It does not on linux, which I assume is where you ran your experiment. On (most? all?) linux system you have the tac command that does the same thing: It collects lines from stdin and outputs them in reverse order.

So no, I don't buy the hypothesis that my problem arises from a failed pipeline.

I cooked up a simpler example. I think it shows more clearly that the problem does not arise from the complexity of the pipeline nor from any error in the pipeline, but from the presence of the backslash-escaped double quote.

⬥ cat tt.csv
,irrelevant,
"2021-10-02",some,data
,also,irrelevant
⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> create table tt(date,x,y);
sqlite> .import -csv "|grep '^\"2' tt.csv" tt
sqlite> select 42;
   ...>

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> select * from tt;
2021-10-02|some|data
sqlite>

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> .import -csv "|grep '^.2' tt.csv" tt
sqlite> select * from tt;
2021-10-02|some|data
2021-10-02|some|data
sqlite>

Edited to add an even simpler example. It seems that an odd number of occurrences of \" within the double quoted argument is the crux here:

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> drop table tt;
sqlite> create table tt(x);
sqlite> .import -csv "|echo '\"hi there\"'" tt
sqlite> select * from tt;
hi there
sqlite> .import -csv "|echo '\"hi there\"' | tr -d '\"'" tt
sqlite> select * from tt;
   ...>
07:40 Edit reply: Strange parsing problem in the shell (artifact: ad707451d6 user: hanche)

Thanks; I will experiment a bit further. Just one quick note, though: tail -r works fine on macOS. It does not on linux, which I assume is where you ran your experiment. On (most? all?) linux system you have the tac command that does the same thing: It collects lines from stdin and outputs them in reverse order.

So no, I don't buy the hypothesis that my problem arises from a failed pipeline.

I cooked up a simpler example. I think it shows more clearly that the problem does not arise from the complexity of the pipeline nor from any error in the pipeline, but from the presence of the backslash-escaped double quote.

⬥ cat tt.csv
,irrelevant,
"2021-10-02",some,data
,also,irrelevant
⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> create table tt(date,x,y);
sqlite> .import -csv "|grep '^\"2' tt.csv" tt
sqlite> select 42;
   ...>

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> select * from tt;
2021-10-02|some|data
sqlite>

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> .import -csv "|grep '^.2' tt.csv" tt
sqlite> select * from tt;
2021-10-02|some|data
2021-10-02|some|data
sqlite>
07:12 Reply: Strange parsing problem in the shell (artifact: aaf7622636 user: hanche)

Thanks; I will experiment a bit further. Just one quick note, though: tail -r works fine on macOS. It does not on linux, which I assume is where you ran your experiment. On (most? all?) linux system you have the tac command that does the same thing: It collects lines from stdin and outputs them in reverse order.

So no, I don't buy the hypothesis that my problem arises from a failed pipeline. But as I said, I will experiment some more and report back.

2021-10-01
17:58 Edit reply: Strange parsing problem in the shell (artifact: 83d2663fdc user: hanche)

(I expect you need to double that backslash.)

The backslash is there to escape the double quote, so that the resulting argument following -csv will be

|iconv -f l1 < trans.csv | grep '^"20' | tail -r

That string, in its turn, should be parsed by /bin/sh (with the initial | removed) so that the argument that grep sees is

^"20

meaning a line beginning with the three characters "20.

And indeed, the data winding up in the database file is consistent with my expectations, so I think I got the number of backslashes right.

17:56 Reply: Strange parsing problem in the shell (artifact: 845cd2f011 user: hanche)

(I expect you need to double that backslash.)

The backslash is there to escape the double quote, so that the resulting argument following -csv will be

|iconv -f l1 < trans.csv | grep '^"20' | tail -r

That string, in its turn, should be parsed by /bin/sh so that the argument that grep sees is

^"20

meaning a line beginning with the three characters "20.

And indeed, the data winding up in the database file is consistent with my expectations, so I think I got the number of backslashes right.

15:28 Post: Strange parsing problem in the shell (artifact: 79f01df29f user: hanche)

This is a bit odd:

⬥ sqlite3 foo.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite>
sqlite> .import -csv "|iconv -f l1 < trans.csv  | grep '^\"20' | tail -r" incoming
sqlite> select 42;
   ...>

That's a secondary prompt string, indicating that the shell is waiting for more input. I haven't found any way out of it other than to hit Crrl-D, exiting the shell.

Interestingly, when I reenter the shell, I find that the import has completed successfully.

Is it the complicated form of the .import line that throws it off? But if so, why does it present a primary prompt after that line? Isn't the parser completely reset after each line that it parses successfully?

I could add that incoming is a view with an INSTEAD OF INSERT trigger. That ought to be irrelevant; but I suppose I could try with a copy of the database in which incoming has been replaced by a regular table.

2021-09-28
16:16 Edit reply: User-defined functions in the CLI? (artifact: fd466eeb15 user: hanche)

That just loads SQL code. And SQLite dot commands, I think. But it can't do what you can't do from the command line.

Edit: Oops, that was mistaken. Sorry.

11:24 Reply: User-defined functions in the CLI? (artifact: 33a5985931 user: hanche)

That just loads SQL code. And SQLite dot commands, I think. But it can't do what you can't do from the command line.

2021-09-20
08:29 Reply: adding record if it doesn't already exist (artifact: 01d741e928 user: hanche)

You could add a RETURNING clause at the end.

2021-09-15
06:53 Reply: DELETE FROM … RETURNING (artifact: c17a97d6f9 user: hanche)

If we could use a DELETE statement as a CTE¹, you could sort it thus:

WITH deleted AS (DELETE FROM foo WHERE condition RETURNING stuff)
SELECT * FROM deleted ORDER BY something;

¹ This capability is on my wishlist, but I am not holding my breath waiting for it. ;-)

2021-09-14
06:57 Reply: Retrieve a record and delete it (artifact: 0fb36361b0 user: hanche)

May I also point out that the DELETE statement allows a RETURNING clause? I find it quite useful.

Tangentially, PostgreSQL allows DELETE statements with a RETUNING clause as a common table expression to be used in a WITH clause. I use it sometimes to move data between tables. Possibly, SQLite users could benefit from the same feature.

2021-09-13
10:02 Reply: Wishing CLI could be usefully embedded (artifact: 1e97be257f user: hanche)

What on earth would anyone want to "embed" it in another application for?

I have no wish to do so myself, but I can't resist pointing out that fossil's sql subcommand appears to do just that.

2021-08-24
07:15 Edit reply: When is "IS" used in SQLite? (artifact: 0497641c3e user: hanche)

Given the fact that SQLite is old enough to drink legally everywhere in the US (I think), you should perhaps not be too surprised.

PS. The question reminds me of Bill Clinton, during his impeachment process I believe: It depends what the meaning of “is” is.

07:10 Reply: When is "IS" used in SQLite? (artifact: d89fdb0953 user: hanche)

Given the fact that SQLite is old enough to drink legally everywhere in the US (I think), you should perhaps not be too surprised.

2021-08-13
16:52 Reply: Retrieve list of tables (artifact: 92d423e273 user: hanche)

I'm not sure whether internal tables always start with "sqlite_".

I am pretty sure I have seen that documented. Moreover, you are not allowed to create a table whose name starts with sqlite_ yourself; you get an error message if you try.

How about temporary tables?

They are listed in temp.sqlite_master.

16:33 Reply: Calculating Trip Duration in Minutes? (artifact: 7f1dd0f637 user: hanche)

If you did read the whole discussion thread here, you should be aware that this is already known to the participants. The statement is true, however, about the convenient fiction that is sometimes known as POSIX time, as well as the timescale used by SQLite itself. That these timescales ignore leap seconds is well known already.

07:52 Reply: LEFT AND RIGHT OUTER JOINS problem (artifact: ce5e425872 user: hanche)

Another way is to run .mode insert, then run SELECT statements to get a representative sample. Also, better run .schema and post the needed bits (avoiding any tables and views not relevant to the question). Try your best to keep the size of the resulting post down to the bare minimum.

2021-08-07
06:07 Reply: SQLite3.exe - How to use arguments AND remain in interactive mode? (artifact: 923f6b12ec user: hanche)

Indeed, but since that is intended for all your SQLite sessions, it could be undesirable to use it for ad hoc purposes.

2021-08-06
13:14 Reply: Fail to calculate long expression (artifact: d7cced262d user: hanche)

That could fail with integers too large to be exactly represented with doubles, however. Numbers can be hard to work with.

10:34 Reply: Select and count from 2 tables (artifact: 6cedd672f6 user: hanche)

I didn't try too hard decoding your pictures, but I note that the expression

genre_name = 'Romance' AND genre_name = 'Comedy'

will never be true. Perhaps you wanted to use OR instead? Or simpler:

genre_name IN ('Romance','Comedy')
10:26 Reply: SQLite3.exe - How to use arguments AND remain in interactive mode? (artifact: 7c137e0c44 user: hanche)

That can't be done, as far as I know.

One workaround is to put the sql commands (and/or dot commands) into a file, then specify that file with the -init flag to the command shell.

2021-08-05
08:57 Reply: CAST type-name is optional (artifact: a39ff88674 user: hanche)

Poking around in the grammar at src/parse.y, I find the entry for CAST as follows:

expr(A) ::= CAST LP expr(E) AS typetoken(T) RP. {
  A = sqlite3ExprAlloc(pParse->db, TK_CAST, &T, 1);
  sqlite3ExprAttachSubtrees(pParse->db, A, E, 0);
}

and looking for typetoken in the same file, there is this illuminating comment:

// A typetoken is really zero or more tokens that form a type name such
// as can be found after the column name in a CREATE TABLE statement.
// Multiple tokens are concatenated to form the value of the typetoken.

To emphasise, zero or more tokens. This makes good sense in the context of CREATE TABLE, less so in the case of a CAST expression.

I conjecture that this inadvertently legalised a CAST expression with no type designation. It certainly appears to be undocumented.

Perhaps the grammar should be tightened to disallow this; but if that cannot (or should not) be done for the sake of backward compatibility, I think it ought to remain undocumented, so as not to trick users into relying on it. Or, it could be documented, with a caveat that the resulting behaviour is undefined. (It seems to cast to numeric at the moment.) In either case, I think it had better be kept out of the syntax diagram.

Just my three cents' worth – inflation, y´know.

2021-08-03
10:45 Reply: Fail to calculate long expression (artifact: faa15478d0 user: hanche)

Read it again. It's not the result that is truncated, it's the operands, before the modulo operation happens. So SQLite computes 8 mod 0, which becomes a NULL.

As to why it does that, it is probably a decision made such a long time ago nobody remembers anymore. It can't easily be changed now, for backwards compatibility. I suppose such a change could be made, with a suitable pragma to turn on the new behaviour, but then you had to make a strong case for that.

2021-07-26
20:41 Edit reply: How do I do a one - many join? (artifact: 9e6bd7be9d user: hanche)

AND WHERE is a syntax error. Replace by AND.

Anyhow, I think your query is better written as a join:

SELECT s.station_name, s.start_lat, s.start_lng, started_at, ended_at, duration
FROM stations AS s
JOIN detail AS d
ON (s.start_lat = d.start_lat AND s.end_lat = d.ended_at);
20:40 Reply: How do I do a one - many join? (artifact: 771b3e9b95 user: hanche)

AND WHERE is a syntax error. Replace by AND.

Anyhow, I think your query is better written as a join:

SELECT s.station_name, s.start_lat ,s.start_lng, started_at, ended_at, duration
FROM stations AS s
JOIN detail AS d
ON (s.start_lat = d.start_lat AND s.end_lat = d.ended_at);
2021-07-22
13:01 Edit reply: How to submit changes (artifact: 41d35ba092 user: hanche)

Look at https://sqlite.org/copyright.html for some pointers

If it's about spelling mistakes in the documentation, I imagine they are not so strict about not accepting patches. Perhaps you could simply post a diff to the forum, if it is not too large.

13:00 Reply: How to submit changes (artifact: f82c368f2c user: hanche)

Look at https://sqlite.org/copyright.html for some pointers

If it's about spelling mistakes in the documentation, I imagine they are not so strict about not accepting patches. Perhaps you could simply post a diff to the forum, if it is not too large.

2021-07-20
21:10 Reply: Calculating duration in ISO8601 timestamp (artifact: d4c8ef3851 user: hanche)

Your input format is wrong: You need a decimal point, not a colon, between the seconds and the fractional seconds.

▶▶▶ create table foo(datetime text);
▶▶▶ insert into foo values ('2021-07-19 08:43:46.956'),('2021-07-20 10:26:41.357');
▶▶▶ select julianday(max(datetime))-julianday(min(datetime)) from foo;
julianday(max(datetime))-julianday(min(datetime))
-------------------------------------------------
1.07146297441795
2021-07-15
10:46 Reply: Help with a query (artifact: 06b0fa9016 user: hanche)

Quick tip: Assuming you're doing this in the SQLite shell, if you run .mode insert, then subsequent SELECTs will produce INSERT statements that others can use to recreate the data. That will make it much easier for them to experiment.

2021-07-14
07:17 Reply: My Database is being deleted after each restart of my code. How can I fix it? (artifact: 563589a8b5 user: hanche)

I can play the guessing game too: A transaction was not closed before exiting. I think some language bindings (python?) open a transaction automatically, but leaves it up to the user to commit it.

2021-07-08
20:16 Reply: edit() function usage (artifact: 1da2477b5a user: hanche)

You're probably onto something there. BBEDIT is a GUI application, whereas the edit() command in SQLite expects a path to an executable which will start an editor, and exit when the user is done. Starting a GUI Application on a Mac is a rather complicated affair involving more than a simple fork(), but the principle remains: The process activating the GUI application will usually continue as soon as it has triggered the activation of the application.

If you do this in the interactive SQLite shell, you would probably see the shell prompt immediately after invoking the edit.

For example, I don't have a copy of BBEDIT, but I tried an experiment using an editor called CotEditor (available from the App Store). CotEditor comes with an associated command line utility simply called cot, which will start CotEditor on a given file. It accepts a flag -w, which causes cot to wait until CotEditor has closed the file.

My little experiment proceeded as follows:

▶▶▶ create table foo(bar);
▶▶▶ insert into foo values("blah");
▶▶▶ select rowid,bar from foo;
rowid  bar
-----  ----
1      blah
▶▶▶ update foo set bar=edit(bar,'cot') where rowid=1;
-- note: the prompt below appeared immediately,
-- before I had time to do any editing; so, no change
▶▶▶ update foo set bar=edit(bar,'cot -w') where rowid=1;
-- here, nothing happened until I had saved and closed the temporary file
▶▶▶ select rowid,bar from foo;
rowid  bar
-----  ------
1      blargh
-- success!

If you wish this to work with BBEDIT, you need a similar tool. I don't know if BBEDIT provides such a tool.

2021-07-06
15:27 Reply: row value misuse (artifact: d2bc1bf7ad user: hanche)

Ah, that explains it.

I do wish for more context in SQLite error messages sometimes. I mean, just adding a few characters, say “near "("k","” would be a lot more helpful than “near "("”.

13:31 Reply: row value misuse (artifact: 6cb4fbeb3f user: hanche)

I am not an expert or authority, but here is my understanding of the issue, as far as it goes. If I am wildly off target, someone will hopefully correct me.

  • A comma-separated list of expressions in parenthesis usually signifies a row value, or tuple.
  • A VALUES expression is a special select statement, and as such produces a sequence of rows. Even if that sequence contains precisely one row, it is something different from a row value.
  • A row value can contain any of the datatypes allowed by SQLite. Note that a row value is not among these; hence ((1,2,),(3,4)) is illegal.

The following seems to work in Postgres, but Sqlite is reporting "row value misused":

select * from kv
where (key, val) in (('k1', 1), ('k3', 3));

That is not so clear-cut. What's on the right side of WHERE … IN (…) is not an expression but a parenthesised list of expressions, so arguably, a list of row values could have been legitimate according to these rules. Perhaps it would be useful; but OTOH, the alternative to a list of expressions is a select statement, and VALUES is of course a select statement, so that will always work.

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near "(":

select * 
from kv 
inner join (values ('k1', 1), ('k3', 3)) as bar("k", "v")
  on kv.key = bar.k and kv.val = bar.v;

I am not sure why that does not work in SQLite. The syntax diagrams seem to allow it.

2021-06-30
21:13 Edit reply: Duplicated rows (and missing rows) when SELECTing with ORDER BY (artifact: 2345d136e4 user: hanche)

Assuming the database file is corrupted, as seems likely (see the answer by David Raymond), you may be able to recover it using the .recover command of the sqlite shell:

sqlite3 original.db .recover | sqlite3 new.db

Run .help recover in the interactive shell to see some possibly useful options.

But after any database corruption, you should view the recovered data with some skepticism.

21:09 Reply: Duplicated rows (and missing rows) when SELECTing with ORDER BY (artifact: f6d3273fb1 user: hanche)

Assuming the database file is corrupted, as seems likely (see the answer by David Raymond), you may be able to recover it using the .recover command of the sqlite shell:

sqlite3 original.db .recover | sqlite3 new.db

But after any database corruption, you should view the recovered data with some skepticism.

2021-05-28
07:52 Reply: Column name includes quotes used in RETURNING expr (artifact: 12b5f41297 user: hanche)

SQLite does allow some syntax for identifiers that is not standard SQL, such as backticks or square brackets for quoting identifiers.

2021-05-27
17:44 Reply: Update table from SQLite3 queries results. (artifact: 3705e447eb user: hanche)

If there are views and triggers and whatnot attached to the table, that might not work so well. In which case, make Table2 temporary, and after populating it,

BEGIN;
DELETE FROM Table1;
INSERT INTO Table1 SELECT * FROM Table2;
END;

The transaction is just to recover in case of a crash or power loss.

14:21 Reply: Is there a way to optimize this UPDATE command to run faster? (artifact: 4e0c9653b7 user: hanche)

I have no idea, but would just point out that the table lacks the chainer_id column that appears in your query.

2021-05-16
09:48 Reply: Nearest-match join (artifact: 0766947fad user: hanche)

If a solution can be found that includes one row for each “nearest” timestamp, then those questions can easily be resolved by applying more filtering to the output, I think. In other words, those are questions that must be asked for any practical application, but they don't really influence the search for a solution much.

09:44 Reply: Columns metadata is invalit for RETURNING clause (artifact: 4afd278888 user: hanche)

My two cents' worth: I think you'd be wise to avoid relying on this feature being implemented soon, if at all. If they plan to implement it for free, they are unlikely to say so before the feature has landed; doing otherwise invites a possibly endless stream of requests for ETAs, with attendant followups when the ETA has passed and the feature isn't implemented yet, effectively turning the ETA into a deadline.

Not that there is anything wrong with asking for features. I've done it myself. Just don't be disappointed if the requested feature doesn't materialise, or it takes much longer than desired to do so.

If the feature truly is important to you, you might consider paying to get it done. See the support page.

2021-05-15
17:22 Reply: Using json_extract in create table for generated fields (artifact: 688b51826e user: hanche)

You can declare a UNIQUE constraint on a generated field. I believe that results in the creation of an index. Perhaps that is all you need?

Here is another possibility: Have a separate VIEW with a trigger to do your insertion into.

CREATE TABLE t1(
  id VARCHAR(64) PRIMARY KEY,
  myjson TEXT);

CREATE VIEW t1j AS SELECT json_insert(myjson,'$.id',id) myjson FROM t1;

CREATE TRIGGER t1_ins
INSTEAD OF INSERT ON t1j BEGIN
  INSERT INTO t1(id,myjson)
  VALUES (json_extract(NEW.myjson,'$.id'),json_remove(NEW.myjson,'$.id'));
END;

INSERT INTO t1j VALUES
  ('{"id":"foo","x":42}'),
  ('{"id":"bar","x":99}');

SELECT * FROM t1;

with the result (assuming column mode)

id   myjson
---  --------
foo  {"x":42}
bar  {"x":99}

I did a bit of extra work to avoid duplicating the id in the database. The view puts it back.

2021-05-13
11:47 Reply: How to convert am/pm time to 24hrs time format (artifact: d7f298f383 user: hanche)

"11:59:59 am {tick} 12:00:00 am {tick} 12:00:01 pm" hurts my OCD.

Same here. To put it in other words, the visual change from 11:59:59 to 12:00:00 is so much greater than the change from 12:00:00 to 12:00:01 (visually and using the Hamming distance), it is only reasonable that the am/pm change should happen at the same time.

Until 2019 ISO 8601 allowed using 24:00:00 to refer to the end of the day but now that is forbidden.

I wasn't aware of that change. It makes sense, though, at least when you wish for machine readable time strings. I might still want to use it for human consumption, though.

I have also noticed that in the US, any insurance contract will start at 12:01 am, in order to avoid ambiguity. It would not help to say 12:00 midnight, since the question is still open whether that is the midnight before or after the stated date. But a slightly disconcerting side effect is that, if you switch insurance providers, your house will be uninsured for one minute after midnight. Better hope it is not struck by lightning at that moment.

More ↓