gamipress / libraries / ct / includes / class-ct-database-schema-updater.php

class-ct-database-schema-updater.php in GamiPress – Gamification plugin to reward points, badges & ranks in WordPress, now with AI 8.0.3, at libraries/ct/includes/class-ct-database-schema-updater.php

294 lines 11.9 KB

Raw Download Zip

1	<?php
2	/**
3	* Database Table Schema Updater class
4	*
5	* @author GamiPress <[email protected]>, Ruben Garcia <[email protected]>
6	*
7	* @since 1.0.0
8	*/
9	// Exit if accessed directly
10	defined( 'ABSPATH' ) \|\| exit;
11
12	if ( ! class_exists( 'CT_DataBase_Schema_Updater' ) ) :
13
14	class CT_DataBase_Schema_Updater {
15
16	/**
17	* @var CT_DataBase Database object
18	*/
19	public $ct_db;
20
21	/**
22	* @var CT_DataBase_Schema Database Schema object
23	*/
24	public $schema;
25
26	/**
27	* CT_DataBase_Schema_Updater constructor.
28	*
29	* @param CT_DataBase $ct_db
30	*/
31	public function __construct( $ct_db ) {
32
33	$this->ct_db = $ct_db;
34	$this->schema = $ct_db->schema;
35
36	}
37
38	/**
39	* Run the database schema update
40	*
41	* @return bool
42	*/
43	public function run() {
44
45	if( $this->schema ) {
46
47	$alters = array();
48
49	// Get schema fields and current table definition to being compared
50	$schema_fields = $this->schema->fields;
51	$current_schema_fields = array();
52
53	// Get a description of current schema
54	$schema_description = $this->ct_db->db->get_results( "DESCRIBE {$this->ct_db->table_name}" );
55
56	// Check stored schema with configured fields to check field deletions and build a custom array to be used after
57	foreach( $schema_description as $field ) {
58
59	$current_schema_fields[$field->Field] = $this->object_field_to_array( $field );
60
61	if( ! isset( $schema_fields[$field->Field] ) ) {
62	// A field to be removed
63	$alters[] = array(
64	'action' => 'DROP',
65	'column' => $field->Field
66	);
67	}
68
69	}
70
71	// Check configured fields with stored fields to check field creations
72	foreach( $schema_fields as $field_id => $field_args ) {
73
74	if( ! isset( $current_schema_fields[$field_id] ) ) {
75	// A field to be added
76	$alters[] = array(
77	'action' => 'ADD',
78	'column' => $field_id
79	);
80
81	} else {
82	// Check changes in field definition
83
84	// Check if key definition has changed
85	if( $field_args['key'] !== $current_schema_fields[$field_id]['key'] ) {
86	$alters[] = array(
87	// Based the action on current key, if is true then ADD, if is false then DROP
88	'action' => ( $field_args['key'] ? 'ADD INDEX' : 'DROP INDEX' ),
89	'column' => $field_id
90	);
91	}
92
93	// TODO: Check the rest of available field args to determine was changed!!!
94	}
95
96	}
97
98	// Queries to be executed at end of checks
99	$queries = array();
100
101	foreach( $alters as $alter ) {
102
103	$column = $alter['column'];
104
105	switch( $alter['action'] ) {
106	case 'ADD':
107	$queries[] = "ALTER TABLE `{$this->ct_db->table_name}` ADD " . $this->schema->field_array_to_schema( $column, $schema_fields[$column] ) . "; ";
108	break;
109	case 'ADD INDEX':
110
111	/*
112	* Indexes have a maximum size of 767 bytes. WordPress 4.2 was moved to utf8mb4, which uses 4 bytes per character.
113	* This means that an index which used to have room for floor(767/3) = 255 characters, now only has room for floor(767/4) = 191 characters.
114	*/
115	$max_index_length = 191;
116
117	if( $schema_fields[$column]['length'] > $max_index_length \|\| $schema_fields[$column]['type'] === 'text' ) {
118	$add_index_query = '`' . $column . '`(`' . $column . '`(' . $max_index_length . '))';
119	} else {
120	$add_index_query = '`' . $column . '`(`' . $column . '`)';
121	}
122
123	// Prevent errors if index already exists
124	drop_index( $this->ct_db->table_name, $column );
125
126	// For indexes query should be executed directly
127	$this->ct_db->db->query( "ALTER TABLE `{$this->ct_db->table_name}` ADD INDEX {$add_index_query}" );
128	break;
129	case 'MODIFY':
130	$queries[] = "ALTER TABLE `{$this->ct_db->table_name}` MODIFY " . $this->schema->field_array_to_schema( $column, $schema_fields[$column] ) . "; ";
131	break;
132	case 'DROP':
133	$queries[] = "ALTER TABLE `{$this->ct_db->table_name}` DROP COLUMN `{$column}`; ";
134
135	// Better use a built-in function here?
136	//maybe_drop_column( $this->ct_db->table_name, $column, "ALTER TABLE `{$this->ct_db->table_name}` DROP COLUMN {$column}" );
137	break;
138	case 'DROP INDEX':
139	// For indexes query should be executed directly
140	//$this->ct_db->db->query( "ALTER TABLE `{$this->ct_db->table_name}` DROP INDEX {$column}" );
141
142	// Use a built-in function for safe drop
143	drop_index( $this->ct_db->table_name, $column );
144	break;
145
146	}
147	}
148
149	if( ! empty( $queries ) ) {
150
151	// Execute the each SQL query
152	foreach( $queries as $sql ) {
153	$updated = $this->ct_db->db->query( $sql );
154	}
155
156	// Was anything updated?
157	return ! empty( $updated );
158	}
159
160	return true;
161
162	}
163
164	}
165
166	/**
167	* Object field returned by the DESCRIBE sentence
168	*
169	* @param stdClass $field stdClass object with next keys:
170	* - string Field Field name
171	* - string Type Field type ("type(length) signed\|unsigned")
172	* - string Null Nullable definition ("YES"\|"NO")
173	* - string Key Key. "PRI" for primary, "MUL" for key definition ("PRI"\|"MUL")
174	* - string\|NULL Default Default definition. A NULL object if not defined. ("Default value"\|NULL)
175	* - string Extra Extra definitions, "auto_increment" for example
176	*
177	* @return array
178	*/
179	public function object_field_to_array( $field ) {
180
181	$field_args = array(
182	'type' => '',
183	'length' => 0,
184	'decimals' => 0, // numeric fields
185	'format' => '', // time fields
186	'options' => array(), // ENUM and SET types
187	'nullable' => (bool) ( $field->Null === 'YES' ),
188	'unsigned' => null, // numeric field
189	'zerofill' => null, // numeric field
190	'binary' => null, // text fields
191	'charset' => false, // text fields
192	'collate' => false, // text fields
193	'default' => false,
194	'auto_increment' => false,
195	'unique' => false,
196	'primary_key' => (bool) ( $field->Key === 'PRI' ),
197	'key' => (bool) ( $field->Key === 'MUL' ),
198	);
199
200	// Determine the field type
201	if( strpos( $field->Type, '(' ) !== false ) {
202	// Check for "type(length)" or "type(length) signed\|unsigned"
203
204	$type_parts = explode( '(', $field->Type );
205
206	$field_args['type'] = $type_parts[0];
207
208	} else if( strpos( $field->Type, ' ' ) !== false ) {
209	// Check for "type signed\|unsigned"
210	$type_parts = explode( ' ', $field->Type );
211
212	$field_args['type'] = $type_parts[0];
213	}
214
215	$field_args['type'] = $field->Type;
216
217	if( strpos( $field->Type, '(' ) !== false ) {
218	// Check for "type(length)" or "type(length) signed\|unsigned"
219
220	$type_parts = explode( '(', $field->Type );
221	$type_part = $type_parts[1];
222
223	$type_definition_parts = explode( ')', $type_part );
224	$type_definition = $type_definition_parts[0];
225
226	if( ! empty( $type_definition ) ) {
227
228	// Determine type definition args
229	switch( strtoupper( $field_args['type'] ) ) {
230	case 'ENUM':
231	case 'SET':
232	$field_args['options'] = explode( ',', $type_definition );
233	break;
234	case 'REAL':
235	case 'DOUBLE':
236	case 'FLOAT':
237	case 'DECIMAL':
238	case 'NUMERIC':
239	if( strpos( $type_definition, ',' ) !== false ) {
240	$decimals = explode( ',', $type_definition );
241
242	$field_args['length'] = $decimals[0];
243	$field_args['decimals'] = $decimals[1];
244	} else if( absint( $type_definition ) !== 0 ) {
245	$field_args['length'] = $type_definition;
246	}
247	break;
248	case 'TIME':
249	case 'TIMESTAMP':
250	case 'DATETIME':
251	$field_args['format'] = $type_definition;
252	break;
253	default:
254	if( absint( $type_definition ) !== 0 ) {
255	$field_args['length'] = $type_definition;
256	}
257	break;
258	}
259
260	}
261
262	}
263
264	// Check for "type signed\|unsigned zerofill ..." or "type(length) signed\|unsigned zerofill ..."
265	$type_definition_parts = explode( ' ', $field->Type );
266
267	// Loop each field definition part to check extra parameters
268	foreach( $type_definition_parts as $type_definition_part ) {
269
270	if( $type_definition_part === 'unsigned' ) {
271	$field_args['unsigned'] = true;
272	}
273
274	if( $type_definition_part === 'signed' ) {
275	$field_args['unsigned'] = false;
276	}
277
278	if( $type_definition_part === 'zerofill' ) {
279	$field_args['zerofill'] = true;
280	}
281
282	if( $type_definition_part === 'binary' ) {
283	$field_args['binary'] = true;
284	}
285
286	}
287
288	return $field_args;
289
290	}
291
292	}
293
294	endif;