Extracting Oracle Calendar Designate Data

We went ahead and did it: We're extracting Oracle Calendar Server Designate data, feeding it into our intermediate database, and outputting it to automatically set up Sharing Roles in Zimbra (just because it was relatively easy and we have a motivated real-world test client down the street).

Microsoft Exchange users - we'll see (side note: you have to be migrating to Exchange 2007 sp1 -- we are not inserting Delegates into Exchange 2003 ever again).

So first issue: How to extract the data.

We use two batch files to create a file out of OCS that we can manipulate easily.

The first is called EXPORT-DES.BAT and looks like this:

call uar "S=Garcia/G=Jerry"

call uar "S=Liberace/G=Walter"

call uar "S=Lennon/G=John"

call uar "S=Amiumi/G=Puffy"

call uar "S=Page/G=Jimmy"

call uar "R=CR Mozart"

call uar "R=Shea Stadium"

All it is is a list of users and resources which are being passed to a second batch file UAR.BAT which does the hard work. Can this be further scripted? Sure -- but we're aiming for simplicity and clarity here so we'll leave further development as an exercise for the motivated migrator.

UAR.BAT is simply executing UNIACCESSRIGHTS on OCS and dropping everything into a file we can read.

UAR.BAT looks like this:

echo %1 >> des-ALL.txt

uniaccessrights -ls -grantor %1 -grantee "S=*" -n 1 -p PASSWORD >>des-ALL.txt

echo enduser >>des-ALL.txt

echo "" >>des-ALL.txt

This produces des-ALL.TXT which looks like this:

"S=Garcia/G=Jerry"
Grantee: S=Lennon/G=John/UID=John.Lennon/ID=257/NODE-ID=1

Designate Right: CONFIDENTIALEVENT=VIEWTIME/CONFIDENTIALTASK=MODIFY/NORMALEVENT=MODIFY/NORMALTASK=MODIFY/PERSONALEVENT=REPLY/PERSONALTASK=MODIFY/PUBLICEVENT=NONE/PUBLICTASK=MODIFYEvent Viewing Right: CONFIDENTIAL=ALL/NORMAL=ALL/PERSONAL=ALL

Grantee: S=Liberace/G=Walter/UID=Walter.Liberace/ID=260/NODE-ID=1Event

Viewing Right: CONFIDENTIAL=NONE/NORMAL=ALL/PERSONAL=TIME

Grantee: S=Page/G=Jimmy/UID=Jimmy.Page/ID=262/NODE-ID=1

Designate Right:CONFIDENTIALEVENT=MODIFY/CONFIDENTIALTASK=MODIFY/NORMALEVENT=MODIFY/NORMALTASK=MODIFY/PERSONALEVENT=MODIFY/PERSONALTASK=MODIFY/PUBLICEVENT=MODIFY/PUBLICTASK=MODIFY

Grantee: Everyone

Default Event Viewing Right: CONFIDENTIAL=ALL/NORMAL=ALL/PERSONAL=ALLDefault

Task Viewing Right: CONFIDENTIAL=ALL/NORMAL=ALL/PERSONAL=ALL

Default Scheduling Right: CANBOOKME=TRUE
enduser

""

"S=Liberace/G=Walter"
Grantee: Everyone

Default Event Viewing Right: CONFIDENTIAL=TIME/NORMAL=TIME/PERSONAL=TIME

Default Task Viewing Right: CONFIDENTIAL=NONE/NORMAL=NONE/PERSONAL=NONEDefault Scheduling Right: CANBOOKME=TRUE
enduser ""

If you think this looks like gobbly-gook you should see some of the other data formats we've had to read over the years.

How's this get put into the intermediate database?

Via the new options on OraCalReader.exe (which we will explain in the next few days).