cc.protea.foundation.utility.UserUtilDatabase Maven / Gradle / Ivy

Go to download
package cc.protea.foundation.utility;


import cc.protea.foundation.integrations.DatabaseUtil;
import cc.protea.foundation.integrations.DatabaseUtil.ItemReturn;
import cc.protea.foundation.integrations.DatabaseUtil.NullReturn;
import cc.protea.foundation.integrations.IntercomUtil;
import cc.protea.foundation.integrations.RedisUtil;
import cc.protea.foundation.integrations.RedisUtil.WithJedisNull;
import cc.protea.foundation.model.ProteaException;
import cc.protea.foundation.util.PasswordUtil;
import cc.protea.foundation.utility.services.login.AuthenticationRequest;
import cc.protea.foundation.utility.services.login.AuthenticationResponse;
import io.intercom.api.User;
import org.apache.commons.lang3.StringUtils;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.util.IntegerMapper;
import org.skife.jdbi.v2.util.StringMapper;
import redis.clients.jedis.Jedis;

import java.sql.ResultSet;
import java.util.Date;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

/**
 * Table structure: profound_user
 *   user_key primary key auto increment
 *   created_on date
 *   first_name string
 *   last_name string
 *   name string
 *   primary_email_address string
 *   facebook_id string
 *   google_id
 *   organization string
 *   linkedin_id
 *   twitter_id
 *   intercom_id
 *   encrypted_password
 *
 * profound_user_email
 *   key
 *   user_key
 *   email_address
 *   validated_on
 *
 * profound_user_role
 *   user_key
 *   role
 */
public class UserUtilDatabase {

	public static class Mapper extends DatabaseUtil.Mapper {
		Class valueType = ProteaUser.class;
		public Mapper(final Class valueType) {
			this.valueType = valueType;
		}
		public ProteaUser map(final ResultSet rs) {
			ProteaUser out = null;
			try {
				out = valueType.newInstance();
			} catch (Exception e) {
				out = new ProteaUser();
			}
			out.createdOn = DatabaseUtil.getDate(rs, "created_on");
			out.emailAddress = DatabaseUtil.getString(rs, "primary_email_address");
			out.firstName = DatabaseUtil.getString(rs, "first_name");
			out.id = DatabaseUtil.getInteger(rs, "user_key");
			out.lastName = DatabaseUtil.getString(rs, "last_name");
			out.lastSeen = DatabaseUtil.getDate(rs, "last_seen");
			out.name = DatabaseUtil.getString(rs, "name");
			out.organization = DatabaseUtil.getString(rs, "organization");
			out.primaryEmail = DatabaseUtil.getString(rs, "primary_email_address");
			out.authentication.facebook = DatabaseUtil.getString(rs, "facebook_id") != null;
			out.authentication.google = DatabaseUtil.getString(rs, "google_id") != null;
			out.authentication.linkedIn = DatabaseUtil.getString(rs, "linkedin_id") != null;
			out.authentication.password = DatabaseUtil.getString(rs, "encrypted_password") != null;
			out.authentication.twitter = DatabaseUtil.getString(rs, "twitter_id") != null;
			return out;
		}
	}

	static void updateLastSeen(final Integer userKey, final String ipAddress) {
		DatabaseUtil.transaction(new NullReturn() {
			public void process(final Handle h) {
				h.execute("UPDATE profound_user SET last_seen = ? WHERE user_key = ?", new Date(), userKey);
			}
		});
	}

	static void fillProteaUser(final ProteaUser user, final boolean fast) {
		ProteaUser db = getProteaUser(user.id, fast, user.getClass());
		if (db == null) {
			return;
		}
		user.authentication = db.authentication;
		user.createdOn = db.createdOn;
		user.emailAddress = db.emailAddress;
		user.firstName = db.firstName;
		user.lastName = db.lastName;
		user.name = db.name;
		user.organization = db.organization;
		user.primaryEmail = db.primaryEmail;
		user.roles = db.roles;
		user.emailAddresses = db.emailAddresses;
	}

	static  T getProteaUser(final int id, final boolean fast, final Class valueType) {
		return (T) DatabaseUtil.get(new ItemReturn() {
			public ProteaUser process(final Handle h) {
				ProteaUser user = h.createQuery("select * from profound_user where user_key = :key")
						.bind("key", id)
						.map(new Mapper(valueType))
						.first();
				if (user == null) {
					return null;
				}
				switch (ProfoundConfiguration.storage.roles) {
					case DATABASE:
						user.roles = h.createQuery("select role from profound_user_role where user_key = :key").bind("key", user.id).map(StringMapper.FIRST).list(Set.class);
						break;
					case REDIS:
						user.roles = UserUtilRedis.getRoles(user.id);
						break;
					default:
						throw new ProteaException("Unexpected role storage configuration");
				}
				user.emailAddresses = h.createQuery("select email_address from profound_user_email where user_key = :key and validated_on is not null").bind("key", user.id).map(StringMapper.FIRST).list(Set.class);
				return user;
			}
		});
	}

	static Integer getUserIdByPassword(final String email, final String password) {
		return DatabaseUtil.get(new ItemReturn() {
			public Integer process(final Handle h) {
				Map map = h.createQuery("SELECT profound_user.user_key, encrypted_password FROM profound_user " +
						" JOIN profound_user_email ON profound_user_email.user_key = profound_user.user_key " +
						" WHERE lower(profound_user_email.email_address) = :email ")
						.bind("email", email.toLowerCase())
						.first();
				if (map == null) {
					map = h.createQuery("SELECT profound_user.user_key, encrypted_password FROM profound_user " +
							" WHERE lower(email) = :email ")
							.bind("email", email.toLowerCase())
							.first();
				}
				if (PasswordUtil.doPasswordsMatch(StringUtils.trim(password), DatabaseUtil.getString(map, "encrypted_password"))) {
					return DatabaseUtil.getInteger(map, "user_key");
				}
				return null;
			}
		});
	}

	private static Integer getUserIdByPointer(final String field, final Object data) {
		return DatabaseUtil.get(new ItemReturn() {
			public Integer process(final Handle h) {
				return h.createQuery("SELECT user_key FROM profound_user WHERE :field = :data")
						.bind("field", field)
						.bind("data", data)
						.map(IntegerMapper.FIRST)
						.first();
			}
		});
	}

	private static void setUserPointer(final Integer key, final String field, final Object data) {
		DatabaseUtil.transaction(new NullReturn() {
			public void process(final Handle h) {
				h.execute("UPDATE profound_user SET :field = :data WHERE user_key = :key", field, data, key);
			}
		});
	}

	static Integer getUserIdByFacebookId(final String facebookId) {
		return UserUtilDatabase.getUserIdByPointer("facebook_id", facebookId);
	}

	static void setFacebookId(final Integer userKey, final String facebookId) {
		UserUtilDatabase.setUserPointer(userKey, "facebook_id", facebookId);
	}

	static Integer getUserIdByGoogleId(final String googleId) {
		return UserUtilDatabase.getUserIdByPointer("google_id", googleId);
	}

	static void setGoogleId(final Integer userId, final String googleId) {
		UserUtilDatabase.setUserPointer(userId, "google_id", googleId);
	}

	static Integer getUserIdByTwitterId(final Long twitterId) {
		return UserUtilDatabase.getUserIdByPointer("twitter_id", twitterId);
	}

	static void setTwitterId(final Integer userId, final Long twitterId) {
		UserUtilDatabase.setUserPointer(userId, "twitter_id", twitterId);
	}

	static Integer getUserIdByLinkedInId(final String linkedInId) {
		return UserUtilDatabase.getUserIdByPointer("linkedinid", linkedInId);
	}

	static void setLinkedInId(final Integer userId, final String linkedInId) {
		UserUtilDatabase.setUserPointer(userId, "linkedinid", linkedInId);
	}

	static Integer getUserIdByIntercomId(final String intercomId) {
		return UserUtilDatabase.getUserIdByPointer("intercom_id", intercomId);
	}

	static void setIntercomId(final Integer userId, final String intercomId) {
		UserUtilDatabase.setUserPointer(userId, "intercom_id", intercomId);
	}

	static Integer getUserIdByEmail(final String email) {
		return DatabaseUtil.get(new ItemReturn() {
			public Integer process(final Handle h) {
				Integer i = h.createQuery("SELECT user_key FROM profound_user_email WHERE lower(email_address) = :email AND validated_on IS NOT NULL")
						.bind("email", email.toLowerCase())
						.map(IntegerMapper.FIRST)
						.first();
				if (i == null) {
					i = h.createQuery("SELECT profound_user.user_key FROM profound_user WHERE lower(email) = :email ")
						.bind("email", email.toLowerCase())
						.map(IntegerMapper.FIRST)
						.first();
				}
				return i;
			}
		});
	}

	static void addEmail(final Integer userId, final String email) {
		DatabaseUtil.transaction(new NullReturn() {
			public void process(final Handle h) {
				h.execute("UPDATE profound_user SET email = ? WHERE user_key = ?", email, userId);
				h.execute("UPDATE profound_user SET primary_email = ? WHERE user_key = ? AND primary_email IS NULL", email, userId);
			}
		});
	}

	static void setPassword(final Integer userId, final String encrypted) {
		DatabaseUtil.transaction(new NullReturn() {
			public void process(final Handle h) {
				h.execute("UPDATE profound_user SET encrypted_password = ? WHERE user_key = ?", encrypted, userId);
			}
		});
	}

	static Integer add() {
		return UserUtilDatabase.add(new AuthenticationRequest(), new AuthenticationResponse());
	}

	static Integer add(final AuthenticationRequest request, final AuthenticationResponse response) {
		UserUtilDatabase.addResponseInformation(request, response);
		final Integer userId = DatabaseUtil.get(new ItemReturn() {
			public Integer process(final Handle h) {
				Integer userId = h.createStatement("INSERT INTO profound_user " +
						"(primary_email_address, email, first_name, last_name, name, created_on, last_seen, facebook_id, google_id, linkedin_id, twitter_id, encrypted_password)" +
						"VALUES (:email, :email, :firstName, :lastName, :name, :date, :date, :facebookId, :googleId, :linkedInId, :twitterId, :password)")
						.bind("email", StringUtils.trim(request.emailAddress))
						.bind("firstName", StringUtils.trim(request.firstName))
						.bind("lastName", StringUtils.trim(request.lastName))
						.bind("name", StringUtils.trim(request.name))
						.bind("date", new Date())
						.bind("facebookId", response.facebookUser == null ? null : response.facebookUser.getId())
						.bind("googleId", response.googleUser == null ? null : response.googleUser.getId())
						.bind("linkedInId", response.linkedInUser == null ? null : response.linkedInUser.id)
						.bind("twitterId", response.twitterUser == null ? null : response.twitterUser.getId())
						.bind("password", request.password == null ? null : PasswordUtil.encryptPassword(request.password))
						.executeAndReturnGeneratedKeys(IntegerMapper.FIRST)
						.first();
				return userId;
			}
		});
		if (StringUtils.isNotBlank(request.emailAddress)) {
			User user = new User()
					.setEmail(request.emailAddress)
					.setUserId(userId.toString());
			final User saved = IntercomUtil.create(user);
			if (user != null && saved != null) {
				RedisUtil.redis(new WithJedisNull() {
					public void process(final Jedis jedis) {
						jedis.hset("users:" + userId, "intercom", saved.getId());
					}
				});
			}
		}
		return userId;
	}

	static void addResponseInformation(final AuthenticationRequest request, final AuthenticationResponse response) {
		if (request.firstName == null) {
			request.firstName = response.getFirstName();
		}
		if (request.lastName == null) {
			request.lastName = response.getLastName();
		}
		if (request.name == null) {
			request.name = response.getName();
		}
	}

	////////////////////////////////////////////////////////////////////////////////////////////////////
	// Deleting

	static void remove(final Integer userId) {
		DatabaseUtil.transaction(new NullReturn() {
			public void process(final Handle h) {
				switch (ProfoundConfiguration.storage.roles) {
					case DATABASE:
						removeAllRoles(h, userId);
						break;
					case REDIS:
						UserUtilRedis.removeAllRoles(userId);
						break;
					default:
						throw new ProteaException("Unexpected role storage configuration");
				}
				h.execute("DELETE FROM profound_user WHERE user_key = ?", userId);
				h.execute("DELETE FROM profound_user_email WHERE user_key = ?", userId);
			}
		});
	}

	////////////////////////////////////////////////////////////////////////////////////////////////////
	// Roles

	static void addRole(final Integer userKey, final String role) {
		DatabaseUtil.transaction(new NullReturn() {
			public void process(final Handle h) {
				h.execute("INSERT INTO profound_user_role(user_key, role) VALUES (?, ?)", userKey, role);
			}
		});
	}

	static void removeRole(final Integer userKey, final String role) {
		DatabaseUtil.transaction(new NullReturn() {
			public void process(final Handle h) {
				h.execute("DELETE FROM profound_user_role WHERE user_key = ? AND role = ?", userKey, role);
			}
		});
	}

	static Set getUserIdsInRole(final String role) {
		Set set = new HashSet();
		DatabaseUtil.get(new ItemReturn>() {
			@SuppressWarnings("unchecked")
			public Set process(final Handle h) {
				return h.createQuery("SELECT user_key FROM profound_user_role WHERE user_key = :user_key AND role = :role")
						.bind("role", role)
						.map(IntegerMapper.FIRST)
						.list(Set.class);
			}
		});
		return set;
	}

	static boolean isUserInRole(final Integer userKey, final String role) {
		return DatabaseUtil.get(new ItemReturn() {
			public Boolean process(final Handle h) {
				return 1 == h.createQuery("SELECT 1 FROM profound_user_role WHERE user_key = :user_key AND role = :role")
						.bind("user_key", userKey)
						.bind("role", role)
						.map(IntegerMapper.FIRST)
						.first();
			}
		});
	}

	static void removeAllRoles(final Handle h, final Integer userKey) {
		h.execute("DELETE FROM profound_user_role WHERE user_key = ?", userKey);
	}

	static void removeAllRoles(final Integer userKey) {
		DatabaseUtil.transaction(new NullReturn() {
			public void process(final Handle h) {
				removeAllRoles(h, userKey);
			}
		});
	}
}