DEV Community: Halim Samy

SQL: Soft Deleting and Unique Constraint

Halim Samy — Mon, 27 Feb 2023 18:32:27 +0000

Introduction

There are two ways to delete data in a database: soft delete and hard delete. Soft delete is a process where the data is not permanently deleted but rather marked as deleted by adding a column like a deleted_at timestamp. This allows the data to be retrieved later if needed. Hard delete, on the other hand, is a process where the data is permanently deleted from the database and cannot be retrieved. Soft delete is often preferred over hard delete as it provides a safety net in case data is accidentally deleted. It also allows for auditing and tracking of deleted data.

The Problem

One issue with soft delete is that it is not compatible with unique constraints. This is because a unique constraint is enforced across all rows, including those that have been marked as deleted with a deleted_at timestamp. As a result, if a row is soft deleted, the unique constraint will prevent the insertion of a new row with the same values, even though the original row is no longer active. This can lead to data integrity issues and should be considered when implementing soft delete in a database.

Consider the following table:

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY (email)
);

-- We are using a soft-delete mechanism, we won't be deleting any row.
REVOKE DELETE ON users FROM 'halimsamy'@'localhost';

As you can see, The deleted_at column is used to determine whether it's deleted or not, and when exactly it has been deleted. We are revoking/removing the delete permission on this table because we will not hard-deleting anything for sure.

Now let's play a bit with our table and see what happens:

-- Add a new user.
INSERT INTO users(name, email) VALUES ('Halim Samy', 'me@halimsamy.com');

-- Delete the added user
UPDATE users SET deleted_at = NOW() WHERE id = 1;

-- Ok, The user was deleted, Let's add him again.
INSERT INTO users(name, email) VALUES ('Halim Samy', 'me@halimsamy.com');

-- ERROR:
-- [23000][1062] Duplicate entry 'me@halimsamy.com' for key 'users.email'

Umm... It seems like I can't add the user again because it's already there theoretically but it's not actually there it's deleted. Well, That's a problem!

The Solution

Dropping the unique constraints it's even an option, so don't even think about it. It has to be there to maintain the consistency of the data. Let's get to the real world:

Partial Indexes (PostgreSQL, MSSQL, ...)

PostgreSQL and MSSQL approached this problem by implementing what is called "Partial Indexes", Which can be applied to a subset of entries according to a specific condition in the WHERE clause.

CREATE UNIQUE INDEX users_email_unique ON users(email, deleted_at) WHERE deleted_at IS NULL;

Virtual Columns (MySQL)

Although the approach of using Partial Indexes is pretty convenient and easy, things aren't that easy when it comes to MySQL for a single reason:

If the Unique Constraint contains any NULL values, MySQL will ignore the constraint completely. (e.g. UNIQUE KEY (email, deleted_at))

We will be using this behavior alongside Virtual Columns as a hack to get soft-delete working with Unique Constraint. Consider the table after modification:

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    deleted_at TIMESTAMP NULL,
    unarchived BOOLEAN GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL,
    PRIMARY KEY (id),
    UNIQUE KEY (email, unarchived)
);

What is the GENERATED VIRTUAL column? It's an auto-generated column based on some expression that gets computed upon selecting a row from that table (not saved to the data storage). We are using that to automatically set unarchived column to 1 if the row is not soft-deleted, otherwise we leave NULL as well.

Setting the unarchived column to 1 when not soft-deleted, gives it a value. The value doesn't matter, it can be anything but it should be constant, so it's not actually a uniqueness factor, the only factor is the email column. On the other hand, when the unarchived column is NULL (row is soft-deleted), MySQL will ignore the Unique Constraint completely (as said above), and that's fine, we don't need to check the consistency of archived data because It was already checked before it became archived.

Conclusion

I think in the case of MySQL we have to hack the technology if the technology is missing something, I think that's called Anti-Pattern. Yet, I am totally OK with that because it's almost at zero cost with no notable downsides except for the new unarchived.

That was a brief P/S Article (Problem and Solution Article) as I would like to call it. I hope that it was quick, enjoyable, and of course useful. See you later and Don't forget to hit me with a Like!

WSL for Developers!: Connect USB devices

Halim Samy — Mon, 06 Jun 2022 01:22:20 +0000

Have you ever wanted to connect a USB to WSL for development or debugging? Good news! this is possible thanks to the usbipd-win project!

What is the usbipd-win project?

Umm! it's simple. Quoted from their GitHub repo:

Windows software for sharing locally connected USB devices to other machines, including Hyper-V guests and WSL 2.

Thus, it requires WSL 2, not WSL 1 (you shouldn't be using WSL 1 anyway!). With this being said let's dive more into this!

Check if your WSL version is supported

What we will be doing here is not supported by every WSL kernel. Open up your WSL terminal and run the following command to check your kernel version:

uname -a

The output should be something like that:

Linux HALIMSAMY 5.10.102.1-microsoft-standard-WSL2 #1 SMP Wed Mar 2 00:30:59 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

Do you see that kernel version above? It should be 5.10.60.1 or later (with WSL2), if that's not the case, feel free to close the article or update your WSL!

Setting up the Windows side

There are two ways to install usbipd-win.

Running the (.msi) installer. Get it from their releases page.
Use the Windows Package Manager (winget): winget install usbipd

It doesn't matter how you install it as long as it gets installed at the end, this is up to your personal preference. However, take this note into consideration from their repo:

If you are using a third-party firewall, you may have to reconfigure it to allow incoming connections on TCP port 3240.

Setting up the WSL side

With the Windows side ready, let's move to WSL. (I'm using Ubuntu 20.04 LTS) Again open up your WSL terminal and run the following command to install some packages that are needed:

sudo apt update
sudo apt upgrade # recommend, but not required
sudo apt install linux-tools-virtual hwdata
sudo update-alternatives --install /usr/local/bin/usbip usbip /usr/lib/linux-tools/*/usbip 20

Attaching device

Plug in the device using USB to the host machine (Windows) and open PowerShell or Command Prompt on your Windows machine as Administrator to continue.

You can list all of the available device by running:

usbipd wsl list

You can expect the output to be like:

BUSID VID:PID DEVICE STATE
1-2 22b8:2e81 < YOUR DEVICE > Not attached
1-3 0b05:1866 USB Input Device Not attached
2-1 0461:4dfb USB Input Device Not attached
2-4 13d3:3563 MediaTek Bluetooth Adapter Not attached

Once you find the device you want and it's bus ID, you can attach it using the following command:

usbipd wsl attach --busid <BUD ID>

You might face a problem where the device is already being used by your Windows host or some application make sure to close that and try again. Also when attaching a device it is recommended to have a WSL terminal already opened so that we ensure that the WSL lightweight VM is up and running!

Now switching to the WSL side, Verify that the device was attached by running:

lsusb

You should see an output similar to:

Bus 002 Device 001: ID 1d6b:0003 Linux Foundation 3.0 root hub
Bus 001 Device 005: ID 22b8:2e81 < YOUR DEVICE >
Bus 001 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hub

Congratulations! Now you can access your devices from inside WSL! When you're done with your device from inside WSL you can de-attach and give it back to the Windows machine by running the following command (from the same Administrator shell):

usbipd wsl detach --busid <BUS ID>

ADB not detecting your phone/device?

When you realize that you can access your USB devices from inside WSL, you would want to use that for your Android development! Well, it's possible actually and in the previous part of the series, we managed to install the Android SDK so we can do that. You can simply just run adb devices and it might detect your device out of the box (it worked for me once!) or we might need to do some extra steps!

Remember that output you got from running lsusb? Do you see those magic numbers after the text "ID" (The 22b8:2e81 part), In fact, those are not just magic numbers, those are the VendorID and ProductID (in order) Now keep that in mind.

Now, I would like you to edit the following file: /etc/udev/rules.d/51-android.rules, use vim or nano it doesn't matter, maybe you would need to install one of them if not already installed.

sudo apt install nano
sudo nano /etc/udev/rules.d/51-android.rules

It is expected that the file would be empty, now add the following line:

SUBSYSTEM=="usb", ATTR{idVendor}=="22b8", ATTR{idProduct}=="2e81", MODE="0666", GROUP="plugdev", SYMLINK+="android%n"

Remember to change the VendorID and ProductID to what you have got from lsusb. Now everything should be working fine. adb devices should show your device.

Additionally, you might need to add the VendorID to your ~/.android/adb_usb.ini (one ID per line, in hex notation like: 0x22b8), but this is usually not needed!

One more issue you can face while attaching your Android device is that Chrome DevTools is using your device from your Windows machine, Disable it for now!

You can also find that starting the adb server as root fixes the problem.

adb kill-server
sudo adb start-server
adb devices

Resources

I'm a man who respects copyrights and if I would quote someone or use this work, I would have to mention him. I didn't come up with all of this out of the blue! In fact, Microsoft has documentation about this topic and a blog post on the Windows Command Line Blog beside the usbipd-win repo wiki and the fix of the ADB issue came from a Stack Exchange answer. I'm just documenting all of those in a single place to save time for people searching for all of this!

WSL for Developers!: Installing the Android SDK

Halim Samy — Wed, 01 Jun 2022 19:09:34 +0000

Introduction

As any developer, you have probably started your career with a Windows machine, after some time you will begin to notice how Windows is horrible with development stuff (at least this is my opinion). For me, I wanted to switch to Linux so badly, Guess what? I did actually. Out of the blue Windows were a part of the past. and used Linux for almost 2 years! everything was perfect except for one thing that you probably know. Yes, thats gaming. for me, Xbox wasnt enough. I wanted a PC gaming experience. (note: dual boot is a pain in the ass)

In the meanwhile, Windows you know, was kind of getting better over time, with the best achievement of Microsoft arrived (that is WSL) I have switched back to Windows. Now I have both Windows gaming and Linux superpower!

About this series

In this series (WSL for Developers!) I will document how I do I use WSL for my different development, for the sake of this series I will assume you have already installed WSL with the Ubuntu distro. You can search online for how to install WSL. Microsoft has an official documentation for installing WSL.

Installing OpenJDK and Gradle

You probably know that Android development requires the JDK and Gradle to be installed. Lets get this done.

sudo apt install openjdk-8-jdk-headless gradle
export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64

You can install OpenJDK 11 if you want, for me I didn't face any issues with both.

Installing Android Command Line Tools

First, we need to get the latest Android command-line tools. You can get it from this link. (Make sure you get the one for Linux, not Windows)

cd ~ # Make sure you are at home!
curl https://dl.google.com/android/repository/commandlinetools-linux-8512546_latest.zip -o /tmp/cmd-tools.zip
mkdir -p android/cmdline-tools
unzip -q -d android/cmdline-tools /tmp/cmd-tools.zip
mv android/cmdline-tools/cmdline-tools android/cmdline-tools/latest
rm /tmp/cmd-tools.zip # delete the zip file (optional)

Setting up environment variables

The Android SDK requires some environment variables to be set. Feel free to edit your .bash_profile or export them the way you like!

export ANDROID_HOME=$HOME/android
export ANDROID_SDK_ROOT=${ANDROID_HOME}
export PATH=${ANDROID_HOME}/cmdline-tools/latest/bin:${ANDROID_HOME}/platform-tools:${ANDROID_HOME}/tools:${ANDROID_HOME}/tools/bin:${PATH}

Accepting SDK licenses

You got it right? we need to agree to some licenses. You probably should read them (if you havent already).

yes | sdkmanager --licenses

Installing SDK components

Now the final part, let's install what we need, feel free to adjust the versions or components depending on your needs!

sdkmanager --update
sdkmanager "platforms;android-30" "build-tools;30.0.3"

You can get a list of all the components available and their versions by running:

sdkmanager --list

Final thoughts

Up to this point, you can build your android apps from inside WSL, but isnt this all about development? where is the emulator? or even attaching a device with USB? What about my IDE and coding experience? Well to be honest those are topics for the up coming article of the series (WSL for Developers!). I like lightweight articles thats my style of writing. Dont worry there is more coming next!

Thanks!

Thanks for reading and coming to the end. I wish that you have enjoyed it and of course found it useful. I would like to thank you that you have given my first article/post a chance and I hope that I will get better over time.