Data Selection

About
Prepared Query
Database
GUI Elements
	CheckBoxPanel
	ListSelection Panel
	BetweenPanel
	CriteriaBox
Files

About

This piece of application allows standard users, not knowing much about databases, to create complex queries against a RDBMS. Also it has to provide flexible queries with very few GUI-elements that can be used by simply clicking onto elements.

The result of such a generated query can be displayed at the screen and if the result fulfills the users requirements, the query can be exported as a csv-file, a xls-file or into a xml-file.

Queries can be stored for later use. The final query can be saved as a sql-file to be reused within the exporter or as a xml-file to be loaded into the GUI.
Dataselections are based onto prepared SQL-statements. The GUI is generated out of this SQL-statement, the embedded macros are interpreted and displayed within tabs. each macro creates its own tab. You can create tabs inside tabs to group functions. By a click onto the binocular the query is interpreted, there is a simple query-checker that eliminiates impossible constructs, e.g. if a AND directly follows a WHEN. This checker is far away from being something like a professional querychecker. This check is linebased, so it's best to have expressions like 'AND' and 'WHERE' onto a single line, dito while using brackets. There are two types of macros SQL-Commands and the 'include' – command to include files (#include fully-qualified-file-name).

Select the prepared query

The queries are prepared by a person having a certain knowledge in creating complex queries. The number of prepared queries is not limited, but it makes sense, not to overload the user with too many queries. The name of the query and its location is defined inside a control file, what then is interpreted by the application as a ComboBox. If this file does not exist, the applications throws an exception. This file is located at the position mentioned within the file admin.selgui.props what is located in the users homedirectory or at the position you defined with the environment-parameter 'admin.home', the defaultlocation is user.home/datadrill/resources.

To overcome this, I've created a little tool, that copies the needed files to your local harddisk from the internet( look here). With this tool, you can also create the demotables I've used to explain how it works. You probably first want to create your own database, because the tools just creates the tables within the actual database and probably messes your actual database up ;-). You find this tool under the menu 'Data operations -> Data Selection -> Create Demo environment'.

To explain the mechanism of this tool, I'm using this SQL-statement containing macros. The goal ist to select all garages in Zurich, Vienna and Dortmund and display the name of the garage, name and directphone of sales and techies:

To go like this, we create a basic query as this:

select distinct
#checkbox(Attributes;employee-list.display)
from   garage, person, function, employe
where  employe.garage_id  = garage.id and
       employe.person_id  = person.id and
       employe.function_id= function.id
       and
#execute(City;IN;garage.city in;select distinct city from garage)
       and
#criteriabox(Function;function.id in;employee-list.function)
order by garage.id

Our database looks as follows:

GUI-elements

CheckBoxPanel

Format: #checkbox(Unique-tab-Title;filename)

The CheckBoxPanel is used to select single elements. In this case it is lets the user select the Attributes to display within the result query.

To create such a panel, you do something like this:

select distinct #checkbox(Attributes;employee-list.display) from garage, person, function, employe

Where the macro #checkbox(Attributes;employee-list.display) must be on its own line and means:

Attributes: is the unique tab-title
employee-list.display: is the name of the file user.home/datadrill/resources/employee-list.display

the file employee-list.display looks as follows:

afs@kollbrunn:~/datadrill/resources> more employee-list.display

garage.name;Name of the Garage;Garage
garage.street;Street of the Garage;Street
garage.zip;Postalcode of the Garage;ZIP
garage.city;City of the garage;City
garage.email;E-Mail of the Garage;E-Mail
garage.phone;Phone of the Garage;Phone
person.firstname;Firstname of Employee;Firstname
person.lastname;Lastname of Employee;Lastname
person.phone;Employee direct Phone;Direct
function.function;Function;Function

Where each line contains three (3) items:

the columname
the text displayed within the gui
the title displayed within the SQL-resultset

According the selection done, the SQL-Statement will then look as this:

select distinct
garage.name "Garage ",
garage.city "City ",
garage.phone "Phone ",
person.firstname "Firstname ",
person.lastname "Lastname ",
person.phone "Direct "
from   garage, person, function, employe

ListSelectionPanel

Format: #execute(Unique-Tab-Title;Type(IN|SIMPLE);(Table.attribute IN|Table.attribute);SQL-Query)
where IN     generates an IN-SQL-Statement (attribute IN ('A','B'...) )
and   SIMPLE only puts spaces in between two selected elements

The ListSelectionPanel lets the user select elements from a list and adds them to the query. The content of the list is generated out of a SQL-query by itself.

In this case, our statement looks as this

#execute(City;IN;garage.city in;select distinct city from garage)

Where the elements are as follows:

unique tab-title
the mode to use (IN lets the interpreter create a SQL-statement with IN)
the table-column (garage.city IN creates the correct sql-statement, so do not forget the keyword IN)
the SQL-statement used, to create the left part of the window

this selection will create such a SQL-fragment:

garage.city in ('Zuerich' , 'Vienna' , 'Dortmund')

BetweenPanel

Format: #between(Unique-tabtitle;table.attribut;number of lines;or|and;text|zahl)

While using the betweenpanel, the user has the possibility to enter values as letters or numbers.

This is from the second example in our demo-DB. We use this to define the mileage range we'd like to have. If we enter between 5000 and 10000 our generated query-fragment would look like this:

( car.mileage BETWEEN 5000 AND 10000 )

as our macro is this:

#between(Mileage;car.mileage;1;and;zahl)

where the values mean:

Unique Tabtitle
table-attribute used
number of lines to generate at the beginning
mode (and or or) to generate the between-SQL-fragment
select 'text' if the attribute contains text and 'zahl' if the content is numbers

CriteriaBox

Format: #criteriabox(unique-tabtitle,tableattrbiut in;filename)

The criteriabox allows to select elements while checking them.

To create such a panel, we add the criteriabox-macro to our sql-statement:

#criteriabox(Function;function.id in;employee-list.function)

where the values are:

unique-tabtitle
table-attribute
filename used to create the items

the file employee-list.function looks as follows:

afs@kollbrunn:~/datadrill/resources> more employee-list.function Sales;1 Marketing;2 CEO;3 Technic;4

Where each line contains two (2) items:

the text displayed onto the screen
the value returned into the sql-statement (make sure to add the ' if the items are in text-format)

According the selection done, the SQL-Statement will then look as this:

function.id in (1,4)

Files:

I've created the demo-DB with this script, there is another file with the DB-content

Get the demo-files Unix-Style, Windows-Style

More examples (a little bit more complex) Unix-Style, Windows-Style