Inserting a new schema

A database schema defines which data is saved in a database and in what form, as well as the relationship between this data. The graphical editor in SiteArchitect can be used to model database tables along with the associated columns and the relationships between individual tables.

Shared access to the database (read-only)

So that database content can be used in several FirstSpirit projects, shared access must be configured in the project settings of all participating projects. Care must be taken to ensure that no more than one project is given write access to the database and that all the remaining projects only have shared read access to the relevant database content. This means that the “Write-protected” and “No schema sync” boxes must be checked for the projects concerned when configuring the corresponding database layer.         

The “No schema sync” setting prevents changes to the database schema from being applied to the physical database. Checking the box for “Write-protected” prevents shared write access from the projects to the database. Read-only access to the database content is then possible in all projects (views of the database); however, changes to the database content cannot be initiated from projects.

Additional information see Databases (→Documentation for Administrators).

Creating a schema

When a new schema is created in SiteArchitect, (as well as adding a schema node below the “Database schemata” root node) this process generates a new database or a new database schema in the database configured for the associated project. If, for example, the project administrator has configured the “Internal database (Derby)” for the project, a new database is created within the internal database (Derby) when “New – Create schema” is selected from the context menu. (The behavior depends on how the database layer is configured for setting “No schema sync” – see Database layer configuration (→Documentation for Administrators).) The editor is able to access the database via the respective tables in the FirstSpirit Data Store and can enter content in the associated tables, which is written to the database (unless the database has been defined as “write-protected” by the project administrator).

If a new schema is created for a data source in FirstSpirit, the database where it is to be stored for productive operation and the rights for the DBMS account used by FirstSpirit in productive operation should be decided in advance. The layer types cannot be easily converted later on (see Database layer configuration (→Documentation for Administrators)). In case of doubt, a separate standard layer should be created for each FirstSpirit schema.

In general, we recommend that development always be carried out in an environment appropriate for productive operation. In particular, the Derby DBMS included in FirstSpirit is not suitable for productive operation and should, therefore, only be used for tests.

A database layer has to be selected in addition to the (language-dependent) display and reference name to generate a new schema.

Database layer: An existing database layer for a database, where the individual database tables for this schema are to be saved, has to be selected in the “Database layer” field. The types of layers that can be selected here are called “DBA layers” and (optional) “Standard layers”:

• Standard layer: A standard layer has to be selected in order for work to be carried out directly on an existing database schema. This layer can be used in multiple FirstSpirit projects that all read content from the same database. Only one of the projects involved should have write permission for the database to prevent overlaps when using a standard layer (see above “Shared access to the database”).
• DBA layer: If a DBA layer is selected, a separate schema or database is created for each project upon creation (during the first sync). In this case, no overlap can occur when writing content.
• New DBA layer: If there is no database layer available for the project yet, the entry “New DBA layer” is shown instead of a database layer. The new layer is generated in addition to the schema or database when the dialog is confirmed.

Creating a schema from a database

Instead of generating an empty schema node, a new schema node is created in the FirstSpirit project based on the pre-existing tables and relationships of a database. The new schema is created from a database using the “Create schema from database”" context menu entry.

Generating a new schema from an existing database in SiteArchitect inserts a new schema node underneath the "“Database schemata”" root node. In the process, an attempt is automatically made to transfer the existing tables and content from an existing database or from an existing database schema to the graphical schema editor (for “"Restrictions”", see Restrictions concerning database systems (→Documentation for Administrators)). If, for example, the project administrator has configured an external Oracle database for the project, a schema based on the external database is generated using the "“New – Create schema from database”" context menu. The associated tables are also transferred. Depending on the database configuration, the editor is granted read access to the database via the Data Store and can read out and generate content from the respective tables.

The structure and content of an external database may not be changed. In contrast to internal databases, only read access is possible for external databases, not write access. The restrictions "“No schema sync”" and “"Write-protected”" have to be enabled by the project administrator for this. In this case, content can be read out from an external schema and generated as a new schema node in FirstSpirit SiteArchitect. The content can then be displayed (but not modified) in the Data Store using table templates.

The following information must be input to create a new schema:

Name of the database schema: The name for the database schema has to be specified in this field. In contrast to when creating a new, empty schema, the name is not freely selectable. Instead, it has to correspond exactly to the physical name of the database schema (or the database).

If the selected name is not available in the respective database, an empty schema node is created. In this case, no content (e.g. tables) can be transferred from the selected database.

Database layer: An existing database layer has to be selected in the “Database layer” field.

The option for selecting an external database is only available if the project administrator has configured the project properties so that an external database can be accessed for the project. Please contact the project or system administrator if you have any questions regarding the database.