BACKGROUND
We have an Oracle 10g installation. There seems to be one important
application implemented in Oracle; a kind of Content Management System
to control information for eventual publication on N or more different
web sites.

Information professionals arrange materials into a hierarchy, add
descriptive metadata (The sort of information one might find in Library
Catalogs), declare the material "ready for prime time" and assign
materials to the different web sites. They also control the production
of images (digital pictures mostly) from the "real world" objects; but
Oracle does not store the actual images themselves.

Some of the materials are "born digital" and dont need digitization
control.

Data entry (descriptive metadata and arrangement) is entered into the
Oracle schema holding the actual data via a web front end written in
pl/sql using Apache and the pl/sql gateway. THe application is in a
separate schema from the production data but has "rights" to read the
tables and to execute packaged procedures living in the production
schema for insert/update etc.


Independant applications running JVM's "Extract" the data by making SQL
calls into Oracle, format the data into XML files, and also index the
information with Lucene.

The XML files and the Lucene indexes are sent off to web farms.

The actual bit streams (pictures, sound files, etc) that are the
"content" are stored outside of the Oracle system in a set of "image"
servers, where "image" can be taken to be a visual image, a sound file,
or any sort of media file.

The actual production data exclusive of indexes and other support
structures isnt more than 2 or 3 GBytes worth.

The pl/sql code to implement the data entry application is maybe 60
KSLOC of pl/sql.

As a practical matter there are never more than a small handful of (say
half a dozen tops?) simultatneous data entry operators simultaneously
active.

Adding everthing togather there's less than 4 or 5 GBytes of data and
70KSLOC of pl/sql code in the whole ORacle portion system.

The heavy lifting, 24x7 web availability to consumers around the globe,
is borne by the web farms and the image delivery mechanis, all outside
of and only loosely coupled to Oracle through the extraction system.

I have no idea what the Oracle license fees are like. The only possibly
relevant fact being that we are a non profit public institution.

THE QUESTION (at last)
The institution now favors using open source products wherever it makes
sense to do so. The decision to go Oracle for this particular project
was taken in or around the year 2000.

There are many more choices now and some options, such as mySql, which
were "weak tea" then, have since become "contenders".

The situation is complicated by lack of information on my part. I am
given to believe that some departments plan to acquire XML database
tools, but I dont know which ones.

I can imagine that storing, retreiving, parsing, querying and
ransforming XML documents will be increasingly important in the future.

With Oracle,we have XML DB, the xml data type and a whole panoply of XML
integration tools. If we were to dump Oracle, and go completely "open
source", (lets say that means mySql , but that's not certain either)
I'm not sure how , if at all, we could recreate that easy access between
the RDBMS and the XML incarnations of the information.

Does it make sense to port the application to "something" else.

If so why so, if not why not?

What options does the group think are viable?

How would we recreate the beauty of the XNL DB features in mySql or
berkely DB or INNODB or whatever?

Lee wrote:

Since the majority of your application appears to be embedded with
PL/SQL - recreating that business logic in PHP or some other tool as
MySQL (IMPO) is still very week in that area. Keep in mind that MySQL is
not free for commercial use. (Why do you think SUN bought it?) Any code
that uses the "free" version must be under GPL and made publicly
available (See the ToC for further explanations). I know of a couple of
places where they spent more than if they had used Oracle. As for Oracle
licensing, only you and your sales rep know what you paid for it. (like
negotiating with a used car salesman - sometimes you "feel" like you got
a good deal - sometimes you don't :-) )

Support - the ability to log a call and get immediate (well sometime
today anyway...) help. I logged a bug with MySQL and it took almost 2
years to get a work-around *not really a fix* in place.

If you could keep your total user data size to < 4GB you could go with
Oracle XE (Windows/Linux only, as is, no support, no patches, < 4GB user
data, but Free). Maybe this could be used for development to reduce
license fees (see licensing above).

To "recreate the beauty..." you would rewrite everything... is it worth it?

Michael Austin wrote:
Somewhere on the web there are estimates of how many LOC in language A
are equivalent to how many in language B.

PL/SQL is a big plus and I would not enjoy losing it, BUT:

Many of those 70K SLOC are in fact just pushing out the forms and are
full of htp.p( 'blah blan') and such. PHP or soemthing for that part of
the application would not be so bad.

There's no question that doing the processing in mySql will be longer,
harder, and uglier. For one thing, the application has a fairly large
table organized as a recursive tree (Each record has the FK of its
parent). We'ld need to completely rething all that because there's no
"Connect By" in mySql.

Before version 5 and before the availability of the berkely DB engine or
the (oracle owned and supported) innoDB engine, I would say mySql is
just out of the picture. But now ..... ?





Keep in mind that MySQL is
it youself, but have all the world as your helper, be able to share it
with others, have total ability to control the internals, ..... sounds
good the them.

Lee wrote:
I guess this is the wrong group to ask if you want to move away from Oracle.

The more nonstandard Oracle-specific features you use, the harder it gets.
The more similar your targeted DBMS is, the easier it gets.

I'd consider PostgreSQL, because a) there is a stored procedure language
not too different from Oracle's, b) it is the best open source database around,
and c) it does not belong to one company.

Yours,
Laurenz Albe

Laurenz Albe wrote:

Unfortunately PostgreSQL has no "Connect By", no XML tools and plpgsql
isn't the same ballpark as PL/SQL (it isn't even the same sport )

I will stay with Oracle if possible.

Y.

On Mar 15, 10:09*pm, Lee <L... (AT) JamToday (DOT) com> wrote:
It is all a cost versus features trade off. Boiled down to simplest
terms:

Assume there is equivalent functionality in the open source to match
Oracle or that the equivalent can be built.
(After all, if it cannot work, then there is no sense in converting.)

Consider the costs -

ORACLE DBMS
development costs W = $0 (they are already paid)
training costs V = $/new developer/year (how stable is the
IT staff?)
license costs U = $/year (can they get some charity
discount?)
Maintenance costs T = $/year (bug fixed and enhancements)
Support costs S = $/year

Total Annual Oracle Cost C = V+U+T

Open Source DBMS - say MySQL
development costs M = $ (one time conversion)
training costs N = $/new developer/year
license costs P = $/year (can they get some charity
discount?)
Maintenance costs R = $/year (bug fixed and enhancements)
Support costs Q = $/year

Total Annual MySQL Cost D = N+P+R+Q

The per year cost difference is C - D
The key question is how many years will it take to save the costs of
conversion?
Assume W is $0 (the current system has been in place long enough that
initial implementation costs has been amortized).

So
M = Y * (C-D) or Y = M/(C-D)
where Y=number of years for payoff.

making those estimates will be tricky. You might consider some of the
individual costs.

Will support costs for Open source Q be less than Oracle support S.
Note that Q will be a variable cost while S is fairly fixed. It's like
the difference between annual insurance costs and being self
insured.When the accident happens, will you have the funds to pay it?
It may be possible to get support for open source, but will it be less
than Oracle?

During the years for payoff, will the maintenance costs (R open source
versus T Oracle) be higher because your staff is less familiar with
the new software? that could stretch out the payoff period.

Training costs are likely to be MUCH higher for the first year or so
of the new system. Suppose the costs are identical per developer. but
you have to train the entire staff on the new system. just for
comparison, assume your staff has a turnover or one developer per
year.
Oracle training cost you: 1*N
you have a staff of J number of developers.
Your first year training costs then will be: J*N
Maybe you bury that cost in the conversion costs (M)


Just some thoughts to consider.

Ed

(disclaimer: I am part of a company that does hosting)
And finally one other idea to consider:
outsource the application.
The CMS can be used remotely, the total costs might be less (again
have to do the estimation work). The reason is the oracle licensing
will be amortized over more applications by the hosting company. you
can still do the enhancements. You might have some reduced work
(backups and such) Contact me if that is something you would like to
consider.
Ed Prochak
Magic Interface, Ltd.
http://www.magicinterface.com
ed prochak at magic interface dot com