1 package fr.ifremer.quadrige2.core.dao.technical.hibernate;
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
27 import com.google.common.base.Charsets;
28 import com.google.common.base.Preconditions;
29 import com.google.common.base.Predicate;
30 import com.google.common.collect.Lists;
31 import com.google.common.collect.Sets;
32 import fr.ifremer.quadrige2.core.config.Quadrige2Configuration;
33 import fr.ifremer.quadrige2.core.config.Quadrige2ConfigurationOption;
34 import fr.ifremer.quadrige2.core.dao.technical.Daos;
35 import fr.ifremer.quadrige2.core.dao.technical.DatabaseSchemaDao;
36 import fr.ifremer.quadrige2.core.dao.technical.liquibase.Liquibase;
37 import fr.ifremer.quadrige2.core.dao.technical.spring.Springs;
38 import fr.ifremer.quadrige2.core.exception.DatabaseSchemaUpdateException;
39 import fr.ifremer.quadrige2.core.exception.Quadrige2TechnicalException;
40 import fr.ifremer.quadrige2.core.exception.VersionNotFoundException;
41 import liquibase.exception.LiquibaseException;
42 import org.apache.commons.io.FileUtils;
43 import org.apache.commons.io.IOUtils;
44 import org.apache.commons.lang3.ArrayUtils;
45 import org.apache.commons.lang3.StringUtils;
46 import org.apache.commons.logging.Log;
47 import org.apache.commons.logging.LogFactory;
48 import org.hibernate.HibernateException;
49 import org.hibernate.SessionFactory;
50 import org.hibernate.cfg.Environment;
51 import org.hibernate.dialect.Dialect;
52 import org.hibernate.engine.spi.SessionFactoryImplementor;
53 import org.hibernate.tool.hbm2ddl.SchemaExport;
54 import org.hibernate.tool.hbm2ddl.SchemaUpdate;
55 import org.nuiton.i18n.I18n;
56 import org.nuiton.version.Version;
57 import org.nuiton.version.VersionBuilder;
58 import org.springframework.beans.factory.annotation.Autowired;
59 import org.springframework.context.ApplicationContext;
60 import org.springframework.context.annotation.Lazy;
61 import org.springframework.core.io.Resource;
62 import org.springframework.dao.DataAccessResourceFailureException;
63 import org.springframework.jdbc.CannotGetJdbcConnectionException;
64 import org.springframework.jdbc.datasource.DataSourceUtils;
65 import org.springframework.stereotype.Repository;
66 import org.springframework.util.ResourceUtils;
67
68 import javax.sql.DataSource;
69 import java.io.File;
70 import java.io.FileWriter;
71 import java.io.IOException;
72 import java.io.InputStream;
73 import java.sql.Connection;
74 import java.sql.PreparedStatement;
75 import java.sql.SQLException;
76 import java.sql.Statement;
77 import java.util.Iterator;
78 import java.util.List;
79 import java.util.Properties;
80 import java.util.Set;
81
82
83
84
85 @Repository("databaseSchemaDao")
86 @Lazy
87 public class DatabaseSchemaDaoImpl
88 extends HibernateDaoSupport
89 implements DatabaseSchemaDao {
90
91
92 private static final Log log =
93 LogFactory.getLog(DatabaseSchemaDaoImpl.class);
94
95 @Autowired
96 private ApplicationContext appContext;
97
98 @Autowired
99 private Liquibase liquibase;
100
101 @Autowired
102 private DataSource dataSource;
103
104 @Autowired
105 private Quadrige2Configuration config;
106
107 private Dialect localDialect = null;
108
109
110
111
112
113
114 @Autowired
115 public DatabaseSchemaDaoImpl(SessionFactory sessionFactory) {
116 super();
117 setSessionFactory(sessionFactory);
118 }
119
120
121
122
123
124
125 public DatabaseSchemaDaoImpl(Quadrige2Configuration config) {
126 super();
127 this.config = config;
128 this.liquibase = new Liquibase(config);
129 }
130
131
132
133
134
135
136
137 public DatabaseSchemaDaoImpl(Quadrige2Configuration config, Liquibase liquibase) {
138 super();
139 this.config = config;
140 this.liquibase = liquibase;
141 }
142
143
144 @Override
145 public void generateCreateSchemaFile(String filename) {
146 if (filename == null || filename.isEmpty()) {
147 throw new IllegalArgumentException("filename could not be null or empty.");
148 }
149 generateCreateSchemaFile(filename, false, false, true);
150 }
151
152
153 @Override
154 public void generateCreateSchemaFile(String filename, boolean doExecute, boolean withDrop, boolean withCreate) {
155
156
157 org.hibernate.cfg.Configuration cfg = getHibernateConfiguration();
158
159
160 SchemaExport se = new SchemaExport(cfg);
161 se.setDelimiter(";");
162 se.setOutputFile(filename);
163 se.execute(false, doExecute, !withCreate, !withDrop);
164 }
165
166
167 @Override
168 public void generateUpdateSchemaFile(String filename) {
169 if (filename == null || filename.isEmpty()) {
170 throw new IllegalArgumentException("filename could not be null or empty.");
171 }
172 generateUpdateSchemaFile(filename, false);
173 }
174
175
176 @Override
177 public void generateUpdateSchemaFile(String filename, boolean doUpdate) {
178
179
180 org.hibernate.cfg.Configuration cfg = getHibernateConfiguration();
181
182
183 SchemaUpdate su = new SchemaUpdate(cfg);
184 su.setDelimiter(";");
185 su.setOutputFile(filename);
186 su.execute(false, false);
187 }
188
189
190
191
192
193
194
195 private org.hibernate.cfg.Configuration getHibernateConfiguration() {
196
197
198 org.hibernate.cfg.Configuration cfg = new org.hibernate.cfg.Configuration();
199 try {
200
201 addRessourceToHibernateConfiguration(cfg, Package.getPackage("fr.ifremer.quadrige2.core.dao"), "**/*.hbm.xml");
202
203
204 addRessourceToHibernateConfiguration(cfg, "", "queries.hbm.xml");
205 addRessourceToHibernateConfiguration(cfg, "", config.getHibernateClientQueriesFile());
206
207 } catch (IOException e) {
208 log.error("exportExtractorSchemaToFile failed", e);
209 throw new DataAccessResourceFailureException(e.getMessage(), e);
210 }
211
212 cfg.setProperty(Environment.DIALECT, config.getHibernateDialect());
213
214
215 HibernateConnectionProvider.setDataSource(dataSource);
216 cfg.setProperty(Environment.CONNECTION_PROVIDER, HibernateConnectionProvider.class.getName());
217
218 return cfg;
219 }
220
221 private void addRessourceToHibernateConfiguration(org.hibernate.cfg.Configuration cfg, Package aPackage, String filePattern) throws IOException {
222 String packageName = aPackage.getName().replace(".", "/");
223 addRessourceToHibernateConfiguration(cfg, packageName, "**/*.hbm.xml");
224
225 }
226
227 private void addRessourceToHibernateConfiguration(org.hibernate.cfg.Configuration cfg, String classPathFolder, String filePattern) throws IOException {
228 Preconditions.checkNotNull(appContext, "No ApplicationContext found. Make bean initialization has been done by Spring.");
229 String fullName = null;
230 if (classPathFolder != null && !classPathFolder.isEmpty()) {
231 fullName = classPathFolder + "/" + filePattern;
232 } else {
233 fullName = filePattern;
234 }
235 org.springframework.core.io.Resource[] resources = appContext.getResources("classpath*:" + fullName);
236 for (Resource resource : resources) {
237 String path = resource.getURL().toString();
238 if (classPathFolder != null && !classPathFolder.isEmpty()) {
239
240 int index = path.lastIndexOf(classPathFolder);
241 if (index != -1) {
242 path = path.substring(index);
243 }
244 } else {
245 int index = path.lastIndexOf("/");
246 if (index != -1) {
247 path = path.substring(index + 1);
248 }
249 }
250 cfg.addResource(path);
251 }
252 }
253
254
255
256
257
258
259 public Dialect getLocalDialect() {
260 if (localDialect == null) {
261 localDialect = ((SessionFactoryImplementor) getSessionFactory()).getDialect();
262 }
263 return localDialect;
264 }
265
266
267 @Override
268 public void updateSchema() throws DatabaseSchemaUpdateException {
269 updateSchema(config.getConnectionProperties());
270 }
271
272
273 @Override
274 public void updateSchema(Properties connectionProperties) throws DatabaseSchemaUpdateException {
275 try {
276 liquibase.executeUpdate(connectionProperties);
277 } catch (LiquibaseException le) {
278 if (log.isErrorEnabled()) {
279 log.error(le.getMessage(), le);
280 }
281 throw new DatabaseSchemaUpdateException("Could not update schema", le);
282 }
283 }
284
285
286 @Override
287 public void updateSchema(File dbDirectory) throws DatabaseSchemaUpdateException {
288
289 Properties connectionProperties = config.getConnectionProperties();
290 connectionProperties.setProperty(Environment.URL, Daos.getJdbcUrl(dbDirectory, config.getDbName()));
291
292
293 updateSchema(connectionProperties);
294 }
295
296
297 @Override
298 public void generateStatusReport(File outputFile) throws IOException {
299 FileWriter fw = new FileWriter(outputFile);
300 try {
301 liquibase.reportStatus(fw);
302 } catch (LiquibaseException le) {
303 if (log.isErrorEnabled()) {
304 log.error(le.getMessage(), le);
305 }
306 throw new Quadrige2TechnicalException("Could not report database status", le);
307 }
308 }
309
310
311 @Override
312 public void generateDiffReport(File outputFile, String typesToControl) throws IOException {
313 try {
314 liquibase.reportDiff(outputFile, typesToControl);
315 } catch (LiquibaseException le) {
316 if (log.isErrorEnabled()) {
317 log.error(le.getMessage(), le);
318 }
319 throw new Quadrige2TechnicalException("Could not report database diff", le);
320 }
321 }
322
323
324 @Override
325 public void generateDiffChangeLog(File outputChangeLogFile, String typesToControl) throws IOException {
326 try {
327 liquibase.generateDiffChangelog(outputChangeLogFile, typesToControl);
328 } catch (LiquibaseException le) {
329 if (log.isErrorEnabled()) {
330 log.error(le.getMessage(), le);
331 }
332 throw new Quadrige2TechnicalException("Could not create database diff changelog", le);
333 }
334 }
335
336
337
338 @Override
339 public Version getSchemaVersion() throws VersionNotFoundException {
340 String systemVersion;
341 try {
342 systemVersion = queryUniqueTyped("lastSystemVersion");
343 if (StringUtils.isBlank(systemVersion)) {
344 throw new VersionNotFoundException(String.format("Could not get the schema version. No version found in SYSTEM_VERSION table."));
345 }
346 } catch (HibernateException he) {
347 throw new VersionNotFoundException(String.format("Could not get the schema version: %s", he.getMessage()));
348 }
349 try {
350 return VersionBuilder.create(systemVersion).build();
351 } catch (IllegalArgumentException iae) {
352 throw new VersionNotFoundException(String.format("Could not get the schema version. Bad schema version found table SYSTEM_VERSION: %s",
353 systemVersion));
354 }
355 }
356
357
358 @Override
359 public Version getSchemaVersionIfUpdate() {
360 return liquibase.getMaxChangeLogFileVersion();
361 }
362
363
364 @Override
365 public boolean shouldUpdateSchema() throws VersionNotFoundException {
366 return getSchemaVersion().compareTo(getSchemaVersionIfUpdate()) >= 0;
367 }
368
369
370 @Override
371 public boolean isDbLoaded() {
372
373
374 if (!isDbExists()) {
375 log.warn("Database directory not found. Could not load database.");
376 return false;
377 }
378
379 Connection connection = null;
380 try {
381 connection = DataSourceUtils.getConnection(dataSource);
382 }
383 catch(CannotGetJdbcConnectionException ex) {
384 log.error(ex);
385 DataSourceUtils.releaseConnection(connection, dataSource);
386 return false;
387 }
388
389
390 String dbValidatioNQuery = config.getDbValidationQuery();
391 if (StringUtils.isBlank(dbValidatioNQuery)) {
392 DataSourceUtils.releaseConnection(connection, dataSource);
393 return true;
394 }
395
396 log.debug(String.format("Check if the database is loaded, using validation query: %s", dbValidatioNQuery));
397
398
399 Statement stmt = null;
400 try {
401 stmt = connection.createStatement();
402 stmt.execute(dbValidatioNQuery);
403 } catch (SQLException ex) {
404 log.error(String.format("Error while executing validation query [%s]: %s", dbValidatioNQuery, ex.getMessage()));
405 return false;
406 }
407 finally {
408 Daos.closeSilently(stmt);
409 DataSourceUtils.releaseConnection(connection, dataSource);
410 }
411
412 return true;
413 }
414
415
416 @Override
417 public boolean isDbExists() {
418 String jdbcUrl = config.getJdbcURL();
419
420 if (!Daos.isFileDatabase(jdbcUrl)) {
421 return true;
422 }
423
424 File f = new File(config.getDbDirectory(), config.getDbName() + ".script");
425 return f.exists();
426 }
427
428
429 @Override
430 public void generateNewDb(File dbDirectory, boolean replaceIfExists) {
431 Preconditions.checkNotNull(dbDirectory);
432
433 Properties connectionProperties = config.getConnectionProperties();
434 connectionProperties.setProperty(Environment.URL, Daos.getJdbcUrl(dbDirectory, config.getDbName()));
435
436
437 generateNewDb(dbDirectory, replaceIfExists, null, connectionProperties, false);
438 }
439
440
441 @Override
442 public void generateNewDb(File dbDirectory, boolean replaceIfExists, File scriptFile, Properties connectionProperties, boolean isTemporaryDb) {
443 Preconditions.checkNotNull(dbDirectory);
444
445
446 if (log.isInfoEnabled()) {
447 log.info(I18n.t("quadrige2.persistence.newEmptyDatabase.directory", dbDirectory));
448 }
449
450 if (dbDirectory.exists() && !dbDirectory.isDirectory()) {
451 throw new Quadrige2TechnicalException(
452 I18n.t("quadrige2.persistence.newEmptyDatabase.notValidDirectory.error", dbDirectory));
453 }
454
455
456 try {
457 FileUtils.forceMkdir(dbDirectory);
458 } catch (IOException e) {
459 throw new Quadrige2TechnicalException(
460 I18n.t("quadrige2.persistence.newEmptyDatabase.mkdir.error", dbDirectory),
461 e);
462 }
463
464 if (ArrayUtils.isNotEmpty(dbDirectory.listFiles())) {
465 if (replaceIfExists) {
466 log.info(I18n.t("quadrige2.persistence.newEmptyDatabase.deleteDirectory", dbDirectory));
467 try {
468 FileUtils.deleteDirectory(dbDirectory);
469 } catch (IOException e) {
470 throw new Quadrige2TechnicalException(
471 I18n.t("quadrige2.persistence.newEmptyDatabase.deleteDirectory.error", dbDirectory), e);
472 }
473 }
474 else {
475 throw new Quadrige2TechnicalException(
476 I18n.t("quadrige2.persistence.newEmptyDatabase.notEmptyDirectory.error", dbDirectory));
477 }
478 }
479
480
481 Properties targetConnectionProperties = connectionProperties != null ? connectionProperties : config.getConnectionProperties();
482
483
484 if (!checkConnection(config, targetConnectionProperties)) {
485 return;
486 }
487
488 try {
489
490 createEmptyDb(config, targetConnectionProperties, scriptFile, isTemporaryDb);
491 } catch (SQLException e) {
492 throw new Quadrige2TechnicalException(
493 I18n.t("quadrige2.persistence.newEmptyDatabase.create.error"),
494 e);
495 } catch (IOException e) {
496 throw new Quadrige2TechnicalException(
497 I18n.t("quadrige2.persistence.newEmptyDatabase.create.error"),
498 e);
499 }
500
501 try {
502
503 Daos.shutdownDatabase(targetConnectionProperties);
504 } catch (SQLException e) {
505 throw new Quadrige2TechnicalException(
506 I18n.t("quadrige2.persistence.newEmptyDatabase.shutdown.error"),
507 e);
508 }
509 }
510
511
512
513
514
515
516
517
518
519
520 protected boolean checkConnection(
521 Quadrige2Configuration config,
522 Properties targetConnectionProperties) {
523
524
525 if (log.isInfoEnabled()) {
526 log.info("Connecting to target database...\n" + Daos.getLogString(targetConnectionProperties));
527 }
528
529
530 boolean isValidConnection = Daos.isValidConnectionProperties(targetConnectionProperties);
531 if (!isValidConnection) {
532 log.error("Connection error: could not connect to target database.");
533 return false;
534 }
535
536 return true;
537 }
538
539
540
541
542
543
544
545
546
547
548
549 private void createEmptyDb(Quadrige2Configuration config, Properties targetConnectionProperties, File scriptFile, boolean isTemporaryDb) throws SQLException, IOException {
550
551 String scriptPath = scriptFile == null ? config.getDbCreateScriptPath() : scriptFile.getAbsolutePath();
552 Preconditions
553 .checkArgument(
554 StringUtils.isNotBlank(scriptPath),
555 String.format(
556 "No path for the DB script has been set in the configuration. This is need to create a new database. Please set the option [%s] in configuration file.",
557 Quadrige2ConfigurationOption.DB_CREATE_SCRIPT_PATH));
558 scriptPath = scriptPath.replaceAll("\\\\", "/");
559 if (log.isInfoEnabled()) {
560 log.info("Will use create script: " + scriptPath);
561 }
562
563
564 String scriptPathWithPrefix = scriptPath;
565 if (!ResourceUtils.isUrl(scriptPath)) {
566 scriptPathWithPrefix = ResourceUtils.FILE_URL_PREFIX + scriptPath;
567 }
568
569
570 Resource scriptResource = Springs.getResource(scriptPathWithPrefix);
571 if (!scriptResource.exists()) {
572 throw new Quadrige2TechnicalException(String.format("Could not find DB script file, at %s", scriptPath));
573 }
574
575 Connection connection = Daos.createConnection(targetConnectionProperties);
576 Daos.setTimezone(connection, config.getDbTimezone());
577 try {
578 List<String> importScriptSql = getImportScriptSql(scriptResource, connection, isTemporaryDb);
579 for (String sql : importScriptSql) {
580 PreparedStatement statement = null;
581 try {
582 statement = connection.prepareStatement(sql);
583 statement.execute();
584 } catch (SQLException sqle) {
585 log.warn("SQL command failed : " + sql, sqle);
586 throw sqle;
587 } finally {
588 Daos.closeSilently(statement);
589 }
590
591 }
592 connection.commit();
593 } finally {
594 Daos.closeSilently(connection);
595 }
596 }
597
598
599
600
601
602
603
604
605
606
607
608 protected List<String> getImportScriptSql(Resource scriptResource, Connection connection, boolean isTemporaryDb) throws SQLException, IOException {
609
610 Version hsqldbVersion = Daos.getDatabaseVersion(connection);
611 boolean isHsqldb2 = !"1.8".equals(hsqldbVersion.toString());
612
613 List<String> result = Lists.newArrayList();
614
615 Predicate<String> predicate = new Predicate<String>() {
616
617 Set<String> includedStarts = Sets.newHashSet(
618 "INSERT INTO DATABASECHANGELOG ");
619
620 Set<String> excludedStarts = Sets.newHashSet(
621 "SET ",
622 "CREATE USER ",
623 "ALTER USER ",
624 "CREATE SCHEMA ",
625 "GRANT DBA TO ",
626 "INSERT INTO ",
627 "CREATE FUNCTION "
628 );
629
630 @Override
631 public boolean apply(String input) {
632 boolean accept = true;
633 for (String forbiddenStart : excludedStarts) {
634 if (input.startsWith(forbiddenStart)
635
636 && !input.startsWith("SET WRITE_DELAY")
637 && !input.startsWith("SET FILES WRITE DELAY")
638 ) {
639 accept = false;
640 break;
641 }
642 }
643 if (!accept) {
644 for (String forbiddenStart : includedStarts) {
645 if (input.startsWith(forbiddenStart)) {
646 accept = true;
647 break;
648 }
649 }
650 }
651 return accept;
652 }
653 };
654
655
656 InputStream is = scriptResource.getInputStream();
657 try {
658 Iterator<String> lines = IOUtils.lineIterator(is, Charsets.UTF_8);
659
660 while (lines.hasNext()) {
661 String line = lines.next().trim().toUpperCase();
662 if (predicate.apply(line)) {
663 if (line.contains("\\U000A")) {
664 line = line.replaceAll("\\\\U000A", "\n");
665 }
666
667
668 if (line.startsWith("CREATE SEQUENCE")) {
669 line = line.replaceAll("START WITH [0-9]+", "START WITH 0");
670 }
671
672
673 if (line.startsWith("CREATE TABLE")
674 || line.startsWith("CREATE MEMORY TABLE")) {
675 line = line.replaceAll("CREATE (MEMORY )?TABLE", "CREATE CACHED TABLE");
676 }
677
678
679
680 if (line.matches("CREATE [ A-Z_-]*TABLE [.A-Z_-]*TEMP_QUERY_PARAMETER\\(.*")) {
681 line = line.replaceAll("CREATE [ A-Z_-]*TABLE [.A-Z_-]*TEMP_QUERY_PARAMETER", "CREATE CACHED TABLE TEMP_QUERY_PARAMETER");
682 }
683
684 if (StringUtils.isNotBlank(line)) {
685 result.add(line);
686 }
687 }
688 }
689 } finally {
690 IOUtils.closeQuietly(is);
691 }
692 return result;
693 }
694 }