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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
import java.io.*;
import java.nio.file.*;
import java.sql.*;
import java.util.*;
class DbMigration {
// constants {{{
static final boolean CONFIG_PRINT_DB = true;
static final String CSV_SEPARATOR = ",";
static final String DB_URL = "jdbc:postgresql:migration";
static final int DB_BATCH_SIZE = 10;
static final String SQL_GET_USERS = "SELECT * FROM users;";
static final String SQL_UPSERT_USER =
"""
INSERT INTO users VALUES (?, ?, ?, ?, ?)
ON CONFLICT (USER_ID)
DO UPDATE SET
MAIL = EXCLUDED.MAIL,
CAN_READ = EXCLUDED.CAN_READ,
CAN_WRITE = EXCLUDED.CAN_WRITE,
CAN_DELETE = EXCLUDED.CAN_DELETE
;
""";
// }}}
// user {{{
public record Role(boolean read, boolean write, boolean delete) {
static final Role Default = new Role(false, false, false);
}
public record User(int id, String mail, Role role) {
public String toString() {
return this.id + " " + this.mail + " " + this.role;
}
}
// }}}
// csv {{{
public static ArrayList<String[]> LoadCSV(String filename) throws IOException {
var path = Paths.get(filename);
var list = Files.lines(path)
.skip(1)
.map(line -> line.split(CSV_SEPARATOR))
.toList();
return new ArrayList<String[]>(list);
}
// }}}
// 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);
}
// }}}
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 {{{
ArrayList<String[]> csvUsers;
ArrayList<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;
}
// }}}
// Sparkassen Nutzer aussortieren {{{
csvUsers.removeIf(row -> row[1].endsWith("@sparkasse.de"));
// }}}
// process roles {{{
// - read roles.csv line by line
// - get user_id and role from each line
// - make sure user_id is in the HashMap
// - if not create default value with no roles
// - update the role specified in that line
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("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
int batchIndex = 0;
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);
// batching
batchIndex += 1;
if (batchIndex > DB_BATCH_SIZE) {
stmt.executeBatch();
batchIndex = 0;
}
}
// commit all outstanding changes
stmt.executeBatch();
conn.commit();
} catch (SQLException e) {
System.out.printf("Expection occured while accessing database: %s\n", e);
return;
}
// }}}
System.out.println("Updated successfully");
}
}
|
