You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@mahout.apache.org by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org> on 2011/06/11 16:18:58 UTC
[jira] [Created] (MAHOUT-731) Add SQL92JDBCDataModel
Add SQL92JDBCDataModel
----------------------
Key: MAHOUT-731
URL: https://issues.apache.org/jira/browse/MAHOUT-731
Project: Mahout
Issue Type: New Feature
Components: Collaborative Filtering
Affects Versions: 0.6
Reporter: Daniel Xiaodan Zhou
Assignee: Sean Owen
Fix For: 0.6
I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by Ted Dunning <te...@gmail.com>.
But not for integration as a recommendation system which is what the
OP was asking about.
R is excellent for data-mining, but sucks for deployment and
integration. Mahout is a bit cumbersome
getting started with, but is awesome for integration of
recommendations and classification. For
the recommendation side, it isn't even all that bad for the getting
started part.
On Sun, Jun 12, 2011 at 11:31 PM, Lance Norskog <go...@gmail.com> wrote:
> R is probably a better choice for speed, management, output options
> and platform footprint reasons.
>
> On Sun, Jun 12, 2011 at 3:43 AM, Sean Owen <sr...@gmail.com> wrote:
>> This would be used in non-distributed contexts, yes.
>> On Jun 12, 2011 8:59 AM, "Ted Dunning" <te...@gmail.com> wrote:
>>> There are definitely drupal sites that can use recommendations.
>>> Whether they need map-reduce to scale this is a separate question.
>>>
>>> On Sat, Jun 11, 2011 at 11:10 PM, Lance Norskog <go...@gmail.com> wrote:
>>>> What are the uses for Mahout combined with Drupal? Are there Drupal
>>>> sites big enough to warrant Mahout's map-reduce scaling?
>>>>
>>>> On Sat, Jun 11, 2011 at 7:44 AM, Sean Owen (JIRA) <ji...@apache.org>
>> wrote:
>>>>>
>>>>> [
>> https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13047916#comment-13047916]
>>>>>
>>>>> Sean Owen commented on MAHOUT-731:
>>>>> ----------------------------------
>>>>>
>>>>> Seems fine to me. Just follow the pattern of the other implementations.
>>>>>
>>>>>> Add SQL92JDBCDataModel
>>>>>> ----------------------
>>>>>>
>>>>>> Key: MAHOUT-731
>>>>>> URL: https://issues.apache.org/jira/browse/MAHOUT-731
>>>>>> Project: Mahout
>>>>>> Issue Type: New Feature
>>>>>> Components: Collaborative Filtering
>>>>>> Affects Versions: 0.6
>>>>>> Reporter: Daniel Xiaodan Zhou
>>>>>> Assignee: Sean Owen
>>>>>> Labels: drupal_integration
>>>>>> Fix For: 0.6
>>>>>>
>>>>>>
>>>>>> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on
>> the sample code of PostgreSQLJDBCDataModel. This class will only use
>> standard SQL92. It won't be high performance, but would have maximum
>> compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is
>> quite important to Drupal/Mahout integration.
>>>>>> Before I actually write the code, is there any suggestions or concerns?
>> Thanks!
>>>>>
>>>>> --
>>>>> This message is automatically generated by JIRA.
>>>>> For more information on JIRA, see:
>> http://www.atlassian.com/software/jira
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Lance Norskog
>>>> goksron@gmail.com
>>>>
>>
>
>
>
> --
> Lance Norskog
> goksron@gmail.com
>
Re: [jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by Lance Norskog <go...@gmail.com>.
R is probably a better choice for speed, management, output options
and platform footprint reasons.
On Sun, Jun 12, 2011 at 3:43 AM, Sean Owen <sr...@gmail.com> wrote:
> This would be used in non-distributed contexts, yes.
> On Jun 12, 2011 8:59 AM, "Ted Dunning" <te...@gmail.com> wrote:
>> There are definitely drupal sites that can use recommendations.
>> Whether they need map-reduce to scale this is a separate question.
>>
>> On Sat, Jun 11, 2011 at 11:10 PM, Lance Norskog <go...@gmail.com> wrote:
>>> What are the uses for Mahout combined with Drupal? Are there Drupal
>>> sites big enough to warrant Mahout's map-reduce scaling?
>>>
>>> On Sat, Jun 11, 2011 at 7:44 AM, Sean Owen (JIRA) <ji...@apache.org>
> wrote:
>>>>
>>>> [
> https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13047916#comment-13047916]
>>>>
>>>> Sean Owen commented on MAHOUT-731:
>>>> ----------------------------------
>>>>
>>>> Seems fine to me. Just follow the pattern of the other implementations.
>>>>
>>>>> Add SQL92JDBCDataModel
>>>>> ----------------------
>>>>>
>>>>> Key: MAHOUT-731
>>>>> URL: https://issues.apache.org/jira/browse/MAHOUT-731
>>>>> Project: Mahout
>>>>> Issue Type: New Feature
>>>>> Components: Collaborative Filtering
>>>>> Affects Versions: 0.6
>>>>> Reporter: Daniel Xiaodan Zhou
>>>>> Assignee: Sean Owen
>>>>> Labels: drupal_integration
>>>>> Fix For: 0.6
>>>>>
>>>>>
>>>>> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on
> the sample code of PostgreSQLJDBCDataModel. This class will only use
> standard SQL92. It won't be high performance, but would have maximum
> compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is
> quite important to Drupal/Mahout integration.
>>>>> Before I actually write the code, is there any suggestions or concerns?
> Thanks!
>>>>
>>>> --
>>>> This message is automatically generated by JIRA.
>>>> For more information on JIRA, see:
> http://www.atlassian.com/software/jira
>>>>
>>>
>>>
>>>
>>> --
>>> Lance Norskog
>>> goksron@gmail.com
>>>
>
--
Lance Norskog
goksron@gmail.com
Re: [jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by Sean Owen <sr...@gmail.com>.
This would be used in non-distributed contexts, yes.
On Jun 12, 2011 8:59 AM, "Ted Dunning" <te...@gmail.com> wrote:
> There are definitely drupal sites that can use recommendations.
> Whether they need map-reduce to scale this is a separate question.
>
> On Sat, Jun 11, 2011 at 11:10 PM, Lance Norskog <go...@gmail.com> wrote:
>> What are the uses for Mahout combined with Drupal? Are there Drupal
>> sites big enough to warrant Mahout's map-reduce scaling?
>>
>> On Sat, Jun 11, 2011 at 7:44 AM, Sean Owen (JIRA) <ji...@apache.org>
wrote:
>>>
>>> [
https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13047916#comment-13047916]
>>>
>>> Sean Owen commented on MAHOUT-731:
>>> ----------------------------------
>>>
>>> Seems fine to me. Just follow the pattern of the other implementations.
>>>
>>>> Add SQL92JDBCDataModel
>>>> ----------------------
>>>>
>>>> Key: MAHOUT-731
>>>> URL: https://issues.apache.org/jira/browse/MAHOUT-731
>>>> Project: Mahout
>>>> Issue Type: New Feature
>>>> Components: Collaborative Filtering
>>>> Affects Versions: 0.6
>>>> Reporter: Daniel Xiaodan Zhou
>>>> Assignee: Sean Owen
>>>> Labels: drupal_integration
>>>> Fix For: 0.6
>>>>
>>>>
>>>> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on
the sample code of PostgreSQLJDBCDataModel. This class will only use
standard SQL92. It won't be high performance, but would have maximum
compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is
quite important to Drupal/Mahout integration.
>>>> Before I actually write the code, is there any suggestions or concerns?
Thanks!
>>>
>>> --
>>> This message is automatically generated by JIRA.
>>> For more information on JIRA, see:
http://www.atlassian.com/software/jira
>>>
>>
>>
>>
>> --
>> Lance Norskog
>> goksron@gmail.com
>>
Re: [jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by Ted Dunning <te...@gmail.com>.
There are definitely drupal sites that can use recommendations.
Whether they need map-reduce to scale this is a separate question.
On Sat, Jun 11, 2011 at 11:10 PM, Lance Norskog <go...@gmail.com> wrote:
> What are the uses for Mahout combined with Drupal? Are there Drupal
> sites big enough to warrant Mahout's map-reduce scaling?
>
> On Sat, Jun 11, 2011 at 7:44 AM, Sean Owen (JIRA) <ji...@apache.org> wrote:
>>
>> [ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13047916#comment-13047916 ]
>>
>> Sean Owen commented on MAHOUT-731:
>> ----------------------------------
>>
>> Seems fine to me. Just follow the pattern of the other implementations.
>>
>>> Add SQL92JDBCDataModel
>>> ----------------------
>>>
>>> Key: MAHOUT-731
>>> URL: https://issues.apache.org/jira/browse/MAHOUT-731
>>> Project: Mahout
>>> Issue Type: New Feature
>>> Components: Collaborative Filtering
>>> Affects Versions: 0.6
>>> Reporter: Daniel Xiaodan Zhou
>>> Assignee: Sean Owen
>>> Labels: drupal_integration
>>> Fix For: 0.6
>>>
>>>
>>> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
>>> Before I actually write the code, is there any suggestions or concerns? Thanks!
>>
>> --
>> This message is automatically generated by JIRA.
>> For more information on JIRA, see: http://www.atlassian.com/software/jira
>>
>
>
>
> --
> Lance Norskog
> goksron@gmail.com
>
Re: [jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by Lance Norskog <go...@gmail.com>.
What are the uses for Mahout combined with Drupal? Are there Drupal
sites big enough to warrant Mahout's map-reduce scaling?
On Sat, Jun 11, 2011 at 7:44 AM, Sean Owen (JIRA) <ji...@apache.org> wrote:
>
> [ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13047916#comment-13047916 ]
>
> Sean Owen commented on MAHOUT-731:
> ----------------------------------
>
> Seems fine to me. Just follow the pattern of the other implementations.
>
>> Add SQL92JDBCDataModel
>> ----------------------
>>
>> Key: MAHOUT-731
>> URL: https://issues.apache.org/jira/browse/MAHOUT-731
>> Project: Mahout
>> Issue Type: New Feature
>> Components: Collaborative Filtering
>> Affects Versions: 0.6
>> Reporter: Daniel Xiaodan Zhou
>> Assignee: Sean Owen
>> Labels: drupal_integration
>> Fix For: 0.6
>>
>>
>> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
>> Before I actually write the code, is there any suggestions or concerns? Thanks!
>
> --
> This message is automatically generated by JIRA.
> For more information on JIRA, see: http://www.atlassian.com/software/jira
>
--
Lance Norskog
goksron@gmail.com
[jira] [Updated] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Xiaodan Zhou updated MAHOUT-731:
---------------------------------------
Comment: was deleted
(was: Index: integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java
===================================================================
--- integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java (revision 0)
+++ integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java (revision 0)
@@ -0,0 +1,218 @@
+/**
+ * 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>
+ *
+ * <p>This class copies largely from {@link PostgreSQLBooleanPrefJDBCDataModel}.</p>
+ *
+ * @see PostgreSQLBooleanPrefJDBCDataModel
+ * @author Daniel Xiaodan Zhou (daniel@michiza.com)
+ */
+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 + "=?";
+ }
+
+ /**
+ * 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);
+ }
+ }
+
+}
\ No newline at end of file
Index: integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java
===================================================================
--- integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java (revision 0)
+++ integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java (revision 0)
@@ -0,0 +1,241 @@
+/**
+ * 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>
+ *
+ * <p>This class copies largely from {@link PostgreSQLJDBCDataModel}.</p>
+ *
+ * @see PostgreSQLJDBCDataModel
+ * @author Daniel Xiaodan Zhou (daniel@michiza.com)
+ */
+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 + "=?";
+ }
+
+ /**
+ * 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);
+ }
+ }
+
+}
)
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Xiaodan Zhou updated MAHOUT-731:
---------------------------------------
Status: Patch Available (was: Open)
Index: integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java
===================================================================
--- integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java (revision 0)
+++ integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92BooleanPrefJDBCDataModel.java (revision 0)
@@ -0,0 +1,218 @@
+/**
+ * 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>
+ *
+ * <p>This class copies largely from {@link PostgreSQLBooleanPrefJDBCDataModel}.</p>
+ *
+ * @see PostgreSQLBooleanPrefJDBCDataModel
+ * @author Daniel Xiaodan Zhou (daniel@michiza.com)
+ */
+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 + "=?";
+ }
+
+ /**
+ * 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);
+ }
+ }
+
+}
\ No newline at end of file
Index: integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java
===================================================================
--- integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java (revision 0)
+++ integration/src/main/java/org/apache/mahout/cf/taste/impl/model/jdbc/SQL92JDBCDataModel.java (revision 0)
@@ -0,0 +1,241 @@
+/**
+ * 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>
+ *
+ * <p>This class copies largely from {@link PostgreSQLJDBCDataModel}.</p>
+ *
+ * @see PostgreSQLJDBCDataModel
+ * @author Daniel Xiaodan Zhou (daniel@michiza.com)
+ */
+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 + "=?";
+ }
+
+ /**
+ * 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);
+ }
+ }
+
+}
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Sean Owen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13049006#comment-13049006 ]
Sean Owen commented on MAHOUT-731:
----------------------------------
Yes it's almost completely identical to the Postgres version -- this doesn't seem right. Either we can just relax the Postgres implementation to be SQL92 compliant, if it doesn't hurt it much, or this needs to be refactored to encapsulate their commonality. I am not comfortable with that much cloning.
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Sean Owen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13047916#comment-13047916 ]
Sean Owen commented on MAHOUT-731:
----------------------------------
Seems fine to me. Just follow the pattern of the other implementations.
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13049130#comment-13049130 ]
Daniel Xiaodan Zhou commented on MAHOUT-731:
--------------------------------------------
I think so too. The only different between SQL92 implementation and PostgreSQL is in setPreference(): SQL92 uses SELECT to test whether a record exists, and use INSERT or UPDATE accordingly. PostgreSQL simply INSERT first, in case of DUPLICATE KEY catches the exception (with a special exception ID) and UPDATE. Arguably this is a little faster than SQL92.
My view is to have SQL92 as base class, and PostgreSQL as subclass that only overrides setPreference(). In fact, the PostgreSQL implementation is also quite identical to MySQL implementation too. Further refactoring is to pull up the set of SQL statements as a member method getDefaultSQL() into AbstractJDBCDataModel. This removes duplicate code in MySQL/Postgre/SQL92 too.
If you think this is fine, I can do the refactoring. Do you think it's OK to check in this patch first for archival purpose?
Also, I'm not sure whether it's OK to use "@author" in Javadoc. Not so many classes in Taste have that...
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Xiaodan Zhou updated MAHOUT-731:
---------------------------------------
Attachment: SQL92.patch
patch attached. copied most of the code from PostgreSQLJDBCDataModel
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13050032#comment-13050032 ]
Daniel Xiaodan Zhou commented on MAHOUT-731:
--------------------------------------------
new patch attached. thanks for the explanation about @author tags.
the patch is successfully built in maven, and I have successfully tested it too.
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch, SQL92_postgres.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13049385#comment-13049385 ]
Daniel Xiaodan Zhou commented on MAHOUT-731:
--------------------------------------------
I will remove @author tags from SQL92 implementation to follow Mahout standard. For the record, I think the Apache coding guideline (http://portals.apache.org/development/code-standards.html) suggests using the @author tags.
I agree not to bother with MySQL. Between Postgres and SQL92, I'd rather have SQL92 as base. Because SQL92 is not likely to change in the future, but Postgres might. Especially if you look at PostgreSQLJDBCDataModel.java line 62:
private static final String POSTGRESQL_DUPLICATE_KEY_STATE = "23505"; // this is brittle...
This is probably going to change in the future, and might affect SQL92 if SQL92 derives from Postgres...
On the other hand, the benefit of duplicated code is that it allows some independence. Besides, the only duplication between SQL92 and Postgres is with the constructors (and the fact that they both override setPreference(), though differently). It has some but not much benefit to have Postgres extends SQL92 or vice versa. So I think the current implementation is fine too.
The reason I'd like to push SQL92 is that Drupal 7 supports Oracle/MSSQL/SQLite (http://drupal.org/requirements). So I need to make sure Mahout can access those databases too for Drupal/Mahout integration.
What do you think?
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Sean Owen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13049314#comment-13049314 ]
Sean Owen commented on MAHOUT-731:
----------------------------------
I think Apache code style guidelines discourage @author tags on individual files. This policy encourages everyone to view all the code as "theirs".
The patch is fine, as it is, and is attached here in JIRA "for the record". I don't think it needs to be committed. I assure you I also will make sure this gets in, in the right form.
And yes if you have time to adjust this patch as we've discussed, that would be great. I think you're welcome to keep it simple: extend the PostgreSQL implementation and override. Don't bother with MySQL. Even though it kind of makes some sense that the "SQL92" implementation is the base version, in practice a SQL92-compliant database that isn't one of the two supported RDBMSes is the rare beast.
That said, if you really wanted to do a big proper clean refactoring to put the SQL92 implementation as the parent, I wouldn't object if it were nice and tidy. I wouldn't tell you that you need to do this though.
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Sean Owen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13049671#comment-13049671 ]
Sean Owen commented on MAHOUT-731:
----------------------------------
(Those are just the conventions of one project, Portals, and I don't know that they're standard. Look at what you turn up with "apache @author tag" on Google for instance.)
Sounds great to have PostgreSQL extend SQL92, please go for it as you see fit.
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13050420#comment-13050420 ]
Daniel Xiaodan Zhou commented on MAHOUT-731:
--------------------------------------------
thanks!
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch, SQL92_postgres.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Xiaodan Zhou updated MAHOUT-731:
---------------------------------------
Attachment: SQL92_postgres.patch
new patch
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch, SQL92_postgres.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Daniel Xiaodan Zhou (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Xiaodan Zhou updated MAHOUT-731:
---------------------------------------
Status: Open (was: Patch Available)
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (MAHOUT-731) Add SQL92JDBCDataModel
Posted by "Sean Owen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/MAHOUT-731?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sean Owen resolved MAHOUT-731.
------------------------------
Resolution: Fixed
OK, submitted with some minor changes
> Add SQL92JDBCDataModel
> ----------------------
>
> Key: MAHOUT-731
> URL: https://issues.apache.org/jira/browse/MAHOUT-731
> Project: Mahout
> Issue Type: New Feature
> Components: Collaborative Filtering
> Affects Versions: 0.6
> Reporter: Daniel Xiaodan Zhou
> Assignee: Sean Owen
> Labels: drupal_integration
> Fix For: 0.6
>
> Attachments: SQL92.patch, SQL92_postgres.patch
>
>
> I'm thinking to work on a patch that adds SQL92JDBCDataModel based on the sample code of PostgreSQLJDBCDataModel. This class will only use standard SQL92. It won't be high performance, but would have maximum compatibility to other popular DBMS, such as Oracle, SQLlite, etc. This is quite important to Drupal/Mahout integration.
> Before I actually write the code, is there any suggestions or concerns? Thanks!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira