Owncloud upgrading

We use Owncloud, and like to ‘harden’ our directories.. so we don’t have data under htdocs, and also so we have the right file permissions.

Assuming you have your owncloud install in /var/sites/owncloud

Then keep your data in data folder and htdocs in htdocs folder.

Write scripts (from owncloud documentation), put them in /var/sites/owncloud



# Sets permissions of the owncloud instance for updating

chown -R ${htuser}:${htgroup} ${ocpath}




printf "Creating possible missing Directories\n"
mkdir -p $ocpath/updater

printf "chmod Files and Directories\n"
find ${ocpath}/ -type f -print0 | xargs -0 chmod 0640
find ${ocpath}/ -type d -print0 | xargs -0 chmod 0750
printf "chown Directories\n"
chown -R ${rootuser}:${htgroup} ${ocpath}/
chown -R ${htuser}:${htgroup} ${ocpath}/apps/
chown -R ${htuser}:${htgroup} ${ocpath}/config/
chown -R ${htuser}:${htgroup} ${ocpath}/updater/
chmod +x ${ocpath}/occ
printf "chmod/chown .htaccess\n"

if [ -f ${ocpath}/.htaccess ]
  chmod 0644 ${ocpath}/.htaccess
  chown ${rootuser}:${htgroup} ${ocpath}/.htaccess




printf "chmod Files and Directories\n"
find ${ocdata}/ -type f -print0 | xargs -0 chmod 0640
find ${ocdata}/ -type d -print0 | xargs -0 chmod 0750

printf "chown Directories\n"
chown -R ${htuser}:${htgroup} ${ocdata}/

printf "chmod/chown .htaccess\n"
if [ -f ${ocdata}/.htaccess ]
  chmod 0644 ${ocdata}/.htaccess
  chown ${rootuser}:${htgroup} ${ocdata}/.htaccess


<VirtualHost *:443>

  ServerName your.server.com
  Header always add Strict-Transport-Security "max-age=15768000"
  DocumentRoot /var/sites/owncloud/htdocs
  <Directory "/var/sites/owncloud/htdocs">
    Options +FollowSymLinks
    AllowOverride All

  SSLEngine On
  SSLCertificateFile /etc/ssl/...
  SSLCertificateChainFile /etc/ssl/...
  SSLCertificateKeyFile /etc/ssl/..

  ErrorLog ${APACHE_LOG_DIR}/error-owncloud-ssl.log
  LogLevel warn
  CustomLog ${APACHE_LOG_DIR}/access-owncloud-ssl.log combined

/var/sites/owncloud/htdocs/config/config.php change these key settings as follows (of course you’ll need Redis to be working!):


$CONFIG = array (
  'trusted_domains' => 
  array (
    0 => 'your.server.com',
  'datadirectory' => '/var/sites/owncloud/data',
  'overwrite.cli.url' => 'https://your.server.com',
  'mail_smtpmode' => 'php',
  'mail_domain' => 'server.com',
  'memcache.local' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => 'localhost',
    'port' => 6379,
  'memcache.locking' => '\\OC\\Memcache\\Redis',

To do an upgrade, log in to your server, switch to root, then

cd /var/sites/owncloud

Back up your MySQL database. Now through the web interface, run the upgrade:


(Note the last one here isn’t necessary if this was already OK)

Posted in Uncategorized | Leave a comment

Changing the email send address in Office 365 without moving your domain

An update to the very good post by Peter Meinl at https://petermeinl.wordpress.com/2011/07/04/changing-the-email-send-address-in-office-365-without-moving-your-domain/#comment-1989

(read that first) (this is really so I don’t forget what I’ve done..)

A few updates for anyone doing this in 2017…

$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session
Set-Mailbox pit@leanwork.onmicrosoft.com -EmailAddresses SMTP:pit@petermeinl.de, pit@leanwork.onmicrosoft.com
Remove-PSSession $Session

You will also need to change the SPF records for (e.g.) petermeinl.de so that email from Exchange 365 passes the SPF test, by adding include:spf.protection.outlook.com before the -all.

Testing sending email to a gmail account at the moment, I get an SPF fail, but I am waiting for the DNS cache to update so hopefully this will sort itself out.

Posted in Uncategorized | Leave a comment

Excel function to count the number of matching words in two cells (‘fuzzy matching’)

I had a requirement to do a ‘fuzzy match’ of two columns of data, based on the number of matching words in each.

Add the following code to your Visual Basic module, and then in your Excel spreadsheet, do something like



Public Function FuzzyMatch(name1 As String, name2 As String) As Integer

    Dim strings1() As String
    Dim strings2() As String

    Dim string1 As Variant
    Dim string2 As Variant

    Dim matches As Integer

    name1 = StrippedName(name1)
    name2 = StrippedName(name2)

    strings1 = Split(name1, " ")
    strings2 = Split(name2, " ")

    matches = 0

    For Each string1 In strings1
        For Each string2 In strings2
            If LCase(string1) = LCase(string2) Then
                matches = matches + 1
            End If

    StoreMatch = matches
End Function

Public Function StrippedName(name As String)
    name = Replace(name, ",", " ")
    name = Replace(name, ".", " ")
    name = Replace(name, "  ", " ")
    name = Replace(name, "  ", " ")

    StrippedName = name
End Function
Posted in Uncategorized | Leave a comment

Installing Windows 10 over Linux (Ubuntu)

I have an Intel NUC that I’d installed Ubuntu on, and now I have decided to install Windows 10 over the top of it so the kids can use it.

I have just spent a merry two hours trying to work this out – so here are some top tips for anyone else trying to sort this. You’ll need another PC to work alongside the one you’re reformatting, and a couple of USB sticks (ideally 3).

  1. Make sure you’ve bought a Windows 10 licence key before you do anything!
  2. You will need to wipe your hard disk completely – this is because the partitions needed by Windows are in a different format.
  3. Follow the instructions at https://msdn.microsoft.com/en-gb/windows/hardware/commercialize/manufacture/desktop/winpe-create-usb-bootable-drive to make a bootable USB drive. Stick this USB into the machine you want to repurpose, and boot from that USB
  4. Follow the instructions at https://msdn.microsoft.com/en-gb/windows/hardware/commercialize/manufacture/desktop/configure-uefigpt-based-hard-drive-partitions#relatedsamplefiles – I found a separate USB stick was good to install this. I also had to use guesswork to find out what drive letter it was on. Also, unplug your Windows PE USB before running the DiskPart script
  5. Make a Windows 10 install USB at https://www.microsoft.com/en-gb/software-download/windows10
  6. Boot the new PC with that
  7. After putting in your licence key, choose ‘Custom Install’ and pick the Windows drive
  8. Oh, and it takes ages
Posted in Uncategorized | Leave a comment

Updating Rails / MySQL database datetime fields

We have a demo site that we show to prospective clients, and we reset it occasionally using a ‘seed database’ that we have created to give realistic reporting data.

The problem is that the timestamps on the seed database get old, which means that when you’re demo-ing, some of the reports and charts look bad.

So .. here is a little Ruby on Rails utility script that can get you an up to date looking database. It is VERY hacky so do be careful. In particular, any datetime fields you’ve created that might represent a future date will need to be excluded (we have one called ‘deadline’, you may have more.

First – back up your live demo database. It is quite easy to have run database migrations since you created your seed database.

Now, load your seed database onto your dev environment and run

rake db:migrate

And then back this up. Next, run

rails c

and paste the following into the rails command line

def update_database_times
puts "SELECT @most_recent_event := FROM_UNIXTIME(0);"
ActiveRecord::Base.descendants.each do |d|
next unless d.table_exists?
d.columns_hash.each do |k,v|
next if k == 'deadline'
puts "SELECT @most_recent_event := GREATEST(IFNULL(MAX(#{k}),@most_recent_event),@most_recent_event) FROM #{d.table_name};" if v.type == :datetime

puts "SELECT @yesterday := SUBDATE(NOW(),1);"
puts "SELECT @days_to_add := GREATEST(0,DATEDIFF(@yesterday,@most_recent_event));"
puts "SELECT @days_to_add := IF(caption='demo',@days_to_add,0) FROM clients;"

ActiveRecord::Base.descendants.each do |d|
next unless d.table_exists?
d.columns_hash.each do |k,v|
puts "UPDATE #{d.table_name} SET #{k} = DATE_ADD(#{k}, INTERVAL @days_to_add DAY);" if v.type == :datetime


Fire up the rails console in the project you are working with. Then paste in the code above, and run it using


This will generate output that will look something like this:

SELECT @most_recent_event := FROM_UNIXTIME(0);
SELECT @most_recent_event := GREATEST(IFNULL(MAX(created_at),@most_recent_event),@most_recent_event) FROM users;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(updated_at),@most_recent_event),@most_recent_event) FROM users;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(created_at),@most_recent_event),@most_recent_event) FROM tasks;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(updated_at),@most_recent_event),@most_recent_event) FROM tasks;
# more lines like these..
SELECT @yesterday := SUBDATE(NOW(),1);
SELECT @days_to_add := GREATEST(0,DATEDIFF(@yesterday,@most_recent_event));
SELECT @days_to_add := IF(caption='demo',@days_to_add,0) FROM clients;
UPDATE users SET created_at = DATE_ADD(created_at, INTERVAL @days_to_add DAY);
UPDATE users SET updated_at = DATE_ADD(updated_at, INTERVAL @days_to_add DAY);
UPDATE tasks SET created_at = DATE_ADD(created_at, INTERVAL @days_to_add DAY);
UPDATE tasks SET updated_at = DATE_ADD(updated_at, INTERVAL @days_to_add DAY);
# more lines like these..

We have put a protection line in where if the caption field in the clients database is not ‘demo’, then don’t change the datetime.

Now – try firing up your dev environment to check the database works. When you’re happy, copy it over to the demo environmnent. Add any new functionality and then back up that database to make it the new seed database.

Be careful; back everything up ahead of times, practice on localhost, and let me know if you’ve got any suggested improvements. Enjoy!

Posted in Uncategorized | Leave a comment

Some tips on getting Windows 10 to run VirtualBox with Ubuntu 14.04 for Rails

Here are some commands I found useful setting up my virtual machine ready for Ruby on Rails – as usual, exercise your own caution!

Host system

  • Windows 10
  • VirtualBox 4.3.30

Virtual Machine

  • Ubuntu 14.04

Set up a new unix user ready for VirtualBox (better than using the default vagrant user):

sudo adduser pete
sudo usermod -aG sudo pete

Shared folders appear on the guest OS in the /media folder with a prefix sf_ and with group vboxsf so set this up

sudo adduser pete vboxsf

Make sure you do this as the user account you’ll want to run Ruby as!

su pete

Get Ubuntu up to date!

sudo apt-get update
sudo apt-get upgrade

Run these again until you don’t get errors!

Get the copy-paste and folder sharing capabilities working

sudo apt-get install virtualbox-guest-dkms

Install ruby:

Instructions from Setup Ruby On Rails on Ubuntu 14.04 Trusty Tahr at https://gorails.com/setup/ubuntu/14.04 are really good.

Only differences:

Where it says

curl -L https://get.rvm.io | bash -s stable

You will get an message – you’ll need to do something like

gpg --keyserver hkp://keys.gnupg.net --recv-keys 409B6B17...2D39DC0E3

Where it says

git config --global user.name "YOUR NAME"

The YOUR NAME bit is your github user name

Installing nodejs has changed – instead of

sudo add-apt-repository ppa:chris-lea/node.js
sudo apt-get update
sudo apt-get install nodejs


curl -sL https://deb.nodesource.com/setup_0.12 | sudo bash -
sudo apt-get install nodejs

Mapping a folder in your home directory to the directory shared via host machine – e.g.

cd ~
ln -s /media/sf_RubymineProjects RubymineProjects
Posted in Techy Stuff | Leave a comment

Owncloud upgrade when data directory is not in standard htdocs folder

When I installed owncloud on our Debian Jessie server I didn’t like the way the data directory was under the htdocs path, so I did a custom install and created an owncloud-specific apache config file (owncloud.conf) in the apache sites-available directory (note we are running Apache 2.4) like this:

<VirtualHost *:443>
        ServerName myowcloud.mydomain.net

        DocumentRoot /var/mysites/owncloud/htdocs

        <Directory "/var/mysites/owncloud/htdocs">
                Options +FollowSymLinks
                AllowOverride All

        SSLEngine On
        SSLCertificateFile /path/to/mydomain.cer
        SSLCertificateChainFile /path/to/optional/rapidsslca.cer
        SSLCertificateKeyFile /path/to/mydomain.key.2014

        ErrorLog ${APACHE_LOG_DIR}/error-mydomain-ssl.log

        LogLevel warn

        CustomLog ${APACHE_LOG_DIR}/access-mydomain-ssl.log combined

Then you can use a2ensite owncloud to enable the site and apache2ctl graceful to restart apache with the new server fired up. I recommend apache2ctl configtest first. And apache2ctl -S tells you if the new config file has been used successfully.

The problem is that this breaks apt-get update owncloud which tries to install in the standard directory – and ends up just installing a fresh copy of owncloud and breaking Apache.

I would recommend you back up your data files first.

Below is the main shell script (which I stress I accept NO warranty for, etc!) that I wrote to automate this. You will need to have some unix skills to adapt this for your own solution, but here it is.


echo "This script will upgrade owncloud if you have it installed in a non-standard directory"
echo "This assumes the data directory is outside of the install folder"

MYSQL_BACKUP_FILENAME=/tmp/owncloud-sqlbkp_`date +"%Y%m%d"`.sql

CheckRunningAsRoot() {
  if [ "$(id -u)" != "0" ]; then
      echo "Error: This script must be run as root"
      exit 2

CheckPathExists() {
  if [ ! -d "$1" ]; then
      echo "Error: Directory $1 does not exist."
      exit 2

CheckPathDoesNotExist() {
  if [ -d "$1" ]; then
      echo "Error: Directory $1 already exists."
      exit 2

CheckFileExists() {
  if [ ! -f "$1" ]; then
      echo "Error: File $1 does not exist."
      exit 2

CheckFileDoesNotExist() {
  if [ -f "$1" ]; then
	echo "Error: File $1 already exists."
	exit 2

CheckRemoteFileExists() {
  if curl --output /dev/null --silent --head --fail "${1}"
      echo "URL ${1} exists"
      echo "Error: URL ${1} does not exist"
      exit 2

CheckDataFileNotInSubfolder() {
  if [ $(grep -c "'datadirectory'\s*=>\s*'" $1 ) -ne 0 ]
    if [ $(grep -c "'datadirectory'\s*=>\s*'$2" $1 ) -ne 0 ]
      echo "Error: it looks like the datadirectory file path is inside $2"
      exit 2
    echo "Error: could not find datadirectory directive in $1"
    exit 2

CallOcc() {
	echo "Calling occ with $@"
	sudo -u www-data php ${HTDOCS_DIRECTORY}/occ $@

CheckPathExists $HTDOCS_DIRECTORY/config
CheckFileExists $HTDOCS_DIRECTORY/occ
CheckRemoteFileExists $DOWNLOAD_URL

echo "Checking data file is not in subfolder of $HTDOCS_DIRECTORY"
CheckFileExists $CONFIG_FILE

echo ""
read -s -p "Enter mysql password for user $MYSQL_USER: " MYSQL_PASSWORD
echo ""
echo "Checking user $MYSQL_USER access to the $MYSQL_DATABASE database"
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD --database=$MYSQL_DATABASE -e "SELECT COUNT(*) FROM oc_users"
if [ $? -ne 0 ]; then
	echo "Error: MySQL problem"
	exit 1

echo ""
echo "Removing any existing temp file and downloading the installation"
rm -r $TMPDIR/owncloud
CheckPathExists $TMPDIR/owncloud

echo ""
echo "Putting Owncloud into maintenance mode"
CallOcc maintenance:mode --on

echo ""
echo "Backing up database"

echo ""
echo "Taking down the Owncloud site"
apache2ctl graceful

echo ""
echo "Backing up the existing htdocs directory and copying over the new scripts"

echo ""
echo "Setting permissions on folders and reinstating the original config.php"
chown -R root:root $HTDOCS_DIRECTORY
chown -R www-data:www-data $HTDOCS_DIRECTORY/apps
chown -R www-data:www-data $HTDOCS_DIRECTORY/config
cp -p $HTDOCS_BACKUP_DIRECTORY/config/config.php $HTDOCS_DIRECTORY/config/config.php

echo ""
echo "Running the upgrade script"
CallOcc upgrade

echo ""
echo "Ending maintenance mode and booting up the Owncloud site"
CallOcc maintenance:mode --off
apache2ctl graceful
Posted in Techy Stuff | Tagged , , , | Leave a comment