PostgresqlDatabase.java [src/m/utils] Revision: default  Date:
package m.utils;

import csip.api.server.ServiceException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/

/**
 *
 * @author ktraff
 */
public class PostgresqlDatabase extends Database implements IDatabase {
    
    public PostgresqlDatabase(String hostname, String port, String dbName, String username, String password) throws ServiceException {
        super(hostname, port, dbName, username, password);
        createSchema(getDefaultSchema());
    }
    
    public PostgresqlDatabase(Connection conn) throws Exception {
        super(conn);
        String url = conn.getMetaData().getURL();
        Pattern pattern = Pattern.compile("jdbc:postgresql:\\/\\/(.+):(\\d+)\\/(.+)\\?(user|password)=(.+)&(user|password)=(.+)");
        Matcher matcher = pattern.matcher(url);
        matcher.matches();
        super.hostname = matcher.group(1);
        super.port = matcher.group(2);
        super.dbName = matcher.group(3);
        super.username = matcher.group(5);
        super.password = matcher.group(7);
    }
    
    @Override
    public void createSchema(String schemaName) throws ServiceException {
        try {
            Statement stmt = conn.createStatement();
            String sql = "CREATE SCHEMA IF NOT EXISTS " + schemaName;
            stmt.executeUpdate(sql);
            
            stmt.close();
        } catch (SQLException ex) {
            throw new csip.api.server.ServiceException(ex);
        }
    }
    
    @Override
    public Table createTable(String schemaName, String tableName) throws Exception {
        return createTable(schemaName, tableName, new ArrayList<String>());
    }
    
    @Override
    public Table createTable(String schemaName, String tableName, ArrayList<String> columns) throws Exception {
        Statement stmt = conn.createStatement();
        String schemaTable = schemaName + ".\"" + tableName + "\"";
        createSchema(schemaName);
        
        String sql = "CREATE TABLE IF NOT EXISTS " + schemaTable + "(" + LocalStringUtils.join(columns, ",") + ") ";
        LOG.log(Level.INFO, sql);
        stmt.executeUpdate(sql);
        
        stmt.close();
        
        return new Table(schemaName, tableName, this);
    }
    
    @Override
    public Table copyTable(Table tbl, String schemaName, String tableName) throws Exception {
        Statement stmt = conn.createStatement();
        String schemaTable = schemaName + ".\"" + tableName + "\"";
        String sql = "CREATE TABLE " + schemaTable + " AS TABLE " + tbl.getSchemaTable();
        LOG.log(Level.INFO, sql);
        stmt.executeUpdate(sql);

        stmt.close();
        
        return new Table(schemaName, tableName, this);
    }
    
    @Override
    public Table where(Table tbl, String expression) throws ServiceException {
        Table filteredTbl = null;
        try {
            String tableName = "where_" + getSessionID() + "_" + new RandomString(4).nextString();
            String schemaTable = getDefaultSchema() + ".\"" + tableName + "\"";
            Statement stmt = conn.createStatement();
            
            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                getProperties(tbl, false)
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " +
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + tbl.getSchemaTable() + " " +
                    "WHERE " + expression;
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);
            filteredTbl = new Table(getDefaultSchema(), tableName, this);
            
            stmt.close();
        } catch (SQLException ex) {
            throw new csip.api.server.ServiceException(ex);
        }
        return filteredTbl;
    }
    
    @Override
    public int deleteTable(String tableName) throws ServiceException {
        try {
            Statement stmt = conn.createStatement();
            String sql = "DROP TABLE IF EXISTS " + tableName;
            LOG.log(Level.INFO, sql);
            int ret = stmt.executeUpdate(sql);
            
            stmt.close();
            return ret;
        } catch (SQLException ex) {
            throw new csip.api.server.ServiceException(ex);
        }
    }
    
    @Override
    public boolean exists(String schema, String tableName) throws ServiceException {
        try {
            Statement stmt = conn.createStatement();
            String sql = "SELECT EXISTS ( " +
                    "SELECT 1 " +
                    "FROM   pg_catalog.pg_class c " +
                    "JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace " +
                    "WHERE  n.nspname = '" + schema + "' " +
                    "AND    c.relname = '" + tableName + "' " +
                    ")";
            LOG.log(Level.INFO, sql);
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            boolean exists = rs.getBoolean("exists");
            
            stmt.close();
            return exists;
        } catch (SQLException ex) {
            throw new csip.api.server.ServiceException(ex);
        }
    }
    
    @Override
    public void addColumn(String schema, String tableName, String columnName, DataType type) throws ServiceException {
        try {
            String sql = "ALTER TABLE " + schema + ".\"" + tableName + "\" ADD COLUMN " + columnName + " ";
            switch (type) {
                case NUMERIC:
                    sql += "numeric";
                    break;
                case STRING:
                    sql += "character varying";
                    break;
                default:
                    sql += "character varying";
                    break;
            }
            
            Statement stmt = conn.createStatement();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);
            stmt.close();
        } catch (SQLException ex) {
            throw new csip.api.server.ServiceException(ex);
        }
    }
    
    @Override
    public ArrayList<String> getColumns(String schema, String tableName) throws ServiceException {
        ArrayList<String> cols = new ArrayList<>();
        try {
            Statement stmt = conn.createStatement();
            String sql = "SELECT attrelid::regclass, attnum, attname " +
                    "FROM   pg_attribute " +
                    "WHERE  attrelid = '" + schema + ".\"" + tableName + "\"'::regclass " +
                    "AND    attnum > 0 " +
                    "AND    NOT attisdropped " +
                    "ORDER  BY attnum";
            
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                cols.add(rs.getString("attname"));
            }
            stmt.close();
        } catch (SQLException ex) {
            throw new csip.api.server.ServiceException(ex);
        }
        return cols;
    }
    
    public String getProperties(Table tbl, boolean useTableSuffix) throws ServiceException {
        ArrayList<String> colsExclude = new ArrayList<>(Arrays.asList(new String[]{
            "the_geom",
            "id",
        }));
        return getProperties(tbl, useTableSuffix, colsExclude);
    }
    
    public String getProperties(Table tbl, boolean useTableSuffix, ArrayList<String> colsExclude) throws ServiceException {
        ArrayList<String> props = new ArrayList<>();
        ArrayList<String> cols = getColumns(tbl.schema, tbl.table);
        for (String col : cols) {
            if (!colsExclude.contains(col)) {
                String prop = tbl.getSchemaTable() + ".\"" + col + "\" AS \"" + col;
                if (useTableSuffix && hasNumberedSuffix(tbl)) {
                    prop += "_" + getNumberedSuffix(tbl);
                }
                prop += "\"";
                props.add(prop);
            }
        }
        return LocalStringUtils.join(props, ",");
    }
    
    public boolean hasNumberedSuffix(Table tbl) {
        return RegularExpression.matches("_[0-9]{1,3}$", tbl.table);
    }
    
    public String getNumberedSuffix(Table tbl) {
        String res = "";
        Pattern ptn = Pattern.compile("_([0-9]{1,3}$)");
        Matcher mchr = ptn.matcher(tbl.table);
        if (mchr.find()) {
            res = mchr.group(1);
        }
        return res;
    }
    
    @Override
    public JSONObject toJSON(Table tbl) throws ServiceException {
        try {
            LOG.log(Level.INFO, "exporting " + tbl.toString() + " to JSON");
            Statement stmt = conn.createStatement();
            ArrayList<String> cols = getColumns(tbl.schema, tbl.table);
            LOG.log(Level.INFO, "Cols to export: " + cols);
            JSONObject json = new JSONObject();
            JSONArray rows = new JSONArray();
            
            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                getProperties(tbl, false, new ArrayList<String>()),
            }));
            String sql = "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + tbl.getSchemaTable() + " ";
            LOG.log(Level.INFO, sql);
            ResultSet rs = stmt.executeQuery(sql);
            
            while (rs.next()) {
                JSONObject jsonProps = new JSONObject();
                for (String col : cols) {
                    jsonProps.put(col, rs.getObject(col));
                }
                rows.put(jsonProps);
            }
            
            stmt.close();
            json.put("rows", rows);
            return json;
        } catch (SQLException|JSONException ex) {
            throw new csip.api.server.ServiceException(ex);
        }
    }
    
}