aboutsummaryrefslogtreecommitdiffstats
path: root/maintenance/postgres
Commit message (Collapse)AuthorAgeFilesLines
...
| * Start generateSchemaChangeSql.phpAmir Sarabadani2020-11-011-9/+10
| | | | | | | | | | | | | | | | Creating schema changes from abstract schema and even abstracting a schema change. Bug: T230420 Change-Id: If626e866642af820dd70c5f9b0fe7c6a951e0a25
* | Migrate revision_actor_temp to abstract schemaAmmar Abdulhamid2020-11-102-11/+19
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | For Postgres: - Drop foreign key from 'revactor_page' - Change datatype of 'revactor_actor' to BigInt as in MySQL/SQLite - Make 'revactor_page' non-nullable as in MySQL/SQLite For MySQL/SQLite: - Drop empty string default constraint from 'revactor_timestamp' as this is not allowed in the corresponding PG's TIMESTAMPTZ field Bug: T230428 Bug: T164898 Change-Id: I8c5c4b338a3000b1e4c2ab82fdae4a9819925868
* | Migrate revision_comment_temp to abstract schemaAmmar Abdulhamid2020-11-102-7/+11
| | | | | | | | | | | | | | | | | | For Postgres: - Change 'revcomment_comment_id' to BigInt as it's in MySQL/SQLite Bug: T230428 Bug: T164898 Change-Id: I4b5af210b63d932e2eb0120075a5fa5250924ace
* | Migrate ip_changes to abstract schemaAmir Sarabadani2020-11-052-10/+12
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | In order to make Postgres work: - Dropped the autoincrement sequence, this is the PK without autoincrement - Change ipc_hex from BYTEA to TEXT - Set default for ipc_rev_id MySQL/SQLite: -Drop default for ipc_rev_timestamp Bug: T230428 Bug: T164898 Change-Id: I7f85d65f6ee3ac8b1d28e33095f37dce81e1727e
* | Migrate externallinks to abstract schemaAmmar Abdulhamid2020-11-052-13/+20
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Postgres changes: - Drop foreign key from `el_from` - Change 'el_index_60 type from BYTEA to TEXT - Set default for `el_from` to sync with MySQL - Also renamed these indexes to sync with MySQL - 'externallinks_from_to' -> 'el_from' - 'externallinks_index' -> 'el_index' - Add index 'el_to' on 'el_to' and 'el_from' columns. This completes the PG indexes to 5 to match up MySQL indexes Bug: T230428 Bug: T164898 Change-Id: I1ab9bde19e456256db99d1244130e8ee11f9b6c8
* | Migrate protected_titles to abstract schemaAmmar Abdulhamid2020-11-022-13/+14
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | For Postgres: - Drop foreign key from pt_title - Change pt_user to non-nullable to sync with MySQL - Change pt_expiry to non-nullable to sync with MySQL - Modify pt_reason_id to use BIGINT to sync with MySQL - Drop default from pt_create_perm field since MySQL and SQLite don't have it and the field is not nullable. - For MySQL/SQLite: - Modify pt_title to use varbinary - Drop DEFAULT constraint from pt_expiry (Postgres already does not have it) Bug: T230428 Bug: T164898 Change-Id: Iff193754260046222ba9b7e704c46e27f21b1a6b
* | Migrate interwiki table to abstract schemaAmir Sarabadani2020-11-022-14/+11
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | For MySQL, turning bool to tinyint(1) but doesn't matter because bool is an alias of tinyint(1) For Postgres: - Changing type of iw_prefix and iw_wikiid to varchar(32) and varchar(64) respectively to make it in sync with Mysql. - Dropping add_interwiki function, it doesn't serve any purpose (search didn't bring anything) Bug: T230428 Bug: T164898 Change-Id: Id652cb544ac1b216f2fad1a8a959739ead8a92cd
* | Migrate new_talk to abstract schemaAmmar Abdulhamid2020-10-262-8/+10
| | | | | | | | | | | | | | | | | | | | | | | | For MySQL - Modify user_last_timestamp from VARBINARY to BINARY For Postgres - Drop foreign key on user_id - Rename indexes to match MySQL convention Bug: T230428 Change-Id: Idc3ded012890d4ace805a646d75cd805b49b250c
* | Merge "Drop all "DROP SEQUENCE" in Postgres"jenkins-bot2020-10-231-26/+0
|\ \
| * | Drop all "DROP SEQUENCE" in PostgresAmir Sarabadani2020-10-191-26/+0
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | tables.sql never was meant as an idempotent script or something that can be ran on an existing database or schema. This serves no purposes and can cause issues if tables-generated.sql gets to run first (by dropping sequences that just got created), currently this doesn't happen and installer, first installs manual tables but it can change in the future. Change-Id: I299a0ae1e49f415910892981c07cfd582d198598
* | | Migrate sites table to abstract schemaAmir Sarabadani2020-10-222-25/+32
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | For MySQL/Sqlite, using mwtinyint instead of bool, in reality it doesn't matter since bool is an alias of tinyint(1) in MySQL. In Postgres: - Changing type of site_domain from TEXT to VARCHAR to make it in sync with MySQL/Sqlite - Renaming all indexes from site_* to sites_* to be in sync with MySQL/Sqlite Bug: T230428 Bug: T164898 Change-Id: Id73bb372807730446901d772fcdbd2f333b397ba
* | | Migrate watchlist table to Abstract schemaAmmar Abdulhamid2020-10-222-15/+18
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Postgres: - Set empty string as default for wl_title to match MySQL - Drop foreign key from wl_user - Change wl_namespace datatype from SMALLINT to INT to match MySQL/SQLite - Drop PG-specific indexes and sync them with MySQL Bug: T230428 Bug: T164898 Change-Id: Ie996b81ca59069443976b90d7bcdce29997d768a
* | | Migrate querycache_info to abstract schemaAmmar Abdulhamid2020-10-203-5/+10
| |/ |/| | | | | | | | | | | | | | | | | | | | | | | | | | | Additional changes for Postgres: - Set empty string as default for qci_type - Set PG-equivalent of the given MySql/Sqlite timestamp as default timestamp for qci_timestamp - Make qci_type non nullable - Make qci_timestamp non nullable - Drop UNIQUE constraint on qci_type Bug: T230428 Bug: T164898 Depends-On: If344395615087c360597a5b3d66ea03e930b7d9b Change-Id: I741d2d079696d4b4eba09945341054d2a145bddc
* | Migrate user_groups to abstract schemaAmir Sarabadani2020-10-192-8/+12
| | | | | | | | | | | | | | | | | | | | | | | | For Postgres: - Dropping foreign key on ug_user - Setting default on ug_user and ug_group - Renaming indexes so they would be the same as MySQL/Sqlite Bug: T42626 Bug: T164898 Bug: T230428 Change-Id: If507d8b8f363016760fe34aac2a0e0a2d99b66aa
* | Migrate page_restrictions to abstract schemaAmir Sarabadani2020-10-192-13/+20
|/ | | | | | | | | | | | | | | | | | | Sqlite doesn't need any change as binary/varbinary both turn into BLOB. For Postgres: - Dropping foreign key on pr_page, as all FKs should be dropped (See T164898) - Add all of indexes, it seems PG was missing all four indexes of the MySQL version and didn't have any - Changing PK from (pr_page,pr_type) to (pr_id). - Had to add changePrimaryKey function to handle such cases, similar to checkIndex - Set pr_page to not nullable Bug: T42626 Bug: T164898 Bug: T230428 Change-Id: Ied11f9c2ee633bf5ae325a6c2ea163dcc8d8d3c5
* Migrate querycachetwo table to abstract schemaAmir Sarabadani2020-09-262-12/+19
| | | | | | | | | | | | | Yes, name of that table is really querycachetwo. For migrating MySQL/Sqlite, changed data type of qcc_title and qcc_titletwo to varbinary. For migrating Postgres, just renaming indexes to stay in sync with MySQL/Sqlite was enough. Bug: T230428 Change-Id: I43952836ce184419622a6f851cb5e9319172d656
* Migrate querycache to abstract schemaAmir Sarabadani2020-09-262-8/+9
| | | | | | | | | | | | | | | | Turning qc_title to varbinary in MySQL to make it consistent with the rest of fields. For Postgres: - Setting 0 as default for qc_value and qc_namespace - Changing data type of qc_namespace from SMALLINT to INT to make it the same with MySQL/Sqlite - Renaming index to make it the same with other schemas - Setting empty string as default of qc_title Bug: T230428 Bug: T164898 Change-Id: I81527121609014d29182ae735a2c1f532d7d4d2a
* Migrate ipblocks_restrictions to abstract schemaAmir Sarabadani2020-09-222-9/+10
| | | | | | | | Also dropping the fkey on ir_ipb_id in Postgres as approved by RFC T164898 Bug: T230428 Bug: T164898 Change-Id: Iaddec3b9992eee39fe82a45d5bda59bd51cde510
* Migrate change_tag_def table to abstract schemaAmir Sarabadani2020-09-172-13/+15
| | | | | | | | | | | | | Also fixing one schema data type drift between MySQL and Postgres and fixing the problem with booleans, currently DBAL turns "boolean" to TINYINT(1) in MySQL because it doesn't support it but it leave it like that in Postgres which in turn breaks it because mediawiki currently doesn't cast values properly, so for now just turning them to SMALLINT which is the status quo (See T257755#6335566) Bug: T230428 Bug: T164898 Change-Id: Ia495a451d51722496942b720a32393f7a6728dee
* Migrate watchlist_expiry to abstract schemaAmir Sarabadani2020-09-172-8/+9
| | | | | | | | | | | | This takes advantage of custom data type registered in the previous commit. Also fixing Postgres drift by dropping the sequence, PK of this table is not autoincrement so it shouldn't have sequence in Postgres. Bug: T230428 Bug: T164898 Change-Id: I4c10990ead1e52c979254d2ac6a25ccf62a31508
* Migrate category to abstract schemaAmir Sarabadani2020-09-052-12/+14
| | | | | | | | | | | | | In order to migrate MySQL and Sqlite to abstract schema changed the cat_title data type from varchar binary to varbinary. This wouldn't affect production. For migrating Postgres, renamed two indexes from category_* to cat_* to make it in sync with MySQL/Sqlite Bug: T164898 Bug: T230428 Change-Id: Iad11aa4f7d809465cb20ac9748bf52b0e1bcd5a4
* Migrate iwlinks to abstract schemaAmir Sarabadani2020-09-053-8/+20
| | | | | | | | | | | | | In order to migrate MySQL and Sqlite to abstract schema changed the iwl_title data type from varchar binary to varbinary. This wouldn't affect production. For migrating Postgres: - Turning the unique index to PK to make it in sync with MySQL Bug: T164898 Bug: T230428 Change-Id: Iaa625b66c874023b8cf2403917fa2fa120279208
* Migrate langlinks to abstract schemaAmir Sarabadani2020-09-053-8/+13
| | | | | | | | | | | | | | | | | In order to migrate MySQL and Sqlite to abstract schema changed the ll_title data type from varchar binary to varbinary. This wouldn't affect production. For migrating Postgres: - Dropping foreign key on ll_from - Setting default of empty string for ll_lang and ll_title - Make ll_lang and ll_title both non-nullable to be in sync with MySQL - Turning the unique index to primary key, similar to MySQL - Renaming an index to sync with MySQL Bug: T164898 Bug: T230428 Change-Id: I57f22896ff67266f99bf08f6dd1b9cc4c51b1db9
* Migrate imagelinks table to abstract schemaAmir Sarabadani2020-08-222-7/+14
| | | | | | | | | | | | | | | In order to migrate MySQL and Sqlite to abstract schema changed the il_to data type from varchar binary to varbinary. This wouldn't affect production. For migrating Postgres: - Dropping foreign key on il_from - Setting default of empty string for il_to - Completely rewriting indexes to make it synced with MySQL Bug: T164898 Bug: T230428 Change-Id: I59f0d0a56d938a168bf1c7de2a1be47f15d1add1
* Migrate templatelinks table to abstract schemaAmir Sarabadani2020-08-222-8/+16
| | | | | | | | | | | | | | | | | In order to migrate MySQL and Sqlite to abstract schema changed the tl_title data type from varchar binary to varbinary. This wouldn't affect production. For migrating Postgres: - Dropping foreign key on tl_from - Changing data type of tl_namespace from SMALLINT to INT and setting its default - Setting default of empty string for tl_title - Completely rewriting indexes to make it synced with MySQL Bug: T164898 Bug: T230428 Change-Id: Idbb65d870f58f7146b9c8bd860e6530bef1e0f12
* Add extra blank line between tables in generated schemasLucas Werkmeister2020-08-201-0/+15
| | | | | | | | Put a single blank line between a CREATE TABLE and each accompanying CREATE INDEX, but two blank lines between those statements and the next CREATE TABLE. Change-Id: I8ae8a3c808a772a338e968213031d390bf1b31ca
* Make newlines in generated schemas more consistentLucas Werkmeister2020-08-191-1/+31
| | | | | | | | | | | | | | | Previously, a first str_replace() would add a single newline before any CREATE (TABLE or INDEX), and then a second one would add another newline after the $wgDBTableOptions, resulting in a blank line between any two CREATEs and also a newline at the end of the file – at least for MySQL schemas. But PostgreSQL and SQLite schemas don’t use $wgDBTableOptions, so the second str_replace() was a no-op for them, and they got no blank line between CREATEs nor a newline at the end of the file. Fix this by making the first str_replace() insert two newlines and appending a final newline all the time. Bug: T260779 Change-Id: Idcc4fae76d382b559f21234f8a6f49e537a60f11
* Migrate pagelinks table to abstract schemaAmir Sarabadani2020-08-192-11/+13
| | | | | | | | | | | | | | | | | In order to migrate MySQL and Sqlite to abstract schema changed the pl_title data type from varchar binary to varbinary. This wouldn't affect production. For migrating Postgres: - Dropping foreign key on pl_from - Changing data type of pl_namespace from SMALLINT to INT and setting its default - Setting default of empty string for pl_title - Completely rewriting indexes to make it synced with MySQL Bug: T164898 Bug: T230428 Change-Id: I4af8202742a1826e6b3f8ff36cf4f7b612b82690
* Migrate redirect table to abstract schemaAmir Sarabadani2020-08-132-11/+10
| | | | | | | | | | | | | | | Changing "varchar() binary" to varbinary in MySQL and Sqlite for two columns: rd_title and rd_fragment. Fixing db drifts of schema in Postgres: - Setting the data type of rd_namespace from SMALLINT and setting its default - Set empty string as default of rd_title - Changing datatype of rd_interwiki to varchar (PG supports that) Bug: T164898 Bug: T230428 Change-Id: I9563792b1fc0ce5f3de78c08703a768a0c2e99d0
* Merge "Migrate l10n_cache and module_deps to abstract schema"jenkins-bot2020-08-094-14/+18
|\
| * Migrate l10n_cache and module_deps to abstract schemaAmir Sarabadani2020-08-064-14/+18
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Also, fixing two data type drifts in Postgres: - lc_key was varchar (with no binary flag) in MySQL but TEXT in Postgres while PG supports varchar. Changing PG to varchar. - lc_value was BYTEA but the rest of varbinary fields are mapped to TEXT, changing it to TEXT data type. And fixing primary key of these tables in Postgres too as it was a unique index instead. Bug: T198811 Bug: T164898 Bug: T230428 Change-Id: I50305556bd870461d05f98c5272cf1d6a65deb15
* | Fix DROP CONSTRAINT for interwiki.iw_prefix PostgreSQL patchGergő Tisza2020-08-081-2/+2
| | | | | | | | | | | | | | | | This partialy reverts commit 9e9414db50870422e1960330a02bc69ce0435851. DROP CONSTRAINT is part of ALTER TABLE in PostgreSQL, not a stadalone command. Change-Id: Ifd1b4235ea3b94d130fa3a89739af68a6db820f7
* | Fix primary key sql patches of PostgresAmir Sarabadani2020-08-072-3/+3
|/ | | | | | | | | | | | | | Out of six PK SQL patches for Postgres, two of them were broken because you can't put drop index inside alter table: http://sqlfiddle.com/#!17/2acf44/1 One of them were caused by yours truly copying from the other one. Also fixing file name in one the pk patches. Noticed in I60a2b65d Change-Id: If793ea61437d46adf3e05c38df4ad37bce81c5cf
* Migrate content table to abstract schemaAmir Sarabadani2020-08-032-11/+8
| | | | | | | | | content.content_id was BIGINT in MySQL (and Sqlite) but INT in Postgres so making Postgres BIGINT as well. Bug: T230428 Bug: T164898 Change-Id: Ib8492e224478dc069ba17e489f7b2bb22d5da804
* Migrate change_tag to abstract schemaAmir Sarabadani2020-08-032-18/+16
| | | | | | | | This is the first table that doesn't have any drift between MySQL and Postgres. Bug: T230428 Change-Id: I8be8906fc0f998c8aec79e148487219b3d43e57c
* Migrate log_search table to abstract schemaAmir Sarabadani2020-08-032-9/+8
| | | | | | | | | Also ls_value was varchar (with no binary flag) in MySQL but TEXT in Postgres while PG supports varchar. Changing PG to varchar. Bug: T230428 Bug: T164898 Change-Id: I2f7fae7b1e781021cb7d3ed39c40c2fc9d20a680
* Migrate user_properties to abstract schemaAmir Sarabadani2020-07-313-9/+11
| | | | | | | | | | | | | Also, dropping foreign key constraint on user_properties.up_user as it's decided to drop all of foreign key constraints in T164898 And fixing primary key of the table in Postgres as it was a unique index instead. Bug: T198811 Bug: T164898 Bug: T230428 Change-Id: I60a2b65df62ec93a363309f8a17c29b83fd6f058
* Migrate site_stats table to abstract schemaAmir Sarabadani2020-07-312-12/+10
| | | | | | | | | | | | | | | Six fields were BIGINT in MySQL (and Sqlite) but INT in Postgres so making Postgres BIGINT as well: - ss_total_edits - ss_good_articles - ss_total_pages - ss_users - ss_active_users - ss_images Bug: T230428 Bug: T164898 Change-Id: I527b2797af3c8ba1a7402a4428b565f8e35cc132
* Migrate slots table to abstract schemaAmir Sarabadani2020-07-212-13/+11
| | | | | | | | | | | | Three fields were BIGINT in MySQL (and Sqlite) but INT in Postgres so making Postgres BIGINT as well: - slot_revision_id - slot_content_id - slot_origin Bug: T230428 Bug: T164898 Change-Id: I5333cc0cbdc36356bd865ae118b883bc367c31eb
* Migrate comment table to abstract schemaAmir Sarabadani2020-07-132-13/+9
| | | | | | | | | comment.comment_id was BIGINT in MySQL (and Sqlite) but INT in Postgres so making Postgres BIGINT as well. Bug: T230428 Bug: T164898 Change-Id: I29a1878eea2adfd6e7d8ad85a94dc83718f9671e
* Migrate bot_passwords table to abstract schemaAmir Sarabadani2020-07-132-10/+9
| | | | | | | | | bot_passwords.bp_token didn't have a default in Postgres while it has one in MySQL (and Sqlite), so adding the default to Postgres as well Bug: T230428 Bug: T164898 Change-Id: I0ae4dbf8f2a5382081c6211c9cad51843000e3f1
* Migrate actor and user_former_groups tables to abstract schemaAmir Sarabadani2020-07-122-19/+16
| | | | | | | | | | Also during the abstraction, fixing the schema drift between MySQL and Postgres in these two tables by removing foreign key, sequences, and changing data types (as outlined in T164898) Bug: T230428 Bug: T164898 Change-Id: If737a746629511b5a53d7ae70328fd558bd58d0e
* Migrate site_identifiers to abstract schemaAmir Sarabadani2020-06-142-10/+8
| | | | | | | | And converge the schema drift between mysql and postgres by setting all to binary Bug: T230428 Change-Id: I6f82b97c6e44c76ccdc0714aa8b32e959fbe896f
* Migrate updatelog to abstract schemaAmir Sarabadani2020-06-142-6/+5
| | | | | | | | | | | | | | And fixing the schema drift between mysql and postgres in this table by changing field type of ul_key and ul_value from TEXT and TEXT to VARCHAR(255) and BYTEA respectively. The automatic generation is done with running generateSchemaSql.php maintenance script. More info: https://www.mediawiki.org/wiki/Manual:Schema_changes#Automatically_generated Bug: T230428 Bug: T164898 Change-Id: Id785539d32546166d6f7a5c3cb1924f4841a2963
* Merge "Remove ipb_anon_only from ipb_address_unique UNIQUE INDEX"jenkins-bot2020-06-061-1/+1
|\
| * Remove ipb_anon_only from ipb_address_unique UNIQUE INDEXDayllan Maza2020-06-021-1/+1
| | | | | | | | | | Bug: T251188 Change-Id: I4618f659be64134664ac1644de2b51d74f000e4c
* | make rev_actor_timestamp index cover the rev_id field.daniel2020-06-031-1/+1
|/ | | | | | | | | | | | | | | | | | | | | | | | | | This adds the rev_id column to the end of the rev_actor_timestamp index to make it unambiguous. With the id field added, the index matches the pagination criterion for user contributions (filter by actor, sort by timestamp, then disambiguate based on revision id). The index is not marked UNIQUE since this would potentially degrade write performance. Uniqueness is already guaranteed by the rev_id field being the primary key. Note that no provisions are made to change the definition of the rev_actor_timestamp index for existing instances. This index was only recently added in I18071a2fe45907a0cf1b0fefebd96a97a2dacb7b and has not been part of any release. It has also not yet been created on the wikimedia servers. For this reason, any existing instances are assumed to be for testing only. Instances would also continue to function normally with the previous index definition. With this patch, the new index will be created correctly when updating from 1.34 or earlier. It will however not be modified for installations of some development version of 1.35. Bug: T200259 Bug: T238966 Change-Id: I511bb21b1ca820d950818cc831f8e3fef43a1559
* More work on formatting generated sqlAmir Sarabadani2020-05-101-0/+1
| | | | | Bug: T230428 Change-Id: I2cb4cab045addd8f532f414923ce323e1bed52f2
* Wire empty abstract schema into installerAmir Sarabadani2020-05-091-0/+3
| | | | | | | | | For more information on schema changes see: https://www.mediawiki.org/wiki/Manual:Schema_changes Bug: T191231 Bug: T230428 Change-Id: I779b9c61e388e998738a2ed41b0b8e2afdecd232
* Alter `revision` for actor, comment, and MCRBrad Jorsch2020-05-081-16/+8
| | | | | | | | | | | | | | | | | | | | | | | We're finally to the point of making the massive alter to the `revision` table that we've been building up to for 2.5 years now. Changes here are: * Drop `rev_text_id`, `rev_content_model`, and `rev_content_format` that MCR obsoleted. * Drop `ar_text_id`, `ar_content_model`, and `ar_content_format` that MCR obsoleted. * Replace `rev_comment` with `rev_comment_id`. * Replace `rev_user` and `rev_user_text` with `rev_actor`, plus associated index changes. Future patches will make the code changes to migrate data from `revision_actor_temp` and `revision_comment_temp` into the new `revision` columns. Bug: T251343 Bug: T184615 Bug: T215466 Change-Id: I18071a2fe45907a0cf1b0fefebd96a97a2dacb7b
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-03 01:23:35 +0000