diff options
Diffstat (limited to 'sqlglot/generator.py')
| -rw-r--r-- | sqlglot/generator.py | 248 |
1 files changed, 213 insertions, 35 deletions
diff --git a/sqlglot/generator.py b/sqlglot/generator.py index b398d8e..3f3365a 100644 --- a/sqlglot/generator.py +++ b/sqlglot/generator.py @@ -65,6 +65,8 @@ class Generator: exp.ReturnsProperty: lambda self, e: self.naked_property(e), exp.ExecuteAsProperty: lambda self, e: self.naked_property(e), exp.VolatilityProperty: lambda self, e: e.name, + exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}", + exp.LogProperty: lambda self, e: f"{'NO ' if e.args.get('no') else ''}LOG", } # Whether 'CREATE ... TRANSIENT ... TABLE' is allowed @@ -97,6 +99,20 @@ class Generator: STRUCT_DELIMITER = ("<", ">") + BEFORE_PROPERTIES = { + exp.FallbackProperty, + exp.WithJournalTableProperty, + exp.LogProperty, + exp.JournalProperty, + exp.AfterJournalProperty, + exp.ChecksumProperty, + exp.FreespaceProperty, + exp.MergeBlockRatioProperty, + exp.DataBlocksizeProperty, + exp.BlockCompressionProperty, + exp.IsolatedLoadingProperty, + } + ROOT_PROPERTIES = { exp.ReturnsProperty, exp.LanguageProperty, @@ -113,8 +129,6 @@ class Generator: exp.TableFormatProperty, } - WITH_SINGLE_ALTER_TABLE_ACTION = (exp.AlterColumn, exp.RenameTable, exp.AddConstraint) - WITH_SEPARATED_COMMENTS = (exp.Select, exp.From, exp.Where, exp.Binary) SENTINEL_LINE_BREAK = "__SQLGLOT__LB__" @@ -122,7 +136,6 @@ class Generator: "time_mapping", "time_trie", "pretty", - "configured_pretty", "quote_start", "quote_end", "identifier_start", @@ -177,7 +190,6 @@ class Generator: self.time_mapping = time_mapping or {} self.time_trie = time_trie self.pretty = pretty if pretty is not None else sqlglot.pretty - self.configured_pretty = self.pretty self.quote_start = quote_start or "'" self.quote_end = quote_end or "'" self.identifier_start = identifier_start or '"' @@ -442,8 +454,20 @@ class Generator: return "UNIQUE" def create_sql(self, expression: exp.Create) -> str: - this = self.sql(expression, "this") kind = self.sql(expression, "kind").upper() + has_before_properties = expression.args.get("properties") + has_before_properties = ( + has_before_properties.args.get("before") if has_before_properties else None + ) + if kind == "TABLE" and has_before_properties: + this_name = self.sql(expression.this, "this") + this_properties = self.sql(expression, "properties") + this_schema = f"({self.expressions(expression.this)})" + this = f"{this_name}, {this_properties} {this_schema}" + properties = "" + else: + this = self.sql(expression, "this") + properties = self.sql(expression, "properties") begin = " BEGIN" if expression.args.get("begin") else "" expression_sql = self.sql(expression, "expression") expression_sql = f" AS{begin}{self.sep()}{expression_sql}" if expression_sql else "" @@ -456,7 +480,10 @@ class Generator: exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else "" unique = " UNIQUE" if expression.args.get("unique") else "" materialized = " MATERIALIZED" if expression.args.get("materialized") else "" - properties = self.sql(expression, "properties") + set_ = " SET" if expression.args.get("set") else "" + multiset = " MULTISET" if expression.args.get("multiset") else "" + global_temporary = " GLOBAL TEMPORARY" if expression.args.get("global_temporary") else "" + volatile = " VOLATILE" if expression.args.get("volatile") else "" data = expression.args.get("data") if data is None: data = "" @@ -475,7 +502,7 @@ class Generator: indexes = expression.args.get("indexes") index_sql = "" - if indexes is not None: + if indexes: indexes_sql = [] for index in indexes: ind_unique = " UNIQUE" if index.args.get("unique") else "" @@ -500,6 +527,10 @@ class Generator: external, unique, materialized, + set_, + multiset, + global_temporary, + volatile, ) ) no_schema_binding = ( @@ -569,13 +600,14 @@ class Generator: def delete_sql(self, expression: exp.Delete) -> str: this = self.sql(expression, "this") + this = f" FROM {this}" if this else "" using_sql = ( f" USING {self.expressions(expression, 'using', sep=', USING ')}" if expression.args.get("using") else "" ) where_sql = self.sql(expression, "where") - sql = f"DELETE FROM {this}{using_sql}{where_sql}" + sql = f"DELETE{this}{using_sql}{where_sql}" return self.prepend_ctes(expression, sql) def drop_sql(self, expression: exp.Drop) -> str: @@ -630,28 +662,27 @@ class Generator: return f"N{self.sql(expression, 'this')}" def partition_sql(self, expression: exp.Partition) -> str: - keys = csv( - *[ - f"""{prop.name}='{prop.text("value")}'""" if prop.text("value") else prop.name - for prop in expression.this - ] - ) - return f"PARTITION({keys})" + return f"PARTITION({self.expressions(expression)})" def properties_sql(self, expression: exp.Properties) -> str: + before_properties = [] root_properties = [] with_properties = [] for p in expression.expressions: p_class = p.__class__ - if p_class in self.WITH_PROPERTIES: + if p_class in self.BEFORE_PROPERTIES: + before_properties.append(p) + elif p_class in self.WITH_PROPERTIES: with_properties.append(p) elif p_class in self.ROOT_PROPERTIES: root_properties.append(p) - return self.root_properties( - exp.Properties(expressions=root_properties) - ) + self.with_properties(exp.Properties(expressions=with_properties)) + return ( + self.properties(exp.Properties(expressions=before_properties), before=True) + + self.root_properties(exp.Properties(expressions=root_properties)) + + self.with_properties(exp.Properties(expressions=with_properties)) + ) def root_properties(self, properties: exp.Properties) -> str: if properties.expressions: @@ -659,13 +690,17 @@ class Generator: return "" def properties( - self, properties: exp.Properties, prefix: str = "", sep: str = ", ", suffix: str = "" + self, + properties: exp.Properties, + prefix: str = "", + sep: str = ", ", + suffix: str = "", + before: bool = False, ) -> str: if properties.expressions: expressions = self.expressions(properties, sep=sep, indent=False) - return ( - f"{prefix}{' ' if prefix and prefix != ' ' else ''}{self.wrap(expressions)}{suffix}" - ) + expressions = expressions if before else self.wrap(expressions) + return f"{prefix}{' ' if prefix and prefix != ' ' else ''}{expressions}{suffix}" return "" def with_properties(self, properties: exp.Properties) -> str: @@ -687,6 +722,98 @@ class Generator: options = f" {options}" if options else "" return f"LIKE {self.sql(expression, 'this')}{options}" + def fallbackproperty_sql(self, expression: exp.FallbackProperty) -> str: + no = "NO " if expression.args.get("no") else "" + protection = " PROTECTION" if expression.args.get("protection") else "" + return f"{no}FALLBACK{protection}" + + def journalproperty_sql(self, expression: exp.JournalProperty) -> str: + no = "NO " if expression.args.get("no") else "" + dual = "DUAL " if expression.args.get("dual") else "" + before = "BEFORE " if expression.args.get("before") else "" + return f"{no}{dual}{before}JOURNAL" + + def freespaceproperty_sql(self, expression: exp.FreespaceProperty) -> str: + freespace = self.sql(expression, "this") + percent = " PERCENT" if expression.args.get("percent") else "" + return f"FREESPACE={freespace}{percent}" + + def afterjournalproperty_sql(self, expression: exp.AfterJournalProperty) -> str: + no = "NO " if expression.args.get("no") else "" + dual = "DUAL " if expression.args.get("dual") else "" + local = "" + if expression.args.get("local") is not None: + local = "LOCAL " if expression.args.get("local") else "NOT LOCAL " + return f"{no}{dual}{local}AFTER JOURNAL" + + def checksumproperty_sql(self, expression: exp.ChecksumProperty) -> str: + if expression.args.get("default"): + property = "DEFAULT" + elif expression.args.get("on"): + property = "ON" + else: + property = "OFF" + return f"CHECKSUM={property}" + + def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str: + if expression.args.get("no"): + return "NO MERGEBLOCKRATIO" + if expression.args.get("default"): + return "DEFAULT MERGEBLOCKRATIO" + + percent = " PERCENT" if expression.args.get("percent") else "" + return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}" + + def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str: + default = expression.args.get("default") + min = expression.args.get("min") + if default is not None or min is not None: + if default: + property = "DEFAULT" + elif min: + property = "MINIMUM" + else: + property = "MAXIMUM" + return f"{property} DATABLOCKSIZE" + else: + units = expression.args.get("units") + units = f" {units}" if units else "" + return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}" + + def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str: + autotemp = expression.args.get("autotemp") + always = expression.args.get("always") + default = expression.args.get("default") + manual = expression.args.get("manual") + never = expression.args.get("never") + + if autotemp is not None: + property = f"AUTOTEMP({self.expressions(autotemp)})" + elif always: + property = "ALWAYS" + elif default: + property = "DEFAULT" + elif manual: + property = "MANUAL" + elif never: + property = "NEVER" + return f"BLOCKCOMPRESSION={property}" + + def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str: + no = expression.args.get("no") + no = " NO" if no else "" + concurrent = expression.args.get("concurrent") + concurrent = " CONCURRENT" if concurrent else "" + + for_ = "" + if expression.args.get("for_all"): + for_ = " FOR ALL" + elif expression.args.get("for_insert"): + for_ = " FOR INSERT" + elif expression.args.get("for_none"): + for_ = " FOR NONE" + return f"WITH{no}{concurrent} ISOLATED LOADING{for_}" + def insert_sql(self, expression: exp.Insert) -> str: overwrite = expression.args.get("overwrite") @@ -833,10 +960,21 @@ class Generator: grouping_sets = ( f"{self.seg('GROUPING SETS')} {self.wrap(grouping_sets)}" if grouping_sets else "" ) - cube = self.expressions(expression, key="cube", indent=False) - cube = f"{self.seg('CUBE')} {self.wrap(cube)}" if cube else "" - rollup = self.expressions(expression, key="rollup", indent=False) - rollup = f"{self.seg('ROLLUP')} {self.wrap(rollup)}" if rollup else "" + + cube = expression.args.get("cube") + if cube is True: + cube = self.seg("WITH CUBE") + else: + cube = self.expressions(expression, key="cube", indent=False) + cube = f"{self.seg('CUBE')} {self.wrap(cube)}" if cube else "" + + rollup = expression.args.get("rollup") + if rollup is True: + rollup = self.seg("WITH ROLLUP") + else: + rollup = self.expressions(expression, key="rollup", indent=False) + rollup = f"{self.seg('ROLLUP')} {self.wrap(rollup)}" if rollup else "" + return f"{group_by}{grouping_sets}{cube}{rollup}" def having_sql(self, expression: exp.Having) -> str: @@ -980,10 +1118,37 @@ class Generator: return f"{self.sql(expression, 'this')}{sort_order}{nulls_sort_change}" + def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str: + partition = self.partition_by_sql(expression) + order = self.sql(expression, "order") + measures = self.sql(expression, "measures") + measures = self.seg(f"MEASURES {measures}") if measures else "" + rows = self.sql(expression, "rows") + rows = self.seg(rows) if rows else "" + after = self.sql(expression, "after") + after = self.seg(after) if after else "" + pattern = self.sql(expression, "pattern") + pattern = self.seg(f"PATTERN ({pattern})") if pattern else "" + define = self.sql(expression, "define") + define = self.seg(f"DEFINE {define}") if define else "" + body = "".join( + ( + partition, + order, + measures, + rows, + after, + pattern, + define, + ) + ) + return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}" + def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str: return csv( *sqls, *[self.sql(sql) for sql in expression.args.get("joins") or []], + self.sql(expression, "match"), *[self.sql(sql) for sql in expression.args.get("laterals") or []], self.sql(expression, "where"), self.sql(expression, "group"), @@ -1092,8 +1257,7 @@ class Generator: def window_sql(self, expression: exp.Window) -> str: this = self.sql(expression, "this") - partition = self.expressions(expression, key="partition_by", flat=True) - partition = f"PARTITION BY {partition}" if partition else "" + partition = self.partition_by_sql(expression) order = expression.args.get("order") order_sql = self.order_sql(order, flat=True) if order else "" @@ -1113,6 +1277,10 @@ class Generator: return f"{this} ({window_args.strip()})" + def partition_by_sql(self, expression: exp.Window | exp.MatchRecognize) -> str: + partition = self.expressions(expression, key="partition_by", flat=True) + return f"PARTITION BY {partition}" if partition else "" + def window_spec_sql(self, expression: exp.WindowSpec) -> str: kind = self.sql(expression, "kind") start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ") @@ -1386,16 +1554,19 @@ class Generator: actions = self.expressions(expression, "actions", prefix="ADD COLUMN ") elif isinstance(actions[0], exp.Schema): actions = self.expressions(expression, "actions", prefix="ADD COLUMNS ") - elif isinstance(actions[0], exp.Drop): - actions = self.expressions(expression, "actions") - elif isinstance(actions[0], self.WITH_SINGLE_ALTER_TABLE_ACTION): - actions = self.sql(actions[0]) + elif isinstance(actions[0], exp.Delete): + actions = self.expressions(expression, "actions", flat=True) else: - self.unsupported(f"Unsupported ALTER TABLE action {actions[0].__class__.__name__}") + actions = self.expressions(expression, "actions") exists = " IF EXISTS" if expression.args.get("exists") else "" return f"ALTER TABLE{exists} {self.sql(expression, 'this')} {actions}" + def droppartition_sql(self, expression: exp.DropPartition) -> str: + expressions = self.expressions(expression) + exists = " IF EXISTS " if expression.args.get("exists") else " " + return f"DROP{exists}{expressions}" + def addconstraint_sql(self, expression: exp.AddConstraint) -> str: this = self.sql(expression, "this") expression_ = self.sql(expression, "expression") @@ -1447,6 +1618,9 @@ class Generator: def escape_sql(self, expression: exp.Escape) -> str: return self.binary(expression, "ESCAPE") + def glob_sql(self, expression: exp.Glob) -> str: + return self.binary(expression, "GLOB") + def gt_sql(self, expression: exp.GT) -> str: return self.binary(expression, ">") @@ -1499,7 +1673,11 @@ class Generator: return f"TRY_CAST({self.sql(expression, 'this')} AS {self.sql(expression, 'to')})" def use_sql(self, expression: exp.Use) -> str: - return f"USE {self.sql(expression, 'this')}" + kind = self.sql(expression, "kind") + kind = f" {kind}" if kind else "" + this = self.sql(expression, "this") + this = f" {this}" if this else "" + return f"USE{kind}{this}" def binary(self, expression: exp.Binary, op: str) -> str: return f"{self.sql(expression, 'this')} {op} {self.sql(expression, 'expression')}" |