From be87b9bf51733dede3d9e7097769e5a03ee48e75 Mon Sep 17 00:00:00 2001 From: "Matteo Nastasi (mop)" Date: Tue, 18 Feb 2014 18:36:56 +0100 Subject: [PATCH] points normalization: manage previous points --- TODO.txt | 7 ++++++ sql/builder.sh | 23 ++++++++++---------- sql/sql.d/066-insert-unnorm-games.sql.test | 20 +++++++++++++++++ sql/sql.d/068-points-normalization.sql | 5 +++++ sql/sql.d/100-anagr.sql.devel | 25 ++++++++++++++++++++++ sql/sql.d/100-anagr.sql.noinsert | 25 ---------------------- 6 files changed, 69 insertions(+), 36 deletions(-) create mode 100644 sql/sql.d/066-insert-unnorm-games.sql.test create mode 100644 sql/sql.d/068-points-normalization.sql create mode 100644 sql/sql.d/100-anagr.sql.devel delete mode 100644 sql/sql.d/100-anagr.sql.noinsert diff --git a/TODO.txt b/TODO.txt index a348846..d4a38d8 100644 --- a/TODO.txt +++ b/TODO.txt @@ -3,6 +3,13 @@ FEATURES | ----------+ + - PLACEMENT-NORMALIZATION + . Normalize previous points + . Denormalize "Info" view + . Denormalize explained matches + . Check placement + . Distinguish between inherited mult and local mult + - AUTO-MAINTENANCE . Terms Of Service acceptance DONE . print date of hard switch diff --git a/sql/builder.sh b/sql/builder.sh index da05960..3d145a6 100755 --- a/sql/builder.sh +++ b/sql/builder.sh @@ -7,7 +7,7 @@ MATCH_DROP='^DROP.*([^-]...|.[^-]..|..[^M].|...[^F])$|^ALTER TABLE.* DROP .*([^- # functions usage () { echo " USAGE" - echo " ./builder [-d|--dryrun] [-a|--allfiles] [-s|--short] ..." + echo " ./builder [-d|--dryrun] [-a|-p|--allfiles|--devfiles] [-s|--short] ..." echo " ./builder <-h|--help|help>" echo " commands are:" echo " create" @@ -46,16 +46,14 @@ sqlexe () { } one_or_all() { - if [ "$ALL_FILES" = "y" ]; then - sfx_files='*' - else - sfx_files='*.sql' - fi - - if [ "$1" = "" ]; then - cat sql.d/$sfx_files - else + if [ "$1" ]; then cat "$1" + elif [ "$TYPE_FILES" = "a" ]; then + cat sql.d/[0-9]* + elif [ "$TYPE_FILES" = "d" ]; then + cat sql.d/[0-9]*.{sql,devel} + else + cat sql.d/[0-9]*.sql fi } @@ -76,7 +74,10 @@ while [ $# -gt 0 ]; do } ;; -a|--allfiles) - ALL_FILES=y + TYPE_FILES=a + ;; + -p|--devfiles) + TYPE_FILES=d ;; -s|--short) SHORT=y diff --git a/sql/sql.d/066-insert-unnorm-games.sql.test b/sql/sql.d/066-insert-unnorm-games.sql.test new file mode 100644 index 0000000..a5089cb --- /dev/null +++ b/sql/sql.d/066-insert-unnorm-games.sql.test @@ -0,0 +1,20 @@ +DELETE FROM bsk_bin5_matches WHERE code = 100; + +INSERT INTO bsk_bin5_matches (code, ttok, tidx, mazzo_next, mult_next) VALUES (100, 'normalize_points', 2, 1, 1) RETURNING *; +INSERT INTO bsk_bin5_table_orders (mcode, ucode, pos) VALUES (100, 10101, 0); +INSERT INTO bsk_bin5_table_orders (mcode, ucode, pos) VALUES (100, 10102, 1); +INSERT INTO bsk_bin5_table_orders (mcode, ucode, pos) VALUES (100, 10103, 2); +INSERT INTO bsk_bin5_games (code, mcode, tstamp, act, asta_pnt, pnt, asta_win, friend, mazzo, mult) + VALUES (200, 100, to_timestamp(112211221122), 2, 60, 0, -1, -1, 0, 0) RETURNING *; +INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (200, 10101, 0); +INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (200, 10102, 0); +INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (200, 10103, 0); +UPDATE bsk_bin5_matches SET (mazzo_next, mult_next) = (2, 0) WHERE code = 100; +INSERT INTO bsk_bin5_games (code, mcode, tstamp, act, asta_pnt, pnt, asta_win, friend, mazzo, mult) + VALUES (201, 100, to_timestamp(112211221142), 0, 61, 37, 2, 1, 1, 1) RETURNING *; +INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (201, 10101, 2); +INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (201, 10102, -2); +INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (201, 10103, -4); + + + diff --git a/sql/sql.d/068-points-normalization.sql b/sql/sql.d/068-points-normalization.sql new file mode 100644 index 0000000..742f804 --- /dev/null +++ b/sql/sql.d/068-points-normalization.sql @@ -0,0 +1,5 @@ +-- +-- points normalization to be able to calculate placing +-- +UPDATE bsk_bin5_points AS p SET pts = pts / (2 ^ g.mult) FROM bsk_bin5_games AS g WHERE g.code = p.gcode; --MF +UPDATE bsk_bin5_points AS p SET pts = pts * (2 ^ g.mult) FROM bsk_bin5_games AS g WHERE g.code = p.gcode; --MB diff --git a/sql/sql.d/100-anagr.sql.devel b/sql/sql.d/100-anagr.sql.devel new file mode 100644 index 0000000..6b3796a --- /dev/null +++ b/sql/sql.d/100-anagr.sql.devel @@ -0,0 +1,25 @@ +-- +-- Populate users db. +-- + +-- macro for user_flag bit-field +-- define(USER_FLAG_TY_ALL, 0x000f0000); // done +-- define(USER_FLAG_TY_NORM, 0x00010000); // done +-- define(USER_FLAG_TY_SUPER, 0x00020000); // done +-- define(USER_FLAG_TY_FIRONLY, 0x00200000); // done +-- define(USER_FLAG_TY_SUSPEND, 0x00400000); // done +-- define(USER_FLAG_TY_DISABLE, 0x00800000); // done + +DELETE FROM #PFX#users WHERE code = 10101; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10101, 'uno', md5('one'), 'uno@pluto.com', CAST (X'00020000' as integer), 10101); +DELETE FROM #PFX#users WHERE code = 10102; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10102, 'due', md5('two'), 'due@pluto.com', CAST (X'00010000' as integer), 10101); +DELETE FROM #PFX#users WHERE code = 10103; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10103, 'tre', md5('thr'), 'tre@pluto.com', CAST (X'00010000' as integer), 10102); +DELETE FROM #PFX#users WHERE code = 10104; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10104, 'qua', md5('for'), 'qua@pluto.com', CAST (X'00010000' as integer), 10102); +DELETE FROM #PFX#users WHERE code = 10105; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10105, 'cin', md5('fiv'), 'cin@pluto.com', CAST (X'00010000' as integer), 10103); +DELETE FROM #PFX#users WHERE code = 10106; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10106, 'sei', md5('six'), 'sei@pluto.com', CAST (X'00210000' as integer), 10103); +ALTER SEQUENCE #PFX#users_code_seq RESTART WITH 10107; diff --git a/sql/sql.d/100-anagr.sql.noinsert b/sql/sql.d/100-anagr.sql.noinsert deleted file mode 100644 index f14a21e..0000000 --- a/sql/sql.d/100-anagr.sql.noinsert +++ /dev/null @@ -1,25 +0,0 @@ --- --- Populate users db. --- - --- macro for user_flag bit-field --- define(USER_FLAG_TY_ALL, 0x000f0000); // done --- define(USER_FLAG_TY_NORM, 0x00010000); // done --- define(USER_FLAG_TY_SUPER, 0x00020000); // done --- define(USER_FLAG_TY_FIRONLY, 0x00200000); // done --- define(USER_FLAG_TY_SUSPEND, 0x00400000); // done --- define(USER_FLAG_TY_DISABLE, 0x00800000); // done - -DELETE FROM #PFX#users WHERE code = 10101; -INSERT INTO #PFX#users (code, login, pass, email, type) VALUES (10101, 'uno', md5('one'), 'uno@pluto.com', CAST (X'00020000' as integer)); -DELETE FROM #PFX#users WHERE code = 10102; -INSERT INTO #PFX#users (code, login, pass, email, type) VALUES (10102, 'due', md5('two'), 'due@pluto.com', CAST (X'00010000' as integer)); -DELETE FROM #PFX#users WHERE code = 10103; -INSERT INTO #PFX#users (code, login, pass, email, type) VALUES (10103, 'tre', md5('thr'), 'tre@pluto.com', CAST (X'00010000' as integer)); -DELETE FROM #PFX#users WHERE code = 10104; -INSERT INTO #PFX#users (code, login, pass, email, type) VALUES (10104, 'qua', md5('for'), 'qua@pluto.com', CAST (X'00010000' as integer)); -DELETE FROM #PFX#users WHERE code = 10105; -INSERT INTO #PFX#users (code, login, pass, email, type) VALUES (10105, 'cin', md5('fiv'), 'cin@pluto.com', CAST (X'00010000' as integer)); -DELETE FROM #PFX#users WHERE code = 10106; -INSERT INTO #PFX#users (code, login, pass, email, type) VALUES (10106, 'sei', md5('six'), 'sei@pluto.com', CAST (X'00210000' as integer)); -ALTER SEQUENCE #PFX#users_code_seq RESTART WITH 10107; -- 2.17.1