SqlSchema::isMyisam() - Code Metrics - Inspection of "v5.12.0 release" - pryley/site-reviews - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 18d41c...f9af5f )

by Paul

created 2021-06-09 21:19 UTC

SqlSchema::isMyisam() A

↳ Parent: SqlSchema

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes

Metric	Value
eloc	1
c	0
b	0
f	0
dl	0
loc	3
ccs	0
cts	2
cp	0
rs	10
cc	1
nc	1
nop	1
crap	2

<?php

namespace GeminiLabs\SiteReviews\Database;

use GeminiLabs\SiteReviews\Database;
use GeminiLabs\SiteReviews\Helpers\Cast;
use GeminiLabs\SiteReviews\Helpers\Str;

class SqlSchema
{
    /**
     * @var array|null
     */
    protected $constraints;

    /**
     * @var \wpdb
     */
    protected $db;

    /**
     * @var array|null
     */
    protected $tables;

    public function __construct()
    {
        require_once ABSPATH.'wp-admin/includes/upgrade.php';
        global $wpdb;
        $this->db = $wpdb;
    }

    /**
     * @return void
     */
    public function addAssignedPostsForeignConstraints()
    {
        glsr(Database::class)->deleteInvalidPostAssignments();
        $this->addForeignConstraint(
            $table = $this->table('assigned_posts'),
            $constraint = $this->foreignConstraint('assigned_posts_rating_id'),
            $foreignKey = 'rating_id',
            $foreignTable = $this->table('ratings'), 
            $foreignColumn = 'ID'
        );
        $this->addForeignConstraint(
            $table = $this->table('assigned_posts'),
            $constraint = $this->foreignConstraint('assigned_posts_post_id'),
            $foreignKey = 'post_id',
            $foreignTable = $this->db->posts, 
            $foreignColumn = 'ID'
        );
    }

    /**
     * @return void
     */
    public function addAssignedTermsForeignConstraints()
    {
        glsr(Database::class)->deleteInvalidTermAssignments();
        $this->addForeignConstraint(
            $table = $this->table('assigned_terms'),
            $constraint = $this->foreignConstraint('assigned_terms_rating_id'),
            $foreignKey = 'rating_id',
            $foreignTable = $this->table('ratings'), 
            $foreignColumn = 'ID'
        );
        $this->addForeignConstraint(
            $table = $this->table('assigned_terms'),
            $constraint = $this->foreignConstraint('assigned_terms_term_id'),
            $foreignKey = 'term_id',
            $foreignTable = $this->db->terms, 
            $foreignColumn = 'term_id'
        );
    }

    /**
     * @return void
     */
    public function addAssignedUsersForeignConstraints()
    {
        glsr(Database::class)->deleteInvalidUserAssignments();
        $this->addForeignConstraint(
            $table = $this->table('assigned_users'),
            $constraint = $this->foreignConstraint('assigned_users_rating_id'),
            $foreignKey = 'rating_id',
            $foreignTable = $this->table('ratings'), 
            $foreignColumn = 'ID'
        );
        $this->addForeignConstraint(
            $table = $this->table('assigned_users'),
            $constraint = $this->foreignConstraint('assigned_users_user_id'),
            $foreignKey = 'user_id',
            $foreignTable = $this->db->users, 
            $foreignColumn = 'ID'
        );
    }

    /**
     * @return void
     */
    public function addReviewsForeignConstraints()
    {
        glsr(Database::class)->deleteInvalidReviews();
        $this->addForeignConstraint(
            $table = $this->table('ratings'),
            $constraint = $this->foreignConstraint('assigned_posts_review_id'),
            $foreignKey = 'review_id',
            $foreignTable = $this->db->posts, 
            $foreignColumn = 'ID'
        );
    }

    /**
     * This method expects the fully formed foreign constraint key
     * @param string $table
     * @param string $constraint
     * @param string $foreignKey
     * @param string $foreignTable
     * @param string $foreignColumn
     * @return int|bool
     * @see $this->foreignConstraint()
     */
    public function addForeignConstraint($table, $constraint, $foreignKey, $foreignTable, $foreignColumn)
    {
        if ($this->foreignConstraintExists($constraint, $foreignTable)) {
            return false;
        }
        $this->removeOrphanedRows($table, $foreignKey, $foreignTable, $foreignColumn);
        return glsr(Database::class)->dbQuery(glsr(Query::class)->sql("
            ALTER TABLE {$table}
            ADD CONSTRAINT {$constraint}
            FOREIGN KEY ({$foreignKey})
            REFERENCES {$foreignTable} ({$foreignColumn})
            ON DELETE CASCADE
        "));
    }

    /**
     * @return void
     */
    public function addForeignConstraints()
    {
        if (!defined('GLSR_UNIT_TESTS')) {
            $this->addAssignedPostsForeignConstraints();
            $this->addAssignedTermsForeignConstraints();
            $this->addAssignedUsersForeignConstraints();
            $this->addReviewsForeignConstraints();
        }
    }

    public function columnExists($table, $column)
    {
        $result = glsr(Database::class)->dbQuery(
            glsr(Query::class)->sql("SHOW COLUMNS FROM {$this->table($table)} LIKE '{$column}'")
        );
        return Cast::toBool($result);
    }

    /**
     * @param string $table
     * @return bool|int
     */
    public function convertTableEngine($table)
    {
        $result = -1;
        $table = $this->table($table);
        if ($this->isMyisam($table)) {
            $result = glsr(Database::class)->dbQuery(glsr(Query::class)->sql("
                ALTER TABLE {$this->db->dbname}.{$table} ENGINE = InnoDB;
            "));
        }
        if (true === $result) {
            update_option(glsr()->prefix.'engine_'.$table, 'innodb');
            $this->addForeignConstraints(); // apply InnoDB constraints
        }
        return $result;
    }

    /**
     * @return bool
     */
    public function createAssignedPostsTable()
    {
        if ($this->tableExists('assigned_posts')) {
            return false;
        }
        dbDelta(glsr(Query::class)->sql("
            CREATE TABLE {$this->table('assigned_posts')} (
                rating_id bigint(20) unsigned NOT NULL,
                post_id bigint(20) unsigned NOT NULL,
                is_published tinyint(1) NOT NULL DEFAULT '1',
                UNIQUE KEY {$this->prefix('assigned_posts_rating_id_post_id_unique')} (rating_id,post_id)
            ) ENGINE=InnoDB {$this->db->get_charset_collate()};
        "));
        glsr(Database::class)->logErrors();
        return true;
    }

    /**
     * @return bool
     */
    public function createAssignedTermsTable()
    {
        if ($this->tableExists('assigned_terms')) {
            return false;
        }
        dbDelta(glsr(Query::class)->sql("
            CREATE TABLE {$this->table('assigned_terms')} (
                rating_id bigint(20) unsigned NOT NULL,
                term_id bigint(20) unsigned NOT NULL,
                UNIQUE KEY {$this->prefix('assigned_terms_rating_id_term_id_unique')} (rating_id,term_id)
            ) ENGINE=InnoDB {$this->db->get_charset_collate()};
        "));
        glsr(Database::class)->logErrors();
        return true;
    }

    /**
     * @return bool
     */
    public function createAssignedUsersTable()
    {
        if ($this->tableExists('assigned_users')) {
            return false;
        }
        dbDelta(glsr(Query::class)->sql("
            CREATE TABLE {$this->table('assigned_users')} (
                rating_id bigint(20) unsigned NOT NULL,
                user_id bigint(20) unsigned NOT NULL,
                UNIQUE KEY {$this->prefix('assigned_users_rating_id_user_id_unique')} (rating_id,user_id)
            ) ENGINE=InnoDB {$this->db->get_charset_collate()};
        "));
        glsr(Database::class)->logErrors();
        return true;
    }

    /**
     * WordPress codex says there must be two spaces between PRIMARY KEY and the key definition.
     * @return bool
     * @see https://codex.wordpress.org/Creating_Tables_with_Plugins
     */
    public function createRatingTable()
    {
        if ($this->tableExists('ratings')) {
            return false;
        }
        dbDelta(glsr(Query::class)->sql("
            CREATE TABLE {$this->table('ratings')} (
                ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                review_id bigint(20) unsigned NOT NULL,
                rating int(11) NOT NULL DEFAULT '0',
                type varchar(20) DEFAULT 'local',
                is_approved tinyint(1) NOT NULL DEFAULT '0',
                is_pinned tinyint(1) NOT NULL DEFAULT '0',
                name varchar(250) DEFAULT NULL,
                email varchar(100) DEFAULT NULL,
                avatar varchar(200) DEFAULT NULL,
                ip_address varchar(100) DEFAULT NULL,
                url varchar(250) DEFAULT NULL,
                terms tinyint(1) NOT NULL DEFAULT '1',
                PRIMARY KEY  (ID),
                UNIQUE KEY {$this->prefix('ratings_review_id_unique')} (review_id),
                KEY {$this->prefix('ratings_rating_type_is_pinned_index')} (rating,type,is_pinned)
            ) ENGINE=InnoDB {$this->db->get_charset_collate()};
        "));
        glsr(Database::class)->logErrors();
        return true;
    }

    /**
     * @return void
     */
    public function createTables()
    {
        $this->createAssignedPostsTable();
        $this->createAssignedTermsTable();
        $this->createAssignedUsersTable();
        $this->createRatingTable();
    }

    /**
     * @param string $table
     * @param string $constraint
     * @return int|bool
     */
    public function dropForeignConstraint($table, $constraint)
    {
        $table = $this->table($table);
        $constraint = $this->foreignConstraint($constraint);
        if (!$this->foreignConstraintExists($constraint)) {
            return false;
        }
        return glsr(Database::class)->dbQuery(glsr(Query::class)->sql("
            ALTER TABLE {$table} DROP FOREIGN KEY {$constraint};
        "));
    }

    /**
     * @param string $constraint
     * @return string
     */
    public function foreignConstraint($constraint)
    {
        $constraint = Str::prefix($constraint, glsr()->prefix);
        $constraint = Str::suffix($constraint, '_foreign');
        if (is_multisite() && $this->db->blogid > 1) {
            return Str::suffix($constraint, '_'.$this->db->blogid);
        }
        return $constraint;
    }

    /**
     * This method expects the fully formed foreign constraint key
     * @param string $constraint
     * @param string $foreignTable
     * @return bool
     * @see $this->foreignConstraint()
     */
    public function foreignConstraintExists($constraint, $foreignTable = '')
    {
        if (!empty($foreignTable) && !$this->isInnodb($foreignTable)) {
            glsr_log()->debug("Cannot check for a foreign constraint because {$foreignTable} does not use the InnoDB engine.");
            return true; // we cannot create foreign contraints on MyISAM tables
        }
        // we don't need to cache this since it only runs on install
        if (!is_array($this->constraints)) {
            $this->constraints = $this->db->get_col("
                SELECT CONSTRAINT_NAME
                FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                WHERE CONSTRAINT_SCHEMA = '{$this->db->dbname}'
            ");
        }
        return in_array($constraint, $this->constraints);
    }

    /**
     * @param string $table
     * @return bool
     */
    public function isMyisam($table)
    {
        return 'myisam' === $this->tableEngine($table);
    }

    /**
     * @param string $table
     * @return bool
     */
    public function isInnodb($table)
    {
        return 'innodb' === $this->tableEngine($table);
    }

    /**
     * @param string $table
     * @param string $prefix
     * @return string
     */
    public function prefix($table, $prefix = '')
    {
        $table = Str::prefix($table, glsr()->prefix);
        return Str::prefix($table, $prefix);
    }

    /**
     * @param string $table
     * @param string $foreignKey
     * @param string $foreignTable
     * @param string $foreignColumn
     * @return int|bool
     */
    public function removeOrphanedRows($table, $foreignKey, $foreignTable, $foreignColumn)
    {
        // Remove all rows from the custom table where the referenced foreign table row does not exist
        return glsr(Database::class)->dbQuery(glsr(Query::class)->sql("
            DELETE t
            FROM {$this->table($table)} AS t
            LEFT JOIN {$this->table($foreignTable)} AS ft ON t.{$foreignKey} = ft.{$foreignColumn}
            WHERE ft.{$foreignColumn} IS NULL
        "));
    }

    /**
     * @param string $table
     * @return string
     */
    public function table($table)
    {
        if (in_array($table, $this->db->tables())) {
            return $table; // WordPress table is already prefixed
        }
        // do this next in case another plugin has created a similar table
        if (Str::endsWith(['ratings', 'assigned_posts', 'assigned_terms', 'assigned_users'], $table)) {
            $table = Str::removePrefix($table, $this->db->get_blog_prefix());
            $table = Str::removePrefix($table, glsr()->prefix);
            return $this->prefix($table, $this->db->get_blog_prefix());
        }
        if (array_key_exists($table, $this->db->tables())) {
            return $this->db->{$table}; // the prefixed WordPress table
        }
        glsr_log()->error("The {$table} table does not exist.");
        return $table; // @todo maybe throw an exception here instead...
    }

    /**
     * @param string $table
     * @return string (lowercased)
     */
    public function tableEngine($table)
    {
        $table = $this->table($table);
        $option = glsr()->prefix.'engine_'.$table;
        $engine = get_option($option);
        if (empty($engine)) {
            $engine = $this->db->get_var("
                SELECT ENGINE
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = '{$this->db->dbname}' AND TABLE_NAME = '{$table}'
            ");
            if (empty($engine)) {
                glsr_log()->warning(sprintf('DB Table Engine: The %s table does not exist in %s.', $table, $this->db->dbname));
                return '';
            }
            $engine = strtolower($engine);
            update_option($option, $engine);
        }
        return $engine;
    }

    /**
     * @param string $table
     * @return bool
     */
    public function tableExists($table)
    {
        if (!is_array($this->tables)) {
            $prefix = $this->db->get_blog_prefix().glsr()->prefix;
            $this->tables = $this->db->get_col(
                $this->db->prepare("SHOW TABLES LIKE %s", $this->db->esc_like($prefix).'%')
            );
        }
        return in_array($this->table($table), $this->tables);
    }

    /**
     * @param bool $removeDbPrefix
     * @return array
     */
    public function tableEngines($removeDbPrefix = false)
    {
        $results = $this->db->get_results("
            SELECT TABLE_NAME, ENGINE
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = '{$this->db->dbname}'
            AND TABLE_NAME IN ('{$this->db->options}','{$this->db->posts}','{$this->db->terms}','{$this->db->users}')
        ");
        $engines = [];
        foreach ($results as $result) {
            if (!array_key_exists($result->ENGINE, $engines)) {
                $engines[$result->ENGINE] = [];
            }
            if ($removeDbPrefix) {
                $result->TABLE_NAME = Str::removePrefix($result->TABLE_NAME, $this->db->get_blog_prefix());
            }
            $engines[$result->ENGINE][] = $result->TABLE_NAME;
        }
        return $engines;
    }

    /**
     * @return bool
     */
    public function tablesExist()
    {
        return $this->tableExists('assigned_posts')
            && $this->tableExists('assigned_terms')
            && $this->tableExists('assigned_users')
            && $this->tableExists('ratings');
    }
}


1		<?php
2
3		namespace GeminiLabs\SiteReviews\Database;
4
5		use GeminiLabs\SiteReviews\Database;
6		use GeminiLabs\SiteReviews\Helpers\Cast;
7		use GeminiLabs\SiteReviews\Helpers\Str;
8
9		class SqlSchema
10		{
11		/**
12		* @var array\|null
13		*/
14		protected $constraints;
15
16		/**
17		* @var \wpdb
18		*/
19		protected $db;
20
21		/**
22		* @var array\|null
23		*/
24		protected $tables;
25
26	23	public function __construct()
27		{
28	23	require_once ABSPATH.'wp-admin/includes/upgrade.php';
29	23	global $wpdb;
30	23	$this->db = $wpdb;
31	23	}
32
33		/**
34		* @return void
35		*/
36		public function addAssignedPostsForeignConstraints()
37		{
38		glsr(Database::class)->deleteInvalidPostAssignments();
39		$this->addForeignConstraint(
40		$table = $this->table('assigned_posts'),
41		$constraint = $this->foreignConstraint('assigned_posts_rating_id'),
42		$foreignKey = 'rating_id',
43		$foreignTable = $this->table('ratings'),
44		$foreignColumn = 'ID'
45		);
46		$this->addForeignConstraint(
47		$table = $this->table('assigned_posts'),
48		$constraint = $this->foreignConstraint('assigned_posts_post_id'),
49		$foreignKey = 'post_id',
50		$foreignTable = $this->db->posts,
51		$foreignColumn = 'ID'
52		);
53		}
54
55		/**
56		* @return void
57		*/
58		public function addAssignedTermsForeignConstraints()
59		{
60		glsr(Database::class)->deleteInvalidTermAssignments();
61		$this->addForeignConstraint(
62		$table = $this->table('assigned_terms'),
63		$constraint = $this->foreignConstraint('assigned_terms_rating_id'),
64		$foreignKey = 'rating_id',
65		$foreignTable = $this->table('ratings'),
66		$foreignColumn = 'ID'
67		);
68		$this->addForeignConstraint(
69		$table = $this->table('assigned_terms'),
70		$constraint = $this->foreignConstraint('assigned_terms_term_id'),
71		$foreignKey = 'term_id',
72		$foreignTable = $this->db->terms,
73		$foreignColumn = 'term_id'
74		);
75		}
76
77		/**
78		* @return void
79		*/
80		public function addAssignedUsersForeignConstraints()
81		{
82		glsr(Database::class)->deleteInvalidUserAssignments();
83		$this->addForeignConstraint(
84		$table = $this->table('assigned_users'),
85		$constraint = $this->foreignConstraint('assigned_users_rating_id'),
86		$foreignKey = 'rating_id',
87		$foreignTable = $this->table('ratings'),
88		$foreignColumn = 'ID'
89		);
90		$this->addForeignConstraint(
91		$table = $this->table('assigned_users'),
92		$constraint = $this->foreignConstraint('assigned_users_user_id'),
93		$foreignKey = 'user_id',
94		$foreignTable = $this->db->users,
95		$foreignColumn = 'ID'
96		);
97		}
98
99		/**
100		* @return void
101		*/
102		public function addReviewsForeignConstraints()
103		{
104		glsr(Database::class)->deleteInvalidReviews();
105		$this->addForeignConstraint(
106		$table = $this->table('ratings'),
107		$constraint = $this->foreignConstraint('assigned_posts_review_id'),
108		$foreignKey = 'review_id',
109		$foreignTable = $this->db->posts,
110		$foreignColumn = 'ID'
111		);
112		}
113
114		/**
115		* This method expects the fully formed foreign constraint key
116		* @param string $table
117		* @param string $constraint
118		* @param string $foreignKey
119		* @param string $foreignTable
120		* @param string $foreignColumn
121		* @return int\|bool
122		* @see $this->foreignConstraint()
123		*/
124		public function addForeignConstraint($table, $constraint, $foreignKey, $foreignTable, $foreignColumn)
125		{
126		if ($this->foreignConstraintExists($constraint, $foreignTable)) {
127		return false;
128		}
129		$this->removeOrphanedRows($table, $foreignKey, $foreignTable, $foreignColumn);
130		return glsr(Database::class)->dbQuery(glsr(Query::class)->sql("
131		ALTER TABLE {$table}
132		ADD CONSTRAINT {$constraint}
133		FOREIGN KEY ({$foreignKey})
134		REFERENCES {$foreignTable} ({$foreignColumn})
135		ON DELETE CASCADE
136		"));
137		}
138
139		/**
140		* @return void
141		*/
142	14	public function addForeignConstraints()
143		{
144	14	if (!defined('GLSR_UNIT_TESTS')) {
145		$this->addAssignedPostsForeignConstraints();
146		$this->addAssignedTermsForeignConstraints();
147		$this->addAssignedUsersForeignConstraints();
148		$this->addReviewsForeignConstraints();
149		}
150	14	}
151
152	14	public function columnExists($table, $column)
153		{
154	14	$result = glsr(Database::class)->dbQuery(
155	14	glsr(Query::class)->sql("SHOW COLUMNS FROM {$this->table($table)} LIKE '{$column}'")
156		);
157	14	return Cast::toBool($result);
158		}
159
160		/**
161		* @param string $table
162		* @return bool\|int
163		*/
164		public function convertTableEngine($table)
165		{
166		$result = -1;
167		$table = $this->table($table);
168		if ($this->isMyisam($table)) {
169		$result = glsr(Database::class)->dbQuery(glsr(Query::class)->sql("
170		ALTER TABLE {$this->db->dbname}.{$table} ENGINE = InnoDB;
171		"));
172		}
173		if (true === $result) {
174		update_option(glsr()->prefix.'engine_'.$table, 'innodb');
175		$this->addForeignConstraints(); // apply InnoDB constraints
176		}
177		return $result;
178		}
179
180		/**
181		* @return bool
182		*/
183	14	public function createAssignedPostsTable()
184		{
185	14	if ($this->tableExists('assigned_posts')) {
186	14	return false;
187		}
188		dbDelta(glsr(Query::class)->sql("
189		CREATE TABLE {$this->table('assigned_posts')} (
190		rating_id bigint(20) unsigned NOT NULL,
191		post_id bigint(20) unsigned NOT NULL,
192		is_published tinyint(1) NOT NULL DEFAULT '1',
193		UNIQUE KEY {$this->prefix('assigned_posts_rating_id_post_id_unique')} (rating_id,post_id)
194		) ENGINE=InnoDB {$this->db->get_charset_collate()};
195		"));
196		glsr(Database::class)->logErrors();
197		return true;
198		}
199
200		/**
201		* @return bool
202		*/
203	14	public function createAssignedTermsTable()
204		{
205	14	if ($this->tableExists('assigned_terms')) {
206	14	return false;
207		}
208		dbDelta(glsr(Query::class)->sql("
209		CREATE TABLE {$this->table('assigned_terms')} (
210		rating_id bigint(20) unsigned NOT NULL,
211		term_id bigint(20) unsigned NOT NULL,
212		UNIQUE KEY {$this->prefix('assigned_terms_rating_id_term_id_unique')} (rating_id,term_id)
213		) ENGINE=InnoDB {$this->db->get_charset_collate()};
214		"));
215		glsr(Database::class)->logErrors();
216		return true;
217		}
218
219		/**
220		* @return bool
221		*/
222	14	public function createAssignedUsersTable()
223		{
224	14	if ($this->tableExists('assigned_users')) {
225	14	return false;
226		}
227		dbDelta(glsr(Query::class)->sql("
228		CREATE TABLE {$this->table('assigned_users')} (
229		rating_id bigint(20) unsigned NOT NULL,
230		user_id bigint(20) unsigned NOT NULL,
231		UNIQUE KEY {$this->prefix('assigned_users_rating_id_user_id_unique')} (rating_id,user_id)
232		) ENGINE=InnoDB {$this->db->get_charset_collate()};
233		"));
234		glsr(Database::class)->logErrors();
235		return true;
236		}
237
238		/**
239		* WordPress codex says there must be two spaces between PRIMARY KEY and the key definition.
240		* @return bool
241		* @see https://codex.wordpress.org/Creating_Tables_with_Plugins
242		*/
243	14	public function createRatingTable()
244		{
245	14	if ($this->tableExists('ratings')) {
246	14	return false;
247		}
248		dbDelta(glsr(Query::class)->sql("
249		CREATE TABLE {$this->table('ratings')} (
250		ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
251		review_id bigint(20) unsigned NOT NULL,
252		rating int(11) NOT NULL DEFAULT '0',
253		type varchar(20) DEFAULT 'local',
254		is_approved tinyint(1) NOT NULL DEFAULT '0',
255		is_pinned tinyint(1) NOT NULL DEFAULT '0',
256		name varchar(250) DEFAULT NULL,
257		email varchar(100) DEFAULT NULL,
258		avatar varchar(200) DEFAULT NULL,
259		ip_address varchar(100) DEFAULT NULL,
260		url varchar(250) DEFAULT NULL,
261		terms tinyint(1) NOT NULL DEFAULT '1',
262		PRIMARY KEY (ID),
263		UNIQUE KEY {$this->prefix('ratings_review_id_unique')} (review_id),
264		KEY {$this->prefix('ratings_rating_type_is_pinned_index')} (rating,type,is_pinned)
265		) ENGINE=InnoDB {$this->db->get_charset_collate()};
266		"));
267		glsr(Database::class)->logErrors();
268		return true;
269		}
270
271		/**
272		* @return void
273		*/
274	14	public function createTables()
275		{
276	14	$this->createAssignedPostsTable();
277	14	$this->createAssignedTermsTable();
278	14	$this->createAssignedUsersTable();
279	14	$this->createRatingTable();
280	14	}
281
282		/**
283		* @param string $table
284		* @param string $constraint
285		* @return int\|bool
286		*/
287		public function dropForeignConstraint($table, $constraint)
288		{
289		$table = $this->table($table);
290		$constraint = $this->foreignConstraint($constraint);
291		if (!$this->foreignConstraintExists($constraint)) {
292		return false;
293		}
294		return glsr(Database::class)->dbQuery(glsr(Query::class)->sql("
295		ALTER TABLE {$table} DROP FOREIGN KEY {$constraint};
296		"));
297		}
298
299		/**
300		* @param string $constraint
301		* @return string
302		*/
303		public function foreignConstraint($constraint)
304		{
305		$constraint = Str::prefix($constraint, glsr()->prefix);
306		$constraint = Str::suffix($constraint, '_foreign');
307		if (is_multisite() && $this->db->blogid > 1) {
308		return Str::suffix($constraint, '_'.$this->db->blogid);
309		}
310		return $constraint;
311		}
312
313		/**
314		* This method expects the fully formed foreign constraint key
315		* @param string $constraint
316		* @param string $foreignTable
317		* @return bool
318		* @see $this->foreignConstraint()
319		*/
320		public function foreignConstraintExists($constraint, $foreignTable = '')
321		{
322		if (!empty($foreignTable) && !$this->isInnodb($foreignTable)) {
323		glsr_log()->debug("Cannot check for a foreign constraint because {$foreignTable} does not use the InnoDB engine.");
324		return true; // we cannot create foreign contraints on MyISAM tables
325		}
326		// we don't need to cache this since it only runs on install
327		if (!is_array($this->constraints)) {
328		$this->constraints = $this->db->get_col("
329		SELECT CONSTRAINT_NAME
330		FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
331		WHERE CONSTRAINT_SCHEMA = '{$this->db->dbname}'
332		");
333		}
334		return in_array($constraint, $this->constraints);
335		}
336
337		/**
338		* @param string $table
339		* @return bool
340		*/
341		public function isMyisam($table)
342		{
343		return 'myisam' === $this->tableEngine($table);
344		}
345
346		/**
347		* @param string $table
348		* @return bool
349		*/
350	14	public function isInnodb($table)
351		{
352	14	return 'innodb' === $this->tableEngine($table);
353		}
354
355		/**
356		* @param string $table
357		* @param string $prefix
358		* @return string
359		*/
360	23	public function prefix($table, $prefix = '')
361		{
362	23	$table = Str::prefix($table, glsr()->prefix);
363	23	return Str::prefix($table, $prefix);
364		}
365
366		/**
367		* @param string $table
368		* @param string $foreignKey
369		* @param string $foreignTable
370		* @param string $foreignColumn
371		* @return int\|bool
372		*/
373		public function removeOrphanedRows($table, $foreignKey, $foreignTable, $foreignColumn)
374		{
375		// Remove all rows from the custom table where the referenced foreign table row does not exist
376		return glsr(Database::class)->dbQuery(glsr(Query::class)->sql("
377		DELETE t
378		FROM {$this->table($table)} AS t
379		LEFT JOIN {$this->table($foreignTable)} AS ft ON t.{$foreignKey} = ft.{$foreignColumn}
380		WHERE ft.{$foreignColumn} IS NULL
381		"));
382		}
383
384		/**
385		* @param string $table
386		* @return string
387		*/
388	23	public function table($table)
389		{
390	23	if (in_array($table, $this->db->tables())) {
391		return $table; // WordPress table is already prefixed
392		}
393		// do this next in case another plugin has created a similar table
394	23	if (Str::endsWith(['ratings', 'assigned_posts', 'assigned_terms', 'assigned_users'], $table)) {
395	23	$table = Str::removePrefix($table, $this->db->get_blog_prefix());
396	23	$table = Str::removePrefix($table, glsr()->prefix);
397	23	return $this->prefix($table, $this->db->get_blog_prefix());
398		}
399	14	if (array_key_exists($table, $this->db->tables())) {
400	14	return $this->db->{$table}; // the prefixed WordPress table
401		}
402		glsr_log()->error("The {$table} table does not exist.");
403		return $table; // @todo maybe throw an exception here instead...
404		}
405
406		/**
407		* @param string $table
408		* @return string (lowercased)
409		*/
410	14	public function tableEngine($table)
411		{
412	14	$table = $this->table($table);
413	14	$option = glsr()->prefix.'engine_'.$table;
414	14	$engine = get_option($option);
415	14	if (empty($engine)) {
416	1	$engine = $this->db->get_var("
417		SELECT ENGINE
418		FROM INFORMATION_SCHEMA.TABLES
419	1	WHERE TABLE_SCHEMA = '{$this->db->dbname}' AND TABLE_NAME = '{$table}'
420		");
421	1	if (empty($engine)) {
422		glsr_log()->warning(sprintf('DB Table Engine: The %s table does not exist in %s.', $table, $this->db->dbname));
423		return '';
424		}
425	1	$engine = strtolower($engine);
426	1	update_option($option, $engine);
427		}
428	14	return $engine;
429		}
430
431		/**
432		* @param string $table
433		* @return bool
434		*/
435	14	public function tableExists($table)
436		{
437	14	if (!is_array($this->tables)) {
438	14	$prefix = $this->db->get_blog_prefix().glsr()->prefix;
439	14	$this->tables = $this->db->get_col(
440	14	$this->db->prepare("SHOW TABLES LIKE %s", $this->db->esc_like($prefix).'%')
441		);
442		}
443	14	return in_array($this->table($table), $this->tables);
444		}
445
446		/**
447		* @param bool $removeDbPrefix
448		* @return array
449		*/
450		public function tableEngines($removeDbPrefix = false)
451		{
452		$results = $this->db->get_results("
453		SELECT TABLE_NAME, ENGINE
454		FROM INFORMATION_SCHEMA.TABLES
455		WHERE TABLE_SCHEMA = '{$this->db->dbname}'
456		AND TABLE_NAME IN ('{$this->db->options}','{$this->db->posts}','{$this->db->terms}','{$this->db->users}')
457		");
458		$engines = [];
459		foreach ($results as $result) {
460		if (!array_key_exists($result->ENGINE, $engines)) {
461		$engines[$result->ENGINE] = [];
462		}
463		if ($removeDbPrefix) {
464		$result->TABLE_NAME = Str::removePrefix($result->TABLE_NAME, $this->db->get_blog_prefix());
465		}
466		$engines[$result->ENGINE][] = $result->TABLE_NAME;
467		}
468		return $engines;
469		}
470
471		/**
472		* @return bool
473		*/
474	14	public function tablesExist()
475		{
476	14	return $this->tableExists('assigned_posts')
477	14	&& $this->tableExists('assigned_terms')
478	14	&& $this->tableExists('assigned_users')
479	14	&& $this->tableExists('ratings');
480		}
481		}
482

pryley / site-reviews

Push — master ( 18d41c...f9af5f )

SqlSchema::isMyisam() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like