Automate database table creation from within CakePHP framework

Question

I'm trying to write a webapp with CakePHP, and like most webapps I would like to create an installer that detects whether the database has been initialized and, if not, executes the installation process.

This process will be entirely automated (it assumes the database itself already exists, and that it is granted full administrative access through the anonymous account with no password...this is for a sandbox environment, so no worries about security), so it needs to be able to detect (regardless of the request!) if the database tables have been created and initialized, and if not, to perform that initialization transparently and then still serve up the user's original request.

I considered writing a sort of Bootstrap controller through which all requests are routed, and a single SQL query is run to determine if the database tables exist, but this seemed cumbersome (and the controller requires a corresponding model, which needn't be the case here). The other possibility is was to override AppModel and place within it the same test, but I was unsure how to do this, as there isn't any documentation along these lines.

Thanks in advance!

tl;dr version: What is the CakePHP equivalent (or how can the equivalent be written for CakePHP) of a J2EE servlet's "init()" method?

Answer 1

Do you really want to make every request check that the database exists? This seems terribly wasteful.

The first time it checks it will set up the database, and then for every one of the next million requests it will check again even though the database has of course by this time been set up!

It's a better idea to require that whomever installs your application do some setup. You should provide a separate script that is not run during every request, but is run manually by the person during installation.

---

Re your comment about the best way to do this with CakePHP, have you looked at Cake's support for database schema migrations?

http://book.cakephp.org/view/734/Schema-management-and-migrations

Answer 2

Something like this might do what you're looking for, but does require an extra query per model.

<?php
class AppModel extends Model {
    var $create_query = false;

    function __construct($id = false, $table = null, $ds = null) {
        parent::__construct($id, $table, $ds);

        if (!empty($this->create_query)) {
            $this->query($this->create_query);
        }
    }
}
?>

Then you would just need to add var $create_query = 'CREATE TABLE IF NOT EXISTS ...;` in your models. The MySQL CREATE TABLE documentation has more information on IF NOT EXISTS.

However, I'm not certain that this isn't trying to call query() too early. Or before the check to see if the table already exists, which it would have to be. Otherwise, CakePHP would error out instead of creating your table. There isn't much documentation on the subject and your best bet for more information going to be to take a look at cake/libs/model/model.php directly.

Update: The code above would not work as written.

After looking into the Model class a little deeper, Model::__construct calls Model::setSource(). Model::setSource() checks to see if the table exists and throws an error if it doesn't. To use this method, that's where you'd have to override and insert the query. The code below may need to differ, depending on which version of CakePHP you're using.

<?php
function setSource($tableName) {
    // From Model::setSource().  I believe this is needed to make query() work.
    $this->setDataSource($this->useDbConfig);

    // Create our table if we have a create query
    if (!empty($this->create_query)) {
        $this->query($this->create_query);
    }

    // Now call the parent
    parent::setSource($tableName);
}
?>

Answer 3

Check out the AppError class - may be there is a missing database table error that your can override and run your create database table SQL from there?

Answer 4

I thought about doing this at one point in time, and came up with a good way to do it.

First off, a note: I believe your app needs write access to app/config/database.php, so you can write the correct information. I have a workaround, but let me explain the method by which the installer works.

PHP Scripts can rewrite themselves (or delete themselves) on the fly. Since the script is loaded into memory and THEN the bytecode is executed, it isn't dependent upon the file once it is done working. So you could theoretically have something that overrides the AppError class to run a specific controller (say your SetupWizardController) with all the regular, CakePHP stuff loaded. Once you go through the entire wizard, write the database.php file/setup anything else needed, overwrite the AppError and AppController files (I can see you needing the AppController to work differently if the app hasn't been installed in certain cases) with ones that are stored somewhere like in app/vendors/myapp. The script has finished executing and now your checks will not occur on subsequent runs.

This would get rid of the problem Bill Karwin brought up, utilize AppError, and make an installer.

The other way to do it is to include the idea of Environments in your application. Say you store your environments in app/config/environment.php. This file would be given write access to (and that access would be removed at the end of setup), or could be overwritten. Regardless, it would exist whenever someone attempts to deploy your application, and database.php automatically imports the databases from the environment.php file. The constructor of the database.php class would then overwrite it's $default database with the one specified in environment.php. The $default in database.php would reference a NoDB Datasource, which you can check in the AppController (I think you would be able to instantiate a dummy model and check it's datasource), and then push up the required Installer. Obviously you'd overwrite the AppController after this as well.