search

rezalas / riftshadow

Dedicated to the preservation of the game and modernization of a classic mud codebase.
MIT License
18 stars 14 forks source link

Refactor SQL scripts #193

Open sean-gilliam opened 2 years ago

sean-gilliam commented 2 years ago

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 commented 2 years ago

I agree. Are there any takers on this?

sean-gilliam commented 2 years ago

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

Psypher9 commented 2 years ago

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

sean-gilliam commented 1 year ago

@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 commented 1 year ago

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

rezalas commented 1 year ago

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

rezalas commented 1 year ago

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

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 commented 1 year ago

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

rezalas commented 1 year ago

closed after minor patch

sean-gilliam commented 1 year ago

Lessons learned from previous attempts.