View Javadoc
1   package net.sumaris.core.dao.schema;
2   
3   /*-
4    * #%L
5    * SUMARiS :: Sumaris Core Shared
6    * $Id:$
7    * $HeadURL:$
8    * %%
9    * Copyright (C) 2018 SUMARiS Consortium
10   * %%
11   * This program is free software: you can redistribute it and/or modify
12   * it under the terms of the GNU General Public License as
13   * published by the Free Software Foundation, either version 3 of the
14   * License, or (at your option) any later version.
15   * 
16   * This program is distributed in the hope that it will be useful,
17   * but WITHOUT ANY WARRANTY; without even the implied warranty of
18   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
19   * GNU General Public License for more details.
20   * 
21   * You should have received a copy of the GNU General Public
22   * License along with this program.  If not, see
23   * <http://www.gnu.org/licenses/gpl-3.0.html>.
24   * #L%
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   * <p>DatabaseSchemaDaoImpl class.</p>
88   */
89  @Repository("databaseSchemaDao")
90  @Lazy
91  public class DatabaseSchemaDaoImpl
92          extends HibernateDaoSupport
93          implements DatabaseSchemaDao, InitializingBean {
94  
95      /** Logger. */
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      * Constructor used by Spring
107      *
108      * @param entityManager a {@link EntityManager} object.
109      */
110     @Autowired
111     public DatabaseSchemaDaoImpl(EntityManager entityManager) {
112         super();
113         setEntityManager(entityManager);
114     }
115 
116     /**
117      * Constructor to use when Spring not started
118      *
119      * @param config a {@link SumarisConfiguration} object.
120      */
121     public DatabaseSchemaDaoImpl(SumarisConfiguration config) {
122         super();
123         this.config = config;
124         this.liquibase = new Liquibase(config);
125     }
126 
127     /**
128      * Constructor to use when Spring not started
129      *
130      * @param config a {@link SumarisConfiguration} object.
131      * @param liquibase a {@link Liquibase} object.
132      */
133     public DatabaseSchemaDaoImpl(SumarisConfiguration config, Liquibase liquibase) {
134         super();
135         this.config = config;
136         this.liquibase = liquibase;
137     }
138 
139     /**
140      * {@inheritDoc}
141      *
142      * Executed automatically when the bean is initialized.
143      */
144     @Override
145     public void afterPropertiesSet() {
146 
147         // check database and server timezones conformity
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                 // silent
162             }
163         }
164     }
165 
166     /** {@inheritDoc} */
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     /** {@inheritDoc} */
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     /** {@inheritDoc} */
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     /** {@inheritDoc} */
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     /** {@inheritDoc} */
209     @Override
210     public void updateSchema() throws DatabaseSchemaUpdateException {
211         updateSchema(config.getConnectionProperties());
212     }
213 
214     /** {@inheritDoc} */
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     /** {@inheritDoc} */
229     @Override
230     public void updateSchema(File dbDirectory) throws DatabaseSchemaUpdateException {
231         // Preparing connection properties
232         Properties connectionProperties = config.getConnectionProperties();
233         connectionProperties.setProperty(Environment.URL, Daos.getJdbcUrl(dbDirectory, config.getDbName()));
234 
235         // Run update
236         updateSchema(connectionProperties);
237     }
238     
239     /** {@inheritDoc} */
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     /** {@inheritDoc} */
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     /** {@inheritDoc} */
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     /** {@inheritDoc} */
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     /** {@inheritDoc} */
302     @Override
303     public Version getSchemaVersionIfUpdate() {
304         return liquibase.getMaxChangeLogFileVersion();
305     }
306 
307     /** {@inheritDoc} */
308     @Override
309     public boolean shouldUpdateSchema() throws VersionNotFoundException {
310         return getSchemaVersion().compareTo(getSchemaVersionIfUpdate()) >= 0;
311     }
312     
313     /** {@inheritDoc} */
314     @Override
315     public boolean isDbLoaded() {
316         
317         // Do not try to run the validation query if the DB not exists
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         // Retrieve a validation query, from configuration
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         // try to execute the validation query
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     /** {@inheritDoc} */
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     /** {@inheritDoc} */
372     @Override
373     public void generateNewDb(File dbDirectory, boolean replaceIfExists) {
374         Preconditions.checkNotNull(dbDirectory);
375         // Preparing connection properties
376         Properties connectionProperties = config.getConnectionProperties();
377         connectionProperties.setProperty(Environment.URL, Daos.getJdbcUrl(dbDirectory, config.getDbName()));
378 
379         // Run Db creation
380         generateNewDb(dbDirectory, replaceIfExists, null, connectionProperties, false);
381     }
382 
383     /** {@inheritDoc} */
384     @Override
385     public void generateNewDb(File dbDirectory, boolean replaceIfExists, File scriptFile, Properties connectionProperties, boolean isTemporaryDb) {
386         Preconditions.checkNotNull(dbDirectory);
387 
388         // Log target connection
389         if (log.isInfoEnabled()) {
390             log.info(I18n.t("sumaris.persistence.newEmptyDatabase.directory", dbDirectory));
391         }
392         // Check output directory validity
393         if (dbDirectory.exists() && !dbDirectory.isDirectory()) {
394             throw new SumarisTechnicalException(
395                     I18n.t("sumaris.persistence.newEmptyDatabase.notValidDirectory.error", dbDirectory));
396         }
397 
398         // Make sure the directory could be created
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         // Get connections properties :
424         Properties targetConnectionProperties = connectionProperties != null ? connectionProperties : config.getConnectionProperties();
425 
426         // Check connections
427         if (!checkConnection(targetConnectionProperties)) {
428             return;
429         }
430 
431         try {
432             // Create the database
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             // Shutdown database at end
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     /* -- Internal methods --*/
453     /**
454      * <p>checkConnection.</p>
455      *
456      * @param targetConnectionProperties a {@link Properties} object.
457      * @return a boolean.
458      */
459     protected boolean checkConnection(
460             Properties targetConnectionProperties) {
461 
462         // Log target connection
463         if (log.isInfoEnabled()) {
464             log.info("Connecting to target database...\n" + Daos.getLogString(targetConnectionProperties));
465         }
466 
467         // Check target connection
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      * <p>createEmptyDb.</p>
479      *
480      * @param config a {@link SumarisConfiguration} object.
481      * @param targetConnectionProperties a {@link Properties} object.
482      * @param scriptFile a {@link File} object.
483      * @param isTemporaryDb a boolean.
484      * @throws SQLException if any.
485      * @throws IOException if any.
486      */
487     private void createEmptyDb(SumarisConfiguration config, Properties targetConnectionProperties, File scriptFile, boolean isTemporaryDb) throws SQLException, IOException {
488         // Getting the script file
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         // Make sure the path is an URL (if not, add "file:" prefix)
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             // No script file: try to generate script
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      * <p>getImportScriptSql.</p>
553      *
554      * @param scriptResource a {@link Resource} object.
555      * @return a {@link List} object.
556      * @throws IOException if any.
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 ", // for HslDB 2.3+
571                     "CREATE SCHEMA ",
572                     "GRANT DBA TO ",
573                     "INSERT INTO ", // In case there is memory tables
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                             // Allow this instructions
583                             && !input.startsWith("SET WRITE_DELAY")       // for HslDB 1.8+
584                             && !input.startsWith("SET FILES WRITE DELAY") // for HslDB 2.3+
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                     // Reset sequence to zero
614                     if (line.startsWith("CREATE SEQUENCE")) {
615                         line = line.replaceAll("START WITH [0-9]+", "START WITH " + config.getSequenceStartWithValue());
616                     }
617 
618                     // Use cached table
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                     // Always use TEMP_QUERY_PARAMETER as standard CACHED table
625                     // (no more use a GLOBAL TEMPORARY table, because JDBC metdata could not be extract)
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                 // To be able to retrieve connection from datasource
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             //sessionSettings.put(Environment.PHYSICAL_NAMING_STRATEGY, "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
664         }
665         else {
666             // To be able to retrieve connection from datasource
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         // Add annotations entities
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      * Check server and database timezones conformity
688      * Warn if offsets differs
689      */
690     private void checkTimezoneConformity() throws SQLException {
691 
692         // get server timezone
693         TimeZone serverTimeZone = TimeZone.getDefault();
694         log.info(I18n.t("sumaris.persistence.serverTimeZone", new Timestamp(new Date().getTime()), serverTimeZone.getID()));
695 
696         // get db timezone offset in time format ex: '1:00' for 1 hour offset
697         String dbOffsetAsString = (String) Daos.sqlUnique(dataSource, getTimezoneQuery(dataSource.getConnection()));
698         log.info(I18n.t("sumaris.persistence.dbTimeZone", getDatabaseCurrentTimestamp(), dbOffsetAsString));
699 
700         // convert db time zone offset in raw offset in milliseconds
701         int dbOffset = Integer.parseInt(dbOffsetAsString.substring(0, dbOffsetAsString.lastIndexOf(":"))) * 3600 * 1000;
702 
703         // compare both offsets
704         if (dbOffset != serverTimeZone.getRawOffset()) {
705             // warn if offsets differs
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 }