|
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 class Index extends DbBackupObject {
|
|
|
13
|
|
|
14 static class IndexFactory implements DBOFactory<Index> {
|
|
|
15
|
|
|
16 @Override
|
|
|
17 public Iterable<Index> getDbBackupObjects(Connection con, Schema schema) throws SQLException {
|
|
|
18 List<Index> indexes = new ArrayList<Index>();
|
|
|
19 PreparedStatement stmt = null;
|
|
|
20 try {
|
|
|
21 stmt = con.prepareStatement(
|
|
|
22 // "SELECT * FROM pg_indexes WHERE schemaname = ?");
|
|
|
23 // duplicate the pg_indexes view definition but skipping the primary indexes
|
|
|
24 "SELECT c.relname AS tablename, i.relname AS indexname, " +
|
|
|
25 "pg_get_indexdef(i.oid) AS indexdef " +
|
|
|
26 "FROM pg_index x " +
|
|
|
27 "JOIN pg_class c ON c.oid = x.indrelid " +
|
|
|
28 "JOIN pg_class i ON i.oid = x.indexrelid " +
|
|
|
29 //"LEFT JOIN pg_namespace n ON n.oid = c.relnamespace " +
|
|
|
30 //"LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace " +
|
|
|
31 "WHERE c.relkind = 'r'::\"char\" AND i.relkind = 'i'::\"char\" " +
|
|
|
32 "AND NOT x.indisprimary AND c.relnamespace = ?");
|
|
|
33 stmt.setInt(1, schema.getOid());
|
|
|
34 ResultSet rs = stmt.executeQuery();
|
|
|
35 while (rs.next()) {
|
|
|
36 indexes.add(new Index(rs.getString("indexname"), schema, rs.getString("tablename"), rs.getString("indexdef")));
|
|
|
37 }
|
|
|
38 rs.close();
|
|
|
39 } finally {
|
|
|
40 if (stmt != null) stmt.close();
|
|
|
41 }
|
|
|
42
|
|
|
43 /*// too slow, get primary keys from pg_constraint instead, use pg_get_constraintdef()
|
|
|
44 ZipBackup.timerStart("primary keys");
|
|
|
45 DatabaseMetaData metaData = con.getMetaData();
|
|
|
46 rs = metaData.getPrimaryKeys(null, schema.getName(), null);
|
|
|
47 Map<String,PrimaryKey> pkeys = new HashMap<String,PrimaryKey>();
|
|
|
48 while (rs.next()) {
|
|
|
49 String tableName = rs.getString("TABLE_NAME");
|
|
|
50 String indexName = rs.getString("PK_NAME");
|
|
|
51 PrimaryKey pkey = pkeys.get(indexName);
|
|
|
52 if (pkey == null) {
|
|
|
53 pkey = new PrimaryKey(indexName, schema, tableName);
|
|
|
54 pkeys.put(indexName, pkey);
|
|
|
55 }
|
|
|
56 pkey.columns.put(Integer.valueOf(rs.getInt("KEY_SEQ")), rs.getString("COLUMN_NAME"));
|
|
|
57 }
|
|
|
58 rs.close();
|
|
|
59 indexes.addAll(pkeys.values());
|
|
|
60 ZipBackup.timerEnd("primary keys");
|
|
|
61 */
|
|
|
62 return indexes;
|
|
|
63 }
|
|
|
64
|
|
|
65 @Override
|
|
|
66 public Index getDbBackupObject(Connection con, String indexName, Schema schema) throws SQLException {
|
|
|
67 Index index = null;
|
|
|
68 PreparedStatement stmt = null;
|
|
|
69 try {
|
|
|
70 stmt = con.prepareStatement("SELECT * FROM pg_indexes WHERE schemaname = ? " +
|
|
|
71 "AND indexname = ?");
|
|
|
72 stmt.setString(1, schema.getName());
|
|
|
73 stmt.setString(2, indexName);
|
|
|
74 ResultSet rs = stmt.executeQuery();
|
|
|
75 if (rs.next()) {
|
|
|
76 index = new Index(rs.getString("indexname"), schema, rs.getString("tablename"), rs.getString("indexdef"));
|
|
|
77 } else {
|
|
|
78 throw new RuntimeException("no such index: " + indexName);
|
|
|
79 }
|
|
|
80 rs.close();
|
|
|
81 } finally {
|
|
|
82 if (stmt != null) stmt.close();
|
|
|
83 }
|
|
|
84 return index;
|
|
|
85 }
|
|
|
86 }
|
|
|
87
|
|
|
88 static class CachingIndexFactory extends CachingDBOFactory<Index> {
|
|
|
89
|
|
|
90 private final Table.CachingTableFactory tableFactory;
|
|
|
91
|
|
|
92 protected CachingIndexFactory(Schema.CachingSchemaFactory schemaFactory, Table.CachingTableFactory tableFactory) {
|
|
|
93 super(schemaFactory);
|
|
|
94 this.tableFactory = tableFactory;
|
|
|
95 }
|
|
|
96
|
|
|
97 @Override
|
|
|
98 protected PreparedStatement getAllStatement(Connection con) throws SQLException {
|
|
|
99 return con.prepareStatement(
|
|
|
100 "SELECT x.indrelid AS table_oid, i.relname AS indexname, " +
|
|
|
101 "pg_get_indexdef(i.oid) AS indexdef, " +
|
|
|
102 "i.relnamespace AS schema_oid " +
|
|
|
103 "FROM pg_index x " +
|
|
|
104 "JOIN pg_class i ON i.oid = x.indexrelid " +
|
|
|
105 "WHERE i.relkind = 'i'::\"char\" " +
|
|
|
106 "AND NOT x.indisprimary ");
|
|
|
107 }
|
|
|
108
|
|
|
109 @Override
|
|
|
110 protected Index newDbBackupObject(Connection con, ResultSet rs, Schema schema) throws SQLException {
|
|
|
111 Table table = tableFactory.getTable(rs.getInt("table_oid"));
|
|
|
112 return new Index(rs.getString("indexname"), schema, table.getName(), rs.getString("indexdef"));
|
|
|
113 }
|
|
|
114
|
|
|
115 }
|
|
|
116
|
|
|
117 protected final String tableName;
|
|
|
118 private final String definition;
|
|
|
119
|
|
|
120 private Index(String name, Schema schema, String tableName, String definition) {
|
|
|
121 super(name, schema, null); // no owner (always same as table)
|
|
|
122 this.tableName = tableName;
|
|
|
123 this.definition = definition;
|
|
|
124 }
|
|
|
125
|
|
|
126 @Override
|
|
|
127 String getSql(DataFilter dataFilter) {
|
|
|
128 return definition.replace(" ON " + schema.getName() + ".", " ON ") + " ;\n"; // remove schema name
|
|
|
129 }
|
|
|
130
|
|
|
131 @Override
|
|
|
132 protected StringBuilder appendCreateSql(StringBuilder buf) {
|
|
|
133 throw new UnsupportedOperationException();
|
|
|
134 }
|
|
|
135
|
|
|
136 /* not used because too slow getting them
|
|
|
137 private final static class PrimaryKey extends Index {
|
|
|
138
|
|
|
139 private SortedMap<Integer,String> columns = new TreeMap<Integer,String>();
|
|
|
140
|
|
|
141 private PrimaryKey(String name, Schema schema, String tableName) {
|
|
|
142 super(name, schema, tableName, null);
|
|
|
143 }
|
|
|
144
|
|
|
145 @Override
|
|
|
146 public String getSql() {
|
|
|
147 StringBuilder buf = new StringBuilder();
|
|
|
148 buf.append("ALTER TABLE ");
|
|
|
149 buf.append(tableName);
|
|
|
150 buf.append(" ADD CONSTRAINT ");
|
|
|
151 buf.append(name);
|
|
|
152 buf.append(" PRIMARY KEY (");
|
|
|
153 for (String column : columns.values()) {
|
|
|
154 buf.append(column);
|
|
|
155 buf.append(",");
|
|
|
156 }
|
|
|
157 buf.deleteCharAt(buf.length()-1);
|
|
|
158 buf.append(");\n");
|
|
|
159 return buf.toString();
|
|
|
160 }
|
|
|
161
|
|
|
162 }
|
|
|
163 */
|
|
|
164
|
|
|
165 }
|
