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
|
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:migration";
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 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("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");
}
// }}}
}
|
