All Downloads are FREE. Search and download functionalities are using the official Maven repository.

org.n52.sos.ds.datasource.AbstractPostgresDatasource Maven / Gradle / Ivy

Go to download 

/*
 * Copyright (C) 2012-2019 52°North Initiative for Geospatial Open Source
 * Software GmbH
 *
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License version 2 as published
 * by the Free Software Foundation.
 *
 * If the program is linked with libraries which are licensed under one of
 * the following licenses, the combination of the program with the linked
 * library is not considered a "derivative work" of the program:
 *
 *     - Apache License, version 2.0
 *     - Apache Software License, version 1.0
 *     - GNU Lesser General Public License, version 3
 *     - Mozilla Public License, versions 1.0, 1.1 and 2.0
 *     - Common Development and Distribution License (CDDL), version 1.0
 *
 * Therefore the distribution of the program linked with libraries licensed
 * under the aforementioned licenses, is permitted by the copyright holders
 * if the distribution is compliant with both the GNU General Public
 * License version 2 and the aforementioned licenses.
 *
 * 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.
 */
package org.n52.sos.ds.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.hibernate.boot.Metadata;
import org.hibernate.dialect.Dialect;
import org.hibernate.mapping.Table;
import org.n52.faroe.ConfigurationError;
import org.n52.hibernate.spatial.dialect.postgis.TimestampWithTimeZonePostgisPG95Dialect;
import org.n52.sos.ds.hibernate.util.HibernateConstants;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;

/**
 * @since 4.0.0
 *
 */
public abstract class AbstractPostgresDatasource
        extends AbstractHibernateFullDBDatasource {

    private static final Logger LOGGER = LoggerFactory.getLogger(AbstractPostgresDatasource.class);

    protected static final String POSTGRES_DRIVER_CLASS = "org.postgresql.Driver";

    protected static final Pattern JDBC_URL_PATTERN = Pattern.compile("^jdbc:postgresql://([^:]+):([0-9]+)/(.*)$");

    protected static final String USERNAME_DESCRIPTION =
            "Your database server user name. The default value for PostgreSQL is \"postgres\".";

    protected static final String USERNAME_DEFAULT_VALUE = "postgres";

    protected static final String PASSWORD_DESCRIPTION =
            "Your database server password. The default value is \"postgres\".";

    protected static final String PASSWORD_DEFAULT_VALUE = "postgres";

    protected static final String HOST_DESCRIPTION =
            "Set this to the IP/net location of PostgreSQL database server. The default value for PostgreSQL is \"localhost\".";

    protected static final String PORT_DESCRIPTION =
            "Set this to the port number of your PostgreSQL server. The default value for PostgreSQL is 5432.";

    protected static final int PORT_DEFAULT_VALUE = 5432;

    // public static final String CATALOG_DEFAULT_VALUE = "public";

    protected static final String SCHEMA_DEFAULT_VALUE = "public";

    protected static final String FUNC_POSTGIS_VERSION = "postgis_version()";

    protected static final String TAB_SPATIAL_REF_SYS = "spatial_ref_sys";

    public AbstractPostgresDatasource() {
        super();
        setUsernameDefault(USERNAME_DEFAULT_VALUE);
        setUsernameDescription(USERNAME_DESCRIPTION);
        setPasswordDefault(PASSWORD_DEFAULT_VALUE);
        setPasswordDescription(PASSWORD_DESCRIPTION);
        setDatabaseDefault(DATABASE_DEFAULT_VALUE);
        setDatabaseDescription(DATABASE_DESCRIPTION);
        setHostDefault(HOST_DEFAULT_VALUE);
        setHostDescription(HOST_DESCRIPTION);
        setPortDefault(PORT_DEFAULT_VALUE);
        setPortDescription(PORT_DESCRIPTION);
        setSchemaDefault(SCHEMA_DEFAULT_VALUE);
        setSchemaDescription(SCHEMA_DESCRIPTION);
    }

    @Override
    protected Dialect createDialect() {
        return new TimestampWithTimeZonePostgisPG95Dialect();
    }

    @Override
    protected String getDriverClass() {
        return POSTGRES_DRIVER_CLASS;
    }

    @Override
    public boolean checkSchemaCreation(Map settings) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = openConnection(settings);
            stmt = conn.createStatement();
            final String schema = (String) settings.get(createSchemaDefinition().getKey());
            final String schemaPrefix = schema == null ? "" : "\"" + schema + "\".";
            final String testTable = schemaPrefix + "sos_installer_test_table";
            final String command = String.format("BEGIN; " + "DROP TABLE IF EXISTS %1$s; "
                    + "CREATE TABLE %1$s (id integer NOT NULL); " + "DROP TABLE %1$s; " + "END;", testTable);
            stmt.execute(command);
            return true;
        } catch (SQLException e) {
            return false;
        } finally {
            close(stmt);
            close(conn);
        }
    }

    @Override
    protected void validatePrerequisites(Connection con, Metadata metadata, Map settings) {
        checkPostgis(con, settings);
        checkSpatialRefSys(con, metadata, settings);
    }

    protected void checkPostgis(Connection con, Map settings) {
        Statement stmt = null;
        try {
            StringBuilder builder = new StringBuilder();
            builder.append(SELECT);
            builder.append(' ');
            builder.append(FUNC_POSTGIS_VERSION);
            builder.append(';');
            stmt = con.createStatement();
            stmt.execute(builder.toString());
            // TODO check PostGIS version
        } catch (SQLException ex) {
            throw new ConfigurationError("PostGIS does not seem to be installed.", ex);
        } finally {
            close(stmt);
        }
    }

    protected void checkSpatialRefSys(Connection con, Metadata metadata, Map settings) {
        Statement stmt = null;
        try {
//            if (!metadata.isTable("spatial_ref_sys")) {
//                throw new ConfigurationError("Missing 'spatial_ref_sys' table.");
//            }
            StringBuilder builder = new StringBuilder();
            builder.append(SELECT);
            builder.append(' ');
            builder.append(DEFAULT_COUNT);
            builder.append(' ');
            builder.append(FROM);
            builder.append(' ');
            builder.append(TAB_SPATIAL_REF_SYS);
            builder.append(';');
            stmt = con.createStatement();
            stmt.execute(builder.toString());
        } catch (SQLException ex) {
            throw new ConfigurationError("Can not read from table 'spatial_ref_sys'", ex);
        } finally {
            close(stmt);
        }
    }

    @Override
    protected String toURL(Map settings) {
        String url = String.format("jdbc:postgresql://%s:%d/%s", settings.get(HOST_KEY), settings.get(PORT_KEY),
                settings.get(DATABASE_KEY));
        return url;
    }

    @Override
    protected String[] parseURL(String url) {
        Matcher matcher = JDBC_URL_PATTERN.matcher(url);
        matcher.find();
        return new String[] { matcher.group(1), matcher.group(2), matcher.group(3) };
    }

    @Override
    public boolean supportsClear() {
        return true;
    }

    @Override
    public void clear(Properties properties) {
        Map settings = parseDatasourceProperties(properties);
        CustomConfiguration config = getConfig(settings);
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = openConnection(settings);
            String catalog = checkCatalog(conn);
            String schema = checkSchema((String) settings.get(SCHEMA_KEY), catalog, conn);
            Iterator tables = getMetadata(conn, settings).collectTableMappings().iterator();
            List names = new LinkedList();
            while (tables.hasNext()) {
                Table table = tables.next();
                if (table.isPhysicalTable()) {
                    names.add(table.getQualifiedName(createDialect(), null, schema));
                }
            }
            if (!names.isEmpty()) {
                stmt = conn.createStatement();
                stmt.execute(String.format("truncate %s restart identity cascade", Joiner.on(", ").join(names)));
            }
        } catch (SQLException ex) {
            throw new ConfigurationError(ex);
        } finally {
            close(stmt);
            close(conn);
        }
    }

    @Override
    protected Connection openConnection(Map settings) throws SQLException {
        try {
            Class.forName(getDriverClass());
            String jdbc = toURL(settings);
            String pass = (String) settings.get(HibernateConstants.CONNECTION_PASSWORD);
            String user = (String) settings.get(HibernateConstants.CONNECTION_USERNAME);
            precheckDriver(jdbc, user, pass);
            return DriverManager.getConnection(jdbc, user, pass);
        } catch (ClassNotFoundException ex) {
            throw new SQLException(ex);
        }
    }

    @Override
    protected String[] checkDropSchema(String[] dropSchema) {
        List checkedSchema = Lists.newLinkedList();
        for (String string : dropSchema) {
            if (!string.startsWith("alter")) {
                checkedSchema.add(string);
            }
        }
        return checkScriptForGeneratedAndDuplicatedEntries(checkedSchema.toArray(new String[checkedSchema.size()]));
    }

    @Override
    public Properties getDatasourceProperties(Map settings) {
        Properties p = super.getDatasourceProperties(settings);
        p.put(HibernateConstants.C3P0_PREFERRED_TEST_QUERY, "SELECT 1");
        return p;
    }

}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy