RDF View for phpBB 2


use phpBB;


create procedure phpBB2.DBA.phpBB2_foaf_iri (in s varchar)
{
  return concat ('http://xmlns.com/foaf/0.1/', s);
};

create procedure phpBB2.DBA.phpBB2_sioc_iri (in s varchar)
{
  return concat ('http://rdfs.org/sioc/ns#', s);
};

create procedure phpBB2.DBA.phpBB2_rdf_iri (in s varchar)
{
  return concat ('http://www.w3.org/1999/02/22-rdf-syntax-ns#', s);
};

create procedure phpBB2.DBA.phpBB2_rdfs_iri (in s varchar)
{
  return concat ('http://www.w3.org/2000/01/rdf-schema#', s);
};

create procedure phpBB2.DBA.phpBB2_geo_iri (in s varchar)
{
  return concat ('http://www.w3.org/2003/01/geo/wgs84_pos#', s);
};

create procedure phpBB2.DBA.phpBB2_atom_iri (in s varchar)
{
  return concat ('http://atomowl.org/ontologies/atomrdf#', s);
};

create procedure phpBB2.DBA.phpBB2_dc_iri (in s varchar)
{
  return concat ('http://purl.org/dc/elements/1.1/', s);
};

create procedure phpBB2.DBA.phpBB2_dcterms_iri (in s varchar)
{
  return concat ('http://purl.org/dc/terms/', s);
};

create procedure phpBB2.DBA.phpBB2_skos_iri (in s varchar)
{
  return concat ('http://www.w3.org/2004/02/skos/core#', s);
};

create procedure phpBB2.DBA.phpBB2_ext_iri (in s varchar)
{
  return concat ('http://rdfs.org/sioc/types#', s);
};

create procedure phpBB2.DBA.phpBB2_get_opt (in opt varchar)
{
  return (select config_value from phpbb_config where config_name = opt);
};

create procedure phpBB2.DBA.phpBB2_get_graph ()
{
  declare script_name,server_name,server_protocol,server_port varchar;
  script_name := trim(trim(phpBB2.DBA.phpBB2_get_opt('script_path')),'/');
  server_name := trim(phpBB2.DBA.phpBB2_get_opt('server_name'));
  if (cast(phpBB2.DBA.phpBB2_get_opt('cookie_secure') as integer))
    server_protocol := 'https://';
  else
    server_protocol := 'http://';
  if (cast(phpBB2.DBA.phpBB2_get_opt('server_port') as integer) <> 80)
    server_port := ':' || cast(phpBB2.DBA.phpBB2_get_opt('server_port') as varchar) || '/';
  else
    server_port := '/';

  return server_protocol || server_name || server_port || script_name || '/';
};

create procedure phpBB2.DBA.phpBB2_user_iri (in id int)
{
  return phpBB2.DBA.phpBB2_get_graph () || sprintf('profile.php?mode=viewprofile&u=%d',id);
};

create procedure phpBB2.DBA.phpBB2_usergroup_iri (in id int)
{
  return phpBB2.DBA.phpBB2_get_graph () || sprintf('groupcp.php?g=%d',id);
};

create procedure phpBB2.DBA.phpBB2_forum_iri (in id int)
{
  return phpBB2.DBA.phpBB2_get_graph () || sprintf('viewforum.php?f=%d',id);
};

create procedure phpBB2.DBA.phpBB2_post_iri (in id int)
{
  return phpBB2.DBA.phpBB2_get_graph () || sprintf('viewtopic.php?p=%d#%d',id,id);
};


create procedure phpBB2.DBA.phpBB2_rdf_init ()
{
  declare iri, site_iri, graph_iri, forum_iri varchar;
  graph_iri := phpBB2.DBA.phpBB2_get_graph ();
  site_iri  := graph_iri;
  delete from DB.DBA.RDF_QUAD where G = DB.DBA.RDF_IID_OF_QNAME (graph_iri);

  -- Site
  DB.DBA.RDF_QUAD_URI (graph_iri, site_iri, phpBB2.DBA.phpBB2_rdf_iri ('type'), phpBB2.DBA.phpBB2_sioc_iri ('Site'));
  DB.DBA.RDF_QUAD_URI (graph_iri, site_iri, phpBB2.DBA.phpBB2_sioc_iri ('link'), site_iri);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, site_iri, phpBB2.DBA.phpBB2_dc_iri ('title'), phpBB2.DBA.phpBB2_get_opt ('sitename'));

  for (select user_id, username,
	  case when length (user_email) then 'mailto:'||user_email else '' end as E_MAIL,
	  case when length (user_email) then sha1_digest (user_email) else '' end as E_MAIL_SHA1
	  from phpbb_users
	  where user_active = 1)do
	{
	  phpBB2.DBA.phpBB2_user_rdf(user_id,username,E_MAIL,E_MAIL_SHA1);
	};
	
	for (select group_id, group_name from phpbb_groups
    where group_type < 2 and group_single_user = 0) do
  {
	  phpBB2.DBA.phpBB2_usergroups_rdf(group_id, group_name);
  };
  
  for (select group_id, user_id from phpbb_user_group
    where user_pending = 0) do
  {
	  phpBB2.DBA.phpBB2_user_usergroups_rdf(group_id, user_id);
  };
  
  for (select forum_id, forum_name, forum_desc from phpbb_forums )do
  {
	  phpBB2.DBA.phpBB2_forum_rdf(forum_id, forum_name,forum_desc);
  };
  
  for (select forum_id, user_id from phpbb_user_group ug
    INNER JOIN phpbb_auth_access aa on ug.group_id = aa.group_id 
    where ug.user_pending = 0) do
  {
	  phpBB2.DBA.phpBB2_forum_moderators_rdf(forum_id, user_id);
  };
  
  for (select pp.post_id, pp.forum_id, pp.poster_id, pt.post_subject, pt.post_text 
    from phpbb_posts pp
    INNER JOIN phpbb_posts_text pt on pp.post_id = pt.post_id) do
  {
	  phpBB2.DBA.phpBB2_post_rdf(post_id, forum_id,poster_id);
	  phpBB2.DBA.phpBB2_post_text_rdf(post_id, post_subject,post_text);
  };

};

create procedure phpBB2.DBA.phpBB2_post_text_rdf (in post_id int,in post_subject varchar, in post_text varchar)
{
  declare graph_iri,post_iri any;
  graph_iri := phpBB2.DBA.phpBB2_get_graph ();
  post_iri := phpBB2.DBA.phpBB2_post_iri (post_id);
  
  DB.DBA.RDF_QUAD_URI_L (graph_iri, post_iri, phpBB2.DBA.phpBB2_dc_iri ('title'), post_subject);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, post_iri, phpBB2.DBA.phpBB2_sioc_iri ('content'), post_text);
};

create procedure phpBB2.DBA.phpBB2_post_rdf (in post_id int,in forum_id int, in user_id int)
{
  declare graph_iri,post_iri,forum_iri,user_iri any;
  graph_iri := phpBB2.DBA.phpBB2_get_graph ();
  post_iri := phpBB2.DBA.phpBB2_post_iri (post_id);
  forum_iri := phpBB2.DBA.phpBB2_forum_iri (forum_id);
  user_iri := phpBB2.DBA.phpBB2_user_iri (user_id);
  
  DB.DBA.RDF_QUAD_URI (graph_iri, post_iri, phpBB2.DBA.phpBB2_rdf_iri ('type'), phpBB2.DBA.phpBB2_sioc_iri ('Post'));
  DB.DBA.RDF_QUAD_URI (graph_iri, post_iri, phpBB2.DBA.phpBB2_rdf_iri ('type'), phpBB2.DBA.phpBB2_sioc_iri ('Item'));

  DB.DBA.RDF_QUAD_URI_L (graph_iri, post_iri, phpBB2.DBA.phpBB2_sioc_iri ('id'), post_id);
  DB.DBA.RDF_QUAD_URI (graph_iri, post_iri, phpBB2.DBA.phpBB2_sioc_iri ('has_creator'), user_iri);
  DB.DBA.RDF_QUAD_URI (graph_iri, post_iri, phpBB2.DBA.phpBB2_sioc_iri ('has_container'), forum_iri);
  DB.DBA.RDF_QUAD_URI (graph_iri, forum_iri, phpBB2.DBA.phpBB2_sioc_iri ('container_of'), post_iri);

};

create procedure phpBB2.DBA.phpBB2_forum_moderators_rdf (in forum_id int, in user_id int)
{
  declare graph_iri, forum_iri, user_iri any;
  graph_iri := phpBB2.DBA.phpBB2_get_graph ();
  forum_iri := phpBB2.DBA.phpBB2_forum_iri (forum_id);
  user_iri := phpBB2.DBA.phpBB2_user_iri (user_id);
  
  DB.DBA.RDF_QUAD_URI (graph_iri, forum_iri, phpBB2.DBA.phpBB2_sioc_iri ('has_moderator'), user_iri);
  DB.DBA.RDF_QUAD_URI (graph_iri, user_iri, phpBB2.DBA.phpBB2_sioc_iri ('moderator_of'), forum_iri);
};

create procedure phpBB2.DBA.phpBB2_forum_rdf (in forum_id int, in forum_name varchar,in forum_desc varchar)
{
  declare graph_iri, iri, site_iri any;
  graph_iri := phpBB2.DBA.phpBB2_get_graph ();
  site_iri := graph_iri;
  iri := phpBB2.DBA.phpBB2_forum_iri (forum_id);
  
  DB.DBA.RDF_QUAD_URI (graph_iri, iri, phpBB2.DBA.phpBB2_rdf_iri ('type'), phpBB2.DBA.phpBB2_sioc_iri ('Forum'));
  DB.DBA.RDF_QUAD_URI (graph_iri, iri, phpBB2.DBA.phpBB2_rdf_iri ('type'), phpBB2.DBA.phpBB2_sioc_iri ('Container'));

  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('id'), forum_id);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('name'), forum_name);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('description'), forum_desc);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_dc_iri ('title'), forum_name);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_dc_iri ('description'), forum_desc);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('type'), 'Forum');
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('has_host'), site_iri);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, site_iri, phpBB2.DBA.phpBB2_sioc_iri ('host_of'), iri);

};

create procedure phpBB2.DBA.phpBB2_user_usergroups_rdf (in group_id int, in user_id int)
{
  declare graph_iri, group_iri, user_iri any;
  graph_iri := phpBB2.DBA.phpBB2_get_graph ();
  group_iri := phpBB2.DBA.phpBB2_usergroup_iri (group_id);
  user_iri := phpBB2.DBA.phpBB2_user_iri (user_id);
  
  DB.DBA.RDF_QUAD_URI (graph_iri, group_iri, phpBB2.DBA.phpBB2_sioc_iri ('has_member'), user_iri);
  DB.DBA.RDF_QUAD_URI (graph_iri, user_iri, phpBB2.DBA.phpBB2_sioc_iri ('member_of'), group_iri);
};

create procedure phpBB2.DBA.phpBB2_usergroups_rdf (in ID int, in group_name varchar)
{
  declare graph_iri,iri any;
  graph_iri := phpBB2.DBA.phpBB2_get_graph ();
  iri := phpBB2.DBA.phpBB2_usergroup_iri (ID);
  
  DB.DBA.RDF_QUAD_URI (graph_iri, iri, phpBB2.DBA.phpBB2_rdf_iri ('type'), phpBB2.DBA.phpBB2_sioc_iri ('Usergroup'));
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('id'), ID);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('name'), group_name);
};

create procedure phpBB2.DBA.phpBB2_user_rdf (in ID int, in username varchar, in user_email varchar, in sha1_digest varchar)
{
  declare graph_iri,iri any;
  graph_iri := phpBB2.DBA.phpBB2_get_graph ();
  iri := phpBB2.DBA.phpBB2_user_iri (ID);

  DB.DBA.RDF_QUAD_URI (graph_iri, iri, phpBB2.DBA.phpBB2_rdf_iri ('type'), phpBB2.DBA.phpBB2_sioc_iri ('User'));
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('id'), ID);

  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('name'), username);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('email'), user_email);
  DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('email_sha1'), sha1_digest);

};

create procedure phpBB2.DBA.phpBB2_delete_rdf_obj (in iri any)
{
  declare graph_iri any;
  graph_iri := DB.DBA.RDF_IID_OF_QNAME (phpBB2.DBA.phpBB2_get_graph ());
  iri := DB.DBA.RDF_IID_OF_QNAME (iri);
  delete from DB.DBA.RDF_QUAD where G = graph_iri and S = iri;
  delete from DB.DBA.RDF_QUAD where G = graph_iri and O = iri;
};

create procedure phpBB2.DBA.phpBB2_delete_rdf_obj_link (in iri1 any,in iri2 any)
{
  declare graph_iri any;
  graph_iri := DB.DBA.RDF_IID_OF_QNAME (phpBB2.DBA.phpBB2_get_graph ());
  iri1 := DB.DBA.RDF_IID_OF_QNAME (iri1);
  iri2 := DB.DBA.RDF_IID_OF_QNAME (iri2);
  delete from DB.DBA.RDF_QUAD where G = graph_iri and S = iri1 and O = iri2;
  delete from DB.DBA.RDF_QUAD where G = graph_iri and S = iri2 and O = iri1;
};

create trigger phpbb_posts_text_i after insert on phpbb_posts_text referencing new as N
{
	  phpBB2.DBA.phpBB2_post_text_rdf(N.post_id, N.post_subject, N.post_text);
};

create trigger phpbb_posts_text_u after update on phpbb_posts_text referencing old as O, new as N
{
  declare graph_iri, post_iri any;
  graph_iri := DB.DBA.RDF_IID_OF_QNAME(phpBB2.DBA.phpBB2_get_graph ());
  post_iri := DB.DBA.RDF_IID_OF_QNAME(phpBB2.DBA.phpBB2_post_iri (O.post_id));
  
  if (O.post_subject <> N.post_subject)
  {
    declare o_short any;
    o_short := DB.DBA.RDF_OBJ_OF_SQLVAL(O.post_subject);
    delete from DB.DBA.RDF_QUAD where G = graph_iri and S = post_iri and O = o_short;
    DB.DBA.RDF_QUAD_URI_L (graph_iri, post_iri, phpBB2.DBA.phpBB2_dc_iri ('title'), N.post_subject);

  }

  if (O.post_text <> N.post_text)
  {
    declare o_short any;
    o_short := DB.DBA.RDF_OBJ_OF_SQLVAL(O.post_text);
    delete from DB.DBA.RDF_QUAD where G = graph_iri and S = post_iri and O = o_short;
    DB.DBA.RDF_QUAD_URI_L (graph_iri, post_iri, phpBB2.DBA.phpBB2_sioc_iri ('content'), N.post_text);
  }
  
};

create trigger phpbb_posts_text_d after delete on phpbb_posts_text referencing old as O
{
  phpBB2.DBA.phpBB2_delete_rdf_obj (phpBB2.DBA.phpBB2_post_iri (O.post_id));
};


create trigger phpbb_posts_i after insert on phpbb_posts referencing new as N
{
  if (isinteger(N.post_id))
	  phpBB2.DBA.phpBB2_post_rdf(N.post_id, N.forum_id,N.poster_id);
};

create trigger phpbb_posts_u after update on phpbb_posts referencing old as O, new as N
{
  declare graph_iri, post_iri any;
  graph_iri := DB.DBA.RDF_IID_OF_QNAME(phpBB2.DBA.phpBB2_get_graph ());
  post_iri := DB.DBA.RDF_IID_OF_QNAME(phpBB2.DBA.phpBB2_post_iri (O.post_id));
  
  if (O.forum_id <> N.forum_id)
  {
    declare forum_iri any;
    forum_iri := DB.DBA.RDF_IID_OF_QNAME(phpBB2.DBA.phpBB2_forum_iri (O.forum_id));
    delete from DB.DBA.RDF_QUAD where G = graph_iri and S = post_iri and O = forum_iri;
    forum_iri := phpBB2.DBA.phpBB2_forum_iri (N.forum_id);
    DB.DBA.RDF_QUAD_URI (graph_iri, post_iri, phpBB2.DBA.phpBB2_sioc_iri ('has_container'), forum_iri);
    DB.DBA.RDF_QUAD_URI (graph_iri, forum_iri, phpBB2.DBA.phpBB2_sioc_iri ('container_of'), post_iri);
  }

  if (O.poster_id <> N.poster_id)
  {
    declare user_iri any;
    user_iri := DB.DBA.RDF_IID_OF_QNAME(phpBB2.DBA.phpBB2_user_iri (O.poster_id));
    delete from DB.DBA.RDF_QUAD where G = graph_iri and S = post_iri and O = user_iri;
    user_iri := phpBB2.DBA.phpBB2_user_iri (N.poster_id);
    DB.DBA.RDF_QUAD_URI (graph_iri, post_iri, phpBB2.DBA.phpBB2_sioc_iri ('has_creator'), user_iri);
  }

  
};

create trigger phpbb_posts_d after delete on phpbb_posts referencing old as O
{
  phpBB2.DBA.phpBB2_delete_rdf_obj (phpBB2.DBA.phpBB2_post_iri (O.post_id));
};


create trigger phpbb_user_forum_i after insert on phpbb_forums referencing new as N
{
	  phpBB2.DBA.phpBB2_forum_rdf(N.forum_id, N.forum_name,N.forum_desc);
};

create trigger phpbb_forums_u after update on phpbb_forums referencing old as O, new as N
{
  --phpBB2.DBA.phpBB2_delete_rdf_obj (phpBB2.DBA.phpBB2_forum_iri (O.forum_id));
  --phpBB2.DBA.phpBB2_forum_rdf(N.forum_id, N.forum_name,N.forum_desc);

  declare graph_iri, iri any;
  graph_iri := DB.DBA.RDF_IID_OF_QNAME(phpBB2.DBA.phpBB2_get_graph ());
  iri := DB.DBA.RDF_IID_OF_QNAME(phpBB2.DBA.phpBB2_forum_iri (O.forum_id));
  
  if (O.forum_name <> N.forum_name)
  {
    declare o_short any;
    o_short := DB.DBA.RDF_OBJ_OF_SQLVAL(O.forum_name);
    delete from DB.DBA.RDF_QUAD where G = graph_iri and S = iri and O = o_short;
    DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('name'), N.forum_name);
    DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_dc_iri ('title'), N.forum_name);
  }

  if (O.forum_desc <> N.forum_desc)
  {
    declare o_short any;
    o_short := DB.DBA.RDF_OBJ_OF_SQLVAL(O.forum_desc);
    delete from DB.DBA.RDF_QUAD where G = graph_iri and S = iri and O = o_short;
    DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_sioc_iri ('description'), N.forum_desc);
    DB.DBA.RDF_QUAD_URI_L (graph_iri, iri, phpBB2.DBA.phpBB2_dc_iri ('description'), N.forum_desc);
  }

  
};

create trigger phpbb_forums_d after delete on phpbb_forums referencing old as O
{
  phpBB2.DBA.phpBB2_delete_rdf_obj (phpBB2.DBA.phpBB2_forum_iri (O.forum_id));
};


create trigger phpbb_user_group_i after insert on phpbb_user_group referencing new as N
{
  if (N.user_pending = 0)
  {
	  phpBB2.DBA.phpBB2_user_usergroups_rdf(N.group_id, N.user_id);
	};
	
	for (select forum_id from phpbb_auth_access aa where N.group_id = aa.group_id)do
	{
	  phpBB2.DBA.phpBB2_forum_moderators_rdf(forum_id, N.user_id);
	};
};

create trigger phpbb_user_group_u after update on phpbb_user_group referencing old as O, new as N
{
  phpBB2.DBA.phpBB2_delete_rdf_obj_link(phpBB2.DBA.phpBB2_usergroup_iri (O.group_id), phpBB2.DBA.phpBB2_user_iri (O.user_id));
  if (N.user_pending = 0)
	  phpBB2.DBA.phpBB2_user_usergroups_rdf(N.group_id, N.user_id);

	for (select forum_id from phpbb_auth_access aa where O.group_id = aa.group_id)do
	{
    phpBB2.DBA.phpBB2_delete_rdf_obj_link(phpBB2.DBA.phpBB2_forum_iri (forum_id), phpBB2.DBA.phpBB2_user_iri (O.user_id));
  }
	for (select forum_id from phpbb_auth_access aa where N.group_id = aa.group_id)do
	{
	  phpBB2.DBA.phpBB2_forum_moderators_rdf(forum_id, N.user_id);
	};
};

create trigger phpbb_user_group_d after delete on phpbb_user_group referencing old as O
{
  phpBB2.DBA.phpBB2_delete_rdf_obj_link(phpBB2.DBA.phpBB2_usergroup_iri (O.group_id), phpBB2.DBA.phpBB2_user_iri (O.user_id));
	for (select forum_id from phpbb_auth_access aa where O.group_id = aa.group_id)do
	{
    phpBB2.DBA.phpBB2_delete_rdf_obj_link(phpBB2.DBA.phpBB2_forum_iri (forum_id), phpBB2.DBA.phpBB2_user_iri (O.user_id));
  }
};


create trigger phpbb_auth_access_i after insert on phpbb_auth_access referencing new as N
{
	for (select user_id from phpbb_user_group ug where ug.group_id = N.group_id)do
	{
	  phpBB2.DBA.phpBB2_forum_moderators_rdf(N.forum_id, user_id);
	};
};

create trigger phpbb_auth_access_u after update on phpbb_auth_access referencing old as O, new as N
{

	for (select user_id from phpbb_user_group ug where ug.group_id = O.group_id)do
	{
    phpBB2.DBA.phpBB2_delete_rdf_obj_link(phpBB2.DBA.phpBB2_forum_iri (O.forum_id), phpBB2.DBA.phpBB2_user_iri (user_id));
  }
	for (select user_id from phpbb_user_group ug where ug.group_id = N.group_id)do
	{
	  phpBB2.DBA.phpBB2_forum_moderators_rdf(N.forum_id, user_id);
	};
};

create trigger phpbb_auth_access_d after delete on phpbb_auth_access referencing old as O
{
	for (select user_id from phpbb_user_group ug where ug.group_id = O.group_id)do
	{
    phpBB2.DBA.phpBB2_delete_rdf_obj_link(phpBB2.DBA.phpBB2_forum_iri (O.forum_id), phpBB2.DBA.phpBB2_user_iri (user_id));
  }
};


create trigger phpbb_groups_i after insert on phpbb_groups referencing new as N
{
  if (N.group_type < 2 and N.group_single_user = 0)
	  phpBB2.DBA.phpBB2_usergroups_rdf(N.group_id, N.group_name);
};

create trigger phpbb_groups_u after update on phpbb_groups referencing old as O, new as N
{
  phpBB2.DBA.phpBB2_delete_rdf_obj (phpBB2.DBA.phpBB2_usergroup_iri (O.group_id));
  if (N.group_type < 2 and N.group_single_user = 0)
	  phpBB2.DBA.phpBB2_usergroups_rdf(N.group_id, N.group_name);

  for (select group_id, user_id from phpbb_user_group
    where user_pending = 0 and group_id = N.group_id) do
  {
	  phpBB2.DBA.phpBB2_user_usergroups_rdf(group_id, user_id);
  };
};

create trigger phpbb_groups_d after delete on phpbb_groups referencing old as O
{
  phpBB2.DBA.phpBB2_delete_rdf_obj (phpBB2.DBA.phpBB2_usergroup_iri (O.group_id));
};


create trigger phpbb_users_i after insert on phpbb_users referencing new as N
{
  if (N.user_active = 1)
	  phpBB2.DBA.phpBB2_user_rdf(N.user_id,N.username,
	    (case when length (N.user_email) then 'mailto:'||N.user_email else '' end),
	    (case when length (N.user_email) then sha1_digest (N.user_email) else '' end)
	  );
};

create trigger phpbb_users_u after update on phpbb_users referencing old as O, new as N
{
  phpBB2.DBA.phpBB2_delete_rdf_obj (phpBB2.DBA.phpBB2_user_iri (O.user_id));
  if (N.user_active = 1)
	  phpBB2.DBA.phpBB2_user_rdf(N.user_id,N.username,
	    (case when length (N.user_email) then 'mailto:'||N.user_email else '' end),
	    (case when length (N.user_email) then sha1_digest (N.user_email) else '' end)
	  );
};

create trigger phpbb_users_d after delete on phpbb_users referencing old as O
{
  phpBB2.DBA.phpBB2_delete_rdf_obj (phpBB2.DBA.phpBB2_user_iri (O.user_id));
};


create trigger phpbb_config_u after update on phpbb_config referencing old as O, new as N
{
  if (O.config_value = N.config_value)
    return;
  if (O.config_name = 'script_path' or
      O.config_name = 'server_name' or 
      O.config_name = 'cookie_secure' or
      O.config_name = 'server_port' )
    {
      declare script_name,server_name,server_protocol,server_port varchar;

      if (O.config_name = 'script_path')
        script_name := trim(trim(O.config_value),'/');
      else
        script_name := trim(trim(phpBB2.DBA.phpBB2_get_opt('script_path')),'/');
      
      if (O.config_name = 'server_name')
        server_name := trim(O.config_value);
      else 
        server_name := trim(phpBB2.DBA.phpBB2_get_opt('server_name'));
      
      if (O.config_name = 'cookie_secure')
      {
        if (cast(O.config_value as integer)) 
          server_protocol := 'https://'; 
        else 
          server_protocol := 'http://';
      } else { 
        if (cast(phpBB2.DBA.phpBB2_get_opt('cookie_secure') as integer)) 
          server_protocol := 'https://'; 
        else 
          server_protocol := 'http://';
      };
      
      if (O.config_name = 'server_port')
      {
        if (cast(O.config_value as integer) <> 80)
          server_port := ':' || cast(O.config_value as varchar) || '/';
        else
          server_port := '/';
      } else {
        if (cast(phpBB2.DBA.phpBB2_get_opt('server_port') as integer) <> 80)
          server_port := ':' || cast(phpBB2.DBA.phpBB2_get_opt('server_port') as varchar) || '/';
        else
          server_port := '/';
      };
    
      declare oiri, niri any;
      oiri := DB.DBA.RDF_IID_OF_QNAME (server_protocol || server_name || server_port || script_name || '/');
      niri := DB.DBA.RDF_MAKE_IID_OF_QNAME (phpBB2.DBA.phpBB2_get_graph ());
      update DB.DBA.RDF_QUAD set G = niri where G = oiri;
      update DB.DBA.RDF_QUAD set S = niri where G = niri and S = oiri;
      update DB.DBA.RDF_QUAD set O = niri where G = niri and O = oiri;
    }
  else if (O.config_name = 'sitename')
    {
      declare iri, graph_iri, pred any;
      graph_iri := DB.DBA.RDF_IID_OF_QNAME (phpBB2.DBA.phpBB2_get_graph ());
      iri := graph_iri;
      pred := DB.DBA.RDF_IID_OF_QNAME (phpBB2.DBA.phpBB2_dc_iri ('title'));
      update DB.DBA.RDF_QUAD set O = DB.DBA.RDF_MAKE_OBJ_OF_SQLVAL (N.config_value)
	  where G = graph_iri and S = iri and P = pred and O = DB.DBA.RDF_MAKE_OBJ_OF_SQLVAL (O.config_value);
    }
};

phpBB2.DBA.phpBB2_rdf_init ();

-- RDF Views definition

drop view phpBB2.DBA.phpBB2_site_rdf;
drop view phpBB2.DBA.phpBB2_users_rdf;
drop view phpBB2.DBA.phpBB2_forums_rdf;
drop view phpBB2.DBA.phpBB2_usergroups_rdf;
drop view phpBB2.DBA.phpBB2_user_usergroups_rdf;
drop view phpBB2.DBA.phpBB2_forum_moderators_rdf;
drop view phpBB2.DBA.phpBB2_posts_rdf;
drop view phpBB2.DBA.phpBB2_forum_posts_rdf;

create view phpBB2.DBA.phpBB2_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 phpBB2.DBA.phpBB2_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
	from phpbb_users
	where user_active = 1;

create view phpBB2.DBA.phpBB2_usergroups_rdf as select group_id, group_name from phpbb_groups
  where group_type < 2 and group_single_user = 0;

create view phpBB2.DBA.phpBB2_user_usergroups_rdf as select group_id, user_id from phpbb_user_group
  where user_pending = 0;

create view phpBB2.DBA.phpBB2_forums_rdf as select
	forum_id,
	forum_name,
	forum_desc,
	'Forum' as forum_type,
	'' as WS_DUMMY
	from phpbb_forums ;

create view phpBB2.DBA.phpBB2_forum_moderators_rdf as 
  select forum_id, user_id 
  from phpbb_user_group ug
  INNER JOIN phpbb_auth_access aa on ug.group_id = aa.group_id 
  where ug.user_pending = 0;

create view phpBB2.DBA.phpBB2_posts_rdf as 
  select pp.post_id,
         pp.forum_id,
         pp.poster_id,
         pt.post_subject,
         pt.post_text 
  from phpbb_posts pp
  INNER JOIN phpbb_posts_text pt on pp.post_id = pt.post_id;

create view phpBB2.DBA.phpBB2_forum_posts_rdf as 
  select post_id,
         forum_id
  from phpbb_posts;

exec (
    string_output_string (sparql_to_sql_text (
'drop quad map graph iri("http://^{URIQADefaultHost}^/phpBB2_v/") .')));

--exec (
--    string_output_string (sparql_to_sql_text (
--'drop quad map virtrdf:phpBB2 .')));

--exec (
--    string_output_string (sparql_to_sql_text (
--'
--drop quad storage virtrdf:phpBB2 .
--')));

exec (
    string_output_string (sparql_to_sql_text (
'
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 phpbbsioc: <http://openlinksw.com/phpBB/sioc/>

#create quad storage virtrdf:phpBB2 {
#    create virtrdf:DefaultQuadMap using storage virtrdf:DefaultQuadStorage .
#  } .


create iri class phpbbsioc:site "http://^{URIQADefaultHost}^/phpBB2/%U" (in dummy varchar not null) .
create iri class phpbbsioc:user_iri "http://^{URIQADefaultHost}^/phpBB2/profile.php?mode=viewprofile&u=%d" (in user_id integer not null) .
create iri class phpbbsioc:usergroup_iri "http://^{URIQADefaultHost}^/phpBB2/groupcp.php?g=%d" (in group_id integer not null) .
create iri class phpbbsioc:forum_iri "http://^{URIQADefaultHost}^/phpBB2/viewforum.php?f=%d" (in forum_id integer not null) .
create iri class phpbbsioc:post_iri "http://^{URIQADefaultHost}^/phpBB2/viewtopic.php?p=%d#%d" (in forum_id integer not null,in forum_id integer not null) .

#alter quad storage virtrdf:phpBB2
alter quad storage virtrdf:DefaultQuadStorage
  {
    create virtrdf:phpBB2 as graph iri ("http://^{URIQADefaultHost}^/phpBB2_v/")
      {

  # Default phpBB Site
  phpbbsioc:site (phpBB2.DBA.phpBB2_site_rdf.WS_DUMMY) a sioc:Site ;
  dc:title WEB_TITLE .

  # User
  phpbbsioc:user_iri (phpBB2.DBA.phpBB2_users_rdf.user_id)
  a sioc:User option (EXCLUSIVE);
  sioc:id user_id ;
  sioc:name username ;
  sioc:email E_MAIL ;
  sioc:email_sha1 E_MAIL_SHA1 .

  # Usergroup
  phpbbsioc:usergroup_iri (phpBB2.DBA.phpBB2_usergroups_rdf.group_id) 
  a sioc:Usergroup option (EXCLUSIVE);
  sioc:id group_id ;
  sioc:name group_name .
      
  # Usergroup - USER ralationship
  phpbbsioc:usergroup_iri (phpBB2.DBA.phpBB2_user_usergroups_rdf.group_id) 
  sioc:has_member phpbbsioc:user_iri (user_id) .

  # USER - Usergroup ralationship
  phpbbsioc:user_iri (phpBB2.DBA.phpBB2_user_usergroups_rdf.user_id) 
  sioc:member_of phpbbsioc:usergroup_iri(group_id) .

  # Forum
  phpbbsioc:forum_iri (phpBB2.DBA.phpBB2_forums_rdf.forum_id)
  a sioc:Forum option (EXCLUSIVE);
  a sioc:Container option (EXCLUSIVE);
  sioc:id forum_id ;
  sioc:name forum_name ;
  sioc:description forum_desc ;
  dc:title forum_name ;
  dc:description forum_desc ;
  sioc:type forum_type ;
  sioc:has_host phpbbsioc:site(WS_DUMMY) .
       
  # Forum - moderators ralationship
  phpbbsioc:user_iri (phpBB2.DBA.phpBB2_forum_moderators_rdf.user_id) 
  sioc:moderator_of phpbbsioc:forum_iri(forum_id) .

  # Site - Forum ralationship
  phpbbsioc:site (phpBB2.DBA.phpBB2_forums_rdf.WS_DUMMY) 
  sioc:host_of phpbbsioc:forum_iri(forum_id) .

  # Moderators - Forum ralationship
  phpbbsioc:forum_iri (phpBB2.DBA.phpBB2_forum_moderators_rdf.forum_id) 
  sioc:has_moderator phpbbsioc:user_iri(user_id) .

  # Post
  phpbbsioc:post_iri (phpBB2.DBA.phpBB2_posts_rdf.post_id,phpBB2.DBA.phpBB2_posts_rdf.post_id)
  a sioc:Post option (EXCLUSIVE);
  a sioc:Item option (EXCLUSIVE);
  sioc:id post_id ;
  sioc:has_container phpbbsioc:forum_iri(forum_id) ;
  dc:title post_subject ;
  sioc:has_creator phpbbsioc:user_iri (poster_id) ;
  sioc:content post_text .

  # Forum - post
  phpbbsioc:forum_iri (phpBB2.DBA.phpBB2_forum_posts_rdf.forum_id) 
  sioc:container_of phpbbsioc:post_iri(post_id,post_id) .

      }
  } .
')));

grant select on phpBB2.DBA.phpBB2_site_rdf to "SPARQL";
grant select on phpBB2.DBA.phpBB2_users_rdf to "SPARQL";
grant select on phpBB2.DBA.phpBB2_usergroups_rdf to "SPARQL";
grant select on phpBB2.DBA.phpBB2_user_usergroups_rdf to "SPARQL";
grant select on phpBB2.DBA.phpBB2_forums_rdf to "SPARQL";
grant select on phpBB2.DBA.phpBB2_forum_moderators_rdf to "SPARQL";
grant select on phpBB2.DBA.phpBB2_posts_rdf to "SPARQL";
grant select on phpBB2.DBA.phpBB2_forum_posts_rdf to "SPARQL";

DB.DBA.XML_SET_NS_DECL ('phpbbsioc', 'http://openlinksw.com/phpBB/sioc/', 2);

CategoryVirtuoso CategoryRDF