pb_query -> SQL mapping

[Ethan Mallove]

I'm unclear about how exactly the pb_query.dtd elements map to the
underlying SQL clauses. It looks like <filter> does all the numerical
and string comparisons, but about what about things like aggregate
functions, date arithmetic, string functions, etc?

-Ethan

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Joachim Worringen-4]

Ethan Mallove wrote:
> I'm unclear about how exactly the pb_query.dtd elements map to the
> underlying SQL clauses. It looks like <filter> does all the numerical
> and string comparisons, but about what about things like aggregate
> functions, date arithmetic, string functions, etc?

SQL aggregate funktions are uses within perfbase operators (like max,
min, avg, ... etc), and other perfbase operators use other SQL clauses.

I don't think I understand what exactly you would like to know.

  Joachim

--
Joachim Worringen, Software Architect, Dolphin Interconnect Solutions
phone ++49/(0)228/324 08 17 - http://www.dolphinics.com

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ethan Mallove]

Joachim Worringen wrote On 07/24/06 04:22,:

> Ethan Mallove wrote:
>
>>I'm unclear about how exactly the pb_query.dtd elements map to the
>>underlying SQL clauses. It looks like <filter> does all the numerical
>>and string comparisons, but about what about things like aggregate
>>functions, date arithmetic, string functions, etc?
>
>
> SQL aggregate funktions are uses within perfbase operators (like max,
> min, avg, ... etc), and other perfbase operators use other SQL clauses.
>

Okay, so instead of "GROUP BY xyz" we set <combiner> to xyz?


> I don't think I understand what exactly you would like to know.
>

For date arithmetic, what would the filter look like for e.g., show only
yesterday's test results?

---

String functions - I was wondering if there's a way to trim or alter the result
data in postgres before it's returned.

---

What would a regexp look like in a perfbase query? E.g.,

SELECT abc FROM foo WHERE xyz ~ 'regexp';

---

Sorry to ask four questions in one email :)


>   Joachim
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Joachim Worringen-4]

Ethan Mallove wrote:
> Okay, so instead of "GROUP BY xyz" we set <combiner> to xyz?

Sort of, but not exactly. A combiner joins multiple data streams into a
single one, and can do this in different ways. See test/combiner and
test/combiner2.

It is not the intention of perfbase to offer a 1:1 mapping - if you need
/want to use SQL, you can do so...

>> I don't think I understand what exactly you would like to know.
>>
>
> For date arithmetic, what would the filter look like for e.g., show only
> yesterday's test results?

You will need to limit the runs that are to be queried with the <run>
element, like:
<run id="runs.yesterday">
        <performed>
                .... (see pb_query.dtd)
        </performed>
</run>

and then refer to this element within your <source>:
<source ...>
        <input>runs.yesterday</input>
<source>

It is also possible to i.e. only use data of the latest run - see
test/slice for examples.

> String functions - I was wondering if there's a way to trim or alter the result
> data in postgres before it's returned.

String manipulation is not yet supported. It could be done via an operator.

> What would a regexp look like in a perfbase query? E.g.,
>
> SELECT abc FROM foo WHERE xyz ~ 'regexp';

This was not implemented until today... It was a simple extension to the
existing filters - now (with an update via svn) you can do

<filter>
        <regexp>^match.*this$</regexp>
</filter>

Hope this helps, Joachim

--
Joachim Worringen, Software Architect, Dolphin Interconnect Solutions
phone ++49/(0)228/324 08 17 - http://www.dolphinics.com

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ethan Mallove]

I'm querying perfbase with this:

---

<query id="MPI install status report">

  <experiment>test_run_correctness</experiment>

  <parameter id="p.date">
    <value>submit_timestamp</value>
    <filter><greaterequal>p.date</greaterequal></filter>
  </parameter>

  <source id="src.result">
    <input>p.date</input>
      <result>test_name</result>
      <result>test_pass</result>
      <result>os_name</result>
  </source>

  <operator id="test_name_count" type="count"> <input>test_name</input> </operator>
  <operator id="os_name_count" type="count"> <input>os_name</input> </operator>
  <operator id="test_pass_count" type="count"> <input>test_pass</input> </operator>

  <combiner id="counts">
    <input>test_name_count</input>
    <input>os_name_count</input>
    <input>test_pass_count</input>
  </combiner>

  <output target="raw_text">
    <input>src.result</input>
    <input>counts</input>
  </output>

</query>

---

I would like to see this:

    name    | os_name | result | count
------------+---------+--------+-------
 test_foo   | SunOS   |      1 |    13
 test_bar   | Linux   |      0 |     1


But I get :

$ cat count.xml | pb query --desc=-
#* ERROR: can not process query specification
    "query object with id '_count' is not defined."

What am I doing wrong?

-Ethan

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ethan Mallove]

Ethan Mallove wrote On 07/25/06 11:52,:

> I'm querying perfbase with this:
>
> ---
>
> <query id="MPI install status report">
>
>   <experiment>test_run_correctness</experiment>
>
>   <parameter id="p.date">
>     <value>submit_timestamp</value>
>     <filter><greaterequal>p.date</greaterequal></filter>
>   </parameter>
>
>   <source id="src.result">
>     <input>p.date</input>
>       <result>test_name</result>
>       <result>test_pass</result>
>       <result>os_name</result>
>   </source>
>
>   <operator id="test_name_count" type="count"> <input>test_name</input> </operator>
>   <operator id="os_name_count" type="count"> <input>os_name</input> </operator>
>   <operator id="test_pass_count" type="count"> <input>test_pass</input> </operator>
>
>   <combiner id="counts">
>     <input>test_name_count</input>
>     <input>os_name_count</input>
>     <input>test_pass_count</input>
>   </combiner>
>
>   <output target="raw_text">
>     <input>src.result</input>
>     <input>counts</input>
>   </output>
>
> </query>
>
> ---
>
> I would like to see this:
>
>     name    | os_name | result | count
> ------------+---------+--------+-------
>  test_foo   | SunOS   |      1 |    13
>  test_bar   | Linux   |      0 |     1
>
>
> But I get :
>
> $ cat count.xml | pb query --desc=-
> #* ERROR: can not process query specification
>     "query object with id '_count' is not defined."
>

Sorry, I meant to say:

#* ERROR: can not process query specification
    "query object with id 'test_name' is not defined."



> What am I doing wrong?
>
> -Ethan
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Joachim Worringen-4]

Ethan Mallove wrote:

> Ethan Mallove wrote On 07/25/06 11:52,:
>> I'm querying perfbase with this:
>>
>> ---
>>
>> <query id="MPI install status report">
>>
>>   <experiment>test_run_correctness</experiment>
>>
>>   <parameter id="p.date">
>>     <value>submit_timestamp</value>
>>     <filter><greaterequal>p.date</greaterequal></filter>
>>   </parameter>

The id is 'p.date', and the id of fixed value referenced in the filter
'p.date', too - I expect this will not work.

>>
>>   <source id="src.result">
>>     <input>p.date</input>
>>       <result>test_name</result>
>>       <result>test_pass</result>
>>       <result>os_name</result>
>>   </source>

I guess the test_name and os_name would better be parameters (as they
are not outcome of a run of the experiment, but are given conditions in
which the run is performed). This will be relevant if you want to i.e.
filter for the OS name. But maybe I don't know your experiement well enough.

>>   <operator id="test_name_count" type="count"> <input>test_name</input> </operator>
>>   <operator id="os_name_count" type="count"> <input>os_name</input> </operator>
>>   <operator id="test_pass_count" type="count"> <input>test_pass</input> </operator>

Well, there is no object in this query with the id 'test_name' -
'test_name' is the name of a result value! In this case, you will need
to specify one source objects for each result value that you want to count.

>>
>>   <combiner id="counts">
>>     <input>test_name_count</input>
>>     <input>os_name_count</input>
>>     <input>test_pass_count</input>
>>   </combiner>
>>
>>   <output target="raw_text">
>>     <input>src.result</input>
>>     <input>counts</input>
>>   </output>

I don't think you'll want to reference the source element, but only
reference the combiner element here.

>>
>> </query>
>>
>> ---
>>
>> I would like to see this:
>>
>>     name    | os_name | result | count
>> ------------+---------+--------+-------
>>  test_foo   | SunOS   |      1 |    13
>>  test_bar   | Linux   |      0 |     1

You'd probably better define test_name and os_name as parameters, and
then do a "blind sweeps" like this:

<parameter id="p.os">
    <value>os_name</value>
    <sweep>
    </sweep>
</parameter>

<source>
   ...
   <input>p.os</input>
</input>

This will generate the same source object for every distinct content of
os_name. See test/sweep.

> #* ERROR: can not process query specification
>     "query object with id 'test_name' is not defined."

The error message gives a hint, doesn't it?

Your experiment requirements look a little bit different from what I am
doing most of the time, but we should be able to make it work.

  cheers, Joachim

--
Joachim Worringen, Software Architect, Dolphin Interconnect Solutions
phone ++49/(0)228/324 08 17 - http://www.dolphinics.com

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...