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

'''It contains the common SQL builders.

.. autosummary ::
    insert
    select
    update
    delete

It is designed for building the standard SQL statement (or PostgreSQL).

.. note::
    If you use MySQL, MoSQL provides a patch for MySQL --- :mod:`mosql.mysql`.
'''

from .util2 import *

# defines formatting chains
single_value      = (value, )
single_identifier = (identifier, )
identifier_list   = (identifier, concat_by_comma)
where_list        = (build_where, )
set_list          = (build_set, )
statement_list    = (concat_by_space, )

# insert

insert  = Clause('insert into', single_identifier)
columns = Clause('columns'    , (identifier, concat_by_comma, paren), hidden=True)
values  = Clause('values'     , (value, concat_by_comma, paren))
returning = Clause('returning'  , identifier_list)

insert_into_stat = Statement([insert, columns, values, returning])

def insert(table, pairs_or_columns=None, values=None, **clauses_args):
    '''It generates the SQL statement, ``insert into ...``.

    The following usages generate the same SQL statement:

    >>> print insert('person', {'person_id': 'mosky', 'name': '<NAME>'})
    INSERT INTO "person" ("person_id", "name") VALUES ('mosky', '<NAME>')

    >>> print insert('person', (('person_id', 'mosky'), ('name', '<NAME>')))
    INSERT INTO "person" ("person_id", "name") VALUES ('mosky', '<NAME>')

    >>> print insert('person', ('person_id', 'name'), ('mosky', '<NAME>'))
    INSERT INTO "person" ("person_id", "name") VALUES ('mosky', '<NAME>')

    or you can ignore the columns:

    >>> print insert('person', values=('mosky', '<NAME>'))
    INSERT INTO "person" VALUES ('mosky', '<NAME>')

    The :func:`update` and :func:`delete` and it are also supported ``returning``.

    >>> print insert('person', {'person_id': 'mosky', 'name': '<NAME>'}, returning=raw('*'))
    INSERT INTO "person" ("person_id", "name") VALUES ('mosky', '<NAME>') RETURNING *
    '''

    clauses_args['insert into'] = table

    if values is None:
        if hasattr(pairs_or_columns, 'items'):
            pairs = pairs_or_columns.items()
        else:
            pairs = pairs_or_columns
        clauses_args['columns'], clauses_args['values'] = zip(*pairs)
    else:
        clauses_args['columns'] = pairs_or_columns
        clauses_args['values']  = values

    return insert_into_stat.format(clauses_args)

# select

select   = Clause('select'  , identifier_list)
from_    = Clause('from'    , identifier_list)
joins    = Clause('joins'   , statement_list, hidden=True)
where    = Clause('where'   , where_list)
group_by = Clause('group by', identifier_list)
having   = Clause('having'  , where_list)
order_by = Clause('order by', identifier_list)
limit    = Clause('limit'   , single_value)
offset   = Clause('offset'  , single_value)

select_stat = Statement([select, from_, joins, where, group_by, having, order_by, limit, offset])

def select(table, where=None, select=raw('*'), **clauses_args):
    '''It generates the SQL statement, ``select ...`` .

    The following usages generate the same SQL statement.

    >>> print select('person', {'person_id': 'mosky'})
    SELECT * FROM "person" WHERE "person_id" = 'mosky'

    >>> print select('person', (('person_id', 'mosky'), ))
    SELECT * FROM "person" WHERE "person_id" = 'mosky'

    Building prepare statement with :class:`mosql.util.param`:

    >>> print select('table', {'custom_param': param('my_param'), 'auto_param': param, 'using_alias': ___})
    SELECT * FROM "table" WHERE "auto_param" = %(auto_param)s AND "using_alias" = %(using_alias)s AND "custom_param" = %(my_param)s

    You can also specify the ``group_by``, ``having``, ``order_by``, ``limit``
    and ``offset`` in the keyword arguments. Here are some examples:

    >>> print select('person', {'name like': 'Mosky%'}, group_by=('age', ))
    SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' GROUP BY "age"

    >>> print select('person', {'name like': 'Mosky%'}, order_by=('age', ))
    SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' ORDER BY "age"

    >>> print select('person', {'name like': 'Mosky%'}, limit=3, offset=1)
    SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' LIMIT 3 OFFSET 1

    The operators are also supported:

    >>> print select('person', {'person_id': ('andy', 'bob')})
    SELECT * FROM "person" WHERE "person_id" IN ('andy', 'bob')

    >>> print select('person', {'name': None})
    SELECT * FROM "person" WHERE "name" IS NULL

    >>> print select('person', {'name like': 'Mosky%', 'age >': 20})
    SELECT * FROM "person" WHERE "age" > 20 AND "name" LIKE 'Mosky%'

    If you want to use the functions, wrap it with :class:`mosql.util.raw`:

    >>> print select('person', select=raw('count(*)'), group_by=('age', ))
    SELECT count(*) FROM "person" GROUP BY "age"

    .. seealso ::<commit_msg>added the doctest<commit_after>#!/usr/bin/env python
# -*- coding: utf-8 -*-

'''It contains the common SQL builders.

.. autosummary ::
    insert
    select
    update
    delete

It is designed for building the standard SQL statement (or PostgreSQL).

.. note::
    If you use MySQL, MoSQL provides a patch for MySQL --- :mod:`mosql.mysql`.
'''

from .util2 import *

# defines formatting chains
single_value      = (value, )
single_identifier = (identifier, )
identifier_list   = (identifier, concat_by_comma)
where_list        = (build_where, )
set_list          = (build_set, )
statement_list    = (concat_by_space, )

# insert

insert  = Clause('insert into', single_identifier)
columns = Clause('columns'    , (identifier, concat_by_comma, paren), hidden=True)
values  = Clause('values'     , (value, concat_by_comma, paren))
returning = Clause('returning'  , identifier_list)

insert_into_stat = Statement([insert, columns, values, returning])

def insert(table, pairs_or_columns=None, values=None, **clauses_args):
    '''It generates the SQL statement, ``insert into ...``.

    The following usages generate the same SQL statement:

    >>> print insert('person', {'person_id': 'mosky', 'name': '<NAME>'})
    INSERT INTO "person" ("person_id", "name") VALUES ('mosky', '<NAME>')

    >>> print insert('person', (('person_id', 'mosky'), ('name', '<NAME>')))
    INSERT INTO "person" ("person_id", "name") VALUES ('mosky', '<NAME>')

    >>> print insert('person', ('person_id', 'name'), ('mosky', '<NAME>'))
    INSERT INTO "person" ("person_id", "name") VALUES ('mosky', '<NAME>')

    or you can ignore the columns:

    >>> print insert('person', values=('mosky', '<NAME>'))
    INSERT INTO "person" VALUES ('mosky', '<NAME>')

    The :func:`update` and :func:`delete` and it are also supported ``returning``.

    >>> print insert('person', {'person_id': 'mosky', 'name': '<NAME>'}, returning=raw('*'))
    INSERT INTO "person" ("person_id", "name") VALUES ('mosky', '<NAME>') RETURNING *
    '''

    clauses_args['insert into'] = table

    if values is None:
        if hasattr(pairs_or_columns, 'items'):
            pairs = pairs_or_columns.items()
        else:
            pairs = pairs_or_columns
        clauses_args['columns'], clauses_args['values'] = zip(*pairs)
    else:
        clauses_args['columns'] = pairs_or_columns
        clauses_args['values']  = values

    return insert_into_stat.format(clauses_args)

# select

select   = Clause('select'  , identifier_list)
from_    = Clause('from'    , identifier_list)
joins    = Clause('joins'   , statement_list, hidden=True)
where    = Clause('where'   , where_list)
group_by = Clause('group by', identifier_list)
having   = Clause('having'  , where_list)
order_by = Clause('order by', identifier_list)
limit    = Clause('limit'   , single_value)
offset   = Clause('offset'  , single_value)

select_stat = Statement([select, from_, joins, where, group_by, having, order_by, limit, offset])

def select(table, where=None, select=raw('*'), **clauses_args):
    '''It generates the SQL statement, ``select ...`` .

    The following usages generate the same SQL statement.

    >>> print select('person', {'person_id': 'mosky'})
    SELECT * FROM "person" WHERE "person_id" = 'mosky'

    >>> print select('person', (('person_id', 'mosky'), ))
    SELECT * FROM "person" WHERE "person_id" = 'mosky'

    It detects the dot in an identifier:

    >>> print select('person', select=('person.person_id', 'person.name'))
    SELECT "person"."person_id", "person"."name" FROM "person"

    Building prepare statement with :class:`mosql.util.param`:

    >>> print select('table', {'custom_param': param('my_param'), 'auto_param': param, 'using_alias': ___})
    SELECT * FROM "table" WHERE "auto_param" = %(auto_param)s AND "using_alias" = %(using_alias)s AND "custom_param" = %(my_param)s

    You can also specify the ``group_by``, ``having``, ``order_by``, ``limit``
    and ``offset`` in the keyword arguments. Here are some examples:

    >>> print select('person', {'name like': 'Mosky%'}, group_by=('age', ))
    SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' GROUP BY "age"

    >>> print select('person', {'name like': 'Mosky%'}, order_by=('age', ))
    SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' ORDER BY "age"

    >>> print select('person', {'name like': 'Mosky%'}, limit=3, offset=1)
    SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' LIMIT 3 OFFSET 1

    The operators are also supported:

    >>> print select('person', {'person_id': ('andy', 'bob')})
    SELECT * FROM "person" WHERE "person_id" IN ('andy', 'bob')

    >>> print select('person', {'name': None})
    SELECT * FROM "person" WHERE "name" IS NULL

    >>> print select('person', {'name like': 'Mosky%', 'age >': 20})
    SELECT * FROM "person" WHERE "age" > 20 AND "name" LIKE 'Mosky%'

    >>> print select('person', {"person_id = '' OR true; --": 'mosky'})
    Traceback (most recent call last):
        ...
    AssertionError: the operator is not allowed: "= '' OR TRUE; --"

    If you want to use the functions, wrap it with :class:`mosql.util.raw`:

    >>> print select('person', select=raw('count(*)'), group_by=('age', ))
    SELECT count(*) FROM "person" GROUP BY "age"

    .. seealso ::
{'fraction_non_alphanumeric': 0.17436250861474845, 'fraction_numerical': 0.0019690853598503494, 'mean_word_length': 4.011346817957572, 'pattern_counts': {'":': 1, '<': 23, '<?xml version=': 0, '>': 123, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '1497992', 'n_tokens_mistral': 3360, 'n_tokens_neox': 3159, 'n_words': 941}