alan-turing-institute / alan-turing-institute/sqlsynthgen

Add SQL Server Functional Tests

Open
#151 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
12
Forks
4
PR merge metrics
No merged PRs in 30d

Description

I have manually verified that SSG works with SQL Server but only for a simple case, locally on MacOS. It would be better to automate this as a form of documentation and to guard against regressions.

## Proposal

We could set up a simple SQL Server service in a GH workflow using [this](https://hub.docker.com/_/microsoft-mssql-server) image and use `sqlcmd` to set up a src database in much the same way as we use `psql` and the `src.dump` file.

The command would be something like:

```shell
sqlcmd -S 127.0.0.1 -C -U sa -P somepassword < sql_server.sql
```

where `sql_server.sql` could be:

```sql
create table tableone (
idcol int primary key,
othercol varchar(255)
);
go
```

We may also want to change the pytest marks so that we have Postgres and SQLServer (and MariaDB?) env vars rather than the single `REQUIRES_DB` setting.

### Steps

- GitHub:
- [ ] Set up SQL Server service.
- [ ] [Install](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver16&tabs=redhat-install#docker) `sqlcmd` and ODBC driver.
- [ ] Run SQL file to create table.
- [ ] Install pyodbc Python library.
- [ ] Split REQUIRES_DB setting into DBMS-specific options.
- [ ] Add functional test for SQL Server.
- [ ] *Optional* Do the same for MariaDB and SQLite.

Contributor guide

Open the contributing guide

Research direction

Start by inspecting the existing GitHub workflow and the current psql/src.dump setup, then trace how the pytest REQUIRES_DB setting selects database-dependent tests. Add the SQL Server service, sqlcmd/ODBC and pyodbc setup, a sql_server.sql schema, and a functional test; done means the workflow runs the SQL Server test successfully.

Written by the indexing model from the issue text.

Assessment

Tech stack
docker, github-actions, python, sql
Domain
ci-cd, databases, testing-qa
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.