ORM - SQL Helper

Permalink 1 user found helpful
Hi everyone,

We are currently working with Concrete5. But I am missing a Database Helper. Is that correct?

Any chance this will be implemented in the near future? Or do I have to write one myself?

View Replies: View Best Answer
aberbenni replied on at Permalink Reply
aberbenni
Good Catch!

to C5 core team: Is there any way to introduce an ORM, developing Add-Ons? I'm wondering to try something simple like RedBean. Or even one of Propel and Doctrine. So enabling full OO MVC architecture.
Mainio replied on at Permalink Reply 1 Attachment
Mainio
Adodb comes with a lightweight active record class but it's not the kind of "full ORM" you see in other PHP frameworks or in Rails for instance.

I've written a small database abstraction layer that extends the adodb's class for the MOST BASIC database actions you can do. This is attached in this message, licensed under MIT license. We use this in almost all of our add-ons.

Usage:
1. Unzip that folder to your /libraries
2. Follow the instructions below

Basic database actions
----------------------
1. Create your model:
<?php
Loader::library('active_record_load', 'pkg_handle');
// Your database table HAS TO BE your class
// name in plural, i.e. here YourDatabaseItems
class YourDatabaseItem extends DatabaseModel {
   const DB_PK = 'itemID'; // Primary key column for this table
   protected $itemID;
   protected $textCol;
   protected $parentID;
}


2. Model functions
Lets assume your table is YourDatabaseItems(INT itemID, VARCHAR textCol, INT parentID), you can run these functions on that model for instance:
// Update model
$i1 = YourDatabaseItem::getByID(123);
$i1->setTextCol('new text');
$i1->save();
// Create new model
$i = new YourDatabaseItem();
$i->setTextCol('new item text');
$i->setParentID($i1->getParentID());
$i->save();


So you basically get the getter/setter methods out-of-the-box. Adodb active record class also has some kind of functionality for "has many", "has one" relations but I haven't tested that so cannot be guaranteed here.

3. Group functions
You'll also get the query class for running basic group actions:
<?php
Loader::library('active_record_load', 'pkg_handle');
class YourDatabaseItem extends DatabaseModel {
   const DB_PK = 'itemID'; // Primary key column for this table
   protected $itemID;
   protected $textCol;
   protected $parentID;
   public static function findByParentID($id) {
      $q = self::getQuery();
      $q->filter('parentID', $id);
      return $q->get();
   }
}


Please note that this is not the perfect / complete solution but it's quite handy if you just need the basic functions / actions.

EDIT: Also should be noted that this is quite a performance heavy solution for bigger lists of data, so in those cases straight SQL calls and list handling is much better / efficient.
Mainio replied on at Permalink Reply
Mainio
And by the way, that is meant to be included in packages only, if you want to include that in the root of your installation, you'll need to tweak the active_record_load.php script.
Mainio replied on at Permalink Reply
Mainio
And just to note, the reason for creating this was basically to get a quick solution for creating custom models that support active record and extend the base class.

You can also do this without the library above straight with the adodb library:
$i = new ADODB_Active_Record('YourDatabaseItems');
$i->textCol = 'new item text';
$i->parentID = 123;
$i->Save();


But with this solution you don't get the getByID and group functions out-of-the-box for instance.
RadiantWeb replied on at Permalink Reply
RadiantWeb
Great concept helper Mainio. thanks for sharing.!

ChadStrat
pumppi replied on at Permalink Reply
Hi Mainio,

This is great library and makes Active Record easy :)
Could this library be on github also?

I have used it and also added an abstract service class in the library. Abstract Service includes some methods that I use everyday on querying.

Offtopic:
Doctrine 2 would be great tool for ORM in Concrete5. Has anyone used it on Concrete5?

Pete
Mainio replied on at Permalink Reply
Mainio
Hi,

Thanks, yeah I can put it up to github, just so many things to do currently... But when I have time I will.

About doctrine, well, I've heard bad things about it. :)

Antti
roooii replied on at Permalink Reply
Good post Mainio. AdoDB is to lightweighted for me. I'll stick with your library for now.

Still wishing for an ORM tool that will be implemented in the near future. I still believe that nowadays a good ORM tool can't be missing in a good framework/cms.
Mainio replied on at Permalink Reply
Mainio
Yeah, me too. My personal opinion is that while there are some drawbacks in some situations of using ORM, it can greatly improve productivity of work when building something more complex.

But this has been discussed, Andy himself (CTO @ concrete5) said something like "if you need ORM, include that in your packages / projects". I think they feel that simple database interaction layer is enough in the core and anything on top of that not required.

But I kinda agree on that in the sense of the whole project and its ecosystem because swapping the database interaction layer in a project as this is not quite possible at this point. And is there any benefit of shipping the product with 2 libraries for the same purpose if they added additional ORM library there.

Also other point is that if Andy started a thread now saying "We've decided to include a full ORM in C5, please post your solutions", the debate on which one would be best would not end during the 2010s.
RadiantWeb replied on at Permalink Best Answer Reply
RadiantWeb
ORM and RESTful API would be major advances for Concrete5 and push it well above the rest of available CMS's today imo. I would put RESTful layers above ORM imho.

C
aberbenni replied on at Permalink Reply
aberbenni
Good post ChadStrat, but remember C5 is a CMS not a framework. If you need something more complex give a try at CMSs on top of symfony:http://www.symfonylab.com/symfony-cms-frameworks-comparison/...
They all have ORM (obvious) and easy REST (cause of symfony).
ScottC replied on at Permalink Reply
ScottC
Adodb is a lightweight orm, it's not like Doctrine or even RedBean, but you can get either of them running as well. There isn't necessarily anything baked in at all, but if you follow how Symfony does it you should be able to get either of them running in under a half a day.
Ricalsin replied on at Permalink Reply
Ricalsin
It seems the more I learn the less I know:

1). Why would you add to the abstraction by using an ORM when C5 has created easily understandable method names that can do it all for you?

2). Yes, Concrete5 is a CMS (built on the zend framework), but it is a CMS that was built to be manipulated by you. To speak of it in terms of being "limited" because it is a CMS (and not a framework) seems inaccurate, does it not? How is C5 NOT a framework if you take the time to understand how it is built?

3). RESTful is freaking me out. It's not websockets (or is it kinda?). I see how it can map a url (for an ORM, or for a backbone implementation to map browser states to a faux url). But if all you need is a function to run per a url, then use the C5 action method - or read the docs where it says it will look for your method at the url address given. Yes/No?

Please, feel free to school me here. I am looking for (technical) peace and understanding (in a world gone mad).
Mainio replied on at Permalink Reply
Mainio
Well, I personally believe ORM is nice but it also hits back in some places. Should C5 include it? Well I think I've made MY point but I personally believe not, at least at this point.

RESTful layers, yes, I kinda agree with the post above this one. Rails does not do anything fancier than C5 than just put a variable in the request telling the server-side code what kind of request the client-side is trying to replicate (to avoid browser issues). So all it does is POST/GET requests by default but of course it can also understand other methods if it gets them.

Is there any value added in adding that? Well, if doing large integration projects and handling server-server transactions, it might be good to have both sides speaking the same language but in most of the use-cases, I don't see any value added than in the current method of just calling a different action in the controller. After all, that is what all other _frameworks_ do as well.

I think this post was ORIGINALLY all about ease-of-use for the db calls. Then it sidetracked to something else.
jordanlev replied on at Permalink Reply
jordanlev
1) While C5 has its internal "API" with understandable method names, it is not complete. And even if it were, often you need to create additional tables for custom data (this happens a lot when you make custom dashboard interfaces for data that doesn't fit in the "everything is a page of content" model -- e.g. store locators).

2a) C5 is not built on the Zend Framework. It uses a couple of ZF libraries for some things (object caching and i18n come to mind), but it's really its own thing with its own unique architecture.

2b) C5 *is* limited compared to other frameworks, and this is a good thing -- because it is a CMS, it has a specific purpose (managing website content), so it makes a lot of assumptions about what needs to happen and it does those things for you. Lower-level frameworks (CakePHP, CodeIgniter, Kohana, Ruby on Rails, Django, etc.) give you a lot more flexibility but also require that you do more work. It's just the nature of the universe -- the tradeoff of specialization vs. generalization.

For example, if you're building a web application that has more to do with users interacting with data (as opposed to an informational website), C5 is absolutely the wrong tool for the job. Everything in C5 revolves around pages and blocks of content (and the files/images that can be displayed, and the users that can have access to do different things with them, etc.). But if you're building a calendar/scheduling app, then there's only one page and most of the "business logic" is probably on the front-end in javascript anyway, and you might need real-time input/output, and a separate API for third-party clients, etc. etc. -- why would you use C5 (or any CMS for that matter) for this purpose, when all of the goodies that C5 gives you have nothing to do with building those kinds of applications?

3a) RESTful is not WebSockets (websockets is for real-time input/output -- RESTful is more about mapping specific things to URL's).

3b) Sure you can make any tool do anything, but that doesn't make it the best tool for the job. (For example, you can pound nails in with a screwdriver, but that doesn't mean there's no reason to own a hammer). C5's architecture is heavily geared towards the idea that you have a website with pages of content, and there are blocks of content on each page. If this is what your website is like, then Concrete5 is the best tool for the job (in my opinion). But if you're building a RESTful API service, C5 gives you only the basics of what you need (and then adds a *TON* of stuff you don't need). The way it handles URL routing alone is enough to make it the wrong tool for a RESTful API, since it doesn't distinguish between the different HTTP verbs (GET vs POST [vs PUT vs DELETE]).

If you do want to learn more about RESTful things, I'd highly recommend checking out the Sinatra micro-framework in Ruby. It's really fun to work with, and in general the Ruby culture is much more technically advanced than the PHP world -- you'll learn a lot which will make your PHP code even better.

Best,
Jordan
Mainio replied on at Permalink Reply
Mainio
"in general the Ruby culture is much more technically advanced than the PHP world"
>> Couldn't agree more with this one. But to avoid any flame war here, note the two words: "in general"

EDIT: And to back up one example: learning ruby has probably made less lines in my PHP code as well.
jordanlev replied on at Permalink Reply
jordanlev
I absolutely agree -- it's just an "in general" thing (and of course it's just my opinion). I think the PHP culture is much more focused on pragmatism. Sometimes this is good and sometimes it's bad. But one thing I can say for sure is that I've never met a programmer who didn't learn something useful by exploring other languages/platforms/frameworks.
mkly replied on at Permalink Reply
mkly
I think PHP coders are just obsessed my MySQL and have a hard time transitioning into application development from web page development.

If you want something Sinatra esk that is PHP since server side is dying anyway, take a look at Slim. Nice easy to use routing and small code base.
ScottC replied on at Permalink Reply
ScottC
I use ORM right now, but it is the ADODB_ActiveRecord ORM that is used lightly in concrete5 right now. If you want to see it in action I would like for you to compare a save method under concrete/models/page.php like around line 1226

$db->query("update Pages set uID = ?, ctID = ?, pkgID = ?, cFilename = ?, cCacheFullPag....

compared to something under say filesets beween line 174 and 184.

I prefer the latter, add a Ricalsin field, add a $file_set->Ricalsion = "Learning" and still call $file_set->save(); //you don't need to update your insert, replace/update queries.

Then you get into things like a true ORM will allow you to unit test a model without it ever connecting to the database. To see an extreme example of this look up Entities in Doctrine.

That being said you will also gain access to hasMany, hasOne and things like that with an ORM which makes updating relational tables a lot easier and less error prone, but my friend David MIRV has had some problems getting it to work in ADODB activerecord so he is trying something a little lighter than Doctrine, that thing is a bit of a pain to use but is the standard now I think in the new version of Zend and certainly in Symfony.
jordanlev replied on at Permalink Reply
jordanlev
I have found ADODB_ActiveRecord to be rather inadequate (the version included in C5 is out of date and has some bugs in it -- e.g. you have to manually set primary key ID field to NULL before saving an existing record otherwise you get weird errors -- and apparently it can't be updated in core without a bunch of other things breaking). Also, it doesn't support many-to-many relations (which to me is the best reason to use an ORM).

FWIW, I've found the "Paris" lightweight ORM to be spiffy:
http://j4mie.github.com/idiormandparis/...

But in my C5 work I've mostly resorted to a super-basic ADODB wrapper that handles the most common use case of "save one record from an array of POSTed data" automatically and then leaves everything else up to custom SQL queries (using the ADODB library for parameterized queries, and putting all of the database access functions into a "model" class for separation of concerns, of course). You can see the code for that here if you're interested:
https://github.com/jordanlev/c5_boilerplate_crud/blob/master/librari...
(and see the dashboard page controller of that package for examples of code that uses that basic DB wrapper:https://github.com/jordanlev/c5_boilerplate_crud/blob/master/control... )

At the end of the day, though, everyone has different preferences for this kind of thing -- so use what works for you (as long as you're not manually escaping your own queries -- that is heresy, and you should absolutely be using ADODB or PDO or something like that to do this for you otherwise you will most likely open yourself up to SQL injection attacks).

-Jordan
Ricalsin replied on at Permalink Reply
Ricalsin
This is seriously helpful info. Thanks for navigating the discussion towards the benefits of ORM (Scott), and the github code (Jordan) that looks like it will make life easier to work with C5 models and controllers.

I have begun checking out Ruby and Sinatra. For now, it seems Slim can play that role in php as I am looking to stick with an implementation of a backbone application off a C5 CMS.

The appeal of C5 to me (for a particular project) has been the ability to handle a large amount of content that needs to be interacted with (by both users and clubs) and their relationships to each other and other categorical data.

The C5 blocks and pages were never really a benefit to me (though it seems that is its core marketability), but rather the dashboard (to manage backend data) and the structure of attribute types to attribute categories and the ability to render differenct views and forms for each.

Ruby sounds interesting and I put a lot of stock in both of your suggestions. Still, I have no idea what would replace that CMS-type handling of content in Ruby; though I recognize there's probably a lighter approach given that page blocks and collections are not a priority for me (on this project). But I am curious to learn more from Ruby, now that I've learned a great deal about code from the C5 architecture. I had to start someplace, and I feel like I learned a lot by using C5 thus far.
ScottC replied on at Permalink Reply
ScottC
I spent a lot of time not looking really at ruby as the language but at rails. There are some really nice things to look at in how you have relations from one table to another and how they use an inflector class and stuff like that to de-pluralize and infer the relations. It is all really neat stuff and really well thought out, and I feel like I have become a much better developer from just looking at how they do things and relating directly to php how Symfony is handling all of their code, though I still like the drupal/concrete5 styled bracketing of methods/classes :)

-Scott
ScottC replied on at Permalink Reply
ScottC
"you have to manually set primary key ID field to NULL before saving an existing record"

Really?

I haven't ever found that to be the case, when you are saving an existing record if you grab the object through activerecord you can simply update whatever you want on the object itself and simply call Save on the object again. I ran into weird issues where it would return an array for a field/column that didn't exist in the db. I always use id field as the primary key though... I believe in convention over configuration.

I still don't think it is that great of an ORM as it is still tied by default to a database connection and all of that, but hey it is easy to be right in hindsight.

Overall though doctrine is what I think is moving toward the standard, they have some money behind it and at least a few developers, there are others such as Redbean which i will be experimenting with on my next marketplace submission once i write it :) - If i can get away from client work :-/
jordanlev replied on at Permalink Reply
jordanlev
Hey Scott,
You're right, I mis-spoke (it's been a while since I've used ADODB ActiveRecord). Looking back at some old code, I see that the issue is for INSERTs: you must explicitly set primary key ID field to NULL otherwise INSERTs will fail.

Seehttp://phplens.com/lens/lensforum/msgs.php?id=18288... for details. This has since been fixed in ADODB, but as I mentioned earlier, C5 has not updated its version of ADODB for years (and probably never will as I imagine it will just get harder and harder to address compatibility concerns as time goes on).

-Jordan
ScottC replied on at Permalink Reply
ScottC
Yeah I think they tried upgrading it a while ago, i remember seeing the commit back when they were on subversion :)

They aren't totally married to the active record part of it throughout the app, but they might be in the addons (i know I am) and in the newer stuff like file_sets and stacks maybe?

Either way I think it is a good idea to either use something else or if you absolutely must then roll your own, but I think rolling your own is kind of a waste of time :) - This coming from someone who has done it lol.
mkly replied on at Permalink Reply
mkly
Honestly, in my concrete5 travels adodb AutoExecute solves most problems that the active record implementation in adodb solves, for me at least. I hate writing SQL and love an ORM, but for C5 stuff, AutoExecute is actually something worth looking into so that you don't have to do This_Stupid_Php_52_Nonsense to namespace everything out of the way in a package.
jordanlev replied on at Permalink Reply
jordanlev
Wow, I didn't know about AutoExecute before -- that's awesome and I want to start using it.

Do you have some kind of general pattern for using that code to save POST'ed form data as easily as possible (which is my use case for this stuff 80% of the time)? For example, how do you handle "whitelisting" of form fields, so you can just pass in the $_POST array without having to worry about random other fields that might have been maliciously changed (e.g. changing the 'id')?
mkly replied on at Permalink Reply
mkly
Ya, the only thing that sucks is knowing if you need to update or insert, but I don't find myself running into that.

In my models I create an 'accessible' property and then a method called cleanAccessible() although I keep changing the name.
class SomeModel {
  protected static $accessible = array(
    'name',
    'birthday',
    'onions'
  }
  /**
   * I usually do this statically because I'm into static
   * methods these days for some reason
   */
  public static function cleanAccessible(array $data) {
    $accessible_data = array();
    foreach($data as $field => $value) {
      if(array_key_exists($field, self::$accessible)) {
        $accessible_data[$field] = $value;


edit: It's basically the same thing as rails.
pumppi replied on at Permalink Reply
Here is one example where I use doctrine and symfony validator
https://github.com/pumppi/c5doctrine...
jordanlev replied on at Permalink Reply
jordanlev
Very cool!
Note that in your controllers you can just call $this->render() (you don't need to return it).
Also, I wonder if there's a way to make the doctrine.ini and console.sh settings changeable via a dashboard page? Maybe something that just reads those files and then presents fields to the user in the dashboard interface, then rewrites the files with the user's new field entries?

Great stuff, though -- thanks for sharing!

-Jordan
pumppi replied on at Permalink Reply
Thanks for the tips.

When I have time I could do settings on dashboard. If someone have more good ideas for improving it, just post those on
http://www.concrete5.org/community/forums/customizing_c5/doctrine-2...