#49 Try to run test against sql server

Codeception · szhajdu · Mar 16, 2023 · Mar 16, 2023 · Mar 16, 2023 · Mar 18, 2023
commit 8d60f38626c2a4f638a3748a727819290e6dc7b5
diff --git a/.github/workflows/main.yml b/.github/workflows/main.yml
@@ -1,5 +1,6 @@
 name: CI
 
+
 on: [push, pull_request]
 
 jobs:
@@ -10,8 +11,8 @@ jobs:
       mysql:
         image: mysql:5.7
         env:
-            MYSQL_ALLOW_EMPTY_PASSWORD: true
-            MYSQL_DATABASE: codeception_test
+          MYSQL_ALLOW_EMPTY_PASSWORD: true
+          MYSQL_DATABASE: codeception_test
         ports:
           - 3306:3306
         options: >-
@@ -31,30 +32,45 @@ jobs:
           --health-retries 5
         ports:
           - 5432:5432
+      sqlsrv:
+        image: mcr.microsoft.com/mssql/server:2019-latest
+        env:
+          SA_PASSWORD: yourStrong(!)Password
+          ACCEPT_EULA: 'Y'
+        ports:
+          - 1433:1433
+        options: >-
+          --health-cmd "/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d master -Q 'SELECT COUNT(*) FROM master.dbo.spt_values;'"
+          --health-interval 10s
+          --health-timeout 5s
+          --health-retries 5
 
     strategy:
       matrix:
         php: [7.4, 8.0]
 
     steps:
-    - name: Checkout code
-      uses: actions/checkout@v2
-
-    - name: Setup PHP
-      uses: shivammathur/setup-php@v2
-      with:
-        php-version: ${{ matrix.php }}
-        extensions: pdo, pgsql, mysql, sqlite
-        coverage: none
-
-    - name: Validate composer.json and composer.lock
-      run: composer validate
-
-    - name: Install dependencies
-      run: composer install --prefer-dist --no-progress --no-interaction --no-suggest
-
-    - name: Run test suite
-      run: php vendor/bin/codecept run
-      env:
-        PGPASSWORD: postgres
-        MYSQL_HOST: 127.0.0.1
+      - name: Create default database for sqlsrv
+        run: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -Q 'CREATE DATABASE codeception_test'
+
+      - name: Checkout code
+        uses: actions/checkout@v2
+
+      - name: Setup PHP
+        uses: shivammathur/setup-php@v2
+        with:
+          php-version: ${{ matrix.php }}
+          extensions: pdo, pgsql, mysql, sqlite, sqlsrv, pdo_sqlsrv, pdo_dblib
+          coverage: none
+
+      - name: Validate composer.json and composer.lock
+        run: composer validate
+
+      - name: Install dependencies
+        run: composer install --prefer-dist --no-progress --no-interaction --no-suggest
+
+      - name: Run test suite
+        run: php vendor/bin/codecept run
+        env:
+          PGPASSWORD: postgres
+          MYSQL_HOST: 127.0.0.1
diff --git a/tests/data/dumps/mssql.sql b/tests/data/dumps/mssql.sql
@@ -0,0 +1,75 @@
+CREATE TABLE [dbo].[groups] (
+    [id] INT NOT NULL IDENTITY(1,1),
+    [name] VARCHAR(100) NULL,
+    [enabled] BIT NULL,
+    [created_at] DATETIME NOT NULL CONSTRAINT DF_groups_created_at DEFAULT GETDATE(),
+    CONSTRAINT PK_groups PRIMARY KEY CLUSTERED ([id] ASC)
+);
+
+INSERT INTO [dbo].[groups]([name],[enabled],[created_at])
+VALUES
+    ('coders', 1, '2012-02-01 21:17:50'),
+    ('jazzman', 0, '2012-02-01 21:18:40');
+
+
+CREATE TABLE [dbo].[users] (
+    [id] INT NOT NULL IDENTITY(1,1),
+    [name] VARCHAR(30) NULL,
+    [email] VARCHAR(255) NULL,
+    [is_active] BIT NOT NULL CONSTRAINT DF_users_is_active DEFAULT 1,
+    [created_at] DATETIME NOT NULL CONSTRAINT DF_users_created_at DEFAULT GETDATE(),
+    CONSTRAINT PK_users PRIMARY KEY CLUSTERED ([id] ASC)
+);
+
+INSERT INTO [dbo].[users]([name],[email],[is_active],[created_at])
+VALUES
+    ('davert','davert@mail.ua', 1, '2012-02-01 21:17:04'),
+    ('nick','nick@mail.ua', 1, '2012-02-01 21:17:15'),
+    ('miles','miles@davis.com', 1, '2012-02-01 21:17:25'),
+    ('bird','charlie@parker.com', 0, '2012-02-01 21:17:39');
+
+
+CREATE TABLE [dbo].[permissions] (
+    [id] INT NOT NULL IDENTITY(1,1),
+    [user_id] INT NULL,
+    [group_id] INT NULL,
+    [role] VARCHAR(30) NULL,
+    CONSTRAINT PK_permissions PRIMARY KEY CLUSTERED ([id] ASC),
+    CONSTRAINT FK_permissions FOREIGN KEY ([group_id]) REFERENCES [dbo].[groups] ([id]) ON DELETE CASCADE,
+    CONSTRAINT FK_users FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([id]) ON DELETE CASCADE
+);
+
+INSERT INTO [dbo].[permissions]([user_id],[group_id],[role])
+VALUES
+    (1,1,'member'),
+    (2,1,'member'),
+    (3,2,'member'),
+    (4,2,'admin');
+
+
+CREATE TABLE [dbo].[order] (
+    [id] INT NOT NULL IDENTITY(1,1),
+    [name] VARCHAR(255) NOT NULL,
+    [status] VARCHAR(255) NOT NULL,
+    CONSTRAINT PK_order PRIMARY KEY CLUSTERED ([id] ASC)
+);
+
+INSERT INTO [dbo].[order]([name],[status]) VALUES ('main', 'open');
+
+
+CREATE TABLE [dbo].[composite_pk] (
+    [group_id] INT NOT NULL,
+    [id] INT NOT NULL,
+    [status] VARCHAR(255) NOT NULL,
+    CONSTRAINT PK_composite_pk PRIMARY KEY CLUSTERED ([group_id] ASC, [id] ASC)
+);
+
+CREATE TABLE [dbo].[no_pk] (
+    [status] varchar(255) NOT NULL
+);
+
+CREATE TABLE [dbo].[empty_table] (
+    [id] int NOT NULL IDENTITY(1,1),
+    [field] varchar(255),
+    CONSTRAINT [PK_empty_table] PRIMARY KEY CLUSTERED ([id])
+);
diff --git a/tests/unit/Codeception/Module/Db/MssqlFreeTdsDbTest.php b/tests/unit/Codeception/Module/Db/MssqlFreeTdsDbTest.php
@@ -0,0 +1,34 @@
+<?php
+
+declare(strict_types=1);
+
+namespace unit\Codeception\Module\Db;
+
+use AbstractDbTest;
+use Codeception\Configuration;
+
+require_once Configuration::testsDir() . 'unit/Codeception/Module/Db/AbstractDbTest.php';
+
+/**
+ * @group db
+ */
+final class MssqlFreeTdsDbTest extends AbstractDbTest
+{
+    public function getPopulator(): string
+    {
+        return '/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P yourStrong(!)Password -d codeception_test -i tests/data/dumps/mssql.sql';
+    }
+
+    public function getConfig(): array
+    {
+        return [
+            'dsn'       => 'dblib:host=localhost;dbname=codeception_test',
+            'user'      => 'sa',
+            'password'  => 'yourStrong(!)Password',
+            'dump'      => 'tests/data/dumps/mssql.sql',
+            'reconnect' => true,
+            'cleanup'   => true,
+            'populate'  => true,
+        ];
+    }
+}
diff --git a/tests/unit/Codeception/Module/Db/MssqlSqlSrvDbTest.php b/tests/unit/Codeception/Module/Db/MssqlSqlSrvDbTest.php
@@ -0,0 +1,34 @@
+<?php
+
+declare(strict_types=1);
+
+namespace unit\Codeception\Module\Db;
+
+use AbstractDbTest;
+use Codeception\Configuration;
+
+require_once Configuration::testsDir() . 'unit/Codeception/Module/Db/AbstractDbTest.php';
+
+/**
+ * @group db
+ */
+final class MssqlSqlSrvDbTest extends AbstractDbTest
+{
+    public function getPopulator(): string
+    {
+        return '/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P yourStrong(!)Password -d codeception_test -i tests/data/dumps/mssql.sql';
+    }
+
+    public function getConfig(): array
+    {
+        return [
+            'dsn'       => 'sqlsrv:Server=localhost;Database=codeception_test',
+            'user'      => 'sa',
+            'password'  => 'yourStrong(!)Password',
+            'dump'      => 'tests/data/dumps/mssql.sql',
+            'reconnect' => true,
+            'cleanup'   => true,
+            'populate'  => true,
+        ];
+    }
+}