View Javadoc

1   /* $Id: PostgresqlConnector.java,v 1.28 2006/01/12 15:13:08 psionides Exp $ */
2   package net.sourceforge.jdbdump.connect.connectors;
3   
4   import java.sql.DatabaseMetaData;
5   import java.sql.PreparedStatement;
6   import java.sql.ResultSet;
7   import java.sql.SQLException;
8   import java.sql.Statement;
9   import java.util.TreeMap;
10  import java.util.Vector;
11  
12  import net.sourceforge.jdbdump.connect.DatabaseConnectionData;
13  import net.sourceforge.jdbdump.connect.DatabaseConnector;
14  import net.sourceforge.jdbdump.dump.Column;
15  import net.sourceforge.jdbdump.dump.Constraint;
16  import net.sourceforge.jdbdump.dump.Dump;
17  import net.sourceforge.jdbdump.dump.Table;
18  
19  import org.apache.log4j.Logger;
20  
21  /***
22   * This is a class handling connection to the database, an implementation of the abstract
23   * class DatabaseConnector. It is a specific connector, providing connection to
24   * PostgreSQL databases.
25   * 
26   * @see DatabaseConnector
27   * @author jsuder
28   */
29  
30  public class PostgresqlConnector extends DatabaseConnector {
31  
32  	/*** A log4j logger for this class. */
33  	private static Logger logger = Logger.getLogger(PostgresqlConnector.class);
34  
35  	/*** A ResultSet storing table data when it is being downloaded. */
36  	protected ResultSet dataSet = null;
37  
38  	/*** The query used to download next parts of a table into dataSet. */
39  	protected String query = null;
40  	
41  	/*** Number of current record in a table while it is being downloaded. */
42  	protected int position;
43  
44  	/*** Number of columns in the current table. */
45  	protected int columns;
46  
47  	/*** Maximum number of table records downloaded at a time. */
48  	public static final int FETCH_SIZE = 1000;
49  	
50  	/***
51  	 * Opens a connection to the database, using the provided connection data.
52  	 * @param url a correct connection URL
53  	 * @param user user's login in the database
54  	 * @param pass user's password
55  	 * @throws SQLException if the connection is not possible
56  	 * @throws ClassNotFoundException if there is a problem with loading a driver
57  	 */
58  
59  	public void connect(String url, String user, String pass) throws SQLException, ClassNotFoundException {
60  		connect(url, user, pass, "org.postgresql.Driver");
61  	}
62  
63  	/***
64  	 * Creates a backup of the structure of the entire database. It doesn't backup
65  	 * any data.
66  	 * 
67  	 * @todo dump other things like procedures, sequences, etc.
68  	 * @return a Dump object containing information about database structure
69  	 * @throws SQLException if the backup process is interrupted by a critical error
70  	 */
71  
72  	public Dump dump() {
73  		Dump dump = new Dump();
74  		dumpTables(dump);
75  
76  		// TODO: dump other things like procedures, sequences, etc.
77  
78  		dump.setDatabaseReader(this);
79  		return dump;
80  	}
81  
82  	/***
83  	 * Downloads the table's primary key from the database. This method implements
84  	 * the empty method dumpTablePrimaryKeys() in the superclass, so is will be called
85  	 * by superclass's dumpTable() after it retrieves all the columns.
86  	 * 
87  	 * @param table a table which should be checked for primary keys
88  	 * @throws SQLException if the backup process is interrupted by a critical error
89  	 */
90  
91  	protected void dumpTablePrimaryKeys(Table table) throws SQLException {
92  		ResultSet rsKeys = meta.getPrimaryKeys(null, null, table.getName());
93  
94  		if (!rsKeys.next()) {
95  			return;
96  		}
97  		
98  		TreeMap<String, String> pkeys = new TreeMap<String, String>(); 
99  		
100 		String pkname = ""; 
101 		do {
102 			pkeys.put(rsKeys.getString("KEY_SEQ"), rsKeys.getString("COLUMN_NAME"));
103 			pkname = rsKeys.getString("PK_NAME");
104 		} while (rsKeys.next());
105 		
106 		String conText = "CONSTRAINT " + pkname + " PRIMARY KEY (";
107 		boolean first = true;
108 		for (String key : pkeys.keySet()) {
109 			if (!first) {
110 				conText = conText + ", ";
111 			} else {
112 				first = false;
113 			}
114 			conText = conText + pkeys.get(key);
115 		}
116 		conText = conText + ")";
117 		
118 		table.addConstraint(new Constraint("PRIMARY", Constraint.ConstraintType.PRIMARY_KEY, conText));
119 	}
120 
121 	/***
122 	 * Downloads the table's foreign (imported) keys from the database. This method implements
123 	 * the empty method dumpTableForeignKeys() in the superclass, so is will be called
124 	 * by superclass's dumpTable() after it retrieves all the columns.
125 	 * 
126 	 * @param table a table which should be checked for foreign keys
127 	 * @throws SQLException if the backup process is interrupted by a critical error
128 	 */
129 
130 	protected void dumpTableForeignKeys(Table table) throws SQLException {
131 		ResultSet rsKeys = meta.getImportedKeys(null, null, table.getName());
132 
133 		/* the main map stores: foreign key name -> internal map
134 		  the internal map stores: sequence number in a foreign key -> name of the 
135 		     column in foreign key, name of the column in primary key, and
136 		     primary key table name */
137 		TreeMap<String, TreeMap<String, String[]>> fkeys
138 			= new TreeMap<String, TreeMap<String, String[]>>(); 
139 
140 		// read all the key data into the map
141 		while (rsKeys.next()) {
142 			TreeMap<String, String[]> map = fkeys.get(rsKeys.getString("FK_NAME"));
143 			if (map == null) {
144 				map = new TreeMap<String, String[]>();
145 				fkeys.put(rsKeys.getString("FK_NAME"), map);
146 			}
147 			String[] tab = new String[3];
148 			tab[0] = rsKeys.getString("FKCOLUMN_NAME");
149 			tab[1] = rsKeys.getString("PKTABLE_NAME");
150 			tab[2] = rsKeys.getString("PKCOLUMN_NAME");
151 			map.put(rsKeys.getString("KEY_SEQ"), tab);
152 		}
153 		
154 		// create proper CONSTRAINT commands from the key data
155 		for (String fkey : fkeys.keySet()) {
156 			String fkname = fkey;
157 			String pktable = "";
158 			
159 			TreeMap<String, String[]> map = fkeys.get(fkey);
160 			boolean first = true;
161 			String fkcolumns = "", pkcolumns = "";
162 			for (String keyseq : map.keySet()) {
163 				String[] tab = map.get(keyseq);
164 				pktable = tab[1];
165 				if (!first) {
166 					fkcolumns = fkcolumns + ", ";
167 					pkcolumns = pkcolumns + ", ";
168 				} else {
169 					first = false;
170 				}
171 				fkcolumns = fkcolumns + tab[0];
172 				pkcolumns = pkcolumns + tab[2];
173 			}
174 				
175 			String conText = "CONSTRAINT " + fkname + " FOREIGN KEY (" + fkcolumns
176 				+ ") REFERENCES " + pktable + " (" + pkcolumns + ")";
177 			table.addConstraint(new Constraint(fkname,
178 				Constraint.ConstraintType.FOREIGN_KEY, conText));
179 		}
180 	}
181 
182 	/***
183 	 * Downloads the structure of a single table column from the database. This method
184 	 * overrides the method in superclass and will be called by superclass's dumpTable()
185 	 * for each column in a table. The specific behaviour which is needed for PostgreSQL
186 	 * is renaming the 'bpchar' data type to 'char'.
187 	 * 
188 	 * @param column a column which should have its parameters downloaded
189 	 * @param rsColumns a ResultSet returned by DatabaseMetaData.getColumns(),
190 	 *     set on this column's record
191 	 * @throws SQLException if the backup process is interrupted by a critical error
192 	 */
193 
194 	protected void dumpColumn(Column column, ResultSet rsColumns) throws SQLException {
195 		super.dumpColumn(column, rsColumns);
196 		if (column.getType().equals("bpchar")) {
197 			column.setType("char");
198 		}
199 	}
200 
201 	/***
202 	 * Restores the structure of the database from a Dump object loaded from a previously
203 	 * created backup file. It doesn't restore any data.
204 	 * 
205 	 * @todo restore other things like procedures, sequences, etc.
206 	 * @param dump a Dump object containing information about database structure
207 	 * @throws SQLException if the restore process is interrupted by a critical error
208 	 */
209 
210 	public void restore(Dump dump) throws SQLException {
211 		clearDatabase(dump);
212 		for (Table table : dump.getTables()) {
213 			restoreTableStructure(table);
214 			restoreTableData(table);
215 		}
216 		for (Table table : dump.getTables()) {
217 			restoreTableConstraints(table);
218 		}
219 		
220 		// TODO: restore other things like procedures, sequences, etc.
221 	}
222 
223 	/***
224 	 * Clears tables from the database if they already exist, before they are
225 	 * inserted from a dump. This method is called by restore().
226 	 * 
227 	 * @param dump the dump which contains names of the tables which should be cleared
228 	 * @throws SQLException if the clear process is interrupted by a critical error
229 	 */
230 
231 	protected void clearDatabase(Dump dump) throws SQLException {
232 		try {
233 			Statement st = connection.createStatement();
234 			for (Table table : dump.getTables()) {
235 				try {
236 					clearConstraints(table);
237 				} catch (SQLException e) {
238 					// ignore that - this means that table doesn't exist, which is not an error
239 					logger.debug(e);
240 				}
241 			}
242 			for (Table table : dump.getTables()) {
243 				try {
244 					logger.debug("DROP TABLE " + table.getName());
245 					st.executeUpdate("DROP TABLE " + table.getName());
246 				} catch (SQLException e) {
247 					// ignore that - this means that table doesn't exist, which is not an error
248 					logger.debug(e);
249 				}
250 			}
251 		} catch (SQLException e) {
252 			logger.error("Couldn't clear database: " + e);
253 		}
254 	}
255 	
256 	/***
257 	 * Removes constraints from the database's tables (if they exist), before they are
258 	 * dropped from the database and then inserted from a dump. This method is called by
259 	 * clearDatabase() and is needed because it may not be possible to drop some of the
260 	 * tables if they have constraints binding them to other tables.  
261 	 *  
262 	 * @param table the table which contains constraints which should be removed
263 	 * @throws SQLException if the clear process is interrupted by a critical error
264 	 */
265 
266 	protected void clearConstraints(Table table) throws SQLException {
267 		Statement st = connection.createStatement();
268 		
269 		String query = "ALTER TABLE " + table.getName() + " DROP CONSTRAINT ";
270 		Vector<Constraint> constraints = table.getConstraints();
271 		for (int i=0; i<constraints.size(); i++) {
272 			Constraint con = constraints.get(i);
273 			if (con.getType() == Constraint.ConstraintType.PRIMARY_KEY) continue;
274 
275 			logger.debug(query + con.getName());
276 			st.executeUpdate(query + con.getName());
277 		}
278 		
279 		st.close();
280 	}
281 
282 	/***
283 	 * Creates a single table using information from a dump. It restores all the
284 	 * fields, also adds a primary key if there is one, but doesn't upload any data yet
285 	 * and doesn't add constraints other than the primary key.
286 	 * 
287 	 * @param table the table which should be added to the database
288 	 * @throws SQLException if the create process is interrupted by a critical error
289 	 */
290 
291 	protected void restoreTableStructure(Table table) throws SQLException {
292 		try {
293 			Statement st = connection.createStatement();
294 			
295 			// prepare the query
296 			StringBuffer query = new StringBuffer("CREATE TABLE " + table.getName() + " (");
297 	
298 			Vector<Column> columns = table.getColumns();
299 	
300 			// get a list of constraints, but remove everything which is not a primary key
301 			Vector<Constraint> constraints = new Vector<Constraint>();
302 			for (int i=0; i<table.getConstraints().size(); i++) {
303 				Constraint con = table.getConstraints().get(i);
304 				if (con.getType() == Constraint.ConstraintType.PRIMARY_KEY) {
305 					constraints.add(con);
306 				}
307 			}
308 	
309 			// add all the columns
310 			for (int i=0; i<columns.size(); i++) {
311 				Column col = columns.get(i); 
312 				String type = col.getType();
313 				
314 				// type name may consist of many words, but it may be necessary 
315 				// to add a size parameter after the first word
316 				String[] typeWords = type.split(" ");
317 				type = typeWords[0].toUpperCase();
318 				
319 				// insert the first word
320 				query.append(col.getName() + " " + typeWords[0]);
321 	
322 				// for various number or character types, add size parameter 
323 				if (type.equals("BIT") || type.endsWith("CHAR") || type.equals("INTERVAL")
324 					|| type.startsWith("TIME")) {
325 					query.append(" (" + col.getLength() + ")");
326 				} else if ((typeWords.length > 1) && (typeWords[1].toUpperCase().equals("VARYING"))) {
327 					// in 'character varying' or 'bit varying', the size has to be after the
328 					// SECOND word from the name...
329 					query.append(" VARYING ");
330 					typeWords[1] = "";
331 					query.append(" (" + col.getLength() + ")");
332 				} else if (type.equals("DECIMAL") || type.equals("NUMERIC")) {
333 					query.append(" (" + col.getLength() + ", " + col.getDecimalDigits() + ")");
334 				}
335 	
336 				// add the rest of the words
337 				for (int j=1; j<typeWords.length; j++) {
338 					query.append(" " + typeWords[j]);
339 				}
340 				
341 				// add NULL/NOT NULL parameter
342 				if (col.getNullable() == DatabaseMetaData.columnNullable) {
343 					query.append(" NULL");
344 				} else if ((col.getNullable() == DatabaseMetaData.columnNoNulls)
345 						&& (col.getNullable2().equals("NO"))) {
346 					query.append(" NOT NULL");
347 				}
348 	
349 				// add DEFAULT parameter
350 				if (col.getDefault() != null) {
351 					query.append(" DEFAULT " + col.getDefault());
352 				}
353 	
354 				// add other parameters
355 				if (col.getRemarks() != null && col.getRemarks().length() > 0) {
356 					query.append(" " + col.getRemarks());
357 				}
358 	
359 				// add a comma if it is not the end of the query
360 				if ((i != columns.size()-1) || constraints.size() > 0) {
361 					query.append(", ");
362 				}
363 			}
364 	
365 			// now add the primary key
366 			for (int i=0; i<constraints.size(); i++) {
367 				Constraint con = constraints.get(i);
368 				String text = con.getText();
369 				query.append(text);
370 				if (i != constraints.size()-1) {
371 					query.append(", ");
372 				}
373 			}
374 	
375 			// finish building the query
376 			query.append(")");
377 			
378 			// finally, execute the query
379 			logger.debug(query+"");
380 			st.executeUpdate(query+"");
381 			st.close();
382 		} catch (SQLException e) {
383 			logger.error("Couldn't restore the structure of table "
384 				+ table.getName() + ": " + e);
385 		}
386 	}
387 	
388 	/***
389 	 * Restores the table's constraints (e.g. foreign keys). This method is called
390 	 * at the end of restoring process, after the data is uploaded, because otherwise
391 	 * we would have to find a correct order in which to download the data to prevent
392 	 * any conflicts.
393 	 * 
394 	 * @param table the table whose constraints should be added
395 	 * @return SQLException if the constraints can't be added for some reason
396 	 */
397 
398 	protected void restoreTableConstraints(Table table) throws SQLException {
399 		try {
400 			Statement st = connection.createStatement();
401 			
402 			String query = "ALTER TABLE " + table.getName() + " ADD ";
403 			Vector<Constraint> constraints = table.getConstraints();
404 			for (int i=0; i<constraints.size(); i++) {
405 				Constraint con = constraints.get(i);
406 				if (con.getType() != Constraint.ConstraintType.PRIMARY_KEY) {
407 					String text = con.getText();
408 					logger.debug(query + text);
409 					st.executeUpdate(query + text);
410 				}
411 			}
412 			
413 			st.close();
414 		} catch (SQLException e) {
415 			logger.error("Couldn't restore constraints of table " + table.getName() + ": " + e);
416 		}
417 	}
418 
419 	/***
420 	 * Restores the table's data into the database. The data are loaded from
421 	 * the backup file record after record just before they are uploaded into
422 	 * the database.
423 	 * 
424 	 * @param table the table whose data should be uploaded
425 	 * @throws SQLException when the data can't be uploaded
426 	 */
427 
428 	protected void restoreTableData(Table table) throws SQLException {
429 		try {
430 			table.initializeData();
431 	
432 			// prepare the query
433 			StringBuffer query = new StringBuffer("INSERT INTO " + table.getName() + " VALUES (");
434 			int columns = table.getColumns().size();
435 			for (int i=0; i<columns; i++) {
436 				Column col = table.getColumns().get(i);
437 				if (i != 0) {
438 					query.append(", ?");
439 				} else {
440 					query.append("?");
441 				}
442 			}
443 			query.append(")");
444 			logger.debug(query+"");
445 			
446 			// create a PreparedStatement to speed up the insert process
447 			PreparedStatement stat = connection.prepareStatement(query+""); 
448 	
449 			// insert all records until the end of the table
450 			while (true) {
451 				try {
452 					String[] record = table.getDataLine();
453 					if (record == null) {
454 						// end of the table
455 						break;
456 					}
457 					
458 					// execute the PreparedStatement
459 					for (int i=0; i<columns; i++) {
460 						stat.setString(i+1, record[i]);
461 					}
462 					stat.executeUpdate();
463 				} catch (Exception e) {
464 					logger.error("Can't read record of data from the dump: " + e);
465 					return;
466 				}
467 			}
468 		} catch (SQLException e) {
469 			logger.error("Couldn't restore data of table " + table.getName() + ": " + e);
470 		}
471 	}
472 
473 	/***
474 	 * Creates a PostgreSQL-specific JDBC connection url (it has the form:
475 	 * jdbc:postgresql://host:port/database).
476 	 * @param data an object providing all the data necessary to open the connection 
477 	 * @return a connection url string generated from the data 
478 	 */
479 
480 	public String createURL(DatabaseConnectionData data) {
481 		String port = data.getPort();
482 		String portStr;
483 
484 		try {
485 			int portNr = Integer.parseInt(port);
486 			portStr = ":" + port;
487 		} catch (NumberFormatException e) {
488 			portStr = "";
489 		}
490 		
491 		return "jdbc:postgresql://" + data.getServer() + portStr + "/" + data.getDatabaseName();
492 	}
493 
494 	/***
495 	 * Prepares the given table for downloading its data. That means, it runs a query
496 	 * "SELECT * FROM tablename" and stores the received result set.
497 	 * @param tableName name of the table from which data should be downloaded
498 	 * @throw SQLException if the resultset can't be returned
499 	 */
500 
501 	public void initializeTableData(String tableName) throws SQLException {
502 		Statement statement = connection.createStatement();
503 
504 		query = "SELECT * FROM " + tableName;
505 		dataSet = statement.executeQuery(query + " LIMIT " + FETCH_SIZE);
506 		columns = dataSet.getMetaData().getColumnCount();
507 		position = FETCH_SIZE;
508 	}
509 
510 	/***
511 	 * Downloads one record of data from the previously initialized table in database.
512 	 * @return next record from the table, or null if there are no more records
513 	 * @throws SQLException if data record can't be downloaded because of a connection error
514 	 *     or an error in the database
515 	 */
516 
517 	public String[] getTableDataLine() throws SQLException { 
518 		if (dataSet.next()) {
519 			String[] dataLine = new String[columns];
520 			for (int i=0; i<columns; i++) {
521 				dataLine[i] = dataSet.getString(i+1);
522 			}
523 			return dataLine;
524 		} else {
525 			Statement statement = connection.createStatement();
526 			dataSet = statement.executeQuery(query + " LIMIT " + FETCH_SIZE + " OFFSET " + position);
527 			position += FETCH_SIZE;
528 			
529 			if (dataSet.next()) {
530 				String[] dataLine = new String[columns];
531 				for (int i=0; i<columns; i++) {
532 					dataLine[i] = dataSet.getString(i+1);
533 				}
534 				return dataLine;
535 			} else {
536 				logger.info("getTableDataLine(): no more data lines in the table.");
537 				return null; // end of data
538 			}
539 		}
540 	}
541 
542 }