/**
   * Runs a simple SQL statement to check if the databse has been patched
   *
   * @return
   */
  private int checkSAK11204ForUpgrade() {
    String test =
        "select count(*) from CALENDAR_EVENT where (RANGE_START is null) or (RANGE_END is null)";
    Connection connection = null;
    Statement s = null;
    ResultSet rs = null;

    try {
      connection = sqlService.borrowConnection();
      s = connection.createStatement();
      rs = s.executeQuery(test);
      if (rs.next()) {
        long ntodo = rs.getLong(1);
        if (ntodo == 0) {
          log.debug("SAK-11204: Database has been migrated  ");
          return OK;
        } else {
          log.info("SAK-11204: Migration check, there are null range fields ");
          return MIGRATE;
        }
      } else {
        log.warn("SAK-11204: Could not count null range fields, assuming migrate ");
        return MIGRATE;
      }
    } catch (SQLException ex) {
      log.info(
          "SAK-11204: Migration check, CALENDAR_EVENT schema not uptodate, test query said:  "
              + ex.getMessage());
      return UPGRADE_SCHEMA;
    } finally {
      try {
        rs.close();
      } catch (Exception ex) {
      }
      try {
        s.close();
      } catch (Exception ex) {
      }
      try {
        if (connection != null) {
          sqlService.returnConnection(connection);
        }
      } catch (Exception ex) {
      }
    }
  }
Exemple #2
0
  public static java.sql.Connection borrowConnection() throws java.sql.SQLException {
    org.sakaiproject.db.api.SqlService service = getInstance();
    if (service == null) return null;

    return service.borrowConnection();
  }
  /** fill in the pubview db fields */
  protected void convertToPubView() {
    M_log.info("convertToPubView");

    try {
      // get a connection
      final Connection connection = m_sqlService.borrowConnection();
      boolean wasCommit = connection.getAutoCommit();
      connection.setAutoCommit(false);

      // read all message records that need conversion
      String sql = "select CHANNEL_ID, MESSAGE_ID, XML, PUBVIEW from " + m_rTableName;
      m_sqlService.dbRead(
          connection,
          sql,
          null,
          new SqlReader() {
            public Object readSqlResultRecord(ResultSet result) {
              try {
                // create the Resource from the db xml
                String channelId = result.getString(1);
                String messageId = result.getString(2);
                String xml = result.getString(3);
                String pubViewSetting = result.getString(4);

                // read the xml
                Document doc = Xml.readDocumentFromString(xml);

                // verify the root element
                Element root = doc.getDocumentElement();
                if (!root.getTagName().equals("message")) {
                  M_log.warn(
                      "convertToPubView(): XML root element not message: " + root.getTagName());
                  return null;
                }
                BaseMessageEdit m = new BaseMessageEdit(null, root);

                // check if the record already has pub view set in the properties
                boolean pubview = false;
                if (m.getProperties().getProperty(ResourceProperties.PROP_PUBVIEW) != null) {
                  // pub view set in properties and in db indicates all is well with this one
                  if ("1".equals(pubViewSetting)) {
                    return null;
                  }

                  // having the property overrides any realm setting...
                  pubview = true;
                }

                // if we don't know pubview from the props, check the realm
                else {
                  // m.getReference() won't work cause we didn't give it its channel...
                  Reference channel = m_entityManager.newReference(channelId);
                  String ref = messageReference(channel.getContext(), channel.getId(), m.getId());
                  pubview = getPubView(ref);

                  // if the pubview setting matches the db, and it's false, all is well
                  if ((!pubview) && ("0".equals(pubViewSetting))) {
                    return null;
                  }
                }

                // update those that have no pubview
                if (!pubview) {
                  String update =
                      "update "
                          + m_rTableName
                          + " set PUBVIEW = ? where CHANNEL_ID = ? and MESSAGE_ID = ?";
                  Object fields[] = new Object[3];
                  fields[0] = "0";
                  fields[1] = channelId;
                  fields[2] = messageId;
                  boolean ok = m_sqlService.dbWrite(connection, update, fields);

                  if (!ok)
                    M_log.info(
                        "convertToPubView: channel: "
                            + channelId
                            + " message: "
                            + messageId
                            + " pubview: "
                            + pubview
                            + " ok: "
                            + ok);
                }

                // update those that have pubview
                else {
                  // set the property
                  m.getPropertiesEdit()
                      .addProperty(ResourceProperties.PROP_PUBVIEW, Boolean.TRUE.toString());

                  // form updated XML
                  doc = Xml.createDocument();
                  m.toXml(doc, new Stack());
                  xml = Xml.writeDocumentToString(doc);

                  String update =
                      "update "
                          + m_rTableName
                          + " set PUBVIEW = ?, XML = ? where CHANNEL_ID = ? and MESSAGE_ID = ?";
                  Object fields[] = new Object[4];
                  fields[0] = "1";
                  fields[1] = xml;
                  fields[2] = channelId;
                  fields[3] = messageId;
                  boolean ok = m_sqlService.dbWrite(connection, update, fields);

                  if (!ok)
                    M_log.info(
                        "convertToPubView: channel: "
                            + channelId
                            + " message: "
                            + messageId
                            + " pubview: "
                            + pubview
                            + " ok: "
                            + ok);
                }

                return null;
              } catch (Throwable ignore) {
                return null;
              }
            }
          });

      connection.commit();
      connection.setAutoCommit(wasCommit);
      m_sqlService.returnConnection(connection);
    } catch (Throwable t) {
      M_log.warn("convertToPubView: failed: " + t);
    }

    M_log.info("convertToPubView: done");
  }
  /** fill in the draft and owner db fields */
  protected void convertToDraft() {
    M_log.info("convertToDraft");

    try {
      // get a connection
      final Connection connection = m_sqlService.borrowConnection();
      boolean wasCommit = connection.getAutoCommit();
      connection.setAutoCommit(false);

      // read all message records that need conversion
      String sql =
          "select CHANNEL_ID, MESSAGE_ID, XML from " + m_rTableName /* + " where OWNER is null" */;
      m_sqlService.dbRead(
          connection,
          sql,
          null,
          new SqlReader() {
            private int count = 0;

            public Object readSqlResultRecord(ResultSet result) {
              try {
                // create the Resource from the db xml
                String channelId = result.getString(1);
                String messageId = result.getString(2);
                String xml = result.getString(3);

                // read the xml
                Document doc = Xml.readDocumentFromString(xml);

                // verify the root element
                Element root = doc.getDocumentElement();
                if (!root.getTagName().equals("message")) {
                  M_log.warn(
                      "convertToDraft(): XML root element not message: " + root.getTagName());
                  return null;
                }
                Message m = new BaseMessageEdit(null, root);

                // pick up the fields
                String owner = m.getHeader().getFrom().getId();
                boolean draft = m.getHeader().getDraft();

                // update
                String update =
                    "update "
                        + m_rTableName
                        + " set OWNER = ?, DRAFT = ? where CHANNEL_ID = ? and MESSAGE_ID = ?";
                Object fields[] = new Object[4];
                fields[0] = owner;
                fields[1] = (draft ? "1" : "0");
                fields[2] = channelId;
                fields[3] = messageId;
                boolean ok = m_sqlService.dbWrite(connection, update, fields);

                if (!ok)
                  M_log.info(
                      "convertToDraft: channel: "
                          + channelId
                          + " message: "
                          + messageId
                          + " owner: "
                          + owner
                          + " draft: "
                          + draft
                          + " ok: "
                          + ok);

                count++;
                if (count % 100 == 0) {
                  M_log.info("convertToDraft: " + count);
                }
                return null;
              } catch (Throwable ignore) {
                return null;
              }
            }
          });

      connection.commit();
      connection.setAutoCommit(wasCommit);
      m_sqlService.returnConnection(connection);
    } catch (Throwable t) {
      M_log.warn("convertToDraft: failed: " + t);
    }

    M_log.info("convertToDraft: done");
  }