SqlServerDatabase xref

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