From 330d75c856cac51f3136b1ab1791c8ef8c564ac0 Mon Sep 17 00:00:00 2001 From: "Matteo Nastasi (mop)" Date: Thu, 26 Nov 2015 18:25:37 +0100 Subject: [PATCH] new usersnet schema and viewes created --- sql/sql.d/075-users-network.sql | 29 ++++++++++++++++++++++++++--- sql/sql.d/910-usernet-sql.devel | 28 ++++++++++++++++++++++++---- 2 files changed, 50 insertions(+), 7 deletions(-) diff --git a/sql/sql.d/075-users-network.sql b/sql/sql.d/075-users-network.sql index 9356b37..d6c2f61 100644 --- a/sql/sql.d/075-users-network.sql +++ b/sql/sql.d/075-users-network.sql @@ -5,11 +5,14 @@ DROP TABLE IF EXISTS #PFX#usersnet; CREATE TABLE #PFX#usersnet ( owner integer REFERENCES #PFX#users (code) ON DELETE cascade ON UPDATE cascade, -- network owner + inher integer NULL REFERENCES #PFX#users (code) + ON DELETE cascade ON UPDATE cascade, -- inheriting user target integer REFERENCES #PFX#users (code) ON DELETE cascade ON UPDATE cascade, -- evaluated user friend integer, -- friendship level - skill integer, -- skill level - trust integer, -- auth + -- 1"black", 2"unknown", 3"test", 4"friend", 5"bff" + skill float, -- skill level + trust float, -- auth ctime timestamp DEFAULT now(), -- creation time mtime timestamp DEFAULT to_timestamp(0) -- modification time ); @@ -17,8 +20,28 @@ CREATE TABLE #PFX#usersnet ( DROP INDEX IF EXISTS #PFX#usersnet_owner_idx; DROP INDEX IF EXISTS #PFX#usersnet_target_idx; DROP INDEX IF EXISTS #PFX#usersnet_owner_target_idx; +DROP INDEX IF EXISTS #PFX#usersnet_owner_inher_idx; +DROP INDEX IF EXISTS #PFX#usersnet_owner_target_inher_idx; + CREATE INDEX #PFX#usersnet_owner_idx ON #PFX#usersnet (owner); CREATE INDEX #PFX#usersnet_target_idx ON #PFX#usersnet (target); -CREATE UNIQUE INDEX #PFX#usersnet_owner_target_idx ON #PFX#usersnet (owner,target); +CREATE INDEX #PFX#usersnet_owner_target_idx ON #PFX#usersnet (owner, target); +CREATE INDEX #PFX#usersnet_owner_inher_idx ON #PFX#usersnet (owner,inher); +CREATE UNIQUE INDEX #PFX#usersnet_owner_target_inher_idx ON #PFX#usersnet (owner, target, inher); + +DROP VIEW #PFX#usersnet_wideskill; +CREATE VIEW #PFX#usersnet_wideskill + AS SELECT un.owner, ur.target, SUM(ur.skill * un.trust) / SUM(un.trust) as skill, count(*) as count + FROM bsk_usersnet AS un, bsk_usersnet AS ur + WHERE un.owner = 10101 -- owner is xxx + AND un.target = ur.owner AND un.friend >= 4 -- 'un' is, at least, our friend + GROUP BY un.owner, ur.target; +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 bsk_usersnet AS un, bsk_usersnet AS ur -- 'un' primary records, 'ur' inheriting records + WHERE un.owner = 10101 -- owner is xxx + AND un.target = ur.owner AND un.friend = 5 -- 'un' is, at least, our friend + GROUP BY un.owner, ur.target; diff --git a/sql/sql.d/910-usernet-sql.devel b/sql/sql.d/910-usernet-sql.devel index cbd0954..2138d71 100644 --- a/sql/sql.d/910-usernet-sql.devel +++ b/sql/sql.d/910-usernet-sql.devel @@ -1,5 +1,25 @@ -DELETE FROM #PFX#usersnet WHERE owner = 10101 AND friend = 10102; -INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10102, 3, 2, 3); -DELETE FROM #PFX#usersnet WHERE owner = 10101 AND friend = 10107; -INSERT INTO #PFX#usersnet (owner, target, friend, skill, trust) VALUES (10101, 10107, 3, 3, 4); +-- primary owner 10101 +DELETE FROM #PFX#usersnet WHERE owner = 10101 AND inher = 10101 AND target = 10102; +INSERT INTO #PFX#usersnet (owner, inher, target, friend, skill, trust) VALUES (10101, 10101, 10102, 4, 2, 5); +DELETE FROM #PFX#usersnet WHERE owner = 10101 AND inher = 10101 AND target = 10103; +INSERT INTO #PFX#usersnet (owner, inher, target, friend, skill, trust) VALUES (10101, 10101, 10103, 5, 2, 3); +-- primary owner: discarded target +DELETE FROM #PFX#usersnet WHERE owner = 10101 AND inher = 10101 AND target = 10104; +INSERT INTO #PFX#usersnet (owner, inher, target, friend, skill, trust) VALUES (10101, 10101, 10104, 1, 2, 5); + +-- secondary owners for 10101 +DELETE FROM #PFX#usersnet WHERE owner = 10102 AND inher = 10102 AND target = 10105; +INSERT INTO #PFX#usersnet (owner, inher, target, friend, skill, trust) VALUES (10102, 10102, 10105, 4, 2, 3); +DELETE FROM #PFX#usersnet WHERE owner = 10103 AND inher = 10103 AND target = 10105; +INSERT INTO #PFX#usersnet (owner, inher, target, friend, skill, trust) VALUES (10103, 10103, 10105, 4, 3, 4); +DELETE FROM #PFX#usersnet WHERE owner = 10104 AND inher = 10104 AND target = 10105; +INSERT INTO #PFX#usersnet (owner, inher, target, friend, skill, trust) VALUES (10104, 10104, 10105, 4, 5, 4); + +DELETE FROM #PFX#usersnet WHERE owner = 10101 AND inher = 10101 AND target = 10107; +INSERT INTO #PFX#usersnet (owner, inher, target, friend, skill, trust) VALUES (10101, 10101, 10107, 3, 3, 4); +DELETE FROM #PFX#usersnet WHERE owner = 10101 AND inher is NULL AND target = 10107; +INSERT INTO #PFX#usersnet (owner, inher, target, friend, skill, trust) VALUES (10101, NULL, 10107, 3, 3, 4); + +SELECT * FROM bsk_usersnet_wideskill; +SELECT * FROM bsk_usersnet_narrowskill; -- 2.17.1