/*
* DB.java
*
* Created on July 30, 2007, 5:45 PM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package org.atomojo.auth.service.db;
import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.infoset.xml.DocumentLoader;
import org.infoset.xml.XMLException;
import org.infoset.xml.sax.SAXDocumentLoader;
import org.milowski.db.DB;
import org.milowski.db.DBCache;
import org.milowski.db.DBConnection;
import org.milowski.db.DBIterator;
import org.milowski.db.DBQueryHandler;
import org.milowski.db.DBResultConstructor;
import org.milowski.db.DBUpdateHandler;
import org.milowski.db.Slot;
/**
*
* @author alex
*/
public class AuthDB extends DB
{
static Logger LOG = Logger.getLogger(AuthDB.class.getName());
static public UUID ROOT_ROLE = UUID.fromString("1434b5c6-5e52-4096-9312-f1060b1a38b5");
static public UUID QUERY_ROLE = UUID.fromString("696a9356-2a27-4347-8bcf-2db548e4cd67");
static public UUID REALM_ROOT_ROLE = UUID.fromString("497d7f39-f9d2-468b-91c8-34ceca927f78");
static public UUID EDITOR_ROLE = UUID.fromString("f046e2e3-4513-40b5-9db9-69ce057fa1f9");
static public UUID SUPERUSER_PERMISSION = UUID.fromString("fce0c8c9-35a2-4720-bf44-daf5790cdaf0");
static public UUID REALM_SUPERUSER_PERMISSION = UUID.fromString("6a7986f8-a5e9-4afb-b221-c8d65a92927b");
static public UUID ACROSS_REALM_PERMISSION = UUID.fromString("f5176c38-af47-4788-9dfc-1cccc25291cf");
static public UUID QUERY_PERMISSION = UUID.fromString("b5289ab7-b767-43a7-9039-b6b5735a2ac9");
/*
static String getProperty(String [] spec) {
String value = System.getProperty(spec[0]);
if (value==null) {
value = spec[1];
}
return value;
}
public static AuthDB createDB(File dir,String name)
{
AuthDB db = new AuthDB(new File(dir,name));
return db;
}
*/
static int EXPIRATION = 5*60*100;
static {
String value = System.getProperty("atomojo.db.cache.expiration");
if (value!=null) {
EXPIRATION = Integer.parseInt(value);
}
}
class RealmUsers {
Map<Realm,DBCache<UUID,RealmUser>> realms = new TreeMap<Realm,DBCache<UUID,RealmUser>>();
DBCache<UUID,RealmUser> get(Realm realm) {
DBCache<UUID,RealmUser> cache = realms.get(realm);
if (cache==null) {
synchronized (realms) {
cache = new RealmUserCache(realm);
realms.put(realm,cache);
}
}
return cache;
}
void remove(Realm realm) {
synchronized (realms) {
realms.remove(realm);
}
}
}
class RealmUserCache extends DBCache<UUID,RealmUser> {
Realm realm;
RealmUserCache(Realm realm) {
super(100,EXPIRATION,true);
this.realm = realm;
}
protected RealmUser fetch(final UUID id)
throws SQLException
{
final User user = userCache.get(id);
if (user!=null) {
final Slot<RealmUser> u = new Slot<RealmUser>();
final DBConnection connection = getConnection();
try {
connection.query(REALM_USER_BY_USER, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setInt(2,user.getId());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
final int rid = set.getInt(1);
final Slot<String> alias = new Slot<String>();
connection.query(REALM_USER_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,rid);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
alias.set(set.getString(1));
}
}
});
u.set(new RealmUser(AuthDB.this,rid,realm,user,alias.get(),set.getString(2),set.getString(3)));
}
}
});
} finally {
release(connection);
}
return u.get();
} else {
return null;
}
}
protected RealmUser fetchById(final Integer id)
throws SQLException
{
final Slot<RealmUser> u = new Slot<RealmUser>();
final DBConnection connection = getConnection();
try {
connection.query(REALM_USER, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,id.intValue());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
final Slot<String> alias = new Slot<String>();
connection.query(REALM_USER_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,id);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
alias.set(set.getString(1));
}
}
});
User user = userCache.get(set.getInt(1));
u.set(new RealmUser(AuthDB.this,id,realm,user,alias.get(),set.getString(2),set.getString(3)));
}
}
});
} finally {
release(connection);
}
return u.get();
}
protected RealmUser fetchByName(final String name)
throws SQLException
{
final Slot<RealmUser> u = new Slot<RealmUser>();
final DBConnection connection = getConnection();
try {
connection.query(REALM_USER_BY_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setString(2,name);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
User user = userCache.get(set.getInt(2));
u.set(new RealmUser(AuthDB.this,set.getInt(1),realm,user,name,set.getString(3),set.getString(4)));
}
}
});
} finally {
release(connection);
}
if (u.get()==null) {
final User user = userCache.getNamed(name);
if (user!=null) {
final DBConnection connection2 = getConnection();
try {
connection2.query(REALM_USER_BY_INHERITED_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setInt(2,user.getId());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
u.set(new RealmUser(AuthDB.this,set.getInt(1),realm,user,null,set.getString(2),set.getString(3)));
}
}
});
} finally {
release(connection2);
}
}
}
return u.get();
}
protected int getId(RealmUser r) {
return r.getId();
}
protected UUID getFacet(RealmUser r) {
return r.getUser().getUUID();
}
protected String getName(RealmUser r) {
return r.getAlias();
}
};
class RealmGroups {
Map<Realm,DBCache<UUID,Group>> realms = new TreeMap<Realm,DBCache<UUID,Group>>();
DBCache<UUID,Group> get(Realm realm) {
DBCache<UUID,Group> cache = realms.get(realm);
if (cache==null) {
synchronized (realms) {
cache = new GroupCache(realm);
realms.put(realm,cache);
}
}
return cache;
}
void remove(Realm realm) {
synchronized (realms) {
realms.remove(realm);
}
}
void clear() {
synchronized (realms) {
realms.clear();
}
}
}
class GroupCache extends DBCache<UUID,Group> {
Realm realm;
GroupCache(Realm realm) {
super(100,EXPIRATION,true);
this.realm = realm;
}
protected Group fetch(final UUID id)
throws SQLException
{
final Slot<Group> g = new Slot<Group>();
DBConnection connection = getConnection();
try {
connection.query(GROUP_BY_UUID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setString(2,id.toString());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
g.set(new Group(AuthDB.this,set.getInt(1),realm,id,set.getString(2)));
}
}
});
} finally {
release(connection);
}
return g.get();
}
protected Group fetchById(final Integer id)
throws SQLException
{
final Slot<Group> g = new Slot<Group>();
DBConnection connection = getConnection();
try {
connection.query(GROUP_BY_ID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setInt(2,id);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
g.set(new Group(AuthDB.this,set.getInt(1),realm,UUID.fromString(set.getString(3)),set.getString(2)));
}
}
});
} finally {
release(connection);
}
return g.get();
}
protected Group fetchByName(final String name)
throws SQLException
{
final Slot<Group> g = new Slot<Group>();
DBConnection connection = getConnection();
try {
connection.query(GROUP_BY_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setString(2,name);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
g.set(new Group(AuthDB.this,set.getInt(1),realm,UUID.fromString(set.getString(3)),set.getString(2)));
}
}
});
} finally {
release(connection);
}
return g.get();
}
protected int getId(Group g) {
return g.getId();
}
protected UUID getFacet(Group g) {
return g.getUUID();
}
protected String getName(Group g) {
return g.getAlias();
}
};
DBCache<UUID,Permission> permissionCache;
DBCache<UUID,Role> roleCache;
DBCache<UUID,Realm> realmCache;
DBCache<UUID,User> userCache;
RealmGroups realmGroupCaches;
RealmUsers realmUserCaches;
/** Creates a new instance of DB */
public AuthDB(String name,String location)
{
super(name,location);
getSchemaMap().put("auth", this.getClass().getResource("authdb.sql"));
permissionCache = new DBCache<UUID,Permission>(-1,EXPIRATION,true) {
protected Permission fetch(final UUID id)
throws SQLException
{
final Slot<Permission> p = new Slot<Permission>();
DBConnection connection = getConnection();
try {
connection.query(PERMISSION_BY_UUID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,id.toString());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
p.set(new Permission(AuthDB.this,set.getInt(1),set.getString(2),id));
}
}
});
} finally {
release(connection);
}
return p.get();
}
protected Permission fetchById(final Integer id)
throws SQLException
{
final Slot<Permission> p = new Slot<Permission>();
DBConnection connection = getConnection();
try {
connection.query(PERMISSION_BY_ID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,id);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
p.set(new Permission(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
}
}
});
} finally {
release(connection);
}
return p.get();
}
protected Permission fetchByName(final String name)
throws SQLException
{
final Slot<Permission> p = new Slot<Permission>();
DBConnection connection = getConnection();
try {
connection.query(PERMISSION_BY_NAME, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,name);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
p.set(new Permission(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
}
}
});
} finally {
release(connection);
}
return p.get();
}
protected int getId(Permission p) {
return p.getId();
}
protected UUID getFacet(Permission p) {
return p.getUUID();
}
protected String getName(Permission r) {
return r.getName();
}
};
roleCache = new DBCache<UUID,Role>(-1,EXPIRATION,true) {
protected Role fetch(final UUID id)
throws SQLException
{
final Slot<Role> r = new Slot<Role>();
DBConnection connection = getConnection();
try {
connection.query(ROLE_BY_UUID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,id.toString());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
r.set(new Role(AuthDB.this,set.getInt(1),set.getString(2),id));
}
}
});
} finally {
release(connection);
}
return r.get();
}
protected Role fetchById(final Integer id)
throws SQLException
{
final Slot<Role> r = new Slot<Role>();
DBConnection connection = getConnection();
try {
connection.query(ROLE_BY_ID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,id);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
r.set(new Role(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
}
}
});
} finally {
release(connection);
}
return r.get();
}
protected Role fetchByName(final String name)
throws SQLException
{
final Slot<Role> r = new Slot<Role>();
DBConnection connection = getConnection();
try {
connection.query(ROLE_BY_NAME, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,name);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
r.set(new Role(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
}
}
});
} finally {
release(connection);
}
return r.get();
}
protected int getId(Role r) {
return r.getId();
}
protected UUID getFacet(Role r) {
return r.getUUID();
}
protected String getName(Role r) {
return r.getName();
}
};
realmCache = new DBCache<UUID,Realm>(-1,EXPIRATION,true) {
protected Realm fetch(final UUID id)
throws SQLException
{
final Slot<Realm> r = new Slot<Realm>();
DBConnection connection = getConnection();
try {
connection.query(REALM_BY_UUID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,id.toString());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
r.set(new Realm(AuthDB.this,set.getInt(1),set.getString(2),id));
}
}
});
} finally {
release(connection);
}
return r.get();
}
protected Realm fetchById(final Integer id)
throws SQLException
{
final Slot<Realm> r = new Slot<Realm>();
DBConnection connection = getConnection();
try {
connection.query(REALM_BY_ID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,id);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
r.set(new Realm(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
}
}
});
} finally {
release(connection);
}
return r.get();
}
protected Realm fetchByName(final String name)
throws SQLException
{
final Slot<Realm> r = new Slot<Realm>();
DBConnection connection = getConnection();
try {
connection.query(REALM_BY_NAME, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,name);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
r.set(new Realm(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
}
}
});
} finally {
release(connection);
}
return r.get();
}
protected int getId(Realm r) {
return r.getId();
}
protected UUID getFacet(Realm r) {
return r.getUUID();
}
protected String getName(Realm r) {
return r.getName();
}
};
userCache = new DBCache<UUID,User>(100,EXPIRATION,true) {
protected User fetch(final UUID id)
throws SQLException
{
final Slot<User> u = new Slot<User>();
DBConnection connection = getConnection();
try {
connection.query(USER_BY_UUID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,id.toString());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
int uid = set.getInt(1);
String alias = getUserAlias(uid);
u.set(new User(AuthDB.this,uid,id,alias,set.getString(2),set.getString(3)));
}
}
});
} finally {
release(connection);
}
return u.get();
}
protected User fetchById(final Integer id)
throws SQLException
{
final Slot<User> u = new Slot<User>();
DBConnection connection = getConnection();
try {
connection.query(USER_BY_ID, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,id.intValue());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
int uid = set.getInt(1);
String alias = getUserAlias(uid);
u.set(new User(AuthDB.this,uid,UUID.fromString(set.getString(4)),alias,set.getString(2),set.getString(3)));
}
}
});
} finally {
release(connection);
}
return u.get();
}
protected User fetchByName(final String name)
throws SQLException
{
final Slot<User> u = new Slot<User>();
DBConnection connection = getConnection();
try {
connection.query(USER_BY_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,name);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
int uid = set.getInt(1);
String alias = getUserAlias(uid);
u.set(new User(AuthDB.this,uid,UUID.fromString(set.getString(4)),alias,set.getString(2),set.getString(3)));
}
}
});
} finally {
release(connection);
}
return u.get();
}
protected int getId(User r) {
return r.getId();
}
protected UUID getFacet(User r) {
return r.getUUID();
}
protected String getName(User r) {
return r.getAlias();
}
};
realmGroupCaches = new RealmGroups();
realmUserCaches = new RealmUsers();
}
public Permission createPermission(final String name,final UUID uuid)
throws SQLException
{
DBConnection connection = getConnection();
try {
int id = connection.create(CREATE_PERMISSION,LAST_ID_FROM_PERMISSIONS, new DBUpdateHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,name);
s.setString(2,uuid.toString());
}
});
return id<0 ? null : permissionCache.get(id);
} finally {
release(connection);
}
}
public Permission getPermission(String name)
throws SQLException
{
return permissionCache.getNamed(name);
}
public Permission getPermission(UUID uuid)
throws SQLException
{
return permissionCache.get(uuid);
}
public Iterator<Permission> getPermissions()
throws SQLException
{
final Slot<Iterator<Permission>> result = new Slot<Iterator<Permission>>();
final DBConnection connection = getConnection();
try {
connection.query(PERMISSIONS, new DBQueryHandler() {
public boolean shouldClose() { return false; }
public void onResults(ResultSet set)
throws SQLException
{
result.set(new DBIterator<Permission>(set,new DBResultConstructor<Permission>() {
public Permission newInstance(ResultSet set)
throws SQLException
{
return permissionCache.get(set.getInt(1));
}
},AuthDB.this,connection));
}
});
} finally {
release(connection);
}
return result.get();
}
public Role createRole(final String name,final UUID uuid)
throws SQLException
{
DBConnection connection = getConnection();
try {
int id = connection.create(CREATE_ROLE,LAST_ID_FROM_ROLES, new DBUpdateHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,name);
s.setString(2,uuid.toString());
}
});
return id<0 ? null : roleCache.get(id);
} finally {
release(connection);
}
}
public Role getRole(String name)
throws SQLException
{
return roleCache.getNamed(name);
}
public Role getRole(UUID uuid)
throws SQLException
{
return roleCache.get(uuid);
}
public Iterator<Role> getRoles()
throws SQLException
{
final Slot<Iterator<Role>> result = new Slot<Iterator<Role>>();
final DBConnection connection = getConnection();
try {
connection.query(ROLES, new DBQueryHandler() {
public boolean shouldClose() { return false; }
public void onResults(ResultSet set)
throws SQLException
{
result.set(new DBIterator<Role>(set,new DBResultConstructor<Role>() {
public Role newInstance(ResultSet set)
throws SQLException
{
return roleCache.get(set.getInt(1));
}
},AuthDB.this,connection));
}
});
} finally {
release(connection);
}
return result.get();
}
public Realm createRealm(final String name,final UUID uuid)
throws SQLException
{
DBConnection connection = getConnection();
try {
int id = connection.create(CREATE_REALM,LAST_ID_FROM_REALMS, new DBUpdateHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,name);
s.setString(2,uuid.toString());
}
});
return id<0 ? null : realmCache.get(id);
} finally {
release(connection);
}
}
public Realm getRealm(String name)
throws SQLException
{
return realmCache.getNamed(name);
}
public Realm getRealm(UUID uuid)
throws SQLException
{
return realmCache.get(uuid);
}
public Iterator<Realm> getRealms()
throws SQLException
{
final Slot<Iterator<Realm>> result = new Slot<Iterator<Realm>>();
final DBConnection connection = getConnection();
try {
connection.query(REALMS, new DBQueryHandler() {
public boolean shouldClose() { return false; }
public void onResults(ResultSet set)
throws SQLException
{
result.set(new DBIterator<Realm>(set,new DBResultConstructor<Realm>() {
public Realm newInstance(ResultSet set)
throws SQLException
{
return realmCache.get(set.getInt(1));
}
},AuthDB.this,connection));
}
});
} finally {
release(connection);
}
return result.get();
}
public boolean isUserAliasAvailable(final String alias)
throws SQLException
{
final Slot<Boolean> found = new Slot<Boolean>(false);
if (alias!=null) {
DBConnection connection = getConnection();
try {
connection.query(USER_ALIAS_EXISTS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,alias);
}
public void onResults(ResultSet set)
throws SQLException
{
found.set(set.next());
}
});
} finally {
release(connection);
}
}
return !found.get();
}
public User createUser(final UUID uuid,final String alias,final String name,final String email)
throws SQLException
{
if (!isUserAliasAvailable(alias)) {
LOG.info("Alias "+alias+" is not available");
return null;
}
final Timestamp tstamp = new Timestamp((new Date()).getTime());
DBConnection connection = getConnection();
try {
int id = connection.create(CREATE_USER,LAST_ID_FROM_USERS, new DBUpdateHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,uuid.toString());
if (name==null) {
s.setNull(2,Types.VARCHAR);
} else {
s.setString(2,name);
}
if (email==null) {
s.setNull(3,Types.VARCHAR);
} else {
s.setString(3,email);
}
s.setTimestamp(4,tstamp);
s.setBoolean(5,true);
}
});
User user = userCache.get(id);
try {
if (alias!=null) {
if (!user.changeAlias(alias)) {
LOG.info("Cannot change to "+alias);
user.delete();
return null;
}
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE,"Cannot reserve alias for user "+uuid.toString(),ex);
user.delete();
return null;
}
return user;
} finally {
release(connection);
}
}
public User getUser(String name)
throws SQLException
{
return userCache.getNamed(name);
}
public User getUser(UUID uuid)
throws SQLException
{
return userCache.get(uuid);
}
public User findUserByEmail(final String email)
throws SQLException
{
final Slot<User> user = new Slot<User>();
final DBConnection connection = getConnection();
try {
connection.query(USER_BY_EMAIL, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1, email);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
user.set(userCache.get(set.getInt(1)));
}
}
});
} finally {
release(connection);
}
return user.get();
}
public Iterator<User> getUsers()
throws SQLException
{
return getUsers(false);
}
public Iterator<User> getUsers(boolean all)
throws SQLException
{
final Slot<Iterator<User>> result = new Slot<Iterator<User>>();
final DBConnection connection = getConnection();
try {
connection.query(all ? USERS : GLOBAL_USERS, new DBQueryHandler() {
public boolean shouldClose() { return false; }
public void onResults(ResultSet set)
throws SQLException
{
result.set(new DBIterator<User>(set,new DBResultConstructor<User>() {
public User newInstance(ResultSet set)
throws SQLException
{
return userCache.get(set.getInt(1));
}
},AuthDB.this,connection));
}
});
} finally {
release(connection);
}
return result.get();
}
public Group createGroup(final Realm realm,final UUID uuid,final String alias)
throws SQLException
{
DBCache<UUID,Group> cache = realmGroupCaches.get(realm);
if (cache.getNamed(alias)!=null) {
return null;
}
DBConnection connection = getConnection();
try {
int id = connection.create(CREATE_GROUP,LAST_ID_FROM_GROUPS, new DBUpdateHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setString(2,uuid.toString());
s.setString(3,alias);
s.setString(4,realm.getId()+"-"+alias);
}
});
return cache.get(id);
} finally {
release(connection);
}
}
public Group getGroup(Realm realm,String name)
throws SQLException
{
return realmGroupCaches.get(realm).getNamed(name);
}
public Group getGroup(Realm realm,UUID uuid)
throws SQLException
{
return realmGroupCaches.get(realm).get(uuid);
}
public Iterator<Group> getGroups(final Realm realm)
throws SQLException
{
final Slot<Iterator<Group>> result = new Slot<Iterator<Group>>();
final DBCache<UUID,Group> cache = realmGroupCaches.get(realm);
final DBConnection connection = getConnection();
try {
connection.query(GROUPS, new DBQueryHandler() {
public boolean shouldClose() { return false; }
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
}
public void onResults(ResultSet set)
throws SQLException
{
result.set(new DBIterator<Group>(set,new DBResultConstructor<Group>() {
public Group newInstance(ResultSet set)
throws SQLException
{
return cache.get(set.getInt(1));
}
},AuthDB.this,connection));
}
});
} finally {
release(connection);
}
return result.get();
}
public boolean isRealmUserAliasAvailable(final Realm realm,final User user,final String alias)
throws SQLException
{
// Check for realm conflicts with specified alias
final Slot<Boolean> found = new Slot<Boolean>(false);
if (alias!=null) {
// check realm against alias
DBConnection connection = getConnection();
try {
connection.query(REALM_USER_BY_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setString(2,alias);
}
public void onResults(ResultSet set)
throws SQLException
{
found.set(set.next());
}
});
if (found.get()) {
return false;
}
// check inherited aliases
connection.query(REALM_USER_HAS_INHERITED_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setString(2,alias);
}
public void onResults(ResultSet set)
throws SQLException
{
found.set(set.next());
}
});
if (found.get()) {
return false;
}
} finally {
release(connection);
}
}
//
if (alias==null) {
// Check inherited alias against local aliases
DBConnection connection = getConnection();
try {
connection.query(REALM_USER_BY_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setString(2,user.getAlias());
}
public void onResults(ResultSet set)
throws SQLException
{
found.set(set.next());
}
});
if (found.get()) {
return false;
}
} finally {
release(connection);
}
}
return true;
}
public RealmUser createRealmUser(final Realm realm,final User user,final String alias,final String name,final String email)
throws SQLException
{
if (!isRealmUserAliasAvailable(realm,user,alias)) {
return null;
}
final Timestamp tstamp = new Timestamp((new Date()).getTime());
DBConnection connection = getConnection();
try {
int id = connection.create(CREATE_REALM_USER,LAST_ID_FROM_REALM_USERS, new DBUpdateHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setInt(2,user.getId());
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.setTimestamp(5,tstamp);
s.setBoolean(6,true);
}
});
RealmUser ruser = realmUserCaches.get(realm).get(id);
if (alias!=null) {
try {
if (!ruser.changeAlias(alias)) {
ruser.delete();
return null;
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE,"Cannot reserve alias for realm user "+user.getUUID().toString(),ex);
ruser.delete();
return null;
}
}
return ruser;
} finally {
release(connection);
}
}
public RealmUser getRealmUser(Realm realm,String name)
throws SQLException
{
///argh need to map by realm
return realmUserCaches.get(realm).getNamed(name);
}
public RealmUser getRealmUser(Realm realm,UUID uuid)
throws SQLException
{
return realmUserCaches.get(realm).get(uuid);
}
public RealmUser findRealmUserByEmail(final Realm realm,final String email)
throws SQLException
{
final Slot<RealmUser> ruser = new Slot<RealmUser>();
final DBConnection connection = getConnection();
try {
connection.query(REALM_USER_BY_EMAIL, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setString(2, email);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
ruser.set(realmUserCaches.get(realm).get(set.getInt(1)));
}
}
});
if (ruser.get()!=null) {
return ruser.get();
}
final User user = findUserByEmail(email);
if (user!=null) {
connection.query(REALM_USER_BY_USER, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
s.setInt(2, user.getId());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
ruser.set(realmUserCaches.get(realm).get(set.getInt(1)));
}
}
});
}
return ruser.get();
} finally {
release(connection);
}
}
public RealmUser findRealmUser(final Realm realm,User user)
throws SQLException
{
DBCache<UUID,RealmUser> cache = realmUserCaches.get(realm);
RealmUser ruser = cache.get(user.getUUID());
return ruser;
}
public Iterator<RealmUser> getRealmUsers(final Realm realm)
throws SQLException
{
final DBCache<UUID,RealmUser> cache = realmUserCaches.get(realm);
final Slot<Iterator<RealmUser>> result = new Slot<Iterator<RealmUser>>();
final DBConnection connection = getConnection();
try {
connection.query(REALM_USERS, new DBQueryHandler() {
public boolean shouldClose() { return false; }
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,realm.getId());
}
public void onResults(ResultSet set)
throws SQLException
{
result.set(new DBIterator<RealmUser>(set,new DBResultConstructor<RealmUser>() {
public RealmUser newInstance(ResultSet set)
throws SQLException
{
return cache.get(set.getInt(1));
}
},AuthDB.this,connection));
}
});
} finally {
release(connection);
}
return result.get();
}
/**
* @param realm The realm of the user (may be null)
* @param session The session id of the authentication
*/
public User.Authenticated isAuthenticated(final Realm realm,final UUID session)
throws SQLException
{
final Slot<User.Authenticated> retval = new Slot<User.Authenticated>();
DBConnection connection = getConnection();
try {
connection.query(USER_SESSION, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,session.toString());
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
Timestamp created = set.getTimestamp(2);
Timestamp expiration = set.getTimestamp(3);
User user = userCache.get(set.getInt(4));
User.Authenticated auth = new User.Authenticated(AuthDB.this,set.getInt(1),session,expiration,created,user,realm);
retval.set(auth);
}
}
});
} finally {
release(connection);
}
User.Authenticated auth = retval.get();
if (auth!=null && auth.isExpired()) {
LOG.info("Session "+session.toString()+" has expired, deleting...");
// the session has expired
auth.delete();
return null;
} else {
return auth;
}
}
public void expireSession(final UUID session)
throws SQLException
{
DBConnection connection = getConnection();
try {
connection.update(DELETE_SESSION, new DBUpdateHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setString(1,session.toString());
}
});
} finally {
release(connection);
}
}
protected String getUserAlias(final int id)
throws SQLException
{
final Slot<String> alias = new Slot<String>();
DBConnection connection = getConnection();
try {
connection.query(USER_ALIAS, new DBQueryHandler() {
public void prepare(PreparedStatement s)
throws SQLException
{
s.setInt(1,id);
}
public void onResults(ResultSet set)
throws SQLException
{
if (set.next()) {
alias.set(set.getString(1));
}
}
});
} finally {
release(connection);
}
return alias.get();
}
static int DELETE_USER_ROLES_BY_USER = 100;
static int DELETE_AUTHENTICATION_BY_USER = 200;
static int CREATE_AUTHENTICATION = 201;
static int DELETE_USER_ALIAS_BY_USER = 300;
static int DELETE_REALM_USER = 400;
static int DELETE_REALM_USER_ALIAS = 401;
static int DELETE_REALM_USER_GROUPS = 402;
static int REALM_USERS_BY_USER = 403;
static int CREATE_REALM_USER_ALIAS = 404;
static int UPDATE_REALM_USER_ALIAS = 405;
static int REALM_USER = 406;
static int REALM_USER_BY_ALIAS = 408;
static int REALM_USER_ALIAS = 409;
static int REALM_USER_BY_USER = 410;
static int REALM_USER_BY_REALM = 411;
static int DELETE_AUTHENTICATED_BY_REALM = 412;
static int DELETE_REALM = 413;
static int DELETE_AUTHENTICATED = 414;
static int CHANGE_REALM_USER_NAME = 415;
static int CHANGE_REALM_USER_EMAIL = 416;
static int REALM_USER_AUTHENTICATED = 417;
static int DELETE_REALM_USER_AUTHENTICATION = 418;
static int REALM_USER_BY_INHERITED_ALIAS = 419;
static int CREATE_REALM_USER = 420;
static int LAST_ID_FROM_REALM_USERS = 421;
static int REALM_USER_BY_EMAIL = 422;
static int REALM_USERS = 423;
static int REALM_USER_HAS_INHERITED_ALIAS = 424;
static int DELETE_USER = 500;
static int USER_HAS_ROLE = 501;
static int DELETE_USER_ROLE = 502;
static int ADD_USER_ROLE = 503;
static int USER_ROLES = 504;
static int DELETE_USER_ALIAS = 505;
static int CHANGE_USER_ALIAS = 506;
static int CREATE_USER_ALIAS = 507;
static int CHANGE_USER_NAME = 508;
static int CHANGE_USER_EMAIL = 509;
static int USER_AUTHENTICATED = 510;
static int DELETE_USER_AUTHENTICATION = 511;
static int DELETE_SESSION = 512;
static int DELETE_EXPIRED_SESSIONS = 513;
static int CREATE_AUTHENTICATED = 514;
static int CHECK_PASSWORD = 515;
static int ENCRYPTED_PASSWORD = 516;
static int USER_BY_ID = 517;
static int USER_BY_UUID = 518;
static int USER_BY_ALIAS = 519;
static int USER_ALIAS = 520;
static int USER_SESSION = 521;
static int USER_ALIAS_EXISTS = 522;
static int CREATE_USER = 523;
static int LAST_ID_FROM_USERS = 524;
static int USER_BY_EMAIL = 525;
static int USERS = 526;
static int GLOBAL_USERS = 527;
static int REALM_BY_ID = 600;
static int REALM_BY_UUID = 601;
static int REALM_BY_NAME = 602;
static int CREATE_REALM = 603;
static int LAST_ID_FROM_REALMS = 604;
static int REALMS = 605;
static int DELETE_PERMISSION = 700;
static int DELETE_ROLE_PERMISSIONS_BY_PERMISSION = 701;
static int PERMISSION_BY_ID = 702;
static int PERMISSION_BY_NAME = 703;
static int PERMISSION_BY_UUID = 704;
static int CREATE_PERMISSION = 705;
static int LAST_ID_FROM_PERMISSIONS = 706;
static int PERMISSIONS = 707;
static int DELETE_ROLE = 800;
static int DELETE_ROLE_PERMISSIONS = 801;
static int DELETE_GROUP_ROLES_BY_ROLE = 802;
static int DELETE_USER_ROLES_BY_ROLE = 803;
static int ROLE_PERMISSIONS = 804;
static int CREATE_ROLE_PERMISSION = 805;
static int DELETE_ROLE_PERMISSION = 806;
static int ROLE_BY_ID = 807;
static int ROLE_BY_UUID = 808;
static int ROLE_BY_NAME = 809;
static int CREATE_ROLE = 810;
static int LAST_ID_FROM_ROLES = 811;
static int ROLES = 812;
static int DELETE_GROUP = 900;
static int DELETE_GROUP_MEMBERS = 901;
static int DELETE_GROUP_ROLES = 902;
static int GROUP_ROLES = 903;
static int GROUP_MEMBERS = 904;
static int CREATE_GROUP_ROLE = 905;
static int DELETE_GROUP_ROLE = 906;
static int CREATE_GROUP_MEMBER = 907;
static int DELETE_GROUP_MEMBER = 908;
static int GROUP_MEMBER = 910;
static int GROUP_BY_REALM = 911;
static int GROUP_BY_REALM_USER = 912;
static int GROUP_BY_ID = 913;
static int GROUP_BY_ALIAS = 914;
static int GROUP_BY_UUID = 915;
static int CREATE_GROUP = 916;
static int LAST_ID_FROM_GROUPS = 917;
static int GROUPS = 918;
protected void prepare(DBConnection dbConnection)
throws SQLException
{
dbConnection.addStatement(DELETE_USER_ROLES_BY_USER, "delete from auth.user_roles where user_ref=?");
dbConnection.addStatement(DELETE_AUTHENTICATION_BY_USER,"delete from auth.authentication where user_ref=?");
dbConnection.addStatement(CREATE_AUTHENTICATION,"insert into auth.authentication (user_ref,password_md5) values (?,?)");
dbConnection.addStatement(DELETE_USER_ALIAS_BY_USER,"delete from auth.users_alias where user_ref=?");
dbConnection.addStatement(DELETE_REALM_USER,"delete from auth.realm_users where id=?");
dbConnection.addStatement(DELETE_REALM_USER_ALIAS,"delete from auth.realm_users_alias where realm_user_ref=?");
dbConnection.addStatement(DELETE_REALM_USER_GROUPS,"delete from auth.group_members where realm_user_ref=?");
dbConnection.addStatement(REALM_USERS_BY_USER,"select id,realm_ref from auth.realm_users where user_ref=?");
dbConnection.addStatement(CREATE_REALM_USER_ALIAS,"insert into auth.realm_users_alias (realm_user_ref,alias,idalias) values (?,?,?)");
dbConnection.addStatement(UPDATE_REALM_USER_ALIAS,"update auth.realm_users_alias set alias=?, idalias=? where realm_user_ref=?");
dbConnection.addStatement(REALM_USER_BY_USER,"select id,name,email from auth.realm_users where realm_ref=? and user_ref=?");
dbConnection.addStatement(REALM_USER_BY_ALIAS,"select u.id,user_ref,name,email from auth.realm_users u,auth.realm_users_alias where realm_ref=? and alias=? and realm_user_ref=u.id");
dbConnection.addStatement(REALM_USER_ALIAS,"select alias from auth.realm_users_alias where realm_user_ref=?");
dbConnection.addStatement(REALM_USER,"select user_ref,name,email from auth.realm_users where id=?");
dbConnection.addStatement(REALM_USER_BY_REALM,"select id from auth.realm_users where realm_ref=?");
dbConnection.addStatement(DELETE_AUTHENTICATED_BY_REALM,"delete from auth.authenticated where realm_ref=?");
dbConnection.addStatement(DELETE_REALM,"delete from auth.realms where id=?");
dbConnection.addStatement(DELETE_AUTHENTICATED,"delete from auth.authenticated where id=?");
dbConnection.addStatement(CHANGE_REALM_USER_NAME,"update auth.realm_users set name=? where id=?");
dbConnection.addStatement(CHANGE_REALM_USER_EMAIL,"update auth.realm_users set email=? where id=?");
dbConnection.addStatement(REALM_USER_AUTHENTICATED,"select id,created,expiration from auth.authenticated where user_ref=? and realm_ref=? and uuid=?");
dbConnection.addStatement(DELETE_REALM_USER_AUTHENTICATION,"delete from auth.authenticated where user_ref=? and realm_ref=? and uuid=?");
dbConnection.addStatement(CREATE_AUTHENTICATED,"insert into auth.authenticated (user_ref,realm_ref,uuid,created,expiration) values(?,?,?,?,?)");
dbConnection.addStatement(CHECK_PASSWORD,"select id from auth.authentication where user_ref=? and password_md5=?");
dbConnection.addStatement(ENCRYPTED_PASSWORD,"select password_md5 from auth.authentication where user_ref=?");
dbConnection.addStatement(REALM_USER_BY_INHERITED_ALIAS, "select id,name,email from auth.realm_users u where realm_ref=? and user_ref=? and not exists(select * from auth.realm_users_alias where realm_user_ref=u.id)");
dbConnection.addStatement(CREATE_REALM_USER,"insert into auth.realm_users (realm_ref,user_ref,name,email,created,valid) values (?,?,?,?,?,?)");
dbConnection.addStatement(LAST_ID_FROM_REALM_USERS,"select identity_val_local() from auth.realm_users");
dbConnection.addStatement(REALM_USER_BY_EMAIL,"select id from auth.realm_users where realm_ref=? and email=?");
dbConnection.addStatement(REALM_USERS,"select id from auth.realm_users where realm_ref=?");
dbConnection.addStatement(REALM_USER_HAS_INHERITED_ALIAS, "select ru.id from auth.realm_users ru, auth.users u, auth.users_alias a where realm_ref=? and not exists(select * from auth.realm_users_alias where realm_user_ref=u.id) and ru.user_ref=u.id and a.user_ref=u.id and a.alias=?");
dbConnection.addStatement(DELETE_USER,"delete from auth.users where id=?");
dbConnection.addStatement(USER_HAS_ROLE,"select id from auth.user_roles where user_ref=? and role_ref=?");
dbConnection.addStatement(DELETE_USER_ROLE,"delete from auth.user_roles where user_ref=? and role_ref=?");
dbConnection.addStatement(DELETE_USER_ROLE,"delete from auth.user_roles where user_ref=? and role_ref=?");
dbConnection.addStatement(ADD_USER_ROLE,"insert into auth.user_roles (user_ref,role_ref) values (?,?)");
dbConnection.addStatement(USER_ROLES,"select role_ref from auth.user_roles where user_ref=?");
dbConnection.addStatement(DELETE_USER_ALIAS,"delete from auth.users_alias where user_ref=?");
dbConnection.addStatement(CHANGE_USER_ALIAS,"update auth.users_alias set alias=?,idalias=? where user_ref=?");
dbConnection.addStatement(CREATE_USER_ALIAS,"insert into auth.users_alias (user_ref,alias,idalias) values (?,?,?)");
dbConnection.addStatement(CHANGE_USER_NAME,"update auth.users set name=? where id=?");
dbConnection.addStatement(CHANGE_USER_EMAIL,"update auth.users set email=? where id=?");
dbConnection.addStatement(USER_AUTHENTICATED,"select id,created,expiration from auth.authenticated where user_ref=? and realm_ref is null and uuid=?");
dbConnection.addStatement(DELETE_USER_AUTHENTICATION,"delete from auth.authenticated where user_ref=? and realm_ref is null and uuid=?");
dbConnection.addStatement(DELETE_SESSION,"delete from auth.authenticated where uuid=?");
dbConnection.addStatement(DELETE_EXPIRED_SESSIONS,"delete from auth.authenticated where user_ref=? and expiration<?");
dbConnection.addStatement(USER_BY_ID,"select id,name,email,uuid from auth.users where id=?");
dbConnection.addStatement(USER_BY_UUID,"select id,name,email,uuid from auth.users where uuid=?");
dbConnection.addStatement(USER_BY_ALIAS,"select u.id,name,email,uuid from auth.users u,auth.users_alias where alias=? and u.id=user_ref");
dbConnection.addStatement(USER_ALIAS,"select alias from auth.users_alias where user_ref=?");
dbConnection.addStatement(USER_SESSION,"select id,created,expiration,user_ref from auth.authenticated where uuid=?");
dbConnection.addStatement(USER_ALIAS_EXISTS, "select id from auth.users_alias where alias=?");
dbConnection.addStatement(CREATE_USER,"insert into auth.users (uuid,name,email,created,valid) values (?,?,?,?,?)");
dbConnection.addStatement(LAST_ID_FROM_USERS,"select identity_val_local() from auth.users");
dbConnection.addStatement(USER_BY_EMAIL,"select id from auth.users where email=?");
dbConnection.addStatement(USERS,"select id from auth.users");
dbConnection.addStatement(GLOBAL_USERS,"select u.id from auth.users u, auth.users_alias a where u.id=a.user_ref and a.alias is not null");
dbConnection.addStatement(REALM_BY_ID,"select id,name,uuid from auth.realms where id=?");
dbConnection.addStatement(REALM_BY_UUID,"select id,name,uuid from auth.realms where uuid=?");
dbConnection.addStatement(REALM_BY_NAME,"select id,name,uuid from auth.realms where name=?");
dbConnection.addStatement(CREATE_REALM,"insert into auth.realms (name,uuid) values (?,?)");
dbConnection.addStatement(LAST_ID_FROM_REALMS,"select identity_val_local() from auth.realms");
dbConnection.addStatement(REALMS,"select id from auth.realms");
dbConnection.addStatement(DELETE_PERMISSION,"delete from auth.permissions where id=?");
dbConnection.addStatement(DELETE_ROLE_PERMISSIONS_BY_PERMISSION,"delete from auth.role_permissions where permission_ref=?");
dbConnection.addStatement(PERMISSION_BY_ID,"select id,name,uuid from auth.permissions where id=?");
dbConnection.addStatement(PERMISSION_BY_NAME,"select id,name,uuid from auth.permissions where name=?");
dbConnection.addStatement(PERMISSION_BY_UUID,"select id,name,uuid from auth.permissions where uuid=?");
dbConnection.addStatement(CREATE_PERMISSION,"insert into auth.permissions (name,uuid) values (?,?)");
dbConnection.addStatement(LAST_ID_FROM_PERMISSIONS,"select identity_val_local() from auth.permissions");
dbConnection.addStatement(PERMISSIONS,"select id from auth.permissions");
dbConnection.addStatement(DELETE_ROLE,"delete from auth.roles where id=?");
dbConnection.addStatement(DELETE_ROLE_PERMISSIONS,"delete from auth.role_permissions where role_ref=?");
dbConnection.addStatement(DELETE_GROUP_ROLES_BY_ROLE,"delete from auth.group_roles where role_ref=?");
dbConnection.addStatement(DELETE_USER_ROLES_BY_ROLE,"delete from auth.user_roles where role_ref=?");
dbConnection.addStatement(ROLE_PERMISSIONS,"select permission_ref from auth.role_permissions where role_ref=?");
dbConnection.addStatement(CREATE_ROLE_PERMISSION,"insert into auth.role_permissions (role_ref,permission_ref) values (?,?)");
dbConnection.addStatement(DELETE_ROLE_PERMISSION,"delete from auth.role_permissions where role_ref=? and permission_ref=?");
dbConnection.addStatement(ROLE_BY_ID,"select id,name,uuid from auth.roles where id=?");
dbConnection.addStatement(ROLE_BY_UUID,"select id,name,uuid from auth.roles where uuid=?");
dbConnection.addStatement(ROLE_BY_NAME,"select id,name,uuid from auth.roles where name=?");
dbConnection.addStatement(CREATE_ROLE,"insert into auth.roles (name,uuid) values (?,?)");
dbConnection.addStatement(LAST_ID_FROM_ROLES,"select identity_val_local() from auth.roles");
dbConnection.addStatement(ROLES,"select id from auth.roles");
dbConnection.addStatement(DELETE_GROUP,"delete from auth.groups where id=?");
dbConnection.addStatement(DELETE_GROUP_MEMBERS,"delete from auth.group_members where group_ref=?");
dbConnection.addStatement(DELETE_GROUP_ROLES,"delete from auth.group_roles where group_ref=?");
dbConnection.addStatement(GROUP_ROLES,"select role_ref from auth.group_roles where group_ref=?");
dbConnection.addStatement(GROUP_MEMBERS,"select realm_user_ref from auth.group_members where group_ref=?");
dbConnection.addStatement(CREATE_GROUP_ROLE,"insert into auth.group_roles (group_ref,role_ref) values (?,?)");
dbConnection.addStatement(DELETE_GROUP_ROLE,"delete from auth.group_roles where group_ref=? and role_ref=?");
dbConnection.addStatement(CREATE_GROUP_MEMBER,"insert into auth.group_members (group_ref,realm_user_ref) values (?,?)");
dbConnection.addStatement(DELETE_GROUP_MEMBER,"delete from auth.group_members where group_ref=? and realm_user_ref=?");
dbConnection.addStatement(GROUP_MEMBER,"select id from auth.group_members where group_ref=? and realm_user_ref=?");
dbConnection.addStatement(GROUP_BY_REALM,"select id from auth.groups where realm_ref=?");
dbConnection.addStatement(GROUP_BY_REALM_USER,"select g.id,g.alias from auth.groups g,auth.group_members where realm_ref=? and g.id=group_ref and realm_user_ref=?");
dbConnection.addStatement(GROUP_BY_ID,"select id,alias,uuid from auth.groups where realm_ref=? and id=?");
dbConnection.addStatement(GROUP_BY_UUID,"select id,alias,uuid from auth.groups where realm_ref=? and uuid=?");
dbConnection.addStatement(GROUP_BY_ALIAS,"select id,alias,uuid from auth.groups where realm_ref=? and alias=?");
dbConnection.addStatement(CREATE_GROUP,"insert into auth.groups (realm_ref,uuid,alias,idalias) values (?,?,?,?)");
dbConnection.addStatement(LAST_ID_FROM_GROUPS,"select identity_val_local() from auth.groups");
dbConnection.addStatement(GROUPS,"select id from auth.groups where realm_ref=?");
}
protected void loadSchema(String name,Connection connection,URL sqlLocation)
throws java.io.IOException,java.sql.SQLException
{
super.loadSchema(name, connection, sqlLocation);
if (name.equals("auth")) {
URL defaultDB = this.getClass().getResource("default-db.xml");
if (defaultDB!=null) {
try {
DocumentLoader loader = new SAXDocumentLoader();
RestoreDestination restore = new RestoreDestination(LOG,this);
loader.generate(defaultDB.toURI(), restore);
} catch (XMLException ex) {
throw new IOException(ex.getMessage());
} catch (URISyntaxException ex) {
throw new IOException(ex.getMessage());
}
}
}
}
}