Changes

Jump to: navigation, search

PostgreSQL Adapter

13 bytes removed, 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.
|}
 * [[PostgreSQL_Adapter-nexj/test-env/todo |TODOs]]>
==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 PostgreSQLserver (DONE) ===* [[PostgreSQL_Adapter-nexj/test-env NexJ_Express_Connecting_to_PostgreSQL| Create Test EnvironmentConnect to PostgreSQL server]]
{|class="collapsible collapsed" style="border-style= Phase 3: PostgreSQLSchemaManager (DONE) ===* Estimated Duration:solid;border3 weeks (Jan 17 -width:thin;borderFeb 11, 2011)* [[PostgreSQL Adapter-color:black"nexj/extendingSchemaManager | Extending SQLSchemaManager ]] !align="left" style="background-color= Phase 4:SteelBlue; font-weightPostgreSQLAdapter (DONE)===* Estimated Duration:bold;border3 weeks (Feb 14 -style:solid;border25, 2011)* [[PostgreSQL_Adapter-widthnexj/extendingSQLAdapter | Extending SQLAdapter ]] ===Phase 5:thin;paddingPostgreSQL UnitTests and Optimization (DONE) ===* Estimated Duration: 2px 2px 2px 2px;"| '''Files to Configure''' |2 weeks (Feb 28 - Mar 11, 2016)|* Add database specific tests fostr PostgreSQL*: srctest for required contrib scripts*:test for custom blob read & write functions* Add PostgreSQL specific optimization ===Phase 6: system.dstypeMegre Code with Latest Revision (DONE) ===* Merge code with lastest revision from http:: DatabaseTool.java // This file calles SQLSchemaMangerFactorymercurial.java and creates the connectionnexj.com/express ===Phase 7:Test with NexJ Express Studio (DONE) ===* Estimated duration: RelationalDatabase.java // Sets Mar 7- Apr 14, 2011* Run model in Express studio using the default properties postgresql adapter* create scripts for database initialization, creation and dumping* create scripts for the supported driversJBoss* Deploy to JBoss === Phase 8:Code Review 1 - Meeting (DONE) ===* Date: RelationalDatabaseFragment.java // Sets the custom fragment properties for the supported driversApr 20,2011 * Meet with NexJ Developer to review code === Phase 9:Code Review 1 - Apply Changes (DONE) ===* Estimated Duration: Driver type and some other properties in JDBCInfo.javaApr 20, 2011 - July 1, 2011* [[PostgreSQL_Adapter_Project_-_Code_Review_1_Changes | Changes to be made]] === Phase 10:: SQLSchemaManagerFactory.java // Sets the connection Code Review 2 (DONE) ===* Send code for the particular adapter typereview* === Phase 11: testCode 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: AllTests.javaCode Review 3 (DONE) ===* Sent code for code review 3* https:: default//bitbucket.config org/gbatumbya/postgresql_external/src/08cdedb9d071/ System resources are being extracted from these 4 XML files === Phase 13:: postgresqlCode Review 3 - Apply Changes (DONE) ===* Upgrade to lastest 7.connection1 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: filestorage.connectionsIntegration Tests (DONE) ===::filestoragefragment.connections* Run Integration tests against the Adapter
=== Phase 315: Test first design Code Review 4 (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 </code>:: To create a <code>user</code> along Code review with <code>password</code>: <code> CREATE USER test WITH PASSWORD 'test'; </code>framework architect* https:: 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;<bitbucket.org/code>:: Create a <code>table<gbatumbya/code> called 'Mutex' with primary key: <code> CREATE TABLE test.Mutex(id INT PRIMARY KEY); <postgresql_external/code>:: Insert a value into the primary key: <code> INSERT INTO test.Mutex(id) VALUES (1); <src/code>6c9a7380c8fe
=== Phase 16: Code Review 4 - Apply Changes (DONE) ===* Estimated Duration: Sep 9 - Sep 12* 3[[PostgreSQL_Adapter_Project_- Working inside Schema 'test'_Code_Review_4_Changes | Changes to be made]]
=== Phase 17: <code> CREATE TABLE test.xxx </code>:: data types are changed in <code> appendColumnTypeCode Review 5 (DONE) </===* Submitted code> in ''PostgreSQLSchemaManager.java'' based for review on each jdbc type's equivalent in PostgreSQLSep 20.* https:: The implementation in <code> crateTextTable </code> in file ''PostgreSQLSchemaManager/bitbucket.java'' is removed for now (base class's version is being called).: <code>CREATE INDEX test.xxx <org/gbatumbya/code> : <code> CREATE Trigger test.xxx postgresql_external/src/ if it's needed for PostgreSQL as well as MySQL<a0d10ef7cdbe/code>
* Modifications{|class="collapsible collapsed" style="border-style= Phase 18:solid;borderCode Review 5 -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 to MySQL adapter''' |- |: 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:::<source lang=java>/* MySQLAdapter.java */
public String getInitialSQL()=== Phase 19: Code Review 6 ===* Date: Oct 13 {* Submitted for code review StringBuffer buf = new StringBuffer();* https://bitbucket.org/gbatumbya/postgresql_external/src/153aec847d54/
buf.append("set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES')"); // 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>
: 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>
|}
{|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;"| '''Files to Configure'''
|-
|
:: [[ PostgreSQL_Adapter-nexj/Adapter | PostgreSQLAdapter.java ]] // Extends SQLAdapter.java - SQL Persistence adapter, responsible for regular data queries (insert, select, delete)
:: [[ PostgreSQL_Adapter-nexj/SchemaManger | PostgreSQLSchemaManager.java ]] // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema
:: [[ PostgreSQL_Adapter-nexj/Upgrade | main.upgrade ]]
: test
:: [[ PostgreSQL_Adapter-nexj/AdapterTest | PostgreSQLAdapterTest.java ]]] // Extends SQLAdapterTest.java
:: [[ PostgreSQL_Adapter-nexj/SchemaMangerTest | PostgreSQLSchemaManagerTest.java ]] // Extends SQLSchemaManagerTest.java
: Script - scripts are being processed through SQLDataTest.java
:: postgresql_setup.sql
:: TO be cond'
|}
==Project Repository =Phase 4=* URL for central repository of the project is: Test properties specific to PostgreSQL ===https://bitbucket.org/gbatumbya/nexjexpress-postgresqladapter
* Add test cases that are specific Steps to PostgreSQL databasework with the repository
=== Phase :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: Optimization===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'
*After implementation is done'''NOTE!''' To avoid any conflict, optimizing do the modified files'Pull' every day and before any push.* '''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.-->
== 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/52322...104073"

Navigation menu