← All changes | includes/class-tables.php +287 -169 1.0.22 → 1.2.73 View file →

		@@ -5,42 +5,41 @@
5	5	* @since 1.0.0
6	6	* @author GeoDirectory Team <info@wpgeodirectory.com>
7	7	*/
8	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 uwp_create_tables()
19		- {
20	9
21		- if ( get_option('uwp_db_version') == USERSWP_VERSION ) return;
	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	+ {
22	20
23		- global $wpdb;
	21	+ global $wpdb;
24	22
25		- $table_name = uwp_get_table_prefix() . 'uwp_form_fields';
	23	+ $wpdb->hide_errors();
26	24
27		- $wpdb->hide_errors();
	25	+ // we may need to do some updates before dbDelta
	26	+ self::upgrade_1200();
28	27
29		- $collate = '';
30		- if ($wpdb->has_cap('collation')) {
31		- if (!empty($wpdb->charset)) $collate = "DEFAULT CHARACTER SET $wpdb->charset";
32		- if (!empty($wpdb->collate)) $collate .= " COLLATE $wpdb->collate";
33		- }
	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	+ }
34	33
35		- /**
36		- * Include any functions needed for upgrades.
37		- *
38		- * @since 1.0.0
39		- */
40		- require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
41		-
42		- $form_fields = "CREATE TABLE " . $table_name . " (
	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 . " (
43	42	id int(11) NOT NULL AUTO_INCREMENT,
44	43	form_type varchar(100) NULL,
45	44	data_type varchar(100) NULL,
46	45	field_type varchar(255) NOT NULL COMMENT 'text,checkbox,radio,select,textarea',
		@@ -52,8 +51,9 @@
52	51	default_value text NULL DEFAULT NULL,
53	52	sort_order int(11) NOT NULL,
54	53	option_values text NULL DEFAULT NULL,
55	54	is_active enum( '0', '1' ) NOT NULL DEFAULT '1',
	55	+ placeholder_value varchar(255) NULL DEFAULT NULL,
56	56	for_admin_use enum( '0', '1' ) NOT NULL DEFAULT '0',
57	57	is_default enum( '0', '1' ) NOT NULL DEFAULT '0',
58	58	is_dummy enum( '0', '1' ) NOT NULL DEFAULT '0',
59	59	is_public enum( '0', '1', '2' ) NOT NULL DEFAULT '0',
		@@ -69,18 +69,20 @@
69	69	css_class varchar(255) NULL DEFAULT NULL,
70	70	decimal_point varchar( 10 ) NOT NULL,
71	71	validation_pattern varchar( 255 ) NOT NULL,
72	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',
73	75	PRIMARY KEY (id)
74	76	) $collate";
75	77
76		- $form_fields = apply_filters('uwp_before_form_field_table_create', $form_fields);
	78	+ $form_fields = apply_filters('uwp_before_form_field_table_create', $form_fields);
77	79
78		- dbDelta($form_fields);
	80	+ dbDelta($form_fields);
79	81
80		- $extras_table_name = uwp_get_table_prefix() . 'uwp_form_extras';
	82	+ $extras_table_name = uwp_get_table_prefix() . 'uwp_form_extras';
81	83
82		- $form_extras = "CREATE TABLE " . $extras_table_name . " (
	84	+ $form_extras = "CREATE TABLE " . $extras_table_name . " (
83	85	id int(11) NOT NULL AUTO_INCREMENT,
84	86	form_type varchar(255) NOT NULL,
85	87	field_type varchar(255) NOT NULL COMMENT 'text,checkbox,radio,select,textarea',
86	88	site_htmlvar_name varchar(255) NOT NULL,
		@@ -90,8 +92,9 @@
90	92	expand_custom_value int(11) NULL DEFAULT NULL,
91	93	searching_range_mode int(11) NULL DEFAULT NULL,
92	94	expand_search int(11) NULL DEFAULT NULL,
93	95	front_search_title varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
	96	+ front_css_class varchar(255) NULL DEFAULT NULL,
94	97	first_search_value int(11) NULL DEFAULT NULL,
95	98	first_search_text varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
96	99	last_search_text varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
97	100	search_min_value int(11) NULL DEFAULT NULL,
		@@ -99,170 +102,285 @@
99	102	search_diff_value int(11) NULL DEFAULT NULL,
100	103	search_condition varchar(100) NULL DEFAULT NULL,
101	104	field_input_type varchar(255) NULL DEFAULT NULL,
102	105	field_data_type varchar(255) NULL DEFAULT NULL,
	106	+ form_id int(11) NOT NULL DEFAULT 1,
103	107	PRIMARY KEY (id)
104	108	) $collate AUTO_INCREMENT=1 ;";
105	109
106		- $form_extras = apply_filters('uwp_before_form_extras_table_create', $form_extras);
	110	+ $form_extras = apply_filters('uwp_before_form_extras_table_create', $form_extras);
107	111
108		- dbDelta($form_extras);
	112	+ dbDelta($form_extras);
109	113
110		- update_option('uwp_db_version', USERSWP_VERSION);
	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 ";
111	132
112		- }
	133	+ $user_meta = apply_filters('uwp_before_usermeta_table_create', $user_meta);
113	134
114		- /**
115		- * Creates uwp_usermeta table which introduced in version 1.0.1
116		- *
117		- * @since 1.0.0
118		- * @package userswp
119		- *
120		- * @return void
121		- */
122		- public function uwp101_create_tables() {
	135	+ dbDelta($user_meta);
123	136
124		- global $wpdb;
	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; ";
125	156
126		- $wpdb->hide_errors();
	157	+ $tabs_tbl_query = apply_filters('uwp_profile_tabs_table_create_query', $tabs_tbl_query);
127	158
128		- $collate = '';
129		- if ($wpdb->has_cap('collation')) {
130		- if (!empty($wpdb->charset)) $collate = "DEFAULT CHARACTER SET $wpdb->charset";
131		- if (!empty($wpdb->collate)) $collate .= " COLLATE $wpdb->collate";
132		- }
	159	+ dbDelta($tabs_tbl_query);
133	160
134		- require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
	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; ";
135	178
	179	+ $tabs_tbl_query = apply_filters('uwp_user_sorting_table_create_query', $tabs_tbl_query);
136	180
137		- // Table for storing userswp usermeta
138		- $usermeta_table_name = get_usermeta_table_prefix() . 'uwp_usermeta';
139		- $user_meta = "CREATE TABLE " . $usermeta_table_name . " (
140		- user_id int(20) NOT NULL,
141		- user_ip varchar(20) NULL DEFAULT NULL,
142		- user_privacy varchar(255) NULL DEFAULT NULL,
143		- uwp_account_username varchar(255) NULL DEFAULT NULL,
144		- uwp_account_email varchar(255) NULL DEFAULT NULL,
145		- uwp_account_first_name varchar(255) NULL DEFAULT NULL,
146		- uwp_account_last_name varchar(255) NULL DEFAULT NULL,
147		- uwp_account_avatar_thumb varchar(255) NULL DEFAULT NULL,
148		- uwp_account_banner_thumb varchar(255) NULL DEFAULT NULL,
149		- uwp_account_display_name varchar(255) NULL DEFAULT NULL,
150		- PRIMARY KEY (user_id)
151		- ) $collate ";
	181	+ dbDelta($tabs_tbl_query);
152	182
153		- $user_meta = apply_filters('uwp_before_usermeta_table_create', $user_meta);
	183	+ }
154	184
155		- dbDelta($user_meta);
156		- }
	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	+ }
157	203
158		- /**
159		- * Deleting the table whenever a blog is deleted
160		- *
161		- * @since 1.0.0
162		- * @package userswp
163		- *
164		- * @param array $tables Tables to delete.
165		- *
166		- * @return array Modified table array to delete
167		- */
168		- public function drop_tables_on_delete_blog( $tables ) {
169		- global $wpdb;
170		- $tables[] = $wpdb->prefix . 'uwp_form_fields';
171		- $tables[] = $wpdb->prefix . 'uwp_form_extras';
172		- $tables[] = $wpdb->prefix . 'uwp_usermeta';
173		- return $tables;
174		- }
	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	+ }
175	216
176		- /**
177		- * Returns the table prefix based on the installation type.
178		- *
179		- * @since 1.0.0
180		- * @package userswp
181		- *
182		- * @return string Table prefix
183		- */
184		- public function get_table_prefix() {
185		- global $wpdb;
186		- return $wpdb->prefix;
187		- }
	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;
188	227
189		- /**
190		- * Returns the user meta table prefix based on the installation type.
191		- *
192		- * @since 1.0.16
193		- * @package userswp
194		- *
195		- * @return string Table prefix
196		- */
197		- public function get_usermeta_table_prefix() {
198		- global $wpdb;
	228	+ if ( ! function_exists( 'is_plugin_active_for_network' ) ) {
	229	+ require_once( ABSPATH . '/wp-admin/includes/plugin.php' );
	230	+ }
199	231
200		- if ( ! function_exists( 'is_plugin_active_for_network' ) ) {
201		- require_once( ABSPATH . '/wp-admin/includes/plugin.php' );
202		- }
	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	+ }
203	239
204		- // Network active.
205		- if ( is_plugin_active_for_network( 'userswp/userswp.php' ) ) {
206		- return $wpdb->base_prefix;
207		- } else {
208		- return $wpdb->prefix;
209		- }
210		- }
	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	+ }
211	264
212		- /**
213		- * Checks whether the column exists in the table.
214		- *
215		- * @since 1.0.0
216		- * @package userswp
217		- *
218		- * @param string $db Table name.
219		- * @param string $column Column name.
220		- *
221		- * @return bool
222		- */
223		- public function column_exists($db, $column)
224		- {
225		- global $wpdb;
226		- $exists = false;
227		- $columns = $wpdb->get_col("show columns from $db");
228		- foreach ($columns as $c) {
229		- if ($c == $column) {
230		- $exists = true;
231		- break;
232		- }
233		- }
234		- return $exists;
235		- }
	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();
236	280
237		- /**
238		- * Adds column if not exist in the table.
239		- *
240		- * @since 1.0.0
241		- * @package userswp
242		- *
243		- * @param string $db Table name.
244		- * @param string $column Column name.
245		- * @param string $column_attr Column attributes.
246		- *
247		- * @return bool|int True when success.
248		- */
249		- public function add_column_if_not_exist($db, $column, $column_attr = "VARCHAR( 255 ) NOT NULL")
250		- {
251		- $excluded = uwp_get_excluded_fields();
	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	+ }
252	293
253		- $starts_with = "uwp_account_";
	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'");
254	307
255		- if ((substr($column, 0, strlen($starts_with)) === $starts_with) && !in_array($column, $excluded)) {
256		- global $wpdb;
257		- $result = 0;// no rows affected
258		- if (!$this->column_exists($db, $column)) {
259		- if (!empty($db) && !empty($column))
260		- $result = $wpdb->query("ALTER TABLE `$db` ADD `$column` $column_attr");
	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;
261	379	}
262		- return $result;
263		- } else {
264		- return true;
265	380	}
	381	+
	382	+ return $columns;
	383	+
266	384	}
267	385
268	386	}