diff options
| -rw-r--r-- | doc/build/reference/sqlalchemy/expressions.rst | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/ext/compiler.py | 53 | ||||
| -rw-r--r-- | lib/sqlalchemy/schema.py | 77 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 108 | ||||
| -rw-r--r-- | test/ext/test_compiler.py | 30 |
5 files changed, 175 insertions, 101 deletions
diff --git a/doc/build/reference/sqlalchemy/expressions.rst b/doc/build/reference/sqlalchemy/expressions.rst index acafa8747..018616bca 100644 --- a/doc/build/reference/sqlalchemy/expressions.rst +++ b/doc/build/reference/sqlalchemy/expressions.rst @@ -146,6 +146,14 @@ Classes :members: where :show-inheritance: +.. autoclass:: FunctionElement + :members: + :show-inheritance: + +.. autoclass:: Function + :members: + :show-inheritance: + .. autoclass:: FromClause :members: :show-inheritance: diff --git a/lib/sqlalchemy/ext/compiler.py b/lib/sqlalchemy/ext/compiler.py index 05df8d2be..76896a352 100644 --- a/lib/sqlalchemy/ext/compiler.py +++ b/lib/sqlalchemy/ext/compiler.py @@ -16,10 +16,10 @@ subclasses and one or more callables defining its compilation:: def compile_mycolumn(element, compiler, **kw): return "[%s]" % element.name -Above, ``MyColumn`` extends :class:`~sqlalchemy.sql.expression.ColumnClause`, the -base expression element for column objects. The ``compiles`` decorator registers -itself with the ``MyColumn`` class so that it is invoked when the object -is compiled to a string:: +Above, ``MyColumn`` extends :class:`~sqlalchemy.sql.expression.ColumnClause`, +the base expression element for named column objects. The ``compiles`` +decorator registers itself with the ``MyColumn`` class so that it is invoked +when the object is compiled to a string:: from sqlalchemy import select @@ -30,8 +30,8 @@ Produces:: SELECT [x], [y] -Compilers can also be made dialect-specific. The appropriate compiler will be invoked -for the dialect in use:: +Compilers can also be made dialect-specific. The appropriate compiler will be +invoked for the dialect in use:: from sqlalchemy.schema import DDLElement @@ -51,11 +51,12 @@ for the dialect in use:: The second ``visit_alter_table`` will be invoked when any ``postgresql`` dialect is used. -The ``compiler`` argument is the :class:`~sqlalchemy.engine.base.Compiled` object -in use. This object can be inspected for any information about the in-progress -compilation, including ``compiler.dialect``, ``compiler.statement`` etc. -The :class:`~sqlalchemy.sql.compiler.SQLCompiler` and :class:`~sqlalchemy.sql.compiler.DDLCompiler` -both include a ``process()`` method which can be used for compilation of embedded attributes:: +The ``compiler`` argument is the :class:`~sqlalchemy.engine.base.Compiled` +object in use. This object can be inspected for any information about the +in-progress compilation, including ``compiler.dialect``, +``compiler.statement`` etc. The :class:`~sqlalchemy.sql.compiler.SQLCompiler` +and :class:`~sqlalchemy.sql.compiler.DDLCompiler` both include a ``process()`` +method which can be used for compilation of embedded attributes:: class InsertFromSelect(ClauseElement): def __init__(self, table, select): @@ -76,6 +77,36 @@ Produces:: "INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z FROM mytable WHERE mytable.x > :x_1)" +Subclassing Guidelines +====================== + +A big part of using the compiler extension is subclassing SQLAlchemy expression constructs. To make this easier, the expression and schema packages feature a set of "bases" intended for common tasks. A synopsis is as follows: + +* :class:`~sqlalchemy.sql.expression.ClauseElement` - This is the root + expression class. Any SQL expression can be derived from this base, and is + probably the best choice for longer constructs such as specialized INSERT + statements. + +* :class:`~sqlalchemy.sql.expression.ColumnElement` - The root of all + "column-like" elements. Anything that you'd place in the "columns" clause of + a SELECT statement (as well as order by and group by) can derive from this - + the object will automatically have Python "comparison" behavior. + +* :class:`~sqlalchemy.sql.expression.FunctionElement` - This is a hybrid of a + ``ColumnElement`` and a "from clause" like object, and represents a SQL + function or stored procedure type of call. Since most databases support + statements along the line of "SELECT FROM <some function>" + ``FunctionElement`` adds in the ability to be used in the FROM clause of a + ``select()`` construct. + +* :class:`~sqlalchemy.schema.DDLElement` - The root of all DDL expressions, + like CREATE TABLE, ALTER TABLE, etc. Compilation of ``DDLElement`` + subclasses is issued by a ``DDLCompiler`` instead of a ``SQLCompiler``. + ``DDLElement`` also features ``Table`` and ``MetaData`` event hooks via the + ``execute_at()`` method, allowing the construct to be invoked during CREATE + TABLE and DROP TABLE sequences. + + """ diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py index 3dee2a0ba..2e5a1a637 100644 --- a/lib/sqlalchemy/schema.py +++ b/lib/sqlalchemy/schema.py @@ -834,7 +834,8 @@ class Column(SchemaItem, expression.ColumnClause): class ForeignKey(SchemaItem): """Defines a column-level FOREIGN KEY constraint between two columns. - ``ForeignKey`` is specified as an argument to a :class:`Column` object, e.g.:: + ``ForeignKey`` is specified as an argument to a :class:`Column` object, + e.g.:: t = Table("remote_table", metadata, Column("remote_id", ForeignKey("main_table.id")) @@ -862,37 +863,37 @@ class ForeignKey(SchemaItem): :class:`Table` object's collection of constraints. :param column: A single target column for the key relationship. A - :class:`Column` object or a column name as a string: - ``tablename.columnkey`` or ``schema.tablename.columnkey``. - ``columnkey`` is the ``key`` which has been assigned to the column - (defaults to the column name itself), unless ``link_to_name`` is - ``True`` in which case the rendered name of the column is used. + :class:`Column` object or a column name as a string: + ``tablename.columnkey`` or ``schema.tablename.columnkey``. + ``columnkey`` is the ``key`` which has been assigned to the column + (defaults to the column name itself), unless ``link_to_name`` is + ``True`` in which case the rendered name of the column is used. :param name: Optional string. An in-database name for the key if - `constraint` is not provided. + `constraint` is not provided. :param onupdate: Optional string. If set, emit ON UPDATE <value> when - issuing DDL for this constraint. Typical values include CASCADE, - DELETE and RESTRICT. + issuing DDL for this constraint. Typical values include CASCADE, + DELETE and RESTRICT. :param ondelete: Optional string. If set, emit ON DELETE <value> when - issuing DDL for this constraint. Typical values include CASCADE, - DELETE and RESTRICT. + issuing DDL for this constraint. Typical values include CASCADE, + DELETE and RESTRICT. :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT - DEFERRABLE when issuing DDL for this constraint. + DEFERRABLE when issuing DDL for this constraint. :param initially: Optional string. If set, emit INITIALLY <value> when - issuing DDL for this constraint. + issuing DDL for this constraint. :param link_to_name: if True, the string name given in ``column`` is - the rendered name of the referenced column, not its locally assigned - ``key``. + the rendered name of the referenced column, not its locally + assigned ``key``. :param use_alter: passed to the underlying - :class:`ForeignKeyConstraint` to indicate the constraint should be - generated/dropped externally from the CREATE TABLE/ DROP TABLE - statement. See that classes' constructor for details. + :class:`ForeignKeyConstraint` to indicate the constraint should be + generated/dropped externally from the CREATE TABLE/ DROP TABLE + statement. See that classes' constructor for details. """ @@ -1420,42 +1421,42 @@ class ForeignKeyConstraint(Constraint): """Construct a composite-capable FOREIGN KEY. :param columns: A sequence of local column names. The named columns - must be defined and present in the parent Table. The names should - match the ``key`` given to each column (defaults to the name) unless - ``link_to_name`` is True. + must be defined and present in the parent Table. The names should + match the ``key`` given to each column (defaults to the name) unless + ``link_to_name`` is True. :param refcolumns: A sequence of foreign column names or Column - objects. The columns must all be located within the same Table. + objects. The columns must all be located within the same Table. :param name: Optional, the in-database name of the key. :param onupdate: Optional string. If set, emit ON UPDATE <value> when - issuing DDL for this constraint. Typical values include CASCADE, - DELETE and RESTRICT. + issuing DDL for this constraint. Typical values include CASCADE, + DELETE and RESTRICT. :param ondelete: Optional string. If set, emit ON DELETE <value> when - issuing DDL for this constraint. Typical values include CASCADE, - DELETE and RESTRICT. + issuing DDL for this constraint. Typical values include CASCADE, + DELETE and RESTRICT. :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT - DEFERRABLE when issuing DDL for this constraint. + DEFERRABLE when issuing DDL for this constraint. :param initially: Optional string. If set, emit INITIALLY <value> when - issuing DDL for this constraint. + issuing DDL for this constraint. :param link_to_name: if True, the string name given in ``column`` is - the rendered name of the referenced column, not its locally assigned - ``key``. + the rendered name of the referenced column, not its locally assigned + ``key``. :param use_alter: If True, do not emit the DDL for this constraint as - part of the CREATE TABLE definition. Instead, generate it via an ALTER - TABLE statement issued after the full collection of tables have been - created, and drop it via an ALTER TABLE statement before the full - collection of tables are dropped. This is shorthand for the usage of - :class:`AddConstraint` and :class:`DropConstraint` applied as - "after-create" and "before-drop" events on the MetaData object. This - is normally used to generate/drop constraints on objects that are - mutually dependent on each other. + part of the CREATE TABLE definition. Instead, generate it via an + ALTER TABLE statement issued after the full collection of tables + have been created, and drop it via an ALTER TABLE statement before + the full collection of tables are dropped. This is shorthand for the + usage of :class:`AddConstraint` and :class:`DropConstraint` applied + as "after-create" and "before-drop" events on the MetaData object. + This is normally used to generate/drop constraints on objects that + are mutually dependent on each other. """ super(ForeignKeyConstraint, self).__init__(name, deferrable, initially) diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 4b49dc0bc..5e4978bfb 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -647,29 +647,25 @@ def alias(selectable, alias=None): def literal(value, type_=None): """Return a literal clause, bound to a bind parameter. - Literal clauses are created automatically when non- - ``ClauseElement`` objects (such as strings, ints, dates, etc.) are - used in a comparison operation with a - :class:`~sqlalchemy.sql.expression._CompareMixin` subclass, such as a ``Column`` - object. Use this function to force the generation of a literal - clause, which will be created as a + Literal clauses are created automatically when non- ``ClauseElement`` + objects (such as strings, ints, dates, etc.) are used in a comparison + operation with a :class:`~sqlalchemy.sql.expression._CompareMixin` + subclass, such as a ``Column`` object. Use this function to force the + generation of a literal clause, which will be created as a :class:`~sqlalchemy.sql.expression._BindParamClause` with a bound value. - value - the value to be bound. Can be any Python object supported by - the underlying DB-API, or is translatable via the given type - argument. + :param value: the value to be bound. Can be any Python object supported by + the underlying DB-API, or is translatable via the given type argument. - type\_ - an optional :class:`~sqlalchemy.types.TypeEngine` which will provide - bind-parameter translation for this literal. + :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` which + will provide bind-parameter translation for this literal. """ return _BindParamClause(None, value, type_=type_, unique=True) def label(name, obj): - """Return a :class:`~sqlalchemy.sql.expression._Label` object for the given - :class:`~sqlalchemy.sql.expression.ColumnElement`. + """Return a :class:`~sqlalchemy.sql.expression._Label` object for the + given :class:`~sqlalchemy.sql.expression.ColumnElement`. A label changes the name of an element in the columns clause of a ``SELECT`` statement, typically via the ``AS`` SQL keyword. @@ -692,8 +688,8 @@ def column(text, type_=None): The object returned is an instance of :class:`~sqlalchemy.sql.expression.ColumnClause`, which represents the - "syntactical" portion of the schema-level :class:`~sqlalchemy.schema.Column` - object. + "syntactical" portion of the schema-level + :class:`~sqlalchemy.schema.Column` object. text the name of the column. Quoting rules will be applied to the @@ -767,8 +763,8 @@ def bindparam(key, value=None, type_=None, unique=False, required=False): return _BindParamClause(key, value, type_=type_, unique=unique, required=required) def outparam(key, type_=None): - """Create an 'OUT' parameter for usage in functions (stored procedures), for - databases which support them. + """Create an 'OUT' parameter for usage in functions (stored procedures), + for databases which support them. The ``outparam`` can be used like a regular function parameter. The "output" value will be available from the @@ -2031,8 +2027,12 @@ class FromClause(Selectable): return self._foreign_keys columns = property(attrgetter('_columns'), doc=_columns.__doc__) - primary_key = property(attrgetter('_primary_key'), doc=_primary_key.__doc__) - foreign_keys = property(attrgetter('_foreign_keys'), doc=_foreign_keys.__doc__) + primary_key = property( + attrgetter('_primary_key'), + doc=_primary_key.__doc__) + foreign_keys = property( + attrgetter('_foreign_keys'), + doc=_foreign_keys.__doc__) # synonyms for 'columns' c = _select_iterable = property(attrgetter('columns'), doc=_columns.__doc__) @@ -2378,31 +2378,26 @@ class _Case(ColumnElement): def _from_objects(self): return list(itertools.chain(*[x._from_objects for x in self.get_children()])) -class Function(ColumnElement, FromClause): - """Describe a SQL function.""" - - __visit_name__ = 'function' +class FunctionElement(ColumnElement, FromClause): + """Base for SQL function-oriented constructs.""" - def __init__(self, name, *clauses, **kwargs): - self.packagenames = kwargs.get('packagenames', None) or [] - self.name = name + def __init__(self, *clauses, **kwargs): self._bind = kwargs.get('bind', None) args = [_literal_as_binds(c, self.name) for c in clauses] - self.clause_expr = ClauseList(operator=operators.comma_op, group_contents=True, *args).self_group() + self.clause_expr = ClauseList( + operator=operators.comma_op, + group_contents=True, *args).\ + self_group() self.type = sqltypes.to_instance(kwargs.get('type_', None)) @property - def key(self): - return self.name - - @property def columns(self): return [self] @util.memoized_property def clauses(self): return self.clause_expr.element - + @property def _from_objects(self): return self.clauses._from_objects @@ -2414,9 +2409,6 @@ class Function(ColumnElement, FromClause): self.clause_expr = clone(self.clause_expr) self._reset_exported() util.reset_memoized(self, 'clauses') - - def _bind_param(self, obj): - return _BindParamClause(self.name, obj, type_=self.type, unique=True) def select(self): return select([self]) @@ -2430,6 +2422,23 @@ class Function(ColumnElement, FromClause): def _compare_type(self, obj): return self.type + def _bind_param(self, obj): + return _BindParamClause(None, obj, type_=self.type, unique=True) + + +class Function(FunctionElement): + """Describe a named SQL function.""" + + __visit_name__ = 'function' + + def __init__(self, name, *clauses, **kw): + self.packagenames = kw.pop('packagenames', None) or [] + self.name = name + FunctionElement.__init__(self, *clauses, **kw) + + def _bind_param(self, obj): + return _BindParamClause(self.name, obj, type_=self.type, unique=True) + class _Cast(ColumnElement): @@ -2837,10 +2846,6 @@ class _Grouping(ColumnElement): self.type = getattr(element, 'type', None) @property - def key(self): - return self.element.key - - @property def _label(self): return getattr(self.element, '_label', None) or self.anon_label @@ -3178,11 +3183,11 @@ class _SelectBaseMixin(object): self._group_by_clause = ClauseList(*util.to_list(group_by) or []) def as_scalar(self): - """return a 'scalar' representation of this selectable, which can be used - as a column expression. + """return a 'scalar' representation of this selectable, which can be + used as a column expression. - Typically, a select statement which has only one column in its columns clause - is eligible to be used as a scalar expression. + Typically, a select statement which has only one column in its columns + clause is eligible to be used as a scalar expression. The returned object is an instance of :class:`~sqlalchemy.sql.expression._ScalarSelect`. @@ -3194,17 +3199,18 @@ class _SelectBaseMixin(object): def apply_labels(self): """return a new selectable with the 'use_labels' flag set to True. - This will result in column expressions being generated using labels against their - table name, such as "SELECT somecolumn AS tablename_somecolumn". This allows - selectables which contain multiple FROM clauses to produce a unique set of column - names regardless of name conflicts among the individual FROM clauses. + This will result in column expressions being generated using labels + against their table name, such as "SELECT somecolumn AS + tablename_somecolumn". This allows selectables which contain multiple + FROM clauses to produce a unique set of column names regardless of + name conflicts among the individual FROM clauses. """ self.use_labels = True def label(self, name): - """return a 'scalar' representation of this selectable, embedded as a subquery - with a label. + """return a 'scalar' representation of this selectable, embedded as a + subquery with a label. See also ``as_scalar()``. diff --git a/test/ext/test_compiler.py b/test/ext/test_compiler.py index 3ee94d027..d625ae0ca 100644 --- a/test/ext/test_compiler.py +++ b/test/ext/test_compiler.py @@ -1,6 +1,7 @@ from sqlalchemy import * from sqlalchemy.types import TypeEngine -from sqlalchemy.sql.expression import ClauseElement, ColumnClause +from sqlalchemy.sql.expression import ClauseElement, ColumnClause,\ + FunctionElement from sqlalchemy.schema import DDLElement from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql import table, column @@ -151,3 +152,30 @@ class UserDefinedTest(TestBase, AssertsCompiledSQL): "DROP THINGY", ) + def test_functions(self): + from sqlalchemy.dialects.postgresql import base as postgresql + + class MyUtcFunction(FunctionElement): + pass + + @compiles(MyUtcFunction) + def visit_myfunc(element, compiler, **kw): + return "utcnow()" + + @compiles(MyUtcFunction, 'postgresql') + def visit_myfunc(element, compiler, **kw): + return "timezone('utc', current_timestamp)" + + self.assert_compile( + MyUtcFunction(), + "utcnow()", + use_default_dialect=True + ) + self.assert_compile( + MyUtcFunction(), + "timezone('utc', current_timestamp)", + dialect=postgresql.dialect() + ) + + +
\ No newline at end of file |