Flarum database changes

changes.md

Conventions

• Column names according to data type:
  • DATETIME or TIMESTAMP -> {verbed}_at (eg. created_at, read_at) or {verbed}_until (eg. suspended_until)
  • INT that is a count -> {noun}_count (eg. comment_count, word_count)
  • Foreign key -> {verbed}_{entity}_id (eg. hidden_user_id)
    • Verb can be omitted for primary relationship (eg. post author is just user_id)
  • BOOL -> is_{adjective} (eg. is_locked)
• Table names:
  • Use plural form (discussions)
  • Underscores for multiple words (access_tokens)
  • Relationships: two table names joined with underscore in alphabetical order (eg. discussions_users) so that Eloquent can automatically figure it out
• Extensions
  • Prefix table names with vendor_package_table
  • Prefix column names with vendor_package_column
  • Core extensions are exempt

Changes

access_tokens

• RENAME id -> token
• ADD created_at
• MODIFY last_activity_at -> DATETIME
• RENAME lifetime -> lifetime_seconds

api_keys

• RENAME id -> key; ADD id surrogate key
• ADD allowed_ips
• ADD user_id (nullable)
• ADD scopes
• ADD created_at
• ADD last_activity_at

RENAME auth_tokens -> registration_tokens

• RENAME id -> token

discussions

• RENAME comments_count -> comment_count
• RENAME participants_count -> participant_count
• RENAME number_index -> post_number_index
• RENAME start_time -> created_at
• RENAME start_user_id -> user_id
• RENAME start_post_id -> first_post_id
• RENAME last_post_time -> last_posted_at
• RENAME last_user_id -> last_posted_user_id
• RENAME hide_time -> hidden_at
• RENAME hide_user_id -> hidden_user_id

RENAME users_discussions -> discussions_users

• RENAME read_time -> last_read_at
• RENAME read_number -> last_read_post_number

email_tokens

• RENAME id -> token

flags

• RENAME time -> created_at

RENAME permissions -> groups_permissions

RENAME users_groups -> groups_users

RENAME mentions_posts -> posts_mentioned_posts

RENAME mentions_users -> posts_mentioned_users

notifications

• DROP sender_id
• DROP subject_type
• RENAME time -> created_at
• RENAME is_read -> read_at
• RENAME is_deleted -> is_hidden

CREATE notifications_from

• id
• from_user_id

password_tokens

• RENAME id -> token

posts

• RENAME time -> created_at
• RENAME edit_time -> edited_at
• RENAME edit_user_id -> edited_user_id
• RENAME hide_time -> hidden_at
• RENAME hide_user_id -> hidden_user_id
• MODIFY content -> LONGTEXT

CREATE posts_users

• post_id
• user_id
• Empty by default - placeholders for extensions wanting to add fields

settings

• MODIFY value -> LONGBLOB (serialize all values)

tags

• RENAME discussions_count -> discussion_count
• ADD post_count
• RENAME last_time -> last_posted_at
• RENAME last_discussion_id -> last_posted_discussion_id
• ADD last_posted_user_id

RENAME users_tags -> tags_users

• RENAME read_time -> marked_as_read_at

users

• RENAME is_activated -> is_email_confirmed
• DROP bio (move to extension)
• DROP preferences
  • Instead: add a new column to store additional data, add a new table if data is big
  • NO Entity-Attribute-Value tables. See here and here.
• RENAME join_time -> joined_at
• RENAME last_seen_time -> last_seen_at
• RENAME discussions_count -> discussion_count
• RENAME comments_count -> comment_count
• RENAME read_time -> marked_all_as_read_at
• RENAME notifications_read_time -> read_notifications_at
• RENAME suspend_until -> suspended_until

CREATE users_users

• user_id
• other_user_id
• Empty by default - placeholders for extensions wanting to add fields (eg. friend users, ignore users)