Index: _BUILD_BOARD.xml
===================================================================
--- ER_BUILD_BOARD.xml (revision 5b4c47b1e0582ba360d59c237b5bdb83754b9aeb)
+++ (revision )
@@ -1,1 +1,0 @@
-BLACKLISTSMODERATORUSERNDOUBLE3SUBMITSTHREADtrue16dPROJECT_THREADTOPIC_THREADDOUBLE3DISCUSSION_THREADDOUBLE3CONTAINS3NBELONGS_TON33true16oDEVELOPERPROJECT_MANAGERtrue16oPERMISSIONSDEFINED_BYNDOUBLE3CUSTOM_ROLE3MDOUBLE3CONTAINS3NDOUBLE3CONTAINSDOUBLE3CHANNEL3NCREATES3NIS_CREATED_BY3Ntrue16oIS_MODERATED_BYREPLIES3trueRole_nametruetruePermission_nametruetrueusernamepasswordDescriptionASSOCIATED_WITHNMregister_datestarted_atSTARTS_AN3truechannel_nameSENDS_MESSAGE_INMMsg_txtStarted_involvment_atended_involmentMTimestampTAGCREATESASSISGNED_TONNtruetrueNamedescriptionLIKEDNMLogologois_activedescstatus3Ncreated_atsexshort_descCommunity_guideline3truereport_idDOUBLE3REPORTDOUBLE3SUBBMITTEDDOUBLE3ABOUT3M3NDOUBLE3FOR_MISCONDUCT3PtruetrueIdTitleDOUBLE3PROJECT_REQUEST3P3trueIdDOUBLE3RECIEVES3NStatusReasonText3trueidnext_discussion_idreasonM3NMHASMP
Index: docs/ER_BUILD_BOARD.xml
===================================================================
--- docs/ER_BUILD_BOARD.xml (revision 4a02f5832d01b629e4f7f1dda64f24e6483e529c)
+++ docs/ER_BUILD_BOARD.xml (revision 4a02f5832d01b629e4f7f1dda64f24e6483e529c)
@@ -0,0 +1,1 @@
+BLACKLISTSMODERATORUSERNDOUBLE3SUBMITSTHREADtrue16dPROJECT_THREADTOPIC_THREADDOUBLE3DISCUSSION_THREADDOUBLE3CONTAINS3NBELONGS_TON33true16oDEVELOPERPROJECT_MANAGERtrue16oPERMISSIONSDEFINED_BYNDOUBLE3CUSTOM_ROLE3MDOUBLE3CONTAINS3NDOUBLE3CONTAINSDOUBLE3CHANNEL3NCREATES3NIS_CREATED_BY3Ntrue16oIS_MODERATED_BYREPLIES3trueRole_nametruetruePermission_nametruetrueusernamepasswordDescriptionASSOCIATED_WITHNMregister_datestarted_atSTARTS_AN3truechannel_nameSENDS_MESSAGE_INMMsg_txtStarted_involvment_atended_involmentMTimestampTAGCREATESASSISGNED_TONNtruetrueNamedescriptionLIKEDNMLogologois_activedescstatus3Ncreated_atsexshort_descCommunity_guideline3truereport_idDOUBLE3REPORTDOUBLE3SUBBMITTEDDOUBLE3ABOUT3M3NDOUBLE3FOR_MISCONDUCT3PtruetrueIdTitleDOUBLE3PROJECT_REQUEST3P3trueIdDOUBLE3RECIEVES3NStatusReasonText3trueidnext_discussion_idreasonM3NMHASMP
Index: docs/UseCase.txt
===================================================================
--- docs/UseCase.txt (revision 4a02f5832d01b629e4f7f1dda64f24e6483e529c)
+++ docs/UseCase.txt (revision 4a02f5832d01b629e4f7f1dda64f24e6483e529c)
@@ -0,0 +1,40 @@
+Use Cases
+
+User
+ - Kreirat topic.
+ - Kreirat discussion thread vo topic thread.
+ - Pravi reply na discussion thread.
+ - Stava like na thread.
+ - Korisnik prijavuva drug korisnik za nesoodvetno odnesuvanje.
+ - Korisnikot se prijavuva za uchestvo vo proekt.
+ - Referencira drug thread.
+
+Moderator
+ - Nadgleduvat odreden Topic/Project thraed.
+ - Dodavat/Kreirat tagoj na dadeni topics.
+ - Otstranuvat korisnik od topicot.
+
+Developer
+ - Kreirat kanal vo project.
+ - Pisuva poraka vo kanal.
+
+Project Owner
+ - Odobruva ili odbiva baranja za ucestvo vo negoviot proekt.
+ - Otstranuva developer od proekt.
+ - Kreira roles za daden proekt
+ - Dodava roles na daden developer
+
+# SQL
+Kaj e smenato:
+ - Discussion
+ - Moderation
+ - Topics
+
+
+
+ # ne mozit dva topici so isto ime da imat vo eden thread ako sa sozdrzat.
+
+
+KVERINJA
+
+-
Index: docs/relational_schema.txt
===================================================================
--- docs/relational_schema.txt (revision 4a02f5832d01b629e4f7f1dda64f24e6483e529c)
+++ docs/relational_schema.txt (revision 4a02f5832d01b629e4f7f1dda64f24e6483e529c)
@@ -0,0 +1,47 @@
+## 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)**_)
Index: lational_schema.txt
===================================================================
--- relational_schema.txt (revision 5b4c47b1e0582ba360d59c237b5bdb83754b9aeb)
+++ (revision )
@@ -1,47 +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)**_)
Index: src/main/resources/db/migration/V2__init_ddl-corrected.sql
===================================================================
--- src/main/resources/db/migration/V2__init_ddl-corrected.sql (revision 4a02f5832d01b629e4f7f1dda64f24e6483e529c)
+++ src/main/resources/db/migration/V2__init_ddl-corrected.sql (revision 4a02f5832d01b629e4f7f1dda64f24e6483e529c)
@@ -0,0 +1,264 @@
+--- Trigger before update/insert za check na iminjata topic/discussion -> OK
+--- Trigger za ko ke adnit dete na topic thread sho e vo proekt, da go dodajt kako belongs_to vo proektot
+--- 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\
+
+
+
+-- DROP TABLES
+DROP TABLE IF EXISTS users CASCADE;
+DROP TABLE IF EXISTS moderator CASCADE;
+DROP TABLE IF EXISTS developer CASCADE;
+DROP TABLE IF EXISTS project_manager CASCADE;
+DROP TABLE IF EXISTS thread CASCADE;
+DROP TABLE IF EXISTS likes CASCADE;
+DROP TABLE IF EXISTS topic_threads_moderators CASCADE;
+DROP TABLE IF EXISTS tag CASCADE;
+DROP TABLE IF EXISTS tag_threads CASCADE;
+DROP TABLE IF EXISTS topic_thread CASCADE;
+DROP TABLE IF EXISTS topic_belongs_to_project CASCADE;
+DROP TABLE IF EXISTS topic_blacklist CASCADE;
+DROP TABLE IF EXISTS project_thread CASCADE;
+DROP TABLE IF EXISTS discussion_thread CASCADE;
+DROP TABLE IF EXISTS developer_associated_with_project CASCADE;
+DROP TABLE IF EXISTS permissions CASCADE;
+DROP TABLE IF EXISTS project_roles CASCADE;
+DROP TABLE IF EXISTS users_project_roles CASCADE;
+DROP TABLE IF EXISTS project_roles_permissions CASCADE;
+DROP TABLE IF EXISTS project_request CASCADE;
+DROP TABLE IF EXISTS report CASCADE;
+DROP TABLE IF EXISTS channel CASCADE;
+DROP TABLE IF EXISTS messages CASCADE;
+DROP TABLE IF EXISTS threads_moderators CASCADE;
+drop type if exists status;
+drop function if exists add_child_topic;
+drop function if exists validate_same_parent;
+drop function if exists validate_topic_title;
+
+
+---- DDL
+
+create table users(
+ username varchar(32) primary key,
+ is_activate bool,
+ password varchar(72),
+ description varchar(200),
+ registered_at timestamp,
+ sex varchar(1)
+);
+
+create table moderator() inherits (users);
+create table developer() inherits (users);
+create table project_manager() inherits (users);
+
+create table thread (
+ id serial primary key,
+ content text,
+ username varchar(32) references users(username)
+);
+
+create table likes(
+ username varchar(32) references users(username),
+ thread_id int references thread(id),
+ primary key(username, thread_id)
+);
+
+create table topic_threads_moderators(
+ thread_id int references thread(id) on delete cascade,
+ username varchar(32) references users(username) on delete cascade,
+ primary key(thread_id, username)
+);
+
+create table tag(
+ name varchar(64) primary key
+);
+
+create table tag_threads(
+ thread_id int references thread(id),
+ tag_name varchar(64) references tag(name),
+ primary key(thread_id, tag_name)
+);
+
+create table topic_thread (
+ title varchar(32),
+ guidelines jsonb,
+ next_discussion_id int,
+ parent_topic_id int REFERENCES thread(id)
+) inherits (thread);
+
+create table topic_belongs_to_project(
+ topic_id int references thread(id) on delete cascade,
+ project_id int references thread(id) on delete cascade,
+ primary key(topic_id,project_id)
+);
+
+create table topic_blacklist(
+ topic_id int REFERENCES thread(id) ON DELETE CASCADE,
+ username varchar(32) references users(username) on delete cascade,
+ moderator varchar(32) references users(username) on delete cascade,
+ primary key(username,moderator,topic_id)
+);
+
+create table project_thread (
+ title varchar(32),
+ repo_url text
+) inherits (thread);
+
+create table discussion_thread(
+ created_by_user varchar(32) not null references users(username),
+ reply_discussion_id int REFERENCES thread(id),
+ topic_id int REFERENCES thread(id)
+) inherits(thread);
+
+create table developer_associated_with_project(
+ project_id int references thread(id),
+ developer varchar(32) references users(username),
+ started_at timestamp,
+ ended_at timestamp,
+ primary key(project_id, developer, started_at)
+);
+
+create table permissions(
+ name varchar(32) primary key
+);
+
+create table project_roles(
+ name varchar(32),
+ project_id int references thread(id) on delete cascade,
+ description text not null,
+ primary key(name, project_id)
+);
+
+create table users_project_roles(
+ username varchar(32) references users(username),
+ project_id int,
+ role_name varchar(32),
+ FOREIGN KEY (role_name, project_id)
+ REFERENCES project_roles(name, project_id),
+ primary key(username, project_id, role_name)
+);
+
+create table project_roles_permissions(
+ permission_name varchar(32) references permissions(name),
+ 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)
+);
+
+CREATE TYPE status AS ENUM ('ACCEPTED', 'DENIED', 'PENDING');
+
+create table project_request(
+ id serial primary key,
+ description varchar (200),
+ status status,
+ submited_by_user varchar(32) references users(username) 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 varchar(32) references users(username) not null,
+ by_user varchar(32) references users(username) not null
+);
+
+create table channel (
+ name varchar (64),
+ description varchar(200),
+ logo_url text,
+ project_id int references thread(id) on delete cascade,
+ developer varchar(32) references users(username),
+ primary key(name, project_id)
+);
+
+create table messages (
+ sent_at timestamp,
+ content varchar(200),
+ sent_by varchar(32) references users(username) not null,
+ project_id int,
+ channel_name varchar(64),
+ FOREIGN KEY (channel_name, project_id)
+ REFERENCES channel(name, project_id) on delete cascade,
+ primary key(channel_name, project_id, sent_at, sent_by)
+);
+
+
+-------------------------- FUNCTIONS ----------------------
+
+CREATE FUNCTION validate_topic_title()
+ RETURNS trigger
+ LANGUAGE plpgsql
+as $$
+BEGIN
+ IF NEW.title IN
+ (
+ SELECT title
+ FROM topic_thread
+ AS t
+ WHERE t.parent_topic_id = NEW.parent_topic_id)
+ THEN RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',NEW.title,NEW.parent_topic_id;
+END IF;
+RETURN NEW;
+END;
+$$;
+
+CREATE FUNCTION add_child_topic()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS $$
+DECLARE
+project_id INT;
+BEGIN
+SELECT t.project_id
+INTO project_id
+FROM topic_belongs_to_project AS t WHERE NEW.id = t.topic_id;
+INSERT INTO topic_belongs_to_project VALUES (NEW.id,project_id);
+END;
+$$;
+
+CREATE FUNCTION validate_same_parent()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF NOT EXISTS (
+ SELECT 1
+ FROM discussion_thread
+ AS dt
+ WHERE NEW.reply_discussion_id = dt.id AND dt.topic_id = NEW.topic_id
+ ) THEN
+ RAISE EXCEPTION 'Can not reply to a discussion that is not in the same topic';
+END IF;
+RETURN NEW;
+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();
+
+
+
+
+