aTw5QjhC

1'use strict'
2const co = require('co')
3const fs = require('mz/fs')
4const sqlite3 = require('../node-sqlite3') // custom version sqlite3
5
6co(function* () {
7  try {
8    yield fs.unlink('old.db')
9    yield fs.unlink('new.db')
10  } catch (e) {}
11
12  const oldDb = new sqlite3.Database('old.db')
13  yield setup(oldDb)
14  yield mock(oldDb)
15
16  const newDb = new sqlite3.Database('new.db')
17  yield setup(newDb)
18  yield migrate(oldDb, newDb)
19
20}).then(() => {
21  console.log('done')
22}).catch(err => {
23  console.error(err)
24})
25
26function* migrate (from, to) {
27  const tables = yield t2p(cb => from.all('SELECT name FROM sqlite_master WHERE type="table"', cb))
28
29  for (let i = 0; i < tables.length; i++) {
30    const name = tables[i].name
31    const rows = yield t2p(cb => from.all(`SELECT * FROM ${name}`, cb)) // todo 分页分批
32
33    if (rows.length) {
34      const row0 = rows[0]
35      const keys = rowKeys(row0)
36      const keyHolder = keys.join(',')
37      const valueHolder = keys.map(k => `?`).join(',')
38
39      const stmt = to.prepare(`INSERT INTO ${name} (${keyHolder}) VALUES (${valueHolder})`)
40      for (let j = 0; j < rows.length; j++) {
41        const values = rowValues(keys, rows[j])
42        stmt.run(values)
43      }
44      yield t2p(cb => stmt.finalize(cb))
45    }
46  }
47}
48
49function rowKeys (row) {
50  return Object.keys(row).sort()
51}
52function rowValues (keys, row) {
53  return keys.reduce((m, k) => {
54    return m.push(row[k]), m
55  }, [])
56}
57
58function* setup (db) {
59  yield t2p(cb => db.run(`
60    CREATE TABLE IF NOT EXISTS lorem (info TEXT, qq INT, happy BOOLEAN);
61  `, cb))
62}
63function* mock (db) {
64  const stmt = db.prepare("INSERT INTO lorem VALUES (?, ?, ?)")
65  for (let i = 0; i < 10; i++) {
66    stmt.run(["Ipsum " + i, i * 100, true])
67  }
68  yield t2p(cb => stmt.finalize(cb))
69}
70
71function t2p (thunk) {
72  return new Promise((rs, rj) => {
73    // thunk((err) => {
74    thunk(function (err) {
75      const rest = [].slice.call(arguments, 1)
76      if (err) rj(err)
77      else rs(rest.length > 1 ? rest : rest[0])
78    })
79  })
80}