Forem: Sergey Royz

Calculating a difference between consecutive rows using triggers

Sergey Royz — Mon, 27 Nov 2023 18:41:14 +0000

Annotation

In this article, we delve into a scenario where the seamless storage of sensor data in a database is coupled with the concurrent computation of the variance between successive measurements.

Problem statement

Sensor data serves as a temporal representation of metric states. An added layer of valuable information can be derived by quantifying the rate of change, or the "velocity," between consecutive readings. However, the computation of these differences, commonly referred to as deltas, can be resource-intensive when performed on demand for each sensor.

The proposal is to use the trigger mechanism during data insertion, making the computation of these deltas automatic and cost-effective. This also shifts the computation burden from the application layer to the database.

What is a trigger?

From Wiki A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.

Example

Proceeding with our case, let's consider the following table:

CREATE TABLE sensor_data
(
    "timestamp"   INTEGER NOT NULL,
    "sensor_id"   INTEGER NOT NULL,
    "value"       FLOAT   NOT NULL
);

ALTER TABLE sensor_data ADD UNIQUE ("timestamp", "sensor_id");

With columns to store value and time deltas the creation script will look as follows:

CREATE TABLE sensor_data
(
    "timestamp" BIGINT  NOT NULL,
    "sensor_id" INTEGER NOT NULL,
    "value"     FLOAT   NOT NULL,
    "dv"        FLOAT,
    "dt"        BIGINT
);

ALTER TABLE sensor_data ADD UNIQUE ("timestamp", "sensor_id");

Creating a trigger

CREATE OR REPLACE FUNCTION calculate_deltas()
    RETURNS trigger AS
$$
DECLARE
    last_value FLOAT;
    last_ts    BIGINT;
    now        BIGINT;
BEGIN
    RAISE NOTICE 'NEW: %', NEW;

    SELECT "timestamp", value
    INTO last_ts, last_value
    FROM sensor_data t
    WHERE t.sensor_id = NEW.sensor_id
    ORDER BY "timestamp" DESC
    LIMIT 1;

    RAISE NOTICE 'last_ts = %; last_value = %', last_ts, last_value;

    IF FOUND THEN
        SELECT (EXTRACT(EPOCH FROM now()) * 1000)::BIGINT INTO now;
        NEW."timestamp" := now;
        NEW.dv := NEW.value - last_value;
        NEW.dt := now - last_ts;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER tr_sensor_data_deltas
    BEFORE INSERT
    ON sensor_data
    FOR EACH ROW
EXECUTE PROCEDURE calculate_deltas();

Showcase

Now let's insert a couple of values into sensor_data table.

INSERT INTO my_sensor_data ("timestamp", sensor_id, "value")
VALUES ((EXTRACT(EPOCH FROM now()) * 1000)::BIGINT, 1, 3.14);
-- wait a bit
INSERT INTO sensor_data("timestamp", sensor_id, "value") 
VALUES((EXTRACT(EPOCH FROM now()) * 1000)::int, 1, 2.74);

Now let's review the data in the table:

SELECT * FROM sensor_data;

Output should be like this

timestamp	sensor_id	value	dv	dt
1701109996686	1	2.74	-0.39	4814
1701109991872	1	3.14	NULL	NULL

The dv and dt columns were populated by the trigger automatically.

Exploring Concurrent Programming in Clojure: A Deep Dive into Promises, Delays, and Futures

Sergey Royz — Sun, 12 Nov 2023 14:32:59 +0000

Intro

Clojure, a modern functional programming language built on the Java Virtual Machine (JVM), provides powerful concurrency features that make it an excellent choice for building scalable and concurrent systems. In this article, we'll delve into a concise code snippet that showcases some of Clojure's concurrency tools, including promise, delay, force, deliver, and future.

An example

Let's break down the provided Clojure code snippet:

(defn wait [timeout]
  (let [p (promise)
        d (delay (Thread/sleep timeout)
                 (deliver p :done))]
    (future (force d))
    (println (str "waiting " timeout "ms..."))
    @p))

promise: The promise function is used to create a placeholder for a value that will be provided asynchronously. In this case, p is a promise that will eventually be fulfilled with the value :done.
delay: The delay function is a mechanism for lazy evaluation. It takes an expression and delays its evaluation until its value is actually needed. In this example, (Thread/sleep timeout) is the expression, and (deliver p :done) is the value to be delivered to the promise p after the timeout.
force: The force function is used to force the evaluation of a delayed value. Here, (force d) ensures that the delayed expression in d (the sleep and promise delivery) is executed.
future: The future macro is a powerful construct for concurrent programming. It evaluates the provided expression asynchronously in a separate thread, returning a future object immediately. In this case, (future (force d)) kicks off the asynchronous execution of the delayed expression.
println: A simple print statement to log a message indicating that we are waiting for a specified amount of time.
@p: The @ symbol is the dereference operator, and @p is used to obtain the value of the promise p. This operation will block until the promise is fulfilled, providing a form of synchronization between the main thread and the asynchronously executing thread.

Usage

(wait 1500)

Outputs:

waiting 1500ms...
=> :done

Conclusion

This concise Clojure code snippet showcases the language's elegant concurrency features, combining promises, delays, and futures to achieve asynchronous execution and synchronization. Understanding these constructs is crucial for building efficient and scalable concurrent systems in Clojure. As you explore further, you'll find that Clojure's rich set of concurrency primitives enables you to express complex concurrent patterns in a concise and expressive manner.

Effective QR-code compression

Sergey Royz — Sat, 07 Oct 2023 18:58:18 +0000

Intro

In the previous article, I described how to extract a QR code from the following message:

{
    "n0": 7,
    "x": 21,
    "c": "AcO9w7fDuCDCoMOdKXbDqkvCt11dwoLCqg/DtV/DgCwBF1/CksKVwqfCiwvDrALDtcKfeQcAw6vDv8O0E8Kgwp/Dg3Vyw6vCpMKmw503Mghkw7/Dk8Kl"
}

The Origin

This message is generated by the service qrshare.io, which serves well in the following cases:

Transferring a file from a desktop to a mobile.
Sharing a file with multiple recipients (e.g., presentations, lecture materials).
Sharing a file during an online meeting, especially on Google Meet calls, using this Chrome Extension.

Explanation of the Format

But why is the QR code data transferred in such a strange format in the first place? This is the logic behind using such a format:

A QR code is a square matrix of zeroes and ones, which can be represented as a one-dimensional array.
The array of 0s and 1s can be split into groups of 8 elements, each representing one byte. This significantly reduces the amount of data to be sent over the network.
The array should be padded with leading zeros so that its length is divisible by 8.
Since JSON is a text-based format and the string obtained in the previous steps is binary, it has to be base64 encoded.

The Code

The code below takes a string as input and produces that magically ciphered QR code, where:

n0 - the number of leading zeroes that should be removed before restoring the matrix.
x - the side length of the square matrix.
c - the base64-encoded binary string representing the sequence of 0s and 1s.

import com.google.zxing.EncodeHintType;
import com.google.zxing.qrcode.decoder.ErrorCorrectionLevel;
import com.google.zxing.qrcode.encoder.Encoder;
import lombok.SneakyThrows;

import java.nio.charset.StandardCharsets;
import java.util.Base64;
import java.util.Map;

public class QRCodeGenerator {

    public static int[] toBinary(int c) {
        if (c < 0 || c > 255) {
            throw new IllegalArgumentException("Input should be withing the range [0, 255], got: " + c);
        }

        int[] bits = new int[8];
        int i = 7;
        while (c > 0) {
            bits[i--] = c & 0x1;
            c = c >> 1;
        }
        return bits;
    }

    private static char oneCharFromBinary(int[] eightBits) {
        int code = 0;
        for (int i = 7; i >= 0; i--) {
            code += eightBits[i] * (1 << (7 - i));
        }
        return (char) code;
    }

    private static String stringFromBinary(int[] bits) {
        if (bits.length % 8 != 0) {
            throw new IllegalArgumentException("Number of bits should be divisible by 8");
        }
        StringBuilder str = new StringBuilder();
        for (int i = 0; i < bits.length; i += 8) {
            int[] charBits = new int[8];
            System.arraycopy(bits, i, charBits, 0, 8);
            str.append(oneCharFromBinary(charBits));
        }
        return str.toString();
    }

    @SneakyThrows
    public static QR encode(String data) {
        if (data == null || data.length() == 0) {
            throw new IllegalArgumentException("Data is empty");
        }
        var hints = Map.of(EncodeHintType.CHARACTER_SET, "UTF-8");
        var qrCode = Encoder.encode(data, ErrorCorrectionLevel.M, hints);
        var m = qrCode.getMatrix();
        var size = m.getWidth() * m.getHeight();
        var zeroesCount = size % 8 == 0 ? 0 : (8 - (size % 8));

        var bits = new int[size + zeroesCount];

        int i = 0;
        for (int y = 0; y < m.getHeight(); y++) {
            for (int x = 0; x < m.getWidth(); x++) {
                bits[(i++) + zeroesCount] = m.get(x, y);
            }
        }

        var rawCode = stringFromBinary(bits);
        var code = Base64.getEncoder().encodeToString(rawCode.getBytes(StandardCharsets.UTF_8));

        return new QR(zeroesCount, m.getWidth(), code);
    }

    // used for testing encoded
    public static int[][] decode(QR qr) {
        var decoded = new String(Base64.getDecoder().decode(qr.code), StandardCharsets.UTF_8);
        var data = decoded.toCharArray();

        var bits = new int[data.length * 8];
        for (int i = 0; i < data.length; i++) {
            var byteBits = toBinary(data[i]);
            System.arraycopy(byteBits, 0, bits, i * 8, 8);
        }

        var matrix = new int[qr.size][qr.size];
        int i = qr.zeroesCount;
        for (int y = 0; y < qr.size; y++) {
            for (int x = 0; x < qr.size; x++) {
                matrix[y][x] = bits[i++];
            }
        }
        return matrix;
    }

    public record QR(int zeroesCount, int size, String code) {
    }
}

The code uses the zxing library for generating QR codes.

Thanks for reading this far!
Try sharing a file with qrshare.io or use the Chrome Extension!

Next time, we'll discuss how to create that beautiful animation when the code is generated. Stay tuned!

The story of one QR-code...

Sergey Royz — Tue, 26 Sep 2023 14:09:53 +0000

As a beginner programmer, you may have heard of QR codes, those square-shaped patterns of black and white squares that store information such as URLs, contact information, or other types of data. But have you ever wondered how QR codes work or how you could generate one in Python? In this article, we'll explore a Python script that generates an ASCII QR code from a given set of data.

The Code Breakdown

Let's start by breaking down the code step by step:

1. Importing Necessary Libraries

import json
import base64
from typing import NamedTuple, Union

The code begins by importing two standard Python libraries: json for working with JSON data and base64 for handling Base64-encoded strings. Additionally, it imports the NamedTuple class and the Union type hint from the typing module.

2. Defining a NamedTuple

class TextToQR(NamedTuple):
    n0: int
    x: int
    c: str

Here, a NamedTuple named TextToQR is defined with three fields: n0, x, and c. This NamedTuple will be used to represent the data needed to generate the ASCII QR code.

3. Generating the ASCII QR Code

def generate_ascii_qr_code(qr: TextToQR, margin: int = 2) -> list[str]:
    # Decode the Base64 string and convert it to a binary string
    decoded = bytes(base64.b64decode(qr.c)).decode('utf-8')
    binary = ''.join(list(map(lambda b: bin(ord(b)).replace('0b', '').zfill(8), decoded)))
    binary = binary[qr.n0:]

    x = qr.x

    rows = []
    rows.extend(['  ' * (x + margin * 2) for _ in range(margin)])

    for i in range(x):
        row = binary[i * x:(i + 1) * x]
        row = ''.join(list(map(lambda c: '█' * 2 if c == '1' else '  ', row)))
        rows.append(('  ' * margin) + row + ('  ' * margin))

    rows.extend(['  ' * (x + margin * 2) for _ in range(margin)])

    return rows

This function, generate_ascii_qr_code, takes a TextToQR object as input along with an optional margin parameter. It generates an ASCII QR code based on the provided data.

The Base64-encoded string qr.c is first decoded and converted to a binary string.
The binary string is sliced starting from the index qr.n0. This allows you to specify where in the binary string to begin generating the QR code.
The code then constructs the ASCII QR code by iterating through the binary string, replacing '1' with '██' (two filled squares) and '0' with ' ' (two spaces).
Rows of spaces are added to the top and bottom of the code to create a margin, as specified by the margin parameter.
The function returns a list of strings, where each string represents a row of the ASCII QR code.

4. Main Program

if __name__ == "__main__":
    data = """
    {
    "n0": 7,
    "x": 29,
    "c": "AcO9wp5nw7ggBsKgw50Swp12w6rDnsKLwrdTwrJdwoLCh1oPw7VVX8OAKF4BF8K8b8Kcw5/CqGpPw69JdsOmEMORF8OnwoJOLMKeAMKkfQHCl8K/YMKnBXzCvcOjw61KEcOKB3fCuXQDMmBedsKfw7AAwp8Yw6fDt8OLw5dgwpLCti11wq1fwpvCpMOZF10Nw7JeCMKNw4/Cn8OTwolS"
    }
    """
    data = json.loads(data)

    qr = generate_ascii_qr_code(TextToQR(**data))
    for line in qr:
        print(line)

The main program begins by loading JSON data containing the QR code configuration. It then calls the generate_ascii_qr_code function with this data and prints each row of the generated ASCII QR code.

Let's see how it works!

After running the code from the above it produces the following awesome output!



    ██████████████  ████    ████████    ████    ██████████████    
    ██          ██                    ████  ██  ██          ██    
    ██  ██████  ██      ██    ██  ██    ██████  ██  ██████  ██    
    ██  ██████  ██  ██  ████  ████████  ██      ██  ██████  ██    
    ██  ██████  ██  ██    ██████  ████    ██    ██  ██████  ██    
    ██          ██  ██        ██████  ██  ████  ██          ██    
    ██████████████  ██  ██  ██  ██  ██  ██  ██  ██████████████    
                    ██  ██        ██  ████████                    
    ██      ██  ████████  ████████      ████  ██████████    ██    
    ████    ████  ████████████  ██  ██        ████  ██  ██        
    ██    ██████████████  ████████  ██    ██    ██  ██████  ██    
    ██  ██████    ████        ██        ████  ██      ██          
    ██  ████████████    ████████          ██    ██    ██████      
        ██  ████    ██    ████████                  ██  ██        
    ██      ██████████  ██              ████    ██  ████████      
    ████████████  ████          ██  ██    ██████          ██      
    ██  ██████████    ██  ████████  ████████      ██████████      
    ████  ██  ██    ██  ██        ██      ██████    ██  ██        
            ██████  ██████  ████████  ██████    ██  ██████  ██    
                    ████    ████    ██    ████            ██      
    ████████    ██████  ████  ██    ██████████████████            
                    ██    ██████████      ████      ██████        
    ██████████████  ██████████    ██  ████████  ██  ██████  ██    
    ██          ██    ██    ██  ██  ████  ████      ██  ████      
    ██  ██████  ██  ████  ██  ████  ██  ██  ████████████    ██    
    ██  ██████  ██    ██    ████  ████    ██      ██  ██████      
    ██  ██████  ██        ████  ██████████    ██    ██  ██████    
    ██          ██      ██      ████  ██████    ██████████        
    ██████████████  ██    ██████      ██    ██  ██  ██    ██

Can you read the code? ;)

Tools

the service used in the article: https://qrshare.io
CLI tool for instant file upload from the console:

pip install qrshare-io-cli

Thanks for reading till then!
I hope this was useful to you, let me know in the comments what you think.