-- Copyright (C) 2007 ETH Zurich
--
-- This file is part of Fosstrak (www.fosstrak.org).
--
-- Fosstrak is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License version 2.1, as published by the Free Software Foundation.
--
-- Fosstrak is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with Fosstrak; if not, write to the Free
-- Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
-- Boston, MA  02110-1301  USA


-- This is the EPCIS database schema file for the creation of the
-- tables in MySQL. Note: this schema is MySQL specific!


BEGIN;

SET storage_engine=INNODB;


-- ---------------------------------------------
-- Vocabularies
-- ---------------------------------------------

CREATE TABLE `voc_BizLoc` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`uri` varchar(1023) NOT NULL 
);

CREATE TABLE `voc_BizLoc_attr` (
`id` bigint NOT NULL REFERENCES `voc_BizLoc`(`id`),
`attribute` varchar(1023) NOT NULL,
`value` varchar(1023) NOT NULL
);

CREATE TABLE `voc_BizStep` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`uri` varchar(1023) NOT NULL 
);

CREATE TABLE `voc_BizStep_attr` (
`id` bigint NOT NULL REFERENCES `voc_BizStep`(`id`),
`attribute` varchar(1023) NOT NULL,
`value` varchar(1023) NOT NULL
);

CREATE TABLE `voc_BizTransType` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`uri` varchar(1023) NOT NULL 
);

CREATE TABLE `voc_BizTransType_attr` (
`id` bigint NOT NULL REFERENCES `voc_BizTransType`(`id`),
`attribute` varchar(1023) NOT NULL,
`value` varchar(1023) NOT NULL
);

CREATE TABLE `voc_BizTrans` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`uri` varchar(1023) NOT NULL 
);

CREATE TABLE `voc_BizTrans_attr` (
`id` bigint NOT NULL REFERENCES `voc_BizTrans`(`id`),
`attribute` varchar(1023) NOT NULL,
`value` varchar(1023) NOT NULL
);

CREATE TABLE `voc_Disposition` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`uri` varchar(1023) NOT NULL 
);

CREATE TABLE `voc_Disposition_attr` (
`id` bigint NOT NULL REFERENCES `voc_Disposition`(`id`),
`attribute` varchar(1023) NOT NULL,
`value` varchar(1023) NOT NULL
);

CREATE TABLE `voc_ReadPoint` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`uri` varchar(1023) NOT NULL 
);

CREATE TABLE `voc_ReadPoint_attr` (
`id` bigint NOT NULL REFERENCES `voc_ReadPoint`(`id`),
`attribute` varchar(1023) NOT NULL,
`value` varchar(1023) NOT NULL
);

CREATE TABLE `voc_EPCClass` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`uri` varchar(1023) NOT NULL 
);

CREATE TABLE `voc_EPCClass_attr` (
`id` bigint NOT NULL REFERENCES `voc_EPCClass`(`id`),
`attribute` varchar(1023) NOT NULL,
`value` varchar(1023) NOT NULL
);


-- ---------------------------------------------
-- Non-Standard Masterdata Vocabularies
-- ---------------------------------------------

CREATE TABLE `voc_Any` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`uri` varchar(1023) NOT NULL,
`vtype` varchar(1023) NOT NULL
);

CREATE TABLE `voc_Any_attr` (
`id` bigint NOT NULL REFERENCES `voc_Any`(`id`),
`attribute` varchar(1023) NOT NULL,
`value` varchar(1023) NOT NULL
);


-- ---------------------------------------------
-- Business Transactions
-- ---------------------------------------------

CREATE TABLE `BizTransaction` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`bizTrans` bigint NOT NULL REFERENCES `voc_BizTrans` (`id`),
`type` bigint REFERENCES `voc_BizTransType` (`id`)
);


-- ---------------------------------------------
-- Aggregation Events
-- ---------------------------------------------

CREATE TABLE `event_AggregationEvent` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`eventTime` timestamp NOT NULL,
`eventTimeMs` bigint(20) NOT NULL,
`recordTime` timestamp NOT NULL,
`recordTimeMs` bigint(20) NOT NULL,
`eventTimeZoneOffset` varchar(8) NOT NULL,
`parentID` varchar(1023) DEFAULT NULL, -- varchar(1023) good choice?
`action` varchar(8) NOT NULL CHECK (`action` IN ('ADD','OBSERVE','DELETE')),
`bizStep` bigint DEFAULT NULL REFERENCES `voc_BizStep` (`id`),
`disposition` bigint DEFAULT NULL REFERENCES `voc_Disposition`(`id`),
`readPoint` bigint DEFAULT NULL REFERENCES `voc_ReadPoint`(`id`),
`bizLocation` bigint DEFAULT NULL REFERENCES `voc_BizLoc`(`id`)
-- `bizTransaction` bigint DEFAULT NULL REFERENCES `voc_BizTrans`(`id`) 
);

CREATE TABLE `event_AggregationEvent_EPCs` (
-- This EPC list is called childEPCs (or childEPCList) in the standard,
-- for uniform access `child` has been ommitted from the table name.
`event_id` bigint NOT NULL REFERENCES `event_AggregationEvent`,
`epc` varchar(1023) NOT NULL,
`idx` int NOT NULL,
INDEX (event_id)
);

CREATE TABLE `event_AggregationEvent_bizTrans` ( 
-- bizTrans 0..* associated with event
`event_id` bigint NOT NULL REFERENCES `event_AggregationEvent` (`id`),
`bizTrans_id` bigint NOT NULL REFERENCES `BizTransaction` (`id`),
`idx` int NOT NULL,
INDEX (event_id)
);

CREATE TABLE `event_AggregationEvent_extensions` (
`id` bigint PRIMARY KEY auto_increment,
`event_id` bigint NOT NULL REFERENCES `event_AggregationEvent` (`id`),
`fieldname` varchar(128) NOT NULL,
`prefix` varchar(32) NOT NULL,
`intValue` integer,
`floatValue` float,
`dateValue` timestamp NULL DEFAULT NULL,
`strValue` varchar(1024),
INDEX (event_id)
);


-- ---------------------------------------------
-- Object Events
-- ---------------------------------------------

CREATE TABLE `event_ObjectEvent` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment -> cross platform sequence???
`eventTime` timestamp NOT NULL,
`eventTimeMs` bigint(20) NOT NULL,
`recordTime` timestamp NOT NULL,
`recordTimeMs` bigint(20) NOT NULL,
`eventTimeZoneOffset` varchar(8) NOT NULL,
`action` varchar(8) NOT NULL CHECK (`action` IN ('ADD','OBSERVE','DELETE')),
`bizStep` bigint DEFAULT NULL REFERENCES `voc_BizStep` (`id`),
`disposition` bigint DEFAULT NULL REFERENCES `voc_Disposition` (`id`),
`readPoint` bigint DEFAULT NULL REFERENCES `voc_ReadPoint` (`id`),
`bizLocation` bigint DEFAULT NULL REFERENCES `voc_BizLoc` (`id`)
-- `bizTransaction` bigint DEFAULT NULL REFERENCES `voc_BizTrans` (`id`)
);

CREATE TABLE `event_ObjectEvent_EPCs` (
`event_id` bigint NOT NULL REFERENCES `event_ObjectEvent`,
`epc` varchar(1023) NOT NULL,
`idx` int NOT NULL,
INDEX (event_id)
);

CREATE TABLE `event_ObjectEvent_bizTrans` ( 
-- bizTrans 0..* associated with event
`event_id` bigint NOT NULL REFERENCES `event_ObjectEvent` (`id`),
`bizTrans_id` bigint NOT NULL REFERENCES `BizTransaction` (`id`),
`idx` int NOT NULL,
INDEX (event_id)
);

CREATE TABLE `event_ObjectEvent_extensions` (
`id` bigint PRIMARY KEY auto_increment,
`event_id` bigint NOT NULL REFERENCES `event_ObjectEvent` (`id`),
`fieldname` varchar(128) NOT NULL,
`prefix` varchar(32) NOT NULL,
`intValue` integer,
`floatValue` float,
`dateValue` timestamp NULL DEFAULT NULL,
`strValue` varchar(1024),
INDEX (event_id)
);


-- ---------------------------------------------
-- Quantity Events
-- ---------------------------------------------

CREATE TABLE `event_QuantityEvent` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`eventTime` timestamp NOT NULL,
`eventTimeMs` bigint(20) NOT NULL,
`recordTime` timestamp NOT NULL,
`recordTimeMs` bigint(20) NOT NULL,
`eventTimeZoneOffset` varchar(8) NOT NULL,
`epcClass` bigint NOT NULL REFERENCES `voc_EPCClass` (`id`),
`quantity` bigint NOT NULL,
`bizStep` bigint DEFAULT NULL REFERENCES `voc_BizStep` (`id`),
`disposition` bigint DEFAULT NULL REFERENCES `voc_Disposition` (`id`),
`readPoint` bigint DEFAULT NULL REFERENCES `voc_ReadPoint` (`id`),
`bizLocation` bigint DEFAULT NULL REFERENCES `voc_BizLoc` (`id`)
);

CREATE TABLE `event_QuantityEvent_bizTrans` ( 
-- bizTrans 0..* associated with event
`event_id` bigint NOT NULL REFERENCES `event_QuantityEvent` (`id`),
`bizTrans_id` bigint NOT NULL REFERENCES `BizTransaction` (`id`),
`idx` int NOT NULL,
INDEX (event_id)
);

CREATE TABLE `event_QuantityEvent_extensions` (
`id` bigint PRIMARY KEY auto_increment,
`event_id` bigint NOT NULL REFERENCES `event_QuantityEvent` (`id`),
`fieldname` varchar(128) NOT NULL,
`prefix` varchar(32) NOT NULL,
`intValue` integer,
`floatValue` float,
`dateValue` timestamp NULL DEFAULT NULL,
`strValue` varchar(1024),
INDEX (event_id)
);


-- ---------------------------------------------
-- Transaction Events
-- ---------------------------------------------

CREATE TABLE `event_TransactionEvent` (
`id` bigint PRIMARY KEY auto_increment, -- id auto_increment
`eventTime` timestamp NOT NULL,
`eventTimeMs` bigint(20) NOT NULL,
`recordTime` timestamp NOT NULL,
`recordTimeMs` bigint(20) NOT NULL,
`eventTimeZoneOffset` varchar(8) NOT NULL,
`parentID` varchar(1023) DEFAULT NULL,
`action` varchar(8) NOT NULL CHECK (`action` IN ('ADD','OBSERVE','DELETE')),
`bizStep` bigint DEFAULT NULL REFERENCES `voc_BizStep` (`id`),
`disposition` bigint DEFAULT NULL REFERENCES `voc_Disposition` (`id`),
`readPoint` bigint DEFAULT NULL REFERENCES `voc_ReadPoint` (`id`),
`bizLocation` bigint DEFAULT NULL REFERENCES `voc_BizLoc` (`id`)
-- `bizTransaction` bigint DEFAULT NULL REFERENCES `voc_BizTrans` (`id`)
);

CREATE TABLE `event_TransactionEvent_EPCs` (
`event_id` bigint NOT NULL REFERENCES `event_TransactionEvent`,
`epc` varchar(1023) NOT NULL,
`idx` int NOT NULL,
INDEX (event_id)
);

CREATE TABLE `event_TransactionEvent_bizTrans` ( 
-- bizTrans 1..* associated with event, at least one not yet enforced in DB
`event_id` bigint NOT NULL REFERENCES `event_TransactionEvent` (`id`),
`bizTrans_id` bigint NOT NULL REFERENCES `BizTransaction` (`id`),
`idx` int NOT NULL,
INDEX (event_id)
);

CREATE TABLE `event_TransactionEvent_extensions` (
`id` bigint PRIMARY KEY auto_increment,
`event_id` bigint NOT NULL REFERENCES `event_TransactionEvent` (`id`),
`fieldname` varchar(128) NOT NULL,
`prefix` varchar(32) NOT NULL,
`intValue` integer,
`floatValue` float,
`dateValue` timestamp NULL DEFAULT NULL,
`strValue` varchar(1024),
INDEX (event_id)
);


-- ---------------------------------------------
-- Subscriptions
-- ---------------------------------------------

CREATE TABLE subscription (
subscriptionid varchar(767) NOT NULL PRIMARY KEY,
params blob,
dest varchar(1023),
sched blob,
trigg varchar(1023),
initialrecordingtime timestamp,
exportifempty boolean,
queryname varchar(1023),
lastexecuted timestamp
);


COMMIT;