Improved handling of comments in queries

What's Changed

• build(deps): bump actions/cache from 2 to 3 by @dependabot in #299
• build(deps-dev): bump pylint from 2.12.2 to 2.13.0 by @dependabot in #300
• build(deps-dev): bump pylint from 2.13.0 to 2.13.2 by @dependabot in #301
• build(deps-dev): bump black from 22.1.0 to 22.3.0 by @dependabot in #302
• build(deps-dev): bump pylint from 2.13.2 to 2.13.3 by @dependabot in #303
• build(deps-dev): bump pylint from 2.13.3 to 2.13.4 by @dependabot in #304
• build(deps-dev): bump pylint from 2.13.4 to 2.13.5 by @dependabot in #305
• build(deps-dev): bump pylint from 2.13.5 to 2.13.7 by @dependabot in #307
• build(deps): bump dependabot/fetch-metadata from 1.3.0 to 1.3.1 by @dependabot in #308
• Do not consider AS ( from window clause as query start by @vakhmenin in #310
• Add next_token_not_comment property of Token by @vakhmenin in #312

New Contributors

• @vakhmenin made their first contribution in #310

Full Changelog: v2.4.0...v2.5.0

Improved USING and DELETE queries handling

What's Changed

• build(deps-dev): bump pylint from 2.10.2 to 2.11.1 by @dependabot in #237
• build(deps-dev): bump black from 21.8b0 to 21.9b0 by @dependabot in #235
• Ignore failed coverage uploads to Coveralls by @macbre in #239
• Add interactive demo link to README by @dylanhogg in #241
• build(deps-dev): bump pytest-cov from 2.12.1 to 3.0.0 by @dependabot in #244
• build(deps-dev): bump flake8 from 3.9.2 to 4.0.1 by @dependabot in #246
• CI - add Python 3.10 by @macbre in #106
• With in join fix by @collerek in #249
• add insert to with ending keywords by @collerek in #250
• build(deps-dev): bump black from 21.9b0 to 21.10b0 by @dependabot in #252
• build(deps-dev): bump coveralls from 3.2.0 to 3.3.0 by @dependabot in #254
• build(deps-dev): bump coverage from 5.5 to 6.1.1 by @dependabot in #256
• build(deps-dev): bump coverage from 6.1.1 to 6.1.2 by @dependabot in #257
• build(deps-dev): bump coveralls from 3.3.0 to 3.3.1 by @dependabot in #258
• build(deps): bump actions/setup-python from 2.2.2 to 2.3.0 by @dependabot in #264
• build(deps-dev): bump black from 21.10b0 to 21.11b1 by @dependabot in #265
• build(deps): bump snok/install-poetry from 1.2 to 1.3 by @dependabot in #266
• build(deps-dev): bump pylint from 2.11.1 to 2.12.1 by @dependabot in #267
• build(deps-dev): bump coverage from 6.1.2 to 6.2 by @dependabot in #268
• build(deps): bump actions/setup-python from 2.3.0 to 2.3.1 by @dependabot in #269
• build(deps-dev): bump pylint from 2.12.1 to 2.12.2 by @dependabot in #272
• build(deps-dev): bump black from 21.11b1 to 21.12b0 by @dependabot in #273
• Add USING to column keywords by @glentakahashi in #255
• Adds DELETE as a supported query type by @Governa in #271
• Allow using keyword as table alias by @niyue in #278
• Add cross join to list of keywords by @Jedi18 in #281
• build(deps-dev): bump black from 21.12b0 to 22.1.0 by @dependabot in #283
• build(deps-dev): bump pytest from 6.2.5 to 7.0.0 by @dependabot in #286
• build(deps): bump actions/setup-python from 2.3.1 to 2.3.2 by @dependabot in #287
• build(deps-dev): bump pytest from 7.0.0 to 7.0.1 by @dependabot in #288
• build(deps): bump dependabot/fetch-metadata from 1.1.1 to 1.2.0 by @dependabot in #289
• build(deps): bump dependabot/fetch-metadata from 1.2.0 to 1.2.1 by @dependabot in #290
• build(deps): bump dependabot/fetch-metadata from 1.2.1 to 1.3.0 by @dependabot in #293
• build(deps): bump actions/setup-python from 2.3.2 to 3 by @dependabot in #292
• build(deps): bump actions/checkout from 2 to 3 by @dependabot in #294

New Contributors

• @glentakahashi made their first contribution in #255
• @Governa made their first contribution in #271
• @niyue made their first contribution in #278
• @Jedi18 made their first contribution in #281

Hi and thanks, folks!

Full Changelog: v2.3.0...v2.4.0

Aliases handling improved

• #187 internals - Multi from aliases (by @collerek)
• #188 bug - Query including Distinct not working
• #189 dependencies - build(deps-dev): bump black from 21.6b0 to 21.7b0 (by @dependabot[bot])
• #190 internals - CI - install the latest black version
• #192 dependencies - build(deps-dev): bump pylint from 2.9.3 to 2.9.4 (by @dependabot[bot])
• #193 dependencies - build(deps-dev): bump coveralls from 3.1.0 to 3.2.0 (by @dependabot[bot])
• #194 pr - Fix for Casting values results in incorrect alias reporting (by @collerek)
• #195 dependencies - build(deps-dev): bump pylint from 2.9.5 to 2.9.6 (by @dependabot[bot])
• #203 pr - Ignore case on column alias names from subquery (by @christopherpickering)
• #211 internals - snok/install-poetry - use a default Poetry version
• #216 dependencies - build(deps-dev): bump pylint from 2.9.6 to 2.10.2 (by @dependabot[bot])
• #217 internals - Extract conditions (by @collerek)
• #218 dependencies - build(deps-dev): bump black from 21.7b0 to 21.8b0 (by @dependabot[bot])
• #219 dependencies - build(deps-dev): bump pytest from 6.2.4 to 6.2.5 (by @dependabot[bot])
• #222 pr - Restore fully qualified columns (by @collerek)
• #223 pr - fix recurrent aliases names leading to infinite loop (by @collerek)
• #228 pr - allow redundant parentheses around (sub)queries (by @collerek)
• #229 pr - additional test for cast (by @collerek)
• #230 dependencies - build(deps): bump sqlparse from 0.4.1 to 0.4.2 (by @dependabot[bot])

Welcome, @christopherpickering, as our new contributor 🙂

Improved handling of with statements

This release of sql-metadata brings even further improvement of handling of with and table creation statements. We also keep up to date with our dependencies and improving CI pipeline.

build(deps-dev): bump black from 21.5b2 to 21.6b0 #167
Fix create schema #171
Create table with schema detects field names as tables #168
Fix for nested with statements in select #173
CI - install Poetry 1.1.7 #174
build(deps-dev): bump pylint from 2.8.3 to 2.9.0 #178
nested with statement causes confusion #172
build(deps-dev): bump pylint from 2.9.0 to 2.9.1 #179
build(deps-dev): bump pylint from 2.9.1 to 2.9.3 #180

Improved leading comments and WITH clauses handling

This minor release brings two fixes:

• #162 bug - Fix parsing with clauses, fix for #161 (by @collerek)
• #164 bug - Handle leading comments in queries (by @macbre)

Aliases and CREATE TABLE queries handling improved

This release of sql-metadata brings improved handling of subqueries (thanks #154) and of CREATE TABLE queries containing a WITH clause (#157).

As an internal change improving our development Coveralls has been introduced that tracks code coverage of this project (#159).

As always we're staying up to date with various dependencies.

Changelog

• #151 dependencies - build(deps-dev): bump pylint from 2.8.2 to 2.8.3 (by @dependabot[bot])
• #152 dependencies - build(deps-dev): bump black from 21.5b1 to 21.5b2 (by @dependabot[bot])
• #153 dependencies - Update black to 21.5b2
• #154 enhancement - Resolving subqueries columns (by @collerek)
• #155 bug - The tables property is still having difficulties finding the tables in a CTAS (by @tipanverella)
• #156 dependencies - build(deps-dev): bump pytest-cov from 2.12.0 to 2.12.1 (by @dependabot[bot])
• #157 bug - Fix for create table with with clause (by @collerek)
• #158 internals - Automerge dependabot pull requests
• #159 internals - Upload coverage report to Coveralls

Various parsing fixes

This release of sql-metadata brings a few fixes regarding handling of leading digits in table names (#139), inline comments (#147) and columns with names containing SQL keywords (#141).

• #139 bug - Tables with leading digits are not properly recognized
• #140 enhancement - Improved handling of tables names with leading digits
• #141 bug - Columns with names that start with a keyword are not recognized as a column (by @markderry)
• #142 dependencies - build(deps-dev): bump pytest-cov from 2.11.1 to 2.12.0 (by @dependabot[bot])
• #143 enhancement - Tables with leading digits (by @collerek)
• #146 bug - Relevant keywords change, fix parenthesis helper (by @collerek)
• #147 bug - Queries with in-line comments (--) are commenting out the entire rest of the query (by @taringoo)
• #148 internals - CI - update poetry action version

New Parser and API

This major release of sql-metadata brings a completely new parser and API implemented by @collerek on top of proposals from #83 and #98.

The gist of the changes is the introduction of the Parser class (see #121) that is later used to get the list columns, tables, aliases.

CREATE TABLE queries are now supported (#35). Aliases handling has been greatly improved thanks to #131.

Code coverage has been improved and is now kept at 100%.

Example

from sql_metadata import Parser

parsed = Parser("SELECT test, id FROM foo, bar")

parsed.columns  # ['test', 'id']
parsed.tables  # ['foo', 'bar']

Migrating from sql_metadata 1.x

sql_metadata.compat module has been implemented to make the introduction of sql-metadata v2.0 smoother.

You can use it by simply changing the imports in your code from:

from sql_metadata import get_query_columns, get_query_tables

into:

from sql_metadata.compat import get_query_columns, get_query_tables

The following functions from the old API are available in the sql_metadata.compat module:

• generalize_sql
• get_query_columns (since #131 columns aliases ARE NOT returned by this function)
• get_query_limit_and_offset
• get_query_tables
• get_query_tokens
• preprocess_query

Changes

• #35 enhancement - Support for CREATE TABLE... statements (by @sandeep-pareek)
• #83 enhancement - New API [draft]
• #85 Postgres - Support Postgres quoted identifiers (by @LanDinh)
• #98 internals - Improve get_query_tokens iterator
• #103 question - Best way to get an array of values? (by @jplock)
• #112 bug - missing tables returned by sql_metadata.get_query_tables (by @tdebroc)
• #121 enhancement - New parser, bug fixes and new functionality. (by @collerek)
• #125 bug - sql_metadata.get_query_columns result error (by @chen-ABC)
• #126 enhancement - Add a support for CREATE TABLE queries
• #127 pr - Improved handling of custom SQL functions when getting the list of columns
• #128 bug - get_query_tables() is returning columns on WITH statements (by @remisalmon)
• #129 dependencies - build(deps-dev): bump black from 21.4b2 to 21.5b0 (by @dependabot[bot])
• #130 dependencies - build(deps-dev): bump pytest from 6.2.3 to 6.2.4 (by @dependabot[bot])
• #131 enhancement - Column aliases support (by @collerek)
• #133 dependencies - build(deps-dev): bump black from 21.5b0 to 21.5b1 (by @dependabot[bot])
• #135 internals - tool.coverage.report: require 100% code coverage
• #136 internals - CI - use Makefile commands
• #137 internals - Introduce a compatibility layer for 1.x functions

Improved brackets handling + dependencies updated

• #100 internals - Lint after running tests
• #101 dependencies - build(deps-dev): bump pylint from 2.6.2 to 2.7.0 (by @dependabot[bot])
• #102 dependencies - build(deps-dev): bump pylint from 2.7.0 to 2.7.1 (by @dependabot[bot])
• #104 dependencies - build(deps-dev): bump coverage from 5.4 to 5.5 (by @dependabot[bot])
• #108 internals - Use poetry 1.1.5
• #109 internals - CI - store ~/.cache dir + pip install wheel
• #110 internals - actions/cache: invalidate on poetry.lock change
• #113 enhancement - Improved brackets handling
• #116 dependencies - build(deps-dev): bump pytest from 6.2.2 to 6.2.3 (by @dependabot[bot])
• #117 dependencies - snok/[email protected]
• #118 dependencies - build(deps-dev): bump pylint from 2.7.4 to 2.8.1 (by @dependabot[bot])
• #119 dependencies - build(deps-dev): bump pylint from 2.8.1 to 2.8.2 (by @dependabot[bot])
• #123 dependencies - Upgrade black to 21.4b1
• #124 dependencies - build(deps-dev): bump black from 21.4b1 to 21.4b2 (by @dependabot[bot])

Improved UNION and GROUP BY handling

This release brings two bug fixes from @aborecki (dzięki! 🙂 🇵🇱 ) and introduces poetry as the dependency manager.

Changelog

• #79 bug - Wrong parsing results from get_query_tables for select with UNION (by @aborecki)
• #80 bug - Wrong parsing results get_query_tables for spaces in GROUP BY keyword (by @aborecki)
• #81 internals - Use Poetry as dependency manager and PyPI publish tool
• #86 dependencies - build(deps-dev): bump pytest from 6.2.1 to 6.2.2 (by @dependabot[bot])
• #87 dependencies - build(deps-dev): bump coverage from 5.3.1 to 5.4 (by @dependabot[bot])
• #88 internals - Run tests on both Python 3.8 and 3.9
• #89 internals - Test with Python 3.7 too
• #90 internals - Improve CI cache handling
• #91 internals - Load cached Poetry venv
• #92 internals - Try to test using Python 3.6 and 3.7 too
• #93 internals - Tests - add code coverage report
• #94 bug - Union's don't return the correct result (by @dylwylie)
• #95 dependencies - build(deps-dev): bump pylint from 2.6.0 to 2.6.2 (by @dependabot[bot])
• #96 pr - Extend test coverage for UNION queries