# MySQL client

## Introduction

[Erlang MySQL client](http://sourceforge.net/projects/erlmysql) for MySQl server
is a native client. It means the client is directly connecting to the server and using
low level (network socket level) connection protocol. So advantage of the client as a native
is speed and performance. Disadvantage is that client design tightly coupled with MySQL 
connection protocol, but MySQL developing team is conservative enough with it.

## Design

The MySQL client is implemented as Erlang/OTP application. Root supervisor of the client
manages a set of datasource processes each of them can be pointed to different MySQl servers
or to the same but differently configured. To create a datasource process (or in other word datasource object) 
we can use function from the client API:

``` Erlang
my:start_client(),
my:new_datasource(my_ds, #datasource{
  host = "localhost",
  port = 3306,
  database = "",
  user = "root",
  password = "root"}
)
```

Datasource is actually connection pool process that is managing a set of connections to server.
The connection pool (or datasource) stores a reusable connection `gen_server` processes and 
is tracking their life cycles. The client is using external 
[Erlang Resource Pool](https://sourceforge.net/projects/erlpool) project for connection pool 
implementation with customized `connection_factory.erl`. When some working process tries to borrow
connection from connection pool then pool is trying to provide an existed but idle connection process.
If set of idle connections is empty then connection pool creates new one. Working process returns
connection to pool after query completed.

## Supported features

The client supports the following client features of MySQL server:
* configure client connection using #client_options{} record
* compression mode for transfer data through connection
* transfer of very long packets as a multi sub-packets
* prepared statements execution
* transfer a long statement parameters (blob parameters)
* fetch cursor on server previously generated by prepared statement
* transaction execution

## Getting started

### Obtain connection
We have already created datasource object above. So now we can obtain connection to our server:
``` Erlang
Conn = datasource:get_connection(my_ds)
```
Command above retrieves connection from pool and if connection pool has no idle connection 
then connection factory creates new one.

### Create database
Let's create new database (or schema) on the server:
``` Erlang
connection:execute_query(Conn, "CREATE DATABASE IF NOT EXISTS testDB")
```

### Create table
Next step is a table creation:
``` Erlang
connection:execute_query(Conn, 
  "CREATE TABLE testDB.sample_table ("
    "id bigint(20) NOT NULL AUTO_INCREMENT,"
    "name varchar(45) DEFAULT NULL,"
    "PRIMARY KEY (id)"
  ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"
)
```

### Insert and select
It is time to insert something to new table:
``` Erlang
connection:execute_query(Conn, 
  "INSERT INTO testDB.sample_table(name) "
  "VALUES ('Alex'), ('John')"
)
```
And finally we can extract this rows from the table:
``` Erlang
{Metadata, Rows} = connection:execute_query(Conn, "SELECT * FROM testDB.sample_table"),
io:format("Result: ~p~n", [Rows])
```

We can see on console something like this:
``` Erlang
Result: [{rs_row_data, [1,"Alex"]},{rs_row_data, [2,"John"]}]
```

Query returns a tuple that contains two members. First one is metadata record 
represented information about table fields and second one is list of records represented
rows of query result. Each row contains values of fields of SELECT statement. In our case
there are 'id' and 'name' fields.

## Prepared statement

Let's prepare a very simple statement:
``` Erlang
Handle = connection:get_prepared_statement_handle(
  Conn,                                               % - connection handle
  "SELECT name FROM testDB.sample_table WHERE id = ?" % - SQL prepared statement with ? placeholders
)
```

The function returns prepared statement handle for followed operations. Now execute it:
``` Erlang
{Metadata, Rows} = connection:execute_statement(
  Conn,                    % - connection handle
  Handle,                  % - prepared statement handle
  [ ?MYSQL_TYPE_LONGLONG ],  % - list of parameter types
  [ 1 ]                      % - list of parameter values, corresponded with the statement ? placeholders.
),
io:format("Result: ~p~n", [Rows])
```

We need to pass a statement's parameter types and values to function that executes prepared statement. Result has to be:
``` Erlang
Result: [{rs_row_data, [1,"Alex"]}]
```

## Compressed mode

Compression protocol is supported by the client if given instance of MySQL supports it. 
To activate this feature set compress field in #client_options record and pass the record 
to datasource definition when create new datasource object. 
``` Erlang
my:new_datasource(
  #datasource{
    name = my_ds,
    host = "localhost",
    port = 3306,
    database = "",
    user = "root",
    password = "root",
    flags = #client_options{compress=1}
  }
),
Conn = connection:get_connection(my_ds)
```

Then established connection `Conn` allows to talk to server with compression. 
Note that when we are using compression we win a packets size but lost a processor time.

## Blob transfer

MySQL client/server protocol allows transfer data for BLOB type field as a separate
operation. Suppose we have a column of LONGBLOB type named as `longblob_col` in our sample_table above:
``` Erlang
  CREATE TABLE testDB.sample_table (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    longblob_col longblob,
    name varchar(45) DEFAULT NULL,
    PRIMARY KEY (id)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
```

And we need to update the field with huge chunk of data. The command is a part of
prepared statement execution cycle, so we need to prepare statement first:
``` Erlang
  Handle = connection:get_prepared_statement_handle(Conn, 
    "UPDATE testDB.sample_table SET longblob_col= ? WHERE id = ?"
  )
```
After that we can send to server long block of data that has size of 1000000 bytes:
``` Erlang
connection:send_statement_long_parameter(Conn, Handle, 0, <<16#AA:8000000>>),
```
Third parameter of the function is a position number of given parameter 
in prepared statement sentence. We can apply the `send_statement_long_parameter/4` a few times 
and all chunks will be merged in one huge data block. Now as we complete a sending 
of statement parameter value to server we can finally execute the statement:
``` Erlang
connection:execute_statement(Conn, Handle, [?LONG_BLOB, ?LONG], [null, 1]),
```
During execution we do not need to send blob parameter value, because it already is in the server.

## Cursor fetch

After a statement is prepared we can execute it under two modes. First kind of execution
is default and immediately returns a result set of the query. Second one
does not return a result set but create a cursor on the server side. To retrieve a data from
this cursor we can use fetch_statement command like this:
``` Erlang
Handle = connection:get_prepared_statement_handle(
  Conn, 
  "SELECT * FROM testDB.sample_table WHERE id < ?"
),  

{Metadata,_} = connection:execute_statement(
  Conn, 
  Handle, 
  [ ?LONGLONG ], 
  [ 1 ], 
  ?CURSOR_TYPE_READ_ONLY, 
  true
),

{_,R} = connection:fetch_statement(Conn, Handle, Metadata, 2),

io:format("Result: ~p~n", [R])
```

Command to execute the prepared statement in cursor mode and does not return any result but we need
get a metadata record for the following command. The next line is a fetch command that return 2 
first rows from the server side cursor. A fetch command returns only binary packets of result set but
skips field metadata. So we have to pass metadata record as a parameter to fetch command
due to properly parse rows data. Result has to be:
``` Erlang
Result: [{rs_row_data, [2,"John"]}]
```

## Utility
### Client Side Cursor

Cursor is utility to help manipulate of result set data retrieved from DB server. Cursor is implemented as a process 
(you can think about the cursor as an object).
This is more convenient way to retrieve data from response. We can create cursor object from response data on client side
and use this cursor to navigate through set of records and fields.
``` Erlang
Result_set = connection:execute_query(Cntn, "SELECT * FROM testDB.sample_table"),
Cursor = cursor:new(Result_set).
```

To navigate to next row we have to call cursor:next function or set desired index of row.
``` Erlang
cursor:next(Cursor),     %% step forward
cursor:set(Cursor, 1),   %% set on cursor beginning
```
Now we can get value of a field:
``` Erlang
Name1 = cursor:get(Cursor, "name"),  %% Name1 = "Alex"
Id1 = cursor:get(Cursor, "id"), %% Id1 = 1
```

Move to next row and get a field by index in current row:
``` Erlang
cursor:next(Cursor),
Name2 = cursor:get(Cursor, 2), %% Name2 = "John"
```

Other operations under cursor are:
  * **reset** - set cursor pointer to a beginning of the cursor.
  * **next** - move pointer to the next position.
  * **set** - set pointer to given position.
  * **skip** - skip a few position ahead.
  * **back** - move pointer to the previous position.

Let us investigate a 'foreach' function.
The function returns list of values for given field from all rows in cursor:
``` Erlang
Names_list = cursor:foreach(Cursor, "name"),  %% Names_list = ["Alex","John"]
```

Cursor object owns to process created it. Other processes cannot access the cursor for safety so cursor cannot 
be used concurrently.

## Conclusion

The Erlang MySQL client (or driver) is similar to MySQL Connector/C driver and very closely 
follows the Connector interface.