create_db.SQLite 3.45 KB
Newer Older
1
2
3
4
5
6
7
8

CREATE TABLE user_table (
  	email_user          	varchar (100) NOT NULL,
  	gecos_user          	varchar (150),
  	password_user		varchar (40),
	cookie_delay_user	integer,
	lang_user		varchar (10),
	attributes_user		varchar(255),
9
	data_user	        varchar(5000),
10
11
12
13
14
15
	PRIMARY KEY (email_user)
);

CREATE TABLE subscriber_table (
  	list_subscriber       	varchar (50) NOT NULL,
	user_subscriber		varchar (100) NOT NULL,
16
  	custom_attribute_subscriber varchar (500),
17
	robot_subscriber	varchar (80) NOT NULL,
18
19
20
21
22
23
	date_subscriber		timestamp NOT NULL,
	update_subscriber	timestamp,
	visibility_subscriber	varchar (20),
	reception_subscriber	varchar (20),
	topics_subscriber       varchar (200),
	bounce_subscriber	varchar (35),
24
	bounce_address_subscriber	varchar (100),
25
26
27
28
29
	comment_subscriber	varchar (150),
	subscribed_subscriber 	boolean,
	included_subscriber 	boolean,
	include_sources_subscriber varchar(50),
	bounce_score_subscriber integer,
30
	PRIMARY KEY (list_subscriber, user_subscriber, robot_subscriber)
31
);
32
CREATE INDEX subscriber_idx ON subscriber_table (user_subscriber,list_subscriber,robot_subscriber);
33
34
35
36

CREATE TABLE admin_table (
	list_admin 		varchar(50) NOT NULL,
 	user_admin 		varchar(100) NOT NULL,
37
 	robot_admin 		varchar(80) NOT NULL,
38
39
40
41
	role_admin 		varchar(15) NOT NULL,
	date_admin 		timestamp NOT NULL,
	update_admin 		timestamp,
	reception_admin 	varchar(20),
42
	visibility_admin 	varchar(20),
43
44
45
46
47
48
	comment_admin 		varchar(150),
	subscribed_admin  	boolean,
	included_admin  	boolean,
	include_sources_admin  	varchar(50),
	info_admin   		varchar(150),
	profile_admin  		varchar(15),
sympa-authors's avatar
Fixed    
sympa-authors committed
49
	PRIMARY KEY (list_admin, user_admin, robot_admin, role_admin)
50
);
51
CREATE	INDEX admin_idx ON admin_table(list_admin, user_admin, robot_admin, role_admin);
52
53
54

CREATE TABLE netidmap_table (
        netid_netidmap              varchar (100) NOT NULL,
55
	serviceid_netidmap	    varchar (100) NOT NULL,
56
57
	robot_netidmap	            varchar (80) NOT NULL,
        email_netidmap              varchar (100),
58
        PRIMARY KEY (netid_netidmap, serviceid_netidmap, robot_netidmap)
59
);
60
CREATE	INDEX netidmap_idx ON netidmap_table(netid_netidmap, serviceid_netidmap, robot_netidmap);
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

CREATE TABLE logs_table (
	id_logs			integer NOT NULL,
	date_logs		integer 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,
	PRIMARY KEY (id_logs)					  
);
CREATE	INDEX logs_idx ON logs_table(id_logs);
79
80

CREATE TABLE session_table (
81
	id_session			varchar(30) NOT NULL,
82
83
84
85
86
87
	start_date_session		integer NOT NULL,
	date_session		        integer NOT NULL,
	remote_addr_session		varchar(60),
	robot_session		        varchar(80),
	email_session		        varchar(100),
	hit_session     	        integer,
88
	data_session		        varchar(5000),
89
90
91
92
	constraint ind_session primary key (id_session)
)
CREATE INDEX session_idx ON session_table(id_session)

93
94
95
96
97
98
99
100
101
102
103
CREATE TABLE one_time_ticket_table (
	ticket_one_time_ticket		varchar(30),
	robot_one_time_ticket		varchar(80),
	email_one_time_ticket		varchar(100),
	dat_one_time_ticket		integer,
	data_one_time_ticket		varchar(200),
	remote_addr_one_time_ticket	varchar(60),
	status_addr_one_time_ticket	varchar(60),
	PRIMARY KEY (ticket_one_time_ticket)
);
CREATE	INDEX one_time_ticket_idx ON one_time_ticket_table_table(ticket_one_time_ticket);