001package com.hfg.sql.jdbc.postgresql;
002
003import com.hfg.datetime.ThreadSafeDateFormat;
004import com.hfg.sql.SQLQuery;
005import com.hfg.sql.SQLUtil;
006import com.hfg.sql.jdbc.*;
007import com.hfg.security.LoginCredentials;
008import com.hfg.sql.table.DatabaseCol;
009import com.hfg.sql.table.DatabaseSequence;
010import com.hfg.util.StringUtil;
011
012import java.sql.Connection;
013import java.sql.ResultSet;
014import java.sql.SQLException;
015import java.sql.Types;
016import java.text.DateFormat;
017import java.time.format.DateTimeFormatter;
018
019//------------------------------------------------------------------------------
020/**
021 Home for PostgreSQL-specific RDBMS info.
022 <div>
023 @author J. Alex Taylor, hairyfatguy.com
024 </div>
025 */
026//------------------------------------------------------------------------------
027// com.hfg XML/HTML Coding Library
028//
029// This library is free software; you can redistribute it and/or
030// modify it under the terms of the GNU Lesser General Public
031// License as published by the Free Software Foundation; either
032// version 2.1 of the License, or (at your option) any later version.
033//
034// This library is distributed in the hope that it will be useful,
035// but WITHOUT ANY WARRANTY; without even the implied warranty of
036// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
037// Lesser General Public License for more details.
038//
039// You should have received a copy of the GNU Lesser General Public
040// License along with this library; if not, write to the Free Software
041// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
042//
043// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
044// jataylor@hairyfatguy.com
045//------------------------------------------------------------------------------
046
047public class PostgreSQL extends RDBMS
048{
049   // These need to be final or else their values will still be unset when the
050   // RDBMS static declaration of PostgresSQL is instantiated.
051   private static final String sName                 = "PostgreSQL";
052   private static final String sDriverClassName      = "org.postgresql.Driver";
053   private static final int    sDefaultPort          = 5432;
054   private static final int    sMaxIdentifierLength  = 63;
055   private static final int    sMaxInClauseArgs      = 2500; // This is a practical but not absolute value
056
057   private static PostgreSQL sInstance;
058
059   private DateFormat mSQLDateFormat;
060   private SQLQuery   mConnTestQuery = new SQLQuery().addSelect("1");
061
062   private static final DateTimeFormatter TIMESTAMP_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
063   private static final DateTimeFormatter TIMESTAMP_WITH_TIMEZONE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxx");
064   
065   //###########################################################################
066   // CONSTRUCTORS
067   //###########################################################################
068
069   //---------------------------------------------------------------------------
070   private PostgreSQL()
071   {
072      super(sName);
073      setDefaultPort(sDefaultPort);
074      setMaxIdentifierLength(sMaxIdentifierLength);
075      setMaxInClauseArgs(sMaxInClauseArgs);
076      setDriverClassName(sDriverClassName);
077   }
078
079
080   //###########################################################################
081   // PUBLIC METHODS
082   //###########################################################################
083
084   //--------------------------------------------------------------------------
085   /**
086    Since PostgreSQL doesn't have a hard limit for the number of in-clause
087    arguments, the user is allowed to set a desired limit.
088    * @param inValue the limit to use when generating SQL.
089    * @return this PostgreSQL object
090    */
091   @Override
092   public PostgreSQL setMaxInClauseArgs(int inValue)
093   {
094      return (PostgreSQL) super.setMaxInClauseArgs(inValue);
095   }
096
097   //--------------------------------------------------------------------------
098   public static synchronized PostgreSQL getInstance()
099   {
100      if (null == sInstance)
101      {
102         sInstance = new PostgreSQL();
103      }
104
105      return sInstance;
106   }
107
108   //---------------------------------------------------------------------------
109   public String getConnectString(JDBCServer inServer, String inDatabaseName)
110   {
111      return getConnectString(inServer, inDatabaseName, null);
112   }
113
114   //---------------------------------------------------------------------------
115   public String getConnectString(JDBCServer inServer, String inDatabaseName, JDBCConnectionSettings inSettings)
116   {
117      StringBuilder buffer = new StringBuilder();
118      buffer.append("jdbc:postgresql://");
119      buffer.append(inServer.getHost());
120      buffer.append(":");
121      buffer.append(inServer.getPort());
122      buffer.append("/");
123      buffer.append(inDatabaseName);
124
125      return buffer.toString();
126   }
127
128   //---------------------------------------------------------------------------
129   public PostgreSQLConnectionPool establishConnectionPool(JDBCServer inServer, String inDatabaseName, LoginCredentials inCredentials, JDBCConnectionPoolSettings inPoolSettings)
130   {
131      PostgreSQLConnectionPool pool = new PostgreSQLConnectionPool(inServer, inDatabaseName, inCredentials);
132      pool.setSettings(inPoolSettings);
133
134      return pool;
135   }
136
137
138   //---------------------------------------------------------------------------
139   public PostgreSQLConnection getConnection(JDBCServer inServer, String inDatabaseName, LoginCredentials inCredentials)
140      throws JDBCException
141   {
142      return new PostgreSQLConnection(inServer, inDatabaseName, inCredentials);
143   }
144
145   //---------------------------------------------------------------------------
146   @Deprecated
147   public DateFormat getSQLDateFormat(int inSQLType)
148   {
149      if (null == mSQLDateFormat)
150      {
151         mSQLDateFormat = new ThreadSafeDateFormat("yyyy-MM-dd HH:mm:ss XXX");
152      }
153
154      return mSQLDateFormat;
155   }
156
157   //---------------------------------------------------------------------------
158   public DateTimeFormatter getSQLDateFormatter(int inSQLType)
159   {
160      DateTimeFormatter formatter;
161
162      switch (inSQLType)
163      {
164         case Types.DATE:
165         case Types.TIMESTAMP:
166            // Ex: '1999-01-08 04:05:06'
167            formatter = TIMESTAMP_FORMATTER;
168            break;
169         case Types.TIMESTAMP_WITH_TIMEZONE:
170            // Ex: '1999-01-08 04:05:06 -8:00'
171            formatter = TIMESTAMP_WITH_TIMEZONE_FORMATTER;
172            break;
173         default:
174            throw new JDBCException("Currently unsupported SQL Type for date: " + inSQLType + "!");
175      }
176      
177      return formatter;
178   }
179
180   //---------------------------------------------------------------------------
181   public DatabaseSequence allocateSequence(String inName)
182   {
183      return new PostgreSQLSequence(inName);
184   }
185
186   //---------------------------------------------------------------------------
187   public Long getLastGeneratedId(Connection inConn, DatabaseCol inIdCol)
188      throws SQLException
189   {
190      // SELECT currval('persons_id_seq');
191      DatabaseSequence sequence = inIdCol.getSequence();
192      if (null == sequence)
193      {
194         // SERIAL column
195         sequence = new DatabaseSequence(inIdCol.getTable().getQualifiedName() + "_" + inIdCol.name() + "_seq");
196      }
197
198      String sql = "SELECT currval(" + StringUtil.singleQuote(sequence) + ")";
199
200      Long id = null;
201
202      ResultSet rs = null;
203      try
204      {
205         rs = SQLUtil.executeQuery(inConn, sql);
206         if (rs.next())
207         {
208            id = rs.getLong(1);
209         }
210      }
211      finally
212      {
213         SQLUtil.closeResultSetAndStatement(rs);
214      }
215
216      return id;
217   }
218
219   //---------------------------------------------------------------------------
220   public SQLQuery getConnTestQuery()
221   {
222      return mConnTestQuery;
223   }
224
225   //---------------------------------------------------------------------------
226   public boolean tableExists(Connection inConn, Schema inSchema, String inTablename)
227         throws SQLException
228   {
229      SQLQuery query = new SQLQuery()
230            .addSelect("1")
231            .addFrom("information_schema.tables")
232            .addWhereClause("table_schema = " + SQLUtil.sqlString(inSchema))
233            .addWhereClause("table_name = " + SQLUtil.sqlString(inTablename));
234
235      boolean result = false;
236
237      ResultSet rs = null;
238      try
239      {
240         rs = query.execute(inConn);
241         result = rs.next();
242      }
243      finally
244      {
245         SQLUtil.close(rs);
246      }
247
248      return result;
249   }
250}