AgeCommit message (Collapse)AuthorFilesLines
2015-10-25DB::Discussions: Minor improvement to post <-> board filter query2.25Yorhel1-1/+1
I don't know why I didn't apply this one before, I did make this change when benchmarking the fulltext search queries and with the introduction of the bb_tsvector() function this change pretty much always improves performance.
2015-10-25Staff: Add error msg when removing used alias + fix bug in alias editingYorhel4-5/+23
The new database schema doesn't allow an alias to be removed when it is still linked to a VN.
2015-10-24DB::Staff: Fix 'aid' filter, used when editing VN staff infoYorhel1-1/+1
This broken filter would cause all staff info to be deleted from a VN upon edit. Not so nice.
2015-10-24SQL: Throwing around some indicesYorhel2-4/+10
These indices provide a significant speed-up of /v+ and /u+ pages, and improve some other stuff as well.
2015-10-24Improve several discussion board SQL queriesYorhel4-10/+27
An index on was necessary to speed up some very common "recent posts" queries on both the homepage and the thread index. Postgres thought that the same index could be used to speed up the full-text search (because it's ordered by date, after all), but that completely killed performance. That was solved with a bb_tsvector() wrapper to tell the query planner that not using the full-text index is incredibly show, which in turn improved the search performance beyond what it was. Many thread-related queries are still somewhat slow, but that seems to be a limitation in the schema. I'll just keep monitoring to see if that's worth fixing in the future. Interestingly, dbThreadCount() needs to use a sequential scan, but it's still remarkably fast.
2015-10-21Improve SQL in dbRevisionGet()Yorhel3-49/+35
Two main improvements: - Filtering on (non)hidden items now doesn't join any of the item tables, instead it looks up the latest revision from the changes table itself, using the index on (type,itemid,rev). It's still not super fast, but a pretty large improvement nonetheless. - The item titles/names are obtained in a separate query. I tried to modify the main query in various ways, but couldn't make it as fast as I'd have liked. I also removed the 'what' flag while I was at it, all uses of the method request all information anyway.
2015-10-21SQL: Fix Multi to use the new DB schemaYorhel5-163/+145
That should be the last thing to convert to the new schema.
2015-10-21SQL: Fix editing + func.sql + triggers.sql + autocreate editing funcsYorhel19-639/+463
This changes quite a bit to the way the editing functions work. Because these functions are very repetitive and it's easy to keep things out of sync, I created a script to generate them automatically. I had to rename a few function and table names for consistency to make this work. Since database entries don't have a 'latest' column anymore, and since the order in which tables are updated doesn't have to be fixed, I dropped many of the SQL triggers and replaced them with a edit_committed() function which is called from edit_*_commit() and checks for stuff to be done. Don't forget to run 'make' before importing the update script.
2015-10-17SQL: Fix all browsing queries to use the new schemaYorhel20-436/+541
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.
2015-10-17SQL: Convert all item-tables to a different schemaYorhel3-235/+669
This commit breaks pretty much everything. Lots of code will have to be fixed to work with this new schema. The basic idea is to separate live data from archived data, which allows for smaller and more effective indices on the live data, and the archived data doesn't need such indices and have to be accessed at all for most operations. Another goal is to eliminate table joins to fetch some necessary information, e.g. it's not necessary anymore to join the main item tables in order to fetch only the latest revision of some item data. This is very much work in progress. I might stumble upon some weird issue while fixing the code, and might have to redesign everything from scratch again. Let's just see how things go.
2015-10-12Notifications: Allow max 500 notifies per user + add SQL index on uidYorhel3-0/+7
Turns out that fetching whether or not you have unread notifications (done on every pageview if you're logged in) was pretty slow. The index speeds up both that query and the "my notifications" view. The extra purge for old notifications for users with more than 500 notifications ensures that the index stays effective for the unread notifications count. Otherwise it'll have to read half of the notifications table anyway to check the 'unread' filter.
2015-10-12SQL: Split constraints/indices/triggers in new file + use idempotent SQLYorhel5-179/+172
Turning the foreign key references into idempotent statements required adding the name for each reference in the query. I used the names of the production database, but since the names are autogenerated at creation time, it's possible that they have other names if the database has been created slightly differently. Using explicit names for everything and having idempotent SQL statements is rather useful when making nontrivial modifications to the database schema. Which is something I consider doing.
2015-10-11SQL: Convert producers_rev.type into enumYorhel3-1/+9
Same reasoning as 19ce5fcf536ed478ad34b6b1014bf6f44841d25d
2015-10-11VNDBUtil: Add some tildes to VN search normalizationYorhel1-1/+1
2015-10-03formValidate: Add json_(maxitems|unique|sort) options to json templateYorhel3-40/+59
Adds slightly more strict validation and simplifies further processing.
2015-10-03js: Minor fixesYorhel3-3/+3
2015-10-03Handle JSON data natively when processing form dataYorhel6-40/+47
No more need for extra json_encode/json_decode calls, and the form_compare() function is more lenient w.r.t. integer/string comparison. This is the improvement I described in commit ed86cfd12b0bed7352e2be525b8e63cb4d6d5448
2015-10-03Rewrote screenshot uploader to support multiple files + use jsonYorhel6-189/+169
This might have broken the screenshot uploader on some crappy browsers, but it's much cleaner than the old iframe hack. The ability to upload multiple files in one go is also very convenient.
2015-10-01VNEdit: Give error on duplicate alias + improved msg on id/page errorYorhel3-9/+22
2015-10-01VN search: Add some more quote characters + & to normalizationYorhel2-1/+6
As suggested by
2015-09-20formValidate(): Let's just allow a '0' id - fix more errorsYorhel3-6/+6
Looks like 0 is actually used often to indicate some special value. Affects basically all 'check all' boxes (had to modify some of those boxes because some used -1, but that wasn't a problem).
2015-09-20Handler::Staff: Fix validation error on primary alias IDYorhel1-1/+1
It can be 0 when creating a new alias as primary name.
2015-09-20Fix handling of empty seiyuu/credits fieldsYorhel2-3/+3
2015-09-20formValidate: Add json template and remove json_validate() functionYorhel5-54/+49
This is less convenient than I had expected, because all the form handling code is designed to work with plain strings rather than any scalar. This means the json data has to be encoded again to get into $frm (not doing this means that, if the form didn't validate, the field won't be filled out correctly). And then decoded for validation, and then encoded again for comparison. I suspect the better solution is to fix the form handling code to handle arbitrary data structures: comparison can be done by deep comparison rather than a simple string compare, and the form generator can auto-encode-to-json if it sees a complex object. Another advantage of this solution is that the comparison function can be less strict with respect to number formatting. In the current scheme you have to be very careful that numbers are not automatically coerced into string format, otherwise the comparison will fail. Either way, that's an idea for the future...
2015-09-20formValidate: Created templates for gtin and editsum fieldsYorhel8-26/+40
2015-09-20Use plural version of reqPostsYorhel1-1/+1
Surprisingly, this was the only change I needed to make to comply with the TUWF reqGet-style function split.
2015-09-20Use the new reqQuery() + update usage of reqPath() for TUWF 1.0Yorhel2-3/+2
2015-09-20Update usage kv_validate() to upcoming TUWF 1.0Yorhel15-101/+90
And added new 'page' and 'id' templates for more strict validation.
2015-09-17Handler::Discussions: Add search box to non-item thread listsYorhel1-22/+31
As requested at
2015-09-17Handler::VNEdit: Use json_validate() for the VN staff/cast dataYorhel1-36/+38
This (only very slightly) simplified further processing of the data. It does add more validation than was present: Previously it was possible to send invalid roles (would give a 500) or invalid numeric IDs (would throw some perl warnings). These issues are now handled earlier on. This change also puts a maximum length on the notes field, but nobody has passed the 300 characters so far.
2015-09-17Rename jsonEncode/Decode to C-style namingYorhel3-13/+13
CamelCase is used for methods, C-style names for functions. I'm just nitpicking now.
2015-09-17Created json_validate() for JSON form data, used for Staff alias editorYorhel3-25/+44
The intention is to move more JS editing forms to use JSON, but manually verifying JSON objects is both painful and likely to introduce errors or vulnerabilities. json_validate() is a bit of a hack, but has the advantage that its validation syntax is the same as for normal forms, and it automatically strips whitespace. I intent to give kv_validate() an upgrade to be more flexible/modular so it can do more custom normalization. But that's for later. I've been meaning to rewrite the JS forms anyway together with the large JS rewrite, but I'm rather lazy. This is one small step in the right direction anyway. Note that json_validate() assumes that the JS code will provide user-friendly messages on bad input, but the staff alias editor doesn't quite do this yet.
2015-09-08Multi::API: Set keepalive on sockets to detect dead TCP connectionsYorhel1-0/+1
Hopefully prevents issues like
2015-09-08L10N: Fix typo in board searchYorhel1-1/+1
2015-09-07Handler::Discussions: Fix two bugs introduced in last commitYorhel2-2/+2
2015-09-07Handler::Discussions: Use ts_headline() to format search resultsYorhel4-7/+47
And also fix strip_bb_tags() to be case-insensitive and fix a bug in converting the query into a tsquery.
2015-09-07Handler::Discussions: Remove formcode from search formYorhel2-4/+7
It's not verified and only uglifies the URLs.
2015-09-07Implement discussion board search functionYorhel8-14/+265
Inspired by wakaranai's implementation at This version is different in a number of aspects: - Separate search functions for title search and fulltext post search. Perhaps not the most convenient option, but the downside of a combined search is that if the query matches the threads' title, then all of the posts in that thread will show up in the results. This didn't seem very useful. - Sorting is based purely on post date. Rank-based sort is slow without a separate caching column, and in my opinion not all that useful. Implementation differences: - Integrated in the existing DB::Discussions functions, so less code to maintain and more code reuse. - No separate caching column for the tsvector, a functional index is used instead. This is a bit slower (index results need to be re-checked against the actual messages, hence the slowdown), but has the advantage of smaller database dumps and less complexity in updating the cache. Things to fix or look at: - Highlighting of the search query in message contents. - Allow or-style query matching
2015-09-06SQL: Convert threads_board.type to ENUMYorhel4-3/+9
The char(2) solution is both inefficient and ugly. Also needed to be careful with handling the extra space that Postgres would automatically add to single-character types.
2015-09-03CSS: Add overflow:hidden to screenshots on homepageYorhel1-1/+1
As suggested at
2015-09-03JS: Fix argument replacement regexp in mt()Yorhel1-1/+1
This caused every [_#] to be replaced multiple times.
2015-08-31Handler::VNPage: Increase limit on number of visible releasesYorhel1-2/+2
From 50 to 200.
2015-08-18vnstaff.js: Export vnsStaffData for use by vncast.jsYorhel1-4/+5
To fix the cast editor. Add pngcrush/slow options + force png32 + atomic replaceYorhel3-6/+24
A recent version of imagemagick creates 16 bit depth PNG images by default for some reason. This results in an unnecessarily large file size increase and pngcrush doesn't do much to counter it (and its -bit_depth option has been deprecated, too). The atomic replace is quite handy to avoid people seeing any wierd intermediate images while the slow+pngcrush options are being used.
2015-08-17Merge branch 'jscleanup'Yorhel35-3464/+3544
2015-08-17jsgen: Support external command for JS compression (like uglifyjs)Yorhel5-17/+46
Tends to compress a bit better than JavaScript::Minifier::JS. But is also a lot slower, so not really useful when devving. Stats for en.js: raw gzip uglifyjs 68199 19446 JS::Minifier::XS 79862 21624 Uncompressed 107662 28663 On an unrelated note, I like how jQuery boasts about being "Only 32kB minified and gzipped.". That's quite a bit more than all of VNDB's Javascript combined. For a damn library.
2015-08-17js: Clean up misc.jsYorhel4-134/+153
- Individual blocks don't leak variables into the parent scope anymore. Previously some blocks would re-use variables from other blocks, creating (almost invisible) dependencies between te blocks. - More consistent code for ulist-change-dropdowns, and all of them have a ref= argument now. - Use 'hidden' class instead of style.display wherever that makes sense. - Remove dead 'advselect' code. Hasn't been used since the addition of search filters. - lang_select doesn't rely on the position of the language class in className anymore (seriously that stuff is fragile...)
2015-08-17js: Add L10N strings to all relevant varsYorhel5-66/+33
This simplifies the JS code in some places and removes a whole number of L10N strings from the "l10n_str" var, thus shrinking the JS size a bit (uncompressed about 1500 bytes, in fact. 500 bytes after gzip).
2015-08-16js: Clean up charops.js & tagops.jsYorhel2-111/+123
charops.js was already recently (re)written and followed much of the new conventions, so that was easy. tagops.js has been simplified somewhat, and does not rely on the position of the "tagspl#" class anymore.
2015-08-15js: Let preprocess L10N strings + add L10N strings to some varsYorhel7-71/+77
This simplifies the JS version of mt() a bit and makes the whole internationalization framework a bit more robust. I also changed the VARS.{rlist_status,age_ratings,languages,platforms,char_roles} arrays to include the L10N string. This simplifies the JS code and reduces the JS size. There's a few more of such lists that can be transformed in the same way, I'll get to that later.