qal.sql package

Submodules

qal.sql.base module

Created on Sep 12, 2013

@author: Nicklas Boerjesson

class qal.sql.base.ParameterBase(_row_separator=None)[source]

Bases: builtins.object

This class is a base class for all parameter classes.

as_sql(_db_type)[source]
class qal.sql.base.ParameterExpressionItem(_operator=None)[source]

Bases: qal.sql.base.ParameterBase

The superclass of all classes that can be considered part of an expression

operator = 'C'
class qal.sql.base.SqlList(_itemclasses=None)[source]

Bases: builtins.list

This is the base class for lists of class instances.

as_sql(_db_type)[source]

Generate SQL for specified database engine

qal.sql.macros module

Created on Sep 26, 2013

@author: Nicklas Boerjesson

qal.sql.macros.copy_to_table(_dal, _values, _field_names, _field_types, _table_name, _create_table=None, _drop_existing=None)[source]

Copy a matrix of data into a table on the resource, return the table name.

Parameters:
  • _dal – An instance of DAL(qal.dal.DAL)
  • _values – The a list(rows) of lists(values) with values to be inserted
  • _field_names – The name of the fields(columns)
  • _field_types – The field types(qal.sql.types)
  • _table_name – The name of the destination tables
  • _create_table – Create the destination table based on _field_names, _field_types
  • _drop_existing – If a table with the same name as the destination table already exists, drop it
Returns:

The name of the destination table.

qal.sql.macros.create_table_skeleton(_table_name, _field_names, _field_types)[source]

Creates a sql for creating tables.

Parameters:
  • _table_name – The table name to use
  • _field_names – The field(column) names
  • _field_types – The types of the fields
Returns:

qal.sql.macros.make_column_definitions(_field_names, _field_types)[source]

Based on a list of field names and field types, return a list of ParameterColumndefinition objects.

Parameters:
  • _field_names – A list of field names
  • _field_types – A list of field types
Returns:

A list of ParameterColumndefinition

qal.sql.macros.make_column_identifiers(_field_names)[source]

Create columns idenfiers from a list of field names.

Parameters:_field_names – A list of field names
Returns:A list of column identifies (instances of ParameterIdentifier)
qal.sql.macros.make_delete_skeleton(_table_name, _key_fields)[source]

NOT IMPLEMENTED: Make a skeleton for an SQL DELETE statement object.

Parameters:
  • _table_name
  • _key_fields
Returns:

qal.sql.macros.make_insert_sql_with_parameters(_table_name, _field_names, _db_type, _field_types)[source]

Make a prepared statement-type INSERT INTO ...VALUES-SQL. See: http://en.wikipedia.org/wiki/Prepared_statement

Parameters:
  • _table_name – The table to insert into
  • _field_names – Fields(column) names
  • _db_type – The database backend type(qal.dal.types)
  • _field_types – A list of field types(qal.sql.types)
Returns:

Return a populated instance of an insert SQL

qal.sql.macros.make_update_skeleton(_table_name)[source]

Make a skeleton for an SQL update object

Parameters:_table_name – The name of the table
Returns:An instance of VerbUpdate
qal.sql.macros.select_all_skeleton(_table_name, _column_names=None)[source]

Returns a “SELECT * FROM _table_name”-structure, if _column_names are specified, SELECT only from those columns

Parameters:
  • _table_name – The name of the table to select from
  • _column_names – Optionally, the name of the columns to select from
Returns:

A populated instance of VerbSelect

qal.sql.meta module

Created on Sep 19, 2010

@author: Nicklas Boerjesson @note: This module accesses the Parameter_* and Verb_* classes from the SQL.py and generates meta data by analyzing its objects.

qal.sql.meta.find_class(_name, _raiserror=True)[source]

Using name, find a class reference

qal.sql.meta.list_class_properties(_classname)[source]

List properties for the class specified in _classname

qal.sql.meta.list_parameter_classes()[source]

List all parameter classes

qal.sql.meta.list_verb_classes()[source]

List all verb classes

qal.sql.remotable module

Created on Sep 30, 2013

@author: Nicklas Boerjesson

class qal.sql.remotable.ParameterRemotable[source]

Bases: builtins.object

This class is an auxilliary class for all parameter classes that are remotable. That is, they can fetch their data from, or perform their actions at, a different location than the parent class. If they return data, the data will be held in the temporary table, where it can be joined with or otherwise managed.

resource_uuid = None

qal.sql.sql module

qal.sql.sql holds the class structure representation of SQL

Note

  • The mySQL DDL implementation defaults to using innoDB, since stuff like foreign keys and other very important security features are lacking from myISAM.
  • All ParameterBase descendants property names are named in a specific way, so that one from that name can discern what types are allowed. For example: sources means that it is a list of ParameterSource.
  • Parameter_* means that it is some form of input, Verb_* means that this statement can be executed stand alone.
  • No parameters can be required in either Verb_* or Parameter_* classes. If they are, the classes cannot be inspected by qal.sql.meta.list_class_properties.
warning:Changes and new classes must satisfy both the import/export of data structures and schema generation.
copyright:Copyright 2010-2014 by Nicklas Boerjesson
license:BSD, see LICENSE for details.
class qal.sql.sql.ParameterAssignment(_left=None, _right=None)[source]

Bases: qal.sql.base.ParameterBase

This class holds a assignment, that is assign the result of the expression to the right to the identifier on the left.

as_sql(_db_type)[source]

Generate SQL for specified database engine

left = None
right = None
class qal.sql.sql.ParameterCase(_when_statements=None, _else_statement=None, _operator=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

Holds a CASE statement (see ParameterWhen)

else_statement = None
when_statements = None
class qal.sql.sql.ParameterCast(_expression=None, _datatype=None, _operator=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

A Cast() converts an expression to a specified datatype. Properties: * expression is a list of expression items(sql_types.expression_item_types()). * datatype is a string containing the datatype(as defined in sql_types.data_types())

Example: http://192.168.0.210/mediawiki/index.php/Unified_BPM_DatabaseAbstractionLayer#ParameterCast.28ParameterExpressionItem.29

datatype = ''
expression = ''
make_cast(_value)[source]

Generate the CAST syntax

class qal.sql.sql.ParameterColumndefinition(_name='', _datatype='', _notnull=None, _default='')[source]

Bases: qal.sql.base.ParameterBase

Holds a physical table column definition (not to confused with ParameterField which is a reference to one)

datatype = ''
default = ''
name = ''
notnull = None
class qal.sql.sql.ParameterCondition(_left=None, _right=None, _operator='', _and_or='')[source]

Bases: qal.sql.base.ParameterBase

This class holds a condition, that is a comparison (IF A=B)

and_or = ''
as_sql(_db_type, _index=0)[source]

Generate SQL for specified database engine (index if for handling when it is the first in a list of conditions)

left = None
operator = ''
right = None
class qal.sql.sql.ParameterConditions[source]

Bases: qal.sql.base.SqlList

This class holds a list of condition or list of conditions ((A=B AND C=D) OR (E=F))

as_sql(_db_type, _parent_index=0)[source]

Generate SQL for specified database engine

get_first_and_or()[source]

Return the first and/or to know if it should add its own (If it is the first condition there is no point in adding its or, but rather the parent conditions’ operator.

class qal.sql.sql.ParameterConstraint(_name='', _constraint_type=None, _references=None, _checkconditions=None)[source]

Bases: qal.sql.base.ParameterBase

Hold a key constraint declaration

checkconditions = None
constraint_type = None
name = ''
references = None
class qal.sql.sql.ParameterDDL(_operator=None)[source]

Bases: qal.sql.base.ParameterBase

Parent class for SQL DDL(Data Definition Language) statement classes

class qal.sql.sql.ParameterDataset(_data_source=None)[source]

Bases: qal.sql.base.ParameterExpressionItem, qal.sql.remotable.ParameterRemotable

Holds a dataset from an external, non-SQL source

data_source = None
dataset_to_sql()[source]

Generate SQL for specified database engine

class qal.sql.sql.ParameterExpression(_expressionitems=None, _operator=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

Holds an expression

expressionitems = None
class qal.sql.sql.ParameterField(_expression=None, _alias='')[source]

Bases: qal.sql.base.ParameterBase

Holds a field definition (SELECT _FIELD1 AS _FIELD)

alias = ''
expression = None
class qal.sql.sql.ParameterFunction(_parameters=None, _name='', _operator=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

Holds an SQL function call

name = ''
parameters = None
class qal.sql.sql.ParameterIdentifier(_identifier='', _operator=None, _prefix=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

Holds an identifier(column-, table or other reference)

identifier = ''
prefix = ''
class qal.sql.sql.ParameterIn(_in_values='', _operator=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

Hold an SQL IN-statement

in_values = None
class qal.sql.sql.ParameterNumeric(_numeric_value='', _operator=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

Holds a numeric parameter/value

numeric_value = ''
class qal.sql.sql.ParameterOrderByItem(_expressionitems=None, _direction=None)[source]

Bases: qal.sql.sql.ParameterExpression

This class holds an order by-statement

direction = None
class qal.sql.sql.ParameterParameter(_datatype='', _operator=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

Holds a parameter to be used in prepared statements.

datatype = ''
class qal.sql.sql.ParameterSet(_subsets=None, _set_operator=None)[source]

Bases: qal.sql.base.ParameterBase

This class holds a set. In SQL, that means more than one tabular datasets is combined using a set operator like UNION.

set_operator = None
subsets = None
class qal.sql.sql.ParameterSource(_expression=None, _conditions=None, _alias='', _join_type=None)[source]

Bases: qal.sql.base.ParameterBase, qal.sql.remotable.ParameterRemotable

This class hold a source of data that can be used with a FROM or JOIN-statement.

alias = ''
conditions = None
expression = None
join_type = None
class qal.sql.sql.ParameterString(_string_value='', _operator=None, _escape_character=None)[source]

Bases: qal.sql.base.ParameterExpressionItem

Holds a string parameter/value

escape_character = ''
string_value = ''
class qal.sql.sql.ParameterWhen(_conditions=None, _result=None)[source]

Bases: qal.sql.base.ParameterBase

Holds a WHEN statement

conditions = None
result = None
class qal.sql.sql.VerbCreateIndex(_name='', _index_type=None, _tablename='', _columnnames=None)[source]

Bases: qal.sql.base.ParameterBase

Holds a statement for creating database indices

columnnames = None
index_type = None
name = ''
tablename = ''
class qal.sql.sql.VerbCreateTable(_name=None, _columns=None, _constraints=None)[source]

Bases: qal.sql.sql.ParameterDDL

Holds a CREATE TABLE statement

columns = None
constraints = None
get_post_statements()[source]

Return post statements. That is statements run after the main SQL

make_columns(_db_type)[source]

Generate DDL for all columns

make_constraints(_db_type)[source]

Generate SQL for all constraints

name = ''
class qal.sql.sql.VerbCustom[source]

Bases: qal.sql.sql.ParameterDDL

This class holds custom statements (written, not generated) for all platforms. This is for when what is currenctly implementet do not suffice

sql_db2 = ''
sql_mysql = ''
sql_oracle = ''
sql_postgresql = ''
sql_sqlserver = ''
class qal.sql.sql.VerbDelete(_sources=None, _operator=None)[source]

Bases: qal.sql.base.ParameterBase

This class holds a DELETE statement

append_field_identifier(_identifier)[source]

Helper function to add ParameterIdentifier instances using just the field names

sources = None

@warning: Important! The first source must have a ParameterIdentifier as expression that specifies the target table.

class qal.sql.sql.VerbDropTable(_name=None)[source]

Bases: qal.sql.base.ParameterBase

This class holds a DELETE statement

name = None

The name of the table to be dropped.

class qal.sql.sql.VerbInsert(_destination_identifier=None, _column_identifiers=None, _select=None)[source]

Bases: qal.sql.base.ParameterBase

This class holds an INSERT statement

column_identifiers = None
data = None
destination_identifier = None
makeIdentifiers(_db_type)[source]

Generate SQL for the identifiers

class qal.sql.sql.VerbSelect(_fields=None, _sources=None, _operator=None, _order_by=None)[source]

Bases: qal.sql.base.ParameterExpressionItem, qal.sql.remotable.ParameterRemotable

This class holds a SELECT statement.

add_limit(_db_type)[source]

Generate SQL for specified database engine for limits on number of rows (TOP/LIMIT/FETCH FIRST)

append_field_identifier(_identifier)[source]

Helper to append field identifier classes to field list only using names

fields = None
order_by = None
sources = None
top_limit = None
class qal.sql.sql.VerbUpdate(_table_identifier=None, _assignments=None, _conditions=None)[source]

Bases: qal.sql.base.ParameterBase

This class holds an INSERT statement

assignments = None
conditions = None
table_identifier = None

qal.sql.types module

Created on Sep 20, 2010

@author: Nicklas Boerjesson @note: This module defines the basic types used in SQL. There are also some helper functions.

qal.sql.types.and_or()[source]

Returns a list of the supported logical operators

qal.sql.types.boolean()[source]

Returns a list of the supported boolean values

qal.sql.types.condition_part()[source]

Returns a list of the supported condition parts

qal.sql.types.constraint_types()[source]

Returns a list of the supported constraint types

qal.sql.types.data_source_types()[source]

Returns a list of the supported data source types

qal.sql.types.data_types()[source]

Returns a list of the supported data types

qal.sql.types.expression_item_types()[source]

Returns a list of the supported expression types

qal.sql.types.in_types()[source]

Returns a list of what is supported in a IN-statement

qal.sql.types.index_types()[source]

Returns a list of the supported index types

qal.sql.types.join_types()[source]

Returns a list of the supported join types

qal.sql.types.quoting_types()[source]

Returns a list of the supported quoting modes

qal.sql.types.set_operator()[source]

Returns a list of the supported set operators

qal.sql.types.sql_property_to_type(_property_name)[source]

Translates a property name to a type, like decimal or string. Property names in the SQL.py class structure are chosen to not collide.

qal.sql.types.tabular_expression_item_types()[source]

Returns a list of the supported tabular expression types

qal.sql.types.verbs()[source]

Returns a list of the supported verb types

qal.sql.utils module

Created on Oct 2, 2012

@author: Nicklas Boerjesson

qal.sql.utils.add_comma(_index, _value)[source]

Adds a comma if index > 0, used in loops when making lists.

qal.sql.utils.add_comma_rs(_index, _value, _row_separator)[source]

Adds a comma and row separator if index > 0, used in loops when making lists of references.

qal.sql.utils.add_operator(_index, _operator)[source]

Adds a an operator if index > 0, used in loops when making conditions.

qal.sql.utils.check_for_param_content(_value)[source]
qal.sql.utils.check_not_null(_classname, _items)[source]
qal.sql.utils.citate(AValue, _db_type)[source]

Adds citations when db_type is DB_POSTGRESQL

qal.sql.utils.comma_separate(_list, _db_type)[source]

Convert a list of possible ParameterBase-descendants.

qal.sql.utils.curr_datetime(_db_type)[source]

Returns a database-type specific (see dal_types) way of getting the current date time.

qal.sql.utils.curr_user(_db_type)[source]

Returns a database-type specific (see dal_types) way of getting the current user.

qal.sql.utils.datatype_to_parameter(_db_type, _datatype)[source]

Converts a python data type to the database-driver appropriate parameter substitution string

qal.sql.utils.db_specific_datatype(value, DB)[source]

Converts general DAL datatypes(as defined in sql_types) database-type specific (see dal_types) representations.

qal.sql.utils.db_specific_datatype_parse_length(_value)[source]
qal.sql.utils.db_specific_object_reference(_value, _db_type)[source]

Qualifies an object reference in a database-type specific (see dal_types) way.

qal.sql.utils.db_specific_operator(_operator, _db_type)[source]

Makes PostgreSQL’s LIKE operator case insensitive by uppercasing it.

qal.sql.utils.error_on_blank(_value, _error)[source]

Raises an error if blank

qal.sql.utils.handle_temp_table_ref(_identifier, _db_type)[source]

Removes the “#” in SQL Server temporary table references.

qal.sql.utils.make_function(_name, _value)[source]

Assembles an SQL function call.

qal.sql.utils.make_operator(_operator, _double_pipe_c)[source]

Handles concatenation operators.

qal.sql.utils.none_as_sql(_value, _db_type, _none_value='', _error=None)[source]

Raises an error or returns a value if blank

qal.sql.utils.oracle_add_escape(_value, _escape_character)[source]

Add the oracle escape ke_delimiteryword if nessesary.

qal.sql.utils.oracle_create_auto_increment(_table, _column)[source]

Work around Oracle’s silly unwillingness to support auto increment.

qal.sql.utils.parenthesise(_value)[source]

Adds a parenthesis around a values.

qal.sql.utils.tabular_source_to_sql(_tabular_source, _db_type)[source]

qal.sql.xml module

Created on Sep 21, 2010

@author: Nicklas Boerjesson

class qal.sql.xml.SQL_XML[source]

Bases: qal.common.xml_utils.XML_Translation

This class converts XML into a class structure(declare in SQL.py) that holds the statements.

base_path = None

The base path of the document. Allows for relative paths within.

encoding = 'utf-8'
generate_schema()[source]

Generates an XML schema based on the class structure in SQL.py

sql_structure_to_xml(_structure)[source]

Translates an XML structure into XML

xml_file_to_sql(_xml_file_name, **kwargs)[source]

Reads a specified XML fil and translates it into an SQL class structure.

xml_to_sql_structure(_xml='', _node=None, _base_path=None)[source]

Translates an XML file into a class structure

qal.sql.xml.sql_property_to_xml_type(_PropertyName)[source]

Converts a SQL class’ property name to an XML type

Module contents

qal.sql is an abstraction of the SQL language

  • Abstracts SQL to enable a single object structure to platform-independently generate SQL for each platform.
  • Can translate itself from and to an XML structure for portability and persistence.
  • Can implicitly join any qal.dataset from all kinds of sources directly on a target server(loads them into temp tables and joins then).
  • Currently available qal.dataset:s : flat file, matrix, XPath(XML/HTML/XHTML) and spreadsheet(Excel-formats/.odt).
  • RBDMS Platforms (qal.dal.types) : MySQL, PostgreSQL, Oracle, DB2 and SQL server
copyright:Copyright 2010-2014 by Nicklas Boerjesson
license:BSD, see LICENSE for details.