RpSequel : rfc sql crud with wordpress

I set out to use MsAccess with xhr/ajax to maintain tables I added to my wordpress database, from my desktop. (Because I suck at html forms backends and consider them a waste of time.)

I used a similar technique ten years ago, setting up msaccess as reporting tool for SAP R/3 with RFC dll’s and ActiveX. That remained stable for eight years without maintenance. Hey, I might get lucky with WordPress xml-rpc and xhr/ajax.

I called the example RpSequel.

adding sql rpc functions

To sync the data sets, I will duplicate a list with sequel operations from my desktop database as rfc call to my blog’s xmlrpc-endpoint. To handle that list, I plug a sql crud method into the xml-rpc method array in WordPress :

add_filter( 'xmlrpc_methods', 'rpsequel_methods' );

function rpsequel_methods( $methods ) {
    $methods['rpsequel.rpsequelInsert'] = 'rpsequelInsert';
    return $methods;
}

function rpsequelInsert($args) {
}

The basic INSERT method itself can be simple:

function rpsequelInsert($args) {
        global $wpdb;

//the first parameters
        $blog_id	= (int) $args[0];
        $username	= $args[1];
        $password	= $args[2];
        $tablename      = $args[3];

//get the structs
        $structs        = $args[4];

//pick the first 
        $struct = $structs[0];

//compose the mysql insert statement     
        foreach($struct as $key => $value){
            $SqlFields .= " `".$key . "`,";
            $SqlValues .= " '".$value . "',";
        }

        $SqlFields=substr($SqlFields, 0, strlen($SqlFields)-1);
        $SqlValues=substr($SqlValues, 0, strlen($SqlValues)-1);
    
        $SqlStatement .= "INSERT INTO ".$wpdb->prefix.$tablename. " (". $SqlFields .  ") VALUES (".$SqlValues . ")";
        
//execute the query        
        $wpdb->query($wpdb->prepare($SqlStatement));

//return the record id
       return $wpdb->insert_id; 
}

That in itself is not very exciting, it stuffs records in the host’s database. Which is fine, however, I want the blog to respond to individual operations.

I can write rpc-functions for every single function I want the blog to perform, that means truckloads of rpc-functions, on both client and server end. I am incredibly lazy, so I ain’t gonna go there.

adding hooks to sql rpc functions

Lucky for me, WordPress has hooks, hooks are cool.

Adding action hooks (before_insert and after_insert) to the crud method makes it more powerful. Two hooks are enough to separate the business logic of the desktop database from the blogs reporting logic.

function rpsequelInsert($args) {
        global $wpdb;

//the first parameters
        $blog_id	= (int) $args[0];
        $username	= $args[1];
        $password	= $args[2];
        $tablename      = $args[3];

//get the structs
        $structs        = $args[4];

//here is the first action hook,
//it hands the tablename and the records over to any
//function that 'listens' on the action hook

do_action('rpsequel_before_insert',  $tablename, $structs);

//the rest of the rather dull method

        $struct = $structs[0];
//.... 
//execute the query        
        $wpdb->query($wpdb->prepare($SqlStatement));

//here is the second action hook,
//it hands the table name with the new record id
//to any function that 'listens' on the action hook

do_action('rpsequel_after_insert',  $tablename, $wpdb->insert_id);

//return the record id
       return $wpdb->insert_id; 
}

Now it is more exciting.

  • I can send a list of records and a rfc insert-method to the blogs xmlrpc endpoint
  • the rfc crud-plugin can process the records one by one
  • Before and after each insert operation, the method triggers an action.
  • Before inserting, it exposes the record data.
  • After inserting, it exposes the record id.

And the last two, was exactly what I wanted.

adding functionality with plugins

Now I can add tiny plugins, that ‘listen’ on the action hooks in the rfc-methods. If there is an INSERT into the database, my plugins read which table it affects. They can perform actions, either before the insert, with the new record data, or after the insert, with the new record id.

add_action ( 'rpsequel_before_insert', 'before_insert_logic', 10, 2);
add_action ( 'rpsequel_after_insert', 'after_insert_logic', 10, 2);

function before_insert_logic($rpc_tablename, $rpc_array) {
   if($rpc_tablename=="ships") {
//do some stuff before inserting incoming records
   }
}

function after_insert_logic($rpc_tablename, $rpc_insert_id) {
   if($rpc_tablename=="ships") {
//do some stuff after inserting incoming records
   }
}

That’s basically all it takes. As technique, it has it’s limitations, but it can come in handy sometimes.

integrating ms office and wordpress with vba and xml-rpc

Okay.

Yesterday I made some basic stuff to grab my WordPress blog data with visual basic for applicationsn cos I don’t feel like programming php admin pages for tables.

So let’s make a basic xml-rpc crud plugin and put my agenda on vba remote control.

The basic xml-rpc plugin is simple, plug extra methods into the xml-rpc method array, and write a function per crud-method.


add_filter( 'xmlrpc_methods', 'add_agenda_xmlrpc_methods' );

function add_agenda_xmlrpc_methods( $methods ) {
    $methods['agenda.addAgendaItem'] = 'addAgendaItem';
    $methods['agenda.updateAgendaItem'] = 'updateAgendaItem';
    $methods['agenda.deleteAgendaItem'] = 'deleteAgendaItem';
    $methods['agenda.reportAgendaItem'] = 'reportAgendaItem';
    return $methods;
}

function addAgendaItem($args) { }

function updateAgendaItem($args) { }

function deleteAgendaItem($args) { }

function reportAgendaItem($args) { }

//basic login helper function
function CheckLogin($user, $pwd) {}

There now, if I call on the xmlrpc.php file, the extra methods are added to the callback array and I can use the table CRUD functions from my vba desktop.

function addAgendaItem($args) {

        $blog_id	= (int) $args[0];
        $username	= $args[1];
        $password	= $args[2];
        $AgendaItem     = $args[3];

//remember : add a login check
//(for the example it is irrelevant)

        global $wpdb;
        $sql = "INSERT INTO ".$wpdb->prefix."Agenda (
                `userid`, `tags`, `description`, `firstdate`, `enddate`, `link`, `price`, `location`
                ) VALUES (
                '".$AgendaItem[0]['userid']."',
                '".$AgendaItem[0]['tags']."',
                '".$AgendaItem[0]['description']."',
                '".$AgendaItem[0]['firstdate']."',
                '".$AgendaItem[0]['enddate']."',
                '".$AgendaItem[0]['link']."',
                '".$AgendaItem[0]['price']."',
                '".$AgendaItem[0]['location']."'                
                )";
        $wpdb->query($wpdb->prepare($sql));

        return $wpdb->insert_id;
}

note : the agendaitem is a struct in an array (see below), I use [0] to get the first struct (which is the actual array with field-value pairs, my record with agenda info).

Activate the plugin, and write a simple test


'Type to hold an agenda info record
Type AgendaItem
    userid As String
    tags As String
    Description As String
    firstdate As String
    enddate As String
    link As String
    price As String
    location As String
End Type


Function AddAgendaItem()

txtURL = "http://wwwblog.com/xmlrpc.php"
txtUserName = "MyUsername"
txtPassword = "MyPassword"
  
  Dim objSvrHTTP As ServerXMLHTTP
  Dim strT As String
  Set objSvrHTTP = New ServerXMLHTTP
  
  objSvrHTTP.Open "POST", txtURL, False, CStr(txtUserName), _
   CStr(txtPassword)
  
  objSvrHTTP.setRequestHeader "Accept", "application/xml"
  objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
  
    strT = ""
    strT = strT & ""
    strT = strT & "agenda.addAgendaItem"
    
    strT = strT & ""
    strT = strT & "" & txtBlogId & ""
    strT = strT & "" & txtUserName & ""
    strT = strT & "" & txtPassword & ""

'now we go make the struct, I use a Type (stdobject), normally
'you'd use a recordset

Dim a As AgendaItem

With a
    .userid = 1
    .Description = "rpc testing"
    .firstdate = "2009/10/14"
    .enddate = "2009/10/14"
    .link = "https://www.juust.org/"
    .price = "rpc testing"
    .location = "limmen"
    .tags = "php, xml-rpc"
End With

    strT = strT & ""
    strT = strT & ""
    
    strT = strT & ""

    strT = strT & "userid" & a.userid & ""
    strT = strT & "tags" & a.tags & ""
    strT = strT & "description" & a.Description & ""
    strT = strT & "firstdate" & a.firstdate & ""
    strT = strT & "enddate" & a.enddate & ""
    strT = strT & "link" & a.link & ""
    strT = strT & "price" & a.price & ""
    strT = strT & "location" & a.location & ""

'close the struct    
    strT = strT & ""
    strT = strT & ""

'close the struct array
    strT = strT & ""

'end parameters
    strT = strT & ""

'end method
    strT = strT & ""

'send the lot to the blog  
  objSvrHTTP.send strT

'print the response to debug 
  Debug.Print  objSvrHTTP.responseText

End function

Et voila :

agenda rpc

That’s yer basic Office-Wordpress XML-RPC integration.

a social spider

I was reading about the BloGee project and some other stuff and then I thought “how much trouble would it be writing a WordPress plugin to do some basic ‘social spidering'”. BloGee is about a micro-content format and that’s a bit out of my scope.

I want a simple ‘social’ spider for WordPress so I am going to take some functions of simpleTags and add them to the WordPress xml-rpc server methods, to get some basic functionality I can call upon through the xml-rpc endpoint.

I don’t know if I mentioned that, adding methods to WordPress XML-RPC differs from the straight forward use of Incutio because WordPress uses its own filter/hook system, the actual IXR_Server instance is made and managed by WordPress itself.

I hence don’t use the

class PeekAtYouServer extends IXR_Server {

style class instancing, in stead I make a class where I hook the function I would normally hand to the IXR_Server as callback into wordpress by adding method/callback to the ‘xmlrpc_methods’ filter array.

	class PeekAtYouServer {

		function PeekAtYouServer() {
		//  add callbacks as methods to the array (filter) xmlrpc_methods 
			add_filter('xmlrpc_methods', array(&$this, 'filterXmlrpcMethods'));
		}

		/**
		 * Here I connect the methodName pay.PeekAtYou to a custom function
		 *
		 * This is the array that is added (as pair) to the xmlrpc_methods filter
		 **/

		function filterXmlrpcMethods(&$methods) {
			$methods['pay.PeekAtYou'] = array(&$this, 'onXmlRpcpayPeekAtYou');
			return $methods;
		}

		/**
		 * the custom function used as callback for pay.PeekAtYou
		 **/

		function onXmlRpcpayPeekAtYou($args) {
				global $wpdb; 
//grab posts
				$sql = "SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status = 'publish'";
				$posts = $wpdb->get_results($sql);
//cycle through all posts and grab the IDs

				$result = array();
				if (!empty($posts)) {
					foreach ($posts as $post) $this->postids[] = $post->ID;
//I got all the ids in an array, 
//now I grab the tags (query is from the simpleTags plugin)
					$this->getTagsFromCurrentPosts();
				} else {
					return new IXR_Error(404, 'no posts for the selected criterium.');
				}
				return $this->tags_currentposts;
		}

	/**
	 * Get tags from current post views
	 * (SimpleTags plugin)
	 * @return boolean
	 */
	function getTagsFromCurrentPosts() {
		if ( is_array($this->postids) && count($this->postids) > 0 ) {

			// Generate SQL from post id
			$postlist = implode( "', '", $this->postids);

			global $wpdb;
			$results = $wpdb->get_results("
				SELECT t.name AS name, t.term_id AS term_id, tt.count AS count
				FROM {$wpdb->term_relationships} AS tr
				INNER JOIN {$wpdb->term_taxonomy} AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
				INNER JOIN {$wpdb->terms} AS t ON (tt.term_id = t.term_id)
				WHERE tt.taxonomy = 'post_tag'
				AND ( tr.object_id IN ('{$postlist}') )
				GROUP BY t.term_id
				ORDER BY tt.count DESC");

			$this->tags_currentposts = $results;
			unset($results, $key);
		}
		return true;
	}
	}

I saved the file as PeekAtYouServer.class.php.

Now I need a simple file for WordPress to ‘spot the plugin’, so I can activate it and make the class instance, that adds the custom method and callback function.

	/*
	Plugin Name: PeekAtYou XMLRPC Server
	Plugin URI: https://www.juust.org/
	Description: Adds Social Spidering to your blog
	Author: juust
	Author URI: https://www.juust.org/
	License: GPL
	Version: 1.1
	*/

	require_once 'PeekAtYouServer.class.php';
	$PeekAtYouServer = new PeekAtYouServer();

I save that as PeekAtYouServer.php and upload the lot to a directory /wp-plugins/pay-xmlrpc-server.

In the Plugin screen (wp 2.5) I can activate the plugin, and then make a call to the xmlrpc-endpoint of the blog using pay.PeekAtYou as methodName.

	include('wp-includes/class-IXR.php');
        $client = new IXR_Client('https://www.juust.org/xmlrpc.php');
	$client->query('pay.PeekAtYou');
	$response = &$client->getResponse();
	print_r( $response);

That returns all tags the blog uses.

Next week : adding some basic social blog-spider functions.