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

[freeirc support <support@xxxxxxxxxxx>]

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