diff options
author | Yorhel <git@yorhel.nl> | 2009-08-08 17:11:56 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-08-08 17:11:56 +0200 |
commit | f95031507420e14ab95512b3bfbbbfab8fe5ce9f (patch) | |
tree | 34a58fbb4ab2de2f977eb98455373fce246919d1 | |
parent | 9d3e1e78bc963ac58b504edb00b428e82e177f68 (diff) |
Converted users.registered to timestamptz
Just a few more to go...
-rw-r--r-- | lib/VNDB/DB/Users.pm | 10 | ||||
-rw-r--r-- | util/dump.sql | 2 | ||||
-rw-r--r-- | util/updates/update_2.6.sql | 6 |
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; |