[Zope] announce: zope dataminer (dmc) version 0.1 - "the bleeding lizard release"

Anthony Baxter anthony@interlink.com.au
Wed, 09 Jun 1999 00:45:11 +1000


following the interests of the whole 'release often' school of
development, I'm rather pleased to announce that version 0.1 of the 
zope dataminer is available for release, from 

http://www.interlink.com.au/anthony/dmc-0.1.tgz

features of this release:
Bugs. 
No documentation as yet, but it ships with a sample application which 
has a tutorial in it.

ok, ok, some info about it - cribbing from the post I sent out just before:

what is this?

The data miner came about because I hate writing one off SQL queries for
marketing people. It allows non-SQL-speakers to build up queries(*)
interactively, viewing that query as a HTML table, a CSV file (can
be read into Excel) or as a pretty graph (for the managers out there).
What's more, the query, once complete, can be bookmarked, or if it's 
a graph, included in an <img src=""> tag.
(*) once someone who speaks a small amount of SQL has set the dataminer up, 
of course.


here's some example text, but the easiest thing to do is to get it, 
unpack it, follow the instructions in the Readme.txt to set up the
sample one (shipped as a zope export.bbe file), and start playing.

The system builds up a URL of the datamine contraints (DMCs) to apply.
This might look like this:
http://burswood:9080/ekorp/datamine_tnt/cons_day/opge1999.05.25/cons_day/cons_item/opeqTIME%20USED/cons_refcode/data_minutes/showgraph

or indeed might just look like this:
http://blah/datamine/cons_day/data_minutes/showcsv

The latter is the easier one, so I'll talk about it first. 
cons_day and data_minutes are both DMCs. When applied in a chain like
this, they will produce SQL of:

SELECT
billing.date date, 
sum(billing.quantity) minutes
 FROM 
billing
 GROUP BY
billing.date

and then show the output as a CSV file. All well and good, and pretty
easy. Each DMC has configured into it what to insert in a select clause,
in the from clause, and in the group by clause. So for cons_day, it has
a select clause of 'billing.date date', select_from of 'billing', and 
group_by of 'billing.date'. 
data_minutes is select: sum(billing.quantity) minutes, select_from: billing,
and no group_by.
(yes it will insert join clauses if the DMCs span multiple tables)

Now, back to the first, more complex example (the one that's causing me
pain)
http://burswood:9080/ekorp/datamine_tnt/cons_day/opge1999.05.25/cons_day/cons_item/opeqTIME%20USED/cons_refcode/data_minutes/showgraph

Say we don't just want to get all the dates in the system, but want to only
get the ones greater than a certain number? A DMC has a __bobo_traverse__
function, so that a URL fragment of: cons_day/opge1999.05.25/ 
will cause the cons_day fragment to apply "op"eration of "ge" (greater or
equal) to 1999.05.25. Unfortunately in doing this, it modifies 'self',
so it writes something to the ZODB each time. What I need is a way to
say 'hey, don't pay any attention to me for a minute, I'm just fiddling
some stuff, but I don't want it to be persistent'.

So that example again, broken down a bit:
http://blah/datamine/       -- folder containing all this.
cons_day/opge1999.05.25/    -- constrain where date > 1999.05.25
cons_day/                   -- but also group by, and select date
cons_item/opeqTIME%20USED/  -- pick entries where the 'item' is 'TIME USED'
cons_refcode/ -- group by refcode
data_minutes/  -- select sum(quantity)
showgraph  -- display it as a graph

this produces SQL of:
SELECT
billing.date date,
accounts.account_group refcode,
sum(billing.quantity) minutes
 FROM 
billing, accounts
 WHERE 
billing.account = accounts.account
 AND
billing.date >= '1999.05.25' and
billing.item = 'TIME USED'
 GROUP BY
billing.date, accounts.account_group

and then gives me back an image of a graph with the x-axis of dates, y-axis
of 'minutes' and multiple lines, (one for each refcode) and a little legend
in a box on the left-hand-side. This is about a moderate-complexity query -
there's some here that span 5 tables and do all sorts of interesting joins.

anyway, it's way way way way way way too late at night, and I'm about to
fall asleep - which would be a bad thing to do in the car. Feedback
to me - although if there's any obvious 'doh!' mistakes, feel free to
send them to zope@zope.org as well - I won't be on email for 8-10 hours.

Anthony
--
Anthony Baxter     <anthony@interlink.com.au>
It's never too late to have a happy childhood.