gridool.db.helpers.GridDbUtils.java Source code

Java tutorial

Introduction

Here is the source code for gridool.db.helpers.GridDbUtils.java

Source

/*
 * @(#)$Id$
 *
 * Copyright 2006-2008 Makoto YUI
 *
 * 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.
 * 
 * Contributors:
 *     Makoto YUI - initial implementation
 */
package gridool.db.helpers;

import gridool.GridException;
import gridool.GridJobFuture;
import gridool.GridKernel;
import gridool.GridNode;
import gridool.db.catalog.DistributionCatalog;
import gridool.db.partitioning.phihash.DBPartitioningJobConf;
import gridool.db.partitioning.phihash.NodeWithPartitionNo;
import gridool.db.partitioning.phihash.monetdb.MonetDBCsvLoadOperation;
import gridool.db.partitioning.phihash.monetdb.MonetDBInvokeCsvLoadJob;
import gridool.dfs.GridXferClient;
import gridool.util.GridUtils;
import gridool.util.collections.LRUMap;
import gridool.util.io.FastByteArrayOutputStream;
import gridool.util.jdbc.JDBCUtils;
import gridool.util.primitive.MutableLong;
import gridool.util.string.StringUtils;
import gridool.util.struct.Pair;
import gridool.util.xfer.RunnableFileSender;

import java.net.InetAddress;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;

import javax.annotation.CheckForNull;
import javax.annotation.Nonnull;
import javax.annotation.Nullable;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 
 * <DIV lang="en"></DIV>
 * <DIV lang="ja"></DIV>
 * 
 * @author Makoto YUI (yuin405@gmail.com)
 */
public final class GridDbUtils {
    private static final Log LOG = LogFactory.getLog(GridDbUtils.class);

    private GridDbUtils() {
    }

    @Nonnull
    public static Connection getPrimaryDbConnection(final DBAccessor dba, boolean autoCommit) throws GridException {
        final Connection conn;
        try {
            conn = dba.getPrimaryDbConnection();
            if (conn.getAutoCommit() != autoCommit) {
                conn.setAutoCommit(autoCommit);
            }
        } catch (SQLException e) {
            LOG.error(e);
            throw new GridException("failed connecting to the primary database: " + dba.getPrimaryDbName());
        }
        return conn;
    }

    @Nonnull
    public static Pair<PrimaryKey, Collection<ForeignKey>> getPrimaryForeignKeys(@Nonnull final DBAccessor dba,
            @Nonnull final String templateTableName) throws GridException {
        final PrimaryKey pkey;
        final Collection<ForeignKey> fkeys;
        final Connection conn = GridDbUtils.getPrimaryDbConnection(dba, false);
        try {
            pkey = GridDbUtils.getPrimaryKey(conn, templateTableName, true);
            fkeys = GridDbUtils.getForeignKeys(conn, templateTableName, true);
        } catch (SQLException e) {
            LOG.error(e);
            throw new GridException(e);
        } finally {
            JDBCUtils.closeQuietly(conn);
        }
        if (pkey == null) {
            throw new IllegalStateException("Primary key is not defined on table: " + templateTableName);
        }
        return new Pair<PrimaryKey, Collection<ForeignKey>>(pkey, fkeys);
    }

    @Nullable
    public static PrimaryKey getPrimaryKey(@Nonnull final Connection conn, @CheckForNull final String pkTableName,
            final boolean reserveAdditionalInfo) throws SQLException {
        if (pkTableName == null) {
            throw new IllegalArgumentException();
        }
        DatabaseMetaData metadata = conn.getMetaData();
        String catalog = conn.getCatalog();
        final ResultSet rs = metadata.getPrimaryKeys(catalog, null, pkTableName);
        final PrimaryKey pkey;
        try {
            if (!rs.next()) {
                return null;
            }
            String pkName = rs.getString("PK_NAME");
            pkey = new PrimaryKey(pkName, pkTableName);
            do {
                pkey.addColumn(rs);
            } while (rs.next());
        } finally {
            rs.close();
        }

        if (reserveAdditionalInfo) {
            // set foreign key column positions
            pkey.setColumnPositions(metadata);
            // set exported keys
            final Collection<ForeignKey> exportedKeys = getExportedKeys(conn, pkTableName, false);
            if (!exportedKeys.isEmpty()) {
                final List<String> pkColumnsProbe = pkey.getColumnNames();
                final int numPkColumnsProbe = pkColumnsProbe.size();
                final List<ForeignKey> exportedKeyList = new ArrayList<ForeignKey>(4);
                outer: for (ForeignKey fk : exportedKeys) {
                    List<String> names = fk.getPkColumnNames();
                    if (names.size() != numPkColumnsProbe) {
                        continue;
                    }
                    for (String name : names) {
                        if (!pkColumnsProbe.contains(name)) {
                            continue outer;
                        }
                    }
                    exportedKeyList.add(fk);
                }
                if (!exportedKeyList.isEmpty()) {
                    pkey.setExportedKeys(exportedKeyList);
                }
            }
        }
        return pkey;
    }

    /**
     * @return column position is not provided in the returning foreign keys
     */
    @Nonnull
    public static Collection<ForeignKey> getExportedKeys(@Nonnull final Connection conn,
            @Nullable final String pkTableName, final boolean setColumnPositions) throws SQLException {
        DatabaseMetaData metadata = conn.getMetaData();
        String catalog = conn.getCatalog();
        final Map<String, ForeignKey> mapping = new HashMap<String, ForeignKey>(4);
        final ResultSet rs = metadata.getExportedKeys(catalog, null, pkTableName);
        try {
            while (rs.next()) {
                final String fkName = rs.getString("FK_NAME");
                ForeignKey fk = mapping.get(fkName);
                if (fk == null) {
                    String fkTableName = rs.getString("FKTABLE_NAME");
                    fk = new ForeignKey(fkName, fkTableName, pkTableName);
                    mapping.put(fkName, fk);
                }
                fk.addColumn(rs, metadata);
            }
        } finally {
            rs.close();
        }
        final Collection<ForeignKey> fkeys = mapping.values();
        if (setColumnPositions) {
            for (ForeignKey fk : fkeys) {
                fk.setColumnPositions(metadata);
            }
        }
        return fkeys;
    }

    public static boolean hasParentTable(@Nonnull final Connection conn, @Nullable final String pkTableName)
            throws SQLException {
        DatabaseMetaData metadata = conn.getMetaData();
        String catalog = conn.getCatalog();
        final ResultSet rs = metadata.getExportedKeys(catalog, null, pkTableName);
        try {
            return rs.next();
        } finally {
            rs.close();
        }
    }

    public static boolean hasParentTable(@Nonnull final PrimaryKey childTablePk) {
        List<ForeignKey> fkeys = childTablePk.getExportedKeys();
        if (fkeys == null || fkeys.isEmpty()) {
            return false;
        }
        return true;
    }

    public static boolean hasParentTableExportedKey(@Nonnull final DBAccessor dba,
            @Nonnull final PrimaryKey childTablePk) throws GridException {
        List<ForeignKey> fkeys = childTablePk.getExportedKeys();
        if (fkeys == null || fkeys.isEmpty()) {
            return false;
        }
        ForeignKey fk = fkeys.get(0);
        String parentTable = fk.getTableName();
        final Connection conn = GridDbUtils.getPrimaryDbConnection(dba, false);
        final boolean hasParent;
        try {
            hasParent = GridDbUtils.hasParentTable(conn, parentTable);
        } catch (SQLException e) {
            LOG.error("Failed to find parent table: " + parentTable, e);
            throw new GridException(e);
        } finally {
            JDBCUtils.closeQuietly(conn);
        }
        return hasParent;
    }

    /**
     * @return column position is provided in the returning foreign keys
     */
    @Nonnull
    public static Collection<ForeignKey> getForeignKeys(@Nonnull final Connection conn,
            @Nullable final String fkTableName, final boolean setColumnPositions) throws SQLException {
        DatabaseMetaData metadata = conn.getMetaData();
        String catalog = conn.getCatalog();
        final Map<String, ForeignKey> mapping = new HashMap<String, ForeignKey>(4);
        final ResultSet rs = metadata.getImportedKeys(catalog, null, fkTableName);
        try {
            while (rs.next()) {
                final String fkName = rs.getString("FK_NAME");
                ForeignKey fk = mapping.get(fkName);
                if (fk == null) {
                    //String fkTableName = rs.getString("FKTABLE_NAME");
                    String pkTableName = rs.getString("PKTABLE_NAME");
                    fk = new ForeignKey(fkName, fkTableName, pkTableName);
                    mapping.put(fkName, fk);
                }
                fk.addColumn(rs, metadata);
            }
        } finally {
            rs.close();
        }
        final Collection<ForeignKey> fkeys = mapping.values();
        if (setColumnPositions) {
            for (ForeignKey fk : fkeys) {
                fk.setColumnPositions(metadata);
            }
        }
        return fkeys;
    }

    public static String getCombinedColumnName(final List<String> columnNames, final boolean sortByName) {
        final int size = columnNames.size();
        if (size == 0) {
            throw new IllegalArgumentException();
        }
        if (size == 1) {
            return columnNames.get(0);
        }
        if (sortByName) {
            Collections.sort(columnNames);
        }
        final StringBuilder buf = new StringBuilder(32);
        for (int i = 0; i < size; i++) {
            if (i != 0) {
                buf.append('&');
            }
            String name = columnNames.get(i);
            buf.append(name);
        }
        return buf.toString();
    }

    public static String makeCopyIntoFileQuery(final String subquery, final String outputFilePath) {
        return "COPY (" + subquery + ") INTO '" + outputFilePath + "' USING DELIMITERS '|','\n','\"'";
    }

    public static String makeCopyIntoTableQuery(final String tableName, final String inputFilePath,
            final int expectedRecords) {
        if (expectedRecords <= 0) {
            throw new IllegalArgumentException("Illegal expected records: " + expectedRecords);
        }
        return "COPY " + expectedRecords + " RECORDS INTO \"" + tableName + "\" FROM '" + inputFilePath
                + "' USING DELIMITERS '|','\n','\"'";
    }

    public static String combineFields(@Nonnull final String[] fields, final int numFields,
            @Nonnull final StringBuilder buf) {
        if (numFields < 1) {
            throw new IllegalArgumentException("Illegal numField: " + numFields);
        }
        if (numFields == 1) {
            return fields[0];
        }
        StringUtils.clear(buf);
        buf.append(fields[0]);
        for (int i = 1; i < numFields; i++) {
            buf.append('|');
            buf.append(fields[i]);
        }
        return buf.toString();
    }

    public static String[] getFkIndexNames(final Collection<ForeignKey> fkeys, final int numFkeys) {
        final String[] idxNames = new String[numFkeys];
        final Iterator<ForeignKey> itor = fkeys.iterator();
        for (int i = 0; i < numFkeys; i++) {
            ForeignKey fk = itor.next();
            String fkTable = fk.getFkTableName();
            List<String> fkColumns = fk.getFkColumnNames();
            idxNames[i] = GridDbUtils.getIndexName(fkTable, fkColumns);
        }
        return idxNames;
    }

    public static int[][] getFkPositions(final Collection<ForeignKey> fkeys, final int numFkeys) {
        final int[][] positions = new int[numFkeys][];
        final Iterator<ForeignKey> itor = fkeys.iterator();
        for (int i = 0; i < numFkeys; i++) {
            ForeignKey fk = itor.next();
            int[] fkeyPos = fk.getFkColumnPositions(true);
            positions[i] = fkeyPos;
        }
        return positions;
    }

    @Nullable
    public static String[] getParentTableFkIndexNames(final PrimaryKey childTablePkey) {
        final List<ForeignKey> parentFkeys = childTablePkey.getExportedKeys();
        if (parentFkeys == null) {
            return null;
        }
        final int numParents = parentFkeys.size();
        if (numParents == 0) {
            return null;
        }
        final String[] idxNames = new String[numParents];
        for (int i = 0; i < numParents; i++) {
            ForeignKey parentFkey = parentFkeys.get(i);
            String fkTable = parentFkey.getFkTableName();
            List<String> fkColumns = parentFkey.getFkColumnNames();
            String idxName = GridDbUtils.getIndexName(fkTable, fkColumns);
            idxNames[i] = idxName;
        }
        return idxNames;
    }

    public static int getMaxColumnCount(@Nonnull final PrimaryKey pkey,
            @Nonnull final Collection<ForeignKey> fkeys) {
        int max = pkey.getColumnNames().size();
        for (final ForeignKey fkey : fkeys) {
            int size = fkey.getColumnNames().size();
            max = Math.max(size, max);
        }
        return max;
    }

    public static int[] getChildTablesPartitionNo(final Collection<ForeignKey> fkeys, final int numFkeys,
            final DistributionCatalog catalog) {
        final int[] partitionNo = new int[numFkeys];
        final Iterator<ForeignKey> itor = fkeys.iterator();
        for (int i = 0; i < numFkeys; i++) {
            ForeignKey fk = itor.next();
            String tblname = fk.getPkTableName();
            int n = catalog.getTablePartitionNo(tblname, true);
            partitionNo[i] = n;
        }
        return partitionNo;
    }

    public static String getIndexName(final String tableName, final List<String> columnNames) {
        final int numColumns = columnNames.size();
        if (numColumns == 0) {
            throw new IllegalArgumentException("No columns was specified for table: " + tableName);
        }
        final StringBuilder buf = new StringBuilder(32);
        buf.append(tableName);
        buf.append('.');
        for (int i = 0; i < numColumns; i++) {
            if (i != 0) {
                buf.append('_');
            }
            String colname = columnNames.get(i);
            buf.append(colname);
        }
        buf.append(".fktbl");
        return buf.toString();
    }

    @SuppressWarnings("unchecked")
    @Nullable
    public static LRUMap<String, List<NodeWithPartitionNo>>[] getFkIndexCaches(final int numFks) {
        if (numFks == 0) {
            return null;
        }
        final LRUMap<String, List<NodeWithPartitionNo>>[] caches = new LRUMap[numFks];
        for (int i = 0; i < numFks; i++) {
            caches[i] = new LRUMap<String, List<NodeWithPartitionNo>>(1000);
        }
        return caches;
    }

    public static String generateCopyIntoQuery(final String tableName, final DBPartitioningJobConf jobConf) {
        return "COPY INTO \"" + tableName + "\" FROM '<src>' USING DELIMITERS '" + jobConf.getFieldSeparator()
                + "', '" + jobConf.getRecordSeparator() + "', '" + jobConf.getStringQuote() + '\'';
    }

    public static void sendfile(final ExecutorService sencExecs, final GridXferClient dfsClient,
            final String fileName, final FastByteArrayOutputStream data, final GridNode dstNode,
            final int dstPort) {
        InetAddress dstAddr = dstNode.getPhysicalAdress();
        Runnable run = new RunnableFileSender(data, fileName, null, dstAddr, dstPort, true, true);
        sencExecs.submit(run);

        String alteredFileName = GridUtils.alterFileName(fileName, dstNode);
        List<GridNode> replicas = dstNode.getReplicas();
        for (GridNode replicaNode : replicas) {
            dstAddr = replicaNode.getPhysicalAdress();
            run = new RunnableFileSender(data, alteredFileName, null, dstAddr, dstPort, true, true);
            sencExecs.submit(run);
        }
    }

    public static long invokeCsvLoadJob(final GridKernel kernel, final String csvFileName,
            final HashMap<GridNode, MutableLong> assignMap, final DBPartitioningJobConf jobConf) {
        String connectUrl = jobConf.getConnectUrl();
        String tableName = jobConf.getTableName();
        String createTableDDL = jobConf.getCreateTableDDL();
        String copyIntoQuery = generateCopyIntoQuery(tableName, jobConf);
        String alterTableDDL = jobConf.getAlterTableDDL();

        MonetDBCsvLoadOperation ops = new MonetDBCsvLoadOperation(connectUrl, tableName, csvFileName,
                createTableDDL, copyIntoQuery, alterTableDDL);
        ops.setAuth(jobConf.getUserName(), jobConf.getPassword());
        final Pair<MonetDBCsvLoadOperation, Map<GridNode, MutableLong>> pair = new Pair<MonetDBCsvLoadOperation, Map<GridNode, MutableLong>>(
                ops, assignMap);

        final GridJobFuture<Long> future = kernel.execute(MonetDBInvokeCsvLoadJob.class, pair);
        final Long numInserted;
        try {
            numInserted = future.get();
        } catch (InterruptedException ie) {
            LOG.error(ie.getMessage(), ie);
            throw new IllegalStateException(ie);
        } catch (ExecutionException ee) {
            LOG.error(ee.getMessage(), ee);
            throw new IllegalStateException(ee);
        }
        return (numInserted == null) ? -1L : numInserted.longValue();
    }

    public static String generateCsvFileName(final DBPartitioningJobConf jobConf, final GridNode senderNode) {
        String tblName = jobConf.getTableName();
        String addr = senderNode.getPhysicalAdress().getHostAddress();
        return tblName + addr + ".csv";
    }

}