create_db.Pg 4.06 KB
Newer Older
root's avatar
root committed
1
2
3
4
5
6
7
8
9
10
11
12
-- PostgreSQL Database creation script

CREATE DATABASE sympa;

-- Connect to DB 
\connect sympa

DROP TABLE user_table;
CREATE TABLE user_table (
  	email_user          	varchar (100) NOT NULL,
  	gecos_user          	varchar (150),
	cookie_delay_user       int4,
13
        password_user		varchar (40),
root's avatar
root committed
14
        lang_user               varchar (10),
15
	attributes_user		varchar (255),
16
	data_user	        varchar (255),
root's avatar
root committed
17
18
19
20
21
22
23
	CONSTRAINT ind_user PRIMARY KEY (email_user)
);

DROP TABLE subscriber_table;
CREATE TABLE subscriber_table (
  	list_subscriber       	varchar (50) NOT NULL,
	user_subscriber		varchar (100) NOT NULL,
24
  	custom_attribute_subscriber varchar (500),
25
	robot_subscriber	varchar (80) NOT NULL,
26
27
	date_subscriber		timestamp with time zone NOT NULL,
	update_subscriber 	timestamp with time zone,
root's avatar
root committed
28
29
	visibility_subscriber	varchar (20),
	reception_subscriber	varchar (20),
30
	topics_subscriber	varchar (200),
31
	bounce_subscriber	varchar (35),
salaun's avatar
salaun committed
32
	bounce_score_subscriber int4,
33
	bounce_address_subscriber	varchar (100),
34
	comment_subscriber	varchar (150),
35
36
	subscribed_subscriber 	smallint,
	included_subscriber 	smallint,
37
	include_sources_subscriber varchar(50),
38
	CONSTRAINT ind_subscriber PRIMARY KEY (robot_subscriber,list_subscriber,user_subscriber)
root's avatar
root committed
39
);
40
41
42
CREATE INDEX subscriber_idx ON subscriber_table (robot_subscriber,list_subscriber,user_subscriber);
CREATE INDEX subscriber_idx2 ON subscriber_table (user_subscriber);

43

44
45
46
47
DROP TABLE admin_table;
CREATE TABLE admin_table (
	list_admin 		varchar(50) NOT NULL,
 	user_admin 		varchar(100) NOT NULL,
48
 	robot_admin 		varchar(80) NOT NULL,
49
50
51
52
	role_admin 		varchar(15) NOT NULL,
	date_admin 		timestamp with time zone NOT NULL,
	update_admin 		timestamp with time zone,
	reception_admin 	varchar(20),
53
	visibility_admin 	varchar(20),
54
	comment_admin 		varchar(150),
55
56
	subscribed_admin  	smallint,
	included_admin  	smallint,
57
58
59
	include_sources_admin  	varchar(50),
	info_admin   		varchar(150),
	profile_admin  		varchar(15),
60
        CONSTRAINT ind_admin PRIMARY KEY (robot_admin, list_admin, role_admin, user_admin)
61
);
62
63
CREATE	INDEX admin_idx ON admin_table(robot_admin, list_admin, role_admin, user_admin);
CREATE	INDEX admin_idx2 ON admin_table(user_admin);
64
 
65
66
67
DROP TABLE netidmap_table;
CREATE TABLE netidmap_table (
        netid_netidmap              varchar (100) NOT NULL,
68
	serviceid_netidmap	    varchar (100) NOT NULL,
69
70
	robot_netidmap	            varchar (80) NOT NULL,
        email_netidmap              varchar (100),
71
        CONSTRAINT ind_netidmap PRIMARY KEY (netid_netidmap, serviceid_netidmap, robot_netidmap)
72
);
73
CREATE	INDEX netidmap_idx ON netidmap_table(netid_netidmap, serviceid_netidmap, robot_netidmap);
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

DROP TABLE logs_table;
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);
93

94
DROP TABLE session_table;
95
CREATE TABLE session_table (
96
	id_session			int8 NOT NULL,
97
98
99
100
101
102
	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,
103
	data_session		        varchar(255),
104
105
106
	CONSTRAINT ind_session PRIMARY KEY (id_session)
);
CREATE	INDEX session_idx ON session_table(id_session);
107
108
109
110
111
112
113
114
115
116
117
118
119
120


DROP TABLE one_time_ticket_table;
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		bigint,
	data_one_time_ticket		varchar(200),
	remote_addr_one_time_ticket	varchar(60),
	status_addr_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);