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
|
BEGIN;
-- Check that the provided name is sane for use in URLs.
CREATE OR REPLACE FUNCTION valid_url_name(name text) RETURNS boolean AS
$$
BEGIN
RETURN name ~ E'^[a-z0-9][a-z0-9_\+\.\-]*$';
END;
$$ LANGUAGE 'plpgsql';
-- Just like valid_url_name, except that @ is permitted (so we can use a
-- reasonable subset of email addresses as usernames).
CREATE OR REPLACE FUNCTION valid_login_name(name text) RETURNS boolean AS
$$
BEGIN
RETURN name ~ E'^[a-z0-9][a-z0-9@_\+\.\-]*$';
END;
$$ LANGUAGE 'plpgsql';
ALTER TABLE login ADD CONSTRAINT login_login_check CHECK (valid_login_name(login));
ALTER TABLE subject ADD CONSTRAINT subject_subj_short_name_check CHECK (valid_url_name(subj_short_name));
ALTER TABLE semester ADD CONSTRAINT semester_year_check CHECK (valid_url_name(year));
ALTER TABLE semester ADD CONSTRAINT semester_semester_check CHECK (valid_url_name(semester));
ALTER TABLE project ADD CONSTRAINT project_short_name_check CHECK (valid_url_name(short_name));
ALTER TABLE project_group ADD CONSTRAINT project_group_groupnm_check CHECK (valid_url_name(groupnm));
ALTER TABLE exercise ADD CONSTRAINT exercise_identifier_check CHECK (valid_url_name(identifier));
ALTER TABLE worksheet ADD CONSTRAINT worksheet_identifier_check CHECK (valid_url_name(identifier));
COMMIT;
|