百度 随着越来越多的海外少年儿童加入到学汉语的大潮中，海外汉语学习者的低龄化渐成趋势。
The inconsistencies between the MySQL and PostgreSQL schemas cause a constant maintenance burden. PostgreSQL support is frequently broken in master. The PostgreSQL schema is certainly better than the MySQL one, if analysed in isolation, but the advantages to PostgreSQL users of having an improved schema are more than outweighed by the disadvantages of inconsistency with the remainder of the installed MediaWiki user base.
We should aim to have a schema which can be automatically generated from maintenance/tables.sql, which is already shared between MySQL and SQLite.
Proposal:
- Require PostgreSQL 9.2 or later. Remove the many special cases from DatabasePostgres supporting earlier versions. It was released in 2012 and is the oldest version which still receives bugfixes. PostgreSQL 9.1 was EOL in September 2016.
- Rename tables mwuser to user, and pagecontent to text. Table names are always quoted, so it doesn't matter if they are keywords. A table prefix can be used if desired to avoid having table names that are keywords.
- Remove foreign key constraints. These were a nice idea, but have flow-on effects, causing inconsistencies and run-time errors.
- Remove the dummy anonymous user
- Remove the page_deleted trigger
- Change old_text to BYTEA, for T26607 etc.
- Change rc_ip to TEXT
- Remove search update triggers, use SearchPostgres::update() instead.
- Remove the profiling table.
- Map AUTO_INCREMENT in the shared schema to PostgreSQL's SERIAL. This is essentially an abbreviated syntax for what the PostgreSQL schema does already. Rename any existing sequences which do not fit the resulting naming scheme.
- Re-implement DatabasePostgres::insertId() using SELECT lastval(), so that it does not require nextSequenceValue() to be called first.
- Change page_random to FLOAT(53). Also change it in the shared schema. This is an SQL-standard synonym for the existing column type on MySQL, and is also understood by PostgreSQL to mean the same thing. It is accepted on SQLite (which stores numbers as strings).
Implementation:
- Require PostgreSQL 9.2 or later.
- Rename tables mwuser to user, and pagecontent to text.
- Remove foreign key constraints. These were a nice idea, but have flow-on effects, causing inconsistencies and run-time errors.
- Remove the dummy anonymous user
- Remove the page_deleted trigger
- Change old_text to BYTEA, for T26607 etc.
- Change rc_ip to TEXT
- Remove search update triggers, use SearchPostgres::update() instead.
- Remove the profiling table (T231366).
- Map AUTO_INCREMENT in the shared schema to PostgreSQL's SERIAL.
- Re-implement DatabasePostgres::insertId() using SELECT lastval(), so that it does not require nextSequenceValue() to be called first. (Done in Ib308190c52673a)
- Change page_random to FLOAT(53). Also change it in the shared schema.