Difference between revisions of "PostgreSQL Adapter Project - Code Review 1 Changes"
(26 intermediate revisions by one other user not shown) | |||
Line 1: | Line 1: | ||
+ | {{Admon/obsolete}} | ||
+ | |||
[[category: NexJ Express PostgreSQL]][[category: NexJ Express Development]] [[category: NexJ Express]] | [[category: NexJ Express PostgreSQL]][[category: NexJ Express Development]] [[category: NexJ Express]] | ||
+ | |||
=General Research= | =General Research= | ||
Line 5: | Line 8: | ||
|- | |- | ||
! Task | ! Task | ||
− | ! | + | ! Out Come |
|- | |- | ||
− | | Can postgreSQL be configured to have the where clause use case insensitive compare for string values? i.e. (<code>select ' | + | | Can postgreSQL be configured to have the where clause use case insensitive compare for string values? i.e. (<code>select 'Q' = 'q'</code> => should return true) |
− | | | + | | http://www.postgresql.org/docs/8.4/static/citext.html |
|- | |- | ||
|Add UUID generation to x64 versions | |Add UUID generation to x64 versions | ||
− | | | + | |UUID generation depends on OSSP-UUID which is not yet ported to x64. |
|- | |- | ||
|Does <code>extract(epoch from value)</code> return milliseconds? | |Does <code>extract(epoch from value)</code> return milliseconds? | ||
− | | | + | |'''YES''' |
|- | |- | ||
|What encoding (UTF-8 and/or UTF-16) does PostgreSQL use for unicode? Can this be configured per column? | |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? | | How is a repeatable read with exclusive lock implemented? | ||
− | | | + | |It is not supported. |
|- | |- | ||
|Does timestamp'value', handle java.sql.timestamp.toString()? | |Does timestamp'value', handle java.sql.timestamp.toString()? | ||
− | | | + | | '''YES''' |
|- | |- | ||
|Does to_timestamp support milliseconds? | |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? | | Does postgreSQL have constants for boolean values? | ||
− | | | + | |'''YES''' |
+ | TRUE and FALSE (they are case insensitive) | ||
|- | |- | ||
| Does posrgreSQL jdbc driver support returning metadata in UpperCase | | 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? | | 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? | | Does lo.sql unlink large objects when table is truncated? | ||
− | | | + | |'''NO''' |
+ | http://www.postgresql.org/docs/8.3/static/lo.html#AEN104018 | ||
|} | |} | ||
=Classes= | =Classes= | ||
+ | |||
+ | |||
==PostgreSQLAdapter== | ==PostgreSQLAdapter== | ||
{| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;" | {| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;" | ||
Line 50: | Line 60: | ||
! Status | ! Status | ||
! UnitTest | ! UnitTest | ||
+ | |- | ||
+ | |* Extending CaseInsensitiveSQLAdapter | ||
+ | |'''DONE''' | ||
+ | |'''DONE''' | ||
|- | |- | ||
|Implement setQueryTime with Timer object | |Implement setQueryTime with Timer object | ||
* if code variation is limited, extract to SQLAdapter | * if code variation is limited, extract to SQLAdapter | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
| Refactor escaping literals | | Refactor escaping literals | ||
* May be make use isLiteral | * May be make use isLiteral | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|appendMatchExpression | |appendMatchExpression | ||
* Add parentheses to enforce operation order | * Add parentheses to enforce operation order | ||
* Remove implementation of MatchNode.WEIGHT.SYMBOL | * Remove implementation of MatchNode.WEIGHT.SYMBOL | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|indexNameMatches | |indexNameMatches | ||
* Use toMetadataCase and toDatabaseCase instead of toLowerCase/toUpperCase | * Use toMetadataCase and toDatabaseCase instead of toLowerCase/toUpperCase | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|appendSuffixHint | |appendSuffixHint | ||
− | * Implement query locking for | + | * Implement query locking for OUTER JOIN queries (i.e. repeatable read + exclusive lock) |
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
| appendLiteral | | appendLiteral | ||
− | * Check if timestamp | + | * Check if timestamp accepts java.sql.timestamp.toString() format |
* If boolean constants are supported, use those instead of string 't'/'f' | * If boolean constants are supported, use those instead of string 't'/'f' | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
| getBind | | getBind | ||
* Check if override is necessary | * Check if override is necessary | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
| isLiteral | | isLiteral | ||
* Use string.length and binary.getSize to ensure less than 1GB | * Use string.length and binary.getSize to ensure less than 1GB | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
| appendTypeConversion | | appendTypeConversion | ||
* Ensure to_timestamp accepts milliseconds | * Ensure to_timestamp accepts milliseconds | ||
* Ensure case Primitive.TIMESTAMP_ORDINAL: returns milliseconds (extract(epoch from | * Ensure case Primitive.TIMESTAMP_ORDINAL: returns milliseconds (extract(epoch from | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
| isUnicode | | isUnicode | ||
* Check if column is set to use UTF-16 encoding | * 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) | * 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] | | s_bindFactoryArray[Primitive.BINARY_ORDINAL] | ||
* Use reflection to make use of postgresql jdbc objects and methods to handle large objects | * Use reflection to make use of postgresql jdbc objects and methods to handle large objects | ||
− | | | + | |'''DONE''' |
| | | | ||
|- | |- | ||
| DUPLICATE_KEY_NAME_PATTERN | | DUPLICATE_KEY_NAME_PATTERN | ||
* Modify pattern to remove dependency on english words | * Modify pattern to remove dependency on english words | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|s_bindFactoryArray | |s_bindFactoryArray | ||
* Use parent BINDS for unchanged binds | * Use parent BINDS for unchanged binds | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|} | |} | ||
Line 127: | Line 141: | ||
! Task | ! Task | ||
! Status | ! Status | ||
+ | ! UnitTest | ||
+ | |- | ||
+ | |* Extending CaseInsensitiveSQLSchemaManager | ||
+ | |'''DONE''' | ||
+ | |'''DONE''' | ||
+ | |- | ||
+ | |Override setCustomDatabaseProperties | ||
+ | *Add template fields for postgreSQL scripts | ||
+ | |'''DONE''' | ||
+ | |'''DONE''' | ||
|- | |- | ||
|dropTable | |dropTable | ||
* Remove <code>if exists</code> | * Remove <code>if exists</code> | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|getGUIDExpr | |getGUIDExpr | ||
Line 139: | Line 163: | ||
|- | |- | ||
|remove trigger and procedure suffix $uid | |remove trigger and procedure suffix $uid | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|getDefaultLongspace, getDefaultTablespace, getDefaultIndexspace | |getDefaultLongspace, getDefaultTablespace, getDefaultIndexspace | ||
Line 149: | Line 173: | ||
|getPrimaryKeys | |getPrimaryKeys | ||
* check if override is needed | * check if override is needed | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|generateIndexName | |generateIndexName | ||
* Check if toLowerCase is needed | * Check if toLowerCase is needed | ||
− | | | + | |'''DONE'''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|appendColumnType | |appendColumnType | ||
* check if serial and bigserial have limits when compared to using sequences | * check if serial and bigserial have limits when compared to using sequences | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|Refactor creating and deleting full-text search indexes | |Refactor creating and deleting full-text search indexes | ||
* createFullTextIndex | * createFullTextIndex | ||
+ | ** generateFullTextSearchProcedureName | ||
** createFullTextSearchProcedure | ** createFullTextSearchProcedure | ||
** createFullTextSearchTrigger | ** createFullTextSearchTrigger | ||
* dropFullTextIndex | * dropFullTextIndex | ||
+ | ** generateFullTextSearchProcedureName | ||
** dropFullTextSearchProcedure | ** dropFullTextSearchProcedure | ||
** dropFullTextSearchTrigger | ** dropFullTextSearchTrigger | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|Refactor checking for BLOB column to isBLOB | |Refactor checking for BLOB column to isBLOB | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|Refactor checking for full-text search index on a column to getFullTextIndex | |Refactor checking for full-text search index on a column to getFullTextIndex | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|alterColumn - use this logic | |alterColumn - use this logic | ||
Line 184: | Line 210: | ||
* alter the column | * alter the column | ||
* create the deleted view/s | * create the deleted view/s | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|renameColumn - use this logic for | |renameColumn - use this logic for | ||
Line 193: | Line 219: | ||
* drop full-text search trigger | * drop full-text search trigger | ||
* create full-text search trigger | * create full-text search trigger | ||
− | | | + | |'''DONE''' |
− | | | + | |'''DONE''' |
|- | |- | ||
|dropIndex | |dropIndex | ||
Line 201: | Line 227: | ||
** drop column$ | ** drop column$ | ||
** drop full-text search trigger | ** 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= | =Scripts= | ||
==postgresql_create.sql== | ==postgresql_create.sql== | ||
+ | {| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;" | ||
+ | |- | ||
+ | ! Task | ||
+ | ! Status | ||
+ | ! UnitTest | ||
+ | |- | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | |||
==postgresql_setup.sql== | ==postgresql_setup.sql== | ||
+ | {| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;" | ||
+ | |- | ||
+ | ! 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== | ==postgresql_drop.sql== | ||
+ | {| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;" | ||
+ | |- | ||
+ | ! Task | ||
+ | ! Status | ||
+ | ! UnitTest | ||
+ | |- | ||
+ | |Add sql to drop custom functions | ||
+ | | | ||
+ | | | ||
+ | |} |
Latest revision as of 20:32, 26 January 2014
Contents
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 |
Classes
PostgreSQLAdapter
Task | Status | UnitTest |
---|---|---|
* Extending CaseInsensitiveSQLAdapter | DONE | DONE |
Implement setQueryTime with Timer object
|
DONE | DONE |
Refactor escaping literals
|
DONE | DONE |
appendMatchExpression
|
DONE | DONE |
indexNameMatches
|
DONE | DONE |
appendSuffixHint
|
DONE | DONE |
appendLiteral
|
DONE | DONE |
getBind
|
DONE | DONE |
isLiteral
|
DONE | DONE |
appendTypeConversion
|
DONE | DONE |
isUnicode
|
DONE | DONE |
s_bindFactoryArray[Primitive.BINARY_ORDINAL]
|
DONE | |
DUPLICATE_KEY_NAME_PATTERN
|
DONE | DONE |
s_bindFactoryArray
|
DONE | DONE |
PostgreSQLSchemaManager
Task | Status | UnitTest |
---|---|---|
* Extending CaseInsensitiveSQLSchemaManager | DONE | DONE |
Override setCustomDatabaseProperties
|
DONE | DONE |
dropTable
|
DONE | DONE |
getGUIDExpr
|
||
remove trigger and procedure suffix $uid | DONE | DONE |
getDefaultLongspace, getDefaultTablespace, getDefaultIndexspace
|
||
getPrimaryKeys
|
DONE | DONE |
generateIndexName
|
DONE' | DONE |
appendColumnType
|
DONE | DONE |
Refactor creating and deleting full-text search indexes
|
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
|
DONE | DONE |
renameColumn - use this logic for
|
DONE | DONE |
dropIndex
|
DONE | DONE |
appendColumnAlternation
|
DONE | |
renameTable
|
DONE | DONE |
deleteLargeObjects
|
DONE | DONE |
getIndexInfo
|
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
|
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 |