2 * Copyright (c) 2014, 2015 Hewlett-Packard Development Company, L.P. and others. All rights reserved.
4 * This program and the accompanying materials are made available under the
5 * terms of the Eclipse Public License v1.0 which accompanies this distribution,
6 * and is available at http://www.eclipse.org/legal/epl-v10.html
9 package org.opendaylight.aaa.idm.persistence;
13 * @author peter.mellquist@hp.com
17 import java.sql.Connection;
18 import java.sql.DatabaseMetaData;
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.sql.SQLException;
22 import java.sql.Statement;
23 import java.util.ArrayList;
24 import java.util.List;
26 import org.opendaylight.aaa.idm.IdmLightApplication;
27 import org.opendaylight.aaa.idm.model.Grant;
28 import org.opendaylight.aaa.idm.model.Grants;
29 import org.slf4j.Logger;
30 import org.slf4j.LoggerFactory;
32 public class GrantStore {
33 private static Logger logger = LoggerFactory.getLogger(GrantStore.class);
34 protected Connection dbConnection = null;
35 protected final static String SQL_ID = "grantid";
36 protected final static String SQL_TENANTID = "domainid";
37 protected final static String SQL_USERID = "userid";
38 protected final static String SQL_ROLEID = "roleid";
40 protected Connection getDBConnect() throws StoreException {
41 dbConnection = IdmLightApplication.getConnection(dbConnection);
45 protected void dbClean() throws StoreException, SQLException{
46 Connection c = dbConnect();
47 String sql = "delete from GRANTS where true";
48 c.createStatement().execute(sql);
52 protected Connection dbConnect() throws StoreException {
55 conn = getDBConnect();
57 catch (StoreException se) {
61 DatabaseMetaData dbm = conn.getMetaData();
62 ResultSet rs = dbm.getTables(null, null, "GRANTS", null);
64 debug("grants Table already exists");
68 logger.info("grants Table does not exist, creating table");
69 Statement stmt = null;
70 stmt = conn.createStatement();
71 String sql = "CREATE TABLE GRANTS " +
72 "(grantid VARCHAR(128) PRIMARY KEY," +
73 "domainid VARCHAR(128) NOT NULL, " +
74 "userid VARCHAR(128) NOT NULL, " +
75 "roleid VARCHAR(128) NOT NULL)" ;
76 stmt.executeUpdate(sql);
80 catch (SQLException sqe) {
81 throw new StoreException("Cannot connect to database server "+ sqe);
89 protected void dbClose() {
90 if (dbConnection != null)
93 dbConnection.close ();
96 logger.error("Cannot close Database Connection " + e);
102 protected void finalize () throws Throwable {
107 protected Grant rsToGrant(ResultSet rs) throws SQLException {
108 Grant grant = new Grant();
110 grant.setGrantid(rs.getString(SQL_ID));
111 grant.setDomainid(rs.getString(SQL_TENANTID));
112 grant.setUserid(rs.getString(SQL_USERID));
113 grant.setRoleid(rs.getString(SQL_ROLEID));
115 catch (SQLException sqle) {
116 logger.error( "SQL Exception : " + sqle);
122 public Grants getGrants(String did, String uid) throws StoreException {
123 Grants grants = new Grants();
124 List<Grant> grantList = new ArrayList<Grant>();
125 Connection conn = dbConnect();
127 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM grants WHERE domainid = ? AND userid = ?");
128 pstmt.setString(1, did);
129 pstmt.setString(2, uid);
130 debug("query string: " + pstmt.toString());
131 ResultSet rs = pstmt.executeQuery();
133 Grant grant = rsToGrant(rs);
134 grantList.add(grant);
139 catch (SQLException s) {
140 throw new StoreException("SQL Exception : " + s);
145 grants.setGrants(grantList);
149 public Grants getGrants(int uid) throws StoreException {
150 Grants grants = new Grants();
151 List<Grant> grantList = new ArrayList<Grant>();
152 Connection conn = dbConnect();
154 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM GRANTS WHERE userid = ? ");
155 pstmt.setInt(1, uid);
156 debug("query string: " + pstmt.toString());
157 ResultSet rs = pstmt.executeQuery();
159 Grant grant = rsToGrant(rs);
160 grantList.add(grant);
165 catch (SQLException s) {
166 throw new StoreException("SQL Exception : " + s);
171 grants.setGrants(grantList);
176 public Grant getGrant(String id) throws StoreException {
177 Connection conn = dbConnect();
179 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM GRANTS WHERE grantid = ? ");
180 pstmt.setString(1, id);
181 debug("query string: " + pstmt.toString());
182 ResultSet rs = pstmt.executeQuery();
184 Grant grant = rsToGrant(rs);
195 catch (SQLException s) {
196 throw new StoreException("SQL Exception : " + s);
203 public Grant getGrant(String did,String uid,String rid) throws StoreException {
204 Connection conn = dbConnect();
206 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM GRANTS WHERE domainid = ? AND userid = ? AND roleid = ? ");
207 pstmt.setString(1, did);
208 pstmt.setString(2, uid);
209 pstmt.setString(3, rid);
210 debug("query string: " + pstmt.toString());
211 ResultSet rs = pstmt.executeQuery();
213 Grant grant = rsToGrant(rs);
224 catch (SQLException s) {
225 throw new StoreException("SQL Exception : " + s);
233 public Grant createGrant(Grant grant) throws StoreException {
234 Connection conn = dbConnect();
236 String query = "insert into grants (grantid,domainid,userid,roleid) values(?,?,?,?)";
237 PreparedStatement statement = conn.prepareStatement(query);
238 statement.setString(1,grant.getUserid()+"@"+grant.getDomainid()+"@"+grant.getRoleid());
239 statement.setString(2,grant.getDomainid());
240 statement.setString(3,grant.getUserid());
241 statement.setString(4,grant.getRoleid());
242 int affectedRows = statement.executeUpdate();
243 if (affectedRows == 0) {
244 throw new StoreException("Creating grant failed, no rows affected.");
248 catch (SQLException s) {
249 throw new StoreException("SQL Exception : " + s);
256 public Grant deleteGrant(Grant grant) throws StoreException {
257 Grant savedGrant = this.getGrant(grant.getGrantid());
258 if (savedGrant==null) {
262 Connection conn = dbConnect();
264 String query = "DELETE FROM GRANTS WHERE grantid = ?";
265 PreparedStatement statement = conn.prepareStatement(query);
266 statement.setString(1, savedGrant.getGrantid());
267 int deleteCount = statement.executeUpdate(query);
268 debug("deleted " + deleteCount + " records");
272 catch (SQLException s) {
273 throw new StoreException("SQL Exception : " + s);
280 private static final void debug(String msg) {
281 if (logger.isDebugEnabled()) {