1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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
50
51
52
53
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
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
385
386
387
388
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 }