Class: Avmtrf1::Oracle::Objects::SqlBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/avmtrf1/oracle/objects/sql_builder.rb

Constant Summary collapse

COLUMNS =
{
  type: :type_name, name: :name, owner: :owner_name, table: :table_name
}.freeze

Instance Method Summary collapse

Instance Method Details

#all_objects_sqlObject


23
24
25
# File 'lib/avmtrf1/oracle/objects/sql_builder.rb', line 23

def all_objects_sql
  "SELECT #{COLUMNS_TO_ATTRIBUTES.keys.join(', ')} FROM #{TABLE_NAME}"
end

#all_users_sqlObject


27
28
29
30
# File 'lib/avmtrf1/oracle/objects/sql_builder.rb', line 27

def all_users_sql
  'SELECT user_id as object_id, username as object_name, \'USER\' as object_type' \
    ', null as owner FROM all_users'
end

#column_by_search_key(search_key) ⇒ Object


42
43
44
# File 'lib/avmtrf1/oracle/objects/sql_builder.rb', line 42

def column_by_search_key(search_key)
  COLUMNS_TO_ATTRIBUTES.key(search_key) || raise("Search key not found: #{search_key}")
end

#search_option(search_key) ⇒ Object


54
55
56
57
58
59
60
# File 'lib/avmtrf1/oracle/objects/sql_builder.rb', line 54

def search_option(search_key)
  [search_key.to_s, search_key.to_sym].each do |k|
    v = options[k]
    return Array(v) if v.present?
  end
  []
end

#search_option_condition(search_key) ⇒ Object


46
47
48
49
50
51
52
# File 'lib/avmtrf1/oracle/objects/sql_builder.rb', line 46

def search_option_condition(search_key)
  values = search_option(search_key)
  return nil unless values.any?

  column = column_by_search_key(search_key)
  values.map { |v| "(LOWER(#{column}) LIKE LOWER('#{v}'))" }.join(' or ')
end

#sqlObject


16
17
18
19
20
21
# File 'lib/avmtrf1/oracle/objects/sql_builder.rb', line 16

def sql
  "SELECT * FROM (\n" +
    [all_objects_sql, all_users_sql].map { |sub_sql| "\t(#{with_where_sql(sub_sql)})\n" }
                                    .join("\tUNION ALL\n") +
    ") order by object_type, owner, object_name\n"
end

#with_where_sql(sql) ⇒ Object


32
33
34
35
36
37
38
39
40
# File 'lib/avmtrf1/oracle/objects/sql_builder.rb', line 32

def with_where_sql(sql)
  r = "select * from (#{sql}) WHERE 1=1"
  COLUMNS_TO_ATTRIBUTES.each_value do |search_key|
    search_option_condition(search_key).if_present do |v|
      r += " and (#{v})"
    end
  end
  r
end