Ahjo

Build system for Ahjo depends on setuptools which has a ReDoS vulnerability CVE-2022-40897 in versions prior to 65.5.1. In this version, setuptools requirement version was updated to 65.5.1.

Metadata update speedup
Metadata deployment is now 95% faster. The speedup is achieved by reducing the number of queries to database.

Enable optimized ORM bulk insert by default
[SQLAlchemy's optimized ORM bulk insert](https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html#optimized-orm-bulk-insert-now-implemented-for-all-backends-other-than-mysql) is now enabled by default. Previously, the feature was disabled because it was incompatible with ahjo's bulk insert implementation. The incompatibility has been resolved.

SQLAlchemy logging
Added support for [SQLAlchemy logging](https://docs.sqlalchemy.org/en/20/core/engines.html#configuring-logging). The feature can be enabled by setting `enable_sqlalchemy_logging` to `true` in the config file.

Pre-commit hook installation parameterized
Git pre-commit hook can now be installed with user-defined paths to scan rules and ignore rules files.

Regression fix: Git version query fails if Timestamp column is not found in git version table
Fixed a regression where the git version query fails if the Timestamp column is not found in the git version table.

Updates to ahjo scan
- New search rules: `sql_object_modification`, `alembic_table_modification`, `sql_insert` and `email`
- Added ability to define custom search rules with regex patterns
- Scan results can be ignored based on rules or matches
- Added command for scan config initialization
- The structure of the ignore file has changed
- An example of the new structure can be found in the `README.md` file
- Scan rules are now defined in a YAML file

Custom command-line arguments for actions
Added ability to pass custom command-line arguments to actions. The arguments are accessible from the context object in the action.

New arguments for ahjo command
Added new arguments `--skip-metadata-update`, `--skip-alembic-update` and `--skip-git-update` to ahjo command. These arguments can be used to skip updating documented extended properties to database, running alembic migrations and updating current git version to git version table.

Timestamp column to git table
Added a new column `Timestamp` to git table. The column holds the timestamp of the commit. The timestamp is updated when the git version is updated to the git table.

Updates to pipelines
- Added a new pipeline for running database tests
- Run ahjo build & sign pipeline only when a tag is created
- MSI installer for both user and system targets
- Added automatic file transfer from ADO to Teams
- Fixed Safety check command in pipeline

Ability to connect with SQLAlchemy URL
Added support for connecting to database with [SQLAlchemy URL](https://docs.sqlalchemy.org/en/20/core/engines.html#database-urls). The URL can be used to define more complex connection settings, e.g. [pyodbc connection attributes](https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver16). Use `sqlalchemy.url` setting in config file to define the URL. If the URL is defined, it overrides the values of `dialect`, `sql_port`, `sql_driver`, `target_server_hostname` and `target_database_name`.

Support for SQLAlchemy engine configuration
Previously ahjo was hard-coded to use SQLAlchemy's default engine configuration. Now it is possible to customize the engine with SQLAlchemy's [create_engine](https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.create_engine) function parameters. The parameters can be defined in config file under `sqlalchemy.*` key. For example `sqlalchemy.pool_size: 10` is passed as pool_size=10 to `create_engine` function.

Support for SQLAlchemy URL query configuration
SQLAlchemy's class method `sqlalchemy.engine.URL.create` accepts a [query](https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.engine.URL.create.params.query) dictionary to be passed to the dialect and/or the DBAPI upon connect. The dictionary can be defined in ahjo config file under `sqla_url_query_map` key. The dictionary can be used for example to define [pyodbc connection attributes](https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver16).

ODBC Driver 18 for SQL Server support
In previous versions, support for ODBC Driver 18 for SQL Server was limited due to hard-coded connection attributes. Now this has been fixed and the driver is supported.

The ODBC Driver 18.0 allows users to send long data types as max data types with the `LongAsMax` connection attribute. In ahjo, this is set to `Yes` by default.

**Notice that the connection encryption defaults have changed in ODBC Driver 18 for SQL Server.** The default value for `Encrypt` is `yes` and the default value for `TrustServerCertificate` is `no`. This means that the driver will encrypt the connection by default and it will not trust the server certificate by default. If you want to use the old defaults, e.g. in development environment, you need to define `Encrypt=no` and/or `TrustServerCertificate=yes` in `sqla_url_query_map` or `sqlalchemy.url` settings in config file.

Deprecated config parameters
`odbc_trust_server_certificate` and `odbc_encrypt` settings are deprecated and will be removed in the future. Use `sqla_url_query_map` or `sqlalchemy.url` settings in config file instead.

Build & Sign pipeline for ahjo MSI installation package
Added a build & sign pipeline for ahjo MSI installation package. The pipeline builds the package and signs it with a certificate. The signed package is published to Azure Artifacts.

Azure-identity to ahjo MSI installation package
In previous version of ahjo MSI installation package, azure-identity was not included. This caused an error when trying to use azure-identity authentication if ahjo was installed with MSI package. This has now been fixed.

Regression fix: UnboundLocalError in drop_sqlfile_objects
Fixed a regression bug where `UnboundLocalError` was raised when `drop_sqlfile_objects` was called.

Regression fix: git version table is not updated when using git version info file
In previous version, git version table was not updated when using git version info file. This has now been fixed.

Disable pyodbc pooling
Disabled pyodbc pooling by default since SQLAlchemy has its own pooling behavior. See [Pyodbc Pooling / connection close behavior](https://docs.sqlalchemy.org/en/20/dialects/mssql.html#pyodbc-pooling-connection-close-behavior) for more information.

Close SQLAlchemy connection if an error occurs
If an error occurs when running an action, the SQLAlchemy connection is now closed so that the connection is not left open.

MSI installer
Ahjo can now be installed with MSI installer. Currently, the installer is not available in public, but it can be built with the instructions in `README.md`. The installation package installs everything that is needed to execute ahjo shell commands including the required parts of the Python runtime setup. In other words, the target environment doesn't need to have Python installed and there is no need to create separate venvs for ahjo.

Ahjo scan
Added ability to scan files in ahjo project with search rules. Currently this feature can be used to search for Finnish Personal Identity Numbers (hetu), but later on it can be used to search for other patterns as well.

Git pre-commit hook for ahjo scan
Added ability to run ahjo scan as a pre-commit hook in git. It can be used to prevent committing files that contain sensitive information to git repository. To install the pre-commit hook, run `ahjo-install-git-hook` in the root of the ahjo project.

Windows Event Logging
Extended logging to Windows Event Log. This feature can be utilized for Azure Monitor activities, for example. To enable logging to Windows Event Log, set `windows_event_log` to `true` in ahjo config file.

Support for yaml config format
Ahjo config file can now be in yaml format. The old json or jsonc format is still supported. Ahjo config file can be converted from json/jsonc to yaml or vice versa with `ahjo-config` command.

Ability to import ahjo actions from different files
By default, ahjo actions are imported from `ahjo_actions.jsonc` file. Now it is possible to import actions from different files as well. It can be useful for example when one wants to use different actions for different environments or separate actions that are compatible with MSI-installed ahjo from actions that are compatible with pip-installed ahjo.

Upper and lower limits to requirements
`setup.cfg` requirements are no longer pinned to specific versions. Instead, upper and lower limits are set to requirements.

Collation check only if database exists and ahjo action affects database
Ahjo checks collation info only if database exists and ahjo action affects database. This prevents unnecessary collation checks when ahjo action does not affect database.

Bug fix: database name with hyphens
Fixed a bug where creating a new database fails when the database name contains hyphens.

ahjo-upgrade status code
Ahjo-upgrade now returns status code 1 if upgrade fails. This can be used to check if upgrade was successful or not, for example in pipelines.

Safety check only relevant packages in Bitbucket pipeline
Updated Bitbucket pipeline to check only ahjo specific packages.

Python 3.12 support
Added Python 3.12 support. Updated the following packages in `setup.cfg`:
- sqlalchemy 2.0.4 -> 2.0.22
- PyYAML 6.0 -> 6.0.1
- pyodbc 4.0.39 -> 5.0.1
- azure-identity 1.6.0 -> 1.14.1

Remove commentjson optional dependency
`commentjson` library has not had updates for few years and does not have pre-built wheels available.
Therefore it has been copied to ahjo package and is no longer an optional dependency.

Add more details to update-db-object-prop error message
Added more details to error message when database object property update fails.

Update file object properties bugfix
Due to refactoring related to v3.3.0 changes, `update_file_object_properties` function did not work correctly if optional parameter `schema_list` was not given. This has been fixed.