Author: srowen
Date: Thu Jun 16 09:47:02 2011
New Revision: 1136346
URL: http://svn.apache.org/viewvc?rev=1136346&view=rev
Log:
MAHOUT-731 SQL92 support
Added:
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java
Modified:
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java
Modified:
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java
URL:
http://svn.apache.org/viewvc/mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java?rev=1136346&r1=1136345&r2=1136346&view=diff
==============================================================================
---
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java
(original)
+++
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLBooleanPrefJDBCDataModel.java
Thu Jun 16 09:47:02 2011
@@ -19,7 +19,6 @@ package org.apache.mahout.cf.taste.impl.
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;
@@ -49,13 +48,12 @@ import java.sql.SQLException;
*
* </p>
*
- * <p>See {@link MySQLBooleanPrefJDBCDataModel} which is largely identical.</p>
- *
- * @see MySQLBooleanPrefJDBCDataModel
+ * @see PostgreSQLJDBCDataModel
*/
-public class PostgreSQLBooleanPrefJDBCDataModel extends
AbstractBooleanPrefJDBCDataModel {
+public class PostgreSQLBooleanPrefJDBCDataModel extends
SQL92BooleanPrefJDBCDataModel {
private static final Logger log =
LoggerFactory.getLogger(PostgreSQLBooleanPrefJDBCDataModel.class);
+
private static final String POSTGRESQL_DUPLICATE_KEY_STATE = "23505"; //
this is brittle...
/**
@@ -65,10 +63,9 @@ public class PostgreSQLBooleanPrefJDBCDa
* </p>
*
* @throws org.apache.mahout.cf.taste.common.TasteException
- * if {@link javax.sql.DataSource} can't be found
+ * if {@link javax.sql.DataSource} can't be found
*/
public PostgreSQLBooleanPrefJDBCDataModel() throws TasteException {
- this(DEFAULT_DATASOURCE_NAME);
}
/**
@@ -77,17 +74,12 @@ public class PostgreSQLBooleanPrefJDBCDa
* under the given name, and using default table/column names.
* </p>
*
- * @param dataSourceName
- * name of {@link javax.sql.DataSource} to look up
+ * @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
+ * if {@link javax.sql.DataSource} can't be found
*/
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);
+ super(dataSourceName);
}
/**
@@ -96,15 +88,10 @@ public class PostgreSQLBooleanPrefJDBCDa
* table/column names.
* </p>
*
- * @param dataSource
- * {@link javax.sql.DataSource} to use
+ * @param dataSource {@link javax.sql.DataSource} to use
*/
public PostgreSQLBooleanPrefJDBCDataModel(DataSource dataSource) {
- this(dataSource,
- DEFAULT_PREFERENCE_TABLE,
- DEFAULT_USER_ID_COLUMN,
- DEFAULT_ITEM_ID_COLUMN,
- DEFAULT_PREFERENCE_TIME_COLUMN);
+ super(dataSource);
}
/**
@@ -113,14 +100,10 @@ public class PostgreSQLBooleanPrefJDBCDa
* table/column names.
* </p>
*
- * @param dataSource
- * {@link javax.sql.DataSource} to use
- * @param preferenceTable
- * name of table containing preference data
- * @param userIDColumn
- * user ID column name
- * @param itemIDColumn
- * item ID column name
+ * @param dataSource {@link javax.sql.DataSource} to use
+ * @param preferenceTable name of table containing preference data
+ * @param userIDColumn user ID column name
+ * @param itemIDColumn item ID column name
* @param timestampColumn timestamp column name (may be null)
*/
public PostgreSQLBooleanPrefJDBCDataModel(DataSource dataSource,
@@ -128,43 +111,7 @@ public class PostgreSQLBooleanPrefJDBCDa
String userIDColumn,
String itemIDColumn,
String timestampColumn) {
- super(dataSource, preferenceTable, userIDColumn, itemIDColumn,
- NO_SUCH_COLUMN,
- // getPreferenceSQL
- "SELECT 1 FROM " + preferenceTable + " WHERE " + userIDColumn + "=?
AND " + itemIDColumn + "=?",
- // getPreferenceTimeSQL
- "SELECT " + timestampColumn + " FROM " + preferenceTable + " WHERE " +
userIDColumn + "=? AND "
- + itemIDColumn + "=?",
- // getUserSQL
- "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + " FROM " +
preferenceTable + " WHERE "
- + userIDColumn + "=?",
- // getAllUsersSQL
- "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + " FROM " +
preferenceTable + " ORDER BY "
- + userIDColumn,
- // getNumItemsSQL
- "SELECT COUNT(DISTINCT " + itemIDColumn + ") FROM " + preferenceTable,
- // getNumUsersSQL
- "SELECT COUNT(DISTINCT " + userIDColumn + ") FROM " + preferenceTable,
- // setPreferenceSQL
- "INSERT INTO " + preferenceTable + '(' + userIDColumn + ',' +
itemIDColumn + ") VALUES (?,?)",
- // removePreference SQL
- "DELETE FROM " + preferenceTable + " WHERE " + userIDColumn + "=? AND
" + itemIDColumn + "=?",
- // getUsersSQL
- "SELECT DISTINCT " + userIDColumn + " FROM " + preferenceTable + "
ORDER BY " + userIDColumn,
- // getItemsSQL
- "SELECT DISTINCT " + itemIDColumn + " FROM " + preferenceTable + "
ORDER BY " + itemIDColumn,
- // getPrefsForItemSQL
- "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + " FROM " +
preferenceTable + " WHERE "
- + itemIDColumn + "=? ORDER BY " + userIDColumn,
- // getNumPreferenceForItemSQL
- "SELECT COUNT(1) FROM " + preferenceTable + " WHERE " + itemIDColumn +
"=?",
- // getNumPreferenceForItemsSQL
- "SELECT COUNT(1) FROM " + preferenceTable + " tp1 JOIN " +
preferenceTable + " tp2 " + "USING ("
- + userIDColumn + ") WHERE tp1." + itemIDColumn + "=? and tp2." +
itemIDColumn + "=?",
- // getMaxPreferenceSQL
- "SELECT 1.0",
- // getMinPreferenceSQL
- "SELECT 1.0");
+ super(dataSource, preferenceTable, userIDColumn, itemIDColumn,
timestampColumn);
}
/**
@@ -196,4 +143,4 @@ public class PostgreSQLBooleanPrefJDBCDa
}
}
-}
\ No newline at end of file
+}
Modified:
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java
URL:
http://svn.apache.org/viewvc/mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java?rev=1136346&r1=1136345&r2=1136346&view=diff
==============================================================================
---
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java
(original)
+++
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/PostgreSQLJDBCDataModel.java
Thu Jun 16 09:47:02 2011
@@ -19,7 +19,6 @@ package org.apache.mahout.cf.taste.impl.
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;
@@ -52,16 +51,13 @@ import java.sql.SQLException;
*
* </p>
*
- * <p>See {@link MySQLJDBCDataModel} which is largely identical.</p>
- *
- * @see MySQLJDBCDataModel
+ * @see PostgreSQLJDBCDataModel
*/
-public class PostgreSQLJDBCDataModel extends AbstractJDBCDataModel {
+public class PostgreSQLJDBCDataModel extends SQL92JDBCDataModel {
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;
+ private static final String POSTGRESQL_DUPLICATE_KEY_STATE = "23505"; //
this is brittle...
/**
* <p>
@@ -70,10 +66,9 @@ public class PostgreSQLJDBCDataModel ext
* </p>
*
* @throws org.apache.mahout.cf.taste.common.TasteException
- * if {@link javax.sql.DataSource} can't be found
+ * if {@link javax.sql.DataSource} can't be found
*/
public PostgreSQLJDBCDataModel() throws TasteException {
- this(DEFAULT_DATASOURCE_NAME);
}
/**
@@ -82,18 +77,12 @@ public class PostgreSQLJDBCDataModel ext
* using default table/column names.
* </p>
*
- * @param dataSourceName
- * name of {@link javax.sql.DataSource} to look up
+ * @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
+ * if {@link javax.sql.DataSource} can't be found
*/
public PostgreSQLJDBCDataModel(String dataSourceName) throws TasteException {
- this(AbstractJDBCComponent.lookupDataSource(dataSourceName),
- DEFAULT_PREFERENCE_TABLE,
- DEFAULT_USER_ID_COLUMN,
- DEFAULT_ITEM_ID_COLUMN,
- DEFAULT_PREFERENCE_COLUMN,
- DEFAULT_PREFERENCE_TIME_COLUMN);
+ super(dataSourceName);
}
/**
@@ -101,16 +90,10 @@ public class PostgreSQLJDBCDataModel ext
* Creates a using the given {@link javax.sql.DataSource} and default
table/column names.
* </p>
*
- * @param dataSource
- * {@link javax.sql.DataSource} to use
+ * @param dataSource {@link javax.sql.DataSource} to use
*/
public PostgreSQLJDBCDataModel(DataSource dataSource) {
- this(dataSource,
- DEFAULT_PREFERENCE_TABLE,
- DEFAULT_USER_ID_COLUMN,
- DEFAULT_ITEM_ID_COLUMN,
- DEFAULT_PREFERENCE_COLUMN,
- DEFAULT_PREFERENCE_TIME_COLUMN);
+ super(dataSource);
}
/**
@@ -118,17 +101,12 @@ public class PostgreSQLJDBCDataModel ext
* 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
- * name of table containing preference data
- * @param userIDColumn
- * user ID column name
- * @param itemIDColumn
- * item ID column name
- * @param preferenceColumn
- * preference column name
- * @param timestampColumn timestamp column name (may be null)
+ * @param dataSource {@link javax.sql.DataSource} to use
+ * @param preferenceTable name of table containing preference data
+ * @param userIDColumn user ID column name
+ * @param itemIDColumn item ID column name
+ * @param preferenceColumn preference column name
+ * @param timestampColumn timestamp column name (may be null)
*/
public PostgreSQLJDBCDataModel(DataSource dataSource,
String preferenceTable,
@@ -136,45 +114,7 @@ public class PostgreSQLJDBCDataModel ext
String itemIDColumn,
String preferenceColumn,
String timestampColumn) {
- super(dataSource, preferenceTable, userIDColumn, itemIDColumn,
preferenceColumn,
- // getPreferenceSQL
- "SELECT " + preferenceColumn + " FROM " + preferenceTable + " WHERE "
+ userIDColumn + "=? AND "
- + itemIDColumn + "=?",
- // getPreferenceTimeSQL
- "SELECT " + timestampColumn + " FROM " + preferenceTable + " WHERE " +
userIDColumn + "=? AND "
- + itemIDColumn + "=?",
- // getUserSQL
- "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + ", " +
preferenceColumn + " FROM " + preferenceTable
- + " WHERE " + userIDColumn + "=? ORDER BY " + itemIDColumn,
- // getAllUsersSQL
- "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + ", " +
preferenceColumn + " FROM " + preferenceTable
- + " ORDER BY " + userIDColumn + ", " + itemIDColumn,
- // getNumItemsSQL
- "SELECT COUNT(DISTINCT " + itemIDColumn + ") FROM " + preferenceTable,
- // getNumUsersSQL
- "SELECT COUNT(DISTINCT " + userIDColumn + ") FROM " + preferenceTable,
- // setPreferenceSQL
- "INSERT INTO " + preferenceTable + '(' + userIDColumn + ',' +
itemIDColumn + ',' + preferenceColumn
- + ") VALUES (?,?,?)",
- // removePreference SQL
- "DELETE FROM " + preferenceTable + " WHERE " + userIDColumn + "=? AND
" + itemIDColumn + "=?",
- // getUsersSQL
- "SELECT DISTINCT " + userIDColumn + " FROM " + preferenceTable + "
ORDER BY " + userIDColumn,
- // getItemsSQL
- "SELECT DISTINCT " + itemIDColumn + " FROM " + preferenceTable + "
ORDER BY " + itemIDColumn,
- // getPrefsForItemSQL
- "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + ", " +
preferenceColumn + " FROM " + preferenceTable
- + " WHERE " + itemIDColumn + "=? ORDER BY " + userIDColumn,
- // getNumPreferenceForItemSQL
- "SELECT COUNT(1) FROM " + preferenceTable + " WHERE " + itemIDColumn +
"=?",
- // getNumPreferenceForItemsSQL
- "SELECT COUNT(1) FROM " + preferenceTable + " tp1 JOIN " +
preferenceTable + " tp2 " + "USING ("
- + 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 + "=?";
+ super(dataSource, preferenceTable, userIDColumn, itemIDColumn,
preferenceColumn, timestampColumn);
}
/**
@@ -211,12 +151,12 @@ public class PostgreSQLJDBCDataModel ext
// Continue with update; just found the key already exists
- stmt2 = conn.prepareStatement(updatePreferenceSQL);
+ stmt2 = conn.prepareStatement(getUpdatePreferenceSQL());
stmt2.setDouble(1, value);
setLongParameter(stmt2, 2, userID);
setLongParameter(stmt2, 3, itemID);
- log.debug("Executing SQL update: {}", updatePreferenceSQL);
+ log.debug("Executing SQL update: {}", getUpdatePreferenceSQL());
stmt2.executeUpdate();
} catch (SQLException sqle) {
Added:
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java
URL:
http://svn.apache.org/viewvc/mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java?rev=1136346&view=auto
==============================================================================
---
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java
(added)
+++
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java
Thu Jun 16 09:47:02 2011
@@ -0,0 +1,219 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF 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.
+ */
+
+package org.apache.mahout.cf.taste.impl.model.jdbc;
+
+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.ResultSet;
+import java.sql.SQLException;
+
+/**
+ * <p>
+ * See also {@link
org.apache.mahout.cf.taste.impl.model.jdbc.SQL92JDBCDataModel} --
+ * 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)
+ * );
+ * 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>
+ *
+ * @see SQL92JDBCDataModel
+ */
+public class SQL92BooleanPrefJDBCDataModel extends
AbstractBooleanPrefJDBCDataModel {
+
+ private static final Logger log =
LoggerFactory.getLogger(SQL92BooleanPrefJDBCDataModel.class);
+
+ private final String verifyPreferenceSQL;
+
+ /**
+ * <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 SQL92BooleanPrefJDBCDataModel() 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 SQL92BooleanPrefJDBCDataModel(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 SQL92BooleanPrefJDBCDataModel(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
+ * name of table containing preference data
+ * @param userIDColumn
+ * user ID column name
+ * @param itemIDColumn
+ * item ID column name
+ * @param timestampColumn timestamp column name (may be null)
+ */
+ public SQL92BooleanPrefJDBCDataModel(DataSource dataSource,
+ String preferenceTable,
+ String userIDColumn,
+ String itemIDColumn,
+ String timestampColumn) {
+ super(dataSource, preferenceTable, userIDColumn, itemIDColumn,
+ NO_SUCH_COLUMN,
+ // getPreferenceSQL
+ "SELECT 1 FROM " + preferenceTable + " WHERE " + userIDColumn + "=?
AND " + itemIDColumn + "=?",
+ // getPreferenceTimeSQL
+ "SELECT " + timestampColumn + " FROM " + preferenceTable + " WHERE " +
userIDColumn + "=? AND "
+ + itemIDColumn + "=?",
+ // getUserSQL
+ "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + " FROM " +
preferenceTable + " WHERE "
+ + userIDColumn + "=?",
+ // getAllUsersSQL
+ "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + " FROM " +
preferenceTable + " ORDER BY "
+ + userIDColumn,
+ // getNumItemsSQL
+ "SELECT COUNT(DISTINCT " + itemIDColumn + ") FROM " + preferenceTable,
+ // getNumUsersSQL
+ "SELECT COUNT(DISTINCT " + userIDColumn + ") FROM " + preferenceTable,
+ // setPreferenceSQL
+ "INSERT INTO " + preferenceTable + '(' + userIDColumn + ',' +
itemIDColumn + ") VALUES (?,?)",
+ // removePreference SQL
+ "DELETE FROM " + preferenceTable + " WHERE " + userIDColumn + "=? AND
" + itemIDColumn + "=?",
+ // getUsersSQL
+ "SELECT DISTINCT " + userIDColumn + " FROM " + preferenceTable + "
ORDER BY " + userIDColumn,
+ // getItemsSQL
+ "SELECT DISTINCT " + itemIDColumn + " FROM " + preferenceTable + "
ORDER BY " + itemIDColumn,
+ // getPrefsForItemSQL
+ "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + " FROM " +
preferenceTable + " WHERE "
+ + itemIDColumn + "=? ORDER BY " + userIDColumn,
+ // getNumPreferenceForItemSQL
+ "SELECT COUNT(1) FROM " + preferenceTable + " WHERE " + itemIDColumn +
"=?",
+ // getNumPreferenceForItemsSQL
+ "SELECT COUNT(1) FROM " + preferenceTable + " tp1 JOIN " +
preferenceTable + " tp2 " + "USING ("
+ + userIDColumn + ") WHERE tp1." + itemIDColumn + "=? and tp2." +
itemIDColumn + "=?",
+ // getMaxPreferenceSQL
+ "SELECT 1.0",
+ // getMinPreferenceSQL
+ "SELECT 1.0");
+
+ verifyPreferenceSQL = "SELECT 1 FROM " + preferenceTable + " WHERE " +
userIDColumn
+ + "=? AND " + itemIDColumn + "=?";
+ }
+
+ protected String getVerifyPreferenceSQL() {
+ return verifyPreferenceSQL;
+ }
+
+ /**
+ * 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
+ public void setPreference(long userID, long itemID, float value) throws
TasteException {
+ Preconditions.checkArgument(!Float.isNaN(value), "NaN 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(verifyPreferenceSQL,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
+ setLongParameter(stmt1, 1, userID);
+ setLongParameter(stmt1, 2, itemID);
+ ResultSet rs = stmt1.executeQuery();
+
+ // test if the record exists already.
+ if (!rs.first()) {
+ stmt2 = conn.prepareStatement(setPreferenceSQL);
+ setLongParameter(stmt2, 1, userID);
+ setLongParameter(stmt2, 2, itemID);
+ stmt2.setDouble(3, value);
+
+ log.debug("Executing SQL update: {}", setPreferenceSQL);
+ stmt2.executeUpdate();
+ }
+ } catch (SQLException sqle) {
+ log.warn("Exception while setting preference", sqle);
+ throw new TasteException(sqle);
+ } finally {
+ IOUtils.quietClose(stmt1);
+ IOUtils.quietClose(stmt2);
+ IOUtils.quietClose(conn);
+ }
+ }
+
+}
Added:
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java
URL:
http://svn.apache.org/viewvc/mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java?rev=1136346&view=auto
==============================================================================
---
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java
(added)
+++
mahout/trunk/integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java
Thu Jun 16 09:47:02 2011
@@ -0,0 +1,246 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF 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.
+ */
+
+package org.apache.mahout.cf.taste.impl.model.jdbc;
+
+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.ResultSet;
+import java.sql.SQLException;
+
+/**
+ * <p>
+ * A {@link org.apache.mahout.cf.taste.model.JDBCDataModel} backed by a SQL92
compatible database and
+ * accessed via JDBC. It should work with most JDBC databases, although not
optimized for performance.
+ * By default, this class 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>
+ *
+ * <p>
+ *
+ * <pre>
+ * CREATE TABLE taste_preferences (
+ * user_id BIGINT NOT NULL,
+ * item_id BIGINT NOT NULL,
+ * 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>
+ *
+ * @see SQL92BooleanPrefJDBCDataModel
+ */
+public class SQL92JDBCDataModel extends AbstractJDBCDataModel {
+
+ private static final Logger log =
LoggerFactory.getLogger(SQL92JDBCDataModel.class);
+
+ private final String updatePreferenceSQL;
+ private final String verifyPreferenceSQL;
+
+ /**
+ * <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 SQL92JDBCDataModel() 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 SQL92JDBCDataModel(String dataSourceName) throws TasteException {
+ this(AbstractJDBCComponent.lookupDataSource(dataSourceName),
+ DEFAULT_PREFERENCE_TABLE,
+ DEFAULT_USER_ID_COLUMN,
+ DEFAULT_ITEM_ID_COLUMN,
+ DEFAULT_PREFERENCE_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 SQL92JDBCDataModel(DataSource dataSource) {
+ this(dataSource,
+ DEFAULT_PREFERENCE_TABLE,
+ DEFAULT_USER_ID_COLUMN,
+ DEFAULT_ITEM_ID_COLUMN,
+ DEFAULT_PREFERENCE_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
+ * name of table containing preference data
+ * @param userIDColumn
+ * user ID column name
+ * @param itemIDColumn
+ * item ID column name
+ * @param preferenceColumn
+ * preference column name
+ * @param timestampColumn timestamp column name (may be null)
+ */
+ public SQL92JDBCDataModel(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 "
+ + itemIDColumn + "=?",
+ // getPreferenceTimeSQL
+ "SELECT " + timestampColumn + " FROM " + preferenceTable + " WHERE " +
userIDColumn + "=? AND "
+ + itemIDColumn + "=?",
+ // getUserSQL
+ "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + ", " +
preferenceColumn + " FROM " + preferenceTable
+ + " WHERE " + userIDColumn + "=? ORDER BY " + itemIDColumn,
+ // getAllUsersSQL
+ "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + ", " +
preferenceColumn + " FROM " + preferenceTable
+ + " ORDER BY " + userIDColumn + ", " + itemIDColumn,
+ // getNumItemsSQL
+ "SELECT COUNT(DISTINCT " + itemIDColumn + ") FROM " + preferenceTable,
+ // getNumUsersSQL
+ "SELECT COUNT(DISTINCT " + userIDColumn + ") FROM " + preferenceTable,
+ // setPreferenceSQL
+ "INSERT INTO " + preferenceTable + '(' + userIDColumn + ',' +
itemIDColumn + ',' + preferenceColumn
+ + ") VALUES (?,?,?)",
+ // removePreference SQL
+ "DELETE FROM " + preferenceTable + " WHERE " + userIDColumn + "=? AND
" + itemIDColumn + "=?",
+ // getUsersSQL
+ "SELECT DISTINCT " + userIDColumn + " FROM " + preferenceTable + "
ORDER BY " + userIDColumn,
+ // getItemsSQL
+ "SELECT DISTINCT " + itemIDColumn + " FROM " + preferenceTable + "
ORDER BY " + itemIDColumn,
+ // getPrefsForItemSQL
+ "SELECT DISTINCT " + userIDColumn + ", " + itemIDColumn + ", " +
preferenceColumn + " FROM " + preferenceTable
+ + " WHERE " + itemIDColumn + "=? ORDER BY " + userIDColumn,
+ // getNumPreferenceForItemSQL
+ "SELECT COUNT(1) FROM " + preferenceTable + " WHERE " + itemIDColumn +
"=?",
+ // getNumPreferenceForItemsSQL
+ "SELECT COUNT(1) FROM " + preferenceTable + " tp1 JOIN " +
preferenceTable + " tp2 " + "USING ("
+ + userIDColumn + ") WHERE tp1." + itemIDColumn + "=? and tp2." +
itemIDColumn + "=?",
+ // getMaxPreferenceSQL
+ "SELECT MAX(" + preferenceColumn + ") FROM " + preferenceTable,
+ // getMinPreferenceSQL
+ "SELECT MIN(" + preferenceColumn + ") FROM " + preferenceTable);
+
+ updatePreferenceSQL = "UPDATE " + preferenceTable + " SET " +
preferenceColumn + "=? WHERE " + userIDColumn
+ + "=? AND " + itemIDColumn + "=?";
+ verifyPreferenceSQL = "SELECT " + preferenceColumn + " FROM " +
preferenceTable + " WHERE " + userIDColumn
+ + "=? AND " + itemIDColumn + "=?";
+ }
+
+ protected String getUpdatePreferenceSQL() {
+ return updatePreferenceSQL;
+ }
+
+ protected String getVerifyPreferenceSQL() {
+ return verifyPreferenceSQL;
+ }
+
+ /**
+ * Override since SQL92 doesn't have the same non-standard capability that
MySQL has, to optionally
+ * insert or update in one statement.
+ */
+ @Override
+ public void setPreference(long userID, long itemID, float value) throws
TasteException {
+ Preconditions.checkArgument(!Float.isNaN(value), "NaN value");
+ log.debug("Setting preference for user {}, item {}", userID, itemID);
+
+ String setPreferenceSQL = getSetPreferenceSQL();
+
+ Connection conn = null;
+ PreparedStatement stmt1 = null;
+ PreparedStatement stmt2 = null;
+ PreparedStatement stmt3 = null;
+ try {
+ conn = getDataSource().getConnection();
+
+ stmt1 = conn.prepareStatement(verifyPreferenceSQL,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
+ setLongParameter(stmt1, 1, userID);
+ setLongParameter(stmt1, 2, itemID);
+ ResultSet rs = stmt1.executeQuery();
+
+ // test if the record exists already.
+ if (rs.first()) {
+ // then we update the record.
+ stmt2 = conn.prepareStatement(updatePreferenceSQL);
+ stmt2.setDouble(1, value);
+ setLongParameter(stmt2, 2, userID);
+ setLongParameter(stmt2, 3, itemID);
+
+ log.debug("Executing SQL update: {}", updatePreferenceSQL);
+ stmt2.executeUpdate();
+
+ } else {
+ // we'll insert the record
+ stmt3 = conn.prepareStatement(setPreferenceSQL);
+ setLongParameter(stmt3, 1, userID);
+ setLongParameter(stmt3, 2, itemID);
+ stmt3.setDouble(3, value);
+
+ log.debug("Executing SQL update: {}", setPreferenceSQL);
+ stmt3.executeUpdate();
+ }
+ } catch (SQLException sqle) {
+ log.warn("Exception while setting preference", sqle);
+ throw new TasteException(sqle);
+ } finally {
+ IOUtils.quietClose(stmt1);
+ IOUtils.quietClose(stmt2);
+ IOUtils.quietClose(stmt3);
+ IOUtils.quietClose(conn);
+ }
+ }
+
+}
