1. Alternative databases CMSC 461 Michael Wilson

2. The power of relational databases • It’s often useful to have relational database power in unusual situations • Useful to organize data in a relational way • Quick to query • How do we do this?

3. Local DBMS? • Running a server locally would be far too costly for the use cases where such things would be useful • These are meant for much more serious applications • There are some much more straightforward libraries and tools we can use

4. SQLite • SQLite is one of the more common file-based databases • Fully featured DBMS • Can query, create tables, create indexes, create triggers • Primary keys, autoincrement

5. SQLite memory footprint • All features enabled, ~500 KB memory space • Can be reduced to as small as 4 KB given the right configuration • Speed vs. memory footprint

6. SQLite and your favorite applications • Firefox • Cookies.sqlite • Content-prefs.sqlite • Downloads.sqlite • Chrome

7. SQLite and mobile devices • Android • Built in! • There’s some Java froofiness in front of it, but it works well! • iOS • Depending on what you need: basically uses the SQLite C library • This library is not exactly easy to use

8. Downsides to SQLite databases? • Really more meant for C and C-like environments • C, C++, Objective C • Built into Android for some reason • We may want to have similar functionality in a JVM language (Java, Groovy, Jython, Clojure, etc.)

9. Java DBMSes • H2 is a Java DBMS • H2 is capable of making in-memory or disk-backed tables • Overall roughly the same concepts • Another Java DBMS • Very feature rich

10. H2, HSQLDB, and Hibernate • H2/HSQLDB are both compatible with Hibernate • Hibernate comes built in with H2 and HSQLDB “dialects” • Can still use the same hibernate calls • This allows you to use H2/HSQLDB as sort of a “prototyping” solution before you connect to a more production ready database

11. XML • XML is a very convenient storage mechanism • Can define arbitrary schemas • Can define data types • Can store a good amount of data in an XML document • With a sufficiently large enough data set, querying could be useful

12. XPath • Query language • NOT based on SQL • Querying based on nodes present in an XML document

13. Simple XML document <books> <book genre="fantasy"> <title>Storm of Swords</title> <author>George R. R. Martin</author> </book> <book genre="sci-fi"> <title>A Deepness in the Sky</title> <author occupation="computer-scientist">VernorVinge</author> </book> </books>

14. Simple XPath query • /books/book/title • Will return all titles • /books/book/author • Will return all authors • /books//author • Returns all authors that are descendants under the “books” element • //title • Select all titles

15. More XML queriess • /books/book[2] • Select the second book in the list • /books/book[1] • Select the first book in the list • /books/book[@genre='fantasy'] • Select all books where genre attribute = fantasy

16. XPath functions • count(//books) • Counts the number of books • concat(//book[@genre='fantasy']/title, ', ', //book[@genre='sci-fi']/title) • Outputs: Storm of Swords, A Deepness in the Sky

17. XPath applications • You can apply XPath to any XML file, which makes it really handy for extracting the data

18. XPath online tester • http://www.freeformatter.com/xpath-tester.html • Can test XPath query expressions here