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

class
	USER_ACCESS

inherit

	POLICY_CACHE_ACCESS

create
	make

feature -- Status

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

feature -- Access

	last_user: USER
			-- Last retrieved user by a retrieve query.

	last_user_information: USER_INFORMATION
			-- Last retrieved user information by retrieve query.

	last_user_settings: TUPLE[strings: DS_HASH_TABLE [STRING, STRING]; integers: DS_HASH_TABLE [INTEGER, STRING]; doubles: DS_HASH_TABLE [DOUBLE, STRING]]
			-- The lastmost retrieved user settings, split up according to their type

	last_friendship_request: USER_FRIENDSHIP_REQUEST
			-- Last retrieved user friendship request by a retrieve query.

	last_friendship: USER_FRIENDSHIP
			-- Last retrieved user friendship by a retrieve query.

	last_friends: LIST [USER]
			-- Last retrieved list of friends.

	last_session: SESSION
			-- Last retrieved session by a retrieve query.

	last_access_mapping: DS_HASH_TABLE [TUPLE [r: DS_ARRAYED_LIST [STRING]; rw:DS_ARRAYED_LIST [STRING]], STRING]
			-- The lastmost retrieved access mapping

feature -- Select queries

	has_bookmarked_project (a_project_id: INTEGER; a_user_id: INTEGER): BOOLEAN
			-- Has user `a_user_id' bookmarked `a_project_id'?
		require
			a_project_id_ok: a_project_id > 0
			a_user_id_ok: a_user_id >= 0
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_project_id, "project_id")
			select_query.set_map_name (a_user_id, "user_id")
			l_qres := select_query.execute_query ("[
					SELECT project_id FROM project_bookmark
					WHERE project_id = :project_id
					AND user_id=:user_id
					]")

			if l_qres.count >= 1 then
				Result := True
			else
				Result := False
			end
		end

	retrieve_user_by_name (a_name: STRING; a_include_disabled: BOOLEAN)
			-- Retrieve a user by `a_name' (if `a_include_disabled' is set to `True' this also retrieves disabled users)
		require
			a_name_ok: a_name /= Void and then not a_name.is_empty
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_user: USER
			l_users: LIST [USER]
			l_query: STRING
		do
			create l_user.make

			create l_selection.make

			l_selection.set_map_name (a_name, "name")
			create l_query.make_from_string ("SELECT * FROM user WHERE name=:name")
			if not a_include_disabled then
				l_query.append (" AND disabled='false' LIMIT 1")
			end
			l_selection.query (l_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

			l_users := l_list_filling.list
			if l_users.count = 1 then
				is_found := True
				last_user := l_users.first
			else
				is_found := False
			end
		ensure
			is_found_implies_user: is_found implies last_user /= Void
		end

	retrieve_user_by_email (a_email: STRING; a_include_disabled: BOOLEAN)
			-- Retrieve a user by `a_email' (if `a_include_disabled' is set to `True' this also retrieves disabled users)
		require
			a_email_ok: a_email /= Void and then not a_email.is_empty
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_user: USER
			l_users: LIST [USER]
			l_query: STRING
		do
			create l_user.make

			create l_selection.make

			l_selection.set_map_name (a_email, "email")
			create l_query.make_from_string ("SELECT * FROM user WHERE email=:email")
			if not a_include_disabled then
				l_query.append (" AND disabled='false' LIMIT 1")
			end
			l_selection.query (l_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

			l_users := l_list_filling.list
			if l_users.count = 1 then
				is_found := True
				last_user := l_users.first
			else
				is_found := False
			end
		ensure
			is_found_implies_user: is_found implies last_user /= Void
		end

	retrieve_user_by_id (a_id: INTEGER)
			-- Retrieve a user by a_id.
		require
			a_id_ok: a_id >= 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_user: USER
			l_users: LIST [USER]
		do
			create l_user.make

			create l_selection.make

			l_selection.set_map_name (a_id, "user_id")
			l_selection.query ("SELECT * FROM user WHERE user_id=:user_id AND disabled='false'")
			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

			l_users := l_list_filling.list
			if l_users.count = 1 then
				is_found := True
				last_user := l_users.first
			else
				is_found := False
			end
		ensure
			is_found_implies_user: is_found implies last_user /= Void
		end

	retrieve_user_by_session (a_session: STRING)
			-- Retrieve a user by a_session.
		require
			a_session_ok: a_session /= Void and then Not a_session.is_empty
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_user: USER
			l_users: LIST [USER]
		do
			create l_user.make

			create l_selection.make

			l_selection.set_map_name (a_session, "session_id")
			l_selection.query ("SELECT u.* FROM session s JOIN user u USING(user_id) WHERE session_id=:session_id AND disabled='false'")
			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

			l_users := l_list_filling.list
			if l_users.count = 1 then
				is_found := True
				last_user := l_users.first
			else
				-- return anonymous user
				last_user := l_user
				is_found := True
			end
		ensure
			is_found_implies_user: is_found implies last_user /= Void
		end

	retrieve_user_by_key (a_key: STRING)
			-- Retrieve a user by a_key.
		require
			a_key_ok: a_key /= Void and then not a_key.is_empty
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_user: USER
			l_users: LIST [USER]
		do
			create l_user.make

			create l_selection.make

			l_selection.set_map_name (a_key, "key")
			l_selection.query ("SELECT * FROM user WHERE user_key=:key AND disabled='false'")
			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

			l_users := l_list_filling.list
			if l_users.count = 1 then
				is_found := True
				last_user := l_users.first
			else
				is_found := False
			end
		ensure
			is_found_implies_user: is_found implies last_user /= Void
		end

	retrieve_user_information_by_id (an_id: INTEGER)
			-- Retrieve user information by user ID.
		require
			an_id_ok: an_id >= 0
		local
			l_selection: DB_SELECTION
			l_selection_result: SELECTION_RESULT
			l_setting, l_type: STRING
			l_user_information_list_filling: DB_ACTION [USER_INFORMATION]
			l_user_information: USER_INFORMATION
			l_user_informations: LIST [USER_INFORMATION]
			l_string: STRING
			l_string_settings: DS_HASH_TABLE [STRING, STRING]
			l_double: DOUBLE
			l_double_settings: DS_HASH_TABLE [DOUBLE, STRING]
			l_integer: INTEGER
			l_integer_settings: DS_HASH_TABLE [INTEGER, STRING]
		do
			-- It's ugly, but let's do the same query twice...
			-- Once for filling the type-separated tuple `last_user_settings' and once for filling
			-- the `last_user_information' object
			select_query.set_map_name (an_id, "user_id")
			l_selection_result := select_query.execute_query ("SELECT * FROM user_information WHERE user_id=:user_id")

			from
				user_valid_setting_names.start
				l_selection_result.start	-- we only need the very first result, there shouldn't be more anyways
				create l_string_settings.make (20)
				create l_integer_settings.make (8)
				create l_double_settings.make_default
			until
				user_valid_setting_names.after
			loop
				l_setting := user_valid_setting_names.key_for_iteration
				l_type := user_valid_setting_names.item_for_iteration

				if l_type.is_equal (setting_string_type) then
					-- read string field
					l_string := l_selection_result.read_string_ref (l_setting)
					if l_string = Void then
						l_string := ""
					end
					l_string_settings.force_last (l_string, l_setting)
				elseif l_type.is_equal (setting_double_type) then
					-- read string field
					l_double := l_selection_result.read_double (l_setting)
					l_double_settings.force_last (l_double, l_setting)
				elseif l_type.is_equal (setting_integer_type) then
					-- read string field
					l_integer := l_selection_result.read_integer (l_setting)
					l_integer_settings.force_last (l_integer, l_setting)
				end

				-- increment
				user_valid_setting_names.forth
			end
			-- create & fill result tuple
			create last_user_settings
			last_user_settings.strings := l_string_settings
			last_user_settings.doubles := l_double_settings
			last_user_settings.integers := l_integer_settings

			-- ------------------------------------------------------------------------------
			-- Do the second query for the "last_user_information" object
			create l_user_information.make

				-- get user information from USER_INFORMATION table
			create l_selection.make
			l_selection.set_map_name (an_id, "user_id")
			l_selection.query ("SELECT * FROM user_information WHERE user_id=:user_id")
			handle_errors_and_warnings

			l_selection.object_convert (l_user_information)
			create l_user_information_list_filling.make (l_selection, l_user_information)
			l_selection.set_action (l_user_information_list_filling)
			l_selection.load_result
			l_selection.terminate
			l_user_informations := l_user_information_list_filling.list

			if l_user_informations.count = 0 then
				-- no user information found, generate a default one
				create last_user_information.make
			else
				last_user_information := l_user_informations.first
			end
		ensure
			is_found_implies_user_information: is_found implies last_user_information /= Void
			last_settings_created: last_user_settings /= Void
			last_setting_types_exist: last_user_settings.integers /= Void and last_user_settings.doubles /= Void and last_user_settings.strings /= Void
		end

	retrieve_friendship_request (a_requester_id, a_requestee_id: INTEGER)
			-- Retrieve a friendship request.
		require
			a_requester_id_ok: a_requester_id > 0
			a_requestee_id_ok: a_requestee_id > 0
		local
			l_selection: DB_SELECTION
			l_user_friendship_request: USER_FRIENDSHIP_REQUEST
			l_user_friendship_requests: LIST [USER_FRIENDSHIP_REQUEST]
			l_user_friendship_request_list_filling: DB_ACTION [USER_FRIENDSHIP_REQUEST]
		do
			create l_user_friendship_request.make
			create l_selection.make
			l_selection.set_map_name (a_requester_id, "requester_id")
			l_selection.set_map_name (a_requestee_id, "requestee_id")
			l_selection.query ("SELECT * FROM user_friendship_request WHERE requester_id=:requester_id AND requestee_id=:requestee_id")
			handle_errors_and_warnings

			l_selection.object_convert (l_user_friendship_request)
			create l_user_friendship_request_list_filling.make (l_selection, l_user_friendship_request)
			l_selection.set_action (l_user_friendship_request_list_filling)
			l_selection.load_result
			l_selection.terminate
			l_user_friendship_requests := l_user_friendship_request_list_filling.list

			if l_user_friendship_requests.count = 1 then
				is_found := True
				last_friendship_request := l_user_friendship_requests.first
			else
				is_found := False
			end
		end

	retrieve_friendship (a_user_id, a_friend_id: INTEGER)
			-- Retrieve a friendship.
		require
			a_user_id_ok: a_user_id > 0
			a_friend_id_ok: a_friend_id > 0
		local
			l_selection: DB_SELECTION
			l_user_friendship: USER_FRIENDSHIP
			l_user_friendships: LIST [USER_FRIENDSHIP]
			l_user_friendship_list_filling: DB_ACTION [USER_FRIENDSHIP]
		do
			create l_user_friendship.make
			create l_selection.make
			l_selection.set_map_name (a_user_id, "user_id")
			l_selection.set_map_name (a_friend_id, "friend_id")
			l_selection.query ("SELECT * FROM user_friendship WHERE user_id=:user_id AND friend_id=:friend_id")
			handle_errors_and_warnings

			l_selection.object_convert (l_user_friendship)
			create l_user_friendship_list_filling.make (l_selection, l_user_friendship)
			l_selection.set_action (l_user_friendship_list_filling)
			l_selection.load_result
			l_selection.terminate
			l_user_friendships := l_user_friendship_list_filling.list

			if l_user_friendships.count = 1 then
				is_found := True
				last_friendship := l_user_friendships.first
			else
				is_found := False
			end
		end

	retrieve_friends_of_user (a_user_id: INTEGER)
			-- Retrieve a list of friends for user with `a_user_id'.
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_selection: SELECTION_RESULT
			l_friends: ARRAYED_LIST [USER]
			user_id: INTEGER
		do
			select_query.set_map_name (a_user_id, "user_id")
			l_selection := select_query.execute_query ("SELECT friend_id FROM user_friendship WHERE user_id=:user_id")

			from
				create l_friends.make (l_selection.count)
				l_selection.start
			until
				l_selection.after
			loop
				user_id := l_selection.read_integer ("friend_id")
				retrieve_user_by_id (user_id)
				if is_found then
					l_friends.force (last_user)
				end
				l_selection.forth
			end

			last_friends := l_friends
		end

	retrieve_application_by_key (a_key: STRING): TUPLE [name: STRING; key: STRING; id: INTEGER]
			-- Retrieve application by its key.
		require
			a_key_ok: a_key /= Void and then not a_key.is_empty
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_key, "key")
			l_qres := select_query.execute_query ("SELECT * FROM application WHERE app_key=:key")

			if l_qres.count = 1 then
				l_qres.start
				create Result
				Result.name := l_qres.read_string ("name")
				Result.id := l_qres.read_integer ("application_id")
				Result.key := a_key
			end
		end

	retrieve_user_project_association_group (a_project: INTEGER; a_user: INTEGER): INTEGER
			-- Retrieve group_id for a_user on a_project.
		require
			a_project_valid: a_project > 0
			a_user_set: a_user >= 0
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_project, "project_id")
			select_query.set_map_name (a_user, "user_id")
			l_qres := select_query.execute_query ("[
					SELECT group_id FROM user_project_association
					WHERE project_id = :project_id
					AND user_id=:user_id
					]")

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

	retrieve_user_community_association_group (a_community_id: INTEGER; a_user_id: INTEGER): INTEGER
			-- Retrieve group_id for a_user on a_community
		require
			a_community_valid: a_community_id > 0
			a_user_set: a_user_id >= 0
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_community_id, "community_id")
			select_query.set_map_name (a_user_id, "user_id")
			l_qres := select_query.execute_query ("[
					SELECT group_id FROM user_community_association
					WHERE community_id = :community_id
					AND user_id=:user_id
					]")

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

	retrieve_session_by_id (a_session_id: STRING)
			-- Retrieve a session by the id.
		require
			a_session_id_ok: a_session_id /= Void and then not a_session_id.is_empty
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [SESSION]
			l_obj: SESSION
			l_sessions: LIST [SESSION]
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_session_id, "session_id")
			l_selection.set_query ("SELECT * FROM session WHERE session_id=:session_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_sessions := l_list_filling.list
			if l_sessions.count = 1 then
				is_found := True
				last_session := l_sessions.first
			else
				is_found := False
			end
		ensure
			is_found_implies_session: is_found implies last_session /= Void
		end

	retrieve_my_name (a_session_id: STRING): STRING
			-- Retrieve user name which corresponds to a_session_id
		require
			a_session_id_ok: a_session_id /= Void and then not a_session_id.is_empty
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_session_id, "session_id")
			l_qres := select_query.execute_query ("[
					SELECT name
					FROM session
					LEFT JOIN user USING (user_id)
					WHERE session_id =:session_id
				]")

			if l_qres.is_empty then
				Result := ""
			else
				l_qres.start
				Result := l_qres.read_string ("name")
			end
		ensure
			Result_not_void: Result /= Void
		end

	retrieve_my_password (a_session_id: STRING): STRING
			-- Retrieve password which corresponds to a_session_id
		require
			a_session_id_ok: a_session_id /= Void and then not a_session_id.is_empty
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_session_id, "session_id")
			l_qres := select_query.execute_query ("[
					SELECT pass
					FROM session
					LEFT JOIN user USING (user_id)
					WHERE session_id =:session_id
				]")

			if l_qres.is_empty then
				Result := ""
			else
				l_qres.start
				Result := l_qres.read_string ("pass")
			end
		ensure
			Result_not_void: Result /= Void
		end

	retrieve_my_email (a_session_id: STRING): STRING
			-- Retrieve email address which corresponds to a_session_id
		require
			a_session_id_ok: a_session_id /= Void and then not a_session_id.is_empty
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_session_id, "session_id")
			l_selection := select_query.execute_query ("[
					SELECT email
					FROM session
					LEFT JOIN user USING (user_id)
					WHERE session_id =:session_id
				]")

			if l_selection.is_empty then
				Result := ""
			else
				l_selection.start
				Result := l_selection.read_string ("email")
			end
		ensure
			Result_not_void: Result /= Void
		end

	retrieve_profile_visible (a_user_id: INTEGER): INTEGER
			-- returns the users profile visibility settings
			-- value = 0 means the profile is not visible.
			-- more detailed visibility settings can be encoded into the integer,
			-- starting from the last bit:
			--   1. profile visible to logged in users only
			--   2. FOAF exported  => not used anymore, bherlig - 2009-07-14
			--   3. SHA1(email) visible in FOAF
			-- the OpenID visibility settings are returned from the OpenID table
		require
			a_user_id_valid: a_user_id >= 0
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_user_id, "user_id")
			l_qres := select_query.execute_query ("[
					SELECT profile_visible FROM user_information
					WHERE user_id=:user_id
					]")

			if l_qres.count = 1 then
				l_qres.start
				Result := l_qres.read_integer ("profile_visible")
			else
				Result := 0
			end
		ensure
			Result_valid: Result >= 0
		end

	retrieve_key (a_session_id: STRING): STRING
			-- Retrieve user key which corresponds to a_session_id
		require
			a_session_id_ok: a_session_id /= Void and then not a_session_id.is_empty
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_session_id, "session_id")
			l_qres := select_query.execute_query ("[
					SELECT user_key
					FROM session
					LEFT JOIN user USING (user_id)
					WHERE session_id =:session_id
				]")

			if l_qres.is_empty then
				Result := ""
			else
				l_qres.start
				Result := l_qres.read_string ("user_key")
			end
		ensure
			Result_not_void: Result /= Void
		end

	retrieve_mail_addresses: LIST [STRING]
			-- Retrieve all mail addresses of enabled users.
		local
			l_res: SELECTION_RESULT
		do
			l_res := select_query.execute_query ("SELECT email FROM user WHERE disabled = 'false'")
			from
				create {ARRAYED_LIST [STRING]}Result.make (l_res.count)
				l_res.start
			until
				l_res.after
			loop
				Result.force (l_res.read_string ("email"))
				l_res.forth
			end
		end

	retrieve_auth_users: STRING
			-- Retrieve encrypted password and usernames in a way usable in a auth user file, set's last_config.
		local
			l_res: SELECTION_RESULT
		do
				-- FIXME Investigate potential memory corruption during resize operation(s) - e.g. when new user is created
				-- Maybe we should use some guessing algorithm to determine a reasonable initial string size instead of a static value
				-- (goal: reduce number of resize ops)
			create Result.make (10240)

			l_res := select_query.execute_query ("SELECT name, ENCRYPT(pass) AS pass FROM user WHERE disabled='false'")
			from
				l_res.start
			until
				l_res.after
			loop
				Result.append (l_res.read_string ("name"))
				Result.append_character (':')
				Result.append (l_res.read_string ("pass"))
				Result.append_character ('%N')
				l_res.forth
			end
		ensure
			result_set: Result /= Void
		end

	retrieve_workitem_subscription (a_user: INTEGER; a_project: INTEGER): ARRAYED_LIST[TUPLE [workitem_type: INTEGER; subscription_type: INTEGER]]
			-- Retrieve workitem type subscriptions for a_user and a_project
		require
			a_user_valid: a_user >= 0
			a_project_valid: a_project > 0
		local
			l_selection: SELECTION_RESULT
			l_tuple: TUPLE [workitem_type: INTEGER; subscription_type: INTEGER]
		do
			select_query.set_map_name (a_user, "user")
			select_query.set_map_name (a_project, "project")
			l_selection := select_query.execute_query ("[
					SELECT s.type_id, s.subscription_type FROM user_workitem_subscription s
					WHERE s.enabled = 1 AND s.user_id=:user AND s.project_id=:project
					]")

				-- get result and put it into an arrayed list as named tuples
			from
				create Result.make (l_selection.count)
				l_selection.start
			until
				l_selection.after
			loop
				create l_tuple
				l_tuple.workitem_type := l_selection.read_integer ("type_id")
				l_tuple.subscription_type := l_selection.read_integer ("subscription_type")

				Result.force (l_tuple)
				l_selection.forth
			end
		ensure
			Result_set: Result /= Void
		end

	retrieve_ftp_users (a_ftp_dir: STRING): STRING
			-- Retrieve ftp user configuration.
		local
			l_res: SELECTION_RESULT
			l_name: STRING
		do
			create Result.make (1024)

			l_res := select_query.execute_query ("SELECT DISTINCT name, ENCRYPT(pass) AS pass FROM user WHERE disabled='false'")
			from
				l_res.start
			until
				l_res.after
			loop
				l_name := l_res.read_string ("name")
				Result.append (l_name)
				Result.append_character (':')
				Result.append (l_res.read_string ("pass"))
				Result.append (":33:33::" + a_ftp_dir + "/")
				Result.append (l_name)
				Result.append ("/./::5120::::::::::%N")
				l_res.forth
			end
		ensure
			result_set: Result /= Void and then not Result.is_empty
		end

	retrieve_svn_access: STRING
			-- Retrieve access configuration for subversion.
		local
			l_public_project_types: DS_SET[INTEGER]
		do
			create {DS_HASH_SET[INTEGER]} l_public_project_types.make (1)
			l_public_project_types.force (2)
			Result := retrieve_access ("svn_write", "rw", l_public_project_types)
		ensure
			result_set: Result /= Void
		end

	retrieve_source_access
			-- Retrieves a table of projects with respective members that have access to it
		local
			l_public_project_types: DS_SET[INTEGER]
		do
			create {DS_HASH_SET[INTEGER]} l_public_project_types.make (1)
			l_public_project_types.force (2)
			retrieve_access_mapping ("svn_write", "rw", l_public_project_types)
		end

	retrieve_download_access: STRING
			-- Retrieve access configuration for download.
		local
			l_public_project_types: DS_SET[INTEGER]
		do
			create {DS_HASH_SET[INTEGER]} l_public_project_types.make (2)
			l_public_project_types.force (1)
			l_public_project_types.force (2)
			Result := retrieve_access ("release_access", "r", l_public_project_types)
		ensure
			result_set: Result /= Void
		end

	retrieve_access (instruction: STRING; permission: STRING; public_project_types: DS_SET[INTEGER]): STRING
			-- Retrieve access configuration for subversion, set's last_config.
		require
			instruction_valid: instruction /= Void and then not instruction.is_empty
			permission_valid: permission /= Void and then (permission.is_equal ("r") or permission.is_equal ("rw"))
			public_project_types_valid: public_project_types /= Void
		local
			l_res: SELECTION_RESULT
			l_project: STRING
			l_glob_user: STRING
		do
				-- get global users
			select_query.set_map_name (instruction, "INSTRUCTION")
			l_res := select_query.execute_query ("[
				SELECT u.name from policy pol
				JOIN user_association ua USING(group_id)
				JOIN user u USING (user_id)
				WHERE pol.instruction=:INSTRUCTION
			]")
			create l_glob_user.make (256)
			from
				l_res.start
			until
				l_res.after
			loop
				l_glob_user.append (l_res.read_string ("name"))
				l_glob_user.append_string (" = " + permission + "%N")
				l_res.forth
			end

				-- get project specific users and write configuration
			select_query.set_map_name (instruction, "INSTRUCTION")
			l_res := select_query.execute_query ("[
				SELECT p.name AS project, p.project_type, u.name AS user FROM policy pol
				JOIN user_project_association upa USING(group_id)
				JOIN user u USING (user_id)
				JOIN project p USING (project_id)
				WHERE pol.instruction=:INSTRUCTION
				ORDER BY p.name
			]")
			create Result.make (1024)
			from
				create l_project.make_empty
				l_res.start
			until
				l_res.after
			loop
				if not l_project.is_equal (l_res.read_string ("project")) then
					l_project := l_res.read_string ("project")
					Result.append ("["+l_project+":/]%N")
						-- add read access for public projects
					if (public_project_types.has (l_res.read_integer ("project_type"))) then
						Result.append ("* = r%N")
					end
					Result.append (l_glob_user)
				end
				Result.append (l_res.read_string ("user"))
				Result.append (" = " + permission + "%N")
				l_res.forth
			end
		ensure
			result_set: Result /= Void
		end

	retrieve_access_mapping (a_instruction: STRING; a_permission: STRING; a_public_project_types: DS_SET[INTEGER])
			-- Similarly to `retrieve_access', this feature returns a table of projects
			-- and associated users that have `a_permission' for `a_instruction' on that
			-- project.
			-- Public access is represented by a special "user" with name "*" (an asteriks)
		require
			instruction_valid: a_instruction /= Void and then not a_instruction.is_empty
			permission_valid: a_permission /= Void and then (a_permission.is_equal ("r") or a_permission.is_equal ("rw"))
			public_project_types_valid: a_public_project_types /= Void
		local
			l_selection_result: SELECTION_RESULT
			l_global_users: DS_ARRAYED_LIST [STRING]
			l_project_name: STRING
			l_tuple: TUPLE [read: DS_ARRAYED_LIST [STRING]; rw:DS_ARRAYED_LIST [STRING]]
			l_read_list, l_rw_list: DS_ARRAYED_LIST [STRING]
		do
				-- get global users
			select_query.set_map_name (a_instruction, "instruction")
			l_selection_result := select_query.execute_query ("[
				SELECT u.name from policy pol
				JOIN user_association ua USING(group_id)
				JOIN user u USING (user_id)
				WHERE pol.instruction=:instruction
			]")
			create l_global_users.make (l_selection_result.count)
			from
				l_selection_result.start
			until
				l_selection_result.after
			loop
				l_global_users.force_last (l_selection_result.read_string ("name"))

				-- increment
				l_selection_result.forth
			end

				-- get project specific users and write configuration
			select_query.set_map_name (a_instruction, "instruction")
			l_selection_result := select_query.execute_query ("[
				SELECT p.name AS project, p.project_type, u.name AS user FROM policy pol
				JOIN user_project_association upa USING(group_id)
				JOIN user u USING (user_id)
				JOIN project p USING (project_id)
				WHERE pol.instruction=:instruction
				ORDER BY p.name
			]")

			create last_access_mapping.make (1024)
			last_access_mapping.set_key_equality_tester (create {KL_STRING_EQUALITY_TESTER})
			from
				l_selection_result.start
			until
				l_selection_result.after
			loop
				l_project_name := l_selection_result.read_string ("project")

				if not last_access_mapping.has (l_project_name) then
					create l_tuple
					create l_read_list.make (2)
					l_tuple.read := l_read_list
					create l_rw_list.make (2)
					l_tuple.rw := l_rw_list

					last_access_mapping.force (l_tuple, l_project_name)

						-- add read access for public projects
					if (a_public_project_types.has (l_selection_result.read_integer ("project_type"))) then
						l_tuple.read.force_last ("*")
					end

					if a_permission.is_equal ("r") then
						l_tuple.read.extend_last (l_global_users)
					else
						l_tuple.rw.extend_last (l_global_users)
					end
				end

				l_tuple := last_access_mapping.item (l_project_name)

				-- append the user to the current project
				if a_permission.is_equal ("r") then
					l_tuple.read.force_last (l_selection_result.read_string ("user"))
				else
					l_tuple.rw.force_last (l_selection_result.read_string ("user"))
				end

				-- increment
				l_selection_result.forth
			end
		ensure
			created: last_access_mapping /= Void
		end

feature -- Insert queries

	insert_session (a_session: SESSION)
			-- Add a_session as a new session entry.
		require
			a_session_not_void: a_session /= Void
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_session.session_id, "session_id")
			l_store.set_map_name (a_session.user_id, "user_id")
			l_store.modify ("INSERT INTO session SET session_id=:session_id, user_id=:user_id, creation_time=UNIX_TIMESTAMP(), access_time=UNIX_TIMESTAMP()")
			handle_errors_and_warnings
		end

	insert_user (a_user: USER)
			-- Add a_user as a new user.
		require
			a_user_not_void: a_user /= Void
		local
			l_store: DB_STORE
		do
			create l_store.make
			l_store.set_repository (db_handler.db_repositories.item ("user"))
			l_store.put (a_user)
			handle_errors_and_warnings
		end

	insert_user_information (a_user_id: INTEGER)
			-- Add a new user information for user with `a_user_id', only used at user creation
			-- to insert correct registration_date
		require
			a_user_id_valid: a_user_id > 0
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_user_id, "user_id")
			l_store.modify ("INSERT INTO `user_information` (`user_id`, `registration_date`) VALUES (:user_id, UNIX_TIMESTAMP())")
			handle_errors_and_warnings
		end

	insert_user_association (a_user_association: USER_ASSOCIATION)
			-- Add a new user association
		local
			l_store: DB_STORE
		do
			create l_store.make
			l_store.set_repository (db_handler.db_repositories.item ("user_association"))
			l_store.put (a_user_association)
			handle_errors_and_warnings
		end

	insert_bookmark (a_project_bookmark: PROJECT_BOOKMARK)
			-- Add a_project_bookmark as a new bookmark.
		require
			a_project_bookmark_not_void: a_project_bookmark /= Void
		local
			l_store: DB_STORE
		do
			if not has_bookmarked_project (a_project_bookmark.project_id, a_project_bookmark.user_id) then
				create l_store.make
				l_store.set_repository (db_handler.db_repositories.item ("project_bookmark"))
				l_store.put (a_project_bookmark)
			end
			handle_errors_and_warnings
		end

	insert_friendship_request (a_requester_id, a_requestee_id: INTEGER)
			-- Insert IDs of requester and requestee into friendship_request table.
		require
			a_requester_id_ok: a_requester_id > 0
			a_requestee_id_ok: a_requestee_id > 0
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_requester_id, "requester_id")
			l_store.set_map_name (a_requestee_id, "requestee_id")
			l_store.modify ("INSERT INTO user_friendship_request (requester_id, requestee_id, creation_time) VALUES (:requester_id, :requestee_id, UNIX_TIMESTAMP())")
			handle_errors_and_warnings
		end

	insert_friendship (a_user_id, a_friend_id: INTEGER)
			-- Insert IDs of two users into friendship table.
		require
			a_user_id_ok: a_user_id > 0
			a_friend_id_ok: a_friend_id > 0
			user_different: a_user_id /= a_friend_id
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_user_id, "user_id")
			l_store.set_map_name (a_friend_id, "friend_id")
			l_store.modify ("INSERT INTO user_friendship (user_id, friend_id) VALUES (:user_id, :friend_id)")
			handle_errors_and_warnings

			l_store.modify ("INSERT INTO user_friendship (user_id, friend_id) VALUES (:friend_id, :user_id)")
			handle_errors_and_warnings
		end


	insert_all_project_workitem_subscriptions (a_user_id: INTEGER; a_project_id: INTEGER)
			-- Insert all workitem subscriptions for `a_user_id' of `a_project_id'.
		require
			a_user_id_ok: a_user_id >= 0
		local
			L_workitem_access: WORKITEM_ACCESS
			l_workitem_types: DS_ARRAYED_LIST[INTEGER]
			l_cursor: DS_LINEAR_CURSOR[INTEGER]
		do
			l_workitem_access := db_interface.workitem_access

			l_workitem_types := l_workitem_access.retrieve_workitem_types
			l_cursor := l_workitem_types.new_cursor
			from
				l_cursor.start
			until
				l_cursor.after
			loop
				update_user_workitem_subscription (a_project_id, a_user_id, l_cursor.item, 1, True)
				update_user_workitem_subscription (a_project_id, a_user_id, l_cursor.item, 2, True)
				l_cursor.forth
			end
		end

feature -- Update queries

	update_session (a_session: SESSION)
			-- Update the entry for a_session with the latest access time if there was some time since the last access.
		require
			a_session_not_void: a_session /= Void
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_session.session_id, "session_id")
				-- for performance reasons only update if the access time is older than 5 minutes
			l_store.modify ("UPDATE session SET access_time=UNIX_TIMESTAMP() WHERE session_id=:session_id AND access_time<UNIX_TIMESTAMP()-300")
			handle_errors_and_warnings
		end

	update_user_project_association (a_project_id: INTEGER; an_user_id: INTEGER; an_access_group: INTEGER)
			-- Update a user project association.
		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 (an_user_id, "user_id")
			l_change.set_map_name (an_access_group, "group_id")

				-- access group of 0 means we can remove the entry
			if an_access_group = 0 then
				l_change.modify ("DELETE FROM user_project_association WHERE project_id=:project_id AND user_id=:user_id")

				-- else update
			else
				l_change.modify ("[
						INSERT INTO user_project_association SET project_id=:project_id, user_id=:user_id, group_id=:group_id
						ON DUPLICATE KEY UPDATE group_id=:group_id
					]")
			end
			handle_errors_and_warnings
		end

	update_user_community_association (a_community_id: INTEGER; an_user_id: INTEGER; an_access_group: INTEGER)
			-- Update a user community association.
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_community_id, "community_id")
			l_change.set_map_name (an_user_id, "user_id")
			l_change.set_map_name (an_access_group, "group_id")

				-- access group of 0 means we can remove the entry
			if an_access_group = 0 then
				l_change.modify ("DELETE FROM user_community_association WHERE community_id=:community_id AND user_id=:user_id")

				-- else update
			else
				l_change.modify ("[
						INSERT INTO user_community_association SET community_id=:community_id, user_id=:user_id, group_id=:group_id
						ON DUPLICATE KEY UPDATE group_id=:group_id
					]")
			end
			handle_errors_and_warnings
		end

	update_user_email (a_user: INTEGER; an_email_address: STRING)
			-- Update the email address of a_user to the new value an_email_address
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user, "user_id")
			l_change.set_map_name (an_email_address, "email")

			l_change.modify ("UPDATE user SET email=:email WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	update_user_last_login (a_user: INTEGER)
			-- Update the the last login timestamp of `a_user' to the current timestamp
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user, "user_id")

			l_change.modify ("UPDATE user_information SET last_login=UNIX_TIMESTAMP() WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	update_profile_visible (a_user_id, a_visibility: INTEGER)
			-- Update the profile visibility
		require
			a_user_id_valid: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.set_map_name (a_visibility, "profile_visible")

			l_change.modify ("UPDATE user_information SET profile_visible=:profile_visible WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	increase_application_login_count(a_application_key: STRING)
			-- increase the login count for application with key `a_application_key' by 1
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_application_key, "app_key")

			l_change.modify ("UPDATE application SET login_count=login_count+1 WHERE app_key=:app_key")
			handle_errors_and_warnings
		end

	update_user_password (a_user: INTEGER; a_password: STRING)
			-- Update the password of a_user to the new value a_password
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user, "user_id")
			l_change.set_map_name (a_password, "pass")

			l_change.modify ("UPDATE user SET pass=:pass WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	update_user_key (a_user: INTEGER; a_key: STRING)
			-- Update the key of a_user to the new value a_key
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user, "user_id")
			l_change.set_map_name (a_key, "key")

			l_change.modify ("UPDATE user SET user_key=:key WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	update_user_workitem_subscription (a_project: INTEGER; a_user: INTEGER; a_workitem_type: INTEGER; a_subscription_type: INTEGER; a_is_enabled: BOOLEAN)
			-- Update a user workitem subscription.
		local
			l_change: DB_CHANGE
		do
			-- Result := True
			create l_change.make
			l_change.set_map_name (a_project, "project_id")
			l_change.set_map_name (a_user, "user_id")
			l_change.set_map_name (a_workitem_type, "type_id")
			l_change.set_map_name (a_subscription_type, "subscription_type")
			if a_is_enabled then
				l_change.modify ("[
						INSERT INTO user_workitem_subscription SET project_id=:project_id, user_id=:user_id, type_id=:type_id, subscription_type=:subscription_type, enabled=1
						ON DUPLICATE KEY UPDATE enabled=1
					]")
			else
				l_change.modify ("[
						INSERT INTO user_workitem_subscription SET project_id=:project_id, user_id=:user_id, type_id=:type_id, subscription_type=:subscription_type, enabled=0
						ON DUPLICATE KEY UPDATE enabled=0
					]")
			end
			handle_errors_and_warnings
		rescue
			-- the workitem subscription could not be inserted (FK constraint)
			-- Result := False
		end

	update_user_information (a_user_id: INTEGER; a_information_type: STRING; a_information_value: STRING)
			-- Update a user information.
		require
			user_id_ok: a_user_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
			l_selection: DB_SELECTION
			l_user_information: USER_INFORMATION
			l_user_information_list_filling: DB_ACTION [USER_INFORMATION]
			l_user_informations: LIST [USER_INFORMATION]
			l_int_value: INTEGER
		do
			create l_user_information.make

				-- check if user is already in user_information table
			create l_selection.make
			l_selection.set_map_name (a_user_id, "user_id")
			l_selection.query ("SELECT * FROM user_information WHERE user_id=:user_id")
			handle_errors_and_warnings

			l_selection.object_convert (l_user_information)
			create l_user_information_list_filling.make (l_selection, l_user_information)
			l_selection.set_action (l_user_information_list_filling)
			l_selection.load_result
			l_selection.terminate
			l_user_informations := l_user_information_list_filling.list

			if l_user_informations.count = 0 then
					-- user does not have any information -> create new set with registration time
				create l_change.make
				l_change.set_map_name (a_user_id, "user_id")
				l_change.modify ("INSERT INTO user_information (user_id, registration_date) VALUES (:user_id, UNIX_TIMESTAMP())")
				handle_errors_and_warnings
			end

				-- check for information_type and call corresponding query
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.set_map_name (a_information_value, "value")

			if a_information_type.is_equal ("first_name") then
				l_change.modify ("UPDATE user_information SET first_name=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("last_name") then
				l_change.modify ("UPDATE user_information SET last_name=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("gender") then
				l_change.modify ("UPDATE user_information SET gender=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("languages") then
				l_change.modify ("UPDATE user_information SET languages=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("birthday") then
				l_change.modify ("UPDATE user_information SET birthday=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("timezone") then
				l_change.modify ("UPDATE user_information SET timezone=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("signature") then
				l_change.modify ("UPDATE user_information SET signature=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("message") then
				l_change.modify ("UPDATE user_information SET message=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("homepage") then
				l_change.modify ("UPDATE user_information SET homepage=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("blog") then
				l_change.modify ("UPDATE user_information SET blog=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("ohloh_profile") then
				l_change.modify ("UPDATE user_information SET ohloh_profile=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("icq") then
				l_change.modify ("UPDATE user_information SET icq=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("aim") then
				l_change.modify ("UPDATE user_information SET aim=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("jabber") then
				l_change.modify ("UPDATE user_information SET jabber=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("yahoo") then
				l_change.modify ("UPDATE user_information SET yahoo=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("msn") then
				l_change.modify ("UPDATE user_information SET msn=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("skype") then
				l_change.modify ("UPDATE user_information SET skype=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("sip") then
				l_change.modify ("UPDATE user_information SET sip=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("irc") then
				l_change.modify ("UPDATE user_information SET irc=:value WHERE user_id=:user_id")
			elseif a_information_type.is_equal ("workitems_per_page") then
				l_int_value := a_information_value.to_integer
				if l_int_value > 0 then
					-- default value
					l_change.unset_map_name ("value")
					l_change.set_map_name (l_int_value, "value")
					l_change.modify ("UPDATE `user_information` SET `workitems_per_page` = :value WHERE user_id=:user_id")
				end
			elseif a_information_type.is_equal ("issues_per_page") then
				l_int_value := a_information_value.to_integer
				if l_int_value > 0 then
					-- default value
					l_change.unset_map_name ("value")
					l_change.set_map_name (l_int_value, "value")
					l_change.modify ("UPDATE `user_information` SET `issues_per_page` = :value WHERE user_id=:user_id")
				end
			elseif a_information_type.is_equal ("feed_show_unread") then
				l_int_value := a_information_value.to_boolean.to_integer
				l_change.unset_map_name ("value")
				l_change.set_map_name (l_int_value, "value")
				l_change.modify ("UPDATE `user_information` SET `feed_show_unread` = :value WHERE user_id=:user_id")
			end

			handle_errors_and_warnings
		end

	update_user_icon (a_user_id: INTEGER; an_icon: STRING)
			-- Update a user icon.
		require
			user_id_ok: a_user_id > 0
			icon_ok: an_icon /= Void
		local
			l_change: DB_CHANGE
			l_selection: DB_SELECTION
			l_user_information: USER_INFORMATION
			l_user_information_list_filling: DB_ACTION [USER_INFORMATION]
			l_user_informations: LIST [USER_INFORMATION]
		do
			create l_user_information.make

				-- check if user is already in user_information table
			create l_selection.make
			l_selection.set_map_name (a_user_id, "user_id")
			l_selection.query ("SELECT * FROM user_information WHERE user_id=:user_id")
			handle_errors_and_warnings

			l_selection.object_convert (l_user_information)
			create l_user_information_list_filling.make (l_selection, l_user_information)
			l_selection.set_action (l_user_information_list_filling)
			l_selection.load_result
			l_selection.terminate
			l_user_informations := l_user_information_list_filling.list

			if l_user_informations.count = 0 then
					-- user does not have any information -> create new set with registration time
				create l_change.make
				l_change.set_map_name (a_user_id, "user_id")
				l_change.modify ("INSERT INTO user_information (user_id, registration_date) VALUES (:user_id, UNIX_TIMESTAMP())")
				handle_errors_and_warnings
			end

				-- now insert icon into user_information
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.set_map_name (an_icon, "value")

			l_change.modify ("UPDATE user_information SET icon=:value WHERE user_id=:user_id")

			handle_errors_and_warnings
		end

feature -- Special Queries

	disable_account (a_user_id: INTEGER)
			-- Disable an user account with user_id `a_user_id' and set user_association to 5.
			-- And update user_information.disable_date to current timestamp.
		require
			a_user_id_ok: a_user_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("UPDATE user SET disabled = 'true' WHERE user_id=:user_id")
			handle_errors_and_warnings

			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("UPDATE user_association SET group_id=5 WHERE user_id=:user_id")
			handle_errors_and_warnings

			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("UPDATE user_information SET disable_date=UNIX_TIMESTAMP() WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	enable_account (a_user_id: INTEGER)
			-- Enable an user account with user_id `a_user_id' and set user_association to 2.
			-- And update user_information.disable_date to current timestamp.
		require
			a_user_id_ok: a_user_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("UPDATE user SET disabled = 'false' WHERE user_id=:user_id")
			handle_errors_and_warnings

			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("UPDATE user_association SET group_id=2 WHERE user_id=:user_id")
			handle_errors_and_warnings

			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("UPDATE user_information SET disable_date=0 WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	has_user_key (a_user_key: STRING): BOOLEAN
			-- Does the database contain `a_user_key'?
		require
			a_user_key_ok: a_user_key /= Void and then not a_user_key.is_empty
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_user_key, "key")
			l_selection := select_query.execute_query ("[
								SELECT *
								FROM user
								WHERE user_key =:key
							]")

			-- Process results
			Result := (l_selection.count > 0)
		end

	user_valid_setting_names: DS_HASH_TABLE[STRING, STRING]
			-- Retrieves valid setting names, i.e. the set of column names and their
			-- corresponding types from the database's table `user_information'.
			-- Note: this feature currently only supports INTEGER, DOUBLE and STRING
			-- data types.
		local
			l_repo: DB_REPOSITORY
			l_column:  COLUMNS[DATABASE]
			i: INTEGER
			l_type_name: STRING
			l_eiffel_type_code: INTEGER
			l_number_information: NUMERIC_INFORMATION
		once
			l_repo := db_handler.db_repositories.item ("user_information")
			from
				i := 1
				create Result.make (l_repo.column_number)
			until
				i > l_repo.column_number
			loop
				l_column := l_repo.column_i_th (i)

				-- Attention, ugly code ahead... :/
				create l_number_information
				l_eiffel_type_code := l_column.eiffel_type

				l_type_name := setting_string_type
				if l_number_information.integer_type_valid (l_eiffel_type_code) then
					l_type_name := setting_integer_type
				elseif l_number_information.double_type_valid (l_eiffel_type_code) then
					l_type_name := setting_double_type
				end

				Result.force (l_type_name, l_column.column_name)

				-- inc
				i := i + 1
			end
		ensure
			created: Result /= Void
		end

feature -- Delete queries

	delete_bookmark (a_project_id: INTEGER; a_user_id: INTEGER)
			-- Delete a bookmark to a project.
		require
			a_project_id_ok: a_project_id >= 0
			a_user_id_ok: a_user_id > 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_user_id, "user_id")
			l_change.modify ("DELETE FROM project_bookmark WHERE project_id=:project_id AND user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_all_bookmarks (a_user_id: INTEGER)
			-- Delete all bookmarks for a_user_id.
		require
			a_user_id_ok: a_user_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("DELETE FROM project_bookmark WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_friendship_request (a_requester_id, a_requestee_id: INTEGER)
			-- Delete IDs of requester and requestee from user_friendship_request.
		require
			a_requester_id_ok: a_requester_id > 0
			a_requestee_id_ok: a_requestee_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_requester_id, "requester_id")
			l_change.set_map_name (a_requestee_id, "requestee_id")
			l_change.modify ("DELETE FROM user_friendship_request WHERE requester_id=:requester_id AND requestee_id=:requestee_id")
			handle_errors_and_warnings
		end

	delete_all_friendship_requests (a_user_id: INTEGER)
			-- Delete all requests from or to a_user_id
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "requester_id")
			l_change.set_map_name (a_user_id, "requestee_id")
			l_change.modify ("DELETE FROM user_friendship_request WHERE requester_id=:requester_id")
			handle_errors_and_warnings

			l_change.modify ("DELETE FROM user_friendship_request WHERE requestee_id=:requestee_id")
			handle_errors_and_warnings
		end

	delete_friendship (a_user_id, a_friend_id: INTEGER)
			-- Delete IDs of user and friend from user_friendship.
			-- Delete two entries because of table design.
		require
			a_user_id_ok: a_user_id >= 0
			a_friend_id_ok: a_friend_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.set_map_name (a_friend_id, "friend_id")
			l_change.modify ("DELETE FROM user_friendship WHERE user_id=:user_id AND friend_id=:friend_id")
			handle_errors_and_warnings

			l_change.modify ("DELETE FROM user_friendship WHERE user_id=:friend_id AND friend_id=:user_id")
			handle_errors_and_warnings
		end

	delete_all_friendships (a_user_id: INTEGER)
			-- Delete all friendships for a_user_id.
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("DELETE FROM user_friendship WHERE user_id=:user_id")
			handle_errors_and_warnings

			l_change.modify ("DELETE FROM user_friendship WHERE friend_id=:user_id")
			handle_errors_and_warnings
		end

	delete_all_user_project_associations (a_user_id: INTEGER)
			-- Delete all user project associations for a_user_id.
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")

			l_change.modify ("DELETE FROM user_project_association WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_all_user_read_workitems (a_user_id: INTEGER)
			-- Delete all read workitems for a_user_id.
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")

			l_change.modify ("DELETE FROM user_read_workitem WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_user_information (a_user_id: INTEGER)
			-- Delete all user information for `a_user_id'.
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")

			l_change.modify ("DELETE FROM user_information WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_project_workitem_subscriptions (a_user_id: INTEGER; a_project_id: INTEGER)
			-- Delete all workitem subscriptions for `a_user_id' of `a_project_id'.
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.set_map_name (a_project_id, "project_id")

			l_change.modify ("DELETE FROM user_workitem_subscription WHERE user_id=:user_id AND project_id=:project_id")
			handle_errors_and_warnings
		end

	delete_all_workitem_subscriptions (a_user_id: INTEGER)
			-- Delete all workitem subscriptions for `a_user_id'.
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")

			l_change.modify ("DELETE FROM user_workitem_subscription WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_session (a_user_id: INTEGER)
			-- Delete backend session of `a_user_id'.
		require
			a_user_id_ok: a_user_id >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("DELETE FROM session WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

feature -- Purge queries

	purge_sessions
			-- Purge all sessions.
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.modify ("TRUNCATE session")
			handle_errors_and_warnings
		end

	purge_old_sessions (a_session_lifetime: INTEGER)
			-- Purge sessions older than `a_session_lifetime' seconds.
		require
			a_session_lifetime_ok: a_session_lifetime > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_session_lifetime, "lifetime")
			l_change.modify ("DELETE FROM session WHERE access_time < (UNIX_TIMESTAMP() - :lifetime)")
			handle_errors_and_warnings
		end

	purge_old_friendship_requests (a_friendship_request_lifetime: INTEGER)
			-- Purge friendship requests older than `a_friendship_request_lifetime' days
		require
			a_friendship_request_lifetime_ok: a_friendship_request_lifetime > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_friendship_request_lifetime, "lifetime")
			l_change.modify ("DELETE FROM user_friendship_request WHERE creation_time<(UNIX_TIMESTAMP()-86400 * :lifetime)")
			handle_errors_and_warnings
		end

	purge_old_unverified_accounts (a_account_activation_lifetime: INTEGER)
			-- Purge friendship requests older than `a_account_activation_lifetime' days
		require
			a_account_activation_lifetime_ok: a_account_activation_lifetime > 0
		local
			l_change: DB_CHANGE
			l_string: STRING
		do
			-- how to find non-verified account, they must be:
			-- disabled
			-- registration date must be near disable date (because it is disabled right after registering)
			-- no dummy accounts (disable_date = 0) or very old accounts
			-- not logged in (last_login = 0)
			create l_string.make_from_string ("[
				DELETE u FROM user as u INNER JOIN user_information ON u.user_id = user_information.user_id
				WHERE user_information.last_login = 0 AND
				user_information.disable_date > 0 AND
				(user_information.disable_date - user_information.registration_date) < 60 AND
				u.disabled = true AND
				user_information.registration_date<(UNIX_TIMESTAMP()-86400 * :lifetime)
				]")

			create l_change.make
			l_change.set_map_name (a_account_activation_lifetime, "lifetime")
			l_change.modify (l_string)
			handle_errors_and_warnings
		end

feature {NONE} -- Implementation

	setting_string_type: STRING = "string"
	setting_integer_type: STRING = "integer"
	setting_double_type: STRING = "double"

end