Trends in Database Development: XML, .NET, WinFS

1. Trends in Database Development: XML, .NET, WinFS Alexander Vaschillo Microsoft

2. Database development • Relational model is working well • Benchmarks • Security • Enhancements • New models • Hierarchical (XML) • Object • Ease of use • New uses • WinFS

3. New Directions in SQL Server • XML • Hierarchical, semi-structured data • Object oriented extensions • New programming models • .NET integration • Server • Client • New applications • WinFS

4. Why new data models • Flat relational result is good to print reports • Hierarchical result is ideal for Web Pages • Object data model is for programming against • Dataset • Objectspaces • Web Services

5. Why XML? • Presentation format • Transport format • Platform independent • Text-based format • Schema with data • International standard not owned by any one company

6. HTTP Access Via URL • URL Query http://server/vroot?sql=select+*+from+Customers+FOR+XML+Auto&root=root • XML View http://server/vroot/schema.xsd/Customer[@ID='ALFKI']?params • Template http://server/vroot/template.xml?params

7. Move data in a standardized format (XML) Object (XML) Loosely Coupled Systems • Scalable. Many to Many. • Changes in Implementation do not break each other Mapping Mapping Data App Logic Application System Data System

8. XPath XQuery XML Files XML View XML/ HTML SQLXML – Bringing worlds together XML world The Two Worlds SQL Language SQL Server Data storage RowSet Data output Relational world

9. Three Worlds C#,C++, VB SQL XPath XQuery XML Files Memory SQL Server Map Map Object RowSet XML/ HTML XML world Object world Relational world

10. Different kinds of data • Structured • Highly regular, homogeneous structure • Rowsets, Comma delimited files • Semi-Structured • Heterogeneous structure • Sparse Occurrences of data • HTML and XML documents • Unstructured • Documents/Content

11. SQLXML From 10,000 Feet • Provides a rich XML view of relational data • Semi-structured, hierarchical view of flat relational data • Two-way view: query and update • Multiple access mechanisms (HTTP, ADO, ADO.NET, SOAP) • Middle tier and Server side • XML: extensible, platform independent format for your data

12. FOR XML Query • SQL Language Extension SELECT… FROM… WHERE… ORDER BY… FOR XML ( raw | auto [, ELEMENTS] | nested [, ELEMENTS] | explicit) [, XMLData] [, BINARY base64])

13. XML Views • Map between relational data and XML • Declarative • Noninvasive • No changes to legacy data sources • No control over DB Server required • XML View is an XML Schema • XSD for SQLXML 2.0 and 3.0 • MSD for Yukon

14. XSD Mapping Example <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="Customer"msdata:relation="Customers"> <xsd:complexType> <xsd:sequence> <xsd:element name="Order"msdata:relation="Orders"> <xsd:annotation><xsd:appinfo> <msdata:relationship parent="Customers" parent-key="CustomerID" child="Orders" child-key="CustomerID" /> </xsd:appinfo></xsd:annotation> <xsd:complexType> <xsd:attribute name="OrderDate" type="xsd:dateTime"/> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="CustomerID" /> </xsd:complexType> </xsd:element> </xsd:schema>

15. XPath/XQuery • Use XPath/XQuery to query SQL Database as if it was an XML file • Each query translates into a SQL statement • XPath • /Customer/Order[@OrderID=‘10692’] • XQuery • For $i in sql:table('Customers', 'CustomerID') Return <Customer ID = {$i/@CustomerID} Name = {$i/@ContactName}/>

16. Native XML StoreXML Data Type • XML data type • Native SQL type • Use for column, variable or parameter CREATE TABLE docs (id INT PRIMARY KEY, xDoc XML NOT NULL) • Store un-typed or typed XML instances • Well-formed and validation checks • Optional XML Schema enforcement • XML instances stored as LOB (2GB) • Efficient binary representation

17. Native XML StoreXML Index • Create XML index on XML column CREATE XML INDEX idx_1 ON docs (xDoc) • Creates indexes on tags, values & paths • Speeds up queries • Entire query is optimized • Same industry leading cost based optimizer • Indexes are used as available

18. XML Schema Support • XML Schema (W3C standard) • Rich mechanism for type definitions and validation constraints • Can be used to constrain XML documents • Benefits of typed data • Guarantees shape of data • Allows storage and query optimizations • XML type system • Store XML schemas in system meta-data

19. XML Query • XQuery: query XML documents and data • Standards-based: W3C working draft • In document 123, return section heading of section 3 and later SELECT id, xDoc::query(' for $s in /doc[@id = 123]//sec[@num >= 3] return <topic>{data($s/heading)}</topic> ') FROM docs

20. XML Data Modification • Insert, update, and delete XQuery extensions • XML sub-tree modification: • Add or delete XML sub-trees • Update values • Add a new section after section 1: UPDATE docs SET xDoc::modify('insert<section num=''2''> <heading>Background</heading> </section>after /doc/section[@num=1]')

21. XQuery and Updates XML View: Unification Model XML View • SQL Server “Yukon” XML data type • Use XQuery • Relational columns • Use SQL • XML View hides representation • Use XQuery against any data Customer Table

22. Choice of XML Technology • Native XML Technology • Very simple way of storing XML data • XML schema is optional • Document order is important • Query and modify XML data • Index XML data • XML View Technology • XML-centric programming model over tables • Schema for XML data required • Order not important • Bulk load XML data; decompose into tables

23. .NET Integration • Server side: SQLCLR • .NET hosted inside the database • Write stored procedures in C# • Use ADO programming model on the server the same way as on the client side • Create UDTs • Client side • Web Services • Dataset • Objectspaces

24. SQLCLR • Component reuse • Mainstream development experience • Familiar choice of programming languages and constructs • Leverage existing libraries and components • Seamless debugging and deployment • Deep integration with the engine

25. VB, C#, … Build Assembly: “TaxLib.dll” SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … SQL Queries: select sum(tax(sal,state) ) from Emp where county = ‘King’ SQL Server SQLCLR Development VS .NET Project Runtime hosted inside SQL

26. SQL or SQLCLR • Why SQL • Set-oriented queries • Large data sets • Why CLR • Computationally intensive • Complex behaviors • Reusable components with rich behaviors • Rich types (polygon)

27. Design Guidelines • T-SQL is best suited for data access • Relational programming model • Static compilation model • Optimized for data access • SLQCLR is for procedural programming and computation • IL compiled to x86 code at runtime, easily outperforms interpreted T-SQL • Compute-intensive business logic encapsulated as functions • Moving computation to where the data is • Data shipping cost goes away • Server CPU now used for user processing

28. Web Services Overview • Natural client side programming model • Turn your existing Stored Procedures into web Services • Messaging done according to SOAP 1.1 standard • Choose how to model results • XML • Objects • Dataset • Can run on database server or mid-tier • Integrated with Visual Studio

29. SOAP And Web Services • WSDL file describing each template and stored proc exposed • Tool to choose which templates and stored procedures to expose IIS/ISAPI SQL Server WSDL Client Message SP SOAP Message Template

30. Easy Programming Model • SQLXML generates WSDL automatically • Visual Studio.NET recognizes a Dataset • Retrieve results of a Stored Procedure and load into a Dataset in 1 line of code! Dim Service As New MyHost.MyWebService() Dim retval As Integer DataSet ds = Service.GetCustomer(Name)

31. User view DBA view Web Services – Decoupled Architecture SQL Server SQLXML Application Method call SQL query XmlReader Rowset Client Server Mid-Tier

32. Levels of Abstraction • Abstract the data source – XML View • Abstract the data access – HTTP queries • Abstract programming model – SQL Server Web Services

33. Data Model Transparency • XML Views – treat your relational data as if it was XML File • Use XML Query Languages • Perform XML Updates • No need to be a DBA, learn SQL, or database programming APIs/logic

34. Data Access Transparency • Access your data from any platform • HTTP queries - platform independent protocol • XML results – standard representation of data • Use SQL or XPath to query

35. Programming Model Transparency • Web services • Use from any platform • Call methods – get XML data returned • SQL Server stored procedure or XML Template is called • Results are transformed into XML form as needed • SQLCLR: • programming model is the same on the server and on the client • Loosely coupled architecture

36. WinFS: Structured Data Storage • Files vs. Databases • NTFS • Part of Operating System • Backup • Win32 APIs • Simple • Database • Optimized for querying • Reliability • Security • Transactions, multi-user, concurrency

37. WinFS • System Files • Exe • Dll • Swap • … • User Files • Documents • Pictures • Messages • …

38. User Files • Unstructured data • Not really unstructured – proprietary structure • Data broken into files • One level of granularity (HTML, Powerpoint) • Easy manipulation? • Proprietary formats • Need particular application to interpret files • No Sharing (Import/Export) • No relationships • Duplication of Data • Compatibility of data (Emails, Contacts,…)

39. WinFS • Database • Reliability, Concurrency, Speed, query optimization • Understanding schemas • Uniform Search • New APIs • SQL • Objects • Old APIs • Will be supported • Old files still work • Want to enable richer integration – provide translations mechanisms

40. WinFS Schemas • Unification on some level • Base schemas shipped with Windows • Play by the rules – all applications will be enabled with your data • Use extensions for your proprietary data • Convenient programming model • Shell supports libraries • Navigation (relationships) • Integration (Email body is a document)

41. WinFS Data Model • Items • Person, Document, Message, Meeting, etc. • Relationships • Author, Attachment, Meeting participant • Nested types • Address • Extensions • Proprietary data • Multityping • Inheritance

42. The Windows Schemas User Data Principals Locations Calendar Events Core Message (Email) Documents Annotations Media Notes Person Tasks Audio Videos Images Games . . . System Infrastructure System Tasks Explorer Config NaturalUI Programs Services Security Help Device . . . WinFSTypes Meta Base File Sync ShellSubscriptions . . .

43. My favorite query • What do I know about “John Smith” • Documents by/about him • Emails from him • His address • Phone calls from him • Annotations he added to my papers • Meetings with him

44. Creating API for a Schema • Create WinFS schema in XML format • Schema compiler generates API assembly • You can add your own “helper” members • The assemblies are installed into a WinFS store • WinFS types are registered as UDTs • Views and other database objects are created WinFS Schema Compiler WinFS Schema Code for StandardAPI CLR Complier API Classes Code for Helper Members

45. WinFS API Example using (ItemContext ic = new ItemContext()) {ic.Open(); Contact c = (Contact) ic.FindItem( typeof(System.Storage.Contact.Person), “DisplayName == ‘Bob Smith’”); c.DisplayName = ‘Robert Smith’;c.BirthDate = ‘01/04/1982’;ic.Update(); }

46. WinFS folders • Every Item must be in at least one folder • Item organization • Lifetime management • One file can be in multiple folders (reference counting) • User can add custom fields to folders

47. WinFS Message Schema (Example) • Message • Subject • -Time sent • -Type • -Status • Contact • Name • -Address • -Email • -Photo • Participant • DisplayName • -Type • -Address • Body • Preference • Account • Name • -Quota • -Type • -Server • Document • Title • -Size • -Type • - • Document • Title • -Size • -Type • - Component

48. Database Integration • XML • Object storage • Programming model • Development environment • Web • File system • Applications