Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
80.00% covered (warning)
80.00%
12 / 15
90.00% covered (success)
90.00%
9 / 10
 CRAP
0.00% covered (danger)
0.00%
0 / 1
SQL
80.00% covered (warning)
80.00%
12 / 15
90.00% covered (success)
90.00%
9 / 10
 19.31
0.00% covered (danger)
0.00%
0 / 1
 limit
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
 6
 explain
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 1
 random
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 1
 dbList
n/a
0 / 0
n/a
0 / 0
 1
 tableList
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 2
 systemTableList
n/a
0 / 0
n/a
0 / 0
 1
 viewList
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 1
 triggerList
n/a
0 / 0
n/a
0 / 0
 1
 functionList
n/a
0 / 0
n/a
0 / 0
 1
 procedureList
n/a
0 / 0
n/a
0 / 0
 1
 sequenceList
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 1
 typeList
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 1
 columnList
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 1
 fkList
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
 1
 indexList
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 1
1<?php declare(strict_types=1);
2/**
3 * Query
4 *
5 * SQL Query Builder / Database Abstraction Layer
6 *
7 * PHP version 8.1
8 *
9 * @package     Query
10 * @author      Timothy J. Warren <tim@timshome.page>
11 * @copyright   2012 - 2023 Timothy J. Warren
12 * @license     http://www.opensource.org/licenses/mit-license.html  MIT License
13 * @link        https://git.timshomepage.net/aviat/Query
14 * @version     4.0.0
15 */
16
17namespace Query\Drivers\Mysql;
18
19use Query\Drivers\AbstractSQL;
20
21/**
22 * MySQL specific SQL
23 */
24class SQL extends AbstractSQL
25{
26    /**
27     * Limit clause
28     */
29    public function limit(string $sql, int $limit, ?int $offset=NULL): string
30    {
31        if ( ! is_numeric($offset))
32        {
33            return $sql . " LIMIT {$limit}";
34        }
35
36        return $sql . " LIMIT {$offset}, {$limit}";
37    }
38
39    /**
40     * Get the query plan for the sql query
41     */
42    public function explain(string $sql): string
43    {
44        return "EXPLAIN EXTENDED {$sql}";
45    }
46
47    /**
48     * Random ordering keyword
49     */
50    public function random(): string
51    {
52        return ' RAND() DESC';
53    }
54
55    /**
56     * Returns sql to list other databases
57     */
58    public function dbList(): string
59    {
60        return <<<'SQL'
61            SHOW DATABASES WHERE `Database` NOT IN ('information_schema','mysql')
62SQL;
63    }
64
65    /**
66     * Returns sql to list tables
67     */
68    public function tableList(string $database=''): string
69    {
70        // @codeCoverageIgnoreStart
71        if ( ! empty($database))
72        {
73            return "SHOW TABLES FROM `{$database}`";
74        }
75        // @codeCoverageIgnoreEnd
76
77        return 'SHOW TABLES';
78    }
79
80    /**
81     * Overridden in MySQL class
82     */
83    public function systemTableList(): string
84    {
85        return <<<'SQL'
86            SELECT `TABLE_NAME` FROM `information_schema`.`TABLES`
87            WHERE `TABLE_SCHEMA`='information_schema'
88SQL;
89    }
90
91    /**
92     * Returns sql to list views
93     */
94    public function viewList(): string
95    {
96        return 'SELECT `table_name` FROM `information_schema`.`views`';
97    }
98
99    /**
100     * Returns sql to list triggers
101     *
102     * @codeCoverageIgnore
103     */
104    public function triggerList(): string
105    {
106        return 'SHOW TRIGGERS';
107    }
108
109    /**
110     * Return sql to list functions
111     *
112     * @codeCoverageIgnore
113     */
114    public function functionList(): string
115    {
116        return 'SHOW FUNCTION STATUS';
117    }
118
119    /**
120     * Return sql to list stored procedures
121     *
122     * @codeCoverageIgnore
123     */
124    public function procedureList(): string
125    {
126        return 'SHOW PROCEDURE STATUS';
127    }
128
129    /**
130     * Return sql to list sequences
131     */
132    public function sequenceList(): ?string
133    {
134        return NULL;
135    }
136
137    /**
138     * SQL to show list of field types
139     */
140    public function typeList(): string
141    {
142        return 'SELECT DISTINCT `DATA_TYPE` FROM `information_schema`.`COLUMNS`';
143    }
144
145    /**
146     * SQL to show information about columns in a table
147     */
148    public function columnList(string $table): string
149    {
150        return "SHOW FULL COLUMNS FROM {$table}";
151    }
152
153    /**
154     * Get the list of foreign keys for the current
155     * table
156     */
157    public function fkList(string $table): string
158    {
159        return <<<SQL
160            SELECT DISTINCT
161                `kcu`.`COLUMN_NAME` as `child_column`,
162                `kcu`.`REFERENCED_TABLE_NAME` as `parent_table`,
163                `kcu`.`REFERENCED_COLUMN_NAME` as `parent_column`,
164                `rc`.`UPDATE_RULE` AS `update`,
165                `rc`.`DELETE_RULE` AS `delete`
166            FROM `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` `tc`
167            INNER JOIN `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
168                ON `kcu`.`CONSTRAINT_NAME`=`tc`.`CONSTRAINT_NAME`
169            INNER JOIN `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS` `rc`
170                ON `rc`.`CONSTRAINT_NAME`=`tc`.`CONSTRAINT_NAME`
171            WHERE `tc`.`CONSTRAINT_TYPE`='FOREIGN KEY'
172            AND `tc`.`TABLE_NAME`='{$table}'
173SQL;
174    }
175
176    /**
177     * Get the list of indexes for the current table
178     */
179    public function indexList(string $table): string
180    {
181        return "SHOW INDEX IN {$table}";
182    }
183}