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?sqlselectfromCustomersF
  ORXMLAutorootroot
• XML View
• http//server/vroot/schema.xsd/Customer_at_ID'ALFK
  I'?params
• Template
• http//server/vroot/template.xml?params

7
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
The Two Worlds
SQL
Language
SQL Server
Data storage
RowSet
Data output
Relational world
9
Three Worlds
SQL
C,C, VB
XPath XQuery
XML Files
SQL Server
Memory
Map
Map
RowSet
Object
XML/ HTML
XML world
Relational world
Object 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

• ltxsdschema xmlnsxsd"http//www.w3.org/2001/XML
  Schema"
• xmlnsmsdata"urnschemas-microsoft-commapp
  ing-schema"gt
• ltxsdelement name"Customer" msdatarelation"Cu
  stomers"gt
• ltxsdcomplexTypegt
• ltxsdsequencegt
• ltxsdelement name"Order"
  msdatarelation"Orders"gt
• ltxsdannotationgtltxsdappinfogt
• ltmsdatarelationship
• parent"Customers"
  parent-key"CustomerID"
• child"Orders"
  child-key"CustomerID" /gt
• lt/xsdappinfogtlt/xsdannotationgt
• ltxsdcomplexTypegt
• ltxsdattribute name"OrderDate"
  type"xsddateTime"/gt
• lt/xsdcomplexTypegt
• lt/xsdelementgt
• lt/xsdsequencegt
• ltxsdattribute name"CustomerID" /gt
• lt/xsdcomplexTypegt
• lt/xsdelementgt

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_at_OrderID10692
• XQuery
• For i in sqltable('Customers', 'CustomerID')
  Return ltCustomer ID i/_at_CustomerID
  Name i/_at_ContactName/gt

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, xDocquery('
• for s in
• /doc_at_id 123//sec_at_num gt 3
• return lttopicgtdata(s/heading)lt/topicgt
• ') 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 xDocmodify('insertltsection
  num''2''gt ltheadinggtBackground
  lt/headinggt lt/sectiongtafter
  /doc/section_at_num1')

21
XML View Unification Model
XML View
Customer Table
CustomerID ContactName Street City XML data type

• SQL Server Yukon XML data type
• Use XQuery
• Relational columns
• Use SQL
• XML View hides representation
• Use XQuery against any data

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
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
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

• Audio
• Videos
• Images
• Games
• . . .

• Principals
• Locations
• Calendar Events
• Core
• Message (Email)

• Documents
• Annotations
• Media
• Notes
• Person Tasks

System
Infrastructure

• WinFSTypes
• Meta
• Base
• File
• Sync
• ShellSubscriptions
• . . .

• System Tasks
• Explorer
• Config
• NaturalUI
• Programs

• Services
• Security
• Help
• Device
• . . .

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), DisplayN
  ame Bob Smith)
• c.DisplayName Robert Smithc.BirthDate
  01/04/1982ic.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

• Document
• Title
• -Size
• -Type
• -

• Document
• Title
• -Size
• -Type
• -

• Account
• Name
• -Quota
• -Type
• -Server

Component
48
Database Integration

• XML
• Object storage
• Programming model
• Development environment
• Web
• File system
• Applications

49
(No Transcript)