indexing
	description: "Database access to issue data."
	author: "Peter Wyss <wyssp@ethz.ch>"
	date: "$Date: 2007-07-12 02:27:58 +0200 (Thu, 12 Jul 2007) $"
	revision: "$Revision: 491 $"

class
	ISSUE_ACCESS

inherit
	DATABASE_ACCESS

create
	make

feature -- Status

	is_found: BOOLEAN
			-- Did the last retrieve query find an issue?

feature -- Access

	last_issue_workitem: TUPLE [workitem_id: INTEGER; issue_id: INTEGER; project_issue_id: INTEGER; title: STRING; description: STRING; is_private: BOOLEAN; is_new: BOOLEAN]
			-- Last retrieved issue workitem by a retrieve query.

	last_issue: TUPLE [issue_id: INTEGER; project_issue_id: INTEGER; project_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING; is_private: BOOLEAN; revisions: ARRAYED_LIST [TUPLE [creation_time: INTEGER; user: STRING; text: STRING; tags: STRING]]]
			-- Last retrieved issue by a retrieve query.

feature -- Select queries

	retrieve_issue_workitem_by_id (a_id: INTEGER) is
			-- Retrieve a issue workitem by a_id.
		require
			a_id_ok: a_id > 0
		local
			l_selection: DB_SELECTION
			l_first_rev_res: SELECTION_RESULT
			l_issue_workitem_list_filling: DB_ACTION [WORKITEM_ISSUE]
			l_issue_workitem: WORKITEM_ISSUE
			l_last_issue_workitem: WORKITEM_ISSUE
			l_issue_revision_list_filling: DB_ACTION [ISSUE_REVISION]
			l_issue_revision: ISSUE_REVISION
			l_last_issue_revision: ISSUE_REVISION
			l_issue_list_filling: DB_ACTION [ISSUE]
			l_issue: ISSUE
			l_last_issue: ISSUE
		do
				-- get issue id
			create l_issue_workitem.make
			create l_selection.make
			l_selection.set_map_name (a_id, "workitem_id")
			l_selection.query ("SELECT * FROM workitem_issue WHERE workitem_id=:workitem_id")
			handle_errors_and_warnings
			l_selection.object_convert (l_issue_workitem)
			create l_issue_workitem_list_filling.make (l_selection, l_issue_workitem)
			l_selection.set_action (l_issue_workitem_list_filling)
			l_selection.load_result
			l_selection.terminate
			if l_issue_workitem_list_filling.list.count = 1 then
				l_last_issue_workitem := l_issue_workitem_list_filling.list.first
					-- get issue base data
				create l_issue_revision.make
				create l_selection.make
				l_selection.set_map_name (l_last_issue_workitem.issue_revision_id, "issue_revision_id")
				l_selection.query ("SELECT * FROM `issue_revision` WHERE issue_revision_id=:issue_revision_id")
				handle_errors_and_warnings
				l_selection.object_convert (l_issue_revision)
				create l_issue_revision_list_filling.make (l_selection, l_issue_revision)
				l_selection.set_action (l_issue_revision_list_filling)
				l_selection.load_result
				l_selection.terminate
				if l_issue_revision_list_filling.list.count = 1 then
					l_last_issue_revision := l_issue_revision_list_filling.list.first
						-- get revisions for issue
					create l_issue.make
					create l_selection.make
					l_selection.set_map_name (l_last_issue_revision.issue_id, "issue_id")
					l_selection.query ("SELECT * FROM `issue` WHERE issue_id=:issue_id")
					handle_errors_and_warnings
					l_selection.object_convert (l_issue)
					create l_issue_list_filling.make (l_selection, l_issue)
					l_selection.set_action (l_issue_list_filling)
					l_selection.load_result
					l_selection.terminate
					if l_issue_list_filling.list.count = 1 then
						l_last_issue := l_issue_list_filling.list.first
							-- create the complete issue
						create last_issue_workitem
						last_issue_workitem.workitem_id := a_id
						last_issue_workitem.issue_id := l_last_issue.issue_id
						last_issue_workitem.project_issue_id := l_last_issue.project_issue_id
						last_issue_workitem.title := l_last_issue.title
						last_issue_workitem.description := l_last_issue_revision.text
						last_issue_workitem.is_private := l_last_issue.private = 1
							-- check if this is the first revision ie. a new issue
						select_query.set_map_name (l_last_issue_revision.issue_id, "issue_id")
						l_first_rev_res := select_query.execute_query ("SELECT MIN(issue_revision_id) AS min_id FROM issue_revision WHERE issue_id=:issue_id")
						l_first_rev_res.start
						last_issue_workitem.is_new := l_first_rev_res.read_integer ("min_id") = l_last_issue_workitem.issue_revision_id

						is_found := True
					else
						is_found := False
					end
				else
					is_found := False
				end
			else
				is_found := False
			end

		ensure
			is_found_implies_issue: is_found implies last_issue_workitem /= Void
		end

	retrieve_issue_by_project_issue_id (a_project_id: INTEGER; a_project_issue_id: INTEGER) is
			-- Retrieve a issue by a_project and a_project_issue_id and stores it in last_issue.
		require
			a_project_id: a_project_id > 0
			a_project_issue_id_ok: a_project_issue_id > 0
		local
			l_selection: SELECTION_RESULT
			l_selection_tags: SELECTION_RESULT
			l_complete_issue: TUPLE [issue_id: INTEGER; project_issue_id: INTEGER; project_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING; is_private: BOOLEAN; revisions: ARRAYED_LIST [TUPLE [creation_time: INTEGER; user: STRING; text: STRING; tags: STRING]]]
			l_revisions: ARRAYED_LIST [TUPLE [creation_time: INTEGER; user: STRING; text: STRING; tags: STRING]]
			l_revision: TUPLE [creation_time: INTEGER; user: STRING; text: STRING; tags: STRING]
		do
				-- get issue base data
			create l_complete_issue
			select_query.set_map_name (a_project_id, "project_id")
			select_query.set_map_name (a_project_issue_id, "project_issue_id")
			l_selection := select_query.execute_query ("SELECT * FROM `issue` WHERE project_id=:project_id AND project_issue_id=:project_issue_id")
			if l_selection.count = 1 then
				l_selection.start
				l_complete_issue.issue_id := l_selection.read_integer ("issue_id")
				l_complete_issue.project_issue_id := l_selection.read_integer ("project_issue_id")
				l_complete_issue.project_id := l_selection.read_integer ("project_id")
				l_complete_issue.title := l_selection.read_string ("title")
				l_complete_issue.is_private := (l_selection.read_integer ("private") = 1)
				l_complete_issue.last_modified := l_selection.read_integer("last_modified")

					-- get revisions for issue
				select_query.set_map_name (l_complete_issue.issue_id, "issue_id")
				l_selection := select_query.execute_query ("[
						SELECT r.issue_revision_id, r.issue_id, r.creation_time, u.name AS user, r.`text`
						FROM issue_revision r
						LEFT JOIN user u USING (user_id)
						WHERE issue_id=:issue_id
						ORDER BY creation_time ASC
						]")

				if l_selection.count > 0 then
					create l_revisions.make (l_selection.count)
						-- get data from first revision
					l_selection.start
					l_complete_issue.creation_time := l_selection.read_integer ("creation_time")
					l_complete_issue.user := l_selection.read_string ("user")
						-- add all revisions
					from
						l_selection.start
					until
						l_selection.after
					loop
						create l_revision
							-- get tags
						l_revision.tags := ""
						select_query.set_map_name (l_selection.read_integer ("issue_revision_id"), "issue_revision_id")
						l_selection_tags := select_query.execute_query ("[
								SELECT t.`text`
								FROM issue_tag_association a
								LEFT JOIN tag_text t USING (tag_id)
								WHERE a.issue_revision_id=:issue_revision_id
								ORDER BY t.`text` ASC
								]")
						from
							l_selection_tags.start
						until
							l_selection_tags.after
						loop
							if l_revision.tags.is_empty then
								l_revision.tags := l_selection_tags.read_string ("text")
							else
								l_revision.tags := l_revision.tags + ", " + l_selection_tags.read_string ("text")
							end
							l_selection_tags.forth
						end

						l_revision.creation_time := l_selection.read_integer ("creation_time")
						l_revision.user := l_selection.read_string ("user")
						l_revision.text := l_selection.read_string ("text")
						l_revisions.force (l_revision)
						l_selection.forth
					end
					l_complete_issue.tags := l_revision.tags
					l_complete_issue.revisions := l_revisions
					last_issue := l_complete_issue
					is_found := True
				else
					is_found := False
				end
			else
				is_found := False
			end

		ensure
			is_found_implies_issue: is_found implies last_issue /= Void
		end

	retrieve_project_issues (a_project_id: INTEGER): DS_HASH_TABLE[TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING], INTEGER] is
			-- Retrieve issues for a_project
		require
			a_project_id: a_project_id > 0
		local
			l_selection: SELECTION_RESULT
			l_selection_rev: SELECTION_RESULT
			l_selection_tags: SELECTION_RESULT
			l_tuple: TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING]
			i: INTEGER
		do
			select_query.set_map_name (a_project_id, "project_id")

			l_selection := select_query.execute_query ("[
					SELECT i.issue_id, i.project_id, i.project_issue_id, i.title, i.private, i.last_modified
					FROM `issue` i
					WHERE i.project_id=:project_id
					]")

				-- get result and put it into an arrayed list as named tuples
			from
				create Result.make (l_selection.count)
				l_selection.start
				i := 0
			until
				l_selection.after
			loop
				create l_tuple
				l_tuple.project_issue_id := l_selection.read_integer ("project_issue_id")
				l_tuple.title := l_selection.read_string ("title")
				l_tuple.last_modified := l_selection.read_integer ("last_modified")

					-- get first revision
				select_query.set_map_name (l_selection.read_integer ("issue_id"), "issue_id")
				l_selection_rev := select_query.execute_query ("[
						SELECT r.issue_revision_id, r.issue_id, r.creation_time, u.name AS user, r.`text`
						FROM issue_revision r
						LEFT JOIN user u USING (user_id)
						WHERE issue_id=:issue_id
						ORDER BY creation_time ASC
						LIMIT 1
						]")

				if l_selection_rev.count = 1 then
					l_selection_rev.start
					l_tuple.user := l_selection_rev.read_string ("user")
					l_tuple.creation_time := l_selection_rev.read_integer ("creation_time")

						-- get last revision for tags
					select_query.set_map_name (l_selection.read_integer ("issue_id"), "issue_id")
					l_selection_rev := select_query.execute_query ("[
						SELECT r.issue_revision_id, r.issue_id, r.creation_time, u.name AS user, r.`text`
						FROM issue_revision r
						LEFT JOIN user u USING (user_id)
						WHERE issue_id=:issue_id
						ORDER BY creation_time DESC
						LIMIT 1
						]")
					l_selection_rev.start

						-- get tags
					l_tuple.tags := ""
					select_query.set_map_name (l_selection_rev.read_integer ("issue_revision_id"), "issue_revision_id")
					l_selection_tags := select_query.execute_query ("[
							SELECT t.`text`
							FROM issue_tag_association a
							LEFT JOIN tag_text t USING (tag_id)
							WHERE a.issue_revision_id=:issue_revision_id
							ORDER BY t.`text` ASC
							]")
					from
						l_selection_tags.start
					until
						l_selection_tags.after
					loop
						if l_tuple.tags.is_empty then
							l_tuple.tags := l_selection_tags.read_string ("text")
						else
							l_tuple.tags := l_tuple.tags + ", " + l_selection_tags.read_string ("text")
						end
						l_selection_tags.forth
					end
				end

				Result.force (l_tuple, i)
				i := i + 1
				l_selection.forth
			end
		ensure
			Result_set: Result /= Void
		end

	retrieve_changed_issues (a_project_id: INTEGER; a_date: INTEGER): DS_HASH_TABLE[TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING], INTEGER] is
			-- Retrieve changed issues for a_project
		require
			a_project_id: a_project_id > 0
		local
			l_issue_t: TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING]
			l_issues: DS_HASH_TABLE[TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING], INTEGER]
			i: INTEGER
		do
				-- currently we reuse an existing call (this could be optimized by a specialised implementation: Move the filtering part to the db)
			l_issues := retrieve_project_issues (a_project_id)
			create Result.make_default

			from
				l_issues.start
				i := 0
			until
				l_issues.after
			loop
				-- Filter matching issues
				-- Because Eiffel does not support removal while iterating we need an additional data structure to add matching issues
				l_issue_t := l_issues.item_for_iteration
				if l_issue_t.last_modified >= a_date then
					Result.force (l_issues.item_for_iteration, i)
					i := i + 1
				end

				l_issues.forth
			end
		ensure
			Result_set: Result /= Void
		end

	 search_issues (a_project_id: INTEGER; a_search_criteria: DS_HASH_TABLE [STRING, STRING]): DS_HASH_TABLE[TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING], INTEGER] is
			-- search issues within a given project according to some search criteria
		require
			a_project_id_valid: a_project_id > 0
			a_search_criteria_valid: a_search_criteria /= Void
		local
			l_selection: SELECTION_RESULT
			l_selection_rev: SELECTION_RESULT
			l_selection_tags: SELECTION_RESULT
			l_tuple: TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING]
			l_search_value: STRING
			l_reporter_to_search: STRING
			l_description_to_search: STRING
			l_description: STRING
			l_tags_to_search: LIST[STRING]
			l_tag_sequence: STRING
			l_tag: STRING
			i: INTEGER
		do
			select_query.set_map_name (a_project_id, "project_id")
			l_search_value := a_search_criteria.item("title")
			if l_search_value /= Void then
					-- title must contain the value of the search criterion
				select_query.set_map_name ("%%" + l_search_value + "%%", "title")
			else
				select_query.set_map_name ('%%', "title")
			end

			l_selection := select_query.execute_query ("[
					SELECT i.issue_id, i.project_id, i.project_issue_id, i.title, i.private, i.last_modified
					FROM `issue` i
					WHERE i.project_id=:project_id AND i.title LIKE :title
					]")

				-- get result and put it into an arrayed list as named tuples
			from
				create Result.make (l_selection.count)
				l_selection.start
				i := 0
			until
				l_selection.after
			loop
				create l_tuple
				l_tuple.project_issue_id := l_selection.read_integer ("project_issue_id")
				l_tuple.title := l_selection.read_string ("title")
				l_tuple.last_modified := l_selection.read_integer ("last_modified")

					-- get first revision (for user and creation_time)
				select_query.set_map_name (l_selection.read_integer ("issue_id"), "issue_id")

				l_selection_rev := select_query.execute_query ("[
						SELECT r.issue_revision_id, r.issue_id, r.creation_time, r.text, u.name AS user
						FROM issue_revision r
						LEFT JOIN user u USING (user_id)
						WHERE r.issue_id=:issue_id
						ORDER BY r.creation_time ASC
						LIMIT 1
						]")

				if l_selection_rev.count = 1 then
					l_selection_rev.start
					l_tuple.user := l_selection_rev.read_string ("user")

						-- search for reporter and description
					l_reporter_to_search := a_search_criteria.item("reporter")
					l_description_to_search := a_search_criteria.item("description")
					l_description := l_selection_rev.read_string ("text")

					if ((l_reporter_to_search = Void or else l_tuple.user.is_equal (l_reporter_to_search)) and
							(l_description_to_search = Void or else l_description.has_substring (l_description_to_search)))  then
						l_tuple.creation_time := l_selection_rev.read_integer ("creation_time")

							-- get last revision for tags
						select_query.set_map_name (l_selection.read_integer ("issue_id"), "issue_id")
						l_selection_rev := select_query.execute_query ("[
							SELECT r.issue_revision_id, r.issue_id, r.creation_time, u.name AS user, r.`text`
							FROM issue_revision r
							LEFT JOIN user u USING (user_id)
							WHERE issue_id=:issue_id
							ORDER BY creation_time DESC
							LIMIT 1
							]")
						l_selection_rev.start

							-- get tags
						l_tuple.tags := ""
						select_query.set_map_name (l_selection_rev.read_integer ("issue_revision_id"), "issue_revision_id")
						l_selection_tags := select_query.execute_query ("[
								SELECT t.`text`
								FROM issue_tag_association a
								LEFT JOIN tag_text t USING (tag_id)
								WHERE a.issue_revision_id=:issue_revision_id
								ORDER BY t.`text` ASC
								]")

							-- prepare tag search
						l_tag_sequence := a_search_criteria.item("tags")
						if l_tag_sequence /= Void then
							l_tags_to_search :=  l_tag_sequence.split (',')
							from
								l_tags_to_search.start
							until
								l_tags_to_search.after
							loop
								l_tag := l_tags_to_search.item
									-- remove trailing and leading whitespaces
								l_tag.right_adjust
								l_tag.left_adjust
								l_tags_to_search.forth
							end
						else
							create {ARRAYED_LIST[STRING]}l_tags_to_search.make (0)
						end
							-- IMPORTANT: We want to compare strings with is_equal and NOT with '=' (used in prune later on)!!!
						l_tags_to_search.compare_objects

						if a_search_criteria.item("status") /= Void then
								-- The status is treated as a regular tag
							l_tags_to_search.force ("status::" + a_search_criteria.item("status"))
						end

						if a_search_criteria.item("assignee") /= Void then
								-- The assignee is treated as a regular tag
							l_tags_to_search.force ("assigned::" + a_search_criteria.item("assignee"))
						end

						if a_search_criteria.item("priority") /= Void then
								-- The priority is treated as a regular tag
							l_tags_to_search.force ("priority::" + a_search_criteria.item("priority"))
						end

						from
							l_selection_tags.start
						until
							l_selection_tags.after
						loop
							l_tag := l_selection_tags.read_string ("text")

							if l_tuple.tags.is_empty then
								l_tuple.tags := l_tag
							else
								l_tuple.tags := l_tuple.tags + ", " + l_tag
							end
								-- remove the tag from the tags to search
								-- IMPORTANT: we have to reset the index for prune to work!!!
							l_tags_to_search.start
							l_tags_to_search.prune (l_tag)

							l_selection_tags.forth
						end

						-- If all tags to search for are found l_tags_to_search should now be empty
						if l_tags_to_search.is_empty then
							Result.force (l_tuple, i)
							i := i + 1
						end

						-- TODO define constants for search criteria
					end
				end

				l_selection.forth
			end
		ensure
			Result_set: Result /= Void
		end

	retrieve_user_issues (a_user_id: INTEGER): DS_HASH_TABLE[TUPLE [project_issue_id: INTEGER; project_name: STRING; creation_time: INTEGER; title: STRING; tags: STRING], INTEGER] is
			-- Retrieve issues reported by user with a_user_id.
		require
			a_user_id_ok: a_user_id > 0
		local
			l_selection_issue_ids: SELECTION_RESULT
			l_selection_issues: SELECTION_RESULT
			l_selection_project: SELECTION_RESULT
			l_selection_revisions: SELECTION_RESULT
			l_selection_tags: SELECTION_RESULT
			l_tuple: TUPLE [project_issue_id: INTEGER; project_name: STRING; creation_time: INTEGER; title: STRING; tags: STRING]
			i: INTEGER
		do
				-- get all issues reported by user with user_id
			select_query.set_map_name (a_user_id, "user_id")
			l_selection_issue_ids := select_query.execute_query ("[
					SELECT ir.issue_id, ir.creation_time FROM (
						SELECT * FROM (
							SELECT * FROM `issue_revision`
							ORDER BY creation_time ASC
						) sorted_issues
						GROUP BY sorted_issues.issue_id
					) ir
					WHERE ir.user_id=:user_id
					ORDER BY ir.creation_time DESC
					]")

				-- loop through retrieved issue_ids and get corresponding issue informations
			from
				create Result.make (l_selection_issue_ids.count)
				l_selection_issue_ids.start
				i := 0
			until
				l_selection_issue_ids.after
			loop
				create l_tuple

					-- get corresponding issue
				select_query.set_map_name (l_selection_issue_ids.read_integer ("issue_id"), "issue_id")
				l_selection_issues := select_query.execute_query ("[
					SELECT project_id, project_issue_id, title, private
					FROM `issue`
					WHERE issue_id=:issue_id
					]")

				check
					valid_issue: l_selection_issues.count = 1
				end

				l_selection_issues.start

				l_tuple.project_issue_id := l_selection_issues.read_integer ("project_issue_id")
				l_tuple.title := l_selection_issues.read_string ("title")

					-- get corresponding project name
				select_query.set_map_name (l_selection_issues.read_integer ("project_id"), "project_id")
				l_selection_project := select_query.execute_query ("[
					SELECT name
					FROM `project`
					WHERE project_id=:project_id
					]")

				check
					valid_project: l_selection_project.count = 1
				end

				l_selection_project.start
				l_tuple.project_name := l_selection_project.read_string ("name")


					-- get last revision of issue for tags
				select_query.set_map_name (l_selection_issue_ids.read_integer ("issue_id"), "issue_id")
				l_selection_revisions := select_query.execute_query ("[
					SELECT issue_revision_id, creation_time
					FROM issue_revision
					WHERE issue_id=:issue_id
					ORDER BY creation_time DESC
					LIMIT 1
					]")

				check
					valid_revision: l_selection_revisions.count = 1
				end

				l_selection_revisions.start
				l_tuple.creation_time := l_selection_revisions.read_integer ("creation_time")

					-- get corresponding tags
				l_tuple.tags := ""
				select_query.set_map_name (l_selection_revisions.read_integer ("issue_revision_id"),  "issue_revision_id")
				l_selection_tags := select_query.execute_query ("[
					SELECT t.text
					FROM issue_tag_association a
					LEFT JOIN tag_text t USING (tag_id)
					WHERE a.issue_revision_id=:issue_revision_id
					ORDER BY t.text ASC
					]")

				from
					l_selection_tags.start
				until
					l_selection_tags.after
				loop
					if l_tuple.tags.is_empty then
						l_tuple.tags := l_selection_tags.read_string ("text")
					else
						l_tuple.tags := l_tuple.tags + ", " + l_selection_tags.read_string ("text")
					end

					l_selection_tags.forth
				end

				Result.force (l_tuple, i)
				l_selection_issue_ids.forth
				i := i + 1
			end

		ensure
			Result_set: Result /= Void
		end

	next_project_issue_id (a_project_id: INTEGER): INTEGER is
			-- Get id of last autoincrement
		local
			l_res: SELECTION_RESULT
		do
			select_query.set_map_name (a_project_id, "project_id")
			l_res := select_query.execute_query ("SELECT IFNULL(MAX(project_issue_id), 0) AS id FROM issue WHERE project_id = :project_id")
			l_res.start
			Result := l_res.read_integer ("id") + 1
		end

	retrieve_last_revision (a_issue_id: INTEGER): INTEGER is
			-- retrieve the last issue revision of issue `a_id'
		require
			a_issue_id_ok: a_issue_id > 0
		local
			l_res: SELECTION_RESULT
		do
				-- read the issue_revision_id from the first revision
			select_query.set_map_name (a_issue_id, "issue_id")
			l_res := select_query.execute_query ("SELECT IFNULL(MAX(issue_revision_id), 0) AS id FROM issue_revision WHERE issue_id = :issue_id")
			l_res.start
			Result := l_res.read_integer ("id")
			handle_errors_and_warnings
		ensure
			result_is_valid: Result > 0
		end

	retrieve_project_tags (a_project_id: INTEGER): ARRAY[STRING] is
			-- retrieve all tags used in the issues of the given project (union)
		require
			a_project_id: a_project_id > 0
		local
			l_issues: DS_HASH_TABLE[TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; tags: STRING], INTEGER]
			l_tags: DS_HASH_SET[STRING]
			l_issue_tags: LIST[STRING]
			l_tag_sequence: STRING
		do
			l_issues := retrieve_project_issues(a_project_id)
				-- IMPORTANT: make sure that is_equal is used for string comparison
			create l_tags.make_equal (1)

			from
				l_issues.start
			until
				l_issues.after
			loop
				-- iterate over all tags and add them to the set
				l_tag_sequence := l_issues.item_for_iteration.tags
				if l_tag_sequence /= Void then
					l_issue_tags := l_tag_sequence.split (',')
					from
						l_issue_tags.start
					until
						l_issue_tags.after
					loop
						l_issue_tags.item.right_adjust
						l_issue_tags.item.left_adjust
						if not l_issue_tags.item.is_empty then
							l_tags.force (l_issue_tags.item)
						end

						l_issue_tags.forth
					end
				end

				l_issues.forth
			end

			Result := l_tags.to_array
		ensure
			Result /= Void
		end

feature -- Insert queries

	insert_issue_workitem (a_issue: WORKITEM_ISSUE) is
			-- Add a_issue as a new issue workitem.
		require
			a_issue_not_void: a_issue /= Void
		local
			l_store: DB_STORE
		do
			create l_store.make
			l_store.set_repository (db_handler.db_repositories.item ("workitem_issue"))
			l_store.put (a_issue)
			handle_errors_and_warnings
		end

	insert_issue (a_issue: ISSUE) is
			-- Add a_issue as a new issue.
		require
			a_issue_not_void: a_issue /= Void
		local
			l_store: DB_STORE
			l_null: DOUBLE
		do
			l_null := db_handler.db_control.numeric_null_value
			db_handler.db_control.set_numeric_null_value ({INTEGER_32}.min_value)

			create l_store.make
			l_store.set_repository (db_handler.db_repositories.item ("issue"))
			l_store.put (a_issue)
			handle_errors_and_warnings

			db_handler.db_control.set_numeric_null_value (l_null)
		end

	insert_issue_revision (a_issue_revision: ISSUE_REVISION) is
			-- Add a_issue_revision as a new issue revision.
		require
			a_issue_revision_not_void: a_issue_revision /= Void
		local
			l_store: DB_CHANGE
			l_last_id: INTEGER
		do
				-- insertion into database is done with manual SQL query because EIFFEL_STORE maps 0 and "" to NULL which is incorrect
			create l_store.make
			l_store.set_map_name(a_issue_revision.issue_id, "issue_id")
			l_store.set_map_name(a_issue_revision.creation_time, "creation_time")
			l_store.set_map_name(a_issue_revision.user_id, "user_id")
			l_store.modify("INSERT INTO `issue_revision` (`issue_id`, `creation_time`, `user_id`) VALUES (:issue_id, :creation_time, :user_id)")

			handle_errors_and_warnings
			l_last_id := last_insert_id

				-- now insertion of (possibly) empty text - has to be done with an UPDATE statement because an
				-- INSERT statement would also lead to DB failure and a mapping to NULL
			create l_store.make
			l_store.set_map_name(l_last_id, "id")
			l_store.set_map_name(a_issue_revision.text, "text")
			l_store.modify("UPDATE `issue_revision` SET `text`=:text WHERE `issue_revision_id`=:id")
			handle_errors_and_warnings
		end

	insert_issue_tag_association (a_issue_revision_id: INTEGER; a_tag_id: INTEGER) is
			-- Add a_issue_tag_association as a new issue tag association
		require
			a_issue_revision_id_ok: a_issue_revision_id > 0
			a_tag_id_ok: a_tag_id > 0
		local
			l_store: DB_STORE
			l_issue_tag_association: ISSUE_TAG_ASSOCIATION
		do
			create l_issue_tag_association.make
			l_issue_tag_association.set_issue_revision_id (a_issue_revision_id)
			l_issue_tag_association.set_tag_id (a_tag_id)
			create l_store.make
			l_store.set_repository (db_handler.db_repositories.item ("issue_tag_association"))
			l_store.put (l_issue_tag_association)
			handle_errors_and_warnings
		end

	remove_tags_for_revision (a_issue_revision_id: INTEGER) is
			-- Remove all tags for revision `a_issue_revision_id'
		require
			a_issue_revision_id_ok: a_issue_revision_id > 0
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name(a_issue_revision_id, "rev_id")
			l_store.modify("DELETE FROM issue_tag_association WHERE `issue_revision_id` = :rev_id")
			handle_errors_and_warnings
		end

feature -- Update queries

	update_issue (a_issue_id: INTEGER; a_title: STRING; a_is_private: BOOLEAN) is
			-- update issue `a_id' with a new title and a new visibility. Also adjust last_modified timestamp.
		require
			a_issue_ok: a_issue_id > 0
			a_title_ok: a_title /= Void
		local
			l_store: DB_CHANGE
			l_date: DATE_TIME
			l_modified_timestamp: INTEGER
		do
			create l_store.make
			create l_date.make_now_utc
			l_modified_timestamp := l_date.definite_duration (create {DATE_TIME}.make (1970, 1, 1, 0, 0, 0)).seconds_count.as_integer_32

			l_store.set_map_name(a_issue_id, "issue_id")
			l_store.set_map_name(a_title, "title")
			l_store.set_map_name(a_is_private, "private")
			l_store.set_map_name(l_modified_timestamp, "last_modified")
			l_store.modify("UPDATE `issue` SET `title` = :title, private = :private, last_modified = :last_modified WHERE `issue_id` = :issue_id")
			handle_errors_and_warnings
		end

	update_first_revision (a_issue_id: INTEGER; a_description: STRING) is
			-- update the first revision of issue `a_id' with a new description
		require
			a_issue_id_ok: a_issue_id > 0
			a_description_ok: a_description /= Void
		local
			l_store: DB_CHANGE
			l_res: SELECTION_RESULT
			first_revision_id: INTEGER
		do
				-- read the issue_revision_id from the first revision
			select_query.set_map_name (a_issue_id, "issue_id")
			l_res := select_query.execute_query ("SELECT IFNULL(MIN(issue_revision_id), 0) AS id FROM issue_revision WHERE issue_id = :issue_id")
			l_res.start
			first_revision_id := l_res.read_integer ("id")
			if (first_revision_id > 0) then
				create l_store.make
				l_store.set_map_name(first_revision_id, "issue_rev_id")
				l_store.set_map_name(a_description, "text")
				l_store.modify ("UPDATE issue_revision SET text = :text WHERE issue_revision_id = :issue_rev_id")
			end
			handle_errors_and_warnings
		end

	update_last_modified_timestamp (a_issue_id: INTEGER; a_date: DATE_TIME) is
			-- Adjusts the last_modified timestamp of an issue
		require
			a_issue_ok: a_issue_id > 0
			a_date_ok: a_date /= Void
		local
			l_store: DB_CHANGE
			l_modified_timestamp: INTEGER
		do
			create l_store.make
			l_modified_timestamp := a_date.definite_duration (create {DATE_TIME}.make (1970, 1, 1, 0, 0, 0)).seconds_count.as_integer_32

			l_store.set_map_name(l_modified_timestamp, "last_modified")
			l_store.set_map_name (a_issue_id, "issue_id");
			l_store.modify("UPDATE `issue` SET last_modified = :last_modified WHERE `issue_id` = :issue_id")
			handle_errors_and_warnings
		end

end