class MdlSql::SqlQuery

Public Class Methods

config(values={}) click to toggle source

Configuration

# File lib/mdlsql/sqlquery.rb, line 32
def self.config(values={})
     # Does config need to be parsed from a config file?
     # @todo check relative paths so file is correctly found.
     #   Maybe should be wise to use full paths.
     if values[:parse]
                      if values[:parse] == :yml || values[:parse] == :yaml
                              if values[:file]
                                      values = YAML.load_file(values[:file])

                                      # Convert keys to symbols
                                      values = values.inject({}){|memo,(k,v)| memo[k.to_sym] = v; memo}
                              else
                                      raise ArgumentError, "File value missing, config file could not be parsed."
                              end
                      end

              end

              @@host = values[:host]
              @@username = values[:username]
              @@password = values[:password]
              @@db = values[:database]
              @@socket = values[:socket]
              @@debug = values[:debug]
      end
new() click to toggle source
# File lib/mdlsql/sqlquery.rb, line 21
def initialize
  # Initializes query as a string
  #   @return self [SqlQuery] so other methods may be concatenated.
  
              return self
end

Public Instance Methods

cols(*values)
Alias for: columns
columns(*values) click to toggle source
@todo check column uniqueness in hash
@todo revise

@todo use Col class @return (@see initialize)

# File lib/mdlsql/sqlquery.rb, line 104
def columns(*values)
              @cols ||= Hash.new

              # key AS value
              if values[0].is_a? Hash
                      values.each do |val|
                              @cols.update(val)
                      end
                      
              else
                      values.each do |val|
                              @cols.update({val => val})
                      end
              end
              return self
      end
Also aliased as: cols
execute(opts={}) click to toggle source

@!method execute() Exacution command @todo return true/false when inserting/updating @todo config for different db

# File lib/mdlsql/sqlquery.rb, line 233
def execute opts={}
         if @@host.nil? || @@username.nil? || @@password.nil? || @@db.nil?
                 raise 'MdlSql has not been correctly configured, please use config() to set host, username, password and db.'
         end
         unless $client
             $client = Mysql2::Client.new(
              :host => @@host, 
              :username => @@username, 
              :password => @@password, 
              :database => @@db,
                                 :symbolize_keys => true
                 )
         end

         if opts[:query]
                 query = opts[:query]
         else                 
                 query = String.new

                 @@socket ||= :mysql

                 case @@socket
                 when :mysql
                         sock = MysqlBuilder
                 end

                 query = sock.send("#{@method}", 
                         {:tables => @tables, 
                                 :where => @where, 
                                 :cols => @cols,
                                 :values => @values,
                                 :join => @join}
                 )
         end

         puts "Query: \n#{query}" if @@debug

         @result = $client.query query
         return @result

 end
from(tables = {})
Alias for: tables
insert() click to toggle source
# File lib/mdlsql/sqlquery.rb, line 73
def insert()
     @method = :insert
              return self
end
into(tables = {})

alias into() and from() select table

Alias for: tables
join(table, cond1, cond2, opts={}) click to toggle source

@option opts [Symbol/String] :op @option opts [Symbol] :type

# File lib/mdlsql/sqlquery.rb, line 185
def join(table, cond1, cond2, opts={})
        @join ||= Array.new

        vars = {
                :table => table, 
                :cond1 => cond1, 
                :cond2 => cond2, 
        }
        vars.merge! opts
        @join.push Join.new vars

        return self
end
leftjoin(table, cond1, cond2, opts={}) click to toggle source
# File lib/mdlsql/sqlquery.rb, line 199
def leftjoin(table, cond1, cond2, opts={})
        opts.update({:type => :left})
        join(table,cond1,cond2, opts)
end
query(str) click to toggle source

Just execute a query. Compromises compatibility.

# File lib/mdlsql/sqlquery.rb, line 278
def query str
        execute :query => str
end
select() click to toggle source

Method selection:

select()
insert()
update(table=nil)
# File lib/mdlsql/sqlquery.rb, line 65
def select()
  # Sets method to select
              #    @return (@see initialize)
  @method = :select
              
              return self
end
set(val={}) click to toggle source
# File lib/mdlsql/sqlquery.rb, line 216
def set(val={})
        if @method == :update
                @values ||= Hash.new

                @values.update val
        else
                raise 'Use set() only for #update.'
        end

        return self
end
tables(tables = {}) click to toggle source

Selects table from which to select (or insert, update) with possible alias. @todo use a hash here to allow many tables (for a select, for example). @note use from() when selecting & into() when inserting for readability.

@return (@see initialize)
# File lib/mdlsql/sqlquery.rb, line 129
def tables(tables = {})
              # table = table.to_sym if table.is_a? String
              # table_alias = table_alias if table_alias.is_a? String

              @tables ||= Array.new
              if tables.is_a? Hash
                      tables.each do |table, table_alias|
                              @tables.push Table.new table, table_alias
                      end
              elsif tables.is_a? Symbol
                      @tables.push Table.new tables, tables
              end
                      

              # @table = table
              # @table_alias = table_alias unless table_alias.nil?
              return self
      end
Also aliased as: into, from
update(table=nil) click to toggle source
# File lib/mdlsql/sqlquery.rb, line 78
def update(table=nil)
     @method = :update

     if table.is_a? Symbol
             from table => table
     elsif table.is_a? String
             from table.to_sym => table.to_sym
     elsif table.is_a? Hash
             from table
     end
              return self
end
values(*val) click to toggle source
# File lib/mdlsql/sqlquery.rb, line 204
def values(*val)
        if @method == :insert
                @values ||= Array.new

                @values << val
        else
                raise 'Use values() only for #insert.'
        end

        return self
end
where(cond1, cond2, opts={}) click to toggle source

Generates a where clause Maybe it's a good idea to make a Where object.

FFS, HAVE A CLEAR IDEA BEFORE WRITING!

@option opts [String] :op default is = @option opts [Symbol] :concat AND, OR…, default is AND. @note First where clause's concat is ignored.

@todo Add IN, BETWEEN and LIKE (can be done with actual where).

# File lib/mdlsql/sqlquery.rb, line 163
def where(cond1, cond2, opts={})
        opts[:op] ||= :'='
        opts[:concat] ||= :AND

        # if cond1.is_a? Hash
        #    if cond1[:table] && cond1[:col]
        #            cond1 = Col.new cond[:col], cond[:table]

        @where ||= Array.new
        wh = Where.new(
                :cond1 => cond1,
                :cond2 => cond2,
                :op => opts[:op],
                :concat => opts[:concat]
        )
        @where.push wh

        return self
end