AuditDataLayer::getTableColumns() - Code Metrics - SetBased/php-audit - Measure and Improve Code Quality continuously with Scrutinizer

AuditDataLayer::getTableColumns() A
last analyzed 2025-06-07 10:40 UTC

↳ Parent: AuditDataLayer

Complexity

Conditions	1
Paths	1

Size

Total Lines	19
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	1

Importance

Changes	1
Bugs	0	Features	0

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

<?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 AuditDataLayer $dl;

  /**
   * The Output decorator.
   *
   * @var AuditStyle
   */
  private AuditStyle $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 name of the MySQL user defined variable for skipping
   *                                                     triggers.
   * @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): mixed
  {
    $this->logQuery($query);

    return parent::executeSingleton0($query);
  }

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

SetBased / php-audit

AuditDataLayer::getTableColumns() A last analyzed 2025-06-07 10:40 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

AuditDataLayer::getTableColumns() A
last analyzed 2025-06-07 10:40 UTC