Wednesday, June 15, 2011

Database Separation between UI and Service

I have decided to use Joomla to manage the front end of the website, because does a great job of handling all of the boring coding stuff that is complicated and uninteresting that I don't want to re-create myself: login security, user validation, form "captcha" validation, standard and modular menu generation, and on and on.

So when you go to http://talkingowlproject.com, you will be interacting with a website that is powered by Joomla.

The actual "AI" part of the website, on the other hand, I will create as an XML REST web service and do the coding as completely custom, object-oriented PHP. It should be simple enough to tweak the code in certain Joomla documents so that they have a "chat box" that you can type into, that interacts with the XML REST service using AJAX.

Since I want to keep the actual AI part of the Talking Owl service separate from the application user interface, it makes sense to me to keep their data segmented, too: Joomla has its entire set of tables that it uses, and I should have a separate set of tables where I store all of the Talking Owl service-specific data (the owls, the conversations, the "knowledge" in the owl brains, and so on).

The Joomla user interface will be able to access the custom Talking Owl tables (to display things like links to favorite owls, or featuring a particular owl or conversation) because the Joomla database object (JFactory::getDBO()) lets you define custom SQL strings and execute them. So I can store all of the Talking Owl Rest service data in custom tables, but I can still access that information and display it in the Joomla UI using the PHP Module (Fiji Web Design) extension, and writing PHP code that reads from my custom tables.

The actual dialogue when a user chats with a Talking Owl will be handled by the REST service, so I don't need to worry about database interaction between Joomla and the Talking Owl tables.

But here's the problem: creating new Owls.

I don't want it to be possible to create new Owls through the REST service. Not at this time. I'm too paranoid about security. I want users to have to create an account through the Joomla UI, log in, and create their owls that way. (This may change later, but it's how I feel right now.)

So how do I create new Owls in the database from the Joomla UI?

My first instinct is to use the ProForms extension (Mad4Media), because it is awesome. It lets you create custom forms with custom fields, it does data checking and validation, it will capture and prevent standard hacker stuff, and it provides things like Captcha validation which I don't want to deal with programming myself.

However, Proforms stores things within its own database, in a format that isn't optimized for storing Owl information (because it's a generic form system and needs extensible columns).

As a result, if I rely on using the data as it is stored by ProForms, my queries for getting Owl data will be needlessly complex and will be hopelessly entwined with the Joomla extension, instead of having my own nice, modular and completely separate set of tables dedicated to the actual Talking Owl Service, like I want.

So what to do?

It seems like I have three options, in order of increasing difficulty (for ME, anyway):

Option 1: After the ProForms form is submitted, have it re-direct to a PHP script, and have one of the things that this script does is copy the relevant data from the ProForms data table into the separate "Talking Owl" data table.

Option 2: Learn how to do "triggers" in MySQL, and simply have the custom Talking Owl data table update whenever there is an INSERT event in the ProForms table.

Option 3: Actually allow new Talking Owls to be created through the XML REST Web Service, and somehow call the web service when the Proforms form is submitted


I will start with Option 1. We will see how it goes.

No comments:

Post a Comment