#!/usr/bin/env bash
# oracle-to-postgresql-empty-string-demo-v1.0.sh
# 复现：Oracle 的 '' = NULL 迁到 PostgreSQL 后会怎样变（盈尺 2026-10-30 实测用脚本）。
# 会在 Oracle 测试 schema 里建表 DEMO_CUST/DEMO_SEM（先 DROP），在 PG 新建数据库 $PGDB（先 DROP）。
# 只能对测试库运行！必须显式 DEMO_CONFIRM=yes。
# 实测环境：Oracle AI Database 26ai Free 23.26.3（docker 镜像 gvenzl/oracle-free:23-slim-faststart）
#           PostgreSQL 17.11；box 8 核 / 15GB，自造 100 万行。
#
# 用法：
#   DEMO_CONFIRM=yes ORA_CONN='lab/密码@localhost/FREEPDB1' ./oracle-to-postgresql-empty-string-demo-v1.0.sh
# 可选环境变量：
#   ORA_SQLPLUS  默认 "docker exec -i ora-lab sqlplus -s"（本机装了 sqlplus 就写 "sqlplus -s"）
#   PSQL         默认 "psql"（需要能 CREATE DATABASE 的账号）
#   PGDB         默认 ora2pg_empty_demo
#   ROWS_K       造数行数（千行），默认 1000 = 100 万行
set -euo pipefail
[ "${DEMO_CONFIRM:-}" = "yes" ] || { echo "拒绝运行：只能对测试库执行，确认后加 DEMO_CONFIRM=yes"; exit 1; }
ORA_CONN=${ORA_CONN:?请设置 ORA_CONN，例如 lab/密码@localhost/FREEPDB1}
ORA_SQLPLUS=${ORA_SQLPLUS:-"docker exec -i ora-lab sqlplus -s"}
PSQL=${PSQL:-psql}
PGDB=${PGDB:-ora2pg_empty_demo}
ROWS_K=${ROWS_K:-1000}
WORK=$(mktemp -d)
trap 'rm -rf "$WORK"' EXIT
chmod 755 "$WORK"
ora() { $ORA_SQLPLUS "$ORA_CONN"; }
pg()  { $PSQL -X -v ON_ERROR_STOP=0 -d "$PGDB" "$@"; }

echo "===== O1 Oracle：'' 的语义 ====="
ora <<'SQL'
set feedback on linesize 200 null [NULL]
select banner_full from v$version;
select case when '' is null then 'Y' else 'N' end as empty_is_null, length('') as len_empty,
       'A' || null || 'B' as concat_null, nvl('', 'fallback') as nvl_empty from dual;
begin execute immediate 'drop table demo_sem purge'; exception when others then null; end;
/
create table demo_sem (id number primary key, nick varchar2(30) not null, email varchar2(60), constraint uq_demo_sem unique (email));
insert into demo_sem values (1, 'a', '');
insert into demo_sem values (2, 'b', '');
insert into demo_sem values (3, '', 'c@lab.test');
commit;
select count(*) total, sum(case when email is null then 1 else 0 end) email_is_null from demo_sem;
exit
SQL

echo "===== O2 Oracle：造 ${ROWS_K}k 行，每 50 行一行写 '' ====="
ora <<SQL
set feedback on timing on linesize 200 pagesize 100
begin execute immediate 'drop table demo_cust purge'; exception when others then null; end;
/
create table demo_cust (id number(10) primary key, phone varchar2(20), email varchar2(60),
  nick varchar2(30) not null, created date not null, constraint uq_demo_cust_email unique (email));
insert /*+ append */ into demo_cust
select id, case when mod(id, 50) = 0 then '' else '138' || lpad(id, 8, '0') end,
       case when mod(id, 50) = 0 then '' else 'u' || id || '@lab.test' end,
       'n' || id, date '2026-01-01' + mod(id, 300) + mod(id, 86400) / 86400
from (select (a.n - 1) * 1000 + b.n as id
        from (select level n from dual connect by level <= ${ROWS_K}) a,
             (select level n from dual connect by level <= 1000) b);
commit;
create index ix_demo_phone on demo_cust(phone);
exec dbms_stats.gather_table_stats(user, 'DEMO_CUST', cascade => true)
select count(*) total, sum(case when phone is null then 1 else 0 end) phone_is_null from demo_cust;
exit
SQL

echo "===== O3 Oracle：WHERE phone IS NULL 的执行计划（单列 B-tree 不存全 NULL 键）====="
ora <<'SQL'
set feedback on timing on linesize 200 pagesize 200
select /*+ gather_plan_statistics */ count(*) from demo_cust where phone is null;
select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));
create index ix_demo_phone_n on demo_cust(phone, 0);
select /*+ gather_plan_statistics */ count(*) from demo_cust where phone is null;
select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));
drop index ix_demo_phone_n;
select count(*) as concat_result_null from demo_cust where ('tel:' || phone) is null;
exit
SQL

echo "===== O4 Oracle：导出 CSV（模拟迁移）====="
ora > "$WORK/cust.csv" <<'SQL'
set markup csv on quote on
set feedback off heading off pagesize 0 trimspool on
select id, phone, email, nick, to_char(created, 'YYYY-MM-DD HH24:MI:SS') from demo_cust order by id;
exit
SQL
sed -i '/^\s*$/d' "$WORK/cust.csv"
echo "csv_lines=$(wc -l < "$WORK/cust.csv")"

echo "===== O5 Oracle：切换后同一条 INSERT 再写 1 万行 ====="
ora <<SQL
set feedback on timing on linesize 200
insert into demo_cust
select id, case when mod(id, 50) = 0 then '' else '138' || lpad(id, 8, '0') end,
       case when mod(id, 50) = 0 then '' else 'u' || id || '@lab.test' end,
       'n' || id, date '2026-10-30' + mod(id, 86400) / 86400
from (select ${ROWS_K}000 + level as id from dual connect by level <= 10000);
commit;
select count(*) total, sum(case when phone is null then 1 else 0 end) phone_is_null,
       sum(case when phone = '' then 1 else 0 end) phone_eq_empty from demo_cust;
exit
SQL

echo "===== P0 PostgreSQL：建库 $PGDB ====="
$PSQL -X -d postgres -c "DROP DATABASE IF EXISTS $PGDB" -c "CREATE DATABASE $PGDB"

echo "===== P1 PostgreSQL：'' 的语义 ====="
pg <<'SQL'
\pset null [NULL]
SELECT version();
SELECT ('' IS NULL) AS empty_is_null, length('') AS len_empty, 'A' || NULL || 'B' AS concat_null,
       concat('A', NULL, 'B') AS concat_fn, coalesce('', 'fallback') AS coalesce_empty;
CREATE TABLE demo_sem (id int PRIMARY KEY, nick varchar(30) NOT NULL, email varchar(60), CONSTRAINT uq_demo_sem UNIQUE (email));
INSERT INTO demo_sem VALUES (1, 'a', '');
INSERT INTO demo_sem VALUES (2, 'b', '');
INSERT INTO demo_sem VALUES (3, '', 'c@lab.test');
SELECT count(*) total, count(*) FILTER (WHERE email IS NULL) email_is_null, count(*) FILTER (WHERE email = '') email_eq_empty FROM demo_sem;
SQL

echo "===== P2 PostgreSQL：导入 Oracle CSV ====="
pg <<SQL
\timing on
CREATE TABLE demo_cust (id bigint PRIMARY KEY, phone varchar(20), email varchar(60),
  nick varchar(30) NOT NULL, created timestamp(0) NOT NULL, CONSTRAINT uq_demo_cust_email UNIQUE (email));
\copy demo_cust FROM '$WORK/cust.csv' WITH (FORMAT csv)
CREATE INDEX ix_demo_phone ON demo_cust(phone);
VACUUM ANALYZE demo_cust;
SELECT count(*) total, count(*) FILTER (WHERE phone IS NULL) phone_is_null, count(*) FILTER (WHERE phone = '') phone_eq_empty FROM demo_cust;
SQL

echo "===== P3 PostgreSQL：切换后同一条 INSERT ====="
pg <<SQL
\timing on
INSERT INTO demo_cust
SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END,
       CASE WHEN id % 50 = 0 THEN '' ELSE 'u' || id || '@lab.test' END,
       'n' || id, timestamp '2026-10-30' + (id % 86400) * interval '1 second'
FROM generate_series(${ROWS_K}001, ${ROWS_K}000 + 10000) AS id;
-- email 修成 NULL，phone 仍写 ''
INSERT INTO demo_cust
SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END,
       CASE WHEN id % 50 = 0 THEN NULL ELSE 'u' || id || '@lab.test' END,
       'n' || id, timestamp '2026-10-30' + (id % 86400) * interval '1 second'
FROM generate_series(${ROWS_K}001, ${ROWS_K}000 + 10000) AS id;
SELECT count(*) total, count(*) FILTER (WHERE phone IS NULL) phone_is_null,
       count(*) FILTER (WHERE phone = '') phone_eq_empty FROM demo_cust;
SELECT count(*) AS concat_result_null FROM demo_cust WHERE ('tel:' || phone) IS NULL;
INSERT INTO demo_cust VALUES (9000000001, '13900000001', 'x@lab.test', '', now());
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM demo_cust WHERE phone IS NULL;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM demo_cust WHERE phone IS NULL OR phone = '';
SQL

echo "===== P3b PostgreSQL：只读扫描 '' 与 NULL（同目录有 postgresql-empty-string-scan-readonly-v1.0.sql 时）====="
SCAN="$(dirname "$0")/postgresql-empty-string-scan-readonly-v1.0.sql"
if [ -f "$SCAN" ]; then pg < "$SCAN"; else echo "跳过：未找到 $SCAN"; fi

echo "===== P4 PostgreSQL：三种处理写入 20 万行 x 3 轮 ====="
pg <<'SQL'
\timing on
CREATE TABLE t_plain (id bigint PRIMARY KEY, phone varchar(20));
CREATE TABLE t_check (id bigint PRIMARY KEY, phone varchar(20) CONSTRAINT ck_phone_not_empty CHECK (phone <> ''));
CREATE TABLE t_trg   (id bigint PRIMARY KEY, phone varchar(20));
CREATE FUNCTION empty_to_null_phone() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.phone := NULLIF(NEW.phone, ''); RETURN NEW; END $$;
CREATE TRIGGER trg_phone_empty_to_null BEFORE INSERT OR UPDATE OF phone ON t_trg
  FOR EACH ROW EXECUTE FUNCTION empty_to_null_phone();
INSERT INTO t_check SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
\echo round 1
INSERT INTO t_plain SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
INSERT INTO t_check SELECT id, CASE WHEN id % 50 = 0 THEN NULL ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
INSERT INTO t_trg   SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
TRUNCATE t_plain, t_check, t_trg;
\echo round 2
INSERT INTO t_plain SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
INSERT INTO t_check SELECT id, CASE WHEN id % 50 = 0 THEN NULL ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
INSERT INTO t_trg   SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
TRUNCATE t_plain, t_check, t_trg;
\echo round 3
INSERT INTO t_plain SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
INSERT INTO t_check SELECT id, CASE WHEN id % 50 = 0 THEN NULL ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
INSERT INTO t_trg   SELECT id, CASE WHEN id % 50 = 0 THEN '' ELSE '138' || lpad(id::text, 8, '0') END FROM generate_series(1, 200000) id;
SELECT 't_plain' t, count(*) FILTER (WHERE phone IS NULL) is_null, count(*) FILTER (WHERE phone = '') eq_empty FROM t_plain
UNION ALL SELECT 't_check', count(*) FILTER (WHERE phone IS NULL), count(*) FILTER (WHERE phone = '') FROM t_check
UNION ALL SELECT 't_trg', count(*) FILTER (WHERE phone IS NULL), count(*) FILTER (WHERE phone = '') FROM t_trg;
SQL

echo "===== P5 PostgreSQL：存量清洗 + 加约束 ====="
pg <<SQL
\timing on
BEGIN;
UPDATE demo_cust SET phone = NULL WHERE phone = '';
UPDATE demo_cust SET nick = 'n' || id WHERE nick = '';
COMMIT;
ALTER TABLE demo_cust ADD CONSTRAINT ck_demo_phone_not_empty CHECK (phone <> '') NOT VALID;
ALTER TABLE demo_cust VALIDATE CONSTRAINT ck_demo_phone_not_empty;
INSERT INTO demo_cust VALUES (9000000002, '', NULL, 'n9000000002', now());
SELECT count(*) FILTER (WHERE phone IS NULL) phone_is_null, count(*) FILTER (WHERE phone = '') phone_eq_empty FROM demo_cust;
SQL
echo "完成。清理：Oracle 里 DROP TABLE demo_cust/demo_sem PURGE；PG 里 DROP DATABASE $PGDB。"