DEV Community: Horia Constantin

Book summary: SQL Antipatterns

Horia Constantin — Tue, 06 Sep 2022 08:05:20 +0000

I was recently going through my notes of SQL Antipatterns and was shocked to realize how actual this book still is. I'm going to share my summary of the book. I guarantee you'll find at least a few antipatterns in your current database design.

Logical Database Design Antipatterns

Jaywalking

Antipattern: Storing a delimiter-separated value string in a varchar field instead of creating an intersection table. It might be easier, but it makes queries harder. And updating the field is also a pain. And your field might have all kinds of consistency errors (for instance, ids are normally numbers, but now you have a varchar. The DB cannot ensure consistency for you).

Legitimate Uses of the Antipattern: when the data you're storing in the varchar is not needed to be used in the queries (gets displayed as-is).

Solution: create an intersection table.

Naive trees

Antipattern: When you have multiple level parent -> child relationship and you represent it using an Adjacency list (you have a parent id in every row). It becomes impossible to get the whole tree in one query (because by default you can only get the next level).

Legitimate Uses of the Antipattern: when you're certain that you will only have 1 level of queries. The alternatives are harder to implement.

Solutions:

Path Enumeration (storing the string of ancestors as an attribute of each node, just like the filesystem directories). Drawback: limitations of the Jaywalking antipattern.
Nested Sets (add to each node nsleft and nsright: the nsleftnumber is less than the numbers of all the node’s children, whereas the nsright number is greater than the numbers of all the node’s children. These numbers have no relation to the id values). General tree reading becomes easy, but editing the tree becomes hard because all the nsleft and nsright need to be reprocessed.
Closure Table: an extra table that holds all the relations between ancestors and descendants (including node indirect relationships and a reference to itself). The only drawback is the extra table size for big trees. But other operations are very easy to implement.

Reference: "Joe Celko’s Trees and Hierarchies in SQL for Smarties"

ID required

Antipattern: Using a primary key that is not the appropriate primary key for this table (e.g., there is a natural primary key like social security number or there could be a compound key in there). If a table has a unique column then, most probably that could be the primary key.

Solution: declare a primary key on a field that is easy to index and makes the most sense for that particular table

Keyless entry

Antipattern: when you don't use the constraints offered by the DB (especially foreign keys).

Legitimate Uses of the Antipattern: never, unless your DB doesn't support it

Solution: always declare constraints

Mixing data with metadata

Antipattern: For example, by appending the year onto the base table name, we’ve combined a data value with a metadata identifier.

This is the reverse of mixing data with metadata from the Entity-Attribute-Value and Polymorphic Associations antipatterns. In those cases, we stored metadata identifiers (a column name and table name) as string data.

In Multicolumn Attributes and Metadata Tribbles, we’re making a data value into a column name or a table name. If you use any of these antipatterns, you create more problems than you solve.

Entity-value-attribute

Antipattern: when you have a table with the columns: entity_id, attribute_name, attribute_value

It appears when you have OOP inheritance and the children have various fields. If you use it, you lose referential integrity and data type validation.

Legitimate Uses of the Antipattern: you shouldn't use this in a relational DB. Just use a non-relational DB or one of the solutions below.

Solutions:

Single table inheritance: one table with all the attributes of the children (it's going to be a sparse table
Concrete table inheritance: completely independent tables for each subtype
Class table inheritance (one table for the parent properties and tables with the child-specific properties)
Semistructured data (one table with the parent properties and an extra blob field with the child-specific attributes). Disadvantages similar to the Entity-value-attribute.

Polymorphic associations

Antipattern: When you need to reference from a single table, multiple parent tables (e.g., comments to features/bugs). You're using the pattern if you store in a column the name of your type of parent table.

Solutions:

Create intersection tables
Create a common super table (with only ids) and reference that with your new table. The children will reference the super table (ex: comments references issues; bugs references issues, features references issues)

Multicolumn attributes

Antipattern: When an entity's attribute can have multiple values, you create multiple columns called attribute1, attribute2, etc.

Solutions:

Create a dependent table (a table that references the initial table by id and has the attribute as the other column)
Store each value with the same meaning in a single column.

Metadata tribbles

Antipattern: When to support scalability and performance, you clone tables or new columns to support new partitions: ex: sales_2013, sales_2014, etc. (tables of the same schema with different entities). Basically, in your database, you will have a table per "some data value".

Legitimate Uses of the Antipattern: when splitting the database sensibly makes database administration tasks easier after the database size passes a certain threshold.

Solutions:

Using Horizontal Partitioning: rows are separated into partitions. Mostly managed by the DB.
Using Vertical Partitioning: splitting a table by columns. Databases usually do this for TEXT and BLOB.

Physical Database Design Antipatterns

Rounding errors (when you want to store floating point numbers in the DB)

Antipattern: When you use float, double, or any other related DB type. The problem is how the number is represented internally by the DB. It will do all kinds of unpredictable roundings.

Legitimate Uses of the Antipattern: scientific applications.
Solution: use NUMERIC or decimal

31 flavors

Antipattern: When you want to restrict a column to specific values (e.g., the status of a ticket), you define the allowed values in the table's schema or as a trigger.

Legitimate Uses of the Antipattern: when you have very few values that will never change. But you might still get it wrong (e.g., gender).

Solution: create a lookup table.

Phantom files

Antipattern: When you want to store Store Images or Other Bulky Media (when you reference them via the DB), and you use the FS directly, instead of the DB. These need to be specifically backed up, and the DB does not manage them.

Legitimate Uses of the Antipattern: when the data files might affect how your DB behaves.

Solution: Use BLOB Data Types As Needed

Index shotgun

Antipattern: When you want to Optimize the performance of the DB, and you throw indexes here and there without thinking too much about it (no indexes, too many indexes, queries that cannot benefit from indexes).

Legitimate Uses of the Antipattern: none
Solution:

Measure the query times
Get the query execution plan (QEP)
Analyze the QEP and find where the index needs to be added after creating the index, test
Optimize: covering (compound) indexes could speed up things even more; use in mem vs. on disk indexes
Rebuild: because indexes become fragmented as rows are deleted/created/updated

Query Antipatterns

Fear of the unknown

Antipattern: thinking that null is just another value. Actually, SQL treats null as a special value, different from zero, false, or an empty string. As a general rule, all operations with null will return null. Or using a value to represent null.

Solution: Treat null as a unique value. Declare Columns NOT NULL when it makes sense. Also, consider if using default makes sense.

Ambiguous groups

Antipattern: What you're already doing: Get Row with Greatest Value per Group. When you're using group by and are trying to get extra information from the row that you selected (like other columns that are not included in the group by expression), in other words, referencing Nongrouped Columns in the select list of the query.

Every column in the select list of a query must have a single value row per row group. This is called the Single-Value Rule. Columns named in the GROUP BY clause are guaranteed to be exactly one value per group, no matter how many rows the group matches.

Legitimate Uses of the Antipattern: none because most DBs will throw an error
Solutions:

Don't add an extra column to the select list
Using a Correlated Subquery (own note: seems pretty hard to implement)
Using a Derived Table: use the group query as a subquery of a query that selects the columns that you need. But merging their results with left join https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column
Using an Aggregate Function for Extra Columns -> apply a function to the extra column in the select list so that you ensure that there's only one value coming back (e.g., max or GROUP_CONCAT)

Random selection

Antipattern: When you must fetch a random sample from one of your tables, you sort data randomly -> ORDER BY RAND(). This operation does a full table scan.

Legitimate Uses of the Antipattern: only when you know for sure that the size of the data that you're randomizing will not be bigger than 50-100 rows.

Solutions:

Pick a Random primary Key Value Between 1 and MAX; only when primary keys are contiguous
Choose Next Higher Key Value: just like before, except you'll be picking the next available key (assumes that the keys are non-contiguous). The results will not be evenly distributed
Get a List of All Key Values. Choose One at Random (do it in the application; does not scale well)
Choose a Random Row Using an Offset: select a random between 0 and total_rows and use it in an OFFSET command
Search the docs of your DB of choice

Spaghetti query

Antipattern: When you want to achieve everything in only one SQL query (solving a complex problem in one step,

You might create a cartesian product without wanting to do that
It's going to be hard to maintain

Legitimate Uses of the Antipattern: when you're going to use that query as a datasource in a 3rd party app

Solutions:

Split your initial query into multiple smaller ones
Use the union label (to combine smaller queries)
Writing SQL Automatically—with SQL -> use concat to create a list of queries to run; you can also do this with a script in bash

Readable passwords

Antipattern: Store Password in Plain Text
Solution: hash your passwords with a salt directly in your application.

SQL injection

Antipattern: when writing dynamic SQL queries, you end up executing unverified input as code.
Solutions:

Parameterize Dynamic Values (use query parameters)
Filter Input
Do your best not to write standard SQL. Rely on your data access framework as much as possible

Diplomatic immunity

Antipattern: when you want to code fast, completely skipping engineering best practices. Make SQL a Second-Class Citizen, not giving the DB the same importance that the code gets in terms of quality.

Solution:

Forget about self-documenting code. It's a myth. Use Entity-relationship diagrams, break them down into functionalities and mention triggers and stored procedures
Use source control
Have tests that test the interaction with the DB

Magic beans

Antipattern: when designing an MVC application and you don't consider enough how to separate the app logic between the M, V, and C. (Own note: I'll probably need to re-read this chapter from the book)

Solution: Active record is a design pattern that maps objects to DB tables. The Model Has an Active Record -> basically, build services that compose the models. Aim to make your model a domain model, not a database model.

Rules of normalization

Should be considered only in the context of your app. How much do you want to normalize/denormalize? It's a tradeoff. And it would be best if you benchmarked it.

The objectives of normalization:

To represent facts about the real world in a way that we can understand
To reduce storing facts redundantly and to prevent anomalous or inconsistent data
To support integrity constraints

The forms

First Normal Form: no repeating groups (no multicolumn attributes, no multiple values in one column)
Second Normal Form: when you're repeating values on a column, instead of creating a dependent table
Third Normal Form: single responsibility principle for tables (put the column in the table that it belongs to)
Advanced 3rd form (Boyce-Codd Normal Form): table doesn’t contain any field (other than the primary key) that can determine the value of another field. Example: teacher, subject, student. The subject is always dependent on the teacher.
Fourth Normal Form: BCNF without compound keys
Sixth normal form: It’s typically used to support a history of changes to attributes (e.g., an audit log).

See no evil

Antipattern: discarding DB error messages or not looking at the raw SQLs that get run when debugging the application.

Solution: Recover from Errors Gracefully -> log potential exceptions every time.

Antipatterns that are not that current anymore?

Implicit Columns

Antipattern: when you want to reduce typing (like explicitly mentioning all columns in the select list), you use:

For insert without specifying the columns, when you refactor you might break the insert or, even worse, the database
A select with * will fetch and display all the data

Legitimate Uses of the Antipattern: when you're writing queries to try things out

Solution: Name Columns Explicitly

Poor man’s search engine

Antipattern: when you want to do a full-text search, you use pattern-matching predicates (e.g., LIKE, REGEXP).

Solution: don't use SQL. Elasticsearch has made this one easy. If you have to use SQL (for simple cases, each vendor has a solution for this already)

Pseudokey neat-freak

Antipattern: when you want to have contiguous keys in a table (e.g., when you have deletions in the DB), you want to fill in the gaps.

Instead of allocating a new primary key value using the automatic pseudokey mechanism, you might want to make any new row use the first unused primary key value
Renumbering Existing Rows -> changing the existing keys so that they are contiguous.

Legitimate Uses of the Antipattern: none.

Solution: just let it be. You can see the primary key id as UUIDs. The fact that they are consecutive is just a coincidence of the implementation.

CITCON 2020, a virtual unconference

Horia Constantin — Mon, 08 Feb 2021 21:42:56 +0000

Adina Moldovan

@adinnaplus

CITCON rocked again, this time ONLINE!
citconf.com #citcon @citcon

16:44 PM - 30 May 2020

Have you heard about open-spaces before? Have you ever been to an unconference? If yes, find something else to read. Otherwise, keep on reading. I’m going to describe how I experienced a virtual unconference I recently attended. By the end, you will have some good reasons to join one.
What is an unconference?

I’ve written about unconferences before, but I never explained why I love this event format. There’s a lot to say, but I’ll restrict myself to the top three reasons why I keep coming back:

Autonomy and empowerment: the format of the event allows the participants to focus on meeting their individual needs. The law of two feet means that you are expected to use your own two feet to move to whatever place you can best contribute and learn. In the same way, all participants are allowed to adjust scheduled sessions as they see fit.
Strong engagement of participants: anyone can propose a topic for a session. During a session, you are encouraged to suggest an improvement if you notice that a session is not going well.
Relaxed expectations: two guiding principles remind participants to take it easy. “Whoever comes is the right people” and “Whatever happens is the only thing that could’ve happened“.

An open-space offers a different kind of experience than a regular conference. Even with a good understanding of the guiding principles, it’s going to be hard to imagine how it develops unless you attend it.

A typical unconference session

A typical unconference session is usually a “go-with-the-flow” discussion on the proposed topic, and it depends on the participants to set the direction and to keep the session on track. Often, participants, bring up a couple of subjects related to the topic. Commonly, some of the subjects will be touched only briefly. There’s no slide deck to take away at the end; every participant takes from the session whatever they need.

CITCON unconference goes virtual

CITCON usually takes place in a European city. Since COVID-19 makes traveling unsafe, CITCON 2020 went online. I volunteered to help the organizers, Jeff and PJ. We used the suggestions from Misha Glouberman, did a couple of test runs, and, in the end, we had a good understanding of the technology stack needed to need all the needs of an open-space. Amelie sums it up perfectly.

Amélie Cornélis

@ameliecornelis

Tool stack this time is:
- slack (single channel)
- zoom rooms (we're all co-hosts)
- single google doc with All The Links
- miro for the schedule

10:37 AM - 30 May 2020

The tools worked well on the day of the conference (~70 participants). A couple of notes on the tools: the organizers arranged for a basic Zoom subscription, and a basic Miro subscription. There was also a need for a full-time “Zoom receptionist”, with the primary responsibility of elevating participants to co-hosts. Being a co-host means that you can move around the breakout rooms, which served as the rooms of the sessions (the blue horizontal swimlanes in the schedule below).

When it’s over, it’s over

I am finishing with another of the four principles of open-spaces. Creativity has its rhythm. So do groups. When you think it is over, ask: Is it over? And if it is, go on to the next thing you have passion for. See you at CITCON. Or at humansconf. Or at DevOpsDays.

Checklist for learning Scala

Horia Constantin — Mon, 08 Feb 2021 21:30:27 +0000

Horia Constantin

@constantinhoria

Just when I thought I knew enough programming: "Defining a set as a function that says whether an item is included in the set or not" 🤯 #scala

type Set = Int => Boolean
def contains(s: Set, elem: Int): Boolean = s(elem)

09:16 AM - 19 Jan 2021

Learning Scala doesn’t have to be painful. In a previous article, I proposed the use of a checklist to give structure to learning a new programming language. My intention with this article is to demonstrate how to use a checklist to learn scala.

The checklist

Mindset

Personal why’s for learning Scala: I’ve wanted to start to learn a new programming language for some time. I chose Scala thinking that I can get a freelance gig faster than anything else. And because a former colleague recommended it.
Find a project/application to build with Scala: a web app that supports most technologies that are required these days for a fully-featured website. I called it scalatraseed since it uses the Scalatra web framework at its core.

Setting

Find a high-quality online Scala course/book: I’ve found excellent resources here. Scala originated in EPFL, so I started with Functional Programming Principles in Scala.
Find/create Scala cheat sheet: here
Find the official Scala documentation: start with the standard library
Find/create a comparison between Scala and Java: these docs and this course
Find/create a dictionary/glossary of the concepts, keywords, etc.: I haven’t been doing very well at this point because I tend to look up a concept as soon as I encounter it.
Take notes: questions, curiosities, “strange” code: useful point, but the questions and curiosities change from one day to another that it makes it useless to write it here.
Start collaborating on an open-source library: this point was a bit more complicated than I thought. Many libraries are either too big or have too many contributors already or are too advanced for a beginner or don’t have good-first-issues. I decided to settle down on Scalatra because I was already using it in my demo project.
Find/create a roadmap that describes the learning process: this is a good start
Find a code conventions document: the online Scala course that I’ve started uses Scalastyle for enforcing the style of the code. If it’s good enough for the Scala core team, it’s good enough for me.
Find/create a list of antipatterns: I couldn’t find anything, probably because Scala is such a flexible language (allowing full OOP or FP and anything else in between). The small things that I’ve found so far revolve around using the language constructs correctly (e.g., use “if” as an expression, not a statement). So, nothing useful here.
Find and learn how to use the most popular build tool: I wanted to leverage my maven knowledge initially, but it’s a constant uphill battle. Sbt for the win!
Find a list of Scala project seeds: from lightbend
Find the frameworks: Scalatest, Scalatra, Sangria

Having applied the checklist, I’m discovering that some points are part of the continuous process of learning (take notes; create a dictionary/glossary), while others are a one-time task (find the official documentation).

I’m also learning that some tasks are not so useful as others. That can be because they’re part of another, more comprehensive task (the online Scala course that I found uses Scalatest) or because they’re not that important at this stage of learning (they just take up mental and checklist space).

Based on this experience and the idea of lean learning, I’ll be updating the checklist template so that it is a more effective learning resource. For historical reasons, I’ll keep the checklist in this article as it is.

Checklist for learning a new programming language

Horia Constantin — Sat, 06 Feb 2021 20:39:11 +0000

Update 22/01/2020: I’ve applied the checklist to learning Scala and I’m updating it based on that experience.

In a workshop that I recently gave, I compared learning a new programming language with learning a new natural language. Depending on your experience, learning a new programming language might be a medium difficulty task or a hard project. In the latter situation, you’re going to need to have some structure in your approach, to ensure you reach your goal. But what is an easy way to start? I propose to use a checklist similar to the one I’ve written below.

Naive solution

“I’ve got enough Java experience. I’m a good software engineer. I’ve played with other languages before, and it went well.” I said to myself. I was aware that Scala has some very funky notations and that it uses plenty of functional programming concepts. But my confidence was high. I’ll do it as I did it before: take an online course, read a book, find the documentation, make a small app, and go from there. But I didn’t expect to get into analysis paralysis because of the overwhelming number of books, frameworks, courses, etc. I eventually decided on an online course and finished it only to realize that I wasn’t learning as much as I wanted. I decided to make the learning process explicit by creating a checklist that I could improve iteratively.

The checklist

I created this checklist iteratively as I was learning Scala, but I’ve generalized it for any programming language. In a future post, I’ll show how I’ve applied it to my learning.

There are two components that I found valuable while learning Scala: keeping my motivation high (mindset) and having a good learning plan that supports my learning experience (setting). Start with mindset first (ordered starting with top priority), continue with the setting.

Mindset

Clarify what your personal why’s for learning are. Try to connect this to intangible needs/wants/dreams. Bad: I want to learn this because my boss promised me a 2% raise at the end of the year. Good: I want to learn this because I’m curious about how it works
Find a project to build while learning. Online courses and books tend to teach concepts without applying them to real-world use cases.

Setting

One-time tasks

Follow a high-quality online course/book about the language. Material created by the language’s author or main contributor clarifies the language philosophy and its specific patterns.
Find/create a cheat sheet with the most common expressions and idioms of the language and keep it open in a browser tab
Find/create a comparison between the new language and another that you’re familiar with
Start collaborating on an open-source library. Pick a common one, but not a complex one. Start by searching on GitHub.
Find the official language documentation, learn how to read it, and keep it open in a browser tab
Find/create a roadmap that describes the steps that you will go through (the table of contents of online courses are usually a good source)
Find a code-conventions/style-guide document

Continuous tasks

Take notes: questions, curiosities, “strange” code.
Find someone to review the code that you write.
Find/create a dictionary/glossary of the concepts, keywords, etc. which are specific to the new language (consider making a mindmap). Aim to understand the use cases for them. And practice it.

Deprecated tasks

These tasks seemed a good idea in theory but were not useful to me in practice.

Find/create a list of antipatterns. It might be syntax or design patterns, but the language that you are learning is going to do a couple of things differently compared to what you were used to.
Find the frameworks that you foresee needing and are the most popular (GitHub search with the new language tag)
Find and learn how to use the most popular build tool

Work in progress

At this point, I’m convinced of the usefulness of having a checklist for learning a new programming language. However, I’m aware that some of these ideas are more effective than others. In a future article, I’ll tell you how my learning process was affected by following this checklist.

Mobile app makers review

Horia Constantin — Sat, 06 Feb 2021 20:13:36 +0000

A friend came to me recently with the idea of creating a mobile app that would support the learning platform that he already has. We discussed what features he wanted and what his budget was. The budget was tight, and I realized that developing the app from scratch was not an option. I used this cost calculator to get an estimate for the custom-built app. Later, I found this calculator that seems simpler and gives a lower estimated cost. It seemed that the only way forward was to look for a platform that would generate the app for me.

I knew that there should be some mobile app generators on the market, but I had no idea what features they offered and at what cost. To my surprise, I discovered that there were more than mature 20 platforms, all offering similar features. I soon found out that the mobile-app-makers industry is quite mature: there are reviews and comparisons and top10’s.

I soon understood that all those articles would not help me with my problem: determine how much it would cost to run such an app. The reason is that the platforms are vastly diverse in terms of their offering: different prices for similar, but not always the same set of app features. The services included in the contract and the actual terms would also vary greatly. I had to make my research and comparison, based on the needs of my friend.

In this post, I’m sharing the results of the research that I’ve done. Disclaimer:

I might have missed a few app makers as I only used this article as a source
My focus was on the feature set that my friend needed. I didn’t look at the rest of the offered features.
I only spent 10-15 minutes with each app builder platform, so I might have missed details that need more digging (or contacting customer support).

This comparison sheet is not detailed nor complete enough to allow me to select a “winner”. Instead, I will use it during the discussions with my friend to make a shortlist of 2-3 mobile app makers that I will investigate further.

How’s Xiaomi MiBand privacy?

Horia Constantin — Sat, 06 Feb 2021 19:45:42 +0000

29/12/2020 Final update: Almost a year after my complaint, I received an answer: the Dutch Personal Data Authority is not going to research my complaint further because they can’t say if they are able to be effective in reaching the goal of the complaint. Additionally, there were no other complaints against Huami, so they consider it a small risk (I wonder if they know that Huami is actually behind Xiaomi). Here's the full official response.

02/06/2020 Update: While still waiting for a response from the Dutch Personal Data Authority, I’ve been contacted by Kevin Grahl, who wrote a great article on how to protect yourself from leaking personal data to Xiaomi. The trick is to use the Lockdown iOS app. I’m confident about this solution 🙂

27/11/2019 Update: After 2 SARs without a response from Huami, I’ve decided to make a complaint with the Dutch Personal Data Authority. The estimated response time is 3 months.

7/11/2019 Update: I just received a concrete answer from Xiaomi’s Privacy Office: “MiFit is a product of Huami. Xiaomi Singapore Pte Ltd is not the MiFit data controller and is not responsible for processing the personal data of MiFit ‘s data subject. Please refer below Huami’s contact information is privacy@huami.com.”. So, I’ve made a SAR to Huami.

Xiaomi Miband

Xiaomi produces a wearable activity tracker called MiBand. The 3rd generation has a reasonable feature set, and it is 2-3 times cheaper than its competitors. It’s a great entry-level band if you want to begin tracking your fitness levels.

I bought a Xiaomi MiBand3 because I was hoping to make sense of my erratic sleeping patterns. I was also eager to discover how much movement I was getting through the day. Additionally, I was hoping to connect with some faraway friends via the MiFit phone app.

But what about privacy?

The data that a fitness tracker generates feels personal, and Xiaomi is a Chinese company. I don’t want the Chinese government to have information about me. It’s bad enough that Chinese citizens have no privacy. To make an informed purchase, I searched for articles about “Xiaomi privacy”. I found two articles (1 and 2) reviewing the InfoSec aspects of a couple of fitness trackers. Yet, nothing that answered my question about Xiaomi. In the end, I decided to buy the band, but I felt uneasy every time the MiFit app “synchronized” with the band. Uneasy, but not concerned enough to look deeper into the matter.

Susana Sanz, from BalkonTactics, renewed my interest in privacy and InfoSec. After our talk, my unease changed into serious concern about the data generated by my shiny, new fitness tracker. The ethical/moral/philosophical aspect of privacy did not interest me; that’s something that others have talked in more detail (Glenn Greenwald and Edward Snowden come to mind).

I was interested to know what exactly does Xiaomi know about me. Making a Subject Access Request (SAR), a right recognized by the GDPR is one way to go about it. For the sake of learning how SAR work, I submitted one to privacy@xiaomi.com. But this process would take a long time, and patience is not one of my virtues. While waiting for an official response, why not use a “hacky”, DIY approach to get an answer?

The privacy investigation

Nowadays, almost all phone apps communicate with a server on the internet. It is possible to intercept that communication, while it’s happening (see the technical method section at the bottom of the post). Thirty minutes later, I confirmed my concern: Xiaomi collects all the data that MiBand generates. To be more specific, all the data displayed in the MiFit app (e.g., sleep data, training data, heart rate) gets uploaded to the Xiaomi servers. There is no way to disable the upload in the configuration of the application. The band keeps a backlog of all the recorded data, and everything gets uploaded to Xiaomi when I open the mobile app. If you don’t use the MiFit app, you’ll end up with a band that only knows how to count your steps and how to measure your heart rate.

The only good news here is that, at this moment, the geo-location data is not collected passively (outside training sessions). But there is no guarantee that it will stay like this in the future.

In the end, I’m left feeling disappointed that Xiaomi has unrestricted access to sensitive information about my lifestyle:

what would happen if someone infiltrates their systems? I’m sure that there are ways in which this data could be exploited to my disadvantage.
what’s up with this shady user agreement? Not clear to me what Xiaomi and its partners do with the data. I understand that the moment it gets uploaded, I “waive any and all ownership, legal and moral rights” to my data. But how does Xiaomi use it? Who are the third parties or Xiaomi affiliates that have access to it? Is it sold or monetized in any way? Lots of questions and no clear answers.

If you’ve been on the internet long enough, you’re familiar with the phrase “If you’re not paying for the product, you are the product”. Fun fact, in 2016, the average worth of a Facebook user was $3.73 per quarter. I’m not ok with that, but let’s leave it there for now. Thinking logically, the phrase should not stand once I start paying for the product. Right? Well, it seems that this is not the case with Xiaomi MiBand.

There’s no privacy and I don’t own the data

I’m dissatisfied that, despite paying for the MiBand, I “waive any and all” rights over the data that I generate. In this new light, what seemed to be a good deal (best cheapest fitness tracker on the market) became a lousy deal when reading the fine print and doing a bit of research.

In the end, there are two big questions left in my mind about this way of collecting personal data:

Is this a general practice of the fitness band industry, or is Xiaomi an exception?
Are other Xiaomi products collecting data in the same way?

If you own a fitness tracker or another Xiaomi product, can you do a bit of digging around and let me know? Or get in touch with me and we can do the research together.

Technical details of the investigation

The technical method of finding out what MiBand3 data gets uploaded to Xiaomi’s servers was simple. Had I known it would be so simple, I wouldn’t have postponed it so much.

The hardware setup: I had to install mitmproxy on my laptop.

connect both my phone (with the Xiaomi MiFit app) and my laptop to the same WiFi network
install and run mitmproxy on my laptop
install the mitmproxy root certificate on my phone
on my phone, set the proxy server for the WiFi to point to my laptop
open the Xiaomi MiFit app and look at the requests going into the proxy running on my laptop

Out of all the requests made by the Xiaomi MiFit app, only one sends a lot of data:

POST https://api-mifit-de.huami.com/v1/data/band_data.json?r=50AB6198-7007-47BF-86AC-53F606CDD4F6&t=1565977799279

In the payload of this request is a key called data_json that contains all the recent band data (as the name of the endpoint suggests). I took a look at the data in the JSON object and saw all the data points that are shown on all the graphs (in the app), including all the geo-coordinates of my running sessions. To my surprise, the MiFit app didn’t seem to send to Xiaomi information about my current location. Yet, this is not a guarantee that this information will remain on my phone in the future.

From a technical perspective, I enjoyed doing this little research. Using mitmproxy was straightforward, and I recommend it if you want to see what communication goes on between the apps on your phone and the internet.

Unit testing algorithms

Horia Constantin — Wed, 03 Feb 2021 23:28:54 +0000

In the last weeks, I’ve been improving my computer science knowledge by solving programming problems. Since these problems have multiple solutions, I find myself writing duplicated unit tests. And refactoring these tests is cumbersome (if find&replace doesn’t work).

To make the point clear, I’m going to pick a problem with many possible implementations: sorting an integer array. Let’s keep things simple and solve the problem using heap sort, insertion sort, merge sort, quick sort, and selection sort. When the time comes to write the tests, all of the tests will be duplicated between implementations. If, in the future, I come up with a new test case, I have to write (copy&paste) that test to the test class of each implementation. This is far from optimal.

After doing a bit of digging, I found a convenient solution to removing the duplication: JUnit5’s @ParameterizedTest and @ValueSource. I had to do a bit of refactoring: I created a new interface that declares the sort method, and I made sure that all *sort classes implement the interface properly (you did create one class per implementation, right?). After that, it was just a matter of wiring up the tests.

Cool! 🙂 Before, the unit tests were all over the place, and not all implementations had all the corner cases tested. Soon enough, I found that the QuickSort implementation throws StackOverflowError when the input array contains duplicate values. You can find the source code here.

Database integration tests

Horia Constantin — Mon, 01 Feb 2021 22:03:24 +0000

Did you ever get stuck while trying to find the best way to write a database integration test without mocking the third party? I know I did.

I’ve known about Martin Fowler since I was a junior developer. He is one of my favorite technology thought-leaders and I enjoy reading the thought-provoking articles that he publishes on his blog. In this post, I want to expand on his article about the test pyramid, in which he explains the difference between unit tests, integration tests and, UI tests. Specifically, I’ll expand on the part about integration tests.

I want to show you an easy way to run integration tests against a database. With this goal in mind, I’ve created a java demo project, that illustrates the idea and can be used as a starting point for more complicated applications.

I chose this topic because of all the moments of hesitation that I had when I was developing applications that interacted with a database. I wanted to have tests, but I didn’t want to spend too much time on their setup and my options were limited:

mocking/stubbing the database interaction layer
connecting to an in-memory database
connecting to a local/remote test database

Regardless of the option I picked, I always had the feeling that the solution could be more elegant. My hesitation would turn into frustration as the application grew and the tests would become harder to maintain. Recently, I discovered a better option: connecting the tests to a database running in a Docker container.

Database integration tests with docker

In my last project, Vlad and Max showed me that using docker containers simplifies running integration tests against a database. No mocking, no complicated infrastructure, no tinkering with configuration files. As long as you can run Docker on the build machine, you can run the tests.

This is the elegant solution that I was looking for:

install and configure Docker on the build servers and the developer machines that will run the tests
use docker-maven-plugin to build a docker image of the database and start/stop a container based on the image
populate the database with the necessary data (use ‘/docker-entrypoint-initdb.d/’ or a database migration tool like flyway)
finally, use maven-failsafe-plugin to run the integration tests

The benefits of Docker integration tests

Integration tests become portable and easy to debug. If the tests run on my laptop, they will run also on the build machine. If the tests fail on the build machine, the problem will also appear on my laptop.

Tests become repeatable: the plugin builds the docker image from scratch before the tests start and remove it after the tests finish.

This approach is transferable, to other types of integration tests. It’s easy to replace the database container with a dockerized REST API (in a move towards contract testing) or with a message queue container.

The demo project that I wrote is more than a typical “hello world” application. The integration test starts a docker MySQL container and makes a simple SQL query. You will notice that the docker-maven-plugin configuration is more complicated than necessary, given such a simple test. The reason: the code samples that I found online seemed trivial. I wanted to have a “bootstrap” project that I could reuse in real-life projects without the hassle of gluing everything from scratch.

This approach to writing integration tests has opened my eyes to the possibilities that exist today in the QA automation domain. The tooling around Docker seems varied enough to accommodate all test types. The number of plausible excuses for not having a proper CI pipeline is getting too low…

Setting up a development MacBook

Horia Constantin — Mon, 01 Feb 2021 21:43:01 +0000

I wrote a set of quick-start scripts for developers that use macOS. A Windows version of these scripts might follow soon.

Setting up a “fresh” laptop for development is always painful, be it windows, macOS or Linux.

It was the second time in the year when I was doing a fresh install of macOS on my MacBook. I was tired of installing all the apps that I need in a one-by-one fashion. I was tired of doing all the small configurations (for macOS and for the apps). Most of the time I forgot some of them and there are a couple of settings that I have to search online every time.

I started off by making a list of settings and applications that I use and then I discovered that most of these things can be automated via the command line. I was sure that someone did this before me and I started searching for some scripts that automate the installation of the development environment on a MacBook. I hit jackpot.

Using a couple of GitHub repositories as inspiration, I created a couple of simple scripts that should install almost everything that I need to start developing on a MacBook. The scripts and the instructions are available here: https://github.com/treaz/mac-dev-setup/

I’m curious how much it will hurt the next time that I have to set up a development MacBook.

Maven plugins that I like

Horia Constantin — Fri, 22 Jan 2021 22:02:27 +0000

I’ve been through a couple of projects until now and I’m noticing that there are a couple of maven plugins that are useful but are not that famous. I’ll list them below, together with a small explanation of why I find them useful.

Build Number Maven Plugin (org.codehaus.mojo)

Sometimes you want to expose the current build version of your application without necessarily updating the artifact version (when you’re iterating fast and using *-SNAPSHOT). I didn’t get to use this one, but I can imagine that it would be good to have in a /info endpoint.

Apache Maven Shade Plugin

Sometimes you need to package all your dependencies into a single jar file. If you’re using Spring-Boot, you’ll have an easier life if you use the spring-boot-maven-plugin. Most of the time I use it for small projects, where I want to keep everything compact.

License Maven Plugin (org.codehaus.mojo)

Sometimes you need to add a license header to all the files of your project or you need to pack with your application a file with all the licenses of the 3rd party libraries that you’re using. I wish I would have known about it three years ago when I was updating THIRD-PARTY.txt files manually.

Versions Maven Plugin

Sometimes you want to update the libraries that you’re using to the latest version. This plugin makes managing that update a lot easier. You have good control over the strategy of the version advance. This plugin makes your life easier, assuming you have enough tests to make sure that the update didn’t break your application. I just discovered this plugin and I like that it makes the library update chore a breeze.

Apache Maven Enforcer Plugin

Sometimes you need to make it clear what versions of OS, Java, etc. the application can be built with (because of incompatibilities). I like that the set of already-made rules is extensive.

Apache Maven Checkstyle Plugin

Sometimes you need to enforce the codestyle that the team has agreed to. A bonus is that it helps the new members of the team to pick up the code standards immediately. I have a love and hate relationship with this plugin, but overall I’m happy to see the same kind of code everywhere.

OWASP Dependency-Check Plugin

You always want to have an application that is as secure as possible. This plugin allows you to automatically check for the latest reported vulnerabilities. I knew about the OWASP Top Ten for a couple of years and I learned about this plugin 2 months ago. I’ll never stop using it.

Java Unconferences/Open-spaces

Horia Constantin — Mon, 18 Jan 2021 11:07:16 +0000

In 2018, I attended CITCON for the first time and then I searched for a similar Java unconference/open-space. I got super lucky to be accepted at JCrete, but that’s another story. The reality is that unconferences are mind-blowing, but a bit hard to find if you don’t know what to look for.

These are the unconferences that I currently know about:

Join one… it’s going to be worth it.

Testing JPQL queries straight from Intellij

Horia Constantin — Sun, 17 Jan 2021 21:39:04 +0000

In my current project, most of the queries are written in Java Persistence Query Language (JPQL). As with any *QL (that eventually gets translated to SQL), it’s cumbersome to do the translation of the *QL to SQL and vice-versa. This translation is generally done when you’re creating a new query or trying to debug an existing query. It would be great to be able to send JPQL queries directly to the DB.

One way to do this is to configure the JPA console in IntelliJ IDEA. Note that this feature is only available in the Ultimate Edition (paid).

For those that are in a rush, this is the minimal configuration needed to get the JPA console going. To keep things simple, let’s assume that you have a single module project, called test-jpa:

Add a new data source to the project (View | Tool Windows | Database). This data source should point to the same DB that your entities use.
Add “JavaEE Persistence” framework support to test-jpa (right click module | Add framework support…). Click OK
Open the Persistence Window (View | Tool Windows | Persistence)
In this window, you will assign a data source to test-jpa (right click module | Assign data sources)
In the Assign Data Sources window, you will see a line with the value “Entities” which points to an empty Data Source field. Click on this field and select the data source from step 1. Click OK.
In the Persistence Window, expand the module and right click on Entities | Console. You have a choice between JPA and Hibernate Console.

Some cool features that both Consoles support:

Navigating to the declaration of a class or field
Auto-completion
Parameterized queries

Reference: https://www.jetbrains.com/help/idea/using-jpa-console.html