PostgreSQL Adapter Project - Code Review 1 Changes

This page may be obsolete.
It contains historical information.

General Research

Task	Out Come
Can postgreSQL be configured to have the where clause use case insensitive compare for string values? i.e. (select 'Q' = 'q' => should return true)	http://www.postgresql.org/docs/8.4/static/citext.html
Add UUID generation to x64 versions	UUID generation depends on OSSP-UUID which is not yet ported to x64.
Does extract(epoch from value) return milliseconds?	YES
What encoding (UTF-8 and/or UTF-16) does PostgreSQL use for unicode? Can this be configured per column?	UTF-8
How is a repeatable read with exclusive lock implemented?	It is not supported.
Does timestamp'value', handle java.sql.timestamp.toString()?	YES
Does to_timestamp support milliseconds?	YES Precision is up to microseconds(10^-6) http://www.postgresql.org/docs/7.4/static/functions-datetime.html
Does postgreSQL have constants for boolean values?	YES TRUE and FALSE (they are case insensitive)
Does posrgreSQL jdbc driver support returning metadata in UpperCase	NO Since database objects are case sensitive, returning UpperCase would make for corrupt data May be consider changing SQLManager to enforce all database object names to be lowercase
Does renaming a column cause error if a view depends on it?	NO View' is updated with to use the new column name
Does lo.sql unlink large objects when table is truncated?	NO http://www.postgresql.org/docs/8.3/static/lo.html#AEN104018

Classes

PostgreSQLAdapter

Task	Status	UnitTest
* Extending CaseInsensitiveSQLAdapter	DONE	DONE
Implement setQueryTime with Timer object if code variation is limited, extract to SQLAdapter	DONE	DONE
Refactor escaping literals May be make use isLiteral	DONE	DONE
appendMatchExpression Add parentheses to enforce operation order Remove implementation of MatchNode.WEIGHT.SYMBOL	DONE	DONE
indexNameMatches Use toMetadataCase and toDatabaseCase instead of toLowerCase/toUpperCase	DONE	DONE
appendSuffixHint Implement query locking for OUTER JOIN queries (i.e. repeatable read + exclusive lock)	DONE	DONE
appendLiteral Check if timestamp accepts java.sql.timestamp.toString() format If boolean constants are supported, use those instead of string 't'/'f'	DONE	DONE
getBind Check if override is necessary	DONE	DONE
isLiteral Use string.length and binary.getSize to ensure less than 1GB	DONE	DONE
appendTypeConversion Ensure to_timestamp accepts milliseconds Ensure case Primitive.TIMESTAMP_ORDINAL: returns milliseconds (extract(epoch from	DONE	DONE
isUnicode Check if column is set to use UTF-16 encoding TRUE == is a unicode column FALSE == is not a unicode column null == unknown (e.g. number column or column not present)	DONE	DONE
s_bindFactoryArray[Primitive.BINARY_ORDINAL] Use reflection to make use of postgresql jdbc objects and methods to handle large objects	DONE
DUPLICATE_KEY_NAME_PATTERN Modify pattern to remove dependency on english words	DONE	DONE
s_bindFactoryArray Use parent BINDS for unchanged binds	DONE	DONE

PostgreSQLSchemaManager

Task	Status	UnitTest
* Extending CaseInsensitiveSQLSchemaManager	DONE	DONE
Override setCustomDatabaseProperties Add template fields for postgreSQL scripts	DONE	DONE
dropTable Remove if exists	DONE	DONE
getGUIDExpr Implement solution for x64 versions
remove trigger and procedure suffix $uid	DONE	DONE
getDefaultLongspace, getDefaultTablespace, getDefaultIndexspace move to SQLAdapter
getPrimaryKeys check if override is needed	DONE	DONE
generateIndexName Check if toLowerCase is needed	DONE'	DONE
appendColumnType check if serial and bigserial have limits when compared to using sequences	DONE	DONE
Refactor creating and deleting full-text search indexes createFullTextIndex generateFullTextSearchProcedureName createFullTextSearchProcedure createFullTextSearchTrigger dropFullTextIndex generateFullTextSearchProcedureName dropFullTextSearchProcedure dropFullTextSearchTrigger	DONE	DONE
Refactor checking for BLOB column to isBLOB	DONE	DONE
Refactor checking for full-text search index on a column to getFullTextIndex	DONE	DONE
alterColumn - use this logic execute SQL to drop all dependent view/s for the column's table alter the column create the deleted view/s	DONE	DONE
renameColumn - use this logic for check if column has full-text search index rename the column rename column$ drop full-text search trigger create full-text search trigger	DONE	DONE
dropIndex call super if index is TEXT index drop column$ drop full-text search trigger	DONE	DONE
appendColumnAlternation run UnitTest to check for change in nullness and precision	DONE
renameTable fix full-text search trigger maintainence	DONE	DONE
deleteLargeObjects change to use for loop instead of iterator	DONE	DONE
getIndexInfo check if override is needed for	DONE	DONE
Use toMetadataCase and toDatabaseCase instead of toLowerCase/toUpperCase	DONE	DONE

Scripts

postgresql_create.sql

Task	Status	UnitTest

postgresql_setup.sql

Task	Status	UnitTest
Add comment to enable PREPARE TRANSACTION	DONE	DONE
Use template field to set location of contrib folder make use of ${path}	DONE	DONE
Add template field to set location for tablespace, indexspace and longspace	DONE	DONE
Add SQL to create tablespace, indexspace and longspace	DONE	DONE
Move function creation to postgresql_create.sql

postgresql_drop.sql

Task	Status	UnitTest
Add sql to drop custom functions