AuditDataLayer::getTriggers() - Code Metrics - Inspection of "Updated dependencies (setbased/php-stratum-mysql:..." - SetBased/php-audit - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( a46189...4fb833 )

by P.R.

created 2020-04-11 13:36 UTC

AuditDataLayer::getTriggers() A

↳ Parent: AuditDataLayer

Complexity

Conditions	1
Paths	1

Size

Total Lines	12
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes

Metric	Value
cc	1
eloc	3
nc	1
nop	1
dl	0
loc	12
ccs	0
cts	4
cp	0
crap	2
rs	10
c	0
b	0
f	0

<?php
declare(strict_types=1);

namespace SetBased\Audit\MySql;

use SetBased\Audit\Metadata\TableColumnsMetadata;
use SetBased\Audit\MySql\Sql\AlterAuditTableAddColumns;
use SetBased\Audit\MySql\Sql\CreateAuditTable;
use SetBased\Audit\MySql\Sql\CreateAuditTrigger;
use SetBased\Audit\Style\AuditStyle;
use SetBased\Helper\CodeStore\MySqlCompoundSyntaxCodeStore;
use SetBased\Stratum\Middle\BulkHandler;
use SetBased\Stratum\Middle\Helper\RowSetHelper;
use SetBased\Stratum\MySql\MySqlConnector;
use SetBased\Stratum\MySql\MySqlDataLayer;

/**
 * Class for executing SQL statements and retrieving metadata from MySQL.
 */
class AuditDataLayer extends MySqlDataLayer
{
  //--------------------------------------------------------------------------------------------------------------------
  /**
   * The singleton of this class.
   *
   * @var AuditDataLayer
   */
  public static $dl;

  /**
   * The Output decorator.
   *
   * @var AuditStyle
   */
  private $io;

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * AuditDataLayer constructor.
   *
   * @param MySqlConnector $connector The object for connecting to the MySql instance.
   * @param AuditStyle     $io        The Output decorator.
   */
  public function __construct(MySqlConnector $connector, AuditStyle $io)
  {
    parent::__construct($connector);

    $this->io = $io;
    self::$dl = $this;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Adds new columns to an audit table.
   *
   * @param string               $auditSchemaName The name of audit schema.
   * @param string               $tableName       The name of the table.
   * @param TableColumnsMetadata $columns         The metadata of the new columns.
   */
  public function addNewColumns(string $auditSchemaName, string $tableName, TableColumnsMetadata $columns): void
  {
    $helper = new AlterAuditTableAddColumns($auditSchemaName, $tableName, $columns);
    $sql    = $helper->buildStatement();

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Creates an audit table.
   *
   * @param string               $dataSchemaName  The name of the data schema.
   * @param string               $auditSchemaName The name of the audit schema.
   * @param string               $tableName       The name of the table.
   * @param TableColumnsMetadata $columns         The metadata of the columns of the audit table (i.e. the audit
   *                                              columns and columns of the data table).
   */
  public function createAuditTable(string $dataSchemaName,
                                   string $auditSchemaName,
                                   string $tableName,
                                   TableColumnsMetadata $columns): void
  {
    $helper = new CreateAuditTable($dataSchemaName, $auditSchemaName, $tableName, $columns);
    $sql    = $helper->buildStatement();

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Creates a trigger on a table.
   *
   * @param string               $dataSchemaName         The name of the data schema.
   * @param string               $auditSchemaName        The name of the audit schema.
   * @param string               $tableName              The name of the table.
   * @param string               $triggerAction          The trigger action (i.e. INSERT, UPDATE, or DELETE).
   * @param string               $triggerName            The name of the trigger.
   * @param TableColumnsMetadata $additionalAuditColumns The metadata of the additional audit columns.
   * @param TableColumnsMetadata $tableColumns           The metadata of the data table columns.
   * @param string|null          $skipVariable           The skip variable.
   * @param string[]             $additionSql            Additional SQL statements.
   */
  public function createAuditTrigger(string $dataSchemaName,
                                     string $auditSchemaName,
                                     string $tableName,
                                     string $triggerName,
                                     string $triggerAction,
                                     TableColumnsMetadata $additionalAuditColumns,
                                     TableColumnsMetadata $tableColumns,
                                     ?string $skipVariable,
                                     array $additionSql): void
  {
    $helper = new CreateAuditTrigger($dataSchemaName,
                                     $auditSchemaName,
                                     $tableName,
                                     $triggerName,
                                     $triggerAction,
                                     $additionalAuditColumns,
                                     $tableColumns,
                                     $skipVariable,
                                     $additionSql);
    $sql    = $helper->buildStatement();

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Creates a temporary table for getting column type information for audit columns.
   *
   * @param string  $schemaName   The name of the table schema.
   * @param string  $tableName    The table name.
   * @param array[] $auditColumns Audit columns from config file.
   */
  public function createTemporaryTable(string $schemaName, string $tableName, array $auditColumns): void
  {
    $sql = new MySqlCompoundSyntaxCodeStore();
    $sql->append(sprintf('create table `%s`.`%s` (', $schemaName, $tableName));
    foreach ($auditColumns as $column)
    {
      $sql->append(sprintf('%s %s', $column['column_name'], $column['column_type']));
      if (end($auditColumns)!==$column)
      {
        $sql->appendToLastLine(',');
      }
    }
    $sql->append(')');

    $this->executeNone($sql->getCode());
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Drops a temporary table.
   *
   * @param string $schemaName The name of the table schema.
   * @param string $tableName  The name of the table.
   */
  public function dropTemporaryTable(string $schemaName, string $tableName): void
  {
    $sql = sprintf('drop table `%s`.`%s`', $schemaName, $tableName);

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Drops a trigger.
   *
   * @param string $triggerSchema The name of the trigger schema.
   * @param string $triggerName   The mame of trigger.
   */
  public function dropTrigger(string $triggerSchema, string $triggerName): void
  {
    $sql = sprintf('drop trigger `%s`.`%s`', $triggerSchema, $triggerName);

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeBulk(BulkHandler $bulkHandler, string $query): void
  {
    $this->logQuery($query);

    parent::executeBulk($bulkHandler, $query);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeMulti(string $queries): array
  {
    $this->logQuery($queries);

    return parent::executeMulti($queries);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeNone(string $query): int
  {
    $this->logQuery($query);

    return parent::executeNone($query);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeRow0(string $query): ?array
  {
    $this->logQuery($query);

    return parent::executeRow0($query);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeRow1(string $query): array
  {
    $this->logQuery($query);

    return parent::executeRow1($query);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeRows(string $query): array
  {
    $this->logQuery($query);

    return parent::executeRows($query);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeSingleton0(string $query)
  {
    $this->logQuery($query);

    return parent::executeSingleton0($query);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeSingleton1(string $query)
  {
    $this->logQuery($query);

    return parent::executeSingleton1($query);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @inheritdoc
   */
  public function executeTable(string $query): int
  {
    $this->logQuery($query);

    return parent::executeTable($query);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects metadata of all columns of table.
   *
   * @param string $schemaName The name of the table schema.
   * @param string $tableName  The name of the table.
   *
   * @return array[]
   */
  public function getTableColumns(string $schemaName, string $tableName): array
  {
    // When a column has no default prior to MariaDB 10.2.7 column_default is null from MariaDB 10.2.7
    // column_default = 'NULL' (string(4)).
    $sql = sprintf("
select COLUMN_NAME                    as column_name
,      COLUMN_TYPE                    as column_type
,      ifnull(COLUMN_DEFAULT, 'NULL') as column_default 
,      IS_NULLABLE                    as is_nullable
,      CHARACTER_SET_NAME             as character_set_name
,      COLLATION_NAME                 as collation_name
from   information_schema.COLUMNS
where  TABLE_SCHEMA = %s
and    TABLE_NAME   = %s
order by ORDINAL_POSITION",
                   $this->quoteString($schemaName),
                   $this->quoteString($tableName));

    return $this->executeRows($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects table engine, character_set_name and table_collation.
   *
   * @param string $schemaName The name of the table schema.
   * @param string $tableName  The name of the table.
   *
   * @return array
   */
  public function getTableOptions(string $schemaName, string $tableName): array
  {
    $sql = sprintf('
select t1.TABLE_SCHEMA       as table_schema
,      t1.TABLE_NAME         as table_name
,      t1.TABLE_COLLATION    as table_collation
,      t1.ENGINE             as engine
,      t2.CHARACTER_SET_NAME as character_set_name
from       information_schema.TABLES                                t1
inner join information_schema.COLLATION_CHARACTER_SET_APPLICABILITY t2  on  t2.COLLATION_NAME = t1.TABLE_COLLATION
where t1.TABLE_SCHEMA = %s
and   t1.TABLE_NAME   = %s',
                   $this->quoteString($schemaName),
                   $this->quoteString($tableName));

    return $this->executeRow1($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects all triggers on a table.
   *
   * @param string $schemaName The name of the table schema.
   * @param string $tableName  The name of the table.
   *
   * @return array[]
   */
  public function getTableTriggers(string $schemaName, string $tableName): array
  {
    $sql = sprintf('
select TRIGGER_NAME as trigger_name
from   information_schema.TRIGGERS
where  TRIGGER_SCHEMA     = %s
and    EVENT_OBJECT_TABLE = %s
order by Trigger_Name',
                   $this->quoteString($schemaName),
                   $this->quoteString($tableName));

    return $this->executeRows($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects all table names in a schema.
   *
   * @param string $schemaName The name of the schema.
   *
   * @return array[]
   */
  public function getTablesNames(string $schemaName): array
  {
    $sql = sprintf("
select TABLE_NAME as table_name
from   information_schema.TABLES
where  TABLE_SCHEMA = %s
and    TABLE_TYPE   = 'BASE TABLE'
order by TABLE_NAME", $this->quoteString($schemaName));

    return $this->executeRows($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects all triggers in a schema
   *
   * @param string $schemaName The name of the table schema.
   *
   * @return array[]
   */
  public function getTriggers(string $schemaName): array
  {
    $sql = sprintf('
select EVENT_OBJECT_TABLE as table_name
,      TRIGGER_NAME       as trigger_name
from   information_schema.TRIGGERS
where  TRIGGER_SCHEMA     = %s
order by EVENT_OBJECT_TABLE
,        TRIGGER_NAME',
                   $this->quoteString($schemaName));

    return $this->executeRows($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Acquires a write lock on a table.
   *
   * @param string $schemaName The schema of the table.
   * @param string $tableName  The table name.
   */
  public function lockTable(string $schemaName, string $tableName): void
  {
    $sql = sprintf('lock tables `%s`.`%s` write', $schemaName, $tableName);

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Resolves the canonical column types of the additional audit columns.
   *
   * @param string  $auditSchema            The name of the audit schema.
   * @param array[] $additionalAuditColumns The metadata of the additional audit columns.
   *
   * @return TableColumnsMetadata
   */
  public function resolveCanonicalAdditionalAuditColumns(string $auditSchema,
                                                         array $additionalAuditColumns): TableColumnsMetadata
  {
    if (empty($additionalAuditColumns))
    {
      return new TableColumnsMetadata([], 'AuditColumnMetadata');
    }

    $tableName = '_TMP_'.uniqid();
    $this->createTemporaryTable($auditSchema, $tableName, $additionalAuditColumns);
    $columns = AuditDataLayer::$dl->getTableColumns($auditSchema, $tableName);
    $this->dropTemporaryTable($auditSchema, $tableName);

    foreach ($additionalAuditColumns as $column)
    {
      $key = RowSetHelper::findInRowSet($columns, 'column_name', $column['column_name']);

      if (isset($column['value_type']))
      {
        $columns[$key]['value_type'] = $column['value_type'];
      }
      if (isset($column['expression']))
      {
        $columns[$key]['expression'] = $column['expression'];
      }
    }

    return new TableColumnsMetadata($columns, 'AuditColumnMetadata');
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Releases all table locks.
   */
  public function unlockTables(): void
  {
    $sql = 'unlock tables';

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Logs the query on the console.
   *
   * @param string $query The query.
   */
  private function logQuery(string $query): void
  {
    $query = trim($query);

    if (strpos($query, "\n")!==false)
    {
      // Query is a multi line query.
      $this->io->logVeryVerbose('Executing query:');
      $this->io->logVeryVerbose('<sql>%s</sql>', $query);
    }
    else
    {
      // Query is a single line query.
      $this->io->logVeryVerbose('Executing query: <sql>%s</sql>', $query);
    }
  }

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

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


1		<?php
2		declare(strict_types=1);
3
4		namespace SetBased\Audit\MySql;
5
6		use SetBased\Audit\Metadata\TableColumnsMetadata;
7		use SetBased\Audit\MySql\Sql\AlterAuditTableAddColumns;
8		use SetBased\Audit\MySql\Sql\CreateAuditTable;
9		use SetBased\Audit\MySql\Sql\CreateAuditTrigger;
10		use SetBased\Audit\Style\AuditStyle;
11		use SetBased\Helper\CodeStore\MySqlCompoundSyntaxCodeStore;
12		use SetBased\Stratum\Middle\BulkHandler;
13		use SetBased\Stratum\Middle\Helper\RowSetHelper;
14		use SetBased\Stratum\MySql\MySqlConnector;
15		use SetBased\Stratum\MySql\MySqlDataLayer;
16
17		/**
18		* Class for executing SQL statements and retrieving metadata from MySQL.
19		*/
20		class AuditDataLayer extends MySqlDataLayer
21		{
22		//--------------------------------------------------------------------------------------------------------------------
23		/**
24		* The singleton of this class.
25		*
26		* @var AuditDataLayer
27		*/
28		public static $dl;
29
30		/**
31		* The Output decorator.
32		*
33		* @var AuditStyle
34		*/
35		private $io;
36
37		//--------------------------------------------------------------------------------------------------------------------
38		/**
39		* AuditDataLayer constructor.
40		*
41		* @param MySqlConnector $connector The object for connecting to the MySql instance.
42		* @param AuditStyle $io The Output decorator.
43		*/
44	29	public function __construct(MySqlConnector $connector, AuditStyle $io)
45		{
46	29	parent::__construct($connector);
47
48	29	$this->io = $io;
49	29	self::$dl = $this;
50	29	}
51
52		//--------------------------------------------------------------------------------------------------------------------
53		/**
54		* Adds new columns to an audit table.
55		*
56		* @param string $auditSchemaName The name of audit schema.
57		* @param string $tableName The name of the table.
58		* @param TableColumnsMetadata $columns The metadata of the new columns.
59		*/
60	2	public function addNewColumns(string $auditSchemaName, string $tableName, TableColumnsMetadata $columns): void
61		{
62	2	$helper = new AlterAuditTableAddColumns($auditSchemaName, $tableName, $columns);
63	2	$sql = $helper->buildStatement();
64
65	2	$this->executeNone($sql);
66	2	}
67
68		//--------------------------------------------------------------------------------------------------------------------
69		/**
70		* Creates an audit table.
71		*
72		* @param string $dataSchemaName The name of the data schema.
73		* @param string $auditSchemaName The name of the audit schema.
74		* @param string $tableName The name of the table.
75		* @param TableColumnsMetadata $columns The metadata of the columns of the audit table (i.e. the audit
76		* columns and columns of the data table).
77		*/
78	25	public function createAuditTable(string $dataSchemaName,
79		string $auditSchemaName,
80		string $tableName,
81		TableColumnsMetadata $columns): void
82		{
83	25	$helper = new CreateAuditTable($dataSchemaName, $auditSchemaName, $tableName, $columns);
84	25	$sql = $helper->buildStatement();
85
86	25	$this->executeNone($sql);
87	25	}
88
89		//--------------------------------------------------------------------------------------------------------------------
90		/**
91		* Creates a trigger on a table.
92		*
93		* @param string $dataSchemaName The name of the data schema.
94		* @param string $auditSchemaName The name of the audit schema.
95		* @param string $tableName The name of the table.
96		* @param string $triggerAction The trigger action (i.e. INSERT, UPDATE, or DELETE).
97		* @param string $triggerName The name of the trigger.
98		* @param TableColumnsMetadata $additionalAuditColumns The metadata of the additional audit columns.
99		* @param TableColumnsMetadata $tableColumns The metadata of the data table columns.
100		* @param string\|null $skipVariable The skip variable.
101		* @param string[] $additionSql Additional SQL statements.
102		*/
103	28	public function createAuditTrigger(string $dataSchemaName,
104		string $auditSchemaName,
105		string $tableName,
106		string $triggerName,
107		string $triggerAction,
108		TableColumnsMetadata $additionalAuditColumns,
109		TableColumnsMetadata $tableColumns,
110		?string $skipVariable,
111		array $additionSql): void
112		{
113	28	$helper = new CreateAuditTrigger($dataSchemaName,
114		$auditSchemaName,
115		$tableName,
116		$triggerName,
117		$triggerAction,
118		$additionalAuditColumns,
119		$tableColumns,
120		$skipVariable,
121		$additionSql);
122	28	$sql = $helper->buildStatement();
123
124	28	$this->executeNone($sql);
125	28	}
126
127		//--------------------------------------------------------------------------------------------------------------------
128		/**
129		* Creates a temporary table for getting column type information for audit columns.
130		*
131		* @param string $schemaName The name of the table schema.
132		* @param string $tableName The table name.
133		* @param array[] $auditColumns Audit columns from config file.
134		*/
135	19	public function createTemporaryTable(string $schemaName, string $tableName, array $auditColumns): void
136		{
137	19	$sql = new MySqlCompoundSyntaxCodeStore();
138	19	$sql->append(sprintf('create table `%s`.`%s` (', $schemaName, $tableName));
139	19	foreach ($auditColumns as $column)
140		{
141	19	$sql->append(sprintf('%s %s', $column['column_name'], $column['column_type']));
142	19	if (end($auditColumns)!==$column)
143		{
144	19	$sql->appendToLastLine(',');
145		}
146		}
147	19	$sql->append(')');
148
149	19	$this->executeNone($sql->getCode());
150	19	}
151
152		//--------------------------------------------------------------------------------------------------------------------
153		/**
154		* Drops a temporary table.
155		*
156		* @param string $schemaName The name of the table schema.
157		* @param string $tableName The name of the table.
158		*/
159	19	public function dropTemporaryTable(string $schemaName, string $tableName): void
160		{
161	19	$sql = sprintf('drop table `%s`.`%s`', $schemaName, $tableName);
162
163	19	$this->executeNone($sql);
164	19	}
165
166		//--------------------------------------------------------------------------------------------------------------------
167		/**
168		* Drops a trigger.
169		*
170		* @param string $triggerSchema The name of the trigger schema.
171		* @param string $triggerName The mame of trigger.
172		*/
173	10	public function dropTrigger(string $triggerSchema, string $triggerName): void
174		{
175	10	$sql = sprintf('drop trigger `%s`.`%s`', $triggerSchema, $triggerName);
176
177	10	$this->executeNone($sql);
178	10	}
179
180		//--------------------------------------------------------------------------------------------------------------------
181		/**
182		* @inheritdoc
183		*/
184		public function executeBulk(BulkHandler $bulkHandler, string $query): void
185		{
186		$this->logQuery($query);
187
188		parent::executeBulk($bulkHandler, $query);
189		}
190
191		//--------------------------------------------------------------------------------------------------------------------
192		/**
193		* @inheritdoc
194		*/
195	19	public function executeMulti(string $queries): array
196		{
197	19	$this->logQuery($queries);
198
199	19	return parent::executeMulti($queries);
200		}
201
202		//--------------------------------------------------------------------------------------------------------------------
203		/**
204		* @inheritdoc
205		*/
206	32	public function executeNone(string $query): int
207		{
208	32	$this->logQuery($query);
209
210	32	return parent::executeNone($query);
211		}
212
213		//--------------------------------------------------------------------------------------------------------------------
214		/**
215		* @inheritdoc
216		*/
217		public function executeRow0(string $query): ?array
218		{
219		$this->logQuery($query);
220
221		return parent::executeRow0($query);
222		}
223
224		//--------------------------------------------------------------------------------------------------------------------
225		/**
226		* @inheritdoc
227		*/
228	25	public function executeRow1(string $query): array
229		{
230	25	$this->logQuery($query);
231
232	25	return parent::executeRow1($query);
233		}
234
235		//--------------------------------------------------------------------------------------------------------------------
236		/**
237		* @inheritdoc
238		*/
239	33	public function executeRows(string $query): array
240		{
241	33	$this->logQuery($query);
242
243	33	return parent::executeRows($query);
244		}
245
246		//--------------------------------------------------------------------------------------------------------------------
247		/**
248		* @inheritdoc
249		*/
250		public function executeSingleton0(string $query)
251		{
252		$this->logQuery($query);
253
254		return parent::executeSingleton0($query);
255		}
256
257		//--------------------------------------------------------------------------------------------------------------------
258		/**
259		* @inheritdoc
260		*/
261	3	public function executeSingleton1(string $query)
262		{
263	3	$this->logQuery($query);
264
265	3	return parent::executeSingleton1($query);
266		}
267
268		//--------------------------------------------------------------------------------------------------------------------
269		/**
270		* @inheritdoc
271		*/
272		public function executeTable(string $query): int
273		{
274		$this->logQuery($query);
275
276		return parent::executeTable($query);
277		}
278
279		//--------------------------------------------------------------------------------------------------------------------
280		/**
281		* Selects metadata of all columns of table.
282		*
283		* @param string $schemaName The name of the table schema.
284		* @param string $tableName The name of the table.
285		*
286		* @return array[]
287		*/
288	28	public function getTableColumns(string $schemaName, string $tableName): array
289		{
290		// When a column has no default prior to MariaDB 10.2.7 column_default is null from MariaDB 10.2.7
291		// column_default = 'NULL' (string(4)).
292	28	$sql = sprintf("
293		select COLUMN_NAME as column_name
294		, COLUMN_TYPE as column_type
295		, ifnull(COLUMN_DEFAULT, 'NULL') as column_default
296		, IS_NULLABLE as is_nullable
297		, CHARACTER_SET_NAME as character_set_name
298		, COLLATION_NAME as collation_name
299		from information_schema.COLUMNS
300		where TABLE_SCHEMA = %s
301		and TABLE_NAME = %s
302		order by ORDINAL_POSITION",
303	28	$this->quoteString($schemaName),
304	28	$this->quoteString($tableName));
305
306	28	return $this->executeRows($sql);
307		}
308
309		//--------------------------------------------------------------------------------------------------------------------
310		/**
311		* Selects table engine, character_set_name and table_collation.
312		*
313		* @param string $schemaName The name of the table schema.
314		* @param string $tableName The name of the table.
315		*
316		* @return array
317		*/
318	25	public function getTableOptions(string $schemaName, string $tableName): array
319		{
320	25	$sql = sprintf('
321		select t1.TABLE_SCHEMA as table_schema
322		, t1.TABLE_NAME as table_name
323		, t1.TABLE_COLLATION as table_collation
324		, t1.ENGINE as engine
325		, t2.CHARACTER_SET_NAME as character_set_name
326		from information_schema.TABLES t1
327		inner join information_schema.COLLATION_CHARACTER_SET_APPLICABILITY t2 on t2.COLLATION_NAME = t1.TABLE_COLLATION
328		where t1.TABLE_SCHEMA = %s
329		and t1.TABLE_NAME = %s',
330	25	$this->quoteString($schemaName),
331	25	$this->quoteString($tableName));
332
333	25	return $this->executeRow1($sql);
334		}
335
336		//--------------------------------------------------------------------------------------------------------------------
337		/**
338		* Selects all triggers on a table.
339		*
340		* @param string $schemaName The name of the table schema.
341		* @param string $tableName The name of the table.
342		*
343		* @return array[]
344		*/
345	28	public function getTableTriggers(string $schemaName, string $tableName): array
346		{
347	28	$sql = sprintf('
348		select TRIGGER_NAME as trigger_name
349		from information_schema.TRIGGERS
350		where TRIGGER_SCHEMA = %s
351		and EVENT_OBJECT_TABLE = %s
352		order by Trigger_Name',
353	28	$this->quoteString($schemaName),
354	28	$this->quoteString($tableName));
355
356	28	return $this->executeRows($sql);
357		}
358
359		//--------------------------------------------------------------------------------------------------------------------
360		/**
361		* Selects all table names in a schema.
362		*
363		* @param string $schemaName The name of the schema.
364		*
365		* @return array[]
366		*/
367	29	public function getTablesNames(string $schemaName): array
368		{
369	29	$sql = sprintf("
370		select TABLE_NAME as table_name
371		from information_schema.TABLES
372		where TABLE_SCHEMA = %s
373		and TABLE_TYPE = 'BASE TABLE'
374	29	order by TABLE_NAME", $this->quoteString($schemaName));
375
376	29	return $this->executeRows($sql);
377		}
378
379		//--------------------------------------------------------------------------------------------------------------------
380		/**
381		* Selects all triggers in a schema
382		*
383		* @param string $schemaName The name of the table schema.
384		*
385		* @return array[]
386		*/
387		public function getTriggers(string $schemaName): array
388		{
389		$sql = sprintf('
390		select EVENT_OBJECT_TABLE as table_name
391		, TRIGGER_NAME as trigger_name
392		from information_schema.TRIGGERS
393		where TRIGGER_SCHEMA = %s
394		order by EVENT_OBJECT_TABLE
395		, TRIGGER_NAME',
396		$this->quoteString($schemaName));
397
398		return $this->executeRows($sql);
399		}
400
401		//--------------------------------------------------------------------------------------------------------------------
402		/**
403		* Acquires a write lock on a table.
404		*
405		* @param string $schemaName The schema of the table.
406		* @param string $tableName The table name.
407		*/
408	28	public function lockTable(string $schemaName, string $tableName): void
409		{
410	28	$sql = sprintf('lock tables `%s`.`%s` write', $schemaName, $tableName);
411
412	28	$this->executeNone($sql);
413	28	}
414
415		//--------------------------------------------------------------------------------------------------------------------
416		/**
417		* Resolves the canonical column types of the additional audit columns.
418		*
419		* @param string $auditSchema The name of the audit schema.
420		* @param array[] $additionalAuditColumns The metadata of the additional audit columns.
421		*
422		* @return TableColumnsMetadata
423		*/
424	29	public function resolveCanonicalAdditionalAuditColumns(string $auditSchema,
425		array $additionalAuditColumns): TableColumnsMetadata
426		{
427	29	if (empty($additionalAuditColumns))
428		{
429	10	return new TableColumnsMetadata([], 'AuditColumnMetadata');
430		}
431
432	19	$tableName = '_TMP_'.uniqid();
433	19	$this->createTemporaryTable($auditSchema, $tableName, $additionalAuditColumns);
434	19	$columns = AuditDataLayer::$dl->getTableColumns($auditSchema, $tableName);
435	19	$this->dropTemporaryTable($auditSchema, $tableName);
436
437	19	foreach ($additionalAuditColumns as $column)
438		{
439	19	$key = RowSetHelper::findInRowSet($columns, 'column_name', $column['column_name']);
440
441	19	if (isset($column['value_type']))
442		{
443	19	$columns[$key]['value_type'] = $column['value_type'];
444		}
445	19	if (isset($column['expression']))
446		{
447	10	$columns[$key]['expression'] = $column['expression'];
448		}
449		}
450
451	19	return new TableColumnsMetadata($columns, 'AuditColumnMetadata');
452		}
453
454		//--------------------------------------------------------------------------------------------------------------------
455		/**
456		* Releases all table locks.
457		*/
458	28	public function unlockTables(): void
459		{
460	28	$sql = 'unlock tables';
461
462	28	$this->executeNone($sql);
463	28	}
464
465		//--------------------------------------------------------------------------------------------------------------------
466		/**
467		* Logs the query on the console.
468		*
469		* @param string $query The query.
470		*/
471	33	private function logQuery(string $query): void
472		{
473	33	$query = trim($query);
474
475	33	if (strpos($query, "\n")!==false)
476		{
477		// Query is a multi line query.
478	33	$this->io->logVeryVerbose('Executing query:');
479	33	$this->io->logVeryVerbose('<sql>%s</sql>', $query);
480		}
481		else
482		{
483		// Query is a single line query.
484	32	$this->io->logVeryVerbose('Executing query: <sql>%s</sql>', $query);
485		}
486	33	}
487
488		//--------------------------------------------------------------------------------------------------------------------
489		}
490
491		//----------------------------------------------------------------------------------------------------------------------
492

SetBased / php-audit

Push — master ( a46189...4fb833 )

AuditDataLayer::getTriggers() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like