3

I quite often see in PHP, WordPress plugins specifically, that people write SQL directly in their plugins... The way I learnt things, everything should be handled in layers... So that if one day the requirements of a given layer change, I only have to worry about changing the layer that everything interfaces.

Right now I'm writing a layer to interface the database so that, if something ever changes in the way I interact with databases, all I have to do is change one layer, not X number of plugins I've created.

I feel as though this is something that other people may have come across in the past, and that my approach my be inefficient.

I'm writing classes such as

Table
Column
Row

That allow me to create database tables, columns, and rows using given objects with specific methods to handle all their functions:

$column = new \Namespace\Data\Column ( /* name, type, null/non-null, etc... */ );
$myTable = new \Namespace\Data\Table( /* name, column objects, and indexes */ );

\Namespace\TableModel.create($myTable);

My questions are...

Has someone else already written something to provide some separation between different layers?

If not, is my approach going to help at all in the long run or am I wasting my time; should I break down and hard-code the sql like everyone else?

If it is going to help writing this myself, is there any approach I could take to handle it more efficiently?

Matt
  • 5,547
  • 23
  • 82
  • 121

2 Answers2

0

You seem to be looking for an ORM. Here is one : http://www.doctrine-project.org/docs/orm/2.0/en/tutorials/getting-started-xml-edition.html

greg0ire
  • 22,714
  • 16
  • 72
  • 101
  • Yes, thank you. I've been out of practice with programming for a while... HTML, CSS, etc. I've been struggling for the past week to remember the terminology for approaching something like this. – Matt Oct 02 '11 at 21:05
-1

To be honest, I'd just hard-code the SQL, because:

  1. Everyone else does so too. Big parts of WordPress would need to be rewritten, if they would ever wish to change from MySQL to something else. It would just be a waste of time to write your perfect layer for your plugin, if the rest of the whole system still only works with hard-coded SQL.

  2. We don't live in a perfect world. Too much abstraction will - soon or late - end up in performance and other issues, which I don't even think of yet. Keep it simple. Also, using SQL you can benefit from some performance "hacks", which maybe won't work for other systems.

  3. SQL is a widely accepted standard and can already be seen as abstraction layer. for example there's even the possibility to access Facebook's Graph via SQL-like syntax (see FQL). If you want to change to another data-source, you'll probably find some layer wich supports SQL-syntax anyways! In that sense, you could even say SQL already is some kind of abstraction layer.

But: if you decide to use SQL, be sure to use WordPress' $wpdb. Using that, you're on the safe side, as WordPress takes care of connecting to the database, forming the queries, etc. If, one day, WordPress will decide to change from databases to something else, they'll need to create a $wpdb-layer to that new source - for backwards compatibility. Also, many general requests already are in $wpdb as functions (such as $wpdb->insert()), so there's no direct need to hard-code SQL.

If however, you decide to use such an abstraction layer: Wikipedia has more information.

Update: I just found out that the CMS Drupal uses a database abstraction layer - but they still use SQL to form their queries, for all the different databases! I think that shows pretty clearly, how SQL can already be used as an abstraction layer.

anroesti
  • 11,053
  • 3
  • 22
  • 33
  • Great points, I won't commit too much to the abstraction layer. I still feel as though the benefits of abstraction are enough to justify writing it, partially it's that just because everyone else isn't using good programming practices doesn't mean I shouldn't. WordPress specific reasons: Even if they never change from MySQL, I have heard elsewhere that things such as `$wpdb` can change and that there may be new, better ways of interacting with WordPress databases in the future. In this case, it would be nice to have that layer of abstraction to change rather than changing 10 - 20 plugins. – Matt Oct 02 '11 at 21:23
  • I do however realize it will not provide any short term rewards and that it does have it's downfalls. I will make sure to keep a good balance here. Thanks again! – Matt Oct 02 '11 at 21:25
  • 2
    Strongly disagree - a lot of CMSes and frameworks have database abstraction, and some of them provide object to form queries (other uses ORMs). DB abstraction IMO is the first abstraction you have to do – Maxim Krizhanovsky Oct 02 '11 at 21:30
  • I agree on that you shouldn't do it "wrong", just because everyone else does. But I also think that using hard-coded SQL isn't a bad thing per definition, because - as stated in the post - you can see SQL already *as* an abstraction layer. I'm quite sure WordPress will keep `$wpdb` always backwards-compatible, even if they change their database. Creating an abstraction layer with SQL-syntax can't be that hard! – anroesti Oct 02 '11 at 21:45
  • Also, I think an abstraction-layer with SQL could perform better in terms of performance and memory-usage, as it doesn't require an object for every query. Also, SQL has a pretty good documentation, and there's a big user base out there - which means more and better support. – anroesti Oct 02 '11 at 21:47