CODEWITHSUNDEEP
databaseormcode-generation
Farinha
Farinha

Reputation: 18091

Generating database tables from object definitions

I know that there are a few (automatic) ways to create a data access layer to manipulate an existing database (LINQ to SQL, Hibernate, etc...). But I'm getting kind of tired (and I believe that there should be a better way of doing things) of stuff like:

  1. Creating/altering tables in Visio
  2. Using Visio's "Update Database" to create/alter the database
  3. Importing the tables into a "LINQ to SQL classes" object
  4. Changing the code accordingly
  5. Compiling

What about a way to generate the database schema from the objects/entities definition? I can't seem to find good references for tools like this (and I would expect some kind of built-in support in at least some frameworks).

It would be perfect if I could just:

  1. Change the object definition
  2. Change the code that manipulates the object
  3. Compile (the database changes are done auto-magically)

Upvotes: 4

Views: 566

Answers (10)

Lucero
Lucero

Reputation: 60266

Kind of a late answer, but here it goes:

I faced the exact same problem and ended up writing my own solution for it, working with .NET and SQL Server only however. It basicaly does implement the process you describe:

  • All DB objects are kept as embedded CREATE scripts as part of the source code
  • DB Objects are set up automatically (or on request) when using the data access functionality
  • All non-table changes are also performed automatically (or on request) at the same time
  • Table changes, which may require special attention to migrate data, are performend via (manually created) change scripts also upon upgrading the database
  • Even manual changes made to any databse object can be detected, so that schema integrity can be verified and rectified
  • An optional lightweight ORM can map stored procedures and objects as well as result sets (even multiple)
  • A command-line application helps keeping the SQL source files in sync with a development database

The library including the database are free under a LGPL license.

http://code.google.com/p/bsn-modulestore/

Upvotes: 0

Alex Yakunin
Alex Yakunin

Reputation: 6678

Check out DataObjects.Net - is is designed to support exactly this case. Code only, and nothing else. Its schema upgrade layer is probably the most featured one you can find, and it really fully abstracts schema upgrade SQL.

Check out product video - you'll notice nothing additional is made to sync the schema. Schema upgrade sample shows the intended usage of this feature.

Upvotes: 4

jeff
jeff

Reputation:

Yes, Django works well.

yes, it will generate your SQL tables from your data model definitions (written in python)

It won't always alter existing tables if you update your structure, you might have to run an ALTER table manually

Ruby on Rails has an even more advanced version of these features (Rails migrations), but I don't like the framework as much, I find ruby and rails pretty idiosyncratic

Upvotes: 0

Leigh Caldwell
Leigh Caldwell

Reputation: 11106

When we built the first version of our own framework (Inon Datamanager) I had it read pre-existing SQL tables and autogenerate Java objects from them.

When my colleagues who came from a Smalltalkish background built the second version, they started from the objects and then autogenerated the tables.

Actually, they forgot about the SQL part altogether until I came back in and added it. But nowadays we just run a trigger on application startup which iterates over the object model, checks if the tables and all the right columns exist, and creates them if not. Very convenient.

This turned out to be a lot easier than you might expect - if your favourite tool doesn't support a similar process, you could probably write it in a couple of hours - assuming the relational to object mapping is relatively simple.

But the point is, it seems to depend on whether you're culturally an object person or a database person - you can regard either one as the authoritative source.

Upvotes: 1

Farinha
Farinha

Reputation: 18091

I kept digging around some of the "major" frameworks and it seems that Django does exactly what I was talking about. Or so it seems from this screencast.

Does anyone have any remark to make about this? Does it work well?

Upvotes: 0

Ed.T
Ed.T

Reputation: 1705

What you described is GORM. It is part of the Grails framework and is built to work with Hibernate (maybe JPA in the future). When I was first using Grails it seemed backwards. I was more comfortable with a Rails style workflow of making the tables and letting the framework generate scaffolding from the database schema. GORM persists your domain objects for you so you create and change the objects, it manages database create/update. This makes more sense now that I have gotten used to it. Sorry to tease you if you aren't looking for a new framework but it is on the roadmap for release 1.1 to make GORM available standalone.

Upvotes: 1

aku
aku

Reputation: 124014

As Jason suggested, object db might be a good choice. Take a look at db4objects.

Upvotes: 1

Dillie-O
Dillie-O

Reputation: 29745

Some of the really big dogs, such as ERwin Data Modeler, will go object to DB. You need to have the big bucks to afford the product though.

Upvotes: 0

lomaxx
lomaxx

Reputation: 115833

I believe this is the problem that the Microsofy Entity Framework is trying to address. Whilst not specifically designed to "Compile (the database changes are done auto-magically)" it does address the issue of handling changes to the domain model without a huge dependance on the underlying data model.

Upvotes: 1

jason saldo
jason saldo

Reputation: 9970

You may be looking for an Object Database.

Upvotes: 1

Related Questions