1209 lines
43 KiB
PL/PgSQL
1209 lines
43 KiB
PL/PgSQL
-- 檔案名稱: htc.sql
|
|
-- 程式說明: 歷史：理論與文化資料庫定義檔
|
|
-- 程式作者: 依瑪貓 imacat <imacat@mail.imacat.idv.tw>
|
|
-- 初稿日期: 2004-10-16
|
|
-- 版權字樣: 版權所有 (c) 2004-2012 依瑪貓
|
|
|
|
SET NAMES 'big5';
|
|
|
|
START TRANSACTION;
|
|
|
|
|
|
--
|
|
-- Table structure for table "mtime"
|
|
--
|
|
|
|
CREATE TABLE mtime (
|
|
tabname varchar(16) NOT NULL PRIMARY KEY,
|
|
mtime timestamp NOT NULL DEFAULT now()
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON mtime TO nobody;
|
|
|
|
|
|
--
|
|
-- Function definition for function "eschtml"
|
|
--
|
|
-- integer eschtml(source text)
|
|
CREATE FUNCTION eschtml(source text) RETURNS text AS '
|
|
DECLARE
|
|
result text;
|
|
BEGIN
|
|
result := source;
|
|
result := replace(result, ''&'', ''&'');
|
|
result := replace(result, ''<'', ''<'');
|
|
result := replace(result, ''>'', ''>'');
|
|
result := replace(result, ''"'', ''"'');
|
|
return result;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION eschtml(source text) TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "country"
|
|
--
|
|
|
|
CREATE TABLE country (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
id char(2) NOT NULL UNIQUE CHECK (position(' ' in id) = 0),
|
|
name_en varchar(64) NOT NULL CHECK (name_en != ''),
|
|
name_zhtw varchar(32) CHECK (name_zhtw IS NULL OR name_zhtw != ''),
|
|
special boolean NOT NULL DEFAULT FALSE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON country TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "users"
|
|
--
|
|
|
|
CREATE TABLE users (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
id varchar(32) NOT NULL UNIQUE CHECK (char_length(id) >= 3),
|
|
passwd char(32) NOT NULL,
|
|
name varchar(32) NOT NULL CHECK (name != ''),
|
|
disabled boolean NOT NULL DEFAULT FALSE,
|
|
deleted boolean NOT NULL DEFAULT FALSE,
|
|
lang varchar(5) CHECK (lang IS NULL OR lang != ''),
|
|
visits smallint NOT NULL DEFAULT 0 CHECK (visits >= 0),
|
|
visited timestamp,
|
|
ip inet,
|
|
host varchar(128),
|
|
ct char(2) REFERENCES country (id) ON UPDATE CASCADE DEFERRABLE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO nobody;
|
|
|
|
CREATE VIEW users_list AS
|
|
SELECT
|
|
users.sn AS sn,
|
|
users.id AS id,
|
|
users.name AS name,
|
|
CASE WHEN users.disabled THEN '停用'
|
|
ELSE ''
|
|
END AS disabled,
|
|
CASE WHEN users.deleted THEN '已刪'
|
|
ELSE ''
|
|
END AS deleted,
|
|
CASE WHEN users.lang IS NULL THEN '（無）'
|
|
ELSE CASE users.lang
|
|
WHEN 'en' THEN '英文'
|
|
WHEN 'zh-tw' THEN '繁體中文'
|
|
WHEN 'zh-cn' THEN '簡體中文'
|
|
WHEN 'ja' THEN '日文'
|
|
WHEN 'de' THEN '德文'
|
|
WHEN 'es' THEN '西班牙文'
|
|
ELSE users.lang
|
|
END
|
|
END AS lang,
|
|
users.visits AS visits,
|
|
CASE WHEN users.visited IS NULL THEN '（無）'
|
|
ELSE to_char(users.visited, 'YYYY-MM-DD HH:MI:SS')
|
|
END AS visited,
|
|
CASE WHEN users.ip IS NULL THEN '（無）'
|
|
ELSE host(users.ip)
|
|
END AS ip,
|
|
CASE WHEN users.host IS NULL THEN '（無）'
|
|
ELSE users.host
|
|
END AS host,
|
|
CASE WHEN users.ct IS NULL THEN '（無）'
|
|
ELSE ct.name_zhtw
|
|
END AS ct,
|
|
to_char(users.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(users.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM users
|
|
LEFT JOIN country AS ct ON users.ct = ct.id
|
|
LEFT JOIN users AS createdby ON users.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON users.updatedby = updatedby.sn
|
|
ORDER BY id;
|
|
GRANT SELECT ON users_list TO nobody;
|
|
|
|
-- INSERT INTO users (sn, id, passwd, name, disabled, deleted, lang, visits, visited, ip, host, created, createdby, updated, updatedby) VALUES (923153018, 'imacat', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', '依瑪貓', FALSE, FALSE, NULL, 0, NULL, NULL, NULL, now(), 923153018, now(), 923153018);
|
|
-- INSERT INTO users (sn, id, passwd, name, disabled, deleted, lang, visits, visited, ip, host, created, createdby, updated, updatedby) VALUES (460376330, 'mandy', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', '小招', FALSE, FALSE, NULL, 0, NULL, NULL, NULL, now(), 923153018, now(), 923153018);
|
|
-- INSERT INTO users (sn, id, passwd, name, disabled, deleted, lang, visits, visited, ip, host, created, createdby, updated, updatedby) VALUES (723676436, 'guestbook', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', '留言本', FALSE, FALSE, NULL, 0, NULL, NULL, NULL, now(), 923153018, now(), 923153018);
|
|
|
|
--
|
|
-- Fixing the country table
|
|
--
|
|
|
|
ALTER TABLE country ADD FOREIGN KEY (createdby) REFERENCES users ON UPDATE CASCADE DEFERRABLE;
|
|
ALTER TABLE country ADD FOREIGN KEY (updatedby) REFERENCES users ON UPDATE CASCADE DEFERRABLE;
|
|
CREATE VIEW country_list AS
|
|
SELECT
|
|
country.sn AS sn,
|
|
country.id AS id,
|
|
COALESCE(country.name_zhtw, country.name_en) AS title,
|
|
CASE WHEN country.special THEN '特殊'
|
|
ELSE ''
|
|
END AS special,
|
|
to_char(country.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(country.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM country
|
|
LEFT JOIN users AS createdby ON country.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON country.updatedby = updatedby.sn
|
|
ORDER BY id;
|
|
GRANT SELECT ON country_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "groups"
|
|
--
|
|
|
|
CREATE TABLE groups (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
id varchar(16) NOT NULL UNIQUE CHECK (char_length(id) >= 3),
|
|
dsc varchar(64) NOT NULL CHECK (dsc != ''),
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON groups TO nobody;
|
|
|
|
CREATE VIEW groups_list AS
|
|
SELECT
|
|
groups.sn AS sn,
|
|
groups.id AS id,
|
|
groups.dsc AS dsc,
|
|
to_char(groups.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(groups.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM groups
|
|
LEFT JOIN users AS createdby ON groups.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON groups.updatedby = updatedby.sn
|
|
ORDER BY id;
|
|
GRANT SELECT ON groups_list TO nobody;
|
|
|
|
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (553229108, 'root', '總管理員', now(), 923153018, now(), 923153018);
|
|
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (802339805, 'guests', '暱名訪客', now(), 923153018, now(), 923153018);
|
|
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (958210993, 'users', '已登入使用者', now(), 923153018, now(), 923153018);
|
|
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (329685674, 'admin', '所有網站管理員', now(), 923153018, now(), 923153018);
|
|
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (157696540, 'acctman', '帳號管理員', now(), 923153018, now(), 923153018);
|
|
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (390105230, 'editor', '網站編輯', now(), 923153018, now(), 923153018);
|
|
|
|
|
|
--
|
|
-- Table structure for table "usermem"
|
|
--
|
|
|
|
CREATE TABLE usermem (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
grp int NOT NULL REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
|
|
member int NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
UNIQUE (grp, member)
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON usermem TO nobody;
|
|
|
|
CREATE VIEW usermem_list AS
|
|
SELECT
|
|
usermem.sn AS sn,
|
|
groups.id || ' (' || groups.dsc || ')' AS grp,
|
|
members.id || ' (' || members.name || ')' AS member,
|
|
to_char(usermem.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(usermem.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM usermem
|
|
LEFT JOIN groups ON usermem.grp = groups.sn
|
|
LEFT JOIN users AS members ON usermem.member = members.sn
|
|
LEFT JOIN users AS createdby ON usermem.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON usermem.updatedby = updatedby.sn
|
|
ORDER BY grp, member;
|
|
GRANT SELECT ON usermem_list TO nobody;
|
|
|
|
-- INSERT INTO usermem (sn, grp, member, created, createdby, updated, updatedby) VALUES (593684712, 553229108, 923153018, now(), 923153018, now(), 923153018);
|
|
|
|
|
|
--
|
|
-- Table structure for table "groupmem"
|
|
--
|
|
|
|
CREATE TABLE groupmem (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
grp int NOT NULL REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
|
|
member int NOT NULL REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE CHECK (member != grp),
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
UNIQUE (grp, member)
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON groupmem TO nobody;
|
|
|
|
CREATE VIEW groupmem_list AS
|
|
SELECT
|
|
groupmem.sn AS sn,
|
|
groups.id || ' (' || groups.dsc || ')' AS grp,
|
|
members.id || ' (' || members.dsc || ')' AS member,
|
|
to_char(groupmem.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(groupmem.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM groupmem
|
|
LEFT JOIN groups ON groupmem.grp = groups.sn
|
|
LEFT JOIN groups AS members ON groupmem.member = members.sn
|
|
LEFT JOIN users AS createdby ON groupmem.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON groupmem.updatedby = updatedby.sn
|
|
ORDER BY grp, member;
|
|
GRANT SELECT ON groupmem_list TO nobody;
|
|
|
|
-- INSERT INTO groupmem (sn, grp, member, created, createdby, updated, updatedby) VALUES (569742102, 329685674, 157696540, now(), 923153018, now(), 923153018);
|
|
-- INSERT INTO groupmem (sn, grp, member, created, createdby, updated, updatedby) VALUES (859385977, 329685674, 390105230, now(), 923153018, now(), 923153018);
|
|
|
|
|
|
--
|
|
-- Table structure for table "scptpriv"
|
|
--
|
|
|
|
CREATE TABLE scptpriv (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
script varchar(64) NOT NULL CHECK (script != ''),
|
|
grp int NOT NULL REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
UNIQUE (script, grp)
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON scptpriv TO nobody;
|
|
|
|
CREATE VIEW scptpriv_list AS
|
|
SELECT
|
|
scptpriv.sn AS sn,
|
|
scptpriv.script AS script,
|
|
groups.dsc AS grp,
|
|
to_char(scptpriv.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(scptpriv.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM scptpriv
|
|
LEFT JOIN groups ON scptpriv.grp = groups.sn
|
|
LEFT JOIN users AS createdby ON scptpriv.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON scptpriv.updatedby = updatedby.sn
|
|
ORDER BY script, grp;
|
|
GRANT SELECT ON scptpriv_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "userpref"
|
|
--
|
|
|
|
CREATE TABLE userpref (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
usr int REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
|
|
domain varchar(64) CHECK (domain IS NULL OR domain != ''),
|
|
name varchar(16) NOT NULL CHECK (name != ''),
|
|
value varchar(255) NOT NULL,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
UNIQUE (usr, domain, name)
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON userpref TO nobody;
|
|
|
|
CREATE VIEW userpref_list AS
|
|
SELECT
|
|
userpref.sn AS sn,
|
|
CASE WHEN userpref.usr IS NOT NULL THEN users.name
|
|
ELSE '所有人'
|
|
END AS usr,
|
|
CASE WHEN userpref.domain IS NOT NULL THEN userpref.domain
|
|
ELSE '所有地方'
|
|
END AS domain,
|
|
userpref.name AS name,
|
|
userpref.value AS value,
|
|
to_char(userpref.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(userpref.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM userpref LEFT JOIN users ON userpref.usr = users.sn
|
|
LEFT JOIN users AS createdby ON userpref.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON userpref.updatedby = updatedby.sn
|
|
ORDER BY domain, usr, name;
|
|
GRANT SELECT ON userpref_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Function definitions for table "guestbook"
|
|
--
|
|
|
|
-- integer guestbook_oldlen(date timestamp, ip inet, hostname text, name text, identity text, location text, email text, url text, message text, updated timestamp, updatedby_arg integer)
|
|
CREATE FUNCTION guestbook_oldlen(date timestamp, ip inet, hostname text, name text, identity text, location text, email text, url text, message text, updated timestamp, updatedby_arg integer) RETURNS integer AS '
|
|
DECLARE
|
|
row record;
|
|
len integer;
|
|
BEGIN
|
|
-- <record></record>: 19, sn: 30 + 1, date: 54 + 1
|
|
len := 105;
|
|
len := len + octet_length(host(ip)) + 22;
|
|
IF hostname IS NOT NULL THEN
|
|
len := len + octet_length(hostname) + 24;
|
|
END IF;
|
|
IF name IS NOT NULL THEN
|
|
len := len + octet_length(eschtml(name)) + 24;
|
|
END IF;
|
|
IF identity IS NOT NULL THEN
|
|
len := len + octet_length(eschtml(identity)) + 28;
|
|
END IF;
|
|
IF location IS NOT NULL THEN
|
|
len := len + octet_length(eschtml(location)) + 28;
|
|
END IF;
|
|
IF email IS NOT NULL THEN
|
|
len := len + octet_length(eschtml(email)) + 25;
|
|
END IF;
|
|
IF url IS NOT NULL THEN
|
|
len := len + octet_length(eschtml(url)) + 23;
|
|
END IF;
|
|
IF message IS NOT NULL THEN
|
|
len := len + octet_length(eschtml(message)) + 27;
|
|
END IF;
|
|
IF updated != date THEN
|
|
len := len + 58;
|
|
IF updatedby_arg = 923153018 THEN
|
|
len := len + 35;
|
|
ELSIF updatedby_arg = 460376330 THEN
|
|
len := len + 34;
|
|
ELSE
|
|
SELECT INTO row name FROM users WHERE sn=updatedby_arg;
|
|
len := len + octet_length(row.name) + 29;
|
|
END IF;
|
|
END IF;
|
|
RETURN len;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION guestbook_oldlen(date timestamp, ip inet, hostname text, name text, identity text, location text, email text, url text, message text, updated timestamp, updatedby_arg integer) TO nobody;
|
|
-- integer guestbook_oldlen(sn_arg integer)
|
|
CREATE FUNCTION guestbook_oldlen(sn_arg integer) RETURNS integer AS '
|
|
DECLARE
|
|
row record;
|
|
BEGIN
|
|
IF sn_arg IS NULL THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
SELECT INTO row * FROM guestbook WHERE sn=sn_arg;
|
|
IF NOT FOUND THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
RETURN guestbook_oldlen(row.created, row.ip, row.host, row.name, row.identity, row.location, row.email, row.url, row.message, row.updated, row.updatedby);
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION guestbook_oldlen(sn_arg integer) TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "guestbook"
|
|
--
|
|
|
|
CREATE TABLE guestbook (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
name varchar(32) CHECK (name IS NULL OR name != ''),
|
|
identity varchar(64) CHECK (identity IS NULL OR identity != ''),
|
|
location varchar(64) CHECK (location IS NULL OR location != ''),
|
|
email varchar(64) CHECK (email IS NULL OR email != ''),
|
|
url varchar(128) CHECK (url IS NULL OR (url != '' AND url != 'http://')),
|
|
message text NOT NULL CHECK (message != ''),
|
|
hid boolean NOT NULL DEFAULT FALSE,
|
|
ip inet NOT NULL,
|
|
host varchar(255) CHECK (host IS NULL OR host != ''),
|
|
ct char(2) NOT NULL REFERENCES country (id) ON UPDATE CASCADE DEFERRABLE,
|
|
pageno int NOT NULL,
|
|
oldpageno int,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON guestbook TO nobody;
|
|
|
|
CREATE VIEW guestbook_list AS
|
|
SELECT
|
|
'/cgi-bin/guestbook.cgi?pageno=' || guestbook.pageno
|
|
|| '#msg' || guestbook.sn
|
|
AS _viewurl,
|
|
guestbook.sn AS sn,
|
|
to_char(guestbook.created, 'YYYY-MM-DD') AS date,
|
|
COALESCE(guestbook.name, '（未設定）') AS name,
|
|
COALESCE(guestbook.identity, '（未設定）') AS identity,
|
|
COALESCE(guestbook.location, '（未設定）') AS location,
|
|
COALESCE(guestbook.email, '（未設定）') AS email,
|
|
COALESCE(guestbook.url, '（未設定）') AS url,
|
|
guestbook.message AS message,
|
|
CASE WHEN guestbook.hid THEN '隱藏'
|
|
ELSE '秀出'
|
|
END AS hid,
|
|
host(guestbook.ip) AS ip,
|
|
COALESCE(guestbook.host, '（不可考）') AS host,
|
|
COALESCE(country.name_zhtw, country.name_en) AS ct,
|
|
guestbook.pageno AS pageno,
|
|
guestbook.oldpageno AS oldpageno,
|
|
to_char(guestbook.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(guestbook.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM guestbook
|
|
LEFT JOIN country ON guestbook.ct = country.id
|
|
LEFT JOIN users AS createdby ON guestbook.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON guestbook.updatedby = updatedby.sn
|
|
ORDER BY guestbook.created DESC;
|
|
GRANT SELECT ON guestbook_list TO nobody;
|
|
|
|
CREATE VIEW guestbook_public AS
|
|
SELECT
|
|
sn AS sn,
|
|
extract(epoch FROM created) AS date,
|
|
name AS name,
|
|
identity AS identity,
|
|
location AS location,
|
|
email AS email,
|
|
url AS url,
|
|
message AS message,
|
|
pageno AS pageno,
|
|
oldpageno AS oldpageno
|
|
FROM guestbook
|
|
WHERE NOT hid
|
|
ORDER BY guestbook.created DESC;
|
|
GRANT SELECT ON guestbook_public TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "pages"
|
|
--
|
|
|
|
CREATE TABLE pages (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
path varchar(64) NOT NULL UNIQUE CHECK (path != ''),
|
|
ord smallint NOT NULL DEFAULT 5000 CHECK (ord >= 0 AND ord < 10000),
|
|
title varchar(128) NOT NULL CHECK (title != ''),
|
|
body text NOT NULL CHECK (body != ''),
|
|
kw varchar(128) NOT NULL CHECK (kw != ''),
|
|
html boolean NOT NULL DEFAULT FALSE,
|
|
hid boolean NOT NULL DEFAULT FALSE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON pages TO nobody;
|
|
|
|
CREATE VIEW pages_list AS
|
|
SELECT
|
|
pages.path AS _viewurl,
|
|
pages.sn AS sn,
|
|
pages.path AS path,
|
|
pages.ord AS ord,
|
|
pages.title AS title,
|
|
pages.body AS body,
|
|
pages.kw AS kw,
|
|
CASE WHEN pages.html THEN 'HTML'
|
|
ELSE '純文字'
|
|
END AS html,
|
|
CASE WHEN pages.hid THEN '隱藏'
|
|
ELSE '秀出'
|
|
END AS hid,
|
|
to_char(pages.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(pages.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM pages
|
|
LEFT JOIN users AS createdby ON pages.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON pages.updatedby = updatedby.sn
|
|
ORDER BY path;
|
|
GRANT SELECT ON pages_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Function definitions for table "links"
|
|
--
|
|
|
|
-- boolean linkcat_id_unique(id_arg text, sn_arg integer, parent_arg integer);
|
|
CREATE FUNCTION linkcat_id_unique(id_arg text, sn_arg integer, parent_arg integer) RETURNS boolean AS '
|
|
BEGIN
|
|
IF parent_arg IS NULL THEN
|
|
-- TODO: 2019/3/9 Removed schema "public"? Added or it will not work on restore. Not knowing why.
|
|
PERFORM * FROM public.linkcat
|
|
WHERE id=id_arg AND sn!=sn_arg AND parent IS NULL;
|
|
RETURN NOT FOUND;
|
|
ELSE
|
|
-- TODO: 2019/3/9 Removed schema "public"? Added or it will not work on restore. Not knowing why.
|
|
PERFORM * FROM public.linkcat
|
|
WHERE id=id_arg AND sn!=sn_arg AND parent=parent_arg;
|
|
RETURN NOT FOUND;
|
|
END IF;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_id_unique(id_arg text, sn_arg integer, parent_arg integer) TO nobody;
|
|
|
|
-- numeric linkcat_fullord(parent_arg integer, ord_arg integer);
|
|
CREATE FUNCTION linkcat_fullord(parent_arg integer, ord_arg integer) RETURNS numeric AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
ord_loop numeric;
|
|
BEGIN
|
|
sn_loop := parent_arg;
|
|
ord_loop := ord_arg;
|
|
WHILE sn_loop IS NOT NULL LOOP
|
|
SELECT INTO row parent, ord FROM linkcat WHERE sn=sn_loop;
|
|
IF NOT FOUND THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
sn_loop := row.parent;
|
|
ord_loop := row.ord + ord_loop / 100;
|
|
END LOOP;
|
|
RETURN ord_loop;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_fullord(parent_arg integer, ord_arg integer) TO nobody;
|
|
-- numeric linkcat_fullord(sn_arg integer);
|
|
CREATE FUNCTION linkcat_fullord(sn_arg integer) RETURNS numeric AS '
|
|
BEGIN
|
|
RETURN linkcat_fullord(sn_arg, 0);
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_fullord(sn_arg integer) TO nobody;
|
|
|
|
-- boolean linkcat_isshown(sn_arg integer, hid_arg boolean, parent_arg integer);
|
|
CREATE FUNCTION linkcat_isshown(sn_arg integer, hid_arg boolean, parent_arg integer) RETURNS boolean AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
BEGIN
|
|
IF hid_arg THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
-- Check if we are hidden by our ancestors
|
|
sn_loop := parent_arg;
|
|
WHILE sn_loop IS NOT NULL LOOP
|
|
SELECT INTO row parent, hid FROM linkcat WHERE sn=sn_loop;
|
|
IF row.hid THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
sn_loop = row.parent;
|
|
END LOOP;
|
|
-- Check if we have childs
|
|
PERFORM links.sn FROM links
|
|
INNER JOIN linkcatz ON linkcatz.link=links.sn
|
|
WHERE NOT links.hid AND linkcatz.cat=sn_arg;
|
|
IF FOUND THEN
|
|
RETURN TRUE;
|
|
END IF;
|
|
-- Check if we have shown child categories
|
|
PERFORM sn FROM linkcat WHERE parent=sn_arg AND linkcat_isshown(sn, hid, parent);
|
|
IF FOUND THEN
|
|
RETURN TRUE;
|
|
END IF;
|
|
RETURN FALSE;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_isshown(sn_arg integer, hid_arg boolean, parent_arg integer) TO nobody;
|
|
-- boolean linkcat_isshown(sn_arg integer);
|
|
CREATE FUNCTION linkcat_isshown(sn_arg integer) RETURNS boolean AS '
|
|
DECLARE
|
|
row record;
|
|
BEGIN
|
|
SELECT INTO row parent, hid FROM linkcat WHERE sn=sn_arg;
|
|
IF NOT FOUND THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
RETURN linkcat_isshown(sn_arg, row.hid, row.parent);
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_isshown(sn_arg integer) TO nobody;
|
|
-- boolean linkcat_isshown_preview(sn_arg integer, hid_arg boolean, parent_arg integer);
|
|
CREATE FUNCTION linkcat_isshown_preview(sn_arg integer, hid_arg boolean, parent_arg integer) RETURNS boolean AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
BEGIN
|
|
IF hid_arg THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
-- Check if we are hidden by our ancestors
|
|
sn_loop := parent_arg;
|
|
WHILE sn_loop IS NOT NULL LOOP
|
|
SELECT INTO row parent, hid FROM linkcat WHERE sn=sn_loop;
|
|
IF row.hid THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
sn_loop = row.parent;
|
|
END LOOP;
|
|
RETURN TRUE;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_isshown_preview(sn_arg integer, hid_arg boolean, parent_arg integer) TO nobody;
|
|
|
|
-- text linkcat_path(sn_arg integer, id_arg text, parent_arg integer);
|
|
CREATE FUNCTION linkcat_path(sn_arg integer, id_arg text, parent_arg integer) RETURNS text AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
path text;
|
|
BEGIN
|
|
PERFORM sn FROM linkcat
|
|
WHERE parent=sn_arg
|
|
AND linkcat_isshown(sn, hid, parent);
|
|
IF FOUND THEN
|
|
path := ''/'' || id_arg || ''/'';
|
|
ELSE
|
|
path := ''/'' || id_arg || ''.html'';
|
|
END IF;
|
|
sn_loop := parent_arg;
|
|
WHILE sn_loop IS NOT NULL LOOP
|
|
SELECT INTO row parent, id FROM linkcat WHERE sn=sn_loop;
|
|
path := ''/'' || row.id || path;
|
|
sn_loop := row.parent;
|
|
END LOOP;
|
|
RETURN path;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_path(sn_arg integer, id_arg text, parent_arg integer) TO nobody;
|
|
-- text linkcat_path(sn_arg integer);
|
|
CREATE FUNCTION linkcat_path(sn_arg integer) RETURNS text AS '
|
|
DECLARE
|
|
row record;
|
|
BEGIN
|
|
IF sn_arg IS NULL THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
SELECT INTO row parent, id FROM linkcat WHERE sn=sn_arg;
|
|
IF NOT FOUND THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
RETURN linkcat_path(sn_arg, row.id, row.parent);
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_path(sn_arg integer) TO nobody;
|
|
|
|
-- boolean linkcat_ischild(parent_arg integer, child_arg integer);
|
|
CREATE FUNCTION linkcat_ischild(parent_arg integer, child_arg integer) RETURNS boolean AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
BEGIN
|
|
sn_loop := child_arg;
|
|
WHILE sn_loop IS NOT NULL LOOP
|
|
SELECT INTO row parent FROM linkcat WHERE sn=sn_loop;
|
|
IF NOT FOUND THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
IF row.parent = parent_arg THEN
|
|
RETURN TRUE;
|
|
END IF;
|
|
sn_loop := row.parent;
|
|
END LOOP;
|
|
RETURN FALSE;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_ischild(parent_arg integer, child_arg integer) TO nobody;
|
|
|
|
-- text linkcat_fulltitle(sn_arg integer);
|
|
CREATE FUNCTION linkcat_fulltitle(sn_arg integer) RETURNS text AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
title_full text;
|
|
BEGIN
|
|
IF sn_arg IS NULL THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
SELECT INTO row * FROM linkcat WHERE sn=sn_arg;
|
|
IF NOT FOUND THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
title_full := row.title;
|
|
WHILE row.parent IS NOT NULL LOOP
|
|
sn_loop := row.parent;
|
|
SELECT INTO row * FROM linkcat WHERE sn=sn_loop;
|
|
title_full := row.title || '' / '' || title_full;
|
|
END LOOP;
|
|
RETURN title_full;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_fulltitle(sn_arg integer) TO nobody;
|
|
-- text linkcat_fulltitle(parent_arg integer, title_arg text);
|
|
CREATE FUNCTION linkcat_fulltitle(parent_arg integer, title_arg text) RETURNS text AS '
|
|
BEGIN
|
|
IF parent_arg IS NULL THEN
|
|
RETURN title_arg;
|
|
END IF;
|
|
RETURN linkcat_fulltitle(parent_arg) || '' / '' || title_arg;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION linkcat_fulltitle(parent_arg integer, title_arg text) TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "linkcat"
|
|
--
|
|
|
|
CREATE TABLE linkcat (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
parent int REFERENCES linkcat ON UPDATE CASCADE DEFERRABLE CHECK (parent IS NULL OR (parent != sn AND NOT linkcat_ischild(sn, parent))),
|
|
id varchar(8) NOT NULL CHECK (char_length(id) >= 2 AND linkcat_id_unique(id, sn, parent)),
|
|
ord smallint NOT NULL DEFAULT 50 CHECK (ord >= 0 AND ord < 100),
|
|
title varchar(128) NOT NULL CHECK (title != ''),
|
|
kw varchar(128) NOT NULL CHECK (kw != ''),
|
|
hid boolean NOT NULL DEFAULT FALSE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON linkcat TO nobody;
|
|
|
|
CREATE VIEW linkcat_list AS
|
|
SELECT
|
|
'/links' || linkcat_path(linkcat.sn, linkcat.id, linkcat.parent) AS _viewurl,
|
|
linkcat.sn AS sn,
|
|
linkcat.id AS id,
|
|
linkcat.ord AS ord,
|
|
linkcat_fulltitle(linkcat.parent, linkcat.title) AS title,
|
|
linkcat.kw AS kw,
|
|
CASE WHEN linkcat.hid THEN '隱藏'
|
|
ELSE '秀出'
|
|
END AS hid,
|
|
to_char(linkcat.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(linkcat.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM linkcat
|
|
LEFT JOIN users AS createdby ON linkcat.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON linkcat.updatedby = updatedby.sn
|
|
ORDER BY linkcat_fullord(linkcat.parent, linkcat.ord), id;
|
|
GRANT SELECT ON linkcat_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "links"
|
|
--
|
|
|
|
CREATE TABLE links (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
title varchar(96) NOT NULL CHECK (title != ''),
|
|
title_2ln varchar(96) CHECK (title_2ln IS NULL OR title_2ln != ''),
|
|
url varchar(128) NOT NULL UNIQUE CHECK (url != '' AND url != 'http://'),
|
|
email varchar(64) CHECK (email IS NULL OR email != ''),
|
|
icon varchar(128) CHECK (icon IS NULL OR (icon != '' AND icon != 'http://')),
|
|
addr varchar(128) CHECK (addr IS NULL OR addr != ''),
|
|
tel varchar(48) CHECK (tel IS NULL OR tel != ''),
|
|
fax varchar(32) CHECK (fax IS NULL OR fax != ''),
|
|
dsc varchar(256) NOT NULL CHECK (dsc != ''),
|
|
hid boolean NOT NULL DEFAULT FALSE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON links TO nobody;
|
|
|
|
CREATE VIEW links_list AS
|
|
SELECT
|
|
links.url AS _viewurl,
|
|
links.sn AS sn,
|
|
links.title AS title,
|
|
links.title_2ln AS title_2ln,
|
|
links.url AS url,
|
|
links.url AS _urlcheck,
|
|
links.icon AS _imgsrc,
|
|
links.email AS email,
|
|
links.addr AS addr,
|
|
links.tel AS tel,
|
|
links.fax AS fax,
|
|
links.dsc AS dsc,
|
|
CASE WHEN links.hid THEN '隱藏'
|
|
ELSE '秀出'
|
|
END AS hid,
|
|
to_char(links.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(links.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM links
|
|
LEFT JOIN users AS createdby ON links.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON links.updatedby = updatedby.sn
|
|
ORDER BY links.title;
|
|
GRANT SELECT ON links_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "linkcatz"
|
|
--
|
|
|
|
CREATE TABLE linkcatz (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
cat int NOT NULL REFERENCES linkcat ON UPDATE CASCADE DEFERRABLE,
|
|
link int NOT NULL REFERENCES links ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
UNIQUE (cat, link)
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON linkcatz TO nobody;
|
|
|
|
CREATE VIEW linkcatz_list AS
|
|
SELECT
|
|
linkcatz.sn AS sn,
|
|
linkcat_fulltitle(linkcat.parent, linkcat.title) AS cat,
|
|
links.title AS link,
|
|
to_char(linkcatz.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(linkcatz.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM linkcatz
|
|
LEFT JOIN linkcat ON linkcatz.cat = linkcat.sn
|
|
LEFT JOIN links ON linkcatz.link = links.sn
|
|
LEFT JOIN users AS createdby ON linkcatz.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON linkcatz.updatedby = updatedby.sn
|
|
ORDER BY linkcat_fullord(linkcat.parent, linkcat.ord), linkcat.id, link;
|
|
GRANT SELECT ON linkcatz_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Function definitions for table "newslets"
|
|
--
|
|
|
|
-- integer newslet_textno(no integer);
|
|
CREATE FUNCTION newslet_textno(no integer) RETURNS text AS '
|
|
DECLARE
|
|
ten integer;
|
|
one integer;
|
|
textno text;
|
|
BEGIN
|
|
-- sanity check
|
|
IF no < 1 THEN
|
|
RETURN NULL;
|
|
-- number one is special
|
|
ELSIF no = 1 THEN
|
|
RETURN ''創刊號'';
|
|
END IF;
|
|
one := no % 10;
|
|
ten := (no - one) / 10;
|
|
-- We do not process numbers larger than 100. It is difficult and not likely.
|
|
IF ten > 9 THEN
|
|
RETURN ''第'' || no || ''期'';
|
|
END IF;
|
|
textno := CASE ten WHEN 0 THEN ''''
|
|
WHEN 1 THEN ''十'' WHEN 2 THEN ''二十'' WHEN 3 THEN ''三十''
|
|
WHEN 4 THEN ''四十'' WHEN 5 THEN ''五十'' WHEN 6 THEN ''六十''
|
|
WHEN 7 THEN ''七十'' WHEN 8 THEN ''八十'' WHEN 9 THEN ''九十''
|
|
END || CASE one WHEN 0 THEN ''''
|
|
WHEN 1 THEN ''一'' WHEN 2 THEN ''二'' WHEN 3 THEN ''三''
|
|
WHEN 4 THEN ''四'' WHEN 5 THEN ''五'' WHEN 6 THEN ''六''
|
|
WHEN 7 THEN ''七'' WHEN 8 THEN ''八'' WHEN 9 THEN ''九''
|
|
END;
|
|
RETURN ''第'' || textno || ''期'';
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION newslet_textno(no integer) TO nobody;
|
|
|
|
-- numeric nlindex_fullord(parent_arg integer, ord_arg integer);
|
|
CREATE FUNCTION nlindex_fullord(parent_arg integer, ord_arg integer) RETURNS numeric AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
ord_loop numeric;
|
|
BEGIN
|
|
sn_loop := parent_arg;
|
|
ord_loop := ord_arg;
|
|
WHILE sn_loop IS NOT NULL LOOP
|
|
SELECT INTO row parent, ord FROM nlindex WHERE sn=sn_loop;
|
|
IF NOT FOUND THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
sn_loop := row.parent;
|
|
ord_loop := row.ord + ord_loop / 100;
|
|
END LOOP;
|
|
RETURN ord_loop;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION nlindex_fullord(parent_arg integer, ord_arg integer) TO nobody;
|
|
-- numeric nlindex_fullord(sn_arg integer);
|
|
CREATE FUNCTION nlindex_fullord(sn_arg integer) RETURNS numeric AS '
|
|
BEGIN
|
|
RETURN nlindex_fullord(sn_arg, 0);
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION nlindex_fullord(sn_arg integer) TO nobody;
|
|
|
|
-- boolean nlindex_ischild(parent_arg integer, child_arg integer);
|
|
CREATE FUNCTION nlindex_ischild(parent_arg integer, child_arg integer) RETURNS boolean AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
BEGIN
|
|
sn_loop := child_arg;
|
|
WHILE sn_loop IS NOT NULL LOOP
|
|
-- TODO: 2019/3/9 Removed schema "public"? Added or it will not work on restore. Not knowing why.
|
|
SELECT INTO row parent FROM public.nlindex WHERE sn=sn_loop;
|
|
IF NOT FOUND THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
IF row.parent = parent_arg THEN
|
|
RETURN TRUE;
|
|
END IF;
|
|
sn_loop := row.parent;
|
|
END LOOP;
|
|
RETURN FALSE;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION nlindex_ischild(parent_arg integer, child_arg integer) TO nobody;
|
|
|
|
-- text nlindex_fulltitle(sn_arg integer);
|
|
CREATE FUNCTION nlindex_fulltitle(sn_arg integer) RETURNS text AS '
|
|
DECLARE
|
|
row record;
|
|
sn_loop integer;
|
|
title_full text;
|
|
BEGIN
|
|
IF sn_arg IS NULL THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
SELECT INTO row nlindex.parent AS parent, COALESCE(nlindex.title, nlarts.title) AS title FROM nlindex
|
|
LEFT JOIN nlarts ON nlindex.art=nlarts.sn
|
|
WHERE nlindex.sn=sn_arg;
|
|
IF NOT FOUND THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
title_full := row.title;
|
|
WHILE row.parent IS NOT NULL LOOP
|
|
sn_loop := row.parent;
|
|
SELECT INTO row nlindex.parent AS parent, COALESCE(nlindex.title, nlarts.title) AS title FROM nlindex
|
|
LEFT JOIN nlarts ON nlindex.art=nlarts.sn
|
|
WHERE nlindex.sn=sn_loop;
|
|
title_full := row.title || '' / '' || title_full;
|
|
END LOOP;
|
|
RETURN title_full;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION nlindex_fulltitle(sn_arg integer) TO nobody;
|
|
-- text nlindex_fulltitle(parent_arg integer, title_arg text);
|
|
CREATE FUNCTION nlindex_fulltitle(parent_arg integer, title_arg text) RETURNS text AS '
|
|
BEGIN
|
|
IF parent_arg IS NULL THEN
|
|
RETURN title_arg;
|
|
END IF;
|
|
RETURN nlindex_fulltitle(parent_arg) || '' / '' || title_arg;
|
|
END
|
|
' LANGUAGE plpgsql;
|
|
GRANT EXECUTE ON FUNCTION nlindex_fulltitle(parent_arg integer, title_arg text) TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "newslets"
|
|
--
|
|
|
|
CREATE TABLE newslets (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
no smallint NOT NULL UNIQUE CHECK (no > 0),
|
|
date date NOT NULL DEFAULT CURRENT_DATE,
|
|
title varchar(32) CHECK (title IS NULL OR title != ''),
|
|
credits varchar(256) CHECK (credits IS NULL OR credits != ''),
|
|
kw varchar(64) NOT NULL CHECK (kw != ''),
|
|
hid boolean NOT NULL DEFAULT FALSE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON newslets TO nobody;
|
|
|
|
CREATE VIEW newslets_list AS
|
|
SELECT
|
|
'/newsletters/' || lpad(cast(newslets.no AS text), 3, '0') || '/'
|
|
AS _viewurl,
|
|
newslets.sn AS sn,
|
|
newslet_textno(newslets.no) AS no,
|
|
newslets.date AS date,
|
|
newslets.title AS title,
|
|
newslets.credits AS credits,
|
|
newslets.kw AS kw,
|
|
CASE WHEN newslets.hid THEN '隱藏'
|
|
ELSE '秀出'
|
|
END AS hid,
|
|
to_char(newslets.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(newslets.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM newslets
|
|
LEFT JOIN users AS createdby ON newslets.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON newslets.updatedby = updatedby.sn
|
|
ORDER BY newslets.no DESC;
|
|
GRANT SELECT ON newslets_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "nlarts"
|
|
--
|
|
|
|
CREATE TABLE nlarts (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
newslet int NOT NULL REFERENCES newslets ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
|
|
ord smallint NOT NULL DEFAULT 50 CHECK (ord >= 0 AND ord < 100),
|
|
title varchar(256) NOT NULL CHECK (title != ''),
|
|
title_h varchar(256) CHECK (title_h IS NULL OR title_h != ''),
|
|
author varchar(8) CHECK (author IS NULL OR author != ''),
|
|
email varchar(64) CHECK ((author IS NULL AND email IS NULL) OR (author IS NOT NULL AND (email IS NULL OR email != ''))),
|
|
authors varchar(64) CHECK ((author IS NULL AND authors IS NULL) OR (author IS NOT NULL AND (authors IS NULL OR authors != ''))),
|
|
body text NOT NULL CHECK (body != ''),
|
|
annots text CHECK (annots IS NULL OR annots != ''),
|
|
kw varchar(128) NOT NULL CHECK (kw != ''),
|
|
html boolean NOT NULL DEFAULT FALSE,
|
|
hid boolean NOT NULL DEFAULT FALSE,
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON nlarts TO nobody;
|
|
|
|
CREATE VIEW nlarts_list AS
|
|
SELECT
|
|
nlarts.sn AS sn,
|
|
newslet_textno(newslets.no) AS newslet,
|
|
nlarts.ord AS ord,
|
|
nlarts.title AS title,
|
|
nlarts.title_h AS title_h,
|
|
COALESCE(nlarts.authors, nlarts.author) AS author,
|
|
nlarts.email AS email,
|
|
nlarts.body AS body,
|
|
nlarts.annots AS annots,
|
|
nlarts.kw AS kw,
|
|
CASE WHEN nlarts.html THEN 'HTML'
|
|
ELSE '純文字'
|
|
END AS html,
|
|
CASE WHEN nlarts.hid THEN '隱藏'
|
|
ELSE '秀出'
|
|
END AS hid,
|
|
to_char(nlarts.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(nlarts.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM nlarts
|
|
LEFT JOIN newslets ON nlarts.newslet = newslets.sn
|
|
LEFT JOIN users AS createdby ON nlarts.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON nlarts.updatedby = updatedby.sn
|
|
ORDER BY newslets.no DESC, nlarts.ord;
|
|
GRANT SELECT ON nlarts_list TO nobody;
|
|
|
|
|
|
--
|
|
-- Table structure for table "nlindex"
|
|
--
|
|
|
|
CREATE TABLE nlindex (
|
|
sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
|
|
newslet int NOT NULL REFERENCES newslets ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
|
|
parent int REFERENCES nlindex ON UPDATE CASCADE DEFERRABLE CHECK (parent IS NULL OR (parent != sn AND NOT nlindex_ischild(sn, parent))),
|
|
ord smallint NOT NULL DEFAULT 50 CHECK (ord >= 0 AND ord < 100),
|
|
art int UNIQUE REFERENCES nlarts ON UPDATE CASCADE DEFERRABLE,
|
|
title varchar(128) CHECK ((art IS NULL AND (title IS NOT NULL AND title != '')) OR (art IS NOT NULL AND (title IS NULL OR title != ''))),
|
|
created timestamp NOT NULL DEFAULT now(),
|
|
createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
|
|
updated timestamp NOT NULL DEFAULT now(),
|
|
updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
|
|
);
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON nlindex TO nobody;
|
|
|
|
CREATE VIEW nlindex_list AS
|
|
SELECT
|
|
nlindex.sn AS sn,
|
|
newslet_textno(newslets.no) AS newslet,
|
|
nlindex.ord AS ord,
|
|
nlindex_fulltitle(nlindex.parent, COALESCE(nlindex.title, nlarts.title)) AS title,
|
|
to_char(nlindex.created, 'YYYY-MM-DD HH:MI:SS') AS created,
|
|
createdby.name AS createdby,
|
|
to_char(nlindex.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
|
|
updatedby.name AS updatedby
|
|
FROM nlindex
|
|
LEFT JOIN newslets ON nlindex.newslet = newslets.sn
|
|
LEFT JOIN nlarts ON nlindex.art = nlarts.sn
|
|
LEFT JOIN users AS createdby ON nlindex.createdby = createdby.sn
|
|
LEFT JOIN users AS updatedby ON nlindex.updatedby = updatedby.sn
|
|
ORDER BY newslets.no DESC, nlindex_fullord(nlindex.parent, nlindex.ord);
|
|
GRANT SELECT ON nlindex_list TO nobody;
|
|
|
|
|
|
--
|
|
-- VIEW: Search
|
|
--
|
|
CREATE VIEW search_list AS
|
|
(SELECT
|
|
'pages' AS section,
|
|
pages.path AS path,
|
|
pages.title AS title,
|
|
null AS author,
|
|
to_char(pages.updated, 'YYYY-MM-DD') AS date,
|
|
pages.body AS body,
|
|
pages.kw AS kw,
|
|
pages.html AS html
|
|
FROM pages
|
|
WHERE NOT pages.hid
|
|
AND pages.path NOT LIKE '/errors/%')
|
|
UNION
|
|
(SELECT
|
|
'links' AS section,
|
|
links.url AS path,
|
|
links.title AS title,
|
|
null AS author,
|
|
to_char(links.updated, 'YYYY-MM-DD') AS date,
|
|
links.dsc AS body,
|
|
CASE WHEN links.title_2ln IS NULL THEN '' ELSE links.title_2ln END
|
|
|| '
|
|
' || CASE WHEN links.url IS NULL THEN '' ELSE links.url END
|
|
|| '
|
|
' || CASE WHEN links.email IS NULL THEN '' ELSE links.email END
|
|
|| '
|
|
' || CASE WHEN links.addr IS NULL THEN '' ELSE links.addr END
|
|
|| '
|
|
' || CASE WHEN links.tel IS NULL THEN '' ELSE links.tel END
|
|
|| '
|
|
' || CASE WHEN links.fax IS NULL THEN '' ELSE links.fax END
|
|
AS kw,
|
|
FALSE AS html
|
|
FROM links
|
|
WHERE NOT links.hid)
|
|
UNION
|
|
(SELECT
|
|
'guestbook' AS section,
|
|
'/cgi-bin/guestbook.cgi?pageno=' || guestbook.pageno
|
|
|| '#msg' || guestbook.sn
|
|
AS path,
|
|
null AS title,
|
|
guestbook.name AS author,
|
|
to_char(guestbook.created, 'YYYY-MM-DD') AS date,
|
|
guestbook.message AS body,
|
|
CASE WHEN guestbook.identity IS NULL THEN '' ELSE guestbook.identity END
|
|
|| '
|
|
' || CASE WHEN guestbook.location IS NULL THEN '' ELSE guestbook.location END
|
|
|| '
|
|
' || CASE WHEN guestbook.email IS NULL THEN '' ELSE guestbook.email END
|
|
|| '
|
|
' || CASE WHEN guestbook.url IS NULL THEN '' ELSE guestbook.url END
|
|
AS kw,
|
|
FALSE AS html
|
|
FROM guestbook
|
|
WHERE NOT guestbook.hid)
|
|
ORDER BY date DESC, title;
|
|
GRANT SELECT ON search_list TO nobody;
|
|
|
|
|
|
COMMIT;
|
|
|