ll.vsql – Half an ORM

class ll.vsql.sqlliteral

Bases: str

Internal marker class that can be used to specify that its value should be treated as literal SQL.

ll.vsql.sql(value: Any) → str

Return an SQL expression for the Python value value.

ll.vsql.comment(s: str) → str

Return an SQL comment with the content s.

I.e. comment("foo") returns "/* foo */".

class ll.vsql.Repr

Bases: object

Base class that provides functionality for implementing __repr__() and _repr_pretty_() (used by IPython).

class ll.vsql.Aggregate

Bases: Enum

Aggregation methods.

class ll.vsql.DataType

Bases: Enum

The datatypes supported in vSQL expressions.

Possible values are:

• NULL

• BOOL

• INT

• NUMBER

• STR

• CLOB

• COLOR

• GEO

• DATE

• DATETIME

• DATEDELTA

• DATETIMEDELTA

• MONTHDELTA

• NULLLIST

• INTLIST

• NUMBERLIST

• STRLIST

• CLOBLIST

• DATELIST

• DATETIMELIST

• NULLSET

• INTSET

• NUMBERSET

• STRSET

• DATESET

• DATETIMESET

classmethod compatible_to(given: DataType, required: DataType) → Error | None

Check whether the type given is compatible to required.

If required is None every given type is accepted. Otherwise the types must be compatible (for example DataType.INT is compatible to DataType.NUMBER, but not the other way around). Every type is compatible to itself.

If given is not compatible to required the appropriate error value is returned, otherwise None is returned.

class ll.vsql.NodeType

Bases: Enum

The different types of vSQL abstract syntax tree nodes.

This corresponds to the different subclasses of AST.

Possible values are:

• FIELD

• CONST_NONE

• CONST_BOOL

• CONST_INT

• CONST_NUMBER

• CONST_STR

• CONST_CLOB

• CONST_DATE

• CONST_DATETIME

• CONST_TIMESTAMP

• CONST_COLOR

• LIST

• SET

• CMP_EQ

• CMP_NE

• CMP_LT

• CMP_LE

• CMP_GT

• CMP_GE

• BINOP_ADD

• BINOP_MUL

• BINOP_SUB

• BINOP_FLOORDIV

• BINOP_TRUEDIV

• BINOP_MOD

• BINOP_AND

• BINOP_OR

• BINOP_CONTAINS

• BINOP_NOTCONTAINS

• BINOP_IS

• BINOP_ISNOT

• BINOP_ITEM

• BINOP_SHIFTLEFT

• BINOP_SHIFTRIGHT

• BINOP_BITAND

• BINOP_BITOR

• BINOP_BITXOR

• TERNOP_SLICE

• UNOP_NOT

• UNOP_NEG

• UNOP_BITNOT

• TERNOP_IF

• ATTR

• FUNC

• METH

exception ll.vsql.VSQLError

Bases: Exception

Base class of exceptions that can happend when compiling vSQL expressions.

class ll.vsql.Error

Bases: str, Enum

The types of errors that can lead to invalid vSQL AST nodes.

Note that some of those can not be produced by the Python implementation.

Possible values are:

• SUBNODEERROR

• NODETYPE

• ARITY

• SUBNODETYPES

• FIELD

• CONST_BOOL

• CONST_INT

• CONST_NUMBER

• CONST_DATE

• CONST_DATETIME

• CONST_TIMESTAMP

• CONST_COLOR

• NAME

• LISTUNSUPPORTEDTYPES

• SETMIXEDTYPES

• SETUNSUPPORTEDTYPES

• DATATYPE_NULL

• DATATYPE_BOOL

• DATATYPE_INT

• DATATYPE_NUMBER

• DATATYPE_STR

• DATATYPE_CLOB

• DATATYPE_COLOR

• DATATYPE_DATE

• DATATYPE_DATETIME

• DATATYPE_DATEDELTA

• DATATYPE_DATETIMEDELTA

• DATATYPE_MONTHDELTA

• DATATYPE_NULLLIST

• DATATYPE_INTLIST

• DATATYPE_NUMBERLIST

• DATATYPE_STRLIST

• DATATYPE_CLOBLIST

• DATATYPE_DATELIST

• DATATYPE_DATETIMELIST

• DATATYPE_NULLSET

• DATATYPE_INTSET

• DATATYPE_NUMBERSET

• DATATYPE_STRSET

• DATATYPE_DATESET

• DATATYPE_DATETIMESET

class ll.vsql.Field

Bases: Repr

A Field object describes a database field.

This field is either in a database table or view or a global variable.

As a table or view field it belongs to a Group object.

__init__(identifier: str | None = None, datatype: DataType = <ll.vsql.DataType.NULL: 'null'>, fieldsql: str | None = None, joinsql: str | None = None, refgroup: Group | None = None)

Create a Field instance.

Argument are:

identifier

The UL4 identifier of the field

datatype

The vSQL datatype of the field

fieldsql

The SQL expression for that fields. This should include {a} as a placeholder for the table alias.

joinsql

If this field is a foreign key to another table, joinsql is the join condition. This should include {m} and {d} placeholder for the table aliases of the master table (i.e. the one where this field is in) and the detail table (i.e. the one that will be joined).

refgroup

The Group object that represents the target table.

class ll.vsql.Group

Bases: Repr

A Group object describes a group of database fields.

These fields are part of a database table or view and are instances of Field.

add_field(identifier: str, datatype: DataType, fieldsql: str, joinsql: str | None = None, refgroup: Group | None = None) → None

Create a Field object from the arguments and add it to the fields of the group.

class ll.vsql.Query

Bases: Repr

A Query object can be used to build an SQL query using vSQL expressions.

__init__(comment: str | None = None, **vars: Field)

Create a new empty Query object.

Arguments are:

commentstr or None

A comment that will be included in the generated SQL.

varsField

These are the top level variables that will be availabe for vSQL expressions added to this query. The argument name is the name of the variable. The argument value is a Field object that describes this variable.

In most cases this Field object is a foreign key to another table, so it has a joinsql and a refgroup.

from_vsql(identifier: str) → str | None

Registers the field identifier identifier as a table to select from.

identifier must belong to one of the fields passed to the constructor and it should reference a table.

from_vsql() will then make sure that this referenced table will be added to the “from” list, even if it is never referenced explicitely in any of the “from” and “where” clauses.

select_vsql(expr: str, comment: str | None = None, alias: str | None = None) → VSQLSelectExpr

Add the vSQL expression expr to the list of expression to select.

comment will be added as a comment after the column expression.

alias can be used to give the expression a column alias.

This compiles expr and adds the resulting SQL. To add an SQL expression directly use select_sql() instead.

select_sql(expr: str, comment=None, alias=None) → SQLSelectExpr

Add the SQL expression expr to the list of expression to select.

comment can be used to give the column a comment in the select list.

alias can be used to give the expression a column alias.

Note that that adds expr directly as “raw” SQL. To add a vSQL expression use select_vsql() instead.

aggregate_vsql(expr: str, comment: str | None = None, alias: str | None = None) → VSQLAggregatedSelectExpr

Add the aggregating vSQL expression expr to the list of expression to select.

comment will be added as a comment after the column expression.

alias can be used to give the expression a column alias.

Note that it’s not possible to mix aggregated and non-aggregated fields. For a vSQL expression to be an aggregating expression it must either be the function call count() (without arguments), or call one of the functions group(), min(), max()` or sum() with one argument. These function do the following:

count()

Return number of records in this group;

min(expr)

Return the minimum value of the expressions expr for all records in this group;

max(expr)

Return the maximum value of the expressions expr for all records in this group;

sum(expr)

Return the sum of the values for the expressions expr for all records in this group.

group(expr)

Use the grouping value expr (which is the same for all records in this group).

aggregate_sql(expr: str, comment: str | None, alias: str | None = None) → SQLAggregatedSelectExpr

Add the aggregating SQL expression expr to the list of expression to select.

comment will be added as a comment after the column expression.

alias can be used to give the expression a column alias.

Note that it’s not possible to mix aggregated and non-aggregated fields.

Make sure that expr is an aggregating expression like count(*) or max(tbl.value)

from_sql(tablename, comment=None, alias=None) → SQLFromExpr

Add a table to the list of tables to select from.

This adds the table in “raw” SQL form.

There’s no need to add to the “from” list in vSQL form, since this is done automatically in select_vsql(), where_vsql() or orderby_vsql().

where_vsql(expr: str) → VSQLWhereExpr

Add vSQL condition expr to the where clause.

Note that this compiles expr and add the resulting SQL. To add an SQL expression directly use where_sql() instead.

If expr doesn’t have the datatype BOOL it will be automatically converted to BOOL.

where_sql(expr: str, comment: str | None = None) → SQLWhereExpr

Add vSQL condition expr to the where clause.

Note that that adds expr directly as “raw” SQL. To add a vSQL expression use where_vsql() instead.

groupby_vsql(expr: str, comment: str | None = None) → VSQLGroupByExpr

Add the grouping vSQL expression expr to the list of expression to group by.

comment will be added as a comment after the column expression.

groupby_sql(expr: str, comment: str | None = None) → SQLGroupByExpr

Add the grouping SQL expression expr to the list of expression to group by.

comment will be added as a comment after the column expression.

orderby_vsql(expr: str, comment: str | None = None) → VSQLOrderByExpr

Add the “order by” vSQL expression expr to this query.

“order by” specifications will be output in the query in the order they have been added.

The format must be a vSQL expression optionally followed by asc or desc optionally followed by nulls first or nulls last

asc sorts in ascending order and desc sorts descending order. If neither is specified neither asc nor desc will be added to the query (which is equivalent to asc).

nulls first outputs null values first, nulls last outputs them last.

Example:

>>> from ll import vsql
>>> q = vsql.Query("Example query", user=la.User.vsqlfield())
>>> q.select_vsql("user.email")
>>> q.orderby_vsql("user.firstname asc nulls first")
>>> q.orderby_vsql("user.surname desc nulls last")
>>> print(q.sqlsource())
/* Example query */
select
        t1.ide_account /* user.email */
from
        identity t1 /* user */
where
        livingapi_pkg.global_user = t1.ide_id(+) /* user */
order by
        t1.ide_firstname /* user.firstname */ asc nulls first,
        t1.ide_surname /* user.surname */ desc nulls last

orderby_sql(expr: str, comment: str | None = None) → SQLOrderByExpr

Add the “order by” SQL expression expr to this query.

“order by” specifications will be output in the query in the order they have been added.

Note that that adds expr directly as “raw” SQL. To add a vSQL expression use select_vsql() instead.

The format must be an SQL expression optionally followed by asc or desc optionally followed by nulls first or nulls last

asc sorts in ascending order and desc sorts descending order. If neither is specified neither asc nor desc will be added to the query (which is equivalent to asc).

nulls first outputs null values first, nulls last outputs them last.

offset(offset: int | None) → None

Use offset as the offset value.

This offset specifies how mnay records to skip before returing the first one. The default 0 or None doesn’t skip any records.

limit(limit: int | None) → None

Use limit to limit the number of records returned.

After limit records no further records will be returned even if there are more than limit records that match the filter condition.

sqlsource(indent='\t') → str

Return the SQL source code for this query.

For example:

>>> from ll import vsql
>>> print(vsql.Query().select_vsql("now()").sqlsource()))
select
        sysdate /* now() */
from
        dual

class ll.vsql.Rule

Bases: Repr

Rule is used to store a type specific vSQL grammar rule.

I.e. one rule object stores the information that:

• there’s and addition operator;

• that adds two INT values;

• with a result of type INT;

• and the SQL code to generate for that operation.

For more information see AST.add_rules().

class ll.vsql.AST

Bases: Repr

Base class of all vSQL abstract syntax tree node types.

The following class attribute is used:

title: str

Contains a human readable name for the AST type.

Instance attributes are:

nodetype: NodeType

Type of the node. There’s a one-to-one correspondence between AST subclasses and NodeType values (except for intermediate classes like BinaryAST)

nodevalue: str

The node value is an instance attribute that represents a string that isn’t represented by any child node. E.g. the values of constants or the names of functions, methods and attributes. Will be overwritten by properties in subclasses.

datatype: DataType | None

The datatype is an instance attribute that represents the datatype of the expression.

If the datatype can’t be determined because of errors datatype will be None.

__init__(*content: AST | str)

Create a new AST node from its content.

content is a mix of str objects containing the UL4 source and child AST nodes.

Normally the user doesn’t call __init__() directly, but uses make() to create the appropriate AST node from child nodes.

For example a function call to the function date could be created like this:

FuncAST(
        "date",
        "(",
        IntAST("2000", 2000),
        ", ",
        IntAST("2", 2),
        ", ",
        IntAST("29", 29),
        ")",
)

but more conveniently like this:

FuncAST.make(
        "date",
        ConstAST.make(2000),
        ConstAST.make(2),
        ConstAST.make(29),
)

abstractmethod classmethod make() → AST

Create an instance of this AST class from its child AST nodes.

This method is abstract and is overwritten in each subclass.

This is a very low level way of creating vSQL expressions.

For example a vSQL expression for "foo".lower() + "bar".upper() can be constructed like this:

vsql.AddAST.make(
        vsql.MethAST.make(
                vsql.StrAST.make("foo"),
                "lower",
        ),
        vsql.MethAST.make(
                vsql.StrAST.make("bar"),
                "upper",
        ),
)

classmethod fromsource(source: str, **vars: Field) → AST

Create a vSQL expression from it source code.

For example "foo".lower() + "bar".upper() can be compiled like this:

vsql.AST.fromsource("'foo'.lower() + 'bar'.upper()")

vars contains the “root” variables that can be referenced in the vSQL expression.

fieldrefs() → Generator[FieldRefAST, None, None]

Return all FieldRefAST objects in this AST.

This is a generator.

classmethod all_types() → Generator[Type[AST], None, None]

Return this class and all subclasses.

This is a generator.

classmethod all_rules() → Generator[Rule, None, None]

Return all grammar rules of this class and all its subclasses.

This is a generator.

classmethod add_rules(spec: str, source: str) → None

Register new syntax rules for this AST class.

These rules are used for type checking and type inference and for converting the vSQL AST into SQL source code.

The arguments spec and source have the following meaning:

spec

spec specifies the allowed combinations of operand types and the resulting type. It consists of the following:

Upper case words

These specify types (e.g. INT or STR; for a list of allowed values see DataType). Also allowed are:

• T followed by an integer, this is used to refer to another type in the spec and

• a combination of several types joined with _. This is a union type, i.e. any of the types in the combination are allowed.

Lower case words

They specify the names of functions, methods or attributes

Any sequence of whitespace or other non-word characters

They are ignored, but can be used to separate types and names and to make the rule clearer.

The first word in the rule always is the result type.

Examples:

INT <- BOOL + BOOL

Adding this rule to AddAST specifies that the types BOOL and BOOL can be added and the resulting type is INT. Note that using + is only syntactic sugar. This rule could also have been written as INT BOOL BOOL or even as INT?????BOOL#$%^&*BOOL.

INT <- BOOL_INT + BOOL_INT

This is equivalent to the four rules: INT <- BOOL + BOOL, INT <- INT + BOOL, INT <- BOOL + INT and INT <- INT + INT.

T1 <- BOOL_INT + T1

This is equivalent to the two rules BOOL <- BOOL + BOOL and INT <- INT + INT.

Note that each rule will only be registered once. So the following code:

AddAST.add_rules(
        "INT <- BOOL_INT + BOOL_INT",
        "..."
)
AddAST.add_rules(
        "NUMBER <- BOOL_INT_NUMBER + BOOL_INT_NUMBER",
        "..."
)

will register the rule INT <- BOOL + BOOL, but not NUMBER <- BOOL + BOOL since the first call already registered a rule for the signature BOOL BOOL.

source

source specifies the SQL source that will be generated for this expression. Two types of placeholders are supported: {s1} means “embed the source code of the first operand in this spot” (and {s2} etc. accordingly) and {t1} embeds the type name (in lowercase) in this spot (and {t2} etc. accordingly).

Example 1:

AttrAST.add_rules(
        f"INT <- DATE.year",
        "extract(year from {s1})"
)

This specifies that a DATE value has an attribute year and that for such a value value the generated SQL source code will be:

extract(year from value)

Example 2:

EQAST.add_rules(
        f"BOOL <- STR_CLOB == STR_CLOB",
        "vsqlimpl_pkg.eq_{t1}_{t2}({s1}, {s2})"
)

This registers four rules for equality comparison between STR and CLOB objects. The generated SQL source code for comparisons between STR and STR will be

vsqlimpl_pkg.eq_str_str(value1, value2)

and for CLOB/CLOB comparison it will be

vsqlimpl_pkg.eq_clob_clob(value1, value2)

validate() → None

Validate the content of this AST node.

If this node turns out to be invalid validate() will set the attribute datatype to None and error to the appropriate Error value.

If this node turns out to be valid, validate() will set the attribute error to None and datatype to the resulting data type of this node.

check_valid(context: str | None = None) → None

Makes sure that self is valid.

If self is invalid an appropriate exception will be raised.

context should describe the context in which the expression is used. E.g. "select" when used in Query.select_vsql() or "where" when used in Query.where_vsql().

source() → str

Return the UL4/vSQL source code of the AST.

children() → Generator[AST, None, None]

Return the child AST nodes of this node.

walknodes() → Generator[AST, None, None]

Return the all child AST nodes of this node (recursively).

walkpaths() → Generator[list[AST], None, None]

Return an iterator for traversing the syntax tree rooted at self.

Items produced by the iterator paths are lists containing the path from the root AST object to self.

Note that the iterator will always produce the same list object that will be changed during the iteration. If you want to keep the value produced during the iteration, you have to make copies.

class ll.vsql.ConstAST

Bases: AST

Base class for all vSQL expressions that are constants.

class ll.vsql.NoneAST

Bases: ConstAST

The constant None.

class ll.vsql.BoolAST

Bases: _ConstWithValueAST

A boolean constant (i.e. True or False).

class ll.vsql.IntAST

Bases: _ConstWithValueAST

An integer constant.

class ll.vsql.NumberAST

Bases: _ConstWithValueAST

A number constant (containing a decimal point).

class ll.vsql.StrAST

Bases: _ConstWithValueAST

A string constant.

class ll.vsql.CLOBAST

Bases: _ConstWithValueAST

A CLOB constant.

This normally will not be created by the Python implementation

class ll.vsql.ColorAST

Bases: _ConstWithValueAST

A color constant (e.g. #fff).

class ll.vsql.DateAST

Bases: _ConstWithValueAST

A date constant (e.g. @(2000-02-29)).

class ll.vsql.DateTimeAST

Bases: _ConstWithValueAST

A datetime constant (e.g. @(2000-02-29T12:34:56)).

class ll.vsql.ListAST

Bases: _SeqAST

A list constant.

For this to work the list may only contain items of “compatible” types, i.e. types that con be converted to a common type without losing information.

class ll.vsql.SetAST

Bases: _SeqAST

A set constant.

For this to work the set may only contain items of “compatible” types, i.e. types that can be converted to a common type without losing information.

class ll.vsql.FieldRefAST

Bases: AST

Reference to a field defined in the database.

__init__(parent: FieldRefAST | None, identifier: str, field: Field | None, *content: AST | str)

Create a FieldRef object.

There are three possible scenarios with respect to identifier and field:

field is not None and field.identifier == identifier

In this case we have a valid Field that describes a real field.

field is not None and field.identifier != identifier and field.identifier == "*"

In this case field is the Field object for the generic typed request parameters. E.g. when the vSQL expression is params.str.foo then field references the Field for params.str.*, so field.identifier == "*" and identifier == "foo".

field is None

In this case the field is unknown.

class ll.vsql.BinaryAST

Bases: AST

Base class of all binary expressions (i.e. expressions with two operands).

class ll.vsql.EQAST

Bases: BinaryAST

Equality comparison (A == B).

class ll.vsql.NEAST

Bases: BinaryAST

Inequality comparison (A != B).

class ll.vsql.LTAST

Bases: BinaryAST

Less-than comparison (A < B).

class ll.vsql.LEAST

Bases: BinaryAST

Less-than or equal comparison (A <= B).

class ll.vsql.GTAST

Bases: BinaryAST

Greater-than comparison (A > B).

class ll.vsql.GEAST

Bases: BinaryAST

Greater-than-or equal comparison (A >= B).

class ll.vsql.AddAST

Bases: BinaryAST

Addition (A + B).

class ll.vsql.SubAST

Bases: BinaryAST

Subtraction (A - B).

class ll.vsql.MulAST

Bases: BinaryAST

Multiplication (A * B).

class ll.vsql.TrueDivAST

Bases: BinaryAST

True division (A / B).

class ll.vsql.FloorDivAST

Bases: BinaryAST

Floor division (A // B).

class ll.vsql.ModAST

Bases: BinaryAST

Modulo operator (A % B).

class ll.vsql.ShiftLeftAST

Bases: BinaryAST

Left shift operator (A << B).

class ll.vsql.ShiftRightAST

Bases: BinaryAST

Right shift operator (A >> B).

class ll.vsql.AndAST

Bases: BinaryAST

Logical “and” (A and B).

class ll.vsql.OrAST

Bases: BinaryAST

Logical “or” (A or B).

class ll.vsql.ContainsAST

Bases: BinaryAST

Containment test (A in B).

class ll.vsql.NotContainsAST

Bases: BinaryAST

Inverted containment test (A not in B).

class ll.vsql.IsAST

Bases: BinaryAST

Identity test (A is B).

class ll.vsql.IsNotAST

Bases: BinaryAST

Inverted identity test (A is not B).

class ll.vsql.ItemAST

Bases: BinaryAST

Item access operator (A[B]).

class ll.vsql.BitAndAST

Bases: BinaryAST

Bitwise “and” (A & B).

class ll.vsql.BitOrAST

Bases: BinaryAST

Bitwise “or” (A | B).

class ll.vsql.BitXOrAST

Bases: BinaryAST

Bitwise “exclusive or” (A ^ B).

class ll.vsql.UnaryAST

Bases: AST

Base class of all unary expressions (i.e. expressions with one operand).

class ll.vsql.NotAST

Bases: UnaryAST

Logical negation (not A).

class ll.vsql.NegAST

Bases: UnaryAST

Arithmetic negation (-A).

class ll.vsql.BitNotAST

Bases: UnaryAST

Bitwise “not” (~A).

class ll.vsql.IfAST

Bases: AST

Ternary “if”/”else” (A if COND else B).

class ll.vsql.SliceAST

Bases: AST

Slice operator (A[B:C]).

class ll.vsql.AttrAST

Bases: AST

Attribute access (A.name).

class ll.vsql.FuncAST

Bases: AST

Function call (name(A, ...)).

class ll.vsql.MethAST

Bases: AST

Method call (A.name(B, ...)).

class ll.vsql.JavaSource

Bases: object

A JavaSource object combines the source code of a Java class that implements a vSQL AST type with the Python class that implements that AST type.

It is used to update the vSQL syntax rules in the Java implemenation of vSQL.

new_lines() → Generator[str, None, None]

Return an iterator over the new Java source code lines that should replace the static initialization block inside the Java source file.

save() → None

Resave the Java source code incorporating the new vSQL type info from the Python AST class.

classmethod all_java_source_files(path: Path) → Generator[JavaSource, None, None]

Return an iterator over all JavaSource objects that can be found in the directory path. path should point to the directory containing the Java vSQL AST classes.

classmethod rewrite_all_java_source_files(path: Path, verbose: bool = False) → None

Rewrite all Java source code files implementing Java vSQL AST classes in the directory path. path should point to the directory containing the Java vSQL AST classes.

ll.vsql.oracle_sql_table() → str

Return the SQL statement for creating the table VSQLRULE.

ll.vsql.oracle_sql_procedure() → str

Return the SQL statement for creating the procedure VSQLGRAMMAR_MAKE.

ll.vsql.oracle_sql_index() → str

Return the SQL statement for creating the index VSQLRULE_I1.

ll.vsql.oracle_sql_tablecomment() → str

Return the SQL statement for creating a comment on the table VSQLRULE.

ll.vsql.recreate_oracle(connectstring: str, verbose: bool = False) → None

Recreate the vSQL syntax rules in the database.

This recreates the procedure VSQLGRAMMAR_MAKE and the table VSQLRULE and its content.