psycodict.base

The shared plumbing underneath every psycodict object.

PostgresBase is the common base of the database, table and statistics classes; it owns statement execution through _execute (logging, slow-query warnings, commit/rollback bookkeeping and reconnection) together with helpers for inspecting tables, indexes and constraints. The module also defines the layout of the meta_* tables – the column lists, types and creation statements shared by everything that reads or writes them – and the metadata format version (META_FORMAT) stamped into meta_format.

exception psycodict.base.InvalidColumnTypeError

Bases: ValueError, RuntimeError

Raised for a column type psycodict will not put into a statement.

A ValueError, since an unusable type is a bad argument, and also a RuntimeError, which is what psycodict raised for an invalid type before 1.0.0 and what existing callers may catch.

psycodict.base.validate_column_type(typ)

Check that typ is a PostgreSQL column type psycodict is willing to create, and return the spelling that callers must put into DDL.

Validation is centralized here because a column type is interpolated into CREATE TABLE and ALTER TABLE statements as SQL text rather than bound as a value: PostgreSQL has no placeholder for a type. Callers must emit the returned spelling and never the string they passed in, since the two are equal only for input that needed no normalization.

INPUT:

• typ – a string, e.g. 'bigint', 'numeric(10, 2)' or 'text COLLATE "C"'. Surrounding whitespace is ignored.

OUTPUT:

A pair (sql_spelling, storage_cost). storage_cost is the width of the type in bytes, or -1 if it is variable, and is used to order columns when creating a table.

Raises InvalidColumnTypeError (a ValueError) on anything else, including a type that merely starts with a valid type.

psycodict.base.column_type_sql(typ)

The SQL fragment for a column type, validated by validate_column_type().

INPUT:

• typ – a string giving a PostgreSQL column type

OUTPUT:

A psycopg.sql.SQL fragment naming the type, ready to be interpolated into a CREATE TABLE or ALTER TABLE statement.

psycodict.base.jsonb_idx(cols, cols_type)

The positions in cols whose type is jsonb, as a tuple of indexes. Used to decide which values need json decoding when reading rows of the meta_* tables.

INPUT:

• cols – a list of column names

• cols_type – a dictionary mapping column names to their types

exception psycodict.base.InvalidDefinitionError

Bases: ValueError

Raised for an index or constraint definition psycodict will not build DDL from, whether it came from a caller, a metadata file or a meta_* row.

psycodict.base.validate_relation_name(name, kind='Relation', max_length=None)

Check that name can be used as a PostgreSQL relation name.

INPUT:

• name – the name of a table, index or constraint

• kind – what the name names, used in the error message

• max_length – a byte length to hold the name to, for a name psycodict is being asked to create. Not applied by default: psycodict builds the names it uses in DDL by appending _tmp or _oldN to an existing one, and an index created at the 63-byte limit would then fail every reload rather than being truncated by PostgreSQL as it always was.

OUTPUT:

name itself. Names are quoted with Identifier wherever they are used, so this is not what stops injection; it stops a name that no Identifier could round-trip, or that came from somewhere it should not have.

psycodict.base.validate_column_name(name)

Check a column name an index or constraint definition refers to.

Columns are quoted with Identifier wherever they are used, and a column that exists is a column whatever it is called – the LMFDB has one called 2adic_index – so this checks only that the name is a string psycodict can put in a statement at all.

psycodict.base.validate_search_table_name(name, reserved=(), strict=True)

Check that name can be used as the name of a search table.

INPUT:

• name – the proposed or recorded name

• reserved – names that are taken for another purpose on the database object (its attributes and methods), which a new table may not shadow

• strict – whether to apply the conventions a new name must follow, as opposed to the rules that keep an existing one safe to use

OUTPUT:

name itself.

A search table’s name is used in more places than a relation name: as an identifier, as the key of a table object on the database, and as the stem of the files copy_to generates. A name containing a path separator or a .. component would send an export outside the directory it was asked for, so that much is checked of every name, however it arrives.

The rest – lowercase spelling, and staying clear of the suffixes psycodict appends to a search table’s own name – is a convention for names psycodict is being asked to create. It is not applied to a name a database already has, since that database is a fact: the LMFDB, for one, has a search table called hgcwa_per_group_stats, and refusing to connect to a database on account of a name that has worked for years would be a worse failure than the one being prevented.

psycodict.base.validate_index_predicate(predicate)

Check the predicate of a partial index.

The predicate is administrative raw SQL: psycodict does not parse it, and create_index documents that it is trusted input. What this rules out is a predicate that does not stay a predicate – one that ends the CREATE INDEX statement it is appended to, or comments out the rest of it – so that a poisoned meta_indexes row cannot turn a restore into two statements.

INPUT:

• predicate – a string giving the WHERE clause of a partial index

OUTPUT:

The predicate, stripped of surrounding whitespace.

This is deliberately conservative: a predicate that needs a semicolon, a comment or a dollar-quoted string is rejected rather than analyzed.

psycodict.base.index_modifier_sql(modifier, type)

The SQL for one modifier of one index column.

INPUT:

• modifier – a modifier normalized by validate_index_definition()

• type – the access method of the index

OUTPUT:

A fixed SQL constant for a direction or null placement, and a quoted identifier for an operator class. Nothing here is built by formatting the stored string into SQL text.

class psycodict.base.IndexDefinition(name, table, access_method, columns, modifiers, storage_params, whereclause)

Bases: tuple

access_method

Alias for field number 2

columns

Alias for field number 3

modifiers

Alias for field number 4

name

Alias for field number 0

storage_params

Alias for field number 5

table

Alias for field number 1

whereclause

Alias for field number 6

class psycodict.base.ConstraintDefinition(name, table, constraint_type, columns, check_func)

Bases: tuple

check_func

Alias for field number 4

columns

Alias for field number 3

constraint_type

Alias for field number 2

name

Alias for field number 0

table

Alias for field number 1

psycodict.base.validate_index_definition(name, table, type, columns, modifiers, storage_params, whereclause=None, valid_columns=None)

Check an index definition and return it normalized.

INPUT:

• name, table – the names of the index and of the relation it is built on. name may be None when the caller has not generated it yet (create_index derives it from the columns it is validating here).

• type – the access method, one of the keys of _operator_classes

• columns – a nonempty list of column names

• modifiers – a list, of the same length as columns, of lists of modifiers for each column: an operator class valid for type, a direction and a null placement

• storage_params – a dictionary of storage parameters valid for type

• whereclause – the predicate of a partial index, or None

• valid_columns – the columns of the relation, if known; when given, every column of the index must be one of them

OUTPUT:

An IndexDefinition. Its modifiers are canonicalized to the spellings in _operator_classes and _index_modifiers and sorted into the order PostgreSQL expects, so the statement builder never emits a string that came out of the metadata.

psycodict.base.validate_constraint_definition(name, table, type, columns, check_func, valid_columns=None, valid_check_functions=())

Check a constraint definition and return it normalized.

INPUT:

• name, table – the names of the constraint and of the relation it applies to

• type – "UNIQUE", "CHECK" or "NOT NULL"

• columns – a nonempty list of column names; NOT NULL takes one

• check_func – for a CHECK constraint, the name of the function it calls, which must be one of valid_check_functions; None otherwise

• valid_columns – the columns of the relation, if known

• valid_check_functions – the approved check functions, normally PostgresTable._valid_check_functions

OUTPUT:

A ConstraintDefinition.

class psycodict.base.PostgresBase(loggername, db)

Bases: object

A base class for various objects that interact with Postgres.

Any class inheriting from this one must provide a connection to the postgres database, as well as a name used when creating a logger.