checkSqlQueries function

Future<List<Map<String, Object?>>> checkSqlQueries(SqlDatabase<Backend> db, GeneratedQueries generated)

Compile each declared query on one backend, without executing its SELECT. SQLite, MySQL and MariaDB report structure only. PostgreSQL additionally checks native storage families. These checks do not prove expression nullability or custom codecs.

Implementation

Future<List<Map<String, Object?>>> checkSqlQueries(
  SqlDatabase<Backend> db,
  GeneratedQueries generated,
) => db.session((session) async {
  final reports = <Map<String, Object?>>[];
  for (final query
      in (generated.manifest['queries']! as List)
          .cast<Map<String, Object?>>()) {
    final source = (query['sql']! as Map<String, Object?>)[db.dialect.name];
    if (source == null) continue;
    final parameters = (query['parameters']! as List)
        .cast<Map<String, Object?>>();
    final columns = (query['columns']! as List).cast<Map<String, Object?>>();
    final command = SqlTemplate(source as String, dialect: db.dialect).compile(
      db.capabilities,
      {
        for (final parameter in parameters)
          parameter['name']! as String: value<Object?>(
            null,
            Codec<Object?>(parameter['type']! as String, (v) => v, (v) => v),
          ),
      },
    );
    String quote(String name) => isMysqlDialect(db.dialect)
        ? '`${name.replaceAll('`', '``')}`'
        : '"${name.replaceAll('"', '""')}"';
    final alias = quote('_orm_check');
    final projected =
        'SELECT ${columns.map((c) => '$alias.${quote(c['name']! as String)}').join(', ')} '
        'FROM (\n${command.sql}\n) AS $alias';
    List<String>? nativeTypes;
    try {
      if (isMysqlDialect(db.dialect)) {
        final name =
            '_orm_check_${DateTime.now().microsecondsSinceEpoch}_${_queryCheckSerial++}';
        var prepared = false;
        await session.execute(SqlCommand('SET @$name = ?', [projected]));
        try {
          // EXPLAIN can execute stored functions while planning a derived
          // table (verified on MariaDB 11.8). PREPARE resolves the projection
          // and placeholders without executing the application statement.
          await session.execute(
            SqlCommand('PREPARE ${quote(name)} FROM @$name'),
          );
          prepared = true;
        } finally {
          try {
            if (prepared) {
              await session.execute(
                SqlCommand('DEALLOCATE PREPARE ${quote(name)}'),
              );
            }
            await session.execute(SqlCommand('SET @$name = NULL'));
          } catch (_) {
            // A failed cleanup must not leave a prepared statement or query
            // text attached to a connection returned to another borrower.
            await session.discard();
            rethrow;
          }
        }
      } else if (db.dialect == SqlDialect.sqlite) {
        await session.execute(
          SqlCommand('EXPLAIN $projected', command.parameters),
        );
      } else {
        final name =
            '_orm_check_${DateTime.now().microsecondsSinceEpoch}_${_queryCheckSerial++}';
        await session.execute(
          SqlCommand('PREPARE ${quote(name)} AS $projected'),
        );
        try {
          final result = await session.execute(
            SqlCommand(
              r'SELECT pg_catalog.format_type(t.kind::oid, NULL) '
              r'FROM pg_catalog.pg_prepared_statements p, '
              r'unnest(p.result_types) WITH ORDINALITY AS t(kind, position) '
              r'WHERE p.name = $1 ORDER BY t.position',
              [name],
            ),
          );
          nativeTypes = [for (final row in result.rows) row.single as String];
          if (nativeTypes.length != columns.length) {
            throw const GenerationException(
              'Database returned a different result shape.',
            );
          }
          for (var i = 0; i < columns.length; i++) {
            if (!_queryStorageMatches(
              columns[i]['type']! as String,
              nativeTypes[i],
            )) {
              throw GenerationException(
                '${columns[i]['name']}: declared ${columns[i]['type']}, database returns ${nativeTypes[i]}. Use an explicit SQL cast or matching codec storage.',
              );
            }
          }
        } finally {
          await session.execute(SqlCommand('DEALLOCATE ${quote(name)}'));
        }
      }
    } catch (error) {
      throw GenerationException(
        '${query['name']} (${db.dialect.name}): $error',
      );
    }
    reports.add({
      'name': query['name'],
      'dialect': db.dialect.name,
      'structureChecked': true,
      'storageTypesChecked': nativeTypes != null,
      'nullabilityChecked': false,
      'columns': [
        for (var i = 0; i < columns.length; i++)
          {
            ...columns[i],
            if (nativeTypes != null) 'nativeType': nativeTypes[i],
          },
      ],
    });
  }
  if (reports.isEmpty) {
    throw const GenerationException('No named queries target this database.');
  }
  return reports;
});