Download the PHP package simbiat/db-manager without Composer

Database manager

Functions to help with database management. To establish connection, pass a \PDO object to the constructor or null if you are using DB Pool library.

checkTable

Check if table (or tables) exist and get a number (integer) of tables that are found. You can also provide a schema (or a list of those), in case you have tables with the same name in multiple schemas. Note that if both $table and $schema are arrays then all possible combinations found will be counted, so be careful with that.

getColumnType

Get a column's type. You need to provide at least table and column names. Schema is optional but recommended, since if you have multiple schemas with the same table and column name in them, the first hit will be returned if schema is not provided.

getColumnDescription

Get column description (comment). You need to provide at least table and column names. Schema is optional but recommended, since if you have multiple schemas with the same table and column name in them, the first hit will be returned if schema is not provided.

isNullable

Checks if a column is nullable and returns respective boolean. You need to provide at least table and column names. Schema is optional but recommended, since if you have multiple schemas with the same table and column name in them, the first hit will be used if schema is not provided.

checkFK

Checks if a foreign key with a provided name exists in a table and returns respective boolean. Schema is optional but recommended, since if you have multiple schemas with the same table and column name in them, the first hit will be used if schema is not provided.

checkColumn

Checks if a column with a provided name exists in a table and returns respective boolean. Schema is optional but recommended, since if you have multiple schemas with the same table and column name in them, the first hit will be used if schema is not provided.

showOrderedTables

Function to get a list of all tables for a schema (or all schemas, if $schema is empty) in order, where first you have tables without dependencies (no foreign keys), and then tables that are dependent on tables that have come before. This is useful if you want to dump backups in a specific order so that you can then restore the data without disabling foreign keys. Set $by_size to true to also sort by size from smallest to largest.
Can't guarantee work on anything besides MySQL/MariaDB. Does not work with cyclic dependencies.

checkCyclicForeignKeys

This function allows you to check for cyclic foreign keys when 2 (or more) tables depend on each other. This is considered bad practice even with nullable columns, but you may easily miss them as your database grows, especially if you have chains of three or more tables.
This will not return the specific FKs you need to deal with, but rather just a list of tables referencing tables that refer to the initial ones. You will need to analyze the references yourself to "untangle" them properly.
You can pass a prepared list of tables with a format of

to limit tables that need to be checked.
Can't guarantee work on anything besides MySQL/MariaDB.

selectAllDependencies

Function to recursively get all dependencies (foreign keys) of a table. This does not mean just all tables referenced by the table's foreign keys, but also tables that those tables depend on.
Can't guarantee work on anything besides MySQL/MariaDB.

showCreateTable

Function to restore ROW_FORMAT value to table definition.
MySQL/MariaDB may now have ROW_FORMAT in SHOW CREATE TABLE output or have a value, which is different from the current one. This function amends that.
A few options are supported:

• $no_increment - Remove the AUTO_INCREMENT=X table option. Column attribute will still be present.
• $if_not_exist - Add the IF NOT EXISTS clause to the resulting definition.
• $add_use - Add the USE statement before the CREATE statement.

hasFKViolated

Function to check if your table (or schema or whole database server) has any violations of FOREIGN KEY constraints. While schema and table are optional, it's recommended to pass them in case there are large tables in the database. Also keep in mind that due to nature of information_schema first run may take a while. You can additionally pass $nullable_only as true to get only constraints that have DELETE_RULE set to SET NULL. This is mainly useful for fixFKViolations function.

The function will return an array like this:

fixFKViolations

Fix constraints' violations found by hasFKViolated. While schema and table are optional, it's recommended to pass them in case there are large tables in the database. Also keep in mind that due to nature of information_schema first run may take a while.
Also has two other options governing how violations will be fixed:

• nullable_only - Whether to get only nullable constraints. If set to false entries that are not nullable will be REMOVED (DELETE will be used, so use with caution). If set to true (default), only nullable constraints will be picked up violations will be updated by settings the values to NULL.
• force_delete - Whether to use DELETE even for nullable constraints. Use with caution.

Returns array similar to hasFKViolated, but with extra key fixed which represents number of rows that were updated/deleted as part of the fix.

rebuildIndexQuery

Generates and optionally runs (if $run is true) a query to rebuild (DROP and then ADD) an index. You need to pass the schema name, the table name and the index name for this to work.