model.py

import uuid

from psycopg2 import IntegrityError
from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    Integer,
    String,
    Table,
    UniqueConstraint,
)
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.sql import func, select

from factory import metadata

mBlueprint = Table(
    'blueprints',
    metadata,
    Column('uuid', UUID, primary_key=True),
    # repository is composed with the registry name also
    Column('repository', String(100)),
    Column('name', String(50)),
    Column('tag', String(10)),
    Column('link', String(255), nullable=True),
    Column('description', String(255)),
    Column('public', Boolean, default=False),
    Column('created_at', DateTime(timezone=True),
           server_default=func.now()),
    Column('user_id', Integer, nullable=True),

    # each image should be unique
    UniqueConstraint('repository', 'name', 'tag'),
)


async def join_blueprints_with(model, user_id: str, db):
    query = select([
        model,
        mBlueprint.c.name.label("blueprint_name"),
        mBlueprint.c.repository.label("blueprint_repository"),
        mBlueprint.c.tag.label("blueprint_tag"),
    ])\
        .where(model.c.user_id == user_id)\
        .select_from(
        model
        .outerjoin(mBlueprint, model.c.blueprint_uuid == mBlueprint.c.uuid)
    )
    async with db.acquire() as conn:
        result = await conn.execute(query)
        rows = await result.fetchall()
return rows