The Microsoft "Oslo" Base Domain Library (the BDL) -- now not completely in T-SQL!

  • Article

The Microsoft "Oslo" repository "provides a robust, enterprise-hardened storage location for the data models. It takes advantage of the best features of SQL Server 2008 to deliver on critical areas such as scalability, security, and performance." The repository team took SQL Server 2008, and used SQL Server best practices to create a series of internal schemas that enabled extremely important features of any enterprise-scale database application -- particularly manageability and security, but also scalability and performance. In short, it's a "pre-designed" SQL Server 2008 application that you can use to kick-start enterprise database application design, development, and deployment. All well and good. If you're strong in SQL skills, you can go to the link in the first sentence of this graph and read all about how it works and the features it supports.

Although the "Oslo" repository can be used for any kind of data-driven application (because it's just SQL Server 2008, duh), it is especially functional when used with data structures that fit the basic schemas in the "Oslo" repository. However, while you can design your data structures in T-SQL to take advantage of the repository features using the documentation here, the question is: How do you do this in "M"?

If you're already familiar with "M" and "Oslo" a little bit, you might know that you can obtain all the features of the repository Base Domain Library (BDL) by compiling your "M" domains with the /t:Repository switch. What this does -- among other things -- is create matching SQL Server views for your tables (using the same name as the tables but without the "Table" on the end), associating your domain models with the appropriate repository Folders, and creating triggers that can be used to secure data access in any way SQL Server 2008 supports, among other things (you can read more about this here). This switch, I think, is a tremendous boon to developers who either don't want to focus on doing data modeling work in T-SQL (we are hoping "M" will be much more efficient and pleasant to use) or who are not especially familiar with T-SQL and SQL Server 2008. Almost all our current samples (How to: Populate a Model into the "Oslo" Repository, How to: Create data using "M", How to: Install the HumanResources Sample Model using "M", and How to: Use "M" to Create Custom Icons and Display Names for some examples) use the /t:Repository switch to turn on "Oslo" repository feature patterns. It works, and it's easy.

There is a problem with this switch for people who want to use "M" but who understand database design well enough to want to control the projection of "M" into T-SQL and into the "Oslo" repository: it's all or nothing. If you create 100 extents in "M" you'll get (most likely) 100+ tables -- it varies depending on the particular "M" statements -- and if you use the /t:Repository target switch you'll ALSO get 100+ views, all secured by their own insert triggers. However, it is often the case that the database schemas contain many more tables than views. Using views are, after all, a very good way to expose data to client applications in a way that is more efficient and understandable for them. Most client applications do not need to know all the internal storage details, and those developers may well not want to know themselves. (In addition, with views you have the ability to version the underlying data tables and modify the views to draw the data from the new tables so that client applications consuming views can continue functioning. Views are good.) No, instead of having everything done for you at all times, you'd often prefer to be able to specify which views are created yourself, and specify which of the "Oslo" repository features are to be applied to them. Right?

The May CTP release of "Oslo" is the beginning of your ability to do this repository "feature pattern" work in "M" and the "M" toolchain rather than having to do this work in T-SQL directly. To do so in the May CTP release, you must use the various "M"-based patterns to structure your data correctly and then add some specific T-SQL calls in the -postSQL parameter of M.exe -- and you do not specify /t:Repository in the m.exe command. The only trick is that there are a couple different ways to handle your "Oslo" repository Folder creation. The easiest way I have found and for which I wrote an example is to:

  1. Use the HasFolderAndAutoId type in the System module, which creates the Folder and Id patterns needed to enable "Oslo" repository management and security features and expose them in "Quadrant".
  2. Make sure your table ends in "Table". :-) (The answer to your question is given to you if you examine the AddForeignFolderKey procedure. For the complete list of pattern restrictions that may be necessary to take advantage of all repository features, see Using the AddStandardPatterns Procedure.)
  3. Use the PathsFolder() pattern when you declare the values for your extent.
  4. Create your own view that has the same name as the table in step 2 but without the "Table" at the end.
  5. Use the mx.exe createFolder option to create the Folder name in the repository that you specified in your extent value declarations.
  6. Create a .sql file that calls AddForeignFolderKey passing the module name and the view name.
  7. Compile your .m file to an image, using the -postSql parameter to add the .sql file to the image.
  8. Install the image into the repository using mx.exe. If you are doing iterative work, also pass the -f switch, which will delete and reinstall the module if it is the same.

If you want to see the sample that does this, scroll to the bottom of Folders and Ids, which will continue to be updated and corrected on a regular basis as we update that information. If you want to read about how all of this works in T-SQL, see "Oslo" Repository Schemas documentation that my colleague Jason Roth put together for the January CTP release.