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.

Installation with Composer (PHP 8.x)

This library is available on Packagist at:

https://packagist.org/packages/tbolner/monetdb-php

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

IDE: Visual Studio Code
Plugins:
Plugins for the Monet-Explorer project:
- C/C++ IntelliSense, debugging, and code browsing. (Microsoft)
- Doxygen Documentation Generator (Christoph Schlosser)

Name		Name	Last commit message	Last commit date
Latest commit History 145 Commits
.github/workflows		.github/workflows
.vscode		.vscode
Examples		Examples
docker		docker
protocol_doc		protocol_doc
src		src
tests		tests
.gitattributes		.gitattributes
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
composer.json		composer.json
composer.lock		composer.lock
gen_doc.php		gen_doc.php

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

MonetDB-PHP

Table of contents

Installation with Composer (PHP 8.x)

Usage without installation

Installation for PHP 7.2 or above.

Examples

Example 1: Simple query

Example 2: Get execution stats

Example 3: Parameterized query with prepared statement

Example 4: Using escaping

Example 5: Renaming fields and using column info

Example 6: Query the first record only

Example 7: Transactions

Example 8: Importing data the fastest way

Example 9: Using multiple connections

API Reference

Connection Class

Response Class

StatusRecord Class

ColumnInfo Class

Development setup through the Docker image

Running the integration tests

IDE setup

About

Releases 10

Packages

Contributors 5

Languages

License

MonetDB/MonetDB-PHP

Folders and files

Latest commit

History

Repository files navigation

MonetDB-PHP

Table of contents

Installation with Composer (PHP 8.x)

Usage without installation

Installation for PHP 7.2 or above.

Examples

Example 1: Simple query

Example 2: Get execution stats

Example 3: Parameterized query with prepared statement

Example 4: Using escaping

Example 5: Renaming fields and using column info

Example 6: Query the first record only

Example 7: Transactions

Example 8: Importing data the fastest way

Example 9: Using multiple connections

API Reference

Connection Class

Response Class

StatusRecord Class

ColumnInfo Class

Development setup through the Docker image

Running the integration tests

IDE setup

About

Topics

Resources

License

Stars

Watchers

Forks

Releases 10

Packages 0

Contributors 5

Languages

Packages