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

Returns a new instance of 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

Returns:



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/mdlsql/sqlquery.rb', line 97

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 ⇒ Object 




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

#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

Parameters:

  • opts (Hash) (defaults to: {}) —

    a customizable set of options

Options Hash (opts):

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


173
174
175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/mdlsql/sqlquery.rb', line 173

def join(table, cond1, cond2, opts={})
	@join ||= Array.new

	vars = {
		:table => table, 
		:cond1 => cond1, 
		:cond2 => cond2, 
		:op => opts[:op], 
		:type => opts[:type]
	}
	@join.push Join.new vars

	return self
end

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



188
189
190
191
 
# File 'lib/mdlsql/sqlquery.rb', line 188

def leftjoin(table, cond1, cond2, opts={})
	opts.update({:type => :left})
	join(table,cond1,cond2, opts)
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 



205
206
207
208
209
210
211
212
213
214
215
 
# File 'lib/mdlsql/sqlquery.rb', line 205

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.

Returns:



122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/mdlsql/sqlquery.rb', line 122

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
	tables.each do |table,table_alias|
		@tables.push Table.new table, table_alias
	end

	# @table = table
	# @table_alias = table_alias unless table_alias.nil?
	return self
end

#update(table = nil) ⇒ Object 



78
79
80
81
82
 
# File 'lib/mdlsql/sqlquery.rb', line 78

def update(table=nil)
	@method = :update
	from table if table
return self
end

#values(*val) ⇒ Object 



193
194
195
196
197
198
199
200
201
202
203
 
# File 'lib/mdlsql/sqlquery.rb', line 193

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!

Parameters:

  • opts (Hash) (defaults to: {}) —

    a customizable set of options

Options Hash (opts):

  • :op (String) —

    default is =

  • :concat (Symbol) —

    AND, OR…, default is AND.



151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/mdlsql/sqlquery.rb', line 151

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