Refactor SQL scripts

[sean-gilliam]

Currently, there exists two SQL files used to set up the database. These files are rift.sql and rift_core.sql. These files are huge text splats that contain a mix of schema definitions and data. In their current state, it makes it hard to reason about or make changes to the files / tables / data.

We should think about breaking these schemas / data bits into their own files in a db directory or some other suitable named directory. While we're at it, we could format the files better and include better documentation.

Possible directory structure:

db
|-- tables
|---- bans.sql
|---- ...
|-- data
|---- bans_initial.sql
|---- ...
|-- migrations
|---- 20221101_alter_ban.sql

[Psypher9]

I agree. Are there any takers on this?

[sean-gilliam]

I was thinking about doing it unless someone else wants to take it up.

[Psypher9]

I'll hold off if you've got your eye on it

[sean-gilliam]

@Psypher9 (or anyone else) If you want to take this on, then you are more than welcome. I'm going to focus on other issues in the code for the time being.

[Psypher9]

Alright, sounds good! I'll see if I can make a space for it

[rezalas]

This cleans up a lot of ambiguity in the SQL data structure.

[rezalas]

I'm building a new environment and when executing the dev-install.sh script I received a few errors during the SQL setup portion.

• It locks during the mariadb install, so you have to ctrl+c to continue it
• the scripts throw the following errors during data import

Importing votes data
--------------
LOCK TABLES `votes` WRITE
--------------

ERROR 1146 (42S02) at line 4 in file: 'votes.sql': Table 'mysql.votes' doesn't exist
--------------
/*!40000 ALTER TABLE `votes` DISABLE KEYS */
--------------

ERROR 1146 (42S02) at line 6 in file: 'votes.sql': Table 'mysql.votes' doesn't exist
--------------

[rezalas]

Okay I see the problem, the data scripts don't use the appropriate database first. I can get this added into them.

[rezalas]

closed after minor patch

[sean-gilliam]

Lessons learned from previous attempts.

• We need to fully understand the relationship between the database and game files before attempting another go at this.
• After refactoring the scripts into separate files, do a clean setup install from a fresh mysql instance. Recompile the project and attempt to start the server. A clean start should ensure the refactor is successful.
• Line that begin /*! are not comments. eg. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; and should be included in the scripts.