Add detection for transaction statements

[not-night-but]

Add detection for transaction statements like BEGIN TRANSACTION, START TRANSACTION, COMMIT, ROLLBACK, etc. I am probably missing some corner cases here and there may be some syntax I'm not aware of but this seems to work

[azmy60]

@not-night-but Is this ready for merging? Is it possible to fix the lint build?

[not-night-but]

@azmy60 I'm not sure why the build is failing, it runs fine on my machine, the errors in the pipeline are from node modules, and I can't rerun the workflow to check if gh is just being stupid

[MasterOdin]

I've pinned the version of @types/node in #84 which seems to have resolved the lint issue on CI, which I'm guessing was from it pulling in a very new version of the dependency that somehow clashed with the version of node running it or something. 🤷

[not-night-but]

Alrighty I'll look into it and address your feedback. Thanks for the review!

[rathboma]

@not-night-but Does this support all START/BEGIN syntaxes?

Disclaimer: I had AI help me with these, need double checking

-- ANSI-ish / generic
START TRANSACTION;              -- Most portable explicit form
BEGIN;                          -- Very common, but overloaded in some dialects
BEGIN TRANSACTION;              -- Accepted by several engines

-- MySQL / MariaDB
-- https://dev.mysql.com/doc/refman/8.4/en/commit.html
START TRANSACTION;
BEGIN;                          -- Alias
BEGIN WORK;                     -- Alias
START TRANSACTION READ ONLY;    -- With characteristics
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- PostgreSQL (and Redshift, CockroachDB, YugabyteDB)
-- https://www.postgresql.org/docs/current/sql-start-transaction.html
BEGIN;
START TRANSACTION;
BEGIN TRANSACTION;
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SQL Server (T-SQL)
-- https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver17
BEGIN TRANSACTION;
BEGIN TRAN;                     -- Shorthand
BEGIN TRANSACTION txn_name;     -- Named transaction

-- SQLite
-- https://sqlite.org/lang_transaction.html
BEGIN;                          -- Deferred by default
BEGIN TRANSACTION;
BEGIN DEFERRED TRANSACTION;
BEGIN IMMEDIATE TRANSACTION;
BEGIN EXCLUSIVE TRANSACTION;

-- Oracle
-- No explicit BEGIN TRANSACTION.
-- Transaction starts implicitly on first DML.
SET TRANSACTION READ ONLY;      -- You may set characteristics
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Snowflake
-- https://docs.snowflake.com/en/sql-reference/transactions#explicit-transactions
BEGIN;
BEGIN TRANSACTION;
START TRANSACTION;

-- BigQuery (scripting)
BEGIN;
BEGIN TRANSACTION;

-- Firebird
-- Started via client API normally; SQL uses SET TRANSACTION.
SET TRANSACTION READ WRITE;

-- MS Access / Jet SQL
BEGIN TRANSACTION;

[rathboma]

Most of these might already be captured, but we probably need tests either way

[not-night-but]

@rathboma Added those test cases, looks like it all works. One thing to note though is that specifically for mysql dbs, Those aliases (BEGIN and BEGIN WORK) do not work as transactions within procedures, functions, triggers, and events, but work as the beginning of compound statements. I'm not sure if this will be an issue for us as that whole statement is just identified as a CREATE_PROCEDURE statement, etc but I wanted to at least bring it up