MySQL and SQL Server wire protocols

# 1. Does the tenant report the listener as CONFIGURED?
#    The families sit at the TOP level of the payload, not under a wrapper.
curl -s "https://$OC_HOST/v1/capabilities" \
  | jq '.mysql_wire_dialect.available, .mssql_tds_dialect.available'

# 2. Is a socket actually LISTENING?
#    (1) can say "configured" while (2) refused to bind, so run both -
#    and give the port scan a control, or a silent "no" proves nothing.
nc -vz $OC_SQL_HOST 443      # control: this MUST answer
nc -vz $OC_SQL_HOST 3306     # MySQL wire
nc -vz $OC_SQL_HOST 1433     # TDS
# If the control line fails too, your path is blocked and you have
# measured your own network, not our listener.

# 3. Prove it end to end with a real login, not a port scan.
mysql --host=$OC_SQL_HOST --port=3306 --user=$OC_DB_USER \
      --ssl-mode=REQUIRED -e "SELECT 1"

DENIED: this tenant has database RBAC configured on N object(s) (...).
ANY grant counts

DENIED: per-user login is configured but this tenant has NO enabled
database user with a password verifier, so no session could ever be
admitted. Refusing to bind.

-- Give the tenant at least one ENABLED database user WITH a password
-- verifier, and the per-user listener has something it can admit.
CREATE USER app_service WITH PASSWORD 'generate-a-real-secret-here';
GRANT SELECT, INSERT, UPDATE ON invoices TO app_service;

// Verified against the engine with Sequelize 6.37 on mysql2 3.24.
import { Sequelize, DataTypes } from "sequelize";

const sequelize = new Sequelize(
  process.env.OC_DATABASE,
  process.env.OC_DB_USER,
  process.env.OC_DB_PASSWORD,
  {
    host: process.env.OC_SQL_HOST,
    port: 3306,
    dialect: "mysql",
    dialectModule: require("mysql2"),
    dialectOptions: {
      // Required in practice. See "TLS is not optional here" below.
      ssl: { minVersion: "TLSv1.2", rejectUnauthorized: true },
    },
    // The engine cannot reflect a schema yet, so DEFINE every model
    // explicitly and never call sequelize.sync() to discover one.
    define: { timestamps: false, freezeTableName: true },
  },
);

const Invoice = sequelize.define("invoices", {
  id: { type: DataTypes.INTEGER, primaryKey: true },
  customer_ref: DataTypes.STRING,
  amount_cents: DataTypes.INTEGER,
  status: DataTypes.STRING,
});

await sequelize.authenticate();

// Single-table reads and writes work, transactions included.
const tx = await sequelize.transaction();
try {
  await Invoice.create(
    { id: 4101, customer_ref: "ACME-77", amount_cents: 129900, status: "open" },
    { transaction: tx },
  );
  await Invoice.update({ status: "settled" }, { where: { id: 4101 }, transaction: tx });
  await tx.commit();
} catch (err) {
  await tx.rollback(); // Rollback is honoured; the row does not appear.
  throw err;
}

const open = await Invoice.findAll({
  where: { status: "open" },
  order: [["amount_cents", "DESC"]],
  limit: 50,
});

// NOT served today. Both of these fail against the MySQL listener.

// 1. Schema reflection - there is nothing to read a table shape back from.
await sequelize.getQueryInterface().describeTable("invoices");   // fails
await sequelize.sync();                                          // fails

// 2. Associations - a modelled domain does not load.
Invoice.hasMany(LineItem, { foreignKey: "invoice_id" });
await Invoice.findAll({ include: [LineItem] });                  // fails

// Do this instead: declare every model by hand (as above) and issue the
// second query yourself, joining in your own application code.
const invoices = await Invoice.findAll({ where: { status: "open" } });
const items = await LineItem.findAll({
  where: { invoice_id: invoices.map((i) => i.id) },
});

// Verified with mssql-jdbc 13.4.0 and 12.10.1 - 34 of 34 checks passed.
String url = "jdbc:sqlserver://" + System.getenv("OC_SQL_HOST") + ":1433"
    + ";databaseName=" + System.getenv("OC_DATABASE")
    + ";encrypt=true"                    // mandatory - the listener is TLS-only
    + ";trustServerCertificate=false"
    + ";hostNameInCertificate=" + System.getenv("OC_SQL_HOST")
    + ";loginTimeout=30";

try (Connection conn = DriverManager.getConnection(
        url, System.getenv("OC_DB_USER"), System.getenv("OC_DB_PASSWORD"))) {

    // Prepared statements, including server-side handles and reuse.
    try (PreparedStatement ps = conn.prepareStatement(
            "SELECT id, customer_ref, amount_cents FROM invoices"
          + " WHERE status = ? ORDER BY amount_cents DESC")) {
        ps.setString(1, "open");
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " " + rs.getString("customer_ref"));
            }
        }
    }

    // Batch execution.
    conn.setAutoCommit(false);
    try (PreparedStatement ins = conn.prepareStatement(
            "INSERT INTO invoices (id, customer_ref, amount_cents, status)"
          + " VALUES (?, ?, ?, ?)")) {
        for (Object[] row : rows) {
            ins.setInt(1, (Integer) row[0]);
            ins.setString(2, (String) row[1]);
            ins.setInt(3, (Integer) row[2]);
            ins.setString(4, (String) row[3]);
            ins.addBatch();
        }
        ins.executeBatch();
    }
    conn.commit();
}

// Verified with microsoft/go-mssqldb 1.11.0 - 21 of 21 checks passed.
package main

import (
    "context"
    "database/sql"
    "fmt"
    "net/url"
    "os"

    _ "github.com/microsoft/go-mssqldb"
)

func main() {
    dsn := (&url.URL{
        Scheme: "sqlserver",
        User:   url.UserPassword(os.Getenv("OC_DB_USER"), os.Getenv("OC_DB_PASSWORD")),
        Host:   os.Getenv("OC_SQL_HOST") + ":1433",
        RawQuery: url.Values{
            "database": {os.Getenv("OC_DATABASE")},
            // Mandatory - the listener is TLS-only and validates.
            "encrypt":                {"true"},
            "TrustServerCertificate": {"false"},
        }.Encode(),
    }).String()

    db, err := sql.Open("sqlserver", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    rows, err := db.QueryContext(context.Background(),
        "SELECT id, customer_ref, amount_cents FROM invoices"+
            " WHERE status = @p1 ORDER BY amount_cents DESC",
        sql.Named("p1", "open"))
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id, amount int
        var ref string
        if err := rows.Scan(&id, &ref, &amount); err != nil {
            panic(err)
        }
        fmt.Println(id, ref, amount)
    }
}

# An application written for Oracle Database connects with a PostgreSQL
# driver, on the PostgreSQL wire port. There is no TNS listener to point
# an Oracle client library at.

psql "host=$OC_SQL_HOST port=5432 dbname=$OC_DATABASE user=$OC_DB_USER sslmode=require"

-- Served, and equivalent within the documented scope:
SELECT NVL(discount_pct, 0), NVL2(shipped_at, 'shipped', 'pending') FROM orders;
SELECT seq_invoice.NEXTVAL FROM DUAL;
SELECT sku FROM catalogue MINUS SELECT sku FROM discontinued;

-- Served, but narrower than Oracle Database (see the scope column):
SELECT LEVEL, id, manager_id FROM staff
  START WITH manager_id IS NULL
  CONNECT BY PRIOR id = manager_id;
SELECT DECODE(status, 'O', 'open', 'C', 'closed', 'other') FROM invoices;
MERGE INTO invoices t USING staging s ON (t.id = s.id)
  WHEN MATCHED THEN UPDATE SET t.status = s.status;

-- ROWNUM row caps port, with Oracle's counter semantics kept:
SELECT id FROM invoices WHERE ROWNUM <= 10;          -- becomes a row cap
SELECT id FROM invoices WHERE ROWNUM > 1;            -- returns NOTHING, as in Oracle
SELECT * FROM (                                      -- the pagination idiom, verbatim
  SELECT a.*, ROWNUM rn FROM ( SELECT id, ref FROM invoices ORDER BY id ) a
  WHERE ROWNUM <= 20
) WHERE rn > 10;

-- REFUSED by name, because ROWNUM is assigned BEFORE the sort: this is
-- ten arbitrary rows THEN sorted in Oracle, not the top ten by date.
-- SELECT * FROM invoices WHERE ROWNUM <= 10 ORDER BY created_at;
SELECT * FROM invoices ORDER BY created_at FETCH FIRST 10 ROWS ONLY;

-- NOT served. ROWID has no equivalent; row identity is the PRIMARY KEY.
-- SELECT ROWID, id FROM invoices;                   -- refused