Enterprise Architect ENTERPRISE ARCHITECT

DBMS Based Repositories

 

The Corporate and Suite Editions of Enterprise Architect support connecting to DBMS based model repositories. To use a DBMS repository you need to create a database and then the Enterprise Architect schema which consists of a collection of tables. General speaking the creation and modification of database definitions is performed by a user (typically a DBA) with an elevated level of security, therefore these types of tasks are handled outside of Enterprise Architect the application. Below are the scripts for each of the supported DBMS products.

Please Note: Throughout Enterprise Architect's history Sparx Systems have endeavored to avoid changing the underlining database structure to remain compatible with older versions. However since 2010 there have been two major schema releases:

  • Schema 1220 released in Jun-2016 with Enterprise Architect v13 (build 1302)
  • Schema 851 released on 10-Feb-2010 with Enterprise Architect v8 (build 851)

In both of the above releases to the schema changes have not required any code changes to the Enterprise Architect logic, thereby allowing earlier version of Enterprise Architect to stay compatible with newer schema definitions (and vice versa). In the most recent release, 'schema 1220' consists mainly of index updates which improve Enterprise Architect's overall performance in larger models.

 

Basic Setup
Creating a new DBMS based repository requires the following steps to be undertaken:

  1. Create an empty database using your management console of choice
  2. Execute the Enterprise Architect schema creation script
  3. Load your model data
Please note: more detailed instructions are available for each DBMS product

 

 

Current DBMS scripts

Enterprise Architect Schema Creation Scripts

MySQL
  EASchema_1220_MySQL.sql
(56 KB, 20-Jun-2016)

Note 1: All long text fields are now MEDIUMTEXT (instead of TEXT) which allows for more than 65K characters.

Note 2: Default date values. Historically the EA MySQL script has used a default value of 0000-00-00 00:00:00 (because early versions did not support the assignment of the current date/time) for some date fields. This will cause errors in the later versions if your MySQL environment is configured as strict mode with either NO_ZERO_IN_DATE or NO_ZERO_DATE. As of v5.7.4 NO_ZERO_IN_DATE and NO_ZERO_DATE are both deprecated and enabled incorporated into Strict Mode. There are two ways around these problems: 1) change the SQL mode of your MySQL environment to allow zero dates or 2) update the EA script and replace the default values of '0000-00-00 00:00:00' to NOW()

Microsoft SQL Server from 2005, all editions including Express and Azure
  EASchema_1220_SQLServer.sql
(63 KB, 20-Jun-2016)

*** Diagram Image Optional Update ***
Without the Diagram Image Optional Update, whenever diagram images were imported via XML (which also included copying diagrams), the user required an elevated level of database permissions in order to insert new images. Using this schema update and EA version 14 users no longer need elevated database permissions.

Important Limitations!
  1. This schema update is NOT backwards compatible, after applying it the model is only compatible with Enterprise Architect Version 14 and later. Sparx Systems recommend that all users update to Enterprise Architect 14 before applying this script.
  2. This schema update requires the repository to be using the EASchema1220.
  EASchema_1220_SQLServer_Update1.sql
(3 KB, 13-Apr-2018)

Oracle from 9i (all editions)
  EASchema_1220_Oracle.sql
(63 KB, 20-Jun-2016)

PostgreSQL
  EASchema_1220_PostgreSQL.sql
(50 KB, 20-Jun-2016)

Adaptive Server Anywhere 8 and 9, SQL Anywhere 10 and 11
  EASchema_1220_SybaseASA.sql
(54 KB, 20-Jun-2016)

 

 

Enterprise Architect Initial data Scripts

Once a DBMS database is created and the Enterprise Architect schema defined, the model now needs the 'base' data loaded. Traditionally this step has been achieved by loading the contents of the 'EABase.eap' model into a newly created database, using the Project Transfer action within Enterprise Architect.

As an alternative it is now possible to load this same data using standard SQL statements. However please note the scripts below, only contain statements to insert the data into a new/empty database, therefore don't attempt to run these on models that already contain data.

MySQL
  EABase_1220_MySQL.sql
(227 KB, 30-Jun-2016)

Microsoft SQL Server from 2005, all editions including Express and Azure
  EABase_1220_SQLSvr.sql
(277 KB, 30-Jun-2016)

Oracle from 9i (all editions)
  EABase_1220_Oracle.sql
(276 KB, 30-Jun-2016)

PostgreSQL
  EABase_1220_Postgres.sql
(277 KB, 30-Jun-2016)

 

 

Enterprise Architect Initial file based respositories

The following are the empty or starter file based repositories containing all the necessary data to begin a new repository, in the various supported formats:

Base Project (EAP) - Microsoft Jet 3.5
 eabase-1220-jet35.zip
(137 Kb, 30-Jun-2016)
Base Project (EAPX) - Microsoft Jet 4.0
 eabase-1220-jet4.zip
(302 Kb, 30-Jun-2016)
Base Project (ACCDB) - Microsoft Access from 2007
 eabase-1220-accdb.zip
(278 Kb, 30-Jun-2016)

 

 

Updating earlier DBMS schemas

If you have an existing Enterprise Archiect DBMS repository that was created between Feburary 2010 and Jun 2016 then more than likely what is now referred to as the EASchema851 was used to create your Enterprise Architect DBMS schema.

If your existing EASchmea851 DBMS repository is running smoothly and without problems, then there is no immediate need to upgarde to the latest schema. However there is a direct upgrade path from EASchema851 to EASchema1220 if you so desired:

As mentioned above Enterprise Architect's official schema definition doesn't change very frequently however if your model was created inbetween 10-Feb-2010 and Jun-2016 then it will not have the most up-to-date indexes defined.

The SQL scripts below are provided to update a 'schema 851' model to 'schema 1220'. The process to run these scripts should be:

  1. Take a backup of the current DBMS repository.
  2. Connect to the RDBMS database to be updated (using the SQL management console of choice).
  3. Load the relevant EASchema_Alter851to1220_xxx.sql file.
  4. Run the script.
MySQL
  EASchema_Alter851to1220_MySQL.sql
(18 KB, 20-Jun-2016)

Note: all long text fields are now MEDIUMTEXT (instead of TEXT) which allows for more than 65K characters.

SQL Server 2000/2005/2008/2012/Express and Azure
  EASchema_Alter851to1220_SQLServer.sql
(97 KB, 20-Jun-2016)

Oracle 9i, 10g, 11g and 12c
  EASchema_Alter851to1220_Oracle.sql
(29 KB, 20-Jun-2016)

PostgreSQL
  EASchema_Alter851to1220_PostgreSQL.sql
(23 KB, 20-Jun-2016)

Sybase Adaptive Server Anywhere 8 and 9, SQL Anywhere 10 and 11
  EASchema_Alter851to1220_SybaseASA.sql
(11 KB, 20-Jun-2016)

Firebird
  EASchema_Alter851to1220_Firebird.sql
(12 KB, 23-Jun-2016)

 

 

Historical DBMS creation scripts


The following historical DBMS scripts are provided as reference material only and should not be used to create new repositories.


EASchema 851 files:

MySQL
EASchema_851_MySQL_InnoDB.sql
(51 KB, 10-Feb-2010)
EASchema_851_MySQL_MyISAM.sql
(51 KB, 10-Feb-2010)
Note: MyISAM Databases do not support transactions or unicode.

Microsoft SQL Server from 2005, all editions including Express and Azure
  EASchema_851_SQLServer.sql
(88 KB, 10-Feb-2010)
  EASchema_851_SQLAzure.sql
(88 KB, 27-Nov-2013)

Oracle from 9i (all editions)
  EASchema_851_Oracle.sql
(105 KB, 10-Feb-2010)

PostgreSQL
  EASchema_851_PostgreSQL.sql
(52 KB, 10-Feb-2010)

Adaptive Server Anywhere 8 and 9, SQL Anywhere 10 and 11
  EASchema_851_SybaseASA.sql
(57 KB, 10-Feb-2010)

 

If your model was created before 10-Feb-2010 then it is most likely your database was created with one of the original schema scripts. Which we have called EASchema700.

If you wish to upgrade to the latest database schema there are two possible methods to achieve this:

  1. Transfer the contents of your existing EASchema700 model into a brand new EASchema1220 database, using Enterprise Architect's Project Transfer function.

  2. Upgrade your existing repository with the use of two different scripts, first apply the EASchema700 to EASchema851 upgrade script (which are below), then the EASchmea851 to EASchema1220.

Sparx Systems recommend the first method, as there are less chances of schema related problems occurring, however the data transfer step can be slow if the model contains alot of data.


MySQL
 EASchema-Alter700to851-MySQL.sql (4 KB)

Microsoft SQL Server from 2005, all editions including Express
 EASchema-Alter700to851-SQLServer.sql (8 KB) Update
(Please Note: This update script is not applicable for SQL Server 2000)

Oracle from 9i (all editions)
 EASchema-alter700to851-oracle.sql (4 KB) Update

PostgreSQL
 EASchema-Alter700to851-Postgres.sql (4 KB) Update

Adaptive Server Anywhere 8 and 9, SQL Anywhere 10 and 11
 EASchema-Alter700to851-ASA.sql (4 KB) Update