<reponame>cedadev/cloudhands-common<gh_stars>0
#!/usr/bin/env python3
#   encoding: UTF-8

from sqlalchemy import Boolean
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import String
from sqlalchemy import UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Table
from sqlalchemy.types import CHAR
from sqlalchemy.orm import relationship

Base = declarative_base()
metadata = Base.metadata


__doc__ = """
The schema module defines tables in the common database
"""


class Group(Base):
    __tablename__ = "groups"

    id = Column("id", Integer, nullable=False, primary_key=True)
    uuid = Column("uuid", CHAR(length=32), nullable=False)
    name = Column("name", String(length=32), nullable=False, unique=True)
    number = Column("number", Integer, nullable=False, unique=True)

    accesses = relationship("Access")


class Organisation(Base):
    __tablename__ = "organisations"

    id = Column("id", Integer, nullable=False, primary_key=True)
    uuid = Column("uuid", CHAR(length=32), nullable=False)
    name = Column("name", String(length=64), nullable=False, unique=True)

    appliances = relationship("Appliance")
    memberships = relationship("Membership")
    subscriptions = relationship("Subscription", cascade="all, delete")
    catalogue = relationship("CatalogueItem")


class CatalogueItem(Base):
    """
    This table stores the details of an appliance as advertised to the user.
    """
    __tablename__ = "catalogueitems"

    uuid = Column("uuid", CHAR(length=32), nullable=False)
    name = Column(
        "name", String(length=32),
        nullable=False, primary_key=True, unique=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"))
    description = Column(
        "description", String(length=64), nullable=False)
    note = Column(
        "note", String(length=1024), nullable=True)
    logo = Column(
        "logo", String(length=32), nullable=True)
    natrouted = Column("natrouted", Boolean, nullable=False)
    
    organisation = relationship("Organisation")


class Artifact(Base):
    """
    This is the base table for all artifacts (products) of the system.

    Artifacts are created with globally unique `uuids` so their identity can
    be maintained across sharded databases.

    Concrete classes define their own tables according to SQLAlchemy's
    `joined-table inheritance`_.

    """
    __tablename__ = "artifacts"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    typ = Column("typ", String(length=32), nullable=False)
    uuid = Column("uuid", CHAR(length=32), nullable=False)
    model = Column("model", String(length=32), nullable=False)
    changes = relationship("Touch", order_by="Touch.at")

    __mapper_args__ = {
        "polymorphic_identity": "artifact",
        "polymorphic_on": typ}


class Access(Artifact):
    """
    """
    __tablename__ = "accesses"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    group_id = Column("group_id", Integer, ForeignKey("groups.id"),
        nullable=False)
    role = Column("role", String(length=32), nullable=False)

    group = relationship("Group")

    __mapper_args__ = {"polymorphic_identity": "access"}


class Appliance(Artifact):
    """
    An Appliance is a computational asset. An appliance is created from an item
    in a catalogue. It is provisioned on one or more nodes, with a configured
    network. The correct data sources must be mounted and the right users given
    access.

    The Appliance record carries links to the
    :py:class:`~cloudhands.common.schema.CatalogueChoice` made by the user and
    the :py:class:`~cloudhands.common.schema.Label` information it was given.

    Once activated, other allocated resources are stored against this artifact,
    such as :py:class:`~cloudhands.common.schema.Node` and
    :py:class:`~cloudhands.common.schema.IPAddress`.

    Periodically a :py:class:`~cloudhands.common.schema.ProviderReport` is
    stored against the appliance to confirm its status.
    """
    __tablename__ = "appliances"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"),
        nullable=False)

    organisation = relationship("Organisation")

    __mapper_args__ = {"polymorphic_identity": "appliance"}


class Membership(Artifact):
    """
    No user may interact with the system without specific membership
    privileges. The user accumulates credentials against a
    membership record. The membership defines the role a user may operate
    within an `organisation`.
    The membership may be revoked by an administrator, or by the system
    automatically if there is no activity within a particular
    :py:class:`~cloudhands.common.schema.TimeInterval`.
    """
    __tablename__ = "memberships"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"),
        nullable=False)
    role = Column("role", String(length=32), nullable=False)

    organisation = relationship("Organisation")

    __mapper_args__ = {"polymorphic_identity": "membership"}


class Host(Artifact):
    """
    A Host is a computational asset. To be useful, a host must be sited on a
    functioning node, with a configured network. It must be installed with a
    particular operating system and software packages. The correct data
    sources must be mounted and the right users given access. All this is
    represented by a Host record and its resources.
    """
    __tablename__ = "hosts"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"),
        nullable=False)
    name = Column("name", String(length=128), nullable=False)

    organisation = relationship("Organisation")

    __mapper_args__ = {"polymorphic_identity": "host"}


class Registration(Artifact):
    """
    The registration is the artifact created during the onboarding of a
    new user. It collects the resources necessary to authenticate the
    user (:py:class:`~cloudhands.common.schema.EmailAddress`, 
    :py:class:`~cloudhands.common.schema.BcryptedPassword`) and to record
    his identity in a multi-user system
    (:py:class:`~cloudhands.common.schema.PublicKey`,
    :py:class:`~cloudhands.common.schema.PosixUId`,
    :py:class:`~cloudhands.common.schema.PosixUIdNumber`).
    """
    __tablename__ = "registrations"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)

    __mapper_args__ = {"polymorphic_identity": "registration"}


class Subscription(Artifact):
    """
    Represents the relationship between an organisation and a provider.
    This artifact records allocated provider resources like
    :py:class:`~cloudhands.common.schema.IPAddress`.
    """
    __tablename__ = "subscriptions"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"))
    provider_id = Column("provider_id", Integer, ForeignKey("providers.id"))

    organisation = relationship("Organisation")
    provider = relationship("Provider")

    __mapper_args__ = {"polymorphic_identity": "subscription"}


class Actor(Base):
    """
    This is the base table for all actors in the system.

    Actors are created with globally unique `uuids` so their identity can
    be maintained across sharded databases.

    Concrete classes define their own tables according to SQLAlchemy's
    `joined-table inheritance`_.

    .. _joined-table inheritance: http://docs.sqlalchemy.org/en/latest/orm\
/inheritance.html#joined-table-inheritance
    """

    __tablename__ = "actors"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    typ = Column("typ", String(length=32), nullable=False)
    uuid = Column("uuid", CHAR(length=32), nullable=False, unique=True)
    handle = Column("handle", String(length=64), nullable=True, unique=True)

    __mapper_args__ = {
        "polymorphic_identity": "actor",
        "polymorphic_on": typ}


class User(Actor):
    """
    Stores human users of the system.
    """
    __tablename__ = "users"

    id = Column("id", Integer, ForeignKey("actors.id"),
                nullable=False, primary_key=True)
    surname = Column("surname", String(length=32), nullable=True, unique=False)

    __mapper_args__ = {"polymorphic_identity": "user"}


class Component(Actor):
    """
    A registry for those parts of the system which take autonomous action.
    """
    __tablename__ = "components"

    id = Column("id", Integer, ForeignKey("actors.id"),
                nullable=False, primary_key=True)

    __mapper_args__ = {"polymorphic_identity": "component"}


class Touch(Base):
    __tablename__ = "touches"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    artifact_id = Column("artifact_id", Integer, ForeignKey("artifacts.id"))
    actor_id = Column("actor_id", Integer, ForeignKey("actors.id"))
    state_id = Column("state_id", Integer, ForeignKey("states.id"))
    at = Column("at", DateTime(), nullable=False)

    artifact = relationship("Artifact")
    actor = relationship("Actor")
    state = relationship("State")
    resources = relationship("Resource", cascade="all, delete-orphan")


class Provider(Base):
    """
    This is the base table for all providers in the system.

    Providers are created with globally unique `uuids` so their identity can
    be maintained across sharded databases.

    Concrete classes define their own tables according to SQLAlchemy's
    `joined-table inheritance`_.
    """

    __tablename__ = "providers"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    typ = Column("typ", String(length=32), nullable=False)
    uuid = Column("uuid", CHAR(length=32), nullable=False, unique=True)
    name = Column("name", String(length=64), nullable=True, unique=True)

    __mapper_args__ = {
        "polymorphic_identity": "provider",
        "polymorphic_on": typ}


class Archive(Provider):
    __tablename__ = "archives"

    id = Column("id", Integer, ForeignKey("providers.id"),
                nullable=False, primary_key=True)
    name = Column("value", String(length=128), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "archive"}


class Cloud(Provider):
    __tablename__ = "clouds"

    id = Column("id", Integer, ForeignKey("providers.id"),
                nullable=False, primary_key=True)
    name = Column("value", String(length=128), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "cloud"}


class Resource(Base):
    """
    This is the base table for all resources in the system.

    Resources can have globally unique `uris` if their identity must
    be maintained across sharded databases.

    Some resources have a `provider`. It is common for some resource values
    to be unique within a provider.

    Concrete classes define their own tables according to SQLAlchemy's
    `joined-table inheritance`_.
    """
    __tablename__ = "resources"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    typ = Column("typ", String(length=32), nullable=False)
    provider_id = Column(
        "provider_id", Integer, ForeignKey("providers.id"), nullable=True)
    touch_id = Column("touch_id", Integer, ForeignKey("touches.id"))

    provider = relationship("Provider")
    touch = relationship("Touch")

    __mapper_args__ = {
        "polymorphic_identity": "resource",
        "polymorphic_on": typ}


class BcryptedPassword(Resource):
    """
    This table stores passwords as bcrypt hashes
    """
    __tablename__ = "bcryptedpasswords"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=60), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "bcryptedpassword"}


class CatalogueChoice(Resource):
    """
    This table stores the details of an appliance as the user expects it to
    be provisioned.
    """
    __tablename__ = "cataloguechoices"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column(
        "name", String(length=32), nullable=False, unique=False)
    description = Column(
        "description", String(length=64), nullable=False)
    logo = Column(
        "logo", String(length=32), nullable=True)
    natrouted = Column("natrouted", Boolean, nullable=False)

    __mapper_args__ = {"polymorphic_identity": "cataloguechoice"}


class Label(Resource):
    """
    This table stores the details of an appliance as the user wants them
    be displayed.
    """
    __tablename__ = "labels"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column(
        "name", String(length=32), nullable=False, unique=False)
    description = Column(
        "description", String(length=64), nullable=True)

    __mapper_args__ = {"polymorphic_identity": "label"}


class LDAPAttribute(Resource):
    """
    This table stores the details of a modification to an entry
    in an LDAP database.
    """
    __tablename__ = "ldapattributes"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    dn = Column(
        "dn", String(length=128), nullable=False, unique=False)
    key = Column(
        "key", String(length=32), nullable=False, unique=False)
    value = Column(
        "value", String(length=32), nullable=False, unique=False)
    verb = Column(
        "verb", String(length=8), nullable=False, unique=False)

    __mapper_args__ = {"polymorphic_identity": "ldapattribute"}


class Directory(Resource):
    """
    This table stores directory paths.
    """
    __tablename__ = "directories"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    description = Column(
        "description", String(length=64), nullable=False)
    mount_path = Column(
        "mount_path", String(length=256), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "directory"}


class EmailAddress(Resource):
    """
    This table stores email addresses. They are expected to be unique.
    """
    __tablename__ = "emailaddresses"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=128), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "emailaddress"}


class OSImage(Resource):
    """
    This table stores names of Operating System images.
    They are expected to be unique.
    """
    __tablename__ = "osimages"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column("name", String(length=64), nullable=False, unique=False)

    __mapper_args__ = {"polymorphic_identity": "osimage"}


class ProviderReport(Resource):
    __tablename__ = "providerreports"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    creation = Column("creation", String(length=32), nullable=True, unique=False)
    power = Column("power", String(length=32), nullable=True, unique=False)
    health = Column("health", String(length=32), nullable=True, unique=False)

    __mapper_args__ = {"polymorphic_identity": "providerreport"}


class ProviderToken(Resource):
    __tablename__ = "providertokens"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    key = Column("key", String(length=32), nullable=True, unique=False)
    value = Column("value", String(length=128), nullable=False, unique=False)

    __mapper_args__ = {"polymorphic_identity": "providertoken"}


class SoftwareDefinedNetwork(Resource):
    __tablename__ = "softwaredefinednetworks"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column("name", String(length=32), nullable=False, unique=False)

    __mapper_args__ = {"polymorphic_identity": "softwaredefinednetwork"}


class TimeInterval(Resource):
    __tablename__ = "timeintervals"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    end = Column("end", DateTime(), nullable=False)

    __mapper_args__ = {"polymorphic_identity": "timeinterval"}


class IPAddress(Resource):
    """
    An Internet address. The address is stored as a string; no interpretation
    (ie: IPv4, IPv6) is placed on the value.
    """
    __tablename__ = "ipaddresses"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=64), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "ipaddress"}


class NATRouting(Resource):
    """
    Network Address Translation of non-routable IP address.
    """
    __tablename__ = "natroutings"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    ip_int = Column("ip_int", String(length=64), nullable=False, unique=False)
    ip_ext = Column("ip_ext", String(length=64), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "natrouting"}


class Node(Resource):
    """
    Represents a physical server, an instance of a virtual machine (VM),
    root jail, container or other computational resource.
    """
    __tablename__ = "nodes"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column("name", String(length=64), nullable=False)
    uri = Column("uri", String(length=256), nullable=True, unique=True)

    __mapper_args__ = {"polymorphic_identity": "node"}


class PosixUId(Resource):
    """
    The POSIX user name of a user at a particular provider.
    """
    __tablename__ = "posixuids"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=64), nullable=False)

    __mapper_args__ = {"polymorphic_identity": "posixuid"}


class PosixUIdNumber(Resource):
    """
    The POSIX user id number at a particular provider.
    """
    __tablename__ = "posixuidnumbers"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", Integer, nullable=False)

    __mapper_args__ = {"polymorphic_identity": "posixuidnumber"}


class PosixGId(Resource):
    """
    The POSIX identity of a user group at a particular provider.
    """
    __tablename__ = "posixgids"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", Integer, nullable=False)
    name = Column("name", String(length=32), nullable=True)

    __mapper_args__ = {"polymorphic_identity": "posixgid"}


class PublicKey(Resource):
    """
    A test representation of a public key.
    """
    __tablename__ = "publickeys"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=512), nullable=False)

    __mapper_args__ = {"polymorphic_identity": "publickey"}


class Serializable(object):

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class State(Base, Serializable):
    """
    State machines which persist their state in the database declare themselves
    using this table.
    """
    __tablename__ = "states"

    __table_args__ = (UniqueConstraint("fsm", "name"),)

    id = Column("id", Integer(), nullable=False, primary_key=True)
    fsm = Column("fsm", String(length=32), nullable=False)
    name = Column("name", String(length=64), nullable=False)

    __mapper_args__ = {'polymorphic_on': fsm}


def fsm_factory(name, states):
    """
    Dynamically create a class for a state machine. The pattern used
    is SQLAlchemy's `single table inheritance`_.

    .. _single table inheritance: http://docs.sqlalchemy.org/en/latest/orm\
/inheritance.html#single-table-inheritance
    """
    className = name.capitalize() + "State"
    attribs = dict(
        __mapper_args__={"polymorphic_identity": name},
        table=name,
        values=states,
    )
    class_ = type(className, (State,), attribs)
    return class_

#!/usr/bin/env python3
#   encoding: UTF-8

from sqlalchemy import Boolean
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import String
from sqlalchemy import UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Table
from sqlalchemy.types import CHAR
from sqlalchemy.orm import relationship

Base = declarative_base()
metadata = Base.metadata


__doc__ = """
The schema module defines tables in the common database
"""


class Group(Base):
    __tablename__ = "groups"

    id = Column("id", Integer, nullable=False, primary_key=True)
    uuid = Column("uuid", CHAR(length=32), nullable=False)
    name = Column("name", String(length=32), nullable=False, unique=True)
    number = Column("number", Integer, nullable=False, unique=True)

    accesses = relationship("Access")


class Organisation(Base):
    __tablename__ = "organisations"

    id = Column("id", Integer, nullable=False, primary_key=True)
    uuid = Column("uuid", CHAR(length=32), nullable=False)
    name = Column("name", String(length=64), nullable=False, unique=True)

    appliances = relationship("Appliance")
    memberships = relationship("Membership")
    subscriptions = relationship("Subscription", cascade="all, delete")
    catalogue = relationship("CatalogueItem")


class CatalogueItem(Base):
    """
    This table stores the details of an appliance as advertised to the user.
    """
    __tablename__ = "catalogueitems"

    uuid = Column("uuid", CHAR(length=32), nullable=False)
    name = Column(
        "name", String(length=32),
        nullable=False, primary_key=True, unique=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"))
    description = Column(
        "description", String(length=64), nullable=False)
    note = Column(
        "note", String(length=1024), nullable=True)
    logo = Column(
        "logo", String(length=32), nullable=True)
    natrouted = Column("natrouted", Boolean, nullable=False)
    
    organisation = relationship("Organisation")


class Artifact(Base):
    """
    This is the base table for all artifacts (products) of the system.

    Artifacts are created with globally unique `uuids` so their identity can
    be maintained across sharded databases.

    Concrete classes define their own tables according to SQLAlchemy's
    `joined-table inheritance`_.

    """
    __tablename__ = "artifacts"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    typ = Column("typ", String(length=32), nullable=False)
    uuid = Column("uuid", CHAR(length=32), nullable=False)
    model = Column("model", String(length=32), nullable=False)
    changes = relationship("Touch", order_by="Touch.at")

    __mapper_args__ = {
        "polymorphic_identity": "artifact",
        "polymorphic_on": typ}


class Access(Artifact):
    """
    """
    __tablename__ = "accesses"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    group_id = Column("group_id", Integer, ForeignKey("groups.id"),
        nullable=False)
    role = Column("role", String(length=32), nullable=False)

    group = relationship("Group")

    __mapper_args__ = {"polymorphic_identity": "access"}


class Appliance(Artifact):
    """
    An Appliance is a computational asset. An appliance is created from an item
    in a catalogue. It is provisioned on one or more nodes, with a configured
    network. The correct data sources must be mounted and the right users given
    access.

    The Appliance record carries links to the
    :py:class:`~cloudhands.common.schema.CatalogueChoice` made by the user and
    the :py:class:`~cloudhands.common.schema.Label` information it was given.

    Once activated, other allocated resources are stored against this artifact,
    such as :py:class:`~cloudhands.common.schema.Node` and
    :py:class:`~cloudhands.common.schema.IPAddress`.

    Periodically a :py:class:`~cloudhands.common.schema.ProviderReport` is
    stored against the appliance to confirm its status.
    """
    __tablename__ = "appliances"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"),
        nullable=False)

    organisation = relationship("Organisation")

    __mapper_args__ = {"polymorphic_identity": "appliance"}


class Membership(Artifact):
    """
    No user may interact with the system without specific membership
    privileges. The user accumulates credentials against a
    membership record. The membership defines the role a user may operate
    within an `organisation`.
    The membership may be revoked by an administrator, or by the system
    automatically if there is no activity within a particular
    :py:class:`~cloudhands.common.schema.TimeInterval`.
    """
    __tablename__ = "memberships"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"),
        nullable=False)
    role = Column("role", String(length=32), nullable=False)

    organisation = relationship("Organisation")

    __mapper_args__ = {"polymorphic_identity": "membership"}


class Host(Artifact):
    """
    A Host is a computational asset. To be useful, a host must be sited on a
    functioning node, with a configured network. It must be installed with a
    particular operating system and software packages. The correct data
    sources must be mounted and the right users given access. All this is
    represented by a Host record and its resources.
    """
    __tablename__ = "hosts"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"),
        nullable=False)
    name = Column("name", String(length=128), nullable=False)

    organisation = relationship("Organisation")

    __mapper_args__ = {"polymorphic_identity": "host"}


class Registration(Artifact):
    """
    The registration is the artifact created during the onboarding of a
    new user. It collects the resources necessary to authenticate the
    user (:py:class:`~cloudhands.common.schema.EmailAddress`, 
    :py:class:`~cloudhands.common.schema.BcryptedPassword`) and to record
    his identity in a multi-user system
    (:py:class:`~cloudhands.common.schema.PublicKey`,
    :py:class:`~cloudhands.common.schema.PosixUId`,
    :py:class:`~cloudhands.common.schema.PosixUIdNumber`).
    """
    __tablename__ = "registrations"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)

    __mapper_args__ = {"polymorphic_identity": "registration"}


class Subscription(Artifact):
    """
    Represents the relationship between an organisation and a provider.
    This artifact records allocated provider resources like
    :py:class:`~cloudhands.common.schema.IPAddress`.
    """
    __tablename__ = "subscriptions"

    id = Column("id", Integer, ForeignKey("artifacts.id"),
                nullable=False, primary_key=True)
    organisation_id = Column(
        "organisation_id", Integer, ForeignKey("organisations.id"))
    provider_id = Column("provider_id", Integer, ForeignKey("providers.id"))

    organisation = relationship("Organisation")
    provider = relationship("Provider")

    __mapper_args__ = {"polymorphic_identity": "subscription"}


class Actor(Base):
    """
    This is the base table for all actors in the system.

    Actors are created with globally unique `uuids` so their identity can
    be maintained across sharded databases.

    Concrete classes define their own tables according to SQLAlchemy's
    `joined-table inheritance`_.

    .. _joined-table inheritance: http://docs.sqlalchemy.org/en/latest/orm\
/inheritance.html#joined-table-inheritance
    """

    __tablename__ = "actors"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    typ = Column("typ", String(length=32), nullable=False)
    uuid = Column("uuid", CHAR(length=32), nullable=False, unique=True)
    handle = Column("handle", String(length=64), nullable=True, unique=True)

    __mapper_args__ = {
        "polymorphic_identity": "actor",
        "polymorphic_on": typ}


class User(Actor):
    """
    Stores human users of the system.
    """
    __tablename__ = "users"

    id = Column("id", Integer, ForeignKey("actors.id"),
                nullable=False, primary_key=True)
    surname = Column("surname", String(length=32), nullable=True, unique=False)

    __mapper_args__ = {"polymorphic_identity": "user"}


class Component(Actor):
    """
    A registry for those parts of the system which take autonomous action.
    """
    __tablename__ = "components"

    id = Column("id", Integer, ForeignKey("actors.id"),
                nullable=False, primary_key=True)

    __mapper_args__ = {"polymorphic_identity": "component"}


class Touch(Base):
    __tablename__ = "touches"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    artifact_id = Column("artifact_id", Integer, ForeignKey("artifacts.id"))
    actor_id = Column("actor_id", Integer, ForeignKey("actors.id"))
    state_id = Column("state_id", Integer, ForeignKey("states.id"))
    at = Column("at", DateTime(), nullable=False)

    artifact = relationship("Artifact")
    actor = relationship("Actor")
    state = relationship("State")
    resources = relationship("Resource", cascade="all, delete-orphan")


class Provider(Base):
    """
    This is the base table for all providers in the system.

    Providers are created with globally unique `uuids` so their identity can
    be maintained across sharded databases.

    Concrete classes define their own tables according to SQLAlchemy's
    `joined-table inheritance`_.
    """

    __tablename__ = "providers"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    typ = Column("typ", String(length=32), nullable=False)
    uuid = Column("uuid", CHAR(length=32), nullable=False, unique=True)
    name = Column("name", String(length=64), nullable=True, unique=True)

    __mapper_args__ = {
        "polymorphic_identity": "provider",
        "polymorphic_on": typ}


class Archive(Provider):
    __tablename__ = "archives"

    id = Column("id", Integer, ForeignKey("providers.id"),
                nullable=False, primary_key=True)
    name = Column("value", String(length=128), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "archive"}


class Cloud(Provider):
    __tablename__ = "clouds"

    id = Column("id", Integer, ForeignKey("providers.id"),
                nullable=False, primary_key=True)
    name = Column("value", String(length=128), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "cloud"}


class Resource(Base):
    """
    This is the base table for all resources in the system.

    Resources can have globally unique `uris` if their identity must
    be maintained across sharded databases.

    Some resources have a `provider`. It is common for some resource values
    to be unique within a provider.

    Concrete classes define their own tables according to SQLAlchemy's
    `joined-table inheritance`_.
    """
    __tablename__ = "resources"

    id = Column("id", Integer(), nullable=False, primary_key=True)
    typ = Column("typ", String(length=32), nullable=False)
    provider_id = Column(
        "provider_id", Integer, ForeignKey("providers.id"), nullable=True)
    touch_id = Column("touch_id", Integer, ForeignKey("touches.id"))

    provider = relationship("Provider")
    touch = relationship("Touch")

    __mapper_args__ = {
        "polymorphic_identity": "resource",
        "polymorphic_on": typ}


class BcryptedPassword(Resource):
    """
    This table stores passwords as bcrypt hashes
    """
    __tablename__ = "bcryptedpasswords"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=60), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "bcryptedpassword"}


class CatalogueChoice(Resource):
    """
    This table stores the details of an appliance as the user expects it to
    be provisioned.
    """
    __tablename__ = "cataloguechoices"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column(
        "name", String(length=32), nullable=False, unique=False)
    description = Column(
        "description", String(length=64), nullable=False)
    logo = Column(
        "logo", String(length=32), nullable=True)
    natrouted = Column("natrouted", Boolean, nullable=False)

    __mapper_args__ = {"polymorphic_identity": "cataloguechoice"}


class Label(Resource):
    """
    This table stores the details of an appliance as the user wants them
    be displayed.
    """
    __tablename__ = "labels"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column(
        "name", String(length=32), nullable=False, unique=False)
    description = Column(
        "description", String(length=64), nullable=True)

    __mapper_args__ = {"polymorphic_identity": "label"}


class LDAPAttribute(Resource):
    """
    This table stores the details of a modification to an entry
    in an LDAP database.
    """
    __tablename__ = "ldapattributes"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    dn = Column(
        "dn", String(length=128), nullable=False, unique=False)
    key = Column(
        "key", String(length=32), nullable=False, unique=False)
    value = Column(
        "value", String(length=32), nullable=False, unique=False)
    verb = Column(
        "verb", String(length=8), nullable=False, unique=False)

    __mapper_args__ = {"polymorphic_identity": "ldapattribute"}


class Directory(Resource):
    """
    This table stores directory paths.
    """
    __tablename__ = "directories"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    description = Column(
        "description", String(length=64), nullable=False)
    mount_path = Column(
        "mount_path", String(length=256), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "directory"}


class EmailAddress(Resource):
    """
    This table stores email addresses. They are expected to be unique.
    """
    __tablename__ = "emailaddresses"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=128), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "emailaddress"}


class OSImage(Resource):
    """
    This table stores names of Operating System images.
    They are expected to be unique.
    """
    __tablename__ = "osimages"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column("name", String(length=64), nullable=False, unique=False)

    __mapper_args__ = {"polymorphic_identity": "osimage"}


class ProviderReport(Resource):
    __tablename__ = "providerreports"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    creation = Column("creation", String(length=32), nullable=True, unique=False)
    power = Column("power", String(length=32), nullable=True, unique=False)
    health = Column("health", String(length=32), nullable=True, unique=False)

    __mapper_args__ = {"polymorphic_identity": "providerreport"}


class ProviderToken(Resource):
    __tablename__ = "providertokens"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    key = Column("key", String(length=32), nullable=True, unique=False)
    value = Column("value", String(length=128), nullable=False, unique=False)

    __mapper_args__ = {"polymorphic_identity": "providertoken"}


class SoftwareDefinedNetwork(Resource):
    __tablename__ = "softwaredefinednetworks"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column("name", String(length=32), nullable=False, unique=False)

    __mapper_args__ = {"polymorphic_identity": "softwaredefinednetwork"}


class TimeInterval(Resource):
    __tablename__ = "timeintervals"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    end = Column("end", DateTime(), nullable=False)

    __mapper_args__ = {"polymorphic_identity": "timeinterval"}


class IPAddress(Resource):
    """
    An Internet address. The address is stored as a string; no interpretation
    (ie: IPv4, IPv6) is placed on the value.
    """
    __tablename__ = "ipaddresses"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=64), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "ipaddress"}


class NATRouting(Resource):
    """
    Network Address Translation of non-routable IP address.
    """
    __tablename__ = "natroutings"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    ip_int = Column("ip_int", String(length=64), nullable=False, unique=False)
    ip_ext = Column("ip_ext", String(length=64), nullable=False, unique=True)

    __mapper_args__ = {"polymorphic_identity": "natrouting"}


class Node(Resource):
    """
    Represents a physical server, an instance of a virtual machine (VM),
    root jail, container or other computational resource.
    """
    __tablename__ = "nodes"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    name = Column("name", String(length=64), nullable=False)
    uri = Column("uri", String(length=256), nullable=True, unique=True)

    __mapper_args__ = {"polymorphic_identity": "node"}


class PosixUId(Resource):
    """
    The POSIX user name of a user at a particular provider.
    """
    __tablename__ = "posixuids"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=64), nullable=False)

    __mapper_args__ = {"polymorphic_identity": "posixuid"}


class PosixUIdNumber(Resource):
    """
    The POSIX user id number at a particular provider.
    """
    __tablename__ = "posixuidnumbers"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", Integer, nullable=False)

    __mapper_args__ = {"polymorphic_identity": "posixuidnumber"}


class PosixGId(Resource):
    """
    The POSIX identity of a user group at a particular provider.
    """
    __tablename__ = "posixgids"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", Integer, nullable=False)
    name = Column("name", String(length=32), nullable=True)

    __mapper_args__ = {"polymorphic_identity": "posixgid"}


class PublicKey(Resource):
    """
    A test representation of a public key.
    """
    __tablename__ = "publickeys"

    id = Column("id", Integer, ForeignKey("resources.id"),
                nullable=False, primary_key=True)
    value = Column("value", String(length=512), nullable=False)

    __mapper_args__ = {"polymorphic_identity": "publickey"}


class Serializable(object):

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class State(Base, Serializable):
    """
    State machines which persist their state in the database declare themselves
    using this table.
    """
    __tablename__ = "states"

    __table_args__ = (UniqueConstraint("fsm", "name"),)

    id = Column("id", Integer(), nullable=False, primary_key=True)
    fsm = Column("fsm", String(length=32), nullable=False)
    name = Column("name", String(length=64), nullable=False)

    __mapper_args__ = {'polymorphic_on': fsm}


def fsm_factory(name, states):
    """
    Dynamically create a class for a state machine. The pattern used
    is SQLAlchemy's `single table inheritance`_.

    .. _single table inheritance: http://docs.sqlalchemy.org/en/latest/orm\
/inheritance.html#single-table-inheritance
    """
    className = name.capitalize() + "State"
    attribs = dict(
        __mapper_args__={"polymorphic_identity": name},
        table=name,
        values=states,
    )
    class_ = type(className, (State,), attribs)
    return class_
#!/usr/bin/env python3 #   encoding: UTF-8 The schema module defines tables in the common database This table stores the details of an appliance as advertised to the user. This is the base table for all artifacts (products) of the system.      Artifacts are created with globally unique `uuids` so their identity can     be maintained across sharded databases.      Concrete classes define their own tables according to SQLAlchemy's     `joined-table inheritance`_. An Appliance is a computational asset. An appliance is created from an item     in a catalogue. It is provisioned on one or more nodes, with a configured     network. The correct data sources must be mounted and the right users given     access.      The Appliance record carries links to the     :py:class:`~cloudhands.common.schema.CatalogueChoice` made by the user and     the :py:class:`~cloudhands.common.schema.Label` information it was given.      Once activated, other allocated resources are stored against this artifact,     such as :py:class:`~cloudhands.common.schema.Node` and     :py:class:`~cloudhands.common.schema.IPAddress`.      Periodically a :py:class:`~cloudhands.common.schema.ProviderReport` is     stored against the appliance to confirm its status. No user may interact with the system without specific membership     privileges. The user accumulates credentials against a     membership record. The membership defines the role a user may operate     within an `organisation`.     The membership may be revoked by an administrator, or by the system     automatically if there is no activity within a particular     :py:class:`~cloudhands.common.schema.TimeInterval`. A Host is a computational asset. To be useful, a host must be sited on a     functioning node, with a configured network. It must be installed with a     particular operating system and software packages. The correct data     sources must be mounted and the right users given access. All this is     represented by a Host record and its resources. The registration is the artifact created during the onboarding of a     new user. It collects the resources necessary to authenticate the     user (:py:class:`~cloudhands.common.schema.EmailAddress`,      :py:class:`~cloudhands.common.schema.BcryptedPassword`) and to record     his identity in a multi-user system     (:py:class:`~cloudhands.common.schema.PublicKey`,     :py:class:`~cloudhands.common.schema.PosixUId`,     :py:class:`~cloudhands.common.schema.PosixUIdNumber`). Represents the relationship between an organisation and a provider.     This artifact records allocated provider resources like     :py:class:`~cloudhands.common.schema.IPAddress`. This is the base table for all actors in the system.      Actors are created with globally unique `uuids` so their identity can     be maintained across sharded databases.      Concrete classes define their own tables according to SQLAlchemy's     `joined-table inheritance`_.      .. _joined-table inheritance: http://docs.sqlalchemy.org/en/latest/orm\ /inheritance.html#joined-table-inheritance Stores human users of the system. A registry for those parts of the system which take autonomous action. This is the base table for all providers in the system.      Providers are created with globally unique `uuids` so their identity can     be maintained across sharded databases.      Concrete classes define their own tables according to SQLAlchemy's     `joined-table inheritance`_. This is the base table for all resources in the system.      Resources can have globally unique `uris` if their identity must     be maintained across sharded databases.      Some resources have a `provider`. It is common for some resource values     to be unique within a provider.      Concrete classes define their own tables according to SQLAlchemy's     `joined-table inheritance`_. This table stores passwords as bcrypt hashes This table stores the details of an appliance as the user expects it to     be provisioned. This table stores the details of an appliance as the user wants them     be displayed. This table stores the details of a modification to an entry     in an LDAP database. This table stores directory paths. This table stores email addresses. They are expected to be unique. This table stores names of Operating System images.     They are expected to be unique. An Internet address. The address is stored as a string; no interpretation     (ie: IPv4, IPv6) is placed on the value. Network Address Translation of non-routable IP address. Represents a physical server, an instance of a virtual machine (VM),     root jail, container or other computational resource. The POSIX user name of a user at a particular provider. The POSIX user id number at a particular provider. The POSIX identity of a user group at a particular provider. A test representation of a public key. State machines which persist their state in the database declare themselves     using this table. Dynamically create a class for a state machine. The pattern used     is SQLAlchemy's `single table inheritance`_.      .. _single table inheritance: http://docs.sqlalchemy.org/en/latest/orm\ /inheritance.html#single-table-inheritance