annotate src/jdbcpgbackup/Sequence.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.List;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
11
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
12 final class Sequence extends DbBackupObject {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
13
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
14 static class SequenceFactory implements DBOFactory<Sequence> {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
15
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
16 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
17 public Iterable<Sequence> getDbBackupObjects(Connection con, Schema schema) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
18 List<Sequence> sequences = new ArrayList<Sequence>();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
19 PreparedStatement stmt = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
20 try {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
21 stmt = con.prepareStatement(
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
22 "SELECT c.relname AS sequencename, pg_get_userbyid(c.relowner) AS owner FROM pg_class c " +
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
23 "WHERE c.relkind='S' AND c.relnamespace = ?");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
24 stmt.setInt(1, schema.getOid());
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
25 ResultSet rs = stmt.executeQuery();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
26 while (rs.next()) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
27 Sequence sequence = getSequence(con, schema, rs.getString("sequencename"), rs.getString("owner"));
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
28 sequences.add(sequence);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
29 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
30 rs.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
31 } finally {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
32 if (stmt != null) stmt.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
33 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
34 return sequences;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
35 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
36
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
37 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
38 public Sequence getDbBackupObject(Connection con, String sequenceName, Schema schema) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
39 PreparedStatement stmt = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
40 Sequence sequence = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
41 try {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
42 stmt = con.prepareStatement(
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
43 "SELECT c.relname AS sequencename, pg_get_userbyid(c.relowner) AS owner FROM pg_class c " +
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
44 "WHERE c.relkind='S' AND c.relnamespace = ? AND c.relname = ?");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
45 stmt.setInt(1, schema.getOid());
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
46 stmt.setString(2, sequenceName);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
47 ResultSet rs = stmt.executeQuery();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
48 if (rs.next()) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
49 sequence = getSequence(con, schema, rs.getString("sequencename"), rs.getString("owner"));
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
50 } else {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
51 throw new RuntimeException("no such sequence " + sequenceName);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
52 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
53 rs.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
54 } finally {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
55 if (stmt != null) stmt.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
56 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
57 return sequence;
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
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
62 static class CachingSequenceFactory extends CachingDBOFactory<Sequence> {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
63
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
64 protected CachingSequenceFactory(Schema.CachingSchemaFactory schemaFactory) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
65 super(schemaFactory);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
66 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
67
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
68 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
69 protected PreparedStatement getAllStatement(Connection con) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
70 return con.prepareStatement(
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
71 "SELECT c.relname AS sequencename, pg_get_userbyid(c.relowner) AS owner, " +
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
72 "c.relnamespace AS schema_oid FROM pg_class c " +
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
73 "WHERE c.relkind='S'");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
74 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
75
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
76 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
77 protected Sequence newDbBackupObject(Connection con, ResultSet rs, Schema schema) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
78 return getSequence(con, schema, rs.getString("sequencename"), rs.getString("owner"));
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
79 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
80
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
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
84 private static Sequence getSequence(Connection con, Schema schema, String sequenceName, String owner) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
85 PreparedStatement stmt = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
86 Sequence sequence = null;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
87 try {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
88 stmt = con.prepareStatement(
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
89 "SELECT * FROM " + schema.getName() + "." + sequenceName);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
90 ResultSet rs = stmt.executeQuery();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
91 if (rs.next())
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
92 sequence = new Sequence(sequenceName, rs, schema, owner);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
93 else
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
94 throw new RuntimeException("no such sequence: " + sequenceName);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
95 rs.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
96 } finally {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
97 if (stmt != null) stmt.close();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
98 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
99 return sequence;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
100 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
101
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
102 private final long last_value;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
103 private final long start_value;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
104 private final long increment_by;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
105 private final long max_value;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
106 private final long min_value;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
107 private final long cache_value;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
108 private final boolean is_cycled;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
109
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
110 private Sequence(String sequenceName, ResultSet rs, Schema schema, String owner) throws SQLException {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
111 //super(rs.getString("sequence_name"), schema, owner); // postgresql bug? not always consistent with pg_class.relname
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
112 super(sequenceName, schema, owner);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
113 this.last_value = rs.getLong("last_value");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
114 this.start_value = rs.getLong("start_value");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
115 this.increment_by = rs.getLong("increment_by");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
116 this.max_value = rs.getLong("max_value");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
117 this.min_value = rs.getLong("min_value");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
118 this.is_cycled = rs.getBoolean("is_cycled");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
119 this.cache_value = rs.getLong("cache_value");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
120 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
121
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
122 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
123 protected StringBuilder appendCreateSql(StringBuilder buf, DataFilter dataFilter) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
124 buf.append("CREATE SEQUENCE ");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
125 buf.append(getName());
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
126 if (increment_by != 1) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
127 buf.append(" INCREMENT BY ");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
128 buf.append(increment_by);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
129 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
130 buf.append(" MINVALUE ");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
131 buf.append(min_value);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
132 buf.append(" MAXVALUE ");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
133 buf.append(max_value);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
134 if (is_cycled)
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
135 buf.append(" CYCLE");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
136 if (cache_value > 1) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
137 buf.append(" CACHE ");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
138 buf.append(cache_value);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
139 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
140 buf.append(" START ");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
141 buf.append(start_value);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
142 buf.append(";\n");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
143 if (dataFilter.dumpData(schema.getName(), name)) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
144 buf.append("SELECT setval('");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
145 buf.append(getName());
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
146 buf.append("',");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
147 buf.append(last_value);
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
148 buf.append(") ;\n");
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
149 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
150 return buf;
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
151 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
152
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
153 @Override
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
154 protected StringBuilder appendCreateSql(StringBuilder buf) {
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
155 throw new UnsupportedOperationException();
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
156 }
7ecd1a4ef557 add content
Franklin Schmidt <fschmidt@gmail.com>
parents:
diff changeset
157 }