conf.yaml

db: cis.db
# Если используется несколько автоматизированных систем, объединенных 
# шлюзом обмена данными, создаются несколько схем в базе данных, которые
# объединяются роутером для получения данных из разных топиков
# имя системы в модель не входит, т.к. является внешним признаком.
# Порядок создания таблиц
create:
  order:
    sys: 1
    dpt: 1
    vt: 1
    grp: 1
    dpe: 1
    dp: 1
    sg: 1
    sg_link: 1
    sg_tree: 1
    alm_lim_type: 1
    alm: 1
    iec_addr: 1
    vl_cur: 0
    vl: 0
# Список систем для которых создаются типы объектов
  sys: |
    CREATE TABLE IF NOT EXISTS sys (
      id INTEGER PRIMARY KEY,
      name TEXT UNIQUE NOT NULL,
      dsc TEXT,
      disabled INTEGER DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME
    );
# Типы объектов
  dpt: |
    CREATE TABLE IF NOT EXISTS dpt (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      dsc TEXT,
      sys INTEGER NOT NULL,
      disabled INTEGER  DEFAULT NULL,
      created_at DATETIME,
      updated_at DATETIME,
      UNIQUE (name, sys),
      FOREIGN KEY (sys) REFERENCES sys(id) ON DELETE CASCADE
    );
# Типы сигналов в типе объекта
  dpe: |
    CREATE TABLE IF NOT EXISTS dpe (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      dsc TEXT,
      dpt INTEGER NOT NULL,
      grp INTEGER,
      disabled INTEGER  DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME,
      UNIQUE (dpt, name),
      FOREIGN KEY (dpt) REFERENCES dpt(id) ON DELETE CASCADE,
      FOREIGN KEY (grp) REFERENCES grp(id) ON UPDATE CASCADE
    );
# Группы для типов сигналов
  grp: |
    CREATE TABLE IF NOT EXISTS grp (
      id INTEGER PRIMARY KEY,
      parent INTEGER,
      vt INTEGER,
      name TEXT UNIQUE NOT NULL,
      dsc TEXT,
      unit INTEGER,
      fmt TEXT,
      created_at DATETIME,
      updated_at DATETIME,
      FOREIGN KEY (vt) REFERENCES vt(id)      
      FOREIGN KEY (parent) REFERENCES grp(id)
    );
# Типы значений 
  vt: |
    CREATE TABLE IF NOT EXISTS vt (
      id INTEGER PRIMARY KEY,
      name TEXT UNIQUE NOT NULL,
      dsc TEXT,
      created_at DATETIME,
      updated_at DATETIME
    );
# Технологические объекты
  dp: |
    CREATE TABLE IF NOT EXISTS dp (
      id INTEGER PRIMARY KEY,
      name TEXT UNIQUE NOT NULL,
      dsc TEXT,
      dpt INTEGER NOT NULL,
      disabled INTEGER  DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME,
      FOREIGN KEY (dpt) REFERENCES dpt(id) ON DELETE CASCADE
    );
# Сигналы, как сочетание объекта и типа сигнала
# Уникальность сигнала определяется сочетанием идентификатора объекта
# и типа сигнала. Номер сигнала можно отнести к разным сочетаниям
# поэтому можно один и тот же сигнал использовать в разных системах,
# т.к. сигнал передается с этим номером. Id нужен для связей с другими 
# таблицами. Алармы формируются по id, если alarm общий для двух систем,
# можно найти id по всем sg равным требуемому, если раздельный, то найти только свой.
# Свойства сигнала (тип, архивация, хранение в оперативной базе определяется в отдельной 
# таблице, аналогичной адресу или берется из типа одной из моделей (что удобнее, т.к.
# это унифицированно)
  sg: |
    CREATE TABLE IF NOT EXISTS sg (
      id INTEGER PRIMARY KEY,
      dp INTEGER,
      dpe INTEGER,
      name TEXT,
      dsc TEXT,
      grp INTEGER,
      disabled INTEGER DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME,
      UNIQUE(dp, dpe),
      FOREIGN KEY (dp) REFERENCES dp(id) ON DELETE SET NULL,
      FOREIGN KEY (dpe) REFERENCES dpe(id) ON DELETE SET NULL,
      FOREIGN KEY (grp) REFERENCES grp(id) ON UPDATE CASCADE
    );
# Таблица связей сигнала и его источника, заполняется в случае, если нужно иметь такой источник
# Можно указывать несколько источников для одного сигнала, в том числе его самого
  sg_link: |
    CREATE TABLE IF NOT EXISTS sg_link (
      id INTEGER PRIMARY KEY,
      source INTEGER NOT NULL,
      target INTEGER NOT NULL,
      disabled INTEGER DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME,
      UNIQUE(source, target),
      FOREIGN KEY (source) REFERENCES sg(id) ON DELETE CASCADE,
      FOREIGN KEY (target) REFERENCES sg(id) ON DELETE CASCADE      
    );
# Таблица связей сигналов в дерево для поиска
# может использоваться для добавления сигналов из списков без типизации
# например, из КП ИО
  sg_tree: |
    CREATE TABLE IF NOT EXISTS sg_tree (
      id INTEGER PRIMARY KEY,
      child INTEGER NOT NULL,
      parent INTEGER NOT NULL,
      name TEXT,
      disabled INTEGER DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME,
      UNIQUE(child, parent),
      FOREIGN KEY (parent) REFERENCES sg(id) ON DELETE CASCADE,
      FOREIGN KEY (child) REFERENCES sg(id) ON DELETE CASCADE
    );
# Таблица для генерации алармов
  alm: |
    CREATE TABLE IF NOT EXISTS alm (
      id INTEGER PRIMARY KEY,
      sg INTEGER NOT NULL,
      id_lim INTEGER NOT NULL,
      lim_type INTEGER NOT NULL,
      val REAL,
      msg TEXT,
      hyst_h REAL,
      hyst_l REAL,
      severity INTEGER,
      disabled INTEGER DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME,
      UNIQUE (sg, id_lim),
      FOREIGN KEY (sg) REFERENCES sg(id) ON DELETE CASCADE,
      FOREIGN KEY (lim_type) REFERENCES alm_lim_type(id)
    );
# Таблица возможных названий диапазонов алармов    
  alm_lim_type: >
    CREATE TABLE IF NOT EXISTS alm_lim_type (
      id INTEGER PRIMARY KEY,
      name TEXT UNIQUE NOT NULL,
      dsc TEXT,
      created_at DATETIME,
      updated_at DATETIME      
    );
# Таблица адресов сигналов ГОСТ Р МЭК 60870-5-104
# направление определяется asdu и драйвером (кроме cot)
  iec_addr: >
    CREATE TABLE IF NOT EXISTS iec_addr (
      id INTEGER PRIMARY KEY,  -- связь 1:1
      asdu INTEGER,
      ca INTEGER,
      ioa INTEGER,
      is_cot INTEGER CHECK (is_cot is NULL OR is_cot = 1),
      th REAL,    -- threshold, порог отклонения для передачи (deadband)
      conv TEXT,  -- кусочно-линейная функция: 4.2|0|18.5|10 (4.2мА - 0, 20мА - 10)
      created_at DATETIME,
      updated_at DATETIME,
      UNIQUE (asdu, ca, ioa),
      FOREIGN KEY (id) REFERENCES sg(id) ON DELETE CASCADE
    );
# Текущие значения сигналов
  val: >
    CREATE TABLE IF NOT EXISTS val (
      sg INTEGER PRIMARY KEY,       -- сигнал
      ts INTEGER NOT NULL,          -- миллисекунды
      seq INT DEFAULT NULL,         -- порядок, если пришло несколько сигналов с одинаковой меткой времени
      val REAL NOT NULL,
      q INTEGER NOT NULL,
      FOREIGN KEY (sg) REFERENCES sg(id) ON DELETE CASCADE
    );
# История изменений значений сигналов
# seq - порядок, если сигналы пришли с одной и той же меткой времени
# ts - INTEGER с миллисекундами
  val_hist: >
    CREATE TABLE IF NOT EXISTS val_hist (
      sg INTEGER NOT NULL,
      ts INTEGER NOT NULL,
      seq INT DEFAULT NULL,
      val REAL NOT NULL,
      q INTEGER NOT NULL,
      PRIMARY KEY (sg, ts, seq),
      FOREIGN KEY (sg) REFERENCES sg(id)  
    );
# Общие триггеры для создания всех таблиц конфигурации
  trg_ins: >
    CREATE TRIGGER IF NOT EXISTS {tbl}_before_insert
      AFTER INSERT ON {tbl}
      BEGIN
          UPDATE {tbl}
          SET
              created_at = COALESCE(NEW.created_at, datetime('now')),
              updated_at = COALESCE(NEW.updated_at, datetime('now'))
          WHERE rowid = NEW.rowid;
      END;
  trg_upd: >
    CREATE TRIGGER IF NOT EXISTS {tbl}_after_update
        AFTER UPDATE ON {tbl}
        BEGIN
            UPDATE {tbl}
            SET updated_at = datetime('now')
            WHERE rowid = NEW.rowid;
        END;
upd:
  sys: 
    sql: |
      INSERT INTO sys(id, name, dsc)
      VALUES (:id, 
              :name, 
              :dsc)
      ON CONFLICT (id) DO UPDATE SET
        name = excluded.name,
        dsc = excluded.dsc;
    required: [id, name]
    fname: sys  
  vt:
    sql: |
      INSERT INTO vt(id, name, dsc)
      VALUES (:id, 
              :name, 
              :dsc)
      ON CONFLICT (id) DO UPDATE SET
        name = excluded.name,
        dsc = excluded.dsc;
    required: [id, name]
    fname: vt    
  dpt: 
    sql: |
      INSERT INTO dpt (id, sys, name, dsc)
      VALUES (:id,
              (SELECT id FROM sys WHERE name = :sys),
              :name,
              :dsc)
      ON CONFLICT (id) DO UPDATE SET
        sys = excluded.sys,
        name = excluded.name,
        dsc = excluded.dsc;
    required: [id, sys, name]
    fname: dpt
  dpe: 
    sql: |
      INSERT INTO dpe (id, dpt, name, dsc, grp)
      VALUES (:id,
              (SELECT dpt.id FROM dpt
               JOIN sys ON dpt.sys = sys.id
               WHERE sys.name = :sys AND dpt.name = :dpt),
              :name,
              :dsc, 
              :grp)
      ON CONFLICT (id) DO UPDATE SET
        dpt = excluded.dpt,
        name = excluded.name,
        dsc = excluded.dsc,
        grp = excluded.grp;
    required: [sys, dpt, id, name]
    fname: dpe
  grp:
    sql: |
      INSERT INTO grp (id, vt, name, dsc, unit, fmt, parent)
      VALUES (:id,
              (SELECT vt.id FROM vt
               WHERE vt.name = :vt),
              :name,
              :dsc,
              :unit,
              :fmt,
              :parent
              )
      ON CONFLICT (id) DO UPDATE SET
        vt = excluded.vt,
        name = excluded.name,
        dsc = excluded.dsc,
        unit = excluded.unit,
        fmt = excluded.fmt,
        parent = excluded.parent;
  dp:
    sql: |
      INSERT INTO dp (id, dpt, name, dsc)
      VALUES (:id,
              (SELECT dpt.id FROM dpt
              JOIN sys ON dpt.sys = sys.id
              WHERE sys.name = :sys AND dpt.name = :dpt),
              :name,
              :dsc
              )
      ON CONFLICT (id) DO UPDATE SET
        dpt = excluded.dpt,
        name = excluded.name,
        dsc = excluded.dsc;
  sg:
    sql: |
      INSERT INTO sg (id, dp, dpe, name, dsc)
      VALUES (
          :id,
          (SELECT dp.id FROM dp WHERE dp.name = :dp),
          (SELECT dpe.id FROM dpe 
          JOIN dpt ON dpt.id = dpe.dpt
          JOIN sys ON sys.id = dpt.sys 
          WHERE dpe.name = :dpe AND sys.name = :sys),
          :name,
          :dsc
      )
      ON CONFLICT (id) DO UPDATE SET
          dp = excluded.dp,
          dpe = excluded.dpe,
          name = excluded.name,
          dsc = excluded.dsc
        ;
    fname: sg
  sg_rel:
    sql: |
      UPDATE sg 
      SET 
          dp = (SELECT id FROM dp 
          WHERE name = SUBSTR(sg.name, 
            INSTR(sg.name, ':') + 1, 
            INSTR(sg.name, '.') - INSTR(sg.name, ':') - 1)
          ),
          dpe = (SELECT id FROM dpe WHERE name = SUBSTR(sg.name, 
            INSTR(sg.name, '.') + 1  )
          )
      WHERE INSTR(sg.name, ':') > 0 AND INSTR(sg.name, '.') > 0;
  iec_addr:
    sql: |
      INSERT INTO iec_addr (id, asdu, ca, ioa, is_cot, th, conv)
      VALUES (:id, 
              :asdu,
              :ca,
              :ioa,
              :is_cot,
              :threshold,
              :conv
              )
      ON CONFLICT (id) DO UPDATE SET
        asdu = excluded.asdu,
        ca = excluded.ca,
        ioa = excluded.ioa,
        is_cot = excluded.is_cot,
        th = excluded.th,
        conv = excluded.conv;
    fname: iec_addr
    nullable: [is_cot, threshold, conv]
exp:
  sys:
    fname: 'ref/out/sys.csv'
    cols: [id, name, dsc]
    sql: |
      SELECT id, name, dsc FROM sys;
  dpt:
    fname: 'ref/out/dpt.csv'
    cols: [id, sys, dpt, dsc]
    sql: |
      SELECT dpt.id, sys.name, dpt.name, dpt.dsc FROM dpt
      JOIN sys ON dpt.sys = sys.id;
  dpe:
    fname: 'ref/out/dpe.csv'
    cols: [id, sys, dpt, name, dsc, grp]
    sql: |
      SELECT dpe.id, sys.name, dpt.name, dpe.name, dpe.dsc,
             dpe.grp FROM dpe
      JOIN dpt ON dpe.dpt = dpt.id
      JOIN sys ON dpt.sys = sys.id;
  dp:
    fname: 'ref/out/dp.csv'
    cols: [id, sys, dpt, name, dsc]
    sql: |
      SELECT dp.id, sys.name, dpt.name, dp.name, dp.dsc FROM dp
      JOIN dpt ON dp.dpt = dpt.id
      JOIN sys ON dpt.sys = sys.id;
  grp:
    fname: 'ref/out/grp.csv'
    cols: [id, parent, name, dsc, unit, fmt, type]
    sql: |
      SELECT c.id, p.name, c.name, c.dsc,
        c.unit, c.fmt, c.vt
        FROM grp c
        LEFT JOIN grp p ON p.id = c.parent;
  alm:
    fname: 'ref/out/alm.csv'
    cols: [id, sg, id_lim, alm_lim_type, val, msg, hyst_h, hyst_l, severity]
    sql: |
      SELECT a.id, a.sg, a.id_lim, t.name, a.val, a.msg,
             a.hyst_h, a.hyst_l, a.severity
        FROM alm a
        JOIN alm_lim_type t ON a.lim_type = t.id
        ORDER BY a.sg, a.id_lim;
  sg:
    fname: 'ref/out/sg.csv'
    cols: [id, sys, dp, dpe, name, dsc]
    sql: |
      SELECT sg.id, sys.name, dp.name, dpe.name, 
             sg.name, sg.dsc
      FROM sg
      JOIN dp ON dp.id = sg.dp
      JOIN dpe ON dpe.id = sg.dpe
      JOIN dpt ON dpt.id = dpe.dpt
      JOIN sys ON sys.id = dpt.sys;
  iec_addr:
    fname: 'ref/out/iec_addr'
    cols: [id, name, dsc, asdu, ca, ioa, is_cot, threshold, conv]
    sql: |
      SELECT a.id, sg.name, sg.dsc, 
             a.asdu, a.ca, a.ioa, a.is_cot, a.th, a.conv
             FROM iec_addr as a
             JOIN sg ON sg.id = a.id;
exp_wcc:
  dpt: |
    SELECT dpt.name, dpe.name, grp.type FROM dpt
    JOIN dpe ON dpe.dpt = dpt.id
    JOIN grp ON dpe.grp = grp.id
    ORDER BY dpt.name, dpe.name
  dp: |
    SELECT dp.name, dpt.name, 0 FROM dp
    JOIN dpt ON dpt.id = dp.dpt 
  distrib: |
    SELECT 
    ?, 
    dp.name || "." || dpe.name,
    dpt.name,
    ? as type,
    ? as drv 
    FROM sg as sg
    JOIN iec_addr ON sg.id = iec_addr.id
    JOIN dp ON dp.id = sg.dp
    JOIN dpe ON dpe.id = sg.dpe
    JOIN dpt ON dpt.id = dp.dpt 
  iec_addr: |
    SELECT 
    dp.name || "." || dpe.name,
    dpt.name,
    iec_addr.asdu,
    iec_addr.ca,
    iec_addr.ioa
    FROM sg as sg
    JOIN iec_addr ON sg.id = iec_addr.id
    JOIN dp ON dp.id = sg.dp
    JOIN dpe ON dpe.id = sg.dpe
    JOIN dpt ON dpt.id = dp.dpt 
other:
  sg: # импортируем сигналы без адресов, только список
  fname: 'ref/out/sg.csv'
  cols: ['id', 'name', 'dsc', 'asdu']
  sql: |
    SELECT
    ROW_NUMBER() OVER (ORDER BY dp.id, dpe.id) as id,
    sys.name || ":" || dp.name || '.' || dpe.name as name,
    dp.dsc || ' ' || dpe.dsc as dsc,
    
    grp.type as iec_asdu
    FROM dp
    JOIN dpe ON dpe.dpt = dp.dpt
    JOIN dpt ON dpe.dpt = dpt.id
    JOIN sys ON sys.id = dpt.sys
    JOIN grp ON grp.id = dpe.grp

read:
  sql: |
    SELECT 
      COALESCE(sg_link.source, sg.id) as s_id, 
      sg.name 
    FROM sg
    LEFT JOIN sg_link ON sg_link.source = sg.id