<commit_before>#!/usr/bin/env python
# -*- coding: utf-8 -*-

'''It provides the basic bricks to build SQLs.

The classes or functions you may use frequently:

.. autosummary ::
    raw
    default
    star
    param
    or_
    and_
    subq

It is designed for standard SQL and tested in PostgreSQL. If your database uses
non-standard SQL, such as MySQL, you may need to customize and override the
following functions.

.. autosummary ::
    escape
    format_param
    stringify_bool
    delimit_identifier
    escape_identifier

.. note::

    For MySQL, an official patch is here - :doc:`/mysql`.

If you need to customize more, the following classes may help you.

.. autosummary ::
    Clause
    Statement
    Query

.. versionchanged:: 0.1.6
    It is rewritten and totally different from old version.
'''

__all__ = [
    'escape', 'format_param', 'stringify_bool',
    'delimit_identifier', 'escape_identifier',
    'raw', 'param', 'default', '___', 'star',
    'qualifier', 'paren', 'value',
    'OptionError', 'allowed_options', 'identifier',
    'joiner',
    'concat_by_comma', 'concat_by_and', 'concat_by_space', 'concat_by_or',
    'OperatorError', 'allowed_operators',
    'build_where', 'build_set', 'build_on',
    'or_', 'and_', 'dot', 'as_', 'asc', 'desc', 'in_operand', 'subq',
    'Clause', 'Statement', 'Query'
]

import sys
if sys.version_info >= (3,):
    unicode = str
    basestring = str

from functools import wraps
from datetime import datetime, date, time

def warning(s):
    print >> sys.stderr, 'Warning: {}'.format(s)

def debug(s):
    print >> sys.stderr, 'Debug: {}'.format(s)

# lowest-level

def escape(s):
    '''It escapes the value.

    By default, it just replaces ``'`` (single-quote) with ``''`` (two single-quotes).

    It aims at avoiding SQL injection. Here are some examples:

    >>> tmpl = "select * from person where person_id = '%s';"
    >>> evil_value = "' or true; -- "

    >>> print tmpl % evil_value
    select * from person where person_id = '' or true; -- ';

    >>> print tmpl % escape(evil_value)
    select * from person where person_id = '\'' or true; -- ';

    .. warning ::
        If you don't use utf-8 for your connection, such as big5, gbk, please
        use native escape function to ensure the security. See
        :mod:`mosql.psycopg2_escape` or :mod:`mosql.MySQLdb_escape` for more
        information.
    '''
    return s.replace("'", "''")

std_escape = escape

def format_param(s=''):
    '''It formats the parameter of prepared statement.

    By default, it formats the parameter in `pyformat
    <http://www.python.org/dev/peps/pep-0249/#paramstyle>`_.

    >>> format_param('name')
    '%(name)s'

    >>> format_param()
    '%s'
    '''
    return '%%(%s)s' % s if s else '%s'

std_format_param = format_param

def stringify_bool(b):
    '''It stringifies the bool.

    By default, it returns ``'TRUE'`` if `b` is true, otherwise it returns
    ``'FALSE'``.
    '''
    return 'TRUE' if b else 'FALSE'

std_stringify_bool = stringify_bool

def delimit_identifier(s):
    '''It delimits the identifier.

    By default, it conforms the standard to encloses the identifier, `s`, by
    ``"`` (double quote).

    .. versionchanged:: 0.9.2
        It's not disableable anymore. Use :class:`raw` instead.
    '''
    return '"%s"' % s

std_delimit_identifier = delimit_identifier

def escape_identifier(s):
    r'''It escapes the identifier.

    By default, it just replaces ``"`` (double-quote) with ``""`` (two double-quotes).

    It also aims at avoid SQL injection. Here are some examples:

    >>> tmpl = 'select * from person where "%s" = \'mosky\';'
    >>> evil_identifier = 'person_id" = \'\' or true; -- '

    >>> print tmpl % evil_identifier
    select * from person where "person_id" = '' or true; -- " = 'mosky';

    >>> print tmpl % escape_identifier(evil_identifier)
    select * from person where "person_id"" = '' or true; -- " = 'mosky';
    '''
    return s.replace('"', '""')

std_escape_identifier = escape_identifier

class raw(str):
    '''The qualifier function does nothing when the input is an instance of this
    class. This is a subclass of built-in `str` type.

    .. warning ::
        It is your responsibility to ensure that your SQL queries are properly escaped if you use this class.
    '''

    def __repr__(self):
        return 'raw(%r)' % str(self)

default = raw('DEFAULT')
'The ``DEFAULT`` keyword in SQL.'

star = raw('*')
'The ``*`` keyword in SQL.'

class param(str):
    '''The :func:`value` builds this type as a parameter for the prepared statement.

    >>> value(param(''))
    '%s'
    >>> value(param('name'))
    '%(name)s'

    This is just a subclass of built-in `str` type.

    The :class:`___` is an alias of it.
    '''

    def __repr__(self):
        return 'param(%r)' % self

___ = param

# low-level

def _is_iterable_not_str(x):
    return not isinstance(x, basestring) and hasattr(x, '__iter__')

def qualifier(f):
    '''A decorator which makes all items in an `iterable` apply a qualifier
    function, `f`, or simply apply the qualifier function to the input if the
    input is not an `iterable`.

    The `iterable` here means the iterable except string.

    It also makes a qualifier function returns the input without changes if the
    input is an instance of :class:`raw`.
    '''

    @wraps(f)
    def qualifier_wrapper(x):
        if isinstance(x, raw):
            return x
        elif _is_iterable_not_str(x):
            return [item if isinstance(item, raw) else f(item) for item in x]
        else:
            if isinstance(x, unicode):
                x = x.encode('utf-8')
            return f(x)

    return qualifier_wrapper

@qualifier
def value(x):
    '''A qualifier function for values.

    >>> print value('normal string')
    'normal string'

    >>> print value(u'normal unicode')
    'normal unicode'

    >>> print value(True)
    TRUE

    >>> print value(datetime(2013, 4, 19, 14, 41, 10))
    '2013-04-19 14:41:10'

    >>> print value(date(2013, 4, 19))
    '2013-04-19'

    >>> print value(time(14, 41, 10))
    '14:41:10'

    >>> print value(raw('count(person_id) > 1'))
    count(person_id) > 1

    >>> print value(param('myparam'))
    %(myparam)s
    '''

    # NOTE: 1. raw and 2. param are subclasses of str, so the two types must be
    # first than str.

    if x is None:
        return 'NULL'
    elif isinstance(x, param):
        return format_param(x)
    elif isinstance(x, basestring):
        return "'%s'" % escape(x)
    elif isinstance(x, (datetime, date, time)):
        return "'%s'" % x
    elif isinstance(x, bool):
        return stringify_bool(x)
    else:
        return str(x)

class OptionError(Exception):
    '''The instance of it will be raised when :func:`identifier` detects an
    invalid option.

    .. seealso ::
        The operators allowed --- :attr:`allowed_options`.'''

    def __init__(self, op):
        self.op = op

    def __str__(self):
        return 'this option is not allowed: %r' % self.op

allowed_options = set(['DESC', 'ASC'])
'''The options which are allowed by :func:`identifier`.

An :exc:`OptionError` is raised if an option not allowed is found.

.. versionchanged:: 0.9.2
    It's not disableable anymore. Use :class:`raw` instead.
'''

def _is_pair(x):
    return _is_iterable_not_str(x) and len(x) == 2

@qualifier
def identifier(s):
    '''A qualifier function for identifiers.

    It uses the :func:`delimit_identifier` and :func:`escape_identifier` to
    qualify the input.

    It returns the input with no changes if :func:`delimit_identifier` is
    ``None``.

    >>> print identifier('column_name')
    "column_name"

    >>> print identifier('column_name desc')
    "column_name" DESC

    >>> print identifier('table_name.column_name')
    "table_name"."column_name"

    >>> print identifier('table_name.column_name DESC')
    "table_name"."column_name" DESC
    '''

    if _is_pair(s):
        return '%s AS %s' % (identifier(s[0]), identifier(s[1]))
    elif s.find('.') == -1 and s.find(' ') == -1:
        return delimit_identifier(escape_identifier(s))
    else:

        t, _, c = s.rpartition('.')
        c, _, op = c.partition(' ')

        r = ''

        if t:
            t = delimit_identifier(escape_identifier(t))
            r += t+'.'

        if c:
            c = delimit_identifier(escape_identifier(c))
            r += c

        if op:
            op = op.upper()
            if op not in allowed_options:
                raise OptionError(op)
            r += ' '+op

        return r

@qualifier
def paren(s):
    '''A qualifier function which encloses the input with ``()`` (paren).'''
    return '(%s)' % s

def joiner(f):
    '''A decorator which makes the input apply this function only if the input
    is an `iterable`, otherwise it just returns the same input.

    The `iterable` here means the iterable except string.
    '''

    @wraps(f)
    def joiner_wrapper(x):
        if _is_iterable_not_str(x):
            return f(x)
        else:
            return x

    return joiner_wrapper

@joiner
def concat_by_and(i):
    '''A joiner function which concats the iterable by ``'AND'``.'''
    return ' AND '.join(i)

@joiner
def concat_by_or(i):
    '''A joiner function which concats the iterable by ``'OR'``.'''
    return ' OR '.join(i)

@joiner
def concat_by_space(i):
    '''A joiner function which concats the iterable by a space.'''
    return ' '.join(i)

@joiner
def concat_by_comma(i):
    '''A joiner function which concats the iterable by ``,`` (comma).'''
    return ', '.join(i)

class OperatorError(Exception):
    '''The instance of it will be raised when :func:`build_where` detects an
    invalid operator.

    .. seealso ::
        The operators allowed --- :attr:`allowed_operators`.'''

    def __init__(self, op):
        self.op = op

    def __str__(self):
        return 'this operator is not allowed: %r' % self.op

allowed_operators = set([
    '<', '>', '<=', '>=', '=', '<>', '!=',
    'IS', 'IS NOT',
    'IN', 'NOT IN',
    'LIKE', 'NOT LIKE',
    'SIMILAR TO', 'NOT SIMILAR TO',
    '~', '~*', '!~', '!~*',
])
'''The operators which are allowed by :func:`build_where`.

An :exc:`OperatorError` is raised if an operator not allowed is found.

.. versionchanged:: 0.9.2
    It's not disableable anymore. Use :class:`raw` instead.
'''

def _to_pairs(x):

    if hasattr(x, 'iteritems'):
        x = x.iteritems()
    elif hasattr(x, 'items'):
        x = x.items()

    return x

def _build_condition(x, key_qualifier=identifier, value_qualifier=value):

    ps = _to_pairs(x)

    pieces = []

    for k, v in ps:

        # find the op

        op = ''

        # TODO: let user use subquery with operator in first (key) part
        if not isinstance(k, raw):

            # split the op out
            k, _, op = k.partition(' ')

            if not op:
                if _is_iterable_not_str(v):
                    op = 'IN'
                elif v is None:
                    op = 'IS'
                else:
                    op = '='
            else:
                op = op.strip().upper()
                if op not in allowed_operators:
                    raise OperatorError(op)

        # feature of autoparam
        if isinstance(v, type) and v.__name__ == 'param':
            v = param(k)

        # qualify the k
        k = key_qualifier(k)

        # qualify the v
        v = value_qualifier(v)
        if _is_iterable_not_str(v):
            v = paren(concat_by_comma(v))

        if op == 'IN' and v == '()':
            pieces.append(stringify_bool(False))
        elif op:
            pieces.append('%s %s %s' % (k, op, v))
        else:
            pieces.append('%s %s' % (k, v))

    return concat_by_and(pieces)
<commit_msg>support to use pair as where's key<commit_after>#!/usr/bin/env python
# -*- coding: utf-8 -*-

'''It provides the basic bricks to build SQLs.

The classes or functions you may use frequently:

.. autosummary ::
    raw
    default
    star
    param
    or_
    and_
    subq

It is designed for standard SQL and tested in PostgreSQL. If your database uses
non-standard SQL, such as MySQL, you may need to customize and override the
following functions.

.. autosummary ::
    escape
    format_param
    stringify_bool
    delimit_identifier
    escape_identifier

.. note::

    For MySQL, an official patch is here - :doc:`/mysql`.

If you need to customize more, the following classes may help you.

.. autosummary ::
    Clause
    Statement
    Query

.. versionchanged:: 0.1.6
    It is rewritten and totally different from old version.
'''

__all__ = [
    'escape', 'format_param', 'stringify_bool',
    'delimit_identifier', 'escape_identifier',
    'raw', 'param', 'default', '___', 'star',
    'qualifier', 'paren', 'value',
    'OptionError', 'allowed_options', 'identifier',
    'joiner',
    'concat_by_comma', 'concat_by_and', 'concat_by_space', 'concat_by_or',
    'OperatorError', 'allowed_operators',
    'build_where', 'build_set', 'build_on',
    'or_', 'and_', 'dot', 'as_', 'asc', 'desc', 'in_operand', 'subq',
    'Clause', 'Statement', 'Query'
]

import sys
if sys.version_info >= (3,):
    unicode = str
    basestring = str

from functools import wraps
from datetime import datetime, date, time

def warning(s):
    print >> sys.stderr, 'Warning: {}'.format(s)

def debug(s):
    print >> sys.stderr, 'Debug: {}'.format(s)

# lowest-level

def escape(s):
    '''It escapes the value.

    By default, it just replaces ``'`` (single-quote) with ``''`` (two single-quotes).

    It aims at avoiding SQL injection. Here are some examples:

    >>> tmpl = "select * from person where person_id = '%s';"
    >>> evil_value = "' or true; -- "

    >>> print tmpl % evil_value
    select * from person where person_id = '' or true; -- ';

    >>> print tmpl % escape(evil_value)
    select * from person where person_id = '\'' or true; -- ';

    .. warning ::
        If you don't use utf-8 for your connection, such as big5, gbk, please
        use native escape function to ensure the security. See
        :mod:`mosql.psycopg2_escape` or :mod:`mosql.MySQLdb_escape` for more
        information.
    '''
    return s.replace("'", "''")

std_escape = escape

def format_param(s=''):
    '''It formats the parameter of prepared statement.

    By default, it formats the parameter in `pyformat
    <http://www.python.org/dev/peps/pep-0249/#paramstyle>`_.

    >>> format_param('name')
    '%(name)s'

    >>> format_param()
    '%s'
    '''
    return '%%(%s)s' % s if s else '%s'

std_format_param = format_param

def stringify_bool(b):
    '''It stringifies the bool.

    By default, it returns ``'TRUE'`` if `b` is true, otherwise it returns
    ``'FALSE'``.
    '''
    return 'TRUE' if b else 'FALSE'

std_stringify_bool = stringify_bool

def delimit_identifier(s):
    '''It delimits the identifier.

    By default, it conforms the standard to encloses the identifier, `s`, by
    ``"`` (double quote).

    .. versionchanged:: 0.9.2
        It's not disableable anymore. Use :class:`raw` instead.
    '''
    return '"%s"' % s

std_delimit_identifier = delimit_identifier

def escape_identifier(s):
    r'''It escapes the identifier.

    By default, it just replaces ``"`` (double-quote) with ``""`` (two double-quotes).

    It also aims at avoid SQL injection. Here are some examples:

    >>> tmpl = 'select * from person where "%s" = \'mosky\';'
    >>> evil_identifier = 'person_id" = \'\' or true; -- '

    >>> print tmpl % evil_identifier
    select * from person where "person_id" = '' or true; -- " = 'mosky';

    >>> print tmpl % escape_identifier(evil_identifier)
    select * from person where "person_id"" = '' or true; -- " = 'mosky';
    '''
    return s.replace('"', '""')

std_escape_identifier = escape_identifier

class raw(str):
    '''The qualifier function does nothing when the input is an instance of this
    class. This is a subclass of built-in `str` type.

    .. warning ::
        It is your responsibility to ensure that your SQL queries are properly escaped if you use this class.
    '''

    def __repr__(self):
        return 'raw(%r)' % str(self)

default = raw('DEFAULT')
'The ``DEFAULT`` keyword in SQL.'

star = raw('*')
'The ``*`` keyword in SQL.'

class param(str):
    '''The :func:`value` builds this type as a parameter for the prepared statement.

    >>> value(param(''))
    '%s'
    >>> value(param('name'))
    '%(name)s'

    This is just a subclass of built-in `str` type.

    The :class:`___` is an alias of it.
    '''

    def __repr__(self):
        return 'param(%r)' % self

___ = param

# low-level

def _is_iterable_not_str(x):
    return not isinstance(x, basestring) and hasattr(x, '__iter__')

def qualifier(f):
    '''A decorator which makes all items in an `iterable` apply a qualifier
    function, `f`, or simply apply the qualifier function to the input if the
    input is not an `iterable`.

    The `iterable` here means the iterable except string.

    It also makes a qualifier function returns the input without changes if the
    input is an instance of :class:`raw`.
    '''

    @wraps(f)
    def qualifier_wrapper(x):
        if isinstance(x, raw):
            return x
        elif _is_iterable_not_str(x):
            return [item if isinstance(item, raw) else f(item) for item in x]
        else:
            if isinstance(x, unicode):
                x = x.encode('utf-8')
            return f(x)

    return qualifier_wrapper

@qualifier
def value(x):
    '''A qualifier function for values.

    >>> print value('normal string')
    'normal string'

    >>> print value(u'normal unicode')
    'normal unicode'

    >>> print value(True)
    TRUE

    >>> print value(datetime(2013, 4, 19, 14, 41, 10))
    '2013-04-19 14:41:10'

    >>> print value(date(2013, 4, 19))
    '2013-04-19'

    >>> print value(time(14, 41, 10))
    '14:41:10'

    >>> print value(raw('count(person_id) > 1'))
    count(person_id) > 1

    >>> print value(param('myparam'))
    %(myparam)s
    '''

    # NOTE: 1. raw and 2. param are subclasses of str, so the two types must be
    # first than str.

    if x is None:
        return 'NULL'
    elif isinstance(x, param):
        return format_param(x)
    elif isinstance(x, basestring):
        return "'%s'" % escape(x)
    elif isinstance(x, (datetime, date, time)):
        return "'%s'" % x
    elif isinstance(x, bool):
        return stringify_bool(x)
    else:
        return str(x)

class OptionError(Exception):
    '''The instance of it will be raised when :func:`identifier` detects an
    invalid option.

    .. seealso ::
        The operators allowed --- :attr:`allowed_options`.'''

    def __init__(self, op):
        self.op = op

    def __str__(self):
        return 'this option is not allowed: %r' % self.op

allowed_options = set(['DESC', 'ASC'])
'''The options which are allowed by :func:`identifier`.

An :exc:`OptionError` is raised if an option not allowed is found.

.. versionchanged:: 0.9.2
    It's not disableable anymore. Use :class:`raw` instead.
'''

def _is_pair(x):
    return _is_iterable_not_str(x) and len(x) == 2

@qualifier
def identifier(s):
    '''A qualifier function for identifiers.

    It uses the :func:`delimit_identifier` and :func:`escape_identifier` to
    qualify the input.

    It returns the input with no changes if :func:`delimit_identifier` is
    ``None``.

    >>> print identifier('column_name')
    "column_name"

    >>> print identifier('column_name desc')
    "column_name" DESC

    >>> print identifier('table_name.column_name')
    "table_name"."column_name"

    >>> print identifier('table_name.column_name DESC')
    "table_name"."column_name" DESC
    '''

    if _is_pair(s):
        return '%s AS %s' % (identifier(s[0]), identifier(s[1]))
    elif s.find('.') == -1 and s.find(' ') == -1:
        return delimit_identifier(escape_identifier(s))
    else:

        t, _, c = s.rpartition('.')
        c, _, op = c.partition(' ')

        r = ''

        if t:
            t = delimit_identifier(escape_identifier(t))
            r += t+'.'

        if c:
            c = delimit_identifier(escape_identifier(c))
            r += c

        if op:
            op = op.upper()
            if op not in allowed_options:
                raise OptionError(op)
            r += ' '+op

        return r

@qualifier
def paren(s):
    '''A qualifier function which encloses the input with ``()`` (paren).'''
    return '(%s)' % s

def joiner(f):
    '''A decorator which makes the input apply this function only if the input
    is an `iterable`, otherwise it just returns the same input.

    The `iterable` here means the iterable except string.
    '''

    @wraps(f)
    def joiner_wrapper(x):
        if _is_iterable_not_str(x):
            return f(x)
        else:
            return x

    return joiner_wrapper

@joiner
def concat_by_and(i):
    '''A joiner function which concats the iterable by ``'AND'``.'''
    return ' AND '.join(i)

@joiner
def concat_by_or(i):
    '''A joiner function which concats the iterable by ``'OR'``.'''
    return ' OR '.join(i)

@joiner
def concat_by_space(i):
    '''A joiner function which concats the iterable by a space.'''
    return ' '.join(i)

@joiner
def concat_by_comma(i):
    '''A joiner function which concats the iterable by ``,`` (comma).'''
    return ', '.join(i)

class OperatorError(Exception):
    '''The instance of it will be raised when :func:`build_where` detects an
    invalid operator.

    .. seealso ::
        The operators allowed --- :attr:`allowed_operators`.'''

    def __init__(self, op):
        self.op = op

    def __str__(self):
        return 'this operator is not allowed: %r' % self.op

allowed_operators = set([
    '<', '>', '<=', '>=', '=', '<>', '!=',
    'IS', 'IS NOT',
    'IN', 'NOT IN',
    'LIKE', 'NOT LIKE',
    'SIMILAR TO', 'NOT SIMILAR TO',
    '~', '~*', '!~', '!~*',
])
'''The operators which are allowed by :func:`build_where`.

An :exc:`OperatorError` is raised if an operator not allowed is found.

.. versionchanged:: 0.9.2
    It's not disableable anymore. Use :class:`raw` instead.
'''

def _to_pairs(x):

    if hasattr(x, 'iteritems'):
        x = x.iteritems()
    elif hasattr(x, 'items'):
        x = x.items()

    return x

def _build_condition(x, key_qualifier=identifier, value_qualifier=value):

    ps = _to_pairs(x)

    pieces = []

    for k, v in ps:

        # find the op

        op = ''

        # TODO: let user use subquery with operator in first (key) part
        # if k is raw, it means we can't modify the k and op
        if not isinstance(k, raw):

            if _is_pair(k):
                # unpack the op out
                k, op = k

            if not op:
                # split the op out
                k, _, op = k.partition(' ')

            if not op:
                # decide op automatically
                if _is_iterable_not_str(v):
                    op = 'IN'
                elif v is None:
                    op = 'IS'
                else:
                    op = '='

            if not isinstance(op, raw):
                # normalize the op
                op = op.strip().upper()
                # verify the op
                if op not in allowed_operators:
                    raise OperatorError(op)

        # feature of autoparam
        if isinstance(v, type) and v.__name__ == 'param':
            v = param(k)

        # qualify the k
        k = key_qualifier(k)

        # qualify the v
        v = value_qualifier(v)
        if _is_iterable_not_str(v):
            v = paren(concat_by_comma(v))

        if op == 'IN' and v == '()':
            pieces.append(stringify_bool(False))
        elif op:
            pieces.append('%s %s %s' % (k, op, v))
        else:
            pieces.append('%s %s' % (k, v))

    return concat_by_and(pieces)

{'fraction_non_alphanumeric': 0.13710014198613546, 'fraction_numerical': 0.007099306773573875, 'mean_word_length': 2.5010233918128657, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 169, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7505773', 'n_tokens_mistral': 7829, 'n_tokens_neox': 7475, 'n_words': 2443}