1 -- --------------------------------------------------
3 -- --------------------------------------------------
5 -- New tables for bresto (same as brestore)
7 CREATE TABLE brestore_knownjobid
9 JobId integer NOT NULL,
10 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
13 CREATE TABLE brestore_pathhierarchy
15 PathId integer NOT NULL,
16 PPathId integer NOT NULL,
17 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
20 CREATE INDEX brestore_pathhierarchy_ppathid
21 ON brestore_pathhierarchy (PPathId);
23 CREATE TABLE brestore_pathvisibility
25 PathId integer NOT NULL,
26 JobId integer NOT NULL,
29 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
32 CREATE INDEX brestore_pathvisibility_jobid
33 ON brestore_pathvisibility (JobId);
36 CREATE TABLE bweb_user
38 userid serial not null,
39 username text not null,
40 use_acl boolean default false,
41 enabled boolean default true,
42 comment text default '',
43 passwd text default '',
47 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username(255));
49 CREATE TABLE bweb_role
51 roleid serial not null,
52 rolename text not null,
53 -- comment text default '',
56 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename(255));
57 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
58 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
59 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
61 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
62 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
63 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
64 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
65 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
67 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
68 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
69 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
70 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
71 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
73 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
74 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
75 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
77 CREATE TABLE bweb_role_member
79 roleid integer not null,
80 userid integer not null,
81 primary key (roleid, userid)
84 CREATE TABLE bweb_client_group_acl
86 client_group_id integer not null,
87 userid integer not null,
88 primary key (client_group_id, userid)
92 -- --------------------------------------------------
94 -- --------------------------------------------------
96 -- Manage Client groups in bweb
97 -- Works with postgresql and mysql5
99 CREATE TABLE client_group
101 client_group_id serial not null,
102 client_group_name text not null,
103 primary key (client_group_id)
106 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name(255));
108 CREATE TABLE client_group_member
110 client_group_id integer not null,
111 clientid integer not null,
112 primary key (client_group_id, clientid)
115 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
118 -- -- creer un nouveau group
120 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
122 -- -- affecter une machine a un group
124 -- INSERT INTO client_group_member (client_group_id, clientid)
125 -- (SELECT client_group_id,
126 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
128 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
131 -- -- modifier l'affectation d'une machine
133 -- DELETE FROM client_group_member
134 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
136 -- -- supprimer un groupe
138 -- DELETE FROM client_group_member
139 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
142 -- -- afficher tous les clients du group SIGMA
144 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
145 -- JOIN client_group using (client_group_id)
146 -- WHERE client_group_name = 'SIGMA';
148 -- -- afficher tous les groups
150 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
152 -- -- afficher tous les job du group SIGMA hier
154 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
155 -- FROM Job JOIN Client USING(ClientId)
156 -- JOIN client_group_member USING (ClientId)
157 -- JOIN client_group USING (client_group_id)
158 -- WHERE client_group_name = 'SIGMA'
159 -- AND Job.StartTime > '2007-03-20';
161 -- -- donne des stats
163 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
164 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
165 -- JobStatus AS jobstatus, client_group_name
166 -- FROM Job JOIN Client USING(ClientId)
167 -- JOIN client_group_member USING (ClientId)
168 -- JOIN client_group USING (client_group_id)
169 -- WHERE Job.StartTime > '2007-03-20'
170 -- GROUP BY JobStatus, client_group_name