Migrating Commonspot Sites Paul Houle Peter Hoyt Library

Migrating Commonspot Sites Paul Houle Peter Hoyt Library Systems Thanks: James Reidy, Surinder Ghangas

Context • Support 21 unit libraries and many projects • Copia, our primary web server has > 50 virtual hosts • Commonspot sites up and running: • Transition to Fatherhood • Public Services Committee • Under development: • Mann Library • ASTEC (Engineering, Math & Physical Sciences and Vet) • NY Aerial Photos

Hardware Sun Fire V 440 Solaris 9 fibre channel Stor. Edge 6130 Array

Software • Solaris 9 • Oracle 9. 2. 0. 6 • Cold Fusion MX 6. 1 Enterprise Edition (Jrun 4) • Apache 2 • Common. Spot 4. 5 sp 2 • Cornell. Custom. Auth. Z

Two Instances SPOTTEST http port 80 http port X /spot/ apache /spottest/ apache spot jrun spottest jrun SPOT oracle SPOTTEST oracle

Database Structure SPOT SITES USERS SPOTTEST SITES USERS SITE A

The problem… SPOTTEST. users SPOTTEST. pages

Stages 1. Find missing users in production system 2. Add missing users to production system 3. Create mapping of test -> production user ids 4. Get database schema dump 5. Extract list of user id fields 6. Update user ids

Schema Dump CREATE TABLE "PSA_SITE". "APPROVAL" ( "PAGEID" NUMBER(12, 0) NOT NULL ENABLE, "CONTROLID" NUMBER(12, 0) NOT NULL ENABLE, "ITEMID" NUMBER(12, 0) DEFAULT 0, "USERGROUPID" NUMBER(12, 0) NOT NULL ENABLE, "APPROVALTABLELIST" NVARCHAR 2(255) NOT NULL ENABLE, "STATUS" NUMBER(12, 0) DEFAULT 0, "APPROVALDATE" VARCHAR 2(20), "APPROVALLEVEL" NUMBER(3, 0) DEFAULT 0, "APPROVEDBY" NUMBER(12, 0) DEFAULT 0, CONSTRAINT "PK__APPROVAL" PRIMARY KEY ("PAGEID", "CONTROLID", "ITEMID", "USERGROUPID", "APPROVALTABLELIST") )

Heuristics I KEEP COLUMNS STOP COLUMNS DROP TABLES AUTHOR ATTRIBORDER AVAILABLECONTROLS CHANGEBY CATEGOR CREATOR CONTROLID FORMAT DELETEDBY CHARID LANGUAGE GROUP CONTROLLIST SITEPAGES OWNER ELEMENTID SYSTEM REQUEST ITEM TRANSACT USER MASK PAGE QUERYID SORTORDER TYPE VERSION HEIGHT

Heuristics II • Group ids start with 1, 000 • Look at values to see if a column contains user id’s • Is it numeric? • Do the numbers match users and groups? • LIST columns, ex. *. ITEMUSERGROUPLIST • Format: “ 7, 12, 55”

Commonspot Software Configuration: James Reidy (jgr 25@cornell. edu) Apache, Jrun, CFMX, SAMBA: Paul Houle (ph 18@cornell. edu) Oracle, Backups, Unix admin: Surinder Ghangas (sg 14@cornell. edu) General Questions: Oya Rieger (oyr 1@cornell. edu) http: //commonspot. library. cornell. edu/