Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 04280200C53 for ; Tue, 11 Apr 2017 12:05:53 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 02DBE160B89; Tue, 11 Apr 2017 10:05:53 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 6ACEE160BAB for ; Tue, 11 Apr 2017 12:05:51 +0200 (CEST) Received: (qmail 69984 invoked by uid 500); 11 Apr 2017 10:05:50 -0000 Mailing-List: contact commits-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@ignite.apache.org Delivered-To: mailing list commits@ignite.apache.org Received: (qmail 69859 invoked by uid 99); 11 Apr 2017 10:05:50 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 11 Apr 2017 10:05:50 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 77BEDE0287; Tue, 11 Apr 2017 10:05:50 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: ptupitsyn@apache.org To: commits@ignite.apache.org Date: Tue, 11 Apr 2017 10:05:54 -0000 Message-Id: <1b7214167b3b44f3843c10d2d5d15f40@git.apache.org> In-Reply-To: <6865ecd947f64e71b1cd47dbe4db1b7a@git.apache.org> References: <6865ecd947f64e71b1cd47dbe4db1b7a@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [05/22] ignite git commit: IGNITE-4349 Discontinue the schema-import utility. archived-at: Tue, 11 Apr 2017 10:05:53 -0000 http://git-wip-us.apache.org/repos/asf/ignite/blob/f6ee9c0f/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java ---------------------------------------------------------------------- diff --git a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java new file mode 100644 index 0000000..1722948 --- /dev/null +++ b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java @@ -0,0 +1,359 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You 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 org.apache.ignite.console.agent.db.dialect; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collection; +import java.util.Collections; +import java.util.HashSet; +import java.util.LinkedHashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; + +import org.apache.ignite.cache.QueryIndex; +import org.apache.ignite.console.agent.db.DbColumn; +import org.apache.ignite.console.agent.db.DbTable; + +import static java.sql.Types.BIGINT; +import static java.sql.Types.BLOB; +import static java.sql.Types.BOOLEAN; +import static java.sql.Types.CHAR; +import static java.sql.Types.CLOB; +import static java.sql.Types.DATE; +import static java.sql.Types.DOUBLE; +import static java.sql.Types.FLOAT; +import static java.sql.Types.INTEGER; +import static java.sql.Types.LONGVARBINARY; +import static java.sql.Types.LONGVARCHAR; +import static java.sql.Types.NUMERIC; +import static java.sql.Types.OTHER; +import static java.sql.Types.SMALLINT; +import static java.sql.Types.SQLXML; +import static java.sql.Types.TIMESTAMP; +import static java.sql.Types.TINYINT; +import static java.sql.Types.VARCHAR; + +/** + * Oracle specific metadata dialect. + */ +public class OracleMetadataDialect extends DatabaseMetadataDialect { + /** SQL to get columns metadata. */ + private static final String SQL_COLUMNS = "SELECT a.owner, a.table_name, a.column_name, a.nullable," + + " a.data_type, a.data_precision, a.data_scale " + + "FROM all_tab_columns a %s " + + " %s " + + " ORDER BY a.owner, a.table_name, a.column_id"; + + /** SQL to get list of PRIMARY KEYS columns. */ + private static final String SQL_PRIMARY_KEYS = "SELECT b.column_name" + + " FROM all_constraints a" + + " INNER JOIN all_cons_columns b ON a.owner = b.owner AND a.constraint_name = b.constraint_name" + + " WHERE a.owner = ? and a.table_name = ? AND a.constraint_type = 'P'"; + + /** SQL to get indexes metadata. */ + private static final String SQL_INDEXES = "SELECT i.index_name, u.column_expression, i.column_name, i.descend" + + " FROM all_ind_columns i" + + " LEFT JOIN user_ind_expressions u on u.index_name = i.index_name and i.table_name = u.table_name" + + " WHERE i.index_owner = ? and i.table_name = ?" + + " ORDER BY i.index_name, i.column_position"; + + /** Owner index. */ + private static final int OWNER_IDX = 1; + + /** Table name index. */ + private static final int TBL_NAME_IDX = 2; + + /** Column name index. */ + private static final int COL_NAME_IDX = 3; + + /** Nullable index. */ + private static final int NULLABLE_IDX = 4; + + /** Data type index. */ + private static final int DATA_TYPE_IDX = 5; + + /** Numeric precision index. */ + private static final int DATA_PRECISION_IDX = 6; + + /** Numeric scale index. */ + private static final int DATA_SCALE_IDX = 7; + + /** Index name index. */ + private static final int IDX_NAME_IDX = 1; + + /** Index name index. */ + private static final int IDX_EXPR_IDX = 2; + + /** Index column name index. */ + private static final int IDX_COL_NAME_IDX = 3; + + /** Index column sort order index. */ + private static final int IDX_COL_DESCEND_IDX = 4; + + /** {@inheritDoc} */ + @Override public Set systemSchemas() { + return new HashSet<>(Arrays.asList("ANONYMOUS", "CTXSYS", "DBSNMP", "EXFSYS", "LBACSYS", "MDSYS", "MGMT_VIEW", + "OLAPSYS", "OWBSYS", "ORDPLUGINS", "ORDSYS", "OUTLN", "SI_INFORMTN_SCHEMA", "SYS", "SYSMAN", "SYSTEM", + "TSMSYS", "WK_TEST", "WKSYS", "WKPROXY", "WMSYS", "XDB", + + "APEX_040000", "APEX_PUBLIC_USER", "DIP", "FLOWS_30000", "FLOWS_FILES", "MDDATA", "ORACLE_OCM", + "SPATIAL_CSW_ADMIN_USR", "SPATIAL_WFS_ADMIN_USR", "XS$NULL", + + "BI", "HR", "OE", "PM", "IX", "SH")); + } + + /** {@inheritDoc} */ + @Override public Collection schemas(Connection conn) throws SQLException { + Collection schemas = new ArrayList<>(); + + ResultSet rs = conn.getMetaData().getSchemas(); + + Set sysSchemas = systemSchemas(); + + while(rs.next()) { + String schema = rs.getString(1); + + if (!sysSchemas.contains(schema) && !schema.startsWith("FLOWS_")) + schemas.add(schema); + } + + return schemas; + } + + /** + * @param rs Result set with column type metadata from Oracle database. + * @return JDBC type. + * @throws SQLException If failed to decode type. + */ + private int decodeType(ResultSet rs) throws SQLException { + String type = rs.getString(DATA_TYPE_IDX); + + if (type.startsWith("TIMESTAMP")) + return TIMESTAMP; + else { + switch (type) { + case "CHAR": + case "NCHAR": + return CHAR; + + case "VARCHAR2": + case "NVARCHAR2": + return VARCHAR; + + case "LONG": + return LONGVARCHAR; + + case "LONG RAW": + return LONGVARBINARY; + + case "FLOAT": + return FLOAT; + + case "NUMBER": + int precision = rs.getInt(DATA_PRECISION_IDX); + int scale = rs.getInt(DATA_SCALE_IDX); + + if (scale > 0) { + if (scale < 4 && precision < 19) + return FLOAT; + + if (scale > 4 || precision > 19) + return DOUBLE; + + return NUMERIC; + } + else { + if (precision < 1) + return INTEGER; + + if (precision < 2) + return BOOLEAN; + + if (precision < 4) + return TINYINT; + + if (precision < 6) + return SMALLINT; + + if (precision < 11) + return INTEGER; + + if (precision < 20) + return BIGINT; + + return NUMERIC; + } + + case "DATE": + return DATE; + + case "BFILE": + case "BLOB": + return BLOB; + + case "CLOB": + case "NCLOB": + return CLOB; + + case "XMLTYPE": + return SQLXML; + } + } + + return OTHER; + } + + /** + * Retrieve primary key columns. + * + * @param stmt Prepared SQL statement to execute. + * @param owner DB owner. + * @param tbl Table name. + * @return Primary key columns. + * @throws SQLException If failed to retrieve primary key columns. + */ + private Set primaryKeys(PreparedStatement stmt, String owner, String tbl) throws SQLException { + Set pkCols = new HashSet<>(); + + stmt.setString(1, owner); + stmt.setString(2, tbl); + + try (ResultSet pkRs = stmt.executeQuery()) { + while(pkRs.next()) + pkCols.add(pkRs.getString(1)); + } + + return pkCols; + } + + /** + * Retrieve index columns. + * + * @param stmt Prepared SQL statement to execute. + * @param owner DB owner. + * @param tbl Table name. + * @return Indexes. + * @throws SQLException If failed to retrieve indexes columns. + */ + private Collection indexes(PreparedStatement stmt, String owner, String tbl) throws SQLException { + Map idxs = new LinkedHashMap<>(); + + stmt.setString(1, owner); + stmt.setString(2, tbl); + + try (ResultSet idxsRs = stmt.executeQuery()) { + while (idxsRs.next()) { + String idxName = idxsRs.getString(IDX_NAME_IDX); + + QueryIndex idx = idxs.get(idxName); + + if (idx == null) { + idx = index(idxName); + + idxs.put(idxName, idx); + } + + String expr = idxsRs.getString(IDX_EXPR_IDX); + + String col = expr == null ? idxsRs.getString(IDX_COL_NAME_IDX) : expr.replaceAll("\"", ""); + + idx.getFields().put(col, !"DESC".equals(idxsRs.getString(IDX_COL_DESCEND_IDX))); + } + } + + return idxs.values(); + } + + /** {@inheritDoc} */ + @Override public Collection tables(Connection conn, List schemas, boolean tblsOnly) + throws SQLException { + Collection tbls = new ArrayList<>(); + + PreparedStatement pkStmt = conn.prepareStatement(SQL_PRIMARY_KEYS); + + PreparedStatement idxStmt = conn.prepareStatement(SQL_INDEXES); + + if (schemas.isEmpty()) + schemas.add(null); + + Set sysSchemas = systemSchemas(); + + try (Statement colsStmt = conn.createStatement()) { + for (String schema: schemas) { + if (systemSchemas().contains(schema) || (schema != null && schema.startsWith("FLOWS_"))) + continue; + + Collection cols = new ArrayList<>(); + + Set pkCols = Collections.emptySet(); + Collection idxs = Collections.emptyList(); + + String sql = String.format(SQL_COLUMNS, + tblsOnly ? "INNER JOIN all_tables b on a.table_name = b.table_name and a.owner = b.owner" : "", + schema != null ? String.format(" WHERE a.owner = '%s' ", schema) : ""); + + try (ResultSet colsRs = colsStmt.executeQuery(sql)) { + String prevSchema = ""; + String prevTbl = ""; + + boolean first = true; + + while (colsRs.next()) { + String owner = colsRs.getString(OWNER_IDX); + String tbl = colsRs.getString(TBL_NAME_IDX); + + if (sysSchemas.contains(owner) || (schema != null && schema.startsWith("FLOWS_"))) + continue; + + boolean changed = !owner.equals(prevSchema) || !tbl.equals(prevTbl); + + if (changed) { + if (first) + first = false; + else + tbls.add(table(prevSchema, prevTbl, cols, idxs)); + + prevSchema = owner; + prevTbl = tbl; + cols = new ArrayList<>(); + pkCols = primaryKeys(pkStmt, owner, tbl); + idxs = indexes(idxStmt, owner, tbl); + } + + String colName = colsRs.getString(COL_NAME_IDX); + + cols.add(new DbColumn(colName, decodeType(colsRs), pkCols.contains(colName), + !"N".equals(colsRs.getString(NULLABLE_IDX)), false)); + } + + if (!cols.isEmpty()) + tbls.add(table(prevSchema, prevTbl, cols, idxs)); + } + } + } + + return tbls; + } +} http://git-wip-us.apache.org/repos/asf/ignite/blob/f6ee9c0f/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/handlers/DatabaseListener.java ---------------------------------------------------------------------- diff --git a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/handlers/DatabaseListener.java b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/handlers/DatabaseListener.java index 4577228..a0e9f8f 100644 --- a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/handlers/DatabaseListener.java +++ b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/handlers/DatabaseListener.java @@ -34,8 +34,8 @@ import java.util.Map; import java.util.Properties; import org.apache.ignite.console.agent.AgentConfiguration; import org.apache.ignite.console.demo.AgentMetadataDemo; -import org.apache.ignite.schema.parser.DbMetadataReader; -import org.apache.ignite.schema.parser.DbTable; +import org.apache.ignite.console.agent.db.DbMetadataReader; +import org.apache.ignite.console.agent.db.DbTable; import org.apache.log4j.Logger; import static org.apache.ignite.console.agent.AgentUtils.resolvePath; @@ -51,6 +51,9 @@ public class DatabaseListener { private final File driversFolder; /** */ + private final DbMetadataReader dbMetaReader; + + /** */ private final AbstractListener schemasLsnr = new AbstractListener() { @Override public Object execute(Map args) throws Exception { String driverPath = null; @@ -79,6 +82,7 @@ public class DatabaseListener { } }; + /** */ private final AbstractListener metadataLsnr = new AbstractListener() { @SuppressWarnings("unchecked") @Override public Object execute(Map args) throws Exception { @@ -118,6 +122,7 @@ public class DatabaseListener { } }; + /** */ private final AbstractListener availableDriversLsnr = new AbstractListener() { @Override public Object execute(Map args) throws Exception { if (driversFolder == null) { @@ -176,6 +181,7 @@ public class DatabaseListener { */ public DatabaseListener(AgentConfiguration cfg) { driversFolder = resolvePath(cfg.driversFolder() == null ? "jdbc-drivers" : cfg.driversFolder()); + dbMetaReader = new DbMetadataReader(); } /** @@ -184,7 +190,7 @@ public class DatabaseListener { * @param jdbcUrl JDBC URL. * @param jdbcInfo Properties to connect to database. * @return Connection to database. - * @throws SQLException + * @throws SQLException If failed to connect. */ private Connection connect(String jdbcDriverJarPath, String jdbcDriverCls, String jdbcUrl, Properties jdbcInfo) throws SQLException { @@ -194,7 +200,7 @@ public class DatabaseListener { if (!new File(jdbcDriverJarPath).isAbsolute() && driversFolder != null) jdbcDriverJarPath = new File(driversFolder, jdbcDriverJarPath).getPath(); - return DbMetadataReader.getInstance().connect(jdbcDriverJarPath, jdbcDriverCls, jdbcUrl, jdbcInfo); + return dbMetaReader.connect(jdbcDriverJarPath, jdbcDriverCls, jdbcUrl, jdbcInfo); } /** @@ -203,7 +209,7 @@ public class DatabaseListener { * @param jdbcUrl JDBC URL. * @param jdbcInfo Properties to connect to database. * @return Collection of schema names. - * @throws SQLException + * @throws SQLException If failed to load schemas. */ protected Collection schemas(String jdbcDriverJarPath, String jdbcDriverCls, String jdbcUrl, Properties jdbcInfo) throws SQLException { @@ -212,7 +218,7 @@ public class DatabaseListener { ", drvCls=" + jdbcDriverCls + ", jdbcUrl=" + jdbcUrl + "]"); try (Connection conn = connect(jdbcDriverJarPath, jdbcDriverCls, jdbcUrl, jdbcInfo)) { - Collection schemas = DbMetadataReader.getInstance().schemas(conn); + Collection schemas = dbMetaReader.schemas(conn); if (log.isDebugEnabled()) log.debug("Finished collection of schemas [jdbcUrl=" + jdbcUrl + ", count=" + schemas.size() + "]"); @@ -261,7 +267,7 @@ public class DatabaseListener { ", drvCls=" + jdbcDriverCls + ", jdbcUrl=" + jdbcUrl + "]"); try (Connection conn = connect(jdbcDriverJarPath, jdbcDriverCls, jdbcUrl, jdbcInfo)) { - Collection metadata = DbMetadataReader.getInstance().metadata(conn, schemas, tblsOnly); + Collection metadata = dbMetaReader.metadata(conn, schemas, tblsOnly); if (log.isDebugEnabled()) log.debug("Finished collection of metadata [jdbcUrl=" + jdbcUrl + ", count=" + metadata.size() + "]"); http://git-wip-us.apache.org/repos/asf/ignite/blob/f6ee9c0f/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index 04226b9..41338dd 100644 --- a/pom.xml +++ b/pom.xml @@ -340,11 +340,6 @@ - - - - -