PHP PDO driver for Snowflake

https://travis-ci.org/snowflakedb/pdo_snowflake.svg?branch=master

Private Preview. Linux Only. No PHP 5 support. PHP 7.2 only.

Configuring Environment

PHP Versions and Extensions

PHP 7.2 is supported. The following extensions are required:

pdo

json

If you're not building PHP from source, you will also need to install the corresponding PHP development package for your OS/environment

Application Server (Optional)

If the PHP is used along with an application server, e.g., nginx, apache, which is the common use case, install them and configure the PHP handler accordingly so that the PHP file can run on the web. For example, Nginx enables the PHP handler by adding the following config in /etc/nginx/sites-available/default:

server {
    ... the standard settings ...

    # PHP handler
    location ~ \.php$ {
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;
        fastcgi_index index.php;
        include fastcgi_params;
    }
}

where PHP 7.2 and the corresponding PHP-FPM (https://php-fpm.org/) package are used, for example.

Restart Nginx and PHP-FPM services.

service nginx restart
service php7.2-fpm restart

Add a PHP test code test.php to the home location of an application server.

<?php phpinfo(); ?>

Ensure it can run and the output includes both pdo and json extensions.

curl http://localhost/test.php | grep -E "(pdo|json)"

Installing PDO driver for Snowflake

Separate instructions exist for Windows, Linux, macOS

Linux

There are two required files for you to copy:

pdo_snowflake.so
cacert.pem

Copy pdo_snowflake.so to the same location as pdo.so where all PHP extentions reside.

Copy cacert.pem to the PHP config directory. For example, PHP-FPM version 7.2 on Ubuntu12 has /etc/php/7.2/fpm/conf.d/ for the extensions.

Note

If you don't have pdo_snowflake.so, build it following the instruction below.

cp cacert.pem /etc/php/7.2/fpm/conf.d/

Add a config file /etc/php/7.2/fpm/conf.d/20-pdo_snowflake.ini including the following contents to the PHP config directory.

extension=pdo_snowflake.so
pdo_snowflake.cacert=/etc/php/7.2/fpm/conf.d/cacert.pem
# pdo_snowflake.logdir=/tmp     # location of log directory
# pdo_snowflake.loglevel=DEBUG  # log level

Restart Nginx and PHP-FPM services. For example:

service nginx restart
service php7.2-fpm restart

Ensure phpinfo() function return the output including pdo_snowflake.

curl http://localhost/test.php | grep -E "(pdo|json|snowflake)"

Note

We have not finalized what package would be the best for binary distribution. So far I'm trying to get pecl account but have not got one yet. Any suggestion is welcome.

Windows

There are two required files for you to copy:

php_pdo_snowflake.dll
cacert.pem

Copy php_pdo_snowflake.dll to the same location as php_pdo.dll where all PHP extensions reside (usually the ext folder in your PHP installation).

Copy cacert.pem to the PHP config directory. For example, PHP version 7.2 installed at C:\` on Windows 10 has :code:`C:\php\php.ini for the extensions.

Note

If you don't have php_pdo_snowflake.dll, build it following the instruction below.

Add the following lines to your php.ini file:

extension=php_pdo_snowflake.dll
pdo_snowflake.cacert=C:\php\cacert.pem
# pdo_snowflake.logdir=C:\path\to\logdir     # location of log directory
# pdo_snowflake.loglevel=DEBUG  # log level

Restart your PHP server and then you should see pdo_snowflake as a PHP extension

Usage

Limitations

Timestamp support on Windows is limited to values between the dates 1/1/1970 and 1/1/2038. Trying to fetch values outside of this range will result in an empty value being returned
Named placeholders (placeholders in SQL queries of the form first_name:) are not supported at this time. Positional placeholders (placeholders in SQL queries of the form ?) are supported.

Connection String

Create a database handle with connection parameters:

$dbh = new PDO("snowflake:account=testaccount", "user", "password");

For non-US-West region, specify region parameter or append it to account parameter.

$dbh = new PDO("snowflake:account=testaccount.us-east-1", "user", "password");
$dbh = new PDO("snowflake:account=testaccount;region=us-east-1", "user", "password");

OCSP Checking

OCSP (Online Certificate Status Protocol) checking is set per PDO connection and enabled by default. To disable OCSP checking, set insecure_mode=true in the DSN connection string. Example shown here:

$dbh = new PDO("snowflake:account=testaccount;insecure_mode=true", "user", "password");

Query

Here is an example of fetch a row:

$account = "<account_name>";
$user = "<user_name>";
$password = "<password";

$dbh = new PDO("snowflake:account=$account", $user, $password);
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
echo "Connected\n";

$sth = $dbh->query("select 1234");
while ($row=$sth->fetch(PDO::FETCH_NUM)) {
    echo "RESULT: " . $row[0] . "\n";
}
$dbh = null;
echo "OK\n";

Build and Test

Build and Install PHP from source on Linux (Optional)

If PHP is not available, download and build from the PHP source code.

# Go to http://php.net/releases/
# Download php source code and copy to $WORKSPACE, for example
cd $WORKSPACE

Set PHP version to the environment variable. For example, set SF_PHP_VERSION to 7.2.24 if the downloaded PHP version is 7.2.24.

export SF_PHP_VERSION=7.2.24

Extract and build PHP:

cd $WORKSPACE
rm -rf $WORKSPACE/php-$SF_PHP_VERSION-src
rm -rf $WORKSPACE/install-php-$SF_PHP_VERSION
tar xvfj php-$SF_PHP_VERSION.tar.bz2
cd php-$SF_PHP_VERSION
./configure \
    --prefix=$WORKSPACE/install-php-$SF_PHP_VERSION \
make
make install

Build PDO Driver on Linux

Set PHP_HOME to the base directory of the PHP. For example, if you built PHP, do this:

export PHP_HOME=$WORKSPACE/install-php-$SF_PHP_VERSION

or do this if the PHP is already installed in the system.

export PHP_HOME=/usr

where $PHP_HOME/bin is referred to run phpize:

Clone the this repository and run the build script.

git clone https://github.com/snowflakedb/pdo_snowflake.git
cd pdo_snowflake
./scripts/build_pdo_snowflake.sh

Run the following command to check if PHP PDO Driver for Snowflake is successfully loaded in memory.

$PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so -m | grep pdo_snowflake

Note

As the build requires a special link process, a simple sequence of phpize followed by make doesn't work. See the build script for the detail.

Build and Install PHP on Windows (Optional)

A set of scripts has been created in this repo to facilitate setting up PHP on Windows:

setup_php_sdk.bat <arch[x64,x86]> <build[Release,Debug]> <visual studio version[VS14,VS15]> <path to PHP SDK>
run_setup_php.bat <arch[x64,x86]> <build[Release,Debug]> <visual studio version[VS14,VS15]> <full PHP version> <path to PHP SDK>

First, we are going to setup the PHP SDK tools:

-- Clone and go to top level of repository
git clone https://github.com/snowflakedb/pdo_snowflake.git
cd pdo_snowflake
.\scripts\setup_php_sdk.bat x64 Release VS15 C:\php-sdk

Now we are going to download (including dependencies) and build PHP:

.\scripts\run_setup_php.bat x64 Release VS15 7.2.24 C:\php-sdk

Build PDO Driver on Windows

Run the following command in the top level of this repo to build the PDO driver on Windows:

run_build_pdo_snowflake.bat <arch[x64,x86]> <build[Release,Debug]> <visual studio version[VS14,VS15]> <full PHP version> <path to PHP SDK>

Example:

run_build_pdo_snowflake.bat x64 Release VS15 7.2.24 C:\php-sdk

Run the following command to check if PHP PDO Driver for Snowflake is successfully loaded in memory.

C:\php\php.exe -dextension=ext\php_pdo_snowflake.dll -m

Prepare for Test

Create a parameter file parameters.json under pdo_snowflake directory:

{
    "testconnection": {
        "SNOWFLAKE_TEST_USER":      "<your_user>",
        "SNOWFLAKE_TEST_PASSWORD":  "<your_password>",
        "SNOWFLAKE_TEST_ACCOUNT":   "<your_account>",
        "SNOWFLAKE_TEST_WAREHOUSE": "<your_warehouse>",
        "SNOWFLAKE_TEST_DATABASE":  "<your_database>",
        "SNOWFLAKE_TEST_SCHEMA":    "<your_schema>",
        "SNOWFLAKE_TEST_ROLE":      "<your_role>"
    }
}

Call env.sh script to set the test connection parameters in the environment variables.

./scripts/env.sh && env | grep SNOWFLAKE_TEST > testenv.ini

Proxy

PHP PDO Driver for Snowflake supports HTTP and HTTPS proxy connections using environment variables. To use a proxy server configure the following environment variables:

http_proxy
https_proxy
no_proxy

export http_proxy="[protocol://][user:password@]machine[:port]"
export https_proxy="[protocol://][user:password@]machine[:port]"

More info can be found on the libcurl tutorial page.

Run Tests

REPORT_EXIT_STATUS=1 NO_INTERACTION=true make test

Profile

You can use callgrind to profile PHP PDO programs. For example, run tests/selectnum.phpt testcase using valgrind along with callgrind option.

valgrind --tool=callgrind $PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so tests/selectnum.phpt
callgrind_annotate callgrind.out.*

Check memory leak by valgrind

Use valgrind to check memeory leak. Both C API and PHP PDO can run along with valgrind. For example, run tests/selectnum.phpt testcase using valgrind by the following command.

valgrind --leak-check=full $PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so tests/selectnum.phpt

and verify no error in the output:

ERROR SUMMARY: 0 errors from 0 contexts ...

Additional Notes

Test Framework

The PHP PDO Snowflake driver uses phpt test framework. Refer the following documents to write tests.

Trouble Shootings

Cannot load module 'pdo_snowflake' because required module 'pdo' is not loaded

In some environments, e.g., Ubuntu 16, when you run make test, the following error message shows up and no test runs.

PHP Warning:  Cannot load module 'pdo_snowflake' because required module 'pdo' is not loaded in Unknown on line 0

Ensure the php has PDO:

$ php -i | grep -i "pdo support"
PDO support => enabled

If not installed, install the package.

Locate pdo.so under /usr/lib and specify it in phpt files, e.g.,

--INI--
extension=/usr/lib/php/20170718/pdo.so
pdo_snowflake.cacert=libsnowflakeclient/cacert.pem
pdo_snowflake.logdir=/tmp
pdo_snowflake_loglevel=DEBUG

Where is the log files?

The location of log files are specified by the parameters in php.ini:

extension=pdo_snowflake.so
pdo_snowflake.cacert=/etc/php/7.2/fpm/conf.d/cacert.pem
pdo_snowflake.logdir=/tmp     # location of log directory
pdo_snowflake.loglevel=DEBUG  # log level

where pdo_snowflake.loglevel can be TRACE, DEBUG, INFO, WARN, ERROR and FATAL.

brettpappas/pdo_snowflake

PHP PDO driver for Snowflake

Configuring Environment

PHP Versions and Extensions

Application Server (Optional)

Installing PDO driver for Snowflake

Linux

Windows

Usage

Limitations

Connection String

OCSP Checking

Query

Build and Test

Build and Install PHP from source on Linux (Optional)

Build PDO Driver on Linux

Build and Install PHP on Windows (Optional)

Build PDO Driver on Windows

Prepare for Test

Proxy

Run Tests

Profile

Check memory leak by valgrind

Additional Notes

Test Framework

Trouble Shootings

Cannot load module 'pdo_snowflake' because required module 'pdo' is not loaded

Where is the log files?