jtdsdatabasemetadata.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

// jTDS JDBC Driver for Microsoft SQL Server and Sybase
// Copyright (C) 2004 The jTDS Project
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
package net.sourceforge.jtds.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

/**
 * jTDS implementation of the java.sql.DatabaseMetaData interface.
 * <p>
 * Implementation note:
 * <p>
 * This is basically the code from the original jTDS driver.
 * Main changes relate to the need to support the new ResultSet
 * implementation.
 * <p>
 * TODO: Many of the system limits need to be revised to more accurately
 * reflect the target database constraints. In many cases limits are soft
 * and determined by bytes per column for example. Probably more of these
 * functions should be altered to return 0 but for now the original jTDS
 * values are returned.
 *
 * @author   Craig Spannring
 * @author   The FreeTDS project
 * @author   Alin Sinpalean
 *  created  17 March 2001
 * @version $Id: JtdsDatabaseMetaData.java,v 1.30 2005/06/01 17:24:14 alin_sinpalean Exp $
 */
public class JtdsDatabaseMetaData implements java.sql.DatabaseMetaData {
    static final int sqlStateXOpen = 1;

    // Internal data needed by this implemention.
    private final int tdsVersion;
    private final int serverType;
    private final ConnectionJDBC2 connection;

    /**
     * Length of a sysname object (table name, catalog name etc.) -- 128 for
     * TDS 7.0, 30 for earlier versions.
     */
    int sysnameLength = 30;

    /**
     * <code>Boolean.TRUE</code> if identifiers are case sensitive (the server
     * was installed that way). Initially <code>null</code>, set the first time
     * any of the methods that check this are called.
     */
    Boolean caseSensitive;

    public JtdsDatabaseMetaData(ConnectionJDBC2 connection) {
        this.connection = connection;
        tdsVersion = connection.getTdsVersion();
        serverType = connection.getServerType();
        if (tdsVersion >= Driver.TDS70) {
            sysnameLength = 128;
        }
    }

    //----------------------------------------------------------------------
    // First, a variety of minor information about the target database.

    /**
     * Can all the procedures returned by getProcedures be called by the
     * current user?
     *
     * @return <code>true</code> if so
     * @throws SQLException if a database-access error occurs.
     */
    public boolean allProceduresAreCallable() throws SQLException {
        // Sybase - if accessible_sproc = Y in server info (normal case) return true
        return true; // per "Programming ODBC for SQLServer" Appendix A
    }

    /**
     * Can all the tables returned by getTable be SELECTed by the
     * current user?
     *
     * @return <code>true</code> if so
     * @throws SQLException if a database-access error occurs.
     */
    public boolean allTablesAreSelectable() throws SQLException {
        // Sybase sp_tables may return tables that you are not able to access.
        return connection.getServerType() == Driver.SQLSERVER;
    }

    /**
     * Does a data definition statement within a transaction force the
     * transaction to commit?
     *
     * @return <code>true</code> if so
     * @throws SQLException if a database-access error occurs.
     */
    public boolean dataDefinitionCausesTransactionCommit() throws SQLException {
        return false;
    }

    /**
     * Is a data definition statement within a transaction ignored?
     *
     * @return <code>true</code> if so
     * @throws SQLException if a database-access error occurs.
     */
    public boolean dataDefinitionIgnoredInTransactions() throws SQLException {
        return false;
    }

    /**
     * Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY blobs?
     *
     * @return <code>true</code> if so
     * @throws SQLException if a database-access error occurs.
     */
    public boolean doesMaxRowSizeIncludeBlobs() throws SQLException {
        return false;
    }

    /**
     * Get a description of a table's optimal set of columns that
     * uniquely identifies a row. They are ordered by SCOPE.
     *
     * <P>Each column description has the following columns:
     *  <OL>
     *    <LI> <B>SCOPE</B> short =>actual scope of result
     *    <UL>
     *      <LI> bestRowTemporary - very temporary, while using row
     *      <LI> bestRowTransaction - valid for remainder of current transaction
     *
     *      <LI> bestRowSession - valid for remainder of current session
     *    </UL>
     *
     *    <LI> <B>COLUMN_NAME</B> String =>column name
     *    <LI> <B>DATA_TYPE</B> short =>SQL data type from java.sql.Types
     *    <LI> <B>TYPE_NAME</B> String =>Data source dependent type name
     *    <LI> <B>COLUMN_SIZE</B> int =>precision
     *    <LI> <B>BUFFER_LENGTH</B> int =>not used
     *    <LI> <B>DECIMAL_DIGITS</B> short =>scale
     *    <LI> <B>PSEUDO_COLUMN</B> short =>is this a pseudo column like an
     *    Oracle ROWID
     *    <UL>
     *      <LI> bestRowUnknown - may or may not be pseudo column
     *      <LI> bestRowNotPseudo - is NOT a pseudo column
     *      <LI> bestRowPseudo - is a pseudo column
     *    </UL>
     *
     *  </OL>
     *
     *
     * @param catalog a catalog name; "" retrieves those without a catalog;
     *        <code>null</code> means drop catalog name from the selection criteria
     * @param schema a schema name; "" retrieves those without a schema
     * @param table a table name
     * @param scope the scope of interest; use same values as SCOPE
     * @param nullable include columns that are nullable?
     * @return ResultSet - each row is a column description
     * @throws SQLException if a database-access error occurs.
     */
    public java.sql.ResultSet getBestRowIdentifier(String catalog,
                                                   String schema,
                                                   String table,
                                                   int scope,
                                                   boolean nullable)
    throws SQLException {
        String colNames[] = {"SCOPE",           "COLUMN_NAME",
                             "DATA_TYPE",       "TYPE_NAME",
                             "COLUMN_SIZE",     "BUFFER_LENGTH",
                             "DECIMAL_DIGITS",  "PSEUDO_COLUMN"};
        int    colTypes[] = {Types.SMALLINT,    Types.VARCHAR,
                             Types.INTEGER,     Types.VARCHAR,
                             Types.INTEGER,     Types.INTEGER,
                             Types.SMALLINT,    Types.SMALLINT};

        String query = "sp_special_columns ?, ?, ?, ?, ?, ?, ?";

        CallableStatement s = connection.prepareCall(syscall(catalog, query));

        s.setString(1, table);
        s.setString(2, schema);
        s.setString(3, catalog);
        s.setString(4, "R");
        s.setString(5, "T");
        s.setString(6, "U");
        s.setInt(7, 3); // ODBC version 3

        JtdsResultSet rs = (JtdsResultSet)s.executeQuery();
        CachedResultSet rsTmp = new CachedResultSet((JtdsStatement)s, colNames, colTypes);
        rsTmp.moveToInsertRow();
        int colCnt = rs.getMetaData().getColumnCount();
        while (rs.next()) {
            for (int i = 1; i <= colCnt; i++) {
                if (i == 3) {
                    int type = TypeInfo.normalizeDataType(rs.getInt(i));
                    rsTmp.updateInt(i, type);
                } else {
                    rsTmp.updateObject(i, rs.getObject(i));
                }
            }
            rsTmp.insertRow();
        }
        rs.close();
        s.close();
        rsTmp.moveToCurrentRow();
        rsTmp.setConcurrency(ResultSet.CONCUR_READ_ONLY);
        return rsTmp;
    }

    /**
     * Get the catalog names available in this database. The results are
     * ordered by catalog name. <P>
     *
     * The catalog column is:
     * <OL>
     *   <LI> <B>TABLE_CAT</B> String =>catalog name
     * </OL>
     *
     *
     * @return ResultSet - each row has a single String column
     *      that is a catalog name
     * @throws SQLException if a database-access error occurs.
     */
    public java.sql.ResultSet getCatalogs() throws SQLException {
        String query = "exec sp_tables '', '', '%', NULL";
        Statement s = connection.createStatement();
        JtdsResultSet rs = (JtdsResultSet)s.executeQuery(query);

        rs.setColumnCount(1);
        rs.setColLabel(1, "TABLE_CAT");

        upperCaseColumnNames(rs);

        return rs;
    }

    /**
     * What's the separator between catalog and table name?
     *
     * @return the separator string
     * @throws SQLException if a database-access error occurs.
     */
    public String getCatalogSeparator() throws SQLException {
        return ".";
    }

    /**
     * What's the database vendor's preferred term for "catalog"?
     *
     * @return the vendor term
     * @throws SQLException if a database-access error occurs.
     */
    public String getCatalogTerm() throws SQLException {
        return "database";
    }

    /**
     * Get a description of the access rights for a table's columns. <P>
     *
     * Only privileges matching the column name criteria are returned. They are
     * ordered by COLUMN_NAME and PRIVILEGE. <P>
     *
     * Each privilige description has the following columns:
     * <OL>
     *   <LI> <B>TABLE_CAT</B> String =>table catalog (may be null)
     *   <LI> <B>TABLE_SCHEM</B> String =>table schema (may be null)
     *   <LI> <B>TABLE_NAME</B> String =>table name
     *   <LI> <B>COLUMN_NAME</B> String =>column name
     *   <LI> <B>GRANTOR</B> =>grantor of access (may be null)
     *   <LI> <B>GRANTEE</B> String =>grantee of access
     *   <LI> <B>PRIVILEGE</B> String =>name of access (SELECT, INSERT, UPDATE,
     *   REFRENCES, ...)
     *   <LI> <B>IS_GRANTABLE</B> String =>"YES" if grantee is permitted to
     *   grant to others; "NO" if not; null if unknown
     * </OL>
     *
     * @param catalog a catalog name; "" retrieves those without a catalog;
     *        <code>null</code> means drop catalog name from the selection criteria
     * @param schema a schema name; "" retrieves those without a schema
     *      schema
     * @param table a table name
     * @param columnNamePattern a column name pattern
     * @return ResultSet - each row is a column privilege description
     * @throws SQLException if a database-access error occurs.
     *
     * @see #getSearchStringEscape
     */
    public java.sql.ResultSet getColumnPrivileges(String catalog,
                                                  String schema,
                                                  String table,
                                                  String columnNamePattern)
    throws SQLException {
        String query = "sp_column_privileges ?, ?, ?, ?";

        CallableStatement s = connection.prepareCall(syscall(catalog, query));

        s.setString(1, table);
        s.setString(2, schema);
        s.setString(3, catalog);
        s.setString(4, processEscapes(columnNamePattern));

        JtdsResultSet rs = (JtdsResultSet)s.executeQuery();

        rs.setColLabel(1, "TABLE_CAT");
        rs.setColLabel(2, "TABLE_SCHEM");