Example: Modifying table definitions

This example demonstrates how to change table definitions. In this scenario, an Invoice table is modified to expand a column length from 50 characters to 100 characters.

package ianywhere.ultralitej.demo;
import ianywhere.ultralitej.*;
/** Reorganize the Invoice table to have a name with an increased size
 *
 * <p>This shows a possible strategy which can be used to reorganize
 * tables, since UltraLiteJ has no table-altering API.
 * <p>The (contrived) example expands the name column to 100 characters.
 *
 */
public class Reorg
{
    /**
     * mainline for program.
     *
     * @param args command-line arguments
     *
     */
    public static void main
        ( String[] args )
    {
        try {
            Configuration config = DatabaseManager.createConfigurationFile( "Sales.ulj" );
            Connection conn = DatabaseManager.connect( config );
            createNewInvoiceTable( conn );
            copyInvoicesToNewTable( conn );
            deleteOldInvoicesTable( conn );
            renameNewInvoicesTable( conn );
            enableSynchronizationForNewTable( conn );
            conn.release();
        } catch( ULjException exc ) {
            Demo.displayExceptionFull( exc );
        }
    }

    private static void createNewInvoiceTable( Connection conn )
        throws ULjException
    {
        conn.schemaCreateBegin();
        TableSchema table_schema = conn.createTable( "NewInvoice" );
        table_schema.createColumn( "inv_no", Domain.INTEGER );
        table_schema.createColumn( "name", Domain.VARCHAR, 100 );   // was 50 in old table
        table_schema.createColumn( "date", Domain.DATE );
        IndexSchema index_schema = table_schema.createPrimaryIndex( "prime_keys" );
        index_schema.addColumn( "inv_no", IndexSchema.ASCENDING );
        table_schema.setNoSync( true );         // we don't want to sync inserts yet
        conn.schemaCreateComplete();
    }

    private static void copyInvoicesToNewTable( Connection conn )
        throws ULjException
    {
        PreparedStatement inserter = conn.prepareStatement(
            "INSERT INTO NewInvoice( inv_no, name, \"date\" ) VALUES( ?, ?, ? )"
        );
        int ordinal_inv_no = 1;
        int ordinal_inv_name = 2;
        int ordinal_inv_date = 3;

        PreparedStatement stmt = conn.prepareStatement(
            "SELECT inv_no, name, \"date\" FROM Invoice"
        );
        ResultSet cursor = stmt.executeQuery();
        for( ; cursor.next(); ) {
            inserter.set( ordinal_inv_no, cursor.getInt( ordinal_inv_no ) );
            inserter.set( ordinal_inv_name, cursor.getString( ordinal_inv_name ) );
            inserter.set( ordinal_inv_date, cursor.getString( ordinal_inv_date ) );
            inserter.execute();
            // in memory-low conditions, we could delete the row from the old table (specify
            // stopSynchronizationDelete, so these deletes would not synchronize.
        }
        inserter.close();
        cursor.close();
        stmt.close
        conn.commit();
    }

    private static void deleteOldInvoicesTable( Connection conn )
        throws ULjException
    {
        conn.dropTable( "Invoice" );
    }

    private static void renameNewInvoicesTable( Connection conn )
        throws ULjException
    {
        conn.renameTable( "NewInvoice", "Invoice" );
    }

    private static void enableSynchronizationForNewTable( Connection conn )
        throws ULjException
    {
        conn.enableSynchronization( "Invoice" );
    }
}