001/*
002// This software is subject to the terms of the Eclipse Public License v1.0
003// Agreement, available at the following URL:
004// http://www.eclipse.org/legal/epl-v10.html.
005// You must accept the terms of that agreement to use this software.
006//
007// Copyright (C) 2006-2011 Pentaho and others
008// Copyright (C) 2006-2007 Cincom Systems, Inc.
009// Copyright (C) 2006-2007 JasperSoft
010// All Rights Reserved.
011*/
012package mondrian.gui;
013
014import org.apache.log4j.Logger;
015
016import java.sql.*;
017import java.util.*;
018
019/**
020 */
021public class JdbcMetaData {
022
023    private static final Logger LOGGER = Logger.getLogger(JdbcMetaData.class);
024
025    // E.g. "org.postgresql.Driver"
026    String jdbcDriverClassName = null;
027
028    // E.g. "jdbc:postgresql://localhost:5432/hello?user=postgres&password=post"
029    String jdbcUsername = null;
030
031    String jdbcConnectionUrl = null;
032    String jdbcPassword = null;
033    String jdbcSchema = null;
034    boolean requireSchema = false;
035
036    Connection conn = null;
037    DatabaseMetaData md = null;
038
039    Workbench workbench;
040
041    public static final String LEVEL_SEPARATOR = "->";
042
043    private String errMsg = null;
044    private Database db = new Database();
045
046    public JdbcMetaData(
047        Workbench wb,
048        String jdbcDriverClassName,
049        String jdbcConnectionUrl,
050        String jdbcUsername,
051        String jdbcPassword,
052        String jdbcSchema,
053        boolean requireSchema)
054    {
055        this.workbench = wb;
056        this.jdbcConnectionUrl = jdbcConnectionUrl;
057        this.jdbcDriverClassName = jdbcDriverClassName;
058        this.jdbcUsername = jdbcUsername;
059        this.jdbcPassword = jdbcPassword;
060        this.jdbcSchema = jdbcSchema;
061        this.requireSchema = requireSchema;
062
063        if (initConnection() == null) {
064            setAllSchemas();
065            closeConnection();
066        }
067    }
068
069    public boolean getRequireSchema() {
070        return requireSchema;
071    }
072
073    /**
074     * @return the workbench i18n converter
075     */
076    public I18n getResourceConverter() {
077        return workbench.getResourceConverter();
078    }
079
080    /**
081     * Tests database connection. Called from Preferences dialog button test
082     * connection.
083     */
084    public JdbcMetaData(
085        String jdbcDriverClassName,
086        String jdbcConnectionUrl,
087        String jdbcUsername,
088        String jdbcPassword)
089    {
090        this.jdbcConnectionUrl = jdbcConnectionUrl;
091        this.jdbcDriverClassName = jdbcDriverClassName;
092        this.jdbcUsername = jdbcUsername;
093        this.jdbcPassword = jdbcPassword;
094
095        if (initConnection() == null) {
096            closeConnection();
097        }
098    }
099
100    /* Creates a database connection and initializes the meta data details */
101    public String initConnection() {
102        LOGGER.debug("JdbcMetaData: initConnection");
103
104        try {
105            if (jdbcDriverClassName == null
106                || jdbcDriverClassName.trim().length() == 0
107                || jdbcConnectionUrl == null
108                || jdbcConnectionUrl.trim().length() == 0)
109            {
110                errMsg = getResourceConverter().getFormattedString(
111                    "jdbcMetaData.blank.exception",
112                    "Driver={0}\nConnection URL={1}\nUse Preferences to set Database Connection parameters first and then open a Schema",
113                    jdbcDriverClassName,
114                    jdbcConnectionUrl);
115                return errMsg;
116            }
117
118            Class.forName(jdbcDriverClassName);
119
120            if (jdbcUsername != null && jdbcUsername.length() > 0) {
121                conn = DriverManager.getConnection(
122                    jdbcConnectionUrl, jdbcUsername, jdbcPassword);
123            } else {
124                conn = DriverManager.getConnection(jdbcConnectionUrl);
125            }
126
127            LOGGER.debug("JDBC connection OPEN");
128            md = conn.getMetaData();
129
130            db.productName = md.getDatabaseProductName();
131            db.productVersion = md.getDatabaseProductVersion();
132            db.catalogName = conn.getCatalog();
133
134            LOGGER.debug("Catalog name = " + db.catalogName);
135            LOGGER.debug("Database Product Name: " + db.productName);
136            LOGGER.debug("Database Product Version: " + db.productVersion);
137            LOGGER.debug("JdbcMetaData: initConnection - no error");
138            return null;
139        } catch (Exception e) {
140            errMsg =
141                e.getClass().getSimpleName() + " : " + e.getLocalizedMessage();
142            LOGGER.error("Database connection exception : " + errMsg, e);
143            return errMsg;
144            //e.printStackTrace();
145        }
146    }
147
148    public void closeConnection() {
149        if (conn == null) {
150            return;
151        }
152
153        md = null;
154        try {
155            conn.close();
156            LOGGER.debug("JDBC connection CLOSE");
157        } catch (Exception e) {
158            LOGGER.error(e);
159        }
160        conn = null;
161    }
162
163    /**
164     * Check to see if the schemaName is in the list of allowed jdbc schemas
165     *
166     * @param schemaName the name of the schmea
167     * @return true if found, or if jdbcSchema is null
168     */
169    private boolean inJdbcSchemas(String schemaName) {
170        if (jdbcSchema == null || jdbcSchema.trim().length() == 0) {
171            return true;
172        }
173
174        String schemas[] = jdbcSchema.split("[,;]");
175        for (String schema : schemas) {
176            if (schema.trim().equalsIgnoreCase(schemaName)) {
177                return true;
178            }
179        }
180
181        return false;
182    }
183
184    /* list all schemas in the currently connected database */
185    public List<String> listAllSchemas() {
186        LOGGER.debug("JdbcMetaData: listAllSchemas");
187
188        if (initConnection() != null) {
189            return null;
190        }
191
192        List<String> schemaNames = new ArrayList<String>();
193        ResultSet rs = null;
194        try {
195            rs = md.getSchemas();
196
197            while (rs.next()) {
198                String schemaName = rs.getString("TABLE_SCHEM");
199                schemaNames.add(schemaName);
200            }
201        } catch (Exception e) {
202            LOGGER.debug(
203                "Exception : Database does not support schemas." + e
204                    .getMessage());
205            return null;
206        } finally {
207            try {
208                rs.close();
209                closeConnection();
210            } catch (Exception e) {
211                // ignore
212            }
213        }
214
215
216        return schemaNames;
217    }
218
219    /* set all schemas in the currently connected database */
220    private void setAllSchemas() {
221        LOGGER.debug("JdbcMetaData: setAllSchemas");
222
223        ResultSet rs = null;
224        boolean gotSchema = false;
225        try {
226            rs = md.getSchemas();
227            while (rs.next()) {
228                String schemaName = rs.getString("TABLE_SCHEM");
229                if (inJdbcSchemas(schemaName)) {
230                    DbSchema dbs = new DbSchema();
231                    dbs.name = schemaName;
232                    LOGGER.debug("JdbcMetaData: setAllTables - " + dbs.name);
233                    setAllTables(dbs);
234                    db.addDbSchema(dbs);
235                    gotSchema = true;
236                }
237            }
238        } catch (Exception e) {
239            LOGGER.debug(
240                "Exception : Database does not support schemas." + e
241                    .getMessage());
242        } finally {
243            try {
244                rs.close();
245            } catch (Exception e) {
246                // ignore
247            }
248        }
249
250        if (!gotSchema) {
251            LOGGER.debug(
252                "JdbcMetaData: setAllSchemas - tables with no schema name");
253            DbSchema dbs = new DbSchema();
254            dbs.name = null;    //tables with no schema name
255            setAllTables(dbs);
256            db.addDbSchema(dbs);
257        }
258    }
259
260    /* set all tables in the currently connected database */
261    private void setAllTables(DbSchema dbs) {
262        LOGGER.debug("JdbcMetaData: Loading schema: '" + dbs.name + "'");
263        ResultSet rs = null;
264        try {
265            // Tables and views can be used
266            try {
267                rs = md.getTables(
268                    null, dbs.name, null, new String[]{"TABLE", "VIEW"});
269            } catch (Exception e) {
270                // this is a workaround for databases that throw an exception
271                // when views are requested.
272                rs = md.getTables(null, dbs.name, null, new String[]{"TABLE"});
273            }
274            while (rs.next()) {
275                // Oracle 10g Driver returns bogus BIN$ tables that cause
276                // exceptions
277                String tbname = rs.getString("TABLE_NAME");
278                if (!tbname.matches("(?!BIN\\$).+")) {
279                    continue;
280                }
281
282                DbTable dbt;
283
284                /* Note: Imported keys are foreign keys which are primary keys
285                 * of in some other tables; Exported keys are primary keys which
286                 * are referenced as foreign keys in other tables.
287                 */
288                ResultSet rs_fks = md.getImportedKeys(null, dbs.name, tbname);
289                try {
290                    if (rs_fks.next()) {
291                        dbt = new FactTable();
292                        do {
293                            ((FactTable) dbt).addFks(
294                                rs_fks.getString("FKCOLUMN_NAME"),
295                                rs_fks.getString("pktable_name"));
296                        } while (rs_fks.next());
297                    } else {
298                        dbt = new DbTable();
299                    }
300                } finally {
301                    try {
302                        rs_fks.close();
303                    } catch (Exception e) {
304                        // ignore
305                    }
306                }
307                dbt.schemaName = dbs.name;
308                dbt.name = tbname;
309                setPKey(dbt);
310                // Lazy loading
311                // setColumns(dbt);
312                dbs.addDbTable(dbt);
313                db.addDbTable(dbt);
314            }
315        } catch (Exception e) {
316            LOGGER.error("setAllTables", e);
317        } finally {
318            try {
319                rs.close();
320            } catch (Exception e) {
321                // ignore
322            }
323        }
324    }
325
326    /**
327     * Gets the Primary key name for a given table name.
328     * This key may be a  composite key made of multiple columns.
329     */
330    private void setPKey(DbTable dbt) {
331        ResultSet rs = null;
332        try {
333            rs = md.getPrimaryKeys(null, dbt.schemaName, dbt.name);
334            if (rs.next()) {
335                //   // a column may have been given a primary key name
336                //===dbt.pk = rs.getString("PK_NAME");
337                // We need the column name which is primary key for the given
338                // table.
339                dbt.pk = rs.getString("column_name");
340            }
341        } catch (Exception e) {
342            LOGGER.error("setPKey", e);
343        } finally {
344            try {
345                rs.close();
346            } catch (Exception e) {
347                // ignore
348            }
349        }
350    }
351
352    private void setColumns(String schemaName, String tableName) {
353        LOGGER.debug(
354            "setColumns: <" + tableName + "> in schema <" + schemaName + ">");
355        DbTable dbt = db.getTable(schemaName, tableName);
356        if (dbt == null) {
357            LOGGER.debug(
358                "No table with name: <"
359                + tableName
360                + "> in schema <"
361                + schemaName
362                + ">");
363            return;
364        }
365        if (initConnection() != null) {
366            return;
367        }
368        try {
369            setColumns(dbt);
370            LOGGER.debug("got " + dbt.colsDataType.size() + " columns");
371        } finally {
372            closeConnection();
373        }
374    }
375
376    /**
377     * Gets all columns for a given table name.
378     *
379     * Assumes that the caller has acquired a connection using
380     * {@link #initConnection()}.
381     */
382    private void setColumns(DbTable dbt) {
383        ResultSet rs = null;
384        try {
385            rs = md.getColumns(null, dbt.schemaName, dbt.name, null);
386            while (rs.next()) {
387                DbColumn col = new DbColumn();
388
389                col.dataType = rs.getInt("DATA_TYPE");
390                col.name = rs.getString("COLUMN_NAME");
391                col.typeName = rs.getString("TYPE_NAME");
392                col.columnSize = rs.getInt("COLUMN_SIZE");
393                col.decimalDigits = rs.getInt("DECIMAL_DIGITS");
394
395                dbt.addColsDataType(col);
396            }
397        } catch (Exception e) {
398            LOGGER.error("setColumns", e);
399        } finally {
400            try {
401                rs.close();
402            } catch (Exception e) {
403                // ignore
404            }
405        }
406    }
407
408    ////////////////////////////////////////////////////////////////////////////
409    // The following functions provide an interface to JdbcMetaData class to
410    // retrieve the meta data details
411
412    public List<String> getAllSchemas() {
413        return db.getAllSchemas();
414    }
415
416    /**
417     * Returns all tables in a given schema.
418     */
419    public List<String> getAllTables(String schemaName) {
420        return db.getAllTables(schemaName);
421    }
422
423    /**
424     * Returns all tables in given schema minus the given table name.
425     */
426    public List<String> getAllTables(String schemaName, String minusTable) {
427        if (minusTable == null) {
428            return getAllTables(schemaName);
429        } else {
430            List<String> allTablesMinusOne = new ArrayList<String>();
431            for (String s : getAllTables(schemaName)) {
432                if (s.endsWith(minusTable)) {
433                    // startsWith and endsWith cannot be compared with
434                    // null argument, throws exception
435                    if ((schemaName == null) || s.startsWith(schemaName)) {
436                        continue;
437                    }
438                }
439                allTablesMinusOne.add(s);
440            }
441            return allTablesMinusOne;
442        }
443    }
444
445    /* get all possible cases of fact tables in a schema */
446    public List<String> getFactTables(String schemaName) {
447        return db.getFactTables(schemaName);
448    }
449
450    /**
451     * Gets all possible cases of dimension tables which are linked to given
452     * fact table by foreign keys.
453     */
454    public List<String> getDimensionTables(
455        String schemaName,
456        String factTable)
457    {
458        List<String> dimeTables = new ArrayList<String>();
459        if (factTable == null) {
460            return dimeTables;
461        } else {
462            return db.getDimensionTables(schemaName, factTable);
463        }
464    }
465
466    public boolean isTableExists(String schemaName, String tableName) {
467        if (tableName == null) {
468            return true;
469        } else {
470            return db.tableExists(schemaName, tableName);
471        }
472    }
473
474    public boolean isColExists(
475        String schemaName, String tableName, String colName)
476    {
477        if (tableName == null || colName == null) {
478            return true;
479        } else {
480            if (!db.hasColumns(schemaName, tableName)) {
481                setColumns(schemaName, tableName);
482            }
483
484            return db.colExists(schemaName, tableName, colName);
485        }
486    }
487
488    /* get all foreign keys in given fact table */
489    public List<String> getFactTableFKs(String schemaName, String factTable) {
490        List<String> fks = new ArrayList<String>();
491        if (factTable == null) {
492            return fks;
493        } else {
494            return db.getFactTableFKs(schemaName, factTable);
495        }
496    }
497
498    public String getTablePK(String schemaName, String tableName) {
499        if (tableName == null) {
500            return null;
501        } else {
502            return db.getTablePK(schemaName, tableName);
503        }
504    }
505
506    /**
507     * Gets all columns of given table in schema.
508     * column string is formatted.
509     */
510    public List<String> getAllColumns(String schemaName, String tableName) {
511        List<String> allcols = new ArrayList<String>();
512
513        if (tableName == null) {
514            List<String> allTables = getAllTables(schemaName);
515
516            for (int i = 0; i < allTables.size(); i++) {
517                String tab = allTables.get(i);
518                List<String> cols;
519                if (tab.indexOf(LEVEL_SEPARATOR) == -1) {
520                    cols = getAllColumns(schemaName, tab);
521                } else {
522                    String[] names = tab.split(LEVEL_SEPARATOR);
523                    cols = getAllColumns(names[0], names[1]);
524                }
525                for (int j = 0; j < cols.size(); j++) {
526                    String col = cols.get(j);
527                    allcols.add(tab + LEVEL_SEPARATOR + col);
528                }
529            }
530            return allcols;
531        } else {
532            if (!db.hasColumns(schemaName, tableName)) {
533                setColumns(schemaName, tableName);
534            }
535            return db.getAllColumns(schemaName, tableName);
536        }
537    }
538
539    /**
540     * Returns all columns of given table in schema.
541     * Column string is formatted.
542     */
543    public List<DbColumn> getAllDbColumns(String schemaName, String tableName) {
544        List<DbColumn> allcols = new ArrayList<DbColumn>();
545
546        if (tableName == null) {
547            List<String> allTables = getAllTables(schemaName);
548
549            for (int i = 0; i < allTables.size(); i++) {
550                String tab = allTables.get(i);
551                List<DbColumn> cols;
552                if (tab.indexOf(LEVEL_SEPARATOR) == -1) {
553                    cols = getAllDbColumns(schemaName, tab);
554                } else {
555                    String[] names = tab.split(LEVEL_SEPARATOR);
556                    cols = getAllDbColumns(names[0], names[1]);
557                }
558                allcols.addAll(cols);
559            }
560            return allcols;
561        } else {
562            if (!db.hasColumns(schemaName, tableName)) {
563                setColumns(schemaName, tableName);
564            }
565            return db.getAllDbColumns(schemaName, tableName);
566        }
567    }
568
569    // get column data type of given table and its col
570    public int getColumnDataType(
571        String schemaName, String tableName, String colName)
572    {
573        if (tableName == null || colName == null) {
574            return -1;
575        } else {
576            if (!db.hasColumns(schemaName, tableName)) {
577                setColumns(schemaName, tableName);
578            }
579            return db.getColumnDataType(schemaName, tableName, colName);
580        }
581    }
582
583    /**
584     * Gets column definition of given table and its col.
585     *
586     * @param schemaName Schema name
587     * @param tableName Table name
588     * @param colName Column name
589     * @return Column definition
590     */
591    public DbColumn getColumnDefinition(
592        String schemaName, String tableName, String colName)
593    {
594        if (tableName == null || colName == null) {
595            return null;
596        } else {
597            if (!db.hasColumns(schemaName, tableName)) {
598                setColumns(schemaName, tableName);
599            }
600            return db.getColumnDefinition(schemaName, tableName, colName);
601        }
602    }
603
604    public String getDbCatalogName() {
605        return db.catalogName;
606    }
607
608    public String getDatabaseProductName() {
609        return db.productName;
610    }
611
612    public String getJdbcConnectionUrl() {
613        return jdbcConnectionUrl;
614    }
615
616    public String getErrMsg() {
617        return errMsg;
618    }
619
620    public static void main(String[] args) {
621        if (args.length < 2) {
622            throw new RuntimeException(
623                "need at least 2 args: driver class and jdbcUrl");
624        }
625
626        String driverClass = args[0];
627        String jdbcUrl = args[1];
628
629        String username = null;
630        String password = null;
631
632        if (args.length > 2) {
633            if (args.length != 4) {
634                throw new RuntimeException(
635                    "need 4 args: including user name and password");
636            }
637            username = args[2];
638            password = args[3];
639        }
640
641        JdbcMetaData sb = new JdbcMetaData(
642            null, driverClass, jdbcUrl, username, password, "", false);
643
644        List<String> foundSchemas = sb.getAllSchemas();
645        System.out.println("allSchemas = " + foundSchemas);
646
647        for (String schemaName : foundSchemas) {
648            List<String> foundTables = sb.getAllTables(schemaName);
649
650            if (foundTables != null && foundTables.size() > 0) {
651                System.out.println("schema = " + schemaName);
652                for (String tableName : foundTables) {
653                    System.out.println("\t" + tableName);
654
655                    List<String> foundColumns = sb.getAllColumns(
656                        schemaName, tableName);
657
658                    for (String columnName : foundColumns) {
659                        System.out.println("\t\t" + columnName);
660                    }
661                }
662            }
663        }
664    }
665
666    /**
667     * Database metadata.
668     */
669    class Database {
670        String catalogName = ""; // database name.
671        String productName = "Unknown";
672        String productVersion = "";
673
674        // list of all schemas in database
675        Map<String, DbSchema> schemas = new TreeMap<String, DbSchema>();
676            //ordered collection, allows duplicates and null
677        Map<String, TableTracker> tables = new TreeMap<String, TableTracker>();
678            // list of all tables in all schemas in database
679
680        List<String> allSchemas;
681
682        private void addDbSchema(DbSchema dbs) {
683            schemas.put(
684                dbs.name != null
685                    ? dbs.name
686                    : "", dbs);
687        }
688
689        class TableTracker {
690            List<DbTable> namedTable = new ArrayList<DbTable>();
691
692            public void add(DbTable table) {
693                namedTable.add(table);
694            }
695
696            public int count() {
697                return namedTable.size();
698            }
699        }
700
701        private void addDbTable(DbTable dbs) {
702            TableTracker tracker = tables.get(dbs.name);
703
704            if (tracker == null) {
705                tracker = new TableTracker();
706                tables.put(dbs.name, tracker);
707            }
708            tracker.add(dbs);
709        }
710
711        private boolean schemaNameEquals(String a, String b) {
712            return (a != null && a.equals(b));
713        }
714
715        private DbSchema getSchema(String schemaName) {
716            return schemas.get(
717                schemaName != null
718                    ? schemaName
719                    : "");
720        }
721
722        private List<String> getAllSchemas() {
723            if (allSchemas == null) {
724                allSchemas = new ArrayList<String>();
725
726                allSchemas.addAll(schemas.keySet());
727            }
728            return allSchemas;
729        }
730
731        private boolean tableExists(String sname, String tableName) {
732            return getTable(sname, tableName) != null;
733        }
734
735        private DbTable getTable(String sname, String tableName) {
736            if (sname == null || sname.equals("")) {
737                TableTracker t = tables.get(tableName);
738                if (t != null) {
739                    return t.namedTable.get(0);
740                } else {
741                    return null;
742                }
743            } else {
744                DbSchema s = schemas.get(sname);
745
746                if (s == null) {
747                    return null;
748                }
749
750                return s.getTable(tableName);
751            }
752        }
753
754        private boolean hasColumns(String schemaName, String tableName) {
755            DbTable table = getTable(schemaName, tableName);
756            if (table != null) {
757                return table.hasColumns();
758            }
759            return false;
760        }
761
762        private boolean colExists(
763            String sname, String tableName, String colName)
764        {
765            DbTable t = getTable(sname, tableName);
766
767            if (t == null) {
768                return false;
769            }
770
771            return t.getColumn(colName) != null;
772        }
773
774        private List<String> getAllTables(String sname) {
775            return getAllTables(sname, false);
776        }
777
778        private List<String> getFactTables(String sname) {
779            return getAllTables(sname, true);
780        }
781
782        private List<String> getAllTables(String sname, boolean factOnly) {
783            List<String> v = new ArrayList<String>();
784
785            if (sname == null || sname.equals("")) {
786                // return a list of "schemaname -> table name" string objects
787                for (TableTracker tt : tables.values()) {
788                    for (DbTable t : tt.namedTable) {
789                        if (!factOnly || (factOnly && t instanceof FactTable)) {
790                            if (t.schemaName == null) {
791                                v.add(t.name);
792                            } else {
793                                v.add(t.schemaName + LEVEL_SEPARATOR + t.name);
794                            }
795                        }
796                    }
797                }
798            } else {
799                // return a list of "tablename" string objects
800                DbSchema s = getSchema(sname);
801
802                if (s != null) {
803                    for (DbTable t : s.tables.values()) {
804                        if (!factOnly || (factOnly && t instanceof FactTable)) {
805                            v.add(t.name);
806                        }
807                    }
808                }
809            }
810            return v;
811        }
812
813        /* get all foreign keys in given fact table */
814        private List<String> getFactTableFKs(String sname, String factTable) {
815            List<String> f = new ArrayList<String>();
816
817            if (sname == null || sname.equals("")) {
818                TableTracker tracker = tables.get(factTable);
819
820                if (tracker == null) {
821                    return f;
822                }
823
824                // return a list of "schemaname -> table name -> fk col" string
825                // objects if schema is not given
826                boolean duplicate = tracker.count() > 1;
827
828                for (DbTable t : tracker.namedTable) {
829                    if (t instanceof FactTable) {
830                        if (duplicate) {
831                            for (String fk : ((FactTable) t).fks.keySet()) {
832                                if (t.schemaName == null) {
833                                    f.add(t.name + LEVEL_SEPARATOR + fk);
834                                } else {
835                                    f.add(
836                                        t.schemaName
837                                        + LEVEL_SEPARATOR
838                                        + t.name
839                                        + LEVEL_SEPARATOR
840                                        + fk);
841                                }
842                            }
843                        } else {
844                            f.addAll(((FactTable) t).fks.keySet());
845                        }
846                    }
847                }
848            } else {
849                DbSchema s = getSchema(sname);
850
851                if (s == null) {
852                    return f;
853                }
854
855                DbTable t = s.getTable(factTable);
856
857                if (t == null) {
858                    return f;
859                }
860
861                // return a list of "fk col name" string objects if schema is
862                // given
863                if (t instanceof FactTable && t.name.equals(factTable)) {
864                    f.addAll(((FactTable) t).fks.keySet());
865                }
866            }
867            return f;
868        }
869
870        private List<String> getDimensionTables(
871            String sname, String factTable)
872        {
873            List<String> f = new ArrayList<String>();
874
875            if (sname == null || sname.equals("")) {
876                TableTracker tracker = tables.get(factTable);
877
878                if (tracker == null) {
879                    return f;
880                }
881
882                // return a list of "schemaname -> table name -> fk col" string
883                // objects if schema is not given
884                boolean duplicate = tracker.count() > 1;
885
886                for (DbTable t : tracker.namedTable) {
887                    if (t instanceof FactTable) {
888                        if (duplicate) {
889                            for (String fkt : ((FactTable) t).fks.values()) {
890                                if (t.schemaName == null) {
891                                    f.add(t.name + LEVEL_SEPARATOR + fkt);
892                                } else {
893                                    f.add(
894                                        t.schemaName
895                                        + LEVEL_SEPARATOR
896                                        + t.name
897                                        + LEVEL_SEPARATOR
898                                        + fkt);
899                                }
900                            }
901                        } else {
902                            f.addAll(((FactTable) t).fks.keySet());
903                        }
904                    }
905                }
906            } else {
907                DbSchema s = getSchema(sname);
908
909                if (s == null) {
910                    return f;
911                }
912
913                DbTable t = s.getTable(factTable);
914
915                if (t == null) {
916                    return f;
917                }
918
919                // return a list of "fk col name" string objects if schema is
920                // given
921                if (t instanceof FactTable && t.name.equals(factTable)) {
922                    f.addAll(((FactTable) t).fks.values());
923                }
924            }
925            return f;
926        }
927
928        private String getTablePK(String sname, String tableName) {
929            if (sname == null || sname.equals("")) {
930                TableTracker tracker = tables.get(tableName);
931
932                if (tracker == null) {
933                    return null;
934                }
935
936                // return a list of "schemaname -> table name ->
937                // dimension table name" string objects if schema is not given
938                return tracker.namedTable.get(0).pk;
939            } else {
940                DbTable t = getTable(sname, tableName);
941
942                if (t == null) {
943                    return null;
944                }
945
946                return t.pk;
947            }
948        }
949
950        private List<String> getAllColumns(String sname, String tableName) {
951            List<String> f = new ArrayList<String>();
952
953            if (sname == null || sname.equals("")) {
954                TableTracker tracker = tables.get(tableName);
955
956                if (tracker == null) {
957                    return f;
958                }
959
960                // return a list of "schemaname -> table name -> cols"
961                // string objects if schema is not given
962                boolean duplicate = tracker.count() > 1;
963
964                for (DbTable t : tracker.namedTable) {
965                    for (Map.Entry<String, DbColumn> c : t.colsDataType
966                        .entrySet())
967                    {
968                        StringBuffer sb = new StringBuffer();
969
970                        if (t.schemaName != null && !duplicate) {
971                            sb.append(t.schemaName).append(LEVEL_SEPARATOR);
972                        }
973                        sb.append(t.name)
974                            .append(LEVEL_SEPARATOR)
975                            .append(c.getKey())
976                            .append(" - ")
977                            .append(c.getValue().displayType());
978
979                        f.add(sb.toString());
980                    }
981                }
982            } else {
983                DbTable t = getTable(sname, tableName);
984
985                if (t == null) {
986                    return f;
987                }
988                // return a list of "col name" string objects if schema is given
989                f.addAll(t.colsDataType.keySet());
990            }
991            return f;
992        }
993
994        private List<DbColumn> getAllDbColumns(String sname, String tableName) {
995            List<DbColumn> f = new ArrayList<DbColumn>();
996
997            if (sname == null || sname.equals("")) {
998                TableTracker tracker = tables.get(tableName);
999
1000                if (tracker == null) {
1001                    return f;
1002                }
1003
1004                for (DbTable t : tracker.namedTable) {
1005                    for (Map.Entry<String, DbColumn> c : t.colsDataType
1006                        .entrySet())
1007                    {
1008                        f.add(c.getValue());
1009                    }
1010                }
1011            } else {
1012                DbTable t = getTable(sname, tableName);
1013
1014                if (t == null) {
1015                    return f;
1016                }
1017
1018                for (Map.Entry<String, DbColumn> c : t.colsDataType
1019                    .entrySet())
1020                {
1021                    f.add(c.getValue());
1022                }
1023            }
1024            return f;
1025        }
1026
1027        private int getColumnDataType(
1028            String sname, String tableName, String colName)
1029        {
1030            DbColumn result = getColumnDefinition(sname, tableName, colName);
1031
1032            if (result == null) {
1033                return -1;
1034            }
1035
1036            return result.dataType;
1037        }
1038
1039        private DbColumn getColumnDefinition(
1040            String sname, String tableName, String colName)
1041        {
1042            DbTable t = getTable(sname, tableName);
1043
1044            if (t == null) {
1045                return null;
1046            }
1047            return t.colsDataType.get(colName);
1048        }
1049    }
1050
1051    class DbSchema {
1052        String name;
1053        /**
1054         * ordered collection, allows duplicates and null
1055         */
1056        final Map<String, DbTable> tables = new TreeMap<String, DbTable>();
1057
1058        private DbTable getTable(String tableName) {
1059            return tables.get(tableName);
1060        }
1061
1062        private void addDbTable(DbTable dbt) {
1063            tables.put(dbt.name, dbt);
1064        }
1065    }
1066
1067    public class DbColumn {
1068        public String name;
1069        public int dataType;
1070        public String typeName;
1071        public int columnSize;
1072        public int decimalDigits;
1073
1074        public String displayType() {
1075            StringBuffer sb = new StringBuffer();
1076            switch (dataType) {
1077            case Types.ARRAY:
1078                sb.append("ARRAY(" + columnSize + ")");
1079                break;
1080            case Types.BIGINT:
1081                sb.append("BIGINT");
1082                break;
1083            case Types.BINARY:
1084                sb.append("BINARY(" + columnSize + ")");
1085                break;
1086            case Types.BLOB:
1087                sb.append("BLOB(" + columnSize + ")");
1088                break;
1089            case Types.BIT:
1090                sb.append("BIT");
1091                break;
1092            case Types.BOOLEAN:
1093                sb.append("BOOLEAN");
1094                break;
1095            case Types.CHAR:
1096                sb.append("CHAR");
1097                break;
1098            case Types.CLOB:
1099                sb.append("CLOB(" + columnSize + ")");
1100                break;
1101            case Types.DATE:
1102                sb.append("DATE");
1103                break;
1104            case Types.DECIMAL:
1105                sb.append("DECIMAL(" + columnSize + ", " + decimalDigits + ")");
1106                break;
1107            case Types.DISTINCT:
1108                sb.append("DISTINCT");
1109                break;
1110            case Types.DOUBLE:
1111                sb.append("DOUBLE(" + columnSize + ", " + decimalDigits + ")");
1112                break;
1113            case Types.FLOAT:
1114                sb.append("FLOAT(" + columnSize + ", " + decimalDigits + ")");
1115                break;
1116            case Types.INTEGER:
1117                sb.append("INTEGER(" + columnSize + ")");
1118                break;
1119            case Types.JAVA_OBJECT:
1120                sb.append("JAVA_OBJECT(" + columnSize + ")");
1121                break;
1122            /*
1123             * No Java 1.6 SQL types for now
1124            case Types.LONGNVARCHAR:
1125                sb.append("LONGNVARCHAR(" + columnSize + ")");
1126                break;
1127            case Types.LONGVARBINARY:
1128                sb.append("LONGVARBINARY(" + columnSize + ")");
1129                break;
1130            case Types.LONGVARCHAR:
1131                sb.append("LONGVARCHAR(" + columnSize + ")");
1132                break;
1133            case Types.NCHAR:
1134                sb.append("NCHAR(" + columnSize + ")");
1135                break;
1136            case Types.NCLOB:
1137                sb.append("NCLOB(" + columnSize + ")");
1138                break;
1139             */
1140            case Types.NULL:
1141                sb.append("NULL");
1142                break;
1143            case Types.NUMERIC:
1144                sb.append("NUMERIC(" + columnSize + ", " + decimalDigits + ")");
1145                break;
1146            /*
1147             * No Java 1.6 SQL types for now
1148            case Types.NVARCHAR:
1149                sb.append("NCLOB(" + columnSize + ")");
1150                break;
1151             */
1152            case Types.OTHER:
1153                sb.append("OTHER");
1154                break;
1155            case Types.REAL:
1156                sb.append("REAL(" + columnSize + ", " + decimalDigits + ")");
1157                break;
1158            case Types.REF:
1159                sb.append("REF");
1160                break;
1161            /*
1162             * No Java 1.6 SQL types for now
1163            case Types.ROWID:
1164                sb.append("ROWID");
1165                break;
1166             */
1167            case Types.SMALLINT:
1168                sb.append("SMALLINT(" + columnSize + ")");
1169                break;
1170            /*
1171             * No Java 1.6 SQL types for now
1172           case Types.SQLXML:
1173                sb.append("SQLXML(" + columnSize + ")");
1174                break;
1175             */
1176            case Types.STRUCT:
1177                sb.append("STRUCT");
1178                break;
1179            case Types.TIME:
1180                sb.append("TIME");
1181                break;
1182            case Types.TIMESTAMP:
1183                sb.append("TIMESTAMP");
1184                break;
1185            case Types.TINYINT:
1186                sb.append("TINYINT(" + columnSize + ")");
1187                break;
1188            case Types.VARBINARY:
1189                sb.append("VARBINARY(" + columnSize + ")");
1190                break;
1191            case Types.VARCHAR:
1192                sb.append("VARCHAR(" + columnSize + ")");
1193                break;
1194            }
1195            return sb.toString();
1196        }
1197    }
1198
1199    class DbTable {
1200        String schemaName;
1201        String name;
1202        String pk;
1203        /**
1204         * sorted map key=column, value=data type of column
1205         */
1206        final Map<String, DbColumn> colsDataType =
1207            new TreeMap<String, DbColumn>();
1208
1209        private void addColsDataType(DbColumn columnDefinition) {
1210            colsDataType.put(columnDefinition.name, columnDefinition);
1211        }
1212
1213        private DbColumn getColumn(String cname) {
1214            return colsDataType.get(cname);
1215        }
1216
1217        private boolean hasColumns() {
1218            return colsDataType.size() > 0;
1219        }
1220    }
1221
1222    class FactTable extends DbTable {
1223        /**
1224         * Sorted map key = foreign key col, value=primary key table associated
1225         * with this fk.
1226         */
1227        final Map<String, String> fks = new TreeMap<String, String>();
1228
1229        private void addFks(String fk, String pkt) {
1230            fks.put(fk, pkt);
1231        }
1232    }
1233}
1234
1235// End JdbcMetaData.java