Automator_DB

This class is used to run any configurations before the plugin is initialized

Contents

  • Methods

  • Source Source

    File: src/core/class-automator-db.php

    class Automator_DB {
    
    	/**
    	 * The instance of the class
    	 *
    	 * @since    1.0.0
    	 * @access   private
    	 * @var      \Uncanny_Automator\Automator_DB
    	 */
    	private static $instance;
    
    	/**
    	 * Creates singleton instance of class
    	 *
    	 * @return Automator_DB $instance
    	 * @since 1.0.0
    	 *
    	 */
    	public static function get_instance() {
    
    		if ( null === self::$instance ) {
    			self::$instance = new self();
    		}
    
    		return self::$instance;
    	}
    
    	/**
    	 * Validates if all Automator tables exists
    	 *
    	 * @param false $execute
    	 *
    	 * @return array
    	 * @since 3.0
    	 */
    	public static function verify_base_tables( $execute = false ) {
    		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    
    		if ( $execute ) {
    			self::create_tables();
    			self::create_views();
    		}
    		$queries        = dbDelta( self::get_schema(), false );
    		$missing_tables = array();
    		foreach ( $queries as $table_name => $result ) {
    			if ( "Created table $table_name" === $result ) {
    				$missing_tables[] = $table_name;
    			}
    		}
    
    		if ( 0 < count( $missing_tables ) ) {
    			update_option( 'automator_schema_missing_tables', $missing_tables );
    		} else {
    			update_option( 'uap_database_version', AUTOMATOR_DATABASE_VERSION );
    			delete_option( 'automator_schema_missing_tables' );
    			delete_option( 'automator_schema_missing_views' );
    		}
    
    		return $missing_tables;
    	}
    
    	/**
    	 * @return array
    	 */
    	public static function verify_base_views() {
    		$missing_views = self::all_views( true );
    
    		if ( ! empty( $missing_views ) ) {
    			update_option( 'automator_schema_missing_views', $missing_views );
    		}
    
    		return $missing_views;
    	}
    
    	/**
    	 * Return create queries for Automator tables
    	 *
    	 * @return string
    	 * @since 3.0
    	 */
    	public static function get_schema() {
    		global $wpdb;
    		$charset_collate = $wpdb->get_charset_collate();
    		// Automator Recipe log
    		$tbl_recipe_log = $wpdb->prefix . 'uap_recipe_log';
    		//Automator trigger log
    		$tbl_trigger_log = $wpdb->prefix . 'uap_trigger_log';
    		//Automator trigger meta data log
    		$tbl_trigger_log_meta = $wpdb->prefix . 'uap_trigger_log_meta';
    		// Automator Action log
    		$tbl_action_log = $wpdb->prefix . 'uap_action_log';
    		//Automator action meta data log
    		$tbl_action_log_meta = $wpdb->prefix . 'uap_action_log_meta';
    		// Automator Closure Log
    		$tbl_closure_log = $wpdb->prefix . 'uap_closure_log';
    		//Automator closure meta data log
    		$tbl_closure_log_meta = $wpdb->prefix . 'uap_closure_log_meta';
    
    		return "CREATE TABLE {$tbl_recipe_log} (
    `ID` bigint unsigned NOT NULL auto_increment,
    `date_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `user_id` bigint unsigned NOT NULL,
    `automator_recipe_id` bigint unsigned NOT NULL,
    `completed` tinyint(1) NOT NULL,
    `run_number` mediumint unsigned NOT NULL DEFAULT 1,
    PRIMARY KEY  (`ID`),
    KEY completed (`completed`),
    KEY user_id (`user_id`),
    KEY automator_recipe_id (`automator_recipe_id`)
    ) ENGINE=InnoDB {$charset_collate};
    CREATE TABLE {$tbl_trigger_log} (
    `ID` bigint unsigned NOT NULL auto_increment,
    `date_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `user_id` bigint unsigned NOT NULL,
    `automator_trigger_id` bigint unsigned NOT NULL,
    `automator_recipe_id` bigint unsigned NOT NULL,
    `automator_recipe_log_id` bigint unsigned NULL,
    `completed` tinyint(1) unsigned NOT NULL,
    PRIMARY KEY  (`ID`),
    KEY user_id (`user_id`),
    KEY completed (`completed`),
    KEY automator_recipe_id (`automator_recipe_id`),
    KEY automator_trigger_id (`automator_trigger_id`),
    KEY automator_recipe_log_id (`automator_recipe_log_id`)
    ) ENGINE=InnoDB {$charset_collate};
    CREATE TABLE {$tbl_trigger_log_meta} (
    `ID` bigint unsigned NOT NULL auto_increment,
    `user_id` bigint unsigned NOT NULL,
    `automator_trigger_log_id` bigint unsigned NULL,
    `automator_trigger_id` bigint unsigned NOT NULL,
    `meta_key` varchar(255) DEFAULT '' NOT NULL,
    `meta_value` longtext NULL,
    `run_number` mediumint unsigned NOT NULL DEFAULT 1,
    `run_time` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY  (`ID`),
    KEY user_id (`user_id`),
    KEY run_number (`run_number`),
    KEY automator_trigger_id (`automator_trigger_id`),
    KEY automator_trigger_log_id (`automator_trigger_log_id`),
    KEY meta_key (meta_key(20))
    ) ENGINE=InnoDB {$charset_collate};
    CREATE TABLE {$tbl_action_log} (
    `ID` bigint unsigned NOT NULL auto_increment,
    `date_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `user_id` bigint unsigned NOT NULL,
    `automator_action_id` bigint unsigned NOT NULL,
    `automator_recipe_id` bigint unsigned NOT NULL,
    `automator_recipe_log_id` bigint unsigned NULL,
    `completed` tinyint(1) unsigned NOT NULL,
    `error_message` longtext NULL,
    PRIMARY KEY  (`ID`),
    KEY user_id (`user_id`),
    KEY completed (`completed`),
    KEY automator_action_id (`automator_action_id`),
    KEY automator_recipe_log_id (`automator_recipe_log_id`),
    KEY automator_recipe_id (`automator_recipe_id`)
    ) ENGINE=InnoDB {$charset_collate};
    CREATE TABLE {$tbl_action_log_meta} (
    `ID` bigint unsigned NOT NULL auto_increment,
    `user_id` bigint unsigned NOT NULL,
    `automator_action_log_id` bigint unsigned NULL,
    `automator_action_id` bigint unsigned NOT NULL,
    `meta_key` varchar(255) DEFAULT '' NOT NULL,
    `meta_value` longtext NULL,
    PRIMARY KEY  (`ID`),
    KEY user_id (`user_id`),
    KEY automator_action_log_id (`automator_action_log_id`),
    KEY automator_action_id (`automator_action_id`),
    KEY meta_key (meta_key(20))
    ) ENGINE=InnoDB {$charset_collate};
    CREATE TABLE {$tbl_closure_log} (
    `ID` bigint unsigned NOT NULL auto_increment,
    `date_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `user_id` bigint unsigned NOT NULL,
    `automator_closure_id` bigint unsigned NOT NULL,
    `automator_recipe_id` bigint unsigned NOT NULL,
    `completed` tinyint(1) unsigned NOT NULL,
    PRIMARY KEY  (`ID`),
    KEY user_id (`user_id`),
    KEY automator_recipe_id (`automator_recipe_id`),
    KEY automator_closure_id (`automator_closure_id`),
    KEY completed (`completed`)
    ) ENGINE=InnoDB {$charset_collate};
    CREATE TABLE {$tbl_closure_log_meta} (
    `ID` bigint unsigned NOT NULL auto_increment,
    `user_id` bigint unsigned NOT NULL,
    `automator_closure_id` bigint unsigned NOT NULL,
    `meta_key` varchar(255) DEFAULT '' NOT NULL,
    `meta_value` longtext NULL,
    PRIMARY KEY  (`ID`),
    KEY user_id (`user_id`),
    KEY automator_closure_id (`automator_closure_id`),
    KEY meta_key (meta_key(15))
    ) ENGINE=InnoDB {$charset_collate};";
    	}
    
    	/**
    	 * The code that runs during plugin activation.
    	 *
    	 * Update DB code to use InnoDB Engine instead of MyISAM.
    	 * Indexes updated
    	 *
    	 * @since    1.0.0
    	 * @version  2.5
    	 * @author   Saad
    	 */
    	public function activation() {
    		$db_version = get_option( 'uap_database_version', null );
    		if ( null !== $db_version && (string) AUTOMATOR_DATABASE_VERSION === (string) $db_version ) {
    			// bail. No db upgrade needed!
    			return;
    		}
    
    		do_action( 'automator_activation_before' );
    		self::create_tables();
    
    		do_action( 'automator_activation_after' );
    	}
    
    	/**
    	 * Create tables
    	 *
    	 * @since 3.0
    	 */
    	public static function create_tables() {
    		$sql = self::get_schema();
    
    		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    		dbDelta( $sql );
    		update_option( 'uap_database_version', AUTOMATOR_DATABASE_VERSION );
    	}
    
    	/**
    	 * Added this to fix MySQL 8 AUTO_INCREMENT issue
    	 * with already created tables
    	 *
    	 * @since  2.9
    	 * @author Saad S.
    	 */
    	public function mysql_8_auto_increment_fix() {
    		global $wpdb;
    
    		$wpdb->query( "ANALYZE TABLE `{$wpdb->prefix}uap_recipe_log`;" );
    		$wpdb->query( "ANALYZE TABLE `{$wpdb->prefix}uap_action_log`;" );
    		$wpdb->query( "ANALYZE TABLE `{$wpdb->prefix}uap_action_log_meta`;" );
    		$wpdb->query( "ANALYZE TABLE `{$wpdb->prefix}uap_closure_log`;" );
    		$wpdb->query( "ANALYZE TABLE `{$wpdb->prefix}uap_closure_log_meta`;" );
    		$wpdb->query( "ANALYZE TABLE `{$wpdb->prefix}uap_trigger_log`;" );
    		$wpdb->query( "ANALYZE TABLE `{$wpdb->prefix}uap_trigger_log_meta`;" );
    	}
    
    	/**
    	 * Call views instead of complex queries on log pages
    	 *
    	 * @version 2.5.1
    	 * @author  Saad
    	 */
    	public function automator_generate_views() {
    
    		do_action( 'automator_database_views_before' );
    
    		if ( AUTOMATOR_DATABASE_VIEWS_VERSION !== get_option( 'uap_database_views_version', 0 ) ) {
    			self::create_views();
    		}
    
    		do_action( 'automator_activation_views_after' );
    
    	}
    
    	/**
    	 * Generate VIEWS
    	 *
    	 * @since 3.0
    	 */
    	public static function create_views() {
    		global $wpdb;
    
    		$recipe_view       = "{$wpdb->prefix}uap_recipe_logs_view";
    		$recipe_view_query = self::recipe_log_view_query();
    		$wpdb->query( "CREATE OR REPLACE VIEW $recipe_view AS $recipe_view_query" ); //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    
    		$trigger_view       = "{$wpdb->prefix}uap_trigger_logs_view";
    		$trigger_view_query = self::trigger_log_view_query();
    
    		$wpdb->query( "CREATE OR REPLACE VIEW $trigger_view AS $trigger_view_query" ); //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    
    		$action_view       = "{$wpdb->prefix}uap_action_logs_view";
    		$action_view_query = self::action_log_view_query();
    
    		$wpdb->query( "CREATE OR REPLACE VIEW $action_view AS $action_view_query" ); //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    		update_option( 'uap_database_views_version', AUTOMATOR_DATABASE_VIEWS_VERSION );
    	}
    
    	/**
    	 * @return string
    	 */
    	public static function recipe_log_view_query() {
    		global $wpdb;
    
    		return apply_filters(
    			'automator_recipe_log_view_query',
    			"SELECT r.user_id,
    							r.date_time AS recipe_date_time,
    							r.completed AS recipe_completed,
    							r.run_number,
    							r.completed,
    							r.automator_recipe_id,
    							u.user_email,
    							u.display_name,
    							p.post_title AS recipe_title
    					FROM {$wpdb->prefix}uap_recipe_log r
    					LEFT JOIN {$wpdb->users} u
    					ON u.ID = r.user_id
    					JOIN {$wpdb->posts} p
    					ON p.ID = r.automator_recipe_id"
    		);
    	}
    
    	/**
    	 * @return string
    	 */
    	public static function trigger_log_view_query() {
    		global $wpdb;
    
    		return apply_filters(
    			'automator_trigger_log_view_query',
    			"SELECT u.ID AS user_id, u.user_email,
                                u.display_name,
                                t.automator_trigger_id,
                                t.date_time AS trigger_date,
                                t.completed AS trigger_completed,
                                t.automator_recipe_id,
                                t.ID,
                                pt.post_title AS trigger_title,
                                tm.meta_value AS trigger_sentence,
                                tm.run_number AS trigger_run_number,
                                tm.run_time AS trigger_run_time,
                                pm.meta_value AS trigger_total_times,
                                p.post_title AS recipe_title,
                                r.date_time AS recipe_date_time,
                                r.completed AS recipe_completed,
                                r.run_number AS recipe_run_number
                            FROM {$wpdb->prefix}uap_trigger_log t
                            LEFT JOIN {$wpdb->users} u
                            ON u.ID = t.user_id
                            LEFT JOIN {$wpdb->posts} p
                            ON p.ID = t.automator_recipe_id
                            LEFT JOIN {$wpdb->posts} pt
                            ON pt.ID = t.automator_trigger_id
                            LEFT JOIN {$wpdb->prefix}uap_trigger_log_meta tm
    						ON tm.automator_trigger_log_id = t.ID AND tm.meta_key = 'sentence_human_readable'
                            LEFT JOIN {$wpdb->prefix}uap_recipe_log r
                            ON t.automator_recipe_log_id = r.ID
                            LEFT JOIN {$wpdb->postmeta} pm
                            ON pm.post_id = t.automator_trigger_id AND pm.meta_key = 'NUMTIMES'"
    		);
    	}
    
    	/**
    	 * @param bool $group_by
    	 *
    	 * @return string
    	 */
    	public static function action_log_view_query( $group_by = true ) {
    		global $wpdb;
    		$qry = "SELECT a.automator_action_id,
    					a.date_time AS action_date,
    					a.completed AS action_completed,
    					a.error_message,
    					a.automator_recipe_id,
    					a.ID AS action_log_id,
    					a.automator_recipe_log_id AS recipe_log_id,
    					r.date_time AS recipe_date_time,
    					r.completed AS recipe_completed,
    					r.run_number AS recipe_run_number,
    					pa.post_title AS action_title,
    					am.meta_value AS action_sentence,
    					p.post_title AS recipe_title,
    					u.ID AS user_id,
    					u.user_email,
    					u.display_name
    			FROM {$wpdb->prefix}uap_action_log a
    			LEFT JOIN {$wpdb->prefix}uap_recipe_log r
    			ON a.automator_recipe_log_id = r.ID
    			LEFT JOIN {$wpdb->posts} p
    			ON p.ID = a.automator_recipe_id
    			JOIN {$wpdb->posts} pa
    			ON pa.ID = a.automator_action_id
    			LEFT JOIN {$wpdb->prefix}uap_action_log_meta am
    			ON a.automator_action_id = am.automator_action_id AND am.automator_action_log_id = a.ID AND am.user_id = a.user_id AND am.meta_key = 'sentence_human_readable_html'
    			LEFT JOIN {$wpdb->users} u
    			ON a.user_id = u.ID";
    		if ( $group_by ) {
    			$qry .= ' GROUP BY a.ID';
    		}
    
    		return apply_filters(
    			'automator_action_log_view_query',
    			$qry
    		);
    	}
    
    	/**
    	 * Check if specific VIEW is missing.
    	 *
    	 * @param string $type
    	 *
    	 * @return bool
    	 */
    	public static function is_view_exists( string $type = 'recipe' ) {
    		global $wpdb;
    		$recipe_view = '';
    		if ( 'recipe' === $type ) {
    			$recipe_view = "{$wpdb->prefix}uap_recipe_logs_view";
    		}
    		if ( 'trigger' === $type ) {
    			$recipe_view = "{$wpdb->prefix}uap_trigger_logs_view";
    		}
    		if ( 'action' === $type ) {
    			$recipe_view = "{$wpdb->prefix}uap_action_logs_view";
    		}
    
    		if ( empty( $recipe_view ) ) {
    			return false;
    		}
    		$results = self::all_views( true );
    		if ( ! in_array( $recipe_view, $results, true ) ) {
    			return true;
    		}
    
    		return false;
    	}
    
    	/**
    	 * Check if all Automator VIEWS exists. Return empty if all VIEWS exists else only the ones that are missing.
    	 *
    	 * @param bool $return_missing
    	 *
    	 * @return array
    	 * @version 3.0
    	 */
    	public static function all_views( bool $return_missing = false ) {
    		global $wpdb;
    		$db      = DB_NAME;
    		$results = $wpdb->get_results( "SHOW FULL TABLES IN $db WHERE TABLE_TYPE LIKE '%VIEW%'" ); //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    		$return  = array(
    			"{$wpdb->prefix}uap_recipe_logs_view",
    			"{$wpdb->prefix}uap_trigger_logs_view",
    			"{$wpdb->prefix}uap_action_logs_view",
    		);
    
    		if ( ! $results ) {
    			return $return_missing ? $return : array();
    		}
    		foreach ( $results as $r ) {
    			if ( ! is_object( $r ) ) {
    				continue;
    			}
    			foreach ( $r as $rr ) {
    				$return = array_diff( $return, array( $rr ) );
    			}
    		}
    
    		return $return;
    	}
    }
    

    Methods Methods