private String getRangeCode(int item) throws SQLException {
String result = "";
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.TYPE);
PreparedStatement stmt = conn.prepareStatement(RANGE_SOURCE);
stmt.setInt(1, item);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
result = "CREATE TYPE " + name + " AS RANGE (\n";
result = result + "\tSUBTYPE =" + rs.getString("subtype") + "\n";
if (rs.getString("opc") != null)
result = result + "\t, SUBTYPE_OPCLASS = " + rs.getString("opc") + "\n";
if (rs.getString("collation") != null)
result = result + "\t, COLLATION = " + rs.getString("collation") + "\n";
if (rs.getString("canonical") != null)
result = result + "\t, CANONICAL = " + rs.getString("canonical") + "\n";
if (rs.getString("subdiff") != null)
result = result + "\t, SUBTYPE_DIFF = " + rs.getString("subdiff") + "\n";
result = result + ")";
}
return result;
}
private String getSequenceCode(int item) throws SQLException {
String result = "";
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.TABLE);
String fromClause = name;
String sql = SEQUENCE_SOURCE + fromClause;
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
result = "CREATE SEQUENCE " + name + " AS\n";
result = result + "\t INCREMENT " + Long.toString(rs.getLong(6)) + "\n";
result = result + "\t MINVALUE " + Long.toString(rs.getLong(2)) + "\n";
result = result + "\t MAXVALUE " + Long.toString(rs.getLong(3)) + "\n";
result = result + "\t START " + Long.toString(rs.getLong(1)) + "\n";
result = result + "\t CACHE " + Long.toString(rs.getLong(4)) + "\n";
result = result + "\t ";
if (!rs.getBoolean(5)) {
result = result + "NO ";
}
result = result + "CYCLE\n";
}
return result;
}
private String getFunctionCode(int item) throws SQLException {
String result = "";
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.FUNCTION);
PreparedStatement stmt = conn.prepareStatement(FUNC_SOURCE);
stmt.setInt(1, item);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
String src = rs.getString(1);
String lang = rs.getString(2);
String ret = rs.getString(3);
result = "CREATE FUNCTION " + name;
result = result + " RETURNS " + ret;
result = result + " AS $$ ";
result = result + src;
result = result + "$$ LANGUAGE " + lang + ";";
}
return result;
}
private String getEnumCode(int item) throws SQLException {
String result = "";
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.TYPE);
PreparedStatement stmt = conn.prepareStatement(ENUM_SOURCE);
stmt.setInt(1, item);
ResultSet rs = stmt.executeQuery();
String enums = "";
while (rs.next()) {
enums = enums + "'" + rs.getString(1) + "',";
}
if (!enums.equals("")) {
// trim off the last ,
enums = enums.substring(0, enums.length() - 1);
result = "CREATE TYPE " + name + " AS ENUM \n";
result = result + "\t(" + enums + ")";
}
return result;
}
private String getCompositeCode(int item) throws SQLException {
String result = "";
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.TABLE);
PreparedStatement stmt = conn.prepareStatement(COMPOSITE_SOURCE);
stmt.setInt(1, item);
ResultSet rs = stmt.executeQuery();
String atts = "";
while (rs.next()) {
atts = atts + "\t" + rs.getString(1) + "\t" + rs.getString(2) + ",\n";
}
if (!atts.equals("")) {
// trim off the last ,\n
atts = atts.substring(0, atts.length() - 2);
result = "CREATE TYPE " + name + " AS ( \n";
result = result + atts + "\n)";
}
return result;
}
private String getTableCode(int item) throws SQLException {
String result = "";
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.TABLE);
PreparedStatement stmt = conn.prepareStatement(COLUMN_SOURCE);
stmt.setString(1, "r");
stmt.setInt(2, item);
ResultSet rs = stmt.executeQuery();
result = "CREATE TABLE " + name + " (\n";
String columns = "";
while (rs.next()) {
columns = columns + "\t " + rs.getString(1);
columns = columns + "\t " + rs.getString(2);
if (rs.getBoolean(3)) {
columns = columns + "\t NOT NULL";
}
String constraintDef = rs.getString(4);
if (constraintDef != null) {
if (constraintDef.toUpperCase().startsWith("PRIMARY KEY")) {
columns = columns + "\t " + "PRIMARY KEY";
} else if (constraintDef.toUpperCase().startsWith("UNIQUE")) {
columns = columns + "\t " + "UNIQUE";
} else if (constraintDef.toUpperCase().startsWith("FOREIGN KEY")) {
columns =
columns
+ "\t "
+ constraintDef.substring(constraintDef.toUpperCase().indexOf("REFERENCES"));
} else {
columns = columns + "\t " + constraintDef;
}
}
columns = columns + ",\n";
}
result = result + columns.substring(0, columns.lastIndexOf(",")) + "\n)";
return result;
}
private String getViewCode(int item) throws SQLException {
String result = "";
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.VIEW);
PreparedStatement stmt = conn.prepareStatement(VIEW_SOURCE);
stmt.setInt(1, item);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
result = "CREATE VIEW " + name + " AS\n";
result = result + rs.getString(1);
}
return result;
}
private String getDomainCode(int item) throws SQLException {
String result = "";
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.TYPE);
PreparedStatement stmt = conn.prepareStatement(DOMAIN_SOURCE);
stmt.setInt(1, item);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
result = "CREATE DOMAIN " + name + " AS " + rs.getString("base_type") + "\n";
if (rs.getString("typdefault") != null)
result = result + "\tDEFAULT " + rs.getString("typdefault") + "\n";
if (rs.getBoolean("typnotnull")) result = result + "\tNOT NULL\n";
result = result + "\t" + rs.getString("check");
}
return result;
}
private String getForeignTableCode(int item) throws SQLException {
String result = "";
PreparedStatement stmt = null;
try {
Database db = new Database(conn);
String name = db.getItemFullName(item, ITEM_TYPE.FOREIGN_TABLE);
stmt = conn.prepareStatement(COLUMN_SOURCE);
stmt.setString(1, "f");
stmt.setInt(2, item);
ResultSet rs = stmt.executeQuery();
result = "CREATE FOREIGN TABLE " + name + " (\n";
String columns = "";
while (rs.next()) {
columns = columns + "\t " + rs.getString(1);
columns = columns + "\t " + rs.getString(2);
if (rs.getBoolean(3)) {
columns = columns + "\t NOT NULL";
}
String constraintDef = rs.getString(4);
if (constraintDef != null) {
if (constraintDef.toUpperCase().startsWith("PRIMARY KEY")) {
columns = columns + "\t " + "PRIMARY KEY";
} else if (constraintDef.toUpperCase().startsWith("UNIQUE")) {
columns = columns + "\t " + "UNIQUE";
} else if (constraintDef.toUpperCase().startsWith("FOREIGN KEY")) {
columns =
columns
+ "\t "
+ constraintDef.substring(constraintDef.toUpperCase().indexOf("REFERENCES"));
} else {
columns = columns + "\t " + constraintDef;
}
}
columns = columns + ",\n";
}
result = result + columns.substring(0, columns.lastIndexOf(",")) + "\n)";
stmt.close();
stmt = conn.prepareStatement(GET_FOREIGN_SERVER);
stmt.setInt(1, item);
rs = stmt.executeQuery();
if (rs.next()) {
result = result + "\nSERVER " + rs.getString(1);
}
stmt.close();
stmt = conn.prepareStatement(GET_OPTIONS);
stmt.setInt(1, item);
rs = stmt.executeQuery();
String options = "";
while (rs.next()) {
if (!options.equals("")) options = options + ", ";
String option = rs.getString("option");
options = options + option.split("=")[0] + " '" + option.split("=")[1] + "'";
}
if (!options.equals("")) {
result = result + "\nOPTIONS (" + options + ")";
}
} finally {
if (stmt != null) stmt.close();
}
return result;
}
