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
$ 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  [authority:1] alf_child_assoc  [qname_localname:5] alf_node  [audit_creator:16, audit_modifier:16] alf_node_properties  [string_value:3] $
The first line of output says the script looked at one column in the table
alf_authority and found one row
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.