university of applied sciences ulm · university of applied sciences ulm the undersigned certify...

University of Applied Sciences Ulm

Online Analytical Processing in a medical database

for brain tumour data

by

Sinja Käfer

A thesis submitted to the University of Applied Sciences Ulm

in partial fulfillment of the requirements for the degree of

Bachelor of Science

University of Applied Sciences Ulm

The undersigned certify that they have read, and recommend to the

University of Applied Sciences Ulm for acceptance, a thesis entitled Online

Analytical Processing in a medical database for brain tumour data

submitted by Sinja Käfer in partial fulfillment of the requirements for the

degree of Bachelor of Science.

Prof. Dr. Tibor Kesztyüs

(First Examiner)

Prof. Dr. Jörg Sander

(Second Examiner)

Date: August, 31st 2009

Acknowledgement

I would like to thank Prof. Dr. Jörg Sander for his very valuable

insight and ideas for my research and being available to discuss and

review my work and to Prof. Dr. Tibor I. Kesztyüs for being available

to support me from Germany. Prof. Dr. Albert Murtha of the Cross

Cancer Institute, the oncologist in the Brain Tumour Analysis Project

(BTAP) group, and Computing Science Prof. Dr. Russ Greiner have

provided ideas for my research and discussed and reviewed my

work.Thanks also to the BTAP members in general for their feedback

during meetings.

Abstract

This Bachelor thesis details the implementation of an analysis tool

with non-aggregate and aggregate function. These analyses are based

on the Brain Tumour database from the patients of the Cross Cancer

Institute. The project is undertaken as part of the Brain Tumour

Analysis Project and will support the section of analysis to facilitate

significant queries of the existing data in the database to accomplish a

better overview, knowledge and possibilities to enforce changes in the

brain tumour diseases.

Table of Contents

1. INTRODUCTION.......................................................................... 1

1.1. MOTIVATION .............................................................................. 2

1.2. PROBLEM DEFINITION AND SCOPE .................................................. 3

1.3. BRAIN ANATOMY AND BRAIN TUMOUR ............................................. 4

1.3.1. STRUCTURE AND FUNCTIONS OF THE BRAIN ......................................... 4

1.3.2. BRAIN TUMOUR.......................................................................... 5

1.4. DATABASE.................................................................................. 6

1.4.1. MYSQL................................................................................... 6

1.4.2. SQL....................................................................................... 7

1.4.3. AGGREGATE FUNCTIONS ............................................................... 7

1.5. RELATED WORK........................................................................... 9

1.5.1. OLAP..................................................................................... 9

1.5.2. QBE .....................................................................................11

2. MATERIAL/ METHODS .............................................................. 12

2.1. PIX4HEALTHCARE SYSTEM........................................................... 12

2.1.1. MAINTENANCE OF THE CLINICAL TRIAL DATA ......................................13

2.1.2. QUERYING OF THE CLINICAL TRIAL DATA...........................................15

2.2. ARCHITECTURE.......................................................................... 16

2.2.1. LAZARUS ................................................................................16

2.2.2. FREEPASCAL ............................................................................16

2.2.3. ZEOSLIB.................................................................................17

2.2.4. APPLICATION ...........................................................................17

2.3. SQL STATEMENTS...................................................................... 18

2.3.1. SELECT STATEMENT ...................................................................19

2.3.2. JOIN .....................................................................................19

2.3.3. WHERE – CLAUSE......................................................................20

2.3.4. GROUP BY – STATEMENT .............................................................20

2.3.5. HAVING – CLAUSE..................................................................... 20

2.3.6. ORDER BY............................................................................... 21

2.3.7. SUMMARY OF THE STATEMENTS...................................................... 21

3. RESULTS................................................................................... 22

3.1. THE DATABASE SYSTEM .............................................................. 22

3.1.1. DATABASE EXTENSION................................................................ 22

3.1.2. DATABASE MODEL ..................................................................... 22

3.2. QUERY TOOL ............................................................................ 24

3.2.1. SELECT FORM .......................................................................... 25

3.2.2. FROM STATEMENT ..................................................................... 28

3.2.3. WHERE FORM .......................................................................... 28

3.2.4. GROUPBY FORM ....................................................................... 30

3.2.5. HAVING FORM.......................................................................... 31

3.2.6. STORAGE, LOAD AND EXPORT OF THE QUERY ..................................... 32

3.2.7. SEARCH AND NEW QUERY ............................................................ 32

3.2.8. RESULT DISPLAY....................................................................... 33

3.2.9. HELP TOOL ............................................................................. 34

3.2.10. SHOW IMAGE FORM ................................................................. 35

3.3. EXPERIMENTAL RESULTS............................................................. 36

3.3.1. NON-AGGREGATE ANALYSIS ......................................................... 36

3.3.2. AGGREGATE ANALYSIS................................................................ 37

4. DISCUSSION ............................................................................ 41

4.1. CONCLUSION ............................................................................ 41

4.2. FUTURE WORK.......................................................................... 43

GLOSSARY ...................................................................................... 45

BIBLIOGRAPHY .............................................................................. 48

5. APPENDIX ................................................................................ 51

5.1. IMPLEMENTATION OF THE IMPORT TOOL......................................... 51

5.1.1. ASCII FILES TO IMPORT IMAGE DATA FOR EACH PATIENT: .......................51

5.1.2. CODING OF THE IMPORT TOOL .......................................................52

5.2. CODING OF THE QUERY TOOL ....................................................... 54

5.2.1. FUNCTION COMBINATION .............................................................54

5.2.2. QUERY ...................................................................................55

5.2.3. SELECT ..................................................................................61

5.2.4. WHERE ..................................................................................65

5.2.5. GROUPBY..............................................................................65

5.2.6. HAVING..................................................................................67

5.2.7. SHOW IMAGE ...........................................................................73

List of Tables

Table 1-1: Aggregate Function..............................................................8

Table 2-1: Overview of the Clinical Trial Data........................................ 14

Table 2-2: General SQL Statement ...................................................... 18

List of Figures

Figure 1-1: The Brain Anatomy ............................................................ 4

Figure 2-1: The Old Query Tool ...........................................................15

Figure 3-1: The Database Model ..........................................................23

Figure 3-2: Overview of the Query Tool ................................................24

Figure 3-3: The Select Form ...............................................................25

Figure 3-4: The Select Item List ..........................................................25

Figure 3-5: Aggregate Functions..........................................................26

Figure 3-6: Memo Field ......................................................................26

Figure 3-7: Memo Field on the main Form.............................................27

Figure 3-8: The predefined From Statement..........................................28

Figure 3-9: The Where Form...............................................................28

Figure 3-10: The Group By Form .........................................................30

Figure 3-11: The Having Form.............................................................31

Figure 3-12: The Result Table .............................................................33

Figure 3-13: The Help Tool .................................................................34

Figure 3-14: Show Image...................................................................35

Figure 3-15: The Non-aggregate Analysis .............................................36

Figure 3-16: Aggregate Analysis 1 .......................................................37





Figure 3-21: Diagram referring to Analysis 5 .........................................40

Introduction

Page 1 of 75

1. Introduction

Nowadays it develops from different reasons, a lot of diseases which are

much more diversified. Therefore research is necessary to achieve

constantly new knowledge to improve the treatments or to find out new

treatment approaches.

The influences are not only the increasing change of the environment but

also the progress in the ever changing world of technology and the hypothec

of our surroundings, which have a huge impact on the human body/being

[1]. Unfortunately they affect the biological system of the human body in a

deeply negative way.

Nevertheless the progress of the technology is also an enormous gain,

especially regarding the range of the medicine that is available. This also

allows constant improvement in research and due to these new possibilities

an improvement in the treatment for several diseases. In the case of brain

tumours, it is necessary to gain new knowledge to achieve reducing the side

effects and increasing survival times. Therefore analyses of data place an

important role in brain tumour research.

Introduction

Page 2 of 75

1.1. Motivation

The Brain Tumour Analysis Project is a collaboration between the Radiation

Oncology Department at Alberta's Cross Cancer Institute [Prof. Dr. Albert

Murtha] and the Department of Computing Science, and the Alberta

Ingenuity Centre for Machine Learning to apply machine learning [Prof. Dr.

Russ Greiner] to apply machine learning and computer vision techniques to

the analysis of brain tumour patient MRI data. The project's ultimate

purpose is to apply automatic tumour segmentation, tumour growth

prediction, and diagnosis and prognosis to improve the treatment of brain

tumours [2].

Due to these aspects the motivation for this thesis was founded. The goal

was to implement an analysing tool whereby users get the possibility to

analyze data about brain tumour patients. With this it should be achieved

that trial data can be displayed in a more useful view whereby conclusions

and relations can be detected. Additionally, the point of view also has to be

respected that the tool must be implemented with a graphical user-friendly

interface. It must be implemented in a way that it will be a support for

analysts and not more of an obstacle.

Currently the brain tumour text-based data are maintained in the

piX4Healthcare system, running on an application server. The MRI images

from the PACS system are anonymized to the corresponding brain tumour

text-based data. The MRI images are stored on a file server. The goal is to

apply the piX4Healthcare system (a hospital information system, developed

by Prof. Dr. T. I. Kesztyüs) to administrate also the MRI images and tools for

machine learning methods to analyse both data types.

Introduction

Page 3 of 75

1.2. Problem Definition and Scope

For many years, the Cross Cancer Institute has collected data of patients

with brain tumours. These data includes text data as well as image data.

These data records are necessary and very valuable for researching not only

to find out changes and similarities between the same or similar kind of

brain tumours, they are also important to improve and specialize the

treatment for each disease.

The problem was that the data already exist but firstly, the referring images

links for the data of a patient were not stored in the database. Secondly,

most of the alphanumeric data was already stored in a database but without

any knowledge of analyses languages, it is not very useful. The

alphanumeric data are stored in the piX4Healthcare system and the

belonging database management system has to be extended with the image

data.

The goal was on the one hand to combine the image data with the referring

alphanumeric data to a patient. On the other hand there has been developed

a mechanism to also give users without expert knowledge in analysing

languages the possibility to work with this data and support them by

creating sophisticated analysis. Therefore it has to be considered that the

analysis tool should be more or less implemented in a self explanatory

graphical user interface with the opportunity to build non-aggregate and

aggregate analysis. Due to the fact that the data is stored in different data

types not all aggregate functions would be reasonable for each column.

Therefore a supporting decision should be given that the user only has the

possibility of aggregate functions to avoid building meaningless analyses.

Introduction

Page 4 of 75

1.3. Brain Anatomy and Brain Tumour

The brain is a highly specialized organ that serves as the control centre for

functions of the body. It allows us to cope with our environment.

1.3.1. Structure and functions of the brain

The main parts of the brain are:

the cerebrum, made up of the right and left cerebral hemispheres,

the cerebellum,

the brain stem.

Figure 1-1: The Brain Anatomy

[3]

Cerebrum

This is the largest and the newest (evolutionarily) part of the brain and is

concerned with all higher mental functions such as thinking and memory. It

is made up of two halves or hemispheres. Each cerebral hemisphere is in

turn divided into four areas, known as the lobes: frontal, parietal, temporal

and occipital lobes [3].

Cerebellum

This is the back part of the brain and is involved in movement coordination

and balance. These activities are carried out subconsciously by this area of

the brain. It can be seen as the floating point unit of the brain if we may

compare it to a computer [4].

Introduction

Page 5 of 75

Brain stem

This is the lower part of the brain that controls the basic functions essential

to maintaining life. These functions include blood pressure, breathing, and

heart beat. It connects the cerebral hemispheres to the spinal cord.

1.3.2. Brain tumour

A tumour is an abnormal mass of tissue in which some cells grow and

multiply uncontrollably, apparently unregulated by the mechanisms that

control normal cells. This can be cancerous or noncancerous. A tumour can

grow within the skull and then it can interfere with normal brain activity [5].

A tumour can cause damage by invading and damaging nerves and healthy

brain tissue because around the tumour the brain tissue is swelling caused

by fluid in the brain tissue, which is called edema. The location of a brain

tumour influences the type of symptoms that occur. This is because different

functions are controlled by different parts of the brain [5].

“For an individual patient, the distribution of occult tumour cells is unlikely

to match precisely a uniform 2cm margin around the visible tumour mass

because tumour does not tend to grow in a homogeneous, spherical fashion.

Thus, the standard treatment regimen is likely to damage healthy, non-

cancerous brain tissue, with associated reduction in mental function. Based

on the experience of radio-oncologists, there is anecdotal evidence that

brain anatomy in various regions can speed or impede tumour growth in

different directions. It seems that the falx cerebri and tentorium cerebelli

present barriers to tumour growth. Many tumours are observed to grow

preferentially along white matter tracts” (J.Sander, T.I. Kesztyüs,

J.Levesque, A.Murtha, M.Brown, p.2) [6].

Introduction

Page 6 of 75

1.4. Database

The database is based on MySQL5 which is an open source relational

Database Management System (RDBMS). A Database Management System

(DBMS) is a software system that uses a standard method to facilitate the

creation, retrieving and running queries on data. In a relational model, data

is stored in the form of tables and the relationship among the data is also

stored in the form of tables. A foreign key is used to establish a link between

the data in two tables.

The database of the piX4Healthcare system already contains several tables

to store data of brain tumour patients. The tables contain indexes to make

the queries selecting specific column values more efficient, because the

tables are very large. For large tables, or for complex queries, indexing is

the most important technique for speeding up queries [7].

1.4.1. MySQL

MySQL is one of the most popular open source database. It is based on the

Structured Query Language (SQL) which is used for adding, removing and

modifying information in the database. The storage engine is based on

InnoDB. InnoDB, which is ACID compliant, is the most common

transactional storage engine for MySQL. It has an internal mechanism for

deadlock detection, and performs a quick and reliable crash recovery, should

a problem occur [8].

Introduction

Page 7 of 75

1.4.2. SQL

SQL is one of the most popular languages for adding, accessing and

managing content stored within a relational Database Management System

(DBMS). It is most noted for its quick processing, proven reliability,

flexibility and ease of use. This SELECT statement is known as a query.

The language has two basic types of SQL statements. One of them is called

Data Definition Language (DDL) that includes the statements Create, Alter

Drop. DDL is used to manage database objects like tables and columns.

The other type is called Data Manipulation Language (DML). This includes

the Insert, Update, Delete and the Select Statements. DML is used to

manage the data that resides in our tables and columns [9].

1.4.3. Aggregate Functions

The main difference between queries with and without aggregate functions

is that the non-aggregate queries process the rows one by one. That is,

each row is processed independently and put into the result as part of a

non-aggregate query. Aggregate queries do something completely different;

they take a table as a whole and construct new rows from it.

An important characteristic of aggregate functions is that the aggregate

expression, usually a column name, can also be a literal, a function or any

combination of chained or nested columns names, literals or functions.

Aggregate functions include sum(), count(), avg(), min(),max() as well as

some other statistical functions like std().

Sum() and avg() work with only numeric data types. Min() and max() work

with character, numeric, and date/time data types. On the other hand,

count() works with all data types.

All aggregate functions ignore the Null values except the Count() aggregate.

Furthermore, the count() function, unlike all other aggregate functions which

return nulls, will only return either a positive integer or zero.

Introduction

Page 8 of 75

Another important point is that non-aggregate and aggregate expressions

cannot be mixed in the SELECT clause without a GROUP BY clause. The

typical SELECT clause must contain either all non-aggregate expressions or

all aggregate expressions. To combine non-aggregate and aggregate results

the GROUP BY clause must be used [10].

AVG() Return the average value of the argument

BIT AND() Return bitwise and

BIT OR() Return bitwise or

BIT XOR() Return bitwise xor

COUNT (DISTINCT) Return the count of a number of different

values

COUNT() Return a count of the number of rows

returned

GROUP CONCAT() Return a concatenated string

MAX() Return the maximum value

MIN() Return the minimum value

STD() Return the population standard deviation

STDDEV_SAMP() Return the sample standard deviation

STDDEV() Return the population standard deviation

SUM() Return the sum

VAR_POP() Return the population standard variance

VAR_SAMP() Return the sample variance

VARIANCE() Return the population standard variance

Table 1-1: Aggregate Function

[10]

Introduction

Page 9 of 75

1.5. Related Work

To analyse data is in each sector, regardless if it is in the research or in the

business sector, an important task for improving either the treatments of

disease or sales if it is in business [11]. The huge amount of data has to be

organized in a way that significant conclusions can be found. Therefore there

is already a structure developed which is called OLAP. In general, for

analysing data, knowledge of analysis languages is not unimportant but

there has also has been developed some mechanisms to give users who do

not have deep knowledge of an analysis language the possibility to create

analysis statements. This is accomplished in a more graphical way for easier

understanding, called QBE (Query by Example) [12].

1.5.1. OLAP

The Online Analytical Processing (OLAP) belongs to the methods of analytical

information systems. It is related to hypothesis analysis methods. The

analyst must know before the real survey which queries he wants to run on

the OLAP System [13].

The OLAP systems get their data either from the operational dataset of the

company or from a data warehouse. The goal of an OLAP system is to

support complex analysis on large amounts of data [14]. The goal is through

a multidimensional view to this data to gain inside insight into the data in

order to support decisions. The structure that is used is called OLAP cube

which is designed by the operational database. Mostly this is built in a star

scheme with one fact and the respective dimensional tables.

Introduction

Page 10 of 75

Types of OLAP Systems

• ROLAP (relational OLAP)

o Access to a relational database

• MOLAP (multidimensional OLAP)

o Access to a multidimensional database

• HOLAP (hybrid OLAP)

o It is an intermediate form of both OLAP Systems

Each of them has its advantage as well as its disadvantage. For instance

MOLAP stores the operation figures persistently. This is a performance

advantage against other OLAP Systems. In comparison ROLAP is better in

scaling but the performance is not as good as in the MOLAP system. One

other advantage of using ROLAP is that it does not need a huge storage

space because the data will be queried in the existing database [15]. In

turn, HOLAP is a good trade-off between using ROLAP or MOLAP.

The time-consuming part of using an OLAP System is not the analysis of the

data but the building and maintenance of these cubes [11].

Nigel Pendse defined the OLAP concept in only 5 rules:

Pendse’s FASMI-Rules

Fast Analysis of Shared Multidimensional Information (FASMI) [13, 21]

Fast: Response time for queries is not to exceed 20 seconds

Analysis: Analysis should be intuitive and should allow arbitrary

calculations

Shared: Effective user access is in place and multi-user access is

possible

Multidimensional: Independent of the underlying database

structure, conceptually, a multi-dimensional view is

implemented

Information: All relevant information is to be provided to the user

Transparently

Introduction

Page 11 of 75

1.5.2. QBE

QBE (Query By Example) is a database query language for relational

databases and was devised by Moshé M. Zloof at IBM Research during the

mid 1970s, in parallel to the development of SQL [12].

It is the first graphical query language, using visual tables where the user

would enter commands, example elements and conditions. Many graphical

front-ends for databases use the ideas from QBE today.

QBE is a feature included with various database applications that provides a

user-friendly method of running database queries. Typically without QBE, a

user must write input commands using correct SQL (Structured Query

Language) syntax in which should be required that the user has SQL

knowledge. Because if the syntax is slightly incorrect the query may return

the wrong results or may not run at all [17].

The ‘Query By Example’ feature provides a simple interface for a user to

enter queries. Instead of writing an entire SQL command, the user can just

fill in blanks or select items to define the query he wants to perform. For

instance, Microsoft Access has a QBE interface known as "Query Design

View" that is completely graphical. [17]

The phpMyAdmin application is used with MySQL, offers a Web-based

interface where users can select a query operator and fill in blanks with

search terms. However QBE implementation is provided in a program, the

purpose is the same – to make it easier to run database queries and to

avoid the frustrations of SQL errors.

Material/ Methods

Page 12 of 75

2. Material/ Methods

2.1. piX4Healthcare system

The piX4Healthcare system is a Hospital Information System (HIS) which

was developed by Prof. Dr. Tibor Kesztyüs.

It is already in commercial use in a hospital and in some academic

institutions like at Alberta’s Cross Cancer Institute.

Beside the commercial side the goal is that the further development will be

in the open source field and to provide the system in medical information

technology training.

It was very important for the Cross Cancer Institute to provide a system to

maintain their huge amount of clinical trial data of brain tumour patients.

They use the system in a reduced version because they use it only for

research and therefore the patient data are anonymized.

It is important that the system satisfies all requirements for documentation

and maintain usability. As there are many different medical departments

with different requirements regarding what data is collected, there should be

the possibility to integrate into the system specific forms.

The template management, which is integrated in the piX4Healthcare

system, facilitates the work with several forms. The user can create these

forms and the administrator has to insert the forms into the system.

The creation of these forms can be achieved by the easy to use form

designer tool within the development environment Lazarus.

The data belonging to the forms are stored in pix4Healthcare database

tables. This accomplishes a quick access to the data.

The current form in which the brain tumour data is maintained is

called the CNS form.

Material/ Methods

Page 13 of 75

2.1.1. Maintenance of the Clinical Trial Data

Currently there are 130 columns which are stored in the database table of

the brain tumour data. The form is separated into seven register. Each

register represents a category with the corresponding fields as shown in

table 2-1.

Category Fields

Identification ACB Number, Patient Initials, Postal Code, Date of

Birth, Date of Death

Presentation Patient’s Height, Patient’s Weight, Handedness, KPS

(Karnofsky Performance Score), ECOG (Eastern

Cooperative Oncology Group), Estimated Duration of

Symptoms, Symptoms, Date of initial MRI, Scanning

Facility of MRI, Size of the Tumour, Location of the

Tumour, Enhancement, CT scan done, Date of initial

CT scan, Scanning Facility of the CT, Size of the

Tumour, Date of Presentation to Neurosurgery, Date

of initial Consultation

Diagnosis 1st Biopsy and 2nd Biopsy:

Date, Surgical Facility, Extend of Resection, Date

Pathologic Dx Reported, Final Diagnosis, ICD Code,

Grade of Diagnosis, 1P Loss of Heterozygosity, 19Q

Loss of Heterozygosity, MGMT Methylated,

Material/ Methods

Page 14 of 75

Initial Treatment RT (Radiation Therapy), RT Start Date, RT End Date,

Fraction Number, Total Dose, RT Type, Chemo,

Temozolomide (TMZ), Payment, TMZ Start Date, TMZ

End Date, Post.RT 5/28

no.cycles, Total Dose

Clinical Course Date of initial MRI Showing Growth, Scanning Facility

of Showing Growth, Date of Initial MRI Showing

Transformation, Scanning Facility of Showing

Transformation, Confirmation by Pathology

Treatment

Progression

Surgery at Progression:

Date of 2nd Biopsy, Surgical Facility, extend of

Resection, Date Pathologic Dx Reported, Final

Diagnosis, ICD Code, Grade of Diagnosis, 1P Loss of

Heterozygosity, 19Q Loss of Heterozygosity, MGMT

Methylated

RT at Progression:

RT Start Date, RT End Date, Fraction Number, Total

Dose, RT Type, Chemo at Progression, Temozolomide

(TMZ), Payment, TMZ Start Date, TMZ End Date,

Post.RT 5/28 no.cycles, Total Dose

Clinical Trials Study Group, Study Number, Study Name

Table 2-1: Overview of the Clinical Trial Data

Material/ Methods

Page 15 of 75

2.1.2. Querying of the Clinical Trial Data

In order to view the clinical trial data a query tool has been implemented.

With this tool, analysts are able to specify well formed row conditions and

display results in tabular format.

One row condition allows specifying one of the 130 fields of the brain tumour

data table followed by a comparison operator and a value field. It is possible

to add multiple row conditions, on multiple columns, to select the rows of

interest. The comparison operator selection box shows only reasonable

operators belonging to the selected field like =, <>,>, =>, <=. To support

the user in entering possible values, a Pop-up Menu shows only the allowed

values. Combining more than one row condition can be realized by adding a

logical operator like 'AND' and 'OR'. The user can set parentheses to join

conditions together to create complex statements. Furthermore it is also

possible to delete a row condition. To avoid invalid queries, exceptions will

be raised with the notification of the missing value, operator, or parenthesis.

Figure 2-1: The Old Query Tool

Material/ Methods

Page 16 of 75

2.2. Architecture

The piX4Healthcare system is developed in FreePascal/Lazarus. This is in

combination a similar development environment to Delphi, which is platform

independent.

The Zeos Database Object component library (ZeosLib) provides the

interface to the database. The database management system is based on

MySQL 5 Server with InnoDB as Storage Engine.

The whole system is accessible at the application server called Ellis and the

user can connect through a remote access tool called NX Client to the

application server.

2.2.1. Lazarus

Lazarus is a cross platform visual Integrated Development Environment

(IDE) for Rapid Application Development (RAD) which provides a Delphi

clone for Pascal and Object Pascal developers. Lazarus is bundled with Free

Pascal. It is based on Borland Delphi. The Lazarus Component Library (LCL)

is similar to Borland Delphi VCL.

Unlike Java, which is intended to be ‘write once, run anywhere’, Lazarus and

Free Pascal aim to be ‘write once, compile anywhere’. Because the same

compiler is available for most operating systems, there is no need for re-

coding to produce identical products for the different platforms. The IDE

offers a forms designer, a debugger, an object inspector and a user friendly

code editor [18].

2.2.2. FreePascal

Free Pascal (FPC for short) is a free, portable and open source compiler for

Pascal and Object Pascal. It is a 32- and 64-bit multi-architecture and multi-

operating system compiler that implements the Borland Pascal dialects

Turbo Pascal and Delphi as well as some Mac Pascal constructs.

Material/ Methods

Page 17 of 75

Free Pascal is a compiler that runs on Linux, Win32, OS/X and more. It is

designed to be able to understand and compile the Delphi syntax, which

adheres to the OOP (Object Oriented Programming) model [19].

.

2.2.3. ZeosLib

ZeosLib (Zeos Database Object component library) is an open-source tool

that provides the interface to the database. The Zeos Database Connectivity

Interface (ZDBC) is a low-level Application Programming Interface (API)

used by ZeosDBO to hide differences in native database client APIs and

provide a uniform interface for high-level component layers. Originally,

ZDBC was a port of JDBC 2.0 (Java Database Connectivity API) to Object

Pascal. Since then the API has been slightly extended but the main

functionality remains unchanged. The main purpose of ZDBC is to serve as

an intermediate layer in ZeosDBO. These layers are called Database

Abstraction Layers (DAL). However, it is also useful for application

programming as it provides extremely light and fast access to SQL

databases [20].

2.2.4. Application

The installation of the application server is a Debian Linux 4.0 system,

running the Database Management System (DBMS) MySQL5 and the

piX4Healthcare system accessible through a terminal server (NX

NoMachine). NoMachine NX makes it possible to transform any traditional

desktop computing environment into a centrally managed, globally

accessible, virtual desktop infrastructure.

The clients can run any supported operating systems for which the NX Client

software is available. These include MS-Windows (2000, XP, Vista), MacOS

(from Version 10.X), and Linux (RedHat, SuSe, Debian, Knoppix, Ubuntu).

Through the service of the terminal server, the work of the administrator is

simplified.

Material/ Methods

Page 18 of 75

2.3. SQL Statements

The SELECT statement can be expanded to make more complex queries

using clauses that modify what results are returned and how the results are

organized. Some examples of clauses are WHERE, GROUPBY and HAVING.

Furthermore, SQL also allows programmers to build queries that combine

results obtained from multiple tables which will be realized through joins

(linking tables based on common column values) or nested queries (queries

that can build upon the results of a secondary query). The aggregate

functions of SQL allow users to perform calculations on a set of records and

return a single value. For example, the aggregate function Count() will

return the number of data items returned by a single query [21].

The Select Statement

Keyword Description

SELECT Retrieves fields from one or more tables.

FROM Tables containing the fields.

WHERE Criteria to restrict the records returned.

GROUP

BY

Determines how the records should be grouped.

HAVING Used with GROUP BY to specify the criteria for the grouped records

ORDER

BY

Is used to sort the result-set by a specified column

Table 2-2: General SQL Statement

Material/ Methods

Page 19 of 75

2.3.1. Select Statement

SELECT allows the user to pick and choose which column values are

returned from the query. FROM allows the user to specify one or more tables

from the database. Thus the SELECT clause is used to retrieve row records

from a database by identifying what columns values are desired and what

tables are to be searched.

For example, the simplest query which can be used to retrieve all records

from a single table is realized through use of a wildcard character, the

asterisk “*”, within a SELECT clause.

However, more complex queries are usually required in order to obtain

interesting and meaningful results. In these instances, the query is usually

associated with more than one this is realized through joining tables [16].

2.3.2. Join

The JOIN keyword is used in a SQL statement to query data from two or

more tables based on a relationship between selected columns of these

tables. There are different possibilities to use a join, i.e. INNER JOIN returns

rows when there is at least one match in both tables. LEFT/ RIGHT OUTER

JOIN return all rows from the left/ right table, even if there are no matches

in the right/left table.

In many instances, tables in a database are often related to each other via

special column values known as keys. A column contains keys if each row of

the column has a unique value. A primary key is a column of keys where the

row value cannot be empty (or NULL) for that column, and that the row

value can be used to uniquely identify the rows of the table. The purpose is

to bind relational data together, across tables, without repeating all of the

data in every table [23].

Material/ Methods

Page 20 of 75

2.3.3. Where – Clause

The WHERE clause allows the user to specify conditions that must be met in

order for rows to be retrieved from database tables thus restricting the

number of rows displayed by the SELECT clause.

2.3.4. Group By – Statement

The GROUP BY clause is used to divide rows of a table into logical categories

and calculate aggregate statistical information for each category.

There are several important characteristics of the GROUP BY clause. First it

is placed after the WHERE clause and before the ORDER BY clause. Second,

grouping columns must be only column names or derived columns. Next, no

columns from the input table can appear in an aggregate query’s Select

statement unless they are also included in the GROUP BY clause. Finally, as

noted above, the GROUPBY clause is used in conjunction with aggregate

functions to group the result set by one or more columns. However, in this

instance, it only returns one row for each value type [22].

2.3.5. Having – Clause

The HAVING clause sets conditions on the GROUP BY clause similar to the

way that the WHERE clause interacts with the SELECT clause.

The HAVING Clause is located between the GROUP BY clause and the ORDER

BY clause. It limits the number of groups displayed by the GROUP BY clause.

While the WHERE search conditions are applied before grouping occurs, the

HAVING search condition is applied after. Furthermore, the HAVING syntax

is similar to the WHERE syntax except that the HAVING clause can contain

aggregate functions and must specify a condition that applies to groups.

Material/ Methods

Page 21 of 75

2.3.6. Order by

The ORDER BY keyword is used to sort the data in a record set. It sorts

there records in ascending order by default and it is placed at the end of the

SQL statement.

2.3.7. Summary of the Statements

The WHERE clause filters the rows that result from the operations specified

in the FROM clause and JOIN keyword. The GROUP BY clause groups the

output of the WHERE clause. The HAVING clause filters rows from the

grouped result.

Results

Page 22 of 75

3. Results

The implementation is separated into two parts, the Database System and

the Query program.

3.1. The Database System

3.1.1. Database Extension

Prior to the modifications outlined in this document, the database of the

pix4Healthcare system could not represent the image data. Thus part of the

work has been to extend the database with tables to save information,

segmentation data, and links to the MRI Image data and to add this new

information into the system. The database extensions include tables to store

the links to the MRI images belonging to the modalities for each study, the

ROIs and the corresponding calculated voxel-sets and aggregate

information.

In order to more easily incorporate the huge amounts of new data, it was

necessary to implement an automatic import mechanism. A tool was created

that takes generated ASCII files (which contain the data), processes the

data into a manageable format and inserts the results into the database (see

appendix 7.1., p. 46-48). All these operations were made to be part of an

automated process.

3.1.2. Database Model

In the extended database model, each patient can be part of several

studies. A study is defined by a description of the study and the by the date

it occurred. Each study contains several different types of images (T1, T1C,

T2 and Flair) taken from a patient. These image types are described in the

‘Image Type’ table. Each image type consists of a set of images which are in

turn made up of several slices. The slices are stored in the ‘Image’ table

along with a slice number, a description and a link to the image file for the

slice.

Results

Page 23 of 75

In addition to the image links, multiple voxel-sets describing the ROI are

also stored to each study of a patient. The ROI table contains the

descriptions of the Regions of Interest.

The voxel-set of these ROI are stored as x, y and z coordinates. A patient

study can also have specific aggregation information associated with it, such

as the Center of Mass, Minimum Bounding Box, and Minimum Bounding

Ellipsoid, which are determined by a description and a value.

The brain tumour text data is stored in a table called formdata_btap_cns

related to the care_person table. It stores personal information such as

gender and name. The foreign keys are indexed in all tables.

The following figure shows the database model.

Figure 3-1: The Database Model

The ROIs, which are currently stored in the ROI Table, are defined as

followed:

• Segmentation of the Tumour

• Edema

The aggregate information belonging to the ROIs are defined as followed:

• Center of Mass - (x, y, z)

• Minimum Bounding Box - (x1, y1, z1), (x2, y2, z2), (x3, y3, z3)

• Minimum Bounding Ellipsoid - Center=(x0, y0, z0), axis1=(x1, y1, z1),

axis2=(x2, y2, z2), axis3=(x3, y3, z3)

The coordinates are stored as string values due to the difference in the

quantity of the coordinates.

Results

Page 24 of 75

3.2. Query Tool

The old Constraint Query Tool can handle unlimited queries where the user

has the possibility to specify row conditions of the Cross Cancer trial in the

WHERE clause.

Now the query tool is extended to the query support for brain tumour

alphanumeric data with aggregate functions. First of all the interface had to

be redesigned.

Now the main form is built in the same way as an SQL statement must be

created. It gives the possibility to create either non-aggregate statements

which include only the SELECT, FROM and the WHERE clauses as well as a

GROUP BY clause, and the HAVING clause. The SELECT clause has the

asterix [*] as the default value in case there is no requirement to select

columns of interest (shown in figure 3-2).

Figure 3-2: Overview of the Query Tool

Results

Page 25 of 75

3.2.1. Select Form

After choosing the “Column Expression”-button on the main form, if the user

wants to select special columns, the SELECT form appears.

Figure 3-3: The Select Form

In this form the possibility is given to choose each column which is stored in

the database. The item list will be created at runtime (see appendix 5.2.3,

p.61). With this option, the item list is always up to date, and independent

of changes in the database (shown in figure 3-4). Furthermore, because

there are currently about 130 items in database, a reduce-search procedure

is implemented so that the user has the opportunity to type in only single

characters and only those items will be displayed which start with these

characters (see appendix 7.2.3, p.61).

Figure 3-4: The Select Item List

Results

Page 26 of 75

For the non-aggregate SQL statement the user only has to select the

columns which she/he is interested in, and can ignore the possibility to do

aggregations (shown in figure 3-4).

Figure 3-5: Aggregate Functions

To add an aggregate function the user first has to activate the aggregate

possibility by selecting the check box after choosing a column of interest.

After this the aggregate combo box shows only the options with these

aggregate possibilities that make sense for this column of interest (shown in

figure 3-5 and appendix 5.2.3, p.62). If the user tries to select an aggregate

without choosing a column first, a reminder will appear that first a column

has to be chosen.

Figure 3-6: Memo Field

All columns, with or without aggregate function, will be displayed in the text

area in the order the user has added them (shown in figure 3-6). This field

is editable so the user has the freedom, on the one hand to create the

statement by choosing the given column names, or on the other hand, to

Results

Page 27 of 75

edit it directly if the user wants to create a more complex statement and is

familiar with SQL, for instance to create a nested query.

Figure 3-7: Memo Field on the main Form

If the SELECT statement is created and “Ok” is selected, the statement will

be loaded into the text area of the main form where now the statement is

also visible in the same way as the names of the columns are stored in the

database (shown in figure 7-7 and appendix 5.2.2, p.55).

The “Column- Expression”-button is after that replaced with the “Edit-”

button because this text area which shows the SELECT clause is read-only.

If there is the demand to edit something in the already created statement

the user can go back to the SELECT form with the “Edit-” button. The

SELECT form appears again with the identical content (see appendix 5.2.2,

p.56).

For changes there are the following options given: (1) clear the whole

statement, which sets it back to ‘*’ after confirming this with the “Ok”

button, (2) edit the current statement again with all the options which are

already described above, (3) “Cancel“ the current edit operation, which will

leave the SELECT form instantly, restoring the previous content.

On the right side on the top a help-button is provided, but this will be

explained in more detail later (also shown in figure 3-4, 3-5, 3-7).

Results

Page 28 of 75

3.2.2. From Statement

This part of the interface is implicitly by the columns that are selected.

Currently one table contains almost all columns; only the gender is stored in

the care_person table. These two tables are joined and queried but this is

not transparent to the user (shown in figure 3-8).

Figure 3-8: The predefined From Statement

3.2.3. Where Form

When choosing the “Row Condition” – button from the main form, the form

to create a WHERE clause appears.

Figure 3-9: The Where Form

In this form the user has the possibility to specify row conditions of the

SELECT statement. The user can select columns in the same way as in the

SELECT form. All columns from the database are available and the content

Results

Page 29 of 75

of the selection box will be created at runtime. To make the search for all

these columns easier there is also in this form the procedure available to

reduce and specify the column selection by typing letters in.

The design of this form differs from the SELECT form because to generate a

WHERE one has to be able to construct expressions that correspond to

conditions.

The general assembly of a statement in this form is accomplished by

pressing the “Add Row Condition” button. Through this the user can add as

many conditions as he wants. Each line will be generated at runtime (see

appendix 5.2.6, p.68).

One row condition is organized with the possibility to select a column from

the database followed by a comparison operator and a value field. The

comparison operator will show only operators that make sense for to the

selected column. The operators are <, <=, >, >=, <>, =. The value field

contains only the possible values that are stored in the database in the

selected column. A pop up menu shows only these values. Because of this

many mistakes can be avoided but the user can also type in a value or a

numeric in the edit field. Combining more than one row condition takes

place by adding one of the logical operators ‘”And” or “Or”.

The user can set parenthesis to join conditions together and the opportunity

to delete each row is also given.

To avoid mistakes in building a WHERE clause there are some checks

implemented that show the user the mistakes he made, for instance a

reminder that says “there are too many open brackets” or “there is a logical

operator missing” and additionally the fields are coloured.

A WHERE clause is finalized by pressing the “OK” –button, which returns

control to the main form. The main form shows then the WHERE clause and

an “Edit” –button instead of the “Row condition”. Also here the “Help” button

is available.

Results

Page 30 of 75

3.2.4. GroupBy Form

The “Group Condition” button is only available if in the SELECT clause an

aggregate function is specified that after pressing this button the GROUP BY

form appears (shown in figure 3-10).

Figure 3-10: The Group By Form

It has a similar layout as the SELECT form. The GROUP BY from is also

designed with a combo box where the user can choose the columns from the

database. But the difference between this one and the other one from the

SELECT and the WHERE form is that in the GROUP BY combo box only

column names will be shown which are the non-aggregate columns of the

SELECT clause (see appendix 5.2.3, p.64). Columns defining the groups,

which the user adds will appear in the editable text area of the form. Also in

this case by confirming the “OK” button the statement will be copied to the

text area in the main form with the option to return to this form through the

“Edit”- button which appears after creating a GROUP BY clause behind the

text area on the main form. Of course there is also the “Help” button

embedded. With the “Clear” and ”Cancel” – buttons the user has the same

opportunities as in the SELECT form to manually edit the column expression

that defines the group.

Results

Page 31 of 75

3.2.5. Having Form

The “Having Condition” button is only available if the GROUP BY clause is

already created because this part of the SQL clause should give the option

to specify condition for the selection of groups in the GROUP BY clause .

After pressing the “Having Condition” button from the main form, the

HAVING form is displayed (shown in figure 3-11).

Figure 3-11: The Having Form

Because the HAVING clause is very similar to the WHERE clause, this form is

designed in a similar way (see appendix 5.2.6, p.67). The difference

between the WHERE form and HAVING form is that the HAVING clause also

allows the user to specify aggregate functions. Because the HAVING clause

only refers to the GROUP BY clause, the columns that are selectable are only

columns that are guaranteed to have only one value per group. Only

columns are selectable which are still not grouped but they can already be

aggregated in the SELECT clause.

After choosing a column from the combo box, the user can decide between

comparison operators that are matched with the selected condition. In the

same way like in the WHERE clause the value field gives the possibility

either to select the value from the pop up menu, which is also only aligned

to the selected column, or to type in the value in this field (see appendix

5.2.6, p.70). Combining more than one HAVING conditions can also be built

Results

Page 32 of 75

by logical operators. With the “Delete”- button at the end of each row the

opportunity is also given to delete each condition (see appendix 5.2.6,

p.72). By confirming the “OK”- button (see appendix 5.2.6, p.72), the

statement will be carried over to the main form and also here, the

“Condition” button will be replaced with the “Edit”- button. The help tool is

also available in this form.

3.2.6. Storage, Load and Export of the Query

With the “Save” – button the user gets the opportunity to save a created

SQL statement, with the option to load it later again. For saving it only

needs a description (see appendix 5.2.2, p.59). It will be saved onto the file

system.

The “Load” – button reads the saved SQL statement line per line in a text

area that is only readable until the “Edit”- button will be selected. By

confirming the “Execute”- button the saved query will be processed and the

result appears in the result window (see appendix 5.2.2, p.59).

Furthermore, in addition to saving and loading an SQL statement there is

also an opportunity to export the result of a query to a CSV file, which is

used for storage of data structured in a table forms, and which can be

imported into spreadsheet programs (see appendix 5.2.2, p.60).

3.2.7. Search and New Query

To execute a created SQL statement the user has to use the “Search”-

button. In this operation, each part of the SQL statement will be combined

and processed as a whole query (see appendix 5.2.2, p.57-58).

The “New Query”- button resets the main form back to the default settings.

Results

Page 33 of 75

3.2.8. Result Display

The result of the query is displayed in a table that contains the Patient IDs

and the selected columns if the SQL statement has no aggregate functions

(shown in figure 3-12). For this purpose there is also the possibility to look

at the images of the patients by clicking the ‘Show Image’-button (see

appendix 5.2.7, p.74-75). Another feature is to sort the columns by clicking

on the specified column.

As soon as the SQL statement is changed into an aggregate statement the

Patient ID’s will be deleted because the aggregate functions take a table as

a whole and construct new rows from it, thus the resulting rows no longer

correspond to individual patients. Therefore the possibility to look at the

images of the patients is not available. Furthermore the result display of an

aggregate function includes in addition to the selected columns the chosen

aggregations which embed the belonging column names, like count(),

sum(), avg().

Figure 3-12: The Result Table

Results

Page 34 of 75

3.2.9. Help Tool

The analysis tool is not only implemented for people who have knowledge

about SQL. This tool should also give support for users that are not very

familiar with analysis languages (shown in figure 8-12). The help tool can

support the user in creating a query. For using this analysis tool a user only

has to understand the single clauses and how they can work - for instance if

aggregate functions are combined with non-aggregate of columns the user

must know that he has to group those. To support users support in this

aspect, the help tool was developed. It provides an overview about SQL in

general, as well as detailed information for each statement.

Figure 3-13: The Help Tool

The table of contents is placed on the left side (shown in figure 3-13), with

the option to click on each topic. On the right side of the help, the referring

content to each topic appears after choosing one. The content includes for

each statement or clause an explanation followed by a little example. The

example is constructed with the goal of helping the user get a better

understanding why and for what the clauses are used or necessary. The

reason and purpose of this help tool is to support users who are not so

familiar with SQL. The help tool is implemented with the support of the

Results

Page 35 of 75

HTML Help Workshop. This is a tool from Microsoft to establish a help file in

CHM- Format.

Due to the independency of operating systems of the piX4Healthcare system

this help tool has to run also on other operating systems than Windows. To

use the help tool or to get the CHM-file displayed in Linux, the KCHM-Viewer

has to be used.

3.2.10. Show Image Form

If a non-aggregate SQL statement is built, that means without GROUP BY

clause and the HAVING clause, the user gets additionally the possibility to

look at the referring image of a patient. This is made possible by the „Show

image“- button which is placed beneath the result table. After selecting a

patient and confirming the “Show Image”-button a new form appears which

shows the images (shown in figure 3-14 and appendix 5.2.7, p.74-75). The

“Show image” option allows the user to display the images belonging to the

patient while in an aggregate SQL statements a calculation on a set of

records will be performed and it only returns a single returned value that

does not belong to one specific patient.

Figure 3-14: Show Image

Results

Page 36 of 75

3.3. Experimental Results

In this section it will be shown how the query tool can be used to issue

several SQL statements that meet the requirements of the study. Valuable

information can be discovered by selecting specific information from the

view, to enhance research.

3.3.1. Non-aggregate Analysis

Analysis

The analysis includes the request for all patients with Karnowski

Performance Score (KPS), and excludes all patients with unknown KPS. It

also selects the fields CT-Scan, Survival Time, and Chemo at Progression

fields.

Figure 3-15: The Non-aggregate Analysis

Result: All patients are listed without KPS ‘Unknown’.

Results

Page 37 of 75

3.3.2. Aggregate Analysis

Analysis 1

The request in the following query will display the average of Age at the first

visit of all patients that are currently stored in the database for the Brain

Tumour Trial.

Figure 3-16: Aggregate Analysis 1

Result: The current average of the Age at first visit of the stored brain

tumour patients is 61.0876 years.

Results

Page 38 of 75

Analysis 2

The following request also displays the average of the Age at first visit but

only for patients that are right-handed or have a height is less than 175 cm.


Result: With conditions right-handed or with a height less than 175 cm, the

average Age at first Visit is 58.6 years

Analysis 3

The next analysis is a request for the average of the Age at first Visit and

the maximum Survival Time of right-handed, males, with a height less than

175 cm.


Result: The maximum of the Survival Time for right-handed with a height

less than 175 cm is 6 years and the average of the Age is 60.0943 years

Results

Page 39 of 75

Analysis 4

The next analysis deals with the request to list all patients with CT-Scan, the

grade of their Diagnosis and having the Symptom ‘Seizure’ included. Each

condition should be counted and grouped. Furthermore the condition

counting of Seizure has to be more than one to be allowed to get displayed.


Result: The columns of the non-aggregate attributes as well as the

aggregate functions are displayed.

Results

Page 40 of 75

Analysis 5

The KPS is an important aspect of the brain tumour treatment. Therefore

KPS must be grouped and counted then specified by gender. Both have to

be grouped but, this request is only interested in the gender ‘male’.


Result: Only male patients are displayed in each categories of KPS, with a

count of each condition.

By exporting the result into a CSV file, the user can visualize

this as a diagram

Patients male

0

1

2

3

100% 90% 80% 70% 60%

KPS

Cou

nt(K

PS)

Figure 3-21: Diagram referring to Analysis 5

Discussion

Page 41 of 75

4. Discussion

4.1. Conclusion

In this document facts are shown that analyse systems especially in the

research section are not only necessary but also helpful and important.

Dealing with the enormous amount of data, and not only to store these in a

database, but also having the possibility to interact with them is important.

Furthermore the organization of the huge amount of data, and to access all

this data in a structure that facilitates users working with it, is necessary.

This allows users not just working with the data but also to learn from the

data and gain new knowledge and relationships within it. In the case of the

brain tumour patient data from the Cross Cancer Institute, it is also the goal

to work with these data recordings and to build knowledge, new awareness

and in the best case to discover new aspects about the evolution of the

diseases. Due to the constant changes in sphere of interest or due to new

gained background the trials have to be aligned to new ideas. Therefore to

get a complete consistent data record is not easy and all the more reason it

is very important to maintain the data carefully.

Oftentimes data are collected in a way, especially from non-analysts — for

instance physicians, which are sometime not very useable to analyse or to

get sophisticated results. In the case of the collected data from the Cross

Cancer Institute data are sometimes not uniformly. For instance the size of

tumour is sometimes written in three dimensional formats

(valueX,valueY,valueZ) but also in two dimensional (valueX,valueY). Due to

the inconsistent data, analysis can get unusable. These mistakes happen

among other things because everybody currently has to accomplish too

many tasks at the same time and priority has to be set. In the cases of

physicians the priority is set definitely in treatment of patients and to deal

with documentation carefully is more considered annoying and time-

consuming and therefore it is often neglected.

Discussion

Page 42 of 75

Because of constant changes in trial and nonuniform data it is difficult to

achieve consistent sufficient data. Good data collection is more difficult than

the simple step it seems. Missing values or other omissions often add a

contribution to a bias in the analysis and therefore verifications along with

the data readings are very important.

In the related work is shown that other systems like this implemented tool

already exist, such as Online Analytical Processing (OLAP) Systems or Query

by Example (QBE). Using these alternatives has some advantages, but also

some disadvantages; using OLAP Systems requires deep knowledge of

supporting languages like SQL but with knowledge of SQL user can achieve

good results of very complex analysis. Using the QBE system does not

require this knowledge of SQL because it works with a graphical user

interface. Otherwise in the case of the Microsoft Access QBE, the database is

not robust enough do deal with a huge amount of data [4] and QBE only

allows queries which are not too complex in terms of a few tables. For

instance calculating with QBE is not possible without coding [23].

The tool discussed here is meant to combine the advantages of both

systems and negate their disadvantages. The goal in this analysis tool was

to provide the benefits of working with an MYSQL database, but also to allow

users of different backgrounds to use it effectively. If the user is very

experienced with SQL, complicated SQL statements can be created through

the option to type additionally into the given editable text areas in each

statement form. On the other hand if the user is not familiar with SQL, this

tool supports the user to get access to the database through the graphical

user interface and to create clauses in a more simplified way. The user can

build with this graphical user interface aggregate and non-aggregate SQL

statements and has the opportunity to see how the SQL statement is built

because it will be displayed on the main form.

Due to the limited time for this project some tasks are still not fulfilled - for

instance proving this tool for usability for users after a detailed introduction.

Discussion

Page 43 of 75

4.2. Future Work

To obtain meaningful analysis results a very important point is to have good

and structured data as well as uniform collected data. Due to the currently

revised and extended trial from the analysts of the Cross Cancer Institute,

the database management system of piX4Healthcare system must be

restructured, and extended in collaboration with Prof. Dr. Tibor Kesztyüs and

the University of Applied Sciences Ulm. Currently all data of this brain

tumour trial of the Cross Cancer Institute, besides the gender, are stored in

one table. Presently this table includes 130 columns. Therefore, that the

new trial sheet has more than 130 columns now, this table has to be

separated in several tables because the size of one table is limited.

The implemented analysing tool bases at the moment on the specific tables

and data of the current study data. Therefore, the tool should be

implemented with a mechanism to make changes in the data easily without

affecting the tools and to use the tool independent from the study.

Furthermore the analysts of the Cross Cancer Institute have still many data

in excel sheets. These data must be imported through an existing import

tool after the restructuring of the database. This import tool is specific

implemented for the piX4Healthcare system.

Additionally to the restructuring of the database, the physician and the

analysts should work out together a trial sheet that should be on the one

hand easy to fill out for the physician as well as providing collected data that

will enable users to do interesting and significant analyses. Both interests

have to be balanced. The method to get this requirement fulfilled is

collecting the data in a uniform manner and a maintained database.

The piX4Healthcare system that the Cross Cancer Institute uses is only a

reduced version of the piX4Healthcare system in Germany which will be

constantly extended and improved. Getting benefit of these changes the

piX4Healthcare system of the Cross Cancer Institute should be synchronized

and extended with the mainline system.

Discussion

Page 44 of 75

Furthermore the next step could be to increase the complexity of the SQL

statements, for instance to give the user the possibility to build nested

queries simplified through a user-friendly interface. One method to realize

this would be combining the similarity measure tool that is implemented by

M. Sußmann, with the aggregate function tool [24]. The aggregate functions

will be performed on the results of the similarity measures.

To achieve this, the query of the aggregate tool should be based on the

result table of the similarity measures instead of the formdata_btap_cns

table. The goal of this combination will be to get a complex tool, for both

aggregate measures and similarity measures, for instance to count all

female patients who have the Centre of Mass in a specified region. The

similarity tool should only give the possibility to select the Row and ROI

conditions. The aggregate tool should provide column expressions that

contain the resulted columns of the similarity measures.

Glossary

Page 45 of 75

Glossary

ACID: Atomicity, Consistency, Isolation, Durability, a set of properties

that guarantee that database transactions are processed

reliably

API: Application programming interface, an interface that

defines the ways by which an application program may

request services from libraries and/or operating systems

Array: An array type is a data type that is meant to describe a

collection of elements (values or variables), each

selected by one or more indices that can be computed

at run time by the program

ASCII: American Standard Code for Information Interchange, a

character- encoding schema based on the ordering of

the English alphabet

Atlas: A registered scan for which Region of Interest (Venticels,

Corpus Callosum, White Matter, etc..) have been defined

CHM: CHM is a help file. It has a set of web pages written in a

subset of HTML and a hyperlinked table of contents.

CHM format is optimized or readings, as files are heavily

indexed. All files are compressed together. Most CHM

browsers have the capability to display a table of contents

outside of the body text of the Help file

Client: A client is an application or system that accesses a

remote service on another computer system

Combo box:It is a combination of a drop-down list or list box

and a single-line textbox, allowing the user to either

type a value directly into the control or choose from the

list of existing options

Csv: Comma separated values; a file which is used for

storage of data structured in a table of lists form and

can be imported into spreadsheet programs

Glossary

Page 46 of 75

DBMS: Database Management System, a set of computer

Programs that controls the creation, maintenance, and

the use of the database of an organization and its end users.

DTI: Diffusion Tensor Imaging, an MR imaging technique

that indicates the directions of fluid flow through tissue

Edema: Swelling of any organ or tissue due to accumulation of

Excess fluid. Edema has many root causes, but its

common mechanism is accumulation of fluid into the

tissues. Cerebral Edema is swelling of the brain which

can occur as the result of a head injury, cardiac arrest,

a brain tumour, or from the lack of proper altitude

acclimatization

Entity: An entity may be defined as a thing which is recognized

as being capable of an independent existence and

which can be uniquely identified.

FLAIR: Fluid Attenuated Inversion Recovery, an MR imaging

technique that produces images similar to T2-weighted

images, but with free water (i.e. normal CSF)

suppressed

GUI: Graphical user interface, a human-computer interface

that uses windows, icons and menus and which can be

manipulated by a mouse.

Item: Is a selected column from the database

Java: Java is an object oriented programming language

KCHM-Viewer: kchmviewer is a CHM (Winhelp) files viewer written

in Qt/ KDE.

MRI: Magnetic Resonance Imaging, an imaging technique

based on the principles of NMR (Nuclear Magnetic

Resonance – a spectroscopic technique used by

scientists to elucidate chemical structure and molecular

dynamics)

Glossary

Page 47 of 75

PID: Each Patient Identification number belongs only to one

patient

Pixel: Elementary point in a picture, usually displayed as a

single- coloured square

RAD: Rapid Application Development provides a number of

tools to help build graphical user interfaces that would

normally take a large development effort.

RDBMS: A Relational database management system (RDBMS), a

DBMS in which data is stored in the form of tables and

The relationship among the data is also stored in the

form of tables.

Server: A server is any combination of hardware or software

designed to provide services to clients

Slice: An orthogonal view of the body part being visualized by

the MRI

SQL: Structured Query Language, the standard syntax for

using a database management system

Study: A set of images of a patient’s brain, all taken in one

session

T1: weighted: An MR image modality that highlights fat

locations

T1C: weighted: An MR image modality that is taken after the

injection of the contrast agent gadolinium

T2: weighted: An MR image modality that highlights water

locations

Voxel: 3-dimensional version of a pixel

Bibliography

Page 48 of 75

Bibliography

[1] Prof. Dr. Jan-Christoph Simon, Veraenderte Umwelt und Krankheit

[08/2009]

http://www.uni-leipzig.de/profil/pdf/6.pdf ,2006

[2] Brain Tumour Analysis Project (BTAP) website (Online). BTAP,

[08/2009]

http://www.cs.ualberta.ca/~btgp/.

[3] Cancerbackup (Online)

[08/2009]

http://www.cancerbackup.org.uk/Cancertype/Brain/General/Thebrain,

April 2007

[4] Prof. Dr. T.I. Kesztyüs,

Class medical documentation and information systems, 2009

[5] MedicineNet

[08/2009]

http://www.medterms.com, 1996

[6] Jörg Sander, Tibor I Kesztyüs, Jonathan Levesque1, Albert Murtha,

and Matthew R G Brown,

A prototype for a decision support system in radiooncology therapy

planning for brain tumors

[7] Sasha Pachev,

Understanding MySQL Internals by O'Reilly Media, Inc., 2007/04/10

Bibliography

Page 49 of 75

[8] Paul DuBois,

MYSQL®, Fourth Edition, Addison-Wesley Professional, 2008

[9] Rudy Limeback,

Simply SQL, 1st Edition, SitePoint, 2008

[10] John J. Patrick,

SQL Fundamentals, Third Edition, Prentice Hall, 2008

[11] OLAP AND OLAP SERVER DEFINITIONS

[08/2009]

http://altaplana.com/olap/glossary.html, 1995

[12] IWIKI:

[08/2009]

http://www.iwiki.de/wiki/index.php/QBE, 2006

[13] Controlling- Portal

[08/2009]

http://www.controllingportal.de/Fachinfo/Business-

Intelligence/OLAP- Online-Analytical-Processing.html

[14] Oracle

[08/2009]

www.oracle.com/solutions/business_intelligence/olap.html

[15] Lynn Langit,

Foundations of SQL Server 2005 Business Intelligence, Apress, 2007

[16] Robert Ericsson; Jason Cline

SQL Server 2005 For Developers, Course Technology PTR, 2006

Bibliography

Page 50 of 75

[17] Roger Jennigs,

Special Edition Using Microsoft® Office Access 2007, Que, 2007

[18] Lazarus

[08/2009]

www.lazarus.freepascal.org, 2006

[19] Free Pascal

[08/2009]

http://www.freepascal.org/, 1993

[20] Zeos Lib

[08/2009]

http://zeos.firmos.at, 2001

[21] Wapedia

[08/2009]

http://wapedia.mobi/en/Talk:FASMI, 2006

[22] Bob Villareal,

Access 2002 Programming by Example, Que, 2002

[23] Maike Sussmann,

Similarity Searching using Image Features

for a Brain Tumour Database, University of Applied Sciences Ulm, BSc

2009

Appendix

Page 51 of 75

5. Appendix

The following parts are only extractions of the implemented tools. It only will

be highlighted the main and important sections

5.1. Implementation of the Import Tool

5.1.1. ASCII files to import image data for each patient:

The ASCII file is built in the same way as the relations of the database

structure. That means that to one entity can refer one or several entities.

Each line describes an entity and begins with the table name set in

parentheses followed by the contents of the table, separated by delimiters.

The voxels are stored in a separate ASCII file that contains the x, y and z

coordinates.

[study]|description1|studydate1

[imagetype]|description

[image]|slicenr|link1


[imagetype]|description2



[aggregate]roi_id|description|value


[voxelset]roi_id|linktotextfile


[study]|description2|studydate2

[imagetype]|description







Appendix

Page 52 of 75

5.1.2. Coding of the Import Tool

First the ASCII file is loaded and each line of the file is read out. Depending

on the entity the belonging values are read out through the specified

delimiters and saved into the according table. The import is set into a try-

catch block. If an exception occurs the complete import is rolled back by

deleting the study from the table. The ON DELETE CASCADE option, which is

set in the image tables, assures that all rows are deleted in the child tables

which correspond to the study in the parent table.

procedure TForm1.ButtonImportClick(Sender: TObject); variables begin assignFile (InFile, filename); reset (InFile); if (pid<>'')then begin sql_pid:='SELECT pid from btap_study where pid='+pid+''; DeleteQuery.SQL.CLear; DeleteQuery.SQL.add(sql_pid); DeleteQuery.Open; if (DeleteQuery.FieldByName('pid').AsString=pid) then begin DeleteQuery.SQL.CLear; d_sql:='delete from btap_study where pid='+pid+''; DeleteQuery.SQL.add(d_sql); DeleteQuery.ExecSQL; end; try while not eof (InFile) do begin readln (InFile, S); line:=S; if (line<>'') then begin l.Delimiter:='|'; l.DelimitedText:=S; test:=l[0]; if test='[study]' then begin studydescr:=l[1]; studydate:=l[2]; StudyQuery.Append; StudyQuery.FieldByName('description').AsString:=studydescr; StudyQuery.FieldByName('study_date').AsString:=studydate; StudyQuery.FieldByName('pid').AsString:=pid;

Appendix

Page 53 of 75

StudyQuery.Post; StudyQuery.Refresh; end else if test='[imagetype]' then … else if test='[image]' then … else if test='[aggregate]' then… else if test='[voxelset]' then begin roi_id:=l[1]; filenamevoxel:=l[2]; lVoxel:=TStringList.Create(); assignFile (InFileVoxel, filenamevoxel); reset (InFileVoxel); while not eof (InFileVoxel) do begin readln (InFileVoxel, SVoxel); line:=SVoxel; if (line<>'') then begin lVoxel.Delimiter:=','; lVoxel.DelimitedText:=SVoxel; voxel_x:=lVoxel[0]; voxel_y:=lVoxel[1]; voxel_z:=lVoxel[2]; VoxelQuery.SQL.Clear; VoxelQuery.SQL.Add('Insert into btap_roi_voxelset (study_id,roi_id,x,y,z) values('+StudyQuery.FieldByName('study_id').AsString+',' +roi_id+','+voxel_x+','+voxel_y+','+voxel_z+')'); VoxelQuery.ExecSQL; end; end; lVoxel.Clear; end; end; l.Clear; end; except on E:Exception do begin DeleteQuery.SQL.CLear; d_sql:='delete from btap_study where pid='+pid+''; DeleteQuery.SQL.add(d_sql); DeleteQuery.ExecSQL; ShowMessage('Exception message='+E.Message+chr(10)+chr(10)+'Import did not work'); end; end; end else begin ShowMessage('Pid is missing'); end; EditPid.Text:='';

Appendix

Page 54 of 75

5.2. Coding of the Query Tool

Note: The following code extractions are expressed in simplified terms.

5.2.1. Function Combination

After choosing an item of a combo-box, this function replaces the name by

the corresponding database name.

function TClass_Combination.Combination(selecteditem:String):String; begin if (selecteditem='All')then selecteditem:='*'; if (selecteditem='Patient ID')then selecteditem:='formdata_btap_cns.pid'; if (selecteditem='Gender')then selecteditem:='care_person.sex'; if (selecteditem='Handedness') then selecteditem:='handedness'; if (selecteditem='19Q Loss of Heterozygosity 1st Bx') then selecteditem:='q_loss_of_heterozyg_first_bx'; ….. end;

The purpose of this function is to change the database name to a predefined

title for each column of the results table, to increase readability of the

results.

function TClass_Combination.DBCombination2(field:String):String; begin if (field='pid') or (field='formdata_btap_cns.pid') then field:='Patient ID'; if (field='sex')then field:='Gender'; if (field='care_person.sex')then field:='Gender'; …… end;

Appendix

Page 55 of 75

This function is an extension to the function above. If an aggregate function

is selected it extends the title in the result table additionally with the chosen

aggregate function.

function TClass_Combination.DBCombination(field:String):String; if copy(field,0,4)='min(' then begin field:=Copy(field,5,length(field)); testfield:=Copy(field,0,length(field)-1); field2:=DBCombination2(testfield); field2:='Min('+field2+')'; end ……

5.2.2. Query

This section shows the implementation of the SELECT statement which is

displayed in figure 8- and 8-3. At the beginning it creates a small text area

with the asterix as default and by activating this button the SELECT form will

appear. After creating the SELECT statement in the form, the text area will

be returned to this procedure and will be loaded to this text area that

adjusts the size to the SELECT statement automatically. This occurs in all

statements.

procedure Tfo_query.Button_ColumnClick(Sender: TObject); begin having_counter:=1; Memo_Select.Text:='*'; Application.CreateForm(TForm_Select,Form_Select); Form_Select.ShowModal; select_sql:=Form_Select.select_sql; if not (Form_Select.select_sql='') then begin Button_Column.Visible:=false; s:=Copy(Form_Select.select_sql,8,Length(Form_Select.select_sql)); Memo_Select.Width:=Canvas.TextWidth(s)+80; Memo_Select.Height:=Canvas.TextHeight(s)+10; Memo_Select.HorzScrollBar.Visible:=false; Memo_Where.VertScrollBar.Visible:=false;

Appendix

Page 56 of 75

if (Memo_Where.Height>65) then begin Memo_Where.VertScrollBar.Visible:=true; Memo_Select.Text:=s; Button_select_edit.Visible:=true; If Memo_Select.Width>850 then begin Memo_Select.Width:=900; Button_select_edit.Left:=Memo_select.Left+Memo_select.Width+10; end; end; end; ….

With the Edit procedure, the Select Form will appear again, but with the

existing content the user has already created. If the user decides to clear

this statement, the part on the main form will be set back to the default

settings like in the procedure above.

procedure Tfo_query.Button_select_editClick(Sender: TObject); begin Application.CreateForm(TForm_Select,Form_Select); if not (Form_Select.select_sql='Select ') then begin Form_Select.select_sql:=select_sql; Form_Select.ShowModal; select_sql:=Form_Select.select_sql; end; if not ((Form_Select.select_sql='Select ')or (Form_Select.select_sql='')) then begin Button_Column.Visible:=false; s:=Copy(Form_Select.select_sql,8,Length(Form_Select.select_sql)); Memo_Select.Width:=Canvas.TextWidth(s)+80; Memo_Select.Height:=Canvas.TextHeight(s)+10; Memo_Select.HorzScrollBar.Visible:=false; Memo_Select.VertScrollBar.Visible:=false; end; if (Memo_Select.Height>65) then begin Memo_Select.VertScrollBar.Visible:=true; Memo_Select.Text:=s; Memo_Select.Visible:=true; end; If Memo_Select.Width>850 then

Appendix

Page 57 of 75

begin Memo_Select.Width:=900; Button_select_edit.Left:=Memo_select.Left+Memo_select.Width+10; end else begin Form_Select.select_sql:='Select *'; select_sql:='Select *'; Memo_select.Width:=60; Memo_select.Caption:=' ' + Copy(Form_Select.select_sql,8,Length(Form_Select.select_sql)) + ' '; Button_Column.Visible:=true; Button_select_edit.Visible:=false; Button_select_edit.Left:=Memo_select.Left+Memo_select.Width+10; end;

In this Procedure the SQL subcomponents are composed into a single SQL

statement.

procedure Tfo_query.Query(); begin DBGrid1.Columns.Clear; getfield:=TClass_Combination.create(); if select_sql='Select *' then select_sql:=getfield.select_sql; v_sql:=select_sql+from_sql+where_sql+groupby+having; formdata_btap_cns.pid,'+Copy(v_sql,7,Length(v_sql)) v_sql:='Select '+Copy(v_sql,7,Length(v_sql));

ZQuery1.SQL.CLear; ZQuery1.SQL.add(v_sql); ZQuery1.open; if select_sql<>'Select *' then begin for i:=0 to DBGrid1.Columns.Count-1 do begin title:=getfield.DBCombination(DBGrid1.Columns[i].Title.Caption);

DBGrid1.Columns[i].Title.Caption:=title; DBGrid1.Columns[i].Alignment:=taLeftJustify; if title='KPS' then DBGrid1.Columns[i].Width:=Canvas.TextWidth(title)+450 else DBGrid1.Columns[i].Width:=Canvas.TextWidth(title)+10; end; end;

Appendix

Page 58 of 75

The Search Procedure first checks the validity of the query. If there are any

errors, a warning is displayed to the user. If the query is complete it will be

processed.

procedure Tfo_query.Button_SearchClick(Sender: TObject); begin if (groupby<>'') then Button_showImage.Enabled:=false; if ((select_sql='Select *') and (groupby<>'')) then ShowMessage('This SQL Statement would not make sense.You have to select either specified colums in the Select Statement OR clear the Group By Statement') else if((select_sql='Select *') and (having<>'')) then ShowMessage(('This SQL Statement would not make sense.You have to select either specified colums In the Select Statement AND in the Group By Statement OR clear the Having Clause')) else if ((select_sql<>'Select *') and (having<>'')and (groupby='')) then ShowMessage('This is not a valid SQL Statement! You have to group either a condition in the Group By Statement OR clear the Having Clause') else if ((Form_Select.agg_counter=1) and (groupby='')) then ShowMessage('You have selected an Aggregate function in the Select Statement but you have not grouped it in the Group By Statement yet') else begin if GroupBox_Query.Enabled=true then Query() else begin ZQuery1.SQL.Clear; ZQuery1.SQL.Add(sql); ZQuery1.Open; end; end;

Appendix

Page 59 of 75

The Save procedure is implemented to save the current SQL statement into

a textfile for later reuse.

procedure Tfo_query.Button_saveSqlClick(Sender: TObject); begin s:=select_sql+from_sql+where_sql+groupby+having; savedialog:=TSaveDialog.Create(self); savedialog.FileName:='SqlStatement'; savedialog.Filter := 'SQL|*.txt'; savedialog.DefaultExt:='txt'; If savedialog.Execute = True Then begin filename:=savedialog.FileName; ZQuery1.SQL.CLear; ZQuery1.SQL.add(s); ZQuery1.open; sl := TStringlist.Create; sl.add(s); end; sl.savetofile(filename); sl.free; savedialog.Free; Query();

The Load Procedure allows the user to load a saved query

procedure Tfo_query.Button_loadClick(Sender: TObject); begin if OpenDialog1.Execute then begin AssignFile(F, OpenDialog1.FileName); Reset(F); Readln(F, S); Form_load.Memo_load.Text := S; CloseFile(F); Form_load.ShowModal; DBGrid1.Columns.Clear; getfield:=TClass_Combination.create(); ZQuery1.SQL.CLear; ZQuery1.SQL.add(Form_load.load_sql); ZQuery1.open; for i:=0 to DBGrid1.Columns.Count-1 do

Appendix

Page 60 of 75

begin DBGrid1.Columns[i].Alignment:=taLeftJustify; DBGrid1.Columns[i].Width:=120 end; end; end;

The user has the ability to export the results of a search into a Comma-

Separated Values file (CSV), implemented by the following Export

procedure.

procedure Tfo_query.Button_ExportClick(Sender: TObject); begin getfield:=TClass_Combination.create(); sql:=v_sql + ' Order By pid'; savedialog:=TSaveDialog.Create(self); savedialog.FileName:='CNSData'; savedialog.Filter := 'CSV|*.csv'; savedialog.DefaultExt:='csv'; If savedialog.Execute = True Then begin filename:=savedialog.FileName; ZQuery1.SQL.CLear; ZQuery1.SQL.add(sql); ZQuery1.open; sl := TStringlist.Create; s:=''; for col := 0 to ZQuery1.FieldCount-1 do s := s + getfield.DBCombination(ZQuery1.Fields[col].DisplayLabel) +','; sl.Add(s); for row := 0 to ZQuery1.RecordCount-1 do begin s:=''; for col:=0 to ZQuery1.FieldCount-1 do s := s+ZQuery1.Fields[col].AsString + ','; sl.add(s); ZQuery1.Next; end; end; sl.savetofile(filename); sl.free; savedialog.Free; Query(); end;

Appendix

Page 61 of 75

5.2.3. Select

This procedure implements how the combo box will be filled with all existing

items from the database during runtime. The correct fields and names are

supplied by the function call “DBcombination”.

procedure TForm_Select.FillCombo box(); begin getfield:=TClass_Combination.create(); select:=getfield.select_sql; ZQuery1.SQL.Clear; v_sql:=select+ ' FROM formdata_btap_cns left outer join care_person on formdata_btap_cns.pid=care_person.pid'; ZQuery1.SQL.Add(v_sql); ZQuery1.Open; for i := 0 to ZQuery1.FieldCount-1 do begin field:=ZQuery1.Fields[i].DisplayLabel; item:=getfield.DBCombination(field); Combo box_Select.Items.Add(item); ZQuery1.Next; end; Combo box_Select.Sorted:=True; end;

Users are allowed to narrow the results shown in a combo-box by typing in a

few characters contained in the field being searched for. This it is shown in

figure 8-4. This procedure is the implementation to restrict the list of items

in the combo-box. The single characters typed in are read and only the

items with these characters will be listed in the combo-box.

procedure TForm_Select.Combo box_SelectChange(Sender: TObject); begin srch:=Combo box_Select.Text; srchl := Length(srch); li:=0; Combo box_Select.Items.Clear; while (li<=l.Count-1) do begin item:=l[li]; itemsrch := Copy(Item, 1, srchl);

Appendix

Page 62 of 75

if (UpperCase(srch)=UpperCase(itemsrch)) then Combo box_Select.Items.Add(item); li:=li+1; end; Combo box_Select.DroppedDown:=true; Combo box_Select.SetFocus; end;

The FillAggregate procedure creates the Aggregates; it produces different

output for different columns because only certain aggregates are applicable

for each column based on the data type and values stored in that column.

procedure TForm_Select.FillAggregate(); begin aggregate:=Combo box_Select.Text; if(aggregate='Date of Birth') or(aggregate='Date of Death') or(aggregate='Date of 1st Bx') or(aggregate='Date of 2nd Bx') or(aggregate='Date of Initial Consultation') or(aggregate='Date of Initial CT') or(aggregate='Date of Initial MRI') or(aggregate='Date of Initial MRI Showing Growth') ……….. Then begin agg_combo box.Items.Clear; agg_combo box.Items.Add('count'); agg_combo box.Items.Add('min'); agg_combo box.Items.Add('max'); end else if(aggregate='Grade Diagnosis 1st Bx') or(aggregate='Grade Diagnosis 2nd Bx') or(aggregate='Survival Time') or(aggregate='Age of first Visit') or(aggregate='Patients Height') ….. Then begin agg_combo box.Items.Clear; agg_combo box.Items.Add('count');

Appendix

Page 63 of 75

agg_combo box.Items.Add('min'); agg_combo box.Items.Add('max'); agg_combo box.Items.Add('avg'); end; end;

The Button_Add procedure converts the selected columns into the real

database names through the DBCombination. The select_sql statement

which will be returned to the main form is built in this procedure. Exception

checking here allows errors to be recognized instantly.

procedure TForm_Select.Button_addClick(Sender: TObject); begin getfield:=TClass_Combination.create(); field:=getfield.Combination(Combo box_Select.Text); if (Combo box_Select.Text='')then begin ShowMessage('You have first to select a column'); end else if (CheckBox1.Checked=true) and(agg_combo box.Text='') and(Combo box_Select.Text<>'') then begin ShowMessage('An aggregate function is missing or uncheck the Aggregate'); agg_combo box.Enabled:=true; end else if not(Combo box_Select.Text='') then begin if (select_sql='') and (CheckBox1.Checked=false)and (Combo box_Select.Text<>'') then select_sql:='Select ' + field else if (CheckBox1.Checked=true) and (select_sql='')and (Combo box_Select.Text<>'') then begin select_sql:='Select '+agg_combo box.Text+'('+field+')'; select_agg:=1; end else if (CheckBox1.Checked=true) and (select_sql<>'') then begin select_sql:=select_sql+','+agg_combo box.Text+'('+field+')'; end else select_sql:=select_sql + ', ' + field; end; if ((agg_combo box.Text<>'') and (CheckBox1.Checked=true)) then agg_counter:=1 ;

Appendix

Page 64 of 75

//Exceptions if(agg_combo box.Text<>'')and(Combo box_Select.Text<>'')and (CheckBox1.Checked=true)then begin Memo1.Text:=select_sql; Combo box_Select.Text:=''; FillCombo box(); add_count:=add_count+1; agg_combo box.Text:=''; agg_combo box.Enabled:=false; CheckBox1.Checked:=false; end else if (agg_combo box.Enabled=false)and(Combo box_Select.Text<>'')and (CheckBox1.Checked=false)then begin Memo1.Text:=select_sql; Combo box_Select.Text:=''; FillCombo box(); add_count:=add_count+1; agg_combo box.Enabled:=false; end; end;

If an aggregate is added, this column will be additionally written in a

stringlist and fills the combo-box in the GROUPBY Form. This is also

implemented in the Button “Add_Click” Procedure.

//fill Combo box_groupby group_items:=Combo box_Select.Text; if(CheckBox1.Checked=false) and (group_items<>'')then begin stringlist.Add(group_items); end;

Appendix

Page 65 of 75

This procedure is invoked and returns the finished SELECT statement to the

text area in the main form by copying the text area from the select form, if

the Ok_Button in the Select form is clicked (shown in 8-4),.

procedure TForm_Select.Button_OKClick(Sender: TObject); begin if(Memo1.Text='Select ')or (Memo1.Text='') then begin Memo1.Text:=''; select_sql:='Select *'; end; ok:=true; Form_Select.close; select_sql:=Copy(Memo1.Text,0,Length(Memo1.Text)-1); end;

5.2.4. Where

The Where and the HAVING clause are built similarly to the Select clause.

Procedures will be explained in the HAVING implementation.

5.2.5. GROUPBY

The combo box of this form will be filled by calling up the GROUPBY form

procedure.

procedure T_GroupBy.FormShow(Sender: TObject); begin Memo1.Text:=groupby_sql; for i:=0 to Form_Select.stringlist.count-1 do begin item:=Form_Select.stringlist[i]; Combo box_groupby.Items.Add(item); end; end;

Appendix

Page 66 of 75

This procedure is implemented in the same way as in the select combo box,

with the ability to filter the list of items by typing in characters.

procedure TForm_Groupby.Combo box_groupbyChange(Sender: TObject); begin srch:=Combo box_groupby.Text; srchl := Length(srch); li:=0; Combo box_groupby.Items.Clear; while (li<=Form_Select.stringlist.Count-1) do begin item:=Form_Select.stringlist[li]; itemsrch := Copy(Item, 1, srchl); if (UpperCase(srch)=UpperCase(itemsrch)) then begin Combo box_groupby.Items.Add(item); li:=li+1; end; Combo box_groupby.DroppedDown:=true; Combo box_groupby.SetFocus;

When the “Add_ Button” in the GROUPBY form (shown in figure 8-10) is

clicked this procedure is invoked. It allows that the column names which are

loaded from the stringlist of the select form, the GROUPBY clause will be

created at this point.

procedure TForm_GroupBy.Button_addClick(Sender: TObject); begin i:=0; if not (Combo box_groupby.Text='') then begin getfield:=TClass_Combination.create(); field:=getfield.Combination(Combo box_groupby.Text); if groupby_sql='' then groupby_sql:='GROUP BY '+field else groupby_sql:=groupby_sql+','+field; Memo1.Text:=groupby_sql; Combo box_groupby.Text:=''; Combo box_groupby.Items.Clear; while (i<=Form_Select.stringlist.Count-1) do begin item:=Form_Select.stringlist[i];

Appendix

Page 67 of 75

Combo box_groupby.Items.Add(item); i:=i+1; end; end else ShowMessage('Please select first a columnname'); end;

In this section the items which are selected will be loaded into another

stringlist. This stringlist is important as when the HAVING combo-box is

clicked, it can be loaded with the items which are chosen in this form.

//fill the having Groupbox having_items:=Combo box_groupby.Text; stringlist_having.Add(having_items);

5.2.6. Having

As mentioned above in the WHERE clause, Having and the WHERE forms are

implemented in the same way. All procedures will be referred also to the

WHERE code besides the aggregate possibility.

If the “Add” – Button is clicked and therefore invoked the procedure will add

a new row, each of this will be created at runtime. Through arrays of each

object, any number of rows can be created. This is shown in figure 8-9 and

8-11.

//GroupBox Item Setlength(GroupBoxesItem, counter+1); GroupBoxesItem[counter]:= TGroupBox.Create(self); GroupBoxesItem[counter].Name:= 'GBItem'+InttoStr(counter+1); GroupBoxesItem[counter].Parent:= ScrollBox1; GroupBoxesItem[counter].Left:= 1; GroupBoxesItem[counter].Top:= x_gbItem; GroupBoxesItem[counter].Width:=850;

Appendix

Page 68 of 75

GroupBoxesItem[counter].Height:=41; GroupBoxesItem[counter].Caption:=''; x_gbItem:=x_gbItem+70; //CheckBox Aggregate Setlength(Aggregate, counter+1); Aggregate[counter]:= TCheckBox.Create(self); Aggregate[counter].Name:= 'CBAggregate'+InttoStr(counter+1); Aggregate[counter].Parent:= GroupBoxesItem[counter]; Aggregate[counter].Left:= 0; Aggregate[counter].Top:= -4; Aggregate[counter].Width:=25; Aggregate[counter].Height:=25; Aggregate[counter].Caption:=''; Aggregate[counter].Font.Bold:=false; Aggregate[counter].OnClick:=@AggregateCheckboxClick; //MenuItem1_agg Setlength(MenuItem1_agg, counter+1); MenuItem1_agg[counter]:= TMenuItem.Create(self); MenuItem1_agg[counter].Name:= 'MenuItem1_agg'+InttoStr(counter+1); MenuItem1_agg[counter].OnClick:=@OnPop1Click; //PopUp1 Setlength(PopUp1,counter+1); PopUp1[counter]:= TPopupMenu.Create(self); PopUp1[counter].Name:= 'PopupMenu1'+InttoStr(counter+1); PopUp1[counter].Parent:= GroupBoxesItem[counter]; PopUp1[counter].Items.Add(MenuItem1_agg[counter]); PopUp1[counter].Items.Add(MenuItem2_agg[counter]); PopUp1[counter].Items.Add(MenuItem3_agg[counter]); PopUp1[counter].Items.Add(MenuItem4_agg[counter]); PopUp1[counter].Items.Add(MenuItem5_agg[counter]); PopUp1[counter].Items.Add(MenuItem6_agg[counter]); …….

Each object has several procedures, including the OptionAggregateClick

procedure. This procedure will be operated if the user clicks on the

“Aggregate” - button. The varchar fields have pop-up menus which list the

possible aggregates which the user can select. It is set the text of the

menuItems. This avoids mistakes by filling out the aggregates. The same

takes place by the value fields.

Appendix

Page 69 of 75

procedure TForm_having.OptionAggregateClick(Sender: TObject); begin i:=0; while i < High(Edits) do if (Edits[i] = Sender) then Break; inc(i); end; if (Edits[i] = Sender) then begin for i:=0 to counter-1 do begin if (Columns[i].Text='') and (Aggregate[i].Checked=true)then ShowMessage('You first have to select a Column'); end; if(Columns[i].Text='Date of Birth') or(Columns[i].Text='Date of Death') or(Columns[i].Text='Date of 1st Bx') or(Columns[i].Text='Date of 2nd Bx') or(Columns[i].Text='Date of Initial Consultation') then begin for j:=3 to 5 do PopUp1[i].Items[j].Visible:=false; MenuItem1_agg[i].Caption:='count'; MenuItem2_agg[i].Caption:='min'; MenuItem3_agg[i].Caption:='max'; PopUp1[i].PopUp(); end; else if (Columns[i].Text='Grade Diagnosis 1st Bx') or (Columns[i].Text='Survival Time') or (Columns[i].Text='Age of first Visit') or(Columns[i].Text='Patients Height') then begin for j:=4 to 5 do PopUp1[i].Items[j].Visible:=false; MenuItem1_agg[i].Caption:='count'; MenuItem2_agg[i].Caption:='min'; MenuItem3_agg[i].Caption:='max'; MenuItem4_agg[i].Caption:='avg'; PopUp1[i].PopUp(); end; end

Appendix

Page 70 of 75

This part shows the implementation of how the items of the HAVING combo

box get filled in, loaded from the created stringlist from the GROUPBY form.

Columns[counter].Items.Clear; for j:=0 to Form_GroupBy.stringlist_having.count-1 do begin items:=Form_GroupBy.stringlist_having[j]; Columns[counter].Items.Add(items); end;

In this procedure creates the HAVING clause. Each component will be

combined to the whole statement

Procedure TForm_having.Query(); begin having_sql:=''; for i := 0 to counter-1 do begin //Column selecteditem:=Columns[i].Text; //Value if (Edits_value[i].Text='')then value:='' else value:='"'+Edits_value[i].Text+'"'; //Aggregate //aggregate_hav:='"'+Edits[i].Text+'"'; if (Edits[i].Text<>'') then aggregate_hav:=Edits[i].Text else aggregate_hav:=''; if (CompOperators[i].Text<>'') then compoperation:=CompOperators[i].Text else compoperation:=''; //logoperation ogoperation:=LogOperators[i].Text; //Fields getfield:=TClass_Combination.create(); field:=getfield.Combination(selecteditem); if Aggregate[i].Checked=true then agg:='('+field+')' else agg:=field; if not(Edits_value[i].Text='') then begin if(selecteditem='Date of Birth')or (selecteditem='Date of Death')or then begin field:='str_to_date('+field+',"%d-%b-%Y")'; value:='str_to_date('+value+',"%d-%b-%Y")';

Appendix

Page 71 of 75

end; if(selecteditem='ECOG')then begin value:='mid('+value+',1,1)'; field:='mid(ecog,1,1)'; end; if(selecteditem='KPS')then begin value:='cast(mid('+value+',1,3) as signed)'; field:='cast(mid(kps,1,3) as signed)'; end; //SQL having_sql:=having_sql+' '+logoperation+' '+aggregate_hav+agg+compoperation+''+value+''; if not (selecteditem='') then begin if not (Edits_value[i].Text='') and (compoperation='') then begin //selectes items date and ecog kps If compoperation='<>'then having_sql:=having_sql+' '+logoperation+' '+aggregate_hav+' not isNull('+agg+')'+ compoperation+value else having_sql:=having_sql+' '+logoperation+' '+aggregate_hav+' isNull('+agg+')'+compoperation+value; end else if (counter=0) then having_sql:='HAVING '+aggregate_hav+agg+compoperation+''+value+'' else having_sql:=having_sql+' '+logoperation+' '+aggregate_hav+agg+compoperation+''+value+''; ……

The “Delete” procedure implements how a whole row will be deleted. It is

independent which row has to be deleted. Thus all components are created

with an array, the index can be figured out, will be deleted and all following

rows will be moved forward in the array. The following code is only an

excerpt of the”Delete” procedure.

procedure TForm_having.Button_DeleteClick(Sender: TObject); Aggregate[i].Free; Aggregate[i]:=NIL; Columns[i].Free; Columns[i]:=NIL; … Aggregate[j+1].Name:= 'CBAggregate'+InttoStr(j+1);

Appendix

Page 72 of 75

Aggregate[j]:=Aggregate[j+1]; Columns[j+1].Name:= 'CBColumn'+InttoStr(j+1); Columns[j]:=Columns[j+1]; … SetLength(Aggregate, length(Aggregate)-1); SetLength(Columns, length(Columns)-1);

The button Ok procedure first checks if all requirements on this form are

fulfilled before the Having clause will be returned to the main form. If

anything is incorrect, a message is displayed that shows what is incorrect.

procedure TForm_having.Button_okClick(Sender: TObject); for i:=0 to counter-1 do begin if not (Columns[i].Text='') then begin if (CompOperators[i].Text='')and (Edits_value[i].Text<>'') then begin ShowMessage('You have to fill in a comparison operator in '+Columns[i].Text+''); CompOperators[i].Color:=clRed; missingfields:=true; end; ….

Appendix

Page 73 of 75

5.2.7. Show Image

The image will be loaded through processes the query belonging to the

selected study, the description and the PID.

procedure TForm_patient.ComboBox_img_typeChange(Sender: TObject); begin imgtype:=ComboBox_img_type.Text; v_sql:='SELECT btap_image.link FROM (btap_imagetype inner join btap_image on btap_imagetype.imagetype_id=btap_image.imagetype_id) '; v_sql:=v_sql+'inner join btap_study on btap_study.study_id=btap_imagetype.study_id '; v_sql:=v_sql+'where btap_study.description="'+study+'" and pid='+patid+' and btap_imagetype.description="'+imgtype+'"'; if not (imgtype='Select') then begin ZQuery_img_type.SQL.Clear; ZQuery_img_type.SQL.Append(v_sql); ZQuery_img_type.Open; imagenr:=0; for row := 0 to ZQuery_img_type.RecordCount-1 do begin Setlength(image, ZQuery_img_type.RecordCount-1); image[row] := TImage.Create(Self); link:=ZQuery_img_type.FieldByName('link').AsString; image[row].Picture.LoadFromFile(link); image[row].Parent:=Form_patient; image[row].Height:=256; image[row].Width:=256; image[row].Left:=60; image[row].Top:=135; image[row].Stretch:=False; image[row].Visible:=false; imagenr:=imagenr+1; ZQuery_img_type.Next; end; imagenr:=0; image[imagenr].Visible:=True; end; end;

Appendix

Page 74 of 75

If the study will be changes in the combo box (shown in the figure 8-14) the

available imagetypes will be load

procedure TForm_patient.ComboBox_studyChange(Sender: TObject); begin ComboBox_img_type.Items.Clear; ComboBox_img_type.Text:='Select'; Form_patient.Height:=130; Image1.Visible:=false; Label4.Visible:=false; Edit_slice.Visible:=false; ButtonLeft.Visible:=false; ButtonRight.Visible:=false; study:=ComboBox_study.Text; v_sql:='SELECT btap_imagetype.description FROM care4x.btap_imagetype inner join btap_study on btap_imagetype.study_id=btap_study.study_id where pid="'+patid+'" and btap_study.description="'+study+'"'; if not (study='Select') then begin ZQuery_img_type.SQL.Clear; ZQuery_img_type.SQL.Add(v_sql); ZQuery_img_type.Open; ZQuery_img_type.Active:=true; for row := 0 to ZQuery_img_type.RecordCount-1 do begin descr:=ZQuery_img_type.FieldByName('description').AsString; ComboBox_img_type.Items.Add(descr); ZQuery_img_type.Next; end; end; end;

The images are separated in slices. With this procedure the user can click

through all slices. The same implementation exists for the “Right_Click”.

procedure TForm_patient.ButtonLeftClick(Sender: TObject); begin if imagenr>0 then begin image[imagenr].Visible:=false; imagenr:=Imagenr-1; image[imagenr].Visible:=true; Edit_slice.Text:=inttoStr(imagenr+1); end; end;

Appendix

Page 75 of 75

The procedure loads to the selected PID the available studies. Those can be

selected as well as the description after choosing a study.

procedure TForm_patient.FormShow(Sender: TObject); begin Form_patient.Height:=140; Image1.Visible:=false; Label4.Visible:=false; Edit_slice.Visible:=false; ButtonLeft.Visible:=false; ButtonRight.Visible:=false; Label_patient.Caption:=patid; v_sql:='SELECT btap_study.description FROM btap_study where pid="'+patid+'"'; ZQuery_study.SQL.Append(v_sql); ZQuery_study.Open; ZQuery_study.Active:=true; for row := 0 to ZQuery_study.RecordCount-1 do begin descr:=ZQuery_study.FieldByName('description').AsString; ComboBox_study.Items.Add(descr); ZQuery_study.Next; end; end;

university of applied sciences ulm · university of applied sciences ulm the undersigned certify...

Documents