All Use Cases
When your database schema changes you can import those changes and update your Dataedo documentation schema in a similar way you imported it.
Import your updates
To update documentation click Update documentation button for the ribbon.
This will open update wizard. The first screen is a connection form. Connection details are populated from last connection - at import or last update. If you didn't use 'Save password' option at last connection then you need to enter the password.
Note: you can change connection details to point to a different database (even on a different server) In this case, use 'Reimport all objects' option that is available in next step. This may cover scenarios where you migrated databases to a new location or you want to switch between Dev, Test and Live environments.
By default, the import will use the same filter you used last time. To change the filter, check the Advanced options box.
Next form will present changes in database schema since last import. You may choose to exclude some of the new objects from importing by unchecking them. You may also choose to include previously ignored objects by checking them.
List shows:
- New - objects that were added to schema since last import
- Deleted - objects that were deleted from schema since last import
- Updated - objects that were updated since last import (e.g. column was added)
- Ignored - objects previously ignored from import
To import changes click Import button.
After update, all new, updated and deleted objects in navigation tree will be marked with icons.
How it works
Dataedo gets (filtered) a list of objects (tables, views, stored procedures and functions) from the server with last modification date (this does not include MySQL - see details in the chapter below). Then it compares list by names with the list from a repository. Objects that do not exist in the repository are considered new, objects that do not exist in the database are considered deleted. Objects that names match but server modification date is newer than the one in the repository (dbms_last_modification_date column) are considered updated.
New objects
New objects are simply added to the repository.
New objects are marked with star icons in a navigation tree. The icon disappears after view gets refreshed.
Deleted objects
Deleted objects are marked as deleted but are still visible in the editor. All content is preserved. You may preview such objects and decide to delete permanently from the repository - all content will be lost. Deleted objects are marked with red cross icons in navigation tree.
Deleted objects and its elements are ignored from all exports.
Note: once you delete the object from repository its name gets to the list of ignored objects and if the object with that name will be created again it will be ignored from future imports. Read more about ignoring and removing objects from documentation.
Updated objects
If an object is detected as updated its definition is read again and compared to the one in the repository. Each element (column, key, relation, trigger, parameter) is looked up by the name - if found, then attributes (data type, nullable, relations' and keys' columns, etc.) are updated in the repository. New elements are added to the documentation. Missing elements are marked deleted just as it happens with objects (for more details go to Deleted columns, keys, etc. section).
Existing descriptions are never overwritten. Descriptions are imported from the database only when empty in the repository.
Updated objects are marked with green tick icons in the navigation tree. The icon disappears after view gets refreshed.
Renamed objects
If the object gets renamed it behaves just as if it was deleted and created with a new name. This works for objects (tables, views, stored procedures and functions) and their elements (columns, unique keys, relations, triggers...).
If the object was marked as deleted you need to review its descriptions. If there were any entered in Dataedo repository with the editor you might want to copy them to new object. This needs to be done manually for main object description and all its elements (i.e. columns, keys, etc.).
Once you no longer need any descriptions of deleted object you may remove it permanently from the repository. You can do it with Delete key or Remove from repository option in ribbon or context menu (right click).
Deleted columns, keys, etc.
Similarly to objects (table, views, etc.) also their elements (columns, keys, parameters, etc.) are marked deleted when missing in import. The idea is the same - copy the description and delete with Delete key or Remove from repository option in context menu (right click).
Renamed columns, keys, etc.
Renamed columns, keys, parameters etc. work the same as objects - element with an old name gets marked as deleted, and an element with a new name is created. And again, copy the description from old to the new element if required and delete the old element.
Force full reimport
For various reasons, you may want to reimport entire schema, not just changes detected with Dataedo. This should have the same effect and cause no problems. The only disadvantage is the long time of the operation.
Some of the reasons you may want to do this:
- You changed source database (for instance from Live to Dev) were last updated stamps are different
- You upgraded Dataedo to new version which imports data that previously was not supported
- You are unsure for any of the above and want to make sure everything is captured
To perform full reimport just check Full reimport checkbox on Filter objects screen.
MySQL
MySQL works engine is a little bit limited compared to SQL Server and Oracle. It doesn't support last updated markers of tables and other objects and therefore Dataedo always performs a full import of MySQL databases (force full import checkbox is always selected).
Updating descriptions from DBMS
Dataedo repository is a master data source for all descriptions. This means that once you entered a description with Dataedo editor or imported with schema they are never overwritten with subsequent schema updates, even if descriptions changed in the database. Those changes are ignored.
However, there are some cases when descriptions are imported while updating schema:
- New object (table/view/procedure/function) - if a new table was added to the database, update behaves exactly as first import - table is imported with its descriptions as new.
- New element (column/parameter) - a new column or parameter was detected in the database, it is read just as a new object and description from DBMS is imported with it.
- Empty description in the repository - if the description of object or element is empty in the repository, Dataedo will read descriptions from DBMS and insert it into the repository.
Forcing reimport of descriptions from DBMS
According to the previous section, if you want to reimport descriptions or comments defined in DBMS you should remove descriptions from the repository (using the editor or manual SQL query directly in the repository) and update database schema with Dataedo.