Transcript TrendsInDB

Trends in Database Development:
XML, .NET, WinFS
Alexander Vaschillo
Microsoft
Database development
Relational model is working well
Benchmarks
Security
Enhancements
New models
Hierarchical (XML)
Object
Ease of use
New uses
WinFS
New Directions in SQL Server
XML
Hierarchical, semi-structured data
Object oriented extensions
New programming models
.NET integration
Server
Client
New applications
WinFS
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?
Presentation format
Transport format
Platform independent
Text-based format
Schema with data
International standard not owned by any
one company
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
Scalable. Many to Many.
Changes in Implementation do not break each
other

Move data in a standardized format (XML)
Object
(XML)
App
Mapping
Logic
Application System
Mapping Data
Data System
The Two Worlds
Language
SQL
Data
storage
SQL
Server
Data
output
XPath
XQuery
XML
View
RowSet
Relational
world
SQLXML
–
Bringing
worlds
together
XML
Files
XML/
HTML
XML
world
Three Worlds
C#,C++,
VB
SQL
XPath
XQuery
Memory
SQL
Server
XML
Files
Map
Map
Object
RowSet
XML/
HTML
Object
world
Relational
world
XML
world
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
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
FOR XML Query
SQL Language Extension
SELECT…
FROM…
WHERE…
ORDER BY…
FOR XML ( raw |
auto [, ELEMENTS] |
nested [, ELEMENTS] |
explicit)
[, XMLData]
[, BINARY base64])
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
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>
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[@OrderID=‘10692’]
XQuery
For $i in sql:table('Customers', 'CustomerID')
Return
<Customer
ID = {$i/@CustomerID}
Name = {$i/@ContactName}
/>
Native XML Store
XML 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
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 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 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, xDoc::query('
for $s in
/doc[@id = 123]//sec[@num >= 3]
return <topic>{data($s/heading)}</topic>
')
FROM docs
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]')
XML View: Unification Model
XML View
XQuery and
Updates
Customer
Table
CustomerID
ContactName
Street
City
SQL Server “Yukon” XML data type
Use XQuery
Relational columns
Use SQL
XML View hides representation
Use XQuery against any data
XML data
type
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
.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
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#, …
VS .NET
Project
Build
Runtime
hosted inside
SQL
SQL Queries:
select
sum(tax(sal,state) )
from Emp where county
= ‘King’
Assembly:
“TaxLib.dll”
SQL Data Definition:
create assembly …
create function …
create procedure …
create trigger …
create type …
SQL Server
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)
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
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
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
WSDL
Client
SOAP
Message
SP
Message
Template
SQL
Server
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
Method call
Application
SQL query
SQLXML
XmlReader
Client
Rowset
Mid-Tier
SQL Server
Server
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
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
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
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
Files vs. Databases
NTFS
Part of Operating System
Backup
Win32 APIs
Simple
Database
Optimized for querying
Reliability
Security
Transactions, multi-user, concurrency
WinFS
System Files
Exe
Dll
Swap
…
User Files
Documents
Pictures
Messages
…
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
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
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)
WinFS Data Model
Items
Person, Document, Message, Meeting, etc.
Relationships
Author, Attachment, Meeting participant
Nested types
Address
Extensions
Proprietary data
Multityping
Inheritance
The Windows Schemas
User Data
Principals
Locations
Calendar Events
Core
Message (Email)
Documents
Annotations
Media
Notes
Person Tasks
System
System Tasks
Explorer
Config
NaturalUI
Programs
Audio
Videos
Images
Games
...
Infrastructure
Services
Security
Help
Device
...
WinFSTypes
Meta
Base
File
Sync
ShellSubscriptions
...
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
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
WinFS
Schema
Compiler
Code for
Standard
API
Code for
Helper
Members
CLR
Complier
API
Classes
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
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
WinFS Message Schema (Example)
Message
-Subject
-Time sent
-Type
-Status
Contact
Participant
-DisplayName
-Type
-Address
-Name
-Address
-Email
-Photo
Body
- Preference
Account
Document
Document
-Name
-Quota
-Type
-Server
-Title
-Size
-Type
-
-Title
-Size
-Type
-
Component
Database Integration
XML
Object storage
Programming model
Development environment
Web
File system
Applications
Reports
Example of table report and HTML report.
Sales by quarter.
WinFS Data model example
List of schema inheritance
Contacts
A common concept shared by everybody
Web services
4 slides.
Mention server side support
Demo
SQLXML HTTP
New mapping
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”
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
Data is relational is objects is XML is Data
Technology
Strengths
Use if…
DataSet and
DataReader
in ADO.NET
•Relational (tabular) model
•Highest performance
•Explicit control
•Fully exposes database
functionality
•You are comfortable with the
relational model
•You require maximum
control/performance/functionality
•You are using UI bound controls
ObjectSpaces
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
SQLXML in
ADO.NET
•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
•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
.NET Framework Integration
Key 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
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
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…
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
XML Views
Overview
Default XML view of relational data
User-defined XML views
Specified using schema mapping
Decouples mapping from domain specific schemas
Bul
k
loa
d
First Name
XML View
Last Name
Address
XQuery,
Updates
Phone
XML data
type
Middle-Tier XML Views
XML View
XQuery and
Updates
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
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
Customer Table
Customer
CustomerID
CustomerID
ContactName
name
Street
City
Street
Country
City
Country
XSD - XML
Schema Definition
MSD - Mapping
Schema Definition
RSD - Relational
Schema Definition
Example
CLR Assembly
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
Nullable="true"> Name="Employment“
BaseType="WinFS.Relationship“
<RelationshipType
Name="Employment“
AllowsHolding="true“
BaseType="WinFS.Relationship“
AllowsEmbedding="false“
AllowsHolding="true“
AllowsReference="true">
AllowsEmbedding="false“
<Property
Name=“IrisScan”
AllowsReference="true">
Type=“WinFS.FileStream”
<Property
Name=“IrisScan” …/>
</ItemType>
Type=“WinFS.FileStream” …/>
</ItemType>
Table View of Person
Name
ItemId
FirstName LastName
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;
…
}
Addresses
IrisScan
Street City State Zip
Street City State Zip
Street City State Zip
NTFS stream
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
T/SQL
XML
Models
APIs
Objects
Services
Schemas
Synchronization People
(WinFS, …)
(Rules, …)
Core WinFS
Operations
…
Data Model
Items
Relationships
Filesystem Srvcs
(Handlers, …)
Relational Engine
NTFS
Extensions
Framework
InfoAgent
Documents
“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
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
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
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
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 filebacked 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
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
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
WinFS
Schema
WinFS
Schema
Compiler
C# code
for UDTs
Schema
Assemblies
CLR
Complier
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,
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
WinFS Type Example
using Contact =
System.Storage.Contact;
using Core = System.Storage.Core;
using Base = System.Storage;
Item
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;
Contact
Person Organization
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,
SQL OR Extensions - Smart
Serialization
Example:
SELECT FirstName, LastName, …
FROM
[System.Storage.Contact.Store].Conta
ct c
WHERE 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 supports a generic collection type
MULTISET<T>
Properties can be declared using
collections
Treated from SQL as “nested table”
Queryable using UNNEST table valued
function
SELECT c.FirstName, c.LastName,
A.addr.City, A.addr.Zip
FROM
[System.Storage.Contact.Store].Conta
Media Schemas
Media.Document
...
Distributor:Contact
►ContentDistributor.Document
Art:Document
►EffectiveBackCoverArt.Document
Art:Document
►EffectiveFrontCoverArt.Document
Logo:Document
►MetadataProviderLogo.Document
Video.VideoRecord
Audio.CachedAlbum
...
Clip:VideoClip
►Clips.Video
...
Record:AudioRecord
◄TrackAlbum.Album
Video.RecordedTV
Audio.PlatterTrack
...
...
Audio.AudioRecord
Audio.PlayList
...
Metadata:CachedTrack
►SuggestedMetadata.Record
Album:CachedAlbum
►TrackAlbum.Record
...
Image.Picture
...
Contact:Contact
►ContactsInPicture.Picture
OtherVersion:Picture
◄►PictureOtherVersions.Picture
Audio.Track
Audio.CachedTrack
Image.Photo
...
Habits:ListeningHabits
◄ListenedTrack.Track
...
Record:AudioRecord
◄SuggestedMetadata.Metadata
...
Location:Location
►PhotoLocation.Photo