← All changes | includes/class-tables.php +284 -164 1.0.12 → 1.2.74 View file →

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