/ - Diff - Vylepšení funkcí a rozhraní frameworku SPADe (Pícha) - PRO_grammers. - Redmine

     # AntiPatternDetectionApp
     This application was created as part of the thesis. It is used for analysis and detection of anti-patterns in project data.
     # ReliSA - SPADe-Web-GUI
     This application was is used for analysis and detection of anti-patterns in project data.
     ## Thesis abstract
     The goal of this thesis is to analyze and automatically detect anti-patterns in the data of project management tools using SPADe tools. The SPADe tool is used to collect data from ALM tools and search for bad practices (anti-patterns) in project data. In order to develop this thesis, an analysis of the available anti-patterns was performed and then a subset was selected for further investigation. In the next part, the detection of the analyzed anti-patterns was implemented using SQL queries and Java programming language was used to process the results of these queries. As an extra feature of this thesis, going beyond the scope of the original assignment, a support web application for running the detection process of implemented anti-patters and results presentation was also developed. Furthermore, an experiment was performed on a selected sets of anti-patterns and project data. The success of the detection was verified by comparing the results to those from a manual anti-pattern detection in project data from the source ALM tools. Detection success was successful at 93.65 % compared to manual control.
     ## Analyzed Anti-Patterns
     In version 1.0.0 of this application, the detection of the following Anti-Patterns is implemented:
     * Business As Usual
     * Long Or Non-Existant Feedback Loops
-...
     * Too Long Sprint
     * Varying Sprint Length
     ## Run
     ### Tools to run
     It will need the following tools to run the application:
     * Docker
     * Docker Compose
     ### Run application
     The following list describes the steps to run (you will need):
 . Open terminal.
 . Move to the root folder of this project (AntiPatternDetectionApp).
 . Build docker images with command `docker-compose build`.
 . Create and run all containers with command `docker-compose run`.
 . Open phpMyAdmin on docker address on port 8082.
 . Create database with name spade using command `CREATE DATABASE spade;`.
 . Restore database from file `db_dump.sql` located in project root folder.
 . Run all commands from file `config.sql` located in project root folder.
     # Basic architecture of app
     # Installation guide
     # Add new Anti-Pattern
     # User guide
     ## Analyze projects
 ) On home page select project to analyze (you can use check box for select/deselect all projects)
     TODO obrázek
 ) Select Anti-Patterns to analyze (you can use check box for select/deselect all Anti-Patterns)
     TODO obrázek
 ) Click to button Analyze
     TODO obrázek
 ) Table with detections result
     TODO obrázek
 ) Show details of detection
 ) Show detail of Anti-Pattern
     ## Configuration of Anti-Patterns
 ) Go to Configuration page
     TODO obrázek
 ) Change corresponding threshold values
     TODO obrázek
 ) Click button Save or press Enter
     TODO obrázek
 ) If the values are set correctly, a message is displayed stating that the values were saved successfully
     TODO obrázek
 ) If the values are set incorrectly, a error message is displayed and the corresponding values are highlighted in red with error message
     TODO obrázek
     ## Show Anti-Pattern detail
 ) Go to Anti-Pattern detail page
 ) On Home page click on anchor Show next to corresponding Anti-Pattern
 ) On result page slick on Anti-Pattern name and anchor Detail
 ) Anti-Pattern detail page is shown (name, description, anchor to catalogue, configuration values)
 ) Configuration values can be updated also in this page
 ) Change corresponding threshold values
 ) Click button Save or press Enter
 ) If the values are set correctly, a message is displayed stating that the values were saved successfully
     TODO obrázek
 ) If the values are set incorrectly, a error message is displayed and the corresponding values are highlighted in red with error message
     TODO obrázek
     ## Show project detail
 ) On home page click on anchor Show, next to corresponding project
     TODO obrázek
 ) Project details is shown (project id, name, description)
     TODO obrázek

         AntiPattern getAntiPatternModel();
         String getAntiPatternSqlFileName();
         List<String> getSqlFileNames();
         void setSqlQueries(List<String> queries);

                 }},
                 "Business_As_Usual.md");
         private final String sqlFileName = "business_as_usual.sql";
         private final List<String> SQL_FILE_NAMES = Arrays.asList(
                 "set_project_id.sql",
                 "select_number_of_iterations.sql",
                 "select_iterations_with_substrings.sql",
                 "select_all_wikipages_that_is_updated_in_iteration.sql");
         // sql queries loaded from sql file
         private List<String> sqlQueries;
-...
+        }
         @Override
         public String getAntiPatternSqlFileName() {
             return this.sqlFileName;
         public List<String> getSqlFileNames() {
             return this.SQL_FILE_NAMES;
+        }
         @Override

                 }}
         );
         private final String SQL_FILE_NAME = "long_or_non_existent_feedback_loops.sql";
         private final List<String> SQL_FILE_NAMES = Arrays.asList(
                 "set_project_id.sql",
                 "select_number_of_iterations.sql",
                 "select_average_iterations_length.sql",
                 "select_number_of_iterations_with_substrings_in_activity.sql",
                 "select_all_activities_with_substrings_and_last_modified_date_as_end_date.sql",
                 "select_project_start_date.sql",
                 "select_project_end_date.sql",
                 "select_all_iterations_that_contains_wikipages_with_substrings.sql");
         // sql queries loaded from sql file
         private List<String> sqlQueries;
-...
+        }
         private float getMaxGapBetweenFeedbackLoopRate() {
             return (float) antiPattern.getConfigurations().get("maxGapBetweenFeedbackLoopRate").getValue();
             return ((PositiveFloat) antiPattern.getConfigurations().get("maxGapBetweenFeedbackLoopRate").getValue()).floatValue();
+        }
         private List<String> getSearchSubstringsWithFeedbackLoop() {
-...
             return this.antiPattern;
+        }
         @Override
         public String getAntiPatternSqlFileName() {
             return this.SQL_FILE_NAME;
         public List<String> getSqlFileNames() {
             return this.SQL_FILE_NAMES;
+        }
         @Override
-...
                         break;
                     case 2:
                         if (rs.size() != 0) {
                             numberOfIterationsWhichContainsAtLeastOneActivityForFeedback = ((Long) rs.get(0).get("totalCountOfIterationsWithFeedbackActivity")).intValue();
                             numberOfIterationsWhichContainsAtLeastOneActivityForFeedback = ((Long) rs.get(0).get("totalCountOfIterationsWithSubstringsInActivity")).intValue();
+                        }
                         break;
                     case 3:

                             new PositiveInteger(2)));
                 }});
         private final String sqlFileName = "ninety_ninety_rule.sql";
         private final List<String> SQL_FILE_NAMES = Arrays.asList(
                 "set_project_id.sql",
                 "select_all_iterations_with_sum_estimated_and_spent_time.sql");
         // sql queries loaded from sql file
         private List<String> sqlQueries;
         private double getMaxDivisionRange() {
             return (Double) antiPattern.getConfigurations().get("maxDivisionRange").getValue();
             return ((PositiveFloat) antiPattern.getConfigurations().get("maxDivisionRange").getValue()).doubleValue();
+        }
         private int getMaxBadDivisionLimit() {
             return (int) antiPattern.getConfigurations().get("maxBadDivisionLimit").getValue();
             return ((PositiveInteger) antiPattern.getConfigurations().get("maxBadDivisionLimit").getValue()).intValue();
+        }
         @Override
-...
+        }
         @Override
         public String getAntiPatternSqlFileName() {
             return this.sqlFileName;
         public List<String> getSqlFileNames() {
             return this.SQL_FILE_NAMES;
+        }
         @Override
-...
         /**
          * Postup detekce:
          *      1) pro každou iteraci udělat součet stráveného a odhadovaného času přes všechny aktivity
          *      2) udělat podíl strávený čas / odhadovaný čas
          *      3) pokud všechny výsledky podílů budou menší než 1.2 => vše ok
          *      4) pokud předchozí bod nezabere, tak iterovat přes všechny podíly
          *      5) pokud budou nalezeny tři iterace po sobě, kde se stále zhoršují odhady => detekováno
          * 1) pro každou iteraci udělat součet stráveného a odhadovaného času přes všechny aktivity
          * 2) udělat podíl strávený čas / odhadovaný čas
          * 3) pokud všechny výsledky podílů budou menší než 1.2 => vše ok
          * 4) pokud předchozí bod nezabere, tak iterovat přes všechny podíly
          * 5) pokud budou nalezeny tři iterace po sobě, kde se stále zhoršují odhady => detekováno
+         *
          * @param project            analyzovaný project
          * @param databaseConnection databázové připojení

                 }},
                 "Road_To_Nowhere.md");
         private final String sqlFileName = "road_to_nowhere.sql";
         private final List<String> SQL_FILE_NAMES = Arrays.asList(
                 "set_project_id.sql",
                 "set_first_iteration_start_date.sql",
                 "set_second_iteration_start_date.sql",
                 "set_number_of_activities_with_substrings.sql",
                 "set_number_of_wiki_pages_with_substrings.sql",
                 "select_number_of_activities_and_wiki_pages_with_substrings.sql");
         // sql queries loaded from sql file
         private List<String> sqlQueries;
         private int getMinNumberOfWikiPagesWithProjectPlan() {
             return (int) antiPattern.getConfigurations().get("minNumberOfWikiPagesWithProjectPlan").getValue();
             return ((PositiveInteger) antiPattern.getConfigurations().get("minNumberOfWikiPagesWithProjectPlan").getValue()).intValue();
+        }
         private int getMinNumberOfActivitiesWithProjectPlan() {
             return (int) antiPattern.getConfigurations().get("minNumberOfActivitiesWithProjectPlan").getValue();
             return ((PositiveInteger) antiPattern.getConfigurations().get("minNumberOfActivitiesWithProjectPlan").getValue()).intValue();
+        }
         private List<String> getSearchSubstringsWithProjectPlan() {
-...
+        }
         @Override
         public String getAntiPatternSqlFileName() {
             return this.sqlFileName;
         public List<String> getSqlFileNames() {
             return this.SQL_FILE_NAMES;
+        }
         @Override
-...
                         Utils.fillQueryWithSearchSubstrings(this.sqlQueries, getSearchSubstringsWithProjectPlan()));
                 if (rs != null) {
                     while (rs.next()) {
                         numberOfIssuesForProjectPlan = rs.getInt("numberOfIssuesForProjectPlan");
                         numberOfWikiPagesForProjectPlan = rs.getInt("numberOfWikiPagesForProjectPlan");
                         numberOfIssuesForProjectPlan = rs.getInt("numberOfActivitiesWithSubstrings");
                         numberOfWikiPagesForProjectPlan = rs.getInt("numberOfWikiPagesWithSubstrings");
+                    }
+                }

                 }},
                 "Specify_Nothing.md");
         private final String sqlFileName = "specify_nothing.sql";
         private final List<String> SQL_FILE_NAMES = Arrays.asList(
                 "set_project_id.sql",
                 "set_number_of_wiki_pages_with_substrings.sql",
                 "set_number_of_activities_with_substrings.sql",
                 "set_average_length_of_activities_description.sql",
                 "select_statistics_for_given_project.sql");
         // sql queries loaded from sql file
         private List<String> sqlQueries;
         private int getMinNumberOfWikiPagesWithSpecification() {
             return (int) antiPattern.getConfigurations().get("minNumberOfWikiPagesWithSpecification").getValue();
             return ((PositiveInteger) antiPattern.getConfigurations().get("minNumberOfWikiPagesWithSpecification").getValue()).intValue();
+        }
         private int getMinNumberOfActivitiesWithSpecification() {
             return (int) antiPattern.getConfigurations().get("minNumberOfActivitiesWithSpecification").getValue();
             return ((PositiveInteger) antiPattern.getConfigurations().get("minNumberOfActivitiesWithSpecification").getValue()).intValue();
+        }
         private int getMinAvgLengthOfActivityDescription() {
             return (int) antiPattern.getConfigurations().get("minAvgLengthOfActivityDescription").getValue();
             return ((PositiveInteger) antiPattern.getConfigurations().get("minAvgLengthOfActivityDescription").getValue()).intValue();
+        }
         private List<String> getSearchSubstringsWithProjectSpecification() {
-...
+        }
         @Override
         public String getAntiPatternSqlFileName() {
             return this.sqlFileName;
         public List<String> getSqlFileNames() {
             return this.SQL_FILE_NAMES;
+        }
         @Override
-...
                         Utils.fillQueryWithSearchSubstrings(this.sqlQueries, getSearchSubstringsWithProjectSpecification()));
                 if (rs != null) {
                     while (rs.next()) {
                         numberOfWikiPages = rs.getInt("numberOfWikiPages");
                         numberOfActivitiesForSpecification = rs.getInt("numberOfActivitiesForSpecification");
                         numberOfWikiPages = rs.getInt("numberOfWikiPagesWithSubstrings");
                         numberOfActivitiesForSpecification = rs.getInt("numberOfActivitiesWithSubstrings");
                         averageLengthOfIssueDescription = rs.getDouble("averageLengthOfIssueDescription");
+                    }
+                }

     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 TooLongSprintDetectorImpl implements AntiPatternDetector {
-...
                 }}
         );
         private final String SQL_FILE_NAME = "too_long_sprint.sql";
         private final List<String> SQL_FILE_NAMES = Arrays.asList(
                 "set_project_id.sql",
                 "set_first_iteration_id.sql",
                 "set_last_iteration_id.sql",
                 "select_all_iterations_with_lengths_without_first_and_last.sql");
         // sql queries loaded from sql file
         private List<String> sqlQueries;
-...
+        }
         @Override
         public String getAntiPatternSqlFileName() {
             return this.SQL_FILE_NAME;
         public List<String> getSqlFileNames() {
             return this.SQL_FILE_NAMES;
+        }
         @Override
         public void setSqlQueries(List<String> queries) {
             this.sqlQueries = queries;
+        }
         private Integer getMaxIterationLength(){
             return (Integer) this.antiPattern.getConfigurations().get("maxIterationLength").getValue();
             return ((PositiveInteger) this.antiPattern.getConfigurations().get("maxIterationLength").getValue()).intValue();
+        }
         private Integer getMaxNumberOfTooLongIterations(){
             return (Integer) this.antiPattern.getConfigurations().get("maxNumberOfTooLongIterations").getValue();
             return ((PositiveInteger) this.antiPattern.getConfigurations().get("maxNumberOfTooLongIterations").getValue()).intValue();
+        }
         /**

     import java.sql.ResultSet;
     import java.sql.SQLException;
     import java.util.ArrayList;
     import java.util.Arrays;
     import java.util.HashMap;
     import java.util.List;
-...
                             new PositiveInteger(1)));
                 }});
         private final String sqlFileName = "varying_sprint_length.sql";
         private final List<String> SQL_FILE_NAMES = Arrays.asList(
                 "set_project_id.sql",
                 "set_first_iteration_id.sql",
                 "set_last_iteration_id.sql",
                 "select_all_iterations_with_lengths_without_first_and_last.sql");
         // sql queries loaded from sql file
         private List<String> sqlQueries;
         private Integer getMaxDaysDifference(){
             return (Integer) this.antiPattern.getConfigurations().get("maxDaysDifference").getValue();
             return ((PositiveInteger) this.antiPattern.getConfigurations().get("maxDaysDifference").getValue()).intValue();
+        }
         private Integer getMaxIterationChanged(){
             return (Integer) this.antiPattern.getConfigurations().get("maxIterationChanged").getValue();
             return ((PositiveInteger) this.antiPattern.getConfigurations().get("maxIterationChanged").getValue()).intValue();
+        }
         @Override
-...
+        }
         @Override
         public String getAntiPatternSqlFileName() {
             return this.sqlFileName;
         public List<String> getSqlFileNames() {
             return this.SQL_FILE_NAMES;
+        }
         @Override

         @Override
         public int intValue() {
             return 0;
             return (int) this.value;
+        }
         @Override
         public long longValue() {
             return 0;
             return (long) this.value;
+        }
         @Override
         public float floatValue() {
             return 0;
             return this.value;
+        }
         @Override
         public double doubleValue() {
             return 0;
             return this.value;
+        }
         public static PositiveFloat parsePositiveFloat(String value) {

         @Override
         public int intValue() {
             return 0;
             return this.value;
+        }
         @Override
         public long longValue() {
             return 0;
             return this.value;
+        }
         @Override
         public float floatValue() {
             return 0;
             return this.value;
+        }
         @Override
         public double doubleValue() {
             return 0;
             return this.value;
+        }
         public static PositiveInteger parsePositiveInteger(String value) {

     @Component
     public class AntiPatternRepository implements ServletContextAware {
         private static final String QUERY_DIR = "/queries/";
         private final Logger LOGGER = LoggerFactory.getLogger(AntiPatternRepository.class);
         private ServletContext servletContext;
         private Map<Long, AntiPatternDetector> antiPatternDetectors = init();
         private static final String QUERY_DIR = "/queries/" ;
         private Map<Long, AntiPatternDetector> init() {
             LOGGER.info("-------START CREATING DETECTORS WITH REFLECTION-------");
             Map<Long, AntiPatternDetector> antiPatterns = new HashMap<>();
-...
             this.servletContext = servletContext;
             LOGGER.info("-------START READING SQL FROM FILES-------");
             for (AntiPatternDetector antiPatternDetector : getAllAntiPatterns()) {
                 LOGGER.info("Reading sql from file " + antiPatternDetector.getAntiPatternSqlFileName());
                 antiPatternDetector.setSqlQueries(loadSqlFile(antiPatternDetector.getAntiPatternSqlFileName()));
                 LOGGER.info("Reading sql files for AP " + antiPatternDetector.getAntiPatternModel().getPrintName());
                 antiPatternDetector.setSqlQueries(loadSqlFile(antiPatternDetector.getSqlFileNames()));
+            }
             LOGGER.info("-------FINISHED READING SQL FROM FILES-------");
+        }
         private List<String> loadSqlFile(String fileName) {
         /**
          * Method for loading list of sql files from given list of files
          * @param fileNames list of files with sql queries
          * @return list of queries
          */
         private List<String> loadSqlFile(List<String> fileNames) {
             List<String> queries = new ArrayList<>();
             try {
                 URL test = servletContext.getResource(QUERY_DIR + fileName);
                 BufferedReader read = new BufferedReader(
                         new InputStreamReader(test.openStream()));
                 String line;
                 while ((line = read.readLine()) != null) {
                     if (line.startsWith("select") || line.startsWith("set") && line.charAt(line.length()-1) == ';') {
                         queries.add(line);
             // walk through all sql filenames and load all sql queries
             for (String fileName : fileNames) {
                 LOGGER.info("Reading sql query from file name " + fileName);
                 try {
                     URL test = servletContext.getResource(QUERY_DIR + fileName);
                     BufferedReader read = new BufferedReader(
                             new InputStreamReader(test.openStream()));
                     String line;
                     while ((line = read.readLine()) != null) {
                         if (line.startsWith("select") || line.startsWith("set") && line.charAt(line.length() - 1) == ';') {
                             queries.add(line);
+                        }
+                    }
                     read.close();
                 } catch (IOException e) {
                     LOGGER.warn("Cannot read sql from file " + fileName, e);
                     return queries;
+                }
                 read.close();
             } catch (IOException e) {
                 LOGGER.warn("Cannot read sql from file " + fileName, e);
                 return queries;
+            }
             return queries;
+        }

     /*
     Anti-pattern name: Business as usual (No sprint retrospective)
     Description: Absence of a retrospective after individual
                  iterations or after the completion project.
     Detection: There will be no activities in the project
                that would indicate that a retrospective is
                taking place (issue with the name of the
                retrospective, issue on which all team members
                log, issue that is repeated periodically,
                issue to which no commit is bound, issue which
                will be marked as administration or something like that).
                There will be no notes in the wiki or other tool called
                retrospectives (%retr%).
     */
     /* Init project id */
     set @projectId = ?;
     /* 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 (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§')) 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 (lower(artifactView.name) like lower('§0§') or lower(artifactView.name) like lower('§1§') or lower(artifactView.name) like lower('§2§') or lower(artifactView.name) like lower('§3§') or lower(artifactView.name) like lower('§4§') or lower(artifactView.name) like lower('§5§') or lower(artifactView.name) like lower('§6§') or lower(artifactView.name) like lower('§7§') or lower(artifactView.name) like lower('§8§') or lower(artifactView.name) like lower('§9§')) group by iteration.id order by iteration.name;

     /*
     Anti-pattern name: Long Or Non-Existant Feedback Loops (No Customer feedback)
     Description: Long spacings between customer feedback or no feedback. The customer
                  enters the project and sees the final result. In the end, the customer
                  may not get what he really wanted. With long intervals of feedback,
                  some misunderstood functionality can be created and we have to spend
                  a lot of effort and time to redo it.
     Detection: How to choose what is the optimal spacing between feedbacks? In ASWI,
                it was mostly after each iteration, ie 2-3 weeks apart. Check if there
                is an activity that is repeated periodically, all team members or
                leaders log time on it (essentially a similar issue as in the anti-Business
                as usual model). Search for an activity named "DEMO", "CUSTOMER", etc.
                Search for some records from the demo in the wiki. Similar to Business as usual.
     */
     /* Init project id */
     set @projectId = ?;
     /* Number of iterations for given project */
     select COUNT(id) as 'numberOfIterations' from iteration where superProjectId = @projectId;
     /* 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 '§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 '§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 '§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;

     /*
     Anti-pattern name: Ninety-Ninety Rule
     Description: The first 90 percent of the code represents the first 90 percent of development time. The
                  remaining 10 percent of the code represents another 90 percent of development time.
                  Then decide on a long delay of the project compared to the original estimate.
                  The functionality is almost done, some number is already closed and is only waiting
                  for one activity to close, but it has been open for a long time.
     Detection: Compare the estimated time and time spent in each iteration. If the estimates deteriorate
                continuously during the project, it will be detected.
     */
     /* Init project id */
     set @projectId = ?;
     /* Select all activities for each iteration and sum estimated time and spent time */
     select iterationName, sum(estimatedTime) as 'estimatedTime', sum(spentTime) as 'spentTime', sum(spentTime)/sum(estimatedTime) as 'timeDivision',  abs(1-(sum(spentTime)/sum(estimatedTime))) as 'deviation' from workUnitView where projectid = @projectId and iterationName is not null group by iterationName order by iterationName;

     /*
     Anti-pattern name: Road To Nowhere
     Description: The project is not sufficiently planned and therefore
                  takes place on an ad hoc basis with an uncertain
                  outcome and deadline. There is no project plan in the project.
     Detection: There is no activity in ALM that would indicate the creation
                of a project plan. There will be no document in the wiki
                called the "Project Plan". Project plan should be created in first or
                second iteration. Also could be detected with field change view. If is
                a lot of changes on issues in the beginning of the iteration so then could
                indicate some planning.
     */
     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('§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/select_all_activities_with_substrings_and_last_modified_date_as_end_date.sql

1

/* Select all activities with substrings and with last modified date as end date */

2

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;

src/main/webapp/queries/select_all_iterations_that_contains_wikipages_with_substrings.sql

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*/

2

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/select_all_iterations_with_lengths_without_first_and_last.sql

1

/* !Global variables must be set for proper behavior! Select all iterations with their length without first and last iterations */

2

select datediff(endDate, startDate) as `iterationLength` from iteration where iteration.superProjectId = @projectId and iteration.id != @idOfFirstIteration and iteration.id != @idOfLastIteration order by iteration.name;

src/main/webapp/queries/select_all_iterations_with_sum_estimated_and_spent_time.sql

1

/* Select all activities for each iteration and sum estimated time and spent time */

2

select iterationName, sum(estimatedTime) as 'estimatedTime', sum(spentTime) as 'spentTime', sum(spentTime)/sum(estimatedTime) as 'timeDivision',  abs(1-(sum(spentTime)/sum(estimatedTime))) as 'deviation' from workUnitView where projectid = @projectId and iterationName is not null group by iterationName order by iterationName;

src/main/webapp/queries/select_all_wikipages_that_is_updated_in_iteration.sql

1

/* Select all wikipages that were created or updated in iteration and have name with retr or revi*/

2

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('§0§') or lower(artifactView.name) like lower('§1§') or lower(artifactView.name) like lower('§2§') or lower(artifactView.name) like lower('§3§') or lower(artifactView.name) like lower('§4§') or lower(artifactView.name) like lower('§5§') or lower(artifactView.name) like lower('§6§') or lower(artifactView.name) like lower('§7§') or lower(artifactView.name) like lower('§8§') or lower(artifactView.name) like lower('§9§')) group by iteration.id order by iteration.name;

src/main/webapp/queries/select_average_iterations_length.sql
	1	/* Average iteration length */
	2	select avg(abs(dateDiff(iteration.endDate, iteration.startDate))) as 'averageIterationLength' from iteration where superProjectId = @projectId;

src/main/webapp/queries/select_iterations_with_substrings.sql

1

/* Select all iteration with detected retrospective activities */

2

select iterationName as 'iterationName', count(name) as 'numberOfIssues' 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§')) group by iterationName;

src/main/webapp/queries/select_number_of_activities_and_wiki_pages_with_substrings.sql

1

/* !Global variables must be set for proper behavior.! Select number of activities and wiki pages with given substrings and for given projects. */

2

select @projectId as `projectId`, @numberOfActivitiesWithSubstrings as `numberOfActivitiesWithSubstrings`, @numberOfWikiPagesWithSubstrings as `numberOfWikiPagesWithSubstrings`;

src/main/webapp/queries/select_number_of_iterations.sql
	1	/* Number of iterations for given project */
	2	select COUNT(id) as 'numberOfIterations' from iteration where superProjectId = @projectId and name like '%itera%';

src/main/webapp/queries/select_number_of_iterations_with_substrings_in_activity.sql

1

/* Select number of iterations which with substring in activity */

2

select count(*) over () as 'totalCountOfIterationsWithSubstringsInActivity' 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;

src/main/webapp/queries/select_project_end_date.sql
	1	/* Get project end date */
	2	select endDate as 'projectEndDate' from iteration where superProjectId = @projectId order by endDate desc limit 1;

src/main/webapp/queries/select_project_start_date.sql
	1	/* Get project start date */
	2	select startDate as 'projectStartDate' from iteration where superProjectId = @projectId order by startDate limit 1;

src/main/webapp/queries/select_statistics_for_given_project.sql

1

/* !Global variables must be set for proper behavior! Show number of wiki pages, number */

2

select @projectId as `projectId`, @numberOfWikiPagesWithSubstrings as `numberOfWikiPagesWithSubstrings`, @numberOfActivitiesWithSubstrings as `numberOfActivitiesWithSubstrings`, @averageLengthOfIssueDescription as `averageLengthOfIssueDescription`;

src/main/webapp/queries/set_average_length_of_activities_description.sql
	1	/* Count average length of issues description */
	2	set @averageLengthOfIssueDescription = (select AVG(CHAR_LENGTH(workUnitView.description)) from workUnitView where workUnitView.projectId = @projectId);

src/main/webapp/queries/set_first_iteration_id.sql
	1	/* Id of first iteration */
	2	set @idOfFirstIteration = (select id from iteration where iteration.superProjectId = @projectId order by name limit 1);

src/main/webapp/queries/set_first_iteration_start_date.sql

1

/* set first iteration start date to the global values named @firstIterationStartDate */

2

set @firstIterationStartDate = (select startDate from iteration where superProjectId = @projectId ORDER BY startDate LIMIT 1 offset 0);

src/main/webapp/queries/set_last_iteration_id.sql
	1	/* Id of last iteration */
	2	set @idOfLastIteration = (select id from iteration where iteration.superProjectId = @projectId order by name desc limit 1);

src/main/webapp/queries/set_number_of_activities_with_substrings.sql

1

/* Select number of activities with given substrings and set it to the global value named @numberOfActivitiesWithSubstrings */

2

set @numberOfActivitiesWithSubstrings = (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));

src/main/webapp/queries/set_number_of_wiki_pages_with_substrings.sql

1

/* Select number of wiki pages with given substrings and set it to the global value named @numberOfWikiPagesWithSubstrings */

2

set @numberOfWikiPagesWithSubstrings = (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§')));

src/main/webapp/queries/set_project_id.sql
	1	/* Init project id */
	2	set @projectId = ?;

src/main/webapp/queries/set_second_iteration_start_date.sql

1

/* set second iteration start date to the global values named @firstIterationStartDate */

2

set @secondIterationStartDate = (select startDate from iteration where superProjectId = @projectId ORDER BY startDate LIMIT 1 offset 1);

     /*
     Anti-pattern name: Specify nothing
     Description: The specification is not done intentionally. Programmers are
                  expected to work better without written specifications.
     Detection: No specification artifact. There is no issue that will have something
                like "DSP, SPECIFICATIONS, ETC." in the title. Initially, meetings
                with the customer should be more frequent to clarify the project framework.
                 No entry in the wiki with the project specification.
     */
     /* 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('§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('§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 */
     select @projectId as `projectId`, @numberOfWikiPages as `numberOfWikiPages`, @numberOfActivitiesForSpecification as `numberOfActivitiesForSpecification`, @averageLengthOfIssueDescription as `averageLengthOfIssueDescription`;

     /*
     Anti-pattern name: Too Long Sprint
     Description: Iterations too long. (ideal iteration length is about 1-2 weeks,
                  maximum 3 weeks). It could also be detected here if the length
                  of the iteration does not change often (It can change at the
                  beginning and at the end of the project, but it should not
                  change in the already started project).
     Detection: Detect the beginning and end of the iteration and what is
                the interval between these time points. We should exclude
                the initial and final iterations, as they could skew the result.
     */
     /* Init project id */
     set @projectId = ?;
     /* Exclude first and last iteration? */
     set @excludeFirstAndLastIteration = true;
     /* Id of first iteration */
     set @idOfFirstIteration = (select id from iteration where iteration.superProjectId = @projectId order by name limit 1);
     /* Id of last iteration */
     set @idOfLastIteration = (select id from iteration where iteration.superProjectId = @projectId order by name desc limit 1);
     /* Select all iterations with their length */
     select datediff(endDate, startDate) as `iterationLength` from iteration where iteration.superProjectId = @projectId and iteration.id != if(@excludeFirstAndLastIteration = true, @idOfFirstIteration, -1) and iteration.id != if(@excludeFirstAndLastIteration = true, @idOfLastIteration, -1) order by iteration.name;

     /*
     Anti-pattern name: Varying Sprint Length
     Description: The length of the sprint changes very often.
                  It is clear that iterations will be different
                  lengths at the beginning and end of the project,
                  but the length of the sprint should not change
                  during the project.
     Detection: Detect sprint lengths throughout the project
                and see if they are too different. Possibility to
                eliminate the first and last sprint. It could be
                otherwise long. Detection would be similar to
                Too Long sprint anti-pattern.
     */
     /* Init project id */
     set @projectId = ?;
     /* Exclude first and last iteration? */
     set @excludeFirstAndLastIteration = true;
     /* Id of first iteration */
     set @idOfFirstIteration = (select id from iteration where iteration.superProjectId = @projectId order by name limit 1);
     /* Id of last iteration */
     set @idOfLastIteration = (select id from iteration where iteration.superProjectId = @projectId order by name desc limit 1);
     /* Select all iterations with their length */
     select datediff(endDate, startDate) as `iterationLength` from iteration where iteration.superProjectId = @projectId and iteration.id != if(@excludeFirstAndLastIteration = true, @idOfFirstIteration, -1) and iteration.id != if(@excludeFirstAndLastIteration = true, @idOfLastIteration, -1) order by iteration.name;

Projekt

Obecné

Profil

ASWI - Pokročilé softwarové inženýrství » ASWI 2023 » Vylepšení funkcí a rozhraní frameworku SPADe (Pícha) - PRO_grammers.

Revize e15a6649

Přidáno uživatelem Ondřej Váně před více než 3 roky(ů)