PostgreSQL Adapter Project - Extending SQLSchemaManager

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