Difference between revisions of "PostgreSQL Adapter Project - Extending SQLSchemaManager"
AnastasiaS (talk | contribs) |
|||
(25 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
− | + | {{Admon/obsolete}} | |
− | {{ | ||
+ | [[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) | ||
− | == | + | == 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 | ||
|- | |- | ||
− | | | + | | 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 | + | 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. | ||
− | * | + | * 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
Contents
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() methodNOTE: 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)