summaryrefslogtreecommitdiff
path: root/util/dump.sql
AgeCommit message (Collapse)AuthorFilesLines
2010-01-09SQL: Split dump.sql into several files and merged update_2.10.sqlYorhel1-789/+0
The functions can now be edited without having to repeat them in the update scripts. Just importing the func.sql file with \i will do the trick.
2010-01-01SQL: Revision insertion abstraction for release entriesYorhel1-1/+1
Also added a little sanity checking on the edit_(vn|release) table, and added a default value for releases_rev.released.
2009-12-28SQL: Fixed bug in producer_relgraph_notify()Yorhel1-5/+2
2009-12-05SQL: Removed changes.causedbyYorhel1-3/+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-05SQL: Call update_vncache() in a triggerYorhel1-1/+16
This removes the need for the dbVNCache() function in perl.
2009-12-05SQL: Don't allow argument of update_vncache() to be 0Yorhel1-18/+11
To batch update, simply do a SELECT update_vncache(id) FROM vn; The function is now more readable as well.
2009-12-05SQL: Removed the use of CONSTRAINT TRIGGERsYorhel1-30/+34
This makes use of the change that the [vn|producers].latest columns are now only updated after the entire revision has been inserted.
2009-11-29SQL: Replace index on tags_vn_inherit (tag) with an index on (tag,vid)Yorhel1-2/+2
This drastically improves the performance of the search-VN-tag-filter feature, and it seems PostgreSQL can use the index even when only filtering results by the tag column.
2009-11-29Tags: Replaced tag_tree() with WITH .. SELECT queries, and removed /g/debugYorhel1-60/+0
The return value of dbTagTree() is also somewhat easier to work with.
2009-11-29SQL: Rewrote tag_vn_calc() to use a WITH RECURSIVE .. SELECT queryYorhel1-32/+27
This is more efficient, and doesn't require the tag_tree() or tag_vn_childs() stored procedures. Does require PostgreSQL 8.4+
2009-11-27Changed VN score on tags pages to display plain averagesYorhel1-12/+8
For three reasons: - Speed tag_vn_calc() is now more than 10 times faster (granted, it could have been a lot faster even with the bayesian rating, but whatever) - Consistency with the tag scores displayed on the VN pages (which are raw averages as well) - It didn't always make sense
2009-11-15SQL: Improved performance of update_vnpopularity()Yorhel1-1/+1
The previous statement was optimized for PostgreSQL 8.3 and took only about a second, but after the update to 8.4 it took about 10 times longer due to a different execution plan being generated. This slightly reworded statement generates a more efficient plan on 8.4.
2009-11-14SQL: Allow NULL for vn.c_popularityYorhel1-2/+2
Sorting from least to most popular VN make sense now, you won't have to wade through those entries without any vote at all.
2009-11-14SQL: Cached bayesian VN rating and vote countsYorhel1-11/+3
Was a good idea after all...
2009-11-14Added bayesian rating and vote count to the VN listYorhel1-0/+10
It's even realtime! To my surprise this calculation isn't very heavy, or PostgreSQL is just extremely fast. The GetVN query on /v/all takes 100ms in the worst case (instead of the usual 30-60ms). Can always cache this later on.
2009-11-14Added wikipedia links for producersYorhel1-1/+2
2009-11-14SQL/L10N: Allow NULL for releases_rev.minage and make the values translatableYorhel1-1/+1
2009-11-02SQL: Fixed another bug with the tags_vn_bayesian.spoiler calculationYorhel1-1/+1
2009-10-24Removed update_rev() function and monthly revcache cronYorhel1-25/+0
The changes.rev column should be correct in the first place, and in the (most likely) impossible condition that it isn't, the update_rev() function is more likely to make things worse.
2009-10-24Added Spawned <> Originated from producer relationYorhel1-1/+1
2009-10-21SQL: Send relgraph notify when needed for producer entriesYorhel1-0/+30
2009-10-21Added relation graphs for producersYorhel1-1/+3
TODO: - document the relations - emit a relgraph notify when needed
2009-10-21SQL: Renamed vn_graphs to relgraphs and synced dbgraph.plYorhel1-11/+7
As the same table can easily be used to store producer relation graphs as well.
2009-10-21SQL: Synced update_2.8.sql into dump.sqlYorhel1-5/+18
This doesn't mean the relations are final, though...
2009-10-18SQL: Fixed calculation of tags_vn_bayesian.spoilerYorhel1-1/+1
2009-10-17SQL: Converted releases_rev.type to an ENUM data typeYorhel1-4/+5
2009-10-12SQL: Converted changes.type to an ENUMYorhel1-4/+5
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-10Display number of unread posts in user menuYorhel1-0/+1
Instead of displaying the total number of threads. Posts are marked as read when the thread is opened.
2009-10-09Added producer role (developer/publisher) to DB and release editorYorhel1-0/+3
They aren't displayed on the site yet, though.
2009-10-09SQL: Converted releases_media.medium to an ENUM typeYorhel1-2/+3
2009-10-08Synchronised and updated dump.sql and dbgraph.plYorhel1-17/+14
Removed the 'SET default_with_oids', as the minimum required PostgreSQL version (8.3) has OIDS disables by default already. Also uncommented the CREATE LANGUAGE, plpgsql is a "trusted language" and as such doesn't need superuser priveleges to create anymore.
2009-09-18Fixed bug with excluding AVG(vote) < 0 VNs from tag pagesYorhel1-2/+3
This was *supposed* to be done in ce6d43aed955effe136f1ce3682828d2d43109cb
2009-08-12Added global user votes ignore listYorhel1-2/+7
2009-08-12Synchronised a NOT NULL constraint in dump.sql with the actual DBYorhel1-1/+1
threads_posts.edited allows NULL values.
2009-08-09Converted changes.added to timestamptzYorhel1-3/+3
That was the last one. I hope I haven't forgotten to update anything.
2009-08-08Converted tags.added to timestamptzYorhel1-1/+1
2009-08-08Converted users.registered to timestamptzYorhel1-1/+1
Just a few more to go...
2009-08-08Converted votes.date to timestamptz and use NOW() as defaultYorhel1-1/+1
2009-08-08Converted threads_posts.date and edited to timestamptzYorhel1-2/+2
Everything still seems to be working fine so far.
2009-08-08Converted wlists.added and rlists.added to timestamptzYorhel1-2/+2
I like how the rlists.added column isn't used anywhere in the code, makes the conversion a lot easier. :-)
2009-08-08Convert old category relations to tags and remove vn_categoriesYorhel1-9/+0
This will currently generate a 500 error for VN pages, because they still try to look something up in the vn_categories table. Will fix that soon. Obviously, this conversion script only works on the actual VNDB database, if you run your own VNDB you can safely ignore the INSERT error when executing update_2.6.sql.
2009-08-06Use timestamptz data type instead of timestampYorhel1-2/+2
It took me a while to figure out that the "timestamp with timezone" PostgreSQL type doesn't actually store the time zone, it simply stores and calculates on UTC, and only converts it to the connection time zone in the formatting stage before sending it to the SQL client. When working with date/times in PgSQL, the NOW() (and related) functions return a timestamp with time zone. Upon converting that to a timestamp without time zone type, the current timezone setting is used. Meaning that comparing a column of type timestamp without timezone to NOW() will only work if all data in the columns is stored in the same timezone as that of the current connection, which in turn means that changing this setting at any point will cause all data in timestamp without timezone columns to be incorrectly interpreted. I often praise PostgreSQL for their comprehensive documentation, but they could've pointed this out more clearly...
2009-08-05Multi::IRC: Reimplemented notifications using PgSQL LISTEN/NOTIFYYorhel1-0/+19
2009-07-31Use bytea data type to store session tokensYorhel1-1/+1
To be consistent with users.passwd - hashes are stored in binary. All conversion from/to hex is done in the DB layer.
2009-07-30Fixed syntax error in update_2.6.sql and removed leading spaces in dump.sqlYorhel1-2/+2
That syntax error was partly 3dB's fault, and partly my fault for changing too much in that merge, heh.
2009-07-30Merge branch 'auth' of git://3decibels.net/vndb into betaYorhel1-1/+11
Conflicts: util/dump.sql util/updates/update_2.6.sql Also updated ChangeLog and made some tiny style changes.
2009-07-29Changed sessions.expiration column to timestamp3dB1-2/+2
-- Updated SQL files to reflect column type change. -- Subroutine dbSessionAdd rewritten to accept an expiration timestap as an optional third argument.
2009-07-28Multiple bugfixes to authorization system & sql tweaks3dB1-1/+11
This commit is tested to work.
2009-07-23Implemented relgraph notify from PgSQL triggerYorhel1-0/+36
This finishes the new relation graph generator, as it'll now regenerate graphs as soon as is needed. This obsletes the VNDB::Util::Misc::vnCacheUpdate() function, this functionality is provided by triggers within PostgreSQL. The update_vncache(0) procedure is now significantly slower due to the trigger on the vn table. It'd be a good idea to rewrite this procedure by using triggers and conditional updates, to drastically lower the number of rows that need to be updated.
2009-07-19Removed DEFERRED property on most foreign key constraintsYorhel1-43/+41
Only allowing the deferred state on a select few foreign keys would detect problems in an earlier stage (rather than waiting for a commit to happen), and guarantees that some things are inserted before others, which in turn eases the writing of trigger functions.