The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database to Class converter

Hi

We have been doing some analysis on our development cycle and we are trying to improve on a number of things.
We came to the conclusion that a nice thing to have would be a database to class converter. We deal with SQLServer and MySql and we program in NET (VB/C#) and also in PHP.
Before I waste my time creating such a tool, do you know of any tool that would "read" a database and create a class that would reflect that same database? I googled it and found some tools but I was wondering if anyone has any real experience with that stuff. I don't need it to be perfect, just good enough to save some real time.

Thanks in advance

PS: if it did PHP classes, it would be fantastic:)
Antonio Louro Send private email
Tuesday, April 15, 2008
 
 
Impedance mismatch, i.e. good luck to you... :)
Don't Fix What Ain't Broke
Tuesday, April 15, 2008
 
 
Take a look at CodeSmith to generate classes based off of a database.  You can study the templates that come with it to see how you can connect to the database, get the schema, and translate that into a class.  I've used the .netTiers templates in the past and have been happy with them..
Osmyn Send private email
Tuesday, April 15, 2008
 
 
Hibernate. Or in your case, NHibernate which is the .NET version.
Steve McLeod Send private email
Tuesday, April 15, 2008
 
 
This tech is called Object Relational Mapping (ORM for short) and has been referred to as "the Vietnam of computer science"

Good luck  9^)
Chris Hulan Send private email
Tuesday, April 15, 2008
 
 
"the Vietnam of computer science" :):)
That's a good one:)

I am not expecting a perfect result from it. But at least something to save some serious typing:)
Antonio Louro Send private email
Tuesday, April 15, 2008
 
 
Antonio

For PHP I've tried propel...

http://propel.phpdb.org/trac/

Don't think there is one that would do all the platforms :(
EdNoWeb
Tuesday, April 15, 2008
 
 
The OP said: "I am not expecting a perfect result from it. But at least something to save some serious typing:) "

This is one of my (many) pet peeves. In general I'm opposed to code generators because developers don't have to live through the "pain" of coming up with a good design and using it. Time saved now by taking shortcuts will cost you many times more in the future in the form of increased maintenance, training, and process costs. A good design up front pays for itself many times over during the life of a product. A well designed set of classes should not require a bunch of "serious typing".

I'm currently on the other end of supporting an enterprise product that contains 250K LOC in the database classes alone out of 400K LOC total! The average table class contains 2K LOC. That means that we have 125 tables.

I also work on a system that I designed from the ground up. The average database class in the solution contains 200 LOC (with comments). That means I could have implemented the exact same number of tables in only 25K LOC. Now you might be thinking that 25K is still a large number. But it really isn't. It would take me all of about 12 hours to do 125 tables. The code is VERY straightforward so new programmers can learn it easily. But the most important part is that I don't need to maintain some extra set of scripts and train everyone on how to run/use them each time my database changes. If I add a new column to a table I can just manually add four new lines of code to the class. That's it.

So I challenge you to take the time to come up with a good design up front that descreases the long term cost of maintenance and training. You'll be glad you did.
dood mcdoogle
Tuesday, April 15, 2008
 
 
LINQ to Entities.
Developer #13
Tuesday, April 15, 2008
 
 
dood mcdoogle

I am not trying to get things done fast and badly.
But a lot of my team's time is spent on somewhat similar tasks. Creating classes to reflect database logic is just one  of those tasks. And in may situations with databases that we didn't create.
So although I do believe that the fundamentals behind your post are true, I don't really think that a time-saving tool is bad in itself. It can be badly used, of course. But can't they all?

Thank you all for your replies!

The PHP class tool seems promissing and will check out Nhibertnate latter on!
Antonio Louro Send private email
Tuesday, April 15, 2008
 
 
"I am not trying to get things done fast and badly."

No one ever is. But that doesn't keep them from ending up that way.

Again, this is just a pet peeve of mine. Obviously code generation can be done well. But it is hard. I've never seen it done well in my experience. And the costs associated with using it are usually underestimated. But your mileage may vary.

Good luck.
dood mcdoogle
Tuesday, April 15, 2008
 
 
+1 dood
Mike Stockdale Send private email
Tuesday, April 15, 2008
 
 
I will posit that a compiler(byte code or machine code) is also in the category of "code generation". This may seem like a technical nitpick, but I happen to use code generation techniques a lot with good success.

Really, code generation is a form of automation(like most programming). If you automate the wrong thing(i.e. typical ORM framework...), that's not a failure of automation, but a failure of design.

So yeah, I hate a lot of ORM frameworks too, but that's mostly because they automate a lot of the wrong problems.
Compilers aren't code generators?
Tuesday, April 15, 2008
 
 
I'm not a fan of code generation either. In my experience, it's not really needed in dynamic language, such as PHP. For example, you could create a generic table gateway, using the database's reflection capabilities.
Troels Knak-Nielsen Send private email
Tuesday, April 15, 2008
 
 
The source for the "The Vietnam of Computer Science" was probably this blog article:
http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx

Well worth reading.
Stephen C. Steel Send private email
Tuesday, April 15, 2008
 
 
Have you tried using typed datasets in .NET?  They're not exactly the same thing as using generated classes, but they give you some of the same benefits, like properties for table fields and easy access to related rows.  It's easy enough to drag-and-drop to create them.
Kyralessa Send private email
Tuesday, April 15, 2008
 
 
If you want to automate the creation of database table classes in the PHP language then I suggest you take a look at Radicore (http://www.radicore.org). This allows you to build classes for an existing database structure in the following way:

(a) Import the table structures into the Data Dictionary.
(b) Export each table to your PHP application.

The 'export' process acually creates two files:
- a basic class file.
- a table structure file which identifies all columns within that table, their attributes, primary key details, candidate key details, and any relationships with other tables.

This method allows the structure file to be replaced at any time (for example, after the table structure has been altered), but the class file is never overwritten as it may contain custom code.

The amount of code which is generated is actually quite small as all the code which is generic to any database table has been defined in an abstract table class, so each database table class merely extends this in order to inherit all that generic code. The starting class file contains no more than:
- the database name
- the table name

The abstract table class does not talk to the database directly, it goes through a separate DAO with a separate class for each of the major databases (MySQL, PostgreSQL and Oracle). This means that I can switch my application from one DBMS to another by changing one line in a configuration file.

By having a separate class for each database table, where the structure of each table class mirrors EXACTLY the stucture of the physical database table, there is NO impedence mismatch and therefore NO need for that abomination called an ORM.

Having built a class for each database table the next step is to create the transactions to maintain the contents of each table. Because I have been building CRUD applications for several decades I have recognised a series of recurring patterns (which have nothing to do with design patterns), so I have built a library of transaction patterns which can be accessed from the data dictionary. A working transaction can be built in three simple steps:
1) Take this database table
2) Take that transaction pattern
3) Go

This will generate the code for the specified transactions so that they can be run immediately from the menu system WITHOUT the need for writing any PHP code, any SQL or any HTML. Again the amount of code which is generated is quite small as it contains calls to functions in the standard library.

While these transactions will carry out the basic Input/Output operations, all the programmer has to do is modify the class files to add the code to implement any business rules. This means that the programmer has to spend virtually NO time in getting the basic transaction up and running so that he can spend ALL his time on the important stuff, which is implementing the business rules.
Tony Marston Send private email
Wednesday, April 16, 2008
 
 
Stephen C. Steel

Thanks for the article:)

dood mcdoogle

I do understand your objections (and everyone else's) and I tend to avoid code generation tools (as I have been doing for ever). I like to be sure how things are done and above all, that they are done the way I want them to be done (I know, control freak and control is but an illusion,etc.). But I am trying to be open-minded here and evaluate if is there anything that can speed our work without compromising quality.

Tony Marston

Thanks for the link. It seems interesting and I will give it a try.
Nice of you to take the time to explain it in such detail!
Antonio Louro Send private email
Wednesday, April 16, 2008
 
 
www.llblgen.com does exactly this for SQL and MySQL, and could also generate PHP if you modified the supplied templates.
Tom
Wednesday, April 16, 2008
 
 
Dunno about php, but Diamond Binding ( http://dunnchurchill.com/products/diamondbinding/ ) does this really well for .net 2.

The main reason you should avoid code generators is that once the code is generated its generally difficult to change. "Proper" tools like Diamond Binding get around this by working like the form designer does - but for your database.
Mike Send private email
Saturday, April 19, 2008
 
 
Actually, here's a better idea, avoid code "generators" that  generate code that's intended to be modified.

I use code generation pretty heavily on some projects. If you write a code generator to create a "skeleton" that you modify into the proper code, yeah, that's pretty crappy. You should hate using those.

Instead, code generators that take an input and produce "black box" output, are very pleasant to use. I use one of those every day, its called a compiler.

Seriously, code generation(and metaprogramming in general) is a foundational technique. Don't use it everyday, but when you need it you need it. If you use one that can only be used once, that would be like a C compiler that made an assembly "skeleton" that you then "modified" to do what you want.
I like compilers. Send private email
Saturday, April 19, 2008
 
 
We use sql2java to generate Java classes for tables. Tried a google search on sql2php because the name is kinda obvious and found http://bthangs.com/sql2php/. IANAPP (I am not a PHP programmer) so you will have to evaluate it yourself.

With sql2java the classes provide the basic read/insert/update functions and several utility functions. We add any custom code to child objects. If the table changes we re-run sql2java and the custom code never gets overwritten.

The sql2java templates are written in Velocity so you could re-write them for PHP.

Agree with some of the criticism of code generators but we put a lot of effort into the templates so we get solid extensible code for our environment in 7 seconds instead of 2 days.
jz Send private email
Monday, April 28, 2008
 
 
dood mcdoogle: 'This is one of my (many) pet peeves. In general I'm opposed to code generators because developers don't have to live through the "pain" of coming up with a good design and using it. Time saved now by taking shortcuts will cost you many times more in the future in the form of increased maintenance, training, and process costs. A good design up front pays for itself many times over during the life of a product.'

I have a simple code generator with good design up front which has saved me a lot of time over the years.  (The code generator simplifies maintenance.  Training is not applicable.  My process is faster because of the code generator.)

Sincerely,

Gene Wirchenko
Gene Wirchenko Send private email
Monday, May 12, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz