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

View by Category
About This Presentation
Title:

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

Description:

Leverage existing libraries and components. Seamless debugging and deployment ... Proprietary data. Multityping. Inheritance. Audio. Videos. Images. Games ... – PowerPoint PPT presentation

Number of Views:31
Avg rating:3.0/5.0
Slides: 83
Provided by: Ale178
Learn more at: http://users.cs.fiu.edu
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
About PowerShow.com