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

Passed

Push — master ( 2da942...d633d7 )

by Paul

created 2021-04-25 02:43 UTC

SqlSchema::removeOrphanedRows() A

↳ Parent: SqlSchema

Complexity

Conditions	1
Paths	1

Size

Total Lines	8
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes

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

pryley / site-reviews

Push — master ( 2da942...d633d7 )

SqlSchema::removeOrphanedRows() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like