Create.DMA.Table.sql
4.41 KB
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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
--------------- CREATE TABLE DMA MESSAGE
-- Table: public.dma_message
CREATE SEQUENCE public.dma_message_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
ALTER SEQUENCE public.dma_message_id_seq
OWNER TO wdchat;
CREATE SEQUENCE public.dma_message_user_rel_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
ALTER SEQUENCE public.dma_message_user_rel_id_seq
OWNER TO wdchat;
-- DROP TABLE public.dma_message;
CREATE TABLE public.dma_message
(
id integer NOT NULL DEFAULT nextval('dma_message_id_seq'::regclass),
key character varying(100) COLLATE pg_catalog."default",
"fromUserId" integer,
"toUserId" integer,
"contactIds" character varying(2000) COLLATE pg_catalog."default",
message character varying(4096) COLLATE pg_catalog."default",
sent boolean DEFAULT false,
delivered boolean DEFAULT false,
read boolean DEFAULT false,
"createdAtTime" bigint,
type integer,
source integer,
status integer NOT NULL DEFAULT 0,
paired_message_id integer,
"contentType" integer,
group_id integer,
group_name character varying(200) COLLATE pg_catalog."default",
from_user_name character varying(80) COLLATE pg_catalog."default",
metadata character varying(100) COLLATE pg_catalog."default",
delivered_time timestamp without time zone,
"createdDate" timestamp without time zone DEFAULT now(),
"updatedDate" timestamp without time zone DEFAULT now(),
"deviceKey" character varying(200) COLLATE pg_catalog."default",
shared boolean DEFAULT false,
"storeOnDevice" boolean DEFAULT false,
"sentToServer" boolean DEFAULT false,
"sendToDevice" boolean DEFAULT false,
"sentMessageTimeAtServer" integer,
"documentId" character varying COLLATE pg_catalog."default",
delete boolean DEFAULT false,
"paredUserId" character varying(100) COLLATE pg_catalog."default",
CONSTRAINT dma_message_pkey PRIMARY KEY (id),
CONSTRAINT dma_message_fromuserid_wd_user_fk FOREIGN KEY ("fromUserId")
REFERENCES public.wd_user (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT dma_message_togroupid_wd_group_fk FOREIGN KEY (group_id)
REFERENCES public.wd_group (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT dma_message_touserid_wd_user_fk FOREIGN KEY ("toUserId")
REFERENCES public.wd_user (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.dma_message
OWNER to wdchat;
-- Index: dma_message_idx_createdattime
-- DROP INDEX public.dma_message_idx_createdattime;
CREATE INDEX dma_message_idx_createdattime
ON public.dma_message USING btree
("createdAtTime")
TABLESPACE pg_default;
-- Table: public.dma_message_user_rel
-- DROP TABLE public.dma_message_user_rel;
CREATE TABLE public.dma_message_user_rel
(
id integer NOT NULL DEFAULT nextval('dma_message_user_rel_id_seq'::regclass),
"messageId" integer NOT NULL,
"userId" integer NOT NULL,
read boolean DEFAULT false,
delete boolean DEFAULT false,
"createdDate" timestamp without time zone NOT NULL DEFAULT now(),
"updatedDate" timestamp without time zone NOT NULL DEFAULT now(),
delivered boolean DEFAULT false,
"deliveredAtTime" timestamp without time zone,
"readAtTime" timestamp without time zone,
blocked boolean DEFAULT false,
"blockedAtTime" timestamp without time zone,
"deleteAtTime" timestamp without time zone,
CONSTRAINT dma_message_user_rel_pk PRIMARY KEY (id),
CONSTRAINT dma_message_user_rel_dma_message_fk FOREIGN KEY ("messageId")
REFERENCES public.dma_message (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT dma_message_user_rel_wd_user_fk FOREIGN KEY ("userId")
REFERENCES public.wd_user (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.dma_message_user_rel
OWNER to wdchat;
-- Index: dma_message_user_rel_constain_unique
-- DROP INDEX public.dma_message_user_rel_constain_unique;
CREATE UNIQUE INDEX dma_message_user_rel_constain_unique
ON public.dma_message_user_rel USING btree
("messageId" DESC, "userId" DESC)
TABLESPACE pg_default;
-------------------------------------------------------