TableManager::upgrade_db_schema() - Code Metrics - Inspection of "Add columns to the email_log table" - sudar/email-log - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 106-feature/store-attachment-n... ( 0b152b )

by Maria Daniel Deepak

created 2018-08-13 14:07 UTC

TableManager::upgrade_db_schema() B

↳ Parent: TableManager

Complexity

Conditions	8
Paths	9

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	72

Importance

Changes

Metric	Value
cc	8
nc	9
nop	0
dl	0
loc	39
ccs	0
cts	26
cp	0
crap	72
rs	8.0515
c	0
b	0
f	0

<?php namespace EmailLog\Core\DB;

/**
 * Handle installation and db table creation.
 */
use EmailLog\Core\Loadie;

defined( 'ABSPATH' ) || exit; // Exit if accessed directly.

/**
 * Helper class to create table.
 *
 * @since 2.0.0
 */
class TableManager implements Loadie {

	/* Database table name */
	const LOG_TABLE_NAME = 'email_log';

	/* Database option name */
	const DB_OPTION_NAME = 'email-log-db';

	/* Database version */
	const DB_VERSION = '0.2';

	/**
	 * Setup hooks.
	 */
	public function load() {
		add_action( 'wpmu_new_blog', array( $this, 'create_table_for_new_blog' ) );

		add_filter( 'wpmu_drop_tables', array( $this, 'delete_table_from_deleted_blog' ) );

		// Do any DB upgrades.
		$this->upgrade_db_schema();
	}

	/**
	 * On plugin activation, create table if needed.
	 *
	 * @param bool $network_wide True if the plugin was network activated.
	 */
	public function on_activate( $network_wide ) {
		if ( is_multisite() && $network_wide ) {
			// Note: if there are more than 10,000 blogs or
			// if `wp_is_large_network` filter is set, then this may fail.
			$sites = get_sites();

			foreach ( $sites as $site ) {
				switch_to_blog( $site['blog_id'] );
				$this->create_table();
				restore_current_blog();
			}
		} else {
			$this->create_table();
		}
	}

	/**
	 * Create email log table when a new blog is created.
	 *
	 * @param int $blog_id Blog Id.
	 */
	public function create_table_for_new_blog( $blog_id ) {
		if ( is_plugin_active_for_network( 'email-log/email-log.php' ) ) {
			switch_to_blog( $blog_id );
			$this->create_table();
			restore_current_blog();
		}
	}

	/**
	 * Add email log table to the list of tables deleted when a blog is deleted.
	 *
	 * @param array $tables List of tables to be deleted.
	 *
	 * @return string[] $tables Modified list of tables to be deleted.
	 */
	public function delete_table_from_deleted_blog( $tables ) {
		$tables[] = $this->get_log_table_name();

		return $tables;
	}

	/**
	 * Get email log table name.
	 *
	 * @return string Email Log Table name.
	 */
	public function get_log_table_name() {
		global $wpdb;

		return $wpdb->prefix . self::LOG_TABLE_NAME;
	}

	/**
	 * Insert log data into DB.
	 *
	 * @param array $data Data to be inserted.
	 */
	public function insert_log( $data ) {
		global $wpdb;

		$table_name = $this->get_log_table_name();
		$wpdb->insert( $table_name, $data );
	}

	/**
	 * Delete log entries by ids.
	 *
	 * @param string $ids Comma separated list of log ids.
	 *
	 * @return false|int Number of log entries that got deleted. False on failure.
	 */
	public function delete_logs( $ids ) {
		global $wpdb;

		$table_name = $this->get_log_table_name();

		// Can't use wpdb->prepare for the below query. If used it results in this bug // https://github.com/sudar/email-log/issues/13.
		$ids = esc_sql( $ids );

		return $wpdb->query( "DELETE FROM {$table_name} where id IN ( {$ids} )" ); //@codingStandardsIgnoreLine
	}

	/**
	 * Delete all log entries.
	 *
	 * @return false|int Number of log entries that got deleted. False on failure.
	 */
	public function delete_all_logs() {
		global $wpdb;

		$table_name = $this->get_log_table_name();

		return $wpdb->query( "DELETE FROM {$table_name}" ); //@codingStandardsIgnoreLine
	}

	/**
	 * Deletes Email Logs older than the specified interval.
	 *
	 * @param int $interval_in_days No. of days beyond which logs are to be deleted.
	 *
	 * @return int $deleted_rows_count  Count of rows deleted.
	 */
	public function delete_logs_older_than( $interval_in_days ) {
		global $wpdb;
		$table_name = $this->get_log_table_name();

		$query              = $wpdb->prepare( "DELETE FROM {$table_name} WHERE sent_date < DATE_SUB( CURDATE(), INTERVAL %d DAY )", $interval_in_days );
		$deleted_rows_count = $wpdb->query( $query );

		return $deleted_rows_count;
	}

	/**
	 * Fetch log item by ID.
	 *
	 * @param array $ids Optional. Array of IDs of the log items to be retrieved.
	 *
	 * @return array Log item(s).
	 */
	public function fetch_log_items_by_id( $ids = array() ) {
		global $wpdb;
		$table_name = $this->get_log_table_name();

		$query = "SELECT * FROM {$table_name}";

		if ( ! empty( $ids ) ) {
			$ids = array_map( 'absint', $ids );

			// Can't use wpdb->prepare for the below query. If used it results in this bug https://github.com/sudar/email-log/issues/13.
			$ids_list = esc_sql( implode( ',', $ids ) );

			$query .= " where id IN ( {$ids_list} )";
		}

		return $wpdb->get_results( $query, 'ARRAY_A' ); //@codingStandardsIgnoreLine
	}

	/**
	 * Fetch log items.
	 *
	 * @param array $request         Request object.
	 * @param int   $per_page        Entries per page.
	 * @param int   $current_page_no Current page no.
	 *
	 * @return array Log entries and total items count.
	 */
	public function fetch_log_items( $request, $per_page, $current_page_no ) {
		global $wpdb;
		$table_name = $this->get_log_table_name();

		$query       = 'SELECT * FROM ' . $table_name;
		$count_query = 'SELECT count(*) FROM ' . $table_name;
		$query_cond  = '';

		if ( isset( $request['s'] ) && $request['s'] !== '' ) {
			$search_term = trim( esc_sql( $request['s'] ) );
			$query_cond .= " WHERE ( to_email LIKE '%$search_term%' OR subject LIKE '%$search_term%' ) ";
		}

		if ( isset( $request['d'] ) && $request['d'] !== '' ) {
			$search_date = trim( esc_sql( $request['d'] ) );
			if ( '' === $query_cond ) {
				$query_cond .= " WHERE sent_date BETWEEN '$search_date 00:00:00' AND '$search_date 23:59:59' ";
			} else {
				$query_cond .= " AND sent_date BETWEEN '$search_date 00:00:00' AND '$search_date 23:59:59' ";
			}
		}

		// Ordering parameters.
		$orderby = ! empty( $request['orderby'] ) ? esc_sql( $request['orderby'] ) : 'sent_date';
		$order   = ! empty( $request['order'] ) ? esc_sql( $request['order'] ) : 'DESC';

		if ( ! empty( $orderby ) & ! empty( $order ) ) {
			$query_cond .= ' ORDER BY ' . $orderby . ' ' . $order;
		}

		// Find total number of items.
		$count_query = $count_query . $query_cond;
		$total_items = $wpdb->get_var( $count_query );

		// Adjust the query to take pagination into account.
		if ( ! empty( $current_page_no ) && ! empty( $per_page ) ) {
			$offset = ( $current_page_no - 1 ) * $per_page;
			$query_cond .= ' LIMIT ' . (int) $offset . ',' . (int) $per_page;
		}

		// Fetch the items.
		$query = $query . $query_cond;
		$items = $wpdb->get_results( $query );

		return array( $items, $total_items );
	}

	/**
	 * Create email log table.
	 *
	 * @access private
	 *
	 * @global object $wpdb
	 */
	private function create_table() {
		global $wpdb;

		$table_name      = $this->get_log_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		if ( $wpdb->get_var( "show tables like '{$table_name}'" ) != $table_name ) {

			$sql = 'CREATE TABLE ' . $table_name . ' (
				id mediumint(9) NOT NULL AUTO_INCREMENT,
				to_email VARCHAR(100) NOT NULL,
				subject VARCHAR(250) NOT NULL,
				message TEXT NOT NULL,
				headers TEXT NOT NULL,
				attachments TEXT NOT NULL,
				sent_date timestamp NOT NULL,
				attachment_name VARCHAR(1000),
				ip_address VARCHAR(15),
				result TINYINT(1)
				PRIMARY KEY  (id)
			) ' . $charset_collate . ' ;';

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );

			add_option( self::DB_OPTION_NAME, self::DB_VERSION );
		}
	}

	/**
	 * Get the total number of email logs.
	 *
	 * @return int Total email log count
	 */
	public function get_logs_count() {
		global $wpdb;

		$query = 'SELECT count(*) FROM ' . $this->get_log_table_name();

		return $wpdb->get_var( $query );
	}

	/**
	 * Upgrades the DB schema.
	 *
	 * Adds new columns to the Database as of v0.2.
	 *
	 * @since 2.3.0
	 */
	public function upgrade_db_schema() {
		global $wpdb;
		$existing_db_version = get_option( self::DB_OPTION_NAME, false );
		$updated_db_version  = self::DB_VERSION;

		if ( ! $existing_db_version || $existing_db_version === $updated_db_version ) {
			return;
		}

		$table_name = $this->get_log_table_name();

		switch ( trim( $existing_db_version ) ) {
			case '0.1':
				$sql                = 'ALTER TABLE ' . $table_name . ' 
				ADD COLUMN attachment_name VARCHAR(1000) AFTER sent_date,
				ADD COLUMN ip_address VARCHAR(15),
				ADD COLUMN result TINYINT(1);';
				$updated_db_version = '0.2';
				break;
		}

		if ( empty( $sql ) || empty( $updated_db_version ) ) {
			return;
		}

		$columns_sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'' . $table_name . '\'
		AND (COLUMN_NAME = \'attachment_name\' OR COLUMN_NAME = \'ip_address\' OR COLUMN_NAME = \'result\' );';

		$rows = $wpdb->get_results( $columns_sql );

		if ( count( $rows ) != 0 ) {
			return;
		}

		$result = $wpdb->query( $sql );
		if ( $result ) {
			update_option( self::DB_OPTION_NAME, $updated_db_version );
		}
	}
}


1		<?php namespace EmailLog\Core\DB;
2
3		/**
4		* Handle installation and db table creation.
5		*/
6		use EmailLog\Core\Loadie;
7
8		defined( 'ABSPATH' ) \|\| exit; // Exit if accessed directly.
9
10		/**
11		* Helper class to create table.
12		*
13		* @since 2.0.0
14		*/
15		class TableManager implements Loadie {
16
17		/* Database table name */
18		const LOG_TABLE_NAME = 'email_log';
19
20		/* Database option name */
21		const DB_OPTION_NAME = 'email-log-db';
22
23		/* Database version */
24		const DB_VERSION = '0.2';
25
26		/**
27		* Setup hooks.
28		*/
29		public function load() {
30		add_action( 'wpmu_new_blog', array( $this, 'create_table_for_new_blog' ) );
31
32		add_filter( 'wpmu_drop_tables', array( $this, 'delete_table_from_deleted_blog' ) );
33
34		// Do any DB upgrades.
35		$this->upgrade_db_schema();
36		}
37
38		/**
39		* On plugin activation, create table if needed.
40		*
41		* @param bool $network_wide True if the plugin was network activated.
42		*/
43		public function on_activate( $network_wide ) {
44		if ( is_multisite() && $network_wide ) {
45		// Note: if there are more than 10,000 blogs or
46		// if `wp_is_large_network` filter is set, then this may fail.
47		$sites = get_sites();
48
49		foreach ( $sites as $site ) {
50		switch_to_blog( $site['blog_id'] );
51		$this->create_table();
52		restore_current_blog();
53		}
54		} else {
55		$this->create_table();
56		}
57		}
58
59		/**
60		* Create email log table when a new blog is created.
61		*
62		* @param int $blog_id Blog Id.
63		*/
64		public function create_table_for_new_blog( $blog_id ) {
65		if ( is_plugin_active_for_network( 'email-log/email-log.php' ) ) {
66		switch_to_blog( $blog_id );
67		$this->create_table();
68		restore_current_blog();
69		}
70		}
71
72		/**
73		* Add email log table to the list of tables deleted when a blog is deleted.
74		*
75		* @param array $tables List of tables to be deleted.
76		*
77		* @return string[] $tables Modified list of tables to be deleted.
78		*/
79	1	public function delete_table_from_deleted_blog( $tables ) {
80	1	$tables[] = $this->get_log_table_name();
81
82	1	return $tables;
83		}
84
85		/**
86		* Get email log table name.
87		*
88		* @return string Email Log Table name.
89		*/
90	2	public function get_log_table_name() {
91	2	global $wpdb;
92
93	2	return $wpdb->prefix . self::LOG_TABLE_NAME;
94		}
95
96		/**
97		* Insert log data into DB.
98		*
99		* @param array $data Data to be inserted.
100		*/
101		public function insert_log( $data ) {
102		global $wpdb;
103
104		$table_name = $this->get_log_table_name();
105		$wpdb->insert( $table_name, $data );
106		}
107
108		/**
109		* Delete log entries by ids.
110		*
111		* @param string $ids Comma separated list of log ids.
112		*
113		* @return false\|int Number of log entries that got deleted. False on failure.
114		*/
115		public function delete_logs( $ids ) {
116		global $wpdb;
117
118		$table_name = $this->get_log_table_name();
119
120		// Can't use wpdb->prepare for the below query. If used it results in this bug // https://github.com/sudar/email-log/issues/13.
121		$ids = esc_sql( $ids );
122
123		return $wpdb->query( "DELETE FROM {$table_name} where id IN ( {$ids} )" ); //@codingStandardsIgnoreLine
124		}
125
126		/**
127		* Delete all log entries.
128		*
129		* @return false\|int Number of log entries that got deleted. False on failure.
130		*/
131		public function delete_all_logs() {
132		global $wpdb;
133
134		$table_name = $this->get_log_table_name();
135
136		return $wpdb->query( "DELETE FROM {$table_name}" ); //@codingStandardsIgnoreLine
137		}
138
139		/**
140		* Deletes Email Logs older than the specified interval.
141		*
142		* @param int $interval_in_days No. of days beyond which logs are to be deleted.
143		*
144		* @return int $deleted_rows_count Count of rows deleted.
145		*/
146		public function delete_logs_older_than( $interval_in_days ) {
147		global $wpdb;
148		$table_name = $this->get_log_table_name();
149
150		$query = $wpdb->prepare( "DELETE FROM {$table_name} WHERE sent_date < DATE_SUB( CURDATE(), INTERVAL %d DAY )", $interval_in_days );
151		$deleted_rows_count = $wpdb->query( $query );
152
153		return $deleted_rows_count;
154		}
155
156		/**
157		* Fetch log item by ID.
158		*
159		* @param array $ids Optional. Array of IDs of the log items to be retrieved.
160		*
161		* @return array Log item(s).
162		*/
163		public function fetch_log_items_by_id( $ids = array() ) {
164		global $wpdb;
165		$table_name = $this->get_log_table_name();
166
167		$query = "SELECT * FROM {$table_name}";
168
169		if ( ! empty( $ids ) ) {
170		$ids = array_map( 'absint', $ids );
171
172		// Can't use wpdb->prepare for the below query. If used it results in this bug https://github.com/sudar/email-log/issues/13.
173		$ids_list = esc_sql( implode( ',', $ids ) );
174
175		$query .= " where id IN ( {$ids_list} )";
176		}
177
178		return $wpdb->get_results( $query, 'ARRAY_A' ); //@codingStandardsIgnoreLine
179		}
180
181		/**
182		* Fetch log items.
183		*
184		* @param array $request Request object.
185		* @param int $per_page Entries per page.
186		* @param int $current_page_no Current page no.
187		*
188		* @return array Log entries and total items count.
189		*/
190		public function fetch_log_items( $request, $per_page, $current_page_no ) {
191		global $wpdb;
192		$table_name = $this->get_log_table_name();
193
194		$query = 'SELECT * FROM ' . $table_name;
195		$count_query = 'SELECT count(*) FROM ' . $table_name;
196		$query_cond = '';
197
198		if ( isset( $request['s'] ) && $request['s'] !== '' ) {
199		$search_term = trim( esc_sql( $request['s'] ) );
200		$query_cond .= " WHERE ( to_email LIKE '%$search_term%' OR subject LIKE '%$search_term%' ) ";
201		}
202
203		if ( isset( $request['d'] ) && $request['d'] !== '' ) {
204		$search_date = trim( esc_sql( $request['d'] ) );
205		if ( '' === $query_cond ) {
206		$query_cond .= " WHERE sent_date BETWEEN '$search_date 00:00:00' AND '$search_date 23:59:59' ";
207		} else {
208		$query_cond .= " AND sent_date BETWEEN '$search_date 00:00:00' AND '$search_date 23:59:59' ";
209		}
210		}
211
212		// Ordering parameters.
213		$orderby = ! empty( $request['orderby'] ) ? esc_sql( $request['orderby'] ) : 'sent_date';
214		$order = ! empty( $request['order'] ) ? esc_sql( $request['order'] ) : 'DESC';
215
216		if ( ! empty( $orderby ) & ! empty( $order ) ) {
217		$query_cond .= ' ORDER BY ' . $orderby . ' ' . $order;
218		}
219
220		// Find total number of items.
221		$count_query = $count_query . $query_cond;
222		$total_items = $wpdb->get_var( $count_query );
223
224		// Adjust the query to take pagination into account.
225		if ( ! empty( $current_page_no ) && ! empty( $per_page ) ) {
226		$offset = ( $current_page_no - 1 ) * $per_page;
227		$query_cond .= ' LIMIT ' . (int) $offset . ',' . (int) $per_page;
228		}
229
230		// Fetch the items.
231		$query = $query . $query_cond;
232		$items = $wpdb->get_results( $query );
233
234		return array( $items, $total_items );
235		}
236
237		/**
238		* Create email log table.
239		*
240		* @access private
241		*
242		* @global object $wpdb
243		*/
244		private function create_table() {
245		global $wpdb;
246
247		$table_name = $this->get_log_table_name();
248		$charset_collate = $wpdb->get_charset_collate();
249
250		if ( $wpdb->get_var( "show tables like '{$table_name}'" ) != $table_name ) {
251
252		$sql = 'CREATE TABLE ' . $table_name . ' (
253		id mediumint(9) NOT NULL AUTO_INCREMENT,
254		to_email VARCHAR(100) NOT NULL,
255		subject VARCHAR(250) NOT NULL,
256		message TEXT NOT NULL,
257		headers TEXT NOT NULL,
258		attachments TEXT NOT NULL,
259		sent_date timestamp NOT NULL,
260		attachment_name VARCHAR(1000),
261		ip_address VARCHAR(15),
262		result TINYINT(1)
263		PRIMARY KEY (id)
264		) ' . $charset_collate . ' ;';
265
266		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
267		dbDelta( $sql );
268
269		add_option( self::DB_OPTION_NAME, self::DB_VERSION );
270		}
271		}
272
273		/**
274		* Get the total number of email logs.
275		*
276		* @return int Total email log count
277		*/
278		public function get_logs_count() {
279		global $wpdb;
280
281		$query = 'SELECT count(*) FROM ' . $this->get_log_table_name();
282
283		return $wpdb->get_var( $query );
284		}
285
286		/**
287		* Upgrades the DB schema.
288		*
289		* Adds new columns to the Database as of v0.2.
290		*
291		* @since 2.3.0
292		*/
293		public function upgrade_db_schema() {
294		global $wpdb;
295		$existing_db_version = get_option( self::DB_OPTION_NAME, false );
296		$updated_db_version = self::DB_VERSION;
297
298		if ( ! $existing_db_version \|\| $existing_db_version === $updated_db_version ) {
299		return;
300		}
301
302		$table_name = $this->get_log_table_name();
303
304		switch ( trim( $existing_db_version ) ) {
305		case '0.1':
306		$sql = 'ALTER TABLE ' . $table_name . '
307		ADD COLUMN attachment_name VARCHAR(1000) AFTER sent_date,
308		ADD COLUMN ip_address VARCHAR(15),
309		ADD COLUMN result TINYINT(1);';
310		$updated_db_version = '0.2';
311		break;
312		}
313
314		if ( empty( $sql ) \|\| empty( $updated_db_version ) ) {
315		return;
316		}
317
318		$columns_sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'' . $table_name . '\'
319		AND (COLUMN_NAME = \'attachment_name\' OR COLUMN_NAME = \'ip_address\' OR COLUMN_NAME = \'result\' );';
320
321		$rows = $wpdb->get_results( $columns_sql );
322
323		if ( count( $rows ) != 0 ) {
324		return;
325		}
326
327		$result = $wpdb->query( $sql );
328		if ( $result ) {
329		update_option( self::DB_OPTION_NAME, $updated_db_version );
330		}
331		}
332		}
333

sudar / email-log

Push — 106-feature/store-attachment-n... ( 0b152b )

TableManager::upgrade_db_schema() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like