I’ve been charged with the task of changing one particular lookup table/entity from ID of UUID to integer ID. However, in my testing of how to accomplish this, I’ve had no luck in figuring out how to do it.
I’m wondering if there is an easier way to do this, or if I will need to create the entity again with a BaseIntegerEntity parent, duplicate the entity structure and re-populate the data from the existing table (and then create new fields in the referring entities to populate the existing data by changing them by hand).
Is there a clean way to do this, or will it inevitably get very messy?
make a development copy of the database, and its backup, or at least the tables in question, with some data
in the lookup table, create the new unique integer field, that can be null
populate that new integer field with values - is it supposed to be some sort of autoincrement later, or are you taking the values of the next key from the keys table containing the next value
create the new to-be foreign key field in the tables that are using the lookup, and populate that field with the corresponding values based on the old lookup field values
(all this can be done with few SQL commands, but you will need to experiment maybe a bit, hence you can recover fast from the backup)
now, depending on your database engine capabilities, put it as primary key, set not null where applicable, then reimplement referential integrities and indexes for the new fields, and drop the old ones (if you cant set the new primary key, you will need to make a copy of the table but with the changes in its structure, and then pump the data from old to new)
if you want to keep old data and fields, you can continue filling it with UUID values, or just make it not required - I would keep them
now comes the CUBA part, create a new project, connect to the development database, and let CUBA reverse engineer these tables and create entity classes, then maybe create some CUBA screens, try if it works
check the class files and see what CUBA did differently, e.g. the field definitions in entity classes, copy-paste that changes into the original source, don’t forget to check the Instance name (@NamePattern) because it’s used in the lookups to display the entity instance name
should work now, test it, then make a backup of your production database, disable users connecting to your app and database, and make the changes
Thank you for taking the time to reply, and in such detail. I was afraid that it would be this complicated. I had only hoped there would be a smoother solution.