Tag Archives: development

VBA Macro to Convert Dates in a Range – the Dirty Way

I used this hacked Excel-Macro to convert all dates in a selected range from a manual data import using Excel to generate all required INSERTs for our MySql database

  • from 03.08.2011 16:51:01
  • to 2011-08-03 16:51:01

In case you wonder, where you can find your Visual-Basic editor and execute your VBA-code… follow these instructions. Here is the code:

Sub PrefixSingleQuoteToRange()
Dim cell As Range
Dim year As String
Dim month As String
Dim day As String

For Each cell In Selection.Cells
' Convert 03.08.2011 16:51:01 to 2011-08-03 16:51:01
year = Mid(DateValue(cell), 7, 4)
month = Mid(DateValue(cell), 4, 2)
day = Mid(DateValue(cell), 1, 2)
cell = "'" & year & "-" & month & "-" & day & " " & TimeValue(cell)
Next

End Sub

If you scratch your head… This is how I ‘generated’ my INSERT-statements:

  • One sheet equals one table.
  • Each line represents a record.
  • For example you have A to L fields paste the following into your cell M:

="INSERT INTO orders (order_id, processing_start_datetime, processing_end_datetime, delivery_datetime, staff_id, store_id, name, phone, comment_order, comment_procession, created_at, updated_at) VALUES ('"&A2&"', '"&B1&"' ,'"&C1&"' ,'"&D1&"' ,'"&E1&"' ,'"&F1&"' ,'"&G1&"' ,'"&H1&"' ,'"&I1&"','"&J1&"','"&K1&"','"&L1&"');"

  • It is a lot of copy and pasting. But in the end all your INSERTS can be copied and pasted into a txt-file to be dumped into your database.

If you know a smarter way to ‘import’ MSSQL data into MySql, please let me know. And I guess there must be many of them ;).

Emulate sendmail on your Dev Machine

In order to prevent email delivery during development and log all email messages that would have been delivered, you can actually do a simple trick: Replace the file /usr/sbin/sendmail (on Ubuntu, use ‘locate sendmail’ to find it if it lies elsewhere) with this little shell-script, or rather make a _bak of the original and save the following instead of the sendmail binary:

#!/bin/bash

LOGDIR="/tmp"
PREFIX="sendmail"
NOW=$(date +%Y-%m-%dT%H.%M.%S)
CNT=1
PRIVATELOG="$LOGDIR/$PREFIX-$NOW.$CNT.log"
COMBINEDLOG="$LOGDIR/$PREFIX-combined.log"

# If privatelogs are being used...
if [ ! -z "$PRIVATELOG" ]; then
# ...make sure the filename is unique and create the file
while [ -f $PRIVATELOG ]; do
CNT=$(($CNT + 1))
PRIVATELOG="$LOGDIR/$PREFIX-$NOW.$CNT.log"
done

echo "$0 $*" > $PRIVATELOG
else
# ...otherwise swap filenames
PRIVATELOG=$COMBINEDLOG
COMBINEDLOG=''
fi

echo "[$NOW]" >> $PRIVATELOG
while read BUF
do
echo $BUF >> $PRIVATELOG
done

# Append privatelog to combinedlog when both logs are used
if [ ! -z "$COMBINEDLOG" ]; then
echo "[$NOW]" >> $COMBINEDLOG
cat $PRIVATELOG >> $COMBINEDLOG
fi

exit 0

When your application now sends mail, these things happen:

  • No email is actually sent.
  • The message gets appended to the file /tmp/sendmail-combined.log, on which you could set a ‘tail -f’ in order to see which emails would have been sent and what contet they would have.
  • One new file (e.g. /tmp/sendmail-2011-02-08T08.02.48.1.log) gets written for every email sent. I personally only use the combined file.

Inspired by http://stackoverflow.com/questions/3710864/simulating-sendmail-with-dummy-script

Feedbackloops in Software Development

Since feedback is a form of communication, excessive feedback will have the effect like an explosion in communication paths and will eventually produce more noise than signal.

Any feedback loop in the system must help facilitate the management of change and efficiently adapt requirements along the the way. If this is not the case for a feedbackloop, folks will try to avoid it.

As developers we also experience a decrease in quality and value of feedback if it is not immediate enough and/or requires too much effort from a team member to receive it. So most of the feedbackloops are built right into modern software development. Take a look at the following list of areas where a software development team or team member receives feedback in different forms:

  • Your IDE checks syntax and indicates errors before you hit ‘save’.
  • A release demo for your customer generates prose user feedback.
  • Commit hooks refuse your changes.
  • Deployment on development and staging systems works or doesn’t.
  • The Compiler compiles or refuses to compile and returns errors.
  • Daily standup meetings serve as publishing institution of individual statuses, schedules and problems. Team members get updated, help each other out or problems get escalated up the command chain.
  • Pair programming for direct feedback from colleagues on development skills, technical decisions, used tools etc.
  • Sprint reviews reflect development processes, collect team feedback and improve them.
  • Test driven development repeatedly asserts requirements and APIs of existing and future components and their interdependencies.
  • The continuous integration server runs all test suites, generates documentation, runs the build, code sniffer etc. and informs the whole development team if something goes wrong.

I hope this article serves you as a short reminder for where and how feedback can create value in your software projects. And remember: Software is written by humans and feedback is the opposite of thought-reading, which often leads to unnecessary friction and pain.

Interesting Links:

Software Craftsmanship Manifesto

I have just received a new book: “Apprenticeship Patterns“: Skimming through the first pages I noticed the words in the 3rd column extending from the points in the 2nd column and thought, ‘Hmm, I have heard about these 2nd column points’…

The following table popped up in my mind, which I would like to share with you:

Software development practices:

Amazon

Traditional
<2000
Agile Manifesto
2001
Craftsman Manifesto
2010
processes and tools Individuals and interactions but also a community of professionals
comprehensive documentation Working software but also well-crafted software
contract negotiation Customer collaboration but also productive partnerships
following a plan Responding to change but also steadily adding value

Any ideas for a 4th column? Looking forward to actually reading the book…

The Ballpoint Game – A Scrum Simulation

I recently played the ballpoint game for the first time and was impressed…

  • … how clearly it demonstrates the different phases of a team going through the ‘storming, norming and performing’-phases for everyone – even for management spectators.
  • … how the iteration-rhythm helps to keep focus and increase the team performance to a very high and predictable level.
  • … how easy it felt to deliver good results once the team started to perform after the 2nd and 3rd iteration.
  • … how much fun it can be, to be part of and work in a cool software develoment team.

You can find various explanations on how the ballpoint game is organized and played.

Here is a nice video that illustrates what happens through the iterations: http://www.youtube.com/watch?v=d4-El7gJuZE

Thanks to Stefan and René for the inspiration!

Consistent Development Environments using VirtualMachines

As a development team we always run into situations where we have trouble setting up a proper development environment for each of the team members to get going or add new staff on the go. It annoyed me every time since it causes a lot of unnecessary communication and friction.

I often heard of virtualization but never actually played seriously with it. The idea is:

If we could have a virtual machine for every project that contains an equivalent environment like the production system, everybody working on it…

  • … could just rely on his development environment by just starting the VM without having to set up anything half-baked themselves.
  • … could use his favourite working environment OS, IDE and tools on which they are most comfortable and thus happy and productive.
  • … could work on their own checked out working copy using version control.
  • … could immedately see what they built refreshing the local browser or starting Unittests on the VM via ssh to check their dev increments.

We used http://www.virtualbox.org. A good starting point to get to know VirtualBox better and learn how to start your first virtual machine: https://help.ubuntu.com/community/VirtualBox

Our target was to be able to startup the development VM as guest system on any developers development machine being the host system, open a browser on the host (!) and call for example http://develop/ to see the webroot of the VM. Additionally we set up samba and ssh on the VM in order to have the webserver’s webroot on the VM available via the filesystem. In order to do that you need to…

  • …start your VM with networking set to ‘Host interface’ instead of the default NAT. This is explained in detail on this page (sorry German) http://www.nwlab.net/tutorials/virtualbox/virtual-networking.html – for me it was tricky to get the guest machine available on the host and have internet access at the same time.
  • …edit the hosts file (on OSX ‘sudo nano /private/etc/hosts’ and reboot) on the development machines and add something like the following line: ’192.168.56.101 develop’. To find out the IP enter ‘sudo ifconfig’ (OSX/Linux) on your host system after you have started the VM. You will see aditional adapters set by virtualbox and the IP address.
  • …configure /etc/samba/smb.conf on the VM, restart samba and connect (e.g. smb://develop/webroot). We check out a working copy of the applicaton under development directly onto the webroot and create a new PHP-Project there in our IDE. Update and commit directly from there.

If you search the web for ready-made VMs you find mostly VMware images. You can not run them directly in VirtualBox and need to convert them.

Under Linux you can convert the VMware image (.vmdk) into a VirtualBox image (.vdi) like this:

sudo apt-get install virtualbox-ose virtualbox-ose-guest-utils;
sudo apt-get install qemu;

qemu-img convert xxx.vmdk xxx.bin;
VBoxManage convertdd xxx.bin xxx.vdi

Install the required packages with apt-get install once. VBoxManage is part of VirtualBox. The last two lines do the conversion.


We ended up creating a fresh install from a Debian 5 netinstall iso. The iso-file can be mounted as CDROM on the creation of the new VM with VirtualBox. Receipes for setting up the appropriate LAMP environment with apt-get install can be found on the web. You only have to do it once. Save the state of your VM afterwards.


There are ways to generate a virtual machine from a physical server. Use Google to find receipes. I used http://www.partimage.org on a Debian Etch system with the live CDROM from http://www.sysresccd.org. This requires that you are able to umount your filesystem or rather boot into the live cd on the production/staging machine in order to generate the partimage.

You mount an external drive over the network or a usb harddrive. The partition (e.g. /dev/sda1) you would like to backup must be umounted. From the live cd you can see your partitions, including attached usb drives, with the ‘fdisk -l‘ command. Just mount the target (e.g. /mnt/usbdrive) and start partimage from the commandline. Dialogues guide you through the image creation.


In case you wonder what is meant by the ‘Host key’ to enter or leave a running VM with your mouse… it is the right (!) Strg-Button on your Keyboard.


I just installed Ubuntu Server on a VM from my MacBook. To have a usable keyboard once you logged on to the new VM, you must do the following in order to have a keymap including the pipe symbol, braces etc.:

  • sudo apt-get install console-data; #to install the keymaps
  • sudo loadkeys mac-macbook-de; #to set the keymap for German MacBook

Once you have done that, you can use your right Command-Key as ‘Alt-Gr-Key’ like on a PC keyboard. The pipe symbol is then typable with ‘Alt-Gr + >’, Braces and Brackets are typable via ‘Alt-Gr + 6,7,8,9′.


This is how you copy a virtual machine using VirtualBox tools:

$ VBoxManage clonevdi /Users/marco/MyMachine.vdi /Users/marco/MyMachine_copy.vdi
$ VBoxManage internalcommands setvdiuuid /Users/marco/MyMachine_copy.vdi

Generalizing Specialists, Agile Heros

Inspired by the Podcast ‘Tips and Advice – Manifesto for Agile Software Development‘ in which Bob Payne and George Dinwiddie discuss the points made in the Agile Manifesto and talk about agile environments and mechanics. If you would like to discover the power of agile teams you should definitely check this one out!

They also mention the term ‘Generalizing Specialist’. Scott W. Ambler wrote about this kind of developer in his essay ‘Generalizing Specialists: Improving Your IT Career Skills‘. This is a kind of developer who is constantly learning and playing with adjacent topics and thus widens his knowledge over the years of his worklife. The effects and advantages for the team, the project owner and the developer are depicted nicely in this essay.

Frameworks…

Find out interesting thoughts and experiences about the selection and usage of a frameworks for your projects in this set of slides ‘Living with Frameworks‘ by Stuart Herbert, Technical Manager at www.gradwell.com.

You will learn about:

  • How frameworks save you time (=money) and ensure quality but can also waste resources if applied in another way the framework was intended to be used.
  • The importance of the framework guru role.
  • That a chosen framework and architecture should be strategically introduced (top-down).
  • Introduction and proper use of a new framework has a steep learning curve.
  • Legacy code and the parallel maintenance hassle.
  • Refactor early, refactor often, perform regular code reviews with the framework guru.
  • Frameworks will not fix bad practice (specification, quality, no training).
  • Your framework should fit your overall development plan and practice.
  • Training your staff in your framework is a means of building your team.
  • Headcount on projects has increased, means more teamwork, organisation around needs to mature too.
  • Importance of upgrades and backward compatibility.

Software Development Myths

There is no doubt: The environment in which software is designed, produced and maintained has fundamental impact on what comes out below the bottom line for all players playing in this game. I have learnt that there is little in software development, you can do intuitively right the first time – even if you have a background from another production discipline. Surprisingly even people working in a software development environment sometimes just don’t have the required understanding nor are they open to it.

To make these points of un-openness transparent, a great way is to talk about common myths around software development. Other people have done this before, so all I deliver here is a list of links that I can recommend for reading and discussion among your team:

Now check yourself: How much cynicism did you feel reading things like ‘More developers mean faster development’ or ‘Every design decision is documented before it is implemented’ or ‘You dont need developers when designing software’ or ‘The answer to every challenge we face in the software industry lies in doing more work in shorter times’ etc?

If you did not feel any cynicism at all or very little, well… it is time to leave the cave.