SQLite

The OCEL 2.0 SQLite format is designed from the ground-up to be efficient, idiomatic, and easily comprehensible.

Minimal Example

In the following minimal example, we see one Event Type, one Event, one Object Type and one Object.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "event" (
"ocel_id" TEXT,
  "ocel_type" TEXT
);
INSERT INTO event VALUES('e1','Create Order');
CREATE TABLE IF NOT EXISTS "event_object" (
"ocel_event_id" TEXT,
  "ocel_object_id" TEXT,
  "ocel_qualifier" TEXT
);
INSERT INTO event_object VALUES('e1','o1','order');
CREATE TABLE IF NOT EXISTS "object" (
"ocel_id" TEXT,
  "ocel_type" TEXT
);
INSERT INTO object VALUES('o1','Order');
CREATE TABLE IF NOT EXISTS "object_object" (
"ocel_source_id" TEXT,
  "ocel_target_id" TEXT,
  "ocel_qualifier" TEXT
);
CREATE TABLE IF NOT EXISTS "event_CreateOrder" (
"ocel_id" TEXT,
  "ocel_time" TEXT
, "total items" INTEGER);
INSERT INTO event_CreateOrder VALUES('e1','1970-01-01 00:00:00',1);
CREATE TABLE IF NOT EXISTS "event_map_type" (
"ocel_type" TEXT,
  "ocel_type_map" TEXT
);
INSERT INTO event_map_type VALUES('Create Order','CreateOrder');
CREATE TABLE IF NOT EXISTS "object_map_type" (
"ocel_type" TEXT,
  "ocel_type_map" TEXT
);
INSERT INTO object_map_type VALUES('Order','Order');
CREATE TABLE IF NOT EXISTS "object_Order" (
 "ocel_id" TEXT,
 "ocel_time" TEXT,
 "ocel_changed_field" TEXT,
 "item" INTEGER
);
INSERT INTO object_Order VALUES('o1','1970-01-01 00:00:00',NULL,1);
COMMIT;

Note that the attribute item of object o1 is set to UNIX timestamp 0 (in ISO 8601).

Since we only have a single order, there are no object-to-object relationships. However, there is an event-to-object relationship from event e1 to object o1.

Attribute Types

Attribute types can have any value of:

  • TEXT
  • TIMESTAMP (ISO 8601 date and time string)
  • INTEGER
  • REAL
  • BOOLEAN (only valid values are 1 (TRUE) or 0 (FALSE))

SQL Validation

We provide an SQL file that validates all constraints. You can download it here.

File Extension

We do not prescribe any particular file extension apart from .sqlite or .sqlite3.

Further Resources

All provided logs in the Event Logs section are available in the SQLite format.

← JSON
Specification →