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
157
|
import java.io.*;
import java.nio.file.*;
import java.sql.*;
import java.util.*;
class DbMigration {
// constants
static final boolean CONFIG_PRINT_DB = false;
static final String CSV_SEPARATOR = ",";
static final String DB_URL = "jdbc:postgresql:users";
static final String SQL_GET_USERS = "SELECT * FROM users;";
static final String SQL_UPSERT_USER =
"""
INSERT INTO users VALUES (?, ?, ?, ?, ?)
ON CONFLICT (ID)
DO UPDATE SET
Mail = EXCLUDED.Mail,
CanRead = EXCLUDED.CanRead,
CanWrite = EXCLUDED.CanWrite,
CanDelete = EXCLUDED.CanDelete
;
""";
// user
public record User(int id, String mail, Role role) {
public String toString() {
return this.id + " " + this.mail + " " + this.role;
}
}
// role
public record Role(boolean read, boolean write, boolean delete) {
static final Role Default = new Role(false, false, false);
}
// csv
public static String[][] LoadCSV(String filename) throws IOException {
var path = Paths.get(filename);
return Files.lines(path)
.skip(1)
.map(line -> line.split(CSV_SEPARATOR))
.toArray(String[][]::new);
}
// db
public static void insertUser(PreparedStatement stmt, User user) throws SQLException {
stmt.setInt(1, user.id);
stmt.setString(2, user.mail);
stmt.setBoolean(3, user.role.read);
stmt.setBoolean(4, user.role.write);
stmt.setBoolean(5, user.role.delete);
// batching
stmt.addBatch();
}
static User[] getUsers(Connection conn) throws SQLException {
var users = new ArrayList<User>();
try (var st = conn.createStatement();
var rs = st.executeQuery(SQL_GET_USERS); ) {
while (rs.next()) {
var userId = rs.getInt(1);
var userMail = rs.getString(2);
var userRole = new Role(rs.getBoolean(3), rs.getBoolean(4), rs.getBoolean(5));
users.add(new User(userId, userMail, userRole));
}
}
return users.toArray(User[]::new);
}
// main
public static void main(String[] args) {
// filenames from command line args
if (args.length != 2) {
System.out.printf("Usage: java src/DbMigrations.java <users.csv> <roles.csv>\n");
return;
}
String filenameUsers = args[0];
String filenameRoles = args[1];
// read csvs
String[][] csvUsers;
String[][] csvRoles;
try {
csvUsers = LoadCSV(filenameUsers);
csvRoles = LoadCSV(filenameRoles);
} catch (IOException e) {
System.out.printf("Expection occured while trying to load CSV: %s\n", e);
return;
}
// process roles
var roles = new HashMap<Integer, Role>();
for (var row : csvRoles) {
// get id and single role
var userId = Integer.parseInt(row[0], 10);
var roleStr = row[1];
// make sure userId is present
roles.putIfAbsent(userId, Role.Default);
// update value by setting one role to true and copying the rest
roles.computeIfPresent(
userId,
(id, role) -> {
if (roleStr.equals("read")) return new Role(true, role.write, role.delete);
if (roleStr.equals("write")) return new Role(role.read, true, role.delete);
if (roleStr.equals("delete")) return new Role(role.read, role.write, true);
return role;
});
}
// OPTIONAL: open db connection for reading
if (CONFIG_PRINT_DB) {
try (var conn = DriverManager.getConnection(DB_URL)) {
var users = getUsers(conn);
System.out.println("DB before update:");
for (var user : users) {
System.out.println(user);
}
} catch (SQLException e) {
System.out.printf("Expection occured while accessing database: %s\n", e);
return;
}
}
// open db connection for updating
try (var conn = DriverManager.getConnection(DB_URL);
var stmt = conn.prepareStatement(SQL_UPSERT_USER); ) {
// batching
conn.setAutoCommit(false);
// update db from csv
for (var row : csvUsers) {
var userId = Integer.parseInt(row[0], 10);
var userMail = row[1];
var userRoles = roles.get(userId);
User user = new User(userId, userMail, userRoles);
insertUser(stmt, user);
}
// commit batch
stmt.executeBatch();
conn.commit();
} catch (SQLException e) {
System.out.printf("Expection occured while accessing database: %s\n", e);
return;
}
System.out.println("Updated successfully");
}
}
|
