1 /*
2 Bloof - visualize the evolution of your software project
3 Copyright ( C ) 2003 Lukasz Pekacki <lukasz@pekacki.de>
4 http://bloof.sf.net/
5
6 This program is free software; you can redistribute it and/or modify it
7 under the terms of the GNU General Public License.
8
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU General Public License for more details.
13
14 You should have received a copy of the GNU General Public License along with
15 this program; if not, write to the Free Software Foundation, Inc.,
16 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
17
18 $RCSfile: Database.java,v $
19 Created on $Date: 2003/09/06 08:35:09 $
20 */
21 package net.sf.bloof.db;
22
23 import java.io.BufferedReader;
24 import java.io.IOException;
25 import java.io.InputStreamReader;
26 import java.io.Reader;
27 import java.sql.Connection;
28 import java.sql.PreparedStatement;
29 import java.sql.ResultSet;
30 import java.sql.ResultSetMetaData;
31 import java.sql.SQLException;
32 import java.sql.Statement;
33 import java.sql.Timestamp;
34 import java.util.Date;
35 import java.util.HashSet;
36 import java.util.Hashtable;
37 import java.util.Iterator;
38 import java.util.Vector;
39 import java.util.logging.Logger;
40
41 import net.sf.bloof.metrics.TimeInterval;
42 import net.sf.bloof.scm.FileState;
43 import net.sf.bloof.scm.ScmAccess;
44 import net.sf.bloof.scm.ScmRevision;
45 import net.sf.bloof.scm.ScmRevisionIterator;
46 import net.sf.bloof.scm.cvsplugin.CvsAccess;
47 import net.sf.bloof.scm.cvsplugin.InvalidRepositoryLocationException;
48 import net.sf.bloof.scm.cvsplugin.LoginDetails;
49 import net.sf.bloof.scm.cvsplugin.RepositoryLocation;
50
51 /***
52 * This class manages the interaction with the database via JDBC Connection
53 * @author Lukasz Pekacki <pekacki@users.sourceforge.net>
54 * @version $Id: Database.java,v 1.19 2003/09/06 08:35:09 pekacki Exp $
55 * @todo Externalize SQL statements
56 */
57 public class Database {
58 /***
59 * Sets the connection and fills the hashtables with the already
60 * stored keys in the database
61 * @param aConnection The connection to set
62 * @throws SQLException on connection error
63 */
64 public Database(Connection aConnection) throws SQLException {
65 mConnection = aConnection;
66 mStatement = mConnection.createStatement();
67 mPrepFileAddStatement = mConnection.prepareStatement(SQL_ADD_FILE);
68 mPrepRevisionAddStatement = mConnection.prepareStatement(SQL_ADD_REVISION);
69 mPreparedAddDeveloperStatement = mConnection.prepareStatement(SQL_ADD_DEVELOPER);
70 mPrepAddFileDeletionStatement = mConnection.prepareStatement(SQL_ADD_FILE_DELETION);
71 fillDeveloperHash();
72 fillFilesHash();
73 }
74 /***
75 * Fetches the next query from the input. Returns null if no more queries
76 * available.
77 */
78 private static String nextQuery(Reader aInputReader) throws IOException {
79 StringBuffer query = new StringBuffer();
80 int c = aInputReader.read();
81 boolean command = false;
82 while (c != -1) {
83
84 if (command == true || (c != ' ' && c != '\n' && c != '\r' && c != '\t')) {
85 command = true;
86 }
87
88 query.append((char) c);
89 if (c == ';') {
90 return new String(query);
91 }
92 c = aInputReader.read();
93 }
94 if (command == false) {
95 return null;
96 }
97 return new String(query);
98 }
99
100 /***
101 * Evaluates the input script and outputs the result to the output stream.
102 * Returns the list of Connections established.
103 * @param aScriptFileName name of the Script file
104 * @param aDbConnection connectin to the database
105 * @throws IOException on error
106 */
107 protected static void proccessDBScript(String aScriptFileName, Connection aDbConnection)
108 throws IOException {
109 // Read in the query. The query is everything up to the ';' character
110 // which denotes the end of an SQL command.
111 // ISSUE: It currently does not recognise ';' if used inside a string...
112 Reader inputReader =
113 new BufferedReader(
114 new InputStreamReader(Database.class.getResourceAsStream(aScriptFileName)));
115
116 Statement statement;
117 try {
118 statement = aDbConnection.createStatement();
119 } catch (SQLException e) {
120 sLogger.warning("SQL Error creating statement: " + e.getMessage());
121 return;
122 }
123
124 String query = nextQuery(inputReader);
125 while (query != null) {
126 try {
127 String command = query.substring(0, query.length() - 1).trim().toLowerCase();
128 if (command.startsWith("//")) {
129 sLogger.fine(command);
130 } else {
131 sLogger.finest(query.trim());
132 statement.execute(query);
133 }
134 } catch (SQLException e) {
135 sLogger.warning(
136 "SQL Error running query ( \n" + query + "\n )\nError: " + e.getMessage());
137 }
138
139 // Fetch the next query
140 query = nextQuery(inputReader);
141 }
142 inputReader.close();
143
144 }
145
146 private void addDeletedFile(ScmRevision aRevision) throws SQLException {
147 mPrepAddFileDeletionStatement.setString(1, aRevision.getQualifiedFilename());
148 mPrepAddFileDeletionStatement.setTimestamp(2, new Timestamp(aRevision.getDate().getTime()));
149 mPrepAddFileDeletionStatement.executeUpdate();
150 }
151
152 /***
153 * Add a file into the database
154 * @param aRevision associated Revision
155 */
156 private void addFile(ScmRevision aRevision) throws SQLException {
157 mPrepFileAddStatement.setString(1, aRevision.getQualifiedFilename());
158 mPrepFileAddStatement.setString(2, aRevision.getFileName());
159 mPrepFileAddStatement.executeUpdate();
160 }
161
162 /***
163 * Add a developer to the database
164 * @param aDeveloperName name of the developer
165 */
166 private void addNewDeveloper(String aDeveloperName) throws SQLException {
167 mPreparedAddDeveloperStatement.setString(1, aDeveloperName);
168 mPreparedAddDeveloperStatement.executeUpdate();
169 }
170
171 /***
172 * Adds this revision to the database
173 * @param aRevision
174 */
175 private void addRevision(ScmRevision aRevision) throws SQLException {
176 mSumRevisions++;
177 mPrepRevisionAddStatement.setString(1, aRevision.getQualifiedFilename());
178 mPrepRevisionAddStatement.setTimestamp(2, new Timestamp(aRevision.getDate().getTime()));
179 mPrepRevisionAddStatement.setString(3, aRevision.getAuthor());
180 mPrepRevisionAddStatement.setString(4, aRevision.getRevisionName());
181 mPrepRevisionAddStatement.setInt(5, aRevision.getAdded());
182 mPrepRevisionAddStatement.setInt(6, aRevision.getRemoved());
183 mPrepRevisionAddStatement.setString(7, aRevision.getComment());
184 mPrepRevisionAddStatement.executeUpdate();
185 if (aRevision.getFileState() == FileState.DEAD) {
186 addDeletedFile(aRevision);
187 }
188 }
189
190 /***
191 * Closes the Database
192 * @throws SQLException on error
193 * @throws IOException on error
194 */
195 public void close() throws SQLException, IOException {
196 McKoiControl.close();
197 PostgresControl.close();
198 mConnection.close();
199
200 }
201
202 /***
203 * Returns the ResultSet for a SQL Query
204 * @param aSqlQuery SQL statement to run on the database
205 * @return ResultSet for the specified SQL query
206 * @throws SQLException on sql error
207 */
208 public ResultSet executeQuery(String aSqlQuery) throws SQLException {
209 sLogger.fine("Running query:" + aSqlQuery);
210 return mStatement.executeQuery(aSqlQuery);
211 }
212
213 private void fillDeveloperHash() throws SQLException {
214 mDeveloperNames = new Hashtable();
215 ResultSet developers = mStatement.executeQuery("SELECT login from Developer");
216 while (developers.next()) {
217 String login = developers.getString("login");
218 if (login == null) {
219 return;
220 }
221 mDeveloperNames.put(login, login);
222 }
223 }
224
225 private void fillFilesHash() throws SQLException {
226 mFileNameMaxRevisionDate = new Hashtable();
227 ResultSet files = mStatement.executeQuery("SELECT pathname from File");
228 HashSet filesSet = new HashSet();
229 while (files.next()) {
230 String qualiname = files.getString("pathname");
231 filesSet.add(qualiname);
232 }
233 for (Iterator iter = filesSet.iterator(); iter.hasNext();) {
234 String qualiname = (String) iter.next();
235 String query =
236 "SELECT max(tstamp) as maxdate from Revision where file = '" + qualiname + "' ";
237 ResultSet maxDateRes = mStatement.executeQuery(query);
238
239 maxDateRes.next();
240 Timestamp t = maxDateRes.getTimestamp(1);
241 if (t != null) {
242 Date maxDate = new Date(maxDateRes.getTimestamp(1).getTime());
243 mFileNameMaxRevisionDate.put(qualiname, maxDate);
244 }
245 }
246
247 }
248
249 /***
250 * Formats the ResultSet as plain mono-spaced text and outputs the result as
251 * String
252 * @param aResultSet input set
253 * @return String return
254 * @throws SQLException on error
255 */
256 public String formatResultSet(ResultSet aResultSet) throws SQLException {
257 StringBuffer sb = new StringBuffer();
258 ResultSetMetaData metaData = aResultSet.getMetaData();
259 // Maximum widths of each column.
260 int[] maxWidths = new int[metaData.getColumnCount()];
261 Vector[] data = new Vector[metaData.getColumnCount()];
262 for (int i = 0; i < data.length; ++i) {
263 data[i] = new Vector();
264 }
265 int rowCount = 0;
266
267 for (int i = 0; i < data.length; ++i) {
268 String str = metaData.getColumnLabel(i + 1);
269 maxWidths[i] = Math.max(str.length(), maxWidths[i]);
270 }
271
272 // Read in the data for the result set,
273 while (aResultSet.next()) {
274 for (int i = 0; i < data.length; ++i) {
275 Object ob = aResultSet.getObject(i + 1);
276 String str = "NULL";
277 if (ob != null) {
278 str = ob.toString();
279 }
280 data[i].addElement(str);
281 maxWidths[i] = Math.max(str.length(), maxWidths[i]);
282 }
283 ++rowCount;
284 }
285
286 // Output the data we stored
287 String[] line = new String[data.length];
288
289 sb.append(formatResultWriteBreak(maxWidths));
290 for (int n = 0; n < line.length; ++n) {
291 line[n] = metaData.getColumnLabel(n + 1);
292 }
293 sb.append(formatResultSetRow(maxWidths, line));
294 sb.append(formatResultWriteBreak(maxWidths));
295 for (int i = 0; i < rowCount; ++i) {
296 for (int n = 0; n < line.length; ++n) {
297 line[n] = (String) data[n].elementAt(i);
298 }
299 sb.append(formatResultSetRow(maxWidths, line));
300 }
301 sb.append(formatResultWriteBreak(maxWidths));
302 return sb.toString();
303
304 }
305
306 private String formatResultSetRow(int[] aWidths, String[] aCols) {
307 StringBuffer sb = new StringBuffer();
308 sb.append('|');
309 for (int i = 0; i < aWidths.length; ++i) {
310 String str = aCols[i];
311 sb.append(' ');
312 sb.append(str);
313 // Write padding
314 int wid = (aWidths[i] + 1) - str.length();
315 for (int n = 0; n < wid; ++n) {
316 sb.append(' ');
317 }
318 sb.append('|');
319 }
320 return sb.toString();
321 }
322
323 private String formatResultWriteBreak(int[] aWidths) {
324 String s = new String();
325 s = "+";
326 for (int i = 0; i < aWidths.length; ++i) {
327 int wid = aWidths[i] + 2;
328 for (int n = 0; n < wid; ++n) {
329 s = s + "+";
330 }
331 s = s + "+";
332 }
333 return s;
334 }
335 /***
336 * Returns the StringIterator of all Developers
337 * ordered by name
338 * @return StringIterator of a Select on all Developers
339 * @throws SQLException on sql error
340 */
341 public StringIterator getDevelopersOrderedByName() throws SQLException {
342 Statement stmt = mConnection.createStatement();
343 return new StringIterator(stmt.executeQuery(SQL_GET_DEVELOPER));
344
345 }
346
347 /***
348 * Returns the StringIterator of all Files ( pathname )
349 * ordered by pathname
350 * @return StringIterator of a Select on all Files ( pathname )
351 * ordered by pathname
352 * @throws SQLException on sql error
353 */
354 public StringIterator getFilesOrderdByPath() throws SQLException {
355 Statement stmt = mConnection.createStatement();
356 return new StringIterator(stmt.executeQuery(SQL_GET_FILES));
357
358 }
359
360 /***
361 * Returns a result set that point on the first row. This method is
362 * a shortcut for fetching a SELECT from a database that has only
363 * one row as result, e.g. "SELECHT COUNT( * ) FROM table".
364 * If the request has no result rows, null is returned
365 * @param aSqlQuery the query specified
366 * @return ResultSet
367 * @throws SQLException on sql error
368 */
369 public ResultSet getOnlyRowResultSet(String aSqlQuery) throws SQLException {
370 Statement stmt = mConnection.createStatement();
371 sLogger.fine("Running one row query:" + aSqlQuery);
372 ResultSet rs = stmt.executeQuery(aSqlQuery);
373 if (rs.next()) {
374 return rs;
375 } else {
376 return null;
377 }
378
379 }
380
381 /***
382 * Returns the SCM Access to the project
383 * @return SCM Access to the project
384 * @throws SQLException on db error
385 */
386 public ScmAccess getProjectScmAccess() throws SQLException {
387 ResultSet res = getOnlyRowResultSet(SQL_GET_PROJECTDATA);
388 String pname = res.getString("name");
389 String login = res.getString("login");
390 String password = res.getString("passwd");
391 String location = res.getString("location");
392 String module = res.getString("module");
393 RepositoryLocation relo = null;
394 try {
395 relo = new RepositoryLocation(location);
396 } catch (InvalidRepositoryLocationException ire) {
397 ire.printStackTrace();
398 }
399 LoginDetails logD = new LoginDetails(login, password);
400 CvsAccess cvs = new CvsAccess(relo, logD, module, pname);
401 return cvs;
402 }
403 /***
404 * Returns the maximum TimeInterval on the whole project
405 * @return TimeInterval of the whole project
406 * @throws SQLException on sql error
407 */
408 public TimeInterval getTimespanBoundaries() throws SQLException {
409 if (mWholeTimespan == null) {
410 ResultSet result = getOnlyRowResultSet(SQL_GET_TIME_BOUNDARY);
411 mWholeTimespan = new TimeInterval(result.getTimestamp(1), result.getTimestamp(2));
412 }
413 return mWholeTimespan;
414 }
415 /***
416 * Populates the database with the revisions of the specified
417 * RevisionIterator
418 * Already existing revisions will not be updated in the database
419 * @param aRevisonIter Iterator for revisions
420 * @throws SQLException if errors occur
421 */
422 public void populateDatabase(ScmRevisionIterator aRevisonIter) throws SQLException {
423 mConnection.setAutoCommit(false);
424 mSumRevisions = 0;
425 while (aRevisonIter.hasNext()) {
426 ScmRevision revision = aRevisonIter.getNext();
427 if (!mDeveloperNames.containsKey(revision.getAuthor())) {
428 addNewDeveloper(revision.getAuthor());
429 mDeveloperNames.put(revision.getAuthor(), revision.getAuthor());
430 }
431 if (!mFileNameMaxRevisionDate.containsKey(revision.getQualifiedFilename())) {
432 addFile(revision);
433 mFileNameMaxRevisionDate.put(revision.getQualifiedFilename(), new Date(0));
434 }
435 Date fileMaxDate = (Date) mFileNameMaxRevisionDate.get(revision.getQualifiedFilename());
436 if (revision.getDate().after(fileMaxDate)) {
437 addRevision(revision);
438 }
439 }
440 mConnection.setAutoCommit(true);
441 }
442
443 /***
444 * Updates a project
445 * @param aRevisonIter iterator on the new revisions
446 * @throws SQLException on database error
447 */
448 public void updateDatabase(ScmRevisionIterator aRevisonIter) throws SQLException {
449 mConnection.setAutoCommit(false);
450 mSumRevisions = 0;
451 while (aRevisonIter.hasNext()) {
452 ScmRevision revision = aRevisonIter.getNext();
453 if (!mDeveloperNames.containsKey(revision.getAuthor())) {
454 addNewDeveloper(revision.getAuthor());
455 mDeveloperNames.put(revision.getAuthor(), revision.getAuthor());
456 }
457 if (!mFileNameMaxRevisionDate.containsKey(revision.getQualifiedFilename())) {
458 addFile(revision);
459 mFileNameMaxRevisionDate.put(revision.getQualifiedFilename(), revision.getDate());
460 }
461 Object o = (Object) mFileNameMaxRevisionDate.get(revision.getQualifiedFilename());
462 Date fileMaxDate = (Date) o;
463 if (revision.getDate().after(fileMaxDate)) {
464 addRevision(revision);
465 }
466
467 }
468 mConnection.setAutoCommit(true);
469 mWholeTimespan = null;
470 }
471 /* DDL script files */
472 protected static final String CREATE_TABLE_FILE = "database.ddl",
473 CREATE_INDEX_FILE = "indices.ddl",
474 DROP_TABLE_FILE = "droptables.ddl";
475
476 private static Logger sLogger = Logger.getLogger(Database.class.getName());
477 private static final String SQL_ADD_DEVELOPER = "INSERT INTO Developer ( login ) VALUES ( ? )",
478 SQL_ADD_FILE = "INSERT INTO File ( pathname,name ) VALUES ( ?,? )",
479 SQL_ADD_REVISION =
480 "INSERT INTO Revision ( file,tstamp,developer,version,lines_add,lines_del, description ) "
481 + "VALUES ( ?,?,?,?,?,?,? )",
482 SQL_ADD_FILE_DELETION = "INSERT INTO DeletedFile (file,tstamp) VALUES (?,?)",
483 SQL_GET_DEVELOPER = "SELECT login FROM Developer ORDER BY login",
484 SQL_GET_FILES = "SELECT pathname FROM File ORDER BY pathname",
485 SQL_GET_PROJECTDATA = "SELECT * FROM Proejct",
486 SQL_GET_TIME_BOUNDARY = "SELECT MIN( tstamp ), MAX( tstamp ) FROM Revision";
487 /* internal attributes */
488 private Connection mConnection;
489 private Hashtable mDeveloperNames;
490 private Hashtable mFileNameMaxRevisionDate;
491 private PreparedStatement mPrepAddFileDeletionStatement;
492 private PreparedStatement mPreparedAddDeveloperStatement;
493 private PreparedStatement mPrepFileAddStatement;
494 private PreparedStatement mPrepRevisionAddStatement;
495 private Statement mStatement;
496 /* Database population attributes */
497 private int mSumRevisions = 0;
498 private TimeInterval mWholeTimespan;
499 }
This page was automatically generated by Maven