Difference between revisions of "PostgreSQL Adapter Project - Code Review 1 Changes"

From CDOT Wiki
Jump to: navigation, search
(→‎Classes)
 
(29 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]]
 +
 +
 
=General Research=
 
=General Research=
−
* Can postgreSQL be configured to have the where clause use case insensitive compare for string values? i.e. (<code>select 'Grace' = 'grace'</code> => should return true)
+
{| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;"
−
* Add UUID generation to x64 versions
+
|-
−
* Does <code>extract(epoch from value)</code> return milliseconds?
+
! Task
−
* What encoding (UTF-8 and/or UTF-16) does PostgreSQL use for unicode? Can this be configured per column?
+
! Out Come
 +
|-
 +
| 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
 +
|UUID generation depends on OSSP-UUID which is not yet ported to x64.
 +
|-
 +
|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?
 +
|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=
 
=Classes=
 +
 +
 
==PostgreSQLAdapter==
 
==PostgreSQLAdapter==
 +
{| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;"
 +
|-
 +
! 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
 
* Add parentheses to enforce operation order
−
**appendMatchExpression
+
* Remove implementation of MatchNode.WEIGHT.SYMBOL
−
**
+
|'''DONE'''
−
* Implement setQueryTime with Timer object
+
|'''DONE'''
−
** if code variation is limited, extract to SQLAdapter
+
|-
 +
|indexNameMatches
 
* Use toMetadataCase and toDatabaseCase instead of toLowerCase/toUpperCase
 
* Use toMetadataCase and toDatabaseCase instead of toLowerCase/toUpperCase
−
** indexNameMatches
+
|'''DONE'''
−
* Refactor escaping literals
+
|'''DONE'''
−
** May be make use isLiteral
+
|-
−
* Implement query locking for aggregate queries (i.e. repeatable read + exclusive lock)
+
|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==
 
==PostgreSQLSchemaManager==
 +
{| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;"
 +
|-
 +
! Task
 +
! Status
 +
! UnitTest
 +
|-
 +
|* Extending CaseInsensitiveSQLSchemaManager
 +
|'''DONE'''
 +
|'''DONE'''
 +
|-
 +
|Override setCustomDatabaseProperties
 +
*Add template fields for postgreSQL scripts
 +
|'''DONE'''
 +
|'''DONE'''
 +
|-
 +
|dropTable
 +
* Remove <code>if exists</code>
 +
|'''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=
 
=Scripts=
−
==postgrecreate.sql==
+
==postgresql_create.sql==
−
==postgresetup.sql==
+
{| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;"
−
==postgredrop.sql==
+
|-
 +
! Task
 +
! Status
 +
! UnitTest
 +
|-
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
 
 +
==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==
 +
{| 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

Important.png
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
Retrieved from "https://wiki.cdot.senecapolytechnic.ca/w/index.php?title=PostgreSQL_Adapter_Project_-_Code_Review_1_Changes&oldid=104074"