path: root/lib/VNDB/DB/
AgeCommit message (Collapse)AuthorFilesLines
2018-02-08Store d+ pages in the DB as versioned entries + use markdownYorhel1-1/+3
This touches a bunch of things: - Adds a new first-class database entry type - Removes the d+.+.+ BBCode link syntax, adds a new d+#+ and d+#+.+ link syntax (references have been updated where possible) - Adds a new dependency on Text::MultiMarkdown
2015-10-21Improve SQL in dbRevisionGet()Yorhel1-46/+33
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 editing + func.sql + triggers.sql + autocreate editing funcsYorhel1-7/+6
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 schemaYorhel1-16/+15
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.
2014-12-22Initial implementation of a staff/seiyuu databasemorkt1-5/+8
2011-04-30Added "All except characters" filter to history browserYorhel1-1/+2
2011-03-30Fixed table name clash SQL error on "hide deleted items" history browserYorhel1-12/+12
Renamed 'changes' join to 'h' instead of 'c'
2011-02-16chardb: Added character add/edit form + misc. fixesYorhel1-0/+1
2011-02-15chardb: Added char entry tables and updated the basic revision frameworkYorhel1-4/+4
The Perl code and SQL-revisioning code only handles the name, original, alias and desc fields at the moment. There is a basic /i+ and /i+.+ page for testing, which should have all the functionality required for the revisioning framework.
2010-11-07DB::Misc: Optimized dbRevisionGet()Yorhel1-17/+19
The code is a bit more complicated now, and it's not a lot faster, but at least this helps a bit.
2010-01-24Versioned the deleting and locking of database entriesYorhel1-15/+9
This is implemented by adding ihid (item hidden) and ilock (item locked) columns to the changes table, The (vn|release|producer).(hidden|locked) columns now work as a cache, refering to the changes.(ihid|ilock) columns with = (vn|release|producer).latest. The cached columns are updated automatically each time a new revision is inserted. This is a pretty large change, bugs are quite likely.
2010-01-01SQL: Revision insertion abstraction for producer entriesYorhel1-1/+1
2010-01-01SQL: Revision insertion abstraction for release entriesYorhel1-5/+4
Also added a little sanity checking on the edit_(vn|release) table, and added a default value for releases_rev.released.
2010-01-01SQL: Made a start on revision insertion abstractionYorhel1-58/+15
This will make it easier to do automated edits, either from cron jobs, Multi, update scripts, or from within SQL triggers. So far only the VN related functions have been defined/updated, trying to edit/add releases or producers will not work at the moment. The functions for editing or adding a new database entry have been merged, as the procedure is rather similar. util/dump.sql will be updated later on.
2009-12-05SQL: Removed changes.causedbyYorhel1-1/+1
This column was used to differentiate between automated edits and user edits, but that later changed to checking for changes.requester = 1. The column has since never really been used, and due to a bug introduced in VNDB 2.0, it has never been updated, either. Meaning it's not even accurate for any database changes made after december 2008...
2009-12-05Merged db[VN|Producer|Release][Edit|Add] into dbItemEdit and dbItemAddYorhel1-17/+24
And also changed the way the item_table.latest column was updated: it is now only updated after the revision insert has completed, making it easier to write trigger functions in SQL.
2009-10-25dbRevisionGet: Fixed 500 when using the 'Include edits of releases' optionYorhel1-1/+1
2009-10-12SQL: Converted changes.type to an ENUMYorhel1-12/+12
This is a very important column in a very important table, I hope I didn't forget to update a piece of code somewhere...
2009-10-10List all known languages on /r and /v/all instead of only those in useYorhel1-15/+1
Performance improvement of ~15ms for all release and VN browse pages. There are in total 20 known languages in the DB, and 12 of them are actually used (i.e. a release in that language exists). Which means 8 of the listed language filters won't produce any results (yet), but I'd say that's an accaptable trade-off.
2009-08-09Converted changes.added to timestamptzYorhel1-1/+2
That was the last one. I hope I haven't forgotten to update anything.
2009-07-04Implemented support for multilingual releasesYorhel1-3/+3
The 'language' column in releases_rev has been replaced with a releases_lang table. As this is quite a big change, there may still be bugs floating around somewhere.
2009-01-25Random quote featureYorhel1-1/+11
2008-12-13Include edits of releases filter to /v+/histYorhel1-7/+13
No idea why, but it looks like a mess :-(
2008-12-09Code cleanup: removed leading spaces and combined ↵Yorhel1-2/+2
DB/{Votes,VNList,WishList}.pm into
2008-12-01Interface for the advanced VN search featureYorhel1-1/+16
We're getting there...
2008-11-29Caching the global database statisticsYorhel1-9/+4
Because I can't say no to a performance increase of 4 to 7ms for -every- pageview! Makes use of postgresql triggers and stored procedures.
2008-11-26All db*(Edit|add) functions accept an uid option, reverse relation updates ↵Yorhel1-5/+5
go to Multi again
2008-11-22Wrote a generic function to handle all /[vrp]+/(lock|hide) URIsYorhel1-1/+13
They pretty much all work the same anyway
2008-11-15Added edits/newly created pages filterYorhel1-1/+4
History browser is now pretty much done
2008-11-15Another history browser filter: item typeYorhel1-1/+1
2008-11-15Hidden items filter for history browserYorhel1-3/+14
The query is starting to get pretty heavy, might want to cache a few columns to get rid of all those table joins
2008-11-15Automated edits filterYorhel1-1/+4
Currently relies on the fact that Multi is the only one making the automated edits
2008-11-14Added basic history browserYorhel1-1/+47
Doesn't work for r+ and v+ yet. Filter options pending...
2008-11-14Adding new producer works + abstracted adding of new items and revisionsYorhel1-1/+59
...and those URL regexes are getting more and more complex >.>
2008-10-31Added some authentication and user functionsYorhel1-0/+1
2008-10-27Database statistics are now fetched from the DBYorhel1-0/+27
...and I created yet another *::Misc module...