Repository: shinichi-takii/ddlparse

Issue:
MySQL CREATE TABLE parsing fails. some columns are missing.
I tried to output a BigQuery column definition from a MySQL table definition, but some columns are missing.

Please show me how to avoid it by crafting `CREATE TABLE`, or if I used ddlparse incorrectly.

# reproduce

## environment

```console
$ sw_vers
ProductName:	Mac OS X
ProductVersion:	10.15.7
BuildVersion:	19H2
$ python -V
Python 3.8.3
$ ll $(python -c "import site; print (site.getsitepackages()[0])")/ | grep ddlparse
drwxr-xr-x    5 sakamossan  staff   160B  9 25 12:49 ddlparse
drwxr-xr-x    8 sakamossan  staff   256B  9 25 12:49 ddlparse-1.8.0.dist-info
```

## execution

I used following files

### /tmp/_.sql

```sql
CREATE TABLE `t` (
  `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `created` DATETIME NULL,
  `modified` DATETIME NULL
) ENGINE = InnoDB;
```

### /tmp/_.py

```python
#!/usr/bin/env python
import sys
import json
from ddlparse import DdlParse

sql = sys.stdin.read()
table = DdlParse().parse(ddl=sql, source_database=DdlParse.DATABASE.mysql)
print(table.to_bigquery_fields())
```

```bash
$ cat /tmp/_.json | /tmp/_.py | jq .
[
  {
    "name": "id",
    "type": "INTEGER",
    "mode": "REQUIRED"
  },
  {
    "name": "created",
    "type": "DATETIME",
    "mode": "NULLABLE"
  }
]
```

modified column is not output.


Discussion:

Patch:
diff --git a/.travis.yml b/.travis.yml
index efe2382..0ea230d 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -7,6 +7,7 @@ python:
   - "3.6"
   - "3.7"
   - "3.8"
+  - "3.9"
 
 # command to install dependencies
 install:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 955ffd3..70cc41d 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -5,6 +5,14 @@ The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/),
 and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).
 
 
+## [1.10.0] - 2021-07-10
+### Added
+- Added supports for Python 3.9
+
+### Fixed
+- Fixed parsing of columns with NULL/NOT NULL constraint.
+
+
 ## [1.9.0] - 2020-11-04
 ### Added
 - Add supports Cloud Spanner data-type.
@@ -58,7 +66,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
   - `FIXED`
 
 ### Fixed
-- Miner fix.
+- Minor fix.
 
 
 ## [1.5.0] - 2020-07-06
@@ -70,7 +78,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 ### Fixed
 - Fix parse for column-constraint.
-- Miner fix.
+- Minor fix.
 
 
 ## [1.4.0] - 2019-12-08
@@ -161,7 +169,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
     - `NUMBER` with no length & scale specification
 
 ### Fixed
-- Miner fix.
+- Minor fix.
 
 
 ## [1.1.1] - 2018-03-25
@@ -182,15 +190,15 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 ## [1.0.2] - 2018-01-09
 ### Fixed
-- Miner enhancement.
+- Minor enhancement.
     - `ddlparse.py` : Exclude unused module.
     - `example.py` : Modified comment.
-    - `README.md` : Miner fix.
+    - `README.md` : Minor fix.
 
 
 ## [1.0.1] - 2018-01-07
 ### Fixed
-- Miner enhancement.
+- Minor enhancement.
 
 
 ## [1.0.0]
@@ -198,6 +206,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 - Initial released.
 
 
+[1.10.0]: https://github.com/shinichi-takii/ddlparse/compare/v1.9.0...v1.10.0
 [1.9.0]: https://github.com/shinichi-takii/ddlparse/compare/v1.8.0...v1.9.0
 [1.8.0]: https://github.com/shinichi-takii/ddlparse/compare/v1.7.0...v1.8.0
 [1.7.0]: https://github.com/shinichi-takii/ddlparse/compare/v1.6.1...v1.7.0
diff --git a/README.md b/README.md
index 7e244ec..9719092 100644
--- a/README.md
+++ b/README.md
@@ -2,7 +2,7 @@
 
 [![PyPI version](https://img.shields.io/pypi/v/ddlparse.svg)](https://pypi.org/project/ddlparse/)
 [![Python version](https://img.shields.io/pypi/pyversions/ddlparse.svg)](https://pypi.org/project/ddlparse/)
-[![Travis CI Build Status](https://travis-ci.org/shinichi-takii/ddlparse.svg?branch=master)](https://travis-ci.org/shinichi-takii/ddlparse)
+[![Travis CI Build Status](https://travis-ci.com/shinichi-takii/ddlparse.svg?branch=master)](https://travis-ci.com/shinichi-takii/ddlparse)
 [![Coveralls Coverage Status](https://coveralls.io/repos/github/shinichi-takii/ddlparse/badge.svg?branch=master)](https://coveralls.io/github/shinichi-takii/ddlparse?branch=master)
 [![codecov Coverage Status](https://codecov.io/gh/shinichi-takii/ddlparse/branch/master/graph/badge.svg)](https://codecov.io/gh/shinichi-takii/ddlparse)
 [![Requirements Status](https://requires.io/github/shinichi-takii/ddlparse/requirements.svg?branch=master)](https://requires.io/github/shinichi-takii/ddlparse/requirements/?branch=master)
diff --git a/ddlparse/__init__.py b/ddlparse/__init__.py
index 514a3f2..843a9fe 100644
--- a/ddlparse/__init__.py
+++ b/ddlparse/__init__.py
@@ -7,8 +7,8 @@
 
 from .ddlparse import *
 
-__copyright__    = 'Copyright (C) 2018-2020 Shinichi Takii'
-__version__      = '1.9.0'
+__copyright__    = 'Copyright (C) 2018-2021 Shinichi Takii'
+__version__      = '1.10.0'
 __license__      = 'BSD-3-Clause'
 __author__       = 'Shinichi Takii'
 __author_email__ = 'shinichi.takii@shaketh.com'
diff --git a/ddlparse/ddlparse.py b/ddlparse/ddlparse.py
index 6fa4a69..930e670 100644
--- a/ddlparse/ddlparse.py
+++ b/ddlparse/ddlparse.py
@@ -645,7 +645,7 @@ class DdlParse(DdlParseBase):
                     + Optional(
                         Regex(r"(?!--)", re.IGNORECASE)
                         + Group(
-                            Optional(Regex(r"\b(?:NOT\s+)NULL?\b", re.IGNORECASE))("null")
+                            Optional(Regex(r"\b(?:NOT\s+)?NULL?\b", re.IGNORECASE))("null")
                             & Optional(Regex(r"\bAUTO_INCREMENT\b", re.IGNORECASE))("auto_increment")
                             & Optional(Regex(r"\b(UNIQUE|PRIMARY)(?:\s+KEY)?\b", re.IGNORECASE))("key")
                             & Optional(Regex(
diff --git a/setup.py b/setup.py
index bf722c3..b227011 100644
--- a/setup.py
+++ b/setup.py
@@ -64,6 +64,7 @@ setup(
         'Programming Language :: Python :: 3.6',
         'Programming Language :: Python :: 3.7',
         'Programming Language :: Python :: 3.8',
+        'Programming Language :: Python :: 3.9',
         'Topic :: Database',
         'Topic :: Software Development :: Libraries :: Python Modules',
     ],
diff --git a/tox.ini b/tox.ini
index 0241569..1c9b29e 100644
--- a/tox.ini
+++ b/tox.ini
@@ -1,5 +1,5 @@
 [tox]
-envlist = py35,py36,py37,py38
+envlist = py35,py36,py37,py38,py39
 
 [testenv]
 deps=


Test patch:
diff --git a/test/test_ddlparse.py b/test/test_ddlparse.py
index 7a50697..086a2a3 100644
--- a/test/test_ddlparse.py
+++ b/test/test_ddlparse.py
@@ -65,7 +65,9 @@ TEST_DATA = {
               Col_49 number,
               Col_50 decimal,
               Col_51 string(20),
-              Col_52 bytes(20)
+              Col_52 bytes(20),
+              Col_53 char(200) NULL,
+              Col_54 char(200) NOT NULL
             );
             """,
         "database": None,
@@ -123,6 +125,8 @@ TEST_DATA = {
             {"name": "Col_50", "type": "DECIMAL", "length": None, "scale": None},
             {"name": "Col_51", "type": "STRING", "length": 20, "scale": None},
             {"name": "Col_52", "type": "BYTES", "length": 20, "scale": None},
+            {"name": "Col_53", "type": "CHAR", "length": 200, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": False, "pk": False, "unique": False, "description": None},
+            {"name": "Col_54", "type": "CHAR", "length": 200, "scale": None, "array_dimensional": 0, "is_unsigned": False, "is_zerofill": False, "not_null": True, "pk": False, "unique": False, "constraint": "NOT NULL", "description": None},
         ],
         "bq_field": [
             '{"name": "Col_01", "type": "STRING", "mode": "REQUIRED"}',
@@ -177,6 +181,8 @@ TEST_DATA = {
             '{"name": "Col_50", "type": "INTEGER", "mode": "NULLABLE"}',
             '{"name": "Col_51", "type": "STRING", "mode": "NULLABLE"}',
             '{"name": "Col_52", "type": "BYTES", "mode": "NULLABLE"}',
+            '{"name": "Col_53", "type": "STRING", "mode": "NULLABLE"}',
+            '{"name": "Col_54", "type": "STRING", "mode": "REQUIRED"}',
         ],
         "bq_standard_data_type": [
             "STRING",
@@ -231,6 +237,8 @@ TEST_DATA = {
             "INT64",
             "STRING",
             "BYTES",
+            "STRING",
+            "STRING",
         ],
     },