Class: Patriot::Util::DBClient::Base Abstract

Inherits:
Object
  • Object
show all
Defined in:
lib/patriot/util/db_client/base.rb

Overview

This class is abstract.

base class for DB connection

Instance Method Summary collapse

Instance Method Details

#build_insert_query(tbl, value, opts = {}) ⇒ String

create a insert statement for given arguments. Subclasses can override this method if necessary

Parameters:

  • tbl (String) —

    the name of table where a row will be inserted

  • value (Hash) —

    a Hash represents the inserted value

  • opts (Hash) (defaults to: {})
  • [Boolean] (Hash) —

    a customizable set of options

Returns:

  • (String) —

    insert statement expression



47
48
49
50
51
52
53
54
55
 
# File 'lib/patriot/util/db_client/base.rb', line 47

def build_insert_query(tbl, value, opts = {})
  opts = {:ignore => false}.merge(opts)
  cols, vals = [], []
  value.each do |c,v|
    cols << c
    vals << quote(v)
  end
  return "INSERT INTO #{tbl} (#{cols.join(',')}) VALUES (#{vals.join(',')})"
end

#close ⇒ Object

close this connection

Raises:

  • (NotImplementedError) 


8
9
10
 
# File 'lib/patriot/util/db_client/base.rb', line 8

def close()
  raise NotImplementedError
end

#cond_exp(cond) ⇒ Object

get an expression of where clause



126
127
128
129
130
131
 
# File 'lib/patriot/util/db_client/base.rb', line 126

def cond_exp(cond)
  cond = cond.map{|k,v| v.nil? ? "#{k} IS NULL" : "#{k} = #{quote(v)}"}.join(' AND ') if cond.is_a?(Hash)
  raise "illegal type of cond : #{cond.class}" unless cond.is_a?(String)
  cond = "WHERE #{cond}" unless cond == ""
  return cond
end

#delete(tbl, cond) ⇒ Integer

delete records from a table

Examples:

the below invocation issues “DELETE FROM table WHERE col = ‘val’”

delete('table', {:col => 'val'})

Parameters:

  • tbl (String) —

    the name of table from which records are deleted

  • cond (Hash) —

    a set of conditions on deleted records

Returns:

  • (Integer) —

    the nubmer of affected rows



95
96
97
98
 
# File 'lib/patriot/util/db_client/base.rb', line 95

def delete(tbl, cond)
  query = "DELETE FROM #{tbl} #{cond_exp(cond)}"
  return execute_statement(query, :update)
end

#do_insert(query) ⇒ Integer

The method which issues an insert statement

Parameters:

  • query (String) —

    an insert statement

Returns:

  • (Integer) —

    a id of last inserted record if the type is :insert

Raises:

  • (NotImplementedError) 


60
61
62
 
# File 'lib/patriot/util/db_client/base.rb', line 60

def do_insert(query)
  raise NotImplementedError
end

#do_select(query) ⇒ Array

The method which issues a select statement

Parameters:

  • query (String) —

    a select statement

Returns:

  • (Array) —

    a list of records

Raises:

  • (NotImplementedError) 


85
86
87
 
# File 'lib/patriot/util/db_client/base.rb', line 85

def do_select(query)
  raise NotImplementedError
end

#do_update(query) ⇒ Integer

The method which issues a delete/update statement

Parameters:

  • query (String) —

    a delete/update statement

Returns:

  • (Integer) —

    the nubmer of affected rows

Raises:

  • (NotImplementedError) 


115
116
117
 
# File 'lib/patriot/util/db_client/base.rb', line 115

def do_update(query)
  raise NotImplementedError
end

#execute_statement(stmt, type = :select) ⇒ Array, Integer

execute a given statment with this connection.

Parameters:

  • stmt (String) —

    SQL statement to be executed

  • type (Symbol) (defaults to: :select) —

    type of execution (return type)

Returns:

  • (Array) —

    a list of records if the type is :select

  • (Integer) —

    a id of last inserted record if the type is :insert

  • (Integer) —

    the nubmer of affected rows if the type is :update



18
19
20
21
22
23
24
25
 
# File 'lib/patriot/util/db_client/base.rb', line 18

def execute_statement(stmt, type = :select)
  method = "do_#{type}".to_sym
  begin
    return self.send(method, stmt)
  rescue => e
    raise "Failed to execute #{stmt} : Caused by (#{e})" 
  end
end

#insert(tbl, value, opts = {}) ⇒ Integer

insert value into a given table

Examples:

the below invocation issues “INSERT INTO table (col) VALUES (‘val’)”

insert('table', {:col => 'val'})

Parameters:

  • tbl (String) —

    the name of table where a row will be inserted

  • value (Hash) —

    a Hash represents the inserted value

  • opts (Hash) (defaults to: {})
  • [Boolean] (Hash) —

    a customizable set of options

Returns:

  • (Integer) —

    a id of the last inserted record



35
36
37
38
 
# File 'lib/patriot/util/db_client/base.rb', line 35

def insert(tbl, value, opts = {})
  query = build_insert_query(tbl, value, opts)
  return execute_statement(query, :insert)
end

#quote(v) ⇒ Object

quote a given value to use in SQL

Raises:

  • (NotImplementedError) 


134
135
136
 
# File 'lib/patriot/util/db_client/base.rb', line 134

def quote(v)
  raise NotImplementedError
end

#select(tbl, cond = {}, opts = {}) ⇒ Array

select records from a table

Examples:

the below invocation issues “SELECT * FROM table WHERE col = ‘val’”

select('table', {:col => 'val'})

Parameters:

  • tbl (String) —

    the name of table where the records are searched

  • cond (Hash) (defaults to: {}) —

    a set of conditons for each attributes

  • opts (Hash) (defaults to: {})
  • [Integer] (Hash) —

    a customizable set of options

  • [ARRAY] (Hash) —

    a customizable set of options

Returns:

  • (Array) —

    a list of records



74
75
76
77
78
79
80
 
# File 'lib/patriot/util/db_client/base.rb', line 74

def select(tbl, cond = {}, opts = {})
  opts  = {:items => ['*']}.merge(opts)
  query = "SELECT #{opts[:items].join(', ')} FROM #{tbl} #{cond_exp(cond)}"
  query = "#{query} LIMIT #{opts[:limit]}" if opts.has_key?(:limit)
  query = "#{query} OFFSET #{opts[:offset]}" if opts.has_key?(:offset)
  return execute_statement(query, :select)
end

#update(tbl, new_val, cond) ⇒ Integer

update records of a table

Examples:

the below invocation issues “UPDATE table SET col = ‘val2’ WHERE col = ‘val1’”

update('table', {:col => 'val2'}, {:col => 'val1'})

Parameters:

  • tbl (String) —

    the name of table in which records are updated

  • new_val (Hash) —

    a set of new values

  • cond (Hash) —

    a set of conditions on update records

Returns:

  • (Integer) —

    the nubmer of affected rows



107
108
109
110
 
# File 'lib/patriot/util/db_client/base.rb', line 107

def update(tbl, new_val, cond)
  query = "UPDATE #{tbl} SET #{value_exp(new_val)} #{cond_exp(cond)}"
  return execute_statement(query, :update)
end

#value_exp(val) ⇒ Object

get an expression used as values in insert statements



120
121
122
123
 
# File 'lib/patriot/util/db_client/base.rb', line 120

def value_exp(val)
  raise "illegal type of value_exp #{val.class}" unless val.is_a?(Hash)
  return val.map{|k,v| v.nil? ? "#{k} = NULL" : "#{k} = #{quote(v)}"}.join(",")
end