From ebf674a03fdbc3e281dd99e6e271ebec24e2448e Mon Sep 17 00:00:00 2001 From: "Matteo Nastasi (mop)" Date: Wed, 11 Sep 2013 18:38:25 +0200 Subject: [PATCH] enhanced data model and sql builder.sh script to manage complex alter table scenario and add dry run option --- sql/builder.sh | 45 ++++++++++++++++++++------------ sql/sql.d/053-user-enh.sql | 6 ++++- sql/sql.d/054-continue-match.sql | 19 ++++++++++++-- sql/sql.d/060-tournament.sql | 16 ++++++++++++ web/briskin5/Obj/briskin5.phh | 4 +-- 5 files changed, 69 insertions(+), 21 deletions(-) create mode 100644 sql/sql.d/060-tournament.sql diff --git a/sql/builder.sh b/sql/builder.sh index 2a02633..5fbd33b 100755 --- a/sql/builder.sh +++ b/sql/builder.sh @@ -3,6 +3,16 @@ # # all this part is from mopshop and we will use it to construct the brisk database # + +if [ "$1" = "-d" -o "$1" = "--dryrun" ]; then + shift + DRY_RUN=y + psql () { + echo "MOCKPSQL params: $@" + cat + } +fi + if [ -f $HOME/.brisk-db.conf ]; then source $HOME/.brisk-db.conf elif [ -f $HOME/.db.conf ]; then @@ -51,6 +61,7 @@ one_or_all() { # MAIN # sht=0 +MATCH_DROP='^drop|^alter table.* drop ' if [ "$1" = "-s" ]; then shift @@ -64,12 +75,12 @@ elif [ "$1" = "destroy" ]; then echo "su root" su root -c "su postgres -c \"dropdb $DBBASE && dropuser $DBUSER\"" elif [ "$1" = "clean" ]; then - ( echo "-- MESG: clean start" ; one_or_all $2 | grep -i '^drop' | tac ; echo "-- MESG: clean end" ; ) | sqlexe $sht + ( echo "-- MESG: clean start" ; one_or_all $2 | egrep -i "$MATCH_DROP" | tac ; echo "-- MESG: clean end" ; ) | sqlexe $sht elif [ "$1" = "build" ]; then - ( echo "-- MESG: build start" ; one_or_all $2 | grep -iv '^drop' ; echo "-- MESG: build end" ; ) | sqlexe $sht + ( echo "-- MESG: build start" ; one_or_all $2 | egrep -iv "$MATCH_DROP" ; echo "-- MESG: build end" ; ) | sqlexe $sht elif [ "$1" = "rebuild" ]; then - ( echo "-- MESG: clean start" ; one_or_all $2 | grep -i '^drop' | tac ; echo "-- MESG: clean end" ; \ - echo "-- MESG: build start" ; one_or_all $2 | grep -iv '^drop' ; echo "-- MESG: build end" ; ) \ + ( echo "-- MESG: clean start" ; one_or_all $2 | egrep -i "$MATCH_DROP" | tac ; echo "-- MESG: clean end" ; \ + echo "-- MESG: build start" ; one_or_all $2 | egrep -iv "$MATCH_DROP" ; echo "-- MESG: build end" ; ) \ | sqlexe $sht elif [ "$1" = "psql" ]; then shift @@ -93,16 +104,18 @@ elif [ "$1" = "add" ]; then cat "$2" | sqlexe $sht else echo " USAGE" - echo " ./builder create" - echo " ./builder destroy" - echo " ./builder clean" - echo " ./builder build" - echo " ./builder rebuild" - echo " ./builder psql" - echo " ./builder piped" - echo " ./builder add " - echo " ./builder dump [dumpfile]" - echo " ./builder dumpall [dumpfile]" - echo " ./builder all" - echo " ./builder help" + echo " ./builder [-d|--dry-run] ..." + echo " commands are:" + echo " create" + echo " destroy" + echo " clean" + echo " build" + echo " rebuild" + echo " psql" + echo " piped" + echo " add " + echo " dump [dumpfile]" + echo " dumpall [dumpfile]" + echo " all" + echo " help" fi diff --git a/sql/sql.d/053-user-enh.sql b/sql/sql.d/053-user-enh.sql index e5c6b44..727a391 100644 --- a/sql/sql.d/053-user-enh.sql +++ b/sql/sql.d/053-user-enh.sql @@ -1,2 +1,6 @@ +ALTER TABLE #PFX#users DROP COLUMN last_dona; ALTER TABLE #PFX#users ADD COLUMN last_dona integer DEFAULT 0; -- last donate -ALTER TABLE #PFX#users ADD COLUMN supp_comp text DEFAULT 'ff0000ffff00' -- fg/bg supporter color \ No newline at end of file + +ALTER TABLE #PFX#users DROP COLUMN supp_comp; +ALTER TABLE #PFX#users ADD COLUMN supp_comp text DEFAULT 'ff0000ffff00'; -- fg/bg supporter color + diff --git a/sql/sql.d/054-continue-match.sql b/sql/sql.d/054-continue-match.sql index 2aefdca..3db1ff9 100644 --- a/sql/sql.d/054-continue-match.sql +++ b/sql/sql.d/054-continue-match.sql @@ -1,16 +1,31 @@ -DROP TABLE #PFX#bin5_table_order; -CREATE TABLE #PFX#bin5_table_order ( +DROP TABLE #PFX#bin5_table_orders; +CREATE TABLE #PFX#bin5_table_orders ( mcode integer REFERENCES #PFX#bin5_matches (code) ON DELETE cascade ON UPDATE cascade, ucode integer REFERENCES #PFX#users (code) ON DELETE cascade ON UPDATE cascade, pos integer ); +ALTER TABLE #PFX#bin5_matches DROP COLUMN mult_next; ALTER TABLE #PFX#bin5_matches ADD COLUMN mult_next integer DEFAULT -1; -- next multiplier + +ALTER TABLE #PFX#bin5_matches DROP COLUMN mazzo_next; ALTER TABLE #PFX#bin5_matches ADD COLUMN mazzo_next integer DEFAULT -1; -- next card shaker + +ALTER TABLE #PFX#bin5_games DROP COLUMN asta_pnt; ALTER TABLE #PFX#bin5_games ADD COLUMN asta_pnt integer DEFAULT -1; -- curr bet points + +ALTER TABLE #PFX#bin5_games DROP COLUMN pnt; ALTER TABLE #PFX#bin5_games ADD COLUMN pnt integer DEFAULT -1; -- curr points made + +ALTER TABLE #PFX#bin5_games DROP COLUMN asta_win; ALTER TABLE #PFX#bin5_games ADD COLUMN asta_win integer DEFAULT -1; -- curr caller id + +ALTER TABLE #PFX#bin5_games DROP COLUMN friend; ALTER TABLE #PFX#bin5_games ADD COLUMN friend integer DEFAULT -1; -- curr callee id + +ALTER TABLE #PFX#bin5_games DROP COLUMN mazzo; ALTER TABLE #PFX#bin5_games ADD COLUMN mazzo integer DEFAULT -1; -- curr card shaker + +ALTER TABLE #PFX#bin5_games DROP COLUMN mult; ALTER TABLE #PFX#bin5_games ADD COLUMN mult integer DEFAULT -1; -- curr multiplier diff --git a/sql/sql.d/060-tournament.sql b/sql/sql.d/060-tournament.sql new file mode 100644 index 0000000..d3f249a --- /dev/null +++ b/sql/sql.d/060-tournament.sql @@ -0,0 +1,16 @@ +DROP TABLE #PFX#bin5_tournaments; +CREATE TABLE #PFX#bin5_tournaments ( + code SERIAL PRIMARY KEY, + name text + ); + +-- add tournaments and field in the bin5_matches table +INSERT INTO #PFX#bin5_tournaments (code, name) VALUES (1, 'normal match'); +ALTER SEQUENCE #PFX#bin5_tournaments_code_seq RESTART WITH 2; + +ALTER TABLE #PFX#bin5_matches DROP COLUMN tourn; +ALTER TABLE #PFX#bin5_matches ADD COLUMN tourn integer DEFAULT 1; + +ALTER TABLE #PFX#bin5_matches DROP CONSTRAINT #PFX#bin5_matches_tourn_fkey; +ALTER TABLE #PFX#bin5_matches ADD FOREIGN KEY (tourn) REFERENCES #PFX#bin5_tournaments(code) ON UPDATE cascade ON DELETE cascade; + diff --git a/web/briskin5/Obj/briskin5.phh b/web/briskin5/Obj/briskin5.phh index 9e7fa04..e5389da 100644 --- a/web/briskin5/Obj/briskin5.phh +++ b/web/briskin5/Obj/briskin5.phh @@ -446,9 +446,9 @@ class Bin5_table extends Table { return ($ct); } - function rules_engine($bri, $action, $user) + function rules_engine($bri, $curtime, $action, $user) { - GLOBAL $G_all_points; + GLOBAL $G_all_points, $G_dbasetype; $pts = array(); -- 2.17.1