class CrudService::Dal

This class creates an instance of a generic DAL (Data Access Layer) with cache capability from the provided mysql client, logger and optionally memcache client. Your should extend this class to provide configuration for your dal, please see the README file at github.com/tomcully/crud-service

Attributes

auto_primary_key[RW]

If true, the primary key values are assigned by the DB layer

cache_prefix[RW]

The memcache key prefix for this DAL

fields[RW]

A Hash of table fields (See README)

log[RW]

The logger to use, e.g. an instance of Console::Logger

memcache[RW]

The Memcache layer to use, e.g. an instance of Dalli::Client

mysql[RW]

The DB layer to use, e.g. an instance of Mysql2::Client

primary_key[RW]

The primary key name

relations[RW]

A Hash of table relations (See README)

table_name[RW]

The DB table name

Public Class Methods

new(mysql, memcache = nil, log) click to toggle source

Create an instance.

# File lib/crud-service/dal.rb, line 29
def initialize(mysql, memcache = nil, log) 
  @mysql = mysql
  @memcache = memcache
  @log = log
end

Public Instance Methods

add_field_from_map!(result, map, field_name, key_name) click to toggle source

Add a field to each record from map using another field as a key

# File lib/crud-service/dal.rb, line 303
def add_field_from_map!(result, map, field_name, key_name)
  out = []
  result.each do |record|
    record[field_name] = map[record[key_name]] if map.has_key?(record[key_name])
  end
end
build_equal_condition(v) click to toggle source

Return an escaped condition string for the value v

# File lib/crud-service/dal.rb, line 156
def build_equal_condition(v) 
  if v.nil?
    # Nulls (nil)
    return "IS NULL"
  elsif v.kind_of? Integer or v.kind_of? Float
    # Integers / Floats
    return "= "+v.to_s
  else
    # Everything Else
    return "= '#{@mysql.escape(v.to_s)}'" 
  end
end
build_fields(query) click to toggle source

Get fields

# File lib/crud-service/dal.rb, line 180
def build_fields(query)
  build_select_fields(@fields.keys - get_excludes(query),nil)
end
build_fields_with_ns(query, ns) click to toggle source

Get fields with a namespace

# File lib/crud-service/dal.rb, line 185
def build_fields_with_ns(query, ns)
  build_select_fields(@fields.keys - get_excludes(query),ns)
end
build_insert(data) click to toggle source

Build SQL INSERT fragment from data

# File lib/crud-service/dal.rb, line 136
def build_insert(data)
  fields = ""
  values = ""
  data.each do |k,v|
    fields += "`#{escape_str_field(k)}`, "
    values += escape_value(v)+", "
  end
  "("+fields.chomp(', ')+") VALUES ("+values.chomp(', ')+")"
end
build_select_fields(fields,ns) click to toggle source

Get fields

# File lib/crud-service/dal.rb, line 170
def build_select_fields(fields,ns)
  select = ""
  fields.each do |k|
    select += "`#{ns}`." unless ns.nil?
    select += "`#{k}`,"
  end
  select.chomp(',')
end
build_update(data) click to toggle source

Build SQL UPDATE fragment from data

# File lib/crud-service/dal.rb, line 147
def build_update(data)
  sql = ""
  data.each do |k,v|
    sql += "`#{escape_str_field(k)}` = "+escape_value(v)+", "
  end
  sql.chomp(", ")
end
build_where(query) click to toggle source

Build a simple where clause from the given query

# File lib/crud-service/dal.rb, line 114
def build_where(query)
  where = ""
  query.each_pair do |k, v| 
    if (k!='include' and k!='exclude')
      where += "(`#{escape_str_field(k)}` #{build_equal_condition(v)}) AND "
    end
  end
  where.chomp(' AND ')
end
build_where_ns(query,ns) click to toggle source

Build a simple where clause from the given query with the given table/db namespace

# File lib/crud-service/dal.rb, line 125
def build_where_ns(query,ns)
  where = ""
  query.each_pair do |k, v| 
    if (k!='include' and k!='exclude')
      where += "(`#{ns}`.`#{escape_str_field(k)}` #{build_equal_condition(v)}) AND "
    end
  end
  where.chomp(' AND ')
end
cached_query(query, tables) click to toggle source

Execute a Query, reading from cache if enabled.

# File lib/crud-service/dal.rb, line 36
def cached_query(query, tables)
  unless @memcache.nil?

    unless tables.include? @table_name
      tables.push @table_name
      tables.sort!
    end

    # Get Table versions
    table_versions = ""

    tables.each do |table|
      tbversion = @memcache.get("#{@cache_prefix}-"+table+"-version")
      if tbversion.nil?
        expire_table_cache([table]) 
        tbversion = 1
      end
      table_versions += table+"-"+tbversion.to_s
    end

    # Get the Query Hash
    querymd5 = "#{@cache_prefix}-"+Digest::MD5.hexdigest(query+":"+table_versions)

    # Read Cache and return if hit
    results = @memcache.get querymd5

    unless results.nil?
      return results 
    end

  end

  # Perform the Query
  begin
    queryresult = @mysql.query(query)
  rescue Exception => e
    @log.error("#{e}")
    return []
  end

  # Collate Results
  results = []
  unless queryresult.nil? or queryresult.count == 0
    queryresult.each do |h|
      results.push h
    end
  end

  unless @memcache.nil?
    # Write to Cache
    @memcache.set querymd5, results
  end

  # Return results
  results
end
delete_by_primary_key(primary_key) click to toggle source

Delete a record by its primary key from data

# File lib/crud-service/dal.rb, line 479
def delete_by_primary_key(primary_key)
  query = "DELETE FROM `#{@table_name}` WHERE "+build_where({@primary_key => primary_key})

  begin
    queryresult = @mysql.query(query)
  rescue Exception => e
    @log.error("#{e}")
    return false
  end

  expire_table_cache(get_all_related_tables)
  true
end
escape_str_field(str) click to toggle source

Escape a field name

# File lib/crud-service/dal.rb, line 204
def escape_str_field(str)
  str = str.to_s.sub(/\`/,'')
  @mysql.escape(str)
end
escape_value(v) click to toggle source

Return an escaped SQL string for the value v

# File lib/crud-service/dal.rb, line 190
def escape_value(v) 
  if v.nil?
    # Nulls (nil)
    return "NULL"
  elsif v.kind_of? Integer or v.kind_of? Float
    # Integers / Floats
    return v.to_s
  else
    # Everything Else
    return "'#{@mysql.escape(v.to_s)}'" 
  end
end
exists_by_primary_key?(primary_key) click to toggle source

Return true if a key exists

# File lib/crud-service/dal.rb, line 399
def exists_by_primary_key?(primary_key)
  qry = "SELECT COUNT(*) AS `c` FROM `#{@table_name}` WHERE "+build_where({@primary_key => primary_key})
  res = cached_query(qry,[@table_name])
  res[0]['c'] != 0
end
expire_table_cache(table_names) click to toggle source

Expire a table cache by incrementing the table version

# File lib/crud-service/dal.rb, line 382
def expire_table_cache(table_names)
  return if @memcache.nil?

  table_names.each do |table_name|
    key = "#{@cache_prefix}-"+table_name+"-version"
    version = @memcache.get(key)
    if version.nil?
      @memcache.set(key,1,nil,{:raw=>true}) 
    else
      @memcache.incr(key, 1, nil)
    end
  end

  true
end
get_all_by_query(query) click to toggle source

Get All records via a query

# File lib/crud-service/dal.rb, line 222
def get_all_by_query(query)
  qry = "SELECT #{build_fields(query)} FROM `#{@table_name}`"
  where = build_where(query)
  qry += " WHERE #{where}" unless where.length == 0
  cached_query(qry,[@table_name])
end
get_all_by_query_as_hash(query) click to toggle source

Get all records for this entity and map ids to a hash

# File lib/crud-service/dal.rb, line 230
def get_all_by_query_as_hash(query)
  map_to_hash_by_primary_key(get_all_by_query(query))
end
get_excludes(query) click to toggle source

Get excludes

# File lib/crud-service/dal.rb, line 317
def get_excludes(query)
  return [] if query.nil? or !query.has_key?('exclude') or query['exclude'].nil?
  query['exclude'].split(',')
end
get_has_many_relation_query_sql(relation, query) click to toggle source

Get the SQL query for a has_many relation

# File lib/crud-service/dal.rb, line 348
def get_has_many_relation_query_sql(relation, query)
  fields = build_select_fields(relation[:table_fields].split(','),'a')

  qry = "SELECT #{fields},`b`.`#{relation[:this_key]}` AS `_table_key` FROM `#{relation[:table]}` AS `a`, `#{@table_name}` AS `b` WHERE (`a`.`#{relation[:table_key]}` = `b`.`#{relation[:this_key]}`)"
  where = build_where_ns(query,'b')
  qry += " AND #{where}" unless where.length == 0
  qry
end
get_has_many_through_relation_query_sql(relation,query) click to toggle source

Get the SQL query for a has_many_through relation

# File lib/crud-service/dal.rb, line 358
def get_has_many_through_relation_query_sql(relation,query)
  fields = build_select_fields(relation[:table_fields].split(','),'a')

  qry = "SELECT #{fields},`c`.`#{relation[:this_key]}` AS `_table_key` FROM `#{relation[:table]}` AS `a`, `#{relation[:link_table]}` AS `b`, `#{@table_name}` AS `c` WHERE (`a`.`#{relation[:table_key]}` = `b`.`#{relation[:link_field]}` AND `b`.`#{relation[:link_key]}` = `c`.`#{relation[:this_key]}`)"
  where = build_where_ns(query,'c')
  qry += " AND #{where}" unless where.length == 0
  qry
end
get_has_one_relation_query_sql(relation, query) click to toggle source

Get the SQL query for a has_one relation

# File lib/crud-service/dal.rb, line 338
def get_has_one_relation_query_sql(relation, query)
  fields = build_select_fields(relation[:table_fields].split(','),'a')

  qry = "SELECT #{fields},`b`.`#{relation[:this_key]}` AS `_table_key` FROM `#{relation[:table]}` AS `a`, `#{@table_name}` AS `b` WHERE (`a`.`#{relation[:table_key]}` = `b`.`#{relation[:this_key]}`)"
  where = build_where_ns(query,'b')
  qry += " AND #{where}" unless where.length == 0
  qry
end
get_includes(query) click to toggle source

Get includes

# File lib/crud-service/dal.rb, line 311
def get_includes(query)
  return [] if query.nil? or !query.has_key?('include') or query['include'].nil?
  query['include'].split(',')
end
get_last_id() click to toggle source

Get the last insert id

# File lib/crud-service/dal.rb, line 210
def get_last_id
  return @mysql.last_id
end
get_one(query) click to toggle source

Get one record via a query

# File lib/crud-service/dal.rb, line 215
def get_one(query)
  res = get_all_by_query(query)
  return nil if res.length == 0
  res[0]
end
get_relation_data_as_hash(query) click to toggle source

Get data for included relations for a query

# File lib/crud-service/dal.rb, line 250
def get_relation_data_as_hash(query) 
  return {} if @relations.nil? or @relations.empty?

  includes = get_includes(query)

  reldata = {}

  @relations.each do |name, relation| 
    unless includes.find_index(name).nil?
      sql = get_relation_query_sql(relation, query)
      tables = get_relation_tables(relation)
      data = cached_query(sql,tables)
      reldata[name] = map_to_hash_of_arrays_by_key(data,'_table_key')
      remove_key_from_hash_of_arrays!(reldata[name],'_table_key')
    end
  end
  reldata
end
get_relation_query_sql(relation, query) click to toggle source

Get sql to load relation

# File lib/crud-service/dal.rb, line 323
def get_relation_query_sql(relation, query)
  case relation[:type]
  when :has_one
    return get_has_one_relation_query_sql(relation, query)
  when :has_many
    return get_has_many_relation_query_sql(relation, query)
  when :has_many_through
    return get_has_many_through_relation_query_sql(relation, query)
  else
    @log.error("Relation type #{relation[:type]} undefined!")
    nil
  end
end
get_relation_tables(relation) click to toggle source

Get an array of table names involved in a relation query

# File lib/crud-service/dal.rb, line 368
def get_relation_tables(relation) 
  case relation[:type]
  when :has_one
    return [@table_name, relation[:table]].sort
  when :has_many
    return [@table_name, relation[:table]].sort
  when :has_many_through
    return [@table_name, relation[:table], relation[:link_table]].sort
  else
    raise "Unknown Relation type #{relation[:type]}"
  end
end
insert(data) click to toggle source

Create a record from data

# File lib/crud-service/dal.rb, line 443
def insert(data)
  query = "INSERT INTO `#{@table_name}` "+build_insert(data)

  begin
    queryresult = @mysql.query(query)
  rescue Exception => e
    @log.error("#{e}")
    return nil
  end

  expire_table_cache(get_all_related_tables)

  if @auto_primary_key
    get_one({@primary_key => get_last_id})
  else
    get_one({@primary_key => data[@primary_key]})
  end
end
map_in_included_relations!(result, query) click to toggle source

Map in the included relations to each of the supplied result rows

# File lib/crud-service/dal.rb, line 235
def map_in_included_relations!(result, query)
  dat = get_relation_data_as_hash(query)
  result.each do |res|
    dat.each do |name, lookup|
      res[name] = lookup[res[@relations[name][:this_key]]]
      if @relations[name][:type] == :has_one
        res[name] = res[name][0] unless res[name].nil?
      else 
        res[name] = [] if res[name].nil?
      end
    end
  end
end
map_to_hash_by_primary_key(result) click to toggle source

Map a result array to a hash by primary key

# File lib/crud-service/dal.rb, line 280
def map_to_hash_by_primary_key(result) 
  hash = {}

  result.each do |record|
    hash[record[@primary_key]] = record
  end

  hash
end
map_to_hash_of_arrays_by_key(result,key) click to toggle source

Map a result array to a hash of arrays by a specific key

# File lib/crud-service/dal.rb, line 291
def map_to_hash_of_arrays_by_key(result,key) 
  res = {}

  result.each do |record|
    res[record[key]] = [] unless res.has_key?(record[key])
    res[record[key]].push record
  end

  res
end
remove_key_from_hash_of_arrays!(hash,key) click to toggle source

Remove the given key from each record in the supplied hash

# File lib/crud-service/dal.rb, line 270
def remove_key_from_hash_of_arrays!(hash,key)
  hash.each do |name,arr|
    arr.each do |record|
      record.delete(key)
    end
  end
  hash
end
update_by_primary_key(primary_key, data) click to toggle source

Update a record by its primary key from data

# File lib/crud-service/dal.rb, line 463
def update_by_primary_key(primary_key, data)
  query = "UPDATE `#{@table_name}` SET "+build_update(data)+" WHERE "+build_where({@primary_key => primary_key})

  begin
    queryresult = @mysql.query(query)
  rescue Exception => e
    @log.error("#{e}")
    return false
  end

  expire_table_cache(get_all_related_tables)

  get_one({@primary_key => primary_key})
end
valid_insert?(data) click to toggle source

Return true if an object is valid for create

# File lib/crud-service/dal.rb, line 406
def valid_insert?(data)
  return false if data.nil?
  return false if data.keys.length == 0

  # Required fields
  @fields.each do |k,s|
    return false if s.has_key?(:required) and s[:required] == true and !data.has_key?(k)
  end

  # Only valid fields, length checking
  data.each_key do |k|
    return false if !@fields.has_key?(k)
    return false if @fields[k].has_key?(:length) and
      !data[k].nil? and
      data[k].length > @fields[k][:length]
  end

  return true
end
valid_query?(query) click to toggle source

Determine if all fields and includes in the query are available

# File lib/crud-service/dal.rb, line 94
def valid_query?(query)
  return false if query.nil?
  return true if query.keys.length == 0

  query.each_key do |k|
    return false if !@fields.has_key?(k) and k!='include' and k!='exclude'
  end

  get_includes(query).each do |k|
    return false if !@fields.has_key?(k) and !@relations.has_key?(k)
  end

  get_excludes(query).each do |k|
    return false if !@fields.has_key?(k)
  end

  true
end
valid_update?(data) click to toggle source

Return true if an object is valid for update

# File lib/crud-service/dal.rb, line 427
def valid_update?(data)
  return false if data.nil?
  return false if data.keys.length == 0

  # Only valid fields, length checking
  data.each_key do |k|
    return false if !@fields.has_key?(k)
    return false if @fields[k].has_key?(:length) and
      !data[k].nil? and
      data[k].length > @fields[k][:length]
  end

  return true
end