CQRS LDC

|
Programming/ CQRS
| CQRS

CQRS LDC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE IF NOT EXISTS CQRS_LDC_EVENT (
    ID VARCHAR(36) NOT NULL,
    AGGREGATE_ID VARCHAR(36) NOT NULL,
    REVISION BIGINT NOT NULL,
    -- ENTINY_ID VARCHAR(36) NOT NULL,
    -- ENTINY_TYPE_ID VARCHAR(64) NOT NULL,

    EVENT JSON NOT NULL CHECK (JSON_VALID(EVENT)),
    TIMESTAMP DATETIME(6) NOT NULL DEFAULT LOCALTIMESTAMP,

 -- EVENT JSONB NOT NULL,
 -- EVENT JSON NOT NULL CHECK (JSON_VALID(EVENT)),
 -- EVENT NVARCHAR(MAX) NOT NULL CHECK (ISJSON(EVENT)=1)
 -- TIMESTAMP TIMESTAMP(6) NOT NULL DEFAULT LOCALTIMESTAMP,
 -- TIMESTAMP DATETIME(6) NOT NULL DEFAULT LOCALTIMESTAMP,
 -- TIMESTAMP DATETIME2(6) NOT NULL DEFAULT SYSDATETIME,

    IS_PUBLISHED BOOLEAN NOT NULL DEFAULT TRUE, -- BOOLEAN, BIT
    CORRELATION_ID VARCHAR(36),

    CONSTRAINT CQRS_LDC_EVENT_PK PRIMARY KEY(ID),
    CONSTRAINT CQRS_LDC_EVENT_AGGREGATE_ID_REVISION UNIQUE (AGGREGATE_ID, REVISION)
);

CREATE TABLE IF NOT EXISTS CQRS_LDC_SNAPSHOT (
    ID VARCHAR(36) NOT NULL,
    AGGREGATE_ID VARCHAR(36) NOT NULL,
    REVISION BIGINT NOT NULL,

    STATE JSONB NOT NULL,
    TIMESTAMP TIMESTAMP(6) NOT NULL DEFAULT LOCALTIMESTAMP,

  --STATE JSONB NOT NULL,
  --STATE JSON NOT NULL CHECK (JSON_VALID(STATE)),
  --STATE NVARCHAR(MAX) NOT NULL CHECK (ISJSON(STATE)=1)
  --TIMESTAMP TIMESTAMP(6) NOT NULL DEFAULT LOCALTIMESTAMP,
  --TIMESTAMP DATETIME(6) NOT NULL DEFAULT LOCALTIMESTAMP,
  --TIMESTAMP DATETIME2(6) NOT NULL DEFAULT SYSDATETIME,

    CONSTRAINT CQRS_LDC_SNAPSHOT_PK PRIMARY KEY(ID),
    CONSTRAINT CQRS_LDC_SNAPSHOT_AGGREGATE_ID_REVISION UNIQUE (AGGREGATE_ID, REVISION)
);