annotate src/jdbcpgbackup/Schema.java @ 33:442ace8fd8ed

global.schema fix
author Franklin Schmidt <fschmidt@gmail.com>
date Mon, 06 Jul 2020 22:29:03 -0600
parents 7ecd1a4ef557
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
1 /* Copyright (c) 2012 Tomislav Gountchev <tomi@gountchev.net> */
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
2
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
3 package jdbcpgbackup;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
4
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
5 import java.sql.Connection;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
6 import java.sql.PreparedStatement;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
7 import java.sql.ResultSet;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
8 import java.sql.SQLException;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
9 import java.util.ArrayList;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
10 import java.util.Collection;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
11 import java.util.Collections;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
12 import java.util.HashMap;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
13 import java.util.Iterator;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
14 import java.util.List;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
15 import java.util.Map;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
16
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
17 final class Schema extends DbBackupObject {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
18
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
19 static class SchemaFactory implements DBOFactory<Schema> {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
20
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
21 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
22 public List<Schema> getDbBackupObjects(Connection con, Schema ignored) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
23 List<Schema> schemas = new ArrayList<Schema>();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
24 PreparedStatement stmt = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
25 try {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
26 stmt = con.prepareStatement(
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
27 "SELECT nspname, pg_get_userbyid(nspowner) AS owner, oid FROM pg_namespace " +
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
28 "WHERE nspname NOT LIKE 'pg_%' " +
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
29 "AND nspname <> 'information_schema'");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
30 ResultSet rs = stmt.executeQuery();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
31 while (rs.next()) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
32 schemas.add(new Schema(rs.getString("nspname"), rs.getString("owner"), rs.getInt("oid")));
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
33 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
34 rs.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
35 } finally {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
36 if (stmt != null) stmt.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
37 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
38 return schemas;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
39 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
40
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
41 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
42 public Schema getDbBackupObject(Connection con, String schemaName, Schema ignored) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
43 PreparedStatement stmt = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
44 Schema schema = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
45 try {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
46 stmt = con.prepareStatement(
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
47 "SELECT pg_get_userbyid(nspowner) AS owner, oid FROM pg_namespace WHERE nspname = ?");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
48 stmt.setString(1, schemaName);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
49 ResultSet rs = stmt.executeQuery();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
50 if (rs.next())
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
51 schema = new Schema(schemaName, rs.getString("owner"), rs.getInt("oid"));
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
52 rs.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
53 } finally {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
54 if (stmt != null) stmt.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
55 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
56 return schema;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
57 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
58
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
59 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
60
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
61 static class CachingSchemaFactory implements DBOFactory<Schema> { // does not extend CachingDBOFactory
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
62
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
63 protected Map<String,Schema> map = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
64 private final Map<Integer,Schema> batch = new HashMap<Integer,Schema>();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
65 private Iterator<Schema> itr;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
66
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
67 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
68 public Collection<Schema> getDbBackupObjects(Connection con, Schema ignored) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
69 if (map == null) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
70 loadMap(con);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
71 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
72 return Collections.unmodifiableCollection(map.values());
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
73 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
74
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
75 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
76 public Schema getDbBackupObject(Connection con, String name, Schema ignored) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
77 if (map == null) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
78 loadMap(con);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
79 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
80 return map.get(name);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
81 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
82
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
83 public Collection<Schema> nextBatch(Connection con, int batchSize) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
84 if (itr == null) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
85 itr = getDbBackupObjects(con, null).iterator();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
86 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
87 batch.clear();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
88 while (itr.hasNext() && batch.size() < batchSize) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
89 Schema schema = itr.next();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
90 batch.put(schema.getOid(), schema);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
91 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
92 return Collections.unmodifiableCollection(batch.values());
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
93 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
94
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
95 public Schema getFromCurrentBatch(int oid) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
96 return batch.get(oid);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
97 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
98
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
99 private void loadMap(Connection con) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
100 map = new HashMap<String,Schema>();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
101 PreparedStatement stmt = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
102 try {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
103 stmt = con.prepareStatement(
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
104 "SELECT nspname AS schemaname, pg_get_userbyid(nspowner) AS owner, oid FROM pg_namespace " +
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
105 "WHERE nspname NOT LIKE 'pg_%' " +
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
106 "AND nspname <> 'information_schema'");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
107 ResultSet rs = stmt.executeQuery();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
108 while (rs.next()) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
109 String schemaName = rs.getString("schemaname");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
110 map.put(schemaName, new Schema(rs.getString("schemaname"), rs.getString("owner"), rs.getInt("oid")));
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
111 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
112 rs.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
113 } finally {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
114 if (stmt != null) stmt.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
115 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
116 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
117
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
118 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
119
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
120 static Schema createSchema(Connection con, String schemaName, String owner, DBOFactory<Schema> schemaFactory) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
121 PreparedStatement stmt = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
122 try {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
123 stmt = con.prepareStatement(
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
124 "CREATE SCHEMA " + schemaName + " AUTHORIZATION " + owner);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
125 stmt.executeUpdate();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
126 } finally {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
127 if (stmt != null) stmt.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
128 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
129 return schemaFactory.getDbBackupObject(con, schemaName, null);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
130 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
131
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
132 private final int oid;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
133
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
134 private Schema(String name, String owner, int oid) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
135 super(name, null, owner);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
136 this.oid = oid;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
137 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
138
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
139 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
140 String getSql(DataFilter dataFilter) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
141 return "CREATE SCHEMA " + name + " AUTHORIZATION " + owner + " ;\n";
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
142 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
143
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
144 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
145 protected StringBuilder appendCreateSql(StringBuilder buf) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
146 throw new UnsupportedOperationException();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
147 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
148
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
149 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
150 String getFullname() {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
151 return name;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
152 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
153
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
154 int getOid() {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
155 return oid;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
156 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
157 }