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

Export Wordpress post URLs

I’m making some Apache redirects from an old domain to a new domain. I need the old domains blog posts to redirect to the new domain. So I need the WordPress permalinks.

In MySQL, you can say the following to get your permalinks.

SELECT wpp.post_title, wpp.guid,wpp.post_date,
CONCAT(wpo_su.option_value,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    wpo.option_value,'%year%', date_format(wpp.post_date,'%Y'))
    ,'%monthnum%',date_format(wpp.post_date,'%m'))
    ,'%day%',date_format(wpp.post_date,'%d'))
    ,'%postname%',wpp.post_name )
    ,'%category%',wpc.slug )
    ,'%post_id%', wpp.id)
) as permalink
FROM wp_posts wpp
INNER JOIN wp_options wpo ON wpo.option_name='permalink_structure'
INNER JOIN wp_options wpo_su ON wpo_su.option_name='siteurl'
INNER JOIN (
    SELECT wtr.object_id ID, max(wpt.slug) slug
    FROM wp_term_relationships wtr
    INNER JOIN wp_term_taxonomy wtt ON wtt.term_taxonomy_id=wtr.term_taxonomy_id AND wtt.taxonomy='category'
    INNER JOIN wp_terms wpt on wpt.term_id=wtt.term_id
    GROUP BY  wtr.object_id
) wpc ON wpc.ID=wpp.ID
WHERE wpp.post_type = 'post'
AND wpp.post_status = 'publish'
ORDER BY wpp.post_date DESC

Tail every MySQL query in your terminal

It’s nice to see what the actual queries that run are on your site. Its pretty simple to set up! In your home folder, edit your .bashrc (or equivalent) and slap in the following:

alias tmysql='mysql -u root -e '\''SET GLOBAL general_log = "ON"'\'';sudo tail -f /var/log/mysql/general.log;mysql -u root -e '\''SET GLOBAL general_log = "OFF"'\''; sudo rm /var/log/mysql/general.log; echo '\''\nExiting Tail, Log Deleted.'\'';

Tweaking paths where required, obviously. Now close and open your terminal again, and when you type tmysql, you’ll start tailing MySQL! Sample output below!

          453 Query    SELECT `main_table`.* FROM `enterprise_customersegment_segment` AS `main_table`
          453 Query    SELECT `op_imagecdn_cache`.* FROM `op_imagecdn_cache` WHERE (`op_imagecdn_cache`.`url`='http:blah.com')
          453 Query    SELECT `main_table`.* FROM `madskull_autoblocks` AS `main_table` WHERE (identifier = 'trust-banners')
          453 Query    SELECT `madskull_autoblocks_definitions`.* FROM `madskull_autoblocks_definitions` WHERE (`madskull_autoblocks_definitions`.`id`='39')

MySQL socket DSN connection syntax

Usually I just bung in 127 or localhost in order to connect up to my DB. However, if you aren’t allowing remote connections, then running on a unix socket is much faster and skips all the networking overhead involved with TCP/IP.

So! To use a socket, you use a DSN for the connection. I’ve done this a few times, but I keep forgetting the syntax, sop here it is, solely for my own memory’s sake:

mysql:unix_socket=/tmp/mysql.sock;dbname=my_db_name_here

Lost connection to MySQL server at ‘reading initial communication packet’

Here’s one you might have had. “Lost connection to MySQL server at ‘reading initial communication packet’ “.

If  you get this, then I assume that you are connecting to another machine on the network, a virtual machine, or a remote server.

It’s a pretty simple fix. Edit your /etc/mysql/my.cnf (path may differ depending on your setup), and change the line that says bind-address = 127.0.0.1 to:

bind-address = 0.0.0.0

Save it, then restart mysql.

sudo service mysql restart

Job done!

Geolocate your visitors!

Right now I’m building a mobile app using Intel’s AppFramework for a single page site, and Apigility for the API.

In the app, there is an autosuggest for towns and postcodes, however I thought it would be nice to have a “Locate Me” button, able to pinpoint your visitor (hopefully with a decent degree of accuracy!), so I Googled away and found the following:

https://github.com/estebanav/javascript-mobile-desktop-geolocation

In your site go into your js folder and clone the repo:

cd js
git clone git@github.com:estebanav/javascript-mobile-desktop-geolocation.git

In your page you’ll include the scripts and add your functionality as required:

<script src="js/javascript-mobile-desktop-geolocation/js/geoPosition.js" type="text/javascript" charset="utf-8"></script>
 <script src="js/javascript-mobile-desktop-geolocation/js/geoPositionSimulator.js" type="text/javascript" charset="utf-8"></script>
<script type="text/javascript">
if(geoPosition.init())
 { 
    // Geolocation Initialisation
    geoPosition.getCurrentPosition(success_callback,error_callback,    {enableHighAccuracy:true});
 }
 else
 {
    // You cannot use Geolocation in this device
 }
 geoPositionSimulator.init();
// p : geolocation object
 function success_callback(p)
 {
    // p.latitude : latitude value
    // p.longitude : longitude value
    console.log(p);
 }
function error_callback(p)
 {
    // p.message : error message
    console.log(p);
 }
</script>

Now if you load your page and go into the web inspector, hopefully you should have some coordinates!

The next part of the puzzle is where it gets interesting: How to find the closest match in your table of areas, postcodes, and lat longs!

I would never have figured this out, but there is an equation called the Haversine formula, which uses spherical trigonometry to calculate areas within a certain distance! It looks like this:

R = earths radius (mean radius = 6,371km)
Δlat = lat2 lat1
Δlong = long2 long1
a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
c = 2.atan2(√a, √(1a))
d = R.c
Angles need to be in radians to pass to Trigonometric functions

http://en.wikipedia.org/wiki/Haversine_formula

Here’s my actual query itself:

SELECT *, 
 ( 3959 * acos( cos( radians(55.864237) ) * cos( radians( latitude ) ) 
 * cos( radians( longitude ) - radians(-4.251806) ) + sin( radians(55.864237) ) 
 * sin( radians( latitude ) ) ) ) AS distance 
FROM postcodes HAVING distance < 20 
ORDER BY distance LIMIT 1;

Here I check for any area within 20 miles, but you can make this as short or long as you want.  The 3959 figure at the start of the query is the number used for miles, if you are using kilometres you should change this number to 6371.  I have limited it to 1 row, as I only want the closest match, however you may want to change this in other situations!

So there we have it! NSA-like spying on your visitors! (With their permission of course!)

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!)