r47088 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r47087‎ | r47088 | r47089 >
Date:15:23, 10 February 2009
Author:mkroetzsch
Status:deferred
Tags:
Comment:
integrated patch for increased PostgreSQL compatibility by Marcel Gsteiger with slight modifications
Modified paths:
  • /trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2_Queries.php (modified) (history)

Diff [purge]

Index: trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2_Queries.php
——@@ -77,7 +77,7 @@
7878 * @param $concept Title
7979 */
8080 public function refreshConceptCache($concept) {
81 - global $smwgQMaxLimit, $smwgQConceptFeatures;
 81+ global $smwgQMaxLimit, $smwgQConceptFeatures, $wgDBtype;
8282 $cid = $this->m_store->getSMWPageID($concept->getDBKey(), SMW_NS_CONCEPT, '');
8383 $cid_c = $this->m_store->getSMWPageID($concept->getDBKey(), SMW_NS_CONCEPT, '', false);
8484 if ($cid != $cid_c) {
——@@ -106,10 +106,18 @@
107107 }
108108 // Update database:
109109 $this->m_dbs->delete('smw_conccache', array('o_id' => $cid), 'SMW::refreshConceptCache');
110 - $this->m_dbs->query("INSERT IGNORE INTO " . $this->m_dbs->tableName('smw_conccache') .
 110+ if ($wgDBtype=='postgres') { // PostgresQL: no INSERT IGNORE, check for duplicates explicitly
 111+ $where = $qobj->where . ($qobj->where?' AND ':'') .
 112+ 'NOT EXISTS (SELECT NULL FROM ' . $this->m_dbs->tableName('smw_conccache') .
 113+ ' WHERE ' . $this->m_dbs->tablename('smw_conccache') . '.s_id = ' . $qobj->alias . '.s_id ' .
 114+ ' AND ' . $this->m_dbs->tablename('smw_conccache') . '.o_id = ' . $qobj->alias . '.o_id )';
 115+ } else { // MySQL just uses INSERT IGNORE, no extra conditions
 116+ $where = $qobj->where;
 117+ }
 118+ $this->m_dbs->query("INSERT " . (($wgDBtype=='postgres')?"":"IGNORE ") . "INTO " . $this->m_dbs->tableName('smw_conccache') .
111119 " SELECT DISTINCT $qobj->joinfield AS s_id, $cid AS o_id FROM " .
112120 $this->m_dbs->tableName($qobj->jointable) . " AS $qobj->alias" . $qobj->from .
113 - ($qobj->where?" WHERE ":'') . $qobj->where . " LIMIT $smwgQMaxLimit",
 121+ ($where?" WHERE ":'') . $where . " LIMIT $smwgQMaxLimit",
114122 'SMW::refreshConceptCache');
115123 $this->m_dbs->update('smw_conc2', array('cache_date' => strtotime("now"), 'cache_count' => $this->m_dbs->affectedRows()), array('s_id' => $cid), 'SMW::refreshConceptCache');
116124 } else { // just delete old data if there is any
——@@ -263,7 +271,10 @@
264272 return $result;
265273 }
266274 $sql_options = $this->getSQLOptions($query,$rootid);
267 - $res = $this->m_dbs->select($this->m_dbs->tableName($qobj->jointable) . " AS $qobj->alias" . $qobj->from, "DISTINCT $qobj->alias.smw_id AS id,$qobj->alias.smw_title AS t,$qobj->alias.smw_namespace AS ns,$qobj->alias.smw_iw AS iw", $qobj->where, 'SMW::getQueryResult', $sql_options);
 275+ $sortfields = implode($qobj->sortfields,','); // also select those, required in standard SQL (though MySQL is quite about it)
 276+ $res = $this->m_dbs->select($this->m_dbs->tableName($qobj->jointable) . " AS $qobj->alias" . $qobj->from,
 277+ "DISTINCT $qobj->alias.smw_id AS id,$qobj->alias.smw_title AS t,$qobj->alias.smw_namespace AS ns,$qobj->alias.smw_iw AS iw"
 278+ . ($sortfields?',':'') . $sortfields, $qobj->where, 'SMW::getQueryResult', $sql_options);
268279
269280 $qr = array();
270281 $count = 0;
——@@ -592,6 +603,7 @@
593604 * the actual result.
594605 */
595606 protected function executeQueries(SMWSQLStore2Query &$query) {
 607+ global $wgDBtype;
596608 switch ($query->type) {
597609 case SMW_SQL2_TABLE: // normal query with conjunctive subcondition
598610 foreach ($query->components as $qid => $joinfield) {
——@@ -656,8 +668,7 @@
657669 break;
658670 case SMW_SQL2_DISJUNCTION:
659671 if ($this->m_qmode !== SMWQuery::MODE_DEBUG) {
660 - $this->m_dbs->query( "CREATE TEMPORARY TABLE " . $this->m_dbs->tableName($query->alias) .
661 - ' ( id INT UNSIGNED KEY ) TYPE=MEMORY', 'SMW::executeQueries' );
 672+ $this->m_dbs->query($this->getCreateTempIDTableSQL($this->m_dbs->tableName($query->alias)), 'SMW::executeQueries');
662673 }
663674 $this->m_querylog[$query->alias] = array();
664675 foreach ($query->components as $qid => $joinfield) {
——@@ -665,8 +676,10 @@
666677 $this->executeQueries($subquery);
667678 $sql = '';
668679 if ($subquery->jointable != '') {
669 - $sql = "INSERT IGNORE INTO " . $this->m_dbs->tableName($query->alias) . " SELECT $subquery->joinfield FROM " .
670 - $this->m_dbs->tableName($subquery->jointable) . " AS $subquery->alias $subquery->from" . ($subquery->where?" WHERE $subquery->where":'');
 680+ $sql = 'INSERT ' . (($wgDBtype=='postgres')?'':'IGNORE ') . 'INTO ' .
 681+ $this->m_dbs->tableName($query->alias) .
 682+ " SELECT $subquery->joinfield FROM " . $this->m_dbs->tableName($subquery->jointable) .
 683+ " AS $subquery->alias $subquery->from" . ($subquery->where?" WHERE $subquery->where":'');
671684 } elseif ($subquery->joinfield !== '') {
672685 /// NOTE: this works only for single "unconditional" values without further
673686 /// WHERE or FROM. The execution must take care of not creating any others.
——@@ -674,7 +687,7 @@
675688 foreach ($subquery->joinfield as $value) {
676689 $values .= ($values?',':'') . '(' . $this->m_dbs->addQuotes($value) . ')';
677690 }
678 - $sql = "INSERT IGNORE INTO " . $this->m_dbs->tableName($query->alias) . " (id) VALUES $values";
 691+ $sql = 'INSERT ' . (($wgDBtype=='postgres')?'':'IGNORE ') . 'INTO ' . $this->m_dbs->tableName($query->alias) . " (id) VALUES $values";
679692 } // else: // interpret empty joinfields as impossible condition (empty result), ignore
680693 if ($sql) {
681694 $this->m_querylog[$query->alias][] = $sql;
——@@ -700,6 +713,7 @@
701714 * and temporary tables are used in many cases.
702715 */
703716 protected function executeHierarchyQuery(&$query) {
 717+ global $wgDBtype;
704718 $fname = "SMWSQLStore2Queries::executeQueries-hierarchy-$query->type (SMW)";
705719 wfProfileIn($fname);
706720 global $smwgQSubpropertyDepth, $smwgQSubcategoryDepth;
——@@ -733,13 +747,12 @@
734748 wfProfileOut($fname);
735749 return; // no real queries in debug mode
736750 }
 751+ $this->m_dbs->query($this->getCreateTempIDTableSQL($tablename), 'SMW::executeHierarchyQuery');
737752
738 - $this->m_dbs->query( "CREATE TEMPORARY TABLE $tablename " .
739 - '( id INT UNSIGNED NOT NULL KEY) TYPE=MEMORY', 'SMW::executeQueries' );
740753 if (array_key_exists($values, $this->m_hierarchies)) { // just copy known result
741754 $this->m_dbs->query("INSERT INTO $tablename (id) SELECT id" .
742755 ' FROM ' . $this->m_hierarchies[$values],
743 - 'SMW::executeQueries');
 756+ 'SMW::executeHierarchyQuery');
744757 wfProfileOut($fname);
745758 return;
746759 }
——@@ -750,33 +763,31 @@
751764 /// obtained in the previous step are relevant. So this is a performance measure.
752765 $tmpnew = 'smw_new';
753766 $tmpres = 'smw_res';
754 - $this->m_dbs->query( "CREATE TEMPORARY TABLE $tmpnew " .
755 - '( id INT UNSIGNED ) TYPE=MEMORY', 'SMW::executeQueries' );
756 - $this->m_dbs->query( "CREATE TEMPORARY TABLE $tmpres " .
757 - '( id INT UNSIGNED ) TYPE=MEMORY', 'SMW::executeQueries' );
 767+ $this->m_dbs->query($this->getCreateTempIDTableSQL($tmpnew), 'SMW::executeQueries');
 768+ $this->m_dbs->query($this->getCreateTempIDTableSQL($tmpres), 'SMW::executeQueries');
 769+ $this->m_dbs->query("INSERT " . (($wgDBtype=='postgres')?"":"IGNORE") . " INTO $tablename (id) VALUES $values", 'SMW::executeHierarchyQuery');
 770+ $this->m_dbs->query("INSERT " . (($wgDBtype=='postgres')?"":"IGNORE") . " INTO $tmpnew (id) VALUES $values", 'SMW::executeHierarchyQuery');
758771
759 - $this->m_dbs->query("INSERT IGNORE INTO $tablename (id) VALUES $values", 'SMW::executeQueries');
760 - $this->m_dbs->query("INSERT IGNORE INTO $tmpnew (id) VALUES $values", 'SMW::executeQueries');
761 -
762772 for ($i=0; $i<$depth; $i++) {
763 - $this->m_dbs->query("INSERT INTO $tmpres (id) SELECT s_id FROM $smw_subs2, $tmpnew WHERE o_id=id",
764 - 'SMW::executeQueries');
 773+ $this->m_dbs->query("INSERT INTO $tmpres (id) SELECT s_id" . ($wgDBtype=='postgres'?'::integer':'') . " FROM $smw_subs2, $tmpnew WHERE o_id=id",
 774+ 'SMW::executeHierarchyQuery');
765775 if ($this->m_dbs->affectedRows() == 0) { // no change, exit loop
766776 break;
767777 }
768 - $this->m_dbs->query("INSERT IGNORE INTO $tablename (id) SELECT $tmpres.id FROM $tmpres",
769 - 'SMW::executeQueries');
 778+ $this->m_dbs->query("INSERT " . (($wgDBtype=='postgres')?'':'IGNORE ') . "INTO $tablename (id) SELECT $tmpres.id FROM $tmpres",
 779+ 'SMW::executeHierarchyQuery');
770780 if ($this->m_dbs->affectedRows() == 0) { // no change, exit loop
771781 break;
772782 }
773 - $this->m_dbs->query('TRUNCATE TABLE ' . $tmpnew, 'SMW::executeQueries'); // empty "new" table
 783+ /// TODO Why does PostgreSQL CASCADE here?
 784+ $this->m_dbs->query('TRUNCATE TABLE ' . $tmpnew . (($wgDBtype=='postgres')?' CASCADE':'') , 'SMW::executeHierarchyQuery'); // empty "new" table
774785 $tmpname = $tmpnew;
775786 $tmpnew = $tmpres;
776787 $tmpres = $tmpname;
777788 }
778789 $this->m_hierarchies[$values] = $tablename;
779 - $this->m_dbs->query('DROP TEMPORARY TABLE smw_new', 'SMW::executeQueries');
780 - $this->m_dbs->query('DROP TEMPORARY TABLE smw_res', 'SMW::executeQueries');
 790+ $this->m_dbs->query((($wgDBtype=='postgres')?'DROP TABLE IF EXISTS smw_new':'DROP TEMPORARY TABLE smw_new'), 'SMW::executeHierarchyQuery');
 791+ $this->m_dbs->query((($wgDBtype=='postgres')?'DROP TABLE IF EXISTS smw_res':'DROP TEMPORARY TABLE smw_res'), 'SMW::executeHierarchyQuery');
781792 wfProfileOut($fname);
782793 }
783794
——@@ -849,13 +860,44 @@
850861
851862 /**
852863 * After querying, make sure no temporary database tables are left.
 864+ * Postgres will eventually clean up everything at the end of the transaction (" ON COMMIT DROP ").
853865 */
854866 protected function cleanUp() {
 867+ global $wgDBtype;
855868 if ($this->m_qmode !== SMWQuery::MODE_DEBUG) {
856869 foreach ($this->m_querylog as $table => $log) {
857 - $this->m_dbs->query("DROP TEMPORARY TABLE " . $this->m_dbs->tableName($table), 'SMW::getQueryResult');
 870+ $this->m_dbs->query((($wgDBtype=='postgres')?"DROP TABLE IF EXISTS ":"DROP TEMPORARY TABLE ") . $this->m_dbs->tableName($table), 'SMW::getQueryResult');
858871 }
859872 }
860873 }
861874
 875+ /**
 876+ * Get SQL code suitable to create a temporary table of the given name, used to store ids.
 877+ * MySQL can do that simply by creating new temporary tables. PostgreSQL first checks if such
 878+ * a table exists. Also, PostgreSQL tables will use a RULE to achieve built-in duplicate
 879+ * elimination. The latter is done using INSERT IGNORE in MySQL.
 880+ * @todo Should the check for table existence in PostgreSQL be otptional? Is it really needed?
 881+ */
 882+ protected function getCreateTempIDTableSQL($tablename) {
 883+ global $wgDBtype;
 884+ if ($wgDBtype=='postgres') { // PostgreSQL: no memory tables, use RULE to emulate INSERT IGNORE
 885+ return "CREATE OR REPLACE FUNCTION create_" . $tablename . "() RETURNS void AS "
 886+ ."$$ "
 887+ ."BEGIN "
 888+ ." IF EXISTS(SELECT NULL FROM pg_tables WHERE tablename='" . $tablename . "' AND schemaname = ANY (current_schemas(true))) "
 889+ ." THEN DELETE FROM " . $tablename ."; "
 890+ ." ELSE "
 891+ ." CREATE TEMPORARY TABLE " . $tablename . " (id INTEGER PRIMARY KEY); "
 892+ ." CREATE RULE " . $tablename . "_ignore AS ON INSERT TO " . $tablename . " WHERE (EXISTS (SELECT NULL FROM " . $tablename
 893+ ." WHERE (" . $tablename . ".id = new.id))) DO INSTEAD NOTHING; "
 894+ ." END IF; "
 895+ ."END; "
 896+ ."$$ "
 897+ ."LANGUAGE 'plpgsql'; "
 898+ ."SELECT create_" . $tablename . "(); ";
 899+ } else { // MySQL_ just a temporary table, use INSERT IGNORE later
 900+ return "CREATE TEMPORARY TABLE " . $tablename . "( id INT UNSIGNED KEY ) TYPE=MEMORY";
 901+ }
 902+ }
 903+
862904 }

Status & tagging log