Class: MdlSql::SqlQuery

Inherits:
Object
  • Object
show all
Defined in:
lib/mdlsql/sqlquery.rb

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ SqlQuery 



21
22
23
24
25
26
 
# 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

Class Method Details

.config(values = {}) ⇒ Object

Configuration



32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
# 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

Instance Method Details

#columns(*values) ⇒ @see initialize Also known as: cols 



104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# 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

#execute(opts = {}) ⇒ Object 




# File 'lib/mdlsql/sqlquery.rb', line 228

#insert ⇒ Object 



73
74
75
76
 
# File 'lib/mdlsql/sqlquery.rb', line 73

def insert()
  @method = :insert
return self
end

#join(table, cond1, cond2, opts = {}) ⇒ Object

Options Hash (opts):

  • :op (Symbol/String)
  • :type (Symbol) 


185
186
187
188
189
190
191
192
193
194
195
196
197
 
# 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 = {}) ⇒ Object 



199
200
201
202
 
# 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) ⇒ Object

Just execute a query. Compromises compatibility.



278
279
280
 
# File 'lib/mdlsql/sqlquery.rb', line 278

def query str
  execute :query => str
end

#select ⇒ Object

Method selection: select() insert() update(table=nil)



65
66
67
68
69
70
71
 
# File 'lib/mdlsql/sqlquery.rb', line 65

def select()
  # Sets method to select
# @return (@see initialize)
  @method = :select

return self
end

#set(val = {}) ⇒ Object 



216
217
218
219
220
221
222
223
224
225
226
 
# 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 = {}) ⇒ @see initialize Also known as: into, from

TODO:

use a hash here to allow many tables (for a select, for example).

Note:

use from() when selecting & into() when inserting for readability.

Selects table from which to select (or insert, update) with possible alias.



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# 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

#update(table = nil) ⇒ Object 



78
79
80
81
82
83
84
85
86
87
88
89
 
# 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) ⇒ Object 



204
205
206
207
208
209
210
211
212
213
214
 
# 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 = {}) ⇒ Object

TODO:

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

Note:

First where clause’s concat is ignored.

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

FFS, HAVE A CLEAR IDEA BEFORE WRITING!

Options Hash (opts):

  • :op (String) —

    default is =

  • :concat (Symbol) —

    AND, OR…, default is AND.



163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
 
# 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