/**
* 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) {
}
}
}
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");
}