Optimize the Core Data table schema

[weichou1229]

🚀 Feature Request

Relevant Package [REQUIRED]

This feature request is for core-data

Description [REQUIRED]

A clear and concise description of the problem or missing capability...

The event and reading contain lots of repeat fields we can extract them to another table to reduce the disk space.

Describe the solution you'd like

1. Create new device_info table
2. Remove UUID from reading
3. In the implementation, - create the new device_info model
   • using a map[key]device_info_id to cache the device_info for checking wether the event or reading device info fields exist

For example, when receiving the event reading below, we can

• store the deviceName, profileName, sourceName, tags in the device_info for the event
• store the deviceName, profileName, resourceName, valueType in the device_info for the reading

{
  "apiVersion": "v3",
  "statusCode": 200,
  "event": {
    "apiVersion": "v3",
    "id": "040bd523-ec33-440d-9d72-e5813a465f37",
    "deviceName": "device-001",
    "profileName": "profile-001",
    "sourceName": "source-1",
    "origin": 1602168089665565200,
    "tags": {
      "Gateway": "HoustonStore-000123",
      "Latitude": { "degrees": 25, "minute": 1,"second": 26.6268000000062 },
      "Longitude": {"degree": 121,"minute": 31, "second": 19.600799999980154}
    },
    "readings": [
      {
        "deviceName": "device-001",
        "resourceName": "resource-001",
        "profileName": "profile-001",
        "id": "31569347-9369-43ec-aa6a-59ea9c624a6f",
        "modified": 1594975851631,
        "origin": 1602168089665565200,
        "valueType": "Float32",
        "value": "39.5"
      },
      {
        "create": 1594876281221,
        "deviceName": "device-001",
        "resourceName": "resource-001",
        "profileName": "profile-001",
        "id": "2fd73a5b-969f-483c-9c52-6bb460a06eb1",
        "origin": 1602168089665565200,
        "valueType": "Int8",
        "value": "75"
      }
    ]
  }
}

-- core_data.device_info is used to store the device related information reusing in the event and reading
CREATE TABLE IF NOT EXISTS core_data.device_info (
    id SERIAL PRIMARY KEY,
    devicename TEXT,
    profilename TEXT,
    sourcename TEXT,
    tags JSONB,
    resourcename TEXT,
    valuetype TEXT DEFAULT '',
    units TEXT DEFAULT '',
    mediatype TEXT
);

-- core_data.event is used to store the event information
CREATE TABLE IF NOT EXISTS core_data.event (
    id UUID,
    origin BIGINT,
    device_info_id SERIAL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_event_id_origin
    ON core_data.event(id, origin);

-- core_data.reading is used to store the reading information
CREATE TABLE IF NOT EXISTS core_data.reading (
    event_id UUID,
    device_info_id SERIAL,
    origin BIGINT,
    value TEXT,
    binaryvalue BYTEA,
    objectvalue JSONB
);

CREATE INDEX IF NOT EXISTS idx_reading_origin
    ON core_data.reading(origin);