You can reverse an existing database schema into an MDriven Designer UML model when you need to understand legacy data, build ViewModels, or work with that database through MDriven.

Reverse engineering reads the database schema—its tables, columns, primary keys, and foreign keys—and creates a model from that information. It creates classes from tables, attributes from columns, and associations from primary-key/foreign-key pairs. It does not infer relationships that exist only in application SQL and are not declared as foreign keys.

Before you start

Prepare a database that you can inspect and, if you intend to test writes or evolve the schema, safely modify. For a production system, begin with a backup or a non-production copy rather than the production database.

You need:

• MDriven Designer and a model file. You can start with a new model and save it before reversing.
• Network access and database credentials that can read the schema.
• A valid connection string for the database provider you select in the reverse-engineering dialog.
• A target package that you are willing to replace for a full reverse.

Important: selecting Go! for a full reverse clears the contents of the package you started from and fills it with the discovered schema. Do not use it on a package that contains model work you need to keep.

For example, if you have restored a copy of a Microsoft sample database to a local SQL Server, use the connection string for that server and database. If connection analysis reports that the SQL Server certificate chain is not trusted, the walkthrough uses TrustServerCertificate=true in the connection string. Follow your organization's connection-security requirements when using this setting.

Reverse a database into a new package

1. In MDriven Designer, create or open a model and save it.
2. In the model tree, select the package that will receive the reversed classes.
3. Right-click the package and choose Functions > Reverse Database.
4. Select the database option that matches your database and enter its connection string.
5. Click Analyze db. MDriven reads the schema and displays the discovered tables.
6. Review the analysis before changing the model. Use Reverse worker when you need to inspect tables, columns, primary keys, and foreign keys in more detail.
7. When the result is acceptable and the selected package may be replaced, click Go!. Confirm the warning.
8. Review any warnings about unsupported or unclear database types. The reverse process can represent types it cannot determine as strings; correct the resulting model types where the application requires a more precise meaning.
9. Inspect the model tree. Drag important classes onto a diagram to review the generated attributes and associations.

A database table named Album becomes an Album class. A column such as Title becomes an attribute. If Album.ArtistId is declared as a foreign key to Artist.ArtistId, the reverse process creates an association between Album and Artist.

Watch the AdventureWorks reverse-engineering walkthrough for an example of analyzing a database, reviewing discovered keys, and refining the resulting model.

Inspect and refine the generated model

A reversed model is a starting point. Compare it with the database schema and with the way the existing application uses the data.

Check keys and identity columns

For a table that uses a database-generated integer identity key, retain the primary-key information found during reversing and configure the mapping so MDriven uses that key scheme instead of a standard ECO_ID key.

1. Select the reversed class and inspect its persistence mapping properties.
2. Verify the primary-key setting created by reversing.
3. Set the PrimaryKeyMapper to AutoInc for normal database auto-increment behavior.
4. Select the key attribute and mark it DbAssigned.

DbAssigned tells ECO not to supply the primary-key value on the initial save. It saves the row and reads it back so that the database-assigned value is available for later use, including as a foreign key on related objects.

For example, if RecordId is an int identity column, configure RecordId as DbAssigned. A newly saved record then receives its key from the database rather than requiring the client to invent one.

Add meaning that a schema cannot express

Database types do not always communicate the intended UI or domain meaning. Add that meaning to the model where needed.

For example, a product-photo column may reverse as a byte array. Change it to a blob and mark it as an image when it represents image data. This lets generated forms recognize that the value is a photo rather than an arbitrary byte array.

= Test reads and writes

Run the model against the same database to inspect real, changeable data with generated forms or the EcoSpace debugger. You can use AutoForms to browse classes and follow generated associations while you learn the data.

When testing inserts, satisfy the database constraints. For example, if Album.ArtistId and Album.GenreId are NOT NULL foreign keys, assigning only an album title will fail. Assign an existing Artist and Genre before saving the new Album.

After you understand the model, create focused ViewModels rather than relying on generated forms as the final user interface. A search ViewModel rooted in a Product type can begin with generated form logic and then be reduced to the fields and filters that users actually need.

When relationships or tables are missing

Reverse engineering can only use metadata declared in the database. A database can be valid for an existing application while still lacking foreign-key constraints, primary keys, or complete type information. In that case, the reversed model will also be incomplete.

Typical symptoms include:

• A table with no declared primary key is not discovered as a normal class.
• A link table has foreign-key-like columns but no declared key, so its associations are not created.
• Existing code joins tables in SQL without a declared foreign-key constraint.
• Provider-specific or complex column types are represented as strings until you refine them.

For example, a FilmActor table that links Film and Actor, but has no declared primary key, may not produce the expected associations automatically. Define the missing mapping in the model after you confirm how the table is keyed and joined.

Add mapping manually

You can manually complete the model when the database metadata is incomplete:

1. Add the missing class if the table was not represented.
2. Define the class primary key in its persistence mapping properties.
3. Add attributes for the required columns and set their persistence mapping information.
4. Add associations for the real foreign-key relationship, including relationships that are enforced by application convention rather than a database constraint.
5. Compare the model with the database schema and test the mapping against real data.

This approach is appropriate when you know the schema but need to restore information that the database does not declare.

Use Reverse worker for incremental mapping work

Do not click Go! when you are largely satisfied with an existing model and only need to investigate or add missing mapping details. Instead:

1. Right-click the relevant package and choose Functions > Reverse Database.
2. Enter the connection string and click Analyze db.
3. Click Reverse worker.
4. Select a table to inspect its columns.
5. Select columns and use Use as PK(primary key) or Attribute or Use as FK(foreign key) as appropriate.
6. Continue to step 4, Find possible actions in the Model.
7. Review the suggested action, such as adding an association or, when no model class represents the table, adding a class.
8. Apply only the changes that match the verified database design.

Reverse worker reduces the need to switch to a separate SQL browser and manually copy database names into persistence mapping properties. It assists the same mapping work; you remain responsible for verifying that the suggested key and relationship reflect the real database design.

Evolve a reversed database carefully

After you modify a reversed model, you may want MDriven to calculate and apply the corresponding database changes. Database evolve compares the schema derived from the current model with a previously stored script. A reversed database does not have that prior MDriven script automatically.

To enable this workflow, allow MDriven to write the current script to the database once before you make model changes. In MDriven, run the model and perform that one-time initialization for the reversed database. MDriven then has an old script to compare with the new script derived from later model changes.

For example, after initialization, adding a required ReleaseDate attribute to Album can produce an ALTER TABLE operation that adds the corresponding column with a default value.

Review the generated SQL script before evolving. Reversed databases can contain indexes and other database objects that are not fully represented in the model. An evolve operation can therefore encounter a database error, such as an index that the generated operation cannot remove. Investigate the named database object directly, correct it only when you own and understand it, and then run the operation again.

For code-based and runtime evolution operations, see HowTos:Evolve Database with Code. For a database that must be recreated rather than evolved, see HowTos:Recreate the SQL Database.

Prepare for normal MDriven model work

A database created from a model receives MDriven administrative tables automatically. A database that was reversed from an existing schema may need those tables before you use it as a standard MDriven model and add new model classes or tables. Use MDrivenServer to create them; the manual process and background are described in HowTos:MDriven Administrative Database Tables.

Checklist

Stage	Verify
Analyze	The connection string works, the expected tables appear, and warnings have been recorded for review.
Reverse	The target package is disposable, because Go! replaces its contents.
Validate	Classes, attributes, primary keys, and foreign-key associations match the schema and real application behavior.
Refine	Identity keys are configured with AutoInc and DbAssigned where the database assigns the value; special data such as images has the required model meaning.
Test	Generated forms or the debugger can read data, and test inserts supply all required NOT NULL relationships.
Evolve	The initial script has been stored, generated SQL has been reviewed, and database objects outside the model have been considered.

See also

• HowTos:Evolve Database with Code
• HowTos:MDriven Administrative Database Tables
• HowTos:Recreate the SQL Database
• ViewModel

Reverse engineer an existing database

MDriven's reverse mechanism reads the database schema and creates model elements from it:

• Tables become classes.
• Fields become attributes.
• Primary-key and foreign-key pairs become associations.

The resulting model can be used to understand the database and to create ViewModels that show real data with WECPOF.

Review the result against the intended model

A database schema may not fully describe the intended information model. For example, SQL can join tables even when no explicit primary-key or foreign-key definition exists. As a result, a reversed model can be missing associations, classes, or attributes.

You can correct missing persistence information manually by adding classes, specifying their primary keys in the properties, and adding associations.

Use Reverse worker to add missing mapping details

The Reverse worker provides an assisted way to inspect schema details without switching to a separate SQL browser:

1. Right-click the package and choose Functions and Reverse Database.
2. Select Analyze db to display the schema.
3. Open Reverse worker instead of selecting Go! when the existing package is largely correct and only needs additional details. Selecting Go! clears the existing package content and fills it with content found in the schema.
4. Select a table and inspect its columns.
5. Use Use as PK(primary key) or Attribute and Use as FK(foreign key) to select columns.
6. Continue to step 4, Find possible actions in the Model, to review suggested actions, such as adding an association or, when applicable, adding a class.

Prepare a reversed database for Database Evolve

Database Evolve calculates database changes by comparing a new database script with an old database script. Therefore, ECO must be allowed to store the current script in the database; otherwise, it has no old script against which to compare the script derived from the current model.

For reversed databases, this storage setup is a one-time operation before making model changes with Database Evolve. Standard ECO databases already have this setup.

When evolving the database, review the SQL Script tab in the dialog to see the script ECO will send to the database. The source example shows that adding a required ReleaseDate attribute to Album produces an ALTER TABLE statement that adds a non-null DATETIME column with a default value.

See also

• HowTos:Reverse Engineer a Database

Reverse-Engineering an Existing Database into MDriven

Reverse engineering creates a UML model from an existing database. The reverse mechanism uses the database schema to create classes from tables, attributes from fields, and associations from primary-key and foreign-key pairs.

Before you begin

You need a valid connection string to the database.

The database schema may not contain all information used by the application. For example, application code can join tables in SQL even when the database has no explicit primary-key or foreign-key definition for that relationship. Review the reversed model and add missing details where necessary.

Create a model from the database schema

1. Open the package that will contain the reversed model.
2. Right-click the package and choose Functions and then Reverse Database.
3. Provide a valid connection string.
4. Select Analyze db to read the schema.
5. Review the analysis and select Go! to create the model.

Warning: Selecting Go! clears the existing content of the selected package and fills it with the classes, attributes, and associations found in the database schema. Use this operation when you want to replace the package contents with the reversed result.

After the reverse operation, drag classes from the model tree onto a diagram to inspect the generated model.

Complete mappings not expressed by the schema

If the reversed model is largely correct but is missing classes, attributes, primary-key information, or associations, add those details manually. You can define a class's primary key in its properties and add missing associations to describe relationships that are not declared as database foreign keys.

MDriven also provides an assisted workflow for adding such details:

1. Right-click the package and choose Functions and then Reverse Database.
2. Select Analyze db.
3. Open Reverse worker rather than selecting Go!.
4. Select a table and inspect its columns.
5. Use Use as PK(primary key) or Attribute to select columns as keys or attributes.
6. Use Use as FK(foreign key) to select foreign-key columns.
7. Continue to step 4, Find possible actions in the Model.
8. Apply an offered action, such as adding an association or, where applicable, adding a class.

The Reverse worker performs the same type of mapping work that can be done manually while avoiding the need to switch to a separate SQL browser to look up schema details.

Handle database-assigned integer keys

For database identity keys, configure the model to use the database key scheme. The reverse process creates integer attributes for integer identity columns. Set the primary-key mapping to AutoInc and mark the key attribute as DbAssigned. This causes ECO to omit setting the primary key on the first save and read the saved row back to obtain the database-assigned value.

Where a database foreign key is NOT NULL, assign the required associated object before saving a new object.

Prepare for database evolution

To use the Database Evolve mechanism after reversing a database, allow ECO to store the current database script in the database. Database Evolve compares the script derived from the changed model with an earlier script; without a stored earlier script, it has no old script to compare.

This is a one-time operation for reversed databases. Standard ECO databases have this information from the start. The SQL Script tab shows the script ECO expects to send to the database during evolution.

When extending a reversed model with new classes or tables, add the MDriven administrative database tables required to fully support standard MDriven models.

See also

• Making sense of legacy data–DB Reverse
• The MDriven administrative database tables