Requests for comment/image and oldimage tables

There's no auto-incrementing primary key for, and no primary key at all for. This should be changed so that we can uniquely and  performantly identify files and their revisions.

Problems

 * 1) File revisions should have better unique identifiers than "current file title + timestamp". (Subject to race conditions, hard to index, hard to query.)
 * 2) Uploading file revisions must not involve rows moving across tables, or rows being replaced.

Use cases

 * End-user improvements
 * T139294: Persistent media links for file versions
 * Ability to potentially create secondary database tables that associate data with files or their revisions (e.g. T33257: File properties storage and License integration MediaWiki). This is not feasible in the current schema.
 * API Improvements
 * prop=imageinfo - Use file revision ID instead of fragile timestamp as identifier.
 * Technical debt
 * LocalFile::recordUpload2 - Eliminate timestamp kludge.

1. Add primary keys
Rejected. This solution would be relatively easy to implement, but does not solve Problem 2.

Advantages
Eliminates the legacy handling of needing to interact with two tables for most operations (image and oldimage).

Brings the file tables in line with the and  table.

This will create proper semantic separation between file entities and the representation of the versions of their (mutable) content.

Effective change

 * Add,  ,  , and   fields.
 * img_id: Primary key of.
 * img_latest: oi_id of the current version of an image (similar to )
 * oi_id: Primary key of.
 * oi_img: img_id of the image this version corresponds to (similar to )
 * (Optional) Rename oldimage to "imagerevision". Or, rename image to "file" and oldimage to "filerevision".
 * (Optional) Drop most image table columns, such as . These would be queried from filerevision instead (after resolving ). This would avoid race conditions by allowing a request to resolve the pointer once and make sure all other queries will be of the same version, and makes it more attractive query slaves instead of making master queries.

Migration strategy
There's multiple ways to migrate to this schema. A few different ideas:
 * Rename oldimage to filerevision, insert all rows from "image" into filerevision.
 * Would require a depool dance since MediaWiki mustn't see the "new" rows until after the migration is done and MediaWiki updated.
 * "image" is considerably larger than "oldimage" on Commons. Going in the other direction may be better. Has the same visibility problem though. And we'd need to "re-create" the new version of the image/file table.
 * (Wikimedia specific) Run whatever alter-table script on unused servers. E.g. Fail-over to codfw for commonswiki DB while migrating eqiad.

Behaviour

 * Metadata no longer lives spread over two tables.
 * Creating a file revision involves inserting a new file_revision row and updating the file.img_latest pointer.

3. Replace with MCR
Multi-content revisions as per T107595.

We could decide to stop using the image/file tables entirely, in favour of storing the information in the wiki page/revision tables. We'd obviously not store the file binary in the text table. But we could make file-backend references from there, similar to what we do with ExternalStorage already.

We could still keep a simplified image (or "file") table as secondary data (similar to "categories").

Out of scope
The following problems are related and may be dealt with in future RFCs. Some of these will get easier after this RFC, but they are considered orthogonal and out of scope.
 * Files should not be identified by their caption, but be given a language-neutral identifier.
 * Use cases:
 * Language-neutral file identifiers (see User:NeilK/Multimedia2011/Titles).
 * Allow embedding a file in a wiki page without depending on the current file name (e.g. references should not break after "renaming" or subject to redirects).
 * Remove need for globally unique "file titles" during uploads.
 * Side effects / new problems it would cause:
 * If file names are reduced to meta data and file-table IDs become the canonical ID for the mutable file (e.g. for use in wikitext) - then we'd need to change file description pages to use that ID in their title (as otherwise they'd still need to be unique since two pages can't have the same name in the File wikipage namespace). Using file IDs publicly would also make ForeignFileRepo considerably more difficult to do since they would very often clash with the local wiki files (whereas currently clashes usually don't happen unintentionally). We could consider not supporting local shadowing of files and embedding the filerepo ID (e.g.  or some such).