userswp / includes / class-tables.php

class-tables.php in UsersWP – Front-end login form, User Registration, User Profile & Members Directory plugin for WP 1.2.38, at includes/class-tables.php

386 lines 13.0 KB

Raw Download Zip

1	<?php
2	/**
3	* UsersWP table related functions
4	*
5	* @since 1.0.0
6	* @author GeoDirectory Team <info@wpgeodirectory.com>
7	*/
8	class UsersWP_Tables {
9
10	/**
11	* Creates UsersWP related tables.
12	*
13	* @since 1.0.0
14	* @package userswp
15	*
16	* @return void
17	*/
18	public function create_tables()
19	{
20
21	global $wpdb;
22
23	$wpdb->hide_errors();
24
25	// we may need to do some updates before dbDelta
26	self::upgrade_1200();
27
28	$collate = '';
29	if ($wpdb->has_cap('collation')) {
30	if (!empty($wpdb->charset)) $collate = "DEFAULT CHARACTER SET $wpdb->charset";
31	if (!empty($wpdb->collate)) $collate .= " COLLATE $wpdb->collate";
32	}
33
34	/**
35	* Include any functions needed for upgrades.
36	*
37	* @since 1.0.0
38	*/
39	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
40	$table_name = uwp_get_table_prefix() . 'uwp_form_fields';
41	$form_fields = "CREATE TABLE " . $table_name . " (
42	id int(11) NOT NULL AUTO_INCREMENT,
43	form_type varchar(100) NULL,
44	data_type varchar(100) NULL,
45	field_type varchar(255) NOT NULL COMMENT 'text,checkbox,radio,select,textarea',
46	field_type_key varchar(255) NOT NULL,
47	site_title varchar(255) NULL DEFAULT NULL,
48	form_label varchar(255) NULL DEFAULT NULL,
49	help_text varchar(255) NULL DEFAULT NULL,
50	htmlvar_name varchar(255) NULL DEFAULT NULL,
51	default_value text NULL DEFAULT NULL,
52	sort_order int(11) NOT NULL,
53	option_values text NULL DEFAULT NULL,
54	is_active enum( '0', '1' ) NOT NULL DEFAULT '1',
55	placeholder_value varchar(255) NULL DEFAULT NULL,
56	for_admin_use enum( '0', '1' ) NOT NULL DEFAULT '0',
57	is_default enum( '0', '1' ) NOT NULL DEFAULT '0',
58	is_dummy enum( '0', '1' ) NOT NULL DEFAULT '0',
59	is_public enum( '0', '1', '2' ) NOT NULL DEFAULT '0',
60	is_required enum( '0', '1' ) NOT NULL DEFAULT '0',
61	is_register_field enum( '0', '1' ) NOT NULL DEFAULT '0',
62	is_search_field enum( '0', '1' ) NOT NULL DEFAULT '0',
63	is_register_only_field enum( '0', '1' ) NOT NULL DEFAULT '0',
64	required_msg varchar(255) NULL DEFAULT NULL,
65	show_in text NULL DEFAULT NULL,
66	user_roles text NULL DEFAULT NULL,
67	extra_fields text NULL DEFAULT NULL,
68	field_icon varchar(255) NULL DEFAULT NULL,
69	css_class varchar(255) NULL DEFAULT NULL,
70	decimal_point varchar( 10 ) NOT NULL,
71	validation_pattern varchar( 255 ) NOT NULL,
72	validation_msg text NULL DEFAULT NULL,
73	form_id int(11) NOT NULL DEFAULT 1,
74	user_sort enum( '0', '1' ) NOT NULL DEFAULT '0',
75	PRIMARY KEY (id)
76	) $collate";
77
78	$form_fields = apply_filters('uwp_before_form_field_table_create', $form_fields);
79
80	dbDelta($form_fields);
81
82	$extras_table_name = uwp_get_table_prefix() . 'uwp_form_extras';
83
84	$form_extras = "CREATE TABLE " . $extras_table_name . " (
85	id int(11) NOT NULL AUTO_INCREMENT,
86	form_type varchar(255) NOT NULL,
87	field_type varchar(255) NOT NULL COMMENT 'text,checkbox,radio,select,textarea',
88	site_htmlvar_name varchar(255) NOT NULL,
89	sort_order int(11) NOT NULL,
90	is_default enum( '0', '1' ) NOT NULL DEFAULT '0',
91	is_dummy enum( '0', '1' ) NOT NULL DEFAULT '0',
92	expand_custom_value int(11) NULL DEFAULT NULL,
93	searching_range_mode int(11) NULL DEFAULT NULL,
94	expand_search int(11) NULL DEFAULT NULL,
95	front_search_title varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
96	front_css_class varchar(255) NULL DEFAULT NULL,
97	first_search_value int(11) NULL DEFAULT NULL,
98	first_search_text varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
99	last_search_text varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
100	search_min_value int(11) NULL DEFAULT NULL,
101	search_max_value int(11) NULL DEFAULT NULL,
102	search_diff_value int(11) NULL DEFAULT NULL,
103	search_condition varchar(100) NULL DEFAULT NULL,
104	field_input_type varchar(255) NULL DEFAULT NULL,
105	field_data_type varchar(255) NULL DEFAULT NULL,
106	form_id int(11) NOT NULL DEFAULT 1,
107	PRIMARY KEY (id)
108	) $collate AUTO_INCREMENT=1 ;";
109
110	$form_extras = apply_filters('uwp_before_form_extras_table_create', $form_extras);
111
112	dbDelta($form_extras);
113
114	// Table for storing userswp usermeta
115	$usermeta_table_name = get_usermeta_table_prefix() . 'uwp_usermeta';
116	$user_meta = "CREATE TABLE " . $usermeta_table_name . " (
117	user_id int(20) NOT NULL,
118	user_ip varchar(20) NULL DEFAULT NULL,
119	user_privacy text NULL DEFAULT NULL,
120	tabs_privacy text NULL DEFAULT NULL,
121	username varchar(255) NULL DEFAULT NULL,
122	email varchar(255) NULL DEFAULT NULL,
123	first_name varchar(255) NULL DEFAULT NULL,
124	last_name varchar(255) NULL DEFAULT NULL,
125	avatar_thumb varchar(255) NULL DEFAULT NULL,
126	banner_thumb varchar(255) NULL DEFAULT NULL,
127	display_name varchar(255) NULL DEFAULT NULL,
128	user_url text NULL DEFAULT NULL,
129	bio text NULL DEFAULT NULL,
130	PRIMARY KEY (user_id)
131	) $collate ";
132
133	$user_meta = apply_filters('uwp_before_usermeta_table_create', $user_meta);
134
135	dbDelta($user_meta);
136
137	// profile tabs layout table
138	$profile_tabs_table_name = uwp_get_table_prefix() . 'uwp_profile_tabs';
139	$tabs_tbl_query = " CREATE TABLE " . $profile_tabs_table_name . " (
140	id int(11) NOT NULL AUTO_INCREMENT,
141	form_type varchar(100) NULL,
142	sort_order int(11) NOT NULL,
143	tab_layout varchar(100) NOT NULL,
144	tab_type varchar(100) NOT NULL,
145	tab_level int(11) NOT NULL,
146	tab_parent int(11) NOT NULL,
147	tab_privacy int(11) NOT NULL DEFAULT '0',
148	user_decided int(11) NOT NULL DEFAULT '0',
149	tab_name varchar(255) NOT NULL,
150	tab_icon varchar(255) NOT NULL,
151	tab_key varchar(255) NOT NULL,
152	tab_content text NULL DEFAULT NULL,
153	form_id int(11) NOT NULL DEFAULT 1,
154	PRIMARY KEY (id)
155	) $collate; ";
156
157	$tabs_tbl_query = apply_filters('uwp_profile_tabs_table_create_query', $tabs_tbl_query);
158
159	dbDelta($tabs_tbl_query);
160
161	// user sorting options table
162	$user_sorting_table_name = uwp_get_table_prefix() . 'uwp_user_sorting';
163	$tabs_tbl_query = " CREATE TABLE " . $user_sorting_table_name . " (
164	id int(11) NOT NULL AUTO_INCREMENT,
165	data_type varchar(255) NOT NULL,
166	field_type varchar(255) NOT NULL,
167	site_title varchar(255) NOT NULL,
168	htmlvar_name varchar(255) NOT NULL,
169	field_icon varchar(255) NULL DEFAULT NULL,
170	sort_order int(11) NOT NULL DEFAULT '0',
171	tab_parent varchar(100) NOT NULL DEFAULT '0',
172	tab_level int(11) NOT NULL DEFAULT '0',
173	is_active int(11) NOT NULL DEFAULT '0',
174	is_default int(11) NOT NULL DEFAULT '0',
175	sort varchar(5) DEFAULT 'asc',
176	PRIMARY KEY (id)
177	) $collate; ";
178
179	$tabs_tbl_query = apply_filters('uwp_user_sorting_table_create_query', $tabs_tbl_query);
180
181	dbDelta($tabs_tbl_query);
182
183	}
184
185	/**
186	* Deleting the table whenever a blog is deleted
187	*
188	* @since 1.0.0
189	* @package userswp
190	*
191	* @param array $tables Tables to delete.
192	*
193	* @return array Modified table array to delete
194	*/
195	public function drop_tables_on_delete_blog( $tables ) {
196	global $wpdb;
197	$tables[] = $wpdb->prefix . 'uwp_form_fields';
198	$tables[] = $wpdb->prefix . 'uwp_form_extras';
199	$tables[] = $wpdb->prefix . 'uwp_usermeta';
200	$tables[] = $wpdb->prefix . 'uwp_profile_tabs';
201	return $tables;
202	}
203
204	/**
205	* Returns the table prefix based on the installation type.
206	*
207	* @since 1.0.0
208	* @package userswp
209	*
210	* @return string Table prefix
211	*/
212	public function get_table_prefix() {
213	global $wpdb;
214	return $wpdb->prefix;
215	}
216
217	/**
218	* Returns the user meta table prefix based on the installation type.
219	*
220	* @since 1.0.16
221	* @package userswp
222	*
223	* @return string Table prefix
224	*/
225	public function get_usermeta_table_prefix() {
226	global $wpdb;
227
228	if ( ! function_exists( 'is_plugin_active_for_network' ) ) {
229	require_once( ABSPATH . '/wp-admin/includes/plugin.php' );
230	}
231
232	// Network active.
233	if ( is_plugin_active_for_network( 'userswp/userswp.php' ) \|\| 1 == get_network_option('', 'uwp_is_network_active') ) {
234	return $wpdb->base_prefix;
235	} else {
236	return $wpdb->prefix;
237	}
238	}
239
240	/**
241	* Checks whether the column exists in the table.
242	*
243	* @since 1.0.0
244	* @package userswp
245	*
246	* @param string $db Table name.
247	* @param string $column Column name.
248	*
249	* @return bool
250	*/
251	public function column_exists($db, $column)
252	{
253	global $wpdb;
254	$exists = false;
255	$columns = $wpdb->get_col("show columns from $db");
256	foreach ($columns as $c) {
257	if ($c == $column) {
258	$exists = true;
259	break;
260	}
261	}
262	return $exists;
263	}
264
265	/**
266	* Adds column if not exist in the table.
267	*
268	* @since 1.0.0
269	* @package userswp
270	*
271	* @param string $db Table name.
272	* @param string $column Column name.
273	* @param string $column_attr Column attributes.
274	*
275	* @return bool\|int True when success.
276	*/
277	public function add_column_if_not_exist($db, $column, $column_attr = "VARCHAR( 255 ) NOT NULL")
278	{
279	$excluded = uwp_get_excluded_fields();
280
281	if (!in_array($column, $excluded)) {
282	global $wpdb;
283	$result = 0;// no rows affected
284	if (!$this->column_exists($db, $column)) {
285	if (!empty($db) && !empty($column))
286	$result = $wpdb->query("ALTER TABLE `$db` ADD `$column` $column_attr");
287	}
288	return $result;
289	} else {
290	return true;
291	}
292	}
293
294	/**
295	* In v1.2.0 we removed some prefixes so they must be updated before dbDelta runs so to not duplicate columns.
296	*
297	* @since 1.2.0
298	*/
299	public function upgrade_1200(){
300	// Only run if its an upgrade, not an install
301	if(get_option('uwp_db_version')){
302	global $wpdb;
303	$meta_table = get_usermeta_table_prefix() . 'uwp_usermeta';
304	$fields_table = uwp_get_table_prefix() . 'uwp_form_fields';
305	$extras_table = uwp_get_table_prefix() . 'uwp_form_extras';
306	$cols = $wpdb->get_results( "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$meta_table' AND TABLE_SCHEMA ='$wpdb->dbname'");
307
308	if(!empty($cols)){
309	$current_cols = array();
310	foreach($cols as $col){
311	$current_cols[] = $col->COLUMN_NAME;
312	}
313	foreach($cols as $col){
314	if ( strpos( $col->COLUMN_NAME, 'uwp_account_' ) === 0 ) {
315	$col_name = sanitize_sql_orderby($col->COLUMN_NAME);
316	$col_type = $col->COLUMN_TYPE;
317	$new_col_name = in_array($col_name,$current_cols) ? str_ireplace("uwp_account_","",$col_name) : str_ireplace("uwp_account_","_",$col_name);
318
319	$sql = "ALTER TABLE `{$meta_table}` CHANGE `$col_name` `$new_col_name` $col_type";
320
321	// alter the usermeta column
322	$wpdb->query( $sql);
323
324	// Update the fields table keys
325	$wpdb->update(
326	$fields_table,
327	array(
328	'htmlvar_name' => $new_col_name,
329	),
330	array( 'htmlvar_name' => $col_name),
331	array(
332	'%s',
333	),
334	array( '%s' )
335	);
336
337	// Update the fields extras table keys
338	$wpdb->update(
339	$extras_table,
340	array(
341	'site_htmlvar_name' => $new_col_name,
342	),
343	array( 'site_htmlvar_name' => $col_name),
344	array(
345	'%s',
346	),
347	array( '%s' )
348	);
349
350	}
351	}
352	}
353
354	// now change all htmlvar_names
355	$wpdb->query( "UPDATE $fields_table SET htmlvar_name = REPLACE(htmlvar_name, 'uwp_account_', '') WHERE htmlvar_name LIKE 'uwp_account_%'");
356	$wpdb->query( "UPDATE $fields_table SET htmlvar_name = REPLACE(htmlvar_name, 'uwp_change_', '') WHERE htmlvar_name LIKE 'uwp_change_%'");
357	$wpdb->query( "UPDATE $fields_table SET htmlvar_name = REPLACE(htmlvar_name, 'uwp_reset_', '') WHERE htmlvar_name LIKE 'uwp_reset_%'");
358	$wpdb->query( "UPDATE $fields_table SET htmlvar_name = REPLACE(htmlvar_name, 'uwp_forgot_', '') WHERE htmlvar_name LIKE 'uwp_forgot_%'");
359	$wpdb->query( "UPDATE $fields_table SET htmlvar_name = REPLACE(htmlvar_name, 'uwp_login_', '') WHERE htmlvar_name LIKE 'uwp_login_%'");
360
361	$wpdb->query( "UPDATE $fields_table SET htmlvar_name = 'avatar' WHERE htmlvar_name = 'uwp_avatar_file'");
362	$wpdb->query( "UPDATE $fields_table SET htmlvar_name = 'banner' WHERE htmlvar_name = 'uwp_banner_file'");
363
364	$wpdb->query( "UPDATE $extras_table SET site_htmlvar_name = REPLACE(site_htmlvar_name, 'uwp_account_', '') WHERE site_htmlvar_name LIKE 'uwp_account_%'");
365
366	}
367
368	}
369
370	public function get_db_usermeta_columns()
371	{ global $wpdb;
372	$meta_table = get_usermeta_table_prefix() . 'uwp_usermeta';
373	$cols = $wpdb->get_results( "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$meta_table' AND TABLE_SCHEMA ='$wpdb->dbname'");
374
375	$columns = array();
376	if (!empty($cols)) {
377	foreach ($cols as $col) {
378	$columns[] = $col->COLUMN_NAME;
379	}
380	}
381
382	return $columns;
383
384	}
385
386	}