/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE
  e_tab_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_tab_exists, -00955);
  e_col_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_col_exists, -01430);
BEGIN
   /* Block 1 - Create Table */

   EXECUTE IMMEDIATE 'CREATE TABLE XXAS_ICAL_EVENTS
    (component    varchar2(30)   NOT NULL
    ,uid_key 	varchar2(30)   NOT NULL
    ,change_type  varchar2(30)   NOT NULL
    ,logged       date           NOT NULL
    ,from_email   varchar2(255)  NOT NULL
    ,proxy_email  varchar2(255)
    ,to_email     varchar2(255)  NOT NULL
    ,subject      varchar2(400)  NOT NULL
    ,html_body    varchar2(4000)
    ,date_start   date           NOT NULL
    ,date_end     date
    ,timezone     varchar2(30)
    ,duration     varchar2(30)
    ,free_busy    varchar2(30)
    ,location     varchar2(255)
    ,tentative    varchar2(30)
    ,privacy      varchar2(30)
    ,priority     number(15)
    ,misc_content varchar2(4000)
    ,attempts     number(15)
    ,last_attempt date
    ,error_text   varchar2(4000))';

    EXECUTE IMMEDIATE
      'GRANT ALL ON  XXAS_ICAL_EVENTS TO &1 WITH GRANT OPTION';

EXCEPTION
  WHEN e_tab_exists THEN NULL;
END;
/

DECLARE
  e_col_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_col_exists, -01430);
BEGIN
   /* Block 2 - create column */
  EXECUTE IMMEDIATE 
       'ALTER TABLE XXAS_ICAL_EVENTS ADD RSVP VARCHAR2(30)';
EXCEPTION
  WHEN e_col_exists THEN NULL;
END;
/

COMMIT; 
EXIT;
