Roxen Community: The Roxen 2.2 database interface

Author: Per Hedbor <per@roxen.com>
Last modified: 2001-09-11 10:55:24
This article aims to introduce the new database support in Roxen 2.2. It also tries to give the reader enough information to write modules and RXML-pages that use the support.

I also try to explain some of the oddities

Note: All features mentioned in this article are not yet finished as of the writing of this article. Specifically, the configuration interface graphical database manipulation tools are still in their infancy.

So. Let's boldly go forth into the world of integrated databases

The author, Per Hedbor
<per@roxen.com>

From version 2.2 and onwards, binary releases of Roxen are bundled with a MySQL database server, and Roxen requires a MySQL to start.

Several internal databases, such as the pre-compiled object-files (.o), the image and argument caches and the documentation are now stored in this mysql, and modules can create tables in it.

Actually, almost all state except the configuration files are saved in the database, and the only reason for not storing the configuration data in the database is that quite a lot of people really want to have readable configuration files.

In the future the configuration data will move into the database as well.

To make it easier to write database applications.

To simplify the core code of Roxen WebServer.

There are a lot of caches and databases in Roxen, and they previously often had support for file-based storage and SQL-storage. When you know that you have a database available, this code can be simplified, and new features can be added.

As an example, the image cache is now stored in a MySQL database and does cache-cleaning, it also scales well, something that was not true before, and would have been much harder to implement without a database.

Sorry, you can't. You have to use a rather modern MySQL. We made this decision because it allows us to use MySQL specific features in the SQL queries.

The easiest way is probably to use the support functions in module.pike, which are all inherited by your module.

They are designed to provide easy access to a single database with multiple tables without the hassle of keeping the table names unique between different modules.

The table names generated by these functions are unique to your module, since they include a hash of the configuration name and the name of your module with the name you specify for the table.

The drawback is that you cannot directly use the table name you specified in a query (such as "SELECT * FROM data", you must use a special syntax "SELECT * FROM #data#" instead.

Also, the table creation functions will in the future be extended in such a way that you can add or remove columns and indices, or change (within reason) the definition of columns in your table definition, and then automatically get changed tables in the database.
The utility functions are:

void set_my_db( string database )

Set the database your module will store its tables in. The default database is called 'shared', but you might want to use another one.
You must call this function before get_my_table and create_tables since changing the database does not currently move the tables.

This is supposed to be fixed before the actual release, but time considerations might make that difficult.

void create_sql_tables( mapping(string:array(string)) definitions)

Create one or more tables. See the example for the usage of create_tables.

string get_my_table( string name, array(string) definition )

Create and/or look up the name of a single table, and return the actual name. The supplied name is not the name the table will actually get, so you do not have to take care to keep it unique between different modules. Please note that you will normally use the create_sql_tables function, which in turn uses this function.

array(mapping) sql_query(string query,mixed...args)
array(mapping) sql_query_ro(string query,mixed...args)
object sql_big_query(string query,mixed...args)
object sql_big_query_ro(string query,mixed...args)

Do an actual query. These functions mirrors the query and big_query methods of the Sql.Sql objects in Pike, with two rather important differences.

#table-name# will be replaced with the actual table name, where 'table-name' is the name specified in either create_tables or get_my_table.

The query will automatically find a suitable sql-object using the DBManager (more about the DBManager later on).

The "_ro" versions of the functions try to operate the database in read-only mode.

Sql.Sql get_my_sql( int read-only )

Return the actual SQL-connection object. Please note that you cannot safely cache this in a module-global variable, since it has to be thread-local for most databases. Instead, use this function whenever you need it. It does the caching for you.
The SQL-object is useful for things like the insert_id in MySQL.

A short module fragment using the support functions:
private constant table_definitions = 
([
  "data": ({ "id INT UNSIGNED PRIMARY KEY",
   	     "data MEDIUMBLOB" }),
  "meta": ({ "id INT UNSIGNED PRIMARY KEY",
	     "data MEDIUMBLOB" })
]);

private void insert(int id, mapping data, mapping meta)
{
  sql_query( "INSERT INTO #data# (id,data) VALUES (%d,%s)",
             id, encode_value(data) );
  sql_query( "INSERT INTO #meta# (id,data) VALUES (%d,%s)",
   	      id, encode_value(meta) );
}

void start( int a, Configuration c )
{
  // Use the 'shared' database. This is the default, but you might want
  // to use another database instead, that's why I show this code.
  set_my_db( "shared" );
  create_tables( table_definitions );
}
The DBManager should be used when you want to use your tables in multiple modules (actually, you can do that using the module local support functions as well, simply call sql_query and friends in the module that created the tables) or when you want to use multiple databases.

You can also use it when you want to create new databases in the internal MySQL database, and when you want information about the existing ones.

Following is a short example module fragment that defines a module variable to use to select the database, and shows how to create some tables in it and how to do a query.
void create_tables()
{
  Sql.Sql sql = DBManager.cached_get( query( "db" ), my_configuration() );
  if( !sql )
    report_error("Cannot create tables, can not connect to %s.",
                 query("db"));
  else
    sql->query( 
#"create table if not exists uri 
                        (id          int unsigned primary key
                                     auto_increment not null,
                         uri         blob not null,
                         uri_md5     varchar(32) binary not null,
                         UNIQUE(uri_md5))"

}


void create( )
{
  defvar( "db", Variable.DatabaseChoice( " none", 0, "Database",
                                          "The database to use" )
                ->set_configuration_pointer( my_configuration ) );
}
As you can see, you normally only need to use DBManager.cached_get to get the Sql.Sql object you will use to do your SQL-queries, but I have included most methods in this overview:
void DBManager.add_dblist_changed_callback( function(void:void) callback )

Add a function to be called when the database list has been changed. This function will be called after all create_db and drop_db calls.

int DBManager.remove_dblist_changed_callback( function(void:void) callback )
Remove a function previously added with add_dblist_changed_callback. Returns non-zero if the function was in the callback list.

array(string) DBManager.list( void|Configuration c )

List all database aliases.
If c is specified, only databases that the given configuration can access will be visible.

mapping DBManager.db_stats( string name )

Return statistics for the specified database (such as the number of tables and their total size). If the database is not an internal database, or the database does not exist, 0 is returned.

int DBManager.is_internal( string name )

Return true if the DB name is an internal database.

string DBManager.db_url( string name, int|void force )

Returns the URL of the db, or 0 if the DB name is an internal database and force is not specified. If force is specified, a URL is always returned unless the database does not exist.

Sql.Sql DBManager.get( string name, void|Configuration c, int|void ro )
Sql.Sql DBManager.cached_get( string name, void|Configuration c, void|int ro )

Get the database name. If the configuration c is specified, only return the database if the configuration has at least read access.
cached_get() is identical to get(), but the authentication verification and mapping database name to DB-url mapping is cached between requests.

void DBManager.drop_db( string name )

Drop the database name. If the database is internal, the actual tables will be deleted as well, otherwise only the mapping from name to the database URL is deleted.

void DBManager.create_db( string name, string path, int is_internal )
Create a new symbolic database alias.
If is_internal is 1, the database will automatically be created if it does not exist, and the path argument is ignored.

If the database name already exists, an error will be thrown.

int DBManager.set_permission( string name, Configuration c, int level )
Set the permission for the configuration c for the database name to level.
Levels:
 DBManager.NONE:  No access
 DBManager.READ:  Read access
 DBManager.WRITE: Write access
Please note that for non-local databases, it's not really possible to differentiate between read and write permissions, Roxen does try to do that anyway by checking the requests and disallowing anything but 'select' and 'show' from read only databases. Please note that this is not really all that secure, it is still possible to circumvent it with clever SQL queries, but it prevents most mistakes.

From local (in the MySQL used by Roxen) databases, the permissions are enforced by using different users, and should be secure as long as the permission system in MySQL is not modified directly by the administrator.

This function returns 0 if it fails. It only does so if the database name does not exist.
If you want to read more, the source-code for the DBManager module resides in server/etc/modules/DBManager.pmod.

Using your databases from RXML is rather simple:
     <emit source=sql db=local query='the database query'>
     </emit>
You can also access module databases like this:
     <emit source=sql module='identifier!0' query='the database query'>
     </emit>
The number after '!' in the identifier is the module copy number. If it is 0, you can omit the !0 part entirely if you want to.

The module identifier is the non-extension part of the module file-name, without the path.

As an example, if your module is named "modules/databases/tablehandler.pike", and you want to access the second copy of it in the current configuration, the full identifier would be "tablehandler!1".

In this case, #table# is replaced with the actual table names, just as for in-module queries. Also, the correct database is automatically used.

This is somewhat harder than accessing it from the within Roxen WebServer.
If you start a script from one of your Roxen modules, you can pass the path to the MySQL as a variable, say:
 Stdio.File start_script( string executable, array args )
 {
    mapping env = getenv();
    Stdio.File file = Stdio.File();

    env->MYSQL_URL = DBManager.db_url( query("db"), 1 );

    Process.create_process( ({ executable }) + args,
                            ([ 
                                "env":env,
                                "stdout":file->pipe(),
                            ]) );

    return file;
 }
Then use the MYSQL_URL environment variable (this will be a pike-style URL, and if you write your script in another language, you will have to parse it) to connect to the MySQL server.

If you don't the MySQL socket for the internal database can be found in the configuration directory, and is named '_mysql/socket'.

On NT-systems MySQL use the default socket.

You can access the Roxen databases from the command-line mysql program if you have read/write access to the mysql socket file (which, as mentioned above, is located in the "_mysql/" sub-directory of the configuration directory, which in turn normally is accessed as "../configurations" relative to the server directory)

Let's say that your Roxen server is installed in '/home/per/roxen/Roxen/2.2/' (not very likely, but anyway) and the configurations are stored in the 'configurations' sub-directory. Then you would start the mysql command-line client like this:
    mysql --socket=/home/per/roxen/Roxen/2.2/configurations/_mysql/socket -urw
There is a directory called 'mysql' in the Roxen 'server'. Link the 'libexec' and 'share' from the $prefix where mysql was installed (often /usr/local or /usr) to that directory using 'ln -s'. If your mysqld binary does not reside in 'libexec', link the directory it is located in instead (typically 'sbin' or 'bin').

Alternatively, you can install a binary release of mysql in the mysql directory instead of linking a already existing one.

This is probably the most common problem, you want to share some data between multiple servers (say, a ordering system and a order status system).

This is how you do it:

Set up a stand-alone database server.

Create a new symbolic database that points to this server in the Roxen configuration interface (do not forgive to give the appropriate access level to the involved configurations).

Use this symbolic database in your modules.

Let's say that you are running a multiple front-end system, and you want to share the argument cache (among other things) between your different servers.

How do you do this?

The answer is: You locate the 'shared' database on a stand-alone mysql server

And how do you do this, then?

First, set up the MySQL that will store your shared database.

Then, in the Roxen configuration interface, locate the 'shared' database under the DB-tab, select it, and then press 'Move database'.

Then type in the URL of the newly created server.

Shut down your Roxen server, and copy the 'configurations' directory to your other front-end servers. You can remove the configuration interface server from the copies.

Start all your servers.

That's, hopefully, all there is to it.