Changes

Jump to: navigation, search

PostgreSQL Adapter

1,572 bytes added, 20:31, 26 January 2014
no edit summary
{{Admon/obsolete}}
 
<big><big> PostgreSQL Adapter for NexJ </big></big>
{{NexJ_Express Index}}
[[category: NexJ Past Projects]]
[[category: NexJ Express PostgreSQL]]
 
== Project Goal==
Develope 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==
* '''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
|-
* Application is now connected to the Postgre database.
|}
-->
==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) ===* [[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 4: PostgreSQLAdapter (DONE)===* Estimated Duration: 3 weeks (Feb 14 - 25, 2011)* [[PostgreSQL_Adapter-nexj/extendingSQLAdapter | Extending SQLAdapter ]] ===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) ===* Merge code with lastest revision from http://mercurial.nexj.com/express ===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 29: Connect to PostgreSQLCode Review 1 - Apply Changes (DONE) ===* Estimated Duration: Apr 20, 2011 - July 1, 2011* [[PostgreSQL_Adapter-nexj/testPostgreSQL_Adapter_Project_-env _Code_Review_1_Changes | Create Test EnvironmentChanges to be made]]
=== Phase 310: Test first design Code Review 2 (DONE) ===* 1- Create a PostgreSQL database through some scripts: Through the process in postgresql_setup.sql, the database was created, initialized and tested::: A database called 'test is created in PostgreSQL : <code> CREATE DATABASE test </Send code>for review:: To create a <code>user</code> along with <code>password</code>: <code> CREATE USER test WITH PASSWORD 'test'; </code>:: Login to the database 'test' with username 'test': <code> psql -U test test </code>:: 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>*
=== Phase 11: Code Review 2 - Apply Changes (DONE) ===* Estimated Duration: July 7, 2011 - July 15, 2011* 3[[PostgreSQL_Adapter_Project_- Working inside Schema 'test'_Code_Review_2_Changes | Changes to be made]]
=== Phase 12: <code> CREATE TABLE test.xxx </code>:: data types are changed in <code> appendColumnTypeCode Review 3 (DONE) </code> in ''PostgreSQLSchemaManager.java'' based on each jdbc type's equivalent in PostgreSQL.===:: The implementation in <* Sent code> crateTextTable </for code> in file ''PostgreSQLSchemaManager.java'' is removed for now (base class's version is being called).review 3* https: <code>CREATE INDEX test//bitbucket.xxx <org/gbatumbya/code> : <code> CREATE Trigger test.xxx postgresql_external/src/ if it's needed for PostgreSQL as well as MySQL<08cdedb9d071/code>
* Modifications{|class="collapsible collapsed" style="border-style= Phase 13:solid;borderCode Review 3 -width:thin;border-color:black"!alignApply Changes (DONE) =="left" style="background-color:SteelBlue; font-weight:bold;border-style:solid;border-width:thin;padding: 2px 2px 2px 2px;"| '''Changes Done * Upgrade to MySQL adapter''' lastest 7.1 branch|- * Create Statement and PreparedStatementProxy* Create PostgreSQLPreparedStatementProxy:|** implement set/getQueryTimeout: When a connection is first established, ** move custom timeout code into this initial SQL statement should execute proxy* Override prepareStatement in MySQL, which is not PostgreSQL to return PostgreSQLPreparedStatementProxy* Implement implicit save points for statements within a transaction block* Modify readSchema to use addColumn for reading columns from the case database** Remove MSSQL and Oracle specific code from addColumn** Override addColumn in PostgreSQL for PostgreSQLtext, so it was commented out:bytea and lo types === Phase 14::<source langIntegration Tests (DONE) ===java>/* MySQLAdapter.java */Run Integration tests against the Adapter
public String getInitialSQL=== Phase 15: Code Review 4 (DONE)=== {* Code review with framework architect StringBuffer buf = new StringBuffer();* https://bitbucket.org/gbatumbya/postgresql_external/src/6c9a7380c8fe
buf.append("set sql_mode = concat== Phase 16: Code Review 4 - Apply Changes (@@sql_mode, ',ANSI_QUOTES')"DONE); // allow using doublequote when quoting column names in "CREATE TABLE" statements buf.append(";set optimizer_search_depth = 0"); // let DB automatically decide on how long it takes to examine plans, improves long planning sessions 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 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>Estimated Duration: After activating the connection, the database is locked.Sep 9 - Sep 12: 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 PostgreSQL_Adapter_Project_-_Code_Review_4_Changes | Changes 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>made]]
|}=== Phase 17: Code Review 5 (DONE) ===* Submitted code for review on Sep 20.* https://bitbucket.org/gbatumbya/postgresql_external/src/a0d10ef7cdbe/
=== 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
===
== Project Repository ==* URL for central repository of the project is: Add test cases that are specific to PostgreSQL databasehttps://bitbucket.org/gbatumbya/nexjexpress-postgresqladapter
=== Phase 5: Optimization===* Steps to work with the repository
: 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 implementation 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 done::: Main branch called 'default' :: Branches for:4- After cloning the project, optimizing 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 modified filesPackage 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 ===* '''List of TasksNOTE!'''To avoid any conflict, do the 'Pull'# implementation of [[ PostgreSQL_Adapter-nexj/Adapter | PostgreSQLAdapterevery day and before any push.java ]]# implementation * '''NOTE!''' First just 'Commit' changesets and do not 'Push' them until the end of [[ PostgreSQL_Adapter-nexj/AdapterTest | PostgreSQLAdapterTestthe 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.java ]]# implementation of [[ PostgreSQL_Adapter-nexj/SchemaManger | PostgreSQLSchemaManager.java ]]# implementation of [[ PostgreSQL_Adapter-nexj/Upgrade | main.upgrade ]]>
== 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
:Concept of Adapter [http://www.vogella.de/articles/DesignPatternAdapter/article.html]
* [[PostgreSQL_Adapter-nexj/Resources#MySQL_Specifications | MySQL Specifications]]
* [[PostgreSQL_Adapter-nexj/Resources#PostgreSQL_-_MySQL_Compare | Postgre - MySQL Compare]]
 
 
== Summary of Applied Research ==
* [[Research Findings - PostgreSQLAdapter]]
* [[Research Notes - PostgreSQLAdapter]]
Retrieved from "https://wiki.cdot.senecapolytechnic.ca/wiki/Special:MobileDiff/52248...104073"

Navigation menu