wp-data-access / WPDataAccess / Design_Table / WPDA_Design_Table_Form.php

WPDA_Design_Table_Form.php in WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards 5.2, at WPDataAccess/Design_Table/WPDA_Design_Table_Form.php

2,737 lines 102.7 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Suppress "error - 0 - No summary was found for this file" on phpdoc generation
5	*
6	* @package WPDataAccess\Design_Table
7	*/
8
9	namespace WPDataAccess\Design_Table {
10
11	use WPDataAccess\Connection\WPDADB;
12	use WPDataAccess\Data_Dictionary\WPDA_Dictionary_Exist;
13	use WPDataAccess\Data_Dictionary\WPDA_Dictionary_Lists;
14	use WPDataAccess\Data_Dictionary\WPDA_List_Columns_Cache;
15	use WPDataAccess\Utilities\WPDA_Message_Box;
16	use WPDataAccess\Utilities\WPDA_Reverse_Engineering;
17	use WPDataAccess\Plugin_Table_Models\WPDA_Design_Table_Model;
18	use WPDataAccess\WPDA;
19	use WPDataAccess\Dashboard\WPDA_Dashboard;
20
21	/**
22	* Class WPDA_Design_Table_Form
23	*
24	* This class provides the user interface of the Data Designer tool. The form can be used to create or alter table
25	* and index designs. Tables and indexes can be created from their design by using the appropriate buttons.
26	*
27	* Tables and their indexes can be reverse engineered from the database. This process can be started from scratch
28	* or applied to an existing table design. For the last situation users can use the reconcile button. This will
29	* bring the table and index design in the same state as their physical database counterpart.
30	*
31	* @author Peter Schulz
32	* @since 1.1.0
33	*/
34	class WPDA_Design_Table_Form {
35
36	const NEW_LINE = '<br/>';
37
38	/**
39	* Page name (wpda_designer)
40	*
41	* @var string\|null
42	*/
43	protected $page = null;
44
45	/**
46	* Action argument
47	*
48	* @var string\|null
49	*/
50	protected $action = null;
51
52	/**
53	* Saved value of action2 argument used for later equations
54	*
55	* @var string\|null
56	*/
57	protected $action2 = null;
58
59	/**
60	* Action2 argument
61	*
62	* @var string\|null
63	*/
64	protected $action2_posted = null;
65
66	/**
67	* Object of type WPDA_Design_Table_Model used for data manipulation
68	*
69	* @var object\|null
70	*/
71	protected $model = null;
72
73	/**
74	* Table name
75	*
76	* @var string\|null
77	*/
78	protected $wpda_table_name = null;
79
80	/**
81	* Schema name
82	*
83	* @var string\|null
84	*/
85	protected $wpda_schema_name = null;
86
87	/**
88	* Table design (taken from WPDA_Design_Table_Model)
89	*
90	* @var array\|null
91	*/
92	protected $wpda_table_design = null;
93
94	/**
95	* Indicates whether the table was found in the database
96	*
97	* @var boolean\|null
98	*/
99	protected $table_exists = null;
100
101	/**
102	* Indicates whether the structure of the database table equals the table design
103	*
104	* @var boolean\|null
105	*/
106	protected $table_altered = false;
107
108	/**
109	* Indicates whether the table is a WordPress table
110	*
111	* @var boolean\|null
112	*/
113	protected $is_wp_table = false;
114
115	/**
116	* Create table statement for the designed table at startup. Updates in the user interface are not immediately
117	* reflected. User needs to reload/save the page.
118	*
119	* @var string
120	*/
121	protected $create_table_statement = '';
122
123	/**
124	* Alter table statement for the designed table at startup. Updates in the user interface are not immediately
125	* reflected. User needs to reload/save the page.
126	*
127	* @var array
128	*/
129	protected $alter_table_statement = array();
130
131
132	/**
133	* Create index statement(s) for the designed index(es) at startup. Updates in the user interface are not
134	* immediately reflected. User needs to reload/save the page.
135	*
136	* @var string
137	*/
138	protected $create_index_statement = '';
139
140	/**
141	* Indicates where a create table statement succeeded
142	*
143	* @var boolean\|null
144	*/
145	protected $create_table_succeeded = null;
146
147	/**
148	* Indicates where a alter table statement succeeded
149	*
150	* @var boolean\|null
151	*/
152	protected $alter_table_succeeded = null;
153
154	/**
155	* Indicates where a create index statement failed
156	*
157	* @var boolean\|null
158	*/
159	protected $create_index_failed = null;
160
161	/**
162	* Named array holding table columns
163	*
164	* @see WPDA_List_Columns_Cache::get_list_columns()
165	*
166	* @var array\|null
167	*/
168	protected $table_columns = null;
169
170	/**
171	* Allowed values are: Basic or Advanced. Can be supplied as an argument. Taken from WPDA class f no argument
172	* is provided.
173	*
174	* @see WPDA::OPTION_BE_DESIGN_MODE
175	*
176	* @var string\|null
177	*/
178	protected $design_mode = null;
179
180	/**
181	* Last error from wpdb
182	*
183	* @var string\|null
184	*/
185	protected $wpdb_error = null;
186
187	/**
188	* Indicates whether columns or indexes were deleted in the actual design. If true checkbox "Show deleted
189	* columns and indexes" will be accessible.
190	*
191	* @var bool
192	*/
193	protected $deleted_columns_and_indexes = false;
194
195	/**
196	* Indicates whether indexes were updated in the actual design.
197	*
198	* @var bool
199	*/
200	protected $updated_indexes = false;
201
202	/**
203	* Holds the structure of the real physical database table
204	*
205	* @var array\|null
206	*/
207	protected $real_table = null;
208
209	/**
210	* Holds the structure of the real physical database indexes
211	*
212	* @var array\|null
213	*/
214	protected $real_indexes = null;
215
216	/**
217	* Indicates whether the design has indexes.
218	*
219	* @var bool
220	*/
221	protected $indexes_found = false;
222
223	/**
224	* Argument which can be used to jump back to another page than the default table list for table designs.
225	*
226	* @var string
227	*/
228	protected $caller = '';
229
230	/**
231	* Holds all available databases
232	*
233	* @var array
234	*/
235	protected $database = array();
236
237	protected $fulltext_support = false;
238
239	/**
240	* WPDA_Design_Table_Form constructor.
241	*
242	* @since 1.1.0
243	*/
244	public function __construct() {
245	if ( isset( $_REQUEST['page'] ) ) {
246	$this->page = sanitize_text_field( wp_unslash( $_REQUEST['page'] ) ); // input var okay.
247	} else {
248	wp_die( __( 'ERROR: Wrong arguments [page not found]', 'wp-data-access' ) );
249	}
250
251	if ( isset( $_REQUEST['action'] ) ) {
252	$this->action = sanitize_text_field( wp_unslash( $_REQUEST['action'] ) ); // input var okay.
253	}
254
255	if ( isset( $_REQUEST['action2'] ) ) {
256	$this->action2 = sanitize_text_field( wp_unslash( $_REQUEST['action2'] ) ); // input var okay.
257	$this->action2_posted = $this->action2;
258	}
259
260	if ( isset( $_REQUEST['design_mode'] ) ) {
261	$this->design_mode = sanitize_text_field( wp_unslash( $_REQUEST['design_mode'] ) ); // input var okay.
262	} else {
263	$this->design_mode = WPDA::get_option( WPDA::OPTION_BE_DESIGN_MODE ); // Default design mode.
264	}
265
266	if ( isset( $_REQUEST['caller'] ) ) {
267	$this->caller = sanitize_text_field( wp_unslash( $_REQUEST['caller'] ) ); // input var okay.
268	}
269
270	$this->fulltext_support = get_option( 'wpda_fulltext_support' );
271
272	global $wpdb;
273
274	if ( 'init' === $this->action2 ) {
275	if ( isset( $_REQUEST['wpda_table_name'] ) && isset( $_REQUEST['wpda_schema_name'] ) ) {
276	// Check if table is already in repository.
277	$wpdb->get_results(
278	$wpdb->prepare(
279	'select * from `%1s` where wpda_schema_name = %s and wpda_table_name = %s ', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
280	array(
281	WPDA::remove_backticks( WPDA_Design_Table_Model::get_base_table_name() ),
282	sanitize_text_field( wp_unslash( $_REQUEST['wpda_schema_name'] ) ),
283	sanitize_text_field( wp_unslash( $_REQUEST['wpda_table_name'] ) ),
284	)
285	)
286	);
287	if ( 1 === $wpdb->num_rows ) {
288	// Table already in repository.
289	$this->action2 = 'edit';
290	} else {
291	// Table not in repository.
292	$this->action2 = 'wpda_reverse_engineering';
293	}
294	} else {
295	wp_die( __( 'ERROR: Wrong arguments [table not found]', 'wp-data-access' ) );
296	}
297	}
298
299	if ( 'wpda_reverse_engineering' === $this->action2 \|\| 'wpda_reconcile' === $this->action2 ) {
300	if ( isset( $_REQUEST['wpda_table_name_re'] ) && isset( $_REQUEST['wpda_schema_name_re'] ) ) {
301	$wpda_table_name_re = sanitize_text_field( wp_unslash( $_REQUEST['wpda_table_name_re'] ) );
302	$wpda_schema_name_re = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schema_name_re'] ) );
303	if ( 'wpda_reconcile' === $this->action2 ) {
304	// Before table can be reconciled old table structure must be deleted.
305	$wpdb->query(
306	$wpdb->prepare(
307	'delete from `%1s` where wpda_table_name = %s and wpda_schema_name = %s ', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
308	array(
309	WPDA::remove_backticks( WPDA_Design_Table_Model::get_base_table_name() ),
310	$wpda_table_name_re,
311	$wpda_schema_name_re,
312	)
313	)
314	);
315	}
316	// Start reverse engineering tabel.
317	$wpda_reverse_engineering = new WPDA_Reverse_Engineering( $wpda_table_name_re, $wpda_schema_name_re );
318	$this->design_mode = isset( $_REQUEST['design_mode_re'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['design_mode_re'] ) ) : $this->design_mode; // input var okay.
319	$table_structure = $wpda_reverse_engineering->get_designer_format( $this->design_mode );
320	if ( count( $table_structure ) > 0 ) {
321	if ( isset( $_REQUEST['wpda_table_name'] ) && '' !== trim( $_REQUEST['wpda_table_name'] ) ) { // phpcs:ignore WordPress.Security.ValidatedSanitizedInput
322	$this->wpda_table_name = sanitize_text_field( wp_unslash( $_REQUEST['wpda_table_name'] ) );
323	} else {
324	$this->wpda_table_name = $wpda_table_name_re;
325	}
326	if ( isset( $_REQUEST['wpda_schema_name'] ) && '' !== trim( $_REQUEST['wpda_schema_name'] ) ) { // phpcs:ignore WordPress.Security.ValidatedSanitizedInput
327	$this->wpda_schema_name = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schema_name'] ) );
328	} else {
329	$this->wpda_schema_name = $wpda_schema_name_re;
330	}
331	$this->wpda_table_design = $table_structure;
332	} else {
333	wp_die( __( 'ERROR: Reverse engineering table failed [invalid structure]', 'wp-data-access' ) );
334	}
335	if ( ! WPDA_Design_Table_Model::insert_reverse_engineered( $this->wpda_table_name, $this->wpda_schema_name, $this->wpda_table_design ) ) {
336	wp_die( __( 'ERROR: Reverse engineering table failed [insert failed]', 'wp-data-access' ) );
337	} else {
338	// Convert named array to object (needed to display structure).
339	$this->wpda_table_design = json_decode( json_encode( $table_structure ) );
340	}
341	$this->action2 = 'edit';
342	} else {
343	wp_die( __( 'ERROR: Wrong arguments [table not found]', 'wp-data-access' ) );
344	}
345	} elseif ( isset( $_REQUEST['wpda_table_name'] ) && isset( $_REQUEST['wpda_schema_name'] ) ) {
346	$this->wpda_table_name = sanitize_text_field( wp_unslash( $_REQUEST['wpda_table_name'] ) );
347	$this->wpda_schema_name = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schema_name'] ) );
348	$this->model = new WPDA_Design_Table_Model();
349
350	if ( 'new' === $this->action2 ) {
351	$insert_result = $this->model->insert();
352	if ( false === $insert_result \|\| $insert_result < 1 ) {
353	wp_die( __( 'ERROR: Insert failed', 'wp-data-access' ) );
354	}
355	$this->action2 = 'edit'; // Show saved records and allow editing.
356	} elseif ( 'edit' === $this->action2 && 'init' !== $this->action2_posted ) {
357	$result_update = $this->model->update();
358	if ( false === $result_update ) {
359	$msg = new WPDA_Message_Box(
360	array(
361	'message_text' => __( 'Update failed', 'wp-data-access' ),
362	'message_type' => 'error',
363	'message_is_dismissible' => false,
364	)
365	);
366	$msg->box();
367	}
368	if ( 0 === $result_update ) {
369	$msg = new WPDA_Message_Box(
370	array(
371	'message_text' => __( 'Nothing to save', 'wp-data-access' ),
372	)
373	);
374	$msg->box();
375	} else {
376	$msg = new WPDA_Message_Box(
377	array(
378	'message_text' => __( 'Succesfully saved changes to database', 'wp-data-access' ),
379	)
380	);
381	$msg->box();
382	}
383	}
384
385	$this->model->query();
386	$structure_messages = $this->model->validate();
387	foreach ( $structure_messages as $messages ) {
388	if ( 'ERR' === $messages[0] ) {
389	$msg = new WPDA_Message_Box(
390	array(
391	'message_text' => $messages[1],
392	'message_type' => 'error',
393	'message_is_dismissible' => false,
394	)
395	);
396	$msg->box();
397	} else {
398	$msg = new WPDA_Message_Box(
399	array(
400	'message_text' => $messages[1],
401	)
402	);
403	$msg->box();
404	}
405	}
406	$this->wpda_table_design = $this->model->get_table_design();
407	$this->design_mode = $this->wpda_table_design->design_mode;
408
409	$this->action2 = 'edit'; // Editing mode.
410	} else {
411	$this->action2 = 'new'; // Design new table from scratch.
412	}
413
414	// Remove backticks from schema and table name to prevent sql injection
415	$this->wpda_schema_name = str_replace( '`', '', $this->wpda_schema_name );
416	$this->wpda_table_name = str_replace( '`', '', $this->wpda_table_name );
417
418	if ( null !== $this->wpda_table_name && null !== $this->wpda_schema_name ) {
419	// Check if table name already exists in database.
420	if ( $this->wpda_schema_name === $wpdb->dbname ) {
421	$wp_tables = $wpdb->tables( 'all', true );
422	if ( isset( $wp_tables[ substr( $this->wpda_table_name, strlen( $wpdb->prefix ) ) ] ) ) {
423	$this->is_wp_table = true;
424	$this->table_exists = true;
425	} else {
426	$this->does_table_exist();
427	}
428	} else {
429	$this->does_table_exist();
430	}
431
432	// Get design structure for real database table.
433	$this->get_table_structure();
434
435	if (
436	'create_table' === $this->action2_posted \|\|
437	'show_create_table_script' === $this->action2_posted
438	) {
439	// Perform create table (and indexes) script.
440	$this->create_table();
441	$this->does_table_exist();
442	$this->get_table_structure();
443	} elseif ( 'alter_table' === $this->action2_posted ) {
444	// Generate alter table script and perform script.
445	$this->do_alter_table();
446	$this->get_table_structure();
447	} elseif ( 'show_alter_table_script' === $this->action2_posted ) {
448	// Generate alter table script and show result in overlay.
449	$this->alter_table();
450	} elseif ( 'create_table_index' === $this->action2_posted ) {
451	// Perform create index(es) script.
452	$this->create_index();
453	$this->get_table_structure();
454	} elseif ( 'drop_table' === $this->action2_posted ) {
455	// Perform drop table script.
456	$this->drop_table();
457	$this->does_table_exist();
458	} elseif ( 'drop_table_index' === $this->action2_posted ) {
459	// Perform drop table script.
460	$this->drop_indexes();
461	$this->get_table_structure();
462	}
463
464	if ( $this->table_exists ) {
465	// Get database table structure.
466	$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $this->wpda_schema_name, $this->wpda_table_name );
467	$table_columns = $wpda_list_columns->get_table_columns();
468
469	// Convert indexed array to named array to improve access.
470	foreach ( $table_columns as $table_column ) {
471	$this->table_columns[ $table_column['column_name'] ] = $table_column;
472	}
473	}
474	}
475
476	$this->databases = WPDA_Dictionary_Lists::get_db_schemas();
477	}
478
479	public function prepare_form() {}
480
481	/**
482	* Get table and index structure from design
483	*
484	* @since 2.0.14
485	*/
486	private function get_table_structure() {
487	if ( $this->table_exists ) {
488	$get_table_structure = new WPDA_Reverse_Engineering( $this->wpda_table_name, $this->wpda_schema_name );
489	$real_structure = $get_table_structure->get_designer_format( $this->design_mode );
490	$this->real_table = $real_structure['table'];
491	$this->real_indexes = $real_structure['indexes'];
492	}
493	}
494
495	/**
496	* Check if table exists in our database
497	*
498	* @since 2.0.14
499	*/
500	private function does_table_exist() {
501	$wpda_dictionary_exists = new WPDA_Dictionary_Exist( $this->wpda_schema_name, $this->wpda_table_name );
502	$this->table_exists = $wpda_dictionary_exists->plain_table_exists();
503	if ( ! $this->table_exists ) {
504	$this->real_table = null;
505	$this->real_indexes = null;
506	}
507	}
508
509	/**
510	* Perform alter table
511	*
512	* Call $this->alter_table() to generate alter table script and process result taken
513	* from $this->create_table_statement.
514	*
515	* @see WPDA_Design_Table_Form::alter_table()
516	* @see WPDA_Design_Table_Form::create_table_statement
517	*
518	* @since 2.0.14
519	*/
520	protected function do_alter_table() {
521	$this->alter_table();
522
523	$wpdadb = WPDADB::get_db_connection( $this->wpda_schema_name );
524	if ( null === $wpdadb ) {
525	wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->wpda_schema_name ) ) );
526	}
527
528	$suppress = $wpdadb->suppress_errors( true );
529	$create_table_statement = str_replace( self::NEW_LINE, "\n", $this->create_table_statement );
530	if ( '' !== $create_table_statement ) {
531	// Process alter table script (store in $create_table_statement)
532	$sql_end = strpos( $create_table_statement, ";\n" );
533	while ( false !== $sql_end ) {
534	$sql = rtrim( substr( $create_table_statement, 0, $sql_end ) );
535	$create_table_statement = substr( $create_table_statement, strpos( $create_table_statement, $sql ) + strlen( $sql ) + 1 );
536	if ( ! $wpdadb->query( $sql ) ) {
537	$this->wpdb_error = $wpdadb->last_error;
538	$this->alter_table_succeeded = false;
539
540	return;
541	}
542	$sql_end = strpos( $create_table_statement, ";\n" );
543	}
544	}
545	$wpdadb->suppress_errors( $suppress );
546	}
547
548	/**
549	* Generate alter table script
550	*
551	* Alter table script is written to $this->create_table_statement.
552	*
553	* @see WPDA_Design_Table_Form::create_table_statement
554	*
555	* @since 2.0.14
556	*/
557	protected function alter_table() {
558	$create_keys_design = array();
559	$create_keys_real = array();
560
561	// Find deleted and changed indexes
562	foreach ( $this->real_indexes as $real_index ) {
563	$drop_real_index = false;
564	$design_index_found = false;
565	foreach ( $this->wpda_table_design->indexes as $design_index ) {
566	if ( $real_index['index_name'] === $design_index->index_name ) {
567	$design_index_found = true;
568	if (
569	$real_index['unique'] != $design_index->unique \|\|
570	$real_index['column_names'] != $design_index->column_names
571	) {
572	$drop_real_index = true;
573	break;
574	}
575	break;
576	}
577	}
578	if ( $drop_real_index \|\| ! $design_index_found ) {
579	$this->create_table_statement .=
580	'DROP INDEX `' . str_replace( '`', '', $real_index['index_name'] ) . "` ON `{$this->wpda_table_name}`;" . self::NEW_LINE;
581	}
582	}
583	if ( '' !== $this->create_table_statement ) {
584	$this->create_table_statement .= self::NEW_LINE;
585	}
586
587	// Find new and changed columns
588	foreach ( $this->wpda_table_design->table as $design_column ) {
589	$design_column_found = false;
590	foreach ( $this->real_table as $real_column ) {
591	if ( $real_column->column_name === $design_column->column_name ) {
592	if ( $real_column != $design_column ) {
593	// Modify column
594	$this->alter_table_column( $design_column, 'MODIFY' );
595	}
596	$design_column_found = true;
597	break;
598	}
599	}
600
601	if ( ! $design_column_found ) {
602	// Add new column
603	$this->alter_table_column( $design_column, 'ADD' );
604	}
605
606	if ( 'Yes' === $design_column->key ) {
607	$create_keys_design[] = $design_column->column_name;
608	}
609	}
610
611	// Find deleted columns
612	foreach ( $this->real_table as $real_column ) {
613	$real_column_found = false;
614	foreach ( $this->wpda_table_design->table as $design_column ) {
615	if ( $real_column->column_name === $design_column->column_name ) {
616	$real_column_found = true;
617	break;
618	}
619	}
620
621	if ( ! $real_column_found ) {
622	// Drop column
623	array_push(
624	$this->alter_table_statement,
625	'DROP COLUMN `' . str_replace( '`', '', $real_column->column_name ) . '`,' . self::NEW_LINE
626	);
627	}
628
629	if ( 'Yes' === $real_column->key ) {
630	$create_keys_real[] = $real_column->column_name;
631	}
632	}
633
634	if ( 0 < count( $this->alter_table_statement ) ) {
635	$this->create_table_statement .= "ALTER TABLE `{$this->wpda_table_name}` ";
636	foreach ( $this->alter_table_statement as $sql ) {
637	$this->create_table_statement .= $sql;
638	}
639	$this->create_table_statement =
640	substr( $this->create_table_statement, 0, strrpos( $this->create_table_statement, ',' ) ) .
641	';' . self::NEW_LINE . self::NEW_LINE;
642
643	$array_difference_1 = array_diff( $create_keys_design, $create_keys_real );
644	$array_difference_2 = array_diff( $create_keys_real, $create_keys_design );
645	if ( 0 !== count( $array_difference_1 ) \|\| 0 !== count( $array_difference_2 ) ) {
646	if ( 0 < count( $create_keys_real ) ) {
647	$this->create_table_statement =
648	"ALTER TABLE `{$this->wpda_table_name}` DROP PRIMARY KEY;" .
649	self::NEW_LINE . self::NEW_LINE . $this->create_table_statement;
650	}
651	if ( 0 < count( $create_keys_design ) ) {
652	$alter_table_statement =
653	"ALTER TABLE `{$this->wpda_table_name}` ADD PRIMARY KEY ";
654	foreach ( $create_keys_design as $key ) {
655	$alter_table_statement .= $key === reset( $create_keys_design ) ? '(' : ',';
656	$alter_table_statement .= '`' . str_replace( '`', '', $key ) . '`';
657	}
658	$alter_table_statement .= ');' . self::NEW_LINE . self::NEW_LINE;
659
660	$this->create_table_statement .= $alter_table_statement;
661	}
662	}
663	}
664
665	// Add new and changed indexes
666	foreach ( $this->wpda_table_design->indexes as $design_index ) {
667	$real_index_found = false;
668	$create_new_index = false;
669	foreach ( $this->real_indexes as $real_index ) {
670	if ( $real_index['index_name'] === $design_index->index_name ) {
671	$real_index_found = true;
672	if (
673	$real_index['unique'] != $design_index->unique \|\|
674	$real_index['column_names'] != $design_index->column_names
675	) {
676	$create_new_index = true;
677	break;
678	}
679	break;
680	}
681	}
682	if ( ! $real_index_found \|\| $create_new_index ) {
683	$unique = '';
684	if ( 'Yes' === $design_index->unique ) {
685	$unique = 'UNIQUE';
686	} elseif ( 'FULLTEXT' === $design_index->unique ) {
687	if ( 'on' === $this->fulltext_support ) {
688	$unique = 'FULLTEXT';
689	} else {
690	$unique = '';
691	}
692	}
693	$column_names_array = explode( ',', $design_index->column_names );
694	$column_names = '`' . implode( '`,`', $column_names_array ) . '`';
695	$this->create_table_statement .=
696	"CREATE $unique INDEX `" . str_replace( '`', '', $design_index->index_name ) . "` ON `{$this->wpda_table_name}` ($column_names);" .
697	self::NEW_LINE;
698	}
699	}
700	}
701
702	/**
703	* Alter column format
704	*
705	* @param object $design_column Column definition
706	* @param string $keyword ADD or MODIFY
707	*
708	* @since 2.0.14
709	*/
710	private function alter_table_column( $design_column, $keyword ) {
711	$alter_table_statement = "$keyword COLUMN `" . str_replace( '`', '', $design_column->column_name ) . '` ';
712	$alter_table_statement .= $design_column->data_type;
713	if ( '' !== $design_column->max_length ) {
714	$alter_table_statement .= "($design_column->max_length)";
715	}
716	if ( 'enum' === $design_column->data_type \|\| 'set' === $design_column->data_type ) {
717	$alter_table_statement .= '(' . $design_column->list . ')';
718	}
719	$alter_table_statement .= ' ';
720	$alter_table_statement .= 'Yes' === $design_column->mandatory ? 'NOT NULL' : 'NULL';
721	if ( '' !== $design_column->default ) {
722	$alter_table_statement .= " DEFAULT {$design_column->default}";
723	}
724	if ( '' !== $design_column->extra ) {
725	$alter_table_statement .= ' ';
726	$alter_table_statement .= $design_column->extra;
727	}
728	$alter_table_statement .= ',' . self::NEW_LINE;
729
730	array_push(
731	$this->alter_table_statement,
732	$alter_table_statement
733	);
734	}
735
736	/**
737	* Drop database table
738	*
739	* Does not drop WordPress tables.
740	*
741	* @since 2.0.14
742	*/
743	protected function drop_table() {
744	if ( $this->is_wp_table ) {
745	$msg = new WPDA_Message_Box(
746	array(
747	'message_text' => sprintf( __( 'Cannot drop WordPress table `%s`', 'wp-data-access' ), $this->wpda_table_name ),
748	'message_type' => 'error',
749	'message_is_dismissible' => false,
750	)
751	);
752	$msg->box();
753
754	return;
755	}
756
757	if ( $this->table_exists ) {
758	$wpdadb = WPDADB::get_db_connection( $this->wpda_schema_name );
759	if ( null === $wpdadb ) {
760	wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->wpda_schema_name ) ) );
761	}
762
763	$suppress = $wpdadb->suppress_errors( true );
764	$drop_table_statement = "DROP TABLE `{$this->wpda_table_name}`";
765	if ( $wpdadb->query( $drop_table_statement ) ) {
766	$msg = new WPDA_Message_Box(
767	array(
768	'message_text' => sprintf( __( 'Table `%s` dropped', 'wp-data-access' ), $this->wpda_table_name ),
769	)
770	);
771	$msg->box();
772	} else {
773	$msg = new WPDA_Message_Box(
774	array(
775	'message_text' => __( 'DROP TABLE failed', 'wp-data-access' ),
776	'message_type' => 'error',
777	'message_is_dismissible' => false,
778	)
779	);
780	$msg->box();
781	$this->create_table_statement = $drop_table_statement;
782	}
783	$wpdadb->suppress_errors( $suppress );
784	}
785	}
786
787	/**
788	* Perform create table statement
789	*
790	* @since 1.1.0
791	*/
792	protected function create_table() {
793	$is_valid = true;
794	$this->create_table_statement = "CREATE TABLE `{$this->wpda_table_name}`" . self::NEW_LINE;
795
796	$create_keys = array();
797	foreach ( $this->wpda_table_design->table as $row ) {
798	$this->create_table_statement .= $row === reset( $this->wpda_table_design->table ) ? '(' : ',';
799	$this->create_table_statement .= '`' . str_replace( '`', '', $row->column_name ) . '`';
800	$this->create_table_statement .= ' ';
801	$this->create_table_statement .= $row->data_type;
802	if ( '' !== $row->max_length ) {
803	$this->create_table_statement .= "($row->max_length)";
804	} else {
805	if (
806	'varchar' === $row->data_type \|\|
807	'char' === $row->data_type \|\|
808	'varbinary' === $row->data_type \|\|
809	'binary' === $row->data_type
810	) {
811	$is_valid = false;
812	$msg = new WPDA_Message_Box(
813	array(
814	'message_text' => "Column {$row->column_name} of {$row->data_type} must have a max length",
815	'message_type' => 'error',
816	'message_is_dismissible' => false,
817	)
818	);
819	$msg->box();
820	}
821	}
822	if ( '' !== $row->type_attribute ) {
823	$this->create_table_statement .= " {$row->type_attribute} ";
824	}
825	if ( 'enum' === $row->data_type \|\| 'set' === $row->data_type ) {
826	$this->create_table_statement .= '(' . $row->list . ')';
827	}
828	$this->create_table_statement .= ' ';
829	$this->create_table_statement .= 'Yes' === $row->mandatory ? 'NOT NULL' : 'NULL';
830	if ( '' !== $row->default ) {
831	$this->create_table_statement .= " DEFAULT {$row->default}";
832	}
833	if ( '' !== $row->extra ) {
834	$this->create_table_statement .= ' ';
835	$this->create_table_statement .= $row->extra;
836	}
837	if ( 'Yes' === $row->key ) {
838	$create_keys[] = $row->column_name;
839	}
840	$this->create_table_statement .= self::NEW_LINE;
841	}
842	if ( 0 < count( $create_keys ) ) {
843	$this->create_table_statement .= ',PRIMARY KEY ';
844	foreach ( $create_keys as $key ) {
845	$this->create_table_statement .= $key === reset( $create_keys ) ? '(' : ',';
846	$this->create_table_statement .= '`' . str_replace( '`', '', $key ) . '`';
847	}
848	$this->create_table_statement .= ')';
849	$this->create_table_statement .= self::NEW_LINE;
850	}
851	$this->create_table_statement .= ')';
852	if ( isset( $this->wpda_table_design->engine ) && '' !== $this->wpda_table_design->engine ) {
853	$this->create_table_statement .= ' ENGINE ' . $this->wpda_table_design->engine;
854	}
855	if ( isset( $this->wpda_table_design->collation ) && '' !== $this->wpda_table_design->collation ) {
856	$collation = explode( '_', $this->wpda_table_design->collation );
857	$this->create_table_statement .= ' DEFAULT CHARACTER SET ' . $collation[0] . ' COLLATE=' . $this->wpda_table_design->collation;
858	}
859	$this->create_table_statement .= ';' . self::NEW_LINE . self::NEW_LINE;
860
861	if ( ! $is_valid ) {
862	return;
863	}
864
865	if ( 'show_create_table_script' === $this->action2_posted ) {
866	// Just show CREATE TABLE script!
867	// SQL script is available in $this->create_table_statement and can be shown on the page.
868	$this->create_index();
869	} else {
870	// Create table and indexes.
871	$wpdadb = WPDADB::get_db_connection( $this->wpda_schema_name );
872	if ( null === $wpdadb ) {
873	wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->wpda_schema_name ) ) );
874	}
875
876	$suppress = $wpdadb->suppress_errors( true );
877
878	$this->create_table_succeeded = $wpdadb->query( str_replace( self::NEW_LINE, '', $this->create_table_statement ) );
879	if ( $this->create_table_succeeded ) {
880	$msg = new WPDA_Message_Box(
881	array(
882	'message_text' => __( 'Table created', 'wp-data-access' ),
883	)
884	);
885	$msg->box();
886	} else {
887	$msg = new WPDA_Message_Box(
888	array(
889	'message_text' => __( 'CREATE TABLE failed', 'wp-data-access' ),
890	'message_type' => 'error',
891	'message_is_dismissible' => false,
892	)
893	);
894	$msg->box();
895
896	$this->wpdb_error = $wpdadb->last_error;
897	}
898
899	$wpdadb->suppress_errors( $suppress );
900	}
901	}
902
903	/**
904	* Show Data Designer form
905	*
906	* @since 1.1.0
907	*/
908	public function show() {
909	?>
910	<script type='text/javascript'>
911	var row_num = 1;
912	var index_num = 1;
913
914	var table_updated = false;
915	var index_updated = false;
916
917	var no_cols_selected = 'no column(s) selected';
918
919	function disable_page() {
920	jQuery(".wpda_view").prop("readonly", true).prop("disabled", true).addClass("disabled");
921	disable_table();
922	disable_index();
923	disable_create_buttons();
924	jQuery('#reconcile_button').prop("readonly", true).prop("disabled", true).addClass("disabled");
925	jQuery('.design_mode').prop("readonly", true).prop("disabled", true).addClass("disabled");
926	jQuery('.column_names').prop("readonly", true).prop("disabled", true).addClass("disabled");
927	}
928
929	function disable_table() {
930	jQuery(".wpda_view_table").prop("readonly", true).prop("disabled", true).addClass("disabled");
931	}
932
933	function disable_index() {
934	jQuery(".wpda_view_index").prop("readonly", true).prop("disabled", true).addClass("disabled");
935	}
936
937	function disable_create_buttons() {
938	jQuery("#button_show_create_table").prop("readonly", true).prop("disabled", true).addClass("disabled");
939	jQuery("#button_show_alter_table").prop("readonly", true).prop("disabled", true).addClass("disabled");
940	jQuery("#button_create_table").prop("readonly", true).prop("disabled", true).addClass("disabled");
941	jQuery("#button_alter_table").prop("readonly", true).prop("disabled", true).addClass("disabled");
942	jQuery("#button_create_index").prop("readonly", true).prop("disabled", true).addClass("disabled");
943	jQuery("#button_recreate_index").prop("readonly", true).prop("disabled", true).addClass("disabled");
944	}
945
946	function rem_row(e) {
947	var curr_id = e.target.parentNode.parentNode.id;
948	if (confirm("Delete column?")) {
949	jQuery("#" + curr_id).remove();
950	updated_table();
951	}
952	}
953
954	function rem_index(e) {
955	var curr_id = e.target.parentNode.parentNode.id;
956	if (confirm("Delete index?")) {
957	jQuery("#" + curr_id).remove();
958	updated_indexes();
959	}
960	}
961
962	function updated_table() {
963	disable_create_buttons();
964	disable_index();
965	table_updated = true;
966	}
967
968	function updated_indexes() {
969	disable_create_buttons();
970	disable_table();
971	index_updated = true;
972	}
973
974	function check_basic_data_type(row_num) {
975	check_numeric_items_off();
976	switch (jQuery('#basic_data_type_' + row_num).val()) {
977	case 'Text':
978	jQuery('#data_type_' + row_num).val('text');
979	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
980	break;
981	case 'Integer':
982	jQuery('#data_type_' + row_num).val('int');
983	jQuery('#max_length_' + row_num).attr('class', 'wpda_digits_only wpda_view_table');
984	break;
985	case 'Real':
986	jQuery('#data_type_' + row_num).val('float');
987	jQuery('#max_length_' + row_num).attr('class', 'wpda_real wpda_view_table');
988	break;
989	case 'List':
990	jQuery('#data_type_' + row_num).val('enum');
991	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
992	break;
993	case 'Boolean':
994	jQuery('#data_type_' + row_num).val('tinyint');
995	jQuery('#max_length_' + row_num).attr('class', 'wpda_digits_only wpda_view_table');
996	break;
997	case 'Datetime':
998	jQuery('#data_type_' + row_num).val('datetime');
999	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
1000	break;
1001	case 'Binary':
1002	jQuery('#data_type_' + row_num).val('binary');
1003	jQuery('#max_length_' + row_num).attr('class', 'wpda_digits_only wpda_view_table');
1004	break;
1005	case 'Blob':
1006	jQuery('#data_type_' + row_num).val('blob');
1007	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
1008	break;
1009	case '*ID':
1010	jQuery('#data_type_' + row_num).val('int');
1011	jQuery('#key_' + row_num).val('Yes');
1012	jQuery('#mandatory_' + row_num).val('Yes');
1013	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_digits_only wpda_view_table');
1014	jQuery('#extra_' + row_num).val('AUTO_INCREMENT');
1015	jQuery('#default_' + row_num).val('');
1016	jQuery('#list_' + row_num).val('');
1017	break;
1018	case '*TimestampC':
1019	jQuery('#data_type_' + row_num).val('timestamp');
1020	jQuery('#key_' + row_num).val('No');
1021	jQuery('#mandatory_' + row_num).val('No');
1022	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
1023	jQuery('#extra_' + row_num).val('');
1024	jQuery('#default_' + row_num).val('CURRENT_TIMESTAMP');
1025	jQuery('#list_' + row_num).val('');
1026	break;
1027	case '*TimestampU':
1028	jQuery('#data_type_' + row_num).val('timestamp');
1029	jQuery('#key_' + row_num).val('No');
1030	jQuery('#mandatory_' + row_num).val('No');
1031	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
1032	jQuery('#extra_' + row_num).val('ON UPDATE CURRENT_TIMESTAMP');
1033	jQuery('#default_' + row_num).val('CURRENT_TIMESTAMP');
1034	jQuery('#list_' + row_num).val('');
1035	break;
1036	}
1037	check_numeric_items_on();
1038	}
1039
1040	function check_data_type(row_num) {
1041	check_numeric_items_off();
1042	switch (jQuery('#data_type_' + row_num + ' :selected').parent().attr('label')) {
1043	case 'Integer':
1044	jQuery('#basic_data_type_' + row_num).val('Integer');
1045	jQuery('#max_length_' + row_num).attr('class', 'wpda_digits_only wpda_view_table');
1046	break;
1047	case 'Text':
1048	jQuery('#basic_data_type_' + row_num).val('Text');
1049	switch (jQuery('#data_type_' + row_num + ' :selected').val()) {
1050	case 'char':
1051	case 'varchar':
1052	jQuery('#max_length_' + row_num).attr('class', 'wpda_digits_only wpda_view_table');
1053	break;
1054	default:
1055	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
1056	}
1057	break;
1058	case 'List':
1059	jQuery('#basic_data_type_' + row_num).val('List');
1060	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
1061	break;
1062	case 'Date and Time':
1063	jQuery('#basic_data_type_' + row_num).val('Datetime');
1064	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
1065	break;
1066	case 'Real':
1067	jQuery('#basic_data_type_' + row_num).val('Real');
1068	jQuery('#max_length_' + row_num).attr('class', 'wpda_real wpda_view_table');
1069	break;
1070	case 'Binary':
1071	switch (jQuery('#data_type_' + row_num + ' :selected').val()) {
1072	case 'binary':
1073	case 'varbinary':
1074	jQuery('#basic_data_type_' + row_num).val('Binary');
1075	jQuery('#max_length_' + row_num).attr('class', 'wpda_digits_only wpda_view_table');
1076	break;
1077	default:
1078	jQuery('#basic_data_type_' + row_num).val('Blob');
1079	jQuery('#max_length_' + row_num).val('').attr('class', 'wpda_nodataentryallowed wpda_view_table');
1080	}
1081	break;
1082	case 'Boolean':
1083	jQuery('#basic_data_type_' + row_num).val('Boolean');
1084	jQuery('#max_length_' + row_num).attr('class', 'wpda_digits_only wpda_view_table');
1085	break;
1086	}
1087	check_numeric_items_on();
1088	}
1089
1090	function check_numeric_items_off() {
1091	jQuery('.wpda_digits_only').off('keyup paste');
1092	jQuery('.wpda_real').off('keyup paste');
1093	jQuery('.wpda_nodataentryallowed').off('keyup paste');
1094	}
1095
1096	function check_numeric_items_on() {
1097	jQuery('.wpda_digits_only').on('keyup paste', function () {
1098	this.value = this.value.replace(/[^\d]/g, '');
1099	});
1100	jQuery('.wpda_real').on('keyup paste', function () {
1101	this.value = this.value.replace(/[^0-9,]/g, '');
1102	});
1103	jQuery('.wpda_nodataentryallowed').on('keyup paste', function () {
1104	this.value = '';
1105	});
1106	}
1107
1108	function pre_submit() {
1109	if (jQuery('#wpda_table_name').val() === '') {
1110	alert('<?php echo __( 'Table name cannot be empty' ); ?>');
1111	return false;
1112	}
1113	if ('<?php echo esc_attr( $this->action2 ); ?>' === 'new') {
1114	if (wpda_db_table_name[jQuery('#wpda_table_name').val()]) {
1115	alert('<?php echo __( 'Table name already used for another table design' ); ?>');
1116	return false;
1117	}
1118	}
1119	var all_columns_entered = true;
1120	jQuery("input[name='column_name[]']").each(function () {
1121	if (jQuery(this).val() === '') {
1122	alert('<?php echo __( 'Column names cannot be empty' ); ?>');
1123	all_columns_entered = false;
1124	}
1125	});
1126	if (!all_columns_entered) {
1127	return false;
1128	}
1129	// Enable all listboxes that have been disable to add arguments to request.
1130	jQuery("select[id^='key']").each(function () {
1131	jQuery(this).attr('disabled', false);
1132	});
1133	jQuery("select[id^='mandatory']").each(function () {
1134	jQuery(this).attr('disabled', false);
1135	});
1136	return true;
1137	}
1138
1139	function add_row(design_mode, init = false, column_name = '', basic_data_type = '', data_type = '', type_attribute = '', key = '', mandatory = '', max_length = '', extra = '', default_value = '', list = '', row_action = '') {
1140	var row_class = '';
1141	switch (row_action) {
1142	case 'u':
1143	row_class = 'wpda_column_updated';
1144	break;
1145	case 'i':
1146	row_class = 'wpda_column_inserted';
1147	break;
1148	case 'd':
1149	row_class = 'wpda_column_deleted';
1150	}
1151	var basic_columns = '<td>' +
1152	'<select name="basic_data_type[]" id="basic_data_type_' + row_num + '" onchange="check_basic_data_type(' + row_num + ')" class="wpda_view_table">' +
1153	' <option value="Text" ' + (basic_data_type === 'Text' ? 'selected' : '') + '>Text</option>' +
1154	' <option value="Integer" ' + (basic_data_type === 'Integer' ? 'selected' : '') + '>Integer</option>' +
1155	' <option value="Real" ' + (basic_data_type === 'Real' ? 'selected' : '') + '>Real</option>' +
1156	' <option value="List" ' + (basic_data_type === 'List' ? 'selected' : '') + '}>List</option>' +
1157	' <option value="Boolean" ' + (basic_data_type === 'Boolean' ? 'selected' : '') + '>Boolean</option>' +
1158	' <option value="Datetime" ' + (basic_data_type === 'Datetime' ? 'selected' : '') + '>Datetime</option>' +
1159	' <option value="Binary" ' + (basic_data_type === 'Binary' ? 'selected' : '') + '>Binary</option>' +
1160	' <option value="Blob" ' + (basic_data_type === 'Blob' ? 'selected' : '') + '>Blob</option>' +
1161	' <option value="ID" ' + (basic_data_type === 'ID' ? 'selected' : '') + '>* Numeric ID (auto increment)</option>' +
1162	' <option value="TimestampC" ' + (basic_data_type === 'TimestampC' ? 'selected' : '') + '>* Timestamp (date created)</option>' +
1163	' <option value="TimestampU" ' + (basic_data_type === 'TimestampU' ? 'selected' : '') + '>* Timestamp (last updated)</option>' +
1164	'</select>' +
1165	'<input type="hidden" name="data_type[]" id="data_type_' + row_num + '" value="' + data_type + '">' +
1166	'<input type="hidden" name="type_attribute[]" id="type_attribute_' + row_num + '" value="' + type_attribute + '">' +
1167	'</td>';
1168	var advanced_columns = '<td>' +
1169	'<select name="data_type[]" id="data_type_' + row_num + '" onchange="check_data_type(' + row_num + ')" class="wpda_view_table">' +
1170	' <option value="" ' + (data_type === '' ? 'selected' : '') + '></option>' +
1171	' <optgroup label="Integer">' +
1172	' <option value="bit" ' + (data_type === 'bit' ? 'selected' : '') + '>bit</option>' +
1173	' <option value="tinyint" ' + (data_type === 'tinyint' ? 'selected' : '') + '>tinyint</option>' +
1174	' <option value="smallint" ' + (data_type === 'smallint' ? 'selected' : '') + '>smallint</option>' +
1175	' <option value="mediumint" ' + (data_type === 'mediumint' ? 'selected' : '') + '>mediumint</option>' +
1176	' <option value="int" ' + (data_type === 'int' ? 'selected' : '') + '>int</option>' +
1177	' <option value="bigint" ' + (data_type === 'bigint' ? 'selected' : '') + '>bigint</option>' +
1178	' </optgroup>' +
1179	' <optgroup label="Text">' +
1180	' <option value="char" ' + (data_type === 'char' ? 'selected' : '') + '>char</option>' +
1181	' <option value="varchar" ' + (data_type === 'varchar' ? 'selected' : '') + '>varchar</option>' +
1182	' <option disabled="disabled">-</option>' +
1183	' <option value="tinytext" ' + (data_type === 'tinytext' ? 'selected' : '') + '>tinytext</option>' +
1184	' <option value="text" ' + (data_type === 'text' ? 'selected' : '') + '>text</option>' +
1185	' <option value="mediumtext" ' + (data_type === 'mediumtext' ? 'selected' : '') + '>mediumtext</option>' +
1186	' <option value="longtext" ' + (data_type === 'longtext' ? 'selected' : '') + '>longtext</option>' +
1187	' </optgroup>' +
1188	' <optgroup label="List">' +
1189	' <option value="enum" ' + (data_type === 'enum' ? 'selected' : '') + '>enum</option>' +
1190	' <option value="set" ' + (data_type === 'set' ? 'selected' : '') + '>set</option>' +
1191	' </optgroup>' +
1192	' <optgroup label="Date and Time">' +
1193	' <option value="date" ' + (data_type === 'date' ? 'selected' : '') + '>date</option>' +
1194	' <option value="datetime" ' + (data_type === 'datetime' ? 'selected' : '') + '>datetime</option>' +
1195	' <option value="timestamp" ' + (data_type === 'timestamp' ? 'selected' : '') + '>timestamp</option>' +
1196	' <option value="time" ' + (data_type === 'time' ? 'selected' : '') + '>time</option>' +
1197	' <option value="year" ' + (data_type === 'year' ? 'selected' : '') + '>year</option>' +
1198	' </optgroup>' +
1199	' <optgroup label="Real">' +
1200	' <option value="decimal" ' + (data_type === 'decimal' ? 'selected' : '') + '>decimal</option>' +
1201	' <option value="double" ' + (data_type === 'double' ? 'selected' : '') + '>double</option>' +
1202	' <option value="float" ' + (data_type === 'float' ? 'selected' : '') + '>float</option>' +
1203	' </optgroup>' +
1204	' <optgroup label="Binary">' +
1205	' <option value="binary" ' + (data_type === 'binary' ? 'selected' : '') + '>binary</option>' +
1206	' <option value="varbinary" ' + (data_type === 'varbinary' ? 'selected' : '') + '>varbinary</option>' +
1207	' <option disabled="disabled">-</option>' +
1208	' <option value="tinyblob" ' + (data_type === 'tinyblob' ? 'selected' : '') + '>tinyblob</option>' +
1209	' <option value="blob" ' + (data_type === 'blob' ? 'selected' : '') + '>blob</option>' +
1210	' <option value="mediumblob" ' + (data_type === 'mediumblob' ? 'selected' : '') + '>mediumblob</option>' +
1211	' <option value="longblob" ' + (data_type === 'longblob' ? 'selected' : '') + '>longblob</option>' +
1212	' </optgroup>' +
1213	' <optgroup label="Boolean">' +
1214	' <option value="boolean" ' + (data_type === 'boolean' ? 'selected' : '') + '>boolean</option>' +
1215	' </optgroup>' +
1216	'</select>' +
1217	'<input type="hidden" name="basic_data_type[]" id="basic_data_type_' + row_num + '" value="' + basic_data_type + '">' +
1218	'</td>' +
1219	'<td>' +
1220	' <select name="type_attribute[]" id="type_attribute_' + row_num + '" class="wpda_view_table">' +
1221	' <option value=""></option>' +
1222	' <option value="unsigned" ' + (type_attribute === 'unsigned' ? 'selected' : '') + '>unsigned</option>' +
1223	' <option value="unsigned zerofill" ' + (type_attribute === 'unsigned zerofill' ? 'selected' : '') + '>unsigned zerofill</option>' +
1224	' </select>' +
1225	'</td>';
1226	var new_row = '<tr id="row_num_' + row_num + '" class="' + row_class + '">' +
1227	'<td class="wpda-table-structure-first-column-move">' +
1228	' <span class="dashicons dashicons-move grabbable" style="float:left;"></span>' +
1229	'</td>' +
1230	'<td>' +
1231	' <input type="text" name="column_name[]" id="column_name_' + row_num + '"' +
1232	' maxlength="64" value="' + column_name + '" class="wpda_mysql_names wpda_view_table">' +
1233	'</td>' +
1234	(design_mode === 'basic' ? basic_columns : advanced_columns) +
1235	'<td>' +
1236	' <select name="key[]" id="key_' + row_num + '" class="wpda_view_table">' +
1237	' <option value="No" ' + (key === 'No' ? 'selected' : '') + '>No</option>' +
1238	' <option value="Yes" ' + (key === 'Yes' ? 'selected' : '') + '>Yes</option>' +
1239	' </select>' +
1240	'</td>' +
1241	'<td>' +
1242	' <select name="mandatory[]" id="mandatory_' + row_num + '" class="wpda_view_table">' +
1243	' <option value="No"' + (mandatory === 'No' ? 'selected' : '') + '>No</option>' +
1244	' <option value="Yes"' + (mandatory === 'Yes' ? 'selected' : '') + '>Yes</option>' +
1245	' </select>' +
1246	'</td>' +
1247	'<td>' +
1248	' <input type="text" name="max_length[]" id="max_length_' + row_num + '"' +
1249	' value="' + (max_length === '0' ? '' : max_length) + '" class="' + (basic_data_type === 'Real' ? 'wpda_float' : 'wpda_digits_only') + ' wpda_view_table">' +
1250	'</td>' +
1251	'<td>' +
1252	' <input type="text" name="extra[]" id="extra_' + row_num + '" value="' + extra + '" class="wpda_view_table">' +
1253	'</td>' +
1254	'<td>' +
1255	' <input type="text" name="default[]" id="default_' + row_num + '" value="' + default_value + '" class="wpda_view_table">' +
1256	'</td>' +
1257	'<td>' +
1258	' <input type="text" name="list[]" id="list_' + row_num + '" value="' + list + '" class="wpda_view_table">' +
1259	' <input type="hidden" name="row_action[]" value="' + row_action + '">' +
1260	'</td>' +
1261	'<td class="wpda-table-structure-last-column">' +
1262	' <a href="javascript:void(0)" onclick="rem_row(event)" class="dashicons dashicons-trash wpda_view_table wpda_tooltip" title="Remove column from table design"></a>' +
1263	'</td>' +
1264	'</tr>';
1265	if (jQuery("#wpda_table_structure tr").length === 0) {
1266	jQuery("#wpda_table_structure").append(new_row);
1267	} else {
1268	jQuery("#wpda_table_structure tr:last").after(new_row);
1269	}
1270	<?php if ( 'basic' === $this->design_mode ) { ?>
1271	check_basic_data_type(row_num);
1272	<?php } else { ?>
1273	check_data_type(row_num)
1274	<?php } ?>
1275	row_num++;
1276	jQuery('.wpda_mysql_names').off('keyup paste');
1277	jQuery('.wpda_mysql_names').on('keyup paste', function () {
1278	this.value = this.value.replace(/[^\w\_]/g, '');
1279	});
1280	check_numeric_items_off();
1281	check_numeric_items_on();
1282	jQuery(".wpda_view_table").off('change paste keyup', updated_table);
1283	jQuery(".wpda_view_table").on('change paste keyup', updated_table);
1284	jQuery("a.wpda_view_table").off();
1285	jQuery("a.wpda_view_table").on('click', updated_table);
1286	jQuery(".wpda_column_deleted").find("input,select").attr('disabled', true);
1287	if (!init) {
1288	updated_table();
1289	}
1290	}
1291
1292	function select_available(e) {
1293
1294	var option = jQuery("#columns_available option:selected");
1295	var add_to = jQuery("#columns_selected");
1296
1297	option.remove();
1298	new_option = add_to.append(option);
1299
1300	if (jQuery("#columns_selected option[value='']").length > 0) {
1301	// Remove ALL from selected list.
1302	jQuery("#columns_selected option[value='']").remove();
1303	}
1304
1305	jQuery('select#columns_selected option').prop("selected", false);
1306
1307	}
1308
1309	function select_selected(e) {
1310
1311	var option = jQuery("#columns_selected option:selected");
1312	if (option[0].value === '') {
1313	// Cannot remove ALL.
1314	return;
1315	}
1316
1317	var add_to = jQuery("#columns_available");
1318
1319	option.remove();
1320	add_to.append(option);
1321
1322	if (jQuery('select#columns_selected option').length === 0) {
1323	jQuery("#columns_selected").append(jQuery('<option></option>').attr('value', '').text(no_cols_selected));
1324	}
1325
1326	jQuery('select#columns_available option').prop("selected", false);
1327	}
1328
1329	function show_index_dialog(e) {
1330	if ('<?php echo esc_attr( $this->action ); ?>' === 'view') {
1331	return;
1332	}
1333
1334	var item_id = e.target.id;
1335	var index_row_num = item_id.substr(item_id.lastIndexOf("_") + 1);
1336
1337	if (jQuery('#add_columns_' + index_row_num).hasClass('disabled') === true) {
1338	return;
1339	}
1340
1341	var columns_available = jQuery(
1342	'<select id="columns_available" name="columns_available[]" multiple size="8" style="width:200px" onchange="select_available()">' +
1343	'</select>'
1344	);
1345	jQuery("input[name='column_name[]']").each(function () {
1346	columns_available.append(jQuery('<option></option>').attr('value', jQuery(this).val()).text(jQuery(this).val()));
1347	});
1348
1349	var columns_selected = jQuery(
1350	'<select id="columns_selected" name="columns_selected[]" multiple size="8" style="width:200px" onchange="select_selected()">' +
1351	'<option value="">' + no_cols_selected + '</option>' +
1352	'</select>'
1353	);
1354
1355	var dialog_table = jQuery('<table style="width:410px"></table>');
1356	var dialog_table_row = dialog_table.append(jQuery('<tr></tr>'));
1357	dialog_table_row.append(jQuery('<td width="50%"></td>').append(columns_available));
1358	dialog_table_row.append(jQuery('<td width="50%"></td>').append(columns_selected));
1359
1360	// var dialog_table_row_available = dialog_table.append(jQuery('<tr></tr>').append(jQuery('<td width="50%"></td>')));
1361	// dialog_table_row_available.append(columns_available);
1362	//
1363	// var dialog_table_row_selected = dialog_table.append(jQuery('<tr></tr>').append(jQuery('<td width="50%"></td>')));
1364	// dialog_table_row_selected.append(columns_selected);
1365
1366	var dialog_text = jQuery('<div style="width:410px"></div>');
1367	var dialog = jQuery('<div></div>');
1368
1369	dialog.append(dialog_text);
1370	dialog.append(dialog_table);
1371
1372	jQuery(dialog).dialog(
1373	{
1374	dialogClass: 'wp-dialog no-close',
1375	title: 'Add column(s) to index',
1376	modal: true,
1377	autoOpen: true,
1378	closeOnEscape: false,
1379	resizable: false,
1380	width: 'auto',
1381	buttons: {
1382	"Close": function () {
1383
1384	var selected_columns = '';
1385	jQuery("#columns_selected option").each(
1386	function () {
1387	selected_columns += jQuery(this).val() + ',';
1388	}
1389	);
1390	if (selected_columns !== '') {
1391	selected_columns = selected_columns.slice(0, -1);
1392	}
1393	jQuery('#column_names_' + index_row_num).val(selected_columns);
1394
1395	jQuery(this).dialog('destroy').remove();
1396
1397	updated_indexes();
1398
1399	},
1400	"Cancel": function () {
1401
1402	jQuery(this).dialog('destroy').remove();
1403
1404	}
1405	}
1406	}
1407	);
1408	jQuery(".ui-button-icon-only").hide();
1409	}
1410
1411	function add_index(init = false, index_name = '', unique = '', column_names = '', row_action = '') {
1412	var row_class = '';
1413	switch (row_action) {
1414	case 'u':
1415	row_class = 'wpda_column_updated';
1416	break;
1417	case 'i':
1418	row_class = 'wpda_column_inserted';
1419	break;
1420	case 'd':
1421	row_class = 'wpda_column_deleted';
1422	}
1423	<?php if ( 'on' === $this->fulltext_support ) { ?>
1424	var fulltext_support = '<option value="FULLTEXT"' + (unique === 'FULLTEXT' ? 'selected' : '') + '>Full Text</option>';
1425	<?php } else { ?>
1426	var fulltext_support = '';
1427	<?php } ?>
1428	var new_index = '<tr id="idx_row_num_' + index_num + '" class="' + row_class + '">' +
1429	'<td style="padding-left:10px;">' +
1430	' <input type="text" name="index_name[]" id="index_name_' + index_num + '"' +
1431	' value="' + index_name + '" class="wpda_view_index wpda_mysql_names">' +
1432	'</td>' +
1433	'<td>' +
1434	' <select name="unique[]" id="unique_' + index_num + '" class="wpda_view_index">' +
1435	' <option value="No"' + (unique === 'No' ? 'selected' : '') + '>Non unique</option>' +
1436	' <option value="Yes"' + (unique === 'Yes' ? 'selected' : '') + '>Unique</option>' +
1437	fulltext_support +
1438	' </select>' +
1439	'</td>' +
1440	'<td>' +
1441	' <input type="text" name="column_names[]" id="column_names_' + index_num + '" class="column_names"' +
1442	' value="' + column_names + '" onclick="show_index_dialog(event)" readonly>' +
1443	'</td>' +
1444	'<td>' +
1445	'</td>' +
1446	' <input type="button" name="add_columns[]" id="add_columns_' + index_num + '"' +
1447	' value="Add column(s)" onclick="show_index_dialog(event)" class="wpda_view_index">' +
1448	' <input type="hidden" name="row_action[]" value="' + row_action + '">' +
1449	'</td>' +
1450	'<td class="wpda-table-structure-last-column" style="width:20px;">' +
1451	' <a href="javascript:void(0)" onclick="rem_index(event)" class="dashicons dashicons-trash wpda_view_index wpda_tooltip" title="Remove index from table design"></a>' +
1452	' </td>' +
1453	'</tr>';
1454	if (jQuery("#wpda_index_structure tr").length === 0) {
1455	jQuery("#wpda_index_structure").append(new_index);
1456	} else {
1457	jQuery("#wpda_index_structure tr:last").after(new_index);
1458	}
1459	index_num++;
1460	jQuery('.wpda_mysql_names').off('keyup paste');
1461	jQuery('.wpda_mysql_names').on('keyup paste', function () {
1462	this.value = this.value.replace(/[^\w\_]/g, '');
1463	});
1464	jQuery('#submit_indexes').attr('disabled', false);
1465	jQuery(".wpda_view_index").off('change paste keyup', updated_indexes);
1466	jQuery(".wpda_view_index").on('change paste keyup', updated_indexes);
1467	jQuery("a.wpda_view_index").off();
1468	jQuery("a.wpda_view_index").on('click', updated_indexes);
1469	jQuery(".wpda_column_deleted").find("input,select").attr('disabled', true);
1470	if (!init) {
1471	updated_indexes();
1472	}
1473	}
1474
1475	function switch_mode(e) {
1476	if ('new' === '<?php echo esc_attr( $this->action2 ); ?>' && !table_updated && !index_updated) {
1477	if (e.target.value !== '<?php echo esc_attr( $this->design_mode ); ?>') {
1478	if (confirm('Switch to ' + e.target.value + ' mode?')) {
1479	jQuery('#switch_mode_form').submit();
1480	} else {
1481	return false;
1482	}
1483	}
1484	} else if ('edit' === '<?php echo esc_attr( $this->action2 ); ?>' \|\| table_updated \|\| index_updated) {
1485	if (e.target.value !== '<?php echo esc_attr( $this->design_mode ); ?>') {
1486	if (confirm('Switch to ' + e.target.value + ' mode?')) {
1487	jQuery('#design_table_form').submit();
1488	} else {
1489	return false;
1490	}
1491	}
1492	}
1493	}
1494
1495	function pre_submit_re() {
1496	if (wpda_db_table_name[jQuery('select[name="wpda_table_name_re"]').val()]) {
1497	alert('<?php echo __( 'Table name already used for another table design' ); ?>');
1498	return false;
1499	}
1500	jQuery('#design_mode_re').val(jQuery('input[name="design_mode"]:checked').val());
1501	return true;
1502	}
1503
1504	function get_tables() {
1505	schema_name = jQuery('#wpda_schema_name_re_list').val();
1506	var url = location.pathname + '?action=wpda_get_tables&hideviews=TRUE';
1507	var data = {
1508	wpdaschema_name: schema_name,
1509	wpda_wpnonce: '<?php echo esc_attr( wp_create_nonce( 'wpda-getdata-access-' . WPDA::get_current_user_login() ) ); ?>'
1510	};
1511	jQuery.post(
1512	url,
1513	data,
1514	function (data) {
1515	jQuery('#wpda_table_name_re_list').find('option').remove();
1516	var jsonData = JSON.parse(data);
1517	for (i = 0; i < jsonData.length; i++) {
1518	jQuery('#wpda_table_name_re_list').append(
1519	jQuery("<option></option>")
1520	.attr("value", jsonData[i]['table_name'])
1521	.text(jsonData[i]['table_name'])
1522	);
1523	}
1524	}
1525	);
1526	}
1527	</script>
1528	<div class="wrap">
1529	<h1>
1530	<a
1531	href="?page=<?php echo '' === $this->caller ? esc_attr( $this->page ) : esc_attr( \WP_Data_Access_Admin::PAGE_MAIN ); ?>"
1532	style="display: inline-block; vertical-align: unset;"
1533	class="dashicons dashicons-arrow-left-alt wpda_tooltip"
1534	title="<?php echo __( 'List', 'wp-data-access' ); ?>"
1535	></a>
1536	<span>Data Designer</span>
1537	<?php
1538	if ( WPDA_Dashboard::add_actions_to_page_title() ) {
1539	?>
1540	<a href="https://wpdataaccess.com/docs/documentation/data-designer/getting-started/" target="_blank"
1541	title="Plugin Help - open a new tab or window" class="wpda_tooltip">
1542	<span class="material-icons" style="font-size: 26px; vertical-align: sub;">help</span>
1543	</a>
1544	<?php
1545	}
1546	?>
1547	</h1>
1548	<div style="display:none;">
1549	<form id="wpda_create_table_form"
1550	action="?page=<?php echo esc_attr( $this->page ); ?>"
1551	method="post">
1552	<input type="hidden" name="action" value="edit"/>
1553	<input type="hidden" name="action2" value="create_table"/>
1554	<input type="hidden" name="wpda_table_name"
1555	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1556	<input type="hidden" name="wpda_schema_name"
1557	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1558	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1559	</form>
1560	<form id="wpda_alter_table_form"
1561	action="?page=<?php echo esc_attr( $this->page ); ?>"
1562	method="post">
1563	<input type="hidden" name="action" value="edit"/>
1564	<input type="hidden" name="action2" value="alter_table"/>
1565	<input type="hidden" name="wpda_table_name"
1566	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1567	<input type="hidden" name="wpda_schema_name"
1568	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1569	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1570	</form>
1571	<form id="wpda_drop_table_form"
1572	action="?page=<?php echo esc_attr( $this->page ); ?>"
1573	method="post">
1574	<input type="hidden" name="action" value="edit"/>
1575	<input type="hidden" name="action2" value="drop_table"/>
1576	<input type="hidden" name="wpda_table_name"
1577	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1578	<input type="hidden" name="wpda_schema_name"
1579	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1580	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1581	</form>
1582	<form id="wpda_create_index_form"
1583	action="?page=<?php echo esc_attr( $this->page ); ?>"
1584	method="post" style="margin-left:1px;margin-right:1px;">
1585	<input type="hidden" name="action" value="edit"/>
1586	<input type="hidden" name="action2" value="create_table_index"/>
1587	<input type="hidden" name="wpda_table_name"
1588	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1589	<input type="hidden" name="wpda_schema_name"
1590	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1591	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1592	</form>
1593	<form id="wpda_drop_index_form"
1594	action="?page=<?php echo esc_attr( $this->page ); ?>"
1595	method="post" style="margin-left:1px;margin-right:1px;">
1596	<input type="hidden" name="action" value="edit"/>
1597	<input type="hidden" name="action2" value="drop_table_index"/>
1598	<input type="hidden" name="wpda_table_name"
1599	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1600	<input type="hidden" name="wpda_schema_name"
1601	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1602	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1603	</form>
1604	</div>
1605	<style>
1606	#overlay_show_create_table {
1607	height: 400px;
1608	width: 600px;
1609	position: fixed;
1610	display: none;
1611	top: 50%;
1612	left: 50%;
1613	transform: translate(-50%, -50%);
1614	-ms-transform: translate(-50%, -50%);
1615	right: 0;
1616	bottom: 0;
1617	background-color: #f9f9f9;
1618	opacity: .95;
1619	border: 1px solid #ccc;
1620	cursor: pointer;
1621	z-index: 1000;
1622	}
1623
1624	#overlay_show_create_table_text {
1625	height: 360px;
1626	width: 400px;
1627	padding: 10px;
1628	position: relative;
1629	top: 50%;
1630	left: 220px;
1631	transform: translate(-50%, -50%);
1632	-ms-transform: translate(-50%, -50%);
1633	color: black;
1634	overflow-y: auto;
1635	background-color: white;
1636	border: 1px solid #ccc;
1637	}
1638	</style>
1639	<div id="overlay_show_create_table">
1640	<div id="overlay_show_create_table_text">
1641	<?php echo $this->create_table_statement . $this->create_index_statement; // phpcs:ignore WordPress.Security.EscapeOutput ?>
1642	</div>
1643	<div style="position: absolute; bottom: 0; right: 0; padding-right: 10px; padding-bottom: 10px;">
1644	<a id="button-copy-clipboard" href="javascript:void(0)" class="button button-secondary"
1645	style="text-align:center;width:150px;"
1646	data-clipboard-text="<?php echo str_replace( self::NEW_LINE, "\n", $this->create_table_statement ) . str_replace( self::NEW_LINE, "\n", $this->create_index_statement ); // phpcs:ignore WordPress.Security.EscapeOutput ?>">
1647	<span class="material-icons wpda_icon_on_button">content_copy</span>
1648	<?php echo __( 'Copy to clipboard' ); ?>
1649	</a>
1650	<br/>
1651	<div style="height: 5px;"></div>
1652	<a href="javascript:void(0)" class="button button-primary"
1653	style="text-align:center;width:150px;"
1654	onclick="jQuery('#overlay_show_create_table').hide()">
1655	<span class="material-icons wpda_icon_on_button">cancel</span>
1656	<?php echo __( 'Close' ); ?>
1657	</a>
1658	</div>
1659	</div>
1660	<script type='text/javascript'>
1661	jQuery(function () {
1662	<?php if ( 'show_create_table_script' === $this->action2_posted \|\| 'show_alter_table_script' === $this->action2_posted ) { ?>
1663	jQuery('#overlay_show_create_table').show();
1664	<?php } ?>
1665	var sql_to_clipboard = new ClipboardJS('#button-copy-clipboard');
1666	sql_to_clipboard.on('success', function (e) {
1667	jQuery.notify('<?php echo __( 'SQL successfully copied to clipboard!' ); ?>','info');
1668	});
1669	sql_to_clipboard.on('error', function (e) {
1670	jQuery.notify('<?php echo __( 'Could not copy SQL to clipboard!' ); ?>','error');
1671	});
1672	jQuery('#wpda_table_structure').sortable();
1673	jQuery( '.wpda_tooltip' ).tooltip();
1674	});
1675	</script>
1676	<?php
1677	if ( ! $this->wpda_table_design ) {
1678	// Allow loading table from database into designer (reverse engineering).
1679	$table_list = WPDA_Dictionary_Lists::get_tables( false );
1680	?>
1681	<div id="wpda_reverse_engineering" style="display: none">
1682	<br/>
1683	<div class="wpda_reverse_engineering">
1684	<form id="wpda_reverse_engineering_form"
1685	action="?page=<?php echo esc_attr( $this->page ); ?>" method="post">
1686	<label><?php echo __( 'Load table from database' ); ?> </label>
1687	<select name="wpda_schema_name_re" id="wpda_schema_name_re_list" onchange="get_tables()">
1688	<?php
1689	global $wpdb;
1690	foreach ( $this->databases as $database ) {
1691	if ( null === $this->wpda_schema_name \|\| '' === $this->wpda_schema_name ) {
1692	$dbs = WPDA::get_user_default_scheme();
1693	} else {
1694	$dbs = $this->wpda_schema_name;
1695	}
1696	$selected = $dbs === $database['schema_name'] ? 'selected' : '';
1697	if ( $wpdb->dbname === $database['schema_name'] ) {
1698	$database_printed = "WordPress database ({$database['schema_name']})";
1699	} else {
1700	$database_printed = $database['schema_name'];
1701	}
1702	echo "<option value='{$database['schema_name']}' $selected>{$database_printed}</option>"; // phpcs:ignore WordPress.Security.EscapeOutput
1703	}
1704	?>
1705	</select>
1706	<select name="wpda_table_name_re" id="wpda_table_name_re_list">
1707	<?php
1708	foreach ( $table_list as $key => $value ) {
1709	echo '<option value="' . esc_attr( $value['table_name'] ) . '">' . esc_attr( $value['table_name'] ) . '</option>';
1710	}
1711	?>
1712	</select>
1713	<input type="hidden" name="action" value="edit"/>
1714	<input type="hidden" name="action2" value="wpda_reverse_engineering"/>
1715	<input type="hidden" name="wpda_table_name" id="wpda_table_name_re"
1716	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1717	<input type="hidden" name="wpda_schema_name" id="wpda_schema_name_re"
1718	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1719	<input type="hidden" id="design_mode_re" name="design_mode_re"/>
1720	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1721	<input type="submit"
1722	class="button button-primary"
1723	value="Start Reverse Engineering"
1724	onclick="return pre_submit_re()"
1725	>
1726	<a href="javascript:void(0)" onclick="jQuery('#wpda_reverse_engineering').hide()"
1727	class="button"><?php echo __( 'Dismiss' ); ?></a>
1728	</form>
1729	</div>
1730	</div>
1731	<?php
1732
1733	}
1734	if ( $this->table_exists ) {
1735	// Add reconcile form.
1736	?>
1737	<div style="display:none;">
1738	<form id="wpda_reconcile_form"
1739	action="?page=<?php echo esc_attr( $this->page ); ?>" method="post">
1740	<input type="hidden" name="action" value="edit"/>
1741	<input type="hidden" name="action2" value="wpda_reconcile"/>
1742	<input type="hidden" name="wpda_table_name_re"
1743	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1744	<input type="hidden" name="wpda_schema_name_re"
1745	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1746	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1747	</form>
1748	</div>
1749	<?php
1750	}
1751	?>
1752	<div style="text-align:right">
1753	<a href="https://wpdataaccess.com/docs/documentation/data-designer/getting-started/"
1754	target="_blank" style="text-decoration:none">
1755	<span class="material-icons wpda_icon_on_button">help_outline</span>
1756	What is the difference between a table design and a database table?
1757	</a>
1758	</div>
1759	<div>
1760	<div style="display:none;">
1761	<form id="show_create_table_form" action="?page=<?php echo esc_attr( $this->page ); ?>"
1762	method="post">
1763	<input type="hidden" name="action" value="edit"/>
1764	<input type="hidden" name="action2" value="show_create_table_script"/>
1765	<input type="hidden" name="wpda_table_name"
1766	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1767	<input type="hidden" name="wpda_schema_name"
1768	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1769	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1770	</form>
1771	<form id="show_alter_table_form" action="?page=<?php echo esc_attr( $this->page ); ?>"
1772	method="post">
1773	<input type="hidden" name="action" value="edit"/>
1774	<input type="hidden" name="action2" value="show_alter_table_script"/>
1775	<input type="hidden" name="wpda_table_name"
1776	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1777	<input type="hidden" name="wpda_schema_name"
1778	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1779	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
1780	</form>
1781	</div>
1782	<form id="design_table_form"
1783	action="?page=<?php echo esc_attr( $this->page ); ?>"
1784	autocomplete="off"
1785	method="post" onsubmit="return pre_submit()">
1786	<fieldset class="wpda_fieldset">
1787	<legend>
1788	<span>
1789	<?php echo __( 'Table definition', 'wp-data-access' ); ?>
1790	<?php
1791	if (
1792	'' !== $this->wpda_schema_name &&
1793	'' !== $this->wpda_table_name &&
1794	null !== $this->wpda_table_design
1795	) {
1796	$table_structure = json_decode( json_encode( $this->wpda_table_design ), true );
1797	if ( isset( $table_structure['table'] ) ) {
1798	$column_names = array_column( $table_structure['table'], 'column_name' );
1799	} else {
1800	$column_names = array();
1801	}
1802	// Validate schema, table and column names
1803	$warning = WPDA::validate_names( $this->wpda_schema_name, $this->wpda_table_name, $column_names );
1804	if ( '' !== $warning ) {
1805	echo $warning; // phpcs:ignore WordPress.Security.EscapeOutput
1806	}
1807	}
1808	?>
1809	</span>
1810	</legend>
1811	<table class="wpda-table-structure">
1812	<thead>
1813	<tr>
1814	<td class="wpda-table-structure-first-column">
1815	<label for "wpda_schema_name"><?php echo __( 'Database' ); ?> </label>
1816	</td>
1817	<td>
1818	<select name="wpda_schema_name" id="wpda_schema_name" style="width:100%;max-width:100%;">
1819	<?php
1820	global $wpdb;
1821	foreach ( $this->databases as $database ) {
1822	if ( null === $this->wpda_schema_name \|\| '' === $this->wpda_schema_name ) {
1823	$dbs = WPDA::get_user_default_scheme();
1824	} else {
1825	$dbs = $this->wpda_schema_name;
1826	}
1827	$selected = $dbs === $database['schema_name'] ? 'selected' : '';
1828	if ( $wpdb->dbname === $database['schema_name'] ) {
1829	$database_printed = "WordPress database ({$database['schema_name']})";
1830	} else {
1831	$database_printed = $database['schema_name'];
1832	}
1833	echo "<option value='{$database['schema_name']}' $selected>{$database_printed}</option>"; // phpcs:ignore WordPress.Security.EscapeOutput
1834	}
1835	?>
1836	</select>
1837	</td>
1838	<td colspan="4">
1839	</td>
1840	<td colspan="4" class="wpda-table-structure-last-column">
1841	<span style="float:right;">
1842	<label>
1843	<input type="radio"
1844	name="design_mode"
1845	value="basic"
1846	class="design_mode"
1847	onclick="return switch_mode(event)"
1848	<?php echo 'basic' === $this->design_mode ? 'checked' : ''; ?>
1849	>
1850	<?php echo __( 'Basic Design Mode' ); ?>
1851	</label>
1852	<label>
1853	<input type="radio"
1854	name="design_mode"
1855	value="advanced"
1856	class="design_mode"
1857	onclick="return switch_mode(event)"
1858	<?php echo 'advanced' === $this->design_mode ? 'checked' : ''; ?>
1859	>
1860	<?php echo __( 'Advanced Design Mode' ); ?>
1861	</label>
1862	</span>
1863	</td> </tr>
1864	<tr>
1865	<td class="wpda-table-structure-first-column">
1866	<label for "wpda_table_name"><?php echo __( 'Table name' ); ?> </label>
1867	</td>
1868	<td>
1869	<input type="text" name="wpda_table_name" id="wpda_table_name" maxlength="64"
1870	style="width:100%;max-width:100%;"
1871	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"
1872	onchange="jQuery('#wpda_table_name_re').val(jQuery(this).val())"
1873	class="wpda_mysql_names wpda_view_table"
1874	/>
1875	</td>
1876	<td colspan="4">
1877	<?php
1878	if ( $this->table_exists ) {
1879	if ( $this->is_wp_table ) {
1880	?>
1881	<span style="vertical-align:-webkit-baseline-middle; cursor:pointer;"
1882	title="<?php echo __( 'You cannot use a WordPress table name', 'wp-data-access' ); ?>"
1883	class="dashicons dashicons-flag wpda_tooltip">
1884	</span>
1885	<?php
1886	} else {
1887	?>
1888	<span title="<?php echo __( 'A table with this name already exists in the database', 'wp-data-access' ); ?>"
1889	class="material-icons pointer wpda_tooltip" style="padding-top:3px">
1890	info
1891	</span>
1892	<?php
1893	}
1894	?>
1895	<a href="javascript:void(0)"
1896	id="reconcile_button"
1897	onclick="if (confirm('<?php echo __( 'Reconcile table? Your current modifications will be lost!' ); ?>')) { jQuery('#wpda_reconcile_form').submit(); }"
1898	class="button wpda_tooltip"
1899	title="Update table design from database table (overwrites current design)">
1900	<span class="material-icons wpda_icon_on_button">history</span>
1901	<?php echo __( 'Reconcile' ); ?>
1902	</a>
1903	<?php
1904	} else {
1905	$title = __( 'New table', 'wp-data-access' );
1906	?>
1907	<span style="vertical-align:-webkit-baseline-middle; cursor:pointer;"
1908	title="<?php echo $title; // phpcs:ignore WordPress.Security.EscapeOutput ?>"
1909	class="dashicons dashicons-warning wpda_tooltip">
1910	</span>
1911	<?php
1912	}
1913	?>
1914	<input type="hidden" name="wpda_table_name_original"
1915	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
1916	<input type="hidden" name="wpda_schema_name_original"
1917	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
1918	<?php
1919	if ( ! $this->wpda_table_design ) {
1920	?>
1921	<a href="javascript:void(0)"
1922	onclick="jQuery('#wpda_reverse_engineering').show()"
1923	class="button wpda_view_table wpda_tooltip"
1924	title="<?php echo __( 'Load table from database' ); ?>"
1925	>
1926	<?php echo __( 'Reverse engineering' ); ?>
1927	</a>
1928	<?php
1929	}
1930	?>
1931	</td>
1932	<td colspan="4" class="wpda-table-structure-last-column">
1933	</td>
1934	</tr>
1935	<?php if ( 'advanced' === $this->design_mode ) { ?>
1936	<tr>
1937	<td class="wpda-table-structure-first-column">
1938	<label for "engine"><?php echo __( 'Engine' ); ?> </label>
1939	</td>
1940	<td>
1941	<select name="engine" id="engine" style="width:100%;max-width:100%;" class="wpda_view_table">
1942	<?php
1943	$engines = WPDA_Dictionary_Lists::get_engines();
1944	$engine_saved = isset( $this->wpda_table_design->engine ) ? $this->wpda_table_design->engine : '';
1945	foreach ( $engines as $engine ) {
1946	$selected_tag = '';
1947	if ( '' === $engine_saved ) {
1948	if ( 'DEFAULT' === $engine['support'] ) {
1949	$selected_tag = 'selected';
1950	}
1951	} else {
1952	if ( $engine_saved === $engine['engine'] ) {
1953	$selected_tag = 'selected';
1954	}
1955	}
1956	?>
1957	<option value="<?php echo esc_attr( $engine['engine'] ); ?>" <?php echo esc_attr( $selected_tag ); ?>>
1958	<?php echo esc_attr( $engine['engine'] ); ?>
1959	</option>
1960	<?php
1961	}
1962	?>
1963	</select>
1964	</td>
1965	<td colspan="8" class="wpda-table-structure-last-column">
1966	<span style="float:right;">
1967	<a id="button_show_create_table" href="javascript:void(0)"
1968	class="button button-secondary wpda_tooltip"
1969	title="Generates a create table script from table and index design."
1970	onclick="jQuery('#show_create_table_form').submit();">
1971	<span class="material-icons wpda_icon_on_button">code</span>
1972	<?php echo __( 'Show CREATE TABLE script' ); ?>
1973	</a>
1974	<a id="button_show_alter_table" href="javascript:void(0)"
1975	class="button button-secondary wpda_tooltip"
1976	title="Generates a alter table script from table and index design."
1977	onclick="jQuery('#show_alter_table_form').submit();">
1978	<span class="material-icons wpda_icon_on_button">code</span>
1979	<?php echo __( 'Show ALTER TABLE script' ); ?>
1980	</a>
1981	</span>
1982	</td>
1983	</tr>
1984	<tr>
1985	<td class="wpda-table-structure-first-column">
1986	<label for "collation"><?php echo __( 'Collation' ); ?> </label>
1987	</td>
1988	<td>
1989	<select name="collation" id="collation" style="width:100%;max-width:100%;"
1990	class="wpda_view_table">
1991	<?php
1992	$character_set_name = '';
1993	$default_collation = WPDA_Dictionary_Lists::get_default_collation();
1994	$collations = WPDA_Dictionary_Lists::get_collations();
1995	$collation_saved = isset( $this->wpda_table_design->collation ) ? $this->wpda_table_design->collation : '';
1996	foreach ( $collations as $collation ) {
1997	if ( $character_set_name !== $collation['character_set_name'] ) {
1998	if ( '' !== $character_set_name ) {
1999	echo '</optgroup>';
2000	}
2001	$character_set_name = $collation['character_set_name'];
2002	echo '<optgroup label="' . esc_attr( $collation['character_set_name'] ) . '">';
2003	}
2004	$selected_tag = '';
2005	if ( '' === $collation_saved ) {
2006	if ( $collation['collation_name'] === $default_collation[0]['default_collation_name'] ) {
2007	$selected_tag = 'selected';
2008	}
2009	} else {
2010	if ( $collation_saved === $collation['collation_name'] ) {
2011	$selected_tag = 'selected';
2012	}
2013	}
2014	?>
2015	<option value="<?php echo esc_attr( $collation['collation_name'] ); ?>" <?php echo esc_attr( $selected_tag ); ?>>
2016	<?php echo esc_attr( $collation['collation_name'] ); ?>
2017	</option>
2018	<?php
2019	}
2020	?>
2021	</select>
2022	<?php echo '</optgroup>'; ?>
2023	</td>
2024	<td colspan="8" class="wpda-table-structure-last-column">
2025	<span style="float:right;">
2026	<label id="checkbox_show_deleted_label">
2027	<input id="checkbox_show_deleted" type="checkbox"
2028	onclick="if (jQuery(this).is(':checked')) { jQuery('.wpda_column_deleted').show(); } else { jQuery('.wpda_column_deleted').hide(); }">
2029	<?php echo __( 'Show deleted columns and indexes' ); ?>
2030	</label>
2031	</span>
2032	</td>
2033	</tr>
2034	<?php } ?>
2035	</thead>
2036	</table>
2037	</fieldset>
2038	<br/>
2039	<fieldset class="wpda_fieldset">
2040	<legend>
2041	<span>
2042	<?php echo __( 'Add columns', 'wp-data-access' ); ?>
2043	</span>
2044	</legend>
2045	<table class="wpda-table-structure" style="border-collapse: collapse;">
2046	<thead>
2047	<tr>
2048	<th class="wpda-table-structure-first-column-move"></th>
2049	<th>
2050	<?php echo __( 'Column name' ); ?>
2051	</th>
2052	<th>
2053	<?php echo __( 'Column type' ); ?>
2054	</th>
2055	<?php if ( 'advanced' === $this->design_mode ) { ?>
2056	<th>
2057	<?php echo __( 'Type attribute' ); ?>
2058	</th>
2059	<?php } ?>
2060	<th style="min-width:60px;">
2061	<?php echo __( 'Key?' ); ?>
2062	</th>
2063	<th style="min-width:90px;">
2064	<?php echo __( 'Mandatory?' ); ?>
2065	</th>
2066	<th>
2067	<?php echo __( 'Max length' ); ?>
2068	</th>
2069	<th>
2070	<?php echo __( 'Extra' ); ?>
2071	<span title="Possible values:
2072
2073	auto_increment
2074	on update current_timestamp (for column types: timestamp and datetime)
2075	virtual generated
2076	virtual stored
2077	default_generated
2078	stored generated
2079
2080	Or combined:
2081	default_generated on update current_timestamp" class="material-icons pointer wpda_tooltip" style="font-size: 140%;vertical-align: middle;">
2082
2083	info
2084	</span>
2085	</th>
2086	<th>
2087	<?php echo __( 'Default value' ); ?>
2088	</th>
2089	<th>
2090	<?php echo __( 'List values' ); ?>
2091	</th>
2092	<th class="wpda-table-structure-last-column">
2093	<a href="javascript:void(0)"
2094	onclick="add_row('<?php echo esc_attr( $this->design_mode ); ?>')"
2095	style="vertical-align:-webkit-baseline-middle;"
2096	class="dashicons dashicons-plus wpda_view_table wpda_tooltip"
2097	title="Add new column to table design"
2098	></a>
2099	</th>
2100	</tr>
2101	</thead>
2102	<tbody id="wpda_table_structure"></tbody>
2103	<tfoot>
2104	<tr>
2105	<td colspan="<?php echo 'basic' === $this->design_mode ? '9' : '10'; ?>">
2106	<input type="hidden" name="submitted_changes" value="table"/>
2107	<input type="hidden" name="action" value="edit"/>
2108	<input type="hidden" name="action2"
2109	value="<?php echo esc_attr( $this->action2 ); ?>"
2110	/>
2111	<?php if ( 'basic' === $this->design_mode ) { ?>
2112	<input type="hidden" name="engine" id="engine"
2113	value="<?php echo isset( $this->wpda_table_design->engine ) ? esc_attr( $this->wpda_table_design->engine ) : ''; ?>"
2114	/>
2115	<input type="hidden" name="collation" id="collation"
2116	value="<?php echo isset( $this->wpda_table_design->collation ) ? esc_attr( $this->wpda_table_design->collation ) : ''; ?>"
2117	/>
2118	<?php } ?>
2119	<a href="javascript:void(0)"
2120	title="Creates database table from design. Does not create indexes."
2121	class="button wpda_tooltip wpda_view
2122	<?php
2123	if ( $this->table_exists ) {
2124	echo ' disabled';
2125	}
2126	?>
2127	"
2128	onclick="if ( confirm('Create database table `<?php echo WPDA::remove_backticks( $this->wpda_schema_name ); // phpcs:ignore WordPress.Security.EscapeOutput ?>`.`<?php echo WPDA::remove_backticks( $this->wpda_table_name ); // phpcs:ignore WordPress.Security.EscapeOutput ?>`?\nDoes not create indexes!') ) { jQuery('#wpda_create_table_form').submit(); }"
2129	<?php
2130	if ( $this->table_exists \|\| 'new' === strtolower( $this->action2 ) ) {
2131	echo ' readonly disabled';
2132	}
2133	?>
2134	>
2135	<span class="material-icons wpda_icon_on_button">check_circle</span>
2136	<?php echo __( 'CREATE TABLE', 'wp-data-access' ); ?>
2137	</a>
2138	<a id="button_alter_table" href="javascript:void(0)" class="button wpda_view wpda_tooltip"
2139	title="Writes design changes to database table and indexes."
2140	onclick="if ( confirm('Alter database table `<?php echo WPDA::remove_backticks( $this->wpda_schema_name ); // phpcs:ignore WordPress.Security.EscapeOutput ?>`.`<?php echo WPDA::remove_backticks( $this->wpda_table_name ); // phpcs:ignore WordPress.Security.EscapeOutput ?>`?\nAlters modified indexes as well!') ) { jQuery('#wpda_alter_table_form').submit(); }"
2141	>
2142	<span class="material-icons wpda_icon_on_button">update</span>
2143	<?php echo __( 'ALTER TABLE', 'wp-data-access' ); ?>
2144	</a>
2145	<a href="javascript:void(0)"
2146	title="This action drops your database table! Not your table design... This cannot be undone."
2147	class="button wpda_tooltip wpda_view
2148	<?php
2149	if ( ! $this->table_exists ) {
2150	echo ' disabled';
2151	}
2152	?>
2153	"
2154	onclick="if ( confirm('Drop database table `<?php echo WPDA::remove_backticks( $this->wpda_schema_name ); // phpcs:ignore WordPress.Security.EscapeOutput ?>`.`<?php echo WPDA::remove_backticks( $this->wpda_table_name ); // phpcs:ignore WordPress.Security.EscapeOutput ?>`?\nTable design will not be deleted!') ) { jQuery('#wpda_drop_table_form').submit(); }"
2155	<?php
2156	if ( ! $this->table_exists ) {
2157	echo ' readonly disabled';
2158	}
2159	?>
2160	>
2161	<span class="material-icons wpda_icon_on_button">delete</span>
2162	<?php echo __( 'DROP TABLE', 'wp-data-access' ); ?>
2163	</a>
2164	<input type='hidden' name='caller'
2165	value='<?php echo esc_attr( $this->caller ); ?>'/>
2166	<button type="submit" class="button button-primary wpda_view_table wpda_tooltip"
2167	title="This does NOT create the table! Is just saves your table design..."
2168	>
2169	<span class="material-icons wpda_icon_on_button">check</span>
2170	Save Table Design
2171	</button>
2172	</td>
2173	</tr>
2174	</tfoot>
2175	</table>
2176	</fieldset>
2177	</form>
2178	<form id="wpda_reset_form" action="?page=<?php echo esc_attr( $this->page ); ?>" method="post">
2179	<input type="hidden" name="action" value="edit"/>
2180	<?php
2181	if ( 'new' !== $this->action2 ) {
2182	?>
2183	<input type="hidden" name="wpda_table_name"
2184	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
2185	<input type="hidden" name="wpda_schema_name"
2186	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
2187	<?php
2188	}
2189	?>
2190	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
2191	</form>
2192	<form id="switch_mode_form" method="post"
2193	action="?page=<?php echo esc_attr( $this->page ); ?>&table_name=<?php
2194	echo WPDA_Design_Table_Model::get_base_table_name(); // phpcs:ignore WordPress.Security.EscapeOutput
2195	?>">
2196	<input type="hidden" name="design_mode"
2197	value="<?php echo 'basic' === $this->design_mode ? 'advanced' : 'basic'; // phpcs:ignore WordPress.Security.EscapeOutput ?>">
2198	<input type="hidden" name="action" value="edit">
2199	<input type='hidden' name='caller' value='<?php echo esc_attr( $this->caller ); ?>'/>
2200	</form>
2201	</div>
2202	<br/>
2203	<div>
2204	<form id="design_table_form_indexes" action="?page=<?php echo esc_attr( $this->page ); ?>"
2205	method="post">
2206	<fieldset class="wpda_fieldset">
2207	<legend>
2208	<span>
2209	<?php echo __( 'Add indexes' ); ?>
2210	</span>
2211	</legend>
2212	<table class="wpda-table-structure" style="border-collapse: collapse;">
2213	<thead>
2214	<tr>
2215	<th style="padding-left:10px;">
2216	<?php echo __( 'Index name' ); ?>
2217	</th>
2218	<th>
2219	<?php echo __( 'Type?' ); ?>
2220	</th>
2221	<th>
2222	<?php echo __( 'Column name(s)' ); ?>
2223	</th>
2224	<th></th>
2225	<th class="wpda-table-structure-last-column" style="width:20px;">
2226	<a href="javascript:void(0)" onclick="add_index()"
2227	style="vertical-align: -webkit-baseline-middle;"
2228	class="dashicons dashicons-plus wpda_view_index wpda_tooltip"
2229	title="Add new index to table design"
2230	></a>
2231	</th>
2232	</tr>
2233	</thead>
2234	<tbody id="wpda_index_structure">
2235	</tbody>
2236	<tfoot>
2237	<tr>
2238	<td colspan="5">
2239	<input type="hidden" name="submitted_changes" value="indexes"/>
2240	<input type="hidden" name="action" value="edit"/>
2241	<input type="hidden" name="action2"
2242	value="<?php echo esc_attr( $this->action2 ); ?>"/>
2243	<input type="hidden" name="wpda_table_name"
2244	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
2245	<input type="hidden" name="wpda_table_name_original"
2246	value="<?php echo esc_attr( $this->wpda_table_name ); ?>"/>
2247	<input type="hidden" name="wpda_schema_name"
2248	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
2249	<input type="hidden" name="wpda_schema_name_original"
2250	value="<?php echo esc_attr( $this->wpda_schema_name ); ?>"/>
2251	<a id="wpda_create_index" href="javascript:void(0)"
2252	title="Drops all indexes and recreates them."
2253	class="button wpda_view wpda_tooltip"
2254	onclick="if ( confirm('<?php echo __( 'Drop all deleted indexes and recreate all changed indexes for table' . ' `' . $this->wpda_table_name . '`?' ); ?>') ) { jQuery('#wpda_create_index_form').submit(); }"
2255	>
2256	<span class="material-icons wpda_icon_on_button">check_circle</span>
2257	<?php echo __( '(RE)CREATE INDEXES', 'wp-data-access' ); ?>
2258	</a>
2259	<a id="wpda_drop_index" href="javascript:void(0)"
2260	title="This action drops your indexes from the database! This cannot be undone."
2261	class="button wpda_view wpda_tooltip"
2262	onclick="if ( confirm('<?php echo __( 'Drop all indexes for table' . ' `' . $this->wpda_table_name . '`?\n' . __( 'Does not drop primary key indexes and index designs!' ) ); ?>')) { jQuery('#wpda_drop_index_form').submit(); }"
2263	>
2264	<span class="material-icons wpda_icon_on_button">delete</span>
2265	<?php echo __( 'DROP INDEXES', 'wp-data-access' ); ?>
2266	</a>
2267	<input type='hidden' name='caller'
2268	value='<?php echo esc_attr( $this->caller ); ?>'/>
2269	<a id="submit_indexes" href="javascript:void(0)"
2270	title="Does NOT create indexes! It just saves your index design..."
2271	onclick="if (!jQuery(this).attr('disabled')) { jQuery('#design_table_form_indexes').submit(); } else { alert('<?php echo __( 'Save table design changes first!' ); ?>'); }"
2272	class="button button-primary wpda_view_index wpda_tooltip">
2273	<span class="material-icons wpda_icon_on_button">check</span>
2274	<?php echo __( 'Save Indexes' ); ?>
2275	</a>
2276	</td>
2277	</tr>
2278	</tfoot>
2279	</table>
2280	</fieldset>
2281	</form>
2282	</div>
2283	<?php
2284	if ( null !== $this->create_table_statement && false === $this->create_table_succeeded ) {
2285	?>
2286	<br/>
2287	<div class="wpda_design_table">
2288	<table class="wpda-table-structure">
2289	<tfoot>
2290	<tr>
2291	<td>
2292	<h3><?php echo __( 'The following CREATE TABLE statement failed' ); ?></h3>
2293	<div>
2294	<div style="padding:10px; text-align: left; width: fit-content; margin: 0 auto;">
2295	<?php echo $this->create_table_statement; // phpcs:ignore WordPress.Security.EscapeOutput ?>
2296	</div>
2297	</div>
2298	<div>
2299	<strong><?php echo $this->wpdb_error; // phpcs:ignore WordPress.Security.EscapeOutput ?></strong>
2300	</div>
2301	</td>
2302	</tr>
2303	</tfoot>
2304	</table>
2305	</div>
2306	<?php
2307	}
2308	if ( null !== $this->create_index_failed && 0 < count( $this->create_index_failed ) ) {
2309	?>
2310	<br/>
2311	<div class="wpda_design_table">
2312	<table class="wpda-table-structure">
2313	<tfoot>
2314	<tr>
2315	<td>
2316	<h3><?php echo __( 'The following CREATE INDEX statement(s) failed' ); ?></h3>
2317	<div>
2318	<div style="padding:10px; text-align: left; width: fit-content; margin: 0 auto;">
2319	<?php
2320	foreach ( $this->create_index_failed as $index_failed ) {
2321	echo "$index_failed<br/>"; // phpcs:ignore WordPress.Security.EscapeOutput
2322	}
2323	?>
2324	</div>
2325	</div>
2326	</td>
2327	</tr>
2328	</tfoot>
2329	</table>
2330	</div>
2331	<?php
2332	}
2333	if ( null !== $this->create_table_statement && false === $this->alter_table_succeeded ) {
2334	?>
2335	<br/>
2336	<div class="wpda_design_table">
2337	<table class="wpda-table-structure">
2338	<tfoot>
2339	<tr>
2340	<td>
2341	<h3><?php echo __( 'The following ALTER TABLE statement failed' ); ?></h3>
2342	<div>
2343	<div style="padding:10px; text-align: left; width: fit-content; margin: 0 auto;">
2344	<?php echo $this->create_table_statement; // phpcs:ignore WordPress.Security.EscapeOutput ?>
2345	</div>
2346	</div>
2347	<div>
2348	<strong><?php echo $this->wpdb_error; // phpcs:ignore WordPress.Security.EscapeOutput ?></strong>
2349	</div>
2350	</td>
2351	</tr>
2352	</tfoot>
2353	</table>
2354	</div>
2355	<?php
2356	}
2357	?>
2358
2359	</div>
2360	<?php
2361
2362	if ( $this->wpda_table_design ) {
2363	// Display table design.
2364	foreach ( $this->wpda_table_design->table as $design_column ) {
2365	if ( ! $this->table_exists ) {
2366	// New table cannot be compared with real table.
2367	$column_changed = '';
2368	} else {
2369	// Check for table structure changes.
2370	if ( isset( $this->table_columns[ $design_column->column_name ] ) ) {
2371	// Check column arguments.
2372	$column_changed = '';
2373	foreach ( $this->real_table as $real_column ) {
2374	if ( $real_column->column_name === $design_column->column_name ) {
2375	if ( $real_column != $design_column ) {
2376	if ( strtolower( $real_column->extra ) === 'auto_increment' && strtolower( $design_column->extra ) === 'auto_increment' ) {
2377	$tmp_real_column = clone $real_column;
2378	$tmp_design_column = clone $design_column;
2379	unset( $tmp_real_column->extra );
2380	unset( $tmp_design_column->extra );
2381	if ( $tmp_real_column != $tmp_design_column ) {
2382	$column_changed = 'u';
2383	$this->table_altered = true;
2384	}
2385	} else {
2386	$column_changed = 'u';
2387	$this->table_altered = true;
2388	}
2389	}
2390	}
2391	}
2392	} else {
2393	// New column.
2394	$column_changed = 'i';
2395	$this->table_altered = true;
2396	}
2397	}
2398	?>
2399	<script type='text/javascript'>
2400	add_row(
2401	'<?php echo esc_attr( $this->design_mode ); ?>',
2402	true,
2403	'<?php echo esc_attr( $design_column->column_name ); ?>',
2404	'<?php echo esc_attr( WPDA_Design_Table_Model::datatype2basic( esc_attr( $design_column->data_type ) ) ); ?>',
2405	'<?php echo esc_attr( $design_column->data_type ); ?>',
2406	'<?php echo esc_attr( $design_column->type_attribute ); ?>',
2407	'<?php echo esc_attr( $design_column->key ); ?>',
2408	'<?php echo esc_attr( $design_column->mandatory ); ?>',
2409	'<?php echo esc_attr( $design_column->max_length ); ?>',
2410	'<?php echo esc_attr( $design_column->extra ); ?>',
2411	'<?php echo esc_attr( $design_column->default ); ?>',
2412	'<?php echo esc_attr( $design_column->list ); ?>',
2413	'<?php echo esc_attr( $column_changed ); ?>'
2414	);
2415	</script>
2416	<?php
2417	}
2418	if ( $this->table_exists ) {
2419	// Check for deleted columns.
2420	foreach ( $this->table_columns as $table_column ) {
2421	$column_found = false;
2422	foreach ( $this->wpda_table_design->table as $design_column ) {
2423	if ( $design_column->column_name === $table_column['column_name'] ) {
2424	$column_found = true;
2425	break;
2426	}
2427	}
2428	if ( ! $column_found ) {
2429	break;
2430	}
2431	}
2432
2433	if ( ! $column_found ) {
2434	// Add deleted column(s) to form and mark as readonly.
2435	foreach ( $this->table_columns as $table_column ) {
2436	$column_found = false;
2437	foreach ( $this->wpda_table_design->table as $design_column ) {
2438	if ( $design_column->column_name === $table_column['column_name'] ) {
2439	$column_found = true;
2440	break;
2441	}
2442	}
2443	if ( ! $column_found ) {
2444	foreach ( $this->real_table as $real_column ) {
2445	if ( $real_column->column_name === $table_column['column_name'] ) {
2446	?>
2447	<script type='text/javascript'>
2448	add_row(
2449	'<?php echo esc_attr( $this->design_mode ); ?>',
2450	true,
2451	'<?php echo esc_attr( $real_column->column_name ); ?>',
2452	'<?php echo esc_attr( WPDA_Design_Table_Model::datatype2basic( esc_attr( $real_column->data_type ) ) ); ?>',
2453	'<?php echo esc_attr( $real_column->data_type ); ?>',
2454	'<?php echo esc_attr( $real_column->type_attribute ); ?>',
2455	'<?php echo esc_attr( $real_column->key ); ?>',
2456	'<?php echo esc_attr( $real_column->mandatory ); ?>',
2457	'<?php echo esc_attr( $real_column->max_length ); ?>',
2458	'<?php echo esc_attr( $real_column->extra ); ?>',
2459	'<?php echo esc_attr( $real_column->default ); ?>',
2460	'<?php echo esc_attr( $real_column->list ); ?>',
2461	'd'
2462	);
2463	</script>
2464	<?php
2465	$this->table_altered = true;
2466	$this->deleted_columns_and_indexes = true;
2467	}
2468	}
2469	}
2470	}
2471	}
2472	}
2473
2474	// Display indexes.
2475	$indexes_found = false;
2476	foreach ( $this->wpda_table_design->indexes as $design_index ) {
2477	$index_changed = '';
2478	if ( $this->table_exists ) {
2479	$index_found = false;
2480	// Check if index was changed or new.
2481	foreach ( $this->real_indexes as $real_index ) {
2482	if ( $design_index->index_name === $real_index['index_name'] ) {
2483	$index_found = true;
2484	if (
2485	$real_index['unique'] != $design_index->unique \|\|
2486	$real_index['column_names'] != $design_index->column_names
2487	) {
2488	$index_changed = 'u';
2489	$this->updated_indexes = true;
2490	}
2491	break;
2492	}
2493	}
2494	if ( ! $index_found ) {
2495	$index_changed = 'i';
2496	$this->updated_indexes = true;
2497	}
2498	}
2499	?>
2500	<script type='text/javascript'>
2501	add_index(
2502	true,
2503	'<?php echo esc_attr( $design_index->index_name ); ?>',
2504	'<?php echo esc_attr( $design_index->unique ); ?>',
2505	'<?php echo esc_attr( $design_index->column_names ); ?>',
2506	'<?php echo $index_changed; // phpcs:ignore WordPress.Security.EscapeOutput ?>'
2507	);
2508	</script>
2509	<?php
2510	$indexes_found = true;
2511	}
2512
2513	if ( $this->table_exists ) {
2514	foreach ( $this->real_indexes as $real_index ) {
2515	$real_indexes_found = false;
2516	foreach ( $this->wpda_table_design->indexes as $design_index ) {
2517	if ( $real_index['index_name'] === $design_index->index_name ) {
2518	$real_indexes_found = true;
2519	break;
2520	}
2521	}
2522
2523	if ( ! $real_indexes_found ) {
2524	// Index was dropped.
2525	?>
2526	<script type='text/javascript'>
2527	add_index(
2528	true,
2529	'<?php echo esc_attr( $real_index['index_name'] ); ?>',
2530	'<?php echo esc_attr( $real_index['unique'] ); ?>',
2531	'<?php echo esc_attr( $real_index['column_names'] ); ?>',
2532	'<?php echo 'd'; ?>'
2533	);
2534	</script>
2535	<?php
2536	$this->deleted_columns_and_indexes = true;
2537	$this->updated_indexes = true;
2538	}
2539	}
2540	}
2541
2542	if ( ! $indexes_found ) {
2543	?>
2544	<script type='text/javascript'>
2545	add_index(true);
2546	</script>
2547	<?php
2548	}
2549	} else {
2550	// Display one empty row.
2551	?>
2552	<script type='text/javascript'>
2553	add_row('<?php echo esc_attr( $this->design_mode ); ?>', true);
2554	add_index(true);
2555	disable_index();
2556	disable_create_buttons();
2557	</script>
2558	<?php
2559	}
2560
2561	?>
2562	<script type='text/javascript'>
2563	<?php if ( ! $this->wpda_table_design ) { ?>
2564	jQuery('#button_show_create_table').prop("readonly", true).prop("disabled", true).addClass("disabled");
2565	<?php } ?>
2566	<?php
2567	if ( ! $this->wpda_table_design \|\| ! $this->table_altered ) {
2568	if ( ! $this->updated_indexes ) {
2569	?>
2570	jQuery("#button_show_alter_table").prop("readonly", true).prop("disabled", true).addClass("disabled");
2571	jQuery("#button_alter_table").prop("readonly", true).prop("disabled", true).addClass("disabled");
2572	<?php
2573	}
2574	}
2575	?>
2576	<?php if ( ! $this->updated_indexes ) { ?>
2577	jQuery('#wpda_create_index').prop("readonly", true).prop("disabled", true).addClass("disabled");
2578	<?php } ?>
2579	<?php if ( ! $this->real_indexes ) { ?>
2580	jQuery('#wpda_drop_index').prop("readonly", true).prop("disabled", true).addClass("disabled");
2581	<?php } ?>
2582	</script>
2583	<?php
2584
2585	if ( 'view' === $this->action ) {
2586	?>
2587	<script type='text/javascript'>
2588	disable_page();
2589	</script>
2590	<?php
2591	}
2592
2593	if ( $this->is_wp_table ) {
2594	// WP table names are not allowed: disable create table button.
2595	?>
2596	<script type='text/javascript'>
2597	disable_page();
2598	</script>
2599	<?php
2600	}
2601
2602	// Save all table names in array table_name check.
2603	?>
2604	<script type='text/javascript'>
2605	var wpda_db_table_name = [];
2606	<?php
2607	$designer_table_list = WPDA_Design_Table_Model::get_designer_table_list();
2608	foreach ( $designer_table_list as $key => $value ) {
2609	echo 'wpda_db_table_name["' . esc_attr( $value['wpda_table_name'] ) . '"]=true;';
2610	}
2611	if ( ! $this->deleted_columns_and_indexes ) {
2612	echo "jQuery('#checkbox_show_deleted_label').addClass('label_disabled');";
2613	echo "jQuery('#checkbox_show_deleted').attr('disabled', true);";
2614	}
2615	?>
2616	</script>
2617	<?php
2618	}
2619
2620	/**
2621	* Drop all indexes from database
2622	*
2623	* @since 2.0.14
2624	*/
2625	protected function drop_indexes() {
2626	foreach ( $this->real_indexes as $real_index ) {
2627	$this->drop_index( $real_index['index_name'] );
2628	}
2629	}
2630
2631	/**
2632	* Drop a specific index from database
2633	*
2634	* @param string $index_name Name of index to be dropped
2635	*
2636	* @since 2.0.14
2637	*/
2638	protected function drop_index( $index_name ) {
2639	$wpdadb = WPDADB::get_db_connection( $this->wpda_schema_name );
2640	if ( null === $wpdadb ) {
2641	wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->wpda_schema_name ) ) );
2642	}
2643
2644	$suppress = $wpdadb->suppress_errors( true );
2645
2646	// Index is deleted from table design: drop index
2647	$drop_index_statement = 'DROP INDEX `' . str_replace( '`', '', $index_name ) . "` ON `{$this->wpda_table_name}`";
2648	if ( $wpdadb->query( $drop_index_statement ) ) {
2649	$msg = new WPDA_Message_Box(
2650	array(
2651	'message_text' => sprintf( __( 'Index `%s` dropped', 'wp-data-access' ), esc_attr( $index_name) ),
2652	)
2653	);
2654	$msg->box();
2655	} else {
2656	$msg = new WPDA_Message_Box(
2657	array(
2658	'message_text' => __( 'DROP INDEX failed', 'wp-data-access' ),
2659	'message_type' => 'error',
2660	'message_is_dismissible' => false,
2661	)
2662	);
2663	$msg->box();
2664	$this->create_index_failed[] = $drop_index_statement;
2665	}
2666	$wpdadb->suppress_errors( $suppress );
2667	}
2668
2669	/**
2670	* Create indexes from design
2671	*
2672	* @since 2.0.14
2673	*/
2674	protected function create_index() {
2675	$wpdadb = WPDADB::get_db_connection( $this->wpda_schema_name );
2676	if ( null === $wpdadb ) {
2677	wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->wpda_schema_name ) ) );
2678	}
2679
2680	$suppress = $wpdadb->suppress_errors( true );
2681
2682	if ( 'show_create_table_script' !== $this->action2_posted ) {
2683	$this->drop_indexes();
2684	}
2685
2686	// Recreate indexes.
2687	foreach ( $this->wpda_table_design->indexes as $index ) {
2688	if ( '' === $index->index_name \|\| '' === $index->column_names ) {
2689	continue;
2690	}
2691
2692	$unique = '';
2693	if ( 'Yes' === $index->unique ) {
2694	$unique = 'UNIQUE';
2695	} elseif ( 'FULLTEXT' === $index->unique ) {
2696	if ( 'on' === $this->fulltext_support ) {
2697	$unique = 'FULLTEXT';
2698	} else {
2699	$unique = '';
2700	}
2701	}
2702	$column_names_array = explode( ',', $index->column_names );
2703	$column_names = '`' . implode( '`,`', $column_names_array ) . '`';
2704	$create_index_statement =
2705	"CREATE $unique INDEX `" . str_replace( '`', '', $index->index_name ) . "` ON `{$this->wpda_table_name}` ($column_names)";
2706	$this->create_index_statement .= $create_index_statement . ';' . self::NEW_LINE;
2707
2708	if ( 'show_create_table_script' === $this->action2_posted ) {
2709	continue;
2710	}
2711
2712	if ( $wpdadb->query( $create_index_statement ) ) {
2713	$msg = new WPDA_Message_Box(
2714	array(
2715	'message_text' => sprintf( __( 'Index `%s` created', 'wp-data-access' ), $index->index_name ),
2716	)
2717	);
2718	$msg->box();
2719	} else {
2720	$msg = new WPDA_Message_Box(
2721	array(
2722	'message_text' => __( 'CREATE INDEX failed', 'wp-data-access' ),
2723	'message_type' => 'error',
2724	'message_is_dismissible' => false,
2725	)
2726	);
2727	$msg->box();
2728	$this->create_index_failed[] = $create_index_statement;
2729	}
2730	}
2731
2732	$wpdadb->suppress_errors( $suppress );
2733	}
2734	}
2735
2736	}
2737