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

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

OR

--export-table-schema <<pathToJDBCFile> <tableNames>>


Required Parameter - <pathToJDBCFile>

  • <pathToJDBCFile> - defines the path and file name of the JDBC Properties file


Example

/home/jdoe/config/config.properties



Optional Parameter - <tableName>

  • <tableName> - defines the names of one or more tables whose schema is to be extracted.

Example

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

Note:

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

The -xt command option can be used to exclude schema tables passed in the command line.


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

Example 2: Exclude all Tables of Sales Schema 

This example reads in all the schemas' tables from the database and excludes all the tables of the Sales schema.


genrocket -xts config.properties -xt Sales:*


Example 3: Exclude Dept and User Tables in Sales Schema

To read in all the tables of Sales schema except for dept and user tables of the Sales schema.


genrocket -xts config.properties Sales:* -xt Sales:dept, Sales:user


Generic

Example 4: Reads all Schemas' Tables and Excludes User Table from All Schemas (if found)

To read in all the schemas' tables and exclude user table from all the schemas, if found.


genrocket -xts config.properties -xt user
Generic


Exclude Schema Tables Specified in CSV File (-xf) Command Option

The -xf command option can be used to exclude schema tables mentioned in the CSV file passed along with the command. 


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:

Sales:product
Sales:*
user

Example 1: Read all Schema's Tables and Exclude Ones Mentioned in CSV File

This example reads in all the schemas' tables and excludes schema tables mentioned in the file.


genrocket -xts config.properties -xf /home/jd/Documents/excludeTables.csv


Example 2: Reads all Sales Schema Tables Except those Defined in CSV File

This example reads in all the tables of the Sales schema except for the ones mentioned in the 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 JDBC Config 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.


      driver=com.mysql.jdbc.Driver    

      user=root        

      password=admin    

      url=jdbc:mysql://localhost:3306/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. 


      connection=NEW


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.