Save rating to database

4 files changed, 121 insertions, 12 deletions

diff --git a/src/database/ConnectionBuilder.java b/src/database/ConnectionBuilder.java
index 74e5c33..f209344 100644
--- a/src/database/ConnectionBuilder.java
+++ b/src/database/ConnectionBuilder.java
@@ -25,7 +25,7 @@ public class ConnectionBuilder {
     public ConnectionBuilder() {
         dbms = "postgresql";
         serverName = "localhost";
-        port = 5432;
+        port = 2014;
     }
 
     public ConnectionBuilder setDbms(String dbms) {
diff --git a/src/database/QueryUtils.java b/src/database/QueryUtils.java
new file mode 100644
index 0000000..9aab081
--- /dev/null
+++ b/src/database/QueryUtils.java
@@ -0,0 +1,102 @@
+package database;
+
+import java.sql.SQLException;
+import java.util.Locale;
+
+/**
+ * Utilities to create queries.
+ *
+ * @author Maurice Laveaux
+ */
+public class QueryUtils {
+    public final static String insertRating 
+            = buildQuery("mentionsbrand", new String[]{"tweetid","brand"},"tweetid","brand", "rating");
+    public final static String insertProfile
+            = buildQuery("twitteruser", new String[]{"userid"},
+            "userid", "displayname", "timezone", "tweetcount", "followercount",
+            "followedcount", "location", "tweetname", "createdat::timestamptz",
+            "language", "description", "verified");
+
+    public final static String insertTweet
+            = buildQuery("tweet", new String[]{"tweetid"},
+            "tweetid", "createdat::timestamptz", "favcount", "retweetcount",
+            "text", "coordinates::point",
+            "language", "retweetid", "replyid", "place",
+            "userid");
+
+    public final static String insertHash
+            = buildQuery("hashtag", null, "tweetid", "hashtag");
+
+    public final static String insertUserUrl
+            = buildQuery("userUrl", null, "userid", "url");
+
+    public final static String insertTweetUrl
+            = buildQuery("tweetUrl", null, "tweetid", "url");
+
+    public final static String insertMentions
+            = buildQuery("mentionsuser", null, "tweetid", "userid");
+
+    public final static String insertBrand
+            = buildQuery("mentionsbrand", null, "tweetid", "brand");
+
+    /**
+     * Builds an upsert query for a table and keys.
+     *
+     * @param table Table to insert or update the data.
+     * @param primaryKeys The primary keys for the table or null if all keys
+     * form the primary key.
+     * @param keys The keys to be inserted or updated.
+     * @return A SQL query containing named parameters.
+     */
+    private static String buildQuery(String table, String[] primaryKeys,
+            String... keys) {
+        String sep = "";
+        String fields = ""; // a, b, c
+        String values = ""; // :a, :b, :c
+        String set_values = ""; // a = nv.a, b = nv.b, c = nv.c
+        String pkey_matches = "";
+        for (String field : keys) {
+            String key = field.replaceFirst("::\\w+", "");
+            fields += sep + key;
+            values += sep + ":" + field;
+            // "u" is "table that gets updated", "nv" is "new values"
+            set_values += sep + key + " = nv." + key;
+            sep = ", ";
+        }
+        sep = "";
+        // assume that all fields form the primary key if pks are missing
+        if (primaryKeys == null) {
+            primaryKeys = keys;
+        }
+        for (String pkey : primaryKeys) {
+            assert !pkey.contains("::") : "Specify primary keys when type cast is given";
+            pkey_matches += sep + "u." + pkey + " = nv." + pkey;
+            sep = " AND ";
+        }
+
+        String sql;
+        // CTE that holds new values
+        sql = "WITH nv (" + fields + ") AS (\n";
+        sql += "    VALUES (" + values + ")\n";
+        sql += ")\n";
+        // CTE that tries to update the table with new values (PSQL extension)
+        sql += ", upsert AS (\n";
+        sql += "    UPDATE " + table + " u SET " + set_values + " FROM nv\n";
+        sql += "    WHERE " + pkey_matches + " RETURNING 1\n";
+        sql += ")\n";
+        // if nothing got updated, insert new entry
+        sql += "INSERT INTO " + table + " (" + fields + ")\n";
+        sql += "SELECT " + fields + " FROM nv\n";
+        sql += "WHERE NOT EXISTS (SELECT 1 FROM upsert)";
+        return sql;
+    }
+
+    public static void setInsertParams(NamedPreparedStatement statement,
+            Long tweetid, String brand, int rating) throws SQLException {
+        statement.setLong("tweetid", tweetid);
+        statement.setInt("rating", rating);
+        statement.setString("brand", brand);
+        
+    }
+
+}
diff --git a/src/main/Analyzor.java b/src/main/Analyzor.java
index 831231a..8f11186 100644
--- a/src/main/Analyzor.java
+++ b/src/main/Analyzor.java
@@ -5,6 +5,8 @@
  */

 package main;

 

+import database.NamedPreparedStatement;

+import database.QueryUtils;

 import java.io.File;

 import java.io.FileNotFoundException;

 import java.sql.Connection;

@@ -28,6 +30,7 @@ public class Analyzor {
     

     //the resultset of the query or the import

     ResultSet data;

+    Connection connection;

     

     //reads the lexicons

     void readLexicon() throws FileNotFoundException{

@@ -61,12 +64,15 @@ public class Analyzor {
         

         PreparedStatement statement;

         

-        try (Connection connection = Main.cb.create()){

+        try {

+            connection = Main.cb.create();

             statement = connection.prepareStatement(query);

             data = statement.executeQuery();

+            

+            

         }

         catch(SQLException ex){

-            System.err.println("could not make a connection with the database");

+            System.err.println("could not make a connection with the database"+ex);

         }

     }

 

@@ -74,10 +80,7 @@ public class Analyzor {
     //this is just a base version

     void sentimentAnalysis() {

         

-        if(data == null){

-            System.err.print("no dataset available: query first");

-            return;

-        }

+        

         

         try{

             readLexicon();

@@ -113,9 +116,12 @@ public class Analyzor {
                         positiverate += bimap.get(pair);

                     }

                 }

-                

-                //prints the rate

-                System.out.println(text + ": " + (int) (positiverate * 10));

+                NamedPreparedStatement m_insertRating;

+                m_insertRating = new NamedPreparedStatement(connection, QueryUtils.insertRating);

+                QueryUtils.setInsertParams(m_insertRating, data.getLong("tweetid"),data.getString("brand"), (int)(positiverate * 10));

+                m_insertRating.executeUpdate();

+                //don't print the rate

+                //System.out.println(text + ": " + (int) (positiverate * 10));

             }

         } catch (SQLException ex) {

             System.err.println("text not found");

diff --git a/src/main/FarmShell.java b/src/main/FarmShell.java
index c41ad6b..daab973 100644
--- a/src/main/FarmShell.java
+++ b/src/main/FarmShell.java
@@ -67,8 +67,7 @@ public class FarmShell {
      * otherwise.

      */

     public boolean execute(String[] args) {

-        //make a new Analyzor

-        analyzor = new Analyzor();

+        

 

         try {

             Command command = Command.fromString(args[0]);

@@ -140,6 +139,8 @@ public class FarmShell {
         }

         switch (command) {

             case query:

+                //make a new Analyzor

+                analyzor = new Analyzor();

                 analyzor.Query(params[0]);

                 break;

             case filterbots: