The ORM Foundation

 Hello there again,

Ok, I've developed a fairly intricate model by now, and I'm preparing to go into development with the generated database.

Problem - As far as I can tell, the Sql Server schema (not the "database schema", but the sql server specific concept of "schema") for the generated objects is unique per model, and is set to be the value of the model's "Name" property. So, all the tables end up belonging to this single schema in the created database. I tried to read up on Groups but I can't figure what they're meant for, and didn't find a way to specify the table's schema though them either.

The problem obviously is, we need tables and other dbobjects to be generated within multiple schemas inside the same database to be able to use this Sql Server feature productively. IF there will be only one schema per model, then I guess we'll need to wait until we can import entities from external models. If not, I have a hope that, maybe, there's some way to change the schema for at least some of the tables? Even if that means manually editing the .orm file or something of the kind?

Question - What approach do you recommend to upgrade existing databases once the model has been modified? I'll be trying to do this using... "Visual Studio Team System 2008 Database Edition" projects, which enable to compare database schemas, etc... is there any other way currently? If not, it would be interesting exploring the possibility of adding some kind of integration between NORMA and VS Team Database Edition projects, which I believe enable to change the database schema without losing existing data in the tables.It might be as simple as generating the sql file in the project's folder, or copying it to the correct folder so the sql server project can catch up from there.

Minor request - Once the database has been created, it would be useful to at least have the generated .sql file include DROP statements for each contraint and table, or to be able to specify which table/s to generate the drop/create table statements for, so there's no need to manually drop the tables (which can become burdensome when there are lot's of FKs between the tables). 

    Workaround: (for Sql Server 2008) To drop all tables in the current database, in Sql Server Management Studio, select the Tables node for the database in Object Explorer, press F7 (or View, Object Explorer Details), select all the tables, select "Script Table for..." from the right click menu, DROP to > New query window. Execute.

Ok, I've done some research.

I found this post from Matt: Re: Generate a 'submodel' by selecting a set of diagrams, and it looks like the planned approach to multi-db multi-schema code generation was in fact via Groups.

I could find browsing the source code that the src\RelationalModel\DcilModel\ConceptualDatabase.xsd xml schema already defines a collection of schemas as a child node of Catalog. I can also see this in the Store Viewer, under Partition > Catalog. Actually the Catalog class (in project DcilModel) defines the property as a Collection of schemas.

So, I guess the only thing left is allowing the Relational mapper extension to map Groups into Schemas (I don't know if the different GroupTypes have been implemented yet, but I'm guessing ElementGrouping is good enough to implement this after absorption). I'm aware this is a shortcut patchwork (aka work-around) at this stage of the development and not the final vision for the feature, but I do really need to start working on this new system and I do need tables mapped to multiple schemas on the database.

 I would vote for Matt's proposed path:

"2. Consider the groupings after absorption has been determined, ignoring groupings for ObjectTypes that do not map to tables. This is definitely something we could do, and does not preclude switching to 1 in the future."

If someone could give me a quick tour around the code for the parts I need to plug some code in to get this feature I'm interested in giving it a try...

Juan,

You've done great research, and you've hit on a number of extension points that are intended to be extended in this direction.

The thread you hit mentions grouping, and indeed this is one of the areas that the grouping is intended for. The core grouping facility will be extended to support nested groups, and you will then be able to specify a 'group type' for both a database and a schema. You will also be able to put one object into multiple schemas, one schema into multiple database, have multiple simulateous subsets of the model mapping to different implementations, etc. There are a number of core issues that need to be added to support this (the generation mechanism needs to recognize multiple targets for one generation type, and the name generation settings need to be associated with individual groups instead of being global). However, at this point, using the grouping mechanism is probably not the quickest way to get what you needed.

There is a reasonable approach to do this with minimal additions to the core tool, and I will make it a priority to get the pieces needed into the tool by the next drop (I was trying to get this ready by the end of the week). Here's what needs to happen:

1. You need a way to get a schema name associated with a table. At the moment, the tables are regenerated frequently, so this basically means getting the schema name associated with the primary ObjectType for the table. In the long run this will be done with a typed group (the 'group type' would add additional properties to the group, and each group member would share the properties, so you would only need to set the 'schema name' property once). For now, you'll need to do this with a 'Custom Property'.
• Use the 'Extension Manager...' dialog to add the 'Custom Properties' extension
• With the selection on the background of an ORM diagram, you'll see a Properties property in the Custom Properties group
• In the Custom Properties dialog, select the 'Model' node
• Click the + button to add a group and name the group 'DatabaseSchema'
• Click the pencil button (the second one, the graphic is a little obscure) to add a property called 'SchemaName'.
• Type the property as a string and set it to display with the 'Entity Type' and 'Value Type' model elements
• You'll probably want to use this group later, so select the group and move it up to the Machine node. You can now copy the settings into subsequent models. We'll use the 'DatabaseSchema' and 'SchemaName' names later, so make sure these are cased as indicated.
2. The first step into the relational generation process extracts the schema information in the .orm file and produces a DCIL format xml file. DCIL represents a database structure. There are instructions in the http://www.ormfoundation.org/forums/p/549/1612.aspx#1612 thread on how to modify this file before moving onto the DDIL stage (DDIL represents an XML form of DDL). The current generated DCIL has a dcl:schema root element, but the format actually supports a dcl:catalog root which would support multiple schemas. A transform could easily be written to spit one catalog with multiple schemas. Here are the issues that I'll need to handle to enable this (work items in bold):
• The DCIL format does not support cross-schema referencing for referenceConstraint (foreign keys refering to a schema in another table) or domain references. Extend the DCIL referenceConstraint and domainRef types to support a target schema. There DDIL already supports schemas, so this should be a DCIL-only schema extension. Modify the GenerateSchemaAttribute in DCILtoDDIL.xslt to recognize the new DCIL attributes. 
• The DCILtoDDIL.xslt transform does not handle dcl:catalog as the root element. You would get one root element per schema, which is bogus xml. Explicitly handle dcl:catalog in DCILtoDDIL.xslt (definitions must be complete for all schemas before references are made).

• The DCIL names are pre-decorated beyond what is done in the Relational Model name generation to be SQL compliant. So, for example, a User table will be "User" in the DCIL file (" is the escaped form of " in the xml attribute). If the unmodified original name were provided in the DCIL, then it would be easy to map the DCIL table name back to the corresponding object type in the .orm file, which would then let us grab the custom property. Add an optional requestedName attribute to named DCIL elements to enable tracing back into the .orm file.

 With these relatively minor tweaks in place you could generate multi-schema DDL. Given that this is a stop-gap approach, I will not be installing the transform file to apply the schemas with the product, but I will make it available here. The other extensions named above will be added to the product as they are clearly holes in the system. When there is a better way to do this in the future, I will also look writing a transform to map this old pattern to the new grouping-based pattern.

If this goes as planned it should be in place by the end of the day, but I have a couple of other issues to address as well, and this is software, so please be patient. The end result of the exercise will be an available selection in the Generated File Modifiers section of the Generation Settings dialog that is called something like Apply DatabaseSchema.SchemaName Properties.

-Matt

 Matth,

 Outstanding!

I was gonna try the custom property route myself before seeing your post about GTs. I'll be itching to see the final result, and I know, this is software.

I'm researching  VS Team Database Edition's extensibility mechanisms to see whether a NORMA or a DB Edition extension could make integration happen without much hassle.

More on that later. Happy coding, and thanks.

 Matt,

 One more little bit of input. I think we need the SchemaName custom property on Subtypes and Objectified Fact Types too, or is the mapping mechanism gonna catch them up from the EntityTypes directly?

All tables are directly associated with either an entity type or (in rare cases) with a value type. If you have an objectified fact type, then there is always an EntityType associated with it. You can see all properties for an entity type (including the extension properties) either in the ObjectifyingEntityType expandable property with the primary objectified FactType shape selected, or by selecting the "ObjectifyingTypeName" above the fact type shape.

There are two cases not covered here:

1. Ternary and higher arity FactTypes, as well as any binary FactType with a spanning internal uniqueness constraint, always have a backing EntityType, but there is no way to graphically select it, so there is no way to set an extension property. In this case, we could look for the property on a FactType if it isn't on the EntityType (this may be what you're hinting at).
2. I think a ValueConstraint may end up as a named domain (I have to check this, I'm not sure). The domain would potentially need a schema name.

In any case, let me get the proof of concept and support pieces in place and then I'll look closer at the actual data gathering.

Thanks for the heads up, I hadn't thought about the implied entity types mapping to tables in the initial response.

If a subtype absorbs into a supertype's table then the supertypes schema will be used. 

-Matt

I've added the discussed modifications to the ORM DCIL mechanism with the March 2009 CTP release (changesets 1380 and 1381) and attached a generator and readme instructions for applying the schema names.

Juan has given this a test drive (thank you Juan) and reports success generator schema names.

-Matt