saper has uploaded a new change for review.
https://gerrit.wikimedia.org/r/91375
Change subject: Sort out index creation for PostgreSQL
......................................................................
Sort out index creation for PostgreSQL
1) Extract indexed fields out of pg_index, not from
parsing "CREATE INDEX" SQL.
2) Allow UNIQUE indexes to be maintained by SMW
SMW_setup.php still attempts to ALTER non-existent
sequences.
Bug: 42659
Change-Id: I2d2cb63331d5ed785295d973b7f3124e1c289b53
---
M includes/storage/SMW_SQLHelpers.php
1 file changed, 66 insertions(+), 20 deletions(-)
git pull
ssh://gerrit.wikimedia.org:29418/mediawiki/extensions/SemanticMediaWiki
refs/changes/75/91375/1
diff --git a/includes/storage/SMW_SQLHelpers.php
b/includes/storage/SMW_SQLHelpers.php
index 18c7874..6f8ecbb 100644
--- a/includes/storage/SMW_SQLHelpers.php
+++ b/includes/storage/SMW_SQLHelpers.php
@@ -342,7 +342,11 @@
self::reportProgress( "Checking index structures for table
$tableName ...\n", $reportTo );
// First remove obsolete indexes.
- $oldIndexes = self::getIndexInfo( $db, $tableName );
+ if ( $wgDBtype == 'postgres' ) {
+ $oldIndexes = self::getIndexInfo( $db, $rawTableName );
+ } else {
+ $oldIndexes = self::getIndexInfo( $db, $tableName );
+ }
if ( $wgDBtype == 'sqlite' ) { // SQLite
/// TODO We do not currently get the right column
definitions in
/// SQLLite; hence we can only drop all indexes.
Wasteful.
@@ -373,7 +377,11 @@
$type = 'INDEX';
}
- self::createIndex( $db, $type,
"{$tableName}_index{$key}", $tableName, $columns, $reportTo );
+ $indexName = "{$tableName}_index{$key}";
+ if ( $wgDBtype === 'postgres' ) {
+ $indexName =
$db->tableName("{$rawTableName}_index{$key}");
+ }
+ self::createIndex( $db, $type, $indexName, $tableName,
$columns, $reportTo );
}
self::reportProgress( " ... done.\n", $reportTo );
@@ -396,25 +404,63 @@
$indexes = array();
if ( $wgDBtype == 'postgres' ) { // postgresql
- $sql = "SELECT i.relname AS indexname,"
- . " pg_get_indexdef(i.oid) AS indexdef, "
- . " replace(substring(pg_get_indexdef(i.oid)
from '\\\\((.*)\\\\)'),' ','') AS indexcolumns"
- . " FROM pg_index x"
- . " JOIN pg_class c ON c.oid = x.indrelid"
- . " JOIN pg_class i ON i.oid = x.indexrelid"
- . " LEFT JOIN pg_namespace n ON n.oid =
c.relnamespace"
- . " LEFT JOIN pg_tablespace t ON t.oid =
i.reltablespace"
- . " WHERE c.relkind = 'r'::\"char\" AND
i.relkind = 'i'::\"char\""
- . " AND c.relname = '" . $tableName . "'"
- . " AND NOT pg_get_indexdef(i.oid) ~ '^CREATE
UNIQUE INDEX'";
- $res = $db->query( $sql, __METHOD__ );
+ $schema = 'mediawiki';
+ $indexNameSql = <<<__INDEXNAME__
+ SELECT i.relname AS indexname
+ FROM pg_index x
+ JOIN pg_class c ON c.oid = x.indrelid AND
c.relkind = 'r'::"char"
+ AND c.relname =
'$tableName'
+ JOIN pg_class i ON i.oid = x.indexrelid AND
i.relkind = 'i'::"char"
+ JOIN pg_namespace n ON n.oid = c.relnamespace
AND n.nspname = '$schema'
+ WHERE NOT x.indisprimary
+__INDEXNAME__;
- if ( !$res ) {
+ $indexNameRes = $db->query( $indexNameSql, __METHOD__ );
+ if ( !$indexNameRes ) {
return false;
}
- foreach ( $res as $row ) {
- $indexes[$row->indexname] = $row->indexcolumns;
+ $atts = array();
+ foreach ( $indexNameRes as $indexRow ) {
+ $index = $indexRow->indexname;
+ $indexAttSql = <<<__INDEXATT__
+ SELECT
+ attname
+ FROM
+ (SELECT
generate_series(array_lower(isub.indkey,1), array_upper(isub.indkey,1)) AS g
+ FROM
+ pg_index isub
+ JOIN pg_class cis
+ ON cis.oid=isub.indexrelid
+ JOIN pg_namespace ns
+ ON cis.relnamespace = ns.oid
+ WHERE cis.relname='$index' AND
ns.nspname='$schema') AS s,
+ pg_attribute,
+ pg_opclass opcls,
+ pg_am,
+ pg_class ci
+ JOIN pg_index i
+ ON ci.oid=i.indexrelid
+ JOIN pg_class ct
+ ON ct.oid = i.indrelid
+ JOIN pg_namespace n
+ ON ci.relnamespace = n.oid
+ WHERE
+ ci.relname='$index' AND
n.nspname='$schema'
+ AND attrelid = ct.oid
+ AND i.indkey[s.g] = attnum
+ AND i.indclass[s.g] = opcls.oid
+ AND pg_am.oid = opcls.opcmethod
+__INDEXATT__;
+
+ $indexAttRes = $db->query( $indexAttSql,
__METHOD__ );
+ if ( !$indexAttRes ) {
+ return false;
+ }
+ foreach ( $indexAttRes as $attRow ) {
+ array_push ( $atts, $attRow->attname );
+ }
+ $indexes[$indexRow->indexname] = implode(",",
$atts);
}
} elseif ( $wgDBtype == 'sqlite' ) { // SQLite
$res = $db->query( 'PRAGMA index_list(' . $tableName .
')' , __METHOD__ );
@@ -489,10 +535,10 @@
protected static function createIndex( $db, $type, $indexName,
$tableName, $columns, $reportTo = null ) {
global $wgDBtype;
- self::reportProgress( " ... creating new index $columns ...",
$reportTo );
+ self::reportProgress( " ... creating new index $indexName
$columns ...", $reportTo );
if ( $wgDBtype == 'postgres' ) { // postgresql
if ( $db->indexInfo( $tableName, $indexName ) === false
) {
- $db->query( "CREATE $type $tableName ON
$tableName USING btree($columns)", __METHOD__ );
+ $db->query( "CREATE $type $indexName ON
$tableName USING btree($columns)", __METHOD__ );
}
} elseif ( $wgDBtype == 'sqlite' ) { // SQLite
$db->query( "CREATE $type $indexName ON $tableName
($columns)", __METHOD__ );
@@ -513,4 +559,4 @@
if ( !is_null( $receiver ) ) $receiver->reportProgress( $msg );
}
-}
\ No newline at end of file
+}
--
To view, visit https://gerrit.wikimedia.org/r/91375
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I2d2cb63331d5ed785295d973b7f3124e1c289b53
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/extensions/SemanticMediaWiki
Gerrit-Branch: 1.8.x
Gerrit-Owner: saper <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
