Ahjo

Support for connecting to databases with Azure Active Directory access tokens

Added ability to authenticate with [azure-identity library](https://pypi.org/project/azure-identity/). Azure specific dependencies can be installed with a new platform tag:

`pip install ahjo[azure]`

Ahjo authentication with azure-identity can be enabled by setting the config variable `azure_authentication` to `DefaultAzureCredential`. This setting provides a default TokenCredential authentication flow, defined in detail [here](https://learn.microsoft.com/en-us/dotnet/api/azure.identity.defaultazurecredential?view=azure-dotnet).

Managed identity client id and token url are defined in the ahjo settings as follows:

{
"BACKEND": {
...
"azure_authentication": "DefaultAzureCredential",
"azure_identity_settings": {
"managed_identity_client_id": "managed_identity_id_value", // The client ID of a user-assigned managed identity.
"token_url": "token_url_value" // (default: https://database.windows.net/.default)
}
...
},
...
}


It should be noted that projects created with previous ahjo versions are not compatible with azure-identity based login. To make previously created ahjo projects compatible, you need to update the method `run_migrations_online` in `alembic/env.py` in the following way:


Old env.py
connectable = create_engine(du.create_sqlalchemy_url(conn_info))

New env.py
connectable = du.create_sqlalchemy_engine(
du.create_sqlalchemy_url(conn_info),
conn_info.get("token")
)

Multi-project-build
With one command, you can run actions (or multi-actions) of multiple projects at once:

`ahjo-multi-project-build path/to/config.jsonc`

SQLAlchemy 2.0 support
Ahjo is now compatible with SQLAlchemy 2.0.

Improved error handling in data insert (MSSQL)
By default, set XACT_ABORT ON and NOCOUNT ON when inserting data with ahjo data command.

API key on pipelines running Safety
Added API key when running Safety.

Support for creating db permissions with sqlalchemy
Added new optional variables to config: `db_permissions` and `db_permission_invoke_method`. The SQL script in `database/create_db_permissions.sql` can be executed with sqlalchemy by setting `db_permission_invoke_method` to `sqlalchemy`.
The parameter `db_permissions` can be used if the script is located in a different file path or if it needs to include scripting variables, for example:

"db_permissions": [
{
"source": "./database/permissions/create_db_1_permissions.sql"
},
{
"source": "./database/permissions/create_db_2_permissions.sql",
"variables":
{
"VARIABLE_REF": "VARIABLE_VALUE"
}
}
],
"db_permission_invoke_method": "sqlalchemy" // Available options: "sqlcmd" (default) or "sqlalchemy"


In SQL files, scripting variables are referenced with syntax: `$(VARIABLE_REF)`.

Fix distutils deprecation warning
The distutils package is deprecated and slated for removal in Python 3.12. In this version, disutils copy_tree was changed to shutil copytree.

Update bitbucket pipeline to support Python 3.10
Change python 3.9 image to python 3.10.4.

Default action to drop obsolete database objects
Added a new master action: drop-obsolete.

Increase the length of columns in git_version table
Change the size of Repository and Branch columns to 255.

Enable Ahjo to use JSON as git version file format
From this version onwards, git version information can be retrieved from a json file. The path to the JSON file is specified by "git_version_info_path" configuration parameter. Git version file should contain three attributes: `repository`, `branch` and `commit`.

Calling URL() directly is deprecated
Calling URL() directly is deprecated in SQLAlchemy. In this version, Ahjo uses URL.create() to construct a URL programmatically.

Bug fix: Ahjo fails to update descriptions for table valued functions
Enable Ahjo to update description for table valued functions.

Bug fix: Incorrect padding error
Fix test: test_conn_info.py. Add pad characters at the end of Base64 string.

Bug fix: Running create-db-login can delete existing login for another database
Raise an exception if there exists a different database assigned to the given login name.

Bug fix: Function deploy_sqlfiles fails if directory is not found in data_src
Function sql_files_found returns empty list if directory is not found in data_src.

Support for adding/dropping SQL files listed in --files argument
Added new master actions: `deploy-files` and `drop-files`.

Pin all dependencies to their exact versions
Install only versions that do not have any known vulnerabilities or compatibility problems.

Update git version in data action and create a separate action for it
In previous versions, the git version was updated to the database only as part of the deploy action.
In this version, git version is updated as part of the data action, since sometimes data is updated without running deploy.
In addition, a new master action `update_git_version` has been added so it can run also in other situations when needed.

Change execution order in deploy-action
In previous versions, if you had metadata descriptions defined for git_version-table, they were not updated when the database was built for the first time. This is because git_version table is created after the metadata update.
Solution was to change execution order in deploy-action:

1. update_git_version
2. update_db_object_properties

Bug fix: Tabs in SQL files are converted to spaces in function execute_from_file
Override default behavior of converting tabs to spaces before parsing input string.

Bug fix: Ahjo fails to split sql code to batches if input contains lower-case batch separators
Change mssql batch separator to case insensitive.

Bug fix: Ahjo fails to split sql code to batches if GO statement is indented
Allow space/tab indentations to GO commands

Bug fix: Ahjo fails to split sql code to batches when sql code contains GOTO statement
Allow GOTO statements to be used in sql files.

Bug fix: execute_from_file interprets colon+letters as parameter placeholder
Escape colon characters in execute_from_file (when using mssql+pyodbc dialect).

Platform-specific dependencies
The dependencies of Ahjo have been split into common dependencies and platform-specific dependencies. Command `pip install ahjo` will install only the common dependencies. In order to use Ahjo with the database engine of your choice, install platform-specific dependencies using available tags. For example, if you use Ahjo with Microsoft SQL Server, use tag `mssql` to install required dependencies.

Common dependencies
- alembic
- commentjson
- pyparsing
- sqlalchemy

Platform-specific dependencies (and available platform tags)
- mssql
- pyodbc


this command installs Ahjo and common dependencies
pip install ahjo

this command installs Ahjo, common dependencies and mssql depenendecies (pyodbc)
pip install ahjo[mssql]


Support for three different Azure AD authentication methods
Added new (optional) variable `azure_authentication` to config.

Possible values:
- "ActiveDirectoryPassword" (Username and password)
- "ActiveDirectoryInteractive" (MFA - username given to Ahjo, password and single sign-on code are given interactively to driver)
- "ActiveDirectoryIntegrated" (Similar to windows authentication/trusted connection)

Notice, that Azure AD authentication is fully supported only by the latest ODBC Driver for SQL Server (ODBC Driver 17 for SQL Server).

Partially replace SQLCMD
To fully enable support for Azure AD authentication in Ahjo, SQLCMD had to be at least partially replaced. The problems with SQLCMD included:
- Every single time SQLCMD was called, a new connection and authentication to database was made
- When in use, MFA had to be executed for every single deployed object
- If you mistyped your password, deploy would spam your credentials to database n*n times, where n is the amount of deployed objects
- This sometimes resulted to user to be locked from database
- SQLCMD is a Microsoft SQL Server specific tool

In this version, SQLCMD has been partially replaced with a solution (`ahjo.database_utilities.sqla_utilities.execute_from_file`) that splits SQL stored in file into batches and executes the batches with SQL Alchemy. This solution solves the problems listed above.
- In `deploy_sqlfiles`, the loop logic is the same, but SQLCMD execution (`ahjo.database_utilities.sqlcmd.invoke_sqlcmd`) is replaced with `ahjo.database_utilities.sqla_utilities.execute_from_file`
- `sqlfiles.py`moved from `operations/tsql` to `operations/general`
- No significant effect to end-user, since all operations can be imported from `ahjo.operations`

Type hints
Added type hints to all modules.

Format bulk insert output
Print only two decimals for bulk insert duration.

Lock SQLAlchemy versions
Ahjo is compatible with SQLAlchemy versions >=1.3.0 and <1.4.0. Version 1.4.0 has some bugs that affect the functionality of Ahjo.

We'll start preparing for SQLAlchemy 2.0.