View Javadoc

1   /*
2    * org.riverock.dbrevision - Database revision engine
3    * For more information about DbRevision, please visit project site
4    * http://www.riverock.org
5    *
6    * Copyright (C) 2006-2006, Riverock Software, All Rights Reserved.
7    *
8    * Riverock - The Open-source Java Development Community
9    * http://www.riverock.org
10   *
11   *
12   * This library is free software; you can redistribute it and/or
13   * modify it under the terms of the GNU Lesser General Public
14   * License as published by the Free Software Foundation; either
15   * version 2.1 of the License, or (at your option) any later version.
16   *
17   * This library is distributed in the hope that it will be useful,
18   * but WITHOUT ANY WARRANTY; without even the implied warranty of
19   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
20   * Lesser General Public License for more details.
21   *
22   * You should have received a copy of the GNU Lesser General Public
23   * License along with this library; if not, write to the Free Software
24   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
25   */
26  package org.riverock.dbrevision.db;
27  
28  import java.sql.Connection;
29  import java.sql.DatabaseMetaData;
30  import java.sql.PreparedStatement;
31  import java.sql.ResultSet;
32  import java.sql.SQLException;
33  import java.sql.Statement;
34  import java.sql.Types;
35  import java.util.ArrayList;
36  import java.util.List;
37  
38  import org.apache.log4j.Logger;
39  
40  import org.riverock.dbrevision.annotation.schema.db.DbField;
41  import org.riverock.dbrevision.annotation.schema.db.DbPrimaryKey;
42  import org.riverock.dbrevision.annotation.schema.db.DbSchema;
43  import org.riverock.dbrevision.annotation.schema.db.DbTable;
44  import org.riverock.dbrevision.annotation.schema.db.DbView;
45  import org.riverock.dbrevision.exception.DbRevisionException;
46  import org.riverock.dbrevision.utils.DbUtils;
47  
48  /**
49   * User: Admin
50   * Date: Aug 30, 2003
51   * Time: 5:07:17 PM
52   * <p/>
53   * $Id: DatabaseManager.java 1141 2006-12-14 14:43:29Z serg_main $
54   */
55  @SuppressWarnings({"UnusedAssignment"})
56  public final class DatabaseManager {
57      private static Logger log = Logger.getLogger(DatabaseManager.class);
58  
59      private static final String DEFAULT_DATE_VALUES[] = {"sysdate", "current_timestamp", "current_time", "current_date"};
60  
61      public static void addPrimaryKey(final Database db_, final DbTable table, final DbPrimaryKey pk) {
62          if (table == null) {
63              String s = "Add primary key failed - table object is null";
64              System.out.println(s);
65              if (log.isInfoEnabled()) {
66                  log.info(s);
67              }
68  
69              return;
70          }
71  
72          DbPrimaryKey checkPk = DatabaseStructureManager.getPrimaryKey(db_, table.getSchema(), table.getName());
73  
74          if (checkPk != null && checkPk.getColumns().size() != 0) {
75              String s = "primary key already exists";
76              System.out.println(s);
77              if (log.isInfoEnabled()) {
78                  log.info(s);
79              }
80  
81              return;
82          }
83  
84          String tempTable = table.getName() + '_' + table.getName();
85          duplicateTable(db_, table, tempTable);
86          db_.dropTable(table);
87          table.setPrimaryKey(pk);
88          db_.createTable(table);
89          copyData(db_, table, tempTable, table.getName());
90          db_.dropTable(tempTable);
91      }
92  
93      public static void copyData(
94          final Database db_, final DbTable fieldsList, final String sourceTable, final String targetTableName
95      ) {
96          if (fieldsList == null || sourceTable == null || targetTableName == null) {
97              if (log.isInfoEnabled()) {
98                  log.info("copy data failed, some objects is null");
99              }
100 
101             return;
102         }
103 
104         String fields = "";
105         boolean isNotFirst = false;
106         for (DbField DbField : fieldsList.getFields()) {
107             if (isNotFirst) {
108                 fields += ", ";
109             }
110             else {
111                 isNotFirst = true;
112             }
113             fields += DbField.getName();
114         }
115 
116         String sql_ =
117             "insert into " + targetTableName +
118                 "(" + fields + ")" +
119                 (db_.isNeedUpdateBracket() ? "(" : "") +
120                 "select " + fields + " from " + sourceTable +
121                 (db_.isNeedUpdateBracket() ? ")" : "");
122 
123         Statement ps = null;
124         try {
125             ps = db_.getConnection().createStatement();
126             ps.execute(sql_);
127         }
128         catch (SQLException e) {
129             String errorString = "Error copy data from table '" + sourceTable +
130                 "' to '" + targetTableName + "' " + e.getErrorCode() + "\nsql - " + sql_;
131 
132             log.error(errorString, e);
133             System.out.println(errorString);
134             throw new DbRevisionException(e);
135         }
136         finally {
137             DbUtils.close(ps);
138             ps = null;
139         }
140     }
141 
142     public static void duplicateTable(final Database db_, final DbTable srcTable, final String targetTableName) {
143         if (srcTable == null) {
144             log.error("duplicate table failed, source table object is null");
145             return;
146         }
147 
148         DbTable tempTable = DatabaseStructureManager.cloneDescriptionTable(srcTable);
149         tempTable.setName(targetTableName);
150         tempTable.setPrimaryKey(null);
151         tempTable.setData(null);
152 
153         db_.createTable(tempTable);
154         copyData(db_, tempTable, srcTable.getName(), targetTableName);
155     }
156 
157     public static DbField getFieldFromStructure(final DbSchema schema, final String tableName, final String fieldName) {
158         if (schema == null || tableName == null || fieldName == null) {
159             return null;
160         }
161 
162         for (DbTable DbTable : schema.getTables()) {
163             if (tableName.equalsIgnoreCase(DbTable.getName())) {
164                 for (DbField DbField : DbTable.getFields()) {
165                     if (fieldName.equalsIgnoreCase(DbField.getName())) {
166                         return DbField;
167                     }
168                 }
169             }
170 
171         }
172         return null;
173     }
174 
175     // cheak what 'tableName' is a table or a view
176     public static DbTable getTableFromStructure(final DbSchema schema, final String tableName) {
177         if (schema == null || tableName == null) {
178             return null;
179         }
180 
181         for (DbTable checkTable : schema.getTables()) {
182             if (tableName.equalsIgnoreCase(checkTable.getName())) {
183                 return checkTable;
184             }
185         }
186         return null;
187     }
188 
189     public static DbView getViewFromStructure(final DbSchema schema, final String viewName) {
190         if (schema == null || viewName == null) {
191             return null;
192         }
193 
194         for (DbView checkView : schema.getViews()) {
195             if (viewName.equalsIgnoreCase(checkView.getName())) {
196                 return checkView;
197             }
198         }
199         return null;
200     }
201 
202     public static boolean isFieldExists(final DbSchema schema, final DbTable table, final DbField field) {
203         if (schema == null || table == null || field == null) {
204             return false;
205         }
206 
207         for (DbTable DbTable : schema.getTables()) {
208             if (table.getName().equalsIgnoreCase(DbTable.getName())) {
209                 for (DbField DbField : DbTable.getFields()) {
210                     if (field.getName().equalsIgnoreCase(DbField.getName())) {
211                         return true;
212                     }
213                 }
214             }
215         }
216         return false;
217     }
218 
219     public static boolean isTableExists(final DbSchema schema, final DbTable table) {
220         if (schema == null || table == null) {
221             return false;
222         }
223 
224         for (DbTable DbTable : schema.getTables()) {
225             if (table.getName().equalsIgnoreCase(DbTable.getName())) {
226                 return true;
227             }
228         }
229         return false;
230     }
231 
232     public static DbSchema getDbStructure(Database adapter) {
233         return getDbStructure(adapter, true);
234     }
235 
236     public static DbSchema getDbStructure(Database adapter, boolean isOnlyCurrent) {
237         DbSchema schema = new DbSchema();
238 
239         String dbSchema;
240         if (isOnlyCurrent) {
241             try {
242                 DatabaseMetaData metaData = adapter.getConnection().getMetaData();
243                 dbSchema = metaData.getUserName();
244             }
245             catch (SQLException e) {
246                 throw new DbRevisionException("Error get metadata", e);
247             }
248         }
249         else {
250             dbSchema = "%";
251         }
252 
253         List<DbTable> list = DatabaseStructureManager.getTableList(adapter.getConnection(), dbSchema, "%");
254         for (DbTable table : list) {
255             schema.getTables().add(table);
256         }
257         schema.getViews().addAll(adapter.getViewList(dbSchema, "%"));
258         schema.getSequences().addAll(adapter.getSequnceList(dbSchema));
259 
260         for (DbTable table : schema.getTables()) {
261             table.getFields().addAll(DatabaseStructureManager.getFieldsList(adapter, table.getSchema(), table.getName()));
262             table.setPrimaryKey(DatabaseStructureManager.getPrimaryKey(adapter, table.getSchema(), table.getName()));
263             table.getForeignKeys().addAll(DatabaseStructureManager.getForeignKeys(adapter, table.getSchema(), table.getName()));
264             table.getIndexes().addAll(DatabaseStructureManager.getIndexes(adapter, table.getSchema(), table.getName()));
265         }
266 
267         for (DbView view : schema.getViews()) {
268             view.setText(adapter.getViewText(view));
269         }
270 
271         return schema;
272     }
273 
274     public static void createWithReplaceAllView(final Database adapter, final DbSchema millSchema) {
275         boolean[] idx = new boolean[millSchema.getViews().size()];
276         for (int i = 0; i < idx.length; i++) {
277             idx[i] = false;
278         }
279 
280         for (boolean anIdx : idx) {
281             if (anIdx) {
282                 continue;
283             }
284 
285             for (int i = 0; i < idx.length; i++) {
286                 if (idx[i]) {
287                     continue;
288                 }
289 
290                 DbView view = millSchema.getViews().get(i);
291                 try {
292                     adapter.createView(view);
293                     idx[i] = true;
294                 }
295                 catch (Exception e) {
296                     if (adapter.testExceptionViewExists(e)) {
297                         try {
298                             DatabaseStructureManager.dropView(adapter, view);
299                         }
300                         catch (Exception e1) {
301                             String es = "Error drop view";
302                             log.error(es, e1);
303                             throw new DbRevisionException(es, e1);
304                         }
305 
306                         try {
307                             adapter.createView(view);
308                             idx[i] = true;
309                         }
310                         catch (Exception e1) {
311                             String es = "Error create view";
312                             log.error(es, e1);
313                             throw new DbRevisionException(es, e1);
314                         }
315                     }
316                 }
317             }
318         }
319     }
320 
321     public static List<DbView> getViewList(final Connection conn, final String schemaPattern, final String tablePattern) {
322         String[] types = {"VIEW"};
323 
324         ResultSet meta = null;
325         List<DbView> v = new ArrayList<DbView>();
326         try {
327             DatabaseMetaData dbMeta = conn.getMetaData();
328 
329             meta = dbMeta.getTables(
330                 null,
331                 schemaPattern,
332                 tablePattern,
333                 types
334             );
335 
336             while (meta.next()) {
337 
338                 DbView table = new DbView();
339 
340                 table.setSchema(meta.getString("TABLE_SCHEM"));
341                 table.setName(meta.getString("TABLE_NAME"));
342                 table.setType(meta.getString("TABLE_TYPE"));
343                 table.setRemark(meta.getString("REMARKS"));
344 
345                 if (log.isDebugEnabled()) {
346                     log.debug("View - " + table.getName() + "  remak - " + table.getRemark());
347                 }
348 
349                 v.add(table);
350             }
351         }
352         catch (Exception e) {
353             String es = "Error get list of view";
354             log.error(es, e);
355         }
356         return v;
357     }
358 
359     public static boolean isSkipTable(final String table) {
360         if (table == null) {
361             return false;
362         }
363 
364         String s = table.trim();
365 
366         String fullCheck[] = {"SQLN_EXPLAIN_PLAN", "DBG", "CHAINED_ROWS"};
367         for (String aFullCheck : fullCheck) {
368             if (aFullCheck.equalsIgnoreCase(s)) {
369                 return true;
370             }
371         }
372 
373         String startCheck[] = {"F_D_", "FOR_DEL_", "F_DEL_", "FOR_D_"};
374         for (String aStartCheck : startCheck) {
375             if (s.toLowerCase().startsWith(aStartCheck.toLowerCase())) {
376                 return true;
377             }
378         }
379 
380         return false;
381     }
382 
383     /**
384      * Check what field's default value is default timestamp(date) for bd column
385      * For example for Oracle value is 'SYSDATE'
386      *
387      * @param val value for DEFAULT_DATE_VALUES
388      * @return true, if value is date, otherwise false
389      */
390     public static boolean checkDefaultTimestamp(final String val) {
391         if (val == null) {
392             return false;
393         }
394 
395         String s = val.trim().toLowerCase();
396         for (String aCheck : DEFAULT_DATE_VALUES) {
397             if (aCheck.equalsIgnoreCase(s)) {
398                 return true;
399             }
400         }
401         return false;
402     }
403 
404     public static int sqlTypesMapping(final String type) {
405         if (type == null) {
406             return Types.OTHER;
407         }
408 
409         if ("BIT".equals(type)) {
410             return Types.BIT;
411         }
412         else if ("TINYINT".equals(type)) {
413             return Types.TINYINT;
414         }
415         else if ("SMALLINT".equals(type)) {
416             return Types.SMALLINT;
417         }
418         else if ("INTEGER".equals(type)) {
419             return Types.INTEGER;
420         }
421         else if ("BIGINT".equals(type)) {
422             return Types.BIGINT;
423         }
424         else if ("FLOAT".equals(type)) {
425             return Types.FLOAT;
426         }
427         else if ("REAL".equals(type)) {
428             return Types.REAL;
429         }
430         else if ("DOUBLE".equals(type)) {
431             return Types.DOUBLE;
432         }
433         else if ("NUMERIC".equals(type)) {
434             return Types.NUMERIC;
435         }
436         else if ("DECIMAL".equals(type)) {
437             return Types.DECIMAL;
438         }
439         else if ("NUMBER".equals(type)) {
440             return Types.DECIMAL;
441         }
442         else if ("CHAR".equals(type)) {
443             return Types.CHAR;
444         }
445         else if ("VARCHAR".equals(type)) {
446             return Types.VARCHAR;
447         }
448         else if ("LONGVARCHAR".equals(type)) {
449             return Types.LONGVARCHAR;
450         }
451         else if ("DATE".equals(type)) {
452             return Types.DATE;
453         }
454         else if ("TIME".equals(type)) {
455             return Types.TIME;
456         }
457         else if ("TIMESTAMP".equals(type)) {
458             return Types.TIMESTAMP;
459         }
460         else if ("BINARY".equals(type)) {
461             return Types.BINARY;
462         }
463         else if ("VARBINARY".equals(type)) {
464             return Types.VARBINARY;
465         }
466         else if ("LONGVARBINARY".equals(type)) {
467             return Types.LONGVARBINARY;
468         }
469         else if ("NULL".equals(type)) {
470             return Types.NULL;
471         }
472         else if ("OTHER".equals(type)) {
473             return Types.OTHER;
474         }
475         else if ("JAVA_OBJECT".equals(type)) {
476             return Types.JAVA_OBJECT;
477         }
478         else if ("DISTINCT".equals(type)) {
479             return Types.DISTINCT;
480         }
481         else if ("STRUCT".equals(type)) {
482             return Types.STRUCT;
483         }
484         else if ("ARRAY".equals(type)) {
485             return Types.ARRAY;
486         }
487         else if ("BLOB".equals(type)) {
488             return Types.BLOB;
489         }
490         else if ("CLOB".equals(type)) {
491             return Types.CLOB;
492         }
493         else if ("REF".equals(type)) {
494             return Types.REF;
495         }
496         else {
497             return Types.OTHER;
498         }
499 
500     }
501 
502     public static List<Long> getLongValueList(final Database db, final String sql, final Object[] params, final int[] types) {
503 
504         Statement stmt = null;
505         PreparedStatement pstm;
506         ResultSet rs = null;
507         List<Long> list = new ArrayList<Long>();
508         try {
509             if (params == null) {
510                 stmt = db.getConnection().createStatement();
511                 rs = stmt.executeQuery(sql);
512             }
513             else {
514                 pstm = db.getConnection().prepareStatement(sql);
515                 for (int i = 0; i < params.length; i++) {
516                     if (types == null) {
517                         pstm.setObject(i + 1, params[i]);
518                     }
519                     else {
520                         pstm.setObject(i + 1, params[i], types[i]);
521                     }
522                 }
523                 rs = pstm.executeQuery();
524                 stmt = pstm;
525             }
526 
527             while (rs.next()) {
528                 long tempLong = rs.getLong(1);
529                 if (rs.wasNull()) {
530                     continue;
531                 }
532 
533                 list.add(tempLong);
534             }
535             return list;
536         }
537         catch (SQLException e) {
538             log.error("error getting long value fron sql '" + sql + "'", e);
539             throw new DbRevisionException(e);
540         }
541         finally {
542             DbUtils.close(rs, stmt);
543             rs = null;
544             stmt = null;
545             pstm = null;
546         }
547     }
548 
549     public static List<Long> getIdByList(final Database adapter, final String sql, final Object[] param) {
550         Statement stmt = null;
551         PreparedStatement pstm;
552         ResultSet rs = null;
553         List<Long> list = new ArrayList<Long>();
554         try {
555             if (param == null) {
556                 stmt = adapter.getConnection().createStatement();
557                 rs = stmt.executeQuery(sql);
558             }
559             else {
560                 pstm = adapter.getConnection().prepareStatement(sql);
561                 for (int i = 0; i < param.length; i++) {
562                     pstm.setObject(i + 1, param[i]);
563                 }
564 
565                 rs = pstm.executeQuery();
566                 stmt = pstm;
567             }
568 
569             while (rs.next()) {
570                 long tempLong = rs.getLong(1);
571                 if (rs.wasNull()) {
572                     continue;
573                 }
574 
575                 list.add(tempLong);
576             }
577             return list;
578         }
579         catch (SQLException e) {
580             final String es = "error getting long value fron sql '" + sql + "'";
581             log.error(es, e);
582             throw new RuntimeException(es, e);
583         }
584         finally {
585             DbUtils.close(rs, stmt);
586             rs = null;
587             stmt = null;
588             pstm = null;
589         }
590     }
591 }