MySqlMetaDataLayer::allCharacterSets() - Code Metrics - Inspection of "Update .scrutinizer.yml." - DatabaseStratum/php-stratum-mysql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( e0c5e8...7926db )

by P.R.

created 2020-10-17 16:11 UTC

MySqlMetaDataLayer::allCharacterSets() A

↳ Parent: MySqlMetaDataLayer

Complexity

Conditions	1
Paths	1

Size

Total Lines	9
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	1

Importance

Changes	1
Bugs	0	Features	0

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

<?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()
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) table_name
  ,      column_name
  ,      column_type
  ,      data_type
  ,      character_maximum_length
  ,      character_set_name
  ,      collation_name
  ,      numeric_precision
  ,      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));
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Selects the SQL mode in the order as preferred by MySQL.
   *
   * @param string $sqlMode The SQL mode.
   *
   * @return string
   *
   * @throws MySqlQueryErrorException
   * @throws ResultException
   */
  public function correctSqlMode(string $sqlMode): string
  {
    $sql = sprintf('set sql_mode = %s', $this->dl->quoteString($sqlMode));
    $this->executeNone($sql);

    $sql = 'select @@sql_mode';

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

DatabaseStratum / php-stratum-mysql

Push — master ( e0c5e8...7926db )

MySqlMetaDataLayer::allCharacterSets() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like