Creating the database

This chapter contains general notes about database configuration and basic instructions for creating the database using the standard command line tools. If you use a different tool for managing the databases, refer to the relevant documentation for more information.

General notes

You can use an existing database instead of creating a new one. Just ensure that the tables used by the WebIssues Server have a unique prefix to avoid clashes with other tables. This way you can also install multiple instances of the server sharing a single database.

The database user used by the WebIssues Server to login to the database must have sufficient privileges to create tables. The simplest solution for MySQL is to grant ALL PRIVILEGES to the database; for PostgreSQL and SQL Server assign the user to the database owner role. Refer to the database documentation for more information about privileges.

Note

WebIssues uses the Unicode standard to store all text. If you use PostgreSQL, you must create the database with UNICODE encoding selected. If you use MySQL version 4.1 or later or SQL Server, WebIssues will automatically use the appropriate encoding.

If you use MySQL 4.0 or earlier, text is stored using the UTF-8 encoding even though the database doesn't support it, so Unicode characters will be seen as sequences of bytes. This may lead to problems when accessing the database from other applications and when transferring or upgrading the database. You may find instructions for upgrading the MySQL database from version 4.0 or earlier in the MySQL manual.

MySQL

Create the database (dbauser is the login of the database administrator, database is the name of the database to create):

mysqladmin -u dbauser -p create database

You will be prompted for the database administrator's password.
Connect to the database server:

mysql -u dbauser -p
From the MySQL command prompt execute the following query to create a user with access to the database (user is the user's login, password is the user's password and database is the name of the database):

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';
Execute the following query to activate the new permissions:

FLUSH PRIVILEGES;

PostgreSQL

Create the user account (user is the user's login):

createuser --pwprompt --encrypted --no-adduser --no-createdb user

You will be prompted for the password for the new user. Note that you have to log in as a user with appropriate permissions to manage the PostgreSQL server.
Create the database (user is the user's login, database is the name of the database to create):

createdb --encoding=UNICODE --owner=user database

SQL Server

Connect to the database server:

sqlcmd.exe -U dbauser -P dbapassword

Use osql.exe instead if you have SQL Server 2000. Replace dbauser and dbapassword with the login and password of the database administrator.
Execute the following queries to create a user's login and the database (user is the user's login, password is the user's password and database is the name of the database to create):

CREATE LOGIN user WITH PASSWORD = 'password';
GO
CREATE DATABASE database;
GO
Execute the following queries to assign appropriate permissions for the database to the user (user is the user's login, database is the name of the database):

USE database;
GO
EXEC sp_grantdbaccess 'user';
GO
EXEC sp_addrolemember 'db_owner', 'user';
GO

Firebird

Create a user account for connecting to the database (user is the user's login, password is the user's password):

gsec -user sysdba -password masterkey
GSEC> add user -pw password
GSEC> quit

Use the appropriate administrator's password instead of masterkey.
Create the database (/path/to/database.fdb is the path of the database file, user is the user's login and password is the user's password):

isql
SQL> create database '/path/to/database.fdb'
CON> user 'user' password 'password'
CON> page_size 16384
CON> default character set UTF8;
SQL> quit;

Note that you may need to set approprate permissions for the database file so that it's accessible for the Firebird server. Refer to Firebird documentation for more details.

« Upgrading the WebIssues Server

Security settings »