Difference between revisions of "PostgreSQL Adapter"

From CDOT Wiki
Jump to: navigation, search
(→‎Phase 5: Optimization)
 
(134 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 +
{{Admon/obsolete}}
 +
 
<big><big> PostgreSQL Adapter for NexJ </big></big>
 
<big><big> PostgreSQL Adapter for NexJ </big></big>
 
{{NexJ_Express Index}}
 
{{NexJ_Express Index}}
 +
[[category: NexJ Past Projects]]
 +
[[category: NexJ Express PostgreSQL]]
 +
  
 
== Project Goal==
 
== Project Goal==
−
Develope an adapter to enable NexJ Express model to interact with PostgreSQL database
+
Develop an adapter to enable NexJ Express model to interact with PostgreSQL database.
 +
 
 +
===Contributors===
 +
* [[User:Gbatumbya|Grace Batumbya]]
 +
* [[Minooz/Projects | Minoo Ziaei]]
 +
* [[User:AnastasiaS | Anastasia Semionova]]
 +
 
 +
 
 +
=== Supported PostgreSQL Versions===
 +
* 8.4+
 +
 
  
 
== Current Status==
 
== Current Status==
−
{|class="collapsible collapsed" style="border-style:solid;border-width:thin;border-color:black"
+
* '''Completed''' (October 21, 2011)
 +
* Get the last PostgreSQL adapter source code from http://mercurial.nexj.com/express.cgi/core
 +
 
 +
<!--{|class="collapsible collapsed" style="border-style:solid;border-width:thin;border-color:black"
 
!align="left" style="background-color:SteelBlue; font-weight:bold;border-style:solid;border-width:thin;padding: 2px 2px 2px 2px;"| Dec 2010  
 
!align="left" style="background-color:SteelBlue; font-weight:bold;border-style:solid;border-width:thin;padding: 2px 2px 2px 2px;"| Dec 2010  
 
|-  
 
|-  
Line 29: Line 47:
 
* Application is now connected to the Postgre database.
 
* Application is now connected to the Postgre database.
 
|}
 
|}
 +
-->
  
 
==Project Phases==
 
==Project Phases==
−
=== Phase 1: Investigation ===
+
=== Phase 1: Investigation (DONE) ===
 
* Get requirements from NexJ (Meeting on Friday Nov 5th)
 
* Get requirements from NexJ (Meeting on Friday Nov 5th)
 
* Walk-through the code
 
* Walk-through the code
−
* Familiarization with PostgreSQL  
+
* Familiarization with PostgreSQL
 +
 
 +
=== Phase 2: Connect to PostgreSQL server (DONE) ===
 +
* [[NexJ_Express_Connecting_to_PostgreSQL| Connect to PostgreSQL server]]
 +
 
 +
=== Phase 3: PostgreSQLSchemaManager (DONE) ===
 +
* Estimated Duration: 3 weeks (Jan 17 - Feb 11, 2011)
 +
* [[PostgreSQL Adapter-nexj/extendingSchemaManager | Extending SQLSchemaManager ]]
  
−
=== Phase 2: Connect to PostgreSQL===
+
=== Phase 4: PostgreSQLAdapter (DONE)===
−
* [[PostgreSQL_Adapter-nexj/test-env | Create Test Environment]]
+
* Estimated Duration: 3 weeks (Feb 14 - 25, 2011)
 +
* [[PostgreSQL_Adapter-nexj/extendingSQLAdapter | Extending SQLAdapter ]]
  
−
=== Phase 3: Test first design ===
+
===Phase 5: PostgreSQL UnitTests and Optimization (DONE) ===
−
* 1- Create a PostgreSQL database through some scripts
+
* Estimated Duration: 2 weeks (Feb 28 - Mar 11, 2016)
−
: Through the process in postgresql_setup.sql, the database was created, initialized and tested:
+
* Add database specific tests fostr PostgreSQL
−
:: A database called 'test is created in PostgreSQL : <code> CREATE DATABASE test </code>
+
*:test for required contrib scripts
−
:: To create a <code>user</code> along with <code>password</code>: <code> CREATE USER test WITH PASSWORD 'test'; </code>
+
*:test for custom blob read & write functions
−
:: Login to the database 'test' with username 'test': <code> psql -U test test </code>
+
* Add PostgreSQL specific optimization
−
:: Create a <code> Schema </code> called 'test' inside the database 'test' with particular owner 'test': <code> CREATE SCHEMA test AUTHORIZATION test;</code>
 
−
:: Create a <code>table</code> called 'Mutex' with primary key: <code> CREATE TABLE test.Mutex(id INT PRIMARY KEY); </code>
 
−
:: Insert a value into the primary key: <code> INSERT INTO test.Mutex(id) VALUES (1); </code>
 
  
−
* 3- Working inside Schema 'test'
+
===Phase 6: Megre Code with Latest Revision (DONE)  ===
 +
* Merge code with lastest revision from http://mercurial.nexj.com/express
  
−
: <code> CREATE TABLE test.xxx </code>
+
===Phase 7: Test with NexJ Express Studio (DONE) ===
−
:: data types are changed in <code> appendColumnType() </code> in ''PostgreSQLSchemaManager.java'' based on each jdbc type's equivalent in PostgreSQL.
+
* Estimated duration: Mar 7- Apr 14, 2011
−
:: The implementation in   <code> crateTextTable </code> in file ''PostgreSQLSchemaManager.java'' is removed for now (base class's version is being called).
+
* Run model in Express studio using the postgresql adapter
−
: <code>CREATE INDEX test.xxx </code>
+
* create scripts for database initialization, creation and dumping
−
: <code> CREATE Trigger test.xxx // if it's needed for PostgreSQL as well as MySQL</code>
+
* create scripts for JBoss
 +
* Deploy to JBoss
  
−
* Modifications
+
=== Phase 8: Code Review 1 - Meeting (DONE) ===
−
{|class="collapsible collapsed" style="border-style:solid;border-width:thin;border-color:black"
+
* Date: Apr 20,2011
−
!align="left" style="background-color:SteelBlue; font-weight:bold;border-style:solid;border-width:thin;padding: 2px 2px 2px 2px;"| '''Changes Done to MySQL adapter'''
+
* Meet with NexJ Developer to review code
−
|-  
+
 
−
|
+
=== Phase 9: Code Review 1 - Apply Changes (DONE) ===
−
: When a connection is first established, this initial SQL statement should execute in MySQL, which is not the case for PostgreSQL, so it was commented out:
+
* Estimated Duration: Apr 20, 2011 - July 1, 2011
−
::
+
* [[PostgreSQL_Adapter_Project_-_Code_Review_1_Changes | Changes to be made]]
−
<source lang=java>
+
 
−
/* MySQLAdapter.java */
+
=== Phase 10: Code Review 2 (DONE) ===
 +
* Send code for review
 +
*
 +
 
 +
=== Phase 11: Code Review 2 - Apply Changes (DONE) ===
 +
* Estimated Duration: July 7, 2011 - July 15, 2011
 +
* [[PostgreSQL_Adapter_Project_-_Code_Review_2_Changes | Changes to be made]]
 +
 
 +
=== Phase 12: Code Review 3 (DONE) ===
 +
* Sent code for code review 3
 +
* https://bitbucket.org/gbatumbya/postgresql_external/src/08cdedb9d071/
 +
 
 +
=== Phase 13: Code Review 3 - Apply Changes (DONE) ===
 +
* Upgrade to lastest 7.1 branch
 +
* Create Statement and PreparedStatementProxy
 +
* Create PostgreSQLPreparedStatementProxy:
 +
** implement set/getQueryTimeout
 +
** move custom timeout code into this proxy
 +
* Override prepareStatement in PostgreSQL to return PostgreSQLPreparedStatementProxy
 +
* Implement implicit save points for statements within a transaction block
 +
* Modify readSchema to use addColumn for reading columns from the database
 +
** Remove MSSQL and Oracle specific code from addColumn
 +
** Override addColumn in PostgreSQL for text, bytea and lo types
 +
 
 +
=== Phase 14: Integration Tests (DONE) ===
 +
* Run Integration tests against the Adapter
 +
 
 +
=== Phase 15: Code Review 4 (DONE)===
 +
* Code review with framework architect
 +
* https://bitbucket.org/gbatumbya/postgresql_external/src/6c9a7380c8fe
  
−
public String getInitialSQL()
+
=== Phase 16: Code Review 4 - Apply Changes (DONE) ===
−
  {
+
* Estimated Duration: Sep 9 - Sep 12
−
      StringBuffer buf = new StringBuffer();
+
* [[PostgreSQL_Adapter_Project_-_Code_Review_4_Changes | Changes to be made]]
  
−
      buf.append("set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES')"); // allow using doublequote when quoting column names in "CREATE TABLE" statements
+
=== Phase 17: Code Review 5 (DONE) ===
−
      buf.append(";set optimizer_search_depth = 0"); // let DB automatically decide on how long it takes to examine plans, improves long planning sessions
+
* Submitted code for review on Sep 20.
−
      buf.append(";set max_sort_length = ").append(Math.max(MAX_VARCHAR_PRECISION, MAX_VARBINARY_PRECISION)); // set TEXT/BLOB minimum sorting length to be same as cutoff between varchar/text
+
* https://bitbucket.org/gbatumbya/postgresql_external/src/a0d10ef7cdbe/
−
      return buf.toString();
 
−
  }
 
−
</source>
 
−
: Also the same line should be commented out in postgresql_create.sql script, which is being used to create tables in the database 'test':
 
−
::
 
−
<source lang=java>
 
−
/* nexj/core/persistence/sql/etc/postgresql_create.sql */
 
−
set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');
 
−
</source>
 
−
: After activating the connection, the database is locked.
 
−
: In MySQL, the storage engine is set to a transactional safe engine such as  [http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#MySQL:InnoDB InnoDB], whereas PostgreSQL has a single built in engine. So, changes needed to be done to this script:
 
−
::
 
−
<source lang=java>
 
−
create table test.Account(
 
−
    id binary(16) not null, contactId binary(16) not null, account Type varchar(16) character set utf8 not null, funds double null,
 
−
    constraint Account_PK primary key(id)
 
−
)engine=innoDB;
 
−
</source>
 
  
−
|}
+
=== Phase 18: Code Review 5 - Apply Changes (DONE) ===
  
 +
=== Phase 19: Code Review 6 ===
 +
* Date: Oct 13
 +
* Submitted for code review
 +
* https://bitbucket.org/gbatumbya/postgresql_external/src/153aec847d54/
  
  
−
===Phase 4: Test properties specific to PostgreSQL ===
 
  
−
* Add test cases that are specific to PostgreSQL database
+
== Project Repository ==
 +
* URL for central repository of the project is: https://bitbucket.org/gbatumbya/nexjexpress-postgresqladapter
  
−
=== Phase 5: Optimization===
+
* Steps to work with the repository
  
−
*After implementation is done, optimizing the modified files.
+
:1- Follow the [https://www.projects.openhealthtools.org/sf/docman/do/downloadDocument/projects.oht_aip/docman.root.nexj_studio_express_v7_1_113_0/doc1783/1 instructions to create the NexJ Express workspace ].
 +
:2- After creating 'ws' directory,
 +
::open Eclipse and
 +
::click on '''File -> Import -> Mercurial -> Clone Existing Mercurial Repository'''
 +
:: Enter this information:
 +
::: '''URL''': https://bitbucket.org/gbatumbya/nexjexpress-postgresqladapter
 +
<!--
 +
:3- The central repository's structure is:
 +
:: Main branch called 'default'
 +
:: Branches for
 +
:4- After cloning the project, follow these steps to add a new branch with your user name:
 +
:: First make sure the active branch is 'default'. Notice the top of the Package Explorer e.g. ''core[5:b8b2f102343@'''default'''(tip)]''
 +
:: Right click on 'core' in the Package Explorer
 +
:: Click on 'Team' -> Add Branch
 +
:: Enter the branch name in the box -> Finish
 +
:: Enter the commit message -> OK
 +
:: Check the 'Force Push' if applicable.
 +
:: Now the branch name at the top of Package Explorer is changed to the new branch name.
 +
: 5- To switch to a different branch, choose 'Switch' instead of 'Add Branch'
  
−
===Phase 2: Coding ===
+
* '''NOTE!''' To avoid any conflict, do the 'Pull' every day and before any push.
−
* '''List of Tasks'''
+
* '''NOTE!''' First just 'Commit' changesets and do not 'Push' them until the end of the day or the time you are confident by latest changes to the code, then 'Push' them all at once and 'Merge' them with the 'default' branch in central repository.
−
# implementation of [[ PostgreSQL_Adapter-nexj/Adapter | PostgreSQLAdapter.java ]]
+
-->
−
# implementation of [[ PostgreSQL_Adapter-nexj/AdapterTest | PostgreSQLAdapterTest.java ]]
 
−
# implementation of [[ PostgreSQL_Adapter-nexj/SchemaManger | PostgreSQLSchemaManager.java ]]
 
−
# implementation of  [[ PostgreSQL_Adapter-nexj/Upgrade | main.upgrade ]]
 
  
 
== Resources ==
 
== Resources ==
 +
* Downloads
 +
Linux binaries http://www.openscg.org/se/postgresql/packages.jsp
 +
* Debugging
 +
:[[Run_Junit_Tests_For_Non_Default_SQL_Adapter | Unit Testing for non Default SQL Adapter]]
 +
:
 
* Intro
 
* Intro
 
:Concept of Adapter [http://www.vogella.de/articles/DesignPatternAdapter/article.html]
 
:Concept of Adapter [http://www.vogella.de/articles/DesignPatternAdapter/article.html]
Line 122: Line 183:
 
* [[PostgreSQL_Adapter-nexj/Resources#MySQL_Specifications | MySQL Specifications]]
 
* [[PostgreSQL_Adapter-nexj/Resources#MySQL_Specifications | MySQL Specifications]]
 
* [[PostgreSQL_Adapter-nexj/Resources#PostgreSQL_-_MySQL_Compare | Postgre - MySQL Compare]]
 
* [[PostgreSQL_Adapter-nexj/Resources#PostgreSQL_-_MySQL_Compare | Postgre - MySQL Compare]]
 +
 +
 +
== Summary of Applied Research ==
 +
* [[Research Findings - PostgreSQLAdapter]]
 +
* [[Research Notes - PostgreSQLAdapter]]

Latest revision as of 19:31, 26 January 2014

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

PostgreSQL Adapter for NexJ


Project Goal

Develop an adapter to enable NexJ Express model to interact with PostgreSQL database.

Contributors


Supported PostgreSQL Versions

  • 8.4+


Current Status


Project Phases

Phase 1: Investigation (DONE)

  • Get requirements from NexJ (Meeting on Friday Nov 5th)
  • Walk-through the code
  • Familiarization with PostgreSQL

Phase 2: Connect to PostgreSQL server (DONE)

Phase 3: PostgreSQLSchemaManager (DONE)

Phase 4: PostgreSQLAdapter (DONE)

Phase 5: PostgreSQL UnitTests and Optimization (DONE)

  • Estimated Duration: 2 weeks (Feb 28 - Mar 11, 2016)
  • Add database specific tests fostr PostgreSQL
    test for required contrib scripts
    test for custom blob read & write functions
  • Add PostgreSQL specific optimization

Phase 6: Megre Code with Latest Revision (DONE)

Phase 7: Test with NexJ Express Studio (DONE)

  • Estimated duration: Mar 7- Apr 14, 2011
  • Run model in Express studio using the postgresql adapter
  • create scripts for database initialization, creation and dumping
  • create scripts for JBoss
  • Deploy to JBoss

Phase 8: Code Review 1 - Meeting (DONE)

  • Date: Apr 20,2011
  • Meet with NexJ Developer to review code

Phase 9: Code Review 1 - Apply Changes (DONE)

Phase 10: Code Review 2 (DONE)

  • Send code for review

Phase 11: Code Review 2 - Apply Changes (DONE)

Phase 12: Code Review 3 (DONE)

Phase 13: Code Review 3 - Apply Changes (DONE)

  • Upgrade to lastest 7.1 branch
  • Create Statement and PreparedStatementProxy
  • Create PostgreSQLPreparedStatementProxy:
    • implement set/getQueryTimeout
    • move custom timeout code into this proxy
  • Override prepareStatement in PostgreSQL to return PostgreSQLPreparedStatementProxy
  • Implement implicit save points for statements within a transaction block
  • Modify readSchema to use addColumn for reading columns from the database
    • Remove MSSQL and Oracle specific code from addColumn
    • Override addColumn in PostgreSQL for text, bytea and lo types

Phase 14: Integration Tests (DONE)

  • Run Integration tests against the Adapter

Phase 15: Code Review 4 (DONE)

Phase 16: Code Review 4 - Apply Changes (DONE)

Phase 17: Code Review 5 (DONE)

Phase 18: Code Review 5 - Apply Changes (DONE)

Phase 19: Code Review 6


Project Repository

  • Steps to work with the repository
1- Follow the instructions to create the NexJ Express workspace .
2- After creating 'ws' directory,
open Eclipse and
click on File -> Import -> Mercurial -> Clone Existing Mercurial Repository
Enter this information:
URL: https://bitbucket.org/gbatumbya/nexjexpress-postgresqladapter

Resources

  • Downloads

Linux binaries http://www.openscg.org/se/postgresql/packages.jsp

  • Debugging
Unit Testing for non Default SQL Adapter
  • Intro
Concept of Adapter [1]
FOSSLC PostgreSQL


Summary of Applied Research

Retrieved from "https://wiki.cdot.senecapolytechnic.ca/w/index.php?title=PostgreSQL_Adapter&oldid=104073"