Creating new tables

From Wikitech

Follow the steps below to create a new database table. If you want to make changes to the existing database schema, use this guide instead.

Preparation

  1. Create a Phabricator task to track the table creation.
  2. Design the table schema, following the database policy and database coding conventions.
  3. Create a Gerrit patch with the database JSON and generated SQL commands to create the new table and link it to the task.
  4. Get the patch reviewed (+1):
    1. Ask a developer knowledgeable in areas of database schema to review the patch (a list is coming, for now just ask around to find one).
    2. Tag the task with the #DBA tag for visibility. A DBA may provide feedback on the patch at this point.
  5. Request DBA signoff (requests are processed in order, with no specific SLA, if you are working against a deadline contact the DBA manager to negotiate):
    1. Add the following information to the task:
      • Should this table be replicated to wiki replicas (does it not contain private data)?
      • Will you be doing cross-joins with the wiki metadata?
      • Size of the table (number of rows expected).
      • Expected growth per year (number of rows).
      • Expected amount of queries, both writes and reads (per minute, per hour...per day, any of those are ok).
      • Examples of queries that will be using the table.
      • The release plan for the feature (are there specific wikis you'd like to test first etc).
    2. Move the task to the Triage column on the DBA workboard.
  6. If the table is for an extension: modify createExtensionTables.php to include extension and tables you need to use.
  7. If the table is for an extension and needs to be created on all new wikis (or all new Wikipedias/Wiktionaries/etc): modify addWiki.php to include the extension and tables you need.
  8. Once the above steps are done, proceed to deployment.

Deployment

  1. If you have production deployment permissions, proceed to the next step. If you don't have deployment permissions and this is a one off deployment (you don't regularly deploy to production), locate a person with deployment permissions and ask them for help. If you can't find any, creating a task tagged with wikimedia-maintenance-script-run and wikimedia-site-requests will likely attract someone who can help you.
  2. Make sure the patch which introduces the tables got merged, and confirm files were deployed to production
    1. Log on to a mwmaint host (mwmaint1002 or mwmaint2001)
    2. Check if the file is present in the location matching the following example:
      • If :
        • the sql file is in Extension:Foo in path sql/new_table.sql
        • the MW version deployed in production is 1.36-wmf.21
      • The location will be: /srv/mediawiki/php-1.36-wmf.21/extensions/Foo/sql/new_table.sql
  3. Schedule table creation at a time that doesn't conflict with the deployment schedule
  4. Create the table:
    1. Log on to a mwmaint host (mwmaint1002 or mwmaint2001, depending on the active cluster)
    2. Run one of the following:
      1. To add a core table to a single wiki:
        mwscript sql.php --wiki=wikidbname maintenance/patches/patchname.sql
      2. To add a core table to all wikis:
        foreachwiki sql.php maintenance/patches/patchname.sql
      3. To create tables required when adding an extension (assuming you already deployed the extension code and that the table is included in createExtensionTables.php script):
        mwscript extensions/WikimediaMaintenance/createExtensionTables.php --wiki=wikidbname extensionname

Future-proofing

  1. If your new table is going to be needed in all wikis, add it to addWiki.php in WikimediaMaintenance extension so that new wikis are created with your table



Retrieved from "https://wikitech.wikimedia.org/w/index.php?title=Creating_new_tables&oldid=2003308"