Schemas

Module for DB schema manipulation

class networkdisk.sql.schema.Schema(dialect, name=None)

A Mapping from names to SchemaObjects, whose classes are give in the SchemaChildClasses class attribute.

add_index(container, columns, *args, name=None, **kwargs)

Wrapper of self.SchemaChildClasses[‘index’] with default improved default naming, based on the names of container and indexed columns.

add_trigger(container, when, action, *args, name=None, **kwargs)

Wrapper of self.SchemaChildClasses[‘trigger’] with default improved default naming, based on names of the container and the event to capture (when and action).

class networkdisk.sql.schema.SchemaContainer(dialect, name)

An intermediate abstract class to represent SQL containers, namely, VIEWs or TABLEs. Both accept many kinds of queries, including creation, selection, insertion and update. Their subformat is minimal: it indeed returns the container name.

create_trigger_query

A trigger is a collection of write queries to be executed when some event occurs. The events are of the kind: - “INSTEAD OF <action> ON <container>” - “AFTER <action> ON <container>” - “BEFORE <action> ON <container>”

where ‘<action>’ is one of “INSERT”, “DELETE”, “UPDATE”, or “UPDATE OF <coma separated column list>”, and <container> is a container object such as VIEWs or TABLEs.

Parameters:

when: str

either “INSTEAD OF”, “BEFORE”, or “AFTER”;

action: str | tuple of (str, columns)

either “INSERT”, “DELETE”, “UPDATE”, or a pair of the form (“UPDATE”, cols) where cols is a list of container columns;

container:

a container (SchemaView or SchemaTable);

subqueries:

a tuple of queries to be executed when the event happen.

insert_query

A class for Insert Queries

Parameters:

container: SQL container

Typically a table or a view. See schema module.

query: ReadQueryFromQueries

Any query from which values to be inserted are taken.

columns: iterable of columns of `container`

The columns of container in which values should be inserted. If STAR then all columns are non-explicitly taken. If None then the columns are automatically determined from the external columns of query, starting by making the correspondence between columns with matching names, and then associating the remaining columns with the untaken columns of container, taken in the order of the container.external_columns iterable.

Attributes:

_insert: str

TODO: should it be in the doc?

insert_values(*valuetuples, columns=None, replace=False, **kwargs)

Parameters:

valuetuples:

a tuple of either values or same-length tuples;

columns:

the ordered iterable of columns in which values should be inserted; If None (default), then all columns but those specified in kwargs are assumed, except when the tuple valuetuples is empty, in which case no columns are assumed;

kwargs:

a dict keyed by container column names, and whose values should be inserted in the corresponding column. The keyed columns should be disjoint from columns.

Examples

>>> t.insert_values( (3, 4, 5), (6, 7, 8), columns=('id', 1, t[2]), key="value" ) 

⇒ inserts the rows (3, 6, “value”), (4, 7, “value”), and (5, 8, “value”) in the columns t[‘id’], t[1], t[2], and t[‘key’].

select_query

A SelectQuery is a selection of columns of another query, which can be of any kind. It has attributes:

TODO: The following is outdated doc.

Parameters:

subqueries:

a possibly empty iterable of subqueries from which the columns are taken;

columns: list | None, default=None

a list of specifications of the selected columns. If None the list is automatically computed from the exposed columns of the subqueries.

aliases: dict or tuples of key/values, default=()

a dictionary defining a partial mapping from column names (typically str) or indices (int) to aliases (str). The mapping is partial, column with no associated alias are just left unaliased. The default value is () which is interpreted as the empty mapping. When looking for a specific column alias, indices have precedence over names.

condition: a condition or None, default=None

A condition to apply to the query in the WHERE clause.

orderby: iterable | None, default=None

a set of columns that are used for ordering the selected tuples, or the value None;

distinct: bool, default=False

if True, add the FILTER keyword to prevent multiple occurrence to be return.

groupby: tuple of internal columns or None, default=None

tuple of internal columns for grouping.

desc: bool | None, default=None

a Boolean if orderby is not None, indicating whether the order should be descending or ascending, or the value None if unspecified;

limit: int | None, delault=None

a limit (int) on the number of tuples to select or None;

offset: int | None, default=None

an offset (int) when limit is not None, or None.

update_query

Class to build Update Query

Parameters:

container: SQLContainer

see SchemaContainer (view or table) to update in sql.schema module

values: dict | iterable of key,value, default=()

A dictionary mapping column specification (name, index, or the column itself) to associated value to introduce.

condition: condition | None, default=None

a condition that rows to update should satisfy.

**kwargs:

a keyworded list of parameters to update values.

class networkdisk.sql.schema.SchemaIndex(dialect, container, columns, name=None, condition=None, unique=False, ifnotexists=False)

class networkdisk.sql.schema.SchemaInnerNode(dialect, name)

class networkdisk.sql.schema.SchemaNode(dialect, name)

This abstract class has two goals: 1. Structure the Schema as a single-chained top-down tree, except for leaves, namely QueryColumn`s, which point to their parent table (`SchemaTable). Top-down links are stored by name in the children dictionary attribute of internal nodes.

2. Propose dump and load serialization method for storing the schema in file or db.

Parameters:

name: str

a name to identified the node.

class networkdisk.sql.schema.SchemaObject(dialect, name)

A creatable SQL object: TABLE, VIEW, TRIGGER, or INDEX.

class networkdisk.sql.schema.SchemaTable(dialect, name, defquery=None, constraints=(), temporary=False)

The SQL tables

add_column(*, dialect=None, name, container_name=None, index_in_container=None, sqltype=None, encoder=None, references=None, on_delete=None, on_update=None, deferrable=None, initially_deferred=None, primarykey=False, autoincrement=None, notnull=False, unique=False, on_conflict=None, default=None, check=None, constraints=())

partial(func, *args, **keywords) - new function with partial application of the given arguments and keywords.

class networkdisk.sql.schema.SchemaTableColumn(dialect, name, container_name, index_in_container, sqltype=None, encoder=None, references=None, on_delete=None, on_update=None, deferrable=None, initially_deferred=None, primarykey=False, autoincrement=None, notnull=False, unique=False, on_conflict=None, default=None, check=None, constraints=())

Class to represent table column.

A column which is both a selectable QueryColumn and a leaf of the schema tree. Additionally, the object has several attributes.

Parameters:

name: str

the QueryColumn name;

sqltype: str | SQLType | None, default=None

either a string indicating an SQL type (e.g., “INTEGER”, “TEXT”), or another instance of QueryColumn. In the latter case, the column SQL type is set to the value of the sqltype attribute of the given other column. When a reference is given (see references parameter), the given sqltype is expected to be None (default), so it can be replaced by the referenced column to set the SQL type as explained above. In any other case, the parameter should be provided; otherwise an NetworkDiskSQLError exception is raised.

encoder: str | None, default=None

a key of the encoderFunctions dictionary, that allows to set the encoder and decoder functions for the column. When not provided, the default encoder for the type is taken. The so-determined encoder and decoder function are used to initialize the object as QueryColumn.

primarykey: bool, default=False

Either False (default), True or a nonempty string to be used as on_conflict for the corresponding constraint (e.g., “IGNORE”), with precedence over the on_conflict keyworded argument.

references: None | QueryColumn, default=None

Either the value None (default) or another instance of QueryColumn to which the instance to initialize references as foreign key.

constraints: iterable, default=()

An iterable (default is ()) of constraint specifications which are either callable objects returning a SchemaConstraint when call with self as argument, or, directly, SchemaConstraint.

on_delete: str | None, default=None

What to do in case of delete, e.g; “CASCADE”, “DO NOTHING”

on_update: str | None, default=None

What to do in case of delete, e.g; “CASCADE”, “DO NOTHING”

autoincrement: bool, default=False

If True, then primarykey is automatically changed to True, and the column type (sqltype attribute) is expected to be “INTEGER”.

notnull:bool, default=False

If True then the column is set to be NOT NULL.

unique:bool, default=False

If True then the column is set to not accept duplicates values.

onconflict:

Default on_conflict for ConflictableConstraint`s (given by `primarykey, unique, or notnull) if their values are True.

check:

Either a condition or a callable object, which returns a condition when call with the column self as argument. The default value None is interpreted as the empty condition.

Notes

Once the encoder and decoder functions have been determined for the column, it is initialized as QueryColumn using its name (name) and the encoder key (encoder).

set_constraints(constraints, references=None, on_delete=None, on_update=None, deferrable=None, initially_deferred=None, primarykey=False, autoincrement=None, unique=False, notnull=False, on_conflict=None, default=None, check=None)

class networkdisk.sql.schema.SchemaTrigger(when, action, *queries, name=None, ifnotexists=False, temporary=False)

class networkdisk.sql.schema.SchemaView(dialect, name, defquery, column_names=())

Class to define views.

Parameters:

name: str

Name of the container (here, a VIEW), to be created. This argument is passed to the __init__ method of the class SchemaContainer.

defquery: Query

A query (str, dict, tuple) that select the rows to be represented by the view. This should be defined before creating the view (method creation_scripts), but can be let to its default None (namely, undefined) value before. In particular, if the SchemaView points an already created Schema VIEW, then this parameter is not required (although it is recommended to keep it for tractability).

column_names:

A partial mapping from external column specifications of the query defquery to names. If an external column of defquery has an associated name (namely, an alias), then the view uses this name as external column name.

contdef:

A list of additional keyworded arguments to be passed to the __init__ method of the SchemaContainer class.