MySqlMetaDataLayer::tableColumn() - Code Metrics - Inspection of "Fix data type from a table column for PL/SQL code..." - DatabaseStratum/php-stratum-mysql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 304ce4...56185b )

by P.R.

created 2020-11-09 08:18 UTC

MySqlMetaDataLayer::tableColumn() A

↳ Parent: MySqlMetaDataLayer

Complexity

Conditions	1
Paths	1

Size

Total Lines	19
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes

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

<?php
declare(strict_types=1);

namespace SetBased\Stratum\MySql;

use SetBased\Stratum\Backend\StratumStyle;
use SetBased\Stratum\Middle\Exception\ResultException;
use SetBased\Stratum\MySql\Exception\MySqlQueryErrorException;

/**
 * Data layer for retrieving metadata and loading stored routines.
 */
class MySqlMetaDataLayer
{
  //--------------------------------------------------------------------------------------------------------------------
  /**
   * The connection to the MySQL instance.
   *
   * @var MySqlDataLayer
   */
  private $dl;

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * MySqlMetaDataLayer constructor.
   *
   * @param MySqlDataLayer $dl The connection to the MySQL instance.
   * @param StratumStyle   $io The Output decorator.
   */
  public function __construct(MySqlDataLayer $dl, StratumStyle $io)
  {
    $this->dl = $dl;
    $this->io = $io;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects the details of all character sets.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function allCharacterSets(): array
  {
    $sql = "
select CHARACTER_SET_NAME  as  character_set_name
,      MAXLEN              as  maxlen
from   information_schema.CHARACTER_SETS
order by CHARACTER_SET_NAME";

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects metadata of tables with a label column.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function allLabelTables(): array
  {
    $sql = "
select t1.TABLE_NAME   as  table_name
,      t1.COLUMN_NAME  as  id
,      t2.COLUMN_NAME  as  label
from       information_schema.COLUMNS t1
inner join information_schema.COLUMNS t2 on t1.table_name = t2.table_name
where t1.table_schema = database()
and   t1.extra        = 'auto_increment'
and   t2.table_schema = database()
and   t2.column_name like '%%\\_label'";

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects all routines in the current schema.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function allRoutines(): array
  {
    $sql = "
select ROUTINE_NAME          as  routine_name                            
,      ROUTINE_TYPE          as  routine_type           
,      SQL_MODE              as  sql_mode       
,      CHARACTER_SET_CLIENT  as  character_set_client                   
,      COLLATION_CONNECTION  as  collation_connection                   
from  information_schema.ROUTINES
where ROUTINE_SCHEMA = database()
and   ROUTINE_TYPE in ('PROCEDURE', 'FUNCTION')
order by routine_name";

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects metadata of all columns of all tables.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function allTableColumns(): array
  {
    $sql = "
(
  select TABLE_NAME                as  table_name                                                       
  ,      COLUMN_NAME               as  column_name                                 
  ,      COLUMN_TYPE               as  column_type                                 
  ,      DATA_TYPE                 as  data_type                                 
  ,      CHARACTER_MAXIMUM_LENGTH  as  character_maximum_length                                 
  ,      CHARACTER_SET_NAME        as  character_set_name                                 
  ,      COLLATION_NAME            as  collation_name                                 
  ,      NUMERIC_PRECISION         as  numeric_precision                                 
  ,      NUMERIC_SCALE             as  numeric_scale                                 
  from   information_schema.COLUMNS
  where  TABLE_SCHEMA = database()
  and    TABLE_NAME  rlike '^[a-zA-Z0-9_]*$'
  and    COLUMN_NAME rlike '^[a-zA-Z0-9_]*$'
  order by TABLE_NAME
  ,        ORDINAL_POSITION
)

union all

(
  select concat(TABLE_SCHEMA,'.',TABLE_NAME)  as  table_name                                               
  ,      COLUMN_NAME                          as  column_name                     
  ,      COLUMN_TYPE                          as  column_type                     
  ,      DATA_TYPE                            as  data_type                     
  ,      CHARACTER_MAXIMUM_LENGTH             as  character_maximum_length                     
  ,      CHARACTER_SET_NAME                   as  character_set_name                     
  ,      COLLATION_NAME                       as  collation_name                     
  ,      NUMERIC_PRECISION                    as  numeric_precision                     
  ,      NUMERIC_SCALE                        as  numeric_scale                     
  from   information_schema.COLUMNS
  where  TABLE_NAME  rlike '^[a-zA-Z0-9_]*$'
  and    COLUMN_NAME rlike '^[a-zA-Z0-9_]*$'
  order by TABLE_SCHEMA
  ,        TABLE_NAME
  ,        ORDINAL_POSITION
)
";

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects all table names in a schema.
   *
   * @param string $schemaName The name of the schema.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function allTablesNames(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->dl->quoteString($schemaName));

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Class a stored procedure without arguments.
   *
   * @param string $procedureName The name of the procedure.
   *
   * @throws MySqlQueryErrorException
   */
  public function callProcedure(string $procedureName): void
  {
    $sql = sprintf('call %s()', $procedureName);

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Checks if a table exists in the current schema.
   *
   * @param string $tableName The name of the table.
   *
   * @return bool
   *
   * @throws MySqlQueryErrorException
   * @throws ResultException
   */
  public function checkTableExists(string $tableName): bool
  {
    $sql = sprintf('
select 1
from   information_schema.TABLES
where table_schema = database()
and   table_name   = %s', $this->dl->quoteString($tableName));

    return !empty($this->executeSingleton0($sql));
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Describes a table.
   *
   * @param string $tableName The table name.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function describeTable(string $tableName): array
  {
    $sql = sprintf('describe `%s`', $tableName);

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Closes the connection to the MySQL instance, if connected.
   */
  public function disconnect(): void
  {
    if ($this->dl!==null)
    {
      $this->dl->disconnect();
      $this->dl = null;
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Drops a routine if it exists.
   *
   * @param string $routineType The type of the routine (function of procedure).
   * @param string $routineName The name of the routine.
   *
   * @throws MySqlQueryErrorException
   */
  public function dropRoutine(string $routineType, string $routineName): void
  {
    $sql = sprintf('drop %s if exists `%s`', $routineType, $routineName);

    $this->executeNone($sql);
  }

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

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * @param string $sql The SQL statement.
   *
   * @return int The number of affected rows (if any).
   *
   * @throws MySqlQueryErrorException
   */
  public function executeNone(string $sql): int
  {
    $this->logQuery($sql);

    return $this->dl->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Executes a query that returns 0 or 1 row.
   * Throws an exception if the query selects 2 or more rows.
   *
   * @param string $sql The SQL statement.
   *
   * @return array|null The selected row.
   *
   * @throws MySqlQueryErrorException
   * @throws ResultException
   */
  public function executeRow0(string $sql): ?array
  {
    $this->logQuery($sql);

    return $this->dl->executeRow0($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Executes a query that returns 1 and only 1 row.
   * Throws an exception if the query selects none, 2 or more rows.
   *
   * @param string $sql The SQL statement.
   *
   * @return array The selected row.
   *
   * @throws MySqlQueryErrorException
   * @throws ResultException
   */
  public function executeRow1(string $sql): array
  {
    $this->logQuery($sql);

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Executes a query that returns 0 or more rows.
   *
   * @param string $sql The SQL statement.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function executeRows(string $sql): array
  {
    $this->logQuery($sql);

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Executes a query that returns 0 or 1 row.
   * Throws an exception if the query selects 2 or more rows.
   *
   * @param string $sql The SQL statement.
   *
   * @return mixed The selected row.
   *
   * @throws MySqlQueryErrorException
   * @throws ResultException
   */
  public function executeSingleton0(string $sql)
  {
    $this->logQuery($sql);

    return $this->dl->executeSingleton0($sql);
  }
  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Executes a query that returns 1 and only 1 row with 1 column.
   * Throws an exception if the query selects none, 2 or more rows.
   *
   * @param string $sql The SQL statement.
   *
   * @return mixed The selected row.
   *
   * @throws MySqlQueryErrorException
   * @throws ResultException
   */
  public function executeSingleton1(string $sql)
  {
    $this->logQuery($sql);

    return $this->dl->executeSingleton1($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects the SQL mode in the order as preferred by MySQL.
   *
   * @return string
   *
   * @throws MySqlQueryErrorException
   * @throws ResultException
   */
  public function getCanonicalSqlMode(): string
  {
    $sql = 'select @@sql_mode';

    return (string)$this->executeSingleton1($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects all labels from a table with labels.
   *
   * @param string $tableName       The table name.
   * @param string $idColumnName    The name of the auto increment column.
   * @param string $labelColumnName The name of the column with labels.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function labelsFromTable(string $tableName, string $idColumnName, string $labelColumnName): array
  {
    $sql = "
select `%s`  id
,      `%s`  label
from   `%s`
where   nullif(`%s`,'') is not null";

    $sql = sprintf($sql, $idColumnName, $labelColumnName, $tableName, $labelColumnName);

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Loads a stored routine.
   *
   * @param string $routineSource The source of the routine.
   *
   * @throws MySqlQueryErrorException
   */
  public function loadRoutine(string $routineSource): void
  {
    $this->executeNone($routineSource);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Escapes special characters in a string such that it can be safely used in SQL statements.
   *
   * Wrapper around [mysqli::real_escape_string](http://php.net/manual/mysqli.real-escape-string.php).
   *
   * @param string $string The string.
   *
   * @return string
   */
  public function realEscapeString(string $string): string
  {
    return $this->dl->realEscapeString($string);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects the parameters of a stored routine.
   *
   * @param string $routineName The name of the routine.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function routineParameters(string $routineName): array
  {
    $sql = sprintf("
select t2.PARAMETER_NAME      as  parameter_name                            
,      t2.DATA_TYPE           as  data_type             
,      t2.NUMERIC_PRECISION   as  numeric_precision                     
,      t2.NUMERIC_SCALE       as  numeric_scale                 
,      t2.CHARACTER_SET_NAME  as  character_set_name                      
,      t2.COLLATION_NAME      as  collation_name                  
,      t2.DTD_IDENTIFIER      as  dtd_identifier                  
from information_schema.ROUTINES   t1
join information_schema.PARAMETERS t2  on  t2.SPECIFIC_SCHEMA = t1.ROUTINE_SCHEMA and
                                           t2.SPECIFIC_NAME   = t1.ROUTINE_NAME and
                                           t2.PARAMETER_MODE  is not null
where t1.ROUTINE_SCHEMA = database()
and   t1.ROUTINE_NAME   = '%s'", $routineName);

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Sets the default character set and collate.
   *
   * @param string $characterSet The character set.
   * @param string $collate      The collate.
   *
   * @throws MySqlQueryErrorException
   */
  public function setCharacterSet(string $characterSet, string $collate): void
  {
    $sql = sprintf('set names %s collate %s', $this->dl->quoteString($characterSet), $this->dl->quoteString($collate));

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Sets the SQL mode.
   *
   * @param string $sqlMode The SQL mode.
   *
   * @throws MySqlQueryErrorException
   */
  public function setSqlMode(string $sqlMode): void
  {
    $sql = sprintf('set sql_mode = %s', $this->dl->quoteString($sqlMode));

    $this->executeNone($sql);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects metadata of a column of table.
   *
   * @param string|null $schemaName The name of the table schema. If null the current schema.
   * @param string      $tableName  The name of the table.
   * @param string      $columnName The name of the column.
   *
   * @return array
   *
   * @throws MySqlQueryErrorException
   */
  public function tableColumn(?string $schemaName, string $tableName, string $columnName): array
  {
    $sql = sprintf('
select COLUMN_NAME         as  column_name
,      COLUMN_TYPE         as  column_type
,      DATA_TYPE           as  data_type
,      NUMERIC_PRECISION   as  numeric_precision
,      NUMERIC_SCALE       as  numeric_scale
,      CHARACTER_SET_NAME  as  character_set_name
,      COLLATION_NAME      as  collation_name
from   information_schema.COLUMNS
where  TABLE_SCHEMA = ifnull(%s, database())
and    TABLE_NAME   = %s
and    COLUMN_NAME  = %s',
                   $this->dl->quoteString($schemaName),
                   $this->dl->quoteString($tableName),
                   $this->dl->quoteString($columnName));

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * 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[]
   *
   * @throws MySqlQueryErrorException
   */
  public function tableColumns(string $schemaName, string $tableName): array
  {
    $sql = sprintf('
select COLUMN_NAME         as  column_name
,      COLUMN_TYPE         as  column_type
,      IS_NULLABLE         as  is_nullable
,      CHARACTER_SET_NAME  as  character_set_name
,      COLLATION_NAME      as  collation_name
,      EXTRA               as  extra
from   information_schema.COLUMNS
where  TABLE_SCHEMA = %s
and    TABLE_NAME   = %s
order by ORDINAL_POSITION',
                   $this->dl->quoteString($schemaName),
                   $this->dl->quoteString($tableName));

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects the primary key from a table (if any).
   *
   * @param string $schemaName The name of the table schema.
   * @param string $tableName  The name of the table.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function tablePrimaryKey(string $schemaName, string $tableName): array
  {
    $sql = sprintf('
show index from `%s`.`%s`
where Key_name = \'PRIMARY\'',
                   $schemaName,
                   $tableName);

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

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects all unique keys from table.
   *
   * @param string $schemaName The name of the table schema.
   * @param string $tableName  The name of the table.
   *
   * @return array[]
   *
   * @throws MySqlQueryErrorException
   */
  public function tableUniqueIndexes(string $schemaName, string $tableName): array
  {
    $sql = sprintf('
show index from `%s`.`%s`
where Non_unique = 0',
                   $schemaName,
                   $tableName);

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

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

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

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

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


1		<?php
2		declare(strict_types=1);
3
4		namespace SetBased\Stratum\MySql;
5
6		use SetBased\Stratum\Backend\StratumStyle;
7		use SetBased\Stratum\Middle\Exception\ResultException;
8		use SetBased\Stratum\MySql\Exception\MySqlQueryErrorException;
9
10		/**
11		* Data layer for retrieving metadata and loading stored routines.
12		*/
13		class MySqlMetaDataLayer
14		{
15		//--------------------------------------------------------------------------------------------------------------------
16		/**
17		* The connection to the MySQL instance.
18		*
19		* @var MySqlDataLayer
20		*/
21		private $dl;
22
23		/**
24		* The Output decorator.
25		*
26		* @var StratumStyle
27		*/
28		private $io;
29
30		//--------------------------------------------------------------------------------------------------------------------
31		/**
32		* MySqlMetaDataLayer constructor.
33		*
34		* @param MySqlDataLayer $dl The connection to the MySQL instance.
35		* @param StratumStyle $io The Output decorator.
36		*/
37	1	public function __construct(MySqlDataLayer $dl, StratumStyle $io)
38		{
39	1	$this->dl = $dl;
40	1	$this->io = $io;
41	1	}
42
43		//--------------------------------------------------------------------------------------------------------------------
44		/**
45		* Selects the details of all character sets.
46		*
47		* @return array[]
48		*
49		* @throws MySqlQueryErrorException
50		*/
51	1	public function allCharacterSets(): array
52		{
53	1	$sql = "
54		select CHARACTER_SET_NAME as character_set_name
55		, MAXLEN as maxlen
56		from information_schema.CHARACTER_SETS
57		order by CHARACTER_SET_NAME";
58
59	1	return $this->executeRows($sql);
60		}
61
62		//--------------------------------------------------------------------------------------------------------------------
63		/**
64		* Selects metadata of tables with a label column.
65		*
66		* @return array[]
67		*
68		* @throws MySqlQueryErrorException
69		*/
70	1	public function allLabelTables(): array
71		{
72	1	$sql = "
73		select t1.TABLE_NAME as table_name
74		, t1.COLUMN_NAME as id
75		, t2.COLUMN_NAME as label
76		from information_schema.COLUMNS t1
77		inner join information_schema.COLUMNS t2 on t1.table_name = t2.table_name
78		where t1.table_schema = database()
79		and t1.extra = 'auto_increment'
80		and t2.table_schema = database()
81		and t2.column_name like '%%\\_label'";
82
83	1	return $this->executeRows($sql);
84		}
85
86		//--------------------------------------------------------------------------------------------------------------------
87		/**
88		* Selects all routines in the current schema.
89		*
90		* @return array[]
91		*
92		* @throws MySqlQueryErrorException
93		*/
94	1	public function allRoutines(): array
95		{
96	1	$sql = "
97		select ROUTINE_NAME as routine_name
98		, ROUTINE_TYPE as routine_type
99		, SQL_MODE as sql_mode
100		, CHARACTER_SET_CLIENT as character_set_client
101		, COLLATION_CONNECTION as collation_connection
102		from information_schema.ROUTINES
103		where ROUTINE_SCHEMA = database()
104		and ROUTINE_TYPE in ('PROCEDURE', 'FUNCTION')
105		order by routine_name";
106
107	1	return $this->executeRows($sql);
108		}
109
110		//--------------------------------------------------------------------------------------------------------------------
111		/**
112		* Selects metadata of all columns of all tables.
113		*
114		* @return array[]
115		*
116		* @throws MySqlQueryErrorException
117		*/
118	1	public function allTableColumns(): array
119		{
120	1	$sql = "
121		(
122		select TABLE_NAME as table_name
123		, COLUMN_NAME as column_name
124		, COLUMN_TYPE as column_type
125		, DATA_TYPE as data_type
126		, CHARACTER_MAXIMUM_LENGTH as character_maximum_length
127		, CHARACTER_SET_NAME as character_set_name
128		, COLLATION_NAME as collation_name
129		, NUMERIC_PRECISION as numeric_precision
130		, NUMERIC_SCALE as numeric_scale
131		from information_schema.COLUMNS
132		where TABLE_SCHEMA = database()
133		and TABLE_NAME rlike '^[a-zA-Z0-9_]*$'
134		and COLUMN_NAME rlike '^[a-zA-Z0-9_]*$'
135		order by TABLE_NAME
136		, ORDINAL_POSITION
137		)
138
139		union all
140
141		(
142		select concat(TABLE_SCHEMA,'.',TABLE_NAME) as table_name
143		, COLUMN_NAME as column_name
144		, COLUMN_TYPE as column_type
145		, DATA_TYPE as data_type
146		, CHARACTER_MAXIMUM_LENGTH as character_maximum_length
147		, CHARACTER_SET_NAME as character_set_name
148		, COLLATION_NAME as collation_name
149		, NUMERIC_PRECISION as numeric_precision
150		, NUMERIC_SCALE as numeric_scale
151		from information_schema.COLUMNS
152		where TABLE_NAME rlike '^[a-zA-Z0-9_]*$'
153		and COLUMN_NAME rlike '^[a-zA-Z0-9_]*$'
154		order by TABLE_SCHEMA
155		, TABLE_NAME
156		, ORDINAL_POSITION
157		)
158		";
159
160	1	return $this->executeRows($sql);
161		}
162
163		//--------------------------------------------------------------------------------------------------------------------
164		/**
165		* Selects all table names in a schema.
166		*
167		* @param string $schemaName The name of the schema.
168		*
169		* @return array[]
170		*
171		* @throws MySqlQueryErrorException
172		*/
173		public function allTablesNames(string $schemaName): array
174		{
175		$sql = sprintf("
176		select TABLE_NAME as table_name
177		from information_schema.TABLES
178		where TABLE_SCHEMA = %s
179		and TABLE_TYPE = 'BASE TABLE'
180		order by TABLE_NAME", $this->dl->quoteString($schemaName));
181
182		return $this->executeRows($sql);
183		}
184
185		//--------------------------------------------------------------------------------------------------------------------
186		/**
187		* Class a stored procedure without arguments.
188		*
189		* @param string $procedureName The name of the procedure.
190		*
191		* @throws MySqlQueryErrorException
192		*/
193	1	public function callProcedure(string $procedureName): void
194		{
195	1	$sql = sprintf('call %s()', $procedureName);
196
197	1	$this->executeNone($sql);
198	1	}
199
200		//--------------------------------------------------------------------------------------------------------------------
201		/**
202		* Checks if a table exists in the current schema.
203		*
204		* @param string $tableName The name of the table.
205		*
206		* @return bool
207		*
208		* @throws MySqlQueryErrorException
209		* @throws ResultException
210		*/
211	1	public function checkTableExists(string $tableName): bool
212		{
213	1	$sql = sprintf('
214		select 1
215		from information_schema.TABLES
216		where table_schema = database()
217	1	and table_name = %s', $this->dl->quoteString($tableName));
218
219	1	return !empty($this->executeSingleton0($sql));
220		}
221
222		//--------------------------------------------------------------------------------------------------------------------
223		/**
224		* Describes a table.
225		*
226		* @param string $tableName The table name.
227		*
228		* @return array[]
229		*
230		* @throws MySqlQueryErrorException
231		*/
232	1	public function describeTable(string $tableName): array
233		{
234	1	$sql = sprintf('describe `%s`', $tableName);
235
236	1	return $this->executeRows($sql);
237		}
238
239		//--------------------------------------------------------------------------------------------------------------------
240		/**
241		* Closes the connection to the MySQL instance, if connected.
242		*/
243	1	public function disconnect(): void
244		{
245	1	if ($this->dl!==null)
246		{
247	1	$this->dl->disconnect();
248	1	$this->dl = null;
249		}
250	1	}
251
252		//--------------------------------------------------------------------------------------------------------------------
253		/**
254		* Drops a routine if it exists.
255		*
256		* @param string $routineType The type of the routine (function of procedure).
257		* @param string $routineName The name of the routine.
258		*
259		* @throws MySqlQueryErrorException
260		*/
261		public function dropRoutine(string $routineType, string $routineName): void
262		{
263		$sql = sprintf('drop %s if exists `%s`', $routineType, $routineName);
264
265		$this->executeNone($sql);
266		}
267
268		//--------------------------------------------------------------------------------------------------------------------
269		/**
270		* Drops a temporary table.
271		*
272		* @param string $tableName the name of the temporary table.
273		*
274		* @throws MySqlQueryErrorException
275		*/
276	1	public function dropTemporaryTable(string $tableName): void
277		{
278	1	$sql = sprintf('drop temporary table `%s`', $tableName);
279
280	1	$this->executeNone($sql);
281	1	}
282
283		//--------------------------------------------------------------------------------------------------------------------
284		/**
285		* @param string $sql The SQL statement.
286		*
287		* @return int The number of affected rows (if any).
288		*
289		* @throws MySqlQueryErrorException
290		*/
291	1	public function executeNone(string $sql): int
292		{
293	1	$this->logQuery($sql);
294
295	1	return $this->dl->executeNone($sql);
296		}
297
298		//--------------------------------------------------------------------------------------------------------------------
299		/**
300		* Executes a query that returns 0 or 1 row.
301		* Throws an exception if the query selects 2 or more rows.
302		*
303		* @param string $sql The SQL statement.
304		*
305		* @return array\|null The selected row.
306		*
307		* @throws MySqlQueryErrorException
308		* @throws ResultException
309		*/
310		public function executeRow0(string $sql): ?array
311		{
312		$this->logQuery($sql);
313
314		return $this->dl->executeRow0($sql);
315		}
316
317		//--------------------------------------------------------------------------------------------------------------------
318		/**
319		* Executes a query that returns 1 and only 1 row.
320		* Throws an exception if the query selects none, 2 or more rows.
321		*
322		* @param string $sql The SQL statement.
323		*
324		* @return array The selected row.
325		*
326		* @throws MySqlQueryErrorException
327		* @throws ResultException
328		*/
329		public function executeRow1(string $sql): array
330		{
331		$this->logQuery($sql);
332
333		return $this->dl->executeRow1($sql);
334		}
335
336		//--------------------------------------------------------------------------------------------------------------------
337		/**
338		* Executes a query that returns 0 or more rows.
339		*
340		* @param string $sql The SQL statement.
341		*
342		* @return array[]
343		*
344		* @throws MySqlQueryErrorException
345		*/
346	1	public function executeRows(string $sql): array
347		{
348	1	$this->logQuery($sql);
349
350	1	return $this->dl->executeRows($sql);
351		}
352
353		//--------------------------------------------------------------------------------------------------------------------
354		/**
355		* Executes a query that returns 0 or 1 row.
356		* Throws an exception if the query selects 2 or more rows.
357		*
358		* @param string $sql The SQL statement.
359		*
360		* @return mixed The selected row.
361		*
362		* @throws MySqlQueryErrorException
363		* @throws ResultException
364		*/
365	1	public function executeSingleton0(string $sql)
366		{
367	1	$this->logQuery($sql);
368
369	1	return $this->dl->executeSingleton0($sql);
370		}
371		//--------------------------------------------------------------------------------------------------------------------
372		/**
373		* Executes a query that returns 1 and only 1 row with 1 column.
374		* Throws an exception if the query selects none, 2 or more rows.
375		*
376		* @param string $sql The SQL statement.
377		*
378		* @return mixed The selected row.
379		*
380		* @throws MySqlQueryErrorException
381		* @throws ResultException
382		*/
383	1	public function executeSingleton1(string $sql)
384		{
385	1	$this->logQuery($sql);
386
387	1	return $this->dl->executeSingleton1($sql);
388		}
389
390		//--------------------------------------------------------------------------------------------------------------------
391		/**
392		* Selects the SQL mode in the order as preferred by MySQL.
393		*
394		* @return string
395		*
396		* @throws MySqlQueryErrorException
397		* @throws ResultException
398		*/
399	1	public function getCanonicalSqlMode(): string
400		{
401	1	$sql = 'select @@sql_mode';
402
403	1	return (string)$this->executeSingleton1($sql);
404		}
405
406		//--------------------------------------------------------------------------------------------------------------------
407		/**
408		* Selects all labels from a table with labels.
409		*
410		* @param string $tableName The table name.
411		* @param string $idColumnName The name of the auto increment column.
412		* @param string $labelColumnName The name of the column with labels.
413		*
414		* @return array[]
415		*
416		* @throws MySqlQueryErrorException
417		*/
418	1	public function labelsFromTable(string $tableName, string $idColumnName, string $labelColumnName): array
419		{
420	1	$sql = "
421		select `%s` id
422		, `%s` label
423		from `%s`
424		where nullif(`%s`,'') is not null";
425
426	1	$sql = sprintf($sql, $idColumnName, $labelColumnName, $tableName, $labelColumnName);
427
428	1	return $this->executeRows($sql);
429		}
430
431		//--------------------------------------------------------------------------------------------------------------------
432		/**
433		* Loads a stored routine.
434		*
435		* @param string $routineSource The source of the routine.
436		*
437		* @throws MySqlQueryErrorException
438		*/
439	1	public function loadRoutine(string $routineSource): void
440		{
441	1	$this->executeNone($routineSource);
442	1	}
443
444		//--------------------------------------------------------------------------------------------------------------------
445		/**
446		* Escapes special characters in a string such that it can be safely used in SQL statements.
447		*
448		* Wrapper around [mysqli::real_escape_string](http://php.net/manual/mysqli.real-escape-string.php).
449		*
450		* @param string $string The string.
451		*
452		* @return string
453		*/
454	1	public function realEscapeString(string $string): string
455		{
456	1	return $this->dl->realEscapeString($string);
457		}
458
459		//--------------------------------------------------------------------------------------------------------------------
460		/**
461		* Selects the parameters of a stored routine.
462		*
463		* @param string $routineName The name of the routine.
464		*
465		* @return array[]
466		*
467		* @throws MySqlQueryErrorException
468		*/
469	1	public function routineParameters(string $routineName): array
470		{
471	1	$sql = sprintf("
472		select t2.PARAMETER_NAME as parameter_name
473		, t2.DATA_TYPE as data_type
474		, t2.NUMERIC_PRECISION as numeric_precision
475		, t2.NUMERIC_SCALE as numeric_scale
476		, t2.CHARACTER_SET_NAME as character_set_name
477		, t2.COLLATION_NAME as collation_name
478		, t2.DTD_IDENTIFIER as dtd_identifier
479		from information_schema.ROUTINES t1
480		join information_schema.PARAMETERS t2 on t2.SPECIFIC_SCHEMA = t1.ROUTINE_SCHEMA and
481		t2.SPECIFIC_NAME = t1.ROUTINE_NAME and
482		t2.PARAMETER_MODE is not null
483		where t1.ROUTINE_SCHEMA = database()
484		and t1.ROUTINE_NAME = '%s'", $routineName);
485
486	1	return $this->executeRows($sql);
487		}
488
489		//--------------------------------------------------------------------------------------------------------------------
490		/**
491		* Sets the default character set and collate.
492		*
493		* @param string $characterSet The character set.
494		* @param string $collate The collate.
495		*
496		* @throws MySqlQueryErrorException
497		*/
498	1	public function setCharacterSet(string $characterSet, string $collate): void
499		{
500	1	$sql = sprintf('set names %s collate %s', $this->dl->quoteString($characterSet), $this->dl->quoteString($collate));
501
502	1	$this->executeNone($sql);
503	1	}
504
505		//--------------------------------------------------------------------------------------------------------------------
506		/**
507		* Sets the SQL mode.
508		*
509		* @param string $sqlMode The SQL mode.
510		*
511		* @throws MySqlQueryErrorException
512		*/
513	1	public function setSqlMode(string $sqlMode): void
514		{
515	1	$sql = sprintf('set sql_mode = %s', $this->dl->quoteString($sqlMode));
516
517	1	$this->executeNone($sql);
518	1	}
519
520		//--------------------------------------------------------------------------------------------------------------------
521		/**
522		* Selects metadata of a column of table.
523		*
524		* @param string\|null $schemaName The name of the table schema. If null the current schema.
525		* @param string $tableName The name of the table.
526		* @param string $columnName The name of the column.
527		*
528		* @return array
529		*
530		* @throws MySqlQueryErrorException
531		*/
532		public function tableColumn(?string $schemaName, string $tableName, string $columnName): array
533		{
534		$sql = sprintf('
535		select COLUMN_NAME as column_name
536		, COLUMN_TYPE as column_type
537		, DATA_TYPE as data_type
538		, NUMERIC_PRECISION as numeric_precision
539		, NUMERIC_SCALE as numeric_scale
540		, CHARACTER_SET_NAME as character_set_name
541		, COLLATION_NAME as collation_name
542		from information_schema.COLUMNS
543		where TABLE_SCHEMA = ifnull(%s, database())
544		and TABLE_NAME = %s
545		and COLUMN_NAME = %s',
546		$this->dl->quoteString($schemaName),
547		$this->dl->quoteString($tableName),
548		$this->dl->quoteString($columnName));
549
550		return $this->executeRow1($sql);
551		}
552
553		//--------------------------------------------------------------------------------------------------------------------
554		/**
555		* Selects metadata of all columns of table.
556		*
557		* @param string $schemaName The name of the table schema.
558		* @param string $tableName The name of the table.
559		*
560		* @return array[]
561		*
562		* @throws MySqlQueryErrorException
563		*/
564		public function tableColumns(string $schemaName, string $tableName): array
565		{
566		$sql = sprintf('
567		select COLUMN_NAME as column_name
568		, COLUMN_TYPE as column_type
569		, IS_NULLABLE as is_nullable
570		, CHARACTER_SET_NAME as character_set_name
571		, COLLATION_NAME as collation_name
572		, EXTRA as extra
573		from information_schema.COLUMNS
574		where TABLE_SCHEMA = %s
575		and TABLE_NAME = %s
576		order by ORDINAL_POSITION',
577		$this->dl->quoteString($schemaName),
578		$this->dl->quoteString($tableName));
579
580		return $this->executeRows($sql);
581		}
582
583		//--------------------------------------------------------------------------------------------------------------------
584		/**
585		* Selects the primary key from a table (if any).
586		*
587		* @param string $schemaName The name of the table schema.
588		* @param string $tableName The name of the table.
589		*
590		* @return array[]
591		*
592		* @throws MySqlQueryErrorException
593		*/
594		public function tablePrimaryKey(string $schemaName, string $tableName): array
595		{
596		$sql = sprintf('
597		show index from `%s`.`%s`
598		where Key_name = \'PRIMARY\'',
599		$schemaName,
600		$tableName);
601
602		return $this->executeRows($sql);
603		}
604
605		//--------------------------------------------------------------------------------------------------------------------
606		/**
607		* Selects all unique keys from table.
608		*
609		* @param string $schemaName The name of the table schema.
610		* @param string $tableName The name of the table.
611		*
612		* @return array[]
613		*
614		* @throws MySqlQueryErrorException
615		*/
616		public function tableUniqueIndexes(string $schemaName, string $tableName): array
617		{
618		$sql = sprintf('
619		show index from `%s`.`%s`
620		where Non_unique = 0',
621		$schemaName,
622		$tableName);
623
624		return $this->executeRows($sql);
625		}
626
627		//--------------------------------------------------------------------------------------------------------------------
628		/**
629		* Logs the query on the console.
630		*
631		* @param string $sql The query.
632		*/
633	1	private function logQuery(string $sql): void
634		{
635	1	$sql = trim($sql);
636
637	1	if (strpos($sql, "\n")!==false)
638		{
639		// Query is a multi line query.
640	1	$this->io->logVeryVerbose('Executing query:');
641	1	$this->io->logVeryVerbose('<sql>%s</sql>', $sql);
642		}
643		else
644		{
645		// Query is a single line query.
646	1	$this->io->logVeryVerbose('Executing query: <sql>%s</sql>', $sql);
647		}
648	1	}
649
650		//--------------------------------------------------------------------------------------------------------------------
651		}
652
653		//----------------------------------------------------------------------------------------------------------------------
654

DatabaseStratum / php-stratum-mysql

Push — master ( 304ce4...56185b )

MySqlMetaDataLayer::tableColumn() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like