Trends in database development xml net winfs
Download
1 / 105

Trends in Database Development: XML, .NET, WinFS - PowerPoint PPT Presentation


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 Presentationdownload

Trends in Database Development: XML, .NET, WinFS

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


Trends in database development xml net winfs l.jpg

Trends in Database Development: XML, .NET, WinFS

Alexander Vaschillo

Microsoft


Sql server stores everything l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

.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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

User view

DBA view

Web Services – Decoupled Architecture

SQL Server

SQLXML

Application

Method call

SQL query

XmlReader

Rowset

Client

Server

Mid-Tier


Universal storage l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

WinFS Data Model

  • Items (Entities)

  • Scalar types

  • Inline Types

  • Inheritance

  • Extensions

  • Relationships


Items l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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>


Inheritance hierarchy l.jpg

Inheritance Hierarchy


Extensions l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

WinFS API

  • Natural programming model

  • Language integration

  • Collections vs. SQL Queries

  • Database operations are hidden from a developer


Querying winfs l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

  • 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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

WinFS

  • System Files

    • Exe

    • Dll

    • Swap

  • User Files

    • Documents

    • Pictures

    • Messages


Winfs data model68 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Database Integration

  • XML

  • Object storage

  • Programming model

  • Development environment

  • Web

  • File system

  • Applications


Reports l.jpg

Reports

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


Winfs data model example l.jpg

WinFS Data model example

  • List of schema inheritance


Contacts l.jpg

Contacts

  • A common concept shared by everybody


Web services l.jpg

Web services

  • 4 slides.

  • Mention server side support


Slide76 l.jpg

Demo

  • SQLXML HTTP


New mapping77 l.jpg

New mapping


Sqlclr summary l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

.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 l.jpg

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 l.jpg

.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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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


Xml view example l.jpg

XML View Example


Example l.jpg

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 l.jpg

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 l.jpg

“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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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)


Media schemas l.jpg

Media Schemas


ad
  • Login