Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
100.00% covered (success)
100.00%
15 / 15
100.00% covered (success)
100.00%
6 / 6
 CRAP
100.00% covered (success)
100.00%
1 / 1
SQL
100.00% covered (success)
100.00%
15 / 15
100.00% covered (success)
100.00%
6 / 6
 14
100.00% covered (success)
100.00%
1 / 1
 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
n/a
0 / 0
n/a
0 / 0
 1
 systemTableList
n/a
0 / 0
n/a
0 / 0
 1
 viewList
n/a
0 / 0
n/a
0 / 0
 1
 triggerList
n/a
0 / 0
n/a
0 / 0
 1
 functionList
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
 1
 procedureList
n/a
0 / 0
n/a
0 / 0
 1
 sequenceList
n/a
0 / 0
n/a
0 / 0
 1
 columnList
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
 1
 typeList
n/a
0 / 0
n/a
0 / 0
 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%
4 / 4
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\Pgsql;
18
19use Query\Drivers\AbstractSQL;
20
21/**
22 * PostgreSQL specific SQL
23 */
24class SQL extends AbstractSQL
25{
26    /**
27     * Get the query plan for the sql query
28     */
29    public function explain(string $sql): string
30    {
31        return "EXPLAIN VERBOSE {$sql}";
32    }
33
34    /**
35     * Random ordering keyword
36     */
37    public function random(): string
38    {
39        return ' RANDOM()';
40    }
41
42    /**
43     * Returns sql to list other databases
44     */
45    public function dbList(): string
46    {
47        return <<<'SQL'
48            SELECT "datname" FROM "pg_database"
49            WHERE "datname" NOT IN ('template0','template1')
50            ORDER BY "datname" ASC
51SQL;
52    }
53
54    /**
55     * Returns sql to list tables
56     */
57    public function tableList(): string
58    {
59        return <<<'SQL'
60            SELECT "table_name"
61            FROM "information_schema"."tables"
62            WHERE "table_type" = 'BASE TABLE'
63            AND "table_schema" NOT IN
64                ('pg_catalog', 'information_schema');
65SQL;
66    }
67
68    /**
69     * Returns sql to list system tables
70     */
71    public function systemTableList(): string
72    {
73        return <<<'SQL'
74            SELECT "table_name"
75            FROM "information_schema"."tables"
76            WHERE "table_type" = 'BASE TABLE'
77            AND "table_schema" IN
78                ('pg_catalog', 'information_schema');
79SQL;
80    }
81
82    /**
83     * Returns sql to list views
84     */
85    public function viewList(): string
86    {
87        return <<<'SQL'
88             SELECT "viewname" FROM "pg_views"
89            WHERE "schemaname" NOT IN
90                ('pg_catalog', 'information_schema')
91            AND "viewname" !~ '^pg_'
92            ORDER BY "viewname" ASC
93SQL;
94    }
95
96    /**
97     * Returns sql to list triggers
98     */
99    public function triggerList(): string
100    {
101        return <<<'SQL'
102            SELECT *
103            FROM "information_schema"."triggers"
104            WHERE "trigger_schema" NOT IN
105                ('pg_catalog', 'information_schema')
106SQL;
107    }
108
109    /**
110     * Return sql to list functions
111     */
112    public function functionList(): ?string
113    {
114        return NULL;
115    }
116
117    /**
118     * Return sql to list stored procedures
119     */
120    public function procedureList(): string
121    {
122        return <<<'SQL'
123            SELECT "routine_name"
124            FROM "information_schema"."routines"
125            WHERE "specific_schema" NOT IN
126                ('pg_catalog', 'information_schema')
127            AND "type_udt_name" != 'trigger';
128SQL;
129    }
130
131    /**
132     * Return sql to list sequences
133     */
134    public function sequenceList(): string
135    {
136        return <<<'SQL'
137            SELECT "c"."relname"
138            FROM "pg_class" "c"
139            WHERE "c"."relkind" = 'S'
140            ORDER BY "relname" ASC
141SQL;
142    }
143
144    /**
145     * Return sql to list columns of the specified table
146     */
147    public function columnList(string $table): string
148    {
149        return <<<SQL
150            SELECT ordinal_position,
151                column_name,
152                data_type,
153                column_default,
154                is_nullable,
155                character_maximum_length,
156                numeric_precision
157            FROM information_schema.columns
158            WHERE table_name = '{$table}'
159            ORDER BY ordinal_position;
160SQL;
161    }
162
163    /**
164     * SQL to show list of field types
165     */
166    public function typeList(): string
167    {
168        return <<<'SQL'
169            SELECT "typname" FROM "pg_catalog"."pg_type"
170            WHERE "typname" !~ '^pg_|_'
171            AND "typtype" = 'b'
172            ORDER BY "typname"
173SQL;
174    }
175
176    /**
177     * Get the list of foreign keys for the current
178     * table
179     */
180    public function fkList(string $table): string
181    {
182        return <<<SQL
183            SELECT
184                "att2"."attname" AS "child_column",
185                "cl"."relname" AS "parent_table",
186                "att"."attname" AS "parent_column",
187                "con"."update" AS "update",
188                "con"."update" AS "delete"
189            FROM
190                (SELECT
191                    unnest(con1.conkey) AS "parent",
192                    unnest(con1.confkey) AS "child",
193                    "con1"."confrelid",
194                    "con1"."conrelid",
195                    "con1"."confupdtype" as "update",
196                    "con1"."confdeltype" as "delete"
197                FROM "pg_class" "cl"
198                JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid"
199                JOIN "pg_constraint" "con1" ON "con1"."conrelid" = "cl"."oid"
200                WHERE "cl"."relname" = '{$table}'
201                    AND "ns"."nspname" = 'public'
202                    AND "con1"."contype" = 'f'
203                )
204                "con"
205                JOIN "pg_attribute" "att" ON
206                    "att"."attrelid" = "con"."confrelid"
207                    AND "att"."attnum" = "con"."child"
208                JOIN "pg_class" "cl" ON
209                    "cl"."oid" = "con"."confrelid"
210                JOIN "pg_attribute" "att2" ON
211                    "att2"."attrelid" = "con"."conrelid"
212                    AND "att2"."attnum" = "con"."parent"
213SQL;
214    }
215
216    /**
217     * Get the list of indexes for the current table
218     */
219    public function indexList(string $table): string
220    {
221        return <<<SQL
222            SELECT
223                t.relname AS table_name,
224                i.relname AS index_name,
225                array_to_string(array_agg(a.attname), ', ') AS column_names
226            FROM
227                pg_class t,
228                pg_class i,
229                pg_index ix,
230                pg_attribute a
231            WHERE
232                t.oid = ix.indrelid
233                AND i.oid = ix.indexrelid
234                AND a.attrelid = t.oid
235                AND a.attnum = ANY(ix.indkey)
236                AND t.relkind = 'r'
237                AND t.relname = '{$table}'
238            GROUP BY
239                t.relname,
240                i.relname
241            ORDER BY
242                t.relname,
243                i.relname;
244SQL;
245    }
246}