diff options
| -rw-r--r-- | ChangeLog | 1 | ||||
| -rw-r--r-- | lib/Multi/IRC.pm | 2 | ||||
| -rw-r--r-- | lib/Multi/Sitemap.pm | 24 | ||||
| -rw-r--r-- | lib/VNDB/DB/Misc.pm | 3 | ||||
| -rw-r--r-- | lib/VNDB/DB/Producers.pm | 2 | ||||
| -rw-r--r-- | lib/VNDB/DB/Releases.pm | 3 | ||||
| -rw-r--r-- | lib/VNDB/DB/VN.pm | 2 | ||||
| -rw-r--r-- | util/dump.sql | 6 | ||||
| -rw-r--r-- | util/updates/update_2.6.sql | 6 |
9 files changed, 29 insertions, 20 deletions
@@ -12,6 +12,7 @@ - Converted font size units to px in the css - Added double-post prevention - Converted old categories to tags and removed last traces of the category system + - Converted all date/time columns to timestamptz 2.5 - 2009-07-09 - Hide NSFW images in diff viewer (unless NSFW warnings are disabled) diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index 8c5d4143..6b3a1dbb 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -261,7 +261,7 @@ sub notify { # name, pid, payload LEFT JOIN releases_rev rr ON c.type = 1 AND c.id = rr.id LEFT JOIN producers_rev pr ON c.type = 2 AND c.id = pr.id JOIN users u ON u.id = c.requester - WHERE c.added > ? + WHERE c.added > to_timestamp(?) ORDER BY c.added| : $_[ARG0] eq 'newpost' ? q|SELECT 't' AS type, tp.tid AS id, tp.num AS rev, t.title, u.username, |.GETBOARDS.q| diff --git a/lib/Multi/Sitemap.pm b/lib/Multi/Sitemap.pm index 175b170a..85afae57 100644 --- a/lib/Multi/Sitemap.pm +++ b/lib/Multi/Sitemap.pm @@ -80,41 +80,41 @@ sub generate { for (map { 'v/'.$_, 'p/'.$_ } 'a'..'z', 0, 'all'); # /v+ - $_[KERNEL]->post(pg => query => ' - SELECT v.id, c.added + $_[KERNEL]->post(pg => query => q| + SELECT v.id, extract('epoch' from c.added) as added FROM vn v JOIN vn_rev vr ON vr.id = v.latest JOIN changes c ON vr.id = c.id WHERE v.hidden = FALSE - ORDER BY v.id', + ORDER BY v.id|, undef, 'addquery', [ 'v', 0.7 ]); # /r+ - $_[KERNEL]->post(pg => query => ' - SELECT r.id, c.added + $_[KERNEL]->post(pg => query => q| + SELECT r.id, extract('epoch' from c.added) as added FROM releases r JOIN releases_rev rr ON rr.id = r.latest JOIN changes c ON c.id = rr.id WHERE r.hidden = FALSE - ORDER BY r.id', + ORDER BY r.id|, undef, 'addquery', [ 'r', 0.5 ]); # /p+ - $_[KERNEL]->post(pg => query => ' - SELECT p.id, c.added + $_[KERNEL]->post(pg => query => q| + SELECT p.id, extract('epoch' from c.added) as added FROM producers p JOIN producers_rev pr ON pr.id = p.latest JOIN changes c ON c.id = pr.id WHERE p.hidden = FALSE - ORDER BY p.id', + ORDER BY p.id|, undef, 'addquery', [ 'p', 0.3 ]); # /g+ - $_[KERNEL]->post(pg => query => ' - SELECT t.id, t.added + $_[KERNEL]->post(pg => query => q| + SELECT t.id, extract('epoch' from t.added) as added FROM tags t WHERE state = 2 - ORDER BY t.id', + ORDER BY t.id|, undef, 'addquery', [ 'g', 0.3, 1 ]); } diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index 1c21675e..eeb860b0 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -127,7 +127,8 @@ sub dbRevisionGet { ); my @select = ( - qw|c.id c.type c.added c.requester c.comments c.rev c.causedby|, + qw|c.id c.type c.requester c.comments c.rev c.causedby|, + q|extract('epoch' from c.added) as added|, $o{what} =~ /user/ ? 'u.username' : (), $o{what} =~ /item/ ? ( 'COALESCE(vr.vid, rr.rid, pr.pid) AS iid', diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index 7a63d1a2..65d1fbca 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -43,7 +43,7 @@ sub dbProducerGet { my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang'; $select .= ', pr.desc, pr.alias, pr.website, p.hidden, p.locked' if $o{what} =~ /extended/; - $select .= ', c.added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev' if $o{what} =~ /changes/; + $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev| if $o{what} =~ /changes/; my($r, $np) = $self->dbPage(\%o, q| SELECT !s diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index bf40d8c2..672596da 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -73,7 +73,8 @@ sub dbReleaseGet { qw|r.id rr.title rr.original rr.website rr.released rr.minage rr.type rr.patch|, 'rr.id AS cid', $o{what} =~ /extended/ ? qw|rr.notes rr.catalog rr.gtin rr.resolution rr.voiced rr.freeware rr.doujin rr.ani_story rr.ani_ero r.hidden r.locked| : (), - $o{what} =~ /changes/ ? qw|c.added c.requester c.comments r.latest u.username c.rev| : (), + $o{what} =~ /changes/ ? + (qw|c.requester c.comments r.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (), ); my($r, $np) = $self->dbPage(\%o, q| diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 49077aee..b3603985 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -85,7 +85,7 @@ sub dbVNGet { $o{what} =~ /extended/ ? ( qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (), $o{what} =~ /changes/ ? ( - qw|c.added c.requester c.comments v.latest u.username c.rev c.causedby|) : (), + qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (), $o{what} =~ /relgraph/ ? 'rg.cmap' : (), $o{what} =~ /ranking/ ? '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS ranking' : (), $tag_ids ? diff --git a/util/dump.sql b/util/dump.sql index f8c7564c..b3baae99 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -34,7 +34,7 @@ CREATE TABLE changes ( id SERIAL NOT NULL PRIMARY KEY, type smallint NOT NULL DEFAULT 0, rev integer NOT NULL DEFAULT 1, - added bigint NOT NULL DEFAULT DATE_PART('epoch', NOW()), + added timestamptz NOT NULL DEFAULT NOW(), requester integer NOT NULL DEFAULT 0, ip inet NOT NULL DEFAULT '0.0.0.0', comments text NOT NULL DEFAULT '', @@ -809,8 +809,8 @@ CREATE SEQUENCE covers_seq; -- Rows that are assumed to be available INSERT INTO users (id, username, mail, rank) VALUES (0, 'deleted', 'del@vndb.org', 0); -INSERT INTO users (username, mail, rank, registered) - VALUES ('multi', 'multi@vndb.org', 0, EXTRACT(EPOCH FROM NOW())); +INSERT INTO users (username, mail, rank) + VALUES ('multi', 'multi@vndb.org', 0); INSERT INTO stats_cache (section, count) VALUES ('users', 1), diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql index 3a1f1482..26d363d7 100644 --- a/util/updates/update_2.6.sql +++ b/util/updates/update_2.6.sql @@ -88,6 +88,12 @@ ALTER TABLE tags ALTER COLUMN added TYPE timestamptz USING to_timestamp(added); ALTER TABLE tags ALTER COLUMN added SET DEFAULT NOW(); +-- changes.added -> timestamptz +ALTER TABLE changes ALTER COLUMN added DROP DEFAULT; +ALTER TABLE changes ALTER COLUMN added TYPE timestamptz USING to_timestamp(added); +ALTER TABLE changes 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; |