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
orHash
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 asfrom
first element - table name orArray
of table_name and table_alias, thenHash
with keys:- on - conditions(description will be bellow)
- options - optional contains
Symbol
orString
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 dataoptions
##### 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 callmodifier.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?