Automatic Generation of Transfer XML

[Brian Kotek]

While people digest my automatic Transfer Object dependency resolver, here is another idea that I've got working: automatic generation of the Transfer XML. I'm using the new cfdbinfo in CF8 to do this.

Basically, it will loop over each table and generate the object definitions and properties. It looks at the primary key and generates the ID property.

For relationships, it looks at the foreign keys defined on the table. Because Transfer defaults to resolving all relationships when it creates the object, one concern when trying to generate the relationships in the XML is not knowing "which side" the relationship should be defined on. For example, If User relates to UserType, do we create a manyToOne relationship in User (many users can have the same type), or a oneToMany on UserType (one type can be used by many users)? By default, if you try to define it in both places, Transfer will blow up because it will try to create the related objects and go into an infinite loop.

I get around it by creating it on both objects but setting lazy="true" on each relationship. This means out of the box things will still work. But it also means that for performance you may want to go in and customize the generated XML to remove lazy="true" and only leave the relationship defined on the object that you need to. While it does mean the developer will need to go in and make some decisions, I still think it's better than doing it by hand. Deleting is always faster than typing. ;-)

For many to many relationships, a convention must be brought in: an underscore. For example, user_address would relate the User table to the Address table. Once it sees that the foreign key table is a link table (by the fact that it has an underscore in the table name), it inspects the foreign keys defined and generates the manyToMany links.

I will probably blog about this (and the Transfer Object autowiring) but since I am a Transfer newbie I wanted to get thoughts from the group first. Does this sound like a useful idea? Anyone see any problems or issues that I haven't taken into consideration? If these ideas sound useful, I can clean them up and make them available to everyone.

Thanks,

Brian

[Jared Rypka-Hauer]

Sounds like an excellent idea, as does some blogging about "where to define the relationships".

That's one thing that's ALWAYS baffled me... the only real way to answer the question is to say "Am I likely to ever have a group for which I want to get a list of users? Am I likely to have a User for which I want to get the list of Groups?"

Knowing that lazy="true" will get around the explosiveness of this issue is a Good Thing(tm). Unfortunately I haven't really had opportunity to get Transfer up and running other than for fun on my own (I've been occupied on a freaking procedural Fusebox application since May). I'm looking forward to doing some good stuff with it in the (hopefully) near future, however.

Laterz,

J

[Jaime Metcher]

Brian,

Sounds like a great addition.  Just a couple of thoughts:

1. I like the idea of defining relationships both ways and then deleting the one you don't want.  I'd be intrigued to see if you can actually run with a relationship defined in both directions.  Have you tried this?  I know there are some circumstances where Transfer will load the whole collection regardless of the "lazy" attribute - there was a thread about this a while back.

2. For many-to-many, maybe the rule could be that if a table contains nothing but two foreign keys (possibly a primary key as well for those who create an independant primary key on every table) you'd use a many-to-many.  In general, once a link table has any field other than foreign keys it's going to need it's own TO definition, and hence a pair of one-to-many's. 

Jaime Metcher

[Mark Mandel]

>
> 1. I like the idea of defining relationships both ways and then deleting the
> one you don't want. I'd be intrigued to see if you can actually run with a
> relationship defined in both directions. Have you tried this? I know there
> are some circumstances where Transfer will load the whole collection
> regardless of the "lazy" attribute - there was a thread about this a while
> back.

Defining definitions both ways won't work... ever ;o) More often than
not it will go into an infinite loop of doom ;)

>
> 2. For many-to-many, maybe the rule could be that if a table contains
> nothing but two foreign keys (possibly a primary key as well for those who
> create an independant primary key on every table) you'd use a many-to-many.
> In general, once a link table has any field other than foreign keys it's
> going to need it's own TO definition, and hence a pair of one-to-many's.
>

The new composite key spec makes this even more interesting ;o)

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Brian Kotek]

It works fine in my tests when I set lazy="true" having both relationships set up. Or do you mean it work work unless you start setting relationships to lazy?

[Mark Mandel]

I mean if you try saving data against it, things will go weird very, very, fast.

Mark

On 9/5/07, Brian Kotek <bria...@gmail.com> wrote:

[Brian Kotek]

On 9/4/07, Jaime Metcher <jmet...@gmail.com> wrote:

Brian,

Sounds like a great addition.  Just a couple of thoughts:

1. I like the idea of defining relationships both ways and then deleting the one you don't want.  I'd be intrigued to see if you can actually run with a relationship defined in both directions.  Have you tried this?  I know there are some circumstances where Transfer will load the whole collection regardless of the "lazy" attribute - there was a thread about this a while back.

Yep, I've tried it and it works, at least for my tests it does. I suppose if there are situations where it won't work, then it's on the developer to go in and tweak the generated XML.

2. For many-to-many, maybe the rule could be that if a table contains nothing but two foreign keys (possibly a primary key as well for those who create an independant primary key on every table) you'd use a many-to-many.  In general, once a link table has any field other than foreign keys it's going to need it's own TO definition, and hence a pair of one-to-many's.

I also generate the object definition for the link table with a pair of one to manys in it, just in case. Again, the idea is to make things easy, so that all one needs to do is go through and start deleting XML that you don't want.

[Brian Kotek]

Ah, yes I haven't tried saving things yet, I was just creating objects and using the links. I'll try saving next, but if it does indeed freak out, then I'll have to make it more clear that you HAVE to go in and remove the undesired links before you can save.

[Mark Mandel]

There isn't much of a question, it will freak out, as there is now way
to synchronise what essentially is the same foreign key values across
2 different relationships.

You're going to get very corrupt data if you do this.

[Brian Kotek]

Well there you have it. It looks like the lesson is, if you try to generate the XML, the first thing you have to do is go in and tweak it to meet your needs. Failure to do this will result in corrupt data if you try to run unmodified XML.

I'm starting to think maybe I shouldn't try releasing this. I can just picture people trying to use it and having it rip up their data...

[Andrew Bateman]

Personally I think this would be a very useful tool even if it disregarded relationships altogether and only output configuration for the properties. Even this would save a load of time for large projects.

 

Another alternative would be to insert the relationship configuration, but have this commented out. That way, you would just uncomment the relationships you wanted to implement and could safely ignore the rest.

 

Any ideas on how you would deal with packages or do you envisage creating a flat object structure and requiring people to divide their objects into packages as they see fit?

 

Andrew 

[Sean Corfield]

On 9/4/07, Andrew Bateman <andrew....@newgency.com> wrote:
> Personally I think this would be a very useful tool even if it disregarded
> relationships altogether and only output configuration for the properties.
> Even this would save a load of time for large projects.

I agree.

> Another alternative would be to insert the relationship configuration, but
> have this commented out. That way, you would just uncomment the
> relationships you wanted to implement and could safely ignore the rest.

Yes, that sounds like a good approach.
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/

"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood

[Bob Silverberg]

I just thought that I'd point out that Brian Rinaldi's cfc generator tool (http://cfcgenerator.riaforge.org/) does this already, via the Transfer template, albeit without the relationships.  I've been using it to generate the transfer xml for my projects and it does save a lot of time.

Bob

[Brian Kotek]

As far as I know, it only does one table at a time though.

On 9/4/07, Bob Silverberg <bob.sil...@gmail.com > wrote:

I just thought that I'd point out that Brian Rinaldi's cfc generator tool ( http://cfcgenerator.riaforge.org/) does this already, via the Transfer template, albeit without the relationships.  I've been using it to generate the transfer xml for my projects and it does save a lot of time.

Bob

[Brian Kotek]

Having the relationships commented out by default sounds like a good way to go. I'll do it that way, so that no one can accidentally run the XML and nuke their database.

On 9/4/07, Sean Corfield <seanco...@gmail.com> wrote:

[Peter Bell]

Sounds good to me. Give away the problem, then you just need to start selling data reclamation software – you could make a fortune :->

Best Wishes,
Peter

> > > > W: www.compoundtheory.com <http://www.compoundtheory.com>
> > > >
> > > > > >
> > > >
> > >
> >
> >
> > --
> > E: mark....@gmail.com
> > W: www.compoundtheory.com <http://www.compoundtheory.com>
> >
> >
> >
> >
>
>
>  >
>


--
E: mark....@gmail.com
W: www.compoundtheory.com <http://www.compoundtheory.com>

[Jaime Metcher]

For completeness, you could also generate the foreign key as a property (commented out).  Then you could label the comments to identify them in groups of three, e.g.:

1a: one-to-many
1b: many-to-one
1c: foreign key property

Just in case you're looking for something to do ;)

Then the programmer just has to go through and decide which one out of each group of three to uncomment.

I know the FK property isn't the way most of us would prefer to use Transfer, but there's a regular trickle of people on this list tying themselves in knots trying to fit their use cases into Transfer's composition management, and Mark gently reminding them that you're *allowed* to manage the composition yourself.

Jaime

On 9/5/07, Brian Kotek <bria...@gmail.com> wrote:

[Adrian Lynch]

Hey Brian, I've done the same. My app looks at all the tables in a DB,
allows you to choose the tables or columns to include and then
generates all the XML needed. I was about to do what you've done with
the foreign keys but to cater for tables without FKs I am going to
allow the user to choose which table and column to link to with the
choice of relation all with the help of jQuery.

Like Illudium, mine produces all the Coldspring files and Transfer
decorators and will soon produce a bunch of custom code too.

I'm interested in seeing your take on things.

Adrian

[Jonathan Logue]

Hi Everyone,

Doesn't it seem a little backward to be introspecting the DB to get
config files? What if there were a standard XML application definition
schema which also included the database definition. This file could be
used to describe not only the structure of the database, but also the
relationships between entities (similar to the Transfer). It could
also include data used by the application for business logic or
presentation of forms, etc. This information could be used to generate
config files for Transfer, ColdSpring, MG, etc. It could also be used
to generate the DB. Any change to the application definition schema
could trigger updates to the database and any generated code or config
files that were mapped and would include the ability to turn pieces on
and off. The huge upside would be that the application definition
schema would be framework agnostic and could also be used by
developers wanted to build custom frameworks. We could also begin to
develop widgets and components that read the defintion file and allow
developers to drop in some really cool opensource modules. Just
thinking out loud.

Has anyone else thought about this approach or started developing such
a beast? Does something already exist that I am not aware of?

Jonathan

[Peter Bell]

I think quite a few of us work that way, but I'm not sure whether there is
anything open source in the CF community. If you're looking for inspiration,
check out Django in the Python world or RiFE in the Java world.

Closest CF project would probably be CF on Rails which is available at
cfrails.riaforge.org which is being managed now by Sam Larbi although I'm
not clear how active the development is . . .

Best Wishes,
Peter

[Zach Loch]

There are commercial tools that generate XML files based off of ERD diagrams.  That is really the way to go.  Creating an XML file with all of the information necessary to generate DB tables would be much more painful then generating the Transfer config files.

Zach

[Zach Loch]

What we really need is a Flex ERD tool to generate XML files......  :)


Zach

[Peter Bell]

Have to disagree with this. The database is too dumb to be much use in creating metadata. At most you might want an import tool for non-green field apps that would take the db model and generate some starting point metadata If doesn’t know nearly enough about the app to be an authoritative source if you want to generate entire apps as opposed to “rough cut” scaffolding. And why bother generating scaffolding when you could just generate the majority of the final app?!

Best Wishes,
Peter

On 9/5/07 3:16 PM, "Zach Loch" <zach...@gmail.com> wrote:

There are commercial tools that generate XML files based off of ERD diagrams.  That is really the way to go.  Creating an XML file with all of the information necessary to generate DB tables would be much more painful then generating the Transfer config files.

Zach

On 9/5/07, Peter Bell <pb...@systemsforge.com> wrote:

I think quite a few of us work that way, but I'm not sure whether there is
anything open source in the CF community. If you're looking for inspiration,
check out Django in the Python world or RiFE in the Java world.

Closest CF project would probably be CF on Rails which is available at

cfrails.riaforge.org <http://cfrails.riaforge.org>  which is being managed now by Sam Larbi although I'm

[Messer, Jon]

I think a lot of CF apps are written to connect to existing database systems (at least a lot of the ones I write are). So being able to introspect or reverse engineer a transfer config file from an existing schema would be very useful. I’ve been using Illudium but it only does one table at a time and doesn’t do relationships.

 

Of course it would be very cool to have a OSS tool that reverse engineered a db to an ERD which could then forward generate xml config files from custom xsl stylesheets. I know there are tools like that but they tend to be pretty expensive.

 

---

[Jonathan Logue]

Jon, there is no reason why that would not be a feature. It would be
easy to write the DB portion of the definition file from an existing
DB, then you could copy and paste the xml. I use Illudium as well and
I have had the same frustration with the one table at a time approach.

Peter, Thanks for the input - I will check out the technologies you
mention to see how they do things. Your description of moving beyond
scaffolding is exactly what I am envisioning... at least at some
point. For now, I am just trying to make my life easier in terms of
managing all these config files and autogenerated code.

Zach, the XML would be used for much more than Transfer XML
generation. It could (theoretically) be used to generate the database,
controllers, MG config, Transfer config, ColdSpring config, form
definitions. There could also be a data management aspect similar to
DbUnit. So, if it was well thought out, you could (theoretically
again) remove the need to understand the various framework
configuration properties since the tool would generate the framework
xml config files for you. With the ability to extend components and
modify as needed.

[Zach Loch]

Jonathan,

We're on the same page.  My point was that generating that XML file would be a major task and could be automated by dumping data captured through an ERD tool.

Zach

[Jonathan Logue]

Got it. Sounds like everyone agrees that it could be a powerful tool.

I will make an attempt to create a basic proof of concept for
discussion.

[Kevin Roche]

Jonathan,

This is basicly the way my scaffolding for Fusebox 5.5 will work. Its is
still in development expect Alpha by the end of this month. I have written a
bit about it in my Blog www.objectiveaction.com but I have been quite slow
getting it all going. I can send you the latest documentation but its a bit
behind the development at present.

What my code will do is introspect the database to create an initial XML
file which user can then modify.

Code of various sorts is then created from the XML file. Although my
templates will assume fusebox I intend to create some support for Reactor
(Nearly Finished), Transfer (Not started), ColdSpring (Just Started) and
Flex (Not started).

Kevin Roche
Team Fusebox

[Jonathan Logue]

Sure, send along anything that might be useful and you would be
willing to share. I would interested in seeing your approach.