RpSequel : rfc sql crud with wordpress
juust | 23/10/2009I 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.
- Comments
- 1 Comment »
- Categories
- php, wordpress, xml-rpc
- Tags
- xml-rpc
Comments rss
Trackback
