| Index: trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore.php |
| — | — | @@ -695,78 +695,54 @@ |
| 696 | 696 | extract( $db->tableNames('smw_relations','smw_attributes','smw_longstrings','smw_specialprops') ); |
| 697 | 697 | |
| 698 | 698 | /// DEBUG |
| 699 | | -// $this->setupTable('smw_test', |
| | 699 | +// $this->setupTable('smw_test2', |
| 700 | 700 | // array('subject_id' => 'INT(8) UNSIGNED NOT NULL', |
| 701 | 701 | // 'subject_namespace' => 'INT(11) NOT NULL', |
| 702 | | -// 'subject_title' => 'VARCHAR(255) NOT NULL', |
| 703 | | -// 'nullvalue' => 'VARCHAR(255)', |
| 704 | | -// 'value_unit' => 'VARCHAR(63)'), $db, $verbose); |
| | 702 | +// 'subject_title' => 'VARCHAR(245) NOT NULL', |
| | 703 | +// //'nullvalue' => 'VARCHAR(255)', |
| | 704 | +// 'value_unit2' => 'VARCHAR(64)', |
| | 705 | +// 'value_unit' => 'VARCHAR(61) NOT NULL'), $db, $verbose); |
| 705 | 706 | |
| 706 | 707 | // create relation table |
| 707 | | - if ($db->tableExists($smw_relations) === false) { |
| 708 | | - $sql = 'CREATE TABLE ' . $wgDBname . '.' . $smw_relations . ' |
| 709 | | - ( subject_id INT(8) UNSIGNED NOT NULL, |
| 710 | | - subject_namespace INT(11) NOT NULL, |
| 711 | | - subject_title VARCHAR(255) NOT NULL, |
| 712 | | - relation_title VARCHAR(255) NOT NULL, |
| 713 | | - object_namespace INT(11) NOT NULL, |
| 714 | | - object_title VARCHAR(255) NOT NULL |
| 715 | | - ) TYPE=innodb'; |
| 716 | | - $res = $db->query( $sql, $fname ); |
| 717 | | - } else { |
| 718 | | - |
| 719 | | - } |
| 720 | | - |
| | 708 | + $this->setupTable($smw_relations, |
| | 709 | + array('subject_id' => 'INT(8) UNSIGNED NOT NULL', |
| | 710 | + 'subject_namespace' => 'INT(11) NOT NULL', |
| | 711 | + 'subject_title' => 'VARCHAR(255) NOT NULL', |
| | 712 | + 'relation_title' => 'VARCHAR(255) NOT NULL', |
| | 713 | + 'object_namespace' => 'INT(11) NOT NULL', |
| | 714 | + 'object_title' => 'VARCHAR(255) NOT NULL'), $db, $verbose); |
| 721 | 715 | $this->setupIndex($smw_relations, array('subject_id','relation_title','object_title,object_namespace'), $db); |
| 722 | | - $this->reportProgress("Relation table set up successfully.\n",$verbose); |
| 723 | 716 | |
| 724 | 717 | // create attribute table |
| 725 | | - if ($db->tableExists($smw_attributes) === false) { |
| 726 | | - $sql = 'CREATE TABLE ' . $wgDBname . '.' . $smw_attributes . ' |
| 727 | | - ( subject_id INT(8) UNSIGNED NOT NULL, |
| 728 | | - subject_namespace INT(11) NOT NULL, |
| 729 | | - subject_title VARCHAR(255) NOT NULL, |
| 730 | | - attribute_title VARCHAR(255) NOT NULL, |
| 731 | | - value_unit VARCHAR(63), |
| 732 | | - value_datatype VARCHAR(31) NOT NULL, |
| 733 | | - value_xsd VARCHAR(255) NOT NULL, |
| 734 | | - value_num DOUBLE |
| 735 | | - ) TYPE=innodb'; /// TODO: remove value_datatype column completely |
| 736 | | - $res = $db->query( $sql, $fname ); |
| 737 | | - } |
| 738 | | - |
| | 718 | + $this->setupTable($smw_attributes, |
| | 719 | + array('subject_id' => 'INT(8) UNSIGNED NOT NULL', |
| | 720 | + 'subject_namespace' => 'INT(11) NOT NULL', |
| | 721 | + 'subject_title' => 'VARCHAR(255) NOT NULL', |
| | 722 | + 'attribute_title' => 'VARCHAR(255) NOT NULL', |
| | 723 | + 'value_unit' => 'VARCHAR(63)', |
| | 724 | + 'value_datatype' => 'VARCHAR(31) NOT NULL', /// TODO: remove value_datatype column |
| | 725 | + 'value_xsd' => 'VARCHAR(255) NOT NULL', |
| | 726 | + 'value_num' => 'DOUBLE'), $db, $verbose); |
| 739 | 727 | $this->setupIndex($smw_attributes, array('subject_id','attribute_title','value_num','value_xsd'), $db); |
| 740 | | - $this->reportProgress("Attribute table set up successfully.\n",$verbose); |
| 741 | 728 | |
| 742 | 729 | // create table for long string attributes |
| 743 | | - if ($db->tableExists($smw_longstrings) === false) { |
| 744 | | - $sql = 'CREATE TABLE ' . $wgDBname . '.' . $smw_longstrings . ' |
| 745 | | - ( subject_id INT(8) UNSIGNED NOT NULL, |
| 746 | | - subject_namespace INT(11) NOT NULL, |
| 747 | | - subject_title VARCHAR(255) NOT NULL, |
| 748 | | - attribute_title VARCHAR(255) NOT NULL, |
| 749 | | - value_blob MEDIUMBLOB |
| 750 | | - ) TYPE=innodb'; |
| 751 | | - $res = $db->query( $sql, $fname ); |
| 752 | | - } |
| 753 | | - |
| | 730 | + $this->setupTable($smw_longstrings, |
| | 731 | + array('subject_id' => 'INT(8) UNSIGNED NOT NULL', |
| | 732 | + 'subject_namespace' => 'INT(11) NOT NULL', |
| | 733 | + 'subject_title' => 'VARCHAR(255) NOT NULL', |
| | 734 | + 'attribute_title' => 'VARCHAR(255) NOT NULL', |
| | 735 | + 'value_blob' => 'MEDIUMBLOB'), $db, $verbose); |
| 754 | 736 | $this->setupIndex($smw_longstrings, array('subject_id','attribute_title'), $db); |
| 755 | | - $this->reportProgress("Table for long string values (Type:Text) set up successfully.\n",$verbose); |
| 756 | 737 | |
| 757 | 738 | // create table for special properties |
| 758 | | - if ($db->tableExists($smw_specialprops) === false) { |
| 759 | | - $sql = 'CREATE TABLE ' . $wgDBname . '.' . $smw_specialprops . ' |
| 760 | | - ( subject_id INT(8) UNSIGNED NOT NULL, |
| 761 | | - subject_namespace INT(11) NOT NULL, |
| 762 | | - subject_title VARCHAR(255) NOT NULL, |
| 763 | | - property_id SMALLINT NOT NULL, |
| 764 | | - value_string VARCHAR(255) NOT NULL |
| 765 | | - ) TYPE=innodb'; /// TODO: remove subject_namespace and subject_title columns completely |
| 766 | | - $res = $db->query( $sql, $fname ); |
| 767 | | - } |
| | 739 | + $this->setupTable($smw_specialprops, |
| | 740 | + array('subject_id' => 'INT(8) UNSIGNED NOT NULL', |
| | 741 | + 'subject_namespace' => 'INT(11) NOT NULL', |
| | 742 | + 'subject_title' => 'VARCHAR(255) NOT NULL', |
| | 743 | + 'property_id' => 'SMALLINT(6) NOT NULL', |
| | 744 | + 'value_string' => 'VARCHAR(255) NOT NULL'), $db, $verbose); |
| 768 | 745 | $this->setupIndex($smw_specialprops, array('subject_id', 'property_id'), $db); |
| 769 | | - $this->reportProgress("Table for special properties set up successfully.\n",$verbose); |
| 770 | | - |
| | 746 | + |
| 771 | 747 | $this->reportProgress("Database initialised successfully.\n",$verbose); |
| 772 | 748 | return true; |
| 773 | 749 | } |
| — | — | @@ -1045,7 +1021,7 @@ |
| 1046 | 1022 | protected function createSQLQuery(SMWDescription $description, &$from, &$where, &$db, &$curtables, $sort = false) { |
| 1047 | 1023 | $subwhere = ''; |
| 1048 | 1024 | if ($description instanceof SMWThingDescription) { |
| 1049 | | - // nothin to check |
| | 1025 | + // nothing to check |
| 1050 | 1026 | } elseif ($description instanceof SMWClassDescription) { |
| 1051 | 1027 | if ($this->addInnerJoin('CATS', $from, $db, $curtables)) { |
| 1052 | 1028 | global $smwgIQSubcategoryInclusions; |
| — | — | @@ -1075,8 +1051,6 @@ |
| 1076 | 1052 | $page->getNamespace(); |
| 1077 | 1053 | if ( $smwgIQRedirectNormalization && ($this->addInnerJoin('REDIRECT', $from, $db, $curtables)) ) { |
| 1078 | 1054 | $cond = '(' . $cond . ') OR (' . |
| 1079 | | -// $curtables['REDIRECT'] . '.rd_from=' . |
| 1080 | | -// $curtables['PAGE'] . '.page_id AND ' . |
| 1081 | 1055 | $curtables['REDIRECT'] . '.rd_title=' . |
| 1082 | 1056 | $db->addQuotes($page->getDBKey()) . ' AND ' . |
| 1083 | 1057 | $curtables['REDIRECT'] . '.rd_namespace=' . |
| — | — | @@ -1123,7 +1097,7 @@ |
| 1124 | 1098 | /// TODO: this is not optimal -- we drop more table aliases than needed, but its hard to find out what is feasible in recursive calls ... |
| 1125 | 1099 | $nexttables = array(); |
| 1126 | 1100 | // pull in page to prevent every child description pulling it seperately! |
| 1127 | | - /// TODO: willl be obsolete when PREVREL provides page indices |
| | 1101 | + /// TODO: will be obsolete when PREVREL provides page indices |
| 1128 | 1102 | if ($this->addInnerJoin('PAGE', $from, $db, $curtables)) { |
| 1129 | 1103 | $nexttables['PAGE'] = $curtables['PAGE']; |
| 1130 | 1104 | } |
| — | — | @@ -1193,9 +1167,19 @@ |
| 1194 | 1168 | * Make sure the table of the given name has the given fields, provided |
| 1195 | 1169 | * as an array with entries fieldname => typeparams. typeparams should be |
| 1196 | 1170 | * in a normalised form and order to match to existing values. |
| | 1171 | + * |
| | 1172 | + * The function returns an array that includes all columns that have been |
| | 1173 | + * changed. For each such column, the array contains an entry |
| | 1174 | + * columnname => action, where action is one of 'up', 'new', or 'del' |
| | 1175 | + * If the table was already fine or was created completely anew, an empty |
| | 1176 | + * array is returned (assuming that both cases require no action). |
| | 1177 | + * |
| | 1178 | + * NOTE: the function partly ignores the order in which fields are set up. |
| | 1179 | + * Only if the type of some field changes will its order be adjusted explicitly. |
| 1197 | 1180 | */ |
| 1198 | 1181 | protected function setupTable($table, $fields, $db, $verbose) { |
| 1199 | 1182 | global $wgDBname; |
| | 1183 | + $this->reportProgress("Setting up table $table ...\n",$verbose); |
| 1200 | 1184 | if ($db->tableExists($table) === false) { // create new table |
| 1201 | 1185 | $sql = 'CREATE TABLE ' . $wgDBname . '.' . $table . ' ('; |
| 1202 | 1186 | $first = true; |
| — | — | @@ -1209,9 +1193,13 @@ |
| 1210 | 1194 | } |
| 1211 | 1195 | $sql .= ') TYPE=innodb'; |
| 1212 | 1196 | $db->query( $sql, 'SMWSQLStore::setupTable' ); |
| | 1197 | + $this->reportProgress(" ... new table created\n",$verbose); |
| | 1198 | + return array(); |
| 1213 | 1199 | } else { // check table signature |
| | 1200 | + $this->reportProgress(" ... table exists already, checking structure ...\n",$verbose); |
| 1214 | 1201 | $res = $db->query( 'DESCRIBE ' . $table, 'SMWSQLStore::setupTable' ); |
| 1215 | 1202 | $curfields = array(); |
| | 1203 | + $result = array(); |
| 1216 | 1204 | while ($row = $db->fetchObject($res)) { |
| 1217 | 1205 | $type = strtoupper($row->Type); |
| 1218 | 1206 | if ($row->Null != 'YES') { |
| — | — | @@ -1219,15 +1207,35 @@ |
| 1220 | 1208 | } |
| 1221 | 1209 | $curfields[$row->Field] = $type; |
| 1222 | 1210 | } |
| | 1211 | + $position = 'FIRST'; |
| 1223 | 1212 | foreach ($fields as $name => $type) { |
| 1224 | 1213 | if ( !array_key_exists($name,$curfields) ) { |
| 1225 | | - $this->reportProgress("Field $name not existing yet.<br />",$verbose); |
| | 1214 | + $this->reportProgress(" ... creating column $name ... ",$verbose); |
| | 1215 | + $db->query("ALTER TABLE $table ADD `$name` $type $position", 'SMWSQLStore::setupTable'); |
| | 1216 | + $result[$name] = 'new'; |
| | 1217 | + $this->reportProgress("done \n",$verbose); |
| 1226 | 1218 | } elseif ($curfields[$name] != $type) { |
| 1227 | | - $this->reportProgress("Field $name has wrong type (should be '$type', but is '$curfields[$name]').<br />",$verbose); |
| | 1219 | + $this->reportProgress(" ... changing type of column $name from '$curfields[$name]' to '$type' ... ",$verbose); |
| | 1220 | + $db->query("ALTER TABLE $table CHANGE `$name` `$name` $type $position", 'SMWSQLStore::setupTable'); |
| | 1221 | + $result[$name] = 'up'; |
| | 1222 | + $curfields[$name] = false; |
| | 1223 | + $this->reportProgress("done.\n",$verbose); |
| 1228 | 1224 | } else { |
| 1229 | | - $this->reportProgress("Field $name is fine.<br />",$verbose); |
| | 1225 | + $this->reportProgress(" ... column $name is fine\n",$verbose); |
| | 1226 | + $curfields[$name] = false; |
| 1230 | 1227 | } |
| | 1228 | + $position = "AFTER $name"; |
| 1231 | 1229 | } |
| | 1230 | + foreach ($curfields as $name => $value) { |
| | 1231 | + if ($value !== false) { // not encountered yet --> delete |
| | 1232 | + $this->reportProgress(" ... deleting obsolete column $name ... ",$verbose); |
| | 1233 | + $db->query("ALTER TABLE $table DROP COLUMN `$name`", 'SMWSQLStore::setupTable'); |
| | 1234 | + $result[$name] = 'del'; |
| | 1235 | + $this->reportProgress("done.\n",$verbose); |
| | 1236 | + } |
| | 1237 | + } |
| | 1238 | + $this->reportProgress(" ... table $table set up successfully.\n",$verbose); |
| | 1239 | + return $result; |
| 1232 | 1240 | } |
| 1233 | 1241 | } |
| 1234 | 1242 | |