r90358 MediaWiki - Code Review archive

Repository:	MediaWiki
Revision:	< r90357‎ \| r90358 \| r90359 >
Date:	19:51, 18 June 2011
Author:	jeroendedauw
Status:	deferred (Comments)
Tags:
Comment:	applying modified SQLite patch by hofoen
Modified paths:	/trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php (modified) (history) /trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php (modified) (history)

Diff [purge]

Index: trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php
—	—	@@ -977,7 +977,7 @@
978	978	} // else: properties with special tables are ignored for now; maybe fix in the future
979	979	}
980	980
981		~~- $query = '(' . implode( ') UNION (', $queries ) . ') ORDER BY smw_sortkey';~~
	981	+ $query = 'SELECT * FROM (' . implode( ') UNION (', $queries ) . ') ORDER BY smw_sortkey';
982	982	// The following line is possible in MW 1.6 and above only:
983	983	// $query = $db->unionQueries($queries, false) . ' ORDER BY smw_sortkey'; // should probably use $db->makeSelectOptions()
984	984	if ( $requestoptions !== null ) {
—	—	@@ -1028,14 +1028,21 @@
1029	1029	. "$$ "
1030	1030	. "LANGUAGE 'plpgsql'; "
1031	1031	. "SELECT create_" . $smw_tmp_unusedprops . "(); ";
	1032	+ } elseif ( $wgDBtype == 'sqlite' ) { // SQLite: no in-memory tables available
	1033	+ $sql = "CREATE TEMPORARY TABLE " . $smw_tmp_unusedprops . "( title VARCHAR(255) )";
1032	1034	} else { // MySQL: use temporary in-memory table
1033	1035	$sql = "CREATE TEMPORARY TABLE " . $smw_tmp_unusedprops . "( title VARCHAR(255) ) ENGINE=MEMORY";
1034	1036	}
1035	1037
1036	1038	$db->query( $sql, $fname );
1037	1039
1038		~~- $db->insertSelect( $smw_tmp_unusedprops, 'page', array( 'title' => 'page_title' ),~~
1039		~~- array( "page_namespace" => SMW_NS_PROPERTY ), $fname );~~
	1040	+ $db->insertSelect(
	1041	+ $smw_tmp_unusedprops,
	1042	+ 'page',
	1043	+ array( 'title' => 'page_title' ),
	1044	+ array( 'page_namespace' => SMW_NS_PROPERTY ),
	1045	+ $fname
	1046	+ );
1040	1047
1041	1048	$smw_ids = $db->tableName( 'smw_ids' );
1042	1049
—	—	@@ -1045,8 +1052,19 @@
1046	1053	// all tables occurring in some property table are used:
1047	1054	foreach ( self::getPropertyTables() as $proptable ) {
1048	1055	if ( $proptable->fixedproperty == false ) { // MW does not seem to have a suitable wrapper for this
1049		~~- $db->query( "DELETE FROM $smw_tmp_unusedprops USING $smw_tmp_unusedprops INNER JOIN " . $db->tableName( $proptable->name ) .~~
1050		~~- " INNER JOIN $smw_ids ON p_id=smw_id AND title=smw_title AND smw_iw=" . $db->addQuotes( '' ), $fname );~~
	1056	+ if ( $wgDBtype == 'sqlite' ) { // SQLite
	1057	+ $db->query(
	1058	+ "DELETE FROM $smw_tmp_unusedprops WHERE title IN (SELECT title FROM $smw_tmp_unusedprops INNER JOIN " .
	1059	+ $db->tableName( $proptable->name ) .
	1060	+ " INNER JOIN $smw_ids ON p_id=smw_id AND title=smw_title AND smw_iw=" .
	1061	+ $db->addQuotes( '' ) . ")",
	1062	+ $fname
	1063	+ );
	1064	+ }
	1065	+ else {
	1066	+ $db->query( "DELETE FROM $smw_tmp_unusedprops USING $smw_tmp_unusedprops INNER JOIN " . $db->tableName( $proptable->name ) .
	1067	+ " INNER JOIN $smw_ids ON p_id=smw_id AND title=smw_title AND smw_iw=" . $db->addQuotes( '' ), $fname );
	1068	+ }
1051	1069	} // else: todo
1052	1070	}
1053	1071
—	—	@@ -1056,8 +1074,17 @@
1057	1075	$subPropertyTable = $propertyTables[$subPropertyTableId];
1058	1076
1059	1077	// (again we have no fitting MW wrapper here:)
1060		~~- $db->query( "DELETE $smw_tmp_unusedprops.* FROM $smw_tmp_unusedprops," . $db->tableName( $subPropertyTable->name ) .~~
1061		~~- " INNER JOIN $smw_ids ON o_id=smw_id WHERE title=smw_title", $fname );~~
	1078	+ if ( $wgDBtype == 'sqlite' ) { // SQLite
	1079	+ // TODO
	1080	+ }
	1081	+ else {
	1082	+ $db->query(
	1083	+ "DELETE $smw_tmp_unusedprops.* FROM $smw_tmp_unusedprops," . $db->tableName( $subPropertyTable->name ) .
	1084	+ " INNER JOIN $smw_ids ON o_id=smw_id WHERE title=smw_title",
	1085	+ $fname
	1086	+ );
	1087	+ }
	1088	+
1062	1089	// properties that are redirects are considered to be used:
1063	1090	// (a stricter and more costy approach would be to delete only redirects to used properties;
1064	1091	// this would need to be done with an addtional query in the above loop)
—	—	@@ -1076,7 +1103,10 @@
1077	1104
1078	1105	$db->freeResult( $res );
1079	1106
1080		~~- $db->query( "DROP TEMPORARY table $smw_tmp_unusedprops", $fname );~~
	1107	+ if ( $wgDBtype != 'sqlite' ) {
	1108	+ $db->query( "DROP TEMPORARY table $smw_tmp_unusedprops", $fname );
	1109	+ }
	1110	+
1081	1111	wfProfileOut( "SMWSQLStore2::getUnusedPropertiesSpecial (SMW)" );
1082	1112
1083	1113	return $result;
—	—	@@ -1215,8 +1245,8 @@
1216	1246	't' => SMWSQLHelpers::getStandardDBType( 'title' ),
1217	1247	'l' => SMWSQLHelpers::getStandardDBType( 'blob' ),
1218	1248	'f' => ( $wgDBtype == 'postgres' ? 'DOUBLE PRECISION' : 'DOUBLE' ),
1219		~~- 'i' => ( $wgDBtype == 'postgres' ? 'INTEGER' : 'INT(8)' ),~~
1220		~~- 'j' => ( $wgDBtype == 'postgres' ? 'INTEGER' : 'INT(8) UNSIGNED' ),~~
	1249	+ 'i' => ( ( $wgDBtype == 'postgres' \|\| $wgDBtype == 'sqlite' ) ? 'INTEGER' : 'INT(8)' ),
	1250	+ 'j' => ( ( $wgDBtype == 'postgres' \|\| $wgDBtype == 'sqlite' ) ? 'INTEGER' : 'INT(8) UNSIGNED' ),
1221	1251	'p' => SMWSQLHelpers::getStandardDBType( 'id' ),
1222	1252	'n' => SMWSQLHelpers::getStandardDBType( 'namespace' ),
1223	1253	'w' => SMWSQLHelpers::getStandardDBType( 'iw' )
—	—	@@ -1237,7 +1267,7 @@
1238	1268	SMWSQLHelpers::setupTable(
1239	1269	'smw_ids',
1240	1270	array(
1241		~~- 'smw_id' => $dbtypes['p'] . ' NOT NULL' . ( $wgDBtype == 'postgres' ? ' PRIMARY KEY' : ' KEY AUTO_INCREMENT' ),~~
	1271	+ 'smw_id' => $dbtypes['p'] . ' NOT NULL' . ( $wgDBtype == 'postgres' ? ' PRIMARY KEY' : ( $wgDBtype == 'sqlite' ? ' PRIMARY KEY AUTOINCREMENT' : ' KEY AUTO_INCREMENT' ) ),
1242	1272	'smw_namespace' => $dbtypes['n'] . ' NOT NULL',
1243	1273	'smw_title' => $dbtypes['t'] . ' NOT NULL',
1244	1274	'smw_iw' => $dbtypes['w'],
Index: trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php
—	—	@@ -25,10 +25,10 @@
26	26	global $wgDBtype;
27	27
28	28	switch ( $input ) {
29		~~- case 'id': return $wgDBtype == 'postgres' ? 'SERIAL' : 'INT(8) UNSIGNED'; // like page_id in MW page table~~
	29	+ case 'id': return $wgDBtype == 'postgres' ? 'SERIAL' : ($wgDBtype == 'sqlite' ? 'INTEGER' :'INT(8) UNSIGNED'); // like page_id in MW page table
30	30	case 'namespace': return $wgDBtype == 'postgres' ? 'BIGINT' : 'INT(11)'; // like page_namespace in MW page table
31	31	case 'title': return $wgDBtype == 'postgres' ? 'TEXT' : 'VARBINARY(255)'; // like page_title in MW page table
32		~~- case 'iw': return $wgDBtype == 'postgres' ? 'TEXT' : 'VARCHAR(32) binary'; // like iw_prefix in MW interwiki table~~
	32	+ case 'iw': return ($wgDBtype == 'postgres' \|\| $wgDBtype == 'sqlite') ? 'TEXT' : 'VARCHAR(32) binary'; // like iw_prefix in MW interwiki table
33	33	case 'blob': return $wgDBtype == 'postgres' ? 'BYTEA' : 'MEDIUMBLOB'; // larger blobs of character data, usually not subject to SELECT conditions
34	34	}
35	35
—	—	@@ -88,7 +88,7 @@
89	89	protected static function createTable( $tableName, array $fields, $db, $reportTo ) {
90	90	global $wgDBtype, $wgDBTableOptions, $wgDBname;
91	91
92		~~- $sql = 'CREATE TABLE ' . ( $wgDBtype == 'postgres' ? '' : "`$wgDBname`." ) . $tableName . ' (';~~
	92	+ $sql = 'CREATE TABLE ' . ( ( $wgDBtype == 'postgres' \|\| $wgDBtype == 'sqlite' ) ? '' : "`$wgDBname`." ) . $tableName . ' (';
93	93
94	94	$fieldSql = array();
95	95
—	—	@@ -97,8 +97,11 @@
98	98	}
99	99
100	100	$sql .= implode( ',', $fieldSql ) . ') ';
101		~~- if ( $wgDBtype != 'postgres' ) $sql .= $wgDBTableOptions;~~
102	101
	102	+ if ( $wgDBtype != 'postgres' && $wgDBtype != 'sqlite' ) {
	103	+ $sql .= $wgDBTableOptions;
	104	+ }
	105	+
103	106	$db->query( $sql, __METHOD__ );
104	107	}
105	108
—	—	@@ -139,8 +142,11 @@
140	143	SMWSQLHelpers::reportProgress( " ... deleting obsolete field $fieldName ... ", $reportTo );
141	144
142	145	if ( $isPostgres ) {
143		~~- $db->query( "ALTER TABLE \"" . $tableName . "\" DROP COLUMN \"" . $fieldName . "\"", __METHOD__ );~~
	146	+ $db->query( 'ALTER TABLE "' . $tableName . '" DROP COLUMN "' . $fieldName . '"', __METHOD__ );
144	147	}
	148	+ else if ( $wgDBtype == 'sqlite' ) {
	149	+ // DROP COLUMN not supported in Sqlite3
	150	+ }
145	151	else {
146	152	$db->query( "ALTER TABLE $tableName DROP COLUMN `$fieldName`", __METHOD__ );
147	153	}
—	—	@@ -184,6 +190,8 @@
185	191	) AND a.attnum > 0 AND NOT a.attisdropped
186	192	ORDER BY a.attnum
187	193	EOT;
	194	+ } elseif ( $wgDBtype == 'sqlite' ) { // SQLite
	195	+ $sql = 'PRAGMA table_info(' . $tableName . ')';
188	196	} else { // MySQL
189	197	$sql = 'DESCRIBE ' . $tableName;
190	198	}
—	—	@@ -193,15 +201,27 @@
194	202	$result = array();
195	203
196	204	foreach ( $res as $row ) {
197		~~- $type = strtoupper( $row->Type );~~
198		-
199	205	if ( $wgDBtype == 'postgres' ) { // postgresql
	206	+ $type = strtoupper( $row->Type );
	207	+
200	208	if ( preg_match( '/^nextval\$.+\$/i', $row->Extra ) ) {
201	209	$type = 'SERIAL NOT NULL';
202	210	} elseif ( $row->Null != 'YES' ) {
203	211	$type .= ' NOT NULL';
204	212	}
	213	+ } elseif ( $wgDBtype == 'sqlite' ) { // SQLite
	214	+ $row->Field = $row->name;
	215	+ $row->Type = $row->type;
	216	+ $type = $row->type;
	217	+ if ( $row->notnull == '1' ) {
	218	+ $type .= ' NOT NULL';
	219	+ }
	220	+ if ( $row->pk == '1' ) {
	221	+ $type .= ' PRIMARY KEY AUTOINCREMENT';
	222	+ }
205	223	} else { // mysql
	224	+ $type = strtoupper( $row->Type );
	225	+
206	226	if ( substr( $type, 0, 8 ) == 'VARCHAR(' ) {
207	227	$type .= ' binary'; // just assume this to be the case for VARCHAR, though DESCRIBE will not tell us
208	228	}
—	—	@@ -362,6 +382,46 @@
363	383	}
364	384	}
365	385	}
	386	+ } elseif ( $wgDBtype == 'sqlite' ) { // SQLite
	387	+ $res = $db->query( 'PRAGMA index_list(' . $tableName . ')' , __METHOD__ );
	388	+
	389	+ if ( !$res ) {
	390	+ return false;
	391	+ }
	392	+
	393	+ $indexes = array();
	394	+
	395	+ foreach ( $db->fetchObject( $res ) as $row ) {
	396	+ if ( !array_key_exists( $row->name, $indexes ) ) {
	397	+ $indexes[$row->name] = array();
	398	+ }
	399	+
	400	+ $indexes[$row->name][$row->seq] = $row->name;
	401	+ }
	402	+
	403	+ foreach ( $indexes as $key => $index ) { // Clean up the existing indexes.
	404	+ $db->query( 'DROP INDEX ' . $key, __METHOD__ );
	405	+ }
	406	+
	407	+ foreach ( $columns as $key => $index ) { // Add the remaining indexes.
	408	+ if ( $index != false ) {
	409	+ $type = 'INDEX';
	410	+
	411	+ // If the index is an array, it'll contain the column name as first element, and index type as second one.
	412	+ if ( is_array( $index ) ) {
	413	+ $column = $index[0];
	414	+
	415	+ if ( count( $index ) > 1 ) {
	416	+ $type = $index[1];
	417	+ }
	418	+ }
	419	+ else {
	420	+ $column = $index;
	421	+ }
	422	+
	423	+ $db->query( "CREATE $type {$tableName}_index{$key} ON $tableName (" . $column . ")", __METHOD__ );
	424	+ }
	425	+ }
366	426	} else { // MySQL
367	427	$res = $db->query( 'SHOW INDEX FROM ' . $tableName , __METHOD__ );
368	428

Follow-up revisions

Revision	Commit summary	Author	Date
r90460	partial revert of r90358, patch breaks stuff	jeroendedauw	13:17, 20 June 2011

Comments

#Comment by MaxSem (talk | contribs) 20:48, 18 June 2011

+		} elseif ( $wgDBtype == 'sqlite' ) { // SQLite: no in-memory tables available
+			$sql = "CREATE TEMPORARY TABLE " . $smw_tmp_unusedprops . "( title VARCHAR(255) )";

Use simply TEXT. Although SQLite eats most types from other DBs just fine, it's always better to use native types in SQLite-only context.

#Comment by MaxSem (talk | contribs) 20:51, 18 June 2011

You can attach an in-memory DB and create a table there, by the way.

#Comment by Jeroen De Dauw (talk | contribs) 20:58, 18 June 2011

Definitely feel free to fix

#Comment by Devayon (talk | contribs) 12:41, 20 June 2011

Special:Ask stopped working after this commit. Do I need to repair/upgrade my data, or is it an actual bug?

#Comment by Jeroen De Dauw (talk | contribs) 13:04, 20 June 2011

This is definitely a bug. I'll see if I can fix it, and if not revert this change.