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