org.opensubsystems.inventory.persist.db
Class ItemDatabaseSchema

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.ItemDatabaseSchema

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:

DB2ItemDatabaseSchema, HsqlDBItemDatabaseSchema, MSSQLItemDatabaseSchema, MySQLItemDatabaseSchema, OracleItemDatabaseSchema, PostgreSQLItemDatabaseSchema, SapDBItemDatabaseSchema, SybaseItemDatabaseSchema

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

Database specific operations related to persistence of items.

Version:

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

Author:

Martin Cerba

Code reviewer:

TODO: Review this code

Code reviewed:

Field Summary

static int[]	ALL_ITEM_CATEGORY_MAP_COLUMNS Static variable for array of all User-Role Map columns codes.
static int	COL_MAP_CATEGORY_ID Code for table column for join table.
static int	COL_MAP_ITEM_ID Code for table column for join table.
static int	ITEM_DESCRIPTION_MAXLENGTH Maximal length of item description.
static int[]	ITEM_MANDATORY_RETRIEVE_COLUMNS Columns which always have to be retrieved from the database
static int	ITEM_NAME_MAXLENGTH Maximal length of item name.
static int	ITEM_NAMESTEM_MAXLENGTH Maximal length of item name stem.
static java.lang.String	ITEM_SCHEMA_NAME Name identifies this schema in the database.
static int	ITEM_SCHEMA_VERSION Version of this schema in the database.
static int	ITEM_SIZE_MAXLENGTH Maximal length of item name size.
static int[]	ITEM_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

ItemDatabaseSchema()
Default constructor.

Method Summary

void	createListIndexes(java.sql.Connection cntDBConnection) Method creates specified LST indexes used for speeding up retrieving data into the list.
java.lang.String	getChangeAvailableCountSQL() Get SQL to available count.
java.lang.String	getChangeCountsSQL() Get SQL to change both available and on hold counts.
java.lang.String	getChangeOnHoldCountSQL(boolean bOnlyIfAvailable) Get SQL to change both available and on hold counts.
protected abstract java.lang.String	getChangeShippedAndAvailableCountSafelySQL() Method returns SQL statement for getChangeShippedAndAvailableCountSafely.
java.lang.String	getChangeShippedAndAvailableCountSQL() Get SQL to change shipped and available counts.
java.lang.StringBuffer	getColumns(boolean specific, int[] columns, java.lang.Object[] prefixes, java.lang.Object[] postfixes, java.lang.StringBuffer buffer)
java.lang.String	getCommonPartSelectCategory() Get common part query to select main category and subcategory
java.lang.String	getDeleteItemById() Get query to delete particular item.
java.lang.String	getDeleteItemCategoryMap() Method to get sql for deleting record from item - category map table
java.lang.String	getInsertItem() Method returns simple insert item query.
abstract java.lang.String	getInsertItemAndFetchGeneratedValues() Get query that inserts a Item to the database and fetches database generated values such as the generated id and creation timestamp
java.lang.String	getInsertItemCategoryMap() Method to get sql for inserting record into item - category map table
java.lang.String[]	getJoinFromWhere(java.util.List conditions, int[] columns)
java.lang.String	getResetCounts() Method to get sql for reset counts
java.lang.String	getSelectCategories() Get query to delete particular item.
java.lang.String	getSelectEmptyCategories(int[] arrIds) Method to get sql for empty category IDs
java.lang.String	getSelectIdsInCategory() Get query to select Ids in category
java.lang.String	getSelectItemById(int[] columns) Get query to select item by ID.
java.lang.String	getSelectItemByInventoryAndName(int[] columns) Method returning item by inventory ID and name.
java.lang.String	getToolsInExceptionReport() Method to get Report query
abstract java.lang.String	getUpdateItemAndFetchGeneratedValues() Get query that updates Item in the database and fetches database generated values such as the updated modification timestamp
protected void	handleInsertItemTypeCategoryMappingException(java.sql.SQLException sqlExc) Common exception handler for insert of item category mapping.
void	handleSQLException(java.sql.SQLException exc, java.sql.Connection dbConnection, int iOperationType, int iDataType, java.lang.Object data)
boolean	insertItemCategories(java.sql.Connection dbConnection, int iItemId, int[] arrCategoryIds) Method that will insert more records into item - category map table and returns sucess flag.
boolean	insertItemCategoryMap(java.sql.Connection dbConnection, int iItemId, int iCategoryId) Method that will insert record into item - category map table and returns sucess flag.

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, 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

isExistingIndex

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

create, getDependentSchemas, getName, getVersion, isInDomain, upgrade

Field Detail

ITEM_SCHEMA_NAME

public static final java.lang.String ITEM_SCHEMA_NAME

Name identifies this schema in the database.

See Also:

Constant Field Values

ITEM_SCHEMA_VERSION

public static final int ITEM_SCHEMA_VERSION

Version of this schema in the database.

See Also:

Constant Field Values

ITEM_MANDATORY_RETRIEVE_COLUMNS

public static final int[] ITEM_MANDATORY_RETRIEVE_COLUMNS

Columns which always have to be retrieved from the database

ITEM_SORT_COLUMNS

public static final int[] ITEM_SORT_COLUMNS

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

COL_MAP_ITEM_ID

public static final int COL_MAP_ITEM_ID

Code for table column for join table. This table is not user visible and therefore this is here in database schema instead of in data class.

See Also:

Constant Field Values

COL_MAP_CATEGORY_ID

public static final int COL_MAP_CATEGORY_ID

Code for table column for join table. This table is not user visible and therefore this is here in database schema instead of in data class.

See Also:

Constant Field Values

ALL_ITEM_CATEGORY_MAP_COLUMNS

public static final int[] ALL_ITEM_CATEGORY_MAP_COLUMNS

Static variable for array of all User-Role Map columns codes.

ITEM_NAME_MAXLENGTH

public static final int ITEM_NAME_MAXLENGTH

Maximal length of item name.

See Also:

Constant Field Values

ITEM_DESCRIPTION_MAXLENGTH

public static final int ITEM_DESCRIPTION_MAXLENGTH

Maximal length of item description.

See Also:

Constant Field Values

ITEM_NAMESTEM_MAXLENGTH

public static final int ITEM_NAMESTEM_MAXLENGTH

Maximal length of item name stem.

See Also:

Constant Field Values

ITEM_SIZE_MAXLENGTH

public static final int ITEM_SIZE_MAXLENGTH

Maximal length of item name size.

See Also:

Constant Field Values

Constructor Detail

ItemDatabaseSchema

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

Default constructor.

Throws:

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

Method Detail

getInsertItemAndFetchGeneratedValues

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

Get query that inserts a Item 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

getUpdateItemAndFetchGeneratedValues

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

Get query that updates Item 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

getInsertItem

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

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

Returns:

String - simple insert item 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

getSelectItemById

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

Get query to select item by ID.

Parameters:

columns - - columns to retrieve

Returns:

String - query

Throws:

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

getDeleteItemById

public java.lang.String getDeleteItemById()

Get query to delete particular item.

Returns:

String - query

getSelectCategories

public java.lang.String getSelectCategories()

Get query to delete particular item.

Returns:

String - query

getCommonPartSelectCategory

public java.lang.String getCommonPartSelectCategory()

Get common part query to select main category and subcategory

Returns:

String - query

getSelectIdsInCategory

public java.lang.String getSelectIdsInCategory()

Get query to select Ids in category

Returns:

String - query

getChangeShippedAndAvailableCountSafelySQL

protected abstract java.lang.String getChangeShippedAndAvailableCountSafelySQL()
                                                                        throws org.opensubsystems.core.error.OSSException

Method returns SQL statement for getChangeShippedAndAvailableCountSafely. This statement can be db dependent. TODO: For Miro: This is not better description ;-) How should I know if to call getChangeShippedAndAvailableCountSecure or just getChangeShippedAndAvailableCount. It would be nice to explain the difference or better the purpose of this method.

Returns:

sql statement string

Throws:

org.opensubsystems.core.error.OSSException - - error in function

getChangeCountsSQL

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

Get SQL to change both available and on hold counts.

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during select

getChangeOnHoldCountSQL

public java.lang.String getChangeOnHoldCountSQL(boolean bOnlyIfAvailable)
                                         throws org.opensubsystems.core.error.OSSException

Get SQL to change both available and on hold counts.

Parameters:

bOnlyIfAvailable - - change it only if there is enough available items

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during select

getChangeShippedAndAvailableCountSQL

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

Get SQL to change shipped and available counts.

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during select

getChangeAvailableCountSQL

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

Get SQL to available count.

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during select

getSelectItemByInventoryAndName

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

Method returning item by inventory ID and name.

Parameters:

columns - - list of specific columns

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during select

getResetCounts

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

Method to get sql for reset counts

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during select

getToolsInExceptionReport

public java.lang.String getToolsInExceptionReport()

Method to get Report query

Returns:

String - Report query

getSelectEmptyCategories

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

Method to get sql for empty category IDs

Parameters:

arrIds - - category Ids or null if all categories have to be selected

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during select

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

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

handleInsertItemTypeCategoryMappingException

protected void handleInsertItemTypeCategoryMappingException(java.sql.SQLException sqlExc)
                                                     throws org.opensubsystems.core.error.OSSException

Common exception handler for insert of item category mapping.

Parameters:

sqlExc - - exception which has occured

Throws:

org.opensubsystems.core.error.OSSException - - more descriptive exception

getJoinFromWhere

public java.lang.String[] getJoinFromWhere(java.util.List conditions,
                                           int[] columns)

Specified by:

getJoinFromWhere in interface org.opensubsystems.patterns.listdata.persist.db.ListDatabaseSchema

Overrides:

getJoinFromWhere in class org.opensubsystems.core.persist.db.DatabaseSchemaImpl

getInsertItemCategoryMap

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

Method to get sql for inserting record into item - category map table

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during constructing query

getDeleteItemCategoryMap

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

Method to get sql for deleting record from item - category map table

Returns:

String - sql query

Throws:

org.opensubsystems.core.error.OSSException - - error during constructing query

insertItemCategoryMap

public boolean insertItemCategoryMap(java.sql.Connection dbConnection,
                                     int iItemId,
                                     int iCategoryId)
                              throws org.opensubsystems.core.error.OSSException

Method that will insert record into item - category map table and returns sucess flag.

Parameters:

dbConnection - - database connection

iItemId - - item ID for map

iCategoryId - - category ID for map

Returns:

boolean - success flag

Throws:

org.opensubsystems.core.error.OSSException - - problem inserting into ItemCategoryMap

insertItemCategories

public boolean insertItemCategories(java.sql.Connection dbConnection,
                                    int iItemId,
                                    int[] arrCategoryIds)
                             throws org.opensubsystems.core.error.OSSException

Method that will insert more records into item - category map table and returns sucess flag.

Parameters:

dbConnection - - database connection

iItemId - - Id if Item

arrCategoryIds - - array of Category IDs where Item belongs

Returns:

boolean - sucess flag

Throws:

org.opensubsystems.core.error.OSSException - - problem inserting into ItemCategoryMap