Fork me on GitHub

Database

At the moment only MySQL and its variants MariaDB & Percona Server are supported.
Mysql connection are lazy loaded.

Simple DB Setup

In order to connect to databse, conigure dsn string in configuration.

        
            hathoora:
                database:
                    default: mysql://dbuser:dbpassword@dbhost:3306/dbname
        
    

Then to use:

        
            // db connections are lazy loaded
            $db = \hathoora\database\dbAdapter::getConnection(); // getConnection() assumes default database configuration
            try
            {
                $arr = $db->fetchArray('SELECT NOW();');
                print_r($arr);
            }
            catch (\Exception $e)
            {
                echo $e->getMessage();
            }
        
    

Multiple DB Setup

In order to connect to multiple databse, conigure dsn strings in configuration.

        
            hathoora:
                database:
                    default: mysql://dbuser:dbpassword@dbhost:3306/dbname
                    db2: mysql://dbuser:dbpassword@dbhost:3306/dbname
        
    

Then to use:

        
           // db connections are lazy loaded
            $db = \hathoora\database\dbAdapter::getConnection(); // getConnection() assumes default database configuration
            $db2 = \hathoora\database\dbAdapter::getConnection('db2');


            try
            {
                $arr = $db->fetchArray('SELECT NOW();');
                print_r($arr);
            }
            catch (\Exception $e)
            {
                echo $e->getMessage();
            }


            // db connections are lazy loaded
            try
            {
                $r = $db2->fetchValue('SELECT NOW();');
                print_r($r);
            }
            catch (\Exception $e)
            {
                echo $e->getMessage();
            }
        
    

Advanced DB Setup

For more complex database connections with multiple databases for read/write consider the following configuration:

        
            database:
                # simple dsn
                default: mysql://dbuser:dbpassword@dbhost:3306/dbname
                db2: mysql://dbuser:dbpassword@dbhost:3306/dbname

                # Advanced configuration:
                # If you have multiple database servers for read/write then this might be a better options.
                dbPool1:

                    # Failover logic: When a server becomes unavailable then following logics is applied:
                    #       default logic:  In default logic
                    #           Write: If master server is not reachable for write, then next writeable master (if any) will be used based on weight.
                    #           Read: If slave server is not reachable for reads, the next slave (if any) will be used based on weight.
                    #               1.  If there are no slave servers available then master read only server (if any) will be used based on weight.
                    #               2.  If there is still no read only master server, then next master with allow_read (if any) will be used based on weight
                    #       TODO custom logic: An array containing class and method to call. User can specify which db server to use.
                    #           This gives user the flexibility to pick a db based on  db health, concurrent threads etc..
                    #           Format is [\class, method]
                    failover: default

                    #list of servers in this pool
                    servers:

                        dbMaster1:
                            dsn: mysql://dbuser:dbpassword@dbhost:3306/dbname

                            # role - In advance db setup, there are two types of roles:
                            #   master - used for write (and some occasions for read, keep reading)
                            #   slave - used for read
                            role: master

                            # read_only - (default: false) 'readonly' mode would not allow any writes to specified server.  Read only
                            # servers (masters) are usually passive in nature or hot stand by. In read only mode data is not written to
                            # dsn and any query except for SELECT is ignored and result in empty result set
                            read_only: false

                            # allow_read -  (default: true) To allow reads from master when there is no slave and no read only master db
                            # Note that you cannot use allow_read & rad_only for the master
                            allow_read: true

                            # weight - For the same roles a database with higher weight is picked first.
                            weight: 1

                            # on_connect - Any sql commands to run on connect
                            on_connect:
                                - SET NAMES utf8;

                        dbMaster2:
                            dsn: mysql://dbuser:dbpassword@dbhost:3306/dbname
                            role: master
                            read_only: true
                            weight: 2
                            on_connect:
                                - SET NAMES utf8;
                                - /* Another SQL command */;

                        dbSlave1:
                            dsn: mysql://dbuser:dbpassword@dbhost:3306/dbname
                            role: slave
                            weight: 1
        
    

Then to use:

        
            $pool = \hathoora\database\dbAdapter::getConnection('dbPool1');
            try
            {
                // will use dbSalve1
                $r = $pool->fetchArray('SELECT NOW();');

                // will use dbMaster2 because of higher weight
                $r = $pool->query('INSERT INTO TABLE ...');

                // you can also identify server manually
                $r = $pool->server('master:dbMaster1')->fetchArray('SELECT NOW();');
                $r = $pool->server('slave:dbSlave1')->fetchArray('INSERT IGNORE NOW();');

                // add comment about the query for logging in webprofiller
                $r = $pool->comment('hello world')->fetchArray('SELECT NOW();');

                // or specify the last server used
                $r = $pool->server('last')->fetchArray('SELECT NOW();');
            }
            catch (\Exception $e)
            {
                echo $e->getMessage();
            }

        
    

Common Operations

Common queries - example of common query operations.

        
            // lazy loaded connection
            $db = \hathoora\database\dbAdapter::getConnection();

            // for fetching single values
            $value = $db->fetchValue('SELECT field FROM TABLE WHERE field = "?" LIMIT 1', array($param1));

            // for fetching a single row of result as an array
            $arr = $db->fetchArray('SELECT * FROM TABLE WHERE field1 = "" AND field2 = "" LIMIT 1', array($param1, $param2));

            // for fetching multiple results as array
            $arr = $db->fetchArrayAll('SELECT * FROM TABLE LIMIT 1');

            // for fetching multiple results as associate array
            $arr = $db->fetchArrayAll('SELECT fieldName, name, age, country, etc.. FROM TABLE WHERE field="?" LIMIT 1',
                                      array($param1),
                                      array('pk' => fieldName));

            // inserting data, if TABLE has AUTO INCREMENT PRIMARY KEY, then $r would be the last insert id value
            $r = $db->insert('INSERT INTO TABLE...', array($param));

            // other queries, $stmt is an instance of \hathoora\database\dbResult
            $stmt = $db->query('SELECT * FROM TABLE WHERE field = "?"', array($param1));
            if ($stmt && $stmt->rowCount())
            {
                while ($row = $stmt->fetchArray())
                {
                    //
                }
            }

            // escaping string
            $escapedString = $db->escape($string);
        
    

Exceptions or not: by default exception is thrown when a query fails.

        
            // db connections are lazy loaded
            $db = \hathoora\database\dbAdapter::getConnection(); // getConnection() assumes default database configuration
            try
            {
                $arr = $db->fetchArray('SELECT NOW();');
                print_r($arr);
            }
            catch (\Exception $e)
            {
                echo $e->getMessage();
            }
        
    

To disable exceptions and fail silently use the following:

        
            // db connections are lazy loaded
            $db = \hathoora\database\dbAdapter::getConnection(); // getConnection() assumes default database configuration

            $arr = $db->silent(true)->fetchArray('SELECT NOW()');
            if ($arr)
            {
                // successful query...
            }

            // to disable silent
            $db->silent(false);
        
    

Transaction & rollback

        
            $rollBack = false;

            // using try catch
            try
            {
                $db->beginTransaction();
                $result = $db->fetchArray('UPDATE ...');
            }
            catch (\Exception $e)
            {
                $rollBack = true;
            }

            // or using silent method
            $arr = $db->silent()->fetchArray('UPDATE ...');
            if (!$arr)
                $rollBack = true;

            if ($rollBack)
                $db->rollback();
            else
                $db->commit();
        
    
Page Took:         4.43 msec
Page Memory:   531.61k
docs / viewController / view (200)
Request UUID: 599cce8e0c84b6.24142842
Get
Post
Cookies
Files
Session
Server
Response
No data.
No data.
No data.
No data.
No data.
REDIRECT_HATHOORA_ENV
prod
REDIRECT_STATUS
200
HATHOORA_ENV
prod
HTTP_HOST
www.hathoora.org
HTTP_X_FORWARDED_FOR
54.166.205.242
HTTP_X_REAL_IP
54.166.205.242
HTTP_CONNECTION
close
HTTP_ACCEPT_ENCODING
x-gzip, gzip, deflate
HTTP_USER_AGENT
CCBot/2.0 (http://commoncrawl.org/faq/)
HTTP_ACCEPT
text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
PATH
/sbin:/usr/sbin:/bin:/usr/bin
SERVER_SIGNATURE
Apache/2.2.15 (CentOS) Server at www.hathoora.org Port 80
SERVER_SOFTWARE
Apache/2.2.15 (CentOS)
SERVER_NAME
www.hathoora.org
SERVER_ADDR
192.168.1.11
SERVER_PORT
80
REMOTE_ADDR
207.126.164.119
DOCUMENT_ROOT
/var/websites/hathoora.org/docroot
SERVER_ADMIN
webmaster@hathoora.org
SCRIPT_FILENAME
/var/websites/hathoora.org/docroot/index.php
REMOTE_PORT
39482
REDIRECT_URL
/docs/v1/database
GATEWAY_INTERFACE
CGI/1.1
SERVER_PROTOCOL
HTTP/1.0
REQUEST_METHOD
GET
QUERY_STRING
REQUEST_URI
/docs/v1/database
SCRIPT_NAME
/index.php
PHP_SELF
/index.php
REQUEST_TIME_FLOAT
1503448718.05
REQUEST_TIME
1503448718
Status: 200
Headers:
Content-Type
text/html; charset=UTF-8
Configutation
Key Value
app.admin
Array
(
    [pattern] => /admin(|/)
    [directory] => hathoora
    [namespace] => hathoora\admin
)
app.docs
Array
(
    [pattern] => /docs(|/)
    [directory] => hathoora
    [dispatcher] => Array
        (
            [class] => customDispatcher
            [method] => dispatch
        )

)
app.site
Array
(
    [default] => 1
)
hathoora.logger
Array
(
    [profiling] => Array
        (
            [enabled] => 1
        )

    [logging] => Array
        (
            [enabled] => 1
            [level] => DEBUG
        )

    [webprofiler] => Array
        (
            [enabled] => 1
            [system] => 
            [show_redirects] => 
        )

)
hathoora.template
Array
(
    [engine] => Array
        (
            [name] => Stuob
        )

)
hathoora.database
Array
(
    [default] => mysql://dbuser:dbpassword@dbhost:3306/dbname
)
services.container
Array
(
    [class] => \hathoora\container
    [method] => getContainer
)
Logging (14)
hathoora.logger.logging.enabled: 1
# Time (msec) Level Memory (KB) Message
1 2.274 DEBUG 309.34 Configuration loaded:
Array
(
    [0] => app_prod.yml
    [1] => config_prod.yml
)
2 2.543 DEBUG 322.95 Listener "kernel.terminate[webprofiler]" has been added because of hathoora.logger.webprofiler.enabled
3 2.693 DEBUG 326.34 Event kernel.ready has been registered.
4 2.891 DEBUG 334.71 Calling App dispatcher (Array ( [class] => customDispatcher [method] => dispatch ) )...
5 3.076 DEBUG 349.02 Event kernel.route has been registered.
6 3.220 DEBUG 378.37 Event kernel.controller has been registered.
7 3.242 DEBUG 379.31 Calling controller (\docs\controller\viewController::view) with following params
Array
(
    [0] => v1
    [1] => database
)
8 3.494 INFO 466.66 Template (docs/v1/database.tpl.php) loaded
9 3.583 INFO 492.70 Template (/var/websites/hathoora.org/app/hathoora/docs//resources/templates/components/htmlMeta.tpl.php) loaded
10 3.634 INFO 493.95 Template (/var/websites/hathoora.org/app/hathoora/docs//resources/templates/components/flashMessages.tpl.php) loaded
11 4.258 INFO 443.55 Template (docs.tpl.php) fetched.
12 4.284 DEBUG 443.14 Event kernel.response has been registered.
13 4.301 DEBUG 444.33 Event kernel.terminate has been registered.
14 4.348 DEBUG 447.55 Event kernel.terminate->webprofiler has been notified.
Profiling
template
# Time (msec) Name Cached Took (msec)
1 3.284 docs.tpl.php 0 0.922