Re: [PATCH] default values for localtime column, better-fitting types

[jrmu <jrmu@xxxxxxxxxx>]

Was this patch tested? If so, IanJ, what do you think about using this
one?

-- 
jrmu
IRCNow (https://ircnow.org)

On Fri, Jun 02, 2023 at 02:12:05AM +0200, freeirc support wrote:
> adds a default value, in the same format as before (unix timestamp),
> so that complicated and failable migration scripts are not required.
> 
> we use an INTEGER because sqlite does not have a special type for
> timestamps. "current_timestamp" and similar are stored as large
> and inefficent TEXT (a UTF-8 or 16 string).
> 
> note that due to sqlite's weak typing, changing the column's declared
> type does not actually do much: it was already stored as an INTEGER
> to begin with, despite the declared VARCHAR type. changing this
> will just make the schema less misleading and confusing to those
> unfamiliar with sqlite's oddities.
> 
> we cannot give a default value to the date column, as botnow
> calculates the value with respect to the current timezone. despite
> the name, localtime does not consider the local timezone; this is
> unchanged from previous behavior.
> ---
>  table.sql | 12 ++++++------
>  1 file changed, 6 insertions(+), 6 deletions(-)
> 
> diff --git a/table.sql b/table.sql
> index 1b71f51..f0e4568 100644
> --- a/table.sql
> +++ b/table.sql
> @@ -7,7 +7,7 @@ CREATE TABLE bnc (
>    username VARCHAR(32),
>    email VARCHAR(100),
>    password VARCHAR(100),
> -  localtime VARCHAR(100),
> +  localtime INTEGER DEFAULT (unixepoch()),
>    captcha INTEGER
>  );
>  CREATE TABLE shell (
> @@ -19,7 +19,7 @@ CREATE TABLE shell (
>    username VARCHAR(32),
>    email VARCHAR(100),
>    password VARCHAR(100),
> -  localtime VARCHAR(100),
> +  localtime INTEGER DEFAULT (unixepoch()),
>    captcha INTEGER
>  );
>  CREATE TABLE mail (
> @@ -31,7 +31,7 @@ CREATE TABLE mail (
>    username VARCHAR(32),
>    email VARCHAR(100),
>    password VARCHAR(100),
> -  localtime VARCHAR(100),
> +  localtime INTEGER DEFAULT (unixepoch()),
>    captcha INTEGER
>  );
>  CREATE TABLE www (
> @@ -73,13 +73,13 @@ CREATE TABLE irc (
>    identified INTEGER,
>    ctcpversion VARCHAR(100),
>    ctcptime VARCHAR(100),
> -  localtime VARCHAR(100),
> +  localtime INTEGER DEFAULT (unixepoch()),
>    oper INTEGER,
>    idle INTEGER,
>    ssl INTEGER,
>    epochtime INTEGER,
>    chans VARCHAR(200),
> -  date VARCHAR(100)
> +  date INTEGER
>  );
>  CREATE TABLE smtp (
>    id INTEGER PRIMARY KEY,
> @@ -89,7 +89,7 @@ CREATE TABLE smtp (
>    deliveredto VARCHAR(100),
>    received VARCHAR(1000),
>    dkim VARCHAR(1000),
> -  date VARCHAR(100),
> +  date INTEGER,
>    other VARCHAR(5000),
>    content VARCHAR(100),
>    mime VARCHAR(100),
> -- 
> 2.35.1
>

Attachment: signature.asc
Description: PGP signature