On 12/13/2012 07:30 PM, Raymond Mancy wrote:
If we are stuck with lugging SQL around, why not try and do something
more
useful and user friendly with it. Why not create an API to our SQL metrics.
So if you knew exactly what you were looking for you could do something like:
wget
http://beaker.example.com/metric/jobs/user_per_day -O jobs_per_user_per_day.sql
Or if we wanted to we could even allow something like the following:
wget
http://beaker.example.com/metrics/systems/usages?from=2011%2001%2010&... -O
systems_usages_from_2011-01-10_rmancy.sql
If the person did not know what was available to them, they could go to a page on our
website
that has links to all the metrics we make available as SQL, so they can see what is
available to them (as well as see the
SQL in their browser)
This I think would also make testing nicer. You could do something like the following:
sql_text = text(metrics.Jobs.per_user_per_day())
session.execute(sql_text)
It also means that we don't have to keep potentially many several .sql files
floating around in our repo. I think it would be far neater to contain
the SQL in our source code, and in the long term would make it a more useful
resource.
I like this suggestion, but the other aspect is that we need to know
when to highlight changes that will require users to update their
queries because the old ones are going to start giving incomplete or
incorrect answers.
So, what we would want to do is:
1. In the first release where we publish a given query for inclusion in
external reporting tools, we capture a copy as an SQL file in the *test
suite*.
2. We name these SQL files based on the metrics API they're meant to match
3. We have a test which checks each of the stored SQL files against the
"live" query retrieved from the API
4. Any time one of those test fails, we know we need to update the
stored SQL in the test suite and flag that public query in our release
notes as requiring an update
The overall idea is to support integration into SQL based business
intelligence tools without crippling our ability to update the database
schema.
Cheers,
Nick.
--
Nick Coghlan
Red Hat Infrastructure Engineering & Development, Brisbane
Python Applications Team Lead
Beaker Development Lead (
http://beaker-project.org/)
GlobalSync Development Lead (
http://pulpdist.readthedocs.org)