<big><big> PostgreSQL Adapter for NexJ - Create Test Environment </big></big>{{NexJ_Express IndexAdmon/obsolete}}

[[category: 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|- Setup | Install the postgresqlPostgreSQL 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 ===For testing the connectionDownload: <code> JDBC3 Postgresql Driver, the 'PostgreSQLAdapterTestVersion 9.0-801 </code> @ http://jdbc.postgresql.java' is being usedorg/download.html * <br/>Install: Copy driver to <code>[[PostgreSQL_Adapter-nexjjdk folder]/jre/lib/test-envext</todo |TODOs]]code> * Files to configure based on === Code: Add PostgreSQL propertiesDriver ===Package: src<code>nexj.core.persistance.sql</code>:: Create <code>PostgreSQLAdapter.java </code> by extending <code>SQLAdapter</ Extends SQLAdapter.java - SQL Persistence adapter, responsible for regular data queries (insert, select, delete)code>:: Create <code>PostgreSQLSchemaManager.java </code> by extending <code>SQLSchemaManager</ Extends SQLSchemaManagercode> :Edit <code>RelationalDatabase.java - class for reading, creating and upgrading the database schema</code>:: systemEdit <code>RelationalDatabaseFragment.dstypejava</code>:: DatabaseToolEdit <code>SQLSchemaManagerFactory.java </code> Package: <code>nexj.core.meta.sys</ This file calles SQLSchemaMangerFactorycode>:Edit <code>system.java and creates the connectiondstypes</code> === SQL Scripts ===Package:: RelationalDatabase<code>nexj.core.persistence.sql.java script<// Sets the default properties for the supported driverscode>:: RelationalDatabaseFragmentCreate * <code>postgresql_setup.java sql</code>* <code>postgresql_drop.sql</ Sets the custom fragment properties for the supported driverscode>:: Driver type and some other properties in JDBCInfo* <code>postgresql_insert.javasql</code>:: SQLSchemaManagerFactory* <code>postgresql_upd_addr.java sql</code>* <code>postgresql_upd_cont.sql</ Sets the connection for the particular adapter typecode> === UnitTest: testTest Connectivity to database ===Package:: AllTests<code>nexj.core.persistance.javasql</code>:: Create <code>PostgreSQLAdapterTest.java </code> by extending <code>SQLAdapterTest</ Extends SQLAdapterTest.javacode>:: Create <code>PostgreSQLSchemaManagerTest.java </code> by extending <code>SQLSchemaManagerTest</ Extends SQLSchemaManagerTest.javacode> Package:: default.config <code>nexj<// System resources are being extracted from these 4 XML filescode>:: Create <code>postgresql.connectionconnections</code> using NexJ Studio:: filestorageEdit <code>default.connectionsconfig</code> to use PostgreSQL::filestoragefragmentEdit all <code>*.connections</code> files to use PostgreSQL: Script - scripts are being processed through SQLDataTest.java:: postgresql_setup.sql:: TO be cond'===Configure UnitTests for more than one Database===

* NOTES<!--== 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

public String getInitialSQL() { StringBuffer buf = new StringBuffer();==Files to Configure===  buf.append("set sql_mode = concat(@@sql_mode'''NOTE''': Changes to these files are involved with '''1-Port''', which should be: 5432, and '''2- Driver package'',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 that should change to examine plans, improves long planning sessions buf.append(";set max_sort_length = ") <code> org.append(Mathpostgresql.max(MAX_VARCHAR_PRECISION, MAX_VARBINARY_PRECISION)); // set TEXT/BLOB minimum sorting length to be same as cutoff between varchar/text return bufxa.toString(); }PGXADataSource </sourcecode>: Also the same line should be commented out in postgresql_createsrc:: system.sql script, which is being used to create tables in the database 'test':dstype::<source lang=DatabaseTool.java> /* nexj/core/persistence/sql/etc/postgresql_createThis file calles SQLSchemaMangerFactory.java and creates the connection:: RelationalDatabase.sql *java /set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');</source>: After activating Sets the connection, default properties for the database is locked.supported drivers: In MySQL, the storage engine is set to a transactional safe engine such as [http://wwwRelationalDatabaseFragment.wikivs.comjava /wiki/MySQL_vs_PostgreSQL#MySQLSets the custom fragment properties for the supported drivers:InnoDB InnoDB], whereas PostgreSQL has a single built : Driver type and some other properties in engineJDBCInfo. So, changes needed to be done to this script:java::<source lang=SQLSchemaManagerFactory.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 scripts===: Through / Sets the process in postgresql_setup.sql, connection for the database was created, initialized and testedparticular adapter type:test:: A database called 'test is created in PostgreSQL : <code> CREATE DATABASE test </code>AllTests.java:: To create a <code>user</code> along with <code>password<default.config /code>: <code> CREATE USER test WITH PASSWORD 'test'; </code>:: Login change to the database 'test' with username 'test'these four files are: <code> psql -U test test </code>adapter="PostgreSQL" port="5432":: Create a <code> Schema </code> called 'test' inside the database 'test' with particular owner 'test': <code> CREATE SCHEMA test AUTHORIZATION test;</code>postgresql.connection :: Create a <code>table</code> called 'Mutex' with primary key: <code> CREATE TABLE testfilestorage.Mutex(id INT PRIMARY KEY); </code>connections:: Insert a value into the primary key: <code> INSERT INTO testfilestoragefragment.Mutex(id) VALUES (1); </codeconnections-->