/MonetDB-PHP

The official PHP client library for accessing MonetDB. For PHP 8.x and 7.2 or above.

Primary LanguageC++Apache License 2.0Apache-2.0

MonetDB-PHP

The official PHP client library for accessing MonetDB. For PHP 8.x and 7.2 or above (see instructions below).

Main features:

  • Parameterized queries, using cached prepared statements.
  • Extensively tested with Japanese characters for the UTF-8 compliance.
  • Multiple, concurrent connections.
  • Allows access to response stats, like execution time and affected row count, etc.
  • The thrown MonetException exception objects contain user-friendly error messages.
  • Provides information about the columns of the response data, like name, SQL type and length.

If you wish to implement your own client library either for PHP or for another language, then please read the guide about the client-server protocol.

Table of contents

Installation with Composer (PHP 8.x)

This library is available on Packagist at:

First install Composer, then execute the following in your project's directory:

composer require tbolner/monetdb-php

Usage without installation

You don't need to use Composer in your project. You can just copy all files in the 'src' folder, and include them in your project through the include.php file, which was created just for this purpose.

require_once(__DIR__."/../path/to/include.php");

Then either reference the classes by a combination of a use statement and the short class name (as it is done in the example projects):

use MonetDB\Connection;

$connection = new Connection("127.0.0.1", 50000, "monetdb", "monetdb", "myDatabase");

Or just use the fully qualified class name (if your project doesn't use namespaces):

$connection = new \MonetDB\Connection("127.0.0.1", 50000, "monetdb", "monetdb", "myDatabase");

Please make sure that the php-mbstring (multi-byte string) extension is installed and enabled, and the character encoding for your project is set to UTF-8: (This is required for preventing SQL injection attacks)

mb_internal_encoding('UTF-8');
mb_regex_encoding('UTF-8');

Installation for PHP 7.2 or above.

Only the 1.1.x versions support PHP 7.2 or above.

composer require tbolner/monetdb-php:1.1.6

Examples

Example projects:

Example 1: Simple query

use MonetDB\Connection;

$connection = new Connection("127.0.0.1", 50000,
    "monetdb", "monetdb", "myDatabase");

$result = $connection->Query('
    select
        name, weight_kg, category, birth_date, net_worth_usd
    from
        cats
');

$columnNames = $result->GetColumnNames();

foreach($result as $record) {
    echo "Name: {$record["name"]}\n";
    echo "Weight: {$record["weight_kg"]} kg\n";
    echo "Category: {$record["category"]}\n";
    echo "Birth date: {$record["birth_date"]}\n";
    echo "Net worth: ${$record["net_worth_usd"]}\n\n";
}

The returned values are always in string representation except the null, which is always returned as null.

Example 2: Get execution stats

$result = $connection->Query(<<<EOF
    update
        "cats"
    set
        "weight_kg" = 9.2
    where
        "name" = 'Ginger';
    
    insert into
        "cats"
        ("name", "weight_kg", "category", "birth_date", "net_worth_usd")
    values
        ('Mew', 8.2, 'shorthair', '2015-03-11', 1250000);
EOF
);

foreach($result->GetStatusRecords() as $stat) {
    echo "Affected rows: {$stat->GetAffectedRows()}\n";
}

Example 3: Parameterized query with prepared statement

$result = $connection->Query('
    select
        *
    from
        "cats"
    where
        "name" = ?
        and "weight_kg" > ?
    limit
        10
', [ "D'artagnan", 5.3 ]);

In MonetDB the placeholders of prepared statements have specific types. This library auto-converts some of the PHP types to the corresponding MonetDB types.

MonetDB type Accepted PHP types Value examples
timestamp string, DateTime "2020-12-20 11:14:26.123456"
date string, DateTime "2020-12-20"
boolean boolean, string, integer true, false, "true", 0, "0", 1, "t", "f", "yes", "no", "enabled", "disabled"
Numeric values integer, float, string 12.34, "12.34" (use string for huge numbers)
Character types string "Hello World!"
Binary string "0f44ba12" (always interpreted as hexadecimal)
time string, DateTime "11:28", "12:28:34"

Always pass the null values as null, and not as a string.

Example 4: Using escaping

$name = $connection->Escape("D'artagnan");
$weight = floatval("5.3");

$result = $connection->Query(<<<EOF
    select
        *
    from
        "cats"
    where
        "name" = '{$name}'
        and "weight_kg" > {$weight}
EOF
);

Example 5: Renaming fields and using column info

$result = $connection->Query('
    select
        "category",
        sys.stddev_samp("weight_kg") as "weight_stddev",
        sys.median("weight_kg") as "weight_median",
        avg("weight_kg") as "weight_mean"
    from
        "cats"
    group by
        "category"
');

echo "The columns of the response data:\n\n";

foreach($result->GetColumnInfo() as $info) {
    echo "Table/resource name: {$info->GetTableName()}\n";
    echo "Field name: {$info->GetColumnName()}\n";
    echo "Type: {$info->GetType()}\n";
    echo "Length: {$info->GetLength()}\n\n";
}

echo "Data:\n\n";

foreach($result as $record) {
    echo "{$record["category"]} : Mean: {$record["weight_mean"]} kg, "
        ."Median: {$record["weight_median"]} kg, "
        ."StdDev: {$record["weight_stddev"]} kg\n";
}

Example 6: Query the first record only

$record = $connection->QueryFirst('
    select
        sum("weight_kg") as "weight"
    from
        "cats"
');

echo "Sum: {$record["weight"]}\n";

Example 7: Transactions

$connection->Query(<<<EOF
    start transaction;

    update
        "cats"
    set
        "weight_kg" = 9.2
    where
        "name" = 'Ginger';
    
    insert into
        "cats"
        ("name", "weight_kg", "category", "birth_date", "net_worth_usd")
    values
        ('Mew', 8.2, 'shorthair', '2015-03-11', 1250000);
    
    commit;
EOF
);

Or:

$connection->Query('start transaction');

$connection->Query(<<<EOF
    update
        "cats"
    set
        "weight_kg" = 9.2
    where
        "name" = 'Ginger'
EOF
);

$connection->Query(<<<EOF
    insert into
        "cats"
        ("name", "weight_kg", "category", "birth_date", "net_worth_usd")
    values
        ('Mew', 8.2, 'shorthair', '2015-03-11', 1250000)
EOF
);

$connection->Query('commit');

Example 8: Importing data the fastest way

$connection->Query(<<<EOF
    copy offset 2 into cats
    from
        '/home/meow/cats.csv'
        ("name", "weight_kg", "category", "birth_date", "net_worth_usd")
    delimiters ',', '\n', '"'
    NULL as '';
EOF
);

Example 9: Using multiple connections

$connection1 = new Connection("127.0.0.1", 50000,
    "monetdb", "monetdb", "myDatabase");
$connection2 = new Connection("127.0.0.1", 50000,
    "monetdb", "monetdb", "myDatabase");
$connection3 = new Connection("127.0.0.1", 50000,
    "monetdb", "monetdb", "myDatabase");

$result1 = $connection1->Query("...");
$result2 = $connection2->Query("...");
$result3 = $connection3->Query("...");

API Reference

Class Summary
Connection Class for encapsulating a connection to a MonetDB server.
Response This class represents a response for an SQL query or for a command. In case of a 'select' query, this class can be iterated through, using a 'foreach' loop. The records are returned as associative arrays, indexed by the column names.
StatusRecord This class shares the information returned by MonetDB about the executed queries. Like execution time, number of rows affected, etc. Note that only specific fields are populated for specific queries, the others remain NULL.
ColumnInfo This class contains information about the columns of a table response to a 'select' query.

Connection Class

Class for encapsulating a connection to a MonetDB server.

Method Documentation
__construct Create a new connection to a MonetDB database.

@param string $host : The host of the database. Use '127.0.0.1' if the DB is on the same machine.
@param int $port : The port of the database. For MonetDB this is usually 50000.
@param string $user : The user name.
@param string $password : The password of the user.
@param string $database : The name of the database to connect to. Don't forget to release and start it.
@param string $saltedHashAlgo = "SHA1" : Optional. The preferred hash algorithm to be used for exchanging the password. It has to be supported by both the server and PHP. This is only used for the salted hashing. Another stronger algorithm is used first (usually SHA512).
@param bool $syncTimeZone = true : If true, then tells the clients time zone offset to the server, which will convert all timestamps is case there's a difference. If false, then the timestamps will end up on the server unmodified.
@param int $maxReplySize = 200 : The maximal number of tuples returned in a response. A higher value results in smaller number of memory allocations and string operations, but also in higher memory footprint.
Close Close the connection
Query Execute an SQL query and return its response. For 'select' queries the response can be iterated using a 'foreach' statement. You can pass an array as second parameter to execute the query as prepared statement, where the array contains the parameter values. SECURITY WARNING: For prepared statements in MonetDB, the parameter values are passed in a regular 'EXECUTE' command, using escaping. Therefore the same security considerations apply here as for using the Connection->Escape(...) method. Please read the comments for that method.

@param string $sql
@param array $params = null : An optional array for prepared statement parameters. If not provided (or null), then a normal query is executed instead of a prepared statement. The parameter values will be converted to the proper MonetDB type when possible. See the relevant section of README.md about parameterized queries for more details.
@return Response
QueryFirst Execute an SQL query and return only the first row as an associative array. If there is more data on the stream, then discard all. Returns null if the query has empty result. You can pass an array as second parameter to execute the query as prepared statement, where the array contains the parameter values.

@param string $sql
@param array $params = null : An optional array for prepared statement parameters. If not provided (or null), then a normal query is executed instead of a prepared statement. See the 'Query' method for more information about the parameter values.
@return string[] -or- null
Command Send a 'command' to MonetDB. Commands are used for configuring the database, for example setting the maximal response size, or for requesting unread parts of a query response ('export').

@param string $command
@param bool $noResponse = true : If true, then returns NULL and makes no read to the underlying socket.
@return Response -or- null
Escape Escape a string value, to be inserted into a query, inside single quotes. The following characters are escaped by this method: backslash, single quote, carriage return, line feed, tabulator, null character, CTRL+Z. As a security measure this library forces the use of multi-byte support and UTF-8 encoding, which is also used by MonetDB, avoiding the SQL-injection attacks, which play with differences between character encodings.

@param string $value
@return string
ClearPsCache Clears the in-memory cache of prepared statements. This is called automatically when an error is received from MonetDB, because that also purges the prepared statements and all session state in this case.
GetMaxReplySize The maximal number of tuples returned in a response.

@return int

Response Class

This class represents a response for an SQL query or for a command. In case of a 'select' query, this class can be iterated through, using a 'foreach' loop. The records are returned as associative arrays, indexed by the column names.

Method Documentation
Discard Read through all of the data and discard it. Use this method when you don't want to iterate through a long query, but you would like to start a new one instead.
IsDiscarded Returns true if this response is no longer connected to an input TCP stream.

@return boolean
GetColumnNames Returns the names of columns for the table. If you would like to have more information about the columns than just their names, then use the 'GetColumnInfo()' method.

@return string[]
Fetch Returns the next row as an associative array, or null if the query ended.

@return array -or- null
GetStatusRecords Returns one or more Status records that tell information about the queries executed through a single request.

@return StatusRecord[]
GetColumnInfo Returns an array of ColumnInfo objects that contain information about the columns of a table response to a 'select' query.

@return ColumnInfo[]

StatusRecord Class

This class shares the information returned by MonetDB about the executed queries. Like execution time, number of rows affected, etc. Note that only specific fields are populated for specific queries, the others remain NULL.

Method Documentation
GetQueryType Returns a short string which identifies the type of the query.

@return string
GetDescription Returns a user-friendly text which describes the effect of the query.

@return string
GetQueryTime The time the server spent on executing the query. In milliseconds.

@return float -or- null
GetSqlOptimizerTime SQL optimizer time in milliseconds.

@return float -or- null
GetMalOptimizerTime MAL optimizer time in milliseconds.

@return float -or- null
GetAffectedRows The number of rows updated or inserted.

@return integer -or- null
GetTotalRowCount The total number of rows in the result set. This includes those rows too, which are not in the current response.

@return integer -or- null
GetAsText Get a description of the status response in a human-readable format.

@return string
GetPreparedStatementID Get the ID of a created prepared statement. This ID can be used in an 'EXECUTE' statement, but only in the same session.

@return integer -or- null
GetResultID Returns the ID of the result set that is returned for a query. It is stored on the server for this session, and parts of it can be queried using the "export" command.

@return integer -or- null
GetAutoCommitState Available after "start transaction", "commit" or "rollback". Tells whether the current session is in auto-commit mode or not.

@return boolean -or- null
GetRowCount The number of rows (tuples) in the current response only.

@return integer -or- null
GetColumnCount Column count. If the response contains tabular data, then this tells the number of columns.

@return integer -or- null
GetQueryID Query ID. A global ID which is also used in functions such as sys.querylog_catalog().

@return integer -or- null
GetLastInsertID The last automatically generated ID by an insert statement. (Usually auto_increment) NULL if none.

@return integer -or- null
GetExportOffset The index (offset) of the first row in a block response. (For an "export" command.)

@return integer -or- null

ColumnInfo Class

This class contains information about the columns of a table response to a 'select' query.

Method Documentation
GetTableName The name of the table the field belongs to, or the name of a temporary resource if the value is the result of an expression.

@return string
GetColumnName Column name.

@return string
GetType The SQL data type of the field.

@return string
GetLength A length value that can be used for deciding the width of the columns when rendering the response.

@return integer

Development setup through the Docker image

  • Build the Docker image:

      docker/build.sh

  • Create the Docker container with Apache listening on port 9292:

      docker/create.sh

  • Login into the container as the host user or as root:

      docker/login.sh
  docker/root_login.sh

  • When you don't need the MonetDB-PHP container anymore, you can get rid of it easily: (this also removes the unused images)

      docker/cleanup.sh

Running the integration tests

  • Login into the running container with docker/login.sh.
  • Execute the tests with:
vendor/bin/phpunit tests

The output should be similar to:

$ vendor/bin/phpunit tests
PHPUnit 8.5.33 by Sebastian Bergmann and contributors.

........................                                          24 / 24 (100%)

Time: 971 ms, Memory: 4.00 MB

OK (24 tests, 50 assertions)

IDE setup