Book a Demo
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. Generally 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. Since 2010 there have been the following schema releases:

  • Schema 1558 released in Feb-2021 with Enterprise Architect v15.2 (build 1558)
  • 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)

Schema 1558
The changes introduced in Schema 1558, are:
Increased Length of User Login - More and more users were finding the original Security User login field too limiting (at 32 characters), particularly when using Single Sign-On (SSO) as it could only store short email addresses, therefore this field was increased to 255 characters.

New definition to easily identify the schema version - Previously it was difficult to determine what schema version a database had been defined with. Schema 1558 introduces a new table definition that allows Enterprise Architect to display the schema version in the Help About screen.

New defaults in MySQL - A number of date/time fields were updated to use the current date/time (instead of an empty date) when inserting new records. Additionally all text based fields now use the character set of UTF8MB4 and collation UTF8MB4_GENERAL_CI. Sparx Systems recommends that customers should review all defaults and update the schema script to suit their environment.

While code changes were introduced in build 1558 to take full advantage of the schema changes, great care was taken to stay both forwards and backwards compatible. Therefore it is not necessary to use build 1558 (or later) of Enterprise Architect when using a repository that is defined with the EASchema1558, although it is recommended.

Schema 1220
Consolidated, standardized and improved the index definitions across the complete range of tables, thereby improving Enterprise Architect's overall performance in larger models. No Enterprise Architect code changes were required, thereby allowing earlier versions of Enterprise Architect to stay compatible with newer schema definitions (and vice versa).

The changes introduced in Schema 1220, are:
(SQLServer only) Changed the datatype of all large text Fields - Prior to EASchema1220 all large text fields (ie for element comments etc) were defined with the datatype NTEXT, however from MS SQL Server 2016 the NTEXT datatype was considered deprecated and as a result all NTEXT fields were changed to NVARCHAR(MAX).

Schema 851
Consisted mainly of index updates which improve Enterprise Architect's overall performance in larger models. No Enterprise Architect code changes were required.

 

 

Current DBMS scripts

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
Note: more detailed instructions are available for each DBMS product

 

Enterprise Architect Schema Creation Scripts

MySQL
  EASchema_1558_MySQL.sql
(60 KB, 2-Feb-2021)

Note 1: A number of DateTime fields now use the default value of CURRENT_TIMESTAMP therefore if your database is v5.6.4 or earlier you will need to replace CURRENT_TIMESTAMP with '0000-00-00 00:00:00'.

Note 2: All tables have been defined with the default Character Set UTF8MB4 and Collation of UTF8MB4_900_CI_AI however this should be reviewed to suit you environment.

Microsoft SQL Server from 2005, all editions including Express and Azure
  EASchema_1558_SQLServer.sql
(67 KB, 2-Feb-2021)

*** Diagram Image Optional Update ***
The 'Diagram Image Optional Update' is a patch applied after EASchema_1558_SQLServer.sql. By default whenever diagram images are imported via XML (which also includes 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 (and later), 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 EASchema1558.
  3. If your repository has been upgraded from an earlier schema release, then DO NOT apply this update if the repository had EASchema_1220_SQLServer_Update1.sql applied.
  EASchema_1558_SQLServer_Update1.sql
(3 KB, 2-Feb-2021)
Oracle from 9i (all editions)
  EASchema_1558_Oracle.sql
(67 KB, 2-Feb-2021)
PostgreSQL
  EASchema_1558_PostgreSQL.sql
(53 KB, 2-Feb-2021)

 

 

Enterprise Architect Initial data Scripts

Once a DBMS database is created and the Enterprise Architect schema defined, the repository 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 insert statements, and expect the database to empty, therefore don't attempt to run these on databases that already contain data.

MySQL
  EABase_1558_MySQL.sql
(277 KB, 26-Aug-2022)
Microsoft SQL Server from 2005, all editions including Express and Azure
  EABase_1558_SQLSvr.sql
(277 KB, 26-Aug-2022)
Oracle from 9i (all editions)
  EABase_1558_Oracle.sql
(276 KB, 26-Aug-2022)
PostgreSQL
  EABase_1558_Postgres.sql
(277 KB, 26-Aug-2022)

 

 

Enterprise Architect Initial file based repositories

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 (QEA) - SQLite
 eabase-1558-qea.zip
(76 Kb, 26-Aug-2022)
Base Project (EAP) - Microsoft Jet 3.5
 eabase-1558-jet35.zip
(145 Kb, 2-Feb-2021)
Base Project (EAPX) - Microsoft Jet 4.0
 eabase-1558-jet4.zip
(125 Kb, 2-Feb-2021)
Base Project (ACCDB) - Microsoft Access from 2007
 eabase-1558-accdb.zip
(445 Kb, 2-Feb-2021)
Base Project (FEAP) - Firebird
 eabase-1558-fb.zip
(235 Kb, 2-Feb-2021)

 

 

Updating earlier DBMS schemas

If you have an existing Enterprise Archiect DBMS repository and you are unsure when it was created or what schema was used, then unfortunately there is no definitive way to tell (unless you are using EASchema1558 or later), apart from comparing the physical definition to the various scripts. In such situations it is normally easiest to simply create a brand new database using the latest schema and perform a Project Transfer into this new database.

If you know (or have identified) what schema your existing Enterprise Archiect DBMS repository has been defined with, then the following alter scripts can be used to update to the next release. Each of these scripts upgrades a schema version to the next version. They can be used consecutively if they are applied in chronological order.
For example if you have a 851 schema you can run the 851 to 1220 script and then the 1220 to 1558.

Important: Sparx Systems recommends that before performing any kind of schema update that the database should be backed up using the DBMS's native backup utility!

Steps to follow when appling a schema update:

  1. Get all users to logout/disconnect from the repository, (you could consider disabling the database server's network card to stop user access, while the backup and update is occurring).
  2. Take a backup of the current DBMS repository.
  3. Connect to the RDBMS database to be updated (using the SQL management console of choice).
  4. Load the relevant EASchema_Alter1220to1558_xxx.sql or EASchema_Alter851to1220_xxx.sql file.
  5. Run the script.
  6. Re-establish the server's connectivity, if steps were takem to stop user access.

 

Alter Scripts

The SQL scripts below are provided to update a 'schema 1220' database to 'schema 1558'.

Firebird
 
  EASchema_Alter1220to1558_Firebird.sql
(1 KB, 2-Feb-2021)

MySQL
 
  EASchema_Alter1220to1558_MySQL.sql
(1 KB, 2-Feb-2021)

Note: A number of DateTime fields now use the default value of NOW() therefore if your database is v5.6.4 (or earlier) you will need to replace NOW() with '0000-00-00 00:00:00'.

SQL Server 2000/2005/2008/2012/Express and Azure
 
  EASchema_Alter1220to1558_SQLServer.sql
(1 KB, 2-Feb-2021)

Oracle 9i, 10g, 11g and 12c
 
  EASchema_Alter1220to1558_Oracle.sql
(1 KB, 2-Feb-2021)

PostgreSQL
 
  EASchema_Alter1220to1558_PostgreSQL.sql
(1 KB, 2-Feb-2021)

 


The SQL scripts below are provided to update a 'schema 851' database to 'schema 1220'.

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

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)

 

 

Historical DBMS creation scripts


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


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 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)
 
  EASchema_1220_SQLServer_Update1.sql *** Diagram Image Optional Update ***
(3 KB, 13-Apr-2018)
  Note 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.

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 file based repositories
 
  Base Project (EAP) - Microsoft Jet 3.5 eabase-1220-jet35.zip
(145 Kb, 30-Jun-2016)
 
  Base Project (EAPX) - Microsoft Jet 4.0 eabase-1220-jet4.zip
(332 Kb, 30-Jun-2016)
 
  Base Project (ACCDB) - Microsoft Access 2007 eabase-1220-accdb.zip
(291 Kb, 30-Jun-2016)


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