Change Set 20768 - M2M and Miscellaneous Fixes

April 5, 2007 01:04

The mechanism for handling Many to Many tables has been completely reworked. Formerly, the model relied on column naming conventions and the use of a common suffix to indicate that a given table was an M2M map. As of this CS, a mapping table is identified by its design. A table will automatically be considered a mapping table if:

  1. It has exactly 2 columns
  2. Both columns are primary keys for the table and foreign keys to another one.

The manyToManySuffix parameter is now ignored.

This proved a bit more difficult than anticipated due to the new loading mechanism. The process of determining M2M mappings is not particularly compatibile the concept of load on demand schema retrieval.

Rob threw me for a loop on that one. So I wrote him a grumpy email. I hope he still buys me those drinks at Mix he promised.

Fixes

  • AutoScaffold now correctly saves changes
  • Incorrect VarBinary data type mapping
  • Work Item 9296: Identity column names must be indentical when using Many to Many (ManyManyList) control
  • Work Item 9338: Transaction not being set in SqlDataProvider.ExecuteTransaction()
  • Work Item 9364: BetweenAnd() does not set parameter names
  • Work Item 9367: DataProvider.BuildWhere() - Bad SQL
  • Work Item 9370: Duplicate properties if >=2 FK columns reference same table
  • Work Item 9374: CodeService.RunTemplate() uses wrong template directory

Big props to justinmk for demonstrating the correct way to submit SubSonic bugs (hint hint)

Download


8 Comments
Actions: E-mail | Permalink | Comment RSSRSS comment feed

Related posts

Comments

April 5. 2007 02:11

Haacked

Can you still force a table to be a mapping table.

For example, in 3rd order normalization, it's common to have an independent primary key, and the other two columns are foreign keys with a unique constraint on the the two columns.

So this would be 3 columns for a MMM table.

1. Primary Key (independent)
2. Foreign Key
3. Foreign Key

(2,3 make up unique index)

I typically use that data model.

Haacked

April 5. 2007 02:27

Eric Kemp

Right now, no. Which is particularly odd considering this is the way I construct mapping tables, too. Guess that's what happens when you spend more time looking at other people's tables than your own.

I'll make sure this is supported in the next CS.

Eric Kemp

April 5. 2007 11:02

Remmus

Just to be a total pain.

I quite often need to order within a M2M map, so, I add another column Frown

I really don't envy you with this one.

Remmus

Remmus

April 5. 2007 13:36

Eric Kemp

Hmm... That makes complete sense, and again something that is consistent with my own requirements. The key here is to find a way to do this as a pattern. If done correctly, it could be a very powerful feature of the mapping capability.

Specifically, I'm pondering the idea of not just sequence numbers as a property, but as managed column. It should be possible for SubSonic to take care of a lot of the ugly plumbing that one invariably accompanies use of sequenced mapping. These include moving objects (rows) up or down in the sequence, returning reverse collections, and doing renumbering when an item is deleted from the map, among others.

I'm gonna mull this one over a bit...

Eric Kemp

April 5. 2007 16:51

You might want to take a poll on how people do mappings. ;)

Sometimes I do have an extra column as well. For example, some data models, I have a "cost" associated with a mapping. So that would go in the mapping table.

Obviously, you have to decide on some conventions for identifying mapping tables. I can think of a few possible.

1. The heuristics you already described (composite primary key of 2 columns consisting of foreign keys)
2. The heuristic I described (independent primary key, unique index/constraint on 2 foreign keys)
3. Naming convention (suffix == "map" or "mapping" for example)
4. (kinda hacky) Use extended properties. For example, could add an extended property in SQL with the words "Mapping Table" or something like that.

This is definitely not a trivial problem.

haacked@gmail.com (Haacked)

April 5. 2007 17:17

Eric Kemp

For auto generated mapping functionality, we're really talking about the stuff that we can do without having to provide additional information to the mapping table, or refer to it directly. Sequence number can be inferred, but when you have an additional values (like cost), you're going to have to start setting values on the mapping, which begins to marginalize the value, at least in terms of automation, that you get from generating the classes in the first place. At some point you may as well just start dealing with the mapping table as its own entity, because SubSonic can't really add much inferred value.

I agree that using extended properties is hacky, but it's an idea that I keep coming back to (although I hadn't thought about it in this context). You could do powerful things with some kind of lightweight directive syntax. Of course, that introduces a new level of complexity in multi-provider support.

I think it might be worth reintroducing #3 (in addition to #2), but I wonder if explicit identification might be more appropriate, given that mapping tables are generally a small subset of all tables, and would provide better legacy support.

I encourage anyone who has a perspective or additional scenario to weigh in here. The more input, the better...

Eric Kemp

April 6. 2007 05:48

Yeah, the multi-database support would stop me from relying soley on extended properties. Not sure if MySql, Postgre, etc... have analagous features.

haacked@gmail.com (Haacked)

April 11. 2007 17:25

kevin

for mapping table I also have a CreateOn, ModifiedOn, and StatusId columns.

this allows for me to know obviously when the m2m was created, and when/if it was Modified. The StatusId is used to change from things like "Active" or "InActive", etc.

I haven't had a chance to play around with M2M just yet but I'm hoping this will be supported.

-kevin

kevin

Comments are closed