Creating a Valid SQL Script to Generate a SQL Server Database

Question

I'm working on testing an application that utilizes a SQL Server database. For testing purposes, I'd really like to be able to utilize an in-memory database, like HSQL. I actually have my tests running but I have one pain point still outstanding - I have a difficult time generating a SQL script that I can use to build my HSQL database. As this system is still in development, database changes can and do happen so I don't want to spend all my time manually updating SQL scripts.

Within SQL Server Management Studio, I can generate a script to build that database. That script, however, contains a lot of "stuff" that is SQL Server specific. As such, I can't just take that script to HSQL and have it execute. Instead, I need to spend a lot of time tearing out the stuff that won't work in HSQL.

A co-worker suggested using PowerBuilder to generate the script and, while the script it generated was somewhat cleaner, it still wouldn't run "as-is" in HSQL.

Can anyone suggest a method (tool, process, etc.) that I can use to point at a SQL Server database and generate myself a script that will build that exact same database in HSQL?

Thanks!

Answer 1

Assuming you are using Hibernate, you can configure Hibernate in your testcase to create the tables when the sessionFactory is created.

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="mappingResources">
        <list>
            <value>...hbm file...</value>
            ...
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
            ...
            <prop key="hibernate.hbm2ddl.auto">create</prop>
        </props>
    </property>
</bean>

The only cons to this approach is if your HBM files don't have the matching settings as your physical SQL Server database (for example, you omit certain constraints or columns in your HBM files), then the created tables in HSQL are going to be different.