[3733] | 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' ); |
---|