SQLAR table - add default mtime - request for ENH.

(1.1) By midijohnny on 2022-06-05 11:30:08 edited from 1.0 [link] [source]

Requesting that the default definition of the table sqlar be modified to include a default epoch timestamp for current time.

Like this:

CREATE TABLE "sqlar" (
	"name"	TEXT,
	"mode"	INT,
	"mtime"	INT DEFAULT (strftime('%s', 'now')),
	"sz"	INT,
	"data"	BLOB,
	PRIMARY KEY("name")
)

Why?

I find it handy to 'stash' SQL commands or text files directly in the DB like this:

INSERT INTO SQLAR(name, data) VALUES('hello.txt', 'Hello World');

This works - I can extract the files using standard commandline options:

sqlite > .archive --extract hello.txt

But the only slight drawback is that the mtime is the start of epoch:

-rw-rw-r-- 1 user user 11 Jan  1  1970 hello.txt

And it would be nice if this was the actual date the file was stashed.

Permissions and size apparently don't need to be set - permissions already default to apparently 'rw-rw-r--' - so nothing too dangerous.

I have manually created my 'sqlar' table this way - and it works. This is a request for the default creation of this table to also included the default. (So I could then use the standard '.archive --create' commands etc).

(2) By Simon Slavin (slavin) on 2022-06-05 17:16:09 in reply to 1.1 [source]

I can't help with your actual request, but I think that once you added the extra column, you could use a TRIGGER to set the value for it. This might make your code a little more elegant.

(3.1) By midijohnny on 2022-06-05 17:49:35 edited from 3.0 in reply to 2 [link] [source]

Thanks Simon - actually I'm not adding an extra column - just adding a default for it.

I'm not sure I agree that a trigger would be more elegant - the 'default' mechanism feels more declarative, a trigger more imperative.

Having said that - a trigger might not be a bad idea here - just for the sake of leaving the original table definition alone.

Edit: I just noticed the DDL I posted is of a slightly different form than the form shown in the documentation - in that the PRIMARY KEY definition is done at the table level rather than the column-level - they are equivalent though. Probably a result of using the tool I'm using - DB Browser for SQLite