Electron 中集成 SQLite3 数据库的最佳实践
站长
· 阅读数 47
本文用到的 SQLite 的代码库为 better-sqlite3，为什么使用 better-sqlite3 而不使用 node-sqlite3，主要就是性能更好。
详细可以查看官方文档说明 why-should-i-use-this-instead-of-node-sqlite3
如果你使用的是 node-sqlite3 库，那么你可以通过 sequelize ORM 自动维护数据库的迭代升级，目前 sequelize 并不支持 better-sqlite3，不过 sequelize 官方有计划用 better-sqlite3 替换掉 node-sqlite3。
关于 better-sqlite3 的加密套件可以使用库 better-sqlite3-multiple-ciphers，也可自己根据官方文档自己编译
本文所有的代码都来自自己写的项目中，具体代码都在这个文件夹：electron_client/src/main/db
那么本文开始。
目标
- 在 Electron 的主进程中初始化 SQLite 以及连接，保证这一过程和后续所有的数据库操作（增删改查）都不阻塞主进程，保证应用的流畅性，以及监控每次 sql 调用的耗时
- 自己实现一套后续维护 SQLite 数据库发生变更的迭代升级方案
SQLite 集成方案
总的来说就是通过 Node 的 worker_threads 新开启一个线程，将数据库的所有操作放在这个线程中执行。
整个流程：
1 渲染进程调用 -> 2 主进程接收 -> 3 主进程通知子线程 -> 4 子线程中的sqlite响应，执行sql返回结果 ->
5 主进程拿到结果返回给渲染进程 -> 6 渲染进程拿到结果，结束
渲染进程调用，到主进程接收 通过 Electron 的 invoke 和 handle 实现
主进程接收到渲染进程的通知后，只需要发送一个消息给子线程（带上参数），然后就不需要做任何事情（等待返回结果执行Promise的resolve）
子线程收到消息后通过参数执行对应的操作（sql），然后将结果通过消息发送给主进程
主进程拿到结果返回给渲染进程
首先贴出相关代码。关于 Node 的线程的详细解释可以看这一篇 文档，写的很好。
App 入口文件 main.ts 代码：
...
import { MainSQL } from './db/main';
import { initialize as SQLChannelsInitialize } from './SQLChannel';
const sql = new MainSQL();
...
// 用来调用初始化 SQLite 的方法
async function initializeSQL(
userDataPath: string
): Promise<{ ok: true; error: undefined } | { ok: false; error: Error }> {
// 临时方案 用于数据库加密套件的密码
const key = userInfo().username;
if (!key) {
logger.info(
'key/initialize: Generating new encryption key, since we did not find it on disk'
);
}
console.log(key);
// 全局变量 用于记录开始时间
sqlInitTimeStart = Date.now();
try {
await sql.initialize({ configDir: userDataPath, key, logger: logger });
} catch (error: unknown) {
if (error instanceof Error) {
return { ok: false, error };
}
return {
ok: false,
error: new Error(`initializeSQL: Caught a non-error '${error}'`),
};
} finally {
// 全局变量 用于记录结束时间
sqlInitTimeEnd = Date.now();
}
return { ok: true, error: undefined };
}
// app onReady 事件中开始调用初始化数据库方法 然后开启渲染进程展示页面
app.on('ready', async () => {
logger.info('app ready');
const userDataPath = app.getPath('userData');
// 国际化相关
if (!locale) {
const appLocale = process.env.NODE_ENV === 'test' ? 'en' : app.getLocale();
logger.info(`locale: ${appLocale}`);
locale = loadLocale({ appLocale });
}
const sqlInitPromise = initializeSQL(userDataPath);
const timeout = new Promise((resolve) =>
setTimeout(resolve, 3000, 'timeout')
);
Promise.race([sqlInitPromise, timeout])
.then((maybeTimeout) => {
if (maybeTimeout !== 'timeout') return;
/** 这里可以加载 loading 过渡 */
logger.info(
'sql.initialize is taking more than three seconds; showing loading dialog'
);
// loadingWindow = new BrowserWindow({
// show: false,
// width: 300,
// height: 265,
// resizable: false,
// frame: false,
// backgroundColor: '#3a76f0',
// webPreferences: {
// nodeIntegration: false,
// preload: path.join(__dirname, 'loading_preload.js'),
// },
// icon: windowIcon,
// });
// loadingWindow.once('ready-to-show', async () => {
// loadingWindow.show();
// // Wait for sql initialization to complete
// await sqlInitPromise;
// loadingWindow.destroy();
// loadingWindow = null;
// });
// loadingWindow.loadURL(prepareURL([__dirname, 'loading.html']));
})
.catch((err) => console.error(err));
const { ok, error: sqlError } = await sqlInitPromise;
console.log(ok);
if (sqlError) {
logger.error('sql.initialize was unsuccessful; returning early');
return;
}
SQLChannelsInitialize(sql);
// 数据库初始化成功之后 开始执行展示页面的过程
createLogin();
});
数据库相关代码都在 db 文件夹下：
db/main.ts 文件代码：
import { join } from 'path';
import { Worker } from 'worker_threads';
import { app } from 'electron';
import { format } from 'util';
import { strictAssert } from '../assert';
import type { ElectronLog, LogLevel } from 'electron-log';
import type { ServerInterface } from './types';
const MIN_TRACE_DURATION = 40;
export type InitializeOptions = Readonly<{
configDir: string;
key: string;
logger: ElectronLog;
}>;
type Methods = keyof Omit<ServerInterface, 'close' | 'removeDB' | 'initialize'>;
export type WorkerRequest = Readonly<
| {
type: 'init';
options: Omit<InitializeOptions, 'logger'>;
}
| {
type: 'close';
}
| {
type: 'removeDB';
}
| {
type: 'sqlCall';
method: Methods;
args: ReadonlyArray<unknown>;
}
>;
export type WrappedWorkerRequest = Readonly<{
seq: number;
request: WorkerRequest;
}>;
export type WrappedWorkerLogEntry = Readonly<{
type: 'log';
level: LogLevel;
args: ReadonlyArray<unknown>;
}>;
export type WrappedWorkerResponse =
| Readonly<{
type: 'response';
seq: number;
error: string | undefined;
response: unknown;
}>
| WrappedWorkerLogEntry;
type PromisePair<T> = {
resolve: (response: T) => void;
reject: (error: Error) => void;
};
// 核心代码 因为项目是用的 ts 写的，可以直接忽略相关的类型代码
export class MainSQL {
private readonly worker: Worker;
private isReady = false; // 数据库是否初始化
private onReady: Promise<void> | undefined;
private readonly onExit: Promise<void>; // 结束数据库的方法
private seq = 0;
private logger?: ElectronLog;
private onResponse = new Map<number, PromisePair<unknown>>();
constructor() {
// 这里的文件路径就是子线程要执行的代码的文件路径 因为整个项目都是 ts 编写，所以需要先编译成 js 代码
// webpack 里面有相关配置 electron_client/.erb/configs 文件中
const scriptDir = app.isPackaged
? join(__dirname, 'mainWorker.js')
: join(__dirname, '../../../.erb/dll/mainWorker.js');
this.worker = new Worker(scriptDir);
// 监听子线程发出的消息的事件
this.worker.on('message', (wrappedResponse: WrappedWorkerResponse) => {
if (wrappedResponse.type === 'log') {
const { level, args } = wrappedResponse;
strictAssert(this.logger !== undefined, 'Logger not initialized');
this.logger[level](`MainSQL: ${format(...args)}`);
return;
}
const { seq, error, response } = wrappedResponse;
const pair = this.onResponse.get(seq);
this.onResponse.delete(seq);
if (!pair) throw new Error(`Unexpected worker response with seq: ${seq}`);
if (error) {
pair.reject(new Error(error));
} else {
pair.resolve(response);
}
});
this.onExit = new Promise<void>((resolve) => {
this.worker.once('exit', resolve);
});
}
// 开始初始化
public async initialize({ configDir, key, logger }: InitializeOptions) {
if (this.isReady || this.onReady) {
throw new Error('Already initialized');
}
this.logger = logger;
this.onReady = this.send({ type: 'init', options: { configDir, key } });
await this.onReady;
this.onReady = undefined;
this.isReady = true;
}
// 关闭数据库的方法
public async close(): Promise<void> {
if (!this.isReady) throw new Error('Not initialized');
await this.send({ type: 'close' });
await this.onExit;
}
// 移除数据库
public async removeDB() {
await this.send({ type: 'removeDB' });
}
// 数据库的所有操作（增删改查）都会通过这个方法通知子线程来执行
public async sqlCall(
method: Methods,
args: ReadonlyArray<unknown>
): Promise<unknown> {
if (this.onReady) await this.onReady;
if (!this.isReady) throw new Error('Not initialized');
const { result, duration } = await this.send<{
result: unknown;
duration: number;
}>({
type: 'sqlCall',
method,
args,
});
// 子线程中数据库执行 sql 的耗时
if (duration > MIN_TRACE_DURATION) {
strictAssert(this.logger !== undefined, 'Logger not initialized');
this.logger.info(`MainSQL: slow query ${method} duration=${duration}ms`);
}
return result;
}
// 通过一层 Promise 包装
private async send<Response>(request: WorkerRequest): Promise<Response> {
const { seq } = this;
this.seq += 1;
const result = new Promise((resolve, reject) => {
this.onResponse.set(seq, { resolve, reject });
}) as Response;
const wrappedRequest: WrappedWorkerRequest = {
seq,
request,
};
this.worker.postMessage(wrappedRequest);
return result;
}
}
db/mainWorker.ts文件，即上面 new Worker(scriptDir) 的文件：
import { LogFunctions } from 'electron-log';
import { parentPort } from 'worker_threads';
import db from './server';
import {
WrappedWorkerRequest,
WrappedWorkerLogEntry,
WrappedWorkerResponse,
} from './main';
// 次文件必须在 worl_thread 中执行
if (!parentPort) throw new Error('Must run as a worker thread');
const port = parentPort;
// 向父进程发送消息 postMessage
function respond(seq: number, error: Error | undefined, response?: unknown) {
const wrappedResponse: WrappedWorkerResponse = {
type: 'response',
seq,
error: error?.stack,
response,
};
port.postMessage(wrappedResponse);
}
// 日志相关
const log = (
level: WrappedWorkerLogEntry['level'],
args: Array<unknown>
): void => {
const weappedResponse: WrappedWorkerResponse = {
type: 'log',
level,
args,
};
port.postMessage(weappedResponse);
};
// 'error' | 'warn' | 'info' | 'verbose' | 'debug' | 'silly';
const logger: Omit<LogFunctions, 'log'> = {
error(...args: Array<unknown>) {
log('error', args);
},
warn(...args: Array<unknown>) {
log('warn', args);
},
info(...args: Array<unknown>) {
log('info', args);
},
verbose(...args: Array<unknown>) {
log('verbose', args);
},
debug(...args: Array<unknown>) {
log('debug', args);
},
silly(...args: Array<unknown>) {
log('silly', args);
},
};
// 子线程监听事件
port.on('message', async ({ seq, request }: WrappedWorkerRequest) => {
try {
// 通过 type 判断要执行那种操作
// 初始化
if (request.type === 'init') {
await db.initialize({ ...request.options, logger });
respond(seq, undefined, undefined);
return;
}
// 关闭数据库
if (request.type === 'close') {
await db.close();
respond(seq, undefined, undefined);
process.exit(0);
return;
}
// 移除数据库
if (request.type === 'removeDB') {
await db.removeDB();
respond(seq, undefined, undefined);
return;
}
// 执行 sql
if (request.type === 'sqlCall') {
const method = db[request.method];
if (typeof method !== 'function') {
throw new Error(`Invalid sql method: ${method}`);
}
const start = Date.now();
const result = await (method as Function).apply(db, request.args);
const end = Date.now();
respond(seq, undefined, { result, duration: end - start });
return;
}
throw new Error('Unexpected request type');
} catch (error) {
respond(seq, error, undefined);
}
});
db/server.ts 文件，SqLite 相关的核心代码：
import { join } from 'path';
import { ensureDirSync, removeSync } from 'fs-extra';
import SQL from 'better-sqlite3-multiple-ciphers';
import { isString } from 'lodash';
import { updateSchema } from './migrations';
import { consoleLogger } from '../utils/consoleLogger';
import { getSchemaVersion, getUserVersion, setUserVersion } from './util';
import type { Database, Statement } from 'better-sqlite3';
import type { LogFunctions } from 'electron-log';
import type { ServerInterface } from './types';
import type { Theme } from 'App/types';
const user_id_key = 1;
// 预定义的一些方法
const dataInterface: ServerInterface = {
close,
removeDB,
//user
updateOrCreateUser,
getUserInfo,
setUserTheme,
// Server-only
initialize,
};
export default dataInterface;
type DatabaseQueryCache = Map<string, Statement<Array<unknown>>>;
const statementCache = new WeakMap<Database, DatabaseQueryCache>();
function prepare<T extends unknown[] | {}>(
db: Database,
query: string
): Statement<T> {
let dbCache = statementCache.get(db);
if (!dbCache) {
dbCache = new Map();
statementCache.set(db, dbCache);
}
let result = dbCache.get(query) as Statement<T>;
if (!result) {
result = db.prepare<T>(query);
dbCache.set(query, result);
}
return result;
}
// 开启加密套件相关
function keyDatabase(db: Database, key: string): void {
// https://github.com/m4heshd/better-sqlite3-multiple-ciphers/issues/14
db.pragma(`cipher='sqlcipher'`);
db.pragma(`legacy=4`);
// https://www.zetetic.net/sqlcipher/sqlcipher-api/#key
db.pragma(`key = '${key}'`);
}
function switchToWAL(db: Database): void {
// https://sqlite.org/wal.html
db.pragma('journal_mode = WAL');
db.pragma('synchronous = FULL');
db.pragma('fullfsync = ON');
}
// 数据库迭代升级的执行的迁移文件 后面要提到的
function migrateSchemaVersion(db: Database) {
const userVersion = getUserVersion(db);
if (userVersion > 0) return;
const schemaVersion = getSchemaVersion(db);
const newUserVersion = schemaVersion;
logger.info(
'migrateSchemaVersion: Migrating from schema_version ' +
`${schemaVersion} to user_version ${newUserVersion}`
);
setUserVersion(db, newUserVersion);
}
// 初始化
function openAndMigrateDatabase(filePath: string, key: string) {
let db: Database | undefined;
try {
db = new SQL(filePath);
keyDatabase(db, key);
switchToWAL(db);
migrateSchemaVersion(db);
return db;
} catch (error) {
logger.error(error);
if (db) db.close();
logger.info('migrateDatabase: Migration without cipher change failed');
throw new Error('migrateDatabase: Migration without cipher change failed');
}
}
const INVALID_KEY = /[^0-9A-Za-z]/;
function openAndSetUpSQLCipher(filePath: string, { key }: { key: string }) {
if (INVALID_KEY.exec(key))
throw new Error(`setupSQLCipher: key '${key}' is not valid`);
const db = openAndMigrateDatabase(filePath, key);
// Because foreign key support is not enabled by default!
db.pragma('foreign_keys = ON');
return db;
}
let globalInstance: Database | undefined;
let logger = consoleLogger;
let databaseFilePath: string | undefined;
// 主进程通知子线程要调用的初始化方法其实就是这个
async function initialize({
configDir,
key,
logger: suppliedLogger,
}: {
configDir: string;
key: string;
logger: Omit<LogFunctions, 'log'>;
}): Promise<void> {
if (globalInstance) throw new Error('Cannot initialize more than once!');
if (!isString(configDir))
throw new Error('initialize: configDir is required!');
if (!isString(key)) throw new Error('initialize: key is required!');
logger = suppliedLogger;
const dbDir = join(configDir, 'db');
// https://github.com/isaacs/node-mkdirp#methods
ensureDirSync(dbDir, { mode: 0o777 });
databaseFilePath = join(dbDir, 'db.sqlite');
logger.info(databaseFilePath);
let db: Database | undefined;
try {
db = openAndSetUpSQLCipher(databaseFilePath, { key });
updateSchema(db, logger);
globalInstance = db;
} catch (error) {
logger.error('Database startup error:', error.stack);
if (db) db.close();
throw error;
}
}
// 下面是具体方法的实现
async function close(): Promise<void> {
for (const dbRef of [globalInstance]) {
// SQLLite documentation suggests that we run `PRAGMA optimize` right
// before closing the database connection.
dbRef?.pragma('optimize');
dbRef?.close();
}
globalInstance = undefined;
}
async function removeDB(): Promise<void> {
if (globalInstance) {
try {
globalInstance.close();
} catch (error) {
logger.error('removeDB: Failed to close database:', error.stack);
}
globalInstance = undefined;
}
if (!databaseFilePath)
throw new Error(
'removeDB: Cannot erase database without a databaseFilePath!'
);
logger.warn('removeDB: Removing all database files');
removeSync(databaseFilePath);
removeSync(`${databaseFilePath}-shm`);
removeSync(`${databaseFilePath}-wal`);
}
function getInstance(): Database {
if (!globalInstance) {
throw new Error('getInstance: globalInstance not set!');
}
return globalInstance;
}
/********************************* user ************************************/
async function updateOrCreateUser(user: DB.UserAttributes) {
const db = getInstance();
const columns = { id: user_id_key, ...user };
const keys = Object.keys(columns);
const insertUser = db.prepare(
`
INSERT OR REPLACE INTO users (
${keys.join(',')}
) VALUES (
${keys.map((key) => `$${key}`).join(',')}
);
`
);
return insertUser.run(columns);
}
async function getUserInfo() {
const db = getInstance();
const user = prepare(
db,
`
SELECT * FROM users WHERE id = $id;
`
).get({ id: user_id_key });
return user as DB.UserAttributes;
}
async function setUserTheme(theme: Theme) {
const db = getInstance();
const update = db.prepare(`UPDATE users SET theme = $theme WHERE id = $id`);
return update.run({ id: user_id_key, theme });
}
db/util.ts：
import type { Database } from 'better-sqlite3';
import { isNumber } from 'lodash';
export function getSQLiteVersion(db: Database): string {
const { sqlite_version: version } = db
.prepare('select sqlite_version() AS sqlite_version')
.get();
return version;
}
export function getSchemaVersion(db: Database): number {
return db.pragma('schema_version', { simple: true });
}
export function setUserVersion(db: Database, version: number): void {
if (!isNumber(version)) {
throw new Error(`setUserVersion: version ${version} is not a number`);
}
db.pragma(`user_version = ${version}`);
}
export function getUserVersion(db: Database): number {
return db.pragma('user_version', { simple: true });
}
export function getSQLCipherVersion(db: Database) {
return db.pragma('cipher_version', { simple: true });
}
至此，主进程关于 SQLite 相关的代码实现都已经结束，那么渲染进程怎么调用：
主要通过 Electron 的主渲染进程的通信机制实现，渲染进程通过 invoke 告知主进程，然后主进程通过 handle 监听，然后通过子线程数据库调用，返回结果。
主进程添加监听 SQLChannel.ts 文件：
import { ipcMain } from 'electron';
type SQLType = {
sqlCall(callName: string, args: ReadonlyArray<unknown>): unknown;
};
let sql: SQLType | undefined;
let initialized = false;
const SQL_CHANNEL_KEY = 'sql-channel';
export function initialize(mainSQL: SQLType): void {
if (initialized) {
throw new Error('sqlChannels: already initialized!');
}
initialized = true;
sql = mainSQL;
// 注册监听事件
ipcMain.handle(SQL_CHANNEL_KEY, async (_event, callName, ...args) => {
if (!sql) {
throw new Error(`${SQL_CHANNEL_KEY}: Not yet initialized!`);
}
return sql.sqlCall(callName, args);
});
}
渲染进程先定义好一些方法 db/client.ts：
import { ipcRenderer as ipc } from 'electron';
import Logging from '../LogForRenderer';
import { compact, fromPairs, isFunction, map, toPairs } from 'lodash';
import createTaskWithTimeout from '../utils/taskWithTimeout';
import { ClientInterface, ServerInterface } from './types';
import { Theme } from 'App/types';
let activeJobCount = 0;
// ipcRenderer.setMaxListeners(0);
const DATABASE_UPDATE_TIMEOUT = 2 * 60 * 1000; // two minutes
const SQL_CHANNEL_KEY = 'sql-channel';
const log = Logging().getLogger();
const dataInterface: ClientInterface = {
close,
removeDB,
// user
updateOrCreateUser,
getUserInfo,
setUserTheme,
};
export default dataInterface;
const channelsAsUnknown = fromPairs(
compact(
map(toPairs(dataInterface), ([name, value]: [string, unknown]) => {
if (isFunction(value)) {
return [name, makeChannel(name)];
}
return null;
})
)
) as unknown;
const channels: ServerInterface = channelsAsUnknown as ServerInterface;
function makeChannel(fnName: string) {
return async (...args: ReadonlyArray<unknown>) => {
activeJobCount += 1;
return createTaskWithTimeout(async () => {
try {
// 调用
return await ipc.invoke(SQL_CHANNEL_KEY, fnName, ...args);
} finally {
activeJobCount -= 1;
if (activeJobCount === 0) {
// resolveShutdown?.();
}
}
}, `SQL channel call (${fnName})`)();
};
}
// Note: will need to restart the app after calling this, to set up afresh
async function close(): Promise<void> {
await channels.close();
}
// Note: will need to restart the app after calling this, to set up afresh
async function removeDB(): Promise<void> {
await channels.removeDB();
}
function updateOrCreateUser(user: DB.UserAttributes) {
return channels.updateOrCreateUser(user);
}
async function getUserInfo() {
return channels.getUserInfo();
}
async function setUserTheme(theme: Theme) {
return channels.setUserTheme(theme);
}
然后在创建 BrowserWindow 的时候，通过 preload 提前将这些方法写入到页面的 js 环境中 preload.js：
import { contextBridge, ipcRenderer } from 'electron';
import sqlClient from '../db/client';
import { Theme, WindowName } from '../../types';
(async function () {
try {
const localeMessages = ipcRenderer.sendSync('locale-data');
let UserInfo = await sqlClient.getUserInfo();
ipcRenderer.send(
'native-theme:init',
UserInfo.theme ? UserInfo.theme : Theme.system
);
// exposeInMainWorld don't support class
contextBridge.exposeInMainWorld('Context', {
platform: process.platform,
NODE_ENV: process.env.NODE_ENV,
ROOT_PATH: window.location.href.startsWith('file') ? '../../' : '/',
getUserInfo: () => UserInfo,
updateUserInfo: (userInfo: DB.UserAttributes) => {
UserInfo = { ...userInfo };
},
windowOpen: (args: Windows.Args) => ipcRenderer.send('window:open', args),
windowClose: (name: WindowName) => ipcRenderer.send('window:close', name),
getUserTheme: (): Theme => ipcRenderer.sendSync('native-theme:get_user'),
getSystemTheme: (): Exclude<Theme, 'system'> =>
ipcRenderer.sendSync('native-theme:get_system'),
themeSetting: (theme: Theme) =>
ipcRenderer.send('native-theme:setting', theme),
// The value of theme setting is system. just change app theme, not to change value of setting.
themeChangedListener: (fn: ThemeChangedListenerFN) => {
ipcRenderer.on('native-theme:changed', (_event: unknown) => {
fn();
});
},
// from setting
themeSettingListener: (fn: ThemeSettingListenerFN) => {
ipcRenderer.on('native-theme:setting', (_event, theme: Theme) => {
fn(theme);
});
},
localeMessages,
sqlClient,
});
} catch (err) {
console.error(err);
}
console.log('preload complete');
})();
页面中就可以直接通过调用 window.Context.sqlClient.getUserInfo() 直接执行 sql 获取用户信息。
SQLite 数据库发生变更的迭代升级方案
主要利用 SQLite 的 user_version Pragma 来实现：
**user_version** Pragma 获取或设置存储在数据库头的用户自定义的版本值。语法如下：
PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;
这是一个 32 位的有符号整数值，可以由开发人员设置，用于版本跟踪的目的。
user_version 如果不设置，默认是 0。
相关代码 migrations/index.ts 文件中：
import type { Database } from 'better-sqlite3';
import type { LogFunctions } from 'electron-log';
import {
getSchemaVersion,
getSQLCipherVersion,
getSQLiteVersion,
getUserVersion,
} from '../util';
//比如 第一个版本 只需要新建一张 user 表
function updateToSchemaVersion1(
currentVersion: number,
db: Database,
logger: Omit<LogFunctions, 'log'>
) {
// 这里是关键
// 第一次安装 app 的时候，currentVersion为 0，那么会执行这个方法
// 执行完之后 设置 db.pragma('user_version = 1');
// 下次打开 app 这个方法会被跳过
// 就算数据库被移除，下次打开app 重新创建数据库，user_version = 0，还是会被初始化执行
if (currentVersion >= 1) return;
logger.info('updateToSchemaVersion1: starting...');
db.transaction(() => {
db.exec(`
CREATE TABLE users(
id INTEGER PRIMARY KEY,
userId INTEGER NOT NULL,
account STRING NOT NULL,
token STRING,
avatar STRING DEFAULT NULL,
email STRING DEFAULT NULL,
theme STRING DEFAULT NULL,
regisTime STRING,
updateTime STRING
);
`);
db.pragma('user_version = 1');
})();
logger.info('updateToSchemaVersion1: success!');
}
// 第二个版本 新建一个 group 表
function updateToSchemaVersion2(
currentVersion: number,
db: Database,
logger: Omit<LogFunctions, 'log'>
) {
if (currentVersion >= 2) return;
logger.info('updateToSchemaVersion1: starting...');
db.transaction(() => {
db.exec(`
CREATE TABLE group(
id INTEGER PRIMARY KEY,
userId INTEGER NOT NULL,
account STRING NOT NULL,
token STRING,
avatar STRING DEFAULT NULL,
email STRING DEFAULT NULL,
theme STRING,
regisTime STRING,
updateTime STRING
);
`);
db.pragma('user_version = 2');
})();
logger.info('updateToSchemaVersion2: success!');
}
...
// 或者执行其他的操作...
...
export const SCHEMA_VERSIONS = [updateToSchemaVersion1];
// 数据库迭代迁移执行这个方法
export function updateSchema(
db: Database,
logger: Omit<LogFunctions, 'log'>
): void {
const sqliteVersion = getSQLiteVersion(db);
const sqlcipherVersion = getSQLCipherVersion(db);
const userVersion = getUserVersion(db);
const maxUserVersion = SCHEMA_VERSIONS.length;
const schemaVersion = getSchemaVersion(db);
logger.info(
'updateSchema:\n',
` Current user_version: ${userVersion};\n`,
` Most recent db schema: ${maxUserVersion};\n`,
` SQLite version: ${sqliteVersion};\n`,
` SQLCipher version: ${sqlcipherVersion};\n`,
` (deprecated) schema_version: ${schemaVersion};\n`
);
if (userVersion > maxUserVersion) {
throw new Error(
`SQL: User version is ${userVersion} but the expected maximum version ` +
`is ${maxUserVersion}. Did you try to start an old version of App?`
);
}
// 执行所有数据库迭代操作的方法 updateToSchemaVersion1 updateToSchemaVersion2...
for (let index = 0; index < maxUserVersion; index += 1) {
const runSchemaUpdate = SCHEMA_VERSIONS[index];
runSchemaUpdate(userVersion, db, logger);
}
}
最后
代码都在这里：electron_client
其实这个项目已经实现了关于 Electron 生产包的 全量 和 增量 更新，package.json 文件中有相关的打包命令：
"publish": "ts-node ./.erb/scripts/publish.ts",
"publish:full:win32": "npm run package:win32 && cross-env PUBLISH_TYPE=full platform=win32 archs=ia32 npm run publish",
"publish:full:win64": "npm run package:win64 && cross-env PUBLISH_TYPE=full platform=win32 archs=x64 npm run publish",
"publish:full:mac": "npm run package:mac && cross-env PUBLISH_TYPE=full platform=darwin npm run publish",
"publish:asar:win32": "npm run package:win32 && cross-env PUBLISH_TYPE=asar platform=win32 archs=ia32 npm run publish",
"publish:asar:win64": "npm run package:win64 && cross-env PUBLISH_TYPE=asar platform=win32 archs=x64 npm run publish",
"publish:asar:mac": "npm run package:mac && cross-env PUBLISH_TYPE=asar platform=darwin npm run publish",
通过执行 publish.ts 脚本打出 全量 或者 增量 更新的包。
感谢各位能看到最后。
转载自:https://juejin.cn/post/7202807471881306172