Processes to drop some objects from database

Description

Actually there are processes that allows to drop indexes and views from the database, and it's very helpful to avoid going to DBeaver to manipulate the database directly.

It would be a good improvement if we add also processes that allows to:

  • Drop a foreign key

  • Drop a column

  • Drop a table

A bit more complex, but a big plus (to avoid losing data with drop/recreate) would be also to have processes for:

  • Rename a column

  • Rename a table

Environment

None

Activity

Show:
Carlos Ruiz
February 18, 2021, 6:12 PM

Thanks - the pull request is ready for peer review again.

Diego Ruiz
February 17, 2021, 12:29 PM

Hi ,

 

The pull request you created solves the issue you describe, however, while testing the PR I encountered a different issue. The AD_Sequence is not updated so if there are records already in the table being renamed, the next time you try to save a new record it will throw an error like:

 

And you need to click save many times until it gets to an unassigned ID.

 

Steps to reproduce:

  1. create a new table with columns (for example copying from M_FreightCategory)

  2. synchronize columns (to create the table)

  3. run the "Create window from table" process to create window, menu, etc

  4. Open the created window and create a new record

  5. Then rename the table > Cache reset

  6. Open the window again and create a new record

 

Expected result

The record is saved

Actual Result

The system tries to assign a used ID and throws an error.

 

 

Best regards,

Diego Ruiz

Carlos Ruiz
February 16, 2021, 2:22 PM

When this is used in conjunction with CreateWindowFromTable from IDEMPIERE-1026, then renaming the table leads to error because the OrderByClause in AD_Tab already contains a reference to the original table name.

Pull request 590 improves this situation.

Diego Ruiz
December 9, 2020, 2:29 PM

Ok,

About 1. As I said it was meant just to maybe add a message at the end to remind the user to run cache reset.

About 3. Expectation and reality can differ sometimes and if the feature is not meant to be used in production, perhaps it should be disabled in production based on som flag or create a warning if it is not expected/desired to be used in production environments because it is hardly likely that even when it is not expected to be used there, it will.

I would then just suggest that it must be really well documented after approval (https://wiki.idempiere.org/en/NF8.1_Drop_Rename_DB_Objects) and perhaps state clearly the risks and side-effects that it can have (I would suggest the same for Drop Column as it can have similar corrupted data effects and nowhere in the documentation wiki is mentioned), if you expect the users to “know what they are doing” it is better to give them all the information.

Best regards,

Diego Ruiz

Carlos Ruiz
December 9, 2020, 2:17 PM

Thanks for the peer review:

1 - yes - Cache Reset is a normal requirement when you add/remove/change a column

2 - I thought the same but AD_Column doesn't have IsCentrallyMaintained flag and we are indeed discussing that because of that AD_Column_Trl must be changed to a view

3 - My expectation here is this is used when you're defining things in development - usually not in a production stuff, and people must know what they are doing - same thing as you describe can happen if somebody goes to SalesRep_ID and change the reference to become a Charge - or things like that - playing with the dictionary is always a dangerous thing and I would expect that people using Dictionary know what they are doing.
I can add a validation to forbid changes when there is a foreign key, but in the end it just cripples the use of some possible cases (for example if the column was just created wrongly)

Regards,

Carlos Ruiz

Fixed

Assignee

Carlos Ruiz

Reporter

Carlos Ruiz

Labels

None

Tested By

None

Fix versions

Priority

Major