Database structure update

Avatar

By CaerCam 23 July 2015 14:24

Member · 54 comments

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?

Avatar

By CaerCam 23 July 2015 15:16

Member · 54 comments

… 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.

Avatar

By Yannick 23 July 2015 16:14

Administrator · 1,390 comments

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

Avatar

By CaerCam 23 July 2015 17:17

Member · 54 comments

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.

  • The forums table could lose its color column to the forum_meta table
  • The topics table could lose its solved column to the topic_meta table
  • The posts table should lose its poster, poster_ip, poster_email, hide_smilies, edited and edited_by columns to the post_meta table
  • The users table could lose its url, msn, location, signature, disp_topics, disp_posts, email_setting, notify_with_post, notify_pm_full, auto_notify, show_smilies, show_img, show_img_sig, show_avatars, show_sig, timezone, dst, time_format, date_format, language, style, use_pm, notify_pm, first_run, facebook, twitter, google, color_scheme and accent columns to the user_meta table

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.

Avatar

By CaerCam 24 July 2015 09:02

Member · 54 comments

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.

Avatar

By Yannick 24 July 2015 16:19

Administrator · 1,390 comments

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).


You can do anything

Avatar

By CaerCam 24 July 2015 16:45

Member · 54 comments

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.

Avatar

By Yannick 24 July 2015 18:09

Administrator · 1,390 comments

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?


You can do anything

Avatar

By CaerCam 24 July 2015 22:22

Member · 54 comments

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:

  • discussions: table containing a private discussion's details, mostly the title and participants
  • messages: table containing a private discussion's messages
  • 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.