Writing your Own ADA Reports

• Introducing the ADA Analyzer
• Prerequisites
• The Input.xml File
• The Query.xml File
• The Output.xml File
• Writing Report Files
• Making sure stuff works: Report Lint
• The ADA Database Schema
• An Example Report

Introducing the ADA Analyzer

The ADA Analyzer is a report generator which lives right on your smartphone, so that you can analyze fencer performances without using a laptop or other platform. It lives under the <Report> button in the first screen of the ADA application. ADA ships with several pre-written reports, but you can write your own reports and run them on your phone as well. This essay tells how to create and edit ADA reports on your own.

ADA reports consist of three control files, grouped together in a directory named for the report on your phone's SD card under /org/tomshiro/ada/reports. The Input.xml file creates the screen where you choose fencer names and other parameters for your database query. The Query.xml file contains the query which will run against your database. The Output.xml file contains the labels and headers displayed with the results of your query. The org/tomshiro/ada/database/ada.db file contains the information you have collected about your fencers. If you remove this file, ADA will create a fresh one when you re-run it on your phone.

Prerequisites

To write a report, you will need access to a laptop or desktop computer. You'll also need:

• An editor capable of producing "flat ASCII" files (not a 'word processor')
• A tool which can open and query sqlite3 databases (I use sqlite shell)
• An open USB port to read and write to your cell phone's SD card
• A python interpreter (version 2.7 or better) if you wish to use the report lint program (highly recommended)

Skills you will Need

To succeed at writing ADA reports, you'll need some basic skills. I am assuming that you can write SQL queries. A little experience with XML is handy, and you'll need some familiarity with command-line tools. This application was developed exclusively on Linux, so some commands may differ in detail on other operating systems from the specific examples given here.

The Input.xml file

The Input.xml file specifies the screen you will use to enter parameters for your query to the ada.db database. Its root element is the Screen element; it can contain only one of these. A Screen element consists of some number of Label and Inputfield elements.

Label elements

Label elements put text on the report input screen. They contain a type and a text element. The Header element changes the text in the header line of the parameter entry screen:

<Label type="Header" text="Everything">

The InputField Element

The InputField element specifies a parameter which you can type in to your query. Each InputField element must contain a type attribute.

InputField types

InputFields can have the following types:
• Fencer Name of a fencer, from a picklist
• Action Name of an action, from a picklist
• Integer An integer. Use the optional limit attribute to specify the largest number you can enter. That value defaults to 99.
• Boolean A true/false value
• Interval An interval (from 1 second to (100 years - 1 second)) The required maxMeasure attribute specifies the largest measure to be displayed. It can be Seconds,Minutes,Days,Weeks,Months, or Years.
• Text Free-form text. The required quotes attribute determines if the text will be surrounded by single-quotes ('') in the query. The optional default attribute will place a default value into this field.
• Id The id of a record. The required table attribute specifies what ids will be input.

InputField Attributes

Each InputField also contains the following attributes:
• label (required). A label displayed for this input field.
• seq (required). A sequence number for this input field.
• position (optional). Can be either above or anything else. If it says above, then the label text is placed above the input field. Otherwise it is to the left of the input field on the same line.
• height (optional)The height in scaled pixels of the input box (label + input field). Defaults to 75 scaled pixels.
• name (required). The name of the input field. This is how you refer to the field in the other xml files which make up a report.

Example Input.xml File

<Screen>
<Label type="Header" text="Everything"/>

<InputField type="Fencer" label="Fencer Name" seq="1" name="A_Fencer_name" />
<InputField type="Action" label="Action Type" seq="2" name="An_Action_type" />
<InputField type="Integer" label="Number" seq="3" name="A_Number" />
<InputField type="Boolean" label="Boolean" seq="4" name="A_Boolean"/>
<InputField type="Interval" label="Interval" seq="5" position="above" height="160" name="An_Interval" maxMeasure="Months"/>
<InputField type="Text" label="Text" quotes="true" default="My Dog Sex" seq="6" name="A_text_field" />
<InputField type="Id" label="An_ID" table="Bout" seq="7" name="An_id_field"/>

</Screen>

The Query.xml file

The Query.xml file holds your report query and its parameters. Its root element is QueryInfo. It contains one Query element and some number of Param elements. Each Param element corresponds to a single InputField element in the Input.xml file. You can have more than one Param element referring to the same InputField element.

Query Elements

A Query element consists of the SQL query you wish to pass to the database, with a "?" for each parameter passed to it. The Query.xml file can have only one query element.

Param Elements

Each Param element has two attributes. The Seq element is the sequence of the parameter in the Query.xml file. The ref element is the name of the InputField which contains the parameter text.

An Example Query.xml File

<QueryInfo>
<Query>
select
?,
?,
?,
?,
?,
?,
?
from
   Action_category
</Query>
<Param seq="1" ref="A_Fencer_name"/>
<Param seq="2" ref="An_Action_type"/>
<Param seq="3" ref="A_Number"/>
<Param seq="4" ref="A_Boolean"/>
<Param seq="5" ref="An_Interval" />
<Param seq="6" ref="A_text_field" />
<Param seq="7" ref="An_id_field"/>
</QueryInfo>

The Output.xml File

The Output.xml file controls how the results of your query are displayed. Its root element is Screen. It contains a series of Label elements. Each Label element must contain a type attribute and a text attribute. It can also contain param elements.

Label Types

Labels can contain the following type attributes:

• Header types set the label of the screen.
• SubHeader types are printed on the screen
• Infoheader types are printed just above the actual information from the query

Label Params

Labels use printf(1) to incorporate parameter information into what is printed on the cell phone screen. The Param element has three required attributes:

• seq is the parameter sequence
• otype is the type of the parameter
• ref is the name of the parameter, as assigned in Input.xml
The otype attrib can be one of the following values:
• String: A string (printf("%s",value))
• Integer: An integer (printf("%d",value))
• Float: A float (printf("%f",value))
• Double: A double (printf("%ld",value))
• Long: A Long (printf("%ld",value))

This method of specifying input parameters gives you maximum flexibility in how you format your headers. You can, for example, print out only the first 10 characters of your fencer name, as 'text="Fencer %.10s"'. The output area, of course, scrolls both horizontally and vertically.

Example Output.xml File

<Screen>

<Label type="Header" text="Everything"/>

<Label type="SubHeader" text = "Everything for %s number %d">
    <Param seq="1" otype="String" ref="A_Fencer_name"/>
    <Param seq="2" otype="Integer" ref="A_Number"/>
</Label>

<Label type="InfoHeader" text="A_Fencer_name|An_Action_type|A_Number|A_Boolean|An_Interval|A_text_field|An_Id_Field"/>

</Screen>

Writing Report Files

To write a report, start with your query. Copy the ada.db file from your phone to your PC, then use sqlite3 or some other tool to open it, write, and debug a working query. Once you have this query, write the Query.xml file, creating a Param stanza for each parameter you pass to your query (e.g. "where Fencer = 'Donnie Darko'"). Substitute the '?' character for each parameter in your query.

Now you are ready to write the Input.xml file, with one InputField stanza for each parameter in Query.xml, named for the ref attribute in the parameters you isolated from your original query.

Finally, write headers and parameters into your Output.xml file.

Putting your report on your Phone

After you have written the three files which make up your new report (and checked them with the linter as needed), you are ready to put the new report on your phone. Make a subdirectory of org/tomshiro/ada/reports on your phone's SD card named for your new report (e.g. "Average_Bout_Time_by_Fencer" ). Copy your Input.xml, Query.xml, and Output.xml files to that directory. The report should show up in the report menu under the <Report> button in the ADA app. When you choose it, you should see the input screen for the parameters specified in your Input.xml file. After you fill out the input form and press the submit button, you should see the results of your query with your parameters substituted.

Making sure stuff works: Report Lint

The Report Lint program checks for simple errors in ADA reports. Since these files are relatively static on your phone, it doesn't do much validity checking on them. If the report files contain errors, ADA tends to simply abend without even providing a clear error. This makes it difficult to develop on. The report linter is designed to give specific information on why an ADA report can't run.

The report linter requires Python 2.6 or better to run. It takes two flags:

• -r specifies the directory containing the three report files (Input.xml,Output.xml,Query.xml)
• -v increases verbosity. When passed, the linter will output lists of the input names, query names, and output names it found.

The linter is called RLint.py. You can invoke it with the python interpreter:

python RLint.py -v -r good_report

Obtain RLint as a tarball from this site. Install it by extracting the tarball to a directory in your $PATH.

The ADA Database Schema

ADA's database schema is as follows:

-- SQLite database design for ADA
-- Double-dash comments work only on a line of their own.
-- Copyright(c) Charles Shapiro July 2013
--    This program is free software: you can redistribute it and/or modify
--    it under the terms of the GNU General Public License as published by
--    the Free Software Foundation, either version 3 of the License, or
--    (at your option) any later version.
--
--    This program is distributed in the hope that it will be useful,
--    but WITHOUT ANY WARRANTY; without even the implied warranty of
--    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--    GNU General Public License for more details.
--
--    You should have received a copy of the GNU General Public License
--    along with this program.  If not, see .

PRAGMA foreign_keys = ON;

create table Fencer (
       _id integer primary key,
       fencername text unique
);

-- seq: order in drop-down list
-- actionname: simple attack, compound attack, parry-riposte, et cetera
-- action_categoryfk: attack,defend, both

create table Action_type (
  _id integer primary key,
  seq integer not null, 
  Action_categoryfk integer references Action_category(_id),
  actionname text 
);


create table Action_category
(
   _id integer primary key,
   category text
);  

-- These lines should go when we have an action editor.

insert into Action_type(seq,actionname) values(1,'Simple Attack');
insert into Action_type(seq,actionname) values(2,'Compound Attack');
insert into Action_type(seq,actionname) values(3,'Fleche');
insert into Action_type(seq,actionname) values(4,'Parry-Riposte');
insert into Action_type(seq,actionname) values(5,'Counterattack 1');
insert into Action_type(seq,actionname) values(6,'Counterattack 2');
insert into Action_type(seq,actionname) values(7,'Counterattack 3');
insert into Action_type(seq,actionname) values(8,'Retreat');   
insert into Action_type(seq,actionname) values(9,'Stand');
insert into Action_type(seq,actionname) values(10,'Parry');

insert into Action_category(category) values('Defend');
insert into Action_category(category) values('Attack');
insert into Action_category(category) values('Both');

-- These lines must be this way because of my idiosyncratic database
-- init code

update Action_type set Action_categoryfk = (select _id from Action_category where category='Defend') where seq in (5,6,7,8,9,10);

update Action_type set Action_categoryfk = (select _id from Action_category where category='Attack') where seq in (1,2,3);

update Action_type set Action_categoryfk = (select _id from Action_category where category='Both') where seq=4;

-- Test data should go away in distributed app

insert into Fencer(fencername) values ('Alfred P Neumann');
insert into Fencer(fencername) values ('Harpo Marx');
insert into Fencer(fencername) values ('Gerald J Slurry');


create table Bout (
   _id integer primary key,
   lfencerfk integer references Fencer(_id),
   rfencerfk integer references Fencer(_id),
   start_time datetime,
   end_time datetime
);

create table Touch (
   _id integer primary key,
   boutfk integer references Bout(_id),
   lactionfk integer references Action_type(_id),
   ractionfk integer references Action_type(_id),
   left_scored boolean,
   right_scored boolean,
   touch_sequence integer,
   committed_time datetime
);

Sample Debug Report

This report contains a field of every possible type in its "Input.xml" file and the simplest possible query which reports every value you put in in its "Query.xml" and "Output.xml" files. You can use it to verify that everything's working on your copy of ADA, and as a basis for writing the reports you need.