Module: SQL

Includes:
Eprint
Included in:
DrbDb
Defined in:
lib/DrbDB/MyMultiSQL.rb

Overview

this file is part of manqod manqod is distributed under the CDDL licence the author of manqod is Dobai-Pataky Balint([email protected])

Constant Summary

Constants included from Eprint

Eprint::TERM_COLOUR

Instance Method Summary collapse

Methods included from Eprint

#ecode, #edebug, #eerror, #eeval, #eexception, #efatal, #einfo, #eprint, #ewarn, #getBinding, #report_mail

Instance Method Details

#add_where(query, where) ⇒ Object 



112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 112

def add_where(query,where)
	sql=String.new(query)
	sql=sql+" \r\n"
	llength=sql.length
	while (sql.gsub!("`"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("\r\n"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("\n"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("  "," ")) and sql.length != llength do llength=sql.length end

	if twi=top_level(sql,"where")
		#insert where after 'where'
		ret=sql[0 .. twi+"where".length] + where +" and "+ sql[twi+"where".length .. sql.length]
	else
		toi=top_level(sql,"group by")
		toi=top_level(sql,"having") if toi.nil?
		toi=top_level(sql,"order by") if toi.nil?
		toi=top_level(sql,"limit") if toi.nil?
		if toi
			#insert the where before group by/having/order by/limit
			ret=sql[0 .. toi-1] + " where #{where} " + sql[toi-1 .. sql.length]
		else
			#just append the where
			ret="#{sql} where #{where}"
		end
	end
	ret
end

#escape_string(string) ⇒ Object 



29
30
31
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 29

def escape_string(string)
@my.escape_string(string)
end

#fields(sql) ⇒ Object 



50
51
52
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 50

def fields(sql)
	@my.fields(sql)
end

#guess_base(query) ⇒ Object 



94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 94

def guess_base(query)
	sql=String.new(query)
	sql=sql+" \r\n"
	llength=sql.length
	while (sql.gsub!("`"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("\r\n"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("\n"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("  "," ")) and sql.length != llength do llength=sql.length end

	if fi=top_level(sql,"from")
		fi=sql.index(" ",fi+"from".length)+1
		ret=sql[fi .. sql.index(" ",fi)-1]
		else
		ret=nil
	end
	ret
end

#guess_table(query, field) ⇒ Object 



54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 54

def guess_table(query,field)
	sql=String.new(query)
	sql=sql+" \r\n"
	llength=sql.length
	while (sql.gsub!("`"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("\r\n"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("\n"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("  "," ")) and sql.length != llength do llength=sql.length end

	llast=0
	while (last=sql.index(")") and (llast != last))
		llast = last
		if first=sql[0 .. last].rindex("(") then 
			sql[first .. last]=self.guess_table(sql[first +1 .. last -1],field)
		end
	end

	sql=sql.gsub(",","")
	first=sql.index(" ",sql.upcase.rindex("FROM")+"from".length)+1 if sql.upcase.rindex("FROM")
	first=sql.index(" ",sql.upcase.rindex("UPDATE")+"update".length)+1 if sql.upcase.rindex("UPDATE")
	first=sql.index(" ",sql.upcase.rindex("INSERT INTO")+"insert into".length)+1 if sql.upcase.rindex("INSERT INTO")
	if first
		last=sql.index(" ",first)-1
		table=sql[first .. last]
	else
		table=""
	end
	table
end

#init_sql(host, user, password, dbase) ⇒ Object 



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 12

def init_sql(host,user,password,dbase)
	if jruby? 
		einfo("using jruby")
		require 'DrbDB/MyMultiSQL/jdbc.rb'
	else
		einfo("using mysql-ruby")
		require 'DrbDB/MyMultiSQL/mysql-ruby.rb'
	end
	begin
		@my=MySQL.new(host,user,password,dbase)
		rescue =>err
		eerror(err)
		@my=nil
	end
	@my
end

#jruby? ⇒ Boolean

Returns:

  • (Boolean) 


8
9
10
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 8

def jruby?
	RUBY_PLATFORM =~ /java/
end

#prepare_sql(isql, caller) ⇒ Object 



157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 157

def prepare_sql(isql,caller)
	sql=String.new(isql)
	if sql.length >0
		while first=sql.index("{")
			i=first+1;k=1
			while (i<sql.length and k>0)
				k=k+1 if sql[i .. i]=="{"
				k=k-1 if sql[i .. i]=="}"
				i=i+1
			end
			last = i-1
			if sql[last .. last]=='}' then
				ind=sql[first .. last]
				indd=ind[1 .. ind.rindex("}")-1]
				begin
					sql[ind]=eval(indd,caller.getBinding).to_s
				rescue StandardError,SyntaxError, NameError => err
					ewarn("String doesn't compile: #{err}\nin query:#{sql}\n")
					sql[ind]=""
				end
			else
				ewarn("can't find closing bracket - } in #{sql[first .. sql.length]}\n")
			end
		end
	end
	return sql
end

#qrow(sql, with_table = false) ⇒ Object 



38
39
40
41
42
43
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 38

def qrow(sql,with_table=false)
 	edebug("SQL: #{sql}")
	if ret=@my.rows(sql,with_table)
		ret.first
	end
end

#query(sql) ⇒ Object 



33
34
35
36
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 33

def query(sql)
	edebug("SQL: #{sql}")
@my.query(sql)
end

#rows(sql, with_table = false) ⇒ Object 



45
46
47
48
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 45

def rows(sql,with_table=false)
 	edebug("SQL: #{sql}")
	@my.rows(sql,with_table)
end

#select_last(query, orderby) ⇒ Object

for select last



140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 140

def select_last(query,orderby) #for select last
	sql=String.new(query)
	sql=sql+" \r\n"
	llength=sql.length
	while (sql.gsub!("`","")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("\r\n"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("\n"," ")) and sql.length != llength do llength=sql.length end
	while (sql.gsub!("  "," ")) and sql.length != llength do llength=sql.length end

	toi=top_level(sql,"order by")
	toi=top_level(sql,"limit") if toi.nil?
	toi=sql.length if toi.nil?
		#insert the orderby
	ret=sql[0 .. toi-1] + " order by #{orderby} desc limit 1"
	ret
end

#top_level(sql, subject) ⇒ Object 



84
85
86
87
88
89
90
91
92
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 84

def top_level(sql,subject)
	i=0
	tsi=nil
	while(i=sql.upcase.index(subject.upcase,i+1))
		tsi=i if sql[0 .. i].count('(') == sql[0 .. i].count(')')
			#at i is toplevel subject
	end
	tsi
end

#update_manqod_db ⇒ Object 



185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
 
# File 'lib/DrbDB/MyMultiSQL.rb', line 185

def update_manqod_db
	current_version="unknown"
	begin
		t=qrow("select max(`version`) as version from db_version")
		current_version=t["version"].to_f unless t.nil?
		einfo("current version: #{current_version}")
		rescue
			ewarn("not auto update compatible")
	end
	return false if t.nil?
	usql=File.new(File::expand_path(File.join(File.join(File.join(File.join(File.dirname(__FILE__),".."),".."),"etc"),"update.sql")),"r")
	sql=""
	while line=usql.gets do
		next if line.length<5 #empty line
		next if line.index("--")&&line.index("--")<2 #line begins with comment
		sql="#{sql} #{line}"
		line_version=sql.split("--")[-1]
		if sql.rindex(";") && line_version && line_version.to_f>0
			line_version=line_version.to_f
			#found end of query and version number
			sql=sql[0 .. sql.rindex("--")-1]
			if line_version > current_version
				einfo("applying update version: #{line_version.to_f}, query: \"#{sql}\"")
				success=true
				begin
					query(sql)
					rescue
					success=false
				end
				if success then
					query("insert into db_version (`version`) values (#{line_version})")
					else
					eerror("failed to apply version: #{line_version}")
				end
			end
			sql=""
		end
	end
end