org.nuiton.topia.persistence.jdbc.JdbcPostgresHelper Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of topia-extension Show documentation
ObServe ToPIA extension module
The newest version!
package org.nuiton.topia.persistence.jdbc;

/*-
 * #%L
 * ObServe Toolkit :: ToPIA Extension
 * %%
 * Copyright (C) 2017 - 2018 IRD, Ultreia.io
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public
 * License along with this program.  If not, see
 * .
 * #L%
 */

import com.google.common.collect.Sets;
import org.apache.commons.lang3.tuple.Pair;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.nuiton.topia.persistence.TopiaException;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

/**
 * Created by tchemit on 13/05/2018.
 *
 * @author Tony Chemit - [email protected]
 */
public class JdbcPostgresHelper extends JdbcHelper2 {

    private static final Log log = LogFactory.getLog(JdbcPostgresHelper.class);
    private static final Set POSTGIS_TABLES = Sets.newHashSet("geometry_columns", "spatial_ref_sys");

    public JdbcPostgresHelper(JdbcConfiguration jdbcConfiguration) {
        super(jdbcConfiguration);
    }

    public Set getTablePrivileges(String schema, String tableName) {
        try {
            try (Connection connection = openConnection()) {
                DatabaseMetaData metaData = connection.getMetaData();
                try (ResultSet observerDataPrivilege = metaData.getTablePrivileges(null, schema, tableName)) {

                    Set tablePrivileges = new LinkedHashSet<>();

                    while (observerDataPrivilege.next()) {
                        String security = observerDataPrivilege.getString("PRIVILEGE");
                        String grantor = observerDataPrivilege.getString("GRANTOR");
                        String grantee = observerDataPrivilege.getString("GRANTEE");
                        // Il se peut que le login soit echappe sous la forme \"login\"
                        grantor = grantor.replaceAll("\\\\\"", "");
                        grantee = grantee.replaceAll("\\\\\"", "");
                        log.debug(String.format("(security %s) - grantee (%s)", security, grantee));
                        if (grantor.equals(jdbcConfiguration.getJdbcConnectionUser())) {
                            log.debug("for " + tableName + " table " + grantor + '/' + security);
                            tablePrivileges.add(security);
                        } else if (grantee.equals(jdbcConfiguration.getJdbcConnectionUser())) {
                            log.debug("for " + tableName + " table " + grantee + '/' + security);
                            tablePrivileges.add(security);
                        }
                    }
                    return tablePrivileges;
                }
            }
        } catch (Exception e) {
            throw new TopiaException("Can't get postgres roles", e);
        }

    }

    public Set getRoles() {
        Set users = new LinkedHashSet<>();

        try {
            try (Connection connection = openConnection()) {
                // la connexion est reussie, on recherche les droits de récupération de la version de la base
                try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT rolname FROM pg_catalog.pg_roles;")) {
                    ResultSet resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        String name = resultSet.getString(1);
                        users.add(name);
                    }
                }
            }
            return users;
        } catch (Exception e) {
            throw new TopiaException(e);
        }
    }

    public List> getTables(Set schemas, Set extraTables) {
        List> result = new ArrayList<>();


            try (Connection connection = openConnection()) {
                DatabaseMetaData data = connection.getMetaData();
                try (ResultSet tables = data.getTables(null, null, null, new String[]{"TABLE"})) {

                    int columnCount = tables.getMetaData().getColumnCount();

                    if (log.isDebugEnabled()) {
                        StringBuilder builder = new StringBuilder();
                        builder.append("\nheader");
                        for (int i = 1; i <= columnCount; i++) {
                            String columnName = tables.getMetaData().getColumnName(i);
                            builder.append("\n [").append(i).append("] :").append(columnName);
                        }
                        log.debug(builder.toString());
                        log.debug("fetchSize : " + tables.getFetchSize());
                    }
                    while (tables.next()) {
                        String schemaName = tables.getString(2);
                        String tableName = tables.getString(3);
                        if (log.isDebugEnabled()) {
                            log.debug(String.format("Discover table named %s", tables));
                        }
                        if (!extraTables.contains(tableName)) {
                            if (POSTGIS_TABLES.contains(tableName)) {
                                continue;
                            }
                            if (schemaName == null || !schemas.contains(schemaName.toLowerCase())) {
                                continue;
                            }
                        }
                        log.debug(String.format("Keep table: %s", tables));
                        result.add(Pair.of(schemaName, tableName));
                    }
                }
            Collections.sort(result);
            return result;
        } catch (SQLException e) {
            throw new TopiaException(e);
        }
    }

    public Set getPostgisFunctions(String functionPattern) {

        String sql = String.format("SELECT ns.nspname::text || '.' ||  p.proname::text || '(' || oidvectortypes(p.proargtypes)::text || ')'" +
                " FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)" +
                " WHERE ns.nspname = 'public' AND p.proname ILIKE '%s%%';", functionPattern);
        try {
            Set result = new LinkedHashSet<>();
            try (Connection connection = openConnection()) {
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                try (ResultSet set = preparedStatement.executeQuery()) {
                    while (set.next()) {
                        String functionPrototype = set.getString(1);
                        result.add(functionPrototype);
                    }
                }
            }
            return result;
        } catch (SQLException e) {
            throw new TopiaException(e);
        }

    }

}