Repository: 3YOURMIND/django-migration-linter

Issue:
Linter fails on CREATE INDEX when creating a new table
Here is an example `CreateModel` from Django:

```python
migrations.CreateModel(
    name='ShipmentMetadataAlert',
    fields=[
        ('deleted_at', models.DateTimeField(blank=True, db_index=True, null=True)),
        ('created_at', common.fields.CreatedField(default=django.utils.timezone.now, editable=False)),
        ('updated_at', common.fields.LastModifiedField(default=django.utils.timezone.now, editable=False)),
        ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False, verbose_name='ID')),
        ('message', models.TextField(blank=True, null=True)),
        ('level', models.CharField(blank=True, choices=[('HIGH', 'high'), ('MEDIUM', 'medium'), ('LOW', 'low')], max_length=16, null=True)),
        ('type', models.CharField(blank=True, choices=[('MOBILE_DEVICE_ALERT', 'MOBILE_DEVICE_ALERT'), ('NON_ACTIVE_CARRIER', 'NON_ACTIVE_CARRIER'), ('OTHER', 'OTHER')], max_length=32, null=True)),
        ('subtype', models.CharField(blank=True, choices=[('DRIVER_PERMISSIONS', 'DRIVER_PERMISSIONS'), ('DRIVER_LOCATION', 'DRIVER_LOCATION'), ('OTHER', 'OTHER')], max_length=32, null=True)),
        ('occurred_at', models.DateTimeField(null=True)),
        ('clear_alert_job_id', models.UUIDField(default=None, null=True)),
        ('metadata', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='alerts', to='shipments.ShipmentMetadata')),
    ],
    options={
        'abstract': False,
    }
)
```

Here are the SQL statements that this spits out in `sqlmigrate`:

```sql
BEGIN;
--
-- Create model ShipmentMetadataAlert
--
CREATE TABLE "shipments_shipmentmetadataalert" ("deleted_at" timestamp with time zone NULL, "created_at" timestamp with time zone NOT NULL, "updated_at" timestamp with time zone NOT NULL, "id" uuid NOT NULL PRIMARY KEY, "message" text NULL, "level" varchar(16) NULL, "type" varchar(32) NULL, "subtype" varchar(32) NULL, "occurred_at" timestamp with time zone NULL, "clear_alert_job_id" uuid NULL, "metadata_id" uuid NOT NULL);
ALTER TABLE "shipments_shipmentmetadataalert" ADD CONSTRAINT "shipments_shipmentme_metadata_id_f20850e8_fk_shipments" FOREIGN KEY ("metadata_id") REFERENCES "shipments_shipmentmetadata" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "shipments_shipmentmetadataalert_deleted_at_c9a93342" ON "shipments_shipmentmetadataalert" ("deleted_at");
CREATE INDEX "shipments_shipmentmetadataalert_metadata_id_f20850e8" ON "shipments_shipmentmetadataalert" ("metadata_id");
COMMIT;
```
 
This is an error from the linter as it outputs the error `CREATE INDEX locks table`. But the table is being created within the migration, it just needs to recognize that.

It seems like the `CREATE INDEX` detection should work the same way that the `ADD_UNIQUE` detection works where it detects that the create table is happening in the same migration:

https://github.com/3YOURMIND/django-migration-linter/blob/db71a9db23746f64d41d681f3fecb9b066c87338/django_migration_linter/sql_analyser/base.py#L26-L40

Discussion:
codecov-commenter: # [Codecov](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) Report
> Merging [#186](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) (5e2e5c1) into [main](https://codecov.io/gh/3YOURMIND/django-migration-linter/commit/aef3db3e4198d06c38bc4b0874e72ed657891eea?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) (aef3db3) will **increase** coverage by `0.08%`.
> The diff coverage is `100.00%`.

[![Impacted file tree graph](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186/graphs/tree.svg?width=650&height=150&src=pr&token=pgNNXGbCf7&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None)](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None)

```diff
@@            Coverage Diff             @@
##             main     #186      +/-   ##
==========================================
+ Coverage   93.32%   93.40%   +0.08%     
==========================================
  Files          77       77              
  Lines        1633     1653      +20     
==========================================
+ Hits         1524     1544      +20     
  Misses        109      109              
```


| [Impacted Files](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) | Coverage Δ | |
|---|---|---|
| [django\_migration\_linter/sql\_analyser/postgresql.py](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-ZGphbmdvX21pZ3JhdGlvbl9saW50ZXIvc3FsX2FuYWx5c2VyL3Bvc3RncmVzcWwucHk=) | `100.00% <100.00%> (ø)` | |
| [tests/unit/test\_sql\_analyser.py](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-dGVzdHMvdW5pdC90ZXN0X3NxbF9hbmFseXNlci5weQ==) | `100.00% <100.00%> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None). Last update [aef3db3...5e2e5c1](https://codecov.io/gh/3YOURMIND/django-migration-linter/pull/186?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None).


Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index d1ec8e5..15fefc0 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,7 +1,8 @@
-## 4.0.0
+## 4.0.0 (unreleased)
 
 - Drop support for Python 2.7 and 3.5
 - Drop support for Django 1.11, 2.0, 2.1, 3.0
+- Fix index creation detection when table is being created in the transaction (issue #178)
 
 ## 3.0.1
 
diff --git a/django_migration_linter/sql_analyser/postgresql.py b/django_migration_linter/sql_analyser/postgresql.py
index 140aba3..3eb18a5 100644
--- a/django_migration_linter/sql_analyser/postgresql.py
+++ b/django_migration_linter/sql_analyser/postgresql.py
@@ -3,14 +3,32 @@ import re
 from .base import BaseAnalyser
 
 
+def has_create_index(sql_statements, **kwargs):
+    regex_result = None
+    for sql in sql_statements:
+        regex_result = re.search(r"CREATE (UNIQUE )?INDEX.*ON (.*) \(", sql)
+        if re.search("INDEX CONCURRENTLY", sql):
+            regex_result = None
+        elif regex_result:
+            break
+    if not regex_result:
+        return False
+
+    concerned_table = regex_result.group(2)
+    table_is_added_in_transaction = any(
+        sql.startswith("CREATE TABLE {}".format(concerned_table))
+        for sql in sql_statements
+    )
+    return not table_is_added_in_transaction
+
+
 class PostgresqlAnalyser(BaseAnalyser):
     migration_tests = [
         {
             "code": "CREATE_INDEX",
-            "fn": lambda sql, **kw: re.search("CREATE (UNIQUE )?INDEX", sql)
-            and not re.search("INDEX CONCURRENTLY", sql),
+            "fn": has_create_index,
             "msg": "CREATE INDEX locks table",
-            "mode": "one_liner",
+            "mode": "transaction",
             "type": "warning",
         },
         {


Test patch:
diff --git a/tests/unit/test_sql_analyser.py b/tests/unit/test_sql_analyser.py
index 00dd50e..65ab7f0 100644
--- a/tests/unit/test_sql_analyser.py
+++ b/tests/unit/test_sql_analyser.py
@@ -233,6 +233,23 @@ class PostgresqlAnalyserTestCase(SqlAnalyserTestCase):
         sql = "CREATE UNIQUE INDEX title_idx ON films (title);"
         self.assertWarningSql(sql)
 
+    def test_create_index_non_concurrently_with_table_creation(self):
+        sql = [
+            'CREATE TABLE "films" ("title" text);',
+            'CREATE INDEX ON "films" ((lower("title")));',
+        ]
+        self.assertValidSql(sql)
+        sql = [
+            'CREATE TABLE "some_table" ("title" text);',
+            'CREATE INDEX ON "films" ((lower("title")));',
+        ]
+        self.assertWarningSql(sql)
+        sql = [
+            'CREATE TABLE "films" ("title" text);',
+            'CREATE INDEX ON "some_table" ((lower("title")));',
+        ]
+        self.assertWarningSql(sql)
+
     def test_create_index_concurrently(self):
         sql = "CREATE INDEX CONCURRENTLY ON films (lower(title));"
         self.assertValidSql(sql)