Open main menu

CDOT Wiki β

Changes

PostgreSQL Adapter Project - Code Review 1 Changes

6,508 bytes added, 20:32, 26 January 2014
no edit summary
{{Admon/obsolete}}
 
[[category: NexJ Express PostgreSQL]][[category: NexJ Express Development]] [[category: NexJ Express]]
 
 
=General Research=
* {| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;"|-! Task! Out Come|- | Can postgreSQL be configured to have the where clause use case insensitive compare for string values? i.e. (<code>select 'GraceQ' = 'graceq'</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 by 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 dataMay 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==
{| 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
* 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==
{| 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=
==postgrecreatepostgresql_create.sql=={| border="1" cellpadding=postgresetup"4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;"|-! Task! Status! UnitTest|-||||}  ==postgresql_setup.sql=={| border="1" cellpadding="4" cellspacing="2" style=postgredrop"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|||}
Retrieved from "https://wiki.cdot.senecapolytechnic.ca/wiki/Special:MobileDiff/63324...104074"