Initial commit

diff --git a/roles.csv b/roles.csv
new file mode 100644 (file)
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 (file)
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<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");
+    }
+}

diff --git a/start_db.sh b/start_db.sh
new file mode 100755 (executable)
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 (file)
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