summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Discussions.pm
AgeCommit message (Collapse)AuthorFilesLines
2020-09-12v2rw: Add new filter querying framework + rewrite homepageYorhel1-176/+0
The new filter framework hasn't been extensively tested yet, but seems to mostly work. No real changes to the homepage, but I did manage to make a few queries a little bit faster. The most significant source of slowness is the releases listing, but I can't optimize that without adding indices.
2020-08-07Discussions: Fix handling of user deletionYorhel1-3/+3
Which I broke by removing the DEFAULT clause on threads_posts.uid. That column still used the old uid=0 for deleted users rather than the uid=NULL that I was planning to migrate the entire DB to. So while I'm fixing up the threads schema, may as well update this too. There's still a bunch of columns relying on uid=0, but I can fix that later.
2020-08-06Discussions: Allow discontinuous post numbers + ignore hidden posts in ↵Yorhel1-2/+2
counts/lastpost This solves a few problems: - 'hidden' posts will no longer cause the thread to be bumped to the front page. - Deleting posts will no longer cause other posts to be renumbered (and hence will not break existing links to posts) - Numbers of deleted posts will no longer be re-used (except when they were the last post in the thread - fixing this would require an additional column in 'threads', but it didn't seem worth the trouble)
2019-12-12v2rw: Discussion board editing & thread creationYorhel1-160/+1
Now with BBCode preview, interactive board search, client-side error reporting and lots of new bugs. This took me far too long, turns out it wasn't such a trivial rewrite.
2019-11-15v2rw: Convert discussion board searchYorhel1-19/+0
With minor styling improvements and full pagination for thread title search. I did combine a few queries which I initially separated for performance reasons. A few quick benchmarks show that it's fast enough, but I'll have to see how it performs in production.
2019-10-10rewards: Apply supporters badge and unicode name (almost) everywhereYorhel1-5/+5
Only place where this isn't applied (yet?): Sorting user lists still goes by the old username and board names don't use the new unicode names. I have to say, I quite like the sql_user() and user_() pattern. It's not without problems when applied to everything, but it's good enough for several use cases.
2019-10-04VNDB::DB::Discussions: Don't count private threads in the discussions tab numberYorhel1-1/+1
2019-06-27Add support for "private" threadsYorhel1-11/+14
Currently a mod-only feature. Each thread now has a 'private' flag which, when set, will make the thread visible only to users mentioned in the boards.
2015-11-11Misc poll improvementsYorhel1-12/+72
- Merged polls table into threads table. Not much of a storage/performance difference, and it's a bit simpler this way. - Merged DB::Polls into DB::Discussions. Mainly because of the above change in DB structure. - Add option to remove an existing poll. - Allow preview and recast to be changed without deleting the votes - Set preview option by default. Because personal preferences. :) - Minor form validation differences
2015-11-10Merge branch 'master' into pollmorkt1-12/+16
2015-10-25DB::Discussions: Fix bug in 'notusers' queryYorhel1-1/+1
'<> ANY' doesn't work that way. NOT EXISTS() is also pretty fast and does what we want.
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-24Improve several discussion board SQL queriesYorhel1-8/+14
An index on threads_posts.date 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-18discussion board polls.morkt1-0/+2
2015-10-17SQL: Fix all browsing queries to use the new schemaYorhel1-3/+1
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-09-07Handler::Discussions: Fix two bugs introduced in last commitYorhel1-1/+1
2015-09-07Handler::Discussions: Use ts_headline() to format search resultsYorhel1-2/+20
And also fix strip_bb_tags() to be case-insensitive and fix a bug in converting the query into a tsquery.
2015-09-07Implement discussion board search functionYorhel1-5/+16
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-2/+2
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.
2010-01-26Made a start on the notification systemYorhel1-11/+1
The current setup should be able to handle all kinds of notifications, though only PMs are implemented at this point. More to come.
2010-01-11Fixed sorting bug on /u+/postsYorhel1-3/+8
Damn, so many bugs... This one was introduced in 9613533da2c58af3c64e3bd4ed7b92b22ffd442f
2009-11-27DB: Abstracted all ORDER BY clauses in the DB abstraction layerYorhel1-7/+11
The ORDER BY was previously specified using an 'order' argument, which would then be directly inserted into the query. The new method is the same as what I used for the public API: two 'sort' and 'reverse' arguments. This should be less error-prone and more readable. This changes quite a lot of code, so I hope I haven't forgotten to update something along the way.
2009-10-10Display number of unread posts in user menuYorhel1-1/+11
Instead of displaying the total number of threads. Posts are marked as read when the thread is opened.
2009-08-08Converted threads_posts.date and edited to timestamptzYorhel1-5/+5
Everything still seems to be working fine so far.
2009-08-06More logical naming of dbGetPost date filter3dB1-3/+3
2009-08-05Removed unnecessary export.3dB1-1/+1
-- Removed unneeded export of previously deleted subroutine dbPostCheckDouble
2009-08-05Reworked double post checking to use existing code3dB1-21/+3
-- Removed dbPostCheckDouble -- Added more filters to dbPostGet to serve similar purpose
2009-08-04Implemented double-post prevention3dB1-1/+21
-- Created a subroutine to check for any recent posts made by a user -- Added double-post checking and error messages to the form handler & html generator
2009-07-07Posts browser on user pages (/u+/posts)Yorhel1-6/+26
2009-07-07Hide threads targeted at users from the main pageYorhel1-1/+3
2009-03-22Merge branch 'tagging' into betaYorhel1-29/+29
Conflicts: lib/VNDB/DB/Discussions.pm util/updates/update_2.3.sql
2009-03-21Replacing all occurences of 'discussion tags' with 'discussion boards'Yorhel1-27/+27
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-02-18Don't count deleted threads on number of threads on item pagesYorhel1-2/+4
2008-12-09Code cleanup: removed leading spaces and combined ↵Yorhel1-2/+2
DB/{Votes,VNList,WishList}.pm into ULists.pm
2008-11-16Added number of threads to discussion tabYorhel1-1/+13
2008-11-16Tiny bugfix with thread creationYorhel1-1/+2
2008-11-16Added reply/post edit/new thread functionalityYorhel1-1/+91
Most of the code, or at least the design, was stolen from 1.X, as those functions were fine. Links to these forms is still pending...
2008-11-15Browsing threads on tags now worksYorhel1-6/+34
2008-11-15Added tag listing to thread pagesYorhel1-2/+32
Not sure about the style, but at least it works. Thread page is now pretty much finished as well, yay!
2008-11-15Made a start with the thread pages + layoutYorhel1-0/+60