Simple and comfortable, as possible, data structures parser in to SQL.
Describing sql with data structures like honeysql or ql in clojure.
Construct data structures inside Constructor instance.
Get result and access any returned data rails like syntax.
This is my first gem and first close meeting with OOP... I would appreciate any help =) And sorry for my English =(
Terrazine is not finished yet. Now it has allmost full SELECT builder, but with some limitations like:
And now it supports only Postgresql.
Add this line to the Gemfile
gem 'terrazine', '0.0.2'
After server initialization set Terrazine.config. Now config accepts only :connection option. In the bright future will be added :adapter option support.
In rails you can set config with after_initialize and it will looks like:
UPD: On production, rails closing PG::Connection from after_initialize, as fast fix connection now can be Proc object which must return PG::Connection. Later i'll try to find better solution
# file config/application.rb
module Name
class Application < Rails::Application
# ....
config.after_initialize do
Terrazine.config connection: -> { ActiveRecord::Base.connection.raw_connection }
end
# ....
end
end
Constructor instance and combine parts of data by it instance methods.Terrazine.send_request(structure||constructor, params = {})::ResultYou 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 write or combine data inside constructor instance. Not finished methods - just rewrites structure without combination with existing data.
You can take a look on more detailed examples in spec/constructor_spec.rb
Structure:
Array
Symbol that begins from _ - :_nullif[:_count, [:_nullif, :row, [:_params, 'mrgl']]] # TODO: param?
# => ['COUNT(NULLIF(row, $1))', ['mrgl']]
Detailed Functions description.
Possible structures:
String
Hash with table alias, table alias will be added to itSymbol - just parsed to stringHash
Array - holder of any possible structures['name', {u: ['role', 'u.phone, m.rating', :field]}]
# => 'name, u.role, u.phone, m.rating, u.field'
Possible structures:
String || SymbolArray
Array inside it will be joined structure.join ' '['users u', [:_values, ...], [:masters, :m]]
'users u, (VALUES...), masters m'
Not finished yet...
Column can be described as :u__name => 'u.name' or :name
Possible structures:
String passes as it isHash represent sql = or IN if value is Array. TODO: IS in case of nil or false
Symbol - column nameArray - only as value! will be placed in querry params ($1).String - will be placed in querry paramsArray
Symbol operator representation, by default :and
eq - =or, andinnotlike, ilikereg - ~, reg_i - ~*, reg_f - !~, reg_fi - !~*Array - holder of any possible structures[[: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)'
[{ role: 'manager', id: [0, 1, 153] },
[:not, [:like, :u__name, 'Aeonax']]]
#=> 'role = $1 AND id IN ($2) AND NOT u.name LIKE $3', ['manager', [0, 1, 153], 'Aeonax']
Possible structures:
Constructor instanceHash with :select valuePossible structures:
Array with combination of possible structures.# String
constructor.select "name, email"
# Symbol
constructor.select :birthdate
# Array as columns
constructor.select [:phone, 'role']
# Array as SQL function
constructor.select [:_nullif, :row, :value]
# Hash with column alias(`AS`) as key and any available for `select` value
constructor.select _missed_calls_count:
{ select: [:_count, [:_nullif, :connected, :true]],
from: [:calls, :c],
where: ['c.client_id = u.id',
['direction = ?', 0]]}
# Hash with table alias as key and any available for `select` values
constructor.select m: [:common_rating, :work_rating, { _master_id: :id }]
# You can take a look of resulted data structure. In future, perhaps, Constructor will be more complicated and it will merge hashes...
constructor.structure
# => { select: ['name, email', :birthdate, :phone, 'role',
# [:_nullif, :row, :value],
# { _missed_calls_count: { select: [:_count, [:_nullif, :connected, :true]],
# from: [:calls, :c],
# where: ['c.client_id = u.id',
# ['direction = ?', 0]]} }] },
# { m: [:common_rating, :work_rating, { _master_id: :id }] }
constructor.build_sql
# => ['SELECT name, email, birthdate, phone, role, NULLIF(row, value), 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]
To specify distinct select you should add to your data structure :distinct value:
trueConstructor instance methods:.distinct
.distinct_select
distinct: true passed by default# as data
distinct: true, select: true
# => 'SELECT DISTINCT * '
# OR via constructor
constructor.distinct_select([:id, :name]).build_sql # => 'SELECT DISTINCT id, name'
# OR
constructor.distinct_select([:id, :name], :phone).build_sql # => 'SELECT DISTINCT ON(phone) id, name '
Possible structures:
Array with combination of possible structures.from: 'table_name table_alias' || :table_name
from: [:table_name, :table_alias]
# => 'FROM table_name table_alias '
from: [:_values, [1, 2], :rgl, [:zgl, :gl]]
# => 'FROM (VALUES(1, 2)) AS rgl (zgl, gl)'
from: [[:table_name, :table_alias], [:_values, [1, 2], :values_name, [*values_column_names]]]
# => 'FROM table_name table_alias, (VALUES(1, 2)) AS values_name (v_c_1, v_c_2)'
I do not like the from syntax, but how it can be made more convenient...?
Possible structures:
String - just passed in to JOIN #{structure} Array with values(same order):
Hash
:on - conditions:option - optional - contains Symbol or String of join type... rename to type?Array with combination of possible structures.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 users u ON rgl = 123'
join: [[[:user, :u], { option: :full, on: [:or, 'mrgl = 2', 'rgl = 22'] }],
[:master, { on: ['z = 12', 'mrgl = 12'] }]]
# => 'FULL JOIN user u ON mrgl = 2 OR rgl = 22 JOIN master ON z = 12 AND mrgl = 12'
Possible structures:
String, Symbol just insert it in "ORDER BY #{structure} "Hash
Symbol - :last || :first || :asc || :descString - '<' || '>' or smthng else that passed in to USINGArray - with symbols insideArray - any possible structuresorder: 'z.amount DESC' || :name
# => 'ORDER BY z.amount DESC ' || 'ORDER BY name '
order: [:name, [:_case ...], { amount: [:first, :desc] }]
# => 'ORDER BY name, CASE ..., amount DESC NULLS FIRST '
with: [:alias_name, { select: true, from: :users}]
with: [[:alias_name, { select: true, from: :users}],
[:alias_name_2, { select: {u: [:name, :email]},
from: :rgl}]]
# => 'WITH alias_name (SELECT * FROM users ), alias_name_2 (...) '
# OR
with name: { select: true },
another_name: { select: :mrgl }
# => 'WITH name AS (SELECT * ), another_name AS (SELECT mrgl ) '
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]]] }]
'SELECT ... UNION SELECT ...'
Pass argument as params to adapter
[:_values, [:_params, 'mrgl', true, 'rgl'], :z, [:f_1, :f_2, :f_3]]
['(VALUES($1, $2, $3) AS z (f_1, f_2, f_3))', ['mrgl', true, 'rgl']]
Second and third arguments are nesessary right now, but in furure i'll do them optional. Arguments:
AS name[:_values, [{u: [:name, :phone]}, :role, [:_params, 'rgl']], :z, [:n, :p, :r, :m]]
# => '(VALUES(u.name, u.phone, role, $1) AS z (n, p, r, m))'
Result row - allow accessing data by field name via method - row.name # => "mrgl" or get hash representation with row.to_h
Contains
valuespg_result - ::Result instanceData 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
rows - Array of ::Rowfields - Array of column/alias names of returned dataoptions:types - hash representing which column require additional parsing and which type:presenter_optionsUsed in result.present(options = {}) for data representation 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.
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.presentdelete - (will be soon) - Symbol, String or Array representing keys that must be deleted from result data.Except this todo's there is a lot commented todo's inside project.-_-
VALUESORDER structureYou can write me your suggestions for improving the syntax, wishes, things that you think are missing here.
My email, Ruby On Rails slack
FAQs
Unknown package
We found that terrazine demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
PyPI confirms no security flaws were exploited in the Ultralytics supply chain attack and highlights improvements for safer package publishing.
Security News
Research
The Socket Research Team breaks down a malicious wrapper package that uses obfuscation to harvest credentials and exfiltrate sensitive data.
Research
Security News
Attackers used a malicious npm package typosquatting a popular ESLint plugin to steal sensitive data, execute commands, and exploit developer systems.