HSQLDB 1.8 and multiple schemas with hbm2ddl

scooper4711 · **Joined:** Thu Oct 12, 2006 6:12 pm **Posts:** 8

Here's a patch to generate schemas automatically for HSQL

Code:

$ svn diff src/org/hibernate/cfg/Configuration.java src/org/hibernate/dialect/Dialect.java src/org/hibernate/dialect/HSQLDialect.java 
Index: src/org/hibernate/cfg/Configuration.java
===================================================================
--- src/org/hibernate/cfg/Configuration.java    (revision 15019)
+++ src/org/hibernate/cfg/Configuration.java    (working copy)
@@ -180,8 +180,8 @@
 
        private transient Mapping mapping = buildMapping();
 
-       
 
+
        protected Configuration(SettingsFactory settingsFactory) {
                this.settingsFactory = settingsFactory;
                reset();
@@ -826,10 +826,30 @@
                        }
                }
 
-               return ArrayHelper.toStringArray( script );
+        Set schemas = findSchemaDefinitions( dialect );
+        Iterator each = schemas.iterator();
+        while ( each.hasNext() ) {
+            String schema = (String)each.next();
+            script.add( "drop schema " + schema );
+        }
+
+        return ArrayHelper.toStringArray( script );
        }
 
-       /**
+    public Set findSchemaDefinitions( Dialect dialect ) {
+        Set schemas = new HashSet();
+        Iterator each = getTableMappings();
+        while ( each.hasNext() ) {
+            Table t = (Table)each.next();
+            if ( t.isPhysicalTable() ) {
+                String schema = t.getQuotedSchema( dialect );
+                if ( schema != null ) schemas.add( schema );
+            }
+        }
+        return schemas;
+    }
+
+    /**
         * Generate DDL for creating tables
         *
         * @see org.hibernate.tool.hbm2ddl.SchemaExport
@@ -841,7 +861,16 @@
                String defaultCatalog = properties.getProperty( Environment.DEFAULT_CATALOG );
                String defaultSchema = properties.getProperty( Environment.DEFAULT_SCHEMA );
 
-               Iterator iter = getTableMappings();
+        Set schemas = findSchemaDefinitions( dialect );
+        Iterator each = schemas.iterator();
+        while ( each.hasNext() ) {
+            String schema = (String)each.next();
+            script.add( dialect.getCreateSchemaString( schema ));
+        }
+
+
+        // generate table statements...
+        Iterator iter = getTableMappings();
                while ( iter.hasNext() ) {
                        Table table = (Table) iter.next();
                        if ( table.isPhysicalTable() ) {
@@ -860,6 +889,7 @@
                        }
                }
 
+        // generate constraint statements...
                iter = getTableMappings();
                while ( iter.hasNext() ) {
                        Table table = (Table) iter.next();
@@ -944,7 +974,7 @@
                while ( iter.hasNext() ) {
                        Table table = (Table) iter.next();
                        if ( table.isPhysicalTable() ) {
-                               
+
                                TableMetadata tableInfo = databaseMetadata.getTableMetadata(
                                                table.getName(),
                                                ( table.getSchema() == null ) ? defaultSchema : table.getSchema(),
@@ -1064,13 +1094,13 @@
 
                String defaultCatalog = properties.getProperty( Environment.DEFAULT_CATALOG );
                String defaultSchema = properties.getProperty( Environment.DEFAULT_SCHEMA );
-               
+
                Iterator iter = getTableMappings();
                while ( iter.hasNext() ) {
                        Table table = (Table) iter.next();
                        if ( table.isPhysicalTable() ) {
-                               
 
+
                                TableMetadata tableInfo = databaseMetadata.getTableMetadata(
                                                table.getName(),
                                                ( table.getSchema() == null ) ? defaultSchema : table.getSchema(),
@@ -1127,7 +1157,7 @@
                while ( iter.hasNext() ) {
                        SecondPass sp = (SecondPass) iter.next();
                        if ( ! (sp instanceof QuerySecondPass) ) {
-                               sp.doSecondPass( classes ); 
+                               sp.doSecondPass( classes );
                                iter.remove();
                        }
                }
@@ -1136,7 +1166,7 @@
                iter = secondPasses.iterator();
                while ( iter.hasNext() ) {
                        SecondPass sp = (SecondPass) iter.next();
-                       sp.doSecondPass( classes ); 
+                       sp.doSecondPass( classes );
                        iter.remove();
                }
 
@@ -2066,7 +2096,7 @@
                                Property prop = pc.getReferencedProperty( propertyName );
                                if ( prop == null ) {
                                        throw new MappingException(
-                                                       "property not known: " + 
+                                                       "property not known: " +
                                                        persistentClass + '.' + propertyName
                                                );
                                }
@@ -2096,7 +2126,7 @@
        public Map getSqlFunctions() {
                return sqlFunctions;
        }
-       
+
        public void addSqlFunction(String functionName, SQLFunction function) {
                sqlFunctions.put( functionName, function );
        }
Index: src/org/hibernate/dialect/Dialect.java
===================================================================
--- src/org/hibernate/dialect/Dialect.java      (revision 15019)
+++ src/org/hibernate/dialect/Dialect.java      (working copy)
@@ -1762,4 +1762,9 @@
        public boolean supportsBindAsCallableArgument() {
                return true;
        }
+
+    public String getCreateSchemaString(String schema)
+    {
+        return "create schema " + schema;
+    }
 }
Index: src/org/hibernate/dialect/HSQLDialect.java
===================================================================
--- src/org/hibernate/dialect/HSQLDialect.java  (revision 15019)
+++ src/org/hibernate/dialect/HSQLDialect.java  (working copy)
@@ -323,4 +323,10 @@
        public boolean supportsLobValueChangePropogation() {
                return false;
        }
+
+    @Override
+    public String getCreateSchemaString(String schema)
+    {
+        return super.getCreateSchemaString(schema) + " authorization dba";
+    }
 }

[/code]

scooper4711 · **Joined:** Thu Oct 12, 2006 6:12 pm **Posts:** 8

Forgot to mention - that patch is vs version 3.2.5. Use on other versions at your own risk. :-)

max · **Posted:** Sun Aug 10, 2008 5:18 am

ok, and what about other databases ? what if the schema already exists ? ...maybe you will start see why we haven't added it to hibernate ;)

...but feel free to fix those issues and report it in jira with a patch...

scooper4711 · **Joined:** Thu Oct 12, 2006 6:12 pm **Posts:** 8

I never meant to imply that the hibernate team was doing less than a fantastic job.
I had an itch (schema support in hsql with hibernate) and scratched it. Then I saw that someone else has the same itch, so I provided my back scratcher. :-)

The patch provided puts the responsibility of creating the schema on the Dialect. It doesn't break things for other dialects. If someone who uses those database and uses Hibernate's ability to create the schema on system start needs the same support, they should be able to implement it with a single method.

I hope I didn't step on anyone's toes by providing a patch. Btw, all the unit tests still work, even with my patch.

max · **Posted:** Mon Aug 11, 2008 7:03 pm

scooper, you misunderstood me. I think it is great that you are sharing your patch but I was just pointing out that it did not seem that the code checked for the existence of a schema before hand which would be a requirement to match the semantics of the different schemaexport options like drop-create, update, etc.

ahutatatong · **Joined:** Mon Aug 31, 2009 9:50 am **Posts:** 1

I had a similar problem and solved it manually.
After starting the hsql server (at this point i have the neccessary login information) i create the schemas. Consider that this code is used for unit tests. I would when possible try to avoid the schema declaration in Entities anyway and rather let the Entity Manager use different persistence units.

Code:

    @BeforeClass
    public static void beforeClass() {
        // subclass should add repositories and datagenerators
        // in order to do so -> override this function
        logger.info("Starting HSQLDB");
        server = new Server();
      server.setDatabaseName(0, "hsqldb");
        String dbPath = System.getProperty("java.io.tmpdir") + "/hsqldb/";
        server.setDatabasePath(0, dbPath);

        server.start();
        logger.info("Started HSQLDB. Data will be stored under " + dbPath);


        try {
            Class.forName("org.hsqldb.jdbcDriver");
            Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/hsqldb", "sa", "");
            Statement statement = c.createStatement();
            statement.execute("create schema LST");
            statement.execute("create schema MDL");
        } catch (Exception e) {
            e.printStackTrace();
        }

        entityManagerFactory = Persistence.createEntityManagerFactory("hsqldb");
        entityManager = entityManagerFactory.createEntityManager();
    }

regards, Harald

djdenv · **Joined:** Mon Jul 18, 2011 10:50 pm **Posts:** 2

I took the proposed solution a little further...

With junit (in combination with a maven project), you can have a totally seperate hibernate.cfg.xml file (/src/test/resources/hibernate.cfg.xml). In doing so, set up your hsqldb there along with all the properties (username, password, connection.url, etc...) and then pull them in with your normal HibernateUtils.java class like so:

Code:

static {
      try {
         // Create the SessionFactory from hibernate.cfg.xml
         AnnotationConfiguration config = new AnnotationConfiguration()
               .configure();

         // CREATE DATABASE MANUALLY
         String driverClass = config.getProperty("connection.driver_class");

         if (StringUtils.contains(driverClass, "hsqldb")) { //not typically used in production. Optionally could search connectionUrl for ":mem"
            LOGGER.info("HSQLDB configuration detected. Creating schema...");
            Class.forName(driverClass);
            String connectionUrl = config.getProperty("connection.url");
            String connectionUserName = config
                  .getProperty("connection.username");
            String connectionPassword = config
                  .getProperty("connection.password");
            String defaultCatalog = config
                  .getProperty("hibernate.default_catalog");

            Connection conn = DriverManager.getConnection(connectionUrl,
                  connectionUserName, connectionPassword);
            Statement st = conn.createStatement(); // statements
            String expression = "CREATE SCHEMA " + defaultCatalog
                  + " AUTHORIZATION DBA";
            st.executeUpdate(expression); // run the query
            st.close();
            conn.close();
         }

         sessionFactory = config.buildSessionFactory();
      } catch (Throwable ex) {
         // Make sure you log the exception, as it might be swallowed
         System.err.println("Initial SessionFactory creation failed." + ex);
         throw new ExceptionInInitializerError(ex);
      }

djdenv · **Joined:** Mon Jul 18, 2011 10:50 pm **Posts:** 2

Cliver, are the posts above not the answer you're looking for? There are several code samples here that have been vetted and work.