• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

codenotary / immudb / 24841644892

23 Apr 2026 02:44PM UTC coverage: 85.279% (-4.0%) from 89.306%
24841644892

push

gh-ci

web-flow
feat: v1.11.0 PostgreSQL compatibility and SQL feature expansion (#2090)

* Add structured audit logging with immutable audit trail

Introduces a new --audit-log flag that records all gRPC operations as
structured JSON events in immudb's tamper-proof KV store. Events are
stored under the audit: key prefix in systemdb, queryable via Scan and
verifiable via VerifiableGet. An async buffered writer ensures minimal
latency impact. Configurable event filtering (all/write/admin) via
--audit-log-events flag.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

* Add PostgreSQL ORM compatibility layer and verification functions

Extend the pgsql wire protocol with immudb verification functions
(immudb_state, immudb_verify_row, immudb_verify_tx, immudb_history,
immudb_tx) accessible via standard SQL SELECT statements.

Add pg_catalog resolvers (pg_attribute, pg_index, pg_constraint,
pg_type, pg_settings, pg_description) and information_schema
resolvers (tables, columns, schemata, key_column_usage) to support
ORM introspection from Django, SQLAlchemy, GORM, and ActiveRecord.

Add PostgreSQL compatibility functions: current_database,
current_schema, current_user, format_type, pg_encoding_to_char,
pg_get_expr, pg_get_constraintdef, obj_description, col_description,
has_table_privilege, has_schema_privilege, and others.

Add SHOW statement emulation for common ORM config queries and
schema-qualified name stripping for information_schema and public
schema references.

* Implement EXISTS and IN subquery support in SQL engine

Replace the previously stubbed ExistsBoolExp and InSubQueryExp
implementations with working non-correlated subquery execution.

EXISTS subqueries resolve the inner SELECT and check if any rows
are returned. IN subqueries resolve the inner SELECT, iterate the
result set, and compare each value against the outer expression.
Both support NOT variants (NOT EXISTS, NOT IN).

Correlated subqueries (referencing outer query columns) ar... (continued)

7254 of 10471 new or added lines in 124 files covered. (69.28%)

115 existing lines in 18 files now uncovered.

44599 of 52298 relevant lines covered (85.28%)

127676.6 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

90.64
/pkg/pgsql/server/unique_ddl.go
1
/*
2
Copyright 2026 Codenotary Inc. All rights reserved.
3

4
SPDX-License-Identifier: BUSL-1.1
5
you may not use this file except in compliance with the License.
6
You may obtain a copy of the License at
7

8
    https://mariadb.com/bsl11/
9

10
Unless required by applicable law or agreed to in writing, software
11
distributed under the License is distributed on an "AS IS" BASIS,
12
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
See the License for the specific language governing permissions and
14
limitations under the License.
15
*/
16

17
package server
18

19
import (
20
        "regexp"
21
        "strings"
22
)
23

24
// PostgreSQL accepts `UNIQUE` as both an inline column constraint
25
// (`email VARCHAR(255) UNIQUE`) and a table-level constraint
26
// (`UNIQUE (a, b)`). immudb's grammar has neither — uniqueness is
27
// expressed only via `CREATE UNIQUE INDEX ON tbl(cols)`
28
// (sql_grammar.y:400-408). Historically the pgwire layer "solved"
29
// this by regex-stripping the `UNIQUE` keyword, which silently
30
// dropped the constraint. That is wrong: constraints are a
31
// semantic promise, not decoration.
32
//
33
// extractUniqueConstraints rewrites each inline/table-level UNIQUE
34
// into a trailing `CREATE UNIQUE INDEX IF NOT EXISTS ON tbl(cols)`
35
// statement, preserving the uniqueness guarantee against the engine.
36
// Called from removePGCatalogReferences BEFORE the pgTypeReplacements
37
// regex pipeline so the downstream regexes see a clean column list.
38
//
39
// Input assumptions:
40
//   - String literals have already been masked by maskStringLiterals,
41
//     so every ' in the string delimits a placeholder token, not
42
//     user content.
43
//   - The input may contain multiple statements separated by `;`.
44

45
// createTableHeader matches `CREATE TABLE [IF NOT EXISTS] <name>` up
46
// to (but not including) the opening `(` of the column list.
47
// `<name>` can be a bare identifier, a double-quoted identifier, or
48
// a schema-qualified form `a.b` (the pg_catalog / public strip pass
49
// runs before this, so the qualifier is usually already gone, but we
50
// stay permissive).
51
var createTableHeaderRe = regexp.MustCompile(`(?is)\bCREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?("[^"]+"|[A-Za-z_][\w.]*)\s*\(`)
52

53
// unquoteIdent strips the surrounding double quotes from a PG-style
54
// quoted identifier, leaving bare identifiers untouched. CREATE INDEX
55
// cannot reference a quoted name (immudb's grammar), so we emit the
56
// bare form.
57
func unquoteIdent(s string) string {
105✔
58
        if len(s) >= 2 && s[0] == '"' && s[len(s)-1] == '"' {
105✔
NEW
59
                return s[1 : len(s)-1]
×
NEW
60
        }
×
61
        return s
105✔
62
}
63

64
// splitTopLevelCommas splits s by commas that are not nested inside
65
// parentheses. Needed to distinguish `UNIQUE (a, b)` (single element,
66
// comma inside parens) from two column definitions.
67
func splitTopLevelCommas(s string) []string {
103✔
68
        var parts []string
103✔
69
        depth := 0
103✔
70
        last := 0
103✔
71
        for i, c := range s {
5,507✔
72
                switch c {
5,404✔
73
                case '(':
14✔
74
                        depth++
14✔
75
                case ')':
14✔
76
                        depth--
14✔
77
                case ',':
244✔
78
                        if depth == 0 {
487✔
79
                                parts = append(parts, s[last:i])
243✔
80
                                last = i + 1
243✔
81
                        }
243✔
82
                }
83
        }
84
        parts = append(parts, s[last:])
103✔
85
        return parts
103✔
86
}
87

88
// stripUniqueFromColumn removes a standalone `UNIQUE` keyword from a
89
// column definition like `email VARCHAR(255) UNIQUE NOT NULL`,
90
// returning the rewritten definition and whether anything was
91
// stripped. Case-insensitive, word-bounded, does not strip inside
92
// parens (covers `DEFAULT unique_fn()` or similar).
93
var uniqueTokenRe = regexp.MustCompile(`(?i)\s+UNIQUE\b`)
94

95
func stripUniqueFromColumn(col string) (string, bool) {
345✔
96
        // Only strip at depth 0 (outside any inner parens).
345✔
97
        var out strings.Builder
345✔
98
        depth := 0
345✔
99
        matches := uniqueTokenRe.FindAllStringIndex(col, -1)
345✔
100
        if len(matches) == 0 {
688✔
101
                return col, false
343✔
102
        }
343✔
103
        // Walk the string; track paren depth; skip matches that fall inside
104
        // inner parens.
105
        stripped := false
2✔
106
        i := 0
2✔
107
        for i < len(col) {
42✔
108
                // Check if any match starts at i AND depth == 0.
40✔
109
                var hit *[]int
40✔
110
                for mi := range matches {
80✔
111
                        if matches[mi][0] == i {
42✔
112
                                hit = &matches[mi]
2✔
113
                                break
2✔
114
                        }
115
                }
116
                if hit != nil && depth == 0 {
42✔
117
                        i = (*hit)[1]
2✔
118
                        stripped = true
2✔
119
                        continue
2✔
120
                }
121
                switch col[i] {
38✔
NEW
122
                case '(':
×
NEW
123
                        depth++
×
NEW
124
                case ')':
×
NEW
125
                        depth--
×
126
                }
127
                out.WriteByte(col[i])
38✔
128
                i++
38✔
129
        }
130
        return out.String(), stripped
2✔
131
}
132

133
// firstIdentifier returns the first identifier-looking token in s
134
// (after skipping leading whitespace). Handles double-quoted and bare
135
// forms. Used to pull the column name off a column definition.
136
func firstIdentifier(s string) string {
2✔
137
        s = strings.TrimLeft(s, " \t\n\r")
2✔
138
        if s == "" {
2✔
NEW
139
                return ""
×
NEW
140
        }
×
141
        if s[0] == '"' {
2✔
NEW
142
                end := strings.IndexByte(s[1:], '"')
×
NEW
143
                if end < 0 {
×
NEW
144
                        return s
×
NEW
145
                }
×
NEW
146
                return s[:end+2]
×
147
        }
148
        end := 0
2✔
149
        for end < len(s) {
14✔
150
                c := s[end]
12✔
151
                if c == '_' || (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') {
22✔
152
                        end++
10✔
153
                        continue
10✔
154
                }
155
                break
2✔
156
        }
157
        return s[:end]
2✔
158
}
159

160
// tableLevelUniqueRe matches a table-level `UNIQUE (col1, col2, …)`
161
// constraint element. Used as a quick check; the parenthesised column
162
// list is then extracted from the tail of the element.
163
var tableLevelUniqueRe = regexp.MustCompile(`(?is)^\s*UNIQUE\s*\(([^)]*)\)\s*$`)
164

165
// extractUniqueConstraints walks every CREATE TABLE statement in sqlStr,
166
// rewriting inline and table-level UNIQUE constraints into trailing
167
// `CREATE UNIQUE INDEX IF NOT EXISTS ON tbl(cols)` statements. Returns
168
// the rewritten SQL. If no CREATE TABLE contains a UNIQUE, returns
169
// sqlStr unchanged (pointer-equal for cheap downstream checks).
170
func extractUniqueConstraints(sqlStr string) string {
560✔
171
        headers := createTableHeaderRe.FindAllStringSubmatchIndex(sqlStr, -1)
560✔
172
        if len(headers) == 0 {
1,025✔
173
                return sqlStr
465✔
174
        }
465✔
175

176
        // Walk headers back-to-front so index positions in the suffix stay
177
        // valid as we splice the prefix.
178
        type insertion struct {
95✔
179
                pos  int    // where to splice (after the matching ')')
95✔
180
                stmt string // "; CREATE UNIQUE INDEX IF NOT EXISTS ON tbl(cols)"
95✔
181
        }
95✔
182
        var insertions []insertion
95✔
183

95✔
184
        // Also accumulate replacements (newColSection) keyed by position.
95✔
185
        type replacement struct {
95✔
186
                from, to int
95✔
187
                with     string
95✔
188
        }
95✔
189
        var replacements []replacement
95✔
190

95✔
191
        for _, h := range headers {
198✔
192
                // h = [match_start, match_end, name_start, name_end]
103✔
193
                openParen := h[1] - 1 // '(' is the last char matched
103✔
194
                if openParen < 0 || openParen >= len(sqlStr) || sqlStr[openParen] != '(' {
103✔
NEW
195
                        continue
×
196
                }
197
                name := unquoteIdent(sqlStr[h[2]:h[3]])
103✔
198

103✔
199
                // Find matching close paren.
103✔
200
                depth := 1
103✔
201
                i := openParen + 1
103✔
202
                for i < len(sqlStr) && depth > 0 {
5,610✔
203
                        switch sqlStr[i] {
5,507✔
204
                        case '(':
14✔
205
                                depth++
14✔
206
                        case ')':
117✔
207
                                depth--
117✔
208
                        }
209
                        i++
5,507✔
210
                }
211
                if depth != 0 {
103✔
NEW
212
                        // Unbalanced — leave this CREATE TABLE alone.
×
NEW
213
                        continue
×
214
                }
215
                closeParen := i - 1
103✔
216
                body := sqlStr[openParen+1 : closeParen]
103✔
217

103✔
218
                parts := splitTopLevelCommas(body)
103✔
219

103✔
220
                var keptParts []string
103✔
221
                var newIndexes []string // parenthesised col lists, e.g. "email" or "a, b"
103✔
222

103✔
223
                for _, p := range parts {
449✔
224
                        trimmed := strings.TrimSpace(p)
346✔
225

346✔
226
                        // Table-level UNIQUE (…)
346✔
227
                        if m := tableLevelUniqueRe.FindStringSubmatch(trimmed); m != nil {
347✔
228
                                newIndexes = append(newIndexes, strings.TrimSpace(m[1]))
1✔
229
                                continue
1✔
230
                        }
231

232
                        // Inline UNIQUE on a column definition.
233
                        stripped, didStrip := stripUniqueFromColumn(p)
345✔
234
                        if didStrip {
347✔
235
                                col := unquoteIdent(firstIdentifier(stripped))
2✔
236
                                if col != "" {
4✔
237
                                        newIndexes = append(newIndexes, col)
2✔
238
                                }
2✔
239
                                keptParts = append(keptParts, stripped)
2✔
240
                                continue
2✔
241
                        }
242

243
                        keptParts = append(keptParts, p)
343✔
244
                }
245

246
                if len(newIndexes) == 0 {
203✔
247
                        continue
100✔
248
                }
249

250
                // Rebuild body. Use a leading newline before each element only
251
                // if the original column list looks multi-line; otherwise keep
252
                // a simple comma-space join. We don't try to preserve exact
253
                // original formatting — the downstream parser doesn't care.
254
                newBody := strings.Join(keptParts, ",")
3✔
255

3✔
256
                replacements = append(replacements, replacement{
3✔
257
                        from: openParen + 1,
3✔
258
                        to:   closeParen,
3✔
259
                        with: newBody,
3✔
260
                })
3✔
261

3✔
262
                for _, cols := range newIndexes {
6✔
263
                        insertions = append(insertions, insertion{
3✔
264
                                pos:  closeParen + 1,
3✔
265
                                stmt: "; CREATE UNIQUE INDEX IF NOT EXISTS ON " + name + "(" + cols + ")",
3✔
266
                        })
3✔
267
                }
3✔
268
        }
269

270
        if len(replacements) == 0 && len(insertions) == 0 {
187✔
271
                return sqlStr
92✔
272
        }
92✔
273

274
        // Apply replacements first (back-to-front), then insertions
275
        // (back-to-front). Insertions go at closeParen+1 of the ORIGINAL
276
        // string; since replacements preserve string length only
277
        // coincidentally, do insertions against the string as it stands
278
        // after each step, walking right-to-left.
279
        //
280
        // Simpler: build a list of (position, op) events in descending
281
        // order of position and apply in that order.
282
        type edit struct {
3✔
283
                from, to int
3✔
284
                with     string
3✔
285
        }
3✔
286
        var edits []edit
3✔
287
        for _, r := range replacements {
6✔
288
                edits = append(edits, edit{r.from, r.to, r.with})
3✔
289
        }
3✔
290
        for _, ins := range insertions {
6✔
291
                edits = append(edits, edit{ins.pos, ins.pos, ins.stmt})
3✔
292
        }
3✔
293
        // Sort descending by `from`.
294
        for i := 1; i < len(edits); i++ {
6✔
295
                for j := i; j > 0 && edits[j-1].from < edits[j].from; j-- {
6✔
296
                        edits[j-1], edits[j] = edits[j], edits[j-1]
3✔
297
                }
3✔
298
        }
299

300
        out := sqlStr
3✔
301
        for _, e := range edits {
9✔
302
                out = out[:e.from] + e.with + out[e.to:]
6✔
303
        }
6✔
304
        return out
3✔
305
}
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc