summaryrefslogtreecommitdiff
path: root/util
AgeCommit message (Collapse)AuthorFilesLines
2009-08-09Converted changes.added to timestamptzYorhel2-3/+9
That was the last one. I hope I haven't forgotten to update anything.
2009-08-08Converted tags.added to timestamptzYorhel2-1/+7
2009-08-08Converted users.registered to timestamptzYorhel2-1/+7
Just a few more to go...
2009-08-08Converted votes.date to timestamptz and use NOW() as defaultYorhel2-1/+7
2009-08-08Converted threads_posts.date and edited to timestamptzYorhel2-2/+13
Everything still seems to be working fine so far.
2009-08-08Converted wlists.added and rlists.added to timestamptzYorhel2-3/+14
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_categoriesYorhel2-11/+52
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 timestampYorhel2-4/+4
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-05Removed last traces of the shared memory processing queueYorhel1-8/+0
Everything has now finally been converted to use the PgSQL LISTEN/NOTIFY feature. This commit removes the last Tie::ShareLite dependency.
2009-08-05Multi::IRC: Reimplemented notifications using PgSQL LISTEN/NOTIFYYorhel2-0/+38
2009-07-31Use bytea data type to store session tokensYorhel2-2/+2
To be consistent with users.passwd - hashes are stored in binary. All conversion from/to hex is done in the DB layer.
2009-07-30Added sessions table to dbgraph.plYorhel1-1/+1
The graph looks even worse now, oh well.
2009-07-30Fixed syntax error in update_2.6.sql and removed leading spaces in dump.sqlYorhel2-4/+4
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 betaYorhel2-1/+24
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 timestamp3dB2-5/+5
-- 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 tweaks3dB2-3/+13
This commit is tested to work.
2009-07-27Applied small adjustments and reorganized DB library code.3dB1-1/+1
-- Removed "ON UPDATE" from sessions.uid column FK -- Fixed dbSessionCheck to work as described -- Merged DB/Sessions.pm into DB/Users.pm
2009-07-24Checkin of auth rewrite progress.3dB1-0/+14
Added a new DB library for handling sessions. New update SQL file for database changes. Added a line to the global config file to set a global salt. It is separate from the cookie_key because it is much more important that it not be changed.
2009-07-23Implemented relgraph notify from PgSQL triggerYorhel2-0/+72
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-20Synchronised dbgraph.pl with the recent DB changesYorhel1-3/+3
Not that the output makes much sense now, but at least it's (mostly) correct.
2009-07-19Removed DEFERRED property on most foreign key constraintsYorhel2-43/+130
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.
2009-07-19Added 1024x600 and 1600x1200 screen resolutionsYorhel1-1/+6
2009-07-19Notify Multi::Image via PostgreSQL for new screenshotsYorhel2-0/+26
This finished the rewrite of Multi::Image and everything surrounding it.
2009-07-19Converted and renamed screenshots.status (int) to processed (bool)Yorhel2-1/+10
There were only two states, processed and unprocessed, so simply using a boolean column with correct naming is more clarifying.
2009-07-18Notify Multi::Image for new cover images via LISTEN/NOTIFYYorhel2-0/+26
Yay! Another weird shared-memory-command optimized away. And the image resizer reacts a lot faster now. Noticably, even.
2009-07-18Use PostgreSQL's LISTEN/NOTIFY to notify Multi::Anime for new animeYorhel2-0/+28
The notify is called from a trigger function, which is called on any UPDATE or INSERT INTO query of which the lastfetch column is NULL. This guarantees that anime info in the DB will always be updated, no matter how its inserted.
2009-07-18Automatically insert rows into anime table for new vn_anime.aid itemsYorhel2-0/+27
This is a lot less error-prone than doing it from Perl. <3 PostgreSQL
2009-07-18Modified definition of the anime tableYorhel2-7/+42
Removed most NOT NULL constraints, and converted lastfetch to a timestamp data type. The site has been updated to handle this, but Multi::Anime won't work.
2009-07-16Made a start on the Multi-rewriteYorhel1-56/+1
Started on multi.pl and Multi::Core, the main differences: - Uses POE::Component::Pg now (get it from http://g.blicky.net/poco-pg.git/) - Doesn't use shared memory anymore - No 'commands' anymore, every session has to handle its own events (communication goes either through POE itself, or the PostgreSQL DB) - No weird Cron stuff anymore All other Multi modules will have to be updated/rewritten to reflect these changes. None of them will work at the moment.
2009-07-14Removed old and unused table/column definitions from dump.sqlYorhel1-11/+0
2009-07-08Keep track of users who requested tagsYorhel2-1/+9
2009-07-04Implemented support for multilingual releasesYorhel2-4/+73
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-06-07Merge branch 'beta'2.4Yorhel2-3/+108
+ Set 2.4 date in ChangeLog
2009-05-31Batch edit releases which are likely to have a doujin or freeware flagYorhel1-0/+53
I really need to write an abstraction layer within PgSQL to make inserting new revisions using plain SQL easier.
2009-05-27Fixed typo on 404 pageYorhel1-1/+1
2009-05-23Animation fields for releasesYorhel2-1/+6
2009-05-23Added freeware and doujin information to releasesYorhel2-1/+6
TODO: - update d3 - filters on /r
2009-05-22Voiced field for releasesYorhel2-3/+5
TODO: filter on /r
2009-05-22Added resolution field to release entriesYorhel2-1/+8
TODO: - Update d3 - Add filter to /r
2009-05-09Don't consider VNs with AVG(vote) < 0 on tag pagesYorhel2-2/+35
The bayesian ranking algorithm isn't exactly meant to be used to differentiate between absolute values, so do a pre-check on AVG(vote) before considering a VN in the rating. I've also played around with using plain old averages as score, but I'd say the ordering is a lot better with the bayesian ranking, the displayed score is just slightly more confusing.
2009-03-30Tiny bugfix in the tag<->vn calculationYorhel1-1/+1
Use a user's highest vote on a child tag in the calculation of the rating of a parent, rather than the average.
2009-03-28Fixed and re-organized tag<->vn calculationYorhel2-68/+64
Fixed major performance bug caused by referencing the wrong table, moved all intermediate views to tag_vn_calc() as temporary views (similar to update_vnpopularity()) and renamed tags_vn_stored to tags_vn_bayesian.
2009-03-22Fixed tiny SQL bugYorhel2-2/+2
2009-03-22Merge branch 'tagging' into betaYorhel3-6/+365
Conflicts: lib/VNDB/DB/Discussions.pm util/updates/update_2.3.sql
2009-03-22Incorporated update_2.3.sql into dump.sql and updated the dbgrapher scriptYorhel2-6/+176
2009-03-21Replacing all occurences of 'discussion tags' with 'discussion boards'Yorhel1-0/+5
Which is a more accurate description, and doesn't confuse with the tagging system. Note than even all internal uses of the word 'tag' have been replaced, as I'm not a huge fan of different terminology in the code and UI. This update might break some things related to the discussion board.
2009-03-14Separated table for tag aliases + proper checking of unique aliases and namesYorhel1-1/+5
2009-03-14Added tag states for pending/deleted/acceptedYorhel1-13/+7
2009-03-08Added caching of tags_vn_bayesian and the VN count for tagsYorhel1-16/+20
Updated hourly by Multi. May want to look for a better way to update this cache, because I'm afraid the current tags_vn_calc() is going to perform very badly on larger databases.
2009-03-08Tag stats on user pages and listYorhel1-0/+32