Play Video on Startup of Raspbian on Raspberry Pi

I wanted to be able to play a video on start up of the Raspberry Pi so no human intervention was necessary – you plug in the Pi it boots and plays the selected video(s). This proved a bit tricky in Raspbian so here are the steps required.

1. Create a file and call it playvid.sh put it on the desktop and in a text editor enter the following;

#!/bin/sh

# get rid of the cursor so we don't see it when videos are running
setterm -cursor off																						

VIDEOPATH="/home/pi/vids" 

# you can normally leave this alone
SERVICE="omxplayer"

# now for our infinite loop!
while true; do 
	$SERVICE -r -o hdmi $VIDEOPATH/video_name
	$SERVICE -r -o hdmi $VIDEOPATH/video_name
done

2. Create a file called videostart.desktop and put the following content in the file

[Desktop Entry]
Version=1.0
Type=Application
Terminal=false
StartupNotify=true
Name=Play Videos
Comment=Play all videos in /home/pi/vids
Exec=lxterminal --command "/home/pi/Desktop/playvid.sh"
Categories=Application;

3. Put videostart.desktop in /etc/xdg/autostart/

4. Put the videos to play into /home/pi/vids or which ever path you define as the VIDEOPATH variable within playvid.sh which you created in Step 1.

5. Edit playvid.sh (from Step 1) to have the right video names. You can add as many videos as required to the list to play, just copy and paste the following line of code replacing video_name with the full name of the file including the extention (i.e. mp4).

	$SERVICE -r -o hdmi $VIDEOPATH/video_name

6. Simply restart the Raspberry Pi and the videos should start playing.

 

Agile Within Application Support

Last year I was asked to lead a support team after it had been through a few iterations of team size, membership and location. The team already had a few ITIL processes in place; having finished documenting and implementing further processes I felt there was an opportunity. Whilst the processes were guiding how to complete activities there was nothing giving structure to how we managed the backlog day-to-day and week-to-week. Having worked on development projects run in an agile way and seen how beneficial the methods were I felt there might be benefit to applying some agile techniques to the running of the support team.

The use of agile in general is not prescriptive; this is an advantage in the world of support where you do not know what you will face each day. There are, however, also activities which do occur regularly, for example infrastructure monitoring and patching. It quickly became clear that the mix, of both planned and unplanned work was suited to the incorporation of components of agile methods. Techniques from SCRUM and the agile principles can easily be applied to the running of the support team.  Although the methods described in this article relate to our work providing 2nd and 3rd line support some of the methods described could also be of value to a team providing 1st line support.

Initially we made use of daily stand ups for team coordination but this was just the start, we soon introduced the idea of sprints and sprint retrospectives for planning. Finally, we introduced the idea of a backlog to enhance our performance as a support team and support planning. SCRUM has the idea of a ScrumMaster who acts as an enabler, seeking to solve problems faced by the team preventing them from completing their work. For our application of agile to support the team lead took on the role of ScrumMaster.

Team coordination

As the team is collocated the value of daily stand ups was not immediately apparent but showed when we had team members away or on training. The stand-ups helped us to have a good knowledge of what each of the other team members was currently working enabling us to cover absences and also to offer help and suggestions to each other. The daily stand-up also allowed us to each start the day with clear goals in mind and that really helps maintain a momentum.

Prioritisation

Approaching the task of prioritisation in an agile way is done through working in priority order to address the backlog, allowing new higher priority incidents to push lower priority incidents into the backlog, for the next day or beyond, as required. This means we avoid getting bogged down working on a first-come, first-served basis. However, working in this way we have to be careful to maintain communication, particularly to those callers whose tickets get pushed down the priority order. Today we are in a much better place, working from a ticketing system on a daily basis we have new incidents raised to us which quickly build a prioritised backlog for each team member.

Planning

We have tried different length sprints for defect fixes, from one to three weeks in length and from our experience we found that in the fast moving world of support we needed to readdress and plan the backlog more regularly than many development projects and therefore had very short sprints of just one week.

At the end of each week we hold our retrospective and planning session. We review the tasks in the plan for the week which will be anything the team has found or recurring tasks, and any incomplete tasks move to the backlog. There is also a second backlog of the new incidents raised that week through the ticketing system with SLAs. One of the biggest challenges is to create a single backlog from these two pools of work, we must also consider work across all of the applications we support in totality to determine the work that is in scope for the sprint.

Change management

Further to managing day to day work and incidents in an agile manner, our support team also handles Change Requests. We attempt to bundle changes where practical into releases. These releases are managed in an agile fashion, working with the application owner and first line support as product and backlog owners to prioritise the work with an effort to release early and often. Releasing early and often allows the feedback time to be short and gives the client greater control and confidence in the changes.

In conclusion

Using agile methods in our approach in delivering support helps us maintain momentum, and gives structure to a role which at times can feel chaotic when being bombarded with incidents and requests amongst a mounting backlog. Furthermore, using agile methods has allowed us to deliver better service to our customers through faster resolution times of the issues that really have an impact on their work and increased communication. Agile has afforded us focus on our work with enough time given to planning and time for each person to ask for help and identify issues.

We have had a very positive experience of implementing agile into the support process and would encourage you to try this within your support teams.

Pushing recordings from DLink DCS-5222L to the Cloud

The DLink DCS-5222L is a brilliant piece of kit. However, it provides limited functionality to push your videos and snapshots to the cloud. Below I describe how I have setup my DCS-5222L to push to the Cloud without intervention.

One solution which I initially used was to install Surveillance Station on my Synology NAS and hook up the DLink so the recordings were copied onto the NAS. However, as my NAS is in the same place as the camera, what if someone comes in, steals the camera, and the NAS? Even with the recordings being synced to an Amazon S3 storage bucket which was done by a scheduled job on the NAS this still meant by the time someone had come in and made off with the NAS and the DLink I would be left with no recordings. So, the solution below was born.

The items involved are quite simple and free (to a point). The steps are described below;

1. Set up an Amazon Web Services (AWS) EC2 Windows Server 2012r2 instance (this is free if you use the basic tier and comes with 30Gb storage!)

2. Set up FileZilla FTP Server running on the instance (follow the instructions here at LifeHacker and further instructions here at How-To Geek).

3. Ensure you open the ports on the Windows Firewall for FTP and also within AWS under Security Groups, within the Security Group your Windows Server is in.

4. Test the FTP server connection from a local machine using the static IP given to you by AWS (or hostname if you set this us, obviously) and ensure you can FTP a file to the EC2 machine. I had issues around running FTP in passive mode, using the troubleshooting steps on the FileZilla wiki here I managed to fix these. I also had/have issues around permissions, I am unable to have my home directory for the FTP account anywhere other then the root of my C: drive, not an issue for me but might be for others.

5. Change the RDP and FTP ports on this box for (slightly) enhanced security – ensure you change the firewall rules before you do this so you don’t block your own RDP connection! To chage the RDP port for Windows Server 2012r2 follow the instructions here. The FTP port should be changed within FileZilla under Edit > Settings and then change “Listen on thse ports”, then click ‘OK’.

6.  Now you are ready to make the changes to the DLink Camera. Login to the camera and access the Setup page. Under Video Clip enter the FTP details of the server you just setup and test the connection. The camera will create a folder on the FTP server if successful and there will be a message telling you if you were successful or not towards the top of the page. You can do the same under the Snapshot section too.

7. Finally, so as to not over fill my EC2 instance I have written myself a small Java application which checks if the CCTV backup is over 5Gb in size and if it is will delete the oldest files until it is back under 5Gb again. This is run hourly as a scheduled windows task. You will find code snippets in my other posts that will help with achieving this if required.

Happy Cloud backup! Now whenever a motion is detected by my DLink DCS-5222L not only does it save the recordings to the Micro SD on board but also pushes the video via FTP to a Cloud based AWS EC2 instance which I can remotely access.

Java – Generating a list of files ordered newest to oldest

I recently had a situation where I needed an ordered list of file names by newest to oldest so I thought I would share the code. This uses the last modified date as in my case that worked and as java.io doesn’t provide the created date only last modified.

First a method which returns an ArrayList of all files within a given directory.

public static ArrayList listFiles(String directoryName) {
		File directory = new File(directoryName);

		ArrayList filenames = new ArrayList();

		// get all the files from a directory
		File[] fList = directory.listFiles();
  		if (fList != null) {
       			for (File file : fList) {
           				if (file.isFile()) {
                					filenames.add(file.getAbsolutePath());
           				} else if (file.isDirectory()) {
                					filenames.addAll(listFiles(file.getAbsolutePath()));
           				}
        			}
		}
		return filenames;
	}

We will then call the above method when building the ordered list of files. As below;

public static Map<String, Long> orderedListOfFiles(String directoryName) {
		Map<String, Long> fileMap = new HashMap<String, Long>();

		// get all the files from a directory
		ArrayList filePaths = listFiles(directoryName);
		for (String path: filePaths) {
     			File file = new File(path);
     			fileMap.put(path, file.lastModified());
		}
		
		List<Map.Entry<String, Long>> list = new LinkedList<Map.Entry<String, Long>>(fileMap.entrySet());
		
		Collections.sort(list, new Comparator<Map.Entry<String, Long>>() {

				public int compare(Map.Entry<String, Long> m1, Map.Entry<String, Long> m2) {
					if (m1.getValue() > m2.getValue())
	            		return -1;
	                return 1;
				}

	        });
		 
		  Map<String, Long> result = new LinkedHashMap<String, Long>();
	        for (Map.Entry<String, Long> entry : list) {
	            result.put(entry.getKey(), entry.getValue());
	        }
		
		return result;
	}

To switch oldest to newest simply modify the compare method implemented inside the Comparator class.

Java Database Connectivity – MySQL, Oracle and SQLite

I have played around with database connectivity with Java for MySQL, Oracle and SQLite. I thought I would share some simple connection setup code with the world.

You will need to download and add the appropriate JARs to your project and or your class path, as well as having a database to connect to, unless you are using SQLite in which case the database is local.

MySQL

First up is MySQL connectivity. This first snippet will check you have loaded the JDBC driver correctly.

 

import java.sql.*;
public class MySqlLoadDriver {
  public static void main(String [] args) {
    Connection con = null;
    try {

      // Load the MySQL JDBC driver
      Class.forName("com.mysql.jdbc.Driver") ;
      System.out.println("MySQL JDBC driver loaded ok.");

    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
}

 

The following snippet will set up a connection to the database. Where the host in the example is set to localhost so you may change this to point to your database address or tunnel in to your database using a client like Putty. Also change username and password to your database username and password.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class SimpleConnection;
{

     static public void main(String args[])
     {

      Connection con = null;

      try {

         Class.forName("com.mysql.jdbc.Driver") ;
         System.out.println("MySQL JDBC driver loaded ok.");
         con = DriverManager.getConnection(
         "jdbc:mysql://127.0.0.1:3306/","username", "password");
         System.out.println("Connected with host:port/database.");
         con.close();

     } catch (Exception e) {
              System.err.println("Exception: "+e.getMessage());
     }

    }

}

 

Oracle

Oracle has a two types of connection I have played with Oracle Call Interface and Oracle’s JDBC Thin driver uses Java sockets to connect directly to Oracle. It provides its own TCP/IP version of Oracle’s SQL*Net protocol. Because it is 100% Java, this driver is platform independent and can also run from a Web Browser. Depending on which one you want to use comment out or delete the other line. In the example I am using the OJDBC Thin Driver not the Oracle Call Interface.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class OracleSimpleConnection {

     static public void main(String args[])

     {
         Connection con = null;

         try
         {
               Class.forName ("oracle.jdbc.OracleDriver");
               System.out.println("Oracle JDBC driver loaded ok.");
               con = DriverManager.getConnection(

                   //"jdbc:oracle:oci7:@//localhost:1521/ORCL","username", "password");
                   "jdbc:oracle:thin:@//192.168.104.11:1521/ORCL","username", "password");
               System.out.println("Success!!");
               System.out.println("Connected with host:port/database.");
               con.close();
          }
          catch (Exception e)
          {
               System.err.println("Exception: "+e.getMessage());
          }
       }
}

 

SQLite

Finally the creation and testing of an SQLite Database, adding in a few things with a simple query.

 

import java.sql.*;

public class SQLiteTest {

public static void main(String[] args) throws Exception {

    Class.forName("org.sqlite.JDBC");

    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
    prep.setString(1, "Gandhi");
    prep.setString(2, "politics");
    prep.addBatch();
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "Wittgenstein");
    prep.setString(2, "smartypants");
    prep.addBatch();
    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);
    ResultSet rs = stat.executeQuery("select * from people;");

    while (rs.next()) {
          System.out.println("name = " + rs.getString("name"));
          System.out.println("job = " + rs.getString("occupation"));
    }

    rs.close();
    conn.close();

  }

}

 

Useful Links;

http://www.sqlite.org/

http://www.zentus.com/sqlitejdbc/

http://www.oracle-internals.com/?p=18

http://www.orafaq.com/wiki/JDBC#Thin_driver

How to install the Oracle JDBC into your local Maven repository

This will work to install any jar or package into your local Maven repository, just change the relevant sections in the command line entry.

To get the Oracle Driver go toand then to the JDBC Drivers page which at the time of writing is here:

Ensure you have Maven properly setup on your machine.

Navigate to the folder containing the jar in a command line prompt.

Enter the following;
mvn install:install-file -Dfile=ojdbc5.jar -DgroupId=com.oracle -DartifactId=ojdbc5 -Dversion=11.2.0.3 -Dpackaging=jar -DgeneratePom=true

Where:
-Dfile is the path to file
-DgroupId is the group ID
-DartifactId is the artefact ID
-Dversion is the version of the package
-Dpackaging is the package type i.e. jar
– DgeneratePom will automatically generate the pom.xml entry

Note: If you are using Powershell you will need to escape the – with a back tick so -Dfile=ojdbc5.jar becomes `-Dfile=ojdbc5.jar`
If you are adding Sun JARs is there is a helpful article on naming conventions to use here.

Useful links;
http://maven.apache.org/guides/mini/guide-3rd-party-jars-local.html
http://stackoverflow.com/questions/442230/how-to-manually-install-an-artifact-in-maven-2
http://stackoverflow.com/questions/1074869/find-jdbc-driver-in-maven-repository

Custom Colours for Checkboxes in User Forms Excel 2007

This guide will show you how to make a checkbox in Excel match the fill of a cell, background or text colour.

This guide is using Excel 2007.

Select the cell which has the fill you require, then in the Home ribbon select the fill icon, and click on More Colors…

Excel Colours

In the Colors dialogue select the Custom tab. Take a note of the RGB code for the fill of the cell. So in the screen shot the RGB is 255,204,153.

Colour Picker

 

Now you need to convert the RGB value to Hexadecimal. I would recommend using http://www.endprod.com/colors/rgb2hex.htm but you can always Google for a different converter.

Now with your Hex value you need to format it ready for input into Excel. The simple explanation is that for Excel you need to build a String to enter as the BackColor property.

The String will start with &H00 followed the Hex you just generated. However, you need to swap the last two characters of the hex string with the last, so if your Hex code is ffcc99 then you need to use 99ccff. So, now you have your string &H00*****, where the asterisks are replaced with your Hex code add a final & to the string and you are now ready to go.

Return to Excel, in the Developer tab on the Ribbon (to add Click the Microsoft Office Button, and then click Excel Options. Click Popular, and then select the Show Developer tab in the Ribbon check box) click on the Design Mode icon. Now single click on the CheckBox you want to change the color of and then in the Properties box enter your new color string in the BackColor, or if it is for the text color enter the string into the ForeColor property.