Soluble\Schema

Introduction

Query your database schema to know everything about your tables, columns, types, foreign keys...

Features

• Inspect, query and discover your database structure.
• Rely on information schema tables for deep and accurate info.
• Support database extended informations (native types, relations...)
• Fast and reliable implementation (at least as fast as possible).

Requirements

• PHP engine 5.4+, 7.0+ or HHVM >= 3.2.

Supported databases

Currently only MySQL and MariaDB are supported.

Database	Driver	Source class
MySQL 5.1+	pdo_mysql, mysqli	Soluble\Schema\Source\MysqlInformationSchema
Mariadb 5.5+	pdo_mysql, mysqli	Soluble\Schema\Source\MysqlInformationSchema

You can create new schema sources (oracle, postgresql...) by implementing the Soluble\Schema\Source\SchemaSourceInterface.

Please see the contribution guide and send a pull request.

Documentation

• Manual in progress and API documentation available.

Installation

Instant installation via composer.

$ composer require soluble/schema:0.*

Most modern frameworks will include Composer out of the box, but ensure the following file is included:

<?php
// include the Composer autoloader
require 'vendor/autoload.php';

Examples

Connection

Initialize the Schema\Source\MysqlInformationSchema with a valid PDO or mysqli connection.

<?php

use Soluble\Schema;

$conn = new \PDO("mysql:host=$hostname", $username, $password, [
            \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
]);

/* Alternatively, use a \mysqli connection instead of PDO */
// $conn = new \mysqli($hostname,$username,$password,$database);
// $conn->set_charset($charset);

$schema = new Schema\Source\MysqlInformationSchema($conn);

// By default the schema (database) is taken from current connection. 
// If you wnat to query a different schema, set it in the second parameter.
$otherDbSchema = new Schema\Source\MysqlInformationSchema($conn, 'otherDbSchema');

Retrieve table informations in a database schema

<?php

// Retrieve table names defined in schema
$tables = $schema->getTables();

// Retrieve full information of tables defined in schema
$infos = $schema->getTablesInformation();

// The resulting array looks like
[
 ["table_name_1"] => [
    ["name"]    => (string) 'Table name'
    ["columns"] => [ // Columns information, 
                     // @see AbstractSource::getColumnsInformation()
                     "col name_1" => ["name" => "", "type" => "", ...]',
                     "col name_2" => ["name" => "", "type" => "", ...]'
                   ]
    ["primary_keys"] => [ // Primary key column(s) or empty
                      "pk_col1", "pk_col2"
                   ],
    ["unique_keys"]  => [ // Uniques constraints or empty if none
                      "unique_index_name_1" => ["col1", "col3"],
                      "unique_index_name_2" => ["col4"]
                   ],
    ["foreign_keys"] => [ // Foreign keys columns and their references or empty if none
                       "col_1" => [
                                    "referenced_table"  => "Referenced table name",
                                    "referenced_column" => "Referenced column name",
                                    "constraint_name"   => "Constraint name i.e. 'FK_6A2CA10CBC21F742'"
                                  ],
                       "col_2" => [ // ...  
                                  ]
                      ],
    ["references"] => [ // Relations referencing this table
                        "ref_table:ref_column->column1" => [
                             "column"             => "Colum name in this table",
                             "referencing_table"  => "Referencing table name", 
                             "referencing_column" => "Column name in the referencing table", 
                             "constraint_name"    => "Constraint name i.e. 'FK_6A2CA10CBC21F742'"
                           ],
                        "ref_table:ref_column->column2" => [ 
                             //...
                           ]
                      ]
    ["indexes"]  => [],
    ["options"]  => [ // Specific table creation options
                      "comment"   => (string) "Table comment",
                      "collation" => (string) "Table collation, i.e. 'utf8_general_ci'",
                      "type"      => (string) "Table type, i.e: 'BASE TABLE'",
                      "engine"    => (string) "Engine type if applicable, i.e. 'InnoDB'",
                    ]
 ],
 ["table_name_2"] => [
   //...
 ]
]
     
// Test if table exists in schema
if ($schema->hasTable($table)) {
    //...
}

Get table columns information

<?php

// Retrieve just column names from a table
$columns = $schema->getColumns($table); 
// -> ['column_name_1', 'column_name_2']

// Retrieve full columns information from a tabme
$columns = $schema->getColumnsInformation($table); 

// resulting column array looks like ->
[
  ["column_name_1"] => [
   ["type"]      => (string)  "Database type, i.e: 'char', 'int', 'bigint', 'decimal'...",
   ["primary"]   => (boolean) "Whether column is (part of) a primary key",
   ["nullable"]  => (boolean) "Whether column is nullable",
   ["default"]   => (string)  "Default value for column or null if none",

   // Specific to primary key(s) columns
   ["autoincrement"] => (boolean) "Whether the primary key is autoincremented"

   // Specific to numeric, decimal, boolean... types
   ["unsigned"]  => (boolean) "Whether the column is unsigned",
   ["precision"] => (int)     "Number precision (or maximum length)",

   // Specific to character oriented types as well as enum, blobs...
   ["length"]       => (int) "Maximum length",
   ["octet_length"] => (int) "Maximum length in octets (differs from length when using multibyte charsets",

   // Columns specific ddl information
   ["options"]  => [ // Column specific options
        "comment"          => "Column comment",
        "definition"       => "DDL definition, i.e. varchar(250)",
        "ordinal_position" => "Column position number",
        "constraint_type"  => "Type of constraint if applicable",
        "column_key"       => "",
        "charset"          => "Column charset, i.e. 'utf8'",
        "collation"        => "Column collation, i.e. 'utf8_unicode_ci'"
        ],
   ],
   ["column_name_2"] => [ 
       //... 
   ]
]

Retrieve table primary key(s)

<?php

// Get primary key
try {
    $pk = $schema->getPrimaryKey($table);
} catch (Schema\Exception\MultiplePrimaryKeyException $e) {
    //...
} catch (Schema\Exception\NoPrimaryKeyException $e) {
    //...
}

// Get multiple primary keys
try {
    $pks = $schema->getPrimaryKeys($table);
} catch (Schema\Exception\NoPrimaryKeyException $e) {
    // ...
}

Retrieve information about unique keys

<?php

$uniques = $schema->getUniqueKeys($table);

// The resulting array look like
[ 
    "unique_index_name_1" => [
           "column_name_1", "column_name_2"
          ],
    "unique_index_name_2" => [ "column_name_1" ]
]

Get foreign keys informations

<?php

$foreign_keys = $schema->getForeignKeys($table);

// The resulting array looks like
[
  "column_name_1" => [
      "referenced_table"  => "Referenced table name",
      "referenced_column" => "Referenced column name",
      "constraint_name"   => "Constraint name i.e. 'FK_6A2CA10CBC21F742'"
     ],
   "column_name_2" => [ 
      // ...  
     ]
]

Retrieve references informations

<?php

$references = $schema->getReferences($table);

// The resulting array looks like
[
    "ref_table:ref_column->column1" => [
         "column"             => "Colum name in this table",
         "referencing_table"  => "Referencing table name", 
         "referencing_column" => "Column name in the referencing table", 
         "constraint_name"    => "Constaint name i.e. 'FK_6A2CA10CBC21F742'"
       ],
    "ref_table:ref_column->column2" => [ 
         //...
       ]
]

API methods

Once a Schema\Source\SchemaSourceInterface is intitalized, you have access to the following methods

Methods	Return	Description
getSchemaConfig()	ArrayObject	Retrieve full extended schema config
getTables()	array	Retrieve table names
getTablesInformation()	array	Retrieve extended tables information
hasTable()	boolean	Whether table exists
getColumns($table)	array	Retrieve column names
getColumnsInformation($table)	array	Retrieve extended columns information
getPrimaryKey($table)	string	Retrieve primary key (unique)
getPrimaryKeys($table)	array	Retrieve primary keys (multiple)
getUniqueKeys($table)	array	Retrieve unique keys
getForeignKeys($table)	array	Retrieve foreign keys information
getReferences($table)	array	Retrieve referencing tables (relations)
getIndexes($table)	array	Retrieve indexes info

Future enhancements

• Supporting more sources like postgres, oracle
• PSR-6 cache implementation

Contributing

Contribution are welcome see contribution guide

Coding standards

• PSR 4 Autoloader
• PSR 2 Coding Style Guide
• PSR 1 Coding Standards
• PSR 0 Autoloading standards