Generation undo-scripts, when generation db-sctipts

[former_member217396]

Hi,

did someone managed, to generate an DB-undo-script at the time where the database scripts are generated?

I'm talking about an database revert script generated during the time when calling "Apply Model Changes to Database".

So let's say when calling "Apply Model Changes to Database", we've got the following scripts:

1) database create/alter script:

create table TABLE_4

    (  COLUMN_1            CHAR,

        COLUMN_2            CHAR,

        COLUMN_3            CHAR );

alter table TABLE_1

    add COLUMN_5 CHAR;

2) Now, what I want to generate as well is an database revert script, looking like this:

drop table TABLE_4;

alter table TABLE_1

  drop column COLUMN_5;

BR,

Rafal

[former_member217396]

Hi Ondrej, hi Thierry,

the proposed by you solution is known to me. The question was more like in the direction - is it possible, to do it in "one run" or not? Seems not.

Another question (related to 😞

the scenario we have here is the following. We have a 3-level-structure we have to work with.

- There are 3 different environments

- 4 releases per year

- xx logical unit of work in each release.

So you can imagine we have something like this:

- Production

  - Rel201601

  - Rel201605

- Test

  - Rel201601

    - LUW1

    - LUW2

    - LUWx

  - Rel201605

    - LUW10

    - LUWy

  - Rel201607

    - LUW20

    - LUW21

- Development

  - Rel201601

    - LUW1

    - LUW2

    - LUWx

  - Rel201605

    - LUW10

    - LUWy

  - Rel201607

    - LUW20

    - LUW21

  - Rel201611

    - LUW30

    - LUW31

Now imagine, an new Object (DB-table W) is part of LUW20 and LUW21, but the scenario is the following one:

- in LUW20, the table W has the following columns A (PK), B and C

- in LUW21, the table W has the following coumns  A (PK), D, X and Y

The requirement is:

1) to generate CREATE/ALTER statements per LUW. As the Database generation script operates on Metaclass/Table level, I'm not sure how to handle this. What I can do, I can mark the column for LUW20 and LUW21, create a script, which will generate a new model only with those attributes, and then create the CREATE/ALTER scripts from that model.

2) if we have the situation, the table is new... we would have the CREATE TABLE statement for both LUWs, the LUW20 and LUW21. This will obviously not work at the end. They would prefer to have script with an embedded logic, to check, if the object already exists in the database or not. If not, then it would be create, if it exists, nothing would happen. But... this has some additional implications... for example, we would have to have an CREATE for the table itself and the PK attributes, but for all others we would have to have an ALTER TABLE adding the additional (new) columns one by one... this is (as per what I know at the moment) not possible at all in PD. Why do they want to have it like this? Because, it might happen, that on LUW will not make it into Production, because of bugs found during SIT etc.

Any ideas how to handle such case?

BR,

Rafal

[Former Member]

Hello Rafal,

This is quite simple:

- Create your base model

- Save your model as an archive

- Adapt you model to generate TABLE_4 and modifications to TABLE_1

- From Database menu, use "Apply Model Changes to Database" to generate modifications (Point 1), use archive model as previously created; click OK until you can edit your script.

- Save your model as an achive

- Make modifications: Delete TABLE_4 and drop COLUMN_5 from TABLE_A

- From Database menu, use "Apply Model Changes to Database" to generate modifications (Point 2), use archive model as previously created; use "Use alter statement when possible"; click OK until you can edit your script.  You should have what you expected.

Kr,

Thierry

[Former Member]

Hi Rafal,

I think, that such a smart code-generating feature cannot be created using simple GTL in xdb file. I think, that you should create an automation, which would create a model from your current DB (perhaps archive model capabilities can be used for that) BEFORE you trigger the "Apply model changes to DB". After the changes are performed in DB, you should run it again, but from the archived model into current (and already modified) DB. Then you will get your undo scripts for your exact changes using only standard functions of PD (archive model, ModifyDatabase function). Perhaps you will need to adjust this proposed process according to the capabilities of functions present in PD API.

HTH,

Ondrej