Can't create a migration script based on static data

Bas_KBas_K Oud Beijerland, The NetherlandsPosts: 15 Bronze 1
Hi Guys,

I'm trying to create a migration script to run AFTER my static data has been deployed. The migration script contains a select from the static data table that is ran in the same deployment script. Redgate sees the migration script as a model change aswell, which means it's embedded in the same script. Sadly the order of scripts being ran is in my case wrong. The static data should first be inserted, after that the migration script needs to run. Is there any way I can do this?

I saw the article that this does not seem to work: https://documentation.red-gate.com/display/SOC5/Static+data+and+migrations

Tagged:

Answers

  • way0utwestway0utwest CO, USA Posts: 307 Rose Gold 1
    There isn't a good way to do this now. The ordering of scripts isn't something you can control. The only good way to do this is schedule two deployments, where you deploy static data changes ahead of the migration script.

    May I ask what the migration script is doing?
  • Bas_KBas_K Oud Beijerland, The NetherlandsPosts: 15 Bronze 1
    edited October 3, 2017 10:24AM
    Deploying in 2 seperate deployments works.. Sadly when I'm going to production the 2 changes are combined into 1 "big" deploy, which creates an error again..

    I have the following tables as static data:

    dbo.Permission
    dbo.Action

    My migration script is as followed:

    INSERT INTO dbo.RoleAction
    ( FK_RoleId ,
    FK_ActionId ,
    FK_PermissionId ,
    Enabled
    )
    VALUES ( 1000 , -- FK_RoleId - int
    2028 , -- FK_ActionId - int
    3340 , -- FK_PermissionId - int
    1 -- Enabled - bit
    )

    This fails in production because the static data is not yet deployed and the migration script already runs..

    Anyway to solve this?
  • way0utwestway0utwest CO, USA Posts: 307 Rose Gold 1
    This is a hack, but what I'd do for this case, which is a bit of an edge case, is cause production drift. I'd test this, but have the script executed in production first manually. Then I'd ensure my migration script is idempotent, so :angry:
    if not exists( select fk_actionid from RoleAction where fk_roleid = 1000 and fk_actionid = 2028 and fk_permissionid = 3340 and enabled = 1)
    INSERT INTO dbo.RoleAction
    ( FK_RoleId ,
    FK_ActionId ,
    FK_PermissionId ,
    Enabled
    )
    VALUES ( 1000 , -- FK_RoleId - int
    2028 , -- FK_ActionId - int
    3340 , -- FK_PermissionId - int
    1 -- Enabled - bit
    )
    
    Note, I'd be careful here with using static values. This is a place where the db development can go south. Ideally you'd insert using some other tokens, like the text or action, and let the ID values fall into place. If you're sure they're always in sync, then this can work, but usually this bites you later.

    I realize this is a hack, and gets away from automation, but is this something you do often with migration scripts?
  • Bas_KBas_K Oud Beijerland, The NetherlandsPosts: 15 Bronze 1
    Meeh.. I actually need to do another query to validate that the insert statement is valid. Because Permission and Action are also static data, I need to check if the "newly" added row inside the Action and Permission table is being added. This also means I need to run the deploy twice for my production env, which is not really recommended..

    Is there any other way to do this? Instead of this "hack"? :s

    I would love to see this "fix" on the roadmap! I got like 3 manual steps now in my deployment process because of this. It really slows down our automation process.

    Cheers
  • Sujith_Chowdari123Sujith_Chowdari123 Posts: 2 New member
    edited May 24, 2021 4:53AM
    Have the same issue. Is there any solution for this in Redgate source control 7 version? I hope not. Also I wonder this is something many users of the product must be facing already, why isn't there a fix for this?
Sign In or Register to comment.