Download the PHP package soluble/metadata without Composer
On this page you can find all versions of the php package soluble/metadata. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download soluble/metadata
More information about soluble/metadata
Files in soluble/metadata
Package metadata
Short Description Extract metadata from database queries
License MIT
Homepage https://github.com/belgattitude/soluble-metadata
FAQ
Example:
In general, it is recommended to use always a project to download your libraries. In an application normally there is more than one library needed.
- Some hosting areas are not accessible by a terminal or SSH. Then it is not possible to use Composer.
- To use Composer is sometimes complicated. Especially for beginners.
- Composer needs much resources. Sometimes they are not available on a simple webspace.
- If you are using private repositories you don't need to share your credentials. You can set up everything on our site and then you provide a simple download link to your team member.
- Simplify your Composer build process. Use our own command line tool to download the vendor folder as binary. This makes your build process faster and you don't need to expose your credentials for private repositories.
Informations about the package metadata
soluble-metadata is a low level library currently focusing on MySQL which extracts metadata from an sql query with extensibility, speed and portability in mind.
Use cases
You can take advantage of soluble/metadata to format/render resulting query data according to their type (when rendering an html table, generating an excel sheet...), for basic validation (max lengths, decimals)...
Features
- Extract metadata information from an SQL query (datatypes,...)
- Common API across various driver implementations.
- Rely on native database driver information (does not parse the query in PHP)
- Works even when the query does not return results (empty resultset).
- Carefully tested with different implementations (libmariadb, mysqlnd, libmysql, pdo_mysql).
Under the hood, the metadata extraction relies on the driver methods
mysqli_stmt::result_metadata()andPDO::getColumnMeta(). Although thesoluble-metadataAPI unify their usage and type detection, differences still exists for more advanced features. A specific effort has been made in the documentation to distinguish possible portability issues when switching from one driver to another. Keep that in mind when using it.
Requirements
- PHP engine 7.1+ (v1.2.0), 7.0+ and 5.4 (v1.0.0)
- Mysqli or PDO_mysql extension enabled (Mysqli exposes more features)
Documentation
- This README and API documentation available.
Installation
Instant installation via composer.
Most modern frameworks will include composer out of the box, but ensure the following file is included:
Basic example
Could print something like :
| Column name | Type | Null | Unsigned | Length | Precision | Scale | Native |
|---|---|---|---|---|---|---|---|
| column_1 | integer | N | Y | BIGINT | |||
| column_2 | string | N | 255 | VARCHAR | |||
| column_3 | decimal | Y | N | 5 | 2 | DECIMAL | |
| column_4 | datetime | Y | DATETIME | ||||
| column_5 | date | Y | DATE | ||||
| column_6 | time | Y | TIME | ||||
| column_7 | float | N | FLOAT | ||||
| column_8 | blob | Y | 16777215 | MEDIUMBLOB | |||
| column_9 | spatial_geometry | Y | null (N/A) |
...
Usage
Step 1. Initiate a metadata reader
-
For Mysqli: send the existing mysqli connection to the
MysqlMetadataReader: - For Pdo_mysql: send the existing pdo_mysql connection to the
PdoMysqlReader:
Step 2. Extract metadata from an SQL query
Alternatively, when you want to get the metadata from a table you can use the helper method
$reader->getTableMetadata($table).
Step 3: Getting column type (4 options)
Step 4: Getting datatype extra information
The following methods are supported and portable between mysqli and PDO_mysql drivers:
Getting column specifications.
The following methods are also portable.
The methods used in the example below gives different results with
pdo_mysqlandmysqlidrivers. Use them with care if portability is required !!!
Unsupported methods
Those methods are still unsupported on both mysqli and PDO_mysql implementations but kept as reference
API
AbstractMetadataReader
Use the Reader\AbstractMetadataReader::getColumnsMetadata($sql) to extract query metadata.
| Methods | Return | Description |
|---|---|---|
getColumnsMetadata($sql) |
ColumnsMetadata |
Metadata information: ArrayObject with column name/alias |
ColumnsMetadata
The Soluble\Metadata\ColumnsMetadata allows to iterate over column information or return a specific column as
an Soluble\Datatype\Column\Definition\AbstractColumnDefinition.
| Methods | Return | Description |
|---|---|---|
getColumn($name) |
AbstractColumnDefinition |
Information about a column |
AbstractColumnDefinition
Metadata information is stored as an Soluble\Datatype\Column\Definition\AbstractColumnDefinition object on which :
| General methods | Return | Description |
|---|---|---|
getName() |
string |
Return column name (unaliased) |
getAlias() |
string |
Return column alias |
getTableName() |
string |
Return origin table |
getSchemaName() |
string |
Originating schema for the column/table |
getOrdinalPosition() |
integer |
Return position in the select |
| Type related methods | Return | Description |
|---|---|---|
getDataType() |
string |
Column datatype (see Column\Type) |
getNativeDataType() |
string |
Return native datatype (VARCHAR, BIGINT...) |
isText() |
boolean |
Whether the column is textual (string, blog...) |
isNumeric() |
boolean |
Whether the column is numeric (decimal, int...) |
isDatetime() |
boolean |
Is a datetime type |
isDate() |
boolean |
Is a date type |
| Flags information | Return | Description |
|---|---|---|
isPrimary() |
boolean |
Whether the column is (part of) primary key |
isAutoIncrement() |
boolean |
If it's an autoincrement column (only mysqli) |
isNullable() |
boolean |
Whether the column is nullable |
getColumnDefault() |
string |
Return default value for column (not working yet) |
| Extra information methods | Return | Description |
|---|---|---|
isComputed() |
boolean |
Whether the column is computed, i.e. '1+1, sum() |
isGroup() |
boolean |
Grouped operation sum(), min(), max() |
| Numeric type specific | Return | Description |
|---|---|---|
getNumericScale() |
integer |
Scale for numbers, i.e DECIMAL(10,2) -> 10 |
getNumericPrecision() |
integer |
Precision, i.e. DECIMAL(10,2) -> 2 |
isNumericUnsigned() |
boolean |
Whether signed or unsigned |
| Character type specific | Return | Description |
|---|---|---|
getCharacterMaximumLength() |
integer |
Max string length for chars (unicode sensitive) |
getCharacterOctetLength() |
integer |
Max octet length for chars, blobs... (binary, no unicode) |
AbstractColumnDefinition implementations
Here's the list of concrete implementations for Soluble\Datatype\Column\Definition\AbstractColumnDefinition.
They can be used as an alternative way to check column datatype. For example
| Definition Type | Interface | Description |
|---|---|---|
BitColumn |
||
BlobColumn |
||
BooleanColumn |
||
DateColumn |
DateColumnInterface |
|
DateTimeColumn |
DatetimeColumnInterface |
|
DecimalColumn |
NumericColumnInterface |
|
FloatColumn |
NumericColumnInterface |
|
GeometryColumn |
||
IntegerColumn |
NumericColumnInterface |
|
StringColumn |
TextColumnInterface |
|
TimeColumn |
||
NullColumn |
Special case for columns aliasing 'NULL' value |
Supported readers
Currently only pdo_mysql and mysqli drivers are supported.
| Drivers | Reader implementation |
|---|---|
| pdo_mysql | Soluble\Metadata\Reader\PdoMysqlMetadataReader |
| mysqli | Soluble\Metadata\Reader\MysqliMetadataReader |
Future ideas
- Implement more drivers (pgsql...), contributions welcome !!!
Contributing
Contribution are welcome see contribution guide
Notes
Currently metadata are read from the underlying database driver by executing a query with a limit 0 (almost no performance penalty). This ensure your query is always correctly parsed (even crazy ones) with almost no effort.
The underlying driver methods mysqli_stmt::result_metadata(), PDO::getColumnMeta()
used respectively by the metadata readers Mysql and PdoMysql are marked as experimental
and subject to change on the PHP website. In practice, they haven't changed since 5.4 and
are stable. In case of a change in the php driver, it should be very easy to add a
specific driver.
Sadly there is some differences between PDO_mysql and mysqli in term of features. Generally the best is to use mysqli instead of pdo. PDO lacks some features like detection of autoincrement, enum, set, unsigned, grouped column and does not distinguish between table/column aliases and their original table/column names.
If you want to rely on this specific feature (aliases) have a look to alternatives like phpmyadmin sql-parser.
Also if you are looking for a more advanced metadata reader (but limited to table - not a query), have a look to the soluble-schema project which share the same datatype standards while exposing more information like foreign keys,... in a more portable way.
