QUERY CACHE - SQL Control Interface

5 new SQL commands:

1)QUERY CACHE LOCK <TIMEOUT>
2)QUERY CACHE UNLOCK
3)QUERY CACHE REMOVE <query_id>
4)QUERY CACHE REMOVE TABLE LIKE "database.table name"
5)QUERY CACHE RESULT <query_id>

let me explain:

1/2)
1) here we will add a new variable (lock_connection_id) at query cache class
this will receive the connection id (or another connection unique identifier)
<timeout> is the time to wait query cache to lock, if timeout<=0 it will wait forever until lock query cache
if query cache is turned off or, if command timeout => return a error "can't lock query cache"

2) this set the query cache lock_connection_id to 0 and unlock query cache

—

3) this remove the query inside query hash of query cache class that's in the position = 99999 (query_id)
if it doesn't exists return an error
if query cache isn't locked for this connection (connection!=0 and connection!=current connection) return an error
if query cache isn't locked for any connection (connection=0) return a warning

4) this will remove queries in cache that use some table using "LIKE" operator to get table names
if query cache isn't locked for this connection (connection!=0 and connection!=current connection) return an error
if query cache isn't locked for any connection (connection=0) return a warning

5) get the result of a query cached (something like MDEV-4599 )

internal changes.. well i don't know all we have to change but...
1)
it's a soft lock, just to one process don't execute lock while other process is using it, but it don't lock internall events like invalidation

somewhere in the end of query cache lock function:

if lock_connection_id !=0, check if that connection is a dead connection or not
if it is not a dead connection
return "can't lock"
else
set lock_connection_id=0 (clear dead lock)
return "lock acquired"

check that we will lock all query cache system using SQL user interface...
maybe a new priority could/should be created at super user level (?!)

2) unlock will just set lock_connection_id=0 when lock_connection_id = current connection_id
if lock_connection_id!=0 check if it's a dead connection
if it's a dead connection set it to 0
continue to next line...
if the lock_connection_id=0 return a warning "query cache not locked"
if lock_connection!=0 (should never happen since we will not get the lock to check this variable)
return a error "query cache locked by some one else"
3) this will remove a query cache inside cache
if query cache isn't locked by "query cache lock" SQL
we generate a warning to tell user to lock query cache (safe)
if index don't exists return an error "this entry doesn't exists, are you crazy?"
4) the same as (3) but remove using table name
—
end

with this, we can add a new feature of MDEV-4583 (query cache clean procedure)

when query cache is full, we could call a user procedure and execute a user procedure to clean cache
since we have information_schema.query_cache_queries and information_schema.query_cache_tables
we can do this all in SQL!!! =]
i don't know how to do it, but i think we can do it =)
in other words, MDEV-4583 could be reduced to:

query_cache_clean_method=DEFAULT / PROCEDURE
query_cache_procedure_name=some global procedure name, maybe inside <mysql> schema
query_cache_procedure_timeout=9999999

maybe we could create a new query_cache table with less information
removing "statment_text" column since it's very big (~32000 bytes)

after accepting MDEV-4581, and this MDEV-4584, we should add a new table to MDEV-4581 (qc_info.cc) that have this information:

Instead of a column in QUERY_CACHE_QUERIES table with a 'group_concat' column having tables like: `db_name`.`table_used1`, `db_name`.`table_used2` ..