MSDN Magazine - February 2009 - (Page 67)

C:\>m.exe /t:none wixbits.m Microsoft (R) "Codename M" Compiler version 1.0.0925.0 Copyright (C) Microsoft Corporation. All rights reserved. C:\> Figure 2 SQL Generated by “M” create table [WixBits].[Packages]( [Id] int not null identity, [Comments] nvarchar(max) null, [Compressed] bit not null default 1, [Description] nvarchar(max) null, [InstallerVersion] int not null default 200, [Language] int not null default 1033, [Manufacturer] nvarchar(max) not null, [Product] int not null, constraint [PK_Packages] primary key clustered ([Id]), constraint [FK_Packages_Product_WixBits_Products] foreign key ([Product]) references [WixBits].[Products] ([Id]) ); create table [WixBits].[Packages_Keywords]( [_Id] bigint not null identity, [Packages_Id] int not null, [Item] nvarchar(max) not null, constraint [PK_Packages_Keywords] primary key clustered ([_Id]), constraint [FK_Packages_Keywords_Packages_Id_WixBits_Packages] foreign key ([Packages_Id]) references [WixBits].[Packages] ([Id]) on delete cascade ); If there are no errors reported by the “M” compiler, your code is clean. Notice here that in addition to passing a file with a .m extension to m.exe, I am also specifying a target (via the /t switch) of none. This means to just do the syntax checking. Without the target switch, the “M” compiler will attempt to use your program to generate a SQL script to create tables and insert values. SQL Generation If you try to generate SQL from the sample bit of WiX model data (after adding the closing double quote), you’ll get an error report like this one: C:\>m wixbits.m Microsoft (R) "Codename M" Compiler version 1.0.0925.0 Copyright (C) Microsoft Corporation. All rights reserved. C:\wixbits.m(16,9): error M2010: The referenced type 'Product' does not define an identity constraint. You can also get this error message if you choose M Mode | Reach SQL Preview from inside Intellipad. What’s happening is that the “M” compiler is trying to generate a SQL script that matches the types and values defined in the program. It does so by attempting to map top-level named values (called extents) to create table statements and to map the values themselves into insert statements. However, because “M” has all kinds of general-purpose data description uses, not everything in it maps to SQL constructs—for example, entity types without identity fields. It’s still valid “M,” but not valid for generating SQL. Generating SQL is the default action of the “M” compiler because a modern relational database is a fabulous environment for storing and manipulating data. It has all kinds of wonderful features such as security, replication, versioning, auditing, and on and on. If you make use of SQL Server for hosting your data, you get all of those features, plus a host of robustness and performance optimizations. To make your “M” types compatible with SQL Server, the mapping requires an identity column, which you can add using the following code: type Package { Id : Integer32 = AutoNumber(); } where identity Id; Figure 3 Mapping “M” Types to SQL insert into [WixBits].[Packages] ([Product], [Description], [Manufacturer]) values (@WixBits_Products_Id0, N'A really super pad for notes', @ WixBits_Products_Manufacturer0); declare @WixBits_Packages_Id0 bigint = @@identity; insert into [WixBits].[Packages_Keywords] ([Item], [Packages_Id]) values (N'Installer', @WixBits_Packages_Id0); insert into [WixBits].[Packages_Keywords] ([Item], [Packages_Id]) values (N'Super', @WixBits_Packages_Id0); insert into [WixBits].[Packages_Keywords] ([Item], [Packages_Id]) values (N'Notepad', @WixBits_Packages_Id0); insert into [WixBits].[Packages_Keywords] ([Item], [Packages_Id]) values (N'Sample', @WixBits_Packages_Id0); Collection fields—the Keywords field of type Text*—maps to another table to hold the zero or more items in that collection, with the corresponding foreign key constraint. Figure 3 illustrates how the values map. Packaging and Deployment As you can see, I am creating a field named Id and using the AutoNumber function to set a default value. Then, I add a constraint on the type using the where keyword that marks the Id field as the identity column. With this in place, I have enough data to get valid SQL for the type, as shown in Figure 2. Hopefully the SQL generated by the “M” compiler matches what you’d write yourself. Module names map to SQL schema names. Type names map to table names. AutoNumber maps to “identity” on the field, and the identity constraint maps to the primary key constraint on the field. Intrinsic “M” types such as Integer32 and Text map go corresponding SQL types such as int and nvarchar. msdnmagazine.com The default output of the “M” compiler is SQL, which you’ll likely want to push into a SQL Server 2008 instance. By default, that SQL is packaged as a single SQL script file: C:\>m.exe wixbits.m Microsoft (R) "Codename M" Compiler version 1.0.0925.0 Copyright (C) Microsoft Corporation. All rights reserved. C:\>dir wixbits.sql 11/22/2008 04:43 PM 2,545 wixbits.sql You are free to use this script file to populate your SQL database instance however you like, such as with sqlcmd.exe or SQL Server Management Studio. However, the SQL script has a lot less of the metadata than the original “M” source code. If you’d like to maintain that metadata—and there is value in doing so, as I will explain February 2009 67 http://www.msdnmagazine.com

Table of Contents Feed for the Digital Edition of MSDN Magazine - February 2009

MSDN Magazine - February 2009 Contents Toolbox CLR Inside Out Data Points Cutting Edge Patterns In Practice Best Practices .Net Interop "Oslo" Basics Patterns Silverlight Under The Table Foundations Windows With C++ .NET Matters Going Places { End Bracket }

MSDN Magazine - February 2009

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.