R bugzilla review

Introduction

What is the bugRzilla Package?

The bugRzilla is an R package that helps the user to interact with the Bugzilla through an API. Bugzilla, a bug-tracking system that enterprise-class piece of software that tracks millions of bugs and issues for thousands of organizations around the world.

The source code for this package is available in the bugRzilla GitHub repository.

About the bugRzilla Google Summer of Code Project:-

bugRzilla is a package to interact with a bugzilla API and specially with R bugzilla. The goal of the project is to help users to submit issues to R Bugzilla. The Project can be found at GSoC’21 project

Explore the issues and bugs on the R Bugzilla to make the submission from bugRzilla better. It might help to identify useful patterns for R core or report the status of the R Bugzilla.

The source code for this report of bugRzilla is available in the bugzilla_viz GitHub repository.

Set up the R Bugzilla Database on your local system

Download SQL and MySQL Workbench

To install SQL on Ubuntu one can refer a blog post by digitalocean. To install MySQL workbench on Ubuntu one can refer a blog post by linuxhint

Download R bugzilla data

1. The R Core have made a dump of the R Bugzilla database on 25/03/2021 which is available for analysis can be downloaded from link.
2. The downloaded data is a zip file so make sure you unzip the file by directly using extract here option to the folder you desire before dumping the file which will have an extension .sql (eg: R-bugs.sql).

Dump downloaded R bugzilla to MySQL workbench.

After considering this open your Terminal and run the command: source <Path>/R-bugs.sql; For Example,

1. At the command prompt, run the following command to launch the mysql shell and enter it as the root user: mysql -u root -p
2. When you’re prompted for a password, enter the one that you set at installation time, or if you haven’t set one, press Enter to submit no password. The following mysql shell prompt should appear: mysql>
3. In MySQL, I used this to dump the data in the empty database:
   • Create an empty database: create database bugRzilla;
   • To check whether the database is created or not use: show databases;
   • Once an empty database is created then to dump the SQL data in the database use: source /home/data/Documents/GSOC/R-bugs.sql;

   • To check the database dump is imported correctly: show tables;

     mysql> show tables;
     +---------------------+
     | Tables_in_bugRzilla |
     +---------------------+
     | attachments         |
     | bugs                |
     | bugs_activity       |
     | bugs_fulltext       |
     | bugs_mod            |
     | components          |
     | longdescs           |
     +---------------------+
     7 rows in set (0.00 sec)

bugRzilla Analysis

For the connection to the database, I’m using the dplyr package, it supports connections to the widely-used open source databases like MySQL.

The package used for the analysis:

# loading packages
library(dplyr, quietly = TRUE)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(dbplyr, quietly = TRUE)

## 
## Attaching package: 'dbplyr'

## The following objects are masked from 'package:dplyr':
## 
##     ident, sql

library(RMySQL, quietly = TRUE)
library(DBI, quietly = TRUE)
library(DT, quietly = TRUE)
library(tidyverse, quietly = TRUE)

## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──

## ✓ ggplot2 3.3.5     ✓ purrr   0.3.4
## ✓ tibble  3.1.2     ✓ stringr 1.4.0
## ✓ tidyr   1.1.3     ✓ forcats 0.5.1
## ✓ readr   1.4.0

## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## x dplyr::filter() masks stats::filter()
## x dbplyr::ident() masks dplyr::ident()
## x dplyr::lag()    masks stats::lag()
## x dbplyr::sql()   masks dplyr::sql()

library(skimr, quietly = TRUE)
library(ggplot2, quietly = TRUE)
library(plotly, quietly = TRUE)

## 
## Attaching package: 'plotly'

## The following object is masked from 'package:ggplot2':
## 
##     last_plot

## The following object is masked from 'package:stats':
## 
##     filter

## The following object is masked from 'package:graphics':
## 
##     layout

library(padr, quietly = TRUE)

Connect bugRzilla SQL Database with R

# Connecting R with MySQL
con <- dbConnect(
    MySQL(),
    dbname='bugRzilla', # change the database name to your database name
    username='root', # change the username to your username
    password='1204', # update your password
    host='localhost',
    port=3306)

#  Accessing Tables names from the Database
DBI::dbListTables(con)

## [1] "attachments"   "bugs"          "bugs_activity" "bugs_fulltext"
## [5] "bugs_mod"      "components"    "longdescs"

Data Exploration of Bugs Table from the Database

bugs_df <- tbl(con, "bugs")

## Warning in .local(conn, statement, ...): Decimal MySQL column 24 imported as
## numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 25 imported as
## numeric

#for quick view of the datatypes and the structure of data
skim(bugs_df)

## Warning in .local(conn, statement, ...): Decimal MySQL column 24 imported as
## numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 25 imported as
## numeric

Data summary

Name	bugs_df
Number of rows	7042
Number of columns	27
_______________________
Column type frequency:
character	15
numeric	12
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
bug_file_loc	0	1	0	136	6990	51	0
bug_severity	0	1	5	11	0	7	0
bug_status	0	1	3	11	0	8	0
creation_ts	0	1	19	19	0	7028	0
delta_ts	0	1	19	19	0	6308	0
short_desc	0	1	1	255	0	6923	0
op_sys	0	1	3	15	0	22	0
priority	0	1	2	2	0	5	0
rep_platform	0	1	3	25	0	7	0
version	0	1	3	15	0	43	0
resolution	0	1	0	19	564	12	0
target_milestone	0	1	3	3	0	1	0
status_whiteboard	0	1	0	0	7042	1	0
lastdiffed	0	1	19	19	0	6324	0
deadline	7008	0	19	19	0	30	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
bug_id	0	1	10817.89	6189.36	1	5686.75	14101.5	16048.75	18097	▃▁▂▂▇
assigned_to	0	1	17.48	120.26	1	2.00	5.0	16.00	2787	▇▁▁▁▁
product_id	0	1	2.00	0.00	2	2.00	2.0	2.00	2	▁▁▇▁▁
reporter	0	1	685.69	1003.34	1	2.00	2.0	1056.00	3432	▇▂▁▁▁
component_id	0	1	9.84	5.20	2	6.00	9.0	15.00	19	▇▇▆▃▆
qa_contact	7042	0	NaN	NA	NA	NA	NA	NA	NA
votes	0	1	0.00	0.00	0	0.00	0.0	0.00	0	▁▁▇▁▁
everconfirmed	0	1	0.83	0.38	0	1.00	1.0	1.00	1	▂▁▁▁▇
reporter_accessible	0	1	1.00	0.00	1	1.00	1.0	1.00	1	▁▁▇▁▁
cclist_accessible	0	1	1.00	0.00	1	1.00	1.0	1.00	1	▁▁▇▁▁
estimated_time	0	1	0.10	6.60	0	0.00	0.0	0.00	552	▇▁▁▁▁
remaining_time	0	1	0.00	0.00	0	0.00	0.0	0.00	0	▁▁▇▁▁

From the above table we can conclude that the few of the columns are having wrong datatype like:

1. creation_ts
2. delta_ts
3. lastdiffed
4. estimated_time
5. remaining_time
6. deadline

Note:The Column estimated_time and remaining_time only contains the integer value. So, It can’t be transformed to Date format datatype. Also there are columns which are empty or they have same value, so it is not interesting for further analysis:

1. target_milestone
2. qa_contact
3. status_whiteboard

# Converting `bugs_df` to `dataframe`
bugs_df <- as.data.frame(bugs_df)

## Warning in .local(conn, statement, ...): Decimal MySQL column 24 imported as
## numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 25 imported as
## numeric

Cleaning the data

First steps, check the data and prepare it for what we want:

#converting the required fields in the correct datatype format
bugs_df <- bugs_df %>%
    mutate_at(vars("creation_ts", "delta_ts", "lastdiffed", "deadline"), as.Date)
# Taking the columns which are useful
bugs_df <- bugs_df %>%
    select("bug_id", "bug_severity", "bug_status", "creation_ts", "delta_ts",
           "op_sys", "priority", "resolution", "component_id", "version", 
           "lastdiffed", "deadline")
#for quick view of the datatypes and the structure of data
skim(bugs_df)

Data summary

Name	bugs_df
Number of rows	7042
Number of columns	12
_______________________
Column type frequency:
character	6
Date	4
numeric	2
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
bug_severity	0	1	5	11	0	7	0
bug_status	0	1	3	11	0	8	0
op_sys	0	1	3	15	0	22	0
priority	0	1	2	2	0	5	0
resolution	0	1	0	19	564	12	0
version	0	1	3	15	0	43	0

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
creation_ts	14	1	1998-08-07	2021-05-07	2009-12-08	4274
delta_ts	30	1	1998-08-09	2021-05-08	2012-07-20	3562
lastdiffed	14	1	1998-08-07	2021-05-08	2012-07-10	3565
deadline	7008	0	2010-04-23	2015-04-23	2013-11-09	30

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
bug_id	0	1	10817.89	6189.36	1	5686.75	14101.5	16048.75	18097	▃▁▂▂▇
component_id	0	1	9.84	5.20	2	6.00	9.0	15.00	19	▇▇▆▃▆

#showing the `datatable`
datatable(head(bugs_df, 5), options = list(scrollX = TRUE))

About the Bugs Data used for Analysis

I’ve taken the 12 columns under consideration to Analyse the Data. The brief description about the columns as follows:

1. bug_id: Unique numeric identifier for bug.
2. bug_severity: How severe the bug is, e.g. enhancement, critical, etc.
3. bug_status: Current status, e.g. NEW, RESOLVED, etc.
4. creation_ts: When bug was filed.
5. delta_ts: The timestamp of the last update on the bug. This includes updates to some related tables (e.g. “longdescs”).
6. op_sys: Operating system bug was seen on, e.g. Windows Vista, Linux, etc.
7. priority: The priority of the bug (P1 = most urgent, P5 = least urgent).
8. resolution: The resolution, if the bug is in a closed state, e.g. FIXED, DUPLICATE, etc.
9. component_id: Numeric ids of the components.
10. version: Version of software in which bug is seen.
11. lastdiffed: The time at which information about this bug changing was last emailed to the cc list.
12. deadline: Date by which bug must be fixed.

Visualizations

bug_created <- bugs_df %>%
    ggplot(aes(x = creation_ts, y = bug_id)) +
    geom_line(color = "darkorchid4") +
    labs(title = "Bug Creation",
           y = "Bug ID",
           x = "Date") +
    theme_bw(base_size = 15)
ggplotly(bug_created)

The Bug Creation Time-series graph shows that which bug_id was filed in which month and year. Frome the graph, we can conclude that in which year the most bugs are filed and when one will zoom the graphs, one can see on which date which bug was filed. The most of the Bugs are filled in the month of January and July. There are some unusual blips like the bug_id = 1, 1605, 1261 is created at 15/02/2010, 15/01/2003, 28/05/2001 respectively.

last_modified <- bugs_df %>%
    ggplot(aes(x = lastdiffed, y = bug_id)) +
    geom_line() +
    labs(title = "Bug Last Modified",
         y = "Bug ID",
         x = "Date") +
    theme_bw(base_size = 15)
ggplotly(last_modified)

The Bug Last Modified Time-series graph shows that which bug_id was the last update. Most of the bugs are last updated in the month of January, March, April, and July and in the year from 2014 to 2016 most bugs are modified and in 2019 to 2020 most bugs are filed. we can see that up to 2010 bug IDs were generally last modified in the same order as their creation date. After that, it seems there was more effort to go back to old bugs these issue some of the issue like bug_id = 997 which was created in the year 2001 was fixed in the year 2019. similarly the bug_id = 412 refers to the issue which is assigned to the wontfix category the bug was created in 2000 but was last modified on 2020.

# Plotting the Time Series graph with the bug_id and delta_ts
last_modified_graph <- bugs_df %>%
    ggplot(aes(x = delta_ts, y = bug_id)) +
    geom_line() +
    labs(title = "Last emailed to the cc list",
           y = "Bug ID",
           x = "Date") + theme_bw(base_size = 15)
ggplotly(last_modified_graph)

The Bug changing was last emailed to the cc list Time-series graph shows that which bug_id was the last emailed to the cc list. Most of the bugs are last updated in the month of January,March, April, and July.

Resolution_graph <- bugs_df %>%
    filter(!resolution == "FIXED") %>%
    ggplot(aes(x = resolution)) +
    geom_bar() +
    scale_x_discrete(guide = guide_axis(n.dodge = 5)) +
    labs(
        title = "Bug Resolution Bar graph with Bug Count",
        x = "Resolution",
        y = "Bug Count"
    ) + coord_flip()
ggplotly(Resolution_graph)

The Resolution bar-graph shows which bug_id belongs to which resolution category, if the bug is in a closed state, e.g. FIXED, DUPLICATE, etc. As we can conclude, that most bugs belong to the fixed category of the resolution.

Status_graph <- bugs_df %>%
    filter(!bug_status == "CLOSED") %>%
    ggplot(aes(x = bug_status)) +
    geom_bar() +
    scale_x_discrete(guide = guide_axis(n.dodge = 4)) +
    labs(
        title = "Bug Status Bar graph with Bug Count",
        x = "Bug Status",
        y = "Bug Count"
    )
ggplotly(Status_graph)

The bug_status bar-graph shows which bug_id belongs to which category of bug_status, e.g. NEW, RESOLVED, etc. As we can conclude, that most bugs belong to the closed category of the bug_status.

Severity_graph <- ggplot(bugs_df,aes(x = bug_severity)) +
    geom_bar() +
    scale_x_discrete(guide = guide_axis(n.dodge = 5)) +
    labs(
        title = "Bug Severity Bar graph with Bug Count",
        x = "Bug Severity",
        y = "Bug Count"
    )
ggplotly(Severity_graph)

The bug_severity bar-graph shows which bug_id belongs to which category of bug_severity. Most of the bugs which are filed are normal, some of the bugs that are filled under enhancements i.e. some new feature requested by the people that people wish it would be included on the R code so they can be retested for the improvement and some minor and major features, and a very few bugs are filed under the blocker category.

Data Exploration of bugs and Attachments Table from the Database

bugs_attach_df <- tbl(con, "attachments")
# Converting `bugs_attach_df` to `dataframe`
bugs_attach_df <- as.data.frame(bugs_attach_df)
#for quick view of the datatypes and the structure of data
skim(bugs_attach_df)

Data summary

Name	bugs_attach_df
Number of rows	1823
Number of columns	11
_______________________
Column type frequency:
character	5
numeric	6
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
creation_ts	0	1	19	19	0	1771	0
modification_time	0	1	19	19	0	1630	0
description	0	1	0	174	187	1485	0
mimetype	0	1	8	71	0	69	0
filename	0	1	3	70	0	1522	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
attach_id	0	1	1876.50	572.77	1	1362.5	1895	2380.5	2838	▁▃▇▇▇
bug_id	0	1	15351.15	3661.05	1	15004.0	16413	17369.0	18097	▁▁▁▁▇
ispatch	0	1	0.43	0.49	0	0.0	0	1.0	1	▇▁▁▁▆
submitter_id	0	1	1313.33	1104.28	1	317.0	979	2143.0	3432	▇▆▂▃▃
isobsolete	0	1	0.12	0.32	0	0.0	0	0.0	1	▇▁▁▁▁
isprivate	0	1	0.00	0.00	0	0.0	0	0.0	0	▁▁▇▁▁

Cleaning attachments Data

bugs_attach_df <- bugs_attach_df %>%
    mutate_at(vars("creation_ts", "modification_time"), as.Date) %>%
    mutate_at(vars("isobsolete", "isprivate", "ispatch"), as.logical)

Joining the bugs and attachments tables

#joining the `attachments` and `bugs` table
baa <- merge(bugs_attach_df, bugs_df, by = intersect(names(bugs_attach_df), names(bugs_df)), all = TRUE)

# Created four columns `creation_month`, `creation_year` and `lastdiffed_month`, `lastdiffed_year` to find in which month and year a bug is created and modified respectively.
baa <- baa %>%
    mutate(creation_month = format(creation_ts, "%m"), 
           creation_year = format(creation_ts, "%Y"), 
           lastdiffed_month = format(lastdiffed, "%m"), 
           lastdiffed_year = format(lastdiffed, "%Y")) %>%
    group_by(creation_month, creation_year)

#showing the `datatable`
datatable(head(baa, 5), options = list(scrollX = TRUE))

About the bugs_activity and attachments Data Used for Analysis

I’ve taken the 15 columns under consideration to Analyse the Data. The brief description about the columns as follows:

1. bug_id: Unique numeric identifier for bug.
2. attach_id: Unique numeric identifier for attachment.
3. creation_ts: When bug was filed.
4. modification_time: The date and time on which the attachment was last modified.
5. description: Text describing the attachment.
6. mimetype: Content type of the attachment like text/plain or image/png.
7. ispatch: Whether attachment is a patch.
8. filename :Path-less file-name of attachment.
9. submitter_id: Unique numeric identifier for who submitted the bug.
10. isobsolete: Whether attachment is marked obsolete.
11. isprivate: TRUE if the attachment should be private and FALSE if the attachment should be public.
12. creation_month: The month in which the bug is created.
13. creation_year: The year in which the bug is created.
14. lastdiffed_month: The month in which the bug is last modified.
15. lastdiffed_year: The year in which the bug is last modified.

Visualizations

#Counting number of bugs per month in an year
bugs_counts <- baa %>%
    arrange(bug_id) %>%
    count(creation_year)

skim(head(bugs_counts))

Data summary

Name	head(bugs_counts)
Number of rows	6
Number of columns	3
_______________________
Column type frequency:
numeric	1
________________________
Group variables	creation_month, creation_year

Variable type: numeric

skim_variable	creation_month	creation_year	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
n	01	1999	0	1	17	NA	17	17	17	17	17	▁▁▇▁▁
n	01	2000	0	1	13	NA	13	13	13	13	13	▁▁▇▁▁
n	01	2001	0	1	30	NA	30	30	30	30	30	▁▁▇▁▁
n	01	2002	0	1	41	NA	41	41	41	41	41	▁▁▇▁▁
n	01	2003	0	1	30	NA	30	30	30	30	30	▁▁▇▁▁
n	01	2004	0	1	21	NA	21	21	21	21	21	▁▁▇▁▁

Note: Here only I’ve shown the overview of only the 6 rows of bugs_counts Since the whole summary of the data is very large.

#filtering the data where resolution is Duplicate
res_dupli <- baa %>%
    filter(resolution == "DUPLICATE" & bug_status == "CLOSED")

# plotting graph with creation year where resolution is Duplicate
duplicate_year <- ggplot(res_dupli) +
    geom_bar(aes(x = creation_year)) +
    labs(
        title = "Year in which Duplicate Bugs are Filed",
        x = "Year",
        y = "Bug_Count"
    )
ggplotly(duplicate_year)

The above Visualization is about the year in which is bugs are filed where resolution is Duplicate. From the graph, we can see that the frequency of Duplicate bugs increased from one or two per year in 2006-08 to a peak in 2012 of 11 per year, but since 2017 has been less than 4 per year. From this we can conclude that duplicate bugs are not a big cause for concern as the number per year is so small.

#filtering the data where resolution is Fixed
res_fixed <- baa %>%
    filter(resolution == "FIXED" & bug_status == "CLOSED")

# plotting graph with last modified year where resolution is Fixed
fixed_year_graph <- ggplot(res_fixed) +
    geom_bar(aes(x = lastdiffed_year)) +
    labs(
        title = "Year in which fixed bugs are last modified",
        x = "Year",
        y = "Bug_Count"
    ) +
    coord_flip()
ggplotly(fixed_year_graph)

The above Visualization is about the year in which is bugs are last modified where resolution is Fixed and their status is closed. From the graph, we can see that the most wast last modified in the year 2002 having a bug count of 328 and In the year, 2021 47 bugs are fixed and closed.

# plotting graph with creation year where resolution is Fixed
fixed_closed_month_graph <- ggplot(res_fixed) +
    geom_bar(aes(x = lastdiffed_month)) +
    labs(
        title = "Month in which fixed and closed bugs are last modified",
        x = "Month",
        y = "Bug_Count"
    )
ggplotly(fixed_closed_month_graph)

The bar graph is about the month in which is bugs are last modified where the resolution is Fixed. From the graph, we can see that the most wast last modified in the month December having a bug count of 559 and in the month of September having a bug count of 228 are least modified. This graph is from the year 1998 to 2021.

res_invalid <- baa %>%
    filter(resolution == "INVALID" & bug_status == "CLOSED")

invaild_year_graph <- ggplot(res_invalid) +
    geom_bar(aes(x = creation_year)) +
    labs(
        title = "Year in which INVALID Bugs are Filed",
        x = "Year",
        y = "Bug_Count"
    ) + coord_flip()
ggplotly(invaild_year_graph)

The bar graph refers to the Creation of Invalid bugs. In the year, 1998 the total of 63 Invalid bugs are created which are least, and in the year 2013 a total of 431 bugs are filed which are most.

priority_graph <- baa %>%
    ggplot(aes(x = creation_year, y = bug_id)) +
    geom_point() +
    facet_wrap( ~priority) +
    labs(title = "Bugs created year with their priorities",
         y = "Bug ID",
         x = "Date") + theme_bw(base_size = 9) +
    coord_flip()
ggplotly(priority_graph)

The Lattice plot gives insight about the bugs when they are created and under which priority they fall like from the above plot we can conclude that the majority of the bugs are filed under the P5 which is having the least priority.

Data Exploration of all the tables in the database

Data Exploration of bugs_mod Table from the Database

bugs_mod_df <- tbl(con, "bugs_mod")
# Converting `bugs_mod_df to `dataframe`
bugs_mod_df <- as.data.frame(bugs_mod_df)
#for quick view of the datatypes and the structure of data
skim(bugs_mod_df)

Data summary

Name	bugs_mod_df
Number of rows	7042
Number of columns	28
_______________________
Column type frequency:
character	18
numeric	10
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
row_names	0	1.00	1	4	0	7042	0
bug_file_loc	7042	0.00	NA	NA	0	0	0
bug_severity	0	1.00	5	11	0	7	0
bug_status	0	1.00	3	11	0	8	0
creation_ts	14	1.00	10	10	0	4274	0
delta_ts	7042	0.00	NA	NA	0	0	0
short_desc	0	1.00	1	255	0	6923	0
op_sys	7042	0.00	NA	NA	0	0	0
priority	0	1.00	2	2	0	5	0
rep_platform	0	1.00	3	25	0	7	0
version	0	1.00	3	15	0	43	0
resolution	564	0.92	4	19	0	11	0
target_milestone	0	1.00	3	3	0	1	0
status_whiteboard	7042	0.00	NA	NA	0	0	0
lastdiffed	7042	0.00	NA	NA	0	0	0
estimated_time	0	1.00	4	6	0	19	0
remaining_time	0	1.00	4	4	0	1	0
deadline	7008	0.00	10	10	0	30	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
bug_id	0	1	10817.89	6189.36	1	5686.75	14101.5	16048.75	18097	▃▁▂▂▇
assigned_to	0	1	17.48	120.26	1	2.00	5.0	16.00	2787	▇▁▁▁▁
product_id	0	1	2.00	0.00	2	2.00	2.0	2.00	2	▁▁▇▁▁
reporter	0	1	685.69	1003.34	1	2.00	2.0	1056.00	3432	▇▂▁▁▁
component_id	0	1	9.84	5.20	2	6.00	9.0	15.00	19	▇▇▆▃▆
qa_contact	7042	0	NaN	NA	NA	NA	NA	NA	NA
votes	0	1	0.00	0.00	0	0.00	0.0	0.00	0	▁▁▇▁▁
everconfirmed	0	1	0.83	0.38	0	1.00	1.0	1.00	1	▂▁▁▁▇
reporter_accessible	0	1	1.00	0.00	1	1.00	1.0	1.00	1	▁▁▇▁▁
cclist_accessible	0	1	1.00	0.00	1	1.00	1.0	1.00	1	▁▁▇▁▁

#showing the baa i.e `bugs_mod_df` table in the `datatable`
datatable(head(bugs_mod_df, 5), options = list(scrollX = TRUE))

Data Exploration of longdescs Table from the Database

longdescs_df <- tbl(con, "longdescs")

## Warning in .local(conn, statement, ...): Decimal MySQL column 4 imported as
## numeric

# Converting `longdescs_df` to `dataframe`
longdescs_df <- as.data.frame(longdescs_df)

## Warning in .local(conn, statement, ...): Decimal MySQL column 4 imported as
## numeric

#for quick view of the datatypes and the structure of data
skim(longdescs_df)

Data summary

Name	longdescs_df
Number of rows	26942
Number of columns	11
_______________________
Column type frequency:
character	3
numeric	8
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
bug_when	0	1.00	19	19	0	26270	0
thetext	0	1.00	0	422285	772	25588	0
extra_data	24966	0.07	1	5	0	1948	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
comment_id	0	1	83378.70	7986.99	1	76528.25	83263.5	90215.75	97284	▁▁▁▃▇
bug_id	0	1	10479.44	6260.77	1	4195.00	13361.0	16072.00	18097	▅▁▃▂▇
who	0	1	457.47	896.85	1	2.00	2.0	412.00	3432	▇▁▁▁▁
work_time	0	1	0.00	0.04	0	0.00	0.0	0.00	5	▇▁▁▁▁
isprivate	0	1	0.00	0.00	0	0.00	0.0	0.00	0	▁▁▇▁▁
already_wrapped	0	1	0.00	0.00	0	0.00	0.0	0.00	0	▁▁▇▁▁
type	0	1	0.35	1.26	0	0.00	0.0	0.00	6	▇▁▁▁▁
is_markdown	0	1	0.04	0.20	0	0.00	0.0	0.00	1	▇▁▁▁▁

#showing the baa i.e `longdescs_df` table in the `datatable`
datatable(head(longdescs_df, 5), options = list(scrollX = TRUE))

The brief description about the columns as follows:

1. comment_id: An integer comment ID.
2. bug_id: The ID of the bug that this comment is on.
3. who: who created the comment.
4. bug_when: When the bug was created.
5. work_time: Adds this many hours to the “Hours Worked” on the bug. If you are not in the time tracking group, this value will be ignored.
6. thetext: The actual text of the comment.
7. isprivate: true if this comment is private (only visible to a certain group called the “insidergroup”), false otherwise.
8. already_wrapped: If this comment is stored in the database word-wrapped, this will be 1. 0 otherwise.
9. type: The time at which information about this bug changing was last emailed to the cc list.
10. extra_data: If this comment is having any extra data in the database, this will be 1. 0 otherwise.
11. is_markdown: true if this comment needs Markdown processing; false otherwise.

From the longdescs_df data table, we can see that most of the columns containing the same value i.i 0 which makes it interesting for the analysis. There is only few columns which can be considered but they are also present in other data-tables for example, Comment_id, bug_id, who which is also submitter_id, bug_when which is also know creation_ts. So there is no use to make analysis on them again.

Data Exploration of bugs_activity Table from the Database

bugs_act_df <- tbl(con, "bugs_activity")
# Converting `longdescs_df` to `dataframe`
bugs_act_df <- as.data.frame(bugs_act_df)
#for quick view of the datatypes and the structure of data
skim(bugs_act_df)

Data summary

Name	bugs_act_df
Number of rows	15114
Number of columns	9
_______________________
Column type frequency:
character	3
numeric	6
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
bug_when	0	1	19	19	0	7290	0
added	0	1	0	133	142	400	0
removed	0	1	0	122	7656	374	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
bug_id	0	1.00	15286.06	2884.99	1	14762.25	15738.0	16894.00	18097	▁▁▁▁▇
attach_id	14880	0.02	2053.50	509.98	1	1711.25	2128.0	2420.50	2833	▁▁▅▇▇
who	0	1.00	462.89	892.91	1	6.00	18.0	308.00	3432	▇▁▁▁▁
fieldid	0	1.00	14.02	6.83	2	9.00	12.0	20.00	54	▇▃▁▁▁
comment_id	15086	0.00	92539.75	1993.95	89370	90952.75	91992.5	93529.25	96991	▆▇▅▂▂
id	0	1.00	8566.00	5187.40	1	3791.25	8723.0	13347.75	17143	▇▆▇▆▇

The brief description about the columns as follows:

1. fieldid: Unique numeric identifier for field
2. added: Values added, if any (comma-separated if multiple)
3. removed: `Values removed, if any (comma-separated if multiple)

From the bugs_activity data table, we can see that most of there is only few columns which can be considered but they are also present in other data-tables for example, bug_id, who which is also submitter_id, bug_when which is also know creation_ts. So there is no use to make analysis on them again.

Joining all the the data tables

#joining all the data tables
total_data <- merge(bugs_df, bugs_act_df, by = intersect(names(bugs_df), 
                                                         names(bugs_act_df)), all = TRUE) %>%
    merge(., bugs_attach_df, by = intersect(names(.), 
                                            names(bugs_attach_df)), all = TRUE) %>%
    merge(., bugs_mod_df, by = intersect(names(.), 
                                         names(bugs_mod_df)), all = TRUE)
# creating a creation_year column 
total_data$creation_year <- as.Date(cut(total_data$creation_ts, 
                                         breaks = "year"))
# creating a creation_month column
total_data$creation_month <- as.Date(cut(total_data$creation_ts, 
                                         breaks = "month"))
# creating a creation_week column
total_data$creation_week <- as.Date(cut(total_data$creation_ts, 
                                        breaks = "week", start.on.monday = FALSE))

# creating a lastdiffed_year column
total_data$lastdiffed_year <- as.Date(cut(total_data$lastdiffed, 
                                           breaks = "year"))
# creating a lastdiffed_year column
total_data$lastdiffed_month <- as.Date(cut(total_data$lastdiffed, 
                                           breaks = "month"))
# creating a lastdiffed_year column
total_data$lastdiffed_week <- as.Date(cut(total_data$lastdiffed, 
                                          breaks = "week", start.on.monday = FALSE))

# selecting required columns for the analysis
total_data <- total_data %>%
    select("bug_id", "creation_ts", "bug_severity", "bug_status", "delta_ts", "op_sys", "priority", "resolution", 
           "component_id", "version", "lastdiffed", "deadline", "attach_id", "who", "bug_when", "fieldid", "added", 
           "removed", "modification_time", "description", "mimetype", "ispatch", "filename", "submitter_id", 
           "isobsolete", "isprivate", "assigned_to", "product_id", "reporter", "creation_year", "creation_month", 
           "creation_week", "lastdiffed_year", "lastdiffed_month", "lastdiffed_week")

#counting total_creation_bug_count
# total_creation_bug_count <- total_data %>%
#     arrange(bug_id) %>%
#     count(creation_year)

# joining total_data and total_bug_count
# total_data <- merge(total_creation_bug_count, total_data, by = intersect(names(total_creation_bug_count), 
#                                                          names(total_data)), all = TRUE)

#for quick view of the datatypes and the structure of data
skim(total_data)

Data summary

Name	total_data
Number of rows	27199
Number of columns	35
_______________________
Column type frequency:
character	12
Date	11
logical	3
numeric	9
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
bug_severity	1697	0.94	5	11	0	7	0
bug_status	1697	0.94	3	11	0	8	0
op_sys	8739	0.68	3	15	0	22	0
priority	1697	0.94	2	2	0	5	0
resolution	2261	0.92	0	19	1251	12	0
version	1697	0.94	3	15	0	43	0
bug_when	12085	0.56	19	19	0	7290	0
added	12085	0.56	0	133	142	400	0
removed	12085	0.56	0	122	7656	374	0
description	25373	0.07	0	174	187	1485	0
mimetype	25373	0.07	8	71	0	69	0
filename	25373	0.07	3	70	0	1522	0

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
creation_ts	30	1.00	1998-08-07	2021-05-07	2012-12-31	4443
delta_ts	8770	0.68	1998-08-09	2021-05-08	2015-12-14	3562
lastdiffed	8753	0.68	1998-08-07	2021-05-08	2015-12-14	3565
deadline	27026	0.01	2010-04-23	2015-04-23	2013-11-08	30
modification_time	25375	0.07	1998-12-04	2021-05-07	2015-08-23	1093
creation_year	30	1.00	1998-01-01	2021-01-01	2012-01-01	24
creation_month	30	1.00	1998-08-01	2021-05-01	2012-12-01	274
creation_week	30	1.00	1998-08-02	2021-05-02	2012-12-30	1166
lastdiffed_year	8753	0.68	1998-01-01	2021-01-01	2015-01-01	24
lastdiffed_month	8753	0.68	1998-08-01	2021-05-01	2015-12-01	272
lastdiffed_week	8753	0.68	1998-08-02	2021-05-02	2015-12-13	1127

Variable type: logical

skim_variable	n_missing	complete_rate	mean	count
ispatch	25373	0.07	0.43	FAL: 1045, TRU: 781
isobsolete	25373	0.07	0.12	FAL: 1615, TRU: 211
isprivate	25373	0.07	0.00	FAL: 1826

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
bug_id	0	1.00	12992.23	5390.83	1	10414.0	15152.0	16615.5	18097	▂▁▁▁▇
component_id	1697	0.94	9.82	5.30	2	6.0	9.0	15.0	19	▇▇▆▃▇
attach_id	25268	0.07	1887.74	569.83	1	1378.5	1920.0	2380.5	2838	▁▃▇▇▇
who	12085	0.56	462.89	892.91	1	6.0	18.0	308.0	3432	▇▁▁▁▁
fieldid	12085	0.56	14.02	6.83	2	9.0	12.0	20.0	54	▇▃▁▁▁
submitter_id	25373	0.07	1314.49	1104.91	1	317.0	983.5	2148.0	3432	▇▆▂▃▃
assigned_to	20157	0.26	17.48	120.26	1	2.0	5.0	16.0	2787	▇▁▁▁▁
product_id	20157	0.26	2.00	0.00	2	2.0	2.0	2.0	2	▁▁▇▁▁
reporter	20157	0.26	685.69	1003.34	1	2.0	2.0	1056.0	3432	▇▂▁▁▁

# created bugs per year
created_year_df <- data.frame(total_data$bug_id,
                              total_data$creation_year)
created_year_df <- aggregate(total_data.bug_id~.,
                             created_year_df,
                             function(x) length(unique(x)))
colnames(created_year_df) <- c("creation_year", "Bug_count")

# last modified bugs per year
lastdiffed_year_df <- data.frame(total_data$bug_id,
                                 total_data$lastdiffed_year)
lastdiffed_year_df <- aggregate(total_data.bug_id~.,
                                lastdiffed_year_df,
                                function(x) length(unique(x)))
colnames(lastdiffed_year_df) <- c("lastdiffed_year", "Bug_count")

# created and last modified bugs per year in single dataframe
lastdiffed_year_df <- pad(lastdiffed_year_df)

## pad applied on the interval: year

cre_last_year <- data.frame(created_year_df,
                            lastdiffed_year_df)

skim(cre_last_year)

Data summary

Name	cre_last_year
Number of rows	24
Number of columns	4
_______________________
Column type frequency:
Date	2
numeric	2
________________________
Group variables	None

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
creation_year	0	1	1998-01-01	2021-01-01	2009-07-02	24
lastdiffed_year	0	1	1998-01-01	2021-01-01	2009-07-02	24

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
Bug_count	0	1	297.75	108.94	63	254.25	309.0	382.00	470	▂▅▇▇▇
Bug_count.1	0	1	292.83	174.42	60	190.25	281.5	304.25	818	▅▇▁▁▁

# created bugs per month
created_month_df <- data.frame(total_data$bug_id, 
                               total_data$creation_month)
created_month_df <- aggregate(total_data.bug_id~., 
                              created_month_df,
                              function(x) length(unique(x)))
colnames(created_month_df) <- c("creation_month", "Bug_count")

# last modified bugs per month
lastdiffed_month_df <- data.frame(total_data$bug_id, 
                                  total_data$lastdiffed_month)
lastdiffed_month_df <- aggregate(total_data.bug_id~., 
                                 lastdiffed_month_df, 
                                 function(x) length(unique(x)))
colnames(lastdiffed_month_df) <- c("lastdiffed_month", "Bug_count")

# created and last modified bugs per month in single dataframe
lastdiffed_month_df <- pad(lastdiffed_month_df)

## pad applied on the interval: month

cre_last_month <- data.frame(created_month_df, 
                             lastdiffed_month_df)

# last modified bugs per week
created_week_df <- data.frame(total_data$bug_id,
                              total_data$creation_week)
created_week_df <- aggregate(total_data.bug_id~.,
                             created_week_df,
                             function(x) length(unique(x)))
colnames(created_week_df) <- c("creation_week", "Bug_count")

# last modified bugs per week
lastdiffed_week_df <- data.frame(total_data$bug_id,
                                 total_data$lastdiffed_week)
lastdiffed_week_df <- aggregate(total_data.bug_id~.,
                                lastdiffed_week_df,
                                function(x) length(unique(x)))
colnames(lastdiffed_week_df) <- c("lastdiffed_week", "Bug_count")

# created and last modified bugs per week in single dataframe
created_week_df <- pad(created_week_df)

## pad applied on the interval: week

lastdiffed_week_df <- pad(lastdiffed_week_df)

## pad applied on the interval: week

cre_last_week <- data.frame(created_week_df,
                            lastdiffed_week_df)
skim(cre_last_week)

Data summary

Name	cre_last_week
Number of rows	1188
Number of columns	4
_______________________
Column type frequency:
Date	2
numeric	2
________________________
Group variables	None

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
creation_week	0	1	1998-08-02	2021-05-02	2009-12-16	1188
lastdiffed_week	0	1	1998-08-02	2021-05-02	2009-12-16	1188

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
Bug_count	22	0.98	6.29	3.49	1	4	6	8	32	▇▃▁▁▁
Bug_count.1	61	0.95	6.24	20.68	1	3	4	7	560	▇▁▁▁▁

Visualizations

cre_last_year_graph <- cre_last_year %>%
    ggplot() +
    geom_line(aes(x = creation_year, 
                  y = Bug_count,
                  colour = "creation_year")) +
    geom_line(aes(x = lastdiffed_year,
                  y = Bug_count.1, 
                  color = "lastdiffed_year")) +
    labs(
        title = "Year in which bugs are Created vs Last modified",
        x = "year",
        y = "Bug_Count"
    )

ggplotly(cre_last_year_graph)

The Time-series graph is the relationship between the bug_count and the year in which the bug is created and last_modified. The aim is to check in every year how many bugs are created and last modified. In the year 2015, a total of 470 bugs are created and 2015, 818 bugs are modified which is a sudden peak from the year 2014. From the year 2007 to 2013 the is a gradual peak but from year 2015 there is a sudden downfall in 2017 from 470 to 172 bugs are created and from 2015 to 2016 there is a sudden downfall from 818 to 287 and again 2018 to 2019 there is an increase in the modification of the bugs from 240 to 595.

cre_last_month_graph <- cre_last_month %>%
    ggplot() +
    geom_line(aes(x = creation_month, y = Bug_count, colour = "creation_month")) +
    geom_line(aes(x = lastdiffed_month, y = Bug_count.1, color="lastdiffed_month")) +
    labs(
        title = "Month in which are Created vs Last modified",
        x = "Month",
        y = "Bug_Count"
    )

ggplotly(cre_last_month_graph)

The Time-series graph is the relationship between the bug_count and the month in which the bug is created and last_modified. The aim is to check in every week how many bugs are created and last modified. From February 2007 to May 2016 there is increase in the bug creation every alternate month. From the month May 2016 to August 2016 there is no bug created which lead to the downfall in the bug creation. And from June 2019 to May 2020 the creation of the bugs increases. In the month, November 2015 to December 2015 and April 2019 to May 2019 there is a sudden increase in the modification of the bugs from 10 to 574 and 16 to 403. There is also a gap of from March 2010 to ’May 2010` in this period there is no bug modified.

cre_last_week_graph <- cre_last_week %>%
    ggplot() +
    geom_line(aes(x = creation_week, 
                  y = Bug_count, 
                  color="creation_week")) +
    geom_line(aes(x = lastdiffed_week, 
                  y = Bug_count.1,
                  color="lastdiffed_week")) +
    labs(
        title = "Week in which bugs are Created vs Last modified",
        x = "Week",
        y = "Bug_Count"
    )

ggplotly(cre_last_week_graph)

The Time-series graph is the relationship between the bug_count and the week in which the bug is created and last_modified. The aim is to check in every week how many bugs are created and last modified. In the first week of May 2016, a total of 32 bugs are created. In the second week of December 2015 and May 2015, a total of 560 and 395 bugs are modified. There are many weeks in which 0 bugs are created this happened mostly from the year 2017 to 2019 and 2010 to 2013.

Conclusion

In this project, I’ve visualized the bugRzilla database creating various visualization. The major graphs are the time-series graph which illustrates the relationship of bug_id, creation_ts, delta_ts, and last_modified to show when the bug was created, last modified & last email to the cc list, last modified respectively. Some time-series graphs can help us to see in which week, month, the year most bugs are created.

The bar graph plots the relationship between the bug_count, bug_status, bug_severity, resolution to check the status like closed, new, etc, how severe the bug is like whether the bug is critical, major, etc and the resolution of the bug-like if the bug is in a closed state, e.g. FIXED, DUPLICATE, etc.

The trend to be observed between the number of bugs reported on a certain epoch versus their respective resolution date is that the higher the number of bugs the longer is the patching period. Some of the resolutions are not assigned to any category like fixed, duplicate, etc. by assigning them a category, It will make the bug report much more efficient report to determine the status of the bug whether the bugs are fixed, Duplicate, etc. The weekly, monthly, and the annual plots support the aforementioned trend. While the bug count can be considered a prominent feature influencing the lastdiffed date, we cannot however ignore the other meta data comprising a bug report, e. g. the severity, number and nature of attachments, and some non quantifiable features like the effectiveness of bug description, and also, the number of bugs assigned to the same person also effects the time taken to patch the bugs.

dbDisconnect(con)

## [1] TRUE