mAirListDB Networked SQL Connection Setup

This tutorial shows how to set up the connection to an SQL server and initialize an empty mAirListDB database on that server.

This article only covers the “true” (networked) SQL backends: PostgreSQL, MSSQL, MySQL. For the “local mode” database (SQlite), setup instructions are available in a separate tutorial.

You must prepare your SQL server for use with mAirListDB first. In particular we will need:

• An empty database (no tables created yet!)
• A user + password to access that database, with sufficient credentials (often the owner of the database)

Setup instructions are very similar for each SQL server type, but we have separate tutorials available for that:

• PostgreSQL setup
• Microsoft SQL Server setup
• MySQL setup

Please set up the server first according to your particular SQL flavor, then return to this article to see instructions for the client setup. In this tutorial we will use PostgreSQL as an example.

In this step, we will set up the connection from the first mAirList computer in your network, and also initialize the database, that is, create the necessary tables etc.

We assume that you have installed and activated mAirList on your computer. Please open the configuration app from the Windows Start Menu.

In the config app, go to Databases, click Add and choose the appropriate connection type that matches your SQL server flavor. In our example, it's mAirListDB (networked, PostgreSQL). The mAirListDB connection properties dialog will appear.

On the General tab, enter the host name or IP of the SQL server, the database name (mairlist5 in the example above) and the name and the password of the user that you created during the server setup (mairlist in our example):

Click Test login to check if the SQL connection works before you continue. You will most likely see an error saying that the SQL connection was successful but the database (and the “config” table) cannot be accessed - this is OK at this point, as we haven't created the tables yet.

Now switch to the Setup tab to create the tables (“database schema”) inside the SQL server. This must only be done once, from the first client computer that you set up! Do not repeat this on the other computers!

The initial setup is usually performed with the same user as entered on the General page (the default login), as we assume that it is the “owner” of the SQL database. If this is not the case, i.e. and the default user does not have the required rights to create tables inside the database, you can enter another set of credentials to be used for the setup.

Now click the Perform Initial Setup button and wait. mAirList will now create the SQL tables necessary to hold the mAirListDB library and scheduling data. This might take a few seconds and will eventually be confirmed with the messsage Setup completed successfully:

If you want to connect additional mAirList computers to the database, go to the mAirList configuration app on each computer, and add the connection as described above. Now that we have created the tables, the Test login button should give the message: Login OK

When you click OK to save the connection, you will see the following message:

Each mAirListDB has a unique identifier (made up of several random hexadecimal characters), the “Database ID”. The ID is stored in the config table inside the SQL server, and also in the database.ini file of each client PC. It will help mAirList to identify the correct connection if you happen to maintain connections to more than one mAirListDB at a time. Clicking Yes in this dialog will retrieve the ID from the SQL table and store it in the local configuration. (You can look it up on the Advanced tab in the connection properties dialog if you like.)

When you install a new version of mAirList, it can happen that the mAirListDB connection stops working, and you see a message similar to the following:

Invalid schema version (found xx, need yy). Please upgrade your database.

This happens when the new software version to add or modify SQL tables to support new functionality. We must perform a Schema Upgrade then.

To perform the schema upgrade, go to the Upgrade tab in the mAirListDB connection properties dialog. You must do this only once for each database.

First, click the Check now button to find out what your current schema version is, and if it matches the required version.

If an upgrade needs to be performed, click Upgrade and wait for the upgrade to complete.