Not sure if I talked about this yet, so at least there will be a dedicated topic.
I think we should introduce new tables to the database and clean up the existing ones. The user table is the best example: it currently contains things that should absolutely not be there: social networks, display choices (avatars, img, signatures…). All these fields should be stored in a metadata table. It will lighten the database by not using/querying big numbers of fields, and more important, it will be more extensible for devs: instead of having plugins that create new fields and add them the numerous queries, we'll have plugins that use specific rows from a table meant just for that purpose.
The same logic can be applied on private messages, forums, topics, posts, groups… Maybe we should brainstorm a bit on a new database schema to implement in the next major release?
… Furthermore, we should probably rewrite the Database layer, too. It is kind of obsolete, to say the least… PDO and query preparation should be a priority.
I agree, there is quiet some work to be done on the database front of the software, I'm open to ideas.
You can do anything
Raw ideas: introduce meta tables for all major existing tables: forum_meta, topic_meta, post_meta and user_meta. These would only contain a set basic fields: id, slug_id, meta_key, meta_value, unique (slug_id corresponding to forum_id, user_id, etc). unique could be useful to limit the metadata to a single occurrence.
Coupled with some OOP on the related code parts (forums, topics, posts and users mostly) we could lighten the database structure and usage, optimize caching, simply coding and power plugins.
Now, maybe some of this will affect a bit the performance of the queries compared to what Luna (and FluxBB) currently offers; the way the database structure is built makes it quite fast to use and load. But the major disadvantage is that it keeps the code in a prehistoric state where you have to alter a whole lot of SQL queries to include birthdate to your users… Plus that structure was design more than a decade ago, when server resources were not remotely close to what they're now. I guess that the price to pay to have a really extensible software.
Priority number 1 should be to implement a better database layer. FeatherBB uses Idiorm, PantherBB uses a PDO-like lib; I don't really like the former, and the latter seems limited to MySQL. But there definitely is a choice to be made here; I don't think the development of Luna can go any further without this first step… The second and third being the database structure evolution mentioned above, and a code refactoring to use more object programming and open the way to plugins.
I agree with the majority of your draft, I'll write down a full one in the coming days. Anyway, I'm wondering wheter or not we should go with our own Database Layer or just pick an already existing one. I'm currently looking into the later with https://github.com/jaredtking/php-database. It's a really simple one and sure can use some work, but like I said: it's a really simple one. Or should we spend some time on writing our own (trough I'm prety sure that including an existing one will require some big changes to it either way).
Looks pretty cool! It will indeed require (very) important changes, but I'm thinking we should group them with a major code rework. I've started coding some classes for users, posts, topics and forums that will get along well with an updated database layer.
It will need some thinking too, as it implies a change of practice. Currently loading a forum page (or a topic page) consists in a single query that fetches all topics/posts from the forum/topic along with related data (user and post data mostly) and a big piece of code to handle the result. Big advantage is that it's a single query; disadvantage is that we get lots of useless information. If we're loading a topic page and a particular user has 10 posts in that page, we're loading its data ten time, once with each post.
Using objects we're running three queries instead of one: one for topics, one posts, one for users. Big advantage is that we only load once what we need: the 10-posts-user data will be loaded once in the total page load; if another piece of code tries to fetch its data later in the process, it's already there. And there we see the point of splitting tables: when loading a user we'd be loading only its basic data, not every possible meta. Disadvantage is, we're running three queries instead of one. That's not necessarily a problem, but it's a choice to be made.
If done right, it shouldn't be much of a performance issue.
I would like to see your work if you're done. Already based on that specific layer?
Anyway, during my write down of the draft for the database, I was also thinking about merging the topic_subscriptions and forum_subscriptions tabels into one table and add a third "type" field to them. This makes subscriptions a little more extensible to - for example - blog posts, etc. for when a plugin needs it.
I'm also looking for a way to merge the posts and messages tables. Or should we keep these out of each other?
Still work in progress, but I'll share it when viewable 😁 It's still based on the current db layer until a choice is made.
Good idea for subscriptions. I don't think messages and posts should be merged, though. For privacy reason, at the very least. But we could probably improve the way messages work, having multiple duplicates of each messages is really a waste of space when we could use a simpler and more efficient structure:
messages_meta: table containing a private discussion's messages meta, mostly the viewed status per user
Messages are unique, a user that remove a private discussion from its inbox results in simply removing him from the participants. If he was the last participant to the discussion, discussion and messages deleted from db.