Difference between revisions of "PostgreSQL Adapter Project - Extending SQLSchemaManager"

From CDOT Wiki
Jump to: navigation, search
 
(25 intermediate revisions by 3 users not shown)
Line 1: Line 1:
<big><big> PostgreSQL Adapter for NexJ - Extending SQL Schema Manager</big></big>
+
{{Admon/obsolete}}
{{NexJ_Express Index}}
 
  
 +
[[category: NexJ Express PostgreSQL]][[category: NexJ Express Development]] [[category: NexJ Express]]
 +
 +
==Summary==
 
*  SQLSchemaManager is used in maintaining database schemas (DDL)
 
*  SQLSchemaManager is used in maintaining database schemas (DDL)
  
== Task Summary ==
+
== Tasks ==
 
{| 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 17: Line 19:
 
| Research and add Maximum column Precision
 
| Research and add Maximum column Precision
 
| DONE
 
| DONE
|
+
| DONE
 
|-  
 
|-  
 
| Figure out Data Type Mapping
 
| Figure out Data Type Mapping
 
| DONE
 
| DONE
 +
| DONE
 +
|-
 +
| Implement <code>analyzeTable</code>
 +
| '''Ask NexJ, what this should do'''
 
|
 
|
 
|-
 
|-
 
| Implement <code>appendColumnType()</code> method
 
| Implement <code>appendColumnType()</code> method
 
| DONE
 
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>appendConcatenate()</code> method
 
| Implement <code>appendConcatenate()</code> method
 
| DONE
 
| DONE
|  
+
| DONE
 
|-
 
|-
 
| Implement <code>appendPrint()</code> method
 
| Implement <code>appendPrint()</code> method
 
| DONE
 
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>appendUpgradeStepEnd()</code> method
 
| Implement <code>appendUpgradeStepEnd()</code> method
|
+
| DONE
|
+
| DONE
 
|-
 
|-
| Implement <code>appendUpgradeStepStart()</code> method
+
| 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
 
| Implement <code>appendTSExtract()</code> method
 
| DONE
 
| 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)
 
| 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
|
+
| DONE
 
|-
 
|-
 
| Implement <code>appendTSIncrement()</code> method
 
| Implement <code>appendTSIncrement()</code> method
|
+
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>getCreateEtcScriptName()</code> method
 
| Implement <code>getCreateEtcScriptName()</code> method
 
| DONE
 
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>getDefaultIndexspace()</code> method
 
| Implement <code>getDefaultIndexspace()</code> method
 
| DONE
 
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>getDefaultLongspace()</code> method
 
| Implement <code>getDefaultLongspace()</code> method
Line 69: Line 75:
 
| Implement <code>getDefaultTablespace()</code> method
 
| Implement <code>getDefaultTablespace()</code> method
 
| DONE
 
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>getDropEtcScriptName()</code> method
 
| Implement <code>getDropEtcScriptName()</code> method
|
+
| ?
 
|
 
|
 
|-
 
|-
 
| Implement <code>getDynamicSQLAppender()</code> method
 
| Implement <code>getDynamicSQLAppender()</code> method
|
+
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>getGUIDExpr()</code> method
 
| Implement <code>getGUIDExpr()</code> method
|
+
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>getNowExpr()</code> method
 
| Implement <code>getNowExpr()</code> method
 
| DONE
 
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>getSeparator()</code> method
 
| Implement <code>getSeparator()</code> method
 
| DONE
 
| DONE
|
+
| DONE
 
|-
 
|-
 
| Implement <code>getSetupEtcScriptName()</code> method
 
| Implement <code>getSetupEtcScriptName()</code> method
 
| DONE
 
| DONE
|
+
| DONE
 
|-
 
|-
| Override <code>renameTable</code>[http://www.postgresql.org/docs/8.0/static/sql-altertable.html]
+
| 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]
 
| 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]
 
| Override <code>renameIndex</code>[http://www.postgresql.org/docs/8.0/static/sql-alterindex.html]
|
+
| DONE
|
+
| DONE
 
|-
 
|-
 
| Override <code>getIndexName()</code>
 
| Override <code>getIndexName()</code>
 
| DONE
 
| 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
 
|-
 
|-
| Override <code>appendColumnSuffix</code>
+
| Add <code>dropTrigerFunctions(column)</code> <br/>to drop full-text trigger functions when column or table is dropped
|
+
| DONE
|
+
| DONE
 
|}
 
|}
  
 
== Task Details ==
 
== Task Details ==
 
=== Extend SQLSchemaManager to support PostgreSQL ===
 
=== Extend SQLSchemaManager to support PostgreSQL ===
Create a new class PostgreSQLSchemaManager by extending SQLSchemaManager and implementing all necessary abstract classes
+
Create a new class PostgreSQLSchemaManager by extending SQLSchemaManager and implementing all necessary abstract methods
  
 
=== Research and add Maximum column Precision ===
 
=== Research and add Maximum column Precision ===
Line 142: Line 164:
 
* Test it
 
* Test it
  
=== Implement appendTSExtract() method ===
+
=== Implement <code>appendTSExtract()</code> method ===
 
This method extracts a field from a timestamp. It uses <code>SQLSubstReaser</code>'s enums.
 
This method extracts a field from a timestamp. It uses <code>SQLSubstReaser</code>'s enums.
* Researc the extract function in the PostgreSQL
+
* Research the extract function in the PostgreSQL
 
* Implement it in Adapter
 
* Implement it in Adapter
* Test it
+
* 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)
 
*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==
 
==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]
 
[http://www.postgresql.org/docs/8.0/static/sql-commands.html#AEN36282 SQL Commands supported by PostgreSQL]

Latest revision as of 20:33, 26 January 2014

Important.png
This page may be obsolete.
It contains historical information.

Summary

  • SQLSchemaManager is used in maintaining database schemas (DDL)

Tasks

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 analyzeTable Ask NexJ, what this should do
Implement appendColumnType() method DONE DONE
Implement appendConcatenate() method DONE DONE
Implement appendPrint() method DONE DONE
Implement appendUpgradeStepEnd() method DONE DONE
Implement appendUpgradeStepStart() method
NOTE: Delimiter cannot be changed in Postgre
Used begin; commit; block
DONE DONE
Implement appendTSExtract() method DONE DONE
Add appendTSField method
*NOTE: It is used in appendTSExtract() method,
and it is quite similar to the one in MySQL.
(Suggest to NexJ to add it to the super class)
DONE DONE
Implement appendTSIncrement() method DONE DONE
Implement getCreateEtcScriptName() method DONE DONE
Implement getDefaultIndexspace() method DONE DONE
Implement getDefaultLongspace() method DONE DONE
Implement getDefaultTablespace() method DONE DONE
Implement getDropEtcScriptName() method  ?
Implement getDynamicSQLAppender() method DONE DONE
Implement getGUIDExpr() method DONE DONE
Implement getNowExpr() method DONE DONE
Implement getSeparator() method DONE DONE
Implement getSetupEtcScriptName() method DONE DONE
Override renameTable[1] (+ recreate functions) DONE DONE
Override renameColumn[2] DONE DONE
Override renameIndex[3] DONE DONE
Override getIndexName() DONE DONE
Override createIndex(Index) to add full text search DONE DONE
Override dropTable(table) (+ drop functions) DONE DONE
Port mysql_insert.sql script to postgresql_insert DONE DONE
Add dropTriggers(columnName, table)
to drop full-text triggers when column is dropped
DONE DONE
Add dropTrigerFunctions(column)
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:
(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 Primitive class

Implement appendColumnType() 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 appendTSExtract() method

This method extracts a field from a timestamp. It uses SQLSubstReaser's enums.

  • Research the extract function in the PostgreSQL
  • Implement it in Adapter
  • Test it
  • 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 appendTSField method to the super class, so we are not repeating the code. (Can be done in the Optimization stage)

References

Connection Properties SQL Commands supported by PostgreSQL