Examples with JDBCSession org.jkiss.dbeaver.model.exec.jdbc.JDBCSession used on opensource projects

Example 1 with JDBCSession

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCSession in project dbeaver by serge-rider.

the class ExasolDataSource method initialize.

// -----------------------
// Initialization/Structure
// -----------------------
@Override
public void initialize(@NotNull DBRProgressMonitor monitor) throws DBException {
    super.initialize(monitor);
    try (JDBCSession session = DBUtils.openMetaSession(monitor, this, "Load data source meta info")) {
        // First try to get active schema from special register 'CURRENT
        // SCHEMA'
        this.activeSchemaName = determineActiveSchema(session);
        this.exasolCurrentUserPrivileges = new ExasolCurrentUserPrivileges(monitor, session, this);
        this.driverMajorVersion = session.getMetaData().getDriverMajorVersion();
    } catch (SQLException e) {
        LOG.warn("Error reading active schema", e);
    }
    String schemaSQL = "select b.object_name,b.owner,b.created,b.object_comment from EXA_ALL_OBJECTS b  " + "inner join EXA_SCHEMAS s on b.object_name = s.schema_name  where b.object_type = 'SCHEMA' ";
    if (exasolCurrentUserPrivileges.getatLeastV6()) {
        //additional where clause to filter virtual schemas
        schemaSQL += " and not schema_is_virtual ";
        //build virtual schema cache for >V6 databases
        virtualSchemaCache = new JDBCObjectSimpleCache<>(ExasolVirtualSchema.class, "select" + "	SCHEMA_NAME as OBJECT_NAME," + "	SCHEMA_OWNER AS OWNER," + "	ADAPTER_SCRIPT," + "	LAST_REFRESH," + "	LAST_REFRESH_BY," + "	ADAPTER_NOTES," + "	OBJECT_COMMENT," + "	CREATED" + " from" + "		EXA_VIRTUAL_SCHEMAS s" + "	INNER JOIN" + "		EXA_ALL_OBJECTS o" + "	ON" + "		o.OBJECT_NAME = s.SCHEMA_NAME AND" + "		o.OBJECT_TYPE = 'SCHEMA'");
    }
    schemaSQL += " union all select distinct SCHEMA_NAME as \"OBJECT_NAME\", 'SYS' as owner, cast(null as timestamp) as created, '' as \"OBJECT_COMMENT\" from SYS.EXA_SYSCAT " + "order by b.object_name";
    schemaCache = new JDBCObjectSimpleCache<>(ExasolSchema.class, schemaSQL);
    try {
        this.dataTypeCache.getAllObjects(monitor, this);
    } catch (DBException e) {
        LOG.warn("Error reading types info", e);
        this.dataTypeCache.setCache(Collections.<ExasolDataType>emptyList());
    }
    if (exasolCurrentUserPrivileges.getUserIsAuthorizedForUsers()) {
        this.userCache = new JDBCObjectSimpleCache<>(ExasolUser.class, "select * from EXA_DBA_USERS ORDER BY USER_NAME");
    }
    if (exasolCurrentUserPrivileges.getUserIsAuthorizedForRoles()) {
        this.roleCache = new JDBCObjectSimpleCache<>(ExasolRole.class, "SELECT * FROM EXA_DBA_ROLES ORDER BY ROLE_NAME");
    }
    if (exasolCurrentUserPrivileges.getUserIsAuthorizedForConnections()) {
        this.connectionCache = new JDBCObjectSimpleCache<>(ExasolConnection.class, "SELECT * FROM EXA_DBA_CONNECTIONS ORDER BY CONNECTION_NAME");
    }
    if (exasolCurrentUserPrivileges.getUserIsAuthorizedForConnectionPrivs()) {
        this.connectionGrantCache = new JDBCObjectSimpleCache<>(ExasolConnectionGrant.class, "SELECT c.*,P.ADMIN_OPTION,P.GRANTEE FROM SYS.EXA_DBA_CONNECTION_PRIVS P " + "INNER JOIN SYS.EXA_DBA_CONNECTIONS C on P.GRANTED_CONNECTION = C.CONNECTION_NAME ORDER BY P.GRANTEE,C.CONNECTION_NAME ");
    }
    if (exasolCurrentUserPrivileges.getUserIsAuthorizedForObjectPrivs()) {
        this.baseTableGrantCache = new JDBCObjectSimpleCache<>(ExasolBaseObjectGrant.class, "SELECT " + "	OBJECT_SCHEMA," + "	OBJECT_TYPE," + "	GRANTEE," + "	OBJECT_NAME," + "	GROUP_CONCAT(" + "		DISTINCT PRIVILEGE" + "	ORDER BY" + "		OBJECT_SCHEMA," + "		OBJECT_NAME" + "		SEPARATOR '|'" + "	) as PRIVS " + " FROM" + "	SYS.EXA_DBA_OBJ_PRIVS P" + " GROUP BY" + "	OBJECT_SCHEMA," + "	OBJECT_TYPE," + "	GRANTEE," + "	OBJECT_NAME ORDER BY GRANTEE,OBJECT_SCHEMA,OBJECT_TYPE,OBJECT_NAME");
    }
    if (exasolCurrentUserPrivileges.getUserIsAuthorizedForSystemPrivs()) {
        this.systemGrantCache = new JDBCObjectSimpleCache<>(ExasolSystemGrant.class, "SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM SYS.EXA_DBA_SYS_PRIVS ORDER BY GRANTEE,PRIVILEGE");
    }
    if (exasolCurrentUserPrivileges.getUserIsAuthorizedForRolePrivs()) {
        this.roleGrantCache = new JDBCObjectSimpleCache<>(ExasolRoleGrant.class, "select r.*,p.ADMIN_OPTION,p.GRANTEE from EXA_DBA_ROLES r " + "INNER JOIN  EXA_DBA_ROLE_PRIVS p ON p.GRANTED_ROLE = r.ROLE_NAME ORDER BY P.GRANTEE,R.ROLE_NAME");
    }
}

Example 2 with JDBCSession

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCSession in project dbeaver by serge-rider.

the class ExasolUtils method generateDDLforTable.

@SuppressWarnings("rawtypes")
public static String generateDDLforTable(DBRProgressMonitor monitor, ExasolDataSource dataSource, ExasolTable exasolTable) throws DBException {
    StringBuilder ddlOutput = new StringBuilder();
    ddlOutput.append("CREATE TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" (");
    try (JDBCSession session = DBUtils.openMetaSession(monitor, dataSource, "Get Table DDL")) {
        try (JDBCStatement dbStat = session.createStatement()) {
            JDBCResultSet rs = dbStat.executeQuery(String.format(TABLE_QUERY_COLUMNS, quoteString(exasolTable.getSchema().getName()), quoteString(exasolTable.getName())));
            // column infos
            List<String> columns = new ArrayList<String>();
            // distribution key infos
            List<String> distKey = new ArrayList<String>();
            while (rs.next()) {
                StringBuilder columnString = new StringBuilder("");
                // double quotation mark for column as the name could be a
                // reserved word
                columnString.append("\n\t\t\"" + rs.getString("COLUMN_NAME") + "\" " + rs.getString("COLUMN_TYPE") + " ");
                // has default value?
                if (rs.getString("COLUMN_DEFAULT") != null)
                    columnString.append("DEFAULT " + rs.getString("COLUMN_DEFAULT") + " ");
                // has identity
                if (rs.getBigDecimal("COLUMN_IDENTITY") != null)
                    columnString.append("IDENTITY " + rs.getBigDecimal("COLUMN_IDENTITY").toString() + " ");
                // has identity
                if (!rs.getBoolean("COLUMN_IS_NULLABLE"))
                    columnString.append("NOT NULL ");
                // comment
                if (rs.getString("COLUMN_COMMENT") != null)
                    // replace ' to double ' -> escape for SQL
                    columnString.append("COMMENT IS '" + rs.getString("COLUMN_COMMENT").replaceAll("'", "''") + "'");
                // if distkey add column to distkey
                if (rs.getBoolean("COLUMN_IS_DISTRIBUTION_KEY"))
                    distKey.add(rs.getString("COLUMN_NAME"));
                columns.add(columnString.toString());
            }
            ddlOutput.append(CommonUtils.joinStrings(",", columns));
            // do we have a distkey?
            if (distKey.size() > 0) {
                ddlOutput.append(",\n\t\t DISTRIBUTE BY " + CommonUtils.joinStrings(",", distKey));
            }
            ddlOutput.append("\n);\n");
        }
        //primary key
        Collection<ExasolTableUniqueKey> pks = exasolTable.getConstraints(monitor);
        if (pks != null & pks.size() > 0) {
            //get only first as there is only 1 primary key
            ExasolTableUniqueKey pk = null;
            pk = pks.iterator().next();
            ArrayList<String> columns = new ArrayList<String>();
            for (DBSEntityAttributeRef c : pk.getAttributeReferences(monitor)) {
                columns.add("\"" + c.getAttribute().getName() + "\"");
            }
            ddlOutput.append("\nALTER TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" ADD CONSTRAINT " + pk.getName() + " PRIMARY KEY (" + CommonUtils.joinStrings(",", columns) + ") " + (pk.getEnabled() ? "ENABLE" : "") + " ;\n");
        }
        //foreign key
        Collection<ExasolTableForeignKey> fks = exasolTable.getAssociations(monitor);
        if (fks != null & fks.size() > 0) {
            //look keys
            for (ExasolTableForeignKey fk : fks) {
                ArrayList<String> columns = new ArrayList<String>();
                for (DBSEntityAttributeRef c : fk.getAttributeReferences(monitor)) {
                    columns.add("\"" + c.getAttribute().getName() + "\"");
                }
                ddlOutput.append("\nALTER TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" ADD CONSTRAINT " + fk.getName() + " FOREIGN KEY (" + CommonUtils.joinStrings(",", columns) + ") REFERENCES \"" + fk.getReferencedTable().getSchema().getName() + "\".\"" + fk.getReferencedTable().getName() + "\" " + (fk.getEnabled() ? "ENABLE" : "") + " ;\n");
            }
        }
        return ddlOutput.toString();
    } catch (SQLException e) {
        throw new DBException(e, dataSource);
    } finally {
        monitor.done();
    }
}

Example 3 with JDBCSession

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCSession in project dbeaver by serge-rider.

the class ExasolTable method read.

private void read(DBRProgressMonitor monitor) throws DBCException {
    JDBCSession session = DBUtils.openMetaSession(monitor, getDataSource(), "Read Table Details");
    try (JDBCStatement stmt = session.createStatement()) {
        String sql = String.format(readAdditionalInfo, ExasolUtils.quoteString(this.getSchema().getName()), ExasolUtils.quoteString(this.getName()), ExasolUtils.quoteString(this.getSchema().getName()), ExasolUtils.quoteString(this.getName()), ExasolUtils.quoteString(this.getSchema().getName()), ExasolUtils.quoteString(this.getName()));
        try (JDBCResultSet dbResult = stmt.executeQuery(sql)) {
            dbResult.next();
            this.hasDistKey = JDBCUtils.safeGetBoolean(dbResult, "TABLE_HAS_DISTRIBUTION_KEY");
            this.lastCommit = JDBCUtils.safeGetTimestamp(dbResult, "LAST_COMMIT");
            this.sizeRaw = JDBCUtils.safeGetLong(dbResult, "RAW_OBJECT_SIZE");
            this.sizeCompressed = JDBCUtils.safeGetLong(dbResult, "MEM_OBJECT_SIZE");
            this.deletePercentage = JDBCUtils.safeGetFloat(dbResult, "DELETE_PERCENTAGE");
            this.createTime = JDBCUtils.safeGetTimestamp(dbResult, "CREATED");
            this.hasRead = true;
        }
    } catch (SQLException e) {
        throw new DBCException(e, getDataSource());
    }
}

Example 4 with JDBCSession

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCSession in project dbeaver by serge-rider.

the class ExasolServerSessionManager method alterSession.

@Override
public void alterSession(DBCSession session, ExasolServerSession sessionType, Map<String, Object> options) throws DBException {
    try {
        String cmd = String.format(Boolean.TRUE.equals(options.get(PROP_KILL_QUERY)) ? KILL_STMT_CMD : KILL_APP_CMD, sessionType.getSessionID().toString());
        PreparedStatement dbStat = ((JDBCSession) session).prepareStatement(cmd);
        dbStat.execute();
    } catch (SQLException e) {
        throw new DBException(e, session.getDataSource());
    }
}

Example 5 with JDBCSession

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCSession in project dbeaver by serge-rider.

the class ExasolPlanAnalyser method explain.

public void explain(DBCSession session) throws DBCException {
    rootNodes = new ArrayList<>();
    JDBCSession connection = (JDBCSession) session;
    boolean oldAutoCommit = false;
    try {
        oldAutoCommit = connection.getAutoCommit();
        if (oldAutoCommit)
            connection.setAutoCommit(false);
        //alter session
        JDBCUtils.executeSQL(connection, "ALTER SESSION SET PROFILE = 'ON'");
        //execute query
        JDBCUtils.executeSQL(connection, query);
        //alter session
        JDBCUtils.executeSQL(connection, "ALTER SESSION SET PROFILE = 'OFF'");
        //rollback in case of DML
        connection.rollback();
        //alter session
        JDBCUtils.executeSQL(connection, "FLUSH STATISTICS");
        connection.commit();
        //retrieve execute info
        try (JDBCPreparedStatement stmt = connection.prepareStatement("SELECT * FROM EXA_USER_PROFILE_LAST_DAY WHERE SESSION_ID = CURRENT_SESSION AND STMT_ID = (select max(stmt_id) from EXA_USER_PROFILE_LAST_DAY where sql_text = ?)")) {
            stmt.setString(1, query);
            try (JDBCResultSet dbResult = stmt.executeQuery()) {
                while (dbResult.next()) {
                    ExasolPlanNode node = new ExasolPlanNode(null, dbResult);
                    rootNodes.add(node);
                }
            }
        }
    } catch (SQLException e) {
        throw new DBCException(e, session.getDataSource());
    } finally {
        //rollback changes because profile actually executes query and it could be INSERT/UPDATE
        try {
            connection.rollback();
            if (oldAutoCommit)
                connection.setAutoCommit(true);
        } catch (SQLException e) {
            LOG.error("Error closing plan analyser", e);
        }
    }
}