With the Database Designer, it is possible to easily define database schemas and deploy them to a database endpoint via Connectors. Deployed database schemas can be used as a backend for the Simplifier applications.
This documentation covers the whole functionality of the Database Designer.
The Database Designer can be reached via the tile “Database Designer“. The main screen of the Database designer contains a list of all currently existing database schemas. The user has the option to create a new database schema via the “plus”-icon in the top-right corner. It is also possible to select an existing database schema and view, edit or even delete it.
When you decide to create a new schema or view/edit an existing schema, you get to the Schema-Details view. Here you can see all the important information about the schema.
On the left side, there is an Entity-Relation Diagram (ER-Diagram) representing the schema. Each database table in the schema is represented as a box. In the box, you can see the name of the database table and a list of all columns of the table. If the database table has a foreign key to another table, it is displayed as an arrow pointing to the target table. When creating or editing a database schema, you can add a new entity to the diagram by hitting the “Add Entity” button in the top right corner of the ER-Diagram.
In the ER-Diagram, you can select a table to see or edit its properties like name or description. These are displayed on the right side of the screen in the “Properties“-section. You can also select a foreign key to view its properties. These can however only be changed in the Table-Details-View (see below). If no table or foreign key is selected, you can see the general properties of the database schema.
Properties can only be edited if the table or foreign key is not deployed to a data source.
When a database table is selected, you can edit the columns, indexes and foreign keys of that table by clicking on the “Edit table details“-button in the Properties section. This will take you to the Table-Details-View.
The Table-Details-View consists of three sub-views: Columns, Indexes, and Foreign Keys.
In this view, you can add new columns to the database table or edit the properties of existing columns. It is also possible to remove a column from the table.
The following properties can be edited for a column:
|Name||The name of the column (cannot be edited if the column is already deployed to a data source).|
|Description||A description for the column.|
|Type||The datatype of the Column (e.g. String, Integer, …).|
|Character limit||With a checkbox whether or not the column should be limited. Only String-Type columns can have a character limit.|
|Default Value||With a checkbox whether or not a default value should be used.|
|NotNull||Indicating if the column is able to store null values or not.|
|Auto-Increment||Whether or not the column should have automatically incrementing values. It is only possible to have one Auto-Increment column per table and it has to be an Integer-Type column.|
In this view, you can add new indexes to the database table or edit the properties of existing indexes. It is also possible to remove an index from the table.
The following properties can be edited for an index:
|Name||A unique name for the Index.|
|Description||A description for the index.|
|Type||The type for the index (Index, Primary Key, Unique).|
|Columns||The columns to which the index should be mapped. This input provides an auto-complete for the existing column names in the table.|
In this view, you can add new foreign keys to the database table or edit the properties of existing foreign keys. It is also possible to remove a foreign key from the table.
The following properties can be edited for a foreign key:
|Name||A unique name for the foreign key.|
|Description||A description of the foreign key.|
|Target table||The target table for the foreign key. The dropdown contains the names of existing tables of the schema.|
|Target columns||The Column mapping to the target table. When a target table is selected, you can see a list of all primary key columns of the target table. You can then choose a column of the current table that should be mapped for each of these primary key columns. You can only choose columns that match the datatype of the target column (e.g. if the target column is an Integer-Type column, only the Integer-Type columns of the current table are listed as possible mapping columns).|
|On Update||The “ON UPDATE” property of the foreign key. Can be set to “RESTRICT”, “CASCADE”, “SET_NULL”, or “SET_DEFAULT”. The default value is “RESTRICTED”, which is also the default value for most databases.|
|On Delete||The “ON DELETE” property of the foreign key. Can be set to “RESTRICT”, “CASCADE”, “SET_NULL”, or “SET_DEFAULT”. The default value is “RESTRICTED”, which is also the default value for most databases.|
Deploy Schema to Datasource
You can deploy a schema to a data source by clicking the “Deploy schema to data source“-Button in the top right corner of the SchemaDetails-View. This will open the schema deployment dialog. You can deploy a schema to any SQL Connector that is defined in the system. Deployment is currently only supported for MySQL, SQLite, MSSQL, and Oracle Databases.
In the schema deployment dialog, you can choose a target SQL connector. After choosing a SQL connector you can start the deployment to the data source. Additionally, there are two flags that can change the mode of deployment:
|Safe Mode||The safe mode flag only impacts the deployment when you are re-deploying a schema that was already deployed and edited afterward. In this case, the deployment will not start if any ALTER or DELETE operations would be performed on the deployed schema to ensure that no data is lost in the database.|
|Dry Run||With the Dry Run flag set, the system will only run a simulation of the deployment instead of the deployment itself.|
The Deployment Log stores information about all past schema deployments. It can be accessed from the Schema Overview by switching to the “Deploy Log”-Tab. Here you find a list of all deployments with the name of the deployed schema, the target SQL connector, the user who executed the deployment, the date and time when the deployment was started and whether the deployment was successful.