Database Schema Tool

Use the Database Schema tool to generate SQL statements for creating database schemas, creating database tables, and populating those tables with initial data.

After the scripts are created, you can supply them to your database administrator, who can review and run them against the database system before you deploy the models.

The Database Schema Tool is primarily intended for use in production environments, where the developers making updates to the project models do not have the appropriate access to make updates to the databases that those models run against. If you are working in a development or test environment in which you have the appropriate permissions to access and make changes to the application database, then you may want to use the Data Load Tool instead. For more information about the Data Load Tool, see Loading databases.

Generating database schema SQL

Use the Database Schema Tool's setup command to generate an SQL script for creating an initial database schema. You or your database administrator can review and run this script against your database system to create the databases required by the model.

To generate code for setting up a new database schema:

In NexJ Studio, click the arrow next to the Run Tool button
and select Database Schema Tool.
The Database Schema Tool opens.
From the Model section, choose the model that you want to create a schema for:
- For the currently loaded model, shown in the label, select Current.
- For a base model in your Model Library, select Base Model and select one of the available models from the drop-down.
- For a previously published model in your file system, select Published Model JAR and click the Browse button
  to locate the published model. You might be prompted to specify the directory where the published model's mixins are located.
If you want to use your database to run the application in a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.

Info

If you are creating a schema for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
1. In the Server field, specify either the server or environment file that contains the server configuration information you want to use when creating the database schema. If you specify an environment file, then it will also include the connections information.
2. In the Connections field, specify the connections file that you want to use when creating the database schema. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
In the Command field, select setup.
In the Data Source field, select the model data source that you want to set up the schema for.
Your selection must be one of the relational data sources described in the model.
In the Output Directory field, click the Browse button
, specify where you want to save the resulting SQL file, and click OK.
[Optional] In the Owner field, specify an owner prefix for your tables.
This field refers to the metadata table owner value. If you leave the field blank, a default value of dbo is used.
Click Advanced to display the additional VM arguments that will be used in the script.
In the VM Arguments field, review the parameters and their values.
Info

The directories set for the following parameters must exist on the machine where the scripts will be run:
- datapath
- indexpath
- longpath
- undopath
Click Run.

The tool outputs a script file named <modelname>_<datasourcename>_setup.sql to the output directory, where <modelname> is the name of the chosen model and <datasourcename> is the name of the chosen data source.

You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.

Creating database tables

Use the Database Schema Tool's create command to generate an SQL script for creating your database tables. This command allows you to choose a set of parameters with initial values to load into your database.

To generate code for creating the database tables:

In NexJ Studio, click the arrow next to the Run Tool button
and select Database Schema Tool.
The Database Schema Tool dialog opens.
From the Model section, choose the model that you want to create database tables for:
- For the currently loaded model, shown in the label, select Current.
- For a base model in your Model Library, select Base Model and select one of the available models from the drop-down list.
- For a previously published model in your file system, select Published Model JAR and click the Browse button
  to locate the published model. You might be prompted to specify the directory where the published model's mixins are located.
If you want to use a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.
1. In the Server field, specify either the server or environment file that contains the server configuration information you want to use when setting up the database. If you specify an environment file, then it will also include the connections information.
2. In the Connections field, specify the connections file that you want to use when setting up the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
Info

If you are creating the database for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
In the Command field, select create.
In the Data Source field, select the model data source that you want to use. Selections are limited to the relational data sources described in the model.
In the Output Directory field, click the Browse button
, specify where you want to save the resulting SQL file, and click OK.
[Optional] In the Owner field, specify an owner prefix for your tables.
This field refers to the metadata table owner value. If you leave the field blank, a default value of dbo is used.
Click Run.

The tool outputs a script file name <modelname>_<datasourcename>_create.sql to the output directory where <modelname> is the name of the chosen model and <datasourcename> is the name of the chosen data source.

You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.

Generating initial database data

Use the Database Schema Tool's insert command to generate an SQL script for inserting system default data into a database. The insert command reads the system default data, then generates an SQL script that inserts this class data into the appropriate tables as defined by the persistence mapping.

Note

Only insert data if it does not already exist in the database; otherwise the insert will fail. If the insert fails because data already existed in the database, the duplicate data will need to be deleted from the model before proceeding. To delete data, use the truncate command. For more information about using the truncate command, see Truncating database schema tables.

To insert data into a database:

In NexJ Studio, click the arrow next to the Run Tool button
and select Database Schema Tool.
The Database Schema Tool opens.
In the Model section, choose the model that you want to insert data from:
- For the currently loaded model, shown in the label, select Current.
- For a base model in your Model Library, select Base Model and select one of the available models from the drop-down list.
- For a previously published model in your file system, select Published Model JAR and click the Browse button
  to locate the published model. You might be prompted to specify the directory where the published model's mixins are located.
Specify the server and connections that you want the application to support when running with this database.
1. In the Server field, specify either the server or environment file that contains the server configuration information you want to use when setting up the database. If you specify an environment file, then it will also include the connections information.
1. In the Connections field, specify the connections file that you want to use when setting up the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
  
  Info
  
  If you are setting up the database for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
In the Command field, select insert.
In the Data Source field, select the data source that you want to insert data into.
Selections are limited to the model's relational data sources.
In the Output Directory field, click the Browse button
, specify where you want to save the resulting SQL file, and click OK.
[Optional] If you want to specify an owner prefix for your tables, enter it in the Owner field.
This field refers to the metadata table owner value. If you leave the field blank, a default value of dbo is used.
Click Run.

The tool outputs a script file to the specified directory called <modelname>_<datasourcename>_insert.sql, where <modelname> is the name of the chosen model and <datasourcename> is the name of the chosen data source.

You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.

Upgrading database schemas

Use the Database Schema Tool's upgrade command to generate an SQL script for upgrading a model data source to support the current version of the model.

As you develop your model, you typically make changes to the information in the Persistence layer, including the data source model. These changes are also captured in the upgrade file. Each individual change is recorded in an upgrade step, with multiple steps being grouped together into an upgrade and associated with a specific model version.

When you run the upgrade task in the Database Schema Tool, the tool examines the database to determine the version of the model that it currently supports.

Upgrading a database schema

Before upgrading a database, ensure that you have updated your model's upgrade file.

To upgrade a database:

In NexJ Studio, click the arrow next to the Run Tool button
and select Database Schema Tool.
The Database Schema Tool opens.
In the Model section, choose the model that you want to use:
- For the currently loaded model, shown in the label, select Current.
- For a base model in your Model Library, select Base Model and select one of the available models from the drop-down.
- For a previously published model in your file system, select Published Model JAR and click the Browse button
  to locate the published model.
If you want to use a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.

Info

If you are setting up the database for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
1. In the Server field, specify either the server or environment file that contains the server configuration information you want to use when upgrading the database. If you specify an environment file, then it will also include the connections information.
2. In the Connections field, specify the connections file that you want to use when upgrading the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
In the Command field, select upgrade.
In the Data Source field, select the desired data source.
Selections are limited to the model's relational data sources.
In the Output Directory field, click the Browse button
, specify where you want to save the resulting SQL file, and click OK.
[Optional] If you require an owner prefix for your tables, include a value for the Owner field.
This field refers to the metadata table owner value. If you leave the field blank, the tool will default to a prefix of dbo.
Click Run.
The tool outputs a script file to the specified directory called <modelname>_<datasourcename>_upgrade.sql, where:
- <modelname> is the name of the chosen model.
- <datasourcename> is the name of the chosen data source.

You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.

Verifying the upgrade

To verify that the upgrade worked after running the script, run your database's management tool or query client.

Verify that the version record in the Version table matches the version of your model.

In Microsoft SQL Server or DB2, issue the following statement to return the value of the version record in the Version table:
select version from NJVersion
In MySQL, issue the following statement to return the value of the version record in the Version table:
SQL
```
use <modelName>;
select version from NJVersion;
```
where <modelName> is your model's name.

Updating database schema statistics

Use the analyze command to generate an SQL script for updating database schema statistics.

To update database schema statistics:

In NexJ Studio, click the arrow next to the Run Tool button
and select Database Schema Tool.
The Database Schema Tool opens.
In the Model section, choose the mode that you want to use:
- For the currently loaded model, shown in the label, select Current.
- For a base model in your Model Library, select Base Model and then select one of the available models from the drop-down.
- For a previously published model in your file system, select Published Model JAR and click the Browse button
  to locate the published model.
If you want to use a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.

Info

If you are setting up the database for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
1. In the Server field, specify either the server or environment file that contains the server configuration information you want to use when setting up the database. If you specify an environment file, then it will also include the connections information.
2. In the Connections field, specify the connections file that you want to use when setting up the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
In the Command field, select analyze.
In the Data Source field, select the desired data source.
Selections are limited to the model's relational data sources.
In the Output Directory field, click the Browse button
, specify where you want to save the resulting SQL file, and click OK.
[Optional] If you require an owner prefix for your tables, include a value for the Owner field.
This field refers to the metadata table owner value. If you leave the field blank, the tool will default to a prefix of dbo.
Click Run.
The tool outputs a script file to the specified directory called <modelname>_<datasourcename>_analyze.sql, where:
- <modelname> is the name of the chosen model.
- <datasourcename> is the name of the chosen data source.

You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.

Exporting metadata schema from databases

Use the export command to generate a metadata datasource file from the schema of a database. Use the wrap command to generate a metadata datasource file and generate class metadata files with persistence mapping from the schema of a database. These two tools are effectively equivalent to the File/Import/NexJ Studio/Relational Database Schema command in NexJ Studio.

To export a metadata schema from a database:

In NexJ Studio, click the down arrow next to the Run Tool button
and select Database Schema Tool.
The Database Schema Tool opens.
In the Model section, choose the model that you want to use:
- For the currently loaded model, shown in the label, select Current.
- For a base model in your Model Library, select Base Model and then select one of the available models from the drop-down.
- For a previously published model in your file system, select the Published Model JAR and click the Browse button
  to locate the published model.
If you want to use a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.

Info

If you are setting up the database for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
1. In the Server field, specify either the server or environment file that contains the server configuration information you want to use when setting up the database. If you specify an environment file, then it will also include the connections information.
2. In the Connections field, specify the connections file that you want to use when setting up the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
In the Command field, select export or wrap.
In the Data Source field, select the desired data source.
Selections are limited to the model's relational data sources.
In the Output Directory field, click the Browse button
, specify where you want to save the resulting SQL file, and click OK.
[Optional] If you require an owner prefix for your tables, include a value for the Owner field.
This field refers to the metadata table owner value. If you leave the field blank, the tool will default to a prefix of dbo.
Click Run.
The tool outputs a script file to the specified directory called <modelname>_<datasourcename>_commandchoice.sql, where:
- <modelname> is the name of the chosen model.
- <datasourcename> is the name of the chosen data source.
- <commandchoice> is the exportorwrap, depending on which command you chose.

You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.

Truncating database schema tables

Use the truncate command to generate an SQL script for truncating the tables within a schema. Running this script will delete all rows from data tables within the database schema, but leave the table definitions intact.

To truncate a database schema:

In NexJ Studio, click the arrow next to the Run Tool button
and select Database Schema Tool.
The Database Schema Tool opens.
In the Model section, choose the model that you want to use:
- For the currently loaded model, shown in the label, select Current.
- For a base model in your Model Library, select Base Model and then select one of the available models from the drop-down.
- For a previously published model in your file system, select Published Model JAR and click the Browse button
  to locate the published model.
If you want to use a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.

Info

If you are truncating a schema for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are truncating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
1. In the Server field, specify either the server or environment file that contains the server configuration information you want to use when setting up the database. If you specify an environment file, then it will also include the connections information.
2. In the Connections field, specify the connections file that you want to use when setting up the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
In the Command field, select truncate.
In the Data Source field, select the desired data source.
Selections are limited to the model's relational data sources.
In the Output Directory field, click the Browse button
, specify where you want to save the resulting SQL file, and click OK.
[Optional] If you require an owner prefix for your tables, include a value for the Owner field.
This field refers to the metadata table owner value. If you leave the field blank, the tool will default to a prefix of dbo.
Click Run. The tool outputs a script file to the specified directory called <modelname>_<datasourcename>_truncate.sql, where:
- <modelname> is the name of the chosen model.
- <datasourcename> is the name of the chosen data source.

You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.

Dropping database schemas

Use the drop command to generate an SQL script for dropping a database schema. This script will drop all tables in the database associated with your chosen environment by systematically traversing the selected datasource definition and removing each table.

Note

Running the generated SQL script will drop your database schema. This cannot be undone.

To drop a database schema:

In NexJ Studio, click the down arrow next to the Run Tool button
and select Database Schema Tool.
The Database Schema Tool opens.
In the Model section, choose the model that you want to use:
- For the currently loaded model, shown in the label, select Current.
- For a base model in your Model Library, select Base Model and then select one of the available models from the drop-down.
- For a previously published model in your file system, select Published Model JAR and click the Browse button
  to locate the published model.
If you want to use a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.

Info

If you are dropping a schema for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are dropping a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
1. In the Server field, specify either the server or environment file that contains the server configuration information you want to use when setting up the database. If you specify an environment file, then it will also include the connections information.
2. In the Connections field, specify the connections file that you want to use when setting up the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
In the Command field, select drop.
In the Data Source field, select the desired data source.
Selections are limited to the model's relational data sources.
In the Output Directory field, click the Browse button
, specify where you want to save the resulting SQL file, and click OK.
[Optional] If you require an owner prefix for your tables, include a value for the Owner field.
This field refers to the metadata table owner value. If you leave the field blank, the tool will default to a prefix of dbo.
Click Run.
The tool outputs a script file to the specified directory called <modelname>_<datasourcename>_drop.sql, where:
- <modelname> is the name of the chosen model.
- <datasourcename> is the name of the chosen data source.

You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.

Setting virtual machine requirements

The Database Schema Tool also allows you to set virtual machine (VM) requirements.

These can alter parameters such as allocation of memory or boot state of the machine.

The following are examples of two VM arguments you could use:

Ddump.format=JSON

Serializes to json rather than to the standard format.

Ddump.compressed=false

Specifies whether to use gzip or not. The default is true.

To set VM requirements:

Click Advanced in the Database Schema Tool to open the VM Arguments field.
Enter your VM Arguments in the VM Arguments field.