Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQLite - tiConf - Valencia 2013

9c8f10abccaa937b8f2b1db88f056654?s=47 Joe Maffia
February 23, 2013

SQLite - tiConf - Valencia 2013

SQLite - Dynamic Data in Titanium & a Primer on JSONDB & SculeJS

9c8f10abccaa937b8f2b1db88f056654?s=128

Joe Maffia

February 23, 2013
Tweet

Other Decks in Technology

Transcript

  1. Yes! my real surname is Maffia! Saturday, 23 February 13

  2. SQLite Dynamic Data in Titanium tiConf - VALENCIA, FEB 23-24th,

    2013 #tiConf @joemaffia Saturday, 23 February 13
  3. Joe Maffia Web, Mobile and Application Developer, Apple maniac, DJ

    - Producer, Sound engineer, music lover and a general full-time geek! http://about.me/joemaffia “amazing what Photoshop can do...” tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  4. {SQLite} SQLite is an in-process library that implements a self-

    contained, serverless, zero-configuration, transactional SQL database engine...contained in a small (~350 KB) C programming library. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  5. Self-Contained It requires very minimal support from external libraries or

    from the operating system. This makes it well suited for use in embedded devices that lack the support infrastructure of a desktop computer. mobile? tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  6. Serverless Most SQL database engines are implemented as a separate

    server process. With SQLite, the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  7. No Setup Zero Configuration • No “setup” procedure. • No

    “installation”. • No “server process” to start, stop or configure. • No “administrator”. • No “configuration files”. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  8. Transactional All changes within a single transaction in SQLite either

    occur completely or not at all. Program crash, operating system crash or a power failure. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  9. Duck Typing tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia

    Saturday, 23 February 13
  10. Feature or Bug? It looks like a duck, it acts

    like a duck, it walks like a duck - it must be a duck. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  11. CREATE TABLE cartoon ( name TEXT, height INTEGER, .... )

    INSERT INTO cartoon (name, height,...) VALUES (“donald”,”18cm”,...) If the string doesn't look like an integer -- it won't convert it. It will store it as a string. But it tries to convert it to integer if it can. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  12. Limits? in this context means sizes or quantities that can

    not be exceeded. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  13. Limits? in this context means sizes or quantities that can

    not be exceeded. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  14. Limits in SQLite SQLite was originally designed with a policy

    of avoiding arbitrary limits. The policy was that if it would fit in memory and you could count it with a 32-bit integer, then it should work. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  15. Unfortunately :( Because the upper bounds were not well defined,

    they were not tested, and bugs (including possible security exploits) were often found when pushing SQLite to extremes. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  16. “Limits” • 1 billion Maximum length of a string or

    BLOB • 2000 Maximum Number Of Columns • 64 Maximum Number Of Tables In A Join http://sqlite.org/limits.html tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  17. Ti.Database tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  18. Create vs Install • Create an empty database and define

    its structure and contents via SQL statements in Ti. • Install a predefined database (with or without data) that is shipped with your app. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  19. Create tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  20. *iCloud iOS 5.0.1+ the database will be included in any

    other user data backed up to iCloud. db.file.setRemoteBackup(false); tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  21. Install tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  22. Let’s use it! tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf

    @joemaffia Saturday, 23 February 13
  23. Write tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  24. Read tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  25. Tips & Tricks • Close db connection. • Transaction. •

    Too large pre-populated db. • Version Number tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  26. db.close(); In a mobile app, you're dealing with a single-user,

    memory constrained environment... It’s vital that you close the database connection when you have completed any INSERT or UPDATE operations. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  27. SQLite faq #19 Use transactions to speed up inserts! By

    default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. You create a single, mass operation against the database rather than x little operations. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  28. Big db = Big app Ship a "skeleton" database file

    instead with the minimum amount of data required for the application to run. Then, on first boot, ask the user's authorization to download a replacement/update from a remote source. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  29. version 1.007αβγ • Is the user really running the old

    version or did a previous update fail? • Are they downloading a new app version or upgrading an existing version? • At the end of the day... is the data correct/ updated/wrong? Include a "version" table in your database! tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  30. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23

    February 13
  31. Tips n Places tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf

    @joemaffia Saturday, 23 February 13
  32. Problem{API call} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia

    Saturday, 23 February 13
  33. Problem Usage of the API is subject to rate limits.

    The default limit is 500 requests per hour Twitter is taking the default limit from 350 and cutting it to 175 Unauthenticated calls are permitted 150 requests per hour. Google warns that rate limits, overage fees are coming to Maps API will be capped at 25,000 map loads per day {API call} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  34. Solution? tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  35. Solution? • I don’t care!?! :( tiConf - VALENCIA, FEB

    23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  36. Solution? • I don’t care!?! :( • Once reached the

    limit, the user will pay the consequences... :/ tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  37. Solution? • I don’t care!?! :( • Once reached the

    limit, the user will pay the consequences... :/ • Limit the API calls :| (timeframe or similar) tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  38. Solution? • I don’t care!?! :( • Once reached the

    limit, the user will pay the consequences... :/ • Limit the API calls :| (timeframe or similar) • Saving results into files... but how many? how to manage them? tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  39. Solution? tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  40. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23

    February 13
  41. Let me guess... ... ...... ...ah yes.... SQLite tiConf -

    VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  42. {code} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  43. Saturday, 23 February 13

  44. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23

    February 13
  45. {initCache} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  46. {expireCache} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  47. {get} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  48. {put} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  49. {del} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  50. {public} tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday,

    23 February 13
  51. Demo Saturday, 23 February 13

  52. Where do you want to go today? {next} tiConf -

    VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  53. ...not here :) tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf

    @joemaffia Saturday, 23 February 13
  54. So, how do I query the database? It’s not a

    database. It’s a key-value store! Meet Bob tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  55. Ok, It’s not a database. How do I query it?

    You write a distributed map reduce function in Erlang! tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  56. Did you just tell me to fuck myself? tiConf -

    VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  57. I believe I did, Bob! tiConf - VALENCIA, FEB 23-24th,

    2013 #tiConf @joemaffia Saturday, 23 February 13
  58. NoSQL on Titanium Wikipedia In computing, NoSQL (sometimes expanded to

    "not only SQL") is a broad class of database management systems that differ from the classic model of the relational database management system (RDBMS) in some significant ways. These data stores may not require fixed table schemas, usually avoid join operations, and typically scale horizontally. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  59. SQLite is great...but: db.execute('INSERT INTO favorites (api_id, tip_avatar, tip_user, tip_text,

    venue_icon, venue_name, venue_meters, venue_address, venue_category, venue_lat, venue_lng) SELECT ?,?,?,?,?,?,?,?,?,?,? WHERE NOT EXISTS (SELECT 1 FROM favorites WHERE api_id=?)', Ti.App.pin.api_id, Ti.App.pin.tip_avatar, Ti.App.pin.tip_user, Ti.App.pin.tip_text, Ti.App.pin.venue_icon, Ti.App.pin.venue_name, Ti.App.pin.venue_meters, Ti.App.pin.venue_address, Ti.App.pin.venue_category, Ti.App.pin.venue_lat, Ti.App.pin.venue_lng, Ti.App.pin.api_id); tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  60. JSONDB Saturday, 23 February 13

  61. • It was slow and difficult to constantly modify, re-index

    and deploy a new schema every time we changed the application or added a new feature • Migrating data between versions of the app became very difficult • Our data wasn’t secure – users with jail broken phones could basically change whatever they wanted whenever they wanted (and they did) • Translating between tabular data and objects in JavaScript turned out to be fairly inefficient even with our own custom ORM libraries • Maintaining large embedded SQL queries turned out to be a maintenance nightmare in JavaScript http://www.irlgaming.com/blog/nosql-ios-titanium-appcelerator-worries/ :( tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  62. • Secure – all data committed to and read from

    disk is signed so that it can’t be tampered with by users • File Based – we didn’t want to run a server on the handset, so all data committed to storage is encoded as JSON and stored in flat files. This also means that all your data is portable • Simple – we wanted to get rid of ugly SQL queries and make everything object based • Easy – no schema, no indexes, no complicated SQL. Just objects. That’s how JavaScript should be. :) http://www.irlgaming.com/blog/nosql-ios-titanium-appcelerator-worries/ tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  63. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23

    February 13
  64. http://www.irlgaming.com/blog/nosql-ios-titanium- appcelerator-worries/ https://github.com/dan-eyles/jsondb-public ReadMORE: tiConf - VALENCIA, FEB 23-24th, 2013

    #tiConf @joemaffia Saturday, 23 February 13
  65. SculeJS (from Minuscule - pronounced skyul) Saturday, 23 February 13

  66. My vision with SculeJS wasn't just to replace JSONDB, but

    to provide powerful, generic data structures and algorithm implementations that developers could leverage in their day to day development. Daniel Eyles tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  67. SculeJS is actually a pretty advanced piece of technology •

    Implements B+Tree and Hash index types. • Compiles query expressions down to byte code that is then executed in a hybrid (stack + register) virtual machine. • The system also performs automatic query expression normalization and index selection to optimize the efficiency of queries. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  68. SculeJS SculeJS can run in your web browser, in a

    NodeJS process, or even inside iOS and Android applications including a JavaScript runtime environment. https://github.com/dan-eyles/sculejs tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23 February 13
  69. tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf @joemaffia Saturday, 23

    February 13
  70. APPLAUSE / Q&A tiConf - VALENCIA, FEB 23-24th, 2013 #tiConf

    @joemaffia Saturday, 23 February 13