module SQL

this file is part of manqod manqod is distributed under the CDDL licence the author of manqod is Dobai-Pataky Balint(dpblnt@gmail.com)

Public Instance Methods

add_where(query,where) click to toggle source
# 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) click to toggle source
# File lib/DrbDB/MyMultiSQL.rb, line 29
def escape_string(string)
              @my.escape_string(string)
end
fields(sql) click to toggle source
# File lib/DrbDB/MyMultiSQL.rb, line 50
def fields(sql)
      @my.fields(sql)
end
guess_base(query) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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?() click to toggle source
# File lib/DrbDB/MyMultiSQL.rb, line 8
def jruby?
        RUBY_PLATFORM =~ /java/
end
prepare_sql(isql,caller) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# File lib/DrbDB/MyMultiSQL.rb, line 33
def query(sql)
      edebug("SQL: #{sql}")
              @my.query(sql)
end
rows(sql,with_table=false) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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() click to toggle source
# 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