Ciro Santilli @cirosantilli 37

 Articles (11k) Discussions (26) Comments (64) Follows  Received likes Files

New Updated  Top  Announced  A-Z  Liked  Followed

SQL TRIGGER Updated 2025-07-16

SQL's implementation of database triggers.

This feature is really cool, as it allows you to keep caches up to date!

In particular, everything that happens in a trigger happens as if it were in a transaction. This way, you can do less explicit transactions when you use triggers. It is a bit like the advantages of SQL CASCADE.

DBMS:

ORM:

Sequelize: SQL TRIGGER in Sequelize

 Read the full article

SQL window RANGE Updated 2025-07-16

 View more

SQLite: www.sqlite.org/windowfunctions.html#exprrange

rm -f tmp.sqlite
sqlite3 tmp.sqlite "create table t (id integer, val integer)"
sqlite3 tmp.sqlite <<EOF
insert into t values
  (0, 0),
  (1, 5),
  (2, 10),
  (3, 14),
  (4, 15),
  (5, 16),
  (6, 20),
  (7, 25),
  (8, 29),
  (9, 30),
  (10, 30),
  (11, 31),
  (12, 35),
  (13, 40)
EOF

Show how many neighbours each column has with val between val - 2 and val + 2 inclusive:

sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
  ORDER BY val RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
) FROM t;
EOF

Output:

0|0|1
1|5|1
2|10|1
3|14|3
4|15|3
5|16|3
6|20|1
7|25|1
8|29|4
9|30|4
10|30|4
11|31|4
12|35|1
13|40|1

val - 1 and val + 1 inclusive instead:

sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
  ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t;
EOF

Output:

0|0|1
1|5|1
2|10|1
3|14|2
4|15|3
5|16|2
6|20|1
7|25|1
8|29|3
9|30|4
10|30|4
11|31|3
12|35|1
13|40|1

There seems to be no analogue to HAVING for window functions, so we can just settle for a subquery for once, e.g.:

sqlite3 tmp.sqlite <<EOF
SELECT * FROM (
  SELECT id, val, COUNT(*) OVER (
    ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) as c FROM t
) WHERE c > 2
EOF

which outputs:

4|15|3
8|29|3
9|30|4
10|30|4
11|31|3

 Read the full article

The best articles by Ciro Santilli Updated 2025-07-16

 View more

These are the best articles ever authored by Ciro Santilli, most of them in the format of Stack Overflow answers.

Ciro posts update about new articles on his Twitter accounts.

A chronological list of all articles is also kept at: Section "Updates".

Some random generally less technical in-tree essays will be present at: Section "Essays by Ciro Santilli".

Trended on Hacker News:
- CIA 2010 covert communication websites on 2023-06-11. 190 points, a mild success.
- x86 Bare Metal Examples on 2019-03-19. 513 points. The third time something related to that repo trends. Hacker news people really like that repo!
  - again 2020-06-27 (archive). 200 points, repository traffic jumped from 25 daily unique visitors to 4.6k unique visitors on the day
- How to run a program without an operating system? on 2018-11-26 (archive). 394 points. Covers x86 and ARM
- ELF Hello World Tutorial on 2017-05-17 (archive). 334 points.
- x86 Paging Tutorial on 2017-03-02. Number 1 Google search result for "x86 Paging" in 2017-08. 142 points.
  Figure 1.
  BIOS bare metal hello world running on a Lenovo ThinkPad T430
  . Source.
x86 assembly
- What does "multicore" assembly language look like?
- What is the function of the push / pop instructions used on registers in x86 assembly? Going down to memory spills, register allocation and graph coloring.
Linux kernel
QEMU
- How to add a new device in QEMU source code?
- How to generate Ubuntu debootstrap disk images for QEMU?
- How to create a multi partition SD disk image without root privileges?
- Figure 4.
  Ubuntu 18.04 running inside QEMU
  . Source. From: How to run Ubuntu desktop on QEMU?
gcc and Binutils:
- How do linkers and address relocation works?
- What is incremental linking or partial linking?
- GOLD (-fuse-ld=gold) linker vs the traditional GNU ld and LLVM ldd
- What is the -fPIE option for position-independent executables in GCC and ld? Concrete examples by running program through GDB twice, and an assembly hello world with absolute vs PC relative load.
- How many GCC optimization levels are there?
- Why does GCC create a shared object instead of an executable binary according to file?
C/C++: almost all of those fall into "disassemble all the things" category. Ciro also does "standards dissection" and "a new version of the standard is out" answers, but those are boring:
- What does "static" mean in a C program?
- In C++ source, what is the effect of extern "C"?
- Char array vs Char Pointer in C
- How to compile glibc from source and use it?
- When should static_cast, dynamic_cast, const_cast and reinterpret_cast be used?
- What exactly is std::atomic in C++?. This answer was originally more appropriately entitled "Let's disassemble some stuff", and got three downvotes, so Ciro changed it to a more professional title, and it started getting upvotes. People judge books by their covers.
- notmain.o 0000000000000000 0000000000000017 W MyTemplate<int>::f(int) main.o 0000000000000000 0000000000000017 W MyTemplate<int>::f(int)
  Code 1.
  nm outputs showing that objects are redefined multiple times across files if you don't use template instantiation properly
  . From: What is explicit template instantiation in C++ and when to use it?

IEEE 754

What is difference between quiet NaN and signaling NaN?
In Java, what does NaN mean?

Without subnormals:

          +---+---+-------+---------------+-------------------------------+
exponent  | ? | 0 |   1   |       2       |               3               |
          +---+---+-------+---------------+-------------------------------+
          |   |   |       |               |                               |
          v   v   v       v               v                               v
          -----------------------------------------------------------------
floats    *    **** * * * *   *   *   *   *       *       *       *       *
          -----------------------------------------------------------------
          ^   ^   ^       ^               ^                               ^
          |   |   |       |               |                               |
          0   |   2^-126  2^-125          2^-124                          2^-123
              |
              2^-127

With subnormals:

          +-------+-------+---------------+-------------------------------+
exponent  |   0   |   1   |       2       |               3               |
          +-------+-------+---------------+-------------------------------+
          |       |       |               |                               |
          v       v       v               v                               v
          -----------------------------------------------------------------
floats    * * * * * * * * *   *   *   *   *       *       *       *       *
          -----------------------------------------------------------------
          ^   ^   ^       ^               ^                               ^
          |   |   |       |               |                               |
          0   |   2^-126  2^-125          2^-124                          2^-123
              |
              2^-127

Code 2.

Visualization of subnormal floating point numbers vs what IEEE 754 would look like without them

. From: What is a subnormal floating point number?

Computer science
- Algorithms
  - Figure 5.
    Average insertion time into heaps, binary search tree and hash maps of the C++ standard library
    . Source. From: Heap vs Binary Search Tree (BST)
- Is it necessary for NP problems to be decision problems?
- Polynomial time and exponential time. Answered focusing on the definition of "exponential time".
- What is the smallest Turing machine where it is unknown if it halts or not?. Answer focusing on "blank tape" initial condition only. Large parts of it are summarizing the Busy Beaver Challenge, but some additions were made.

Git

  | 0           | 4            | 8           | C              |
  |-------------|--------------|-------------|----------------|
0 | DIRC        | Version      | File count  | ctime       ...| 0
  | ...         | mtime                      | device         |
2 | inode       | mode         | UID         | GID            | 2
  | File size   | Entry SHA-1                              ...|
4 | ...                        | Flags       | Index SHA-1 ...| 4
  | ...                                                       |

Code 3.

ASCII art depicting the binary file format of the Git index file

. From: What does the git index contain EXACTLY?

tree {tree_sha}
{parents}
author {author_name} <{author_email}> {author_date_seconds} {author_date_timezone}
committer {committer_name} <{committer_email}> {committer_date_seconds} {committer_date_timezone}

{commit message}

Code 4.

Description of the Git commit object binary data structure

. From: What is the file format of a git commit object data structure?

How do I clone a subdirectory only of a Git repository?

Python
- What is the difference between old style and new style classes in Python?
- What is a mixin in Python, and why are they useful?
- What are the differences between threads and processes in Python?
  Figure 6.
  Python Threads vs Processes with 8 hyperthreads
  . Source.
Web technology
- What does enctype='multipart/form-data' mean?
- JavaScript
  - How does JavaScript .prototype work?
  - What is the difference between .prop() vs .attr() in JavaScript?
OpenGL
- Figure 7.
  OpenGL rendering output dumped to a GIF file
  . Source. From: How to use GLUT/OpenGL to render to a file?
- Figure 8.
  Example of a texture atlas containing glyphs
  . Source.
  Image by Nicolas P. Rougier, author of Freetype GL.
  Used on Ciro Santilli's answer: How to draw text using only OpenGL methods?
- Figure 9.
  OpenGL glFrustrum vs glOrtho
  . Source. From: How to use glOrtho() in OpenGL?
- What are shaders in OpenGL?
- Why do we use 4x4 matrices to transform things in 3D?
- Figure 10.
  Sinusoidal circular wave heatmap generated with an OpenGL shader at 60 FPS on SDL
  . Source.
  From: Is it possible to build a heatmap from point data at 60 times per second?
  Compared CPU vs GPU shaders.
- Image Processing with GLSL shaders? Compared the CPU and GPU for a simple blur algorithm.
  Figure 11. Source.
  Video 1.
  OpenGL GPU GLSL fragment shader real time v4l2 Linux webcam computer vision box blur vs CPU
  . Source.
Node.js
- What's the difference between dependencies, devDependencies and peerDependencies in npm package.json file?
Ruby on Rails
- What is the difference between +<%+, +<%=+, +<%#+ and +-%>+ in ERB in Rails?
POSIX
- What is POSIX? Huge classified overview of the most important things that POSIX specifies.

Systems programming

What do the terms "CPU bound" and "I/O bound" mean?
Figure 12.
Plot of "real", "user" and "sys" mean times of the output of time for CPU-bound workload with 8 threads
. Source. From: What do 'real', 'user' and 'sys' mean in the output of time?

+--------+                  +------------+       +------+
| device |>---------------->| function 0 |>----->| BAR0 |
|        |                  |            |       +------+
|        |>------------+    |            |
|        |             |    |            |       +------+
   ...        ...      |    |            |>----->| BAR1 |
|        |             |    |            |       +------+
|        |>--------+   |    |            |
+--------+         |   |         ...        ...    ...
                   |   |    |            |
                   |   |    |            |       +------+
                   |   |    |            |>----->| BAR5 |
                   |   |    +------------+       +------+
                   |   |
                   |   |
                   |   |    +------------+       +------+
                   |   +--->| function 1 |>----->| BAR0 |
                   |        |            |       +------+
                   |        |            |
                   |        |            |       +------+
                   |        |            |>----->| BAR1 |
                   |        |            |       +------+
                   |        |            |
                   |             ...        ...    ...
                   |        |            |
                   |        |            |       +------+
                   |        |            |>----->| BAR5 |
                   |        +------------+       +------+
                   |
                   |
                   |             ...
                   |
                   |
                   |        +------------+       +------+
                   +------->| function 7 |>----->| BAR0 |
                            |            |       +------+
                            |            |
                            |            |       +------+
                            |            |>----->| BAR1 |
                            |            |       +------+
                            |            |
                                 ...        ...    ...
                            |            |
                            |            |       +------+
                            |            |>----->| BAR5 |
                            +------------+       +------+

Code 5.

Logical struture PCIe device, functions and BARs

. From: What is the Base Address Register (BAR) in PCIe?

Electronics
- Raspberry Pi
  - Figure 13.
    Raspberry Pi 2 directly connected to a laptop with an Ethernet cable
    . Image from answer to: How to hook up a Raspberry Pi via Ethernet to a laptop without a router?
    Figure 14.
    Raspberry Pi 2 connected to a laptop with an USB UART adapter
    . Image from answer to: How to hook up a Raspberry Pi via Ethernet to a laptop without a router?
    Figure 15.
    Raspberry Pi OS being emulated on QEMU 2.5.0 on Ubuntu 16.04 with a modified kernel
    . Image from answer to: How to emulate the Raspberry Pi 2 on QEMU?
    Figure 16.
    Bare metal LED blinker program running on a Raspberry Pi 2
    . Image from answer to: How to run a C program with no OS on the Raspberry Pi?
Computer security
- Why is the same origin policy so important?
Media
- Video 2.
  Canon in D in C
  . Source.
  From: How is audio represented with numbers in computers?.
  The original question was deleted, lol...: How to programmatically synthesize music?
- How to resize a picture using ffmpeg's sws_scale()?
- Is there any decent speech recognition software for Linux? ran a few examples manually on vosk-api and compared to ground truth.
Eclipse
- How to set up the Eclipse for remote C debugging with gdbserver?
Computer hardware
- Are there good open source standard cell libraries to learn IC synthesis with EDA tools?
Scientific visualization software
- Figure 17.
  VisIt zoom in 10 million straight line plot with some manually marked points
  . Source. From: Section "Survey of open source interactive plotting software with a 10 million point scatter plot benchmark by Ciro Santilli"
Numerical analysis
- Video 3.
  Real-time heat equation OpenGL visualization with interactive mouse cursor using relaxation method by Ciro Santilli (2016)
  Source.
Computational physics
- Figure 18.
  gnuplot plot of the y position of a sphere bouncing on a plane simulated in Bullet Physics
  . Source. From: What is the simplest collision example possible in a Bullet Physics simulation?
Register transfer level languages like Verilog and VHDL
- Verilog:
  Figure 19.
  Interacgive ASDF-controlled demo with core logic written in Verilog using Verilator
  .
  From: Is it possible to do interactive user input and output simulation in VHDL or Verilog?
  See also: Section "Verilator interactive example"
Android
- Figure 20. Source. From: How to compile the Android AOSP kernel and test it with the Android Emulator?
- Video 4.
  Android screen showing live on an Ubuntu laptop through ADB
  . Source. From: How to see the Android screen live on an Ubuntu desktop through ADB?
Debugging
Program optimization
- What is tail call optimization?
- Figure 21.
  gprof2dot image generated from the gprof data of a simple test program
  . Source.
  From: How can I profile C++ code running on Linux?
  The answer compares gprof, valgrind callgrind, perf and gperftools on a single simple executable.
Data
- Figure 22.
  Mathematics dump of Wikipedia CatTree
  . Source. In this project, Ciro Santilli explored extracting the category and article tree out of the Wikipedia dumps.
Mathematics
- Figure 23.
  Diagram of the fundamental theorem on homomorphisms by Ciro Santilli (2020)
  
  Shows the relationship between group homomorphisms and normal subgroups.
  From: What is the intuition behind normal subgroups?
- Section "Formalization of mathematics": some early thoughts that could be expanded. Ciro almost had a stroke when he understood this stuff in his teens.
- Figure 24.
  Simple example of the Discrete Fourier transform
  . Source. That was missing from Wikipedia page: en.wikipedia.org/wiki/Discrete_Fourier_transform!
Network programming
- How to make an HTTP get request in C without libcurl?
Physics
- What is the difference between plutonium and uranium?
- Figure 25.
  Spacetime diagram illustrating how faster-than-light travel implies time travel
  . From: Does faster than light travel imply travelling back in time?
Biology
- Figure 26.
  Top view of an open Oxford Nanopore MinION
  . Source. From: Section "How to use an Oxford Nanopore MinION to extract DNA from river water and determine which bacteria live in it"
- Figure 27.
  Mass fractions in a minimal growth medium vs an amino acid cut in a simulation of the E. Coli Whole Cell Model by Covert Lab
  . Source. From: Section "E. Coli Whole Cell Model by Covert Lab"
Quantum computing
- Section "Quantum computing is just matrix multiplication"
- Figure 28.
  Visualization of the continuous deformation of states as we walk around the Bloch sphere represented as photon polarization arrows
  . From: Understanding the Bloch sphere.
Bitcoin
- Section "Cool data embedded in the Bitcoin blockchain"
GIMP
- Figure 29.
  GIMP screenshot part of how to combine two images side-by-side in GIMP?
Home DIY
- Figure 30.
  Total_Blackout_Cassette_Roller_Blind_With_Curtains.
  Source. From: Section "How to blackout your window without drilling"
China
- What would happen if I walked around Beijing with a t-shirt that said "freedom of speech is pretty great"?

 Read the full article

Microwave Updated 2025-07-16

 View more

Micro means "small wavelength compared to radio waves", not micron-sized.

Microwave production and detection is incredibly important in many modern applications:

telecommunications, e.g. being used in
- Wi-Fi
- satellite communications
  youtu.be/EYovBJR6l5U?list=PL-_93BVApb58SXL-BCv4rVHL-8GuC2WGb&t=27 from CuriousMarc comments on some piece of Apollo equipment they were restoring/reversing:
  These are the boxes that brought you voice, data and live TV from the moon, and should be early masterpieces of microwave electronics, the blackest of black arts in analog electronics.
  Ah, Ciro Santilli really wishes he knew what that meant more precisely. Sounds so cool!
- 4G and other cellular network standards
radar. As an example, 1965 Nobel Prize in Physics laureate Julian Schwinger did some notable work in the area in World War II, while most other physicists went to the Manhattan Project instead.
This is well highlighted in QED and the men who made itby Silvan Schweber (1994). Designing the cavity wasn't easy. One of the key initial experiments of quantum electrodynamics, the Lamb-Retherford experiment from 1947, fundamental for modern physics, was a direct consequence of post-radar research by physicists who started to apply wartime developments to their scientific search.
Wikipedia also mentions en.wikipedia.org/w/index.php?title=Microwave&oldid=1093188913#Radar_2:
The first modern silicon and germanium diodes were developed as microwave detectors in the 1930s, and the principles of semiconductor physics learned during their development led to semiconductor electronics after the war.
microwave is the natural frequency of several important Atomic, Molecular and Optical Physics phenomena, and has been used extensively in quantum computing applications, including completely different types of quantum computer type:
- trapped ion quantum computer; Video "Trapping Ions for Quantum Computing by Diana Craik (2019)"
- superconducting quantum computer; e.g. this Junior Microwave Design Engineer job accouncement from Alice&Bob: archive.ph/wip/4wGPJ
Likely part of the appeal of microwaves is that they are non-ionizing, so you don't destroy stuff. But at the same time, they are much more compatible with atomic scale energies than radio waves, which have way way too little energy.

 Read the full article

Calculus of variations Updated 2025-07-16

 View more

Calculus of variations is the field that searches for maxima and minima of Functionals, rather than the more elementary case of functions from

R

R

 Read the full article

Heavyside step function Updated 2025-07-16

 Read the full article

ImageMagick HOWTO Updated 2025-07-16

 Read the full article

Induced pluripotent stem cell Updated 2025-07-16

 Read the full article

UPDATE with JOIN (SQL) Updated 2025-07-16

 View more

Dumping examples under nodejs/sequelize/raw/many_to_many.js.

Not possible without subqueries in the standard syntax, a huge shame: stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server

The UPDATE + FROM extension exists in a few DBMS s:

ORM:

Sequelize: UPDATE with JOIN in Sequelize

 Read the full article

Ciro Santilli's hardware / House 2018-01 / Gas hob Updated 2025-07-16

 View more

SMEG, cannot determine exact model.

2020-11: started sparking by itself once every 5 minutes. Knob controls dirty in hole, but can't find out how to access. Seems slightly glued insulated around edges.

 Read the full article

Ciro Santilli's hardware / Nokia G22 (2023) Updated 2025-07-16

 View more

£112.99

Buying October 2023 as an immediate backup phone after the Google Pixel 3a (2020) touchscreen died, and Motorola Moto G6 Play (2018) wouldn't connect to giffgaff.

www.gsmarena.com/nokia_g22-12137.php

Still working checks: May 2024.

 Read the full article

Minetest Updated 2025-07-16

 View more

github.com/minetest/minetest Written in C++, which is, a plus.

Good Minecraft clone. On Ubuntu 21.10 did:

sudo snap install minetest

which installed 5.4.1, and it worked, except it had no sound, to an error:

ERROR[Main]: Audio: Global Initialization: Failed to open device

 Read the full article

Public-key cryptography Updated 2025-07-16

 View more

It allows you to do two things:

 Read the full article

Ciro's Edict #5 / Misc OurBigBook.com Updated 2025-07-16

 View more

 Read the full article

Symmetric bilinear form Updated 2025-07-16

 View more

symmetric bilinear maps that is also a bilinear form.

 Read the full article

Training, validation, and test data sets Updated 2025-07-16

 View more

stats.stackexchange.com/questions/19048/what-is-the-difference-between-test-set-and-validation-set

 Read the full article

Ubuntu 23.04 Updated 2025-07-16

 Read the full article

Uranium Updated 2025-07-16

 Read the full article

Arbitrary code execution Updated 2025-07-16

 Read the full article

Complex analysis Updated 2025-07-16

 View more

The surprising thing is that a bunch of results are simpler in complex analysis!

 Read the full article

 Unlisted articles are being shown, click here to show only listed articles.