Author: srowen
Date: Tue Nov 9 14:01:57 2010
New Revision: 1032985
URL: http://svn.apache.org/viewvc?rev=1032985&view=rev
Log:
Experimental PostgreSQL support
Added:
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java
- copied, changed from r1032231,
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java
- copied, changed from r1032231,
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLJDBCDataModel.java
Modified:
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractBooleanPrefJDBCDataModel.java
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractJDBCDataModel.java
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java
mahout/trunk/core/src/main/java/org/apache/mahout/common/IOUtils.java
Modified:
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractBooleanPrefJDBCDataModel.java
URL:
http://svn.apache.org/viewvc/mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractBooleanPrefJDBCDataModel.java?rev=1032985&r1=1032984&r2=1032985&view=diff
==============================================================================
---
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractBooleanPrefJDBCDataModel.java
(original)
+++
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractBooleanPrefJDBCDataModel.java
Tue Nov 9 14:01:57 2010
@@ -36,7 +36,9 @@ import com.google.common.base.Preconditi
public abstract class AbstractBooleanPrefJDBCDataModel extends
AbstractJDBCDataModel {
private static final Logger log =
LoggerFactory.getLogger(AbstractBooleanPrefJDBCDataModel.class);
-
+
+ static final String NO_SUCH_COLUMN = "NO_SUCH_COLUMN";
+
private final String setPreferenceSQL;
protected AbstractBooleanPrefJDBCDataModel(DataSource dataSource,
@@ -86,6 +88,11 @@ public abstract class AbstractBooleanPre
protected Preference buildPreference(ResultSet rs) throws SQLException {
return new BooleanPreference(getLongColumn(rs, 1), getLongColumn(rs, 2));
}
+
+ @Override
+ String getSetPreferenceSQL() {
+ return setPreferenceSQL;
+ }
@Override
public void setPreference(long userID, long itemID, float value) throws
TasteException {
Modified:
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractJDBCDataModel.java
URL:
http://svn.apache.org/viewvc/mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractJDBCDataModel.java?rev=1032985&r1=1032984&r2=1032985&view=diff
==============================================================================
---
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractJDBCDataModel.java
(original)
+++
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/AbstractJDBCDataModel.java
Tue Nov 9 14:01:57 2010
@@ -247,6 +247,10 @@ public abstract class AbstractJDBCDataMo
return preferenceColumn;
}
+ String getSetPreferenceSQL() {
+ return setPreferenceSQL;
+ }
+
@Override
public LongPrimitiveIterator getUserIDs() throws TasteException {
log.debug("Retrieving all users...");
Modified:
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java
URL:
http://svn.apache.org/viewvc/mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java?rev=1032985&r1=1032984&r2=1032985&view=diff
==============================================================================
---
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java
(original)
+++
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java
Tue Nov 9 14:01:57 2010
@@ -45,9 +45,7 @@ import org.apache.mahout.cf.taste.impl.c
* </p>
*/
public class MySQLBooleanPrefJDBCDataModel extends
AbstractBooleanPrefJDBCDataModel {
-
- private static final String NO_SUCH_COLUMN = "NO_SUCH_COLUMN";
-
+
/**
* <p>
* Creates a using the default {...@link javax.sql.DataSource} (named
Copied:
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java
(from r1032231,
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java)
URL:
http://svn.apache.org/viewvc/mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java?p2=mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java&p1=mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java&r1=1032231&r2=1032985&rev=1032985&view=diff
==============================================================================
---
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLBooleanPrefJDBCDataModel.java
(original)
+++
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java
Tue Nov 9 14:01:57 2010
@@ -17,92 +17,102 @@
package org.apache.mahout.cf.taste.impl.model.jdbc;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-
-import javax.sql.DataSource;
-
+import com.google.common.base.Preconditions;
import org.apache.mahout.cf.taste.common.TasteException;
import org.apache.mahout.cf.taste.impl.common.jdbc.AbstractJDBCComponent;
+import org.apache.mahout.common.IOUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
/**
* <p>
- * See also {...@link MySQLJDBCDataModel} -- same except deals with a table
without preference info:
+ * See also {...@link
org.apache.mahout.cf.taste.impl.model.jdbc.PostgreSQLJDBCDataModel} --
+ * same except deals with a table without preference info:
* </p>
- *
+ *
* <p>
- *
+ *
* <pre>
* CREATE TABLE taste_preferences (
* user_id BIGINT NOT NULL,
* item_id BIGINT NOT NULL,
- * PRIMARY KEY (user_id, item_id),
- * INDEX (user_id),
- * INDEX (item_id)
- * )
+ * PRIMARY KEY (user_id, item_id)
+ * );
+ * CREATE INDEX taste_preferences_user_id_index ON taste_preferences (user_id);
+ * CREATE INDEX taste_preferences_item_id_index ON taste_preferences (item_id);
* </pre>
- *
+ *
* </p>
+ *
+ * <p>See {...@link MySQLBooleanPrefJDBCDataModel} which is largely
identical.</p>
+ *
+ * @see MySQLBooleanPrefJDBCDataModel
*/
-public class MySQLBooleanPrefJDBCDataModel extends
AbstractBooleanPrefJDBCDataModel {
-
- private static final String NO_SUCH_COLUMN = "NO_SUCH_COLUMN";
-
+public class PostgreSQLBooleanPrefJDBCDataModel extends
AbstractBooleanPrefJDBCDataModel {
+
+ private static final Logger log =
LoggerFactory.getLogger(PostgreSQLBooleanPrefJDBCDataModel.class);
+ private static final String POSTGRESQL_DUPLICATE_KEY_STATE = "23505"; //
this is brittle...
+
/**
* <p>
* Creates a using the default {...@link javax.sql.DataSource} (named
* {...@link #DEFAULT_DATASOURCE_NAME} and default table/column names.
* </p>
- *
+ *
* @throws org.apache.mahout.cf.taste.common.TasteException
* if {...@link javax.sql.DataSource} can't be found
*/
- public MySQLBooleanPrefJDBCDataModel() throws TasteException {
+ public PostgreSQLBooleanPrefJDBCDataModel() throws TasteException {
this(DEFAULT_DATASOURCE_NAME);
}
-
+
/**
* <p>
* Creates a using the default {...@link javax.sql.DataSource} found
* under the given name, and using default table/column names.
* </p>
- *
+ *
* @param dataSourceName
* name of {...@link javax.sql.DataSource} to look up
* @throws org.apache.mahout.cf.taste.common.TasteException
* if {...@link javax.sql.DataSource} can't be found
*/
- public MySQLBooleanPrefJDBCDataModel(String dataSourceName) throws
TasteException {
+ public PostgreSQLBooleanPrefJDBCDataModel(String dataSourceName) throws
TasteException {
this(AbstractJDBCComponent.lookupDataSource(dataSourceName),
DEFAULT_PREFERENCE_TABLE,
DEFAULT_USER_ID_COLUMN,
DEFAULT_ITEM_ID_COLUMN,
DEFAULT_PREFERENCE_TIME_COLUMN);
}
-
+
/**
* <p>
* Creates a using the given {...@link javax.sql.DataSource} and default
* table/column names.
* </p>
- *
+ *
* @param dataSource
* {...@link javax.sql.DataSource} to use
*/
- public MySQLBooleanPrefJDBCDataModel(DataSource dataSource) {
+ public PostgreSQLBooleanPrefJDBCDataModel(DataSource dataSource) {
this(dataSource,
DEFAULT_PREFERENCE_TABLE,
DEFAULT_USER_ID_COLUMN,
DEFAULT_ITEM_ID_COLUMN,
DEFAULT_PREFERENCE_TIME_COLUMN);
}
-
+
/**
* <p>
* Creates a using the given {...@link javax.sql.DataSource} and default
* table/column names.
* </p>
- *
+ *
* @param dataSource
* {...@link javax.sql.DataSource} to use
* @param preferenceTable
@@ -113,11 +123,11 @@ public class MySQLBooleanPrefJDBCDataMod
* item ID column name
* @param timestampColumn timestamp column name (may be null)
*/
- public MySQLBooleanPrefJDBCDataModel(DataSource dataSource,
- String preferenceTable,
- String userIDColumn,
- String itemIDColumn,
- String timestampColumn) {
+ public PostgreSQLBooleanPrefJDBCDataModel(DataSource dataSource,
+ String preferenceTable,
+ String userIDColumn,
+ String itemIDColumn,
+ String timestampColumn) {
super(dataSource, preferenceTable, userIDColumn, itemIDColumn,
NO_SUCH_COLUMN,
// getPreferenceSQL
@@ -136,7 +146,7 @@ public class MySQLBooleanPrefJDBCDataMod
// getNumUsersSQL
"SELECT COUNT(DISTINCT " + userIDColumn + ") FROM " + preferenceTable,
// setPreferenceSQL
- "INSERT IGNORE INTO " + preferenceTable + '(' + userIDColumn + ',' +
itemIDColumn + ") VALUES (?,?)",
+ "INSERT INTO " + preferenceTable + '(' + userIDColumn + ',' +
itemIDColumn + ") VALUES (?,?)",
// removePreference SQL
"DELETE FROM " + preferenceTable + " WHERE " + userIDColumn + "=? AND
" + itemIDColumn + "=?",
// getUsersSQL
@@ -156,20 +166,34 @@ public class MySQLBooleanPrefJDBCDataMod
// getMinPreferenceSQL
"SELECT 1.0");
}
-
- @Override
- protected int getFetchSize() {
- // Need to return this for MySQL Connector/J to make it use streaming mode
- return Integer.MIN_VALUE;
- }
-
+
+ /**
+ * Override since PostgreSQL doesn't have the same non-standard capability
that MySQL has, to optionally
+ * ignore an insert that fails since the row exists already.
+ */
@Override
- protected void advanceResultSet(ResultSet resultSet, int n) throws
SQLException {
- // Can't use relative on MySQL Connector/J
- int i = 0;
- while ((i < n) && resultSet.next()) {
- i++;
+ public void setPreference(long userID, long itemID, float value) throws
TasteException {
+ Preconditions.checkArgument(!Float.isNaN(value), "Invalid value: " +
value);
+ log.debug("Setting preference for user {}, item {}", userID, itemID);
+
+ String setPreferenceSQL = getSetPreferenceSQL();
+ Connection conn = null;
+ PreparedStatement stmt = null;
+ try {
+ conn = getDataSource().getConnection();
+ stmt = conn.prepareStatement(setPreferenceSQL);
+ setLongParameter(stmt, 1, userID);
+ setLongParameter(stmt, 2, itemID);
+ log.debug("Executing SQL update: {}", setPreferenceSQL);
+ stmt.executeUpdate();
+ } catch (SQLException sqle) {
+ if (!POSTGRESQL_DUPLICATE_KEY_STATE.equals(sqle.getSQLState())) {
+ log.warn("Exception while setting preference", sqle);
+ throw new TasteException(sqle);
+ }
+ } finally {
+ IOUtils.quietClose(null, stmt, conn);
}
}
-
+
}
\ No newline at end of file
Copied:
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java
(from r1032231,
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLJDBCDataModel.java)
URL:
http://svn.apache.org/viewvc/mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java?p2=mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java&p1=mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLJDBCDataModel.java&r1=1032231&r2=1032985&rev=1032985&view=diff
==============================================================================
---
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/MySQLJDBCDataModel.java
(original)
+++
mahout/trunk/core/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java
Tue Nov 9 14:01:57 2010
@@ -17,144 +17,77 @@
package org.apache.mahout.cf.taste.impl.model.jdbc;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-
-import javax.sql.DataSource;
-
+import com.google.common.base.Preconditions;
import org.apache.mahout.cf.taste.common.TasteException;
import org.apache.mahout.cf.taste.impl.common.jdbc.AbstractJDBCComponent;
+import org.apache.mahout.common.IOUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
/**
* <p>
- * A {...@link org.apache.mahout.cf.taste.model.JDBCDataModel} backed by a
MySQL database and
+ * A {...@link org.apache.mahout.cf.taste.model.JDBCDataModel} backed by a
PostgreSQL database and
* accessed via JDBC. It may work with other JDBC databases. By default, this
class
- * assumes that there is a {...@link DataSource} available under the JNDI name
+ * assumes that there is a {...@link javax.sql.DataSource} available under the
JNDI name
* "jdbc/taste", which gives access to a database with a "taste_preferences"
table with the following schema:
* </p>
- *
- * <table>
- * <tr>
- * <th>user_id</th>
- * <th>item_id</th>
- * <th>preference</th>
- * </tr>
- * <tr>
- * <td>987</td>
- * <td>123</td>
- * <td>0.9</td>
- * </tr>
- * <tr>
- * <td>987</td>
- * <td>456</td>
- * <td>0.1</td>
- * </tr>
- * <tr>
- * <td>654</td>
- * <td>123</td>
- * <td>0.2</td>
- * </tr>
- * <tr>
- * <td>654</td>
- * <td>789</td>
- * <td>0.3</td>
- * </tr>
- * </table>
- *
- * <p>
- * <code>preference</code> must have a type compatible with the Java
<code>float</code> type.
- * <code>user_id</code> and <code>item_id</code> should be compatible with
long type (BIGINT). For example,
- * the following command sets up a suitable table in MySQL, complete with
primary key and indexes:
- * </p>
- *
+ *
* <p>
- *
+ *
* <pre>
* CREATE TABLE taste_preferences (
* user_id BIGINT NOT NULL,
* item_id BIGINT NOT NULL,
- * preference FLOAT NOT NULL,
- * PRIMARY KEY (user_id, item_id),
- * INDEX (user_id),
- * INDEX (item_id)
+ * preference REAL NOT NULL,
+ * PRIMARY KEY (user_id, item_id)
* )
+ * CREATE INDEX taste_preferences_user_id_index ON taste_preferences (user_id);
+ * CREATE INDEX taste_preferences_item_id_index ON taste_preferences (item_id);
* </pre>
- *
- * </p>
*
- * <p>The table may optionally have a <code>timestamp</code> column whose type
is compatible with Java
- * <code>long</code>.</p>
- *
- * <h3>Performance Notes</h3>
- *
- * <p>
- * See the notes in {...@link AbstractJDBCDataModel} regarding using
connection pooling. It's pretty vital to
- * performance.
- * </p>
- *
- * <p>
- * Some experimentation suggests that MySQL's InnoDB engine is faster than
MyISAM for these kinds of
- * applications. While MyISAM is the default and, I believe, generally
considered the lighter-weight and
- * faster of the two engines, my guess is the row-level locking of InnoDB
helps here. Your mileage may vary.
- * </p>
- *
- * <p>
- * Here are some key settings that can be tuned for MySQL, and suggested size
for a data set of around 1
- * million elements:
- * </p>
- *
- * <ul>
- * <li>innodb_buffer_pool_size=64M</li>
- * <li>myisam_sort_buffer_size=64M</li>
- * <li>query_cache_limit=64M</li>
- * <li>query_cache_min_res_unit=512K</li>
- * <li>query_cache_type=1</li>
- * <li>query_cache_size=64M</li>
- * </ul>
- *
- * <p>
- * Also consider setting some parameters on the MySQL Connector/J driver:
- * </p>
- *
- * <pre>
- * cachePreparedStatements = true
- * cachePrepStmts = true
- * cacheResultSetMetadata = true
- * alwaysSendSetIsolation = false
- * elideSetAutoCommits = true
- * </pre>
- *
- * <p>
- * Thanks to Amila Jayasooriya for contributing MySQL notes above as part of
Google Summer of Code 2007.
* </p>
+ *
+ * <p>See {...@link MySQLJDBCDataModel} which is largely identical.</p>
+ *
+ * @see MySQLJDBCDataModel
*/
-public class MySQLJDBCDataModel extends AbstractJDBCDataModel {
-
+public class PostgreSQLJDBCDataModel extends AbstractJDBCDataModel {
+
+ private static final Logger log =
LoggerFactory.getLogger(PostgreSQLJDBCDataModel.class);
+ private static final String POSTGRESQL_DUPLICATE_KEY_STATE = "23505"; //
this is brittle...
+
+ private final String updatePreferenceSQL;
+
/**
* <p>
- * Creates a using the default {...@link DataSource} (named
+ * Creates a using the default {...@link javax.sql.DataSource} (named
* {...@link #DEFAULT_DATASOURCE_NAME} and default table/column names.
* </p>
- *
- * @throws TasteException
- * if {...@link DataSource} can't be found
+ *
+ * @throws org.apache.mahout.cf.taste.common.TasteException
+ * if {...@link javax.sql.DataSource} can't be found
*/
- public MySQLJDBCDataModel() throws TasteException {
+ public PostgreSQLJDBCDataModel() throws TasteException {
this(DEFAULT_DATASOURCE_NAME);
}
-
+
/**
* <p>
- * Creates a using the default {...@link DataSource} found under the given
name, and
+ * Creates a using the default {...@link javax.sql.DataSource} found under
the given name, and
* using default table/column names.
* </p>
- *
+ *
* @param dataSourceName
- * name of {...@link DataSource} to look up
- * @throws TasteException
- * if {...@link DataSource} can't be found
+ * name of {...@link javax.sql.DataSource} to look up
+ * @throws org.apache.mahout.cf.taste.common.TasteException
+ * if {...@link javax.sql.DataSource} can't be found
*/
- public MySQLJDBCDataModel(String dataSourceName) throws TasteException {
+ public PostgreSQLJDBCDataModel(String dataSourceName) throws TasteException {
this(AbstractJDBCComponent.lookupDataSource(dataSourceName),
DEFAULT_PREFERENCE_TABLE,
DEFAULT_USER_ID_COLUMN,
@@ -162,16 +95,16 @@ public class MySQLJDBCDataModel extends
DEFAULT_PREFERENCE_COLUMN,
DEFAULT_PREFERENCE_TIME_COLUMN);
}
-
+
/**
* <p>
- * Creates a using the given {...@link DataSource} and default table/column
names.
+ * Creates a using the given {...@link javax.sql.DataSource} and default
table/column names.
* </p>
- *
+ *
* @param dataSource
- * {...@link DataSource} to use
+ * {...@link javax.sql.DataSource} to use
*/
- public MySQLJDBCDataModel(DataSource dataSource) {
+ public PostgreSQLJDBCDataModel(DataSource dataSource) {
this(dataSource,
DEFAULT_PREFERENCE_TABLE,
DEFAULT_USER_ID_COLUMN,
@@ -179,14 +112,14 @@ public class MySQLJDBCDataModel extends
DEFAULT_PREFERENCE_COLUMN,
DEFAULT_PREFERENCE_TIME_COLUMN);
}
-
+
/**
* <p>
- * Creates a using the given {...@link DataSource} and default table/column
names.
+ * Creates a using the given {...@link javax.sql.DataSource} and default
table/column names.
* </p>
- *
+ *
* @param dataSource
- * {...@link DataSource} to use
+ * {...@link javax.sql.DataSource} to use
* @param preferenceTable
* name of table containing preference data
* @param userIDColumn
@@ -197,12 +130,12 @@ public class MySQLJDBCDataModel extends
* preference column name
* @param timestampColumn timestamp column name (may be null)
*/
- public MySQLJDBCDataModel(DataSource dataSource,
- String preferenceTable,
- String userIDColumn,
- String itemIDColumn,
- String preferenceColumn,
- String timestampColumn) {
+ public PostgreSQLJDBCDataModel(DataSource dataSource,
+ String preferenceTable,
+ String userIDColumn,
+ String itemIDColumn,
+ String preferenceColumn,
+ String timestampColumn) {
super(dataSource, preferenceTable, userIDColumn, itemIDColumn,
preferenceColumn,
// getPreferenceSQL
"SELECT " + preferenceColumn + " FROM " + preferenceTable + " WHERE "
+ userIDColumn + "=? AND "
@@ -222,7 +155,7 @@ public class MySQLJDBCDataModel extends
"SELECT COUNT(DISTINCT " + userIDColumn + ") FROM " + preferenceTable,
// setPreferenceSQL
"INSERT INTO " + preferenceTable + '(' + userIDColumn + ',' +
itemIDColumn + ',' + preferenceColumn
- + ") VALUES (?,?,?) ON DUPLICATE KEY UPDATE " + preferenceColumn +
"=?",
+ + ") VALUES (?,?,?)",
// removePreference SQL
"DELETE FROM " + preferenceTable + " WHERE " + userIDColumn + "=? AND
" + itemIDColumn + "=?",
// getUsersSQL
@@ -239,20 +172,60 @@ public class MySQLJDBCDataModel extends
+ userIDColumn + ") WHERE tp1." + itemIDColumn + "=? and tp2." +
itemIDColumn + "=?",
"SELECT MAX(" + preferenceColumn + ") FROM " + preferenceTable,
"SELECT MIN(" + preferenceColumn + ") FROM " + preferenceTable);
+
+ updatePreferenceSQL = "UPDATE " + preferenceTable + " SET " +
preferenceColumn + "=? WHERE " + userIDColumn
+ + "=? AND " + itemIDColumn + "=?";
}
-
- @Override
- protected int getFetchSize() {
- // Need to return this for MySQL Connector/J to make it use streaming mode
- return Integer.MIN_VALUE;
- }
-
+
+ /**
+ * Override since PostgreSQL doesn't have the same non-standard capability
that MySQL has, to optionally
+ * insert or update in one statement.
+ */
@Override
- protected void advanceResultSet(ResultSet resultSet, int n) throws
SQLException {
- // Can't use relative on MySQL Connector/J
- int i = 0;
- while ((i < n) && resultSet.next()) {
- i++;
+ public void setPreference(long userID, long itemID, float value) throws
TasteException {
+ Preconditions.checkArgument(!Float.isNaN(value), "Invalid value: " +
value);
+
+ log.debug("Setting preference for user {}, item {}", userID, itemID);
+
+ String setPreferenceSQL = getSetPreferenceSQL();
+
+ Connection conn = null;
+ PreparedStatement stmt1 = null;
+ PreparedStatement stmt2 = null;
+ try {
+ conn = getDataSource().getConnection();
+
+ stmt1 = conn.prepareStatement(setPreferenceSQL);
+ setLongParameter(stmt1, 1, userID);
+ setLongParameter(stmt1, 2, itemID);
+ stmt1.setDouble(3, value);
+
+ log.debug("Executing SQL update: {}", setPreferenceSQL);
+ try {
+ stmt1.executeUpdate();
+ } catch (SQLException sqle) {
+ if (!POSTGRESQL_DUPLICATE_KEY_STATE.equals(sqle.getSQLState())) {
+ throw sqle;
+ }
+ }
+
+ // Continue with update; just found the key already exists
+
+ stmt2 = conn.prepareStatement(updatePreferenceSQL);
+ stmt2.setDouble(1, value);
+ setLongParameter(stmt2, 2, userID);
+ setLongParameter(stmt2, 3, itemID);
+
+ log.debug("Executing SQL update: {}", updatePreferenceSQL);
+ stmt2.executeUpdate();
+
+ } catch (SQLException sqle) {
+ log.warn("Exception while setting preference", sqle);
+ throw new TasteException(sqle);
+ } finally {
+ IOUtils.quietClose(null, stmt1, null);
+ IOUtils.quietClose(null, stmt2, null);
+ IOUtils.quietClose(null, null, conn);
}
}
Modified: mahout/trunk/core/src/main/java/org/apache/mahout/common/IOUtils.java
URL:
http://svn.apache.org/viewvc/mahout/trunk/core/src/main/java/org/apache/mahout/common/IOUtils.java?rev=1032985&r1=1032984&r2=1032985&view=diff
==============================================================================
--- mahout/trunk/core/src/main/java/org/apache/mahout/common/IOUtils.java
(original)
+++ mahout/trunk/core/src/main/java/org/apache/mahout/common/IOUtils.java Tue
Nov 9 14:01:57 2010
@@ -24,6 +24,7 @@ import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
+import com.google.common.base.Preconditions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -38,12 +39,15 @@ public final class IOUtils {
private IOUtils() { }
- public static void quietClose(Closeable closeable) {
- if (closeable != null) {
- try {
- closeable.close();
- } catch (IOException ioe) {
- log.warn("Unexpected exception while closing; continuing", ioe);
+ public static void quietClose(Closeable... closeables) {
+ Preconditions.checkNotNull(closeables);
+ for (Closeable closeable : closeables) {
+ if (closeable != null) {
+ try {
+ closeable.close();
+ } catch (IOException ioe) {
+ log.warn("Unexpected exception while closing; continuing", ioe);
+ }
}
}
}
