Revize 157c07d1

#6 Allow for String AP parameters

- sql scripts refactor for string parametrization
- added method for inserting substrings into sql queries
- substrings extracted to the AP classes
- allow reparametrization on configuration page

src/main/java/cz/zcu/fav/kiv/antipatterndetectionapp/Constants.java

@@ -2 +2 @@
 
 public class Constants {
     public static final String ANTI_PATTERN_CATALOGUE_URL = "https://github.com/ReliSA/Software-process-antipatterns-catalogue/blob/master/catalogue/";
+    public static final String SUBSTRING_DELIMITER = "||";
 }

src/main/java/cz/zcu/fav/kiv/antipatterndetectionapp/detecting/DatabaseConnection.java

@@ -6 +6 @@
 import cz.zcu.fav.kiv.antipatterndetectionapp.utils.Utils;
 
 import java.sql.*;
-import java.util.*;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
 
+/**
+ * A class that takes care of database connections and running queries.
+ */
 public class DatabaseConnection {
 
     private Connection databaseConnection;
 
+    /**
+     * Constructor that takes application properties from configuration file name application.properties
+     * and creating new database connection with given parameters.
+     */
     public DatabaseConnection() {
         ApplicationProperties applicationProperties = ((ApplicationProperties) SpringApplicationContext.getContext()
                 .getBean("applicationProperties"));
@@ -21 +30 @@
         this.databaseConnection = createConnection(connectionUrl, dataSourceUsername, dataSourcePassword);
     }
 
+    /**
+     * Method for creating database connection with given parameters.
+     *
+     * @param connectionUrl      database url to connect
+     * @param dataSourceUsername database username
+     * @param dataSourcePassword database user password
+     * @return new database connection
+     */
     private Connection createConnection(String connectionUrl, String dataSourceUsername, String dataSourcePassword) {
         Connection conn = null;
         try {
@@ -32 +49 @@
         return conn;
     }
 
-    public void closeConnection() {
+    /**
+     * Method for closing database connection. It is necessary to close the database
+     * connection when the database connection is not used due to the accumulation
+     * of unused connections.
+     */
+    void closeConnection() {
         try {
             this.databaseConnection.close();
         } catch (SQLException e) {
@@ -40 +62 @@
         }
     }
 
+    /**
+     * Simple getter for getting the database connection.
+     *
+     * @return database connection
+     */
     public Connection getDatabaseConnection() {
         return databaseConnection;
     }
 
+    /**
+     * Method for analyzing a given project. These methods are intended
+     * for queries that have a single select output (for example Road To Nowhere).
+     *
+     * @param project analyzed project
+     * @param queries list of queries
+     * @return result set of results
+     */
     public ResultSet executeQueries(Project project, List<String> queries) {
         Statement stmt;
         ResultSet resultSet = null;
@@ -51 +86 @@
             stmt = this.getDatabaseConnection().createStatement();
 
             for (String query : queries) {
-                if(queries.indexOf(query) != queries.size()-1){
-                    if(query.contains("?"))
+                if (queries.indexOf(query) != queries.size() - 1) {
+                    if (query.contains("?"))
                         query = query.replace("?", project.getId().toString());
                     stmt.executeQuery(query);
                 } else {
@@ -66 +101 @@
         return resultSet;
     }
 
-    public List<List<Map<String,Object>>> executeQueriesWithMultipleResults(Project project, List<String> queries) {
+    public List<List<Map<String, Object>>> executeQueriesWithMultipleResults(Project project, List<String> queries) {
         Statement stmt;
-        List<List<Map<String,Object>>> allResults = new ArrayList<>();
+        List<List<Map<String, Object>>> allResults = new ArrayList<>();
         ResultSet resultSet = null;
         try {
             stmt = this.getDatabaseConnection().createStatement();
 
             for (String query : queries) {
-                if(queries.indexOf(query) != queries.size()-1){
-                    if(query.contains("?"))
+                if (queries.indexOf(query) != queries.size() - 1) {
+                    if (query.contains("?"))
                         query = query.replace("?", project.getId().toString());
                     resultSet = stmt.executeQuery(query);
                 } else {
@@ -83 +118 @@
                 }
 
                 if (query.toLowerCase().startsWith("select")) {
-                    allResults.add(resultSetToArrayList(resultSet));
+                    allResults.add(Utils.resultSetToArrayList(resultSet));
                 }
 
             }
@@ -93 +128 @@
 
         return allResults;
     }
-
-    private List<Map<String,Object>> resultSetToArrayList(ResultSet rs) throws SQLException {
-        ResultSetMetaData md = rs.getMetaData();
-        int columns = md.getColumnCount();
-        List<Map<String, Object>> list = new ArrayList<>();
-        while (rs.next()) {
-            Map<String, Object> row = new HashMap<>(columns);
-            for (int i = 1; i <= columns; ++i) {
-                row.put(md.getColumnName(i), rs.getObject(i));
-            }
-            list.add(row);
-        }
-
-        return list;
-    }
 }

src/main/java/cz/zcu/fav/kiv/antipatterndetectionapp/detecting/detectors/BusinessAsUsualDetectorImpl.java

@@ -1 +1 @@
 package cz.zcu.fav.kiv.antipatterndetectionapp.detecting.detectors;
 
+import cz.zcu.fav.kiv.antipatterndetectionapp.Constants;
 import cz.zcu.fav.kiv.antipatterndetectionapp.detecting.DatabaseConnection;
 import cz.zcu.fav.kiv.antipatterndetectionapp.model.*;
+import cz.zcu.fav.kiv.antipatterndetectionapp.utils.Utils;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
 
 public class BusinessAsUsualDetectorImpl implements AntiPatternDetector {
 
@@ -23 +22 @@
                 put("divisionOfIterationsWithRetrospective", new Configuration<Float>("divisionOfIterationsWithRetrospective",
                         "Division of iterations with retrospective",
                         "Minimum percentage of the total number of iterations with a retrospective (0,1)", 0.66666f));
+                put("searchSubstringsWithRetrospective", new Configuration<String>("searchSubstringsWithRetrospective",
+                        "Search substrings with retrospective",
+                        "Substring that will be search in wikipages and activities",
+                        "%retr%" + Constants.SUBSTRING_DELIMITER +
+                                "%revi%" + Constants.SUBSTRING_DELIMITER +
+                                "%week%scrum%"));
             }},
             "Business_As_Usual.md");
 
@@ -35 +40 @@
         return (float) antiPattern.getConfigurations().get("divisionOfIterationsWithRetrospective").getValue();
     }
 
+    private List<String> getSearchSubstringsWithRetrospective() {
+        return Arrays.asList(((String) antiPattern.getConfigurations().get("searchSubstringsWithRetrospective").getValue()).split("\\|\\|"));
+    }
+
     @Override
     public AntiPattern getAntiPatternModel() {
         return this.antiPattern;
@@ -75 +84 @@
         Map<String, Integer> iterationsResults = new HashMap<>();
 
         // projít výsledky dotazů a dát do jedné mapy => v této mapě by měly být všechny iterace
-        List<List<Map<String, Object>>> resultSets = databaseConnection.executeQueriesWithMultipleResults(project, this.sqlQueries);
+        List<List<Map<String, Object>>> resultSets = databaseConnection.executeQueriesWithMultipleResults(project,
+                Utils.fillQueryWithSearchSubstrings(this.sqlQueries, getSearchSubstringsWithRetrospective()));
         for (int i = 0; i < resultSets.size(); i++) {
             List<Map<String, Object>> rs = resultSets.get(i);
 

src/main/java/cz/zcu/fav/kiv/antipatterndetectionapp/detecting/detectors/LongOrNonExistentFeedbackLoopsDetectorImpl.java

@@ -1 +1 @@
 package cz.zcu.fav.kiv.antipatterndetectionapp.detecting.detectors;
 
+import cz.zcu.fav.kiv.antipatterndetectionapp.Constants;
 import cz.zcu.fav.kiv.antipatterndetectionapp.detecting.DatabaseConnection;
 import cz.zcu.fav.kiv.antipatterndetectionapp.model.*;
 import cz.zcu.fav.kiv.antipatterndetectionapp.utils.Utils;
@@ -8 +9 @@
 
 import java.math.BigDecimal;
 import java.sql.Date;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
 
 public class LongOrNonExistentFeedbackLoopsDetectorImpl implements AntiPatternDetector {
 
@@ -33 +31 @@
                         "Maximum gap between feedback loop rate",
                         "Value that multiplies average iteration length for given project. Result" +
                                 " is maximum threshold value for gap between feedback loops in days.", 2f));
+                put("searchSubstringsWithFeedbackLoop", new Configuration<String>("searchSubstringsWithFeedbackLoop",
+                        "Search substrings with feedback loop",
+                        "Substring that will be search in wikipages and activities",
+                        "%schůz%zákazník%" + Constants.SUBSTRING_DELIMITER +
+                                "%předvedení%zákazník%" + Constants.SUBSTRING_DELIMITER +
+                                "%zákazn%demo%" + Constants.SUBSTRING_DELIMITER +
+                                "%schůz%zadavat%" + Constants.SUBSTRING_DELIMITER +
+                                "%inform%schůz%" + Constants.SUBSTRING_DELIMITER +
+                                "%zákazn%" + Constants.SUBSTRING_DELIMITER +
+                                "%zadavatel%"));
             }}
-            );
+    );
 
     private final String SQL_FILE_NAME = "long_or_non_existent_feedback_loops.sql";
     // sql queries loaded from sql file
@@ -48 +56 @@
         return (float) antiPattern.getConfigurations().get("maxGapBetweenFeedbackLoopRate").getValue();
     }
 
+    private List<String> getSearchSubstringsWithFeedbackLoop() {
+        return Arrays.asList(((String) antiPattern.getConfigurations().get("searchSubstringsWithFeedbackLoop").getValue()).split("\\|\\|"));
+    }
+
     @Override
     public AntiPattern getAntiPatternModel() {
         return this.antiPattern;
@@ -91 +103 @@
         Date projectStartDate = null;
         Date projectEndDate = null;
 
-        List<List<Map<String, Object>>> resultSets = databaseConnection.executeQueriesWithMultipleResults(project, this.sqlQueries);
+        List<List<Map<String, Object>>> resultSets = databaseConnection.executeQueriesWithMultipleResults(project,
+                Utils.fillQueryWithSearchSubstrings(this.sqlQueries, getSearchSubstringsWithFeedbackLoop()));
         for (int i = 0; i < resultSets.size(); i++) {
             List<Map<String, Object>> rs = resultSets.get(i);
 

src/main/java/cz/zcu/fav/kiv/antipatterndetectionapp/detecting/detectors/RoadToNowhereDetectorImpl.java

@@ -1 +1 @@
 package cz.zcu.fav.kiv.antipatterndetectionapp.detecting.detectors;
 
+import cz.zcu.fav.kiv.antipatterndetectionapp.Constants;
 import cz.zcu.fav.kiv.antipatterndetectionapp.detecting.DatabaseConnection;
 import cz.zcu.fav.kiv.antipatterndetectionapp.model.*;
+import cz.zcu.fav.kiv.antipatterndetectionapp.utils.Utils;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
 import java.sql.ResultSet;
 import java.sql.SQLException;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
+import java.util.*;
 
 public class RoadToNowhereDetectorImpl implements AntiPatternDetector {
 
@@ -28 +28 @@
                 put("minNumberOfActivitiesWithProjectPlan", new Configuration<Integer>("minNumberOfActivitiesWithProjectPlan",
                         "Minimum number of activities with project plan",
                         "Number of activities", 1));
+                put("searchSubstringsWithProjectPlan", new Configuration<String>("searchSubstringsWithProjectPlan",
+                        "Search substrings with project plan",
+                        "Substring that will be search in wikipages and activities",
+                        "%plán projektu%" + Constants.SUBSTRING_DELIMITER +
+                                "%project plan%" + Constants.SUBSTRING_DELIMITER +
+                                "%plan project%" + Constants.SUBSTRING_DELIMITER +
+                                "%projektový plán%"));
             }},
             "Road_To_Nowhere.md");
 
@@ -43 +50 @@
         return (int) antiPattern.getConfigurations().get("minNumberOfActivitiesWithProjectPlan").getValue();
     }
 
+    private List<String> getSearchSubstringsWithProjectPlan() {
+        return Arrays.asList(((String) antiPattern.getConfigurations().get("searchSubstringsWithProjectPlan").getValue()).split("\\|\\|"));
+    }
+
     @Override
     public AntiPattern getAntiPatternModel() {
         return this.antiPattern;
@@ -60 +71 @@
 
     /**
      * Postup detekce:
-     *      1) u každého projektu zkusit nalézt jestli obsahuje nějaké wiki stránky s projektovým plánem
-     *      2) dále zkusit najít aktivity, které by naznačovali, že vznikl nějaký projektový plán
-     *      3) pokud nebude nalezena žádná aktivita nebo wiki stránka, tak je antivzor detekován
+     * 1) u každého projektu zkusit nalézt jestli obsahuje nějaké wiki stránky s projektovým plánem
+     * 2) dále zkusit najít aktivity, které by naznačovali, že vznikl nějaký projektový plán
+     * 3) pokud nebude nalezena žádná aktivita nebo wiki stránka, tak je antivzor detekován
      *
      * @param project            analyzovaný project
      * @param databaseConnection databázové připojení
@@ -77 +88 @@
         int numberOfWikiPagesForProjectPlan = 0;
 
         try {
-            ResultSet rs = databaseConnection.executeQueries(project, this.sqlQueries);
+            ResultSet rs = databaseConnection.executeQueries(project,
+                    Utils.fillQueryWithSearchSubstrings(this.sqlQueries, getSearchSubstringsWithProjectPlan()));
             if (rs != null) {
                 while (rs.next()) {
                     numberOfIssuesForProjectPlan = rs.getInt("numberOfIssuesForProjectPlan");
@@ -94 +106 @@
         resultDetails.add(new ResultDetail("Number of issues for creating project plan", String.valueOf(numberOfIssuesForProjectPlan)));
         resultDetails.add(new ResultDetail("Number of wiki pages for creating project plan", String.valueOf(numberOfWikiPagesForProjectPlan)));
 
-        if( numberOfIssuesForProjectPlan >= getMinNumberOfActivitiesWithProjectPlan() || numberOfWikiPagesForProjectPlan >= getMinNumberOfWikiPagesWithProjectPlan()) {
+        if (numberOfIssuesForProjectPlan >= getMinNumberOfActivitiesWithProjectPlan() || numberOfWikiPagesForProjectPlan >= getMinNumberOfWikiPagesWithProjectPlan()) {
             resultDetails.add(new ResultDetail("Conclusion", "Found some activities or wiki pages for project plan in first two iterations"));
             return new QueryResultItem(this.antiPattern, false, resultDetails);
         } else {

src/main/java/cz/zcu/fav/kiv/antipatterndetectionapp/detecting/detectors/SpecifyNothingDetectorImpl.java

@@ -1 +1 @@
 package cz.zcu.fav.kiv.antipatterndetectionapp.detecting.detectors;
 
+import cz.zcu.fav.kiv.antipatterndetectionapp.Constants;
 import cz.zcu.fav.kiv.antipatterndetectionapp.detecting.DatabaseConnection;
 import cz.zcu.fav.kiv.antipatterndetectionapp.model.*;
+import cz.zcu.fav.kiv.antipatterndetectionapp.utils.Utils;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
 import java.sql.ResultSet;
 import java.sql.SQLException;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
+import java.util.*;
 
 public class SpecifyNothingDetectorImpl implements AntiPatternDetector {
 
@@ -30 +30 @@
                 put("minAvgLengthOfActivityDescription", new Configuration<Integer>("minAvgLengthOfActivityDescription",
                         "Minimum average length of activity description",
                         "Minimum average number of character of activity description", 150));
+                put("searchSubstringsWithProjectSpecification", new Configuration<String>("searchSubstringsWithProjectSpecification",
+                        "Search substrings with project specification",
+                        "Substring that will be search in wikipages and activities",
+                        "%dsp%" + Constants.SUBSTRING_DELIMITER +
+                                "%specifikace%" + Constants.SUBSTRING_DELIMITER +
+                                "%specification%" + Constants.SUBSTRING_DELIMITER +
+                                "%vize%proj%" + Constants.SUBSTRING_DELIMITER +
+                                "%vize%produ%"));
             }},
             "Specify_Nothing.md");
 
@@ -49 +57 @@
         return (int) antiPattern.getConfigurations().get("minAvgLengthOfActivityDescription").getValue();
     }
 
+    private List<String> getSearchSubstringsWithProjectSpecification() {
+        return Arrays.asList(((String) antiPattern.getConfigurations().get("searchSubstringsWithProjectSpecification").getValue()).split("\\|\\|"));
+    }
+
     @Override
     public AntiPattern getAntiPatternModel() {
         return this.antiPattern;
@@ -86 +98 @@
         double averageLengthOfIssueDescription = 0;
 
         try {
-            ResultSet rs = databaseConnection.executeQueries(project, this.sqlQueries);
+            ResultSet rs = databaseConnection.executeQueries(project,
+                    Utils.fillQueryWithSearchSubstrings(this.sqlQueries, getSearchSubstringsWithProjectSpecification()));
             if (rs != null) {
                 while (rs.next()) {
                     numberOfWikiPages = rs.getInt("numberOfWikiPages");

src/main/java/cz/zcu/fav/kiv/antipatterndetectionapp/service/AntiPatternServiceImpl.java

@@ -90 +90 @@
                         } catch (NumberFormatException e) {
                             return false;
                         }
+                    } else if (antiPatternDetector.getAntiPatternModel().getConfigurations().get(configNames[i]).getValue().getClass() == String.class) {
+                            antiPatternDetector.getAntiPatternModel().getConfigurations().get(configNames[i]).setValue((configValues[i]));
+                            setConfigurationChanged(true);
                     }
                 }
             }

src/main/java/cz/zcu/fav/kiv/antipatterndetectionapp/utils/Utils.java

@@ -5 +5 @@
 import org.slf4j.LoggerFactory;
 
 import java.sql.Date;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
 import java.time.LocalDate;
 import java.time.temporal.ChronoUnit;
-import java.util.ArrayList;
-import java.util.Arrays;
-import java.util.List;
+import java.util.*;
 
 public class Utils {
 
@@ -34 +35 @@
     public static List<ResultDetail> createResultDetailsList(ResultDetail... resultDetails) {
         return new ArrayList<>(Arrays.asList(resultDetails));
     }
+
+    public static List<Map<String, Object>> resultSetToArrayList(ResultSet rs) throws SQLException {
+        ResultSetMetaData md = rs.getMetaData();
+        int columns = md.getColumnCount();
+        List<Map<String, Object>> list = new ArrayList<>();
+        while (rs.next()) {
+            Map<String, Object> row = new HashMap<>(columns);
+            for (int i = 1; i <= columns; ++i) {
+                row.put(md.getColumnName(i), rs.getObject(i));
+            }
+            list.add(row);
+        }
+
+        return list;
+    }
+
+    public static List<String> fillQueryWithSearchSubstrings(List<String> queries, List<String> substrings) {
+        List<String> preparedQueries = new ArrayList<>();
+        for (String query : queries) {
+            // check if query contains sequence of substrings to fill
+            if (query.contains("§0§")) {
+                int counter = 0;
+
+                //insert all substrings in the query
+                for (String substring : substrings) {
+                    query = query.replaceAll("§" + counter + "§", substring);
+                    counter++;
+                }
+
+                // replacement of unused places for substrings
+                query = query.replaceAll("§[0-9]§", "^@");
+
+            }
+            preparedQueries.add(query);
+        }
+
+        return preparedQueries;
+    }
 }

src/main/webapp/queries/business_as_usual.sql

@@ -17 +17 @@
 
 /* Init project id */
 set @projectId = ?;
-/* Retrospective substring */
-set @restrospectiveSubstring = '%retr%';
-/* Revision substring */
-set @revisionSubstring = '%revi%';
 /* Number of iterations for given project */
 select COUNT(id) as 'numberOfIterations' from iteration where superProjectId = @projectId and name like '%itera%';
 /* Select all iteration with detected retrospective activities */
-select iterationName as 'iterationName', count(name) as 'numberOfIssues' from workUnitView where projectId = @projectId and (name like @restrospectiveSubstring or name like @revisionSubstring or name like '%week%scrum%') group by iterationName;
+select iterationName as 'iterationName', count(name) as 'numberOfIssues' from workUnitView where projectId = @projectId and (lower(name) like lower('%retr%') or lower(name) like lower('%revi%') or lower(name) like lower('%week%scrum%')) group by iterationName;
 /* Select all wikipages that were created or updated in iteration and have name with retr or revi*/
-select iteration.name as 'iterationName', count(distinct(artifactView.name)) as 'numberOfWikiPages' from artifactView inner join fieldChangeView on artifactView.id = fieldChangeView.itemId inner join iteration on (fieldChangeView.created between iteration.startDate and iteration.endDate) and iteration.superProjectId = @projectId where artifactView.projectId = @projectId and artifactView.artifactClass like 'WIKIPAGE' and (artifactView.name like '%retr%' or artifactView.description like '%retr%') group by iteration.id order by iteration.name;
+select iteration.name as 'iterationName', count(distinct(artifactView.name)) as 'numberOfWikiPages' from artifactView inner join fieldChangeView on artifactView.id = fieldChangeView.itemId inner join iteration on (fieldChangeView.created between iteration.startDate and iteration.endDate) and iteration.superProjectId = @projectId where artifactView.projectId = @projectId and artifactView.artifactClass like 'WIKIPAGE' and (lower(artifactView.name) like lower('%retr%') or lower(artifactView.description) like lower('%retr%') or lower(artifactView.description) like lower('%week%scrum%')) group by iteration.id order by iteration.name;

src/main/webapp/queries/long_or_non_existent_feedback_loops.sql

@@ -23 +23 @@
 /* Average iteration length */
 select avg(abs(dateDiff(iteration.endDate, iteration.startDate))) as 'averageIterationLength' from iteration where superProjectId = @projectId;
 /* Select number of iterations which contains at least one feedback activity */
-select count(*) over () as 'totalCountOfIterationsWithFeedbackActivity' from workUnitView as wuv where wuv.projectId = @projectId and (wuv.name like "%schůz%zákazník%" OR wuv.name like "%předvedení%zákazník%" OR wuv.name LIKE "%zákazn%demo%" OR wuv.name like "%schůz%zadavat%" OR wuv.name like "%inform%schůz%" OR wuv.name like "%zákazn%" OR wuv.name like "%zadavatel%") group by wuv.iterationName order by wuv.activityEndDate;
+select count(*) over () as 'totalCountOfIterationsWithFeedbackActivity' from workUnitView as wuv where wuv.projectId = @projectId and (wuv.name like "§0§" or wuv.name like "§1§" or wuv.name like "§2§" or wuv.name like "§3§" or wuv.name like "§4§" or wuv.name like "§5§" or wuv.name like "§6§" or wuv.name like "§7§" or wuv.name like "§8§" or wuv.name like "§9§") group by wuv.iterationName order by wuv.activityEndDate;
 /* Select all activities for feedback loop with last modified date as end date */
-select wuv.id, wuv.iterationName, wuv.name, cast(max(fieldChangeView.created) as date) as 'endDate' from workUnitView as wuv inner join fieldChangeView on wuv.id = fieldChangeView.itemId where wuv.projectId = @projectId and (wuv.name like "%schůz%zákazník%" OR wuv.name like "%předvedení%zákazník%" OR wuv.name LIKE "%zákazn%demo%" OR wuv.name like "%schůz%zadavat%" OR wuv.name like "%inform%schůz%" OR wuv.name like "%zákazn%" OR wuv.name like "%zadavatel%") GROUP by id order by fieldChangeView.created;
+select wuv.id, wuv.iterationName, wuv.name, cast(max(fieldChangeView.created) as date) as 'endDate' from workUnitView as wuv inner join fieldChangeView on wuv.id = fieldChangeView.itemId where wuv.projectId = @projectId and (wuv.name like "§0§" or wuv.name like "§1§" or wuv.name like "§2§" or wuv.name like "§3§" or wuv.name like "§4§" or wuv.name like "§5§" or wuv.name like "§6§" or wuv.name like "§7§" or wuv.name like "§8§" or wuv.name like "§9§") GROUP by id order by fieldChangeView.created;
 /* Get project start date */
 select startDate as 'projectStartDate' from iteration where superProjectId = @projectId order by startDate limit 1;
 /* Get project end date */
 select endDate as 'projectEndDate' from iteration where superProjectId = @projectId order by endDate desc limit 1;
 /* Select all iterations that contains wiki pages which were created or updated in iteration and have name or description that mentions some key words for customer demo*/
-select iteration.name as 'iterationWithCustomerFeedback', cast(max(fieldChangeView.created) as date) as 'appointmentDate' from artifactView inner join fieldChangeView on artifactView.id = fieldChangeView.itemId inner join iteration on (fieldChangeView.created between iteration.startDate and iteration.endDate) and iteration.superProjectId = @projectId where artifactView.artifactClass like "WIKIPAGE" and artifactView.projectId = @projectId and length(fieldChangeView.newValue) > length(fieldChangeView.oldValue) and artifactView.name like "%zápis%schůz%" or artifactView.name like "%schůz%zákazník%" OR artifactView.name like "%zákazn%demo%" or artifactView.name like "%schůz%zadavat%" or artifactView.name like "%zadavatel%" OR artifactView.name like "%zákazn%" group by iteration.name order by iteration.name;
+select iteration.name as 'iterationWithCustomerFeedback', cast(max(fieldChangeView.created) as date) as 'appointmentDate' from artifactView inner join fieldChangeView on artifactView.id = fieldChangeView.itemId inner join iteration on (fieldChangeView.created between iteration.startDate and iteration.endDate) and iteration.superProjectId = @projectId where artifactView.artifactClass like "WIKIPAGE" and artifactView.projectId = @projectId and length(fieldChangeView.newValue) > length(fieldChangeView.oldValue) and (artifactView.name like "§0§" or artifactView.name like "§1§" or artifactView.name like "§2§" or artifactView.name like "§3§" or artifactView.name like "§4§" or artifactView.name like "§5§" or artifactView.name like "§6§" or artifactView.name like "§7§" or artifactView.name like "§8§" or artifactView.name like "§9§") group by iteration.name order by iteration.name;

src/main/webapp/queries/road_to_nowhere.sql

@@ -15 +15 @@
 set @projectId = ?;
 set @firstIterationStartDate = (select startDate from iteration where superProjectId = @projectId ORDER BY startDate LIMIT 1 offset 0);
 set @secondIterationStartDate = (select startDate from iteration where superProjectId = @projectId ORDER BY startDate LIMIT 1 offset 1);
-set @numberOfIssuesForProjectPlan = (SELECT count(*) from workUnitView where projectId = @projectId  and (lower(workUnitView.name) like lower('%plán projektu%') or lower(workUnitView.description) like lower('%plán projektu%') or lower(workUnitView.name) like lower('%project plan%') or lower(workUnitView.description) like lower('%project plan%') or lower(workUnitView.name) like lower('%plan project%') or lower(workUnitView.description) like lower('%plan project%') or lower(workUnitView.name) like lower('%projektový plán%') or lower(workUnitView.description) like lower('%projektový plán%')) AND (iterationStartDate = @firstIterationStartDate OR iterationStartDate = @secondIterationStartDate));
-set @numberOfWikiPagesForProjectPlan = (SELECT count(*) from artifactView where projectId = @projectId AND artifactClass like 'WIKIPAGE' AND (lower(artifactView.name) like lower('%plán projektu%') or lower(artifactView.description) like lower('%plán projektu%') or lower(artifactView.name) like lower('%plán projektu%') or lower(artifactView.description) like lower('%plán projektu%') or lower(artifactView.name) like lower('%project plan%') or lower(artifactView.description) like lower('%project plan%') or lower(artifactView.name) like lower('%plan project%') or lower(artifactView.description) like lower('%plan project%') or lower(artifactView.name) like lower('%projekotvý plán%') or lower(artifactView.description) like lower('%projektový plán%')));
+set @numberOfIssuesForProjectPlan = (SELECT count(*) from workUnitView where projectId = @projectId  and (lower(workUnitView.name) like lower('§0§') or lower(workUnitView.description) like lower('§0§') or lower(workUnitView.name) like lower('§1§') or lower(workUnitView.description) like lower('§1§') or lower(workUnitView.name) like lower('§2§') or lower(workUnitView.description) like lower('§2§') or lower(workUnitView.name) like lower('§3§') or lower(workUnitView.description) like lower('§3§') or lower(workUnitView.name) like lower('§4§') or lower(workUnitView.description) like lower('§4§') or lower(workUnitView.name) like lower('§5§') or lower(workUnitView.description) like lower('§5§') or lower(workUnitView.name) like lower('§6§') or lower(workUnitView.description) like lower('§6§') or lower(workUnitView.name) like lower('§7§') or lower(workUnitView.description) like lower('§7§') or lower(workUnitView.name) like lower('§8§') or lower(workUnitView.description) like lower('§8§') or lower(workUnitView.name) like lower('§9§') or lower(workUnitView.description) like lower('§9§')) AND (iterationStartDate = @firstIterationStartDate OR iterationStartDate = @secondIterationStartDate));
+set @numberOfWikiPagesForProjectPlan = (SELECT count(*) from artifactView where projectId = @projectId AND artifactClass like 'WIKIPAGE' AND (lower(artifactView.name) like lower('§0§') or lower(artifactView.description) like lower('§0§') or lower(artifactView.name) like lower('§1§') or lower(artifactView.description) like lower('§1§') or lower(artifactView.name) like lower('§2§') or lower(artifactView.description) like lower('§2§') or lower(artifactView.name) like lower('§3§') or lower(artifactView.description) like lower('§3§') or lower(artifactView.name) like lower('§4§') or lower(artifactView.description) like lower('§4§') or lower(artifactView.name) like lower('§5§') or lower(artifactView.description) like lower('§5§') or lower(artifactView.name) like lower('§6§') or lower(artifactView.description) like lower('§6§') or lower(artifactView.name) like lower('§7§') or lower(artifactView.description) like lower('§7§') or lower(artifactView.name) like lower('§8§') or lower(artifactView.description) like lower('§8§') or lower(artifactView.name) like lower('§9§') or lower(artifactView.description) like lower('§9§')));
 select @projectId as `projectId`, @numberOfIssuesForProjectPlan as `numberOfIssuesForProjectPlan`, @numberOfWikiPagesForProjectPlan as `numberOfWikiPagesForProjectPlan`;

src/main/webapp/queries/specify_nothing.sql

@@ -13 +13 @@
 /* Init project id */
 set @projectId = ?;
 /* Find number of wikipages with some project specification */
-set @numberOfWikiPages = (select count(name) from artifactView where projectId = @projectId and artifactClass = 'WIKIPAGE' and (lower(name) like lower('%dsp%') or lower(name) like lower('%specifikace%') or lower(name) like lower('%specification%') or lower(name) like lower('%vize%proj%') or lower(name) like lower('%vize%produ%') or lower(description) like lower('%DSP%') or lower(description) like lower('%specifikace%') or lower(description) like lower('%specification%')));
+set @numberOfWikiPages = (select count(name) from artifactView where projectId = @projectId and artifactClass = 'WIKIPAGE' and (lower(name) like lower('§0§') or lower(description) like lower('§0§') or lower(name) like lower('§1§') or lower(description) like lower('§1§') or lower(name) like lower('§2§') or lower(description) like lower('§2§') or lower(name) like lower('§3§') or lower(description) like lower('§3§') or lower(name) like lower('§4§') or lower(description) like lower('§4§') or lower(name) like lower('§5§') or lower(description) like lower('§5§') or lower(name) like lower('§6§') or lower(description) like lower('§6§') or lower(name) like lower('§7§') or lower(description) like lower('§7§') or lower(name) like lower('§8§') or lower(description) like lower('§8§') or lower(name) like lower('§9§') or lower(description) like lower('§9§')));
 /* Find activities for creating DSP or project specification */
-set @numberOfActivitiesForSpecification = (SELECT count(id) from workUnitView where projectId = @projectId and (lower(name) like lower('%DSP%') or lower(name) like lower('%specifikace%') or lower(name) like lower('%specification%') or lower(name) like lower('%vize%proj%')));
+set @numberOfActivitiesForSpecification = (SELECT count(id) from workUnitView where projectId = @projectId and (lower(name) like lower('§0§') or lower(name) like lower('§1§') or lower(name) like lower('§2§') or lower(name) like lower('§3§') or lower(name) like lower('§4§') or lower(name) like lower('§5§') or lower(name) like lower('§6§') or lower(name) like lower('§7§') or lower(name) like lower('§8§') or lower(name) like lower('§9§')));
 /* Count average length of issues description */
 set @averageLengthOfIssueDescription = (select AVG(CHAR_LENGTH(workUnitView.description)) from workUnitView where workUnitView.projectId = @projectId);
 /* Show all statistics */