New articles - OurBigBook.com

Ciro Santilli 40 Updated 2025-07-16

github.com/mdawsonuk/LevelDBDumper/tree/e750a27ff58443ecc410b5c16abbdc539d617387#installation worked on Ubuntu 23.10 Annoying installation, but worked: github.com/mdawsonuk/LevelDBDumper/issues/13

Initial issues off-the-bat:

 Read the full article

MongoDB by

Ciro Santilli 40 Updated 2025-07-16

 View more

List databases:

echo 'show dbs' | mongo

Delete database:

use mydb
db.dropDatabase()

or:

echo 'db.dropDatabase()' | mongo mydb

View collections within a database:

echo 'db.getCollectionNames()' | mongo mydb

Show all data from one of the collections: stackoverflow.com/questions/24985684/mongodb-show-all-contents-from-all-collections

echo 'db.collectionName.find()' | mongo mydb

 Read the full article

Relational database by

Ciro Santilli 40 Updated 2025-07-16

 Read the full article

Relational database management system by

Ciro Santilli 40 Updated 2025-07-16

 Read the full article

Ciro Santilli's hardware / Vacuum cleaner by

Ciro Santilli 40 Updated 2025-07-16

 Read the full article

SQL example by

Ciro Santilli 40 Updated 2025-07-16

 View more

We have some runnable SQL examples with assertion under the sequelize/raw directory.

These examples are written in the Sequelize library using raw queries.

Sequelize is used minimally, just to feed raw queries in transparently to any underlying database, and get minimally parsed results out for us, which we then assert with standard JavaScript. The queries themselves are all written by hand.

By default the examples run on SQLite. Just like the examples from sequelize example, you can set the database at runtime as:

./index.js or ./index.js l: SQLite
./index.js p: PostgreSQL. You must manually create a database called tmp and ensure that peer authentication works for it

Here we list only examples which we believe are standard SQL, and should therefore work across different SQL implementations:

nodejs/sequelize/raw/index.js: basic hello world to demonstrate the setup and very simple functionality
nodejs/sequelize/raw/many_to_many.js: illustrates many-to-many relations with JOIN. Contains:
SQL transaction examples:
- nodejs/sequelize/raw/commit_error.js: stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on PostgreSQL, once something fails inside a transaction, all queries in the current transaction are ignored, and COMMIT simply does a ROLLBACK. Let's check. Yup, true for Postgres, but false for SQLite, SQLite just happily runs anything it can, you really need ROLLBACK for it.
- SQL isolation level example
GROUP BY and SQL aggregate functions:
- nodejs/sequelize/raw/group_by_extra_column.js: let's see if it blows up or not on different DB systems, sqlite3 Node.js package allows it:
  - github.com/sequelize/sequelize/issues/5481#issuecomment-964387232
  - dba.stackexchange.com/questions/141594/how-select-column-does-not-list-in-group-by-clause/141600 says that it was allowed in SQL:1999 when there are no ambiguities due to constraints, e.g. when grouping by unique columns
  - github.com/postgres/postgres/blob/REL_13_5/src/test/regress/sql/functional_deps.sql#L27 shows that PostgreSQL wants it to work for UNIQUE NOT NULL, but they just haven't implemented it as of 13.5, where it only works if you group by PRIMARY KEY
  - dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b also says that UNIQUE NOT NULL doesn't work. Dan Lenski then points to a rationale mailing list thread.
- nodejs/sequelize/raw/group_by_max_full_row.js: here we try to get the full row of each group at which a given column reaches the max of the group
  - Postgres: has SELECT DISCINTCT ON which works perfectly if you only want one row in case of multiple rows attaining the max. ON is an extension to the standard unfortunately: www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT Docs specify that it always respects ORDER BY when selecting the row.
    stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case
    stackoverflow.com/questions/586781/postgresql-fetch-the-rows-which-have-the-max-value-for-a-column-in-each-group/587209#587209 also present in simpler form at stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of/123481#123481 gives a very nice OUTER JOIN only solution! Incredible, very elegant.
    dba.stackexchange.com/questions/171938/get-only-rows-with-max-group-value asks specifically the case of multiple matches to the max
  - SQLite:
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/48328243#48328243 teaches us that in SQLite min and max are magic and guarantee that the matching row is returned
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/72996649#72996649 Ciro Santilli uses the magic of ROW_NUMBER
    stackoverflow.com/questions/17277152/sqlite-select-distinct-of-one-column-and-get-the-others/71924314#71924314 get any full row without specifying which, we teach how to specify
    code.djangoproject.com/ticket/22696 WONTFIXed DISTINCT ON
    stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494 DISTINCT vs DISTINCT ON, somewhat related question
  - stackoverflow.com/questions/5803032/group-by-to-return-entire-row asks how to take the top N with distinct after order limit. I don't know how to do it in Postgres
- nodejs/sequelize/raw/most_frequent.js: illustrates a few variants of findind the mode, including across GROUP
  - stackoverflow.com/questions/12235595/find-most-frequent-value-in-sql-column/72979899#72979899
- nodejs/sequelize/raw/group_by_max_n.js: get the top N in each group
order results in the same order as IN:
- MysQL: stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause
- PostgreSQL:
  - stackoverflow.com/questions/866465/order-by-the-in-value-list
  - dba.stackexchange.com/questions/59394/order-of-returned-rows-with-in-statement
LIMIT by a running total: TODO links

 Read the full article

SQL implementation by

Ciro Santilli 40 Updated 2025-07-16

 Read the full article

IBM Db2 by

Ciro Santilli 40 Updated 2025-07-16

 Read the full article

MySQL by

Ciro Santilli 40 Updated 2025-07-16

 View more

sudo mysql

works on Ubuntu 20.10.

Create user for further logins without sudo askubuntu.com/questions/915585/how-to-login-mysql-shell-when-mysql-have-no-password/1325689#1325689:

sudo mysql -e "CREATE USER $USER"

Run command from CLI stackoverflow.com/questions/1602904/how-do-you-run-a-single-query-through-mysql-from-the-command-line

mysql -e 'SHOW DATABASES'

Create test user with password:

sudo mysql -e 'CREATE USER user0 IDENTIFIED WITH mysql_native_password BY "a"'
sudo mysql -e 'GRANT ALL PRIVILEGES ON database_name.* TO "user0"'

and login as that user:

mysql -u user0 -p

mysql -u user0 -pmypassword

The IDENTIFIED WITH mysql_native_password part is to overcome "Client does not support authentication protocol requested by server" when connecting from Node.js.

List users:

sudo mysql -e 'SELECT * FROM mysql.user'

View permissions for each user on each DB: serverfault.com/questions/263868/how-to-know-all-the-users-that-can-access-a-database-mysql

sudo mysql -e 'SELECT * FROM mysql.db'

List databases:

sudo mysql -e 'SHOW DATABASES'

Create database:

sudo mysql -e 'CREATE DATABASE mydb0'

Destroy database:

sudo mysql -e 'DROP DATABASE mydb0'

Show tables in database:

sudo mysql -e 'SHOW TABLES' mydb0

or:

sudo mysql -e 'SHOW TABLES FROM mydb0'

 Read the full article

mysqldump by

Ciro Santilli 40 Updated 2025-07-16

 View more

dev.mysql.com/doc/refman/8.0/en/mysqldump.html

 Read the full article

mysqldump to CSV by

Ciro Santilli 40 Updated 2025-07-16

 View more

 Read the full article

PostgreSQL by

Ciro Santilli 40 Updated 2025-07-16

 View more

PostgreSQL feels good.

Its feature set is insanely large! Just look at stuff like: stackoverflow.com/questions/1986491/sql-split-string-by-space-into-table-in-postgresql/1993058#1993058

Had a look at the source tree, and also felt good.

If Oracle is the Microsoft of database, Postgres is the Linux, and MySQL (or more precisely MariaDB) is the FreeBSD (i.e. the one that got delayed by legal issues). Except that their software licenses were accidentally swapped.

The only problem with Postgres is its name. PostgreSQL is so unpronounceable and so untypeable that you should just call it "Postgres" like everyone else.

 Read the full article

PostgreSQL getting started by

Ciro Santilli 40 Updated 2025-07-16

 View more

On Ubuntu 20.10 PostgreSQL 12.6, login with psql on my default username without sudo fails with: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist

This is the one that worked on Ubuntu 21.04: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152

sudo -u postgres createuser -s $(whoami)
createdb $(whoami)

Explanation:

sudo -u postgres uses the postgres user via peer authentication
-s in createuser -s: make it a superuser
createdb: TODO why do we have to create a table with the same name as the user? Otherwise login fails.

You can now run psql without any password. This works without password due to peer authentication:

sudo cat /etc/postgresql/12/main/pg_hba.conf

shows that peer authentication is available to all users apparently:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

List users:

psql -c '\du'

output:

                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 ciro        | Superuser, Create role, Create DB                          | {}
 owning_user |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Delete user later on:

psql -c 'DROP USER username;'

Create a database:

createdb testdb0

Help toplevel:

help

Get help for Postgres commands such as \h and so on:

\?

List supported SQL commands:

\h

Show syntax for one type of command:

\h SELECT

List all databases:

psql -c '\l'

which shows:

    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
 ciro        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 testdb0     | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

Delete a database:

psql -c 'DROP DATABASE "testdb0";'

If you didn't give a database from the command line e.g.:

psql

you can do that afterwards with:

\c testdb0

Let's create a table and test that it is working:

psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'

List tables, no special tables:

psql testdb0 -c '\dt'

gives:

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | table0 | table | ciro
(1 row)

View table schema: stackoverflow.com/questions/109325/postgresql-describe-table

psql testdb0 -c '\d+ table0'

output:

                                      Table "public.table0"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 int0   | integer       |           |          |         | plain    |              |
 char0  | character(16) |           |          |         | extended |              |

Insert some data into it and get the data out:

psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;'

output:

 int0 |      char0
------+------------------
    2 | two
    3 | three
    5 | five
    7 | seven
(4 rows)

Delete the table:

psql testdb0 -c 'DROP TABLE table0;'

 Read the full article

Peer authentication by

Ciro Santilli 40 Updated 2025-07-16

 View more

www.postgresql.org/docs/13/auth-peer.html

Uses the name of the current Linux user to login without a password.

 Read the full article

Brain-computer interfaces could be the next big thing by

Ciro Santilli 40 Updated 2025-07-16

 View more

Main article: Section "Brain-computer interface".

This is one of the deep tech bets that Ciro Santilli would put his money in as of 2020.

How hard could it be? You just have to learn the encoding of the neural spine/eyes/ear, add an invasive device that multiplexes it, and then the benefits could be mind blowing.

Musk approves.

Interestingly and obviously, the initial advances in the area are happening for people that have hearing or vision difficulties. Since they already have a deficient sense, you don't lose that much by a failed attempt.

Hearing is likely to be the first since it feels the simplest. Ciro heard there are even already clinical applications there. TODO source.

 Read the full article

Semantic triple by

Ciro Santilli 40 Updated 2025-07-16

 Read the full article

PostgreSQL logging by

Ciro Santilli 40 Updated 2025-07-16

 View more

stackoverflow.com/questions/722221/how-to-log-postgresql-queries

Ubuntu 21.10 has a certain default level of logging by default to:

/var/log/postgresql/postgresql-13-main.log

but it does not log everything, only/mostly errors it seems.

Setting:

log_statement = 'all'

under:

/etc/postgresql/13/main/postgresql.conf

and then restarting the server:

sudo service restart postgresql

just works.

Realtime monitoring for long queries instead: stackoverflow.com/questions/8597516/app-to-monitor-postgresql-queries-in-real-time

 Read the full article

PostgreSQL serialization failure by

Ciro Santilli 40 Updated 2025-07-16

 View more

When using SQL REPEATABLE READ isolation level and SQL SERIALIZABLE isolation level, concurrent transactions may fail with a serialization failure, and then you might need to retry them. You server code or your ORM must always account for that.

A good way to explore when it happens is to use the example

 Pinned article: Introduction to the OurBigBook Project

Welcome to the OurBigBook Project! Our goal is to create the perfect publishing platform for STEM subjects, and get university-level students to write the best free STEM tutorials ever.

Everyone is welcome to create an account and play with the site: ourbigbook.com/go/register. We belive that students themselves can write amazing tutorials, but teachers are welcome too. You can write about anything you want, it doesn't have to be STEM or even educational. Silly test content is very welcome and you won't be penalized in any way. Just keep it legal!

Video 1.

Intro to OurBigBook

. Source.

We have two killer features:

topics: topics group articles by different users with the same title, e.g. here is the topic for the "Fundamental Theorem of Calculus" ourbigbook.com/go/topic/fundamental-theorem-of-calculus
Articles of different users are sorted by upvote within each article page. This feature is a bit like:
- a Wikipedia where each user can have their own version of each article
- a Q&A website like Stack Overflow, where multiple people can give their views on a given topic, and the best ones are sorted by upvote. Except you don't need to wait for someone to ask first, and any topic goes, no matter how narrow or broad
This feature makes it possible for readers to find better explanations of any topic created by other writers. And it allows writers to create an explanation in a place that readers might actually find it.
Figure 1.
Screenshot of the "Derivative" topic page
. View it live at: ourbigbook.com/go/topic/derivative
Video 2.
OurBigBook Web topics demo
. Source.
local editing: you can store all your personal knowledge base content locally in a plaintext markup format that can be edited locally and published either:
- to OurBigBook.com to get awesome multi-user features like topics and likes
- as HTML files to a static website, which you can host yourself for free on many external providers like GitHub Pages, and remain in full control
This way you can be sure that even if OurBigBook.com were to go down one day (which we have no plans to do as it is quite cheap to host!), your content will still be perfectly readable as a static site.
Figure 2.
You can publish local OurBigBook lightweight markup files to either https://OurBigBook.com or as a static website
.
Figure 3.
Visual Studio Code extension installation
.
Figure 4.
Visual Studio Code extension tree navigation
.
Figure 5.
Web editor
. You can also edit articles on the Web editor without installing anything locally.
Video 3.
Edit locally and publish demo
. Source. This shows editing OurBigBook Markup and publishing it using the Visual Studio Code extension.
Video 4.
OurBigBook Visual Studio Code extension editing and navigation demo
. Source.
Internal cross file references done right:
Infinitely deep tables of contents:
Figure 6.
Dynamic article tree with infinitely deep table of contents
.
Live URL: ourbigbook.com/cirosantilli/chordate
Descendant pages can also show up as toplevel e.g.: ourbigbook.com/cirosantilli/chordate-subclade

All our software is open source and hosted at: github.com/ourbigbook/ourbigbook

Further documentation can be found at: docs.ourbigbook.com

Feel free to reach our to us for any help or suggestions: docs.ourbigbook.com/#contact

 Site settings

 Pinned article: Introduction to the OurBigBook Project  Hide

 Pinned article: Introduction to the OurBigBook Project