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.impl;
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  import java.util.Collections;
38  
39  import org.apache.commons.lang.StringUtils;
40  import org.apache.log4j.Logger;
41  
42  import org.riverock.dbrevision.annotation.schema.db.DbDataFieldData;
43  import org.riverock.dbrevision.annotation.schema.db.DbField;
44  import org.riverock.dbrevision.annotation.schema.db.DbForeignKey;
45  import org.riverock.dbrevision.annotation.schema.db.DbPrimaryKey;
46  import org.riverock.dbrevision.annotation.schema.db.DbSequence;
47  import org.riverock.dbrevision.annotation.schema.db.DbTable;
48  import org.riverock.dbrevision.annotation.schema.db.DbView;
49  import org.riverock.dbrevision.annotation.schema.db.DbPrimaryKeyColumn;
50  import org.riverock.dbrevision.db.Database;
51  import org.riverock.dbrevision.db.DatabaseManager;
52  import org.riverock.dbrevision.db.DbPkComparator;
53  import org.riverock.dbrevision.exception.DbRevisionException;
54  import org.riverock.dbrevision.utils.DbUtils;
55  
56  /**
57   * InterBase database connect 
58   * $Author: serg_main $
59   *
60   * $Id: InterbaseDatabase.java 1141 2006-12-14 14:43:29Z serg_main $
61   *
62   */
63  @SuppressWarnings({"UnusedAssignment"})
64  public class InterbaseDatabase extends Database {
65      private static Logger log = Logger.getLogger( InterbaseDatabase.class );
66  
67      /**
68       * get family for this adapter
69       * @return family
70       */
71      public Family getFamily() {
72          return Family.INTERBASE;
73      }
74  
75      public void setBlobField(String tableName, String fieldName, byte[] bytes, String whereQuery, Object[] objects, int[] fieldTyped) {
76          
77      }
78  
79      public InterbaseDatabase(Connection conn) {
80          super(conn);
81      }
82  
83      public int getMaxLengthStringField() {
84          return 4000;
85      }
86  
87      public boolean isBatchUpdate() {
88          return false;
89      }
90  
91      public boolean isNeedUpdateBracket() {
92          return false;
93      }
94  
95      public boolean isByteArrayInUtf8() {
96          return false;
97      }
98  
99      public boolean isSchemaSupports() {
100         return false;  //To change body of implemented methods use File | Settings | File Templates.
101     }
102 
103     public String getDefaultSchemaName(DatabaseMetaData databaseMetaData) {
104         return null;  //To change body of implemented methods use File | Settings | File Templates.
105     }
106 
107     public String getClobField(ResultSet rs, String nameField) {
108         return getClobField(rs, nameField, 20000);
109     }
110 
111     public byte[] getBlobField(ResultSet rs, String nameField, int maxLength) {
112         return null;
113     }
114 
115     public void createTable(DbTable table) {
116         if (table == null || table.getFields().isEmpty() ) {
117             return;
118         }
119 
120         String sql = "create table " + table.getName() + "\n" +
121             "(";
122 
123         boolean isFirst = true;
124 
125         for (DbField field : table.getFields()) {
126             if (!isFirst)
127                 sql += ",";
128             else
129                 isFirst = !isFirst;
130 
131             sql += "\n" + field.getName();
132             switch (field.getJavaType()) {
133 
134                 case Types.NUMERIC:
135                 case Types.DECIMAL:
136                     if (field.getDecimalDigit()!=0) {
137                         sql += " DECIMAL(" + (field.getSize()==null || field.getSize() > 38 ? 38 : field.getSize()) + ',' + field.getDecimalDigit() + ")";
138                     }
139                     else {
140                         if (field.getSize() == 1)
141                             sql += " SMALLINT";
142                         else
143                             sql += " DOUBLE PRECISION";
144                     }
145                     break;
146 
147                 case Types.INTEGER:
148                     sql += " INTEGER";
149                     break;
150 
151                 case Types.DOUBLE:
152                     sql += " DOUBLE";
153                     break;
154 
155                 case Types.CHAR:
156                     sql += " VARCHAR(1)";
157                     break;
158 
159                 case Types.VARCHAR:
160                     sql += " VARCHAR(" + field.getSize() + ")";
161                     break;
162 
163                 case Types.TIMESTAMP:
164                 case Types.DATE:
165                     sql += " DATETIME";
166                     break;
167 
168                 case Types.LONGVARCHAR:
169                     // Oracle 'long' fields type
170                     sql += " VARCHAR(10)";
171                     break;
172 
173                 case Types.LONGVARBINARY:
174                     // Oracle 'long raw' fields type
175                     sql += " LONGVARBINARY";
176                     break;
177 
178                 default:
179                     field.setJavaStringType("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
180                     System.out.println("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
181             }
182 
183             if (field.getDefaultValue() != null) {
184                 String val = field.getDefaultValue().trim();
185 
186                 // TODO rewrite. check only if type is 'date' 
187                 if (DatabaseManager.checkDefaultTimestamp(val)) {
188                     val = "current_timestamp";
189                 }
190 
191                 sql += (" DEFAULT " + val);
192             }
193 
194             if (field.getNullable()==DatabaseMetaData.columnNoNulls) {
195                 sql += " NOT NULL ";
196             }
197         }
198         if (table.getPrimaryKey() != null && table.getPrimaryKey().getColumns().size() != 0) {
199             DbPrimaryKey pk = table.getPrimaryKey();
200 
201             //            constraintDefinition:
202 //            [ CONSTRAINT name ]
203 //            UNIQUE ( column [,column...] ) |
204 //            PRIMARY KEY ( column [,column...] ) |
205 
206             sql += ",\nCONSTRAINT " + pk.getPkName() + " PRIMARY KEY (\n";
207 
208             Collections.sort(pk.getColumns(), DbPkComparator.getInstance());
209             isFirst = true;
210             for (DbPrimaryKeyColumn column : pk.getColumns()) {
211                 if (!isFirst) {
212                     sql += ",";
213                 }
214                 else {
215                     isFirst = !isFirst;
216                 }
217 
218                 sql += column.getColumnName();
219             }
220             sql += "\n)";
221         }
222         sql += "\n)";
223 
224         Statement st = null;
225         try {
226             st = this.getConnection().createStatement();
227             st.execute(sql);
228             int count = st.getUpdateCount();
229             if (log.isDebugEnabled()) {
230                 log.debug("count of processed records " + count);
231             }
232         }
233         catch (SQLException e) {
234             log.error("SQL:\n"+sql);
235             throw new DbRevisionException(e);
236         }
237         finally {
238             DbUtils.close(st);
239             st = null;
240         }
241 
242     }
243 
244     public void createForeignKey(DbTable view) {
245     }
246 
247     public void dropTable(DbTable table) {
248         dropTable(table.getName());
249     }
250 
251     public void dropTable(String nameTable) {
252         if (nameTable == null) {
253             return;
254         }
255 
256         String sql = "drop table " + nameTable;
257 
258         Statement st = null;
259         try {
260             st = this.getConnection().createStatement();
261             st.execute(sql);
262             int count = st.getUpdateCount();
263             if (log.isDebugEnabled())
264                 log.debug("count of deleted object " + count);
265         }
266         catch (SQLException e) {
267             log.error("Error drop table " + nameTable, e);
268             throw new DbRevisionException(e);
269         }
270         finally {
271             DbUtils.close(st);
272             st = null;
273         }
274     }
275 
276     public void dropSequence(String nameSequence) {
277     }
278 
279     public void dropConstraint(DbForeignKey impPk) {
280         if (impPk == null) {
281             return;
282         }
283 
284         String sql = "ALTER TABLE " + impPk.getPkTableName() + " DROP CONSTRAINT " + impPk.getPkName();
285 
286         PreparedStatement ps = null;
287         try {
288             ps = this.getConnection().prepareStatement(sql);
289             ps.executeUpdate();
290         }
291         catch (SQLException e) {
292             throw new DbRevisionException(e);
293         }
294         finally {
295             DbUtils.close(ps);
296             ps = null;
297         }
298     }
299 
300     public void addColumn(DbTable table, DbField field) {
301         String sql = "alter table " + table.getName() + " add " + field.getName() + " ";
302 
303         switch (field.getJavaType()) {
304 
305             case Types.NUMERIC:
306             case Types.DECIMAL:
307                 sql += " DOUBLE PRECISION";
308                 break;
309 
310             case Types.INTEGER:
311                 sql += " INTEGER";
312                 break;
313 
314             case Types.DOUBLE:
315                 sql += " DOUBLE";
316                 break;
317 
318             case Types.CHAR:
319                 sql += " VARCHAR(1)";
320                 break;
321 
322             case Types.VARCHAR:
323                 sql += (" VARCHAR(" + field.getSize() + ") ");
324                 break;
325 
326             case Types.TIMESTAMP:
327             case Types.DATE:
328                 sql += " DATETIME";
329                 break;
330 
331             case Types.LONGVARCHAR:
332                 // Oracle 'long' fields type
333                 sql += " VARCHAR(10)";
334                 break;
335 
336             case Types.LONGVARBINARY:
337                 // Oracle 'long raw' fields type
338                 sql += " LONGVARBINARY";
339                 break;
340 
341             default:
342                 field.setJavaStringType("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
343                 System.out.println("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
344         }
345 
346         if (field.getDefaultValue() != null) {
347             String val = field.getDefaultValue().trim();
348 
349             //TODO rewrite init of def as in createTable
350 //                if (!val.equalsIgnoreCase("null"))
351 //                    val = "'"+val+"'";
352             if (DatabaseManager.checkDefaultTimestamp(val))
353                 val = "current_timestamp";
354 
355             sql += (" DEFAULT " + val);
356         }
357 
358         if (field.getNullable() == DatabaseMetaData.columnNoNulls) {
359             sql += " NOT NULL ";
360         }
361 
362         if (log.isDebugEnabled())
363             log.debug("Interbase addColumn sql - \n" + sql);
364 
365         Statement ps = null;
366         try {
367             ps = this.getConnection().createStatement();
368             ps.executeUpdate(sql);
369             this.getConnection().commit();
370         }
371         catch (SQLException e) {
372             throw new DbRevisionException(e);
373         }
374         finally {
375             DbUtils.close(ps);
376             ps = null;
377         }
378     }
379 
380     public String getOnDeleteSetNull() {
381         return "ON DELETE NO ACTION";
382     }
383 
384     public String getDefaultTimestampValue() {
385         return "current_timestamp";
386     }
387 
388 /*
389 ALTER TABLE table
390 { [ ALTER COLUMN column_name
391     { new_data_type [ ( precision [ , scale ] ) ]
392         [ COLLATE < collation_name > ]
393         [ NULL | NOT NULL ]
394         | {ADD | DROP } ROWGUIDCOL }
395     ]
396     | ADD
397         { [ < column_definition > ]
398         |  column_name AS computed_column_expression
399         } [ ,...n ]
400     | [ WITH CHECK | WITH NOCHECK ] ADD
401         { < table_constraint > } [ ,...n ]
402     | DROP
403         { [ CONSTRAINT ] constraint_name
404             | COLUMN column } [ ,...n ]
405     | { CHECK | NOCHECK } CONSTRAINT
406         { ALL | constraint_name [ ,...n ] }
407     | { ENABLE | DISABLE } TRIGGER
408         { ALL | trigger_name [ ,...n ] }
409 }
410 
411 < column_definition > ::=
412     { column_name data_type }
413     [ [ DEFAULT constant_expression ] [ WITH VALUES ]
414     | [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ]
415         ]
416     [ ROWGUIDCOL ]
417     [ COLLATE < collation_name > ]
418     [ < column_constraint > ] [ ...n ]
419 
420 < column_constraint > ::=
421     [ CONSTRAINT constraint_name ]
422     { [ NULL | NOT NULL ]
423         | [ { PRIMARY KEY | UNIQUE }
424             [ CLUSTERED | NONCLUSTERED ]
425             [ WITH FILLFACTOR = fillfactor ]
426             [ ON { filegroup | DEFAULT } ]
427             ]
428         | [ [ FOREIGN KEY ]
429             REFERENCES ref_table [ ( ref_column ) ]
430             [ ON DELETE { CASCADE | NO ACTION } ]
431             [ ON UPDATE { CASCADE | NO ACTION } ]
432             [ NOT FOR REPLICATION ]
433             ]
434         | CHECK [ NOT FOR REPLICATION ]
435             ( logical_expression )
436     }
437 
438 < table_constraint > ::=
439     [ CONSTRAINT constraint_name ]
440     { [ { PRIMARY KEY | UNIQUE }
441         [ CLUSTERED | NONCLUSTERED ]
442         { ( column [ ,...n ] ) }
443         [ WITH FILLFACTOR = fillfactor ]
444         [ ON {filegroup | DEFAULT } ]
445         ]
446         |    FOREIGN KEY
447             [ ( column [ ,...n ] ) ]
448             REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
449             [ ON DELETE { CASCADE | NO ACTION } ]
450             [ ON UPDATE { CASCADE | NO ACTION } ]
451             [ NOT FOR REPLICATION ]
452         | DEFAULT constant_expression
453             [ FOR column ] [ WITH VALUES ]
454         |    CHECK [ NOT FOR REPLICATION ]
455             ( search_conditions )
456     }
457 
458 */
459 
460     public List<DbView> getViewList(String schemaPattern, String tablePattern) {
461         return DatabaseManager.getViewList(getConnection(), schemaPattern, tablePattern);
462     }
463 
464     public List<DbSequence> getSequnceList(String schemaPattern) {
465         return new ArrayList<DbSequence>();
466     }
467 
468     public String getViewText(DbView view) {
469         return null;
470     }
471 
472     public void createView(DbView view) {
473         if (view == null ||
474             view.getName() == null || view.getName().length() == 0 ||
475             view.getText() == null || view.getText().length() == 0
476         )
477             return;
478 
479         String sql_ =
480             "CREATE VIEW " + view.getName() +
481             " AS " + StringUtils.replace(view.getText(), "||", "+");
482 
483         Statement ps = null;
484         try {
485             ps = this.getConnection().createStatement();
486             ps.execute(sql_);
487         }
488         catch (SQLException e) {
489             String errorString = "Error create view. Error code " + e.getErrorCode() + "\n" + sql_;
490             log.error(errorString, e);
491             throw new DbRevisionException(errorString, e);
492         }
493         finally {
494             DbUtils.close(ps);
495             ps = null;
496         }
497     }
498 
499     public void createSequence(DbSequence seq) {
500     }
501 
502     public void setLongVarbinary(PreparedStatement ps, int index, DbDataFieldData fieldData) {
503         try {
504             ps.setNull(index, Types.VARCHAR);
505         }
506         catch (SQLException e) {
507             throw new DbRevisionException(e);
508         }
509     }
510 
511     public void setLongVarchar(PreparedStatement ps, int index, DbDataFieldData fieldData) {
512         try {
513             ps.setString(index, "");
514         }
515         catch (SQLException e) {
516             throw new DbRevisionException(e);
517         }
518     }
519 
520     public String getClobField(ResultSet rs, String nameField, int maxLength) {
521         return null;
522     }
523 /*
524             CLOB clob = ((OracleResultSet) rs).getCLOB(nameField);
525 
526             if (clob == null)
527                 return null;
528 
529             return clob.getSubString(1, maxLength);
530         }
531 */
532 
533     public boolean testExceptionTableNotFound(Exception e) {
534         if (((SQLException) e).getErrorCode() == 208)
535             return true;
536         return false;
537     }
538 
539     public boolean testExceptionIndexUniqueKey(Exception e, String index) {
540         if (e instanceof SQLException) {
541             if (((SQLException) e).getErrorCode() == -(org.hsqldb.Trace.VIOLATION_OF_UNIQUE_INDEX))
542                 return true;
543         }
544 /*
545         if ((e instanceof SQLException) &&
546                 ((e.toString().indexOf("ORA-00001") != -1) &&
547                 (e.toString().indexOf(index) != -1)))
548 
549             return true;
550 */
551         return false;
552     }
553 
554     public boolean testExceptionIndexUniqueKey(Exception e) {
555         return false;
556     }
557 
558     public boolean testExceptionTableExists(Exception e) {
559         if (e instanceof SQLException) {
560             if (((SQLException) e).getErrorCode() == 335544351)
561                 return true;
562         }
563         return false;
564     }
565 
566     public boolean testExceptionViewExists(Exception e) {
567         if (e instanceof SQLException) {
568             if (((SQLException) e).getErrorCode() == 2714)
569                 return true;
570         }
571         return false;
572     }
573 
574     public boolean testExceptionSequenceExists(Exception e) {
575         return false;
576     }
577 
578     public boolean testExceptionConstraintExists(Exception e) {
579         if (e instanceof SQLException) {
580             if (((SQLException) e).getErrorCode() == -(org.hsqldb.Trace.CONSTRAINT_ALREADY_EXISTS))
581                 return true;
582         }
583         return false;
584     }
585 }