aiida.storage.psql_dos.orm.querybuilder package

Implementation of QueryBuilder backend.

Submodules

A module containing the logic for creating joined queries.

class aiida.storage.psql_dos.orm.querybuilder.joiner.JoinReturn(query, aliased_edge)

Bases: tuple

__annotations__ = {'aliased_edge': typing.Union[sqlalchemy.orm.util.AliasedClass, NoneType], 'query': <class 'sqlalchemy.orm.query.Query'>}

__getnewargs__()

Return self as a plain tuple. Used by copy and pickle.

__module__ = 'aiida.storage.psql_dos.orm.querybuilder.joiner'

static __new__(_cls, query: sqlalchemy.orm.query.Query, aliased_edge: Optional[sqlalchemy.orm.util.AliasedClass] = None)

Create new instance of JoinReturn(query, aliased_edge)

__repr__()

Return a nicely formatted representation string

__slots__ = ()

_asdict()

Return a new dict which maps field names to their values.

_field_defaults = {'aliased_edge': None}

_field_types = {'aliased_edge': typing.Union[sqlalchemy.orm.util.AliasedClass, NoneType], 'query': <class 'sqlalchemy.orm.query.Query'>}

_fields = ('query', 'aliased_edge')

_fields_defaults = {}

classmethod _make(iterable)

Make a new JoinReturn object from a sequence or iterable

_replace(**kwds)

Return a new JoinReturn object replacing specified fields with new values

aliased_edge: Optional[sqlalchemy.orm.util.AliasedClass]

Alias for field number 1

query: sqlalchemy.orm.query.Query

Alias for field number 0

class aiida.storage.psql_dos.orm.querybuilder.joiner.SqlaJoiner(entity_mapper: aiida.storage.psql_dos.orm.querybuilder.joiner._EntityMapper, filter_builder: Callable[[sqlalchemy.orm.util.AliasedClass, Dict[str, Any]], Optional[sqlalchemy.sql.elements.BooleanClauseList]])

Bases: object

A class containing the logic for SQLAlchemy entities joining entities.

__dict__ = mappingproxy({'__module__': 'aiida.storage.psql_dos.orm.querybuilder.joiner', '__doc__': 'A class containing the logic for SQLAlchemy entities joining entities.', '__init__': <function SqlaJoiner.__init__>, 'get_join_func': <function SqlaJoiner.get_join_func>, '_entity_join_map': <function SqlaJoiner._entity_join_map>, '_join_computer_authinfo': <function SqlaJoiner._join_computer_authinfo>, '_join_user_authinfo': <function SqlaJoiner._join_user_authinfo>, '_join_group_node': <function SqlaJoiner._join_group_node>, '_join_node_group': <function SqlaJoiner._join_node_group>, '_join_node_user': <function SqlaJoiner._join_node_user>, '_join_user_node': <function SqlaJoiner._join_user_node>, '_join_computer_node': <function SqlaJoiner._join_computer_node>, '_join_node_computer': <function SqlaJoiner._join_node_computer>, '_join_group_user': <function SqlaJoiner._join_group_user>, '_join_user_group': <function SqlaJoiner._join_user_group>, '_join_node_comment': <function SqlaJoiner._join_node_comment>, '_join_comment_node': <function SqlaJoiner._join_comment_node>, '_join_node_log': <function SqlaJoiner._join_node_log>, '_join_log_node': <function SqlaJoiner._join_log_node>, '_join_user_comment': <function SqlaJoiner._join_user_comment>, '_join_authinfo_user': <function SqlaJoiner._join_authinfo_user>, '_join_comment_user': <function SqlaJoiner._join_comment_user>, '_join_node_outputs': <function SqlaJoiner._join_node_outputs>, '_join_node_inputs': <function SqlaJoiner._join_node_inputs>, '_join_node_descendants_recursive': <function SqlaJoiner._join_node_descendants_recursive>, '_join_node_ancestors_recursive': <function SqlaJoiner._join_node_ancestors_recursive>, '__dict__': <attribute '__dict__' of 'SqlaJoiner' objects>, '__weakref__': <attribute '__weakref__' of 'SqlaJoiner' objects>, '__annotations__': {}})

__init__(entity_mapper: aiida.storage.psql_dos.orm.querybuilder.joiner._EntityMapper, filter_builder: Callable[[sqlalchemy.orm.util.AliasedClass, Dict[str, Any]], Optional[sqlalchemy.sql.elements.BooleanClauseList]])

Initialise the class

__module__ = 'aiida.storage.psql_dos.orm.querybuilder.joiner'

__weakref__

list of weak references to the object (if defined)

_entity_join_map() → Dict[str, Dict[str, Callable[[sqlalchemy.orm.query.Query, Type[aiida.storage.psql_dos.models.base.Model], Type[aiida.storage.psql_dos.models.base.Model], bool, Dict[str, Any], bool], aiida.storage.psql_dos.orm.querybuilder.joiner.JoinReturn]]]

Map relationship type keywords to functions The first level defines the entity which has been passed to the qb.append function, and the second defines the relationship with respect to a given tag.

_join_authinfo_user(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased comment

• entity_to_join – aliased user

_join_comment_node(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased comment

• entity_to_join – aliased node

_join_comment_user(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased comment

• entity_to_join – aliased user

_join_computer_authinfo(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – the aliased user you want to join to

• entity_to_join – the (aliased) node or group in the DB to join with

_join_computer_node(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – the (aliased) computer entity

• entity_to_join – the (aliased) node entity

_join_group_node(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – The (aliased) ORMclass that is a group in the database

• entity_to_join – The (aliased) ORMClass that is a node and member of the group

joined_entity and entity_to_join are joined via the table_groups_nodes table. from joined_entity as group to enitity_to_join as node. (enitity_to_join is with_group joined_entity)

_join_group_user(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased dbgroup

• entity_to_join – aliased dbuser

_join_log_node(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased log

• entity_to_join – aliased node

_join_node_ancestors_recursive(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, filter_dict: Dict[str, Any], expand_path=False)

joining ancestors using the recursive functionality :TODO: Move the filters to be done inside the recursive query (for example on depth) :TODO: Pass an option to also show the path, if this is wanted.

_join_node_comment(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased node

• entity_to_join – aliased comment

_join_node_computer(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An entity that can use a computer (eg a node)

• entity_to_join – aliased dbcomputer entity

_join_node_descendants_recursive(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, filter_dict: Dict[str, Any], expand_path=False)

joining descendants using the recursive functionality :TODO: Move the filters to be done inside the recursive query (for example on depth) :TODO: Pass an option to also show the path, if this is wanted.

_join_node_group(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – The (aliased) node in the database

• entity_to_join – The (aliased) Group

joined_entity and entity_to_join are joined via the table_groups_nodes table. from joined_entity as node to enitity_to_join as group. (enitity_to_join is a group with_node joined_entity)

_join_node_inputs(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – The (aliased) ORMclass that is an output

• entity_to_join – The (aliased) ORMClass that is an input.

joined_entity and entity_to_join are joined with a link from joined_entity as output to enitity_to_join as input (enitity_to_join is with_outgoing joined_entity)

_join_node_log(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased node

• entity_to_join – aliased log

_join_node_outputs(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – The (aliased) ORMclass that is an input

• entity_to_join – The (aliased) ORMClass that is an output.

joined_entity and entity_to_join are joined with a link from joined_entity as input to enitity_to_join as output (enitity_to_join is with_incoming joined_entity)

_join_node_user(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – the aliased node

• entity_to_join – the aliased user to join to that node

_join_user_authinfo(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – the aliased user you want to join to

• entity_to_join – the (aliased) node or group in the DB to join with

_join_user_comment(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased user

• entity_to_join – aliased comment

_join_user_group(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – An aliased user

• entity_to_join – aliased group

_join_user_node(query: sqlalchemy.orm.query.Query, joined_entity, entity_to_join, isouterjoin: bool, **_kw)

Parameters

• joined_entity – the aliased user you want to join to

• entity_to_join – the (aliased) node or group in the DB to join with

get_join_func(entity_key: str, relationship: str) → Callable[[sqlalchemy.orm.query.Query, Type[aiida.storage.psql_dos.models.base.Model], Type[aiida.storage.psql_dos.models.base.Model], bool, Dict[str, Any], bool], aiida.storage.psql_dos.orm.querybuilder.joiner.JoinReturn]

Return the function to join two entities

class aiida.storage.psql_dos.orm.querybuilder.joiner._EntityMapper(*args, **kwargs)

Bases: Protocol

Mapping of implemented entity types.

property AuthInfo: Type[aiida.storage.psql_dos.models.base.Model]

property Comment: Type[aiida.storage.psql_dos.models.base.Model]

property Computer: Type[aiida.storage.psql_dos.models.base.Model]

property Group: Type[aiida.storage.psql_dos.models.base.Model]

property Link: Type[aiida.storage.psql_dos.models.base.Model]

property Log: Type[aiida.storage.psql_dos.models.base.Model]

property Node: Type[aiida.storage.psql_dos.models.base.Model]

property User: Type[aiida.storage.psql_dos.models.base.Model]

__abstractmethods__ = frozenset({})

__dict__ = mappingproxy({'__module__': 'aiida.storage.psql_dos.orm.querybuilder.joiner', '__doc__': 'Mapping of implemented entity types.', 'AuthInfo': <property object>, 'Node': <property object>, 'Group': <property object>, 'Link': <property object>, 'User': <property object>, 'Computer': <property object>, 'Comment': <property object>, 'Log': <property object>, 'table_groups_nodes': <property object>, '__dict__': <attribute '__dict__' of '_EntityMapper' objects>, '__weakref__': <attribute '__weakref__' of '_EntityMapper' objects>, '__parameters__': (), '_is_protocol': True, '__subclasshook__': <function Protocol.__init_subclass__.<locals>._proto_hook>, '__init__': <function _no_init>, '__abstractmethods__': frozenset(), '_abc_impl': <_abc_data object>, '__annotations__': {}})

__init__(*args, **kwargs)

__module__ = 'aiida.storage.psql_dos.orm.querybuilder.joiner'

__parameters__ = ()

__subclasshook__()

Abstract classes can override this to customize issubclass().

This is invoked early on by abc.ABCMeta.__subclasscheck__(). It should return True, False or NotImplemented. If it returns NotImplemented, the normal algorithm is used. Otherwise, it overrides the normal algorithm (and the outcome is cached).

__weakref__

list of weak references to the object (if defined)

_abc_impl = <_abc_data object>

_is_protocol = True

property table_groups_nodes: Type[sqlalchemy.sql.schema.Table]

aiida.storage.psql_dos.orm.querybuilder.joiner._check_dbentities(entities_cls_joined, entities_cls_to_join, relationship: str)

Type check for entities

Parameters

• entities_cls_joined – A tuple of the aliased class passed as joined_entity and the ormclass that was expected

• entities_cls_joined – A tuple of the aliased class passed as entity_to_join and the ormclass that was expected

• relationship (str) – The relationship between the two entities to make the Exception comprehensible

Sqla query builder implementation

class aiida.storage.psql_dos.orm.querybuilder.main.SqlaQueryBuilder(backend)

Bases: aiida.orm.implementation.querybuilder.BackendQueryBuilder

QueryBuilder to use with SQLAlchemy-backend and schema defined in backends.sqlalchemy.models

property AuthInfo

property Comment

property Computer

property Group

property Link

property Log

property Node

property User

__abstractmethods__ = frozenset({})

__init__(backend)

Parameters

backend – the backend

__module__ = 'aiida.storage.psql_dos.orm.querybuilder.main'

_abc_impl = <_abc_data object>

_add_to_projections(alias: sqlalchemy.orm.util.AliasedClass, projectable_entity_name: str, cast: Optional[str] = None, func: Optional[str] = None, **_kw: Any) → None

Parameters

• alias – An alias for an ormclass

• projectable_entity_name – User specification of what to project. Appends to query’s entities what the user wants to project (have returned by the query)

_build() → sqlalchemy.orm.query.Query

build the query and return a sqlalchemy.Query instance

_build_join_func(index: int, joining_keyword: str, joining_value: str)

Parameters

• index – Index of this node within the path specification

• joining_keyword – the relation on which to join

• joining_value – the tag of the nodes to be joined

_build_order_by(alias: sqlalchemy.orm.util.AliasedClass, field_key: str, entityspec: dict) → None

Build the order_by parameter of the query.

_build_projections(tag: str, projection_count: int, items_to_project: Optional[List[Dict[str, dict]]] = None) → int

Build the projections for a given tag.

static _compile_query(query: sqlalchemy.orm.query.Query, literal_binds: bool = False) → sqlalchemy.sql.compiler.SQLCompiler

Compile the query to the SQL executable.

Params literal_binds

Inline bound parameters (this is normally handled by the Python DBAPI).

_get_projectable_entity(alias: sqlalchemy.orm.util.AliasedClass, column_name: str, attrpath: List[str], cast: Optional[str] = None) → Union[sqlalchemy.sql.elements.ColumnElement, sqlalchemy.orm.attributes.InstrumentedAttribute]

Return projectable entity for a given alias and column name.

_get_tag_alias(tag: str) → sqlalchemy.orm.util.AliasedClass

Get the alias of a tag

_update_query(data: aiida.orm.implementation.querybuilder.QueryDictType) → sqlalchemy.orm.query.Query

Return the sqlalchemy.orm.Query instance for the current query specification.

To avoid unnecessary re-builds of the query, the hashed dictionary representation of this instance is compared to the last query returned, which is cached by its hash.

analyze_query(data: aiida.orm.implementation.querybuilder.QueryDictType, execute: bool = True, verbose: bool = False) → str

Return the query plan, i.e. a list of SQL statements that will be executed.

See: https://www.postgresql.org/docs/11/sql-explain.html

Params execute

Carry out the command and show actual run times and other statistics.

Params verbose

Display additional information regarding the plan.

as_sql(data: aiida.orm.implementation.querybuilder.QueryDictType, inline: bool = False) → str

Convert the query to an SQL string representation.

Warning

This method should be used for debugging purposes only, since normally sqlalchemy will handle this process internally.

Params inline

Inline bound parameters (this is normally handled by the Python DBAPI).

build_filters(alias: sqlalchemy.orm.util.AliasedClass, filter_spec: Dict[str, Any]) → Optional[sqlalchemy.sql.elements.BooleanClauseList]

Recurse through the filter specification and apply filter operations.

Parameters

• alias – The alias of the ORM class the filter will be applied on

• filter_spec – the specification of the filter

Returns

an sqlalchemy expression.

count(data: aiida.orm.implementation.querybuilder.QueryDictType) → int

Return the number of results of the query

first(data: aiida.orm.implementation.querybuilder.QueryDictType) → Optional[List[Any]]

Executes query, asking for one instance.

Returns

One row of aiida results

static get_column(colname: str, alias: sqlalchemy.orm.util.AliasedClass) → sqlalchemy.orm.attributes.InstrumentedAttribute

Return the column for a given projection.

static get_column_names(alias: sqlalchemy.orm.util.AliasedClass) → List[str]

Given the backend specific alias, return the column names that correspond to the aliased table.

classmethod get_corresponding_properties(entity_table: str, given_properties: List[str], mapper: Dict[str, Dict[str, str]])

This method returns a list of updated properties for a given list of properties. If there is no update for the property, the given property is returned in the list.

classmethod get_corresponding_property(entity_table: str, given_property: str, mapper: Dict[str, Dict[str, str]]) → str

This method returns an updated property for a given a property. If there is no update for the property, the given property is returned.

get_creation_statistics(user_pk: Optional[int] = None) → Dict[str, Any]

Return a dictionary with the statistics of node creation, summarized by day.

Note

Days when no nodes were created are not present in the returned ctime_by_day dictionary.

Parameters

user_pk – If None (default), return statistics for all users. If user pk is specified, return only the statistics for the given user.

Returns

a dictionary as follows:

{
   "total": TOTAL_NUM_OF_NODES,
   "types": {TYPESTRING1: count, TYPESTRING2: count, ...},
   "ctime_by_day": {'YYYY-MMM-DD': count, ...}
}

where in ctime_by_day the key is a string in the format ‘YYYY-MM-DD’ and the value is an integer with the number of nodes created that day.

get_filter_expr(operator: str, value: Any, attr_key: List[str], is_jsonb: bool, alias=None, column=None, column_name=None)

Applies a filter on the alias given.

Expects the alias of the ORM-class on which to filter, and filter_spec. Filter_spec contains the specification on the filter. Expects:

Parameters

• operator – The operator to apply, see below for further details

• value – The value for the right side of the expression, the value you want to compare with.

• path – The path leading to the value

• is_jsonb – Whether the value is in a json-column, or in an attribute like table.

Implemented and valid operators:

• for any type: * == (compare single value, eg: ‘==’:5.0) * in (compare whether in list, eg: ‘in’:[5, 6, 34]

• for floats and integers:

  • >

  • <

  • <=

  • >=

• for strings:

  • like (case - sensitive), for example ‘like’:’node.calc.%’ will match node.calc.relax and node.calc.RELAX and node.calc. but not node.CALC.relax

  • ilike (case - unsensitive) will also match node.CaLc.relax in the above example

  Note

  The character % is a reserved special character in SQL, and acts as a wildcard. If you specifically want to capture a % in the string, use: _%

• for arrays and dictionaries (only for the SQLAlchemy implementation):

  • contains: pass a list with all the items that the array should contain, or that should be among the keys, eg: ‘contains’: [‘N’, ‘H’])

  • has_key: pass an element that the list has to contain or that has to be a key, eg: ‘has_key’:’N’)

• for arrays only (SQLAlchemy version):

  • of_length

  • longer

  • shorter

All the above filters invoke a negation of the expression if preceded by ~:

# first example:
filter_spec = {
    'name' : {
        '~in':[
            'halle',
            'lujah'
        ]
    } # Name not 'halle' or 'lujah'
}

# second example:
filter_spec =  {
    'id' : {
        '~==': 2
    }
} # id is not 2

static get_filter_expr_from_column(operator: str, value: Any, column) → sqlalchemy.sql.elements.BinaryExpression

A method that returns an valid SQLAlchemy expression.

Parameters

• operator – The operator provided by the user (‘==’, ‘>’, …)

• value – The value to compare with, e.g. (5.0, ‘foo’, [‘a’,’b’])

• column – an instance of sqlalchemy.orm.attributes.InstrumentedAttribute or

get_filter_expr_from_jsonb(operator: str, value, attr_key: List[str], column=None, column_name=None, alias=None)

Return a filter expression

get_projectable_attribute(alias: sqlalchemy.orm.util.AliasedClass, column_name: str, attrpath: List[str], cast: Optional[str] = None) → sqlalchemy.sql.elements.ColumnElement

Return an attribute store in a JSON field of the give column

get_session() → sqlalchemy.orm.session.Session

Returns

a valid session, an instance of sqlalchemy.orm.session.Session

static get_table_name(aliased_class: sqlalchemy.orm.util.AliasedClass) → str

Returns the table name given an Aliased class

iterall(data: aiida.orm.implementation.querybuilder.QueryDictType, batch_size: Optional[int]) → Iterable[List[Any]]

Return an iterator over all the results of a list of lists.

iterdict(data: aiida.orm.implementation.querybuilder.QueryDictType, batch_size: Optional[int]) → Iterable[Dict[str, Dict[str, Any]]]

Return an iterator over all the results of a list of dictionaries.

modify_expansions(alias: sqlalchemy.orm.util.AliasedClass, expansions: List[str]) → List[str]

Modify names of projections if ** was specified.

This is important for the schema having attributes in a different table. In SQLA, the metadata should be changed to _metadata to be in-line with the database schema

rebuild_aliases() → None

Rebuild the mapping of tag -> alias

set_field_mappings()

Set conversions between the field names in the database and used by the QueryBuilder

property table_groups_nodes

to_backend(res) → Any

Convert results to return backend specific objects.

• convert DbModel instances to BackendEntity instances.

• convert UUIDs to strings

Parameters

res – the result returned by the query

:returns:backend compatible instance

use_query(data: aiida.orm.implementation.querybuilder.QueryDictType) → Iterator[sqlalchemy.orm.query.Query]

Yield the built query.

previous

aiida.storage.psql_dos.orm package

next

aiida.storage.sqlite_zip package