Script naming file to specify the order instead of numerical numbering

[tech7857]

Hi @rdagumampan

We have a requirement where instead of numbering the files to specify the order , there should be a file which has the script name in the sequence specified to be deployed.

reason- if we have 1000 scripts numbered and suddenly we have add a script in between we will have reorder all of them and it is very tedious task.

is this possible and can be accommodated?

thanks

[rdagumampan]

@tech7857, interesting use case and thanks for reaching out. On hindsight, every change we applied the database is considered immutable and any new scripts will just be another immutable version thats needed to be applied. I tried to imagine when your case would be possible. Maybe, if you use a tool to dump each object as file and order them with sequence number. I agree that can be tedious. Can you use timestamp as a work around? Like prefixing the files with YYMMDDTMMSS during generation time?

I will investigate this further.

P.S. Please star and fork our repo. It goes a long way in getting better stats and helping more people discover this tool :) Thanks!

[tech7857]

@rdagumampan Thanks for taking a look. The main problem comes when we have to add a dependency between the scripts, so even timestamping wont solve.

It will be best if there is a sequence file which has the script laid out in order and application reference that file to apply the scripts in order

thanks

[rdagumampan]

@tech7857 I understand and it makes perfect sense. I will consider this feature in the next release and you are always welcome to submit a PR. If I understand this right, it would be represented like this.

_manifest.ini

script3.sql
script1.sql
script2.sql

directory structure

\v0.00
\v0.00\_manifest.ini
\v0.00\script1.sql
\v0.00\script2.sql
\v0.00\script3.sql

[tech7857]

Thank You @rdagumampan for considering this. Yes, this looks great. Can the same be done for the _erase directory as well.

Also it would be helpful to know when is the next release scheduled. We are trying to implement this in our release as quickly as possible.

Thanks Again

[rdagumampan]

@tech7857 the next release would be v1.3 which is set in summer June 2022. But I started implementing this and would be available via nightly build of v1.2.x. I will tag you here when its ready, perhaps this week we'll have something working.

[tech7857]

@rdagumampan This is great.. Thanks for getting this so quickly. Just one thing - the same will be implemented for the _ erase directory also?

[rdagumampan]

...
INF   2022-03-06 19:41:08Z   Found 5 script files on <redacted>\yuniql_testdb_c7ff7be2\v0.00
  + test_v0_00_01.sql
  + test_v0_00_02.sql
  + test_v0_00_03.sql
  + test_v0_00_04.sql
  + test_v0_00_05.sql
INF   2022-03-06 19:41:08Z   A custom execution sequence manifest is detected. Scripts will run as listed in the content of _sequence.ini file. Any scripts not listed in the manifest will be skipped and will not be committed in the version where it is placed. Skipped scripts can only be executed by moving them to the next version. Expected sequence: 
  + test_v0_00_03.sql
  + test_v0_00_01.sql
  + test_v0_00_05.sql
  + test_v0_00_02.sql
  + test_v0_00_04.sql
DBG   2022-03-06 19:41:08Z   Executing sql statement as part of : <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_03.sql
DBG   2022-03-06 19:41:08Z   Executing statement DAFE03: 

CREATE TABLE public."TEST_DB_OBJECT_3" (
    TEST_DB_COLUMN_1 INT NOT NULL,
    TEST_DB_COLUMN_2 VARCHAR(255) NULL,
    TEST_DB_COLUMN_3 VARCHAR(255) NULL
);

DBG   2022-03-06 19:41:08Z   Statement DAFE03 executed in 10 ms
INF   2022-03-06 19:41:08Z   Executed script file <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_03.sql.
DBG   2022-03-06 19:41:08Z   Executing sql statement as part of : <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_01.sql
DBG   2022-03-06 19:41:08Z   Executing statement 5BBB5A: 

CREATE TABLE public."TEST_DB_OBJECT_1" (
    TEST_DB_COLUMN_1 INT NOT NULL,
    TEST_DB_COLUMN_2 VARCHAR(255) NULL,
    TEST_DB_COLUMN_3 VARCHAR(255) NULL
);

DBG   2022-03-06 19:41:08Z   Statement 5BBB5A executed in 7 ms
INF   2022-03-06 19:41:08Z   Executed script file <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_01.sql.
DBG   2022-03-06 19:41:08Z   Executing sql statement as part of : <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_05.sql
DBG   2022-03-06 19:41:08Z   Executing statement AEC204: 

CREATE TABLE public."TEST_DB_OBJECT_5" (
    TEST_DB_COLUMN_1 INT NOT NULL,
    TEST_DB_COLUMN_2 VARCHAR(255) NULL,
    TEST_DB_COLUMN_3 VARCHAR(255) NULL
);

DBG   2022-03-06 19:41:08Z   Statement AEC204 executed in 7 ms
INF   2022-03-06 19:41:08Z   Executed script file <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_05.sql.
DBG   2022-03-06 19:41:08Z   Executing sql statement as part of : <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_02.sql
DBG   2022-03-06 19:41:08Z   Executing statement 9B1683: 

CREATE TABLE public."TEST_DB_OBJECT_2" (
    TEST_DB_COLUMN_1 INT NOT NULL,
    TEST_DB_COLUMN_2 VARCHAR(255) NULL,
    TEST_DB_COLUMN_3 VARCHAR(255) NULL
);

DBG   2022-03-06 19:41:08Z   Statement 9B1683 executed in 8 ms
INF   2022-03-06 19:41:08Z   Executed script file <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_02.sql.
DBG   2022-03-06 19:41:08Z   Executing sql statement as part of : <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_04.sql
DBG   2022-03-06 19:41:08Z   Executing statement AFB650: 

CREATE TABLE public."TEST_DB_OBJECT_4" (
    TEST_DB_COLUMN_1 INT NOT NULL,
    TEST_DB_COLUMN_2 VARCHAR(255) NULL,
    TEST_DB_COLUMN_3 VARCHAR(255) NULL
);

DBG   2022-03-06 19:41:08Z   Statement AFB650 executed in 8 ms
INF   2022-03-06 19:41:08Z   Executed script file <redacted>\yuniql_testdb_c7ff7be2\v0.00\test_v0_00_04.sql.
....

[rdagumampan]

@tech7857 , please try this v1.3 pre-release and let me know. This enhancement works in any directories and including all subdirectories. You can place _sequence.ini in the version directories like v0.00, in the non-version directories like _erase or in the child directories of those directories like v0.00\setup-tables\.

...
INF   2022-03-06 19:41:08Z   Found 5 script files on <redacted>\yuniql_testdb_c7ff7be2\v0.00
  + test_v0_00_01.sql
  + test_v0_00_02.sql
  + test_v0_00_03.sql
  + test_v0_00_04.sql
  + test_v0_00_05.sql

INF   2022-03-06 19:41:08Z   A custom execution sequence manifest is detected. \
Scripts will run as listed in the content of _sequence.ini file. Any scripts not listed in the manifest will be skipped \
and will not be committed in the version where it is placed. Skipped scripts can only be executed \
by moving them to the next version. Expected sequence: 
  + test_v0_00_03.sql
  + test_v0_00_01.sql
  + test_v0_00_05.sql
  + test_v0_00_02.sql
  + test_v0_00_04.sql

[tech7857]

@rdagumampan Thanks for getting this in so quickly. It is looking great. I will test this more and update you.

[rdagumampan]

@tech7857 I am assuming this works out fine for you so I am closing the ticket. Please reopen if needed. Thanks again for your feedback. Have nice weekend!

[tech7857]

Hi @rdagumampan Yes all my test cases passed. One thing I wanted to check if in erase directory I create a sub directory with sequence file, the script execution doesn't recognize it. Although we will not be creating a sub directory under erase but wanted to check if its expected?

Also when can this be released officially?

Thanks again and have a great weekend.