Last active
September 18, 2018 22:37
-
-
Save lthms/52cb376d93f70559d7244eb9cbc24381 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE sheets ( | |
| id SERIAL PRIMARY KEY, | |
| character_id INTEGER NOT NULL REFERENCES characters, | |
| created TIMESTAMP NOT NULL DEFAULT now(), | |
| validated TIMESTAMP, | |
| current BOOLEAN, | |
| future BOOLEAN, | |
| invalidated TIMESTAMP, | |
| name VARCHAR(80) NOT NULL, | |
| avatar_small INTEGER REFERENCES images, | |
| avatar_large INTEGER REFERENCES images, | |
| history INTEGER NOT NULL REFERENCES documents, | |
| CONSTRAINT validation_process CHECK( | |
| (invalidated >= validated) AND (validated >= created) | |
| ), | |
| CONSTRAINT invalidated_has_been_validated CHECK( | |
| /* invalidated is not null -> validated is not null */ | |
| (invalidated IS NULL) OR (validated IS NOT NULL) | |
| ), | |
| /* there is only one “current” sheet per character (we rely on the fact that | |
| NULL is ignored for uniqueness) */ | |
| CONSTRAINT current_or_null CHECK(current), | |
| CONSTRAINT one_sheet_current UNIQUE (character_id, current), | |
| CONSTRAINT current_is_validated CHECK( | |
| /* current is not null -> validated is not null */ | |
| (current IS NULL) OR (validated IS NOT NULL) | |
| ), | |
| CONSTRAINT invalidated_is_not_current CHECK( | |
| /* invalidated is not null -> current is null */ | |
| (invalidated IS NULL) OR (current IS NULL) | |
| ), | |
| CONSTRAINT future_or_null CHECK(future), | |
| CONSTRAINT one_sheet_future UNIQUE (character_id, future), | |
| CONSTRAINT future_is_not_validated CHECK( | |
| /* future is not null -> validated is null */ | |
| (future IS NULL) OR (validated IS NULL) | |
| ), | |
| CONSTRAINT current_has_small_avatar CHECK( | |
| /* current is not null -> avatar_small is not null */ | |
| (current IS NULL) OR (avatar_small IS NOT NULL) | |
| ), | |
| CONSTRAINT current_has_large_avatar CHECK( | |
| /* current is not null -> avatar_small is not null */ | |
| (current IS NULL) OR (avatar_large IS NOT NULL) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment