· 8 years ago · Mar 09, 2018, 06:34 PM
1<?php
2
3/**
4 *
5 * Author: Ben Greene
6 * Version: 1.0
7 *
8 * Description:
9 * Class that creates and maintains a custom WP table.
10 *
11 * There are no get/insert functions, those are left to the individual
12 * implementations that are built on top of this.
13 *
14 * Make sure you pass in a valid `.ini` file that can be used
15 * to build/update the DB
16 */
17
18class Database_Table_Manager {
19
20 protected $version;
21 protected $table_name;
22 protected $columns;
23
24 public function init_db( $path_to_ini ) {
25 $setup = $this->setup_info( $path_to_ini );
26
27 if( false !== $setup ) {
28 if( ! $this->table_exists() ) {
29 $this->create_table();
30 }
31
32 if( is_admin() &&
33 ! $this->table_up_to_date() &&
34 isset( $_POST['should_update_table_' . $this->table_name ] ) &&
35 $_POST['should_update_table_' . $this->table_name ] ) {
36 $this->update_table();
37 }
38
39 add_action( 'admin_notices', array( $this, 'display_update_notice' ) );
40 }
41 else {
42 error_log( 'Error setting up a table from the file: ' . $path_to_ini . '. Please check your settings.' );
43 }
44 }
45
46 // Display to the admin that they should update the table.
47 public function display_update_notice() {
48 if( ! is_admin() ||
49 $this->table_up_to_date() ) {
50 return;
51 } ?>
52 <div class="notice notice-success">
53 <p>You need to update the database for <?php echo $this->table_name ?>. <form action="" method="POST"><input type="hidden" name="should_update_table_<?php echo $this->table_name; ?>" value="true"/><?php submit_button('Update Now'); ?></form></p>
54 </div>
55 <?php }
56
57 protected function setup_info( $path_to_ini ) {
58 $settings = parse_ini_file( $path_to_ini, true );
59 if( false === $settings ) {
60 return false;
61 }
62
63 // Check if general info is present, if not return false.
64 if( isset( $settings['general_info'] ) &&
65 isset( $settings['general_info']['table_name'] ) &&
66 isset( $settings['general_info']['table_version'] ) ) {
67 $this->table_name = $settings['general_info']['table_name'];
68 $this->version = $settings['general_info']['table_version'];
69 } else {
70 return false;
71 }
72
73 // Check if there are columns in the ini file, if not return false.
74 if( isset( $settings['columns'] ) ) {
75 $this->columns = $settings['columns'];
76 } else {
77 return false;
78 }
79
80 return true;
81 }
82
83 /**
84 * Loop through the table columns and build the new table.
85 */
86 protected function create_table() {
87 require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
88 global $wpdb;
89 $full_table_name = $wpdb->prefix . $this->table_name;
90 $charset = $wpdb->get_charset_collate();
91
92 $sql = "CREATE TABLE $full_table_name ( ID mediumint NOT NULL AUTO_INCREMENT, ";
93 // Build each column individually.
94 foreach( $this->columns as $col_name => $col_settings ) {
95 // If there isn't a default passed, default is 'null'
96 $default = isset( $col_settings['default'] ) ? 'DEFAULT \'' . $col_settings['default'] . '\'' : null;
97 // Check if the column should allow NULL values
98 $not_null = isset( $col_settings['allow_null'] ) ? '' : 'NOT NULL';
99 // Put together the query string pieces
100 $sql .= sprintf( '%s %s %s %s, ', $col_name, $col_settings['type'], $default, $not_null);
101 }
102 $sql .= "PRIMARY KEY (ID) ) $charset;";
103 dbDelta( $sql );
104 update_option( $this->table_name . '_table_version', $this->version );
105 }
106
107 protected function update_table() {
108 require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
109 global $wpdb;
110
111 $full_table_name = $wpdb->prefix . $this->table_name;
112 $sql = "";
113 $line_prefix = "ALTER TABLE $full_table_name \n";
114
115 foreach ( $this->columns as $name => $col_settings ) {
116 // check column exists to determine the command to use
117 $result = $wpdb->get_var( "SHOW COLUMNS FROM `$full_table_name` LIKE '$name'" );
118 $cmd = !empty( $result ) ? 'MODIFY COLUMN' : 'ADD';
119 // If there isn't a default passed, default is 'null'
120 $default = '';
121 if( empty( $result ) ) {
122 $default = isset( $col_settings['default'] ) ? ' DEFAULT \'' . $col_settings['default'] . '\'' : null;
123 }
124 // Check if the column should allow NULL values
125 $not_null = isset( $col_settings['allow_null'] ) ? '' : 'NOT NULL';
126
127 // Put together the query string pieces
128 $sql = sprintf( '%s %s %s %s %s %s;', $line_prefix, $cmd, $name, $col_settings['type'], $not_null, $default);
129 $results = $wpdb->query( $sql );
130 }
131
132 update_option( $this->table_name . '_table_version', $this->version );
133 }
134
135 protected function str_lreplace( $search, $replace, $subject ) {
136 $pos = strrpos($subject, $search);
137
138 if($pos !== false)
139 {
140 $subject = substr_replace($subject, $replace, $pos, strlen($search));
141 }
142
143 return $subject;
144 }
145
146 /**
147 * ------------------------------------------------------------------
148 * Helpers for checking if the table is correctly setup
149 * ------------------------------------------------------------------
150 */
151
152 protected function table_exists() {
153 global $wpdb;
154 $full_table_name = $wpdb->prefix . $this->table_name;
155
156 if( $full_table_name == $wpdb->get_var("SHOW TABLES LIKE '$full_table_name'") ) {
157 return true;
158 } else {
159 return false;
160 }
161 }
162
163 protected function table_up_to_date() {
164 $table_version = get_option( $this->table_name . '_table_version' );
165 if( $table_version == $this->version ) {
166 return true;
167 }
168 return false;
169 }
170}