class Knj::Objects

Public Instance Methods

datarow_from_datarow_argument(datarow_argument) click to toggle source
# File lib/knj/objects/objects_sqlhelper.rb, line 480
def datarow_from_datarow_argument(datarow_argument)
  if datarow_argument.is_a?(String)
    const = Knj::Strings.const_get_full(datarow_argument)
  else
    const = datarow_argument
  end
  
  self.load_class(datarow_argument.to_s.split("::").last) if !const.initialized? #Make sure the class is initialized.
  
  return const
end
datarow_obj_from_args(args, list_args, class_name) click to toggle source

Used by sqlhelper-method to look up datarow-classes and automatically load them if they arent loaded already.

# File lib/knj/objects/objects_sqlhelper.rb, line 462
def datarow_obj_from_args(args, list_args, class_name)
  class_name = class_name.to_sym
  
  if !args.key?(:joined_tables)
    raise "No joined tables on '#{args[:table]}' to find datarow for: '#{class_name}'."
  end
  
  args[:joined_tables].each do |table_name, table_data|
    next if table_name.to_sym != class_name
    return self.datarow_from_datarow_argument(table_data[:datarow]) if table_data[:datarow]
    
    self.requireclass(class_name) if @objects.key?(class_name)
    return @args[:module].const_get(class_name)
  end
  
  raise "Could not figure out datarow for: '#{class_name}'."
end
not(not_v, val) click to toggle source
# File lib/knj/objects/objects_sqlhelper.rb, line 492
def not(not_v, val)
  if not_v == "not" or not_v == "not_"
    return val
  end
  
  return ""
end
sqlhelper(list_args, args_def) click to toggle source

This method helps build SQL from Objects-instances list-method. It should not be called directly but only through Objects.list.

# File lib/knj/objects/objects_sqlhelper.rb, line 3
def sqlhelper(list_args, args_def)
  args = args_def
  
  if args[:db]
    db = args[:db]
  else
    db = @args[:db]
  end
  
  if args[:table]
    table_def = "`#{db.esc_table(args[:table])}`."
  else
    table_def = ""
  end
  
  sql_joins = ""
  sql_where = ""
  sql_order = ""
  sql_limit = ""
  sql_groupby = ""
  
  do_joins = {}
  
  limit_from = nil
  limit_to = nil
  
  if list_args.key?("orderby")
    orders = []
    orderstr = list_args["orderby"]
    list_args["orderby"] = [list_args["orderby"]] if list_args["orderby"].is_a?(Hash)
    
    if list_args["orderby"].is_a?(String)
      found = false
      found = true if args[:cols].key?(orderstr)
      
      if found
        sql_order << " ORDER BY "
        ordermode = " ASC"
        if list_args.key?("ordermode")
          if list_args["ordermode"] == "desc"
            ordermode = " DESC"
          elsif list_args["ordermode"] == "asc"
            ordermode = " ASC"
            raise "Unknown ordermode: #{list_args["ordermode"]}"
          end
          
          list_args.delete("ordermode")
        end
        
        sql_order << "#{table_def}`#{db.esc_col(list_args["orderby"])}`#{ordermode}"
        list_args.delete("orderby")
      end
    elsif list_args["orderby"].is_a?(Array)
      sql_order << " ORDER BY "
      
      list_args["orderby"].each do |val|
        ordermode = nil
        orderstr = nil
        found = false
        
        if val.is_a?(Array)
          if val[1] == "asc"
            ordermode = " ASC"
          elsif val[1] == "desc"
            ordermode = " DESC"
          end
          
          if val[0].is_a?(Array)
            if args[:joined_tables]
              args[:joined_tables].each do |table_name, table_data|
                next if table_name.to_s != val[0][0].to_s
                do_joins[table_name] = true
                orders << "`#{db.esc_table(table_name)}`.`#{db.esc_col(val[0][1])}`#{ordermode}"
                found = true
                break
              end
            end
            
            raise "Could not find joined table for ordering: '#{val[0][0]}'." if !found
          else
            orderstr = val[0]
          end
        elsif val.is_a?(String)
          orderstr = val
          ordermode = " ASC"
        elsif val.is_a?(Hash) and val[:type] == :sql
          orders << val[:sql]
          found = true
        elsif val.is_a?(Hash) and val[:type] == :case
          caseorder = " CASE"
          
          val[:case].each do |key, caseval|
            col = key.first
            isval = key.last
            col_str = nil
            
            if col.is_a?(Array)
              raise "No joined tables for '#{args[:table]}'." if !args[:joined_tables]
              
              found = false
              args[:joined_tables].each do |table_name, table_data|
                if table_name == col.first
                  do_joins[table_name] = true
                  col_str = "`#{db.esc_table(table_name)}`.`#{db.esc_col(col.last)}`"
                  found = true
                  break
                end
              end
              
              raise "No such joined table on '#{args[:table]}': '#{col.first}' (#{col.first.class.name}) with the following joined table:\n#{Php4r.print_r(args[:joined_tables], true)}" if !found
            elsif col.is_a?(String) or col.is_a?(Symbol)
              col_str = "#{table_def}`#{col}`"
              found = true
            else
              raise "Unknown type for case-ordering: '#{col.class.name}'."
            end
            
            raise "'colstr' was not set." if !col_str
            caseorder << " WHEN #{col_str} = '#{db.esc(isval)}' THEN '#{db.esc(caseval)}'"
          end
          
          if val[:else]
            caseorder << " ELSE '#{db.esc(val[:else])}'"
          end
          
          caseorder << " END"
          orders << caseorder
        elsif val.is_a?(Hash)
          raise "No joined tables." if !args.key?(:joined_tables)
          
          if val[:mode] == "asc"
            ordermode = " ASC"
          elsif val[:mode] == "desc"
            ordermode = " DESC"
          end
          
          if args[:joined_tables]
            args[:joined_tables].each do |table_name, table_data|
              if table_data[:parent_table]
                table_name_real = table_name
              elsif table_data[:datarow]
                table_name_real = self.datarow_from_datarow_argument(table_data[:datarow]).classname
              else
                table_name_real = @args[:module].const_get(table_name).classname
              end
              
              if table_name.to_s == val[:table].to_s
                do_joins[table_name] = true
                
                if val[:sql]
                  orders << val[:sql]
                elsif val[:col]
                  orders << "`#{db.esc_table(table_name_real)}`.`#{db.esc_col(val[:col])}`#{ordermode}"
                else
                  raise "Couldnt figure out how to order based on keys: '#{val.keys.sort}'."
                end
                
                found = true
                break
              end
            end
          end
        else
          raise "Unknown object: #{val.class.name}"
        end
        
        found = true if args[:cols].key?(orderstr)
        
        if !found
          raise "Column not found for ordering: #{orderstr}."
        end
        
        orders << "#{table_def}`#{db.esc_col(orderstr)}`#{ordermode}" if orderstr
      end
      
      sql_order << orders.join(", ")
      list_args.delete("orderby")
    else
      raise "Unknown orderby object: #{list_args["orderby"].class.name}."
    end
  end
  
  list_args.each do |realkey, val|
    found = false
    
    if realkey.is_a?(Array)
      if !args[:joins_skip]
        datarow_obj = self.datarow_obj_from_args(args_def, list_args, realkey[0])
        args = datarow_obj.columns_sqlhelper_args
        raise "Couldnt get arguments from SQLHelper." if !args
      else
        datarow_obj = @args[:module].const_get(realkey[0])
        args = args_def
      end
      
      table_sym = realkey[0].to_sym
      do_joins[table_sym] = true
      list_table_name_real = table_sym
      table = "`#{db.esc_table(list_table_name_real)}`."
      key = realkey[1]
    else
      table = table_def
      args = args_def
      key = realkey
    end
    
    if args.key?(:cols_bools) and args[:cols_bools].index(key) != nil
      val_s = val.to_s
      
      if val_s == "1" or val_s == "true"
        realval = "1"
      elsif val_s == "0" or val_s == "false"
        realval = "0"
      else
        raise "Could not make real value out of class: #{val.class.name} => #{val}."
      end
      
      sql_where << " AND #{table}`#{db.esc_col(key)}` = '#{db.esc(realval)}'"
      found = true
    elsif args[:cols].key?(key)
      if val.is_a?(Array)
        if val.empty? and db.opts[:type].to_s == "mysql"
          sql_where << " AND false"
        else
          escape_sql = Knj::ArrayExt.join(
            :arr => val,
            :callback => proc{|value|
              db.escape(value)
            },
            :sep => ",",
            :surr => "'"
          )
          sql_where << " AND #{table}`#{db.esc_col(key)}` IN (#{escape_sql})"
        end
      elsif val.is_a?(Hash) and val[:type].to_sym == :col
        raise "No table was given for join: '#{val}', key: '#{key}' on table #{table}." if !val.key?(:table)
        do_joins[val[:table].to_sym] = true
        sql_where << " AND #{table}`#{db.esc_col(key)}` = `#{db.esc_table(val[:table])}`.`#{db.esc_col(val[:name])}`"
      elsif val.is_a?(Hash) and val[:type] == :sqlval and val[:val] == :null
        sql_where << " AND #{table}`#{db.esc_col(key)}` IS NULL"
      elsif val.is_a?(Proc)
        call_args = Knj::Hash_methods.new(:ob => self, :db => db)
        sql_where << " AND #{table}`#{db.esc_col(key)}` = '#{db.esc(val.call(call_args))}'"
      else
        sql_where << " AND #{table}`#{db.esc_col(key)}` = '#{db.esc(val)}'"
      end
      
      found = true
    elsif key.to_s == "limit_from"
      limit_from = val.to_i
      found = true
    elsif key.to_s == "limit_to"
      limit_to = val.to_i
      found = true
    elsif key.to_s == "limit"
      limit_from = 0
      limit_to = val.to_i
      found = true
    elsif args.key?(:cols_dbrows) and args[:cols_dbrows].index("#{key.to_s}_id") != nil
      if val == false
        sql_where << " AND #{table}`#{db.esc_col(key.to_s + "_id")}` = '0'"
      elsif val.is_a?(Array)
        if val.empty?
          sql_where << " AND false"
        else
          sql_where << " AND #{table}`#{db.esc_col("#{key}_id")}` IN (#{Knj::ArrayExt.join(:arr => val, :sep => ",", :surr => "'", :callback => proc{|obj| obj.id.sql})})"
        end
      else
        sql_where << " AND #{table}`#{db.esc_col(key.to_s + "_id")}` = '#{db.esc(val.id)}'"
      end
      
      found = true
    elsif match = key.match(/^([A-z_\d]+)_(search|has)$/) and args[:cols].key?(match[1]) != nil
      if match[2] == "search"
        Knj::Strings.searchstring(val).each do |str|
          sql_where << " AND #{table}`#{db.esc_col(match[1])}` LIKE '%#{db.esc(str)}%'"
        end
      elsif match[2] == "has"
        if val
          sql_where << " AND #{table}`#{db.esc_col(match[1])}` != ''"
        else
          sql_where << " AND #{table}`#{db.esc_col(match[1])}` = ''"
        end
      end
      
      found = true
    elsif match = key.match(/^([A-z_\d]+)_(not|lower)$/) and args[:cols].key?(match[1])
      if match[2] == "not"
        if val.is_a?(Array)
          if val.empty?
            #ignore.
          else
            escape_sql = Knj::ArrayExt.join(
              :arr => val,
              :callback => proc{|value|
                db.escape(value)
              },
              :sep => ",",
              :surr => "'"
            )
            sql_where << " AND #{table}`#{db.esc_col(match[1])}` NOT IN (#{escape_sql})"
          end
        else
          sql_where << " AND #{table}`#{db.esc_col(match[1])}` != '#{db.esc(val)}'"
        end
      elsif match[2] == "lower"
        sql_where << " AND LOWER(#{table}`#{db.esc_col(match[1])}`) = LOWER('#{db.esc(val)}')"
      else
        raise "Unknown mode: '#{match[2]}'."
      end
      
      found = true
    elsif args.key?(:cols_date) and match = key.match(/^(.+)_(day|week|month|year|from|to|below|above)(|_(not))$/) and args[:cols_date].index(match[1]) != nil
      not_v = match[4]
      val = Datet.in(val) if val.is_a?(Time)
      
      if match[2] == "day"
        if val.is_a?(Array)
          sql_where << " AND ("
          first = true
          
          val.each do |realval|
            if first
              first = false
            else
              sql_where << " OR "
            end
            
            sql_where << "#{db.sqlspecs.strftime("%d %m %Y", "#{table}`#{db.esc_col(match[1])}`")} #{self.not(not_v, "!")}= #{db.sqlspecs.strftime("%d %m %Y", "'#{db.esc(realval.dbstr)}'")}"
          end
          
          sql_where << ")"
        else
          sql_where << " AND #{db.sqlspecs.strftime("%d %m %Y", "#{table}`#{db.esc_col(match[1])}`")} #{self.not(not_v, "!")}= #{db.sqlspecs.strftime("%d %m %Y", "'#{db.esc(val.dbstr)}'")}"
        end
      elsif match[2] == "week"
        sql_where << " AND #{db.sqlspecs.strftime("%W %Y", "#{table}`#{db.esc_col(match[1])}`")} #{self.not(not_v, "!")}= #{db.sqlspecs.strftime("%W %Y", "'#{db.esc(val.dbstr)}'")}"
      elsif match[2] == "month"
        sql_where << " AND #{db.sqlspecs.strftime("%m %Y", "#{table}`#{db.esc_col(match[1])}`")} #{self.not(not_v, "!")}= #{db.sqlspecs.strftime("%m %Y", "'#{db.esc(val.dbstr)}'")}"
      elsif match[2] == "year"
        sql_where << " AND #{db.sqlspecs.strftime("%Y", "#{table}`#{db.esc_col(match[1])}`")} #{self.not(not_v, "!")}= #{db.sqlspecs.strftime("%Y", "'#{db.esc(val.dbstr)}'")}"
      elsif match[2] == "from" or match[2] == "above"
        sql_where << " AND #{table}`#{db.esc_col(match[1])}` >= '#{db.esc(val.dbstr)}'"
      elsif match[2] == "to" or match[2] == "below"
        sql_where << " AND #{table}`#{db.esc_col(match[1])}` <= '#{db.esc(val.dbstr)}'"
      else
        raise "Unknown date-key: #{match[2]}."
      end
      
      found = true
    elsif args.key?(:cols_num) and match = key.match(/^(.+)_(from|to|above|below|numeric)$/) and args[:cols_num].index(match[1]) != nil
      if match[2] == "from"
        sql_where << " AND #{table}`#{db.esc_col(match[1])}` >= '#{db.esc(val)}'"
      elsif match[2] == "to"
        sql_where << " AND #{table}`#{db.esc_col(match[1])}` <= '#{db.esc(val)}'"
      elsif match[2] == "above"
        sql_where << " AND #{table}`#{db.esc_col(match[1])}` > '#{db.esc(val)}'"
      elsif match[2] == "below"
        sql_where << " AND #{table}`#{db.esc_col(match[1])}` < '#{db.esc(val)}'"
      else
        raise "Unknown method of treating cols-num-argument: #{match[2]}."
      end
      
      found = true
    elsif match = key.match(/^(.+)_lookup$/) and args[:cols].key?("#{match[1]}_id") and args[:cols].key?("#{match[1]}_class")
      sql_where << " AND #{table}`#{db.esc_col("#{match[1]}_class")}` = '#{db.esc(val.table)}'"
      sql_where << " AND #{table}`#{db.esc_col("#{match[1]}_id")}` = '#{db.esc(val.id)}'"
      found = true
    elsif realkey == "groupby"
      found = true
      
      if val.is_a?(Array)
        val.each do |col_name|
          raise "Column '#{val}' not found on table '#{table}'." if !args[:cols].key?(col_name)
          sql_groupby << ", " if sql_groupby.length > 0
          sql_groupby << "#{table}`#{db.esc_col(col_name)}`"
        end
      elsif val.is_a?(String)
        sql_groupby << ", " if sql_groupby.length > 0
        sql_groupby << "#{table}`#{db.esc_col(val)}`"
      else
        raise "Unknown class given for 'groupby': '#{val.class.name}'."
      end
    end
    
    list_args.delete(realkey) if found
  end
  
  args = args_def
  
  if !args[:joins_skip]
    raise "No joins defined on '#{args[:table]}' for: '#{args[:table]}'." if !do_joins.empty? and !args[:joined_tables]
    
    do_joins.each do |table_name, temp_val|
      raise "No join defined on table '#{args[:table]}' for table '#{table_name}'." if !args[:joined_tables].key?(table_name)
      table_data = args[:joined_tables][table_name]
      
      if table_data.key?(:parent_table)
        join_table_name_real = table_name
        sql_joins << " LEFT JOIN `#{table_data[:parent_table]}` AS `#{table_name}` ON 1=1"
      else
        const = @args[:module].const_get(table_name)
        join_table_name_real = const.classname
        sql_joins << " LEFT JOIN `#{const.table}` AS `#{const.classname}` ON 1=1"
      end
      
      if table_data[:ob]
        ob = table_data[:ob]
      else
        ob = self
      end
      
      class_name = args[:table].to_sym
      
      if table_data[:datarow]
        datarow = self.datarow_from_datarow_argument(table_data[:datarow])
      else
        self.requireclass(class_name) if @objects.key?(class_name)
        datarow = @args[:module].const_get(class_name)
      end
      
      if !datarow.columns_sqlhelper_args
        ob.requireclass(datarow.table.to_sym)
        raise "No SQL-helper-args on class '#{datarow.table}' ???" if !datarow.columns_sqlhelper_args
      end
      
      newargs = datarow.columns_sqlhelper_args.clone
      newargs[:table] = join_table_name_real
      newargs[:joins_skip] = true
      
      #Clone the where-arguments and run them against another sqlhelper to sub-join.
      join_args = table_data[:where].clone
      ret = self.sqlhelper(join_args, newargs)
      sql_joins << ret[:sql_where]
      
      #If any of the join-arguments are left, then we should throw an error.
      join_args.each do |key, val|
        raise "Invalid key '#{key}' when trying to join table '#{table_name}' on table '#{args_def[:table]}'."
      end
    end
  end
  
  #If limit arguments has been given then add them.
  if limit_from and limit_to
    sql_limit = " LIMIT #{limit_from}, #{limit_to}"
  end
  
  sql_groupby = nil if sql_groupby.length <= 0
  
  return {
    :sql_joins => sql_joins,
    :sql_where => sql_where,
    :sql_limit => sql_limit,
    :sql_order => sql_order,
    :sql_groupby => sql_groupby
  }
end