|
0
|
1 /* Copyright (c) 2012 Tomislav Gountchev <tomi@gountchev.net> */
|
|
|
2
|
|
|
3 package jdbcpgbackup;
|
|
|
4
|
|
|
5 import java.sql.Connection;
|
|
|
6 import java.sql.PreparedStatement;
|
|
|
7 import java.sql.ResultSet;
|
|
|
8 import java.sql.SQLException;
|
|
|
9 import java.util.ArrayList;
|
|
|
10 import java.util.List;
|
|
|
11
|
|
|
12 final class Constraint extends DbBackupObject {
|
|
|
13
|
|
|
14 static class ConstraintFactory implements DBOFactory<Constraint> {
|
|
|
15
|
|
|
16 @Override
|
|
|
17 public Iterable<Constraint> getDbBackupObjects(Connection con, Schema schema) throws SQLException {
|
|
|
18 List<Constraint> constraints = new ArrayList<Constraint>();
|
|
|
19 PreparedStatement stmt = null;
|
|
|
20 try {
|
|
|
21 stmt = con.prepareStatement(
|
|
|
22 "SELECT c.oid, c.conname, p.relname, pg_get_userbyid(p.relowner) AS owner, " +
|
|
|
23 "pg_get_constraintdef(c.oid) AS constraintdef, c.contype " +
|
|
|
24 "FROM pg_constraint c, pg_class p " +
|
|
|
25 "WHERE c.connamespace = ? " +
|
|
|
26 "AND c.conrelid = p.oid " +
|
|
|
27 "ORDER BY c.contype DESC");
|
|
|
28 stmt.setInt(1, schema.getOid());
|
|
|
29 ResultSet rs = stmt.executeQuery();
|
|
|
30 while (rs.next()) {
|
|
|
31 constraints.add(new Constraint(rs.getString("conname"), schema, rs.getString("relname"),
|
|
|
32 rs.getString("owner"), rs.getString("constraintdef"), rs.getString("contype").charAt(0)));
|
|
|
33 }
|
|
|
34 rs.close();
|
|
|
35 } finally {
|
|
|
36 if (stmt != null) stmt.close();
|
|
|
37 }
|
|
|
38 return constraints;
|
|
|
39 }
|
|
|
40
|
|
|
41 @Override
|
|
|
42 public Constraint getDbBackupObject(Connection con, String constraintName, Schema schema) throws SQLException {
|
|
|
43 Constraint constraint = null;
|
|
|
44 PreparedStatement stmt = null;
|
|
|
45 try {
|
|
|
46 stmt = con.prepareStatement(
|
|
|
47 "SELECT c.oid, c.conname, p.relname, pg_get_userbyid(p.relowner) AS owner, " +
|
|
|
48 "pg_get_constraintdef(c.oid) AS constraintdef, c.contype " +
|
|
|
49 "FROM pg_constraint c, pg_class p " +
|
|
|
50 "WHERE c.connamespace = ? " +
|
|
|
51 "AND c.conrelid = p.oid " +
|
|
|
52 "AND c.conname = ? ");
|
|
|
53 stmt.setInt(1, schema.getOid());
|
|
|
54 stmt.setString(2, constraintName);
|
|
|
55 ResultSet rs = stmt.executeQuery();
|
|
|
56 if (rs.next()) {
|
|
|
57 constraint = new Constraint(constraintName, schema, rs.getString("relname"),
|
|
|
58 rs.getString("owner"), rs.getString("constraintdef"), rs.getString("contype").charAt(0));
|
|
|
59 } else {
|
|
|
60 throw new RuntimeException("no such constraint: " + constraintName);
|
|
|
61 }
|
|
|
62 rs.close();
|
|
|
63 } finally {
|
|
|
64 if (stmt != null) stmt.close();
|
|
|
65 }
|
|
|
66 return constraint;
|
|
|
67 }
|
|
|
68
|
|
|
69 }
|
|
|
70
|
|
|
71 static class CachingConstraintFactory extends CachingDBOFactory<Constraint> {
|
|
|
72
|
|
|
73 private final Table.CachingTableFactory tableFactory;
|
|
|
74
|
|
|
75 protected CachingConstraintFactory(Schema.CachingSchemaFactory schemaFactory, Table.CachingTableFactory tableFactory) {
|
|
|
76 super(schemaFactory);
|
|
|
77 this.tableFactory = tableFactory;
|
|
|
78 }
|
|
|
79
|
|
|
80 @Override
|
|
|
81 protected PreparedStatement getAllStatement(Connection con) throws SQLException {
|
|
|
82 return con.prepareStatement(
|
|
|
83 "SELECT c.oid, c.conname, c.conrelid AS table_oid, " +
|
|
|
84 "pg_get_constraintdef(c.oid) AS constraintdef, " +
|
|
|
85 "c.connamespace AS schema_oid, c.contype " +
|
|
|
86 "FROM pg_constraint c");
|
|
|
87 }
|
|
|
88
|
|
|
89 @Override
|
|
|
90 protected Constraint newDbBackupObject(Connection con, ResultSet rs, Schema schema) throws SQLException {
|
|
|
91 Table table = tableFactory.getTable(rs.getInt("table_oid"));
|
|
|
92 return new Constraint(rs.getString("conname"), schema, table.getName(),
|
|
|
93 table.getOwner(), rs.getString("constraintdef"), rs.getString("contype").charAt(0));
|
|
|
94 }
|
|
|
95
|
|
|
96 // get the primary key constraints before the rest
|
|
|
97 @Override
|
|
|
98 public final Iterable<Constraint> getDbBackupObjects(Connection con, Schema schema) throws SQLException {
|
|
|
99 Iterable<Constraint> constraints = super.getDbBackupObjects(con, schema);
|
|
|
100 List<Constraint> pklist = new ArrayList<Constraint>();
|
|
|
101 List<Constraint> fklist = new ArrayList<Constraint>();
|
|
|
102 for (Constraint constraint : constraints) {
|
|
|
103 if (constraint.type == 'p') pklist.add(constraint);
|
|
|
104 else fklist.add(constraint);
|
|
|
105 }
|
|
|
106 pklist.addAll(fklist);
|
|
|
107 return pklist;
|
|
|
108 }
|
|
|
109
|
|
|
110 }
|
|
|
111
|
|
|
112 private final String tableName;
|
|
|
113 private final String definition;
|
|
|
114 private final char type;
|
|
|
115
|
|
|
116 private Constraint(String name, Schema schema, String tableName, String tableOwner, String definition, char type) {
|
|
|
117 super(name, schema, tableOwner);
|
|
|
118 this.tableName = tableName;
|
|
|
119 this.definition = definition.replace(" REFERENCES " + schema.getName() + ".", " REFERENCES "); // remove schema name
|
|
|
120 this.type = type;
|
|
|
121 }
|
|
|
122
|
|
|
123 @Override
|
|
|
124 protected StringBuilder appendCreateSql(StringBuilder buf) {
|
|
|
125 buf.append("ALTER TABLE ");
|
|
|
126 buf.append(tableName);
|
|
|
127 buf.append(" ADD CONSTRAINT ");
|
|
|
128 buf.append(name);
|
|
|
129 buf.append(" ");
|
|
|
130 buf.append(definition);
|
|
|
131 buf.append(" ;\n");
|
|
|
132 return buf;
|
|
|
133 }
|
|
|
134
|
|
|
135 }
|
