More term dates

Here are two functions/procedures to use together with the provisional term dates functions I posted in:

http://www.cs.ox.ac.uk/blogs/edward-crichton/2012/05/30/full-term-dates/

for converting to and from Oxford term dates.


# get the date for: a term and a week and a day of week, with 1..7 being sun-sat. (2..6 = mon-fri)
# e.g. you can get Thursday of week 0 Michaelmas term with: SELECT getDateForTermWeekDay(2012,'MT',0,5);

DROP FUNCTION IF EXISTS getDateForTermWeekDay;

delimiter //

create function getDateForTermWeekDay(year int(5),term char(2),weekInTerm int(2),dayOfWeek int(1)) RETURNS date
main:BEGIN
	declare termStart date;
	
	IF term NOT IN ('MT','HT','TT')
	THEN
		RETURN NULL;
	END IF;
	
	IF term='MT'
	THEN
		SET termStart=michaelmas(year);
	END IF;
	
	IF term='HT'
	THEN
		SET termStart=hilary(year);
	END IF;
	
	IF term='TT'
	THEN
		SET termStart=trinity(year);
	END IF;
	
	RETURN DATE_ADD(DATE_ADD(termStart, INTERVAL (weekInTerm-1) WEEK), INTERVAL (dayOfWeek-1) DAY);

END//

delimiter ;

DROP PROCEDURE IF EXISTS getTermWeekDayForDate;

delimiter //

# returns year, term, week and day, with week starting from week '-1' onwards.
# e.g. call getTermWeekDayForDate('2012-10-04');

create procedure getTermWeekDayForDate(IN date_in date)
main:BEGIN
	
	DECLARE Y int(5);
	DECLARE MT date;
	DECLARE HT date;
	DECLARE TT date;
	DECLARE term char(2);
	DECLARE relative date;
	DECLARE WEEK_DEC decimal(5,3);
	DECLARE WEEK int(2);
		
	SET Y=EXTRACT(YEAR FROM date_in);
	
	SET MT=michaelmas(Y);
	SET HT=hilary(Y);
	SET TT=trinity(Y);
	
	IF date_in >=DATE_SUB(MT,INTERVAL 2 WEEK)
	THEN
		SET term='MT';
		SET relative=MT;
	ELSEIF date_in >=DATE_SUB(TT,INTERVAL 2 WEEK)
	THEN
		SET term='TT';
		SET relative=TT;
	
	
	ELSEIF date_in >=DATE_SUB(HT,INTERVAL 2 WEEK)
	THEN
		SET term='HT';
		SET relative=HT;
	
	END IF;
	
	SET WEEK_DEC=FLOOR((((DATEDIFF(date_in,DATE_SUB(relative,INTERVAL 2 WEEK)))-14)/7))+1;
	SET WEEK=WEEK_DEC;
	
	SELECT Y AS Year,term AS Term,WEEK AS Week, DAYOFWEEK(date_in) AS Day;
END//

delimiter ;
mysql> SELECT getDateForTermWeekDay(2012,'MT',0,5);
+--------------------------------------+
| getDateForTermWeekDay(2012,'MT',0,5) |
+--------------------------------------+
| 2012-10-04                           | 
+--------------------------------------+

mysql> call getTermWeekDayForDate('2012-10-04');
+------+------+------+------+
| Year | Term | Week | Day  |
+------+------+------+------+
| 2012 | MT   |    0 |    5 | 
+------+------+------+------+

When are the holidays?

When a system sends out emails requesting that some action is to be taken, or deadlines are being set automatically, it is important that these do not fall on dates where there are no administrators at work to deal with any consequences.
You need to know when the holidays are so that if a date is calculated that lands on a holiday, it can be moved forwards or backwards in time.

Here is a procedure that calculates the calculable public bank holidays and office closures for Oxford for a given year:


DROP PROCEDURE IF EXISTS listHolidays;

delimiter //

create procedure listHolidays(
		IN currentYear int(5)
		)
main:BEGIN		
	declare date_in date;
	declare yearEnd_par date;
	declare newYear_par date;
	declare christmasClose_par date;
	
	SET date_in=CONCAT(currentYear,'-01-01');
	SET yearEnd_par=CONCAT(currentYear+1,'-01-01');
	
	# When is new year day?
	IF DAYOFWEEK(yearEnd_par)=1
	THEN
		SET newYear_par=CONCAT(currentYear+1,'-01-02');
	END IF;

	IF DAYOFWEEK(yearEnd_par)=7
	THEN
		SET newYear_par=CONCAT(currentYear+1,'-01-03');

	END IF;

	IF DAYOFWEEK(yearEnd_par)!=7 AND DAYOFWEEK(yearEnd_par)!=1
	THEN
		SET newYear_par=yearEnd_par;
	END IF;
	
	# extend year end
	IF yearEnd_par < newYear_par
	THEN
		SET yearEnd_par=newYear_par;
	END IF;
	
	# calc office closure for christmas
	SET christmasClose_par=DATE_SUB(newYear_par,INTERVAL 9 DAY);
	IF DAYOFWEEK(christmasClose_par)=1 OR DAYOFWEEK(christmasClose_par)=7
	THEN
		SET christmasClose_par=DATE_SUB(christmasClose_par,INTERVAL 2 DAY);
	END IF;
	
	CREATE TEMPORARY TABLE holidays (title varchar(255) not null, type varchar(255), date date not null);
	
	WHILE date_in <= yearEnd_par
	DO
	
		# new year
		IF EXTRACT(MONTH FROM date_in)=1
		THEN

			IF DAYOFWEEK(CONCAT(currentYear,'-01-01'))=1 AND date_in=CONCAT(currentYear,'-01-02')
			THEN
				INSERT INTO holidays values('New Year','bank',date_in);
				
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-01-01'))=7 AND date_in=CONCAT(currentYear,'-01-03')
			THEN
				INSERT INTO holidays values('New Year','bank',date_in);
				
			END IF;

			IF date_in=CONCAT(currentYear,'-01-01') AND DAYOFWEEK(CONCAT(currentYear,'-01-01'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-01-01'))!=1
			THEN
				INSERT INTO holidays values('New Year','bank',date_in);
				
			END IF;
		END IF;
		
		
		# Good friday, Easter monday
	
		IF EXTRACT(MONTH FROM date_in)=3 OR EXTRACT(MONTH FROM date_in)=4
		THEN
			IF date_in=DATE_SUB(easter(currentYear),INTERVAL 3 day)
			THEN
				INSERT INTO holidays values('Easter','office',date_in);
			END IF;
			
			IF date_in=DATE_SUB(easter(currentYear),INTERVAL 2 day)
			THEN
				INSERT INTO holidays values('Good Friday','bank',date_in);
			END IF;
			
			IF date_in=DATE_ADD(easter(currentYear),INTERVAL 1 day)
			THEN
				INSERT INTO holidays values('Easter Monday','bank',date_in);
			END IF;
		END IF;
		
		# may day, spring bank. 1st and last monday in may.
		IF EXTRACT(MONTH FROM date_in)=5
		THEN

			IF (2-DAYOFWEEK(CONCAT(currentYear,'-05-01')))>=0 AND date_in=DATE_ADD( CONCAT(currentYear,'-05-01'), INTERVAL ( 2- DAYOFWEEK(  CONCAT(currentYear,'-05-01') ) ) DAY )
			THEN
				INSERT INTO holidays values('May Day','bank',date_in);
			END IF;


			IF (2-DAYOFWEEK(CONCAT(currentYear,'-05-01')))<0 AND date_in=DATE_ADD(CONCAT(currentYear,'-05-01'), INTERVAL ( 9 - DAYOFWEEK(CONCAT(currentYear,'-05-01') ) ) DAY)
			THEN
				INSERT INTO holidays values('May Day','bank',date_in);
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-05-31'))>=2 AND date_in=DATE_ADD( CONCAT(currentYear,'-05-31'), INTERVAL ( 2 - DAYOFWEEK(  CONCAT(currentYear,'-05-31')  ) ) DAY )
			THEN
				INSERT INTO holidays values('Spring Bank','bank',date_in);
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-05-31'))=1 AND date_in=DATE_ADD(CONCAT(currentYear,'-05-24'), INTERVAL (2-DAYOFWEEK(CONCAT(currentYear,'-05-31'))) DAY)
			THEN
				INSERT INTO holidays values('Spring Bank','bank',date_in);
			END IF;

		END IF;
		
		IF EXTRACT(MONTH FROM date_in)=8
		THEN

			IF DAYOFWEEK(CONCAT(currentYear,'-08-31'))>=2 AND date_in=DATE_ADD( CONCAT(currentYear,'-08-31'), INTERVAL ( 2 - DAYOFWEEK(  CONCAT(currentYear,'-08-31')  ) ) DAY )
			THEN
				INSERT INTO holidays values('Summer Bank','bank',date_in);
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-08-31'))=1 AND date_in=DATE_ADD(CONCAT(currentYear,'-08-24'), INTERVAL (2-DAYOFWEEK(CONCAT(currentYear,'-08-31'))) DAY)
			THEN
				INSERT INTO holidays values('Summer Bank','bank',date_in);
			END IF;


		END IF;
		
		# christmas day in lieu of weekend
		IF EXTRACT(MONTH FROM date_in)=12
		THEN

			IF DAYOFWEEK(CONCAT(currentYear,'-12-25'))=1 AND date_in=CONCAT(currentYear,'-12-26')
			THEN
				INSERT INTO holidays values('Christmas','bank',date_in);
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-12-25'))=7 AND date_in=CONCAT(currentYear,'-12-27')
			THEN
				INSERT INTO holidays values('Christmas','bank',date_in);
			END IF;

			IF date_in=CONCAT(currentYear,'-12-25') AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=1
			THEN
				INSERT INTO holidays values('Christmas','bank',date_in);
			END IF;
		END IF;
	
		# boxing day or monday or tuesday in lieu
		# university office closures
		IF EXTRACT(MONTH FROM date_in)=12 OR EXTRACT(MONTH FROM date_in)=1
		THEN
			
		
			IF (DAYOFWEEK(CONCAT(currentYear,'-12-25'))=1 OR (DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=1 AND DAYOFWEEK(CONCAT(currentYear,'-12-26'))=1) ) AND date_in=CONCAT(currentYear,'-12-27')
			THEN
				INSERT INTO holidays values('Boxing Day','bank',date_in);
			END IF;

			IF (DAYOFWEEK(CONCAT(currentYear,'-12-25'))=7 OR (DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=1 AND DAYOFWEEK(CONCAT(currentYear,'-12-26'))=7) ) AND date_in=CONCAT(currentYear,'-12-28')
			THEN
				INSERT INTO holidays values('Boxing Day','bank',date_in);
			END IF;
			
			IF date_in=CONCAT(currentYear,'-12-26') AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=1 AND DAYOFWEEK(CONCAT(currentYear,'-12-26'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-26'))!=1
			THEN
				INSERT INTO holidays values('Boxing Day','bank',date_in);
			END IF;
			
			
			IF date_in>=christmasClose_par AND date_in <=newYear_par
			THEN
			
				INSERT INTO holidays values('Christmas','office',date_in);
			END IF;
			
			
		END IF;
	
		SET date_in=DATE_ADD(date_in, INTERVAL 1 DAY);
		
	END WHILE;
	
	# output results
	SELECT * FROM holidays;
	
	DROP TEMPORARY TABLE holidays;
END//

delimiter ;

# returns where easter is according to the Carter algorithm
DROP FUNCTION IF EXISTS easter;

delimiter //

create function easter(Y int(5))
	RETURNS date
	
main:BEGIN

	DECLARE D int(4);
	DECLARE E int(4);
	DECLARE Q int(4);
	
	SET D=225-(11 * (Y MOD 19));
	
	WHILE D > 50 DO
	
		SET D=D-30;
	
	END WHILE;

	IF D > 48
	THEN
		SET D=D-1;
	END IF;
	
	
	SET E = (Y + FLOOR(Y/4) + D + 1) MOD 7;
	
	SET Q=D + 7 - E;
	
	IF Q<32
	THEN
	
		return CONCAT(Y,'-','03','-',Q);
	
	ELSE
	
		return CONCAT(Y,'-','04','-',(Q-31));
	
	END IF;

END//

delimiter ;

Obviously, if there are royal jubilees or weddings these will have to be dealt with separately.

For 2012 and 2013 the results are:

mysql> call listHolidays(2012);
+---------------+--------+------------+
| title         | type   | date       |
+---------------+--------+------------+
| New Year      | bank   | 2012-01-02 | 
| Easter        | office | 2012-04-05 | 
| Good Friday   | bank   | 2012-04-06 | 
| Easter Monday | bank   | 2012-04-09 | 
| May Day       | bank   | 2012-05-07 | 
| Spring Bank   | bank   | 2012-05-28 | 
| Summer Bank   | bank   | 2012-08-27 | 
| Christmas     | office | 2012-12-21 | 
| Christmas     | office | 2012-12-22 | 
| Christmas     | office | 2012-12-23 | 
| Christmas     | office | 2012-12-24 | 
| Christmas     | bank   | 2012-12-25 | 
| Christmas     | office | 2012-12-25 | 
| Boxing Day    | bank   | 2012-12-26 | 
| Christmas     | office | 2012-12-26 | 
| Christmas     | office | 2012-12-27 | 
| Christmas     | office | 2012-12-28 | 
| Christmas     | office | 2012-12-29 | 
| Christmas     | office | 2012-12-30 | 
| Christmas     | office | 2012-12-31 | 
| Christmas     | office | 2013-01-01 | 
+---------------+--------+------------+

mysql> call listHolidays(2013);
+---------------+--------+------------+
| title         | type   | date       |
+---------------+--------+------------+
| New Year      | bank   | 2013-01-01 | 
| Easter        | office | 2013-03-28 | 
| Good Friday   | bank   | 2013-03-29 | 
| Easter Monday | bank   | 2013-04-01 | 
| May Day       | bank   | 2013-05-06 | 
| Spring Bank   | bank   | 2013-05-27 | 
| Summer Bank   | bank   | 2013-08-26 | 
| Christmas     | office | 2013-12-23 | 
| Christmas     | office | 2013-12-24 | 
| Christmas     | bank   | 2013-12-25 | 
| Christmas     | office | 2013-12-25 | 
| Boxing Day    | bank   | 2013-12-26 | 
| Christmas     | office | 2013-12-26 | 
| Christmas     | office | 2013-12-27 | 
| Christmas     | office | 2013-12-28 | 
| Christmas     | office | 2013-12-29 | 
| Christmas     | office | 2013-12-30 | 
| Christmas     | office | 2013-12-31 | 
| Christmas     | office | 2014-01-01 | 
+---------------+--------+------------+

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).

Full term dates

I’ve just fallen off my chair. I’ve managed to calculate the dates of full term programmatically. No more keeping tables of dates, or at least no more entering them.

I should share this so, here are the stored functions for mysql:

# returns where easter is according to the Carter algorithm
DROP FUNCTION IF EXISTS easter;

delimiter //

create function easter(Y int(5))
	RETURNS date
	
main:BEGIN

	DECLARE D int(4);
	DECLARE E int(4);
	DECLARE Q int(4);
	
	SET D=225-(11 * (Y MOD 19));
	
	WHILE D > 50 DO
	
		SET D=D-30;
	
	END WHILE;

	IF D > 48
	THEN
		SET D=D-1;
	END IF;
	
	
	SET E = (Y + FLOOR(Y/4) + D + 1) MOD 7;
	
	SET Q=D + 7 - E;
	
	IF Q<32
	THEN
	
		return CONCAT(Y,'-','03','-',Q);
	
	ELSE
	
		return CONCAT(Y,'-','04','-',(Q-31));
	
	END IF;

END//

delimiter ;

# term dates
# "Michaelmas, Term shall begin on and include 1 October and end on and include 17 December."
# Full term actually appears to be the first Sunday after the first Monday
# find the first Monday in October and add 6 days.


DROP FUNCTION IF EXISTS michaelmas;

delimiter //

create function michaelmas(Y int(5))
	RETURNS date
	
main:BEGIN


	IF (2-DAYOFWEEK(CONCAT(Y,'-10-01')))>=0
	THEN
		RETURN DATE_ADD(DATE_ADD( CONCAT(Y,'-10-01'), INTERVAL ( 2- DAYOFWEEK(  CONCAT(Y,'-10-01') ) ) DAY ), INTERVAL 6 DAY);
	ELSE
		RETURN DATE_ADD(DATE_ADD(CONCAT(Y,'-10-01'), INTERVAL ( 9 - DAYOFWEEK(CONCAT(Y,'-10-01') ) ) DAY), INTERVAL 6 DAY);
	END IF;
	
END//

delimiter ;

# "Hilary, Term shall begin on and include 7 January and end on and include 25 March or the Saturday before Palm Sunday, whichever is the earlier."
# Full term actually appears to be the first Sunday after the first Monday after 7th January

DROP FUNCTION IF EXISTS hilary;

delimiter //

create function hilary(Y int(5))
	RETURNS date
	
main:BEGIN


	IF (2-DAYOFWEEK(CONCAT(Y,'-01-07')))>=0
	THEN
		RETURN DATE_ADD(DATE_ADD( CONCAT(Y,'-01-07'), INTERVAL ( 2- DAYOFWEEK(  CONCAT(Y,'-01-07') ) ) DAY ), INTERVAL 6 DAY);
	ELSE
		RETURN DATE_ADD(DATE_ADD(CONCAT(Y,'-01-07'), INTERVAL ( 9 - DAYOFWEEK(CONCAT(Y,'-01-07') ) ) DAY), INTERVAL 6 DAY);
	END IF;
	
END//

delimiter ;

# "Trinity, Term shall begin on and include 20 April or the Wednesday after Easter, whichever is the later, and end on and include 6 July."
# Full term actually appears to be the first Sunday after the latest of (20th April or Wednesday after Easter)

DROP FUNCTION IF EXISTS trinity;

delimiter //

create function trinity(Y int(5))
	RETURNS date
	
main:BEGIN
	DECLARE latest date;
	DECLARE twentyTh date;
	
	SET twentyTh=CONCAT(Y,'-04-20');
	
	# Wednesday after Easter
	SET latest=DATE_ADD(easter(Y), INTERVAL 3 DAY);
	
	IF latest < twentyTh
	THEN
	
		SET latest=twentyTh;
	
	END IF;

	IF (1-DAYOFWEEK(latest))>=0
	THEN
		RETURN DATE_ADD( latest, INTERVAL ( 1- DAYOFWEEK(  latest ) ) DAY );
	ELSE
		RETURN DATE_ADD(latest, INTERVAL ( 8 - DAYOFWEEK(latest ) ) DAY);
	END IF;
	
END//

delimiter ;

So if I want the term dates for the academic year starting MT2012 I could query:

SELECT michaelmas(2012),hilary(2013),trinity(2013);
+------------------+--------------+---------------+
| michaelmas(2012) | hilary(2013) | trinity(2013) |
+------------------+--------------+---------------+
| 2012-10-07       | 2013-01-13   | 2013-04-21    | 
+------------------+--------------+---------------+

Officially (which this is not), the dates for full term are prescribed by Council and then published by the Registrar to the University Gazette, and they may be different from the provisional dates. But it seems to be infrequent that the provisional dates are changed. Which, at least, makes this useful for entering dates in advance.