AuditTable::createTriggers() - Code Metrics - Inspection of "Merge remote-tracking branch 'origin/master'" - SetBased/php-audit - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Push — master ( d02081...898276 )

by P.R.

created 2018-05-25 18:18 UTC

AuditTable::createTriggers() A

↳ Parent: AuditTable

Complexity

Conditions	1
Paths	1

Size

Total Lines	15
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	6
nc	1
nop	1
dl	0
loc	15
rs	9.4285
c	0
b	0
f	0

<?php

namespace SetBased\Audit;

use SetBased\Audit\Metadata\TableColumnsMetadata;
use SetBased\Audit\MySql\AuditDataLayer;
use SetBased\Audit\MySql\Metadata\AlterColumnMetadata;
use SetBased\Stratum\Style\StratumStyle;

/**
 * Class for creating audit tables and triggers.
 */
class AuditTable
{
  //--------------------------------------------------------------------------------------------------------------------
  /**
   * The metadata of the additional audit columns.
   *
   * @var TableColumnsMetadata
   */
  private $additionalAuditColumns;

  /**
   * The unique alias for this data table.
   *
   * @var string
   */
  private $alias;

  /**
   * The name of the schema with the audit tables.
   *
   * @var string
   */
  private $auditSchemaName;

  /**
   * The name of the schema with the data tables.
   *
   * @var string
   */
  private $dataSchemaName;

  /**
   * The metadata of the columns of the data table retrieved from information_schema.
   *
   * @var TableColumnsMetadata
   */
  private $dataTableColumnsDatabase;

  /**
   * The output decorator
   *
   * @var StratumStyle
   */
  private $io;

  /**
   * The skip variable for triggers.
   *
   * @var string
   */
  private $skipVariable;

  /**
   * The name of the data and audit table.
   *
   * @var string
   */
  private $tableName;

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Object constructor.
   *
   * @param StratumStyle         $io                     The output for log messages.
   * @param string               $dataSchemaName         The name of the schema with data tables.
   * @param string               $auditSchemaName        The name of the schema with audit tables.
   * @param string               $tableName              The name of the data and audit table.
   * @param TableColumnsMetadata $additionalAuditColumns The metadata of the additional audit columns.
   * @param string               $alias                  An unique alias for this table.
   * @param string               $skipVariable           The skip variable
   */
  public function __construct($io,
                              $dataSchemaName,
                              $auditSchemaName,
                              $tableName,
                              $additionalAuditColumns,
                              $alias,
                              $skipVariable)
  {
    $this->io                       = $io;
    $this->dataSchemaName           = $dataSchemaName;
    $this->auditSchemaName          = $auditSchemaName;
    $this->tableName                = $tableName;
    $this->dataTableColumnsDatabase = new TableColumnsMetadata($this->getColumnsFromInformationSchema());
    $this->additionalAuditColumns   = $additionalAuditColumns;
    $this->alias                    = $alias;
    $this->skipVariable             = $skipVariable;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Drops all audit triggers from a table.
   *
   * @param StratumStyle $io         The output decorator.
   * @param string       $schemaName The name of the table schema.
   * @param string       $tableName  The name of the table.
   */
  public static function dropAuditTriggers($io, $schemaName, $tableName)
  {
    $triggers = AuditDataLayer::getTableTriggers($schemaName, $tableName);
    foreach ($triggers as $trigger)
    {
      if (preg_match('/^trg_audit_.*_(insert|update|delete)$/', $trigger['trigger_name']))
      {
        $io->logVerbose('Dropping trigger <dbo>%s</dbo> on <dbo>%s.%s</dbo>',
                        $trigger['trigger_name'],
                        $schemaName,
                        $tableName);

        AuditDataLayer::dropTrigger($schemaName, $trigger['trigger_name']);
      }
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Returns a random alias for this table.
   *
   * @return string
   */
  public static function getRandomAlias()
  {
    return uniqid();
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Creates an audit table for this table.
   */
  public function createAuditTable()
  {
    $this->io->logInfo('Creating audit table <dbo>%s.%s<dbo>', $this->auditSchemaName, $this->tableName);

    // In the audit table all columns from the data table must be nullable.
    $dataTableColumnsDatabase = clone($this->dataTableColumnsDatabase);
    $dataTableColumnsDatabase->makeNullable();

    $columns = TableColumnsMetadata::combine($this->additionalAuditColumns, $dataTableColumnsDatabase);
    AuditDataLayer::createAuditTable($this->dataSchemaName, $this->auditSchemaName, $this->tableName, $columns);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Creates audit triggers on this table.
   *
   * @param string[] $additionalSql Additional SQL statements to be include in triggers.
   */
  public function createTriggers($additionalSql)
  {
    // Lock the table to prevent insert, updates, or deletes between dropping and creating triggers.
    $this->lockTable();

    // Drop all triggers, if any.
    static::dropAuditTriggers($this->io, $this->dataSchemaName, $this->tableName);

    // Create or recreate the audit triggers.
    $this->createTableTrigger('INSERT', $this->skipVariable, $additionalSql);
    $this->createTableTrigger('UPDATE', $this->skipVariable, $additionalSql);
    $this->createTableTrigger('DELETE', $this->skipVariable, $additionalSql);

    // Insert, updates, and deletes are no audited again. So, release lock on the table.
    $this->unlockTable();
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Returns the table name.
   *
   * @return string
   */
  public function getTableName()
  {
    return $this->tableName;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Main function for work with table.
   *
   * @param string[] $additionalSql Additional SQL statements to be include in triggers.
   */
  public function main($additionalSql)
  {
    $comparedColumns = $this->getTableColumnInfo();
    $newColumns      = $comparedColumns['new_columns'];

    $this->addNewColumns($newColumns);
    $this->createTriggers($additionalSql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Adds new columns to audit table.
   *
   * @param TableColumnsMetadata $columns TableColumnsMetadata array
   */
  private function addNewColumns($columns)
  {
    // Return immediately if there are no columns to add.
    if ($columns->getNumberOfColumns()==0) return;

    $alterColumns = $this->alterNewColumns($columns);

    AuditDataLayer::addNewColumns($this->auditSchemaName, $this->tableName, $alterColumns);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Returns metadata of new table columns that can be used in a 'alter table .. add column' statement.
   *
   * @param TableColumnsMetadata $newColumns The metadata new table columns.
   *
   * @return \SetBased\Audit\Metadata\TableColumnsMetadata
   */
  private function alterNewColumns($newColumns)
  {
    $alterNewColumns = new TableColumnsMetadata();
    foreach ($newColumns->getColumns() as $newColumn)
    {
      $properties          = $newColumn->getProperties();
      $properties['after'] = $this->dataTableColumnsDatabase->getPreviousColumn($properties['column_name']);

      $alterNewColumns->appendTableColumn(new AlterColumnMetadata($properties));
    }

    return $alterNewColumns;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Creates a triggers for this table.
   *
   * @param string      $action      The trigger action (INSERT, DELETE, or UPDATE).
   * @param string|null $skipVariable
   * @param string[]    $additionSql The additional SQL statements to be included in triggers.
   */
  private function createTableTrigger($action, $skipVariable, $additionSql)
  {
    $triggerName = $this->getTriggerName($action);

    $this->io->logVerbose('Creating trigger <dbo>%s.%s</dbo> on table <dbo>%s.%s</dbo>',
                          $this->dataSchemaName,
                          $triggerName,
                          $this->dataSchemaName,
                          $this->tableName);

    AuditDataLayer::createAuditTrigger($this->dataSchemaName,
                                       $this->auditSchemaName,
                                       $this->tableName,
                                       $triggerName,
                                       $action,
                                       $this->additionalAuditColumns,
                                       $this->dataTableColumnsDatabase,
                                       $skipVariable,
                                       $additionSql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects and returns the metadata of the columns of this table from information_schema.
   *
   * @return array[]
   */
  private function getColumnsFromInformationSchema()
  {
    $result = AuditDataLayer::getTableColumns($this->dataSchemaName, $this->tableName);

    return $result;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Compare columns from table in data_schema with columns in config file.
   *
   * @return array<string,TableColumnsMetadata>
   */
  private function getTableColumnInfo()
  {
    $actual = new TableColumnsMetadata(AuditDataLayer::getTableColumns($this->auditSchemaName, $this->tableName));
    $target = TableColumnsMetadata::combine($this->additionalAuditColumns, $this->dataTableColumnsDatabase);

    $new      = TableColumnsMetadata::notInOtherSet($target, $actual);
    $obsolete = TableColumnsMetadata::notInOtherSet($actual, $target);
    $altered  = TableColumnsMetadata::differentColumnTypes($actual, $target, ['is_nullable']);

    $this->logColumnInfo($new, $obsolete, $altered);

    return ['new_columns'      => $new,
            'obsolete_columns' => $obsolete,
            'altered_columns'  => $altered];
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Returns the trigger name for a trigger action.
   *
   * @param string $action Trigger on action (Insert, Update, Delete)
   *
   * @return string
   */
  private function getTriggerName($action)
  {
    return strtolower(sprintf('trg_audit_%s_%s', $this->alias, $action));
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Lock the data table to prevent insert, updates, or deletes between dropping and creating triggers.
   */
  private function lockTable()
  {
    AuditDataLayer::lockTable($this->dataSchemaName, $this->tableName);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Logs info about new, obsolete, and altered columns.
   *
   * @param \SetBased\Audit\Metadata\TableColumnsMetadata $newColumns      The metadata of the new columns.
   * @param TableColumnsMetadata                          $obsoleteColumns The metadata of the obsolete columns.
   * @param \SetBased\Audit\Metadata\TableColumnsMetadata $alteredColumns  The metadata of the altered columns.
   */
  private function logColumnInfo($newColumns, $obsoleteColumns, $alteredColumns)
  {
    foreach ($newColumns->getColumns() as $column)
    {
      $this->io->logInfo('New column <dbo>%s.%s</dbo>',
                         $this->tableName,
                         $column->getName());
    }

    foreach ($obsoleteColumns->getColumns() as $column)
    {
      $this->io->logInfo('Obsolete column <dbo>%s.%s</dbo>',
                         $this->tableName,
                         $column->getName());
    }

    foreach ($alteredColumns->getColumns() as $column)
    {
      $this->io->logInfo('Type of <dbo>%s.%s</dbo> has been altered to <dbo>%s</dbo>',
                         $this->tableName,
                         $column->getName(),
                         $column->getProperty('column_type'));
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Releases the table lock.
   */
  private function unlockTable()
  {
    AuditDataLayer::unlockTables();
  }

  //--------------------------------------------------------------------------------------------------------------------
}

//----------------------------------------------------------------------------------------------------------------------


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

SetBased / php-audit

Push — master ( d02081...898276 )

AuditTable::createTriggers() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like