Jump to content

User:LSobanski (WMF)/Creating new tables

From Wikitech
This is a draft, use at your own peril


This is an attempt at rewriting Creating_new_tables in a way that is easier to follow. Comments and suggestions are welcome.

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 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 contain private data)?
      • 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. Once the two reviews are done, proceed to deployment.

Deployment

  1. Merge the patch
  2. 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)
    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):
        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=User:LSobanski_(WMF)/Creating_new_tables&oldid=2233898"