Repository: supabase/cli
Pull request #395 (2022-08-22 02:36:22)

chore: implement token based statement splitter
## What kind of change does this PR introduce?

Splitting long sql string into multiple statements provides clearer debug logs when applying migration.

## What is the current behavior?

Current splitter fails to ignore `;` when it's inside a dollar quote. 

## What is the new behavior?

The core problem is to figure out whether the current `;` separator is inside an escaped string literal. PostgreSQL has multiple ways of opening a string literal, `$$`, `'`, `--`, `/*`, etc. We use a FSM to guarantee these states are entered exclusively. If not in one of the above escape states, the next `;` can be parsed as statement separator. 

Each statement is split as it is, without removing comments or white spaces. 

## Additional context

Add any other context or screenshots.


coveralls: ## Pull Request Test Coverage Report for [Build 2900903349](https://coveralls.io/builds/51823761)

* **114** of **119**   **(95.8%)**  changed or added relevant lines in **4** files are covered.
* No unchanged relevant lines lost coverage.
* Overall coverage increased (+**1.4%**) to **27.15%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [internal/db/diff/diff.go](https://coveralls.io/builds/51823761/source?filename=internal%2Fdb%2Fdiff%2Fdiff.go#L84) | 0 | 1 | 0.0%
| [internal/db/diff/migra.go](https://coveralls.io/builds/51823761/source?filename=internal%2Fdb%2Fdiff%2Fmigra.go#L47) | 12 | 16 | 75.0%
<!-- | **Total:** | **114** | **119** | **95.8%** | -->


|  Totals | [![Coverage Status](https://coveralls.io/builds/51823761/badge)](https://coveralls.io/builds/51823761) |
| :-- | --: |
| Change from base [Build 2889222729](https://coveralls.io/builds/51794129): |  1.4% |
| Covered Lines: | 1370 |
| Relevant Lines: | 5046 |

---
##### 💛  - [Coveralls](https://coveralls.io)


Patch:
diff --git a/go.mod b/go.mod
index a29ddf14..e62c9bf9 100644
--- a/go.mod
+++ b/go.mod
@@ -76,7 +76,7 @@ require (
 	github.com/hashicorp/hcl v1.0.0 // indirect
 	github.com/inconshreveable/mousetrap v1.0.0 // indirect
 	github.com/jackc/chunkreader/v2 v2.0.1 // indirect
-	github.com/jackc/pgconn v1.11.0 // indirect
+	github.com/jackc/pgconn v1.11.0
 	github.com/jackc/pgerrcode v0.0.0-20220416144525-469b46aa5efa
 	github.com/jackc/pgio v1.0.0 // indirect
 	github.com/jackc/pgmock v0.0.0-20210724152146-4ad1a8207f65
diff --git a/internal/db/diff/diff.go b/internal/db/diff/diff.go
index bf501828..6eacc00b 100644
--- a/internal/db/diff/diff.go
+++ b/internal/db/diff/diff.go
@@ -81,7 +81,7 @@ func run(p utils.Program) error {
 
 	// 1. Create shadow db and run migrations
 	{
-		if err := createShadowDb(ctx, utils.DbId, utils.ShadowDbName); err != nil {
+		if err := ResetDatabase(ctx, utils.DbId, utils.ShadowDbName); err != nil {
 			return err
 		}
 
diff --git a/internal/db/diff/migra.go b/internal/db/diff/migra.go
index 1fb76843..0c15fb76 100644
--- a/internal/db/diff/migra.go
+++ b/internal/db/diff/migra.go
@@ -16,6 +16,7 @@ import (
 	"github.com/spf13/viper"
 	"github.com/supabase/cli/internal/debug"
 	"github.com/supabase/cli/internal/utils"
+	"github.com/supabase/cli/internal/utils/parser"
 )
 
 var (
@@ -43,13 +44,13 @@ func RunMigra(ctx context.Context, schema []string, file string, fsys afero.Fs)
 	}
 
 	fmt.Fprintln(os.Stderr, "Creating shadow database...")
-	if err := createShadowDb(ctx, utils.DbId, utils.ShadowDbName); err != nil {
+	if err := ResetDatabase(ctx, utils.DbId, utils.ShadowDbName); err != nil {
 		return err
 	}
 
 	fmt.Fprintln(os.Stderr, "Initialising schema...")
 	url := fmt.Sprintf("postgresql://postgres:postgres@localhost:%d/%s", utils.Config.Db.Port, utils.ShadowDbName)
-	if err := applyMigrations(ctx, url, fsys, opts...); err != nil {
+	if err := ApplyMigrations(ctx, url, fsys, opts...); err != nil {
 		return err
 	}
 
@@ -74,29 +75,9 @@ func RunMigra(ctx context.Context, schema []string, file string, fsys afero.Fs)
 	return SaveDiff(out, file, fsys)
 }
 
-func toBatchQuery(contents string) (batch pgx.Batch) {
-	var lines []string
-	for _, line := range strings.Split(contents, "\n") {
-		trimmed := strings.TrimSpace(line)
-		if len(trimmed) == 0 || strings.HasPrefix(trimmed, "--") {
-			continue
-		}
-		lines = append(lines, trimmed)
-		if strings.HasSuffix(trimmed, ";") {
-			query := strings.Join(lines, "\n")
-			batch.Queue(query[:len(query)-1])
-			lines = nil
-		}
-	}
-	if len(lines) > 0 {
-		batch.Queue(strings.Join(lines, "\n"))
-	}
-	return batch
-}
-
-// Creates a fresh database inside supabase_cli_db container.
-func createShadowDb(ctx context.Context, container, shadow string) error {
-	// Reset shadow database
+// Creates a fresh database inside a Postgres container.
+func ResetDatabase(ctx context.Context, container, shadow string) error {
+	// Our initial schema should not exceed the maximum size of an env var, ~32KB
 	env := []string{"DB_NAME=" + shadow, "SCHEMA=" + utils.InitialSchemaSql}
 	cmd := []string{"/bin/bash", "-c", resetShadowScript}
 	if _, err := utils.DockerExecOnce(ctx, container, env, cmd); err != nil {
@@ -105,15 +86,24 @@ func createShadowDb(ctx context.Context, container, shadow string) error {
 	return nil
 }
 
+func ToBatchQuery(stats []string) (batch pgx.Batch) {
+	for _, line := range stats {
+		trim := strings.TrimSpace(strings.TrimRight(line, ";"))
+		if len(trim) > 0 {
+			batch.Queue(trim)
+		}
+	}
+	return batch
+}
+
 // Applies local migration scripts to a database.
-func applyMigrations(ctx context.Context, url string, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
+func ApplyMigrations(ctx context.Context, url string, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
 	// Parse connection url
 	config, err := pgx.ParseConfig(url)
 	if err != nil {
 		return err
 	}
 	// Apply config overrides
-	config.PreferSimpleProtocol = true
 	for _, op := range options {
 		op(config)
 	}
@@ -140,11 +130,13 @@ func applyMigrations(ctx context.Context, url string, fsys afero.Fs, options ...
 				}
 			}
 			fmt.Fprintln(os.Stderr, "Applying migration "+utils.Bold(migration.Name())+"...")
-			contents, err := afero.ReadFile(fsys, filepath.Join(utils.MigrationsDir, migration.Name()))
+			sql, err := fsys.Open(filepath.Join(utils.MigrationsDir, migration.Name()))
 			if err != nil {
 				return err
 			}
-			batch := toBatchQuery(string(contents))
+			defer sql.Close()
+			// Batch migration commands
+			batch := ToBatchQuery(parser.Split(sql))
 			if err := conn.SendBatch(ctx, &batch).Close(); err != nil {
 				return err
 			}
diff --git a/internal/init/init.go b/internal/init/init.go
index b753e04d..003f09d3 100644
--- a/internal/init/init.go
+++ b/internal/init/init.go
@@ -14,7 +14,7 @@ var (
 	//go:embed templates/init_gitignore
 	initGitignore []byte
 
-	errAlreadyInitialized = errors.New("Project already initialized. Remove " + utils.Bold("supabase") + " to reinitialize.")
+	errAlreadyInitialized = errors.New("Project already initialized. Remove " + utils.Bold(utils.ConfigPath) + " to reinitialize.")
 )
 
 func Run(fsys afero.Fs) error {
diff --git a/internal/utils/parser/state.go b/internal/utils/parser/state.go
new file mode 100644
index 00000000..0ceac07f
--- /dev/null
+++ b/internal/utils/parser/state.go
@@ -0,0 +1,137 @@
+package parser
+
+import (
+	"bytes"
+	"unicode"
+	"unicode/utf8"
+)
+
+type State interface {
+	// Return nil to emit token
+	Next(r rune, data []byte) State
+}
+
+// Initial state: ready to parse next token
+type ReadyState struct{}
+
+func (s *ReadyState) Next(r rune, data []byte) State {
+	switch r {
+	// ; never appears in identifiers so it's safe to ignore "
+	case '$':
+		offset := len(data) - utf8.RuneLen(r)
+		return &TagState{offset: offset}
+	case '\'':
+		return &QuoteState{}
+	case '-':
+		return &CommentState{}
+	case '/':
+		return &BlockState{}
+	case '\\':
+		return &EscapeState{}
+	case ';':
+		// Emit token
+		return nil
+	}
+	return s
+}
+
+// Opened a line comment
+type CommentState struct{}
+
+func (s *CommentState) Next(r rune, data []byte) State {
+	if r == '-' {
+		// No characters are escaped in comments, which is the same as dollar
+		return &DollarState{delimiter: []byte{'\n'}}
+	}
+	// Break out of comment state
+	state := &ReadyState{}
+	return state.Next(r, data)
+}
+
+// Opened a block comment
+type BlockState struct {
+	depth int
+}
+
+func (s *BlockState) Next(r rune, data []byte) State {
+	const open = "/*"
+	const close = "*/"
+	window := data[len(data)-2:]
+	if bytes.Equal(window, []byte(open)) {
+		s.depth += 1
+		return s
+	}
+	if s.depth == 0 {
+		// Break out of block state
+		state := &ReadyState{}
+		return state.Next(r, data)
+	}
+	if bytes.Equal(window, []byte(close)) {
+		s.depth -= 1
+		if s.depth == 0 {
+			return &ReadyState{}
+		}
+	}
+	return s
+}
+
+// Opened a single quote '
+type QuoteState struct {
+	escape bool
+}
+
+func (s *QuoteState) Next(r rune, data []byte) State {
+	if s.escape {
+		// Preserve escaped quote ''
+		if r == '\'' {
+			s.escape = false
+			return s
+		}
+		// Break out of quote state
+		state := &ReadyState{}
+		return state.Next(r, data)
+	}
+	if r == '\'' {
+		s.escape = true
+	}
+	return s
+}
+
+// Opened a dollar quote, no characters are ever esacped.
+type DollarState struct {
+	delimiter []byte
+}
+
+func (s *DollarState) Next(r rune, data []byte) State {
+	window := data[len(data)-len(s.delimiter):]
+	if bytes.Equal(window, s.delimiter) {
+		// Break out of block state
+		return &ReadyState{}
+	}
+	return s
+}
+
+// Opened a tag, ie. $tag$
+type TagState struct {
+	offset int
+}
+
+func (s *TagState) Next(r rune, data []byte) State {
+	if r == '$' {
+		return &DollarState{delimiter: data[s.offset:]}
+	}
+	// Valid tag: https://www.postgresql.org/docs/current/sql-syntax-lexical.html
+	if unicode.IsLetter(r) || unicode.IsDigit(r) || r == '_' {
+		return s
+	}
+	// Break out of tag state
+	state := &ReadyState{}
+	return state.Next(r, data)
+}
+
+// Opened a \ escape
+type EscapeState struct{}
+
+func (s *EscapeState) Next(r rune, data []byte) State {
+	return &ReadyState{}
+}
diff --git a/internal/utils/parser/token.go b/internal/utils/parser/token.go
new file mode 100644
index 00000000..e868a7b5
--- /dev/null
+++ b/internal/utils/parser/token.go
@@ -0,0 +1,79 @@
+package parser
+
+import (
+	"bufio"
+	"io"
+	"unicode/utf8"
+)
+
+// State transition table for tokenizer:
+//
+//   Ready -> Ready (default)
+//   Ready -> Error (on invalid syntax)
+//   Ready -> Done (on ;, emit token)
+//   Ready -> Done (on EOF, emit token)
+//
+//   Ready -> Comment (on --)
+//   Comment -> Comment (default)
+//   Comment -> Ready (on \n)
+//
+//   Ready -> Block (on /*)
+//   Block -> Block (on /*, +-depth)
+//   Block -> Ready (on */, depth 0)
+//
+//   Ready -> Quote (on ')
+//   Quote -> Quote (on '', default)
+//   Quote -> Ready (on ')
+//
+//   Ready -> Dollar (on $tag$)
+//   Dollar -> Dollar (default)
+//   Dollar -> Ready (on $tag$)
+//
+//   Ready -> Escape (on \)
+//   Escape -> Ready (on next)
+//
+type tokenizer struct {
+	state State
+	last  int
+}
+
+func (t *tokenizer) ScanToken(data []byte, atEOF bool) (advance int, token []byte, err error) {
+	// If we requested more data, resume from last position.
+	for width := 1; t.last < len(data); t.last += width {
+		r, width := utf8.DecodeRune(data[t.last:])
+		end := t.last + width
+		t.state = t.state.Next(r, data[:end])
+		// Emit token
+		if t.state == nil {
+			t.last = 0
+			t.state = &ReadyState{}
+			return end, data[:end], nil
+		}
+	}
+	if !atEOF || len(data) == 0 {
+		// Request more data or end the stream
+		return 0, nil, nil
+	}
+	// We're at EOF. If we have a final, non-terminated token, return it.
+	return len(data), data, nil
+}
+
+// Use bufio.Scanner to split a PostgreSQL string into multiple statements.
+//
+// The core problem is to figure out whether the current ; separator is inside
+// an escaped string literal. PostgreSQL has multiple ways of opening a string
+// literal, $$, ', --, /*, etc. We use a FSM to guarantee these states are
+// entered exclusively. If not in one of the above escape states, the next ;
+// token can be parsed as statement separator.
+//
+// Each statement is split as it is, without removing comments or white spaces.
+func Split(sql io.Reader) (stats []string) {
+	t := tokenizer{state: &ReadyState{}}
+	scanner := bufio.NewScanner(sql)
+	scanner.Split(t.ScanToken)
+	for scanner.Scan() {
+		token := scanner.Text()
+		stats = append(stats, token)
+	}
+	return stats
+}


Test patch:
diff --git a/internal/db/diff/migra_test.go b/internal/db/diff/migra_test.go
index 13d4f19d..3278b115 100644
--- a/internal/db/diff/migra_test.go
+++ b/internal/db/diff/migra_test.go
@@ -32,10 +32,10 @@ func TestApplyMigrations(t *testing.T) {
 		defer conn.Close(t)
 		conn.Query("create table test").
 			Reply("SELECT 0").
-			Query("drop table test").
+			Query("drop table test;-- ignore me").
 			Reply("SELECT 0")
 		// Run test
-		assert.NoError(t, applyMigrations(context.Background(), postgresUrl, fsys, conn.Intercept))
+		assert.NoError(t, ApplyMigrations(context.Background(), postgresUrl, fsys, conn.Intercept))
 	})
 
 	t.Run("ignores empty local directory", func(t *testing.T) {
@@ -45,7 +45,7 @@ func TestApplyMigrations(t *testing.T) {
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		// Run test
-		assert.NoError(t, applyMigrations(context.Background(), postgresUrl, fsys, conn.Intercept))
+		assert.NoError(t, ApplyMigrations(context.Background(), postgresUrl, fsys, conn.Intercept))
 	})
 
 	t.Run("ignores outdated migrations", func(t *testing.T) {
@@ -60,15 +60,15 @@ func TestApplyMigrations(t *testing.T) {
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		// Run test
-		assert.NoError(t, applyMigrations(context.Background(), postgresUrl, fsys, conn.Intercept))
+		assert.NoError(t, ApplyMigrations(context.Background(), postgresUrl, fsys, conn.Intercept))
 	})
 
 	t.Run("throws error on invalid postgres url", func(t *testing.T) {
-		assert.Error(t, applyMigrations(context.Background(), "invalid", afero.NewMemMapFs()))
+		assert.Error(t, ApplyMigrations(context.Background(), "invalid", afero.NewMemMapFs()))
 	})
 
 	t.Run("throws error on failture to connect", func(t *testing.T) {
-		assert.Error(t, applyMigrations(context.Background(), postgresUrl, afero.NewMemMapFs()))
+		assert.Error(t, ApplyMigrations(context.Background(), postgresUrl, afero.NewMemMapFs()))
 	})
 
 	t.Run("throws error on failture to send batch", func(t *testing.T) {
@@ -85,6 +85,6 @@ func TestApplyMigrations(t *testing.T) {
 		conn.Query(query).
 			ReplyError(pgerrcode.DuplicateTable, "table \"test\" already exists")
 		// Run test
-		assert.Error(t, applyMigrations(context.Background(), postgresUrl, fsys, conn.Intercept))
+		assert.Error(t, ApplyMigrations(context.Background(), postgresUrl, fsys, conn.Intercept))
 	})
 }
diff --git a/internal/utils/parser/state_test.go b/internal/utils/parser/state_test.go
new file mode 100644
index 00000000..a89ff172
--- /dev/null
+++ b/internal/utils/parser/state_test.go
@@ -0,0 +1,114 @@
+package parser
+
+import (
+	"strings"
+	"testing"
+
+	"github.com/stretchr/testify/assert"
+)
+
+func TestLineComment(t *testing.T) {
+	t.Run("after separator", func(t *testing.T) {
+		sql := "END;-- comment"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"END;", "-- comment"}, stats)
+	})
+
+	t.Run("before separator", func(t *testing.T) {
+		sql := "SELECT --; 1"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"SELECT --; 1"}, stats)
+	})
+
+	t.Run("not started", func(t *testing.T) {
+		sql := "- ;END"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"- ;", "END"}, stats)
+	})
+
+	t.Run("between lines", func(t *testing.T) {
+		sql := "-- /* \n; */ END"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"-- /* \n;", " */ END"}, stats)
+	})
+}
+
+func TestBlockComment(t *testing.T) {
+	t.Run("contains separator", func(t *testing.T) {
+		sql := "SELECT /* ; */ 1;"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"SELECT /* ; */ 1;"}, stats)
+	})
+
+	t.Run("nested block", func(t *testing.T) {
+		sql := "SELECT /*; /*;*/ ;*/ 1"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"SELECT /*; /*;*/ ;*/ 1"}, stats)
+	})
+
+	t.Run("not started", func(t *testing.T) {
+		sql := "/ * ; */ END"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"/ * ;", " */ END"}, stats)
+	})
+}
+
+func TestSeparator(t *testing.T) {
+	t.Run("no spaces", func(t *testing.T) {
+		sql := ";END;;"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{";", "END;", ";"}, stats)
+	})
+
+	t.Run("between spaces", func(t *testing.T) {
+		sql := "BEGIN   ;  END"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"BEGIN   ;", "  END"}, stats)
+	})
+
+	t.Run("backslash escaped", func(t *testing.T) {
+		sql := "\\;;\\;"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"\\;;", "\\;"}, stats)
+	})
+}
+
+func TestDollarQuote(t *testing.T) {
+	t.Run("named tag", func(t *testing.T) {
+		sql := "$tag$ any ; string$tag$"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"$tag$ any ; string$tag$"}, stats)
+	})
+
+	t.Run("anonymous tag", func(t *testing.T) {
+		sql := "$$\"Dane's horse\"$$"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"$$\"Dane's horse\"$$"}, stats)
+	})
+
+	t.Run("not started", func(t *testing.T) {
+		sql := "SELECT \"$\"; $$"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"SELECT \"$\";", " $$"}, stats)
+	})
+}
+
+func TestSingleQuote(t *testing.T) {
+	t.Run("escapes separator", func(t *testing.T) {
+		sql := "SELECT ';' 1"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"SELECT ';' 1"}, stats)
+	})
+
+	t.Run("preserves single quote", func(t *testing.T) {
+		sql := "SELECT ';'';' 1"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"SELECT ';'';' 1"}, stats)
+	})
+
+	t.Run("literal backslash", func(t *testing.T) {
+		sql := "SELECT '\\'; 1'"
+		stats := Split(strings.NewReader(sql))
+		assert.ElementsMatch(t, []string{"SELECT '\\';", " 1'"}, stats)
+	})
+}
diff --git a/internal/utils/parser/testdata/all.sql b/internal/utils/parser/testdata/all.sql
new file mode 100644
index 00000000..c6394886
--- /dev/null
+++ b/internal/utils/parser/testdata/all.sql
@@ -0,0 +1,140 @@
+CREATE SCHEMA "blocks";
+CREATE TABLE "blocks"."block"
+(
+    uuid                UUID PRIMARY KEY   NOT NULL,
+    settings_type       CHARACTER VARYING  NOT NULL,
+    settings            JSONB              NOT NULL,
+    title               CHARACTER VARYING,
+    view_type           CHARACTER VARYING,
+    enabled             BOOL DEFAULT TRUE  NOT NULL,
+    visibility_settings JSONB
+);
+
+CREATE UNIQUE INDEX "block_uuid_uindex"
+    ON "blocks"."block" (uuid);
+
+CREATE INDEX "enabled_index"
+    ON "blocks"."block" (enabled);
+
+CREATE TABLE "blocks"."placement"
+(
+    location_name   CHARACTER VARYING   NOT NULL,
+    index           INT                 NOT NULL,
+    block_uuid      UUID                NOT NULL,
+    scope_path      CHARACTER VARYING   NOT NULL,
+    inherited       BOOL DEFAULT FALSE  NOT NULL,
+    excluded_scopes CHARACTER VARYING [],
+
+    -- NOTE: Because we UPDATE the primary (location_name, index) key on the placement-table,
+    -- the constraint behavior on the primary key is set to DEFERRABLE and INITIALLY IMMEDIATE.
+    --
+    -- For more inforation, refer to this bug-report:
+    --
+    -- https://www.postgresql.org/message-id/flat/20170322123053.1421.55154%40wrigleys.postgresql.org
+
+    CONSTRAINT placement_location_name_index_pk PRIMARY KEY (location_name, index) DEFERRABLE INITIALLY IMMEDIATE,
+    CONSTRAINT placement_block_uuid_fk FOREIGN KEY (block_uuid) REFERENCES "blocks"."block" (uuid) ON DELETE CASCADE
+);
+
+CREATE INDEX "placement_location_name_index_index"
+    ON "blocks".placement (location_name, index);
+
+CREATE INDEX "placement_scope_path_index"
+    ON "blocks".placement (scope_path);
+
+CREATE INDEX "placement_index_index"
+    ON "blocks".placement (index);
+
+CREATE TABLE "blocks"."location_hash"
+(
+    location_name CHARACTER VARYING PRIMARY KEY  NOT NULL,
+    hash          CHARACTER VARYING              NOT NULL
+);
+
+CREATE TABLE "blocks"."child_blocks"
+(
+    parent_uuid  UUID    NOT NULL,
+    column_index INT     NOT NULL,
+    child_uuids  UUID [] NOT NULL,
+    CONSTRAINT child_blocks_parent_uuid_index_pk PRIMARY KEY (parent_uuid, column_index),
+    CONSTRAINT child_blocks_block_uuid_fk FOREIGN KEY (parent_uuid) REFERENCES "blocks"."block" (uuid) ON DELETE CASCADE
+
+    -- TODO add a trigger to sanitize child_uuids when a block record is deleted
+);
+
+CREATE INDEX "child_blocks_parent_uuid_index"
+    ON "blocks"."child_blocks" (parent_uuid);
+
+CREATE TABLE "blocks"."user_bucket"
+(
+    user_uuid   UUID PRIMARY KEY NOT NULL,
+    block_uuids UUID []          NOT NULL
+);
+
+CREATE UNIQUE INDEX "user_bucket_user_uuid_uindex"
+    ON "blocks"."user_bucket" (user_uuid);
+
+CREATE VIEW "blocks"."block_count" AS
+    SELECT
+        b."uuid"                               AS "block_uuid",
+        (
+            (SELECT COUNT(*)
+                FROM "blocks"."placement" p
+                WHERE p."block_uuid" = b."uuid")
+            +
+            (SELECT COALESCE(SUM((SELECT COUNT(*)
+                                    FROM unnest(c."child_uuids") cb
+                                    WHERE cb = b."uuid")), 0)
+                FROM "blocks"."child_blocks" c)
+        )                                      AS "ref_count",
+        (SELECT COUNT(*)
+            FROM "blocks"."user_bucket" k
+            WHERE "uuid" = ANY (k."block_uuids")) AS "user_count"
+    FROM "blocks"."block" b;
+
+CREATE OR REPLACE FUNCTION "blocks".delete_orphaned_blocks()
+    RETURNS TRIGGER
+AS $$
+BEGIN
+    LOOP
+        -- Note that RETURN QUERY does not return from the function - it works
+        -- more like the yield-statement in PHP, in that records from the
+        -- DELETE..RETURNING statement are returned, and execution then
+        -- resumes from the following statement.
+
+        DELETE FROM "blocks"."block" b
+        WHERE b.uuid IN (
+            SELECT c.block_uuid
+            FROM "blocks"."block_count" c
+            WHERE c.ref_count = 0 AND c.user_count = 0
+        );
+
+        -- The FOUND flag is set TRUE/FALSE after executing a query - so we
+        -- EXIT from the LOOP block when the DELETE..RETURNING statement does
+        -- not delete and return any records.
+
+        EXIT WHEN NOT FOUND;
+    END LOOP;
+
+    RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE TRIGGER delete_orphans
+AFTER UPDATE OR DELETE
+    ON "blocks"."user_bucket"
+FOR EACH STATEMENT
+EXECUTE PROCEDURE "blocks".delete_orphaned_blocks();
+
+CREATE TRIGGER delete_orphans
+AFTER UPDATE OR DELETE
+    ON "blocks"."placement"
+FOR EACH STATEMENT
+EXECUTE PROCEDURE "blocks".delete_orphaned_blocks();
+
+CREATE TRIGGER delete_orphans
+AFTER UPDATE OR DELETE
+    ON "blocks"."child_blocks"
+FOR EACH STATEMENT
+EXECUTE PROCEDURE "blocks".delete_orphaned_blocks();
diff --git a/internal/utils/parser/testdata/split_00.sql b/internal/utils/parser/testdata/split_00.sql
new file mode 100644
index 00000000..78a47bc1
--- /dev/null
+++ b/internal/utils/parser/testdata/split_00.sql
@@ -0,0 +1,1 @@
+CREATE SCHEMA "blocks";
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_01.sql b/internal/utils/parser/testdata/split_01.sql
new file mode 100644
index 00000000..a877e55f
--- /dev/null
+++ b/internal/utils/parser/testdata/split_01.sql
@@ -0,0 +1,11 @@
+
+CREATE TABLE "blocks"."block"
+(
+    uuid                UUID PRIMARY KEY   NOT NULL,
+    settings_type       CHARACTER VARYING  NOT NULL,
+    settings            JSONB              NOT NULL,
+    title               CHARACTER VARYING,
+    view_type           CHARACTER VARYING,
+    enabled             BOOL DEFAULT TRUE  NOT NULL,
+    visibility_settings JSONB
+);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_02.sql b/internal/utils/parser/testdata/split_02.sql
new file mode 100644
index 00000000..a4eecb94
--- /dev/null
+++ b/internal/utils/parser/testdata/split_02.sql
@@ -0,0 +1,4 @@
+
+
+CREATE UNIQUE INDEX "block_uuid_uindex"
+    ON "blocks"."block" (uuid);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_03.sql b/internal/utils/parser/testdata/split_03.sql
new file mode 100644
index 00000000..20eec906
--- /dev/null
+++ b/internal/utils/parser/testdata/split_03.sql
@@ -0,0 +1,4 @@
+
+
+CREATE INDEX "enabled_index"
+    ON "blocks"."block" (enabled);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_04.sql b/internal/utils/parser/testdata/split_04.sql
new file mode 100644
index 00000000..493f7aac
--- /dev/null
+++ b/internal/utils/parser/testdata/split_04.sql
@@ -0,0 +1,21 @@
+
+
+CREATE TABLE "blocks"."placement"
+(
+    location_name   CHARACTER VARYING   NOT NULL,
+    index           INT                 NOT NULL,
+    block_uuid      UUID                NOT NULL,
+    scope_path      CHARACTER VARYING   NOT NULL,
+    inherited       BOOL DEFAULT FALSE  NOT NULL,
+    excluded_scopes CHARACTER VARYING [],
+
+    -- NOTE: Because we UPDATE the primary (location_name, index) key on the placement-table,
+    -- the constraint behavior on the primary key is set to DEFERRABLE and INITIALLY IMMEDIATE.
+    --
+    -- For more inforation, refer to this bug-report:
+    --
+    -- https://www.postgresql.org/message-id/flat/20170322123053.1421.55154%40wrigleys.postgresql.org
+
+    CONSTRAINT placement_location_name_index_pk PRIMARY KEY (location_name, index) DEFERRABLE INITIALLY IMMEDIATE,
+    CONSTRAINT placement_block_uuid_fk FOREIGN KEY (block_uuid) REFERENCES "blocks"."block" (uuid) ON DELETE CASCADE
+);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_05.sql b/internal/utils/parser/testdata/split_05.sql
new file mode 100644
index 00000000..003b9695
--- /dev/null
+++ b/internal/utils/parser/testdata/split_05.sql
@@ -0,0 +1,4 @@
+
+
+CREATE INDEX "placement_location_name_index_index"
+    ON "blocks".placement (location_name, index);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_06.sql b/internal/utils/parser/testdata/split_06.sql
new file mode 100644
index 00000000..c9b41508
--- /dev/null
+++ b/internal/utils/parser/testdata/split_06.sql
@@ -0,0 +1,4 @@
+
+
+CREATE INDEX "placement_scope_path_index"
+    ON "blocks".placement (scope_path);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_07.sql b/internal/utils/parser/testdata/split_07.sql
new file mode 100644
index 00000000..824464ab
--- /dev/null
+++ b/internal/utils/parser/testdata/split_07.sql
@@ -0,0 +1,4 @@
+
+
+CREATE INDEX "placement_index_index"
+    ON "blocks".placement (index);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_08.sql b/internal/utils/parser/testdata/split_08.sql
new file mode 100644
index 00000000..db30a5dd
--- /dev/null
+++ b/internal/utils/parser/testdata/split_08.sql
@@ -0,0 +1,7 @@
+
+
+CREATE TABLE "blocks"."location_hash"
+(
+    location_name CHARACTER VARYING PRIMARY KEY  NOT NULL,
+    hash          CHARACTER VARYING              NOT NULL
+);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_09.sql b/internal/utils/parser/testdata/split_09.sql
new file mode 100644
index 00000000..fad90ece
--- /dev/null
+++ b/internal/utils/parser/testdata/split_09.sql
@@ -0,0 +1,12 @@
+
+
+CREATE TABLE "blocks"."child_blocks"
+(
+    parent_uuid  UUID    NOT NULL,
+    column_index INT     NOT NULL,
+    child_uuids  UUID [] NOT NULL,
+    CONSTRAINT child_blocks_parent_uuid_index_pk PRIMARY KEY (parent_uuid, column_index),
+    CONSTRAINT child_blocks_block_uuid_fk FOREIGN KEY (parent_uuid) REFERENCES "blocks"."block" (uuid) ON DELETE CASCADE
+
+    -- TODO add a trigger to sanitize child_uuids when a block record is deleted
+);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_10.sql b/internal/utils/parser/testdata/split_10.sql
new file mode 100644
index 00000000..4abf69d4
--- /dev/null
+++ b/internal/utils/parser/testdata/split_10.sql
@@ -0,0 +1,4 @@
+
+
+CREATE INDEX "child_blocks_parent_uuid_index"
+    ON "blocks"."child_blocks" (parent_uuid);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_11.sql b/internal/utils/parser/testdata/split_11.sql
new file mode 100644
index 00000000..91fa4a32
--- /dev/null
+++ b/internal/utils/parser/testdata/split_11.sql
@@ -0,0 +1,7 @@
+
+
+CREATE TABLE "blocks"."user_bucket"
+(
+    user_uuid   UUID PRIMARY KEY NOT NULL,
+    block_uuids UUID []          NOT NULL
+);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_12.sql b/internal/utils/parser/testdata/split_12.sql
new file mode 100644
index 00000000..835d649b
--- /dev/null
+++ b/internal/utils/parser/testdata/split_12.sql
@@ -0,0 +1,4 @@
+
+
+CREATE UNIQUE INDEX "user_bucket_user_uuid_uindex"
+    ON "blocks"."user_bucket" (user_uuid);
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_13.sql b/internal/utils/parser/testdata/split_13.sql
new file mode 100644
index 00000000..c39a2179
--- /dev/null
+++ b/internal/utils/parser/testdata/split_13.sql
@@ -0,0 +1,19 @@
+
+
+CREATE VIEW "blocks"."block_count" AS
+    SELECT
+        b."uuid"                               AS "block_uuid",
+        (
+            (SELECT COUNT(*)
+                FROM "blocks"."placement" p
+                WHERE p."block_uuid" = b."uuid")
+            +
+            (SELECT COALESCE(SUM((SELECT COUNT(*)
+                                    FROM unnest(c."child_uuids") cb
+                                    WHERE cb = b."uuid")), 0)
+                FROM "blocks"."child_blocks" c)
+        )                                      AS "ref_count",
+        (SELECT COUNT(*)
+            FROM "blocks"."user_bucket" k
+            WHERE "uuid" = ANY (k."block_uuids")) AS "user_count"
+    FROM "blocks"."block" b;
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_14.sql b/internal/utils/parser/testdata/split_14.sql
new file mode 100644
index 00000000..8e04845f
--- /dev/null
+++ b/internal/utils/parser/testdata/split_14.sql
@@ -0,0 +1,30 @@
+
+
+CREATE OR REPLACE FUNCTION "blocks".delete_orphaned_blocks()
+    RETURNS TRIGGER
+AS $$
+BEGIN
+    LOOP
+        -- Note that RETURN QUERY does not return from the function - it works
+        -- more like the yield-statement in PHP, in that records from the
+        -- DELETE..RETURNING statement are returned, and execution then
+        -- resumes from the following statement.
+
+        DELETE FROM "blocks"."block" b
+        WHERE b.uuid IN (
+            SELECT c.block_uuid
+            FROM "blocks"."block_count" c
+            WHERE c.ref_count = 0 AND c.user_count = 0
+        );
+
+        -- The FOUND flag is set TRUE/FALSE after executing a query - so we
+        -- EXIT from the LOOP block when the DELETE..RETURNING statement does
+        -- not delete and return any records.
+
+        EXIT WHEN NOT FOUND;
+    END LOOP;
+
+    RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql;
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_15.sql b/internal/utils/parser/testdata/split_15.sql
new file mode 100644
index 00000000..58920aae
--- /dev/null
+++ b/internal/utils/parser/testdata/split_15.sql
@@ -0,0 +1,7 @@
+
+
+CREATE TRIGGER delete_orphans
+AFTER UPDATE OR DELETE
+    ON "blocks"."user_bucket"
+FOR EACH STATEMENT
+EXECUTE PROCEDURE "blocks".delete_orphaned_blocks();
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_16.sql b/internal/utils/parser/testdata/split_16.sql
new file mode 100644
index 00000000..10cdd16a
--- /dev/null
+++ b/internal/utils/parser/testdata/split_16.sql
@@ -0,0 +1,7 @@
+
+
+CREATE TRIGGER delete_orphans
+AFTER UPDATE OR DELETE
+    ON "blocks"."placement"
+FOR EACH STATEMENT
+EXECUTE PROCEDURE "blocks".delete_orphaned_blocks();
\ No newline at end of file
diff --git a/internal/utils/parser/testdata/split_17.sql b/internal/utils/parser/testdata/split_17.sql
new file mode 100644
index 00000000..a8c67d26
--- /dev/null
+++ b/internal/utils/parser/testdata/split_17.sql
@@ -0,0 +1,7 @@
+
+
+CREATE TRIGGER delete_orphans
+AFTER UPDATE OR DELETE
+    ON "blocks"."child_blocks"
+FOR EACH STATEMENT
+EXECUTE PROCEDURE "blocks".delete_orphaned_blocks();
\ No newline at end of file
diff --git a/internal/utils/parser/token_test.go b/internal/utils/parser/token_test.go
new file mode 100644
index 00000000..5e6559e5
--- /dev/null
+++ b/internal/utils/parser/token_test.go
@@ -0,0 +1,40 @@
+package parser
+
+import (
+	_ "embed"
+	"io/fs"
+	"os"
+	"path/filepath"
+	"strings"
+	"testing"
+
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+)
+
+func TestSplit(t *testing.T) {
+	const testdata = "testdata"
+
+	var fixture []string
+	require.NoError(t, filepath.WalkDir(testdata, func(path string, f fs.DirEntry, err error) error {
+		if err != nil {
+			return err
+		}
+		if !strings.HasPrefix(f.Name(), "split_") {
+			return nil
+		}
+		contents, err := os.ReadFile(path)
+		if err != nil {
+			return err
+		}
+		fixture = append(fixture, string(contents))
+		return nil
+	}))
+	require.Len(t, fixture, 18)
+
+	sql, err := os.Open(filepath.Join(testdata, "all.sql"))
+	require.NoError(t, err)
+	stats := Split(sql)
+
+	assert.ElementsMatch(t, fixture, stats[:len(fixture)])
+}