TECHNOTE: 7792 - DDL SUMMARY: DDL 6. > ROSE DDL 6.0 >> DDL INTRO 6.1 >> REVERSE ENGINERING 6.1.1 >>> ERWIN TO ROSE 6.1.2 >>> ROSE TO ERWIN 6.1.3 >>> ERWIN HELP 6.2 >> GETTING STARTED EXAMPLES 6.3 >> LANGUAGE SPECIFIC OUTPUT 6.4 >> DDL PROPERTIES 6.4.1 >>> TYPE: ATTRIBUTE 6.4.2 >>> TYPE: PROJECT 6.5 >> ASSOCIATION 6.6 >> NAVIGABLE 6.7 >> MULTIPLICITY 6.8 >> KEYS/QUALIFIERS : CREATE INDEX 6.9 >> FOREIGN KEY COLUMN NAME 6.10 >> AGGREGATION 6.11 >> DEPENDENCY, INSTANTIATES, REALIZES 6.12 >> GENERALIZATION 6.13 >> LINK ATTRIBUTE 6.14 >> VERSION\PATCH INFO 6.15 >> DDLGEN ERRORS 6.16 >> QUESTIONS AND ANSWERS ================================================================== 6. > ROSE ================================================================== 6.0 >> DDL INTRO Rational Rose 98 and 98i allows you to generate DDL (database definition language) scripts which will build your database tables and views for you, based upon a model you've built in Rose. This script is save to a text file which you can then use with any program that understand SQL code. DDL code-generation features include: Generation of tables Generation of application and base views Expression of primary keys Generation of primary keys (if not specified) Generation of indices Change of attributes into columns Support for single classes, relationships, and inheritance hierarchies Support for data integrity constraints including: null, not null, unique, primary key, foreign key, check, references, delete cascade, secondary indices, composite uniqueness, and composite indices NOTE: There is support for MODELING triggers and stored procedures by imbedding these in functions attached to the class BUT code for these are not generated by Rose! Rose DDL generation in Rose 3.0, 4.0 and 98/98i have been tested with the following databases: Oracle 7 SQLServer 6 ANSI 89 Sybase 10,11 Watcom 4 Support for DDL generation prior to Rose 4.0: Rose/PowerBuilder 2.7, Rose/SQLWindows 2.7 and Rose/Visual Basic 3.0 have support the generation of SQL/DDL for all persistent objects in a Rational Rose class diagram. The following databases are supported: Rose -> PowerBuilder - SQLWindows - Visual Basic Watcom x x - Sybase x x - Oracle x x x ANSI x x x SQLBase x x - SQLServer x x x Other 3.0 versions did not have DDL capibilities built in. There was a DDL add on, on Rational's web site for Rose\C++ 3.0. This add on enabled you to generate DDL from Rose\C++ 3.0. This add on was made to give this capibility to C++. This add on is no longer necessary since all current shipping varients of Rose now ship with DDL generation. ------------------------------------------------------------------ 6.1 >> REVERSE ENGINERING All members of the Rational Rose 98 product family include support for the creation of relational database logical models. Rose reverse engineer Oracle8 tables and only Oracle8 via the Oracle8 addin that ships with Rose 98 Enterprise Edition. If you wish to reverse engineer tables other than Oracle8 (or have the need to do E-R diagrams) Rational recommends you use Rose in conjunction with Logic Works ERwin. An ERwin Addin ships with Rose98 Enterprise Edition (and available for downloading for other edtions). This Addin will convert a ERwin file to a Rose model file and vise versa. ERwin forward and reverse engineers databases and generates SQL schema, triggers and stored procedures for all major SQL and desktop databases. Through ERwin's Windows interface, users can draw a graphical, Entity-Relationship (E-R) model of the business rules governing the data in the application. ERwin can be used to create the physical models and their implementations on the relational database. Once the relational database design is completed, the reverse engineering capabilities can be used to update the Rational Rose model. There are two directions in which one can exchange data; between ERwin and Rose, and between Rose and ERwin. 6.1.1 >>> ERWIN TO ROSE The Rose 98 Enterprise edtion shiped with the ERwin 3.0 add-in. You will need to obtain the update mentioned below if: - You are working with ERwin 3.5. - You have Rose 98 Professional or Modeler editions. The ERwin addin doesn't ship with these versions, but you can still download and use it with these versions. - You are working with Rose 98i. This addin no longer ships with 98i but you can still download it and use it with 98i. You can obtain the addin directly from the following logicworks site: http://www.platinum.com/products/appdev/lw/logiclinks/linkware.htm ====================== For Users of ERwin 3.0 ERwin Translation Wizard For Users of ERwin 3.5 ERwin Translation Wizard ====================== ERwin/Rational Rose Translation Wizard. This program converts ERwin/ERX version model files into a Rational Rose model. It converts ERwin Entities, Relationships, Attributes, and Data Types to Rose Objects, Associations, Attributes, and Data Types respectively. This program requires Windows 95 or Windows NT (32-bit environments). To use after installing make sure ERwin option is check in, Tools:Add-Ins and then, Tools:ERwin Translation Wizard:Translate Model Translate ERwin Model into Rose Refer to the README.TXT for more product information. ERWIN 2.5: If you have ERwin 2.5 you can use an older intergration link. This Integration Link takes a Logic Works ERwin/ERX version 2.5 ERX file and converts it to a Rational Rose version 3 PTL file. This file can then be read into Rose versions 3,4 or 98\98i. This utility can be found at ftp://ftp.rational.com/public/rose/rose_cpp/patches/ filename: rose32.zip This integration link requires Windows 95 or Windows NT (32-bit environments). It will not run under DOS or Windows 3.x (16-bit environments). 6.1.2 >>> ROSE TO ERWIN There are two way you can move model information from Rose to ERwin. 1) You can also use the ERwin add-in to convert Rose models into ERwin models. Tools:ERwin Translation Wizard:Translate Model Translate Rose Model into ERwin Make sure the classes you wish to generate code for are marked persistent. Otherwise the translation wizard, will report success, but diagram is blank in ERwin. Additionally, Go to ERwin tab in class specification, and make sure Type is set to Table or View. 2) Rational Rose can generate DDL files that can be captured by ERwin or other database mapping tools. In Rose: Make sure the classes you wish to generate code for are marked persistent. Otherwise you will wind up with a zero byte file. Highlight classes, then go to Tools:DDL generation. In ERwin: File:Open, List files of type, SQL DDL (*.sql,*.ers) and open the .SQL file generated from Rose. 6.1.3 >>> ERWIN HELP Once you have this addin activated you should find the following in on line help: Index tab, ERWIN ERwin properties aggregation association attribute class dependency generalization model ERwin to Rose translation monitoring performing selecting ERwin objets Selecting the translation direction setting up programmatic datatype mappings specifying the ERX file ERwin Translation Wizard accessing montoring the translation process seleting ERwin objects selecting RTose objects selecting the translation direction using or Help, Contents tab, ERwin Translation Wizard Rational shiped the add-in with Rose 98. However it is not a Rational product. Logic Works developed and owns the source code for this addin. Support, defects, usage issue should go thru them. For more information regarding intergration contact PLATINUM Technical Support, USA and Canada call: 800-833-PLAT for help desk (Platium) ERwin support number: 609-514-2020 Other countries, contact your local PLATINUM office Web site problem form and other ERwin tech support resources: http://support.platinum.com/byp/erwin/ ------------------------------------------------------------------ 6.2 >> GETTING STARTED EXAMPLES SQL\DDL Code Generation: 1. Open your Rose model and select the persistant classes for which you want to generate code. If no classes are selected, the generator will attempt to provide an entire schema script for all PERSISTANT classes. 2. Be sure that each selected class's specification contains the desired attributes. Define Length and Type for each attribute. 3. If the default Code Generation properties for a class component or the model itself are inappropriate, edit these properties, or attach an appropriate property set. 4. Choose your database of choice from the DDL Setup Dialog box after selecting DDL generation. Be certain that the DDL Script File Name is set to the name of the file in which generated script is to be written. By default it is set to SQL.DDL. 5. The generated script may now be used to feed other tools or be utilized to create a database schema. ---------------------------- // Create Class, name a _________ | a | |---------| | | --------- If no classes are marked PERSISTANT, (class specification, detail tab, persistence to Persistent (defaults to transient)). a zero byte file will be generated. // Persistence = Persistent CREATE TABLE T_a( cId NUMBER(5), PRIMARY KEY(aId)) ---------------------------- // Create Class, name a, add attribute name_a _________ | a | |---------| | name_a | --------- // Incorrect column length CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)) // Set Attribute name_a property LENGTH to 10: CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)) // Set Attribute name_a property PRIMARYKEY to TRUE: // No system generted attribute and key CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)) ------------------------------------------------------------------ 6.3 >> LANGUAGE SPECIFIC OUTPUT Rational will output DDL in the following languages: Oracle 7 SQLServer 6 ANSI 89 Sybase 10,11 Watcom 4 Examples: --------- | a | |---------| | name_a | --------- name_a: Type: Number PrimaryKey set to False ANSI: CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)) Oracle: CREATE TABLE T_A( name_A VARCHAR(10), PRIMARY KEY(name_A)); SQLServer: CREATE TABLE T_A( name_A VARCHAR(10), PRIMARY KEY(name_A)) go Sybase: CREATE TABLE T_A( name_A VARCHAR(10), PRIMARY KEY(name_A)) go Watcom: CREATE TABLE T_A( name_A VARCHAR(10), PRIMARY KEY(name_A)); !NOTE! The above is a simple example. The language version can have more effect depending upon model. ------------------------------------------------------------------ 6.4 >> DDL PROPERTIES 6.4.1 >>> TYPE: ATTRIBUTE NAME VALUE ----------------------------------- ColumnType VARCHAR Length [BLANK] NullsOK True PrimaryKey False Unique False CompositeUnique False CheckConstraint ColumnType: The attribute type is usually part of the external schema, referenced in the language that is being generated (i.e., C++ ,VB , etc.). This property allows the mapping to a fundamental data type (i.e., CHAR, VARCHAR, etc.). No column is generated for attributes for which the property ColumnType is blank. Length: Provides an optional length for the attribute. Length is used in two forms - character length and Number length. For Strings - the length is the length in characters, i.e. if 40 is input into length the output column would be VARCHAR(40) For Number - the length is either used as precision or precision,scale. Scale is the number of fractional digits and precision is the total number of digits. Examples: length of 15 would be NUMBER(15) length of 8,2 would be NUMBER(8,2) NullsOK: Boolean value to mark attribute as Nullable. If set to True, the attribute must have a value: NAME VARCHAR(40) NOT NULL PrimaryKey: Marks the attribute as the Primary key, or part of a primary key. If no attributes are set, a system generated identifier is added to the table definition. name_A VARCHAR(10), PRIMARY KEY(name_A)) If more than one primary key attributes are identified, a concatenated primary key is generated. name_a1 VARCHAR(10), name_a2 VARCHAR(10), PRIMARY KEY(name_a1,name_a2)); Unique: Marks the attribute as having unique values. If set, uniqueness is forced on the attribute: NAME VARCHAR(40) UNIQUE Composite Unique: Allows one concatenated unique key per table. If attributes in the class are set, a single concatenated key is produced. UNIQUE( NAME,ADDRESS) --------- | a | |---------| | name_a1 | | name_a2 | --------- name_a: PrimaryKey set to TRUE Length set to 10 CompositeUnique set to TRUE CREATE TABLE T_A( name_A1 VARCHAR(10), PRIMARY KEY(name_A1), UNIQUE( name_A1)) Another way of saying same thing: PrimaryKey set to TRUE Length set to 10 Unique set to TRUE CREATE TABLE T_A( name_A1 VARCHAR(10) UNIQUE, PRIMARY KEY(name_A1)) CheckConstraint: Allows one constraint check per attribute. The value should be the constraint. --------- | a | |---------| | name_a1 | --------- name_a: PrimaryKey set to TRUE Length set to 10 CREATE TABLE T_A( name_A1 number(10), PRIMARY KEY(name_A1), CHECK(name_A1 > 100 )) To change defaultS: With nothing hightlighted on the diagram hit F4. Go to DDL tab. For example, ColumnType for default Attribute is VARCHAR. You can change this to anything you like. 6.4.2 >>> TYPE: PROJECT NAME VALUE ----------------------------------- Directory AUTO GENERATE DataBase ANSI PrimaryKeyColumnName Id PrimaryKeyColumnType NUMBER(5) ViewName V_ TableName T_ InheritSuffix _V DropClause False BaseViews False DDLScriptFilename DDL1.SQL Directory Root directory on which to base code generation. If Directory = AUTO GENERATE then the current working directory is used. NOTE: DDL code gen script v1.2 ONLY. For earlier versions of Rose, the location of the pathmap $DESIGN controlls output location. DataBase Sets the default targeted database. Current supplied databases are Oracle, SQLServer, Sybase,Watcom, and ANSI standard DDL. NOTE: Bug in v1.2 Code generation window always pops up as Watcom as Target Database in DDL Setup window. Workaround: Change language in DDL Setup Window. PrimaryKeyColumnKey This is the extension that is concatenated to the class name to create the primary key name if a Primary Key is supplied. NOTE: Use for system generated keys only. Default is Id. --------- | a | |---------| | name_a1 | --------- name_a: PrimaryKey set to FALSE Length set to 10 CREATE TABLE T_A( name_A1 VARCHAR(10), AId NUMBER(5), PRIMARY KEY(AId)) PrimaryKeyColumnType This is the datatype used for generated Primary Keys. default: NUMBER(5) tablenameId NUMBER(5), ViewName Provides an optional naming standard prefix that is concatenated for all generated views. If blank, no prefix is added. default: V_ NOTE: This property is currently not implemented. To generate a view, you use inheritance, at which point the InheritSuffix is used. TableName Provides an optional naming standard prefix that is concatenated for each generated table. If blank, no prefix is added. default: T_ CREATE TABLE T_tablename( InheritSuffix An additional optional view suffix that is added to the join views THAT ARE PRODUCED FOR INHERITANCE mapping. Setting this property to a blank value causes the generator to ignore the addition of an Inherit Suffix. default: _V CREATE VIEW viewname_V( DropClause Boolean value that, if true, creates a DROP TABLE statement before each CREATE TABLE statement. DROP TABLE T_A CREATE TABLE T_A( name_A1 VARCHAR(10) UNIQUE, AId NUMBER(5), PRIMARY KEY(AId)) DDLScriptFileName The default script file name that will be created to contain the DDL script. default: DDL1.SQL NOTE: DDLScriptFilename property only accepts the filename only, NOT the path. For example: DDLScriptFilename set to "D:\data\sql\DDL1.SQL" is INCORRECT. DDL code is generated to the location specified by the Directory property (v1.2) and $DESIGN pathmap in earlier Rose versions. So to get the desired result here, Edit Directory property or Edit:Path Map Change either Directory or $DESIGN to point to disired location: i.e. d:\data\sql Change DDLScriptFilename to .sql DDL code will now generate to d:\data\sql\.sql ------------------------------------------------------------------ 6.5 >> ASSOCIATION Each class is created as a table, with each subclass being a subset of the top table in the hierarchy. This means each subclass table will share the primary key of the parent. _________ __________ | a | | b | |---------|<------|----------| | name_a | | name_b | --------- ---------- // SYSTEM generated primary key CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10), aId NUMBER(5) REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); _________ __________ | a | | b | |---------|<------|----------| | name_a | | name_b | --------- ---------- // USER set primary key on both CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)) CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), FOREIGN KEY (name_a) REFERENCES T_a, PRIMARY KEY(name_b)) _________ _________ _________ | a | | b | | c | |---------|<-----|---------|<----|---------| | name_a | | name_b | | name_c | --------- --------- --------- // SYSTEM generated primary key CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10), aId NUMBER(5) REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); CREATE TABLE T_c( name_c VARCHAR(10), bId NUMBER(5) REFERENCES T_b(bId), cId NUMBER(5), PRIMARY KEY(cId)); ------------------------------------------------------------------ 6.6 >> NAVIGABLE The Navigable field indicates what direction the role is navigating to. By default, roles are bi-directional and no navigation notation is provided. When the navigable field is set, an arrowhead is displayed at the end of the association that the role is navigating to. Multiplicity should be checked on ONE SIDE ONLY to produce single arrow. To set a role's navigation, click on the Navigable box in the association specification or select Navigable through the adornment menu. The navigable arrowhead will point in the direction of the role, unless a containment adornment is displayed. Containment adornments override navigable adornments. If Navigability is set for the direction of the association, a relation is created via a foreign key, between the resultant tables. EXAMPLES: _________ _________ | a | | b | |---------|------|---------| | name_a | | name_b | --------- --------- Role A Detail, Navigable: Not checked Role B Detail, Navigable: Not checked CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10); bId NUMBER(5), PRIMARY KEY(bId)); _________ _________ | a | | b | |---------|<-----|---------| | name_a | | name_b | --------- --------- Role A Detail, Navigable: Not checked Role B Detail, Navigable: Checked CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10); aId NUMBER(5)REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); _________ _________ | a | | b | |---------|------|---------| | name_a | | name_b | --------- --------- Role A Detail, Navigable: Checked Role B Detail, Navigable: Checked *** ERROR invalid model causes invalid code *** No error or waring is given from Rose. *** Relationship causes illegal cycle. a's primary key references *** b and b's references a. *** You cannot have 2 tables referrencing each other *** NOTE: the default when creating a new association is that *** Navigable is checked on both sides! CREATE TABLE T_a( name_a VARCHAR(10), bId NUMBER(5)REFERENCES T_b(bId), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10); aId NUMBER(5)REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); !NOTE! Having navigation check on both sides can also lead to duplicate attritbutes, primary or foreign keys. ------------------------------------------------------------------ 6.7 >> MULTIPLICITY Multiplicity and Role Names DO NOT affect DDL generated code. They are not mapped to DDL code generation. Multiplicity is commonly utilized in other DB notation and mapping tools as the determining factor for the creation of relations between tables. For example: one-to-one, one row in table A is connect to one and only one row in table B. one-to-many one row in table A has many connection to rows in table B. many-to-many one row in table A has many connections to rows in table B and vise versa.). If one-to-many, then it will generate a foreign key embedded in table B. If many to many, it will generate a link table (table C) with primary key of a and primary key of b to act as a lookup table. However the above multiplicity HAS NO EFFECT ON DDL CODE GENERATED with Rose. Rose maps the determination of relationships via navigability. If Navigability is set for the direction of the association, a relation is created via a foreign key, between the resultant tables, regardless of multiplicity. ------------------------------------------------------------------ 6.8 >> KEYS/QUALIFIERS : CREATE INDEX _________ _________ | a | | b | |---------|<--[name_a]-----[name_b]--|---------| | name_a | | name_b | --------- --------- Association specification, Role A Detail, Keys/Qualifiers: name_a Association specification, Role B Detail, Keys/Qualifiers: name_b CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)) CREATE INDEX a_1 ON T_a(name_a) CREATE TABLE T_b( name_b VARCHAR(10), aId NUMBER(5) REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)) CREATE INDEX b_1 ON T_b(name_b) NOTE: index name is generated by Rose, and not changable through user interface. ------------------------------------------------------------------ 6.9 >> FOREIGN KEY COLUMN NAME The default for a foreign key column name is the referenced table name followed by the design property for system defined identifiers. Named relationships will use the relationship name as the foreign key column name in the table. _________ _________ | a | | b | |---------|<-----|---------| | name_a | | name_b | --------- --------- Role A Detail, Navigable: Not checked Role B Detail, Navigable: Checked Using system generated primary keys CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10), aId NUMBER(5) REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); _________ _________ | a | assoc_name | b | |---------|<-------------|---------| | name_a | | name_b | --------- --------- Role A Detail, Navigable: Not checked Role B Detail, Navigable: Checked Using system generated primary keys Association name: assoc_name CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10), assoc_name NUMBER(5) REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); NOTE: Only works if using system generated keys! Role A Detail, Navigable: Not checked Role B Detail, Navigable: Checked name_a, name_b, PrimaryKey property set to True Association name: assoc_name CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)); CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), FOREIGN KEY (name_a) REFERENCES T_a, PRIMARY KEY(name_b)); ------------------------------------------------------------------ 6.10 >> AGGREGATION Aggregation or containment will modify the relationship by adding a DELETE CASCADE constraint to the Foreign key in DATABASES THAT PROVIDE THIS CAPABILITY. If the association is specified by "by-reference", then only the foreign key is referenced. If "by-value" is specified, then an additional constraint for DELETE CASCADE is added. If you use the ON DELETE CASCADE option, the database permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity. _________ _________ | a | | b | |---------|<-------<>|---------| | name_a | | name_b | --------- --------- name_a, name_b, PrimaryKey set to TRUE Aggregation between class a and b Role A Detail, Navigable: Not Checked Role B Detail, Navigable: Checked Aggregation specification, Role B Detail, Containment of b: by value. ORACLE: CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)); CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), FOREIGN KEY (name_a) REFERENCES T_a ON DELETE CASCADE, PRIMARY KEY(name_b)); ANSI: CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)) CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), FOREIGN KEY (name_a) REFERENCES T_a, PRIMARY KEY(name_b)) ------------------------------------------------------------------ 6.11 >> DEPENDENCY, INSTANTIATES, REALIZES _________ __________ | a | | b | |---------|<- - - - |----------| | name_a | | name_b | --------- ---------- Dependency between class a and b // Does nothing. Above produces same code as below. _________ __________ | a | | b | |---------| |----------| | name_a | | name_b | --------- ---------- CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)) CREATE TABLE T_b( name_b VARCHAR(10), bId NUMBER(5), PRIMARY KEY(bId)) ------------------------------------------------------------------ 6.12 >> GENERALIZATION _________ __________ | a | | b | |---------|<|-------|----------| | name_a | | name_b | --------- ---------- Generalization between class a and b // a join view is created for each subclass table. name_a, PrimaryKey set to TRUE Generalization between class a and b Notes: NAME PRIMARY KEY IN PARENT: Any class which is a superclass of another Persistent subclass MUST have a Primary Key explicitly defined by you, not auto generated. You should not name this key the same name that is auto generated by the code generator. DON'T NAME PRIMARY KEY IN CHILD: Only the parent class should have a primary key declared. A child cannot explicitly name a primary key; it inherits the key from the parent. The tables generated for the child classes are automatically given an identical primary key to the parent class, which is also made a foreign key into the parent class's table. CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)); CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), PRIMARY KEY(name_a), FOREIGN KEY (name_a) REFERENCES T_a); CREATE VIEW b_V( name_b, name_a) AS SELECT T_b.name_b, T_aname_a FROM T_b,T_a WHERE T_b.name_a=T_aname_a; ------------------------------------------------------------------ 6.13 >> LINK ATTRIBUTE Link Classes Link tables are tables that make it possible to effectively have a "many-to-many" relationship between tables. The link table acts as a lookup table. they contain the primary key from each table, and typically the primary key in a link table is a compound key comprising the two compound keys. Link classes map to associative entities - a method to resolve many-to-many relations between tables. To create an associative entity in Rose, place a link class between the two classes that have the many-to-many relationship. The name of the class will be the name of the resultant table. This table will have any additional attributes that are defined in the class. It will also have, as its’ primary key, the concatenated keys of the two dependent tables. _________ __________ | a | | b | |---------|---------|----------| | name_a | | | name_b | --------- | ---------- | _________ | c | |---------| | name_c | --------- name_a PrimaryKey set to TRUE name_b PrimaryKey set to TRUE (MUST BE user defined primary key) Assocation between a and b; Navigibility: Role A Detail, Navigable: Not Checked Role B Detail, Navigable: Not Checked link attribute to association. CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)) CREATE TABLE T_b( name_b VARCHAR(10), PRIMARY KEY(name_b)) CREATE TABLE T_c( name_c VARCHAR(10), name_a VARCHAR(10), FOREIGN KEY (name_a) REFERENCES T_a, name_b VARCHAR(10), FOREIGN KEY (name_b) REFERENCES T_b, PRIMARY KEY(name_a,name_b)) ------------------------------------------------------------------ 6.14 >> VERSION\PATCH INFO 4.0: v1.0 and v1.1 98: v1.1 and v1.2 4.0 v1.1 <> 98 v1.1 98i: v2.0 DDL Source code (ddlgen.ebs) ships with 98i. Source code for early versions is available for downloading. DDL Source code and update for 4.0 (v1.1): ftp://ftp.rational.com/public/rose/rose_cpp/patches ddlgen.ebs -> Update and source code for ddl gen script (4.0) DDL Source code and update for Rose 98 (v1.2): http://www.rational.com/products/rose/98downloads.html Rose 98 DDL add-in version 1.2 update ------------------------------------------------ Rose 98 DDL add-in version 1.2 update readme.txt ------------------------------------------------ Rational Rose 98 Release Notes for Rose 98 DDL add-in version 1.2 update 14-August-1998 Contents: readme.txt (this file) ddlgen.ebs 31,061 07-09-98 1:39p ddlgen.reg 94 08-12-98 3:25p This patch updates the DDL add-in from version 1.1 to version 1.2 for the following editions of Rose 98 version 4.5.8163.3 (Windows 95/NT): - Enterprise Edition (Service Release 1) - Modeler Edition (Service Release 1) - Professional Java Edition (Service Release 1) - Professional Visual Basic Edition (Service Release 1) The patch needs not to be installed if you have: - Professional C++ Edition (Service Release 1) - Versions updated to 4.5.8163.3 with Service Pack 1 If you are uncertain whether you need this patch, you can find the actual version of your DDL add-in in Help|About. Defects Fixed: This patch fixes the following defects in Rose 98 DDL add-in 1.1: 1. 124116 - "Not possible to disable DDL generation of attributes." This updated DDLGEN script won't generate a column for attributes for which the property ColumnType is blank. 2. 132129 - "Parenthesis always generated with attributes." With the update when no length is specified, this will generate the attribute and type with no (). Installation: 1. Copy the file ddlgen.ebs to the scripts subdirectory of your Rose home directory. If you have changed the existing ddlgen.ebs, you may wish to backup the changed file first. 2. Tools:Open Script scripts\ddlgen.ebs Debugger:Compile... Go up one level to the Rose home directory Save button Yes button to replace the existing file 3. Double-click on the file ddlgen.reg. This will increase the DDL add-in version number to 1.2 (shown in Help:About or Add-Ins:Add-In Manager). ------------------------------------------------------------------ 6.15 >> DDLGEN ERRORS When trying to generate DDL code the following error is returned: 1. Error 76 on line 265: Path not found -or- DDL Generator Problem initializing Generator 2. Error 53 on line 265: File not found -or- Error 76 on line 265 path not found 3. Unable to open [path]/ddlgen.ebx -or- Unable to open $SCRIPT_PATH/ddlgen.ebx 4. Error 70 on line 266 (Rose on UNIX) -or- Error 53 on line 266 (Rose on UNIX) 5. Error 91 on line 838: Object variable or With block not set. 6. SYSTEM ERROR:line 482, PC 958 : Call has 4 argument(s); 5 are required. -------------------------------------------------------------------- 1. Error 76 on line 265: Path not found -or- DDL Generator Problem initializing Generator #1 These errors indicate that you do not have DDL properties loaded (If you open up a class specification, the far right tab should be DDL). Error 76 on line 265: Path not found Tools:DDL Generation Will open up the DDL Setup window DDL Script File Name: Supply a name to fix. No default name here usually indicates that DDL properties are not loaded. After supplying name and pressing OK button if DDL properties aren't loaded the following error is returned: DDL Generator Problem initializing Generator Reasons you might not have DDL properties loaded: - Model came from an older or different version of Rose that did not have these properties. Properties are save within the model file. - If this happens with a new file then rose.ini file is probably not correctly pointing at the location of the .pty file. Close Rose then open rose.ini OR .rose.ini in an editor and make sure the following correctly points to the location of the pty file. -UNIX- ( .rose.ini located in home directory ) [Rational Rose 4.0] ROSE_CPP_PTY=/export/rational/releases/rose.4010/codegen/rose_cpp.pty -WINDOWS- ( located in \win95 or \winnt directory ) [Rational Rose 4.0] ROSE_CPP_PTY=C:\Program Files\Rational\Rational Rose C++ 4.0\rose_cpp.pty To add these properties to the current model, do the following: Tools:Properties:Update properties... .pty is dependent upon version of Rose being used. For example with Rose\C++ the filename is rose_cpp.pty. -UNIX- file is located in /.../rose.4010/codegen/ directory -Windows- file is located in Rose's home directory Now do a File:Save to store these properties in this model file. #2 Error 76 on line 265: Path not found This problem can be related to a problem with a path map variable in the rose.ini file. To check\correct this, go to \windows (or \winnt) directory and find rose.ini file. Open this file up in an editor and look for the following section: [Virtual Path Map] Underneath this section find the DESIGN path map variable: a) Problem with known defect: DEFECT 126821 DDL: Path map comment triggers - Error 76 on line 265: Path not found DESIGN=C:\program files\rational\rational rose c++ 4.0}# specifies... The }# is a comment marker with the text following being the comment. Remove this entire section, so that DESIGN looks like the following: DESIGN=C:\program files\rational\rational rose c++ 4.0 b) If the path for DESIGN does not point to a valid location this error will occur. To correct, edit this line to point to a valid location. -------------------------------------------------------------------- 2. Error 53 on line 265: File not found -or- Error 76 on line 265 path not found The usual cause of these errors are that a path and filename has been given where only a filename is expected. This can happen by incorrectly setting the DDLScriptFilename to a path or by typing in a path as well as a file name in DDL Setup dialog window, "DDL Script File Name:" DDLScriptFilename: This property only accepts the filename NOT the path. For example: DDLScriptFilename is set to "D:\data\sql\DDL1.SQL" DDL code is generated to the location specified by the $DESIGN pathmap variable. So to get the desired result here, Edit:Path Map change $DESIGN to point to d:\data\sql change DDLScriptFilename to ddl1.sql DDL code will now generate to d:\data\sql\ddl1.sql Setup dialog window is "DDL Script File Name": As with DDLScriptFilename, This is for file NAME only NOT path. Path for DDL generation is controled by $DESIGN path map. So to change from default, rose home directory, go to Edit:PathMap and set $DESIGN to desired output directory. ex. d:\temp Then change DDL Script File Name to whatever... ex. test.sql Output will now go to d:\temp\test.sql -------------------------------------------------------------------- 3. Unable to open [path]/ddlgen.ebx -or- Unable to open $SCRIPT_PATH/ddlgen.ebx This message indicate that Rose cannot find the ddlgen.ebx file which is the DDL generation script. Reason why Rose might not be able to find this file: $SCRIPT_PATH is not set to point to the ddl script, ddlgen.ebx. To correct this, set this path map variable to point to the script file. For example: File:Edit Path Map... Symbol:$SCRIPT_PATH (All upper case! Path maps are case sensitive) Actual Path: (Browse and select the script directory) Actual Path: .../rose.4010/scripts (Where ... is the complete path. With the Unix platform scripts are located in /scripts, located underneth the home directory) Actual Path: ...\Rational Rose C++ 4.0 (Where ... is the complete path. With Windows platform scripts are located in home directory) OK Also make sure the file ddlgen.ebx exist in this location (default location). You should now be able to generate DDL. -------------------------------------------------------------------- 4. Error 70 on line 266 (Rose on UNIX) -or- Error 53 on line 266 (Rose on UNIX) This error indicates you are trying to write the DDL file to a location where the file cannot be created (rights or bad path for example). To correct Start rose from your home directory -or- Include a valid path to a directory where you have rights to create the file in front of the filename. -------------------------------------------------------------------- 5. Error 91 on line 838: Object variable or With block not set. Error 91 on line ... Object variable or With block variable not set Error 91 refers to an attempt to call a method for an object that "is nothing", that is, the equivalent to a null pointer in C. This error can arise in a number of different locations when calling a Rose script. 1) Can you create a new test model, one class, mark it as persistent, and then do DDL generation on this class? If no, same or different error message? 2) If the test model generates, then we can assume it not a configuration issue. This error can occur with DDL generation if a class (that was selected) had a unresolved reference to another class or relation not loaded (or no longer exists in model). To correct, Run Check Model, and clean up any unresolve references to any class that you are attempting to generate from. If this doesn't help, try selectively hightlighting classes and generating to narrow it down to the problem class(es). Then check class specification for relationships that don't appear on the diagram and remove them. For more information on fixing unresolved references see the following Technical Note at, http://www.rational.com/products/rose/support/tan/index.jtmpl It lists the most common problems and cures for these messages. TITLE: Causes and cures for Unresolved References -------------------------------------------------------------------- 6. SYSTEM ERROR:line 482, PC 958 : Call has 4 argument(s); 5 are required. You cannot use a HAS relation between classes for DDL generation. You must use a AGGREGATE Association. If you use a HAS relation you will get System Error... 5 are required. Note that the example in \rose_vb\samples\ordersys\ordrsyso.mdl is misleading ( a request to change this has already been made ). Class category DS DATABASE use a has relation. The only reason this doesn't generate a error when trying to generate DDL code, is that one is a class utility, which can't be marked as persistant, which means it never genertes code directly. ------------------------------------------------------------------ 6.16 >> QUESTIONS AND ANSWERS Q1. How come I get a zero byte file? Q2. How do I control the order of table creation? Q3. How do I control the ordering of attributes? Q4. How can I surpress generation for certain attributes? Q5. Attributes with () - no value in the parenthesis. Q6. Problem with order of generated code or duplicate attribute, Q7. How do I change the default value (VARCHAR) for attribute type? Q8. What support does Rose's DDL generation have for constraint? Q9. Linkage between type displayed on diagram and property. Q10. Cardinality isn't working. Q11. The DDL code is generated with a dangling WHERE. Q12. Why doesn't parent primary key appear in child? Q13. Duplicate names in link attribute link class. Q14. Relationship causes illegal cycle importing code into ERwin. Q15. How to create a self referencing index? Q16. Problems with classes having more than 1 relationship. Q17. When I generate code, the word CASCADE is mispelled 'CASCASE'. Q18. How to control code generation output location? Q19. Can I output to another language then is currently listed? Q20. Can you change the case of the output? Q21. I don't see the DDL property tab. Q22. How do I create a Foreign Key? Q23. Foreign key has relation name instead of the attribute. Q24. Override control of primary key inheritance at lower level. Q25. System generated key concatenated with attribute. Q26. How can I create a compound primary key in Rose? Q27. How can I handle the composite unique concept in Rose? Q28. How do I get Rose to automatically create primary keys? Q29. How to generate attributes in the form tablename.attributename Q30. How do I get it to choose Oracle as the default DDL language ? Q31. Can I change the name of the index? Q32. Can I make the attribute as autoincremental type or other type? Q33. How to have a Primary Key based on attribute and FK attribute? -------------------------------------------------------------------- Q1. How come I get a zero byte file? I go to generate DDL code, everything appears to work, no error messages are returned, yet I wind up with a zero byte file. A> Set class to persistent. [6.2] Q2. How do I control the order of table creation? When creating the DDL script from Rose, "Create Table" statements are written to the script file in an order that conflicts with the foreign and primary key dependancies. As a result, I receive errors for declaring references to tables which have not been created yet in the DDL sequence. Example: CREATE TABLE T_a( bId NUMBER(5) REFERENCES T_b(bId), aId NUMBER(5), PRIMARY KEY(aId)); ERROR at line 2: ORA-00942: table or view does not exist CREATE TABLE T_b( bId NUMBER(5), PRIMARY KEY(bId)); Table created. Should be: CREATE TABLE T_b( bId NUMBER(5), PRIMARY KEY(bId)); Table created. CREATE TABLE T_a( bId NUMBER(5) REFERENCES T_b(bId), aId NUMBER(5), PRIMARY KEY(aId)); Table created. A> DEFECT 123366 - Error in DDL generation - order of declaration The order of the "Create Table" stmnts depends on the order how you select the classes. If you select class A before B the create table statement for class A will precede the one for class B. (Hold down the CTRL key while clicking on the classes will allow you to select multiple classes and added them to the list in the order you want them generated). WORKAROUND: If you run into problems with order don't select "select all" since the result is unknown. Select the classes in the order you want the statements generated. As the classes are selected, they are stored in a class collection. Roses' ddlgen script then generates the SQL code in this order. A second workaround is to run your sql code twice. The first time thorough the 'base' tables will get created and then the second pass will find these referenced tables. Q3. How do I control the ordering of attributes? A> Go to Class Specification, attribute tab (In Rose\VB attributes are called properties). Click on Signature or Name bar to toggle between aphabetical and order of creation. You can also highlight and drag attributs (or properties) to what ever order you like. The order here will determine the order of generation in the DDL code generated. Q4. How can I surpress generation for certain attributes? A> Set attributes columntype property to blank. Note this works with v1.2, v2.0 but does not work with all older ddl vers. [6.4.1, 6.14] Q5. Attributes with () - no value in the parenthesis. When I generate DDL code for an attibute, incorrect code is generated. I get () with no value in the parathesis. name_a VARCHAR(), There should be something between the () or no () at all. A> Defect in Rose get v1.2 update. [6.14] Q6. Problem with order of generated code or duplicate attribute, key definitions. A> Check that navigable is not check on both side of relation. [6.6] Q7. How do I change the default value (VARCHAR) for attribute type? A> To change this default: With nothing hightlighted on the diagram hit F4. Go to DDL tab. ColumnType for default Attribute is VARCHAR. You can change this to anything you like. This will be the new default for all NEW attributes created from this point on. To set this on individual attributes: Go to Class Attribute Specification for . DDL tab, Change ColumnType. [6.4.1] Q8. What support does Rose's DDL generation have for constraint? A> Rose DDL generation provides support for data integrity constraints including: null, not null, unique, primary key, foreign key, check, references, delete cascade, secondary indices, composite uniqueness, and composite indices, delete cascade. [6.0] These features provide the information for the creation of relationships between tables via referential integrity constraints and the addition of indexes to the tables: Association Name Navigability and Multiplicity Aggregation Keys Link Classes Q9. Linkage between type displayed on diagram and property. There's no linkage between Type displayed on diagram (class specification) and DDL property columntype. A> Yes this is correct. DEFECT 141429 - Why does TYPE field not reflect columntype property or change with it? The only way to update Type or columntype, is to directly change one or the other. There is no linkage between them. The only other possible option is to write a script to do this. Q10. Cardinality isn't working. A> Rose uses Navibable. [6.6, 6.7] Q11. The DDL code is generated with a dangling WHERE. i.e. a WHERE with nothing following. How come? example: CREATE TABLE T_b( name_b VARCHAR(10)) CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)) CREATE VIEW b_V( name_b, name_a) AS SELECT T_b.name_b, T_aname_a FROM T_b,T_a WHERE A> You MUST define a primary key If you use inheritance, than you MUST name a primary key on the top parent class, you cannot use a system assigned one. This is a limitation. [6.12] DEFECT 127083 - Rose should return error if no primary key is choosen In the above example if you make name_a the primary key (by going to Class Attribute Specification for name_a, DDL tab, and set PrimaryKey to True), the following code is generated: CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), PRIMARY KEY(name_a), FOREIGN KEY (name_a) REFERENCES T_a ) CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)) CREATE VIEW b_V( name_b, name_a) AS SELECT T_b.name_b, T_aname_a FROM T_b,T_a WHERE T_b.name_a=T_aname_a Q12. Why doesn't parent primary key appear in child? I created two class: A, B each with an attribute: name_a, name_b, with a genaralization between. Why doesn't aID (the primary key) appear in B? A> Set primary key. You MUST define a key for use of inheritance. See example in Q11. Q13. Duplicate names in link attribute link class. A> Multiplicity. [6.13, 6.6] Q14. Relationship causes illegal cycle importing code into ERwin. When trying to use the DDL code generated from Rose, it fails with errors about illegal relation code when trying to import this code into Logic Works. ERwin returns: Relationship causes illegal cycle. Parent: T_tablename Child: T_tablename A> The most likely source of this error, is you have navigble checked on both sides of a relationship. To correct have Navigable selected on only one side or no sides. [6.6] Q15. How to create a self referencing index? Can I use the Rose/DDL to add an index to a table without having to create a reference table. I just want table "a" with an index on key_qual_a, without creating table "b". A> keys on supplier class on association creates an index. [6.8] --------- | a | |---------|-[name_a]---- | name_a | | --------- | | | ------------------- association to self Navigable NOT check on either Role A Detail or Role B Detail attribute name_a: PrimaryKey set to FALSE Role A detail tab, key\qualifier set to "name_a" The following code is generated: CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE INDEX a_1 ON T_a(name_a); Q16. Problems with classes having more than 1 relationship. I'm having problems generating DDL with classes that have more than 1 relationship to each other. I'm getting duplicate foreign keys. A> DDL modeling in Rose currently does not support multiple _unnamed_ associations beween classes. This is a limitation due to the way Rose currently handles named and unnamed associations. [6.9] If the association is named, then the association between classes on all diagrams with the same association name are in fact the same association. Conversely In the case where the association does not have a name, they are also treated as if they are the same association and with DDL generation this causes the same line to be generated twice. _________ _________ | a | | b | |---------|<-----|---------| | name_a |<-----| name_b | --------- --------- CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10), aId NUMBER(5)REFERENCES T_a(aId), aId NUMBER(5)REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); If you named the associations then they are treated as two different associations and the duplicate code will not be generated: _________ _________ | a | aa | b | |---------|<-----|---------| | name_a |<-----| name_b | --------- bb --------- CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10), bb NUMBER(5)REFERENCES T_a(aId), aa NUMBER(5)REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); DEFECT 129492 - DDL: Duplicate code with classes that have more than 1 unnamed relationship Q17. When I generate code, the word CASCADE is mispelled 'CASCASE'. With Oracle and the drop tables option enabled, the following incorrect syntax is generated: DROP TABLE table_name CASCASE CONSTRAINT; A> Defect in older versions of DDL script. Correct in v1.2 and later. [6.14] Q18. How to control code generation output location? A> $DESIGN or Directory property. [6.4.2] Q19. Can I output to another language then is currently listed? A> No not directly within Rose. Target Database supported are only those listed; ANSI, Oracle, SQLServer, Sybase and Watcom. If you want to output to another language your options are, 1) Use output to ANSI [6.3] 2) Modify existing ddlgen.ebs script [6.14] 3) Export model to ERwin, generate from ERwin [6.1.2] Q20. Can you change the case of the output? A> No not directly within Rose. Only by modifying source. Q21. I don't see the DDL property tab. A> This indicates DDL properties are not loaded. 1) Problem only happens with old file, new file DDL is there: Model came from an older or different version of Rose that did not have these properties. Properties are save within the model file. To add these properties to the current model, do the following: Tools:Properties:Update properties... .pty is dependent upon version of Rose being used. For example; rose_cpp.pty, rosejava.pty, rose_vb.pty, etc. Now do a File:Save to store these properties in this model file. 2) Problem happens with new file (Rose 4.0) If this happens with a new file then rose.ini file is probably not correctly pointing at the location of the .pty file. Close Rose then open rose.ini OR .rose.ini in an editor and make sure that pathing information under the [Rational Rose 4.0] section is correct. i.e. points to the location of the .mnu and .pty files. -UNIX- ( .rose.ini located in home directory ) [Rational Rose 4.0] ROSE_CPP_PTY=/export/rational/releases/rose.4010/codegen/rose_cpp.pty -WINDOWS- ( located in \win95 or \winnt directory ) [Rational Rose 4.0] ROSE_CPP_PTY=C:\Program Files\Rational\Rational Rose C++ 4.0\rose_cpp.pty 3) DDL Add-In not checked. (Rose 98/98i) Go to Add-Ins:Add-In Manger and make sure DDL option is checked. Q22. How do I create a Foreign Key? A> Rose uses navigability to determine relationships between tables. If navibability is set for the direction of the association, a relation is created via a Foreign key (regardless of multiplicity). The default for a foreign key column name is the referenced table name followed by the design property for system defined identifiers. Named relationships will use the relationship name as the foreign key column name in the table. [6.10, 6.5] An example to create FOREIGN KEY: Two classes; a, b with Association between In Class Specification, detail tab, set Persistence to Persistent Create an attribute in each class; name_a, name_b Go to name_a specifications, DDL properties and set PrimaryKey to TRUE _________ _________ | a | | b | |---------|<---------|---------| | name_a | | name_b | --------- --------- Association between a and b Role A Detail, Navigable: Not checked Role B Detail, Navigable: Checked CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)); CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), FOREIGN KEY (name_a) REFERENCES T_a, bId NUMBER(5), PRIMARY KEY(bId)); Aggregation or containment will modify the relationship by adding a DELETE CASCADE constraint to the Foreign key in databases that provide this capability. _________ _________ | a | | b | |---------|<-------<>|---------| | name_a | | name_b | --------- --------- Aggregation between class a and b Role A Detail, Navigable: Not checked Role B Detail, Navigable: Checked CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)); CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), FOREIGN KEY (name_a) REFERENCES T_a ON DELETE CASCADE, bId NUMBER(5), PRIMARY KEY(bId)); Q23. Foreign key has relation name instead of the attribute. The Foreign key is genrated wrong. Shows up with relation name instead of the attribute name. A> This is documented behavior. The default for a foreign key column name is the referenced table name followed by the design property for system defined identifiers. Named relationships will use the relationship name as the foreign key column name in the table. [6.9] _________ _________ | a | | b | |---------|<-----|---------| | name_a | | name_b | --------- --------- Role A Detail, Navigable: Not checked Role B Detail, Navigable: Checked CREATE TABLE T_b( name_b VARCHAR(10), aId NUMBER(5)REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); _________ _________ | a | assoc_name | b | |---------|<-------------|---------| | name_a | | name_b | --------- --------- Role A Detail, Navigable: Not checked Role B Detail, Navigable: Checked CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE TABLE T_b( name_b VARCHAR(10), assoc_name NUMBER(5)REFERENCES T_a(aId), bId NUMBER(5), PRIMARY KEY(bId)); Q24. Override control of primary key inheritance at lower level. When generating DDL, I want to be able to override control of primary key inheritance at lower level of inheritance chain in the model. Is this possible? A> No, you cannot override the inherited key. Q25. System generated key concatenated with attribute. Can you create a primary key with the generated id and one of your own attributes? A> No, currently this is not possible. Q26. How can I create a compound primary key in Rose? A> Rose can handle compound primary keys if they are part of the attributes of the table. If the PrimaryKey property is set on more than one attribute, than the primary key is concatenated. [6.4.1] --------- | a | |---------| | name_a1 | | name_a2 | --------- name_a1, name_a2 PrimaryKey properties set to TRUE CREATE TABLE T_a( name_a1 VARCHAR(10), name_a2 VARCHAR(10), PRIMARY KEY(name_a1,name_a2)); Unfortunely, the use of foreign keys as concatentated primary keys is not supported in Rose. Q27. How can I handle the composite unique concept in Rose? A> There is a DDL property called CompositeUnique. Identifies if attribute is part of a composite. One may set one set of attributes (only one is the limitation) to the composite unique property and a composite unqiue constraint is created for the concatentated set of attributes. [6.4.1] --------- | a | |---------| | name_a1 | | name_a2 | --------- name_a1, name_a2 PrimaryKey properties set to TRUE CompositeUnique properties set to TRUE CREATE TABLE T_a( name_a1 VARCHAR(10), name_a2 VARCHAR(10), PRIMARY KEY(name_a1,name_a2), UNIQUE( name_a1,name_a2)); Q28. How do I get Rose to automatically create primary keys? A> If no primary key is identified, then a system id will automatically be created for that object. [6.2, 6.4.1] Q29. How to generate attributes in the form tablename.attributename A> Rose does not directly support adding the table name to the attribute, i.e. tablename.attribute However you can get this output by naming the attribute along with the tablename. For example: ------------- | a | |-------------| | T_a.name_a1 | | T_a.name_a2 | ------------- T_a.name_a1 PrimaryKey property set to TRUE CREATE TABLE T_a( T_a.name_a1 VARCHAR(10), T_a.name_a2 VARCHAR(10), PRIMARY KEY(T_a.name_a1)); Q30. How do I get it to choose Oracle as the default DDL language ? I tried setting the default project DDL DataBase to Oracle but it still comes up with Watcomm. A> This is a defect, the default target database should correspond to "database" property in project properties. However DDL:Generate Code, "DDL Setup" window, Target Database, always defauts to Watcom. Workaround: Change language in DDL Setup Window. [6.4.2] Q31. Can I change the name of the index? For example, "a_1" in the following example, CREATE TABLE T_a( name_a VARCHAR(10), aId NUMBER(5), PRIMARY KEY(aId)); CREATE INDEX a_1 ON T_a(name_a); A> Currently not supported. Only current opion to change name in ddl output after generation or modify the source code (script .ebs file). If you wish to try going this route, source code (ddlgen.ebs) ships with 98i and is available for downloading with previous version. [6.8, 6.14] Q32. Can I make the attribute as autoincremental type or other type? I would like to create an autoincremental type attribute (SQL Server). Is this possible? A> Yes you should be able to specify any TYPE for the attribute that is reconized by your server. If you put in "FRED" as the DDL property type, then "FRED" will get generated in the code. As I understand the IDENTITY syntax for SQL Server is: CREATE TABLE TABLE1( ID IDENTITY(m,n), FIELD1 ..., FIELDn ..., PRIMARY KEY ID) Where m - the start number of autoincremental column, by default 1 n - the increment, by default 1 So for example I created NewClass with an attribute called name_a. Then went into name_a attribute specification, DDL tab and set Type to IDENTINTY and Length to (1,5) and PrimaryKey property to true. The following code is then generated: CREATE TABLE T_NewClass2( name_a IDENTITY(1,5), PRIMARY KEY(name_a)) go Q33. How to have a Primary Key based on attribute and FK attribute? How can I make Unique Keys based on more than one columns with one of the columns being the Foreign Key coming from some other class linked to the class in question. I can make a Unique Key by choosing columns in the class but the Foreign Key column will be available only when the mapping between classes is done. <<< A> Add an attribute to the second class with the same name as the primary key attribute in the first class. Go to DDL property tab for this attribute and set the ColumnType property to blank, (this will cause it NOT to be generated) and set PrimaryKey property to True. For example: _________ __________ | a | | b | |---------|<------|----------| | name_a | | name_b | --------- | name_a | ---------- Class: a attribute: name_a PrimaryKey TRUE ColumnType VARCHAR Class: b attribute: name_b PrimaryKey TRUE ColumnType VARCHAR name_a PrimaryKey TRUE ColumnType Association between a and b CREATE TABLE T_a( name_a VARCHAR(10), PRIMARY KEY(name_a)); CREATE TABLE T_b( name_b VARCHAR(10), name_a VARCHAR(10), FOREIGN KEY (name_a) REFERENCES T_a, PRIMARY KEY(name_b,name_a)); ================================================================== ==================================================================