PHP code example of soluble / metadata

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

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

    

soluble / metadata example snippets

// include the Composer autoloader

use Soluble\Metadata\Reader;
use Soluble\Datatype\Column\Type as DataType;


$conn = new \mysqli($hostname,$username,$password,$database);
$conn->set_charset($charset);

$metaReader = new Reader\MysqliMetadataReader($conn);

$sql = "select * from `my_table`";

try {
    $md = $metaReader->getColumnsMetadata($sql);
} catch (\Soluble\Metadata\Exception\InvalidQueryException $e) {
    // ...
}

foreach($md as $column_name => $col_def) {
   
   $datatype = $col_def->getDatatype();
   
   echo $column_name . "\t" . $datatype . "\t";
   
   echo ($col_def->isNullable() ? 'Y' : 'N') . '\t';
   
   switch ($datatype) {
       case DataType::TYPE_STRING:  // equivalent to 'string'
           echo $col_def->getCharacterOctetLength() . "\t";
           break;
       case DataType::TYPE_INTEGER:
           echo ($col_def->isNumericUnsigned() ? 'Y' : 'N') . "\t";
           break;
       case DataType::TYPE_DECIMAL:
           echo ($col_def->isNumericUnsigned() ? 'Y' : 'N') . "\t";
           echo $col->getNumericPrecision() . "\t";  // For DECIMAL(5,2) -> precision = 5 
           echo $col->getNumericScale() . "\t";      // For DECIMAL(5,2) -> scale = 2
           break;
           
       // ...see the doc for more possibilitities
   }
   
   echo $col_def->getNativeType() . PHP_EOL;
}  

    
    use Soluble\Metadata\Reader;
    
    $conn = new \mysqli($hostname,$username,$password,$database);
    $conn->set_charset($charset);
    
    $reader = new Reader\MysqliMetadataReader($conn);
    
    

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

//....

$reader = new Reader\MysqliMetadataReader($conn);

$sql = "
         SELECT `p`.`post_id`,
                `p`.`title` AS `post_title` 
                `p`.`created_at`,
                'constant' AS `constant_col`,
                1 + 2 AS `computed_col`, 
                null as `null_col`
                COUNT(`c`.*) as `nb_comments`,
                MAX(`c`.`created_at`) as latest_comment
                 
            FROM `post` AS `p`
            LEFT OUTER JOIN `comment` as `c`  
                 ON `c`.`post_id` = `p`.`post_id`
            GROUP BY `p`.`post_id`, `p`.`title`, 
                     `p`.`created_at`, `constant_col`, 
                     `computed_col`, `null_col`     
       ";


try {    
    $meta = $reader->getColumnsMetadata($sql);
} catch (\Soluble\Metadata\Exception\InvalidQueryException $e) { 
    //...
}

/*
  The resulting ColumnsMetadata will contain something like:

  [
     "post_id"        => '<Soluble\Datatype\Column\Definition\IntegerColumn>',
     "post_title"     => '<Soluble\Datatype\Column\Definition\StringColumn>',
     "created_at"     => '<Soluble\Datatype\Column\Definition\DatetimeColumn>',
     "constant_col"   => '<Soluble\Datatype\Column\Definition\StringColumn>',
     "computed_col"   => '<Soluble\Datatype\Column\Definition\IntegerColumn>',
     "null_col"       => '<Soluble\Datatype\Column\Definition\NullColumn>',
     "nb_comments"    => '<Soluble\Datatype\Column\Definition\IntegerColumn>',
     "latest_comment" => '<Soluble\Datatype\Column\Definition\DateTimeColumn>'
     
  ]
    
*/  

// ...

$meta = $reader->getColumnsMetadata($sql);

// Retrieve a specific column (i.e. 'post_title')
// Note the parameter is the column alias if defined 

$col = $meta->getColumn('post_title'); 

                                       
// Type detection
// ----------------------

// Option 1, type detection by datatype name
// ------------------------------------------

echo $col->getDatatype(); // -> 'string' (equivalent to Soluble\Datatype\Column\Type::TYPE_STRING)  

/* 
   The normalized datatypes are defined in the 
   Soluble\Datatype\Column\Type::TYPE_(*) and can be :
   'string', 'integer', 'decimal', 'float', 'boolean', 
   'datetime', 'date', 'time', 'bit', 'spatial_geometry'
*/


// Option 2, type detection by classname
// --------------------------------------

if ($col instanceof \Soluble\Datatype\Column\IntegerColumn) {
    // ... could be also BitColumn, BlobColumn, BooleanColumn
    // ... DateColumn, DateTimeColumn, DecimalColumn, FloatColumn
    // ... GeometryColumn, IntegerColumn, StringColumn, TimeColumn,
    // ... NullColumn
}

// Option 3, type detection by interface (more generic)
// -----------------------------------------------------

if ($col instanceof \Soluble\Datatype\Column\NumericColumnInterface) {
   // ... for example NumericColumnInterface 
   // ... 

// ...

// For all types
// -------------

echo $col->getOrdinalPosition(); // -> 2 (column position in the query)
echo $col->isNullable() ? 'nullable' : 'not null';
echo $col->isPrimary() ? 'PK' : '';  // Many columns may have the primary flag
                                     // The meaning of it depends on your query

// For integer and decimal types
echo $col->isNumericUnsigned();   // Whether the numeric value is unsigned.

// For decimal based types
// -----------------------

echo $col->getNumericPrecision(); // For DECIMAL(5,2) -> 5 is the precision
echo $col->getNumericScale();     // For DECIMAL(5,2) -> 2 is the scale

// For character/blob based types
// ------------------------------

echo $col->getCharacterOctetLength();  // Octet length (in multibyte context length might differs)
 

 


// ...

echo $col->getAlias(); // Column alias name -> "post_title" (or column name if not aliased)

echo $col->isComputed(); // Whenever there's no table linked (for GROUP, constants, expression...)

echo $col->getTableAlias(); // Originating table alias -> "p" (or table name if not aliased)
                            // If empty, the column is computed (constant, group,...)

// ...

echo $col->getTableName();  // Originating table -> "post"
                            // (*) PDO_mysql always return the table alias if aliased 

echo $col->getName();  // Column original name -> "title". 
                       // (*) PDO_mysql always return the alias if aliased

echo $col->getNativeType(); // Return the column definition native type
                            // i.e: BIGINT, SMALLINT, VARCHAR, ENUM
                            // (*) PDO_mysql consider 
                            //        - ENUM, SET and VARCHAR as CHAR
                            
echo $col->isGroup(); // Whenever the column is part of a group (MIN, MAX, AVG,...)
                      // (*) PDO_mysql is not able to retrieve group information
                      // (*) Mysqli: detection of group is linked to the internal driver
                      //     Check your driver with mysqli_get_client_version().
                      //       - mysqlnd detects:
                      //          - COUNT, MIN, MAX
                      //       - libmysql detects:
                      //          - COUNT, MIN, MAX, AVG, GROUP_CONCAT
                      //       - libmariadb detects:
                      //          - COUNT, MIN, MAX, AVG, GROUP_CONCAT and growing    


// For numeric types
// -----------------

echo $col->isAutoIncrement();   // Only make sense for primary keys.
                                // (*) Unsupported with PDO_mysql

echo $col->isNumericUnsigned(); // Whether the numeric value is unsigned.
                                // (*) Unsupported with PDO_mysql

// ... 

echo $col->getColumnDefault(); // Always return null
echo $col->getCharacterMaximumLength();  // Returns $col->getCharacterOctetLength()
                                         // and does not (yet) handle multibyte aspect.

use Soluble\Metadata\Reader;
use PDO;

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

$reader = new Reader\PdoMysqlMetadataReader($conn);

$sql = "select id, name from my_table";

$columnsMeta = $reader->getColumnsMetadata($sql);

$reader = new Reader\PdoMysqlMetadataReader($conn);

$sql = "select id, name from my_table";

$columnsMeta = $reader->getColumnsMetadata($sql);

foreach ($columnsMeta as $col_name => $col_def) {
    echo $coldev->getDatatype() . PHP_EOL; 
}

$col = $columnsMeta->getColumn('id');
echo $col->getDatatype();

use Soluble\Datatype\Column\Definition;

if ($coldef instanceof Definition\DateColumnInterface) {

    // equivalent to
    // if ($coldef->isDate()) {

    $date = new \DateTime($value);
    echo $value->format('Y');
} elseif ($coldef instanceof Definition\NumericColumnInterface) {
    echo number_format($value, $coldef->getNumericPrecision);
}