I have a database that contains a version table with one value stored in it which is the database version number.
I would like to be able to update the database version automatically using numbered scripts.
If the database is version "6" and I have a dir of scrips numbered inconsistently as per the below:
001.sql, 02.sql, 3.sql, 4script.sql, 5.sql, 6.upgrade.sql, 7.sql, 8.data.sql, 009.updated.sql
What is the best way to only run scripts which are numbered greater than the version number and to do this in sequence?
Should I use an SQL script or create a bash script?
I am a little stuck on how to approach this so any pointers or advice will be much appreciated...