|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* components |
|
4
|
|
|
* |
|
5
|
|
|
* @author Wolfy-J |
|
6
|
|
|
*/ |
|
7
|
|
|
namespace Spiral\Database\Drivers\SQLServer\Schemas; |
|
8
|
|
|
|
|
9
|
|
|
use Spiral\Database\Schemas\Prototypes\AbstractColumn; |
|
10
|
|
|
use Spiral\Database\Schemas\Prototypes\AbstractIndex; |
|
11
|
|
|
use Spiral\Database\Schemas\Prototypes\AbstractReference; |
|
12
|
|
|
use Spiral\Database\Schemas\Prototypes\AbstractTable; |
|
13
|
|
|
|
|
14
|
|
|
class SQLServerTable extends AbstractTable |
|
15
|
|
|
{ |
|
16
|
|
|
/** |
|
17
|
|
|
* {@inheritdoc} |
|
18
|
|
|
*/ |
|
19
|
|
|
protected function fetchColumns(): array |
|
20
|
|
|
{ |
|
21
|
|
|
$query = 'SELECT * FROM [information_schema].[columns] INNER JOIN [sys].[columns] AS [sysColumns] ' |
|
22
|
|
|
. 'ON (object_name([object_id]) = [table_name] AND [sysColumns].[name] = [COLUMN_NAME]) ' |
|
23
|
|
|
. 'WHERE [table_name] = ?'; |
|
24
|
|
|
|
|
25
|
|
|
$result = []; |
|
26
|
|
|
foreach ($this->driver->query($query, [$this->getName()]) as $schema) { |
|
27
|
|
|
//Column initialization needs driver to properly resolve enum type |
|
28
|
|
|
$result[] = SQLServerColumn::createInstance( |
|
29
|
|
|
$this->getName(), |
|
30
|
|
|
$schema, |
|
31
|
|
|
$this->driver |
|
32
|
|
|
); |
|
33
|
|
|
} |
|
34
|
|
|
|
|
35
|
|
|
return $result; |
|
36
|
|
|
} |
|
37
|
|
|
|
|
38
|
|
|
/** |
|
39
|
|
|
* {@inheritdoc} |
|
40
|
|
|
*/ |
|
41
|
|
|
protected function fetchIndexes(): array |
|
42
|
|
|
{ |
|
43
|
|
|
$query = 'SELECT [indexes].[name] AS [indexName], [cl].[name] AS [columnName], ' |
|
44
|
|
|
. "[is_primary_key] AS [isPrimary], [is_unique] AS [isUnique]\n" |
|
45
|
|
|
. "FROM [sys].[indexes] AS [indexes]\n" |
|
46
|
|
|
. "INNER JOIN [sys].[index_columns] as [columns]\n" |
|
47
|
|
|
. " ON [indexes].[object_id] = [columns].[object_id] AND [indexes].[index_id] = [columns].[index_id]\n" |
|
48
|
|
|
. "INNER JOIN [sys].[columns] AS [cl]\n" |
|
49
|
|
|
. " ON [columns].[object_id] = [cl].[object_id] AND [columns].[column_id] = [cl].[column_id]\n" |
|
50
|
|
|
. "INNER JOIN [sys].[tables] AS [t]\n" |
|
51
|
|
|
. " ON [indexes].[object_id] = [t].[object_id]\n" |
|
52
|
|
|
. 'WHERE [t].[name] = ? AND [is_primary_key] = 0 ORDER BY [indexes].[name], [indexes].[index_id], [columns].[index_column_id]'; |
|
53
|
|
|
|
|
54
|
|
|
$result = $indexes = []; |
|
55
|
|
|
foreach ($this->driver->query($query, [$this->getName()]) as $index) { |
|
56
|
|
|
//Collecting schemas first |
|
57
|
|
|
$indexes[$index['indexName']][] = $index; |
|
58
|
|
|
} |
|
59
|
|
|
|
|
60
|
|
|
foreach ($indexes as $name => $schema) { |
|
61
|
|
|
//Once all columns are aggregated we can finally create an index |
|
62
|
|
|
$result[] = SQLServerIndex::createInstance($this->getName(), $schema); |
|
63
|
|
|
} |
|
64
|
|
|
|
|
65
|
|
|
return $result; |
|
66
|
|
|
} |
|
67
|
|
|
|
|
68
|
|
|
/** |
|
69
|
|
|
* {@inheritdoc} |
|
70
|
|
|
*/ |
|
71
|
|
View Code Duplication |
protected function fetchReferences(): array |
|
|
|
|
|
|
72
|
|
|
{ |
|
73
|
|
|
$references = $this->driver->query('sp_fkeys @fktable_name = ?', [$this->getName()]); |
|
74
|
|
|
|
|
75
|
|
|
$result = []; |
|
76
|
|
|
foreach ($references as $schema) { |
|
77
|
|
|
$result[] = SQlServerReference::createInstance( |
|
78
|
|
|
$this->getName(), |
|
79
|
|
|
$this->getPrefix(), |
|
80
|
|
|
$schema |
|
81
|
|
|
); |
|
82
|
|
|
} |
|
83
|
|
|
|
|
84
|
|
|
return $result; |
|
85
|
|
|
} |
|
86
|
|
|
|
|
87
|
|
|
/** |
|
88
|
|
|
* {@inheritdoc} |
|
89
|
|
|
*/ |
|
90
|
|
|
protected function fetchPrimaryKeys(): array |
|
91
|
|
|
{ |
|
92
|
|
|
$query = "SELECT [indexes].[name] AS [indexName], [cl].[name] AS [columnName]\n" |
|
93
|
|
|
. "FROM [sys].[indexes] AS [indexes]\n" |
|
94
|
|
|
. "INNER JOIN [sys].[index_columns] as [columns]\n" |
|
95
|
|
|
. " ON [indexes].[object_id] = [columns].[object_id] AND [indexes].[index_id] = [columns].[index_id]\n" |
|
96
|
|
|
. "INNER JOIN [sys].[columns] AS [cl]\n" |
|
97
|
|
|
. " ON [columns].[object_id] = [cl].[object_id] AND [columns].[column_id] = [cl].[column_id]\n" |
|
98
|
|
|
. "INNER JOIN [sys].[tables] AS [t]\n" |
|
99
|
|
|
. " ON [indexes].[object_id] = [t].[object_id]\n" |
|
100
|
|
|
. 'WHERE [t].[name] = ? AND [is_primary_key] = 1 ORDER BY [indexes].[name], [indexes].[index_id], [columns].[index_column_id]'; |
|
101
|
|
|
|
|
102
|
|
|
$result = []; |
|
103
|
|
|
foreach ($this->driver->query($query, [$this->getName()]) as $schema) { |
|
104
|
|
|
$result[] = $schema['columnName']; |
|
105
|
|
|
} |
|
106
|
|
|
|
|
107
|
|
|
return $result; |
|
108
|
|
|
} |
|
109
|
|
|
|
|
110
|
|
|
/** |
|
111
|
|
|
* {@inheritdoc} |
|
112
|
|
|
*/ |
|
113
|
|
|
protected function createColumn(string $name): AbstractColumn |
|
114
|
|
|
{ |
|
115
|
|
|
return new SQLServerColumn($this->getName(), $name); |
|
116
|
|
|
} |
|
117
|
|
|
|
|
118
|
|
|
/** |
|
119
|
|
|
* {@inheritdoc} |
|
120
|
|
|
*/ |
|
121
|
|
|
protected function createIndex(string $name): AbstractIndex |
|
122
|
|
|
{ |
|
123
|
|
|
return new SQLServerIndex($this->getName(), $name); |
|
124
|
|
|
} |
|
125
|
|
|
|
|
126
|
|
|
/** |
|
127
|
|
|
* {@inheritdoc} |
|
128
|
|
|
*/ |
|
129
|
|
|
protected function createForeign(string $name): AbstractReference |
|
130
|
|
|
{ |
|
131
|
|
|
return new SQlServerReference($this->getName(), $this->getPrefix(), $name); |
|
132
|
|
|
} |
|
133
|
|
|
} |
spiral /
components
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.