Browse CodeHomeBlogBrowse CodeForumIssueTrackerWiki

Line # Revision Author
1 241 alan /**
2 270 alan * Copyright (c) 2008-2010, Wave2 Limited. All rights reserved.
3 247 alan *
4 * All rights reserved.
5 *
6 * Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
7 *
8 * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
9 * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
10 * Neither the name of Wave2 Limited nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
11 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
12 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
13 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
14 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR
15 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
16 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
17 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
18 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
19 * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
20 * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
21 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
22 */
23 241 alan package org.binarystor.postgresql;
24
25 import java.io.ByteArrayOutputStream;
26 import java.io.IOException;
27 import java.io.File;
28 import java.io.FileWriter;
29 import java.io.BufferedWriter;
30 import java.sql.*;
31 import java.util.ArrayList;
32 import java.util.List;
33 244 alan import java.util.Map;
34 241 alan import java.util.TreeMap;
35 242 alan import java.util.HashMap;
36 241 alan import org.kohsuke.args4j.*;
37
38 /**
39 247 alan *
40 * @author Alan Snelson
41 */
42 241 alan public class PostgreSQLDump {
43
44 //Command Line Arguments
45 247 alan @Option(name = "--help")
46 241 alan private boolean help;
47 247 alan @Option(name = "-h", usage = "PostgreSQL Server Hostname")
48 241 alan private String hostname;
49 250 alan @Option(name = "-p", usage = "PostgreSQL Server Port Number")
50 private int port = 5432;
51 @Option(name = "-U", usage = "PostgreSQL Username")
52 241 alan private String username;
53 250 alan @Option(name = "-W", usage = "PostgreSQL Password")
54 241 alan private String password;
55 247 alan @Option(name = "-v")
56 250 alan private String database;
57 241 alan //TODO remove prior to release
58 private boolean verbose = true;
59 // receives other command line parameters than options
60 @Argument
61 private List<String> arguments = new ArrayList<String>();
62 private static String version = "0.1";
63 private String schema = "public";
64 private Connection conn = null;
65 private DatabaseMetaData databaseMetaData;
66 private String databaseProductVersion = null;
67 private int databaseProductMajorVersion = 0;
68 private int databaseProductMinorVersion = 0;
69 private String posgresqlVersion = null;
70 242 alan //Privilege codes
71 247 alan private static final HashMap<String, String> pCodes = new HashMap<String, String>() {
72 {
73 242 alan put("a", "INSERT");
74 put("c", "CONNECT");
75 put("C", "CREATE");
76 put("d", "DELETE");
77 put("D", "TRUNCATE");
78 put("r", "SELECT");
79 put("t", "TRIGGER");
80 put("T", "TEMPORARY");
81 put("U", "USAGE");
82 245 alan put("w", "UPDATE");
83 242 alan put("x", "REFERENCES");
84 244 alan put("X", "EXECUTE");
85 247 alan }
86 242 alan };
87 241 alan
88 /**
89 247 alan * Default contructor for PostgreSQLDump.
90 */
91 241 alan public PostgreSQLDump() {
92 }
93
94 /**
95 247 alan * Create a new instance of PostgreSQLDump using default database (postgres).
96 *
97 * @param host PostgreSQL Server Hostname
98 * @param username PostgreSQL Username
99 * @param password PostgreSQL Password
100 * @throws SQLException
101 */
102 241 alan public PostgreSQLDump(String host, String username, String password) throws SQLException {
103 247 alan try {
104 241 alan connect(host, username, password, "postgres");
105 250 alan this.hostname = host;
106 this.username = username;
107 this.password = password;
108 if (System.getenv("PGDATABASE") != null){
109 this.database = System.getenv("PGDATABASE");
110 } else {
111 this.database = "postgres";
112 }
113 247 alan } catch (SQLException se) {
114 241 alan throw se;
115 }
116
117 }
118
119 /**
120 247 alan * Create a new instance of PostgreSQLDump using supplied database.
121 *
122 * @param host PostgreSQL Server Hostname
123 * @param username PostgreSQL Username
124 * @param password PostgreSQL Password
125 * @param db Default database
126 * @throws SQLException
127 */
128 public PostgreSQLDump(String host, String username, String password, String db) throws SQLException {
129 try {
130 241 alan connect(host, username, password, db);
131 250 alan this.hostname = host;
132 this.username = username;
133 this.password = password;
134 this.database = db;
135 247 alan } catch (SQLException se) {
136 241 alan throw se;
137 }
138 }
139
140 /**
141 247 alan * Connect to PostgreSQL server
142 *
143 * @param host PostgreSQL Server Hostname
144 * @param port
145 * @param username PostgreSQL Username
146 * @param password PostgreSQL Password
147 * @param db Default database
148 * @throws SQLException
149 */
150 public void connect(String host, int port, String username, String password, String db) throws SQLException {
151 try {
152 Class.forName("org.postgresql.Driver").newInstance();
153 conn = DriverManager.getConnection("jdbc:postgresql://" + host + ":" + port + "/" + db, username, password);
154 241 alan databaseMetaData = conn.getMetaData();
155 databaseProductVersion = databaseMetaData.getDatabaseProductVersion();
156 databaseProductMajorVersion = databaseMetaData.getDatabaseMajorVersion();
157 databaseProductMinorVersion = databaseMetaData.getDatabaseMinorVersion();
158 250 alan this.hostname = host;
159 this.port = port;
160 this.username = username;
161 this.password = password;
162 this.database = db;
163 247 alan if (verbose) {
164 System.out.println("PostgreSQL " + databaseProductMajorVersion + "." + databaseProductMinorVersion + " Database connection established");
165 241 alan }
166 247 alan } catch (SQLException se) {
167 241 alan throw se;
168 247 alan } catch (Exception e) {
169 System.err.println("Cannot connect to database server");
170 241 alan }
171 }
172
173 242 alan /**
174 247 alan * Connect to PostgreSQL server using default port
175 *
176 * @param host PostgreSQL Server Hostname
177 * @param username PostgreSQL Username
178 * @param password PostgreSQL Password
179 * @param db Default database
180 * @throws SQLException
181 */
182 public void connect(String host, String username, String password, String db) throws SQLException {
183 connect(host, 5432, username, password, db);
184 241 alan }
185
186 247 alan /**
187 * Parse permissions string and generate GRANT statements
188 *
189 * @param acl PostgreSQL acl string
190 * @param name PostgreSQL name of object for grant statements
191 * @param type PostgreSQL type of object for grant statements
192 * @return
193 */
194 public static String parseACL(String acl, String name, String type) {
195 242 alan String aclCommands = "";
196 247 alan String[] acls = acl.substring(1, acl.length() - 1).split(",");
197 HashMap<String, ArrayList<String>> withGrant = new HashMap();
198 HashMap<String, ArrayList<String>> withoutGrant = new HashMap();
199 244 alan //Function names require braces
200 247 alan if (type.equals("FUNCTION")) {
201 244 alan name = "\"" + name + "\"()";
202 247 alan } else {
203 244 alan name = "\"" + name + "\"";
204 }
205 242 alan for (String priv : acls) {
206 String user = priv.split("=")[0];
207 243 alan String privs = priv.split("=")[1].split("/")[0];
208 242 alan //Public Role?
209 247 alan if (user.equals("")) {
210 242 alan user = "public";
211 }
212 243 alan //Schema privileges GRANT ALL?
213 247 alan if (type.equals("DATABASE") && privs.contains("C") && privs.contains("T") && privs.contains("c")) {
214 if (privs.contains("*")) {
215 244 alan aclCommands += "GRANT ALL ON DATABASE " + name + " TO " + user + " WITH GRANT OPTION;\n";
216 243 alan } else {
217 244 alan aclCommands += "GRANT ALL ON DATABASE " + name + " TO " + user + ";\n";
218 243 alan }
219 247 alan } else if (type.equals("SCHEMA") && privs.contains("U") && privs.contains("C")) {
220 if (privs.contains("*")) {
221 244 alan aclCommands += "GRANT ALL ON SCHEMA " + name + " TO " + user + " WITH GRANT OPTION;\n";
222 243 alan } else {
223 244 alan aclCommands += "GRANT ALL ON SCHEMA " + name + " TO " + user + ";\n";
224 243 alan }
225 247 alan } else if (type.equals("SEQUENCE") && privs.contains("r") && privs.contains("w") && privs.contains("U")) {
226 if (privs.contains("*")) {
227 245 alan aclCommands += "GRANT ALL ON SEQUENCE " + name + " TO " + user + " WITH GRANT OPTION;\n";
228 } else {
229 aclCommands += "GRANT ALL ON SEQUENCE " + name + " TO " + user + ";\n";
230 }
231 247 alan } else if (type.equals("TABLE") && privs.contains("a") && privs.contains("r") && privs.contains("w") && privs.contains("d") && privs.contains("x") && privs.contains("t")) {
232 if (privs.contains("*")) {
233 246 alan aclCommands += "GRANT ALL ON TABLE " + name + " TO " + user + " WITH GRANT OPTION;\n";
234 } else {
235 aclCommands += "GRANT ALL ON TABLE " + name + " TO " + user + ";\n";
236 }
237 247 alan } else {
238 243 alan //Determine privileges and check if GRANT OPTION set.
239 247 alan for (char c : privs.toCharArray()) {
240 if (pCodes.containsKey(Character.toString(c))) {
241 243 alan //Is this the last character?
242 247 alan if (privs.indexOf(c) != privs.length() - 1) {
243 243 alan //Is the next character a GRANT
244 247 alan if ('*' == privs.charAt(privs.indexOf(c) + 1)) {
245 if (withGrant.containsKey(user)) {
246 243 alan ArrayList userPrivs = withGrant.get(user);
247 userPrivs.add(pCodes.get(Character.toString(c)));
248 247 alan withGrant.put(user, userPrivs);
249 243 alan } else {
250 ArrayList userPrivs = new ArrayList();
251 userPrivs.add(pCodes.get(Character.toString(c)));
252 247 alan withGrant.put(user, userPrivs);
253 243 alan }
254 } else {
255 247 alan if (withoutGrant.containsKey(user)) {
256 243 alan ArrayList userPrivs = withoutGrant.get(user);
257 userPrivs.add(pCodes.get(Character.toString(c)));
258 247 alan withoutGrant.put(user, userPrivs);
259 243 alan } else {
260 ArrayList userPrivs = new ArrayList();
261 userPrivs.add(pCodes.get(Character.toString(c)));
262 247 alan withoutGrant.put(user, userPrivs);
263 243 alan }
264 242 alan }
265 247 alan } else {
266 if (withoutGrant.containsKey(user)) {
267 ArrayList userPrivs = withoutGrant.get(user);
268 userPrivs.add(pCodes.get(Character.toString(c)));
269 withoutGrant.put(user, userPrivs);
270 } else {
271 ArrayList userPrivs = new ArrayList();
272 userPrivs.add(pCodes.get(Character.toString(c)));
273 withoutGrant.put(user, userPrivs);
274 }
275 242 alan }
276 }
277 }
278 }
279 }
280 247 alan //create GRANT statements without GRANT OPTION
281 for (String user : withoutGrant.keySet()) {
282 aclCommands += "GRANT ";
283 for (String priv : withoutGrant.get(user)) {
284 if (withoutGrant.get(user).get(withoutGrant.get(user).size() - 1).equals(priv)) {
285 aclCommands += priv;
286 } else {
287 aclCommands += priv + ", ";
288 242 alan }
289 247 alan }
290 aclCommands += " ON " + type + " " + name + " TO \"" + user + "\";\n";
291 }
292 //create GRANT statements with GRANT OPTION
293 for (String user : withGrant.keySet()) {
294 aclCommands += "GRANT ";
295 for (String priv : withGrant.get(user)) {
296 if (withGrant.get(user).get(withGrant.get(user).size() - 1).equals(priv)) {
297 aclCommands += priv;
298 } else {
299 aclCommands += priv + ", ";
300 242 alan }
301 247 alan }
302 aclCommands += " ON " + type + " " + name + " TO \"" + user + "\" WITH GRANT OPTION;\n";
303 }
304 243 alan return aclCommands;
305 242 alan }
306
307 247 alan /**
308 * Obtain server parameters
309 *
310 250 alan * @return parameters
311 247 alan */
312 public Map<String, String> dumpServerParameters() {
313 244 alan Map<String, String> parameters = new TreeMap();
314 247 alan try {
315 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
316 s.executeQuery("SELECT name, setting, vartype FROM pg_catalog.pg_settings");
317 ResultSet rs = s.getResultSet();
318 while (rs.next()) {
319 if (rs.getString("vartype").equals("string")) {
320 parameters.put(rs.getString("name"), "'" + rs.getString("setting") + "'");
321 244 alan }
322 247 alan parameters.put(rs.getString("name"), rs.getString("setting"));
323 244 alan }
324 } catch (SQLException e) {
325 247 alan System.err.println(e.getMessage());
326 244 alan }
327 247 alan if (verbose) {
328 244 alan System.out.println(parameters.toString());
329 }
330 return parameters;
331 247 alan }
332 244 alan
333 247 alan /**
334 * Returns create statement for supplied role
335 *
336 * @param role PostgreSQL role name
337 * @return
338 */
339 public String dumpCreateRole(String role) {
340 245 alan String createRole = null;
341 247 alan try {
342 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
343 s.executeQuery("SELECT pg_catalog.pg_authid.oid, rolname, rolcanlogin, rolpassword, rolsuper, rolinherit, rolcreatedb, " +
344 "rolcreaterole, rolconnlimit, rolvaliduntil, rolcatupdate, description FROM pg_catalog.pg_authid " +
345 "LEFT OUTER JOIN pg_catalog.pg_shdescription " +
346 "ON pg_catalog.pg_authid.oid = pg_catalog.pg_shdescription.objoid WHERE rolname = '" + role + "';");
347 ResultSet rs = s.getResultSet();
348 while (rs.next()) {
349 245 alan createRole = "CREATE ROLE " + role;
350 247 alan if (rs.getBoolean("rolcanlogin") == true) {
351 245 alan createRole += " LOGIN";
352 }
353 247 alan if (rs.getString("rolpassword") != null) {
354 245 alan createRole += " ENCRYPTED PASSWORD '" + rs.getString("rolpassword") + "'";
355 }
356 //Superuser?
357 247 alan if (rs.getBoolean("rolsuper") == true) {
358 245 alan createRole += " SUPERUSER";
359 } else {
360 createRole += " NOSUPERUSER";
361 }
362 //Inherit?
363 247 alan if (rs.getBoolean("rolinherit") == true) {
364 245 alan createRole += " INHERIT";
365 } else {
366 createRole += " NOINHERIT";
367 }
368 //CreateDB?
369 247 alan if (rs.getBoolean("rolcreatedb") == true) {
370 245 alan createRole += " CREATEDB";
371 } else {
372 createRole += " NOCREATEDB";
373 }
374 //CreateRole?
375 247 alan if (rs.getBoolean("rolcreaterole") == true) {
376 245 alan createRole += " CREATEROLE";
377 } else {
378 createRole += " NOCREATEROLE";
379 }
380 //Connection Limit?
381 247 alan if (rs.getInt("rolconnlimit") != -1) {
382 245 alan createRole += " CONNECTION LIMIT " + rs.getString("rolconnlimit");
383 }
384 //Valid Until?
385 247 alan if (rs.getString("rolvaliduntil") != null && !rs.getString("rolvaliduntil").equals("infinity")) {
386 245 alan createRole += " VALID UNTIL '" + rs.getString("rolvaliduntil") + "'";
387 }
388 createRole += ";\n";
389 //Update Catalog Direct?
390 247 alan if (rs.getBoolean("rolsuper") == true) {
391 if (rs.getBoolean("rolcatupdate") == false) {
392 245 alan createRole += "UPDATE pg_catalog.pg_authid SET rolcatupdate=false WHERE rolname='" + role + "';\n";
393 }
394 }
395 //Role Membership?
396 247 alan Statement rolestmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
397 245 alan rolestmt.executeQuery("SELECT rolname FROM pg_auth_members JOIN pg_catalog.pg_authid ON pg_catalog.pg_auth_members.roleid = pg_catalog.pg_authid.oid WHERE member = " + rs.getString("oid"));
398 247 alan ResultSet rolers = rolestmt.getResultSet();
399 while (rolers.next()) {
400 245 alan createRole += "GRANT \"" + rolers.getString("rolname") + "\" TO " + role + ";\n";
401 }
402 247 alan if (rs.getString("description") != null) {
403 245 alan createRole += "COMMENT ON ROLE " + role + " IS '" + rs.getString("description") + "';\n";
404 }
405 }
406 } catch (SQLException e) {
407 System.err.println(e.getMessage());
408 }
409 247 alan if (verbose) {
410 245 alan System.out.println(createRole);
411 }
412 return createRole;
413 }
414 244 alan
415 247 alan /**
416 * Returns create statement for supplied database
417 *
418 * @param database PostgreSQL database name
419 250 alan * @return createDatabase
420 247 alan */
421 public String dumpCreateDatabase(String database) {
422 241 alan String createDatabase = null;
423 247 alan try {
424 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
425 241 alan //PostgreSQL version 8.2+
426 247 alan if (databaseProductMajorVersion == 8 & databaseProductMinorVersion >= 2) {
427 s.executeQuery("SELECT tableoid, oid, (SELECT rolname FROM pg_catalog.pg_roles " +
428 "WHERE oid = datdba) AS dba, pg_encoding_to_char(encoding) AS encoding, " +
429 "NULL AS datcollate, NULL AS datctype, datfrozenxid, " +
430 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, " +
431 "datconnlimit, shobj_description(oid, 'pg_database') AS description, datacl FROM pg_database " +
432 "WHERE datname = '" + database + "';");
433 241 alan }
434 247 alan ResultSet rs = s.getResultSet();
435 while (rs.next()) {
436 243 alan createDatabase = "CREATE DATABASE " + database + " WITH OWNER = " + rs.getString("dba");
437 247 alan if (rs.getString("encoding") != null) {
438 241 alan createDatabase += " ENCODING = '" + rs.getString("encoding") + "'";
439 }
440 247 alan if (rs.getString("datcollate") != null) {
441 241 alan createDatabase += " LC_COLLATE = '" + rs.getString("datcollate") + "'";
442 }
443 247 alan if (rs.getString("datctype") != null) {
444 241 alan createDatabase += " LC_CTYPE = '" + rs.getString("datctype") + "'";
445 }
446 247 alan if (!rs.getString("tablespace").equals("pg_default")) {
447 241 alan createDatabase += " TABLESPACE = " + rs.getString("tablespace");
448 }
449 243 alan createDatabase += " CONNECTION LIMIT = " + rs.getString("datconnlimit") + ";\n";
450 247 alan if (rs.getString("datacl") != null) {
451 createDatabase += parseACL(rs.getString("datacl"), database, "DATABASE");
452 243 alan }
453 247 alan if (rs.getString("description") != null) {
454 243 alan createDatabase += "COMMENT ON DATABASE " + database + " IS '" + rs.getString("description") + "';\n";
455 241 alan }
456 }
457 } catch (SQLException e) {
458 System.err.println(e.getMessage());
459 }
460 247 alan if (verbose) {
461 241 alan System.out.println(createDatabase);
462 }
463 return createDatabase;
464 }
465
466 247 alan /**
467 * Returns create statement for supplied schema
468 *
469 * @param schema PostgreSQL role name
470 250 alan * @return createSchema
471 247 alan */
472 243 alan public String dumpCreateSchema(String schema) {
473 String createSchema = null;
474 247 alan try {
475 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
476 s.executeQuery("SELECT nspacl, description, rolname FROM pg_catalog.pg_namespace LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_namespace.oid=pg_catalog.pg_description.objoid JOIN pg_catalog.pg_roles ON pg_catalog.pg_roles.oid=pg_catalog.pg_namespace.nspowner WHERE nspname = '" + schema + "'");
477 ResultSet rs = s.getResultSet();
478 while (rs.next()) {
479 243 alan createSchema = "CREATE SCHEMA \"" + schema + "\" AUTHORIZATION " + rs.getString("rolname") + ";\n";
480 247 alan if (rs.getString("nspacl") != null) {
481 createSchema += parseACL(rs.getString("nspacl"), schema, "SCHEMA");
482 243 alan }
483 247 alan if (rs.getString("description") != null) {
484 243 alan createSchema += "COMMENT ON SCHEMA \"" + schema + "\" IS '" + rs.getString("description") + "';\n";
485 }
486 }
487 } catch (SQLException e) {
488 System.err.println(e.getMessage());
489 }
490 247 alan if (verbose) {
491 243 alan System.out.println(createSchema);
492 }
493 return createSchema;
494 }
495
496 247 alan /**
497 * Returns create statement for supplied domain
498 *
499 * @param schema PostgreSQL schema
500 * @param domain PostgreSQL domain name
501 250 alan * @return createDomain
502 247 alan */
503 245 alan public String dumpCreateDomain(String schema, String domain) {
504 String createDomain = null;
505 247 alan try {
506 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
507 s.executeQuery("SELECT (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = typowner) as typowner, typnotnull, pg_catalog.format_type(typbasetype, typtypmod) AS typdefn, " +
508 245 alan "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) AS typdefaultbin, typdefault, description " +
509 "FROM pg_catalog.pg_type LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_type.oid " +
510 "WHERE pg_catalog.pg_type.oid = (SELECT oid FROM pg_catalog.pg_type WHERE typname='" + domain + "' AND typnamespace = (SELECT oid from pg_catalog.pg_namespace WHERE nspname = '" + schema + "'))::pg_catalog.oid");
511 247 alan ResultSet rs = s.getResultSet();
512 while (rs.next()) {
513 245 alan createDomain = "CREATE DOMAIN \"" + domain + "\" AS " + rs.getString("typdefn");
514 //Default?
515 247 alan if (rs.getString("typdefaultbin") != null) {
516 245 alan createDomain += " DEFAULT " + rs.getString("typdefaultbin");
517 }
518 247 alan if (rs.getBoolean("typnotnull") == true) {
519 245 alan createDomain += " NOT NULL";
520 }
521 createDomain += ";\n";
522 createDomain += "ALTER DOMAIN \"" + domain + "\" OWNER TO " + rs.getString("typowner") + ";\n";
523 //Comment?
524 247 alan if (rs.getString("description") != null) {
525 245 alan createDomain += "COMMENT ON DOMAIN \"" + domain + "\" IS '" + rs.getString("description") + "';\n";
526 }
527 }
528 } catch (SQLException e) {
529 System.err.println(e.getMessage());
530 }
531 247 alan if (verbose) {
532 245 alan System.out.println(createDomain);
533 }
534 return createDomain;
535 }
536
537 247 alan /**
538 * Returns create statement for supplied sequence
539 *
540 * @param schema PostgreSQL schema
541 * @param sequence PostgreSQL domain name
542 250 alan * @return createSequence
543 247 alan */
544 245 alan public String dumpCreateSequence(String schema, String sequence) {
545 String createSequence = null;
546 247 alan try {
547 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
548 s.executeQuery("SELECT increment_by, min_value, max_value, last_value, cache_value, " +
549 245 alan "is_cycled, relacl, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = relowner) as relowner, " +
550 "description FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_description " +
551 "ON pg_catalog.pg_description.objoid = pg_catalog.pg_class.oid " +
552 "JOIN \"" + sequence + "\" ON pg_catalog.pg_class.relname=sequence_name " +
553 "WHERE relkind = 'S' AND relname = '" + sequence + "'");
554 247 alan ResultSet rs = s.getResultSet();
555 while (rs.next()) {
556 245 alan createSequence = "CREATE SEQUENCE \"" + sequence + "\" ";
557 createSequence += "INCREMENT " + rs.getString("increment_by") + " ";
558 createSequence += "MINVALUE " + rs.getString("min_value") + " ";
559 createSequence += "MAXVALUE " + rs.getString("max_value") + " ";
560 createSequence += "START " + rs.getString("last_value") + " ";
561 createSequence += "CACHE " + rs.getString("cache_value");
562 247 alan if (rs.getBoolean("is_cycled") == true) {
563 245 alan createSequence += " CYCLE";
564 }
565 createSequence += ";\n";
566 createSequence += "ALTER TABLE \"" + sequence + "\" OWNER TO " + rs.getString("relowner") + ";\n";
567 //Privileges?
568 247 alan if (rs.getString("relacl") != null) {
569 createSequence += parseACL(rs.getString("relacl"), schema, "SEQUENCE");
570 245 alan }
571 //Comment?
572 247 alan if (rs.getString("description") != null) {
573 245 alan createSequence += "COMMENT ON SEQUENCE \"" + sequence + "\" IS '" + rs.getString("description") + "';\n";
574 }
575 }
576 } catch (SQLException e) {
577 System.err.println(e.getMessage());
578 }
579 247 alan if (verbose) {
580 245 alan System.out.println(createSequence);
581 }
582 return createSequence;
583 }
584
585 247 alan /**
586 *
587 * @return
588 */
589 public File dumpAllDatabases() {
590 243 alan return null;
591 }
592
593 247 alan /**
594 *
595 * @param database
596 * @return
597 */
598 public File dumpDatabase(String database) {
599 241 alan return null;
600 }
601
602 247 alan /**
603 *
604 * @param database
605 * @return
606 */
607 public File dumpAllTables(String database) {
608 246 alan return null;
609 241 alan }
610
611 247 alan /**
612 * Returns create statement for supplied table using property schema
613 *
614 * @param table PostgreSQL domain name
615 * @return
616 */
617 241 alan public String dumpCreateTable(String table) {
618 247 alan return dumpCreateTable(schema, table);
619 241 alan }
620
621 247 alan /**
622 * Returns create statement for supplied table
623 *
624 * @param schema PostgreSQL schema
625 * @param table PostgreSQL domain name
626 250 alan * @return createTable
627 247 alan */
628 241 alan public String dumpCreateTable(String schema, String table) {
629 246 alan String createTable = null;
630 String comments = "";
631 247 alan HashMap<Integer, String> columnNames = new HashMap();
632 try {
633 Statement tablestmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
634 tablestmt.executeQuery("SELECT pg_catalog.pg_class.oid, relname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = relowner) AS rolname, " +
635 246 alan "relnatts, relhasoids, relchecks, reltriggers, " +
636 "relhaspkey, relacl, description, spcname FROM pg_catalog.pg_class " +
637 "LEFT OUTER JOIN pg_catalog.pg_tablespace ON pg_catalog.pg_class.reltablespace = pg_catalog.pg_tablespace.oid " +
638 "LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_class.oid " +
639 "WHERE relname = '" + table + "' AND relnamespace = (SELECT oid from pg_namespace " +
640 "WHERE nspname = '" + schema + "')::pg_catalog.oid AND relkind = 'r' AND objsubid = 0");
641 ResultSet tablers = tablestmt.getResultSet();
642 247 alan while (tablers.next()) {
643 246 alan createTable = "CREATE TABLE \"" + table + "\"\n(\n";
644 //Process Columns
645 247 alan Statement columnstmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
646 columnstmt.executeQuery("SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, " +
647 "t.typstorage, a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign, " +
648 "a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, defs.adsrc, description " +
649 "FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t " +
650 "ON a.atttypid = t.oid LEFT OUTER JOIN pg_catalog.pg_attrdef defs " +
651 "ON defs.adrelid = a.attrelid AND defs.adnum = a.attnum " +
652 "LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = " +
653 "(SELECT oid FROM pg_class WHERE relname = '" + table + "' AND relnamespace = " +
654 "(SELECT oid from pg_namespace WHERE nspname = '" + schema + "'))::pg_catalog.oid " +
655 "AND pg_catalog.pg_description.objsubid = a.attnum WHERE a.attrelid = " +
656 "(SELECT oid FROM pg_class WHERE relname = '" + table + "' AND " +
657 "relnamespace = (SELECT oid from pg_namespace " +
658 "WHERE nspname = '" + schema + "'))" + "::pg_catalog.oid " +
659 "AND a.attnum > 0::pg_catalog.int2 AND attisdropped = FALSE ORDER BY a.attrelid, a.attnum");
660 246 alan ResultSet columnrs = columnstmt.getResultSet();
661 247 alan while (columnrs.next()) {
662 246 alan columnNames.put(columnrs.getInt("attnum"), columnrs.getString("attname"));
663 createTable += " \"" + columnrs.getString("attname") + "\" " + columnrs.getString("atttypname");
664 241 alan //NOT NULL?
665 247 alan if (columnrs.getBoolean("attnotnull") == true) {
666 241 alan createTable += " NOT NULL";
667 }
668 246 alan //Default?
669 247 alan if (columnrs.getBoolean("atthasdef") == true) {
670 246 alan createTable += " DEFAULT " + columnrs.getString("adsrc");
671 241 alan }
672 247 alan if (!columnrs.isLast() || tablers.getInt("relchecks") > 0) {
673 246 alan createTable += ",";
674 }
675 //Comment?
676 247 alan if (columnrs.getString("description") != null) {
677 246 alan createTable += " -- " + columnrs.getString("description") + "\n";
678 247 alan comments += "COMMENT ON COLUMN \"" + table + "\".\"" + columnrs.getString("attname") + "\" IS '" + columnrs.getString("description") + "';\n";
679 246 alan } else {
680 createTable += "\n";
681 }
682 }
683 //Constraints?
684 247 alan Statement constraintstmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
685 constraintstmt.executeQuery("SELECT conname, contype, conkey, consrc, spcname FROM pg_catalog.pg_constraint " +
686 246 alan "LEFT OUTER JOIN pg_catalog.pg_class ON pg_catalog.pg_constraint.conname = pg_catalog.pg_class.relname " +
687 "LEFT OUTER JOIN pg_catalog.pg_tablespace ON pg_catalog.pg_class.reltablespace = pg_catalog.pg_tablespace.oid " +
688 "WHERE connamespace = (SELECT oid from pg_namespace " +
689 "WHERE nspname = 'public')::pg_catalog.oid AND conrelid = " +
690 247 alan tablers.getString("oid"));
691 246 alan ResultSet constraintrs = constraintstmt.getResultSet();
692 247 alan while (constraintrs.next()) {
693 246 alan createTable += " CONSTRAINT \"" + constraintrs.getString("conname") + "\"";
694 //Primary Key?
695 247 alan if (constraintrs.getString("contype").equals("p")) {
696 246 alan createTable += " PRIMARY KEY (";
697 Integer[] columns = (Integer[]) constraintrs.getArray("conkey").getArray();
698 247 alan for (int i = 0; i < columns.length; i++) {
699 246 alan createTable += "\"" + columnNames.get(columns[i]) + "\"";
700 247 alan if (i < columns.length - 1) {
701 246 alan createTable += ",";
702 } else {
703 createTable += ")";
704 }
705 }
706 247 alan if (constraintrs.getString("spcname") != null) {
707 246 alan createTable += " USING INDEX TABLESPACE \"" + constraintrs.getString("spcname") + "\"";
708 }
709 //Unique Constraint?
710 247 alan } else if (constraintrs.getString("contype").equals("u")) {
711 246 alan createTable += " UNIQUE (";
712 Integer[] columns = (Integer[]) constraintrs.getArray("conkey").getArray();
713 247 alan for (int i = 0; i < columns.length; i++) {
714 246 alan createTable += "\"" + columnNames.get(columns[i]) + "\"";
715 247 alan if (i < columns.length - 1) {
716 246 alan createTable += ",";
717 } else {
718 createTable += ")";
719 }
720 }
721 247 alan if (constraintrs.getString("spcname") != null) {
722 246 alan createTable += " USING INDEX TABLESPACE \"" + constraintrs.getString("spcname") + "\"";
723 }
724 //Check Constraints?
725 247 alan } else if (constraintrs.getString("contype").equals("c")) {
726 246 alan createTable += " CHECK " + constraintrs.getString("consrc");
727 }
728 247 alan if (!constraintrs.isLast()) {
729 241 alan createTable += ",\n";
730 246 alan } else {
731 createTable += "\n";
732 241 alan }
733 }
734 246 alan
735 createTable += ")\n";
736 //Table has OIDs?
737 247 alan if (tablers.getBoolean("relhasoids") == true) {
738 246 alan createTable += "WITH ( OIDS=TRUE )";
739 } else {
740 createTable += "WITH ( OIDS=TRUE )";
741 241 alan }
742 246 alan //Non default tablespace?
743 247 alan if (tablers.getString("spcname") != null) {
744 246 alan createTable += " TABLESPACE \"" + tablers.getString("spcname") + "\"";
745 }
746 createTable += ";\n";
747 //Table Owner
748 247 alan createTable += "ALTER TABLE \"" + table + "\" OWNER TO " + tablers.getString("rolname") + ";\n";
749 246 alan //Privileges?
750 247 alan if (tablers.getString("relacl") != null) {
751 createTable += parseACL(tablers.getString("relacl"), table, "TABLE");
752 246 alan }
753 //Table Comment?
754 247 alan if (tablers.getString("description") != null) {
755 246 alan createTable += "COMMENT ON TABLE \"" + table + "\" IS '" + tablers.getString("description") + "';\n";
756 }
757 //Comments?
758 247 alan if (!comments.equals("")) {
759 246 alan createTable += comments;
760 }
761 241 alan }
762 } catch (SQLException e) {
763 247 alan System.err.println(e.getMessage());
764 241 alan }
765 247 alan if (verbose) {
766 241 alan System.out.println(createTable);
767 }
768 return createTable;
769 }
770
771 244 alan /**
772 247 alan * Returns create statement for supplied function
773 *
774 * @param schema PostgreSQL Schema
775 * @param function PostgreSQL Function
776 250 alan * @return createFunction
777 247 alan */
778 241 alan public String dumpCreateFunction(String schema, String function) {
779 244 alan String createFunction = null;
780 247 alan try {
781 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
782 s.executeQuery("SELECT (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = proowner) as owner, pg_catalog.format_type(prorettype::pg_catalog.oid,NULL) as prorettype, proallargtypes, proargmodes, proargnames, prosrc, lanname, provolatile, proisstrict, " +
783 244 alan "prosecdef, procost, proretset, prorows, proacl, description FROM pg_catalog.pg_proc " +
784 "LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_proc.oid=pg_catalog.pg_description.objoid " +
785 "JOIN pg_language ON pg_catalog.pg_proc.prolang=pg_catalog.pg_language.oid " +
786 "WHERE pronamespace = (SELECT oid from pg_namespace WHERE nspname = '" + schema + "')::pg_catalog.oid " +
787 241 alan "AND proname = '" + function + "'");
788 247 alan ResultSet rs = s.getResultSet();
789 while (rs.next()) {
790 244 alan createFunction = "CREATE OR REPLACE FUNCTION \"" + function + "\"(";
791 //"SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
792 247 alan if (rs.getArray("proargnames") != null) {
793 Statement t = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
794 244 alan String[] argnames = (String[]) rs.getArray("proargnames").getArray();
795 Long[] argtypes = (Long[]) rs.getArray("proallargtypes").getArray();
796 String[] argmodes = (String[]) rs.getArray("proargmodes").getArray();
797 247 alan for (int i = 0; i < argnames.length; i++) {
798 244 alan //IN or OUT or INOUT
799 247 alan if (argmodes[i].equals("i")) {
800 244 alan createFunction += "IN \"" + argnames[i] + "\" ";
801 } else if (argmodes[i].equals("o")) {
802 createFunction += "OUT \"" + argnames[i] + "\" ";
803 }
804 247 alan t.executeQuery("SELECT pg_catalog.format_type('" + argtypes[i] + "'::pg_catalog.oid, NULL) as typename");
805 ResultSet trs = t.getResultSet();
806 while (trs.next()) {
807 244 alan createFunction += trs.getString("typename");
808 247 alan if (i != argnames.length - 1) {
809 244 alan createFunction += ", ";
810 }
811 }
812 }
813 }
814 createFunction += ") RETURNS ";
815 247 alan if (rs.getBoolean("proretset")) {
816 244 alan createFunction += "SETOF ";
817 }
818 createFunction += rs.getString("prorettype") + " AS\n";
819 //Single line or multi-line?
820 247 alan if (rs.getString("prosrc").contains("\n")) {
821 244 alan createFunction += "$$" + rs.getString("prosrc") + "$$\n";
822 247 alan } else {
823 244 alan createFunction += "'" + rs.getString("prosrc") + "'\n";
824 }
825 createFunction += "LANGUAGE '" + rs.getString("lanname") + "' ";
826 247 alan if (rs.getString("provolatile").equals("s")) {
827 244 alan createFunction += "STABLE ";
828 }
829 247 alan if (rs.getString("provolatile").equals("v")) {
830 244 alan createFunction += "VOLATILE ";
831 }
832 247 alan if (rs.getString("provolatile").equals("i")) {
833 244 alan createFunction += "IMMUTABLE ";
834 }
835 247 alan if (rs.getBoolean("proisstrict")) {
836 244 alan createFunction += "STRICT ";
837 }
838 247 alan if (rs.getBoolean("prosecdef")) {
839 244 alan createFunction += "SECURITY DEFINER ";
840 }
841 247 alan if (rs.getBoolean("proretset")) {
842 244 alan createFunction += "COST " + rs.getString("procost") + " ROWS " + rs.getString("prorows") + ";\n";
843 247 alan } else {
844 244 alan createFunction += "COST " + rs.getString("procost") + ";\n";
845 }
846 createFunction += "ALTER FUNCTION \"" + function + "\"() OWNER TO " + rs.getString("owner") + ";\n";
847 247 alan if (rs.getString("proacl") != null) {
848 createFunction += parseACL(rs.getString("proacl"), function, "FUNCTION");
849 244 alan }
850 247 alan if (rs.getString("description") != null) {
851 244 alan createFunction += "COMMENT ON FUNCTION \"" + function + "\"() IS '" + rs.getString("description") + "';\n";
852 }
853 241 alan }
854 } catch (SQLException e) {
855 247 alan System.err.println(e.getMessage());
856 241 alan }
857 247 alan if (verbose) {
858 244 alan System.out.println(createFunction);
859 }
860 241 alan return createFunction;
861 }
862
863 247 alan /**
864 * Returns create statement for supplied trigger
865 *
866 * @param schema
867 * @param trigger
868 250 alan * @return createTrigger
869 247 alan */
870 public String dumpCreateTrigger(String schema, String table, String trigger) {
871 String createTrigger = null;
872 try {
873 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
874 s.executeQuery("SELECT tgname, tgfoid::pg_catalog.regproc AS tgfname, tgtype, " +
875 "tgnargs, tgargs, tgenabled, tgisconstraint, tgconstrname, tgdeferrable, " +
876 "tgconstrrelid, tginitdeferred, t.tableoid, t.oid, " +
877 "tgconstrrelid::pg_catalog.regclass AS tgconstrrelname, description " +
878 "FROM pg_catalog.pg_trigger t " +
879 "LEFT OUTER JOIN pg_catalog.pg_description ON t.oid=pg_catalog.pg_description.objoid " +
880 "WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname = '" + table + "' " +
881 "AND relnamespace = (SELECT oid from pg_namespace " +
882 "WHERE nspname = '" + schema + "'))::pg_catalog.oid AND tgconstraint = 0 " +
883 "AND tgname = '" + trigger + "'");
884 ResultSet rs = s.getResultSet();
885 while (rs.next()) {
886 createTrigger = "CREATE TRIGGER \"" + trigger + "\"\n";
887 // Trigger type
888 int findx = 0;
889 if (((rs.getInt("tgtype")) & (1 << 1)) != 0) {
890 createTrigger += " BEFORE";
891 } else {
892 createTrigger += " AFTER";
893 }
894 if (((rs.getInt("tgtype")) & (1 << 2)) != 0) {
895 createTrigger += " INSERT";
896 findx++;
897 }
898 if (((rs.getInt("tgtype")) & (1 << 3)) != 0) {
899 if (findx > 0) {
900 createTrigger += " OR DELETE";
901 } else {
902 createTrigger += " DELETE";
903 }
904 findx++;
905 }
906 if (((rs.getInt("tgtype")) & (1 << 4)) != 0) {
907 if (findx > 0) {
908 createTrigger += " OR UPDATE";
909 } else {
910 createTrigger += " UPDATE";
911 }
912 }
913 if (((rs.getInt("tgtype")) & (1 << 5)) != 0) {
914 if (findx > 0) {
915 createTrigger += " OR TRUNCATE";
916 } else {
917 createTrigger += " TRUNCATE";
918 }
919 }
920 createTrigger += "\n ON \"" + table + "\"\n";
921 if (((rs.getInt("tgtype")) & (1 << 1)) != 0) {
922 createTrigger += " FOR EACH ROW\n";
923 } else {
924 createTrigger += " FOR EACH STATEMENT\n";
925 }
926 createTrigger += " EXECUTE PROCEDURE " + rs.getString("tgfname") + "";
927 //Do we have function arguments?
928 if (rs.getInt("tgnargs") > 0){
929 createTrigger += "(" + rs.getString("tgargs").substring(0, rs.getString("tgargs").length()-4).replace("\000", ",") + ");";
930 } else {
931 createTrigger += "();";
932 }
933 if (rs.getString("description") != null) {
934 createTrigger += "\nCOMMENT ON TRIGGER \"" + trigger + "\" ON \"" + table + "\" IS '" + rs.getString("description") + "';";
935 }
936
937 241 alan }
938 } catch (SQLException e) {
939 247 alan System.err.println(e.getMessage());
940 createTrigger = null;
941 241 alan }
942 247 alan if (verbose) {
943 System.out.println(createTrigger);
944 }
945 241 alan return createTrigger;
946 }
947
948 247 alan /**
949 *
950 * @param out
951 * @param table
952 */
953 public void dumpTable(BufferedWriter out, String table) {
954 try {
955 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
956 s.executeQuery("SELECT /*!40001 SQL_NO_CACHE */ * FROM " + table);
957 ResultSet rs = s.getResultSet();
958 241 alan ResultSetMetaData rsMetaData = rs.getMetaData();
959 247 alan if (rs.last()) {
960 241 alan out.write("--\n-- Dumping data for table `" + table + "`\n--\n\n");
961 rs.first();
962 }
963 int columnCount = rsMetaData.getColumnCount();
964 String prefix = new String("INSERT INTO " + table + " (");
965 for (int i = 1; i <= columnCount; i++) {
966 247 alan if (i == columnCount) {
967 241 alan prefix += rsMetaData.getColumnName(i) + ") VALUES(";
968 247 alan } else {
969 241 alan prefix += rsMetaData.getColumnName(i) + ",";
970 }
971 }
972 String postfix = new String();
973 int count = 0;
974 247 alan while (rs.next()) {
975 241 alan postfix = "";
976 for (int i = 1; i <= columnCount; i++) {
977 247 alan if (i == columnCount) {
978 241 alan //System.err.println(rs.getMetaData().getColumnClassName(i));
979 postfix += "'" + rs.getString(i) + "');\n";
980 247 alan } else {
981 241 alan //System.err.println(rs.getMetaData().getColumnTypeName(i));
982 247 alan if (rs.getMetaData().getColumnTypeName(i).equalsIgnoreCase("LONGBLOB")) {
983 try {
984 241 alan postfix += "'" + escapeString(rs.getBytes(i)).toString() + "',";
985 247 alan } catch (Exception e) {
986 241 alan postfix += "NULL,";
987 }
988 247 alan } else {
989 try {
990 241 alan postfix += "'" + escapeString(rs.getBytes(i)).toString() + "',";
991 247 alan } catch (Exception e) {
992 241 alan postfix += "NULL,";
993 }
994 247 alan }
995 }
996 241 alan }
997 out.write(prefix + postfix);
998 ++count;
999 }
1000 247 alan rs.close();
1001 241 alan s.close();
1002 247 alan } catch (IOException e) {
1003 System.err.println(e.getMessage());
1004 } catch (SQLException e) {
1005 System.err.println(e.getMessage());
1006 241 alan }
1007 }
1008
1009 247 alan /**
1010 *
1011 * @param database
1012 * @return
1013 */
1014 241 alan public File dumpAllViews(String database) {
1015 return null;
1016 }
1017
1018 247 alan /**
1019 *
1020 * @param view
1021 * @return
1022 */
1023 241 alan public String dumpCreateView(String view) {
1024 return null;
1025 }
1026
1027 247 alan /**
1028 *
1029 * @param view
1030 * @return
1031 */
1032 241 alan public File dumpView(String view) {
1033 return null;
1034 }
1035
1036 247 alan /**
1037 250 alan * Return a list of databases managed by this instance
1038 247 alan *
1039 250 alan * @return databases
1040 247 alan */
1041 241 alan public ArrayList<String> listDatabases() {
1042 ArrayList<String> databases = new ArrayList();
1043 247 alan try {
1044 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
1045 250 alan s.executeQuery("SELECT * FROM pg_database WHERE datistemplate=false;");
1046 247 alan ResultSet rs = s.getResultSet();
1047 while (rs.next()) {
1048 241 alan databases.add(rs.getString("datname"));
1049 }
1050 } catch (SQLException e) {
1051 247 alan System.err.println(e.getMessage());
1052 241 alan }
1053
1054 return databases;
1055 }
1056
1057 247 alan /**
1058 250 alan * Return a list of schemas within the connected database
1059 247 alan *
1060 250 alan * @return schemas
1061 247 alan */
1062 241 alan public ArrayList<String> listSchemas() {
1063 ArrayList<String> schemas = new ArrayList();
1064 247 alan try {
1065 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
1066 s.executeQuery("SELECT nspname FROM pg_catalog.pg_namespace WHERE " +
1067 241 alan "SUBSTRING(nspname FROM 1 FOR 3) != 'pg_' AND " +
1068 "nspname != 'information_schema'");
1069 247 alan ResultSet rs = s.getResultSet();
1070 while (rs.next()) {
1071 241 alan schemas.add(rs.getString("nspname"));
1072 }
1073 } catch (SQLException e) {
1074 247 alan System.err.println(e.getMessage());
1075 241 alan }
1076 247 alan if (verbose) {
1077 241 alan System.out.println(schemas.toString());
1078 }
1079 return schemas;
1080 }
1081
1082 247 alan /**
1083 250 alan * Return a list of user defined functions within the supplied schema
1084 247 alan *
1085 * @param schema
1086 250 alan * @return functions
1087 247 alan */
1088 241 alan public ArrayList<String> listFunctions(String schema) {
1089 ArrayList<String> functions = new ArrayList();
1090 247 alan try {
1091 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
1092 s.executeQuery("SELECT tableoid, oid, proname, prolang, pronargs, proargtypes, prorettype, " +
1093 241 alan "proacl, pronamespace FROM pg_proc WHERE NOT proisagg AND " +
1094 "pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog') AND " +
1095 "pronamespace = (SELECT oid from pg_namespace WHERE nspname = '" + schema + "')::pg_catalog.oid");
1096 247 alan ResultSet rs = s.getResultSet();
1097 while (rs.next()) {
1098 functions.add(rs.getString("proname"));
1099 }
1100 241 alan } catch (SQLException e) {
1101 247 alan System.err.println(e.getMessage());
1102 241 alan }
1103 247 alan if (verbose) {
1104 241 alan System.out.println(functions.toString());
1105 }
1106 return functions;
1107 }
1108
1109 247 alan /**
1110 250 alan * Returns a list of triggers associated with the supplied schema / table
1111 247 alan *
1112 * @param table
1113 * @param schema
1114 250 alan * @return triggers
1115 247 alan */
1116 241 alan public ArrayList<String> listTriggers(String table, String schema) {
1117 ArrayList<String> triggers = new ArrayList();
1118 247 alan try {
1119 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
1120 s.executeQuery("SELECT tgname, tgfoid::pg_catalog.regproc AS tgfname, tgtype, tgnargs, tgargs, tgenabled, " +
1121 241 alan "tgisconstraint, tgconstrname, tgdeferrable, tgconstrrelid, tginitdeferred, tableoid, oid, " +
1122 "tgconstrrelid::pg_catalog.regclass AS tgconstrrelname FROM pg_catalog.pg_trigger t " +
1123 "WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname = '" + table + "' AND relnamespace = (SELECT oid from pg_namespace " +
1124 "WHERE nspname = '" + schema + "'))::pg_catalog.oid::pg_catalog.oid AND tgconstraint = 0");
1125 247 alan ResultSet rs = s.getResultSet();
1126 while (rs.next()) {
1127 241 alan triggers.add(rs.getString(1));
1128 }
1129 } catch (SQLException e) {
1130 247 alan System.err.println(e.getMessage());
1131 241 alan }
1132 247 alan if (verbose) {
1133 241 alan System.out.println(triggers.toString());
1134 }
1135 return triggers;
1136 }
1137
1138 247 alan /**
1139 250 alan * Returns a list of tables within the supplied schema
1140 247 alan *
1141 * @param schema
1142 250 alan * @return tables
1143 247 alan */
1144 241 alan public ArrayList<String> listTables(String schema) {
1145 ArrayList<String> tables = new ArrayList();
1146 247 alan try {
1147 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
1148 s.executeQuery("SELECT relname FROM pg_class WHERE relkind = 'r' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = '" + schema + "')::pg_catalog.oid");
1149 ResultSet rs = s.getResultSet();
1150 while (rs.next()) {
1151 241 alan tables.add(rs.getString(1));
1152 }
1153 } catch (SQLException e) {
1154 247 alan System.err.println(e.getMessage());
1155 241 alan }
1156 247 alan if (verbose) {
1157 241 alan System.out.println(tables.toString());
1158 }
1159 return tables;
1160 }
1161
1162 250 alan /**
1163 247 alan *
1164 * @return
1165 */
1166 250 alan public String getDatabase() {
1167 return database;
1168 }
1169
1170 /**
1171 * Switch Database - requires existing econnection.
1172 *
1173 * @param schema
1174 */
1175 public void setDatabase(String database) {
1176 try{
1177 conn.close();
1178 connect(this.hostname, this.port, this.username, this.password, database);
1179 this.database = database;
1180 } catch (SQLException se) {
1181 System.err.println(se.getMessage());
1182 }
1183 }
1184
1185 /**
1186 * Return current Schema
1187 *
1188 * @return schema
1189 */
1190 247 alan public String getSchema() {
1191 241 alan return schema;
1192 }
1193
1194 247 alan /**
1195 250 alan * Set schema name
1196 247 alan *
1197 * @param schema
1198 */
1199 public void setSchema(String schema) {
1200 241 alan this.schema = schema;
1201 }
1202
1203 /**
1204 247 alan * Escape string ready for insert via pgsql client
1205 *
1206 * @param bIn String to be escaped passed in as byte array
1207 * @return bOut PostgreSQL compatible insert ready ByteArrayOutputStream
1208 */
1209 private ByteArrayOutputStream escapeString(byte[] bIn) {
1210 241 alan int numBytes = bIn.length;
1211 247 alan ByteArrayOutputStream bOut = new ByteArrayOutputStream(numBytes + 2);
1212 241 alan for (int i = 0; i < numBytes; ++i) {
1213 byte b = bIn[i];
1214
1215 switch (b) {
1216 247 alan case 0: /* Must be escaped for 'mysql' */
1217 241 alan bOut.write('\\');
1218 bOut.write('0');
1219 break;
1220
1221 247 alan case '\n': /* Must be escaped for logs */
1222 241 alan bOut.write('\\');
1223 bOut.write('n');
1224 break;
1225
1226 247 alan case '\r':
1227 241 alan bOut.write('\\');
1228 bOut.write('r');
1229 break;
1230
1231 247 alan case '\\':
1232 241 alan bOut.write('\\');
1233 bOut.write('\\');
1234
1235 break;
1236
1237 247 alan case '\'':
1238 241 alan bOut.write('\\');
1239 bOut.write('\'');
1240
1241 break;
1242
1243 247 alan case '"': /* Better safe than sorry */
1244 241 alan bOut.write('\\');
1245 bOut.write('"');
1246 break;
1247
1248 247 alan case '\032': /* This gives problems on Win32 */
1249 241 alan bOut.write('\\');
1250 bOut.write('Z');
1251 break;
1252
1253 247 alan default:
1254 241 alan bOut.write(b);
1255 }
1256 }
1257 return bOut;
1258 }
1259
1260 247 alan private String getHeader() {
1261 241 alan //return Dump Header
1262 return "-- BinaryStor PostgreSQL Dump " + version + "\n--\n-- Host: " + hostname + " " + "Database: " + schema + "\n-- ------------------------------------------------------\n-- Server Version: " + databaseProductVersion + "\n--";
1263 }
1264
1265 /**
1266 247 alan * Main entry point for PostgreSQLDump when run from command line
1267 *
1268 * @param args Command line arguments
1269 * @throws IOException
1270 */
1271 public static void main(String[] args) throws IOException {
1272 241 alan new PostgreSQLDump().doMain(args);
1273 }
1274
1275 /**
1276 247 alan * Parse command line arguments and run PostgreSQLDump
1277 *
1278 * @param args Command line arguments
1279 * @throws IOException
1280 */
1281 241 alan public void doMain(String[] args) throws IOException {
1282
1283 String usage = "Usage: java -jar PostgreSQLDump.jar [OPTIONS] database [tables]\nOR java -jar PostgreSQLDump.jar [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]\nOR java -jar PostgreSQLDump.jar [OPTIONS] --all-databases [OPTIONS]\nFor more options, use java -jar PostgreSQLDump.jar --help";
1284 CmdLineParser parser = new CmdLineParser(this);
1285
1286 // if you have a wider console, you could increase the value;
1287 // here 80 is also the default
1288 parser.setUsageWidth(80);
1289
1290 try {
1291 // parse the arguments.
1292 parser.parseArgument(args);
1293
1294 if (help) {
1295 throw new CmdLineException("Print Help");
1296 }
1297
1298 // after parsing arguments, you should check
1299 // if enough arguments are given.
1300 247 alan if (arguments.isEmpty()) {
1301 241 alan throw new CmdLineException("No argument is given");
1302 247 alan }
1303 241 alan
1304 247 alan } catch (CmdLineException e) {
1305 if (e.getMessage().equalsIgnoreCase("Print Help")) {
1306 241 alan System.err.println("PostgreSQLDump.java Ver " + version + "\nThis software comes with ABSOLUTELY NO WARRANTY. This is free software,\nand you are welcome to modify and redistribute it under the BSD license" + "\n\n" + usage);
1307 return;
1308 }
1309 // if there's a problem in the command line,
1310 // you'll get this exception. this will report
1311 // an error message.
1312 System.err.println(e.getMessage());
1313 // print usage.
1314 System.err.println(usage);
1315 return;
1316 }
1317
1318
1319 //Do we have a hostname? if not use localhost as default
1320 247 alan if (hostname == null) {
1321 241 alan hostname = "localhost";
1322 }
1323 //First argument here should be database
1324 schema = arguments.remove(0);
1325
1326 247 alan try {
1327 241 alan //Create temporary file to hold SQL output.
1328 File temp = File.createTempFile(schema, ".sql");
1329 BufferedWriter out = new BufferedWriter(new FileWriter(temp));
1330 this.connect(hostname, username, password, schema);
1331 out.write(getHeader());
1332 247 alan for (String arg : arguments) {
1333 241 alan System.out.println(arg);
1334 out.write(dumpCreateTable(arg));
1335 247 alan this.dumpTable(out, arg);
1336 241 alan }
1337 out.flush();
1338 out.close();
1339 this.cleanup();
1340 247 alan } catch (SQLException se) {
1341 System.err.println(se.getMessage());
1342 241 alan }
1343 }
1344
1345 247 alan /**
1346 *
1347 * @return
1348 */
1349 public int cleanup() {
1350 try {
1351 conn.close();
1352 if (verbose) {
1353 System.out.println("Database connection terminated");
1354 241 alan }
1355 247 alan } catch (Exception e) { /* ignore close errors */ }
1356 241 alan return 1;
1357 }
1358 270 alan }