Completed
Push — master ( 08dfad...a46189 )
by P.R.
04:31
created

AuditTable::alterNewColumns()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 12
Code Lines 6

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 7
CRAP Score 2

Importance

Changes 0
Metric Value
cc 2
eloc 6
nc 2
nop 1
dl 0
loc 12
ccs 7
cts 7
cp 1
crap 2
rs 10
c 0
b 0
f 0
1
<?php
2
declare(strict_types=1);
3
4
namespace SetBased\Audit;
5
6
use SetBased\Audit\Metadata\TableColumnsMetadata;
7
use SetBased\Audit\MySql\AuditDataLayer;
8
use SetBased\Audit\MySql\Metadata\AlterColumnMetadata;
9
use SetBased\Audit\Style\AuditStyle;
10
11
/**
12
 * Class for creating audit tables and triggers.
13
 */
14
class AuditTable
15
{
16
  //--------------------------------------------------------------------------------------------------------------------
17
  /**
18
   * The metadata of the additional audit columns.
19
   *
20
   * @var TableColumnsMetadata
21
   */
22
  private $additionalAuditColumns;
23
24
  /**
25
   * The unique alias for this data table.
26
   *
27
   * @var string|null
28
   */
29
  private $alias;
30
31
  /**
32
   * The name of the schema with the audit tables.
33
   *
34
   * @var string
35
   */
36
  private $auditSchemaName;
37
38
  /**
39
   * The name of the schema with the data tables.
40
   *
41
   * @var string
42
   */
43
  private $dataSchemaName;
44
45
  /**
46
   * The metadata of the columns of the data table retrieved from information_schema.
47
   *
48
   * @var TableColumnsMetadata
49
   */
50
  private $dataTableColumnsDatabase;
51
52
  /**
53
   * The output decorator
54
   *
55
   * @var AuditStyle
56
   */
57
  private $io;
58
59
  /**
60
   * The skip variable for triggers.
61
   *
62
   * @var string|null
63
   */
64
  private $skipVariable;
65
66
  /**
67
   * The name of the data and audit table.
68
   *
69
   * @var string
70
   */
71
  private $tableName;
72
73
  //--------------------------------------------------------------------------------------------------------------------
74
  /**
75
   * Object constructor.
76
   *
77
   * @param AuditStyle           $io                     The output for log messages.
78
   * @param string               $dataSchemaName         The name of the schema with data tables.
79
   * @param string               $auditSchemaName        The name of the schema with audit tables.
80
   * @param string               $tableName              The name of the data and audit table.
81
   * @param TableColumnsMetadata $additionalAuditColumns The metadata of the additional audit columns.
82
   * @param string|null          $alias                  An unique alias for this table.
83
   * @param string|null          $skipVariable           The skip variable
84
   */
85 28
  public function __construct(AuditStyle $io,
86
                              string $dataSchemaName,
87
                              string $auditSchemaName,
88
                              string $tableName,
89
                              TableColumnsMetadata $additionalAuditColumns,
90
                              ?string $alias,
91
                              ?string $skipVariable)
92
  {
93 28
    $this->io                       = $io;
94 28
    $this->dataSchemaName           = $dataSchemaName;
95 28
    $this->auditSchemaName          = $auditSchemaName;
96 28
    $this->tableName                = $tableName;
97 28
    $this->dataTableColumnsDatabase = new TableColumnsMetadata($this->getColumnsFromInformationSchema());
98 28
    $this->additionalAuditColumns   = $additionalAuditColumns;
99 28
    $this->alias                    = $alias;
100 28
    $this->skipVariable             = $skipVariable;
101 28
  }
102
103
  //--------------------------------------------------------------------------------------------------------------------
104
  /**
105
   * Drops all audit triggers from a table.
106
   *
107
   * @param AuditStyle $io         The output decorator.
108
   * @param string     $schemaName The name of the table schema.
109
   * @param string     $tableName  The name of the table.
110
   */
111 28
  public static function dropAuditTriggers(AuditStyle $io, string $schemaName, string $tableName): void
112
  {
113 28
    $triggers = AuditDataLayer::getTableTriggers($schemaName, $tableName);
114 28
    foreach ($triggers as $trigger)
115
    {
116 10
      if (preg_match('/^trg_audit_.*_(insert|update|delete)$/', $trigger['trigger_name']))
117
      {
118 10
        $io->logVerbose('Dropping trigger <dbo>%s</dbo> on <dbo>%s.%s</dbo>',
119 10
                        $trigger['trigger_name'],
120
                        $schemaName,
121
                        $tableName);
122
123 10
        AuditDataLayer::dropTrigger($schemaName, $trigger['trigger_name']);
124
      }
125
    }
126 28
  }
127
128
  //--------------------------------------------------------------------------------------------------------------------
129
  /**
130
   * Returns a random alias for this table.
131
   *
132
   * @return string
133
   */
134
  public static function getRandomAlias(): string
135
  {
136
    return uniqid();
137
  }
138
139
  //--------------------------------------------------------------------------------------------------------------------
140
  /**
141
   * Creates an audit table for this table.
142
   */
143 25
  public function createAuditTable(): void
144
  {
145 25
    $this->io->logInfo('Creating audit table <dbo>%s.%s<dbo>', $this->auditSchemaName, $this->tableName);
146
147
    // In the audit table all columns from the data table must be nullable.
148 25
    $dataTableColumnsDatabase = clone($this->dataTableColumnsDatabase);
149 25
    $dataTableColumnsDatabase->makeNullable();
150
151 25
    $columns = TableColumnsMetadata::combine($this->additionalAuditColumns, $dataTableColumnsDatabase);
152 25
    AuditDataLayer::createAuditTable($this->dataSchemaName, $this->auditSchemaName, $this->tableName, $columns);
153 25
  }
154
155
  //--------------------------------------------------------------------------------------------------------------------
156
  /**
157
   * Creates audit triggers on this table.
158
   *
159
   * @param string[] $additionalSql Additional SQL statements to be include in triggers.
160
   */
161 28
  public function createTriggers(array $additionalSql): void
162
  {
163
    // Lock the table to prevent insert, updates, or deletes between dropping and creating triggers.
164 28
    $this->lockTable();
165
166
    // Drop all triggers, if any.
167 28
    static::dropAuditTriggers($this->io, $this->dataSchemaName, $this->tableName);
168
169
    // Create or recreate the audit triggers.
170 28
    $this->createTableTrigger('INSERT', $this->skipVariable, $additionalSql);
171 28
    $this->createTableTrigger('UPDATE', $this->skipVariable, $additionalSql);
172 28
    $this->createTableTrigger('DELETE', $this->skipVariable, $additionalSql);
173
174
    // Insert, updates, and deletes are no audited again. So, release lock on the table.
175 28
    $this->unlockTable();
176 28
  }
177
178
  //--------------------------------------------------------------------------------------------------------------------
179
  /**
180
   * Returns the table name.
181
   *
182
   * @return string
183
   */
184
  public function getTableName(): string
185
  {
186
    return $this->tableName;
187
  }
188
189
  //--------------------------------------------------------------------------------------------------------------------
190
  /**
191
   * Main function for work with table.
192
   *
193
   * @param string[] $additionalSql Additional SQL statements to be include in triggers.
194
   */
195 28
  public function main(array $additionalSql): void
196
  {
197 28
    $comparedColumns = $this->getTableColumnInfo();
198 28
    $newColumns      = $comparedColumns['new_columns'];
199
200 28
    $this->addNewColumns($newColumns);
201 28
    $this->createTriggers($additionalSql);
202 28
  }
203
204
  //--------------------------------------------------------------------------------------------------------------------
205
  /**
206
   * Adds new columns to audit table.
207
   *
208
   * @param TableColumnsMetadata $columns TableColumnsMetadata array
209
   */
210 28
  private function addNewColumns(TableColumnsMetadata $columns): void
211
  {
212
    // Return immediately if there are no columns to add.
213 28
    if ($columns->getNumberOfColumns()==0) return;
214
215 2
    $alterColumns = $this->alterNewColumns($columns);
216
217 2
    AuditDataLayer::addNewColumns($this->auditSchemaName, $this->tableName, $alterColumns);
218 2
  }
219
220
  //--------------------------------------------------------------------------------------------------------------------
221
  /**
222
   * Returns metadata of new table columns that can be used in a 'alter table .. add column' statement.
223
   *
224
   * @param TableColumnsMetadata $newColumns The metadata new table columns.
225
   *
226
   * @return TableColumnsMetadata
227
   */
228 2
  private function alterNewColumns(TableColumnsMetadata $newColumns): TableColumnsMetadata
229
  {
230 2
    $alterNewColumns = new TableColumnsMetadata();
231 2
    foreach ($newColumns->getColumns() as $newColumn)
232
    {
233 2
      $properties          = $newColumn->getProperties();
234 2
      $properties['after'] = $this->dataTableColumnsDatabase->getPreviousColumn($properties['column_name']);
235
236 2
      $alterNewColumns->appendTableColumn(new AlterColumnMetadata($properties));
237
    }
238
239 2
    return $alterNewColumns;
240
  }
241
242
  //--------------------------------------------------------------------------------------------------------------------
243
  /**
244
   * Creates a triggers for this table.
245
   *
246
   * @param string      $action      The trigger action (INSERT, DELETE, or UPDATE).
247
   * @param string|null $skipVariable
248
   * @param string[]    $additionSql The additional SQL statements to be included in triggers.
249
   */
250 28
  private function createTableTrigger(string $action, ?string $skipVariable, array $additionSql): void
251
  {
252 28
    $triggerName = $this->getTriggerName($action);
253
254 28
    $this->io->logVerbose('Creating trigger <dbo>%s.%s</dbo> on table <dbo>%s.%s</dbo>',
255 28
                          $this->dataSchemaName,
256
                          $triggerName,
257 28
                          $this->dataSchemaName,
258 28
                          $this->tableName);
259
260 28
    AuditDataLayer::createAuditTrigger($this->dataSchemaName,
261 28
                                       $this->auditSchemaName,
262 28
                                       $this->tableName,
263
                                       $triggerName,
264
                                       $action,
265 28
                                       $this->additionalAuditColumns,
266 28
                                       $this->dataTableColumnsDatabase,
267
                                       $skipVariable,
268
                                       $additionSql);
269 28
  }
270
271
  //--------------------------------------------------------------------------------------------------------------------
272
  /**
273
   * Selects and returns the metadata of the columns of this table from information_schema.
274
   *
275
   * @return array[]
276
   */
277 28
  private function getColumnsFromInformationSchema(): array
278
  {
279 28
    $result = AuditDataLayer::getTableColumns($this->dataSchemaName, $this->tableName);
280
281 28
    return $result;
282
  }
283
284
  //--------------------------------------------------------------------------------------------------------------------
285
  /**
286
   * Compare columns from table in data_schema with columns in config file.
287
   *
288
   * @return array<string,TableColumnsMetadata>
289
   */
290 28
  private function getTableColumnInfo(): array
291
  {
292 28
    $actual = new TableColumnsMetadata(AuditDataLayer::getTableColumns($this->auditSchemaName, $this->tableName));
293 28
    $target = TableColumnsMetadata::combine($this->additionalAuditColumns, $this->dataTableColumnsDatabase);
294
295 28
    $new      = TableColumnsMetadata::notInOtherSet($target, $actual);
296 28
    $obsolete = TableColumnsMetadata::notInOtherSet($actual, $target);
297 28
    $altered  = TableColumnsMetadata::differentColumnTypes($actual, $target, ['is_nullable']);
298
299 28
    $this->logColumnInfo($new, $obsolete, $altered);
300
301 28
    return ['new_columns'      => $new,
302 28
            'obsolete_columns' => $obsolete,
303 28
            'altered_columns'  => $altered];
304
  }
305
306
  //--------------------------------------------------------------------------------------------------------------------
307
  /**
308
   * Returns the trigger name for a trigger action.
309
   *
310
   * @param string $action Trigger on action (Insert, Update, Delete)
311
   *
312
   * @return string
313
   */
314 28
  private function getTriggerName(string $action): string
315
  {
316 28
    return strtolower(sprintf('trg_audit_%s_%s', $this->alias, $action));
317
  }
318
319
  //--------------------------------------------------------------------------------------------------------------------
320
  /**
321
   * Lock the data table to prevent insert, updates, or deletes between dropping and creating triggers.
322
   */
323 28
  private function lockTable(): void
324
  {
325 28
    AuditDataLayer::lockTable($this->dataSchemaName, $this->tableName);
326 28
  }
327
328
  //--------------------------------------------------------------------------------------------------------------------
329
  /**
330
   * Logs info about new, obsolete, and altered columns.
331
   *
332
   * @param TableColumnsMetadata $newColumns      The metadata of the new columns.
333
   * @param TableColumnsMetadata $obsoleteColumns The metadata of the obsolete columns.
334
   * @param TableColumnsMetadata $alteredColumns  The metadata of the altered columns.
335
   */
336 28
  private function logColumnInfo(TableColumnsMetadata $newColumns,
337
                                 TableColumnsMetadata $obsoleteColumns,
338
                                 TableColumnsMetadata $alteredColumns): void
339
  {
340 28
    foreach ($newColumns->getColumns() as $column)
341
    {
342 2
      $this->io->logInfo('New column <dbo>%s.%s</dbo>',
343 2
                         $this->tableName,
344 2
                         $column->getName());
345
    }
346
347 28
    foreach ($obsoleteColumns->getColumns() as $column)
348
    {
349 2
      $this->io->logInfo('Obsolete column <dbo>%s.%s</dbo>',
350 2
                         $this->tableName,
351 2
                         $column->getName());
352
    }
353
354 28
    foreach ($alteredColumns->getColumns() as $column)
355
    {
356
      $this->io->logInfo('Type of <dbo>%s.%s</dbo> has been altered to <dbo>%s</dbo>',
357
                         $this->tableName,
358
                         $column->getName(),
359
                         $column->getProperty('column_type'));
360
    }
361 28
  }
362
363
  //--------------------------------------------------------------------------------------------------------------------
364
  /**
365
   * Releases the table lock.
366
   */
367 28
  private function unlockTable(): void
368
  {
369 28
    AuditDataLayer::unlockTables();
370 28
  }
371
372
  //--------------------------------------------------------------------------------------------------------------------
373
}
374
375
//----------------------------------------------------------------------------------------------------------------------
376