psycodict.validation

What psycodict will put into a statement.

Almost all of psycodict’s SQL is composed from bound placeholders and quoted identifiers, which are safe whatever the values are. The exceptions are the things PostgreSQL has no placeholder for – a column type above all – which have to be interpolated as SQL text. This module is where those are checked, in one place, so that the rule is the same wherever the value came from: a caller, the header of a data file, or a row of a meta_* table that something else edited. Alongside them sit the identifier rules: what names psycodict will create, and how it derives the name of a companion table or a _tmp index from one it was given.

Nothing here touches a connection: every function takes a value and returns the spelling to emit, or raises.

exception psycodict.validation.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.validation.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.

EXAMPLES:

>>> from psycodict.validation import validate_column_type
>>> validate_column_type("bigint")
('bigint', 8)
>>> validate_column_type("  TEXT  ")
('text', -1)
>>> validate_column_type('varchar(16)[] COLLATE "C"')
('varchar(16)[] COLLATE "C"', -1)
>>> validate_column_type("text; DROP TABLE students; --")
Traceback (most recent call last):
...
psycodict.validation.InvalidColumnTypeError: 'text; DROP TABLE students; --' is not a valid type: it contains the character ';'

psycodict.validation.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.

EXAMPLES:

>>> from psycodict.validation import column_type_sql
>>> column_type_sql("numeric(10, 2)").as_string()
'numeric(10, 2)'

psycodict.validation.array_cast_type_sql(typ)

The SQL fragment for casting to an array whose elements have type typ, validated by validate_column_type().

This is not column_type_sql(typ) with [] appended. A column type may end in a COLLATE clause, which belongs to the column rather than to the type: text COLLATE "C"[] is a syntax error, and the cast target wants the data type alone. So the collation is dropped and the dimension is added to the type underneath it.

INPUT:

• typ – a string giving a PostgreSQL column type

OUTPUT:

A psycopg.sql.SQL fragment naming the array type, ready to be interpolated after a ::.

EXAMPLES:

>>> from psycodict.validation import array_cast_type_sql
>>> array_cast_type_sql("text").as_string()
'text[]'
>>> array_cast_type_sql("integer[]").as_string()
'integer[][]'
>>> array_cast_type_sql('text COLLATE "C"').as_string()
'text[]'

psycodict.validation.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.validation.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.validation.validate_persistent_name(name, kind='Relation')

Check a name that is stored as the key of a relation.

A persistent name – an index name in meta_indexes, a constraint name in meta_constraints, the table name they refer to – has to be the catalog name of the relation it describes. PostgreSQL truncates a name over its limit rather than refusing it, so a longer string cannot be one: the metadata would be naming a relation that does not exist.

This is the distinction derived_identifier() must not blur. A physical name psycodict builds by appending _tmp may be shortened, because psycodict computes it the same way everywhere; a name the metadata stores may not, because it is what the relation is called.

psycodict.validation.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.validation.identifier_bytes(name)

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

psycodict.validation.utf8_prefix(name, max_bytes)

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

psycodict.validation.catalog_identifier(name)

The spelling the catalog holds name under.

PostgreSQL truncates an over-long identifier as it parses the statement, so a relation created as <57 bytes>_counts is in pg_class under the first 63 bytes of that name. SQL keeps reaching it by the long spelling – the same truncation happens every time, which is why physical_table_name() concatenates rather than shortens – and a comparison the server makes is fine too, since pg_class.relname is of type name and a bound value is coerced to it.

What is not fine is comparing the long spelling to a catalog name in Python, where nothing truncates anything. Put a name through here first.

psycodict.validation.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.validation.physical_table_name(logical_name, component='search', lifecycle='')

The name of one relation of a search table’s family.

INPUT:

• logical_name – the search table’s name, as meta_tables records it

• component – "search", "counts" or "stats"

• lifecycle – "" for the live relation, or the suffix of a copy psycodict swaps through: "_tmp", "_old3"

OUTPUT:

The relation’s name, as every path must compute it.

Unlike derived_identifier(), this does not shorten a name that does not fit, and deliberately so. PostgreSQL truncates an over-long identifier consistently – a legacy table whose counts table was created as 67 bytes has it in the catalog under the first 63, and a reference by the 67-byte name still reaches it – so concatenation is what addresses the relations an existing database already has. Shortening them here would make psycodict look for relations that do not exist.

What truncation does break is distinctness: <63 bytes>_old1 and <63 bytes>_old2 are the same relation. So a relation psycodict is about to create goes through check_new_table_name() first, which refuses the name rather than letting two of them become one. A name within MAX_SEARCH_TABLE_NAME_LENGTH – which every table created since that limit is – can never reach either case.

psycodict.validation.check_new_table_name(name, kind='Table')

Refuse a relation psycodict is about to create under a name PostgreSQL would truncate.

Only reachable for a table whose name predates MAX_SEARCH_TABLE_NAME_LENGTH; the point is that it stops rather than creating a second relation that turns out to be the first one.

psycodict.validation.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.validation.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.validation.search_table_family(name)

The relations a search table owns: itself and its companions.

The spellings physical_table_name() produces, which are the ones psycodict puts into DDL. For the spellings the catalog holds them under, put each through catalog_identifier().

psycodict.validation.validate_search_table_registry(names, relations)

Check the recorded search-table names as a set, against what the database actually has.

INPUT:

• names – the names recorded in meta_tables

• relations – the names of the relations in psycodict’s schema

OUTPUT:

names, unchanged.

Validating one name at a time is not enough: what makes a name a search table is that it owns a whole family of relations – itself, its counts table and its stats table – and that nothing else owns any of them. A row naming foo_counts when foo is a search table would otherwise put foo’s counts table into the registry as a table in its own right, and every write to one would go to the other.

That is a rule about ownership rather than about spelling, which is why a name ending in _stats is not refused out of hand: the LMFDB has a search table called hgcwa_per_group_stats, and it owns hgcwa_per_group_stats, hgcwa_per_group_stats_counts and hgcwa_per_group_stats_stats – a complete family that overlaps nothing.

relations holds catalog spellings, so the family is put through catalog_identifier() before being looked up in it. A legacy table whose name leaves fewer than seven bytes for _counts has that relation in the catalog under a truncated name, and asking for the untruncated one here would report a table the database plainly has as missing – which refresh_tables runs before building anything, so it would refuse to connect at all.

psycodict.validation.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.validation.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.validation.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.validation.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.validation.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.validation.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.validation.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.