Repository: ariga/atlas
Pull request #1100 (2022-08-26 14:08:05)

cmd/atlas/internal/migrate: code-generate mapping between revision st…
…ructs


Patch:
diff --git a/cmd/atlas/internal/migrate/ent/convert.go b/cmd/atlas/internal/migrate/ent/convert.go
new file mode 100644
index 00000000..828c2bea
--- /dev/null
+++ b/cmd/atlas/internal/migrate/ent/convert.go
@@ -0,0 +1,42 @@
+// Copyright 2021-present The Atlas Authors. All rights reserved.
+// This source code is licensed under the Apache 2.0 license found
+// in the LICENSE file in the root directory of this source tree.
+
+// Code generated by entc, DO NOT EDIT.
+
+package ent
+
+import "ariga.io/atlas/sql/migrate"
+
+// SetRevision takes the values for each field from the given migrate.Revision.
+func (rc *RevisionCreate) SetRevision(rev *migrate.Revision) *RevisionCreate {
+	rc.SetID(rev.Version)
+	rc.SetDescription(rev.Description)
+	rc.SetType(rev.Type)
+	rc.SetApplied(rev.Applied)
+	rc.SetTotal(rev.Total)
+	rc.SetExecutedAt(rev.ExecutedAt)
+	rc.SetExecutionTime(rev.ExecutionTime)
+	rc.SetError(rev.Error)
+	rc.SetHash(rev.Hash)
+	rc.SetPartialHashes(rev.PartialHashes)
+	rc.SetOperatorVersion(rev.OperatorVersion)
+	return rc
+}
+
+// AtlasRevision returns an migrate.Revision from the current Revision.
+func (r *Revision) AtlasRevision() *migrate.Revision {
+	return &migrate.Revision{
+		Version:         r.ID,
+		Description:     r.Description,
+		Type:            r.Type,
+		Applied:         r.Applied,
+		Total:           r.Total,
+		ExecutedAt:      r.ExecutedAt,
+		ExecutionTime:   r.ExecutionTime,
+		Error:           r.Error,
+		Hash:            r.Hash,
+		PartialHashes:   r.PartialHashes,
+		OperatorVersion: r.OperatorVersion,
+	}
+}
diff --git a/cmd/atlas/internal/migrate/ent/entc.go b/cmd/atlas/internal/migrate/ent/entc.go
index 6047873a..8ea6b353 100644
--- a/cmd/atlas/internal/migrate/ent/entc.go
+++ b/cmd/atlas/internal/migrate/ent/entc.go
@@ -8,6 +8,7 @@ package main
 
 import (
 	"log"
+	"path/filepath"
 
 	"entgo.io/ent/entc"
 	"entgo.io/ent/entc/gen"
@@ -26,7 +27,7 @@ func main() {
 			gen.FeatureExecQuery,
 			gen.FeatureSchemaConfig,
 		},
-	})
+	}, entc.TemplateFiles(filepath.Join("template", "convert.tmpl")))
 	if err != nil {
 		log.Fatalf("running ent codegen: %v", err)
 	}
diff --git a/cmd/atlas/internal/migrate/ent/template/convert.tmpl b/cmd/atlas/internal/migrate/ent/template/convert.tmpl
new file mode 100644
index 00000000..36742b6e
--- /dev/null
+++ b/cmd/atlas/internal/migrate/ent/template/convert.tmpl
@@ -0,0 +1,36 @@
+{{/* gotype: entgo.io/ent/entc/gen.Graph */}}
+
+{{ define "convert" }}
+
+{{ $pkg := base $.Config.Package }}
+{{ template "header" $ }}
+
+import "ariga.io/atlas/sql/migrate"
+
+{{ range $n := $.Nodes }}
+    {{ if eq $n.Name "Revision" }}
+        {{ $builder := $n.CreateName }}
+        {{ $receiver := receiver $builder }}
+
+        // SetRevision takes the values for each field from the given migrate.Revision.
+        func ({{ $receiver }} *{{ $builder }}) SetRevision(rev *migrate.Revision) *{{ $builder }} {
+            {{ $receiver }}.SetID(rev.Version)
+            {{- range $f := $n.Fields }}
+                {{ $receiver }}.Set{{ $f.StructField }}(rev.{{ $f.StructField }})
+            {{- end }}
+            return {{ $receiver }}
+        }
+
+        // AtlasRevision returns an migrate.Revision from the current Revision.
+        func({{ $n.Receiver}} *Revision) AtlasRevision() *migrate.Revision {
+            return &migrate.Revision{
+                Version: r.ID,
+                {{- range $f := $n.Fields }}
+                    {{ $f.StructField }}: r.{{ $f.StructField }},
+                {{- end }}
+            }
+        }
+    {{ end }}
+{{ end }}
+
+{{ end }}
diff --git a/cmd/atlas/internal/migrate/migrate.go b/cmd/atlas/internal/migrate/migrate.go
index 7cbeb19d..585e5476 100644
--- a/cmd/atlas/internal/migrate/migrate.go
+++ b/cmd/atlas/internal/migrate/migrate.go
@@ -153,17 +153,7 @@ func (r *EntRevisions) Flush(ctx context.Context) error {
 // write attempts to write the given revision to the database.
 func (r *EntRevisions) write(ctx context.Context, rev *migrate.Revision) error {
 	return r.ec.Revision.Create().
-		SetID(rev.Version).
-		SetDescription(rev.Description).
-		SetType(rev.Type).
-		SetApplied(rev.Applied).
-		SetTotal(rev.Total).
-		SetExecutedAt(rev.ExecutedAt).
-		SetExecutionTime(rev.ExecutionTime).
-		SetError(rev.Error).
-		SetHash(rev.Hash).
-		SetPartialHashes(rev.PartialHashes).
-		SetOperatorVersion(rev.OperatorVersion).
+		SetRevision(rev).
 		OnConflict(sql.ConflictColumns(revision.FieldID)).
 		UpdateNewValues().
 		Exec(ctx)
diff --git a/doc/md/lint/analyzers.md b/doc/md/lint/analyzers.md
index ca04a309..59549af4 100644
--- a/doc/md/lint/analyzers.md
+++ b/doc/md/lint/analyzers.md
@@ -32,15 +32,15 @@ will be deleted from disk, with no way to recover it. There are definitely situa
 of change is desired, but they are relatively rare. Using the `destructive` ([GoDoc](https://pkg.go.dev/ariga.io/atlas@master/sql/sqlcheck/destructive))
 Analyzer, teams can detect this type of change and design workflows that prevent it from happening accidentally. 
 
-### Data-dependant Changes 
+### Data-dependent Changes 
 
-Data-dependant changes are changes to a database schema that _may_ succeed or fail, depending on the
+Data-dependent changes are changes to a database schema that _may_ succeed or fail, depending on the
 data that is stored in the database. For instance, consider a statement such as:
 
 ```sql
 ALTER TABLE `example`.`orders` ADD UNIQUE INDEX `idx_name` (`name`);
 ```
-This statement is considered data-dependant because if the `orders` table 
+This statement is considered data-dependent because if the `orders` table 
 contains duplicate values on the name column we will not be able to add a uniqueness
 constraint. Consider we added two records with the name `atlas` to the table:
 ```
@@ -62,4 +62,82 @@ This type of change is tricky because a developer trying to simulate it locally
 might succeed in performing it only to be surprised that their migration script
 fails in production, breaking a deployment sequence or causing other unexpected
 behavior. Using the `datadepend` ([GoDoc](https://pkg.go.dev/ariga.io/atlas@master/sql/sqlcheck/datadepend)) 
-Analyzer, teams can detect this risk early and account for it in pre-deployment checks to a database. 
\ No newline at end of file
+Analyzer, teams can detect this risk early and account for it in pre-deployment checks to a database. 
+
+## Checks
+
+The following schema change checks are provided by Atlas:
+
+| **Check**                          | **Short Description**                                                       |
+|------------------------------------|-----------------------------------------------------------------------------|
+| [**DS1**](#destructive-changes)    | Destructive changes                                                         |
+| [DS101](#DS101)                    | Schema was dropped                                                          |
+| [DS102](#DS102)                    | Table was dropped                                                           |
+| [DS103](#DS103)                    | Non-virtual column was dropped                                              |
+| [**MF1**](#data-dependent-changes) | Changes that might fail                                                     |
+| [MF101](#MF101)                    | Add unique index to existing column                                         |
+| [MF102](#MF102)                    | Modifying non-unique index to unique                                        |
+| [MF103](#MF103)                    | Adding a non-nullable column to an existing table                           |
+| **MY**                             | MySQL and MariaDB specific checks                                           |
+| [MY101](#MY101)                    | Adding a non-nullable column without a `DEFAULT` value to an existing table |
+
+
+#### DS101 {#DS101}
+
+Destructive change that is reported when a database schema was dropped. For example:
+
+```sql
+DROP SCHEMA test;
+```
+
+#### DS102 {#DS102}
+
+Destructive change that is reported when a table schema was dropped. For example:
+
+```sql
+DROP TABLE test.t;
+```
+
+#### DS103 {#DS103}
+
+Destructive change that is reported when a non-virtual column was dropped. For example:
+
+```sql
+ALTER TABLE t DROP COLUMN c;
+```
+
+#### MF101 {#MF101}
+
+Adding a unique index to a table might fail in case one of the indexed columns contain duplicate entries. For example:
+
+```sql
+CREATE UNIQUE INDEX i ON t(c);
+```
+
+#### MF102 {#MF102}
+
+Modifying a non-unique index to be unique might fail in case one of the indexed columns contain duplicate entries.
+
+:::note
+Since index modification is done with `DROP` and `CREATE`, this check will be reported only when analyzing changes
+programmatically or when working with the [declarative workflow](../concepts/workflows.md#declarative-migrations).
+:::
+
+#### MF103 {#MF103}
+
+Adding a non-nullable column to a table might fail in case the table is not empty. For example:
+
+```sql
+ALTER TABLE t ADD COLUMN c int NOT NULL;
+```
+
+#### MY101 {#MY101}
+
+Adding a non-nullable column to a table without a `DEFAULT` value implicitly sets existing rows with the column
+zero (default) value. For example:
+
+```sql
+ALTER TABLE t ADD COLUMN c int NOT NULL;
+// highlight-next-line
+-- Append column `c` to all existing rows with the value 0.
+```
\ No newline at end of file
diff --git a/doc/md/versioned/new.md b/doc/md/versioned/new.md
deleted file mode 100644
index 6ae65269..00000000
--- a/doc/md/versioned/new.md
+++ /dev/null
@@ -1,48 +0,0 @@
----
-id: new
-slug: /versioned/new
-title: Manual Migrations
----
-In some cases it is desirable to add a migration file manually. This could be done
-to provision resources that Atlas does not yet capture in its DDL (such as triggers and
-views) or to seed data with `INSERT` statements. 
-
-To manually add a new migration file to the directory use the `migrate new` command. 
-
-### Flags
-When using `migrate new` to create a new migration file users may supply the following flags:
-* `--dir` the URL of the migration directory, by default it is `file://migrations`, e.g a
-  directory named `migrations` in the current working directory.
-
-### Migration name
-Users may optionally add a final positional argument to set the name of the migration
-file.  This name will be appended to the migration version number in the filename as such:
-`<version>_<name>.sql`.
-
-### Recalculating the directory hash
-Atlas maintains a file named `atlas.sum` in the migration directory. This file  is used to 
-ensure the integrity of the migration directory and force developers to deal with 
-situations where migration order or contents was modified after the fact. 
-
-After manually editing the contents of a newly created migration file, the checksums for
-the directory must be recalculated. This can be done by running `atlas migrate hash --force`
-command.
-
-### Examples
-
-Creating a new migration file:
-```text
-atlas migrate new
-```
-
-Creating a new migration file named "add_user":
-```text
-atlas migrate new add_user
-```
-
-Creating a new migration file in a specific directory:
-```text
-atlas migrate new --dir file://custom/path/to/dir
-```
-
-
diff --git a/doc/md/versioned/new.mdx b/doc/md/versioned/new.mdx
new file mode 100644
index 00000000..44625623
--- /dev/null
+++ b/doc/md/versioned/new.mdx
@@ -0,0 +1,148 @@
+---
+id: new
+slug: /versioned/new
+title: Manual Migrations
+---
+
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+In some cases it is desirable to add a migration file manually. This could be done
+to provision resources that Atlas does not yet capture in its DDL (such as triggers and
+views) or to seed data with `INSERT` statements. 
+
+To manually add a new migration file to the directory use the `migrate new` command. 
+
+### Flags
+When using `migrate new` to create a new migration file users may supply the following flags:
+* `--dir` the URL of the migration directory, by default it is `file://migrations`, e.g a
+  directory named `migrations` in the current working directory.
+
+### Migration name
+Users may optionally add a final positional argument to set the name of the migration
+file.  This name will be appended to the migration version number in the filename as such:
+`<version>_<name>.sql`.
+
+### Custom statements delimiter
+
+The semicolon character (`;`) is recognized by Atlas as a statement delimiter. In some cases, however, the delimiter
+may need to be redefined because the semicolon itself is used in one of the DDL statements. For example, a stored
+program containing semicolon characters.
+
+#### Using `\n\n\n` as a delimiter:
+
+<Tabs
+defaultValue="migration_file"
+values={[
+{label: 'Migration file', value: 'migration_file'},
+{label: 'Scanned statements', value: 'statements'},
+]}>
+<TabItem value="migration_file">
+
+```sql
+//highlight-next-line-info
+-- atlas:delimiter \n\n\n
+
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+
+
+CALL dorepeat(1000);
+```
+
+</TabItem>
+<TabItem value="statements">
+
+Statement 1:
+
+```sql
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+```
+
+Statement 2:
+```sql
+CALL dorepeat(1000);
+```
+
+</TabItem>
+</Tabs>
+
+
+#### Using `-- end` as a delimiter:
+
+<Tabs
+defaultValue="migration_file"
+values={[
+{label: 'Migration file', value: 'migration_file'},
+{label: 'Scanned statements', value: 'statements'},
+]}>
+<TabItem value="migration_file">
+
+```sql
+//highlight-next-line-info
+-- atlas:delimiter -- end
+
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+-- end
+CALL dorepeat(1000);
+```
+
+</TabItem>
+<TabItem value="statements">
+
+Statement 1:
+
+```sql
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+```
+
+Statement 2:
+```sql
+CALL dorepeat(1000);
+```
+
+</TabItem>
+</Tabs>
+
+### Recalculating the directory hash
+Atlas maintains a file named `atlas.sum` in the migration directory. This file  is used to 
+ensure the integrity of the migration directory and force developers to deal with 
+situations where migration order or contents was modified after the fact. 
+
+After manually editing the contents of a newly created migration file, the checksums for
+the directory must be recalculated. This can be done by running `atlas migrate hash --force`
+command.
+
+### Examples
+
+Creating a new migration file:
+```text
+atlas migrate new
+```
+
+Creating a new migration file named "add_user":
+```text
+atlas migrate new add_user
+```
+
+Creating a new migration file in a specific directory:
+```text
+atlas migrate new --dir file://custom/path/to/dir
+```
+
+
diff --git a/doc/website/src/css/custom.css b/doc/website/src/css/custom.css
index 83b7590b..40ea5799 100644
--- a/doc/website/src/css/custom.css
+++ b/doc/website/src/css/custom.css
@@ -177,7 +177,7 @@ div[class^='announcementBar_'] {
   font-weight: bold;
 }
 
-.tabs-container  p {
+.tabs-container p {
   margin-bottom: 10px;
 }
 
@@ -201,4 +201,10 @@ div[class^='announcementBar_'] {
 }
 .code-block-info-line span {
   color: rgb(191, 199, 213)!important;
+}
+
+/* Full-width checks table. */
+.docs-doc-id-lint\/analyzers table {
+  display:table;
+  width:100%;
 }
\ No newline at end of file
diff --git a/sql/migrate/lex.go b/sql/migrate/lex.go
index 23fc2ce1..4e946df3 100644
--- a/sql/migrate/lex.go
+++ b/sql/migrate/lex.go
@@ -72,6 +72,7 @@ func (l *lex) stmt() (stmt string, err error) {
 		if l.delim != delimiter {
 			stmt = strings.TrimSuffix(stmt, l.delim)
 		}
+		stmt = strings.TrimSpace(stmt)
 	}()
 	// Trim trailing whitespace.
 	l.skipSpaces()
@@ -96,15 +97,16 @@ func (l *lex) stmt() (stmt string, err error) {
 			if err := l.skipQuote(r); err != nil {
 				return "", err
 			}
+		// Delimiters take precedence over comments.
+		case strings.HasPrefix(l.input[l.pos-l.width:], l.delim) && l.depth == 0:
+			l.pos += len(l.delim) - l.width
+			return l.input[:l.pos], nil
 		case r == '#':
 			l.skipComment("#", "\n")
 		case r == '-' && l.next() == '-':
 			l.skipComment("--", "\n")
 		case r == '/' && l.next() == '*':
 			l.skipComment("/*", "*/")
-		case strings.HasPrefix(l.input[l.pos-l.width:], l.delim) && l.depth == 0:
-			l.pos += len(l.delim) - l.width
-			return l.input[:l.pos], nil
 		}
 	}
 }
diff --git a/sql/mysql/driver.go b/sql/mysql/driver.go
index f6645254..39376297 100644
--- a/sql/mysql/driver.go
+++ b/sql/mysql/driver.go
@@ -169,12 +169,10 @@ func (d *Driver) CheckClean(ctx context.Context, revT *migrate.TableIdent) error
 		return err
 	}
 	if s != nil {
-		switch {
-		case len(s.Tables) == 0, (revT.Schema == "" || s.Name == revT.Schema) && len(s.Tables) == 1 && s.Tables[0].Name == revT.Name:
+		if len(s.Tables) == 0 || ((revT.Schema == "" || s.Name == revT.Schema) && len(s.Tables) == 1 && s.Tables[0].Name == revT.Name) {
 			return nil
-		default:
-			return &migrate.NotCleanError{Reason: fmt.Sprintf("found table %q in schema %q", s.Tables[0].Name, s.Name)}
 		}
+		return &migrate.NotCleanError{Reason: fmt.Sprintf("found table %q in schema %q", s.Tables[0].Name, s.Name)}
 	}
 	r, err := d.InspectRealm(ctx, nil)
 	if err != nil {
diff --git a/sql/mysql/mysqlcheck/mysqlcheck.go b/sql/mysql/mysqlcheck/mysqlcheck.go
index c39c3785..8a1934de 100644
--- a/sql/mysql/mysqlcheck/mysqlcheck.go
+++ b/sql/mysql/mysqlcheck/mysqlcheck.go
@@ -17,6 +17,9 @@ import (
 	"ariga.io/atlas/sql/sqlcheck/destructive"
 )
 
+// codeImplicitUpdate is a MySQL specific code for reporting implicit update.
+var codeImplicitUpdate = sqlcheck.Code("MY101")
+
 func addNotNull(p *datadepend.ColumnPass) (diags []sqlcheck.Diagnostic, err error) {
 	// Two types of reporting, implicit rows update and
 	// changes that may cause the migration to fail.
@@ -31,7 +34,8 @@ func addNotNull(p *datadepend.ColumnPass) (diags []sqlcheck.Diagnostic, err erro
 	}
 	implicitUpdate := func(tt, v string) {
 		diags = append(diags, sqlcheck.Diagnostic{
-			Pos: p.Change.Pos,
+			Code: codeImplicitUpdate,
+			Pos:  p.Change.Pos,
 			Text: fmt.Sprintf(
 				"Adding a non-nullable %q column %q on table %q without a default value implicitly sets existing rows with %s",
 				tt, p.Column.Name, p.Table.Name, v,
diff --git a/sql/sqlcheck/datadepend/datadepend.go b/sql/sqlcheck/datadepend/datadepend.go
index 1a5cae07..5e7a6a95 100644
--- a/sql/sqlcheck/datadepend/datadepend.go
+++ b/sql/sqlcheck/datadepend/datadepend.go
@@ -42,7 +42,7 @@ type (
 	ColumnHandler func(*ColumnPass) ([]sqlcheck.Diagnostic, error)
 )
 
-// New creates a new data-dependant analyzer with the given options.
+// New creates a new data-dependent analyzer with the given options.
 func New(r *schemahcl.Resource, h Handler) (*Analyzer, error) {
 	az := &Analyzer{Handler: h}
 	if r, ok := r.Resource("data_depend"); ok {
@@ -58,6 +58,13 @@ func (a *Analyzer) Analyze(ctx context.Context, p *sqlcheck.Pass) error {
 	return a.Report(p, a.Diagnostics(ctx, p))
 }
 
+// List of codes.
+var (
+	codeAddUniqueI  = sqlcheck.Code("MF101")
+	codeModUniqueI  = sqlcheck.Code("MF102")
+	codeAddNotNullC = sqlcheck.Code("MF103")
+)
+
 // Diagnostics runs the common analysis on the file and returns its diagnostics.
 func (a *Analyzer) Diagnostics(_ context.Context, p *sqlcheck.Pass) (diags []sqlcheck.Diagnostic) {
 	for _, sc := range p.File.Changes {
@@ -82,6 +89,7 @@ func (a *Analyzer) Diagnostics(_ context.Context, p *sqlcheck.Pass) (diags []sql
 					// A unique index was added on an existing column.
 					if c.I.Unique && column != nil {
 						diags = append(diags, sqlcheck.Diagnostic{
+							Code: codeAddUniqueI,
 							Pos:  sc.Pos,
 							Text: fmt.Sprintf("Adding a unique index %q on table %q might fail in case column %q contains duplicate entries", c.I.Name, m.T.Name, column.Name),
 						})
@@ -89,6 +97,7 @@ func (a *Analyzer) Diagnostics(_ context.Context, p *sqlcheck.Pass) (diags []sql
 				case *schema.ModifyIndex:
 					if c.Change.Is(schema.ChangeUnique) && c.To.Unique && p.File.IndexSpan(m.T, c.To)&sqlcheck.SpanAdded == 0 {
 						diags = append(diags, sqlcheck.Diagnostic{
+							Code: codeModUniqueI,
 							Pos:  sc.Pos,
 							Text: fmt.Sprintf("Modifying an index %q on table %q might fail in case of duplicate entries", c.To.Name, m.T.Name),
 						})
@@ -101,6 +110,12 @@ func (a *Analyzer) Diagnostics(_ context.Context, p *sqlcheck.Pass) (diags []sql
 						if err != nil {
 							return
 						}
+						for i := range d {
+							// In case there is no driver-specific code.
+							if d[i].Code == "" {
+								d[i].Code = codeAddNotNullC
+							}
+						}
 						diags = append(diags, d...)
 					}
 				}
diff --git a/sql/sqlcheck/destructive/destructive.go b/sql/sqlcheck/destructive/destructive.go
index 711e3bd9..12457128 100644
--- a/sql/sqlcheck/destructive/destructive.go
+++ b/sql/sqlcheck/destructive/destructive.go
@@ -33,6 +33,13 @@ func New(r *schemahcl.Resource) (*Analyzer, error) {
 	return az, nil
 }
 
+// List of codes.
+var (
+	codeDropS = sqlcheck.Code("DS101")
+	codeDropT = sqlcheck.Code("DS102")
+	codeDropC = sqlcheck.Code("DS103")
+)
+
 // Analyze implements sqlcheck.Analyzer.
 func (a *Analyzer) Analyze(_ context.Context, p *sqlcheck.Pass) error {
 	var diags []sqlcheck.Diagnostic
@@ -50,11 +57,16 @@ func (a *Analyzer) Analyze(_ context.Context, p *sqlcheck.Pass) error {
 					case n > 1:
 						text = fmt.Sprintf("Dropping non-empty schema %q with %d tables", c.S.Name, n)
 					}
-					diags = append(diags, sqlcheck.Diagnostic{Pos: sc.Pos, Text: text})
+					diags = append(diags, sqlcheck.Diagnostic{
+						Code: codeDropS,
+						Pos:  sc.Pos,
+						Text: text,
+					})
 				}
 			case *schema.DropTable:
 				if p.File.SchemaSpan(c.T.Schema) != sqlcheck.SpanDropped && p.File.TableSpan(c.T) != sqlcheck.SpanTemporary {
 					diags = append(diags, sqlcheck.Diagnostic{
+						Code: codeDropT,
 						Pos:  sc.Pos,
 						Text: fmt.Sprintf("Dropping table %q", c.T.Name),
 					})
@@ -67,6 +79,7 @@ func (a *Analyzer) Analyze(_ context.Context, p *sqlcheck.Pass) error {
 					}
 					if g := (schema.GeneratedExpr{}); !sqlx.Has(d.C.Attrs, &g) || strings.ToUpper(g.Type) != "VIRTUAL" {
 						diags = append(diags, sqlcheck.Diagnostic{
+							Code: codeDropC,
 							Pos:  sc.Pos,
 							Text: fmt.Sprintf("Dropping non-virtual column %q", d.C.Name),
 						})
diff --git a/sql/sqlcheck/sqlcheck.go b/sql/sqlcheck/sqlcheck.go
index 11be0fdc..f779512c 100644
--- a/sql/sqlcheck/sqlcheck.go
+++ b/sql/sqlcheck/sqlcheck.go
@@ -73,6 +73,7 @@ type (
 	Diagnostic struct {
 		Pos  int    // Diagnostic position.
 		Text string // Diagnostic text.
+		Code string // Code describes the check. For example, DS101
 	}
 
 	// ReportWriter represents a writer for analysis reports.
@@ -236,6 +237,18 @@ func (f *File) tableSpan(t *schema.Table) *tableSpan {
 	return f.spans[t.Schema.Name].tables[t.Name]
 }
 
+// codes registry
+var codes sync.Map
+
+// Code stores the given code in the registry.
+// It protects from duplicate analyzers' codes.
+func Code(code string) string {
+	if _, loaded := codes.LoadOrStore(code, struct{}{}); loaded {
+		panic("sqlcheck: Code called twice for " + code)
+	}
+	return code
+}
+
 // drivers specific analyzers.
 var drivers sync.Map
 
diff --git a/sql/sqlite/driver.go b/sql/sqlite/driver.go
index cc8b4089..dfea83bb 100644
--- a/sql/sqlite/driver.go
+++ b/sql/sqlite/driver.go
@@ -8,7 +8,6 @@ import (
 	"context"
 	"errors"
 	"fmt"
-	"io/ioutil"
 	"net/url"
 	"os"
 	"path/filepath"
@@ -135,7 +134,7 @@ func (d *Driver) CheckClean(ctx context.Context, revT *migrate.TableIdent) error
 // Lock implements the schema.Locker interface.
 func (d *Driver) Lock(_ context.Context, name string, timeout time.Duration) (schema.UnlockFunc, error) {
 	path := filepath.Join(os.TempDir(), name+".lock")
-	c, err := ioutil.ReadFile(path)
+	c, err := os.ReadFile(path)
 	if errors.Is(err, os.ErrNotExist) {
 		return acquireLock(path, timeout)
 	}


Test patch:
diff --git a/cmd/atlas/internal/lint/run_test.go b/cmd/atlas/internal/lint/run_test.go
index f1de95b4..18e84361 100644
--- a/cmd/atlas/internal/lint/run_test.go
+++ b/cmd/atlas/internal/lint/run_test.go
@@ -91,10 +91,10 @@ sqlite3, migrations
 Steps:
 {"Name":"Detect New Migration Files","Text":"Found 1 new migration files (from 2 total)"}
 {"Name":"Replay Migration Files","Text":"Loaded 1 changes on dev database"}
-{"Name":"Analyze 2.sql","Text":"1 reports were found in analysis","Result":{"Name":"2.sql","Text":"CREATE TABLE pets (id INT)\nDROP TABLE users","Reports":[{"Text":"Report 2","Diagnostics":[{"Pos":1,"Text":"Diagnostic 1"},{"Pos":2,"Text":"Diagnostic 2"}]}]}}
+{"Name":"Analyze 2.sql","Text":"1 reports were found in analysis","Result":{"Name":"2.sql","Text":"CREATE TABLE pets (id INT)\nDROP TABLE users","Reports":[{"Text":"Report 2","Diagnostics":[{"Pos":1,"Text":"Diagnostic 1","Code":""},{"Pos":2,"Text":"Diagnostic 2","Code":""}]}]}}
 
 Files:
-{"Name":"2.sql","Text":"CREATE TABLE pets (id INT)\nDROP TABLE users","Reports":[{"Text":"Report 2","Diagnostics":[{"Pos":1,"Text":"Diagnostic 1"},{"Pos":2,"Text":"Diagnostic 2"}]}]}
+{"Name":"2.sql","Text":"CREATE TABLE pets (id INT)\nDROP TABLE users","Reports":[{"Text":"Report 2","Diagnostics":[{"Pos":1,"Text":"Diagnostic 1","Code":""},{"Pos":2,"Text":"Diagnostic 2","Code":""}]}]}
 
 
 Current Schema:
diff --git a/sql/migrate/lex_test.go b/sql/migrate/lex_test.go
index 2f290f07..ac93d968 100644
--- a/sql/migrate/lex_test.go
+++ b/sql/migrate/lex_test.go
@@ -24,6 +24,6 @@ func TestLocalFile_Stmts(t *testing.T) {
 		require.NoError(t, err)
 		buf, err := os.ReadFile(filepath.Join(path, f.Name()+".golden"))
 		require.NoError(t, err)
-		require.Equal(t, string(buf), strings.Join(stmts, "\n\n"))
+		require.Equal(t, string(buf), strings.Join(stmts, "\n-- end --\n"))
 	}
 }
diff --git a/sql/migrate/testdata/lex/1.sql.golden b/sql/migrate/testdata/lex/1.sql.golden
index 0375e1de..663bcc1c 100644
--- a/sql/migrate/testdata/lex/1.sql.golden
+++ b/sql/migrate/testdata/lex/1.sql.golden
@@ -1,19 +1,19 @@
 CREATE TABLE t1(id int);
-
+-- end --
 CREATE TABLE t2(id int);
-
+-- end --
 CREATE TABLE t3(id int);
-
+-- end --
 CREATE TABLE t4(
     id int,
     name varchar(255)
 );
-
+-- end --
 CREATE TABLE t4(
     id int,
     `name` varchar(255) DEFAULT ';'
 ) ENGINE=InnoDB;
-
+-- end --
 CREATE TABLE t5(
     id int
     /* comment */
diff --git a/sql/migrate/testdata/lex/10_delimiter_comment.sql b/sql/migrate/testdata/lex/10_delimiter_comment.sql
new file mode 100644
index 00000000..d097d6eb
--- /dev/null
+++ b/sql/migrate/testdata/lex/10_delimiter_comment.sql
@@ -0,0 +1,9 @@
+-- atlas:delimiter -- end
+
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+-- end
+CALL dorepeat(1000);
diff --git a/sql/migrate/testdata/lex/10_delimiter_comment.sql.golden b/sql/migrate/testdata/lex/10_delimiter_comment.sql.golden
new file mode 100644
index 00000000..2a19a94a
--- /dev/null
+++ b/sql/migrate/testdata/lex/10_delimiter_comment.sql.golden
@@ -0,0 +1,7 @@
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+-- end --
+CALL dorepeat(1000);
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/2_mysql.sql.golden b/sql/migrate/testdata/lex/2_mysql.sql.golden
index 5dc690e2..71c7c986 100644
--- a/sql/migrate/testdata/lex/2_mysql.sql.golden
+++ b/sql/migrate/testdata/lex/2_mysql.sql.golden
@@ -1,23 +1,23 @@
 create table t1 (b char(0));
-
+-- end --
 create table t1 (b char(0) not null);
-
+-- end --
 create table if not exists t1 (b char(0) not null);
-
+-- end --
 create table t2 engine=heap select * from t1;
-
+-- end --
 create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid)) engine=heap;
-
+-- end --
 create table mysqltest.$test1 (a$1 int, $b int, c$ int);
-
+-- end --
 create table t2 (b int) select a as b, a+1 as b from t1;
-
+-- end --
 create table t1 select if('2002'='2002','Y','N');
-
+-- end --
 create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
-
+-- end --
 insert into t1 values ("a", 1), ("b", 2);
-
+-- end --
 create table t2
 select
     a,
@@ -30,21 +30,21 @@ select
     ifnull(h,cast('yet another binary data' as binary))   as h,
     addtime(cast('1:0:0' as time),cast('1:0:0' as time))  as dd
 from t1;
-
+-- end --
 CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext);
-
+-- end --
 INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL);
-
+-- end --
 create table t1 (
     a varchar(112) charset utf8 collate utf8_bin not null,
     primary key (a)
 ) select 'test' as a ;
-
+-- end --
 create table טבלה_של_אריאל
 (
     כמות int
 );
-
+-- end --
 CREATE TABLE t1(
     c1 INT DEFAULT 12 COMMENT 'column1',
     c2 INT NULL COMMENT 'column2',
diff --git a/sql/migrate/testdata/lex/3_delimiter.sql.golden b/sql/migrate/testdata/lex/3_delimiter.sql.golden
index d3f85e97..5642c3ae 100644
--- a/sql/migrate/testdata/lex/3_delimiter.sql.golden
+++ b/sql/migrate/testdata/lex/3_delimiter.sql.golden
@@ -1,3 +1,3 @@
 CREATE INDEX i1 ON t1(c1)
-
+-- end --
 CREATE INDEX i2 ON t2(c2)
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/4_delimiter.sql.golden b/sql/migrate/testdata/lex/4_delimiter.sql.golden
index d3f85e97..5642c3ae 100644
--- a/sql/migrate/testdata/lex/4_delimiter.sql.golden
+++ b/sql/migrate/testdata/lex/4_delimiter.sql.golden
@@ -1,3 +1,3 @@
 CREATE INDEX i1 ON t1(c1)
-
+-- end --
 CREATE INDEX i2 ON t2(c2)
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/5_delimiter.sql b/sql/migrate/testdata/lex/5_delimiter.sql
index e4563c40..354321bf 100644
--- a/sql/migrate/testdata/lex/5_delimiter.sql
+++ b/sql/migrate/testdata/lex/5_delimiter.sql
@@ -1,7 +1,6 @@
--- atlas:delimiter ATLAS_DELIMITER
+-- atlas:delimiter \n-- end --\n
 
 DELIMITER $$
-
 CREATE DEFINER='boring' PROCEDURE proc ()
     COMMENT 'ATLAS_DELIMITER'
     SQL SECURITY INVOKER
@@ -14,7 +13,8 @@ BEGIN
 
     SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' background thread', IF(@rows != 1, 's', '')) AS summary;
 END$$
-
 DELIMITER ;
 
+-- end --
+
 CALL proc();
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/5_delimiter.sql.golden b/sql/migrate/testdata/lex/5_delimiter.sql.golden
index 3edc85f5..549ebcdd 100644
--- a/sql/migrate/testdata/lex/5_delimiter.sql.golden
+++ b/sql/migrate/testdata/lex/5_delimiter.sql.golden
@@ -1,5 +1,4 @@
 DELIMITER $$
-
 CREATE DEFINER='boring' PROCEDURE proc ()
     COMMENT 'ATLAS_DELIMITER'
     SQL SECURITY INVOKER
@@ -12,7 +11,6 @@ BEGIN
 
     SELECT CONCAT('Enabled ', @rows := ROW_COUNT(), ' background thread', IF(@rows != 1, 's', '')) AS summary;
 END$$
-
 DELIMITER ;
-
+-- end --
 CALL proc();
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/6_skip_comment.sql.golden b/sql/migrate/testdata/lex/6_skip_comment.sql.golden
index 4bb3b004..013d94c6 100644
--- a/sql/migrate/testdata/lex/6_skip_comment.sql.golden
+++ b/sql/migrate/testdata/lex/6_skip_comment.sql.golden
@@ -1,15 +1,15 @@
 CREATE TABLE t1(id int);
-
+-- end --
 CREATE TABLE t2(id int);
-
+-- end --
 CREATE TABLE t3(id int);
-
+-- end --
 CREATE TABLE t4(
     id int
     /* comment */
     -- comment
 ) ENGINE=InnoDB;
-
+-- end --
 CREATE TABLE t5(
     id int
     /* comment */
diff --git a/sql/migrate/testdata/lex/7_delimiter_2n.sql b/sql/migrate/testdata/lex/7_delimiter_2n.sql
new file mode 100644
index 00000000..5cb0d166
--- /dev/null
+++ b/sql/migrate/testdata/lex/7_delimiter_2n.sql
@@ -0,0 +1,14 @@
+-- atlas:delimiter \n\n
+
+CREATE EXTENSION IF NOT EXISTS unaccent;
+
+DELIMITER $$
+CREATE OR REPLACE FUNCTION public.slugify(
+    v TEXT
+) RETURNS TEXT STRICT IMMUTABLE AS $$
+BEGIN
+    RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
+END;
+$$
+LANGUAGE plpgsql;
+DELIMITER ;
diff --git a/sql/migrate/testdata/lex/7_delimiter_2n.sql.golden b/sql/migrate/testdata/lex/7_delimiter_2n.sql.golden
new file mode 100644
index 00000000..a1885a5d
--- /dev/null
+++ b/sql/migrate/testdata/lex/7_delimiter_2n.sql.golden
@@ -0,0 +1,12 @@
+CREATE EXTENSION IF NOT EXISTS unaccent;
+-- end --
+DELIMITER $$
+CREATE OR REPLACE FUNCTION public.slugify(
+    v TEXT
+) RETURNS TEXT STRICT IMMUTABLE AS $$
+BEGIN
+    RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
+END;
+$$
+LANGUAGE plpgsql;
+DELIMITER ;
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/8_delimiter_3n.sql b/sql/migrate/testdata/lex/8_delimiter_3n.sql
new file mode 100644
index 00000000..5d35bbd1
--- /dev/null
+++ b/sql/migrate/testdata/lex/8_delimiter_3n.sql
@@ -0,0 +1,15 @@
+-- atlas:delimiter \n\n\n
+
+CREATE EXTENSION IF NOT EXISTS unaccent;
+
+
+DELIMITER $$
+CREATE OR REPLACE FUNCTION public.slugify(
+    v TEXT
+) RETURNS TEXT STRICT IMMUTABLE AS $$
+BEGIN
+    RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
+END;
+$$
+LANGUAGE plpgsql;
+DELIMITER ;
diff --git a/sql/migrate/testdata/lex/8_delimiter_3n.sql.golden b/sql/migrate/testdata/lex/8_delimiter_3n.sql.golden
new file mode 100644
index 00000000..a1885a5d
--- /dev/null
+++ b/sql/migrate/testdata/lex/8_delimiter_3n.sql.golden
@@ -0,0 +1,12 @@
+CREATE EXTENSION IF NOT EXISTS unaccent;
+-- end --
+DELIMITER $$
+CREATE OR REPLACE FUNCTION public.slugify(
+    v TEXT
+) RETURNS TEXT STRICT IMMUTABLE AS $$
+BEGIN
+    RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
+END;
+$$
+LANGUAGE plpgsql;
+DELIMITER ;
\ No newline at end of file
diff --git a/sql/migrate/testdata/lex/9_delimiter_3n.sql b/sql/migrate/testdata/lex/9_delimiter_3n.sql
new file mode 100644
index 00000000..e07af836
--- /dev/null
+++ b/sql/migrate/testdata/lex/9_delimiter_3n.sql
@@ -0,0 +1,10 @@
+-- atlas:delimiter \n\n\n
+
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+
+
+CALL dorepeat(1000);
diff --git a/sql/migrate/testdata/lex/9_delimiter_3n.sql.golden b/sql/migrate/testdata/lex/9_delimiter_3n.sql.golden
new file mode 100644
index 00000000..2a19a94a
--- /dev/null
+++ b/sql/migrate/testdata/lex/9_delimiter_3n.sql.golden
@@ -0,0 +1,7 @@
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+    SET @x = 0;
+    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END;
+-- end --
+CALL dorepeat(1000);
\ No newline at end of file