← Back to branch summary

~azzar1/unity/add-show-desktop-key

« back to all changes in this revision

Viewing changes to userdb/migrations/20100216-01.sql

  • Committer: William Grant
  • Date: 2012-06-28 01:52:02 UTC
  • Revision ID: me@williamgrant.id.au-20120628015202-f6ru7o367gt6nvgz
Hah

Show diffs side-by-side

added added

removed removed

Lines of Context:
userdb/migrations/20100216-01.sql
 
1
BEGIN;
 
2
 
 
3
-- Check that the provided name is sane for use in URLs.
 
4
CREATE OR REPLACE FUNCTION valid_url_name(name text) RETURNS boolean AS 
 
5
$$
 
6
    BEGIN
 
7
        RETURN name ~ E'^[a-z0-9][a-z0-9_\+\.\-]*$';
 
8
    END;
 
9
$$ LANGUAGE 'plpgsql';
 
10
 
 
11
-- Just like valid_url_name, except that @ is permitted (so we can use a
 
12
-- reasonable subset of email addresses as usernames).
 
13
CREATE OR REPLACE FUNCTION valid_login_name(name text) RETURNS boolean AS 
 
14
$$
 
15
    BEGIN
 
16
        RETURN name ~ E'^[a-z0-9][a-z0-9@_\+\.\-]*$';
 
17
    END;
 
18
$$ LANGUAGE 'plpgsql';
 
19
 
 
20
ALTER TABLE login ADD CONSTRAINT login_login_check CHECK (valid_login_name(login)); 
 
21
ALTER TABLE subject ADD CONSTRAINT subject_subj_short_name_check CHECK (valid_url_name(subj_short_name)); 
 
22
ALTER TABLE semester ADD CONSTRAINT semester_year_check CHECK (valid_url_name(year)); 
 
23
ALTER TABLE semester ADD CONSTRAINT semester_semester_check CHECK (valid_url_name(semester)); 
 
24
ALTER TABLE project ADD CONSTRAINT project_short_name_check CHECK (valid_url_name(short_name)); 
 
25
ALTER TABLE project_group ADD CONSTRAINT project_group_groupnm_check CHECK (valid_url_name(groupnm)); 
 
26
ALTER TABLE exercise ADD CONSTRAINT exercise_identifier_check CHECK (valid_url_name(identifier)); 
 
27
ALTER TABLE worksheet ADD CONSTRAINT worksheet_identifier_check CHECK (valid_url_name(identifier)); 
 
28
 
 
29
COMMIT;