From 2054502215862619b019d6ffccb6692a9b5281ed Mon Sep 17 00:00:00 2001 From: "Matteo Nastasi (mop)" Date: Sun, 13 Feb 2011 17:26:06 +0100 Subject: [PATCH] data for places and to archive match points --- sql/STORAGE.txt | 16 ++++++++++++ sql/sql.d/050-bin5.sql | 25 +++++++++++++++++++ sql/sql.d/051-bin5-places.sql | 25 +++++++++++++++++++ ...gr.sql.noinsert => 100-anagr.sql.noinsert} | 0 4 files changed, 66 insertions(+) create mode 100644 sql/STORAGE.txt create mode 100644 sql/sql.d/050-bin5.sql create mode 100644 sql/sql.d/051-bin5-places.sql rename sql/sql.d/{005-anagr.sql.noinsert => 100-anagr.sql.noinsert} (100%) diff --git a/sql/STORAGE.txt b/sql/STORAGE.txt new file mode 100644 index 0000000..749e71f --- /dev/null +++ b/sql/STORAGE.txt @@ -0,0 +1,16 @@ +== rende la view dei punteggi == +select m.ttok, m.tidx, g.code, g.tstamp, p.pts, u.login from bsk_bin5_points as p, bsk_bin5_games as g, bsk_bin5_matches as m, bsk_users as u where p.ucode = u.code AND p.gcode = g.code AND g.mcode = m.code AND g.tstamp > '2010-10-01 00:00:00' ORDER BY g.tstamp; + + +== rende somma punteggi da una certa data == +select sum(p.pts), u.login from bsk_bin5_points as p, bsk_bin5_games as g, bsk_bin5_matches as m, bsk_users as u where p.ucode = u.code AND p.gcode = g.code AND g.mcode = m.code AND g.tstamp > '2010-10-01 00:00:00' GROUP BY (u.login) ORDER BY sum(p.pts); + +== vicini al conteggio == +select (sum(p.pts) * 100 ) / count(p.pts), u.login from bsk_bin5_points as p, bsk_bin5_games as g, bsk_bin5_matches as m, bsk_users as u where p.ucode = u.code AND p.gcode = g.code AND g.mcode = m.code AND g.tstamp > '2010-12-01 00:00:00' GROUP BY (u.login) ORDER BY (sum(p.pts) * 100 ) / count(p.pts); + + +== conteggio esatto == +select (float4(sum(p.pts)) * 100.0 ) / float4(count(p.pts)), u.login from bsk_bin5_points as p, bsk_bin5_games as g, bsk_bin5_matches as m, bsk_users as u where p.ucode = u.code AND p.gcode = g.code AND g.mcode = m.code AND g.tstamp > '2010-10-01 00:00:00' GROUP BY (u.login) ORDER BY (float4(sum(p.pts)) * 100.0 ) / float4(count(p.pts)); + +== conteggio ancora più esatto == +select (float4(sum(p.pts)) * 100.0 ) / float4(count(p.pts)), count(p.pts), u.login from bsk_bin5_points as p, bsk_bin5_games as g, bsk_bin5_matches as m, bsk_users as u where p.ucode = u.code AND p.gcode = g.code AND g.mcode = m.code AND g.tstamp > '2010-10-01 00:00:00' GROUP BY (u.login) ORDER BY (float4(sum(p.pts)) * 100.0 ) / float4(count(p.pts)) DESC, count(p.pts) DESC diff --git a/sql/sql.d/050-bin5.sql b/sql/sql.d/050-bin5.sql new file mode 100644 index 0000000..938d5d3 --- /dev/null +++ b/sql/sql.d/050-bin5.sql @@ -0,0 +1,25 @@ +-- +-- briskin5 (bin5) related tables +-- + +DROP TABLE #PFX#bin5_matches; +CREATE TABLE #PFX#bin5_matches ( + code SERIAL PRIMARY KEY, + ttok text UNIQUE, -- token associated to the match + tidx integer -- table index + ); + +DROP TABLE #PFX#bin5_games; +CREATE TABLE #PFX#bin5_games ( + code SERIAL PRIMARY KEY, + mcode integer REFERENCES #PFX#bin5_matches (code) ON DELETE cascade ON UPDATE cascade, + tstamp timestamp -- end game time + ); + +DROP TABLE #PFX#bin5_points; +CREATE TABLE #PFX#bin5_points ( + gcode integer REFERENCES #PFX#bin5_games (code) ON DELETE cascade ON UPDATE cascade, + ucode integer REFERENCES #PFX#users (code) ON DELETE cascade ON UPDATE cascade, + pts integer -- points + ); + diff --git a/sql/sql.d/051-bin5-places.sql b/sql/sql.d/051-bin5-places.sql new file mode 100644 index 0000000..ebf3add --- /dev/null +++ b/sql/sql.d/051-bin5-places.sql @@ -0,0 +1,25 @@ +-- +-- try to start with a single table for all placings +-- +-- tri = 0 lo = 0 +-- mon = 2 hi = 1 +-- wee = 4 +-- + +DROP TABLE #PFX#bin5_places_mtime; +CREATE TABLE #PFX#bin5_places_mtime ( + code int, + mtime timestamp +); +INSERT INTO #PFX#bin5_places_mtime (code, mtime) VALUES (0, now()); + +DROP TABLE #PFX#bin5_places; +CREATE TABLE #PFX#bin5_places ( + type integer, + rank integer, + ucode integer REFERENCES #PFX#users (code) ON DELETE cascade ON UPDATE cascade, + login text, + pts integer, + games integer, + score float +); diff --git a/sql/sql.d/005-anagr.sql.noinsert b/sql/sql.d/100-anagr.sql.noinsert similarity index 100% rename from sql/sql.d/005-anagr.sql.noinsert rename to sql/sql.d/100-anagr.sql.noinsert -- 2.17.1