Database Management Systems For Mobile Devices

1. Database Management Systems For MobileDevices • Robert L. Foster Jr. • November 18, 2008

2. Light Weight DBMS? • Definition • Purpose • Examples • Tradeoffs

3. Formal Definition • There isn’t one... • Major DBMS’s can run on most operating systems providing full functionality: MySQl Oracle etc... • What about embedded systems? • Why do we need one?

4. DBMS For Mobile Devices • DBMS’s for mobile device should be dedicated to optimizing at least one of the following, preferably both: • power optimization • memory management • commonality: small footprints

5. Power Optimization • Mobile devices such as laptops, cell phones, PDA’s, and radios have a limited power supply. • How can we optimize our DBMS to use as few resources as possible while providing the necessary functionality? • What is the necessary functionality for a mobile device?

6. Power Optimization Continued... • Query • Add • Delete • Atomicity?

7. Memory Management • How much memory is available on the device at any given time? • How much of the devices internal memory should be dedicated to the DB? • How frequently can disk accesses be made before latency issues arise?

8. Examples • There are numerous “light weight” DBMS’s dedicated to addressing these specific issues. • How do we choose which system is the best for a given device?

9. Examples • What’s good for the goose is not necessarily what’s good for the gander... • Mobile phones have increasingly larger amounts of memory, and faster internet connections. • Multi-Touch technology and GPS capabilities are a drain on batteries, and memory.

10. Metrics • What should we consider when choosing a light weight DBMS for a device? • Operating System • Acidity • Contents (Blobs, Clobs, etc...) • Max Sizes: • Database • Tables • Row Size • Columns Per Row • Views (caching)

11. Metrics Continued...Capabilities? • Union • Intersect • Inner Joins • Outer Joins • Merge • Language? • Cost

12. Options • SQLite • SmallSQL • Pyrrho DBMS • ScimoreDB • HSQLDB • Embedded MySQL

13. HSQLDB • Hyperthreaded Structured Query Language • Written in Java • Offers JDBC Driver • 100-600k • Cross Platform • OpenOffice & Mathematica

14. ScimoreDB Embedded • Distributed Query Language (DQL) • Text based query language • Windows Only • C++ • ACID • Independent process or part of your application • Free • 4MB (A relatively small footprint in comparison)

15. SmallSQL • Java • JDBC Driver • 200k • No Installation Required • Provides no network interface or user management...so what’s it good for?

16. SQLite • ACID • C • Cross Platform • 500K • Uses a Single Database File • FREE!

17. SQLite Continued... • Not a stand alone process • A library? • Linked Function Calls • Dynamically Typed (Read Weakly) • C, C++, Java, C#, and LISP bindings available • SQLite3

18. More on SQLite • SQLite3 - Stand alone application provides: • create database • define tables • insert and change rows • run queries • manage database • Single application (Resides on the host machine)

19. SQLite Code Samples • Because of weak typing and the use of a single database file several layers may be necessary. • SQLiteDatabaseCalls#include "sqlite3.h"/*Minimal SQLite3 Interface this provides the only access to SQLite3 library functions*/sqlite3* createDatabase(char *pFilename);int getDataFromDB(sqlite3* db, constchar* sqlCommand, char** & data, int & rows, int & cols);int clearOldData(sqlite3* db, constchar* sqlCommand);int insertResults(sqlite3* db, int currentTime, constchar* sqlCommand);int clearEntries(sqlite3* db, constchar* sqlCommand);

20. Code Samples Continued... • DatabaseManager#include "SQLDatabaseCalls.h"//Basic Interface Provided to Client Codevoid* DbHandleCreate();int DbHandleClose(void* pHandle);bool addDatatoTable(constchar* sqlCommand);bool clearTable(const char* sqlCommand);bool clearTableEntry(constchar* sqlCommand);char** queryEntry(constchar* sqlCommand, int & rows, int & cols);void freeTable(char** table);

21. Code Samples Continued... • Client Code # include "DbManager.h"//Uses The DatabaseManager and handles all type issues and structuresbool addPolicy(SPolicy* policy, SMode* oldMode, char* content);bool deletePolicy(constchar * modeID, constchar * policyID);bool queryPolicy(int argc, char** argv, int & messagelen, char*& xmlBuf);bool deleteComplaint(int argc, char** argv);bool deleteConflict(int argc, char** argv); bool deleteMode(int argc, char** argv);bool findComplaint(int argc, char** argv, int & messagelen, char*& xmlBuf);bool findConflict(int argc, char** argv, int & messagelen, char*& xmlBuf);bool findDecision(int argc, char** argv, int & messagelen, char*& xmlBuf);

22. SQLite IRL • 125 million copies in Mozilla Firefox. • 20 million Mac OS X computers, each of which contains multiple copies of SQLite. (In Mac OS X v10.4 Apple introduced SQLite as a persistence layer of the Core Data API.) • 300 million downloads of Skype. (The Skype service has 100 million registered users.) • Nokia phones with Symbian OS version 9.4 or later. (The first one is Nokia 5800) • Every iPhone and iPod touch. • Google Android phones like the T-Mobile G1

23. Tradeoffs • Incomplete Functionality • Support • ACID compliance • Boundary Limitations (Max Sizes) • Access Control • Coding/Implementation overhead

24. References • “Comparison of relational database management systems.” Wikipedia: The Free Encyclopedia. 15 November 2008 <http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems> • “SQLite.” Wikipedia: The Free Encyclopedia. 15 November 2008 <http://en.wikipedia.org/wiki/SQLite> • “HSQLDB.” 15 November 2008 <http://hsqldb.org/> • “SmalSQL Database - the Java Desktop Database with JDBC interface.” 15 November 2008 <http://www.smallsql.de/> • “Scimore - High performance distributed, client/server and fully feature embeded SQL database.” 15 November 2008 <http://www.scimore.com/> • “SQLite Documentation.” 15 November 2008 <http://www.sqlite.org/docs.html>