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

From CDOT Wiki
Jump to: navigation, search
(General Research)
(PostgreSQLSchemaManager)
Line 169: Line 169:
 
|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
Line 233: Line 235:
 
|
 
|
 
|}
 
|}
 
  
 
=Scripts=
 
=Scripts=

Revision as of 14:12, 21 April 2011


General Research

Task Status
Can postgreSQL be configured to have the where clause use case insensitive compare for string values? i.e. (select 'Q' = 'q' => should return true) (DONE)

http://www.postgresql.org/docs/8.4/static/citext.html

Add UUID generation to x64 versions
Does extract(epoch from value) return milliseconds?
What encoding (UTF-8 and/or UTF-16) does PostgreSQL use for unicode? Can this be configured per column?
How is a repeatable read with exclusive lock implemented?
Does timestamp'value', handle java.sql.timestamp.toString()?
Does to_timestamp support milliseconds?
Does postgreSQL have constants for boolean values?
Does posrgreSQL jdbc driver support returning metadata in UpperCase
Does renaming a column cause error if a view depends on it?
Does lo.sql unlink large objects when table is truncated?

Classes

PostgreSQLAdapter

Task Status UnitTest
Implement setQueryTime with Timer object
  • if code variation is limited, extract to SQLAdapter
Refactor escaping literals
  • May be make use isLiteral
appendMatchExpression
  • Add parentheses to enforce operation order
  • Remove implementation of MatchNode.WEIGHT.SYMBOL
indexNameMatches
  • Use toMetadataCase and toDatabaseCase instead of toLowerCase/toUpperCase
appendSuffixHint
  • Implement query locking for aggregate queries (i.e. repeatable read + exclusive lock)
appendLiteral
  • Check if timestamp accepts java.sql.timestamp.toString() format
  • If boolean constants are supported, use those instead of string 't'/'f'
getBind
  • Check if override is necessary
isLiteral
  • Use string.length and binary.getSize to ensure less than 1GB
appendTypeConversion
  • Ensure to_timestamp accepts milliseconds
  • Ensure case Primitive.TIMESTAMP_ORDINAL: returns milliseconds (extract(epoch from
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)
s_bindFactoryArray[Primitive.BINARY_ORDINAL]
  • Use reflection to make use of postgresql jdbc objects and methods to handle large objects
DUPLICATE_KEY_NAME_PATTERN
  • Modify pattern to remove dependency on english words
s_bindFactoryArray
  • Use parent BINDS for unchanged binds


PostgreSQLSchemaManager

Task Status UnitTest
Override setCustomDatabaseProperties
  • Add template fields for postgreSQL scripts
dropTable
  • Remove if exists
getGUIDExpr
  • Implement solution for x64 versions
remove trigger and procedure suffix $uid
getDefaultLongspace, getDefaultTablespace, getDefaultIndexspace
  • move to SQLAdapter
getPrimaryKeys
  • check if override is needed
generateIndexName
  • Check if toLowerCase is needed
appendColumnType
  • check if serial and bigserial have limits when compared to using sequences
Refactor creating and deleting full-text search indexes
  • createFullTextIndex
    • generateFullTextSearchProcedureName
    • createFullTextSearchProcedure
    • createFullTextSearchTrigger
  • dropFullTextIndex
    • generateFullTextSearchProcedureName
    • dropFullTextSearchProcedure
    • dropFullTextSearchTrigger
Refactor checking for BLOB column to isBLOB
Refactor checking for full-text search index on a column to getFullTextIndex
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
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
dropIndex
  • call super
  • if index is TEXT index
    • drop column$
    • drop full-text search trigger
appendColumnAlternation
  • run UnitTest to check for change in nullness and precision
renameTable
  • fix full-text search trigger maintainence
deleteLargeObjects
  • change to use for loop instead of iterator
getIndexInfo
  • check if override is needed for
Use toMetadataCase and toDatabaseCase instead of toLowerCase/toUpperCase

Scripts

postgresql_create.sql

Task Status UnitTest


postgresql_setup.sql

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


postgresql_drop.sql

Task Status UnitTest
Add sql to drop custom functions