db

1
<?php
2

3
namespace Upside\Db;
4

5
use Upside\Db\Schema\AlterTable;
6
use Upside\Db\Schema\CreateTable;
7

8
class Schema
9
{
10
    protected Connection $connection;
11

12
    /** Table list. */
13
    protected ?array $table_list = null;
14

15
    /** Currently used database name. */
16
    protected ?string $current_database = null;
17

18
    /** Column list */
19
    protected array $columns = [];
20

21
    public function __construct(Connection $connection)
22
    {
23
        $this->connection = $connection;
24
    }
25

26
    /**
27
     * Get the name of the currently used database
28
     *
29
     * @throws \Exception
30
     */
31
    public function get_current_database(): string
32
    {
33
        if ($this->current_database === null) {
34
            $compiler = $this->connection->schema_compiler();
35
            $result = $compiler->current_database($this->connection->get_dsn());
36

37
            if (isset($result['result'])) {
38
                $this->current_database = $result['result'];
39
            } else {
40
                $this->current_database = $this->connection->column($result['sql'], $result['params']);
41
            }
42
        }
43

44
        return $this->current_database;
45
    }
46

47
    /**
48
     * Check if the specified table exists
49
     *
50
     * @param string $table Table name
51
     * @param boolean $clear (optional) Refresh table list
52
     *
53
     * @throws \Exception
54
     */
55
    public function has_table(string $table, bool $clear = false): bool
56
    {
57
        $list = $this->get_tables($clear);
58

59
        return isset($list[\strtolower($table)]);
60
    }
61

62
    /**
63
     * Get a list with all tables that belong to the currently used database
64
     *
65
     * @param boolean $clear (optional) Refresh table list
66
     *
67
     * @return  string[]
68
     * @throws \Exception
69
     */
70
    public function get_tables(bool $clear = false): array
71
    {
72
        if ($clear) {
73
            $this->table_list = null;
74
        }
75

76
        if ($this->table_list === null) {
77
            $compiler = $this->connection->schema_compiler();
78

79
            $database = $this->get_current_database();
80

81
            $sql = $compiler->get_tables($database);
82

83
            $results = $this->connection
84
                ->query($sql['sql'], $sql['params'])
85
                ->fetch_num()
86
                ->all();
87

88
            $this->table_list = [];
89

90
            foreach ($results as $result) {
91
                $this->table_list[\strtolower($result[0])] = $result[0];
92
            }
93
        }
94

95
        return $this->table_list;
96
    }
97

98
    /**
99
     * Get a list with all columns that belong to the specified table
100
     *
101
     * @param string $table
102
     * @param boolean $clear (optional) Refresh column list
103
     * @param boolean $names (optional) Return only the column names
104
     *
105
     * @return false|string[]
106
     * @throws \Exception
107
     */
108
    public function get_columns(string $table, bool $clear = false, bool $names = true): array|false
109
    {
110
        if ($clear) {
111
            unset($this->columns[$table]);
112
        }
113

114
        if (!$this->has_table($table, $clear)) {
115
            return false;
116
        }
117

118
        if (!isset($this->columns[$table])) {
119
            $compiler = $this->connection->schema_compiler();
120

121
            $database = $this->get_current_database();
122

123
            $sql = $compiler->get_columns($database, $table);
124

125
            $results = $this->connection
126
                ->query($sql['sql'], $sql['params'])
127
                ->fetch_assoc()
128
                ->all();
129

130
            $columns = [];
131

132
            foreach ($results as $ord => &$col) {
133
                $columns[$col['name']] = [
134
                    'name' => $col['name'],
135
                    'type' => $col['type'],
136
                ];
137
            }
138

139
            $this->columns[$table] = $columns;
140
        }
141

142
        return $names ? \array_keys($this->columns[$table]) : $this->columns[$table];
143
    }
144

145
    /**
146
     * Creates a new table
147
     *
148
     * @param string $table Table name
149
     * @param callable $callback A callback that will define table's fields and indexes
150
     * @throws \Exception
151
     */
152
    public function create(string $table, callable $callback)
153
    {
154
        $compiler = $this->connection->schema_compiler();
155

156
        $schema = new CreateTable($table);
157

158
        $callback($schema);
159

160
        foreach ($compiler->create($schema) as $result) {
161
            $this->connection->command($result['sql'], $result['params']);
162
        }
163

164
        //clear table list
165
        $this->table_list = null;
166
    }
167

168
    /**
169
     * Alters a table's definition
170
     *
171
     * @param string $table Table name
172
     * @param callable $callback A callback that will add or remove fields or indexes
173
     * @throws \Exception
174
     */
175
    public function alter(string $table, callable $callback)
176
    {
177
        $compiler = $this->connection->schema_compiler();
178

179
        $schema = new AlterTable($table);
180

181
        $callback($schema);
182

183
        unset($this->columns[\strtolower($table)]);
184

185
        foreach ($compiler->alter($schema) as $result) {
186
            $this->connection->command($result['sql'], $result['params']);
187
        }
188
    }
189

190
    /**
191
     * Change a table's name
192
     *
193
     * @param string $table The table
194
     * @param string $name The new name of the table
195
     * @throws \Exception
196
     */
197
    public function rename_table(string $table, string $name)
198
    {
199
        $result = $this->connection->schema_compiler()->rename_table($table, $name);
200
        $this->connection->command($result['sql'], $result['params']);
201
        $this->table_list = null;
202
        unset($this->columns[\strtolower($table)]);
203
    }
204

205
    /**
206
     * Deletes a table
207
     */
208
    public function drop(string $table)
209
    {
210
        $compiler = $this->connection->schema_compiler();
211

212
        $result = $compiler->drop($table);
213

214
        $this->connection->command($result['sql'], $result['params']);
215

216
        //clear table list
217
        $this->table_list = null;
218
        unset($this->columns[strtolower($table)]);
219
    }
220

221
    /**
222
     * Deletes all records from a table
223
     */
224
    public function truncate(string $table)
225
    {
226
        $compiler = $this->connection->schema_compiler();
227

228
        $result = $compiler->truncate($table);
229

230
        $this->connection->command($result['sql'], $result['params']);
231
    }
232
}
233