tursodatabase / libsql

libSQL is a fork of SQLite that is both Open Source, and Open Contributions.
https://turso.tech/libsql
MIT License
11.25k stars 286 forks source link

fix .dump export command #1575

Closed sivukhin closed 4 months ago

sivukhin commented 4 months ago

Context

There are few minor issues with current implementation of .dump command in the sqld:

  1. It stop after first VIRTUAL TABLE or table with sqlite_ prefix - which is a bug
    $> CREATE VIRTUAL TABLE email USING fts5(sender, title, body);
    $> CREATE TABLE t ( value );
    $> INSERT INTO t VALUES (1), (2);
    $> .dump
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    PRAGMA writable_schema=ON;
    INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql) VALUES('table','email','email',0,'CREATE VIRTUAL TABLE email USING fts5(sender, title, body)');
    PRAGMA writable_schema=OFF;
    COMMIT;
  2. It emit incorrect dump if sqld decides to preserve rowid:
    $> CREATE TABLE t ( x INTEGER PRIMARY KEY DESC );
    $> INSERT INTO t VALUES (1), (2), (10);
    $> .dump
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    CREATE TABLE IF NOT EXISTS t ( x INTEGER PRIMARY KEY DESC );
    INSERT INTO t(rowid,x) VALUES(1);
    INSERT INTO t(rowid,x) VALUES(2);
    INSERT INTO t(rowid,x) VALUES(3);
    COMMIT;