RenewCMS/features/postgres_database_driver.feature

Feature: Optional PostgreSQL database driver
  An operator who prefers PostgreSQL runs RenewCMS against it by setting
  environment variables. SQLite stays the default, and MySQL/MariaDB behaves
  exactly as before.

  Background:
    Given the database drivers are registered by name in database.drivers
    And the database is chosen by the DB_DRIVER environment variable
    And repositories, use cases and handlers are unchanged

  Scenario Outline: Driver selection
    Given DB_DRIVER is "<value>"
    When the server starts
    Then the outcome is "<outcome>"

    Examples:
      | value      | outcome                                                  |
      | unset      | the sqlite driver opens DB_FILE                          |
      | sqlite     | the sqlite driver opens DB_FILE                          |
      | mysql      | the mysql driver opens the DB_HOST connection            |
      | postgres   | the postgres driver opens the DB_HOST connection         |
      | postgresql | the postgres driver opens the DB_HOST connection         |
      | oracle     | startup fails with: unsupported database driver "oracle" |

  Scenario Outline: Missing PostgreSQL variable
    Given DB_DRIVER is "postgres"
    And "<missing>" is not set
    When the environment is validated
    Then startup fails with "Environment variable <missing> is not set"

    Examples:
      | missing     |
      | DB_HOST     |
      | DB_USER     |
      | DB_PASSWORD |
      | DB_NAME     |

  Scenario: SQLite and MySQL do not require PostgreSQL-only variables
    Given DB_DRIVER is "sqlite" or "mysql"
    And DB_SSLMODE is "maybe"
    When the settings are read
    Then DB_SSLMODE is ignored and validation succeeds

  Scenario Outline: Connection string built from variables
    Given DB_HOST "<host>", DB_PORT "<port>", DB_USER "app", DB_NAME "cms", DB_SSLMODE "<sslmode>"
    When the PostgreSQL DSN is built
    Then the DSN targets "<host>:<expectedPort>" and database "cms"
    And it sets sslmode "<expectedSslmode>"
    And it sets the session time zone to UTC
    And a password containing spaces, quotes, "@" or "/" is escaped so that parsing the DSN returns it unchanged

    Examples:
      | host     | port | expectedPort | sslmode     | expectedSslmode |
      | db.local |      | 5432         |             | disable         |
      | db.local | 5433 | 5433         | require     | require         |
      | pg       | 5432 | 5432         | verify-full | verify-full     |

  Scenario: Invalid SSL mode is rejected
    Given DB_DRIVER is "postgres" and DB_SSLMODE is "maybe"
    When the settings are read
    Then startup fails naming DB_SSLMODE and the accepted values

  Scenario Outline: Fail fast when the server cannot be used
    Given DB_DRIVER is "postgres" and "<fault>"
    When the server starts
    Then startup stops with "Unable to open the database"
    And the message names the host and port
    And the message does not contain the DB_PASSWORD value

    Examples:
      | fault                                             |
      | nothing listens on DB_PORT                        |
      | the password is wrong                             |
      | the database does not exist                       |
      | the server requires TLS and DB_SSLMODE is disable |

  Scenario: Data directory with PostgreSQL
    Given DB_DRIVER is "postgres" and DATA_DIR is "/data"
    Then update state, staging and backups live in "/data"
    And DB_FILE is ignored

  Scenario: Connection pool
    Given DB_MAX_OPEN_CONNS 4, DB_MAX_IDLE_CONNS 2 and DB_CONN_MAX_LIFETIME_SECONDS 60
    When the PostgreSQL connection is opened
    Then the pool limits are 4 open, 2 idle and 60 seconds

  Scenario: Migration on PostgreSQL
    Given an empty PostgreSQL database
    When database.Migrate runs
    Then the tables articles, images, users, invites, api_keys and purge_settings exist in schema "public"
    And the unique indexes on users and api_keys exist
    And the first user lacking a role becomes "owner" and the others "admin"
    And running database.Migrate a second time changes nothing

  Scenario: Existing drivers are untouched by the PostgreSQL addition
    Given a SQLite file holding rows, and a MySQL database holding rows
    When database.Migrate runs on each
    Then the schema and every row are unchanged on both

  Scenario Outline: Repository behaviour is the same on every driver
    Given the "<driver>" driver
    Then the repository contract "<case>" holds

    Examples:
      | driver   | case                                                                  |
      | postgres | create user and read it back with its id and timestamps               |
      | postgres | username "Bob" and "bob" are distinct users                           |
      | postgres | email "A@x.io" and "a@x.io" follow the same rule as on sqlite         |
      | postgres | duplicate email returns an error and stores nothing                   |
      | postgres | the failed duplicate insert leaves the connection usable              |
      | postgres | unknown id returns domain ErrUserNotFound                             |
      | postgres | accept invite twice: the second returns ErrInviteNotFound             |
      | postgres | accept invite with a taken username leaves the invite unaccepted      |
      | postgres | renew invite with unchanged values still succeeds                     |
      | postgres | duplicate image hashes are grouped and counted                        |
      | postgres | media search treats % and _ literally                                 |
      | postgres | media search is case-sensitive exactly as on sqlite                   |
      | postgres | article body of 1 MB round-trips                                      |
      | postgres | article title with emoji and accents round-trips                      |
      | postgres | zero reset expiration round-trips as a zero time                      |
      | postgres | stored times read back in UTC                                         |
      | postgres | purge settings save, then SaveLastRun with identical values succeeds  |
      | postgres | api key unique hashed_key, revoke and last_used                       |
      | postgres | offline article reads as not found publicly, online article is listed |
      | postgres | listing order and pagination match sqlite for the same data           |

  Scenario Outline: Backup before an update
    Given the "<driver>" driver with a populated database
    When the pre-update backup runs
    Then a file "<pattern>" exists in DATA_DIR/backups
    And only the 3 newest backups are kept
    And a failure returns an error, so the update download fails instead of claiming a backup

    Examples:
      | driver   | pattern             |
      | sqlite   | renewcms-<time>.db  |
      | mysql    | renewcms-<time>.sql |
      | postgres | renewcms-<time>.sql |

  Scenario: PostgreSQL backup content
    Given a PostgreSQL database with 2 users and 1 article
    When the pre-update backup runs
    Then the dump contains the schema, indexes and the rows of every table
    And it does not contain the DB_PASSWORD value
    And replaying it into an empty database restores the same rows
    And sequences are reset so the next insert gets an unused id

  Scenario: Secrets are never exposed
    Given DB_DRIVER is "postgres"
    Then no log line, error message or file in the repository contains the DB_PASSWORD value

  Scenario: Static builds
    Then the module builds with CGO_ENABLED=0 for the 6 task release targets