/
Database settings in sakai.properties

Database settings in sakai.properties

May 28, 2006

  • There are currently 3 databases supported in Sakai (Oracle, MySQL, HSQLDB)
    Sakai will use HSQLDB in memory mode by default (once Sakai is shut down all data is lost)

    • Look at the parent page to this one for more info on sakai.properties configuration

  1. Database section - look for the following in the sakai.properties file

    # DATABASE CONFIGURATION - make sure to modify details to match your particular setup

  2. Configuring the username and password, these are used to login to the database when making a connection

    # The username and password. The defaults are for the out-of-the-box HSQLDB. Change to match your setup. username@javax.sql.BaseDataSource=sakai password@javax.sql.BaseDataSource=ironchef

    • The defaults are: username="sa", password="" (blank)

  3. Turning on auto.ddl

    # establish auto.ddl - on by default auto.ddl=true #auto.ddl=false

    • This tells Sakai to create and update database tables automatically, you will want to always leave this on as a developer (default is true/on)

  4. Turning on a database

    • WARNING: You can only have one database configured at a time, if you try to enable more than one by uncommenting lines, either the last one will work or you will get many strange errors

  5. HSQLDB enabling and configuration
    Uncomment the following lines to enable HSQLDB

    ## HSQLDB settings - on by default vendor@org.sakaiproject.db.api.SqlService=hsqldb driverClassName@javax.sql.BaseDataSource=org.hsqldb.jdbcDriver hibernate.dialect=org.hibernate.dialect.HSQLDialect validationQuery@javax.sql.BaseDataSource=select 1 from SYSTEM_USERS # two hsqldb storage options: first for in-memory (no persistence between runs), second for disk based url@javax.sql.BaseDataSource=jdbc:hsqldb:. #url@javax.sql.BaseDataSource=jdbc:hsqldb:${sakai.home}/db/sakai.db

    • HSQLDB is turned on by default, it only stores data in memory by default

    • HSQLDB works well for development and for demos

    • WARNING: Never use HSQLDB in production unless you are an expert on HSQLDB and are running a very small Sakai installation, even then it is not a great idea

    1. To use HSQLDB in file mode (where it stores data on the filesystem), comment out this line:

      url@javax.sql.BaseDataSource=jdbc:hsqldb:.

      and uncomment this one

      url@javax.sql.BaseDataSource=jdbc:hsqldb:${sakai.home}/db/sakai.db

  6. MySQL enabling and configuration
    Uncomment the following lines to enable MySQL

    ## MySQL settings - make sure to alter as appropriate vendor@org.sakaiproject.db.api.SqlService=mysql driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver hibernate.dialect=org.hibernate.dialect.MySQLDialect url@javax.sql.BaseDataSource=jdbc:mysql://127.0.0.1:3306/sakai?useUnicode=true&characterEncoding=UTF-8 validationQuery@javax.sql.BaseDataSource=select 1 from DUAL defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

    • The default configuration points to a MySQL server on your local machine 127.0.0.1, make sure to change this if your database server is located elsewhere

  7. Oracle enabling and configuration
    Uncomment the following lines to enable Oracle

    ## Oracle settings - make sure to alter as appropriate vendor@org.sakaiproject.db.api.SqlService=oracle driverClassName@javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver hibernate.dialect=org.hibernate.dialect.Oracle9Dialect url@javax.sql.BaseDataSource=jdbc:oracle:thin:@your.oracle.dns:1521:SID validationQuery@javax.sql.BaseDataSource=select 1 from DUAL defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED # For improved Oracle performance, implementers using Oracle should strongly consider # enabling all the following settings just as they appear (suggestion from the University of Michigan). #validationQuery@javax.sql.BaseDataSource= #defaultTransactionIsolationString@javax.sql.BaseDataSource= #testOnBorrow@javax.sql.BaseDataSource=false

    • The default configuration points to a made-up Oracle server your.oracle.dns:1521:SID, make sure to fix the jdbc connection string to something real like oracle.edtech.vt.edu:1521:HPRD

    • Pay attention to the comments about performance, you should probably follow them

  8. Turning on/off hibernate logging

    # enable hibernate SQL debugging output hibernate.show_sql=false

    • Change hibernate.show_sql=false to hibernate.show_sql=true to enable verbose hibnerate logging

    • WARNING: Never do this in production unless you are watching it carefully, this can produce massive logs and slow down you server

  9. Sample sakai.properties file: Look at the parent page for a sample