Saturday 13 June 2015

JPA Queries containing count

A simple howto and reference regarding the usage of the count() function in your JPA code.

First thing is correct syntax. Counting the number of rows is not done using the syntax count(*) but rather count(entity). So a proper named query declaration would be something like :

@NamedQueries({
    @NamedQuery(
            name = "PixelStation.pixelStationExists",
            query = "SELECT COUNT(p) FROM PixelStation p WHERE p.pixel = :pixel AND p.station = :station"
    )
})

Next thing to remember is that the class returned by the query is the Java Long. So getting a value would be like:

        Long count = _entityManager.createNamedQuery("PixelStation.pixelStationExists", Long.class)
                .setParameter("station", station)
                .setParameter("pixel", pixel)
                .getSingleResult();

        // record exists no need to do anything
        if (count >= 1)
            return;

        // insert new record
        ...

Friday 10 April 2015

IP tables forward traffic between ethernet and wi-fi

Recently, I was asked to create a Linux based WI-fi access point using the new Raspberry Pi 2 Model B. This project had a .. happy ending, thanks to the numerous bloggers of the π community. Here, there and there just to mention a few.

What I wish to keep as a reference to this blog post is the iptables setup that allowed the device to forward IP v4 traffic between the Ethernet and the Wi-fi ports.

To get started create a file like /etc/iptables.ip-v4.nat containing the following:

# Generated by iptables-save v1.4.14 on Mon Mar 23 18:48:53 2015
*filter
:INPUT ACCEPT [121:10892]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [5:800]
-A FORWARD -i eth0 -o wlan0 -m state --state RELATED,ESTABLISHED -j ACCEPT
-A FORWARD -i wlan0 -o eth0 -j ACCEPT
COMMIT
# Completed on Mon Mar 23 18:48:53 2015
# Generated by iptables-save v1.4.14 on Mon Mar 23 18:48:53 2015
*nat
:PREROUTING ACCEPT [96:7931]
:INPUT ACCEPT [20:2899]
:OUTPUT ACCEPT [2:224]
:POSTROUTING ACCEPT [0:0]
-A POSTROUTING -o eth0 -j MASQUERADE
COMMIT
# Completed on Mon Mar 23 18:48:53 2015

Next, edit /etc/network/interfaces and place the following after the wi-fi setup at the very end:

# Configure firewall to allow traffic between wlan0 and eth0
up iptables-restore < /etc/iptables.ip-v4.nat

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.

Saturday 14 February 2015

Java: Read String from console

Once again, I was out there trying to find the neatest solution for reading a string from the IDE console. It turns out that the shortest piece of code that one can write to achieve this would be :

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

...

     public static String getString() throws IOException
     {
        try (BufferedReader br = new BufferedReader(new InputStreamReader(System.in))) {;
            return br.readLine();
        }
    }