org.opensubsystems.inventory.persist.db
Class IdentificationDatabaseSchema

java.lang.Object
  org.opensubsystems.core.persist.db.DatabaseSchemaImpl
      org.opensubsystems.core.persist.db.ModifiableDatabaseSchemaImpl
          org.opensubsystems.patterns.listdata.persist.db.impl.ModifiableListDatabaseSchemaImpl
              org.opensubsystems.inventory.persist.db.IdentificationDatabaseSchema

All Implemented Interfaces:

org.opensubsystems.core.persist.db.DatabaseOperations, org.opensubsystems.core.persist.db.DatabaseSchema, org.opensubsystems.core.persist.db.ModifiableDatabaseSchema, org.opensubsystems.patterns.listdata.persist.db.ListDatabaseSchema

Direct Known Subclasses:

DB2IdentificationDatabaseSchema, HsqlDBIdentificationDatabaseSchema, MSSQLIdentificationDatabaseSchema, MySQLIdentificationDatabaseSchema, OracleIdentificationDatabaseSchema, PostgreSQLIdentificationDatabaseSchema, SapDBIdentificationDatabaseSchema, SybaseIdentificationDatabaseSchema

public abstract class IdentificationDatabaseSchema
extends org.opensubsystems.patterns.listdata.persist.db.impl.ModifiableListDatabaseSchemaImpl

Database specific operations related to persistence of identifications.

Version:

$Id: IdentificationDatabaseSchema.java,v 1.18 2007/01/12 06:19:45 bastafidli Exp $

Author:

Miro Halas

Code reviewer:

TODO: Review this code

Code reviewed:

Field Summary

static int	IDENTIFICATION_COMMENTS_MAXLENGTH Maximal length for comments.
static int	IDENTIFICATION_EXTERNALNUMBER_MAXLENGTH Maximal length of external number.
static int	IDENTIFICATION_INTERNALNUMBER_MAXLENGTH Maximal length of internal number.
static int[]	IDENTIFICATION_MANDATORY_RETRIEVE_COLUMNS Columns which always have to be retrieved from the database
static java.lang.String	IDENTIFICATION_SCHEMA_NAME Name identifies this schema in the database.
static int	IDENTIFICATION_SCHEMA_VERSION Version of this schema in the database.
static int[]	IDENTIFICATION_SORT_COLUMNS Static variable for array of all columns codes that can be used for sorting.

Fields inherited from class org.opensubsystems.patterns.listdata.persist.db.impl.ModifiableListDatabaseSchemaImpl

m_mapFilterableColumns, m_mapListableTableNames, m_mapMandatoryRetrieveColumns, m_mapSortableColumns

Fields inherited from class org.opensubsystems.core.persist.db.ModifiableDatabaseSchemaImpl

m_mapModifiableTableNames

Fields inherited from class org.opensubsystems.core.persist.db.DatabaseSchemaImpl

DATABASE_SCHEMA_PREFIX, DATABASE_SCHEMA_PREFIX_DEFAULT, m_arrDependentSchemas, m_bIsInDomain, m_iSchemaVersion, m_strSchemaName, NL

Fields inherited from interface org.opensubsystems.core.persist.db.DatabaseOperations

DBOP_DELETE, DBOP_INSERT, DBOP_SELECT, DBOP_UPDATE

Constructor Summary

IdentificationDatabaseSchema()
Default constructor.

Method Summary

void	createListIndexes(java.sql.Connection cntDBConnection) Method creates specified LST indexes used for speeding up retrieving data into the list.
int	delete(java.sql.Connection dbConnection, int iId, int iDomainId) Delete identification with specified ID.
java.lang.StringBuffer	getColumns(boolean specific, int[] columns, java.lang.Object[] prefixes, java.lang.Object[] postfixes, java.lang.StringBuffer buffer)
java.lang.String	getDeleteIdentificationById() Get query to delete particular identification.
protected java.util.Map	getExistingSerialNumbersData(java.sql.Connection dbConnection, java.util.List lstSerialNumbers, int iInventoryId, int iDomainId) Get list of existing IDs and SERIAL NUMBERs within the DB
java.lang.String	getInsertIdentification() Method returns simple insert identification query.
abstract java.lang.String	getInsertIdentificationAndFetchGeneratedValues() Get query that inserts a identification to the database and fetches database generated values such as the generated id and creation timestamp
java.lang.String	getSelectAllDataForInventory(int[] columns) Get query to select all specified columns belonging to specified inventory from identification table.
protected java.lang.String	getSelectExternalNumberExist(Identification data) Get sql query to check external number, inventory, manufacturer is not used yet
java.lang.String	getSelectForItem(int[] columns) Get query to select identifications for particular Item ID.
java.lang.String	getSelectIdentificationById(int[] columns) Get query to select identification by ID.
java.lang.String	getSelectIdentificationByInternalNumber(int[] columns) Get query to select identification by internal number and .
java.lang.String	getSelectInternalNumberForExternalNumber(int[] columns) Get query to select internal number for specified inventory and external number.
java.lang.String	getSelectInternalNumberForNotUniquelyTracked() Get query to select internal number for not uniquely tracked
java.lang.String	getSelectInternalNumberUsed() Check existence of internal number for specified inventory and domain
java.lang.String	getSelectLastCreated() Get sql statemnet used in function getNextIdentification to retrieve last created identification value
protected java.lang.String	getSelectNextInternalNumberOrderedNumbersSql() Get sql statemnet used in function getNextIdentification to retrieve ordered list of identifications
java.lang.String	getSetAllRetired() Get query to set up all retired.
java.lang.String	getUpdateIdentification() Method returns simple update identification query.
abstract java.lang.String	getUpdateIdentificationAndFetchGeneratedValues() Get query that updates identification in the database and fetches database generated values such as the updated modification timestamp
void	handleSQLException(java.sql.SQLException exc, java.sql.Connection dbConnection, int iOperationType, int iDataType, java.lang.Object data)
protected void	setRetired(java.sql.Connection dbConnection, java.util.List lstIDs, int iDomainId) Set up retired flag for all specified identification IDs

Methods inherited from class org.opensubsystems.patterns.listdata.persist.db.impl.ModifiableListDatabaseSchemaImpl

getFilterbleColumns, getMandatoryRetrieveColumns, getSelectList, getSortableColumns

Methods inherited from class org.opensubsystems.core.persist.db.ModifiableDatabaseSchemaImpl

deleteRelatedData, getModifiableTableNames

Methods inherited from class org.opensubsystems.core.persist.db.DatabaseSchemaImpl

getDependentSchemas, getJoinFromWhere, getName, getOwnerColumnNames, getSchemaPrefix, getVersion, isExistingIndex, isInDomain, upgrade, upgradeView

Methods inherited from class java.lang.Object

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

Methods inherited from interface org.opensubsystems.patterns.listdata.persist.db.ListDatabaseSchema

getJoinFromWhere, isExistingIndex

Methods inherited from interface org.opensubsystems.core.persist.db.DatabaseSchema

create, getDependentSchemas, getName, getVersion, isInDomain, upgrade

Field Detail

IDENTIFICATION_SCHEMA_NAME

public static final java.lang.String IDENTIFICATION_SCHEMA_NAME

Name identifies this schema in the database.

See Also:

Constant Field Values

IDENTIFICATION_SCHEMA_VERSION

public static final int IDENTIFICATION_SCHEMA_VERSION

Version of this schema in the database.

See Also:

Constant Field Values

IDENTIFICATION_MANDATORY_RETRIEVE_COLUMNS

public static final int[] IDENTIFICATION_MANDATORY_RETRIEVE_COLUMNS

Columns which always have to be retrieved from the database

IDENTIFICATION_SORT_COLUMNS

public static final int[] IDENTIFICATION_SORT_COLUMNS

Static variable for array of all columns codes that can be used for sorting.

IDENTIFICATION_EXTERNALNUMBER_MAXLENGTH

public static final int IDENTIFICATION_EXTERNALNUMBER_MAXLENGTH

Maximal length of external number.

See Also:

Constant Field Values

IDENTIFICATION_INTERNALNUMBER_MAXLENGTH

public static final int IDENTIFICATION_INTERNALNUMBER_MAXLENGTH

Maximal length of internal number.

See Also:

Constant Field Values

IDENTIFICATION_COMMENTS_MAXLENGTH

public static final int IDENTIFICATION_COMMENTS_MAXLENGTH

Maximal length for comments.

See Also:

Constant Field Values

Constructor Detail

IdentificationDatabaseSchema

public IdentificationDatabaseSchema()
                             throws org.opensubsystems.core.error.OSSException

Default constructor.

Throws:

org.opensubsystems.core.error.OSSException - - error occured.

Method Detail

getInsertIdentificationAndFetchGeneratedValues

public abstract java.lang.String getInsertIdentificationAndFetchGeneratedValues()
                                                                         throws org.opensubsystems.core.error.OSSException

Get query that inserts a identification to the database and fetches database generated values such as the generated id and creation timestamp

Returns:

String - query for simple insert or stored procedure call

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getUpdateIdentificationAndFetchGeneratedValues

public abstract java.lang.String getUpdateIdentificationAndFetchGeneratedValues()
                                                                         throws org.opensubsystems.core.error.OSSException

Get query that updates identification in the database and fetches database generated values such as the updated modification timestamp

Returns:

String - query for simple update or stored procedure call

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getInsertIdentification

public java.lang.String getInsertIdentification()
                                         throws org.opensubsystems.core.error.OSSException

Method returns simple insert identification query. This method is common for all databases and can be overwritten for each specific database schema.

Returns:

String - simple insert identification query

Throws:

org.opensubsystems.core.error.OSSException - - exception during getting query

getUpdateIdentification

public java.lang.String getUpdateIdentification()
                                         throws org.opensubsystems.core.error.OSSException

Method returns simple update identification query. This method is common for all databases and can be overwritten for each specific database schema.

Returns:

String - simple update identification query

Throws:

org.opensubsystems.core.error.OSSException - - exception during getting query

createListIndexes

public void createListIndexes(java.sql.Connection cntDBConnection)
                       throws java.sql.SQLException,
                              org.opensubsystems.core.error.OSSException

Method creates specified LST indexes used for speeding up retrieving data into the list.

Parameters:

cntDBConnection - - valid connection to database

Throws:

java.sql.SQLException - - problem creating the database schema

org.opensubsystems.core.error.OSSException - - problem creating the database schema

getSelectIdentificationById

public java.lang.String getSelectIdentificationById(int[] columns)
                                             throws org.opensubsystems.core.error.OSSException

Get query to select identification by ID.

Parameters:

columns - - columns to retrieve

Returns:

String - query

Throws:

org.opensubsystems.core.error.OSSException - - and error has occured

getSelectInternalNumberForNotUniquelyTracked

public java.lang.String getSelectInternalNumberForNotUniquelyTracked()

Get query to select internal number for not uniquely tracked

Returns:

String - query

getSelectIdentificationByInternalNumber

public java.lang.String getSelectIdentificationByInternalNumber(int[] columns)
                                                         throws org.opensubsystems.core.error.OSSException

Get query to select identification by internal number and .

Parameters:

columns - - list of columns to retrieve

Returns:

String - query

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getSelectInternalNumberForExternalNumber

public java.lang.String getSelectInternalNumberForExternalNumber(int[] columns)
                                                          throws org.opensubsystems.core.error.OSSException

Get query to select internal number for specified inventory and external number.

Parameters:

columns - - list of columns to retrieve

Returns:

String - query

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getSetAllRetired

public java.lang.String getSetAllRetired()

Get query to set up all retired.

Returns:

String - query

getSelectForItem

public java.lang.String getSelectForItem(int[] columns)
                                  throws org.opensubsystems.core.error.OSSException

Get query to select identifications for particular Item ID.

Parameters:

columns - - list of columns to retrieve

Returns:

String - query

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getSelectAllDataForInventory

public java.lang.String getSelectAllDataForInventory(int[] columns)
                                              throws org.opensubsystems.core.error.OSSException

Get query to select all specified columns belonging to specified inventory from identification table.

Parameters:

columns - - list of columns to retrieve

Returns:

String - query

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getDeleteIdentificationById

public java.lang.String getDeleteIdentificationById()

Get query to delete particular identification.

Returns:

String - query

getSelectNextInternalNumberOrderedNumbersSql

protected java.lang.String getSelectNextInternalNumberOrderedNumbersSql()

Get sql statemnet used in function getNextIdentification to retrieve ordered list of identifications

Returns:

String with sql statement

getSelectLastCreated

public java.lang.String getSelectLastCreated()

Get sql statemnet used in function getNextIdentification to retrieve last created identification value

Returns:

String with sql statement

getSelectInternalNumberUsed

public java.lang.String getSelectInternalNumberUsed()

Check existence of internal number for specified inventory and domain

Returns:

String with sql statement

getColumns

public java.lang.StringBuffer getColumns(boolean specific,
                                         int[] columns,
                                         java.lang.Object[] prefixes,
                                         java.lang.Object[] postfixes,
                                         java.lang.StringBuffer buffer)
                                  throws org.opensubsystems.core.error.OSSException

Throws:

org.opensubsystems.core.error.OSSException

delete

public int delete(java.sql.Connection dbConnection,
                  int iId,
                  int iDomainId)
           throws org.opensubsystems.core.error.OSSException,
                  java.sql.SQLException

Delete identification with specified ID.

Parameters:

dbConnection - - database connection

iId - - ID of the Identification that have to be deleted

iDomainId - - domain ID the deleted data belongs to

Returns:

int - number of deleted identification

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

java.sql.SQLException - - an error has occured

handleSQLException

public void handleSQLException(java.sql.SQLException exc,
                               java.sql.Connection dbConnection,
                               int iOperationType,
                               int iDataType,
                               java.lang.Object data)
                        throws org.opensubsystems.core.error.OSSException

Specified by:

handleSQLException in interface org.opensubsystems.core.persist.db.DatabaseSchema

Overrides:

handleSQLException in class org.opensubsystems.core.persist.db.ModifiableDatabaseSchemaImpl

Throws:

org.opensubsystems.core.error.OSSException

getSelectExternalNumberExist

protected java.lang.String getSelectExternalNumberExist(Identification data)

Get sql query to check external number, inventory, manufacturer is not used yet

Parameters:

data - - Identification data object

Returns:

String - sql query

setRetired

protected void setRetired(java.sql.Connection dbConnection,
                          java.util.List lstIDs,
                          int iDomainId)
                   throws org.opensubsystems.core.error.OSSException,
                          java.sql.SQLException

Set up retired flag for all specified identification IDs

Parameters:

dbConnection - - database connection

lstIDs - - list of strings with maximum 1000 ids in string

iDomainId - - domain ID the updated data belongs to

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

java.sql.SQLException - - an error has occured

getExistingSerialNumbersData

protected java.util.Map getExistingSerialNumbersData(java.sql.Connection dbConnection,
                                                     java.util.List lstSerialNumbers,
                                                     int iInventoryId,
                                                     int iDomainId)
                                              throws org.opensubsystems.core.error.OSSException,
                                                     java.sql.SQLException

Get list of existing IDs and SERIAL NUMBERs within the DB

Parameters:

dbConnection - - database connection

lstSerialNumbers - - list of strings with maximum serial numbers in string

iInventoryId - - inventory ID the data belong to

iDomainId - - domain ID the data belong to

Returns:

Map - hash map of existing IDs and SERIAL NUMBERs

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

java.sql.SQLException - - an error has occured