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.cache;
022
023import java.sql.DatabaseMetaData;
024import java.sql.ResultSet;
025import java.sql.ResultSetMetaData;
026import java.sql.SQLException;
027import java.sql.Statement;
028import java.util.ArrayList;
029import java.util.Collection;
030import java.util.HashMap;
031import java.util.HashSet;
032import java.util.LinkedList;
033import java.util.List;
034import java.util.Map;
035import java.util.Set;
036import java.util.regex.Pattern;
037
038import net.sf.hajdbc.ColumnProperties;
039import net.sf.hajdbc.Dialect;
040import net.sf.hajdbc.ForeignKeyConstraint;
041import net.sf.hajdbc.Messages;
042import net.sf.hajdbc.QualifiedName;
043import net.sf.hajdbc.SequenceProperties;
044import net.sf.hajdbc.UniqueConstraint;
045import net.sf.hajdbc.util.Strings;
046
047/**
048 * Processes database meta data into useful structures.
049 * @author Paul Ferraro
050 */
051@SuppressWarnings("nls")
052public class DatabaseMetaDataSupportImpl implements DatabaseMetaDataSupport
053{
054        // As defined in SQL-92 specification: http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt
055        private static final String[] SQL_92_RESERVED_WORDS = new String[] {
056                "absolute", "action", "add", "all", "allocate", "alter", "and", "any", "are", "as", "asc", "assertion", "at", "authorization", "avg",
057                "begin", "between", "bit", "bit_length", "both", "by",
058                "cascade", "cascaded", "case", "cast", "catalog", "char", "character", "char_length", "character_length", "check", "close", "coalesce", "collate", "collation", "column", "commit", "connect", "connection", "constraint", "constraints", "continue", "convert", "corresponding", "count", "create", "cross", "current", "current_date", "current_time", "current_timestamp", "current_user", "cursor",
059                "date", "day", "deallocate", "dec", "decimal", "declare", "default", "deferrable", "deferred", "delete", "desc", "describe", "descriptor", "diagnostics", "disconnect", "distinct", "domain", "double", "drop",
060                "else", "end", "end-exec", "escape", "except", "exception", "exec", "execute", "exists", "external", "extract",
061                "false", "fetch", "first", "float", "for", "foreign", "found", "from", "full",
062                "get", "global", "go", "goto", "grant", "group",
063                "having", "hour",
064                "identity", "immediate", "in", "indicator", "initially", "inner", "input", "insensitive", "insert", "int", "integer", "intersect", "interval", "into", "is", "isolation",
065                "join",
066                "key",
067                "language", "last", "leading", "left", "level", "like", "local", "lower",
068                "match", "max", "min", "minute", "module", "month",
069                "names", "national", "natural", "nchar", "next", "no", "not", "null", "nullif", "numeric",
070                "octet_length", "of", "on", "only", "open", "option", "or", "order", "outer", "output", "overlaps",
071                "pad", "partial", "position", "precision", "prepare", "preserve", "primary", "prior", "privileges", "procedure", "public",
072                "read", "real", "references", "relative", "restrict", "revoke", "right", "rollback", "rows",
073                "schema", "scroll", "second", "section", "select", "session", "session_user", "set", "size", "smallint", "some", "space", "sql", "sqlcode", "sqlerror", "sqlstate", "substring", "sum", "system_user",
074                "table", "temporary", "then", "time", "timestamp", "timezone_hour", "timezone_minute", "to", "trailing", "transaction", "translate", "translation", "trim", "true",
075                "union", "unique", "unknown", "update", "upper", "usage", "user", "using",
076                "value", "values", "varchar", "varying", "view",
077                "when", "whenever", "where", "with", "work", "write",
078                "year",
079                "zone"
080        };
081        
082        private static final Pattern UPPER_CASE_PATTERN = Pattern.compile("[A-Z]");
083        private static final Pattern LOWER_CASE_PATTERN = Pattern.compile("[a-z]");
084        
085        private Dialect dialect;
086        private Set<String> reservedIdentifierSet = new HashSet<String>();
087        private Pattern identifierPattern;
088        private String quote;
089        private boolean supportsMixedCaseIdentifiers;
090        private boolean supportsMixedCaseQuotedIdentifiers;
091        private boolean storesLowerCaseIdentifiers;
092        private boolean storesLowerCaseQuotedIdentifiers;
093        private boolean storesUpperCaseIdentifiers;
094        private boolean storesUpperCaseQuotedIdentifiers;
095        private boolean supportsSchemasInDDL;
096        private boolean supportsSchemasInDML;
097        
098        /**
099         * Constructs a new DatabaseMetaDataSupport using the specified DatabaseMetaData implementation.
100         * @param metaData a DatabaseMetaData implementation
101         * @param dialect the vendor-specific dialect of the cluster
102         * @throws SQLException if an error occurs access DatabaseMetaData
103         */
104        public DatabaseMetaDataSupportImpl(DatabaseMetaData metaData, Dialect dialect) throws SQLException
105        {
106                this.dialect = dialect;
107                
108                this.identifierPattern = dialect.getIdentifierPattern(metaData);
109                this.quote = metaData.getIdentifierQuoteString();
110                this.supportsMixedCaseIdentifiers = metaData.supportsMixedCaseIdentifiers();
111                this.supportsMixedCaseQuotedIdentifiers = metaData.supportsMixedCaseQuotedIdentifiers();
112                this.storesLowerCaseIdentifiers = metaData.storesLowerCaseIdentifiers();
113                this.storesLowerCaseQuotedIdentifiers = metaData.storesLowerCaseQuotedIdentifiers();
114                this.storesUpperCaseIdentifiers = metaData.storesUpperCaseIdentifiers();
115                this.storesUpperCaseQuotedIdentifiers = metaData.storesUpperCaseQuotedIdentifiers();
116                this.supportsSchemasInDML = metaData.supportsSchemasInDataManipulation();
117                this.supportsSchemasInDDL = metaData.supportsSchemasInTableDefinitions();
118                
119                for (String word: SQL_92_RESERVED_WORDS)
120                {
121                        this.reservedIdentifierSet.add(this.normalizeCase(word));
122                }
123                
124                for (String word: metaData.getSQLKeywords().split(Strings.COMMA))
125                {
126                        this.reservedIdentifierSet.add(this.normalizeCase(word));
127                }
128        }
129        
130        /**
131         * Returns all tables in this database mapped by schema.
132         * @param metaData a DatabaseMetaData implementation
133         * @return a Map of schema name to Collection of table names
134         * @throws SQLException if an error occurs access DatabaseMetaData
135         */
136        @Override
137        public Collection<QualifiedName> getTables(DatabaseMetaData metaData) throws SQLException
138        {
139                List<QualifiedName> list = new LinkedList<QualifiedName>();
140                
141                ResultSet resultSet = metaData.getTables(this.getCatalog(metaData), null, Strings.ANY, new String[] { "TABLE" });
142                
143                while (resultSet.next())
144                {
145                        list.add(new QualifiedName(resultSet.getString("TABLE_SCHEM"), resultSet.getString("TABLE_NAME")));
146                }
147                
148                resultSet.close();
149                
150                return list;
151        }
152
153        /**
154         * Returns the columns of the specified table.
155         * @param metaData a DatabaseMetaData implementation
156         * @param table a schema qualified table name
157         * @return a Map of column name to column properties
158         * @throws SQLException if an error occurs access DatabaseMetaData
159         */
160        @Override
161        public Map<String, ColumnProperties> getColumns(DatabaseMetaData metaData, QualifiedName table) throws SQLException
162        {
163                Map<String, ColumnProperties> columnMap = new HashMap<String, ColumnProperties>();
164                
165                Statement statement = metaData.getConnection().createStatement();
166                
167                try
168                {
169                        ResultSetMetaData resultSet = statement.executeQuery("SELECT * FROM " + this.qualifyNameForDML(table) + " WHERE 0=1").getMetaData();
170                        
171                        for (int i = 1; i <= resultSet.getColumnCount(); ++i)
172                        {
173                                String column = this.quote(resultSet.getColumnName(i));
174                                int type = resultSet.getColumnType(i);
175                                String nativeType = resultSet.getColumnTypeName(i);
176                                boolean autoIncrement = resultSet.isAutoIncrement(i);
177                                
178                                columnMap.put(column, new ColumnPropertiesImpl(column, type, nativeType, null, null, autoIncrement));
179                        }
180                }
181                finally
182                {
183                        statement.close();
184                }
185                
186                return columnMap;
187        }
188
189        /**
190         * Returns the primary key of the specified table.
191         * @param metaData a DatabaseMetaData implementation
192         * @param table a schema qualified table name
193         * @return a unique constraint
194         * @throws SQLException if an error occurs access DatabaseMetaData
195         */
196        @Override
197        public UniqueConstraint getPrimaryKey(DatabaseMetaData metaData, QualifiedName table) throws SQLException
198        {
199                UniqueConstraint constraint = null;
200
201                ResultSet resultSet = metaData.getPrimaryKeys(this.getCatalog(metaData), this.getSchema(table), table.getName());
202                
203                while (resultSet.next())
204                {
205                        String name = this.quote(resultSet.getString("PK_NAME"));
206
207                        if (constraint == null)
208                        {
209                                constraint = new UniqueConstraintImpl(name, this.qualifyNameForDDL(table));
210                        }
211                        
212                        String column = this.quote(resultSet.getString("COLUMN_NAME"));
213                        
214                        constraint.getColumnList().add(column);
215                }
216                
217                resultSet.close();
218                
219                return constraint;
220        }
221
222        /**
223         * Returns the foreign key constraints on the specified table.
224         * @param metaData a DatabaseMetaData implementation
225         * @param table a schema qualified table name
226         * @return a Collection of foreign key constraints.
227         * @throws SQLException if an error occurs access DatabaseMetaData
228         */
229        @Override
230        public Collection<ForeignKeyConstraint> getForeignKeyConstraints(DatabaseMetaData metaData, QualifiedName table) throws SQLException
231        {
232                Map<String, ForeignKeyConstraint> foreignKeyMap = new HashMap<String, ForeignKeyConstraint>();
233                
234                ResultSet resultSet = metaData.getImportedKeys(this.getCatalog(metaData), this.getSchema(table), table.getName());
235                
236                while (resultSet.next())
237                {
238                        String name = this.quote(resultSet.getString("FK_NAME"));
239                        
240                        ForeignKeyConstraint foreignKey = foreignKeyMap.get(name);
241                        
242                        if (foreignKey == null)
243                        {
244                                foreignKey = new ForeignKeyConstraintImpl(name, this.qualifyNameForDDL(table));
245                                
246                                String foreignSchema = this.quote(resultSet.getString("PKTABLE_SCHEM"));
247                                String foreignTable = this.quote(resultSet.getString("PKTABLE_NAME"));
248                                
249                                foreignKey.setForeignTable(this.qualifyNameForDDL(new QualifiedName(foreignSchema, foreignTable)));
250                                foreignKey.setDeleteRule(resultSet.getInt("DELETE_RULE"));
251                                foreignKey.setUpdateRule(resultSet.getInt("UPDATE_RULE"));
252                                foreignKey.setDeferrability(resultSet.getInt("DEFERRABILITY"));
253                                
254                                foreignKeyMap.put(name, foreignKey);
255                        }
256                        
257                        String column = this.quote(resultSet.getString("FKCOLUMN_NAME"));
258                        String foreignColumn = this.quote(resultSet.getString("PKCOLUMN_NAME"));
259
260                        foreignKey.getColumnList().add(column);
261                        foreignKey.getForeignColumnList().add(foreignColumn);
262                }
263                
264                resultSet.close();
265                
266                return foreignKeyMap.values();
267        }
268
269        /**
270         * Returns the unique constraints on the specified table - excluding the primary key of the table.
271         * @param metaData a schema qualified table name
272         * @param table a qualified table name
273         * @param primaryKey the primary key of this table
274         * @return a Collection of unique constraints.
275         * @throws SQLException if an error occurs access DatabaseMetaData
276         */
277        @Override
278        public Collection<UniqueConstraint> getUniqueConstraints(DatabaseMetaData metaData, QualifiedName table, UniqueConstraint primaryKey) throws SQLException
279        {
280                Map<String, UniqueConstraint> keyMap = new HashMap<String, UniqueConstraint>();
281                
282                ResultSet resultSet = metaData.getIndexInfo(this.getCatalog(metaData), this.getSchema(table), table.getName(), true, false);
283                
284                while (resultSet.next())
285                {
286                        if (resultSet.getShort("TYPE") == DatabaseMetaData.tableIndexHashed)
287                        {
288                                String name = this.quote(resultSet.getString("INDEX_NAME"));
289                                
290                                // Don't include the primary key
291                                if ((primaryKey != null) && name.equals(primaryKey.getName())) continue;
292                                
293                                UniqueConstraint key = keyMap.get(name);
294                                
295                                if (key == null)
296                                {
297                                        key = new UniqueConstraintImpl(name, this.qualifyNameForDDL(table));
298                                        
299                                        keyMap.put(name, key);
300                                }
301                                
302                                String column = this.quote(resultSet.getString("COLUMN_NAME"));
303
304                                key.getColumnList().add(column);
305                        }
306                }
307                
308                resultSet.close();
309                
310                return keyMap.values();
311        }
312
313        /**
314         * Returns the schema qualified name of the specified table suitable for use in a data modification language (DML) statement.
315         * @param name a schema qualified name
316         * @return a Collection of unique constraints.
317         */
318        @Override
319        public String qualifyNameForDML(QualifiedName name)
320        {
321                return this.qualifyName(name, this.supportsSchemasInDML);
322        }
323
324        /**
325         * Returns the schema qualified name of the specified table suitable for use in a data definition language (DDL) statement.
326         * @param name a schema qualified name
327         * @return a Collection of unique constraints.
328         */
329        @Override
330        public String qualifyNameForDDL(QualifiedName name)
331        {
332                return this.qualifyName(name, this.supportsSchemasInDDL);
333        }
334
335        private String qualifyName(QualifiedName name, boolean supportsSchemas)
336        {
337                StringBuilder builder = new StringBuilder();
338                
339                String schema = name.getSchema();
340                
341                if (supportsSchemas && (schema != null))
342                {
343                        builder.append(this.quote(schema)).append(Strings.DOT);
344                }
345                
346                return builder.append(this.quote(name.getName())).toString();
347        }
348        
349        private String getCatalog(DatabaseMetaData metaData) throws SQLException
350        {
351                String catalog = metaData.getConnection().getCatalog();
352                
353                return (catalog != null) ? catalog : Strings.EMPTY;
354        }
355        
356        private String getSchema(QualifiedName name)
357        {
358                String schema = name.getSchema();
359                
360                return (schema != null) ? schema : Strings.EMPTY;
361        }
362        
363        private String quote(String identifier)
364        {
365                if (identifier == null) return null;
366                
367                int quoteLength = this.quote.length();
368                
369                // Strip any existing quoting
370                String raw = (identifier.startsWith(this.quote) && identifier.endsWith(this.quote)) ? identifier.substring(quoteLength, identifier.length() - quoteLength) : identifier;
371                
372                String normal = this.normalizeCase(raw);
373                
374                // Quote reserved identifiers
375                boolean requiresQuoting = this.reservedIdentifierSet.contains(normal);
376                
377                // Quote identifiers containing special characters
378                requiresQuoting |= !this.identifierPattern.matcher(raw).matches();
379                
380                // Quote mixed-case identifiers if detected and supported by DBMS
381                requiresQuoting |= !this.supportsMixedCaseIdentifiers && this.supportsMixedCaseQuotedIdentifiers && ((this.storesLowerCaseIdentifiers && !this.storesLowerCaseQuotedIdentifiers && UPPER_CASE_PATTERN.matcher(raw).find()) || (this.storesUpperCaseIdentifiers && !this.storesUpperCaseQuotedIdentifiers && LOWER_CASE_PATTERN.matcher(raw).find()));
382                
383                return requiresQuoting ? this.quote + this.normalizeCaseQuoted(raw) + this.quote : normal;
384        }
385        
386        private String normalizeCase(String identifier)
387        {
388                if (this.storesLowerCaseIdentifiers) return identifier.toLowerCase();
389                
390                if (this.storesUpperCaseIdentifiers) return identifier.toUpperCase();
391                
392                return identifier;
393        }
394        
395        private String normalizeCaseQuoted(String identifier)
396        {
397                if (this.storesLowerCaseQuotedIdentifiers) return identifier.toLowerCase();
398                
399                if (this.storesUpperCaseQuotedIdentifiers) return identifier.toUpperCase();
400                
401                return identifier;
402        }
403        
404        private String normalize(String qualifiedName, String defaultSchema)
405        {
406                String parts[] = qualifiedName.split(Pattern.quote(Strings.DOT));
407
408                String name = parts[parts.length - 1];
409                String schema = (parts.length > 1) ? parts[parts.length - 2] : defaultSchema;
410                        
411                return this.qualifyNameForDML(new QualifiedName(schema, name));
412        }
413        
414        /**
415         * Returns a collection of sequences using dialect specific logic.
416         * @param metaData database meta data
417         * @return a collection of sequences
418         * @throws SQLException
419         */
420        @Override
421        public Collection<SequenceProperties> getSequences(DatabaseMetaData metaData) throws SQLException
422        {
423                Collection<QualifiedName> sequences = this.dialect.getSequences(metaData);
424                
425                List<SequenceProperties> sequenceList = new ArrayList<SequenceProperties>(sequences.size());
426                
427                for (QualifiedName sequence: sequences)
428                {
429                        sequenceList.add(new SequencePropertiesImpl(this.qualifyNameForDML(sequence)));
430                }
431                
432                return sequenceList;
433        }
434        
435        /**
436         * Locates an object from a map keyed by schema qualified name.
437         * @param <T> an object
438         * @param map a map of database 
439         * @param name the name of the object to locate
440         * @param defaultSchemaList a list of default schemas
441         * @return the object with the specified name
442         * @throws SQLException
443         */
444        @Override
445        public <T> T find(Map<String, T> map, String name, List<String> defaultSchemaList) throws SQLException
446        {
447                T properties = map.get(this.normalize(name, null));
448                
449                if (properties == null)
450                {
451                        for (String schema: defaultSchemaList)
452                        {
453                                if (properties == null)
454                                {
455                                        properties = map.get(this.normalize(name, schema));
456                                }
457                        }
458                }
459                
460                if (properties == null)
461                {
462                        throw new SQLException(Messages.getMessage(Messages.SCHEMA_LOOKUP_FAILED, name, defaultSchemaList, this.dialect.getClass().getName() + ".getDefaultSchemas()"));
463                }
464                
465                return properties;
466        }
467        
468        /**
469         * Identifies any identity columns from the from the specified collection of columns
470         * @param columns the columns of a table
471         * @return a collection of column names
472         * @throws SQLException
473         */
474        @Override
475        public Collection<String> getIdentityColumns(Collection<ColumnProperties> columns) throws SQLException
476        {
477                List<String> columnList = new LinkedList<String>();
478                
479                for (ColumnProperties column: columns)
480                {
481                        if (column.isAutoIncrement())
482                        {
483                                columnList.add(column.getName());
484                        }
485                }
486                
487                return columnList;
488        }
489}