update

[l21s-case-study] / src / DbMigration.java

diff --git a/src/DbMigration.java b/src/DbMigration.java
index bcf22d901eb6688f5110c7778a92df8785de205f..6265545dda375479db601488dafe4ed0d45eb085 100644 (file)
--- a/src/DbMigration.java
+++ b/src/DbMigration.java
@@ -4,45 +4,52 @@ import java.sql.*;
 import java.util.*;
 
 class DbMigration {
 import java.util.*;
 
 class DbMigration {

-    // constants
-    static final boolean CONFIG_PRINT_DB = false;
+    // constants {{{
+    static final boolean CONFIG_PRINT_DB = true;

     static final String CSV_SEPARATOR = ",";
     static final String CSV_SEPARATOR = ",";

-    static final String DB_URL = "jdbc:postgresql:users";
+    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 =
             """
     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
-              ;
+            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
+    // }}}
+
+    // 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;
         }
     }
 
     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 {
+    // csv {{{
+    public static ArrayList<String[]> LoadCSV(String filename) throws IOException {

         var path = Paths.get(filename);
         var path = Paths.get(filename);

-        return Files.lines(path)
+        var list = Files.lines(path)

                 .skip(1)
                 .map(line -> line.split(CSV_SEPARATOR))
                 .skip(1)
                 .map(line -> line.split(CSV_SEPARATOR))

-                .toArray(String[][]::new);
+                .toList();
+        return new ArrayList<String[]>(list);

     }
 
     }
 

-    // db
+    // }}}
+
+    // db {{{

     public static void insertUser(PreparedStatement stmt, User user) throws SQLException {
         stmt.setInt(1, user.id);
         stmt.setString(2, user.mail);
     public static void insertUser(PreparedStatement stmt, User user) throws SQLException {
         stmt.setInt(1, user.id);
         stmt.setString(2, user.mail);


@@ -58,7 +65,7 @@ class DbMigration {
         var users = new ArrayList<User>();
 
         try (var st = conn.createStatement();
         var users = new ArrayList<User>();
 
         try (var st = conn.createStatement();

-                var rs = st.executeQuery(SQL_GET_USERS); ) {
+             var rs = st.executeQuery(SQL_GET_USERS); ) {

             while (rs.next()) {
                 var userId = rs.getInt(1);
                 var userMail = rs.getString(2);
             while (rs.next()) {
                 var userId = rs.getInt(1);
                 var userMail = rs.getString(2);


@@ -70,19 +77,21 @@ class DbMigration {
         return users.toArray(User[]::new);
     }
 
         return users.toArray(User[]::new);
     }
 

-    // main
+    // }}}
+

     public static void main(String[] args) {
     public static void main(String[] args) {

-        // filenames from command line 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];
         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;
+        // read csvs {{{
+        ArrayList<String[]> csvUsers;
+        ArrayList<String[]> csvRoles;

         try {
             csvUsers = LoadCSV(filenameUsers);
             csvRoles = LoadCSV(filenameRoles);
         try {
             csvUsers = LoadCSV(filenameUsers);
             csvRoles = LoadCSV(filenameRoles);


@@ -90,8 +99,18 @@ class DbMigration {
             System.out.printf("Expection occured while trying to load CSV: %s\n", e);
             return;
         }
             System.out.printf("Expection occured while trying to load CSV: %s\n", e);
             return;
         }

-
-        // process roles
+        // }}}
+        
+        // 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 roles = new HashMap<Integer, Role>();
         for (var row : csvRoles) {
             // get id and single role


@@ -111,13 +130,14 @@ class DbMigration {
                         return role;
                     });
         }
                         return role;
                     });
         }

+        // }}}

 
 

-        // OPTIONAL: open db connection for reading
+        // OPTIONAL: open db connection for reading {{{

         if (CONFIG_PRINT_DB) {
             try (var conn = DriverManager.getConnection(DB_URL)) {
 
                 var users = getUsers(conn);
         if (CONFIG_PRINT_DB) {
             try (var conn = DriverManager.getConnection(DB_URL)) {
 
                 var users = getUsers(conn);

-                System.out.println("DB before update:");
+                System.out.println("Before update:");

                 for (var user : users) {
                     System.out.println(user);
                 }
                 for (var user : users) {
                     System.out.println(user);
                 }


@@ -127,11 +147,14 @@ class DbMigration {
                 return;
             }
         }
                 return;
             }
         }

+        // }}}

 
 

-        // open db connection for updating
+        // open db connection for updating {{{

         try (var conn = DriverManager.getConnection(DB_URL);
         try (var conn = DriverManager.getConnection(DB_URL);

-                var stmt = conn.prepareStatement(SQL_UPSERT_USER); ) {
+             var stmt = conn.prepareStatement(SQL_UPSERT_USER); ) {
+

             // batching
             // batching

+            int batchIndex = 0;

             conn.setAutoCommit(false);
 
             // update db from csv
             conn.setAutoCommit(false);
 
             // update db from csv


@@ -142,15 +165,23 @@ class DbMigration {
 
                 User user = new User(userId, userMail, userRoles);
                 insertUser(stmt, user);
 
                 User user = new User(userId, userMail, userRoles);
                 insertUser(stmt, user);

+
+                // batching
+                batchIndex += 1;
+                if (batchIndex > DB_BATCH_SIZE) {
+                    stmt.executeBatch();
+                    batchIndex = 0;
+                }

             }
 
             }
 

-            // commit batch
+            // commit all outstanding changes

             stmt.executeBatch();
             conn.commit();
         } catch (SQLException e) {
             System.out.printf("Expection occured while accessing database: %s\n", e);
             return;
         }
             stmt.executeBatch();
             conn.commit();
         } catch (SQLException e) {
             System.out.printf("Expection occured while accessing database: %s\n", e);
             return;
         }

+        // }}}

 
         System.out.println("Updated successfully");
     }
 
         System.out.println("Updated successfully");
     }