note
	description: "Database access to issue data."
	author: "Peter Wyss <wyssp@ethz.ch>"
	date: "$Date$"
	revision: "$Revision$"

class
	ISSUE_ACCESS

inherit
	DATABASE_ACCESS

	A_SHARED_LOGGERS

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;
				deadline: INTEGER;
				work_amount: INTEGER;
				revisions: ARRAYED_LIST [
					TUPLE [
						creation_time: INTEGER;
						user: STRING;
						text: STRING;
						text_type: INTEGER;
						tags: STRING
					]
				]
			]
			-- Last retrieved issue by a retrieve query.

	last_issue_count: INTEGER
			-- The number of most recently retrieved issues.
			-- Note: This is currently only used by `retrieve_user_issue_count'

	last_simple_issue: ISSUE

	last_planning_data: TUPLE[deadline: INTEGER; work_amount: INTEGER]

feature -- Tag constants

	status_open: STRING = "status::open"
			-- Tag for open issues.

	status_closed: STRING = "status::closed"
			-- Tag for closed issues.

feature -- Select queries

	retrieve_issue_by_id (a_issue_id: INTEGER)
			-- Retrieve an issue by a_issue_id
		require
			a_issue_id_ok: a_issue_id > 0
		local
			l_issue: ISSUE
			l_selection_result: SELECTION_RESULT
		do
			select_query.set_map_name (a_issue_id, "issue_id")
			l_selection_result := select_query.execute_query ("SELECT * FROM issue WHERE issue_id=:issue_id")

			if l_selection_result.count = 1 then
				l_selection_result.start

				create l_issue.make
				l_issue.set_issue_id (l_selection_result.read_integer ("issue_id"))
				l_issue.set_project_id (l_selection_result.read_integer ("project_id"))
				l_issue.set_project_issue_id (l_selection_result.read_integer ("project_issue_id"))
				l_issue.set_title (l_selection_result.read_string ("title"))
				l_issue.set_private (l_selection_result.read_integer ("private"))
				l_issue.set_last_modified (l_selection_result.read_integer ("last_modified"))
				l_issue.set_deadline (l_selection_result.read_integer ("deadline"))
				l_issue.set_work_amount (l_selection_result.read_integer ("work_amount"))

				last_simple_issue := l_issue
				is_found := True
			else
				is_found := False
			end
		end

	retrieve_issue_by_project_issue_id (a_project_id: INTEGER; a_project_issue_id: INTEGER; a_include_private_issues: BOOLEAN)
			-- 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_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;
				deadline: INTEGER;
				work_amount: INTEGER;
				revisions: ARRAYED_LIST [
					TUPLE [
						creation_time: INTEGER;
						user: STRING;
						text: STRING;
						text_type: INTEGER;
						tags: STRING
					]
				]
			]
			l_revisions: ARRAYED_LIST [TUPLE [creation_time: INTEGER; user: STRING; text: STRING; text_type: INTEGER; tags: STRING]]
			l_revision: TUPLE [creation_time: INTEGER; user: STRING; text: STRING; text_type: INTEGER; 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")
			if a_include_private_issues then
				l_selection := select_query.execute_query ("SELECT * FROM `issue` WHERE project_id=:project_id AND project_issue_id=:project_issue_id")
			else
				l_selection := select_query.execute_query ("SELECT * FROM `issue` WHERE project_id=:project_id AND project_issue_id=:project_issue_id AND private=0")
			end
			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")
				l_complete_issue.deadline := l_selection.read_integer ("deadline")
				l_complete_issue.work_amount := l_selection.read_integer ("work_amount")

					-- 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`,
					  r.`text_type`,
					  GROUP_CONCAT(tt.text ORDER BY tt.text ASC SEPARATOR ', ') AS tags
					FROM issue_revision r
					LEFT JOIN user u USING (user_id)
					JOIN `issue_tag_association` ita ON (ita.issue_revision_id = r.issue_revision_id)
					JOIN `tag_text` tt ON (tt.tag_id = ita.tag_id )
					WHERE issue_id=:issue_id
					GROUP BY r.issue_revision_id asc
					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

						l_revision.tags := l_selection.read_string ("tags")
						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_revision.text_type := l_selection.read_integer ("text_type")
						l_revisions.force (l_revision)

						-- increment
						l_selection.forth
					end

					-- The tags of the "main" issue are the ones from the most-recent "issue-description" revision
					l_complete_issue.tags := l_revisions.last.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; a_include_private_issues: BOOLEAN; a_min_date: INTEGER):
		DS_HASH_TABLE[
			TUPLE [
				project_issue_id: INTEGER;
				creation_time: INTEGER;
				last_modified: INTEGER;
				user: STRING;
				title: STRING;
				deadline: INTEGER;
				work_amount: INTEGER;
				tags: DS_ARRAYED_LIST [STRING]
			],
		INTEGER]

			-- Retrieve issues for a_project
		require
			a_project_id: a_project_id > 0
		local
			l_sql: STRING
			l_selection: SELECTION_RESULT
			l_tuple: TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; deadline: INTEGER; work_amount: INTEGER; tags: DS_ARRAYED_LIST [STRING]]
			l_creator: TUPLE[issue_revision_id: INTEGER; user: STRING; creation_time: INTEGER]
			i: INTEGER
		do
			select_query.set_map_name (a_project_id, "project_id")

			l_sql := "[
						SELECT i.issue_id, i.project_id, i.project_issue_id, i.title, i.private, i.last_modified, i.deadline, i.work_amount
						FROM `issue` i
						WHERE i.project_id=:project_id
					]"

			if not a_include_private_issues then
				l_sql.append(" AND i.private=0")
			end

			if a_min_date > 0 then
				select_query.set_map_name (a_min_date, "min_date")
				l_sql.append(" AND i.last_modified >= :min_date")
			end

			l_selection := select_query.execute_query (l_sql)

				-- 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")
				l_tuple.deadline := l_selection.read_integer ("deadline")
				l_tuple.work_amount := l_selection.read_integer ("work_amount")

					-- get first revision

				l_creator := retrieve_issue_creator(l_selection.read_integer ("issue_id"))

				if l_creator /= Void then
					l_tuple.user := l_creator.user
					l_tuple.creation_time := l_creator.creation_time

						-- get last revision for tags
					l_tuple.tags := retrieve_issue_tags (l_selection.read_integer ("issue_id"))
				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; a_include_private_issues: BOOLEAN): DS_HASH_TABLE[TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; deadline: INTEGER; work_amount: INTEGER; tags: DS_ARRAYED_LIST [STRING]], INTEGER]
			-- Retrieve changed issues for a_project
		require
			project_id_ok: a_project_id > 0
			date_ok: a_date >= 0
		do
			Result := retrieve_project_issues (a_project_id, a_include_private_issues, a_date)
		ensure
			Result_set: Result /= Void
		end

	 search_issues (a_project_id: INTEGER; a_search_criteria: DS_HASH_TABLE [A_STRING_VALUE, A_STRING_VALUE]; a_include_private_issues: BOOLEAN): DS_HASH_TABLE[TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; deadline: INTEGER; work_amount: INTEGER; tags: DS_ARRAYED_LIST [STRING]], INTEGER]
			-- 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_tuple: TUPLE [project_issue_id: INTEGER; creation_time: INTEGER; last_modified: INTEGER; user: STRING; title: STRING; deadline: INTEGER; work_amount: INTEGER; tags: DS_ARRAYED_LIST [STRING]]
			l_search_value: A_STRING_VALUE
			l_reporter_to_search: A_STRING_VALUE
			l_description_to_search: A_STRING_VALUE
			l_description: STRING
			l_tags_to_search: LIST [STRING]
			l_tag_sequence: A_STRING_VALUE
			l_tag: STRING

			l_issue_id: INTEGER
			l_creator: TUPLE [issue_revision_id: INTEGER; user: STRING; creation_time: INTEGER]
			i: INTEGER
		do
			select_query.set_map_name (a_project_id, "project_id")
			l_search_value := a_search_criteria.item (create {A_STRING_VALUE}.make ("title"))
			if l_search_value /= Void then
					-- title must contain the value of the search criterion
				select_query.set_map_name ("%%" + l_search_value.value + "%%", "title")
			else
				select_query.set_map_name ('%%', "title")
			end

			if a_include_private_issues then
				l_selection := select_query.execute_query ("[
						SELECT i.issue_id, i.project_id, i.project_issue_id, i.title, i.private, i.last_modified, i.deadline, i.work_amount
						FROM `issue` i
						WHERE i.project_id=:project_id AND i.title LIKE :title
						]")
			else
				l_selection := select_query.execute_query ("[
						SELECT i.issue_id, i.project_id, i.project_issue_id, i.title, i.private, i.last_modified, i.deadline, i.work_amount
						FROM `issue` i
						WHERE i.project_id=:project_id AND i.title LIKE :title AND i.private=0
						]")
			end

				-- 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
				l_issue_id := l_selection.read_integer ("issue_id")

				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")
				l_tuple.deadline := l_selection.read_integer ("deadline")
				l_tuple.work_amount := l_selection.read_integer ("work_amount")

					-- get first revision (for user and creation_time)
				l_creator := retrieve_issue_creator (l_issue_id)
				if l_creator /= Void then
					l_tuple.user := l_creator.user
					l_tuple.creation_time := l_creator.creation_time
					l_description := retrieve_issue_description (l_issue_id)

						-- search for reporter and description
					l_reporter_to_search := a_search_criteria.item(create {A_STRING_VALUE}.make ("reporter"))
					l_description_to_search := a_search_criteria.item(create {A_STRING_VALUE}.make ("description"))

					if ((l_reporter_to_search = Void or else l_tuple.user.is_equal (l_reporter_to_search.value)) and
							(l_description_to_search = Void or else l_description.has_substring (l_description_to_search.value)))  then

							-- prepare tag search
						l_tag_sequence := a_search_criteria.item(create {A_STRING_VALUE}.make ("tags"))
						if l_tag_sequence /= Void then
							l_tags_to_search :=  l_tag_sequence.value.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(create {A_STRING_VALUE}.make ("status")) /= Void then
								-- The status is treated as a regular tag
							l_tags_to_search.force ("status::" + a_search_criteria.item(create {A_STRING_VALUE}.make ("status")).value)
						end

						if a_search_criteria.item (create {A_STRING_VALUE}.make ("resolution")) /= Void then
								-- The resolution is treated as a regular tag
							l_tags_to_search.force ("resolution::" + a_search_criteria.item (create {A_STRING_VALUE}.make ("resolution")).value)
						end

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

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

						if not l_tags_to_search.is_empty then
							-- get the current tags
							l_tuple.tags := retrieve_issue_tags (l_issue_id)
							from l_tuple.tags.start
							until l_tuple.tags.after
							loop
								l_tag := l_tuple.tags.item_for_iteration
									-- 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)

								-- inc
								l_tuple.tags.forth
							end
						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_issue_count (a_user_id: INTEGER)
			-- Retrieve the number of reported issues by the user with `a_user_id'.
			-- Note: This feature is related to `retrieve_user_issues'.
		require
			a_user_id_ok: a_user_id > 0
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_user_id, "user_id")
			l_selection := select_query.execute_query ("[
					SELECT COUNT(ir.issue_id) AS cnt 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
				]")
			if l_selection.is_empty then
				last_issue_count := 0
				is_found := False
			else
				l_selection.start
				last_issue_count := l_selection.read_integer ("cnt")
				is_found := True
			end
		ensure
			Nonnegative: last_issue_count >= 0
		end

	retrieve_user_issues (a_user_id: INTEGER; a_start_index: INTEGER; a_limit: INTEGER; a_sort_column: STRING; a_sort_order: STRING): DS_ARRAYED_LIST[TUPLE [project_issue_id: INTEGER; title: STRING; deadline: INTEGER; work_amount: INTEGER; project_name: STRING; last_updated: INTEGER; tags: STRING; status: STRING; assigned: STRING]]
			-- Retrieve issues reported by user with `a_user_id'.
		require
			a_user_id_ok: a_user_id > 0
		local
			l_sort_column: STRING
			l_sort_order: STRING
			l_selection: SELECTION_RESULT
			l_tuple: TUPLE [project_issue_id: INTEGER; title: STRING; deadline: INTEGER; work_amount: INTEGER; project_name: STRING; last_updated: INTEGER; tags: STRING; status: STRING; assigned: STRING]
			l_sql: STRING
		do
				-- get all issues reported by user with user_id
			select_query.set_map_name (a_user_id, "user_id")
			select_query.set_map_name (a_limit, "limit")

			-- sanitize sorting column
			l_sort_column := sanatize_reported_issues_column (a_sort_column, "last_updated")

			-- sanitize sorting order
			if equal (a_sort_order.as_lower, "asc") then
				l_sort_order := "ASC"
			else
				l_sort_order := "DESC"
			end

			-- This large SQL query retrieves all the data we need.
			-- Annotation:
			--	#1	Retrieve the issue-ID's that the user reported.
			--		This is done by on the issue-revision table: basically we
			--			- group by issue_id's, and thus
			--			- get the earliest revision of each issue
			--		(First subquery after the FROM clause)
			--	#2	Join with issue table. This will net us the basic data, title,
			--		project_issue_id, etc.
			--		(First JOIN clause)
			--	#3  Join with `project` table to get the corresponding project names
			--		(Second JOIN clause)
			--	We now need to get the latest tags for each of the issues. Therefore
			--	we need the latest revision.
			--	#4	Get the latest revision of each issue.
			--		(Third JOIN clause, subquery into table `latest_ir`)
			--	#5	With the latest revision, retrieve the corresponding tag-data. We join
			--		the corresponding tables and do a GROUP_CONCAT to get all tags in one
			--		single string.
			--		(Fourth and Fifth JOIN clause, subquery into table `ita` and `tt` respectively)
			--	We display the "status" and "assigned" tags as separate columns in the frontend.
			--  For being able to sort by these, we need to have them as separate columns
			--	in the query. This is done as in to #5, but we use LEFT JOINs and
			--	GROUP_CONCAT to eliminate duplicates
			--	#6	Get the "status" tag
			--		(Fifth JOIN clause, subquery into table `status`)
			--	#7	Get the "assigned" tag.
			--		NOTE: This *has* to be a LEFT JOIN, as not all issues might have an assigned-tag.
			--		(Sixth JOIN clause, subquery into table `assignment`)
			l_sql := "[
				SELECT
				  i.project_issue_id, i.title, i.deadline, i.work_amount,
				  p.name AS project_name,
				  latest_ir.creation_time as last_updated,
				  GROUP_CONCAT(tt.text ORDER BY tt.text ASC SEPARATOR ', ') as tags,
				  GROUP_CONCAT(status.text separator ', ') AS status,
				  GROUP_CONCAT(assignment.text separator ', ') AS assigned
				FROM
				(
					SELECT
						MIN(issue_revision_id), issue_id, user_id
					FROM `issue_revision`
					JOIN issue i USING(issue_id)
					GROUP BY issue_id
				) ir
				JOIN `issue` i USING (issue_id)
				JOIN `project` p USING (project_id)
				JOIN (
				    SELECT
					MAX(issue_revision_id) as latest_revision, issue_id, creation_time
				    FROM issue_revision
				    GROUP BY issue_id 
				  ) AS latest_ir ON (latest_ir.issue_id = ir.issue_id)
				JOIN `issue_tag_association` ita ON (ita.issue_revision_id = latest_ir.latest_revision)
				JOIN `tag_text` tt ON (tt.tag_id = ita.tag_id )
				LEFT JOIN `tag_text` status ON (status.tag_id = ita.tag_id AND status.text LIKE 'status::%')
				LEFT JOIN `tag_text` assignment ON (assignment.tag_id = ita.tag_id AND assignment.text LIKE 'assigned::%')

				WHERE ir.user_id=:user_id
				GROUP BY i.issue_id
			]"
			-- Add sort order & direction
			l_sql.append(" ORDER BY " + l_sort_column + " " + l_sort_order + ", last_updated DESC");

			-- Workaround for a bug in eiffel store which maps 0 to NULL in the query variables
			if a_start_index = 0 then
				l_sql.append (" LIMIT 0, :limit")
			else
				select_query.set_map_name (a_start_index, "start")
				l_sql.append (" LIMIT :start, :limit")
			end

			l_selection := select_query.execute_query (l_sql)

			from
				l_selection.start
				create Result.make (l_selection.count)
			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.deadline := l_selection.read_integer ("deadline")
				l_tuple.work_amount := l_selection.read_integer ("work_amount")
				l_tuple.project_name := l_selection.read_string ("project_name")
				l_tuple.last_updated := l_selection.read_integer ("last_updated")
				l_tuple.tags := l_selection.read_string ("tags")
				l_tuple.status := l_selection.read_string ("status")
				l_tuple.assigned := l_selection.read_string_ref ("assigned")
				if l_tuple.assigned = Void then
					-- The issue wasn't assigned to anyone
					l_tuple.assigned := ""
				end

				Result.force_last (l_tuple)

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

	next_project_issue_id (a_project_id: INTEGER): INTEGER
			-- 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
			-- 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")
		ensure
			result_is_valid: Result >= 0
		end

	retrieve_last_revision_from_project_issue_id (a_project_id, a_project_issue_id: INTEGER): INTEGER
			-- Retrieve the last issue revision of the project-issue `a_project_issue_id' of project `a_project_id'
		require
			project_id_ok: a_project_id > 0
			issue_id_ok: a_project_issue_id > 0
		local
			l_selection_result: SELECTION_RESULT
		do
				-- read the issue_revision_id from the first revision
			select_query.set_map_name (a_project_id, "project_id")
			select_query.set_map_name (a_project_issue_id, "project_issue_id")
			l_selection_result := select_query.execute_query ("[
				SELECT
				    MAX(issue_revision_id) AS max_rev
				FROM
				    issue i
				JOIN
				    issue_revision ir USING (issue_id)
				WHERE
				    i.project_id = :project_id
				    AND
				    i.project_issue_id = :project_issue_id
				]")

			l_selection_result.start
			Result := l_selection_result.read_integer ("max_rev")
		ensure
			result_is_valid: Result >= 0
		end

	retrieve_project_tags (a_project_id: INTEGER; a_include_private_issues: BOOLEAN): ARRAY[STRING]
			-- 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; deadline: INTEGER; work_amount: INTEGER; tags: DS_ARRAYED_LIST [STRING]], INTEGER]
			l_tags: DS_HASH_SET [STRING]
			l_issue_tags: DS_ARRAYED_LIST [STRING]
--			l_tag_sequence: STRING
		do
			l_issues := retrieve_project_issues(a_project_id, a_include_private_issues, 0)
				-- 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
				if l_issues.item_for_iteration.tags /= Void then
					l_issue_tags := l_issues.item_for_iteration.tags
					from
						l_issue_tags.start
					until
						l_issue_tags.after
					loop
						if not l_issue_tags.item_for_iteration.is_empty then
							l_tags.force (l_issue_tags.item_for_iteration.twin)
						end

						l_issue_tags.forth
					end
				end

				l_issues.forth
			end

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

	retrieve_issue_attachments (a_issue_id: INTEGER): DS_ARRAYED_LIST[TUPLE[issue_attachment_id: INTEGER; file_name: STRING; description: STRING]]
			-- retrieve all attachments of an issue
		require
			a_issue_id_ok: a_issue_id > 0
		local
			l_selection_result: SELECTION_RESULT
			l_issue_attachment: TUPLE[issue_attachment_id: INTEGER; file_name: STRING; description: STRING]
		do
			select_query.set_map_name (a_issue_id, "issue_id")
			l_selection_result := select_query.execute_query ("SELECT issue_attachment_id, file_name, description FROM issue_attachment WHERE issue_id=:issue_id")

			create Result.make (l_selection_result.count)
			from
				l_selection_result.start
			until
				l_selection_result.after
			loop
				create l_issue_attachment
				l_issue_attachment.issue_attachment_id := l_selection_result.read_integer ("issue_attachment_id")
				l_issue_attachment.file_name := l_selection_result.read_string ("file_name")
				l_issue_attachment.description := l_selection_result.read_string ("description")

				Result.force_last (l_issue_attachment)

				-- Increment
				l_selection_result.forth
			end
		ensure
			Result /= Void
		end

	retrieve_issue_id (a_project_id, a_project_issue_id: INTEGER): INTEGER
			-- retrieve the issue id from project and project issue id
		require
			a_project_id_ok: a_project_id > 0
			a_project_issue_id_ok: a_project_issue_id > 0
		local
			l_selection_result: SELECTION_RESULT
		do
			select_query.set_map_name (a_project_id, "project_id")
			select_query.set_map_name (a_project_issue_id, "project_issue_id")
			l_selection_result := select_query.execute_query ("SELECT issue_id FROM issue WHERE project_id=:project_id AND project_issue_id=:project_issue_id")

			l_selection_result.start
			if not l_selection_result.after then
				Result := l_selection_result.read_integer ("issue_id")
			else
				Result := -1
			end
		end

	retrieve_project_issue_id_from_issue_revision (a_issue_revision: INTEGER): INTEGER
			-- Given an issues `issue_revision_id', retrieve the issue's `project_issue_id'
		require
			issue_revision_specified: a_issue_revision > 0
		local
			l_selection_result: SELECTION_RESULT
		do
			select_query.set_map_name (a_issue_revision, "issue_rev")
			l_selection_result := select_query.execute_query ("[
				SELECT `project_issue_id`
				FROM `issue`
				JOIN `issue_revision` ir USING(`issue_id`)
				WHERE `ir`.`issue_revision_id` = :issue_rev
				LIMIT 1
				]")
			l_selection_result.start
			if not l_selection_result.after then
				Result := l_selection_result.read_integer ("project_issue_id")
			end
		ensure
			retrieved: Result > 0
		end

	retrieve_subscribed_mail_addesses (a_project_id, a_project_issue_id: INTEGER): DS_ARRAYED_LIST [STRING]
			-- retrieve all users who subscribed an issue
		require
			a_project_id_ok: a_project_id > 0
			a_project_issue_id_ok: a_project_issue_id > 0
		local
			l_selection_result: SELECTION_RESULT
--			l_issue_id: INTEGER
			l_last_revision_id: INTEGER
			l_item: STRING
			l_user_list: ARRAYED_LIST[STRING]
		do
			l_last_revision_id := retrieve_last_revision_from_project_issue_id (a_project_id, a_project_issue_id)
			if l_last_revision_id > 0 then
				select_query.set_map_name (l_last_revision_id, "issue_revision_id")

				l_selection_result := select_query.execute_query ("[
						SELECT DISTINCT tt.text 
						FROM issue_revision JOIN issue_tag_association USING(issue_revision_id) JOIN tag_text tt USING(tag_id) 
						WHERE issue_revision_id=:issue_revision_id AND tt.text like 'subscribed::%'
					]"
				)
			end

			from
				create l_user_list.make (l_selection_result.count)
				l_selection_result.start
			until
				l_selection_result.after
			loop
				l_item := l_selection_result.read_string ("text")
				l_user_list.force (l_item.substring (13, l_item.count))
				l_selection_result.forth
			end

			from
				create Result.make (l_user_list.count)
				l_user_list.start
			until
				l_user_list.after
			loop
				select_query.set_map_name (l_user_list.item, "name")
				l_selection_result := select_query.execute_query ("SELECT email FROM user WHERE name=:name")
				l_selection_result.start
				if not l_selection_result.after then
					Result.force_last (l_selection_result.read_string ("email"))
				end
				l_user_list.forth
			end
		end

	retrieve_planning_data (a_project_id, a_project_issue_id: INTEGER)
			-- retrieve the planning data of an issue
		require
			a_project_id_ok: a_project_id > 0
			a_project_issue_id_ok: a_project_issue_id > 0
		local
			l_selection_result: SELECTION_RESULT
			l_planning_data: TUPLE[deadline: INTEGER; work_amount: INTEGER]
		do
			select_query.set_map_name (a_project_id, "project_id")
			select_query.set_map_name (a_project_issue_id, "project_issue_id")
			l_selection_result := select_query.execute_query ("SELECT deadline, work_amount FROM issue WHERE project_id = :project_id AND project_issue_id = :project_issue_id")

			if l_selection_result.count = 1 then
				l_selection_result.start
				create l_planning_data
				l_planning_data.deadline := l_selection_result.read_integer ("deadline")
				l_planning_data.work_amount := l_selection_result.read_integer ("work_amount")

				last_planning_data := l_planning_data
				is_found := true

			else
				is_found := false
			end
		end

	retrieve_issue_statistics: DS_HASH_TABLE [TUPLE [open: INTEGER; closed: INTEGER], STRING]
			-- Retrieve issue statistics.
		local
			l_element: TUPLE [open: INTEGER; closed: INTEGER]
			l_selection: SELECTION_RESULT
			l_name: STRING
			l_cnt: INTEGER
			l_tag: STRING
		do
			l_selection := select_query.execute_query ("[
				SELECT
					count(*) AS cnt,
					p.name,
					tt.text
				FROM issue_revision ir
				JOIN issue_tag_association tag USING(issue_revision_id)
				JOIN issue i USING(issue_id)
				JOIN tag_text tt USING(tag_id)
				JOIN project p USING(project_id)
				WHERE
						tt.text IN ('
]"
							+ status_open +"', '" + status_closed + "[
')
					AND issue_revision_id=
						(
							SELECT MAX(issue_revision_id)
							FROM issue_revision
							WHERE issue_id=i.issue_id
						)
				GROUP BY project_id, tt.text
			]")
			from
				create Result.make (5000)
				l_selection.start
			until
				l_selection.after
			loop
				l_name := l_selection.read_string ("name")
				if Result.has (l_name) then
					l_element := Result.item (l_name)
				else
					create l_element
					Result.put (l_element, l_name)
				end
				l_cnt := l_selection.read_integer ("cnt")
				l_tag := l_selection.read_string ("text")
				if status_open.is_equal (l_tag) then
					l_element.open := l_cnt
				elseif status_closed.is_equal (l_tag) then
					l_element.closed := l_cnt
				else
					check
						should_not_reach: False
					end
				end

				l_selection.forth
			end
		end

	retrieve_issue_description (a_issue_id: INTEGER): STRING
			-- Retrieve the current, i.e. most recent issue-description of `a_issue_id'.
			-- Note that the result could be a (unified) diff.
		local
			l_selection_result: SELECTION_RESULT
		do
			select_query.set_map_name (a_issue_id, "issue_id")
			l_selection_result := select_query.execute_query ("[
				SELECT
				  ir.text
				FROM
				  issue_revision ir,
				  (
				    SELECT
				      MAX(issue_revision_id) as max_rev_id
				    FROM
				      issue_revision
				    WHERE
				      issue_id = :issue_id
				      AND
				      text_type = 1
				  ) max_rev
				WHERE
				  ir.issue_revision_id = max_rev.max_rev_id
			]")

			create Result.make_empty
			if l_selection_result.count > 0 then
				l_selection_result.start
				Result := l_selection_result.read_string ("text")
			end
		ensure
			created: Result /= Void
		end

	retrieve_issue_tags (a_issue_id: INTEGER): DS_ARRAYED_LIST[STRING]
			-- Retrieve the current, i.e. most recent tags of `a_issue_id'
			-- The returned tags are trimed for whitespace.
		local
			l_selection_result: SELECTION_RESULT
			l_tag: STRING
		do
				-- read the issue_revision_id from the first revision
			select_query.set_map_name (a_issue_id, "issue_id")

			l_selection_result := select_query.execute_query ("[
					SELECT
					  tt.text
					FROM
					  issue_revision ir
					JOIN
					  (
					    SELECT
						    IFNULL(MAX(issue_revision_id), 0) AS last_rev_id
					    FROM
						    issue_revision
					    WHERE issue_id = :issue_id
					  ) maxrev
					JOIN
					  issue_tag_association ita USING (issue_revision_id)
					JOIN
					  tag_text tt USING (tag_id)
					WHERE
					  ir.issue_revision_id = maxrev.last_rev_id
				]"
			)

			create Result.make (l_selection_result.count)
			from
				l_selection_result.start
			until
				l_selection_result.after
			loop
				l_tag := l_selection_result.read_string ("text")
				-- trim
				l_tag.left_adjust
				l_tag.right_adjust

				result.force_last (l_tag.twin)
				-- increment
				l_selection_result.forth
			end
		end

	retrieve_revision_tags (a_issue_revision: INTEGER): DS_ARRAYED_LIST[STRING]
			-- Given an issue revision, returns a list of tags used in it.
			-- Note: this feature uses its own DB-cursor.
		require
			revision_specified: a_issue_revision > 0
		local
			l_tag_sql: STRING
			l_tag_selection: SELECTION_RESULT
			l_tag_select_query: DATABASE_SELECT_QUERY
		do
			create l_tag_select_query.make (Current)
			l_tag_select_query.set_map_name (a_issue_revision, "rev_id")
			l_tag_sql := "[
				SELECT
			  		tt.text
				FROM
			  		tag_text tt
				LEFT JOIN
					issue_tag_association ita USING (tag_id)
				WHERE
					ita.issue_revision_id = :rev_id
				]"
			l_tag_selection := l_tag_select_query.execute_query (l_tag_sql)
			create Result.make (l_tag_selection.count)

			from l_tag_selection.start
			until l_tag_selection.after
			loop
				Result.force_last (l_tag_selection.read_string ("text"))

				-- inc
				l_tag_selection.forth
			end
		ensure
			created: Result /= Void
		end

	retrieve_previous_revision_tags (a_issue_revision: INTEGER): DS_ARRAYED_LIST[STRING]
			-- Retrieve the previous tags of the issue revision `a_issue_revision'
		local
			l_sql: STRING
			l_selection_result: SELECTION_RESULT
		do
			select_query.set_map_name (a_issue_revision, "issue_revision")
			l_selection_result := select_query.execute_query ("[
					SELECT
					  tt.text
					FROM
					  issue_revision ir
					JOIN issue_tag_association ita USING (issue_revision_id)
					JOIN tag_text tt USING (tag_id)
					WHERE 
					  ir.issue_revision_id = (
					    SELECT issue_revision_id AS prev_revision_id
					    FROM issue_revision ir
					    WHERE
					      ir.issue_id = 
						(
						SELECT
						  issue_id
						FROM
						  `issue_revision`
						WHERE
						  issue_revision_id = :issue_revision
						)
					      and ir.issue_revision_id < :issue_revision
					    ORDER BY ir.issue_revision_id DESC
					    LIMIT 1
					  )
				]")

			create Result.make (l_selection_result.count)
			from l_selection_result.start
			until l_selection_result.after
			loop
				Result.force_last (l_selection_result.read_string ("text"))

				-- inc
				l_selection_result.forth
			end

		end

	retrieve_tag_id (a_tag: STRING): INTEGER
			-- Retrieve id of a_tag
		require
			a_tag_ok: a_tag /= Void and then not a_tag.is_empty
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_tag, "a_tag")
			l_selection := select_query.execute_query ("SELECT tag_id FROM tag_text WHERE text=:a_tag")
			if l_selection.is_empty then
				Result := 0
			else
				l_selection.start
				Result := l_selection.read_integer ("tag_id")
			end
		end

	retrieve_issue_creator (a_issue_id: INTEGER): TUPLE[issue_revision_id: INTEGER; user: STRING; creation_time: INTEGER]
			-- Retrieve the creation time and the creating user of a given issue.
		local
			l_selection_result: SELECTION_RESULT
		do
				-- get first revision
			select_query.set_map_name (a_issue_id, "issue_id")
			l_selection_result := select_query.execute_query ("[
				SELECT
				  ir.creation_time,
				  ir.user_id,
				  u.name AS user,
				  min_rev.min_rev_id
				FROM
				  issue_revision ir,
				  user u,
				  (
				    SELECT
				      MIN(issue_revision_id) as min_rev_id
				    FROM
				      issue_revision
				    WHERE
				      issue_id = :issue_id
				  ) min_rev
				WHERE
				  ir.issue_revision_id = min_rev.min_rev_id
				  AND
				  ir.user_id = u.user_id
			]")

			if l_selection_result.count = 1 then
				l_selection_result.start

				create Result
				Result.user := l_selection_result.read_string ("user")
				Result.creation_time := l_selection_result.read_integer ("creation_time")
				Result.issue_revision_id := l_selection_result.read_integer ("min_rev_id")
			end
		end

feature -- Status

	is_new_status (a_issue_id: INTEGER): BOOLEAN
			-- check if this is the first revision ie. a new issue
		local
			l_query: DATABASE_SELECT_QUERY
			l_selection: SELECTION_RESULT
		do
			create l_query.make (Current)
			l_query.set_map_name (a_issue_id, "issue_id")
			l_selection := l_query.execute_query ("[
				SELECT COUNT( issue_revision_id ) AS count
				FROM issue_revision
				WHERE issue_id = :issue_id
				]")

			l_selection.start
			Result := l_selection.read_integer ("count") <= 1
		end

feature -- Insert queries

	insert_issue (a_issue: ISSUE)
			-- 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)
			-- 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.set_map_name(a_issue_revision.text_type, "text_type")
			l_store.modify("INSERT INTO `issue_revision` (`issue_id`, `creation_time`, `user_id`, `text_type`) VALUES (:issue_id, :creation_time, :user_id, :text_type)")

			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)
			-- 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

	insert_issue_attachment (a_issue_attachment: ISSUE_ATTACHMENT)
			-- Add an issue attachment
		require
			a_issue_attachment_not_void: a_issue_attachment /= Void
		local
			l_store: DB_STORE
		do
			create l_store.make
			l_store.set_repository (db_handler.db_repositories.item ("issue_attachment"))
			l_store.put (a_issue_attachment)
			handle_errors_and_warnings
		end

	insert_tag_text (a_tag: STRING)
			-- Add a_tag as a new tag text
		require
			a_tag_not_void: a_tag /= Void
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_tag, "a_tag")
			l_store.modify ("INSERT IGNORE INTO tag_text SET text=:a_tag")

			handle_errors_and_warnings
		end

feature -- Update queries

	update_issue (a_issue_id: INTEGER; a_title: STRING; a_is_private: BOOLEAN; a_deadline, a_work_amount: INTEGER)
			-- 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
		do
			create l_store.make

			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 (a_deadline, "deadline")
			l_store.set_map_name (a_work_amount, "work_amount")
			l_store.modify("UPDATE `issue` SET `title` = :title, private = :private, last_modified = UNIX_TIMESTAMP(), deadline = :deadline, work_amount = :work_amount WHERE `issue_id` = :issue_id")
			handle_errors_and_warnings
		end

	update_last_modified_timestamp (a_issue_id: INTEGER; a_date: DATE_TIME)
			-- 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

	update_issue_data (a_issue_id, a_deadline, a_work_amount: INTEGER)
			-- update the data of an issue
		require
			a_issue_id_ok: a_issue_id > 0
			a_deadline_ok: a_deadline > -1
			a_work_amount_ok: a_work_amount >= 0
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_issue_id, "issue_id")
			l_store.set_map_name (a_deadline, "deadline")
			l_store.set_map_name (a_work_amount, "work_amount")
			l_store.modify ("UPDATE `issue` SET deadline = :deadline, work_amount = :work_amount WHERE issue_id = :issue_id")
			handle_errors_and_warnings
		end


feature -- Delete queries

	remove_tags_for_revision (a_issue_revision_id: INTEGER)
			-- 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

	remove_issue_tag_association (a_issue_revision_id, a_tag_id: INTEGER)
			-- Remove a tag for revision `a_issue_revision_id'
		require
			a_issue_revision_id_ok: a_issue_revision_id > 0
			a_tag_id_ok: a_tag_id > 0
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_issue_revision_id, "issue_revision_id")
			l_store.set_map_name (a_tag_id, "tag_id")
			l_store.modify ("DELETE FROM issue_tag_association WHERE issue_revision_id=:issue_revision_id AND tag_id=:tag_id")
			handle_errors_and_warnings
		end

	remove_issue_attachment (a_issue_id: INTEGER; a_file_name: STRING)
			-- remove an issue attachment
		require
			a_issue_id_ok: a_issue_id > 0
			a_file_name_ok: a_file_name /= Void and then not a_file_name.is_empty
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_issue_id, "issue_id")
			l_change.set_map_name (a_file_name, "file_name")
			l_change.modify ("DELETE FROM issue_attachment WHERE issue_id = :issue_id AND file_name = :file_name")
			handle_errors_and_warnings
		end

	remove_issue_all_attachments (a_issue_id: INTEGER)
			-- Remove all attachments of an issue
		require
			a_issue_id_ok: a_issue_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_issue_id, "issue_id")
			l_change.modify ("DELETE FROM issue_attachment WHERE issue_id = :issue_id")
			handle_errors_and_warnings
		end

	delete_issue (a_issue_id: INTEGER)
			-- Delete an issue
			-- Note that associated attachments should be deleted by their appropriate calls (e.g. remove_attachment), as file-operations are involved.
			-- Workitem-data and other associated DB-relations (tags, etc.) get deleted through cascading.
		require
			a_issue_id_ok: a_issue_id > 0
		local
			l_change: DB_CHANGE
		do
			-- Delete issue
			create l_change.make
			l_change.set_map_name (a_issue_id, "issue_id")
			l_change.modify ("DELETE FROM issue WHERE issue_id = :issue_id")
			handle_errors_and_warnings
		end

feature {NONE} -- Utilities

	sanatize_reported_issues_column (a_sorting_column: STRING; a_default_sorting_column: STRING): STRING
			-- Checks if `a_sorting_column' is a valid column name in the DB's table `wi'
			-- If so, it is returned as `Result', otherwise the default sorting column is returned.
		require
			sorting_column_exists: a_sorting_column /= Void
			default_value_specified: a_default_sorting_column /= Void and then not a_default_sorting_column.is_empty
		local
			l_sorting_column: STRING
		do
			l_sorting_column := a_sorting_column.as_lower

			if reported_issues_columns.has (l_sorting_column) then
				Result := l_sorting_column
			else
				if reported_issues_columns.has (a_default_sorting_column.as_lower) then
					Result := a_default_sorting_column
				else
					-- The default sort column was invalid, take a hardcoded value
					Result := "last_updated"
				end
			end
		end

	reported_issues_columns: ARRAY [STRING]
			-- Constant list of columns that the "reported issues" list can be sorted by.
		once
			create Result.make_from_array (<<"project_issue_id", "title", "deadline", "work_amount", "project_name", "last_updated", "tags", "status", "assigned">>)
			Result.compare_objects
		end

end