rem Create tables for Oracle


create sequence epc_collection_id increment by 1 start with 1;

create table epc_collection
       (
	collection_id number(4) CONSTRAINT epc_pk_coll_rec PRIMARY KEY, 
	collection_name varchar2(255),
	start_time date,
	end_time date,
	scheduled_by	varchar2(8),
	ms_granularity number);

create table epc_process
	(collection_id number(4) CONSTRAINT fk_proc_id REFERENCES
		       epc_collection (collection_id) on delete cascade,
	epid number(9) ,
	username varchar2(8),
	cpu_type varchar2(32),
	os_ver  varchar2(32),
	nodename varchar2(64),
	time	 date,
	time_nano number,
	end_time date,
	end_time_nano number);

create table epc_facility
(   
  CONSTRAINT epc_pk_fac_num PRIMARY KEY (vendor, facility_number, 
					 facility_version, class_name),
  vendor number(9),
  facility_number number(4), 
  facility_name varchar2(20),
  facility_version varchar2(11),
  class_name varchar2(20));


create table epc_event
(   
  CONSTRAINT epc_pk_eve_rec PRIMARY KEY (vendor, facility_number, 
					 facility_version, event_number),
  vendor number(9),
  facility_number number(4), 
  facility_version varchar2(11),
  event_number number (4),
  event_name varchar2(16),
  event_header varchar2(16),
  event_table_name varchar2(30));

create table epc_item
(   
  CONSTRAINT epc_pk_ite_rec PRIMARY KEY (vendor, facility_number, 
					 facility_version, item_number),
  vendor number(9),
  facility_number number(4), 
  facility_version varchar2(11),
  item_number number (4),
  item_name varchar2(16),
  item_header varchar2(16),
  item_width number,
  item_size number,
  item_char number);

create table epc_event_item
(   
  CONSTRAINT epc_pk_eveite_rec PRIMARY KEY (vendor, facility_number,
					    facility_version, event_number,
					    event_type, item_number, 
					    item_position),
  vendor number(9),
  facility_number number(4), 
  facility_version varchar2(11),
  event_number number(4), 
  event_type number(2),
  item_number number(4),
  item_position number);

create table epc_facility_registration
(
collection_id number(4) CONSTRAINT fk_fac_reg_id REFERENCES
	      epc_collection (collection_id) on delete cascade,
epid number(8),
timestamp date,
timestamp_nano number,
reg_id_string varchar2(256),
vendor number(9),
facility_number number(4),
facility_version varchar2(11));

commit;
