title: How to verify an HSQLDB's schema is correct before launching
body: <p>We are using hsqldb in our project and have things working well.  We are currently configured to use an in-process file database, and our DriverManagerDataSource, LocalContainerEntityManagerFactoryBean, and TransactionManager are all instantiated as beans in our context XML file:</p>
  
  <pre><code>&lt;bean id="databaseValidator" class="com.apps.database.DatabaseValidator"&gt;&lt;/bean&gt;
  
  &lt;bean id="dataSource" depends-on="databaseValidator" class="com.apps.database.DriverManagerDataSourceWithDirectory"&gt;
         &lt;property name="driverClassName" value="org.hsqldb.jdbcDriver" /&gt;
         &lt;property name="url" value="jdbc:hsqldb:file:db/theDB" /&gt;
         &lt;property name="username" value="--------" /&gt;
         &lt;property name="password" value="--------" /&gt;
  &lt;/bean&gt;
  
  &lt;bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
         &lt;property name="dataSource" ref="dataSource" /&gt;
         &lt;property name="jpaProperties"&gt;
                &lt;props&gt;
                      &lt;prop key="hibernate.dialect"&gt;org.hibernate.dialect.HSQLDialect&lt;/prop&gt;
                      &lt;prop key="hibernate.hbm2ddl.auto"&gt;validate&lt;/prop&gt;
                &lt;/props&gt;
         &lt;/property&gt;
         &lt;property name="packagesToScan"&gt;
                &lt;list&gt;
                      &lt;value&gt;com.apps.model.logic&lt;/value&gt;
                      &lt;value&gt;com.apps.jpa&lt;/value&gt;
                &lt;/list&gt;
         &lt;/property&gt;
         &lt;property name="mappingResources"&gt;
                &lt;list&gt;
                      &lt;value&gt;Setting.hbm.xml&lt;/value&gt;
                &lt;/list&gt;
         &lt;/property&gt;
         &lt;property name="jpaVendorAdapter"&gt;
                &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"&gt;
                      &lt;property name="database" value="HSQL" /&gt;
                &lt;/bean&gt;
         &lt;/property&gt;
  &lt;/bean&gt;
  
  &lt;bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager"&gt;
         &lt;property name="entityManagerFactory" ref="entityManagerFactory" /&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p>Things get tricky whenever we have to modify the database schema by introducing a new entity or by modifying the fields of an existing entity.  Namely, it requires all of our developers to delete the existing copies of our file database in their development environment and reinsert an updated copy.  I would like this to be an automatic process such that, upon application launch, we can check if the existing DB file has the correct schema before trying to connect to it.  In fact I would like to validate a database's schema every time the app launches to make sure that the end-user (in a non-development environment) can have their database migrate to a current schema.</p>
  
  <p>In order to achieve this, we created a bean (databaseValidator - see above and below) that fires up before anything else that attempts to validate the schema by creating an in-code EntityManagerFactory and calling 'afterPropertiesSet'.  This actually does the trick - it fails if the schema has a mismatch, and succeeds if the schema is valid.  While this works, it holds onto our database files such that we cannot delete them and replace them with the updated database files.  Since we cannot delete the files, we cannot replace them with an updated file-database.</p>
  
  <p>Overall, I feel this is not the best approach to validate a file database's schema before connecting to it.  I have a feeling that we might want to take advantage of the existing EntityManagerFactory (as defined in the XML file above) to determine if a schema is valid or not, but I am not sure how to piggy-back off of the exception that it throws.  Does anyone have any better suggestions on how we could programmatically test a file db's schema?  </p>
  
  <pre><code>private boolean isTheSchemaValid() {
  
      boolean isValid = false;
      LocalContainerEntityManagerFactoryBean emf = null;
  
      try {
  
          emf = new LocalContainerEntityManagerFactoryBean();
  
          DriverManagerDataSource dataSource = new DriverManagerDataSource();
          dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
          dataSource.setUrl("jdbc:hsqldb:file:db/theDB");
          dataSource.setUsername("--------");
          dataSource.setPassword("--------");
          emf.setDataSource(dataSource);
  
          Properties jpaProperties = new Properties();
          jpaProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
          jpaProperties.setProperty("hibernate.hbm2ddl.auto", "validate");
          emf.setJpaProperties(jpaProperties);
          emf.setPackagesToScan("com.test.model.logic", "com.test.jpa");
          emf.setMappingResources("Setting.hbm.xml");
  
          HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
          adapter.setDatabasePlatform("HSQL");
          emf.setJpaVendorAdapter(adapter);
  
          emf.afterPropertiesSet();
  
          isValid = true;
      } catch (Exception ex) {
          isValid = false;
      } finally {
          emf = null;
      }
  
      return isValid;
  }
  </code></pre>
  
  <p>I'm happy to provide more information as needed.  Thanks!</p>