I don't understand CRUD
Pardon the pun. What I mean is, I don't understand the allure of auto-generating Create/Read/Update/Delete queries for your application (which is a trend I've seen in a lot of ColdFusion frameworks lately). I'll admit that I haven't ever used this, so I'm falling into the trap of criticizing something I haven't tried. However, the examples I've seen of this are usually way too simplified for my needs. The applications I build are almost never simple enough that somebody else's code will successfully interface with my database. My queries are usually fairly complex, and they often require a lot of brain bending effort to build correctly, which is not conducive to generated SQL. A big reason for this is because I make a lot of effort to create normalized databases. And a properly normalized database (3rd normal form, imo) usually requires that nearly every query joins a handful of tables together to get the data you need. I could be wrong, but I doubt generated queries will support that correctly.
However, I'm open to discussion on this. Somebody that has a lot of experience with scaffolding and it's relatives, please convince me that I'm wrong here. Because if so many smart minds are convinced that generated SQL is a good idea, I must be wrong.
Jake Munson
36 Yrs old
In general, they are meant to be starting place, not a whole application.
More advanced ones like ModelGlue and RubyOnRails actually handle joins, but maybe not the complex ones in your environment.
Most of them understand views so if you can combine alot of the basics of your complex queries into views, you can use them.
Finally in a shameless plug, my generator (Squidhead) creates the CRUD SQL code as stored procedures, and then pulls in all of the stored procedures in the database, not just the ones it creates. So you can do all of your development in the database (just MS SQL for now) and not have to do the basic framing of CFC based on Stored procedures by hand . (Which is pretty rule based.)
I hope some of this helps.
Do you have a link to Squidhead? I'd like to try it out.
I'm not a fan of overfocusing on database normalization because beyond a certain point you're trading processor cycles for disk space and in my world disk space (wasted columns) is cheaper than processor cycles (complex joins).
The question I might ask is what business benefit do your clients get from the SQL optimizations you perform? A cleanly designed db structure with integer keys and well designed indexes is one thing, but beyond a certain point, wuldn't they get more value in you either charging less or adding better user permissions or a new feature rather than you spending a bunch of time writing custom CRUD when you might have been able to generate an 80% as good solution in 2% of the time?
That said, most CF CRUD generators are using db introspection which isn't rich enough to be deeply useful. You need to have a metabase and custom data types to generate usable CRUD supporting custom fields, validations and transformations for data types like SSN or PhoneNumber. Will be posting my take on all this as soon as I get my code base stable.
1. Use views
2. Don't make complex queries
In response to 1, I think that's an excellent idea. As far as 2 goes, I think that's an over simplification. The problem I see with auto-generated code is that it is 'just a start', as a few of you stated. The reason we are able to make money as developers is because people have unique problems that need to be solved. You will /never/ find a tool out there that will fully generate a plug-n-play app for you, because every problem has it's own unique issues. The same goes for SQL. I can never just grab some SQL from an old app and drop it into a new one, because the new one has different problems than the old one.
The reason for database normalization is not to save disk space, imo, it's to increase maintainability. How easy is it to add a column, and go back through your app changing your code, when compared to adding a new row in a table? I'd take the later, but a lot of apps I've seen are built to do the former. So my 'clients' (I work for a large corporation, I'm not a consultant) benefit from normalization in that it's much easier to maintain the database and it's related interfaces.
That said, you guys have convinced me that first of all, I need to be using more views, and secondly generated SQL does have a place and can be very useful. But I don't think it's the answer for every problem, and I think some people try to bend their application to fit the code generator, which could have some negative side effects.
I work on fairly simple projects, but I see large complex projects and it seems to me that many of the "custom" solutions could be solved using patterns and heuristics. An if you can use patterns and rules then you can write software to write software.
There is no software that can be written that you cannot write software to write. The only question is how many times you will be writing similar software and whether it is worth the additional time to write the code that writes the code.
I have written generators handling complex joins, supporting versioning, auditing, rollback, roles based security systems, advanced, preference set multi-modal notification capabilities and the like. I have generators that use custom data types to generate well structured CRUD supporting all kinds of things people usually treat as custom. I also try to code to patterns where there won't be an unacceptable loss of utility as if I can generate code based on recurring patterns I can create substantially more value in less time.
Just my 2c!
Good points. I agree 100% code generation is *NOT* the solution to every problem. In fact nothing is the solution to every problem (except maybe XML according to Matt over at CF Weekly :->).
It is amazing how much you can generate though and it is a lot more than people realize, but it does not work for all problem spaces and in general, the bigger the budget for a piece of code the more it is worth hand tuning it. It is the difference between buying a Toyota and at most adding some after market fog lights or a spoiler and paying to have a F1 racing car hand tuned. one is better value for money, but the other sure as heck goes faster (although I wouldn't want to try to bring the shopping home in it!).
I write sedans and occasional sports tourers, but I'm not in the business of racing cars because it is a job for craftsmen and can't scale and I'm building a software company to generate sedans more cost effectively!
Every single insert statement I've ever written looks just like this:
insert into table_name (column_list) values (value_list)
And every update statement like:
update table_name set column=value where some_condition_holds
Likewise with delete: delete from table_name where some_condition_holds
I don't think I've repeated any statement over and over again as much as those 3 (well, maybe i++). So, I finally got fed up with it never have to write it again (well, only time will tell, but I don't anticipate needing to for most things): I just name my model class the same as the table, pass a form to it (actually, the framework takes care of that too), and its fields gets compared to the columns in the db - with the appropriate insert/update/delete being performed. Life is good.
Now, I admit that the R(ead) in CRUD is a bit less "templated." But, it is easy enough to get simple stuff for your views. And, it is also possible to support joins (I recently added that capability to cfrails - thanks for the mention btw), so you can get a little complex there as well.
The point to remember though, is that you don't *have* to use it. You shouldn't need to bend your application (well, aside from actually using the ORM) to the CRUD/ORM to get it to work. If you can't get the data you need with the available functions (which, for the majority of them, I think you can), or if the abstraction actually ends up making it more complex to deal with, you can always drop right back and write that *one* query by hand - rather than dealing with all of them.
Peter is right about having a richer set of "types" being needed. But, I think the DB introspection is a good starting point - with it you can auto-validate things like "is this a number" or "is the string too long" and such. If you add some more types, it becomes more useful (for instance, a string must match such and such pattern).
It's important to note that for me, this isn't a "starting point." There is no code actually generated for you to change - it just does what it is supposed to, and you can extend it. Now, scaffolding - that would be "a starting point" only =), but not for the actual operations themselves.
.....
"The reason for database normalization is not to save disk space, imo, it's to increase maintainability. " I agree with this. But the irony of it is that it decreases maintainability in the code by making it more complex.
....
Jake, you also mentioned "How easy is it to add a column, and go back through your app changing your code" and I'm with Peter on that: I just add a column and it appears in my application. If I don't need any business logic around it, then I'm good to go with no changes to the code at all - it is reflected in the form, and save/read operations automatically - another great thing about CRUD.
Feels like I've written a book...
Bottom line is, if you can use a generator to supply most of the simple queries for an app, then it's a good thing, imo. But like we've said before, you will still have to write a lot of queries.
I think the CF frameworks are still 'young' when it comes to this subject. When someone puts one out that fully supports DB foreign keys, data types, views, and other such stuff, for all the major DBs, I'll be much more inclined to get excited about it. :)
For updates, most times in an actual application I only ever update one record at a time (and same with deletes - although occasionally those get to be multiple in batch processing), so I've covered almost all of those queries. In any case, the goal of ORM is to make each row in a table an object, basically, so all operations on a row are exactly that - it is not trying to solve the problem of updating multiple rows at a time.
"but what's the point of adding a column if you don't need any business logic for it?"
Well, I mean things like "customer_name." What business logic is there for that? You just store and retrieve it and show it. When there is business logic (say "total_price") then that is all I need to add - no messing with the form and the insert/update statements. Basically, changes to the DB do not result in changes cascading through the application. So if all the sudden I wanted to start tracking a customer's cat's name, I just add the column and I'm done.
Indeed, they are still young and have some way to go.
I think I may need to blow my head off now. I'll never understand why the whole Java community has put this POS on such a pedestal. I've seen it used in a few corporate web apps and every one of them suffers because of it. Hibernate loads huge object trees of data to show some of the simplest web pages and the developer never knows about it until the production database has way more data than expected. Don't even get me started on concurrency - You think you can do read-consistency better than an Oracle does? Get a grip. Why the heck should I manage it outside the database when there's all all that (Insert RDMS of choice here) goodness built into the database?
And then there's the Hibernate config... I've created a beautiful datamodel and used it to create my structures et al in the database. Now I have to re-write the relationships with obscure XML tags and attributes? Since there's no compile-time checking on XML I won't know it's bad until it's being used (Yeah, I know unit tests should catch that - but not all of it!) and Yes, I realize compile-time won't test my SQL either but at least I can write it and test it myself before putting it into the app - I don't get to see the SQL when Hibernate builds it. Yes, I know you can turn that on too but it's a work-around for writing and testing it yourself.
Give me a CRUD framework any day and let me write the hard stuff myself.
Frameworks should make the easy things simple and the hard things easier. I think Hibernate missed the boat on that one.
-Mark
I used the phpobjectgenerator - very useful tool. But I agree that it couldn't do everything. You can design your tables(objects)and define relationships, and for a more complex actions to do by yourself all the work. I think the power of CRUD is in a first step: I feel like I design my application in UML, and have a partner who write repetitive code for me, and I can do the work which is most difficult :)
Eric Roberts
Owner/CF Developer
Three Ravens Consulting
http://www.threeravensconsulting.com