1 | -- SQL file for AWL tables |
---|
2 | |
---|
3 | -- Table for holding the schema version so we can be more structured in future |
---|
4 | CREATE TABLE awl_db_revision ( |
---|
5 | schema_id INT4, |
---|
6 | schema_major INT4, |
---|
7 | schema_minor INT4, |
---|
8 | schema_patch INT4, |
---|
9 | schema_name TEXT, |
---|
10 | applied_on TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp |
---|
11 | ); |
---|
12 | GRANT SELECT ON awl_db_revision TO general; |
---|
13 | |
---|
14 | CREATE or REPLACE FUNCTION check_db_revision( INT, INT, INT ) RETURNS BOOLEAN AS ' |
---|
15 | DECLARE |
---|
16 | major ALIAS FOR $1; |
---|
17 | minor ALIAS FOR $2; |
---|
18 | patch ALIAS FOR $3; |
---|
19 | matching INT; |
---|
20 | BEGIN |
---|
21 | SELECT COUNT(*) INTO matching FROM awl_db_revision |
---|
22 | WHERE schema_major = major AND schema_minor = minor AND schema_patch = patch; |
---|
23 | IF matching != 1 THEN |
---|
24 | RAISE EXCEPTION ''Database has not been upgraded to %.%.%'', major, minor, patch; |
---|
25 | RETURN FALSE; |
---|
26 | END IF; |
---|
27 | SELECT COUNT(*) INTO matching FROM awl_db_revision |
---|
28 | WHERE (schema_major = major AND schema_minor = minor AND schema_patch > patch) |
---|
29 | OR (schema_major = major AND schema_minor > minor) |
---|
30 | OR (schema_major > major) |
---|
31 | ; |
---|
32 | IF matching >= 1 THEN |
---|
33 | RAISE EXCEPTION ''Database revisions after %.%.% have already been applied.'', major, minor, patch; |
---|
34 | RETURN FALSE; |
---|
35 | END IF; |
---|
36 | RETURN TRUE; |
---|
37 | END; |
---|
38 | ' LANGUAGE 'plpgsql'; |
---|
39 | |
---|
40 | -- The schema_id should always be incremented. The major / minor / patch level should |
---|
41 | -- be incremented as seems appropriate... |
---|
42 | CREATE or REPLACE FUNCTION new_db_revision( INT, INT, INT, TEXT ) RETURNS BOOLEAN AS ' |
---|
43 | DECLARE |
---|
44 | major ALIAS FOR $1; |
---|
45 | minor ALIAS FOR $2; |
---|
46 | patch ALIAS FOR $3; |
---|
47 | blurb ALIAS FOR $4; |
---|
48 | new_id INT; |
---|
49 | BEGIN |
---|
50 | SELECT MAX(schema_id) + 1 INTO new_id FROM awl_db_revision; |
---|
51 | IF NOT FOUND OR new_id IS NULL THEN |
---|
52 | new_id := 1; |
---|
53 | END IF; |
---|
54 | INSERT INTO awl_db_revision (schema_id, schema_major, schema_minor, schema_patch, schema_name) |
---|
55 | VALUES( new_id, major, minor, patch, blurb ); |
---|
56 | RETURN TRUE; |
---|
57 | END; |
---|
58 | ' LANGUAGE 'plpgsql'; |
---|
59 | SELECT new_db_revision(1,1,0, 'Dawn' ); |
---|