Creating Database Tables from Object Definitions

Question

I know that there are several (automatic) ways to create a data access layer to manage an existing database (LINQ to SQL, Hibernate, etc.). But I get tired (and I believe that there must be a better way to do things) of things like:

• Creating / Modifying Tables in Visio
• Using Visio "Update Database" to create / modify a database
• Import tables into a LINQ to SQL class object
• Change code accordingly
• Compilation

How about a way to create a database schema from an object / object definition? I cannot find good links to such tools (and I would expect some built-in support, at least in some frameworks).

It would be ideal if I could just:

• Change object definition
• Change the code that controls the object
• Compile (database changes are made automatically)

Answer 1

Check out DataObjects.Net - designed to support just this case. Only code, and nothing more. Its schema update level is probably the most recognized that you can find, and it really completely abstracts the SQL schema update.

Check out the product video - you will not notice that nothing has been done to synchronize the circuit. An example schema update shows the intended use of this function.

Answer 2

You may be looking for a database of objects .

Answer 3

I believe this is a problem that Microsofy Entity Framework is trying to solve. Although it is not specifically designed for "Compilation (database changes are made automatically)," it solves the problem of processing changes in the domain model without a huge dependency on the underlying data model.

Answer 4

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

Answer 5

What you described is GORM. It is part of the Grails framework and is built to work with Hibernate (possibly JPA in the future). When I first used Grails, it seemed the opposite. I was more comfortable working with the Rails process to create tables and let the structure generate forests from the database schema. GORM saves your domain objects for you, so you create and modify objects, control the creation / updating of the database. Now that makes more sense when I'm used to it. Sorry to tease you if you are not looking for a new structure, but it is on the roadmap for version 1.1 to make GORM available standalone.

Answer 6

When we built the first version of our own structure ( Inon Datamanager ), I read its earlier SQL tables and auto- generate Java objects from them.

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

Actually, they completely forgot about the SQL part, until I came back and added it. But we are currently launching a trigger when starting an application that iterates over the object model, checks to see if tables and all the correct columns exist, and creates them if not. Very comfortably.

This turned out to be much simpler than you might expect - if your favorite tool does not support such a process, you could probably write it in a couple of hours - assuming that the relation to object matching is relatively simple.

But the fact is that it depends on whether you are a cultural person or a database person - you can consider any of them as an authoritative source.

Answer 7

Some of the really big dogs, such as ERwin Data Modeler , will access the database. You need to have a lot of money to afford a product.

Answer 8

I kept digging around some of the “core” frameworks, and it seems that Django is doing exactly what I was talking about. Or, it seems, from this screencast .

Does anyone have any comments about this? Does it work well?

Answer 9

Yes, Django works well.

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

It will not always modify existing tables, if you update the structure, you may need to manually start the ALTER table.

Ruby on Rails has an even more advanced version of these functions (Rails migrations), but I don’t like the framework, I find rubies and rails quite peculiar

Answer 10

Kind of a late answer, but here it goes:

I ran into the same problem and ended up writing my own solution for myself, but only with .NET and SQL Server. It basically implements the process you described:

• All database objects are stored as embedded CREATE scripts as part of the source code.
• Database objects are installed automatically (or upon request) when using the data access function
• All changes without a table are also performed automatically (or upon request) at the same time.
• Table changes that may require special attention when transferring data are performed using (manually created) change scripts also when updating the database
• Even manual changes made to any database object can be detected, so that the integrity of the circuit can be checked and corrected.
• An optional lightweight ORM can display stored procedures and objects, as well as result sets (even several).
• Command-line application helps synchronize SQL source files with development database

The library, including the database, is free under the LGPL license.

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