Native XML Database for Information Systems

Native XML Databasefor Information Systems Chris Wallace Data, Schemas and Applications Feb 2006

Exploring the design space • “design as a conversation with the materials in the situation” (Schon) • Native XML database (NXD) • Storing, querying and updating XML documents without mapping into relations • Schema-free • Trees are to NXD what tables are to RDBMS • Tables are trees • Information Systems • Focus on semi-structured data (mixture of simple data items, text and complex nested structures) • Searching, derived data, visualisation • Process support • Large problem space variously supported by spreadsheets, word documents, ad-hoc databases, increasingly web-integrated data. Chris Wallace, IS School Research Seminar, Feb 2006

eXist Native XML Database • Open source Java • European team of developers led by Wolfgang Meier • Documents (files) are organised in collections (folders) in a file store • XML Documents stored in an efficient, B+ tree structure with indexes • Non-XML resources (XQuery, CSS, JPEG ..), etc can be stored as binary • Deployable in different ways • Embedded in a Java application • Part of a Cocoon pipeline • As web application in Apache/Tomcat • With embedded Jetty HTTPserver (as on stocks) • Multiple Interfaces • REST – to Java servlet • SOAP • XML:RPC Chris Wallace, IS School Research Seminar, Feb 2006

NXD case studies • FOLD • modules, programmes, scheme operations, staff, organisational structures, events • Family photos and history • Integration of meta-data on family photos with family history (births, deaths and marriages) • ISD3 Assignment • a web-based calculator • e.g. a currency converter • The language patterns book Chris Wallace, IS School Research Seminar, Feb 2006

ISD3 Coursework • Develop a simple web-based calculator • Not just a programming exercise • User interface design • Users language, units, not raw data • User interaction design • Data design • choice of representation of domain facts • Veracity • Relationship between data in database and domain being modelled • How is veracity monitored and maintained • Process • Examine some of the XP processes • Test-driven development Chris Wallace, IS School Research Seminar, Feb 2006

Application Design • Data model is one simple table: • Currency code, name and symbol • Latest conversion rates from GBP to currency X • Currency Coding • Use ISO4217 e.g. XE.COM list • Core algorithm: • Conversion from N X to ? Y is • N * rate(X to GBP) * rate(GBP to Y) i.e. • N * (1/rate (GBP to X) * rate(GBP to Y) • Currency rates to be updated by an administrator • (not via a web-service) • Interface is to be a sticky form: • Input form and output result on one page • Input form default values from last input • Veracity management • Rates must be dated and sourced – Chris Wallace, IS School Research Seminar, Feb 2006

Technical Decisions • Choice of platform: • PL/SQL and Oracle • ASP.NET and SQL Server • JSP (Java Servlet Page) and JDBC to Postgres(say) • PHP and MySQL • XML and Native XML Database (eXist) • Calculation location: • client-side in ECMAScript (aka JavaScript) • server-side • with/without Ajax Chris Wallace, IS School Research Seminar, Feb 2006

Two approaches • PHP-MySQL • Define and create MySQL table • Write PHP script to provide interface and access the database using SQL • Write editor for the Currency table • XML • Create MS Excel spreadsheet of currencies • Convert to XML in Excel and save • Write XQuery script to provide interface Chris Wallace, IS School Research Seminar, Feb 2006

Development Processfor currency converter • XP Practices: • ‘Spike’ Simple end to end implementation • Incremental development • Setup eXist database • Using the Admin interface: • Create a directory for application • Create a subdirectory for currency data • Create XML dataset(s) in Excel • Upload to eXist • Write the XQuery script cur.xql • Upload to eXist • Execute in browser Chris Wallace, IS School Research Seminar, Feb 2006

Currency Data in XML • Start MS Excel 2003 • Create the spreadsheet with column headings • Convert to List (needs XML add-in) • Save as XML data Chris Wallace, IS School Research Seminar, Feb 2006

XQuery • W3C candidate recommendation • http://www.w3.org/TR/xquery/ • Designed by, amongst others, Don Chamberlin • http://www.research.ibm.com/journal/sj/414/chamberlin.pdf • A functional programming Language • Based on XPath (tree-access language) • Integrate, select, update, compute and construct XML documents • cf PL/SQL • http://www.w3.org/XML/Query/ Chris Wallace, IS School Research Seminar, Feb 2006

Write the XQuery script • Use the admin interface to test simple queries • Use a syntax aware editor if possible • JEdit • Dreamweaver • Java Client interface to eXist • PFE32 Chris Wallace, IS School Research Seminar, Feb 2006

Executing an XQuery cur.xql?fromAmount=100&fromCode=USD&toCode=EUR Client Browser eXist: Server User clicks link Get cur.xql + parameters servlet fetch cur.xql parameters cur.xql eXist DB html XQuery Engine render XSLT Chris Wallace, IS School Research Seminar, Feb 2006

XQuery Script (1) declare namespace request="http://exist-db.org/xquery/request"; let $fromAmount := request:request-parameter("fromAmount",“100"), $fromCode := request:request-parameter("fromCode","GBP"), $toCode := request:request-parameter("toCode","EUR"), $currencies := doc('/db/calculator/currencyTable.xml')//Currency, $fromCurrency := $currencies[Code=$fromCode], $toCurrency := $currencies[Code=$toCode], $toAmount := round(xs:decimal($fromAmount) * xs:decimal($toCurrency/Rate) div xs:decimal($fromCurrency/Rate) ) return Chris Wallace, IS School Research Seminar, Feb 2006

XQuery Script (1) Default declare namespace request="http://exist-db.org/xquery/request"; let $fromAmount := request:request-parameter("fromAmount",“100"), $fromCode := request:request-parameter("fromCode","GBP"), $toCode := request:request-parameter("toCode","EUR"), $currencies := doc('/db/calculator/currencyTable.xml')//Currency, $fromCurrency := $currencies[Code=$fromCode], $toCurrency := $currencies[Code=$toCode], $toAmount := round(xs:decimal($fromAmount) * xs:decimal($toCurrency/Rate) div xs:decimal($fromCurrency/Rate) ) return … Return node sequence of all Currency elements in this doc Filter Condition Cast Chris Wallace, IS School Research Seminar, Feb 2006

XQuery Script (2) XML return <html> <head> <title>Currency Calculator</title> </head> <body> <h1>Currency Calculator</h1> <form method ="get"> <table border="1"> <tr><td>Amount to Convert</td> <td><input type="text" name="fromAmount“ value="{$fromAmount}"/> </td> </tr> Current script is called by default Embedded XQuery Chris Wallace, IS School Research Seminar, Feb 2006

XQuery (3) FLWOR expression <tr><td>From Currency</td><td> <select name="fromCode"> {for $currency in $currencies let $code := data($currency/Code), $name := data($currency/Name) return if ($code = $fromCode) then <option value="{$code}" selected="yes">{$name}</option> else <option value="{$code}" >{$name}</option> } </select> </td> </tr> conditional Chris Wallace, IS School Research Seminar, Feb 2006

XQuery (4) <tr><td><input type="submit" name="Convert"/></td></tr> <tr><td>Converts to </td><td>{$toAmount}</td></tr> </table> </form> </body> </html> Switch to XQuery again, In PHP this would be either $toAmount (if in PHP) or <?php print $toAmount; ?> (if in HTML) Chris Wallace, IS School Research Seminar, Feb 2006

Round two - enhancements • Add another currency • ZAR Rand 10.4767 • Add new columns • Meta data • to convey the accuracy, timeliness and origin of the data itself • Source and date/time • Update spreadsheet • Add columns and data • Update XQuery script – cur2.xql • Add source – Sources and oldest date Chris Wallace, IS School Research Seminar, Feb 2006

Round 3 – Currency Table • Same document used for different purpose: • currency.xsl • curtable.xql • Run it curtable.xql Chris Wallace, IS School Research Seminar, Feb 2006

The Language Patterns book • Local / Web-based • Work-in-progress • Should be converted to XML • currently organised by • Pattern • Language • But should be able to be viewed as • Language • Pattern • An XML database would solve this problem • As an exercise for next week, you will extend the number of languages with ActionScript • Get you familiar with the on-line book • Extend the book Chris Wallace, IS School Research Seminar, Feb 2006

Native XML Database for Information Systems