SQLite Forum

Timeline
Login

34 forum posts by user hanche occurring on or before 2021-05-06 11:06:39.

More ↑
2021-05-06
11:06 Reply: order of INSERT affects the output (artifact: ec95766a77 user: hanche)

I don't know why there could be any criticism because imho it has absolutely no negative impact.

That is not at clear. In fact, Postel's law itself has been criticised because being liberal in what you accept can allow errors to go undetected, with possible data corruption or worse (security holes, in the case of the Internet) as a consequence.

I wish it were called Postel's guideline instead of a law.

And to be clear, I have nothing against the deviation from the standard discussed in this thread. But I would be surprised to learn it had never happened that someone, somewhere, got bitten by it. Hopefully not too seriously.

10:55 Reply: Is CSV parsing too liberal? (artifact: 094b374050 user: hanche)

This is getting a bit far afield for the sqlite forum perhaps, but I have yet to come across a version of sed that removes a BOM automatically. Certainly not on the mac (not even GNU sed from macports), and not on ubuntu 20.04.

And on a side note, there may have been good reasons for BOMs in the past, but I think that time is long gone. It's just a hack, it causes no end of trouble, and it needs to go. The Unicode consortium already did their part, by removing the name BYTE ORDER MARK from the code point.

2021-05-05
16:09 Reply: SQLite Plus: all the missing SQLite functions (artifact: e947716902 user: hanche)

Since this thread popped up once more, it could be good to notice that the project owner did change the name. It is now called sqlean.

2021-05-04
18:07 Reply: Is CSV parsing too liberal? (artifact: 44fdf2cb0c user: hanche)

Adding a BOM stripper is precisely what I did. One, convert Latin-1 to UTF-8, two, strip the BOM, three, reverse the order of the lines, and the file is ready for import.

if BOMs are supposed to be ignored at the beginning of files

Depends who is doing the supposing. Ignoring a BOM at the beginning of a file is not a universal or very widespread convention, except perhaps in the Windows world. (Did I contradict myself just now?) I can't say I blame tail for not including code for it. As far as it is concerned, it was just another odd character at the beginning of the line. If the line moves, so does the character.

15:02 Reply: Is CSV parsing too liberal? (artifact: fc0abe9802 user: hanche)

Right. Learning: check; sucking it up: working on it! And for the record, I hate the BOM. It just makes life difficult for everyone. Side note: I posted this not primarily for my own benefit, but for others who might run into the same sort of issue. So if it's not going to get fixed, it's all the same to me.

13:10 Reply: Is CSV parsing too liberal? (artifact: 2cf97ed870 user: hanche)

The SQLite Shell is really just a convenience tool and isn't supposed to be all things for all users.

I appreciate that, but I still think a warning (not an error) would be useful here, since this sort of thing could so easily lead to data corruption. Just like when the number of fields does not match the number of table columns.

It's not a big deal for me, though. Burned once, I have learned that lesson. But I did think it worth mentioning.

11:29 Post: Is CSV parsing too liberal? (artifact: 7df1832eff user: hanche)

Consider this transcript:

▶▶▶ create table foo(u,v);
▶▶▶ .shell cat foo.csv
"a b",c
x"d e",f
▶▶▶ .import -csv foo.csv foo
▶▶▶ select * from foo;
u       v
------  -
a b     c
x"d e"  f

Here the second line of the csv file is illegal according to RFC4180. I question whether the sqlite shell should raise an error, or at least issue a warning, when encountering such.

How I got bitten by this? I had a csv file that I wanted to import in reverse order. So I basically did

.import -csv '|tail -r foo.csv'

(On macOS, tail -r prints the file with the order of the lines reversed. On linux, use tac instead.)

I could not for the life of me understand why the first field of the last record included the quotes – until, after wasting much time, I realised that the csv file started with the infamous U+FEFF ZERO WIDTH NO-BREAK SPACE, also known as the byte order mark (BOM). And upon reversing the order of the lines, that was now heading the final line, where it was no longer ignored.

2021-05-03
16:14 Reply: insert with returning question (artifact: 96b0dc20ba user: hanche)

I don't get the behaviour you describe from sqlite.

Since your table has no primary keys or unique constraints, INSERT will always insert a new row, and the RETURNING clause will indeed give you the new rowid.

If you made the a column, say, a primary key, and use INSERT OR REPLACE, you also get rows inserted and rowids returned.

So perhaps you are thinking of INSERT OR IGNORE, and you would like a single sql command INSERTing og IGNOREing, respectively, but returning a rowid in either case? Honestly, I don't know if you can. But can you tell us whether my interpretation is correct, or I have misunderstood the question completely?

2021-05-01
17:45 Reply: Beginner's problem with left join. (artifact: 148aa86490 user: hanche)

NULL values are not equal to anything, not even to other NULL values.

Replace b.var6=NULL by b.var6 IS NULL.

(There are also comparison operators IS DISTINCT FROM and IS NOT DISTINCT FROM that can tell the difference between null and non-null values. But that is getting too verbose, when you already know that you want to compare with NULL.)

08:17 Reply: classic save possibility (artifact: 469e9114b4 user: hanche)

Could you perhaps use savepoints instead? They do nest, where ordinary transactions do not.

Just beware that you cannot use BEGINEND inside another transaction, even if it is the savepoint type.

2021-04-25
13:03 Reply: Sending output to a pipe (artifact: 4d3967145e user: hanche)

I agree that it is not reasonable to expect .output to behave any differently than it currently does; that would be way too complicated. The thing is, I don't think I have ever used .output, but I use .once regularly. And when you don't connect the two commands in your head, it is very easy to fall into the trap of assuming that .once will wait before actually running the named command. Clearly, that is what happened to me. (I do know about pipes and what they do; so here is at least one data point suggesting it is not evident.)

Here is one suggestion for an addition to the documentation:

The given command is started immediately. Beware of specifying a command that will try to interact with the terminal.

This should provide sufficient warning for knowledgeable users. But perhaps it is too cryptic for less sophisticated users.

2021-04-24
14:49 Reply: Sending output to a pipe (artifact: d779782557 user: hanche)

Indeed, I know and expect that less does all that, but it would not have been a problem if the sqlite shell would wait until there is some output to display before invoking the external command. It would also have to wait for the command to finish before printing a new prompt and trying to read user input once more.

But never mind: Now that I know it is not expected to work, I can find other ways.

I imagine others might expect the same behaviour I did, though, so perhaps it could deserve a mention in the documentation.

12:56 Post: Sending output to a pipe (artifact: 9b3b29110c user: hanche)

It has clearly been a while since I tried this, so I was quite surprised when

.once |less

fired up the less command immediately, rendering me unable to execute the sql command I had intended to. I had expected sqlite to wait for me to type in a query, then execute the query, and finally firing up the command and piping the query output into it.

Am I doing this wrong?

2021-04-06
09:10 Reply: Compiling sqlite3 with readline support (artifact: 024506c506 user: hanche)

Thanks, but I already figured it out. I don't know why the distributed configure script works for you but not for me, though. I ended up editing the configure script (patch at the end of this message). Then I had to set LDFLAGS=-L/opt/local/lib in the environment before running

../configure --prefix=/some-irrelevant-path --disable-editline --enable-readline --disable-tcl
make install

(disabling tcl because otherwise, it tries to install some tcl stuff in /opt/local). One problem that I encountered along the way is the system provided /usr/lib/libreadline.dylib, which is a symlink to libedit.3.dylib.

Now the shell works as it should.

But editing the configure script? I feel dirty. Someday, maybe I'll learn enough about autoconf to diagnose the problem better. But that day is not today.

--- configure
+++ configure
@@ -8691,12 +8691,12 @@
   soname_spec='${libname}${release}${major}$shared_ext'
   shlibpath_overrides_runpath=yes
   shlibpath_var=DYLD_LIBRARY_PATH
   shrext_cmds='`test .$module = .yes && echo .so || echo .dylib`'

-  sys_lib_search_path_spec="$sys_lib_search_path_spec /usr/local/lib"
-  sys_lib_dlsearch_path_spec='/usr/local/lib /lib /usr/lib'
+  sys_lib_search_path_spec="$sys_lib_search_path_spec /opt/local/lib"
+  sys_lib_dlsearch_path_spec='/opt/local/lib /lib /usr/lib'
   ;;

 dgux*)
   version_type=linux
   need_lib_prefix=no
@@ -11128,11 +11128,11 @@
   found="yes"
 else

 			found="no"
 			if test "$cross_compiling" != yes; then
-				for dir in /usr /usr/local /usr/local/readline /usr/contrib /mingw; do
+				for dir in /usr /opt/local /opt/local/readline /usr/contrib /mingw; do
 					for subdir in include include/readline; do
 						as_ac_File=`$as_echo "ac_cv_file_$dir/$subdir/readline.h" | $as_tr_sh`
 { $as_echo "$as_me:${as_lineno-$LINENO}: checking for $dir/$subdir/readline.h" >&5
 $as_echo_n "checking for $dir/$subdir/readline.h... " >&6; }
 if eval \${$as_ac_File+:} false; then :
08:04 Reply: Compiling sqlite3 with readline support (artifact: be524982fc user: hanche)

Er, I take that back: It builds, but it breaks history. I think what happens is that sqlite code thinks it is talking to editline, where in reality it is talking to readline.

I am resorting to editing the configure script now. I know this is wrong and should never be necessary, but I want to get to the bottom of this. I'll report back when I understand more.

2021-04-05
20:21 Reply: Compiling sqlite3 with readline support (artifact: 9048715201 user: hanche)

I finally made it happen! The key, so it seems, is to ignore the configure options and instead set LDFLAGS='-L/opt/local/lib -lreadline' in the environment, then configure and make.

During my experiments, I was looking in config.log and found that no matter what I did, the configure script did not include -lreadline when testing for the readline library, so of course the test failed. In particular, I found no effect from using the configure options --with-readline-lib and --with-readline-lib mentioned by ./configure --help.

Setting the environment did the trick, even though – amusingly enough – the compilation ran with -DHAVE_READLINE=0 -DHAVE_EDITLINE=1. But it works as I had expected, anyhow.

PS. One of my desperate attempts involved running autoupdate; autoconf, which of course changed the configure script somewhat. But that may have no bearing on the outcome; I just don't know.

PPS. I really dislike autoconf. Sure, it works wonders when it works, but when it doesn't, it is just about impossible for a mere mortal to diagnose the problem.

17:46 Reply: Compiling sqlite3 with readline support (artifact: 7df7e477a0 user: hanche)

Thanks. But readline-5? Well, I tried installing that port. The portfile still exists, but the port command failed to download the needed file after trying about a dozen sites. So it looks impossible to install the port.

I do have readline installed (it's version 8), but your configure line failed to pick it up.

Will sqlite3 not build with readline version 8?

16:46 Reply: Command Line Shell set parameter as date (artifact: 1f2f69774f user: hanche)

Already fixed before I wrote my previous reply, I notice. Very good. I should have pulled before running my experiment.

(But the documentation for how the arguments to dot commands are parsed could still be improved. See my reply above.)

14:16 Post: Compiling sqlite3 with readline support (artifact: c3bc736b94 user: hanche)

How can I use configure to compile sqlite3 with GNU readline? If I run ./configure --help, the relevant parts I see are these:

  --enable-editline       enable BSD editline support
  --disable-readline      disable readline support
  --with-readline-lib     specify readline library
  --with-readline-inc     specify readline include paths

I have tried every conceivable permutation of these, plus setting LD_FLAGS and CPP_FLAGS in the environment, and no matter what I try, sqlite3 is built either with editline support or neither readline nor editline support.

I much prefer GNU readline over editline. (Though editline is sort of okay.)

Any tips? I am on macOS, using macports, and I have GNU readline here:

/opt/local/lib/libreadline.dylib
/opt/local/include/readline/readline.h

If anyone on the list has successfully built sqlite3 on macOS with readline support, I'd like to hear how.

14:03 Reply: Command Line Shell set parameter as date (artifact: 6429638c79 user: hanche)

Honestly, I can't think of any reason to retain the current behaviour, nor how anybody could be relying on it. Sorry to disappoint. ;-)

Anyhow, I compiled a version patched as follows:

--- src/shell.c.in
+++ src/shell.c.in
@@ -2913,11 +2913,11 @@
   sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, -1, &wrSchema);
   sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, 1, 0);
   sqlite3_exec(p->db,
     "CREATE TABLE IF NOT EXISTS temp.sqlite_parameters(\n"
     "  key TEXT PRIMARY KEY,\n"
-    "  value ANY\n"
+    "  value\n"
     ") WITHOUT ROWID;",
     0, 0, 0);
   sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, wrSchema, 0);
   sqlite3_db_config(p->db, SQLITE_DBCONFIG_DEFENSIVE, defensiveMode, 0);
 }

And it does not show the anomaly.

Marginally related: Since I haven't found any documentation for how dot commands are interpreted, I consulted the source. As far as I can figure it out, the rules are as follows:

You may quote an argument with single or double quotes. If double quotes, backslash escapes are interpreted. If not quoted, the argument ends at the next space. If quoted, the quotes are stripped, and the argument is the result.

If the command is .parameters set, the value argument is first put verbatim into a REPLACE INTO sqlite_parameters statement. If that fails, the statement is run again, but now with the value argument quoted as a string. As a result the following dot commands will all insert the integer 7:

.param set :x 007
.param set :x '007'
.param set :x "007"

while the command

.param set :x "'007'"
will indeed insert the string '007' (assuming the patch above). This is pretty hard to guess, so it might be good to document it.

2021-04-03
20:13 Reply: Command Line Shell set parameter as date (artifact: cce8a228de user: hanche)

I couldn't help experimenting a little:

▶▶▶ .param init
▶▶▶ .param set :bond "'007'"
▶▶▶ .param list
:bond 7

Uh-oh. What happened to the double-oh in double-oh seven? Well, it seems the answer lies here:

▶▶▶ .schema sqlite_parameters
CREATE TABLE temp.sqlite_parameters(
  key TEXT PRIMARY KEY,
  value ANY
) WITHOUT ROWID;

Which raises the question: Why was the type of the value here specified as ANY, which by the rules given in section 3.1 of the Datatypes doc means it has INTEGER affinity? Was it intentional?

2021-04-01
08:35 Reply: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: 51ee6364ba user: hanche)

We're getting a bit far afield for a sqlite mailing list, perhaps, but anyhow:

Yes, this will work, with the caveat that NUL bytes (0x00) in the original binary will be stored as NUL (0x00) in the resulting UTF-8 encoding, and so you absolutely cannot pass this through code such as the standard C string routines that assume strings are NUL terminated.

You could, in principle, get around that by encoding NUL bytes instead using a different code points such as 0x100 (the letter Ā, letter A with macron), and undoing this on the receiving end before converting back to binary. Either that, or simply ensure that the string is not touched by anything treating NUL as a terminator. In C, that means using a character array and keeping track of the number of bytes separately.

2021-03-31
09:40 Edit reply: Feature request: Import csv into a table with generated columns (artifact: d429ccb10a user: hanche)

That is a pretty clever idea! I'll file it away for future use. In this particular case, it seems to me that the approach I outlined in a sibling post to yours is easier. But yours seems superior if one has to do many of these imports into the same table, as the creation of the view and trigger only has to be done once.

Edit, much later:

I just had an opportunity to try out this idea, and it worked great! Also, after reading up on triggers, it turned out to be almost ridiculously easy to implement. (And if the format of the csv file ever changes, I can just recreate the view to fit the new format.) Thank you!

09:33 Reply: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: c7a923b90b user: hanche)

As indicated, I had more to say. To repeat myself, I am confused.

You speak of a browser, but don't tell us which browser. Is it just a generic web browser? But then there is also a C program, or quite possibly two C programs, and your browser communicates with the C program(s) – how? Do you have a C program acting like a web server that the browser is talking to? Over http(s), or the websocket protocol, or some other means? And last, but not least, since this is after all the sqlite forum, where in all this does your use of sqlite fit in?

In short, without some clear picture of where the data (the gif file) originates, how it is passed to the browser, what you do to it there (using javascript?), before you do what? Pass it to another C program?

In short, I think your problem description has too many words and too little code. I just can't wrap my brain around it, and so feel unable to help. It would be great if you could cook up a toy example and provide code for it, if that is at all possible.

Regarding indexedDB, as far as I know, in most browsers it is indeed based on sqlite. But the specification of the indexedDB API does not assume that, and hence you most likely do not have access to the full sqlite functionality from within the browser. In which case, you just need to stick to whatever the API gives you.

09:19 Edit reply: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: f7d07c1d7c user: hanche)

I admit I have a hard time understanding your setup.

But let me make a few remarks that may help:

Specifying charset=utf-8 for a gif image is definitely wrongheaded. It probably comes from a default configuration of the server. It may or may not be harmless: That depends what the client does when it sees this specification. Worst case scenario, the client will get some mangled data that cannot be reconstituted into the original gif.

I created a small (16x16) gif file for experimental purposes.

I notice that it starts with the characters GIF89a immediately followed by binary data. If you see those characters at the beginning of your data, you are indeed looking at binary data. It will almost certainly contain NUL characters, and thus will not survive a trip through a C string.

Here is a hexdump of the file:

47494638396110000e00f00000ff00000000ff21f90400000000002c0000
000010000e000002178c8fa9cbbd00236452d23a1f0617f7fa599ae794e6
9914003b

Any hex representation of a gif file should start with 474946383961 (that is hex for GIF89a.

Similarly, here is a base64 encoding of the same file:

R0lGODlhEAAOAPAAAP8AAAAA/yH5BAAAAAAALAAAAAAQAA4AAAIXjI+py70AI2RS0jofBhf3+lma55TmmRQAOw==

Any base64 representation of a gif file should start with R0lGODlh, which is GIF89a in base64.

Hopefully, these observations may be a help in understanding what form of the data you are looking at.

Note that JSON cannot encode binary data. So if a GIF file is to be sent via JSON, it has to be encoded as a string. Base64 and hex are perhaps the two most popular ways of doing that. Obviously, you have to know what you've got in order to decode it.

I have some more to say, but I'll defer that to a second reply (for readability, and to give my mind time to clear).

Edited to add:

I ran a tiny experiment with the gif file above (eliding some sqlite3 verbosty):

⬥ sqlite3
▶▶▶ create table g(b);
▶▶▶ insert into g values(readfile('tt.gif'));
▶▶▶ .headers off
▶▶▶ select hex(b) from g;
47494638396110000E00F00000FF00000000FF21F90400000000002C0000000010000E000002178C8FA9CBBD00236452D23A1F0617F7FA599AE794E69914003B

Again, look for the telltale 474946383961 at the beginning of the return value.

08:52 Reply: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: df77dbcb2c user: hanche)

I admit I have a hard time understanding your setup.

But let me make a few remarks that may help:

Specifying charset=utf-8 for a gif image is definitely wrongheaded. It probably comes from a default configuration of the server. It may or may not be harmless: That depends what the client does when it sees this specification. Worst case scenario, the client will get some mangled data that cannot be reconstituted into the original gif.

I created a small (16x16) gif file for experimental purposes.

I notice that it starts with the characters GIF89a immediately followed by binary data. If you see those characters at the beginning of your data, you are indeed looking at binary data. It will almost certainly contain NUL characters, and thus will not survive a trip through a C string.

Here is a hexdump of the file:

47494638396110000e00f00000ff00000000ff21f90400000000002c0000
000010000e000002178c8fa9cbbd00236452d23a1f0617f7fa599ae794e6
9914003b

Any hex representation of a gif file should start with 474946383961 (that is hex for GIF89a.

Similarly, here is a base64 encoding of the same file:

R0lGODlhEAAOAPAAAP8AAAAA/yH5BAAAAAAALAAAAAAQAA4AAAIXjI+py70AI2RS0jofBhf3+lma55TmmRQAOw==

Any base64 representation of a gif file should start with R0lGODlh, which is GIF89a in base64.

Hopefully, these observations may be a help in understanding what form of the data you are looking at.

Note that JSON cannot encode binary data. So if a GIF file is to be sent via JSON, it has to be encoded as a string. Base64 and hex are perhaps the two most popular ways of doing that. Obviously, you have to know what you've got in order to decode it.

I have some more to say, but I'll defer that to a second reply (for readability, and to give my mind time to clear).

07:57 Reply: Automatic change True value to 1 (artifact: 48d6bebbf9 user: hanche)

This is possibly a redundant remark, but it's worth keeping in mind the difference between the keyword true and the string 'true'. The latter is not converted to 1.

2021-03-27
17:40 Reply: Novice question about running UTF-8 on CLI from file. (artifact: 3125bd3704 user: hanche)

It is available for Windows.

Are you sure? It does not look that way to me.

But a bit of googling reveals this list of text editors, at least some of which run on Windows, and several of them free: 13 Best Text Editors to Speed up Your Workflow in 2021

2021-03-25
20:05 Reply: json_tree documentation improvement: change type "string" to "text" (artifact: 109c981af3 user: hanche)

But there seems to be a contradiction between the docs and the implementation, then:

▶▶▶ select * from json_tree('"foo"');
key  value  type  atom  id  parent  fullkey  path
---  -----  ----  ----  --  ------  -------  ----
     foo    text  foo   0           $        $

Shouldn't the type field here adhere to JSON terminology, and hence contain the string 'string' in this case?

But changing it might break many applications, so for the sake of backward compatibility, I guess we're stuck with 'text'. And then the question is once more whether the documentation ought to reflect this.

2021-03-20
13:10 Edit reply: Week numbers in queries (artifact: 73d99e4497 user: hanche)

I am reviving this old thread to announce that I wrote a patch that implements the %V, %u and %G directives from (unix) strftime(). Briefly, %V is the week number as ISO 8601 defines it, %G is the corresponding year (since a week is considered to belong to the year that holds most of its days), and %u is the weekday with 1=Monday, 7=Sunday.

Without further ado, here is the patch:

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
 **   %%  %
 */
@@ -1025,13 +1028,15 @@
         case 'd':
         case 'H':
         case 'm':
         case 'M':
         case 'S':
+        case 'V':
         case 'W':
           n++;
           /* fall thru */
+        case 'u':
         case 'w':
         case '%':
           break;
         case 'f':
           n += 8;
@@ -1038,10 +1043,11 @@
           break;
         case 'j':
           n += 3;
           break;
         case 'Y':
+        case 'G':
           n += 8;
           break;
         case 's':
         case 'J':
           n += 50;
@@ -1117,13 +1123,42 @@
           sqlite3Int64ToText(iS, &z[j]);
           j += sqlite3Strlen30(&z[j]);
           break;
         }
         case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
+        case 'u': {
+          z[j++] = (char)(((x.iJD+43200000)/86400000) % 7) + '1';
+          break;
+        }
         case 'w': {
           z[j++] = (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_snprintf(5,&z[j],"%04d",y.Y); j+=sqlite3Strlen30(&z[j]);
+          }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_snprintf(3, &z[j],"%02d",nDay/7+1);
+            j += 2;
+          }
+          break;
         }
         case 'Y': {
           sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
           break;
         }

This is unencumbered code. I wrote it from scratch, only drawing inspiration (and copying/modifying snippets) from the sqlite source. Anyone, including the sqlite core team, is allowed to use it as they see fit. I am willing to sign papers to that effect. But of course, even though I have tested the code and believe it to be correct, I do not accept liability if there is a bug. Use at your own risk.

I would be happy if code resembling this were to make it into the official sqlite source. ISO week numbers are used all over the place in Europe, after all.

Edited to add: If anyone would like to review the code, any comments are welcome. And if anything about the code is unclear, please ask. I have some tests generated with GNU date, if anyone is interested. But I have not tried to write test cases fitting into the sqlite test framework, since I have no idea how.

13:00 Reply: Week numbers in queries (artifact: 981761eb8b user: hanche)

I am reviving this old thread to announce that I wrote a patch that implements the %V, %u and %G directives from (unix) strftime(). Briefly, %V is the week number as ISO 8601 defines it, %G is the corresponding year (since a week is considered to belong to the year that holds most of its days), and %u is the weekday with 1=Monday, 7=Sunday.

Without further ado, here is the patch:

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
 **   %%  %
 */
@@ -1025,13 +1028,15 @@
         case 'd':
         case 'H':
         case 'm':
         case 'M':
         case 'S':
+        case 'V':
         case 'W':
           n++;
           /* fall thru */
+        case 'u':
         case 'w':
         case '%':
           break;
         case 'f':
           n += 8;
@@ -1038,10 +1043,11 @@
           break;
         case 'j':
           n += 3;
           break;
         case 'Y':
+        case 'G':
           n += 8;
           break;
         case 's':
         case 'J':
           n += 50;
@@ -1117,13 +1123,42 @@
           sqlite3Int64ToText(iS, &z[j]);
           j += sqlite3Strlen30(&z[j]);
           break;
         }
         case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
+        case 'u': {
+          z[j++] = (char)(((x.iJD+43200000)/86400000) % 7) + '1';
+          break;
+        }
         case 'w': {
           z[j++] = (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_snprintf(5,&z[j],"%04d",y.Y); j+=sqlite3Strlen30(&z[j]);
+          }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_snprintf(3, &z[j],"%02d",nDay/7+1);
+            j += 2;
+          }
+          break;
         }
         case 'Y': {
           sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
           break;
         }

This is unencumbered code. I wrote it from scratch, only drawing inspiration (and copying/modifying snippets) from the sqlite source. Anyone, including the sqlite core team, is allowed to use it as they see fit. I am willing to sign papers to that effect. But of course, even though I have tested the code and believe it to be correct, I do not accept liability if there is a bug. Use at your own risk.

I would be happy if code resembling this were to make it into the official sqlite source. ISO week numbers are used all over the place in Europe, after all.

2021-03-02
14:13 Reply: Feature request: Import csv into a table with generated columns (artifact: 317f298348 user: hanche)

That is a pretty clever idea! I'll file it away for future use. In this particular case, it seems to me that the approach I outlined in a sibling post to yours is easier. But yours seems superior if one has to do many of these imports into the same table, as the creation of the view and trigger only has to be done once.

14:11 Reply: Feature request: Import csv into a table with generated columns (artifact: f2cd06e62b user: hanche)

Ah, thanks; I hadn't quite stopped to think about the difference between information available to the shell and what the core library can discover so easily. Still, however, I don't think the docs explains (to my level of understanding) what happened. I think it all depends what mechanism the shell is using to communicate the tuples to the database layer and telling it what to do with it. As I haven't studied the source, and am not at all familiar with the C API, I really can't know.

Unfortunately, my csv files come from spreadsheets not of my making, so the column headers won't match. But your response (and the other followup) got a handful of my brain cells working again, so I found a better way, demonstrated here with my toy example.

I start sqlite3 with no argument, thus working at first with the in-memory database, then:

▶▶▶ .shell cat n.csv
"fee fie foe foo",bar
1,2
3,4
▶▶▶ .import -csv n.csv tmpn
▶▶▶ attach 'n.db' as n;
▶▶▶ .schema n
CREATE TABLE n.n(a,b,c as (a+b) stored);
▶▶▶ insert into n.n select * from tmpn;
▶▶▶ select * from n;
a  b  c
-  -  -
1  2  3
3  4  7

This seems to me the easiest approach, as the critical parts (.import, attach, insert) are the same no matter the particulars of the database schema and the details of the csv file, so long as the number of columns matches.

2021-03-01
16:55 Post: Feature request: Import csv into a table with generated columns (artifact: dac8559275 user: hanche)

Consider this very simple example (if you'll excuse my unusual prompt):

▶▶▶ create table p(a integer, b integer, c integer as (a + b) stored);
▶▶▶ insert into p values(40,2);
▶▶▶ select * from p;
a   b  c
--  -  --
40  2  42
▶▶▶ .shell cat p.csv
1,3
2,4
▶▶▶ .mode csv
▶▶▶ .import p.csv p
Error: table p has 2 columns but 3 values were supplied

The insert statement works fine, evidently ignoring the generated column. But the .import fails with a rather puzzling error message. (It doesn't matter whether the column is stored or not.)

I think it would be useful to be able to import csv files directly into a table with generated columns. As it is, the easiest workaround is to import the csv into a temporary table, then insert from there.

(I labeled this a feature request, but it seems to me to exist in the grey zone between a feature request and a bug report. Make of it what you will. But the error message is at least misleading.)

– Harald