Oracle Tips by
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.
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
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.
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.
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.
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.
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.
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.
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
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.
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.
typical flow of a project may look like the following graphic (Figure
9.1: TimeTraker Flow
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.
must be able to run reports. Five reports will be available:
Worked By Resource
Resources Assigned to Projects
Projects and Tasks
Project Hours Remaining By Project
Project Hours Remaining By Resource
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
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.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.