puPHPet Box MySQL backup

As you all know by now, I love puPHPet. And I like tinkering with things. Which of course leads to the occasional breaking of things. And then I realised, okay, your sites files are being mounted into this virtual machine, what about the databases though?

The great thing about puPHPet and Vagrant is you can build a full server up from scratch in minutes. The one downside is that it cant be smart enough to uninstall things. If you add lines to your config.yaml it will install something, but removing those lines means puPHPet won’t even know it was there, so how would it know to uninstall it? The exceptions of course being the ones which have their own config.yaml entry, with an install: ‘1’ or install: ‘0’

Anyway, I generated me a new config and was about to blitz my old VM when the usual gut instinct checks kicked in, and the DB sprung to mind immediately. So I made a quick backupdbs.sh shell script (courtesy of a StackOverflow post) which makes individual sql files for each of your DB’s. I decided I would keep the script in my mounted sites folder in a bin directory. So hence it lives in /var/www/bin/backupdbs.sh

#!/bin/bash

USER="root"
PASSWORD="YOURPASSWORDHERE"

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
    fi
done

To back everything up, vagrant ssh into the machine, then (you’ll probably need to switch user to www-data) do the following:

sudo su www-data
cd /var/www/bin
./backupdbs.sh

Now you’ll find all your DB’s SQL files date stamped in the folder! You can continue to break, tweak, fix, and run your VM to your hearts content 😀

Advertisements

Auto Sorting Zend_Db_Table_Rowsets with Pagination

Expanding upon my abstract DB class that auto creates Zend_Paginators on demand, I thought it was about time I did something regarding automatically sorting the records returned.

First thing I did was go in and edit my db base class (need a reminder? https://delboy1978uk.wordpress.com/2014/06/30/auto-paginator-with-zend_db_table/), adding a sort_field property, a setSortField() method, and a little if statement in our getResult() method :

    /** @var string $sort_field */
    protected $sort_field = null;
    /**
     *  For instance 'date_added DESC'
     *  @param int $no
     *  @return $this
     */
    public function setSortField($sort_and_direction)
    {
        $this->sort_field = $sort_and_direction;
        return $this;
    }
    /**
     * @param Zend_Db_Select|string $select
     * @return \Zend_Db_Table_Rowset_Abstract|Paginator
     */
    public function getResult($select)
    {
        if(is_string($select))
        {
            $select = $this->select()->where($select);
        }
        if($this->sort_field)
        {
            $select->order($this->sort_field);
        }
        if($this->paginator_flag == true)
        {
            return $this->getPaginator($select);
        }
        return $this->fetchAll($select);
    }

Now we have that, we can sort any queries like so:

        // Get the page number
        $page = ($this->getRequest()->getParam('page')) ?: 1;
                
        // Set pagination
        $db->setPaginatorFlag(true);
        $db->setNumPerPage(30);
        $db->setPage($page);
        
        // Get sort field
        $sort = $this->getRequest()->getParam('sort');
        $dir = $this->getRequest()->getParam('dir');
        
        // Set the sort
        $db->setSortField($sort.' '.$dir);

Again, in your DB class method, make sure it calls $this->getResult(); and not fetchAll().

Anyway, thats only one half of the equation, what we need is sort links! I create a view helper for this, which basically calls the url view helper, but with additional search capabilities. In the Bootstrap (as you’ll know), we define our routes as such:

$router->addRoute(
    'lead-assessor-view-supply',
    new Zend_Controller_Router_Route(
        '/lead-assessor/view-supply/:orgid/:id/:page/:sort/:dir',
         array(
            'controller' => 'lead-assessor',
            'action' => 'view-supply', 
            'page' => 1, 
            'sort' => 'date_from', 
            'dir' => 'DESC'
         )
      )
);

As you can see, I have added sort and dir in addition to page.  To get the links working, send the page, sort, and dir variables to the view, and in the view, instead of calling $this->url(blahblah); we will call our own view helper, sortLinks(); Here’s the class:

<?php 

class ESOS_View_Helper_SortLinks extends Zend_View_Helper_Abstract
{
    /** 
     *  Pass in a route name, and an array of any params which are the same for all the sort links
     *  @param string $route_name
     *  @param array $params
     *  @param string $field_name
     *  @param string $sort_field
     *  @param string $sort_dir
     *  @param string $field_label
     *  @param string $classes
     *  @return string
     */
    public function sortLinks(array $params,$route_name,$field_name,$field_label, $classes = null)
    {
        // Set the current sort field
        $current_sort_field = isset($params['sort']) ? $params['sort'] : 'ASC' ;
        
        // Set the Icon
        $icon = $this->view->sortIcon($field_name,$current_sort_field,$params['dir']);
        
        // Set params
        $params['sort'] = $field_name;
        $params['dir'] = $this->view->sortDir($field_name, $current_sort_field, $params['dir']);
        
        // Build the HTML
        $link = '<a href="';
        $link .= $this->view->url($params,$route_name);
        $link .= '" class="';
        $link .= $classes;
        $link .= '">';
        $link .= $icon;
        $link .= $field_label;
        $link .= '</a>';
        
        // Return the HTML
        return $link;
    }
}

Almost there! You’ll see it is calling another two view helpers, sortIcon and sortDir:

<?php

class ESOS_View_Helper_SortDir extends Zend_View_Helper_Abstract
{
    public function sortDir($field_name, $current_sort_field, $dir)
    {
        if($field_name == $current_sort_field)
        {
            switch($dir)
            {
                case 'ASC':
                    $dir = 'DESC';
                    break;
                case 'DESC':
                default:
                    $dir = 'ASC';
                    break;
            }
        }
        return $dir;
    }
}
<?php

class ESOS_View_Helper_SortIcon extends Zend_View_Helper_Abstract
{
    public function sortIcon($field_name, $current_sort_field, $dir)
    {
        if($field_name == $current_sort_field)
        {
            switch($dir)
            {
                case 'ASC':
                    $icon = '<i class="fa fa-caret-up"></i>&nbsp;';
                    break;
                case 'DESC':
                default:
                    $icon = '<i class="fa fa-caret-up"></i>&nbsp;';
                    break;
            }
        }
        else 
        {
            $icon = null;
        }
        return $icon;
    }
}

These just figure out which direction of sort the link will generate. The Icons are from Font Awesome, but feel free to use anything you like!

Tying it all together, in your view, define an array of any parameters that you will pass into each sort link (sort, and dir should always be in there, page if using the paginator):

    // Common params for the sortLinks
    $params = array(
        'orgid' => $this->org->getId(),
        'page' => $this->page,
        'sort' => $this->sort,
        'dir' => $this->dir,
    );

At the top of your table where you want your clickable sort links, call:

<?= $this->sortLinks($params,'portfolio-admin-users','name',$this->t('Organisation')); ?>

Sorting By Price

Sorting By Status

This will save a LOT of farting around in the future! Have fun! 🙂

Advanced Paginator with Zend_Db_Table

Make your pagination EASY! Whether using Zend Framework’s MVC, or just the library classes!

By now you might have read by now that half the folk that were using Zend_Paginator have been using it wrong.  They would get their rowset from the table, and then create a paginator and pass in the rowset. Sure, it works, but you have just fetched ALL the rows first, and Paginator displays only the records in question. That is a huge waste of resources!

What SHOULD be happening is that you pass a select() object to the paginator, to which it will figure out the offsets and limits.

Anyway, pretty much all my db classes were returning either rowsets or rows depending on the method. What i needed was for it to return the select. So I had an idea. Auto pagination!

Rather than your db classes extending Zend_Db_Table, we will extend our own abstract class which will  itself extend Zend_Db_Table.

Our class will have a couple of properties;  a flag for pagination on/off, the page number, and the num per page. It will also have a few  methods, as you can see below.

<?php

namespace AA\Repository;

use Zend_Db_Table_Abstract as Table;
use Zend_Db_Table_Select as Select;
use Zend_Paginator as Paginator;
use Zend_View_Helper_PaginationControl as PaginationControl;

abstract class AbstractRepository extends Table
{
 /** @var bool */
 private $paginator_flag = false;
 /** @var int */
 private $page = 1;
 /** @var int */
 private $num_per_page = 30;

 /**
 * @param bool $bool
 * @return $this
 */
 public function setPaginatorFlag($bool)
 {
 $this->paginator_flag = $bool;
 return $this;
 }

 /**
 * @param int $no
 * @return $this
 */
 public function setPage($no)
 {
 $this->page = $no;
 return $this;
 }

 /**
 * @param int $no
 * @return $this
 */
 public function setNumPerPage($no)
 {
 $this->num_per_page = $no;
 return $this;
 }

 /**
 * @param Select $select
 * @return Paginator
 */
 private function getPaginator(Select $select)
 {
 PaginationControl::setDefaultViewPartial('NavControls.phtml');
 $paginator = Paginator::factory($select);
 $paginator->setCurrentPageNumber($this->page);
 $paginator->setItemCountPerPage($this->num_per_page);
 return $paginator;
 }

 /**
 * @param Select $select
 * @return \Zend_Db_Table_Rowset_Abstract|Paginator
 */
 protected function getResult(Select $select)
 {
 if($this->paginator_flag == true)
 {
 return $this->getPaginator($select);
 }
 return $this->fetchAll($select);
 }
}

(If you aren’t using namespaces in your code, you can tweak it to the old style.)

The setPaginatorFlag($bool) turns paging on or off. setPage($no) and
setNumPerPage($no) are self explanitory. getPaginator(Select $select) returns a paginator from the select object.  The interesting method is getResult(Select $select). It checks wether we asked for a paginator. If so it calls getPaginator, if not it does a plain fetchAll.

Have a look at a simple DB Class which now uses the auto paginator:

namespace AA\Repository;


class Competitors extends AbstractRepository
{
 protected $_name = 'branch_competitors';
 protected $_rowClass = 'AA\Entity\Branch\Competitors';


 public function getCompetitors($bid)
 {
 return $this->fetchRow('bid = '.$bid);
 }

 public function getAllCompetitors()
 {
 $select = $this->select()
 ->order('bid DESC');
 return $this->getResult($select);
 }
}

The interesting parts here are the extends AbstractRepository, and return $this->getResult($select). We now use getResult so that it can decide whether to give you a rowset or a paginator.

In your views, you can now just echo it out to give you your paginator! (Of course, you will have your pagination view partial set! see manual for details)

However if you are using Zend Framework libraries without using the MVC component, we can still use Zend Paginator without concerning ourselves with Zend_View etc. I created a view helper class with a static render method, accepting the paginator and the current url:

namespace AA\View\Helper;

use Zend_Paginator;


class Paginator 
{
 public static function render(Zend_Paginator $paginator,$url)
 {
 $url = parse_url($url);
 parse_str($url['query'],$result);
 $class = null; //for now
 if ($paginator->getPages()->pageCount)
 {
 $html = '<div class="pagination"><ul>';
 if (isset($paginator->getPages()->previous))
 {
 $result['page'] = $paginator->getPages()->first;
 $query = http_build_query($result);
 $html .= '<li>
 <a class="'.$class.'" id="first" href="'.$url['path'].'?'.$query.'">
 first
 </a>
 </li>';
 }
 else
 {
 $html .= '<li class="disabled"><a href="#">first</a></li>';
 }
 if (isset($paginator->getPages()->previous))
 {
 $result['page'] = $paginator->getPages()->previous;
 $query = http_build_query($result);
 $html .= '<li>
 <a class="'.$class.'" id="last" href="'.$url['path'].'?'.$query.'">
 previous
 </a>
 </li>';
 }
 else
 {
 $html .= '<li class="disabled"><a href="#">previous</a></li>';
 }
 if (isset($paginator->getPages()->next))
 {
 $result['page'] = $paginator->getPages()->next;
 $query = http_build_query($result);
 $html .= '<li>
 <a class="'.$class.'" id="next" href="'.$url['path'].'?'.$query.'">
 next
 </a>
 </li>';
 }
 else
 {
 $html .= '<li class="disabled"><a href="#">next</a></li>';
 }
 if (isset($paginator->getPages()->next))
 {
 $result['page'] = $paginator->getPages()->last;
 $query = http_build_query($result);
 $html .= '<li>
 <a class="'.$class.'" id="last" href="'.$url['path'].'?'.$query.'">
 last
 </a>
 </li>';
 }
 else
 {
 $html .= '<li class="disabled"><a href="#">last</a></li>';
 }
 $html .= '</ul></div>';
 }
 else
 {
 $html = null;
 }
 return $html;
 }
}

So on a plain ol’ PHP page (that’s able to autoload the classes, if not, require them or look into composer), here’s all you would need (my current url in this example is “/?id1=-competitors”):

$page = ($_GET['page']) ? filter_input(INPUT_GET, 'page', FILTER_SANITIZE_NUMBER_INT) : 1;
//set pagination on for this query
$db->setPaginatorFlag(true)
   ->setPage($page)
   ->setNumPerPage(30);
//get paginator/rowset
$competitors = $db->getAllCompetitors();
//show paginator
echo \AA\View\Helper\Paginator::render($competitors,'/?id1=competitors');
foreach($competitors as $competitor)
{
    //row stuff here
}

Awesome! Now you need never again have to muck around with paginators, whether using Zend Frameworks MVC or not! Have fun!



			

Overriding DB Connected Resources in Apigility

Apigility rocks! It’s still in its early beta days (and hence a little buggy at times), but it’s WELL worth playing with now for building your API’s!

Anyway, you can create code connected and db connected services. Db connected REST services pretty much give you the auto CRUD functionality, but it would be nice to have some where, and order clauses!

This feature should be ready for version 1.2, but in the meantime, lets override it mannually!

You will notice in your Rest/ServiceName folder (in my particular case, Agents), that it has auto generated an AgentsCollection and an AgentsEntity PHP file.

We will create our own Resource Class which will extend the DBConnectedResource. So I created AgentsResource.php :

 

 <?php

namespace Allagents\V1\Rest\Agents;
use ZF\Apigility\DbConnectedResource;
use Zend\Paginator\Adapter\DbTableGateway as TableGatewayPaginator;

class AgentsResource extends DbConnectedResource
{
 public function fetchAll($data = array())
 {
 // WHERE CLAUSE
 $where = '';
 foreach($data->where as $clause)
 {
 $where .= $clause.' AND ';
 }
 //chop off the last AND
 $where = substr($where,0,-4);

 // ORDER CLAUSE
 $order = '';
 foreach($data->order as $clause)
 {
 $order .= $clause.' , ';
 }
 //chop off the last comma
 $order = substr($order,0,-2);

 // GROUP CLAUSE
 $group = '';
 foreach($data->group as $clause)
 {
 $group .= $clause.' , ';
 }
 //chop off the last comma
 $group = substr($group,0,-2);

 // HAVING CLAUSE
 $having = '';
 foreach($data->having as $clause)
 {
 $having .= $clause.' AND ';
 }
 //chop off the last AND
 $having = substr($having,0,-4);
 $adapter = new TableGatewayPaginator($this->table,$where, $order, $group, $having);
 return new $this->collectionClass($adapter);
 }
}

Ok, now we have to tell ZF2 to use our resource class instead of the default. As always, ZF2 modules are mega customisable, and usually involves tweaking your module.config. Apigility is no different, so jump into your module folder and open the module.config.php. We are adding one line to our DBConnectedResource. Find the section ‘zf-apigility’ => ‘db-connected’ and you will see your service’s entry. We add an array key like so; ‘resource_class’ => ‘Allagents\\V1\\Rest\\Agents\\AgentsResource’,

'zf-apigility' => array(
 'db-connected' => array(
 'Allagents\\V1\\Rest\\Agents\\AgentsResource' => array(
 'adapter_name' => 'DBAdapter',
 'table_name' => 'agents',
 'hydrator_name' => 'Zend\\Stdlib\\Hydrator\\ArraySerializable',
 'controller_service_name' => 'Allagents\\V1\\Rest\\Agents\\Controller',
 'entity_identifier_name' => 'aid',
 'resource_class' => 'Allagents\\V1\\Rest\\Agents\\AgentsResource',
 ),
 ),
 ),

Lastly, we need to whitelist the query parameters that we use, otherwise they will not appear in the HAL links!

Again, in the module.config.php, in the section ‘zf-rest’, we add an array defining the whitelisted parameters:

'zf-rest' => array(
 'Allagents\\V1\\Rest\\Agents\\Controller' => array(
 'listener' => 'Allagents\\V1\\Rest\\Agents\\AgentsResource',
 'route_name' => 'allagents.rest.agents',
 'route_identifier_name' => 'agents_id',
 'collection_name' => 'agents',
 'entity_http_methods' => array(
 0 => 'GET',
 1 => 'PATCH',
 2 => 'PUT',
 3 => 'DELETE',
 ),
 'collection_http_methods' => array(
 0 => 'GET',
 1 => 'POST',
 ),
 'collection_query_whitelist' => array(
 0 => 'where',
 1 => 'order',
 2 => 'having',
 3 => 'group',
 ),
),


To test it is working, call your URL (in my case /agents) and you should see the normal expected results. Now test with a query sting. I made a little script to build the query up:

<?php

$query = http_build_query(array(
 'where' => array(
 'name LIKE "A%"',
 ),
 'order' => array(
 'name asc',
 ),
 'group' => array(

 ),
 'having' => array(

 ),
));

echo $query.'<br />';

Which output:
where%5B0%5D=name+LIKE+%22A%25%22&order%5B0%5D=name+asc
So I called: /agents?where%5B0%5D=name+LIKE+%22A%25%22&order%5B0%5D=name+asc
You should have your filtered results, with HAL links in the JSON, like so!

{
“_links”: {
“self”: {
“href”: “http://api.allagents.del/agents?where%5B0%5D=name%20LIKE%20%22A%25%22&order%5B0%5D=name%20asc&page=1&#8221;
},
“first”: {
“href”: “http://api.allagents.del/agents?where%5B0%5D=name%20LIKE%20%22A%25%22&order%5B0%5D=name%20asc&#8221;
},
“last”: {
“href”: “http://api.allagents.del/agents?where%5B0%5D=name%20LIKE%20%22A%25%22&order%5B0%5D=name%20asc&page=44&#8221;
},
“next”: {
“href”: “http://api.allagents.del/agents?where%5B0%5D=name%20LIKE%20%22A%25%22&order%5B0%5D=name%20asc&page=2&#8221;
}
},
“_embedded”: {
“agents”: [
{
“aid”: “2842”,
“etc”:”etc!”

Have fun! Maybe I could even get this into the actual Apigility code by sending them a Pull Request! (Probably not though lol, I’m sure they’ll figure a better way of dealing with the query params than passing RAW SQL portions haha!)

Setup a ZF1 DB Adapter in Zend Framework 2

If you checked my last post, you will have read about how to get all your old ZF1 classes autoloading in your new shiny ZF2 project. However, the DB settings from your application.ini won’t have been set, and so you’ll get a 500 response with  No Db Adapter. Not a problem. We just create the db adapter ourselves and tell Zend_Db that it is the default.

I’ve put this in the ZF2 index.php just before the last line, Zend\Mvc\Application::init($appConfig)->run();

//Set up ZF1 DB
$params = array(
 'host' => '127.0.0.1',
 'username' => 'xxx',
 'password' => 'xxx',
 'dbname' => 'xxx'
);
$adapter = Zend_Db::factory('Pdo_Mysql',$params);
Zend_Db_Table_Abstract::setDefaultAdapter($adapter);

Now your autoloading ZF1 DB classes wont freak out, and should connect no problem!

Slouch on the CouchDB

No doubt you will know about the new generation of document oriented databases designed to work using the HTTP protocol, such as Mongo and CouchDB. They come pretty much with a built in API! So it’s time to mess around with it!

I downloaded Couchbase Server community edition (which is pretty nifty I’ll admit). Once you get that installed you can access localhost on port 8091 and it will take you into a cool looking admin panel:

One of the admin screens on couchbase server
One of the admin screens on couchbase server

It comes with a sample database, and you can access the API on port 8092. The full range of ports is as follows:

Port Description Node to Node Node to Client Cluster Administration XDCR
8091 Web Administration Port Yes Yes Yes Yes
8092 Couchbase API Port Yes Yes No Yes
11209 Internal Cluster Port Yes No No No
11210 Internal Cluster Port Yes Yes No No
11211 Client interface (proxy) Yes Yes No No
4369 Erlang Port Mapper (epmd) Yes No No No
21100 to 21199 (inclusive) Node data exchange Yes No No No

There are some great videos at http://www.relaxed.tv that show you how couch works. This is the introductory video:

The curl syntax should be something like:

curl  localhost:8092/
{"couchdb":"Welcome","version":"1.2.0a-be4fa61-git","couchbase":"2.0.0-1976-rel-community"}

As you can know, being able to query a database and perform CRUD over HTTP would be incredibly useful, so this is very promising!

It’s stupid O’clock and I’ve been up all night but this looks great! I will update this post when I return to have a play with my new toy! 🙂