doc:jroboplc:modules:raduga_dbscr 

dbscr.raduga.init1:

  - if not has_domain({schema}, JROBO_RADUGA)
 
  - do CREATE DOMAIN {schema}JROBO_RADUGA AS INTEGER
 
  # RD_USER
  - |
    do CREATE TABLE {schema}RD_USER (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_USER PRIMARY KEY,
        NAME        VARCHAR(64) NOT NULL CONSTRAINT {schema}UQ_RD_USER_NAME UNIQUE,
        PSW         VARCHAR(64),
        SUPER       BOOLEAN,
        DELETED     BOOLEAN
      )

  # RD_ROLE
  - |
    do CREATE TABLE {schema}RD_ROLE (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_ROLE PRIMARY KEY,
        NAME        VARCHAR(64) NOT NULL CONSTRAINT {schema}UQ_RD_ROLE_NAME UNIQUE,
        DELETED     BOOLEAN
      )

  # RD_USER_ROLE
  - |
    do CREATE TABLE {schema}RD_USER_ROLE (
        USER_ID     INTEGER CONSTRAINT {schema}FK_RD_USERROLE_USER REFERENCES {schema}RD_USER ON DELETE CASCADE ON UPDATE CASCADE,
        ROLE_ID     INTEGER CONSTRAINT {schema}FK_RD_USERROLE_ROLE REFERENCES {schema}RD_ROLE ON DELETE CASCADE ON UPDATE CASCADE
      )

  # RD_PERM
  - |
    do CREATE TABLE {schema}RD_PERM (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_PERM PRIMARY KEY,
        NAME        VARCHAR(64) NOT NULL CONSTRAINT {schema}UQ_RD_PERM_NAME UNIQUE,
        DESCR       VARCHAR(128),
        DELETED     BOOLEAN
      )

  # RD_ROLE_PERM
  - |
    do CREATE TABLE {schema}RD_ROLE_PERM (
        ROLE_ID       INTEGER CONSTRAINT {schema}FK_RD_ROLEPERM_ROLE REFERENCES {schema}RD_USER ON DELETE CASCADE ON UPDATE CASCADE,
        PERM_ID       INTEGER CONSTRAINT {schema}FK_RD_ROLEPERM_PERM REFERENCES {schema}RD_PERM ON DELETE CASCADE ON UPDATE CASCADE
      )
 

  # RD_PRODUCT
  - |
    do CREATE TABLE {schema}RD_PRODUCT (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_PRODUCT PRIMARY KEY,
        NAME        VARCHAR(128) NOT NULL CONSTRAINT {schema}UQ_RD_PRODUCT_NAME UNIQUE,
        DELETED     BOOLEAN
      )

  # RD_PARAM
  - |
    do CREATE TABLE {schema}RD_PARAM (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_PARAM PRIMARY KEY,
        NAME        VARCHAR(32) NOT NULL CONSTRAINT {schema}UQ_RD_PARAM_NAME UNIQUE, 
        DESCR       VARCHAR(128),
        VALTYPE     CHAR(6) NOT NULL CONSTRAINT {schema}CH_RD_PARAM_VALTYPE CHECK (VALTYPE in ('BOOL','INT','LONG','DOUBLE','STRING')),
        DELETED     BOOLEAN
      )

  # RD_LINE
  - |
    do CREATE TABLE {schema}RD_LINE (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_LINE PRIMARY KEY,
        NAME        VARCHAR(32) NOT NULL CONSTRAINT {schema}UQ_RD_LINE_NAME UNIQUE, 
        DESCR       VARCHAR(128),
        HIDDEN      BOOLEAN,
        DELETED     BOOLEAN
      )
 

  # RD_DOSER
  - |
    do CREATE TABLE {schema}RD_DOSER (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_DOSER PRIMARY KEY,
        NAME        VARCHAR(32) NOT NULL CONSTRAINT {schema}UQ_RD_DOSER_NAME UNIQUE, 
        DELETED     BOOLEAN
      )

  # RD_LINE_DOSER
  - |
    do CREATE TABLE {schema}RD_LINE_DOSER (
        LINE_ID     INTEGER CONSTRAINT {schema}FK_RD_LINEDOSER_LINE REFERENCES {schema}RD_LINE ON DELETE CASCADE ON UPDATE CASCADE,
        DOSER_ID    INTEGER CONSTRAINT {schema}FK_RD_LINEDOSER_DOSER REFERENCES {schema}RD_DOSER ON DELETE CASCADE ON UPDATE CASCADE
    )

  # RD_BUNKER
  - |
    do CREATE TABLE {schema}RD_BUNKER (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_BUNKER PRIMARY KEY,
        NAME        VARCHAR(32) NOT NULL CONSTRAINT {schema}UQ_RD_BUNKER_NAME UNIQUE, 
        PRODUCT_ID  INTEGER CONSTRAINT {schema}FK_RD_BUNKER_PRODUCT REFERENCES {schema}RD_PRODUCT ON DELETE SET NULL ON UPDATE CASCADE,
        WEIGHT      BIGINT DEFAULT 0,
        DELETED     BOOLEAN
      )

  # RD_LINE_BUNKER
  - |
    do CREATE TABLE {schema}RD_LINE_BUNKER (
        LINE_ID     INTEGER CONSTRAINT {schema}FK_RD_LINEBUNKER_LINE REFERENCES {schema}RD_LINE ON DELETE CASCADE ON UPDATE CASCADE,
        BUNKER_ID   INTEGER CONSTRAINT {schema}FK_RD_LINEBUNKER_BUNKER REFERENCES {schema}RD_BUNKER ON DELETE CASCADE ON UPDATE CASCADE
      )

  # RD_FEEDER
  - |
    do CREATE TABLE {schema}RD_FEEDER (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_FEEDER PRIMARY KEY,
        DOSER_ID    INTEGER CONSTRAINT {schema}FK_RD_DOSERBUNKER_DOSER REFERENCES {schema}RD_DOSER ON DELETE CASCADE ON UPDATE CASCADE,
        FEEDER_NUM  SMALLINT,
        BUNKER_ID   INTEGER CONSTRAINT {schema}FK_RD_DOSERBUNKER_BUNKER REFERENCES {schema}RD_BUNKER ON DELETE CASCADE ON UPDATE CASCADE,
        DELETED     BOOLEAN
    )

  # RD_LINE_PARAM
  - |
    do CREATE TABLE {schema}RD_LINE_PARAM (
        LINE_ID     INTEGER CONSTRAINT {schema}FK_RD_LINEPARAM_LINE REFERENCES {schema}RD_LINE ON DELETE CASCADE ON UPDATE CASCADE,
        PARAM_ID    INTEGER CONSTRAINT {schema}FK_RD_LINEPARAM_PARAM REFERENCES {schema}RD_PARAM ON DELETE CASCADE ON UPDATE CASCADE
      )

  # RD_RECIPE
  - |
    do CREATE TABLE {schema}RD_RECIPE (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_RECIPE PRIMARY KEY,
        NAME        VARCHAR(128) NOT NULL CONSTRAINT {schema}UQ_RD_RECIPE_NAME UNIQUE,
        DESCR       VARCHAR(2048),
        PRODUCT_ID  INTEGER CONSTRAINT {schema}FK_RD_RECIPE_PRODUCT REFERENCES {schema}RD_PRODUCT,
        DT          TIMESTAMP,
        DELETED     BOOLEAN
      )

  # RD_RECIPE_PRODUCT
  - |
    do CREATE TABLE {schema}RD_RECIPE_PRODUCT (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_RECIPE_PRODUCT PRIMARY KEY,
        RECIPE_ID   INTEGER CONSTRAINT {schema}FK_RD_RECIPEPRODUCT_RECIPE REFERENCES {schema}RD_RECIPE ON DELETE CASCADE ON UPDATE CASCADE,
        PRODUCT_ID  INTEGER CONSTRAINT {schema}FK_RD_RECIPEPRODUCT_PRODUCT REFERENCES {schema}RD_PRODUCT,
        WEIGHT      BIGINT
      )

  # RD_RECIPE_PARAM
  - |
    do CREATE TABLE {schema}RD_RECIPE_PARAM (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_RECIPE_PARAM PRIMARY KEY,
        RECIPE_ID   INTEGER CONSTRAINT {schema}FK_RD_RECIPEPARAM_RECIPE REFERENCES {schema}RD_RECIPE ON DELETE CASCADE ON UPDATE CASCADE,
        PARAM_ID    INTEGER CONSTRAINT {schema}FK_RD_RECIPEPARAM_PARAM REFERENCES {schema}RD_PARAM,
        VAL         VARCHAR(32)
      )
 

  # RD_PLACE
  - |
    do CREATE TABLE {schema}RD_PLACE (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_PLACE PRIMARY KEY,
        NAME        VARCHAR(128) NOT NULL CONSTRAINT {schema}UQ_RD_PLACE_NAME UNIQUE,
        DELETED     BOOLEAN
      )

  # RD_SHIFT
  - |
    do CREATE TABLE {schema}RD_SHIFT (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_SHIFT PRIMARY KEY,
        USER_ID     INTEGER CONSTRAINT {schema}FK_RD_SHIFT_USER REFERENCES {schema}RD_USER,
        PLACE_ID    INTEGER CONSTRAINT {schema}FK_RD_SHIFT_PLACE REFERENCES {schema}RD_PLACE,
        DT_OPEN     TIMESTAMP,
        DT_CLOSE    TIMESTAMP
      )

  # RD_SHIFT_INPUT
  - |
    do CREATE TABLE {schema}RD_SHIFT_INPUT (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_SHIFTINPUT PRIMARY KEY,
        SHIFT_ID    INTEGER CONSTRAINT {schema}FK_RD_SHIFTINPUT_SHIFT REFERENCES {schema}RD_SHIFT ON DELETE CASCADE ON UPDATE CASCADE,
        PRODUCT_ID  INTEGER CONSTRAINT {schema}FK_RD_SHIFTINPUT_PRODUCT REFERENCES {schema}RD_PRODUCT,
        BUNKER_ID   INTEGER CONSTRAINT {schema}FK_RD_SHIFTINPUT_BUNKER REFERENCES {schema}RD_BUNKER,
        DT          TIMESTAMP,
        WEIGHT      BIGINT
      )

  # RD_SHIFT_OUTPUT
  - |
    do CREATE TABLE {schema}RD_SHIFT_OUTPUT (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_SHIFTOUTPUT PRIMARY KEY,
        SHIFT_ID    INTEGER CONSTRAINT {schema}FK_RD_SHIFTOUTPUT_SHIFT REFERENCES {schema}RD_SHIFT ON DELETE CASCADE ON UPDATE CASCADE,
        PRODUCT_ID  INTEGER CONSTRAINT {schema}FK_RD_SHIFTOUTPUT_PRODUCT REFERENCES {schema}RD_PRODUCT,
        BUNKER_ID   INTEGER CONSTRAINT {schema}FK_RD_SHIFTOUTPUT_BUNKER REFERENCES {schema}RD_BUNKER,
        DT          TIMESTAMP,
        WEIGHT      BIGINT
      )

  # RD_TASK
  - |
    do CREATE TABLE {schema}RD_TASK (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_TASK PRIMARY KEY,
        LINE_ID     INTEGER CONSTRAINT {schema}FK_RD_TASK_LINE REFERENCES {schema}RD_LINE,
        RECIPE_ID   INTEGER CONSTRAINT {schema}FK_RD_TASK_RECIPE REFERENCES {schema}RD_RECIPE,
        PRODUCT_ID  INTEGER CONSTRAINT {schema}FK_RD_TASK_PRODUCT REFERENCES {schema}RD_PRODUCT,
        BUNKER_ID   INTEGER CONSTRAINT {schema}FK_RD_TASK_BUNKER REFERENCES {schema}RD_BUNKER,
        USER_ID     INTEGER CONSTRAINT {schema}FK_RD_TASK_USER REFERENCES {schema}RD_USER,
        SHIFT_ID    INTEGER CONSTRAINT {schema}FK_RD_TASK_SHIFT REFERENCES {schema}RD_SHIFT,
        DT_CREATE   TIMESTAMP,
        DT_EXEC     TIMESTAMP,
        DT_FINISH   TIMESTAMP,
        SET_WEIGHT_TOT BIGINT,
        STATUS      INTEGER
      )

  # RD_TASK_PRODUCT
  - |
    do CREATE TABLE {schema}RD_TASK_PRODUCT (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_TASKPRODUCT PRIMARY KEY,
        TASK_ID     INTEGER CONSTRAINT {schema}FK_RD_TASKPRODUCT_TASK REFERENCES {schema}RD_TASK,
        PRODUCT_ID  INTEGER CONSTRAINT {schema}FK_RD_TASKPRODUCT_PRODUCT REFERENCES {schema}RD_PRODUCT,
        BUNKER_ID   INTEGER CONSTRAINT {schema}FK_RD_TASKPRODUCT_BUNKER REFERENCES {schema}RD_BUNKER,
        WEIGHT      BIGINT
      )

  # RD_TASK_PARAM
  - |
    do CREATE TABLE {schema}RD_TASK_PARAM (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_TASKPARAM PRIMARY KEY,
        TASK_ID     INTEGER CONSTRAINT {schema}FK_RD_TASKPARAM_TASK REFERENCES {schema}RD_TASK,
        PARAM_ID    INTEGER CONSTRAINT {schema}FK_RD_TASKPARAM_PRODUCT REFERENCES {schema}RD_PARAM,
        VAL         VARCHAR(32)
      )

  # RD_TASK_COMMENT
  - |
    do CREATE TABLE {schema}RD_TASK_COMMENT (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_TASKCOMMENT PRIMARY KEY,
        TASK_ID     INTEGER CONSTRAINT {schema}FK_RD_TASKCOMMENT_TASK REFERENCES {schema}RD_TASK,
        USER_ID     INTEGER CONSTRAINT {schema}FK_RD_TASKCOMMENT_USER REFERENCES {schema}RD_USER,
        TEXT        VARCHAR(2048)
      )

  # RD_TASK_EXEC
  - |
    do CREATE TABLE {schema}RD_TASK_EXEC (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_TASKEXEC PRIMARY KEY,
        TASK_ID     INTEGER CONSTRAINT {schema}FK_RD_TASKEXEC_TASK REFERENCES {schema}RD_TASK,
        FEEDER_ID   INTEGER CONSTRAINT {schema}FK_RD_TASKEXEC_FEEDER REFERENCES {schema}RD_FEEDER,
        CYCLE_ID    INTEGER,
        DT          TIMESTAMP,
        WEIGHT      BIGINT,
        SUM_WEIGHT_BEG BIGINT,
        SUM_WEIGHT_END BIGINT
      )

  # RD_TASK_CTL
  - |
    do CREATE TABLE {schema}RD_TASK_CTL (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT {schema}PK_RD_TASKCTL PRIMARY KEY,
        TASK_ID     INTEGER CONSTRAINT {schema}FK_RD_TASKCTL_TASK REFERENCES {schema}RD_TASK,
        CYCLE_ID    INTEGER,
        DT          TIMESTAMP,
        WEIGHT      BIGINT
      )

  - do CREATE UNIQUE DESCENDING INDEX {schema}IX_RD_TASKEXEC_ID_DSC ON {schema}RD_TASK_EXEC (ID);
 
  - do CREATE SEQUENCE {schema}SQ_RD_CYCLE_ID
  - do ALTER SEQUENCE {schema}SQ_RD_CYCLE_ID RESTART WITH 0
 
  - do CREATE DOMAIN {schema}JROBO_RADUGA_INIT_1 AS INTEGER
 

dbscr.raduga.init2:

  - if has_domain({schema}, JROBO_RADUGA_INIT_1)
 
  - do DROP DOMAIN {schema}JROBO_RADUGA_INIT_1
  - do INSERT INTO {schema}RD_USER (ID, NAME, PSW) VALUES (1, 'Supervisor', '')
  - do INSERT INTO {schema}RD_USER (ID, NAME, PSW) VALUES (2, 'Оператор', '')
  • doc/jroboplc/modules/raduga_dbscr.txt
  • Last modified: 2023/09/27 19:44
  • by 127.0.0.1