net.ontopia.topicmaps.cmdlineutils.rdbms.RDBMSIndexTool.java Source code

Java tutorial

Introduction

Here is the source code for net.ontopia.topicmaps.cmdlineutils.rdbms.RDBMSIndexTool.java

Source

/*
 * #!
 * Ontopia Engine
 * #-
 * Copyright (C) 2001 - 2013 The Ontopia Project
 * #-
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * !#
 */

package net.ontopia.topicmaps.cmdlineutils.rdbms;

import java.io.File;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import net.ontopia.persistence.proxy.ConnectionFactoryIF;
import net.ontopia.persistence.proxy.DefaultConnectionFactory;
import net.ontopia.persistence.rdbms.DatabaseProjectReader;
import net.ontopia.persistence.rdbms.Index;
import net.ontopia.persistence.rdbms.Project;
import net.ontopia.persistence.rdbms.Table;
import net.ontopia.utils.CmdlineOptions;
import net.ontopia.utils.CmdlineUtils;
import net.ontopia.utils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;

/**
 * EXPERIMENTAL: A tool that inspects a database to see if the proper
 * indexes has been created.<p>
 */
public class RDBMSIndexTool {

    public static void main(String[] argv) throws Exception {

        // Initialize logging
        CmdlineUtils.initializeLogging();

        // Register logging options
        CmdlineOptions options = new CmdlineOptions("RDBMSIndexTool", argv);
        CmdlineUtils.registerLoggingOptions(options);

        // Parse command line options
        try {
            options.parse();
        } catch (CmdlineOptions.OptionsException e) {
            System.err.println("Error: " + e.getMessage());
            System.exit(1);
        }

        // Get command line arguments
        String[] args = options.getArguments();

        if (args.length != 1) {
            usage();
            System.exit(3);
        }

        // load database schema project
        ClassLoader cloader = RDBMSIndexTool.class.getClassLoader();
        InputStream istream = cloader.getResourceAsStream("net/ontopia/topicmaps/impl/rdbms/config/schema.xml");
        Project dbp = DatabaseProjectReader.loadProject(istream);

        // open database connection
        String propfile = args[0];
        ConnectionFactoryIF cf = new DefaultConnectionFactory(PropertyUtils.loadProperties(new File(propfile)),
                true);

        Connection conn = cf.requestConnection();
        try {
            DatabaseMetaData dbm = conn.getMetaData();
            boolean downcase = dbm.storesLowerCaseIdentifiers();

            Map extra_indexes = new TreeMap();
            Map missing_indexes = new TreeMap();

            Iterator tables = dbp.getTables().iterator();
            while (tables.hasNext()) {
                Table table = (Table) tables.next();
                String table_name = (downcase ? table.getName().toLowerCase() : table.getName());
                //! System.out.println("T :"  + table_name);

                // get primary keys from database
                Map pkeys = getPrimaryKeys(table_name, dbm);

                // get indexes from database
                Map indexes = getIndexes(table_name, dbm);

                Map dindexes = new HashMap();
                if (table.getPrimaryKeys() != null) {
                    String pkey = table_name + '(' + StringUtils.join(table.getPrimaryKeys(), ',') + ')';
                    if (!pkeys.containsKey(pkey))
                        System.out.println("PKM: " + pkey);
                }

                Iterator iter = table.getIndexes().iterator();
                while (iter.hasNext()) {
                    Index index = (Index) iter.next();
                    String i = table_name + '(' + StringUtils.join(index.getColumns(), ',') + ')';
                    String index_name = (downcase ? index.getName().toLowerCase() : index.getName());
                    dindexes.put(i, index_name);
                }

                Set extra = new HashSet(indexes.keySet());
                extra.removeAll(dindexes.keySet());
                extra.removeAll(pkeys.keySet());
                if (!extra.isEmpty()) {
                    Iterator i = extra.iterator();
                    while (i.hasNext()) {
                        Object k = i.next();
                        extra_indexes.put(k, indexes.get(k));
                    }
                }

                Set missing = new HashSet(dindexes.keySet());
                missing.addAll(pkeys.keySet());
                missing.removeAll(indexes.keySet());
                if (!missing.isEmpty()) {
                    Iterator i = missing.iterator();
                    while (i.hasNext()) {
                        Object k = i.next();
                        missing_indexes.put(k, dindexes.get(k));
                    }
                }

            }
            if (!extra_indexes.isEmpty())
                System.out.println("/* --- Extra indexes ----------------------------------------- */");
            Iterator eiter = extra_indexes.keySet().iterator();
            while (eiter.hasNext()) {
                Object k = eiter.next();
                System.out.println("drop index " + extra_indexes.get(k) + "; /* " + k + " */");
            }

            if (!missing_indexes.isEmpty())
                System.out.println("/* --- Missing indexes---------------------------------------- */");
            Iterator miter = missing_indexes.keySet().iterator();
            while (miter.hasNext()) {
                Object k = miter.next();
                System.out.println("create index " + missing_indexes.get(k) + " on " + k + ";");
            }

        } finally {
            conn.rollback();
            conn.close();
        }

    }

    protected static void print(String prefix, Collection c) {
        Iterator iter = c.iterator();
        while (iter.hasNext()) {
            Object k = iter.next();
            System.out.println(prefix + k);
        }
    }

    protected static void print(String prefix, Map m) {
        Iterator iter = m.keySet().iterator();
        while (iter.hasNext()) {
            Object k = iter.next();
            System.out.println(prefix + k + " " + m.get(k));
        }
    }

    protected static Map getIndexes(String table_name, DatabaseMetaData dbm) throws SQLException {
        // returns { table_name(colname,...) : index_name }
        Map result = new HashMap(5);
        ResultSet rs = dbm.getIndexInfo(null, null, table_name, false, false);
        String prev_index_name = null;
        String columns = null;

        while (rs.next()) {
            String index_name = rs.getString(6);

            if (prev_index_name != null && !prev_index_name.equals(index_name)) {
                result.put(table_name + '(' + columns + ')', prev_index_name);
                columns = null;
            }
            // column_name might be quoted, so unquote it before proceeding
            String column_name = unquote(rs.getString(9), dbm.getIdentifierQuoteString());

            if (columns == null)
                columns = column_name;
            else
                columns = columns + "," + column_name;

            prev_index_name = index_name;
        }
        rs.close();

        if (prev_index_name != null)
            result.put(table_name + '(' + columns + ')', prev_index_name);

        return result;
    }

    protected static Map getPrimaryKeys(String table_name, DatabaseMetaData dbm) throws SQLException {
        // returns { table_name(colname,...) : index_name }
        Map result = new HashMap(5);
        ResultSet rs = dbm.getPrimaryKeys(null, null, table_name);
        String prev_index_name = null;
        String columns = null;

        while (rs.next()) {
            String index_name = rs.getString(6);
            if (prev_index_name != null && !prev_index_name.equals(index_name)) {
                result.put(table_name + '(' + columns + ')', prev_index_name);
                columns = null;
            }
            // column_name might be quoted, so unquote it before proceeding
            String column_name = unquote(rs.getString(4), dbm.getIdentifierQuoteString());
            if (columns == null)
                columns = column_name;
            else
                columns = columns + "," + column_name;

            prev_index_name = index_name;
        }
        rs.close();

        if (prev_index_name != null)
            result.put(table_name + '(' + columns + ')', prev_index_name);

        return result;
    }

    protected static String unquote(String column_name, String quote) {
        // column_name might be quoted, so unquote it before proceeding
        if (column_name != null && column_name.startsWith(quote) && column_name.endsWith(quote))
            return column_name.substring(quote.length(), column_name.length() - quote.length());
        else
            return column_name;
    }

    private static void usage() {
        System.out.println("java net.ontopia.topicmaps.cmdlineutils.rdbms.RDBMSIndexTool [options] <dbprops>");
        System.out.println("");
        System.out.println("  Analyzes database indexes.");
        System.out.println("");
        System.out.println("  Options:");
        CmdlineUtils.printLoggingOptionsUsage(System.out);
        System.out.println("");
        System.out.println("  <dbprops>:   the database configuration file");
        System.out.println("");
    }

}