Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 3 |
CRAP | |
0.00% |
0 / 34 |
| CommonQueries | |
0.00% |
0 / 1 |
|
0.00% |
0 / 3 |
30.00 | |
0.00% |
0 / 34 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| db_summary_echo | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| db_summary | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 27 |
|||
| <?php | |
| /** | |
| * CommonQueries | |
| * | |
| * @package inc_ia/Sql | |
| * @author Raul Santos | |
| * @copyright 2017 | |
| * @version 1.0.1 | |
| */ | |
| /** @noinspection SqlNoDataSourceInspection */ | |
| /** @noinspection SqlResolve */ | |
| namespace ia\Sql\Mysql; | |
| use ia\Lib\iaTableIt; | |
| /* | |
| settings | |
| mysql | |
| mysql version | |
| server_number | |
| timezone | |
| charset/collation conecction | |
| charset/collation db | |
| tempfilesizes | |
| innodb_files_per_table | |
| innodb_autoinc_ | |
| php | |
| mysqlnd | |
| version | |
| Command buffer size | |
| Read buffer size | |
| Read timeout | |
| Loaded plugins | |
| Collecting statistics | |
| Collecting memory statistics | |
| mysqli | |
| mysqli.max_links | |
| mysqli.max_persistent | |
| mysqli.reconnect | |
| mysqli.rollback_on_cached_plink | |
| mysqli-connection | |
| ? | |
| hay Cache mysqlnd | |
| */ | |
| class CommonQueries { | |
| protected $db; | |
| public function __construct($db) { | |
| $this->db = $db; | |
| } | |
| //@TODO get base tables | |
| //@TODO get views | |
| //@TODO get pk for table | |
| //@TODO get unique index for table | |
| //@TODO get cols & type for table | |
| public function db_summary_echo($db_summary) { | |
| echo "<style>".iaTableIt::tableIt_css()."</style>"; | |
| foreach($db_summary as $label => $d) { | |
| echo "<h3>$label</h3>"; | |
| iaTableIt::tableIt($d, $label,[], false); | |
| } | |
| } | |
| public function db_summary() { | |
| $dbName = 'DATABASE()'; | |
| $dbNamed = $this->db->single_read("SELECT $dbName"); | |
| $ret = []; | |
| $sql = [ | |
| 'object count' => | |
| "SELECT 'Tables',COUNT(*) AS `Count` FROM INFORMATION_SCHEMA.TABLES WHERE table_type='BASE TABLE' AND table_schema=$dbName | |
| Union ALL | |
| SELECT 'Views',COUNT(*) AS `Count` FROM INFORMATION_SCHEMA.VIEWS WHERE table_schema=$dbName | |
| Union ALL | |
| SELECT 'Procedures',Count(*) AS `Count` FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_schema=$dbName AND routine_type='PROCEDURE' | |
| Union ALL | |
| SELECT 'Functions',Count(*) AS `Count` FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_schema=$dbName AND routine_type='FUNCTION' | |
| Union ALL | |
| SELECT 'Triggers',Count(*) AS `Count` FROM INFORMATION_SCHEMA.`TRIGGERS` WHERE trigger_schema=$dbName | |
| Union ALL | |
| SELECT 'Events',COUNT(*) AS `Count` FROM INFORMATION_SCHEMA.`EVENTS` WHERE EVENT_SCHEMA=$dbName", | |
| 'database characters' => | |
| "SELECT '$dbNamed' as `Database`, default_character_set_name, default_collation_name | |
| FROM INFORMATION_SCHEMA.SCHEMATA | |
| WHERE schema_name = $dbName | |
| ", | |
| 'table characters' => | |
| "SELECT T.table_name, T.engine, CCSA.character_set_name, T.table_collation | |
| FROM INFORMATION_SCHEMA.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA | |
| WHERE CCSA.collation_name = T.table_collation AND T.table_schema = $dbName", | |
| 'tables pk'=>"SELECT '@TODO'", | |
| 'tables unique'=>"SELECT '@TODO'", | |
| 'tables fk'=>"SELECT '@TODO'", | |
| 'column same name different charset/coallate'=> | |
| "SELECT | |
| a.TABLE_NAME as table_a, a.COLUMN_NAME as column_a, a.CHARACTER_SET_NAME as charset_a, a.COLLATION_NAME as collation_a, | |
| b.TABLE_NAME as table_b, b.COLUMN_NAME as column_b, b.CHARACTER_SET_NAME as charset_b, b.COLLATION_NAME as collation_b | |
| FROM INFORMATION_SCHEMA.`COLUMNS` a | |
| JOIN INFORMATION_SCHEMA.`COLUMNS` b ON a.column_name=b.column_name AND a.table_schema = b.table_schema | |
| WHERE a.table_schema = $dbName AND a.table_name < b.table_name | |
| AND (a.COLLATION_NAME<>b.COLLATION_NAME OR a.CHARACTER_SET_NAME<>b.CHARACTER_SET_NAME) | |
| ORDER BY 1,2", | |
| 'column characters different from table' => | |
| "SELECT T.table_name, CCSA.character_set_name as table_charset, T.table_collation, | |
| col.COLUMN_NAME, col.character_set_name, col.COLLATION_NAME | |
| FROM INFORMATION_SCHEMA.`TABLES` T | |
| JOIN INFORMATION_SCHEMA.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA ON CCSA.collation_name = T.table_collation | |
| JOIN INFORMATION_SCHEMA.`COLUMNS` col ON col.table_schema = T.table_schema AND col.table_name = T.table_name | |
| WHERE T.table_schema = $dbName | |
| AND (CCSA.character_set_name <> col.character_set_name OR col.COLLATION_NAME <> T.table_collation)", | |
| 'columns same name, diferent Data types'=>"SELECT '@TODO'", | |
| 'columns fk, diferent Data types'=>"SELECT '@TODO'", | |
| 'trigger count' => | |
| "SELECT event_object_table AS `Table`, COUNT(*) AS `Count` | |
| FROM INFORMATION_SCHEMA.`TRIGGERS` | |
| WHERE event_object_schema=$dbName | |
| GROUP BY 1", | |
| 'trigger detail' => | |
| "SELECT event_object_table AS `Table`, CONCAT_WS(' ', action_timing, event_manipulation) AS `Type`, COUNT(*) AS `Count` | |
| FROM INFORMATION_SCHEMA.`TRIGGERS` | |
| WHERE event_object_schema=$dbName | |
| GROUP BY 1,2", | |
| 'triggers names' => | |
| "SELECT event_object_table AS `Table`, group_concat(trigger_name) AS `triggers` | |
| FROM INFORMATION_SCHEMA.`TRIGGERS` | |
| WHERE event_object_schema=$dbName | |
| GROUP BY 1", | |
| 'table columns' => | |
| "SELECT col.table_name, t.table_type, count(*) AS `columns_num`, group_concat(col.column_name SEPARATOR ', ') AS `columns` | |
| FROM INFORMATION_SCHEMA.COLUMNS col | |
| JOIN INFORMATION_SCHEMA.TABLES t ON t.table_name=col.table_name AND t.table_schema=col.table_schema | |
| WHERE col.table_schema = $dbName | |
| GROUP BY 1,2", | |
| 'users for db' => "SELECT '@TODO'", | |
| ]; | |
| foreach($sql as $label => $sql) { | |
| $ret[$label] = $this->db->selectArrayIndex($sql); | |
| } | |
| return $ret; | |
| } | |
| } | |