update

diff --git a/src/DbMigration.java b/src/DbMigration.java
index 2675c3fb6c051c7bc43206d7420cf92ad9c01c8c..6265545dda375479db601488dafe4ed0d45eb085 100644 (file)
--- a/src/DbMigration.java
+++ b/src/DbMigration.java
@@ -5,9 +5,10 @@ import java.util.*;
 
 class DbMigration {
     // constants {{{
-    static final boolean CONFIG_PRINT_DB = false;
+    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 =
             """
@@ -37,12 +38,13 @@ class DbMigration {
     // }}}
 
     // csv {{{
-    public static String[][] LoadCSV(String filename) throws IOException {
+    public static ArrayList<String[]> LoadCSV(String filename) throws IOException {
         var path = Paths.get(filename);
-        return Files.lines(path)
+        var list = Files.lines(path)
                 .skip(1)
                 .map(line -> line.split(CSV_SEPARATOR))
-                .toArray(String[][]::new);
+                .toList();
+        return new ArrayList<String[]>(list);
     }
 
     // }}}
@@ -63,7 +65,7 @@ class DbMigration {
         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);
@@ -77,7 +79,6 @@ class DbMigration {
 
     // }}}
 
-    // main {{{
     public static void main(String[] args) {
         // filenames from command line args {{{
         if (args.length != 2) {
@@ -89,8 +90,8 @@ class DbMigration {
         // }}}
 
         // read csvs {{{
-        String[][] csvUsers;
-        String[][] csvRoles;
+        ArrayList<String[]> csvUsers;
+        ArrayList<String[]> csvRoles;
         try {
             csvUsers = LoadCSV(filenameUsers);
             csvRoles = LoadCSV(filenameRoles);
@@ -99,8 +100,17 @@ class DbMigration {
             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
@@ -141,9 +151,10 @@ class DbMigration {
 
         // open db connection for updating {{{
         try (var conn = DriverManager.getConnection(DB_URL);
-                var stmt = conn.prepareStatement(SQL_UPSERT_USER); ) {
+             var stmt = conn.prepareStatement(SQL_UPSERT_USER); ) {
 
             // batching
+            int batchIndex = 0;
             conn.setAutoCommit(false);
 
             // update db from csv
@@ -154,12 +165,18 @@ class DbMigration {
 
                 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;
@@ -168,5 +185,4 @@ class DbMigration {
 
         System.out.println("Updated successfully");
     }
-    // }}}
 }