Source code for ferrosoft.ops.sql

import csv
from subprocess import PIPE
from uuid import UUID

from ferrosoft.ops import commands




def tenant_database_name(tenant_id: UUID):
    return "fpm_%s" % str(tenant_id).replace("-", "_")





def create_tenant_role_command(role_name: str, password: str, member_role: str) -> str:
    return "CREATE ROLE %s LOGIN PASSWORD '%s' ROLE %s" % (
        role_name,
        password,
        member_role,
    )





def create_tenant_database_command(database_name: str, owner_role: str):
    return "CREATE DATABASE %s WITH OWNER = %s" % (
        database_name,
        owner_role,
    )





def reassign_owner(new_owner: str):
    return "REASSIGN OWNED BY current_user TO %s" % new_owner





def tenant_host_update_command(hostname: str) -> str:
    return "UPDATE ferrobase_tenant SET database_hostname = '%s'" % hostname





def sql_select(query: str, psql_executable: str = "psql"):
    """
    Executes a SQL query and yields the result rows.

    This reads the whole result set into a string buffer,
    therefore should only be used for results expected to be small.

    Args:
        query: SELECT string
        psql_executable: psql executable file name

    """
    result = commands.run(
        [
            psql_executable,
            "--csv",
            # disables header row
            "-t",
            "-c",
            query,
        ],
        stdout=PIPE,
        encoding="utf-8",
        errors="replace",
    )

    csv_reader = csv.reader(result.stdout.splitlines())
    for row in csv_reader:
        yield row