From af5d80281ca7394f3365bbbeaabbe5d471ebe310 Mon Sep 17 00:00:00 2001
From: "Matteo Nastasi (mop)" <nastasi@alternativeoutput.it>
Date: Wed, 26 Feb 2014 08:14:17 +0100
Subject: [PATCH] add new tag #NOW# to be able to use current time in sql
 statements, used #PFX# tag instead of bsk_ in new sql files

---
 sql/builder.sh                             |  5 +-
 sql/sql.d/066-insert-unnorm-games.sql.test | 62 +++++++++++-----------
 sql/sql.d/068-points-normalization.sql     |  4 +-
 3 files changed, 36 insertions(+), 35 deletions(-)

diff --git a/sql/builder.sh b/sql/builder.sh
index 3d145a6..e71d26e 100755
--- a/sql/builder.sh
+++ b/sql/builder.sh
@@ -3,6 +3,7 @@
 #
 MATCH_DROP='^DROP.*([^-]...|.[^-]..|..[^M].|...[^F])$|^ALTER TABLE.* DROP .*([^-]...|.[^-]..|..[^M].|...[^F])$|^DELETE .*([^-]...|.[^-]..|..[^M].|...[^F])$|--MB$'
 
+DATECUR="$(date +%s)"
 
 #  functions
 usage () {
@@ -37,9 +38,9 @@ sqlexe () {
     sht=$1
 
     if [ "$SHORT" = "y" ];  then
-        sed "s/#PFX#/$PFX/g" | psql -a $pg_args 2>&1 | egrep 'ERROR|^-- MESG'
+        sed "s/#PFX#/$PFX/g;s/#NOW#/$DATECUR/g" | psql -a $pg_args 2>&1 | egrep 'ERROR|^-- MESG|^-- FILE '
     else
-        sed "s/#PFX#/$PFX/g" | psql -a $pg_args
+        sed "s/#PFX#/$PFX/g;s/#NOW#/$DATECUR/g" | psql -a $pg_args
     fi
 
     return 0
diff --git a/sql/sql.d/066-insert-unnorm-games.sql.test b/sql/sql.d/066-insert-unnorm-games.sql.test
index 3430369..930815e 100644
--- a/sql/sql.d/066-insert-unnorm-games.sql.test
+++ b/sql/sql.d/066-insert-unnorm-games.sql.test
@@ -1,37 +1,37 @@
-DELETE FROM bsk_bin5_matches WHERE code = 100 OR code = 101;
+DELETE FROM #PFX#bin5_matches WHERE code = 100 OR code = 101;
 
-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(139000000), 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(139000100), 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);
+INSERT INTO #PFX#bin5_matches (code, ttok, tidx, mazzo_next, mult_next) VALUES (100, 'normalize_points', 2, 1, 1) RETURNING *;
+INSERT INTO #PFX#bin5_table_orders (mcode, ucode, pos) VALUES (100, 10101, 0);
+INSERT INTO #PFX#bin5_table_orders (mcode, ucode, pos) VALUES (100, 10102, 1);
+INSERT INTO #PFX#bin5_table_orders (mcode, ucode, pos) VALUES (100, 10103, 2);
+INSERT INTO #PFX#bin5_games (code, mcode, tstamp, act, asta_pnt, pnt, asta_win, friend, mazzo, mult)
+                      VALUES (200, 100, to_timestamp(#NOW# - 1000), 2, 60, 0, -1, -1, 0, 0) RETURNING *;
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (200, 10101, 0);
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (200, 10102, 0);
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (200, 10103, 0);
+UPDATE #PFX#bin5_matches SET (mazzo_next, mult_next) = (2, 0) WHERE code = 100;
+INSERT INTO #PFX#bin5_games (code, mcode, tstamp, act, asta_pnt, pnt, asta_win, friend, mazzo, mult)
+                      VALUES (201, 100, to_timestamp(#NOW# - 900), 0, 61, 37, 2, 1, 1, 1) RETURNING *;
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (201, 10101, 2);
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (201, 10102, -2);
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (201, 10103, -4);
 
 
-INSERT INTO bsk_bin5_matches (code, ttok, tidx, mazzo_next, mult_next) VALUES (101, 'normalize_points2', 2, 1, 0) RETURNING *;
-INSERT INTO bsk_bin5_table_orders (mcode, ucode, pos) VALUES (101, 10101, 0);
-INSERT INTO bsk_bin5_table_orders (mcode, ucode, pos) VALUES (101, 10102, 1);
-INSERT INTO bsk_bin5_table_orders (mcode, ucode, pos) VALUES (101, 10103, 2);
-INSERT INTO bsk_bin5_games (code, mcode, tstamp, act, asta_pnt, pnt, asta_win, friend, mazzo, mult)
-                      VALUES (202, 101, to_timestamp(139000200), 2, 60, 0, -1, -1, 0, 0) RETURNING *;
-INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (202, 10101, 0);
-INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (202, 10102, 0);
-INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (202, 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 (203, 101, to_timestamp(139000300), 0, 81, 37, 2, 1, 1, 1) RETURNING *;
-INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (203, 10101, 6);
-INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (203, 10102, -6);
-INSERT INTO bsk_bin5_points (gcode, ucode, pts) VALUES (203, 10103, -12);
+INSERT INTO #PFX#bin5_matches (code, ttok, tidx, mazzo_next, mult_next) VALUES (101, 'normalize_points2', 2, 1, 0) RETURNING *;
+INSERT INTO #PFX#bin5_table_orders (mcode, ucode, pos) VALUES (101, 10101, 0);
+INSERT INTO #PFX#bin5_table_orders (mcode, ucode, pos) VALUES (101, 10102, 1);
+INSERT INTO #PFX#bin5_table_orders (mcode, ucode, pos) VALUES (101, 10103, 2);
+INSERT INTO #PFX#bin5_games (code, mcode, tstamp, act, asta_pnt, pnt, asta_win, friend, mazzo, mult)
+                      VALUES (202, 101, to_timestamp(#NOW# - 800), 2, 60, 0, -1, -1, 0, 0) RETURNING *;
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (202, 10101, 0);
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (202, 10102, 0);
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (202, 10103, 0);
+UPDATE #PFX#bin5_matches SET (mazzo_next, mult_next) = (2, 0) WHERE code = 100;
+INSERT INTO #PFX#bin5_games (code, mcode, tstamp, act, asta_pnt, pnt, asta_win, friend, mazzo, mult)
+                      VALUES (203, 101, to_timestamp(#NOW# - 700), 0, 81, 37, 2, 1, 1, 1) RETURNING *;
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (203, 10101, 6);
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (203, 10102, -6);
+INSERT INTO #PFX#bin5_points (gcode, ucode, pts) VALUES (203, 10103, -12);
 
 
 
diff --git a/sql/sql.d/068-points-normalization.sql b/sql/sql.d/068-points-normalization.sql
index 742f804..18b4385 100644
--- a/sql/sql.d/068-points-normalization.sql
+++ b/sql/sql.d/068-points-normalization.sql
@@ -1,5 +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
+UPDATE #PFX#bin5_points AS p SET pts = pts / (2 ^ g.mult) FROM #PFX#bin5_games AS g WHERE g.code = p.gcode; --MF
+UPDATE #PFX#bin5_points AS p SET pts = pts * (2 ^ g.mult) FROM #PFX#bin5_games AS g WHERE g.code = p.gcode; --MB
-- 
2.17.1