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);
}
}
}