PSEC: Connecting Dots, Part IV

SUMMARY: Review and refine database schema. Add database initialization code for testing, populating tables with minimum data set. Looked at issues of security and user authentication surrounding use of AJAX with WordPress. Reviewed several solutions, then slept on it.

Now, I get to fill in all the parts that actually do stuff.

STEP ONE! Read some data from the database and stuff them into some kind of Javascript data structure.

First, I have to stuff some data into the tables, using MySQL Workbench Community Edition. Now I have to remember what my own database schema is and put in some data.

dstest_init_tables() can be used to insert the data that I will put in.

Side Note: I happened to stumble on a reference to object relational mapping, which is how one maps two different data types to each other. In this case, it’s javascript objects versus relational databases, and there’s a LOT of controversy about the best way to handle it. Whee.

Database Structure Review

There are currently four tables: ACTIONS, TASKS, TASK_PREREQS and TASK_DEPS. ACTIONS are the description of what to do. TASKS are assigned actions. PREREQS and DEPS are for prerequisite and dependent tasks.

The two tables I need to populate are ACTIONS and TASKS. One action referred to one task should do the trick. Wrote some code to drop tables, reinitialize them, and then add some data by setting a special variable, confirmed that the database is populated with MySQL Workbench.

Getting the Data

So I have some data stored in a SQL database on a server, and I need to populate some kind of data structure in Javascript that can be drawn as a browser element. That suggests a “Model” of tasks, which are then used to draw a particular “View”. And now we are in the thick of what people normally think of Application Design.

I need to talk my way through this for a few minutes.

Ok, first thing is to limit what my test application does. It really is just reading to-do lists out of two tables. While I have grander plans in the future, it’s important that I don’t complicate things too much.

At the very minimum, I am envisioning an associative array of objects, indexed by id number. The objects have to be initialized somehow from a data source, which is ultimately grabbed from MySQL via some kind of server-side PHP script that packages it all up nicely.

So how does that work? And how do I set up a web service so the page knows where it lives? Hm. Let’s write some pseudocode.

  • Call webserviceURL with command like “fetch dataset”. This initiates some kind of GET or POST request to the server.
  • The webservice runs, and somehow has to figure out WHO is requesting the data. Since the webservice is running outside of the theme’s function.php, none of the code will have any idea who the logged-in user is. How’s THAT done?
  • The webservice responds with the requested data or an error
  • The javascript callback occurs, which is dispatched somehow.

The first thing that comes to mind is that my webservice page will need to access the wordpress database. I’d much rather access it THROUGH wordpress by loading the bare minimum number of files. The easy–but hefty–way is to just include wp-config.php with SHORTINIT defined, which defines wpdb but doesn’t not load user authentication. Loading everything, without theme support set, will also add the necessary user information. However, this does load a LOT of stuff for just a database call.

Alternatively, I think I could probably set some kind of cookie for my application if a user is authenticated. I don’t want to authenticate remote users…I just want to authenticate that users that are logged in can access the web service. So, before I go any further, I need to read-up on how to authenticate based on a session cookie.

(much time passes)

Well, this is a big can of worms!

  • WordPress apparently uses PHPass for its hashing. There’s a tutorial on how to do it “properly”, which I’m reading.

  • WordPress also uses Nonces to protect specific actions from being “replayed”. There are links to how to use the built-in function in plugins at WordPress Nonces.

  • I could implement cookie-based authentication, using a secure PHP hashing approach to username/password that somehow allows me to match with a wordpress user. The contents of the cookie, set up by the web page, is sent during the webservice call, which authenticates against it. But I’d have to authenticate against the cookie by storing values separately. Ideally I would use WP’s built in functionality.

<

p>While looking through using wp nonces in plugins, I got the impression that AJAX can be used with plugins. But how? I’m now reading AJAX in Plugins to see how it works…

Ah! The best way to use AJAX in WP is to follow these directions. Apparently, the AJAX dispatching is handled for you through the definition of action hooks.

This still seems like overkill to me, though there is an DOING_AJAX constant that maybe covers this. I suppose I should try it first with the “right way” and see how slow it is. This succinct example provides the template to try.

I’ll pick this up tomorrow. Sigh.