|
Oracle® Sensor Edge Server Guide
10g Release 3 (10.1.3) B25142-01 |
|
 Previous |
 Next |
|
Oracle® Sensor Edge Server Guide
10g Release 3 (10.1.3) B25142-01 |
|
|
Previous |
Next |
This chapter describes the Sensor Data Repository through the following sections:
The Sensor Data Repository is a collection of database tables, views, and PL/SQL packages for storing and querying sensor event data. See also "Oracle Sensor Edge Server and Sensor Data Repository Considerations".
The Sensor Data Repository's relational tables store the actual event observations and metadata. The relational views are based on these tables. Applications use these relational views and the programming interface.
Table 4-1 describes the relational tables in the Sensor Data Repository.
Table 4-1 Relational tables in the Sensor Data Repository
| Table Name | Purpose |
|---|---|
|
|
Table storing the events from the middle ware and applications. |
|
|
Cached copy of the tags that have been observed so far. |
|
|
Mapping table to define the kind of events that a device can send or receive. |
|
|
The context/containment table for defining relationship between contexts and containments. |
|
|
The table where the relationship between the contexts are defined. |
|
|
Table for storing information related to event such as Event Type and Subtypes. |
|
|
Devices table. |
|
|
Table to store diagnostic information. |
|
|
Log table for warning and internal error. |
The Sensor Data Repository's views (described in Table 4-2) are used for querying the stored data.
Table 4-2 Relational Views in the Sensor Data Repository
| View Name | Description |
|---|---|
|
|
View of the device's capabilities |
|
|
Read-only view of the context; the view can be changed using PL/SQL |
|
|
Read-only view of the context relationship |
|
|
View of the context relationship with context names |
|
|
Read-only view of the device table |
|
|
Device capability view |
|
|
View of the device's diagnostic information |
|
|
View showing the event captured by a device |
|
|
View showing the last diagnostics information |
|
|
View of the latest observation made by a device |
|
|
Read-only view of the diagnostics table |
|
|
Read-only view of the event table |
|
|
View of the currently valid event metadata |
|
|
View of the event with type and subtype in place |
|
|
Read-only view of the tags seen |
|
|
View of the last device that detected the tag |
|
|
View of the path taken by the tag in terms of devices that have detected it |
The Sensor Data Repository defines a PL/SQL package which enables you to manipulate the contextual containment relationship and manually insert an event into the queue. Table 4-3 describes the procedures specified in the PL/SQL package. See also "PL/SQL Programming Interface".
Table 4-3 Procedures specified in the package
| Procedure | Description |
|---|---|
|
on_event |
Procedure to handle the incoming event and sort out how to disassemble the parts and put them into various tables. |
|
create_ctxt |
Procedure to add a new context |
|
update_ctxt_rel |
Procedure to update the context hierarchy |
|
update_ctxt |
Procedure to update the context |
|
remove_ctxt |
Procedure to remove a context |
This section describes the following operations and queries of the Sensor Data Repository.
The creation and deletion of a Sensor Data Repository is essentially the same as creating a Sensor Data Repository schema. The creation of a repository should be automatic if you opt to install it as part of the installation process. Otherwise, you can manually invoke a SQL script to install the repository. On the server side, there is a flag, called isArchived, that should be turned on to enable the server to start archiving data.
The Oracle Sensor Edge Server, if set to archiving mode, automatically sends events to Sensor Data Repository so that all of the events going to the Oracle Sensor Edge Server instance are archived. The application has the option of manually enqueuing events to the repository through the use of PL/SQL procedure in the edg_sda package (see "PL/SQL Programming Interface").
Use the views and relational tables described in Table 4-1 and Table 4-2 to query the Sensor Data Repository. See also "Schema Reference".
This section lists the tables and views and PL/SQL programming interface of the Sensor Data Repository.
The Sensor Data Repository includes the following tables:
A mapping table that defies the types of events that a device can send or receive.
Table 4-4 EDG_CAP_TAB table
| Name | Data Type | Nulls? | Default Value | Description |
|---|---|---|---|---|
|
OBJECT_ID |
NUMBER(10) |
N |
na |
Primary key for this mapping entry |
|
REF_DEVICE |
NUMBER(10) |
Y |
na |
Reference to the device interested |
|
REF_EVENT_INFO |
NUMBER(10) |
Y |
na |
Reference to the event information interested |
|
SEND_OR_RECV |
VARCHAR2(16) |
Y |
'SEND'$ |
Flag to indicate whether the device can send or receive the event |
|
CREATED_BY |
VARCHAR2(256) |
Y |
USER$ |
The user who created this entry |
|
CREATED_TIME |
TIMESTAMP(6) |
Y |
SYSTIMESTAMP$ |
The time when the entry was created |
The table in which relationships between the contexts are defined.
Table 4-5 EDG_CTXT_REL_TAB table
| Name | Data Type | Nulls? | Default Value | Description |
|---|---|---|---|---|
|
OBJECT_ID |
NUMBER(10) |
N |
na |
Primary key for the relationship entry between the contexts |
|
CID |
NUMBER(10) |
Y |
na |
The child context's id$ |
|
PID |
NUMBER(10) |
Y |
na |
The parent context's id$ |
|
CREATED_BY |
VARCHAR2(256) |
Y |
USER |
The user who created this entry$ |
|
CREATED_TIME |
TIMESTAMP(6) |
Y |
TIMESTAMP |
The time when the entry was created |
|
RETIRED_BY |
VARCHAR2(256) |
Y |
na |
The user who voided this entry |
|
RETIRED_TIME |
TIMESTAMP(6) |
Y |
na |
The time when the entry was voided |
|
IS_CURRENT |
VARCHAR2(1) |
Y |
na |
Indicator as to whether the entry is current |
|
REF_NEXT_VER |
NUMBER(10) |
Y |
na |
Reference to the next version of the relationship, added to help reconstruct the containment history for a context. |
The context/containment table for defining the relationship between contexts and containments.
Table 4-6 EDG_CTXT_TAB table
| Name | Data Type | Nulls? | Default Value | Description |
|---|---|---|---|---|
|
OBJECT_ID |
NUMBER(10) |
N |
na |
Primary key for the context entry |
|
NAME |
VARCHAR2(1024) |
Y |
na |
The name for the context |
|
DESCRIPTION |
NUMBER(10) |
Y |
na |
The description for the context |
|
IS_DEFAULT |
VARCHAR2(256) |
Y |
'F'$ |
Context is the universe flag |
|
CREATED_BY |
TIMESTAMP(6) |
Y |
USERS$ |
User who created the entry |
|
CREATED_TIME |
VARCHAR2(256) |
Y |
SYSTIMESTAMP$ |
Time entry was created |
|
RETIRED_BY |
TIMESTAMP(6) |
Y |
na |
User who voided the entry |
|
RETIRED_TIME |
VARCHAR2(1) |
Y |
na |
Time entry was voided |
|
IS_CURRENT |
NUMBER(10) |
Y |
'T'$ |
Current entry flag |
The devices table.
Table 4-7 EDG_DEVICE_TAB table
| Number | Name | Data Type | Nulls? | Default Value |
|---|---|---|---|---|
|
1 Primary key for devices |
OBJECT_ID |
NUMBER(10) |
N |
na |
|
2 Tag ID (EPC code) representing this device |
TAG_ID |
VARCHAR2(256) |
Y |
na |
|
3 Name for this device object |
NAME |
VARCHAR2(256) |
N |
na |
|
4 Description for this device |
DESCRIPTION |
VARCHAR21024) |
Y |
na |
|
5 Site name where this device is located |
SITE_NAME |
VARCHAR2(256) |
N |
na |
|
6 User who created this device |
CREATED_BY |
VARCHAR2(256) |
Y |
USER$ |
|
7 Time when this device entry was created |
CREATED_TIME |
TIMESTAMP(6) |
Y |
SYSTIMESTAMP$ |
|
8 Reference to help locate the last diagnostic status of the device |
LAST_STATUS |
NUMBER(10) |
Y |
na |
Table used to store diagnostic information.
Table 4-8 EDG_DIAG_TAB table
| Number | Name | Data Type | Nulls? | Default Value |
|---|---|---|---|---|
|
1 Primary key for diagnostic entry |
OBJECT_ID |
NUMBER(10) |
N |
na |
|
2 Reference to the device for this diagnostic entry |
REF_DEVICE |
NUMBER(10) |
Y |
na |
|
3 Status for the device, server, or component |
STATUS |
VARCHAR2(64) |
N |
na |
|
4 Message associated with an error or warning |
MESSAGE |
VARCHAR21024) |
Y |
na |
|
5 Time when the erroneous event was encountered |
TIME |
TIMESTAMP(6) |
Y |
na |
|
6 User who created this diagnostic entry |
CREATED_BY |
VARCHAR2(256) |
Y |
USER$ |
|
7 Time when this diagnostic entry was created |
CREATED_TIME |
TIMESTAMP(6) |
Y |
SYSTIMESTAMP$ |
Table used to store event information (such as Type and Subtype).
Table 4-9 EDG_EVENT_INFO_TAB table
| Number | Name | Data Type | Nulls? | Default Value |
|---|---|---|---|---|
|
1 Primary key for event entry |
OBJECT_ID |
NUMBER(10) |
N |
na |
|
2 Name for event entry |
NAME |
VARCHAR2(256) |
N |
na |
|
3 Type for this event information entry |
TYPE |
NUMBER(5) |
N |
na |
|
4 Subtype for this event information entry |
SUBTYPE |
NUMBER(5) |
Y |
-1$ |
|
5 Flag indicating if event is custom-defined or provided by, and registered with, Oracle |
REGISTERED |
VARCHAR2(1) |
Y |
'F'$ |
|
6 Description of the event |
DESCRIPTION |
VARCHAR2(1024) |
Y |
na |
|
7 Usage pattern for the id field in the event; must be in sync with the driver |
ID_USAGE |
VARCHAR2(1024) |
Y |
na |
|
8 Usage pattern for the data field in the event; must be in sync with the driver |
DATA_USAGE |
VARCHAR2(1024) |
Y |
na |
|
User who created this entry |
CREATED_BY |
VARCHAR2(256) |
Y |
USER$ |
|
Time entry was created |
CREATED_TIME |
TIMESTAMP(6) |
Y |
SYSTIMESTAMP$ |
|
User who voided this entry |
RETIRED_BY |
VARCHAR2(256) |
Y |
na |
|
Time this entry was voided |
RETIRED_TIME |
TIMESTAMP(6) |
Y |
na |
|
Denotes if entry is valid |
IS_CURRENT |
VARCHAR2(1) |
Y |
'T'$ |
Table used to store event information from middle ware and applications.
Table 4-10 EDG_EVENT_TAB table
| Number | Name | Data Type | Nulls? | Default Value |
|---|---|---|---|---|
|
1 Primary key for events |
OBJECT_ID |
NUMBER(10) |
N |
na |
|
2 Tag ID (EPC code) for the tag |
TAG_ID |
VARCHAR2(256) |
Y |
na |
|
3 Device from which the event originated |
REF_DEVICE |
NUMBER(10) |
Y |
na |
|
4 Context relevant to the event; set by applications depending on context |
REF_CTXT |
NUMBER(10) |
Y |
-1$ |
|
5 Source name of the event |
SOURCE_NAME |
VARCHAR2(64) |
Y |
'F'$ |
|
6 Reference to event metadata |
REF_EVENT_INFO |
NUMBER(10) |
Y |
na |
|
7 Reference used to correlate aggregated events |
CORRELATION_ID |
VARCHAR2(64) |
Y |
na |
|
8 Data field for the event; depends on type of device. See ID_USAGE field in EDG_EVENTINFO for more information |
DATA |
VARCHAR2(1024) |
Y |
na |
|
Time event occurred |
TIME |
TIMESTAMP(6) |
Y |
na |
Table used to store warnings and internal errors.
Table 4-11 EDG_LOG table
| Number | Name | Data Type | Nulls? | Default Value |
|---|---|---|---|---|
|
1 Primary key for log entry |
OBJECT_ID |
NUMBER(10) |
N |
na |
|
2 Log level, allowed values are: "N" for notify, "W" for warning, and "E" for error. |
LOG_LEVEL |
VARCHAR2(1) |
Y |
na |
|
3 Message to be logged for the error/warning |
MESSAGE |
VARCHAR2(4000) |
Y |
na |
|
4 Database user who created the log entry |
CREATED_BY |
VARCHAR2(64) |
Y |
USER$ |
|
5 Time entry was created inside the database |
CREATED_TIME |
TIMESTAMP(6) |
Y |
SYSTIMESTAMP$ |
Cached copy of the tags observed so far.
Table 4-12 EDG_TAG_TAB table
| Number | Name | Data Type | Nulls? | Default Value |
|---|---|---|---|---|
|
1 Primary key for tag entry |
OBJECT_ID |
NUMBER(10) |
N |
na |
|
2 Tag ID (EPC code) |
TAG_ID |
VARCHAR2(256) |
Y |
na |
|
3 Reference to the last observation of the tag |
LAST_EVENT |
NUMBER(10) |
Y |
na |
|
4 User who created the entry |
CREATED_BY |
VARCHAR2(256) |
Y |
USER$ |
|
5 Time entry was created |
CREATED_TIME |
TIMESTAMP(6) |
Y |
SYSTIMESTAMP$ |
The Sensor Data Repository includes the following views:
View of the device's capabilities
Table 4-13 EDG_CAP view
| View | Data Type | Nulls? |
|---|---|---|
|
1 OBJECT_ID Primary key for device-event capability mappings |
NUMBER(10) |
N |
|
2 REF_DEVICE Reference to the device of interest |
NUMBER(10) |
Y |
|
3 REF_EVENT_INFO Reference to the event information of interest |
NUMBER(10) |
Y |
|
4 SEND_OR_RECV Flag indicating is device can send/receive events |
VARCHAR2(16) |
Y |
SELECT object_id, ref_device, ref_event_info, send_or_recv FROM edg_cap_tab
Read-only view of the context, the view can be changed using PL/SQL procedures.
Table 4-14 EDG_CTXT view
| View | Data Type | Nulls? |
|---|---|---|
|
1 OBJECT_ID Primary key for the context entry |
NUMBER(10) |
N |
|
2 NAME Name for the context |
VARCHAR2(1024) |
Y |
|
3 DESCRIPTION Description of the context |
VARCHAR2(1024) |
Y |
|
4 SEND_OR_RECV Whether or not the context is the universe |
VARCHAR2(16) |
Y |
SELECT object_id, name, description, is_default FROM edg_ctxt_tab WHERE is_current = 'T' WITH READ ONLY
Read-only view of the context relationship.
Table 4-15 EDG_CTXT_REL view
| View | Data Type | Nulls? |
|---|---|---|
|
1 OBJECT_ID Primary key for the relationship entry between the contexts |
NUMBER(10) |
N |
|
2 CID The Child context's ID |
NUMBER(10) |
Y |
|
3 PID The Parent context's ID |
NUMBER(10) |
Y |
SELECT object_id, cid, pid FROM edg_ctxt_rel_tab WHERE is_current = 'T' WITH READ ONLY
Read-only view of the context relationship.
Table 4-16 EDG_CTXT_REL_NAME_VW view
| View | Data Type | Nulls? |
|---|---|---|
|
1 CHILD_ID The Child context's ID |
NUMBER(10) |
Y |
|
2 PARENT_ID The Parent context's ID |
NUMBER(10) |
Y |
|
3 PARENT_NAME Name of parent context |
VARCHAR2(1024) |
Y |
|
4 CHILD_NAME Name of child context |
VARCHAR2(1024) |
Y |
SELECT rel.cid child_id, rel.pid parent_id, cP.name parent_name, cC.name child_name FROM edg_ctxt cP, edg_ctxt cC, edg_ctxt_rel rel WHERE cP.object_id = rel.pid AND cC.object_id = rel.cid
Read-only view of the device table.
Table 4-17 EDG_DEVICE view
| Number | Data Type | Nulls? |
|---|---|---|
|
1 OBJECT_ID Primary key for the device |
NUMBER(10) |
N |
|
2 TAG_ID Tag ID (EPC code) representing the device |
VARCHAR2(256) |
Y |
|
3 NAME Name for the device object |
VARCHAR2(256) |
N |
|
4 DESCRIPTION Device description |
VARCHAR2(1024) |
Y |
|
5 SITE_NAME Name of site where device is located |
VARCHAR2(256) |
N |
|
6 CREATED_BY User who created device entry |
VARCHAR2(256) |
Y |
|
7 CREATED_TIME Time when device entry was created |
TIMESTAMP(6) |
Y |
|
8 LAST_STATUS Reference to help locate the last diagnostic status of the device |
NUMBER(10) |
Y |
SELECT "OBJECT_ID", "TAG_ID", "NAME", "DESCRIPTION", "SITE_NAME", "CREATED_BY", "CREATED _TIME", "LAST_STATUS" FROM edg_device_tab WITH READ ONLY
Device capability view.
Table 4-18 EDG_DEV_CAP_VW view
| Number | Data Type | Nulls? |
|---|---|---|
|
1 DEVICE_TAG_ID Tag ID (EPC code) representing the device |
VARCHAR2(256) |
Y |
|
2 DEVICE_NAME Name for the device object |
VARCHAR2(256) |
N |
|
3 DEVICE_DESC Description of the device |
VARCHAR2(64) |
Y |
|
4 SITE_NAME Name of site where device is located |
VARCHAR2(1024) |
N |
|
5 EVENT_TYPE Event information entry type |
VARCHAR2(256) |
N |
|
6 EVENT_SUBTYPE Event information subtype |
NUMBER(5) |
Y |
|
7 EVENT_DESC Description for this event type |
NUMBER(5) |
Y |
|
8 SEND_OR_RECV Flag indicating if device can send/receive |
VARCHAR2(1024) |
Y |
SELECT dev.tag_id device_tag_id, dev.name device_name, dev.description device_desc, dev.site_name site_name, ei.type event_type, ei.subtype event_subtype,ei.description event_desc, cap.send_or_recv send_or_recv FROM edg_cap cap, edg_device dev. edg_event_info ei WHERE cap.ref_device=dev.object_id AND cap.ref_event_info=ei.object_id
View showing device's diagnostic information.
Table 4-19 EDG_DEV_DIAG_VW view
| View | Data Type | Nulls? |
|---|---|---|
|
1 DEVICE_TAG_ID Tag ID (EPC code) representing the devices |
VARCHAR2(256) |
Y |
|
2 STATUS Status of the device, server or component |
VARCHAR2(64) |
Y |
|
3 MESSAGE Message related to error or warnings |
VARCHAR2(1024) |
Y |
|
4 TIME Time when erroneous condition was encountered |
TIMESTAMP(6) |
Y |
|
5 DEVICE_NAME Name for the device object |
VARCHAR2(256) |
N |
|
6 DEVICE_DESC Description of the device |
VARCHAR2(1024) |
Y |
|
7 SITE_NAME Site name where device resides |
VARCHAR2(256) |
N |
SELECT dev.tag_id device_tag_id, diag.status status, diag.message message, diag.time time, dev.name device_name, dev.description device_desc, dev.site_name site_name FROM edg_diag diag, edg_device dev WHERE dev.object_id=diag.ref_device
View showing events captured by a device.
Table 4-20 EDG_DEV_EVENT_VW view
| View | Data Type | Nulls? |
|---|---|---|
|
1 DEVICE_NAME Name for the device object |
VARCHAR2(256) |
N |
|
2 DEVICE_TAG_ID Tag ID (EPC code) representing the device |
VARCHAR2(256) |
Y |
|
3 DEVICE_DESC Description for the device |
VARCHAR2(1024) |
Y |
|
4 SITE_NAME Name of site where device is located |
VARCHAR2(256) |
N |
|
5 EVENT_ID Reference to the event object |
NUMBER(10) |
N |
|
6 EVENT_TAG_ID Tag ID (EPC code) representing the tag and device for this event |
VARCHAR2(256) |
Y |
|
7 DEVICE_ID Reference to the device object |
NUMBER(10) |
Y |
|
8. EVENT_DATA Data field for the event. Varies according to type of device. See id_usage field in edge_event_info for more information |
VARCHAR2(1024) |
Y |
|
9 EVENT_TIME Time when event occurred |
TIMESTAMP(6) |
Y |
|
10 EVENT_CORRELATION_ID Reference used to correlate aggregated events |
VARCHAR2(64) |
Y |
|
11 EVENT_NAME Name of the kind of event |
VARCHAR2(256) |
N |
|
12 EVENT_TYPE Type for the event information entry, such as 200 for RFID observations |
NUMBER(5) |
N |
|
13 EVENT_SUBTYPE Subtype for the event information entry, such as 1 for IN_FIELD RFID observation |
NUMBER(5) |
Y |
|
14 EVENT_DESC Description for kind of event |
VARCHAR2(1024) |
Y |
SELECT dev.name device_name, dev.tag_id device_tag_id, dev.description device_desc, dev.site_name site_name diag.status status, ev.* FROM edg_device dev, edg_event_vw ev WHERE ev.device_id=dev_object_id
View showing the last diagnostics information.
Table 4-21 EDG_DEV_LAST_DIAG_VW view
| View | Data Type | Nulls? |
|---|---|---|
|
1 DEVICE_TAG_ID Tag ID (EPC code) representing the devices |
VARCHAR2(256) |
Y |
|
2 STATUS Status of the device, server or component |
VARCHAR2(64) |
Y |
|
3 MESSAGE Message related to error or warnings |
VARCHAR2(1024) |
Y |
|
4 TIME Time when erroneous condition was encountered |
TIMESTAMP(6) |
Y |
|
5 DEVICE_NAME Name for the device object |
VARCHAR2(256) |
N |
|
6 DEVICE_DESC Description of the device |
VARCHAR2(1024) |
Y |
|
7 SITE_NAME Site name where device resides |
VARCHAR2(256) |
N |
SELECT dev.tag_id device_tag_id, diag.status status, diag.message message, diag.time time, dev.name device_name, dev.description device_desc, dev.site_name site_name FROM edg_diag diag, edg_device dev WHERE dev.last_status = diag.object_id
View showing the last observation made by a device.
Table 4-22 EDG_DEV_LAST_OBSV_VW view
| View | Data Type | Nulls? |
|---|---|---|
|
1 DEVICE_NAME Name for the device object |
VARCHAR2(256) |
N |
|
2 DEVICE_TAG_ID Tag ID (EPC code) representing the device |
VARCHAR2(256) |
Y |
|
3 DEVICE_DESC Description of the device |
VARCHAR2(1024) |
Y |
|
4 SITE_NAME Site name where device resides |
VARCHAR2(256) |
Y |
|
5 EVENT_ID Reference to the event object |
NUMBER(10) |
N |
|
6 DEVICE_ID Tag ID (EPC code) representing the devices |
VARCHAR2(256) |
Y |
|
7 EVENT_TAG_ID Reference to the device object |
NUMBER(10) |
Y |
|
8 EVENT_DATA Data field for the event. Varies according to device. See id_usage field in edg_event_info |
VARCHAR2(1024) |
Y |
|
9 EVENT_TIME Time when event occurred |
VARCHAR2(1024) |
Y |
|
10 EVENT_CORRELATION_ID Reference used to correlate aggregated events |
VARCHAR2(64) |
Y |
|
11 EVENT_NAME Name of the kind of event |
VARCHAR2(256) |
Y |
|
12 EVENT_TYPE Type for the event information entry, such as 200 for RFID observations |
NUMBER(5) |
Y |
|
13 EVENT_SUBTYPE Subtype for the event information entry, such as 1 for IN_FIELD RFID observations |
NUMBER(5) |
Y |
|
14 EVENT_DESC Description for the kind of event |
VARCHAR2(1024) |
Y |
SELECT device_name, device_tag_id, device_desc, site_name, event_id, event_tag_id, device_id, event_data, event_time, event_correlation_id, event_name, event_type, event_subtype, event_desc FROM (
Read-only view of the diagnostics table
Table 4-23 EDG_DIAG view
| View | Data Type | Nulls? |
|---|---|---|
|
1 OBJECT_ID Primary key for the diagnostics entry |
NUMBER(10) |
N |
|
2 REF_DEVICE Reference to the related device. |
NUMBER(10) |
Y |
|
3 STATUS The status of the device, server or component |
VARCHAR2(64) |
Y |
|
4 MESSAGE Message related to error or warnings |
VARCHAR2(1024) |
Y |
|
5 TIME Time when erroneous condition was encountered |
TIMESTAMP(6) |
Y |
|
6 CREATED_BY The user who created the diagnostic entry |
VARCHAR2(256) |
Y |
|
7 CREATED_TIME Time when the diagnostic entry was created |
TIMESTAMP(6) |
Y |
SELECT "CONNECT_ID", "REF_DEVICE", "STATUS", "MESSAGE", "TIME", "CREATED_BY", "CREATED_TIME" FROM edg_diag_tab WITH READ ONLY
Read-only view of the events table.
Table 4-24 EDG_EVENT view
| View | Data Type | Nulls? |
|---|---|---|
|
1 OBJECT_ID Primary key for the event |
NUMBER(10) |
N |
|
2 TAG_ID Tag ID (EPC code) representing the tag and device for which the event was created |
VARCHAR2(256) |
Y |
|
3 REF_DEVICE The device from which the event originated |
NUMBER(10) |
Y |
|
4 REF_CTXT Context relevant to the event; set by applications depending on the contextual situation |
NUMBER(10) |
Y |
|
5 SOURCE_NAME Source name of the event |
VARCHAR2(64) |
Y |
|
6 REF_EVENT_INFO Reference to the event metadata |
NUMBER(10) |
Y |
|
7 CORRELATION_ID Reference used to correlate aggregated events |
VARCHAR2(64) |
Y |
|
8 DATA Data field for the event; varies depending on device see id_usage in edg_event_info for more information |
VARCHAR2(1024) |
Y |
|
9 TIME Time when the event occurred |
TIMESTAMP(6) |
Y |
SELECT "OBJECT_ID", "TAG_ID", "REF_DEVICE", "REF_CTXT", "SOURCE_NAME", "REF_EVENT_INFO", "CORRELATION_ID" "DATA", "TIME" FROM edg_event_tab WITH READ ONLY
View of the currently valid event metadata.
Table 4-25 EDG_EVENT_INFO view
| View | Data Type | Nulls? |
|---|---|---|
|
1 OBJECT_ID Primary key for the event information entry |
NUMBER(10) |
N |
|
2 NAME Name for the event information entry |
VARCHAR2(256) |
N |
|
3 TYPE Type of event information entry |
NUMBER(5) |
N |
|
4 SUBTYPE Subtype of event information entry |
NUMBER(5) |
N |
|
5 REGISTERED Flag indicating if event information is custom-defined or provided by, and registered at, Oracle |
VARCHAR2(1) |
Y |
|
6 DESCRIPTION Description for the kind of event |
VARCHAR2(1024) |
Y |
|
7 ID_USAGE Usage pattern for the ID field in the event (from the middle ware side); must be in sync with the driver implementation |
VARCHAR2(1024) |
Y |
|
8 DATA_USAGE Usage pattern for the data field in the event (from the middle ware side); must be in sync with the driver implementation |
VARCHAR2(1024) |
Y |
SELECT object_id, name, type, subtype, registered, description, id_usage, data_usage, data_usage FROM edg_event_tab WHERE is_current='T'
View of the event with Type and Subtype in place.
Table 4-26 EDG_EVENT_VW view
| View | Data Type | Nulls? |
|---|---|---|
|
1 EVENT_ID Primary key for the event |
NUMBER(10) |
N |
|
2 EVENT_TAG_ID Tag ID (EPC code) representing the tag and device for which the event was created |
VARCHAR2(256) |
Y |
|
3 DEVICE_ID Device from which the event originated |
NUMBER(10) |
Y |
|
4 EVENT_DATA Data field for the event. Varies according to kind of device. See id_usage field in edg_event_info for more information |
VARCHAR2(1024) |
Y |
|
5 EVENT_TIME Time when the event occurred |
TIMESTAMP(6) |
Y |
|
6 CORRELATION_ID Reference used to correlate aggregated events |
VARCHAR2(64) |
Y |
|
7 EVENT_NAME Name of the kind of event |
VARCHAR2(256) |
N |
|
8 EVENT_TYPE Type for the event information entry, such as 200 for RFID observations |
NUMBER(5) |
N |
|
9 EVENT_SUBTYPE Subtype for the event information entry, such as 1 for IN_FIELD RFID observation event |
NUMBER(5) |
Y |
|
10 EVENT_DESC Description for the kind of event |
VARCHAR2(1024) |
Y |
SELECT ev.object_id event_id, ev.tag_id event_tag_id, ev.ref_device device_id, ev.data event_data, ev.time event_time, ev.correlation_id event correlation_id, md.name event_name, md.type event_type, md.subtype event_subtype, md.description event_descFROM edg_event_tab ev, edg_event_info md WHERE ev.ref_event_info=md.object_id
Read-only view of the tags screen.
Table 4-27 EDG_TAG view
| View | Data Type | Nulls? |
|---|---|---|
|
1 OBJECT_ID Primary key for the tag entry |
NUMBER(10) |
N |
|
2 TAG_ID Tag ID (EPC code). |
VARCHAR2(256) |
Y |
|
3 LAST_EVENT Reference to the last observation of the tag |
NUMBER(10) |
Y |
SELECT object_id tag_id, last_event FROM edg_tag_tab WITH READ ONLY
View of the last device that detected the tag.
Table 4-28 EDG_TAG_LAST_DEV_VW view
| Number | Data Type | Nulls? |
|---|---|---|
|
1 DEVICE_NAME Name for the device object |
VARCHAR2(256) |
N |
|
2 DEVICE_TAG_ID Tag ID (EPC code) representing the device |
VARCHAR2(256) |
Y |
|
3 DEVICE_DESC Description for the device |
VARCHAR2(1024) |
Y |
|
4 SITE_NAME Name of site where device is located |
VARCHAR2(256) |
N |
|
5 EVENT_ID Reference to the event object |
NUMBER(10) |
N |
|
6 EVENT_TAG_ID Tag ID (EPC code) representing the tag and device for this event |
VARCHAR2(256) |
Y |
|
7 DEVICE_ID Reference to the device object |
NUMBER(10) |
Y |
|
8. EVENT_DATA Data field for the event. Varies according to type of device. See id_usage field in edge_event_info for more information |
VARCHAR2(1024) |
Y |
|
9 EVENT_TIME Time when event occurred |
TIMESTAMP(6) |
Y |
|
10 EVENT_CORRELATION_ID Reference used to correlate aggregated events |
VARCHAR2(64) |
Y |
|
11 EVENT_NAME Name of the kind of event |
VARCHAR2(256) |
N |
|
12 EVENT_TYPE Type for the event information entry, such as 200 for RFID observations |
NUMBER(5) |
N |
|
13 EVENT_SUBTYPE Subtype for the event information entry, such as 1 for IN_FIELD RFID observation |
NUMBER(5) |
Y |
|
14 EVENT_DESC Description for kind of event |
VARCHAR2(1024) |
Y |
SELECT devEVENT.* FROM edg_tag, edg_dev_event_vw devEVENT WHERE tag.last_event-devEVENT.event_id
View of the path taken by the tag in terms of the devices that have detected it.
Table 4-29 EDG_TAG_PATH_VW view
| View | Data Type | Nulls? |
|---|---|---|
|
1 DEVICE_NAME Name for the device object |
VARCHAR2(256) |
N |
|
2 DEVICE_TAG_ID Tag ID (EPC code) representing the device |
VARCHAR2(256) |
Y |
|
3 DEVICE_DESC Description for the device |
VARCHAR2(1024) |
Y |
|
4 SITE_NAME Name of site where device is located |
VARCHAR2(256) |
N |
|
5 EVENT_ID Reference to the event object |
NUMBER(10) |
N |
|
6 EVENT_TAG_ID Tag ID (EPC code) representing the tag and device for this event |
VARCHAR2(256) |
Y |
|
7 DEVICE_ID Reference to the device object |
NUMBER(10) |
Y |
|
8. EVENT_DATA Data field for the event. Varies according to type of device. See id_usage field in edge_event_info for more information |
VARCHAR2(1024) |
Y |
|
9 EVENT_TIME Time when event occurred |
TIMESTAMP(6) |
Y |
|
10 EVENT_CORRELATION_ID Reference used to correlate aggregated events |
VARCHAR2(64) |
Y |
|
11 EVENT_NAME Name of the kind of event |
VARCHAR2(256) |
Y |
|
12 EVENT_TYPE Type for the event information entry, such as 200 for RFID observations |
NUMBER(5) |
Y |
|
13 EVENT_SUBTYPE Subtype for the event information entry, such as 1 for IN_FIELD RFID observation |
NUMBER(5) |
Y |
|
14 EVENT_DESC Description for kind of event |
VARCHAR2(1024) |
Y |
|
15 NEXT_DEVICE_ID Reference to the next device object |
NUMBER |
Y |
|
16 NEXT_DEVICE_NAME Name of the next device object |
VARCHAR2(256) |
Y |
|
17 NEXT_DEVICE_TAG_ID Tag ID (EPC code) representing the next time |
VARCHAR2(256) |
Y |
|
18 TIME_DIFF Time taken to travel from one device to the next |
INTERVAL DAY() TO SECOND() |
Y |
SELECT device_name .device_tag_id .device_desc .site_name .event_id .event_tag_id .device_id .event_data .event_time .event_correlation_id .event_name .event_type .event_subtype .event_desc .next_device_id .next_device_name .next_device_tag_id .(event_end_time-event_time) time_diff FROM ( SELECT edv.*, DECODE( LEAD(edv.device_id) OVER (PARTITION BE edv.event_tag_id ORDER BY edv.event_time), edv.device_id, 0, 1) hop, LEAD(edv.device_id) OVER (PARTITION BY edv.event_tag_id ORDER BY edv.event_time) next_device_id, LEAD(edv.device_name) OVER (PARTITION BY edv.event_tag_id ORDER BY edv.event_time) next_device_id, LEAD(edv.device_TAG_ID) OVER (PARTITION BY edv.event_tag_id ORDER BY edv.event_time) next_device_tag_id, LEAD(edv.event_time) OVER (PARTITION BY edv.event_tag_id ORDER BY edv.event_time) event_end_time FROM edg_dev_event_vw edv ) ev_path WHERE hop=1
The Sensor Data Repository includes the following PL/SQL package.
1: PACKAGE EDG_SDA 1S 2: 3: --- procedure to handle the incoming event 4: --- and sort out how to disassemble the parts 5: --- and put them into various tables. 6: 7: PROCEDURE on_event 8: (p_correlation_id IN edg_event_tab.correlation_id%TYPE 9: .p_source_name IN edg_event_tab.source_name%TYPE 10. .p_site_name IN edg_device_tab.site_name%TYPE 11. .p_device_name IN edg_device_tab.name%TYPE 12: .p_type IN edg_event_info_tab.type%TYPE 13: .p_subtype IN edg_event_info_tab.type%TYPE 14: .p_time IN edg_event_tab.time%TYPE 15: .p_id IN edg_event_tab_tag.id%TYPE 16: .p_data IN edg_event_tab.data%TYPE 17: ); 18: 19: --- procedure to add a new context 20: PROCEDURE create_ctxt 21: (p_object_id) OUT edg_ctxt_tab.object_id%TYPE 22: .p_name IN edg_ctxt_tab.name%TYPE 23: .p_description IN edg_ctxt_tab.description%TYPE 24: .p_parent_ctxt_tab.object_id%TYPE 25: ); 26: 27: ---procedure to add a new context 28: PROCEDURE create_ctxt 29: (p_name IN edg_ctxt_tab.name%TYPE 30: .p_description IN edg_ctxt_tab.description%TYPE 31: .p_parent_ctxt_id IN edg_ctxt_tab.object_id%TYPE 32: ); 33. 34: --- procedure to update the context hierarchy 35: PROCEDURE update_ctxt_rel 36: (p_cid IN edg_ctxt_rel_tab.cid%TYPE 37: .p_cid IN edg_ctxt_rel_tab.pid%TYPE 38: ); 39: 40: --- procedure to update the context 41: PROCEDURE update_ctxt 42: (p_object_id IN edg_ctxt_tab.object_is%TYPE 43: .p_name IN edg_ctxt_tab.name%TYPE 44: .p_description IN edg_ctxt_tab.description%TYPE 45: ); 46: 47: --- procedure to remove a context 48: PROCEDURE remove_ctxt 49: (p_object_id IN edg_ctxt_tab.object_id%TYPE 50: 51: END EDG_SDA;
