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
This solution is relatively easy to implement, but does not solve Problem 2.
 * Rejected

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

Brings the file tables in line with the and  table. This will create proper semantic separation between file entities and the versions of their (mutable) content.

Effective change Migration strategy Behaviour
 * Add,  ,  , and   fields.
 * img_id: Primary key
 * img_latest: oi_id of the current version of an image (similar to )
 * oi_id: Primary key
 * 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".
 * Create new fields and populate them with a script.
 * (Requires freeze?) Backfill file_revision with the "current" image data.
 * 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).