SQLite Forum

Serialize database to text
Login

Serialize database to text

(1) By sergeylapin on 2021-10-20 09:37:19 [link] [source]

Hi, I recently read https://sqlite.org/whynotgit.html article and agree a lot with points made there. However, I am not ready to make a migration from git to fossil, yet. Currently, main obstacle for me in sqlite-git integration is diffing, for some reason binary is always new from git point of view. This issues is described really well in this article - https://ongardie.net/blog/sqlite-in-git.

Diego Ongaro, suggest to implement driver to serialize/deserialize on commit sqlite db, which is what I am doing right now. Unfortunately, I have failed to find how to serialize to sqlite to op log.

Can somebody help me with that?

import sqlite3 from 'sqlite3';
import path from 'path';
import fs from 'fs';
import { execSync } from 'child_process';
import { root } from '../core';

let log: string[] = []
describe('sqlite driver', () => {
  it('unpack a to text', () => {
    log = [
      "CREATE TABLE test(info TEXT)",
      "INSERT INTO test (info) VALUES ('info1')"
    ]
    // how to get these ^^^ ?
  });

  it('pack a previously unpacked to text to b', () => {
    if (fs.existsSync(path.join(__dirname, 'b.db'))) {
      fs.unlinkSync(path.join(__dirname, 'b.db'))
    }

    let bDb = new sqlite3.Database(path.join(__dirname, 'b.db'));

    bDb.serialize(() => {
      for (let line of log) {
        bDb.run(line);
      }
    });
    expect(execSync(`cd ${root} && git diff --name-only`).toString()).toBe('');
  });

  it('copy b to c and check that git does not have changes', () => {
    execSync(`cp ${__dirname}/b.db ${__dirname}/c.db`).toString();
    expect(execSync(`cd ${root} && git diff --name-only`).toString()).toBe('');
  });
});

(2) By Ivan Tolstosheyev (itroot) on 2021-10-20 11:41:46 in reply to 1 [source]

https://www.sqlite.org/cli.html#converting_an_entire_database_to_an_ascii_text_file

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(info TEXT);
INSERT INTO test VALUES('info1');
COMMIT;
sqlite> 

(3) By sergeylapin on 2021-10-20 12:11:07 in reply to 2 [link] [source]

Thanks!