SQLite Forum

Recover Generated Columns Table
Login

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.