Internal SQL

Introduction

ClearOS supports a variety of relational databases - MySQL, MariaDB, PostgreSQL, SQLite, MariaDB, and MSSQL (the latter is in beta currently) have been packaged into an app that allows end users of ClearOS to install and setup a database engine with very little knowledge of what's going on 'under the hood', while the others can be installed from the command line using the extended respositories from within ClearOS.

There are, however, times when an app developer doesn't necessarily want the overhead of having to so go through the steps of setting up a RDBMS but needs the functionality. The internal or sandboxed MySQL (really, MariaDB) solves this problem by providing a set of packages that any app can pull in as a dependency and configure a database programatically.

Including the Sandboxed MySQL/MariaDB Database Engine in Your App

To ensure availablity of the MySQL/MariaDB database in your app, simply add the following to your deploy/info.php app metadata file:

$app['core_requires'] = array(
    'app-system-database-core',
);

This will ensure the database is available on installation and that it won't disappear (eg. cannot be removed) without a dependency to remove your app as well.

Examples

There are several examples that you can model development of your app from. Network and web content reporting uses the database to store information to create tabular and graphical reports. ownCloud and Kopano use this method to allow the app to be completely bootstrapped programatically without any user intervention. The Mail Archive is a custom app that uses an RDBMS to store information on mail that has come through the server and allow it to be searched quickly.

Access

Location

All binaries and conriguration files are stored in exactly the same location as any MySQL install, with the exception that the root path (/) is replaced with:

/usr/clearos/sandbox

For example, the mysql binary to acces to the console would be found here:

/usr/clearos/sandbox/usr/bin/mysql

and the MySQL server conriguration file here:

/usr/clearos/sandbox/etc/my.cnf

Port

The internal database uses a non-standard, port 3308, in order to access the database to avoid conflict with the standard MySQL or MariaDB databases that may be installed on the system.

Password

By convention, the sandboxed MySQL/MariaDB engine is automatically configured with a master root password that is saved to the following location:

/var/clearos/system_database/root

Console

A bit of lengthy script that could be added to your bash shell as an alias can save a lot of time accessing the database during development!

/usr/clearos/sandbox/usr/bin/mysql -uroot -p$(sed "s/password\s*=\s*//" < /var/clearos/system_database/root)

Bootstrapping

Let's take a look at the following script that initializes the Kopano database for the mail and collaboration suite app:

#!/bin/sh

MYSQL="/usr/clearos/sandbox/usr/bin/mysql"
MYSQLADMIN="/usr/clearos/sandbox/usr/bin/mysqladmin"

DB_CONFIG="/var/clearos/system_database/root"
APP_CONFIG="/etc/kopano/server.cfg"
APP_DB_CONFIG="/var/clearos/system_database/kopano"
APP_DB_NAME="kopano"
APP_DB_USERNAME="kopano"

# Start system database
#----------------------

/usr/clearos/apps/system_database/deploy/bootstrap

# Grab root database password
#----------------------------

ROOTPASS=`grep ^password $DB_CONFIG 2>/dev/null | sed "s/^password[[:space:]]*=[[:space:]]*//"`

if [ -z "$ROOTPASS" ]; then
    echo "Unable to authenticate with database"
    exit 1
fi

# Create databases (if necessary) 
#--------------------------------

$MYSQL -uroot -p"$ROOTPASS" -e 'status;' $APP_DB_NAME >/dev/null 2>&1

if [ $? -ne 0 ]; then
    echo "Creating $APP_DB_NAME database"
    $MYSQLADMIN -uroot -p"$ROOTPASS" create $APP_DB_NAME >/dev/null 2>&1
fi

# Add/Update database password
#-----------------------------

APP_PASSWORD=`grep ^password $APP_DB_CONFIG 2>/dev/null | sed "s/^password[[:space:]]*=[[:space:]]*//"`

if [ -z "$APP_PASSWORD" ]; then
    echo "Generating password"
    APP_PASSWORD=`openssl rand -base64 20`
    touch $APP_DB_CONFIG
    chmod 600 $APP_DB_CONFIG
    echo "password = $APP_PASSWORD" >> $APP_DB_CONFIG
fi

APP_PASSWORD_SEDSAFE=`echo $APP_PASSWORD | sed 's/\//\\\\\//g'`

echo "Updating privileges"
$MYSQL -uroot -p"$ROOTPASS" -e "GRANT ALL PRIVILEGES ON $APP_DB_NAME.* TO $APP_DB_USERNAME@localhost IDENTIFIED BY \"$APP_PASSWORD\" WITH GRANT OPTION;" $APP_DB_NAME >/dev/null 2>&1

# Update app configuration file
#------------------------------

if [ -e /etc/kopano/server.cfg ]; then
    echo "Setting database password in app configuration"
    sed -i -e "s/^mysql_database[[:space:]]*=.*/mysql_database = $APP_DB_NAME/" $APP_CONFIG
    sed -i -e "s/^mysql_user[[:space:]]*=.*/mysql_user = $APP_DB_USERNAME/" $APP_CONFIG
    sed -i -e "s/^mysql_password[[:space:]]*=.*/mysql_password = $APP_PASSWORD_SEDSAFE/" $APP_CONFIG
    sed -i -e "s/^mysql_host[[:space:]]*=.*/mysql_host = 127.0.0.1/" $APP_CONFIG
    sed -i -e "s/^mysql_port[[:space:]]*=.*/mysql_port = 3308/" $APP_CONFIG
fi

As you can see, it's a simple bash script. The first section declares some variables with locations to scripts and paths that will be used throughout. Next, we make sure the System (Sandboxed) Database is running through the use of a convenience function. Your app may not be the first to pull it in and if so, this step is unnecessary, but harmless.

We then extract the root password so that we can use it to add our own database and create a new user for the app. We save this user in the same format as the root user.

/var/clearos/system_database/basename

Finally, we store the credentials and configuration (eg. host, port etc.) wherever it may be required. In this case, it's Kopano's /etc/kopano/server.cfg configuration file.

You can name this database bootstrapping script anything, but by convention, you'll see it named initialize-database in the apps that use it. Add it to the deploy/ folder in your app so that it is included in your package. Add the full path script name to your install file, also found in the deploy/ folder of your app.

This script gets called automatically with the installation of your app, ensuring the system database and the database you created for your app will be available.