/*
* DBAuthService.java
*
* Created on July 2, 2007, 10:57 PM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package org.atomojo.app.db;
import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.TreeMap;
import java.util.UUID;
import org.atomojo.app.auth.AuthCredentials;
import org.atomojo.app.auth.AuthException;
import org.atomojo.app.auth.AuthService;
import org.atomojo.app.auth.User;
/**
*
* @author alex
*/
public class DBAuthService extends DBConnectionPool implements AuthService
{
Map<String,User> users;
/** Creates a new instance of DBAuthService */
public DBAuthService()
{
super(null,null);
users = new TreeMap<String,User>();
}
static final int STATEMENT_CREATE_GROUP = 1000;
static final int STATEMENT_FIND_GROUP_ID = 1001;
static final int STATEMENT_DELETE_GROUP_MEMBERS = 1002;
static final int STATEMENT_DELETE_GROUP = 1003;
static final int STATEMENT_FIND_AUTHOR_ID = 1004;
static final int STATEMENT_CREATE_AUTHOR = 1005;
static final int STATEMENT_CREATE_GROUP_MEMBER = 1006;
static final int STATEMENT_DELETE_GROUP_MEMBER = 1007;
static final int STATEMENT_LAST_ID_FROM_AUTHOR = 1008;
static final int STATEMENT_AUTHOR_GROUPS = 1009;
static final int STATEMENT_AUTHOR_BY_ALIAS = 1010;
static final int STATEMENT_ALL_AUTHORS = 1011;
static final int STATEMENT_DELETE_AUTHOR = 1012;
static final int STATEMENT_UPDATE_AUTHOR = 1013;
static final int STATEMENT_UPDATE_PASSWORD = 1014;
static final int STATEMENT_AUTHENTICATE = 1015;
protected void prepare(DBConnection dbConnection)
throws SQLException
{
dbConnection.addStatement(STATEMENT_CREATE_GROUP,"insert into atom.groups (name) values (?)");
dbConnection.addStatement(STATEMENT_FIND_GROUP_ID,"select id from atom.groups where name=?");
dbConnection.addStatement(STATEMENT_DELETE_GROUP_MEMBERS,"delete from atom.group_members where group_ref=?");
dbConnection.addStatement(STATEMENT_DELETE_GROUP,"delete from atom.groups where id=?");
dbConnection.addStatement(STATEMENT_FIND_AUTHOR_ID,"select id from atom.author where alias=?");
dbConnection.addStatement(STATEMENT_CREATE_AUTHOR, "insert into atom.author (alias,uuid,name,email,password_md5,created,valid) values (?,?,?,?,?,?,?)");
dbConnection.addStatement(STATEMENT_CREATE_GROUP_MEMBER, "insert into atom.group_members (author_ref,group_ref) values (?,?)");
dbConnection.addStatement(STATEMENT_DELETE_GROUP_MEMBER, "delete from atom.group_members where group_ref=? and author_ref=?");
dbConnection.addStatement(STATEMENT_LAST_ID_FROM_AUTHOR,"select identity_val_local() from atom.author");
dbConnection.addStatement(STATEMENT_AUTHOR_GROUPS,"select name from atom.groups,atom.group_members where atom.group_members.author_ref=? and atom.group_members.group_ref=atom.groups.id");
dbConnection.addStatement(STATEMENT_AUTHOR_BY_ALIAS,"select id,uuid,name,email,created,valid from atom.author where alias=?");
dbConnection.addStatement(STATEMENT_ALL_AUTHORS,"select id,alias,uuid,name,email,created,valid from atom.author");
dbConnection.addStatement(STATEMENT_DELETE_AUTHOR,"delete from atom.author where alias=?");
dbConnection.addStatement(STATEMENT_UPDATE_AUTHOR,"update atom.author set name=?,email=? where alias=?");
dbConnection.addStatement(STATEMENT_UPDATE_PASSWORD, "update atom.author set password_md5=? where alias=?");
dbConnection.addStatement(STATEMENT_AUTHENTICATE,"select id,uuid,name,email from atom.author where alias=? and password_md5=?");
}
public void init(Properties props)
{
String name = props.getProperty("database");
String path = props.getProperty("dir");
setConnection(name,new File(path));
}
public void release(ResultSet r)
throws SQLException
{
if (r!=null) {
r.close();
}
}
public void release(PreparedStatement statement)
throws SQLException
{
if (statement!=null) {
statement.clearParameters();
}
}
public boolean createGroup(AuthCredentials cred,String name)
throws AuthException
{
try {
PreparedStatement s = null;
DBConnection dbConnection = getConnection();
try {
s = dbConnection.getStatement(STATEMENT_CREATE_GROUP);
s.setString(1,name);
int count = s.executeUpdate();
return count==1;
} catch (SQLException ex) {
throw new AuthException("Cannot create group "+name+" due to SQL error.",ex);
} finally {
release(s);
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
public boolean deleteGroup(AuthCredentials cred,String name)
throws AuthException
{
try {
DBConnection dbConnection = getConnection();
try {
int groupId = lookupInternalId(STATEMENT_FIND_GROUP_ID,name);
if (groupId<0) {
throw new AuthException("Group "+name+" cannot be found.");
}
PreparedStatement s = dbConnection.getStatement(STATEMENT_DELETE_GROUP_MEMBERS);
s.setInt(1,groupId);
s.executeUpdate();
release(s);
s = dbConnection.getStatement(STATEMENT_DELETE_GROUP);
s.setInt(1,groupId);
int count = s.executeUpdate();
release(s);
return count>0;
} catch (SQLException ex) {
throw new AuthException("Cannot delete group "+name,ex);
} finally {
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
public boolean addUserToGroup(AuthCredentials cred,String alias,String name)
throws AuthException
{
try {
DBConnection dbConnection = getConnection();
try {
int userId = lookupInternalId(STATEMENT_FIND_AUTHOR_ID,alias);
int groupId = lookupInternalId(STATEMENT_FIND_GROUP_ID,name);
if (groupId<0) {
throw new SQLException("Group "+name+" cannot be found.");
}
PreparedStatement s = dbConnection.getStatement(STATEMENT_CREATE_GROUP_MEMBER);
s.setInt(1,userId);
s.setInt(2,groupId);
int count = s.executeUpdate();
release(s);
if (count!=1) {
return false;
}
} catch (SQLException ex) {
throw new AuthException("Cannot add user "+alias+" to group "+name+" due to SQL error.",ex);
} finally {
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
return true;
}
public boolean removeUserFromGroup(AuthCredentials cred,String alias,String name)
throws AuthException
{
try {
DBConnection dbConnection = getConnection();
try {
int userId = lookupInternalId(STATEMENT_FIND_AUTHOR_ID,alias);
if (userId<0) {
throw new AuthException("User "+alias+" cannot be found.");
}
int groupId = lookupInternalId(STATEMENT_FIND_GROUP_ID,name);
if (groupId<0) {
throw new AuthException("Group "+name+" cannot be found.");
}
PreparedStatement s = dbConnection.getStatement(STATEMENT_DELETE_GROUP_MEMBER);
s.setInt(1,groupId);
s.setInt(2,userId);
int count = s.executeUpdate();
release(s);
return count>0;
} catch (SQLException ex) {
throw new AuthException("Cannot delete user "+alias+" from group "+name,ex);
} finally {
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
public User createUser(AuthCredentials auth,String alias,String name,String email,String password)
throws AuthException
{
UUID uid = UUID.randomUUID();
try {
String md5Password = User.md5Password(password);
DBConnection dbConnection = getConnection();
try {
PreparedStatement s = dbConnection.getStatement(STATEMENT_CREATE_AUTHOR);
Timestamp tstamp = new Timestamp((new Date()).getTime());
s.setString(1, alias);
s.setString(2,uid.toString());
if (name==null) {
s.setNull(3,Types.VARCHAR);
} else {
s.setString(3,name);
}
if (email==null) {
s.setNull(4,Types.VARCHAR);
} else {
s.setString(4,email);
}
s.setString(5,md5Password);
s.setTimestamp(6,tstamp);
s.setBoolean(7,true);
int count = s.executeUpdate();
if (count!=1) {
throw new SQLException("Cannot insert user "+alias+" into DB.");
}
release(s);
s = dbConnection.getStatement(STATEMENT_LAST_ID_FROM_AUTHOR);
ResultSet r = s.executeQuery();
int id = -1;
if (r.next()) {
id = r.getInt(1);
}
release(r);
release(s);
List<String> empty = Collections.emptyList();
User u = new User(alias,uid,name,email,empty);
synchronized (users) {
users.put(alias,u);
}
return u;
} catch (SQLException ex) {
throw new AuthException("Cannot create user "+alias+" due to SQL error.",ex);
} finally {
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
} catch (java.security.NoSuchAlgorithmException ex) {
throw new AuthException(ex.getMessage());
}
}
protected List<String> getGroups(int id)
throws SQLException
{
DBConnection dbConnection = getConnection();
List<String> names = new ArrayList<String>();
try {
PreparedStatement s = dbConnection.getStatement(STATEMENT_AUTHOR_GROUPS);
s.setInt(1,id);
ResultSet r = s.executeQuery();
while (r.next()) {
names.add(r.getString(1));
}
release(r);
return names;
} finally {
release(dbConnection);
}
}
public User getUser(AuthCredentials cred,String alias)
throws AuthException
{
User u = users.get(alias);
if (u==null) {
try {
DBConnection dbConnection = getConnection();
try {
PreparedStatement s = dbConnection.getStatement(STATEMENT_AUTHOR_BY_ALIAS);
s.setString(1,alias);
ResultSet r = s.executeQuery();
if (r.next()) {
int id = r.getInt(1);
u = new User(alias,UUID.fromString(r.getString(2)),r.getString(3),r.getString(4),getGroups(id));
synchronized (users) {
users.put(alias,u);
}
}
release(r);
release(s);
} catch (SQLException ex) {
throw new AuthException("Cannot get user "+alias+" due to SQL error.",ex);
} finally {
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
return u;
}
public Iterator<User> getUsers(AuthCredentials cred)
throws AuthException
{
try {
DBConnection dbConnection = getConnection();
try {
PreparedStatement s = dbConnection.getStatement(STATEMENT_ALL_AUTHORS);
return new DBIterator<User>(s.executeQuery(),new ResultConstructor<User>() {
public User newInstance(ResultSet r)
throws SQLException
{
int id = r.getInt(1);
String alias = r.getString(2);
User u = users.get(alias);
if (u==null) {
u = new User(alias,UUID.fromString(r.getString(3)),r.getString(4),r.getString(5),getGroups(id));
synchronized (users) {
users.put(u.getAlias(),u);
}
}
return u;
}
},this,dbConnection);
} catch (SQLException ex) {
release(dbConnection);
throw new AuthException("Cannot execute queries connection.",ex);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
public boolean deleteUser(AuthCredentials cred,String alias)
throws AuthException
{
try {
DBConnection dbConnection = getConnection();
try {
PreparedStatement s = dbConnection.getStatement(STATEMENT_DELETE_AUTHOR);
s.setString(1,alias);
int count = s.executeUpdate();
release(s);
synchronized (users) {
users.remove(alias);
}
return count>0;
} catch (SQLException ex) {
throw new AuthException("Cannot delete user "+alias+" from the database.",ex);
} finally {
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
public boolean updateUser(AuthCredentials cred,String alias,String name,String email)
throws AuthException
{
try {
DBConnection dbConnection = getConnection();
try {
PreparedStatement s = dbConnection.getStatement(STATEMENT_UPDATE_AUTHOR);
if (name==null) {
s.setNull(1, Types.VARCHAR);
} else {
s.setString(1,name);
}
if (email==null) {
s.setNull(2, Types.VARCHAR);
} else {
s.setString(2,email);
}
s.setString(3,alias);
int count = s.executeUpdate();
release(s);
synchronized (users) {
users.remove(alias);
}
return count>0;
} catch (SQLException ex) {
throw new AuthException("Cannot update user "+alias+" in the database.",ex);
} finally {
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
public boolean setPassword(AuthCredentials cred,String alias,String password)
throws AuthException
{
try {
DBConnection dbConnection = getConnection();
try {
PreparedStatement s = dbConnection.getStatement(STATEMENT_UPDATE_PASSWORD);
s.setString(1, User.md5Password(password));
s.setString(2,alias);
int count = s.executeUpdate();
release(s);
return count>0;
} catch (java.security.NoSuchAlgorithmException ex) {
throw new AuthException(ex.getMessage());
} catch (SQLException ex) {
throw new AuthException("Cannot update user "+alias+" in the database.",ex);
} finally {
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
public User authenticate(String alias,String password)
throws AuthException
{
try {
DBConnection dbConnection = getConnection();
PreparedStatement s = null;
ResultSet r = null;
try {
s = dbConnection.getStatement(STATEMENT_AUTHENTICATE);
s.setString(1,alias);
s.setString(2,User.md5Password(password));
r = s.executeQuery();
User u = null;
if (r.next()) {
int id = r.getInt(1);
UUID uuid = UUID.fromString(r.getString(2));
String name = r.getString(3);
u = users.get(alias);
if (u==null) {
u = new User(alias,uuid,name,r.getString(4),getGroups(id));
synchronized (users) {
users.put(u.getAlias(),u);
}
}
}
return u;
} catch (java.security.NoSuchAlgorithmException ex) {
throw new AuthException(ex.getMessage());
} catch (SQLException ex) {
throw new AuthException("Cannot get user "+alias+" from database.",ex);
} finally {
release(r);
release(s);
release(dbConnection);
}
} catch (SQLException ex) {
throw new AuthException("Cannot get database connection.",ex);
}
}
public User verifySession(String session)
throws AuthException
{
return null;
}
protected int lookupInternalId(int statementId,String value)
throws SQLException
{
PreparedStatement s = null;
ResultSet r = null;
DBConnection dbConnection = getConnection();
try {
s = dbConnection.getStatement(statementId);
s.setString(1,value);
r = s.executeQuery();
if (r.next()) {
return r.getInt(1);
}
return -1;
} finally {
release(r);
release(s);
release(dbConnection);
}
}
}