3. Revision SQL files Namaing Convention#
The tool discovers and applies SQL files to the database for version control.
These files need to follow a certain naming conventions.
All SQL files are considered revision files
They must follow the pattern RV[0-9]*__.*.(upgrade|downgrade).sql. In words, it means
They start with the prefix RV
After RV, it follows an arbitrary revision number (e.g. RV1, RV2, RV3 etc. etc.)
After RV(arbitrary_revision_number), it follows double underscores and an arbitrary number of characters. Everything after __ describes what the SQL file does.
After RV(arbitrary_revision_number)__(description), it follows a dot and the character group of either upgrade or downgrade. When applied, an upgrade revision file will move the database version upward by 1, while a downgrade revision file will move the database version downward by 1.
After RV(arbitrary_revision_number)__(description).(upgrade/downgrade), it follows a dot and the character group of sql .
Overall, RV(arbitrary_revision_number)__(description).(upgrade/downgrade).sql
Example SQL revision files
RV1__create_scm_company_secrets_and_tbl_earnings.upgrade.sql
RV1__delete_scm_company_secrets_cascade.downgrade.sql
RV2__alter_scm_company_secrets_tbl_earnings_updated_at_add_index.upgrade.sql
RV2__alter_scm_company_secrets_tbl_earnings_updated_at_remove_index.downgrade.sql