XML in Postgres – The Game Changer

1 06 2009

About the time that Postgres 8.3 came out, Adobe decided to open-source something called Flex. Up to that point, I had a non-committal relationship with Postgres. Meaning that I didn’t have a real strong preference between Postgres vs MySQL, and I didn’t participate in either community. I had heard some really good things about Adobe Flex, but I never had much interest in it until it was open-sourced. But when the two came together… it was like the Reese’s Penut Butter Cup commercials from the 80’s or 90’s. Or the KY his and her’s commercials of late… but maybe not quite that good 🙂

The point is, it was a game changer. Once you have peanut butter with chocolate, you don’t go back to putting peanut butter on celery. So why this watershed moment? Ok, you cheated and looked at the title. With Postgres 8.3 came the ability to read and write XML right from the database. And with Adobe Flex, we (finally) had a web client that could take that XML and make it wonderfully easy to work with.

All web browsers can work with XML on some level. They have to in order to render the html. But try doing client side XSLT that will work on all platforms and all browsers. It is neither fun nor easy. But Flex uses E4X (ECMAScript for XML) which is the best paradigm I’ve seen yet for working with XML. It is pretty similar to the SimpleXML functionality in PHP.

So lets take a round trip with our data. Our data starts off in regular relational tables. (Try to resist the temptation to store your relational data in XML.) For simple stuff, table_to_xml and query_to_xml make it extremely easy to convert your data to XML. If you have more complex needs, you can build pretty much anything using building blocks like XMLELEMENT, XMLAGG, XMLATTRIBUTES, etc. Postgres’ xml documentation can be found here.

We pass that XML back to the middle tier. And the great thing is, there is nothing to do here.  Because both the client and the database now “speak” the same language, there is no need to translate in the middle tier.

When Flex gets the XML, you can bi-directionally bind it to form and datagrid controls. The datagrid control supports bi-di binding out of the box, for other form controls you can extend them to support it or add a <binding/> tag. But whatever the method, the user can directly modify the XML we got from the database; adding, deleting and changing nodes or even reordering using drag and drop. When the user is done making changes, they hit save and the XML is sent back to the middle tier.

The middle tier checks authentication and permissions then passes the XML along to the database. Again, not much work being done here. No translating between form fields and database fields and no building queries out of strings and variables. It just passes along the XML to a stored procedure on the database.

The database does another permissions check. Then it shreds the XML back into relational data. The process, which we’ve taken to calling End-to-End XML, is a great simplification over the traditional web development approach. In a typical LAMP style application, the majority of the code is on the middle tier. It does all the work translating client requests into queries, binding data, iterating over result sets and building HTML forms and tables.  Almost no programming is done on the database.  As proven by the fact that the most popular database for this stack didn’t have views, stored procedures or transactions for a long time. And the mantra I’ve heard from the Ruby on Rails folks is that the database is largely irrelevant. Its just a place for persistence.

But with End-to-End XML, all of your data centric code is right there on the database. Exactly where it should be. (But given that I’m an Oracle developer by trade, I may be a bit biased.) And again, all of our client side code is on the client side.

The loose coupling has also made it easier to port applications between Oracle and Postgres backends. Flex doesn’t care where it gets the XML from only that it has the same structure. And both Oracle and Postgres follow the SQL/XML spec fairly closely. So the code between the two looks almost identical. Well with the inclusion of some Postgres helper functions that I’ll highlight in another post.

So there you have it. The story of how two open-source products combined to made my coding life much easier and more enjoyable.  And as for MySQL, I’m all to glad to leave it in my past, along with iterating over record-sets to create HTML rows and cells; spending all day coding a really cool Javascript function only to find that it is totally busted on Internet Explorer; or trying to get CSS to look the same in one browser as it does in another.

And as for Postgres, not only is it far superior to any other open-source db out there (and most commercial one’s I’ve worked with); but they’ve got a super community. They’ve got quite a few mailing lists. But the general mailing list is where end users like myself can go and get help or advice. And the core developers are never too busy or too high and mighty to stop by and answer any question you may have.

Here are the slides from the End-to-End XML talk I gave at the PostgreSQL conference.




4 responses

7 08 2009
Michael REMY

hi !

i build a user interface in Adobe Flex3, i need to get data from a distant postgres server.
i don’t find a way to connect, SQL, get data from the server !
any example of a basic case (populate a list via table ) ?

if there any way to ask server by a xml technology ? like XML-RPC…

i can’t imagine ADOBE forget to integrate some Postgres method in ActionScript 3…

7 08 2009
how to retrieve postgres data from a flex3 application ? - dBforums

[…] They didn't forget, you don't connect directly to any database directly from Flex. You need a middle tier (java, python, php) that brokers requests between the db and the client, and handles authenticating the client. The good news is that Postgres is really good for working with XML as is Flex, so your middle tier can be much smaller than traditional web programming. I cover Flex, Postgres and Flex in my blog and you may find this article of interest. XML in Postgres – The Game Changer Flex and Specs() […]

4 09 2009

Great post!

Do you can give us more detail about how you connect your client layer (Flex-dataProvider) with your database? What type of RPC you are using?

Thanks for advance…

4 09 2009

Hi Scotts partner in crime here….

We typically keep the RPC layer pretty simple. REST web services are built by having web pages that respond to simple get/post parameters and return xml in the document data. Flex HTTPService components get the XML from the url, and you can specify in flex what post parameters you wan to send in the HTTP request. When the flex app wants to save data, it serializes the XML and passes it as a post parameter in another HTTP request, which the PHP code passes right off to the db layer for further processing.

Flex supports SOAP, but we don’t bother using it. It’s just too much work when we’re keeping the middle tier so lean, and would get in the way of passing data to the DB layer.

I ended up developing a routing library (in PHP) that based on URL parameters, loads a class from a url convention, and then calls a method in the class if it exists. The method gets its data from a single POST variable, and uses normal db function calls passing the single XML parameter into the function. The postgres functions take it from there. Parsing data doing relational joins against the parsed xml data. The mddle tier is in PHP in our case, but it could be anything.

So I guess you could say its custom REST, but the point is it’s really, REALLY thin.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: