trends in database development xml net winfs
Download
Skip this Video
Download Presentation
Trends in Database Development: XML, .NET, WinFS

Loading in 2 Seconds...

play fullscreen
1 / 105

TrendsDBWinFs - PowerPoint PPT Presentation


  • 192 Views
  • Uploaded on

Trends in Database Development: XML, .NET, WinFS. Alexander Vaschillo Microsoft. SQL Server Stores Everything. Overall direction: Storing stuff whatever this stuff is Different data models Relational Hierarchical (XML) Object (Graphs) Files. Latest in SQL Server. XML

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'TrendsDBWinFs' - Solomon


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql server stores everything
SQL Server Stores Everything
  • Overall direction: Storing stuff whatever this stuff is
  • Different data models
    • Relational
    • Hierarchical (XML)
    • Object (Graphs)
    • Files
latest in sql server
Latest in SQL Server
  • XML
    • Mapping to relational (SQLXML)
    • Native (XML Datatype)
  • Objects
    • Mapping to relational (ObjectSpaces)
    • Native (CLR UDT)
  • .NET integration
    • Server
    • Client
  • Using all of the above
    • WinFS
the two worlds

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

xsd mapping example
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>

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

new mapping
New Mapping

Customer

Customer

Name

Name

R

Purchase

Product

Product

PartNum

PartNum

Data Model Graph

Mapping

Data Model Graph

native xml store xml data type
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
native xml store xml index
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
xml query
XML Query
  • XQuery: query XML documents and data
    • Standards-based: W3C
  • 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

xml view unification model

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

net integration
.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
sqlclr
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
sqlclr development

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

web services overview
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
easy programming model
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)

web services decoupled architecture

User view

DBA view

Web Services – Decoupled Architecture

SQL Server

SQLXML

Application

Method call

SQL query

XmlReader

Rowset

Client

Server

Mid-Tier

universal storage
Universal Storage
  • SQL server
    • Abstracts data model from the user
    • Abstracts data access programming model from the user
    • Abstracts its existence from the user
  • Can it replace file system?
user files
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,…)
winfs
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
winfs schemas
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)
the windows schemas
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

. . .

winfs data model
WinFs Data Model
  • Common data model
  • Simple but rich
  • Common programming model for all applications
  • Unified store - unified access
  • Subsume relational concepts
  • Rich object abstraction for data
  • Semantic relationships
  • Align with CLR
common data model
Common Data Model
  • Map to CLR - framework
  • Map to SQL – add some abstractions
  • Express data in XML
  • Combine SQL 99, CLR, XSD, UML
    • Nested tables
    • Relationships
    • Scalar types
  • Provide mapping to other data models
sql 99 data model
SQL 99 Data Model
  • Too implementation specific – not a good application level abstraction
  • Tables, not types are first class
    • Identity
    • Operational semantics (copy, etc.)
  • Integrate SQL with Programming language rather than map from objects to DB
  • No high-level relationship support
    • Can use low-level concepts (foreign keys)
clr data model
CLR Data Model
  • Framework on top
    • Constrain
    • Enhance with additional functionality
  • Not designed for persistence
    • References are not durable
    • Foreign keys can be persisted
  • No set–oriented query capabilities
  • No high-level relationship concepts
    • Can use collections to implement some
  • No support for BLOBs
xml xsd data model
XML/XSD Data Model
  • Use XML syntax, but not semantics
  • No Relationships in XSD Data Model
  • Too many concepts not useful for us and hard to map to CLR and SQL
  • Different set of scalar types
  • Complex mechanisms for type extension
    • Not compatible with CLR
winfs data model28
WinFS Data Model
  • Items (Entities)
  • Scalar types
  • Inline Types
  • Inheritance
  • Extensions
  • Relationships
items
Items
  • Have identity
  • Smallest referenceable unit
  • Have Properties and Behaviors (methods)
  • Can exist independently, can be copied, etc.
  • Person, Message, Document, Audio,...

<EntityType Name="Person">

<Property Name="Name" Type="WinFS.String"/>

<Property Name="Age" Type="WinFS.Int32" Default="1"/>

<Property Name="Picture" Type="WinFS.Binary"/>

<Property Name="Addresses" Type="Array(Address)"/>

</EntityType>

scalar types
Scalar Types
  • Used for properties on an Item (Relatonship)
  • Carefully chosen subset of SQL and CLR types
  • String, Boolean, Binary, Byte, Int16, Int32, Int64, Single, Double, Decimal, DateTime, Guid, XML, Stream.
  • Enumeration

<Enumeration Name="Gender" >

<EnumerationMember Name="Male" />

<EnumerationMember Name="Female" />

</Enumeration>

  • Arrays and Sets
inline type
Inline type
  • A structure without identity
  • Not referenceable
  • Has to be contained in an Entity (or Relationship)
  • Example: Address

<InlineType Name="Address">

<Property Name="Street" Type="String" Nullable="false"/>

<Property Name="City" Type="String" Nullable="false"/>

...

</InlineType>

inheritance
Inheritance
  • Single inheritance (Items, Inline types)
  • Substitutability

<Type Name="Name" >

<Property Name="FirstName" Type="WinFS.String" />

<Property Name="LastName" Type="WinFS.String" />

</Type>

<Type Name="NameWithMiddleInitial" BaseType="Name" >

<Property Name="MiddleInitial" Type=“WinFS.String" />

</Type>

<Type Name="Person" BaseType="System.Storage.Item" >

<Property Name=" PersonalName" Type="Array(Name)" />

</Type>

extensions
Extensions
  • Non-invasively adds more structures to an existing Item
  • Multiple extensions can be added independently
  • Must be attached to an Item and live with it

<EntityExtensionType Name=”MSNData” ExtendsType=”PersonItem” >

<Property Name="Passport" Type="PassportData" />

<Property Name="MSNId" Type="Guid" />

</EntityExtentionType>

relationships
Relationships
  • Document-Author, Message-Participant, Album-Record
  • Association and Composition
  • Relate two Items
  • May have properties
  • Support cardinalities (1:1, m:1, 1:m, m:m)
  • May control lifetime
  • Based on common values or identity
relationship example
Relationship Example

<EntityType Name="Customer" ...> ...

</EntityType>

<EntityType Name="Order" ...>

<Property Name="CustRef" Type="Ref(Customer)"/> ...

</EntityType>

<Association Name="OrderCustomer" >

<End Role="OrderRole" Type="Order" Multiplicity="*" />

<End Role="CustomerRole" Type="Customer" OnDelete="Cascade” Multiplicity="1" />

<Reference FromRole=”OrderRole” ToRole=”CustomerRole” Property=”CustRef”/>

</Association>

relationship example37
Relationship Example

<Association Name="DocumentAuthor" >

<End Role="DocumentRole" Type="Document" Multiplicity="*" />

<End Role="ContactRole" Type="Contact" Multiplicity="1"/>

<Condition>

DocumentRole.Author = ContactRole.Name

</Condition >

</Association>

data model mapping
Data Model Mapping
  • A WinFS schema is mapped to a SQL schema
  • Types are mapped to CRL classes in the storage (UDT), and CLR API classes
  • Classes are automatically created based on type definition
  • Views are generated for each type
  • Relationships: UDT vs. metadata
  • Schema becomes a namespace in the API
winfs api
WinFS API
  • Natural programming model
  • Language integration
  • Collections vs. SQL Queries
  • Database operations are hidden from a developer
querying winfs
Querying WinFS

StorageContext sc = new StorageContext();

StorageSearcher<PersonItem> searcher =

sc.Items.FilterByType<PersonItem>().

Filter(“Exists(Names[LastName=’Smith’]”);

PersonItem p1 = searcher.GetFirst();

or

foreach (PersonItem p in searcher)

{ ...

}

query composition
Query Composition

StorageSearcher<MessageItem> messages =

sc.Items.FilterByType<MessageItem>().

Filter("Subject LIKE \'%Academic Days%\'");

StorageSearcher<StorageRecord> myMessages

= messages.Project("Subject, Size ").

Sort(“Size desc”);

foreach( StorageRecord m in myMessages)

{

string s = m[“Subject”];

int size = m[“Size”];

}

item creation
Item Creation

Item root = sc.GetRootItem();

PersonItem person = new PersonItem();

person.DateOfBirth = “11/01/1960";

FullName fullName = new FullName();

fullName.FirstName = “John”;

fullName.LastName = “Smith”;

person.Names.Add(fullName);

root.Children.Add(person);

sc.SaveChanges();

relationship navigation
Relationship Navigation

StorageSearcher<OrganizationItem> organizations =

sc.Items.WithType<OrganizationItem>().

Filter("Keywords[Value=‘Financial\']");

StorageSearcher<PersonItem> employees = EmploymentRelation.GetEmployees(organizations);

StorageSearcher<DocumentItem> documents = DocumentAuthorRelation.GetDocuments(employees);

foreach( DocumentItem document in documents)

{ ...

}

notifications
Notifications

PersonItem person =

sc.Items.FilterByType<PersonItem>().

Filter(“Exists(Names[LastName=’Smith’]”).GetFirst();

StoreWatcherOptions Opts=new StoreWatcherOptions();

Opts.NotifyModified = true;

StoreWatcher w = person.GetWatcher( Opts );

w.StoreObjectChanged += new StoreEventHandler( MyHandler );

void MyHandler( Object sender, StoreEventArgs e )

{

}

creating api for a schema
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

CLR

Complier

Schema

Assemblies

WinFS

Schema

Compiler

C# code

for UDTs

WinFS

Schema

Code for

Standard API

CLR

Complier

API

Classes

Code for

Helper Members

winfs message schema example
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

my favorite query
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
choice of xml technology
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
file migration to winfs
File Migration to WinFS
  • File promotion / demotion
  • Write conversion routines for common file types (gif, eml, doc, pdf,...)
  • Works for non-WinFS stores (removable media, etc.)
  • WinFS apps use one API to write pure WinFS and file-backed items
  • Allows interop between legacy and new applications
database development
Database development
  • Relational model is working well
    • Benchmarks
    • Security
    • Enhancements
  • New models
    • Hierarchical (XML)
    • Object
  • Ease of use
  • New uses
    • WinFS
why new data models
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
why xml
Why XML?
  • Presentation format
  • Transport format
  • Platform independent
  • Text-based format
  • Schema with data
  • International standard not owned by any one company
http access via url
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

loosely coupled systems

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

sqlxml from 10 000 feet
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
xml views
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
xml schema support
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
xml data modification
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]\')

design guidelines
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
soap and web services
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

levels of abstraction
Levels of Abstraction
  • Abstract the data source – XML View
  • Abstract the data access – HTTP queries
  • Abstract programming model – SQL Server Web Services
data model transparency
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
data access transparency
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
programming model transparency
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
winfs structured data storage
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
winfs67
WinFS
  • System Files
    • Exe
    • Dll
    • Swap
  • User Files
    • Documents
    • Pictures
    • Messages
winfs data model68
WinFS Data Model
  • Items
    • Person, Document, Message, Meeting, etc.
  • Relationships
    • Author, Attachment, Meeting participant
  • Nested types
    • Address
  • Extensions
    • Proprietary data
    • Multityping
  • Inheritance
winfs api example
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();

}

winfs folders
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
database integration
Database Integration
  • XML
  • Object storage
  • Programming model
  • Development environment
  • Web
  • File system
  • Applications
reports
Reports
  • Example of table report and HTML report. Sales by quarter.
winfs data model example
WinFS Data model example
  • List of schema inheritance
contacts
Contacts
  • A common concept shared by everybody
web services
Web services
  • 4 slides.
  • Mention server side support
slide76
Demo
  • SQLXML HTTP
sqlclr summary
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
debugging sql server yukon
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
ado net data access support
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/functionality
  • 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

net framework integration key features
.NET Framework IntegrationKey Features
  • Server-side programmingenvironment 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
authoring debugging deploying
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
net integration key theme choice control
.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
xml scenarios semi structured storage
XML ScenariosSemi-structured storage…
  • XML Datatype
    • Loosely structured data
    • Data with a dynamic schema
  • XML Views
    • Mixed data – structured/unstructured
    • XML stores w/o relational support challenged
xml views overview

Bulk

load

XML View

XQuery,

Updates

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

XQuery and

Updates

Middle-Tier XML Views

XML View

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

Customer Table

middle tier xml views87
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 map:view(“nwind.msd”)//Customer

where $i/CustomerID = “ALFKI”

return $i

  • Identical mapping technology used by ADO.NET Objectspaces for objects
example

CLR Assembly

CLR Assembly

public partial class Person_t : Item {

private String _AddressLine;

private PersonalNames _Name;

public String AddressLine {

get { return _AddressLine; }

set { _AddressLine = value; }

}

public Relationship Employment {

get { …}

// cached or select statement

set { …}

private FileStream_IrisScan;

}

public partial class Person_t : Item {

private String _AddressLine;

private PersonalNames _Name;

public String AddressLine {

get { return _AddressLine; }

set { _AddressLine = value; }

}

public Relationship Employment {

get { …}

// cached or select statement

set { …}

private FileStream_IrisScan;

}

WinFS Schema

WinFS Schema

<ItemType Name="Person”

BaseType="Core.Contact" ... >

<Property Name="PersonalNames”

Type="MultiSet“

MultiSetOfType="FullName“

Nullable="true">

<Property Name="AddressLine“

Type="WinFS.String" Nullable="true">

<RelationshipType Name="Employment“

BaseType="WinFS.Relationship“

AllowsHolding="true“

AllowsEmbedding="false“

AllowsReference="true">

<Property Name=“IrisScan”

Type=“WinFS.FileStream” …/>

</ItemType>

<ItemType Name="Person”

BaseType="Core.Contact" ... >

<Property Name="PersonalNames”

Type="MultiSet“

MultiSetOfType="FullName“

Nullable="true">

<Property Name="AddressLine“

Type="WinFS.String" Nullable="true">

<RelationshipType Name="Employment“

BaseType="WinFS.Relationship“

AllowsHolding="true“

AllowsEmbedding="false“

AllowsReference="true">

<Property Name=“IrisScan”

Type=“WinFS.FileStream” …/>

</ItemType>

Street

Street

City

City

State

State

Zip

Zip

Street

Street

Street

Street

City

City

City

City

State

State

State

State

Zip

Zip

Zip

Zip

LastName

LastName

FirstName

FirstName

Table View of Person

Table View of Person

IrisScan

IrisScan

Addresses

Addresses

Name

Name

ItemId

ItemId

NTFS stream

NTFS stream

Example

Example
api examples
API Examples
  • VB Managed API

Dim personItem As Person

For Each personItem In Person.FindAll(context, “PersonalNames.Surname=’Smith’)

...

Next

  • T-SQL

select p._Item from [System.Storage.Contacts.Store].[Person] p

where exists (select * from unnest (p.PersonalNames) n

where n.Surname=‘Smith\')

winfs services filesystem
“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

Models

Framework

WinFS ServicesFilesystem

APIs

Objects

T/SQL

XML

Services

Schemas

People

Synchronization(WinFS, …)

Documents

InfoAgent (Rules, …)

Core WinFS

Data Model

Items

Operations

Relationships

Filesystem Srvcs (Handlers, …)

Extensions

Relational Engine

NTFS

finding items in winfs
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%’ )”
user benefits
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…
developer benefits
Developer Benefits
  • Populated, well-defined data definitions (types)
  • You don’t have to build your own store or API
  • Applications can create and share data
  • The storage subsystem is Extensible
    • It’s much easier to build a smart connected application
    • Applications can create and share types
metadata handlers motivation
Metadata HandlersMotivation
  • Promotion
    • End-users don’t 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
data requirements in next generation applications
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
    • Optimistic concurrency control and API mapping
creating api for a schema97
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

schema compilation process
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

data model mapping overview
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
winfs data model100
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)
winfs type example
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;

MultiSet<Address> Addresses;…

}

type Contact.Organization : Core.Contact {string OrganizationName;…

}

Item

Contact

Person

Organization

sql or extensions smart serialization
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
sql or extensions smart serialization103
SQL OR Extensions - Smart Serialization
  • Example:SELECT 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
sql or extensions collections
SQL OR Extensions - Collections
  • SQL supports a generic collection type MULTISET<T>
  • 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.Contact.Store].Contact cCROSS APPLY UNNEST(c.Addresses) AS A(addr)
  • Current investigating replacing the MultiSet collection with IList<T> (to support ordering)
ad