Complex Scenarios and solutions

Below you'll find some complex scenarios which describe how sync source definitions manage what's done when sync is performed. They describe a situation and solutions how to overcome a specific problem.

Starting model first with additional database originating elements following

In this scenario you start with an entity model and add a relational model data container which means the sync source is by default set to Model. Clicking Perform Tasks in the Sync Relational Model Data tab will make the designer create all the tables, mappings, primary key fields, foreign key constraints and unique constraints. You're then able to export a DDL SQL script.

After the DDL SQL script has been ran on the db, the DBA adds a couple of stored procedures and a view to the schema to make it easier to work with certain data in the code. As the sync source for the complete database is set to Model it's not possible to simply click Perform Tasks and pull in the stored procedures and the view, even though the implied sync source is 'database' for these elements.

Solution

You should change the sync source for the schema to Mixed. This means the tables are still synchronized with the entity model, but the rest is synchronized with the database (as that's implied anyway). After changing the sync source, you click Perform Tasks and the catalog refresh is started for everything except tables. The user can select the stored procedures from the schema elements in the wizard and import them into the project.

Starting model first and an additional catalog from same server has to be added

After the script has been exported, you're told to include an additional catalog from the DB server with an already existing schema to the project. This is not directly possible as the existing database in the project is set to Model.

Solution

To solve this, you sets the sync source for the relational model data Container to Mixed. Clicking Perform Tasks will open the relational model data retrieval wizard to obtain the relational model data for the new catalog(s) to include in the project.

Entity model in project and mapped on model synced schema. Existing catalog is added to project

In this scenario you have an entity model in the project which is mapped onto a schema with sync source set to Model for SQL Server. The model now also has to be mapped onto an already existing schema, in another database of another type, e.g. PostgreSQL. You add this schema and catalog through Add relational model data from a database. However, the entities are not automatically mapped onto the new catalog.

Solution

If a new catalog is added through sync, the database sync task will auto-map existing unmapped entities after the sync process to targets in the database of the sync task, but only to existing targets; it won't create new tables as the sync source is set to Database. If a new catalog is added through the normal UI (i.e. using Add relational model data from a database), an auto-map to existing targets is issued after the relational model data is added. The log for this auto-map action is shown after the relational model data retrieve action has taken place. The auto-map is a single action that can't be undone.

FK on table FkSide in schema SD points to table PkSide in SM (both DB synced and in same catalog). Then SM gets sync source set to 'Model' or 'Mixed'

In this scenario a schema crossing FK is present on the FK side table FkSide in schema SD. It points to the PK side of it, PkSide, in schema SM. Both schemas have their sync source set to Database. Syncing both schemas with the catalog will bring the FK back from the DB in the meta-data.

Now the Sync source of SM, which contains PkSide is set to Model (or Mixed). Syncing the schemas again with their sources will now result in the FK not being read from the database schema anymore, as the PK side, PkSide, isn't read from the database, as the sync source of SM is set to Model' (or Mixed). This leads to a warning that the relationship between the entities mapped onto FkSide and PkSide has no backing FK anymore.

Solution

The user has to mark the relationship in the entity model between the entities mapped onto FkSide and PkSide as 'Model Only'.

Auto-map of field fails due to type not found

If an entity contains e.g. a Guid typed field, automapping it to e.g. Oracle will fail, as there are no type conversions defined. You are now unable to create the field unless specific actions are taken.

Solution

You have to perform several steps:

Make sure that Auto assign type converter to field mapping is checked in Project settings / Entity Model / General.
Make sure there's a type conversion defined with a type converter which can convert Guid values, to either byte[] or string.
Make sure the type conversion defined has a Length filter set for 32 or 36 length. If this isn't done, the default types will be BLOB for byte[] and NCLOB for string. With the proper length defined more appropriate types are chosen.

Sync with model fails as model element has error, you fix the error

In this scenario you have created an entity model and it's mapped onto a schema with sync source set to Mixed or Model. When you try to Sync the relational model data, the designer can't sync everything as there's an error with a model element, e.g. a relationship has an error as there's no FK field. This means the relationship isn't going to get a backing FK constraint.

As this is an error, the real-time validation system will pick this up and code generation won't be possible. You see the error and correct it. However by fixing the error, the error is gone but there's still a pending sync action. This is not obvious however: you might have forgotten the sync failed due to the error (e.g. because you went home right after that).

Solution

Validation will pick up differences in types, FK/PK/UC missing from relational model data etc., so the validation performed prior to code generation will pick this up: if a model is valid it means the relational model data matches it, otherwise an error would be produced. You can then perform the sync again to make the relational model data match the model.