database installation

Database installation

Before you can set up your own server you have to decide wich database backend you want to use. Currently, the server supports three different types of database backends: * mySQL * SQLite * PostgreSQL

Settings up SQLite (Default database)

Requirements

So far, the Mana Server has been tested with SQLite version 3.6.23.1

Preparation of database file

The creation of a new database file is very easy. Just go to the directory where you want to create it and type:

sqlite3 mana.db < ${the Mana server-dir}/src/sql/sqlite/createTables.sql

This will create the mana.db file and execute the SQL statements in createTables.sql. To verify if the installation was successful, you could do in a shell prompt:

$ sqlite3 mana.db sqlite> .tables

This should give you a list of mana_* tables.

Configuring the Mana server to use the database

Before starting the Mana server, you'll have to configure the account server where your database file is located.
The Mana server engines use a XML file called [manaserv.xml](manaserv.xml.md) with an easy to read key = value structure.

Locate the option sqlite_database and modify the value attribute according to your environment. You can use a relative path starting at the location of your the Mana server executable.

 <option name="sqlite_database" value="./mana.db"/>

Settings up MySQL

Requirements

To run the Mana server with MySQL you should use a MySQL version >= 5.0.*. It was tested successfully with 5.0.51a.
Note: You need to compile the Mana server with MySQL support to be able to use MySQL: See Compilation of the Mana server for more information

Creating a database and a user

This chapter assumes, that you still have little experience working with mysql, so it does not describe how to install MySQL itself.
The first step to get the Mana server running under MySQL would be the creation of a database and a user.

As you can easily do this with graphical frontends like phpMyAdmin, we will give you a handy script for doing that from command line.
Don't forget to replace the placeholders with appropriate values for your environment.

For the following commands you have to be logged in as a database administrator, preferably root.

CREATE USER 'manasys'@'localhost' IDENTIFIED BY '<insert your favorite password here>';

GRANT USAGE ON * . * TO 'manasys'@'localhost' IDENTIFIED BY '<the chosen password>' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

CREATE DATABASE IF NOT EXISTS mana;

GRANT ALL PRIVILEGES ON mana . * TO 'manasys'@'localhost';

In short, we have now a new database called mana, a new database user manasys that has full rights in its database, and the restriction that it is only able to connect from localhost.

Create database tables

If our new database user is ready for use, we have to create all necessary tables. The installation of the Mana server is providing you a ready to use database script.

You can find this script in the following folder: ./src/sql/mysql/createTables.sql
To run this script connect as user manasys to your database from a shell prompt, fro instance:

mysql --host=localhost --user=manasys --password=<password> --database=<mana>

After typing your password you should be connected to the database. The command \. runs a database script:

. <path to the script>/createTables.sql -- e.g. . ~/manaserv/src/sql/mysql/createTables.sql

Configuring the Mana server to use the database

The following options need to be set in your manaserv.xml (change them to your actual used settings):

<option name="mysql_hostname" value="127.0.0.1"/> <option name="mysql_port" value="3306"/> <option name="mysql_database" value="mana"/> <option name="mysql_username" value="mana"/> <option name="mysql_password" value="password"/>

Settings up PostgreSQL

'TODO'