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.
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.
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.
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:
For example, the mysql binary to acces to the console would be found here:
and the MySQL server conriguration file here:
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.
By convention, the sandboxed MySQL/MariaDB engine is automatically configured with a master root password that is saved to the following location:
It is good practice to create your own user/pass account for your app.
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)
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.
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.
Make sure both your install and initialize-database are executable (eg. chmod 750)
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.
Copyright © 2009- ClearCenter