class PgQueryOpt

Constants

VERSION

Public Class Methods

hi() click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 29
def self.hi
  puts "Hello world!"
end

Public Instance Methods

conn_etcd() click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 110
def conn_etcd
  return unless @etcd.nil?

  @etcd = if @etcd_user.empty?
            Etcdv3.new(endpoints: 'http://' + @etcd_host + ':' + @etcd_port, command_timeout: 5)
          else
            Etcdv3.new(endpoints: 'http://' + @etcd_host + ':' + @etcd_port, command_timeout: 5, user: @etcd_user, password: @etcd_passwd)
          end
end
etcd_data(filter_id) click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 162
def etcd_data(filter_id)
  if @data_in_etcd[filter_id].nil?
    data                     = @etcd.get(filter_id, range_end: filter_id + '0')
    @data_in_etcd[filter_id] = data
  end
  return {} if @data_in_etcd[filter_id].kvs.count.zero?

  JSON.parse(@data_in_etcd[filter_id].kvs.first.value)

end
find_table_list(tree, table_list = {}) click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 454
def find_table_list(tree, table_list = {})
  key_list = %w[SelectStmt UpdateStmt relation fromClause JoinExpr larg rarg]
  if tree.is_a?(Array)
    tree.each do |k|
      find_table_list(k, table_list)
    end
  else
    tree.keys.each do |key|
      find_table_list(tree[key], table_list) if key_list.include?(key)
    end

    unless tree['RangeVar'].nil?
      table          = {}
      table['table'] = tree['RangeVar']['relname']
      if tree['RangeVar']['schemaname'].nil?
        unless @default_scheme.nil?
          @default_scheme.split(',').each do |scheme|
            scheme           = scheme.strip
            table_defination = get_col_with_table(scheme, table['table'])
            next if table_defination.empty?

            table['schema'] = scheme
            break
          end
        end
      else
        table['schema'] = tree['RangeVar']['schemaname']
      end
      table['alias']             = if tree['RangeVar']['alias'].nil?
                                     if tree['RangeVar']['schemaname'].nil?
                                       table['table']
                                     else
                                       table['schema'] + '.' + table['table']
                                     end
                                   else
                                     tree['RangeVar']['alias']['Alias']['aliasname']
                                   end

      table['columns']           = get_col_with_table(table['schema'], table['table']) unless table['schema'].nil?
      table_list[table['alias']] = table
    end
  end
  table_list
end
get_alias(node) click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 181
def get_alias(node)
  if node.is_a?(Array)
    alias_array = []
    node.each do |col|
      alias_array.push(get_string(col))
    end
    alias_array.join('.')
  else
    get_string(node)
  end
end
get_col_with_table(schema, table) click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 193
def get_col_with_table(schema, table)
  etcd_data('/' + @db + '/' + schema + '/' + table)
end
get_filters(table_list, where_part = nil) click to toggle source

@param [Object] table_list @param [Object] where_part

# File lib/pg_ddm_sql_modifier/parser.rb, line 262
def get_filters(table_list, where_part = nil)
  filter_w = []
  table_list.each do |alias_name, table|
    filter_list = []
    next if table['schema'].nil?

    if where_part.nil?
      @groups.each do |key, value|
        filter_temp = etcd_data('/sqlfilter/' + @db + '/' + table['schema'] + '/' + table['table'] + key)
        next if filter_temp.empty?
        next if filter_temp['enabled'].to_s == 'false'

        filter_list.push(filter_temp)
      end
      filter_temp = etcd_data('/sqlfilter/' + @db + '/' + table['schema'] + '/' + table['table'] + '/*')

      unless filter_temp.empty?
        filter_list.push(filter_temp) if filter_temp['enabled'].to_s == 'true'
      end
    else
      next unless (@db + '.' + table['schema'] + '.' + table['table']) == where_part['table_column'].split('.')[0...-1].join('.')

      filter_list.push(where_part)
    end
    next if filter_list.count.zero?

    filter_list.each do |filter|
      where_condition = filter['filter'].gsub(table['schema'] + '.' + table['table'], alias_name)
      where_query     = PgQuery.parse('SELECT WHERE ' + where_condition)
      filter_w.push(where_query.tree[0]['RawStmt']['stmt']['SelectStmt']['whereClause'])
    end
  end
  filter_w
end
get_role(sql) click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 90
def get_role(sql)
  parser = if @sql
             @query_parser
           else
             PgQuery.parse(sql)
           end
  tree   = parser.tree
  tree.extend Hashie::Extensions::DeepFind
  keys  = tree.deep_find_all('FuncCall')
  keys2 = tree.deep_find_all('TransactionStmt')
  keys3 = tree.deep_find_all('SelectStmt')


  if keys.nil? && keys2.nil? && !keys3.nil?
    'read'
  else
    'master'
  end
end
get_string(node) click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 173
def get_string(node)
  return node if node.is_a?(String)
  return node['String']['str'] unless node['String'].nil?
  return nil unless node['A_Star'].nil?

  node.to_s
end
mask(ref, table_list) click to toggle source

@param [Object] ref @param [Object] table_list @return [Hash{null->null}]

# File lib/pg_ddm_sql_modifier/parser.rb, line 200
def mask(ref, table_list)
  return_column_ref = {}
  if ref.count == 1
    table_list.each do |alias_name, table|
      next unless table['columns'].find { |col| !col.key(get_string(ref[-1])).nil? }

      ref = alias_name.split('.') + [get_string(ref[-1])]
      break
    end
  end

  tab = table_list[get_alias(ref.first(ref.count - 1))] unless table_list.empty?
  unless tab.nil?
    filter = '/rules/' + @db + '/' + tab['schema'] + '/' + tab['table']
    data   = {}
    @groups.each do |key, value|
      group = etcd_data(key)
      next if group['enabled'].to_s == 'false'

      data_temp = etcd_data(filter + '/' + get_string(ref[-1]) + key)
      next if data_temp['enabled'].to_s == 'false'

      if data_temp.empty?
        data = {}
      else
        data = data_temp
        break
      end
    end

    unless data.empty?
      name = if @name.nil?
               get_string(ref[-1])
             elsif @name.is_a?(String)
               @name
             end

      if data['rule'] == 'send_null'
        return_column_ref = { 'ResTarget' => { 'name' => name, 'val' => { 'A_Const' => { 'val' => { 'Null' => {} } } } } }
      elsif data['rule'] == 'delete_col'
        return_column_ref = { 'del' => 1 }
      else
        change_colname = JSON.parse(data['prop'].gsub('%col%', { 'ColumnRef' => { 'fields' => ref } }.to_json))
        # TODO: Schema is not dynamic
        func = { 'funcname' => [{ 'String' => { 'str' => 'mask' } }, { 'String' => { 'str' => data['rule'] } }], 'args' => change_colname }

        return_column_ref = { 'ResTarget' => { 'name' => name, 'val' => { 'FuncCall' => func } } }
      end
      if data['filter'] != ''
        filter_tables               = {}
        filter_tables[tab['alias']] = tab

        filter_where      = get_filters(filter_tables, data)
        return_column_ref = { 'ResTarget' => { 'name' => name, 'val' => { 'CaseExpr' => { 'args' => [{ 'CaseWhen' => { 'expr' => filter_where[0], 'result' => return_column_ref['ResTarget']['val'] } }], 'defresult' => { 'ColumnRef' => { 'fields' => ref } } } } } }
      end
    end
  end
  return_column_ref
end
parse(items, table_list = [], masked = true) click to toggle source

@param [Object] items @param [Array] table_list

# File lib/pg_ddm_sql_modifier/parser.rb, line 299
def parse(items, table_list = [], masked = true)
  old_table_list = []
  return if items.nil?

  if items.is_a?(Hash)
    if items.keys.is_a?(Array)
      items.keys.each do |item|
        case item
        when 'fields'
          @ref               = items[item]
          @return_column_ref = mask(@ref, table_list) if @ref[-1]['A_Star'].nil? && masked == true && @mode == 'select'
          @name              = nil
        when 'ResTarget'
          if @mode == 'update' && masked == true
            masked_field = mask([items[item]['name']], table_list)
            items[item] = { "name" => masked_field[item]['name'], "val" => { "CaseExpr" => { "args" => [{ "CaseWhen" => { "expr" => { "A_Expr" => { "kind" => 0, "name" => [{ "String" => { "str" => "=" } }], "lexpr" => masked_field[item]['val'], "rexpr" => items[item]['val'] } }, "result" => { "ColumnRef" => { "fields" => [items[item]['name']] } } } }], "defresult" => items[item]['val'] } } } unless masked_field.empty?
          end
        when 'name'
          @name = items[item]
        when 'ColumnRef', 'args'
          @remove_ref = 3
        when 'funcname'
          @in_function = 1 if get_string(items[item][0]) == 'count'
        when 'A_Star'
          if @in_function.zero?
            @col_list = {}
            case @ref.count
            when 1
              table_list.each do |alias_name, table|
                @col_list[alias_name] = table['columns']
              end
            when 2, 3
              alias_name            = get_alias(@ref.first(@ref.count - 1))
              @col_list[alias_name] = table_list[alias_name]['columns'] unless table_list[alias_name].nil?
            else
              raise 'SQL exception check your alias names (' + get_alias(@ref) + ')'
            end
            @remove_ref = 1
          else
            @in_function = 0
          end
        when 'SelectStmt', 'UpdateStmt'
          @mode          = if item == 'UpdateStmt'
                             'update'
                           else
                             'select'
                           end
          old_table_list = table_list
          table_list     = find_table_list(items[item])
          filters        = get_filters(table_list)
        when 'InsertStmt'
          return items
        when 'A_Expr'
          if items[item].is_a?(Hash)
            if items[item].include?('name')
              masked = false unless get_string(items[item]['name'][0]) == '||'
            end
          end
        end


        parse(items[item], table_list, masked)

        masked      = true if item == 'A_Expr'
        @name       = nil if item == 'name'

        @remove_ref = 2 if item == 'ResTarget' && @remove_ref == 1

        if item == 'ResTarget'
          unless items[item].include?('name')
            items[item]['name'] = @change_name unless @change_name.nil?
          end
          @change_name = nil
        end

        if item == 'fields' && @remove_ref == 3 && !@return_column_ref.nil? && @return_column_ref['del'].nil?
          unless @return_column_ref.empty?
            items.delete(item)
            items[item]  = [@return_column_ref['ResTarget']['val']]
            @change_name = @return_column_ref['ResTarget']['name'] if @return_column_ref['ResTarget'].include?('name')
          end
          @return_column_ref = {}
        end

        if item == 'ResTarget' && !@return_column_ref.nil?
          unless @return_column_ref.empty?
            if @return_column_ref['del'].nil?
              items[item] = @return_column_ref['ResTarget']
            else
              items.delete(item)
            end
          end
          @return_column_ref = {}
        end

        if !filters.nil? && item == 'SelectStmt'
          if filters.count > 0
            filters.push(items['SelectStmt']['whereClause']) unless items['SelectStmt']['whereClause'].nil?
            items['SelectStmt']['whereClause'] = { 'BoolExpr' => { 'boolop' => 0, 'args' => filters } }
          end
        end

        table_list = old_table_list if %w[SelectStmt UpdateStmt].include?(item)

      end
    end
  end

  if items.is_a?(Array)
    i = 0
    items.each do |item|
      parse(item, table_list, masked)

      reparse = 0
      unless item.nil?
        if item.empty?
          items.delete_at(i)
          reparse = 1
        end
      end

      if @remove_ref == 2
        k = 1

        @col_list.each do |alias_name, alias_table|
          next if alias_table.nil?

          if alias_table.count > 0 && reparse.zero?
            reparse = 1
            items.delete_at(i)
            k -= 1
          end
          alias_table.each do |col|
            fields = alias_name.split('.').push(col['column_name'])
            items.insert(i + k, 'ResTarget' => { 'val' => { 'ColumnRef' => { 'fields' => fields } } })
            k += 1
          end
        end
        @col_list   = {}
        @remove_ref = 0

      end
      parse(items[i], table_list, masked) if reparse == 1
      unless items[i].nil?
        if items[i].empty?
          items.delete_at(i)
        end
      end
      i += 1
    end
  end

  items
end
properties(sql, username, db, etcd_host, etcd_port, etcd_user, etcd_passwd, user_regex, tag_regex, default_scheme, tag_users) click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 33
def properties(sql, username, db, etcd_host, etcd_port, etcd_user, etcd_passwd, user_regex, tag_regex, default_scheme, tag_users)

  @sql            = sql
  @username       = username
  @db             = db
  @etcd_host      = etcd_host
  @etcd_port      = etcd_port
  @etcd_user      = etcd_user
  @etcd_passwd    = etcd_passwd
  @user_regex     = user_regex
  @tag_regex      = tag_regex
  @data_in_etcd   = {}
  @default_scheme = default_scheme
  @in_function    = 0
  @name           = nil
  @mask           = true
  @change_name    = nil
  @mode           = 'select'


  tag_users = tag_users.delete(' ').split(',')
  if tag_users.include?(username)
    @pass_tag = /#{@tag_regex}/.match(@sql)
    return @sql if @pass_tag
  end

  conn_etcd

  @query_parser = PgQuery.parse(@sql)
  @sql          = @sql.strip

  @tag_sql = %r{(?<=^/\*)([^\*]*)(?=\*/)}.match(@sql)
  @tag_sql = @tag_sql ? '/* ' + @tag_sql[1].strip + ' */' : ''

  if @user_id.nil?
    @user_id = /#{@user_regex}/.match(@sql)

    @user_id = @user_id[1].strip if @user_id
  end

  user_to_group

  return @sql if @groups.empty?

  i = 0
  @query_parser.tree.each do |parse_item|
    @query_parser.tree[i] = parse(parse_item)
    i                     += 1
  end
  @tag_sql + @query_parser.deparse
rescue StandardError => e
  puts e
  puts e.backtrace.to_s
  @sql

end
user_to_group() click to toggle source
# File lib/pg_ddm_sql_modifier/parser.rb, line 120
def user_to_group
  data        = []
  @groups     = {}
  key_replace = []

  unless @username.nil?
    filter = '/dbuser/' + @username.strip
    key_replace.push(filter)
    data += @etcd.get(filter, range_end: filter + '0').kvs
  end

  unless @user_id.nil?
    filter = '/users/' + @user_id.strip
    key_replace.push(filter)
    data += @etcd.get(filter, range_end: filter + '0').kvs
  end

  filter = '/users/*'
  key_replace.push(filter)
  data += @etcd.get(filter, range_end: filter + '0').kvs

  filter = '/dbuser/*'
  key_replace.push(filter)
  data += @etcd.get(filter, range_end: filter + '0').kvs


  i = 0
  data.each do |val|
    val_obj = JSON.parse(val.value)
    if val_obj['enabled'].to_s == 'false'
      data.delete_at(i)
    else
      key = val.key.dup
      key_replace.each { |key_data| key.gsub! key_data, '' }
      @groups[key] = {}
    end
    i += 1
  end

  data
end