From dc35e3289fd4266bb12ed7c6ec5beab02c1a48de Mon Sep 17 00:00:00 2001 From: "Matteo Nastasi (mop)" Date: Sun, 6 Dec 2015 15:32:42 +0100 Subject: [PATCH] add usersnet_party view to extract users allowed to join the table in 'party' mode --- TODO.txt | 8 +++-- sql/sql.d/075-users-network.sql | 23 ++++++++++----- sql/sql.d/900-anagr_devel.sql | 14 ++++++++- sql/sql.d/910-usernet_devel.sql | 52 +++++++++++++++++++++++++++++++-- 4 files changed, 84 insertions(+), 13 deletions(-) diff --git a/TODO.txt b/TODO.txt index 14f91a5..6f5fe62 100644 --- a/TODO.txt +++ b/TODO.txt @@ -3,24 +3,28 @@ BUGS | ------+ - - Global vars checker - Not triple chars between letters + + - Global vars checker - usermgmt: add user delete DONE - 10002 message when already clicked DONE - trim spaces in apprentice form DONE - Remove books from chat DONE - usermgmt: add email + FEATURES | ----------+ - * inherited info + WIP * inherited info * Users Network construction DONE - sql DONE - dbase API DONE - add apprentices tables - remove entry when set unknown - enhance info interface + . SQL to show current evaluation . disable parts of interface + - minisplash at end of the match - differentiate table authorization * add new kind of isolation for apprentice diff --git a/sql/sql.d/075-users-network.sql b/sql/sql.d/075-users-network.sql index 8d4fca4..cf108c0 100644 --- a/sql/sql.d/075-users-network.sql +++ b/sql/sql.d/075-users-network.sql @@ -32,12 +32,19 @@ CREATE VIEW #PFX#usersnet_wideskill DROP VIEW #PFX#usersnet_narrowskill; CREATE VIEW #PFX#usersnet_narrowskill - AS SELECT un.owner, ur.target, SUM(ur.skill * un.trust) / SUM(un.trust) as skill, count(*) as count - FROM #PFX#usersnet AS un, #PFX#usersnet AS ur -- 'un' primary records, 'ur' inheriting records - WHERE un.target = ur.owner AND un.friend = 5 -- 'un' is, at least, our friend - GROUP BY un.owner, ur.target; + AS SELECT un.owner AS owner, ur.target AS target, SUM(ur.skill * un.trust) / SUM(un.trust) AS skill, + COUNT(*) AS count, + SUM(CASE WHEN ur.friend = 1 THEN 1 ELSE 0 END) AS black -- count number of friend + -- blacklisting the target + FROM #PFX#usersnet AS un, #PFX#usersnet as ur + WHERE un.target = ur.owner AND un.friend = 5 -- ur owner must be bbf of un.owner + GROUP BY un.owner, ur.target HAVING SUM(CASE WHEN ur.friend = 1 THEN 1 ELSE 0 END) = 0; --- DROP VIEW #PFX#usersnet_allfriends; --- CREATE VIEW #PFX#usersnet_allfriends --- AS SELECT un.owner, ur.target FROM #PFX#usersnet AS un, #PFX#usersnet AS ur --- WHERE +DROP VIEW #PFX#usersnet_party; +CREATE VIEW #PFX#usersnet_party + AS (SELECT ns.* FROM #PFX#usersnet_narrowskill AS ns + -- all except targets managed directly by the owner + WHERE target NOT IN (SELECT target FROM #PFX#usersnet WHERE owner = ns.owner) + UNION ALL + (SELECT owner, target, skill, 1 AS count, 0 AS black FROM #PFX#usersnet + WHERE friend > 2)) ORDER BY target; diff --git a/sql/sql.d/900-anagr_devel.sql b/sql/sql.d/900-anagr_devel.sql index 1d42603..d1a5def 100644 --- a/sql/sql.d/900-anagr_devel.sql +++ b/sql/sql.d/900-anagr_devel.sql @@ -30,4 +30,16 @@ DELETE FROM #PFX#users WHERE code = 10107; INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10107, E'"/\\''|', md5('sev'), 'sev@pluto.com', CAST (X'00210000' as integer), 10103); DELETE FROM #PFX#users WHERE code = 10108; INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10108, E'appr', md5('appr'), 'appr@pluto.com', CAST (X'00080000' as integer), 10103); -ALTER SEQUENCE #PFX#users_code_seq RESTART WITH 10109; + +DELETE FROM #PFX#users WHERE code = 10109; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10109, 'bosi', md5('bosi'), 'bosi@pluto.com', CAST (X'00010000' as integer), 10103); +DELETE FROM #PFX#users WHERE code = 10110; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10110, 'bono', md5('bono'), 'bono@pluto.com', CAST (X'00010000' as integer), 10103); +DELETE FROM #PFX#users WHERE code = 10111; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10111, 'nosi', md5('nosi'), 'nosi@pluto.com', CAST (X'00010000' as integer), 10103); +DELETE FROM #PFX#users WHERE code = 10112; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10112, 'sino', md5('sino'), 'sino@pluto.com', CAST (X'00010000' as integer), 10103); +DELETE FROM #PFX#users WHERE code = 10113; +INSERT INTO #PFX#users (code, login, pass, email, type, guar_code) VALUES (10113, 'doub', md5('boub'), 'doub@pluto.com', CAST (X'00010000' as integer), 10103); + +ALTER SEQUENCE #PFX#users_code_seq RESTART WITH 10114; diff --git a/sql/sql.d/910-usernet_devel.sql b/sql/sql.d/910-usernet_devel.sql index 3be8d5c..d082220 100644 --- a/sql/sql.d/910-usernet_devel.sql +++ b/sql/sql.d/910-usernet_devel.sql @@ -3,6 +3,10 @@ DELETE FROM #PFX#usersnet WHERE owner = 10101 AND target = 10102; INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10102, 4, 2, 5); DELETE FROM #PFX#usersnet WHERE owner = 10101 AND target = 10103; INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10103, 5, 2, 3); +-- doub +DELETE FROM #PFX#usersnet WHERE owner = 10101 AND target = 10113; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10113, 5, 2, 5); + -- primary owner: discarded target DELETE FROM #PFX#usersnet WHERE owner = 10101 AND target = 10104; INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10104, 1, 2, 5); @@ -14,10 +18,54 @@ DELETE FROM #PFX#usersnet WHERE owner = 10103 AND target = 10105; INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10103, 10105, 4, 3, 4); DELETE FROM #PFX#usersnet WHERE owner = 10104 AND target = 10105; INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10104, 10105, 4, 5, 4); +DELETE FROM #PFX#usersnet WHERE owner = 10113 AND target = 10105; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10113, 10105, 4, 2, 5); DELETE FROM #PFX#usersnet WHERE owner = 10101 AND target = 10107; INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10107, 3, 3, 4); -SELECT * FROM bsk_usersnet_wideskill; -SELECT * FROM bsk_usersnet_narrowskill; +-- check sit permission +-- bosi +DELETE FROM #PFX#usersnet WHERE owner = 10102 AND target = 10109; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10102, 10109, 4, 2, 3); +DELETE FROM #PFX#usersnet WHERE owner = 10103 AND target = 10109; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10103, 10109, 4, 2, 3); + +-- bono +DELETE FROM #PFX#usersnet WHERE owner = 10102 AND target = 10110; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10102, 10110, 4, 2, 3); +DELETE FROM #PFX#usersnet WHERE owner = 10103 AND target = 10110; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10103, 10110, 1, 2, 3); + +-- nosi +DELETE FROM #PFX#usersnet WHERE owner = 10101 AND target = 10111; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10111, 1, 2, 3); +DELETE FROM #PFX#usersnet WHERE owner = 10102 AND target = 10111; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10102, 10111, 4, 2, 3); +DELETE FROM #PFX#usersnet WHERE owner = 10103 AND target = 10111; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10103, 10111, 4, 2, 3); + +-- sino +DELETE FROM #PFX#usersnet WHERE owner = 10101 AND target = 10112; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10112, 4, 2, 3); +DELETE FROM #PFX#usersnet WHERE owner = 10102 AND target = 10112; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10102, 10112, 4, 2, 3); +DELETE FROM #PFX#usersnet WHERE owner = 10103 AND target = 10112; +INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10103, 10112, 1, 2, 3); + +SELECT * FROM #PFX#usersnet WHERE owner = 10101 OR owner IN (10102, 10103, 10113) ORDER BY target; + +SELECT * FROM #PFX#usersnet_wideskill ORDER BY owner, target; + +SELECT * FROM #PFX#usersnet_narrowskill ORDER BY owner, target; + +SELECT owner, target, skill, 1 AS count, us.login as login FROM #PFX#usersnet, #PFX#users as us WHERE owner = 10101 AND us.code = target AND friend > 2 ORDER BY target; + +SELECT ns.*, us.login AS login FROM #PFX#usersnet_narrowskill as ns, #PFX#users AS us WHERE owner = 10101 + AND ns.target NOT IN (SELECT target FROM #PFX#usersnet WHERE owner = 10101) +AND us.code = ns.target UNION SELECT owner, target, skill, 1 AS count, 0 AS black, us.login as login FROM #PFX#usersnet, #PFX#users as us WHERE owner = 10101 AND us.code = target AND friend > 2 ORDER BY target; + +SELECT un.owner, ur.target, SUM(ur.skill * un.trust) AS num, SUM(un.trust) AS den, SUM(ur.skill * un.trust) / SUM(un.trust) AS skill, COUNT(*) AS count FROM #PFX#usersnet AS un, #PFX#usersnet as ur WHERE un.target = ur.owner AND un.friend = 5 AND ur.target NOT IN (SELECT target FROM #PFX#usersnet WHERE owner = un.owner) GROUP BY un.owner, ur.target; + +SELECT us.login, pa.* FROM #PFX#usersnet_party pa, #PFX#users as us WHERE pa.target = us.code AND pa.owner = 10101; -- 2.17.1