0001: /**********************************************************************************
0002: * $URL: https://source.sakaiproject.org/svn/site/tags/sakai_2-4-1/site-impl/impl/src/java/org/sakaiproject/site/impl/DbSiteService.java $
0003: * $Id: DbSiteService.java 22828 2007-03-17 19:00:21Z 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.site.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.HashSet;
0027: import java.util.Iterator;
0028: import java.util.List;
0029: import java.util.Map;
0030: import java.util.Properties;
0031: import java.util.Set;
0032:
0033: import org.apache.commons.logging.Log;
0034: import org.apache.commons.logging.LogFactory;
0035: import org.sakaiproject.db.api.SqlReader;
0036: import org.sakaiproject.db.api.SqlService;
0037: import org.sakaiproject.db.api.SqlServiceDeadlockException;
0038: import org.sakaiproject.entity.api.ResourcePropertiesEdit;
0039: import org.sakaiproject.exception.IdUsedException;
0040: import org.sakaiproject.javax.PagingPosition;
0041: import org.sakaiproject.site.api.Group;
0042: import org.sakaiproject.site.api.Site;
0043: import org.sakaiproject.site.api.SitePage;
0044: import org.sakaiproject.site.api.ToolConfiguration;
0045: import org.sakaiproject.time.api.Time;
0046: import org.sakaiproject.util.BaseDbFlatStorage;
0047: import org.sakaiproject.util.BaseResourcePropertiesEdit;
0048: import org.sakaiproject.util.StringUtil;
0049:
0050: /**
0051: * <p>
0052: * DbSiteService is an extension of the BaseSiteService with a database storage.
0053: * </p>
0054: */
0055: public abstract class DbSiteService extends BaseSiteService {
0056: /** Our logger. */
0057: private static Log M_log = LogFactory.getLog(DbSiteService.class);
0058:
0059: /** Table name for sites. */
0060: protected String m_siteTableName = "SAKAI_SITE";
0061:
0062: /** Table name for site properties. */
0063: protected String m_sitePropTableName = "SAKAI_SITE_PROPERTY";
0064:
0065: /** ID field for site. */
0066: protected String m_siteIdFieldName = "SITE_ID";
0067:
0068: /** Site sort field. */
0069: protected String m_siteSortField = "TITLE";
0070:
0071: /** All fields for site. */
0072: protected String[] m_siteFieldNames = { "SITE_ID", "TITLE", "TYPE",
0073: "SHORT_DESC", "DESCRIPTION", "ICON_URL", "INFO_URL",
0074: "SKIN", "PUBLISHED", "JOINABLE", "PUBVIEW", "JOIN_ROLE",
0075: "IS_SPECIAL", "IS_USER", "CREATEDBY", "MODIFIEDBY",
0076: "CREATEDON", "MODIFIEDON", "CUSTOM_PAGE_ORDERED" };
0077:
0078: /**********************************************************************************************************************************************************************************************************************************************************
0079: * Dependencies
0080: *********************************************************************************************************************************************************************************************************************************************************/
0081:
0082: /**
0083: * @return the MemoryService collaborator.
0084: */
0085: protected abstract SqlService sqlService();
0086:
0087: /**********************************************************************************************************************************************************************************************************************************************************
0088: * Configuration
0089: *********************************************************************************************************************************************************************************************************************************************************/
0090:
0091: /** If true, we do our locks in the remote database, otherwise we do them here. */
0092: protected boolean m_useExternalLocks = true;
0093:
0094: /**
0095: * Configuration: set the external locks value.
0096: *
0097: * @param value
0098: * The external locks value.
0099: */
0100: public void setExternalLocks(String value) {
0101: m_useExternalLocks = new Boolean(value).booleanValue();
0102: }
0103:
0104: /** Configuration: to run the ddl on init or not. */
0105: protected boolean m_autoDdl = false;
0106:
0107: /**
0108: * Configuration: to run the ddl on init or not.
0109: *
0110: * @param value
0111: * the auto ddl value.
0112: */
0113: public void setAutoDdl(String value) {
0114: m_autoDdl = new Boolean(value).booleanValue();
0115: }
0116:
0117: /**********************************************************************************************************************************************************************************************************************************************************
0118: * Init and Destroy
0119: *********************************************************************************************************************************************************************************************************************************************************/
0120:
0121: /**
0122: * Final initialization, once all dependencies are set.
0123: */
0124: public void init() {
0125: try {
0126: // if we are auto-creating our schema, check and create
0127: if (m_autoDdl) {
0128: sqlService().ddl(this .getClass().getClassLoader(),
0129: "sakai_site");
0130:
0131: // also load the 2.1 new site database tables
0132: sqlService().ddl(this .getClass().getClassLoader(),
0133: "sakai_site_group");
0134:
0135: // also load the 2.1.0.003 field insertion
0136: sqlService().ddl(this .getClass().getClassLoader(),
0137: "sakai_site_2_1_0_003");
0138:
0139: // also load the 2.4.0 field insertion
0140: sqlService().ddl(this .getClass().getClassLoader(),
0141: "sakai_site_2_4_0_001");
0142: }
0143:
0144: super .init();
0145:
0146: M_log.info("init(): site table: " + m_siteTableName
0147: + " external locks: " + m_useExternalLocks);
0148: } catch (Throwable t) {
0149: M_log.warn("init(): ", t);
0150: }
0151: }
0152:
0153: /**********************************************************************************************************************************************************************************************************************************************************
0154: * BaseSiteService extensions
0155: *********************************************************************************************************************************************************************************************************************************************************/
0156:
0157: /**
0158: * Construct a Storage object.
0159: *
0160: * @return The new storage object.
0161: */
0162: protected Storage newStorage() {
0163: return new DbStorage(this );
0164: }
0165:
0166: /**********************************************************************************************************************************************************************************************************************************************************
0167: * Storage implementation
0168: *********************************************************************************************************************************************************************************************************************************************************/
0169:
0170: protected class DbStorage extends BaseDbFlatStorage implements
0171: Storage, SqlReader {
0172: /** A prior version's storage model. */
0173: protected Storage m_oldStorage = null;
0174:
0175: /** The service. */
0176: protected BaseSiteService m_service = null;
0177:
0178: /**
0179: * Construct.
0180: *
0181: * @param user
0182: * The StorageUser class to call back for creation of Resource and Edit objects.
0183: */
0184: public DbStorage(BaseSiteService service) {
0185: super (m_siteTableName, m_siteIdFieldName, m_siteFieldNames,
0186: m_sitePropTableName, m_useExternalLocks, null,
0187: sqlService());
0188: m_reader = this ;
0189:
0190: m_service = service;
0191:
0192: setSortField(m_siteSortField, null);
0193:
0194: // no locking
0195: setLocking(false);
0196: }
0197:
0198: public boolean check(String id) {
0199: return super .checkResource(id);
0200: }
0201:
0202: public Site get(String id) {
0203: return (Site) super .getResource(id);
0204: }
0205:
0206: public List getAll() {
0207: return super .getAllResources();
0208: }
0209:
0210: public Site put(String id) {
0211: // check for already exists
0212: if (check(id))
0213: return null;
0214:
0215: BaseSite rv = (BaseSite) super .putResource(id, fields(id,
0216: null, false));
0217: if (rv != null)
0218: rv.activate();
0219: return rv;
0220: }
0221:
0222: public void save(final Site edit) {
0223: // run our save code in a transaction that will restart on deadlock
0224: // if deadlock retry fails, or any other error occurs, a runtime error will be thrown
0225: m_sql.transact(new Runnable() {
0226: public void run() {
0227: saveTx(edit);
0228: }
0229: }, "site:" + edit.getId());
0230: }
0231:
0232: /**
0233: * The transaction code to save a site.
0234: *
0235: * @param edit
0236: * The site to save.
0237: */
0238: protected void saveTx(Site edit) {
0239: // TODO: (SAK-8669) re-write to commit only the diff, not a complete delete / add -ggolden
0240:
0241: // write the pages, tools, properties,
0242: // and then commit the site and release the lock
0243:
0244: // delete the pages, tools, page properties, tool properties
0245: Object fields[] = new Object[1];
0246: fields[0] = caseId(edit.getId());
0247:
0248: String statement = "delete from SAKAI_SITE_TOOL_PROPERTY where SITE_ID = ?";
0249: m_sql.dbWrite(statement, fields);
0250:
0251: statement = "delete from SAKAI_SITE_TOOL where SITE_ID = ?";
0252: m_sql.dbWrite(statement, fields);
0253:
0254: statement = "delete from SAKAI_SITE_PAGE_PROPERTY where SITE_ID = ?";
0255: m_sql.dbWrite(statement, fields);
0256:
0257: statement = "delete from SAKAI_SITE_PAGE where SITE_ID = ?";
0258: m_sql.dbWrite(statement, fields);
0259:
0260: statement = "delete from SAKAI_SITE_GROUP_PROPERTY where SITE_ID = ?";
0261: m_sql.dbWrite(statement, fields);
0262:
0263: statement = "delete from SAKAI_SITE_GROUP where SITE_ID = ?";
0264: m_sql.dbWrite(statement, fields);
0265:
0266: // since we've already deleted the old values, don't delete them again.
0267: boolean deleteAgain = false;
0268:
0269: // add each page
0270: int pageOrder = 1;
0271: for (Iterator iPages = edit.getPages().iterator(); iPages
0272: .hasNext();) {
0273: SitePage page = (SitePage) iPages.next();
0274:
0275: // write the page
0276: statement = "insert into SAKAI_SITE_PAGE (PAGE_ID, SITE_ID, TITLE, LAYOUT, POPUP, SITE_ORDER)"
0277: + " values (?,?,?,?,?,?)";
0278:
0279: fields = new Object[6];
0280: fields[0] = page.getId();
0281: fields[1] = caseId(edit.getId());
0282: fields[2] = page.getTitle();
0283: fields[3] = Integer.toString(page.getLayout());
0284: fields[4] = ((((BaseSitePage) page).m_popup) ? "1"
0285: : "0");
0286: fields[5] = new Integer(pageOrder++);
0287: m_sql.dbWrite(statement, fields);
0288:
0289: // write the page's properties
0290: writeProperties("SAKAI_SITE_PAGE_PROPERTY", "PAGE_ID",
0291: page.getId(), "SITE_ID", caseId(edit.getId()),
0292: page.getProperties(), deleteAgain);
0293:
0294: // write the tools
0295: int toolOrder = 1;
0296: for (Iterator iTools = page.getTools().iterator(); iTools
0297: .hasNext();) {
0298: ToolConfiguration tool = (ToolConfiguration) iTools
0299: .next();
0300:
0301: // write the tool
0302: statement = "insert into SAKAI_SITE_TOOL (TOOL_ID, PAGE_ID, SITE_ID, REGISTRATION, PAGE_ORDER, TITLE, LAYOUT_HINTS)"
0303: + " values (?,?,?,?,?,?,?)";
0304:
0305: fields = new Object[7];
0306: fields[0] = tool.getId();
0307: fields[1] = page.getId();
0308: fields[2] = caseId(edit.getId());
0309: fields[3] = tool.getToolId();
0310: fields[4] = new Integer(toolOrder++);
0311: fields[5] = tool.getTitle();
0312: fields[6] = tool.getLayoutHints();
0313: m_sql.dbWrite(statement, fields);
0314:
0315: // write the tool's properties
0316: writeProperties("SAKAI_SITE_TOOL_PROPERTY",
0317: "TOOL_ID", tool.getId(), "SITE_ID",
0318: caseId(edit.getId()), tool
0319: .getPlacementConfig(), deleteAgain);
0320: }
0321: }
0322:
0323: // add each group
0324: for (Iterator iGroups = edit.getGroups().iterator(); iGroups
0325: .hasNext();) {
0326: Group group = (Group) iGroups.next();
0327:
0328: // write the group
0329: statement = "insert into SAKAI_SITE_GROUP (GROUP_ID, SITE_ID, TITLE, DESCRIPTION)"
0330: + " values (?,?,?,?)";
0331:
0332: fields = new Object[4];
0333: fields[0] = group.getId();
0334: fields[1] = caseId(edit.getId());
0335: fields[2] = group.getTitle();
0336: fields[3] = group.getDescription();
0337: m_sql.dbWrite(statement, fields);
0338:
0339: // write the group's properties
0340: writeProperties("SAKAI_SITE_GROUP_PROPERTY",
0341: "GROUP_ID", group.getId(), "SITE_ID",
0342: caseId(edit.getId()), group.getProperties(),
0343: deleteAgain);
0344: }
0345:
0346: // write the site and properties, releasing the lock
0347: super .commitResource(edit,
0348: fields(edit.getId(), edit, true), edit
0349: .getProperties(), null);
0350: }
0351:
0352: /**
0353: * @inheritDoc
0354: */
0355: public void saveInfo(String siteId, String description,
0356: String infoUrl) {
0357: String statement = "update "
0358: + m_siteTableName
0359: + " set DESCRIPTION = ?, INFO_URL = ? where SITE_ID = ?";
0360:
0361: Object fields[] = new Object[3];
0362: fields[0] = description;
0363: fields[1] = infoUrl;
0364: fields[2] = caseId(siteId);
0365:
0366: m_sql.dbWrite(statement, fields);
0367: }
0368:
0369: /**
0370: * @inheritDoc
0371: */
0372: public void saveToolConfig(final ToolConfiguration tool) {
0373: // in a transaction
0374: m_sql.transact(new Runnable() {
0375: public void run() {
0376: saveToolConfigTx(tool);
0377: }
0378: }, "siteToolConfig:" + tool.getId());
0379: }
0380:
0381: /**
0382: * The transactino code for saving a tool config.
0383: */
0384: protected void saveToolConfigTx(ToolConfiguration tool) {
0385: // delete this tool and tool properties
0386: Object fields[] = new Object[2];
0387: fields[0] = caseId(tool.getSiteId());
0388: fields[1] = caseId(tool.getId());
0389:
0390: String statement = "delete from SAKAI_SITE_TOOL_PROPERTY where SITE_ID = ? and TOOL_ID = ?";
0391: m_sql.dbWrite(statement, fields);
0392:
0393: statement = "delete from SAKAI_SITE_TOOL where SITE_ID = ? and TOOL_ID = ?";
0394: m_sql.dbWrite(statement, fields);
0395:
0396: // write the tool
0397: statement = "insert into SAKAI_SITE_TOOL (TOOL_ID, PAGE_ID, SITE_ID, REGISTRATION, PAGE_ORDER, TITLE, LAYOUT_HINTS)"
0398: + " values (?,?,?,?,?,?,?)";
0399:
0400: fields = new Object[7];
0401: fields[0] = tool.getId();
0402: fields[1] = tool.getPageId();
0403: fields[2] = caseId(tool.getSiteId());
0404: fields[3] = tool.getToolId();
0405: fields[4] = new Integer(tool.getPageOrder());
0406: fields[5] = tool.getTitle();
0407: fields[6] = tool.getLayoutHints();
0408: m_sql.dbWrite(statement, fields);
0409:
0410: // write the tool's properties
0411: writeProperties("SAKAI_SITE_TOOL_PROPERTY", "TOOL_ID", tool
0412: .getId(), "SITE_ID", caseId(tool.getSiteId()), tool
0413: .getPlacementConfig());
0414: }
0415:
0416: /**
0417: * @inheritDoc
0418: */
0419: public void remove(final Site edit) {
0420: // in a transaction
0421: m_sql.transact(new Runnable() {
0422: public void run() {
0423: removeTx(edit);
0424: }
0425: }, "siteRemove:" + edit.getId());
0426: }
0427:
0428: /**
0429: * Transaction code to remove a site.
0430: */
0431: protected void removeTx(Site edit) {
0432: // delete all the pages, tools, properties, permissions
0433: // and then the site and release the lock
0434:
0435: // delete the pages, tools, page properties, tool properties, permissions
0436: Object fields[] = new Object[1];
0437: fields[0] = caseId(edit.getId());
0438:
0439: String statement = "delete from SAKAI_SITE_TOOL_PROPERTY where SITE_ID = ?";
0440: m_sql.dbWrite(statement, fields);
0441:
0442: statement = "delete from SAKAI_SITE_TOOL where SITE_ID = ?";
0443: m_sql.dbWrite(statement, fields);
0444:
0445: statement = "delete from SAKAI_SITE_PAGE_PROPERTY where SITE_ID = ?";
0446: m_sql.dbWrite(statement, fields);
0447:
0448: statement = "delete from SAKAI_SITE_PAGE where SITE_ID = ?";
0449: m_sql.dbWrite(statement, fields);
0450:
0451: statement = "delete from SAKAI_SITE_USER where SITE_ID = ?";
0452: m_sql.dbWrite(statement, fields);
0453:
0454: statement = "delete from SAKAI_SITE_GROUP_PROPERTY where SITE_ID = ?";
0455: m_sql.dbWrite(statement, fields);
0456:
0457: statement = "delete from SAKAI_SITE_GROUP where SITE_ID = ?";
0458: m_sql.dbWrite(statement, fields);
0459:
0460: // delete the site and properties
0461: super .removeResource(edit, null);
0462: }
0463:
0464: public int count() {
0465: return super .countAllResources();
0466: }
0467:
0468: /**
0469: * {@inheritDoc}
0470: */
0471: public List getSites(SelectionType type, Object ofType,
0472: String criteria, Map propertyCriteria, SortType sort,
0473: PagingPosition page) {
0474: // Note: super users are not treated any differently - they get only those sites they have permission for,
0475: // not based on super user status
0476:
0477: // if we are joining, start our where with the join clauses
0478: StringBuffer where = new StringBuffer();
0479: if ((type == SelectionType.ACCESS)
0480: || (type == SelectionType.UPDATE)) {
0481: // join on site id and also select the proper user
0482: where
0483: .append("SAKAI_SITE.SITE_ID = SAKAI_SITE_USER.SITE_ID and SAKAI_SITE_USER.USER_ID = ? and ");
0484: }
0485:
0486: // ignore user sites
0487: if (type.isIgnoreUser())
0488: where.append("SAKAI_SITE.IS_USER = '0' and ");
0489: // reject special sites
0490: if (type.isIgnoreSpecial())
0491: where.append("SAKAI_SITE.IS_SPECIAL = '0' and ");
0492: // reject unpublished sites
0493: if (type.isIgnoreUnpublished())
0494: where.append("SAKAI_SITE.PUBLISHED = 1 and ");
0495:
0496: if (ofType != null) {
0497: if (ofType.getClass().equals(String.class)) {
0498: // type criteria is a simple String value
0499: where.append("SAKAI_SITE.TYPE = ? and ");
0500: } else if (ofType instanceof String[]
0501: || ofType instanceof List
0502: || ofType instanceof Set) {
0503: // more complex type criteria
0504: int size = 0;
0505: if (ofType instanceof String[]) {
0506: size = ((String[]) ofType).length;
0507: } else if (ofType instanceof List) {
0508: size = ((List) ofType).size();
0509: } else if (ofType instanceof Set) {
0510: size = ((Set) ofType).size();
0511: }
0512: if (size > 0) {
0513: where.append("SAKAI_SITE.TYPE IN (?");
0514: for (int i = 1; i < size; i++) {
0515: where.append(",?");
0516: }
0517: where.append(") and ");
0518: }
0519: }
0520: }
0521:
0522: // reject non-joinable sites
0523: if (type == SelectionType.JOINABLE)
0524: where.append("SAKAI_SITE.JOINABLE = '1' and ");
0525: // check for pub view status
0526: if (type == SelectionType.PUBVIEW)
0527: where.append("SAKAI_SITE.PUBVIEW = '1' and ");
0528: // check criteria
0529: if (criteria != null)
0530: where
0531: .append("UPPER(SAKAI_SITE.TITLE) like UPPER(?) and ");
0532: // update permission
0533: if (type == SelectionType.UPDATE)
0534: where.append("SAKAI_SITE_USER.PERMISSION <= -1 and ");
0535: // access permission
0536: if (type == SelectionType.ACCESS)
0537: where
0538: .append("SAKAI_SITE_USER.PERMISSION <= SAKAI_SITE.PUBLISHED and ");
0539: // joinable requires NOT access permission
0540: if (type == SelectionType.JOINABLE)
0541: where
0542: .append("SITE_ID not in (select SITE_ID from SAKAI_SITE_USER where USER_ID = ? and PERMISSION <= PUBLISHED) and ");
0543:
0544: // do we need a join?
0545: String join = null;
0546: if ((type == SelectionType.ACCESS)
0547: || (type == SelectionType.UPDATE)) {
0548: // join with the SITE_USER table
0549: join = "SAKAI_SITE_USER";
0550: }
0551: if (sort == SortType.CREATED_BY_ASC
0552: || sort == SortType.CREATED_BY_DESC
0553: || sort == SortType.MODIFIED_BY_ASC
0554: || sort == SortType.MODIFIED_BY_DESC) {
0555: // join with SITE_USER_ID_MAP table
0556: if (join != null) {
0557: join += ", SAKAI_USER_ID_MAP";
0558: } else {
0559: join = "SAKAI_USER_ID_MAP";
0560: }
0561: }
0562:
0563: // add propertyCriteria if specified
0564: if ((propertyCriteria != null)
0565: && (propertyCriteria.size() > 0)) {
0566: for (int i = 0; i < propertyCriteria.size(); i++) {
0567: where
0568: .append("SAKAI_SITE.SITE_ID in (select SITE_ID from SAKAI_SITE_PROPERTY where NAME = ? and UPPER(VALUE) like UPPER(?)) and ");
0569: }
0570: }
0571:
0572: // where sorted by createdby, need to join with SAKAI_USER_ID_MAP in order to find out the user eid
0573: if (sort == SortType.CREATED_BY_ASC
0574: || sort == SortType.CREATED_BY_DESC) {
0575: // add more to where clause
0576: where
0577: .append("SAKAI_SITE.CREATEDBY = SAKAI_USER_ID_MAP.USER_ID and ");
0578: } else if (sort == SortType.MODIFIED_BY_ASC
0579: || sort == SortType.MODIFIED_BY_DESC) {
0580: // sort by modifiedby
0581: where
0582: .append("SAKAI_SITE.MODIFIEDBY = SAKAI_USER_ID_MAP.USER_ID and ");
0583: }
0584:
0585: // add order by if needed
0586: String order = null;
0587: if (sort == SortType.ID_ASC) {
0588: order = "SAKAI_SITE.SITE_ID ASC";
0589: } else if (sort == SortType.ID_DESC) {
0590: order = "SAKAI_SITE.SITE_ID DESC";
0591: } else if (sort == SortType.TITLE_ASC) {
0592: order = "SAKAI_SITE.TITLE ASC";
0593: } else if (sort == SortType.TITLE_DESC) {
0594: order = "SAKAI_SITE.TITLE DESC";
0595: } else if (sort == SortType.TYPE_ASC) {
0596: order = "SAKAI_SITE.TYPE ASC";
0597: } else if (sort == SortType.TYPE_DESC) {
0598: order = "SAKAI_SITE.TYPE DESC";
0599: } else if (sort == SortType.PUBLISHED_ASC) {
0600: order = "SAKAI_SITE.PUBLISHED ASC";
0601: } else if (sort == SortType.PUBLISHED_DESC) {
0602: order = "SAKAI_SITE.PUBLISHED DESC";
0603: } else if (sort == SortType.CREATED_BY_ASC) {
0604: order = "SAKAI_USER_ID_MAP.EID ASC";
0605: } else if (sort == SortType.CREATED_BY_DESC) {
0606: order = "SAKAI_USER_ID_MAP.EID DESC";
0607: } else if (sort == SortType.MODIFIED_BY_ASC) {
0608: order = "SAKAI_USER_ID_MAP.EID ASC";
0609: } else if (sort == SortType.MODIFIED_BY_DESC) {
0610: order = "SAKAI_USER_ID_MAP.EID DESC";
0611: } else if (sort == SortType.CREATED_ON_ASC) {
0612: order = "SAKAI_SITE.CREATEDON ASC";
0613: } else if (sort == SortType.CREATED_ON_DESC) {
0614: order = "SAKAI_SITE.CREATEDON DESC";
0615: } else if (sort == SortType.MODIFIED_ON_ASC) {
0616: order = "SAKAI_SITE.MODIFIEDON ASC";
0617: } else if (sort == SortType.MODIFIED_ON_DESC) {
0618: order = "SAKAI_SITE.MODIFIEDON DESC";
0619: }
0620:
0621: int fieldCount = 0;
0622: if (ofType != null) {
0623: if (ofType instanceof String) {
0624: // type criteria is a simple String value
0625: fieldCount++;
0626: }
0627: // more complex types
0628: else if (ofType instanceof String[]) {
0629: fieldCount += ((String[]) ofType).length;
0630: } else if (ofType instanceof List) {
0631: fieldCount += ((List) ofType).size();
0632: } else if (ofType instanceof Set) {
0633: fieldCount += ((Set) ofType).size();
0634: }
0635: }
0636: if (criteria != null)
0637: fieldCount += 1;
0638: if ((type == SelectionType.JOINABLE)
0639: || (type == SelectionType.ACCESS)
0640: || (type == SelectionType.UPDATE))
0641: fieldCount++;
0642: if (propertyCriteria != null)
0643: fieldCount += (2 * propertyCriteria.size());
0644: Object fields[] = null;
0645: if (fieldCount > 0) {
0646: fields = new Object[fieldCount];
0647: int pos = 0;
0648: if ((type == SelectionType.ACCESS)
0649: || (type == SelectionType.UPDATE)) {
0650: fields[pos++] = sessionManager()
0651: .getCurrentSessionUserId();
0652: }
0653: if (ofType != null) {
0654: if (ofType instanceof String) {
0655: // type criteria is a simple String value
0656: fields[pos++] = ofType;
0657: } else if (ofType instanceof String[]) {
0658: for (int i = 0; i < ((String[]) ofType).length; i++) {
0659: // of type String[]
0660: fields[pos++] = (String) ((String[]) ofType)[i];
0661: }
0662: } else if (ofType instanceof List) {
0663: for (Iterator l = ((List) ofType).iterator(); l
0664: .hasNext();) {
0665: // of type List
0666: fields[pos++] = l.next();
0667: }
0668: } else if (ofType instanceof Set) {
0669: for (Iterator l = ((Set) ofType).iterator(); l
0670: .hasNext();) {
0671: // of type Set
0672: fields[pos++] = l.next();
0673: }
0674: }
0675: }
0676: if (criteria != null) {
0677: criteria = "%" + criteria + "%";
0678: fields[pos++] = criteria;
0679: }
0680: if ((propertyCriteria != null)
0681: && (propertyCriteria.size() > 0)) {
0682: for (Iterator i = propertyCriteria.entrySet()
0683: .iterator(); i.hasNext();) {
0684: Map.Entry entry = (Map.Entry) i.next();
0685: String name = (String) entry.getKey();
0686: String value = (String) entry.getValue();
0687: fields[pos++] = name;
0688: fields[pos++] = "%" + value + "%";
0689: }
0690: }
0691: if (type == SelectionType.JOINABLE) {
0692: fields[pos++] = sessionManager()
0693: .getCurrentSessionUserId();
0694: }
0695: }
0696:
0697: List rv = null;
0698:
0699: // where has a trailing 'and ' to remove
0700: if ((where.length() > 5)
0701: && (where.substring(where.length() - 5)
0702: .equals(" and "))) {
0703: where.setLength(where.length() - 5);
0704: }
0705:
0706: // paging
0707: if (page != null) {
0708: // adjust to the size of the set found
0709: // page.validate(rv.size());
0710: rv = getSelectedResources(where.toString(), order,
0711: fields, page.getFirst(), page.getLast(), join);
0712: } else {
0713: rv = getSelectedResources(where.toString(), order,
0714: fields, join);
0715: }
0716:
0717: return rv;
0718: }
0719:
0720: /**
0721: * {@inheritDoc}
0722: */
0723: public List getSiteTypes() {
0724: String statement = "select distinct TYPE from SAKAI_SITE order by TYPE";
0725:
0726: List rv = sqlService().dbRead(statement);
0727:
0728: return rv;
0729: }
0730:
0731: /**
0732: * {@inheritDoc}
0733: */
0734: public String getSiteSkin(final String siteId) {
0735: if (siteId == null)
0736: return m_service.adjustSkin(null, true);
0737:
0738: // let the db do the work
0739: String statement = "select SKIN, PUBLISHED from SAKAI_SITE where SITE_ID = ?";
0740: Object fields[] = new Object[1];
0741: fields[0] = caseId(siteId);
0742:
0743: List rv = sqlService().dbRead(statement, fields,
0744: new SqlReader() {
0745: public Object readSqlResultRecord(
0746: ResultSet result) {
0747: try {
0748: String skin = result.getString(1);
0749: int published = result.getInt(2);
0750:
0751: // adjust the skin value
0752: skin = m_service.adjustSkin(skin,
0753: (published == 1));
0754:
0755: return skin;
0756: } catch (SQLException e) {
0757: M_log.warn("getSiteSkin: " + siteId
0758: + " : " + e);
0759: return null;
0760: }
0761: }
0762: });
0763:
0764: if ((rv != null) && (rv.size() > 0)) {
0765: return (String) rv.get(0);
0766: }
0767:
0768: return m_service.adjustSkin(null, true);
0769: }
0770:
0771: /**
0772: * {@inheritDoc}
0773: */
0774: public int countSites(SelectionType type, Object ofType,
0775: String criteria, Map propertyCriteria) {
0776: // if we are joining, start our where with the join clauses
0777: StringBuffer where = new StringBuffer();
0778: if ((type == SelectionType.ACCESS)
0779: || (type == SelectionType.UPDATE)) {
0780: // join on site id and also select the proper user
0781: where
0782: .append("SAKAI_SITE.SITE_ID = SAKAI_SITE_USER.SITE_ID and SAKAI_SITE_USER.USER_ID = ? and ");
0783: }
0784:
0785: // ignore user sites
0786: if (type.isIgnoreUser())
0787: where.append("SAKAI_SITE.IS_USER = '0' and ");
0788: // reject special sites
0789: if (type.isIgnoreSpecial())
0790: where.append("SAKAI_SITE.IS_SPECIAL = '0' and ");
0791: // reject unpublished sites
0792: if (type.isIgnoreUnpublished())
0793: where.append("SAKAI_SITE.PUBLISHED = 1 and ");
0794:
0795: // reject unwanted site types
0796: if (ofType != null) {
0797: if (ofType instanceof String) {
0798: // type criteria is a simple String value
0799: where.append("SAKAI_SITE.TYPE = ? and ");
0800: } else if (ofType instanceof String[]
0801: || ofType instanceof List
0802: || ofType instanceof Set) {
0803: // more complex type criteria
0804: int size = 0;
0805: if (ofType instanceof String[]) {
0806: size = ((String[]) ofType).length;
0807: } else if (ofType instanceof List) {
0808: size = ((List) ofType).size();
0809: } else if (ofType instanceof Set) {
0810: size = ((Set) ofType).size();
0811: }
0812: if (size > 0) {
0813: where.append("SAKAI_SITE.TYPE IN (?");
0814: for (int i = 1; i < size; i++) {
0815: where.append(",?");
0816: }
0817: where.append(") and ");
0818: }
0819: }
0820: }
0821:
0822: // reject non-joinable sites
0823: if (type == SelectionType.JOINABLE)
0824: where.append("SAKAI_SITE.JOINABLE = '1' and ");
0825: // check for pub view status
0826: if (type == SelectionType.PUBVIEW)
0827: where.append("SAKAI_SITE.PUBVIEW = '1' and ");
0828: // check criteria
0829: if (criteria != null)
0830: where
0831: .append("UPPER(SAKAI_SITE.TITLE) like UPPER(?) and ");
0832: // update permission
0833: if (type == SelectionType.UPDATE)
0834: where.append("SAKAI_SITE_USER.PERMISSION <= -1 and ");
0835: // access permission
0836: if (type == SelectionType.ACCESS)
0837: where
0838: .append("SAKAI_SITE_USER.PERMISSION <= SAKAI_SITE.PUBLISHED and ");
0839: // joinable requires NOT access permission
0840: if (type == SelectionType.JOINABLE)
0841: where
0842: .append("SAKAI_SITE.SITE_ID not in (select SITE_ID from SAKAI_SITE_USER where USER_ID = ? and PERMISSION <= PUBLISHED) and ");
0843:
0844: // do we need a join?
0845: String join = null;
0846: if ((type == SelectionType.ACCESS)
0847: || (type == SelectionType.UPDATE)) {
0848: // join with the SITE_USER table
0849: join = "SAKAI_SITE_USER";
0850: }
0851:
0852: // add propertyCriteria if specified
0853: if ((propertyCriteria != null)
0854: && (propertyCriteria.size() > 0)) {
0855: for (int i = 0; i < propertyCriteria.size(); i++) {
0856: where
0857: .append("SAKAI_SITE.SITE_ID in (select SITE_ID from SAKAI_SITE_PROPERTY where NAME = ? and UPPER(VALUE) like UPPER(?)) and ");
0858: }
0859: }
0860:
0861: int fieldCount = 0;
0862: if (ofType != null) {
0863: if (ofType instanceof String) {
0864: // type criteria is a simple String value
0865: fieldCount++;
0866: }
0867: // more complex types
0868: else if (ofType instanceof String[]) {
0869: fieldCount += ((String[]) ofType).length;
0870: } else if (ofType instanceof List) {
0871: fieldCount += ((List) ofType).size();
0872: } else if (ofType instanceof Set) {
0873: fieldCount += ((Set) ofType).size();
0874: }
0875: }
0876: if (criteria != null)
0877: fieldCount += 1;
0878: if ((type == SelectionType.JOINABLE)
0879: || (type == SelectionType.ACCESS)
0880: || (type == SelectionType.UPDATE))
0881: fieldCount++;
0882: if (propertyCriteria != null)
0883: fieldCount += (2 * propertyCriteria.size());
0884: Object fields[] = null;
0885: if (fieldCount > 0) {
0886: fields = new Object[fieldCount];
0887: int pos = 0;
0888: if ((type == SelectionType.ACCESS)
0889: || (type == SelectionType.UPDATE)) {
0890: fields[pos++] = sessionManager()
0891: .getCurrentSessionUserId();
0892: }
0893: if (ofType != null) {
0894: if (ofType instanceof String) {
0895: // type criteria is a simple String value
0896: fields[pos++] = ofType;
0897: } else if (ofType instanceof String[]) {
0898: for (int i = 0; i < ((String[]) ofType).length; i++) {
0899: // of type String[]
0900: fields[pos++] = (String) ((String[]) ofType)[i];
0901: }
0902: } else if (ofType instanceof List) {
0903: for (Iterator l = ((List) ofType).iterator(); l
0904: .hasNext();) {
0905: // of type List
0906: fields[pos++] = l.next();
0907: }
0908: } else if (ofType instanceof Set) {
0909: for (Iterator l = ((Set) ofType).iterator(); l
0910: .hasNext();) {
0911: // of type Set
0912: fields[pos++] = l.next();
0913: }
0914: }
0915: }
0916: if (criteria != null) {
0917: criteria = "%" + criteria + "%";
0918: fields[pos++] = criteria;
0919: }
0920: if ((propertyCriteria != null)
0921: && (propertyCriteria.size() > 0)) {
0922: for (Iterator i = propertyCriteria.entrySet()
0923: .iterator(); i.hasNext();) {
0924: Map.Entry entry = (Map.Entry) i.next();
0925: String name = (String) entry.getKey();
0926: String value = (String) entry.getValue();
0927: fields[pos++] = name;
0928: fields[pos++] = "%" + value + "%";
0929: }
0930: }
0931: if (type == SelectionType.JOINABLE) {
0932: fields[pos++] = sessionManager()
0933: .getCurrentSessionUserId();
0934: }
0935: }
0936:
0937: // where has a trailing 'and ' to remove
0938: if ((where.length() > 5)
0939: && (where.substring(where.length() - 5)
0940: .equals(" and "))) {
0941: where.setLength(where.length() - 5);
0942: }
0943:
0944: int rv = countSelectedResources(where.toString(), fields,
0945: join);
0946:
0947: return rv;
0948: }
0949:
0950: /**
0951: * Access the ToolConfiguration that has this id, if one is defined, else return null. The tool may be on any SitePage in the site.
0952: *
0953: * @param id
0954: * The id of the tool.
0955: * @return The ToolConfiguration that has this id, if one is defined, else return null.
0956: */
0957: public ToolConfiguration findTool(final String id) {
0958: String sql = "select REGISTRATION, SAKAI_SITE_TOOL.TITLE, LAYOUT_HINTS, SAKAI_SITE_TOOL.SITE_ID, PAGE_ID, SKIN, PUBLISHED, PAGE_ORDER from SAKAI_SITE_TOOL, SAKAI_SITE"
0959: + " where SAKAI_SITE_TOOL.SITE_ID = SAKAI_SITE.SITE_ID"
0960: + " and TOOL_ID = ?";
0961:
0962: Object fields[] = new Object[1];
0963: fields[0] = id;
0964:
0965: List found = m_sql.dbRead(sql, fields, new SqlReader() {
0966: public Object readSqlResultRecord(ResultSet result) {
0967: try {
0968: // get the fields
0969: String registration = result.getString(1);
0970: String title = result.getString(2);
0971: String layout = result.getString(3);
0972: String siteId = result.getString(4);
0973: String pageId = result.getString(5);
0974: String skin = result.getString(6);
0975: int published = result.getInt(7);
0976: int pageOrder = result.getInt(8);
0977:
0978: // adjust the skin value
0979: skin = m_service.adjustSkin(skin,
0980: (published == 1));
0981:
0982: // make the tool
0983: BaseToolConfiguration tool = new BaseToolConfiguration(
0984: id, registration, title, layout,
0985: pageId, siteId, skin, pageOrder);
0986:
0987: return tool;
0988: } catch (SQLException e) {
0989: M_log.warn("findTool: " + id + " : " + e);
0990: return null;
0991: }
0992: }
0993: });
0994:
0995: if (found.size() > 1) {
0996: M_log.warn("findTool: multiple results for tool id: "
0997: + id);
0998: }
0999:
1000: ToolConfiguration rv = null;
1001: if (found.size() > 0) {
1002: rv = (ToolConfiguration) found.get(0);
1003: }
1004:
1005: return rv;
1006: }
1007:
1008: /**
1009: * {@inheritDoc}
1010: */
1011: public SitePage findPage(final String id) {
1012: String sql = "select PAGE_ID, SAKAI_SITE_PAGE.TITLE, LAYOUT, SAKAI_SITE_PAGE.SITE_ID, SKIN, PUBLISHED, POPUP "
1013: + "from SAKAI_SITE_PAGE, SAKAI_SITE where SAKAI_SITE_PAGE.SITE_ID = SAKAI_SITE.SITE_ID "
1014: + "and PAGE_ID = ?";
1015:
1016: Object fields[] = new Object[1];
1017: fields[0] = id;
1018:
1019: List found = m_sql.dbRead(sql, fields, new SqlReader() {
1020: public Object readSqlResultRecord(ResultSet result) {
1021: try {
1022: // get the fields
1023: String pageId = result.getString(1);
1024: String title = result.getString(2);
1025: String layout = result.getString(3);
1026: String siteId = result.getString(4);
1027: String skin = result.getString(5);
1028: int published = result.getInt(6);
1029: boolean popup = "1".equals(result.getString(7)) ? true
1030: : false;
1031:
1032: // adjust the skin value
1033: skin = m_service.adjustSkin(skin,
1034: (published == 1));
1035:
1036: // make the page
1037: BaseSitePage page = new BaseSitePage(pageId,
1038: title, layout, popup, siteId, skin);
1039:
1040: return page;
1041: } catch (SQLException e) {
1042: M_log.warn("findPage: " + id + " : " + e);
1043: return null;
1044: }
1045: }
1046: });
1047:
1048: if (found.size() > 1) {
1049: M_log.warn("findPage: multiple results for page id: "
1050: + id);
1051: }
1052:
1053: SitePage rv = null;
1054: if (found.size() > 0) {
1055: rv = (SitePage) found.get(0);
1056: }
1057:
1058: return rv;
1059: }
1060:
1061: /**
1062: * Access the Site id for the page with this id.
1063: *
1064: * @param id
1065: * The id of the page.
1066: * @return The Site id for the page with this id, if the page is found, else null.
1067: */
1068: public String findPageSiteId(String id) {
1069: String sql = "select SITE_ID from SAKAI_SITE_PAGE where PAGE_ID = ?";
1070: Object fields[] = new Object[1];
1071: fields[0] = id;
1072:
1073: List found = m_sql.dbRead(sql, fields, null);
1074:
1075: if (found.size() > 1) {
1076: M_log
1077: .warn("findPageSiteId: multiple results for page id: "
1078: + id);
1079: }
1080:
1081: String rv = null;
1082: if (found.size() > 0) {
1083: rv = (String) found.get(0);
1084: }
1085:
1086: return rv;
1087: }
1088:
1089: /**
1090: * {@inheritDoc}
1091: */
1092: public String findGroupSiteId(String id) {
1093: String sql = "select SS.SITE_ID from SAKAI_SITE_GROUP SS where SS.GROUP_ID = ?";
1094: Object fields[] = new Object[1];
1095: fields[0] = id;
1096:
1097: List found = m_sql.dbRead(sql, fields, null);
1098:
1099: if (found.size() > 1) {
1100: M_log
1101: .warn("findGroupSiteId: multiple results for page id: "
1102: + id);
1103: }
1104:
1105: String rv = null;
1106: if (found.size() > 0) {
1107: rv = (String) found.get(0);
1108: }
1109:
1110: return rv;
1111: }
1112:
1113: /**
1114: * Access the Site id for the tool with this id.
1115: *
1116: * @param id
1117: * The id of the tool.
1118: * @return The Site id for the tool with this id, if the tool is found, else null.
1119: */
1120: public String findToolSiteId(String id) {
1121: String sql = "select SITE_ID from SAKAI_SITE_TOOL where TOOL_ID = ?";
1122: Object fields[] = new Object[1];
1123: fields[0] = id;
1124:
1125: List found = m_sql.dbRead(sql, fields, null);
1126:
1127: if (found.size() > 1) {
1128: M_log
1129: .warn("findToolSiteId: multiple results for page id: "
1130: + id);
1131: }
1132:
1133: String rv = null;
1134: if (found.size() > 0) {
1135: rv = (String) found.get(0);
1136: }
1137:
1138: return rv;
1139: }
1140:
1141: /**
1142: * Establish the internal security for this site. Previous security settings are replaced for this site. Assigning a user with update implies the two reads; assigning a user with unp read implies the other read.
1143: *
1144: * @param siteId
1145: * The id of the site.
1146: * @param updateUsers
1147: * The set of String User Ids who have update access.
1148: * @param visitUnpUsers
1149: * The set of String User Ids who have visit unpublished access.
1150: * @param visitUsers
1151: * The set of String User Ids who have visit access.
1152: */
1153: public void setSiteSecurity(final String siteId,
1154: Set updateUsers, Set visitUnpUsers, Set visitUsers) {
1155: // normalize the input parameters - remove any user in more than one set
1156:
1157: // adjust visitUsers to remove any that are in visitUnpUsers or updateUsers
1158: Set targetVisit = new HashSet();
1159: targetVisit.addAll(visitUsers);
1160: targetVisit.removeAll(visitUnpUsers);
1161: targetVisit.removeAll(updateUsers);
1162:
1163: // adjust visitUnpUsers to remove any that are in updateUsers
1164: Set targetUnp = new HashSet();
1165: targetUnp.addAll(visitUnpUsers);
1166: targetUnp.removeAll(updateUsers);
1167:
1168: Set targetUpdate = updateUsers;
1169:
1170: // read existing
1171: String statement = "select USER_ID, PERMISSION from SAKAI_SITE_USER "
1172: + "where SITE_ID = ?";
1173: Object[] fields = new Object[1];
1174: fields[0] = caseId(siteId);
1175:
1176: // collect the current data in three sets, update, unp, visit
1177: final Set existingUpdate = new HashSet();
1178: final Set existingUnp = new HashSet();
1179: final Set existingVisit = new HashSet();
1180:
1181: m_sql.dbRead(statement, fields, new SqlReader() {
1182: public Object readSqlResultRecord(ResultSet result) {
1183: try {
1184: String userId = result.getString(1);
1185: int permission = result.getInt(2);
1186: if (permission == -1) {
1187: existingUpdate.add(userId);
1188: } else if (permission == 0) {
1189: existingUnp.add(userId);
1190: } else if (permission == 1) {
1191: existingVisit.add(userId);
1192: } else {
1193: M_log
1194: .warn("setSiteSecurity: invalid permission "
1195: + permission
1196: + " site: "
1197: + siteId
1198: + " user: "
1199: + userId);
1200: }
1201: } catch (Throwable ignore) {
1202: return null;
1203: }
1204: return null;
1205: }
1206: });
1207:
1208: // compute the delete and insert sets for each of the three permissions
1209:
1210: // delete if the user is in targetUpdate, but it is already in one of the other categories
1211: Set updDeletes = new HashSet();
1212: updDeletes.addAll(existingUnp);
1213: updDeletes.addAll(existingVisit);
1214: updDeletes.retainAll(targetUpdate);
1215:
1216: // also delete if the user is in the existing and not in the target
1217: Set obsolete = new HashSet();
1218: obsolete.addAll(existingUpdate);
1219: obsolete.removeAll(targetUpdate);
1220: updDeletes.addAll(obsolete);
1221:
1222: // insert if the user is in targetUpdate, but is not already in update
1223: Set updInserts = new HashSet();
1224: updInserts.addAll(targetUpdate);
1225: updInserts.removeAll(existingUpdate);
1226:
1227: // delete if the user is in targetUnp, but it is already in one of the other categories
1228: Set unpDeletes = new HashSet();
1229: unpDeletes.addAll(existingUpdate);
1230: unpDeletes.addAll(existingVisit);
1231: unpDeletes.retainAll(targetUnp);
1232:
1233: // also delete if the user is in the existing and not in the target
1234: obsolete.clear();
1235: obsolete.addAll(existingUnp);
1236: obsolete.removeAll(targetUnp);
1237: unpDeletes.addAll(obsolete);
1238:
1239: // insert if the user is in targetUnp, but is not already in unp
1240: Set unpInserts = new HashSet();
1241: unpInserts.addAll(targetUnp);
1242: unpInserts.removeAll(existingUnp);
1243:
1244: // delete if the user is in targetVisit, but it is already in one of the other categories
1245: Set visitDeletes = new HashSet();
1246: visitDeletes.addAll(existingUpdate);
1247: visitDeletes.addAll(existingUnp);
1248: visitDeletes.retainAll(targetVisit);
1249:
1250: // also delete if the user is in the existing and not in the target
1251: obsolete.clear();
1252: obsolete.addAll(existingVisit);
1253: obsolete.removeAll(targetVisit);
1254: visitDeletes.addAll(obsolete);
1255:
1256: // insert if the user is in targetVisit, but is not already in visit
1257: Set visitInserts = new HashSet();
1258: visitInserts.addAll(targetVisit);
1259: visitInserts.removeAll(existingVisit);
1260:
1261: // if there's anything to do
1262: if (updDeletes.size() > 0 || updInserts.size() > 0
1263: || unpDeletes.size() > 0 || unpInserts.size() > 0
1264: || visitDeletes.size() > 0
1265: || visitInserts.size() > 0) {
1266: // delete old, write new, each in it's own transaction to avoid possible deadlock
1267: // involving modifications to multiple rows in a transaction
1268: fields = new Object[2];
1269: fields[0] = caseId(siteId);
1270:
1271: // delete
1272: statement = "delete from SAKAI_SITE_USER where SITE_ID = ? and USER_ID = ?";
1273: for (Iterator i = updDeletes.iterator(); i.hasNext();) {
1274: String userId = (String) i.next();
1275: fields[1] = userId;
1276: m_sql.dbWrite(statement, fields);
1277: }
1278: for (Iterator i = unpDeletes.iterator(); i.hasNext();) {
1279: String userId = (String) i.next();
1280: fields[1] = userId;
1281: m_sql.dbWrite(statement, fields);
1282: }
1283: for (Iterator i = visitDeletes.iterator(); i.hasNext();) {
1284: String userId = (String) i.next();
1285: fields[1] = userId;
1286: m_sql.dbWrite(statement, fields);
1287: }
1288:
1289: // insert
1290: statement = "insert into SAKAI_SITE_USER (SITE_ID, USER_ID, PERMISSION) values (?, ?, ?)";
1291: fields = new Object[3];
1292: fields[0] = caseId(siteId);
1293:
1294: fields[2] = new Integer(-1);
1295: for (Iterator i = updInserts.iterator(); i.hasNext();) {
1296: String userId = (String) i.next();
1297: fields[1] = userId;
1298: m_sql.dbWrite(statement, fields);
1299: }
1300:
1301: fields[2] = new Integer(0);
1302: for (Iterator i = unpInserts.iterator(); i.hasNext();) {
1303: String userId = (String) i.next();
1304: fields[1] = userId;
1305: m_sql.dbWrite(statement, fields);
1306: }
1307:
1308: fields[2] = new Integer(1);
1309: for (Iterator i = visitInserts.iterator(); i.hasNext();) {
1310: String userId = (String) i.next();
1311: fields[1] = userId;
1312: m_sql.dbWrite(statement, fields);
1313: }
1314: }
1315: }
1316:
1317: /**
1318: * {@inheritDoc}
1319: */
1320: public void setUserSecurity(final String userId,
1321: Set updateSites, Set visitUnpSites, Set visitSites) {
1322: // normalize the input parameters - remove any user in more than one set
1323:
1324: // adjust visitSites to remove any that are in visitUnpSites or updateSites
1325: Set targetVisit = new HashSet();
1326: targetVisit.addAll(visitSites);
1327: targetVisit.removeAll(visitUnpSites);
1328: targetVisit.removeAll(updateSites);
1329:
1330: // adjust visitUnpSites to remove any that are in updateSites
1331: Set targetUnp = new HashSet();
1332: targetUnp.addAll(visitUnpSites);
1333: targetUnp.removeAll(updateSites);
1334:
1335: Set targetUpdate = updateSites;
1336:
1337: // read existing
1338: String statement = "select SITE_ID, PERMISSION from SAKAI_SITE_USER "
1339: + "where USER_ID = ?";
1340: Object[] fields = new Object[1];
1341: fields[0] = userId;
1342:
1343: // collect the current data in three sets, update, unp, visit
1344: final Set existingUpdate = new HashSet();
1345: final Set existingUnp = new HashSet();
1346: final Set existingVisit = new HashSet();
1347:
1348: m_sql.dbRead(statement, fields, new SqlReader() {
1349: public Object readSqlResultRecord(ResultSet result) {
1350: try {
1351: String siteId = result.getString(1);
1352: int permission = result.getInt(2);
1353: if (permission == -1) {
1354: existingUpdate.add(siteId);
1355: } else if (permission == 0) {
1356: existingUnp.add(siteId);
1357: } else if (permission == 1) {
1358: existingVisit.add(siteId);
1359: } else {
1360: M_log
1361: .warn("setUserSecurity: invalid permission "
1362: + permission
1363: + " site: "
1364: + siteId
1365: + " user: "
1366: + userId);
1367: }
1368: } catch (Throwable ignore) {
1369: return null;
1370: }
1371: return null;
1372: }
1373: });
1374:
1375: // compute the delete and insert sets for each of the three permissions
1376:
1377: // delete if the site is in targetUpdate, but it is already in one of the other categories
1378: Set updDeletes = new HashSet();
1379: updDeletes.addAll(existingUnp);
1380: updDeletes.addAll(existingVisit);
1381: updDeletes.retainAll(targetUpdate);
1382:
1383: // also delete if the user is in the existing and not in the target
1384: Set obsolete = new HashSet();
1385: obsolete.addAll(existingUpdate);
1386: obsolete.removeAll(targetUpdate);
1387: updDeletes.addAll(obsolete);
1388:
1389: // insert if the site is in targetUpdate, but is not already in update
1390: Set updInserts = new HashSet();
1391: updInserts.addAll(targetUpdate);
1392: updInserts.removeAll(existingUpdate);
1393:
1394: // delete if the site is in targetUnp, but it is already in one of the other categories
1395: Set unpDeletes = new HashSet();
1396: unpDeletes.addAll(existingUpdate);
1397: unpDeletes.addAll(existingVisit);
1398: unpDeletes.retainAll(targetUnp);
1399:
1400: // also delete if the user is in the existing and not in the target
1401: obsolete.clear();
1402: obsolete.addAll(existingUnp);
1403: obsolete.removeAll(targetUnp);
1404: unpDeletes.addAll(obsolete);
1405:
1406: // insert if the site is in targetUnp, but is not already in unp
1407: Set unpInserts = new HashSet();
1408: unpInserts.addAll(targetUnp);
1409: unpInserts.removeAll(existingUnp);
1410:
1411: // delete if the site is in targetVisit, but it is already in one of the other categories
1412: Set visitDeletes = new HashSet();
1413: visitDeletes.addAll(existingUpdate);
1414: visitDeletes.addAll(existingUnp);
1415: visitDeletes.retainAll(targetVisit);
1416:
1417: // also delete if the user is in the existing and not in the target
1418: obsolete.clear();
1419: obsolete.addAll(existingVisit);
1420: obsolete.removeAll(targetVisit);
1421: visitDeletes.addAll(obsolete);
1422:
1423: // insert if the site is in targetVisit, but is not already in visit
1424: Set visitInserts = new HashSet();
1425: visitInserts.addAll(targetVisit);
1426: visitInserts.removeAll(existingVisit);
1427:
1428: // if there's anything to do
1429: if (updDeletes.size() > 0 || updInserts.size() > 0
1430: || unpDeletes.size() > 0 || unpInserts.size() > 0
1431: || visitDeletes.size() > 0
1432: || visitInserts.size() > 0) {
1433: // delete old, write new, each in it's own transaction to avoid possible deadlock
1434: // involving modifications to multiple rows in a transaction
1435: fields = new Object[2];
1436: fields[1] = userId;
1437:
1438: // delete
1439: statement = "delete from SAKAI_SITE_USER where SITE_ID = ? and USER_ID = ?";
1440: for (Iterator i = updDeletes.iterator(); i.hasNext();) {
1441: String siteId = (String) i.next();
1442: fields[0] = caseId(siteId);
1443: m_sql.dbWrite(statement, fields);
1444: }
1445: for (Iterator i = unpDeletes.iterator(); i.hasNext();) {
1446: String siteId = (String) i.next();
1447: fields[0] = caseId(siteId);
1448: m_sql.dbWrite(statement, fields);
1449: }
1450: for (Iterator i = visitDeletes.iterator(); i.hasNext();) {
1451: String siteId = (String) i.next();
1452: fields[0] = caseId(siteId);
1453: m_sql.dbWrite(statement, fields);
1454: }
1455:
1456: // insert
1457: statement = "insert into SAKAI_SITE_USER (SITE_ID, USER_ID, PERMISSION) values (?, ?, ?)";
1458: fields = new Object[3];
1459: fields[1] = userId;
1460:
1461: fields[2] = new Integer(-1);
1462: for (Iterator i = updInserts.iterator(); i.hasNext();) {
1463: String siteId = (String) i.next();
1464: fields[0] = caseId(siteId);
1465: m_sql.dbWrite(statement, fields);
1466: }
1467:
1468: fields[2] = new Integer(0);
1469: for (Iterator i = unpInserts.iterator(); i.hasNext();) {
1470: String siteId = (String) i.next();
1471: fields[0] = caseId(siteId);
1472: m_sql.dbWrite(statement, fields);
1473: }
1474:
1475: fields[2] = new Integer(1);
1476: for (Iterator i = visitInserts.iterator(); i.hasNext();) {
1477: String siteId = (String) i.next();
1478: fields[0] = caseId(siteId);
1479: m_sql.dbWrite(statement, fields);
1480: }
1481: }
1482: }
1483:
1484: /**
1485: * Read site properties from storage into the site's properties.
1486: *
1487: * @param edit
1488: * The user to read properties for.
1489: */
1490: public void readSiteProperties(Site site,
1491: ResourcePropertiesEdit props) {
1492: super .readProperties(site, props);
1493: }
1494:
1495: /**
1496: * Read site properties and all page and tool properties for the site from storage.
1497: *
1498: * @param site
1499: * The site for which properties are desired.
1500: */
1501: public void readAllSiteProperties(Site site) {
1502: // read and un-lazy the site properties
1503: readSiteProperties(site, ((BaseSite) site).m_properties);
1504: ((BaseResourcePropertiesEdit) ((BaseSite) site).m_properties)
1505: .setLazy(false);
1506:
1507: // read and unlazy the page properties for the entire site
1508: readSitePageProperties((BaseSite) site);
1509: for (Iterator i = site.getPages().iterator(); i.hasNext();) {
1510: BaseSitePage page = (BaseSitePage) i.next();
1511: ((BaseResourcePropertiesEdit) page.m_properties)
1512: .setLazy(false);
1513: }
1514:
1515: // read and unlazy the tool properties for the entire site
1516: readSiteToolProperties((BaseSite) site);
1517: for (Iterator i = site.getPages().iterator(); i.hasNext();) {
1518: BaseSitePage page = (BaseSitePage) i.next();
1519: for (Iterator t = page.getTools().iterator(); t
1520: .hasNext();) {
1521: BaseToolConfiguration tool = (BaseToolConfiguration) t
1522: .next();
1523: tool.m_configLazy = false;
1524: }
1525: }
1526:
1527: // read and unlazy the group properties for the entire site
1528: readSiteGroupProperties((BaseSite) site);
1529: for (Iterator i = site.getGroups().iterator(); i.hasNext();) {
1530: BaseGroup group = (BaseGroup) i.next();
1531: ((BaseResourcePropertiesEdit) group.m_properties)
1532: .setLazy(false);
1533: }
1534: }
1535:
1536: /**
1537: * Read properties for all pages in the site
1538: *
1539: * @param site
1540: * The site to read properties for.
1541: */
1542: protected void readSitePageProperties(final BaseSite site) {
1543: // get the properties from the db for all pages in the site
1544: String sql = "select PAGE_ID, NAME, VALUE from SAKAI_SITE_PAGE_PROPERTY where ( SITE_ID = ? )";
1545:
1546: Object fields[] = new Object[1];
1547: fields[0] = site.getId();
1548: m_sql.dbRead(sql, fields, new SqlReader() {
1549: public Object readSqlResultRecord(ResultSet result) {
1550: try {
1551: // read the fields
1552: String pageId = result.getString(1);
1553: String name = result.getString(2);
1554: String value = result.getString(3);
1555:
1556: // get the page
1557: BaseSitePage page = (BaseSitePage) site
1558: .getPage(pageId);
1559: if (page != null) {
1560: page.m_properties.addProperty(name, value);
1561: }
1562:
1563: // nothing to return
1564: return null;
1565: } catch (SQLException e) {
1566: M_log.warn("readSitePageProperties: " + e);
1567: return null;
1568: }
1569: }
1570: });
1571: }
1572:
1573: /**
1574: * Read properties for all tools in the site
1575: *
1576: * @param site
1577: * The site to read properties for.
1578: */
1579: protected void readSiteToolProperties(final BaseSite site) {
1580: // get the properties from the db for all pages in the site
1581: String sql = "select TOOL_ID, NAME, VALUE from SAKAI_SITE_TOOL_PROPERTY where ( SITE_ID = ? )";
1582:
1583: Object fields[] = new Object[1];
1584: fields[0] = site.getId();
1585: m_sql.dbRead(sql, fields, new SqlReader() {
1586: public Object readSqlResultRecord(ResultSet result) {
1587: try {
1588: // read the fields
1589: String toolId = result.getString(1);
1590: String name = result.getString(2);
1591: String value = result.getString(3);
1592:
1593: // get the page
1594: BaseToolConfiguration tool = (BaseToolConfiguration) site
1595: .getTool(toolId);
1596: if (tool != null) {
1597: tool.getMyConfig().setProperty(name, value);
1598: }
1599:
1600: // nothing to return
1601: return null;
1602: } catch (SQLException e) {
1603: M_log.warn("readSitePageProperties: " + e);
1604: return null;
1605: }
1606: }
1607: });
1608: }
1609:
1610: /**
1611: * Read properties for all groups in the site
1612: *
1613: * @param site
1614: * The site to read group properties for.
1615: */
1616: protected void readSiteGroupProperties(final BaseSite site) {
1617: // get the properties from the db for all pages in the site
1618: String sql = "select GROUP_ID, NAME, VALUE from SAKAI_SITE_GROUP_PROPERTY where ( SITE_ID = ? )";
1619:
1620: Object fields[] = new Object[1];
1621: fields[0] = site.getId();
1622: m_sql.dbRead(sql, fields, new SqlReader() {
1623: public Object readSqlResultRecord(ResultSet result) {
1624: try {
1625: // read the fields
1626: String groupId = result.getString(1);
1627: String name = result.getString(2);
1628: String value = result.getString(3);
1629:
1630: // get the group
1631: BaseGroup group = (BaseGroup) site
1632: .getGroup(groupId);
1633: if (group != null) {
1634: group.m_properties.addProperty(name, value);
1635: }
1636:
1637: // nothing to return
1638: return null;
1639: } catch (SQLException e) {
1640: M_log.warn("readSiteGroupProperties: " + e);
1641: return null;
1642: }
1643: }
1644: });
1645: }
1646:
1647: /**
1648: * Read page properties from storage into the page's properties.
1649: *
1650: * @param page
1651: * The page for which properties are desired.
1652: */
1653: public void readPageProperties(SitePage page,
1654: ResourcePropertiesEdit props) {
1655: super .readProperties(null, "SAKAI_SITE_PAGE_PROPERTY",
1656: "PAGE_ID", page.getId(), props);
1657: }
1658:
1659: /**
1660: * Read tool properties from storage into the tool's properties.
1661: *
1662: * @param tool
1663: * The tool for which properties are desired.
1664: */
1665: public void readToolProperties(ToolConfiguration tool,
1666: Properties props) {
1667: super .readProperties(null, "SAKAI_SITE_TOOL_PROPERTY",
1668: "TOOL_ID", tool.getId(), props);
1669: }
1670:
1671: /**
1672: * Read group properties from storage into the group's properties.
1673: *
1674: * @param group
1675: * The group for which properties are desired.
1676: */
1677: public void readGroupProperties(Group group, Properties props) {
1678: super .readProperties(null, "SAKAI_SITE_GROUP_PROPERTY",
1679: "GROUP_ID", group.getId(), props);
1680: }
1681:
1682: /**
1683: * Read site pages from storage into the site's pages.
1684: *
1685: * @param site
1686: * The site for which pages are desired.
1687: */
1688: public void readSitePages(final Site site,
1689: final ResourceVector pages) {
1690: // read all resources from the db with a where
1691: String sql = "select PAGE_ID, TITLE, LAYOUT, POPUP from SAKAI_SITE_PAGE"
1692: + " where SITE_ID = ?" + " order by SITE_ORDER ASC";
1693:
1694: Object fields[] = new Object[1];
1695: fields[0] = site.getId();
1696:
1697: List all = m_sql.dbRead(sql, fields, new SqlReader() {
1698: public Object readSqlResultRecord(ResultSet result) {
1699: try {
1700: // get the fields
1701: String id = result.getString(1);
1702: String title = result.getString(2);
1703: String layout = result.getString(3);
1704: boolean popup = "1".equals(result.getString(4)) ? true
1705: : false;
1706:
1707: // make the page
1708: BaseSitePage page = new BaseSitePage(site, id,
1709: title, layout, popup);
1710:
1711: // add it to the pages
1712: pages.add(page);
1713:
1714: return null;
1715: } catch (SQLException ignore) {
1716: return null;
1717: }
1718: }
1719: });
1720: }
1721:
1722: /**
1723: * Read site page tools from storage into the page's tools.
1724: *
1725: * @param page
1726: * The page for which tools are desired.
1727: */
1728: public void readPageTools(final SitePage page,
1729: final ResourceVector tools) {
1730: // read all resources from the db with a where
1731: String sql = "select TOOL_ID, REGISTRATION, TITLE, LAYOUT_HINTS, PAGE_ORDER from SAKAI_SITE_TOOL"
1732: + " where PAGE_ID = ?" + " order by PAGE_ORDER ASC";
1733:
1734: Object fields[] = new Object[1];
1735: fields[0] = page.getId();
1736:
1737: List all = m_sql.dbRead(sql, fields, new SqlReader() {
1738: public Object readSqlResultRecord(ResultSet result) {
1739: try {
1740: // get the fields
1741: String id = result.getString(1);
1742: String registration = result.getString(2);
1743: String title = result.getString(3);
1744: String layout = result.getString(4);
1745: int pageOrder = result.getInt(5);
1746:
1747: // make the tool
1748: BaseToolConfiguration tool = new BaseToolConfiguration(
1749: page, id, registration, title, layout,
1750: pageOrder);
1751:
1752: // add it to the tools
1753: tools.add(tool);
1754:
1755: return null;
1756: } catch (SQLException ignore) {
1757: return null;
1758: }
1759: }
1760: });
1761: }
1762:
1763: /**
1764: * Read tools for all pages from storage into the site's page's tools.
1765: *
1766: * @param site
1767: * The site for which tools are desired.
1768: */
1769: public void readSiteTools(final Site site) {
1770: // read all tools for the site
1771: String sql = "select TOOL_ID, PAGE_ID, REGISTRATION, TITLE, LAYOUT_HINTS, PAGE_ORDER from SAKAI_SITE_TOOL"
1772: + " where SITE_ID = ?"
1773: + " order by PAGE_ID, PAGE_ORDER ASC";
1774:
1775: Object fields[] = new Object[1];
1776: fields[0] = site.getId();
1777:
1778: List all = m_sql.dbRead(sql, fields, new SqlReader() {
1779: public Object readSqlResultRecord(ResultSet result) {
1780: try {
1781: // get the fields
1782: String id = result.getString(1);
1783: String pageId = result.getString(2);
1784: String registration = result.getString(3);
1785: String title = result.getString(4);
1786: String layout = result.getString(5);
1787: int pageOrder = result.getInt(6);
1788:
1789: // get the page
1790: BaseSitePage page = (BaseSitePage) site
1791: .getPage(pageId);
1792: if ((page != null) && (page.m_toolsLazy)) {
1793: // make the tool
1794: BaseToolConfiguration tool = new BaseToolConfiguration(
1795: page, id, registration, title,
1796: layout, pageOrder);
1797:
1798: // add it to the tools
1799: page.m_tools.add(tool);
1800: }
1801:
1802: return null;
1803: } catch (SQLException ignore) {
1804: return null;
1805: }
1806: }
1807: });
1808:
1809: // unlazy the page tools
1810: for (Iterator i = site.getPages().iterator(); i.hasNext();) {
1811: BaseSitePage page = (BaseSitePage) i.next();
1812: page.m_toolsLazy = false;
1813: }
1814: }
1815:
1816: /**
1817: * @inheritDoc
1818: */
1819: public void readSiteGroups(final Site site,
1820: final Collection groups) {
1821: String sql = "select SS.GROUP_ID, SS.TITLE, SS.DESCRIPTION "
1822: + "from SAKAI_SITE_GROUP SS where SS.SITE_ID = ?";
1823: // TODO: order by? title? -ggolden
1824:
1825: Object fields[] = new Object[1];
1826: fields[0] = site.getId();
1827:
1828: List all = m_sql.dbRead(sql, fields, new SqlReader() {
1829: public Object readSqlResultRecord(ResultSet result) {
1830: try {
1831: // get the fields
1832: String groupId = result.getString(1);
1833: String title = result.getString(2);
1834: String description = result.getString(3);
1835:
1836: // make the group
1837: BaseGroup group = new BaseGroup(groupId, title,
1838: description, site);
1839:
1840: // add it to the groups
1841: groups.add(group);
1842:
1843: return null;
1844: } catch (SQLException e) {
1845: M_log.warn("readSiteGroups: " + site.getId()
1846: + " : " + e);
1847: return null;
1848: }
1849: }
1850: });
1851: }
1852:
1853: /**
1854: * Get the fields for the database from the edit for this id, and the id again at the end if needed
1855: *
1856: * @param id
1857: * The resource id
1858: * @param edit
1859: * The edit (may be null in a new)
1860: * @param idAgain
1861: * If true, include the id field again at the end, else don't.
1862: * @return The fields for the database.
1863: */
1864: protected Object[] fields(String id, Site edit, boolean idAgain) {
1865: Object[] rv = new Object[idAgain ? 20 : 19];
1866: rv[0] = caseId(id);
1867: if (idAgain) {
1868: rv[19] = rv[0];
1869: }
1870:
1871: if (edit == null) {
1872: String current = sessionManager()
1873: .getCurrentSessionUserId();
1874:
1875: // if no current user, since we are working up a new user record, use the user id as creator...
1876: if (current == null)
1877: current = "";
1878:
1879: Time now = timeService().newTime();
1880:
1881: rv[1] = "";
1882: rv[2] = "";
1883: rv[3] = "";
1884: rv[4] = "";
1885: rv[5] = "";
1886: rv[6] = "";
1887: rv[7] = "";
1888: rv[8] = new Integer(0);
1889: rv[9] = "0";
1890: rv[10] = "0";
1891: rv[11] = "";
1892: rv[12] = isSpecialSite(id) ? "1" : "0";
1893: rv[13] = isUserSite(id) ? "1" : "0";
1894: rv[14] = current;
1895: rv[15] = current;
1896: rv[16] = now;
1897: rv[17] = now;
1898: rv[18] = "0";
1899: }
1900:
1901: else {
1902: rv[1] = StringUtil
1903: .trimToZero(((BaseSite) edit).m_title);
1904: rv[2] = StringUtil.trimToZero(((BaseSite) edit).m_type);
1905: rv[3] = StringUtil
1906: .trimToZero(((BaseSite) edit).m_shortDescription);
1907: rv[4] = StringUtil
1908: .trimToZero(((BaseSite) edit).m_description);
1909: rv[5] = StringUtil.trimToZero(((BaseSite) edit).m_icon);
1910: rv[6] = StringUtil.trimToZero(((BaseSite) edit).m_info);
1911: rv[7] = StringUtil.trimToZero(((BaseSite) edit).m_skin);
1912: rv[8] = new Integer((((BaseSite) edit).m_published) ? 1
1913: : 0);
1914: rv[9] = ((((BaseSite) edit).m_joinable) ? "1" : "0");
1915: rv[10] = ((((BaseSite) edit).m_pubView) ? "1" : "0");
1916: rv[11] = StringUtil
1917: .trimToZero(((BaseSite) edit).m_joinerRole);
1918: rv[12] = isSpecialSite(id) ? "1" : "0";
1919: rv[13] = isUserSite(id) ? "1" : "0";
1920: rv[14] = StringUtil
1921: .trimToZero(((BaseSite) edit).m_createdUserId);
1922: rv[15] = StringUtil
1923: .trimToZero(((BaseSite) edit).m_lastModifiedUserId);
1924: rv[16] = edit.getCreatedTime();
1925: rv[17] = edit.getModifiedTime();
1926: rv[18] = edit.isCustomPageOrdered() ? "1" : "0";
1927: }
1928:
1929: return rv;
1930: }
1931:
1932: /**
1933: * Read from the result one set of fields to create a Resource.
1934: *
1935: * @param result
1936: * The Sql query result.
1937: * @return The Resource object.
1938: */
1939: public Object readSqlResultRecord(ResultSet result) {
1940: try {
1941: String id = result.getString(1);
1942: String title = result.getString(2);
1943: String type = result.getString(3);
1944: String shortDesc = result.getString(4);
1945: String description = result.getString(5);
1946: String icon = result.getString(6);
1947: String info = result.getString(7);
1948: String skin = result.getString(8);
1949: boolean published = result.getInt(9) == 1;
1950: boolean joinable = "1".equals(result.getString(10)) ? true
1951: : false;
1952: boolean pubView = "1".equals(result.getString(11)) ? true
1953: : false;
1954: String joinRole = result.getString(12);
1955: boolean isSpecial = "1".equals(result.getString(13)) ? true
1956: : false;
1957: boolean isUser = "1".equals(result.getString(14)) ? true
1958: : false;
1959: String createdBy = result.getString(15);
1960: String modifiedBy = result.getString(16);
1961: java.sql.Timestamp ts = result.getTimestamp(17,
1962: sqlService().getCal());
1963: Time createdOn = null;
1964: if (ts != null) {
1965: createdOn = timeService().newTime(ts.getTime());
1966: }
1967: ts = result.getTimestamp(18, sqlService().getCal());
1968: Time modifiedOn = null;
1969: if (ts != null) {
1970: modifiedOn = timeService().newTime(ts.getTime());
1971: }
1972: boolean customPageOrdered = "1".equals(result
1973: .getString(19)) ? true : false;
1974:
1975: // create the Resource from these fields
1976: return new BaseSite(id, title, type, shortDesc,
1977: description, icon, info, skin, published,
1978: joinable, pubView, joinRole, isSpecial, isUser,
1979: createdBy, createdOn, modifiedBy, modifiedOn,
1980: customPageOrdered);
1981: } catch (SQLException e) {
1982: M_log.warn("readSqlResultRecord: " + e);
1983: return null;
1984: }
1985: }
1986: }
1987: }
|