Makefile and init script for building/versioning a simple data backend.

init.sh

#!/usr/sh

DB="joy.db"
VERSION=$(cat VERSION)
SERVER=$(cat SERVER)     # url for downloading big data tables

if [ -e $DB ]
then
    rm $DB
fi

# import TSV data file ($path) into table
function import
{
    path=$1
    table=$2
    prefix=${path%%:*}
    extension=${path##*.}
    echo "Importing $path into $table"
    if [ $prefix = http ]
    then
        curl $path | gunzip -c | sed 1d | 
            sqlite3 -separator '	'  $DB ".import /dev/stdin $table"
    elif [ $extension = gz ]
        then
            gunzip -c $path | sed 1d | \
            sqlite3 -separator '	'  $DB ".import /dev/stdin $table"
    else
        sed 1d $path | \
            sqlite3 -separator '	'  $DB ".import /dev/stdin $table"
    fi
}


function read
{
    path=$1
    prefix=${path%%:*}
    extension=${path##*.}
    echo "Reading $path"
    if [ $prefix = http ]
    then
        curl $path | gunzip -c | sqlite3 $DB 
    elif [ $extension = gz ]
        then 
            gunzip -c $path | sqlite3 $DB
    else
        cat $path | sqlite3 $DB
    fi
}


# IMPORT LOCAL TABLES

TABLES="subjects sessions visits transcripts"

for table in $TABLES; do
    schema="tables/$table/schema.sql"
    read $schema
    for data in $(ls tables/$table/data*); do
        import $data $table 
    done;
done;


# IMPORT REMOTE TABLES

TABLES="utterances"

for table in $TABLES; do
    URL="http://$SERVER/joy/data/$table/$VERSION"
    schema="$URL/schema.sql.gz"
    data="$URL/data.tsv.gz" 
    read $schema
    import $data $table
done;


# UPDATE ALL TABLES

for update in $(ls tables/*/updates/$VERSION/*.sql); do
    read $update
done;


# ADD LOG TABLE AND TRIGGERS

read "tables/log/schema.sql"

layout.md

Sample Layout

.
├── Makefile
├── SERVER    # url for downloading big data tables
├── VERSION   # current version of dataset
├── init.sh
├── joy.db
├── update    # last update (based on svn revision num)
├── jobs
│   └── complete
└── tables
    ├── log
    ├── sessions
    ├── subjects
    ├── transcripts
    │   └── updates
    ├── utterances
    │   ├── problems
    │   └── updates
    └── visits
        └── updates

Makefile

# Makefile for building/versioning data backend

VERSION := $(shell cat VERSION)
 
.PHONY: svn all clean

all: svn joy.db update

svn:
  svn update

joy.db: init.sh
	@echo "Making joy.db ... please be patient"
	sh init.sh
	@echo "Finished making joy.db"

updates := $(wildcard tables/*/updates/$(VERSION)/*.sql)

apply: $(updates)
	@for file in $?; \
		do echo "Applying updates from $$file"; \
            sqlite3 joy.db < $$file; \
		done

update: svn apply
	@svn info $(JOY/data/tables/utterances/updates) | \
		 awk '/Rev:/ { sub(/.*: /, ""); print }' > update
				
clean:
	rm joy.db
	rm update