r72134 MediaWiki - Code Review archive

Repository:	MediaWiki
Revision:	< r72133‎ \| r72134 \| r72135 >
Date:	18:14, 1 September 2010
Author:	leonsp
Status:	deferred (Comments)
Tags:
Comment:	DB2: Implemented prepared statements for INSERT and UPDATE to allow more than 32k of text data DB2: New LIST_SET_PREPARED mode for DatabaseIbm_db2::makeList() that handles ? tokens in prepared UPDATE statement DB2: General fixes for DB2 database schema DB2: Factored out foreign key definitions into foreignkeys.sql so that more can be added down the line without messing with the main schema DB2: Better limit and offset support
Modified paths:	/trunk/phase3/includes/Defines.php (modified) (history) /trunk/phase3/includes/GlobalFunctions.php (modified) (history) /trunk/phase3/includes/db/DatabaseIbm_db2.php (modified) (history) /trunk/phase3/maintenance/ibm_db2/foreignkeys.sql (added) (history) /trunk/phase3/maintenance/ibm_db2/tables.sql (modified) (history)

Diff [purge]

Index: trunk/phase3/includes/db/DatabaseIbm_db2.php
—	—	@@ -459,7 +459,7 @@
460	460	* @param $string String: the relevant debug message
461	461	*/
462	462	private function installPrint($string) {
463		~~- wfDebug("$string");~~
	463	+ wfDebug("$string\n");
464	464	if ($this->mMode == self::INSTALL_MODE) {
465	465	print "<li>$string</li>";
466	466	flush();
—	—	@@ -634,16 +634,13 @@
635	635	*/
636	636	/private/
637	637	public function doQuery( $sql ) {
638		~~- //print "<li><pre>$sql</pre></li>";~~
639		~~- // Switch into the correct namespace~~
640	638	$this->applySchema();
641	639
642	640	$ret = db2_exec( $this->mConn, $sql, $this->mStmtOptions );
643		~~- if( !$ret ) {~~
644		~~- print "<br><pre>";~~
645		~~- print $sql;~~
646		~~- print "</pre><br>";~~
	641	+ if( $ret == FALSE ) {
647	642	$error = db2_stmt_errormsg();
	643	+ $this->installPrint("<pre>$sql</pre>");
	644	+ $this->installPrint($error);
648	645	throw new DBUnexpectedError($this, 'SQL error: ' . htmlspecialchars( $error ) );
649	646	}
650	647	$this->mLastResult = $ret;
—	—	@@ -748,6 +745,12 @@
749	746	} else {
750	747	print " done</li>";
751	748	}
	749	+ $res = $this->sourceFile( "../maintenance/ibm_db2/foreignkeys.sql" );
	750	+ if ($res !== true) {
	751	+ print " <b>FAILED</b>: " . htmlspecialchars( $res ) . "</li>";
	752	+ } else {
	753	+ print "<li>Foreign keys done</li>";
	754	+ }
752	755	$res = null;
753	756
754	757	// TODO: populate interwiki links
—	—	@@ -881,67 +884,35 @@
882	885	* LIST_OR - ORed WHERE clause (without the WHERE)
883	886	* LIST_SET - comma separated with field names, like a SET clause
884	887	* LIST_NAMES - comma separated field names
	888	+ * LIST_SET_PREPARED - like LIST_SET, except with ? tokens as values
885	889	*/
886		~~- public function makeList( $a, $mode = LIST_COMMA ) {~~
	890	+ function makeList( $a, $mode = LIST_COMMA ) {
887	891	if ( !is_array( $a ) ) {
888		~~- throw new DBUnexpectedError( $this, 'Database::makeList called with incorrect parameters' );~~
	892	+ throw new DBUnexpectedError( $this, 'DatabaseBase::makeList called with incorrect parameters' );
889	893	}
890		-
891		~~- $first = true;~~
892		~~- $list = '';~~
893		~~- foreach ( $a as $field => $value ) {~~
894		~~- if ( !$first ) {~~
895		~~- if ( $mode == LIST_AND ) {~~
896		~~- $list .= ' AND ';~~
897		~~- } elseif($mode == LIST_OR) {~~
898		~~- $list .= ' OR ';~~
899		~~- } else {~~
900		~~- $list .= ',';~~
	894	+
	895	+ // if this is for a prepared UPDATE statement
	896	+ // (this should be promoted to the parent class
	897	+ // once other databases use prepared statements)
	898	+ if ($mode == LIST_SET_PREPARED) {
	899	+ $first = true;
	900	+ $list = '';
	901	+ foreach ( $a as $field => $value ) {
	902	+ if (!$first) {
	903	+ $list .= ", $field = ?";
901	904	}
902		~~- } else {~~
903		~~- $first = false;~~
904		~~- }~~
905		~~- if ( ($mode == LIST_AND \|\| $mode == LIST_OR) && is_numeric( $field ) ) {~~
906		~~- $list .= "($value)";~~
907		~~- } elseif ( ($mode == LIST_SET) && is_numeric( $field ) ) {~~
908		~~- $list .= "$value";~~
909		~~- } elseif ( ($mode == LIST_AND \|\| $mode == LIST_OR) && is_array($value) ) {~~
910		~~- if( count( $value ) == 0 ) {~~
911		~~- throw new MWException( __METHOD__.': empty input' );~~
912		~~- } elseif( count( $value ) == 1 ) {~~
913		~~- // Special-case single values, as IN isn't terribly efficient~~
914		~~- // Don't necessarily assume the single key is 0; we don't~~
915		~~- // enforce linear numeric ordering on other arrays here.~~
916		~~- $value = array_values( $value );~~
917		~~- $list .= $field." = ".$this->addQuotes( $value[0] );~~
918		~~- } else {~~
919		~~- $list .= $field." IN (".$this->makeList($value).") ";~~
920		~~- }~~
921		~~- } elseif( is_null($value) ) {~~
922		~~- if ( $mode == LIST_AND \|\| $mode == LIST_OR ) {~~
923		~~- $list .= "$field IS ";~~
924		~~- } elseif ( $mode == LIST_SET ) {~~
925		~~- $list .= "$field = ";~~
926		~~- }~~
927		~~- $list .= 'NULL';~~
928		~~- } else {~~
929		~~- if ( $mode == LIST_AND \|\| $mode == LIST_OR \|\| $mode == LIST_SET ) {~~
930		~~- $list .= "$field = ";~~
931		~~- }~~
932		~~- if ( $mode == LIST_NAMES ) {~~
933		~~- $list .= $value;~~
934		~~- }~~
935		~~- // Leo: Can't insert quoted numbers into numeric columns~~
936		~~- // (?) Might cause other problems. May have to check column type before insertion.~~
937		~~- else if ( is_numeric($value) ) {~~
938		~~- $list .= $value;~~
939		~~- }~~
940	905	else {
941		~~- $list .= $this->addQuotes( $value );~~
	906	+ $list .= "( $field = ?";
	907	+ $first = false;
942	908	}
943	909	}
	910	+ $list .= ')';
	911	+
	912	+ return $list;
944	913	}
945		~~- return $list;~~
	914	+
	915	+ // otherwise, call the usual function
	916	+ return parent::makeList( $a, $mode );
946	917	}
947	918
948	919	/**
—	—	@@ -956,10 +927,14 @@
957	928	throw new DBUnexpectedError( $this, "Invalid non-numeric limit passed to limitResult()\n" );
958	929	}
959	930	if( $offset ) {
960		~~- $this->installPrint("Offset parameter not supported in limitResult()\n");~~
	931	+ //$this->installPrint("Offset parameter not supported in limitResult()\n");
	932	+ if ( stripos($sql, 'where') === false ) {
	933	+ return "$sql AND (ROWNUM BETWEEN $offset AND $offset+$limit)";
	934	+ }
	935	+ else {
	936	+ return "$sql WHERE (ROWNUM BETWEEN $offset AND $offset+$limit)";
	937	+ }
961	938	}
962		~~- // TODO implement proper offset handling~~
963		~~- // idea: get all the rows between 0 and offset, advance cursor to offset~~
964	939	return "$sql FETCH FIRST $limit ROWS ONLY ";
965	940	}
966	941
—	—	@@ -1027,8 +1002,8 @@
1028	1003	*/
1029	1004	private function calcInsertId($table, $primaryKey, $stmt) {
1030	1005	if ($primaryKey) {
1031		~~- $id_row = $this->fetchRow($stmt);~~
1032		~~- $this->mInsertId = $id_row[0];~~
	1006	+ $this->mInsertId = db2_last_insert_id($this->mConn);
	1007	+ //$this->installPrint("Last $primaryKey for $table was $this->mInsertId");
1033	1008	}
1034	1009	}
1035	1010
—	—	@@ -1052,11 +1027,12 @@
1053	1028	// get database-specific table name (not used)
1054	1029	$table = $this->tableName( $table );
1055	1030	// format options as an array
1056		~~- if ( !is_array( $options ) ) $options = array( $options );~~
	1031	+ $options = IBM_DB2Helper::makeArray($options);
1057	1032	// format args as an array of arrays
1058	1033	if ( !( isset( $args[0] ) && is_array( $args[0] ) ) ) {
1059	1034	$args = array($args);
1060	1035	}
	1036	+
1061	1037	// prevent insertion of NULL into primary key columns
1062	1038	list($args, $primaryKeys) = $this->removeNullPrimaryKeys($table, $args);
1063	1039	// if there's only one primary key
—	—	@@ -1081,27 +1057,29 @@
1082	1058	}
1083	1059
1084	1060	$sql = "INSERT INTO $table (" . implode( ',', $keys ) . ') VALUES ';
1085		~~- switch($key_count) {~~
1086		~~- //case 0 impossible~~
1087		~~- case 1:~~
1088		~~- $sql .= '(?)';~~
1089		~~- break;~~
1090		~~- default:~~
1091		~~- $sql .= '(?' . str_repeat(',?', $key_count-1) . ')';~~
	1061	+ if ($key_count == 1) {
	1062	+ $sql .= '(?)';
	1063	+ } else {
	1064	+ $sql .= '(?' . str_repeat(',?', $key_count-1) . ')';
1092	1065	}
1093		~~- // add logic to read back the new primary key value~~
1094		~~- if ($primaryKey) {~~
1095		~~- $sql = "SELECT $primaryKey FROM FINAL TABLE($sql)";~~
1096		~~- }~~
	1066	+ //$this->installPrint("Preparing the following SQL:");
	1067	+ //$this->installPrint("$sql");
	1068	+ //$this->installPrint(print_r($args, true));
1097	1069	$stmt = $this->prepare($sql);
1098	1070
1099	1071	// start a transaction/enter transaction mode
1100	1072	$this->begin();
1101	1073
1102	1074	if ( !$ignore ) {
	1075	+ //$first = true;
1103	1076	foreach ( $args as $row ) {
	1077	+ //$this->installPrint("Inserting " . print_r($row, true));
1104	1078	// insert each row into the database
1105	1079	$res = $res & $this->execute($stmt, $row);
	1080	+ if (!$res) {
	1081	+ $this->installPrint("Last error:");
	1082	+ $this->installPrint($this->lastError());
	1083	+ }
1106	1084	// get the last inserted value into a generated column
1107	1085	$this->calcInsertId($table, $primaryKey, $stmt);
1108	1086	}
—	—	@@ -1118,8 +1096,14 @@
1119	1097	foreach ( $args as $row ) {
1120	1098	$overhead = "SAVEPOINT $ignore ON ROLLBACK RETAIN CURSORS";
1121	1099	db2_exec($this->mConn, $overhead, $this->mStmtOptions);
	1100	+ //$this->installPrint("Inserting " . print_r($row, true));
1122	1101
1123	1102	$this->execute($stmt, $row);
	1103	+ //$this->installPrint(wfGetAllCallers());
	1104	+ if (!$res2) {
	1105	+ $this->installPrint("Last error:");
	1106	+ $this->installPrint($this->lastError());
	1107	+ }
1124	1108	// get the last inserted value into a generated column
1125	1109	$this->calcInsertId($table, $primaryKey, $stmt);
1126	1110
—	—	@@ -1139,6 +1123,7 @@
1140	1124	}
1141	1125	// commit either way
1142	1126	$this->commit();
	1127	+ $this->freePrepared($stmt);
1143	1128
1144	1129	return $res;
1145	1130	}
—	—	@@ -1187,11 +1172,20 @@
1188	1173	public function update( $table, $values, $conds, $fname = 'Database::update', $options = array() ) {
1189	1174	$table = $this->tableName( $table );
1190	1175	$opts = $this->makeUpdateOptions( $options );
1191		~~- $sql = "UPDATE $opts $table SET " . $this->makeList( $values, LIST_SET );~~
	1176	+ $sql = "UPDATE $opts $table SET " . $this->makeList( $values, LIST_SET_PREPARED );
1192	1177	if ( $conds != '*' ) {
1193	1178	$sql .= " WHERE " . $this->makeList( $conds, LIST_AND );
1194	1179	}
1195		~~- return $this->query( $sql, $fname );~~
	1180	+ $stmt = $this->prepare( $sql );
	1181	+ $this->installPrint("UPDATE: " . print_r($values, TRUE));
	1182	+ // assuming for now that an array with string keys will work
	1183	+ // if not, convert to simple array first
	1184	+ $result = $this->execute( $stmt, $values );
	1185	+ $this->freePrepared( $stmt );
	1186	+ //$result = $this->query( $sql, $fname );
	1187	+ // commit regardless of state
	1188	+ //$this->commit();
	1189	+ return $result;
1196	1190	}
1197	1191
1198	1192	/**
—	—	@@ -1208,7 +1202,10 @@
1209	1203	if ( $conds != '*' ) {
1210	1204	$sql .= ' WHERE ' . $this->makeList( $conds, LIST_AND );
1211	1205	}
1212		~~- return $this->query( $sql, $fname );~~
	1206	+ $result = $this->query( $sql, $fname );
	1207	+ // commit regardless
	1208	+ //$this->commit();
	1209	+ return $result;
1213	1210	}
1214	1211
1215	1212	/**
—	—	@@ -1643,7 +1640,7 @@
1644	1641	* @return mixed
1645	1642	*/
1646	1643	public function decodeBlob($b) {
1647		~~- return $b->getData();~~
	1644	+ return "$b";
1648	1645	}
1649	1646
1650	1647	/**
—	—	@@ -1791,3 +1788,11 @@
1792	1789	return 'BITOR('.$fieldLeft.', '.$fieldRight.')';
1793	1790	}
1794	1791	}
	1792	+
	1793	+class IBM_DB2Helper {
	1794	+ public static function makeArray($maybeArray) {
	1795	+ if ( !is_array( $maybeArray ) ) $maybeArray = array( $maybeArray );
	1796	+
	1797	+ return $maybeArray;
	1798	+ }
	1799	+}
Index: trunk/phase3/includes/Defines.php
—	—	@@ -192,6 +192,7 @@
193	193	define( 'LIST_SET', 2 );
194	194	define( 'LIST_NAMES', 3);
195	195	define( 'LIST_OR', 4);
	196	+define( 'LIST_SET_PREPARED', 8); // List of (?, ?, ?) for DatabaseIbm_db2
196	197
197	198	/**
198	199	* Unicode and normalisation related
Index: trunk/phase3/includes/GlobalFunctions.php
—	—	@@ -1980,6 +1980,8 @@
1981	1981	# TS_POSTGRES
1982	1982	} elseif ( preg_match( '/^(\d{4})\-(\d\d)\-(\d\d) (\d\d):(\d\d):(\d\d)\.\d GMT$/', $ts, $da ) ) {
1983	1983	# TS_POSTGRES
	1984	+ } elseif (preg_match('/^(\d{4})\-(\d\d)\-(\d\d) (\d\d):(\d\d):(\d\d)\.\d\d\d$/',$ts,$da)) {
	1985	+ # TS_DB2
1984	1986	} elseif ( preg_match( '/^[A-Z][a-z]{2}, \d\d [A-Z][a-z]{2} \d{4} \d\d:\d\d:\d\d/', $ts ) ) {
1985	1987	# TS_RFC2822
1986	1988	$uts = strtotime( $ts );
Index: trunk/phase3/maintenance/ibm_db2/tables.sql
—	—	@@ -8,6 +8,7 @@
9	9
10	10
11	11	CREATE TABLE user (
	12	+ -- Needs to start with 0
12	13	user_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
13	14	user_name VARCHAR(255) NOT NULL UNIQUE,
14	15	user_real_name VARCHAR(255),
—	—	@@ -71,7 +72,7 @@
72	73
73	74
74	75	CREATE TABLE page (
75		~~- page_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),~~
	76	+ page_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
76	77	page_namespace SMALLINT NOT NULL,
77	78	page_title VARCHAR(255) NOT NULL,
78	79	page_restrictions VARCHAR(1024),
—	—	@@ -96,7 +97,7 @@
97	98
98	99
99	100	CREATE TABLE revision (
100		~~- rev_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),~~
	101	+ rev_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
101	102	rev_page BIGINT NOT NULL DEFAULT 0,
102	103	-- REFERENCES page (page_id) ON DELETE CASCADE,
103	104	rev_text_id BIGINT, -- FK
—	—	@@ -119,9 +120,7 @@
120	121
121	122
122	123	CREATE TABLE text ( -- replaces reserved word 'text'
123		~~- --old_id INTEGER NOT NULL,~~
124		~~- old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),~~
125		~~- --PRIMARY KEY DEFAULT nextval('text_old_id_val'),~~
	124	+ old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
126	125	old_text CLOB(16M) INLINE LENGTH 4096,
127	126	old_flags VARCHAR(1024)
128	127	);
—	—	@@ -129,8 +128,8 @@
130	129
131	130	CREATE TABLE page_restrictions (
132	131	--pr_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('pr_id_val'),
133		~~- --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),~~
134		~~- pr_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),~~
	132	+ --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
	133	+ pr_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
135	134	pr_page INTEGER NOT NULL DEFAULT 0,
136	135	--(used to be nullable)
137	136	-- REFERENCES page (page_id) ON DELETE CASCADE,
—	—	@@ -184,7 +183,7 @@
185	184
186	185
187	186	CREATE TABLE redirect (
188		~~- rd_from BIGINT NOT NULL PRIMARY KEY,~~
	187	+ rd_from BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
189	188	--REFERENCES page(page_id) ON DELETE CASCADE,
190	189	rd_namespace SMALLINT NOT NULL DEFAULT 0,
191	190	rd_title VARCHAR(255) NOT NULL DEFAULT '',
—	—	@@ -248,7 +247,7 @@
249	248	--
250	249	CREATE TABLE external_user (
251	250	-- Foreign key to user_id
252		~~- eu_local_id BIGINT NOT NULL PRIMARY KEY,~~
	251	+ eu_local_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
253	252
254	253	-- Some opaque identifier provided by the external database
255	254	eu_external_id VARCHAR(255) NOT NULL
—	—	@@ -289,7 +288,7 @@
290	289	);
291	290
292	291	CREATE TABLE ipblocks (
293		~~- ipb_id INTEGER NOT NULL PRIMARY KEY,~~
	292	+ ipb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
294	293	--DEFAULT nextval('ipblocks_ipb_id_val'),
295	294	ipb_address VARCHAR(1024),
296	295	ipb_user BIGINT NOT NULL DEFAULT 0,
—	—	@@ -358,7 +357,6 @@
359	358	oi_sha1 VARCHAR(255) NOT NULL DEFAULT ''
360	359	--FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
361	360	);
362	361	CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
363	362	CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
364	363	CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
—	—	@@ -366,7 +364,7 @@
367	365
368	366
369	367	CREATE TABLE filearchive (
370		~~- fa_id INTEGER NOT NULL PRIMARY KEY,~~
	368	+ fa_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
371	369	--PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
372	370	fa_name VARCHAR(255) NOT NULL,
373	371	fa_archive_name VARCHAR(255),
—	—	@@ -398,7 +396,7 @@
399	397
400	398
401	399	CREATE TABLE recentchanges (
402		~~- rc_id INTEGER NOT NULL PRIMARY KEY,~~
	400	+ rc_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
403	401	--PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
404	402	rc_timestamp TIMESTAMP(3) NOT NULL,
405	403	rc_cur_time TIMESTAMP(3) NOT NULL,
—	—	@@ -509,7 +507,7 @@
510	508
511	509
512	510	CREATE TABLE logging (
513		~~- log_id BIGINT NOT NULL PRIMARY KEY,~~
	511	+ log_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
514	512	--PRIMARY KEY DEFAULT nextval('log_log_id_seq'),
515	513	log_type VARCHAR(32) NOT NULL,
516	514	log_action VARCHAR(32) NOT NULL,
—	—	@@ -534,10 +532,11 @@
535	533
536	534
537	535	CREATE TABLE trackbacks (
538		~~- tb_id INTEGER NOT NULL PRIMARY KEY,~~
	536	+ tb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
539	537	--PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
540	538	-- foreign key also in MySQL
541		~~- tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,~~
	539	+ tb_page INTEGER,
	540	+ -- REFERENCES page(page_id) ON DELETE CASCADE,
542	541	tb_title VARCHAR(255) NOT NULL,
543	542	tb_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,
544	543	tb_ex CLOB(64K) INLINE LENGTH 4096,
—	—	@@ -548,7 +547,7 @@
549	548
550	549
551	550	CREATE TABLE job (
552		~~- job_id BIGINT NOT NULL PRIMARY KEY,~~
	551	+ job_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
553	552	--PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
554	553	job_cmd VARCHAR(255) NOT NULL,
555	554	job_namespace SMALLINT NOT NULL,
—	—	@@ -558,47 +557,6 @@
559	558	CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
560	559
561	560
562		-
563		-
564		-
565		-
566		-
567		-
568		-
569		-
570	561	--TODO
571	562	--CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
572	563	--$mw$
—	—	@@ -644,7 +602,7 @@
645	603
646	604
647	605	CREATE TABLE category (
648		~~- cat_id INTEGER NOT NULL PRIMARY KEY,~~
	606	+ cat_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
649	607	--PRIMARY KEY DEFAULT nextval('category_id_seq'),
650	608	cat_title VARCHAR(255) NOT NULL,
651	609	cat_pages INTEGER NOT NULL DEFAULT 0,
Index: trunk/phase3/maintenance/ibm_db2/foreignkeys.sql
—	—	@@ -0,0 +1,107 @@
	2	+-- good
	3	+ALTER TABLE user_groups ADD CONSTRAINT USER_GROUPS_FK1 FOREIGN KEY (ug_user) REFERENCES user(user_id) ON DELETE CASCADE
	4	+;
	5	+
	6	+-- good
	7	+ALTER TABLE user_newtalk ADD CONSTRAINT USER_NEWTALK_FK1 FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
	8	+;
	9	+
	10	+-- referenced value not found
	11	+ALTER TABLE revision ADD CONSTRAINT REVISION_PAGE_FK FOREIGN KEY (rev_page) REFERENCES page(page_id) ON DELETE CASCADE
	12	+;
	13	+-- referenced value not found
	14	+ALTER TABLE revision ADD CONSTRAINT REVISION_USER_FK FOREIGN KEY (rev_user) REFERENCES user(user_id) ON DELETE RESTRICT
	15	+;
	16	+
	17	+-- good
	18	+ALTER TABLE page_restrictions ADD CONSTRAINT PAGE_RESTRICTIONS_PAGE_FK FOREIGN KEY (pr_page) REFERENCES page(page_id) ON DELETE CASCADE
	19	+;
	20	+
	21	+-- good
	22	+ALTER TABLE page_props ADD CONSTRAINT PAGE_PROPS_PAGE_FK FOREIGN KEY (pp_page) REFERENCES page(page_id) ON DELETE CASCADE
	23	+;
	24	+
	25	+-- cannot contain null values
	26	+-- ALTER TABLE archive ADD CONSTRAINT ARCHIVE_USER_FK FOREIGN KEY (ar_user) REFERENCES user(user_id) ON DELETE SET NULL
	27	+--;
	28	+
	29	+-- referenced value not found
	30	+ALTER TABLE redirect ADD CONSTRAINT REDIRECT_FROM_FK FOREIGN KEY (rd_from) REFERENCES page(page_id) ON DELETE CASCADE
	31	+;
	32	+
	33	+-- referenced value not found
	34	+ALTER TABLE pagelinks ADD CONSTRAINT PAGELINKS_FROM_FK FOREIGN KEY (pl_from) REFERENCES page(page_id) ON DELETE CASCADE
	35	+;
	36	+
	37	+-- good
	38	+ALTER TABLE templatelinks ADD CONSTRAINT TEMPLATELINKS_FROM_FK FOREIGN KEY (tl_from) REFERENCES page(page_id) ON DELETE CASCADE
	39	+;
	40	+
	41	+-- good
	42	+ALTER TABLE imagelinks ADD CONSTRAINT IMAGELINKS_FROM_FK FOREIGN KEY (il_from) REFERENCES page(page_id) ON DELETE CASCADE
	43	+;
	44	+
	45	+-- good
	46	+ALTER TABLE categorylinks ADD CONSTRAINT CATEGORYLINKS_FROM_FK FOREIGN KEY (cl_from) REFERENCES page(page_id) ON DELETE CASCADE
	47	+;
	48	+
	49	+-- good
	50	+ALTER TABLE externallinks ADD CONSTRAINT EXTERNALLINKS_FROM_FK FOREIGN KEY (el_from) REFERENCES page(page_id) ON DELETE CASCADE
	51	+;
	52	+
	53	+-- good
	54	+ALTER TABLE langlinks ADD CONSTRAINT LANGLINKS_FROM_FK FOREIGN KEY (ll_from) REFERENCES page(page_id) ON DELETE CASCADE
	55	+;
	56	+
	57	+-- cannot contain null values
	58	+-- ALTER TABLE ipblocks ADD CONSTRAINT IPBLOCKS_USER_FK FOREIGN KEY (ipb_user) REFERENCES user(user_id) ON DELETE SET NULL
	59	+--;
	60	+
	61	+-- good
	62	+ALTER TABLE ipblocks ADD CONSTRAINT IPBLOCKS_BY_FK FOREIGN KEY (ipb_by) REFERENCES user(user_id) ON DELETE CASCADE
	63	+;
	64	+
	65	+-- cannot contain null values
	66	+-- ALTER TABLE image ADD CONSTRAINT IMAGE_USER_FK FOREIGN KEY (img_user) REFERENCES user(user_id) ON DELETE SET NULL
	67	+--;
	68	+
	69	+-- cannot contain null values
	70	+-- ALTER TABLE oldimage ADD CONSTRAINT OLDIMAGE_USER_FK FOREIGN KEY (oi_user) REFERENCES user(user_id) ON DELETE SET NULL
	71	+--;
	72	+
	73	+-- good
	74	+ALTER TABLE oldimage ADD CONSTRAINT OLDIMAGE_NAME_FK FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
	75	+;
	76	+
	77	+-- cannot contain null values
	78	+-- ALTER TABLE filearchive ADD CONSTRAINT FILEARCHIVE_DELETED_USER_FK FOREIGN KEY (fa_deleted_user) REFERENCES user(user_id) ON DELETE SET NULL
	79	+--;
	80	+
	81	+-- cannot contain null values
	82	+-- ALTER TABLE filearchive ADD CONSTRAINT FILEARCHIVE_USER_FK FOREIGN KEY (fa_user) REFERENCES user(user_id) ON DELETE SET NULL
	83	+--;
	84	+
	85	+-- cannot contain null values
	86	+-- ALTER TABLE recentchanges ADD CONSTRAINT RECENTCHANGES_USER_FK FOREIGN KEY (rc_user) REFERENCES user(user_id) ON DELETE SET NULL
	87	+--;
	88	+
	89	+-- cannot contain null values
	90	+-- ALTER TABLE recentchanges ADD CONSTRAINT RECENTCHANGES_CUR_ID_FK FOREIGN KEY (rc_cur_id) REFERENCES page(page_id) ON DELETE SET NULL
	91	+--;
	92	+
	93	+-- good
	94	+ALTER TABLE watchlist ADD CONSTRAINT WATCHLIST_USER_FK FOREIGN KEY (wl_user) REFERENCES user(user_id) ON DELETE CASCADE
	95	+;
	96	+
	97	+-- good
	98	+-- already in MySQL schema
	99	+ALTER TABLE trackbacks ADD CONSTRAINT TRACKBACKS_PAGE_FK FOREIGN KEY (tb_page) REFERENCES page(page_id) ON DELETE CASCADE
	100	+;
	101	+
	102	+-- cannot contain null values
	103	+-- ALTER TABLE protected_titles ADD CONSTRAINT PROTECTED_TITLES_USER_FK FOREIGN KEY (pt_user) REFERENCES user(user_id) ON DELETE SET NULL
	104	+--;
	105	+
	106	+-- cannot contain null values
	107	+-- ALTER TABLE logging ADD CONSTRAINT LOGGING_USER_FK FOREIGN KEY (log_user) REFERENCES user(user_id) ON DELETE SET NULL
	108	+--;
\ No newline at end of file

Follow-up revisions

Revision	Commit summary	Author	Date
r72213	Based on r72134 feedback, applied spacing conventions from MediaWiki style gu...	leonsp	19:10, 2 September 2010
r97445	Minor whitespace fixes in GlobalFunctions.php...	krinkle	23:21, 18 September 2011

Comments

#Comment by Leonsp (talk | contribs) 21:16, 1 September 2010

This is related to the discussion on r45755 and was followed up by r72166.

#Comment by Nikerabbit (talk | contribs) 17:16, 2 September 2010

Needs more spacing with regards to our coding style.

#Comment by Leonsp (talk | contribs) 19:16, 2 September 2010

Fixed it up in r72213.

Status & tagging log

00:23, 5 December 2010 Reedy (talk | contribs) changed the status of r72134 [removed: new added: deferred]