Repository: kayak/pypika
Pull request #753 (2023-09-21 07:18:43)

feat: Support creating/dropping indices on tables
- Added support for creating/dropping indices (tested against SQLite and PG)

AzisK: Could you also add this possibility in the documentation in the README?

Patch:
diff --git a/.github/workflows/black.yml b/.github/workflows/black.yml
deleted file mode 100644
index c386fce..0000000
--- a/.github/workflows/black.yml
+++ /dev/null
@@ -1,12 +0,0 @@
----
-name: Lint
-
-on: [push, pull_request]
-
-jobs:
-  lint:
-    runs-on: ubuntu-latest
-    steps:
-      - uses: actions/checkout@v2
-      - uses: actions/setup-python@v2
-      - uses: psf/black@21.5b1
diff --git a/.github/workflows/pythonpackage.yml b/.github/workflows/ci.yml
similarity index 51%
rename from .github/workflows/pythonpackage.yml
rename to .github/workflows/ci.yml
index 8665b42..cce2084 100644
--- a/.github/workflows/pythonpackage.yml
+++ b/.github/workflows/ci.yml
@@ -1,32 +1,36 @@
-name: Unit Tests
+name: CI
 
 on:
   push:
-    branches: ["master"]
   pull_request:
-    branches: ["master"]
+  workflow_dispatch:
 
 jobs:
-  test:
+  lint:
+    name: Lint
+    runs-on: ubuntu-latest
+    steps:
+      - uses: actions/checkout@v4
+      - uses: actions/setup-python@v4
+      - uses: pre-commit/action@v3.0.0
 
+  test:
+    name: Unit Tests
     runs-on: ubuntu-latest
     strategy:
-      max-parallel: 4
+      max-parallel: 5
       matrix:
-        python-version: [3.6, 3.7, 3.8, 3.9]
+        python-version: ["3.8", "3.9", "3.10", "3.11"]
 
     steps:
-      - uses: actions/checkout@v1
+      - uses: actions/checkout@v4
       - name: Set up Python ${{ matrix.python-version }}
-        uses: actions/setup-python@v1
+        uses: actions/setup-python@v4
         with:
           python-version: ${{ matrix.python-version }}
 
       - name: Install dependencies
-        run: |
-          python -m pip install --upgrade pip
-          pip install -r requirements-dev.txt
-          pip install "coveralls<3.0.0"
+        run: python -m pip install -U pip -r requirements-dev.txt "coveralls<3.0.0"
 
       - name: Run test suite
         env:
diff --git a/.pre-commit-config.yaml b/.pre-commit-config.yaml
new file mode 100644
index 0000000..3232a4d
--- /dev/null
+++ b/.pre-commit-config.yaml
@@ -0,0 +1,25 @@
+exclude: '.git'
+default_stages: [commit]
+fail_fast: false
+
+repos:
+  - repo: https://github.com/pre-commit/pre-commit-hooks
+    rev: v4.4.0
+    hooks:
+      - id: trailing-whitespace
+        files: "pypika.*"
+        exclude: ".*json$|.*txt$|.*csv|.*md|.*svg"
+      - id: check-yaml
+      - id: no-commit-to-branch
+        args: ['--branch', 'master']
+      - id: check-merge-conflict
+      - id: check-ast
+      - id: check-json
+      - id: check-toml
+      - id: check-yaml
+      - id: debug-statements
+
+  - repo: https://github.com/psf/black
+    rev: 23.9.1
+    hooks:
+      - id: black
diff --git a/README.rst b/README.rst
index 3d90bf9..534d76d 100644
--- a/README.rst
+++ b/README.rst
@@ -510,7 +510,7 @@ Example of a correlated subquery in the `SELECT`
 Unions
 """"""
 
-Both ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonomous with "UNION`` so |Brand| does not
+Both ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonymous with "UNION`` so |Brand| does not
 provide a separate function for it.  Unions require that queries have the same number of ``SELECT`` clauses so
 trying to cast a unioned query to string will throw a ``SetOperationException`` if the column sizes are mismatched.
 
@@ -1274,6 +1274,100 @@ This produces:
 
         CREATE TABLE "names" AS (SELECT "last_name","first_name" FROM "person")
 
+Managing Table Indices
+^^^^^^^^^^^^^^^^^^^^^^
+
+Create Indices
+""""""""""""""""
+
+The entry point for creating indices is ``pypika.Query.create_index``.
+An index name (as ``str``) or a ``pypika.terms.Index`` a table (as ``str`` or ``pypika.Table``) and
+columns (as ``pypika.Column``) must be specified.
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    person = Table("person")
+    stmt = Query \
+        .create_index(my_index) \
+        .on(person) \
+        .columns(person.first_name, person.last_name)
+
+This produces:
+
+.. code-block:: sql
+
+    CREATE INDEX my_index
+    ON person (first_name, last_name)
+
+It is also possible to create a unique index
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    person = Table("person")
+    stmt = Query \
+        .create_index(my_index) \
+        .on(person) \
+        .columns(person.first_name, person.last_name) \
+        .unique()
+
+This produces:
+
+.. code-block:: sql
+
+        CREATE UNIQUE INDEX my_index
+        ON person (first_name, last_name)
+
+It is also possible to create an index if it does not exist
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    person = Table("person")
+    stmt = Query \
+        .create_index(my_index) \
+        .on(person) \
+        .columns(person.first_name, person.last_name) \
+        .if_not_exists()
+
+This produces:
+
+.. code-block:: sql
+
+        CREATE INDEX IF NOT EXISTS my_index
+        ON person (first_name, last_name)
+
+Drop Indices
+""""""""""""""""
+
+Then entry point for dropping indices is ``pypika.Query.drop_index``.
+It takes either ``str`` or ``pypika.terms.Index`` as an argument.
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    stmt = Query.drop_index(my_index)
+
+This produces:
+
+.. code-block:: sql
+
+    DROP INDEX my_index
+
+It is also possible to drop an index if it exists
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    stmt = Query.drop_index(my_index).if_exists()
+
+This produces:
+
+.. code-block:: sql
+
+    DROP INDEX IF EXISTS my_index
+
 .. _tutorial_end:
 
 
diff --git a/pypika/enums.py b/pypika/enums.py
index 751889c..82febc3 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -139,7 +139,7 @@ class Dialects(Enum):
     ORACLE = "oracle"
     MSSQL = "mssql"
     MYSQL = "mysql"
-    POSTGRESQL = "postgressql"
+    POSTGRESQL = "postgresql"
     REDSHIFT = "redshift"
     SQLLITE = "sqllite"
     SNOWFLAKE = "snowflake"
diff --git a/pypika/queries.py b/pypika/queries.py
index 2adc1b1..76eeea2 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -1,11 +1,10 @@
 from copy import copy
 from functools import reduce
-from typing import Any, List, Optional, Sequence, Tuple as TypedTuple, Type, Union, Set
+from typing import Any, List, Optional, Sequence, Tuple as TypedTuple, Type, Union
 
 from pypika.enums import Dialects, JoinType, ReferenceOption, SetOperation
 from pypika.terms import (
     ArithmeticExpression,
-    Criterion,
     EmptyCriterion,
     Field,
     Function,
@@ -384,6 +383,14 @@ class Query:
         """
         return CreateQueryBuilder().create_table(table)
 
+    @classmethod
+    def create_index(cls, index: Union[str, Index]) -> "CreateIndexBuilder":
+        """
+        Query builder entry point. Initializes query building and sets the index name to be created. When using this
+        function, the query becomes a CREATE statement.
+        """
+        return CreateIndexBuilder().create_index(index)
+
     @classmethod
     def drop_database(cls, database: Union[Database, Table]) -> "DropQueryBuilder":
         """
@@ -432,6 +439,14 @@ class Query:
         """
         return DropQueryBuilder().drop_view(view)
 
+    @classmethod
+    def drop_index(cls, index: Union[str, Index]) -> "DropQueryBuilder":
+        """
+        Query builder entry point. Initializes query building and sets the index name to be dropped. When using this
+        function, the query becomes a DROP statement.
+        """
+        return DropQueryBuilder().drop_index(index)
+
     @classmethod
     def into(cls, table: Union[Table, str], **kwargs: Any) -> "QueryBuilder":
         """
@@ -1564,8 +1579,8 @@ class Joiner:
 
         criterion = None
         for field in fields:
-            consituent = Field(field, table=self.query._from[0]) == Field(field, table=self.item)
-            criterion = consituent if criterion is None else criterion & consituent
+            constituent = Field(field, table=self.query._from[0]) == Field(field, table=self.item)
+            criterion = constituent if criterion is None else criterion & constituent
 
         self.query.do_join(JoinOn(self.item, self.how, criterion))
         return self.query
@@ -2042,6 +2057,70 @@ class CreateQueryBuilder:
         return self.__str__()
 
 
+class CreateIndexBuilder:
+    def __init__(self) -> None:
+        self._index = None
+        self._columns = []
+        self._table = None
+        self._wheres = None
+        self._is_unique = False
+        self._if_not_exists = False
+
+    @builder
+    def create_index(self, index: Union[str, Index]) -> "CreateIndexBuilder":
+        self._index = index
+
+    @builder
+    def columns(self, *columns: Union[str, TypedTuple[str, str], Column]) -> "CreateIndexBuilder":
+        for column in columns:
+            if isinstance(column, str):
+                column = Column(column)
+            elif isinstance(column, tuple):
+                column = Column(column_name=column[0], column_type=column[1])
+            self._columns.append(column)
+
+    @builder
+    def on(self, table: Union[Table, str]) -> "CreateIndexBuilder":
+        self._table = table
+
+    @builder
+    def where(self, criterion: Union[Term, EmptyCriterion]) -> "CreateIndexBuilder":
+        """
+        Partial index where clause.
+        """
+        if self._wheres:
+            self._wheres &= criterion
+        else:
+            self._wheres = criterion
+
+    @builder
+    def unique(self) -> "CreateIndexBuilder":
+        self._is_unique = True
+
+    @builder
+    def if_not_exists(self) -> "CreateIndexBuilder":
+        self._if_not_exists = True
+
+    def get_sql(self) -> str:
+        if not self._columns or len(self._columns) == 0:
+            raise AttributeError("Cannot create index without columns")
+        if not self._table:
+            raise AttributeError("Cannot create index without table")
+        columns_str = ", ".join([c.name for c in self._columns])
+        unique_str = "UNIQUE" if self._is_unique else ""
+        if_not_exists_str = "IF NOT EXISTS" if self._if_not_exists else ""
+        base_sql = f"CREATE {unique_str} INDEX {if_not_exists_str} {self._index} ON {self._table}({columns_str})"
+        if self._wheres:
+            base_sql += f" WHERE {self._wheres}"
+        return base_sql.replace("  ", " ")
+
+    def __str__(self) -> str:
+        return self.get_sql()
+
+    def __repr__(self) -> str:
+        return self.__str__()
+
+
 class DropQueryBuilder:
     """
     Query builder used to build DROP queries.
@@ -2081,6 +2160,10 @@ class DropQueryBuilder:
     def drop_view(self, view: str) -> "DropQueryBuilder":
         self._set_target('VIEW', view)
 
+    @builder
+    def drop_index(self, index: str) -> "DropQueryBuilder":
+        self._set_target('INDEX', index)
+
     @builder
     def if_exists(self) -> "DropQueryBuilder":
         self._if_exists = True
diff --git a/pypika/terms.py b/pypika/terms.py
index c522550..2139ea6 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -486,12 +486,18 @@ class SystemTimeValue(LiteralValue):
 
 class Criterion(Term):
     def __and__(self, other: Any) -> "ComplexCriterion":
+        if isinstance(other, EmptyCriterion):
+            return self
         return ComplexCriterion(Boolean.and_, self, other)
 
     def __or__(self, other: Any) -> "ComplexCriterion":
+        if isinstance(other, EmptyCriterion):
+            return self
         return ComplexCriterion(Boolean.or_, self, other)
 
     def __xor__(self, other: Any) -> "ComplexCriterion":
+        if isinstance(other, EmptyCriterion):
+            return self
         return ComplexCriterion(Boolean.xor_, self, other)
 
     @staticmethod
@@ -532,6 +538,9 @@ class EmptyCriterion(Criterion):
     def __xor__(self, other: Any) -> Any:
         return other
 
+    def __invert__(self) -> Any:
+        return self
+
 
 class Field(Criterion, JSON):
     def __init__(
diff --git a/pypika/utils.py b/pypika/utils.py
index 1506704..ca3e9c4 100644
--- a/pypika/utils.py
+++ b/pypika/utils.py
@@ -1,4 +1,4 @@
-from typing import Any, Callable, List, Optional, Type
+from typing import Any, Callable, List, Optional, Type, TypeVar
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -36,7 +36,10 @@ class FunctionException(Exception):
     pass
 
 
-def builder(func: Callable) -> Callable:
+C = TypeVar("C")
+
+
+def builder(func: C) -> C:
     """
     Decorator for wrapper "builder" functions.  These are functions on the Query class or other classes used for
     building queries which mutate the query and return self.  To make the build functions immutable, this decorator is
diff --git a/pyproject.toml b/pyproject.toml
index 5f182ce..dcc56ba 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -1,8 +1,8 @@
 [tool.black]
 line-length = 120
 skip-string-normalization = true
-target-version = ['py36', 'py37', 'py38']
-exclude = '''
+target-version = ['py38', 'py39', 'py310', 'py311']
+force-exclude = '''
 (
   /(
       \.eggs         # exclude a few common directories in the
diff --git a/requirements-dev.txt b/requirements-dev.txt
index def44f9..3926afa 100644
--- a/requirements-dev.txt
+++ b/requirements-dev.txt
@@ -9,6 +9,3 @@ tox==3.14.3
 tox-venv==0.4.0
 tox-gh-actions==0.3.0
 coverage==5.1
-
-# Formatting
-black==21.5b1
diff --git a/setup.cfg b/setup.cfg
index be10a91..46084aa 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -13,3 +13,4 @@ universal = 1
 [flake8]
 exclude = docs, .git, __pycache__
 max-line-length = 120
+per-file-ignores = __init__.py:F401
diff --git a/tox.ini b/tox.ini
index cd8eb1d..2f0e689 100644
--- a/tox.ini
+++ b/tox.ini
@@ -1,13 +1,16 @@
 [tox]
-envlist = py36,py37,py38,py39,pypy3
+envlist = py38,py39,py310,py311,pypy3
+
 [testenv]
+description = testing against {basepython}
 deps = -r requirements-dev.txt
 commands =
     coverage run
     coverage xml
+
 [gh-actions]
 python =
-    3.6: py36
-    3.7: py37
     3.8: py38
     3.9: py39
+    3.10: py310
+    3.11: py311


Test patch:
diff --git a/pypika/tests/test_create.py b/pypika/tests/test_create.py
index e081a44..3250765 100644
--- a/pypika/tests/test_create.py
+++ b/pypika/tests/test_create.py
@@ -1,7 +1,7 @@
 import unittest
 
-from pypika import Column, Columns, Query, Tables
-from pypika.terms import ValueWrapper
+from pypika import Column, Columns, Query, Tables, Table
+from pypika.terms import ValueWrapper, Index
 from pypika.enums import ReferenceOption
 
 
@@ -165,3 +165,46 @@ class CreateTableTests(unittest.TestCase):
     def test_create_table_as_select_not_query_raises_error(self):
         with self.assertRaises(TypeError):
             Query.create_table(self.new_table).as_select("abc")
+
+
+class CreateIndexTests(unittest.TestCase):
+    new_index = Index("abc")
+    new_table = Table("my_table")
+    columns = (new_table.a, new_table.b)
+    where = new_table.a > 1
+
+    def test_create_index_simple(self) -> None:
+        q = Query.create_index(self.new_index).on(self.new_table).columns(*self.columns)
+        self.assertEqual(
+            f'CREATE INDEX "{self.new_index.name}" ON "{self.new_table.get_table_name()}"({", ".join([c.name for c in self.columns])})',
+            q.get_sql(),
+        )
+
+    def test_create_index_where(self) -> None:
+        q = Query.create_index(self.new_index).on(self.new_table).columns(*self.columns).where(self.where)
+        self.assertEqual(
+            f'CREATE INDEX "{self.new_index.name}" ON "{self.new_table.get_table_name()}"({", ".join([c.name for c in self.columns])}) WHERE {self.where.get_sql()}',
+            q.get_sql(),
+        )
+
+    def test_create_index_unique(self) -> None:
+        q = Query.create_index(self.new_index).on(self.new_table).columns(*self.columns).unique()
+        self.assertEqual(
+            f'CREATE UNIQUE INDEX "{self.new_index.name}" ON "{self.new_table.get_table_name()}"({", ".join([c.name for c in self.columns])})',
+            q.get_sql(),
+        )
+
+    def test_create_index_if_not_exists(self) -> None:
+        q = Query.create_index(self.new_index).on(self.new_table).columns(*self.columns).if_not_exists()
+        self.assertEqual(
+            f'CREATE INDEX IF NOT EXISTS "{self.new_index.name}" ON "{self.new_table.get_table_name()}"({", ".join([c.name for c in self.columns])})',
+            q.get_sql(),
+        )
+
+    def test_create_index_without_columns_raises_error(self) -> None:
+        with self.assertRaises(AttributeError):
+            Query.create_index(self.new_index).on(self.new_table).get_sql()
+
+    def test_create_index_without_table_raises_error(self) -> None:
+        with self.assertRaises(AttributeError):
+            Query.create_index(self.new_index).columns(*self.columns).get_sql()
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 2ecaa13..37b94e8 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -791,6 +791,62 @@ class EmptyCriterionTests(unittest.TestCase):
 
         self.assertEqual(len(empty_criterion.fields_()), 0)
 
+    def test_empty_criterion_on_the_left(self):
+        t = Table("test1")
+        q = EmptyCriterion() & t.f1 == "v1"
+
+        self.assertEqual(
+            '"test1"."f1"=\'v1\'',
+            q.get_sql(with_namespace=True),
+        )
+
+    def test_empty_criterion_on_the_right(self):
+        t = Table("test1")
+        q = (t.f1 == "v1") & EmptyCriterion()
+
+        self.assertEqual(
+            '"test1"."f1"=\'v1\'',
+            q.get_sql(with_namespace=True),
+        )
+
+    def test_invertion_of_the_empty_criterion(self):
+        t = Table("test1")
+        q = (t.f1 == "v1") & ~EmptyCriterion()
+
+        self.assertEqual(
+            '"test1"."f1"=\'v1\'',
+            q.get_sql(with_namespace=True),
+        )
+
+    def test_different_operations_with_empty_criterion(self):
+        t = Table("test1")
+        q1 = (t.f1 == "v1") & EmptyCriterion()
+        q2 = (t.f1 == "v1") | EmptyCriterion()
+        q3 = (t.f1 == "v1") ^ EmptyCriterion()
+
+        expected_sql = '"test1"."f1"=\'v1\''
+        self.assertEqual(
+            expected_sql,
+            q1.get_sql(with_namespace=True),
+        )
+        self.assertEqual(
+            expected_sql,
+            q2.get_sql(with_namespace=True),
+        )
+        self.assertEqual(
+            expected_sql,
+            q3.get_sql(with_namespace=True),
+        )
+
+    def test_more_than_one_empty_criterions(self):
+        t = Table("test1")
+        q = EmptyCriterion() & ~(EmptyCriterion() | EmptyCriterion()) & (t.f1 == "v1") & ~EmptyCriterion()
+
+        self.assertEqual(
+            '"test1"."f1"=\'v1\'',
+            q.get_sql(with_namespace=True),
+        )
+
 
 class AllTests(unittest.TestCase):
     def test_zero_args_returns_empty_criterion(self):
diff --git a/pypika/tests/test_drop.py b/pypika/tests/test_drop.py
index ef5b132..265ae73 100644
--- a/pypika/tests/test_drop.py
+++ b/pypika/tests/test_drop.py
@@ -40,3 +40,10 @@ class DropTableTests(unittest.TestCase):
 
         self.assertEqual('DROP VIEW "myview"', str(q1))
         self.assertEqual('DROP VIEW IF EXISTS "myview"', str(q2))
+
+    def test_drop_index(self):
+        q1 = Query.drop_index("myindex")
+        q2 = Query.drop_index("myindex").if_exists()
+
+        self.assertEqual('DROP INDEX "myindex"', str(q1))
+        self.assertEqual('DROP INDEX IF EXISTS "myindex"', str(q2))