phpBB 3 Linked Data Views Script to set up your own instance
USE PHPBB3;
DROP VIEW PHPBB3.DBA.phpBB3_site_rdf;
DROP VIEW PHPBB3.DBA.phpBB3_users_rdf;
DROP VIEW PHPBB3.DBA.phpBB3_forums_rdf;
DROP VIEW PHPBB3.DBA.phpBB3_usergroups_rdf;
DROP VIEW PHPBB3.DBA.phpBB3_user_usergroups_rdf;
DROP VIEW PHPBB3.DBA.phpBB3_forum_moderators_rdf;
DROP VIEW PHPBB3.DBA.phpBB3_posts_rdf;
DROP VIEW PHPBB3.DBA.phpBB3_forum_posts_rdf;
CREATE VIEW "PHPBB3"."phpBB".phpBB3_site_rdf AS
SELECT TOP 1
config_value AS WEB_TITLE,
'' AS WEB_LINK,
'' AS WS_DUMMY
FROM phpbb_config
WHERE config_name = 'sitename';
CREATE VIEW "PHPBB3"."phpBB".phpBB3_users_rdf AS
SELECT user_id,
username,
'' AS WS_DUMMY
FROM phpbb_users
WHERE user_type <> 2;
CREATE VIEW "PHPBB3"."phpBB".phpBB3_usergroups_rdf AS
SELECT group_id,
group_name
FROM phpbb_groups;
CREATE VIEW "PHPBB3"."phpBB".phpBB3_user_usergroups_rdf AS
SELECT g.group_name,
u.username
FROM phpbb_user_group ug,
phpbb_users u,
phpbb_groups g
WHERE ug.group_id = g.group_id
AND u.user_id = ug.user_id
AND ug.user_pending = 0;
CREATE VIEW "PHPBB3"."phpBB".phpBB3_forums_rdf AS
SELECT f.forum_id,
f.forum_name,
f.forum_desc,
'Forum' AS forum_type,
'' AS WS_DUMMY
FROM phpbb_forums f
phpbb_acl_groups aclg,
phpbb_groups g
WHERE
f.forum_id = aclg.forum_id
AND aclg.group_id = g.group_id
AND g.group_name = 'GUESTS';
CREATE VIEW "PHPBB3"."phpBB".phpBB3_forum_moderators_rdf AS
SELECT forum_id,
u.username
FROM phpbb_user_group ug,
phpbb_acl_groups aa,
phpbb_users u
WHERE ug.group_id = aa.group_id
AND u.user_id = ug.user_id
AND aa.group_id = 4
AND ug.user_pending = 0;
CREATE VIEW "PHPBB3"."phpBB".phpBB3_posts_rdf AS
SELECT pp.post_id,
pp.forum_id,
u.username,
pp.post_subject,
pp.post_text
FROM phpbb_posts pp,
phpbb_users u,
phpbb_acl_groups aclg,
phpbb_groups g
WHERE pp.poster_id = u.user_id
AND pp.forum_id = aclg.forum_id
AND aclg.group_id = g.group_id
AND g.group_name = 'GUESTS';
CREATE VIEW "PHPBB3"."phpBB".phpBB3_forum_posts_rdf AS
SELECT post_id,
forum_id
FROM phpbb_posts;
GRANT SELECT ON "PHPBB3"."phpBB".phpBB3_site_rdf TO "SPARQL";
GRANT SELECT ON "PHPBB3"."phpBB".phpBB3_users_rdf TO "SPARQL";
GRANT SELECT ON "PHPBB3"."phpBB".phpBB3_usergroups_rdf TO "SPARQL";
GRANT SELECT ON "PHPBB3"."phpBB".phpBB3_user_usergroups_rdf TO "SPARQL";
GRANT SELECT ON "PHPBB3"."phpBB".phpBB3_forums_rdf TO "SPARQL";
GRANT SELECT ON "PHPBB3"."phpBB".phpBB3_forum_moderators_rdf TO "SPARQL";
GRANT SELECT ON "PHPBB3"."phpBB".phpBB3_posts_rdf TO "SPARQL";
GRANT SELECT ON "PHPBB3"."phpBB".phpBB3_forum_posts_rdf TO "SPARQL";
GRANT EXECUTE ON DB.DBA.RDF_SPONGE_UP TO "SPARQL";
GRANT SPARQL_UPDATE TO "SPARQL";
SPARQL
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX sioct: <http://rdfs.org/sioc/types#>
PREFIX atom: <http://atomowl.org/ontologies/atomrdf#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX dct: <http://purl.org/dc/terms/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>
PREFIX bm: <http://www.w3.org/2002/01/bookmark#>
PREFIX exif: <http://www.w3.org/2003/12/exif/ns/>
PREFIX ann: <http://www.w3.org/2000/10/annotation-ns#>
PREFIX phpbb: <http://openlinksw.com/phpBB3/>
DROP QUAD MAP GRAPH IRI("http://^{URIQADefaultHost}^/phpBB3_v/") .
CREATE IRI CLASS phpbb:site_iri
"http://^{URIQADefaultHost}^/phpBB3/%U#this"
( IN dummy VARCHAR NOT NULL ) .
CREATE IRI CLASS phpbb:user_iri
"http://^{URIQADefaultHost}^/phpBB3/user/%U#this"
( IN user_name INTEGER NOT NULL ) .
CREATE IRI CLASS phpbb:usergroup_iri
"http://^{URIQADefaultHost}^/phpBB3/group/%U#this"
( IN group_name INTEGER NOT NULL ) .
CREATE IRI CLASS phpbb:forum_iri
"http://^{URIQADefaultHost}^/phpBB3/forum/%d#this"
( IN forum_id INTEGER NOT NULL ) .
CREATE IRI CLASS phpbb:post_iri
"http://^{URIQADefaultHost}^/phpBB3/post/%d#this"
( IN post_id INTEGER NOT NULL ) .
ALTER QUAD STORAGE virtrdf:DefaultQuadStorage
{
CREATE virtrdf:phpBB3 AS GRAPH IRI ("http://^{URIQADefaultHost}^/phpBB3_v/")
{
# Default phpBB Space
phpbb:site_iri (PHPBB3.phpBB.phpBB3_site_rdf.WS_DUMMY)
a sioc:Space ;
dc:title WEB_TITLE .
# User
phpbb:user_iri (PHPBB3.phpBB.phpBB3_users_rdf.username)
a sioc:User ;
sioc:id user_id ;
sioc:name username ;
sioc:has_space phpbb:site_iri(WS_DUMMY) .
# Usergroup
phpbb:usergroup_iri (PHPBB3.phpBB.phpBB3_usergroups_rdf.group_name)
a sioc:Usergroup ;
sioc:id group_id ;
sioc:name group_name .
# Usergroup - USER relationship
phpbb:usergroup_iri (PHPBB3.phpBB.phpBB3_user_usergroups_rdf.group_name)
sioc:has_member phpbb:user_iri(username) .
# USER - Usergroup relationship
phpbb:user_iri (PHPBB3.phpBB.phpBB3_user_usergroups_rdf.username)
sioc:member_of phpbb:usergroup_iri(group_name) .
# Forum
phpbb:forum_iri (PHPBB3.phpBB.phpBB3_forums_rdf.forum_id)
a sioc:Forum ;
sioc:id forum_id ;
sioc:name forum_name ;
sioc:description forum_desc ;
dc:title forum_name ;
dc:description forum_desc ;
sioc:type forum_type .
# Forum - moderators relationship
phpbb:user_iri (PHPBB3.phpBB.phpBB3_forum_moderators_rdf.username)
sioc:moderator_of phpbb:forum_iri(forum_id) .
# Site - Forum relationship
phpbb:site_iri (PHPBB3.phpBB.phpBB3_users_rdf.WS_DUMMY)
sioc:space_of phpbb:user_iri(username) .
# Moderators - Forum relationship
phpbb:forum_iri (PHPBB3.phpBB.phpBB3_forum_moderators_rdf.forum_id)
sioc:has_moderator phpbb:user_iri(username) .
# Post
phpbb:post_iri (PHPBB3.phpBB.phpBB3_posts_rdf.post_id)
a sioc:Post ;
sioc:id post_id ;
sioc:has_container phpbb:forum_iri(forum_id) ;
dc:title post_subject ;
sioc:has_creator phpbb:user_iri (username) ;
sioc:content post_text .
phpbb:user_iri (PHPBB3.phpBB.phpBB3_posts_rdf.username)
sioc:creator_of phpbb:post_iri(post_id) .
# Forum - post
phpbb:forum_iri (PHPBB3.phpBB.phpBB3_forum_posts_rdf.forum_id)
sioc:container_of phpbb:post_iri(post_id)
}
};
DELETE FROM db.dba.url_rewrite_rule_list
WHERE urrl_list like 'phpBB3_rule%';
DELETE FROM db.dba.url_rewrite_rule
WHERE urr_rule like 'phpBB3_rule%';
CREATE PROCEDURE phpBB3.phpbb.ur_get_uid
(
IN par VARCHAR,
IN fmt VARCHAR,
IN val VARCHAR
)
{
DECLARE id INT;
id := (SELECT user_id FROM phpBB3..phpbb_users WHERE username = val);
RETURN sprintf (fmt, id);
};
CREATE PROCEDURE phpBB3.phpbb.ur_get_gid
(
IN par VARCHAR,
IN fmt VARCHAR,
IN val VARCHAR
)
{
DECLARE id INT;
id := (SELECT group_id FROM phpBB3..phpbb_groups WHERE group_name = val);
RETURN sprintf (fmt, id);
};
CREATE PROCEDURE phpBB3.phpbb.ur_get_uname
(
IN par VARCHAR,
IN fmt VARCHAR,
IN val VARCHAR
)
{
DECLARE id ANY;
id := (SELECT username FROM phpBB3..phpbb_users WHERE user_id = val);
RETURN sprintf (fmt, id);
};
CREATE PROCEDURE phpBB3.phpbb.ur_get_gname
(
IN par VARCHAR,
IN fmt VARCHAR,
IN val VARCHAR
)
{
DECLARE id ANY;
id := (SELECT group_name FROM phpBB3..phpbb_groups WHERE group_id = val);
RETURN sprintf (fmt, id);
};
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule_u',
1,
'/phpBB3/user/([^#]*)',
VECTOR('uname'),
1,
'/phpBB3/memberlist.php?mode=viewprofile&u=%d',
VECTOR('uname'),
'phpBB3.phpbb.ur_get_uid',
NULL,
2,
303
);
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule_ug',
1,
'/phpBB3/group/([^#]*)',
VECTOR('uname'),
1,
'/phpBB3/memberlist.php?mode=group&g=%d',
VECTOR('uname'),
'phpBB3.phpbb.ur_get_gid',
NULL,
2,
303
);
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule_f',
1,
'/phpBB3/forum/([^#]*)',
VECTOR('id'),
1,
'/phpBB3/viewforum.php?f=%s',
VECTOR('id'),
NULL,
NULL,
2,
303
);
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule_p',
1,
'/phpBB3/post/([^#]*)',
VECTOR('id'),
1,
'/phpBB3/viewtopic.php?t=%s',
VECTOR('id'),
NULL,
NULL,
2,
303
);
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule',
1,
'/phpBB3/([^#]*)',
VECTOR('path'),
1,
'/sparql?query=DESCRIBE+%%3Chttp%%3A//^{URIQADefaultHost}^/phpBB3/%U%%23this%%3E+FROM+%%3Chttp%%3A//^{URIQADefaultHost}^/phpBB3_v%%2F%%3E&format=%U',
VECTOR('path', '*accept*'),
NULL,
'(text/rdf.n3)|(application/rdf.xml)|(text/rdf.turtle)|(text/rdf.ttl)',
2,
NULL
);
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule_u_rdf',
1,
'/phpBB3/memberlist.php\\?mode=viewprofile&u=([0-9]+)',
VECTOR('id'),
1,
'/phpBB3/user/%U#this',
VECTOR('id'),
'phpBB3.phpbb.ur_get_uname',
'(text/rdf.n3)|(application/rdf.xml)|(text/rdf.turtle)|(text/rdf.ttl)',
2,
303
);
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule_g_rdf',
1,
'/phpBB3/memberlist.php\\?mode=group&g=([0-9]+)',
VECTOR('id'),
1,
'/phpBB3/group/%U#this',
VECTOR('id'),
'phpBB3.phpbb.ur_get_gname',
'(text/rdf.n3)|(application/rdf.xml)|(text/rdf.turtle)|(text/rdf.ttl)',
2,
303
);
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule_f_rdf',
1,
'/phpBB3/viewforum.php\\?f=([0-9]+)',
VECTOR('id'),
1,
'/phpBB3/forum/%U#this',
VECTOR('id'),
NULL,
'(text/rdf.n3)|(application/rdf.xml)|(text/rdf.turtle)|(text/rdf.ttl)',
2,
303
);
db.dba.urlrewrite_create_regex_rule
(
'phpBB3_rule_p_rdf',
1,
'/phpBB3/viewtopic.php\\?t=([0-9]+)',
VECTOR('id'),
1,
'/phpBB3/post/%U#this',
VECTOR('id'),
NULL,
'(text/rdf.n3)|(application/rdf.xml)|(text/rdf.turtle)|(text/rdf.ttl)',
2,
303
);
db.dba.urlrewrite_create_rulelist
(
'phpBB3_rule_list',
1,
VECTOR
(
'phpBB3_rule_u',
'phpBB3_rule_ug',
'phpBB3_rule_f',
'phpBB3_rule_p',
'phpBB3_rule',
'phpBB3_rule_u_rdf',
'phpBB3_rule_g_rdf',
'phpBB3_rule_f_rdf',
'phpBB3_rule_p_rdf'
)
);
db.dba.vhost_remove
(lpath=>'/phpBB3');
db.dba.vhost_define
(
lpath=>'/phpBB3',
ppath=>'/vad/vsp/phpBB3/',
is_dav=>0,
is_brws=>0,
vsp_user=>'dba',
def_page=>'index.php',
opts=>vector ('url_rewrite', 'phpBB3_rule_list')
);
db.dba.vhost_remove
(
vhost=>'*sslini*',
lhost=>'*sslini*',
lpath=>'/phpBB3'
);
db.dba.vhost_define
(
vhost=>'*sslini*',
lhost=>'*sslini*',
lpath=>'/phpBB3',
ppath=>'/vad/vsp/phpBB3/',
is_dav=>0,
is_brws=>0,
vsp_user=>'dba',
def_page=>'index.php',
opts=>VECTOR ('url_rewrite', 'phpBB3_rule_list')
);
DB.DBA.XML_SET_NS_DECL ('phpbb', 'http://openlinksw.com/phpBB3/', 2);
References