KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > core > persist > db > postgresql > PostgreSQLDatabaseImpl


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: PostgreSQLDatabaseImpl.java,v 1.26 2007/01/07 06:14:43 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.postgresql;
23
24 import java.sql.CallableStatement JavaDoc;
25 import java.sql.Connection JavaDoc;
26 import java.sql.PreparedStatement JavaDoc;
27 import java.sql.SQLException JavaDoc;
28 import java.sql.Statement JavaDoc;
29 import java.util.Iterator JavaDoc;
30 import java.util.Map JavaDoc;
31 import java.util.logging.Level JavaDoc;
32 import java.util.logging.Logger JavaDoc;
33
34 import org.opensubsystems.core.data.BasicDataObject;
35 import org.opensubsystems.core.data.ModifiableDataObject;
36 import org.opensubsystems.core.error.OSSDatabaseAccessException;
37 import org.opensubsystems.core.error.OSSException;
38 import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl;
39 import org.opensubsystems.core.persist.db.DatabaseImpl;
40 import org.opensubsystems.core.persist.db.DatabaseTransactionFactoryImpl;
41 import org.opensubsystems.core.util.DatabaseUtils;
42 import org.opensubsystems.core.util.Log;
43
44 /**
45  * Management layer for PostgreSQL database (www.postgresql.org)
46  *
47  * @version $Id: PostgreSQLDatabaseImpl.java,v 1.26 2007/01/07 06:14:43 bastafidli Exp $
48  * @author Julo Legeny
49  * @code.reviewer Miro Halas
50  * @code.reviewed 1.21 2006/04/05 05:03:08 bastafidli
51  */

52 public class PostgreSQLDatabaseImpl extends DatabaseImpl
53 {
54    // Cached values ////////////////////////////////////////////////////////////
55

56    /**
57     * Logger for this class
58     */

59    private static Logger JavaDoc s_logger = Log.getInstance(PostgreSQLDatabaseImpl.class);
60
61    // Constructors /////////////////////////////////////////////////////////////
62

63    /**
64     * Default constructor for empty database.
65     *
66     * @throws OSSException - problem connecting to database
67     */

68    public PostgreSQLDatabaseImpl(
69    ) throws OSSException
70    {
71       super();
72    }
73    
74    // Database administration //////////////////////////////////////////////////
75

76    /**
77     * {@inheritDoc}
78     */

79    protected void createUser(
80       Connection JavaDoc cntAdminDBConnection
81    ) throws OSSException
82    {
83       // Now create the user
84
PreparedStatement JavaDoc pstmQuery = null;
85       try
86       {
87          // PostgreSQL must have password typed within the ''
88
/*
89             pstmQuery = cntAdminDBConnection.prepareStatement(
90                            "CREATE USER ? WITH password ?");
91             pstmQuery.setString(1, DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
92             pstmQuery.setString(2,
93                                 DatabaseConnectionFactoryImpl.getInstance().getDatabasePassword());
94          */

95
96          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
97          
98          buffer.append("CREATE USER ");
99          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
100          buffer.append(" WITH PASSWORD '");
101          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabasePassword());
102          buffer.append("'");
103
104          try
105          {
106             pstmQuery = cntAdminDBConnection.prepareStatement(buffer.toString());
107    
108             if (pstmQuery.execute())
109             {
110                // Close any results
111
pstmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
112             }
113          }
114          finally
115          {
116             DatabaseUtils.closeStatement(pstmQuery);
117             pstmQuery = null;
118          }
119          
120          // we have to explicitely create database schema with user name
121
buffer.delete(0, buffer.length());
122          buffer.append("CREATE SCHEMA ");
123          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
124          buffer.append(" AUTHORIZATION ");
125          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
126          
127          try
128          {
129             pstmQuery = cntAdminDBConnection.prepareStatement(buffer.toString());
130    
131             if (pstmQuery.execute())
132             {
133                // Close any results
134
pstmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
135             }
136          }
137          finally
138          {
139             DatabaseUtils.closeStatement(pstmQuery);
140             pstmQuery = null;
141          }
142
143          // At this point we don't know if this is just a single operation
144
// and we need to commit or if it is a part of bigger transaction
145
// and the commit is not desired until all operations proceed.
146
// Therefore let the DatabaseTransactionFactory resolve it
147
DatabaseTransactionFactoryImpl.getInstance().commitTransaction(
148                                                  cntAdminDBConnection);
149          
150          s_logger.log(Level.FINER, "Database user "
151                       + DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser()
152                       + " with password "
153                       + DatabaseConnectionFactoryImpl.getInstance().getDatabasePassword()
154                       + " and database schema "
155                       + DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser()
156                       + " created.");
157       }
158       catch (SQLException JavaDoc sqleExc)
159       {
160          try
161          {
162             // At this point we don't know if this is just a single operation
163
// and we need to commit or if it is a part of bigger transaction
164
// and the commit is not desired until all operations proceed.
165
// Therefore let the DatabaseTransactionFactory resolve it
166
DatabaseTransactionFactoryImpl.getInstance().rollbackTransaction(
167                cntAdminDBConnection);
168          }
169          catch (SQLException JavaDoc sqleExc2)
170          {
171             // Ignore this
172
s_logger.log(Level.WARNING,
173                                 "Failed to rollback changes for creation of user.",
174                                 sqleExc2);
175          }
176          s_logger.log(Level.SEVERE,
177                              "Unable to create default database user.",
178                              sqleExc);
179          throw new OSSDatabaseAccessException("Unable to create default database user.",
180                                               sqleExc);
181       }
182       finally
183       {
184          DatabaseUtils.closeStatement(pstmQuery);
185       }
186    }
187
188    /**
189     * {@inheritDoc}
190     */

191    public void startDatabaseServer() throws OSSException
192    {
193       // TODO: Feature: Implement starting database server
194
// Starting a system service is by its nature going to be
195
// operating-system specific, so I think it best bet would be to call
196
// some external scripts to do this.
197
}
198
199    /**
200     * {@inheritDoc}
201     */

202    public void createDatabaseInstance() throws OSSException
203    {
204       // TODO: Feature: Implement creating database instance
205
// Creating a new database can be done by connecting to database template1
206
// and issuing "CREATE DATABASE ...", you can do similarly with users. See
207
// the "SQL Commands" section of the manuals for details.
208
}
209
210    /**
211     * {@inheritDoc}
212     */

213    public void stop(
214    ) throws OSSException
215    {
216       // Starting a system service is by its nature going to be
217
// operating-system specific, so I think it best bet would be to call
218
// some external scripts to do this.
219

220       s_logger.entering(this.getClass().getName(), "stop");
221
222       // TODO: Feature: Implement this so we can safely stop the database when
223
// the application is finished.
224
super.stop();
225
226       s_logger.entering(this.getClass().getName(), "stop");
227    }
228
229    /**
230     * {@inheritDoc}
231     */

232    public boolean preferCountToLast(
233    )
234    {
235       // For PostgreSQL it is generally faster to execute count(*) than to do last()
236
// since it seems that it creates copy of the result set, which takes some
237
// time
238
return true;
239    }
240
241    /**
242     * {@inheritDoc}
243     */

244    public int getDatabaseType()
245    {
246       return DatabaseImpl.POSTGRESQL_DATABASE_TYPE;
247    }
248
249    /**
250     * {@inheritDoc}
251     */

252    public String JavaDoc getDatabaseTypeIdentifier()
253    {
254       return DatabaseImpl.POSTGRESQL_DATABASE_TYPE_IDENTIFIER;
255    }
256
257    /**
258     * {@inheritDoc}
259     */

260    public String JavaDoc getCurrentTimestampFunctionCall()
261    {
262       // Postgres requries () for this call
263
return "now()";
264    }
265
266    /**
267     * {@inheritDoc}
268     */

269    public String JavaDoc getSQLCountFunctionCall()
270    {
271       // For PostgreSQL is best for performance use COUNT(*)
272
return "count(*)";
273    }
274
275    /**
276     * {@inheritDoc}
277     */

278    public Object JavaDoc[] getSQLAnalyzeFunctionCall(
279       Map JavaDoc mapTableNames
280    )
281    {
282       // PostgreSQL uses VACUUM ANALYZE [<table_name>] to update indexes and
283
// increase performance
284
// VACUUM ANALYZE command must be processed outside of the transaction,
285
// so there has to be set up autoCommit = true
286

287       String JavaDoc[] arrReturn = new String JavaDoc[mapTableNames.size()];
288       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
289       Iterator JavaDoc itItem;
290       int iIndex = 0;
291
292       itItem = mapTableNames.values().iterator();
293       while (itItem.hasNext())
294       {
295          // construct analyze query for each table from the array
296
buffer.append("vacuum analyze ");
297          buffer.append((String JavaDoc)itItem.next());
298          // add constructed query to the output array
299
arrReturn[iIndex++] = buffer.toString();
300          // delete buffer for next usage
301
buffer.delete(0, buffer.length());
302       }
303
304       return new Object JavaDoc[] {arrReturn, Boolean.TRUE};
305    }
306
307    /**
308     * {@inheritDoc}
309     */

310    public boolean hasSelectListRangeSupport(
311    )
312    {
313       // PostgreSQL supports rows limitation by using clause LIMIT X OFFSET Y
314
return true;
315    }
316
317    /**
318     * {@inheritDoc}
319     */

320    public boolean isCallableStatement(
321       String JavaDoc strQuery
322    )
323    {
324       // TODO: For Julo: PostgreSQL is just doing normal select so we should
325
// probably check if it matches pattern select .... from xyz (...)
326
return strQuery.indexOf("select ") != -1;
327    }
328
329    /**
330     * {@inheritDoc}
331     */

332    public void insertAndFetchGeneratedValues(
333       Connection JavaDoc dbConnection,
334       PreparedStatement JavaDoc insertStatement,
335       boolean bIsInDomain,
336       String JavaDoc strTableName,
337       int iIndex,
338       BasicDataObject data
339    ) throws SQLException JavaDoc,
340             OSSException
341    {
342       PostgreSQLDataUtils.insertAndFetchGeneratedValues(insertStatement, data);
343    }
344
345    /**
346     * {@inheritDoc}
347     */

348    public void updatedAndFetchGeneratedValues(
349       String JavaDoc strDataName,
350       Connection JavaDoc dbConnection,
351       PreparedStatement JavaDoc updateStatement,
352       boolean bIsInDomain,
353       String JavaDoc strTableName,
354       int iIndex,
355       ModifiableDataObject data
356    ) throws SQLException JavaDoc,
357             OSSException
358    {
359       PostgreSQLDataUtils.updateAndFetchGeneratedValues((CallableStatement JavaDoc)updateStatement,
360                                                         data);
361    }
362 }
363
Popular Tags