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.
- 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.
- 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.
- 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.
- 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.
- 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
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.
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.
- 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.
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 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.
- 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.
- 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:
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.
- 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.
- 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.
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.
- 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.
- 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.
- 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.
- 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.
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.
- 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.
- 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.