DAL / Persistence Layer Comparisons
I was curious yesterday about which databases were supported by which ColdFusion Data Access Layers (DALs). I went to check on that and before I knew it, I had compiled quite a few comparisons on each.
Perhaps I should back up at this point and explain what I mean by a DAL. I DAL is any application that manages access to data (usually a database). These are often referred to as an ORM. An ORM, however, is one type of DAL (one that maps objects to a database). An ORM is a DAL, but a DAL need not be an ORM.
The table below represents all of the major ColdFusion DALs and the information that I was able to find about them. If any of my information is incorrect or if I am missing anything, please let me know.
In the interest of fair disclosure, I am the author of DataMgr. That being said, the point of this is just to provide a handy reference.
| DataFaucet* | DataMgr | Reactor | Squidhead | Transfer | |
|---|---|---|---|---|---|
| Current Version | 1.1 RC | 2.2 | ??? | 2.0 | 1.1 |
| First Blog Entry | 2008-06-10* | 2005-08-03 | 2005-09-26 | 2007-01-31 | 2005-11-15 |
| Cost | Free | Free | Free | Free | Free |
| Documentation | HTML | HTML | HTML | HTML | HTML |
| License | BSD | LGPL | ??? | Apache, V2 | Common Public |
| ORM? | Yes | No | Yes | No | Yes |
| Need File Write Access? | Yes*** | No | Yes | Yes | Yes |
| Database Introspection? | Yes | Yes | Yes | Yes | No |
| Database Modification? | Yes | Yes | No | No | No |
| Query Caching | Yes | CF8 | ??? | ??? | No |
| Object Caching | Yes | N/A | ??? | N/A | Yes |
| Download Size | 666K | 67K | 1.90M | 243K | 2.30M |
| Expanded Size | 1.5M | 356K | 4.72M | 982K | 3.34M |
| Databases | |||||
| MS Access | Yes | '97+ | |||
| MS SQL Server | Yes | 2000+ | 2000+ | 2000+ | 2000+ |
| MySQL | Yes | 4.1+ | 4+ | 5+ | 4.1+ |
| Oracle | 9i+ | 9i+ | 9i+ | 10g+ | 9i+ |
| PostgreSQL | 8+ | 8+ | 8.1+ | ||
| DB2 | Yes | ||||
| Derby | Yes | ||||
| CFML Servers | |||||
| ColdFusion | Yes | 6.1+ | Yes | 7+** | 7+ |
| Open BD | 1.0+ | 1.0+ | |||
| Railo | 3.0.1+ | 3.0.1+ | 3.0+ | ||
* Previously part of onTap framework.
** Squidhead requires CF8, but the generated code can run on CF7 or better.
*** Generates configuration files from objects, rather than generating the objects themselves (for persistence service only).
If you can think of anything else I should add to this table (or find any information that needs correction), let me know.
The current version of DataFaucet does not generate code (files) -- and that's my preference in general. Whatever it does is done at run-time.
You might want to add a couple of links or foot notes on "database introspection" and "database modification", maybe to wiki articles like you did for DAL. Modification could link to the wiki article for DDL http://en.wikipedia.org/wiki/Data_Definition_Langu...
PostgreSQL support (lower case G) is unknown with DataFaucet - I've heard that the syntax is mostly the same as Oracle, so in theory the Oracle agent might work, but I haven't tested it. There's a similarly unknown status with regard to support for Sybase because SQL Server was originally based on an older build of Sybase.
Thanks for the suggestions and corrections.
John,
In fairness, I suspect caching is one of the weaker points of DataMgr relative to some of the others.
Ike,
Do you know if FourQ can work independently of FarCry?
I agree that the link on ActiveSchema isn't optimal, but I haven't found one I really like yet. I will probably do an entry on the concept soon. In the meantime, that seems like a decent explanation. I thought about using the DDL page, but that covers what SQL is used for Active Schema, but not what it is.
Let me know of anything else that needs modification.
I do know that it does the ActiveSchema stuff -- and nothing else -- it won't allow you to use a pre-existing schema, which is one of the ways it differs from these others, so ... I'm not sure how you would label that, but if you're going to add FourQ it might be worth also adding a row to indicate that a given tool is able to use pre-existing entities or schemas (or not in the case of FourQ).
I think right now FourQ is the only one that won't utilize externally generated schemas.
The up-side is that although it limits your control of the schema (actually it seems fairly a lot), for those who dislike working with relational stuff, FourQ is a pretty "Berlin Wall" style abstraction. It's all over their documentation even that basically as far as FarCry is concerned, if you're *thinking* about the database, you're doing something wrong. Whether that's a good thing or a bad thing may be a matter of opinion. :)
I think I won't include FourQ since it is not separable from FarCry.
Just let me know when you want me to include object caching in DataFaucet and I will.
Object Caching in DF: I'm not real particular about it -- anyone who's interested can read the comments to see that it's in the BER (and it's mentioned on the DF blog), but if you want to change it to "yes (BER)" you can.
Thanks for the information. I hadn't seen that support listed anywhere. I have that added to the table now.
I think I have all of those corrections in. Thanks for letting me know about those.
Is the creation of configuration files required or just available as a convenience? Perhaps I should change that line to "Requires File Write Access"?
I may find a way to eliminate those config files in a future version, I think it would be nice, but for now that's how they work.
So basically yeah, "requires file write access" would be a good description. They're just files with a WDDX packet in them, so it's not something you're likely to create by hand -- but they're generated from the metadata in your CFCs, so you are actually writing config, you're just writing it in-context.