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

SQLite - tiConf - Valencia 2013

Joe Maffia
February 23, 2013

SQLite - tiConf - Valencia 2013

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

Joe Maffia

February 23, 2013
Tweet

Other Decks in Technology

Transcript

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

    View full-size slide

  2. SQLite
    Dynamic Data in Titanium
    tiConf - VALENCIA, FEB 23-24th, 2013
    #tiConf @joemaffia
    Saturday, 23 February 13

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  35. Solution?
    • I don’t care!?! :(
    tiConf - VALENCIA, FEB 23-24th, 2013
    #tiConf @joemaffia
    Saturday, 23 February 13

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  41. Let me guess...
    ...
    ......
    ...ah yes....
    SQLite
    tiConf - VALENCIA, FEB 23-24th, 2013
    #tiConf @joemaffia
    Saturday, 23 February 13

    View full-size slide

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

    View full-size slide

  43. Saturday, 23 February 13

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  51. Demo
    Saturday, 23 February 13

    View full-size slide

  52. Where do you want to go today?
    {next}
    tiConf - VALENCIA, FEB 23-24th, 2013
    #tiConf @joemaffia
    Saturday, 23 February 13

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  56. Did you just tell me to
    fuck myself?
    tiConf - VALENCIA, FEB 23-24th, 2013
    #tiConf @joemaffia
    Saturday, 23 February 13

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  60. JSONDB
    Saturday, 23 February 13

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  65. SculeJS
    (from Minuscule - pronounced skyul)
    Saturday, 23 February 13

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide