Proper way to create new SQL table in a MediaWiki Extension

Question

I am creating a mediawiki extension that needs is own SQL table. What's the proper way to create it in the extension, and do I need to create separate SQL code for each database I want to support, the way the OATHAuth extension does? That doesn't seem like the right approach.

(OATHAuth hooks onLoadExtensionSchemaUpdates)

Answer 1

For a schema change for an extension, the LoadExtensionSchemaUpdates hook page is the right place to look.

The LoadExtensionSchemaUpdates hook is

fired when MediaWiki is updated (php maintenance/update.php) to allow extensions to update the database

If you take a look at the ArticleFeedbackv5.sql file, that's basically what the sql file should look like

CREATE TABLE IF NOT EXISTS /*_*/aft_feedback (
      -- id is no auto-increment, but a in PHP generated unique value
      aft_id binary(32) NOT NULL PRIMARY KEY,
      aft_page integer unsigned NOT NULL,
    ...
    ) /*$wgDBTableOptions*/;

    -- sort indexes (central feedback page; lots of data - more details indexes for most popular actions)
    CREATE INDEX /*i*/relevance ON /*_*/aft_feedback (aft_relevance_score, aft_id, aft_has_comment, aft_oversight, aft_archive, aft_hide);
    CREATE INDEX /*i*/age ON /*_*/aft_feedback (aft_timestamp, aft_id, aft_has_comment, aft_oversight, aft_archive, aft_hide);
    CREATE INDEX /*i*/helpful ON /*_*/aft_feedback (aft_net_helpful, aft_id, aft_has_comment, aft_oversight, aft_archive, aft_hide);

    -- page-specific
    CREATE INDEX /*i*/relevance_page ON /*_*/aft_feedback (aft_page, aft_relevance_score);
    CREATE INDEX /*i*/age_page ON /*_*/aft_feedback (aft_page, aft_timestamp);
    CREATE INDEX /*i*/helpful_page ON /*_*/aft_feedback (aft_page, aft_net_helpful);

    -- index for archive-job
    CREATE INDEX /*i*/archive_queue ON /*_*/aft_feedback (aft_archive, aft_archive_date);

    -- index for mycontribs data
    CREATE INDEX /*i*/contribs ON /*_*/aft_feedback (aft_user, aft_timestamp);
    CREATE INDEX /*i*/contribs_anon ON /*_*/aft_feedback (aft_user, aft_user_text, aft_timestamp);

And then you reference this file in the hook in your hook file

public static function loadExtensionSchemaUpdates( $updater = null ) {
            // that's for adding a table
            $updater->addExtensionTable(
                'aft_feedback',
                dirname( __FILE__ ) . '/sql/ArticleFeedbackv5.sql' //that's the previous sql file
            );
            ...
}

Don't forget to add the hook in the hook section in your extension.json file. The table will be created when you run the update.php file.

As for your question regarding creating a file for each database engine, we can't use the same file for each of them as there might be some differences in the queries. See what Tgr said.

If you haven't yet, take a look at the hook file.

protected function execute() {
        switch ( $this->updater->getDB()->getType() ) {
            case 'mysql':
            case 'sqlite':
                $this->updater->addExtensionTable( 'oathauth_users', "{$this->base}/sql/mysql/tables.sql" );
                $this->updater->addExtensionUpdate( [ [ $this, 'schemaUpdateOldUsersFromInstaller' ] ] );
                $this->updater->dropExtensionField(
                    'oathauth_users',
                    'secret_reset',
                    "{$this->base}/sql/mysql/patch-remove_reset.sql"
                );
                $this->updater->addExtensionField(
                    'oathauth_users',
                    'module',
                    "{$this->base}/sql/mysql/patch-add_generic_fields.sql"
                );
                $this->updater->addExtensionUpdate(
                    [ [ __CLASS__, 'schemaUpdateSubstituteForGenericFields' ] ]
                );
                $this->updater->dropExtensionField(
                    'oathauth_users',
                    'secret',
                    "{$this->base}/sql/mysql/patch-remove_module_specific_fields.sql"
                );
                /*$this->updaterAddExtensionUpdate(
                    [ [ __CLASS__, 'schemaUpdateTOTPToMultipleKeys' ] ]
                );*/
                break;
            case 'oracle':
                $this->updater->addExtensionTable( 'oathauth_users', "{$this->base}/sql/oracle/tables.sql" );
                break;
            case 'postgres':
                $this->updater->addExtensionTable( 'oathauth_users', "{$this->base}/sql/postgres/tables.sql" );
                break;
        }
        return true;
    }