Difference between revisions of "NexJ Express Connecting to PostgreSQL"

From CDOT Wiki
Jump to: navigation, search
(→‎- Modifications)
 
(33 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
<big><big> PostgreSQL Adapter for NexJ - Create Test Environment </big></big>
+
{{Admon/obsolete}}
−
{{NexJ_Express Index}}
 
  
−
== 1- Setup the postgresql-connection ==
+
[[category: NexJ Express PostgreSQL]][[Category:NexJ_Express_Development]][[category: NexJ Express]]
−
For testing the connection, the 'PostgreSQLAdapterTest.java' is being used.
 
−
=== - [[PostgreSQL_Adapter-nexj/test-env/todo |TODOs]]===
 
  
−
=== - [[PostgreSQL_Adapter-nexj/test-env/files | Files to Modify]]===
+
== Task Summary ==
 +
{| border="1" cellpadding="4" cellspacing="2" style="border: 1px solid black;border-collapse:collapse;"
 +
|-
 +
! Task
 +
! Status
 +
|-
 +
| Install the PostgreSQL Server
 +
| DONE
 +
|-
 +
| Install PostgreSQL driver
 +
| DONE
 +
|-
 +
| Add code to connect to server
 +
| DONE
 +
|-
 +
| Create Script for Test database
 +
| DONE
 +
|-
 +
| Test connection to the server
 +
| DONE
 +
|-
 +
| Configure UnitTests for multiple DB's
 +
|
 +
|}
 +
 
 +
== Task Details ==
 +
=== Install PostgreSQL ===
 +
Download: <code>Version 9.0</code> from list of [http://www.enterprisedb.com/products/pgdownload.do#windows PostgreSQL downloads].
 +
 
 +
=== Install PostgreSQL JDBC3 driver ===
 +
Download: <code> JDBC3 Postgresql Driver, Version 9.0-801 </code> @ http://jdbc.postgresql.org/download.html
 +
<br/>Install: Copy driver to <code>[jdk folder]/jre/lib/ext</code>
 +
 
 +
=== Code: Add PostgreSQL Driver ===
 +
Package: <code>nexj.core.persistance.sql</code>
 +
:Create <code>PostgreSQLAdapter.java</code> by extending <code>SQLAdapter</code>
 +
:Create <code>PostgreSQLSchemaManager.java</code> by extending <code>SQLSchemaManager</code>
  
−
===- STEPS for Connection===
+
:Edit <code>RelationalDatabase.java</code>
−
: Installed the <code> JDBC3 Postgresql Driver, Version 9.0-801 </code> and added it to the libraries in JDK
+
:Edit <code>RelationalDatabaseFragment.java</code>
 +
:Edit <code>SQLSchemaManagerFactory.java</code>
 +
 
 +
Package: <code>nexj.core.meta.sys</code>
 +
:Edit <code>system.dstypes</code>
 +
 
 +
=== SQL Scripts ===
 +
Package: <code>nexj.core.persistence.sql.script</code>
 +
:Create
 +
* <code>postgresql_setup.sql</code>
 +
* <code>postgresql_drop.sql</code>
 +
* <code>postgresql_insert.sql</code>
 +
* <code>postgresql_upd_addr.sql</code>
 +
* <code>postgresql_upd_cont.sql</code>
 +
 
 +
=== UnitTest: Test Connectivity to database ===
 +
Package: <code>nexj.core.persistance.sql</code>
 +
:Create <code>PostgreSQLAdapterTest.java</code> by extending <code>SQLAdapterTest</code>
 +
:Create <code>PostgreSQLSchemaManagerTest.java</code> by extending <code>SQLSchemaManagerTest</code>
 +
 
 +
Package: <code>nexj</code>
 +
:Create <code>postgresql.connections</code> using NexJ Studio
 +
:Edit <code>default.config</code> to use PostgreSQL
 +
:Edit all <code>*.connections</code> files to use PostgreSQL
 +
 
 +
===Configure UnitTests for more than one Database===
 +
 
 +
<!--
 +
== Setup the postgresql-connection ==
 +
For testing the connection, the 'AllTests.java' is being used.
 +
: Installed the <code> Installer version Version 9.0.1-1</code> from list of [http://www.enterprisedb.com/products/pgdownload.do#windows PostgreSQL downloads].
 +
: Installed the <code> JDBC3 Postgresql Driver, Version 9.0-801 </code> @ http://jdbc.postgresql.org/download.html and added it to the libraries in JDK
 
:: The driver class which is being used for NexJ Express Model is: <code> org.postgresql.xa.PGXADataSource </code>
 
:: The driver class which is being used for NexJ Express Model is: <code> org.postgresql.xa.PGXADataSource </code>
 
:: The driver is loaded from RelationalDatabaseFragment.java and RelationalDatabase.java
 
:: The driver is loaded from RelationalDatabaseFragment.java and RelationalDatabase.java
Line 30: Line 94:
 
: After all properties are loaded, it creates the connection through Connection Factory files.
 
: After all properties are loaded, it creates the connection through Connection Factory files.
  
−
===- Modifications===
+
===Files to Configure===
−
{|class="collapsible collapsed" style="border-style:solid;border-width:thin;border-color:black"
+
'''NOTE''': Changes to these files are involved with '''1-Port''', which should be: 5432, and '''2- Driver package''' that should change to <code> org.postgresql.xa.PGXADataSource </code>
−
!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'''  
+
: src
−
|-  
+
:: system.dstype
−
|
+
:: DatabaseTool.java // This file calles SQLSchemaMangerFactory.java and creates the connection
−
: 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:
+
:: RelationalDatabase.java // Sets the default properties for the supported drivers
−
::
+
:: RelationalDatabaseFragment.java // Sets the custom fragment properties for the supported drivers
−
<source lang=java>
+
:: Driver type and some other properties in JDBCInfo.java
−
/* MySQLAdapter.java */
+
:: SQLSchemaManagerFactory.java  // Sets the connection for the particular adapter type
−
 
+
: test
−
public String getInitialSQL()
+
:: AllTests.java
−
  {
+
:: default.config  // change to these four files are: adapter="PostgreSQL" port="5432"
−
      StringBuffer buf = new StringBuffer();
+
:: postgresql.connection
−
 
+
:: filestorage.connections
−
      buf.append("set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES')"); // allow using doublequote when quoting column names in "CREATE TABLE" statements
+
::filestoragefragment.connections
−
      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>
 
−
|}
 
−
 
 
−
== 2- 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 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>
 
−
 
 
−
== 3- Working inside Schema 'test'==
 
−
 
 
−
: <code> CREATE TABLE test.xxx </code>
 
−
:: data types are changed in <code> appendColumnType() </code> in ''PostgreSQLSchemaManager.java'' based on each jdbc type's equivalent in PostgreSQL.
 
−
:: The implementation in  <code> crateTextTable </code> in file ''PostgreSQLSchemaManager.java'' is removed for now (base class's version is being called).
 
−
: <code>CREATE INDEX test.xxx </code>
 
−
: <code> CREATE Trigger test.xxx // if it's needed for PostgreSQL as well as MySQL</code>
 

Latest revision as of 19:30, 26 January 2014

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

Task Summary

Task Status
Install the PostgreSQL Server DONE
Install PostgreSQL driver DONE
Add code to connect to server DONE
Create Script for Test database DONE
Test connection to the server DONE
Configure UnitTests for multiple DB's

Task Details

Install PostgreSQL

Download: Version 9.0 from list of PostgreSQL downloads.

Install PostgreSQL JDBC3 driver

Download: JDBC3 Postgresql Driver, Version 9.0-801 @ http://jdbc.postgresql.org/download.html
Install: Copy driver to [jdk folder]/jre/lib/ext

Code: Add PostgreSQL Driver

Package: nexj.core.persistance.sql

Create PostgreSQLAdapter.java by extending SQLAdapter
Create PostgreSQLSchemaManager.java by extending SQLSchemaManager
Edit RelationalDatabase.java
Edit RelationalDatabaseFragment.java
Edit SQLSchemaManagerFactory.java

Package: nexj.core.meta.sys

Edit system.dstypes

SQL Scripts

Package: nexj.core.persistence.sql.script

Create
  • postgresql_setup.sql
  • postgresql_drop.sql
  • postgresql_insert.sql
  • postgresql_upd_addr.sql
  • postgresql_upd_cont.sql

UnitTest: Test Connectivity to database

Package: nexj.core.persistance.sql

Create PostgreSQLAdapterTest.java by extending SQLAdapterTest
Create PostgreSQLSchemaManagerTest.java by extending SQLSchemaManagerTest

Package: nexj

Create postgresql.connections using NexJ Studio
Edit default.config to use PostgreSQL
Edit all *.connections files to use PostgreSQL

Configure UnitTests for more than one Database

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