Changes

Jump to: navigation, search

PostgreSQL Adapter

1,574 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 24: Connect to PostgreSQLPostgreSQLAdapter (DONE)===* Estimated Duration: 3 weeks (Feb 14 - 25, 2011)* [[PostgreSQL_Adapter-nexj/test-env extendingSQLAdapter | Create Test EnvironmentExtending SQLAdapter ]]
=== Phase 35: Test first design PostgreSQL UnitTests and Optimization (DONE) ===* 1Estimated Duration: 2 weeks (Feb 28 - Create a PostgreSQL database through some scripts: Through the process in postgresql_setup.sqlMar 11, the database was created, initialized and tested:2016):: A * Add database called 'test is created in specific tests fostr PostgreSQL : <code> CREATE DATABASE test </code>*:: To create a <code>user</code> along with <code>password</code>: <code> CREATE USER test WITH PASSWORD 'test'; </code>for required contrib scripts*:: Login to the database 'test' with username 'test': <code> psql -U test test </code>for custom blob read & write functions:: 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>* Add PostgreSQL specific optimization
===Phase 6: Megre Code with Latest Revision (DONE) ===* 3- Working inside Schema 'test'Merge code with lastest revision from http://mercurial.nexj.com/express
===Phase 7: <code> CREATE TABLE test.xxx </code>:: data types are changed in <code> appendColumnTypeTest with NexJ Express Studio (DONE) </code> in ''PostgreSQLSchemaManager.java'' based on each jdbc type's equivalent in PostgreSQL. ===* Estimated duration:: The implementation Mar 7- Apr 14, 2011* Run model in <code> crateTextTable </code> in file ''PostgreSQLSchemaManager.java'' is removed Express studio using the postgresql adapter* create scripts for now (base class's version is being called).database initialization, creation and dumping: <code>CREATE INDEX test.xxx </code> * create scripts for JBoss: <code> CREATE Trigger test.xxx // if it's needed for PostgreSQL as well as MySQL</code>* Deploy to JBoss
=== Phase 8: Code Review 1 - Meeting (DONE) ===* ModificationsDate: Apr 20,2011 {|class* Meet with NexJ Developer to review code =="collapsible collapsed" style="borderPhase 9: Code Review 1 -styleApply Changes (DONE) ===* Estimated Duration:solid;borderApr 20, 2011 -width:thin;borderJuly 1, 2011* [[PostgreSQL_Adapter_Project_-color_Code_Review_1_Changes | Changes to be made]] === Phase 10:black"Code Review 2 (DONE) ===!align* Send code for review* =="left" style="background-colorPhase 11:SteelBlue; fontCode Review 2 -weightApply Changes (DONE) ===* Estimated Duration:bold;borderJuly 7, 2011 -style:solid;borderJuly 15, 2011* [[PostgreSQL_Adapter_Project_-width:thin;padding: 2px 2px 2px 2px;"_Code_Review_2_Changes | '''Changes Done to MySQL adapter''' 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: When a connection is first established, ** implement set/getQueryTimeout** 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 outbytea and lo types === Phase 14:Integration Tests (DONE) ===* Run Integration tests against the Adapter === Phase 15::Code Review 4 (DONE)===<source lang=java>* Code review with framework architect* https://* MySQLAdapterbitbucket.java *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== Phase 17: Code Review 5 (@@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_createSubmitted code for review on Sep 20.sql *https:/set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');</source>: After activating the connection, the database is lockedbitbucket.: In MySQL, the storage engine is set to a transactional safe engine such as [http:org/gbatumbya/www.wikivs.compostgresql_external/wikisrc/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;<a0d10ef7cdbe/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 ===
== 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/52249...104073"

Navigation menu