snowflakedb/pdo_snowflake

PHP PDO driver for snowflake

pdo_snowflake's Issues

Change type names and enums to use SF_ instead of SNOWFLAKE_

We want to be consistent and so we're using the SF_ designation.

Add session parameter support

When connecting to Snowflake, session parameters, e.g., autocommit, timezone, etc, can be specified.

Implement Close session in snowflake_term

C99 instead of C89?

Apparently sticking to C89 is pain as we implicitly use convenient C99 features.

• __VA_ARGS__ macro in logger
• Double slash comments //
• Declaring variables in the middle of code instead of the beginning.

If I add -std=c89, bunch of errors show up, and some are very difficult to update.

The main concern was C99 code would not be compiled by Visual Studio, but it appeared VS 2015 already has most of C99 features and even C++11.
https://msdn.microsoft.com/en-us/library/hh409293.aspx

Maybe we should change the policy.

Honestly I underestimated C89 limitation. It just reminds me painful old days.

Coding Style: Four space tabs and braces at the end style

Agreed to use four space tabs and braces at the end style.

Use TRAVIS_JOB_ID to set the schema

In order for the tests to run concurrently, each job should use different schema.

affected rows support

DML should return the number of affected rows.

Add bindColumn support

Goal

• Fetch data and store into variables

Support Renew Session

We need to be able to renew a client session in the background without any input from the client.

Add logging

Need to integrate Snowflake Connector for C logger with PDO.
And PHP ini can specify the log file name and severity.

Implement Ctrl+C to stop a query support

Goal

• Provide a signal hander for the applications to stop a query.
• Provide an example of Ctrl+C to stop a query.

TBD:

• Do we want to support Ctrl+C behavior by default.

Add Support for Binding Arrays of Input/Output Params

Someone (Saber or Markus) mentioned to me that this would be a nice feature in the C API. I think this would be a relatively trivial addition and quite convenient for developers who use the C API. I see two options for this:

1. (My first choice) We add a number of parameters field to the binding functions that will tell us if someone passes in a single binding structs or an array of structs.

2. (An alternative) Create new functions called "snowflake_bind_(input|output)_array" that deal with binding arrays of binding structs.

Need C Unittest framework

As we have more test cases as examples, we need better test framework and runners to run tests automatically, detect and aggregate errors.

I'm thinking of adding either one of them:
https://libcheck.github.io/check/doc/check_html/
https://cmocka.org/

Change snowflake_stmt_close to snowflake_stmt_term

Add PUT and GET support

Not v1.0 but future version.

Allow to set the statement parameters before query

Due to the fix for #35, all parameters are cleared in query/prepare.
This prevents from setting the statement attribute before query.

can we remove SF_STATE_WARNING?

So far SF_STATE_WARNING is not used, and if no use case is given, let's remove it for now.
Later if we need it, we can add it.

Add large result set support

Goal

• Execute a query select seq8(), randstr(100, random()) from table(generator(rowcount=>1000000)) and fetch results.

Support Transaction

Goal

• begin, commit and rollback support
• Auto commit mode (by default, http://php.net/manual/en/pdo.transactions.php)
• rolback on close if the transaction is still opened.

Implement better error handling in PDO

Snowflake Connector's error should be propagated to PDO better.

Build PDO for Mac

• Build Curl and Openssl on Mac
• Commit the library and headers
• Build PDO for Mac
• Pass all existing tests.

Add len to SF_BIND_OUTPUT and SF_BIND_INPUT

When binding columns/results, max length is provided by the application, but it doesn't necessarily the actual length of value and value may not be null terminated, so SF_BIND_OUTPUT needs len member so that Snowflake client can set it for the application.

Binding parameters is similar. The applications should be able to specify the length.

Review logging

Goals

• Ensure the log includes required info for debug.
• Ensure the log can be enabled and disabled as the customer needs.
• Ensure the log location can be specified.
• Ensure the log doesn't include sensitive information, i.e., password, private key

Add bindParameter support

Goal

• Prepare a query
• Execute a query with parameters

Fix bind parameters

Bind parameters is not working. Fixing.

Implement php_info callbacks

Run Snowflake Connector for C test case on TravisCI

We should run Snowflake Connector for C tests on TravisCI.

Make SNOWFLAKE_STMT/SF_STMT structure reusable

better to make SNOWFLAKE_STMT structure reusable such that the applications won't need to recreate it on every needs.

Add test case including NULL character.

Mostly the code assumes the string data are null terminated. What if NULL is included in the data?
This issue is to add a test case including NULL with appropriate fixes if need.

Support named binding

Looks like we may be able to support named binding.

Reorganize headers

Ideally only one header should be exposed without namespace conflict. The following changes are required:

• Hide cJSON data structure from public
• Hide array_list structure from public
• Hide uuid data structure from public.
• Change header file name starting with "snowflake_" to avoid header name conflict.
• Change public macro, constant values and function names starting with "SF_", "sf_" or "snowflake_"
• Move all other private use headers from the public include folder to internal folder.

can SF_ERROR_CODE and SF_STATUS be combined?

The value 0 is reserved for no error. All other value indicates error.

Support Ctrl+C well

By default, PHP CLI allows the uses to interrupt a script by Ctrl+C. A problem is it leaks lot of memory. Is there any way to gracefully free all memory?

Note this could be PHP fundamental issue. Need further investigation.

What is the difference between snowflake_close and snowflake_term?

snowflake_close is to close a session.
snowflake_term is to wipe out the SNOWFLAKE context.

I don't think we need to separate those two function calls but one should be good enough.

error: duplicate 'const' declaration specifier [-Werror,-Wduplicate-decl-specifier]

Following the steps in the readme i'm encountering the following error. This is a terrible bug report (Sorry) because I don't know what further details to provide.

I successfully built PHP and cloned pdo_snowflake

I did also try omitting -r which (unsurpisingly, I guess) failed on clang: error: no such file or directory: 'libsnowflakeclient/cmake-build/libsnowflakeclient.a'

-- The C compiler identification is AppleClang 7.3.0.7030031
-- The CXX compiler identification is AppleClang 7.3.0.7030031
-- Check for working C compiler: /Library/Developer/CommandLineTools/usr/bin/cc
-- Check for working C compiler: /Library/Developer/CommandLineTools/usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Detecting C compile features
-- Detecting C compile features - done
-- Check for working CXX compiler: /Library/Developer/CommandLineTools/usr/bin/c++
-- Check for working CXX compiler: /Library/Developer/CommandLineTools/usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Detecting CXX compile features
-- Detecting CXX compile features - done
libcurl is located at /usr/local/lib/libcurl.a
libssl is located at /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/deps-build/linux/openssl/lib/libssl.a
libcrypto is located at /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/deps-build/linux/openssl/lib/libcrypto.a
-- Configuring done
-- Generating done
-- Build files have been written to: /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/cmake-build
/usr/local/Cellar/cmake/3.7.1/bin/cmake -H/Users/ntaylor/src/pdo_snowflake/libsnowflakeclient -B/Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/cmake-build --check-build-system CMakeFiles/Makefile.cmake 0
/usr/local/Cellar/cmake/3.7.1/bin/cmake -E cmake_progress_start /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/cmake-build/CMakeFiles /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/cmake-build/CMakeFiles/progress.marks
/Library/Developer/CommandLineTools/usr/bin/make -f CMakeFiles/Makefile2 all
/Library/Developer/CommandLineTools/usr/bin/make -f CMakeFiles/snowflakeclient.dir/build.make CMakeFiles/snowflakeclient.dir/depend
cd /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/cmake-build && /usr/local/Cellar/cmake/3.7.1/bin/cmake -E cmake_depends "Unix Makefiles" /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/cmake-build /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/cmake-build /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/cmake-build/CMakeFiles/snowflakeclient.dir/DependInfo.cmake --color=
Scanning dependencies of target snowflakeclient
/Library/Developer/CommandLineTools/usr/bin/make -f CMakeFiles/snowflakeclient.dir/build.make CMakeFiles/snowflakeclient.dir/build
[  1%] Building C object CMakeFiles/snowflakeclient.dir/lib/client.c.o
/Library/Developer/CommandLineTools/usr/bin/cc  -DLOG_USE_COLOR -I/Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/deps-build/linux/curl/include -I/Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/deps-build/linux/openssl/include -I/Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/include  -g -pthread -fPIC -Werror   -std=gnu90 -o CMakeFiles/snowflakeclient.dir/lib/client.c.o   -c /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/lib/client.c
In file included from /Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/lib/client.c:17:
/Users/ntaylor/src/pdo_snowflake/libsnowflakeclient/lib/connection.h:338:46: error: duplicate 'const' declaration specifier [-Werror,-Wduplicate-decl-specifier]
ARRAY_LIST *json_get_object_keys(const cJSON const *item);
                                             ^~~~~~
1 error generated.
make[2]: *** [CMakeFiles/snowflakeclient.dir/lib/client.c.o] Error 1
make[1]: *** [CMakeFiles/snowflakeclient.dir/all] Error 2
make: *** [all] Error 2```

Map data types between Snowflake and PHP

Goal

• Clarify data type mapping between Snowflake and PHP
  -- Converting from Snowflake to PHP
  -- Converting from PHP to Snowflake

Add memory alloc hook to C API

It is better the application can provide memory alloc hooks.

Implement PingPong

Goal:

• Execute select count(*) from table(generator(timelimit=>60)) successfully

This requires to ping result every N seconds.

Not exit from memory alloc failure

Snowflake Client needs auto free memory mode.

PHP has its own memory management APIs, i.e., ecalloc, efree, etc, so that allocated memories will be freed up in the end of request if it wants.
However, when a connection is not closed, while all memories allocated by PDO layers will be freed automatically, the memories in Snowflake Client layer won't be, because those memories are allocated SF_CALLOC and SF_FREE that currently simple mapping to the system functions calloc and free. This will yield memory leak.

Snowflake Client/Connector should have a mode that does similar to PHP memory manager such that the memories are automatically freed in each of snowflake_*term function calls.

Performance tuning on snowflake_fetch

added

diff --git a/libsnowflakeclient/CMakeLists.txt b/libsnowflakeclient/CMakeLists.txt
index 678c194..8b24362 100644
--- a/libsnowflakeclient/CMakeLists.txt
+++ b/libsnowflakeclient/CMakeLists.txt
@@ -8,7 +8,7 @@ project(snowflakeclient)
 add_definitions(-DLOG_USE_COLOR)
 
 set(CMAKE_VERBOSE_MAKEFILE ON)
-set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -g -pthread -fPIC -Wall")
+set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -g -pg -pthread -fPIC -Wall")
 
 set(SOURCE_FILES
         include

and ran ex_large_result_set and found

Flat profile:

Each sample counts as 0.01 seconds.
  %   cumulative   self              self     total           
 time   seconds   seconds    calls  ms/call  ms/call  name    
 99.93    178.88   178.88   199693     0.90     0.90  cJSON_GetArraySize
  0.02    178.91     0.03   199738     0.00     0.00  cJSON_DetachItemViaPointer
  0.02    178.94     0.03   199723     0.00     0.90  snowflake_fetch
  0.01    178.96     0.02   199746     0.00     0.00  clear_snowflake_error
  0.01    178.97     0.01   399516     0.00     0.00  array_list_get
  0.01    178.98     0.01   399377     0.00     0.00  get_array_item
  0.01    178.99     0.01   181966     0.00     0.00  parse_array
  0.00    179.00     0.01   364887     0.00     0.00  cJSON_New_Item
  0.00    179.00     0.01                             cJSON_InitHooks
...

Since cJSON_GetArraySize is used to check if the size of result is 0 or not, it can be simplified.

Chunk Downloader Bugs

Markus found 2 bugs in the chunk downloader code:

1. The chunk_downloader_term function either returns false or unknown value. It should return true on success.

2. The signature of the json_get_object_keys function is 'const cJSON const *item' when it should be 'const cJSON * const item'.

3. We should also compile with '-Werror' (at least with libsnowflakeclient) to turn warnings into errors.

Add sql_len parameters to snowflake_prepare, snowflake_query, etc

Aside from a pointer to the command/SQL text, a SQL length parameter is required.

Build PDO for Windows

• Build Curl and Openssl for Windows
• Commit library and headers
• Build PDO for Windows
• Pass all existing tests

Add a function returning column info

stmt->desc should be returned by a function.

Add snowflake_stmt_desc to return SF_COLUMN_DESC

In order to get the metadata for fetched data, we need to have a function snowflake_stmt_desc to return SF_COLUMN_DESC.

128 bit integer and BigDecimal support

Numeric values over precision 18 won't fit into int64. We may need to considering support __128int, decimal128, etc.

CA certs

We use our own cacerts as often the system's ca certs are not reliable.
When bundling the cacert.pem, there are couple of options:

• Bundle in the package so that the installer puts cacert.pem and add the config.
• Transform cacert.pem into a large string so that the code can parse on memory.

In either case, the applications should be able to reconfigure the cacert.pem

Implement Connection with user name and password and run select 1

Goal:

• Execute the connection successfully:
  $dbh = new PDO("snowflake:account=testaccount", "user", "password");
• Implement getAvailableDrivers()
• Implement query() to get the result from select 1.

Integrate with Valgrind