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