On demand blogs using WordPress Multisite and stored procedures

Since I’m in a giving mood, I thought I would share the stored procedures I have employed that allows the people here at the Department of Computer Science to use a self service interface to create their own blogs. We have a database that knows who everyone is, what projects they are on, what research groups they are in, and so it follows that they can be offered blogs based on this information.
It is always a good idea to take yourself out of the loop where possible: there is no point making more work for yourself or being constantly interrupted when it is not necessary. So rather than me having to sign in to the WordPress administration site and input all the details each and every time someone whats a blog, I thought I had better create a self service interface.
It shouldn’t be too bad, surely WordPress is using basic database principles and therefore it should be a case of inserting and updating a few rows in the nicely normalized tables? Actually no… Under the hood WordPress is rather ugly. The most surprising ugliness is its use of entirely new sets of tables for each blog. So to create a blog, you have to create a whole bunch of tables. There is also a whole load of settings packed into strings which are usually created by the php code at blog creation time and it trips up if they are not there.
The solution I have used is to create a template blog using the interface, with all the default plug-ins and settings and interface set up. I can then duplicate this blog whenever I want to create a new one.

Here it is:


DROP PROCEDURE IF EXISTS wp_createUserAndBlog;

delimiter //

create procedure wp_createUserAndBlog(
		IN firstName_in varchar(255),
		IN lastName_in VARCHAR(255),
		IN username_in VARCHAR(50),
		IN email_in VARCHAR(255),
		IN homepage_in VARCHAR(100),
		IN siteid_in bigint(20),
		IN path_in VARCHAR(100), -- NOTE put on a trailing '/'
		IN template_blog_id_in bigint(20),-- a blog to use as the set up template
		IN blog_name_in longtext,
		IN blog_description_in longtext,
		IN user_blog_role varchar(20)
	)
  main:BEGIN
  
  declare blog_id_var bigint(20) default null;
  declare websitedomain_var varchar(200) default null;
  declare websitepath_var varchar(100) default null;
  declare fullpath_var varchar(100) default null;
  declare siteurl_var varchar(255) default null;
  declare user_id_var bigint(20) unsigned default null;
  declare display_name_var varchar(255) default null;
  declare http_authentication_options longtext;
  
  
  SELECT domain,path INTO websitedomain_var,websitepath_var FROM wp_site WHERE id=siteid_in;
  
  IF websitedomain_var is null THEN
  
  LEAVE main;
  
  END IF;
  
  set fullpath_var = CONCAT(websitepath_var,path_in);
  set siteurl_var = CONCAT('http://',websitedomain_var,fullpath_var);
  
-- ensure the blog exists
  
  select blog_id INTO blog_id_var from wp_blogs where path = fullpath_var;
  
  IF blog_id_var is null
  
  THEN
  
-- create the blog record and set the blog_id_var
  
  INSERT INTO wp_blogs (site_id,domain,path,registered) values (siteid_in,websitedomain_var,fullpath_var,NOW());
  
  select blog_id INTO blog_id_var from wp_blogs where path = fullpath_var;
  
-- create the tables
  
  call wp_createBlogTables(blog_id_var);
  
-- copy the relevant information into the tables
  
  call wp_copyTemplate(template_blog_id_in, blog_id_var);
  
-- admin_email, blogdescription, blogname, home, siteurl, upload_path
  
  call wp_initOptions(blog_id_var,'admin_email',email_in);
  call wp_initOptions(blog_id_var,'blogname',blog_name_in);
  call wp_initOptions(blog_id_var,'blogdescription',blog_description_in);
  call wp_initOptions(blog_id_var,'home',siteurl_var);
  call wp_initOptions(blog_id_var,'siteurl',siteurl_var);
  call wp_initOptions(blog_id_var,'upload_path',CONCAT('wp-content/blogs.dir/',blog_id_var,'/files'));
  call wp_initOptions(blog_id_var,'fileupload_url',CONCAT(siteurl_var,'files'));
  
  END IF;
  
  set @login_uri = CONCAT('https://',websitedomain_var,fullpath_var,'wp-login.php');
  set @logout_uri = CONCAT('https://',websitedomain_var,fullpath_var,'wp-login.php?action=logout');
  
  set http_authentication_options = CONCAT('a:7:{s:13:\"allow_wp_auth\";b:0;s:10:\"auth_label\";s:19:\"HTTP authentication\";s:9:\"login_uri\";s:',Length(@login_uri),':\"",@login_uri,"\";s:10:\"logout_uri\";s:',Length(@logout_uri),':\"",@logout_uri,"\";s:16:\"auto_create_user\";b:0;s:24:\"auto_create_email_domain\";s:0:\"\";s:10:\"db_version\";i:1;}');

  call wp_initOptions(blog_id_var,'http_authentication_options',http_authentication_options);
  
-- so now we have a blog_id
  
-- ensure that we have the user set up for this blog
set display_name_var = CONCAT(firstName_in,' ',lastName_in);
  SELECT ID INTO user_id_var FROM wp_users WHERE user_login=username_in;
  
  IF user_id_var is null
  
  THEN
  
-- create the user record and set the user_id_var
  
  INSERT INTO wp_users (user_login,user_pass,user_nicename,user_email,user_url,user_registered,display_name) values (username_in,MD5(RAND()),username_in,email_in,homepage_in,NOW(),display_name_var);
  
  select ID INTO user_id_var from wp_users where user_login=username_in;
  
  ELSE
  
  UPDATE wp_users set user_email=email_in, display_name=display_name_var where user_login=username_in;
  
  END IF;
  
-- now we have a user record

-- ensure that the user is added to the blog

call wp_ensureUserMeta(user_id_var,'first_name',firstName_in);
call wp_ensureUserMeta(user_id_var,'last_name',lastName_in);
call wp_ensureUserMeta(user_id_var,'nickname',display_name_var);
call wp_ensureUserMeta(user_id_var,'description','');
call wp_initUserMeta(user_id_var,'rich_editing','true');
call wp_initUserMeta(user_id_var,'comment_shortcuts','false');
call wp_initUserMeta(user_id_var,'admin_color','fresh');
call wp_initUserMeta(user_id_var,'use_ssl','0');
call wp_initUserMeta(user_id_var,'show_admin_bar_front','true');
call wp_initUserMeta(user_id_var,'show_admin_bar_admin','true');
call wp_initUserMeta(user_id_var,'aim','');
call wp_initUserMeta(user_id_var,'yim','');
call wp_initUserMeta(user_id_var,'jabber','');
call wp_initUserMeta(user_id_var,'primary_blog',blog_id_var);
call wp_initUserMeta(user_id_var,'source_domain',websitedomain_var);
call wp_initUserMeta(user_id_var,'wp_dashboard_quick_press_last_post_id','1');
call wp_initUserMeta(user_id_var,'wp_user-settings','m1=o&m0=o');
call wp_initUserMeta(user_id_var,'wp_user-settings-time','1310036172');
call wp_initUserMeta(user_id_var,'meta-box-order_dashboard','a:4:{s:6:"normal";s:44:"dashboard_right_now,dashboard_incoming_links";s:4:"side";s:83:"dashboard_quick_press,dashboard_recent_drafts,dashboard_primary,dashboard_secondary";s:7:"column3";s:0:"";s:7:"column4";s:0:"";}');
call wp_initUserMeta(user_id_var,'screen_layout_dashboard','2');
call wp_initUserMeta(user_id_var,'closedpostboxes_dashboard','a:0:{}');
call wp_initUserMeta(user_id_var,'metaboxhidden_dashboard','a:0:{}');

-- editor or administrator

IF user_blog_role = 'administrator' THEN

call wp_ensureUserMeta(user_id_var,CONCAT('wp_',blog_id_var,'_capabilities'),'a:1:{s:13:"administrator";s:1:"1";}');
call wp_ensureUserMeta(user_id_var,CONCAT('wp_',blog_id_var,'_user_level'),'10');

ELSE

call wp_ensureUserMeta(user_id_var,CONCAT('wp_',blog_id_var,'_capabilities'),'a:1:{s:6:"editor";s:1:"1";}');
call wp_ensureUserMeta(user_id_var,CONCAT('wp_',blog_id_var,'_user_level'),'7');

END IF;

call wp_initUserMeta(user_id_var,CONCAT('wp_',blog_id_var,'_user-settings'),'m1=o&m0=o');
call wp_initUserMeta(user_id_var,CONCAT('wp_',blog_id_var,'_user-settings-time'),'1310036172');

  END//

delimiter ;


DROP PROCEDURE IF EXISTS wp_ensureUserMeta;

delimiter //

create procedure wp_ensureUserMeta(
		IN user_id_in bigint(20) unsigned,
		IN meta_key_in VARCHAR(255),
		IN meta_value_in longtext
	)
  
  main:BEGIN
  declare umeta_id_var  bigint(20) unsigned default null;
	
  SELECT umeta_id INTO umeta_id_var FROM wp_usermeta WHERE meta_key=meta_key_in AND user_id=user_id_in;
  
  IF umeta_id_var is null
  
  THEN
  
  INSERT INTO wp_usermeta (user_id,meta_key,meta_value) values (user_id_in,meta_key_in,meta_value_in);
 
  ELSE
  
  UPDATE wp_usermeta set meta_value=meta_value_in where umeta_id=umeta_id_var;
 
  END IF;
 
 
 END//

delimiter ;

DROP PROCEDURE IF EXISTS wp_initUserMeta;

delimiter //

create procedure wp_initUserMeta(
		IN user_id_in bigint(20) unsigned,
		IN meta_key_in VARCHAR(255),
		IN meta_value_in longtext
	)
  
  main:BEGIN
  declare umeta_id_var  bigint(20) unsigned default null;
	
  SELECT umeta_id INTO umeta_id_var FROM wp_usermeta WHERE meta_key=meta_key_in AND user_id=user_id_in;
 
  IF umeta_id_var is null
  
  THEN
  
  INSERT INTO wp_usermeta (user_id,meta_key,meta_value) values (user_id_in,meta_key_in,meta_value_in);
 
  END IF;
 
 
 END//

delimiter ;


DROP PROCEDURE IF EXISTS wp_createBlogTables;

delimiter //

create procedure wp_createBlogTables(
		IN blog_id_in bigint(20)
	)
  
  main:BEGIN
  
  set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_increment,
  `comment_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;
  
  set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL auto_increment,
  `comment_post_ID` bigint(20) unsigned NOT NULL default '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL default '',
  `comment_author_url` varchar(200) NOT NULL default '',
  `comment_author_IP` varchar(100) NOT NULL default '',
  `comment_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL default '0',
  `comment_approved` varchar(20) NOT NULL default '1',
  `comment_agent` varchar(255) NOT NULL default '',
  `comment_type` varchar(20) NOT NULL default '',
  `comment_parent` bigint(20) unsigned NOT NULL default '0',
  `user_id` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`comment_ID`),
  KEY `comment_approved` (`comment_approved`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;

  set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_links` (
  `link_id` bigint(20) unsigned NOT NULL auto_increment,
  `link_url` varchar(255) NOT NULL default '',
  `link_name` varchar(255) NOT NULL default '',
  `link_image` varchar(255) NOT NULL default '',
  `link_target` varchar(25) NOT NULL default '',
  `link_description` varchar(255) NOT NULL default '',
  `link_visible` varchar(20) NOT NULL default 'Y',
  `link_owner` bigint(20) unsigned NOT NULL default '1',
  `link_rating` int(11) NOT NULL default '0',
  `link_updated` datetime NOT NULL default '0000-00-00 00:00:00',
  `link_rel` varchar(255) NOT NULL default '',
  `link_notes` mediumtext NOT NULL,
  `link_rss` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`link_id`),
  KEY `link_visible` (`link_visible`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;
 
 set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_options` (
  `option_id` bigint(20) unsigned NOT NULL auto_increment,
  `blog_id` int(11) NOT NULL default '0',
  `option_name` varchar(64) NOT NULL default '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL default 'yes',
  PRIMARY KEY  (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=117 DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;
 
 set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_increment,
  `post_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;
 
 set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_author` bigint(20) unsigned NOT NULL default '0',
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL default 'publish',
  `comment_status` varchar(20) NOT NULL default 'open',
  `ping_status` varchar(20) NOT NULL default 'open',
  `post_password` varchar(20) NOT NULL default '',
  `post_name` varchar(200) NOT NULL default '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL default '0',
  `guid` varchar(255) NOT NULL default '',
  `menu_order` int(11) NOT NULL default '0',
  `post_type` varchar(20) NOT NULL default 'post',
  `post_mime_type` varchar(100) NOT NULL default '',
  `comment_count` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;
 
 set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_term_relationships` (
  `object_id` bigint(20) unsigned NOT NULL default '0',
  `term_taxonomy_id` bigint(20) unsigned NOT NULL default '0',
  `term_order` int(11) NOT NULL default '0',
  PRIMARY KEY  (`object_id`,`term_taxonomy_id`),
  KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;

set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_term_taxonomy` (
  `term_taxonomy_id` bigint(20) unsigned NOT NULL auto_increment,
  `term_id` bigint(20) unsigned NOT NULL default '0',
  `taxonomy` varchar(32) NOT NULL default '',
  `description` longtext NOT NULL,
  `parent` bigint(20) unsigned NOT NULL default '0',
  `count` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`term_taxonomy_id`),
  UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
  KEY `taxonomy` (`taxonomy`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;


set @sql=CONCAT("CREATE TABLE IF NOT EXISTS `wp_",blog_id_in,"_terms` (
  `term_id` bigint(20) unsigned NOT NULL auto_increment,
  `name` varchar(200) NOT NULL default '',
  `slug` varchar(200) NOT NULL default '',
  `term_group` bigint(10) NOT NULL default '0',
  PRIMARY KEY  (`term_id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8");
  prepare stmt from @sql;
  execute stmt;

 END//

delimiter ;

DROP PROCEDURE IF EXISTS wp_copyTemplate;

delimiter //

create procedure wp_copyTemplate(
		IN template_blog_id_in bigint(20),
		IN into_blog_id_in bigint(20)
	)
  
  main:BEGIN
  declare template_prefix_var varchar(64) default null;
  declare into_prefix_var varchar(64) default null;
  
  set template_prefix_var=CONCAT('wp_',template_blog_id_in,'_');
  set into_prefix_var=CONCAT('wp_',into_blog_id_in,'_');

  call wp_copyTable(CONCAT(template_prefix_var,'term_relationships'),CONCAT(into_prefix_var,'term_relationships'));
  call wp_copyTable(CONCAT(template_prefix_var,'term_taxonomy'),CONCAT(into_prefix_var,'term_taxonomy'));
  call wp_copyTable(CONCAT(template_prefix_var,'terms'),CONCAT(into_prefix_var,'terms'));
  
  call wp_copyTable(CONCAT(template_prefix_var,'options'),CONCAT(into_prefix_var,'options'));
  
  set @sql=CONCAT("UPDATE ",into_prefix_var,'options'," SET option_name=\"wp_",into_blog_id_in,"_user_roles\" WHERE option_name=\"wp_",template_blog_id_in,"_user_roles\"");
  prepare stmt from @sql;
  execute stmt;
  
 END//

delimiter ;

DROP PROCEDURE IF EXISTS wp_copyTable;

delimiter //

create procedure wp_copyTable(
		IN f_in varchar(64),
		IN t_in varchar(64)
	)
  
  main:BEGIN
  
  set @sql=CONCAT("INSERT INTO ",t_in," SELECT * FROM ",f_in);
  prepare stmt from @sql;
  execute stmt;
  
 END//

delimiter ;

DROP PROCEDURE IF EXISTS wp_initOptions;

delimiter //

create procedure wp_initOptions(
		IN blog_id_in bigint(20),
		IN option_name_in varchar(64),
		IN option_value_in longtext
	)
  
  main:BEGIN
  
  set @sql=CONCAT("UPDATE wp_",blog_id_in,'_options SET option_value=',QUOTE(option_value_in),' WHERE option_name=\"',option_name_in,'\"');
  
  prepare stmt from @sql;
  execute stmt;
  
 END//

delimiter ;

To create a blog along with a user I can call:

call wp_createUserAndBlog(
"Joe", -- first name
"Blogs", -- last name
"joeblogs", -- username
"joe.blogs@ficticious.com", -- email address
"http://www.ficticious.net/people/joe.blogs/", -- homepage
1, -- site id : SELECT id FROM wp_site;
"Joe-Blogs/", -- URL path extension to the site URL path.
3, -- the template blog id : SELECT blog_id FROM wp_blogs WHERE path LIKE '%template%';
"The Blog of Joe Blogs", -- description of the blog
"Joe Blogs", -- title of the blog
"editor" -- administrator or editor
);

You can add someone else to the same blog by supplying different details, or update the details of the blog (except for the path) or the user (except for the username).