Subject: Postgres soccer demo (rules, views, etc.)

From: Peter van Oosterom <[email-masked]>
Date: Wed, 16 Jun 93 22:44:09 MET DST

#! /bin/sh
# This is a shell archive.  Remove anything before this line, then unpack
# it by saving it into a file and typing "sh file".  To overwrite existing
# files, type "sh file -c".  You can also feed this as standard input via
# unshar, or by typing "sh <file", e.g..  If this archive is complete, you
# will see the following message at the end:
#		"End of shell archive."
# Contents:  README classes data destroy queries rules setup views
# Wrapped by pjmg1@gissn1 on Wed Jun 16 22:24:44 1993
PATH=/bin:/usr/bin:/usr/ucb ; export PATH
if test -f 'README' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'README'\"
else
echo shar: Extracting \"'README'\" \(537 characters\)
sed "s/^X//" >'README' <<'END_OF_FILE'
XPostgres soccer demo
X--------------------
X
XThis is a Postgres demonstration for keeping track of the rankings
Xin a soccer competition by using rules. In order to initialize the
Xdemo execute the shell script "setup" (creates database, defines classes
Xand rules, and fill the database with data). Some example queries are
Xgiven in the file "queries".
X
XNote that the method for assigning points is: 2 points for a victory
Xand 1 point for a draw. 
X
XThis demo was written by:
XPamela Mercera and Peter van Oosterom (FEL-TNO, The Netherlands).
END_OF_FILE
if test 537 -ne `wc -c <'README'`; then
    echo shar: \"'README'\" unpacked with wrong size!
fi
# end of 'README'
fi
if test -f 'classes' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'classes'\"
else
echo shar: Extracting \"'classes'\" \(598 characters\)
sed "s/^X//" >'classes' <<'END_OF_FILE'
X/*  
X * script to set up the soccer database 
X */
X
Xcreate  TEAM (name = char16, town = char16) 
X\g 
Xcreate  RANKING (won = int4,
X               draw = int4, lost = int4, 
X               goalsmade = int4, goalsagainst = int4)
X        inherits (TEAM)
X\g
Xcreate  MATCH (datum = abstime, home = char16, away = char16,
X                   hgoals = int4, agoals = int4) 
X\g
X
Xdefine rule AddToRanking is 
X       on append to TEAM  
X       do append RANKING (name = new.name, town = new.town, 
X                        won = 0, lost = 0,
X                        draw = 0, goalsmade = 0, goalsagainst = 0)
X\g
END_OF_FILE
if test 598 -ne `wc -c <'classes'`; then
    echo shar: \"'classes'\" unpacked with wrong size!
fi
# end of 'classes'
fi
if test -f 'data' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'data'\"
else
echo shar: Extracting \"'data'\" \(3428 characters\)
sed "s/^X//" >'data' <<'END_OF_FILE'
X/*
X * script to add some data to soccer data base
X */
X
Xappend TEAM(name = "ajax", town = "amsterdam")
X\g
Xappend TEAM(name = "den haag", town = "den haag")
X\g
Xappend TEAM(name = "feyenoord", town = "rotterdam")
X\g
Xappend TEAM(name = "fortuna", town = "sitard")
X\g
Xappend TEAM(name = "groningen", town = "groningen")
X\g
Xappend TEAM(name = "heerenveen", town = "heerenveen")
X\g
Xappend TEAM(name = "mvv", town = "maastricht")
X\g
Xappend TEAM(name = "nec", town = "nijmegen")
X\g
Xappend TEAM(name = "psv", town = "eindhoven")
X\g
X
X
Xappend MATCH(datum = "Jun 7 1991", home = "ajax", away = "den haag",
Xhgoals = 5, agoals = 0)
X\g
Xappend MATCH(datum = "Jun 7 1991", home = "feyenoord", away = "fortuna",
Xhgoals = 0, agoals = 0)
X\g
Xappend MATCH(datum = "Jun 7 1991", home = "groningen", away = "heerenveen",
Xhgoals = 3, agoals = 0)
X\g
Xappend MATCH(datum = "Jun 6 1991", home = "den haag", away = "ajax",
Xhgoals = 0, agoals = 1)
X\g
Xappend MATCH(datum = "Jun 6 1991", home = "fortuna", away = "feyenoord",
Xhgoals = 3, agoals = 2)
X\g
Xappend MATCH(datum = "Jun 2 1991", home = "den haag", away = "feyenoord",
Xhgoals = 0, agoals = 2)
X\g
Xappend MATCH(datum = "Jun 2 1991", home = "groningen", away = "fortuna",
Xhgoals = 3, agoals = 1)
X\g
Xappend MATCH(datum = "Jun 1 1991", home = "feyenoord", away = "den haag",
Xhgoals = 0, agoals = 2)
X\g
Xappend MATCH(datum = "Jun 1 1991", home = "fortuna", away = "groningen",
Xhgoals = 0, agoals = 0)
X\g
Xappend MATCH(datum = "May 26 1991", home = "ajax", away = "feyenoord",
Xhgoals = 2, agoals = 0)
X\g
Xappend MATCH(datum = "May 26 1991", home = "fortuna", away = "heerenveen",
Xhgoals = 1, agoals = 0)
X\g
Xappend MATCH(datum = "May 25 1991", home = "feyenoord", away = "ajax",
Xhgoals = 0, agoals = 4)
X\g
Xappend MATCH(datum = "May 25 1991", home = "heerenveen", away = "fortuna",
Xhgoals = 1, agoals = 2)
X\g
Xappend MATCH(datum = "May 19 1991", home = "fortuna", away = "den haag",
Xhgoals = 3, agoals = 1)
X\g
Xappend MATCH(datum = "May 19 1991", home = "groningen", away = "feyenoord",
Xhgoals = 1, agoals = 1)
X\g
Xappend MATCH(datum = "May 18 1991", home = "den haag", away = "fortuna",
Xhgoals = 0, agoals = 0)
X\g
Xappend MATCH(datum = "May 18 1991", home = "feyenoord", away = "groningen",
Xhgoals = 2, agoals = 0)
X\g
Xappend MATCH(datum = "May 12 1991", home = "fortuna", away = "ajax",
Xhgoals = 1, agoals = 1)
X\g
Xappend MATCH(datum = "May 12 1991", home = "groningen", away = "den haag",
Xhgoals = 2, agoals = 0)
X\g
Xappend MATCH(datum = "May 12 1991", home = "heerenveen", away = "feyenoord",
Xhgoals = 0, agoals = 0)
X\g
Xappend MATCH(datum = "May 11 1991", home = "ajax", away = "fortuna",
Xhgoals = 2, agoals = 0)
X\g
Xappend MATCH(datum = "May 11 1991", home = "den haag", away = "groningen",
Xhgoals = 1, agoals = 2)
X\g
Xappend MATCH(datum = "May 11 1991", home = "feyenoord", away = "heerenveen",
Xhgoals = 1, agoals = 1)
X\g
Xappend MATCH(datum = "May 5 1991", home = "groningen", away = "ajax",
Xhgoals = 1, agoals = 2)
X\g
Xappend MATCH(datum = "May 5 1991", home = "heerenveen", away = "den haag",
Xhgoals = 3, agoals = 0)
X\g
Xappend MATCH(datum = "May 4 1991", home = "ajax", away = "groningen",
Xhgoals = 1, agoals = 1)
X\g
Xappend MATCH(datum = "May 4 1991", home = "den haag", away = "heerenveen",
Xhgoals = 3, agoals = 2)
X\g
Xappend MATCH(datum = "Feb 28 1991", home = "heerenveen", away = "ajax",
Xhgoals = 1, agoals = 4)
X\g
Xappend MATCH(datum = "Feb 27 1991", home = "ajax", away = "heerenveen",
Xhgoals = 4, agoals = 0)
X\g
X
END_OF_FILE
if test 3428 -ne `wc -c <'data'`; then
    echo shar: \"'data'\" unpacked with wrong size!
fi
# end of 'data'
fi
if test -f 'destroy' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'destroy'\"
else
echo shar: Extracting \"'destroy'\" \(244 characters\)
sed "s/^X//" >'destroy' <<'END_OF_FILE'
X/*
X * script to remove classes and rules
X */
X
Xremove rule AddToRanking\g
Xremove rule Draw \g
Xremove rule HomeWon \g
Xremove rule AwayWon \g
Xremove rule HomeLost \g
Xremove rule AwayLost \g
X
Xdestroy  RANKING \g 
Xdestroy  MATCH \g
Xdestroy  TEAM \g
END_OF_FILE
if test 244 -ne `wc -c <'destroy'`; then
    echo shar: \"'destroy'\" unpacked with wrong size!
fi
# end of 'destroy'
fi
if test -f 'queries' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'queries'\"
else
echo shar: Extracting \"'queries'\" \(1164 characters\)
sed "s/^X//" >'queries' <<'END_OF_FILE'
X/* 
X * this script contains some queries
X */
X
Xretrieve (ShortRanking.all)
X      sort by points using >, saldogoals using >, goalsmade using >
X\g            
X
Xretrieve (LongRanking.all)
X      sort by points using >, saldogoals using >, goalsmade using >
X\g
X
Xretrieve (w.oid, w.all) from w in MATCH ["now"] 
X\g
X
Xretrieve (w.oid, w.all) from w in MATCH ["Jun 12 1991", epoch] 
X\g
X
Xretrieve (w.oid, w.all) from w in MATCH ["Jun 10 1991", "Jun 11 1991"] 
X\g
X
Xretrieve (w.oid, w.all) from w in MATCH [,] 
X\g
X
Xretrieve (RANKING.all) where RANKING.name = "heerenveen"
X\g
X
Xretrieve (s.all) 
X   from s in RANKING 
X   where ( (2*s.won) + s.draw) > "10"::int4 )
X\g
X
Xretrieve (w.oid, w.datum, t.all) 
X   from t in TEAM,
X        w in MATCH
X   where (w.hgoals > w.agoals) and (w.home = t.name)
X\g
X
X/* replace some matches and show that they are being vacuumed to
X   a secondary storage system 
X   Cancel all the matches on 7 June, the new date is 14 June     */
X
Xreplace w(datum = "Jun 14 1991") 
X   from w in MATCH
X   where w.datum = "Jun 7 1991"
X\g
X
Xretrieve (w.oid, w.all) 
X   from w in MATCH [,]
X\g
X
Xretrieve (TEAM.all)
X\g
X
Xretrieve (MATCH.all)
X\g
X
Xretrieve (RANKING.all)
X\g
END_OF_FILE
if test 1164 -ne `wc -c <'queries'`; then
    echo shar: \"'queries'\" unpacked with wrong size!
fi
# end of 'queries'
fi
if test -f 'rules' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'rules'\"
else
echo shar: Extracting \"'rules'\" \(1533 characters\)
sed "s/^X//" >'rules' <<'END_OF_FILE'
X/* 
X * script with the rules that update the RANKING instances when a match is played
X */
X
Xdefine rule Draw is
X   on append to MATCH where new.hgoals = new.agoals
X   do replace S(draw = S.draw + 1,
X                goalsagainst = S.goalsagainst + new.agoals,
X                goalsmade = S.goalsmade + new.hgoals)
X      from S in RANKING
X      where (S.name = new.home) or
X            (S.name = new.away)
X\g
X
Xdefine rule HomeWon is
X   on append to MATCH where new.hgoals > new.agoals
X   do replace S(won = S.won + 1,
X                goalsagainst = S.goalsagainst + new.agoals,
X                goalsmade = S.goalsmade + new.hgoals)
X      from S in RANKING
X      where S.name = new.home
X\g
X
Xdefine rule AwayLost is
X   on append to MATCH where new.hgoals > new.agoals
X   do replace S(lost = S.lost + 1,
X                goalsagainst = S.goalsagainst + new.hgoals,
X                goalsmade = S.goalsmade + new.agoals)
X      from S in RANKING
X      where S.name = new.away
X\g
X
Xdefine rule AwayWon is
X   on append to MATCH where new.hgoals < new.agoals
X   do replace S(won = S.won + 1,
X                goalsagainst = S.goalsagainst + new.hgoals,
X                goalsmade = S.goalsmade + new.agoals)
X      from S in RANKING
X      where S.name = new.away
X\g
X
Xdefine rule HomeLost is
X   on append to MATCH where new.hgoals < new.agoals
X   do replace S(lost = S.lost + 1,
X                goalsagainst = S.goalsagainst + new.agoals,
X                goalsmade = S.goalsmade + new.hgoals)
X      from S in RANKING
X      where S.name = new.home
X\g
X
END_OF_FILE
if test 1533 -ne `wc -c <'rules'`; then
    echo shar: \"'rules'\" unpacked with wrong size!
fi
# end of 'rules'
fi
if test -f 'setup' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'setup'\"
else
echo shar: Extracting \"'setup'\" \(192 characters\)
sed "s/^X//" >'setup' <<'END_OF_FILE'
X#! /bin/sh
X# Master script to create a soccer database from scratch.
X
X#set -v
XDB=${1:-soccer}
X
Xdestroydb $DB
Xcreatedb $DB
Xmonitor $DB << EOF
X\i classes
X\i rules
X\i data
X\i views
Xvacuum \g
XEOF
END_OF_FILE
if test 192 -ne `wc -c <'setup'`; then
    echo shar: \"'setup'\" unpacked with wrong size!
fi
chmod +x 'setup'
# end of 'setup'
fi
if test -f 'views' -a "${1}" != "-c" ; then 
  echo shar: Will not clobber existing file \"'views'\"
else
echo shar: Extracting \"'views'\" \(584 characters\)
sed "s/^X//" >'views' <<'END_OF_FILE'
X/*
X * script to define some views to enable easy retrieval of rankings
X */
Xdefine view ShortRanking (RANKING.name, RANKING.goalsmade,
X          points = ((2*RANKING.won) + RANKING.draw),
X          saldogoals = (RANKING.goalsmade - RANKING.goalsagainst))
X\g
X
Xdefine view LongRanking (RANKING.name,
X          games= (RANKING.won + RANKING.draw + RANKING.lost),
X          RANKING.won, RANKING.draw, RANKING.lost,
X          points = ((2*RANKING.won) + RANKING.draw),
X          RANKING.goalsmade, RANKING.goalsagainst,
X          saldogoals = (RANKING.goalsmade - RANKING.goalsagainst))
X\g
END_OF_FILE
if test 584 -ne `wc -c <'views'`; then
    echo shar: \"'views'\" unpacked with wrong size!
fi
# end of 'views'
fi
echo shar: End of shell archive.
exit 0