Performing schema operations

UltraLiteJ provides schema methods that allow you to create tables, columns, indexes, and keys in a database. This section explains how to perform schema operations.

Types of schema objects

Table schema Methods in the Connection interface are used to access table properties. The schema is accessed directly using the TableSchema interface. See TableSchema interface.
Column schema Methods in the TableSchema interface are used to access column properties. The schema is accessed directly using the ColumnSchema interface. See ColumnSchema interface.
Index schema Methods in the TableSchema interface are used to access indexes. The schema is accessed directly using the IndexSchema interface. See IndexSchema interface.
Foreign key schema Methods in the Connection interface are used to access foreign keys. The schema is accessed directly using the ForeignKeySchema interface. See ForeignKeySchema interface.

Creating new tables, columns, indexes, and keys

All table, column, index, and key operations must be performed using the API, and only when the connected database is in schema creation mode.

To perform schema operations

Connect to an UltraLiteJ database.

This example assumes that the database is connected to the Connection, conn. For details on how to connect to an UltraLiteJ database, see Accessing an UltraLiteJ database store.
Put the Connection into schema creation mode using the following code:
conn.schemaCreateBegin();
Schema creation mode prohibits data operations from being made and locks out additional connections to the database.

Perform all table, column, index, and key operations.

The following procedures demonstrate how to create a new Employee table that contains an integer column named emp_number. The emp_number column is the primary index for the Employee table, and acts as a foreign key that references an integer column, access_number, in a Security table.

To create a new table Use the createTable method to define the name of the table and assign the result to a TableSchema:

TableSchema table_schema = conn.createTable("Employee");

Assigning the result to a TableSchema allows you to perform more detailed schema operations to the table. For more details, see TableSchema interface.

To add a column to a table Use the createColumn method to define the column name and type:

table_schema.createColumn("emp_number", Domain.INTEGER);

To assign a primary index to a column

Use the createPrimaryIndex method to create a new primary index on the table and assign the result to an IndexSchema:

IndexSchema index_schema = 
    table_schema.createPrimaryIndex("prime_keys");

Use the addColumn method to specify the primary index column and sort order:

index_schema.addColumn("emp_number", IndexSchema.ASCENDING);

To assign a foreign key to a column This procedure assumes that a Security table with an access_number integer column already exists in the database. If it does not exist, create it using the procedures listed above.

Use the createForeignKey method to specify the tables involved in the foreign key creation:

ForeignKeySchema foreign_key_schema = conn.createForeignKey(
    "Employee",
    "Security",
    "fk_emp_to_sec"
);

The first parameter references the table to contain the foreign key; the second parameter references the table containing the column to which the foreign key refers.

Use the addColumnReference method to specify the two columns involved in the foreign key creation:

foreign_key_schema.addColumnReference("emp_number", "access_number");

The first parameter references the column name in the first table to become a foreign key; the second parameter references the column name in the second table to which the foreign key refers.

Put the Connection out of schema creation mode:
conn.schemaCreateComplete();