SQLite Forum

pragma table_info Bug
Login
If the generator can't be improved, you will have to improve the schema in the database.

For your convenience, here is a Proof of Concept bash script to repair the definition of CBRELEVE.

```sh
#!/bin/bash
# PoC for patching a malformed schema
# Use at your own risk.

db=tmp/test.db
tee=tmp/test.tee
mkdir -p tmp
rm -f $db*

# generate a test database
sqlite3 $db  <<'EOF'
CREATE TABLE [CBRELEVE] (
  [Clt] VARCHAR(6) NOT NULL,
  [Typ] [UNSIGNED WORD](1) NOT NULL,
  [Date] DATETEXT NOT NULL,
  [nSeq] [INTEGER UNSIGNED] NOT NULL,
  [Nf] [DOUBLE PRECISION],
  [Nom] VARCHAR(35),
  [RegClt] VARCHAR(4),
  [Tra] [UNSIGNED WORD](1),
  [PiedHtMon0] [DOUBLE PRECISION],
  [PiedHtInd0] [UNSIGNED WORD](1),
  [PiedHtMon1] [DOUBLE PRECISION],
  [PiedHtInd1] [UNSIGNED WORD](1),
  [PiedHtMon2] [DOUBLE PRECISION],
  [PiedHtInd2] [UNSIGNED WORD](1),
  [PiedHtMon3] [DOUBLE PRECISION],
  [PiedHtInd3] [UNSIGNED WORD](1),
  [PiedHtMon4] [DOUBLE PRECISION],
  [PiedHtInd4] [UNSIGNED WORD](1),
  [PiedHtMon5] [DOUBLE PRECISION],
  [PiedHtInd5] [UNSIGNED WORD](1),
  [PiedHtMon6] [DOUBLE PRECISION],
  [PiedHtInd6] [UNSIGNED WORD](1),
  [PiedHtMon7] [DOUBLE PRECISION],
  [PiedHtInd7] [UNSIGNED WORD](1),
  [PiedHtMon8] [DOUBLE PRECISION],
  [PiedHtInd8] [UNSIGNED WORD](1),
  [PiedHtMon9] [DOUBLE PRECISION],
  [PiedHtInd9] [UNSIGNED WORD](1),
  [PiedHtMon10] [DOUBLE PRECISION],
  [PiedHtInd10] [UNSIGNED WORD](1),
  [PiedHtMon11] [DOUBLE PRECISION],
  [PiedHtInd11] [UNSIGNED WORD](1),
  [dPiedTpf] [DOUBLE PRECISION],
  [dPiedMht] [DOUBLE PRECISION],
  [dPiedRem0] [DOUBLE PRECISION],
  [dPiedRem1] [DOUBLE PRECISION],
  [dPiedTx0] [DOUBLE PRECISION],
  [dPiedTx1] [DOUBLE PRECISION],
  [dPiedPnt] [DOUBLE PRECISION],
  [dPiedTva] [DOUBLE PRECISION],
  [dPiedTTC] [DOUBLE PRECISION], dPiedTaxe0 DOUBLE PRECISION DEFAULT '0', dPiedTaxe1 DOUBLE PRECISION DEFAULT '0', dPiedTaxe2 DOUBLE PRECISION DEFAULT '0', dPiedTaxe3 DOUBLE PRECISION DEFAULT '0', dPiedTaxe4 DOUBLE PRECISION DEFAULT '0', dPiedTaxe5 DOUBLE PRECISION DEFAULT '0', dPiedTaxe6 DOUBLE PRECISION DEFAULT '0', dPiedTaxe7 DOUBLE PRECISION DEFAULT '0', PiedTaxeInd0 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd1 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd2 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd3 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd4 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd5 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd6 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd7 UNSIGNED WORD(1) DEFAULT ' ', 
  CONSTRAINT [sqlite_autoindex_CBRELEVE_1] PRIMARY KEY ([Clt], [Typ], [Date], [nSeq]));
EOF

# Generate correctly spelled DDL
patch=$( sqlite3 $db \
	"SELECT quote(sql) FROM sqlite_schema \
	WHERE type=='table' AND name=='CBRELEVE'" \
| gawk '{
	t1 = gensub(/\](\([[:digit:]]+\))/,"\\1]","g",$0)
	t2 = gensub(/, ([^\n[])/,",\n\\1","g",t1)
	print t2
}' | tee $tee )

# apply the patch to  the database
sqlite3 $db \
	"PRAGMA writable_schema=on" \
	"UPDATE sqlite_schema SET sql=$patch \
	WHERE type=='table' AND name=='CBRELEVE'"

# check the schema
sqlite3 $db ".schema" | less -NS
# printf "%s\n" "$patch"
# less -NS $tee
```

Enjoy!

~~~
-- 
Regards,
Kees Nuyt
~~~