This Week I Learned: 2022W50

No TWIL last week cause with the end of the year (and the writing of my Python book), it was quite hard to keep up.

Mysql - JSON

You can enforce the schema of a JSON column in MySQL.

Let’s say we create a table with a JSON column type.

CREATE TABLE my_table (my_data JSON NOT NULL DEFAULT('{}'));

We can actually alter the table and create a constraint to check before insertion if the JSON column is valid, given a custom JSON schema.

First generate a JSON schema (you can use tools like this one online to make it easier).

Then we are going to alter the table and create a constraint that checks if the JSON data input is valid given the JSON schema.

ALTER TABLE my_table
    ADD CONSTRAINT my_data_is_valid CHECK (
        JSON_SCHEMA_VALID(
            '{
              "$schema": "http://json-schema.org/draft-04/schema#",
              "type": "object",
              "additionalProperties": false,
              "properties": {
                "posts": {
                  "type": "array",
                  "items": [
                    {
                      "type": "object",
                      "properties": {
                        "title": {
                          "type": "string"
                        },
                        "tags": {
                          "type": "array",
                          "items": [
                            {
                              "type": "string"
                            },
                            {
                              "type": "string"
                            }
                          ]
                        }
                      },
                      "required": [
                        "title",
                        "tags"
                      ]
                    }
                  ]
                }
              },
              "required": [
                "posts"
              ]
            }',
            my_data
        )
    );

Now if we try to insert some valid and invalid data:

/* No error */
INSERT INTO my_table (my_data) VALUES ('{"posts": [{"title": "some title", "tags": ["food", "tech"]}]}');

/* Here I add a forbidden new property */
/* ERROR 3819 (HY000) at line XX: Check constraint 'my_data_is_valid' is violated. */
INSERT INTO my_table (my_data) VALUES ('{"posts": [{"title": "some title", "tags": ["food", "tech"]}], "someAdditionalProperty": "lorem"}');

/* Here I forget to provide the required tags property */
/* ERROR 3819 (HY000) at line XX: Check constraint 'my_data_is_valid' is violated. */
INSERT INTO my_table (my_data) VALUES ('{"posts": [{"title": "some title"}]}');

Found in this tweet.

PHP - Enum

PHP Enums don’t allow most of magic methods, on the contrary to objects. Doc here.

Vim

In Vim/Neovim, after you have selected some lines visually, you can use the key = to autoformat these lines. Pretty useful!

Vim

You can use & in your search and replace to use the matched experession.

For example, if you have this text:

- https://google.com
- https://github.com
- https://reddit.com

If you search and replace using the & sign:

:%s/https:\/\/.\+/[&](&)

- [https://google.com](https://google.com)
- [https://github.com](https://github.com)
- [https://reddit.com](https://reddit.com)