001/*
002 *  Copyright 2010 Anyware Services
003 *
004 *  Licensed under the Apache License, Version 2.0 (the "License");
005 *  you may not use this file except in compliance with the License.
006 *  You may obtain a copy of the License at
007 *
008 *      http://www.apache.org/licenses/LICENSE-2.0
009 *
010 *  Unless required by applicable law or agreed to in writing, software
011 *  distributed under the License is distributed on an "AS IS" BASIS,
012 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 *  See the License for the specific language governing permissions and
014 *  limitations under the License.
015 */
016package org.ametys.plugins.forms.table;
017
018import java.sql.DatabaseMetaData;
019import java.sql.PreparedStatement;
020import java.sql.SQLException;
021import java.sql.Types;
022
023import org.apache.commons.lang.StringUtils;
024
025import org.ametys.core.datasource.ConnectionHelper;
026import org.ametys.core.datasource.dbtype.SQLDatabaseTypeExtensionPoint;
027import org.ametys.plugins.forms.table.FormTableManager.DbColumn;
028
029/**
030 * Database abstraction helper.
031 */
032public final class DbTypeHelper
033{
034    private DbTypeHelper()
035    {
036        // Hides the default constructor.
037    }
038    
039    /**
040     * Normalize a name (table name, column name, and so on) according to the database type.
041     * @param dbType the database type.
042     * @param str the string to normalize.
043     * @return the normalized string.
044     */
045    public static String normalizeName(String dbType, String str)
046    {
047        switch (dbType)
048        {
049            case ConnectionHelper.DATABASE_ORACLE:
050                return StringUtils.replace(str, "-", "_");
051            default:
052                return str;
053        }
054    }
055    
056    /**
057     * Filter a name (table name, column name, and so on) according to the database type
058     * so that it can be passed to {@link DatabaseMetaData} methods, such as getTables and getColumns.
059     * @param dbType the database type.
060     * @param str the string to filter.
061     * @return the filtered string.
062     */
063    public static String filterName(String dbType, String str)
064    {
065        switch (dbType)
066        {
067            case ConnectionHelper.DATABASE_ORACLE:
068                return str.toUpperCase();
069            default:
070                return str;
071        }
072    }
073    
074    /**
075     * Get the varchar type identifier corresponding to the database type.
076     * @param dbType the database type.
077     * @return the real varchar type identifier (to be included in CREATE statement).
078     */
079    public static String getVarcharType(String dbType)
080    {
081        return "VARCHAR(255)";
082    }
083    
084    /**
085     * Get the text type identifier corresponding to the database type.
086     * @param dbType the database type.
087     * @return the real text type identifier (to be included in CREATE statement).
088     */
089    public static String getTextType(String dbType)
090    {
091        switch (dbType)
092        {
093            case ConnectionHelper.DATABASE_DERBY:
094                return "LONG VARCHAR";
095            case ConnectionHelper.DATABASE_ORACLE:
096                return "CLOB";
097            default:
098                return "TEXT";
099        }
100    }
101    
102    /**
103     * Get the boolean type identifier corresponding to the database type.
104     * @param dbType the database type.
105     * @return the real boolean type identifier (to be included in CREATE statement).
106     */
107    public static String getBooleanType(String dbType)
108    {
109        switch (dbType)
110        {
111            case ConnectionHelper.DATABASE_DERBY:
112            case ConnectionHelper.DATABASE_POSTGRES:
113                return "SMALLINT";
114            case ConnectionHelper.DATABASE_ORACLE:
115                return "NUMBER(1)";
116            default:
117                return "INT(1)";
118        }
119    }
120    
121    /**
122     * Get the binary type identifier corresponding to the database type.
123     * @param dbType the database type.
124     * @return the real binary type identifier (to be included in CREATE statement).
125     */
126    public static String getBinaryType(String dbType)
127    {
128        switch (dbType)
129        {
130            case ConnectionHelper.DATABASE_MYSQL:
131                return "LONGBLOB";
132            case ConnectionHelper.DATABASE_POSTGRES:
133                return "BYTEA";
134            default:
135                return "BLOB";
136        }
137    }
138    
139    /**
140     * Get the integer type identifier corresponding to the database type.
141     * @param dbType the database type.
142     * @return the integer type identifier (to be included in CREATE statement).
143     */
144    public static String getIntegerType(String dbType)
145    {
146        return "INTEGER";
147    }
148    
149    /**
150     * Get the date and time type identifier corresponding to the database type.
151     * @param dbType the database type.
152     * @return the real date and time type identifier (to be included in CREATE statement).
153     */
154    public static String getDateTimeType(String dbType)
155    {
156        return "TIMESTAMP";
157    }
158    
159    /**
160     * Get the "identity" type (int, integer, number, ...)
161     * @param dbType the database type.
162     * @return the "identity" type
163     */
164    public static String getIdentityType (String dbType)
165    {
166        switch (dbType)
167        {
168            case ConnectionHelper.DATABASE_ORACLE:
169                return "number NOT NULL";
170            case ConnectionHelper.DATABASE_MYSQL:
171            case ConnectionHelper.DATABASE_DERBY:
172                return "int NOT NULL";
173            default:
174                return "";
175        }
176    }
177    /**
178     * Get the "identity" string marker (AUTO_INCREMENT, SERIAL...)
179     * @param dbType the database type.
180     * @return the "identity" string marker.
181     */
182    public static String getIdentityMarker(String dbType)
183    {
184        switch (dbType)
185        {
186            case ConnectionHelper.DATABASE_MYSQL:
187                return "AUTO_INCREMENT";
188            case ConnectionHelper.DATABASE_DERBY:
189                return "GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)";
190            case ConnectionHelper.DATABASE_POSTGRES:
191                return "SERIAL";
192            default:
193                return "";
194        }
195    }
196    
197    /**
198     * Get the "identity" value (NULL, sequence next val call, ...)
199     * @param dbType the database type.
200     * @return the "identity" value.
201     */
202    public static String getIdentityValue(String dbType)
203    {
204        return "NULL";
205    }
206    
207    /**
208     * Get the "now" function (NOW(), CURDATE, ...)
209     * @param dbType the database type.
210     * @return the "now" function.
211     */
212    public static String getCurrentDateFunction(String dbType)
213    {
214        switch (dbType)
215        {
216            case ConnectionHelper.DATABASE_MYSQL:
217                return "NOW()";
218            case ConnectionHelper.DATABASE_ORACLE:
219                return "CURDATE";
220            case ConnectionHelper.DATABASE_POSTGRES:
221                return "CURRENT_TIMESTAMP";
222            case ConnectionHelper.DATABASE_DERBY:
223            default:
224                return "";
225        }
226    }
227    
228    /**
229     * Test if we have to include the identity column in INSERTs.
230     * @param dbType the database type.
231     * @return true if the identity is to be included in INSERT statements, false otherwise. 
232     */
233    public static boolean insertIdentity(String dbType)
234    {
235        switch (dbType)
236        {
237            case ConnectionHelper.DATABASE_DERBY:
238            case ConnectionHelper.DATABASE_POSTGRES:
239                return false;
240            default:
241                return true;
242        }
243    }
244    
245    /**
246     * Set the identity value into the statement.
247     * @param stmt the prepared statement.
248     * @param index the column index.
249     * @param dbType the database type.
250     * @throws SQLException if a SQL exception occurs.
251     */
252    public static void setIdentity(PreparedStatement stmt, int index, String dbType) throws SQLException
253    {
254        switch (dbType)
255        {
256            case ConnectionHelper.DATABASE_MYSQL:
257                stmt.setNull(index, Types.INTEGER);
258                break;
259            case ConnectionHelper.DATABASE_DERBY:
260                break;
261            case ConnectionHelper.DATABASE_POSTGRES:
262                stmt.setNull(index, Types.INTEGER);
263                break;
264            case ConnectionHelper.DATABASE_ORACLE:
265                return;
266            default:
267                stmt.setNull(index, Types.INTEGER);
268                break;
269                
270        }
271    }
272    
273    /**
274     * Get a SQL statement to rename a column.
275     * @param tableName the table name.
276     * @param column the column to rename.
277     * @param newName the new column name.
278     * @param dbType the database type.
279     * @param sqlDatabaseTypeExtensionPoint SQLDatabaseTypeExtensionPoint
280     * @return the SQL rename query.
281     */
282    public static String getRenameColumnStatement(String tableName, DbColumn column, String newName, String dbType, SQLDatabaseTypeExtensionPoint sqlDatabaseTypeExtensionPoint)
283    {
284        return getRenameColumnStatement(tableName, column.getName(), newName, column.getColumnTypeIdentifier(), dbType, sqlDatabaseTypeExtensionPoint);
285    }
286    
287    /**
288     * Get a SQL statement to rename a column.
289     * @param tableName the table name.
290     * @param columnName the current column name.
291     * @param newName the new column name.
292     * @param columnType the full column type string (i.e TEXT, VARCHAR(255), INT(1), and so on).
293     * @param dbType the database type.
294     * @param sqlDatabaseTypeExtensionPoint SQLDatabaseTypeExtensionPoint
295     * @return the SQL rename query.
296     */
297    public static String getRenameColumnStatement(String tableName, String columnName, String newName, String columnType, String dbType, SQLDatabaseTypeExtensionPoint sqlDatabaseTypeExtensionPoint)
298    {
299        StringBuilder buff = new StringBuilder();
300        
301        String escapedTableName = sqlDatabaseTypeExtensionPoint.languageEscapeTableName(dbType, tableName);
302        String escapedColumnName = sqlDatabaseTypeExtensionPoint.languageEscapeTableName(dbType, columnName);
303        String escapedNewName = sqlDatabaseTypeExtensionPoint.languageEscapeTableName(dbType, newName);
304        
305        switch (dbType)
306        {
307            case ConnectionHelper.DATABASE_MYSQL:
308                buff.append("ALTER TABLE ").append(escapedTableName);
309                buff.append(" CHANGE ").append(escapedColumnName);
310                buff.append(" ").append(escapedNewName).append(" ");
311                buff.append(columnType);
312                buff.append(" DEFAULT NULL");
313                break;
314            case ConnectionHelper.DATABASE_DERBY:
315                buff.append("RENAME COLUMN ").append(escapedTableName).append(".").append(escapedColumnName);
316                buff.append(" TO ").append(escapedNewName);
317                break;
318            case ConnectionHelper.DATABASE_ORACLE:
319            case ConnectionHelper.DATABASE_POSTGRES:
320            default:
321                buff.append("ALTER TABLE ").append(escapedTableName);
322                buff.append(" RENAME COLUMN ").append(escapedColumnName);
323                buff.append(" TO ").append(escapedNewName);
324                break;
325        }
326        
327        return buff.toString();
328    }
329    
330}