Performing schema operations

The UltraLiteJ API contains schema methods that allow you to create tables, columns, indexes, and keys in an UltraLiteJ 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. These operations can only be performed 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 database into schema creation mode using the following code:
conn.schemaCreateBegin();
Schema creation mode prohibits data operations 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 acts as the primary index for the Employee table and also acts as a foreign key, referencing an integer column named access_number in a Security table.

To create a new table Use the createTable method on the Connection 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.

To add a column to a table: Use the createColumn method on the TableSchema 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 on the TableSchema and assign the result to an IndexSchema:

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

Use the addColumn method on the IndexSchema 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 exists in the database.

Use the createForeignKey method on the Connection to specify the tables involved:

ForeignKeySchema foreign_key_schema = conn.createForeignKey(
    "Employee",
    "Department",
    "fk_emp_to_dept"
);

Use the addColumnReference method to specify the two columns involved:

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

Put the database out of schema creation mode using the following code:
conn.schemaCreateComplete();