DEV Community: Cecilia Baggini

SonarCloud vs LeftShift for automated code quality and security checks

Cecilia Baggini — Fri, 07 May 2021 16:49:09 +0000

Lately, I have been looking for automated tools to improve the quality and security of my apps. I have looked at two online apps for this, SonarCloud and LeftShift.
They are both intuitive and easy to setup; they can also be connected to GitHub and GitLab to examine repositories automatically every time a new commit is pushed.
ShiftLeft requires access to all repositories, while SonarCloud gives the option of only allowing access to some repositories.
ShiftLeft identifies security issues in the code and scored them according to severity.

SonarCloud provide a more extensive report, including three sections: reliability (are there any bugs?), maintainability (is the code clear and well-structured?) and security (are there any vulnerabilities?).

At first sight, SonarCode is more comprehensive, as it looks at the general code structure and how maintainable it is as well as security issues. However, when I analysed the same app with both tools, they suggested different security issues that should be fix. In conclusion, I will probably use them both as they provide complementary information.

How to create interactive HTML reports using R

Cecilia Baggini — Sat, 01 May 2021 09:52:21 +0000

In this post, I will explain how to create interactive HTML reports including widgets such as maps and interactive graphs using R. I assume that the reader is familiar with basic R. I will use two public APIs from the Environment Agency to download data, the Water Quality Archive for water quality data (phosphate in this example) and the Rainfall API to look for rainfall monitoring locations.
First, let’s load the packages we need and get the current date.

library(readr)
library(sf)
library(dplyr)
library(leaflet)
library(rgdal)
library(tidyr)
library(ggplot2)
library(plotly)
library(DT)
library(htmltools)

###Get Current Date and Time
now <- format(as.Date(Sys.Date()), '%d/%m/%Y')

Then, we can retrieve phosphate information for two sites (Powick and Leintwardine on the River Teme, West Midlands). The information to retrieve determinand and site codes are in the documentation linked above. Once we have retrieved the data, we can rename the columns to something a bit more sensible than the original output and create a new concentration column that converts concentrations below the limit of detection to half of the limit of detection. We also add latitude and longitude columns converting from UK-specific coordinates (eastings and northings).

# Set URL parameters
base_url = "https://environment.data.gov.uk/water-quality/data/measurement.csv?_limit=10000"
sampling_points = "&samplingPoint=MD-13598380&samplingPoint=MD-13624310"
start_date = "&startDate=2019-01-01"
detcode = "&determinand=0118"

# retrieve data from API and rename columns
all_records <- read_csv(paste0(base_url, sampling_points, detcode, start_date))
colnames(all_records) <- c("id", "smpt_url", "smpt_code", "smpt_name", "sample_datetime",           "determinand",                           "determinand_def", "detcode", "less_than", "concentration", "result_interpretation",
                           "unit", "smpt_type", "is_compliance", "sample_purpose", "easting", "northing")

# Add concentration and latitude/longitude columns
all_records$less_than_bool <- ifelse(is.na(all_records$less_than), FALSE, TRUE)
all_records$concentration_adj <- as.numeric(ifelse(all_records$less_than_bool == "TRUE", all_records$concentration/2, all_records$concentration))

latlon <- all_records %>%
  st_as_sf(coords = c("easting", "northing"), crs = 27700) %>%
  st_transform(4326) %>%
  st_coordinates() %>%
  as_tibble()
all_records$lat <- latlon$Y
all_records$lon <- latlon$X

We then create an HTML table with site names, codes and coordinates.

sites <- all_records %>% select(smpt_code, smpt_name, easting, 
                       northing, lat, lon) %>% 
  distinct(.keep_all = TRUE)

# Sites table
table_df <- sites %>% select(smpt_code, smpt_name, easting, northing) %>% 
  rename("Site code" = smpt_code,
         "Site name"= smpt_name,
         "Easting" = easting,
         "Northing" = northing)
sites_table <- datatable(table_df, width="100%", height="100%",
                         caption = htmltools::tags$caption( style = 'caption-side: top; 
                                                     text-align: center; color:black; 
                                                     font-size:200% ;','Monitoring sites') )

Then, we search for the closest rainfall monitoring site to each of the water quality sites and put this information in another table.

rainfall_data = data.frame()
for (i in 1:nrow(sites)) {
  lat <- sites$lat[i]
  lon <- sites$lon[i]
  rainfall_st <- read_csv(paste0("https://environment.data.gov.uk/flood-monitoring/id/stations.csv?parameter=rainfall&lat=",lat,"&long=",lon,"&dist=10"))
  if (nrow(rainfall_st) > 0) {
    rainfall_data <- rbind(rainfall_data, rainfall_st[1,])
  }
}
rainfall_data <- as.data.frame(rainfall_data)

# rainfall table
rainfall_table_df <- rainfall_data %>% select(notation, qualifier, easting, northing) %>% 
  rename("Site code" = notation,
         "Site type"= qualifier,
         "Easting" = easting,
         "Northing" = northing)
rainfall_table <- datatable(rainfall_table_df, width="100%", height="100%",
                            caption = htmltools::tags$caption( style = 'caption-side: top; 
                                                     text-align: center; color:black; 
                                                     font-size:200% ;','Rainfall gauges') )

At this point, we can create a map showing water quality and corresponding rainfall monitoring point.

pal <- colorFactor("Dark2", domain = all_records$smpt_code)
fillColor = ~pal(smpt_code)
# map
m <- leaflet() %>% 
  fitBounds(min(all_records$lon), min(all_records$lat), 
            max(all_records$lon), max(all_records$lat),
            options = list(padding = c(25,25))) %>%
  addTiles() %>%
  ## add sites
  addCircleMarkers(data = sites, ~lon, ~lat, 
                   color = "black",
                   weight = 0.5,
                   radius = 6,
                   layerId = ~smpt_code, 
                   fillOpacity = 0.7,
                   opacity = 1,
                   fillColor = fillColor,
                   popup = ~paste("<strong>Site Id :</strong>", smpt_code, "<br>",
                                  "<strong>Site Name :</strong>", smpt_name, "<br>")
  ) %>%
  ## add rainfall sites
  addCircleMarkers(data = rainfall_data, ~long, ~lat, 
                   color = "black",
                   weight = 0.5,
                   radius = 6,
                   layerId = ~notation, 
                   fillOpacity = 0.7,
                   opacity = 1,
                   fillColor = "blue",
                   popup = ~paste("<strong>Site Id :</strong>", notation, "<br>",
                                  "<strong>Site type :</strong>", qualifier, "<br>")
  )

Then we create an interactive Plotly graph that shows how phosphate changes in time at the two sites we selected.

# time series graph
ts <- ggplot(all_records, aes(sample_datetime, concentration_adj, colour = smpt_name)) +
  geom_line() +
  geom_point() +
  theme_bw() +
  labs(title = "Phosphate concentration by site",
       colour = "Site name") +
  xlab("Sample date") +
  ylab("Concentration (mg/l)") 
ts1 <- ggplotly(ts)

Finally, we assemble the two tables, the map and the graph and a title in a tagList and then save it to an HTML file.

# assemble final document
doc <- htmltools::tagList(
  h1(paste("Phosphate data summary - data extracted on ", now), style="text-align:center;"),
  div(m),
  div(div(sites_table, style="width:50%;"),
      div(rainfall_table, style="width:50%;margin-left:2em;"), style="margin:2em;display:flex;"),
  div(ts1)
)

htmltools::save_html(html = doc, file = "Phosphate_report.html")

And here is the final product!

Best practice for building a RESTful API

Cecilia Baggini — Fri, 23 Apr 2021 09:02:28 +0000

Lately, I have been learning how to use Node.js and Express to build APIs. There are no fixed standards to build APIs, but reading online I identified some common themes on how to build a functional and usable API. Some common themes were:

Don’t return plain text

Even if the response body is formatted like a JSON, if the response header is not set as application/json some clients may have issues parsing it.

Use plural rather than singular

For example, use /articles/ rather than /article/

Avoid using verbs in URIs

Use the HTTP verbs (e.g. GET, POST, PUT, PATCH, DELETE) to let the user understand what sort of action the endpoint will perform.
For example, use POST: /users/ rather than POST: /users/newUser/

Always return a meaningful status code with error message in the response body

If the request is not successful, the API should return an error status rather than an ok status like 200. It is also helpful for the user if there is an error message in the response body.

Use trailing slashes consistently

All the endpoints of an API should be consistent in using trailing slashes (e.g. /users/) or not (e.g. /users). Ideally, the client should be automatically redirected to the correct endpoint if they use the other version of the URI. Most frameworks will have such an option, so it is worth looking for it and using it.

Use a framework

As an API gets more complex, it is worth investing some time in learning an API framework, such as Django REST Framework for Python or Restify for Node.js. Using an API-specific framework will make it much easier to keep the API consistent and usable.

Using indexes to speed up queries in PostgreSQL

Cecilia Baggini — Thu, 15 Apr 2021 14:19:24 +0000

In this post, I am going to explain how you can use indexes to speed up SELECT queries on a PostgreSQL database. Most of what this post covers will apply to any type of SQL database, but the syntax may be specific to PostgreSQL.

I spent the last month at work designing a new database to store chemical substances concentrations in rivers to provide data to the web application I manage. Finally I figured everything out: how many tables to create, how to connect them, which primary and foreign keys to use… and then I queried it from my web application and it took ages to return any result!

Had I spent a month working on something that would make my application unusable? Admittedly, the table that was giving me issues has almost 30 million rows, but everything I read online suggested that shouldn’t be a problem. So how to speed up my queries and make my application responsive?

I had already read about indexes, but I was convinced that since I already had a primary key covering multiple columns (including the one I was using to filter my slow query), it would not make a massive difference. How wrong I was! Adding an index for substance names to my table reduced query times from 23 to 0.4 seconds!

So when is adding an index to a table appropriate? Indexes will massively speed up SELECT queries that filter a fraction of the rows from a table. So if you’re going to use regularly queries that filter a large table based on one column, it makes sense to create an index for that column.
Creating an index for a table in PostgreSQL is very easy: just write

CREATE INDEX index_name ON table_name (column_name1, column_name2);

If you’re repeatedly querying a table for a small slice of rows, a partial index is probably a better choice. Partial indexes only cover a subset of a table’s data, are smaller in size, easier to maintain and faster to scan. In some cases, creating a full column index when you only want to get a small portion of the table can actually slow down your queries. The code to create a partial index in PostgreSQL is:

CREATE INDEX index_name ON table_name (column_name1) WHERE condition;

That’s it for now, hope it was useful!