Beginning SQL Server Modeling Model-Driven Application Development in SQL Server 2008 (cid:2) (cid:2) (cid:2) Bart Weller Beginning SQL Server Modeling: Model-Driven Application Development in SQL Server 2008 Copyright © 2010 by Bart Weller All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. Image copyright notices and permissions: Page 1: ©iStockphoto.com/tompics Page 13: ©iStockphoto.com/tiridifilm Page 33: ©iStockphoto.com/EricHood Page 81: ©iStockphoto.com/seamartini Page 115: ©iStockphoto.com/Leadinglights Page 137: By permission of the Master and Fellows of St John's College, Cambridge, UK, and Ned Lee Fielden Page 163: ©iStockphoto.com/pavlen ISBN-13 (pbk): 978-1-4302-2751-9 ISBN-13 (electronic): 978-1-4302-2752-6 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Python and the Python logos are trademarks or registered trademarks of the Python Software Foundation. Publisher and President: Paul Manning Lead Editor: Mark Beckner Development Editor: Ewan Buckingham Technical Reviewer: Fabio Claudio Ferrachiatti Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes, Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Coordinating Editors: Candace English and Debra Kelly Copy Editor: Kim Benbow Compositor: Bytheway Publishing Services Indexer: Brenda Miller Artist: April Milne Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or visit www.springeronline.com. For information on translations, please e-mail [email protected], or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales– eBook Licensing web page at www.apress.com/info/bulksales. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at www.apress.com. You will need to answer questions pertaining to this book in order to successfully download the code. To: Kathie—wife and companion Riley, Grady, Sallie, and Brook—the future Future generations, with sincere apologies from the present Willy—a great llama and herd jester; may he rest in peace Contents at a Glance (cid:2) Contents at a Glance..............................................................................................iv (cid:2) Contents..................................................................................................................v (cid:2) About the Author.....................................................................................................x (cid:2) About the Technical Reviewer...............................................................................xi (cid:2) Acknowledgments................................................................................................xii (cid:2) Introduction.........................................................................................................xiii (cid:2) Chapter 1: Installing and Setting Up SQL Server Modeling.....................................1 (cid:2) Chapter 2: Introduction to Intellipad.....................................................................13 (cid:2) Chapter 3: Domain-Specific Languages 101: Lola’s Lunch Counter......................33 (cid:2) Chapter 4: Introduction to Quadrant.....................................................................81 (cid:2) Chapter 5: M – The Modeling Language..............................................................115 (cid:2) Chapter 6: SQL Server Modeling Services – The Folder Pattern.........................137 (cid:2) Chapter 7: SQL Server Modeling Services – Security........................................163 (cid:2) Appendix A: Intellipad Primer.............................................................................207 (cid:2) Appendix B: Intellipad Mini-Buffer Commands...................................................215 (cid:2) Appendix C: Intellipad Commands and Gestures................................................217 (cid:2) Appendix D: The Quadrant Menu Tree.................................................................225 (cid:2) Appendix E: Generated T-SQL for the Car Model Example..................................229 (cid:2) Index...................................................................................................................235 iv (cid:2) CONTENTS Contents (cid:2) Contents at a Glance..............................................................................................iv (cid:2) Contents..................................................................................................................v (cid:2) About the Author.....................................................................................................x (cid:2) About the Technical Reviewer...............................................................................xi (cid:2) Acknowledgments................................................................................................xii (cid:2) Introduction.........................................................................................................xiii (cid:2) Chapter 1: Installing and Setting Up SQL Server Modeling.....................................1 Software Pre-Requisites....................................................................................................1 Hardware and Operating System Requirements...............................................................2 Configuring SQL Server.....................................................................................................2 Downloading and Installing...............................................................................................4 Checking the Installation..........................................................................................................................7 If Something Goes Wrong…..............................................................................................8 The Repair Option.....................................................................................................................................9 The Uninstall Option.................................................................................................................................9 Summary.........................................................................................................................12 (cid:2) Chapter 2: Introduction to Intellipad....................................................................13 Getting Started with Intellipad.........................................................................................13 The File Menu.........................................................................................................................................14 The Edit Menu.........................................................................................................................................16 The View Menu................................................................................................................20 Full Screen..............................................................................................................................................21 v (cid:2) CONTENTS Zoom.......................................................................................................................................................23 Split Windows.........................................................................................................................................23 The Mini-Buffer.......................................................................................................................................24 The Help Menu.................................................................................................................25 Commands List.......................................................................................................................................25 Intellipad Primer.....................................................................................................................................26 View Title Banner Functions............................................................................................26 View Modes............................................................................................................................................27 (cid:2) Chapter 3: Domain-Specific Languages 101: Lola’s Lunch Counter.....................33 Some Caveats..................................................................................................................33 A Simple Exercise: The Sandwich Language...................................................................34 Where You Want to End Up.....................................................................................................................34 Getting Started with the Intellipad DSL Grammar Mode Interface.........................................................36 Broadening the Choices..........................................................................................................................39 Interleaving (Ignoring) Whitespace.........................................................................................................40 Defining Tokens......................................................................................................................................41 Enabling Multiple DSL Statements.........................................................................................................42 Tightening Up the Syntax........................................................................................................................45 Moving Toward Structured Data.............................................................................................................45 Testing the Syntax..................................................................................................................................48 Making the Syntax More Flexible...........................................................................................................52 Extending SandwichOrders to More Than One Main Ingredient.............................................................54 Deployment............................................................................................................................................56 Thinking Ahead.......................................................................................................................................78 Concluding Thoughts.......................................................................................................79 (cid:2) Chapter 4: Introduction to Quadrant.....................................................................81 My Car: Creating a Simple Model in Quadrant.................................................................81 Building the Car Model in Quadrant........................................................................................................82 vi (cid:2) CONTENTS Deploying the Model to SQL Server........................................................................................................85 Viewing the Model and Adding Data in the Explorer..............................................................................87 Customizing Column Views in Quadrant.................................................................................................93 Viewing and Editing the Model in SQL Server........................................................................................95 Managing Changes to the Data in Quadrant...........................................................................................97 Managing Conflicts in Quadrant.............................................................................................................99 Using the Quadrant Explorer Query Bar................................................................................................102 More on Customizing the View.............................................................................................................105 Summary.......................................................................................................................114 (cid:2) Chapter 5: M – The Modeling Language.............................................................115 Why M?..........................................................................................................................115 Getting Started with M...................................................................................................115 Modules................................................................................................................................................116 Types....................................................................................................................................................117 Intrinsic and Derived Types..................................................................................................................118 Modules Revisited: Import and Export Directives..........................................................127 Extents...........................................................................................................................129 Computed Values...........................................................................................................133 Overloading..........................................................................................................................................135 Languages.....................................................................................................................136 Summary.......................................................................................................................136 (cid:2) Chapter 6: SQL Server Modeling Services – The Folder Pattern.........................137 The Modeling Services Folder Pattern...........................................................................138 Example: A Quality Control System for CarModel.................................................................................139 (cid:2) Chapter 7: SQL Server Modeling Services – Security........................................163 Using Security to Limit Data Visibility............................................................................164 Setting Up – Installing the PatternApplication Sample..................................................164 vii (cid:2) CONTENTS Building on the CarModel..............................................................................................165 Building the MfgComponentModel Project in Visual Studio...........................................167 Refining the Model to Include Security..........................................................................170 HasFolderAndAutoId.............................................................................................................................175 Adding the PatternApplication Module.................................................................................................176 Building the Project..............................................................................................................................181 Deploying to the Database....................................................................................................................182 Creating the QC Folders........................................................................................................................187 Building the Sample Data.....................................................................................................................189 Setting Up the QC Manager Test Users................................................................................................195 Summary.......................................................................................................................205 (cid:2) Appendix A: Intellipad Primer.............................................................................207 Intellipad Basics............................................................................................................207 Buffers..................................................................................................................................................207 Views....................................................................................................................................................208 Modes...................................................................................................................................................208 The Mini-Buffer.....................................................................................................................................208 Working with M in Intellipad..........................................................................................209 SQL Preview.........................................................................................................................................209 Basic DSL Authoring Configuration.......................................................................................................209 Customizing Intellipad...................................................................................................209 Changing the Menus.............................................................................................................................210 Changing the Colors.............................................................................................................................210 Adding New Modes...............................................................................................................................210 Customizing Commands.......................................................................................................................211 Intellipad Components...................................................................................................211 Compiled Components..........................................................................................................................211 Declarative Components.......................................................................................................................212 viii (cid:2) CONTENTS Script Components...............................................................................................................................212 List of Available Modes..................................................................................................212 Command-Line Options.................................................................................................213 (cid:2) Appendix B: Intellipad Mini-Buffer Commands...................................................215 (cid:2) Appendix C: Intellipad Commands and Gestures................................................217 (cid:2) Appendix D: The Quadrant Menu Tree................................................................225 (cid:2) Appendix E: Generated T-SQL for the Car Model Example..................................229 (cid:2) Index...................................................................................................................235 ix