Repository: kayak/pypika
Pull request #607 (2021-06-17 22:59:37)

enhance DROP statement
Added:
DROP DATABASE
DROP USER
DROP VIEW
DROP DICTIONARY (clickhouse only)
DROP QUOTA (clickhouse only)

https://dev.mysql.com/doc/refman/8.0/en/drop-database.html
https://dev.mysql.com/doc/refman/8.0/en/drop-view.html
https://dev.mysql.com/doc/refman/8.0/en/drop-user.html
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9008.htm
https://clickhouse.tech/docs/en/sql-reference/statements/drop/

coveralls: 
[![Coverage Status](https://coveralls.io/builds/40686400/badge)](https://coveralls.io/builds/40686400)

Coverage decreased (-0.3%) to 98.05% when pulling **e534661dde6e881672637e3030087ba3ce66a706 on gleb-kov:drop-support** into **40d78852a31a4d5f5eeef8859b907a7a0d5e0129 on kayak:master**.


Patch:
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 4f42465..a855123 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -5,17 +5,15 @@ from typing import Any, Optional, Union
 from pypika.enums import Dialects
 from pypika.queries import (
     CreateQueryBuilder,
-    Query,
-    QueryBuilder,
+    Database,
+    DropQueryBuilder,
     Selectable,
     Table,
-    DropQueryBuilder,
+    Query,
+    QueryBuilder,
 )
 from pypika.terms import ArithmeticExpression, Criterion, EmptyCriterion, Field, Function, Star, Term, ValueWrapper
-from pypika.utils import (
-    QueryException,
-    builder,
-)
+from pypika.utils import QueryException, builder, format_quotes
 
 
 class SnowflakeQuery(Query):
@@ -685,6 +683,30 @@ class ClickHouseQuery(Query):
             dialect=Dialects.CLICKHOUSE, wrap_set_operation_queries=False, as_keyword=True, **kwargs
         )
 
+    @classmethod
+    def drop_database(self, database: Union[Database, str]) -> "ClickHouseDropQueryBuilder":
+        return ClickHouseDropQueryBuilder().drop_database(database)
+
+    @classmethod
+    def drop_table(self, table: Union[Table, str]) -> "ClickHouseDropQueryBuilder":
+        return ClickHouseDropQueryBuilder().drop_table(table)
+
+    @classmethod
+    def drop_dictionary(self, dictionary: str) -> "ClickHouseDropQueryBuilder":
+        return ClickHouseDropQueryBuilder().drop_dictionary(dictionary)
+
+    @classmethod
+    def drop_quota(self, quota: str) -> "ClickHouseDropQueryBuilder":
+        return ClickHouseDropQueryBuilder().drop_quota(quota)
+
+    @classmethod
+    def drop_user(self, user: str) -> "ClickHouseDropQueryBuilder":
+        return ClickHouseDropQueryBuilder().drop_user(user)
+
+    @classmethod
+    def drop_view(self, view: str) -> "ClickHouseDropQueryBuilder":
+        return ClickHouseDropQueryBuilder().drop_view(view)
+
 
 class ClickHouseQueryBuilder(QueryBuilder):
     QUERY_CLS = ClickHouseQuery
@@ -713,6 +735,36 @@ class ClickHouseQueryBuilder(QueryBuilder):
         )
 
 
+class ClickHouseDropQueryBuilder(DropQueryBuilder):
+    QUERY_CLS = ClickHouseQuery
+
+    def __init__(self):
+        super().__init__(dialect=Dialects.CLICKHOUSE)
+        self._cluster_name = None
+
+    @builder
+    def drop_dictionary(self, dictionary: str) -> "ClickHouseDropQueryBuilder":
+        super()._set_target('DICTIONARY', dictionary)
+
+    @builder
+    def drop_quota(self, quota: str) -> "ClickHouseDropQueryBuilder":
+        super()._set_target('QUOTA', quota)
+
+    @builder
+    def on_cluster(self, cluster: str) -> "ClickHouseDropQueryBuilder":
+        if self._cluster_name:
+            raise AttributeError("'DropQuery' object already has attribute cluster_name")
+        self._cluster_name = cluster
+
+    def get_sql(self, **kwargs: Any) -> str:
+        query = super().get_sql(**kwargs)
+
+        if self._drop_target_kind != "DICTIONARY" and self._cluster_name is not None:
+            query += " ON CLUSTER " + format_quotes(self._cluster_name, super().QUOTE_CHAR)
+
+        return query
+
+
 class SQLLiteValueWrapper(ValueWrapper):
     def get_value_sql(self, **kwargs: Any) -> str:
         if isinstance(self.value, bool):
diff --git a/pypika/queries.py b/pypika/queries.py
index 922cecb..b5165c2 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -384,6 +384,18 @@ class Query:
         """
         return CreateQueryBuilder().create_table(table)
 
+    @classmethod
+    def drop_database(cls, database: Union[Database, Table]) -> "DropQueryBuilder":
+        """
+        Query builder entry point. Initializes query building and sets the table name to be dropped. When using this
+        function, the query becomes a DROP statement.
+
+        :param database: An instance of a Database object or a string database name.
+
+        :return: DropQueryBuilder
+        """
+        return DropQueryBuilder().drop_database(database)
+
     @classmethod
     def drop_table(cls, table: Union[str, Table]) -> "DropQueryBuilder":
         """
@@ -396,6 +408,30 @@ class Query:
         """
         return DropQueryBuilder().drop_table(table)
 
+    @classmethod
+    def drop_user(cls, user: str) -> "DropQueryBuilder":
+        """
+        Query builder entry point. Initializes query building and sets the table name to be dropped. When using this
+        function, the query becomes a DROP statement.
+
+        :param user: String user name.
+
+        :return: DropQueryBuilder
+        """
+        return DropQueryBuilder().drop_user(user)
+
+    @classmethod
+    def drop_view(cls, view: str) -> "DropQueryBuilder":
+        """
+        Query builder entry point. Initializes query building and sets the table name to be dropped. When using this
+        function, the query becomes a DROP statement.
+
+        :param view: String view name.
+
+        :return: DropQueryBuilder
+        """
+        return DropQueryBuilder().drop_view(view)
+
     @classmethod
     def into(cls, table: Union[Table, str], **kwargs: Any) -> "QueryBuilder":
         """
@@ -1938,7 +1974,8 @@ class DropQueryBuilder:
     QUERY_CLS = Query
 
     def __init__(self, dialect: Optional[Dialects] = None) -> None:
-        self._drop_table = None
+        self._drop_target_kind = None
+        self._drop_target: Union[Database, Table, str] = ""
         self._if_exists = None
         self.dialect = dialect
 
@@ -1947,35 +1984,49 @@ class DropQueryBuilder:
         kwargs.setdefault("secondary_quote_char", self.SECONDARY_QUOTE_CHAR)
         kwargs.setdefault("dialect", self.dialect)
 
-    def get_sql(self, **kwargs: Any) -> str:
-        self._set_kwargs_defaults(kwargs)
-
-        if not self._drop_table:
-            return ""
-
-        querystring = self._drop_table_sql(**kwargs)
-
-        return querystring
+    @builder
+    def drop_database(self, database: Union[Database, str]) -> "DropQueryBuilder":
+        target = database if isinstance(database, Database) else Database(database)
+        self._set_target('DATABASE', target)
 
     @builder
     def drop_table(self, table: Union[Table, str]) -> "DropQueryBuilder":
-        if self._drop_table:
-            raise AttributeError("'Query' object already has attribute drop_table")
+        target = table if isinstance(table, Table) else Table(table)
+        self._set_target('TABLE', target)
+
+    @builder
+    def drop_user(self, user: str) -> "DropQueryBuilder":
+        self._set_target('USER', user)
 
-        self._drop_table = table if isinstance(table, Table) else Table(table)
+    @builder
+    def drop_view(self, view: str) -> "DropQueryBuilder":
+        self._set_target('VIEW', view)
 
     @builder
     def if_exists(self) -> "DropQueryBuilder":
         self._if_exists = True
 
-    def _drop_table_sql(self, **kwargs: Any) -> str:
-        if_exists = ''
-        if self._if_exists:
-            if_exists = 'IF EXISTS '
+    def _set_target(self, kind: str, target: Union[Database, Table, str]) -> None:
+        if self._drop_target:
+            raise AttributeError("'DropQuery' object already has attribute drop_target")
+        self._drop_target_kind = kind
+        self._drop_target = target
+
+    def get_sql(self, **kwargs: Any) -> str:
+        self._set_kwargs_defaults(kwargs)
+
+        if_exists = 'IF EXISTS ' if self._if_exists else ''
+        target_name: str = ""
+
+        if isinstance(self._drop_target, Database):
+            target_name = self._drop_target.get_sql(**kwargs)
+        elif isinstance(self._drop_target, Table):
+            target_name = self._drop_target.get_sql(**kwargs)
+        else:
+            target_name = format_quotes(self._drop_target, self.QUOTE_CHAR)
 
-        return "DROP TABLE {if_exists}{table}".format(
-            if_exists=if_exists,
-            table=self._drop_table.get_sql(**kwargs),
+        return "DROP {kind} {if_exists}{name}".format(
+            kind=self._drop_target_kind, if_exists=if_exists, name=target_name
         )
 
     def __str__(self) -> str:


Test patch:
diff --git a/pypika/tests/dialects/test_clickhouse.py b/pypika/tests/dialects/test_clickhouse.py
index 477af9a..1a8b8b8 100644
--- a/pypika/tests/dialects/test_clickhouse.py
+++ b/pypika/tests/dialects/test_clickhouse.py
@@ -2,6 +2,7 @@ from unittest import TestCase
 
 from pypika import (
     ClickHouseQuery,
+    Database,
     Table,
 )
 
@@ -41,3 +42,45 @@ class ClickHouseUpdateTests(TestCase):
         q = ClickHouseQuery.update(self.table_abc).where(self.table_abc.foo == 0).set("foo", "bar")
 
         self.assertEqual('ALTER TABLE "abc" UPDATE "foo"=\'bar\' WHERE "foo"=0', str(q))
+
+
+class ClickHouseDropQuery(TestCase):
+    table_abc = Table("abc")
+    database_xyz = Database("mydb")
+    cluster_name = "mycluster"
+
+    def test_drop_database(self):
+        q1 = ClickHouseQuery.drop_database(self.database_xyz)
+        q2 = ClickHouseQuery.drop_database(self.database_xyz).on_cluster(self.cluster_name)
+        q3 = ClickHouseQuery.drop_database(self.database_xyz).if_exists().on_cluster(self.cluster_name)
+
+        self.assertEqual('DROP DATABASE "mydb"', str(q1))
+        self.assertEqual('DROP DATABASE "mydb" ON CLUSTER "mycluster"', str(q2))
+        self.assertEqual('DROP DATABASE IF EXISTS "mydb" ON CLUSTER "mycluster"', str(q3))
+
+    def test_drop_table(self):
+        q1 = ClickHouseQuery.drop_table(self.table_abc)
+        q2 = ClickHouseQuery.drop_table(self.table_abc).on_cluster(self.cluster_name)
+        q3 = ClickHouseQuery.drop_table(self.table_abc).if_exists().on_cluster(self.cluster_name)
+
+        self.assertEqual('DROP TABLE "abc"', str(q1))
+        self.assertEqual('DROP TABLE "abc" ON CLUSTER "mycluster"', str(q2))
+        self.assertEqual('DROP TABLE IF EXISTS "abc" ON CLUSTER "mycluster"', str(q3))
+
+    def test_drop_dictionary(self):
+        q1 = ClickHouseQuery.drop_dictionary("dict")
+        q2 = ClickHouseQuery.drop_dictionary("dict").on_cluster(self.cluster_name)
+        q3 = ClickHouseQuery.drop_dictionary("dict").if_exists().on_cluster(self.cluster_name)
+
+        self.assertEqual('DROP DICTIONARY "dict"', str(q1))
+        self.assertEqual('DROP DICTIONARY "dict"', str(q2))  # NO CLUSTER
+        self.assertEqual('DROP DICTIONARY IF EXISTS "dict"', str(q3))  # NO CLUSTER
+
+    def test_drop_other(self):
+        q1 = ClickHouseQuery.drop_quota("myquota")
+        q2 = ClickHouseQuery.drop_user("myuser")
+        q3 = ClickHouseQuery.drop_view("myview")
+
+        self.assertEqual('DROP QUOTA "myquota"', str(q1))
+        self.assertEqual('DROP USER "myuser"', str(q2))
+        self.assertEqual('DROP VIEW "myview"', str(q3))
diff --git a/pypika/tests/test_drop.py b/pypika/tests/test_drop.py
index e887359..ef5b132 100644
--- a/pypika/tests/test_drop.py
+++ b/pypika/tests/test_drop.py
@@ -1,22 +1,42 @@
 import unittest
 
 from pypika import (
-    Tables,
     Columns,
+    Database,
     Query,
+    Tables,
 )
 
 
 class DropTableTests(unittest.TestCase):
+    database_xyz = Database("mydb")
     new_table, existing_table = Tables("abc", "efg")
     foo, bar = Columns(("a", "INT"), ("b", "VARCHAR(100)"))
 
+    def test_drop_database(self):
+        q1 = Query.drop_database(self.database_xyz)
+        q2 = Query.drop_database(self.database_xyz).if_exists()
+
+        self.assertEqual('DROP DATABASE "mydb"', str(q1))
+        self.assertEqual('DROP DATABASE IF EXISTS "mydb"', str(q2))
+
     def test_drop_table(self):
-        q = Query.drop_table(self.new_table)
+        q1 = Query.drop_table(self.new_table)
+        q2 = Query.drop_table(self.new_table).if_exists()
+
+        self.assertEqual('DROP TABLE "abc"', str(q1))
+        self.assertEqual('DROP TABLE IF EXISTS "abc"', str(q2))
+
+    def test_drop_user(self):
+        q1 = Query.drop_user("myuser")
+        q2 = Query.drop_user("myuser").if_exists()
 
-        self.assertEqual('DROP TABLE "abc"', str(q))
+        self.assertEqual('DROP USER "myuser"', str(q1))
+        self.assertEqual('DROP USER IF EXISTS "myuser"', str(q2))
 
-    def test_drop_table_if_exists(self):
-        q = Query.drop_table(self.new_table).if_exists()
+    def test_drop_view(self):
+        q1 = Query.drop_view("myview")
+        q2 = Query.drop_view("myview").if_exists()
 
-        self.assertEqual('DROP TABLE IF EXISTS "abc"', str(q))
+        self.assertEqual('DROP VIEW "myview"', str(q1))
+        self.assertEqual('DROP VIEW IF EXISTS "myview"', str(q2))