From 718f4d0258049aa92f229c08d5ec7204dae3ffa6 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 17 Oct 2015 17:05:50 +0200 Subject: SQL: Fix all browsing queries to use the new schema This basically makes VNDB browsable again, but editing entries is still broken. I split off the get-old-revision functionality from the db*Get() methods into db*GetRev(). This split makes sense even with the old SQL schema: db*Get() had to special-case some joins/filters when fetching an older revision, and none of the other filters would work in that case. This split does cause some code duplication in that all db*GetRev() methods look very much alike, and that the columns they fetch is almost identical to the db*Get() methods. Not sure yet how to avoid the duplication elegantly. I didn't do a whole lot of query optimization yet (most issues require extra indices, I'll investigate later which indices will make a big difference), but I did fix some low hanging fruit whenever I encountered something. I don't think I've worsened anything, performance-wise. --- lib/VNDB/Util/BrowseHTML.pm | 4 ++-- lib/VNDB/Util/CommonHTML.pm | 16 ++++++++-------- 2 files changed, 10 insertions(+), 10 deletions(-) (limited to 'lib/VNDB/Util') diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm index b54ad5d9..d57c0546 100644 --- a/lib/VNDB/Util/BrowseHTML.pm +++ b/lib/VNDB/Util/BrowseHTML.pm @@ -137,11 +137,11 @@ sub htmlBrowseHist { ], row => sub { my($s, $n, $i) = @_; - my $revurl = "/$i->{type}$i->{iid}.$i->{rev}"; + my $revurl = "/$i->{type}$i->{itemid}.$i->{rev}"; Tr; td class => 'tc1_1'; - a href => $revurl, "$i->{type}$i->{iid}"; + a href => $revurl, "$i->{type}$i->{itemid}"; end; td class => 'tc1_2'; a href => $revurl, ".$i->{rev}"; diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index 7b3fa673..bcc2ea97 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -133,12 +133,12 @@ sub htmlHiddenMessage { my($self, $type, $obj) = @_; return 0 if !$obj->{hidden}; my $board = $type =~ /[cs]/ ? 'db' : $type eq 'r' ? 'v'.$obj->{vn}[0]{vid} : $type.$obj->{id}; - # fetch edit summary (not present in $obj because the changes aren't fetched) - my $editsum = $type eq 'v' ? $self->dbVNGet(id => $obj->{id}, what => 'changes')->[0]{comments} - : $type eq 'r' ? $self->dbReleaseGet(id => $obj->{id}, what => 'changes')->[0]{comments} - : $type eq 'c' ? $self->dbCharGet(id => $obj->{id}, what => 'changes')->[0]{comments} - : $type eq 's' ? $self->dbStaffGet(id => $obj->{id}, what => 'changes')->[0]{comments} - : $self->dbProducerGet(id => $obj->{id}, what => 'changes')->[0]{comments}; + # fetch edit summary (not present in $obj, requires the db*GetRev() methods) + my $editsum = $type eq 'v' ? $self->dbVNGetRev(id => $obj->{id})->[0]{comments} + : $type eq 'r' ? $self->dbReleaseGetRev(id => $obj->{id})->[0]{comments} + : $type eq 'c' ? $self->dbCharGetRev(id => $obj->{id})->[0]{comments} + : $type eq 's' ? $self->dbStaffGetRev(id => $obj->{id})->[0]{comments} + : $self->dbProducerGetRev(id => $obj->{id})->[0]{comments}; div class => 'mainbox'; h1 $obj->{title}||$obj->{name}; div class => 'warning'; @@ -183,7 +183,7 @@ sub htmlRevision { a class => 'prev', href => sprintf('/%s%d.%d', $type, $new->{id}, $new->{rev}-1), '<- '.mt '_revision_previous' if $new->{rev} > 1; a class => 'next', href => sprintf('/%s%d.%d', $type, $new->{id}, $new->{rev}+1), mt('_revision_next').' ->' - if $new->{cid} != $new->{latest}; + if !$new->{lastrev}; p class => 'center'; a href => "/$type$new->{id}", "$type$new->{id}"; end; @@ -310,7 +310,7 @@ sub htmlEditMessage { } end; end; - if($obj && $obj->{latest} != $obj->{cid}) { + if($obj && !$obj->{lastrev}) { div class => 'warning'; h2 mt '_editmsg_revert_title'; p mt '_editmsg_revert_msg', $num; -- cgit v1.2.3 From 4b1807a58912ff0b4542063071e072ccf53fd1bd Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 1 Nov 2015 10:24:43 +0100 Subject: Remove deprecated 'staffedit' permission flag --- lib/VNDB/Util/LayoutHTML.pm | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-) (limited to 'lib/VNDB/Util') diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index e041960a..76b15690 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -104,9 +104,7 @@ sub _menu { if($self->authCan('edit')) { a href => '/v/add', mt '_menu_addvn'; br; a href => '/p/new', mt '_menu_addproducer'; br; - if($self->authCan('staffedit')) { - a href => '/s/new', mt '_menu_addstaff'; br; - } + a href => '/s/new', mt '_menu_addstaff'; br; a href => '/c/new', mt '_menu_addcharacter'; br; } br; -- cgit v1.2.3 From 3de8d24697511fe324cae2526eb65d6aafb5968b Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 1 Nov 2015 10:37:56 +0100 Subject: Removed support for sha256-hashed passwords They had to be deleted from the database at some point, otherwise we still have thousands of easily-cracked password hashes in the database. Note that I could have opted to use scrypt on top of the sha256 hashes so the passwords would remain secure without needing to reset everything, but doing that after one year of switching to scrypt is likely not worth it. Everyone who still actively uses his account has already been converted to scrypt, everyone else should just reset their password whevener they decide to come back. --- lib/VNDB/Util/Auth.pm | 23 ++--------------------- 1 file changed, 2 insertions(+), 21 deletions(-) (limited to 'lib/VNDB/Util') diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm index a1c6cf08..0c3b5b73 100644 --- a/lib/VNDB/Util/Auth.pm +++ b/lib/VNDB/Util/Auth.pm @@ -5,7 +5,7 @@ package VNDB::Util::Auth; use strict; use warnings; use Exporter 'import'; -use Digest::SHA qw|sha1 sha1_hex sha256|; +use Digest::SHA qw|sha1 sha1_hex|; use Crypt::URandom 'urandom'; use Crypt::ScryptKDF 'scrypt_raw'; use Encode 'encode_utf8'; @@ -112,16 +112,7 @@ sub _authCheck { my $d = $self->dbUserGet(username => $user, what => 'extended notifycount')->[0]; return 0 if !$d->{id}; - # Old-style hashes - if(length $d->{passwd} == 41) { - return 0 if _authPreparePassSha256($self, $pass, substr $d->{passwd}, 0, 9) ne $d->{passwd}; - $self->{_auth} = $d; - # Update database with new hash format, now that we have the plain text password - $self->dbUserEdit($d->{id}, passwd => $self->authPreparePass($pass)); - return 1; - } - - # New scrypt hashes + # scrypt format if(length $d->{passwd} == 46) { my($N, $r, $p, $salt) = unpack 'NCCa8', $d->{passwd}; return 0 if $self->authPreparePass($pass, $salt, $N, $r, $p) ne $d->{passwd}; @@ -144,16 +135,6 @@ sub authPreparePass { } -# Same as authPreparePass, but for the old sha256 hash. -# Arguments: pass, optionally salt -# Returns: encrypted password (as a binary string) -sub _authPreparePassSha256 { - my($self, $pass, $salt) = @_; - $salt ||= encode_utf8(randomascii(9)); - return $salt.sha256($self->{global_salt} . encode_utf8($pass) . $salt); -} - - # Generates a random token that can be used to reset the password. # Returns: token (hex string), token-encrypted (binary string) sub authPrepareReset { -- cgit v1.2.3 From 8c9cfe238e565088b37d256eb461a01919ef9968 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 1 Nov 2015 13:42:56 +0100 Subject: Switch to HTML5 doctype + s/acronym/abbr/ + s/ / /e I'd have preferred to stick with XHTML 1.0, but unfortunately browsers won't allow you to use modern Javascript APIs with an older doctype. Note that most pages don't actually validate correctly as HTML5, I'm relying on browsers to be lenient. In either case, I'd like VNDB to stay valid XML (XHTML5, then), and luckily that shouldn't be a problem. --- lib/VNDB/Util/CommonHTML.pm | 8 ++++---- lib/VNDB/Util/FormHTML.pm | 4 ++-- lib/VNDB/Util/LayoutHTML.pm | 7 ++----- 3 files changed, 8 insertions(+), 11 deletions(-) (limited to 'lib/VNDB/Util') diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index bcc2ea97..7504ace8 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -204,12 +204,12 @@ sub htmlRevision { table class => 'stripe'; thead; Tr; - td; lit ' '; end; + td; lit ' '; end; td; revheader($self, $type, $old); end; td; revheader($self, $type, $new); end; end; Tr; - td; lit ' '; end; + td; lit ' '; end; td colspan => 2; b mt '_revision_edit_summary', $new->{rev}; br; br; @@ -439,6 +439,7 @@ sub htmlSearchBox { sub htmlRGHeader { my($self, $title, $type, $obj) = @_; + # This used to be a good test for inline SVG support, but I'm not sure it is nowadays. if(($self->reqHeader('Accept')||'') !~ /application\/xhtml\+xml/) { $self->htmlHeader(title => $title); $self->htmlMainTabs($type, $obj, 'rg'); @@ -452,8 +453,7 @@ sub htmlRGHeader { $self->htmlFooter; return 1; } - $self->resHeader('Content-Type' => 'application/xhtml+xml; charset=UTF-8'); - $self->htmlHeader(title => $title, svg => 1); + $self->htmlHeader(title => $title); $self->htmlMainTabs($type, $obj, 'rg'); return 0; } diff --git a/lib/VNDB/Util/FormHTML.pm b/lib/VNDB/Util/FormHTML.pm index 9612f904..afecbe08 100644 --- a/lib/VNDB/Util/FormHTML.pm +++ b/lib/VNDB/Util/FormHTML.pm @@ -97,7 +97,7 @@ sub htmlFormPart { if(/check/) { Tr class => 'newfield'; td class => 'label'; - lit ' '; + lit ' '; end; td class => 'field'; input type => 'checkbox', name => $o{short}, id => $o{short}, tabindex => 10, @@ -120,7 +120,7 @@ sub htmlFormPart { } elsif($o{label}) { txt $o{label}; } else { - lit ' '; + lit ' '; } end; } diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index 76b15690..fcd26346 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -17,11 +17,8 @@ sub htmlHeader { # %options->{ title, noindex, search, feeds, svg } $skin = $self->{skin_default} if !$self->{skins}{$skin} || !-d "$VNDB::ROOT/static/s/$skin"; # heading - html lang => $self->{l10n}->language_tag(), $o{svg} ? ( - doctype => 'xhtml-math-svg', - 'xmlns:svg' => 'http://www.w3.org/2000/svg', - 'xmlns:xlink' => 'http://www.w3.org/1999/xlink' - ) : (); + lit ''; + tag 'html', lang => $self->{l10n}->language_tag(); head; title $o{title}; Link rel => 'shortcut icon', href => '/favicon.ico', type => 'image/x-icon'; -- cgit v1.2.3 From bf391c804fbbd6469d2e2892c29f98cc7204d251 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 1 Nov 2015 14:28:07 +0100 Subject: Use HTML5 'placeholder' attribute for main search box Instead of the JS hack. --- lib/VNDB/Util/LayoutHTML.pm | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/VNDB/Util') diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index fcd26346..505d03a8 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -76,7 +76,7 @@ sub _menu { form action => '/v/all', method => 'get', id => 'search'; fieldset; legend 'Search'; - input type => 'text', class => 'text', id => 'sq', name => 'sq', value => $o{search}||mt('_menu_emptysearch'); + input type => 'text', class => 'text', id => 'sq', name => 'sq', value => $o{search}, placeholder => mt('_menu_emptysearch'); input type => 'submit', class => 'submit', value => 'Search'; end; end; -- cgit v1.2.3 From 878cff6baf375ffa9cf8ab33c7f448744381586e Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 1 Nov 2015 14:31:02 +0100 Subject: Remove 'you need to be logged in to edit this page' message While helpful, it's also rather dominant. We're not that desperate for new contributes anymore. --- lib/VNDB/Util/CommonHTML.pm | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'lib/VNDB/Util') diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index 7504ace8..b576e4ad 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -329,11 +329,7 @@ sub htmlItemMessage { if($obj->{locked}) { p class => 'locked', mt '_itemmsg_locked'; - } elsif(!$self->authInfo->{id}) { - p class => 'locked'; - lit mt '_itemmsg_login', '/u/login'; - end; - } elsif(!$self->authCan('edit')) { + } elsif($self->authInfo->{id} && !$self->authCan('edit')) { p class => 'locked', mt '_itemmsg_denied'; } } -- cgit v1.2.3 From 333c6e35103584cb244f035ec217a82e77549b44 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 1 Nov 2015 14:36:29 +0100 Subject: Util::LayoutHTML: Suppress warning when $o{search} isn't defined --- lib/VNDB/Util/LayoutHTML.pm | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/VNDB/Util') diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index 505d03a8..f2344fc5 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -76,7 +76,7 @@ sub _menu { form action => '/v/all', method => 'get', id => 'search'; fieldset; legend 'Search'; - input type => 'text', class => 'text', id => 'sq', name => 'sq', value => $o{search}, placeholder => mt('_menu_emptysearch'); + input type => 'text', class => 'text', id => 'sq', name => 'sq', value => $o{search}||'', placeholder => mt('_menu_emptysearch'); input type => 'submit', class => 'submit', value => 'Search'; end; end; -- cgit v1.2.3