seriously simplified postgresql relationship notations/generation

I wanted to blog this small hack while it was still fresh in my head. I am using this to define and fine-tune a postgresql database prior to loading up the data (seperate scripts for another post perhaps)

from a bash commandline paste in:


function m2m () {
for i in $@;
do
echo "create table $i (id bigserial primary key , meta xml );";
done;
echo "create table $1_$2 ($1 bigint references $1, $2 bigint references $2, primary key ($1,$2 )); "
}
function relate () {
for i in $@;
do
IFS='+';
eval c=(${i[*]});
src=$c;
unset c[0];
for j in ${c[*]};
do
m2m $src $j;
done;
done
}

function bouncedb () {
p=($@);
db=$p;
dropdb $db;
createdb $db;
unset p[0];
relate ${p[*]}
}



then :

bouncedb jim artist+album album+song song+lyrics

create table artist (id bigserial primary key , meta xml ); create table album (id bigserial primary key , meta xml ); create table artist_album (artist bigint references artist, album bigint references album, primary key (artist,album )); create table album (id bigserial primary key , meta xml ); create table song (id bigserial primary key , meta xml ); create table album_song (album bigint references album, song bigint references song, primary key (album,song )); create table song (id bigserial primary key , meta xml ); create table lyrics (id bigserial primary key , meta xml ); create table song_lyrics (song bigint references song, lyrics bigint references lyrics, primary key (song,lyrics ));