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:
Create an empty database using your management console of choice
Execute the Enterprise Architect schema creation script
Load your model data
Note: more detailed instructions are available for each DBMS product
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
*** 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!
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.
This schema update requires the repository to be using the EASchema1558.
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.
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.
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:
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:
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).
Take a backup of the current DBMS repository.
Connect to the RDBMS database to be updated (using the SQL management console of choice).
Load the relevant EASchema_Alter1220to1558_xxx.sql or EASchema_Alter851to1220_xxx.sql file.
Run the script.
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'.
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'.
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
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!
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.
This schema update requires the repository to be using the EASchema1220.
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:
Transfer the contents of your existing EASchema700 model into a brand new EASchema1220 database,
using Enterprise Architect's Project Transfer function.
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.
