Révision bf9c3717
Ajouté par Remy Menard il y a environ 11 ans
| IoTa-Installer/resources/epcis_schema.sql | ||
|---|---|---|
|
-- 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;
|
||
|
|
||
|
-- 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;
|
||
|
|
||
Formats disponibles : Unified diff
Version 1.99
- a few bugs fixed!
- new testing application to capture events
- new options to the command line to configure the public/private keys
for TLS and for signature
- new method more accurate than "traceEPC": the events are sorted by
EPCIS
- New options added to the command line
- user can be identified by alias, if the DN of his certificate is
incompatible with the LDAP directory
- new shell script to configure the LDAP directory (same
functionnalities as IoTa-Installer)
- installs and configures LaMBDa
- finer certificate mangagement
- EpcILoN correctly subscribe with TLS to ETa
- new figure for LaMBDa
- shows data flows between PHI and YPSilon
- sets DS and DSeTa in different schemas