diff options
| -rw-r--r-- | lib/Multi/IRC.pm | 2 | ||||
| -rw-r--r-- | lib/VNDB/DB/Tags.pm | 5 | ||||
| -rw-r--r-- | util/dump.sql | 2 | ||||
| -rw-r--r-- | util/updates/update_2.6.sql | 6 |
4 files changed, 11 insertions, 4 deletions
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index 84f307d9..8c5d4143 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -274,7 +274,7 @@ sub notify { # name, pid, payload 'g' AS type, t.id, t.name AS title, u.username FROM tags t JOIN users u ON u.id = t.addedby - WHERE t.added > ? + WHERE t.added > to_timestamp(?) ORDER BY t.added|; $_[KERNEL]->post(pg => query => $q, [ $t ], 'formatid', [ keys %{$_[HEAP]{notify}} ]); diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 3e095d3c..755758c7 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -39,7 +39,8 @@ sub dbTagGet { 't.meta = ?' => $o{meta}?1:0 ) : (), ); my @select = ( - qw|t.id t.meta t.name t.description t.added t.state t.c_vns|, + qw|t.id t.meta t.name t.description t.state t.c_vns|, + q|extract('epoch' from t.added) as added|, $o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (), ); my @join = $o{what} =~ /addedby/ ? 'JOIN users u ON u.id = t.addedby' : (); @@ -88,7 +89,7 @@ sub dbTagEdit { my($self, $id, %o) = @_; $self->dbExec('UPDATE tags !H WHERE id = ?', { - $o{upddate} ? ('added = ?' => time) : (), + $o{upddate} ? ('added = NOW()' => 1) : (), map { +"$_ = ?" => $o{$_} } qw|name meta description state| }, $id); $self->dbExec('DELETE FROM tags_aliases WHERE tag = ?', $id); diff --git a/util/dump.sql b/util/dump.sql index a89a5e7e..f8c7564c 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -181,7 +181,7 @@ CREATE TABLE tags ( name varchar(250) NOT NULL UNIQUE, description text NOT NULL DEFAULT '', meta boolean NOT NULL DEFAULT FALSE, - added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()), + added timestamptz NOT NULL DEFAULT NOW(), state smallint NOT NULL DEFAULT 0, c_vns integer NOT NULL DEFAULT 0, addedby integer NOT NULL DEFAULT 1 diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql index 7f0242f7..3a1f1482 100644 --- a/util/updates/update_2.6.sql +++ b/util/updates/update_2.6.sql @@ -82,6 +82,12 @@ ALTER TABLE users ALTER COLUMN registered TYPE timestamptz USING to_timestamp(re ALTER TABLE users ALTER COLUMN registered SET DEFAULT NOW(); +-- tags.added -> timestamptz +ALTER TABLE tags ALTER COLUMN added DROP DEFAULT; +ALTER TABLE tags ALTER COLUMN added TYPE timestamptz USING to_timestamp(added); +ALTER TABLE tags ALTER COLUMN added 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; |