From b8a155727f6ac18b0afed3e1ffb0177b924b4581 Mon Sep 17 00:00:00 2001 From: patrick-scho Date: Sun, 23 Nov 2025 23:18:09 +0100 Subject: [PATCH] Initial commit --- roles.csv | 7 ++ src/DbMigration.java | 157 +++++++++++++++++++++++++++++++++++++++++++ start_db.sh | 2 + users.csv | 4 ++ 4 files changed, 170 insertions(+) create mode 100644 roles.csv create mode 100644 src/DbMigration.java create mode 100755 start_db.sh create mode 100644 users.csv diff --git a/roles.csv b/roles.csv new file mode 100644 index 0000000..f216d01 --- /dev/null +++ b/roles.csv @@ -0,0 +1,7 @@ +user_id,role +1000,read +1000,write +1000,delete +2000,read +3000,read +3000,delete \ No newline at end of file diff --git a/src/DbMigration.java b/src/DbMigration.java new file mode 100644 index 0000000..bcf22d9 --- /dev/null +++ b/src/DbMigration.java @@ -0,0 +1,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(); + + 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 \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(); + 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"); + } +} diff --git a/start_db.sh b/start_db.sh new file mode 100755 index 0000000..56b9b2d --- /dev/null +++ b/start_db.sh @@ -0,0 +1,2 @@ +#!/usr/bin/env bash +postgres -D db diff --git a/users.csv b/users.csv new file mode 100644 index 0000000..a0a828b --- /dev/null +++ b/users.csv @@ -0,0 +1,4 @@ +user_id,mail +1000,schmidt@deutsche-bank.deee +2000,mueller@deutsche-bank.de +3000,meyer@sparkasse.de -- 2.50.1