3to4.sql

.bail on

begin transaction;

update globals set value=4 where name='dbSchemaVersion';

-- remove lastGamesSince
delete from globals where name = 'lastGamesSince';

-- add a processed column and fill it with a value
ALTER TABLE matches ADD COLUMN processed real;

update matches set processed = 1456360000;

commit;
vacuum;

run.sh

#!/bin/bash

### Code below is generic and can be reused
set -u
expectedSchemaVersion=3
db=$1
migrationDir="$(dirname "$0")"

### Check we're in the right directory
if [ ! -e "lib.py" ]; then
    echo "Run this script from the root directory of the repo."
    exit 1
fi

### Test the schema version
v=$(echo "select value from globals where name = 'dbSchemaVersion';"  | sqlite3 $db 2> /dev/null)
if [ $? == 1 ]; then
    # No schema version at all!
    v=-1
fi
if [ $v != $expectedSchemaVersion ]; then
    echo "Error: expected schema version $expectedSchemaVersion got $v"
    exit 1
fi

set -e
### Code above is generic and can be reused



### Run the migration SQL
sqlite3 "$db" < $migrationDir/3to4.sql

test.sh

#!/bin/bash


### Code below is generic and can be reused
migrationDir="$(dirname "$0")"
export lolSlackLogging=${lolSlackLogging:-21}

fromDir=/mnt/sdb1/BACKUPS/rsnapshot/hourly.0/wk/home/nelson/lolslackbot

dbToTest=$fromDir/games.sqlite
matchesToTest=$fromDir/matches
finalSchema=../test/dev.sqlite

# Make a test environment
dir=`mktemp -d --tmpdir lolslack-XXXXXX`
echo "Test data directory is $dir"
export lolSlackDir=$dir
db=$lolSlackDir/games.sqlite

# Make a directory for testing
/bin/cp -a $dbToTest $db
ln -s $matchesToTest $lolSlackDir/matches
### Code above is generic and can be reused



# Run the migration script
$migrationDir/run.sh $db

echo

# Ensure the schema change went through
echo "Diffing the schema; should be empty."
/tmp/s/sqldiff --schema $db $finalSchema

# Report some stuff from the new table
echo
echo -n "Schema version should be 4: "
echo "select value from globals where name = 'dbSchemaVersion';" | sqlite3 $db
echo -n "Should be 0 unprocessed matches: "
echo "select count(*) from matches where processed is null;" | sqlite3 $db
echo -n "Should be 1456360000: "
echo "select processed from matches order by matchCreation desc limit 1;" | sqlite3 $db
echo -n "Should be 1 global variable: "
echo "select count(*) from globals;" | sqlite3 $db

echo "Results are in $dir"