Mem DB - Best Practice for DDL on Startup?

Help
Anonymous
2012-05-09
2014-01-19

  • Anonymous
    2012-05-09

    Hello.  New to hsqldb.

    Background:  We are considering using hsqldb for a project that requires a small in memory DB.  It would probably not be embedded, running separately in its own Listener JVM.  All of its tables would be pure Memory Table, speed is important so we want to minimize file IO.  DB would have Lots of inserts and lots of selects constantly.  We'd control its size by throwing away all data older than some threshold.

    I've made a SQL script that runs on HSQLDB that creates a Schema, sets up some simple Users and Roles, and creates several tables in the Schema.  I need all these objects to be in a pure Memory DB.  What's the best way to configure the Server (Listener) to run this script on startup?  I've been searching through the documentation and can't find anything about running scripts for creating Memory tables on startup.  I see there is a .scripts file in my DB's directory, but putting the script at the end of this file doesn't work at all.

     
  • Fred Toussi
    Fred Toussi
    2012-05-09

    Use a file: database and turn logging off. See the Guide, Deployment Chapter

     
  • Fred Toussi
    Fred Toussi
    2012-05-09

    Execute your schema creation statements via SqlTool, then shutdown the database. The .script file will be written out in the internal, supported format

     

  • Anonymous
    2012-05-09

    Alright!  I did that it works.  I don't quite understand why, but it does.  Questions and comments:

    In the .script file it shows those tables I created as Memory Tables even though it is a file database.  Does turning off the logging make it work like a mem: database?

    I tried setting it back to a mem: database in my server.properties after getting it working as a file: database.  I guess mem: DBs don't read the .script file?  But thankfully it didn't alter my .script file.  Setting it back to file: in server.properties  and restarting the server made everything good again, with my schema looking good.

    I followed your instructions using DatabaseSwingManager instead of SqlTool.  Seemed to work ok.  I was careful to do these things in the GUI:

    SET FILES LOG FALSE;
    CHECKPOINT;
    SHUTDOWN;

    I tried using the property to shut off my logging in my server.properties, but it threw an error and didn't work:
    hsqldb.log_data=false

     
  • Fred Toussi
    Fred Toussi
    2012-05-09

    MEMORY tables in a file: database are kept entirely in memory. A file database reads the data for these tables from the .script file.

    There is one more step, to set the files_readonly in the .properties file to protect the .script file from change.