PreparedStatements

From Cubawiki

Preparing a statement means explaining it to the DB once, so it doesn't have to parse it every time it is used. Consequently, this query doesn't have to be composed by Lore once it is prepared.

Prepared statements offer some important advantages:

• The query doesn't have to be interpreted by the DB every time
• The query call is available via direct method call.
• DB validates against types, thus preventing SQL injection
• It doesn't Lore require to compose the query string again. This effects the most significant performance gain (Up to 60% execution time in some benchmarks)

A simple example:

Article.prepare(:recently_changed, Lore::Type.integer) { |a|                                                                                                                                   
     a.where(true)                                                                                                                                                                                
     a.order_by(:changed, :desc)                                                                                                                                                                  
     a.limit(Lore::Clause.new('$1'))                                                                                                                                                              
}

After doing this once, you now can call

Article.recently_changed(5) # => Array of 5 Article instances

Which results in an SQL statement like

EXECUTE public_article__recently_changed(5); 

Use prepared statements whenever a query is going to be called often and / or performance is critical.

You should use them nearly everywhere for security reasons alone as they make SQL injection a lot harder. This is because Postgres is checking against data types when calling a prepared statement. You don't need to rely on this alone, though. Lore has pretty tight validation procedured to detect SQL injection or just escape certain characters.

Some prepared statements are pre-configured automatically. These are:

• Model#by_id(primary_key_value) (For models with one primary key only) - Same as Model.load(:pkey_name => primary_key_value), but as prepared statement.
• Model.latest(amount) - Same as Model.find(amount).sort_by(:primary_key, :desc).entities, but as prepared statement

Now that (auto-)prepared statements exist, you should use Model.by_id, not Model.load whenever you load a single model instance from DB. More efficient, more secure. In case you have a model with more than one primary key, define your own prepared statement for this task, like

class Your_Model
    prepare(:_by_ids, Lore::Type.integer, Lore::Type.integer) { |m| 
      m.where((m.pkey_1 == Clause.new('$1')) & (m.pkey_2 == Clause.new('$2'))
      m.limit(1)
    }
    def self.by_ids(a,b)
      _by_ids(a,b).first
    end
end

The wrapper method returning the first result element is needed as Model.prepare always returns results as an array.