Open main menu

CDOT Wiki β

Changes

NexJ Express Connecting to PostgreSQL

240 bytes added, 20:30, 26 January 2014
no edit summary
<big><big> PostgreSQL Adapter for NexJ - Create Test Environment </big></big>{{NexJ_Express IndexAdmon/obsolete}}
== 1- Setup the postgresql-connection ==For testing the connection, the 'PostgreSQLAdapterTest.java' is being used.=== - [[PostgreSQL_Adapter-nexj/test-env/todo |TODOscategory: NexJ Express PostgreSQL]][[Category:NexJ_Express_Development]][[category: NexJ Express]]===
==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 [[PostgreSQL_Adapterhttp://www.enterprisedb.com/products/pgdownload.do#windows PostgreSQL downloads]. === Install PostgreSQL JDBC3 driver ===Download: <code> JDBC3 Postgresql Driver, Version 9.0-nexj801 </code> @ http://jdbc.postgresql.org/test-envdownload.html <br/files |Files >Install: Copy driver to Modify]<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>
:Edit <code>RelationalDatabase.java</code>: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 === STEPS Configure UnitTests for Connectionmore 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 is loaded from RelationalDatabaseFragment.java and RelationalDatabase.java
</source>
: After all properties are loaded, it creates the connection through Connection Factory files.
===Modifications===
{|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;"| '''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()
{
StringBuffer buf = new StringBuffer();
 
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)
);
</source>
 
 
 
|}
=== 2- Create a PostgreSQL database through some scriptsFiles to Configure==='''NOTE''': Through the process in postgresql_setup.sqlChanges to these files are involved with '''1-Port''', the database was createdwhich should be: 5432, initialized and tested::: A database called 'test is created in PostgreSQL : ''2- Driver package''' that should change to <code> CREATE DATABASE test org.postgresql.xa.PGXADataSource </code>:src: To create a <code>user<: system.dstype:: DatabaseTool.java /code> along with <code>password</code>This file calles SQLSchemaMangerFactory.java and creates the connection:: <code> CREATE USER test WITH PASSWORD 'test'; <RelationalDatabase.java //code>Sets the default properties for the supported drivers:: Login to RelationalDatabaseFragment.java // Sets the custom fragment properties for the database 'test' with username 'test'supported drivers:: <code> psql -U test test </code>Driver type and some other properties in JDBCInfo.java:: Create a <code> Schema <SQLSchemaManagerFactory.java //code> called 'test' inside Sets the connection for the database 'test' with particular owner 'adapter type: test':: <code> CREATE SCHEMA test AUTHORIZATION test;</code>AllTests.java:: Create a <code>table<default.config //code> called 'Mutex' with primary keychange to these four files are: adapter="PostgreSQL" port="5432":: <code> CREATE TABLE testpostgresql.Mutex(id INT PRIMARY KEY); </code>connection :: Insert a value into the primary keyfilestorage.connections:: <code> INSERT INTO testfilestoragefragment.Mutex(id) VALUES (1); </codeconnections-->
Retrieved from "https://wiki.cdot.senecapolytechnic.ca/wiki/Special:MobileDiff/52223...104071"