View source for PostgreSQL Adapter Project - Extending SQLSchemaManager

{{Admon/obsolete}}

[[category: NexJ Express PostgreSQL]][[category: NexJ Express Development]] [[category: NexJ Express]]

==Summary==
*  SQLSchemaManager is used in maintaining database schemas (DDL)

== Tasks ==
{| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;"
|-
! Task
! Status
! Test Status
|- 
| Extend SQLSchemaManager to support PostgreSQL
| DONE
| DONE
|- 
| Research and add Maximum column Precision
| DONE
| DONE
|- 
| Figure out Data Type Mapping
| DONE
| DONE
|-
| Implement <code>analyzeTable</code>
| '''Ask NexJ, what this should do'''
|
|-
| Implement <code>appendColumnType()</code> method
| DONE
| DONE
|-
| Implement <code>appendConcatenate()</code> method
| DONE
| DONE
|-
| Implement <code>appendPrint()</code> method
| DONE
| DONE
|-
| Implement <code>appendUpgradeStepEnd()</code> method
| DONE
| DONE
|-
| Implement <code>appendUpgradeStepStart()</code> method<br/> '''NOTE''': Delimiter cannot be changed in Postgre<br/> Used begin; commit; block
| DONE
| DONE
|-
| Implement <code>appendTSExtract()</code> method
| DONE
| DONE
|-
| Add <code>appendTSField</code> method <br /> *NOTE: It is used in <code>appendTSExtract()</code> method,<br /> and it is quite similar to the one in MySQL. <br />(Suggest to NexJ to add it to the super class)
| DONE
| DONE
|-
| Implement <code>appendTSIncrement()</code> method
| DONE
| DONE
|-
| Implement <code>getCreateEtcScriptName()</code> method
| DONE
| DONE
|-
| Implement <code>getDefaultIndexspace()</code> method
| DONE
| DONE
|-
| Implement <code>getDefaultLongspace()</code> method
| DONE
| DONE
|-
| Implement <code>getDefaultTablespace()</code> method
| DONE
| DONE
|-
| Implement <code>getDropEtcScriptName()</code> method
| ?
|
|-
| Implement <code>getDynamicSQLAppender()</code> method
| DONE
| DONE
|-
| Implement <code>getGUIDExpr()</code> method
| DONE
| DONE
|-
| Implement <code>getNowExpr()</code> method
| DONE
| DONE
|-
| Implement <code>getSeparator()</code> method
| DONE
| DONE
|-
| Implement <code>getSetupEtcScriptName()</code> method
| DONE
| DONE
|-
| Override <code>renameTable</code>[http://www.postgresql.org/docs/8.0/static/sql-altertable.html] (+ recreate functions)
| DONE
| DONE
|-
| Override <code>renameColumn</code>[http://www.postgresql.org/docs/8.0/static/sql-altertable.html]
| DONE
| DONE
|-
| Override <code>renameIndex</code>[http://www.postgresql.org/docs/8.0/static/sql-alterindex.html]
| DONE
| DONE
|-
| Override <code>getIndexName()</code>
| DONE
| DONE
|-
| Override <code>createIndex(Index)</code> to add full text search
| DONE
| DONE
|-
| Override <code>dropTable(table)</code> (+ drop functions)
| DONE
| DONE
|-
| Port <code>mysql_insert.sql</code> script to <code>postgresql_insert</code>
| DONE
| DONE
|-
| Add <code>dropTriggers(columnName, table)</code> <br/>to drop full-text triggers when column is dropped
| DONE
| DONE
|-
| Add <code>dropTrigerFunctions(column)</code> <br/>to drop full-text trigger functions when column or table is dropped
| DONE
| DONE
|}

== Task Details ==
=== Extend SQLSchemaManager to support PostgreSQL ===
Create a new class PostgreSQLSchemaManager by extending SQLSchemaManager and implementing all necessary abstract methods

=== Research and add Maximum column Precision ===
Following are the Maximum column precisions we might need to establish:<br />
(All constants are defined in PostgreSQLAdapter class)
* DEFAULT_FETCH_SIZE
* MAX_CHAR_PRECISION
* MAX_VARCHAR_PRECISION
* MAX_BINARY_PRECISION
* MAX_VARBINARY_PRECISION
* MAX_REAL_PRECISION
* TIMESTAMP_FORMAT_SQL
* TIMESTAMP_FORMAT

=== Figure out Data Type Mapping ===
http://www.postgresql.org/docs/current/static/datatype.html
* Go over all data types offered in PostgreSQL
* Map types defined in <code>Primitive</code> class

=== Implement <code>appendColumnType()</code> method ===
This method appends a table column type declaration to a string buffer.
* use logic and mapping to append required column data types to incoming string.
* Test it

=== Implement <code>appendTSExtract()</code> method ===
This method extracts a field from a timestamp. It uses <code>SQLSubstReaser</code>'s enums.
* Research the extract function in the PostgreSQL
* Implement it in Adapter
* Test it<br />

*NOTE: The logic to implement the extract function is similar to the one in MySQL Adapter. It could be a good idea to suggest to NexJ to add the <code>appendTSField</code> method to the super class, so we are not repeating the code. (Can be done in the Optimization stage)

==References==
[http://jdbc.postgresql.org/documentation/84/connect.html#connection-parameters Connection Properties]
[http://www.postgresql.org/docs/8.0/static/sql-commands.html#AEN36282 SQL Commands supported by PostgreSQL]