PHP code example of soluble / schema

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

soluble / schema example snippets

// include the Composer autoloader

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 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)) {
    //...
}

// 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"] => [ 
       //... 
   ]
]

// 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) {
    // ...
}

$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" ]
]

$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" => [ 
      // ...  
     ]
]

$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" => [ 
         //...
       ]
]