Database Schema

SkyPortal uses a PostgreSQL database to manage persistent state. This section documents the schema of the database and describes how the application interacts with it.

SQLAlchemy Model API Documentation

The SkyPortal Python backend interacts with the PostgreSQL backend using the SQLAlchemy object relational mapper. Each database table is represented by a Python class, and each table Column is represented by a class attribute. This Section documents each of the SkyPortal and baselayer classes that are mapped to database tables.

class baselayer.app.models.ACL(**kwargs)

An access control list item representing a privilege within the application. ACLs are aggregated into collections called Roles which are assumed by Users. Examples of ACLs include Upload Data, Comment, and Manage Groups.

Attributes:
created_at

UTC time of insertion of object’s row into the database.

id

ACL name.

modified

UTC time the object’s row was last modified in the database.

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_readable_by(user_or_token)

Return a boolean indicating whether a User or Token has read access to this object.

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

to_dict()

Serialize this object to a Python dictionary.

created_at

UTC time of insertion of object’s row into the database.

id

ACL name.

modified

UTC time the object’s row was last modified in the database.

class baselayer.app.models.AccessibleIfRelatedRowsAreAccessible(**properties_and_modes)
Attributes:
properties_and_modes

Methods

check_cls_for_attributes(cls, attrs)

Check that a target class has the specified attributes.

query_accessible_rows(cls, user_or_token[, ...])

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

select_accessible_rows(cls, user_or_token[, ...])

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

user_id_from_user_or_token(user_or_token)

Return the user_id associated with a specified User or Token object.

query_accessible_rows(cls, user_or_token, columns=None)

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token. All query based functions will be deprecated when moving to SQL Alchemy 2.0 in favor of select functions.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
querysqlalchemy.Query

Query for the accessible rows.

select_accessible_rows(cls, user_or_token, columns=None)

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
sqlalchemy select object
class baselayer.app.models.AccessibleIfUserMatches(relationship_chain)
Attributes:
relationship_chain
relationship_names

List of names of each relationship in the chain.

Methods

check_cls_for_attributes(cls, attrs)

Check that a target class has the specified attributes.

query_accessible_rows(cls, user_or_token[, ...])

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

select_accessible_rows(cls, user_or_token[, ...])

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

user_id_from_user_or_token(user_or_token)

Return the user_id associated with a specified User or Token object.

query_accessible_rows(cls, user_or_token, columns=None)

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token. All query based functions will be deprecated when moving to SQL Alchemy 2.0 in favor of select functions.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
querysqlalchemy.Query

Query for the accessible rows.

property relationship_names

List of names of each relationship in the chain.

select_accessible_rows(cls, user_or_token, columns=None)

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
sqlalchemy select object
class baselayer.app.models.ComposedAccessControl(*access_controls, logic='and')
Attributes:
access_controls
logic

Methods

check_cls_for_attributes(cls, attrs)

Check that a target class has the specified attributes.

query_accessible_rows(cls, user_or_token[, ...])

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

select_accessible_rows(cls, user_or_token[, ...])

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

user_id_from_user_or_token(user_or_token)

Return the user_id associated with a specified User or Token object.

query_accessible_rows(cls, user_or_token, columns=None)

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token. All query based functions will be deprecated when moving to SQL Alchemy 2.0 in favor of select functions.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
querysqlalchemy.Query

Query for the accessible rows.

select_accessible_rows(cls, user_or_token, columns=None)

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
sqlalchemy select object
class baselayer.app.models.CronJobRun(**kwargs)

A record of a run (or attempted run) of a cron job.

Attributes:
created_at

UTC time of insertion of object’s row into the database.

exit_status

Exit status of cron job subprocess (e.g. 0 or 1).

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

output

Cron job’s subprocess output, or exception string.

script

Name of script being run.

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_readable_by(user_or_token)

Return a boolean indicating whether a User or Token has read access to this object.

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

to_dict()

Serialize this object to a Python dictionary.

created_at

UTC time of insertion of object’s row into the database.

exit_status

Exit status of cron job subprocess (e.g. 0 or 1).

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

output

Cron job’s subprocess output, or exception string.

script

Name of script being run.

class baselayer.app.models.CustomUserAccessControl(query_or_query_generator)

Methods

check_cls_for_attributes(cls, attrs)

Check that a target class has the specified attributes.

query_accessible_rows(cls, user_or_token[, ...])

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

select_accessible_rows(cls, user_or_token[, ...])

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

user_id_from_user_or_token(user_or_token)

Return the user_id associated with a specified User or Token object.

query_accessible_rows(cls, user_or_token, columns=None)

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token. All query based functions will be deprecated when moving to SQL Alchemy 2.0 in favor of select functions.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
querysqlalchemy.Query

Query for the accessible rows.

select_accessible_rows(cls, user_or_token, columns=None)

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
sqlalchemy.Select object
class baselayer.app.models.JoinModel

Dummy class that join_models subclass. Provides an easy way to access all join_model mapped classes via the __subclasses__() method.

class baselayer.app.models.Public

A record accessible to anyone.

Methods

check_cls_for_attributes(cls, attrs)

Check that a target class has the specified attributes.

query_accessible_rows(cls, user_or_token[, ...])

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

select_accessible_rows(cls, user_or_token[, ...])

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

user_id_from_user_or_token(user_or_token)

Return the user_id associated with a specified User or Token object.

query_accessible_rows(cls, user_or_token, columns=None)

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token. All query based functions will be deprecated when moving to SQL Alchemy 2.0 in favor of select functions.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
querysqlalchemy.Query

Query for the accessible rows.

select_accessible_rows(cls, user_or_token, columns=None)

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
sqlalchemy select object
class baselayer.app.models.Restricted

A record that can only be accessed by a System Admin.

Methods

check_cls_for_attributes(cls, attrs)

Check that a target class has the specified attributes.

query_accessible_rows(cls, user_or_token[, ...])

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

select_accessible_rows(cls, user_or_token[, ...])

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

user_id_from_user_or_token(user_or_token)

Return the user_id associated with a specified User or Token object.

query_accessible_rows(cls, user_or_token, columns=None)

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token. All query based functions will be deprecated when moving to SQL Alchemy 2.0 in favor of select functions.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
querysqlalchemy.Query

Query for the accessible rows.

select_accessible_rows(cls, user_or_token, columns=None)

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
sqlalchemy.Select object
class baselayer.app.models.Role(**kwargs)

A collection of ACLs. Roles map Users to ACLs. One User may assume multiple Roles.

Attributes:
acls

ACLs associated with the Role.

created_at

UTC time of insertion of object’s row into the database.

id

Role name.

modified

UTC time the object’s row was last modified in the database.

users

Users who have this Role.

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_readable_by(user_or_token)

Return a boolean indicating whether a User or Token has read access to this object.

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

to_dict()

Serialize this object to a Python dictionary.

acls

ACLs associated with the Role.

created_at

UTC time of insertion of object’s row into the database.

id

Role name.

modified

UTC time the object’s row was last modified in the database.

users

Users who have this Role.

class baselayer.app.models.RoleACL(**kwargs)

Join table class mapping Roles to ACLs.

Attributes:
acl
acl_id
created_at

UTC time of insertion of object’s row into the database.

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

role
role_id

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_readable_by(user_or_token)

Return a boolean indicating whether a User or Token has read access to this object.

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

to_dict()

Serialize this object to a Python dictionary.

create = <baselayer.app.models.AccessibleIfRelatedRowsAreAccessible object>
created_at

UTC time of insertion of object’s row into the database.

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

read = <baselayer.app.models.AccessibleIfRelatedRowsAreAccessible object>
class baselayer.app.models.SlugifiedStr(*args: Any, **kwargs: Any)

Slugified string

Attributes:
comparator_factory

Base class for custom comparison operations defined at the type level.

python_type

Return the Python type object expected to be returned by instances of this type, if known.

sort_key_function

Methods

Comparator(expr)

A TypeEngine.Comparator that is specific to TypeDecorator.

adapt(cls, **kw)

Produce an "adapted" form of this type, given an "impl" class to work with.

as_generic([allow_nulltype])

Return an instance of the generic type corresponding to this type using heuristic rule.

bind_expression(bindparam)

Given a bind value (i.e.

bind_processor(dialect)

Provide a bound value processing function for the given Dialect.

coerce_compared_value(op, value)

Suggest a type for a 'coerced' Python value in an expression.

column_expression(column)

Given a SELECT column expression, return a wrapping SQL expression.

compare_against_backend(dialect, conn_type)

Compare this type against the given backend type.

compare_values(x, y)

Given two values, compare them for equality.

compile([dialect])

Produce a string-compiled form of this TypeEngine.

copy(**kw)

Produce a copy of this TypeDecorator instance.

dialect_impl(dialect)

Return a dialect-specific implementation for this TypeEngine.

evaluates_none()

Return a copy of this type which has the should_evaluate_none flag set to True.

get_dbapi_type(dbapi)

Return the DBAPI type object represented by this TypeDecorator.

impl

alias of String

literal_processor(dialect)

Provide a literal processing function for the given Dialect.

load_dialect_impl(dialect)

Return a TypeEngine object corresponding to a dialect.

process_bind_param(value, dialect)

Receive a bound parameter value to be converted.

process_literal_param(value, dialect)

Receive a literal parameter value to be rendered inline within a statement.

process_result_value(value, dialect)

Receive a result-row column value to be converted.

result_processor(dialect, coltype)

Provide a result value processing function for the given Dialect.

type_engine(dialect)

Return a dialect-specific TypeEngine instance for this TypeDecorator.

with_variant(type_, *dialect_names)

Produce a copy of this type object that will utilize the given type when applied to the dialect of the given name.

copy_value

cache_ok: bool | None = True

Indicate if statements using this ExternalType are “safe to cache”.

The default value None will emit a warning and then not allow caching of a statement which includes this type. Set to False to disable statements using this type from being cached at all without a warning. When set to True, the object’s class and selected elements from its state will be used as part of the cache key. For example, using a TypeDecorator:

class MyType(TypeDecorator):
    impl = String

    cache_ok = True

    def __init__(self, choices):
        self.choices = tuple(choices)
        self.internal_only = True

The cache key for the above type would be equivalent to:

>>> MyType(["a", "b", "c"])._static_cache_key
(<class '__main__.MyType'>, ('choices', ('a', 'b', 'c')))

The caching scheme will extract attributes from the type that correspond to the names of parameters in the __init__() method. Above, the “choices” attribute becomes part of the cache key but “internal_only” does not, because there is no parameter named “internal_only”.

The requirements for cacheable elements is that they are hashable and also that they indicate the same SQL rendered for expressions using this type every time for a given cache value.

To accommodate for datatypes that refer to unhashable structures such as dictionaries, sets and lists, these objects can be made “cacheable” by assigning hashable structures to the attributes whose names correspond with the names of the arguments. For example, a datatype which accepts a dictionary of lookup values may publish this as a sorted series of tuples. Given a previously un-cacheable type as:

class LookupType(UserDefinedType):
    '''a custom type that accepts a dictionary as a parameter.

    this is the non-cacheable version, as "self.lookup" is not
    hashable.

    '''

    def __init__(self, lookup):
        self.lookup = lookup

    def get_col_spec(self, **kw):
        return "VARCHAR(255)"

    def bind_processor(self, dialect):
        # ...  works with "self.lookup" ...

Where “lookup” is a dictionary. The type will not be able to generate a cache key:

>>> type_ = LookupType({"a": 10, "b": 20})
>>> type_._static_cache_key
<stdin>:1: SAWarning: UserDefinedType LookupType({'a': 10, 'b': 20}) will not
produce a cache key because the ``cache_ok`` flag is not set to True.
Set this flag to True if this type object's state is safe to use
in a cache key, or False to disable this warning.
symbol('no_cache')

If we did set up such a cache key, it wouldn’t be usable. We would get a tuple structure that contains a dictionary inside of it, which cannot itself be used as a key in a “cache dictionary” such as SQLAlchemy’s statement cache, since Python dictionaries aren’t hashable:

>>> # set cache_ok = True
>>> type_.cache_ok = True

>>> # this is the cache key it would generate
>>> key = type_._static_cache_key
>>> key
(<class '__main__.LookupType'>, ('lookup', {'a': 10, 'b': 20}))

>>> # however this key is not hashable, will fail when used with
>>> # SQLAlchemy statement cache
>>> some_cache = {key: "some sql value"}
Traceback (most recent call last): File "<stdin>", line 1,
in <module> TypeError: unhashable type: 'dict'

The type may be made cacheable by assigning a sorted tuple of tuples to the “.lookup” attribute:

class LookupType(UserDefinedType):
    '''a custom type that accepts a dictionary as a parameter.

    The dictionary is stored both as itself in a private variable,
    and published in a public variable as a sorted tuple of tuples,
    which is hashable and will also return the same value for any
    two equivalent dictionaries.  Note it assumes the keys and
    values of the dictionary are themselves hashable.

    '''

    cache_ok = True

    def __init__(self, lookup):
        self._lookup = lookup

        # assume keys/values of "lookup" are hashable; otherwise
        # they would also need to be converted in some way here
        self.lookup = tuple(
            (key, lookup[key]) for key in sorted(lookup)
        )

    def get_col_spec(self, **kw):
        return "VARCHAR(255)"

    def bind_processor(self, dialect):
        # ...  works with "self._lookup" ...

Where above, the cache key for LookupType({"a": 10, "b": 20}) will be:

>>> LookupType({"a": 10, "b": 20})._static_cache_key
(<class '__main__.LookupType'>, ('lookup', (('a', 10), ('b', 20))))

New in version 1.4.14: - added the cache_ok flag to allow some configurability of caching for TypeDecorator classes.

New in version 1.4.28: - added the ExternalType mixin which generalizes the cache_ok flag to both the TypeDecorator and UserDefinedType classes.

See also

sql_caching

impl

alias of String

process_bind_param(value, dialect)

Receive a bound parameter value to be converted.

Custom subclasses of _types.TypeDecorator should override this method to provide custom behaviors for incoming data values. This method is called at statement execution time and is passed the literal Python data value which is to be associated with a bound parameter in the statement.

The operation could be anything desired to perform custom behavior, such as transforming or serializing data. This could also be used as a hook for validating logic.

Parameters:
  • value – Data to operate upon, of any type expected by this method in the subclass. Can be None.

  • dialect – the Dialect in use.

See also

types_typedecorator

_types.TypeDecorator.process_result_value()

process_result_value(value, dialect)

Receive a result-row column value to be converted.

Custom subclasses of _types.TypeDecorator should override this method to provide custom behaviors for data values being received in result rows coming from the database. This method is called at result fetching time and is passed the literal Python data value that’s extracted from a database result row.

The operation could be anything desired to perform custom behavior, such as transforming or deserializing data.

Parameters:
  • value – Data to operate upon, of any type expected by this method in the subclass. Can be None.

  • dialect – the Dialect in use.

See also

types_typedecorator

_types.TypeDecorator.process_bind_param()

class baselayer.app.models.Token(**kwargs)

A command line token that can be used to programmatically access the API as a particular User.

Attributes:
accessible_groups

Return the list of Groups a User or Token has access to.

accessible_streams

Return the list of Streams a User or Token has access to.

acls

The ACLs granted to the Token.

created_at

UTC time of insertion of object’s row into the database.

created_by

The User that created the token.

created_by_id

The ID of the User that created the Token.

groups

The groups the Token owner is a member of.

id

The value of the token. This field is used for authenticating as a User on the command line.

is_admin
is_system_admin
modified

UTC time the object’s row was last modified in the database.

name

The name of the token.

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_readable_by(user_or_token)

Return a boolean indicating whether this Token is readable by the specified User (or Token instance, if a token is passed).

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

to_dict()

Serialize this object to a Python dictionary.

property accessible_groups

Return the list of Groups a User or Token has access to. For non-admin Users or Token owners, this corresponds to the Groups they are a member of. For System Admins, this corresponds to all Groups.

property accessible_streams

Return the list of Streams a User or Token has access to.

acls

The ACLs granted to the Token.

create = <baselayer.app.models.AccessibleIfUserMatches object>
created_at

UTC time of insertion of object’s row into the database.

created_by

The User that created the token.

created_by_id

The ID of the User that created the Token.

delete = <baselayer.app.models.AccessibleIfUserMatches object>
property groups

The groups the Token owner is a member of.

id

The value of the token. This field is used for authenticating as a User on the command line.

is_readable_by(user_or_token)

Return a boolean indicating whether this Token is readable by the specified User (or Token instance, if a token is passed).

Parameters:
user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

Returns:
readablebool

Whether this Token instance is readable by the User or Token.

modified

UTC time the object’s row was last modified in the database.

name

The name of the token.

read = <baselayer.app.models.AccessibleIfUserMatches object>
update = <baselayer.app.models.AccessibleIfUserMatches object>
class baselayer.app.models.TokenACL(**kwargs)

Join table mapping Tokens to ACLs

Attributes:
acl
acl_id
created_at

UTC time of insertion of object’s row into the database.

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

token
token_id

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_readable_by(user_or_token)

Return a boolean indicating whether a User or Token has read access to this object.

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

to_dict()

Serialize this object to a Python dictionary.

create = <baselayer.app.models.AccessibleIfRelatedRowsAreAccessible object>
created_at

UTC time of insertion of object’s row into the database.

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

read = <baselayer.app.models.AccessibleIfRelatedRowsAreAccessible object>
class baselayer.app.models.User(**kwargs)

An application user.

Attributes:
accessible_groups

Return the list of Groups a User or Token has access to.

accessible_streams

Return the list of Streams a User or Token has access to.

acls

ACLs granted to user, separate from role-level ACLs

affiliations

The User’s affiliations (ex: Department of Mathematics, University X)

annotations
annotations_on_photometry
annotations_on_spectra
assignments

Objs the User has assigned to ObservingRuns.

catalog_queries

The catalog queries this User has made.

comments
comments_on_earthquakes
comments_on_gcns
comments_on_shifts
comments_on_spectra
contact_email

The phone number at which the user prefers to receive communications.

contact_phone

The email at which the user prefers to receive communications.

created_at

UTC time of insertion of object’s row into the database.

default_followup_requests

The default follow-up requests this User has made.

default_observationplan_requests

The default observation plan requests this User has made.

earthquakeevents

The EarthquakeEvents saved by this user

earthquakenotices

The EarthquakeNotices saved by this user

expiration_date

The date until which the user’s account is valid. Users are set to view-only upon expiration.

first_name

The User’s first name.

followup_requests

The follow-up requests this User has made.

gcnevents

The gcnevents saved by this user

gcnnotices

The GcnNotices saved by this user

gcnproperties

The gcnproperties saved by this user

gcnsummaries

The gcnsummaries saved by this user

gcntags

The gcntags saved by this user

gravatar_url

The Gravatar URL inferred from the user’s contact email, or, if the contact email is null, the username.

group_admission_requests

User’s requests to join groups.

groups

The Groups this User is a member of.

id

Unique object identifier.

is_admin
is_system_admin
last_name

The User’s last name.

listings

The listings saved by this user

localizationproperties

The localizationproperties saved by this user

localizations

The localizations saved by this user

localizationtags

The localizationtags saved by this user

mmadetector_spectra

MMADetectorSpectra uploaded by this User.

mmadetector_time_intervals

MMADetectorTimeInterval uploaded by this User.

modified

UTC time the object’s row was last modified in the database.

notifications

Notifications to be displayed on front-end associated with User

oauth_uid

The user’s OAuth UID.

observationplan_requests

The observation plan requests this User has made.

observing_runs

Observing Runs this User has created.

permissions

List of the names of all of the user’s ACLs (role-level + individual).

photometric_series

PhotometricSeries uploaded by this User.

photometry

Photometry uploaded by this User.

preferences

The user’s application settings.

recurring_apis
reminders
reminders_on_earthquakes
reminders_on_gcns
reminders_on_shifts
reminders_on_spectra
roles

The roles assumed by this user.

saved_sources
shifts

The Shifts this User is a member of.

single_user_group
source_notifications

Source notifications the User has sent out.

sources

The Sources accessible to this User.

sources_in_gcn

SourcesConfirmedInGCN this User has created.

spectra

Spectra uploaded by this User.

streams

The Streams this User has access to.

survey_efficiency_for_observation_plan

The survey efficiency analyses on ObservationPlans this User has made.

survey_efficiency_for_observations

The survey efficiency analyses on Observations this User has made.

tokens

This user’s tokens.

transaction_requests

The FacilityTransactionRequests initiated by this User.

transactions

The FacilityTransactions initiated by this User.

username

The user’s username.

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_active()

Boolean flag indicating whether the User is currently active.

is_authenticated()

Boolean flag indicating whether the User is currently authenticated.

is_readable_by(user_or_token)

Return a boolean indicating whether a User or Token has read access to this object.

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

user_model()

The base model for User subclasses.

to_dict

property accessible_groups

Return the list of Groups a User or Token has access to. For non-admin Users or Token owners, this corresponds to the Groups they are a member of. For System Admins, this corresponds to all Groups.

property accessible_streams

Return the list of Streams a User or Token has access to.

acls

ACLs granted to user, separate from role-level ACLs

affiliations

The User’s affiliations (ex: Department of Mathematics, University X)

assignments

Objs the User has assigned to ObservingRuns.

catalog_queries

The catalog queries this User has made.

contact_email

The phone number at which the user prefers to receive communications.

contact_phone

The email at which the user prefers to receive communications.

created_at

UTC time of insertion of object’s row into the database.

default_followup_requests

The default follow-up requests this User has made.

default_observationplan_requests

The default observation plan requests this User has made.

delete = <baselayer.app.models.CustomUserAccessControl object>
earthquakeevents

The EarthquakeEvents saved by this user

earthquakenotices

The EarthquakeNotices saved by this user

expiration_date

The date until which the user’s account is valid. Users are set to view-only upon expiration.

first_name

The User’s first name.

followup_requests

The follow-up requests this User has made.

gcnevents

The gcnevents saved by this user

gcnnotices

The GcnNotices saved by this user

gcnproperties

The gcnproperties saved by this user

gcnsummaries

The gcnsummaries saved by this user

gcntags

The gcntags saved by this user

property gravatar_url

The Gravatar URL inferred from the user’s contact email, or, if the contact email is null, the username.

group_admission_requests

User’s requests to join groups.

groups

The Groups this User is a member of.

id

Unique object identifier.

is_active()

Boolean flag indicating whether the User is currently active.

is_authenticated()

Boolean flag indicating whether the User is currently authenticated.

last_name

The User’s last name.

listings

The listings saved by this user

localizationproperties

The localizationproperties saved by this user

localizations

The localizations saved by this user

localizationtags

The localizationtags saved by this user

mmadetector_spectra

MMADetectorSpectra uploaded by this User.

mmadetector_time_intervals

MMADetectorTimeInterval uploaded by this User.

modified

UTC time the object’s row was last modified in the database.

notifications

Notifications to be displayed on front-end associated with User

oauth_uid

The user’s OAuth UID.

observationplan_requests

The observation plan requests this User has made.

observing_runs

Observing Runs this User has created.

property permissions

List of the names of all of the user’s ACLs (role-level + individual).

photometric_series

PhotometricSeries uploaded by this User.

photometry

Photometry uploaded by this User.

preferences

The user’s application settings.

roles

The roles assumed by this user.

shifts

The Shifts this User is a member of.

source_notifications

Source notifications the User has sent out.

sources

The Sources accessible to this User.

sources_in_gcn

SourcesConfirmedInGCN this User has created.

spectra

Spectra uploaded by this User.

streams

The Streams this User has access to.

survey_efficiency_for_observation_plan

The survey efficiency analyses on ObservationPlans this User has made.

survey_efficiency_for_observations

The survey efficiency analyses on Observations this User has made.

to_dict()

Serialize this object to a Python dictionary.

tokens

This user’s tokens.

transaction_requests

The FacilityTransactionRequests initiated by this User.

transactions

The FacilityTransactions initiated by this User.

update = <baselayer.app.models.CustomUserAccessControl object>
classmethod user_model()

The base model for User subclasses.

username

The user’s username.

class baselayer.app.models.UserACL(**kwargs)

Join table mapping Users to ACLs

Attributes:
acl
acl_id
created_at

UTC time of insertion of object’s row into the database.

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

user
user_id

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_readable_by(user_or_token)

Return a boolean indicating whether a User or Token has read access to this object.

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

to_dict()

Serialize this object to a Python dictionary.

create = <baselayer.app.models.AccessibleIfRelatedRowsAreAccessible object>
created_at

UTC time of insertion of object’s row into the database.

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

read = <baselayer.app.models.AccessibleIfRelatedRowsAreAccessible object>
class baselayer.app.models.UserAccessControl

Logic for controlling user access to database records. Mapped classes can set their create, read, update, or delete attributes to subclasses of this class to ensure they are only accessed by users or tokens with the requisite permissions.

Methods

check_cls_for_attributes(cls, attrs)

Check that a target class has the specified attributes.

query_accessible_rows(cls, user_or_token[, ...])

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

select_accessible_rows(cls, user_or_token[, ...])

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

user_id_from_user_or_token(user_or_token)

Return the user_id associated with a specified User or Token object.

static check_cls_for_attributes(cls, attrs)

Check that a target class has the specified attributes. If not, raise a TypeError.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The class to check.

attrslist of str

The names of the attributes to check for.

query_accessible_rows(cls, user_or_token, columns=None)

Construct a Query object that, when executed, returns the rows of a specified table that are accessible to a specified user or token. All query based functions will be deprecated when moving to SQL Alchemy 2.0 in favor of select functions.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
querysqlalchemy.Query

Query for the accessible rows.

select_accessible_rows(cls, user_or_token, columns=None)

Construct a Select object that, when executed, returns the rows of a specified table that are accessible to a specified user or token.

Parameters:
clsbaselayer.app.models.DeclarativeMeta

The mapped class of the target table.

user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

columnslist of sqlalchemy.Column, optional, default None

The columns to retrieve from the target table. If None, queries the mapped class directly and returns mapped instances.

Returns:
sqlalchemy select object
static user_id_from_user_or_token(user_or_token)

Return the user_id associated with a specified User or Token object.

Parameters:
user_or_tokenbaselayer.app.models.User or baselayer.app.models.Token

The User or Token to check.

Returns:
user_id: int

The user_id associated with the User or Token object.

class baselayer.app.models.UserRole(**kwargs)

Join table mapping Users to Roles.

Attributes:
created_at

UTC time of insertion of object’s row into the database.

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

role
role_id
user
user_id

Methods

create_or_get(id)

Return a new cls if an instance with the specified primary key does not exist, else return the existing instance.

get(id_or_list, user_or_token[, mode, ...])

Return a database record if it is accessible to the specified User or Token.

get_all(user_or_token[, mode, options, columns])

Retrieve all database records accessible by the specified User or token.

get_if_accessible_by(cls_id, user_or_token)

Return a database record if it is accessible to the specified User or Token.

get_if_readable_by(ident, user_or_token[, ...])

Return an object from the database if the requesting User or Token has access to read the object.

get_records_accessible_by(user_or_token[, ...])

Retrieve all database records accessible by the specified User or token.

is_accessible_by(user_or_token[, mode])

Check if a User or Token has a specified type of access to this database record.

is_readable_by(user_or_token)

Return a boolean indicating whether a User or Token has read access to this object.

query_records_accessible_by(user_or_token[, ...])

Return the query for all database records accessible by the specified User or token.

select(user_or_token[, mode, options, columns])

Return the select statement for all database records accessible by the specified User or token.

to_dict()

Serialize this object to a Python dictionary.

create = <baselayer.app.models.AccessibleIfRelatedRowsAreAccessible object>
created_at

UTC time of insertion of object’s row into the database.

id

Unique object identifier.

modified

UTC time the object’s row was last modified in the database.

read = <baselayer.app.models.AccessibleIfRelatedRowsAreAccessible object>
baselayer.app.models.bulk_verify(mode, collection, accessor)

Vectorized permission check for a heterogeneous set of records. If an access leak is detected, it will be handled according to the security section of the application’s configuration.

Parameters:
modestr

The access mode to check. Can be create, read, update, or delete.

collectioncollection of baselayer.app.models.Base.

The records to check. These records will be grouped by type, and a single database query will be issued to check access for each record type.

accessorbaselayer.app.models.User or baselayer.app.models.Token

The user or token to check.

baselayer.app.models.init_db(user, database, password=None, host=None, port=None, autoflush=True, engine_args={})
Parameters:
engine_argsdict
  • pool_size: The number of connections maintained to the DB. Default 5.

  • max_overflow: The number of additional connections that will be made as needed.

    Once these extra connections have been used, they are discarded.

    Default 10.

  • pool_recycle:

    Prevent the pool from using any connection that is older than this (specified in seconds). Default 3600.

baselayer.app.models.join_model(join_table, model_1, model_2, column_1=None, column_2=None, fk_1='id', fk_2='id', base=<class 'sqlalchemy.orm.decl_api.Base'>, new_name=None, overlaps=None)

Helper function to create a join table for a many-to-many relationship.

Parameters:
join_tablestr

Name of the new table to be created.

model_1str

First model in the relationship.

model_2str

Second model in the relationship.

column_1str, optional

Name of the join table column corresponding to model_1. If None, then {table1`[:-1]_id} will be used (e.g., `user_id for users).

column_2str, optional

Name of the join table column corresponding to model_2. If None, then {table2`[:-1]_id} will be used (e.g., `user_id for users).

fk_1str, optional

Name of the column from model_1 that the foreign key should refer to.

fk_2str, optional

Name of the column from model_2 that the foreign key should refer to.

basesqlalchemy.ext.declarative.api.DeclarativeMeta

SQLAlchemy model base to subclass.

new_name: str, optional

Name of the new model class. If None, then the name will be model_1.__name__ + model_2.__name__.

overlapsstr or list of str, optional

Each relationship defined in this model will have, by default, overlaps=f”{table_1}, {table_2}”. If any additional overalapping relationships or columns need to be added, they can be given as a string or list of strings.

Returns:
sqlalchemy.ext.declarative.api.DeclarativeMeta

SQLAlchemy association model class

Entity Relationship Diagram

The following entity relationship diagram visualizes the structure of the SkyPortal database.

Skyportal Entity Relationship Diagram