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
|
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);
}
public static void setInsertBrandParams(NamedPreparedStatement brandStmt,
long id, String brand) throws SQLException {
brandStmt.setLong("tweetid", id);
brandStmt.setString("brand", brand);
// TODO: rating (positive)
}
}
|
