README

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.

MonetDB-PHP
Table of contents
Installation with Composer (PHP 8.x)
Usage without installation
Installation for PHP 7.2 or above.
Examples
API Reference
Development setup through the Docker image
Running the integration tests
IDE setup

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.

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

Connection Class

Class for encapsulating a connection to a MonetDB server.

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.

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.

ColumnInfo Class

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

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)

tbolner / monetdb-php

Maintainers

Details