Low Level Database Access: Difference between revisions

{{Navigation for Marauroa Top|Internals}}

The level code database access code is encapsulated in DAO classes. Only these classes use JDBC to send SQL queries to the database.

You start a transaction by obtaining a DBTransaction object from the [http://stendhal.game-host.org/hudson/job/marauroa_HEAD/javadoc/marauroa/server/db/TransactionPool.html TransactionPool]. The you use the methods of the [http://stendhal.game-host.org/hudson/job/marauroa_HEAD/javadoc/marauroa/server/db/DBTransaction.html DBTransaction] object to talk to the database. Note that many methods expect a parameter map as second parameter. This allows you to use <nowiki>[variables]</nowiki> in your SQL statement without having to worry about escaping input parameters to prevent SQL injection attacks.

DBTransaction, however, does not execute the SQL statements itself. It internally forwards them to a subclass of DatabaseAdapter. This is a very tiny abstraction layer of to hide SQL dialects used by different database systems.

[[Image:Classdiagram_database_access.png]]

Please stick to the following rules when writing your own DAO classes

* use <nowiki>[variables]</nowiki> for untrusted data because it will be escaped automatically to prevent SQL Injection Attacks

* register your class in DAORegistry (see next section) instead of calling the constructor directly

* use dbTransaction.getLastInsertId() instead of "select @@identity" and call it directly after the insert in question

* try to avoid database system specific code (pay special attention to generally supported SQL functions)

* provide all methods with two signatures: One with DBTransaction as first parameter and one without. The second one should just get the transaction itself, call the first one, and commit/rollback the transaction

DAO classes should never be instantiated directly. Instead you should (and marauroa does) use the DAORegistry. This allows you to write a subclass of a DAO provided by marauroa and register it instead. If you are familiar with Spring, this is a similar concept. But without all the bulk of xml configuration files, parameter injection and interfaces with only one single implementation.

Imagine you want to subclass the CharacterDAO with your class SomeGameCharacterDAO:

<source lang="java">

public class SomeGameCharacterDAO extends CharacterDAO {

...

</source>

You simply register it as

<source lang="java">

DAORegistry.get().register(CharacterDAO.class, new SomeGameCharacterDAO());

</source>

Note: In the register call the first parameter is the parent class you want to replace.

== Adding support for another database system ==

Marauroa tries to stick closely to the SQL standard. It is, however, not possible to write SQL statements that work on all common database systems because each database system has its own dialect. Luckily there are only minor differences. For example MySQL requires "create table" statements to end in "TYPE=InnoDB". Marauroa uses an interface [http://stendhal.game-host.org/hudson/job/marauroa_HEAD/javadoc/marauroa/server/db/adapter/DatabaseAdapter.html DatabaseAdapter] to hide those differences.

There is already a default implementation provided for this interface called [http://stendhal.game-host.org/hudson/job/marauroa_HEAD/javadoc/marauroa/server/db/adapter/AbstractDatabaseAdapter.html AbstractDatabaseAdapter] ([http://arianne.cvs.sf.net/viewvc/arianne/marauroa/src/marauroa/server/db/adapter/AbstractDatabaseAdapter.java?view=markup source]). And we provide a MySQLDatabaseAdapter ([http://arianne.cvs.sf.net/viewvc/arianne/marauroa/src/marauroa/server/db/adapter/MySQLDatabaseAdapter.java?view=markup source]) and a H2DatabaseAdapter ([http://arianne.cvs.sf.net/viewvc/arianne/marauroa/src/marauroa/server/db/adapter/H2DatabaseAdapter.java?view=markup source]), too.

Just have a look at those java files. It should be very easy to add more adapters for other database systems. Note: We are very interested in accepting those adapters and adding them to the main code base.

== Updating the database structure ==

If you add tables and columns it is a good practice to create them automatically on server start. The method runSQLScript() in the class JDBCHelper can be used to execute an SQL script. Note: You should use "create table if not exists" in stead of simple "create table" so that you can execute the script on every server start without having to worry about whether the table already exists or not.

Unfortunately there is no "create column if not exists" clause in SQL. So if we have the need to add columns to existing tables, we will need to do the check ourselves.

The following example shows the code that was used to add the new result column to the passwordChange table. Before that column was added, only successful password changes were logged. So the result column should be initialized to 1.

<source lang="java">

if (!transaction.doesColumnExist("passwordChange", "result")) {

transaction.execute("ALTER TABLE passwordChange ADD COLUMN (result TINYINT);", null);

transaction.execute("UPDATE passwordChange SET result=1 WHERE result IS NULL", null);

}

</source>

{{#breadcrumbs: [[Marauroa]] | [[Navigation for Marauroa Developers|Internals]] | [[Low Level Database Access|Low Level Database Access]] }}