Recover Generated Columns Table
(1) By hardisk0 on 2021-05-25 11:38:53 [source]
Hi, I create a table with generated columns and insert into 2 records
$ ./sqlite3 gencol2.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> CREATE TABLE t1(
...> a INTEGER PRIMARY KEY,
...> b INT,
...> c TEXT,
...> d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
...> e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
...> );
sqlite> insert into t1 values(1,2,'Hello SQLite3');
sqlite> insert into t1 values(2,2,'Hi SQLite3');
Here is '.dump' result:
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1(
a INTEGER PRIMARY KEY,
b INT,
c TEXT,
d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
);
INSERT INTO t1 VALUES(1,2,'Hello SQLite3');
INSERT INTO t1 VALUES(2,2,'Hi SQLite3');
COMMIT;
And here is '.recover' result:
sqlite> .recover
PRAGMA foreign_keys=OFF;
BEGIN;
PRAGMA writable_schema = on;
CREATE TABLE IF NOT EXISTS t1(
a INTEGER PRIMARY KEY,
b INT,
c TEXT,
d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
);
CREATE TABLE "lost_and_found"(rootpgno INTEGER, pgno INTEGER, nfield INTEGER, id INTEGER, c0, c1, c2, c3);
INSERT INTO "lost_and_found" VALUES(2, 2, 4, 1, NULL, 2, 'Hello SQLite3', 'ell');
INSERT INTO "lost_and_found" VALUES(2, 2, 4, 2, NULL, 2, 'Hi SQLite3', 'i S');
PRAGMA writable_schema = off;
COMMIT;
The data of generated columns table was recoverd into 'lost_and_found' table.