# Migrations

The migrations folder contains SQL functions that are used to help with creating
a consistent database structure and running SQL functions on which TypeScript
code depends on.

## Axinom Utility SQL functions

The folder `before-migration` contains functions that encapsulate best practice
approaches on different topics. They are added to the database schema `ax_utils`
and can be used during runtime.

- The setup file includes instructions to create the required database schema
  for the utility functions and the SQL define functions
- The `raise_error` encapsulates the logic to raise a PostgreSQL database
  exception with appropriate error codes. The Mosaic TypeScript solution has
  helpers to map those codes to specific error types.
- The validation functions file offers an assortment of different validation
  functions that make sure that the data in a cell follows specific rules. They
  are created in a way to be understandable by the Mosaic TypeScript code to
  raise corresponding errors.
- GraphQL subscriptions are supported via database change triggers. The
  subscriptions file provides the means to enable this functionality and
  integrate into the GraphQL subscription handler.
- The created update triggers file contains database triggers that add change
  date, user names and other data from the database context when something is
  saved or updated.
- The authorization contains functions that are part of the Mosaic authorization
  flow. When creating a database connection, the Mosaic TypeScript stack adds
  some variables to the context about the acting user and his permissions. The
  authorization functions enable checks if specific permissions exist.

All of those functions need to be updated when a new version of the db-common
library is used in a service. The Mosaic stack automatically takes care of that
if the best-practice service setup is used.

## Define SQL functions

The define SQL functions help with creating database tables, indexes, ... in a
way that supports to set up and change the database in an easy and reliable way.
Those functions are only used during database migrations - never at runtime.
They reside in their own SQL schema to which the GraphQL role does not have
access.

The define SQL functions depend on the `ax_utils` functions.

## VSCode Snippets, Documentation, and Maintainability

Both Utility and Define SQL functions are there to simplify creation of database
migrations. But because they are provided as part of the library and there is no
intellisense support in sql files - we need to also make these functions
discoverable and provide documentation and examples to make it easier to use
them.

To help with that - we provide a way to generate a file that contains VSCode
snippets for all relevant functions, which adds a possibility to scaffold code
pieces in migration files and easily fill them with relevant data. There are 2
perspectives that should be kept in mind to make this work: Library Consumer and
Library Maintainer.

For more info on custom VSCode snippets - check here:
https://code.visualstudio.com/docs/editor/userdefinedsnippets

### Library Consumer Perspective

From this perspective, the following steps can be performed by the developer:

- install or update `@axinom/mosaic-db-common` and `@axinom/mosaic-cli`
- if .mosaic file does not exist in monorepo root, run the cli command:
  `mosaic create-extension-config`
  - This is a one-time setup to create a `.mosaic` config file to allow
    inclusion of modular CLI commands into the mosaic CLI.
- run the cli command: `yarn mosaic generate-vscode-sql-snippets`
  - if it's a multitenant service - run
    `yarn mosaic generate-vscode-sql-snippets -m` to include multitenancy
    snippets
- This generates or updates a `.vscode/mosaic-sql-migrations.code-snippets`
  file, which contains all sql migration code snippets that are supported by the
  currently installed `@axinom/mosaic-db-common` version.
  - This file shall be kept under source control.
  - There is no need to look into the contents of this file unless you are just
    interested.
- go to `migrations/current.sql` or any other `.sql` file.
- Start typing `ax-` and intellisense will show a dropdown of all available
  snippets.
  - Use the Arrow Up and Down keys to select different options.
  - Documentation window will be visible with text explanation and preview of
    generated code.
    - If this window is not able to be viewed fully and some content are
      hidden - its size can be changed by dragging the right-bottom corner of
      the window.
  - typing after `ax-` would narrow down the selection. e.g. `ax-auth` would
    find multiple authentication-related snippets, e.g.
    `ax-define-authentication`
  - When fitting snippet is selected - clicking `Enter` would expand it into the
    actual migration code and highlight properties for easier editing
    - It is recommended to edit highlighted values and clicking `Tab` to switch
      to the next highlighted value. Some snippets are constructed in a way to
      modify multiple values at once in such a way, making creating the
      resulting migrations code easier.

With this, user is provided with an option to browse available snippets,
familiarize with them using IDE intellisense capabilities, and simplify the
process of writing migrations.

### Library Maintainer Perspective

To provide the user experience from the section above - files in this
`migrations` folder must be maintained in a certain way and it's good to know
how things work.

The resulting `.vscode/mosaic-sql-migrations.code-snippets` file is generated
based on 2 different sources (combining contents of both):

- from all `.sql` files inside of the `migrations` folders and sub-folders
  (including `before-migration` and `define`)
- from all `.code-snippets` files inside of the `migrations` folders and
  sub-folders, which contains ready-to-use snippets that are not related to any
  single `ax_utils` or `ax_define` sql function. (can be related to no functions
  or to multiple functions)

To be able to generate SQL snippets from sql files - certain conventions must be
followed when creating/modifying sql functions.

In it's simplified form, the function example can look like this:

```SQL
/*-snippet
{
  "body": [
    "SELECT ax_define.function_name('${1:column_name}');"
  ],
  "description": [
    "Description of function that will be visible in SQL snippet"
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_define.function_name(column_name text)
  RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  -- Function logic which is not important in this particular case
END;
$$;
```

The logic that generates the SQL snippet performs the following steps:

- Parses the SQL file into memory and read it line by line
- if line starts with `/*-snippet` - starts recording of lines after it
- if line starts with `snippet-*/` - recording of lines is stopped.
  - Content that is recorder is passed to `JSON.Parse` and converted from string
    to json object
- Reading of lines continues, until a line is found that starts with
  `CREATE OR REPLACE FUNCTION`
  - Logic reads the function schema and name and generates a json object name
    and snippet name. For the example above, it would be
    `Function Name (Ax Define)` and `ax-function-name`
  - Because it's theoretically possible to have a function with same name in
    multiple database schemas, (and we can potentially have
    `ax_utils.function_name` - we preserve schema name in form of `(Ax Define)`
    suffix, since json function name is the one that must be unique, snippet
    names can have duplicates)
- Once these steps are performed - a json snippet object is saved for this
  function and parsing continues anew. The resulting snippet would look like
  this:
  - scope is always `sql` and is attached at the end of the object

```JSON
"Function Name (Ax Define)": {
    "prefix": "ax-function-name",
    "body": [
      "SELECT ax_define.function_name('${1:column_name}');"
    ],
    "description": [
      "Description of function that will be visible in SQL snippet"
    ],
    "scope": "sql"
  },
```

As for `migrations/snippets` files - objects like the one above must be defined
there and files are read as is and merged into resulting
`.vscode/mosaic-sql-migrations.code-snippets` file.

### Development Tips

The way to develop and test the snippets code would look like this:

- If you modify typescript parts of `db-common` - run `yarn dev` for continuous
  updates that will trigger on changes.
  - If only sql or code-snippets files are modified - no need to keep the lib
    running.
- Once adjustments are made - save them and run
  `yarn mosaic generate-vscode-sql-snippets -m` in some backend service in Navy
  repository (that has `@axinom/mosaic-db-common` dependency) or from Navy
  repository root.
- `.vscode/mosaic-sql-migrations.code-snippets` will be updated
- go to any `.sql` file in monorepo (preferably `migrations/current.sql` in one
  of the backend services) and test the added/updated snippet there.

When maintaining comment snippet sections of sql functions, it's good to keep in
mind, that they are fed into `JSON.Parse` as is, and therefore must be valid
json objects. This means that if `yarn mosaic generate-vscode-sql-snippets -m`
fails - chances are - json object is not valid.

For example:

- `SyntaxError: Unexpected token ] in JSON at position 1035` might signify that
  there is an extra `,` added at the end of the last string of the array which
  must be removed.
- Similar error with other token value could signify that some character, for
  example `"` must be escaped like `\"`.
- You might have a regex inside of the snippet that end with `*/`, which will be
  recognized as a closing tag for the comment.
  - In such situation - either modify the regex or move the whole snippet into
    one of the `migrations/snippets` files.

Another thing to keep in mind, is that since `ax_utils` are updated on the fly
in projects whenever a new migration is applied - extra care must be done when
doing changes in `before-migration` files.

- Do not rename parameters of `ax_utils` functions, even if function name stays
  the same. PostgreSQL recognizes that as a breaking change.
  - Acceptable is to rename the column and to add something like
    `DROP FUNCTION IF EXISTS ax_utils.function_name(TEXT, TEXT, TEXT);` before
    definition of affected function. This way, old function will be dropped and
    new property name would be applied. (but keep the number and order of
    parameters the same)
- Do not just delete `ax_utils` functions. Because they can already be used by
  some projects, they must stay, even if they are not used.
  - Removing the snippet comment would make it harder to discover them, so that
    might be a valid option.
  - But modifying the snippet to indicate that ax_utils function is
    deprecated/obsolete is a better option.
  - The only exception to this is early stages of development, where we do not
    have non-axinom projects and are 100% that `ax_utils` function is not used
    in any project.
    - And even in this case, if dropped function was already released - an
      explicit drop statement must be added, e.g.

```SQL
-- TODO: Remove these after 01.01.2023
DROP FUNCTION IF EXISTS ax_utils.function_name(TEXT, TEXT, TEXT);
```

The TODO comment is added to signify when this drop statement can be removed.
Assumption is that one year is enough for a library to be updated at least once
for this statement to be executed in consuming services.

Worse case scenario:

- Someone uses the function that will be dropped in the future.
- That someone updates the library to the latest version where this function no
  longer exists and drop statement is present.
- A bug is introduced and (hopefully) reported to us.
- We provide the code version of dropped function, which can be be explicitly
  added as a script to run before each migration is applied in affected project.
  - example -
    `services/video/service/migrations/after-reset/initial-define-functions.sql`,
    but instead - use it before migrations.

Also, some notes about the contents of snippets comment section:

- Both body and description are string arrays to make it possible to expand both
  sections with more text, while keeping it all on the screen.
  - If text contents are short - both can be changed from arrays of strings to
    just strings.
- Snippets will be displayed differently depending on existence of `\n` inside
  of the `description`.
  - If it exists - line break will be added, but it is possible that text might
    not be visible, curring it at the border of the window with `...`. In this
    case - window must be manually resized for contents to be fully read.
  - If `\n` is not specified - intellisense window will try to wrap the text no
    matter the size and always keep it readable, but it might not look very
    good.
- Formatting capabilities of snippets documentation window is quite limited, so
  it makes sense to always preview it after changes to make sure it is
  acceptable. (perhaps adding a few `\n` line breaks would make it better)