VOS.VirtRDFViewPHPBB3Script

  • Topic
  • Discussion
  • VOS.VirtRDFViewPHPBB3Script(Last) -- DAVWikiAdmin? , 2017-06-29 07:39:50 Edit WebDAV System Administrator 2017-06-29 07:39:50

    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