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
|
import com.google.gson.Gson;
import com.pinapelz.Holodex;
import com.pinapelz.HolodexException;
import com.pinapelz.datatypes.Channel;
import com.pinapelz.query.ChannelQueryBuilder;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.sql.*;
public class Main
{
// Organization name -> formatted name
private static Map<String, String> organizations = Map.of(
"Hololive", "Hololive",
"Nijisanji", "Nijisanji",
"VShojo", "VShojo",
"idol Corp", "Idol Corp",
"EIEN Project", "Eien Project",
"Phase Connect", "Phase Connect"
);
public static HashMap<String, String> readSettings() throws IOException {
String settings = new String(Files.readAllBytes(Paths.get("secrets.json")));
return new Gson().fromJson(settings, HashMap.class);
}
public static Connection createDBConnection(HashMap<String, String> settings) throws IOException, SQLException {
String url = "jdbc:postgresql://"+settings.get("pg_host")+"/"+settings.get("pg_db");
Properties props = new Properties();
props.setProperty("user", settings.get("pg_user"));
props.setProperty("password", settings.get("pg_password"));
Connection conn = DriverManager.getConnection(url, props);
return conn;
}
public static void main( String[] args ) throws IOException, HolodexException, SQLException {
HashMap<String, String> settings = readSettings();
Connection conn = createDBConnection(settings);
System.out.println("Sucessfully connected to the database");
Holodex holodex = new Holodex(settings.get("holodex_key"));
Statement stmt = conn.createStatement();
stmt.executeUpdate("DELETE FROM " + settings.get("pg_table"));
stmt.close();
System.out.println("Deleted all rows from the channels table");
for (String org : organizations.keySet()) {
ChannelQueryBuilder query = new ChannelQueryBuilder().setOrg(organizations.get(org)).setLimit(200);
System.out.println("Getting channels for " + org);
List<Channel> channels = holodex.getChannels(new ChannelQueryBuilder().setOrg(org));
System.out.println("Got " + channels.size() + " channels");
for (Channel channel : channels) {
if(channel.english_name == null || channel.photo == null || channel.english_name.equals("")) {
System.out.println("Skipping " + channel.name);
continue;
}
if(channel.inactive){
System.out.println("Skipping inactive channel " + channel.english_name);
continue;
}
String insert = "INSERT INTO " + settings.get("pg_table") +
" (name, affiliation, image_url) " +
"VALUES ('" + channel.english_name + "', '" + org+ "', '" + channel.photo + "')";
System.out.println(insert);
try{
stmt = conn.createStatement();
stmt.executeUpdate(insert);
stmt.close();
} catch (SQLException e) {
System.out.println("Failed to insert " + channel.english_name);
System.out.println(e.getMessage());
}
}
}
}
}
|
