summaryrefslogtreecommitdiff
path: root/util/sql/all.sql
AgeCommit message (Collapse)AuthorFilesLines
2020-04-04SQL reorg: Move util/sql/ to sql/ as a top-level dirYorhel1-11/+0
Been wanting to do this for a while... I've kept util/sql as a symlink for compatibility with the devdump, old update scripts and other code I may have forgotten. I'll remove it later.
2019-12-30Minor refactor: Move all trigger functions and declarations to separate fileYorhel1-0/+1
This keeps the function body and the trigger declaration closer to each other, so it's easier to tell when the function will be called and which cases it needs to handle. If you ignore errors from existing trigger declarations, the triggers.sql script is idempotent and can be used in from scripts.
2019-08-15Minor util/sql/ reorganization + Make devdump more robustYorhel1-53/+3
I never really liked the hack that devdump.pl had to use to temporarily disable triggers and references. This new importer first imports all schema-related things, then the data, then the functions and table attributes - like an actual database dump. This restructuring should also make it (slightly) easier to import the "near-complete" database dump, but that's still going to involve a fair amount of scripting. This also fixes #22 - the script now asks whether to import a 'dump.sql' if it exists.
2019-06-19Add Greek & Esperanto languagesYorhel1-1/+1
2018-10-06Add 960x640 resolutionYorhel1-1/+1
https://vndb.org/t950.514
2018-10-02DB: Convert resolution into an enumYorhel1-0/+1
Been wanting to do this for a long time - using an integer index into an array that changes once in a while is way too fragile. Doubly so when said indices are also used in filters and URLs that can't be updated every time a new resolution is added.
2018-06-03Add development DB dump, remove old devdb, convert README to markdownYorhel1-16/+1
2018-02-08Store d+ pages in the DB as versioned entries + use markdownYorhel1-2/+2
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
2018-02-04SQL: Load permissions after creating sequencesYorhel1-4/+4
Otherwise the vndb_site/_multi users won't have usage rights on those. Fixes https://vndb.org/t10218.6
2017-11-26Add language: Bulgarian and Platforms: Nintendo Switch & Wii UYorhel1-2/+2
2016-11-27SQL: Use separate role for the website + disallow access to user dataYorhel1-0/+4
Previously the website was connected to the database with a "database owner" user, which has far too many permissions. Now there's a special vndb_site user with only the necessary permissions. The primary reason to do this is to decrease the impact if the site process is compromised. E.g. it's now no longer possible to delete or modify old entry revisions. An attacker can still do a lot of damage, however. Additionally (and this was the main reason to implement this change in the first place), the user sessions, passwords and email data is now not easily accessible anymore. Hopefully, the new user management abstractions will prevent email and password dumps in case of an SQL injection or RCE vulnerability in the site code. Of course, this only works if my implementation is fully correct and there's no privilige escalation vulnerability somewhere. Furthermore, changing your password now invalidates any existing sessions, and the password reset function is disabled for 'usermods' (because usermods can list email addresses from the database, and the password reset function could still allow an attacker to gain access to anyone's account). I also changed the format of the password reset tokens, as they totally don't need to be salted.
2016-08-09Add Croatian languageYorhel1-1/+1
2016-07-31Add Thai languageYorhel1-1/+1
2016-02-15Add Tagalog languageYorhel1-1/+1
2015-10-21SQL: Fix editing + func.sql + triggers.sql + autocreate editing funcsYorhel1-1/+5
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-12SQL: Split constraints/indices/triggers in new file + use idempotent SQLYorhel1-86/+4
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 enumYorhel1-0/+1
Same reasoning as 19ce5fcf536ed478ad34b6b1014bf6f44841d25d
2015-09-07Implement discussion board search functionYorhel1-0/+4
Inspired by wakaranai's implementation at https://github.com/morkt/vndb/commit/b852c87ad145fdaaa09c79b6378dd819b46f7e87 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 ENUMYorhel1-0/+1
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-08-08Hide sexual traits by default + Add profile option to change defaultYorhel1-1/+1
2015-07-21Add profile option for the default spoiler settingYorhel1-1/+1
This fixes the unexpected behaviour that changing the spoiler setting on one page will change it for all pages. All manual spoiler changing options are temporary now.
2015-07-21Add profile option for default visible tag categories on VN pagesYorhel1-1/+1
The name of the profile setting isn't very clear. Not sure what to do with it.
2015-07-21Add profile option to show or summarize tags on VN pages by defaultYorhel1-1/+1
2015-05-13Add staff statistic to main menuYorhel1-0/+3
2015-05-13Change order of staff rolesYorhel1-1/+1
https://vndb.org/t6138.226 - https://vndb.org/t6048.132
2015-05-11New language: Catalan (/Valencian)Yorhel1-1/+1
2015-05-11Three new platforms: FM Towns, PC Engine, X68000Yorhel1-1/+1
2015-01-29staff: Added 'scenario' credit typeYorhel1-1/+1
2015-01-28sql: Merge staff changes into /util/sql/ and rename staff.sql2.24Yorhel1-2/+6
So that the /util/sql/ files are in sync with the actual DB again.
2014-10-13SQL: Use enum to represent platformsYorhel1-0/+1
I believe I didn't do this conversion earlier (back when I converted the language types) because PostgreSQL didn't support dynamically adding new values to an existing enum back then, and modifying an enum was a huge pain. Recent versions do support this, so there's no reason to keep it as a string. ...I just felt like adding some churn to the code base.
2014-08-29Sync language type util/sql/all.sql with recent updateYorhel1-1/+1
2013-09-14Added Arabic and Hebrew languagesYorhel1-1/+1
2013-01-05Don't use Multi for processing screenshotsYorhel1-2/+0
TODO: Get rid of the 'processing' flag and all the async loading of screenshot data in the screenshot uploader.
2013-01-05Don't use Multi for processing character imagesYorhel1-2/+0
2013-01-05Don't use Multi for processing VN cover imagesYorhel1-1/+0
I used to do this to avoid loading Image::Magick in each TUWF process, decreasing memory usage, and lowering the blocking time by avoiding too much processing. Memory isn't much of a problem nowadays, and processing images is fast enough, too, so this complexity isn't necessary anymore. (Character images and screenshots pending)
2012-07-24Add wishlist / VN list status options to VN list browserSpaceRanger1-1/+1
2012-06-04all.sql: Fix creation of the deleted and multi accountsYorhel1-2/+2
2012-03-27Added Indonesian languageYorhel1-1/+1
2012-01-09Added language for UkrainianYorhel1-1/+1
And added an update_2.23.sql file which now also includes the previously added indices. Currently, this update file can be run as often as you want, it doesn't make any noticable changes when you run it on a database that has already been updated. (i.e. I can update the main site without a new release)
2011-12-29Maintain VN search cache for hidden entries as wellYorhel1-2/+1
Required in order to search for hidden entries (obviously :P)
2011-04-08Added char/tag/trait stats to database statistics boxYorhel1-0/+9
2011-04-08Generate dbedit/dbdel notifications on character editsYorhel1-1/+3
2011-03-19chardb: Synchronized DB changes with util/sql/* and dbgraph.plYorhel1-1/+8
2011-02-04Added category field to tags (content/ero/technical)Yorhel1-0/+1
Not very useful at the moment, but will be used to improve several other things.
2011-01-02SQL: Synchronised the language ENUM with the actual DBYorhel1-1/+1
I changed the ENUM in 2.11, and apparently forgot to update the local SQL files... I'm sure there are more such issues. >_>
2011-01-01More infrastructural changes to accomodate for the permanent filtersYorhel1-1/+1
- Added a 'prefs' option to htmlFooter() to add preference data for use by Javascript. - Added an /xml/prefs.xml URL for setting preferences from JS. - Added 'filter_*' keys to the prefs_key ENUM - Load filters by default on VN and Release browser
2010-12-23Converted the notify_announce and notify_dbedit preferencesYorhel1-3/+5
And renamed notify_dbedit to notify_nodbedit, since the default is to provide a notify on a database edit. Also fixed a few bugs along the way.
2010-12-23Added users_prefs table and removed users.(skin|customcss)Yorhel1-0/+1
Will convert the other preferences later.
2010-12-18RFC-01: Implemented (most) of the SQL partYorhel1-0/+3
- Created vnlists table - Converted rlists.vstat into that table - Added triggers to make sure that there is always a corresponding row in vnlists for every row in rlists. - Added a check on vnlists for the 'listdel' notify
2010-11-14SQL: Removed CREATE LANGUAGE plpgsqlYorhel1-3/+0
plpgsql is available by default in PostgreSQL 9.0, so there's no need for this statement anymore.