Database Schema

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)#

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)#

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)#

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)#

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)#

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. a `BindParameter` instance), return a SQL expression which will typically wrap the given parameter.
`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))))

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

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

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.
bio: A short biography of the user, or description for bot accounts.
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_gcntags: The default gcn tags 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
gcnreports: The gcnreports 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_bot: Whether or not the user account should be flagged as a bot account.
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.
photometryvalidations: PhotometryValidation this User has created.
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.
tns_submissions: The TNSRobotSubmission this user has made (manual or automatic).
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.

bio#: A short biography of the user, or description for bot accounts.

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_gcntags#: The default gcn tags 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

gcnreports#: The gcnreports 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.

is_bot#: Whether or not the user account should be flagged as a bot account.

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.

photometryvalidations#: PhotometryValidation this User has created.

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.

tns_submissions#: The TNSRobotSubmission this user has made (manual or automatic).

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)#

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.

Database Schema

Contents

Database Schema#

SQLAlchemy Model API Documentation#

Entity Relationship Diagram#