com.aspc.remote.database

Class TableUtil

java.lang.Object

com.aspc.remote.database.TableUtil

public final class TableUtil
extends java.lang.Object

CreateTables NB: Sybase has a limit of 30 characters for an index or table name. TODO: If an key table doesn't exist then we should make sure that when we create the key table the index version string is cleared. If I drop the index table it should gracefully recreate the table and reindex the fields.
THREAD MODE: MULTI-THREADED

Since:

February 13, 2001, 3:20 PM

Author:

Nigel Leck

Field Summary

Fields

Modifier and Type	Field and Description
static java.lang.String	FIELD_MODIFIER_NULL null
static java.lang.String	FUNCTION_LENGTH The length case function ( different for each database)
static java.lang.String	FUNCTION_UPPER The upper case function ( different for each database)

Method Summary

Modifier and Type	Method and Description
void	addColumn(java.lang.String pTableName, java.lang.String pColumnName, java.lang.String type) Add a column to a table.
void	alterColumn(java.lang.String pTableName, java.lang.String pColumnName, java.lang.String type, boolean nullable) Add a column to a table.
void	clearCache() Clears all cached information about this database.
static void	clearCacheAll() Clears all cached information about ALL databases.
java.lang.String	convertLong(java.lang.String column)
java.lang.String	convertNull(java.lang.String column, int value) ISNULL function
java.lang.String	convertNull(java.lang.String column, java.lang.String value)
void	createdTable(java.lang.String tableName) If a table is created then clear what was known about the table.
void	createIndex(java.lang.String tableName, java.lang.String logicalIndexName, boolean unique, java.lang.String columns) create an index on a table.
void	createTable(java.lang.String tableName, java.lang.String fieldList, java.lang.String primaryKey) create a table
boolean	doesColumnExist(java.lang.String tableName, java.lang.String columnName) Does the column exist ?
boolean	doesProcedureExist(java.lang.String procedure) Does this procedure exist ?
boolean	doesTableExist(java.lang.String tableName) Does the table exist ?
boolean	doesTableIndexExist(java.lang.String tableName, java.lang.String indexName) Does an index of this name exist on this table ?
void	dropAllIndexes(java.lang.String tableName) drop all the indexes for a table
void	dropColumn(java.lang.String table, java.lang.String column) Drop a column.
void	dropIndex(java.lang.String tableName, java.lang.String indexName) Drop an index for a table.
void	dropTable(java.lang.String tableName) Drop a table
void	dropZZTables() Drop temporary tables (starts with zz)
ColumnInfo	fetchColumn(java.lang.String tableName, java.lang.String columnName) get the column.
IndexInfo	fetchIndexInfo(java.lang.String tableName, java.lang.String indexName) fetch the index info
static TableUtil	find(DataBase dBase) Find the table utils for this database.
ColumnInfo	getColumn(java.lang.String tableName, java.lang.String columnName) get the column.
ColumnType	getColumnDataType(java.lang.String tableName, java.lang.String columnName) get the column type.
ColumnInfo[]	getColumns(java.lang.String tableName) Does the column exist ?
int	getColumnScale(java.lang.String tableName, java.lang.String columnName) The column scale
int	getColumnSize(java.lang.String tableName, java.lang.String columnName) The column size
java.lang.String	getColumnStdTypeName(java.lang.String tableName, java.lang.String columnName) get the column type.
int	getColumnType(java.lang.String tableName, java.lang.String columnName) get the column type.
DataBase	getDataBase() Get the database for this table utilities.
java.lang.String	getDoubleTypeName() get the double type.
java.lang.String	getFieldModifier(java.lang.String modifier) Get the field modifier for this database.
IndexInfo[]	getIndexes(java.lang.String tableName) List All tables names.
java.lang.String	getLongTypeName() get the long type.
java.lang.String	getSQLFunction(java.lang.String name) Get the function for this database.
java.lang.String	getTextTypeName() get the text type.
java.lang.String	isBlankClause(java.lang.String column) column IS NULL or blank ( only white space)
boolean	isColumnNullable(java.lang.String tableName, java.lang.String columnName) get the column type.
java.lang.String[]	listTables() List All tables names.
java.lang.String	mathTruncate(java.lang.String column) truncate a column value
int	maxClauses() The maximum clauses for this database type.
java.lang.String	notBlankClause(java.lang.String column) column IS NOT NULL or and not blank ( only white space)
void	renameColumn(java.lang.String tableName, java.lang.String oldName, java.lang.String newName, java.lang.String def) Rename a column
void	renameIndex(java.lang.String tableName, java.lang.String oldIndexName, java.lang.String newIndexName) Rename an index for a table.
void	renameTable(java.lang.String oldTableName, java.lang.String newTableName) Rename a table.
void	updateStats(java.lang.String tableName) Update stats

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

FUNCTION_UPPER

public static final java.lang.String FUNCTION_UPPER

The upper case function ( different for each database)

See Also:

Constant Field Values

FUNCTION_LENGTH

public static final java.lang.String FUNCTION_LENGTH

The length case function ( different for each database)

See Also:

Constant Field Values

FIELD_MODIFIER_NULL

public static final java.lang.String FIELD_MODIFIER_NULL

null

See Also:

Constant Field Values

Method Detail

maxClauses

@CheckReturnValue
public int maxClauses()

The maximum clauses for this database type.

Returns:

The number of clauses.

find

@CheckReturnValue
 @Nonnull
public static TableUtil find(@Nonnull
                                                         DataBase dBase)

Find the table utils for this database.

Parameters:

dBase - The database.

Returns:

The table utils.

clearCache

public void clearCache()

Clears all cached information about this database.

clearCacheAll

public static void clearCacheAll()

Clears all cached information about ALL databases.

doesProcedureExist

@CheckReturnValue
public boolean doesProcedureExist(@Nonnull
                                                    java.lang.String procedure)
                                             throws java.sql.SQLException

Does this procedure exist ?

Parameters:

procedure - the name

Returns:

true if exists

Throws:

java.sql.SQLException - a serious problem

doesTableExist

@CheckReturnValue
public boolean doesTableExist(@Nonnull
                                                java.lang.String tableName)
                                         throws java.sql.SQLException

Does the table exist ?

Parameters:

tableName - The table to check

Returns:

true if the table exists.

Throws:

java.sql.SQLException - A SQL exception

getColumnDataType

@CheckReturnValue
 @Nonnull
public ColumnType getColumnDataType(@Nonnull
                                                                java.lang.String tableName,
                                                                @Nonnull
                                                                java.lang.String columnName)
                                                         throws java.sql.SQLException

get the column type.

Parameters:

tableName - the table name

columnName - the column name

Returns:

the data type

Throws:

java.sql.SQLException - A SQL exception

getColumnType

@CheckReturnValue
public int getColumnType(@Nonnull
                                           java.lang.String tableName,
                                           @Nonnull
                                           java.lang.String columnName)
                                    throws java.sql.SQLException

get the column type.

Parameters:

tableName - the table name

columnName - the column name

Returns:

the type

Throws:

java.sql.SQLException - A SQL exception

getColumn

@CheckReturnValue
 @Nonnull
public ColumnInfo getColumn(@Nonnull
                                                        java.lang.String tableName,
                                                        @Nonnull
                                                        java.lang.String columnName)
                                                 throws java.sql.SQLException

get the column.

Parameters:

tableName - the table name

columnName - the column name

Returns:

the column

Throws:

java.sql.SQLException - A SQL exception

fetchColumn

@CheckReturnValue
 @Nullable
public ColumnInfo fetchColumn(@Nonnull
                                                           java.lang.String tableName,
                                                           @Nonnull
                                                           java.lang.String columnName)
                                                    throws java.sql.SQLException

get the column.

Parameters:

tableName - the table name

columnName - the column name

Returns:

the column

Throws:

java.sql.SQLException - A SQL exception

getColumnStdTypeName

@CheckReturnValue
 @Nonnull
public java.lang.String getColumnStdTypeName(@Nonnull
                                                                         java.lang.String tableName,
                                                                         @Nonnull
                                                                         java.lang.String columnName)
                                                                  throws java.sql.SQLException

get the column type.

Parameters:

tableName - the table name

columnName - the column name

Returns:

the type

Throws:

java.sql.SQLException - A SQL exception

isColumnNullable

@CheckReturnValue
public boolean isColumnNullable(@Nonnull
                                                  java.lang.String tableName,
                                                  @Nonnull
                                                  java.lang.String columnName)
                                           throws java.sql.SQLException

get the column type.

Parameters:

tableName - the table name

columnName - the column name

Returns:

the type

Throws:

java.sql.SQLException - A SQL exception

getColumnSize

@CheckReturnValue
public int getColumnSize(@Nonnull
                                           java.lang.String tableName,
                                           @Nonnull
                                           java.lang.String columnName)
                                    throws java.sql.SQLException

The column size

Parameters:

tableName - The table name

columnName - The column name

Returns:

the column size.

Throws:

java.sql.SQLException - A SQL exception

getColumnScale

@CheckReturnValue
public int getColumnScale(@Nonnull
                                            java.lang.String tableName,
                                            @Nonnull
                                            java.lang.String columnName)
                                     throws java.sql.SQLException

The column scale

Parameters:

tableName - The table name

columnName - The column name

Returns:

the column scale.

Throws:

java.sql.SQLException - A SQL exception

doesColumnExist

@CheckReturnValue
public boolean doesColumnExist(@Nonnull
                                                 java.lang.String tableName,
                                                 @Nonnull
                                                 java.lang.String columnName)
                                          throws java.sql.SQLException

Does the column exist ?

Parameters:

tableName - The table name

columnName - The column name

Returns:

true if the column exist.

Throws:

java.sql.SQLException - A SQL exception

fetchIndexInfo

@CheckReturnValue
 @Nullable
public IndexInfo fetchIndexInfo(@Nonnull
                                                             java.lang.String tableName,
                                                             @Nonnull
                                                             java.lang.String indexName)
                                                      throws java.sql.SQLException

fetch the index info

Parameters:

tableName - the table name

indexName - the index name

Returns:

null if the index doesn't exist

Throws:

java.sql.SQLException - a problem

getColumns

@CheckReturnValue
 @Nonnull
public ColumnInfo[] getColumns(@Nonnull
                                                           java.lang.String tableName)
                                                    throws java.sql.SQLException

Does the column exist ?

Parameters:

tableName - The table name

Returns:

true if the column exist.

Throws:

java.sql.SQLException - A SQL exception

doesTableIndexExist

@CheckReturnValue
public boolean doesTableIndexExist(@Nonnull
                                                     java.lang.String tableName,
                                                     @Nonnull
                                                     java.lang.String indexName)
                                              throws java.sql.SQLException

Does an index of this name exist on this table ?

Parameters:

tableName - the table name

indexName - the index name

Returns:

true if the index exists

Throws:

java.sql.SQLException - A SQL exception

getDataBase

@CheckReturnValue
 @Nonnull
public DataBase getDataBase()

Get the database for this table utilities.

Returns:

the database.

convertNull

@CheckReturnValue
 @Nonnull
public java.lang.String convertNull(@Nonnull
                                                                java.lang.String column,
                                                                @Nonnull
                                                                java.lang.String value)

Parameters:

column - the column

value - the default value

Returns:

the null clause

notBlankClause

@CheckReturnValue
 @Nonnull
public java.lang.String notBlankClause(@Nonnull
                                                                   java.lang.String column)

column IS NOT NULL or and not blank ( only white space)

Parameters:

column - the column

Returns:

the null clause

isBlankClause

@CheckReturnValue
 @Nonnull
public java.lang.String isBlankClause(@Nonnull
                                                                  java.lang.String column)

column IS NULL or blank ( only white space)

Parameters:

column - A text/varchar column

Returns:

the null clause

convertNull

@CheckReturnValue
 @Nonnull
public java.lang.String convertNull(@Nonnull
                                                                java.lang.String column,
                                                                int value)

ISNULL function

Parameters:

column - the column

value - the default value

Returns:

the null clause

mathTruncate

@CheckReturnValue
 @Nonnull
public java.lang.String mathTruncate(@Nonnull
                                                                 java.lang.String column)

truncate a column value

Parameters:

column - the column

Returns:

the truncate function

convertLong

@CheckReturnValue
 @Nonnull
public java.lang.String convertLong(@Nonnull
                                                                java.lang.String column)

Parameters:

column - the column

Returns:

the long convert function

getSQLFunction

@CheckReturnValue
 @Nonnull
public java.lang.String getSQLFunction(@Nonnull
                                                                   java.lang.String name)

Get the function for this database.

Parameters:

name - our function name

Returns:

the SQL function name.

getFieldModifier

@CheckReturnValue
 @Nonnull
public java.lang.String getFieldModifier(@Nonnull
                                                                     java.lang.String modifier)

Get the field modifier for this database.

Parameters:

modifier - the modifier

Returns:

the sql modifier name.

listTables

@CheckReturnValue
 @Nonnull
public java.lang.String[] listTables()
                                                          throws java.sql.SQLException

List All tables names. We don't return the ConcurrentHashMap as it caused too many problems with other programs creating an enumeration of the tables which then changes in the background.

Returns:

A list table.

Throws:

java.sql.SQLException - A SQL Exception

getIndexes

@CheckReturnValue
 @Nonnull
public IndexInfo[] getIndexes(@Nonnull
                                                          java.lang.String tableName)
                                                   throws java.sql.SQLException

List All tables names. We don't return the ConcurrentHashMap as it caused too many problems with other programs creating an enumeration of the tables which then changes in the background.

Parameters:

tableName - The table to check

Returns:

The list of indexes for the table.

Throws:

java.sql.SQLException - A SQL exception

alterColumn

public void alterColumn(@Nonnull
                        java.lang.String pTableName,
                        @Nonnull
                        java.lang.String pColumnName,
                        @Nonnull
                        java.lang.String type,
                        boolean nullable)
                 throws java.sql.SQLException

Add a column to a table.

Parameters:

pTableName - The table to add the column.

pColumnName - The column to add.

type - The column type.

nullable - the field is nullable

Throws:

java.sql.SQLException - A SQL exception

addColumn

public void addColumn(@Nonnull
                      java.lang.String pTableName,
                      @Nonnull
                      java.lang.String pColumnName,
                      @Nonnull
                      java.lang.String type)
               throws java.sql.SQLException

Add a column to a table.

Parameters:

pTableName - The table to add the column.

pColumnName - The column to add.

type - The column type.

Throws:

java.sql.SQLException - A SQL exception

updateStats

public void updateStats(@Nonnull
                        java.lang.String tableName)
                 throws java.lang.Exception

Update stats

Parameters:

tableName - The table name

Throws:

java.lang.Exception - A serious problem

renameTable

public void renameTable(@Nonnull
                        java.lang.String oldTableName,
                        @Nonnull
                        java.lang.String newTableName)
                 throws java.sql.SQLException

Rename a table.

Parameters:

oldTableName - The old table.

newTableName - The new table

Throws:

java.sql.SQLException - A SQL exception

dropColumn

public void dropColumn(@Nonnull
                       java.lang.String table,
                       @Nonnull
                       java.lang.String column)
                throws java.sql.SQLException

Drop a column.

Parameters:

table - The table to drop the column from

column - The column to drop

Throws:

java.sql.SQLException - A serious problem

renameColumn

public void renameColumn(@Nonnull
                         java.lang.String tableName,
                         @Nonnull
                         java.lang.String oldName,
                         @Nonnull
                         java.lang.String newName,
                         @Nonnull
                         java.lang.String def)
                  throws java.lang.Exception

Rename a column

Parameters:

tableName - The table to rename the column.

oldName - The old column name

newName - The new column name

def - The def

Throws:

java.lang.Exception - A serious problem

createTable

public void createTable(@Nonnull
                        java.lang.String tableName,
                        @Nonnull
                        java.lang.String fieldList,
                        @Nullable
                        java.lang.String primaryKey)
                 throws java.sql.SQLException

create a table

Parameters:

fieldList - The field list

tableName - The table to create.

primaryKey - the primary key

Throws:

java.sql.SQLException - a SQL exception

createIndex

public void createIndex(@Nonnull
                        java.lang.String tableName,
                        @Nonnull
                        java.lang.String logicalIndexName,
                        boolean unique,
                        @Nonnull
                        java.lang.String columns)
                 throws java.sql.SQLException

create an index on a table.

Parameters:

tableName - The table name

logicalIndexName - the index name

unique - is it unique ?

columns - The columns

Throws:

java.sql.SQLException - A SQL exception

dropTable

public void dropTable(java.lang.String tableName)
               throws java.lang.Exception

Drop a table

Parameters:

tableName - The table name to drop

Throws:

java.lang.Exception - A serious problem

dropZZTables

public void dropZZTables()
                  throws java.lang.Exception

Drop temporary tables (starts with zz)

Throws:

java.lang.Exception - A serious problem

dropAllIndexes

public void dropAllIndexes(@Nonnull
                           java.lang.String tableName)
                    throws java.lang.Exception

drop all the indexes for a table

Parameters:

tableName - The table name

Throws:

java.lang.Exception - A serious problem

dropIndex

public void dropIndex(@Nonnull
                      java.lang.String tableName,
                      @Nonnull
                      java.lang.String indexName)
               throws java.sql.SQLException

Drop an index for a table.

Parameters:

tableName - The table name to drop the index from.

indexName - The index to drop

Throws:

java.sql.SQLException - A SQL exception

renameIndex

public void renameIndex(@Nonnull
                        java.lang.String tableName,
                        @Nonnull
                        java.lang.String oldIndexName,
                        @Nonnull
                        java.lang.String newIndexName)

Rename an index for a table.

Parameters:

tableName - The table name to drop the index from.

oldIndexName - The old index name

newIndexName - The new index name

getTextTypeName

@CheckReturnValue
public java.lang.String getTextTypeName()

get the text type.

Returns:

The text type.

getLongTypeName

@CheckReturnValue
public java.lang.String getLongTypeName()

get the long type.

Returns:

The long type.

getDoubleTypeName

@CheckReturnValue
public java.lang.String getDoubleTypeName()

get the double type.

Returns:

The double type.

createdTable

public void createdTable(@Nonnull
                         java.lang.String tableName)
                  throws java.sql.SQLException

If a table is created then clear what was known about the table.

Parameters:

tableName - the table is created.

Throws:

java.sql.SQLException - A SQL exception