lib/toolhound-ruby/base.rb in toolhound-ruby-1.0.4 vs lib/toolhound-ruby/base.rb in toolhound-ruby-1.0.5
- old
+ new
@@ -1,25 +1,29 @@
module Toolhound
class Base
+ DB_TYPE_REGEX = /^(int|dec|var|bol|dte|bin)/
- attr_accessor :attributes
+ include Toolhound::Util
+ attr_accessor :connection
def self.table_name
- @@table_name
+ @@table_name[self.name]
end
def self.primary_key
- @@primary_key
+ @@primary_key[self.name]
end
def self.table_name=(table_name)
- @@table_name = table_name
+ @@table_name ||= {}
+ @@table_name[self.name] = table_name
end
def self.primary_key=(primary_key)
- @@primary_key = primary_key
+ @@primary_key ||= {}
+ @@primary_key[self.name] = primary_key
end
def self.rename_attributes(hash)
hash = Hash[hash.map {|key, value| [key.to_s, value] }]
@@ -28,36 +32,269 @@
def self.renamed_attributes
@@rename_attributes ||= {}
end
- def self.find(id)
- # table_includes = ""
- results = query "SELECT TOP(1) * FROM #{table_name} #{table_includes} WHERE #{primary_key} = #{id}"
+ # def self.connection
+ # @connection ||= Toolhound.connection
+ # end
+ #
+ def initialize(connection, options = {})
+ @connection = connection
+ # @original = attrs
+ # self.attributes = transform_attributes(attrs)
+ end
+ def table_name
+ name = self.class.table_name || demodulize(self.class.name)
+ formatted_table_name(name)
+ end
+ def primary_key
+ id = self.class.primary_key || "int#{demodulize(self.class.name)}ID"
+ formmatted_column_name(id)
+ end
+
+ def locale
+ @locale ||= "EN-US"
+ end
+
+ def default_wheres
+ []
+ end
+
+ def default_selects
+ {}
+ end
+ def default_joins
+ []
+ end
+
+ def all(options = {})
+ options = merge_options({selects: default_selects, joins: default_joins, where: default_wheres}, options)
+ build_and_query options
+ end
+
+ def find(id, options = {})
+ # "tblInventory.bolIsActive = 1 AND tblInventory.bolDeleted = 0 AND tblInventory.intInventoryID = #{id}"
+ # wheres = [] + default_wheres
+ wheres = default_wheres + [{:"#{primary_key}" => id}]
+ options = merge_options({limit: 1, selects: default_selects, joins: default_joins, where: wheres}, options)
+ results = build_and_query options
results.first
end
- def self.all(options = {})
- results = query "SELECT * FROM #{table_name}"
+ def merge_options(defaults, options = {})
+ where = options.delete :where
+ selects = options.delete :selects
+ joins = options.delete :joins
+ defaults[:where] = (defaults[:where] || []) + (where || [])
+ defaults[:selects] = defaults[:selects].merge(selects || {})
+ defaults[:joins] = defaults[:joins] + (joins || [])
+ defaults.merge options
end
- def self.query(query)
- klass = self
+ # def where(wheres, options = {})
+ #
+ # sql = build_sql(limit: 1, selects: default_selects, joins: default_joins, where: wheres)
+ #
+ # end
+
+ # def find(id)
+ # # table_includes = ""
+ # results = query "SELECT TOP(1) * FROM #{table_name} #{table_includes} WHERE #{primary_key} = #{id}"
+ # results.first
+ # end
+ #
+ # def all(options = {})
+ # results = query "SELECT * FROM #{table_name}"
+ # end
+
+
+
+ def formatted_table_name(table)
+ table = table.to_s
+ unless /^tbl/i =~ table
+ table = "tbl#{camelize(table, true)}"
+ end
+ camelize(table, false)
+ end
+
+ def formmatted_column_name(column)
+ column = column.to_s
+ camelize(column, false)
+ end
+
+ def build_selects(selects)
+ _build_selects(selects).join(", ")
+ end
+
+ def _build_selects(selects)
+ arr = []
+ aggs = {
+ count: "COUNT",
+ max: "MAX",
+ min: "MIN",
+ average: "AVERAGE"
+ }
+ selects.each do |table, values|
+
+ values.each do |v|
+ if v.is_a? Hash
+
+ end
+ # select = "#{formatted_table_name(table)}."
+ if v.is_a? Hash
+ select = "#{formatted_table_name(table)}.#{formmatted_column_name(v.first[0])}"
+ options = v.first[1]
+ if options.is_a? Hash
+ if options[:agg]
+ select = "#{aggs[options[:agg]]}(#{select})"
+ end
+ if options[:as]
+ select += " AS #{formmatted_column_name(options[:as])}"
+ end
+ else
+ select += " AS #{formmatted_column_name(options)}"
+ end
+ #
+ else
+ select = "#{formatted_table_name(table)}.#{formmatted_column_name(v)}"
+ end
+ arr << select
+ end
+ end
+ arr
+ end
+
+
+ def build_joins(joins)
+ _build_joins(joins).join(" ")
+ end
+
+ def _build_joins(joins)
+ join_types = {
+ inner: "INNER JOIN",
+ left: "LEFT OUTER JOIN",
+ left_outer: "LEFT OUTER JOIN",
+ left_inner: "LEFT INNER JOIN",
+ right: "RIGHT OUTER JOIN",
+ right_outer: "RIGHT OUTER JOIN",
+ right_inner: "RIGHT INNER JOIN",
+ }
+
+ # joins_query = joins.map do |join|
+ # type = join_types[join[:type] || :inner]
+ # table = formatted_table_name(join[:table])
+ # on = join[:on]
+ #
+ # on_str = on ? "ON #{on}" : ""
+ # "#{type} #{table} #{on_str}"
+ # end
+ # joins_query.join(" ")
+ joins
+ end
+
+
+ def build_where(wheres)
+ arr = _build_where(wheres)
+ arr.join(" AND ")
+ end
+
+ def _build_where(wheres)
+ arr = []
+ case wheres.class.to_s
+ when "String"
+ return wheres
+ when "Hash"
+ wheres.each do |k, v|
+ table, column = formatted_table_and_column(k)
+ op = :eq
+ if v.is_a? Hash
+ op = v.delete :op
+ v = v.delete :value
+ end
+
+
+ arr << "#{table}.#{column} #{get_operator(op, v)}"
+ # if v.is_a? Hash
+ # # key, value = v.first
+ # # op
+ # # arr <<
+ # v.each do |k1, v1|
+ # arr << "#{formatted_table_name(k)}.#{formmatted_column_name(k1)} = '#{v1}'"
+ # end
+ # else
+ # arr << "#{formatted_table_name(table_name)}.#{formmatted_column_name(k)} = '#{v}'"
+ # end
+ end
+ when "Array"
+ wheres.each do |v|
+ arr += _build_where(v)
+ end
+ end
+ arr
+ end
+
+ def get_operator(op, value)
+ operators = {eq: "=", gt: ">", gte: ">=", lt: "<", lte: "<=", ne: "!=", in: "IN", nin: "NOT IN", like: "LIKE", between: "BETWEEN"}
+ ops = operators.values
+ operator = operators[op]
+ unless operator
+ operator = ops.include?(op) ? op : "="
+ end
+
+ if operator == "IN" || operator == "NOT IN"
+ value = "(#{value})"
+ elsif operator == "BETWEEN"
+ value = "'#{value[0]}' AND '#{value[1]}'"
+ else
+ value = "'#{value}'"
+ end
+ "#{operator} #{value}"
+ end
+
+ def formatted_table_and_column(key)
+ key = key.to_s
+ first, second = key.split(".")
+ if first && second
+ [formatted_table_name(first), formmatted_column_name(second)]
+ elsif(first)
+ [table_name, formmatted_column_name(first)]
+ end
+
+ end
+
+ def query(query, options)
+ data = []
results = connection.execute(query)
- results.map do |row|
- klass.new row
+ results.each(:cache_rows => false) do |row|
+ data << transform_attributes(row)
end
+ data
end
- def self.connection
- @connection ||= Toolhound.connection
+ def build_and_query(options, query_options = {})
+ sql = build_sql(options)
+ results = query(sql, query_options)
end
- def initialize(attrs = {}, options = {})
- # @original = attrs
- self.attributes = transform_attributes(attrs)
+
+ def build_sql(obj)
+ limit = obj[:limit]
+ selects = obj[:selects] ? build_selects(obj[:selects]) : "*"
+ joins = obj[:joins] ? build_joins(obj[:joins]) : ""
+ from = obj[:from] ? formatted_table_name(obj[:from]) : table_name
+ where = obj[:where] ? build_where(obj[:where]) : nil
+ order = obj[:order]
+
+ limit_str = limit ? "TOP(#{limit})" : ""
+ where_str = where.length > 0 ? "WHERE #{where}" : ""
+ order_str = order ? "ORDER BY #{order}" : ""
+
+ sql = "SELECT #{limit_str} #{selects} FROM #{from} #{joins} #{where_str} #{order_str}"
+ puts sql
+ sql
end
# def attributes=(hash)
# @attributes = hash
# end
@@ -69,25 +306,32 @@
def transform_attributes(attrs)
hash = {}
attrs.each do |k, v|
key = transform_attribute_key(k)
- hash[key] = v
+ if hash.include? key
+ hash[:"#{key}1"] = v
+ else
+ hash[key] = v
+ end
end
hash
end
def transform_attribute_key(key)
renamed = self.class.renamed_attributes
if renamed.include? key
renamed[key].to_sym
- elsif key == self.class.primary_key
- :id
else
# "varTransferReceiptPrefix"
- word = key[3..key.length]
- word.underscore.to_sym
+ if DB_TYPE_REGEX =~ key
+ word = key[3..key.length]
+ underscore(word).to_sym
+ else
+ underscore(key).to_sym
+ end
end
+
end
end
end