1 / 49

Trends in Database Development: XML, .NET, WinFS

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

lara
Télécharger la présentation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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?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

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

  8. 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

  9. 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

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

  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[@OrderID=‘10692’] • XQuery • For $i in sql:table('Customers', 'CustomerID') Return <Customer ID = {$i/@CustomerID} Name = {$i/@ContactName}/>

  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, xDoc::query(' for $s in /doc[@id = 123]//sec[@num >= 3] return <topic>{data($s/heading)}</topic> ') 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 xDoc::modify('insert<section num=''2''> <heading>Background</heading> </section>after /doc/section[@num=1]')

  21. 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

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

  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. User view DBA view 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 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 . . .

  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), “DisplayName == ‘Bob Smith’”); c.DisplayName = ‘Robert Smith’;c.BirthDate = ‘01/04/1982’;ic.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 • Account • Name • -Quota • -Type • -Server • Document • Title • -Size • -Type • - • Document • Title • -Size • -Type • - Component

  48. Database Integration • XML • Object storage • Programming model • Development environment • Web • File system • Applications

More Related