summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--css/v2.css20
-rw-r--r--elm/AdvSearch/Fields.elm11
-rw-r--r--elm/ColSelect.elm80
-rw-r--r--elm/Discussions/Edit.elm2
-rw-r--r--elm/Discussions/PostEdit.elm2
-rw-r--r--elm/Discussions/Reply.elm2
-rw-r--r--elm/Lib/Api.elm1
-rw-r--r--elm/Lib/Html.elm11
-rw-r--r--elm/Reviews/Comment.elm2
-rw-r--r--elm/Reviews/Edit.elm2
-rw-r--r--elm/StaffEdit.elm2
-rw-r--r--elm/UList/LabelEdit.elm13
-rw-r--r--elm/UList/Widget.elm7
-rw-r--r--elm/User/Edit.elm25
-rw-r--r--elm/User/Login.elm2
-rw-r--r--elm/User/Register.elm4
-rw-r--r--elm/VNLengthVote.elm2
-rw-r--r--elm/checkall.js4
-rw-r--r--elm/elm.json1
-rw-r--r--lib/Multi/API.pm12
-rw-r--r--lib/Multi/Maintenance.pm2
-rw-r--r--lib/VNDB/ExtLinks.pm14
-rw-r--r--lib/VNDB/Types.pm12
-rw-r--r--lib/VNWeb/AdvSearch.pm3
-rw-r--r--lib/VNWeb/Auth.pm2
-rw-r--r--lib/VNWeb/Elm.pm1
-rw-r--r--lib/VNWeb/HTML.pm2
-rw-r--r--lib/VNWeb/Misc/HomePage.pm6
-rw-r--r--lib/VNWeb/Producers/Page.pm4
-rw-r--r--lib/VNWeb/Releases/Lib.pm2
-rw-r--r--lib/VNWeb/Staff/Edit.pm4
-rw-r--r--lib/VNWeb/TableOpts.pm20
-rw-r--r--lib/VNWeb/ULists/Elm.pm7
-rw-r--r--lib/VNWeb/ULists/List.pm280
-rw-r--r--lib/VNWeb/User/Edit.pm12
-rw-r--r--lib/VNWeb/User/List.pm4
-rw-r--r--lib/VNWeb/User/Login.pm4
-rw-r--r--lib/VNWeb/User/Page.pm8
-rw-r--r--lib/VNWeb/User/Register.pm2
-rw-r--r--lib/VNWeb/VN/Length.pm2
-rw-r--r--lib/VNWeb/VN/List.pm23
-rw-r--r--lib/VNWeb/VN/Page.pm22
-rw-r--r--lib/VNWeb/Validation.pm18
-rw-r--r--sql/func.sql17
-rw-r--r--sql/perms.sql2
-rw-r--r--sql/schema.sql33
-rw-r--r--sql/tableattrs.sql6
-rw-r--r--sql/triggers.sql13
-rwxr-xr-xutil/devdump.pl1
-rw-r--r--util/updates/2021-09-02-some-foreign-key-stuff.sql5
-rw-r--r--util/updates/2021-09-26-vn-length-cache.sql6
-rw-r--r--util/updates/2021-10-27-freegame-mugen.sql3
-rw-r--r--util/updates/2021-10-28-username-casefold.sql2
-rw-r--r--util/updates/2021-10-28-username-history.sql16
-rw-r--r--util/updates/2021-10-28-website-length.sql4
55 files changed, 470 insertions, 297 deletions
diff --git a/css/v2.css b/css/v2.css
index 541af409..c21f4f6c 100644
--- a/css/v2.css
+++ b/css/v2.css
@@ -350,8 +350,14 @@ p.screenshots { text-align: center; margin-top: 10px; padding: 0; heig
p.screenshots img { margin: 2px; }
li.announcement { margin-bottom: 3px; margin-top: 3px }
li.announcement a { font-weight: bold; font-size: 15px; color: $maintext }
-.homepage h1 { margin-bottom: 5px }
-.homepage > div { overflow: hidden }
+.homepage { margin: 21px 0 -10px 0; display: grid; gap: 10px; grid-template-columns: 1fr 1fr 1fr; }
+@media (max-width: 1300px) { .homepage { grid-template-columns: 1fr 1fr } }
+@media (max-width: 900px) { .homepage { grid-template-columns: 1fr } }
+.homepage > div { overflow: hidden; border: 1px solid $border; padding: 0 2px 10px 2px; background: $boxbg; }
+.homepage h1 { margin: 0 0 5px 0; font-size: 18px; font-weight: bold; color: $boxtitle }
+.homepage h1 a { color: $boxtitle; }
+.homepage h2 { font-size: 14px; margin-top: 3px; }
+.homepage ul { list-style-type: none; margin-left: 10px; }
.homepage li { display: flex; line-height: 1.1 }
.homepage li span { white-space: nowrap; padding-right: 4px; padding-bottom: 3px }
.homepage li span:first-child { overflow: hidden; text-overflow: ellipsis }
@@ -665,7 +671,7 @@ div#vntags { margin: 0 30px 0 30px; border-top: 1px solid $bo
.vngrid > div > a > span:first-child { display: block; font-weight: bold }
.vngrid table { margin: 10px 0 }
.vngrid table td { padding: 0 5px 0 0 }
-.vngrid .ulist-widget-icon { float: right; margin: 280px 0 -300px -20px!important; padding: 3px; width: 20px; height: 20px } /* Horrible hacks everywhere */
+.vngrid .ulist-widget-icon { float: right; margin: 273px 0 -300px 0px!important; padding: 10px 5px 0 10px; background-color: rgba(0,0,0,0.8); border-radius: 15px 0 0 0 } /* Horrible hacks everywhere */
@@ -674,7 +680,7 @@ div#vntags { margin: 0 30px 0 30px; border-top: 1px solid $bo
.prodvns { list-style-type: none }
.prodvns li > span:first-child { display: inline-block; width: 80px; text-align: right; padding-right: 15px }
.prodvns li > span:last-child { color: $grayedout; padding-left: 15px }
-.prodvns .ulist-widget-icon { padding-right: 5px; width: 14px; height: 14px }
+.prodvns .ulist-widget-icon { padding-right: 5px }
/***** Producer list ******/
@@ -959,6 +965,12 @@ div.lengthlist {
.tco3 { width: 60px; text-align: right; padding-bottom: 0 }
}
+/* Just kill me already */
+[id^=ulist_labeledit] li > a {
+ padding-right: 30px!important;
+ .liststatus_icon, .spinner { float: right; margin-right: -26px; margin-top: 2px }
+}
+
/***** User notifications *****/
diff --git a/elm/AdvSearch/Fields.elm b/elm/AdvSearch/Fields.elm
index 723634fb..ff7c2004 100644
--- a/elm/AdvSearch/Fields.elm
+++ b/elm/AdvSearch/Fields.elm
@@ -512,6 +512,13 @@ fieldUpdate dat msg_ (num, dd, model) =
in (dat, (num,dd,FMNest newGrandModel), Cmd.none)
_ -> noop
+ -- Move root node to sub; for child nodes this is handled in nestUpdate, but the root node must be handled separately
+ (FMoveSub, FMNest m) ->
+ let subfields = [(num,DD.toggle dd False,model)]
+ (ndat,subm) = nestInit True m.qtype m.qtype subfields dat
+ (ndat2,subf) = fieldCreate -1 (ndat, FMNest subm)
+ in (ndat2, subf, Cmd.none)
+
(FSNest (NAnd a b), FMNest m) -> mapc FMNest FSNest (nestUpdate dat (NAnd a b) m)
(FSNest (NNeg a b), FMNest m) -> mapc FMNest FSNest (nestUpdate dat (NNeg a b) m)
(FSNest msg, FMNest m) -> mapf FMNest FSNest (nestUpdate dat msg m)
@@ -569,9 +576,7 @@ fieldViewDd dat dd lbl cont =
, if dat.level <= 1
then b [ title "Can't move this filter to parent branch" ] [ text "↰" ]
else a [ href "#", onClickD FMovePar, title "Move this filter to parent branch" ] [ text "↰" ]
- , if dat.level == 0
- then b [ title "Can't move this filter into a subbranch" ] [ text "↳" ]
- else a [ href "#", onClickD FMoveSub, title "Create new branch for this filter" ] [ text "↳" ]
+ , a [ href "#", onClickD FMoveSub, title "Create new branch for this filter" ] [ text "↳" ]
] :: cont ()
]
diff --git a/elm/ColSelect.elm b/elm/ColSelect.elm
deleted file mode 100644
index d78d0995..00000000
--- a/elm/ColSelect.elm
+++ /dev/null
@@ -1,80 +0,0 @@
--- Column selection dropdown for tables. Assumes that the currently selected
--- columns are in the query string as the 'c' parameter, e.g.:
---
--- ?c=column_id&c=modified&...
---
--- Accepts a [ $current_url, [ list of columns ] ] from Perl, e.g.:
---
--- [ '?c=column_id', [
--- [ 'column_id', 'Column Label' ],
--- [ 'modified', 'Date modified' ],
--- ...
--- ] ]
---
--- TODO: Convert all uses of this module to the more flexible TableOpts.
-module ColSelect exposing (main)
-
-import Html exposing (..)
-import Html.Attributes exposing (..)
-import Html.Events exposing (..)
-import Browser
-import Browser.Navigation exposing (load)
-import Set
-import Erl -- elm/url can't extract a full list of query parameters and hence can't be used to modify a parameter without removing all others.
-import Lib.DropDown as DD
-import Lib.Api as Api
-import Lib.Html exposing (..)
-
-
-main : Program (String, Columns) Model Msg
-main = Browser.element
- { init = \e -> (init e, Cmd.none)
- , view = view
- , update = update
- , subscriptions = \model -> DD.sub model.dd
- }
-
-
-type alias Columns = List (String, String)
-
-type alias Model =
- { cols : Columns
- , url : Erl.Url -- Without the "c" parameter
- , sel : Set.Set String
- , dd : DD.Config Msg
- }
-
-
-init : (String, Columns) -> Model
-init (u, c) =
- { cols = c
- , url = Erl.removeQuery "c" <| Erl.parse u
- , sel = Set.fromList <| Erl.getQueryValuesForKey "c" <| Erl.parse u
- , dd = DD.init "colselect" Open
- }
-
-
-type Msg
- = Open Bool
- | Toggle String Bool
- | Update
-
-
-update : Msg -> Model -> (Model, Cmd Msg)
-update msg model =
- case msg of
- Open b -> ({ model | dd = DD.toggle model.dd b }, Cmd.none)
- Toggle s b -> ({ model | sel = if b then Set.insert s model.sel else Set.remove s model.sel }, Cmd.none)
- Update -> (model, load <| Erl.toString <| List.foldl (\s u -> Erl.addQuery "c" s u) model.url <| Set.toList model.sel)
-
-
-view : Model -> Html Msg
-view model =
- let item (cid, cname) = li [ ] [ linkRadio (Set.member cid model.sel) (Toggle cid) [ text cname ] ]
- in
- DD.view model.dd Api.Normal
- (text "Select columns")
- (\_ -> [ ul []
- <| List.map item model.cols
- ++ [ li [ ] [ input [ type_ "button", class "submit", value "update", onClick Update ] [] ] ]
- ])
diff --git a/elm/Discussions/Edit.elm b/elm/Discussions/Edit.elm
index d478f52b..3f63369c 100644
--- a/elm/Discussions/Edit.elm
+++ b/elm/Discussions/Edit.elm
@@ -226,7 +226,7 @@ view model =
, formField "msg::Message"
[ TP.view "msg" model.msg Content 700 ([rows 12, cols 50] ++ GDE.valMsg)
[ b [ class "standout" ] [ text " (English please!) " ]
- , a [ href "/d9#3" ] [ text "Formatting" ]
+ , a [ href "/d9#4" ] [ text "Formatting" ]
]
]
]
diff --git a/elm/Discussions/PostEdit.elm b/elm/Discussions/PostEdit.elm
index a46638a4..d8de1aab 100644
--- a/elm/Discussions/PostEdit.elm
+++ b/elm/Discussions/PostEdit.elm
@@ -94,7 +94,7 @@ view model =
, formField "msg::Message"
[ TP.view "msg" model.msg Content 700 ([rows 12, cols 50] ++ GPE.valMsg)
[ b [ class "standout" ] [ text " (English please!) " ]
- , a [ href "/d9#3" ] [ text "Formatting" ]
+ , a [ href "/d9#4" ] [ text "Formatting" ]
]
]
]
diff --git a/elm/Discussions/Reply.elm b/elm/Discussions/Reply.elm
index 1769b06c..f465414d 100644
--- a/elm/Discussions/Reply.elm
+++ b/elm/Discussions/Reply.elm
@@ -75,7 +75,7 @@ view model =
[ TP.view "msg" model.msg Content 600 ([rows 4, cols 50] ++ GDR.valMsg)
[ b [] [ text "Quick reply" ]
, b [ class "standout" ] [ text " (English please!) " ]
- , a [ href "/d9#3" ] [ text "Formatting" ]
+ , a [ href "/d9#4" ] [ text "Formatting" ]
]
, submitButton "Submit" model.state True
]
diff --git a/elm/Lib/Api.elm b/elm/Lib/Api.elm
index 2ba63fa1..1953e8d8 100644
--- a/elm/Lib/Api.elm
+++ b/elm/Lib/Api.elm
@@ -40,6 +40,7 @@ showResponse res =
BadEmail -> "Unknown email address."
Bot -> "Invalid answer to the anti-bot question."
Taken -> "Username already taken, please choose a different name."
+ NameThrottle -> "You can only change your username once every 24 hours."
DoubleEmail -> "Email address already used for another account."
DoubleIP -> "You can only register one account from the same IP within 24 hours."
BadCurPass -> "Current password is invalid."
diff --git a/elm/Lib/Html.elm b/elm/Lib/Html.elm
index 3f13dbee..08648d55 100644
--- a/elm/Lib/Html.elm
+++ b/elm/Lib/Html.elm
@@ -210,3 +210,14 @@ platformIcon l = img [ class "platicon", src <| Ffi.urlStatic ++ "/f/plat/" ++ l
releaseTypeIcon : String -> Html m
releaseTypeIcon t = abbr [ class ("icons rt"++t), title (Maybe.withDefault "" <| lookup t T.releaseTypes) ] [ text " " ]
+
+-- Special values: -1 = "add to list", not 1-6 = unknown
+-- (Because why use the type system to encode special values?)
+ulistIcon : Int -> String -> Html m
+ulistIcon n lbl =
+ let fn = if n == -1 then "add"
+ else if n >= 1 && n <= 6 then "l" ++ String.fromInt n
+ else "unknown"
+ in img [ src (Ffi.urlStatic ++ "/f/list-" ++ fn ++ ".svg")
+ , class ("liststatus_icon "++fn), title lbl
+ ] []
diff --git a/elm/Reviews/Comment.elm b/elm/Reviews/Comment.elm
index 8b2399dd..0fa07fc0 100644
--- a/elm/Reviews/Comment.elm
+++ b/elm/Reviews/Comment.elm
@@ -44,7 +44,7 @@ view (state,_,content) =
[ TP.view "msg" content Content 600 ([rows 4, cols 50] ++ GRC.valMsg)
[ b [] [ text "Comment" ]
, b [ class "standout" ] [ text " (English please!) " ]
- , a [ href "/d9#3" ] [ text "Formatting" ]
+ , a [ href "/d9#4" ] [ text "Formatting" ]
]
, submitButton "Submit" state True
]
diff --git a/elm/Reviews/Edit.elm b/elm/Reviews/Edit.elm
index 5a917c3b..a97c514f 100644
--- a/elm/Reviews/Edit.elm
+++ b/elm/Reviews/Edit.elm
@@ -163,7 +163,7 @@ view model =
, tr [ class "newpart" ] [ td [ colspan 2 ] [ text "" ] ]
, formField "text::Review"
[ TP.view "sum" model.text Text 700 ([rows (if model.isfull then 30 else 10), cols 50] ++ GRE.valText)
- [ a [ href "/d9#3" ] [ text "BBCode formatting supported" ] ]
+ [ a [ href "/d9#4" ] [ text "BBCode formatting supported" ] ]
, div [ style "width" "700px", style "text-align" "right" ] <|
let num c s = if c then b [ class " standout" ] [ text s ] else text s
in
diff --git a/elm/StaffEdit.elm b/elm/StaffEdit.elm
index 14dd1abb..a0c09c53 100644
--- a/elm/StaffEdit.elm
+++ b/elm/StaffEdit.elm
@@ -119,7 +119,7 @@ update msg model =
LWikidata n-> ({ model | l_wikidata= n }, Cmd.none)
LTwitter s -> ({ model | l_twitter = s }, Cmd.none)
LAnidb s -> ({ model | l_anidb = if s == "" then Nothing else String.toInt s }, Cmd.none)
- LPixiv s -> ({ model | l_pixiv = Maybe.withDefault model.l_pixiv (String.toInt s) }, Cmd.none)
+ LPixiv s -> ({ model | l_pixiv = Maybe.withDefault 0 (String.toInt s) }, Cmd.none)
Desc m -> let (nm,nc) = TP.update m model.desc in ({ model | desc = nm }, Cmd.map Desc nc)
AliasDel i -> (validate { model | alias = delidx i model.alias }, Cmd.none)
diff --git a/elm/UList/LabelEdit.elm b/elm/UList/LabelEdit.elm
index f6a60d3b..d1bdc865 100644
--- a/elm/UList/LabelEdit.elm
+++ b/elm/UList/LabelEdit.elm
@@ -84,21 +84,26 @@ update msg model =
view : Model -> String -> Html Msg
view model txt =
let
- str = String.join ", " <| List.filterMap (\l -> if l.id /= 7 && Set.member l.id model.sel then Just l.label else Nothing) model.labels
+ lbl = List.intersperse (text ", ") <| List.filterMap (\l ->
+ if l.id /= 7 && Set.member l.id model.sel
+ then Just <| span []
+ [ if l.id <= 6 && txt /= "-" then ulistIcon l.id l.label else text ""
+ , text (" " ++ l.label) ]
+ else Nothing) model.labels
item l =
li [ ]
[ linkRadio (Set.member l.id model.tsel) (Toggle l.id True)
[ text l.label
, text " "
- , span [ class "spinner", classList [("invisible", Dict.get l.id model.state /= Just Api.Loading)] ] []
, case Dict.get l.id model.state of
+ Just Api.Loading -> span [ class "spinner" ] []
Just (Api.Error _) -> b [ class "standout" ] [ text "error" ] -- Need something better
- _ -> text ""
+ _ -> if l.id <= 6 then ulistIcon l.id l.label else text ""
]
]
in
DD.view model.dd
(if List.any (\s -> s == Api.Loading) <| Dict.values model.state then Api.Loading else Api.Normal)
- (text <| if str == "" then txt else str)
+ (if List.isEmpty lbl then text txt else span [] lbl)
(\_ -> [ ul [] <| List.map item <| List.filter (\l -> l.id /= 7) model.labels ])
diff --git a/elm/UList/Widget.elm b/elm/UList/Widget.elm
index ec4375a5..0b25fd24 100644
--- a/elm/UList/Widget.elm
+++ b/elm/UList/Widget.elm
@@ -248,15 +248,14 @@ view : Model -> Html Msg
view model =
let
icon () =
- let fn = if not model.onlist then "add"
+ let fn = if not model.onlist then -1
else List.range 1 6
|> List.filter (\n -> Set.member n model.labels.tsel)
|> List.maximum
- |> Maybe.map (\n -> "l" ++ String.fromInt n)
- |> Maybe.withDefault "unknown"
+ |> Maybe.withDefault 0
lbl = if not model.onlist then "Add to list"
else String.join ", " <| List.filterMap (\l -> if Set.member l.id model.labels.tsel && l.id /= 7 then Just l.label else Nothing) model.labels.labels
- in img [ src (Ffi.urlStatic ++ "/f/list-" ++ fn ++ ".svg"), class ("ulist-widget-icon liststatus_icon "++fn), title lbl, onClickN (Open True) ] []
+ in span [ onClickN (Open True), class "ulist-widget-icon" ] [ ulistIcon fn lbl ]
rel r =
case Dict.get r.rid model.relNfo of
diff --git a/elm/User/Edit.elm b/elm/User/Edit.elm
index 78598aee..7470401d 100644
--- a/elm/User/Edit.elm
+++ b/elm/User/Edit.elm
@@ -35,6 +35,7 @@ type alias Model =
, id : String
, title : String
, username : String
+ , nusername : Maybe String
, opts : GUE.RecvOpts
, admin : Maybe GUE.SendAdmin
, prefs : Maybe GUE.SendPrefs
@@ -50,6 +51,7 @@ init d =
, id = d.id
, title = d.title
, username = d.username
+ , nusername = Nothing
, opts = d.opts
, admin = d.admin
, prefs = d.prefs
@@ -99,7 +101,7 @@ type PassMsg
| Pass2 String
type Msg
- = Username String
+ = Username (Maybe String)
| Admin AdminMsg
| Prefs PrefMsg
| Pass PassMsg
@@ -180,7 +182,7 @@ updatePass msg model =
encode : Model -> GUE.Send
encode model =
{ id = model.id
- , username = model.username
+ , username = Maybe.withDefault model.username model.nusername
, admin = model.admin
, prefs = model.prefs
, password = Maybe.andThen (\p -> if p.cpass && p.pass1 == p.pass2 then Just { old = p.opass, new = p.pass1 } else Nothing) model.pass
@@ -193,7 +195,7 @@ update msg model =
Admin m -> ({ model | admin = Maybe.map (updateAdmin m) model.admin }, Cmd.none)
Prefs m -> ({ model | prefs = Maybe.map (updatePrefs m) model.prefs }, Cmd.none)
Pass m -> ({ model | pass = Maybe.map (updatePass m) model.pass, passNeq = False }, Cmd.none)
- Username s -> ({ model | username = s }, Cmd.none)
+ Username s -> ({ model | nusername = s }, Cmd.none)
Submit ->
if Maybe.withDefault False (Maybe.map (\p -> p.cpass && p.pass1 /= p.pass2) model.pass)
@@ -215,7 +217,6 @@ view model =
adminform m =
[ tr [ class "newpart" ] [ td [ colspan 2 ] [ text "Admin options" ] ]
- , perm False <| formField "username::Username" [ inputText "username" model.username Username GUE.valUsername ]
, formField "Permissions"
[ text "Fields marked with * indicate permissions assigned to new users by default", br_ 1
, perm False <| span [] [ inputButton "None" (Admin PermNone) [], inputButton "Default" (Admin PermDefault) [], br_ 1 ]
@@ -300,7 +301,21 @@ view model =
[ h1 [] [ text model.title ]
, table [ class "formtable" ] <|
[ tr [ class "newpart" ] [ td [ colspan 2 ] [ text "Account settings" ] ]
- , formField "Username" [ text model.username ]
+ , formField "Username"
+ [ text model.username, text " "
+ , if model.prefs == Nothing then text "" else label []
+ [ inputCheck "" (model.nusername /= Nothing) (\b -> Username <| if b then Just model.username else Nothing)
+ , text " change" ]
+ ]
+ , Maybe.withDefault (text "") <| Maybe.map (\u ->
+ tr [] [ K.node "td" [colspan 2] [("username_change", table []
+ [ formField "username::New username"
+ [ inputText "username" u (Username << Just) GUE.valUsername
+ , br [] []
+ , text "You may only change your username once a day. Your old username(s) will be displayed on your profile for a month after the change."
+ ]
+ ])] ]
+ ) model.nusername
, Maybe.withDefault (text "") <| Maybe.map (\m ->
formField "email::E-Mail" [ inputText "email" m.email (Prefs << EMail) GUE.valPrefsEmail ]
) model.prefs
diff --git a/elm/User/Login.elm b/elm/User/Login.elm
index c1c55dfe..11eb5dd5 100644
--- a/elm/User/Login.elm
+++ b/elm/User/Login.elm
@@ -63,7 +63,7 @@ type Msg
update : Msg -> Model -> (Model, Cmd Msg)
update msg model =
case msg of
- Username n -> ({ model | invalid = False, username = String.toLower n }, Cmd.none)
+ Username n -> ({ model | invalid = False, username = n }, Cmd.none)
Password n -> ({ model | invalid = False, password = n }, Cmd.none)
Newpass1 n -> ({ model | newpass1 = n, noteq = False }, Cmd.none)
Newpass2 n -> ({ model | newpass2 = n, noteq = False }, Cmd.none)
diff --git a/elm/User/Register.elm b/elm/User/Register.elm
index 915c9dbe..6d888629 100644
--- a/elm/User/Register.elm
+++ b/elm/User/Register.elm
@@ -50,7 +50,7 @@ type Msg
update : Msg -> Model -> (Model, Cmd Msg)
update msg model =
case msg of
- Username n -> ({ model | username = String.toLower n }, Cmd.none)
+ Username n -> ({ model | username = n }, Cmd.none)
EMail n -> ({ model | email = n }, Cmd.none)
VNs n -> ({ model | vns = Maybe.withDefault model.vns (String.toInt n) }, Cmd.none)
@@ -82,7 +82,7 @@ view model =
[ formField "username::Username"
[ inputText "username" model.username Username GUR.valUsername
, br_ 1
- , text "Preferred username. Must be lowercase, between 2 and 15 characters long and consist entirely of alphanumeric characters or a dash."
+ , text "Preferred username. Must be between 2 and 15 characters long and consist entirely of alphanumeric characters or a dash."
, text " Names that look like database identifiers (i.e. a single letter followed by several numbers) are also disallowed."
]
, formField "email::E-Mail"
diff --git a/elm/VNLengthVote.elm b/elm/VNLengthVote.elm
index 16fd3ff3..6aa0cf19 100644
--- a/elm/VNLengthVote.elm
+++ b/elm/VNLengthVote.elm
@@ -137,7 +137,7 @@ view model = div [class "lengthvotefrm"] <|
, text "- Exact measurements preferred, but rough estimates are accepted too."
, br [] []
, text "Play time: "
- , inputNumber "vnlengthhours" model.hours Hours [ Html.Attributes.min "0", Html.Attributes.max "500" ]
+ , inputNumber "vnlengthhours" model.hours Hours [ Html.Attributes.min "0", Html.Attributes.max "435" ]
, text " hours "
, inputNumber "" model.minutes Minutes [ Html.Attributes.min "0", Html.Attributes.max "59" ]
, text " minutes"
diff --git a/elm/checkall.js b/elm/checkall.js
index bc87bad4..60bf0760 100644
--- a/elm/checkall.js
+++ b/elm/checkall.js
@@ -5,10 +5,12 @@
* <input type="checkbox" class="checkall" name="$somename">
*
* Checking that will synchronize all other checkboxes with name="$somename".
+ * The "x-checkall" attribute may also be used instead of "name".
*/
document.querySelectorAll('input[type=checkbox].checkall').forEach(function(el) {
el.addEventListener('click', function() {
- document.querySelectorAll('input[type=checkbox][name="'+el.name+'"]').forEach(function(el2) {
+ var name = el.getAttribute('x-checkall') || el.name;
+ document.querySelectorAll('input[type=checkbox][name="'+name+'"], input[type=checkbox][x-checkall="'+name+'"]').forEach(function(el2) {
if(el2.checked != el.checked)
el2.click();
});
diff --git a/elm/elm.json b/elm/elm.json
index 3db9993a..6c052936 100644
--- a/elm/elm.json
+++ b/elm/elm.json
@@ -6,7 +6,6 @@
"elm-version": "0.19.1",
"dependencies": {
"direct": {
- "RomanErnst/erl": "2.1.1",
"elm/browser": "1.0.1",
"elm/core": "1.0.2",
"elm/file": "1.0.1",
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 1d0ac5cf..b8666b97 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -276,7 +276,6 @@ sub login {
cres $c, ['ok'], 'Login using client "%s" ver. %s', $c->{client}, $c->{clientver};
return;
} else {
- $arg->{username} = lc $arg->{username};
return cerr $c, auth => "Password too weak, please log in on the site and change your password"
if config->{password_db} && PWLookup::lookup(config->{password_db}, $arg->{password});
}
@@ -295,7 +294,7 @@ sub login_auth {
if $tm-AE::time() > config->{login_throttle}[1];
# Fetch user info
- cpg $c, 'SELECT id, encode(user_getscryptargs(id), \'hex\') FROM users WHERE username = $1', [ $arg->{username} ], sub {
+ cpg $c, 'SELECT id, username, encode(user_getscryptargs(id), \'hex\') FROM users WHERE lower(username) = lower($1)', [ $arg->{username} ], sub {
login_verify($c, $arg, $tm, $_[0]);
};
};
@@ -307,7 +306,8 @@ sub login_verify {
return cerr $c, auth => "No user with the name '$arg->{username}'" if $res->nRows == 0;
my $uid = $res->value(0,0);
- my $sargs = $res->value(0,1);
+ my $username = $res->value(0,1);
+ my $sargs = $res->value(0,2);
return cerr $c, auth => "Account disabled" if !$sargs || length($sargs) != 14*2;
my $token = urandom(20);
@@ -317,16 +317,16 @@ sub login_verify {
cpg $c, 'SELECT user_login($1, decode($2, \'hex\'), decode($3, \'hex\'))', [ $uid, unpack('H*', $passwd), unpack('H*', $token) ], sub {
if($_[0]->nRows == 1 && ($_[0]->value(0,0)||'') =~ /t/) {
$c->{uid} = $uid;
- $c->{username} = $arg->{username};
+ $c->{username} = $username;
$c->{client} = $arg->{client};
$c->{clientver} = $arg->{clientver};
pg_cmd 'SELECT user_logout($1, decode($2, \'hex\'))', [ $uid, unpack('H*', $token) ];
- cres $c, ['ok'], 'Successful login by %s (%s) using client "%s" ver. %s', $arg->{username}, $c->{uid}, $c->{client}, $c->{clientver};
+ cres $c, ['ok'], 'Successful login by %s (%s) using client "%s" ver. %s', $username, $c->{uid}, $c->{client}, $c->{clientver};
} else {
my @a = ( $tm + config->{login_throttle}[0], norm_ip($c->{ip}) );
pg_cmd 'UPDATE login_throttle SET timeout = to_timestamp($1) WHERE ip = $2', \@a;
pg_cmd 'INSERT INTO login_throttle (ip, timeout) SELECT $2, to_timestamp($1) WHERE NOT EXISTS(SELECT 1 FROM login_throttle WHERE ip = $2)', \@a;
- cerr $c, auth => "Wrong password for user '$arg->{username}'";
+ cerr $c, auth => "Wrong password for user '$username'";
}
};
}
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 382031e2..7c0bb1ac 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -83,6 +83,8 @@ my %dailies = (
# takes about 5 seconds, OK
vnstats => 'SELECT update_vnvotestats()',
+ lengthcache => 'SELECT update_vn_length_cache(NULL)',
+
# takes about 10 seconds, OK
imagecache => 'SELECT update_images_cache(NULL)',
diff --git a/lib/VNDB/ExtLinks.pm b/lib/VNDB/ExtLinks.pm
index ce465407..b3caab71 100644
--- a/lib/VNDB/ExtLinks.pm
+++ b/lib/VNDB/ExtLinks.pm
@@ -89,11 +89,6 @@ our %LINKS = (
, fmt2 => sub { config->{dlsite_url} && sprintf config->{dlsite_url}, shift->{l_dlsite_shop}||'home' }
, regex => qr{(?:www\.)?dlsite\.com/.*/(?:dlaf/=/link/work/aid/.*/id|work/=/product_id)/([VR]J[0-9]{6}).*}
, patt => 'https://www.dlsite.com/<store>/work/=/product_id/<VJ or RJ-code>' },
- l_dlsiteen => { label => 'DLsite (eng)'
- , fmt => 'https://www.dlsite.com/eng/work/=/product_id/%s.html'
- , fmt2 => sub { config->{dlsite_url} && sprintf config->{dlsite_url}, shift->{l_dlsiteen_shop}||'eng' }
- , regex => qr{(?:www\.)?dlsite\.com/.*/(?:dlaf/=/link/work/aid/.*/id|work/=/product_id)/([VR]E[0-9]{6}).*}
- , patt => 'https://www.dlsite.com/<store>/work/=/product_id/<VE or RE-code>' },
l_gog => { label => 'GOG'
, fmt => 'https://www.gog.com/game/%s'
, regex => qr{(?:www\.)?gog\.com/game/([a-z0-9_]+).*} },
@@ -111,7 +106,7 @@ our %LINKS = (
, regex => qr{(?:www\.)?(?:jlist|jbox)\.com/(?:.+/)?([a-z0-9-]*[0-9][a-z0-9-]*)} },
l_jastusa => { label => 'JAST USA'
, fmt => 'https://jastusa.com/games/%s'
- , regex => qr{(?:www\.)?jastusa\.com/games/([a-z0-9-]+)} },
+ , regex => qr{(?:www\.)?jastusa\.com/games/([a-z0-9_-]+)} },
l_fakku => { label => 'Fakku'
, fmt => 'https://www.fakku.net/games/%s'
, regex => qr{(?:www\.)?fakku\.(?:net|com)/games/([^/]+)(?:[/\?].*)?} },
@@ -127,6 +122,10 @@ our %LINKS = (
l_freem => { label => 'Freem!'
, fmt => 'https://www.freem.ne.jp/win/game/%d'
, regex => qr{(?:www\.)?freem\.ne\.jp/win/game/([0-9]+)} },
+ l_freegame => { label => 'Freegame Mugen'
+ , fmt => 'https://freegame-mugen.jp/%s.html'
+ , regex => qr{(?:www\.)?freegame-mugen\.jp/([^/]+/game_[0-9]+)\.html}
+ , patt => 'https://freegame-mugen.jp/<genre>/game_<id>.html' },
l_novelgam => { label => 'NovelGame'
, fmt => 'https://novelgame.jp/games/show/%d'
, regex => qr{(?:www\.)?novelgame\.jp/games/show/([0-9]+)} },
@@ -168,6 +167,8 @@ our %LINKS = (
l_nutaku => { label => 'Nutaku'
, fmt => 'https://www.nutaku.net/games/%s/'
, regex => qr{(?:www\.)?nutaku\.net/games/(?:mobile/|download/|app/)?([a-z0-9-]+)/?} }, # The section part does sometimes link to different pages, but it's the same game and the non-section link always works.
+ # deprecated
+ l_dlsiteen => { label => 'DLsite (eng)', fmt => 'https://www.dlsite.com/eng/work/=/product_id/%s.html' },
},
s => {
l_site => { label => 'Official website', fmt => '%s' },
@@ -286,6 +287,7 @@ sub enrich_extlinks {
l 'l_googplay';
l 'l_animateg';
l 'l_freem';
+ l 'l_freegame';
l 'l_novelgam';
l 'l_gyutto';
l 'l_digiket';
diff --git a/lib/VNDB/Types.pm b/lib/VNDB/Types.pm
index 8b4ae92a..e13f8e33 100644
--- a/lib/VNDB/Types.pm
+++ b/lib/VNDB/Types.pm
@@ -167,12 +167,12 @@ hash CREDIT_TYPE =>
hash VN_LENGTH =>
- 0 => { txt => 'Unknown', time => '' },
- 1 => { txt => 'Very short', time => '< 2 hours' },
- 2 => { txt => 'Short', time => '2 - 10 hours' },
- 3 => { txt => 'Medium', time => '10 - 30 hours' },
- 4 => { txt => 'Long', time => '30 - 50 hours' },
- 5 => { txt => 'Very long', time => '> 50 hours' };
+ 0 => { txt => 'Unknown', time => '', low => 0, high => 0 },
+ 1 => { txt => 'Very short', time => '< 2 hours', low => 1, high => 2*60 },
+ 2 => { txt => 'Short', time => '2 - 10 hours', low => 2*60, high => 10*60 },
+ 3 => { txt => 'Medium', time => '10 - 30 hours', low => 10*60, high => 30*60 },
+ 4 => { txt => 'Long', time => '30 - 50 hours', low => 30*60, high => 50*60 },
+ 5 => { txt => 'Very long', time => '> 50 hours', low => 50*60, high => 32767 };
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm
index 82030ffd..5cc769dd 100644
--- a/lib/VNWeb/AdvSearch.pm
+++ b/lib/VNWeb/AdvSearch.pm
@@ -312,7 +312,8 @@ f v => 2 => 'lang', { enum => \%LANGUAGE }, '=' => sub { sql 'v.c_language
f v => 3 => 'olang', { enum => \%LANGUAGE }, '=' => sub { sql 'v.olang =', \$_ };
f v => 4 => 'platform', { enum => \%PLATFORM }, '=' => sub { sql 'v.c_platforms && ARRAY', \$_, '::platform[]' };
f v => 6 => 'developer-id',{ vndbid => 'p' }, '=' => sub { sql 'v.c_developers && ARRAY', \$_, '::vndbid[]' };
-f v => 5 => 'length', { uint => 1, enum => \%VN_LENGTH }, '=' => sub { sql 'v.length =', \$_ };
+f v => 5 => 'length', { uint => 1, enum => \%VN_LENGTH },
+ '=' => sub { sql 'COALESCE(v.c_length BETWEEN', \$VN_LENGTH{$_}{low}, 'AND', \$VN_LENGTH{$_}{high}, ', v.length =', \$_, ')' };
f v => 7 => 'released', { fuzzyrdate => 1 }, sql => sub { sql 'v.c_released', $_[0], \($_ == 1 ? strftime('%Y%m%d', gmtime) : $_) };
f v => 9 => 'popularity',{ uint => 1, range => [ 0, 100] }, sql => sub { sql 'v.c_popularity', $_[0], \($_*100) };
f v => 10 => 'rating', { uint => 1, range => [10, 100] }, sql => sub { sql 'v.c_rating', $_[0], \($_*10) };
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm
index 06ca88ec..285367ca 100644
--- a/lib/VNWeb/Auth.pm
+++ b/lib/VNWeb/Auth.pm
@@ -175,7 +175,7 @@ sub login {
my($self, $user, $pass, $pretend) = @_;
return 0 if $self->uid || !$user || !$pass;
- my $uid = tuwf->dbVali('SELECT id FROM users WHERE username =', \$user);
+ my $uid = tuwf->dbVali('SELECT id FROM users WHERE lower(username) = lower(', \$user, ')');
return 0 if !$uid;
my $encpass = $self->_encpass($uid, $pass);
return 0 if !$encpass;
diff --git a/lib/VNWeb/Elm.pm b/lib/VNWeb/Elm.pm
index 1c02a5fe..da98ae2b 100644
--- a/lib/VNWeb/Elm.pm
+++ b/lib/VNWeb/Elm.pm
@@ -48,6 +48,7 @@ our %apis = (
BadEmail => [], # Unknown email address in password reset form
Bot => [], # User didn't pass bot verification
Taken => [], # Username already taken
+ NameThrottle => [], # Username change throttled
DoubleEmail => [], # Account with same email already exists
DoubleIP => [], # Account with same IP already exists
BadCurPass => [], # Current password is incorrect when changing password
diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm
index 0b77408e..b1517c8a 100644
--- a/lib/VNWeb/HTML.pm
+++ b/lib/VNWeb/HTML.pm
@@ -86,7 +86,7 @@ sub user_ {
my $uniname = f 'uniname_can' && f 'uniname';
a_ href => '/'.f('id'),
$fancy && $uniname ? (title => f('name'), $uniname) :
- (!$fancy && $uniname ? (title => $uniname) : (), $capital ? ucfirst f 'name' : f 'name');
+ (!$fancy && $uniname ? (title => $uniname) : (), $capital ? f 'name' : f 'name');
txt_ '⭐' if $fancy && f 'support_can' && f 'support_enabled';
}
diff --git a/lib/VNWeb/Misc/HomePage.pm b/lib/VNWeb/Misc/HomePage.pm
index 64238e1c..97d4b63b 100644
--- a/lib/VNWeb/Misc/HomePage.pm
+++ b/lib/VNWeb/Misc/HomePage.pm
@@ -70,7 +70,7 @@ sub recent_db_posts_ {
FROM threads t
JOIN threads_boards tb ON tb.tid = t.id AND tb.type = \'an\'
JOIN threads_posts tp ON tp.tid = t.id AND tp.num = 1
- WHERE NOT t.hidden AND NOT t.private AND tp.date >', sql_fromtime(time-2*30*24*3600), '
+ WHERE NOT t.hidden AND NOT t.private AND tp.date >', sql_fromtime(time-30*24*3600), '
ORDER BY tb.tid DESC
LIMIT 1+1'
);
@@ -252,12 +252,10 @@ TUWF::get qr{/}, sub {
};
screens_;
};
- div_ class => 'homepage threelayout', sub {
+ div_ class => 'homepage', sub {
div_ \&recent_changes_;
div_ \&recent_db_posts_;
div_ \&recent_vn_posts_;
- };
- div_ class => 'homepage threelayout', sub {
div_ sub { reviews_ };
div_ sub { releases_ 0 };
div_ sub { releases_ 1 };
diff --git a/lib/VNWeb/Producers/Page.pm b/lib/VNWeb/Producers/Page.pm
index 73b7f798..ad454fdf 100644
--- a/lib/VNWeb/Producers/Page.pm
+++ b/lib/VNWeb/Producers/Page.pm
@@ -72,7 +72,7 @@ sub rel_ {
FROM releases r
JOIN releases_producers rp ON rp.id = r.id
WHERE rp.pid =', \$p->{id}, ' AND NOT r.hidden
- ORDER BY r.released, r.id
+ ORDER BY r.released, r.title, r.id
');
enrich_extlinks r => $r;
enrich_release $r;
@@ -126,7 +126,7 @@ sub vns_ {
GROUP BY rv.vid
) rels(vid, developer, publisher, released) ON rels.vid = v.id
WHERE NOT v.hidden
- ORDER BY rels.released
+ ORDER BY rels.released, v.title
');
h1_ 'Visual Novels';
diff --git a/lib/VNWeb/Releases/Lib.pm b/lib/VNWeb/Releases/Lib.pm
index 6f9bafe8..a008c8d1 100644
--- a/lib/VNWeb/Releases/Lib.pm
+++ b/lib/VNWeb/Releases/Lib.pm
@@ -39,7 +39,7 @@ sub release_extlinks_ {
return if !$r->{extlinks}->@*;
if($r->{extlinks}->@* == 1 && $r->{website}) {
- a_ href => $r->{website}, sub {
+ a_ href => $r->{extlinks}[0][1], sub {
abbr_ class => 'icons external', title => 'Official website', '';
};
return
diff --git a/lib/VNWeb/Staff/Edit.pm b/lib/VNWeb/Staff/Edit.pm
index 24311d89..82166176 100644
--- a/lib/VNWeb/Staff/Edit.pm
+++ b/lib/VNWeb/Staff/Edit.pm
@@ -19,8 +19,8 @@ my $FORM = {
l_site => { required => 0, default => '', weburl => 1 },
l_wikidata => { required => 0, uint => 1, max => (1<<31)-1 },
l_twitter => { required => 0, default => '', regex => qr/^\S+$/, maxlength => 16 },
- l_anidb => { required => 0, id => 1, default => undef },
- l_pixiv => { required => 0, id => 1, default => 0 },
+ l_anidb => { required => 0, uint => 1, max => (1<<31)-1, default => undef },
+ l_pixiv => { required => 0, uint => 1, max => (1<<31)-1, default => 0 },
hidden => { anybool => 1 },
locked => { anybool => 1 },
diff --git a/lib/VNWeb/TableOpts.pm b/lib/VNWeb/TableOpts.pm
index fa976f40..30f5f52b 100644
--- a/lib/VNWeb/TableOpts.pm
+++ b/lib/VNWeb/TableOpts.pm
@@ -118,6 +118,12 @@ sub tableopts {
}
+# COMPAT: For old URLs, we assume that this validation is used on the 's'
+# parameter, so we can accept two formats:
+# - "s=$compat_sort_column/$order"
+# - "s=$compat_sort_column&o=$order"
+# In the latter case, the validation will use reqGet() to get the 'o'
+# parameter.
TUWF::set('custom_validations')->{tableopts} = sub {
my($t) = @_;
+{ onerror => sub {
@@ -125,11 +131,12 @@ TUWF::set('custom_validations')->{tableopts} = sub {
bless([$d // $t->{default},$t], __PACKAGE__)
}, func => sub {
my $obj = bless [undef, $t], __PACKAGE__;
- my $col = [grep $_->{compat} && $_->{compat} eq $_[0], values $t->{columns}->%*]->[0];
+ my($val,$ord) = $_[0] =~ m{^([^/]+)/([ad])$} ? ($1,$2) : ($_[0],undef);
+ my $col = [grep $_->{compat} && $_->{compat} eq $val, values $t->{columns}->%*]->[0];
if($col && defined $col->{sort_id}) {
$obj->[0] = $t->{default};
$obj->set_sort_col_id($col->{sort_id});
- my $ord = tuwf->reqGet('o');
+ $ord //= tuwf->reqGet('o');
$obj->set_order($ord && $ord eq 'd' ? 1 : 0);
} else {
$obj->[0] = _dec($_[0]) // return 0;
@@ -189,6 +196,15 @@ sub sql_order {
# Returns whether the given column key is visible.
sub vis { $_[0][0] & (1 << (12+$_[0][1]{columns}{$_[1]}{vis_id})) }
+# Given a list of column names, return a new object with only these columns visible
+sub vis_param {
+ my($self, @cols) = @_;
+ my $n = bless [@$self], __PACKAGE__;
+ $n->[0] = $n->[0] & 0b1111_1111_1111;
+ $n->[0] |= 1 << (12+$self->[1]{columns}{$_}{vis_id}) for @cols;
+ $n;
+}
+
my $FORM_OUT = form_compile any => {
save => { required => 0 },
diff --git a/lib/VNWeb/ULists/Elm.pm b/lib/VNWeb/ULists/Elm.pm
index c88156df..ab2839e0 100644
--- a/lib/VNWeb/ULists/Elm.pm
+++ b/lib/VNWeb/ULists/Elm.pm
@@ -235,14 +235,9 @@ elm_api UListWidget => $WIDGET, { uid => { vndbid => 'u' }, vid => { vndbid => '
our %SAVED_OPTS = (
- # Labels
l => { onerror => [], type => 'array', scalar => 1, values => { int => 1 } },
mul => { anybool => 1 },
- # Sort column & order
- s => { onerror => 'title', enum => [qw[ title label vote voted added modified started finished rel rating ]] },
- o => { onerror => 'a', enum => ['a', 'd'] },
- # Visible columns
- c => { onerror => [], type => 'array', scalar => 1, values => { enum => [qw[ label vote voted added modified started finished rel rating ]] } },
+ s => { onerror => '' }, # TableOpts query string
);
my $SAVED_OPTS = {
diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm
index 35703aa1..4d279b0a 100644
--- a/lib/VNWeb/ULists/List.pm
+++ b/lib/VNWeb/ULists/List.pm
@@ -5,24 +5,113 @@ use VNWeb::ULists::Lib;
use VNWeb::Releases::Lib;
+my $TABLEOPTS = tableopts
+ title => {
+ name => 'Title',
+ sort_sql => 'v.title',
+ sort_id => 0,
+ compat => 'title',
+ sort_default => 'asc',
+ },
+ voted => {
+ name => 'Vote date',
+ sort_sql => 'uv.vote_date',
+ sort_id => 1,
+ vis_id => 0,
+ compat => 'voted'
+ },
+ vote => {
+ name => 'Vote',
+ sort_sql => 'uv.vote',
+ sort_id => 2,
+ vis_id => 1,
+ compat => 'vote'
+ },
+ rating => {
+ name => 'Rating',
+ sort_sql => 'v.c_rating',
+ sort_id => 3,
+ vis_id => 2,
+ compat => 'rating'
+ },
+ label => {
+ name => 'Labels',
+ sort_sql => sql('ARRAY(SELECT ul.label FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl <> ', \7, ')'),
+ sort_id => 4,
+ vis_id => 3,
+ compat => 'label'
+ },
+ added => {
+ name => 'Added',
+ sort_sql => 'uv.added',
+ sort_id => 5,
+ vis_id => 4,
+ compat => 'added'
+ },
+ modified => {
+ name => 'Modified',
+ sort_sql => 'uv.lastmod',
+ sort_id => 6,
+ vis_id => 5,
+ compat => 'modified'
+ },
+ started => {
+ name => 'Start date',
+ sort_sql => 'uv.started',
+ sort_id => 7,
+ vis_id => 6,
+ compat => 'started'
+ },
+ finished => {
+ name => 'Finish date',
+ sort_sql => 'uv.finished',
+ sort_id => 8,
+ vis_id => 7,
+ compat => 'finished'
+ },
+ rel => {
+ name => 'Release date',
+ sort_sql => 'v.c_released',
+ sort_id => 9,
+ vis_id => 8,
+ compat => 'rel'
+ };
+
+
sub opt {
my($u, $filtlabels) = @_;
+ # Note that saved defaults may still use the old query format, which is
+ # { s => $sort_column, o => $order, c => [$visible_columns] }
my sub load { my $o = $u->{"ulist_$_[0]"}; ($o && eval { JSON::XS->new->decode($o) } or {})->%* };
+ state $s_default = tuwf->compile({ tableopts => $TABLEOPTS })->validate(undef)->data;
+ state $s_vnlist = $s_default->sort_param(title => 'a')->vis_param(qw/label vote added started finished/)->query_encode;
+ state $s_votes = $s_default->sort_param(voted => 'd')->vis_param(qw/vote voted/)->query_encode;
+ state $s_wishlist = $s_default->sort_param(title => 'a')->vis_param(qw/label added/)->query_encode;
+
my $opt =
# Presets
- tuwf->reqGet('vnlist') ? { mul => 0, p => 1, l => [1,2,3,4,7,-1,0], s => 'title', o => 'a', c => [qw/label vote added started finished/], load 'vnlist' } :
- tuwf->reqGet('votes') ? { mul => 0, p => 1, l => [7], s => 'voted', o => 'd', c => [qw/vote voted/], load 'votes' } :
- tuwf->reqGet('wishlist') ? { mul => 0, p => 1, l => [5], s => 'title', o => 'a', c => [qw/label added/], load 'wish' } :
+ tuwf->reqGet('vnlist') ? { mul => 0, p => 1, l => [1,2,3,4,7,-1,0], s => $s_vnlist, load 'vnlist' } :
+ tuwf->reqGet('votes') ? { mul => 0, p => 1, l => [7], s => $s_votes, load 'votes' } :
+ tuwf->reqGet('wishlist') ? { mul => 0, p => 1, l => [5], s => $s_wishlist, load 'wish' } :
# Full options
tuwf->validate(get =>
p => { upage => 1 },
ch=> { onerror => undef, enum => [ 'a'..'z', 0 ] },
q => { onerror => undef },
- %VNWeb::ULists::Elm::SAVED_OPTS
+ %VNWeb::ULists::Elm::SAVED_OPTS,
+ # Compat for old URLs
+ o => { onerror => undef, enum => ['a', 'd'] },
+ c => { onerror => undef, type => 'array', scalar => 1, values => { enum => [qw[ label vote voted added modified started finished rel rating ]] } },
)->data;
+ $opt->{s} .= "/$opt->{o}" if $opt->{o};
+ $opt->{s} = tuwf->compile({ tableopts => $TABLEOPTS })->validate($opt->{s})->data;
+ $opt->{s} = $opt->{s}->vis_param($opt->{c}->@*) if $opt->{c};
+ delete $opt->{o};
+ delete $opt->{c};
+
# $labels only includes labels we are allowed to see, getting rid of any labels in 'l' that aren't in $labels ensures we only filter on visible labels
my %accessible_labels = map +($_->{id}, 1), @$filtlabels;
my %opt_l = map +($_, 1), grep $accessible_labels{$_}, $opt->{l}->@*;
@@ -42,43 +131,38 @@ sub filters_ {
txt_ " ($_->{count})";
}
- form_ method => 'get', sub {
- input_ type => 'hidden', name => 's', value => $opt->{s};
- input_ type => 'hidden', name => 'o', value => $opt->{o};
- input_ type => 'hidden', name => 'ch', value => $opt->{ch} if defined $opt->{ch};
- input_ type => 'hidden', name => 'c', value => $_ for $opt->{c}->@*;
- p_ class => 'labelfilters', sub {
- input_ type => 'text', class => 'text', name => 'q', value => $opt->{q}||'', style => 'width: 500px', placeholder => 'Search', tabindex => 10;
- br_;
- # XXX: Rather silly that everything in this form is a form element except for the alphabet filter. Meh, behavior seems intuitive enough.
- span_ class => 'browseopts', sub {
- a_ href => $url->(ch => $_, p => undef), ($_//'') eq ($opt->{ch}//'') ? (class => 'optselected') : (), !defined($_) ? 'ALL' : $_ ? uc $_ : '#'
- for (undef, 'a'..'z', 0);
+ input_ type => 'hidden', name => 'ch', value => $opt->{ch} if defined $opt->{ch};
+ p_ class => 'labelfilters', sub {
+ input_ type => 'text', class => 'text', name => 'q', value => $opt->{q}||'', style => 'width: 500px', placeholder => 'Search', tabindex => 10;
+ br_;
+ # XXX: Rather silly that everything in this form is a form element except for the alphabet filter. Meh, behavior seems intuitive enough.
+ span_ class => 'browseopts', sub {
+ a_ href => $url->(ch => $_, p => undef), ($_//'') eq ($opt->{ch}//'') ? (class => 'optselected') : (), !defined($_) ? 'ALL' : $_ ? uc $_ : '#'
+ for (undef, 'a'..'z', 0);
+ };
+ br_;
+ span_ class => 'linkradio', sub {
+ join_ sub { em_ ' / ' }, \&lblfilt_, grep $_->{id} < 10, @$filtlabels;
+
+ span_ class => 'hidden', sub {
+ em_ ' || ';
+ input_ type => 'checkbox', name => 'mul', value => 1, id => 'form_l_multi', tabindex => 10, $opt->{mul} ? (checked => 'checked') : ();
+ label_ for => 'form_l_multi', 'Multi-select';
};
+ debug_ $filtlabels;
+ };
+ my @cust = grep $_->{id} >= 10, @$filtlabels;
+ if(@cust) {
br_;
span_ class => 'linkradio', sub {
- join_ sub { em_ ' / ' }, \&lblfilt_, grep $_->{id} < 10, @$filtlabels;
-
- span_ class => 'hidden', sub {
- em_ ' || ';
- input_ type => 'checkbox', name => 'mul', value => 1, id => 'form_l_multi', tabindex => 10, $opt->{mul} ? (checked => 'checked') : ();
- label_ for => 'form_l_multi', 'Multi-select';
- };
- debug_ $filtlabels;
- };
- my @cust = grep $_->{id} >= 10, @$filtlabels;
- if(@cust) {
- br_;
- span_ class => 'linkradio', sub {
- join_ sub { em_ ' / ' }, \&lblfilt_, @cust;
- }
+ join_ sub { em_ ' / ' }, \&lblfilt_, @cust;
}
- br_;
- input_ type => 'submit', class => 'submit', tabindex => 10, value => 'Update filters';
- input_ type => 'button', class => 'submit', tabindex => 10, id => 'managelabels', value => 'Manage labels' if $own;
- input_ type => 'button', class => 'submit', tabindex => 10, id => 'savedefault', value => 'Save as default' if $own;
- input_ type => 'button', class => 'submit', tabindex => 10, id => 'exportlist', value => 'Export' if $own;
- };
+ }
+ br_;
+ input_ type => 'submit', class => 'submit', tabindex => 10, value => 'Update filters';
+ input_ type => 'button', class => 'submit', tabindex => 10, id => 'managelabels', value => 'Manage labels' if $own;
+ input_ type => 'button', class => 'submit', tabindex => 10, id => 'savedefault', value => 'Save as default' if $own;
+ input_ type => 'button', class => 'submit', tabindex => 10, id => 'exportlist', value => 'Export' if $own;
};
}
@@ -89,7 +173,7 @@ sub vn_ {
my %labels = map +($_,1), $v->{labels}->@*;
td_ class => 'tc1', sub {
- input_ type => 'checkbox', class => 'checkhidden', name => 'collapse_vid', id => 'collapse_vid'.$v->{id}, value => 'collapsed_vid'.$v->{id};
+ input_ type => 'checkbox', class => 'checkhidden', 'x-checkall' => 'collapse_vid', id => 'collapse_vid'.$v->{id}, value => 'collapsed_vid'.$v->{id};
label_ for => 'collapse_vid'.$v->{id}, sub {
my $obtained = grep $_->{status} == 2, $v->{rels}->@*;
my $total = $v->{rels}->@*;
@@ -108,19 +192,19 @@ sub vn_ {
};
};
- td_ class => 'tc_voted', $v->{vote_date} ? fmtdate $v->{vote_date}, 'compact' : '-' if in voted => $opt->{c};
+ td_ class => 'tc_voted', $v->{vote_date} ? fmtdate $v->{vote_date}, 'compact' : '-' if $opt->{s}->vis('voted');
td_ mkclass(tc_vote => 1, compact => $own, stealth => $own), sub {
txt_ fmtvote $v->{vote} if !$own;
elm_ 'UList.VoteEdit' => $VNWeb::ULists::Elm::VNVOTE, { uid => $uid, vid => $v->{id}, vote => fmtvote($v->{vote}) }, sub {
div_ @_, fmtvote $v->{vote}
} if $own && ($v->{vote} || sprintf('%08d', $v->{c_released}||0) < strftime '%Y%m%d', gmtime);
- } if in vote => $opt->{c};
+ } if $opt->{s}->vis('vote');
td_ class => 'tc_rating', sub {
txt_ sprintf '%.2f', ($v->{c_rating}||0)/100;
b_ class => 'grayedout', sprintf ' (%d)', $v->{c_votecount};
- } if in rating => $opt->{c};
+ } if $opt->{s}->vis('rating');
td_ class => 'tc_labels', sub {
my @l = grep $labels{$_->{id}} && $_->{id} != 7, @$labels;
@@ -132,31 +216,31 @@ sub vn_ {
} else {
txt_ $txt;
}
- } if in label => $opt->{c};
+ } if $opt->{s}->vis('label');
td_ class => 'tc_title', sub {
a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 70;
b_ class => 'grayedout', id => 'ulist_notes_'.$v->{id}, $v->{notes} if $v->{notes} || $own;
};
- td_ class => 'tc_added', fmtdate $v->{added}, 'compact' if in added => $opt->{c};
- td_ class => 'tc_modified', fmtdate $v->{lastmod}, 'compact' if in modified => $opt->{c};
+ td_ class => 'tc_added', fmtdate $v->{added}, 'compact' if $opt->{s}->vis('added');
+ td_ class => 'tc_modified', fmtdate $v->{lastmod}, 'compact' if $opt->{s}->vis('modified');
td_ class => 'tc_started', sub {
txt_ $v->{started}||'' if !$own;
elm_ 'UList.DateEdit' => $VNWeb::ULists::Elm::VNDATE, { uid => $uid, vid => $v->{id}, date => $v->{started}||'', start => 1 }, sub {
div_ @_, $v->{started}||''
} if $own;
- } if in started => $opt->{c};
+ } if $opt->{s}->vis('started');
td_ class => 'tc_finished', sub {
txt_ $v->{finished}||'' if !$own;
elm_ 'UList.DateEdit' => $VNWeb::ULists::Elm::VNDATE, { uid => $uid, vid => $v->{id}, date => $v->{finished}||'', start => 0 }, sub {
div_ @_, $v->{finished}||''
} if $own;
- } if in finished => $opt->{c};
+ } if $opt->{s}->vis('finished');
- td_ class => 'tc_rel', sub { rdate_ $v->{c_released} } if in rel => $opt->{c};
+ td_ class => 'tc_rel', sub { rdate_ $v->{c_released} } if $opt->{s}->vis('rel');
};
tr_ mkclass(hidden => 1, 'collapsed_vid'.$v->{id} => 1, odd => $n % 2 == 0), sub {
@@ -191,7 +275,7 @@ sub listing_ {
my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN vn v ON v.id = uv.vid WHERE', $where);
- my $lst = tuwf->dbPagei({ page => $opt->{p}, results => 50 },
+ my $lst = tuwf->dbPagei({ page => $opt->{p}, results => $opt->{s}->results },
'SELECT v.id, v.title, v.original, uv.vote, uv.notes, uv.started, uv.finished, v.c_rating, v.c_votecount, v.c_released
,', sql_totime('uv.added'), ' as added
,', sql_totime('uv.lastmod'), ' as lastmod
@@ -199,18 +283,7 @@ sub listing_ {
FROM ulist_vns uv
JOIN vn v ON v.id = uv.vid
WHERE', $where, '
- ORDER BY', {
- title => 'v.title',
- label => sql('ARRAY(SELECT ul.label FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl <> ', \7, ')'),
- vote => 'uv.vote',
- voted => 'uv.vote_date',
- added => 'uv.added',
- modified => 'uv.lastmod',
- started => 'uv.started',
- finished => 'uv.finished',
- rel => 'v.c_released',
- rating => 'v.c_rating',
- }->{$opt->{s}}, $opt->{o} eq 'd' ? 'DESC' : 'ASC', 'NULLS LAST, v.title'
+ ORDER BY', $opt->{s}->sql_order(), 'NULLS LAST, v.title'
);
enrich_flatten labels => id => vid => sql('SELECT vid, lbl FROM ulist_vns_labels WHERE uid =', \$uid, 'AND vid IN'), $lst;
@@ -226,42 +299,30 @@ sub listing_ {
}, $lst;
enrich_release_elm map $_->{rels}, @$lst;
- # TODO: Thumbnail view?
- paginate_ $url, $opt->{p}, [ $count, 50 ], 't', sub {
- elm_ ColSelect => 'raw', [ $url->(), [
- [ voted => 'Vote date' ],
- [ vote => 'Vote' ],
- [ rating => 'Rating' ],
- [ label => 'Labels' ],
- [ added => 'Added' ],
- [ modified => 'Modified' ],
- [ started => 'Start date' ],
- [ finished => 'Finish date' ],
- [ rel => 'Release date' ],
- ] ];
- };
+ paginate_ $url, $opt->{p}, [$count, $opt->{s}->results], 't', sub { $opt->{s}->elm_ };
div_ class => 'mainbox browse ulist', sub {
table_ sub {
thead_ sub { tr_ sub {
td_ class => 'tc1', sub {
- input_ type => 'checkbox', class => 'checkall', name => 'collapse_vid', id => 'collapse_vid';
+ # TODO: these checkboxes shouldn't be included in the query string
+ input_ type => 'checkbox', class => 'checkall', 'x-checkall' => 'collapse_vid', id => 'collapse_vid';
label_ for => 'collapse_vid', sub { txt_ 'Opt' };
};
- td_ class => 'tc_voted', sub { txt_ 'Vote date'; sortable_ 'voted', $opt, $url } if in voted => $opt->{c};
- td_ class => 'tc_vote', sub { txt_ 'Vote'; sortable_ 'vote', $opt, $url } if in vote => $opt->{c};
- td_ class => 'tc_rating', sub { txt_ 'Rating'; sortable_ 'rating', $opt, $url } if in rating => $opt->{c};
- td_ class => 'tc_labels', sub { txt_ 'Labels'; sortable_ 'label', $opt, $url } if in label => $opt->{c};
+ td_ class => 'tc_voted', sub { txt_ 'Vote date'; sortable_ 'voted', $opt, $url } if $opt->{s}->vis('voted');
+ td_ class => 'tc_vote', sub { txt_ 'Vote'; sortable_ 'vote', $opt, $url } if $opt->{s}->vis('vote');
+ td_ class => 'tc_rating', sub { txt_ 'Rating'; sortable_ 'rating', $opt, $url } if $opt->{s}->vis('rating');
+ td_ class => 'tc_labels', sub { txt_ 'Labels'; sortable_ 'label', $opt, $url } if $opt->{s}->vis('label');
td_ class => 'tc_title', sub { txt_ 'Title'; sortable_ 'title', $opt, $url; debug_ $lst };
- td_ class => 'tc_added', sub { txt_ 'Added'; sortable_ 'added', $opt, $url } if in added => $opt->{c};
- td_ class => 'tc_modified', sub { txt_ 'Modified'; sortable_ 'modified', $opt, $url } if in modified => $opt->{c};
- td_ class => 'tc_started', sub { txt_ 'Start date'; sortable_ 'started', $opt, $url } if in started => $opt->{c};
- td_ class => 'tc_finished', sub { txt_ 'Finish date'; sortable_ 'finished', $opt, $url } if in finished => $opt->{c};
- td_ class => 'tc_rel', sub { txt_ 'Release date';sortable_ 'rel', $opt, $url } if in rel => $opt->{c};
+ td_ class => 'tc_added', sub { txt_ 'Added'; sortable_ 'added', $opt, $url } if $opt->{s}->vis('added');
+ td_ class => 'tc_modified', sub { txt_ 'Modified'; sortable_ 'modified', $opt, $url } if $opt->{s}->vis('modified');
+ td_ class => 'tc_started', sub { txt_ 'Start date'; sortable_ 'started', $opt, $url } if $opt->{s}->vis('started');
+ td_ class => 'tc_finished', sub { txt_ 'Finish date'; sortable_ 'finished', $opt, $url } if $opt->{s}->vis('finished');
+ td_ class => 'tc_rel', sub { txt_ 'Release date';sortable_ 'rel', $opt, $url } if $opt->{s}->vis('rel');
}};
vn_ $uid, $own, $opt, $_, $lst->[$_], $labels for (0..$#$lst);
};
};
- paginate_ $url, $opt->{p}, [ $count, 50 ], 'b';
+ paginate_ $url, $opt->{p}, [$count, $opt->{s}->results], 'b';
}
@@ -320,28 +381,33 @@ TUWF::get qr{/$RE{uid}/ulist}, sub {
} ) : (),
sub {
my $empty = !grep $_->{count}, @$filtlabels;
- div_ class => 'mainbox', sub {
- h1_ $title;
- if($empty) {
- p_ $own
- ? 'Your list is empty! You can add visual novels to your list from the visual novel pages.'
- : user_displayname($u).' does not have any visible visual novels in their list.';
- } else {
- filters_ $own, $filtlabels, $opt, $opt_labels, \&url;
- elm_ 'UList.ManageLabels' if $own;
- elm_ 'UList.SaveDefault', $VNWeb::ULists::Elm::SAVED_OPTS_OUT, { uid => $u->{id}, opts => $opt } if $own;
- div_ class => 'hidden exportlist', sub {
- b_ 'Export your list';
- br_;
- txt_ 'This function will export all visual novels and releases in your list, even those marked as private ';
- txt_ '(there is currently no import function, more export options may be added later).';
- br_;
- br_;
- a_ href => "/$u->{id}/list-export/xml", "Download XML export.";
- } if $own;
- }
- };
- listing_ $u->{id}, $own, $opt, $labels, \&url if !$empty;
+ form_ method => 'get', sub {
+ div_ class => 'mainbox', sub {
+ h1_ $title;
+ if($empty) {
+ p_ $own
+ ? 'Your list is empty! You can add visual novels to your list from the visual novel pages.'
+ : user_displayname($u).' does not have any visible visual novels in their list.';
+ } else {
+ filters_ $own, $filtlabels, $opt, $opt_labels, \&url;
+ elm_ 'UList.ManageLabels' if $own;
+ elm_ 'UList.SaveDefault', $VNWeb::ULists::Elm::SAVED_OPTS_OUT, {
+ uid => $u->{id},
+ opts => { l => $opt->{l}, mul => $opt->{mul}, s => $opt->{s}->query_encode() },
+ } if $own;
+ div_ class => 'hidden exportlist', sub {
+ b_ 'Export your list';
+ br_;
+ txt_ 'This function will export all visual novels and releases in your list, even those marked as private ';
+ txt_ '(there is currently no import function, more export options may be added later).';
+ br_;
+ br_;
+ a_ href => "/$u->{id}/list-export/xml", "Download XML export.";
+ } if $own;
+ }
+ };
+ listing_ $u->{id}, $own, $opt, $labels, \&url if !$empty;
+ }
};
};
diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm
index ad74088f..9b1dfd5a 100644
--- a/lib/VNWeb/User/Edit.pm
+++ b/lib/VNWeb/User/Edit.pm
@@ -7,7 +7,7 @@ use VNDB::Skins;
my $FORM = {
id => { vndbid => 'u' },
title => { _when => 'out' },
- username => { username => 1 }, # Can only be modified with perm_usermod
+ username => { username => 1 }, # Can only be modified by the user itself or a perm_usermod
opts => { _when => 'out', type => 'hash', keys => {
# Supporter options available to this user
@@ -103,7 +103,7 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
my $data = shift;
my $username = tuwf->dbVali('SELECT username FROM users WHERE id =', \$data->{id});
- return tuwf->resNotFound if !$username;
+ return tuwf->resNotFound if !length $username;
return elm_Unauth if !can_edit u => $data;
my $own = $data->{id} eq auth->uid || auth->permUsermod;
@@ -122,7 +122,6 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
}
if(auth->permUsermod) {
- $set{username} = $data->{username};
$set{ign_votes} = $data->{admin}{ign_votes};
$set{email_confirmed} = 1;
tuwf->dbExeci(select => sql_func user_setperm_usermod => \$data->{id}, \auth->uid, sql_fromhex(auth->token), \$data->{admin}{perm_usermod});
@@ -135,6 +134,13 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
$set{perm_lengthvote} = $data->{admin}{perm_lengthvote} if auth->permDbmod;
$set{perm_tag} = $data->{admin}{perm_tag} if auth->permTagmod;
+ if($own && $data->{username} ne $username) {
+ return elm_NameThrottle if tuwf->dbVali('SELECT 1 FROM users_username_hist WHERE id =', \$data->{id}, 'AND date > NOW()-\'1 day\'::interval');
+ return elm_Taken if !is_unique_username $data->{username}, $data->{id};
+ $set{username} = $data->{username};
+ tuwf->dbExeci('INSERT INTO users_username_hist', { id => $data->{id}, old => $username, new => $data->{username} });
+ }
+
if($own && $data->{password}) {
return elm_InsecurePass if is_insecurepass $data->{password}{new};
diff --git a/lib/VNWeb/User/List.pm b/lib/VNWeb/User/List.pm
index 210e6a23..a67d5805 100644
--- a/lib/VNWeb/User/List.pm
+++ b/lib/VNWeb/User/List.pm
@@ -67,7 +67,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
)->data;
my @where = (
- $char eq 'all' ? () : $char eq '0' ? "ascii(username) not between ascii('a') and ascii('z')" : "username like '$char%'",
+ $char eq 'all' ? () : $char eq '0' ? "ascii(lower(username)) not between ascii('a') and ascii('z')" : "lower(username) like '$char%'",
$opt->{q} ? sql_or(
auth->permUsermod && $opt->{q} =~ /@/ ? sql('id IN(SELECT y FROM user_emailtoid(', \$opt->{q}, ') x(y))') : (),
$opt->{q} =~ /^u?([0-9]{1,6})$/ ? sql 'id =', \"u$1" : (),
@@ -80,7 +80,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
FROM users u
WHERE', sql_and(@where),
'ORDER BY', {
- username => 'username',
+ username => 'lower(username)',
registered => 'id',
vns => 'c_vns',
votes => 'c_votes',
diff --git a/lib/VNWeb/User/Login.pm b/lib/VNWeb/User/Login.pm
index fa679325..0aaa1aba 100644
--- a/lib/VNWeb/User/Login.pm
+++ b/lib/VNWeb/User/Login.pm
@@ -34,7 +34,7 @@ elm_api UserLogin => undef, {
}
# Failed login, log and update throttle.
- auth->audit(tuwf->dbVali('SELECT id FROM users WHERE username =', \$data->{username}), 'bad password', 'failed login attempt');
+ auth->audit(tuwf->dbVali('SELECT id FROM users WHERE lower(username) = lower(', \$data->{username}, ')'), 'bad password', 'failed login attempt');
my $upd = {
ip => \$ip,
timeout => sql_fromtime $tm + config->{login_throttle}[0]
@@ -50,7 +50,7 @@ elm_api UserChangePass => undef, {
newpass => { password => 1 },
}, sub {
my $data = shift;
- my $uid = tuwf->dbVali('SELECT id FROM users WHERE username =', \$data->{username});
+ my $uid = tuwf->dbVali('SELECT id FROM users WHERE lower(username) = lower(', \$data->{username}, ')');
die if !$uid;
return elm_InsecurePass if is_insecurepass $data->{newpass};
auth->audit($uid, 'password change', 'after login with an insecure password');
diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm
index ff4361b0..803fff65 100644
--- a/lib/VNWeb/User/Page.pm
+++ b/lib/VNWeb/User/Page.pm
@@ -19,13 +19,19 @@ sub _info_table_ {
};
} if $u->{user_uniname_can} && $u->{user_uniname};
tr_ sub {
+ my $old = tuwf->dbAlli('SELECT date::date, old FROM users_username_hist WHERE id =', \$u->{id},
+ auth->permUsermod ? () : 'AND date > NOW()-\'1 month\'::interval', 'ORDER BY date DESC');
td_ class => 'key', 'Username';
td_ sub {
- txt_ ucfirst $u->{user_name};
+ txt_ $u->{user_name};
txt_ ' ('; a_ href => "/$u->{id}", $u->{id};
txt_ ')';
debug_ $u;
sup if !($u->{user_uniname_can} && $u->{user_uniname});
+ for(@$old) {
+ br_;
+ b_ class => 'grayedout', "Changed from '$_->{old}' on $_->{date}.";
+ }
};
};
tr_ sub {
diff --git a/lib/VNWeb/User/Register.pm b/lib/VNWeb/User/Register.pm
index f8938a8e..cd7d4f8e 100644
--- a/lib/VNWeb/User/Register.pm
+++ b/lib/VNWeb/User/Register.pm
@@ -28,7 +28,7 @@ elm_api UserRegister => undef, {
my $num = tuwf->dbVali("SELECT count FROM stats_cache WHERE section = 'vn'");
return elm_Bot if $data->{vns} < $num*0.995 || $data->{vns} > $num*1.005;
- return elm_Taken if tuwf->dbVali('SELECT 1 FROM users WHERE username =', \$data->{username});
+ return elm_Taken if !is_unique_username $data->{username};
return elm_DoubleEmail if tuwf->dbVali('SELECT 1 FROM user_emailtoid(', \$data->{email}, ') x');
my $ip = tuwf->reqIP;
diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm
index 75c135b8..f1a2635e 100644
--- a/lib/VNWeb/VN/Length.pm
+++ b/lib/VNWeb/VN/Length.pm
@@ -186,7 +186,7 @@ our $LENGTHVOTE = form_compile any => {
vid => { vndbid => 'v' },
vote => { type => 'hash', required => 0, keys => {
rid => { type => 'array', minlength => 1, values => { vndbid => 'r' } },
- length => { uint => 1, range => [1,32767] },
+ length => { uint => 1, range => [1,26159] }, # 435h59m, largest round-ish number where the 'fast' speed adjustment doesn't overflow a smallint
speed => { uint => 1, enum => [0,1,2] },
notes => { required => 0, default => '' },
} },
diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm
index 8bbfe858..4dcb1785 100644
--- a/lib/VNWeb/VN/List.pm
+++ b/lib/VNWeb/VN/List.pm
@@ -32,6 +32,10 @@ sub TABLEOPTS {
sort_id => 2,
sort_sql => 'v.c_released ?o, v.title',
},
+ length => {
+ name => 'Length',
+ vis_id => 4,
+ },
developer => {
name => 'Developer',
vis_id => 2,
@@ -75,6 +79,16 @@ sub listing_ {
paginate_ \&url, $opt->{p}, [$count, $opt->{s}->results], 't', sub { $opt->{s}->elm_ };
+ my sub len_ {
+ my($v) = @_;
+ if ($v->{c_lengthnum}) {
+ vnlength_ $v->{c_length};
+ b_ class => 'grayedout', " ($v->{c_lengthnum})";
+ } elsif($_->{length}) {
+ txt_ $VN_LENGTH{$v->{length}}{txt};
+ }
+ }
+
div_ class => 'mainbox browse vnbrowse', sub {
table_ class => 'stripe', sub {
thead_ sub { tr_ sub {
@@ -85,6 +99,7 @@ sub listing_ {
td_ class => 'tc_plat', '';
td_ class => 'tc_lang', '';
td_ class => 'tc_rel', sub { txt_ 'Released'; sortable_ 'released', $opt, \&url };
+ td_ class => 'tc_length',sub { txt_ 'Length'; } if $opt->{s}->vis('length');
td_ class => 'tc_pop', sub { txt_ 'Popularity'; sortable_ 'popularity', $opt, \&url } if $opt->{s}->vis('popularity');
td_ class => 'tc_rating',sub { txt_ 'Rating'; sortable_ 'rating', $opt, \&url } if $opt->{s}->vis('rating');
td_ class => 'tc_average',sub{ txt_ 'Average'; sortable_ 'average', $opt, \&url } if $opt->{s}->vis('average');
@@ -101,6 +116,7 @@ sub listing_ {
td_ class => 'tc_plat', sub { join_ '', sub { platform_ $_ if $_ ne 'unk' }, sort $_->{platforms}->@* };
td_ class => 'tc_lang', sub { join_ '', sub { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' }, reverse sort $_->{lang}->@* };
td_ class => 'tc_rel', sub { rdate_ $_->{c_released} };
+ td_ class => 'tc_length',sub { len_ $_ } if $opt->{s}->vis('length');
td_ class => 'tc_pop', sprintf '%.2f', ($_->{c_popularity}||0)/100 if $opt->{s}->vis('popularity');
td_ class => 'tc_rating',sub {
txt_ sprintf '%.2f', ($_->{c_rating}||0)/100;
@@ -139,6 +155,10 @@ sub listing_ {
td_ sub { tagscore_ $_->{tagscore} };
} if $tagscore;
tr_ sub {
+ td_ 'Length';
+ td_ sub { len_ $_ };
+ } if $opt->{s}->vis('length');
+ tr_ sub {
td_ 'Popularity:';
td_ sprintf '%.2f', ($_->{c_popularity}||0)/100;
} if $opt->{s}->vis('popularity');
@@ -253,7 +273,8 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
$count = tuwf->dbVali('SELECT count(*) FROM vn v WHERE', $where);
$list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, '
SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
- , v.image, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang
+ , v.image, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang',
+ $opt->{s}->vis('length') ? ', v.length, v.c_length, v.c_lengthnum' : (), '
FROM vn v
WHERE', $where, '
ORDER BY', $opt->{s}->sql_order(),
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 60876e1e..4cf3c659 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -11,7 +11,7 @@ use VNDB::Func 'fmtrating';
# Also used by Chars::VNTab & Reviews::VNTab
sub enrich_vn {
my($v, $revonly) = @_;
- enrich_merge id => 'SELECT id, c_votecount FROM vn WHERE id IN', $v;
+ enrich_merge id => 'SELECT id, c_votecount, c_length, c_lengthnum FROM vn WHERE id IN', $v;
enrich_merge vid => 'SELECT id AS vid, title, original, c_released FROM vn WHERE id IN', $v->{relations};
enrich_merge aid => 'SELECT id AS aid, title_romaji, title_kanji, year, type, ann_id, lastfetch FROM anime WHERE id IN', $v->{anime};
enrich_extlinks v => $v;
@@ -159,13 +159,7 @@ sub infobox_length_ {
my $today = strftime('%Y%m%d', gmtime);
return if !grep $_->{type} ne 'trial' && $_->{released} <= $today, $v->{releases}->@*;
- my $stats = tuwf->dbRowi('
- SELECT count(*) as count
- , percentile_cont(', \0.5, ') WITHIN GROUP (ORDER BY l.length + (l.length/(1+1+1+1) * (l.speed-1))) AS median
- FROM vn_length_votes l
- LEFT JOIN users u ON u.id = l.uid
- WHERE u.perm_lengthvote IS DISTINCT FROM false AND NOT l.ignore AND l.vid =', \$v->{id});
- return if !$v->{length} && !$stats->{count} && !VNWeb::VN::Length::can_vote();
+ return if !$v->{c_length} && !$v->{c_lengthnum} && !VNWeb::VN::Length::can_vote();
my $my = VNWeb::VN::Length::can_vote()
&& tuwf->dbRowi('SELECT rid::text[] AS rid, length, speed, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid);
@@ -173,16 +167,12 @@ sub infobox_length_ {
tr_ sub {
td_ 'Play time';
td_ sub {
- if($stats->{count}) {
- my $m = $stats->{median};
- my $len = $m < 2*60 ? 1
- : $m < 10*60 ? 2
- : $m < 30*60 ? 3
- : $m < 50*60 ? 4 : 5;
- txt_ $VN_LENGTH{$len}{txt}.' (';
+ if($v->{c_lengthnum}) {
+ my $m = $v->{c_length};
+ txt_ +(grep $m >= $_->{low} && $m < $_->{high}, values %VN_LENGTH)[0]{txt}.' (';
vnlength_ $m;
txt_ ' from ';
- a_ href => "/$v->{id}/lengthvotes", sprintf '%d vote%s', $stats->{count}, $stats->{count}==1?'':'s';
+ a_ href => "/$v->{id}/lengthvotes", sprintf '%d vote%s', $v->{c_lengthnum}, $v->{c_length}==1?'':'s';
txt_ ')';
} elsif($v->{length}) {
txt_ "$VN_LENGTH{$v->{length}}{txt} ($VN_LENGTH{$v->{length}}{time})";
diff --git a/lib/VNWeb/Validation.pm b/lib/VNWeb/Validation.pm
index 9796351b..a365c853 100644
--- a/lib/VNWeb/Validation.pm
+++ b/lib/VNWeb/Validation.pm
@@ -15,6 +15,7 @@ use Exporter 'import';
our @EXPORT = qw/
samesite
is_insecurepass
+ is_unique_username
form_compile
form_changed
validate_dbid
@@ -36,7 +37,7 @@ TUWF::set custom_validations => {
editsum => { required => 1, length => [ 2, 5000 ] },
page => { uint => 1, min => 1, max => 1000, required => 0, default => 1, onerror => 1 },
upage => { uint => 1, min => 1, required => 0, default => 1, onerror => 1 }, # pagination without a maximum
- username => { regex => qr/^(?!-*[a-z][0-9]+-*$)[a-z0-9-]*$/, minlength => 2, maxlength => 15 },
+ username => { regex => qr/^(?!-*[a-zA-Z][0-9]+-*$)[a-zA-Z0-9-]*$/, minlength => 2, maxlength => 15 },
password => { length => [ 4, 500 ] },
language => { enum => \%LANGUAGE },
gtin => { required => 0, default => 0, func => sub { $_[0] = 0 if !length $_[0]; $_[0] eq 0 || gtintype($_[0]) } },
@@ -100,6 +101,21 @@ sub is_insecurepass {
config->{password_db} && PWLookup::lookup(config->{password_db}, shift)
}
+# Test uniqueness of a username in the database. Usernames with similar
+# homographs are considered duplicate.
+# (Would be much faster and safer to do this normalization in the DB and put a
+# unique constraint on the normalized name, but we have a bunch of existing
+# username clashes that I can't just change)
+sub is_unique_username {
+ my($name, $excludeid) = @_;
+ my sub norm {
+ # lowercase, normalize 'i1l' and '0o'
+ sql "regexp_replace(regexp_replace(lower(", $_[0], "), '[1l]', 'i', 'g'), '0', 'o', 'g')";
+ };
+ !tuwf->dbVali('SELECT 1 FROM users WHERE', norm('username'), '=', norm(\$name),
+ $excludeid ? ('AND id <>', \$excludeid) : ());
+}
+
# Recursively remove keys from hashes that have a '_when' key that doesn't
# match $when. This is a quick and dirty way to create multiple validation
diff --git a/sql/func.sql b/sql/func.sql
index 88277d97..cb1c6b55 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -138,6 +138,23 @@ $$ LANGUAGE SQL;
+-- Updates vn.c_length and vn.c_lengthnum
+CREATE OR REPLACE FUNCTION update_vn_length_cache(vndbid) RETURNS void AS $$
+ WITH s (vid, cnt, len) AS (
+ SELECT v.id, count(l.vid) FILTER (WHERE u.id IS NOT NULL AND l.vid IS NOT NULL)
+ , percentile_cont(0.5) WITHIN GROUP (ORDER BY l.length + (l.length/4 * (l.speed-1))) FILTER (WHERE u.id IS NOT NULL AND l.vid IS NOT NULL)
+ FROM vn v
+ LEFT JOIN vn_length_votes l ON l.vid = v.id AND NOT l.ignore
+ LEFT JOIN users u ON u.id = l.uid AND u.perm_lengthvote
+ WHERE ($1 IS NULL OR v.id = $1)
+ GROUP BY v.id
+ ) UPDATE vn SET c_lengthnum = cnt, c_length = len
+ FROM s
+ WHERE s.vid = id AND (c_lengthnum, c_length) IS DISTINCT FROM (cnt, len)
+$$ LANGUAGE SQL;
+
+
+
-- c_weight = if not_referenced then 0 else lower(c_votecount) -> higher(c_weight) && higher(*_stddev) -> higher(c_weight)
--
-- Current algorithm:
diff --git a/sql/perms.sql b/sql/perms.sql
index d966d801..de6da264 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -78,6 +78,7 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON users TO vndb_site;
GRANT SELECT (id, perm_usermod), INSERT (id, mail) ON users_shadow TO vndb_site;
+GRANT SELECT, INSERT ON users_username_hist TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON vn TO vndb_site;
GRANT SELECT, INSERT, DELETE ON vn_anime TO vndb_site;
GRANT SELECT, INSERT ON vn_anime_hist TO vndb_site;
@@ -162,6 +163,7 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_multi;
GRANT SELECT, UPDATE, DELETE ON users TO vndb_multi;
GRANT SELECT (id), DELETE ON users_shadow TO vndb_multi;
+GRANT SELECT, DELETE ON users_username_hist TO vndb_multi;
GRANT SELECT, UPDATE ON vn TO vndb_multi;
GRANT SELECT ON vn_anime TO vndb_multi;
GRANT SELECT ON vn_hist TO vndb_multi;
diff --git a/sql/schema.sql b/sql/schema.sql
index e3a957f0..98821a28 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -308,7 +308,7 @@ CREATE TABLE producers ( -- dbentry_type=p
name varchar(200) NOT NULL DEFAULT '', -- [pub]
original varchar(200) NOT NULL DEFAULT '', -- [pub]
alias varchar(500) NOT NULL DEFAULT '', -- [pub]
- website varchar(250) NOT NULL DEFAULT '', -- [pub]
+ website varchar(1024) NOT NULL DEFAULT '', -- [pub]
"desc" text NOT NULL DEFAULT '', -- [pub]
l_wp varchar(150) -- (deprecated)
);
@@ -322,7 +322,7 @@ CREATE TABLE producers_hist (
name varchar(200) NOT NULL DEFAULT '',
original varchar(200) NOT NULL DEFAULT '',
alias varchar(500) NOT NULL DEFAULT '',
- website varchar(250) NOT NULL DEFAULT '',
+ website varchar(1024) NOT NULL DEFAULT '',
"desc" text NOT NULL DEFAULT '',
l_wp varchar(150)
);
@@ -386,12 +386,12 @@ CREATE TABLE releases ( -- dbentry_type=r
hidden boolean NOT NULL DEFAULT FALSE,
title varchar(300) NOT NULL DEFAULT '', -- [pub]
original varchar(250) NOT NULL DEFAULT '', -- [pub]
- website varchar(250) NOT NULL DEFAULT '', -- [pub]
+ website varchar(1024) NOT NULL DEFAULT '', -- [pub]
catalog varchar(50) NOT NULL DEFAULT '', -- [pub]
engine varchar(50) NOT NULL DEFAULT '', -- [pub]
notes text NOT NULL DEFAULT '', -- [pub]
l_dlsite text NOT NULL DEFAULT '', -- [pub]
- l_dlsiteen text NOT NULL DEFAULT '', -- [pub] (deprecated, DLsite doesn't have a separate English shop anymore)
+ l_dlsiteen text NOT NULL DEFAULT '', -- (deprecated, DLsite doesn't have a separate English shop anymore)
l_gog text NOT NULL DEFAULT '', -- [pub]
l_denpa text NOT NULL DEFAULT '', -- [pub]
l_jlist text NOT NULL DEFAULT '', -- [pub]
@@ -401,7 +401,8 @@ CREATE TABLE releases ( -- dbentry_type=r
l_googplay text NOT NULL DEFAULT '', -- [pub]
l_fakku text NOT NULL DEFAULT '', -- [pub]
l_gyutto integer[] NOT NULL DEFAULT '{}', -- [pub]
- l_dmm text[] NOT NULL DEFAULT '{}' -- [pub]
+ l_dmm text[] NOT NULL DEFAULT '{}', -- [pub]
+ l_freegame text NOT NULL DEFAULT '' -- [pub]
);
-- releases_hist
@@ -438,7 +439,7 @@ CREATE TABLE releases_hist (
official boolean NOT NULL DEFAULT TRUE,
title varchar(300) NOT NULL DEFAULT '',
original varchar(250) NOT NULL DEFAULT '',
- website varchar(250) NOT NULL DEFAULT '',
+ website varchar(1024) NOT NULL DEFAULT '',
catalog varchar(50) NOT NULL DEFAULT '',
engine varchar(50) NOT NULL DEFAULT '',
notes text NOT NULL DEFAULT '',
@@ -453,7 +454,8 @@ CREATE TABLE releases_hist (
l_googplay text NOT NULL DEFAULT '',
l_fakku text NOT NULL DEFAULT '',
l_gyutto integer[] NOT NULL DEFAULT '{}',
- l_dmm text[] NOT NULL DEFAULT '{}'
+ l_dmm text[] NOT NULL DEFAULT '{}',
+ l_freegame text NOT NULL DEFAULT ''
);
-- releases_lang
@@ -986,7 +988,7 @@ CREATE TABLE users (
perm_tag boolean NOT NULL DEFAULT true, -- [pub] (public because this is used in calculating VN tag scores)
perm_tagmod boolean NOT NULL DEFAULT false,
perm_review boolean NOT NULL DEFAULT true,
- username varchar(20) NOT NULL UNIQUE, -- [pub]
+ username varchar(20) NOT NULL, -- [pub]
uniname text NOT NULL DEFAULT '',
ip inet NOT NULL DEFAULT '0.0.0.0',
skin text NOT NULL DEFAULT '',
@@ -1018,6 +1020,15 @@ CREATE TABLE users_shadow (
passwd bytea NOT NULL DEFAULT ''
);
+-- users_username_hist
+CREATE TABLE users_username_hist (
+ id vndbid NOT NULL,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ old text NOT NULL,
+ new text NOT NULL,
+ PRIMARY KEY(id, date)
+);
+
-- vn
CREATE TABLE vn ( -- dbentry_type=v
id vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('v', nextval('vn_id_seq')::int) CONSTRAINT vn_id_check CHECK(vndbid_type(id) = 'v'), -- [pub]
@@ -1045,7 +1056,9 @@ CREATE TABLE vn ( -- dbentry_type=v
c_languages language[] NOT NULL DEFAULT '{}',
c_platforms platform[] NOT NULL DEFAULT '{}',
c_developers vndbid[] NOT NULL DEFAULT '{}',
- c_average smallint -- [pub], decimal vote*100, i.e. 100 - 1000
+ c_average smallint, -- [pub], decimal vote*100, i.e. 100 - 1000
+ c_length smallint,
+ c_lengthnum smallint NOT NULL DEFAULT 0
);
-- vn_hist
@@ -1153,7 +1166,7 @@ CREATE TABLE vn_staff_hist (
-- vn_length_votes
CREATE TABLE vn_length_votes (
- id integer PRIMARY KEY,
+ id serial PRIMARY KEY,
vid vndbid NOT NULL, -- [pub]
date timestamptz NOT NULL DEFAULT NOW(), -- [pub]
length smallint NOT NULL, -- [pub] minutes
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index f984a4e4..f79e20cc 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -84,9 +84,10 @@ ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_uid_fkey
ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE;
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE;
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE users_shadow ADD CONSTRAINT users_shadow_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE users_username_hist ADD CONSTRAINT users_username_hist_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE vn ADD CONSTRAINT vn_image_fkey FOREIGN KEY (image) REFERENCES images (id);
ALTER TABLE vn ADD CONSTRAINT vn_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
@@ -160,4 +161,5 @@ CREATE UNIQUE INDEX changes_itemrev ON changes (itemid, rev);
CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 'v1')); -- 'v1' is an invalid release id, but works as a 'no release specified' value in the UNIQUE qualifier.
CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 'v1'));
CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -- For VN recent votes & vote graph. INCLUDE(vote) speeds up vote graph even more
+CREATE UNIQUE INDEX users_username_key ON users (lower(username));
CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes;
diff --git a/sql/triggers.sql b/sql/triggers.sql
index 74a6b413..d5c10678 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -288,6 +288,19 @@ CREATE TRIGGER update_reviews_cache AFTER INSERT OR UPDATE OR DELETE ON reviews_
+-- Call update_vn_length_cache() for every change on vn_length_votes
+
+CREATE OR REPLACE FUNCTION update_vn_length_cache() RETURNS trigger AS $$
+BEGIN
+ PERFORM update_vn_length_cache(id) FROM (SELECT OLD.vid UNION SELECT NEW.vid) AS x(id) WHERE id IS NOT NULL;
+ RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER vn_length_cache AFTER INSERT OR UPDATE OR DELETE ON vn_length_votes FOR EACH ROW EXECUTE PROCEDURE update_vn_length_cache();
+
+
+
-- Call update_images_cache() for every change on image_votes
diff --git a/util/devdump.pl b/util/devdump.pl
index 02688f8d..38ffa16d 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -160,6 +160,7 @@ sub copy_entry {
copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@vids;
# VN-related niceties
+ copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE vid IN($vids)", {uid => 'user'};
copy tags_vn => "SELECT DISTINCT ON (tag,vid,vndbid_num(uid)%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'};
copy quotes => "SELECT * FROM quotes WHERE vid IN($vids)";
my $votes = "SELECT vid, vndbid('u', vndbid_num(uid)%8+2) AS uid, (percentile_cont((vndbid_num(uid)%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(vote_date) AS vote_date"
diff --git a/util/updates/2021-09-02-some-foreign-key-stuff.sql b/util/updates/2021-09-02-some-foreign-key-stuff.sql
new file mode 100644
index 00000000..09abff70
--- /dev/null
+++ b/util/updates/2021-09-02-some-foreign-key-stuff.sql
@@ -0,0 +1,5 @@
+-- Add an ON UPDATE CASCADE clause to these contraints to simplify moving lists across users or VNs.
+ALTER TABLE ulist_vns_labels DROP CONSTRAINT ulist_vns_labels_uid_lbl_fkey;
+ALTER TABLE ulist_vns_labels DROP CONSTRAINT ulist_vns_labels_uid_vid_fkey;
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE ON UPDATE CASCADE;
diff --git a/util/updates/2021-09-26-vn-length-cache.sql b/util/updates/2021-09-26-vn-length-cache.sql
new file mode 100644
index 00000000..40dfa0a0
--- /dev/null
+++ b/util/updates/2021-09-26-vn-length-cache.sql
@@ -0,0 +1,6 @@
+ALTER TABLE vn ADD COLUMN c_length smallint;
+ALTER TABLE vn ADD COLUMN c_lengthnum smallint NOT NULL DEFAULT 0;
+
+\i sql/func.sql
+\i sql/triggers.sql
+select update_vn_length_cache(null);
diff --git a/util/updates/2021-10-27-freegame-mugen.sql b/util/updates/2021-10-27-freegame-mugen.sql
new file mode 100644
index 00000000..cc3f487b
--- /dev/null
+++ b/util/updates/2021-10-27-freegame-mugen.sql
@@ -0,0 +1,3 @@
+ALTER TABLE releases ADD COLUMN l_freegame text NOT NULL DEFAULT '';
+ALTER TABLE releases_hist ADD COLUMN l_freegame text NOT NULL DEFAULT '';
+\i sql/editfunc.sql
diff --git a/util/updates/2021-10-28-username-casefold.sql b/util/updates/2021-10-28-username-casefold.sql
new file mode 100644
index 00000000..88bc1238
--- /dev/null
+++ b/util/updates/2021-10-28-username-casefold.sql
@@ -0,0 +1,2 @@
+ALTER TABLE users DROP CONSTRAINT users_username_key;
+CREATE UNIQUE INDEX users_username_key ON users (lower(username));
diff --git a/util/updates/2021-10-28-username-history.sql b/util/updates/2021-10-28-username-history.sql
new file mode 100644
index 00000000..ac703fc8
--- /dev/null
+++ b/util/updates/2021-10-28-username-history.sql
@@ -0,0 +1,16 @@
+CREATE TABLE users_username_hist (
+ id vndbid NOT NULL,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ old text NOT NULL,
+ new text NOT NULL,
+ PRIMARY KEY(id, date)
+);
+ALTER TABLE users_username_hist ADD CONSTRAINT users_username_hist_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
+\i sql/perms.sql
+
+INSERT INTO users_username_hist (id, date, old, new)
+ SELECT affected_uid, date
+ , regexp_replace(detail, 'username: "([^"]+)" -> "([^"]+)"', '\1', '') AS old
+ , regexp_replace(detail, 'username: "([^"]+)" -> "([^"]+)"', '\2', '') AS new
+ FROM audit_log
+ WHERE detail ~ 'username: "([^"]+)" -> "([^"]+)"' AND EXISTS(SELECT 1 FROM users WHERE id = affected_uid);
diff --git a/util/updates/2021-10-28-website-length.sql b/util/updates/2021-10-28-website-length.sql
new file mode 100644
index 00000000..a666e05f
--- /dev/null
+++ b/util/updates/2021-10-28-website-length.sql
@@ -0,0 +1,4 @@
+ALTER TABLE producers ALTER COLUMN website TYPE varchar(1024);
+ALTER TABLE producers_hist ALTER COLUMN website TYPE varchar(1024);
+ALTER TABLE releases ALTER COLUMN website TYPE varchar(1024);
+ALTER TABLE releases_hist ALTER COLUMN website TYPE varchar(1024);