001/* Implementation of DatabaseType for PostgreSQL.
002
003Copyright (c) 2012 The Regents of the University of California.
004All rights reserved.
005Permission is hereby granted, without written agreement and without
006license or royalty fees, to use, copy, modify, and distribute this
007software and its documentation for any purpose, provided that the above
008copyright notice and the following two paragraphs appear in all copies
009of this software.
010
011IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
012FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
013ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
014THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
015SUCH DAMAGE.
016
017THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
018INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
019MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
020PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
021CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
022ENHANCEMENTS, OR MODIFICATIONS.
023
024*/
025
026package org.kepler.util.sql;
027
028import java.sql.Connection;
029import java.sql.DriverManager;
030import java.sql.SQLException;
031import java.sql.Statement;
032import java.util.Map;
033import java.util.regex.Matcher;
034import java.util.regex.Pattern;
035
036
037/**
038 *
039 * An implementation of DatabaseType for PostgreSQL.
040 *
041 * @author Daniel Crawl
042 * @version $Id: PostgreSQL.java 33602 2015-08-21 22:15:53Z crawl $
043 *
044 */
045    
046public class PostgreSQL extends DatabaseType
047{
048    /** Only this package (DatabaseFactory) can instantiate. */
049    protected PostgreSQL()
050    {
051        super();
052    }
053    
054    /** Connect to a database. */
055    @Override
056    public void connect(Map<String,String> parameters) throws SQLException
057    {
058        try
059        {
060            super.connect(parameters);
061        }
062        catch(SQLException e)
063        {
064            // see if the reason we could not connect is that the
065            // database does not exist.
066            String message = e.getMessage();
067            Matcher matcher = _databaseDoesNotExistPattern.matcher(message);
068            if(matcher.find())
069            {
070                // create the database
071                String dbName = matcher.group(1);
072                Connection connection = null;
073                try
074                {
075                    // see if the full url was specified
076                    String jdbcUrl = parameters.get(DatabaseFactory.Parameter.JDBC_URL.getName());
077                    if(jdbcUrl != null && !jdbcUrl.isEmpty())
078                    {
079                        throw new SQLException("JDBC URL specified, but database does not exist. " +
080                                " This case is not supported; the database must be created manually.");
081                    }
082                    else
083                    {
084                        // connect to the database named "postgres"
085                        String url = _getJDBCUrl(
086                            parameters.get(DatabaseFactory.Parameter.HOST.getName()),
087                            parameters.get(DatabaseFactory.Parameter.PORT.getName()),
088                            "postgres");
089                        connection = DriverManager.getConnection(url, 
090                            parameters.get(DatabaseFactory.Parameter.USER.getName()), 
091                            parameters.get(DatabaseFactory.Parameter.PASSWD.getName()));
092                    }
093                    
094                    Statement statement = null;
095                    try
096                    {
097                        statement = connection.createStatement();
098                        // postgres database names are case-insensitive, so
099                        // put quotes around the name to get upper-case letters.
100                        statement.execute("CREATE DATABASE \"" + dbName + "\"");
101                    }
102                    finally
103                    {
104                        if(statement != null)
105                        {
106                            statement.close();
107                        }
108                    }
109                }
110                finally
111                {
112                    if(connection != null)
113                    {
114                        connection.close();
115                    }
116                }
117                
118                // try connecting again
119                super.connect(parameters);
120            }
121            else
122            {
123                throw e;
124            }
125        }
126    
127    }
128
129    /** Adjust the name of a column. */
130    @Override
131    public String getColumnName(String columnName)
132    {
133        // user is reserved
134        if(columnName.equals("user"))
135        {
136            return "\"user\"";
137        }
138        return columnName;
139    }
140    
141    /** Get a string representation of the false value for a boolean column. */
142    @Override
143    public String getFalseValue()
144    {
145        return "'0'";
146    }
147
148    /** Get a string representation of the true value for a boolean column. */
149    @Override
150    public String getTrueValue()
151    {
152        return "'1'";
153    }
154
155    /** Get a string representing the default time. */
156    @Override
157    public String getDefaultTimeStr()
158    {
159        return "'01-Jan-00 1:0:0 am'";
160    }
161    
162    /** Get the name of the type. */
163    @Override
164    public String getName()
165    {
166        return "PostgreSQL";
167    }
168
169    /** Rename a column. */
170    @Override
171    public void renameColumn(String oldName, Column newColumn, String tableName)
172            throws SQLException {
173        
174        // XXX this looks the same as oracle.
175        String sqlStr = "ALTER TABLE " + getTableName(tableName) + " RENAME COLUMN " +
176            getColumnName(oldName) + " TO " + getColumnDefinition(newColumn);
177        _executeSQL(sqlStr);
178    }
179
180    /** Change a column to allow null values. */
181    @Override
182    public void setColumnNull(Column column, String tableName) throws SQLException
183    {
184        String sqlStr = "ALTER TABLE " + getTableName(tableName) + " ALTER COLUMN " +
185                getColumnName(column.getName()) + " DROP NOT NULL";
186        _executeSQL(sqlStr);                
187    }
188    
189    /** Set not null constraint to a column. */
190    @Override
191    public void setColumnNotNull(Column column, String tableName)
192            throws SQLException {
193        // FIXME this looks the same as hsql.
194        String sqlStr = "ALTER TABLE " + getTableName(tableName) + " ALTER COLUMN " +
195                getColumnName(column.getName()) + " SET NOT NULL";
196        _executeSQL(sqlStr);                
197    }
198
199    ///////////////////////////////////////////////////////////////////
200    // protected methods
201
202    /** Returns true if foreign keys are automatically indexed. */
203    @Override
204    protected boolean _areForeignKeysIndexed()
205    {
206        return false;
207    }
208
209    /** Get the driver class name. */
210    @Override
211    protected String _getDriverName()
212    {
213        return "org.postgresql.Driver";
214    }
215    
216    /** Get a JDBC URL. */
217    @Override
218    protected String _getJDBCUrl(String hostName, String port,
219            String databaseName) throws SQLException {
220        String hostAndPort = _combineHostAndPort(hostName, port);
221        return "jdbc:postgresql://" + hostAndPort + "/" + databaseName;
222    }
223
224    /** Get the SQL string of a column type. */
225    @Override
226    protected String _getTypeString(Column column)
227    {
228        String retval = null;
229
230        // auto-incrementing columns are type serial
231        if(column.isAutoIncrement())
232        {
233            retval = "serial";
234        }
235        else
236        {
237            switch(column.getType())
238            {
239            case Binary:
240                retval = "bytea";
241                break;
242            case Boolean:
243                retval = "boolean";
244                break;
245            case Blob:
246                retval = "bytea";
247                break;
248            case Integer:
249                retval = "integer";
250                break;
251            case TextBlob:
252                retval = "text";
253                break;
254            case Timestamp:
255                retval = "timestamp";
256                break;
257            case Varchar:
258                retval = "varchar";
259                break;
260            case VarBinary:
261                retval = "bytea";
262                break;
263            default:
264                break;
265            }
266        }
267
268        return retval;
269    }
270
271    /** Returns true if database supports auto-generated keys in its prepared
272     *  statements.
273     */
274    @Override
275    protected boolean _hasGeneratedKeys()
276    {
277        return true;
278    }
279    
280    /** Returns true if an auto-incremented column value
281     *  in a ResultSet must be referenced with the name
282     *  instead of position.
283     */
284    @Override
285    protected boolean _isAutoIncResultReferencedByName()
286    {
287        return true;
288    }
289
290    /** Regex to match error when connecting and database not exist. */
291    private final static Pattern _databaseDoesNotExistPattern =
292            Pattern.compile("FATAL: database \"([^\\s]+)\" does not exist");
293
294}