Query replacement with values looked up from a database
I am trying to create a virtual database layer that is (almost) transparent to the client. I have a database table consisting of virtual database names and the actual database that each name corresponds to. I would like to set the proxy up such that when a user submits a SELECT query containing a virtual database name enclosed in [brackets], the proxy first looks up the string inside the brackets in the virtual database table to find the actual database name. Then, I would like the proxy to replace the bracketed string in the original query with the actual database name. I haven't been able to wrap my head around how to do this from the docs and examples online. The script I am trying to get to work is copied below. read_query() first reads a query and checks that it's a SELECT and that it has a bracketed string. It then puts a new query on the queue for the database corresponding to the string contained in the brackets, and stores the original query in a global variable 'query'. I want read_query_result() to modify the original query sent by the client by replacing the bracketed string with the returned database name. Then, I want read_query_result to query the server with this modified query, and return that result to the client.
function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
query = string.sub(packet, 2)
if string.match(string.upper(query), '^%s*SELECT') then
print("String matched")
local _, _, vTable = string.find(query, '%[(.+)%]')
if vTable then
print("Original query: " .. query)
local physicalQuery = "SELECT physicalTableName FROM proxy_tables.virtualTableMap WHERE virtualTableName = " .. vTable .. ";"
proxy.queries:append(1, string.char(proxy.COM_QUERY) .. physicalQuery )
return proxy.PROXY_SEND_QUERY
end
end
end
end
function read_query_result( response )
if response.id == 1 then
proxy.queries:append(2, string.gsub(query, '%[.+%]', response.resultset.rows[1]))
return proxy.PROXY_SEND_QUERY
elseif response.id == 2 then
return proxy.SEND_RESULT
end
end
The main problem I have is that I do not know how to access the result set contained in the response from the server. Is there any place that I can find the structure of this table so that I can know how to access the data contained inside? Lua recognizes this as 'userdata', and I'm not sure how to access the data returned by the server. I would like to take this data and plug it into the original query. In all the examples I've seen online, these results are either ignored or sent as is, but never modified and sent.
Beyond that, is it even possible to have read_query_result() submit another query to the server? If it is possible, then what is the best way for me to get the original query information to read_query_result from read_query? I stored it in a global variable 'query' in the above script, but I'm not sure if global variables are maintained by the proxy. Is the functionality that I'm after even possible? Any and all help would be greatly appreciated.
Edited 1 time(s). Last edit at 09/26/2007 05:13PM by Benjamin Lamothe.