<?php
# autoprefix_mysql.php - Automatic selection of MySQL table prefix
# Version 0.2  Sun, 26 Nov 2006 02:04:34 -0600
#
# Copyright (C) 2006  Dwayne C. Litzenberger <dlitz@dlitz.net>
# 
# Permission is hereby granted, free of charge, to any person obtaining
# a copy of this software and associated documentation files (the
# "Software"), to deal in the Software without restriction, including
# without limitation the rights to use, copy, modify, merge, publish,
# distribute, sublicense, and/or sell copies of the Software, and to
# permit persons to whom the Software is furnished to do so.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
# A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
# OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
# SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
# LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
# DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
# THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
# OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

# Usage:
#
#  1. Define AUTOPREFIX_APP_UUID as a universally unique identifier for your
#  application (see RFC 4122).
#
#  2. Define AUTOPREFIX_SUGGESTED_PREFIX as a 2-33 character string containing
#  the suggested prefix for your application's database tables.  (For example,
#  "yourapp_")
#
#  3. Optionally define AUTOPREFIX_APP_INSTANCE as string representing this
#  specific instance of your application.  This value should be configurable by
#  the administrator.  The default value is the empty string.  A good default
#  for AUTOPREFIX_APP_INSTANCE is $_SERVER['SERVER_NAME'].  Note that the
#  $app_instance parameter overrides this setting.
#
#  4. Optionally define AUTOPREFIX_GEN_PREFIX as a prefix that will be used in
#  case your AUTOPREFIX_SUGGESTED_PREFIX is already taken.
#
#  5. Optionally define AUTOPREFIX_META_PREFIX as a string that will be
#  prepended to *every* table name used and returned by this script.
#
#  6. Call autoprefix_mysql_get_prefix to get the database table prefix for
#  your application.

require_once "mysqlext.php";
require_once "uuid.php";

function autoprefix_mysql_get_prefix($db_conn, $app_instance=null)
{
    # Perform sanity checks on the inputs
    if (!defined('AUTOPREFIX_APP_UUID') or
        !uuid_is_valid(AUTOPREFIX_APP_UUID))
    {
        die("AUTOPREFIX_APP_UUID must be defined and a valid UUID string");
    }
    if (!defined('AUTOPREFIX_SUGGESTED_PREFIX') or
        !preg_match('/^[[:alpha:][:digit:]_]{2,33}$/s',
            AUTOPREFIX_SUGGESTED_PREFIX))
    {
        die("AUTOPREFIX_SUGGESTED_PREFIX must be set, must contain only " .
            "alphanumerics and underscores, and must be between 2 and 33 " .
            "characters in length.");
    }
    if (!is_resource($db_conn)) {
        die("db_conn must be a mysql connection resource");
    }
    
    # Define some useful variables
    if (!isset($app_instance) or is_null($app_instance)) {
        $app_instance = defined('AUTOPREFIX_APP_INSTANCE') ?
            AUTOPREFIX_APP_INSTANCE : '';
    }
    $meta_prefix = defined('AUTOPREFIX_META_PREFIX') ?
        AUTOPREFIX_META_PREFIX : '';
    $gen_prefix = defined('AUTOPREFIX_GEN_PREFIX') ?
        AUTOPREFIX_GEN_PREFIX : 'autoprefix_';
    $main_table = $meta_prefix.'autoprefix_24823d5c-0803-11db-8b07-0002a50ec775_0';
    
    # Check if the prefix-list table exists
    $result = mysql_execute($db_conn, "SHOW TABLES LIKE ?", array($main_table))
        or die("Error listing autoprefix table");
    $table_exists = false;
    while($row = mysql_fetch_row($result)) {
        // Some characters (% and _) are wildcards for SHOW TABLES LIKE, so we
        // iterate through the list of returned rows and make sure we really have an
        // exact match.
        if ($row[0] === $main_table) {
            $table_exists = true;
            break;
        }
    }
    mysql_free_result($result);

    # If the prefix-list table does not exist, create it.
    if (!$table_exists) {
        mysql_execute($db_conn, "
            CREATE TABLE `$main_table` (
                id          BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
                uuid        CHAR(36),
                instance    VARCHAR(255),
                prefix      VARCHAR(64),
                UNIQUE INDEX(uuid, instance),
                UNIQUE INDEX(prefix) )")
            or die("Could not create autoprefix table");
    }
    unset($table_exists);
    
    # Attempt to find out the existing prefix
    $result = mysql_execute($db_conn,
        "SELECT prefix FROM `$main_table`
         WHERE uuid = ? AND instance = ? AND prefix IS NOT NULL
         LIMIT 1",
        array(AUTOPREFIX_APP_UUID, $app_instance));
    $row = mysql_fetch_array($result);
    mysql_free_result($result);
    if ($row) {
        # Existing entry was found.  Return it.
        return $meta_prefix . $row['prefix'];
    }

    # No existing entry was found.  Create a blank entry, which we will
    # populate later.
    mysql_execute($db_conn,
        "INSERT INTO `$main_table`
         (uuid, instance, prefix) VALUES (NULL, NULL, NULL)")
        or die("Couldn't create autoprefix entry");
    $entry_id = mysql_insert_id($db_conn);

    # Generate a table prefix.  First, we try using the suggested prefix.  If
    # that fails (because a table with the given prefix already exists), then
    # we generate a different prefix and try again.
    for ($i = 0; $i < 100; $i++) {
        if ($i == 0) {
            $prefix = AUTOPREFIX_SUGGESTED_PREFIX;
        } else {
            $prefix = $gen_prefix . $entry_id . 'x' . $i . '_';
        }
        
        $prefix_in_use = false;

        # Check if a table with the given prefix already exists.
        $result = mysql_execute($db_conn, "SHOW TABLES LIKE ?", array($meta_prefix . $prefix . "%"))
            or die("Error listing tables");
        while($row = mysql_fetch_row($result)) {
            // Some characters (% and _) are wildcards for SHOW TABLES LIKE, so we
            // iterate through the list of returned rows and make sure we really have an
            // exact match.
            if (substr($row[0], 0, strlen($prefix) + strlen($meta_prefix)) === ($meta_prefix . $prefix)) {
                $prefix_in_use = true;
                break;
            }
        }
        mysql_free_result($result);

        if (!$prefix_in_use) {
            # Attempt to set the prefix to the suggested prefix.  This will fail (due
            # to the UNIQUE INDEX) if the prefix is already in use.
            $result = mysql_execute($db_conn,
                "UPDATE `$main_table` SET prefix = ? WHERE id = ?",
                array($prefix, $entry_id));
            if (!$result) {
                $prefix_in_use = true;
            }
        }

        if (!$prefix_in_use) {
            break;
        }
    }
    if ($prefix_in_use) {
        die("autoprefix: Could not automatically generate prefix after 100 tries (id=$entry_id)");
    }

    # Set the uuid and instance fields
    mysql_execute($db_conn,
        "UPDATE `$main_table` SET uuid = ?, instance = ? WHERE id = ?",
        array(AUTOPREFIX_APP_UUID, $app_instance, $entry_id))
        or die("Could not set autoprefix uuid and instance (id=$entry_id)");

    return $meta_prefix . $prefix;
}

# vim:set ts=4 sw=4 sts=4 expandtab: