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

class
	USER_ACCESS

inherit
	DATABASE_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_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.

	policies: LIST [POLICY]
			-- Last retrieved policies.

	last_user_associations: LIST [USER_ASSOCIATION]
			-- Last retrieved user associations.

	last_user_project_associations: LIST [USER_PROJECT_ASSOCIATION]
			-- Last retrieved user project associations.

	last_user_community_associations: LIST [USER_COMMUNITY_ASSOCIATION]
			-- Last retrieved user community associations.

	last_access_groups: ARRAYED_LIST [INTEGER]
			-- last retrieved access groups.

	last_openids: ARRAY [STRING]
			-- last retrieved openid urls.

feature -- Select queries

	has_bookmarked_project (a_project_id: INTEGER; a_user_id: INTEGER): BOOLEAN is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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_from_openid (a_openid: STRING): STRING is
			-- Retrieve a user_name by a_openid.
		require
			a_openid_ok: a_openid /= Void and then not a_openid.is_empty
		local
			l_selection_result: SELECTION_RESULT
		do
			select_query.set_map_name (a_openid, "openid")
			l_selection_result := select_query.execute_query("SELECT u.name FROM user_openid s JOIN user u USING(user_id) WHERE s.openid=:openid AND u.disabled='false' LIMIT 1")
			handle_errors_and_warnings
			if l_selection_result.count > 0 then
				l_selection_result.start
				Result := l_selection_result.read_string ("name")
			else
				Result := ""
			end
		ensure
			Result_not_void: Result /= Void
		end

	retrieve_user_by_session (a_session: STRING) is
			-- 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) is
			-- 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) is
			-- Retrieve user information by user ID.
		require
			an_id_ok: an_id >= 0
		local
			l_selection: DB_SELECTION
			l_user_list_filling: DB_ACTION [USER]
			l_user: USER
			l_users: LIST [USER]
			l_user_information_list_filling: DB_ACTION [USER_INFORMATION]
			l_user_information: USER_INFORMATION
			l_user_informations: LIST [USER_INFORMATION]
		do
			create l_user.make
			create l_user_information.make

				-- get user from USER table
			create l_selection.make
			l_selection.set_map_name (an_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_user_list_filling.make (l_selection, l_user)
			l_selection.set_action (l_user_list_filling)
			l_selection.load_result
			l_selection.terminate
			l_users := l_user_list_filling.list

				-- 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_users.count = 1 and l_user_informations.count = 1 then
					-- user and informations found
				is_found := True
				last_user := l_users.first
				last_user_information := l_user_informations.first
			elseif l_users.count = 1 and l_user_informations.count = 0 then
					-- user but no informations found, create empty informations
				is_found := True
				last_user := l_users.first
				create last_user_information.make
			else
				is_found := False
			end
		ensure
			is_found_implies_user_and_information: is_found implies last_user /= Void and last_user_information /= Void
		end

	retrieve_friendship_request (a_requester_id, a_requestee_id: INTEGER) is
			-- 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) is
			-- 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) is
			-- 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")
			handle_errors_and_warnings

			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] is
			-- 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_policies is
			-- Retrieve all policies.
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [POLICY]
			l_policy: POLICY
		do
			create l_policy.make

			create l_selection.make
			l_selection.set_query ("SELECT * FROM policy")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_policy)
			create l_list_filling.make (l_selection, l_policy)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			policies := l_list_filling.list
		ensure
			policies_filled: policies /= Void
		end

	retrieve_user_association (a_user: INTEGER) is
			-- Retrieve user associations for a_user.
		require
			a_user_set: a_user >= 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER_ASSOCIATION]
			l_obj: USER_ASSOCIATION
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_user, "user_id")
			l_selection.set_query ("SELECT * FROM user_association WHERE 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_user_associations := l_list_filling.list
		ensure
			last_user_associations_filled: last_user_associations /= Void
		end

	retrieve_user_project_association (a_user: INTEGER) is
			-- Retrieve user project associations for a_user.
		require
			a_user_set: a_user >= 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER_PROJECT_ASSOCIATION]
			l_obj: USER_PROJECT_ASSOCIATION
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_user, "user_id")
			l_selection.set_query ("SELECT * FROM user_project_association WHERE 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_user_project_associations := l_list_filling.list
		ensure
			last_user_project_associations_filled: last_user_project_associations /= Void
		end

	retrieve_user_project_association_group (a_project: INTEGER; a_user: INTEGER): INTEGER is
			-- 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 (a_user_id: INTEGER) is
			-- Retrieve user community associations for a_user.
		require
			a_user_id_set: a_user_id >= 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER_COMMUNITY_ASSOCIATION]
			l_obj: USER_COMMUNITY_ASSOCIATION
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_user_id, "user_id")
			l_selection.set_query ("SELECT * FROM user_community_association WHERE 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_user_community_associations := l_list_filling.list
		ensure
			last_user_community_associations_filled: last_user_community_associations /= Void
		end

	retrieve_user_community_association_group (a_community_id: INTEGER; a_user_id: INTEGER): INTEGER is
			-- 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) is
			-- 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 is
			-- 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 is
			-- 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 is
			-- 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 is
			-- 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
			--   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_openids_from_user (a_user_id: INTEGER; a_visible: BOOLEAN) is
			-- returns an array containing the openids associated with given user.
			-- if `a_visible' is True, only visible openid's will be returned.
		require
			a_user_id_valid: a_user_id > 0
		local
			l_selection: DB_SELECTION
			l_openids: ARRAYED_LIST [DB_RESULT]
			l_openid: DB_TUPLE
			i: INTEGER
			l_query: STRING
		do
			create l_openids.make (30)

			create l_selection.make
			l_selection.set_container (l_openids)
			l_selection.set_map_name (a_user_id, "user_id")
			l_query := "SELECT openid FROM user_openid WHERE user_id=:user_id"
			if a_visible then
				l_query.append (" AND visible=1")
			end
			l_selection.set_query (l_query)
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.load_result
			l_selection.terminate

			from
				create last_openids.make (0, l_openids.count-1)
				l_openids.start
				i := 0
			until
				l_openids.after
			loop
				create l_openid.copy (l_openids.item)
				last_openids.put (l_openid.item (1).out, i)
				l_openids.forth
				i := i + 1
			end
		ensure
			last_openids_filled: last_openids /= Void
		end

	retrieve_foaf (a_user_id: INTEGER): ARRAY [STRING] is
			-- returns an array containing the foaf urls associated with given user.
		require
			a_user_id_valid: a_user_id > 0
		local
			l_selection: DB_SELECTION
			l_foaf: ARRAYED_LIST [DB_RESULT]
			l_f: DB_TUPLE
			i: INTEGER
			l_query: STRING
		do
			create l_foaf.make (50)

			create l_selection.make
			l_selection.set_container (l_foaf)
			l_selection.set_map_name (a_user_id, "user_id")
			l_query := "SELECT foaf FROM user_foaf WHERE user_id=:user_id"
			l_selection.set_query (l_query)
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.load_result
			l_selection.terminate

			from
				create Result.make (0, l_foaf.count-1)
				l_foaf.start
				i := 0
			until
				l_foaf.after
			loop
				create l_f.copy (l_foaf.item)
				Result.put (l_f.item (1).out, i)
				l_foaf.forth
				i := i + 1
			end
		ensure
			Result_filled: Result /= Void
		end

	retrieve_key (a_session_id: STRING): STRING is
			-- 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_access_groups (a_kind: STRING) is
			-- Retrieve all possible global access groups.
		require
			a_kind_valid: a_kind /= Void and then (a_kind.is_equal ("global") or a_kind.is_equal ("project") or a_kind.is_equal ("community"))
		local
			l_selection: DB_SELECTION
			l_groups: ARRAYED_LIST [DB_RESULT]
			l_grp: DB_TUPLE
		do
			create l_groups.make (10)

			create l_selection.make
			l_selection.set_container (l_groups)
			l_selection.set_map_name (a_kind, "kind")
			l_selection.set_query ("SELECT group_id FROM access_group WHERE kind=:kind")
			l_selection.execute_query
			handle_errors_and_warnings

			l_selection.load_result
			l_selection.terminate
			from
				create last_access_groups.make (l_groups.count)
				l_groups.start
			until
				l_groups.after
			loop
				create l_grp.copy (l_groups.item)
				last_access_groups.force (l_grp.item (1).out.to_integer)
				l_groups.forth
			end
		ensure
			last_access_groups_set: last_access_groups /= Void
		end

	retrieve_mail_addresses: LIST [STRING] is
			-- 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 is
			-- 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]] is
			-- 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: STRING is
			-- 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::/data/ftp/")
				Result.append (l_name)
				Result.append ("/./::5120::::::::::%N")
				l_res.forth
			end
		ensure
			result_set: Result /= Void
		end

	retrieve_svn_access: STRING is
			-- Retrieve access configuration for subversion, set's last_config.
		local
			l_res: SELECTION_RESULT
			l_project: STRING
			l_glob_user: STRING
		do
				-- get global users
			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='svn_write'
			]")
			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 (" = rw%N")
				l_res.forth
			end

				-- get project specific users and write configuration
			l_res := select_query.execute_query ("[
				SELECT p.name AS project, p.closed_source, 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='svn_write'
				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 everyone if it is not protected
					if not l_res.read_string ("closed_source").to_boolean then
						Result.append ("* = r%N")
					end
					Result.append (l_glob_user)
				end
				Result.append (l_res.read_string ("user"))
				Result.append (" = rw%N")
				l_res.forth
			end
		ensure
			result_set: Result /= Void
		end

feature -- Insert queries

	insert_session (a_session: SESSION) is
			-- 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) is
			-- 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_openid (a_user_id: INTEGER; an_openid: STRING) is
			-- Add a_user as a new user.
		require
			a_user_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.set_map_name (an_openid, "openid")
			l_store.modify ("INSERT INTO `user_openid` (`user_id`, `openid`) VALUES (:user_id, :openid)")
			handle_errors_and_warnings
		end

	insert_user_information (a_user_id: INTEGER) is
			-- 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) is
			-- 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_foaf (a_foaf: STRING; a_user_id: INTEGER) is
			-- Insert a foaf of a user.
		require
			a_foaf_ok: a_foaf /= Void and then not a_foaf.is_empty
			a_user_id_ok: a_user_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_foaf, "foaf")
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("INSERT INTO user_foaf (user_id, foaf) VALUES  (:user_id, :foaf)")
			handle_errors_and_warnings
		end

	insert_friendship_request (a_requester_id, a_requestee_id: INTEGER) is
			-- 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) is
			-- 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) is
			-- 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
			create l_workitem_access.make (db_handler)
			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
			handle_errors_and_warnings
		end


feature -- Update queries

	update_session (a_session: SESSION) is
			-- 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_openid (a_openid: STRING; a_user_id: INTEGER; a_visibility: INTEGER) is
			-- Delete an openid of a user.
		require
			a_openid_ok: a_openid /= Void and then not a_openid.is_empty
			a_user_id_ok: a_user_id > 0
			a_visibility_valid: a_visibility >= 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_openid, "openid")
			l_change.set_map_name (a_user_id, "user_id")
			if a_visibility >= 1 then
				l_change.set_map_name (1, "visible")
			else
				l_change.set_map_name (0, "visible")
			end

			l_change.modify ("UPDATE user_openid SET visible=:visible WHERE openid=:openid AND user_id=:user_id")
			handle_errors_and_warnings
		end

	update_user_project_association (a_project_id: INTEGER; an_user_id: INTEGER; an_access_group: INTEGER) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- Update a user workitem subscription.
		local
			l_change: DB_CHANGE
		do
			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
		end

	update_user_information (a_user_id: INTEGER; a_information_type: STRING; a_information_value: STRING) is
			-- 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]
		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")
			end

			handle_errors_and_warnings
		end

	update_user_icon (a_user_id: INTEGER; an_icon: STRING) is
			-- 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) is
			-- 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) is
			-- 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

feature -- Delete queries

	delete_bookmark (a_project_id: INTEGER; a_user_id: INTEGER) is
			-- 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) is
			-- 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_foaf (a_foaf: STRING; a_user_id: INTEGER) is
			-- Delete a foaf of a user.
		require
			a_foaf_ok: a_foaf /= Void and then not a_foaf.is_empty
			a_user_id_ok: a_user_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_foaf, "foaf")
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("DELETE FROM user_foaf WHERE foaf=:foaf AND user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_all_foaf (a_user_id: INTEGER) is
			-- Delete all foaf of a user.
		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_foaf WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_openid (a_openid: STRING; a_user_id: INTEGER) is
			-- Delete an openid of a user.
		require
			a_openid_ok: a_openid /= Void and then not a_openid.is_empty
			a_user_id_ok: a_user_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_openid, "openid")
			l_change.set_map_name (a_user_id, "user_id")
			l_change.modify ("DELETE FROM user_openid WHERE openid=:openid AND user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_all_openid (a_user_id: INTEGER) is
			-- Delete all openids 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_openid WHERE user_id=:user_id")
			handle_errors_and_warnings
		end

	delete_friendship_request (a_requester_id, a_requestee_id: INTEGER) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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) is
			-- 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

feature -- Purge queries

	purge_sessions is
			-- 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) is
			-- 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.modify ("DELETE FROM session WHERE access_time<UNIX_TIMESTAMP()-" + a_session_lifetime.out)
			handle_errors_and_warnings
		end

	purge_old_friendship_requests (a_friendship_request_lifetime: INTEGER) is
			-- 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.modify ("DELETE FROM user_friendship_request WHERE creation_time<(UNIX_TIMESTAMP()-86400*" + a_friendship_request_lifetime.out + ")")
			handle_errors_and_warnings
		end

end