summaryrefslogtreecommitdiff
path: root/src/database/QueryUtils.java
blob: a37b806a3abb940ebe893a1dc2fe84cdcd02b589 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
package database;

import data.Tweet;
import data.User;
import java.sql.SQLException;
import java.util.Locale;

/**
 * Utilities to create queries.
 *
 * @author Maurice Laveaux
 */
public class QueryUtils {

    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 tweetStatement,
            NamedPreparedStatement profileStatement,
            Tweet tweet, String tweetText) throws SQLException {
        tweetStatement.setLong("tweetid", tweet.id);
        tweetStatement.setTimestamp("createdat", tweet.created_at);
        tweetStatement.setInt("favcount", tweet.favorite_count);
        tweetStatement.setLong("retweetcount", tweet.retweet_count);
        tweetStatement.setString("text", tweetText);
        if (tweet.coordinates != null) {
            float[] coords = tweet.coordinates.coordinates;
            String coords_str = String.format(Locale.ENGLISH, "%f,%f", coords[0], coords[1]);
            tweetStatement.setString("coordinates", coords_str);
        } else {
            tweetStatement.setString("coordinates", null);
        }
        tweetStatement.setString("language", tweet.lang);
        if (tweet.retweeted_status != null) {
            tweetStatement.setLong("retweetid", tweet.retweeted_status.id);
        } else {
            tweetStatement.setLong("retweetid", null);
        }
        tweetStatement.setLong("replyid", tweet.in_reply_to_user_id);
        // TODO: place is not a string...
        if (tweet.place != null) {
            tweetStatement.setString("place", tweet.place.full_name + " " + tweet.place.country);
        } else {
            tweetStatement.setString("place", null);
        }

        User twuser = tweet.user;
        tweetStatement.setLong("userid", twuser.id);
        profileStatement.setLong("userid", twuser.id);
        profileStatement.setString("displayname", twuser.name);
        profileStatement.setString("timezone", twuser.time_zone);
        profileStatement.setInt("tweetcount", twuser.statuses_count);
        profileStatement.setInt("followercount", twuser.followers_count);
        profileStatement.setInt("followedcount", twuser.friends_count);
        String userLocation = getUserLocation(twuser);
        profileStatement.setString("location", userLocation);
        profileStatement.setString("tweetname", twuser.screen_name);
        profileStatement.setTimestamp("createdat", twuser.created_at);
        profileStatement.setString("language", twuser.lang);
        profileStatement.setString("description", twuser.description);
        profileStatement.setBoolean("verified", twuser.verified);
    }

    public static void setInsertBrandParams(NamedPreparedStatement brandStmt,
            long id, String brand) throws SQLException {
        brandStmt.setLong("tweetid", id);
        brandStmt.setString("brand", brand);
        // TODO: rating (positive)
    }

    private static String getUserLocation(User user) {
        String location = user.location;
        if (location != null && location.contains("\0")) {
            System.err.println("Warning: \\0 location found for user " + user);
            location = location.replace("\0", "");
        }
        return location;
    }
}