-- SQL to resize personalisation tables without losing existing data
-- M. Hinds, 23/10/2000

-- create duplicate tables in which to preserve data

CREATE TABLE OC_CONTENT_ITEMS2
(   ITEM_ID            NUMBER(38, 0) NOT NULL,
    ITEM_NAME          VARCHAR2(50),
    ITEM_BODY          CLOB,
    ITEM_GRAPHIC_URL   VARCHAR2(400),
    ITEM_LINK_URL      VARCHAR2(400),
    MORE_IMAGE_URL     varchar2(400),
    MORE_IMAGE_URL_FOCUS varchar2(400),
    MERCHANDISING      VARCHAR2(1),
    PRIMARY KEY (ITEM_ID))
TABLESPACE acs_other_tables
  PCTFREE 10
  PCTUSED 50
  INITRANS 1
  STORAGE(INITIAL 1200K NEXT 120K
    MINEXTENTS 1 MAXEXTENTS 249
  PCTINCREASE 0
)  ENABLE PRIMARY KEY USING INDEX
     TABLESPACE acs_other_indexes;


CREATE TABLE OC_CONTENT_ITEMS_SCHEDULE2
(   ITEM_ID            NUMBER(38, 0) NOT NULL,
    SCHEDULE_ID        NUMBER(38, 0) NOT NULL,
    AOL_SCHEDULE_ID    NUMBER(38, 0),
    UPLOAD_FILE        VARCHAR2(100),
    UPLOAD_TYPE        NUMBER(38, 0),
    INTERVAL_DAY       NUMBER(1, 0),
    INTERVAL_HOUR      NUMBER(2, 0),
    INTERVAL_MINUTE    NUMBER(2, 0),
    LAST_MODIFIED      DATE,
    MODIFIED_IP_ADDRESS VARCHAR2(50),
    LAST_MODIFYING_USER NUMBER(38, 0),
    PRIMARY KEY (ITEM_ID, SCHEDULE_ID),
    FOREIGN KEY (ITEM_ID)
        REFERENCES OC_CONTENT_ITEMS2,
    FOREIGN KEY (LAST_MODIFYING_USER)
        REFERENCES USERS(USER_ID))
TABLESPACE acs_other_tables
  PCTFREE 10
  PCTUSED 50
  INITRANS 1
  STORAGE(INITIAL 64K NEXT 8K
    MINEXTENTS 1 MAXEXTENTS 249
  PCTINCREASE 0
)  ENABLE PRIMARY KEY USING INDEX
     TABLESPACE acs_other_indexes;


CREATE TABLE OC_USER_OPTIONS2
(   USER_ID            NUMBER(38, 0) NOT NULL,
    GROUP_ID           NUMBER(38, 0) NOT NULL,
    REGISTRATION_DATE  DATE NOT NULL,
    DEFAULT_HOMEPAGE   VARCHAR2(1),
    COMMENTS           VARCHAR2(500),
    LAST_MODIFIED      DATE,
    MODIFIED_IP_ADDRESS VARCHAR2(50),
    LAST_MODIFYING_USER NUMBER(38, 0),
    PRIMARY KEY (USER_ID, GROUP_ID),
    FOREIGN KEY (USER_ID)
        REFERENCES USERS(USER_ID),
    FOREIGN KEY (GROUP_ID)
        REFERENCES USER_GROUPS(GROUP_ID),
    FOREIGN KEY (LAST_MODIFYING_USER)
        REFERENCES USERS(USER_ID))
TABLESPACE acs_other_tables
  PCTFREE 10
  PCTUSED 50
  INITRANS 1
  STORAGE(INITIAL 400M NEXT 40M
    MINEXTENTS 1 MAXEXTENTS 249
  PCTINCREASE 0
)  ENABLE PRIMARY KEY USING INDEX
     TABLESPACE acs_other_indexes;


CREATE TABLE OC_USER_ITEMS2
(   USER_ID            NUMBER(38, 0) NOT NULL,
    GROUP_ID           NUMBER(38, 0) NOT NULL,
    ITEM_ID            NUMBER(38, 0) NOT NULL,
    SORT_KEY           NUMBER(38, 0),
    LAST_MODIFIED      DATE,
    MODIFIED_IP_ADDRESS VARCHAR2(50),
    LAST_MODIFYING_USER NUMBER(38, 0),
    PRIMARY KEY (USER_ID, GROUP_ID, ITEM_ID),
    FOREIGN KEY (USER_ID, GROUP_ID)
        REFERENCES OC_USER_OPTIONS2,
    FOREIGN KEY (ITEM_ID)
        REFERENCES OC_CONTENT_ITEMS2,
    FOREIGN KEY (LAST_MODIFYING_USER)
        REFERENCES USERS(USER_ID))
TABLESPACE acs_other_tables
  PCTFREE 10
  PCTUSED 50
  INITRANS 1
  STORAGE(INITIAL 90M NEXT 9M
    MINEXTENTS 1 MAXEXTENTS 249
  PCTINCREASE 0
)  ENABLE PRIMARY KEY USING INDEX
     TABLESPACE acs_other_indexes;

-- copy the data across !!WARNING!! item_body is not included because it's a BLOB and they're fiddly - see below

INSERT INTO OC_CONTENT_ITEMS2 SELECT * FROM OC_CONTENT_ITEMS;
INSERT INTO OC_CONTENT_ITEMS_SCHEDULE2 SELECT * FROM OC_CONTENT_ITEMS_SCHEDULE;
INSERT INTO OC_USER_OPTIONS2 SELECT * FROM OC_USER_OPTIONS;
INSERT INTO OC_USER_ITEMS2 SELECT * FROM OC_USER_ITEMS;
COMMIT;

-- confirm that the duplicates match the originals

SELECT COUNT(*) FROM OC_USER_ITEMS;
SELECT COUNT(*) FROM OC_USER_ITEMS2;
SELECT COUNT(*) FROM OC_CONTENT_ITEMS;
SELECT COUNT(*) FROM OC_CONTENT_ITEMS2;
SELECT COUNT(*) FROM OC_CONTENT_ITEMS_SCHEDULE;
SELECT COUNT(*) FROM OC_CONTENT_ITEMS_SCHEDULE2;
SELECT COUNT(*) FROM OC_USER_OPTIONS;
SELECT COUNT(*) FROM OC_USER_OPTIONS2;


-------------------------------------------------------------------------
-- NOW RUN THE TCL PROGRAM TO COPY THE BLOBS IN OC_CONTENT_ITEMS ! ! ! --

/* stick this code into a .tcl file and run it on AOLserver

set db [ns_db gethandle]
set selection [ns_db select $db "select item_id, item_body from oc_content_items"]

ReturnHeaders

set ids [list]
set bodies [list]

while {[ns_db getrow $db $selection]} {
  set_variables_after_query
  lappend ids $item_id
  lappend bodies $item_body
}

set errmsg {}

for {set i 0} {$i<[llength $ids]} {incr i} {

  if {[catch {ns_ora clob_dml $db "update oc_content_items2
  set item_body = empty_clob()
  where item_id = $item_id
  returning item_body
  into :one" [lindex $bodies $i]} errmsg]} {
    ns_write "Oracle error: $errmsg<p>"
  } else {
    ns_write "OK<p>"
  }
}
*/

-- delete the originals

drop TABLE OC_USER_ITEMS;
drop TABLE OC_CONTENT_ITEMS_SCHEDULE;
drop TABLE OC_USER_OPTIONS;
drop TABLE OC_CONTENT_ITEMS;

-- rename the duplicates to the original names

RENAME OC_USER_ITEMS2 TO OC_USER_ITEMS;
RENAME OC_CONTENT_ITEMS_SCHEDULE2 TO OC_CONTENT_ITEMS_SCHEDULE;
RENAME OC_USER_OPTIONS2 TO OC_USER_OPTIONS;
RENAME OC_CONTENT_ITEMS2 TO OC_CONTENT_ITEMS;