What is XTS?
XTS stands for Extract Table Schema. In this approach GenRocket can retrieve one, many or all table schemas from a given database by connecting directly to the database via JDBC to extract the database’s schema. The extracted schema is saved to an encrypted file. The encrypted file can then be imported to the GenRocket web application to automatically do the following:
- Import the application / database Data Schema
- GenRocket XTS and Data Warehouse will automatically set up all of the Domains, Attributes and assign Generators
- You will use the Domain Referencing Wizard to establish Parent-Child-Sibling relationships for the Domains
- You will swap out the Generators that were incorrectly assigned by the Data Warehouse for the correct Generators and modify Generator parameters
In This Article
- XTS Command
- Required Parameter - <pathToJDBCFile>
- Optional Parameter - <tableName>
- What happens if no table names are included?
- Exclude Schema Tables (-xt) Command Option
- Exclude Schema Tables Specified in CSV File (-xf) Command Option
- Special note about Oracle Databases
- Connect to a Database
- JDBC Configuration File
- Example JDBC Configuration File
- Sequence of Events
- How to import Domains from an XTS File
The following command line options instruct GenRocket to retrieve one, many, or all table schemas from a given database by connecting directly to the database via JDBC to extract the database's schema. The extracted schema is saved to an encrypted file. The encrypted file can then be imported to the GenRocket web application to automatically create Domains for each table whose schema is defined within the encrypted file.
-xts <<pathToJDBCFile> <tableNames>>
--export-table-schema <<pathToJDBCFile> <tableNames>>
Required Parameter - <pathToJDBCFile>
- <pathToJDBCFile> - defines the path and file name of the JDBC Properties file
Optional Parameter - <tableName>
- <tableName> - defines the names of one or more tables whose schema is to be extracted.
-xts /home/jdoe/config/config.properties company,department,user
Example 1: Table belongs to a Specific Schema
If the table belongs to a particular schema in your database, e.g. schema1 then your command should include the schema name as well.
genrocket -xts /home/jdoe/config/config.properties schema1:company,schema1:department, schema1:user
Example 2: Extracting all Schema Tables
This example shows how to extract all tables of your schema:
genrocket -xts /home/jdoe/config/config.properties schema1:*
In case you are using a non bash shell (like Z shell) which interprets asterisk(*) as a special character and if you get an exception like: zsh: no matches found: schema1:*
You will have to use backslash(\) before asterisk(*) so that Z shell doesn't interpret as a special character. The command will then look like:
genrocket -xts /home/jdoe/config/config.properties schema1:\*
Example 3: Extracting Tables from more than one Schema
This example shows how to extract tables from multiple schemas:
genrocket -xts /home/jdoe/config/config.properties schema1:user,schema2:address
What happens if no table names are included?
By not providing any table names, all of the table's schema within the given database will be extracted.
Exclude Schema Tables (-xt) Command Option
genrocket -xts config.properties -xt schema:table,schema:table
Example 1: Exclude Product and Order Tables in Sales Schema
This example reads in all the schemas' tables from the database and excludes the product and order tables of the Sales schema.
genrocket -xts config.properties -xt Sales:product,Sales:order
Exclude Schema Tables Specified in CSV File (-xf) Command Option
genrocket -xts config.properties -xf <FILE_PATH>
The CSV file passed along with the -xf option will need to contain the schema table names, one row per table:
Example 1: Read all Schema's Tables and Exclude Ones Mentioned in CSV File
genrocket -xts config.properties -xf /home/jd/Documents/excludeTables.csv
Example 2: Reads all Sales Schema Tables Except those Defined in CSV File
genrocket -xts config.properties Sales:* -xf /home/jd/Documents/excludeTables.csv
Special note about Oracle Databases
To extract specific tables for an Oracle database, please use uppercase schema/table names.
Connect to a Database
The GenRocket table schema importer must connect to a database via JDBC. It connects to a database by accessing a user's profile and then reading the configuration file passed in the command line with the -xts option.
JDBC Configuration File
The JDBC configuration file must contain the following four parameters (the order of the parameters does not matter):
- driver - The name of the database driver
- user - The username to connect to the database
- password - The password to connect to the database
- url - The universal resource locator to connect to the database
Example JDBC Configuration File
The following example configuration file connects to a MySQL database called, acme.
Note: For Oracle, if you face the following exception in creating the XTS file
ORA-01000: maximum open cursors exceeded
The Config file needs to have 1 more property.
This property creates the new Connection to read each table information.
Sequence of Events
The following sequence diagram describes the steps that GenRocket executes once an -xts command is given:
How to import Domains from an XTS File
To learn how to import Domains from an XTS File, click here.