List of All Articles with the Topic "Development"

Shell script to Back up critical files (using RSYNC)

Posted on by Updated on
1

Following up from my article on backing up USB drives, this recipe backs up the critical files on my desktop to remote storage (a NAS device on my network). Note that PC2 is the desktop to be backed up, SLUG1 (192.168.1.2) is the NAS device and USER1 is the user id doing the backup.

#!/bin/bash

# Backup Key PC2 files to Slug1

# Sync 2007 picture folders
##rsync -rl /home/user1/pictures/2007/  user1@192.168.1.2:/public/pictures/2007/

# Ensure that /mnt/slug1-root/ is mounted
#if [ ! -e /mnt/slug1-root/user1/backups/PC2/bin/ ]; then
#    mount-slug-root.sh
#fi
# Ensure that /media/slug1-public/ is mounted
#if [ ! -e /media/slug1-public/DISK1.txt ]; then
#    mount-slug-public.sh
#fi

# NOTE that to configure the rsync sessions on SLUG1, edit the file /opt/etc/rsyncd.conf
TOPUB='user1@192.168.1.2::public'
# Use this form if not using sessions
#TOPUB='user1@192.168.1.2:/public'
# Or use this form if the remote folder is mounted locally
#TOPUB='/media/slug1-public'
TOJK='user1@192.168.1.2::pc2'
#TOJK='user1@192.168.1.2:/user1/backups/PC2'
#TOJK='/mnt/slug1-root/user1/backups/PC2'

JKDT=`date --rfc-3339=date`
JKLOG="/home/user1/Backups/pc2backup_$JKDT.log"

echo "Starting PC2 backup at `date`" >$JKLOG
echo "=================================================================="
echo "Starting PC2 backup at `date`"
echo "The log file is at $JKLOG, all backups are to SLUG1/pc2 or SLUG1/public"
echo " "

#--out-format=FORMAT     output updates using the specified FORMAT
#--log-file=FILE         log what we're doing to the specified FILE
#--chmod=CHMOD
#--exclude=PATTERN       exclude files matching PATTERN
#     --exclude-from=FILE     read exclude patterns from FILE
#     --include=PATTERN       don't exclude files matching PATTERN
#     --include-from=FILE
#--dry-run
#OPTS='--verbose --archive --recursive --links --perms --executability --owner --group --devices --specials --times --human-readable --delete --delete-after --stats --ipv4 --progress --password-file=/home/user1/bin/tmppw.tmp --dry-run'
OPTS='--verbose --archive --recursive --links --executability --devices --specials --times --human-readable --delete --delete-after --stats --ipv4 --progress'
echo "Back up various bits - WARNING: DELETES files from destination" >>$JKLOG

RSYNC_PASSWORD=`kdialog --password "Password for jk@slug1 please:"`
#kdialog --password "Password for jk@slug1 please:" >~/tmppw.tmp

echo "Backups to SLUG1/pc2"
echo " "
# ** JK BACKUPS **
echo "user1/bin"
echo "rsync $OPTS /home/user1/bin/ $TOJK/bin/" >>$JKLOG
rsync $OPTS /home/user1/bin/ $TOJK/bin/ >>$JKLOG 2>&1
echo "=========================================" >>$JKLOG
echo "user1/backups"
echo "rsync $OPTS /home/user1/Backups/ $TOJK/Backups/" >>$JKLOG
rsync $OPTS /home/user1/Backups/ $TOJK/Backups/ >>$JKLOG 2>&1
echo "=========================================" >>$JKLOG
#echo "rsync $OPTS /home/user1/Downloads/ $TOJK/Downloads/" >>$JKLOG
#rsync $OPTS /home/user1/Downloads/ $TOJK/Downloads/ >>$JKLOG 2>&1
#echo "=========================================" >>$JKLOG

echo "Backups to SLUG1/public"
echo " "
# ** Backups to public **

echo "user1/ebooks"
echo "rsync $OPTS /home/user1/eBooks/ $TOPUB/ebooks/sorting/" >>$JKLOG
rsync $OPTS /home/user1/eBooks/ $TOPUB/ebooks/sorting/ >>$JKLOG 2>&1
echo "=========================================" >>$JKLOG

echo "user1/pictures/Lnnnn"
echo "Back up picture files - WARNING: Does NOT delete files from destination" >>$JKLOG
OPTS='--verbose --archive --recursive --links --times --human-readable --stats --ipv4'
echo "rsync $OPTS /home/user1/Pictures/L2007/ $TOPUB/pictures/2007/" >>$JKLOG
rsync $OPTS /home/user1/Pictures/L2007/ $TOPUB/pictures/2007/ >>$JKLOG 2>&1
echo "rsync $OPTS /home/user1/Pictures/L2008/ $TOPUB/pictures/2008/" >>$JKLOG
rsync $OPTS /home/user1/Pictures/L2008/ $TOPUB/pictures/2008/ >>$JKLOG 2>&1
echo "=========================================" >>$JKLOG

echo "user1/backups/usbpen1 & usbpen2"
echo "rsync $OPTS /home/user1/Backups/USBPEN1/ $TOJK/Backups/USBPEN1/" >>$JKLOG
rsync $OPTS /home/user1/Backups/USBPEN1/ $TOJK/Backups/USBPEN1/ >>$JKLOG 2>&1
echo "=========================================" >>$JKLOG
echo "rsync $OPTS /home/user1/Backups/USBPEN2/ $TOJK/Backups/USBPEN2/" >>$JKLOG
rsync $OPTS /home/user1/Backups/USBPEN2/ $TOJK/Backups/USBPEN2/ >>$JKLOG 2>&1
echo "=========================================" >>$JKLOG

echo "Google Earth places"
echo "Back up Google Earth myplaces.kml" >>$JKLOG
rsync $OPTS /home/user1/.googleearth/myplaces.kml $TOPUB/maps+walks/pc2-myplaces.kml >>$JKLOG 2>&1
echo "=========================================" >>$JKLOG

#echo '' >~/tmppw.tmp
#rm ~/tmppw.tmp

echo " "
echo "ENDING PC2 backup at `date`" >>$JKLOG
echo "ENDING PC2 backup at `date`"
echo "=================================================================="

# To run under schedule
#    Log
#    Replace password

I have a similar script that runs on the NAS device which backs key files on that to a remote hosting service on a different continent! That way, I don’t have to worry about the house burning down or being burgled.

Automatically Backing up a USB Drive with RSYNC (KDE)

Posted on by Updated on
1

USB Drives of all kinds need to be backed up and the best backup is an automatic one (it’s the only way to make sure that it gets done!).

So here is one recipe for doing just that using RSYNC and some BASH scripting magic.

I’ve split this into two files. You don’t have to do this of course and one may well be better for you. I used two because I can run the second one manually as well. Put everything in autorun.sh if you want to backup each drive individually, however, note that KDE produces an annoying extra dialog (a security warning) asking if you really want to run the autorun.

  • autorun.sh
    This resides in the root of the USB drive and is executed automatically by KDE when the drive is detected (though not if the drive is attached when booting)
  • usb-backup-manual.sh
    This is a bit of a nasty hack, I have manually configured a list of drives that might be attached so that I can back them all up together. Not elegant but it works for me.

autorun.sh

#!/bin/bash

# KDE will automatically run an executable file called: .autorun, autorun or autorun.sh (in that order)
# Alternatively, a non-executable file called .autoopen or autoopen can contain a file name
# of a non-executable file on the media which will be opened with the default app for that file.
# See: http://standards.freedesktop.org/autostart-spec/autostart-spec-0.5.html#mounting

# Also see: http://b50.roxor.pl/~michal/linux/autorun.txt
# for some interesting ideas

# Where are we running from? e.g. /media/usbpen1
mediaDir=$(echo $0|sed 's/autorun//')

kdialog --title "USB Drive Backup" --yesno "I'd like to backup the USB drives, can I?"
if [ $? = 0 ]; then
echo " OK Selected, I'm going"
echo "Autobackup run: `date`" >usb-linux-auto-backup.log
exec ~/bin/usb-backup-manual.sh
else
echo " Cancel selected, so do nothing - bye."
fi
</code></pre><h4>usb-backup-manual.sh</h4><pre><code>
#! /bin/bash

#http://www.sanitarium.net/golug/rsync_backups.html
#http://www.mikerubel.org/computers/rsync_snapshots/
#http://rsync.samba.org/examples.html

echo "Starting USB Backup: `date`"
echo "Starting USB Backup: `date`" >~/Backups/usb-backup-manual.log

# From
MNT="/media"
# To
TO="/home/julian/Backups"

dcopRef=`kdialog --progressbar "Starting backup - press cancel to stop further processing (no next step)" 4`
dcop $dcopRef showCancelButton true

#until test "true" == `dcop $dcopRef wasCancelled`; do
for f in "CF2G1" "SD1G1" "USBPEN1" "USBPEN2"
do
dcop $dcopRef setLabel "Backing up $MNT/$f  ==>  $TO"
echo "--------------------------------------"
echo "$f  ==>  $TO"
inc=$((`dcop $dcopRef progress` + 1))
sleep 2
if [ -e $MNT/$f ]; then
  dcop $dcopRef setProgress $inc
  RSCMD="rsync --recursive --times --delete-during --stats --human-readable -h $MNT/$f $TO"
  echo $RSCMD
  echo $RSCMD  >>~/Backups/usb-backup-manual.log
  $RSCMD
  dcop $dcopRef setLabel "RSYNC for $f finished"
else
  dcop $dcopRef setProgress $inc
  dcop $dcopRef setLabel "$MNT/$f not mounted"
  echo "$MNT/$f not mounted"
  echo "$MNT/$f not mounted"  >>~/Backups/usb-backup-manual.log
fi
echo "======================================="
sleep 2
done

dcop $dcopRef close

echo "End: `date`"
echo "End: `date`" >>~/Backups/usb-backup-manual.log

Note the use of KDialog to provide a minimal GUI. In the second file, KDialog produces a progress bar.

Also note the RSYNC parameters. These are always painful to get to grips with so it is nice to have an example to work from. In this case I am backing up so I am making sure that the backup is an exact copy of the original (as opposed to synchronising which would allow changes to happen on either side).

Development Virtual Machine (VirtualBox)

Posted on by Updated on
Reply

I’ve been thinking ahead to a change of job recently. Knowing that I’ll be getting a new Windows based laptop and needing to have development capabilities and having developed a taste for Linux ;)

I’ve used my favourite VM tool VirtualBox (now owned by Sun) to create a sparlkly new OpenSUSE 11.0 virtual machine complete with Apache, MySQL, PHP, etc. as well as office tools such as Open Office, mind/concept-mapping and diagraming applications.

Unlike the Windows XP VM that I use on my Linux desktop to give me access to Windows applications – which needs 2GB of RAM to perform nicely, the SUSE VM only needs 1GB of RAM to feel as fast (even though XP doesn’t have Apache, MySQL, etc. running.

Although I’ve created this on my Linux desktop, it should be easy enough to transfer to my new laptop. To help keep file sizes down, I’ve chosen to use three virtual disks. One for SWAP, one for /home and one for the root. This will make it a bit easier to transfer back and forth if I need to – though I’ll probably end up with two separate and different copies as I’m already finding that doing personal development work is much easier on the VM than it is on the host OS thanks to it being a more focused machine with less rubbish installed.

Cannot directly use the IF function on a MySQL query that uses GROUP BY

Posted on by Updated on
Reply

Cannot directly use the IF function on a query that uses GROUP BY.
This tip provides an alternative that is cross-db.

It is extracted from:
http://surfnet.dl.sourceforge.net/sourceforge/souptonuts/README_mysql.txt

SQL Characteristic Functions: Do it without “if”, “case”, or “GROUP_CONCAT”.
Yes, there is use for this…”if” statements sometimes cause problems when used in combination.

The simple secret, and it’s also why they work in almost all databases, is the following functions:

  • sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively
  • abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0
  • 1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0

Quick example:

sign(-1) = -1,  abs( sign(-1) ) = 1,  1-abs(sign(-1) ) = 0

Data for full example:

CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY  (pkey)
);

insert into exams (name,exam,score) values ('Bob',1,75);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,78);
insert into exams (name,exam,score) values ('Bob',4,80);

insert into exams (name,exam,score) values ('Sue',1,90);
insert into exams (name,exam,score) values ('Sue',2,97);
insert into exams (name,exam,score) values ('Sue',3,98);
insert into exams (name,exam,score) values ('Sue',4,99);

mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
|    1 | Bob  |    1 |    75 |
|    2 | Bob  |    2 |    77 |
|    3 | Bob  |    3 |    78 |
|    4 | Bob  |    4 |    80 |
|    5 | Sue  |    1 |    90 |
|    6 | Sue  |    2 |    97 |
|    7 | Sue  |    3 |    98 |
|    8 | Sue  |    4 |    99 |
+------+------+------+-------+
8 rows in set (0.00 sec)

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Note, the above pivot table was created with one select statement

You may think IF’s would be cleaner. NO, WATCH OUT!
Look the following gives INCORRECT RESULTS!

select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |  NULL |  NULL |  NULL |
| Sue  |    90 |  NULL |  NULL |  NULL |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Note the above gives indeterminate results.

Paul DuBois [ paul at snake.net ] showed me the correct way to
perform this select statement. According to him

When you include a GROUP BY clause in a query, the only values you
can select are the grouped columns or summary values calculated
from the groups. If you display additional columns, they’re not
tied to the grouped columns and the values displayed for them are
indeterminate.

If you rewrite the query like this, you get the correct result:

select name,
sum(if(exam=1,score,null)) as exam1,
sum(if(exam=2,score,null)) as exam2,
sum(if(exam=3,score,null)) as exam3,
sum(if(exam=4,score,null)) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) -
sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) -
sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) -
sum(score*(1-abs(sign(exam- 3)))) as delta_3_4
from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 |delta_3_4 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |2 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |1 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

Above delta_1_2 shows the difference between the first and second exams, with the numbers
being positive because both Bob and Sue improved their score with each exam. Calculating
the deltas here shows it’s possible to compare two rows, not columns which is easily done
with the standard SQL statements but rows in the original table.

mysql>select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam-1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam-2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam-3)))) as delta_3_4,

sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam-1))))  +
sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam-2))))  +
sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam-3))))  as TotalIncPoints
from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 |delta_3_4 | TotalIncPoints |

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |2 |              5 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |1 |              9 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
2 rows in set (0.00 sec)

TotalIncPoints shows the sum of the deltas.

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam-1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam-2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam-3)))) as delta_3_4,

sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam-1))))  +
sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam-2))))  +
sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam-3))))  as TotalIncPoints,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 |delta_3_4 | TotalIncPoints | AVG   |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |2 |              5 | 77.50 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |1 |              9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)

It’s possible to combine Total Increasing Point TotalIncPoints with AVG. In fact, it’s possible to combine all of the example cuts of the data into one SQL statement, which provides additional options for displaying data on your page

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2  as AVG1_2,

(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,

(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG|
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob  |    75 |    77 |    78 |    80 |  76.00 |  77.50 |  79.00 |77.50 |
| Sue  |    90 |    97 |    98 |    99 |  93.50 |  97.50 |  98.50 |96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)

Exam scores are listing along with moving averages…again it’s all with one select statement.

Defaulting optional parameters

Posted on by Updated on
Reply

If you have a function that takes an optional parameter – e.g.

function blah($xx,$yy='Default',$zz){ ....

You can use the function and ensure that the optional parm always takes the correct default (even if the definition changes) by passing a “NULL” to it. e.g.

...
$mydate=blah($something,NULL,$more);
...