class MdlSql::SqlQuery
Public Class Methods
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
# 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
@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
@!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
# File lib/mdlsql/sqlquery.rb, line 73 def insert() @method = :insert return self end
@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
# File lib/mdlsql/sqlquery.rb, line 199 def leftjoin(table, cond1, cond2, opts={}) opts.update({:type => :left}) join(table,cond1,cond2, opts) end
Just execute a query. Compromises compatibility.
# File lib/mdlsql/sqlquery.rb, line 278 def query str execute :query => str end
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
# 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
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
# 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
# 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
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