Source Code Cross Referenced for DbAuthzGroupService.java in  » ERP-CRM-Financial » sakai » org » sakaiproject » authz » impl » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » ERP CRM Financial » sakai » org.sakaiproject.authz.impl 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


0001:        /**********************************************************************************
0002:         * $URL: https://source.sakaiproject.org/svn/authz/tags/sakai_2-4-1/authz-impl/impl/src/java/org/sakaiproject/authz/impl/DbAuthzGroupService.java $
0003:         * $Id: DbAuthzGroupService.java 22827 2007-03-17 19:00:11Z ggolden@umich.edu $
0004:         ***********************************************************************************
0005:         *
0006:         * Copyright (c) 2003, 2004, 2005, 2006 2007 The Sakai Foundation.
0007:         * 
0008:         * Licensed under the Educational Community License, Version 1.0 (the "License"); 
0009:         * you may not use this file except in compliance with the License. 
0010:         * You may obtain a copy of the License at
0011:         * 
0012:         *      http://www.opensource.org/licenses/ecl1.php
0013:         * 
0014:         * Unless required by applicable law or agreed to in writing, software 
0015:         * distributed under the License is distributed on an "AS IS" BASIS, 
0016:         * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 
0017:         * See the License for the specific language governing permissions and 
0018:         * limitations under the License.
0019:         *
0020:         **********************************************************************************/package org.sakaiproject.authz.impl;
0021:
0022:        import java.sql.Connection;
0023:        import java.sql.ResultSet;
0024:        import java.sql.SQLException;
0025:        import java.util.Collection;
0026:        import java.util.HashMap;
0027:        import java.util.HashSet;
0028:        import java.util.Iterator;
0029:        import java.util.List;
0030:        import java.util.Map;
0031:        import java.util.Set;
0032:        import java.util.Vector;
0033:
0034:        import org.apache.commons.logging.Log;
0035:        import org.apache.commons.logging.LogFactory;
0036:        import org.sakaiproject.authz.api.AuthzGroup;
0037:        import org.sakaiproject.authz.api.Member;
0038:        import org.sakaiproject.authz.api.Role;
0039:        import org.sakaiproject.db.api.SqlReader;
0040:        import org.sakaiproject.db.api.SqlService;
0041:        import org.sakaiproject.db.api.SqlServiceDeadlockException;
0042:        import org.sakaiproject.javax.PagingPosition;
0043:        import org.sakaiproject.time.api.Time;
0044:        import org.sakaiproject.user.api.UserNotDefinedException;
0045:        import org.sakaiproject.util.BaseDbFlatStorage;
0046:        import org.sakaiproject.util.BaseResourceProperties;
0047:        import org.sakaiproject.util.BaseResourcePropertiesEdit;
0048:        import org.sakaiproject.util.StringUtil;
0049:
0050:        /**
0051:         * <p>
0052:         * DbAuthzGroupService is an extension of the BaseAuthzGroupService with database storage.
0053:         * </p>
0054:         */
0055:        public abstract class DbAuthzGroupService extends BaseAuthzGroupService {
0056:            /** Our log (commons). */
0057:            private static Log M_log = LogFactory
0058:                    .getLog(DbAuthzGroupService.class);
0059:
0060:            /** All the event functions we know exist on the db. */
0061:            protected Collection m_functionCache = new HashSet();
0062:
0063:            /** All the event role names we know exist on the db. */
0064:            protected Collection m_roleNameCache = new HashSet();
0065:
0066:            /** Table name for realms. */
0067:            protected String m_realmTableName = "SAKAI_REALM";
0068:
0069:            /** Table name for realm properties. */
0070:            protected String m_realmPropTableName = "SAKAI_REALM_PROPERTY";
0071:
0072:            /** ID field for realm. */
0073:            protected String m_realmIdFieldName = "REALM_ID";
0074:
0075:            /** AuthzGroup dbid field. */
0076:            protected String m_realmDbidField = "REALM_KEY";
0077:
0078:            /** All "fields" for realm reading. */
0079:            protected String[] m_realmReadFieldNames = {
0080:                    "REALM_ID",
0081:                    "PROVIDER_ID",
0082:                    "(select MAX(ROLE_NAME) from SAKAI_REALM_ROLE where ROLE_KEY = MAINTAIN_ROLE)",
0083:                    "CREATEDBY", "MODIFIEDBY", "CREATEDON", "MODIFIEDON",
0084:                    "REALM_KEY" };
0085:
0086:            /** All "fields" for realm update. */
0087:            protected String[] m_realmUpdateFieldNames = {
0088:                    "REALM_ID",
0089:                    "PROVIDER_ID",
0090:                    "MAINTAIN_ROLE = (select MAX(ROLE_KEY) from SAKAI_REALM_ROLE where ROLE_NAME = ?)",
0091:                    "CREATEDBY", "MODIFIEDBY", "CREATEDON", "MODIFIEDON" };
0092:
0093:            /** All "fields" for realm insert. */
0094:            protected String[] m_realmInsertFieldNames = { "REALM_ID",
0095:                    "PROVIDER_ID", "MAINTAIN_ROLE", "CREATEDBY", "MODIFIEDBY",
0096:                    "CREATEDON", "MODIFIEDON" };
0097:
0098:            /** All "field values" for realm insert. */
0099:            protected String[] m_realmInsertValueNames = {
0100:                    "?",
0101:                    "?",
0102:                    "(select MAX(ROLE_KEY) from SAKAI_REALM_ROLE where ROLE_NAME = ?)",
0103:                    "?", "?", "?", "?" };
0104:
0105:            /**********************************************************************************************************************************************************************************************************************************************************
0106:             * Dependencies
0107:             *********************************************************************************************************************************************************************************************************************************************************/
0108:
0109:            /**
0110:             * @return the ServerConfigurationService collaborator.
0111:             */
0112:            protected abstract SqlService sqlService();
0113:
0114:            /**********************************************************************************************************************************************************************************************************************************************************
0115:             * Configuration
0116:             *********************************************************************************************************************************************************************************************************************************************************/
0117:
0118:            /** If true, we do our locks in the remote database, otherwise we do them here. */
0119:            protected boolean m_useExternalLocks = true;
0120:
0121:            /**
0122:             * Configuration: set the external locks value.
0123:             * 
0124:             * @param value
0125:             *        The external locks value.
0126:             */
0127:            public void setExternalLocks(String value) {
0128:                m_useExternalLocks = new Boolean(value).booleanValue();
0129:            }
0130:
0131:            /** Configuration: to run the ddl on init or not. */
0132:            protected boolean m_autoDdl = false;
0133:
0134:            /**
0135:             * Configuration: to run the ddl on init or not.
0136:             * 
0137:             * @param value
0138:             *        the auto ddl value.
0139:             */
0140:            public void setAutoDdl(String value) {
0141:                m_autoDdl = new Boolean(value).booleanValue();
0142:            }
0143:
0144:            /**********************************************************************************************************************************************************************************************************************************************************
0145:             * Init and Destroy
0146:             *********************************************************************************************************************************************************************************************************************************************************/
0147:
0148:            /**
0149:             * Final initialization, once all dependencies are set.
0150:             */
0151:            public void init() {
0152:                try {
0153:                    // if we are auto-creating our schema, check and create
0154:                    if (m_autoDdl) {
0155:                        sqlService().ddl(this .getClass().getClassLoader(),
0156:                                "sakai_realm");
0157:                        sqlService().ddl(this .getClass().getClassLoader(),
0158:                                "sakai_realm_2_4_0_001");
0159:                    }
0160:
0161:                    super .init();
0162:
0163:                    // pre-cache role and function names
0164:                    cacheRoleNames();
0165:                    cacheFunctionNames();
0166:
0167:                    M_log.info("init(): table: " + m_realmTableName
0168:                            + " external locks: " + m_useExternalLocks);
0169:                } catch (Throwable t) {
0170:                    M_log.warn("init(): ", t);
0171:                }
0172:            }
0173:
0174:            /**********************************************************************************************************************************************************************************************************************************************************
0175:             * BaseAuthzGroupService extensions
0176:             *********************************************************************************************************************************************************************************************************************************************************/
0177:
0178:            /**
0179:             * Construct a Storage object.
0180:             * 
0181:             * @return The new storage object.
0182:             */
0183:            protected Storage newStorage() {
0184:                return new DbStorage();
0185:
0186:            } // newStorage
0187:
0188:            /**
0189:             * Check / assure this role name is defined.
0190:             * 
0191:             * @param name
0192:             *        the role name.
0193:             */
0194:            protected void checkRoleName(String name) {
0195:                if (name == null)
0196:                    return;
0197:                name = name.intern();
0198:
0199:                // check the cache to see if the role name already exists
0200:                if (m_roleNameCache.contains(name))
0201:                    return;
0202:
0203:                // see if we have it in the db
0204:                String statement = "select count(1) from SAKAI_REALM_ROLE where ROLE_NAME = ?";
0205:                Object[] fields = new Object[1];
0206:                fields[0] = name;
0207:
0208:                List results = sqlService().dbRead(statement, fields,
0209:                        new SqlReader() {
0210:                            public Object readSqlResultRecord(ResultSet result) {
0211:                                try {
0212:                                    int count = result.getInt(1);
0213:                                    return new Integer(count);
0214:                                } catch (SQLException ignore) {
0215:                                    return null;
0216:                                }
0217:                            }
0218:                        });
0219:
0220:                boolean rv = false;
0221:                if (!results.isEmpty()) {
0222:                    rv = ((Integer) results.get(0)).intValue() > 0;
0223:                }
0224:
0225:                // write if we didn't find it
0226:                if (!rv) {
0227:                    if ("oracle".equals(sqlService().getVendor())) {
0228:                        statement = "insert into SAKAI_REALM_ROLE (ROLE_KEY, ROLE_NAME) values (SAKAI_REALM_ROLE_SEQ.NEXTVAL, ?)";
0229:                    } else if ("mysql".equals(sqlService().getVendor())) {
0230:                        statement = "insert into SAKAI_REALM_ROLE (ROLE_KEY, ROLE_NAME) values (DEFAULT, ?)";
0231:                    } else
0232:                    // if ("hsqldb".equals(m_sql.getVendor()))
0233:                    {
0234:                        statement = "insert into SAKAI_REALM_ROLE (ROLE_KEY, ROLE_NAME) values (NEXT VALUE FOR SAKAI_REALM_ROLE_SEQ, ?)";
0235:                    }
0236:
0237:                    // write, but if it fails, we don't really care - it will fail if another app server has just written this role name
0238:                    sqlService().dbWriteFailQuiet(null, statement, fields);
0239:                }
0240:
0241:                synchronized (m_roleNameCache) {
0242:                    m_roleNameCache.add(name);
0243:                }
0244:            }
0245:
0246:            /**
0247:             * Read all the role records, caching them
0248:             */
0249:            protected void cacheRoleNames() {
0250:                synchronized (m_roleNameCache) {
0251:                    String statement = "select ROLE_NAME from SAKAI_REALM_ROLE";
0252:                    List results = sqlService().dbRead(statement, null,
0253:                            new SqlReader() {
0254:                                public Object readSqlResultRecord(
0255:                                        ResultSet result) {
0256:                                    try {
0257:                                        String name = result.getString(1);
0258:                                        m_roleNameCache.add(name);
0259:                                    } catch (SQLException ignore) {
0260:                                    }
0261:
0262:                                    return null;
0263:                                }
0264:                            });
0265:                }
0266:            }
0267:
0268:            /**
0269:             * Check / assure this function name is defined.
0270:             * 
0271:             * @param name
0272:             *        the role name.
0273:             */
0274:            protected void checkFunctionName(String name) {
0275:                if (name == null)
0276:                    return;
0277:                name = name.intern();
0278:
0279:                // check the cache to see if the function name already exists
0280:                if (m_functionCache.contains(name))
0281:                    return;
0282:
0283:                // see if we have this on the db
0284:                String statement = "select count(1) from SAKAI_REALM_FUNCTION where FUNCTION_NAME = ?";
0285:                Object[] fields = new Object[1];
0286:                fields[0] = name;
0287:
0288:                List results = sqlService().dbRead(statement, fields,
0289:                        new SqlReader() {
0290:                            public Object readSqlResultRecord(ResultSet result) {
0291:                                try {
0292:                                    int count = result.getInt(1);
0293:                                    return new Integer(count);
0294:                                } catch (SQLException ignore) {
0295:                                    return null;
0296:                                }
0297:                            }
0298:                        });
0299:
0300:                boolean rv = false;
0301:                if (!results.isEmpty()) {
0302:                    rv = ((Integer) results.get(0)).intValue() > 0;
0303:                }
0304:
0305:                // write if we didn't find it
0306:                if (!rv) {
0307:                    if ("oracle".equals(sqlService().getVendor())) {
0308:                        statement = "insert into SAKAI_REALM_FUNCTION (FUNCTION_KEY, FUNCTION_NAME) values (SAKAI_REALM_FUNCTION_SEQ.NEXTVAL, ?)";
0309:                    } else if ("mysql".equals(sqlService().getVendor())) {
0310:                        statement = "insert into SAKAI_REALM_FUNCTION (FUNCTION_KEY, FUNCTION_NAME) values (DEFAULT, ?)";
0311:                    } else
0312:                    // if ("hsqldb".equals(m_sql.getVendor()))
0313:                    {
0314:                        statement = "insert into SAKAI_REALM_FUNCTION (FUNCTION_KEY, FUNCTION_NAME) values (NEXT VALUE FOR SAKAI_REALM_FUNCTION_SEQ, ?)";
0315:                    }
0316:
0317:                    // write, but if it fails, we don't really care - it will fail if another app server has just written this function
0318:                    sqlService().dbWriteFailQuiet(null, statement, fields);
0319:                }
0320:
0321:                // cache the existance of the function name
0322:                synchronized (m_functionCache) {
0323:                    m_functionCache.add(name);
0324:                }
0325:            }
0326:
0327:            /**
0328:             * Read all the function records, caching them
0329:             */
0330:            protected void cacheFunctionNames() {
0331:                synchronized (m_functionCache) {
0332:                    String statement = "select FUNCTION_NAME from SAKAI_REALM_FUNCTION";
0333:                    List results = sqlService().dbRead(statement, null,
0334:                            new SqlReader() {
0335:                                public Object readSqlResultRecord(
0336:                                        ResultSet result) {
0337:                                    try {
0338:                                        String name = result.getString(1);
0339:                                        m_functionCache.add(name);
0340:                                    } catch (SQLException ignore) {
0341:                                    }
0342:
0343:                                    return null;
0344:                                }
0345:                            });
0346:                }
0347:            }
0348:
0349:            /**********************************************************************************************************************************************************************************************************************************************************
0350:             * Storage implementation
0351:             *********************************************************************************************************************************************************************************************************************************************************/
0352:
0353:            /**
0354:             * Covers for the BaseXmlFileStorage, providing AuthzGroup and RealmEdit parameters
0355:             */
0356:            protected class DbStorage extends BaseDbFlatStorage implements 
0357:                    Storage, SqlReader {
0358:                /**
0359:                 * Construct.
0360:                 */
0361:                public DbStorage() {
0362:                    super (m_realmTableName, m_realmIdFieldName,
0363:                            m_realmReadFieldNames, m_realmPropTableName,
0364:                            m_useExternalLocks, null, sqlService());
0365:                    m_reader = this ;
0366:
0367:                    setDbidField(m_realmDbidField);
0368:                    setWriteFields(m_realmUpdateFieldNames,
0369:                            m_realmInsertFieldNames, m_realmInsertValueNames);
0370:
0371:                    setLocking(false);
0372:
0373:                    // setSortField(m_realmSortField, null);
0374:                }
0375:
0376:                public boolean check(String id) {
0377:                    return super .checkResource(id);
0378:                }
0379:
0380:                public AuthzGroup get(String id) {
0381:                    return get(null, id);
0382:                }
0383:
0384:                protected AuthzGroup get(Connection conn, String id) {
0385:                    // read the base
0386:                    BaseAuthzGroup rv = (BaseAuthzGroup) super .getResource(
0387:                            conn, id);
0388:
0389:                    completeGet(conn, rv, false);
0390:
0391:                    return rv;
0392:                }
0393:
0394:                /**
0395:                 * Complete the read process once the basic realm info has been read
0396:                 * 
0397:                 * @param realm
0398:                 *        The real to complete
0399:                 */
0400:                public void completeGet(BaseAuthzGroup realm) {
0401:                    completeGet(null, realm, false);
0402:                }
0403:
0404:                /**
0405:                 * Complete the read process once the basic realm info has been read
0406:                 * 
0407:                 * @param conn
0408:                 *        optional SQL connection to use.
0409:                 * @param realm
0410:                 *        The real to complete.
0411:                 * @param updateProvider
0412:                 *        if true, update and store the provider info.
0413:                 */
0414:                protected void completeGet(Connection conn,
0415:                        final BaseAuthzGroup realm, boolean updateProvider) {
0416:                    if (realm == null)
0417:                        return;
0418:
0419:                    if (!realm.m_lazy)
0420:                        return;
0421:                    realm.m_lazy = false;
0422:
0423:                    // update the db and realm with latest provider
0424:                    if (updateProvider) {
0425:                        refreshAuthzGroup(realm);
0426:                    }
0427:
0428:                    // read the properties
0429:                    if (((BaseResourceProperties) realm.m_properties).isLazy()) {
0430:                        ((BaseResourcePropertiesEdit) realm.m_properties)
0431:                                .setLazy(false);
0432:                        super .readProperties(conn, realm.getKey(),
0433:                                realm.m_properties);
0434:                    }
0435:
0436:                    // read the roles and role functions
0437:                    String sql = "SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME FROM SAKAI_REALM_RL_FN"
0438:                            + " INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY = SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID = ?"
0439:                            + " INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY"
0440:                            + " INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY = SAKAI_REALM_RL_FN.FUNCTION_KEY";
0441:                    Object fields[] = new Object[1];
0442:                    fields[0] = realm.getId();
0443:                    List all = m_sql.dbRead(conn, sql, fields, new SqlReader() {
0444:                        public Object readSqlResultRecord(ResultSet result) {
0445:                            try {
0446:                                // get the fields
0447:                                String roleName = result.getString(1);
0448:                                String functionName = result.getString(2);
0449:
0450:                                // make the role if needed
0451:                                BaseRole role = (BaseRole) realm.m_roles
0452:                                        .get(roleName);
0453:                                if (role == null) {
0454:                                    role = new BaseRole(roleName);
0455:                                    realm.m_roles.put(role.getId(), role);
0456:                                }
0457:
0458:                                // add the function to the role
0459:                                role.allowFunction(functionName);
0460:
0461:                                return null;
0462:                            } catch (SQLException ignore) {
0463:                                return null;
0464:                            }
0465:                        }
0466:                    });
0467:
0468:                    // read the role descriptions
0469:                    sql = "SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_ROLE_DESC.DESCRIPTION, SAKAI_REALM_ROLE_DESC.PROVIDER_ONLY"
0470:                            + " FROM SAKAI_REALM_ROLE_DESC"
0471:                            + " INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY = SAKAI_REALM_ROLE_DESC.REALM_KEY AND SAKAI_REALM.REALM_ID = ?"
0472:                            + " INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_ROLE_DESC.ROLE_KEY";
0473:                    m_sql.dbRead(conn, sql, fields, new SqlReader() {
0474:                        public Object readSqlResultRecord(ResultSet result) {
0475:                            try {
0476:                                // get the fields
0477:                                String roleName = result.getString(1);
0478:                                String description = result.getString(2);
0479:                                boolean providerOnly = "1".equals(result
0480:                                        .getString(3));
0481:
0482:                                // find the role - create it if needed
0483:                                // Note: if the role does not yet exist, it has no functions
0484:                                BaseRole role = (BaseRole) realm.m_roles
0485:                                        .get(roleName);
0486:                                if (role == null) {
0487:                                    role = new BaseRole(roleName);
0488:                                    realm.m_roles.put(role.getId(), role);
0489:                                }
0490:
0491:                                // set the description
0492:                                role.setDescription(description);
0493:
0494:                                // set the provider only flag
0495:                                role.setProviderOnly(providerOnly);
0496:
0497:                                return null;
0498:                            } catch (SQLException ignore) {
0499:                                return null;
0500:                            }
0501:                        }
0502:                    });
0503:
0504:                    // read the role grants
0505:                    sql = "SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_RL_GR.USER_ID, SAKAI_REALM_RL_GR.ACTIVE, SAKAI_REALM_RL_GR.PROVIDED"
0506:                            + " FROM SAKAI_REALM_RL_GR"
0507:                            + " INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY = SAKAI_REALM_RL_GR.REALM_KEY AND SAKAI_REALM.REALM_ID = ?"
0508:                            + " INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_GR.ROLE_KEY";
0509:                    all = m_sql.dbRead(conn, sql, fields, new SqlReader() {
0510:                        public Object readSqlResultRecord(ResultSet result) {
0511:                            try {
0512:                                // get the fields
0513:                                String roleName = result.getString(1);
0514:                                String userId = result.getString(2);
0515:                                String active = result.getString(3);
0516:                                String provided = result.getString(4);
0517:
0518:                                // give the user one and only one role grant - there should be no second...
0519:                                BaseMember grant = (BaseMember) realm.m_userGrants
0520:                                        .get(userId);
0521:                                if (grant == null) {
0522:                                    // find the role - if it does not exist, create it for this grant
0523:                                    // NOTE: it would have no functions or description
0524:                                    BaseRole role = (BaseRole) realm.m_roles
0525:                                            .get(roleName);
0526:                                    if (role == null) {
0527:                                        role = new BaseRole(roleName);
0528:                                        realm.m_roles.put(role.getId(), role);
0529:                                    }
0530:
0531:                                    grant = new BaseMember(role, "1"
0532:                                            .equals(active), "1"
0533:                                            .equals(provided), userId);
0534:
0535:                                    realm.m_userGrants.put(userId, grant);
0536:                                } else {
0537:                                    M_log
0538:                                            .warn("completeGet: additional user - role grant: "
0539:                                                    + userId + " " + roleName);
0540:                                }
0541:
0542:                                return null;
0543:                            } catch (SQLException ignore) {
0544:                                return null;
0545:                            }
0546:                        }
0547:                    });
0548:                }
0549:
0550:                /**
0551:                 * {@inheritDoc}
0552:                 */
0553:                public List getAuthzGroups(String criteria, PagingPosition page) {
0554:                    List rv = null;
0555:
0556:                    if (criteria != null) {
0557:                        criteria = "%" + criteria + "%";
0558:                        String where = "( UPPER(REALM_ID) like UPPER(?) or UPPER(PROVIDER_ID) like UPPER(?) )";
0559:                        Object[] fields = new Object[2];
0560:                        fields[0] = criteria;
0561:                        fields[1] = criteria;
0562:
0563:                        // paging
0564:                        if (page != null) {
0565:                            // adjust to the size of the set found
0566:                            // page.validate(rv.size());
0567:
0568:                            rv = getSelectedResources(where, fields, page
0569:                                    .getFirst(), page.getLast());
0570:                        } else {
0571:                            rv = getSelectedResources(where, fields);
0572:                        }
0573:                    }
0574:
0575:                    else {
0576:                        // paging
0577:                        if (page != null) {
0578:                            // adjust to the size of the set found
0579:                            // page.validate(rv.size());
0580:
0581:                            rv = getAllResources(page.getFirst(), page
0582:                                    .getLast());
0583:                        } else {
0584:                            rv = getAllResources();
0585:                        }
0586:                    }
0587:
0588:                    return rv;
0589:                }
0590:
0591:                /**
0592:                 * {@inheritDoc}
0593:                 */
0594:                public int countAuthzGroups(String criteria) {
0595:                    int rv = 0;
0596:
0597:                    if (criteria != null) {
0598:                        criteria = "%" + criteria + "%";
0599:                        String where = "( UPPER(REALM_ID) like UPPER(?) or UPPER(PROVIDER_ID) like UPPER(?) )";
0600:                        Object[] fields = new Object[2];
0601:                        fields[0] = criteria;
0602:                        fields[1] = criteria;
0603:
0604:                        rv = countSelectedResources(where, fields);
0605:                    }
0606:
0607:                    else {
0608:                        rv = countAllResources();
0609:                    }
0610:
0611:                    return rv;
0612:                }
0613:
0614:                /**
0615:                 * {@inheritDoc}
0616:                 */
0617:                public Set getProviderIds(String authzGroupId) {
0618:                    String statement = "select srp.PROVIDER_ID from SAKAI_REALM sr INNER JOIN SAKAI_REALM_PROVIDER srp on sr.REALM_KEY = srp.REALM_KEY where sr.REALM_ID=?";
0619:                    List results = sqlService().dbRead(statement,
0620:                            new Object[] { authzGroupId }, null);
0621:                    if (results == null) {
0622:                        return new HashSet();
0623:                    }
0624:                    return new HashSet(results);
0625:                }
0626:
0627:                /**
0628:                 * {@inheritDoc}
0629:                 */
0630:                public Set getAuthzGroupIds(String providerId) {
0631:                    String statement = "select sr.REALM_ID from SAKAI_REALM sr INNER JOIN SAKAI_REALM_PROVIDER srp on sr.REALM_KEY = srp.REALM_KEY where srp.PROVIDER_ID=?";
0632:                    List results = sqlService().dbRead(statement,
0633:                            new Object[] { providerId }, null);
0634:                    if (results == null) {
0635:                        return new HashSet();
0636:                    }
0637:                    return new HashSet(results);
0638:                }
0639:
0640:                /**
0641:                 * {@inheritDoc}
0642:                 */
0643:                public Set getAuthzGroupsIsAllowed(String userId, String lock,
0644:                        Collection azGroups) {
0645:                    // further limited to only those authz groups in the azGroups parameter if not null
0646:
0647:                    // if azGroups is not null, but empty, we can short-circut and return an empty set
0648:                    // or if the lock is null
0649:                    if (((azGroups != null) && azGroups.isEmpty())
0650:                            || lock == null) {
0651:                        return new HashSet();
0652:                    }
0653:
0654:                    // Just like unlock, except we use all realms and get their ids
0655:                    // Note: consider over all realms just those realms where there's a grant of a role that satisfies the lock
0656:                    // Ignore realms where anon or auth satisfy the lock.
0657:
0658:                    boolean auth = (userId != null)
0659:                            && (!userDirectoryService().getAnonymousUser()
0660:                                    .getId().equals(userId));
0661:                    String sql = "";
0662:                    StringBuffer sqlBuf = null;
0663:
0664:                    // Assemble SQL
0665:                    sqlBuf = new StringBuffer();
0666:                    sqlBuf.append("select SR.REALM_ID ");
0667:                    sqlBuf.append("from SAKAI_REALM_FUNCTION SRF ");
0668:                    sqlBuf
0669:                            .append("inner join SAKAI_REALM_RL_FN SRRF on SRF.FUNCTION_KEY = SRRF.FUNCTION_KEY ");
0670:                    sqlBuf
0671:                            .append("inner join SAKAI_REALM_RL_GR SRRG on SRRF.ROLE_KEY = SRRG.ROLE_KEY and SRRF.REALM_KEY = SRRG.REALM_KEY ");
0672:                    sqlBuf
0673:                            .append("inner join SAKAI_REALM SR on SRRF.REALM_KEY = SR.REALM_KEY ");
0674:                    sqlBuf.append("where SRF.FUNCTION_NAME = ? ");
0675:                    sqlBuf.append("and SRRG.USER_ID = ? ");
0676:                    sqlBuf.append("and SRRG.ACTIVE = '1' ");
0677:
0678:                    if (azGroups != null) {
0679:                        sqlBuf.append("and SR.REALM_ID in (");
0680:                        for (int i = 0; i < azGroups.size() - 1; i++) {
0681:                            sqlBuf.append("?,");
0682:                        }
0683:                        sqlBuf.append("?) ");
0684:                    }
0685:
0686:                    sql = sqlBuf.toString();
0687:
0688:                    int size = 2;
0689:                    if (azGroups != null) {
0690:                        size += azGroups.size();
0691:                    }
0692:                    Object[] fields = new Object[size];
0693:                    fields[0] = lock;
0694:                    fields[1] = userId;
0695:                    if (azGroups != null) {
0696:                        int pos = 2;
0697:                        for (Iterator i = azGroups.iterator(); i.hasNext();) {
0698:                            fields[pos++] = i.next();
0699:                        }
0700:                    }
0701:
0702:                    // Get resultset
0703:                    List results = m_sql.dbRead(sql, fields, null);
0704:                    Set rv = new HashSet();
0705:                    rv.addAll(results);
0706:
0707:                    return rv;
0708:                }
0709:
0710:                /**
0711:                 * {@inheritDoc}
0712:                 */
0713:                public AuthzGroup put(String id) {
0714:                    BaseAuthzGroup rv = (BaseAuthzGroup) super .putResource(id,
0715:                            fields(id, null, false));
0716:                    if (rv != null) {
0717:                        rv.activate();
0718:                    }
0719:
0720:                    return rv;
0721:                }
0722:
0723:                /**
0724:                 * {@inheritDoc}
0725:                 */
0726:                public AuthzGroup edit(String id) {
0727:                    BaseAuthzGroup edit = (BaseAuthzGroup) super 
0728:                            .editResource(id);
0729:
0730:                    if (edit != null) {
0731:                        edit.activate();
0732:                        completeGet(null, edit, true);
0733:                    }
0734:
0735:                    return edit;
0736:                }
0737:
0738:                /**
0739:                 * @inheritDoc
0740:                 */
0741:                public void save(final AuthzGroup edit) {
0742:                    // pre-check the roles and functions to make sure they are all defined
0743:                    for (Iterator iRoles = ((BaseAuthzGroup) edit).m_roles
0744:                            .values().iterator(); iRoles.hasNext();) {
0745:                        Role role = (Role) iRoles.next();
0746:
0747:                        // make sure the role name is defined / define it
0748:                        checkRoleName(role.getId());
0749:
0750:                        for (Iterator iFunctions = role.getAllowedFunctions()
0751:                                .iterator(); iFunctions.hasNext();) {
0752:                            String function = (String) iFunctions.next();
0753:
0754:                            // make sure the role name is defined / define it
0755:                            checkFunctionName(function);
0756:                        }
0757:                    }
0758:
0759:                    // run our save code in a transaction that will restart on deadlock
0760:                    // if deadlock retry fails, or any other error occurs, a runtime error will be thrown
0761:                    m_sql.transact(new Runnable() {
0762:                        public void run() {
0763:                            saveTx(edit);
0764:                        }
0765:                    }, "azg:" + edit.getId());
0766:
0767:                    // update with the provider
0768:                    refreshAuthzGroup((BaseAuthzGroup) edit);
0769:                }
0770:
0771:                /**
0772:                 * The transaction code to save the azg.
0773:                 * 
0774:                 * @param edit
0775:                 *        The azg to save.
0776:                 */
0777:                protected void saveTx(AuthzGroup edit) {
0778:                    // update SAKAI_REALM_RL_FN: read, diff with the edit, add and delete
0779:                    save_REALM_RL_FN(edit);
0780:
0781:                    // update SAKAI_REALM_RL_GR
0782:                    save_REALM_RL_GR(edit);
0783:
0784:                    // update SAKAI_REALM_PROVIDER
0785:                    save_REALM_PROVIDER(edit);
0786:
0787:                    // update SAKAI_REALM_ROLE_DESC
0788:                    save_REALM_ROLE_DESC(edit);
0789:
0790:                    // update the main realm table and properties
0791:                    super .commitResource(edit, fields(edit.getId(),
0792:                            ((BaseAuthzGroup) edit), true), edit
0793:                            .getProperties(), ((BaseAuthzGroup) edit).getKey());
0794:                }
0795:
0796:                protected void save_REALM_RL_FN(AuthzGroup azg) {
0797:                    // add what we have in the azg, unless we see it in the db
0798:                    final Set<RoleAndFunction> toAdd = new HashSet<RoleAndFunction>();
0799:                    for (Iterator iRoles = ((BaseAuthzGroup) azg).m_roles
0800:                            .values().iterator(); iRoles.hasNext();) {
0801:                        Role role = (Role) iRoles.next();
0802:                        for (Iterator iFunctions = role.getAllowedFunctions()
0803:                                .iterator(); iFunctions.hasNext();) {
0804:                            String function = (String) iFunctions.next();
0805:                            toAdd.add(new RoleAndFunction(role.getId(),
0806:                                    function));
0807:                        }
0808:                    }
0809:
0810:                    // delete anything we see in the db we don't have in the azg
0811:                    final Set<RoleAndFunction> toDelete = new HashSet<RoleAndFunction>();
0812:
0813:                    // read what we have there now
0814:                    String sql = "SELECT RR.ROLE_NAME, RF.FUNCTION_NAME FROM SAKAI_REALM_RL_FN RRF"
0815:                            + " INNER JOIN SAKAI_REALM R ON RRF.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?"
0816:                            + " INNER JOIN SAKAI_REALM_ROLE RR ON RRF.ROLE_KEY = RR.ROLE_KEY"
0817:                            + " INNER JOIN SAKAI_REALM_FUNCTION RF ON RRF.FUNCTION_KEY = RF.FUNCTION_KEY";
0818:                    Object fields[] = new Object[1];
0819:                    fields[0] = caseId(azg.getId());
0820:                    m_sql.dbRead(sql, fields, new SqlReader() {
0821:                        public Object readSqlResultRecord(ResultSet result) {
0822:                            try {
0823:                                String role = result.getString(1);
0824:                                String function = result.getString(2);
0825:                                RoleAndFunction raf = new RoleAndFunction(role,
0826:                                        function);
0827:
0828:                                // if we have it in the set toAdd, we can remove it (it's alredy on the db)
0829:                                if (toAdd.contains(raf)) {
0830:                                    toAdd.remove(raf);
0831:                                }
0832:
0833:                                // if we don't have it in the azg, we need to delete it
0834:                                else {
0835:                                    toDelete.add(raf);
0836:                                }
0837:                            } catch (Throwable e) {
0838:                                M_log.warn("save_REALM_RL_FN: " + e.toString());
0839:                            }
0840:
0841:                            return null;
0842:                        }
0843:                    });
0844:
0845:                    fields = new Object[3];
0846:                    fields[0] = caseId(azg.getId());
0847:
0848:                    // delete what we need to
0849:                    if ("mysql".equals(sqlService().getVendor())) {
0850:                        sql = "DELETE RRF FROM SAKAI_REALM_RL_FN RRF"
0851:                                + " INNER JOIN SAKAI_REALM R ON RRF.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?"
0852:                                + " INNER JOIN SAKAI_REALM_ROLE RR ON RRF.ROLE_KEY = RR.ROLE_KEY AND RR.ROLE_NAME = ?"
0853:                                + " INNER JOIN SAKAI_REALM_FUNCTION RF ON RRF.FUNCTION_KEY = RF.FUNCTION_KEY AND RF.FUNCTION_NAME = ?";
0854:                    } else {
0855:                        sql = "DELETE FROM SAKAI_REALM_RL_FN"
0856:                                + " WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)"
0857:                                + " AND ROLE_KEY IN (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = ?)"
0858:                                + " AND FUNCTION_KEY IN (SELECT FUNCTION_KEY FROM SAKAI_REALM_FUNCTION WHERE FUNCTION_NAME = ?)";
0859:
0860:                    }
0861:                    for (RoleAndFunction raf : toDelete) {
0862:                        fields[1] = raf.role;
0863:                        fields[2] = raf.function;
0864:                        m_sql.dbWrite(sql, fields);
0865:                    }
0866:
0867:                    // add what we need to
0868:                    sql = "INSERT INTO SAKAI_REALM_RL_FN (REALM_KEY, ROLE_KEY, FUNCTION_KEY) VALUES ("
0869:                            + " (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?),"
0870:                            + " (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = ?),"
0871:                            + " (SELECT FUNCTION_KEY FROM SAKAI_REALM_FUNCTION WHERE FUNCTION_NAME = ?))";
0872:                    for (RoleAndFunction raf : toAdd) {
0873:                        fields[1] = raf.role;
0874:                        fields[2] = raf.function;
0875:                        m_sql.dbWrite(sql, fields);
0876:                    }
0877:                }
0878:
0879:                protected void save_REALM_RL_GR(AuthzGroup azg) {
0880:                    // add what we have in the azg, unless we see it in the db
0881:                    final Set<UserAndRole> toAdd = new HashSet<UserAndRole>();
0882:                    for (Iterator i = ((BaseAuthzGroup) azg).m_userGrants
0883:                            .entrySet().iterator(); i.hasNext();) {
0884:                        Map.Entry entry = (Map.Entry) i.next();
0885:                        Member grant = (Member) entry.getValue();
0886:                        toAdd.add(new UserAndRole(grant.getUserId(), grant
0887:                                .getRole().getId(), grant.isActive(), grant
0888:                                .isProvided()));
0889:                    }
0890:
0891:                    // delete anything we see in the db we don't have in the azg
0892:                    final Set<UserAndRole> toDelete = new HashSet<UserAndRole>();
0893:
0894:                    // read what we have there now
0895:                    String sql = "SELECT RRG.USER_ID, RR.ROLE_NAME, RRG.ACTIVE, RRG.PROVIDED FROM SAKAI_REALM_RL_GR RRG "
0896:                            + " INNER JOIN SAKAI_REALM R ON RRG.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?"
0897:                            + " INNER JOIN SAKAI_REALM_ROLE RR ON RRG.ROLE_KEY = RR.ROLE_KEY";
0898:                    Object fields[] = new Object[1];
0899:                    fields[0] = caseId(azg.getId());
0900:                    m_sql.dbRead(sql, fields, new SqlReader() {
0901:                        public Object readSqlResultRecord(ResultSet result) {
0902:                            try {
0903:                                String userId = result.getString(1);
0904:                                String role = result.getString(2);
0905:                                boolean active = "1"
0906:                                        .equals(result.getString(3));
0907:                                boolean provided = "1".equals(result
0908:                                        .getString(4));
0909:                                UserAndRole uar = new UserAndRole(userId, role,
0910:                                        active, provided);
0911:
0912:                                // if we have it in the set toAdd, we can remove it (it's alredy on the db)
0913:                                if (toAdd.contains(uar)) {
0914:                                    toAdd.remove(uar);
0915:                                }
0916:
0917:                                // if we don't have it in the azg, we need to delete it
0918:                                else {
0919:                                    toDelete.add(uar);
0920:                                }
0921:                            } catch (Throwable e) {
0922:                                M_log.warn("save_REALM_RL_GR: " + e.toString());
0923:                            }
0924:
0925:                            return null;
0926:                        }
0927:                    });
0928:
0929:                    fields = new Object[5];
0930:                    fields[0] = caseId(azg.getId());
0931:
0932:                    // delete what we need to
0933:                    if ("mysql".equals(sqlService().getVendor())) {
0934:                        sql = "DELETE RRG FROM SAKAI_REALM_RL_GR RRG"
0935:                                + " INNER JOIN SAKAI_REALM R ON RRG.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?"
0936:                                + " INNER JOIN SAKAI_REALM_ROLE RR ON RRG.ROLE_KEY = RR.ROLE_KEY AND RR.ROLE_NAME = ?"
0937:                                + " WHERE RRG.USER_ID = ? AND RRG.ACTIVE = ? AND RRG.PROVIDED = ?";
0938:                    } else {
0939:                        sql = "DELETE FROM SAKAI_REALM_RL_GR"
0940:                                + " WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)"
0941:                                + " AND ROLE_KEY IN (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = ?)"
0942:                                + " AND USER_ID = ? AND ACTIVE = ? AND PROVIDED = ?";
0943:                    }
0944:                    for (UserAndRole uar : toDelete) {
0945:                        fields[1] = uar.role;
0946:                        fields[2] = uar.userId;
0947:                        fields[3] = uar.active ? "1" : "0";
0948:                        fields[4] = uar.provided ? "1" : "0";
0949:                        m_sql.dbWrite(sql, fields);
0950:                    }
0951:
0952:                    // add what we need to
0953:                    sql = "INSERT INTO SAKAI_REALM_RL_GR (REALM_KEY, USER_ID, ROLE_KEY, ACTIVE, PROVIDED) VALUES ("
0954:                            + " (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?), ?, "
0955:                            + " (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = ?), ?, ?)";
0956:                    for (UserAndRole uar : toAdd) {
0957:                        fields[1] = uar.userId;
0958:                        fields[2] = uar.role;
0959:                        fields[3] = uar.active ? "1" : "0";
0960:                        fields[4] = uar.provided ? "1" : "0";
0961:                        m_sql.dbWrite(sql, fields);
0962:                    }
0963:                }
0964:
0965:                protected void save_REALM_PROVIDER(AuthzGroup azg) {
0966:                    // we we are not provider, delete any for this realm
0967:                    if ((azg.getProviderGroupId() == null)
0968:                            || (m_provider == null)) {
0969:                        String sql = null;
0970:                        if ("mysql".equals(sqlService().getVendor())) {
0971:                            sql = "DELETE RP FROM SAKAI_REALM_PROVIDER RP"
0972:                                    + " INNER JOIN SAKAI_REALM R ON RP.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?";
0973:                        } else {
0974:                            sql = "DELETE FROM SAKAI_REALM_PROVIDER"
0975:                                    + " WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)";
0976:                        }
0977:                        Object[] fields = new Object[1];
0978:                        fields[0] = caseId(azg.getId());
0979:                        m_sql.dbWrite(sql, fields);
0980:                        return;
0981:                    }
0982:
0983:                    // add what we have in the azg, unless we see it in the db
0984:                    final Set<String> toAdd = new HashSet<String>();
0985:                    String[] ids = m_provider
0986:                            .unpackId(azg.getProviderGroupId());
0987:                    if (ids != null) {
0988:                        for (String id : ids) {
0989:                            toAdd.add(id);
0990:                        }
0991:                    }
0992:
0993:                    // delete anything we see in the db we don't have in the azg
0994:                    final Set<String> toDelete = new HashSet<String>();
0995:
0996:                    // read what we have there now
0997:                    String sql = "SELECT RP.PROVIDER_ID FROM SAKAI_REALM_PROVIDER RP"
0998:                            + " INNER JOIN SAKAI_REALM R ON RP.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?";
0999:                    Object fields[] = new Object[1];
1000:                    fields[0] = caseId(azg.getId());
1001:                    m_sql.dbRead(sql, fields, new SqlReader() {
1002:                        public Object readSqlResultRecord(ResultSet result) {
1003:                            try {
1004:                                String provider = result.getString(1);
1005:
1006:                                // if we have it in the set toAdd, we can remove it (it's alredy on the db)
1007:                                if (toAdd.contains(provider)) {
1008:                                    toAdd.remove(provider);
1009:                                }
1010:
1011:                                // if we don't have it in the azg, we need to delete it
1012:                                else {
1013:                                    toDelete.add(provider);
1014:                                }
1015:                            } catch (Throwable e) {
1016:                                M_log.warn("save_REALM_PROVIDER: "
1017:                                        + e.toString());
1018:                            }
1019:
1020:                            return null;
1021:                        }
1022:                    });
1023:
1024:                    fields = new Object[2];
1025:                    fields[0] = caseId(azg.getId());
1026:
1027:                    // delete what we need to
1028:                    if ("mysql".equals(sqlService().getVendor())) {
1029:                        sql = "DELETE RP FROM SAKAI_REALM_PROVIDER RP"
1030:                                + " INNER JOIN SAKAI_REALM R ON RP.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?"
1031:                                + " WHERE RP.PROVIDER_ID = ?";
1032:                    } else {
1033:                        sql = "DELETE FROM SAKAI_REALM_PROVIDER"
1034:                                + " WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)"
1035:                                + " AND PROVIDER_ID = ?";
1036:                    }
1037:                    for (String provider : toDelete) {
1038:                        fields[1] = provider;
1039:                        m_sql.dbWrite(sql, fields);
1040:                    }
1041:
1042:                    // add what we need to
1043:                    sql = "INSERT INTO SAKAI_REALM_PROVIDER (REALM_KEY, PROVIDER_ID) VALUES ("
1044:                            + " (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?), ?)";
1045:                    for (String provider : toAdd) {
1046:                        fields[1] = provider;
1047:                        m_sql.dbWrite(sql, fields);
1048:                    }
1049:                }
1050:
1051:                protected void save_REALM_ROLE_DESC(AuthzGroup azg) {
1052:                    // add what we have in the azg, unless we see it in the db
1053:                    final Set<RoleAndDescription> toAdd = new HashSet<RoleAndDescription>();
1054:                    for (Iterator iRoles = ((BaseAuthzGroup) azg).m_roles
1055:                            .values().iterator(); iRoles.hasNext();) {
1056:                        Role role = (Role) iRoles.next();
1057:                        toAdd.add(new RoleAndDescription(role.getId(), role
1058:                                .getDescription(), role.isProviderOnly()));
1059:                    }
1060:
1061:                    // delete anything we see in the db we don't have in the azg
1062:                    final Set<RoleAndDescription> toDelete = new HashSet<RoleAndDescription>();
1063:
1064:                    // read what we have there now
1065:                    String sql = "SELECT RR.ROLE_NAME, RRD.DESCRIPTION, RRD.PROVIDER_ONLY FROM SAKAI_REALM_ROLE_DESC RRD"
1066:                            + " INNER JOIN SAKAI_REALM R ON RRD.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?"
1067:                            + " INNER JOIN SAKAI_REALM_ROLE RR ON RRD.ROLE_KEY = RR.ROLE_KEY";
1068:                    Object fields[] = new Object[1];
1069:                    fields[0] = caseId(azg.getId());
1070:                    m_sql.dbRead(sql, fields, new SqlReader() {
1071:                        public Object readSqlResultRecord(ResultSet result) {
1072:                            try {
1073:                                String role = result.getString(1);
1074:                                String description = result.getString(2);
1075:                                boolean providerOnly = "1".equals(result
1076:                                        .getString(3));
1077:                                RoleAndDescription rad = new RoleAndDescription(
1078:                                        role, description, providerOnly);
1079:
1080:                                // if we have it in the set toAdd, we can remove it (it's alredy on the db)
1081:                                if (toAdd.contains(rad)) {
1082:                                    toAdd.remove(rad);
1083:                                }
1084:
1085:                                // if we don't have it in the azg, we need to delete it
1086:                                else {
1087:                                    toDelete.add(rad);
1088:                                }
1089:                            } catch (Throwable e) {
1090:                                M_log.warn("save_REALM_ROLE_DESC: "
1091:                                        + e.toString());
1092:                            }
1093:
1094:                            return null;
1095:                        }
1096:                    });
1097:
1098:                    fields = new Object[2];
1099:                    fields[0] = caseId(azg.getId());
1100:
1101:                    // delete what we need to
1102:                    if ("mysql".equals(sqlService().getVendor())) {
1103:                        sql = "DELETE RRD FROM SAKAI_REALM_ROLE_DESC RRD"
1104:                                + " INNER JOIN SAKAI_REALM R ON RRD.REALM_KEY = R.REALM_KEY AND R.REALM_ID = ?"
1105:                                + " INNER JOIN SAKAI_REALM_ROLE RR ON RRD.ROLE_KEY = RR.ROLE_KEY AND RR.ROLE_NAME = ?";
1106:                    } else {
1107:                        sql = "DELETE FROM SAKAI_REALM_ROLE_DESC"
1108:                                + " WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)"
1109:                                + " AND ROLE_KEY IN (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = ?)";
1110:                    }
1111:                    for (RoleAndDescription rad : toDelete) {
1112:                        fields[1] = rad.role;
1113:                        m_sql.dbWrite(sql, fields);
1114:                    }
1115:
1116:                    fields = new Object[4];
1117:                    fields[0] = caseId(azg.getId());
1118:
1119:                    // add what we need to
1120:                    sql = "INSERT INTO SAKAI_REALM_ROLE_DESC (REALM_KEY, ROLE_KEY, DESCRIPTION, PROVIDER_ONLY) VALUES ("
1121:                            + " (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?),"
1122:                            + " (SELECT ROLE_KEY FROM SAKAI_REALM_ROLE WHERE ROLE_NAME = ?), ?, ?)";
1123:                    for (RoleAndDescription rad : toAdd) {
1124:                        fields[1] = rad.role;
1125:                        fields[2] = rad.description;
1126:                        fields[3] = rad.providerOnly ? "1" : "0";
1127:                        m_sql.dbWrite(sql, fields);
1128:                    }
1129:                }
1130:
1131:                public void cancel(AuthzGroup edit) {
1132:                    super .cancelResource(edit);
1133:                }
1134:
1135:                public void remove(final AuthzGroup edit) {
1136:                    // in a transaction
1137:                    m_sql.transact(new Runnable() {
1138:                        public void run() {
1139:                            removeTx(edit);
1140:                        }
1141:                    }, "azgRemove:" + edit.getId());
1142:                }
1143:
1144:                /**
1145:                 * Transaction code for removing the azg.
1146:                 */
1147:                protected void removeTx(AuthzGroup edit) {
1148:                    // delete all the role functions, auth grants, anon grants, role grants, fucntion grants
1149:                    // and then the realm and release the lock.
1150:
1151:                    // delete the role functions, role grants, provider entries
1152:                    Object fields[] = new Object[1];
1153:                    fields[0] = caseId(edit.getId());
1154:
1155:                    String statement = null;
1156:
1157:                    if ("mysql".equals(sqlService().getVendor())) {
1158:                        statement = "DELETE SAKAI_REALM_RL_FN FROM SAKAI_REALM_RL_FN INNER JOIN SAKAI_REALM ON SAKAI_REALM_RL_FN.REALM_KEY = SAKAI_REALM.REALM_KEY AND SAKAI_REALM.REALM_ID = ?";
1159:                    } else {
1160:                        statement = "DELETE FROM SAKAI_REALM_RL_FN WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)";
1161:                    }
1162:                    m_sql.dbWrite(statement, fields);
1163:
1164:                    if ("mysql".equals(sqlService().getVendor())) {
1165:                        statement = "DELETE SAKAI_REALM_RL_GR FROM SAKAI_REALM_RL_GR INNER JOIN SAKAI_REALM ON SAKAI_REALM_RL_GR.REALM_KEY = SAKAI_REALM.REALM_KEY AND SAKAI_REALM.REALM_ID = ?";
1166:                    } else {
1167:                        statement = "DELETE FROM SAKAI_REALM_RL_GR WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)";
1168:                    }
1169:                    m_sql.dbWrite(statement, fields);
1170:
1171:                    if ("mysql".equals(sqlService().getVendor())) {
1172:                        statement = "DELETE SAKAI_REALM_PROVIDER FROM SAKAI_REALM_PROVIDER INNER JOIN SAKAI_REALM ON SAKAI_REALM_PROVIDER.REALM_KEY = SAKAI_REALM.REALM_KEY AND SAKAI_REALM.REALM_ID = ?";
1173:                    } else {
1174:                        statement = "DELETE FROM SAKAI_REALM_PROVIDER WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)";
1175:                    }
1176:                    m_sql.dbWrite(statement, fields);
1177:
1178:                    if ("mysql".equals(sqlService().getVendor())) {
1179:                        statement = "DELETE SAKAI_REALM_ROLE_DESC FROM SAKAI_REALM_ROLE_DESC INNER JOIN SAKAI_REALM ON SAKAI_REALM_ROLE_DESC.REALM_KEY = SAKAI_REALM.REALM_KEY AND SAKAI_REALM.REALM_ID = ?";
1180:                    } else {
1181:                        statement = "DELETE FROM SAKAI_REALM_ROLE_DESC WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)";
1182:                    }
1183:                    m_sql.dbWrite(statement, fields);
1184:
1185:                    // delete the realm and properties
1186:                    super 
1187:                            .removeResource(edit, ((BaseAuthzGroup) edit)
1188:                                    .getKey());
1189:                }
1190:
1191:                /**
1192:                 * Get the fields for the database from the edit for this id, and the id again at the end if needed
1193:                 * 
1194:                 * @param id
1195:                 *        The resource id
1196:                 * @param edit
1197:                 *        The edit (may be null in a new)
1198:                 * @param idAgain
1199:                 *        If true, include the id field again at the end, else don't.
1200:                 * @return The fields for the database.
1201:                 */
1202:                protected Object[] fields(String id, BaseAuthzGroup edit,
1203:                        boolean idAgain) {
1204:                    Object[] rv = new Object[idAgain ? 8 : 7];
1205:                    rv[0] = caseId(id);
1206:                    if (idAgain) {
1207:                        rv[7] = rv[0];
1208:                    }
1209:
1210:                    if (edit == null) {
1211:                        String current = sessionManager()
1212:                                .getCurrentSessionUserId();
1213:
1214:                        // if no current user, since we are working up a new user record, use the user id as creator...
1215:                        if (current == null)
1216:                            current = "";
1217:
1218:                        Time now = timeService().newTime();
1219:
1220:                        rv[1] = "";
1221:                        rv[2] = "";
1222:                        rv[3] = current;
1223:                        rv[4] = current;
1224:                        rv[5] = now;
1225:                        rv[6] = now;
1226:                    }
1227:
1228:                    else {
1229:                        rv[1] = StringUtil.trimToZero(edit.m_providerRealmId);
1230:                        rv[2] = StringUtil.trimToZero(edit.m_maintainRole);
1231:                        rv[3] = StringUtil.trimToZero(edit.m_createdUserId);
1232:                        rv[4] = StringUtil
1233:                                .trimToZero(edit.m_lastModifiedUserId);
1234:                        rv[5] = edit.getCreatedTime();
1235:                        rv[6] = edit.getModifiedTime();
1236:                    }
1237:
1238:                    return rv;
1239:                }
1240:
1241:                /**
1242:                 * Read from the result one set of fields to create a Resource.
1243:                 * 
1244:                 * @param result
1245:                 *        The Sql query result.
1246:                 * @return The Resource object.
1247:                 */
1248:                public Object readSqlResultRecord(ResultSet result) {
1249:                    try {
1250:                        String id = result.getString(1);
1251:                        String providerId = result.getString(2);
1252:                        String maintainRole = result.getString(3);
1253:                        String createdBy = result.getString(4);
1254:                        String modifiedBy = result.getString(5);
1255:                        java.sql.Timestamp ts = result.getTimestamp(6,
1256:                                sqlService().getCal());
1257:                        Time createdOn = null;
1258:                        if (ts != null) {
1259:                            createdOn = timeService().newTime(ts.getTime());
1260:                        }
1261:                        ts = result.getTimestamp(7, sqlService().getCal());
1262:                        Time modifiedOn = null;
1263:                        if (ts != null) {
1264:                            modifiedOn = timeService().newTime(ts.getTime());
1265:                        }
1266:
1267:                        // the special local integer 'db' id field, read after the field list
1268:                        Integer dbid = new Integer(result.getInt(8));
1269:
1270:                        // create the Resource from these fields
1271:                        return new BaseAuthzGroup(dbid, id, providerId,
1272:                                maintainRole, createdBy, createdOn, modifiedBy,
1273:                                modifiedOn);
1274:                    } catch (SQLException e) {
1275:                        M_log.warn("readSqlResultRecord: " + e);
1276:                        return null;
1277:                    }
1278:                }
1279:
1280:                /**
1281:                 * {@inheritDoc}
1282:                 */
1283:                public boolean isAllowed(String userId, String lock,
1284:                        String realmId) {
1285:                    if ((lock == null) || (realmId == null))
1286:                        return false;
1287:
1288:                    // does the user have any roles granted that include this lock, based on grants or anon/auth?
1289:                    boolean auth = (userId != null)
1290:                            && (!userDirectoryService().getAnonymousUser()
1291:                                    .getId().equals(userId));
1292:
1293:                    String statement = "select count(1) "
1294:                            + "from "
1295:                            + "  SAKAI_REALM_RL_FN MAINTABLE "
1296:                            + "     LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES "
1297:                            + "        ON (MAINTABLE.REALM_KEY = GRANTED_ROLES.REALM_KEY AND MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), "
1298:                            + "  SAKAI_REALM REALMS, "
1299:                            + "  SAKAI_REALM_ROLE ROLES, "
1300:                            + "  SAKAI_REALM_FUNCTION FUNCTIONS "
1301:                            + "where "
1302:                            +
1303:                            // our criteria
1304:                            "  ( "
1305:                            + "    ROLES.ROLE_NAME in('"
1306:                            + ANON_ROLE
1307:                            + "'"
1308:                            + (auth ? ",'" + AUTH_ROLE + "'" : "")
1309:                            + ") "
1310:                            + "    or "
1311:                            + "    ( "
1312:                            + "      GRANTED_ROLES.USER_ID = ? "
1313:                            + "      AND GRANTED_ROLES.ACTIVE = 1 "
1314:                            + "    ) "
1315:                            + "  )"
1316:                            + "  AND FUNCTIONS.FUNCTION_NAME = ? "
1317:                            + "  AND REALMS.REALM_ID in (?) "
1318:                            +
1319:                            // for the join
1320:                            "  AND MAINTABLE.REALM_KEY = REALMS.REALM_KEY "
1321:                            + "  AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY "
1322:                            + "  AND MAINTABLE.ROLE_KEY = ROLES.ROLE_KEY ";
1323:
1324:                    Object[] fields = new Object[3];
1325:                    fields[0] = userId;
1326:                    fields[1] = lock;
1327:                    fields[2] = realmId;
1328:
1329:                    List resultsNew = m_sql.dbRead(statement, fields,
1330:                            new SqlReader() {
1331:                                public Object readSqlResultRecord(
1332:                                        ResultSet result) {
1333:                                    try {
1334:                                        int count = result.getInt(1);
1335:                                        return new Integer(count);
1336:                                    } catch (SQLException ignore) {
1337:                                        return null;
1338:                                    }
1339:                                }
1340:                            });
1341:
1342:                    boolean rvNew = false;
1343:                    int countNew = -1;
1344:                    if (!resultsNew.isEmpty()) {
1345:                        countNew = ((Integer) resultsNew.get(0)).intValue();
1346:                        rvNew = countNew > 0;
1347:                    }
1348:
1349:                    return rvNew;
1350:                }
1351:
1352:                /**
1353:                 * {@inheritDoc}
1354:                 */
1355:                public boolean isAllowed(String userId, String lock,
1356:                        Collection realms) {
1357:                    if (lock == null)
1358:                        return false;
1359:
1360:                    boolean auth = (userId != null)
1361:                            && (!userDirectoryService().getAnonymousUser()
1362:                                    .getId().equals(userId));
1363:
1364:                    if (realms == null || realms.size() < 1) {
1365:                        M_log.warn("isAllowed(): called with no realms: lock: "
1366:                                + lock + " user: " + userId);
1367:                        if (M_log.isDebugEnabled()) {
1368:                            try {
1369:                                throw new Exception();
1370:                            } catch (Exception e) {
1371:                                M_log.debug("isAllowed():", e);
1372:                            }
1373:                        }
1374:                        return false;
1375:                    }
1376:
1377:                    String inClause = orInClause(realms.size(),
1378:                            "SAKAI_REALM.REALM_ID");
1379:
1380:                    String statement = null;
1381:
1382:                    // any of the grant or role realms
1383:                    if ("mysql".equals(sqlService().getVendor())) {
1384:                        statement = "select count(1) from SAKAI_REALM_RL_FN,SAKAI_REALM force index "
1385:                                + "(AK_SAKAI_REALM_ID) where SAKAI_REALM_RL_FN.REALM_KEY = SAKAI_REALM.REALM_KEY "
1386:                                + "and " + inClause;
1387:                    } else
1388:                    // oracle and hsql
1389:                    {
1390:                        statement = "select count(1) from SAKAI_REALM_RL_FN "
1391:                                + "where REALM_KEY in (select REALM_KEY from SAKAI_REALM where "
1392:                                + inClause + ")";
1393:                    }
1394:                    statement = statement
1395:                            + " and FUNCTION_KEY in (select FUNCTION_KEY from SAKAI_REALM_FUNCTION where FUNCTION_NAME = ?) "
1396:                            + "and (ROLE_KEY in "
1397:                            + "(select ROLE_KEY from SAKAI_REALM_RL_GR where ACTIVE = '1' and USER_ID = ? "
1398:                            +
1399:                            // granted in any of the grant or role realms
1400:                            "and REALM_KEY in (select REALM_KEY from SAKAI_REALM where "
1401:                            + inClause
1402:                            + ")) "
1403:                            + "or ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '"
1404:                            + ANON_ROLE
1405:                            + "') "
1406:                            + (auth ? "or ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '"
1407:                                    + AUTH_ROLE + "') "
1408:                                    : "") + ")";
1409:
1410:                    Object[] fields = new Object[2 + (2 * realms.size())];
1411:                    int pos = 0;
1412:                    for (Iterator i = realms.iterator(); i.hasNext();) {
1413:                        String role = (String) i.next();
1414:                        fields[pos++] = role;
1415:                    }
1416:                    fields[pos++] = lock;
1417:                    fields[pos++] = userId;
1418:                    for (Iterator i = realms.iterator(); i.hasNext();) {
1419:                        String role = (String) i.next();
1420:                        fields[pos++] = role;
1421:                    }
1422:
1423:                    List results = m_sql.dbRead(statement, fields,
1424:                            new SqlReader() {
1425:                                public Object readSqlResultRecord(
1426:                                        ResultSet result) {
1427:                                    try {
1428:                                        int count = result.getInt(1);
1429:                                        return new Integer(count);
1430:                                    } catch (SQLException ignore) {
1431:                                        return null;
1432:                                    }
1433:                                }
1434:                            });
1435:
1436:                    boolean rv = false;
1437:                    int count = -1;
1438:                    if (!results.isEmpty()) {
1439:                        count = ((Integer) results.get(0)).intValue();
1440:                        rv = count > 0;
1441:                    }
1442:
1443:                    return rv;
1444:
1445:                    // return rvNew;
1446:                }
1447:
1448:                /**
1449:                 * {@inheritDoc}
1450:                 */
1451:                public Set getUsersIsAllowed(String lock, Collection realms) {
1452:                    if ((lock == null) || (realms == null)
1453:                            || (realms.isEmpty()))
1454:                        return new HashSet();
1455:
1456:                    String sql = "";
1457:                    StringBuffer sqlBuf = null;
1458:
1459:                    // Assemble SQL
1460:                    sqlBuf = new StringBuffer();
1461:                    sqlBuf.append("select SRRG.USER_ID ");
1462:                    sqlBuf.append("from SAKAI_REALM_RL_GR SRRG ");
1463:                    if ("mysql".equals(sqlService().getVendor())) {
1464:                        sqlBuf
1465:                                .append("inner join SAKAI_REALM SR force index (AK_SAKAI_REALM_ID) ON SRRG.REALM_KEY = SR.REALM_KEY ");
1466:                    } else
1467:                    // oracle and hsql
1468:                    {
1469:                        sqlBuf
1470:                                .append("inner join SAKAI_REALM SR ON SRRG.REALM_KEY = SR.REALM_KEY ");
1471:                    }
1472:                    sqlBuf.append("where "
1473:                            + orInClause(realms.size(), "SR.REALM_ID") + " ");
1474:                    sqlBuf.append("and SRRG.ACTIVE = '1' ");
1475:                    sqlBuf.append("and SRRG.ROLE_KEY in ");
1476:                    sqlBuf.append("(select SRRF.ROLE_KEY ");
1477:                    sqlBuf.append("from SAKAI_REALM_RL_FN SRRF ");
1478:                    sqlBuf
1479:                            .append("inner join SAKAI_REALM_FUNCTION SRF ON SRRF.FUNCTION_KEY = SRF.FUNCTION_KEY ");
1480:                    if ("mysql".equals(sqlService().getVendor())) {
1481:                        sqlBuf
1482:                                .append("inner join SAKAI_REALM SR1 force index (AK_SAKAI_REALM_ID) ON SRRF.REALM_KEY = SR1.REALM_KEY ");
1483:                    } else
1484:                    // oracle and hsql
1485:                    {
1486:                        sqlBuf
1487:                                .append("inner join SAKAI_REALM SR1 ON SRRF.REALM_KEY = SR1.REALM_KEY ");
1488:                    }
1489:                    sqlBuf.append("where SRF.FUNCTION_NAME = ? ");
1490:                    sqlBuf.append("and "
1491:                            + orInClause(realms.size(), "SR1.REALM_ID") + ")");
1492:                    sql = sqlBuf.toString();
1493:
1494:                    Object[] fields = new Object[1 + (2 * realms.size())];
1495:                    int pos = 0;
1496:                    for (Iterator i = realms.iterator(); i.hasNext();) {
1497:                        String roleRealm = (String) i.next();
1498:                        fields[pos++] = roleRealm;
1499:                    }
1500:                    fields[pos++] = lock;
1501:                    for (Iterator i = realms.iterator(); i.hasNext();) {
1502:                        String roleRealm = (String) i.next();
1503:                        fields[pos++] = roleRealm;
1504:                    }
1505:
1506:                    // read the strings
1507:                    List results = m_sql.dbRead(sql, fields, null);
1508:
1509:                    // prepare the return
1510:                    Set rv = new HashSet();
1511:                    rv.addAll(results);
1512:                    return rv;
1513:                }
1514:
1515:                /**
1516:                 * {@inheritDoc}
1517:                 */
1518:                public Set getAllowedFunctions(String role, Collection realms) {
1519:                    if ((role == null) || (realms == null)
1520:                            || (realms.isEmpty()))
1521:                        return new HashSet();
1522:
1523:                    String sql = "";
1524:                    StringBuffer sqlBuf = null;
1525:
1526:                    // Assemble SQL
1527:                    sqlBuf = new StringBuffer();
1528:                    sqlBuf.append("select DISTINCT FUNCTION_NAME ");
1529:                    sqlBuf.append("from SAKAI_REALM_FUNCTION SRF ");
1530:                    sqlBuf
1531:                            .append("inner join SAKAI_REALM_RL_FN SRRF on SRF.FUNCTION_KEY = SRRF.FUNCTION_KEY ");
1532:                    sqlBuf
1533:                            .append("inner join SAKAI_REALM_ROLE SRR on SRRF.ROLE_KEY = SRR.ROLE_KEY ");
1534:                    sqlBuf
1535:                            .append("inner join SAKAI_REALM SR on SRRF.REALM_KEY = SR.REALM_KEY ");
1536:                    sqlBuf.append("where SRR.ROLE_NAME = ? ");
1537:                    sqlBuf.append("and "
1538:                            + orInClause(realms.size(), "SR.REALM_ID"));
1539:                    sql = sqlBuf.toString();
1540:
1541:                    Object[] fields = new Object[1 + realms.size()];
1542:                    fields[0] = role;
1543:                    int pos = 1;
1544:                    for (Iterator i = realms.iterator(); i.hasNext();) {
1545:                        String roleRealm = (String) i.next();
1546:                        fields[pos++] = roleRealm;
1547:                    }
1548:
1549:                    // read the strings
1550:                    List results = m_sql.dbRead(sql, fields, null);
1551:
1552:                    // prepare the return
1553:                    Set rv = new HashSet();
1554:                    rv.addAll(results);
1555:                    return rv;
1556:                }
1557:
1558:                /**
1559:                 * {@inheritDoc}
1560:                 */
1561:                public void refreshUser(String userId, Map providerGrants) {
1562:                    if (userId == null)
1563:                        return;
1564:
1565:                    String sql = "";
1566:                    String sqlParam = "";
1567:                    StringBuffer sqlBuf = null;
1568:                    StringBuffer sqlParamBuf = null;
1569:
1570:                    // read this user's grants from all realms
1571:                    sqlBuf = new StringBuffer();
1572:                    sqlBuf
1573:                            .append("select SRRG.REALM_KEY, SRR.ROLE_NAME, SRRG.ACTIVE, SRRG.PROVIDED ");
1574:                    sqlBuf.append("from SAKAI_REALM_ROLE SRR ");
1575:                    sqlBuf
1576:                            .append("inner join SAKAI_REALM_RL_GR SRRG on SRR.ROLE_KEY = SRRG.ROLE_KEY ");
1577:                    sqlBuf.append("where SRRG.USER_ID = ?");
1578:                    sql = sqlBuf.toString();
1579:
1580:                    Object[] fields = new Object[1];
1581:                    fields[0] = userId;
1582:
1583:                    List grants = m_sql.dbRead(sql, fields, new SqlReader() {
1584:                        public Object readSqlResultRecord(ResultSet result) {
1585:                            try {
1586:                                int realmKey = result.getInt(1);
1587:                                String roleName = result.getString(2);
1588:                                String active = result.getString(3);
1589:                                String provided = result.getString(4);
1590:                                return new RealmAndRole(new Integer(realmKey),
1591:                                        roleName, "1".equals(active), "1"
1592:                                                .equals(provided));
1593:                            } catch (Throwable ignore) {
1594:                                return null;
1595:                            }
1596:                        }
1597:                    });
1598:
1599:                    // make a map, realm id -> role granted, each for provider and non-provider (or inactive)
1600:                    Map<Integer, String> existing = new HashMap<Integer, String>();
1601:                    Map<Integer, String> nonProvider = new HashMap<Integer, String>();
1602:                    for (Iterator i = grants.iterator(); i.hasNext();) {
1603:                        RealmAndRole rar = (RealmAndRole) i.next();
1604:                        // active and provided are the currently stored provider grants
1605:                        if (rar.active && rar.provided) {
1606:                            if (existing.containsKey(rar.realmId)) {
1607:                                M_log
1608:                                        .warn("refreshUser: duplicate realm id found in provider grants: "
1609:                                                + rar.realmId);
1610:                            } else {
1611:                                existing.put(rar.realmId, rar.role);
1612:                            }
1613:                        }
1614:
1615:                        // inactive or not provided are the currently stored internal grants - not to be overwritten by provider info
1616:                        else {
1617:                            if (nonProvider.containsKey(rar.realmId)) {
1618:                                M_log
1619:                                        .warn("refreshUser: duplicate realm id found in nonProvider grants: "
1620:                                                + rar.realmId);
1621:                            } else {
1622:                                nonProvider.put(rar.realmId, rar.role);
1623:                            }
1624:                        }
1625:                    }
1626:
1627:                    // compute the user's realm roles based on the new provider information
1628:                    // same map form as existing, realm id -> role granted
1629:                    Map<Integer, String> target = new HashMap<Integer, String>();
1630:
1631:                    // for each realm that has a provider in the map, and does not have a grant for the user,
1632:                    // add the active provided grant with the map's role.
1633:
1634:                    if ((providerGrants != null) && (providerGrants.size() > 0)) {
1635:                        // get all the realms that have providers in the map, with their full provider id
1636:
1637:                        // Assemble SQL. Note: distinct must be used because one cannot establish an equijoin between
1638:                        // SRP.PROVIDER_ID and SR.PROVIDER_ID as the values in SRP.PROVIDER_ID often include
1639:                        // additional concatenated course values. It may be worth reviewing this strategy.
1640:
1641:                        sqlBuf = new StringBuffer();
1642:                        sqlBuf
1643:                                .append("select distinct SRP.REALM_KEY, SR.PROVIDER_ID ");
1644:                        sqlBuf.append("from SAKAI_REALM_PROVIDER SRP ");
1645:                        sqlBuf
1646:                                .append("inner join SAKAI_REALM SR on SRP.REALM_KEY = SR.REALM_KEY ");
1647:                        sqlBuf.append("where "
1648:                                + orInClause(providerGrants.size(),
1649:                                        "SRP.PROVIDER_ID"));
1650:                        sql = sqlBuf.toString();
1651:
1652:                        Object[] fieldsx = new Object[providerGrants.size()];
1653:                        int pos = 0;
1654:                        for (Iterator f = providerGrants.keySet().iterator(); f
1655:                                .hasNext();) {
1656:                            String providerId = (String) f.next();
1657:                            fieldsx[pos++] = providerId;
1658:                        }
1659:                        List realms = m_sql.dbRead(sql, fieldsx,
1660:                                new SqlReader() {
1661:                                    public Object readSqlResultRecord(
1662:                                            ResultSet result) {
1663:                                        try {
1664:                                            int id = result.getInt(1);
1665:                                            String provider = result
1666:                                                    .getString(2);
1667:                                            return new RealmAndProvider(
1668:                                                    new Integer(id), provider);
1669:                                        } catch (Throwable ignore) {
1670:                                            return null;
1671:                                        }
1672:                                    }
1673:                                });
1674:
1675:                        if ((realms != null) && (realms.size() > 0)) {
1676:                            for (Iterator r = realms.iterator(); r.hasNext();) {
1677:                                RealmAndProvider rp = (RealmAndProvider) r
1678:                                        .next();
1679:                                String role = (String) providerGrants
1680:                                        .get(rp.providerId);
1681:                                if (role != null) {
1682:                                    if (target.containsKey(rp.realmId)) {
1683:                                        M_log
1684:                                                .warn("refreshUser: duplicate realm id computed for new grants: "
1685:                                                        + rp.realmId);
1686:                                    } else {
1687:                                        target.put(rp.realmId, role);
1688:                                    }
1689:                                }
1690:                            }
1691:                        }
1692:                    }
1693:
1694:                    // compute the records we need to delete: every existing not in target or not matching target's role
1695:                    List<Integer> toDelete = new Vector<Integer>();
1696:                    for (Iterator i = existing.entrySet().iterator(); i
1697:                            .hasNext();) {
1698:                        Map.Entry entry = (Map.Entry) i.next();
1699:                        Integer realmId = (Integer) entry.getKey();
1700:                        String role = (String) entry.getValue();
1701:
1702:                        String targetRole = (String) target.get(realmId);
1703:                        if ((targetRole == null) || (!targetRole.equals(role))) {
1704:                            toDelete.add(realmId);
1705:                        }
1706:                    }
1707:
1708:                    // compute the records we need to add: every target not in existing, or not matching's existing's role
1709:                    // we don't insert target grants that would override internal grants
1710:                    List<RealmAndRole> toInsert = new Vector<RealmAndRole>();
1711:                    for (Iterator i = target.entrySet().iterator(); i.hasNext();) {
1712:                        Map.Entry entry = (Map.Entry) i.next();
1713:                        Integer realmId = (Integer) entry.getKey();
1714:                        String role = (String) entry.getValue();
1715:
1716:                        String existingRole = (String) existing.get(realmId);
1717:                        String nonProviderRole = (String) nonProvider
1718:                                .get(realmId);
1719:                        if ((nonProviderRole == null)
1720:                                && ((existingRole == null) || (!existingRole
1721:                                        .equals(role)))) {
1722:                            toInsert.add(new RealmAndRole(realmId, role, true,
1723:                                    true));
1724:                        }
1725:                    }
1726:
1727:                    // if any, do it
1728:                    if ((toDelete.size() > 0) || (toInsert.size() > 0)) {
1729:                        // do these each in their own transaction, to avoid possible deadlock
1730:                        // caused by transactions modifying more than one row at a time.
1731:
1732:                        // delete
1733:                        sql = "delete from SAKAI_REALM_RL_GR where REALM_KEY = ? and USER_ID = ?";
1734:                        fields = new Object[2];
1735:                        fields[1] = userId;
1736:                        for (Iterator i = toDelete.iterator(); i.hasNext();) {
1737:                            Integer realmId = (Integer) i.next();
1738:                            fields[0] = realmId;
1739:                            m_sql.dbWrite(sql, fields);
1740:                        }
1741:
1742:                        // insert
1743:                        sql = "insert into SAKAI_REALM_RL_GR (REALM_KEY, USER_ID, ROLE_KEY, ACTIVE, PROVIDED) "
1744:                                + "values (?, ?, (select ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = ?), '1', '1')";
1745:                        fields = new Object[3];
1746:                        fields[1] = userId;
1747:                        for (Iterator i = toInsert.iterator(); i.hasNext();) {
1748:                            RealmAndRole rar = (RealmAndRole) i.next();
1749:                            fields[0] = rar.realmId;
1750:                            fields[2] = rar.role;
1751:
1752:                            m_sql.dbWrite(sql, fields);
1753:                        }
1754:                    }
1755:                }
1756:
1757:                /**
1758:                 * {@inheritDoc}
1759:                 */
1760:                public void refreshAuthzGroup(BaseAuthzGroup realm) {
1761:                    if ((realm == null) || (m_provider == null))
1762:                        return;
1763:
1764:                    String sql = "";
1765:                    StringBuffer sqlBuf = null;
1766:
1767:                    // Note: the realm is still lazy - we have the realm id but don't need to worry about changing grants
1768:
1769:                    // get the latest userEid -> role name map from the provider
1770:                    Map target = m_provider.getUserRolesForGroup(realm
1771:                            .getProviderGroupId());
1772:
1773:                    // read the realm's grants
1774:                    sqlBuf = new StringBuffer();
1775:                    sqlBuf
1776:                            .append("select SRRG.USER_ID, SRR.ROLE_NAME, SRRG.ACTIVE, SRRG.PROVIDED ");
1777:                    sqlBuf.append("from SAKAI_REALM_RL_GR SRRG ");
1778:                    sqlBuf
1779:                            .append("inner join SAKAI_REALM SR on SRRG. REALM_KEY = SR. REALM_KEY ");
1780:                    sqlBuf
1781:                            .append("inner join SAKAI_REALM_ROLE SRR on SRRG.ROLE_KEY = SRR.ROLE_KEY ");
1782:                    sqlBuf.append("where SR.REALM_ID = ?");
1783:                    sql = sqlBuf.toString();
1784:
1785:                    Object[] fields = new Object[1];
1786:                    fields[0] = caseId(realm.getId());
1787:
1788:                    List grants = m_sql.dbRead(sql, fields, new SqlReader() {
1789:                        public Object readSqlResultRecord(ResultSet result) {
1790:                            try {
1791:                                String userId = result.getString(1);
1792:                                String roleName = result.getString(2);
1793:                                String active = result.getString(3);
1794:                                String provided = result.getString(4);
1795:                                return new UserAndRole(userId, roleName, "1"
1796:                                        .equals(active), "1".equals(provided));
1797:                            } catch (Throwable ignore) {
1798:                                return null;
1799:                            }
1800:                        }
1801:                    });
1802:
1803:                    // make a map, user id -> role granted, each for provider and non-provider (or inactive)
1804:                    Map<String, String> existing = new HashMap<String, String>();
1805:                    Map<String, String> nonProvider = new HashMap<String, String>();
1806:                    for (Iterator i = grants.iterator(); i.hasNext();) {
1807:                        UserAndRole uar = (UserAndRole) i.next();
1808:
1809:                        // active and provided are the currently stored provider grants
1810:                        if (uar.active && uar.provided) {
1811:                            if (existing.containsKey(uar.userId)) {
1812:                                M_log
1813:                                        .warn("refreshRealm: duplicate user id found in provider grants: "
1814:                                                + uar.userId);
1815:                            } else {
1816:                                existing.put(uar.userId, uar.role);
1817:                            }
1818:                        }
1819:
1820:                        // inactive or not provided are the currently stored internal grants - not to be overwritten by provider info
1821:                        else {
1822:                            if (nonProvider.containsKey(uar.userId)) {
1823:                                M_log
1824:                                        .warn("refreshRealm: duplicate user id found in nonProvider grants: "
1825:                                                + uar.userId);
1826:                            } else {
1827:                                nonProvider.put(uar.userId, uar.role);
1828:                            }
1829:                        }
1830:                    }
1831:
1832:                    // compute the records we need to delete: every existing not in target or not matching target's role
1833:                    List<String> toDelete = new Vector<String>();
1834:                    for (Iterator i = existing.entrySet().iterator(); i
1835:                            .hasNext();) {
1836:                        Map.Entry entry = (Map.Entry) i.next();
1837:                        String userId = (String) entry.getKey();
1838:                        String role = (String) entry.getValue();
1839:
1840:                        try {
1841:                            String userEid = userDirectoryService().getUserEid(
1842:                                    userId);
1843:                            String targetRole = (String) target.get(userEid);
1844:                            if ((targetRole == null)
1845:                                    || (!targetRole.equals(role))) {
1846:                                toDelete.add(userId);
1847:                            }
1848:                        } catch (UserNotDefinedException e) {
1849:                            M_log
1850:                                    .warn("refreshAuthzGroup: cannot find eid for user: "
1851:                                            + userId);
1852:                        }
1853:                    }
1854:
1855:                    // compute the records we need to add: every target not in existing, or not matching's existing's role
1856:                    // we don't insert target grants that would override internal grants
1857:                    List<UserAndRole> toInsert = new Vector<UserAndRole>();
1858:                    for (Iterator i = target.entrySet().iterator(); i.hasNext();) {
1859:                        Map.Entry entry = (Map.Entry) i.next();
1860:                        String userEid = (String) entry.getKey();
1861:                        try {
1862:                            String userId = userDirectoryService().getUserId(
1863:                                    userEid);
1864:
1865:                            String role = (String) entry.getValue();
1866:
1867:                            String existingRole = (String) existing.get(userId);
1868:                            String nonProviderRole = (String) nonProvider
1869:                                    .get(userId);
1870:                            if ((nonProviderRole == null)
1871:                                    && ((existingRole == null) || (!existingRole
1872:                                            .equals(role)))) {
1873:                                toInsert.add(new UserAndRole(userId, role,
1874:                                        true, true));
1875:                            }
1876:                        } catch (UserNotDefinedException e) {
1877:                            M_log
1878:                                    .warn("refreshAuthzGroup: cannot find id for user eid: "
1879:                                            + userEid);
1880:                        }
1881:                    }
1882:
1883:                    // if any, do it
1884:                    if ((toDelete.size() > 0) || (toInsert.size() > 0)) {
1885:                        // do these each in their own transaction, to avoid possible deadlock
1886:                        // caused by transactions modifying more than one row at a time.
1887:
1888:                        // delete
1889:                        if ("mysql".equals(sqlService().getVendor())) {
1890:                            sql = "DELETE SAKAI_REALM_RL_GR FROM SAKAI_REALM_RL_GR INNER JOIN SAKAI_REALM"
1891:                                    + " ON SAKAI_REALM_RL_GR.REALM_KEY = SAKAI_REALM.REALM_KEY AND SAKAI_REALM.REALM_ID = ?"
1892:                                    + " WHERE SAKAI_REALM_RL_GR.USER_ID = ?";
1893:                        } else {
1894:                            sql = "DELETE FROM SAKAI_REALM_RL_GR "
1895:                                    + " WHERE REALM_KEY IN (SELECT REALM_KEY FROM SAKAI_REALM WHERE REALM_ID = ?)"
1896:                                    + " AND USER_ID = ?";
1897:                        }
1898:                        fields = new Object[2];
1899:                        fields[0] = caseId(realm.getId());
1900:                        for (Iterator i = toDelete.iterator(); i.hasNext();) {
1901:                            String userId = (String) i.next();
1902:                            fields[1] = userId;
1903:                            m_sql.dbWrite(sql, fields);
1904:                        }
1905:
1906:                        // insert
1907:                        sql = "insert into SAKAI_REALM_RL_GR (REALM_KEY, USER_ID, ROLE_KEY, ACTIVE, PROVIDED)"
1908:                                + " values ((select REALM_KEY from SAKAI_REALM where REALM_ID = ?), ?, (select ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = ?), '1', '1')";
1909:                        fields = new Object[3];
1910:                        fields[0] = caseId(realm.getId());
1911:                        for (Iterator i = toInsert.iterator(); i.hasNext();) {
1912:                            UserAndRole uar = (UserAndRole) i.next();
1913:                            fields[1] = uar.userId;
1914:                            fields[2] = uar.role;
1915:
1916:                            m_sql.dbWrite(sql, fields);
1917:                        }
1918:                    }
1919:                }
1920:
1921:                public class RealmAndProvider {
1922:                    public Integer realmId;
1923:
1924:                    public String providerId;
1925:
1926:                    public RealmAndProvider(Integer id, String provider) {
1927:                        this .realmId = id;
1928:                        this .providerId = provider;
1929:                    }
1930:                }
1931:
1932:                public class RealmAndRole {
1933:                    public Integer realmId;
1934:
1935:                    public String role;
1936:
1937:                    boolean active;
1938:
1939:                    boolean provided;
1940:
1941:                    public RealmAndRole(Integer id, String role,
1942:                            boolean active, boolean provided) {
1943:                        this .realmId = id;
1944:                        this .role = role;
1945:                        this .active = active;
1946:                        this .provided = provided;
1947:                    }
1948:
1949:                    public boolean equals(Object obj) {
1950:                        if (!(obj instanceof  RealmAndRole))
1951:                            return false;
1952:                        if (this  == obj)
1953:                            return true;
1954:                        RealmAndRole other = (RealmAndRole) obj;
1955:                        if (StringUtil.different(this .role, other.role))
1956:                            return false;
1957:                        if (this .provided != other.provided)
1958:                            return false;
1959:                        if (this .active != other.active)
1960:                            return false;
1961:                        if (((this .realmId == null) && (other.realmId != null))
1962:                                || ((this .realmId != null) && (other.realmId == null))
1963:                                || ((this .realmId != null)
1964:                                        && (other.realmId != null) && (!this .realmId
1965:                                        .equals(other.realmId))))
1966:                            return false;
1967:                        return true;
1968:                    }
1969:
1970:                    public int hashCode() {
1971:                        return (this .role
1972:                                + Boolean.valueOf(this .provided).toString()
1973:                                + Boolean.valueOf(this .active).toString() + this .realmId)
1974:                                .hashCode();
1975:                    }
1976:                }
1977:
1978:                public class UserAndRole {
1979:                    public String userId;
1980:
1981:                    public String role;
1982:
1983:                    boolean active;
1984:
1985:                    boolean provided;
1986:
1987:                    public UserAndRole(String userId, String role,
1988:                            boolean active, boolean provided) {
1989:                        this .userId = userId;
1990:                        this .role = role;
1991:                        this .active = active;
1992:                        this .provided = provided;
1993:                    }
1994:
1995:                    public boolean equals(Object obj) {
1996:                        if (!(obj instanceof  UserAndRole))
1997:                            return false;
1998:                        if (this  == obj)
1999:                            return true;
2000:                        UserAndRole other = (UserAndRole) obj;
2001:                        if (StringUtil.different(this .role, other.role))
2002:                            return false;
2003:                        if (this .provided != other.provided)
2004:                            return false;
2005:                        if (this .active != other.active)
2006:                            return false;
2007:                        if (StringUtil.different(this .userId, other.userId))
2008:                            return false;
2009:                        return true;
2010:                    }
2011:
2012:                    public int hashCode() {
2013:                        return (this .role
2014:                                + Boolean.valueOf(this .provided).toString()
2015:                                + Boolean.valueOf(this .active).toString() + this .userId)
2016:                                .hashCode();
2017:                    }
2018:                }
2019:
2020:                public class RoleAndFunction {
2021:                    public String role;
2022:
2023:                    public String function;
2024:
2025:                    public RoleAndFunction(String role, String function) {
2026:                        this .role = role;
2027:                        this .function = function;
2028:                    }
2029:
2030:                    public boolean equals(Object obj) {
2031:                        if (!(obj instanceof  RoleAndFunction))
2032:                            return false;
2033:                        if (this  == obj)
2034:                            return true;
2035:                        RoleAndFunction other = (RoleAndFunction) obj;
2036:                        if (StringUtil.different(this .role, other.role))
2037:                            return false;
2038:                        if (StringUtil.different(this .function, other.function))
2039:                            return false;
2040:                        return true;
2041:                    }
2042:
2043:                    public int hashCode() {
2044:                        return (this .role + this .function).hashCode();
2045:                    }
2046:                }
2047:
2048:                public class RoleAndDescription {
2049:                    public String role;
2050:
2051:                    public String description;
2052:
2053:                    public boolean providerOnly;
2054:
2055:                    public RoleAndDescription(String role, String description,
2056:                            boolean providerOnly) {
2057:                        this .role = role;
2058:                        this .description = description;
2059:                        this .providerOnly = providerOnly;
2060:                    }
2061:
2062:                    public boolean equals(Object obj) {
2063:                        if (!(obj instanceof  RoleAndDescription))
2064:                            return false;
2065:                        if (this  == obj)
2066:                            return true;
2067:                        RoleAndDescription other = (RoleAndDescription) obj;
2068:                        if (StringUtil.different(this .role, other.role))
2069:                            return false;
2070:                        if (StringUtil.different(this .description,
2071:                                other.description))
2072:                            return false;
2073:                        if (this .providerOnly != other.providerOnly)
2074:                            return false;
2075:                        return true;
2076:                    }
2077:
2078:                    public int hashCode() {
2079:                        return (this .role + this .description + Boolean.valueOf(
2080:                                this .providerOnly).toString()).hashCode();
2081:                    }
2082:                }
2083:
2084:                /**
2085:                 * {@inheritDoc}
2086:                 */
2087:                public String getUserRole(String userId, String azGroupId) {
2088:                    if ((userId == null) || (azGroupId == null))
2089:                        return null;
2090:
2091:                    String sql = "select SRR.ROLE_NAME from SAKAI_REALM_RL_GR SRRG "
2092:                            + "inner join SAKAI_REALM SR on SRRG.REALM_KEY = SR.REALM_KEY "
2093:                            + "inner join SAKAI_REALM_ROLE SRR on SRRG.ROLE_KEY = SRR.ROLE_KEY "
2094:                            + "where SR.REALM_ID = ? and SRRG.USER_ID = ? and SRRG.ACTIVE = '1'";
2095:
2096:                    Object[] fields = new Object[2];
2097:                    fields[0] = azGroupId;
2098:                    fields[1] = userId;
2099:
2100:                    // read the string
2101:                    List results = m_sql.dbRead(sql, fields, null);
2102:
2103:                    // prepare the return
2104:                    String rv = null;
2105:                    if ((results != null) && (!results.isEmpty())) {
2106:                        rv = (String) results.get(0);
2107:                        if (results.size() > 1) {
2108:                            M_log.warn("getUserRole: user: " + userId
2109:                                    + " multiple roles");
2110:                        }
2111:                    }
2112:
2113:                    return rv;
2114:                }
2115:
2116:                /**
2117:                 * {@inheritDoc}
2118:                 */
2119:                public Map getUsersRole(Collection userIds, String azGroupId) {
2120:                    if ((userIds == null) || (userIds.isEmpty())
2121:                            || (azGroupId == null)) {
2122:                        return new HashMap();
2123:                    }
2124:
2125:                    String inClause = orInClause(userIds.size(), "SRRG.USER_ID");
2126:
2127:                    String sql = "select SRRG.USER_ID, SRR.ROLE_NAME from SAKAI_REALM_RL_GR SRRG "
2128:                            + "inner join SAKAI_REALM SR on SRRG.REALM_KEY = SR.REALM_KEY "
2129:                            + "inner join SAKAI_REALM_ROLE SRR on SRRG.ROLE_KEY = SRR.ROLE_KEY "
2130:                            + "where SR.REALM_ID = ? and "
2131:                            + inClause
2132:                            + " and SRRG.ACTIVE = '1'";
2133:
2134:                    Object[] fields = new Object[1 + userIds.size()];
2135:                    fields[0] = azGroupId;
2136:                    int pos = 1;
2137:                    for (Iterator i = userIds.iterator(); i.hasNext();) {
2138:                        fields[pos++] = i.next();
2139:                    }
2140:
2141:                    // the return
2142:                    final Map rv = new HashMap();
2143:
2144:                    // read
2145:                    m_sql.dbRead(sql, fields, new SqlReader() {
2146:                        public Object readSqlResultRecord(ResultSet result) {
2147:                            try {
2148:                                // read the results
2149:                                String userId = result.getString(1);
2150:                                String role = result.getString(2);
2151:
2152:                                if ((userId != null) && (role != null)) {
2153:                                    rv.put(userId, role);
2154:                                }
2155:                            } catch (Throwable t) {
2156:                            }
2157:
2158:                            return null;
2159:                        }
2160:                    });
2161:
2162:                    return rv;
2163:                }
2164:
2165:            } // DbStorage
2166:
2167:            /** To avoide the dreaded ORA-01795 and the like, we need to limit to <100 the items in each in(?, ?, ...) clause, connecting them with ORs. */
2168:            protected final static int MAX_IN_CLAUSE = 99;
2169:
2170:            /**
2171:             * Form a SQL IN() clause, but break it up with ORs to keep the size of each IN below 100
2172:             * 
2173:             * @param size
2174:             *        The size
2175:             * @param field
2176:             *        The field name
2177:             * @return a SQL IN() with ORs clause this large.
2178:             */
2179:            protected String orInClause(int size, String field) {
2180:                // Note: to avoide the dreaded ORA-01795 and the like, we need to limit to <100 the items in each in(?, ?, ...) clause, connecting them with ORs -ggolden
2181:                int ors = size / MAX_IN_CLAUSE;
2182:                int leftover = size - (ors * MAX_IN_CLAUSE);
2183:                StringBuffer buf = new StringBuffer();
2184:
2185:                // enclose them all in parens if we have > 1
2186:                if (ors > 0) {
2187:                    buf.append(" (");
2188:                }
2189:
2190:                buf.append(" " + field + " IN ");
2191:
2192:                // do all the full MAX_IN_CLAUSE '?' in/ors
2193:                if (ors > 0) {
2194:                    for (int i = 0; i < ors; i++) {
2195:                        buf.append("(?");
2196:                        for (int j = 1; j < MAX_IN_CLAUSE; j++) {
2197:                            buf.append(",?");
2198:                        }
2199:                        buf.append(")");
2200:
2201:                        if (i < ors - 1) {
2202:                            buf.append(" OR " + field + " IN ");
2203:                        }
2204:                    }
2205:                }
2206:
2207:                // add one more for the extra
2208:                if (leftover > 0) {
2209:                    if (ors > 0) {
2210:                        buf.append(" OR " + field + " IN ");
2211:                    }
2212:                    buf.append("(?");
2213:                    for (int i = 1; i < leftover; i++) {
2214:                        buf.append(",?");
2215:                    }
2216:                    buf.append(")");
2217:                }
2218:
2219:                // enclose them all in parens if we have > 1
2220:                if (ors > 0) {
2221:                    buf.append(" )");
2222:                }
2223:
2224:                return buf.toString();
2225:            }
2226:        }
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.