PostgreSQLMappingDatabaseSchema


1   /*
2    * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenSubsystems
5    * 
6    * $Id: PostgreSQLMappingDatabaseSchema.java,v 1.8 2007/01/07 06:15:21 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.patterns.mappeddata.persist.db.postgresql;
23  
24  import java.sql.Connection  ;
25  import java.sql.PreparedStatement  ;
26  import java.sql.SQLException  ;
27  import java.sql.Statement  ;
28  import java.util.logging.Level  ;
29  import java.util.logging.Logger  ;
30  
31  import org.opensubsystems.core.error.OSSException;
32  import org.opensubsystems.core.persist.db.postgresql.PostgreSQLDataUtils;
33  import org.opensubsystems.core.util.DatabaseUtils;
34  import org.opensubsystems.core.util.Log;
35  import org.opensubsystems.patterns.mappeddata.data.MappedData;
36  import org.opensubsystems.patterns.mappeddata.persist.db.MappingDatabaseSchema;
37  import org.opensubsystems.patterns.mappeddata.persist.db.db2.DB2MappingDatabaseSchema;
38  
39  /**
40   * Database specific operations related to persistence of mapping tables 
41   * for PostgreSQL database.
42   *
43   * @version $Id: PostgreSQLMappingDatabaseSchema.java,v 1.8 2007/01/07 06:15:21 bastafidli Exp $
44   * @author Julian Legeny
45   * @code.reviewer Miro Halas
46   * @code.reviewed 1.6 2006/08/14 22:42:41 jlegeny
47   */
48  public class PostgreSQLMappingDatabaseSchema extends MappingDatabaseSchema
49  {
50     /*
51        Use autogenerated numbers for IDs using sequence
52        Name all constraints to easily identify them later.
53  
54        create table BF_MAP_TABLE
55        (
56           ID SERIAL,
57           ID1 INTEGER NOT NULL,      
58           ID2 INTEGER NOT NULL,
59           MAPPING_TYPE INTEGER NOT NULL,
60           CUSTOM_DATA VARCHAR(4000) DEFAULT NULL,
61           CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
62           MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
63           CONSTRAINT BF_MAP_TABLE_UQ UNIQUE (ID1, ID2, MAPPING_TYPE),
64           CONSTRAINT BF_MAP_TABLE_FK1 FOREIGN KEY (ID1) 
65              REFERENCES BF_TABLE1 (COLUMN_ID1) ON DELETE CASCADE,
66           CONSTRAINT BF_MAP_TABLE_FK2 FOREIGN KEY (ID2) 
67              REFERENCES BF_TABLE2 (COLUMN_ID2) ON DELETE CASCADE,
68           CONSTRAINT BF_MAP_TABLE_PK PRIMARY KEY (ID)
69        )
70  
71        CREATE OR REPLACE FUNCTION INSERT_BF_MAP_TABLE
72        (
73           INTEGER,
74           INTEGER,
75           INTEGER,
76           VARCHAR(4000)
77        ) RETURNS type_int_timestamp AS '
78        DECLARE
79           in_id1 ALIAS FOR $1;
80           in_id2 ALIAS FOR $2;
81           in_mapping_type ALIAS FOR $3;
82           in_custom_data ALIAS FOR $4;
83           out_key INTEGER;
84           out_timestamp TIMESTAMP WITH TIME ZONE;
85           output_result type_int_timestamp;
86        BEGIN
87           SELECT INTO out_timestamp now();
88           INSERT INTO + strUserName + BF_MAP_TABLE (ID1, ID2, MAPPING_TYPE, CUSTOM_DATA,  
89                  CREATION_DATE, MODIFICATION_DATE)
90              VALUES (in_id1, in_id2, in_mapping_type, in_custom_data,
91                 out_timestamp, out_timestamp);
92           out_key := CURRVAL(''bf_map_table_id_seq'');
93           output_result.intgr := out_key;
94           output_result.tmstp := out_timestamp;
95           RETURN output_result;
96        END;
97        ' LANGUAGE 'plpgsql';
98  
99  
100       CREATE OR REPLACE FUNCTION UPDATE_BF_MAP_TABLE
101       (
102          INTEGER,
103          INTEGER,
104          INTEGER,
105          INTEGER,
106          VARCHAR(4000),
107          TIMESTAMP WITH TIME ZONE
108       ) RETURNS type_int_timestamp AS '
109       DECLARE
110          in_id ALIAS FOR $1;
111          in_id1 ALIAS FOR $2;
112          in_id2 ALIAS FOR $3;
113          in_mapping_type ALIAS FOR $4;
114          in_custom_data ALIAS FOR $5;
115          in_modification_date ALIAS FOR $6;
116          out_updated_count INTEGER;
117          out_timestamp TIMESTAMP WITH TIME ZONE;
118          output_result type_int_timestamp;
119       BEGIN
120          SELECT INTO out_timestamp now();
121          UPDATE  + strUserName + BF_MAP_TABLE SET ID1 = in_id1, ID2 = in_id2 
122             MAPPING_TYPE = in_mapping_type,  CUSTOM_DATA = in_custom_data, 
123             MODIFICATION_DATE = out_timestamp WHERE ID = in_id 
124             AND MODIFICATION_DATE = in_modification_date;
125          GET DIAGNOSTICS out_updated_count = ROW_COUNT;
126          output_result.intgr := out_updated_count;
127          output_result.tmstp := out_timestamp;
128          RETURN output_result;
129       END;
130       ' LANGUAGE 'plpgsql';
131    */   
132 
133    // Cached values ////////////////////////////////////////////////////////////
134 
135    /**
136     * Logger for this class
137     */
138    private static Logger   s_logger = Log.getInstance(DB2MappingDatabaseSchema.class);
139 
140    // Constructors /////////////////////////////////////////////////////////////
141 
142    /**
143     * Full constructor.
144     * 
145     * @param strMapTableName - table name for mapping table
146     * @param schema1 - schema name the table 1 was defined in
147     * @param strTableName1 - name of the table 1
148     * @param strColumnName1 - name of the column 1
149     * @param schema2 - schema name the table 2 was defined in
150     * @param strTableName2 - name of the table 2
151     * @param strColumnName2 - name of the column 2
152     * @throws OSSException - an error has occured
153     */
154    public PostgreSQLMappingDatabaseSchema(
155       String   strMapTableName, 
156       Class    schema1, 
157       String   strTableName1, 
158       String   strColumnName1, 
159       Class    schema2, 
160       String   strTableName2, 
161       String   strColumnName2
162    ) throws OSSException
163    {
164       super(strMapTableName, schema1, strTableName1, strColumnName1, 
165             schema2, strTableName2, strColumnName2);
166    }
167 
168    // Public methods ///////////////////////////////////////////////////////////
169 
170    /**
171     * {@inheritDoc}
172     */
173    public void create(
174       Connection   cntDBConnection,
175       String       strUserName
176    ) throws SQLException  , OSSException
177    {
178       Statement   stmQuery = null;
179       try
180       {
181          stmQuery = cntDBConnection.createStatement();
182 
183          if (stmQuery.execute(constructSQL(
184                                 "ID SERIAL,", "INTEGER",
185                                 "VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + ")" + NL,
186                                 "TIMESTAMP WITH TIME ZONE", m_strConstraintBody, true, true)))
187          {
188             // Close any results
189             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
190          }
191          s_logger.log(Level.FINEST, "Table " + getSchemaPrefix() + 
192                                     m_strMapTableName + " created.");
193 
194          ///////////////////////////////////////////////////////////////////////
195 
196          if (stmQuery.execute(constructInsertStoredProc(strUserName, m_strMapTableName)))
197          {
198             // Close any results
199             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
200          }
201          s_logger.log(Level.FINEST, "Procedure INSERT_" + m_strMapTableName + " created.");
202          
203          ///////////////////////////////////////////////////////////////////////  
204 
205          if (stmQuery.execute(constructUpdateStoredProc(strUserName, m_strMapTableName)))
206          {
207             // Close any results
208             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
209          }
210          s_logger.log(Level.FINEST, "Procedure UPDATE_" + m_strMapTableName + " created.");
211       }
212       catch (SQLException   sqleExc)
213       {
214          s_logger.log(Level.WARNING, "Failed to create schema " + MAPPING_SCHEMA_NAME, sqleExc);
215          throw sqleExc;
216       }
217       finally
218       {
219          DatabaseUtils.closeStatement(stmQuery);
220       }
221    }   
222 
223    /**
224     * {@inheritDoc}
225     */
226    public MappedData insertMappedData(
227       Connection   dbConnection, 
228       MappedData data
229    ) throws OSSException
230    {
231       PreparedStatement   insertStatement = null;
232       
233       try
234       {
235          StringBuffer   buffer = new StringBuffer  ();
236          int iIndex = 1;
237       
238          buffer.append("select INTGR, TMSTP from INSERT_");
239          buffer.append(getSchemaPrefix());
240          buffer.append(m_strMapTableName);
241          buffer.append(" (?, ?, ?, ?)");
242          
243          insertStatement = dbConnection.prepareStatement(buffer.toString());
244 
245          insertStatement.setInt(iIndex++, data.getMappedId1());
246          insertStatement.setInt(iIndex++, data.getMappedId2());
247          insertStatement.setInt(iIndex++, data.getMappingType());
248          insertStatement.setString(iIndex++, data.getCustomData());
249 
250          PostgreSQLDataUtils.insertAndFetchGeneratedValues(insertStatement, data);
251       }
252       catch (SQLException   eExc)
253       {
254          handleInsertMappedDataException(eExc);
255       }
256       finally
257       {
258          DatabaseUtils.closeStatement(insertStatement);
259       }
260 
261       return data;   
262    }
263 
264    /**
265     * {@inheritDoc}
266     */
267    public MappedData updateMappedData(
268       Connection   dbConnection, 
269       MappedData data
270    ) throws OSSException
271    {
272       PreparedStatement   updateStatement = null;
273       
274       try
275       {
276          StringBuffer   buffer = new StringBuffer  ();
277          int iIndex = 1;
278       
279          buffer.append("select INTGR, TMSTP from UPDATE_");
280          buffer.append(getSchemaPrefix());
281          buffer.append(m_strMapTableName);
282          buffer.append(" (?, ?, ?, ?, ?, ?)");
283          
284          updateStatement = dbConnection.prepareStatement(buffer.toString());
285 
286          updateStatement.setInt(iIndex++, data.getId());
287          updateStatement.setInt(iIndex++, data.getMappedId1());
288          updateStatement.setInt(iIndex++, data.getMappedId2());
289          updateStatement.setInt(iIndex++, data.getMappingType());
290          updateStatement.setString(iIndex++, data.getCustomData());
291          updateStatement.setTimestamp(iIndex++, data.getModificationTimestamp());
292 
293          PostgreSQLDataUtils.updateAndFetchGeneratedValues(updateStatement, data);
294       }   
295       catch (SQLException   eExc)
296       {
297          handleUpdateMappedDataException(eExc, dbConnection, data);
298       }
299       finally
300       {
301          DatabaseUtils.closeStatement(updateStatement);
302       }
303 
304       return data;
305    }
306 
307    // Helper methods ///////////////////////////////////////////////////////////
308 
309    /**
310     * Construt SQL command for stored procedure that process inserting data.
311     * 
312     * @param strMapTableName - table name
313     * @param strUserName - user name
314     * @return String - constructed command of insert stored procedure
315     */
316    public String   constructInsertStoredProc(
317       String   strUserName,
318       String   strMapTableName
319    )
320    {
321       StringBuffer   buffer = new StringBuffer  ();
322 
323       buffer.append("CREATE OR REPLACE FUNCTION INSERT_");
324       buffer.append(getSchemaPrefix());
325       buffer.append(strMapTableName);
326       buffer.append(NL + "(" + NL +
327          "   INTEGER, " + NL +
328          "   INTEGER, " + NL +
329          "   INTEGER, " + NL +
330          "   VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + ") " + NL +
331          ")  RETURNS type_int_timestamp AS '" + NL + 
332          "DECLARE " + NL +
333          "   in_id1 ALIAS FOR $1; " + NL +
334          "   in_id2 ALIAS FOR $2; " + NL +
335          "   in_mapping_type ALIAS FOR $3; " + NL +
336          "   in_custom_data ALIAS FOR $4; " + NL +
337          "   out_key INTEGER; " + NL +
338          "   out_timestamp TIMESTAMP WITH TIME ZONE; " + NL +
339          "   output_result type_int_timestamp; " + NL +
340          "BEGIN " + NL +
341          "   SELECT INTO out_timestamp now(); " + NL +
342          "   INSERT INTO ");
343       buffer.append(strUserName);
344       buffer.append(".");
345       buffer.append(getSchemaPrefix());
346       buffer.append(strMapTableName);
347       buffer.append("(ID1, ID2, " + NL +
348          "      MAPPING_TYPE, CUSTOM_DATA, CREATION_DATE, MODIFICATION_DATE) " + NL +
349          "   VALUES (in_id1, in_id2, in_mapping_type, in_custom_data, " + NL +
350          "      out_timestamp, out_timestamp);" + NL +
351          "   out_key := CURRVAL(''");
352       buffer.append(getSchemaPrefix().toLowerCase());
353       buffer.append(strMapTableName.toLowerCase());
354       buffer.append("_id_seq''); " + NL +
355          "   output_result.intgr := out_key; " + NL +
356          "   output_result.tmstp := out_timestamp; " + NL +
357          "   RETURN output_result; " + NL +
358          "END;" + NL +
359          "' LANGUAGE 'plpgsql';");
360       
361       return buffer.toString();
362    }
363    
364    /**
365     * Construt SQL command for stored procedure that process inserting data.
366     * 
367     * @param strMapTableName - table name
368     * @param strUserName - user name
369     * @return String - constructed command of insert stored procedure
370     */
371    public static String   constructUpdateStoredProc(
372       String   strUserName,
373       String   strMapTableName
374    )
375    {
376       StringBuffer   buffer = new StringBuffer  ();
377 
378       buffer.append("CREATE OR REPLACE FUNCTION UPDATE_");
379       buffer.append(getSchemaPrefix());
380       buffer.append(strMapTableName);
381       buffer.append(NL + "(" + NL +
382          "   INTEGER, " + NL +
383          "   INTEGER, " + NL +
384          "   INTEGER, " + NL +
385          "   INTEGER, " + NL +
386          "   VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + "), " +
387          "   TIMESTAMP WITH TIME ZONE " + NL +
388          ")  RETURNS type_int_timestamp AS '" + NL + 
389          "DECLARE " + NL +
390          "   in_id ALIAS FOR $1; " + NL +
391          "   in_id1 ALIAS FOR $2; " + NL +
392          "   in_id2 ALIAS FOR $3; " + NL +
393          "   in_mapping_type ALIAS FOR $4; " + NL +
394          "   in_custom_data ALIAS FOR $5; " + NL +
395          "   in_modification_date ALIAS FOR $6;" + NL +
396          "   out_updated_count INTEGER; " + NL +
397          "   out_timestamp TIMESTAMP WITH TIME ZONE; " + NL +
398          "   output_result type_int_timestamp; " + NL +
399          "BEGIN " + NL +
400          "   SELECT INTO out_timestamp now(); " + NL +
401          "   UPDATE ");
402       buffer.append(strUserName);
403       buffer.append(".");
404       buffer.append(getSchemaPrefix());
405       buffer.append(strMapTableName);
406       buffer.append(" SET ID1 = in_id1, ID2 = in_id2, " + NL +
407          "   MAPPING_TYPE = in_mapping_type, CUSTOM_DATA = in_custom_data, " + NL +
408          "   MODIFICATION_DATE = out_timestamp WHERE ID = in_id " + NL + 
409          "   AND MODIFICATION_DATE = in_modification_date; " + NL +
410          "   GET DIAGNOSTICS out_updated_count = ROW_COUNT; " + NL +
411          "   output_result.intgr := out_updated_count;" + NL +
412          "   output_result.tmstp := out_timestamp; " + NL +
413          "   RETURN output_result; " + NL +
414          "END;" + NL +
415          "' LANGUAGE 'plpgsql';");
416       
417       return buffer.toString();
418    }
419 }
420
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags