By default RAMADDA uses a built in Java Derby relational database. RAMADDA also can run using MySQL, PostgresSQL, Oracle and H2. Derby works pretty well though there are some performance issues with search and doing many deletes. If you are planning on really using RAMADDA and foresee a large amount of content stored in the repository (by large we we mean the number of entries in the repository not the overall file size) then its probably a good idea to use mysql or postgres.

The database to use and its connection parameters are specified in a .properties file, e.g., db.properties. The properties file can be placed in the ramadda home directory. To define the database just uncomment the appropriate ramadda.db properties in repository.properties, e.g.:

ramadda.db=derby
#ramadda.db=mysql
#ramadda.db=postgres
#ramadda.db=h2

And then specify the jdbc url and the login credentials. The URLS can be of the form:

jdbc:<db type>://<hostname>/<database name>
jdbc:<db type>:<database name>
jdbc:<db type>://<hostname>/<database name>
jdbc:<db type>://<hostname>:port/<database name>

If you are running the database on the same machine as RAMADDA the hostname can be "localhost". In the below examples we assume a database called "repository".

MySQL

For MySQL:

ramadda.db=mysql
ramadda.db.mysql.url=jdbc:mysql://localhost:3306/repository?zeroDateTimeBehavior=convertToNull
ramadda.db.mysql.user=the database user
ramadda.db.mysql.password=the database password

Postgres

For Postgres:

ramadda.db=postgres
#note: the jdbc url uses "postgresql" as the database type
ramadda.db.postgres.url=jdbc:postgresql://localhost/repository
ramadda.db.postgres.user=the database user
ramadda.db.postgres.password=the database password

H2

For H2 the default setting specifies the database to be in the RAMADDA home directory under the sub-directory h2db. The JDBC URL has the full file path of where to store the database.

ramadda.db=h2
ramadda.db.h2.url=jdbc:h2:%repositorydir%/h2db
ramadda.db.h2.user=
ramadda.db.h2.password=

Database Issues

Because RAMADDA can run on top of most any relational database the SQL it uses is pretty simple and has to be the lowest common denominator. (Sorry, no fancy Oracle tricks, etc.) There are a few things that have to be dealt with though for each database in RAMADDA code. For example, mysql and derby use "double" for the primitive double type. Postgres uses "float8". Likewise there are issues with date/time. From repository/resources/makedb.sql is the comment:

---Note: the ramadda.datetime and ramadda.double are replaced by ramadda
---with the appropriate datatype for the database being used.
---mysql has a datetime type, postgres and derby have timestamp
---we can't use timestamp for mysql because it only goes back to 1970
---derby and mysql have double. postgres has float8

What we do is use the class org.ramadda.repository.database.DatabaseManager to convert the sql text to the correct syntax of the underlying database. This is mostly done in the DatabaseManager.convertSql method.