Changeset 32745

Timestamp:

2019-02-05T23:03:16+13:00 (5 years ago)

Author:

ak19

Message:

More Western Wilson stuff. 1. Major changes to fix handling of utf8 stuff in db so uniqueness actually works: so finding (selecting) exact matches works and insert unique violations don't happen from code. Inserting is now made lowercase since only macrons matter and case doesn't. 2. The SQL db's MarkedWords table needs to specify the uniqueness of its utf8 marked_word column differently for the utf8-ness to work.

Location:

other-projects/the-macronizer/trunk/src

Files:

• java/util/MacroniserLogFileData.java (modified) (1 diff)
• java/util/MacroniserLogFileProcessor.java (modified) (9 diffs)
• java/util/MySQLAccess.java (modified) (8 diffs)
• sql-scripts/MySQL_Code.sql (modified) (1 diff)

other-projects/the-macronizer/trunk/src/java/util/MacroniserLogFileData.java

@@ -35 +35 @@
         sb.append(time.format(DateTimeFormatter.ofPattern("HH:mm:ss")));
         sb.append("] DirectInput.doPost() \n Input:");
-        sb.append(inputText);
+        if(inputText != null) sb.append(inputText);
         sb.append("Output:");
-        sb.append(outputText);
+        if(outputText != null) sb.append(outputText);
 
         return sb.toString();

other-projects/the-macronizer/trunk/src/java/util/MacroniserLogFileProcessor.java

@@ -15 +15 @@
 
 public class MacroniserLogFileProcessor {
+    static boolean debug = false;
     /** ARGUMENTS:
      *              /home/wjkw1/RESEARCH_2018-19/bash_test/loggingtest.log
@@ -26 +27 @@
 
     public static void main(String[] args) {
-        //TODO: re enable this method and add text to command line interface
-        //checkArgs(args.length);
+        checkArgs(args.length);
         String filename = args[0];
         //extracts using yesterdays date
         extractFromLogFile(filename);
 
-        //TODO: REMOVE
-        int count = 0;
-        for (MacroniserLogFileData entry: extractedEntries
-             ) {
-            printMessage((++count)+": "+entry.toString());
-        }
+
+        if(debug) {
+            int count = 0;
+            for (MacroniserLogFileData entry : extractedEntries) {
+                printMessage((++count) + ": " + entry.toString());
+            }
+        }
+
         if(exportToDB()) {
-            // TODO: move the log file to processed folder
-
+            System.out.println("Success. Move the log file " + filename + " to processed folder");
+            System.exit(0);
         } else {
-            // TODO: move the log file to reprocess folder
+            System.err.println("FAILED. Move the log file " + filename + " to reprocess folder");
+            System.exit(-1);
         }
     }
@@ -57 +60 @@
 
             //loop through all entries
-            for (MacroniserLogFileData entry : extractedEntries
-                    ) {
+            for (MacroniserLogFileData entry : extractedEntries) {
                 //get the marked words from first entry
                 ArrayList<String> markedWords = getMarkedWordsFromOutput(entry.getOutputText());
-                if (markedWords != null) {
+                // all these markedWords share the same date and time
+                LocalDate date = entry.getDate();
+                LocalTime time = entry.getTime();
+
+                if (markedWords == null) { // no words, enter NULL into db
+                    if(debug) {
+                        printMessage("word=NULL");
+                        printMessage(date.toString());
+                        printMessage(time.format(DateTimeFormatter.ofPattern("HH:mm:ss")));
+                        printMessage(""); // newline
+                    }
+
+                    MySQLAccess.Tuple tuple = new MySQLAccess.Tuple(null,date,time);
+                    // b. add to DB
+                    sqlAccess.addNewEntry(tuple);
+                } else { // process all the words
                     for (String word : markedWords) {
-                        // create the tuple
-                        MySQLAccess.Tuple tuple = new MySQLAccess.Tuple(word,entry.getDate(),entry.getTime());
-                        //TODO: insert all into database
-                        //insert all into database
-                        printMessage(word);
-                        printMessage(entry.getDate().toString());
-                        printMessage(entry.getTime().format(DateTimeFormatter.ofPattern("HH:mm:ss")));
-                        printMessage("");
-
+                        if(debug) {
+                            printMessage(word);
+                            printMessage(date.toString());
+                            printMessage(time.format(DateTimeFormatter.ofPattern("HH:mm:ss")));
+                            printMessage(""); // newline
+                        }
+
+                        //insert all into database:
+                        // a. create the tuple: always entering into db as lowercase so we don't consider tō different from Tō
+                        // whether when inserting into DB or searching for the term
+                        MySQLAccess.Tuple tuple = new MySQLAccess.Tuple(word.toLowerCase(),date,time);
+                        // b. add to DB
                         sqlAccess.addNewEntry(tuple);
                     }
+
                 }
             }
             success = true;
 
-        }catch (Exception e) {
-            e.printStackTrace();
+        } catch (Exception e) {
+            e.printStackTrace(); // goes to std.err, see https://stackoverflow.com/questions/12095378/difference-between-e-printstacktrace-and-system-out-printlne
             success = false;
 
@@ -91 +112 @@
     //returns an array list of all marked words, null if there are none
     private static ArrayList<String> getMarkedWordsFromOutput(String outputText) {
+        if(outputText == null) { return null; }
+
         final Pattern TAG_REGEXP = Pattern.compile("<mark>(.+?)</mark>", Pattern.DOTALL);
         final Matcher matcher = TAG_REGEXP.matcher(outputText);
@@ -104 +127 @@
     //reads the log file and creates a list of data
     private static void extractFromLogFile(String filename) {
-        //TODO: change the date that is used
-//        LocalDate yesterday = LocalDate.now().minusDays(1L);
-        LocalDate specifiedDate = LocalDate.of(2018,11,06);
 
         BufferedReader br = null;
@@ -133 +153 @@
                         //perform operations if not null, else error
                         if(entry != null) {
-                            ////check if entry date is after specified date
-                            if(entry.getDate().isAfter(specifiedDate)){
-                                //stop processing
-                                break;
-                            } else if (entry.getDate().isEqual(specifiedDate)){
-                                extractedEntries.add(entry);
-                            }
+                            extractedEntries.add(entry);
                             //remove old content and keep new tag
                             extractedSB.setLength(0);
                             extractedSB.append(line);
                         } else{
-                            printErrorMsg("Parsing of entry in log file found an error, continuing on next lines...");
+                            //printErrorMsg("Parsing of entry in log file found an error, continuing on next lines...");
                             extractedSB.setLength(0);
                             extractedSB.append(line);
@@ -179 +193 @@
         //Get the input output portion of the string
         String input_output = "";
-        if(extractedString.charAt(0)=='I'){
+        if (extractedString.charAt(0) == 'I') {
 
             //Get the date and time of entry
@@ -191 +205 @@
             input_output = extractedString.replaceAll(directInputRegexp, "").trim();
 
-            int INDEX_STARTOF_INPUT = 6, INDEX_ENDOF_INPUT = getEndofInputIndex(input_output),
-                    INDEX_STARTOF_OUTPUT = INDEX_ENDOF_INPUT + 8, INDEX_ENDOF_OUTPUT = input_output.length();
-            entryInput = input_output.substring(INDEX_STARTOF_INPUT, INDEX_ENDOF_INPUT);
-            entryOutput = input_output.substring(INDEX_STARTOF_OUTPUT, INDEX_ENDOF_OUTPUT);
-
+            int INDEX_STARTOF_INPUT = 6;
+            int INDEX_ENDOF_INPUT = getEndofInputIndex(input_output);
+            int INDEX_STARTOF_OUTPUT = INDEX_ENDOF_INPUT + 8;
+            int INDEX_ENDOF_OUTPUT = input_output.length();
+            if (INDEX_ENDOF_INPUT == -1) {
+                entryInput = null;
+                entryOutput = null;
+
+            } else {
+                entryInput = input_output.substring(INDEX_STARTOF_INPUT, INDEX_ENDOF_INPUT);
+                entryOutput = input_output.substring(INDEX_STARTOF_OUTPUT, INDEX_ENDOF_OUTPUT);
+            }
             entry = new MacroniserLogFileData(entryDate, entryTime, entryInput, entryOutput);
+
             return entry;
 
-        } else if (extractedString.charAt(0)=='E') {
+        } else if (extractedString.charAt(0) == 'E') {
             input_output = extractedString.replaceAll(fileUploadRegexp, "");
             return null;
@@ -225 +247 @@
             return indexes.get(middle_index);
         } else {
-            printErrorMsg("No output tag could be found, error in log file.");
+            printMessage("Warning: No output tag could be found. Probably NULL input.");
             return -1;
         }

other-projects/the-macronizer/trunk/src/java/util/MySQLAccess.java

@@ -56 +56 @@
 
     private final String DB_NAME = "Macroniser";
-    // TODO: from Properties file
+    // obtained from Properties file:
     private String USERNAME; //= "root"; by default
     private String PASSWORD; //= "pinky";
@@ -91 +91 @@
 
             }
-            System.err.println("*** Found user: |" + USERNAME + "|");
-            System.err.println("*** Found pwd: " + PASSWORD);
+            //System.out.println("*** Found user: |" + USERNAME + "|");
+            //System.out.println("*** Found pwd: " + PASSWORD);
 
             // This will load the MySQL driver, each DB has its own driver Class.forName("com.mysql.jdbc.Driver");
@@ -99 +99 @@
             statement = connect.createStatement();
 
-            // TODO: can we use preparedStatement here instead of statement?
-            int result = statement.executeUpdate("set names utf8mb4");
-            System.err.println("Was set utf8 a success? " + result); // should return 0 for SQL stmts that return nothing
+            int result = statement.executeUpdate("set names utf8mb4"); // should return 0 for SQL stmts that return nothing
+
             success = true;
         } catch (SQLException e) {
@@ -192 +191 @@
             insertOccurrence(word_id, time_id, date_id);
 
-            System.err.println("The ID's are:(word, date, time) (" + word_id + "," + date_id + "," + time_id + ")");
+            if(MacroniserLogFileProcessor.debug) {
+                System.out.println("The IDs are:(word, date, time) (" + word_id + "," + date_id + "," + time_id + ")");
+            }
 
             connect.commit();
@@ -234 +235 @@
     //gets the specified marked word's id
     private int getMarkedWordID(String marked_word) throws SQLException {
+        // in cases where the user didn't enter any input str, marked_word will be null. Still of interest, store to db as string "NULL"
+        if(marked_word == null) {
+            marked_word = "NULL";
+        }
+
         // Warning: "select * from table WHERE str_field LIKE ?" does not work when we want exact matches (or matches featuring macrons)
         // Use "WHERE BINARY str_filed = ?" instead
@@ -253 +259 @@
     private int insertMarkedWord(String word) throws SQLException {
 
+        if(word == null) {
+            word = "NULL";
+        }
+
         String query = "INSERT INTO MarkedWords (marked_word) VALUES (?)";
         preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
         preparedStatement.setString(1, word);
+
 
         int word_id = -1;
@@ -293 +304 @@
         while (resultSet.next()) {
             date_id = resultSet.getInt("date_id");
-            System.err.println(date_id + " - " + date.toString());
+            if(MacroniserLogFileProcessor.debug) System.out.println(date_id + " - " + date.toString());
         }
         return date_id;
@@ -382 +393 @@
 
     public static void main(String[] args) {
-        System.err.println("Hello pinky!");
         MySQLAccess mysql = new MySQLAccess();
 

other-projects/the-macronizer/trunk/src/sql-scripts/MySQL_Code.sql

@@ -6 +6 @@
 
 
+/*
+Unique constraint on marked_word field needs to be specified specially to deal with utf8
+e.g. to distinguish between āno and anō, rather than treating them both the same breaking uniqueness.
+https://stackoverflow.com/questions/25318479/unique-constraint-violation-with-utf-8-values
+*/
 CREATE TABLE IF NOT EXISTS MarkedWords (
     word_id INT AUTO_INCREMENT,
-    marked_word varchar (255) UNIQUE,
+    marked_word varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE,
     PRIMARY KEY (word_id)
     );