Ok – so today you are James Bond who just got itself (again!)
messed into a very dangerous endeavor and one of your tasks is to find some information
hidden in a CUBRID database! :)
There is some data in there that you need to get your hands
on, and the only thing you know is that the text you are looking for contains a
special word - “Estiarte”.
... and just to make things worse, you don’t have any Hollywood-magic USB
stick which copies and indexes 1 TB in 1 sec!!! (ha-ha) …or any other
spy-wonder-tools…!
But you are not that helpless… …because you already know some
things about CUBRID, right? :)
So you broke into the mystical computer’s room, and you have
right in front of you a nice, (already unlocked!) CUBRID SQL command prompt
window – so let's START! …just make sure you don’t waste any time – the bad guys are
coming for you…!! (If you wonder why it’s unlocked,
that’s because the administrator forgot to disable the “public” user or to change its default password!)
First of all, James Bond needs a plan – because he is always prepared!!!… So let’s work
on the plan…
What do we need to be able to discover the text which
contains the “keyword”?
- We need to write down some SELECT(s) using a LIKE clause
- We need to know on which columns we will execute the SELECT(s)
- We need to know on which tables we will execute the SELECT(s)
Also, we need to look into:
- Minimizing the effort – time is an issue here!
- Optimize the execution of SQLs, in any way we can
The list of user tables can be obtained simple as that, from
the db_class system metadata table:
SELECT * FROM db_class
WHERE class_type='CLASS' and is_system_class='NO';
And the list of the columns can be similarly obtained by
querying the db_columns metadata table.
But let’s do better – let’s combine tables and columns in a
single query, to find out what columns and from which tables we need to query:
SELECT
class_name, attr_name
FROM
db_attribute
WHERE
class_name IN (select class_name from db_class
where class_type='CLASS' and is_system_class='NO')
ORDER BY class_name
ASC, attr_name ASC
Another piece of the puzzle is how we do the LIKE filter:
SELECT * FROM code
WHERE s_name LIKE '%Estiarte%'
Please
note that for simplicity we are assuming that the column is of type
CHAR/VARCHAR/STRING.
But what
if isn’t…? Shouldn’t we do a TO_CHAR….? (if we don’t, http://www.youtube.com/watch?v=ks7-A-7Zvak… :) )
|
OK – so we have the tables, the columns and we know how to
search for our keyword within the columns data.
How do we assemble these pieces efficient, simple and
fast…?
Well, what you can do immediately is to generate the set of SQL
queries which must be used in the CUBRID search:
SELECT
CONCAT('SELECT * FROM ', class_name, ' WHERE
', attr_name, ' LIKE ''%Estiarte%'';')
AS qry
FROM
db_attribute
WHERE
class_name IN (select class_name from db_class
where class_type='CLASS' and is_system_class='NO')
ORDER BY class_name
ASC, attr_name ASC
What we obtain is a query for each column in the database
that might contain our information:
So far so good! But we can do better than copy/paste/execute,
for each SELECT returned!
Final piece – how to execute all these in a "better" way…? Let’s
see – there are a couple of options:
- Use the PREPARE & EXECUTE CUBRID functions. But in our case, this will not work, because PREPARE does not support multiple SQL statements.
- Save the results in an external file, eliminate the surrounding ‘-s, copy back the commands to the SQL prompt and wait for the execution to complete… Simple, isn’t it? :)
- Find a way to have just one single query! How do we do that…? Not that complicated – using GROUP_CONCAT and UNION!
SELECT
GROUP_CONCAT(CONCAT('SELECT
TO_CHAR(`', attr_name,'`) FROM `',
class_name, '` WHERE `', attr_name, '`
LIKE ''%Estiarte%''') SEPARATOR ' UNION ')
FROM
db_attribute
WHERE
class_name IN (select
class_name from db_class where class_type='CLASS' and is_system_class='NO')
ORDER BY class_name ASC, attr_name ASC
(Please note that we
did some other things as well, like surrounding the column and the tables’
names with `, to prevent trouble when we are dealing with CUBRID reserved
words.)
We will obtain a single SELECT we can execute in one step… …and
that’s it – Estiarte was found!
Final thoughts – all these can be further optimized, and
that is exactly what the James Bond would most probably do in a next similar
task:
|
That’s it – James Bond is happy for now – he completed
the mission and he can move on, to the next one.
Now, can you find a better way to solve this task…?
In the meantime, see
you next time – Bye-bye! :)
/Dee
P.S. Hey, did you figured out what or who is “Manuel Estiarte”…?
:D
