Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > postgresql > PostgreSQLDatabaseTestSchema


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: PostgreSQLDatabaseTestSchema.java,v 1.17 2007/01/07 06:15:36 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */
21
22 package org.opensubsystems.core.persist.db.driver.postgresql;
23
24 import java.sql.CallableStatement ;
25 import java.sql.Connection ;
26 import java.sql.PreparedStatement ;
27 import java.sql.ResultSet ;
28 import java.sql.SQLException ;
29 import java.sql.Statement ;
30 import java.sql.Types ;
31 import java.util.logging.Level ;
32 import java.util.logging.Logger ;
33
34 import org.opensubsystems.core.error.OSSException;
35 import org.opensubsystems.core.persist.db.driver.DatabaseTestSchema;
36 import org.opensubsystems.core.util.DatabaseUtils;
37 import org.opensubsystems.core.util.Log;
38
39 /**
40  * This class encapsulates details about creation and upgrade
41  * of database schema required to test database driver functionality
42  * for tables which are Postgre SQL database specific
43  *
44  * @version $Id: PostgreSQLDatabaseTestSchema.java,v 1.17 2007/01/07 06:15:36 bastafidli Exp $
45  * @author Julo Legeny
46  * @code.reviewer Miro Halas
47  * @code.reviewed 1.14 2005/02/26 21:13:42 bastafidli
48  */
49 public class PostgreSQLDatabaseTestSchema extends DatabaseTestSchema
50 {
51    /*
52       These tables are database specific
53       
54       CREATE SEQUENCE GENERATEDKEY_TEST_SEQ INCREMENT BY 1 START WITH 1 NO CYCLE
55       
56       CREATE TABLE GENERATEDKEY_TEST
57       (
58          TEST_KEY INTEGER NOT NULL PRIMARY KEY,
59          TEST_VALUE VARCHAR(50) NOT NULL
60       )
61       
62       CREATE TYPE type_key_insertcount AS (key INTEGER, insert_count INTEGER);
63
64       CREATE OR REPLACE FUNCTION insert_generatedkey_test(
65          VARCHAR(50)
66       ) RETURNS type_key_insertcount AS '
67       DECLARE
68          in_value ALIAS FOR $1;
69          out_key INTEGER;
70          insert_count INTEGER;
71          output_result type_key_insertcount;
72       BEGIN
73          out_key := NEXTVAL(''generatedkey_test_seq'');
74          INSERT INTO generatedkey_test(test_key, test_value) VALUES (out_key, in_value);
75          output_result.key := out_key;
76          output_result.insert_count := insert_count;
77          RETURN output_result;
78       END
79       ' LANGUAGE 'plpgsql';
80
81       -- function returns number of updated records
82       CREATE OR REPLACE FUNCTION insert_row_count_test (
83          VARCHAR(50)
84       ) RETURNS INTEGER AS '
85       DECLARE
86          in_value ALIAS FOR $1;
87          out_rows INTEGER;
88       BEGIN
89          INSERT INTO generatedkey_test(test_key, test_value)
90                 VALUES (NEXTVAL(''generatedkey_test_seq''), in_value);
91          -- get number of inserted rows
92          GET DIAGNOSTICS out_rows = ROW_COUNT;
93          RETURN out_rows;
94       END
95       ' LANGUAGE 'plpgsql';
96
97       -- function returns number of updated records
98       CREATE OR REPLACE FUNCTION update_transaction_test_value(
99          VARCHAR(50),
100          VARCHAR(50)
101       ) RETURNS INTEGER AS '
102       DECLARE
103          old_test_value ALIAS FOR $1;
104          new_test_value ALIAS FOR $2;
105          updated_count INTEGER;
106       BEGIN
107          UPDATE transaction_test SET test_value = new_test_value
108                 WHERE test_value = old_test_value;
109          -- get number of updated rows
110          GET DIAGNOSTICS updated_count = ROW_COUNT;
111          RETURN updated_count;
112       END
113       ' LANGUAGE 'plpgsql';
114    */
115    
116    // Cached values ////////////////////////////////////////////////////////////
117
118    /**
119     * Logger for this class
120     */
121    private static Logger s_logger = Log.getInstance(PostgreSQLDatabaseTestSchema.class);
122
123    // Constructors /////////////////////////////////////////////////////////////
124
125    /**
126     * Default constructor.
127     *
128     * @throws OSSException - error occured.
129     */
130    public PostgreSQLDatabaseTestSchema(
131    ) throws OSSException
132    {
133       super();
134    }
135
136    // Lifecycle events /////////////////////////////////////////////////////////
137
138    /**
139     * {@inheritDoc}
140     */
141    public void create(
142       Connection cntDBConnection,
143       String strUserName
144    ) throws SQLException
145    {
146       // First create any generic tables
147 super.create(cntDBConnection, strUserName);
148
149       // Now try to create any database specific tables
150 Statement stmQuery = null;
151       try
152       {
153          stmQuery = cntDBConnection.createStatement();
154
155          if (stmQuery.execute("CREATE SEQUENCE generatedkey_test_seq " +
156                               "INCREMENT BY 1 START WITH 1 NO CYCLE"))
157          {
158             // Close any results
159 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
160          }
161          s_logger.log(Level.FINEST, "Sequence GENERATEDKEY_TEST_SEQ created.");
162          /*
163          if (stmQuery.execute("grant all on GENERATEDKEY_TEST_SEQ to " + strUserName))
164          {
165             // Close any results
166             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
167          }
168          Log.getLogger().log(Level.FINEST,
169                              "Access for sequence GENERATEDKEY_TEST_SEQ set for user "
170                              + strUserName);
171          */
172
173          ///////////////////////////////////////////////////////////////////////
174
175          if (stmQuery.execute("CREATE TYPE type_key_insertcount AS (key INTEGER, " +
176                               "insert_count INTEGER)"))
177          {
178             // Close any results
179 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
180          }
181          s_logger.log(Level.FINEST, "Type TYPE_KEY_INSERTCOUNT created.");
182          /*
183          if (stmQuery.execute("grant all on GENERATEDKEY_TEST_SEQ to " + strUserName))
184          {
185             // Close any results
186             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
187          }
188          Log.getLogger().log(Level.FINEST,
189                "Access for sequence GENERATEDKEY_TEST_SEQ set for user "
190                + strUserName);
191          */
192
193          ///////////////////////////////////////////////////////////////////////
194
195          if (stmQuery.execute("CREATE TABLE GENERATEDKEY_TEST" + NL +
196                               "(" + NL +
197                               " TEST_KEY INTEGER NOT NULL PRIMARY KEY," + NL +
198                               " TEST_VALUE VARCHAR(50) NOT NULL" + NL +
199                               ")"))
200          {
201             // Close any results
202 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
203          }
204          s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created.");
205          /*
206          if (stmQuery.execute("grant all on GENERATEDKEY_TEST to " + strUserName))
207          {
208             // Close any results
209             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
210          }
211          s_logger.log(Level.FINEST,
212                       "Access for table GENERATEDKEY_TEST set for user "
213                       + strUserName);
214          */
215
216          ///////////////////////////////////////////////////////////////////////
217
218          if (stmQuery.execute("CREATE OR REPLACE FUNCTION insert_generatedkey_test " + NL +
219                               "(" + NL +
220                               " VARCHAR(50)" + NL +
221                               ")" + NL +
222                               "RETURNS type_key_insertcount AS ' " + NL +
223                               "DECLARE " + NL +
224                               "in_value ALIAS FOR $1; " + NL +
225                               "out_key INTEGER; " + NL +
226                               "insert_count INTEGER; " + NL +
227                               "output_result type_key_insertcount; " + NL +
228                               "BEGIN " + NL +
229                               " out_key := NEXTVAL(''generatedkey_test_seq''); " + NL +
230                               " INSERT INTO generatedkey_test(test_key, test_value) " + NL +
231                               " VALUES (out_key, in_value); " + NL +
232                               " GET DIAGNOSTICS insert_count = ROW_COUNT; " + NL +
233                               " output_result.key := out_key; " + NL +
234                               " output_result.insert_count := insert_count; " + NL +
235                               " RETURN output_result; " + NL +
236                               "END " + NL +
237                               "' LANGUAGE 'plpgsql';"))
238          {
239             // Close any results
240 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
241          }
242          s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created.");
243          /*
244          if (stmQuery.execute("grant execute on INSERT_GENERATEDKEY_TEST to " + strUserName))
245          {
246             // Close any results
247             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
248          }
249          Log.getLogger().log(Level.FINEST,
250                              "Access for procedure INSERT_GENERATEDKEY_TEST set for user "
251                              + strUserName);
252          */
253
254          ///////////////////////////////////////////////////////////////////////
255
256          if (stmQuery.execute("CREATE OR REPLACE FUNCTION insert_row_count_test " + NL +
257                               "(" + NL +
258                               " VARCHAR(50)" + NL +
259                               ")" + NL +
260                               "RETURNS INTEGER AS ' " + NL +
261                               "DECLARE " + NL +
262                               " in_value ALIAS FOR $1; " + NL +
263                               " out_rows INTEGER; " + NL +
264                               " insert_count INTEGER; " + NL +
265                               "BEGIN " + NL +
266                               " INSERT INTO generatedkey_test(test_key, test_value) " + NL +
267                               " VALUES (NEXTVAL(''generatedkey_test_seq''), in_value); " + NL +
268                               " GET DIAGNOSTICS out_rows = ROW_COUNT; " + NL +
269                               " RETURN out_rows; " + NL +
270                               "END " + NL +
271                               "' LANGUAGE 'plpgsql';"))
272          {
273             // Close any results
274 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
275          }
276          s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created.");
277          /*
278          if (stmQuery.execute("grant execute on INSERT_GENERATEDKEY_TEST to " + strUserName))
279          {
280             // Close any results
281             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
282          }
283          Log.getLogger().log(Level.FINEST,
284                              "Access for procedure INSERT_GENERATEDKEY_TEST set for user "
285                              + strUserName);
286          */
287
288          ///////////////////////////////////////////////////////////////////////
289
290          if (stmQuery.execute("CREATE OR REPLACE FUNCTION update_transaction_test_value" + NL +
291                               "(" + NL +
292                               " VARCHAR(50), " + NL +
293                               " VARCHAR(50)" + NL +
294                               ") " + NL +
295                               "RETURNS INTEGER AS ' " + NL +
296                               "DECLARE " + NL +
297                               " old_test_value ALIAS FOR $1; " + NL +
298                               " new_test_value ALIAS FOR $2; " + NL +
299                               " updated_count INTEGER; " + NL +
300                               "BEGIN " + NL +
301                               " UPDATE transaction_test SET " + NL +
302                               " test_value = new_test_value " + NL +
303                               " WHERE test_value = old_test_value; " + NL +
304                               " GET DIAGNOSTICS updated_count = ROW_COUNT; " + NL +
305                               " RETURN updated_count; " + NL +
306                               "END " + NL +
307                               "' LANGUAGE 'plpgsql';"))
308          {
309             // Close any results
310 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
311          }
312          s_logger.log(Level.FINEST, "Procedure UPDATE_TRANSACTION_TEST_VALUE created.");
313          /*
314          if (stmQuery.execute("grant all on UPDATE_TRANSACTION_TEST_VALUE to " + strUserName))
315          {
316             // Close any results
317             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
318          }
319          Log.getLogger().log(Level.FINEST,
320                              "Access for procedure UPDATE_TRANSACTION_TEST_VALUE set for user "
321                              + strUserName);
322          */
323       }
324       catch (SQLException sqleExc)
325       {
326          s_logger.log(Level.WARNING, "Failed to create database test schema.",
327                              sqleExc);
328          throw sqleExc;
329       }
330       finally
331       {
332          DatabaseUtils.closeStatement(stmQuery);
333       }
334    }
335
336    /**
337     * {@inheritDoc}
338     */
339    public String getInsertGeneratedKey(
340    )
341    {
342       return "INSERT INTO generatedkey_test(test_key, test_value) " +
343               "VALUES (NEXTVAL('generatedkey_test_seq'), ?)";
344    }
345
346    /**
347     * {@inheritDoc}
348     */
349    public int[] executeInsertGeneratedKey2(
350       Connection dbConnection,
351       String strValue
352    ) throws SQLException
353    {
354
355       PreparedStatement insertStatement = null;
356       // CallableStatement insertStatement = null;
357 ResultSet rsResults = null;
358       int iInsertCount = 0;
359       int iGeneratedKey = 0;
360       int[] returnValues = null;
361       
362       try
363       {
364          insertStatement = dbConnection.prepareStatement(
365                               "SELECT * FROM insert_generatedkey_test(?)");
366          insertStatement.setString(1, strValue);
367          rsResults = insertStatement.executeQuery();
368          
369          if (rsResults.next())
370          {
371             iGeneratedKey = rsResults.getInt(1);
372             iInsertCount = rsResults.getInt(2);
373          }
374          
375          returnValues = new int[2];
376          returnValues[0] = iInsertCount;
377          returnValues[1] = iGeneratedKey;
378       }
379       finally
380       {
381          DatabaseUtils.closeResultSetAndStatement(rsResults, insertStatement);
382       }
383       
384       return returnValues;
385    }
386
387    /**
388     * {@inheritDoc}
389     */
390    public int executeUpdateTestValue(
391       Connection dbConnection,
392       String strOldValue,
393       String strNewValue
394    ) throws SQLException
395    {
396       CallableStatement updateStatement = null;
397       int iUpdateCount = 0;
398
399       try
400       {
401          updateStatement = dbConnection.prepareCall(
402                               " { ? = call UPDATE_TRANSACTION_TEST_VALUE (?, ?) } ");
403          updateStatement.registerOutParameter(1, Types.INTEGER);
404          updateStatement.setString(2, strOldValue);
405          updateStatement.setString(3, strNewValue);
406             
407          updateStatement.execute();
408          iUpdateCount = updateStatement.getInt(1);
409       }
410       finally
411       {
412          DatabaseUtils.closeStatement(updateStatement);
413       }
414       
415       return iUpdateCount;
416    }
417
418    /**
419     * {@inheritDoc}
420     */
421    public int[] executeInsertRow(
422       Connection dbConnection,
423       String strValue)
424    throws SQLException
425    {
426       CallableStatement insertStatement = null;
427       int iInsertCount = 0;
428       int iInsertCountReturnedFromSP = 0;
429       int[] returnValues = null;
430
431       try
432       {
433          insertStatement = dbConnection.prepareCall(
434                               " { ? = call INSERT_ROW_COUNT_TEST (?) } ");
435          insertStatement.registerOutParameter(1, Types.INTEGER);
436          insertStatement.setString(2, strValue);
437          
438          // here we cannot use iInsertCount = insertStatement.execute(); It's not supported.
439 // It means iInsertCount will be always 0 and it will show assert about it in jUnit tests.
440 insertStatement.execute();
441          iInsertCountReturnedFromSP = insertStatement.getInt(1);
442
443          returnValues = new int[2];
444
445          // value returned from insertStatement.execute(); Here it will be always 0,
446 // because there is not supported iInsertCount = insertStatement.execute();
447 returnValues[0] = iInsertCount;
448
449          // value (number of inserted rows) returned from stored procedure.
450 returnValues[1] = iInsertCountReturnedFromSP;
451       }
452       finally
453       {
454          DatabaseUtils.closeStatement(insertStatement);
455       }
456       
457       return returnValues;
458    }
459
460    /**
461     * {@inheritDoc}
462     */
463    public void createTestUser(
464       Connection cntAdminDBConnection,
465       String strDatabaseURL,
466       String strUserName,
467       String strUserPassword
468    ) throws SQLException
469    {
470       Statement stmQuery = null;
471       try
472       {
473          String strCreateUserQuery = "CREATE USER " + strUserName + " WITH PASSWORD '"
474                                      + strUserPassword + "'";
475
476          stmQuery = cntAdminDBConnection.createStatement();
477
478          if (stmQuery.execute(strCreateUserQuery))
479          {
480             // Close any results
481 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
482          }
483       }
484       finally
485       {
486          DatabaseUtils.closeStatement(stmQuery);
487       }
488    }
489
490    /**
491     * {@inheritDoc}
492     */
493    public void dropTestUser(
494       Connection cntAdminDBConnection,
495       String strDatabaseURL,
496       String strUserName
497    ) throws SQLException
498    {
499       Statement stmQuery = null;
500       try
501       {
502          String strDropUserQuery = "DROP USER " + strUserName;
503
504          stmQuery = cntAdminDBConnection.createStatement();
505
506          if (stmQuery.execute(strDropUserQuery))
507          {
508             // Close any results
509 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
510          }
511       }
512       finally
513       {
514          DatabaseUtils.closeStatement(stmQuery);
515       }
516    }
517 }
518

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags