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)', 'varchar(16)[]' 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 (which every array is), and is used to order columns when creating a table.

The declaration is read in the order PostgreSQL writes it: a scalar type, then any array brackets, then a collation. Each layer is validated and the spelling is rebuilt from the validated pieces, so no part of the caller’s string reaches the DDL unchecked.

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

psycodict.base.normalize_storage_param(key, value, access_method)

Check one index storage parameter and return the value to emit.

INPUT:

• key – the parameter name

• value – what it was given, from a caller or from meta_indexes

• access_method – the index type, whose parameters key must be one of

OUTPUT:

The normalized value, which is what both the WITH clause and the metadata row get: a boolean spelled "on" is stored as True, so a definition means the same thing however it was written.

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 identifier.

INPUT:

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

• 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 record. Not applied by default: psycodict derives the names it puts in DDL from an existing one (see derived_identifier()), and a name already in a database is a fact rather than a proposal.

OUTPUT:

name itself.

A quoted identifier can hold anything but a NUL, and psycodict quotes every name with Identifier, so this checks what would actually make a name unusable rather than what it looks like: x-y, index with spaces and idx_é are all legal PostgreSQL names, and a name that looks like SQL is inert once quoted. Control characters are refused as a psycodict policy – they would make its logs and export files unreadable – not because PostgreSQL minds.

psycodict.base.identifier_bytes(name)

The length of an identifier as PostgreSQL measures it: UTF-8 bytes, not Python characters.

psycodict.base.utf8_prefix(name, max_bytes)

The longest prefix of name that fits in max_bytes UTF-8 bytes, cut on a character boundary.

psycodict.base.derived_identifier(base, suffix='', max_length=63)

The name of a relation psycodict derives from another one.

INPUT:

• base – the name it is derived from

• suffix – what psycodict appends: "_tmp", "_old1", "_dep0", "_pkey" and so on

• max_length – the server’s identifier limit, in bytes

OUTPUT:

base + suffix when that fits, which is the overwhelmingly common case and what psycodict has always produced. When it does not fit, the base is cut on a UTF-8 character boundary and a short digest of the whole base is inserted before the suffix, so that two names differing only in the part that was cut do not collide.

Every path that creates, drops, renames or looks for a derived relation must call this, and none may build name + suffix itself: PostgreSQL truncates a name it is given, so a path that computed the name differently would look for a relation that another path had created under a different name.

psycodict.base.parse_check_function(name, valid_check_functions=())

The identifier components of an approved CHECK function.

INPUT:

• name – the function as recorded in meta_constraints, either "function" or "schema.function"

• valid_check_functions – the approved names

OUTPUT:

A tuple of components, to be emitted as Identifier(*components). A qualified name is two identifiers and must not be quoted as one: PostgreSQL reads "schema.function" as a single function whose name contains a dot, which is not the function that was approved.

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_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.