summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/VNDB/DB/Users.pm10
-rw-r--r--util/dump.sql2
-rw-r--r--util/updates/update_2.6.sql6
3 files changed, 12 insertions, 6 deletions
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index c3d7c484..e1f4c378 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -37,14 +37,14 @@ sub dbUserGet {
$o{ip} ? (
'ip = ?' => $o{ip} ) : (),
$o{registered} ? (
- 'registered > ?' => $o{registered} ) : (),
+ 'registered > to_timestamp(?)' => $o{registered} ) : (),
$o{search} ? (
'username ILIKE ?' => "%$o{search}%") : (),
);
my @select = (
- qw|id username mail rank salt registered c_votes c_changes show_nsfw show_list skin customcss ip c_tags|,
- q|encode(passwd, 'hex') AS passwd|,
+ qw|id username mail rank salt c_votes c_changes show_nsfw show_list skin customcss ip c_tags|,
+ q|encode(passwd, 'hex') AS passwd|, q|extract('epoch' from registered) as registered|,
$o{what} =~ /stats/ ? (
'(SELECT COUNT(*) FROM rlists WHERE uid = u.id) AS releasecount',
'(SELECT COUNT(DISTINCT rv.vid) FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_vn rv ON rv.rid = r.latest WHERE uid = u.id) AS vncount',
@@ -90,8 +90,8 @@ sub dbUserEdit {
# username, pass(ecrypted), salt, mail, [ip]
sub dbUserAdd {
my($s, @o) = @_;
- $s->dbExec(q|INSERT INTO users (username, passwd, salt, mail, ip, registered) VALUES(?, decode(?, 'hex'), ?, ?, ?, ?)|,
- @o[0..3], $o[4]||$s->reqIP, time);
+ $s->dbExec(q|INSERT INTO users (username, passwd, salt, mail, ip) VALUES(?, decode(?, 'hex'), ?, ?, ?)|,
+ @o[0..3], $o[4]||$s->reqIP);
}
diff --git a/util/dump.sql b/util/dump.sql
index fa29460f..a89a5e7e 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -255,7 +255,7 @@ CREATE TABLE users (
mail varchar(100) NOT NULL,
rank smallint NOT NULL DEFAULT 3,
passwd bytea NOT NULL DEFAULT '',
- registered bigint NOT NULL DEFAULT 0,
+ registered timestamptz NOT NULL DEFAULT NOW(),
show_nsfw boolean NOT NULL DEFAULT FALSE,
show_list boolean NOT NULL DEFAULT TRUE,
c_votes integer NOT NULL DEFAULT 0,
diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql
index 18e8aee0..7f0242f7 100644
--- a/util/updates/update_2.6.sql
+++ b/util/updates/update_2.6.sql
@@ -76,6 +76,12 @@ ALTER TABLE votes ALTER COLUMN date TYPE timestamptz USING to_timestamp(date);
ALTER TABLE votes ALTER COLUMN date SET DEFAULT NOW();
+-- users.registered -> timestamptz
+ALTER TABLE users ALTER COLUMN registered DROP DEFAULT;
+ALTER TABLE users ALTER COLUMN registered TYPE timestamptz USING to_timestamp(registered);
+ALTER TABLE users ALTER COLUMN registered SET DEFAULT NOW();
+
+
-- screenshots.status (smallint) -> screenshots.processed (boolean)
ALTER TABLE screenshots RENAME COLUMN status TO processed;
ALTER TABLE screenshots ALTER COLUMN processed DROP DEFAULT;