summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2018-02-08 16:06:17 +0100
committerYorhel <git@yorhel.nl>2018-02-08 16:12:07 +0100
commit93b79ef9ebafcfccd0d239ffd06e2b547e209e3d (patch)
tree98d96f024d00bf318b16243518e53c2977136202 /util
parent3f3a4d9810bb2483a89442b85d438639f26ecb7e (diff)
Store d+ pages in the DB as versioned entries + use markdown
This touches a bunch of things: - Adds a new first-class database entry type - Removes the d+.+.+ BBCode link syntax, adds a new d+#+ and d+#+.+ link syntax (references have been updated where possible) - Adds a new dependency on Text::MultiMarkdown
Diffstat (limited to 'util')
-rwxr-xr-xutil/bbcode-test.pl4
-rw-r--r--util/sql/all.sql4
-rw-r--r--util/sql/devdb.sql55
-rw-r--r--util/sql/func.sql3
-rw-r--r--util/sql/perms.sql4
-rw-r--r--util/sql/schema.sql16
-rw-r--r--util/updates/update_20180208.sql57
7 files changed, 137 insertions, 6 deletions
diff --git a/util/bbcode-test.pl b/util/bbcode-test.pl
index ba39fb13..1100b34e 100755
--- a/util/bbcode-test.pl
+++ b/util/bbcode-test.pl
@@ -105,6 +105,10 @@ my @tests = (
'<a href="/r12.1">r12.1</a> <a href="/v6.3">v6.3</a> <a href="/s1.2">s1.2</a>',
'r12.1 v6.3 s1.2',
+ 'd3 d1.3 d2#4 d5#6.7',
+ '<a href="/d3">d3</a> <a href="/d1.3">d1.3</a> <a href="/d2#4">d2#4</a> <a href="/d5#6.7">d5#6.7</a>',
+ 'd3 d1.3 d2#4 d5#6.7',
+
'v17 text dds16v21 more text1 v9',
'<a href="/v17">v17</a> text dds16v21 more text1 <a href="/v9">v9</a>',
'v17 text dds16v21 more text1 v9',
diff --git a/util/sql/all.sql b/util/sql/all.sql
index b3a87948..a8009b0d 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -8,13 +8,13 @@ CREATE TYPE blood_type AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
-CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's');
+CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's', 'd');
CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
CREATE TYPE language AS ENUM ('ar', 'bg', 'ca', 'cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'he', 'hr', 'hu', 'id', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sv', 'ta', 'th', 'tr', 'uk', 'vi', 'zh');
CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
-CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't', 's');
+CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't', 's', 'd');
CREATE TYPE platform AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'fmt', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pce', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'psv', 'drc', 'sat', 'sfc', 'swi', 'wii', 'wiu', 'n3d', 'x68', 'xb1', 'xb3', 'xbo', 'web', 'oth');
CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce', 'vn_list_own', 'vn_list_wish', 'tags_all', 'tags_cat', 'spoilers', 'traits_sexual');
CREATE TYPE producer_type AS ENUM ('co', 'in', 'ng');
diff --git a/util/sql/devdb.sql b/util/sql/devdb.sql
index b4c3e4c4..dea9ab29 100644
--- a/util/sql/devdb.sql
+++ b/util/sql/devdb.sql
@@ -4,7 +4,7 @@
SET CONSTRAINTS ALL DEFERRED;
-- Hack to disable triggers
SET session_replication_role = replica;
-TRUNCATE TABLE chars_vns, chars_traits_hist, staff_hist, tags_vn, vn_anime_hist, sessions, releases_lang_hist, releases_lang, rlists, releases_producers_hist, releases_platforms, releases_producers, releases_vn_hist, tags_parents, screenshots, stats_cache, threads_poll_options, threads_poll_votes, traits_parents, votes, vn_relations_hist, vn_screenshots_hist, vn_hist, vn_relations, releases_vn, releases_media, releases_hist, vn_seiyuu_hist, vn_screenshots, vn_seiyuu, vn_staff_hist, traits, producers_hist, releases_platforms_hist, producers_relations, releases_media_hist, chars_hist, chars, staff, users_prefs, vn, tags_aliases, affiliate_links, vn_anime, releases, login_throttle, relgraphs, tags, staff_alias, traits_chars, threads_posts, threads, threads_boards, tags_vn_inherit, users, vn_staff, vnlists, notifications, chars_traits, producers, anime, staff_alias_hist, wlists, chars_vns_hist, quotes, changes, producers_relations_hist CASCADE;
+TRUNCATE TABLE chars_traits_hist, chars_vns_hist, releases_media_hist, releases_lang_hist, releases_platforms_hist, releases_producers_hist, releases_vn_hist, threads_poll_options, threads_poll_votes, vn_relations_hist, vn_seiyuu_hist, vn_anime_hist, affiliate_links, anime, changes, chars, chars_hist, chars_traits, chars_vns, docs, docs_hist, login_throttle, notifications, producers, producers_hist, producers_relations, quotes, releases, releases_hist, releases_lang, releases_media, releases_platforms, releases_producers, releases_vn, relgraphs, rlists, screenshots, staff, staff_alias, staff_alias_hist, staff_hist, stats_cache, tags, tags_aliases, tags_parents, tags_vn, tags_vn_inherit, threads, threads_boards, threads_posts, traits, traits_chars, traits_parents, users, users_prefs, vn, vn_anime, vn_hist, vn_relations, vn_screenshots, vn_screenshots_hist, vn_seiyuu, vn_staff, vn_staff_hist, vnlists, producers_relations_hist, votes, wlists, sessions CASCADE;
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
@@ -28,8 +28,8 @@ COPY anime (id, year, ann_id, nfo_id, type, title_romaji, title_kanji, lastfetch
COPY users (id, username, mail, perm, passwd, registered, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed) FROM stdin;
0 deleted del@vndb.org 0 \\x 2018-01-04 19:13:32.823908+00 0 0 0.0.0.0 0 f f
6 user user@vndb.org 21 \\x0001000008013fb73157f3cdcd27990198726e7e083007b307adcc70a4705170acd69e307f5e764e031d28d39eb4 2018-01-04 22:25:42.054632+00 0 3 0.0.0.0 0 f f
-1 multi multi@vndb.org 0 \\x 2018-01-04 19:13:32.82486+00 0 3 0.0.0.0 0 f f
4 admin admin@vndb.org 503 \\x000100000801ec4185fed438752d6b3b968e2b2cd045f70005cb7e10cafdbb694a82246bd34a065b6e977e0c3dcc 2018-01-04 19:26:10.731713+00 6 48 0.0.0.0 3 f f
+1 multi multi@vndb.org 0 \\x 2018-01-04 19:13:32.82486+00 0 17 0.0.0.0 0 f f
\.
COPY changes (id, type, itemid, rev, added, requester, ip, comments, ihid, ilock) FROM stdin;
1 v 1 1 2018-01-04 22:30:48.928438+00 4 0.0.0.0 add f f
@@ -50,8 +50,22 @@ COPY changes (id, type, itemid, rev, added, requester, ip, comments, ihid, ilock
16 v 2 4 2018-02-03 22:01:48.939799+00 4 0.0.0.0 relation f f
17 v 3 2 2018-02-03 22:01:48.939799+00 1 0.0.0.0 Reverse relation update caused by revision v2.4 f f
18 v 1 3 2018-02-03 22:02:58.404134+00 4 0.0.0.0 more info f f
-\.
-SELECT pg_catalog.setval('changes_id_seq', 18, true);
+19 d 2 1 2018-02-08 14:54:17.489737+00 1 0.0.0.0 Empty page f f
+20 d 3 1 2018-02-08 14:54:17.502614+00 1 0.0.0.0 Empty page f f
+21 d 4 1 2018-02-08 14:54:17.511867+00 1 0.0.0.0 Empty page f f
+22 d 5 1 2018-02-08 14:54:17.528422+00 1 0.0.0.0 Empty page f f
+23 d 6 1 2018-02-08 14:54:17.531662+00 1 0.0.0.0 Empty page f f
+24 d 7 1 2018-02-08 14:54:17.54061+00 1 0.0.0.0 Empty page f f
+25 d 9 1 2018-02-08 14:54:17.5497+00 1 0.0.0.0 Empty page f f
+26 d 10 1 2018-02-08 14:54:17.553128+00 1 0.0.0.0 Empty page f f
+27 d 11 1 2018-02-08 14:54:17.564129+00 1 0.0.0.0 Empty page f f
+28 d 12 1 2018-02-08 14:54:17.57348+00 1 0.0.0.0 Empty page f f
+29 d 13 1 2018-02-08 14:54:17.582157+00 1 0.0.0.0 Empty page f f
+30 d 14 1 2018-02-08 14:54:17.590882+00 1 0.0.0.0 Empty page f f
+31 d 15 1 2018-02-08 14:54:17.599512+00 1 0.0.0.0 Empty page f f
+32 d 16 1 2018-02-08 14:54:17.608296+00 1 0.0.0.0 Empty page f f
+\.
+SELECT pg_catalog.setval('changes_id_seq', 32, true);
SELECT pg_catalog.setval('charimg_seq', 1, false);
COPY chars (id, locked, hidden, name, original, alias, image, "desc", gender, s_bust, s_waist, s_hip, b_month, b_day, height, weight, bloodt, main, main_spoil) FROM stdin;
1 f f Celica A. Mercury セリカ゠アヤツキ゠マーキュリー Antenna Girl, Celica Ayatsuki Mercury 0 Hobbies: Strolls\nLikes: Sister, family\nDislikes: Nothing\n\nCelica Ayatsuki Mercury is the younger sister of Nine, Jubei's sister-in-law and aunt of Kokonoe. She is a playable character in Chronophantasma Extend and Centralfiction. After the Dark War, she watched over the Black Beast's remains and built a church. She was also the previous owner of the Nox Nyctores, Deus Machina: Nirvana, but both she and Trinity sealed Nirvana at some point.\n<hidden by spoiler settings>\n\nCelica is a kind, generous, and sociable person who deeply loves her sister and friends. She is a sentimental individual who maintains a firm stance that all life is precious and will use her healing magic to assist those in need of it, regardless of who or what they are. She is also shown to be selfless to the point that she will shield a person with her own body without any hesitation and is willing to sacrifice herself, believing that her sacrifice will be a small price to pay for the chance to defeat the Black Beast and save the world.\nIt has also been noted by people that she has no sense of direction, which usually ends with her getting lost. She is also shown to be very stubborn, such as when she ignores her sister's warnings and heads to Japan by herself to search for her missing father and is also unwilling to admit that she has a poor sense of direction.\n\nCelica has the rare ability to use healing Magic. She has been shown to heal most wounds, although she cannot use her magic to remove illnesses such as seithr poisoning. She has also exhibited the latent ability to suppress seithr, and it is for this reason that Celica is the key to Kushinada's Lynchpin, a device created to seal the seithr within the Gate, and thus disrupt the source of the Black Beast's power, although at the cost of her life. This also makes her the ultimate weakness of the Azure Grimoire as Ragna was unable to see through his right eye and move or use his Grimoire as long as he was near her. f 0 0 0 9 8 160 51 a \N 0
@@ -86,6 +100,39 @@ COPY chars_vns_hist (chid, vid, rid, spoil, role) FROM stdin;
13 2 \N 0 primary
\.
SELECT pg_catalog.setval('covers_seq', 1, false);
+COPY docs (id, locked, hidden, title, content) FROM stdin;
+2 f f Adding/Editing a Visual Novel
+3 f f Adding/Editing a Release
+4 f f Adding/Editing a Producer
+5 f f Editing guidelines
+6 f f Frequently Asked Questions
+7 f f About us
+9 f f Discussion board
+10 f f Tags & traits
+11 f f Public Database API
+12 f f Adding/Editing Characters
+13 f f How to Capture Screenshots
+14 f f Database Dumps
+15 f f Special Games
+16 f f Adding/Editing Staff Members
+\.
+COPY docs_hist (chid, title, content) FROM stdin;
+19 Adding/Editing a Visual Novel
+20 Adding/Editing a Release
+21 Adding/Editing a Producer
+22 Editing guidelines
+23 Frequently Asked Questions
+24 About us
+25 Discussion board
+26 Tags & traits
+27 Public Database API
+28 Adding/Editing Characters
+29 How to Capture Screenshots
+30 Database Dumps
+31 Special Games
+32 Adding/Editing Staff Members
+\.
+SELECT pg_catalog.setval('docs_id_seq', 16, true);
COPY login_throttle (ip, timeout) FROM stdin;
127.0.0.0 2018-02-04 14:45:01+00
\.
diff --git a/util/sql/func.sql b/util/sql/func.sql
index dcd537b3..e36d46e8 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -258,6 +258,7 @@ BEGIN
WHEN 'p' THEN INSERT INTO producers DEFAULT VALUES RETURNING id INTO ret.itemid;
WHEN 'c' THEN INSERT INTO chars DEFAULT VALUES RETURNING id INTO ret.itemid;
WHEN 's' THEN INSERT INTO staff DEFAULT VALUES RETURNING id INTO ret.itemid;
+ WHEN 'd' THEN INSERT INTO docs DEFAULT VALUES RETURNING id INTO ret.itemid;
END CASE;
END IF;
-- insert change
@@ -624,6 +625,7 @@ CREATE OR REPLACE FUNCTION notify_dbdel(xtype dbentry_type, xedit edit_rettype)
UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
+ UNION SELECT d.title FROM docs d WHERE xtype = 'd' AND d.id = xedit.itemid
UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
) x(title) ON true
WHERE h.type = xtype AND h.itemid = xedit.itemid
@@ -645,6 +647,7 @@ CREATE OR REPLACE FUNCTION notify_dbedit(xtype dbentry_type, xedit edit_rettype)
UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
+ UNION SELECT d.title FROM docs d WHERE xtype = 'd' AND d.id = xedit.itemid
UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
) x(title) ON true
WHERE h.type = xtype AND h.itemid = xedit.itemid
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index 4a5d94ef..5b643ed1 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -14,6 +14,8 @@ GRANT SELECT, INSERT, DELETE ON chars_traits TO vndb_site;
GRANT SELECT, INSERT ON chars_traits_hist TO vndb_site;
GRANT SELECT, INSERT, DELETE ON chars_vns TO vndb_site;
GRANT SELECT, INSERT ON chars_vns_hist TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON docs TO vndb_site;
+GRANT SELECT, INSERT ON docs_hist TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON producers TO vndb_site;
@@ -98,6 +100,8 @@ GRANT SELECT ON chars TO vndb_multi;
GRANT SELECT ON chars_hist TO vndb_multi;
GRANT SELECT ON chars_traits TO vndb_multi;
GRANT SELECT ON chars_vns TO vndb_multi;
+GRANT SELECT ON docs TO vndb_multi;
+GRANT SELECT ON docs_hist TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_multi;
GRANT SELECT, UPDATE ON producers TO vndb_multi;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 4cddf7e7..c580894f 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -157,6 +157,22 @@ CREATE TABLE chars_vns_hist (
role char_role NOT NULL DEFAULT 'main'
);
+-- docs
+CREATE TABLE docs ( -- dbentry_type=d
+ id SERIAL PRIMARY KEY,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ title varchar(200) NOT NULL DEFAULT '',
+ content text NOT NULL DEFAULT ''
+);
+
+-- docs_hist
+CREATE TABLE docs_hist (
+ chid integer NOT NULL PRIMARY KEY,
+ title varchar(200) NOT NULL DEFAULT '',
+ content text NOT NULL DEFAULT ''
+);
+
-- login_throttle
CREATE TABLE login_throttle (
ip inet NOT NULL PRIMARY KEY,
diff --git a/util/updates/update_20180208.sql b/util/updates/update_20180208.sql
new file mode 100644
index 00000000..d84ac0e1
--- /dev/null
+++ b/util/updates/update_20180208.sql
@@ -0,0 +1,57 @@
+CREATE TABLE docs (
+ id SERIAL PRIMARY KEY,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ title varchar(200) NOT NULL DEFAULT '',
+ content text NOT NULL DEFAULT ''
+);
+CREATE TABLE docs_hist (
+ chid integer NOT NULL PRIMARY KEY,
+ title varchar(200) NOT NULL DEFAULT '',
+ content text NOT NULL DEFAULT ''
+);
+ALTER TYPE dbentry_type ADD VALUE 'd';
+ALTER TYPE notification_ltype ADD VALUE 'd';
+
+\i util/sql/func.sql
+\i util/sql/editfunc.sql
+\i util/sql/perms.sql
+
+
+-- Insert empty pages
+CREATE OR REPLACE FUNCTION insert_doc(integer, text) RETURNS void AS $$
+BEGIN
+ PERFORM setval('docs_id_seq', $1-1);
+ PERFORM edit_d_init(NULL, NULL);
+ UPDATE edit_revision SET requester = 1, comments = 'Empty page', ip = '0.0.0.0';
+ UPDATE edit_docs SET title = $2;
+ PERFORM edit_d_commit();
+END
+$$ LANGUAGE plpgsql;
+
+SELECT insert_doc( 2, 'Adding/Editing a Visual Novel');
+SELECT insert_doc( 3, 'Adding/Editing a Release');
+SELECT insert_doc( 4, 'Adding/Editing a Producer');
+SELECT insert_doc( 5, 'Editing guidelines');
+SELECT insert_doc( 6, 'Frequently Asked Questions');
+SELECT insert_doc( 7, 'About us');
+SELECT insert_doc( 9, 'Discussion board');
+SELECT insert_doc(10, 'Tags & traits');
+SELECT insert_doc(11, 'Public Database API');
+SELECT insert_doc(12, 'Adding/Editing Characters');
+SELECT insert_doc(13, 'How to Capture Screenshots');
+SELECT insert_doc(14, 'Database Dumps');
+SELECT insert_doc(15, 'Special Games');
+SELECT insert_doc(16, 'Adding/Editing Staff Members');
+
+DROP FUNCTION insert_doc(integer, text);
+
+
+
+-- Update doc references
+CREATE OR REPLACE FUNCTION safedocreplace(text) RETURNS text AS $$
+ SELECT regexp_replace($1, 'd(2|3|4|5|6|7|9|10|11|12|13|14|15|16)\.([1-8](?:\.[1-8])?)', 'd\1#\2', 'g')
+$$ LANGUAGE sql;
+UPDATE threads_posts SET msg = safedocreplace(msg) WHERE msg ~ 'd[1-9]';
+UPDATE changes SET comments = safedocreplace(comments) WHERE comments ~ 'd[1-9]';
+DROP FUNCTION safedocreplace(text);