Trends%20in%20Database%20Development:%20XML,%20.NET,%20WinFS

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)
50
Reports

• Example of table report and HTML report. Sales by
  quarter.

51
WinFS Data model example

• List of schema inheritance

52
Contacts

• A common concept shared by everybody

53
Web services

• 4 slides.
• Mention server side support

54
Demo

• SQLXML HTTP

55
New mapping
56
SQLCLR Summary

• Richer programming model in database
• Any .NET language, selected .NET frameworks
• Tight integration with VS.NET
• Deep integration SQL and .NET Runtime
• Basis for security, reliability, scalability,
  performance
• ADO.NET provider inside SQL
• Common middle- and server-tier data access
• Manageable and serviceable
• Scripts, metadata, profiler events, performance
  counters

57
Debugging SQL Server Yukon

• Seamlessly step cross-language
• T-SQL and SQL/CLR code
• Set breakpoints anywhere
• Both local and remote debugging
• Inspect anything
• SQL types
• UDTs
• Managed objects

58
ADO.NET Data Access Support
Data is relational is objects is XML is Data
Technology Strengths
Use if

• You are comfortable with the relational model
• You require maximum control/performance/functional
  ity
• You are using UI bound controls

• Relational (tabular) model
• Highest performance
• Explicit control
• Fully exposes database functionality

DataSet and DataReader in ADO.NET

• Business level objects
• Relational mapping via metadata
• Decoupled from database schema
• Smaller working set than other object abstractions

• You need a strong business object layer
• You know the shape of the results you want to
  work with

ObjectSpaces in ADO.NET

• You need to query data from XML data sources e.g.
  XML Web Services
• You use vertical industry XML schemas for content
  publishing e.g. XBRL, RIXML, FinXML
• You need to load XML documents into database
  tables
• You are using UI bound controls for XML

• Interoperability. Format for the Web B2B, A2A
• Sparse (semi-structured) data
• XML Services e.g. XQuery, XSD
• Relational mapping via metadata
• Decoupled from database schema

SQLXML in ADO.NET
59
.NET Framework IntegrationKey Features

• Server-side programming environment for
• User Defined Functions, Stored Procedures,
  Triggers
• User Defined Types, user defined Aggregates
• In-Proc Data Access (ADO.NET V2 - Whidbey)
• Common ADO .NET Programming Model
• Both Mid-tier/data tier
• Security
• Integration of SQL and CLR security
• Three levels of code access security
• Safe, External-Access (verifiable), Unsafe
• Tight integration with Visual Studio
• Authoring, debugging, deployment, profiling

60
Authoring/Debugging/Deploying

• New Visual Studio project type in Whidbey for
  Yukon managed code
• Server debug integration
• Full debugger visibility
• Set breakpoints anywhere
• Single step support
• Between languages T-SQL, C, VB, C
• Between deployment tiers
• E.g. ASP.NET, through SQL Server stored proc
  call, back to mid-tier

61
.NET IntegrationKey Theme Choice Control

• Choice of where to run logic
• Database, for logic that runs close to data
• Mid-tier, for logic that scales out
• Symmetric programming model
• Leverage skills mid-tier server
• Safe extended stored proc replacement
• Choice of programming language
• C, VB.NET, Managed C, for a safe, modern
  execution environment
• T-SQL enhancements continue
• Right choice for data-intensive procedures

62
XML ScenariosSemi-structured storage
First Name Last Name Address Phone XML data type

• XML Datatype
• Loosely structured data
• Data with a dynamic schema
• XML Views
• Mixed data structured/unstructured
• XML stores w/o relational support challenged

63
XML ViewsOverview

• Default XML view of relational data
• User-defined XML views
• Specified using schema mapping
• Decouples mapping from domain specific schemas

First Name Last Name Address Phone XML data type

64
Middle-Tier XML Views
XML View
Customer Table
CustomerID ContactName Street City XML data type

• SQL Server Yukon XML data type
• Sparse (semi-structured) data
• XML Views
• Mixed data structured/unstructured
• XML View hides representation

65
Middle-Tier XML Views

• Declarative syntax for mapping between XML and
  relational data
• Support for common database design patterns
• Stored proc support
• Extensibility mechanism with SQL queries.
• XQuery over XML Views
• for i in mapview(nwind.msd)//Customer
• where i/CustomerID ALFKI
• return i
• Identical mapping technology used by ADO.NET
  Objectspaces for objects

66
XML View Example
67
Example
Example
68
API Examples

• VB Managed API
• Dim personItem As Person
• For Each personItem In Person.FindAll(context,
  PersonalNames.SurnameSmith)
• ...
• Next

• T-SQL
• select p._Item from System.Storage.Contacts.Store
  .Person p
• where exists (select from unnest
  (p.PersonalNames) n
• where n.SurnameSmith')

69
WinFS ServicesFilesystem

• File-backed Items
• Items with traditional filestream parts within
• Uses real NTFS streams and file handles
• Any file can be imported into WinFS as a
  File-back Item
• WinFS is backwards compatible with Win32

APIs
XML
T/SQL
Objects
Services
Schemas
People
Synchronization(WinFS, )
Documents
InfoAgent (Rules, )

Core WinFS
Data Model
Items
Operations
Relationships
Filesystem Srvcs (Handlers, )
Extensions
Relational Engine
NTFS
70
Finding Items In WinFS

• OPath
• Simple query language in the object domain
• Uses paradigm familiar to OO programmers
• Supports
• Simple equalities
• Wild cards
• IN, LIKE operators
• Date expressions
• Traverse relationships
• Grouping expressions
• Simple math expressions (, -)
• Example
• (DisplayName Sean Chai) (DisplayName like
  K )

71
User Benefits

• Find my stuff
• The big presentation I got from Toby I was
  working on last week
• One view of data
• IM Toby, Hotmail Toby, Corporate Toby,
• Exposing relationships
• Doc authors, Meeting attendees, Meeting
  Locations, Location occupants

72
Developer Benefits

• Populated, well-defined data definitions (types)
• You dont have to build your own store or API
• Applications can create and share data
• The storage subsystem is Extensible
• Its much easier to build a smart connected
  application
• Applications can create and share types

73
Metadata HandlersMotivation

• Promotion
• End-users dont need to re-tag their content
  with metadata
• WinFS automatically pulls it out of files
• Existing applications continue to write to files
• Appropriate metadata surfaces in WinFS items
• Demotion
• WinFS apps use one API to write pure WinFS and
  file-backed items
• WinFS demotes metadata back to files
• Allows interop between legacy and new
  applications
• Provides fidelity of metadata through moves/copies

74
Data Requirements in Next Generation Applications

• Model complex objects
• Complex structure
• Inheritance
• Unstructured, XML and Structured data
• Rich Relationships
• Value-based
• Link based
• WinFS provides a built in model with more
  services for complex objects
• Rich and Common Query
• Common across client and server
• Common across different typed of data SQL,
  Objects, XML
• Granular operations
• Copy, Move
• Backup/Restore
• Security
• Rich organization
• Hierarchical Namespace
• Active Notifications and Data Synchronization
• Integrated Business logic

75
Schema compilation process

• WinFS schemas are defined using XML syntax
• The WinFS schema compilation process generates C
  code from the WinFS Schema file
• The C source files are compiled into assemblies
• The assemblies are installed into a WinFS store
• WinFS types are registered as UDTs
• Views and other database objects are created

CLR Complier
Schema Assemblies
WinFS Schema
WinFS Schema Compiler
C code for UDTs
WinFS Store
76
Data Model Mapping Overview

• A WinFS schema is mapped to a SQL schema
• A CLR class is generated for each Item, Nested,
  Extension Relationship type
• The classes are registered as SQL User Defined
  Types (UDTs)
• Search views are provided for each Item,
  Extension and Relationship type
• Updates are enabled through the WinFS Update API
  operations
• CreateItem, CreateRelationship, CreateExtension
• UpdateItem, UpdateRelationship, UpdateExtension
• DeleteRelationship, DeleteExtension

77
WinFS Data Model

• The WinFS Data Model describes
• the shape of the data stored in WinFS
• the constraints on the data
• associations between data
• WinFS world is comprised of items, relationships
  and extensions
• Items are the primary objects that applications
  work on
• Items can be associated with other items via
  relationships
• Items can be extended with extensions (or
  subclass)

78
WinFS Type Example

• using Contact System.Storage.Contact
• using Core System.Storage.Core
• using Base System.Storage
• type Contact.Address Base.NestedType
• string Street
• string City
• string Zip
• type Contact.Person Core.Contact
• datetime BirthDate
• binary Picture
• Address BirthAddress
• MultiSetltAddressgt Addresses
• type Contact.Organization Core.Contact string
  OrganizationName

Item
Contact
Organization
Person
79
SQL OR Extensions - Smart Serialization

• SQLCLR types leverage a custom serialization
  library (SL)
• Efficient access to properties of embedded
  objects
• Avoids object construction or method invocations
  for simple property getters and setters
• Property and field access translates to compiled
  field accessors
• New structured serialization format
• Understands inheritance, embedded types,
  collection types
• Internal to SQL
• Provides record like performance for accessing
  object properties

80
SQL OR Extensions - Smart Serialization

• ExampleSELECT FirstName, LastName, FROM
  System.Storage.Contact.Store.Contact cWHERE
  BirthAddress.City Seattle
• Fetching HomeAddress.City does not require the
  materialization of the Address object
• Properties retrieved by directly cracking the
  serialized form

81
SQL OR Extensions - Collections

• SQL supports a generic collection type
  MULTISETltTgt
• Properties can be declared using collections
• Treated from SQL as nested table
• Queryable using UNNEST table valued
  functionSELECT c.FirstName, c.LastName,
  A.addr.City, A.addr.ZipFROM System.Storage.Conta
  ct.Store.Contact cCROSS APPLY
  UNNEST(c.Addresses) AS A(addr)
• Current investigating replacing the MultiSet
  collection with IListltTgt (to support ordering)

82
WinFS Programming Surface

• WinFS provides multiple programming surfaces
• Object APIs for accessing items
• SQL Access (read) to items
• Win32 access for file access
• APIs for accessing XML and File data
• Item (WinFS) API provides strongly typed data
  classes for items
• Generic read data access available through
  ADO.Net
• Win32 API offers file stream and directory access

Applications
WinFS API
GenericData Access
Win32 API
WinFS
83
Media Schemas