001 package com.sptci.rwt;
002
003 import java.util.List;
004
005 import static junit.framework.Assert.*;
006 import junit.framework.Test;
007 import junit.framework.TestCase;
008 import junit.framework.TestSuite;
009
010 import com.sptci.util.CloseJDBCResources;
011
012 /**
013 * Unit test setup class for creating test database objects to use in
014 * the unit test suite. This also functions as a rudimentary test for
015 * the {@link BatchQueryExecutor} class.
016 *
017 * <p>© Copyright 2007 <a href='http://sptci.com/' target='_new'>Sans Pareil Technologies, Inc.</a></p>
018 * @author Rakesh Vidyadharan 2007-09-25
019 * @version $Id: CreateTestObjects.java 4123 2008-05-25 21:49:01Z rakesh $
020 */
021 public class CreateTestObjects extends TestCase
022 {
023 static ConnectionParameters parameters;
024 static ConnectionManager manager;
025 static final String tableName = "test" + System.currentTimeMillis();
026 static final String refTableName = "testref" + System.currentTimeMillis();
027 static final String viewName = "testView" + System.currentTimeMillis();
028 static final String functionName = "testFunction" + System.currentTimeMillis();
029 static final String triggerName = "testTrigger" + System.currentTimeMillis();
030 static final String sequenceName = "testSequence" + System.currentTimeMillis();
031 static final String indexName = "testIndex" + System.currentTimeMillis();
032
033 public static Test suite()
034 {
035 return new TestSuite( CreateTestObjects.class );
036 }
037
038 /**
039 * Test creating tables using {@link BatchQueryExecutor}.
040 */
041 public void testCreate() throws Exception
042 {
043 parameters = getParameters();
044 assertNotNull( "Checking initialised ConnectionParameters", parameters );
045 manager = new ConnectionManager( parameters );
046 createTable();
047 }
048
049 /**
050 * Fetch the connection parameters from the build properties file
051 */
052 protected ConnectionParameters getParameters()
053 {
054 final String type = System.getProperty( "database.type" );
055 assertNotNull( "Checking database.type", type );
056
057 final String driver = System.getProperty( "jdbc.driver" );
058 assertNotNull( "Checking JDBC driver", driver );
059
060 final String url = System.getProperty( "url.pattern" );
061 assertNotNull( "Checking url.pattern", url );
062
063 final String host = System.getProperty( "database.host" );
064 assertNotNull( "Checking database host", host );
065
066 final String name = System.getProperty( "database.name" );
067 assertNotNull( "Checking databsae name", name );
068
069 final String port = System.getProperty( "database.port" );
070 assertNotNull( "Checking port", port );
071
072 final String user = System.getProperty( "database.user" );
073 assertNotNull( "Checking databsae user", user );
074
075 final String password = System.getProperty( "database.password" );
076 assertNotNull( "Checking password", password );
077
078 return new ConnectionParameters( user, password, host,
079 Integer.parseInt( port ), name, type, url, driver );
080 }
081
082 /**
083 * Create a test table to ensure that there is at least one table for
084 * testing analysers.
085 */
086 protected void createTable() throws Exception
087 {
088 String ddl = "create table $TABLENAME$ ( \n" +
089 "name character varying(100) not null, \n" +
090 "description character varying(1000) null, \n" +
091 "constraint pk_$TABLENAME$ primary key (name) ); \n" +
092 "comment on table $TABLENAME$ is 'A unit test table'; \n" +
093 "create table $REFTABLENAME$ ( \n" +
094 "id numeric(9) not null, \n" +
095 "name character varying(100) not null, \n" +
096 "constraint pk_$REFTABLENAME$ primary key (id,name), \n" +
097 "constraint fk_$TABLENAME$_$REFTABLENAME$ " +
098 "foreign key (name) references $TABLENAME$ " +
099 "(name) on delete cascade ); \n" +
100 "create index idx_name_$REFTABLENAME$ on $REFTABLENAME$ (name); \n" +
101 "comment on table $REFTABLENAME$ is 'A unit test reference table'; \n" +
102 "create view $VIEWNAME$ as \n" +
103 "select b.id, a.name, a.description \n" +
104 "from $TABLENAME$ a, $REFTABLENAME$ b \n" +
105 "where a.name = b.name \n" +
106 "order by a.name; \n" +
107 "comment on view $VIEWNAME$ is 'A unit test schema'; \n" +
108 "create function $FUNCTIONNAME$() returns trigger as $func_body$\n" +
109 "begin select old.*; return old; end; \n" +
110 "$func_body$ language plpgsql; \n" +
111 "create trigger $TRIGGERNAME$ after insert or update or delete on $REFTABLENAME$ \n" +
112 " for each row execute procedure $FUNCTIONNAME$();\n" +
113 "create sequence $SEQUENCENAME$;\n" +
114 "create index $INDEXNAME$ on $REFTABLENAME$ (id,name);\n";
115 ddl = ddl.replaceAll( "\\$TABLENAME\\$", tableName );
116 ddl = ddl.replaceAll( "\\$REFTABLENAME\\$", refTableName );
117 ddl = ddl.replaceAll( "\\$VIEWNAME\\$", viewName );
118 ddl = ddl.replaceAll( "\\$FUNCTIONNAME\\$", functionName );
119 ddl = ddl.replaceAll( "\\$TRIGGERNAME\\$", triggerName );
120 ddl = ddl.replaceAll( "\\$SEQUENCENAME\\$", sequenceName );
121 ddl = ddl.replaceAll( "\\$INDEXNAME\\$", indexName );
122 final int ddlCount = 11;
123
124 BatchQueryExecutor executor = new BatchQueryExecutor( manager );
125 List<Rows> rows = executor.execute( ddl );
126
127 System.out.format( "Created test tables: %s, %s and view: %s%n",
128 tableName, refTableName, viewName );
129
130 assertEquals( "Ensuring ddlCount results returned",
131 rows.size(), ddlCount );
132 assertEquals( "Ensuring single row results returned",
133 rows.get( 0 ).getRows().size(), 1 );
134 assertEquals( "Ensuring only one column in row results",
135 rows.get( 0 ).getRows().get( 0 ).getColumns().size(), 1 );
136 }
137 }