DB Schema Worksheet
Introduction
DB Schema is an AlchemyJ Component which loads table structure information from database and links data dictionary in an AlchemyJ workbook. Before using this component, the database connection must be setup in the ##ExternalResources worksheet.
DB Schema component should work with the following AlchemyJ Extended Functions, can reference the example in below functions to use it.
Configuration
All columns are retrieved from the specified table except Case Insensitive ,Data Dictionary Address and Data Name . These columns should be supplemented by the user.
| Configuration Item | Description |
|---|---|
| Data Source ID | String. The key name of Data Source. |
| Table Owner | String. The user name of the owner of the table,it will be different depending on the database type For example, the owner of MSSQL is dbo, the owner of PostgreSQL DB is public. When you input the table name, such as dbo.tablename, it will be automatically filled in. |
| Table Name | String. The name of the table. |
| Column ID | Integer. The unique ID of the column. |
| Column Display Name | String. The column name for display. |
| Column Name | String. The name of the column. |
| Data Type | String. Data Type of the column, it will be different depending on the database type. e.g. VARCHAR2, DATE, NUMBER, TIMESTAMP, FLOAT and so on. |
| Data Length | Integer. The length of the data. |
| Data Precision | Integer. The precision of the data. |
| Data Scale | Integer. The scale of the data. |
| Mandatory | It can be Y or empty. Y means the value must be not null. |
| Key Order | Integer. It could be empty or an integer. It specifies the order of the key column among other key columns. 1 is meaning this is the first key column. |
| Default Value Type | The type of default value when the value of the field is not provided. GUID - Assign a GUID. SQL - Calculate the default value by a SQL statement. AUTO_NUM - Assign a sequence number. |
| In Out | When the column has a default value, For Oracle, MySQL and MariaDB database table, it will be set as InOut. For PostgreSQL and MSSQL database table, it will be set as InOut or Out. If the column does not have a default value, it will be set as empty. |
| Mapped Data Type | String. Base on the column 'Data Type' value defined in different database, auto-fill in the unified data type definition in AlchemyJ. |
| Data Dictionary Address | The address of the Data Dictionary range in a Data Dictionary worksheet. It is recommended to use ajAddress so that the address can be updated automatically. |
| Data Name | The Data Name in Data Dictionary which this column maps to. |
| Case Insensitive | Whether this field of table is case sensitive when filtering or searching the DB records. Yes - Case Insensitive. No - Case Sensitive, Default is No. |
Add DB Schema
Go to the AlchemyJ ribbon Model -> Add Component on the dropdown list, select DB Schema. before add this component please add database connection first [External Resources Worksheet](External Resources Worksheet) .
If there are more than one active data source, a dialog box will be shown for you to select which data source to use when load table info from database.
Input the name of the table like dbo.tb_customer you want to retrieve from your database. The table information will be loaded to the worksheet automatically.
One DB Schema worksheet can contain multiple table schemas. To add a new table schema into an existing DB Schema worksheet, please repeat above steps again in the DB Schema worksheet you would like to add.
If the properties of a table in database has been changed, you should go to the DBSchema sheet,simply add the same table to DB Schema again (using Add Component\DB Schema), then it will get the new table info and delete the old one.
Externalized Configuration
After your workbook is generate as a REST API, and now you want to modify the DB Schema and make it effective when call the Rest API . If the workbook of DB Schema is the same as the workbook of generated REST API, you need to compiled it again. By reading the externalized DB Schema configuration, you do not need to recompile it to make it effective.
Prepare a workbook to save the DB Schema configuration.
Use the ajReadWorkbook function to load the configuration to the workbook which is used to generate REST API. More details about ajReadWorkbook, refer to ajReadWorkbook.
After generating REST API, if the table info change, only update the DB Schema in the external workbook which is created in step 1 with no need to modify it in the API workbook and compiled it again.
