## This is a schema for MySQL for the calendar objects in Jetspeed
##
## This schema creates the tables needed for the calendar objects and
## initializes them.
##
## This schema is imported into MySQL with the folowing command
## mysql Turbine < MySQL_calendar_v1.0.sql

drop table if exists JETSPEED_VEVENT_OBJECTS;

create table JETSPEED_VEVENT_OBJECTS (
       VEVENT_ID integer AUTO_INCREMENT PRIMARY KEY,
       OWNER integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       CLASSIFICATION varchar(15),
       DATE_CREATED datetime,
       DESCRIPTION text,
       DTSTART_DATE_TIME datetime,
       DTSTART_DATE date,
       GEO_LATITUDE float,
       GEO_LONGITUDE float,
       LAST_MODIFIED datetime,
       LOCATION text,
       ORGANIZER varchar(80),
       PRIORITY integer,
       DTSTAMP datetime,
       SEQUENCE integer,
       STATUS varchar(15),
       SUMMARY varchar(80),
       TRANSPARENCY varchar(14),
       UNIQUE_ID varchar(100),
       URL varchar(80),
       RECUR_ID_DT datetime,
       RECUR_ID_D date,
       DTEND_DT datetime,
       DTEND_D date,
       DURATION integer,
       DESC_ALT_REP varchar(80),
       DESC_LANG varchar(10),
       LOCATION_ALT_REP varchar(80),
       LOCATION_LANG varchar(10),
       DTSTART_TZID varchar(80),
       ORGANIZER_NAME varchar(80),
       ORGANIZER_DIR varchar(80),
       ORGANIZER_SENTBY varchar(80),
       ORGANIZER_LANG varchar(10),
       SUMMARY_ALT_REP varchar(80),
       SUMMARY_LANG varchar(10),
       RECUR_ID_TZID varchar(80),
       RECUR_ID_RANGE varchar(16),
       DTEND_TZID varchar(80));
       
drop table if exists JETSPEED_VTODO_OBJECTS;

create table JETSPEED_VTODO_OBJECTS (
       VEVENT_ID integer AUTO_INCREMENT PRIMARY KEY,
       OWNER integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       CLASSIFICATION varchar(15),
       DATE_CREATED datetime,
       DESCRIPTION text,
       DTSTART_DATE_TIME datetime,
       DTSTART_DATE date,
       GEO_LATITUDE float,
       GEO_LONGITUDE float,
       LAST_MODIFIED datetime,
       LOCATION text,
       ORGANIZER varchar(80),
       PRIORITY integer,
       DTSTAMP datetime,
       SEQUENCE integer,
       STATUS varchar(15),
       SUMMARY varchar(80),
       UNIQUE_ID varchar(100),
       URL varchar(80),
       RECUR_ID_DT datetime,
       RECUR_ID_D date,
       DUE_DATE_TIME datetime,
       DUE_DATE date,
       DURATION integer,
       DESC_ALT_REP varchar(80),
       DESC_LANG varchar(10),
       LOCATION_ALT_REP varchar(80),
       LOCATION_LANG varchar(10),
       DTSTART_TZID varchar(80),
       ORGANIZER_NAME varchar(80),
       ORGANIZER_DIR varchar(80),
       ORGANIZER_SENTBY varchar(80),
       ORGANIZER_LANG varchar(10),
       SUMMARY_ALT_REP varchar(80),
       SUMMARY_LANG varchar(10),
       RECUR_ID_TZID varchar(80),
       RECUR_ID_RANGE varchar(16),
       DUE_TZID varchar(80),
       PERCENT integer,
       DATE_COMPLETED datetime
       );

drop table if exists JETSPEED_ATTENDEE_OBJECTS;

create table JETSPEED_ATTENDEE_OBJECTS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       LANGUAGE varchar(3),
       COMMON_NAME varchar(50),
       DIRECTORY varchar(80),
       SENTBY varchar(80),
       ATTENDEE varchar(80),
       PARTICIPATION varchar(14),
       ROLE varchar(16),
       RSVP varchar(7),
       USER_TYPE varchar(11));

drop table if exists JETSPEED_CATEGORY_OBJECTS;

create table JETSPEED_CATEGORY_OBJECTS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       LANGUAGE varchar(3),
       CATEGORY varchar(50));

drop table if exists JETSPEED_RESOURCE_OBJECTS;

create table JETSPEED_RESOURCE_OBJECTS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       LANGUAGE varchar(3),
       RESOURCE varchar(50));
       
drop table if exists JETSPEED_COMMENT_OBJECTS;

create table JETSPEED_COMMENT_OBJECTS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       LANGUAGE varchar(3),
       ALT_REP varchar(80),
       COMMENT text);

drop table if exists JETSPEED_CONTACT_OBJECTS;

create table JETSPEED_CONTACT_OBJECTS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       LANGUAGE varchar(3),
       ALT_REP varchar(80),
       CONTACT text);
       
drop table if exists JETSPEED_EXDATE_OBJECTS;

create table JETSPEED_EXDATE_OBJECTS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       VALUE_PARAM varchar(11),
       DATE_TIME_VALUE datetime,
       DATE_VALUE date,
       TIME_ZONE_ID varchar(80));
       
      
drop table if exists JETSPEED_RDATE_OBJECTS;

create table JETSPEED_RDATE_OBJECTS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       VALUE_PARAM varchar(11),
       DATE_TIME_VALUE datetime,
       DATE_VALUE date,
       TIME_ZONE_ID varchar(80));

drop table if exists JETSPEED_RECUR_RULES;

create table JETSPEED_RECUR_RULES (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       UNTIL_DATE_TIME datetime,
       UNTIL_DATE date,
       COUNT_VALUE integer,
       INTERVAL_COLUMN integer,
       BY_SECOND text,
       BY_MINUTE text,
       BY_HOUR text,
       BY_DAY text,
       BY_MONTHDAY text,
       BY_YEARDAY text,
       BY_WEEKNO text,
       BY_MONTH text,
       BY_SETPOS text,
       WEEKDAY text,
       FREQ varchar(10));
       
drop table if exists JETSPEED_EX_RULES;       
       
create table JETSPEED_EX_RULES (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       UNTIL_DATE_TIME datetime,
       UNTIL_DATE date,
       COUNT_VALUE integer,
       INTERVAL_COLUMN integer,
       BY_SECOND text,
       BY_MINUTE text,
       BY_HOUR text,
       BY_DAY text,
       BY_MONTHDAY text,
       BY_YEARDAY text,
       BY_WEEKNO text,
       BY_MONTH text,
       BY_SETPOS text,
       WEEKDAY text,
       FREQ varchar(10));

drop table if exists JETSPEED_REQUEST_STATUS;

create table JETSPEED_REQUEST_STATUS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       LANGUAGE varchar(3),
       STATUS_CODE varchar(12),
       STATUS_DESC text,
       EXCEPTION_DATA text);

drop table if exists JETSPEED_RELATED_PROPS;

create table JETSPEED_RELATED_PROPS (
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       REL_TYPE varchar(9),
       RELATED_PROP text);

drop table if exists JETSPEED_XPROP_OBJECTS;

create table JETSPEED_XPROP_OBJECTS(
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       LANGUAGE varchar(3),
       XPROP text,
       XPROP_NAME varchar(80));

drop table if exists JETSPEED_DELEGATEE_OBJECTS;

create table JETSPEED_DELEGATEE_OBJECTS(
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       DELEGATEE varchar(80));
       
drop table if exists JETSPEED_DELEGATOR_OBJECTS;

create table JETSPEED_DELEGATOR_OBJECTS(
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       DELEGATOR varchar(80));

drop table if exists JETSPEED_MEMBER_OBJECTS;

create table JETSPEED_MEMBER_OBJECTS(
       DATABASE_ID integer PRIMARY KEY,
       OWNER_ID integer,
       READ_PERMISSION integer,
       WRITE_PERMISSION integer,
       PARENTS_TABLE varchar(80),
       PARENTS_ID integer,
       MEMBER varchar(80));

delete from ID_TABLE where ID_TABLE_ID = 5;
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (5, 'JETSPEED_ATTENDEE_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 6;       
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (6, 'JETSPEED_CATEGORY_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 7;       
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (7, 'JETSPEED_COMMENT_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 8;
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (8, 'JETSPEED_CONTACT_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 9;       
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (9, 'JETSPEED_EXDATE_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 10;       
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (10, 'JETSPEED_RDATE_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 11;       
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (11, 'JETSPEED_RECUR_RULES', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 12;       
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (12, 'JETSPEED_EX_RULES', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 13;
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (13, 'JETSPEED_REQUEST_STATUS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 14;
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (14, 'JETSPEED_RELATED_PROPS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 15;
insert into ID_TABLE(ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (15, 'JETSPEED_XPROP_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 16;
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (16, 'JETSPEED_RESOURCE_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 17;
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (17, 'JETSPEED_DELEGATEE_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 18;
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (18, 'JETSPEED_DELEGATOR_OBJECTS', 1, 1);

delete from ID_TABLE where ID_TABLE_ID = 19;
insert into ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) values
       (19, 'JETSPEED_MEMBER_OBJECTS', 1, 1);
       
       
