2 Replies Latest reply on Oct 16, 2015 5:01 PM by Jon Tara

    Advanced usage of SQLite

    Jon Tara

      The Rhom Ruby API documentation has a brief section near the end on "advanced" Queries. This seems applicable really only to Property Bag models, and promises that "rhom can optimize the query for the property bag table" using "Advanced Queries".


      It's important to note the qualifier here - "property bag table". IMO, from my exploration of the source code, if you are NOT using Property Bag models, all the "advanced" queries do is obfuscate your code by building-up queries using complex data structures. Under the hood, (when using a Fixed Schema model) Rhom creates a SQL query and then sends it off to SQLite. This adds some overhead (I would imagine minimal) but more importantly, I find the complex data structures used in find to be quite unreadable!


      And, so, as I have been using Fixed Schema, I've been moving most of my code toward using either find_by_sql or at least SQL :conditions specifications to find.


      One caution - and a Rhom blind spot - is that find_by_sql doesn't offer binding of variables, and so you will need to sanitize variables and bake their values into the SQL query. (:conditions with a SQL fragment does offer binding). So, this limits the usefulness of some of the techniques I'd like to explore (see below).


      (Ah, perhaps there is an out.... it looks like Database.executeSql has bound parameters and will return results. The documentation is a bit wonky, describing args as "array of sql expressions", when I'd imagine it is actually "array of values to bind"...)


      I'd like to start a discussion here on the advanced ORM techniques that myself and other developers have used or would like to explore. Most of these involve more direct interaction with SQLite. At one time, (in ancient history) Rhodes did not use SQLite on all platforms, (are there any that still don't use it?) and so strict adherence to only using Rhom was more of an imperative if you wanted to write cross-platform code.


      A few random thoughts for starter:


      - I work mainly with iOS and Android, working on OSX. I've found the combination of SimPholders2 and DB Browser for SQLite quite handy for inspection, debugging, developing SQL queries, etc.  SimPholders (while buggy...) easily finds the iOS Simulator's files on your development system, so that you can open the DB with a GUI tools like DB Browser for SQLite. (You may occasionally have to restart Simpholders2 and/or manually kill it, for which System Monitor is handy...)


      - You certainly can do joins between tables, subqueries, and the full range of the available SQL using find_by_sql, and I've either worked on code (or written myself) that does quite a bit of this.


      - While I haven't tried it, I'd imagine you can use subqueries in a SQL :conditions. (?)


      - Database also has executeSQL and executeBatchSQL which allow you to execute some arbitrary SQL. This could be used to define triggers and views (see below).


      - SQLite has a pretty complete SQL implementation, much of which goes unexploited by Rhom. Of particular interest (and not explored by me yet) are triggers and views. So, for example, instead of writing Ruby or Javascript code to delete dependent records when a record is deleted, or update corresponding attributes that are denormalized across multiple tables it seems you could write a trigger and add it using executeSQL, greatly simplifying applciation code.


      I'm curious as to the extent others have exploited these capabilities, and what other advanced database techniques you have used in Rhodes?

        • Re: Advanced usage of SQLite
          Pietro Francesco Maggi

          Thanks Jon!

          Very useful information :-)



          • Re: Advanced usage of SQLite
            Jon Tara

            A couple of discoveries:


            Database.executeSql() return hash is inconsistent with Rhom return hashes (find(), find_by_sql(), etc.)


              - Database returns character string keys

              - Rhom returns symbol keys


            Database documentation is pretty misleading. It shows you how to create an instance of Database and then use it, which you should NOT do to open the database that holds your models! It's only in case you have created some additional SQLite database that you're only going to use with low-level Database calls.


            If you do want to use Database on the standard database (that holds your models) use:


              db = ::Rho::RHO.get_src_db(get_source_name)


            in your model. That will return Rhom's instance of the Database object, which is already instantiated and open. Don't use Database.new!


            As it turns out, it is trivial to change Rhom find_by_sql() to accept bound parameters. I don't know why this wasn't done ages ago. It's a two-word change to the code.


            You might patch it in the Rhodes source, monkey-patch it by including a module in your models (a bit tricky of you really want to "override"), or include a new method in your models. I elected the latter.


            I just copied the small find_by_sql code from the Rhom object factory. The only changes are the addition of args on lines 17 and 21.



            module BaseModel
              def self.included receiver
                receiver.extend ClassMethods
              module ClassMethods
                # find_by_sql with bound arguments
                # See Rhodes source lib/framework/rhom/rhom_object_factory.rb
                # While it's possible with some gymnastics to override find_by_sql in
                # the model class, (so that it might take an optional second parameter)
                # simply defining a new method that takes args is
                # safer than the monkey-patch, as it's less sensitive to changes in
                # the underlying Rhom code.
                def find_by_sql_with_args(sql_query, args)
                  raise ArgumentError, 'find_by_sql only works with FixedSchema models' if !is_schema_source
                  db = ::Rho::RHO.get_src_db(get_source_name)
                  list = db.execute_sql(sql_query, args)
                  ret_list = []
                  list.each do |rowhash|
                      new_obj = self.new({:object=>"#{rowhash['object']}"})
                      rowhash.each do |attrName, attrVal|
                          new_obj.vars.merge!( { attrName.to_sym()=>attrVal } ) if attrVal
                      ret_list << new_obj



            I define a bunch of other useful methods in (some application-specific, some generally useful) in BaseModel as well. Just require the file containing the code above, and:


              include BaesModel


            in your models.


            Now, you can safely use find_by_sql without concern about SQL injection, which is a vulnerability (both from a standpoint of security as well as reliability) if you just include arguments in your SQL statements using Ruby string interpolation.


            I'd strongly suggest Rhodes adopt this two-word change. (Actually, a wee bit more to retrofit find_by_sql, need to check for absent second parameter).


            (I'll submit a pull request...)