Changes

<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|- [[PostgreSQL_Adapter| Install the PostgreSQL Server| DONE|-nexj/test| Install PostgreSQL driver| DONE|-env/files | Files Add code to connect to Modify]]===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- STEPS 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> :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 ===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

===- ModificationsFiles to Configure==={|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;"| '''NOTE''': Changes Done to MySQL adapterthese files are involved with ''' |1- |: When a connection is first establishedPort''', this initial SQL statement which should execute in MySQL, which is not the case for PostgreSQL, so it was commented out::be:<source lang=java>/* MySQLAdapter.java */ public String getInitialSQL() { StringBuffer buf = new StringBuffer();  buf.append("set sql_mode = concat(@@sql_mode5432, 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.sql */set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');</source>: After activating java and creates the connection, the database is locked.: In MySQL, the storage engine is set to a transactional safe engine such as [http://wwwRelationalDatabase.wikivs.comjava /wiki/MySQL_vs_PostgreSQL#MySQL:InnoDB InnoDB], whereas PostgreSQL has a single built in engine. So, changes needed to be done to this script:Sets the default properties for the supported drivers::<source lang=RelationalDatabaseFragment.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 / Sets the process in postgresql_setup.sql, custom fragment properties for the database was created, initialized and tested:supported drivers:: A database called 'test is created Driver type and some other properties in PostgreSQL : <code> CREATE DATABASE test </code>JDBCInfo.java:: To create a <code>user<SQLSchemaManagerFactory.java /code> along with <code>password</code>: <code> CREATE USER test WITH PASSWORD 'test'; </code>:: Login to Sets the database 'test' with username 'test': <code> psql -U test test </code>:: Create a <code> Schema </code> called 'test' inside connection for the database 'test' with particular owner 'test'adapter type: <code> CREATE SCHEMA test AUTHORIZATION test;</code>:: Create a <code>table</code> called 'Mutex' with primary key: <code> CREATE TABLE testAllTests.Mutex(id INT PRIMARY KEY); </code>java:: Insert a value into the primary key: <code> INSERT INTO testdefault.Mutex(id) VALUES (1); <config //code>  == 3- Creating Schema change to these four files are: adapter="PostgreSQL" port="5432" : <code> CREATE TABLE test: postgresql.xxx </code>connection :: data types are changed in <code> appendColumnType() </code> in ''PostgreSQLSchemaManager.java'' based on each jdbc type's equivalent in PostgreSQLfilestorage.connections:: <code> crateTextTable </code> in :: The implementation in <code> CREATE INDEX test.xxx </code> in file ''PostgreSQLSchemaManager.java'' is removed for now (base class's version is being called)filestoragefragment.connections: <code> CREATE Trigger test.xxx // if it's needed for PostgreSQL as well as MySQL</code-->