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.impl;
27
28 import java.io.ByteArrayInputStream;
29 import java.io.ByteArrayOutputStream;
30 import java.io.InputStream;
31 import java.sql.Blob;
32 import java.sql.Connection;
33 import java.sql.DatabaseMetaData;
34 import java.sql.PreparedStatement;
35 import java.sql.ResultSet;
36 import java.sql.SQLException;
37 import java.sql.Statement;
38 import java.sql.Types;
39 import java.util.ArrayList;
40 import java.util.Collections;
41 import java.util.List;
42
43 import org.apache.commons.codec.binary.Base64;
44 import org.apache.commons.lang.StringUtils;
45 import org.apache.log4j.Logger;
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
63
64
65
66
67
68 @SuppressWarnings({"UnusedAssignment"})
69 public final class MySqlDatabase extends Database {
70 private final static Logger log = Logger.getLogger(MySqlDatabase.class);
71
72
73
74
75
76 public Family getFamily() {
77 return Family.MYSQL;
78 }
79
80 public void setBlobField(String tableName, String fieldName, byte[] bytes, String whereQuery, Object[] objects, int[] fieldTyped) {
81 PreparedStatement ps = null;
82 ResultSet rs = null;
83 try {
84 ps = getConnection().prepareStatement(
85 "update "+tableName+" set "+fieldName+"=? where "+ whereQuery
86 );
87 int idx=1;
88 ps.setBinaryStream(idx++, new ByteArrayInputStream(bytes), bytes.length);
89 for (int i=0; i<objects.length; i++) {
90 if (objects[i]!=null) {
91 ps.setObject(idx++, objects[i], fieldTyped[i]);
92 }
93 else {
94 ps.setNull(idx++, fieldTyped[i]);
95 }
96 }
97 ps.executeUpdate();
98 }
99 catch (SQLException e) {
100 throw new DbRevisionException(e);
101 }
102 finally {
103 DbUtils.close(rs, ps);
104 rs=null;
105 ps=null;
106 }
107 }
108
109 public MySqlDatabase(Connection conn) {
110 super(conn);
111 }
112
113 public int getMaxLengthStringField() {
114 return 65535;
115 }
116
117 public boolean isBatchUpdate() {
118 return false;
119 }
120
121 public boolean isNeedUpdateBracket() {
122 return true;
123 }
124
125 public boolean isByteArrayInUtf8() {
126 return false;
127 }
128
129 public boolean isSchemaSupports() {
130 return true;
131 }
132
133 public String getDefaultSchemaName(DatabaseMetaData databaseMetaData) {
134 try {
135 return databaseMetaData.getUserName();
136 }
137 catch (SQLException e) {
138 throw new DbRevisionException(e);
139 }
140 }
141
142 public String getClobField(ResultSet rs, String nameField) {
143 return getClobField(rs, nameField, 20000);
144 }
145
146 public byte[] getBlobField(ResultSet rs, String nameField, int maxLength) {
147 try {
148 Blob blob = rs.getBlob(nameField);
149 ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
150 int count;
151 byte buffer[] = new byte[1024];
152
153 InputStream inputStream = blob.getBinaryStream();
154 while ((count = inputStream.read(buffer)) >= 0) {
155 outputStream.write(buffer, 0, count);
156 outputStream.flush();
157 }
158 outputStream.close();
159 return outputStream.toByteArray();
160 }
161 catch (Exception e) {
162 throw new DbRevisionException(e);
163 }
164 }
165
166 public void createTable(DbTable table) {
167 if (table == null || table.getFields().isEmpty()) {
168 return;
169 }
170
171 String sql = "create table " + table.getName() + "\n" +
172 "(";
173
174 boolean isFirst = true;
175
176 for (DbField field : table.getFields()) {
177 if (!isFirst) {
178 sql += ",";
179 }
180 else {
181 isFirst = !isFirst;
182 }
183
184 sql += "\n" + field.getName() + "";
185 int fieldType = field.getJavaType();
186 switch (fieldType) {
187
188 case Types.BIT:
189 sql += " BIT";
190 break;
191
192 case Types.TINYINT:
193 sql += " TINYINT";
194 break;
195
196 case Types.BIGINT:
197 sql += " BIGINT";
198 break;
199
200 case Types.NUMERIC:
201 case Types.DECIMAL:
202 if (field.getDecimalDigit() == null || field.getDecimalDigit() == 0)
203 sql += " DECIMAL";
204 else
205 sql += " DECIMAL(" + (field.getSize()==null || field.getSize()>31?31:field.getSize()) + "," + field.getDecimalDigit() + ")";
206 break;
207
208 case Types.INTEGER:
209 sql += " INTEGER";
210 break;
211
212 case Types.DOUBLE:
213 if (field.getDecimalDigit() == null || field.getDecimalDigit() == 0)
214 sql += " DOUBLE";
215 else
216 sql += " DOUBLE(" + (field.getSize()==null || field.getSize()>31?31:field.getSize()) + "," + field.getDecimalDigit() + ")";
217 break;
218
219 case Types.CHAR:
220 sql += " VARCHAR(1)";
221 break;
222
223 case Types.VARCHAR:
224 if (field.getSize() < 0x5555)
225 sql += " VARCHAR(" + field.getSize() + ")";
226 else
227 sql += " TEXT";
228 break;
229
230 case Types.TIMESTAMP:
231 case Types.DATE:
232 sql += " TIMESTAMP";
233 break;
234
235 case Types.LONGVARCHAR:
236 sql += " text ";
237 break;
238
239 case Types.LONGVARBINARY:
240 case Types.BLOB:
241 sql += " LONGBLOB";
242 break;
243
244 case Types.CLOB:
245 sql += " LONGTEXT";
246 break;
247
248 case Types.OTHER:
249 sql += " LONGTEXT";
250 break;
251
252 default:
253 field.setJavaStringType("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
254 System.out.println("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
255 }
256
257 if (field.getDefaultValue() != null) {
258 String val = field.getDefaultValue().trim();
259
260 if (StringUtils.isNotBlank(val)) {
261 switch (fieldType) {
262 case Types.CHAR:
263 case Types.VARCHAR:
264 val = "'" + val + "'";
265 break;
266 case Types.TIMESTAMP:
267 case Types.DATE:
268 if (DatabaseManager.checkDefaultTimestamp(val)) {
269 val = "CURRENT_TIMESTAMP";
270 }
271
272 break;
273 default:
274 }
275 sql += (" DEFAULT " + val);
276 }
277 }
278
279 if (field.getNullable() == DatabaseMetaData.columnNoNulls) {
280 sql += " NOT NULL ";
281 }
282 }
283 if (table.getPrimaryKey() != null && table.getPrimaryKey().getColumns().size() != 0) {
284 DbPrimaryKey pk = table.getPrimaryKey();
285
286
287
288
289 sql += ",\n PRIMARY KEY (\n";
290
291 isFirst = true;
292 Collections.sort(pk.getColumns(), DbPkComparator.getInstance());
293 for (DbPrimaryKeyColumn c : pk.getColumns()) {
294 if (!isFirst) {
295 sql += ",";
296 }
297 else {
298 isFirst = false;
299 }
300
301 sql += c.getColumnName();
302 }
303 sql += "\n)";
304 }
305 sql += "\n)";
306
307 PreparedStatement ps = null;
308 try {
309 ps = this.getConnection().prepareStatement(sql);
310 ps.executeUpdate();
311 }
312 catch (SQLException e) {
313 log.error("Error create table\nSQL:\n"+sql+"\n");
314 throw new DbRevisionException(e);
315 }
316 finally {
317 DbUtils.close(ps);
318 ps = null;
319 }
320
321 }
322
323 public void createForeignKey(DbTable view) {
324 }
325
326 public void dropTable(DbTable table) {
327 dropTable(table.getName());
328 }
329
330 public void dropTable(String nameTable) {
331 if (nameTable == null)
332 return;
333
334 String sql = "drop table " + nameTable;
335
336 Statement ps = null;
337 try {
338 ps = this.getConnection().createStatement();
339 ps.executeUpdate(sql);
340 }
341 catch (SQLException e) {
342 throw new DbRevisionException(e);
343 }
344 finally {
345 DbUtils.close(ps);
346 ps = null;
347 }
348 }
349
350 public void dropSequence(String nameSequence) {
351 }
352
353 public void dropConstraint(DbForeignKey impPk) {
354 if (impPk == null) {
355 return;
356 }
357
358 String sql = "ALTER TABLE " + impPk.getPkTableName() + " DROP CONSTRAINT " + impPk.getPkName();
359 PreparedStatement ps = null;
360 try {
361 ps = this.getConnection().prepareStatement(sql);
362 ps.executeUpdate();
363 }
364 catch (SQLException e) {
365 throw new DbRevisionException(e);
366 }
367 finally {
368 DbUtils.close(ps);
369 ps = null;
370 }
371 }
372
373 public void addColumn(DbTable table, DbField field) {
374 String sql = "alter table " + table.getName() + " add " + field.getName() + " ";
375
376 int fieldType = field.getJavaType();
377 switch (fieldType) {
378
379 case Types.NUMERIC:
380 case Types.DECIMAL:
381 sql += " DECIMAL";
382 break;
383
384 case Types.INTEGER:
385 sql += " INTEGER";
386 break;
387
388 case Types.DOUBLE:
389 sql += " DOUBLE";
390 break;
391
392 case Types.CHAR:
393 sql += " VARCHAR(1)";
394 break;
395
396 case Types.VARCHAR:
397 if (field.getSize() < 256)
398 sql += " VARCHAR(" + field.getSize() + ")";
399 else
400 sql += " TEXT";
401 break;
402
403 case Types.TIMESTAMP:
404 case Types.DATE:
405 sql += " TIMESTAMP";
406 break;
407
408 case Types.LONGVARCHAR:
409
410 sql += " VARCHAR(10)";
411 break;
412
413 case Types.LONGVARBINARY:
414 sql += " LONGVARBINARY";
415 break;
416
417 case Types.BLOB:
418 sql += " LONGBLOB";
419 break;
420
421 default:
422 field.setJavaStringType("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
423 System.out.println("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
424 }
425
426 if (field.getDefaultValue() != null) {
427 String val = field.getDefaultValue().trim();
428
429 if (StringUtils.isNotBlank(val)) {
430 switch (fieldType) {
431 case Types.CHAR:
432 case Types.VARCHAR:
433 val = "'" + val + "'";
434 break;
435 case Types.TIMESTAMP:
436 case Types.DATE:
437 if (DatabaseManager.checkDefaultTimestamp(val))
438 val = "CURRENT_TIMESTAMP";
439
440 break;
441 default:
442 }
443 sql += (" DEFAULT " + val);
444 }
445 }
446
447 if (field.getNullable() == DatabaseMetaData.columnNoNulls) {
448 sql += " NOT NULL ";
449 }
450
451 if (log.isDebugEnabled())
452 log.debug("MySql addColumn sql - \n" + sql);
453
454 Statement ps = null;
455 try {
456 ps = this.getConnection().createStatement();
457 ps.executeUpdate(sql);
458 }
459 catch (SQLException e) {
460 throw new DbRevisionException(e);
461 }
462 finally {
463 DbUtils.close(ps);
464 ps = null;
465 }
466 }
467
468 public String getOnDeleteSetNull() {
469 return "";
470 }
471
472 public String getDefaultTimestampValue() {
473 return "CURRENT_TIMESTAMP";
474 }
475
476 public List<DbView> getViewList(String schemaPattern, String tablePattern) {
477
478 return new ArrayList<DbView>(0);
479 }
480
481 public List<DbSequence> getSequnceList(String schemaPattern) {
482 return new ArrayList<DbSequence>();
483 }
484
485 public String getViewText(DbView view) {
486 return null;
487 }
488
489 public void createView(DbView view) {
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518 }
519
520 public void createSequence(DbSequence seq) {
521 }
522
523 public void setLongVarbinary(PreparedStatement ps, int index, DbDataFieldData fieldData) {
524 try {
525 byte[] bytes = Base64.decodeBase64(fieldData.getStringData().getBytes());
526
527 byte[] fileBytes = new byte[]{};
528 if (bytes!=null) {
529 fileBytes = bytes;
530 }
531 ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(fileBytes);
532 ps.setBinaryStream(index, byteArrayInputStream, fileBytes.length);
533
534 bytes = null;
535 byteArrayInputStream = null;
536 fileBytes = null;
537 }
538 catch (SQLException e) {
539 throw new DbRevisionException(e);
540 }
541 }
542
543 public void setLongVarchar(PreparedStatement ps, int index, DbDataFieldData fieldData) {
544 try {
545 ps.setString(index, fieldData.getStringData());
546 }
547 catch (SQLException e) {
548 throw new DbRevisionException(e);
549 }
550 }
551
552 public String getClobField(ResultSet rs, String nameField, int maxLength) {
553 return null;
554 }
555
556
557
558
559
560
561
562
563
564
565 public boolean testExceptionTableNotFound(Exception e) {
566
567 if (e instanceof SQLException) {
568 SQLException exception = (SQLException) e;
569 log.error("Error code: " + exception.getErrorCode());
570 log.error("getSQLState : " + exception.getSQLState());
571 if (exception.getErrorCode() == 1146) {
572 return true;
573 }
574 }
575 return false;
576 }
577
578 public boolean testExceptionIndexUniqueKey(Exception e, String index) {
579 return testExceptionIndexUniqueKey(e);
580 }
581
582 public boolean testExceptionIndexUniqueKey(Exception e) {
583 if (e instanceof SQLException) {
584 SQLException exception = (SQLException) e;
585 log.error("Error code: " + exception.getErrorCode());
586 log.error("getSQLState : " + exception.getSQLState());
587 if (exception.getErrorCode() == 1062) {
588 return true;
589 }
590 }
591 return false;
592 }
593
594 public boolean testExceptionTableExists(Exception e) {
595 if (e instanceof SQLException) {
596 if (((SQLException) e).getErrorCode() == 1050)
597 return true;
598 }
599 return false;
600 }
601
602 public boolean testExceptionViewExists(Exception e) {
603 if (e instanceof SQLException) {
604 if (((SQLException) e).getErrorCode() == 2714)
605 return true;
606 }
607 return false;
608 }
609
610 public boolean testExceptionSequenceExists(Exception e) {
611 return false;
612 }
613
614 public boolean testExceptionConstraintExists(Exception e) {
615 return false;
616 }
617 }