Search in sources :

Example 21 with TableDetailInfo

use of com.cubrid.common.core.common.model.TableDetailInfo in project cubrid-manager by CUBRID.

the class ColumnViewerSorter method showEditDialog.

public void showEditDialog(Table table, int index) {
    if (tableListView.getTable().getItemCount() < index) {
        return;
    }
    TableItem item = table.getItem(index);
    Object objData = item.getData();
    if (objData != null && objData instanceof TableDetailInfo) {
        TableDetailInfo info = (TableDetailInfo) objData;
        InputTextDialog dialog = new InputTextDialog(Display.getCurrent().getActiveShell(), Messages.titleTableDescEditor, Messages.msgTableDescEditor, Messages.labelTableDescEditor, info.getTableDesc());
        if (dialog.open() == IDialogConstants.OK_ID) {
            String tableName = info.getTableName();
            String columnName = null;
            String description = dialog.getResult();
            Connection conn = null;
            try {
                conn = JDBCConnectionManager.getConnection(database.getDatabaseInfo(), true);
                SchemaCommentHandler.updateDescription(database.getDatabaseInfo(), conn, tableName, columnName, description);
                info.setTableDesc(description);
                tableListView.setInput(tableList);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                QueryUtil.commit(conn);
                QueryUtil.freeQuery(conn);
            }
        }
    //For bug TOOLS-3324
    //			if (info != null) {
    //				openTableDetail(info);
    //			}
    }
}
Also used : InputTextDialog(com.cubrid.common.ui.spi.table.button.InputTextDialog) TableItem(org.eclipse.swt.widgets.TableItem) TableDetailInfo(com.cubrid.common.core.common.model.TableDetailInfo) Connection(java.sql.Connection) PartInitException(org.eclipse.ui.PartInitException) SQLException(java.sql.SQLException)

Example 22 with TableDetailInfo

use of com.cubrid.common.core.common.model.TableDetailInfo in project cubrid-manager by CUBRID.

the class LoadTableDetailInfoTask method execute.

public void execute() {
    if (StringUtil.isEmpty(tableName)) {
        return;
    }
    StringBuilder sql = new StringBuilder().append("SELECT \n").append("    c.class_name, \n").append("    COUNT(*) AS count_column, \n").append("    CAST(SUM(\n").append("    CASE \n").append("          WHEN \n").append("              \"data_type\" = 'BIGINT' THEN 8.0 \n").append("          WHEN \n").append("              \"data_type\" = 'INTEGER' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'SMALLINT' THEN 2.0 \n").append("          WHEN \n").append("              \"data_type\" = 'FLOAT' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'DOUBLE' THEN 8.0 \n").append("          WHEN \n").append("              \"data_type\" = 'MONETARY' THEN 12.0 \n").append("          WHEN \n").append("              \"data_type\" = 'STRING' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'VARCHAR' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'NVARCHAR' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'CHAR' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'NCHAR' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'TIMESTAMP' THEN 8.0 \n").append("          WHEN \n").append("              \"data_type\" = 'DATE' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'TIME' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'DATETIME' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'BIT' THEN FLOOR(prec / 8.0) \n").append("          WHEN \n").append("              \"data_type\" = 'BIT VARYING' THEN FLOOR(prec / 8.0) \n").append("          ELSE 0 \n").append("    END ) AS BIGINT) AS size_column, \n").append("    SUM(\n").append("    CASE \n").append("          WHEN \n").append("              \"data_type\" = 'STRING' THEN 1 \n").append("          WHEN \n").append("              \"data_type\" = 'VARCHAR' THEN 1 \n").append("          WHEN \n").append("              \"data_type\" = 'NVARCHAR' THEN 1 \n").append("          WHEN \n").append("              \"data_type\" = 'NCHAR' THEN 1 \n").append("          WHEN \n").append("              \"data_type\" = 'BIT VARYING' THEN 1 \n").append("          ELSE 0 \n").append("    END ) AS size_over_column, \n").append("    c.class_type, \n").append("    c.partitioned \n").append("FROM \n").append("    db_class c, \n").append("    db_attribute a \n").append("WHERE \n").append("    c.class_name = ? \n").append("    AND \n").append("    c.class_name = a.class_name \n").append("    AND \n").append("    c.is_system_class = 'NO' \n").append("    AND \n").append("    c.class_type = 'CLASS' \n").append("    AND \n").append("    a.from_class_name IS NULL \n").append("GROUP BY c.class_name;\n");
    String query = sql.toString();
    StringBuilder sqlIndex = new StringBuilder().append("SELECT \n").append("    c.class_name, \n").append("    SUM(\n").append("    CASE \n").append("          WHEN \n").append("              i.is_unique = 'YES' \n").append("              AND \n").append("              i.is_primary_key = 'NO' THEN 1 \n").append("          ELSE 0 \n").append("    END ) AS count_unique, \n").append("    SUM(\n").append("    CASE \n").append("          WHEN \n").append("              i.is_unique = 'YES' \n").append("              AND \n").append("              i.is_primary_key = 'YES' THEN 1 \n").append("          ELSE 0 \n").append("    END ) AS count_primary_key, \n").append("    SUM(DECODE(i.is_foreign_key, 'YES', 1, 0)) AS count_foreign_key, \n").append("    SUM(\n").append("    CASE \n").append("          WHEN \n").append("              i.is_unique = 'NO' \n").append("              AND \n").append("              i.is_primary_key = 'NO' THEN 1 \n").append("          ELSE 0 \n").append("    END ) AS count_index \n").append("FROM \n").append("    db_class c, \n").append("    db_index_key k, \n").append("    db_index i \n").append("WHERE \n").append("    c.class_name = ? ").append("    AND \n").append("    c.class_name = k.class_name \n").append("    AND \n").append("    k.class_name = i.class_name \n").append("    AND \n").append("    k.index_name = i.index_name \n").append("    AND \n").append("    c.class_type = 'CLASS' \n").append("    AND \n").append("    c.is_system_class = 'NO' \n").append("    AND \n").append("    i.key_count >= 1 \n").append("    AND \n").append("    NOT EXISTS (SELECT 1 FROM db_partition p WHERE c.class_name = LOWER(p.partition_class_name)) \n").append("GROUP BY c.class_name;\n");
    String queryIndex = sqlIndex.toString();
    // [TOOLS-2425]Support shard broker
    if (CubridDatabase.hasValidDatabaseInfo(database)) {
        query = database.getDatabaseInfo().wrapShardQuery(query);
        queryIndex = database.getDatabaseInfo().wrapShardQuery(queryIndex);
    }
    PreparedStatement pStmt = null;
    try {
        pStmt = connection.prepareStatement(query);
        pStmt.setString(1, tableName);
        rs = pStmt.executeQuery();
        while (rs.next()) {
            String tableName = rs.getString(1);
            int countColumn = rs.getInt(2);
            BigDecimal recordsSize = rs.getBigDecimal(3);
            boolean columnOverSize = rs.getInt(4) > 0;
            String classType = rs.getString(5);
            String partitioned = rs.getString(6);
            tableInfo = new TableDetailInfo();
            tableInfo.setTableName(tableName);
            tableInfo.setColumnsCount(countColumn);
            tableInfo.setRecordsSize(recordsSize);
            tableInfo.setHasUnCountColumnSize(columnOverSize);
            tableInfo.setClassType(classType);
            tableInfo.setPartitioned(partitioned);
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        QueryUtil.freeQuery(pStmt, rs);
    }
    if (tableInfo != null) {
        try {
            pStmt = connection.prepareStatement(queryIndex);
            pStmt.setString(1, tableName);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString(1);
                int ukCount = rs.getInt(2);
                int pkCount = rs.getInt(3);
                int fkCount = rs.getInt(4);
                int indexCount = rs.getInt(5);
                tableInfo.setTableName(tableName);
                tableInfo.setUkCount(ukCount);
                tableInfo.setPkCount(pkCount);
                tableInfo.setFkCount(fkCount);
                tableInfo.setIndexCount(indexCount);
            }
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        } finally {
            QueryUtil.freeQuery(pStmt, rs);
        }
    }
    try {
        Map<String, SchemaComment> commentMap = SchemaCommentHandler.loadTableDescriptions(database.getDatabaseInfo(), connection);
        SchemaComment schemaComment = SchemaCommentHandler.find(commentMap, tableInfo.getTableName(), null);
        if (schemaComment != null) {
            tableInfo.setTableDesc(schemaComment.getDescription());
        }
    } catch (SQLException ex) {
        LOGGER.error(ex.getMessage(), ex);
    }
    finish();
}
Also used : SQLException(java.sql.SQLException) TableDetailInfo(com.cubrid.common.core.common.model.TableDetailInfo) PreparedStatement(java.sql.PreparedStatement) SchemaComment(com.cubrid.common.core.schemacomment.model.SchemaComment) BigDecimal(java.math.BigDecimal) SQLException(java.sql.SQLException)

Example 23 with TableDetailInfo

use of com.cubrid.common.core.common.model.TableDetailInfo in project cubrid-manager by CUBRID.

the class LoadTableRecordCountsProgress method run.

public void run(IProgressMonitor monitor) throws InvocationTargetException, InterruptedException {
    Connection conn = null;
    monitor.beginTask(Messages.loadTableRecordCountsProgressTaskName, tableList.size());
    try {
        conn = JDBCConnectionManager.getConnection(database.getDatabaseInfo(), true);
        for (TableDetailInfo tablesDetailInfo : tableList) {
            monitor.subTask(Messages.bind(Messages.loadTableRecordCountsProgressSubTaskName, tablesDetailInfo.getTableName()));
            int recordCount = getRecordsCount(conn, tablesDetailInfo.getTableName());
            tablesDetailInfo.setRecordsCount(recordCount);
            monitor.worked(1);
            if (monitor.isCanceled()) {
                break;
            }
        }
        success = true;
    } catch (Exception e) {
        LOGGER.error("", e);
    } finally {
        QueryUtil.freeQuery(conn);
        monitor.done();
    }
}
Also used : Connection(java.sql.Connection) TableDetailInfo(com.cubrid.common.core.common.model.TableDetailInfo) InvocationTargetException(java.lang.reflect.InvocationTargetException) SQLException(java.sql.SQLException)

Example 24 with TableDetailInfo

use of com.cubrid.common.core.common.model.TableDetailInfo in project cubrid-manager by CUBRID.

the class OpenTablesDetailInfoPartProgress method run.

public void run(IProgressMonitor monitor) throws InvocationTargetException, InterruptedException {
    DatabaseInfo databaseInfo = NodeUtil.findDatabaseInfo(database);
    if (databaseInfo == null) {
        return;
    }
    Connection conn = null;
    try {
        monitor.setTaskName(Messages.tablesDetailInfoPartProgressTaskName);
        if (databaseInfo.getUserTableInfoList() == null) {
            return;
        }
        tableList = new ArrayList<TableDetailInfo>();
        Map<String, TableDetailInfo> map = new HashMap<String, TableDetailInfo>();
        conn = JDBCConnectionManager.getConnection(databaseInfo, true);
        if (!loadUserSchemaList(conn, map)) {
            success = false;
            return;
        }
        Set<String> tableNameSet = map.keySet();
        if (tableNameSet != null) {
            Map<String, SchemaComment> comments = null;
            if (SchemaCommentHandler.isInstalledMetaTable(databaseInfo, conn)) {
                try {
                    comments = SchemaCommentHandler.loadTableDescriptions(databaseInfo, conn);
                } catch (SQLException e) {
                    LOGGER.error(e.getMessage(), e);
                }
            }
            List<String> tableNames = new ArrayList<String>();
            for (String tableName : tableNameSet) {
                tableNames.add(tableName);
            }
            Collections.sort(tableNames);
            for (String tableName : tableNames) {
                TableDetailInfo info = map.get(tableName);
                info.setRecordsCount(-1);
                SchemaComment cmt = SchemaCommentHandler.find(comments, tableName, null);
                if (cmt != null) {
                    info.setTableDesc(cmt.getDescription());
                }
                tableList.add(info);
            }
        }
        success = true;
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        QueryUtil.freeQuery(conn);
    }
}
Also used : DatabaseInfo(com.cubrid.cubridmanager.core.cubrid.database.model.DatabaseInfo) HashMap(java.util.HashMap) SQLException(java.sql.SQLException) Connection(java.sql.Connection) TableDetailInfo(com.cubrid.common.core.common.model.TableDetailInfo) ArrayList(java.util.ArrayList) SQLException(java.sql.SQLException) InvocationTargetException(java.lang.reflect.InvocationTargetException) SchemaComment(com.cubrid.common.core.schemacomment.model.SchemaComment)

Example 25 with TableDetailInfo

use of com.cubrid.common.core.common.model.TableDetailInfo in project cubrid-manager by CUBRID.

the class OpenTablesDetailInfoPartProgress method loadUserSchemaList.

public boolean loadUserSchemaList(Connection conn, Map<String, TableDetailInfo> tablesMap) {
    // FIXME move this logic to core module
    StringBuilder sql = new StringBuilder().append("SELECT \n").append("    c.class_name, \n").append("    COUNT(*) AS count_column, \n").append("    CAST(SUM(\n").append("    CASE \n").append("          WHEN \n").append("              \"data_type\" = 'BIGINT' THEN 8.0 \n").append("          WHEN \n").append("              \"data_type\" = 'INTEGER' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'SMALLINT' THEN 2.0 \n").append("          WHEN \n").append("              \"data_type\" = 'FLOAT' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'DOUBLE' THEN 8.0 \n").append("          WHEN \n").append("              \"data_type\" = 'MONETARY' THEN 12.0 \n").append("          WHEN \n").append("              \"data_type\" = 'STRING' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'VARCHAR' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'NVARCHAR' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'CHAR' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'NCHAR' THEN a.prec \n").append("          WHEN \n").append("              \"data_type\" = 'TIMESTAMP' THEN 8.0 \n").append("          WHEN \n").append("              \"data_type\" = 'DATE' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'TIME' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'DATETIME' THEN 4.0 \n").append("          WHEN \n").append("              \"data_type\" = 'BIT' THEN FLOOR(prec / 8.0) \n").append("          WHEN \n").append("              \"data_type\" = 'BIT VARYING' THEN FLOOR(prec / 8.0) \n").append("          ELSE 0 \n").append("    END ) AS BIGINT) AS size_column, \n").append("    SUM(\n").append("    CASE \n").append("          WHEN \n").append("              \"data_type\" = 'STRING' THEN 1 \n").append("          WHEN \n").append("              \"data_type\" = 'VARCHAR' THEN 1 \n").append("          WHEN \n").append("              \"data_type\" = 'NVARCHAR' THEN 1 \n").append("          WHEN \n").append("              \"data_type\" = 'NCHAR' THEN 1 \n").append("          WHEN \n").append("              \"data_type\" = 'BIT VARYING' THEN 1 \n").append("          ELSE 0 \n").append("    END ) AS size_over_column, \n").append("    MAX(c.class_type) AS class_type, \n").append("    MAX(c.partitioned) AS partitioned \n").append("FROM \n").append("    db_class c, \n").append("    db_attribute a \n").append("WHERE \n").append("    c.class_name = a.class_name \n").append("    AND \n").append("    c.is_system_class = 'NO' \n").append("    AND \n").append("    c.class_type = 'CLASS' \n").append("    AND \n").append("    a.from_class_name IS NULL \n").append("GROUP BY c.class_name\n");
    String query = sql.toString();
    // [TOOLS-2425]Support shard broker
    if (CubridDatabase.hasValidDatabaseInfo(database)) {
        query = database.getDatabaseInfo().wrapShardQuery(query);
    }
    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query);
        while (rs.next()) {
            String tableName = rs.getString(1);
            int countColumn = rs.getInt(2);
            BigDecimal recordsSize = rs.getBigDecimal(3);
            boolean columnOverSize = rs.getInt(4) > 0;
            String classType = rs.getString(5);
            String partitioned = rs.getString(6);
            TableDetailInfo info = null;
            if (tablesMap.containsKey(tableName)) {
                info = tablesMap.get(tableName);
            } else {
                info = new TableDetailInfo();
                tablesMap.put(tableName, info);
            }
            info.setTableName(tableName);
            info.setColumnsCount(countColumn);
            info.setRecordsSize(recordsSize);
            info.setHasUnCountColumnSize(columnOverSize);
            info.setClassType(classType);
            info.setPartitioned(partitioned);
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return false;
    } finally {
        QueryUtil.freeQuery(stmt, rs);
    }
    sql = new StringBuilder().append("SELECT \n").append("    c.class_name, \n").append("    SUM(\n").append("    CASE \n").append("          WHEN \n").append("              i.is_unique = 'YES' \n").append("              AND \n").append("              i.is_primary_key = 'NO' THEN 1 \n").append("          ELSE 0 \n").append("    END ) AS count_unique, \n").append("    SUM(\n").append("    CASE \n").append("          WHEN \n").append("              i.is_unique = 'YES' \n").append("              AND \n").append("              i.is_primary_key = 'YES' THEN 1 \n").append("          ELSE 0 \n").append("    END ) AS count_primary_key, \n").append("    SUM(DECODE(i.is_foreign_key, 'YES', 1, 0)) AS count_foreign_key, \n").append("    SUM(\n").append("    CASE \n").append("          WHEN \n").append("              i.is_unique = 'NO' \n").append("              AND \n").append("              i.is_primary_key = 'NO' THEN 1 \n").append("          ELSE 0 \n").append("    END ) AS count_index \n").append("FROM \n").append("    db_class c, \n").append("    db_index_key k, \n").append("    db_index i \n").append("WHERE \n").append("    c.class_name = k.class_name \n").append("    AND \n").append("    k.class_name = i.class_name \n").append("    AND \n").append("    k.index_name = i.index_name \n").append("    AND \n").append("    c.class_type = 'CLASS' \n").append("    AND \n").append("    c.is_system_class = 'NO' \n").append("    AND \n").append("    i.key_count >= 1 \n").append("    AND \n").append("    NOT EXISTS (SELECT 1 FROM db_partition p WHERE c.class_name = LOWER(p.partition_class_name)) \n").append("GROUP BY c.class_name;\n");
    query = sql.toString();
    // [TOOLS-2425]Support shard broker
    if (CubridDatabase.hasValidDatabaseInfo(database)) {
        query = database.getDatabaseInfo().wrapShardQuery(query);
    }
    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query);
        while (rs.next()) {
            String tableName = rs.getString(1);
            if (ConstantsUtil.isExtensionalSystemTable(tableName)) {
                continue;
            }
            int ukCount = rs.getInt(2);
            int pkCount = rs.getInt(3);
            int fkCount = rs.getInt(4);
            int indexCount = rs.getInt(5);
            TableDetailInfo info = null;
            if (tablesMap.containsKey(tableName)) {
                info = tablesMap.get(tableName);
            } else {
                info = new TableDetailInfo();
                tablesMap.put(tableName, info);
            }
            info.setTableName(tableName);
            info.setUkCount(ukCount);
            info.setPkCount(pkCount);
            info.setFkCount(fkCount);
            info.setIndexCount(indexCount);
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return false;
    } finally {
        QueryUtil.freeQuery(stmt, rs);
    }
    return true;
}
Also used : Statement(java.sql.Statement) ResultSet(java.sql.ResultSet) TableDetailInfo(com.cubrid.common.core.common.model.TableDetailInfo) BigDecimal(java.math.BigDecimal) SQLException(java.sql.SQLException) InvocationTargetException(java.lang.reflect.InvocationTargetException)

Aggregations

TableDetailInfo (com.cubrid.common.core.common.model.TableDetailInfo)27 ArrayList (java.util.ArrayList)11 Connection (java.sql.Connection)6 HashMap (java.util.HashMap)6 TableSchemaCompareModel (com.cubrid.common.ui.compare.schema.model.TableSchemaCompareModel)5 InvocationTargetException (java.lang.reflect.InvocationTargetException)5 SQLException (java.sql.SQLException)5 SchemaInfo (com.cubrid.common.core.common.model.SchemaInfo)4 WrappedDatabaseInfo (com.cubrid.common.ui.cubrid.database.erwin.WrappedDatabaseInfo)4 ICubridNode (com.cubrid.common.ui.spi.model.ICubridNode)4 CommonTaskExec (com.cubrid.common.ui.spi.progress.CommonTaskExec)4 ExecTaskWithProgress (com.cubrid.common.ui.spi.progress.ExecTaskWithProgress)4 DatabaseInfo (com.cubrid.cubridmanager.core.cubrid.database.model.DatabaseInfo)4 TableItem (org.eclipse.swt.widgets.TableItem)4 PartInitException (org.eclipse.ui.PartInitException)4 AbstractUITask (com.cubrid.common.core.task.AbstractUITask)3 ITask (com.cubrid.common.core.task.ITask)3 TableSchemaModel (com.cubrid.common.ui.compare.schema.model.TableSchemaModel)3 CubridDatabase (com.cubrid.common.ui.spi.model.CubridDatabase)3 OpenTablesDetailInfoPartProgress (com.cubrid.common.ui.spi.progress.OpenTablesDetailInfoPartProgress)3