Emmanuel BENOîT
e50775ec76
* The main loader script has been updated to generate the list of files it needs to load automatically. As a consequence, files that contained manually-maintained lists of scripts have been removed, and definition directories have been renamed accordingly. * PostgreSQL extension loading and configuration has been moved to a separate script to be loaded automatically in the main transaction. * Data and function definition scripts that had the -data or -functions suffix have been renamed (the suffix is unnecessary). * Unit tests have been reorganised to follow the definition's structure. * Documentation has been improved
64 lines
1.8 KiB
SQL
64 lines
1.8 KiB
SQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- Views used to generate the administration overview
|
|
--
|
|
-- Copyright(C) 2004-2010, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
|
|
CREATE VIEW admin.ov_new_messages
|
|
AS SELECT admin_id , count(*) AS new_messages
|
|
FROM msgs.admin_delivery
|
|
WHERE status = 'UNREAD'
|
|
GROUP BY admin_id;
|
|
|
|
|
|
CREATE VIEW admin.ov_pending_names
|
|
AS SELECT count(*) AS pending_names
|
|
FROM naming.changed_map_names
|
|
LEFT OUTER JOIN naming.validated_map_names USING (name_id)
|
|
WHERE validated_at IS NULL;
|
|
|
|
|
|
CREATE VIEW admin.ov_pending_bans
|
|
AS SELECT count(*) AS pending_bans
|
|
FROM admin.active_ban_requests
|
|
WHERE NOT validated;
|
|
|
|
|
|
CREATE VIEW admin.ov_pending_bugs
|
|
AS SELECT count(*) AS pending_bugs
|
|
FROM bugs.br_main_view
|
|
WHERE status = 'PENDING';
|
|
|
|
|
|
CREATE VIEW admin.ov_open_bugs
|
|
AS SELECT count(*) AS open_bugs
|
|
FROM bugs.br_main_view
|
|
WHERE status = 'OPEN';
|
|
|
|
|
|
CREATE VIEW admin.ov_updated_bugs
|
|
AS SELECT administrator_id , count(*) AS updated_bugs
|
|
FROM bugs.br_admin_view
|
|
WHERE updated
|
|
GROUP BY administrator_id;
|
|
|
|
|
|
CREATE VIEW admin.overview
|
|
AS SELECT a.id AS admin_id ,
|
|
( CASE WHEN nm IS NULL THEN 0 ELSE nm.new_messages END )::BIGINT AS new_messages ,
|
|
pn.pending_names , pb.pending_bans , pbg.pending_bugs , ob.open_bugs ,
|
|
( CASE WHEN ub IS NULL THEN 0 ELSE ub.updated_bugs END )::BIGINT AS updated_bugs
|
|
FROM admin.administrators a
|
|
LEFT OUTER JOIN admin.ov_new_messages nm ON nm.admin_id = a.id
|
|
INNER JOIN admin.ov_pending_names pn ON TRUE
|
|
INNER JOIN admin.ov_pending_bans pb ON TRUE
|
|
INNER JOIN admin.ov_pending_bugs pbg ON TRUE
|
|
INNER JOIN admin.ov_open_bugs ob ON TRUE
|
|
LEFT OUTER JOIN admin.ov_updated_bugs ub ON ub.administrator_id = a.id
|
|
WHERE a.privileges <> 0;
|
|
|
|
GRANT SELECT ON admin.overview TO :dbuser;
|