note
	description: "Database access to project data."
	author: "Patrick Ruckstuhl <patrick@tario.org>"
	date: "$Date$"
	revision: "$Revision$"

class
	PROJECT_ACCESS

inherit
	DATABASE_ACCESS

create
	make

feature -- Status

	is_found: BOOLEAN
			-- Did the last retrieve query find a project?

feature -- Access

	last_project: PROJECT
			-- Last retrieved project by a retrieve query.

	last_project_list: LIST [PROJECT]
			-- Last retrieved project list.

	last_member_list: LIST [USER]
			-- Last retrieved member list.

	last_bookmarker_list: LIST [USER]
			-- Last retrieved bookmarker list.

	last_project_request: PROJECT_REQUEST
			-- Last retrieved project request by a retrieve query.

	last_project_information: PROJECT_INFORMATION
			-- Last retrieved project settings by retrieve query.
	last_repositories: DS_ARRAYED_LIST [STRING]
			-- The lastmost retrieved code repository names of a project.

feature -- Select queries

	retrieve_project_by_name (a_name: STRING)
			-- Retrieve a project by a_name.
		require
			a_name_ok: a_name /= Void and then not a_name.is_empty
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [PROJECT]
			l_obj: PROJECT
			l_projects: LIST [PROJECT]
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_name, "name")
			l_selection.set_query ("SELECT * FROM project WHERE name=:name")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			l_projects := l_list_filling.list
			if l_projects.count = 1 then
				is_found := True
				last_project := l_projects.first
			else
				is_found := False
			end
		ensure
			is_found_implies_project: is_found implies last_project /= Void
		end

	retrieve_project_by_id (a_id: INTEGER)
			-- Retrieve a project by a_id.
		require
			a_id_ok: a_id > 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [PROJECT]
			l_obj: PROJECT
			l_projects: LIST [PROJECT]
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_id, "project_id")
			l_selection.set_query ("SELECT * FROM project WHERE project_id=:project_id")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			l_projects := l_list_filling.list
			if l_projects.count = 1 then
				is_found := True
				last_project := l_projects.first
			else
				is_found := False
			end
		ensure
			is_found_implies_project: is_found implies last_project /= Void
		end

	retrieve_projects_of_user (a_user: STRING): ARRAYED_LIST[TUPLE[project_id: INTEGER; project_name: STRING; group: INTEGER]]
			-- Retrieve all projects of which a_user is developer or owner
		require
			a_user_valid: a_user /= Void and then not a_user.is_empty
		local
			l_qres: SELECTION_RESULT
			l_tuple: TUPLE[project_id: INTEGER; project_name: STRING; group: INTEGER]
		do
			select_query.set_map_name (a_user, "user_name")
			l_qres := select_query.execute_query ("[
					SELECT p.project_id, p.name, a.group_id
					FROM project p
					LEFT JOIN user_project_association a USING (project_id)
					LEFT JOIN user u USING (user_id)
					WHERE u.name =:user_name
				]")

				-- get result and put it into an arrayed list as named tuples
			from
				create Result.make (l_qres.count)
				l_qres.start
			until
				l_qres.after
			loop
				create l_tuple
				l_tuple.project_id := l_qres.read_integer ("project_id")
				l_tuple.project_name := l_qres.read_string ("name")
				l_tuple.group := l_qres.read_integer ("group_id")
				Result.force (l_tuple)
				l_qres.forth
			end
		ensure
			Result_set: Result /= Void
		end

	retrieve_bookmark_projects_of_user (a_user: INTEGER)
			-- Retrieve all projects a_user has bookmarked
		require
			a_user_valid: a_user > 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [PROJECT]
			l_obj: PROJECT
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_user, "user_id")
			l_selection.set_query ("[
					SELECT p.*
					FROM project p
					LEFT JOIN project_bookmark b USING (project_id)
					WHERE b.user_id =:user_id
				]")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			last_project_list := l_list_filling.list
		ensure
			last_project_list: last_project_list /= Void
		end

	retrieve_members (a_project: INTEGER; a_access_group: INTEGER)
			-- Retrieve members of a_project that are in a_access_group.
		require
			a_project_valid: a_project > 0
			a_access_group_valid: a_access_group > 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_obj: USER
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_project, "project_id")
			l_selection.set_map_name (a_access_group, "group_id")
			l_selection.set_query ("[
					SELECT u.* FROM user_project_association a
					JOIN user u USING(user_id)
					WHERE project_id=:project_id AND group_id=:group_id
					]")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			last_member_list := l_list_filling.list
		ensure
			last_member_list: last_member_list /= Void
		end

	retrieve_bookmarkers (a_project_id: INTEGER)
			-- Retrieve bookmarkers of a project with a_project_id.
		require
			a_project_id_valid: a_project_id > 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_user: USER
		do
			create l_user.make

			create l_selection.make
			l_selection.set_map_name (a_project_id, "project_id")
			l_selection.set_query ("[
					SELECT u.* FROM project_bookmark pb
					JOIN user u USING (user_id)
					WHERE pb.project_id=:project_id
					]")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_user)
			create l_list_filling.make (l_selection, l_user)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			last_bookmarker_list := l_list_filling.list
		ensure
			last_bookmarker_list: last_bookmarker_list /= Void
		end

	is_projects_closed_source (a_project: INTEGER): BOOLEAN
			-- Retrieve if a_project is closed source
		require
			a_project_valid: a_project > 0
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_project, "project_id")
			l_qres := select_query.execute_query ("[
					SELECT project_type FROM project
					WHERE project_id = :project_id
					]")

			if l_qres.count = 1 then
				l_qres.start
				Result := l_qres.read_integer ("project_type") = 1
			else
				Result := True
			end
		end

	retrieve_project_request_by_id (a_request_id: INTEGER)
			-- Retrieve project request with id a_request_id
		require
			a_request_id_valid: a_request_id > 0
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_request_id, "request_id")
			l_selection := select_query.execute_query ("[
					SELECT p.request_id, p.user_id, p.name, p.description, p.project_type, p.project_visibility, p.message FROM project_request p
					WHERE p.request_id=:request_id
					]")


			if l_selection.count = 1 then
				is_found := True
				l_selection.start
				create last_project_request.make
				last_project_request.set_request_id (l_selection.read_integer ("request_id"))
				last_project_request.set_user_id (l_selection.read_integer ("user_id"))
				last_project_request.set_name (l_selection.read_string ("name"))
				last_project_request.set_description (l_selection.read_string ("description"))
				last_project_request.set_project_type (l_selection.read_integer ("project_type"))
				last_project_request.set_project_visibility (l_selection.read_integer ("project_visibility"))
				last_project_request.set_message (l_selection.read_string ("message"))
			else
				is_found := False
			end

		ensure
			is_found_implies_project_request: is_found implies last_project_request /= Void
		end

	retrieve_project_request_by_name (a_request_project_name: STRING)
			-- Retrieve project request with name a_request_project_name
		require
			a_request_project_name_valid: a_request_project_name /= Void
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_request_project_name, "name")
			l_selection := select_query.execute_query ("[
					SELECT p.request_id, p.user_id, p.name, p.description, p.project_type, p.rcs_type, p.project_visibility, p.message FROM project_request p
					WHERE p.name=:name
					]")


			if l_selection.count = 1 then
				is_found := True
				l_selection.start
				create last_project_request.make
				last_project_request.set_request_id (l_selection.read_integer ("request_id"))
				last_project_request.set_user_id (l_selection.read_integer ("user_id"))
				last_project_request.set_name (l_selection.read_string ("name"))
				last_project_request.set_description (l_selection.read_string ("description"))
				last_project_request.set_project_type (l_selection.read_integer ("project_type"))
				last_project_request.set_rcs_type (l_selection.read_integer ("rcs_type"))
				last_project_request.set_project_visibility (l_selection.read_integer ("project_visibility"))
				last_project_request.set_message (l_selection.read_string ("message"))
			else
				is_found := False
			end

		ensure
			is_found_implies_project_request: is_found implies last_project_request /= Void
		end

	retrieve_statistics : DS_HASH_TABLE[STRING, STRING]
			-- Get general information about Origo projects
		local
			l_selection: SELECTION_RESULT
			l_project_count: INTEGER
			l_open_source_count: INTEGER
			l_project_count_hidden: INTEGER
			l_open_source_count_hidden: INTEGER
			l_user_count: INTEGER
			l_recent_projects: STRING
		do
			create Result.make(6)

				-- project count (open and closed source projects are included)
			l_selection := select_query.execute_query ("SELECT COUNT(*) AS count FROM project")
			handle_errors_and_warnings
			l_selection.start

			l_project_count := l_selection.read_integer ("count")
			Result.force(l_project_count.out, "project_count")

				-- open source count
			l_selection := select_query.execute_query ("SELECT COUNT(*) AS count FROM project WHERE project_type = 2")
			l_selection.start
			l_open_source_count := l_selection.read_integer ("count")
			Result.force(l_open_source_count.out, "open_source_count")

				-- hidden project count
			l_selection := select_query.execute_query ("SELECT COUNT(*) AS count FROM project p WHERE project_visibility = 1")
			l_selection.start
			l_project_count_hidden := l_selection.read_integer ("count")
			Result.force(l_project_count_hidden.out, "project_count_hidden")

				-- hidden open source count
			l_selection := select_query.execute_query ("[
					SELECT COUNT(*) AS count FROM project
					WHERE project_type = 2 AND project_visibility = 1
					]")
			l_selection.start
			l_open_source_count_hidden := l_selection.read_integer ("count")
			Result.force(l_open_source_count_hidden.out, "open_source_count_hidden")

				-- user count (excluding disabled users)
			l_selection := select_query.execute_query ("SELECT COUNT(*) AS count FROM user WHERE disabled='false'")
			l_selection.start
			l_user_count := l_selection.read_integer ("count")
			Result.force(l_user_count.out, "user_count")

				-- recently created projects (project names are separated by comma)
			l_selection := select_query.execute_query ("SELECT name FROM project WHERE project_visibility != 1 ORDER BY project_id DESC LIMIT 5")
			create l_recent_projects.make_empty

			from
				l_selection.start
			until
				l_selection.after
			loop
				if l_recent_projects.is_empty then
					l_recent_projects := l_selection.read_string ("name")
				else
					l_recent_projects := l_recent_projects + "," + l_selection.read_string ("name")
				end

				l_selection.forth
			end

			Result.force(l_recent_projects, "recent_projects")
		end

	retrieve_project_settings_by_id (an_id: INTEGER)
			-- Retrieve project settings by project ID and sets `last_project' if `is_found' is true
		require
			an_id_ok: an_id > 0
		local
			l_selection: DB_SELECTION
			l_project_settings: PROJECT
			l_project_settings_list_filling: DB_ACTION [PROJECT]
			l_project_settings_list: LIST [PROJECT]
		do
			create l_project_settings.make

			-- get project settings from PROJECT_SETTINGS table
			create l_selection.make
			l_selection.set_map_name (an_id, "project_id")
			l_selection.query ("SELECT * FROM project WHERE project_id=:project_id")
			handle_errors_and_warnings

			-- load data into `l_project_settings'
			l_selection.object_convert (l_project_settings)
			create l_project_settings_list_filling.make (l_selection, l_project_settings)
			l_selection.set_action (l_project_settings_list_filling)
			l_selection.load_result
			l_selection.terminate
			l_project_settings_list := l_project_settings_list_filling.list

			-- set `last_project' field
			if l_project_settings_list.count = 1 then
				-- project_settings found
				is_found := True
				last_project := l_project_settings_list.first
			else
				is_found := False
				last_project := Void
			end
		ensure
			is_found_implies_project: is_found implies last_project /= Void
			not_found_implies_void_last_project: not is_found implies last_project = Void
		end

	retrieve_project_information_by_id (an_id: INTEGER)
			-- Retrieve project information by project ID and sets `last_project_information' if `is_found' is true
		require
			an_id_ok: an_id > 0
		local
			l_selection: DB_SELECTION
			l_project_information: PROJECT_INFORMATION
			l_project_information_list_filling: DB_ACTION [PROJECT_INFORMATION]
			l_project_information_list: LIST [PROJECT_INFORMATION]
		do
			create l_project_information.make

			-- get project information from PROJECT_INFORMATION table
			create l_selection.make
			l_selection.set_map_name (an_id, "project_id")
			l_selection.query ("SELECT * FROM project_information WHERE project_id=:project_id")
			handle_errors_and_warnings

			-- load data into `l_project_information'
			l_selection.object_convert (l_project_information)
			create l_project_information_list_filling.make (l_selection, l_project_information)
			l_selection.set_action (l_project_information_list_filling)
			l_selection.load_result
			l_selection.terminate
			l_project_information_list := l_project_information_list_filling.list

			-- set `last_project_information' field
			if l_project_information_list.count = 1 then
				-- project_information found
				is_found := True
				last_project_information := l_project_information_list.first
			else
				is_found := False
				last_project_information := Void
			end
		ensure
			is_found_implies_project_information: is_found implies last_project_information /= Void
			not_found_implies_void_last_project_information: not is_found implies last_project_information = Void
		end

	retrieve_repositories (a_project_id: INTEGER)
			-- Retrieve all repositories of `a_project_id'
		require
			id_ok: a_project_id > 0
		local
			l_selection_result: SELECTION_RESULT
		do
			select_query.set_map_name (a_project_id, "project_id")
			l_selection_result := select_query.execute_query ("[
				SELECT * FROM git_repository
				JOIN project_git_association pga USING (git_id)
				WHERE pga.project_id  = :project_id
			]")

			create last_repositories.make (l_selection_result.count)
			from l_selection_result.start
			until l_selection_result.after
			loop
				last_repositories.force_last (l_selection_result.read_string ("name"))

				-- inc
				l_selection_result.forth
			end
		ensure
			created: last_repositories /= Void
		end

	retrieve_repository_id (a_repository_name: STRING a_project_id: INTEGER): INTEGER
			-- Retrieve the ID of `a_repository_name' associated with `a_project_id'
		require
			name_ok: a_repository_name /= Void and then not a_repository_name.is_empty
			id_ok: a_project_id > 0
		local
			l_selection_result: SELECTION_RESULT
		do
			is_found := False
			select_query.set_map_name (a_repository_name, "repository_name")
			select_query.set_map_name (a_project_id, "project_id")

			l_selection_result := select_query.execute_query ("[
				SELECT * FROM git_repository gr
				JOIN project_git_association pga USING (git_id)
				WHERE
						pga.project_id  = :project_id
					AND
						gr.name = :repository_name
			]")

			if l_selection_result.count > 0 then
				is_found := True
				l_selection_result.start
				Result := l_selection_result.read_integer ("git_id")
			end
		ensure
			found: is_found implies Result > 0
		end

feature -- Insert queries

	insert_project (a_project: PROJECT)
			-- Add `a_project' as a new project.
		require
			a_project_not_void: a_project /= Void
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_project.name, "name")
			l_store.set_map_name (a_project.logo, "logo")
			l_store.set_map_name (a_project.project_type, "project_type")
			l_store.set_map_name (a_project.rcs_type, "rcs_type")
			l_store.set_map_name (a_project.project_visibility, "project_visibility")
			l_store.set_map_name (a_project.creation_time, "creation_time")
			l_store.set_map_name (a_project.creator_id, "creator_id")
			l_store.modify ("[
				INSERT INTO `project`
					(`name`, `logo`, `project_type`, `rcs_type`, `project_visibility`, `creation_time`, `creator_id`)
				VALUES
					(:name, :logo, :project_type, :rcs_type, :project_visibility, :creation_time, :creator_id)
				]")
			handle_errors_and_warnings
		end

	insert_project_request (a_project_request: PROJECT_REQUEST)
			-- Add a_project_request as a new project request.
		require
			a_project_request_not_void: a_project_request /= Void
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_project_request.name, "name")
			l_store.set_map_name (a_project_request.user_id.out, "user_id")
			l_store.set_map_name (a_project_request.description, "description")
			l_store.set_map_name (a_project_request.project_type, "project_type")
			l_store.set_map_name (a_project_request.rcs_type, "rcs")
			l_store.set_map_name (a_project_request.project_visibility, "project_visibility")

			if a_project_request.message.is_empty then
				l_store.set_map_name (a_project_request.message, "message")
				l_store.modify ("[
					INSERT INTO project_request
					SET
						name=:name,
						user_id=:user_id,
						description=:description,
						project_type=:project_type,
						rcs_type=:rcs,
						project_visibility=:project_visibility,
						message=''
					]")
			else
				l_store.set_map_name (a_project_request.message, "message")
				l_store.modify ("[
					INSERT INTO project_request
					SET
						name=:name,
						user_id=:user_id,
						description=:description,
						project_type=:project_type,
						rcs_type=:rcs,
						project_visibility=:project_visibility,
						message=:message
					]")
			end

			handle_errors_and_warnings
		end

	insert_project_information (a_project_id: INTEGER; a_description: STRING)
			-- Add a new project_information for project with `a_project_id', only used at project creation
		require
			a_project_id_valid: a_project_id > 0
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_project_id, "project_id")
			l_store.set_map_name (a_description, "description")
			l_store.modify ("INSERT INTO `project_information` (`project_id`, `description`) VALUES (:project_id, :description)")
			handle_errors_and_warnings
		end

	insert_repository (a_project_id: INTEGER; a_repository_name: STRING)
			-- Add a reference between a source code repository and a project
		require
			id_ok: a_project_id > 0
			name_exists: a_repository_name /= Void and then not a_repository_name.is_empty
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_project_id, "project_id")
			l_store.set_map_name (a_repository_name, "repo_name")

			l_store.modify ("[
				INSERT INTO `git_repository` (`name`) VALUES (:repo_name);
			]")
			handle_errors_and_warnings

			l_store.modify ("[
				INSERT INTO `project_git_association` (`project_id`, `git_id`) VALUES (:project_id, LAST_INSERT_ID())
			]")
			handle_errors_and_warnings
		end

feature -- Update queries

	update_project_description (a_project_id: INTEGER; a_description: STRING)
			-- Update the description of a_project_id to the new value a_description
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")
			l_change.set_map_name (a_description, "description")

			l_change.modify ("UPDATE project_information SET description=:description WHERE project_id=:project_id")
			handle_errors_and_warnings
		end

	update_project_logo (a_project_id: INTEGER; a_logo: STRING)
			-- Update the logo of a_project_id to the new value a_logo
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")
			l_change.set_map_name (a_logo, "logo")

			l_change.modify ("UPDATE project SET logo=:logo WHERE project_id=:project_id")
			handle_errors_and_warnings
		end

	update_project_settings (a_project_id: INTEGER; a_setting_type: STRING; a_setting_value: STRING)
			-- Update the project settings.
			-- does NOT update name, project_type and hidden
		require
			a_project_id_ok: a_project_id > 0
			setting_type_ok: a_setting_type /= Void and then not a_setting_type.is_empty
			setting_value_ok: a_setting_value /= Void
		local
			l_change: DB_CHANGE
		do
				-- check for information_type and call corresponding query
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")

			if a_setting_type.is_equal ("logo") then
				l_change.set_map_name (a_setting_value, "logo")
				l_change.modify ("UPDATE project SET logo=:logo WHERE project_id=:project_id")

			elseif a_setting_type.is_equal ("svn_path_doc") then
				l_change.set_map_name (a_setting_value, "svn_path_doc")
				l_change.modify ("UPDATE project SET svn_path_doc=:svn_path_doc WHERE project_id=:project_id")

			elseif a_setting_type.is_equal ("project_pretty_name") then
				l_change.set_map_name (a_setting_value, "project_pretty_name")
				l_change.modify ("UPDATE project SET project_pretty_name=:project_pretty_name WHERE project_id=:project_id")
			end

			handle_errors_and_warnings
		end

	update_project_information (a_project_id: INTEGER; a_information_type: STRING; a_information_value: STRING)
			-- Update the project information.
		require
			a_project_id_ok: a_project_id > 0
			information_type_ok: a_information_type /= Void and then not a_information_type.is_empty
			information_value_ok: a_information_value /= Void
		local
			l_change: DB_CHANGE
		do
				-- check for information_type and call corresponding query
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")

			if a_information_type.is_equal ("short_description") then
				l_change.set_map_name (a_information_value, "short_description")
				l_change.modify ("UPDATE project_information SET short_description=:short_description WHERE project_id=:project_id")

			elseif a_information_type.is_equal ("description") then
				l_change.set_map_name (a_information_value, "description")
				l_change.modify ("UPDATE project_information SET description=:description WHERE project_id=:project_id")

			elseif a_information_type.is_equal ("programming_language") then
				l_change.set_map_name (a_information_value, "programming_language")
				l_change.modify ("UPDATE project_information SET programming_language=:programming_language WHERE project_id=:project_id")

			elseif a_information_type.is_equal ("operating_system") then
				l_change.set_map_name (a_information_value, "operating_system")
				l_change.modify ("UPDATE project_information SET operating_system=:operating_system WHERE project_id=:project_id")

			elseif a_information_type.is_equal ("license") then
				l_change.set_map_name (a_information_value, "license")
				l_change.modify ("UPDATE project_information SET license=:license WHERE project_id=:project_id")

			elseif a_information_type.is_equal ("category") then
				l_change.set_map_name (a_information_value, "category")
				l_change.modify ("UPDATE project_information SET category=:category WHERE project_id=:project_id")
			end

			handle_errors_and_warnings
		end

	update_project_type (a_project_id: INTEGER; a_type, a_rcs, a_visibility: INTEGER)
			-- Update the project type and visibility
		require
			pid_ok: a_project_id > 0
			type_ok: a_type > 0
			rcs_ok: a_rcs > 0
			visibility_ok: a_visibility > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")
			l_change.set_map_name (a_type, "type")
			l_change.set_map_name (a_rcs, "rcs")
			l_change.set_map_name (a_visibility, "project_visibility")

			l_change.modify ("UPDATE project SET project_type=:type, rcs_type=:rcs, project_visibility=:project_visibility WHERE project_id=:project_id")
			handle_errors_and_warnings
		end

feature -- Delete queries

	delete_project (a_project_id: INTEGER)
			-- Delete project with a_project_id
		require
			a_project_id_ok: a_project_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")

			l_change.modify ("DELETE FROM project WHERE project_id=:project_id")
			handle_errors_and_warnings
		end

	delete_project_request (a_project_name: STRING)
			-- Delete a project request
		require
			a_project_name_ok: not a_project_name.is_empty
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_project_name, "project_name")

			l_change.modify ("DELETE FROM project_request WHERE name=:project_name")
			handle_errors_and_warnings
		end

	delete_repository (a_repository_id: INTEGER)
			-- Remove the repository `a_repository_id' from the database (and thus remove
			--	the association with its project).
		require
			id_ok: a_repository_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_repository_id, "repository_id")

			l_change.modify ("DELETE FROM `git_repository` WHERE git_id = :repository_id")
			handle_errors_and_warnings
		end

feature -- Utility

	is_valid_project_type_id (a_id: INTEGER): BOOLEAN
			-- Does `a_id' denote a valid project_type?
		do
			Result := project_type_list.has (a_id)
		end

	project_type_id_to_string (a_id: INTEGER): STRING
			-- Retrieves the name of the project-type referenced by `a_id'
		require
			is_valid_project_type_id (a_id)
		do
			Result := project_type_list.at (a_id)
		ensure
			found: Result /= Void
		end

	project_type_string_to_id (a_type_name: STRING): INTEGER
			-- Retrieves the type-id of the project-type referenced by `a_type_name'
		require
			name_specified: a_type_name /= Void
		local
			 l_cursor: DS_HASH_TABLE_CURSOR [STRING, INTEGER]
		do
			Result := 0
			if project_type_list.has_item (a_type_name) then
				-- We use a custom key so the internal cursor of project_type_list isn't moved.
				l_cursor := project_type_list.new_cursor
				l_cursor.search_forth (a_type_name)
				Result := l_cursor.key
			end
		ensure
			id_ok: Result >= 0
		end

	project_type_list: DS_HASH_TABLE [STRING, INTEGER]
			-- Returns a mapping of (valid) project_type ID's to the corresponding names.
		local
			l_selection: SELECTION_RESULT
		once
			l_selection := select_query.execute_query ("SELECT * FROM project_type")

			create Result.make (l_selection.count)
			Result.set_equality_tester (create {KL_EQUALITY_TESTER [STRING]})

			from
				l_selection.start
			until
				l_selection.after
			loop
				Result.force (l_selection.read_string ("name"), l_selection.read_integer ("project_type_id"))
				-- inc
				l_selection.forth
			end
		ensure
			created: Result /= Void
		end

	is_valid_rcs_type_id (a_id: INTEGER): BOOLEAN
			-- Does `a_id' denote a valid rcs_type?
		do
			Result := rcs_type_list.has (a_id)
		end

	rcs_type_list: DS_HASH_TABLE [STRING, INTEGER]
			-- Returns a mapping of (valid) rcs_type ID's to the corresponding names.
		local
			l_selection: SELECTION_RESULT
		once
			l_selection := select_query.execute_query ("SELECT * FROM rcs_type")

			create Result.make (l_selection.count)
			Result.set_equality_tester (create {KL_EQUALITY_TESTER [STRING]})

			from
				l_selection.start
			until
				l_selection.after
			loop
				Result.force (l_selection.read_string ("name"), l_selection.read_integer ("rcs_type_id"))
				-- inc
				l_selection.forth
			end
		ensure
			created: Result /= Void
		end

	is_valid_visibility_type_id (a_id: INTEGER): BOOLEAN
			-- Does `a_id' denote a valid visibility_type?
		do
			Result := visibility_type_list.has (a_id)
		end

	visibility_type_list: DS_HASH_TABLE [STRING, INTEGER]
			-- Returns a mapping of (valid) visibility_type ID's to the corresponding names.
		local
			l_selection: SELECTION_RESULT
		once
			l_selection := select_query.execute_query ("SELECT * FROM project_visibility")

			create Result.make (l_selection.count)
			Result.set_equality_tester (create {KL_EQUALITY_TESTER [STRING]})

			from
				l_selection.start
			until
				l_selection.after
			loop
				Result.force (l_selection.read_string ("name"), l_selection.read_integer ("project_visibility_id"))
				-- inc
				l_selection.forth
			end
		ensure
			created: Result /= Void
		end

end