-
-
Notifications
You must be signed in to change notification settings - Fork 104
/
MsSqlDriver.php
205 lines (165 loc) 路 5.28 KB
/
MsSqlDriver.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
<?php
/**
* This file is part of the Nette Framework (https://nette.org)
* Copyright (c) 2004 David Grudl (https://davidgrudl.com)
*/
declare(strict_types=1);
namespace Nette\Database\Drivers;
use Nette;
/**
* Supplemental MS SQL database driver.
*/
class MsSqlDriver extends PdoDriver
{
public function delimite(string $name): string
{
// @see https://msdn.microsoft.com/en-us/library/ms176027.aspx
return '[' . str_replace(['[', ']'], ['[[', ']]'], $name) . ']';
}
public function formatDateTime(\DateTimeInterface $value): string
{
return $value->format("'Y-m-d H:i:s'");
}
public function formatDateInterval(\DateInterval $value): string
{
throw new Nette\NotSupportedException;
}
public function formatLike(string $value, int $pos): string
{
$value = strtr($value, ["'" => "''", '%' => '[%]', '_' => '[_]', '[' => '[[]']);
return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
}
public function applyLimit(string &$sql, ?int $limit, ?int $offset): void
{
if ($offset) {
throw new Nette\NotSupportedException('Offset is not supported by this database.');
} elseif ($limit < 0) {
throw new Nette\InvalidArgumentException('Negative offset or limit.');
} elseif ($limit !== null) {
$sql = preg_replace('#^\s*(SELECT(\s+DISTINCT|\s+ALL)?|UPDATE|DELETE)#i', '$0 TOP ' . $limit, $sql, 1, $count);
if (!$count) {
throw new Nette\InvalidArgumentException('SQL query must begin with SELECT, UPDATE or DELETE command.');
}
}
}
/********************* reflection ****************d*g**/
public function getTables(): array
{
$tables = [];
foreach ($this->pdo->query('SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES') as $row) {
$tables[] = [
'name' => $row['TABLE_SCHEMA'] . '.' . $row['TABLE_NAME'],
'view' => ($row['TABLE_TYPE'] ?? null) === 'VIEW',
];
}
return $tables;
}
public function getColumns(string $table): array
{
[$table_schema, $table_name] = explode('.', $table);
$columns = [];
$query = <<<X
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
IS_NULLABLE,
COLUMN_DEFAULT,
DOMAIN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = {$this->pdo->quote($table_schema)}
AND TABLE_NAME = {$this->pdo->quote($table_name)}
X;
foreach ($this->pdo->query($query, \PDO::FETCH_ASSOC) as $row) {
$columns[] = [
'name' => $row['COLUMN_NAME'],
'table' => $table,
'nativetype' => $row['DATA_TYPE'],
'size' => $row['CHARACTER_MAXIMUM_LENGTH'] ?? ($row['NUMERIC_PRECISION'] ?? null),
'unsigned' => false,
'nullable' => $row['IS_NULLABLE'] === 'YES',
'default' => $row['COLUMN_DEFAULT'],
'autoincrement' => $row['DOMAIN_NAME'] === 'COUNTER',
'primary' => $row['COLUMN_NAME'] === 'ID',
'vendor' => $row,
];
}
return $columns;
}
public function getIndexes(string $table): array
{
[, $table_name] = explode('.', $table);
$indexes = [];
$query = <<<X
SELECT
name_index = ind.name,
id_column = ic.index_column_id,
name_column = col.name,
ind.is_unique,
ind.is_primary_key
FROM
sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id
WHERE
t.name = {$this->pdo->quote($table_name)}
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id
X;
foreach ($this->pdo->query($query) as $row) {
$id = $row['name_index'];
$indexes[$id]['name'] = $id;
$indexes[$id]['unique'] = $row['is_unique'] !== 'False';
$indexes[$id]['primary'] = $row['is_primary_key'] !== 'False';
$indexes[$id]['columns'][$row['id_column'] - 1] = $row['name_column'];
}
return array_values($indexes);
}
public function getForeignKeys(string $table): array
{
[$table_schema, $table_name] = explode('.', $table);
$keys = [];
$query = <<<X
SELECT
obj.name AS [fk_name],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM
sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
WHERE
tab1.name = {$this->pdo->quote($table_name)}
X;
foreach ($this->pdo->query($query) as $id => $row) {
$keys[$id]['name'] = $row['fk_name'];
$keys[$id]['local'] = $row['column'];
$keys[$id]['table'] = $table_schema . '.' . $row['referenced_table'];
$keys[$id]['foreign'] = $row['referenced_column'];
}
return array_values($keys);
}
public function getColumnTypes(\PDOStatement $statement): array
{
return Nette\Database\Helpers::detectTypes($statement);
}
public function isSupported(string $item): bool
{
return $item === self::SupportSubselect;
}
}