* Licensed to Jasig under one or more contributor license

* agreements. See the NOTICE file distributed with this work

* for additional information regarding copyright ownership.

* Jasig licenses this file to you under the Apache License,

* Version 2.0 (the "License"); you may not use this file

* except in compliance with the License. You may obtain a

* copy of the License at:

*

* http://www.apache.org/licenses/LICENSE-2.0

*

* Unless required by applicable law or agreed to in writing,

* software distributed under the License is distributed on

* an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY

* KIND, either express or implied. See the License for the

* specific language governing permissions and limitations

* under the License.

*/

* SQL implementation for the 2.x relational database model.

*

* Prior to uPortal 2.5, this class existed in the org.jasig.portal package.  It was

* moved to its present package to express that it is part of the

* Simple Layout Manager implementation.

*

* @author George Lindholm

* @version $Revision: 21145 $ $Date: 2010-07-19 15:19:16 -0500 (Mon, 19 Jul 2010) $

*/

    protected final Log log = LogFactory.getLog(getClass());

    private static String PROFILE_TABLE = "UP_USER_PROFILE";

  //This class is instantiated ONCE so NO class variables can be used to keep state between calls

  protected static int DEBUG = 0;

  protected static final String channelPrefix = "n";

  protected static final String folderPrefix = "s";

   

  private final DataSource dataSource;

  private final IDatabaseMetadata databaseMetadata;

  protected final IChannelRegistryStore channelRegistryStore;

  protected final ICounterStore counterStore;

  protected final IPersonManager personManager;

 

  // I18n property

  protected static final boolean localeAware = LocaleManager.isLocaleAware();

    public RDBMUserLayoutStore () throws Exception {

        this.dataSource = RDBMServices.getDataSource();

        this.databaseMetadata = RDBMServices.getDbMetaData();

        this.channelRegistryStore = ChannelRegistryStoreFactory.getChannelRegistryStoreImpl();

        this.counterStore = CounterStoreFactory.getCounterStoreImpl();

        this.personManager = PersonManagerLocator.getPersonManager();

       

        if (this.databaseMetadata.supportsOuterJoins()) {

            final IJoinQueryString joinQuery = this.databaseMetadata.getJoinQuery();

            if (joinQuery instanceof DatabaseMetaDataImpl.JdbcDb) {

                joinQuery.addQuery("layout",

                        "{oj UP_LAYOUT_STRUCT ULS LEFT OUTER JOIN UP_LAYOUT_PARAM USP ON ULS.USER_ID = USP.USER_ID AND ULS.STRUCT_ID = USP.STRUCT_ID} WHERE");

                joinQuery.addQuery("ss_struct",

                        "{oj UP_SS_STRUCT USS LEFT OUTER JOIN UP_SS_STRUCT_PAR USP ON USS.SS_ID=USP.SS_ID} WHERE");

                joinQuery.addQuery("ss_theme",

                        "{oj UP_SS_THEME UTS LEFT OUTER JOIN UP_SS_THEME_PARM UTP ON UTS.SS_ID=UTP.SS_ID} WHERE");

            }

            else if (joinQuery instanceof DatabaseMetaDataImpl.PostgreSQLDb) {

                joinQuery.addQuery("layout",

                        "UP_LAYOUT_STRUCT ULS LEFT OUTER JOIN UP_LAYOUT_PARAM USP ON ULS.USER_ID = USP.USER_ID AND ULS.STRUCT_ID = USP.STRUCT_ID WHERE");

                joinQuery.addQuery("ss_struct",

                        "UP_SS_STRUCT USS LEFT OUTER JOIN UP_SS_STRUCT_PAR USP ON USS.SS_ID=USP.SS_ID WHERE");

                joinQuery.addQuery("ss_theme",

                        "UP_SS_THEME UTS LEFT OUTER JOIN UP_SS_THEME_PARM UTP ON UTS.SS_ID=UTP.SS_ID WHERE");

            }

            else if (joinQuery instanceof DatabaseMetaDataImpl.OracleDb) {

                joinQuery.addQuery("layout",

                        "UP_LAYOUT_STRUCT ULS, UP_LAYOUT_PARAM USP WHERE ULS.STRUCT_ID = USP.STRUCT_ID(+) AND ULS.USER_ID = USP.USER_ID(+) AND");

                joinQuery.addQuery("ss_struct",

                        "UP_SS_STRUCT USS, UP_SS_STRUCT_PAR USP WHERE USS.SS_ID=USP.SS_ID(+) AND");

                joinQuery.addQuery("ss_theme", "UP_SS_THEME UTS, UP_SS_THEME_PARM UTP WHERE UTS.SS_ID=UTP.SS_ID(+) AND");

            }

            else {

                throw new Exception("Unknown database driver");

            }

        }

    }

 

    private final SingletonDoubleCheckedCreator<IPerson> systemPersonCreator = new SingletonDoubleCheckedCreator<IPerson>() {

        protected IPerson createSingleton(Object... args) {

            // be sure we only do this once...

            // Load the "system" user id from the database

            final SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(RDBMUserLayoutStore.this.dataSource);

            final int systemUserId = jdbcTemplate.queryForInt("SELECT USER_ID FROM UP_USER WHERE USER_NAME = 'system'");

            log.info("Found user id " + systemUserId + " for the 'system' user.");

            return new SystemUser(systemUserId);

        }

    };

    private final IPerson getSystemUser() {

        return this.systemPersonCreator.get();

    }

  /**

   * Registers a NEW structure stylesheet with the database.

   * @param ssd the Stylesheet description object

   * @return an <code>Integer</code> id for the registered Stylesheet description object

   */

  public Integer addStructureStylesheetDescription (StructureStylesheetDescription ssd) throws Exception {

    Connection con = RDBMServices.getConnection();

    try {

      // Set autocommit false for the connection

      RDBMServices.setAutoCommit(con, false);

      Statement stmt = con.createStatement();

      try {

        // we assume that this is a new stylesheet.

        int id = counterStore.getIncrementIntegerId("UP_SS_STRUCT");

        ssd.setId(id);

        String sQuery = "INSERT INTO UP_SS_STRUCT (SS_ID,SS_NAME,SS_URI,SS_DESCRIPTION_URI,SS_DESCRIPTION_TEXT) VALUES ("

            + id + ",'" + ssd.getStylesheetName() + "','" + ssd.getStylesheetURI() + "','" + ssd.getStylesheetDescriptionURI()

            + "','" + ssd.getStylesheetWordDescription() + "')";

        if (log.isDebugEnabled())

            log.debug("RDBMUserLayoutStore::addStructureStylesheetDescription(): " + sQuery);

        stmt.executeUpdate(sQuery);

        // insert all stylesheet params

        for (Enumeration e = ssd.getStylesheetParameterNames(); e.hasMoreElements();) {

          String pName = (String)e.nextElement();

          sQuery = "INSERT INTO UP_SS_STRUCT_PAR (SS_ID,PARAM_NAME,PARAM_DEFAULT_VAL,PARAM_DESCRIPT,TYPE) VALUES (" + id

              + ",'" + pName + "','" + ssd.getStylesheetParameterDefaultValue(pName) + "','" + ssd.getStylesheetParameterWordDescription(pName)

              + "',1)";

          if (log.isDebugEnabled())

              log.debug("RDBMUserLayoutStore::addStructureStylesheetDescription(): " + sQuery);

          stmt.executeUpdate(sQuery);

        }

        // insert all folder attributes

        for (Enumeration e = ssd.getFolderAttributeNames(); e.hasMoreElements();) {

          String pName = (String)e.nextElement();

          sQuery = "INSERT INTO UP_SS_STRUCT_PAR (SS_ID,PARAM_NAME,PARAM_DEFAULT_VAL,PARAM_DESCRIPT,TYPE) VALUES (" + id

              + ",'" + pName + "','" + ssd.getFolderAttributeDefaultValue(pName) + "','" + ssd.getFolderAttributeWordDescription(pName)

              + "',2)";

          if (log.isDebugEnabled())

              log.debug("RDBMUserLayoutStore::addStructureStylesheetDescription(): " + sQuery);

          stmt.executeUpdate(sQuery);

        }

        // insert all channel attributes

        for (Enumeration e = ssd.getChannelAttributeNames(); e.hasMoreElements();) {

          String pName = (String)e.nextElement();

          sQuery = "INSERT INTO UP_SS_STRUCT_PAR (SS_ID,PARAM_NAME,PARAM_DEFAULT_VAL,PARAM_DESCRIPT,TYPE) VALUES (" + id

              + ",'" + pName + "','" + ssd.getChannelAttributeDefaultValue(pName) + "','" + ssd.getChannelAttributeWordDescription(pName)

              + "',3)";

          if (log.isDebugEnabled())

              log.debug("RDBMUserLayoutStore::addStructureStylesheetDescription(): " + sQuery);

          stmt.executeUpdate(sQuery);

        }

        // Commit the transaction

        RDBMServices.commit(con);

        return  new Integer(id);

      } catch (Exception e) {

        // Roll back the transaction

        RDBMServices.rollback(con);

        throw  e;

      } finally {

        stmt.close();

      }

    } finally {

      RDBMServices.releaseConnection(con);

    }

  }

  /**

   * Registers a NEW theme stylesheet with the database.

   * @param tsd Stylesheet description object

   * @return an <code>Integer</code> id of the registered Theme Stylesheet if successful;

   *                 <code>null</code> otherwise.

   */

  public Integer addThemeStylesheetDescription (ThemeStylesheetDescription tsd) throws Exception {

    Connection con = RDBMServices.getConnection();

    try {

      // Set autocommit false for the connection

      RDBMServices.setAutoCommit(con, false);

      Statement stmt = con.createStatement();

      try {

        // we assume that this is a new stylesheet.

        int id = counterStore.getIncrementIntegerId("UP_SS_THEME");

        tsd.setId(id);

        String sQuery = "INSERT INTO UP_SS_THEME (SS_ID,SS_NAME,SS_URI,SS_DESCRIPTION_URI,SS_DESCRIPTION_TEXT,STRUCT_SS_ID,SAMPLE_URI,SAMPLE_ICON_URI,MIME_TYPE,DEVICE_TYPE,SERIALIZER_NAME,UP_MODULE_CLASS) VALUES ("

            + id + ",'" + tsd.getStylesheetName() + "','" + tsd.getStylesheetURI() + "','" + tsd.getStylesheetDescriptionURI()

            + "','" + tsd.getStylesheetWordDescription() + "'," + tsd.getStructureStylesheetId() + ",'" + tsd.getSamplePictureURI()

            + "','" + tsd.getSampleIconURI() + "','" + tsd.getMimeType() + "','" + tsd.getDeviceType() + "','" + tsd.getSerializerName()

            + "','" + tsd.getCustomUserPreferencesManagerClass() + "')";

        if (log.isDebugEnabled())

            log.debug("RDBMUserLayoutStore::addThemeStylesheetDescription(): " + sQuery);

        stmt.executeUpdate(sQuery);

        // insert all stylesheet params

        for (Enumeration e = tsd.getStylesheetParameterNames(); e.hasMoreElements();) {

          String pName = (String)e.nextElement();

          sQuery = "INSERT INTO UP_SS_THEME_PARM (SS_ID,PARAM_NAME,PARAM_DEFAULT_VAL,PARAM_DESCRIPT,TYPE) VALUES (" + id +

              ",'" + pName + "','" + tsd.getStylesheetParameterDefaultValue(pName) + "','" + tsd.getStylesheetParameterWordDescription(pName)

              + "',1)";

          if (log.isDebugEnabled())

              log.debug("RDBMUserLayoutStore::addThemeStylesheetDescription(): " + sQuery);

          stmt.executeUpdate(sQuery);

        }

        // insert all channel attributes

        for (Enumeration e = tsd.getChannelAttributeNames(); e.hasMoreElements();) {

          String pName = (String)e.nextElement();

          sQuery = "INSERT INTO UP_SS_THEME_PARM (SS_ID,PARAM_NAME,PARAM_DEFAULT_VAL,PARAM_DESCRIPT,TYPE) VALUES (" + id +

              ",'" + pName + "','" + tsd.getChannelAttributeDefaultValue(pName) + "','" + tsd.getChannelAttributeWordDescription(pName)

              + "',3)";

          if (log.isDebugEnabled())

              log.debug("RDBMUserLayoutStore::addThemeStylesheetDescription(): " + sQuery);

          stmt.executeUpdate(sQuery);

        }

        // Commit the transaction

        RDBMServices.commit(con);

        return  new Integer(id);

      } catch (Exception e) {

        // Roll back the transaction

        RDBMServices.rollback(con);

        throw  e;

      } finally {

        stmt.close();

      }

    } finally {

      RDBMServices.releaseConnection(con);

    }

  }

  /**

   * Update the theme stylesheet description.

   * @param stylesheetDescriptionURI

   * @param stylesheetURI

   * @param stylesheetId

   * @return true if update succeeded, otherwise false

   */

  public boolean updateThemeStylesheetDescription (String stylesheetDescriptionURI, String stylesheetURI, int stylesheetId) {

    try {

      Document stylesheetDescriptionXML = getDOM(stylesheetDescriptionURI);

      String ssName = this.getRootElementTextValue(stylesheetDescriptionXML, "parentStructureStylesheet");

      // should thrown an exception

      if (ssName == null)

        return  false;

      // determine id of the parent structure stylesheet

      Integer ssId = getStructureStylesheetId(ssName);

      // stylesheet not found, should thrown an exception here

      if (ssId == null)

        return  false;

      ThemeStylesheetDescription sssd = new ThemeStylesheetDescription();

      sssd.setId(stylesheetId);

      sssd.setStructureStylesheetId(ssId.intValue());

      String xmlStylesheetName = this.getName(stylesheetDescriptionXML);

      String xmlStylesheetDescriptionText = this.getDescription(stylesheetDescriptionXML);

      sssd.setStylesheetName(xmlStylesheetName);

      sssd.setStylesheetURI(stylesheetURI);

      sssd.setStylesheetDescriptionURI(stylesheetDescriptionURI);

      sssd.setStylesheetWordDescription(xmlStylesheetDescriptionText);

      sssd.setMimeType(this.getRootElementTextValue(stylesheetDescriptionXML, "mimeType"));

      if (log.isDebugEnabled())

          log.debug("RDBMUserLayoutStore::updateThemeStylesheetDescription() : setting mimetype=\""

          + sssd.getMimeType() + "\"");

      sssd.setSerializerName(this.getRootElementTextValue(stylesheetDescriptionXML, "serializer"));

      if (log.isDebugEnabled())

          log.debug("RDBMUserLayoutStore::updateThemeStylesheetDescription() : setting serializerName=\""

          + sssd.getSerializerName() + "\"");

      sssd.setCustomUserPreferencesManagerClass(this.getRootElementTextValue(stylesheetDescriptionXML, "userPreferencesModuleClass"));

      sssd.setSamplePictureURI(this.getRootElementTextValue(stylesheetDescriptionXML, "samplePictureURI"));

      sssd.setSampleIconURI(this.getRootElementTextValue(stylesheetDescriptionXML, "sampleIconURI"));

      sssd.setDeviceType(this.getRootElementTextValue(stylesheetDescriptionXML, "deviceType"));

      // populate parameter and attriute tables

      this.populateParameterTable(stylesheetDescriptionXML, sssd);

      this.populateChannelAttributeTable(stylesheetDescriptionXML, sssd);

      updateThemeStylesheetDescription(sssd);

    } catch (Exception e) {

        if (log.isDebugEnabled())

            log.debug("Exception updating theme stylesheet description=" +

                    "[" + stylesheetDescriptionURI + "] stylesheetUri=["+ stylesheetURI +

                    "] stylesheetId=["+ stylesheetId + "]", e);

      return  false;

    }

    return  true;

  }

  /**

   * Update the structure stylesheet description

   * @param stylesheetDescriptionURI

   * @param stylesheetURI

   * @param stylesheetId

   * @return true if update succeeded, otherwise false

   */

  public boolean updateStructureStylesheetDescription (String stylesheetDescriptionURI, String stylesheetURI, int stylesheetId) {

    try {

      Document stylesheetDescriptionXML = getDOM(stylesheetDescriptionURI);

      StructureStylesheetDescription fssd = new StructureStylesheetDescription();

      String xmlStylesheetName = this.getName(stylesheetDescriptionXML);

      String xmlStylesheetDescriptionText = this.getDescription(stylesheetDescriptionXML);

      fssd.setId(stylesheetId);

      fssd.setStylesheetName(xmlStylesheetName);

      fssd.setStylesheetURI(stylesheetURI);

      fssd.setStylesheetDescriptionURI(stylesheetDescriptionURI);

      fssd.setStylesheetWordDescription(xmlStylesheetDescriptionText);

      // populate parameter and attriute tables

      this.populateParameterTable(stylesheetDescriptionXML, fssd);

      this.populateFolderAttributeTable(stylesheetDescriptionXML, fssd);

      this.populateChannelAttributeTable(stylesheetDescriptionXML, fssd);

      // now write out the database record

      updateStructureStylesheetDescription(fssd);

    } catch (Exception e) {

        if (log.isDebugEnabled())

            log.debug("Exception updating structure stylesheet description " +

                    "stylesheetDescriptionUri=[" + stylesheetDescriptionURI + "]" +

                    " stylesheetUri=[" + stylesheetURI +

                    "] stylesheetId=" + stylesheetId , e);

      return  false;

    }

    return  true;

  }

  /**

   * Add a structure stylesheet description

   * @param stylesheetDescriptionURI

   * @param stylesheetURI

   * @return an <code>Integer</code> id of the registered Structure Stylesheet description object if successful;

   *                      <code>null</code> otherwise.

   */

  public Integer addStructureStylesheetDescription (String stylesheetDescriptionURI, String stylesheetURI) {

    // need to read in the description file to obtain information such as name, word description and media list

    try {

      Document stylesheetDescriptionXML = getDOM(stylesheetDescriptionURI);

      StructureStylesheetDescription fssd = new StructureStylesheetDescription();

      String xmlStylesheetName = this.getName(stylesheetDescriptionXML);

      String xmlStylesheetDescriptionText = this.getDescription(stylesheetDescriptionXML);

      fssd.setStylesheetName(xmlStylesheetName);

      fssd.setStylesheetURI(stylesheetURI);

      fssd.setStylesheetDescriptionURI(stylesheetDescriptionURI);

      fssd.setStylesheetWordDescription(xmlStylesheetDescriptionText);

      // populate parameter and attriute tables

      this.populateParameterTable(stylesheetDescriptionXML, fssd);

      this.populateFolderAttributeTable(stylesheetDescriptionXML, fssd);

      this.populateChannelAttributeTable(stylesheetDescriptionXML, fssd);

      // now write out the database record

      // first the basic record

      //UserLayoutStoreFactory.getUserLayoutStoreImpl().addStructureStylesheetDescription(xmlStylesheetName, stylesheetURI, stylesheetDescriptionURI, xmlStylesheetDescriptionText);

      return  addStructureStylesheetDescription(fssd);

    } catch (Exception e) {

        if (log.isDebugEnabled())

            log.debug("Error adding stylesheet: " +

                    "description Uri=[" + stylesheetDescriptionURI + "] " +

                    "stylesheetUri=[" + stylesheetURI + "]", e);

    }

    return  null;

  }

  /**

   * Add theme stylesheet description

   * @param stylesheetDescriptionURI

   * @param stylesheetURI

   * @return an <code>Integer</code> id of the registered Theme Stylesheet if successful;

   *                 <code>null</code> otherwise.

   */

  public Integer addThemeStylesheetDescription (String stylesheetDescriptionURI, String stylesheetURI) {

    // need to read iN the description file to obtain information such as name, word description and mime type list

    try {

      Document stylesheetDescriptionXML = getDOM(stylesheetDescriptionURI);

      if (log.isDebugEnabled()){

          log.debug("RDBMUserLayoutStore::addThemeStylesheetDescription() : stylesheet name = " + this.getName(stylesheetDescriptionXML));

          log.debug("RDBMUserLayoutStore::addThemeStylesheetDescription() : stylesheet description = " + this.getDescription(stylesheetDescriptionXML));

      }

      String ssName = this.getRootElementTextValue(stylesheetDescriptionXML, "parentStructureStylesheet");

      // should thrown an exception

      if (ssName == null)

        return  null;

      // determine id of the parent structure stylesheet

      Integer ssId = getStructureStylesheetId(ssName);

      // stylesheet not found, should thrown an exception here

      if (ssId == null)

        return  null;

      ThemeStylesheetDescription sssd = new ThemeStylesheetDescription();

      sssd.setStructureStylesheetId(ssId.intValue());

      String xmlStylesheetName = this.getName(stylesheetDescriptionXML);

      String xmlStylesheetDescriptionText = this.getDescription(stylesheetDescriptionXML);

      sssd.setStylesheetName(xmlStylesheetName);

      sssd.setStylesheetURI(stylesheetURI);