Wednesday 18 March 2015

MySQL Cross database foregin keys

Here is a small query to display all foreign keys across different databases. May come in very handy when deciding which database to restore first :)

USE information_schema;

SELECT * 
    FROM 
        KEY_COLUMN_USAGE 
    WHERE 
        CONSTRAINT_SCHEMA != REFERENCED_TABLE_SCHEMA;

Thursday 12 March 2015

Last modified date/time for CakePHP projects

What is the last modification date of a CakePHP project? To answer that you would probably have to ask what is the latest date and time that any file was modified in your CakePHP project sub-folders. These sub-folders are usually Model, View, Controller, webroot and perhaps Vendor or Console.

In order to solve this in Linux, I crafted the following bash script:

#!/bin/bash

BASE_PATH="/var/www/html/MyProject/app"           # replace with your own
SUB_COMPONENTS="Model View Controller webroot"    # add folders as needed

LAST_MODIFIED=$(
 for COMPONENT in $SUB_COMPONENTS 
 do
  SUB_PATH=$BASE_PATH/$COMPONENT
  RESULT=`find $SUB_PATH -type f -exec stat --format '%z' "{}" \; | sort -r | head --bytes=16`
  echo $RESULT
 done | sort -r | head --lines=1 )

echo $LAST_MODIFIED
exit 0

the idea is to get all files in each of the paths, contained in the $SUB_COMPONENTS variable, using the find command. Then execute stat to retrieve the last modification datetime, sort the results in reverse order and finally use head to retrieve the first 16 characters which make up the first YYYY-mm-dd HH:ii characters of the timestamp.

The entire result of the for loop is then fed to sort once more to create the list with the highest time stamp being first. Finally we use head again to throw away all output lines apart from the first.

An example of using this in CakePHP controller code would be something like:

    const LAST_MODIFIED_COMMAND = '/opt/bin/cakeLastModified';

    ...

    public function about()
    {
        $this->set('lastModified', exec(self::LAST_MODIFIED_COMMAND));
    }

... and then display the value of the $lastModified variable in the corresponding about view.

Note: Once again, I would like to give credit to the good people at stackoverflow.com for providing answers to all the minor problems that made this one up. So this code would not be here if l0b0 had not provided such a concise and clear answer to Sorting in shell script and if How to recursively find and list the latest modified files in a directory with subdirectories and times? did not have a working answer.