Database testing

Creating a test plan for databases

What do we need to test, in terms of how MediaWiki deals with databases?

Permissions. One problem we ran into with PostgreSQL: they had their db set up differently from how we were doing installation. So we should check anything in any database where permissions are different from how they are in MySQL/MariaDB.
- For example, you can set up a user in PostgreSQL that's not a superuser, but who can create users & databases. We didn't test for that case.
- Other RDBMSes, such as DB2, might also have finer-grained permissions than MySQL does. This includes:
  - ownership of tables
  - ability of user to update, delete data from tables
- TO TEST: make sure page updates work.

Searching. Searching in MySQL/MariaDB requires the 'searchindex' table to be MyISAM or InnoDB. And other similar differences in other DBs like PostgreSQL?
- TO TEST: Searches return results for existing pages w/o Lucene extension

Schema setup
- check that we have field sizes set up correctly
- whatever maximum HTML field is --- can it be stored correctly in database?
- TO TEST: Create a page, edit the page
- TO TEST: Create a user, Change the password

Schema changes. Our schema changes are slightly different for PostgreSQL and other DBs.
- Example: SQLite - we munge the MySQL schema & use that for SQLite; it would be good to make sure no further munging is necessary.
- make sure schema updates correctly
- make sure the MW schema doesn't end up in the postgres or template1 database in PostgreSQL
- SQLite problem with conversion noted in CR
- TO TEST: Install an older version and update.
- TO TEST: Install an older version of an extension and update it.
- IDEAL WORLD: Abstract the schema, so all DBMSs are kept up to date automatically and don't require as many implementation-specific patches. Initial proof of concept for MySQL stashed in abstract-schema branch.

Quoting and identifiers. How do we quote/identify table or column names in a query? MySQL is the only DB that ... identifiers primarily with backticks. We should test against all RDBMSes. probably an easy place to make a difference.
- do we have custom table names?
- check for any tables that extensions create. does it work in all DBs?
- TO TEST:

Weird page names. Long page titles, with Unicode characters and spaces.
- TO TEST: Create, read, edit, and delete pages with long and strange titles.

Transactions. We use them extensively with PostgreSQL, maybe other DBs as well. Make sure that, if they are nested, they are nested correctly.
- TO TEST: Pg will talk about errors if you look at its logs. May need to tweak how logging works.

Import. Fix any problems that show up when you import a full Wikipedia dump.
- TO TEST: Import a full Wikipedia dump and ... look for shell & errors? try to do basic operations?

Dump. Test dump creation.
- ensure dump export is clean no matter the database
- example problem: PHP characters at the end of the dump.
- TO TEST: Run a full export dump off Wikipedia, look for shell & errors, diff it from the dump we should have?

Interface coverage. Broadly test the Web UI and API.
- example problem: https://bugzilla.wikimedia.org/show_bug.cgi?id=29392
- TO TEST: (a) Run a wide spectrum of page and API requests from an automated test script. Look for valid responses. Look for fault indicators such as HTTP status codes in the range 400-599 or API error elements. (b) Test/fix the test script. Extend it to cover any subsequent interface bugs that are reported in Bugzilla and thereby proven to be uncovered. For each such extension, reference the URL of the bug it covers.

MySQL (InnoDB, with way lower priority on MyISAM)
SQLite
Already tests schema conversion from MySQL and upgrades from previous versions as part of non-destructive unit tests.
PostgreSQL