Terrazin

Idea

Simple and comfortable, as possible, data structures parser in to SQL.

Data

Describing sql with data structures like honeysql or ql in clojure.

Constructor

Construct data structures inside Constructor instance.

Result

Get result and access any returned data rails like syntax.

Realization

This is my first gem and first close meeting with OOP... I would appreciate any help =) And sorry for my English =(

Detailed description

Usage

Describe whole data structure, or create Constructor instance and combine parts of data by it instance methods. Then send result to Terrazine.send_request(structure||constructor, params = {}) and it will return you Terrazine::Result instance. (description will be soon)

Constructor

You can create Constructor instance by calling Terrazine.new_constructor. It optional accepts data structure. 

constructor = Terrazine.new_constructor
constructor_2 = Terrazine.new_constructor from: :calls

Instance methods

Instance methods write or combine data inside constructor instance. Not finished methods - just rewrites structure without combination with existing data.

  • [ ] with
  • [x] select/distinct_select
  • [ ] from
  • [ ] join
  • [x] where
  • [x] limit
  • [x] paginate
  • [x] merge - just merging instance structure with argument
  • [x] build_sql

Data Structures

Select

Accepts

  • String || Symbol
  • Hash represents column alias - 'AS' (if key begins from _) OR table alias that will join to the values table prefix OR another data structure(present keyword :select).
  • Another Constructor or Hash representing data structure
  • Array can contain all of the above structures OR in case of first symbol/string begins from _ it will represent SQL function
    ```ruby constructor.select "name, email" constructor.select :birthdate constructor.select m: [:common_rating, :work_rating, { _master_id: :id }] constructor.select { _missed_calls_count: { select: [:_count, [:_nullif, :connected, :true]], from: [:calls, :c], where: ['c.client_id = u.id', ['direction = ?', 0]]} } constructor.structure # => { select: ['name, email', :birthdate, # { m: [:common_rating, :work_rating, { _master_id: :id }] }, # { _missed_calls_count: { select: [:_count, [:_nullif, :connected, :true]], # from: [:calls, :c], # where: ['c.client_id = u.id', # ['direction = ?', 0]]} }] }

constructor.build_sql

=> ['SELECT name, email, birthdate, m.common_rating, m.work_rating, m.id AS master_id,

(SELECT COUNT(NULLIF(connected, TRUE))

FROM calls c

WHERE c.client_id = u.id AND direction = $1) AS missed_calls_count',

0]


#### From
Accepts
- `String` || `Symbol`
- `Array` can contains table_name and table_alias OR `VALUES` OR both  
```ruby
from 'table_name table_alias' || :table_name
from [:table_name, :table_alias]
from [[:table_name, :table_alias], [:_values, [1, 2], :values_name, [*values_column_names]]]
from [:mrgl, [:_values, [1, 2], :rgl, [:zgl, :gl]]]

I do not like the from syntax, but how it can be made more convenient...?

Join

Accpets

  • String
  • Array: First element same as from first element - table name or Array of table_name and table_alias, then Hash with keys:
    • on - conditions(description will be bellow)
    • options - optional contains Symbol or String of join type... rename to type?

Array can be nested 

join 'users u ON u.id = m.user_id'
join ['users u ON u.id = m.user_id',
      'skills s ON u.id = s.user_id']
join [[:user, :u], { on: 'rgl = 123' }]
join [[[:user, :u], { option: :full, on: [:or, 'mrgl = 2', 'rgl = 22'] }],
      [:master, { on: ['z = 12', 'mrgl = 12'] }]]

Conditions

Current conditions implementation is sux... -_- Soon i'll change it.
Now it accepts String or Array.
First element of array is Symbol representation of join condition - :or || :and or by default :and. 

conditions 'mrgl = 12'
conditions ['z = 12', 'mrgl = 12']
conditions ['NOT z = 13', [:or, 'mrgl = 2', 'rgl = 22']]
conditions [:or, ['NOT z = 13', [:or, 'mrgl = 2', 'rgl = 22']],
                 [:or, 'rgl = 12', 'zgl = lol']]
conditions [['NOT z = 13',
            [:or, 'mrgl = 2', 'rgl = 22']],
            [:or, 'rgl = 12', 'zgl = lol']]
# => 'NOT z = 13 AND (mrgl = 2 OR rgl = 22) AND (rgl = 12 OR zgl = lol)'

With

with [:alias_name, { select: true, from: :users}]
with [[:alias_name, { select: true, from: :users}],
      [:alias_name_2, { select: {u: [:name, :email]},
                        from: :rgl}]]

Union

union: [{ select: true, from: [:o_list, [:_values, [1], :al, [:master]]] },
        { select: true, from: [:co_list, [:_values, [0, :FALSE, :TRUE, 0],
                                                    :al, [:rating, :rejected,
                                                          :payment, :master]]] }]

Result representation

::Row

Result row - allow accessing data by field name via method - row.name # => "mrgl" or get hash representation with row.to_h Contains

  • values
  • pg_result - ::Result instance

::Result < ::Row

Data can be accessed like from row - it use first row, or you can iterate rows.
Methods each, each_with_index, first, last, map, count, present? delegates to rows. index delegates to fields.
For data representation as Hash or Array exists method present
After initialize PG::Result cleared

Contains
  • rows - Array of ::Row
  • fields - Array of column/alias names of returned data
  • options ##### Options
  • :types - hash representing which column require additional parsing and which type
  • :presenter_options

::Presenter

Used in result.present(options = {}) - it represents data as Hash or Array. options are merged with result.options[:presenter_options] Data will be presented as Array if rows > 1 or options[:array] present.

Available options
  • array - if querry returns only one row, but on client you await for array of data.
  • structure - Hash with field as key and value as modifier. Modifier will rewrite field value in result. Modifier acts:
    • Proc - it will call proc with row as argument, and! then pass it to modifier_presentation again
    • ::Result - it will call modifier.present
    • any else will be returned without changes

TODO:

  • [ ] Parse data like arrays, booleans, nil to SQL
  • [ ] Relocate functions builder in to class, finally I found how it can be done nice=))
  • [ ] meditate about structure supporting another databases(now supports only postgress)
  • [ ] should I bother with extra spaces?

Tests

  • [ ] Constructor + Builder
  • [ ] Result
  • [ ] Request

Think of a better data structure for

  • [ ] from
  • [ ] join !!!
  • [ ] where !!!!!! Support for rails like syntax with hash?