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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
|
-- PostgreSQL Database creation script
CREATE TABLE user_table (
email_user varchar (100) NOT NULL,
gecos_user varchar (150),
cookie_delay_user int4,
password_user varchar (40),
last_login_date_user int4,
last_login_host_user varchar (60),
wrong_login_count_user int4,
lang_user varchar (10),
attributes_user varchar (255),
data_user varchar (255),
CONSTRAINT ind_user PRIMARY KEY (email_user)
);
CREATE TABLE subscriber_table (
list_subscriber varchar (50) NOT NULL,
user_subscriber varchar (100) NOT NULL,
custom_attribute_subscriber varchar (500),
robot_subscriber varchar (80) NOT NULL,
date_subscriber timestamp with time zone NOT NULL,
update_subscriber timestamp with time zone,
visibility_subscriber varchar (20),
reception_subscriber varchar (20),
topics_subscriber varchar (200),
bounce_subscriber varchar (35),
bounce_score_subscriber int4,
bounce_address_subscriber varchar (100),
comment_subscriber varchar (150),
subscribed_subscriber smallint,
included_subscriber smallint,
include_sources_subscriber varchar(50),
suspend_subscriber smallint,
suspend_start_date_subscriber int4,
suspend_end_date_subscriber int4,
CONSTRAINT ind_subscriber PRIMARY KEY (robot_subscriber,list_subscriber,user_subscriber)
);
CREATE INDEX subscriber_idx ON subscriber_table (robot_subscriber,list_subscriber,user_subscriber);
CREATE INDEX subscriber_idx2 ON subscriber_table (user_subscriber);
CREATE TABLE admin_table (
list_admin varchar(50) NOT NULL,
user_admin varchar(100) NOT NULL,
robot_admin varchar(80) NOT NULL,
role_admin varchar(15) NOT NULL,
date_admin timestamp with time zone NOT NULL,
update_admin timestamp with time zone,
reception_admin varchar(20),
visibility_admin varchar(20),
comment_admin varchar(150),
subscribed_admin smallint,
included_admin smallint,
include_sources_admin varchar(50),
info_admin varchar(150),
profile_admin varchar(15),
CONSTRAINT ind_admin PRIMARY KEY (robot_admin, list_admin, role_admin, user_admin)
);
CREATE INDEX admin_idx ON admin_table(robot_admin, list_admin, role_admin, user_admin);
CREATE INDEX admin_idx2 ON admin_table(user_admin);
CREATE TABLE exclusion_table (
list_exclusion varchar(50),
user_exclusion varchar(100),
date_exclusion int4,
CONSTRAINT ind_exclusion PRIMARY KEY (list_exclusion, user_exclusion)
);
CREATE INDEX exclusion_idx ON exclusion_table(list_exclusion, user_exclusion);
CREATE TABLE netidmap_table (
netid_netidmap varchar (100) NOT NULL,
serviceid_netidmap varchar (100) NOT NULL,
robot_netidmap varchar (80) NOT NULL,
email_netidmap varchar (100),
CONSTRAINT ind_netidmap PRIMARY KEY (netid_netidmap, serviceid_netidmap, robot_netidmap)
);
CREATE INDEX netidmap_idx ON netidmap_table(netid_netidmap, serviceid_netidmap, robot_netidmap);
CREATE TABLE logs_table (
id_logs bigint NOT NULL,
date_logs int4 NOT NULL,
robot_logs varchar (80),
list_logs varchar (50),
action_logs varchar (50) NOT NULL,
parameters_logs varchar (100),
target_email_logs varchar (100),
user_email_logs varchar (100),
msg_id_logs varchar (255),
status_logs varchar (10) NOT NULL,
error_type_logs varchar (150),
client_logs varchar (100),
daemon_logs varchar (10) NOT NULL,
CONSTRAINT ind_logs PRIMARY KEY (id_logs)
);
CREATE INDEX logs_idx ON logs_table(id_logs);
CREATE TABLE session_table (
id_session int8 NOT NULL,
start_date_session int4 NOT NULL,
date_session int4 NOT NULL,
remote_addr_session varchar(60),
robot_session varchar(80),
email_session varchar(100),
hit_session int4,
data_session varchar(255),
CONSTRAINT ind_session PRIMARY KEY (id_session)
);
CREATE INDEX session_idx ON session_table(id_session);
CREATE TABLE one_time_ticket_table (
ticket_one_time_ticket varchar(30),
robot_one_time_ticket varchar(80),
email_one_time_ticket varchar(100),
date_one_time_ticket bigint,
data_one_time_ticket varchar(200),
remote_addr_one_time_ticket varchar(60),
status_one_time_ticket varchar(60),
CONSTRAINT ind_one_time_ticket PRIMARY KEY (ticket_one_time_ticket)
);
CREATE INDEX one_time_ticket_idx ON one_time_ticket_table(ticket_one_time_ticket);
CREATE TABLE bulkmailer_table(
messagekey_bulkmailer varchar(80),
packetid_bulkmailer varchar(33),
messageid_bulkmailer varchar(100),
receipients_bulkmailer text,
returnpath_bulkmailer varchar(100),
robot_bulkmailer varchar(80),
listname_bulkmailer varchar(50),
verp_bulkmailer smallint,
merge_bulkmailer smallint,
priority_message_bulkmailer int4,
priority_packet_bulkmailer int4,
reception_date_bulkmailer int4,
delivery_date_bulkmailer int4,
lock_bulkmailer varchar(30),
constraint ind_bulkmailer PRIMARY KEY (messagekey_bulkmailer, packetid_bulkmailer)
);
CREATE INDEX messagekey_bulkmailer_idx ON bulkmailer_table(messagekey_bulkmailer);
CREATE INDEX packetid_bulkmailer_idx ON bulkmailer_table(packetid_bulkmailer);
CREATE TABLE bulkspool_table (
messagekey_bulkspool varchar(33),
messageid_bulkspool varchar(100),
message_bulkspool text,
lock_bulkspool smallint,
dkim_privatekey_bulkspool varchar(100),
dkim_selector_bulkspool varchar(50),
dkim_d_bulkspool varchar(50),
dkim_i_bulkspool varchar(50),
dkim_header_list_bulkspool varchar(500),
constraint ind_bulkspool primary key (messagekey_bulkspool)
);
CREATE INDEX messagekey_bulkspool_idx ON bulkspool_table(messagekey_bulkspool);
CREATE TABLE conf_table (
robot_conf varchar(80),
label_conf varchar(80),
value_conf varchar(300),
constraint ind_conf primary key (robot_conf,label_conf)
);
CREATE INDEX robot_conf_idx ON conf_table(robot_conf,label_conf);
|
