001/*
002 * HA-JDBC: High-Availability JDBC
003 * Copyright (c) 2004-2007 Paul Ferraro
004 * 
005 * This library is free software; you can redistribute it and/or modify it 
006 * under the terms of the GNU Lesser General Public License as published by the 
007 * Free Software Foundation; either version 2.1 of the License, or (at your 
008 * option) any later version.
009 * 
010 * This library is distributed in the hope that it will be useful, but WITHOUT
011 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or 
012 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License 
013 * for more details.
014 * 
015 * You should have received a copy of the GNU Lesser General Public License
016 * along with this library; if not, write to the Free Software Foundation, 
017 * Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
018 * 
019 * Contact: ferraro@users.sourceforge.net
020 */
021package net.sf.hajdbc.dialect;
022
023import java.sql.Connection;
024import java.sql.DatabaseMetaData;
025import java.sql.ResultSet;
026import java.sql.SQLException;
027import java.sql.Statement;
028import java.text.MessageFormat;
029import java.util.Collection;
030import java.util.Collections;
031import java.util.LinkedList;
032import java.util.List;
033import java.util.regex.Matcher;
034import java.util.regex.Pattern;
035
036import net.sf.hajdbc.ColumnProperties;
037import net.sf.hajdbc.Dialect;
038import net.sf.hajdbc.ForeignKeyConstraint;
039import net.sf.hajdbc.QualifiedName;
040import net.sf.hajdbc.SequenceProperties;
041import net.sf.hajdbc.TableProperties;
042import net.sf.hajdbc.UniqueConstraint;
043import net.sf.hajdbc.util.Strings;
044
045/**
046 * @author  Paul Ferraro
047 * @since   1.1
048 */
049@SuppressWarnings("nls")
050public class StandardDialect implements Dialect
051{
052        private Pattern selectForUpdatePattern = this.compile(this.selectForUpdatePattern());
053        private Pattern insertIntoTablePattern = this.compile(this.insertIntoTablePattern());
054        private Pattern sequencePattern = this.compile(this.sequencePattern());
055        private Pattern currentTimestampPattern = this.compile(this.currentTimestampPattern());
056        private Pattern currentDatePattern = this.compile(this.currentDatePattern());
057        private Pattern currentTimePattern = this.compile(this.currentTimePattern());
058        private Pattern randomPattern = this.compile(this.randomPattern());
059        
060        private Pattern compile(String pattern)
061        {
062                return Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
063        }
064        
065        protected String selectForUpdatePattern()
066        {
067                return "SELECT\\s+.+\\s+FOR\\s+UPDATE";
068        }
069
070        protected String insertIntoTablePattern()
071        {
072                return "INSERT\\s+(?:INTO\\s+)?'?([^'\\s\\(]+)";
073        }
074
075        protected String sequencePattern()
076        {
077                return "NEXT\\s+VALUE\\s+FOR\\s+'?([^',\\s\\(\\)]+)";
078        }
079        
080        protected String currentDatePattern()
081        {
082                return "(?<=\\W)CURRENT_DATE(?=\\W)";
083        }
084        
085        protected String currentTimePattern()
086        {
087                return "(?<=\\W)CURRENT_TIME(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)|(?<=\\W)LOCALTIME(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)";
088        }
089
090        protected String currentTimestampPattern()
091        {
092                return "(?<=\\W)CURRENT_TIMESTAMP(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)|(?<=\\W)LOCALTIMESTAMP(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)";
093        }
094        
095        protected String randomPattern()
096        {
097                return "(?<=\\W)RAND\\s*\\(\\s*\\)";
098        }
099
100        /**
101         * @see net.sf.hajdbc.Dialect#getSimpleSQL()
102         */
103        @Override
104        public String getSimpleSQL()
105        {
106                return this.executeFunctionSQL(this.currentTimestampFunction());
107        }
108
109        protected String executeFunctionFormat()
110        {
111                StringBuilder builder = new StringBuilder("SELECT {0}");
112                
113                String dummyTable = this.dummyTable();
114                
115                if (dummyTable != null)
116                {
117                        builder.append(" FROM ").append(dummyTable);
118                }
119                
120                return builder.toString();
121        }
122        
123        protected String executeFunctionSQL(String function)
124        {
125                return MessageFormat.format(this.executeFunctionFormat(), function);
126        }
127        
128        protected String currentTimestampFunction()
129        {
130                return "CURRENT_TIMESTAMP";
131        }
132        
133        protected String dummyTable()
134        {
135                return null;
136        }
137
138        /**
139         * @see net.sf.hajdbc.Dialect#getTruncateTableSQL(net.sf.hajdbc.TableProperties)
140         */
141        @Override
142        public String getTruncateTableSQL(TableProperties properties)
143        {
144                return MessageFormat.format(this.truncateTableFormat(), properties.getName());
145        }
146        
147        protected String truncateTableFormat()
148        {
149                return "DELETE FROM {0}";
150        }
151
152        /**
153         * @see net.sf.hajdbc.Dialect#getCreateForeignKeyConstraintSQL(net.sf.hajdbc.ForeignKeyConstraint)
154         */
155        @Override
156        public String getCreateForeignKeyConstraintSQL(ForeignKeyConstraint key)
157        {
158                return MessageFormat.format(this.createForeignKeyConstraintFormat(), key.getName(), key.getTable(), Strings.join(key.getColumnList(), Strings.PADDED_COMMA), key.getForeignTable(), Strings.join(key.getForeignColumnList(), Strings.PADDED_COMMA), key.getDeleteRule(), key.getUpdateRule(), key.getDeferrability());
159        }
160        
161        protected String createForeignKeyConstraintFormat()
162        {
163                return "ALTER TABLE {1} ADD CONSTRAINT {0} FOREIGN KEY ({2}) REFERENCES {3} ({4}) ON DELETE {5,choice,0#CASCADE|1#RESTRICT|2#SET NULL|3#NO ACTION|4#SET DEFAULT} ON UPDATE {6,choice,0#CASCADE|1#RESTRICT|2#SET NULL|3#NO ACTION|4#SET DEFAULT} {7,choice,5#DEFERRABLE INITIALLY DEFERRED|6#DEFERRABLE INITIALLY IMMEDIATE|7#NOT DEFERRABLE}";
164        }
165        
166        /**
167         * @see net.sf.hajdbc.Dialect#getDropForeignKeyConstraintSQL(net.sf.hajdbc.ForeignKeyConstraint)
168         */
169        @Override
170        public String getDropForeignKeyConstraintSQL(ForeignKeyConstraint key)
171        {
172                return MessageFormat.format(this.dropForeignKeyConstraintFormat(), key.getName(), key.getTable());
173        }
174        
175        protected String dropForeignKeyConstraintFormat()
176        {
177                return this.dropConstraintFormat();
178        }
179        
180        protected String dropConstraintFormat()
181        {
182                return "ALTER TABLE {1} DROP CONSTRAINT {0}";
183        }
184
185        /**
186         * @see net.sf.hajdbc.Dialect#getCreateUniqueConstraintSQL(net.sf.hajdbc.UniqueConstraint)
187         */
188        @Override
189        public String getCreateUniqueConstraintSQL(UniqueConstraint constraint)
190        {
191                return MessageFormat.format(this.createUniqueConstraintFormat(), constraint.getName(), constraint.getTable(), Strings.join(constraint.getColumnList(), Strings.PADDED_COMMA));
192        }
193        
194        protected String createUniqueConstraintFormat()
195        {
196                return "ALTER TABLE {1} ADD CONSTRAINT {0} UNIQUE ({2})";
197        }
198
199        /**
200         * @see net.sf.hajdbc.Dialect#getDropUniqueConstraintSQL(net.sf.hajdbc.UniqueConstraint)
201         */
202        @Override
203        public String getDropUniqueConstraintSQL(UniqueConstraint constraint)
204        {
205                return MessageFormat.format(this.dropUniqueConstraintFormat(), constraint.getName(), constraint.getTable());
206        }
207        
208        protected String dropUniqueConstraintFormat()
209        {
210                return this.dropConstraintFormat();
211        }
212
213        /**
214         * @see net.sf.hajdbc.Dialect#isSelectForUpdate(java.lang.String)
215         */
216        @Override
217        public boolean isSelectForUpdate(String sql)
218        {
219                return this.selectForUpdatePattern.matcher(sql).find();
220        }
221        
222        /**
223         * @see net.sf.hajdbc.Dialect#parseInsertTable(java.lang.String)
224         */
225        @Override
226        public String parseInsertTable(String sql)
227        {
228                return this.parse(this.insertIntoTablePattern, sql);
229        }
230
231        /**
232         * @see net.sf.hajdbc.Dialect#getDefaultSchemas(java.sql.DatabaseMetaData)
233         */
234        @Override
235        public List<String> getDefaultSchemas(DatabaseMetaData metaData) throws SQLException
236        {
237                return Collections.singletonList(metaData.getUserName());
238        }
239
240        protected String executeFunction(Connection connection, String function) throws SQLException
241        {
242                Statement statement = connection.createStatement();
243                
244                ResultSet resultSet = statement.executeQuery(this.executeFunctionSQL(function));
245                
246                resultSet.next();
247                
248                String value = resultSet.getString(1);
249                
250                resultSet.close();
251                statement.close();
252                
253                return value;
254        }
255
256        protected List<String> executeQuery(Connection connection, String sql) throws SQLException
257        {
258                List<String> resultList = new LinkedList<String>();
259                
260                Statement statement = connection.createStatement();
261                
262                ResultSet resultSet = statement.executeQuery(sql);
263                
264                while (resultSet.next())
265                {
266                        resultList.add(resultSet.getString(1));
267                }
268                
269                resultSet.close();
270                statement.close();
271                
272                return resultList;
273        }
274
275        /**
276         * @see net.sf.hajdbc.Dialect#parseSequence(java.lang.String)
277         */
278        @Override
279        public String parseSequence(String sql)
280        {
281                return this.parse(this.sequencePattern, sql);
282        }
283
284        /**
285         * @see net.sf.hajdbc.Dialect#getColumnType(net.sf.hajdbc.ColumnProperties)
286         */
287        @Override
288        public int getColumnType(ColumnProperties properties)
289        {
290                return properties.getType();
291        }
292
293        /**
294         * @see net.sf.hajdbc.Dialect#getSequences(java.sql.DatabaseMetaData)
295         */
296        @Override
297        public Collection<QualifiedName> getSequences(DatabaseMetaData metaData) throws SQLException
298        {
299                List<QualifiedName> sequenceList = new LinkedList<QualifiedName>();
300                
301                ResultSet resultSet = metaData.getTables(Strings.EMPTY, null, Strings.ANY, new String[] { this.sequenceTableType() });
302                
303                while (resultSet.next())
304                {
305                        sequenceList.add(new QualifiedName(resultSet.getString("TABLE_SCHEM"), resultSet.getString("TABLE_NAME")));
306                }
307                
308                resultSet.close();
309                
310                return sequenceList;
311        }
312
313        protected String sequenceTableType()
314        {
315                return "SEQUENCE";
316        }
317
318        /**
319         * @see net.sf.hajdbc.Dialect#getNextSequenceValueSQL(net.sf.hajdbc.SequenceProperties)
320         */
321        @Override
322        public String getNextSequenceValueSQL(SequenceProperties sequence)
323        {
324                return this.executeFunctionSQL(MessageFormat.format(this.nextSequenceValueFormat(), sequence.getName()));
325        }
326        
327        protected String nextSequenceValueFormat()
328        {
329                return "NEXT VALUE FOR {0}";
330        }
331        
332        /**
333         * @see net.sf.hajdbc.Dialect#getAlterSequenceSQL(net.sf.hajdbc.SequenceProperties, long)
334         */
335        @Override
336        public String getAlterSequenceSQL(SequenceProperties sequence, long value)
337        {
338                return MessageFormat.format(this.alterSequenceFormat(), sequence.getName(), String.valueOf(value));
339        }
340        
341        protected String alterSequenceFormat()
342        {
343                return "ALTER SEQUENCE {0} RESTART WITH {1}";
344        }
345
346        @Override
347        public String getAlterIdentityColumnSQL(TableProperties table, ColumnProperties column, long value) throws SQLException
348        {
349                return MessageFormat.format(this.alterIdentityColumnFormat(), table.getName(), column.getName(), String.valueOf(value));
350        }
351
352        protected String alterIdentityColumnFormat()
353        {
354                return "ALTER TABLE {0} ALTER COLUMN {1} RESTART WITH {2}";
355        }
356        
357        /**
358         * @see net.sf.hajdbc.Dialect#getIdentifierPattern(java.sql.DatabaseMetaData)
359         */
360        @Override
361        public Pattern getIdentifierPattern(DatabaseMetaData metaData) throws SQLException
362        {
363                return Pattern.compile(MessageFormat.format("[a-zA-Z][\\w{0}]*", Pattern.quote(metaData.getExtraNameCharacters())));
364        }
365
366        protected String parse(Pattern pattern, String string)
367        {
368                Matcher matcher = pattern.matcher(string);
369                
370                return matcher.find() ? matcher.group(1) : null;
371        }
372
373        /**
374         * @see net.sf.hajdbc.Dialect#evaluateCurrentDate(java.lang.String, java.sql.Date)
375         */
376        @Override
377        public String evaluateCurrentDate(String sql, java.sql.Date date)
378        {
379                return this.evaluateTemporal(sql, this.currentDatePattern, date, this.dateLiteralFormat());
380        }
381        
382        protected String dateLiteralFormat()
383        {
384                return "DATE ''{0}''";
385        }
386
387        /**
388         * @see net.sf.hajdbc.Dialect#evaluateCurrentTime(java.lang.String, java.sql.Time)
389         */
390        @Override
391        public String evaluateCurrentTime(String sql, java.sql.Time time)
392        {
393                return this.evaluateTemporal(sql, this.currentTimePattern, time, this.timeLiteralFormat());
394        }
395        
396        protected String timeLiteralFormat()
397        {
398                return "TIME ''{0}''";
399        }
400
401        /**
402         * @see net.sf.hajdbc.Dialect#evaluateCurrentTimestamp(java.lang.String, java.sql.Timestamp)
403         */
404        @Override
405        public String evaluateCurrentTimestamp(String sql, java.sql.Timestamp timestamp)
406        {
407                return this.evaluateTemporal(sql, this.currentTimestampPattern, timestamp, this.timestampLiteralFormat());
408        }
409        
410        protected String timestampLiteralFormat()
411        {
412                return "TIMESTAMP ''{0}''";
413        }
414
415        private String evaluateTemporal(String sql, Pattern pattern, java.util.Date date, String format)
416        {
417                return pattern.matcher(sql).replaceAll(MessageFormat.format(format, date.toString()));
418        }
419
420        /**
421         * @see net.sf.hajdbc.Dialect#evaluateRand(java.lang.String)
422         */
423        @Override
424        public String evaluateRand(String sql)
425        {       
426                StringBuffer buffer = new StringBuffer();
427                Matcher matcher = this.randomPattern.matcher(sql);
428                
429                while (matcher.find())
430                {
431                        matcher.appendReplacement(buffer, Double.toString(Math.random()));
432                }
433                
434                return matcher.appendTail(buffer).toString();
435        }
436}