Index: docs/RelationalSchema.txt =================================================================== --- docs/RelationalSchema.txt (revision c98514d15766f9513e20a2f49ee9941844a08cfa) +++ docs/RelationalSchema.txt (revision c98514d15766f9513e20a2f49ee9941844a08cfa) @@ -0,0 +1,103 @@ +## Relational Schema + +1. **users** (_**username**_, is_activate, password, description, registered_at, sex) + +2. **moderator** (_**username#(users)**_) [inherits users] + +3. **developer** (_**username#(users)**_) [inherits users] + +4. **project_manager** (_**username#(users)**_) [inherits users] + +5. **thread** (_**id**_, description, logo_url, title, username#(users)) + +6. **likes** (_**username#(users)**_, _**thread_id#(thread)**_) + +7. **threads_moderators** (_**thread_id#(thread)**_, _**username#(users)**_) + +8. **tag** (_**name**_) + +9. **tag_threads** (_**thread_id#(thread)**_, _**tag_name#(tag)**_) + +10. **topic_thread** (_**id**_, description, logo_url, title, username#(users), guidelines, next_discussion_id) [inherits thread] + +11. **project_thread** (_**id**_, description, logo_url, title, username#(users), repo_url) [inherits thread] + +12. **discussion_thread** (_**id**_, _**topic_id#(thread)**_, text, created_by_user#(users), reply_discussion, reply_topic_id) + +13. **topic_belongs_to_project** (_**topic_id#(thread)**_, _**project_id#(thread)**_) + +14. **topic_blacklist** (_**username#(users)**_, _**project_id#(users)**_) + +15. **developer_associated_with_project** (_**project_id#(thread)**_, _**developer#(users)**_, _**started_at**_, ended_at) + +16. **permissions** (_**name**_) + +17. **project_roles** (_**name**_, _**project_id#(thread)**_, description) + +18. **users_project_roles** (_**username#(users)**_, _**project_id#(thread)**_, _**role_name#(project_roles)**_) + +19. **project_roles_permissions** (_**permission_name#(permissions)**_, _**role_name#(project_roles)**_, _**project_id#(thread)**_) + +20. **project_request** (_**id**_, reason, status, submited_by_user#(users), for_project#(thread)) + +21. **report** (_**id**_, created_at, description, status, thread_id#(thread), for_user#(users), by_user#(users)) + +22. **channel** (_**name**_, description, logo_url, _**project_id#(thread)**_, developer#(users)) + +23. **messages** (_**sent_at**_, content, _**sent_by#(users)**_, _**project_id#(thread)**_, _**channel_name#(channel)**_) + + + + +V2 + + +## Relational Schema + +1. **users** (_**username**_, is_activate, password, description, registered_at, sex) + +2. **moderator** (_**username#(users)**_) + +3. **developer** (_**username#(users)**_) + +4. **project_manager** (_**username#(users)**_) + +5. **thread** (_**id**_, content, _**username#(users)**_) + +6. **likes** (_**username#(users)**_, _**thread_id#(thread)**_) + +7. **topic_threads_moderators** (_**thread_id#(thread)**_, _**username#(users)**_) + +8. **tag** (_**name**_) + +9. **tag_threads** (_**thread_id#(thread)**_, _**tag_name#(tag)**_) + +10. **topic_thread** (_**id#(thread)**_, title, guidelines, next_discussion_id, parent_topic_id#(thread)) + +11. **topic_belongs_to_project** (_**topic_id#(thread)**_, _**project_id#(thread)**_) + +12. **topic_blacklist** (_**topic_id#(thread)**_, _**username#(users)**_, _**moderator#(users)**_) + +13. **project_thread** (_**id#(thread)**_, title, repo_url) + +14. **discussion_thread** (_**id#(thread)**_, _**created_by_user#(users)**_, reply_discussion_id#(thread), topic_id#(thread)) + +15. **developer_associated_with_project** (_**project_id#(thread)**_, _**developer#(users)**_, _**started_at**_, ended_at) + +16. **permissions** (_**name**_) + +17. **project_roles** (_**name**_, _**project_id#(thread)**_, description) + +18. **users_project_roles** (_**username#(users)**_, _**project_id#(thread)**_, _**role_name#(project_roles)**_) + +19. **project_roles_permissions** (_**permission_name#(permissions)**_, _**role_name#(project_roles)**_, _**project_id#(thread)**_) + +20. **project_request** (_**id**_, description, status, _**submited_by_user#(users)**_, _**project_id#(thread)**_) + +21. **report** (_**id**_, created_at, description, status, _**thread_id#(thread)**_, _**for_user#(users)**_, _**by_user#(users)**_) + +22. **channel** (_**name**_, description, logo_url, _**project_id#(thread)**_, _**developer#(users)**_) + +23. **messages** (_**sent_at**_, content, _**sent_by#(users)**_, _**project_id#(thread)**_, _**channel_name#(channel)**_) + + Index: cs/relational_schema.txt =================================================================== --- docs/relational_schema.txt (revision 974365a8c5532cd79cd5837aa6bf1a628db46a24) +++ (revision ) @@ -1,103 +1,0 @@ -## Relational Schema - -1. **users** (_**username**_, is_activate, password, description, registered_at, sex) - -2. **moderator** (_**username#(users)**_) [inherits users] - -3. **developer** (_**username#(users)**_) [inherits users] - -4. **project_manager** (_**username#(users)**_) [inherits users] - -5. **thread** (_**id**_, description, logo_url, title, username#(users)) - -6. **likes** (_**username#(users)**_, _**thread_id#(thread)**_) - -7. **threads_moderators** (_**thread_id#(thread)**_, _**username#(users)**_) - -8. **tag** (_**name**_) - -9. **tag_threads** (_**thread_id#(thread)**_, _**tag_name#(tag)**_) - -10. **topic_thread** (_**id**_, description, logo_url, title, username#(users), guidelines, next_discussion_id) [inherits thread] - -11. **project_thread** (_**id**_, description, logo_url, title, username#(users), repo_url) [inherits thread] - -12. **discussion_thread** (_**id**_, _**topic_id#(thread)**_, text, created_by_user#(users), reply_discussion, reply_topic_id) - -13. **topic_belongs_to_project** (_**topic_id#(thread)**_, _**project_id#(thread)**_) - -14. **topic_blacklist** (_**username#(users)**_, _**project_id#(users)**_) - -15. **developer_associated_with_project** (_**project_id#(thread)**_, _**developer#(users)**_, _**started_at**_, ended_at) - -16. **permissions** (_**name**_) - -17. **project_roles** (_**name**_, _**project_id#(thread)**_, description) - -18. **users_project_roles** (_**username#(users)**_, _**project_id#(thread)**_, _**role_name#(project_roles)**_) - -19. **project_roles_permissions** (_**permission_name#(permissions)**_, _**role_name#(project_roles)**_, _**project_id#(thread)**_) - -20. **project_request** (_**id**_, reason, status, submited_by_user#(users), for_project#(thread)) - -21. **report** (_**id**_, created_at, description, status, thread_id#(thread), for_user#(users), by_user#(users)) - -22. **channel** (_**name**_, description, logo_url, _**project_id#(thread)**_, developer#(users)) - -23. **messages** (_**sent_at**_, content, _**sent_by#(users)**_, _**project_id#(thread)**_, _**channel_name#(channel)**_) - - - - -V2 - - -## Relational Schema - -1. **users** (_**username**_, is_activate, password, description, registered_at, sex) - -2. **moderator** (_**username#(users)**_) - -3. **developer** (_**username#(users)**_) - -4. **project_manager** (_**username#(users)**_) - -5. **thread** (_**id**_, content, _**username#(users)**_) - -6. **likes** (_**username#(users)**_, _**thread_id#(thread)**_) - -7. **topic_threads_moderators** (_**thread_id#(thread)**_, _**username#(users)**_) - -8. **tag** (_**name**_) - -9. **tag_threads** (_**thread_id#(thread)**_, _**tag_name#(tag)**_) - -10. **topic_thread** (_**id#(thread)**_, title, guidelines, next_discussion_id, parent_topic_id#(thread)) - -11. **topic_belongs_to_project** (_**topic_id#(thread)**_, _**project_id#(thread)**_) - -12. **topic_blacklist** (_**topic_id#(thread)**_, _**username#(users)**_, _**moderator#(users)**_) - -13. **project_thread** (_**id#(thread)**_, title, repo_url) - -14. **discussion_thread** (_**id#(thread)**_, _**created_by_user#(users)**_, reply_discussion_id#(thread), topic_id#(thread)) - -15. **developer_associated_with_project** (_**project_id#(thread)**_, _**developer#(users)**_, _**started_at**_, ended_at) - -16. **permissions** (_**name**_) - -17. **project_roles** (_**name**_, _**project_id#(thread)**_, description) - -18. **users_project_roles** (_**username#(users)**_, _**project_id#(thread)**_, _**role_name#(project_roles)**_) - -19. **project_roles_permissions** (_**permission_name#(permissions)**_, _**role_name#(project_roles)**_, _**project_id#(thread)**_) - -20. **project_request** (_**id**_, description, status, _**submited_by_user#(users)**_, _**project_id#(thread)**_) - -21. **report** (_**id**_, created_at, description, status, _**thread_id#(thread)**_, _**for_user#(users)**_, _**by_user#(users)**_) - -22. **channel** (_**name**_, description, logo_url, _**project_id#(thread)**_, _**developer#(users)**_) - -23. **messages** (_**sent_at**_, content, _**sent_by#(users)**_, _**project_id#(thread)**_, _**channel_name#(channel)**_) - - Index: src/main/resources/db/migration/V3__init_ddl.sql =================================================================== --- src/main/resources/db/migration/V3__init_ddl.sql (revision 974365a8c5532cd79cd5837aa6bf1a628db46a24) +++ src/main/resources/db/migration/V3__init_ddl.sql (revision c98514d15766f9513e20a2f49ee9941844a08cfa) @@ -3,4 +3,6 @@ --- Trigger za check dali reply na discussion thread pripagjat na ist topic thread kako na toj so mu pret reply --- IMENUVANJE: triggeri so provervat nesto prefix = check, funkcii za istite prefix = validate +--- Nemame contraint sho velit deka sekoj topic thread trebat da e moderiran + ---- DROP TABLES DROP TABLE IF EXISTS users CASCADE; @@ -28,4 +30,5 @@ DROP TABLE IF EXISTS messages CASCADE; DROP TABLE IF EXISTS threads_moderators CASCADE; +drop table if exists blacklisted_user CASCADE; drop type if exists status; drop function if exists add_child_topic; @@ -36,8 +39,8 @@ id serial PRIMARY key, username varchar(32) UNIQUE NOT NULL, - is_activate bool, + is_activate bool not null, password varchar(72), description varchar(200), - registered_at timestamp, + registered_at timestamp not null, sex varchar(1) ); @@ -47,6 +50,6 @@ create table thread ( id serial primary key, - content text, - user_id int references users(id) + content text not null, + user_id int references users(id) not null ); create table likes( @@ -69,6 +72,6 @@ ); create table topic_thread ( - title varchar(32), - guidelines jsonb, + title varchar(32) not null, + guidelines jsonb not null, next_discussion_id int, parent_topic_id int REFERENCES thread(id) @@ -79,5 +82,5 @@ primary key(topic_id,project_id) ); -create table topic_blacklist( +create table blacklisted_user( topic_id int REFERENCES thread(id) ON DELETE CASCADE, user_id int references users(id) on delete cascade, @@ -88,11 +91,11 @@ ); create table project_thread ( - title varchar(32), + title varchar(32) not null, repo_url text ) inherits (thread); create table discussion_thread( - user_id int not null references users(id), + user_id int references users(id), reply_discussion_id int REFERENCES thread(id), - topic_id int REFERENCES thread(id) + topic_id int REFERENCES thread(id) not null ) inherits(thread); create table developer_associated_with_project( @@ -114,6 +117,6 @@ create table users_project_roles( user_id int references users(id), - project_id int, - role_name varchar(32), + project_id int , + role_name varchar(32) , FOREIGN KEY (role_name, project_id) REFERENCES project_roles(name, project_id), @@ -122,31 +125,31 @@ create table project_roles_permissions( permission_name varchar(32) references permissions(name), - role_name varchar(32), + role_name varchar(32) , project_id int, primary key(permission_name, role_name, project_id), FOREIGN KEY (role_name, project_id) - REFERENCES project_roles(name, project_id) + REFERENCES project_roles(name, project_id) ); CREATE TYPE status AS ENUM ('ACCEPTED', 'DENIED', 'PENDING'); create table project_request( id serial primary key, - description varchar (200), - status status, + description varchar (200) not null, + status status not null, user_id int references users(id) not null, project_id int references thread(id) not null ); create table report( - id serial primary key, - created_at timestamp, - description varchar(200), - status status, - thread_id int references thread(id) not null, - for_user_id int references users(id) not null, - by_user_id int references users(id) not null + id serial, + created_at timestamp not null, + description varchar(200) not null, + status status not null, + thread_id int references thread(id), + for_user_id int references users(id), + by_user_id int references users(id), + primary key(id,thread_id,for_user_id,by_user_id) ); create table channel ( name varchar (64), description varchar(200), - logo_url text, project_id int references thread(id) on delete cascade, developer_id int references users(id), @@ -155,6 +158,6 @@ create table messages ( sent_at timestamp, - content varchar(200), - sent_by int references users(id) not null, + content varchar(200) not null, + sent_by int references users(id), project_id int, channel_name varchar(64), @@ -209,15 +212,32 @@ END; $$; + +create function validate_topics_moderation() +returns trigger +LANGUAGE plpgsql +as $$ +BEGIN + +END; +$$; -------------------------- TRIGGERS ---------------------- + CREATE or replace TRIGGER check_topic_name BEFORE INSERT OR UPDATE ON topic_thread FOR EACH ROW EXECUTE FUNCTION validate_topic_title(); + CREATE OR REPLACE TRIGGER project_insert_child_topic AFTER INSERT ON topic_thread FOR EACH ROW EXECUTE FUNCTION add_child_topic(); + CREATE OR REPLACE TRIGGER check_same_parent BEFORE INSERT ON discussion_thread FOR EACH ROW EXECUTE FUNCTION validate_same_parent(); + +create or replace trigger check_topics_moderation + before insert on topic_thread + for each row + EXECUTE function validate_topics_moderation