---++phpBB 3 Linked Data Views Script to set up your own instance

<verbatim>
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);
</verbatim>

---+++References
   * [[PHPBB3SIOCRef][phpBB 3 SIOC Reference]]
   * [[http://ods.openlinksw.com/dataspace/dav/wiki/ODS/OdsIntegrationphpBB][phpBB 3 Installation Guide]]