soluble/schema

Database information schema

0.11.2 2017-08-25 20:49 UTC

This package is auto-updated.

Last update: 2022-11-15 22:48:30 UTC


README

PHP Version Build Status codecov Scrutinizer Quality Score Latest Stable Version Total Downloads License

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

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