r46920 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r46919‎ | r46920 | r46921 >
Date:14:55, 6 February 2009
Author:mkroetzsch
Status:deferred
Tags:
Comment:
added code for PostgresQL support (and moved some generic SQL helpers to their own file), based on contributions by Marcel Gsteiger

+ fixed Bug 17382.
Modified paths:
  • /trunk/extensions/SemanticMediaWiki/includes/SMW_GlobalFunctions.php (modified) (history)
  • /trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php (added) (history)
  • /trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php (modified) (history)

Diff [purge]

Index: trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php
——@@ -779,7 +779,7 @@
780780 's_id' => $bnode,
781781 'p_id' => $pid,
782782 'value_unit' => $dv->getUnit(),
783 - 'value_xsd' => $dv[0],
 783+ 'value_xsd' => $keys[0],
784784 'value_num' => $dv->getNumericValue() );
785785 break;
786786 }
——@@ -1068,23 +1068,25 @@
10691069 global $wgDBtype;
10701070 $this->reportProgress("Setting up standard database configuration for SMW ...\n\n",$verbose);
10711071 $this->reportProgress("Selected storage engine is \"SMWSQLStore2\" (or an extension thereof)\n\n",$verbose);
1072 - if ($wgDBtype === 'postgres') {
1073 - $this->reportProgress("Sorry, Postgres is currently not supported. Please contact the\ndevelopers if you wish to help fixing this.\n",$verbose);
1074 - return;
1075 - }
10761072 $db =& wfGetDB( DB_MASTER );
10771073 extract( $db->tableNames('smw_ids','smw_rels2','smw_atts2','smw_text2',
10781074 'smw_spec2','smw_subs2','smw_redi2','smw_inst2',
10791075 'smw_conc2','smw_conccache') );
 1076+ $reportTo = $verbose?$this:NULL; // use $this to report back from static SMWSQLHelpers
 1077+ // repeatedly used DB field types defined here for convenience
 1078+ $dbt_id = SMWSQLHelpers::getStandardDBType('id');
 1079+ $dbt_namespace = SMWSQLHelpers::getStandardDBType('namespace');
 1080+ $dbt_title = SMWSQLHelpers::getStandardDBType('title');
 1081+ $dbt_iw = SMWSQLHelpers::getStandardDBType('iw');
 1082+ $dbt_blob = SMWSQLHelpers::getStandardDBType('blob');
10801083
1081 - $this->setupTable($smw_ids, // internal IDs used in this store
1082 - array('smw_id' => 'INT(8) UNSIGNED NOT NULL KEY AUTO_INCREMENT',
1083 - 'smw_namespace' => 'INT(11) NOT NULL',
1084 - 'smw_title' => 'VARCHAR(255) binary NOT NULL',
1085 - 'smw_iw' => 'CHAR(32)',
1086 - 'smw_sortkey' => 'VARCHAR(255) binary NOT NULL'
1087 - ), $db, $verbose);
1088 - $this->setupIndex($smw_ids, array('smw_id','smw_title,smw_namespace,smw_iw', 'smw_sortkey'), $db);
 1084+ SMWSQLHelpers::setupTable($smw_ids, // internal IDs used in this store
 1085+ array('smw_id' => $dbt_id . ' NOT NULL' . ($wgDBtype=='postgres'?' PRIMARY KEY':' KEY AUTO_INCREMENT'),
 1086+ 'smw_namespace' => $dbt_namespace . ' NOT NULL',
 1087+ 'smw_title' => $dbt_title . ' NOT NULL',
 1088+ 'smw_iw' => $dbt_iw,
 1089+ 'smw_sortkey' => $dbt_title . ' NOT NULL'), $db, $reportTo);
 1090+ SMWSQLHelpers::setupIndex($smw_ids, array('smw_id','smw_title,smw_namespace,smw_iw', 'smw_sortkey'), $db);
10891091 // NOTE: smw_ids is normally used to store references to wiki pages (possibly with some external
10901092 // interwiki prefix). There are, however, some special objects that are also stored therein. These
10911093 // are marked by special interwiki prefixes (iw) that cannot occcur in real life:
——@@ -1102,67 +1104,71 @@
11031105 // are reserved for hardcoded ids built into SMW) and normal entries. It is no object, but makes sure that
11041106 // SQL's auto increment counter is high enough to not add any objects before that marked "border".
11051107
1106 - $this->setupTable($smw_redi2, // fast redirect resolution
1107 - array('s_title' => 'VARCHAR(255) binary NOT NULL',
1108 - 's_namespace' => 'INT(11) NOT NULL',
1109 - 'o_id' => 'INT(8) UNSIGNED NOT NULL',), $db, $verbose);
1110 - $this->setupIndex($smw_redi2, array('s_title,s_namespace','o_id'), $db);
 1108+ SMWSQLHelpers::setupTable($smw_redi2, // fast redirect resolution
 1109+ array('s_title' => $dbt_title . ' NOT NULL',
 1110+ 's_namespace' => $dbt_namespace . ' NOT NULL',
 1111+ 'o_id' => $dbt_id . ' NOT NULL'), $db, $reportTo);
 1112+ SMWSQLHelpers::setupIndex($smw_redi2, array('s_title,s_namespace','o_id'), $db);
11111113
1112 - $this->setupTable($smw_rels2, // properties with other pages as values ("relations")
1113 - array('s_id' => 'INT(8) UNSIGNED NOT NULL',
1114 - 'p_id' => 'INT(8) UNSIGNED NOT NULL',
1115 - 'o_id' => 'INT(8) UNSIGNED NOT NULL'), $db, $verbose);
1116 - $this->setupIndex($smw_rels2, array('s_id','p_id','o_id'), $db);
 1114+ SMWSQLHelpers::setupTable($smw_rels2, // properties with other pages as values ("relations")
 1115+ array('s_id' => $dbt_id . ' NOT NULL',
 1116+ 'p_id' => $dbt_id . ' NOT NULL',
 1117+ 'o_id' => $dbt_id . ' NOT NULL'), $db, $reportTo);
 1118+ SMWSQLHelpers::setupIndex($smw_rels2, array('s_id','p_id','o_id'), $db);
11171119
1118 - $this->setupTable($smw_atts2, // most standard properties ("attributes")
1119 - array('s_id' => 'INT(8) UNSIGNED NOT NULL',
1120 - 'p_id' => 'INT(8) UNSIGNED NOT NULL',
1121 - 'value_unit' => 'VARCHAR(63) binary',
1122 - 'value_xsd' => 'VARCHAR(255) binary NOT NULL',
1123 - 'value_num' => 'DOUBLE'), $db, $verbose);
1124 - $this->setupIndex($smw_atts2, array('s_id','p_id','value_num','value_xsd'), $db);
 1120+ SMWSQLHelpers::setupTable($smw_atts2, // most standard properties ("attributes")
 1121+ array('s_id' => $dbt_id . ' NOT NULL',
 1122+ 'p_id' => $dbt_id . ' NOT NULL',
 1123+ 'value_unit' => ($wgDBtype=='postgres'?'TEXT':'VARCHAR(63) binary'),
 1124+ 'value_xsd' => $dbt_title . ' NOT NULL',
 1125+ 'value_num' => ($wgDBtype=='postgres'?'DOUBLE PRECISION':'DOUBLE')), $db, $reportTo);
 1126+ SMWSQLHelpers::setupIndex($smw_atts2, array('s_id','p_id','value_num','value_xsd'), $db);
11251127
1126 - $this->setupTable($smw_text2, // properties with long strings as values
1127 - array('s_id' => 'INT(8) UNSIGNED NOT NULL',
1128 - 'p_id' => 'INT(8) UNSIGNED NOT NULL',
1129 - 'value_blob' => 'MEDIUMBLOB'), $db, $verbose);
1130 - $this->setupIndex($smw_text2, array('s_id','p_id'), $db);
 1128+ SMWSQLHelpers::setupTable($smw_text2, // properties with long strings as values
 1129+ array('s_id' => $dbt_id . ' NOT NULL',
 1130+ 'p_id' => $dbt_id . ' NOT NULL',
 1131+ 'value_blob' => $dbt_blob), $db, $reportTo);
 1132+ SMWSQLHelpers::setupIndex($smw_text2, array('s_id','p_id'), $db);
11311133
11321134 // field renaming between SMW 1.3 and SMW 1.4:
11331135 if ( ($db->tableExists($smw_spec2)) && ($db->fieldExists($smw_spec2, 'sp_id', 'SMWSQLStore2::setup')) ) {
1134 - $db->query("ALTER TABLE $smw_spec2 CHANGE `sp_id` `p_id` INT(8) UNSIGNED NOT NULL", 'SMWSQLStore2::setup');
 1136+ if ($wgDBtype=='postgres') {
 1137+ $db->query("ALTER TABLE $smw_spec2 ALTER COLUMN sp_id RENAME TO p_id", 'SMWSQLStore2::setup');
 1138+ } else {
 1139+ $db->query("ALTER TABLE $smw_spec2 CHANGE `sp_id` `p_id` $dbt_id NOT NULL", 'SMWSQLStore2::setup');
 1140+ }
11351141 }
1136 - $this->setupTable($smw_spec2, // very important special properties, for faster access
1137 - array('s_id' => 'INT(8) UNSIGNED NOT NULL',
1138 - 'p_id' => 'INT(8) UNSIGNED NOT NULL',
1139 - 'value_string' => 'VARCHAR(255) binary NOT NULL'), $db, $verbose);
1140 - $this->setupIndex($smw_spec2, array('s_id', 'p_id', 's_id,p_id'), $db);
 1142+ SMWSQLHelpers::setupTable($smw_spec2, // very important special properties, for faster access
 1143+ array('s_id' => $dbt_id . ' NOT NULL',
 1144+ 'p_id' => $dbt_id . ' NOT NULL',
 1145+ 'value_string' => $dbt_title . ' NOT NULL'), $db, $reportTo);
 1146+ SMWSQLHelpers::setupIndex($smw_spec2, array('s_id', 'p_id', 's_id,p_id'), $db);
11411147
1142 - $this->setupTable($smw_subs2, // subproperty/subclass relationships
1143 - array('s_id' => 'INT(8) UNSIGNED NOT NULL',
1144 - 'o_id' => 'INT(8) UNSIGNED NOT NULL',), $db, $verbose);
1145 - $this->setupIndex($smw_subs2, array('s_id', 'o_id'), $db);
 1148+ SMWSQLHelpers::setupTable($smw_subs2, // subproperty/subclass relationships
 1149+ array('s_id' => $dbt_id . ' NOT NULL',
 1150+ 'o_id' => $dbt_id . ' NOT NULL'), $db, $reportTo);
 1151+ SMWSQLHelpers::setupIndex($smw_subs2, array('s_id', 'o_id'), $db);
11461152
1147 - $this->setupTable($smw_inst2, // class instances (s_id the element, o_id the class)
1148 - array('s_id' => 'INT(8) UNSIGNED NOT NULL',
1149 - 'o_id' => 'INT(8) UNSIGNED NOT NULL',), $db, $verbose);
1150 - $this->setupIndex($smw_inst2, array('s_id', 'o_id'), $db);
 1153+ SMWSQLHelpers::setupTable($smw_inst2, // class instances (s_id the element, o_id the class)
 1154+ array('s_id' => $dbt_id . ' NOT NULL',
 1155+ 'o_id' => $dbt_id . ' NOT NULL',), $db, $reportTo);
 1156+ SMWSQLHelpers::setupIndex($smw_inst2, array('s_id', 'o_id'), $db);
11511157
1152 - $this->setupTable($smw_conc2, // concept descriptions
1153 - array('s_id' => 'INT(8) UNSIGNED NOT NULL KEY',
1154 - 'concept_txt' => 'MEDIUMBLOB',
1155 - 'concept_docu' => 'MEDIUMBLOB',
1156 - 'concept_features' => 'INT(8)',
1157 - 'concept_size' => 'INT(8)',
1158 - 'concept_depth' => 'INT(8)',
1159 - 'cache_date' => 'INT(8) UNSIGNED',
1160 - 'cache_count' => 'INT(8) UNSIGNED' ), $db, $verbose);
1161 - $this->setupIndex($smw_conc2, array('s_id'), $db);
 1158+ SMWSQLHelpers::setupTable($smw_conc2, // concept descriptions
 1159+ array('s_id' => $dbt_id . ' NOT NULL' . ($wgDBtype=='postgres'?' PRIMARY KEY':' KEY'),
 1160+ 'concept_txt' => $dbt_blob,
 1161+ 'concept_docu' => $dbt_blob,
 1162+ 'concept_features' => ($wgDBtype=='postgres'?'INTEGER':'INT(8)'),
 1163+ 'concept_size' => ($wgDBtype=='postgres'?'INTEGER':'INT(8)'),
 1164+ 'concept_depth' => ($wgDBtype=='postgres'?'INTEGER':'INT(8)'),
 1165+ 'cache_date' => ($wgDBtype=='postgres'?'INTEGER':'INT(8) UNSIGNED'),
 1166+ 'cache_count' => ($wgDBtype=='postgres'?'INTEGER':'INT(8) UNSIGNED'), ), $db, $reportTo);
 1167+ SMWSQLHelpers::setupIndex($smw_conc2, array('s_id'), $db);
11621168
1163 - $this->setupTable($smw_conccache, // concept cache: member elements (s)->concepts (o)
1164 - array('s_id' => 'INT(8) UNSIGNED NOT NULL',
1165 - 'o_id' => 'INT(8) UNSIGNED NOT NULL'), $db, $verbose);
1166 - $this->setupIndex($smw_conccache, array('o_id'), $db);
 1169+ SMWSQLHelpers::setupTable($smw_conccache, // concept cache: member elements (s)->concepts (o)
 1170+ array('s_id' => $dbt_id . ' NOT NULL',
 1171+ 'o_id' => $dbt_id . ' NOT NULL'), $db, $reportTo);
 1172+ SMWSQLHelpers::setupIndex($smw_conccache, array('o_id'), $db);
11671173
11681174 $this->reportProgress("Database initialised successfully.\n\n",$verbose);
11691175 $this->reportProgress("Setting up internal property indices ...\n",$verbose);
——@@ -1185,13 +1191,14 @@
11861192 $this->reportProgress(" ... writing entries for internal properties.\n",$verbose);
11871193 foreach (SMWSQLStore2::$special_ids as $prop => $id) {
11881194 $p = SMWPropertyValue::makeProperty($prop);
1189 - $db->replace('smw_ids', array(), array('smw_id' => $id, 'smw_title' => $p->getDBkey(), 'smw_namespace' => SMW_NS_PROPERTY, 'smw_iw' => $this->getPropertyInterwiki($p), 'smw_sortkey' => $p->getDBkey()), 'SMW::setup');
 1195+ $db->replace('smw_ids', array('smw_id'), array('smw_id' => $id, 'smw_title' => $p->getDBkey(), 'smw_namespace' => SMW_NS_PROPERTY, 'smw_iw' => $this->getPropertyInterwiki($p), 'smw_sortkey' => $p->getDBkey()), 'SMW::setup');
11901196 }
11911197 $this->reportProgress("Internal properties initialised successfully.\n",$verbose);
11921198 return true;
11931199 }
11941200
11951201 function drop($verbose = true) {
 1202+ global $wgDBtype;
11961203 $this->reportProgress("Deleting all database content and tables generated by SMW ...\n\n",$verbose);
11971204 $db =& wfGetDB( DB_MASTER );
11981205 $tables = array('smw_rels2', 'smw_atts2', 'smw_text2', 'smw_spec2',
——@@ -1199,7 +1206,7 @@
12001207 'smw_conc2');
12011208 foreach ($tables as $table) {
12021209 $name = $db->tableName($table);
1203 - $db->query("DROP TABLE IF EXISTS $name", 'SMWSQLStore2::drop');
 1210+ $db->query('DROP TABLE' . ($wgDBtype=='postgres'?'':' IF EXISTS'). $name, 'SMWSQLStore2::drop');
12041211 $this->reportProgress(" ... dropped table $name.\n", $verbose);
12051212 }
12061213 $this->reportProgress("All data removed successfully.\n",$verbose);
——@@ -1478,131 +1485,11 @@
14791486 return $result;
14801487 }
14811488
1482 -
14831489 /**
1484 - * Make sure the table of the given name has the given fields, provided
1485 - * as an array with entries fieldname => typeparams. typeparams should be
1486 - * in a normalised form and order to match to existing values.
1487 - *
1488 - * The function returns an array that includes all columns that have been
1489 - * changed. For each such column, the array contains an entry
1490 - * columnname => action, where action is one of 'up', 'new', or 'del'
1491 - * If the table was already fine or was created completely anew, an empty
1492 - * array is returned (assuming that both cases require no action).
1493 - *
1494 - * @note The function partly ignores the order in which fields are set up.
1495 - * Only if the type of some field changes will its order be adjusted explicitly.
1496 - */
1497 - protected function setupTable($table, $fields, $db, $verbose) {
1498 - global $wgDBname;
1499 - $this->reportProgress("Setting up table $table ...\n",$verbose);
1500 - if ($db->tableExists($table) === false) { // create new table
1501 - $sql = 'CREATE TABLE `' . $wgDBname . '`.' . $table . ' (';
1502 - $first = true;
1503 - foreach ($fields as $name => $type) {
1504 - if ($first) {
1505 - $first = false;
1506 - } else {
1507 - $sql .= ',';
1508 - }
1509 - $sql .= $name . ' ' . $type;
1510 - }
1511 - $sql .= ') TYPE=innodb';
1512 - $db->query( $sql, 'SMWSQLStore2::setupTable' );
1513 - $this->reportProgress(" ... new table created\n",$verbose);
1514 - return array();
1515 - } else { // check table signature
1516 - $this->reportProgress(" ... table exists already, checking structure ...\n",$verbose);
1517 - $res = $db->query( 'DESCRIBE ' . $table, 'SMWSQLStore2::setupTable' );
1518 - $curfields = array();
1519 - $result = array();
1520 - while ($row = $db->fetchObject($res)) {
1521 - $type = strtoupper($row->Type);
1522 - if (substr($type,0,8) == 'VARCHAR(') {
1523 - $type .= ' binary'; // just assume this to be the case for VARCHAR, avoid collation checks
1524 - }
1525 - if ($row->Null != 'YES') {
1526 - $type .= ' NOT NULL';
1527 - }
1528 - if ($row->Key == 'PRI') { /// FIXME: updating "KEY" is not possible, the below query will fail in this case.
1529 - $type .= ' KEY';
1530 - }
1531 - if ($row->Extra == 'auto_increment') {
1532 - $type .= ' AUTO_INCREMENT';
1533 - }
1534 - $curfields[$row->Field] = $type;
1535 - }
1536 - $position = 'FIRST';
1537 - foreach ($fields as $name => $type) {
1538 - if ( !array_key_exists($name,$curfields) ) {
1539 - $this->reportProgress(" ... creating column $name ... ",$verbose);
1540 - $db->query("ALTER TABLE $table ADD `$name` $type $position", 'SMWSQLStore2::setupTable');
1541 - $result[$name] = 'new';
1542 - $this->reportProgress("done \n",$verbose);
1543 - } elseif ($curfields[$name] != $type) {
1544 - $this->reportProgress(" ... changing type of column $name from '$curfields[$name]' to '$type' ... ",$verbose);
1545 - $db->query("ALTER TABLE $table CHANGE `$name` `$name` $type $position", 'SMWSQLStore2::setupTable');
1546 - $result[$name] = 'up';
1547 - $curfields[$name] = false;
1548 - $this->reportProgress("done.\n",$verbose);
1549 - } else {
1550 - $this->reportProgress(" ... column $name is fine\n",$verbose);
1551 - $curfields[$name] = false;
1552 - }
1553 - $position = "AFTER $name";
1554 - }
1555 - foreach ($curfields as $name => $value) {
1556 - if ($value !== false) { // not encountered yet --> delete
1557 - $this->reportProgress(" ... deleting obsolete column $name ... ",$verbose);
1558 - $db->query("ALTER TABLE $table DROP COLUMN `$name`", 'SMWSQLStore2::setupTable');
1559 - $result[$name] = 'del';
1560 - $this->reportProgress("done.\n",$verbose);
1561 - }
1562 - }
1563 - $this->reportProgress(" ... table $table set up successfully.\n",$verbose);
1564 - return $result;
1565 - }
1566 - }
1567 -
1568 - /**
1569 - * Make sure that each of the column descriptions in the given array is indexed by *one* index
1570 - * in the given DB table.
1571 - */
1572 - protected function setupIndex($table, $columns, $db) {
1573 - $table = $db->tableName($table);
1574 - $res = $db->query( 'SHOW INDEX FROM ' . $table , 'SMW::SetupIndex');
1575 - if ( !$res ) {
1576 - return false;
1577 - }
1578 - $indexes = array();
1579 - while ( $row = $db->fetchObject( $res ) ) {
1580 - if (!array_key_exists($row->Key_name, $indexes)) {
1581 - $indexes[$row->Key_name] = array();
1582 - }
1583 - $indexes[$row->Key_name][$row->Seq_in_index] = $row->Column_name;
1584 - }
1585 - foreach ($indexes as $key => $index) { // clean up existing indexes
1586 - $id = array_search(implode(',', $index), $columns );
1587 - if ( $id !== false ) {
1588 - $columns[$id] = false;
1589 - } else { // duplicate or unrequired index
1590 - $db->query( 'DROP INDEX ' . $key . ' ON ' . $table, 'SMW::SetupIndex');
1591 - }
1592 - }
1593 -
1594 - foreach ($columns as $key => $column) { // add remaining indexes
1595 - if ($column != false) {
1596 - $db->query( "ALTER TABLE $table ADD INDEX ( $column )", 'SMW::SetupIndex');
1597 - }
1598 - }
1599 - return true;
1600 - }
1601 -
1602 - /**
16031490 * Print some output to indicate progress. The output message is given by
16041491 * $msg, while $verbose indicates whether or not output is desired at all.
16051492 */
1606 - protected function reportProgress($msg, $verbose) {
 1493+ public function reportProgress($msg, $verbose = true) {
16071494 if (!$verbose) {
16081495 return;
16091496 }
——@@ -1709,13 +1596,14 @@
17101597 * the title is a redirect target (we do not want chains of redirects).
17111598 */
17121599 protected function makeSMWPageID($title, $namespace, $iw, $canonical=true, $sortkey = '') {
 1600+ global $wgDBtype;
17131601 wfProfileIn('SMWSQLStore2::makeSMWPageID (SMW)');
17141602 $oldsort = '';
17151603 $id = $this->getSMWPageIDandSort($title, $namespace, $iw, $oldsort, $canonical);
17161604 if ($id == 0) {
17171605 $db =& wfGetDB( DB_MASTER );
17181606 $sortkey = $sortkey?$sortkey:(str_replace('_',' ',$title));
1719 - $db->insert('smw_ids', array('smw_id' => 0, 'smw_title' => $title, 'smw_namespace' => $namespace, 'smw_iw' => $iw, 'smw_sortkey' => $sortkey), 'SMW::makeSMWPageID');
 1607+ $db->insert('smw_ids', array('smw_id' => (($wgDBtype=='postgres')?($db->nextSequenceValue('smw_ids_smw_id_seq')):0), 'smw_title' => $title, 'smw_namespace' => $namespace, 'smw_iw' => $iw, 'smw_sortkey' => $sortkey), 'SMW::makeSMWPageID');
17201608 $id = $db->insertId();
17211609 $this->m_ids["$iw $namespace $title -"] = $id; // fill that cache, even if canonical was given
17221610 // This ID is also authorative for the canonical version.
——@@ -1797,6 +1685,7 @@
17981686 * a new bnode id!
17991687 */
18001688 protected function makeSMWBnodeID($sid) {
 1689+ global $wgDBtype;
18011690 $db =& wfGetDB( DB_MASTER );
18021691 $id = 0;
18031692 // check if there is an unused bnode to take:
——@@ -1813,7 +1702,7 @@
18141703 }
18151704 // if no node was found yet, make a new one:
18161705 if ($id == 0) {
1817 - $db->insert('smw_ids', array('smw_id' => 0, 'smw_title' => '', 'smw_namespace' => $sid, 'smw_iw' => SMW_SQL2_SMWIW), 'SMW::makeSMWBnodeID');
 1706+ $db->insert('smw_ids', array('smw_id' => (($wgDBtype=='postgres')?($db->nextSequenceValue('smw_ids_smw_id_seq')):0), 'smw_title' => '', 'smw_namespace' => $sid, 'smw_iw' => SMW_SQL2_SMWIW), 'SMW::makeSMWBnodeID');
18181707 $id = $db->insertId();
18191708 }
18201709 return $id;
——@@ -1827,11 +1716,12 @@
18281717 * to an id that is still in use somewhere).
18291718 */
18301719 protected function moveID($curid, $targetid = 0) {
 1720+ global $wgDBtype;
18311721 $db =& wfGetDB( DB_MASTER );
18321722 $row = $db->selectRow('smw_ids', array('smw_id', 'smw_namespace', 'smw_title', 'smw_iw', 'smw_sortkey'), array('smw_id' => $curid), 'SMWSQLStore2::moveID');
18331723 if ($row === false) return; // no id at current position, ignore
18341724 if ($targetid == 0) {
1835 - $db->insert('smw_ids', array('smw_id' => 0, 'smw_title' => $row->smw_title, 'smw_namespace' => $row->smw_namespace, 'smw_iw' => $row->smw_iw, 'smw_sortkey' => $row->smw_sortkey), 'SMW::moveID');
 1725+ $db->insert('smw_ids', array('smw_id' => (($wgDBtype=='postgres')?($db->nextSequenceValue('smw_ids_smw_id_seq')):0), 'smw_title' => $row->smw_title, 'smw_namespace' => $row->smw_namespace, 'smw_iw' => $row->smw_iw, 'smw_sortkey' => $row->smw_sortkey), 'SMW::moveID');
18361726 $targetid = $db->insertId();
18371727 } else {
18381728 $db->insert('smw_ids', array('smw_id' => $targetid, 'smw_title' => $row->smw_title, 'smw_namespace' => $row->smw_namespace, 'smw_iw' => $row->smw_iw, 'smw_sortkey' => $row->smw_sortkey), 'SMW::moveID');
Index: trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php
——@@ -0,0 +1,287 @@
 2+<?php
 3+/**
 4+ * Some static helper functions that SMW uses for setting up
 5+ * SQL databases.
 6+ *
 7+ * @author Markus Krötzsch
 8+ * @author Marcel Gsteiger
 9+ * @file
 10+ * @ingroup SMWStore
 11+ */
 12+
 13+/**
 14+ * Static class to collect some helper functions that SMW uses
 15+ * for settnig up SQL databases.
 16+ * @ingroup SMWStore
 17+ */
 18+class SMWSQLHelpers {
 19+
 20+ /**
 21+ * Database backends often have different types that need to be used
 22+ * repeatedly in (Semantic) MediaWiki. This function provides the preferred
 23+ * type (as a string) for various common kinds of columns. The input
 24+ * is one of the following strings: 'id' (page id numbers or similar),
 25+ * 'title' (title strings or similar), 'namespace' (namespace numbers),
 26+ * 'blob' (longer text blobs), 'iw' (interwiki prefixes).
 27+ */
 28+ static public function getStandardDBType($input) {
 29+ global $wgDBtype;
 30+ switch ($input) {
 31+ case 'id': return $wgDBtype=='postgres'?'SERIAL':'INT(8) UNSIGNED'; // like page_id in MW page table
 32+ case 'namespace': return $wgDBtype=='postgres'?'BIGINT':'INT(11)'; // like page_namespace in MW page table
 33+ case 'title': return $wgDBtype=='postgres'?'TEXT':'VARCHAR(255) binary'; // like page_title in MW page table
 34+ case 'iw': return $wgDBtype=='postgres'?'TEXT':'VARCHAR(32) binary'; // like iw_prefix in MW interwiki table
 35+ case 'blob': return $wgDBtype=='postgres'?'BYTEA':'MEDIUMBLOB'; // larger blobs of character data, usually not subject to SELECT conditions
 36+ }
 37+ return false;
 38+ }
 39+
 40+ /**
 41+ * Generic creation and updating function for database tables. Ideally, it
 42+ * would be able to modify a table's signature in arbitrary ways, but it will
 43+ * fail for some changes. Its string-based interface is somewhat too
 44+ * impoverished for a permanent solution. It would be possible to go for update
 45+ * scripts (specific to each change) in the style of MediaWiki instead.
 46+ *
 47+ * Make sure the table of the given name has the given fields, provided
 48+ * as an array with entries fieldname => typeparams. typeparams should be
 49+ * in a normalised form and order to match to existing values.
 50+ *
 51+ * The function returns an array that includes all columns that have been
 52+ * changed. For each such column, the array contains an entry
 53+ * columnname => action, where action is one of 'up', 'new', or 'del'
 54+ * If the table was already fine or was created completely anew, an empty
 55+ * array is returned (assuming that both cases require no action).
 56+ *
 57+ * If progress reports during this operation are desired, then the parameter $reportTo should
 58+ * be given an object that has a method reportProgress(string) for doing so.
 59+ *
 60+ * @note The function partly ignores the order in which fields are set up.
 61+ * Only if the type of some field changes will its order be adjusted explicitly.
 62+ */
 63+ public static function setupTable($table, $fields, $db, $reportTo = NULL) {
 64+ global $wgDBname, $wgDBtype;
 65+ $fname = 'SMWSQLHelpers::setupTable';
 66+
 67+ SMWSQLHelpers::reportProgress("Setting up table $table ...\n",$reportTo);
 68+ if ($db->tableExists($table) === false) { // create new table
 69+ $sql = 'CREATE TABLE ' . ($wgDBtype=='postgres'?'': "`$wgDBname`.") . $table . ' (';
 70+ $first = true;
 71+ foreach ($fields as $name => $type) {
 72+ if ($first) {
 73+ $first = false;
 74+ } else {
 75+ $sql .= ',';
 76+ }
 77+ $sql .= $name . ' ' . $type;
 78+ }
 79+ $sql .= ') ' . ($wgDBtype=='postgres'?'':'TYPE=innodb');
 80+ $db->query( $sql, $fname );
 81+ SMWSQLHelpers::reportProgress(" ... new table created\n",$reportTo);
 82+ return array();
 83+ } else { // check table signature
 84+ SMWSQLHelpers::reportProgress(" ... table exists already, checking structure ...\n",$reportTo);
 85+ if ($wgDBtype=='postgres') { // postgresql
 86+ // use the data dictionary in postgresql to get an output comparable to DESCRIBE
 87+ // To find out what kind of magic takes place here (and to remove the bugs included), simply use:
 88+ // psql
 89+ // \set ECHO_HIDDEN
 90+ // \d <tablename>
 91+ $sql = 'SELECT a.attname as "Field", '
 92+ .' upper(pg_catalog.format_type(a.atttypid, a.atttypmod)) as "Type", '
 93+ .' (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) '
 94+ .' FROM pg_catalog.pg_attrdef d '
 95+ .' WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as "Extra", '
 96+ .' case when a.attnotnull THEN \'NO\'::text else \'YES\'::text END as "Null", a.attnum '
 97+ .' FROM pg_catalog.pg_attribute a '
 98+ .' WHERE a.attrelid = ('
 99+ .' SELECT c.oid '
 100+ .' FROM pg_catalog.pg_class c '
 101+ .' LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace '
 102+ .' WHERE c.relname ~ \'^(' . $table . ')$\' '
 103+ .' AND pg_catalog.pg_table_is_visible(c.oid) '
 104+ .' LIMIT 1 '
 105+ .' ) AND a.attnum > 0 AND NOT a.attisdropped '
 106+ .' ORDER BY a.attnum';
 107+ } else { // mysql
 108+ $sql = 'DESCRIBE ' . $table;
 109+ }
 110+ $res = $db->query($sql, $fname);
 111+ $curfields = array();
 112+ $result = array();
 113+ while ($row = $db->fetchObject($res)) {
 114+ $type = strtoupper($row->Type);
 115+ if ($wgDBtype=='postgres') { // postgresql
 116+ if (eregi('^nextval\\(.+\\)$',$row->Extra)) {
 117+ $type = 'SERIAL NOT NULL';
 118+ } elseif ($row->Null != 'YES') {
 119+ $type .= ' NOT NULL';
 120+ }
 121+ } else { // mysql
 122+ if (substr($type,0,8) == 'VARCHAR(') {
 123+ $type .= ' binary'; // just assume this to be the case for VARCHAR, though DESCRIBE will not tell us
 124+ }
 125+ if ($row->Null != 'YES') {
 126+ $type .= ' NOT NULL';
 127+ }
 128+ if ($row->Key == 'PRI') { /// FIXME: updating "KEY" is not possible, the below query will fail in this case.
 129+ $type .= ' KEY';
 130+ }
 131+ if ($row->Extra == 'auto_increment') {
 132+ $type .= ' AUTO_INCREMENT';
 133+ }
 134+ }
 135+ $curfields[$row->Field] = $type;
 136+ }
 137+
 138+ if ($wgDBtype=='postgres') { // postgresql
 139+ foreach ($fields as $name => $type) {
 140+ $keypos = strpos($type,' PRIMARY KEY');
 141+ if ($keypos > 0) {
 142+ $type=substr($type,0,$keypos);
 143+ }
 144+ if ( !array_key_exists($name,$curfields) ) {
 145+ SMWSQLHelpers::reportProgress(" ... creating column $name ... ",$reportTo);
 146+ $db->query("ALTER TABLE $table ADD \"" . $name . "\" $type", $fname);
 147+ $result[$name] = 'new';
 148+ SMWSQLHelpers::reportProgress("done \n",$reportTo);
 149+ } elseif ($curfields[$name] != $type) {
 150+ SMWSQLHelpers::reportProgress(" ... changing type of column $name from '$curfields[$name]' to '$type' ... ",$reportTo);
 151+ $notnullposnew = strpos($type,' NOT NULL');
 152+ if ($notnullposnew > 0) {
 153+ $type=substr($type,0,$notnullposnew);
 154+ }
 155+ $notnullposold = strpos($curfields[$name],' NOT NULL');
 156+ $typeold = ($notnullposold > 0)?substr($curfields[$name],0,$notnullposold):$curfields[$name];
 157+ if ($typeold!=$type) {
 158+ $db->query("ALTER TABLE \"".$table."\" ALTER COLUMN \"".$name."\" TYPE " . $type, $fname);
 159+ }
 160+ if ($notnullposold!=$notnullposnew) {
 161+ $db->query("ALTER TABLE \"". $table . "\" ALTER COLUMN \"".$name."\" ".($notnullposnew>0?'SET':'DROP'). " NOT NULL", $fname);
 162+ }
 163+ $result[$name] = 'up';
 164+ $curfields[$name] = false;
 165+ SMWSQLHelpers::reportProgress("done.\n",$reportTo);
 166+ } else {
 167+ SMWSQLHelpers::reportProgress(" ... column $name is fine\n",$reportTo);
 168+ $curfields[$name] = false;
 169+ }
 170+ }
 171+ foreach ($curfields as $name => $value) {
 172+ if ($value !== false) {
 173+ SMWSQLHelpers::reportProgress(" ... deleting obsolete column $name ... ",$reportTo);
 174+ $db->query("ALTER TABLE \"". $table . "\" DROP COLUMN \"" . $name . "\"", $fname);
 175+ $result[$name] = 'del';
 176+ SMWSQLHelpers::reportProgress("done.\n",$reportTo);
 177+ }
 178+ }
 179+ } else { // mysql
 180+ $position = 'FIRST';
 181+ foreach ($fields as $name => $type) {
 182+ if ( !array_key_exists($name,$curfields) ) {
 183+ SMWSQLHelpers::reportProgress(" ... creating column $name ... ",$reportTo);
 184+ $db->query("ALTER TABLE $table ADD `$name` $type $position", $fname);
 185+ $result[$name] = 'new';
 186+ SMWSQLHelpers::reportProgress("done \n",$reportTo);
 187+ } elseif ($curfields[$name] != $type) {
 188+ SMWSQLHelpers::reportProgress(" ... changing type of column $name from '$curfields[$name]' to '$type' ... ",$reportTo);
 189+ $db->query("ALTER TABLE $table CHANGE `$name` `$name` $type $position", $fname);
 190+ $result[$name] = 'up';
 191+ $curfields[$name] = false;
 192+ SMWSQLHelpers::reportProgress("done.\n",$reportTo);
 193+ } else {
 194+ SMWSQLHelpers::reportProgress(" ... column $name is fine\n",$reportTo);
 195+ $curfields[$name] = false;
 196+ }
 197+ $position = "AFTER $name";
 198+ }
 199+ foreach ($curfields as $name => $value) {
 200+ if ($value !== false) { // not encountered yet --> delete
 201+ SMWSQLHelpers::reportProgress(" ... deleting obsolete column $name ... ",$reportTo);
 202+ $db->query("ALTER TABLE $table DROP COLUMN `$name`", $fname);
 203+ $result[$name] = 'del';
 204+ SMWSQLHelpers::reportProgress("done.\n",$reportTo);
 205+ }
 206+ }
 207+ }
 208+
 209+ SMWSQLHelpers::reportProgress(" ... table $table set up successfully.\n",$reportTo);
 210+ return $result;
 211+ }
 212+ }
 213+
 214+ /**
 215+ * Make sure that each of the column descriptions in the given array is indexed by *one* index
 216+ * in the given DB table.
 217+ */
 218+ public static function setupIndex($table, $columns, $db, $reportTo = NULL) {
 219+ global $wgDBtype,$verbose;
 220+ $table = $db->tableName($table);
 221+ $fname = 'SMWSQLHelpers::setupIndex';
 222+
 223+ if ($wgDBtype=='postgres') { // postgresql
 224+ $sql= "SELECT i.relname AS indexname,"
 225+ . " pg_get_indexdef(i.oid) AS indexdef, "
 226+ . " replace(substring(pg_get_indexdef(i.oid) from '\\\\((.*)\\\\)'),' ','') AS indexcolumns"
 227+ . " FROM pg_index x"
 228+ . " JOIN pg_class c ON c.oid = x.indrelid"
 229+ ." JOIN pg_class i ON i.oid = x.indexrelid"
 230+ ." LEFT JOIN pg_namespace n ON n.oid = c.relnamespace"
 231+ ." LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace"
 232+ ." WHERE c.relkind = 'r'::\"char\" AND i.relkind = 'i'::\"char\""
 233+ ." AND c.relname = '" . $table . "'"
 234+ ." AND NOT pg_get_indexdef(i.oid) ~ '^CREATE UNIQUE INDEX'";
 235+ $res = $db->query($sql,$fname);
 236+ if ( !$res ) {
 237+ return false;
 238+ }
 239+ $indexes = array();
 240+ while ( $row = $db->fetchObject( $res ) ) {
 241+ // remove unneeded indexes, let indexes alone that already exist in the correct fashion
 242+ if (array_key_exists($row->indexcolumns,$columns)) {
 243+ $columns[$row->indexcolumns]=false;
 244+ } else {
 245+ $db->query('DROP INDEX IF EXISTS ' . $row->indexname, $fname);
 246+ }
 247+ }
 248+ foreach ($columns as $key => $column) { // add remaining indexes
 249+ if ($column != false) {
 250+ $db->query("CREATE INDEX " . $table . "_index" . $key . " ON " . $table . " USING btree(" . $column . ")", $fname);
 251+ }
 252+ }
 253+ } else { // mysql
 254+ $res = $db->query( 'SHOW INDEX FROM ' . $table , $fname);
 255+ if ( !$res ) {
 256+ return false;
 257+ }
 258+ $indexes = array();
 259+ while ( $row = $db->fetchObject( $res ) ) {
 260+ if (!array_key_exists($row->Key_name, $indexes)) {
 261+ $indexes[$row->Key_name] = array();
 262+ }
 263+ $indexes[$row->Key_name][$row->Seq_in_index] = $row->Column_name;
 264+ }
 265+ foreach ($indexes as $key => $index) { // clean up existing indexes
 266+ $id = array_search(implode(',', $index), $columns );
 267+ if ( $id !== false ) {
 268+ $columns[$id] = false;
 269+ } else { // duplicate or unrequired index
 270+ $db->query( 'DROP INDEX ' . $key . ' ON ' . $table, $fname);
 271+ }
 272+ }
 273+
 274+ foreach ($columns as $key => $column) { // add remaining indexes
 275+ if ($column != false) {
 276+ $db->query( "ALTER TABLE $table ADD INDEX ( $column )", $fname);
 277+ }
 278+ }
 279+ }
 280+ return true;
 281+ }
 282+
 283+ /// If a receiver is given, report the given message to its reportProgress method.
 284+ protected static function reportProgress($msg, $receiver = NULL) {
 285+ if ($receiver !== NULL) $receiver->reportProgress($msg);
 286+ }
 287+
 288+}
\ No newline at end of file
Property changes on: trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php
___________________________________________________________________
Name: svn:eol-style
1289 + native
Index: trunk/extensions/SemanticMediaWiki/includes/SMW_GlobalFunctions.php
——@@ -14,7 +14,7 @@
1515 * @defgroup SMW Semantic MediaWiki
1616 */
1717
18 -define('SMW_VERSION','1.5c-SVN');
 18+define('SMW_VERSION','1.5d-SVN');
1919
2020 // constants for displaying the factbox
2121 define('SMW_FACTBOX_HIDDEN', 1);
——@@ -169,6 +169,7 @@
170170 $wgAutoloadClasses['SMWDisjunction'] = $smwgIP . '/includes/storage/SMW_Description.php';
171171 $wgAutoloadClasses['SMWSomeProperty'] = $smwgIP . '/includes/storage/SMW_Description.php';
172172 $wgAutoloadClasses['SMWSQLStore2'] = $smwgIP . '/includes/storage/SMW_SQLStore2.php';
 173+ $wgAutoloadClasses['SMWSQLHelpers'] = $smwgIP . '/includes/storage/SMW_SQLHelpers.php';
173174 // Do not autoload RAPStore, since some special pages load all autoloaded classes, which causes
174175 // troubles with RAP store if RAP is not installed (require_once fails).
175176 //$wgAutoloadClasses['SMWRAPStore'] = $smwgIP . '/includes/storage/SMW_RAPStore.php';

Status & tagging log