note
	description: "Database access to comment workitem data."
	author: "Marco Zietzling <marco.zietzling@gmail.com>"
	date: "$Date$"
	revision: "$Revision$"

class
	COMMENT_ACCESS

inherit
	DATABASE_ACCESS

create
	make

feature -- Select queries

	comment_type_id_from_string (a_comment_type: STRING): INTEGER
		require
			type_specified: a_comment_type /= Void and then not a_comment_type.is_empty
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_comment_type, "type_name")
			l_selection := select_query.execute_query ("SELECT `type_id` FROM `comment_type` WHERE type_name = :type_name LIMIT 1")

			if l_selection.is_empty then
				Result := 0
			else
				l_selection.start
				Result := l_selection.read_integer ("type_id")
			end
		ensure
			proper_result: Result >= 0
		end

feature -- Status

	is_new_status (a_comment_reference: STRING): BOOLEAN
			-- Utility feature to help us decide wheter a given comment "is new" or not			
		local
			l_query: DATABASE_SELECT_QUERY
			l_selection: SELECTION_RESULT
		do
			-- Note: we cannot reasonably decide if a comment is new or not, as they are
			-- not revisioned in the backend.Instead, we check if the URL of the comment
			-- is already in the database. This should work as comment's are always
			-- referenced using HTML anchors.

			create l_query.make (Current)
			l_query.set_map_name (a_comment_reference, "reference")
			l_selection := l_query.execute_query ("[
					SELECT COUNT(*) as cnt FROM `wi`
					JOIN workitem_type wt USING (type_id)
					WHERE
					  wt.name = 'comment'
					  AND
					  ref = :reference
				]")

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

end