Data Type Conversion Settings
Features and Tools > Model Actions > Model Conversion > Data Type Conversion Settings
Before you start the conversion process, you can set conversion settings for data types.
- Enable Expert mode: Select Settings | Options | General | check the Expert Mode checkbox.
- From the main menu, select Expert Mode | Expert Mode Settings | Data Type Conversion Settings.
- In the Source and Destination boxes choose the desired databases.
- Press to display available data types. Click to view only exceptions.
You can see a list of data types of source model that will be converted to data types in destination model.
Example: Oracle Blob data type will be converted to Microsoft SQL Server 2012 model as Image data type. You can change the destination model data type.
|
Note: The modified database conversion settings are saved to DataTypeConversion.txn file stored by default in: C:\Users\*username*\AppData\Roaming\Quest Software\Toad Data Modeler\*Installation name*\Configs. If you want to restore the original conversion settings, you can simply delete this .txn file. |
- Confirm OK.
Execute Script
If you want to use your own script for data type conversion, click the Go to Exception Script button. A dialog offer you to create a new script. Confirm and the newly created script will be shown in Script Editor, where you can edit it as you like.
Example:
function Convert(){
var Log = System.CreateObject('Log');
Log.Information('Konverze');
Log.Information('Input MD: '+ InputModelDef.Abbrev );
Log.Information('Output MD: '+ OutputModelDef.Abbrev );
Log.Information(InputDataType.ConversionID.Code);
var outDT
if (InputDataType.ConversionID.Code == 'C_X'){
Log.Information('HERE');
for( i = 0; i < OutputDataTypes.Count; i++ )
{
outDT = OutputDataTypes.GetObject( i );
Log.Information( outDT.Name );
if (outDT.Name=='Box') {
return(i);
}
}
}
return(0);
}
PER - PER Conversion Information
Features and Tools > Model Actions > Model Conversion > PER - PER Conversion Information
See some basic information on what is converted and how during the PER to PER conversion.
- Data Types are converted.
- Permissions are compared by names.
- SQL code is copied and commented not to generate any code.
See other details here:
Data Types |
|
Supported data type |
Supported data type |
Same data type |
Example*: |
Integer |
Integer |
Integer |
|
Supported data type |
Equivalent data type |
Equivalent data type |
Example**: |
Float |
Real |
Real |
Permissions |
|
Supported Permission |
Supported Permission |
Same Permission |
Example: |
SELECT |
SELECT |
SELECT |
|
Supported Permission |
Unsupported Permission |
--None-- |
Example: |
DROP (MySQL 5) |
--None-- (Microsoft SQL 2005) |
--None-- (Microsoft SQL 2005) |
|
Unsupported Permission |
Supported Permission |
Model B Permission |
Example: |
--None-- (Microsoft SQL 2005) |
DROP (MySQL 5) |
DROP (MySQL 5)
(DROP preserved in already existing model.) |
Deny Permission |
|
Deny Permission Supported |
Deny Permission Supported |
Deny Permission Converted |
|
Deny Permission Supported |
Deny Permission Unsupported |
--None--
|
|
Deny Permission Unsupported |
Deny Permission Supported |
Model B Deny Permission |
Example: |
MySQL 5 |
Microsoft SQL 2005 |
Microsoft SQL 2005 (Deny permission preserved in already existing model.) |
Grantor |
|
Grantor Supported |
Grantor Supported |
Grantor Converted |
|
Grantor Supported
|
Grantor Unsupported
|
--None--
|
|
Grantor Unsupported
|
Grantor Supported
|
Grantor is empty. |
Example: |
MySQL 5
|
Microsoft SQL 2005
|
Microsoft SQL 2005 (Grantor preserved in already existing model.) |
Users and User Groups |
|
Supported |
Supported |
Converted successfully |
|
Supported |
Unsupported |
--None-- |
|
Unsupported |
Supported |
--None-- |
Example: |
Users unsupported in PostgreSQL |
Users supported in Microsoft SQL 2005 |
--None-- |
Model A - Currently opened model that you want to convert.
Model B - Model to which you want to convert Model A.
Complete Model A is converted to Model B.
Other Information
- Conversion between PostgreSQL and Microsoft SQL: Serial and BigSerial data types in PostgreSQL are converted to Identity in Microsoft SQL. Identity in Microsoft SQL is converted to combination of sequence and default "nextval" in PostgreSQL.
- Conversion between PostgreSQL and MySQL: Serial data type in PostgreSQL is converted to Autoincrement in MySQL. Autoincrement in MySQL is converted to combination of sequence and default "nextval" in PostgreSQL.
- Conversion between PostgreSQL and Oracle: Serial and BigSerial data types in PostgreSQL are converted to combination of sequence and a sequence selected for attribute in Oracle. A sequence selected for attribute in Oracle is converted to default "nextval" in PostgreSQL (the sequence is converted automatically).
- Conversion from Oracle to Microsoft SQL (and Microsoft SQL to Oracle): Automatic conversion of Identity in Microsoft SQL to Sequence in Oracle (and back).
- Conversion from Oracle to MySQL (and back): Sequence in Oracle are converted to Autoincrement in MySQL (and back).
- Conversion from Microsoft SQL to MySQL (and back): During conversion of Identity (Microsoft SQL) to Autoincrement (MySQL) and back the new IdentitySeed/Initial Autoincrement is taken into consideration. (In version 3.4, only conversion between checkbox Identity and Autoincrement was possible.)
- Model Conversion from MySQL to Microsoft SQL and Oracle: MySQL Enum data type is converted to Char data type, a check constraint for the attribute is created, the parameter is preserved (see the SQL tab of the Check Constraint Properties dialog).
* Data types conversion examples - conversion from MySQL 5 model to SQL Server 2005 model.
** See the equivalent data types for conversion in the Settings menu | Data Type Conversion Settings.
PER - LER Conversion Information
Features and Tools > Model Actions > Model Conversion > PER - LER Conversion Information
See the following basic information on what is converted during PER to LER and LER to PER conversion and how.
Data Types |
Similar to PER to PER conversion. |
The conversion rules should be defined in the Data Types Conversion Settings dialog. |
The conversion rules should be defined in the Data Types Conversion Settings dialog. |
Self Relationship |
In PER model, only non-identifying self relationship is supported. |
Self relationship is converted properly. |
Identifying self relationship will change to non-identifying self relationship. |
Cardinality |
In PER model, cardinality of one side of relationship is 1..n.
|
Cardinality is converted properly. |
E.g. 2..5 cardinality in LER model is converted to 1..5 in PER model. |
Parent Key |
Parent key can be defined in LER model.
In LER model, open the Relationship Properties dialog | General tab | Foreign Unique Identifier box.
(In PER model, Parent Key is defined in the Relationship properties dialog | Foreign Keys tab.) |
Parent key in PER model is different than PK (alternate key, unique attribute or index). -> Parent key defined in PER model is converted to LER model properly. |
Selected UI of LER model is converted to PER model (PK and appropriate alternate keys are created). |
Foreign Keys |
In LER model, keys are not transferred from parent to child entity.
|
No FKs are displayed in child entity in LER model. |
FKs that are not displayed in LER model are visible in child entity in PER model. |
Primary Keys |
|
PK (Primary key) in PER -> PUI (Primary unique identifier) in LER |
PUI (Primary unique identifier) in LER -> PK (Primary key) in PER |
Alternate Keys |
You can select alternate key as a parent key. |
AK (Alternate key) in PER -> UI (Unique identifier) in LER |
UI (Unique identifier) in LER -> AK (Alternate key) in PER |
NN versus M Attributes |
NN - Not Null in PER model.
M - Mandatory in LER model.
The values can be displayed in ER diagram. |
NN -> M |
M -> NN |
Inheritance |
Inheritance is not supported in PER model. |
-- |
Conversion of inheritance to PER model will be executed by the rules set in the Inheritance dialog | Generation tab. |
Valid Values in Attribute |
Valid values are supported only in LER model.
They can be defined for the following data types: Bigint, Float, Integer, VarChar. |
--
(Check constraints from PER model are not converted to LER model.) |
Default values in LER model -> Check constraints in PER model. |
Defaults for Attributes and Domains |
|
Converted properly. |
Converted properly. |
Rules for Attributes and Domains |
In LER model, attributes and domains can have rules.
In PER model, attributes and domains have check constraints and these check constraints can have rules. |
Attribute check constraint has rules in PER model. -> Rules for this attribute are converted to LER model. |
Attribute has rules in LER model. -> Check constraint with this rule for the attribute is available in PER model. |
LER to PER Conversion - Self-Relationship
If there is a self-relationship in LER model, the entity has two columns, both of the same name (primary key), in converted PER model. Other modifications are necessary.
Possible solution: You can define a name for the propagated attributes in LER model before the conversion. Open the Attribute Properties dialog | General tab | enter the name to the Propagated Name box.
If this box is empty, Toad Data Modeler will behave standardly (two columns of the same name in PER model).
Convert Model
Features and Tools > Model Actions > Model Conversion > Convert Model
Toad Data Modeler allows you to convert:
- Physical models from one database platform to another (e.g. Oracle 10g model can be converted to SQL Server 2008 model)
- Physical model to Logical model
- Logical model to Physical model
The conversion can be executed via:
- Model Actions | Convert Model or Model Menu | Convert | Run
- Simple Model Conversion in Model Menu | Convert
How to Convert Model
Physical to Physical
- Select your desired database platform.
- Pay attention to the Conversion Settings dialog.
|
Note: Converting between different platforms means that some items, which contain SQL code, may not be converted correctly (e.g. After Scripts, Before Scripts, Procedures...). In this case, it is recommended to check the Comment Out Database Specific Items checkbox. This way, SQL code, that cannot be successfully converted, will be commented out. You can review these parts of code later and fix them on your own. |
- Choose what object types will be converted. You can access full list of Objects and Properties by clicking on Detailed Settings.
- Check items you want to convert to another model. For easier item management use buttons located on the top.
- A review dialog shows with some statistics about how many and what kinds of objects will be converted. Once you're done reviewing, click on Finish.
|
TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information. |
- The conversion process occurs and your model is now converted to another database platform.
Physical to Logical
- Select Logical model from the list of available database platforms.
- Pay attention to the Conversion Settings dialog.
|
Note: Since Logical Model doesn't have any objects which contain SQL, all your procedures, scripts and functions will be lost. |
- Review the statistic and when you're done, click the Finish button.
- After a short while your Physical model will be converted to Logical model.
|
TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information. |
Logical to Physical
|
Note:
Before you convert your Logical model to Physical model, you should be aware of the following:
- Physical model supports only non-identifying self relationship.
- Inheritance is not supported in PER model. Toad Data Modeler solves this by converting Inheritance object into either Single Table, N-1 Tables or N Tables. See Inheritance.
- Keys in LER models do not migrate.
- You can select a linking method in LER model.
- M:N relationships are supported in both models.
- Before you start the conversion, you can set up the conversion rules in the Data Type Conversion Settings dialog. This option is available only if Expert Mode is enabled.
- Cycled relationships will be ignored during LER to PER conversion and will not be converted. A message informing you about this will be displayed in Message Explorer Log.
|
- Select your desired database platform.
- Change any of the conversion settings, if necessary.
- Check items you want to convert to another model. For easier item management use buttons located on the top.
|
TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information. |
Selection Tree Overview
|
Display options for properties. |
Right-click an item to see the following options:
Expand All Children |
Expands all sub-items of the selected item. |
Collapse All Children |
Collapses all sub-items of the selected item. |