redirection / models / database.php

database.php in Redirection 3.5, at models/database.php

321 lines 10.2 KB

Raw Download Zip

1	<?php
2
3	class RE_Database {
4	public function get_charset() {
5	global $wpdb;
6
7	$charset_collate = '';
8	if ( ! empty( $wpdb->charset ) ) {
9	$charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
10	}
11
12	if ( ! empty( $wpdb->collate ) ) {
13	$charset_collate .= " COLLATE=$wpdb->collate";
14	}
15
16	return $charset_collate;
17	}
18
19	private function create_items_sql( $prefix, $charset_collate ) {
20	return "CREATE TABLE IF NOT EXISTS `{$prefix}redirection_items` (
21	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
22	`url` mediumtext NOT NULL,
23	`regex` int(11) unsigned NOT NULL DEFAULT '0',
24	`position` int(11) unsigned NOT NULL DEFAULT '0',
25	`last_count` int(10) unsigned NOT NULL DEFAULT '0',
26	`last_access` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
27	`group_id` int(11) NOT NULL DEFAULT '0',
28	`status` enum('enabled','disabled') NOT NULL DEFAULT 'enabled',
29	`action_type` varchar(20) NOT NULL,
30	`action_code` int(11) unsigned NOT NULL,
31	`action_data` mediumtext,
32	`match_type` varchar(20) NOT NULL,
33	`title` text,
34	PRIMARY KEY (`id`),
35	KEY `url` (`url`(191)),
36	KEY `status` (`status`),
37	KEY `regex` (`regex`),
38	KEY `group_idpos` (`group_id`,`position`),
39	KEY `group` (`group_id`)
40	) $charset_collate";
41	}
42
43	private function create_groups_sql( $prefix, $charset_collate ) {
44	return "CREATE TABLE IF NOT EXISTS `{$prefix}redirection_groups` (
45	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
46	`name` varchar(50) NOT NULL,
47	`tracking` int(11) NOT NULL DEFAULT '1',
48	`module_id` int(11) unsigned NOT NULL DEFAULT '0',
49	`status` enum('enabled','disabled') NOT NULL DEFAULT 'enabled',
50	`position` int(11) unsigned NOT NULL DEFAULT '0',
51	PRIMARY KEY (`id`),
52	KEY `module_id` (`module_id`),
53	KEY `status` (`status`)
54	) $charset_collate";
55	}
56
57	private function create_log_sql( $prefix, $charset_collate ) {
58	return "CREATE TABLE IF NOT EXISTS `{$prefix}redirection_logs` (
59	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
60	`created` datetime NOT NULL,
61	`url` mediumtext NOT NULL,
62	`sent_to` mediumtext,
63	`agent` mediumtext NOT NULL,
64	`referrer` mediumtext,
65	`redirection_id` int(11) unsigned DEFAULT NULL,
66	`ip` varchar(45) DEFAULT NULL,
67	`module_id` int(11) unsigned NOT NULL,
68	`group_id` int(11) unsigned DEFAULT NULL,
69	PRIMARY KEY (`id`),
70	KEY `created` (`created`),
71	KEY `redirection_id` (`redirection_id`),
72	KEY `ip` (`ip`),
73	KEY `group_id` (`group_id`),
74	KEY `module_id` (`module_id`)
75	) $charset_collate";
76	}
77
78	private function create_404_sql( $prefix, $charset_collate ) {
79	return "CREATE TABLE IF NOT EXISTS `{$prefix}redirection_404` (
80	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
81	`created` datetime NOT NULL,
82	`url` varchar(255) NOT NULL DEFAULT '',
83	`agent` varchar(255) DEFAULT NULL,
84	`referrer` varchar(255) DEFAULT NULL,
85	`ip` varchar(45) DEFAULT NULL,
86	PRIMARY KEY (`id`),
87	KEY `created` (`created`),
88	KEY `url` (`url`(191)),
89	KEY `referrer` (`referrer`(191)),
90	KEY `ip` (`ip`)
91	) $charset_collate";
92	}
93
94	public function get_all_tables() {
95	global $wpdb;
96
97	$charset_collate = $this->get_charset();
98
99	return array(
100	"{$wpdb->prefix}redirection_items" => $this->create_items_sql( $wpdb->prefix, $charset_collate ),
101	"{$wpdb->prefix}redirection_groups" => $this->create_groups_sql( $wpdb->prefix, $charset_collate ),
102	"{$wpdb->prefix}redirection_logs" => $this->create_log_sql( $wpdb->prefix, $charset_collate ),
103	"{$wpdb->prefix}redirection_404" => $this->create_404_sql( $wpdb->prefix, $charset_collate ),
104	);
105	}
106
107	public function create_tables() {
108	global $wpdb;
109
110	foreach ( $this->get_all_tables() as $sql ) {
111	if ( $wpdb->query( $sql ) === false ) {
112	throw new Exception( 'There was a database error installing Redirection - please post these details to https://github.com/johngodley/redirection/issues - '.$sql.' = '.$wpdb->print_error() );
113	return false;
114	}
115	}
116	}
117
118	public function create_defaults() {
119	global $wpdb;
120
121	$this->create_default_groups();
122
123	update_option( 'redirection_version', REDIRECTION_DB_VERSION );
124
125	$group = $wpdb->get_row( "SELECT * FROM {$wpdb->prefix}redirection_groups LIMIT 1" );
126	if ( $group ) {
127	red_set_options( array( 'last_group_id' => $group->id ) );
128	}
129	}
130
131	private function create_default_groups() {
132	global $wpdb;
133
134	$existing_groups = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}redirection_groups" );
135
136	// Default groups
137	if ( intval( $existing_groups, 10 ) === 0 ) {
138	$wpdb->insert( $wpdb->prefix.'redirection_groups', array( 'name' => __( 'Redirections', 'redirection' ), 'module_id' => 1, 'position' => 0 ) );
139	$wpdb->insert( $wpdb->prefix.'redirection_groups', array( 'name' => __( 'Modified Posts', 'redirection' ), 'module_id' => 1, 'position' => 1 ) );
140	}
141	}
142
143	public function install() {
144	global $wpdb;
145
146	$wpdb->show_errors();
147	$this->create_tables();
148	$this->create_defaults();
149	$wpdb->hide_errors();
150
151	return true;
152	}
153
154	public function upgrade( $current, $target ) {
155	global $wpdb;
156
157	$wpdb->show_errors();
158
159	if ( $current !== false ) {
160	$versions = array(
161	'2.0.1' => 'upgrade_to_201',
162	'2.1.16' => 'upgrade_to_216',
163	'2.2' => 'upgrade_to_220',
164	'2.3.1' => 'upgrade_to_231',
165	'2.3.2' => 'upgrade_to_232',
166	'2.3.3' => 'upgrade_to_233',
167	'2.4' => 'upgrade_to_24',
168	);
169
170	foreach ( $versions AS $vers => $upgrade ) {
171	if ( version_compare( $current, $vers ) === -1 ) {
172	$this->$upgrade();
173	}
174	}
175
176	update_option( 'redirection_version', $target );
177	}
178
179	$wpdb->hide_errors();
180	}
181
182	/**
183	* Convert IP columns to VARCHAR(45)
184	*/
185
186	private function upgrade_to_24() {
187	global $wpdb;
188
189	// Expand logs to VARCHAR(45)
190	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_logs` CHANGE `ip` `ip` VARCHAR(45) DEFAULT NULL" );
191
192	// 404 log is an INT - add a new column with VARCHAR(45) and we'll delete the INT later
193	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_404` ADD `ipaddress` VARCHAR(45) DEFAULT NULL AFTER `ip`" );
194
195	// Convert all INT ips to VARCHAR ips
196	$wpdb->query( "UPDATE {$wpdb->prefix}redirection_404 SET ipaddress=INET_NTOA(ip)" );
197
198	// Now remove the old column
199	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_404` DROP `ip`" );
200
201	// And rename the new column
202	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_404` CHANGE `ipaddress` `ip` VARCHAR(45) DEFAULT NULL" );
203
204	// This was missed in a previous upgrade script
205	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_404` ADD INDEX `ip` (`ip`)" );
206
207	// Convert 'title' to a TEXT column
208	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_items` CHANGE `title` `title` text" );
209	}
210
211	/**
212	* 2.32 => 2.3.3
213	* Migrate any groups with incorrect module_ids
214	* Create a group if none exists
215	*/
216	private function upgrade_to_233() {
217	global $wpdb;
218
219	$wpdb->query( "UPDATE {$wpdb->prefix}redirection_groups SET module_id=1 WHERE module_id > 2" );
220	$this->create_default_groups();
221	}
222
223	/**
224	* 2.3.1 => 2.3.2
225	* Delete the redirection_modules table
226	*/
227	private function upgrade_to_232() {
228	global $wpdb;
229
230	$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}redirection_modules;" );
231	}
232
233	/**
234	* 2.2 => 2.3.1
235	* Update any group referring to 404 module to WordPress module
236	* Create 404 log table
237	*/
238	private function upgrade_to_231() {
239	global $wpdb;
240
241	$wpdb->query( "UPDATE {$wpdb->prefix}redirection_groups SET module_id=1 WHERE module_id=3" );
242	$wpdb->query( $this->create_404_sql( $wpdb->prefix, $this->get_charset() ) );
243	}
244
245	/**
246	* 2.1.6 => 2.2.0
247	* Add indices to redirection items and logs
248	*/
249	private function upgrade_to_220() {
250	global $wpdb;
251
252	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_items` ADD INDEX `group_idpos` (`group_id`,`position`)" );
253	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_items` ADD INDEX `group` (`group_id`)" );
254	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_logs` ADD INDEX `created` (`created`)" );
255	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_logs` ADD INDEX `redirection_id` (`redirection_id`)" );
256	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_logs` ADD INDEX `ip` (`ip`)" );
257	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_logs` ADD INDEX `group_id` (`group_id`)" );
258	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_logs` ADD INDEX `module_id` (`module_id`)" );
259	}
260
261	/**
262	* 2.0.1 => 2.1.6
263	* Update any group referring to 404 module to WordPress module
264	* Create 404 log table
265	*/
266	private function upgrade_to_216() {
267	global $wpdb;
268
269	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_groups` ADD INDEX(module_id)" );
270	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_groups` ADD INDEX(status)" );
271	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_items` ADD INDEX(url(191))" );
272	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_items` ADD INDEX(status)" );
273	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_items` ADD INDEX(regex)" );
274	}
275
276	/**
277	* <2.0.1 => 2.0.1
278	*/
279	private function upgrade_to_201() {
280	global $wpdb;
281
282	$wpdb->query( "ALTER TABLE `{$wpdb->prefix}redirection_items` ADD `title` varchar(50) NULL" );
283	}
284
285	public function remove() {
286	global $wpdb;
287
288	$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}redirection_items" );
289	$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}redirection_logs" );
290	$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}redirection_groups" );
291	$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}redirection_modules" );
292	$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}redirection_404" );
293
294	delete_option( 'redirection_lookup' );
295	delete_option( 'redirection_post' );
296	delete_option( 'redirection_root' );
297	delete_option( 'redirection_index' );
298	delete_option( 'redirection_options' );
299	delete_option( 'redirection_version' );
300	}
301
302	public function get_status() {
303	global $wpdb;
304
305	$missing = array();
306
307	foreach ( $this->get_all_tables() as $table => $sql ) {
308	$result = $wpdb->query( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
309
310	if ( intval( $result, 10 ) !== 1 ) {
311	$missing[] = $table;
312	}
313	}
314
315	return array(
316	'status' => count( $missing ) === 0 ? 'good' : 'error',
317	'message' => count( $missing ) === 0 ? __( 'All tables present', 'redirection' ) : __( 'The following tables are missing:', 'redirection' ).' '.join( ',', $missing ),
318	);
319	}
320	}
321