The Enterprise Architect product from Sparx Systems is emerging as the preferred UML modeling tool at many OpenEdge sites. In order to make it easy for people to get started using this powerful tool, information will be collected here to assist people in this process.
Associated with the presentation in this library entry were some demonstration files including some MDG technology to allow reading and writing .df files from within Enterprise Architect. These have been gathered here to make it easier for people to find this regularly requested technology.
The downloads include:
1. DB Config - Sample OE EAP projects.
2. DB Install - The MDG technology.
3. Demo 1.
4. Demo 2.
5. Demo 3.
6. Demo 4.
7. Demo 5.
The attachment below is the manual from DB Config
As of version 7.1, Enterprise Architect does not come supplied with datatype definitions for OpenEdge. There is a Utility provided by PSC under PSDN Community which includes these datatypes along with other functionality, but there are some issues with that implementation. (Also see this) That Utility does not currently provide the option for loading the datatypes separately from the stereotypes that are also in that Utility and these stereotypes are at variance with the proposed UML Profile for ABL.
Therefore, an XML file is provided here for independently loading datatypes appropriate for use with the proposed Profile. These datatypes should be suitable for any use of EA with OpenEdge. They are provided as open source and so may be customized to taste by any user. We will note differences between the implementation in the Utility and the present implementation as appropriate. All reference to "Utility" refers to the one provided at the links on PSDN Code Share above (also available here).
There are a number of differences between the implementation provided by the Utility and the implementation here. These include:
In the EA schema, which is not yet documented, there are 18 columns in the t_datatype table which is used to store the datatypes. My assumptions about the meaning of these columns in building this dataset are as follows:
Type Code or DDL to indicate usage domain.
ProductName Indicates a set, "OpenEdge" is used here for DDL datatypes and "OpenEdge ABL" for Code datatypes.
Datatype The name of the datatype.
Size Purpose unclear. Left as 0.
MaxLen Maximum length. Only defined for character, CLOB, and decimal.
MaxPrec Maximum precision? Only defined for decimal.
MaxScale Maximum scale? Does not apply to OpenEdge. See note below.
DefaultLen Default length. Only defined when MaxLen is defined.
DefaultPrec Default precision. Only defined for decimal.
DefaultScale Default scale. Does not apply to OpenEdge. See note below.
User 0 for defined by Sparx. 1 for defined by other.
Pdata1 Not used.
Pdata2 Not used.
Pdata3 Not used.
Pdata4 Not used. See note below.
HasLength Has length characteristics. Not used in Sparx samples, so not used here.
Generictype See note below.
DatatypeID A number used to reference this datatype.
In the Utility implementation, Pdata4 is given the value "TechID=OpenEdge;". This assignment is apparently an artifact of the technology used in the Utility. No Sparx standard datatypes have any value in this column. This assignment has been dropped in the implementation here.
GenericType suggests a mapping onto a standardized, generic domain such as might be used in a Computationally Independent Model or Platform Independent Model prior to determining a target technology. However, its use in the Sparx samples is more often to duplicate the Datatype name. In the current work, we have taken the route of using the SQL equivalent for the datatype when possible and of using generic forms such as "handle" for those that have no SQL equivalent. See Table 2-10 of the Database Administration manual for mappings.
MaxScale is defined for all datatypes with a value of zero in the implementation in the Utility, although this also seems to be an artifact of the methods used, not something done intentionally. In the datatypes supplied by Sparx, no MaxScale is provided for the Code datatypes for VisualBasic, C++, Java, or C#, so it is provisionally omitted here. DefaultScale does not apply to OpenEdge, although it is defined for most datatypes supplied by Sparx. It is provisionally omitted here until it is clear that it has some impact. With both values omitted, MaxScale has a value of 0 anyway and DefaultScale has no value.
To install, open a project in EA, select Tools from the menu, select Import Reference Data, browse for the downloaded file, select, highlight the dataset for "Model Data Types for OpenEdge - Code and DDL" and press button to complete. To avoid having to do this for every project, do the addition in EABase or other suitable base project from which new projects will be derived.
13 December 2007 - Original - untested
14 December 2007 - Revised, change Code to OpenEdge ABL - limited testing
15 December 2007 - Revised, drop MaxScale, DefaultScale, and PData4 - no new testing.
For services related to this material, see my website.
The standard repository for Enterprise Architect uses the JET database engine. This makes it accessible with Access and SQL, but those working with ABL may prefer to use an OpenEdge database and ABL, being more familiar and more powerful. An OpenEdge database is required for some of the ABL to UML tools available here.
Prerequisites
The following discussion assumes the use of EA 7.0. Whether it works with anything earlier is not known to me, but the tools developed and published here will generally be based on the latest version of both OpenEdge and Enterprise Architect. Even if the target system is an earlier version, these tools will generally not be back-ported. Note that you must have the Corporate Edition of EA to use an alternate database for a repository.
Before EA can be configured to use OpenEdge as a Repository the following Software Components need to be installed and configured:
* MDAC 2.6 or 2.7
* Install Progress OpenEdge 10.0B03.
EA has also been tested by me personally against OpenEdge 10.1B02 and PSC indicates it works with 10.1B01, but not 10.1B unpatched. If you are using 10.1B, you must have installed Service Pack 1 at a minimum. See Solution P120293 in the KnowledgeBase. There has been some prior testing against version 10.0B01, but there are known to be problems with all 10.1A versions.
Once these preconditions have been met the following process should be used to create the EA OpenEdge repository:
Create an Empty Repository Database
Using appropriate OpenEdge tools, create an empty database.
Start a server for this database with an appropriate -S parameter so that you can connect to it with SQL and make an appropriate entry in /etc/services and/or C:\Windows\system32\drivers\etc\services.
Use the Data Administration Tool to create users for use by EA. I typically setup users for sysprogress (the SQL administrator, the user who created the database, and a generic user for use by the appropriate tool, e.g., EA in this case. All should get passwords as many ODBC functions seem to insist on them.
Use sqlexp to assign the needed permissions. See the discussion in the subpage below about setting up the database for use by SQL, unless you are already familiar with this process.
Do one of the following two steps to load the repository schema:
SQL Schema Load
From a proenv equivalent, run the OpenEdge Basemodel.sql (54 Kb) script file to create all of the required tables/indexes etc. Remove the "_.txt" extensions before use. To run the script, use a command like the following:
sqlexp -db EAEmpty -S EAEmpty -user xxx -password yyy -infile OpenEdge_BaseModel.sql
Note that you will see a lot of errors from the DROP TABLE commands at the beginning where no such table exists.
.df Schema Load
The equivalent of the SQL schema load is also provided as an attached .df file which can be loaded using standard database administration tools. Note that this approach is not supported by Sparx, but has worked for some people who have encountered difficulties with the SQL scripts. Sparx may create more current scripts. To use this script, you need to include the FullKeywordForget.txt file to the -k parameter for the session. The smaller KeywordForget.txt file is required for writing code against some parts of the schema.
One may want to back up this database before proceeding, but note that it is not yet provided with all of the data you will want in order for it to serve as the base for all future projects.
Prepare Database for Use
Start a server for the database, if you don't already have one running. In the server start up parameters for the repository, include a parameter for -SQLStmtCache. I am currently using 10000. That might be more than is needed, but 1000 was definitely not enough.
Create a ODBC entry for the database using the instructions in the subpage below.
Create an Empty EA Project
You will now have an empty database, you can use the data transfer tool to transfer an existing model to the server, to achieve this use the following steps:
Please note: it is recommended that you run the Data Integrity tool on your basic project you wish to transfer to OpenEdge. This will ensure data is 'clean' before uploading. To ensure the integrity of a .EAP file open the .EAP file in EA and select Tools | Data Management | Data Integrity.
Then
Note: when entering the Extended Properties DefaultSchema=PUB, don't go back and test the connection - you will loose this setting (a Microsoft quirk!).
Opening the New Project in EA
This process is covered in the EA help file under the topic "Connect to a Progress OpenEdge Repository" and is detailed in the subpage below.
Saving a Base Project
Rather than go through this whole process repeatedly, it is useful to back up a copy at one or more suitable points. Depending on the work you are doing, you will probably want some basic things in every project such as the OE datatypes, possibly the stereotypes for the Profile from this site, etc. There is no particular magic about this data ... it is simply data in tables so once you figure out what you need, get it added to an empty project and then back up that database. The transfer process described here is only required for converting from one database form to another. To create new projects from a base, you simply need to make a copy of the database and go through the connection dialog in the subpage to connect to it for the first time.
Transferring back to an EAP file
In exchanging models with others, it may be desirable at some point to transfer your OpenEdge repository back to an EAP file, e.g., for use by someone off site or for viewing with the free EA viewer. This transfer is easily accomplished by following the same instructions for the transfer, except reversing the source and target databases (probably selecting a new target name, since you don't want to overwrite your EAP base.
Notes
Sparx says "If you wish to enable user security, when prompted enter {F08113BA-8B4F-41df-8F01-46DF2C35D249} as the key to enable security." I haven't found that prompt yet.
EA help files refer to this process as "upsizing" a model. It always consists of *transferring* an existing model from the built-in MS JET database to the OpenEdge database. So, even if you are starting a new project, you need to create an empty project with the desired components and transfer that to the OpenEdge repository. If one was going to do this repeatedly, it would seem sensible to create the empty JET project once, transfer it to OpenEdge once, and then same a copy of that database for starting future new projects. Similarly, if one had some standard model elements which were generally required, e.g., those for the ABL to UML tools found on this site, then it is desirable to either have those in the project one transfers from or to add them to the model before saving the repository to use as a base for future projects.
Material adapted from OpenEdge Scripts on the Registered Users portion of the Sparx website. To get to that page, use the Registered Users link at the top of any Sparx website page, log in, and select OpenEdge Scripts under the Corporate Resources on the box in the left column.
Also note that PSC has published a very detailed howto guide covering this material: OpenEdge RDBMS as Model Repository. The material in this document does not differ materially from what is presented here, but is very detailed step by step if you are not familiar with these operations.
The "_modified" script below is a new one provided by Sparx Systems. It changes the date fields to datetime for better accuracy and changes the CHAR(1) to CHARACTER so that they don't map to the FIXCHAR datatype in OE, which can't be accessed from ABL. No BLOBs though. Apparently, that doesn't work. The TMH script is one with some further enhancements of my own. This will be developed further and sent back to Sparx.
Work is underway to explore changing the default field format for strings to something longer than "X(8)" and the possibility of moving away from all caps table and field names.
For services related to this material, see my website.
In order to use an OpenEdge data repository in EA follow the steps below:
1. In the Open Project dialog, select the Connect to Server checkbox. This is in the upper right corner.
2. Click on the [...] (Browse) button, as you normally would to browse for a project. As you have selected Connect to Server, the Data Link Properties dialog displays instead of the Browse Directories dialog.
3. Select Microsoft OLE DB Provider for ODBC Drivers from the list.
4. Click on the Next button. The Connection tab displays.
5. In the Use data source name field, click on the drop-down arrow and select the ODBC driver you have set up to connect to your OpenEdge repository.
6. Enter the User name and Password.
7. Select the initial catalog.
8. Click on the Test Connection button to confirm that the details are correct.
9. If the test succeeds, click on the OK button. If the test does not succeed, revise your settings. Note, do *not* do this test *after* setting the Extended Properties in step 13.
10. Click on the Advanced Tab.
11. Specify ReadWrite under Access Permissions.
12. Click on the All tab.
13. In the Property Value field, edit the Extended Properties value to: DefaultSchema=PUB.
14. After you have clicked on the OK button, the Logon to Progress dialog displays.
15. Give the connection a suitable name so you can recognize it in the Recently panel on the openStart Page dialog.
16. Click on the OK button to complete the configuration.
This will return you to the Open Project dialog where you can click on Open and you will now be using the new repository.
The following assumes that Enterprise Architect is being used on a Windows platform.
1. Select the Windows Control Panel | Administrative Tools | Data Sources (ODBC) option. The ODBC Data Sources Administrator window displays. Select the System DSN tab.
2. Click on the Add button. The Create New Data Source dialog displays, enabling you to add a new DSN.
3. Select the DataDirect/OpenEdge SQL or other appropriate Progress driver from the list. The name varies by version.
4. Click on the Finish button. The DSN Configuration dialog displays.
5. Enter the following configuration details:
· The Data Source Name
· The Description (optional)
· The Host Name and Port Number of the DBMS server
· The Database Name on the selected server
· The User ID.
Note, you must use the service/port number and not the name.
6. Click on the Test Connect button to confirm that the details are correct.
7. If the test succeeds, click on the OK button to complete the configuration.
8. If the test does not succeed, review your settings.
9. Click OK to exit until no longer in the tool.
Note that there is further detail on this process in the Help for EA under the topic "Set up a Progress OpenEdge ODBC Driver".
Performance of this connection with large models is problematic at the moment and is being researched. Tentative partial recommendations include running update statistics on the database and setting the Fetch Array Size in the ODBC connection to a much larger value. I am currently using 5000. I am setting the default isolation level to blank since setting to read uncommitted seems to produce errors.
Different people have different ideas of how they want to set up databases for SQL access, but in case this is an area unfamiliar to someone trying to setup an OpenEdge database for use with Enterprise Architect, the following describes one approach.
Note that I always set up my databases on a Unix box where I have my own database management scripts, even though the tools I will be using it with are on a Windows platform, so adaptation of the following to Windows is left as an exercise for the reader.
When one first creates the database, it will have no entries in the _User table. Some people like it that way because they manage security in other ways, but to me it seems like it presents a lot of problems, particularly with SQL access, because there are many cases where a tool *really* wants a user id and password. So, my normal practice is to use the Database Administration tools to define three users, one for the database creator, typically root for me, one for sysprogress, which is the default SQL administrator, and one for a generic SQL user, typically named for the tool which will be using this connection, e.g., something like EA in this case. All of these get login names, appropriate descriptions, and passwords.
To use the database with SQL, you will need to have a server running with a -S parameter with a service name and you will have to define the service name in the /etc/services or /Windows/system32/drivers/etc/services files as needed.
Then, with a server running using
$DLC/bin/sqlexp $DBNAME -S $SERVICE -user sysprogress -password xxxxxxxx
I will get a SQL session and run two commands:
grant DBA to EA; grant RESOURCE to EA;
Follow this with:
commit;
to commit the work. To confirm that this has all worked properly, use
select * from sysprogress.sysdbauth;
to list the authorizations and you should see all three users with both priviledges.
With that much setup, you should then be able to define a connection to the database from OpenEdge Architect using the appropriate host name, service name, and the generic userid and password created above.
To use it with Enterprise Architect, you also need to define it as an ODBC datasource. That takes the same information, but you have to use the service number, not the name for reasons known only to Microsoft. This process is described in detail in another subpage.