RDF View for phpBB 3

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,
	 case when length (user_email) then 'mailto:'||user_email else null end as E_MAIL,
	 case when length (user_email) then sha1_digest (user_email) else null end as E_MAIL_SHA1,
	 '' 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 forum_id,
	 forum_name,
	 forum_desc,
	 'Forum' as forum_type,
	 '' as WS_DUMMY
  from phpbb_forums;

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 where pp.poster_id = u.user_id;

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:email E_MAIL ;
	sioc:email_sha1 E_MAIL_SHA1 ;
	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);