ll.vsql – Half an ORM

Normally an object-relational mapper consist of:

  1. An infrastructure for specifying which records to fetch from the database. This can be as simple as specifying a primary key value, or as complex as a complete query language.

  2. A way of mapping those records to objects and fields to attributes.

vSQL provides the first part only. vSQL does this by using a subset of UL4 expressions retargeted for generating SQL expressions used in SQL queries. So the end result of using vSQL always is a SQL query that you can execute normally.

Currently only Oracle is supported.

The purpose of vSQL is not to abstract away differences between the relational database and the object oriented world or to shield the developer from having to deal with SQL or from handling the result of database queries.

Instead vSQL provides a safe query language that can be used to provided a publicly accessible interface to database content. This eliminates the risky parts of query construction, effectively preventing SQL injection attacks, while offering the expressive power of an ORM without the overhead.

Example

To create an SQL query via vSQL expressions in the simplest form we’re using the class Query like this:

from ll import vsql
q = vsql.Query()

# Define the expression we want
q.select_vsql("('foo'.upper() + 'bar'.lower())[:3]")
q.select_vsql("now() + years(3)")

# Output the SQL query
print(q.sqlsource())

this outputs:

select
        vsqlimpl_pkg.slice_str((upper('foo') || lower('bar')), null, 3) /* ('foo'.upper() + 'bar'.lower())[:3] */,
        vsqlimpl_pkg.add_datetime_months(sysdate, (12 * 3)) /* now() + years(3) */
from
        dual

Selecting from tables

If you want to select records from a table, the Query object needs to know about the structure of the table, and needs a variable that can be used to refer to records of that table. If the table looks like this:

create table person
(
        per_id integer,
        per_firstname varchar2(100),
        per_lastname varchar2(100),
        per_birthday date
);

the table definition for vSQL can be defined like this:

person_table = vsql.Group("person")
person_table.add_field("id", vsql.DataType.INT, "{a}.per_id")
person_table.add_field("firstname", vsql.DataType.STR, "{a}.per_firstname")
person_table.add_field("lastname", vsql.DataType.STR, "{a}.per_lastname")
person_table.add_field("birthday", vsql.DataType.DATE, "{a}.per_birthday")

Selecting from this table then works like this:

from ll import vsql

person_table = vsql.Group("person")
person_table.add_field("id", vsql.DataType.INT, "{a}.per_id")
person_table.add_field("firstname", vsql.DataType.STR, "{a}.per_firstname")
person_table.add_field("lastname", vsql.DataType.STR, "{a}.per_lastname")
person_table.add_field("birthday", vsql.DataType.DATE, "{a}.per_birthday")

q = vsql.Query(
        p=vsql.Field("p", vsql.DataType.INT, refgroup=person_table)
)

# We want to force the query to select from `person`,
# even if we don't select any fields
q.from_vsql("p")

# Specify which fields we want returned
q.select_vsql("p.firstname + ' ' + p.lastname")
q.select_vsql("p.birthday")

# Return anly people born 1990 or later
q.where_vsql("p.birthday > @(1990-01-01)")

# Youngest first
q.orderby_vsql("p.birthday", "desc")

# Output the SQL query
print(q.sqlsource())

this outputs:

select
        ((t1.per_firstname /* p.firstname */ || ' ') || t1.per_lastname /* p.lastname */) /* p.firstname + ' ' + p.lastname */,
        t1.per_birthday /* p.birthday */
from
        person t1 /* p */
where
        decode(vsqlimpl_pkg.cmp_datetime_datetime(t1.per_birthday /* p.birthday */, to_date('1990-01-01', 'YYYY-MM-DD')), 1, 1, null, null, 0) = 1 /* p.birthday > @(1990-01-01) */
order by
        t1.per_birthday /* p.birthday */ desc

Using foreign key references

When you have foreign key fields that reference other tables, you can use them too. When we assume that our table person looks like this:

create table person
(
        per_id integer,
        per_firstname varchar2(100),
        per_lastname varchar2(100),
        per_birthday date,
        com_id integer
);

with the new field com_id referencing the table company that looks like this:

create table company
(
        com_id integer,
        com_name varchar2(100)
);

then we can define the table structure and query connected fields, as the following code demonstrates:

from ll import vsql

company_table = vsql.Group("company")
company_table.add_field("id", vsql.DataType.INT, "{a}.com_id")
company_table.add_field("name", vsql.DataType.STR, "{a}.com_name")

person_table = vsql.Group("person")
person_table.add_field("id", vsql.DataType.INT, "{a}.per_id")
person_table.add_field("firstname", vsql.DataType.STR, "{a}.per_firstname")
person_table.add_field("lastname", vsql.DataType.STR, "{a}.per_lastname")
person_table.add_field("birthday", vsql.DataType.DATE, "{a}.per_birthday")
person_table.add_field("company", vsql.DataType.INT, "{a}.com_id", "{m}.com_id = {d}.com_id", company_table)

q = vsql.Query(
        p=vsql.Field("p", vsql.DataType.INT, refgroup=person_table)
)

# We want to force the query to select from `person`,
# even if we don't select any fields
q.from_vsql("p")

# Specify which fields we want returned
q.select_vsql("p.firstname + ' ' + p.lastname")
q.select_vsql("p.birthday")
q.select_vsql("p.company.name")

# Return anly people born 1990 or later
q.where_vsql("p.birthday > @(1990-01-01)")

# Youngest first
q.orderby_vsql("p.birthday", "desc")

# Output the SQL query
print(q.sqlsource())

This outputs:

select
        ((t1.per_firstname /* p.firstname */ || ' ') || t1.per_lastname /* p.lastname */) /* p.firstname + ' ' + p.lastname */,
        t1.per_birthday /* p.birthday */,
        t2.com_name /* p.company.name */
from
        person t1 /* p */,
        company t2 /* p.company */
where
        t1.com_id = t2.com_id /* p.company */ and
        decode(vsqlimpl_pkg.cmp_datetime_datetime(t1.per_birthday /* p.birthday */, to_date('1990-01-01', 'YYYY-MM-DD')), 1, 1, null, null, 0) = 1 /* p.birthday > @(1990-01-01) */
order by
        t1.per_birthday /* p.birthday */ desc

vSQL standard library

Many vSQL operations can not be converted to simple SQL expressions. For these vSQL uses an Oracle package vsqlimpl_pkg that contains the “vSQL standard library”. This packages is available from Github at https://github.com/LivingLogic/LivingLogic.Oracle.ul4

Module content

class ll.vsql.sqlliteral[source]

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) → Template[source]

Return an SQL expression for the Python value value.

ll.vsql.add_comment(sqlsource: Template, comment: str | None) → Template[source]

Append the comment comment to the template sqlsource.

If sqlsource already ends in the comment, it is not added again.

I.e. add_comment(t"bar", "foo") returns t"bar /* foo */".

ll.vsql.to_tstring(obj: str | Template | None) → Template | None[source]

Convert obj to a flattened t-string.

None is returned unchanged, a str is wrapped in a templatelib.Template, and an existing t-string is flattened via orasql.flatten_tstring().

ll.vsql.tstring_replace(obj: str | Template, search: str, replace: str)[source]

Replace all occurrences of search with replace in the literal string parts of the t-string obj.

Only the literal string parts are modified; interpolations are kept unchanged. A new templatelib.Template with the replaced parts is returned.

I.e. tstring_replace(t"a{b}a", "a", "x") returns t"x{b}x".

class ll.vsql.Repr[source]

Bases: object

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

class ll.vsql.Aggregate[source]

Bases: Enum

Aggregation methods.

class ll.vsql.DataType[source]

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[source]

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[source]

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[source]

Bases: Exception

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

class ll.vsql.Error[source]

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[source]

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 | Template | None = None, joinsql: str | Template | None = None, refgroup: Group | None = None)[source]

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[source]

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 | Template, joinsql: str | Template | None = None, refgroup: Group | None = None) → None[source]

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

class ll.vsql.Query[source]

Bases: Repr

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

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

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[source]

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[source]

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 | Template, comment=None, alias=None) → SQLSelectExpr[source]

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[source]

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 | Template, comment: str | None, alias: str | Template | None = None) → SQLAggregatedSelectExpr[source]

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[source]

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[source]

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 | Template, comment: str | None = None) → SQLWhereExpr[source]

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[source]

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 | Template, comment: str | None = None) → SQLGroupByExpr[source]

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 | Template, comment: str | None = None) → VSQLOrderByExpr[source]

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[source]

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[source]

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[source]

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[source]

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[source]

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[source]

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)[source]

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[source]

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[source]

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][source]

Return all FieldRefAST objects in this AST.

This is a generator.

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

Return this class and all subclasses.

This is a generator.

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

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

This is a generator.

classmethod add_rules(spectemplate: Template, source: Template) → None[source]

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.

Both arguments are template strings (t-strings). The arguments spectemplate and source have the following meaning:

spectemplate

spectemplate specifies the allowed combinations of operand types and the resulting type. Only the interpolations are used; the literal text is ignored (but can be used to make the rule clearer). Each interpolation is one of the following:

Datatypes

A datatype is given by interpolating the appropriate DataType member directly (e.g. {dt.INT} or {dt.STR}, where dt is an alias for DataType). A string like {'T1'} refers to another type in the spec.

Union types

A set, list or tuple of DataType members (e.g. {(dt.BOOL, dt.INT)}) specifies a union type, i.e. any of the types in the collection is allowed. Some predefined unions are available as module level variables (e.g. {INTLIKE} or {NUMBERLIKE}).

Names

The name of a function, method or attribute is given as a (lowercase) string constant (e.g. {'year'}).

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

Examples:

t"{dt.INT} <- {dt.BOOL} + {dt.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 t"{dt.INT} {dt.BOOL} {dt.BOOL}" or even as t"{dt.INT}?????{dt.BOOL}#$%^&*{dt.BOOL}".

t"{dt.INT} <- {(dt.BOOL, dt.INT)} + {(dt.BOOL, dt.INT)}"

This is equivalent to the four rules: t"{dt.INT} <- {dt.BOOL} + {dt.BOOL}", t"{dt.INT} <- {dt.INT} + {dt.BOOL}", t"{dt.INT} <- {dt.BOOL} + {dt.INT}" and t"{dt.INT} <- {dt.INT} + {dt.INT}".

t"{'T1'} <- {(dt.BOOL, dt.INT)} + {'T1'}"

This is equivalent to the two rules t"{dt.BOOL} <- {dt.BOOL} + {dt.BOOL}" and t"{dt.INT} <- {dt.INT} + {dt.INT}".

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

AddAST.add_rules(
        t"{dt.INT} <- {(dt.BOOL, dt.INT)} + {(dt.BOOL, dt.INT)}",
        t"..."
)
AddAST.add_rules(
        t"{dt.NUMBER} <- {(dt.BOOL, dt.INT, dt.NUMBER)} + {(dt.BOOL, dt.INT, dt.NUMBER)}",
        t"..."
)

will register the rule t"{dt.INT} <- {dt.BOOL} + {dt.BOOL}", but not t"{dt.NUMBER} <- {dt.BOOL} + {dt.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 interpolations 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(
        t"{dt.INT} <- {dt.DATE}.{'year'}",
        t"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(
        t"{dt.BOOL} <- {(dt.STR, dt.CLOB)} == {(dt.STR, dt.CLOB)}",
        t"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[source]

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[source]

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[source]

Return the UL4/vSQL source code of the AST.

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

Return the child AST nodes of this node.

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

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

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

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[source]

Bases: AST

Base class for all vSQL expressions that are constants.

class ll.vsql.NoneAST[source]

Bases: ConstAST

The constant None.

class ll.vsql.BoolAST[source]

Bases: _ConstWithValueAST

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

class ll.vsql.IntAST[source]

Bases: _ConstWithValueAST

An integer constant.

class ll.vsql.NumberAST[source]

Bases: _ConstWithValueAST

A number constant (containing a decimal point).

class ll.vsql.StrAST[source]

Bases: _ConstWithValueAST

A string constant.

class ll.vsql.CLOBAST[source]

Bases: _ConstWithValueAST

A CLOB constant.

This normally will not be created by the Python implementation

class ll.vsql.ColorAST[source]

Bases: _ConstWithValueAST

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

class ll.vsql.DateAST[source]

Bases: _ConstWithValueAST

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

class ll.vsql.DateTimeAST[source]

Bases: _ConstWithValueAST

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

class ll.vsql.ListAST[source]

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[source]

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[source]

Bases: AST

Reference to a field defined in the database.

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

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[source]

Bases: AST

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

class ll.vsql.EQAST[source]

Bases: BinaryAST

Equality comparison (A == B).

class ll.vsql.NEAST[source]

Bases: BinaryAST

Inequality comparison (A != B).

class ll.vsql.LTAST[source]

Bases: BinaryAST

Less-than comparison (A < B).

class ll.vsql.LEAST[source]

Bases: BinaryAST

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

class ll.vsql.GTAST[source]

Bases: BinaryAST

Greater-than comparison (A > B).

class ll.vsql.GEAST[source]

Bases: BinaryAST

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

class ll.vsql.AddAST[source]

Bases: BinaryAST

Addition (A + B).

class ll.vsql.SubAST[source]

Bases: BinaryAST

Subtraction (A - B).

class ll.vsql.MulAST[source]

Bases: BinaryAST

Multiplication (A * B).

class ll.vsql.TrueDivAST[source]

Bases: BinaryAST

True division (A / B).

class ll.vsql.FloorDivAST[source]

Bases: BinaryAST

Floor division (A // B).

class ll.vsql.ModAST[source]

Bases: BinaryAST

Modulo operator (A % B).

class ll.vsql.ShiftLeftAST[source]

Bases: BinaryAST

Left shift operator (A << B).

class ll.vsql.ShiftRightAST[source]

Bases: BinaryAST

Right shift operator (A >> B).

class ll.vsql.AndAST[source]

Bases: BinaryAST

Logical “and” (A and B).

class ll.vsql.OrAST[source]

Bases: BinaryAST

Logical “or” (A or B).

class ll.vsql.ContainsAST[source]

Bases: BinaryAST

Containment test (A in B).

class ll.vsql.NotContainsAST[source]

Bases: BinaryAST

Inverted containment test (A not in B).

class ll.vsql.IsAST[source]

Bases: BinaryAST

Identity test (A is B).

class ll.vsql.IsNotAST[source]

Bases: BinaryAST

Inverted identity test (A is not B).

class ll.vsql.ItemAST[source]

Bases: BinaryAST

Item access operator (A[B]).

class ll.vsql.BitAndAST[source]

Bases: BinaryAST

Bitwise “and” (A & B).

class ll.vsql.BitOrAST[source]

Bases: BinaryAST

Bitwise “or” (A | B).

class ll.vsql.BitXOrAST[source]

Bases: BinaryAST

Bitwise “exclusive or” (A ^ B).

class ll.vsql.UnaryAST[source]

Bases: AST

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

class ll.vsql.NotAST[source]

Bases: UnaryAST

Logical negation (not A).

class ll.vsql.NegAST[source]

Bases: UnaryAST

Arithmetic negation (-A).

class ll.vsql.BitNotAST[source]

Bases: UnaryAST

Bitwise “not” (~A).

class ll.vsql.IfAST[source]

Bases: AST

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

class ll.vsql.SliceAST[source]

Bases: AST

Slice operator (A[B:C]).

class ll.vsql.AttrAST[source]

Bases: AST

Attribute access (A.name).

class ll.vsql.FuncAST[source]

Bases: AST

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

class ll.vsql.MethAST[source]

Bases: AST

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

ll.vsql.dt

alias of DataType

class ll.vsql.JavaSource[source]

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][source]

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

save() → None[source]

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][source]

Return an iterator over all JavaSource objects for vSQL AST nodes 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[source]

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[source]

Return the SQL statement for creating the table VSQLRULE.

ll.vsql.oracle_sql_procedure() → str[source]

Return the SQL statement for creating the procedure VSQLGRAMMAR_MAKE.

ll.vsql.oracle_sql_index() → str[source]

Return the SQL statement for creating the index VSQLRULE_I1.

ll.vsql.oracle_sql_tablecomment() → str[source]

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

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

Recreate the vSQL syntax rules in the database.

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