Assume you are given a rdbms schema you are not familiar with and you are wondering where a piece of information is stored. To me, this happens every now and then. A few days ago, a customer asked me to create a report based on data in his ERP database. Unfortunately it turned out that the schema was hosted in MS SQL Server, fairly big (roughly 100 tables with 30-40 columns on average), and looked meaningless to me (table- and column names). I remembered that ages ago, a colleague mentioned that he had written a tool addressing this kind of developer/admin problem. I never got to see it. Nevertheless, my current requirement made me revisit the idea. I really tried hard finding something ready to download and run – and failed – did not even find something close.
Quick, but not dirty !
To come up with something quickly, I picked Groovy. All of a sudden, an ugly reverse engineering task showed some beautiful aspects. I don’t want to praise the details of Groovy again. It just proved to be AWESOME in this case. Special thanks go to @mrhaki for his inspiring and practical posts. There is a link to the source at the bottom if you are interested. A few final remarks before you blow up your system:
$ ./DBBFSearch.groovy
error: Missing required options: v, U
usage: DBBFSearch -h -u userid -p password -v value -U url -d driver -j
jar -f function -m -q -s
-d,--driver <driver> Driver class
-f,--function <function> Column function
-h,--help help
-j,--jar <jar> Jar file containing driver
-m,--matches Show row matches, default is count only
-p,--password Password
-q,--query Show SQL query
-s,--substring Substring search
-U,--url <url> Database URL
-u,--userid <userid> Userid
-v,--value <value> Value to search for
Example:
DBBFSearch -v findme \
-U 'jdbc:mysql://localhost/db?user=user&password=pass' \
-j mysql:mysql-connector-java:5.1.6 -s -f lower
$
What I personally like most is the fact that you can execute it right away.
Here is a quick demo using the customers ERP my Alfresco schema searching for admin.
$ groovy https://raw.github.com/gist/2170917/a4e2af6cee3022b34fe5ec536d2c2b9ed9891c2a/DBBFSearch.groovy \ -v admin -U 'jdbc:mysql://localhost/alfresco?user=alfresco&password=alfresco' \ -j mysql:mysql-connector-java:5.1.6 \ -f lower | grep '\] \[' alf_authority [1] [authority:1] alf_child_assoc [2] [qname_localname:5] alf_node [6] [audit_creator:16, audit_modifier:16] alf_node_properties [1] [string_value:3] $
The first line of output says the script looked at one column in the table alf_authority and found one row authority matching admin. The other lines have analogous meanings.
Just copying and pasting this commandline should work if you happen to have Groovy (>= 1.8.3 mostly because of the remote script execution) and the “usual developer alfresco database” – Trust me ! ;)
PS: With mysql I was sometimes executing things like
$ mysqldump --skip-extended-insert --user=alfresco \ --password=alfresco alfresco | \ grep www.alfresco.org >/dev/null && echo "strike" strike $
Sure, this approach has limitations. But sometimes it helps you getting at least basic information.
PPS: The Alfresco entity relationship diagram from the older post may be helpful if you really try digging in the Alfresco schema.
References: