 |
|
EnterpriseDB: TimeTraker
Oracle Tips by
Burleson
|
The
purpose of this chapter is to bring together the topics in previous
chapters and provide the migration of a complete application from
Oracle 10g to EnterpriseDB Advanced Server 8.1.
This
chapter will introduce TimeTraker, a time entry system. The goal is
to provide a non-trivial example while trying to not make the example
overwhelming. This application could have easily been an order entry
system, a simple HR system or one of many other applications that are
often used as examples. I hope this example will introduce a new kink
into the example universe (the exampleverse?).
TimeTraker accepts input from any system that can call a stored
procedure or that can produce an XML document. TimeTraker is a pretty
standard time tracking application in that it tracks resources –
that's people; schedules, projects, tasks, etc and in that it provides
a set of reports. Reports are generated in both formatted output
(text) and in Comma Separated Values (CSV) for loading into a
spreadsheet program.
After
introducing TimeTraker and describing the architecture, I will take a
brief walk through the code. I will introduce all of the major
components and provide some description of how they work.
I will
then actually generate some data, get it into the system and provide
reports for comparing with the later goal of migration. Obviously,
the goal is migration but I want to explain how the system works so
that I can do a step-by-step post migration walk through.
I am
purposely creating the application so that it forces me to make some
choices during the migration. If I were to just accept all of the
defaults of the Migration Toolset (Chapter 7), there would not be much
purpose to an additional chapter.
The
application will require schema changes as well as code changes. I am
purposely not including a front end to the application. With the
plethora of languages and tools that can access EnterpriseDB, there is
no way I could do justice to that particular topic.
If you
are writing your applications using standard practices (data
encapsulation via stored procedures) and standard database interfaces
(JDBC, ODBC, etc), your front end should require minimal changes when
porting. If you are hard-coding database specific code in your
front-end, you may be required to make additional front-end changes.
A word
of warning: This chapter will cover many topics and a lot of code. If
you are not prepared to have an Oracle database and an EnterpriseDB
database running while you follow along, you may get lost. This is
probably the most complex chapter in this book.
Requirements/Architecture
In this
section, I will outline the requirements for the TimeTraker
application. The main requirements for this application are to
present a real world application that is not so complex as to be
useless but that is still complex enough to provide a working example
of migrating an application.
TimeTraker is written for Oracle 10g. TimeTraker will compile on any
10g edition, including Enterprise, Standard, Standard Edition One and
Express.
TimeTraker is a simple system for tracking hours worked on tasks.
Tasks can be shared between projects and resources provide hours by
task, not by project.
Security
in TimeTraker will be based on user roles. An end-user may be an
administrator, project manager or user. Users can only add hours
worked to projects and run reports. Project managers can add
resources, projects and tasks. Administrators can change calendars,
schedules and reports.
A
typical flow of a project may look like the following graphic (Figure
9.1):
Figure
9.1: TimeTraker Flow
A
project manager must be able to create a project, create tasks for a
project and associate those tasks to a project. A project manager
must also be able to assign resources to work on tasks.
Developers/users must be able to enter hours worked on tasks.
Everyone
must be able to run reports. Five reports will be available:
* Hours
Worked By Resource
*
Resources Assigned to Projects
*
Projects and Tasks
*
Project Hours Remaining By Project
*
Project Hours Remaining By Resource
All five
reports produce a text file suitable for display and a CSV file
suitable for loading and manipulating
via a spreadsheet
TimeTraker allows input directly or via an XML document. Input will
normally be via a GUI application. The primary interface between the
GUI and the database will be stored procedures.
TimeTraker will also provide a calendar and resource schedule lookup
for front-end GUI use. Maintenance routines must be written for these
lookups.
As far
as requirements go, we now have input and output requirements as well
as security. A system any larger than this would require a book of
its own so I will stop here for requirements. The next step is to
build a data model.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
|
