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:



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

TODO:

config for different db




# 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

Parameters:

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

    a customizable set of options

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.

Returns:



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!

Parameters:

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

    a customizable set of options

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