Updating the physical database structure

Whenever we make a change to the database structure within the project, the physical structure does not update automatically; it is therefore necessary to carry out some realignment operations. As we all know Recreating the structure of a database also means deleting all the data contained; in the initial stages of testing we can also delete all data but when the change is made with the application already operational, and in use by users, total deletion is absolutely an operation not to be carried out.

With the logic of the ZZ_OBJECTS table, which we saw in the previous tutorials, the update operation is painless and quick: this is because the actual update instructions will always be processed by making a comparison between a hypothetical ZZ_OBJECTS of the project tree and the present ZZ_OBJECTS on the database.

In the youbb project we have pending the update of the last view created; first of all, before proceeding we can make a comparison between the database in the project and the physical database to understand the changes that will be made; Project tree , database, right click Show structure differences . We note that a difference is signaled to us in correspondence with the newly created View.

Let's move on to the physical update that we can make from the compilation window Edit Menu -> Compile Project and set Create DDL, Run DDL and this time Edit Structure .

In the second tab I disable all the flags as we do not yet have an application to compile and I proceed with the OK button, the comparison will be made, the ddl file created and executed in the database engine: we check the database presence of sight.

It will always be possible to Update the database or sometimes it will be necessary to Recreate it, well to this question I answer It depends ... it depends on the changes we have made, it depends on the presence or absence of data in the database: for example the change of a primary key field on a table populated is an operation that could fail.

If you are unable to update, you have to recreate, go back to the compilation window choose Create DDL, Execute DDL, Recreate Structure. If you want the present data to be kept, check Save existing data; this is a feature offered by the environment and not by the database engine; when the Ok button is pressed, queries are performed to retrieve all the database data, they are saved on a file with the DAT extension, then the structure is recreated (completely empty) and the data from the previous files are reimported: therefore you understand that the operation will have a time proportional to the amount of data contained and the number of tables in the database.