You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@texera.apache.org by gi...@apache.org on 2026/07/05 21:15:28 UTC

(texera) branch gh-readonly-queue/main/pr-6145-f246b24ab8d5fbb61c4bce6208a904238ade7f6b created (now 34c0f47737)

This is an automated email from the ASF dual-hosted git repository.

github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-6145-f246b24ab8d5fbb61c4bce6208a904238ade7f6b
in repository https://gitbox.apache.org/repos/asf/texera.git


      at 34c0f47737 feat(local-dev): auto-apply pending sql/updates before the sbt build (#6145)

This branch includes the following new commits:

     new 34c0f47737 feat(local-dev): auto-apply pending sql/updates before the sbt build (#6145)

The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails.  The revisions
listed as "add" were already present in the repository and have only
been added to this reference.



(texera) 01/01: feat(local-dev): auto-apply pending sql/updates before the sbt build (#6145)

Posted by gi...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-6145-f246b24ab8d5fbb61c4bce6208a904238ade7f6b
in repository https://gitbox.apache.org/repos/asf/texera.git

commit 34c0f47737081b3021481b471935951b40afa0e9
Author: Yicong Huang <17...@users.noreply.github.com>
AuthorDate: Sun Jul 5 14:15:22 2026 -0700

    feat(local-dev): auto-apply pending sql/updates before the sbt build (#6145)
    
    ### What changes were proposed in this PR?
    
    jOOQ codegen reads the live postgres at sbt-compile time, so pulling
    main after a PR adds a new `sql/updates/N.sql` breaks `bin/local-dev.sh
    up` with `not found: value <NEW_TABLE>` until the update is applied by
    hand (hit today with `27.sql` / `workflow_cover_image` from #5704).
    `infra_ensure_db_schema` only bootstrapped `sql/texera_ddl.sql` on a
    fresh DB; nothing reconciled an existing local DB, and the repo's
    official runner (`sql/docker-compose.yml`, liquibase) is manual with
    creds that don't match local-dev's.
    
    This PR teaches local-dev to reconcile the schema before any sbt build,
    on every path that builds (full-stack `up`, `auto`, single-service
    `up`):
    
    - `parse_changelog_changesets` parses `sql/changelog.xml` (BSD-awk
    compatible, skips the commented example changeset) into id/author/path
    triples.
    - `infra_apply_sql_updates` diffs those against liquibase's
    `public.databasechangelog` and psql-applies the missing updates in
    document order, with the same normalization the official liquibase
    compose uses: strip the `\c` meta-command (not every file has one —
    `23.sql` doesn't) and connect to `texera_db` directly; the files' own
    `SET search_path` targets the schema. Applications are recorded in
    `databasechangelog` (liquibase-created DDL, `MD5SUM` left `NULL` so a
    later real liquibase run adopts the rows and fills checksums instead of
    re-executing).
    - A fresh bootstrap seeds every changeSet as applied — `texera_ddl.sql`
    is kept in sync with `sql/updates/*`, so replaying them would be
    redundant.
    - A failing update aborts with a clear message BEFORE the sbt build
    instead of letting the compile die on missing generated tables. The
    all-current fast path is one psql query + one awk parse.
    
    ### Any related issues, documentation, discussions?
    
    Closes #6144. The `--help` header's docker-infra paragraph documents the
    behavior. Related context: the official liquibase runner in
    `sql/docker-compose.yml` remains the mechanism for non-local deployments
    (the `ddl-change` email workflow notifies admins); this PR only
    automates the local dev loop while staying compatible with its
    bookkeeping.
    
    ### How was this PR tested?
    
    - `bash bin/local-dev/tests/test_local_dev_sh.sh` — 50 passed, 0 failed.
    New cases: the reconcile function exists and is wired into
    `infra_ensure_db_schema` on both paths (apply on existing DB, seed on
    fresh bootstrap), tracks via `databasechangelog` with `ON_ERROR_STOP`,
    the changelog parser handles the real `sql/changelog.xml` (5 changesets,
    commented example skipped), and every referenced update file exists on
    disk.
    - Live test against a real local dev DB that had `27.sql` applied
    manually and no `databasechangelog` table: first run applied changeSets
    23–27 idempotently (and surfaced that `23.sql`'s `notebook` tables had
    in fact never been applied to this DB — now healed) and wrote the 5
    tracking rows with correct authors; second run took the fast path
    ("schema current"). Verified `notebook` exists in the `texera_db` schema
    afterwards.
    
    ### Was this PR authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Code (Claude Fable 5)
    
    Co-authored-by: Claude Fable 5 <no...@anthropic.com>
---
 bin/local-dev/main.sh                    | 129 +++++++++++++++++++++++++++++--
 bin/local-dev/tests/test_local_dev_sh.sh |  47 +++++++++++
 2 files changed, 170 insertions(+), 6 deletions(-)

diff --git a/bin/local-dev/main.sh b/bin/local-dev/main.sh
index 1844dfd7cb..c597264b7c 100755
--- a/bin/local-dev/main.sh
+++ b/bin/local-dev/main.sh
@@ -100,7 +100,10 @@
 # Docker infra (postgres / minio / lakefs / lakekeeper / litellm) IS managed
 # here: `up` brings it up via `docker compose` (project texera-local-dev) and
 # `down` tears down any docker targets. The script warns if expected ports are
-# unreachable.
+# unreachable. Before any sbt build the postgres schema is reconciled: a fresh
+# DB is bootstrapped from sql/texera_ddl.sql, and pending sql/updates/*.sql
+# from sql/changelog.xml are applied automatically (tracked in liquibase's
+# databasechangelog table) so jOOQ codegen sees the schema the code expects.
 #
 # Logs and pid book-keeping live under: ${TEXERA_LOCAL_DEV_DIR:-/tmp/texera-local-dev}
 
@@ -1344,16 +1347,17 @@ infra_ensure_db_schema() {
         return 0
     fi
     # `feedback` is one of the newer tables; if it exists we assume the
-    # whole schema is current. (texera_ddl.sql is idempotent with
-    # CREATE TABLE IF NOT EXISTS, so re-applying it is safe even if some
-    # tables already exist, but skipping the copy + exec is faster.)
+    # base schema is in place and only sql/updates/* may be pending.
+    # (texera_ddl.sql is idempotent with CREATE TABLE IF NOT EXISTS, so
+    # re-applying it is safe even if some tables already exist, but
+    # skipping the copy + exec is faster.)
     local has_feedback=""
     has_feedback=$(docker exec "$pg" psql -U texera -d texera_db -tAc \
         "SELECT 1 FROM pg_tables WHERE schemaname='texera_db' AND tablename='feedback'" \
         2>/dev/null || true)
     if [[ "$has_feedback" == "1" ]]; then
-        tui_skip "postgres: schema already current"
-        return 0
+        infra_apply_sql_updates
+        return $?
     fi
     tui_step "postgres: applying sql/texera_ddl.sql (one-time bootstrap)"
     local ddl="$REPO_ROOT/sql/texera_ddl.sql"
@@ -1364,11 +1368,124 @@ infra_ensure_db_schema() {
     docker cp "$ddl" "$pg":/tmp/texera_ddl.sql >/dev/null
     if docker exec -u postgres "$pg" psql -U texera -f /tmp/texera_ddl.sql >/dev/null 2>&1; then
         tui_ok "postgres: schema bootstrapped"
+        # texera_ddl.sql is kept in sync with sql/updates/*, so a fresh
+        # bootstrap already contains every changeSet — record them as
+        # applied instead of replaying them.
+        infra_apply_sql_updates seed
     else
         tui_warn "postgres: ddl exec returned non-zero (check container logs)"
     fi
 }
 
+# Parse the liquibase changelog (sql/changelog.xml) into "id<TAB>author<TAB>path"
+# lines, in document order, skipping XML comments (the trailing example
+# changeset). Relies on the file's flat convention: a <changeSet id=".."
+# author=".."> line followed by a <sqlFile path=".."/> line.
+parse_changelog_changesets() {
+    local changelog="$1"
+    awk '
+        /<!--/ { c=1 }
+        c { if (/-->/) c=0; next }
+        /<changeSet / {
+            split($0, a, "\"")   # a[2]=id, a[4]=author
+            id=a[2]; author=a[4]; next
+        }
+        /<sqlFile / && id != "" {
+            split($0, b, "\"")   # b[2]=path
+            printf "%s\t%s\t%s\n", id, author, b[2]
+            id=""; author=""
+        }
+    ' "$changelog"
+}
+
+# Reconcile sql/updates/* with the live DB so jOOQ codegen (which reads the
+# database at sbt-compile time) sees the schema the checked-out code expects.
+# The repo's official runner is liquibase (sql/docker-compose.yml — manual,
+# and its hardcoded creds differ from local-dev's); we reuse its bookkeeping,
+# public.databasechangelog, but apply the files with psql, normalizing the
+# same way its compose does: strip the `\c` psql meta-command (not every
+# update file has one — 23.sql doesn't) and connect to texera_db directly.
+# The files' own `SET search_path TO texera_db` targets the right schema.
+# MD5SUM is left NULL — a later real liquibase run fills the checksum in
+# instead of re-executing.
+#   $1 = "seed": record every changeSet as applied without running it (fresh
+#        DB just bootstrapped from texera_ddl.sql, which already has them).
+# Returns non-zero when an update fails to apply — callers abort before the
+# sbt build rather than let the compile die on missing generated tables.
+infra_apply_sql_updates() {
+    local seed="${1:-}"
+    local pg="texera-postgres"
+    local changelog="$REPO_ROOT/sql/changelog.xml"
+    [[ -f "$changelog" ]] || return 0
+
+    local applied=""
+    applied=$(docker exec "$pg" psql -U texera -d texera_db -tAc \
+        "SELECT id FROM public.databasechangelog" 2>/dev/null || true)
+
+    # Collect the pending changeSets first so the common all-current case
+    # stays a single cheap query + parse.
+    local pending=()
+    local line="" id="" author="" path=""
+    while IFS= read -r line; do
+        IFS=$'\t' read -r id author path <<< "$line"
+        [[ -n "$id" && -n "$path" ]] || continue
+        case $'\n'"$applied"$'\n' in
+            *$'\n'"$id"$'\n'*) continue ;;
+        esac
+        pending+=("$line")
+    done < <(parse_changelog_changesets "$changelog")
+
+    if (( ${#pending[@]} == 0 )); then
+        tui_skip "postgres: schema current (all sql/updates applied)"
+        return 0
+    fi
+
+    # Liquibase creates this table on first use; match its DDL so a later
+    # real liquibase run treats our rows as its own.
+    docker exec "$pg" psql -U texera -d texera_db -qc "
+        CREATE TABLE IF NOT EXISTS public.databasechangelog (
+            id VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL,
+            filename VARCHAR(255) NOT NULL,
+            dateexecuted TIMESTAMP WITHOUT TIME ZONE NOT NULL,
+            orderexecuted INTEGER NOT NULL, exectype VARCHAR(10) NOT NULL,
+            md5sum VARCHAR(35), description VARCHAR(255),
+            comments VARCHAR(255), tag VARCHAR(255), liquibase VARCHAR(20),
+            contexts VARCHAR(255), labels VARCHAR(255),
+            deployment_id VARCHAR(10))" >/dev/null 2>&1 || {
+        tui_warn "postgres: cannot create databasechangelog -- skipping sql/updates"
+        return 0
+    }
+
+    for line in "${pending[@]}"; do
+        IFS=$'\t' read -r id author path <<< "$line"
+        if [[ -z "$seed" ]]; then
+            local sql_file="$REPO_ROOT/$path"
+            if [[ ! -f "$sql_file" ]]; then
+                tui_err "postgres: changelog references missing file $path"
+                return 1
+            fi
+            tui_step "postgres: applying $path (changeSet $id)"
+            if ! sed 's/^\\c.*$//' "$sql_file" \
+                    | docker exec -i "$pg" psql -U texera -d texera_db \
+                        -v ON_ERROR_STOP=1 -f - >/dev/null 2>&1; then
+                tui_err "postgres: $path failed -- inspect with: docker exec -i texera-postgres psql -U texera -d texera_db < $path"
+                return 1
+            fi
+        fi
+        docker exec "$pg" psql -U texera -d texera_db -qc "
+            INSERT INTO public.databasechangelog
+                (id, author, filename, dateexecuted, orderexecuted, exectype)
+            VALUES ('$id', '$author', 'changelog.xml', now(),
+                (SELECT COALESCE(MAX(orderexecuted),0)+1 FROM public.databasechangelog),
+                'EXECUTED')" >/dev/null 2>&1 || true
+    done
+    if [[ -n "$seed" ]]; then
+        tui_ok "postgres: ${#pending[@]} changeSet(s) recorded as applied (bootstrap DDL already has them)"
+    else
+        tui_ok "postgres: ${#pending[@]} sql/update(s) applied"
+    fi
+}
+
 # Build precondition: ensure ONLY the postgres container is up + schema
 # bootstrapped. common/dao's jooqGenerate sourceGenerator connects to
 # postgres via JDBC at sbt-compile time; if postgres isn't reachable the
diff --git a/bin/local-dev/tests/test_local_dev_sh.sh b/bin/local-dev/tests/test_local_dev_sh.sh
index 63320386a7..c8d5e1d417 100755
--- a/bin/local-dev/tests/test_local_dev_sh.sh
+++ b/bin/local-dev/tests/test_local_dev_sh.sh
@@ -546,5 +546,52 @@ else
     _fail "build_all: auto short-circuit ignores missing launchers"
 fi
 
+# 27) sql/updates auto-apply (regression for the jOOQ compile failure after a
+#     pull adds a new sql/updates/N.sql). Structural: the reconcile function
+#     exists, is wired into infra_ensure_db_schema on BOTH paths (existing DB ->
+#     apply pending; fresh bootstrap -> seed as applied), and records into
+#     liquibase's databasechangelog so the official runner stays compatible.
+if grep -qE '^infra_apply_sql_updates\(\) \{' "$MAIN_SH"; then
+    _pass "infra_apply_sql_updates helper is defined"
+else
+    _fail "infra_apply_sql_updates helper missing"
+fi
+schema_body=$(awk '/^infra_ensure_db_schema\(\)/{f=1} f{print} f&&/^}/{exit}' "$MAIN_SH")
+if [[ "$schema_body" == *"infra_apply_sql_updates"* ]] \
+   && [[ "$schema_body" == *"infra_apply_sql_updates seed"* ]]; then
+    _pass "infra_ensure_db_schema reconciles updates (apply on existing DB, seed on bootstrap)"
+else
+    _fail "infra_ensure_db_schema doesn't wire infra_apply_sql_updates on both paths"
+fi
+updates_body=$(awk '/^infra_apply_sql_updates\(\)/{f=1} f{print} f&&/^}/{exit}' "$MAIN_SH")
+if [[ "$updates_body" == *"databasechangelog"* && "$updates_body" == *"ON_ERROR_STOP"* ]]; then
+    _pass "infra_apply_sql_updates tracks via databasechangelog and fails fast on psql errors"
+else
+    _fail "infra_apply_sql_updates missing databasechangelog tracking or ON_ERROR_STOP"
+fi
+
+# 28) The changelog parser handles the real sql/changelog.xml: emits
+#     id/author/path triples, skips the commented example changeset, and every
+#     referenced update file exists on disk (repo-consistency check).
+parse_fn=$(awk '/^parse_changelog_changesets\(\)/{f=1} f{print} f&&/^}/{exit}' "$MAIN_SH")
+parsed=$(eval "$parse_fn"; parse_changelog_changesets "$REPO_ROOT/sql/changelog.xml")
+n_parsed=$(printf '%s\n' "$parsed" | grep -c .)
+example_skipped=true
+printf '%s' "$parsed" | grep -q "^1	" && example_skipped=false
+if (( n_parsed >= 5 )) && $example_skipped; then
+    _pass "changelog parser: $n_parsed changesets, commented example skipped"
+else
+    _fail "changelog parser wrong" "n=$n_parsed parsed=$(printf '%s' "$parsed" | head -2 | tr '\n' '|')"
+fi
+missing_files=""
+while IFS=$'\t' read -r _id _author _path; do
+    [[ -n "$_path" && ! -f "$REPO_ROOT/$_path" ]] && missing_files+=" $_path"
+done <<< "$parsed"
+if [[ -z "$missing_files" ]]; then
+    _pass "changelog: every referenced sql/updates file exists"
+else
+    _fail "changelog references missing files:$missing_files"
+fi
+
 printf "\n%d passed, %d failed\n" "$PASS" "$FAIL"
 (( FAIL == 0 ))