Schema round-tripping in the LightSpeed designer

LightSpeed 2 includes a designer which you can use to create your domain models visually by dragging and dropping. As you update the model, LightSpeed automatically generates the entities as C# or Visual Basic code.

However, the entity code is only one half of the equation. What about the database side of things? To help you keep your database and your model in sync, the LightSpeed designer includes a feature called “schema round-tripping,” which enables you to update your model to match your database schema, or update your database schema to match your model.

For example, suppose you’re designing a Product entity, and you add a Description attribute. Without schema round-tripping, you would go into a separate database design tool (such as SQL Server Management Studio or MySQL Administrator) to add a column to the Products table in the database. Using the LightSpeed designer, however, you can right-click the Product shape and choose Update Database. LightSpeed determines that the Description attribute doesn’t have a corresponding database column, and offers to create one:

If you’re happy that this is the right change, LightSpeed applies it:

Schema round-tripping works in both directions: if you had added the Description attribute to your database first, you could have chosen Update From Source, and LightSpeed would have offered to add it to your model. So you can design in a data-centric way or an object-centric way, whichever you feel most comfortable with.

Schema round-tripping can help you with the following changes:

  • Adding a new attribute / column
  • Deleting an attribute / column
  • Changing the data type of an attribute / column
  • Adding a one-to-many association / foreign key
  • Deleting a one-to-many association / foreign key
  • Adding a new entity / table
  • Changing the identity type of an entity / table

The full feature set is available on SQL Server, Oracle and PostgreSQL. There are currently some limitations in MySQL and SQLite support. If you run into these limitations, though, don’t forget that you can instead make changes through your database tools and use Update From Source to get them into LightSpeed. Alternatively, if you do prefer to design model-first, you can use the Update Database dialog as a checklist, even if you have to make the changes manually.

To get schema round-tripping to work, you need to tell LightSpeed which database to synchronise against. You can set this up manually by clicking on the designer background and configuring the provider and connection string in the Properties window. LightSpeed will automatically configure the database if you drag a table from Server Explorer (and the model doesn’t already have synchronisation set up). One gotcha is that, in order to match up entities with database tables, LightSpeed needs to know whether you are using the convention that table names are pluralised or not (that is, should it look for a Person table or a People table). If you find that LightSpeed keeps offering to create a Person table when the database already contains a perfectly good People table, click the designer background, go to Properties and check the pluralisation setting.

Schema round-tripping isn’t a 100% synchronisation. For example, it doesn’t currently detect if the length of a text field is out of sync between the database and the model. Nor does it have the facilities of a production-quality database designer. For example, it doesn’t generate SQL scripts that can be saved and run in your other environments, and in the 2.0 release it doesn’t have great support for bulk updates such as adding multiple tables with relationships between them all in one go. It’s aimed at enabling you to rapidly make small, frequent, iterative changes — adding and removing attributes or relationships, adding new entities, changing data types — and thereby to speed up your development cycle. Within those limitations, however, it will help you more rapidly prototype, prove, evolve and test your domain model.

Schema round-tripping is included in all editions of LightSpeed, including the free Express edition.

kick it on DotNetKicks.com

Tagged as LightSpeed

5 Responses to “Schema round-tripping in the LightSpeed designer”

  • Hi,

    When setting applying changes to a SQL Lite database, it seems reserved words are not taken into consideration! e.g. I have an entity named Order, when synchronizing with the database an exception occurs (because ORDER is a reserved word).

    By the way, where do we post errors/feedbacks/etc.?

    Thanks for the great product. Keep it up.

  • Hello Hadi,

    Yes, that is a bug. Thanks for reporting the problem: we will look into it. You can post errors and feedback at http://www.mindscape.co.nz/forums/Forum.aspx?ForumID=9 (you can get there via the Support link at the top of the page). And thanks for the kind words!

  • By the way, if your database naming conventions permit, you can get around this by setting the model’s “Pluralized in Database” property to True. This means the designer will map the Order entity to an Orders table, and Orders is not a reserved word so this will work. If you do this, you must also set LightSpeedContext.PluralizeTableNames = true at runtime (in code or in configuration).

    (We still plan to fix the underlying issue — I just wanted to mention this as a workaround, and because some people prefer plural table names anyway!)

  • This is a nice feature. When you look at doing Migrations, it would be ideal to add the field to the Migrations code in the Up method. An example of how Castle does this is here:
    http://macournoyer.wordpress.com/2006/09/20/database-migration-for-net/

    A set of all the tools in .Net are here:
    http://flux88.com/NETDatabaseMigrationToolRoundup.aspx

    Then, if the model is maintained in code, I can apply the model to an old version and it will apply the changes.

  • Hello Hadi,

    We have just committed a fix for the reserved words bug — tables and columns whose names are reserved words can now be created using the LightSpeed designer on most of the supported databases. (The issue is still outstanding on PostgreSQL and Oracle for various reasons.) The fix will be in nightly builds numbered 20080626 and above.

  • Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top