db_update.php Outputter Supports All 3 DBMSs

[Fountain of Apples]

I personally LOVE the online SQL parser that outputs a db_update.php file. However, I find it kind of weird that it can only output an installer for one kind of DBMS. Would it be possible to have it output for all three? You could set up a switch on the $dbms var (which is set in config.php) and then have the appropriately parsed SQL assigned based on the case value of $dbms; the rest of the script could be as-is. This would allow for making one db_update.php for all three DBMS types.
For example, I adapted the db_update.php code for one of my MODs to do this, and this is how the SQL is assigned:

Code: Select all

//
// Build Array of SQL Statements.
//
$sql = array();
switch ($dbms)
{
    case 'mysql'&#58
    case 'mysql4'&#58
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_check (
        mod_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        mod_name VARCHAR(250) NOT NULL DEFAULT \'\',
        mod_current_version TEXT NOT NULL DEFAULT \'\',
        mod_new_version TEXT NOT NULL DEFAULT \'\',
        mod_secondary_version TEXT NOT NULL DEFAULT \'\',
        mod_domain_loc TEXT NOT NULL,
        mod_file_name TEXT NOT NULL,
        mod_file_loc TEXT NOT NULL,
        mod_topic_loc TEXT NOT NULL DEFAULT \'\',
        mod_download_loc TEXT NOT NULL DEFAULT \'\',
        mod_dev_status TEXT NOT NULL DEFAULT \'\',
        mod_error VARCHAR(250) DEFAULT \'\',
        mod_status TINYINT(4) NOT NULL DEFAULT 1,
        mod_time_stamp INTEGER(11) NOT NULL DEFAULT 0,
        mod_author_notes TEXT NOT NULL DEFAULT \'\',
        PRIMARY KEY (mod_id)
    )';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_check VALUES(1,\'phpBB\',\'\',\'\',\'\',\'www.phpbb.com\',\'20x.txt\',\'updatecheck\',\'http&#58//www.phpbb.com\',\'\',\'\',\'\',1,0,\'\')';
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_config (
        config_name VARCHAR(255) NOT NULL,
        config_value TEXT NOT NULL DEFAULT \'\',
        PRIMARY KEY (config_name)
    )';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'check_time\',\'86400\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'background_check\',1)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'show_admin_index\',1)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_email\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'email_address\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_pm\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'pm_id\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_post\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'post_forum\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'post_contents\',\'\')';
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_log (
        log_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        log_timestamp INTEGER(11) NOT NULL DEFAULT 0,
        mod_name VARCHAR(250) NOT NULL DEFAULT \'\',
        log_action TEXT NOT NULL DEFAULT \'\',
        PRIMARY KEY (log_id)
    )';
    break;
    case 'mssql'&#58
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_check (
        mod_id INTEGER NOT NULL IDENTITY(1, 1),
        mod_name VARCHAR(250) NOT NULL DEFAULT (\'\'),
        mod_current_version TEXT NOT NULL DEFAULT (\'\'),
        mod_new_version TEXT NOT NULL DEFAULT (\'\'),
        mod_secondary_version TEXT NOT NULL DEFAULT (\'\'),
        mod_domain_loc TEXT NOT NULL,
        mod_file_name TEXT NOT NULL,
        mod_file_loc TEXT NOT NULL,
        mod_topic_loc TEXT NOT NULL DEFAULT (\'\'),
        mod_download_loc TEXT NOT NULL DEFAULT (\'\'),
        mod_dev_status TEXT NOT NULL DEFAULT (\'\'),
        mod_error VARCHAR(250) NULL DEFAULT (\'\'),
        mod_status SMALLINT NOT NULL DEFAULT (1),
        mod_time_stamp INTEGER NOT NULL DEFAULT (0),
        mod_author_notes TEXT NOT NULL DEFAULT (\'\'),
        CONSTRAINT ' . $table_prefix . 'version_check_pk PRIMARY KEY (mod_id) ON [PRIMARY],
        CHECK (mod_id>=0)
    )  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_check VALUES(1,\'phpBB\',\'\',\'\',\'\',\'www.phpbb.com\',\'20x.txt\',\'updatecheck\',\'http&#58//www.phpbb.com\',\'\',\'\',\'\',1,0,\'\')';
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_config (
        config_name VARCHAR(255) NOT NULL,
        config_value TEXT NOT NULL DEFAULT (\'\'),
        CONSTRAINT ' . $table_prefix . 'version_config_pk PRIMARY KEY (config_name) ON [PRIMARY]
    )  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'check_time\',\'86400\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'background_check\',1)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'show_admin_index\',1)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_email\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'email_address\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_pm\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'pm_id\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_post\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'post_forum\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'post_contents\',\'\')';
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_log (
        log_id INTEGER NOT NULL IDENTITY(1, 1),
        log_timestamp INTEGER NOT NULL DEFAULT (0),
        mod_name VARCHAR(250) NOT NULL DEFAULT (\'\'),
        log_action TEXT NOT NULL DEFAULT (\'\'),
        CONSTRAINT ' . $table_prefix . 'version_log_pk PRIMARY KEY (log_id) ON [PRIMARY],
        CHECK (log_id>=0)
    )  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]';
    break;
    case 'postgres'&#58
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_check (
        mod_id SERIAL,
        mod_name VARCHAR(250) NOT NULL DEFAULT \'\',
        mod_current_version TEXT NOT NULL DEFAULT \'\',
        mod_new_version TEXT NOT NULL DEFAULT \'\',
        mod_secondary_version TEXT NOT NULL DEFAULT \'\',
        mod_domain_loc TEXT NOT NULL,
        mod_file_name TEXT NOT NULL,
        mod_file_loc TEXT NOT NULL,
        mod_topic_loc TEXT NOT NULL DEFAULT \'\',
        mod_download_loc TEXT NOT NULL DEFAULT \'\',
        mod_dev_status TEXT NOT NULL DEFAULT \'\',
        mod_error VARCHAR(250) DEFAULT \'\',
        mod_status SMALLINT NOT NULL DEFAULT 1,
        mod_time_stamp INTEGER NOT NULL DEFAULT 0,
        mod_author_notes TEXT NOT NULL DEFAULT \'\',
        CONSTRAINT ' . $table_prefix . 'version_check_pk PRIMARY KEY (mod_id),
        CHECK (mod_id>=0)
    )';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_check VALUES(1,\'phpBB\',\'\',\'\',\'\',\'www.phpbb.com\',\'20x.txt\',\'updatecheck\',\'http&#58//www.phpbb.com\',\'\',\'\',\'\',1,0,\'\')';
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_config (
        config_name VARCHAR(255) NOT NULL,
        config_value TEXT NOT NULL DEFAULT \'\',
        CONSTRAINT ' . $table_prefix . 'version_config_pk PRIMARY KEY (config_name)
    )';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'check_time\',\'86400\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'background_check\',1)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'show_admin_index\',1)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_email\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'email_address\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_pm\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'pm_id\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'update_post\',0)';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'post_forum\',\'\')';
    $sql[] = 'INSERT INTO ' . $table_prefix . 'version_config VALUES(\'post_contents\',\'\')';
    $sql[] = 'CREATE TABLE ' . $table_prefix . 'version_log (
        log_id SERIAL,
        log_timestamp INTEGER NOT NULL DEFAULT 0,
        mod_name VARCHAR(250) NOT NULL DEFAULT \'\',
        log_action TEXT NOT NULL DEFAULT \'\',
        CONSTRAINT ' . $table_prefix . 'version_log_pk PRIMARY KEY (log_id),
        CHECK (log_id>=0)
    )';
    break;
    default&#58
    message_die(GENERAL_ERROR, 'Sorry, but AVC is not compatible with ' . $dbms . ', it is only compatible with MySQL, PostgreSQL, and Microsoft SQL.');
}

The rest of the script is as it was originally.
--Fountain of Apples

[3Di]

I personally think it is a good idea. Really.

Obviously somebody else (me? ) could ask: why not to let it to output a DBAL one?

Greets

[TerraFrost]

While DBAL converts LIMITs (which only work on MySQL) to what they would need to be for each DBMS, it doesn't do anything like that for CREATE TABLEs, ALTER TABLEs, and stuff like that. Generally, this isn't a problem, since CREATE TABLEs are only usually done when a MOD is being installed. For EasyMOD (and the db_update.php thing), however, it kinda is. The *.php files for the varrious DBMSs could, I suppose, be modified, but requiring someone install a MOD just to install another one doesn't seem like an uber good idea. Also, it'd mean extra processing that has to be done for something which, like I said, is very unlikely to occur, anyway.

Supporting all three DBMSs seems to me to be a good idea, though. Haven't looked at db_update.php recently, though, so I don't know how feasable it is...

[Fountain of Apples]

I missed that.