From 5865bdbb0c0b9492d57af220bb00ee9d1f7eeed7 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 8 Aug 2009 16:09:47 +0200 Subject: Improved handling of the timestamp columns in anime and session tables Anything fetched from the DB to Perl should be converted to a UNIX timestamp, and everything that goes from Perl to the DB should be converted from a UNIX timestamp to a timestamptz data type. Also, when creating a session, don't rely on the fact that the expiration default happens to be the same as the cookie expiration time calculated in Perl. It's cleaner to calculate the date at one place and then use that everywhere else. --- lib/VNDB/DB/Users.pm | 9 ++------- lib/VNDB/DB/VN.pm | 2 +- lib/VNDB/Util/Auth.pm | 2 +- 3 files changed, 4 insertions(+), 9 deletions(-) diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index 7e280999..c3d7c484 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -115,13 +115,8 @@ sub dbUserDel { # uid, 40 character session token, expiration time (timestamp) sub dbSessionAdd { my($s, @o) = @_; - if (defined $o[2]) { - $s->dbExec(q|INSERT INTO sessions (uid, token, expiration) VALUES(?, decode(?, 'hex'), ?)|, - @o); - } else { - $s->dbExec(q|INSERT INTO sessions (uid, token) VALUES(?, decode(?, 'hex'))|, - @o); - } + $s->dbExec(q|INSERT INTO sessions (uid, token, expiration) VALUES(?, decode(?, 'hex'), to_timestamp(?))|, + @o[0,1], $o[2]||(time+31536000)); } diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 23961987..49077aee 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -111,7 +111,7 @@ sub dbVNGet { if($o{what} =~ /anime/) { push(@{$r->[$r{$_->{vid}}]{anime}}, $_) && delete $_->{vid} for (@{$self->dbAll(q| - SELECT va.vid, a.* + SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji, extract('epoch' from a.lastfetch) AS lastfetch FROM vn_anime va JOIN anime a ON va.aid = a.id WHERE va.vid IN(!l)|, diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm index b9724964..ad225d92 100644 --- a/lib/VNDB/Util/Auth.pm +++ b/lib/VNDB/Util/Auth.pm @@ -42,7 +42,7 @@ sub authLogin { my $token = sha1_hex(join('', Time::HiRes::gettimeofday()) . join('', map chr(rand(93)+33), 1..9)); my $expiration = time + 31536000; # 1yr my $cookie = $token . $self->{_auth}{id}; - $self->dbSessionAdd($self->{_auth}{id}, $token); + $self->dbSessionAdd($self->{_auth}{id}, $token, $expiration); my $expstr = strftime("%a, %d %b %Y %H:%M:%S GMT", gmtime($expiration)); $self->resRedirect($to, 'post'); -- cgit v1.2.3