import sqlite3 from 'sqlite3'
|
import { getDataDir } from 'ee-core/ps';
|
import path from "path";
|
import fs from 'fs';
|
const sqlite = require('sqlite3').verbose();
|
|
class SQLiteDB {
|
private db!: sqlite3.Database;
|
private dbName: string;
|
|
constructor(dbName: string = "sqlite-grain.db") {
|
this.dbName = dbName;
|
}
|
|
/**
|
* 初始化数据库连接
|
*/
|
async init(): Promise<void> {
|
const dbDir = path.join(getDataDir(), "db");
|
if (!fs.existsSync(dbDir)) {
|
fs.mkdirSync(dbDir, { recursive: true });
|
}
|
const dbFile = path.join(dbDir, this.dbName);
|
|
return new Promise((resolve, reject) => {
|
this.db = new sqlite.Database(dbFile, (err) => {
|
if (err) {
|
console.error('数据库连接失败:', err);
|
reject(err);
|
} else {
|
console.log('数据库连接成功');
|
resolve();
|
}
|
});
|
});
|
}
|
|
/**
|
* 创建表
|
* @param tableName 表名
|
* @param schema 表结构定义
|
*/
|
async createTable(tableName: string, schema: string): Promise<void> {
|
return new Promise((resolve, reject) => {
|
const query = `CREATE TABLE IF NOT EXISTS ${tableName} (${schema})`;
|
this.db.run(query, (err) => {
|
if (err) {
|
reject(err);
|
} else {
|
resolve();
|
}
|
});
|
});
|
}
|
|
/**
|
* 插入数据
|
* @param tableName 表名
|
* @param data 要插入的数据对象
|
*/
|
async insert<T>(tableName: string, data: Partial<T>): Promise<number> {
|
const columns = Object.keys(data).join(', ');
|
const placeholders = Object.keys(data).map(() => '?').join(', ');
|
const values = Object.values(data);
|
|
return new Promise((resolve, reject) => {
|
const query = `INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`;
|
this.db.run(query, values, function (err) {
|
if (err) {
|
reject(err);
|
} else {
|
resolve(this.lastID);
|
}
|
});
|
});
|
}
|
|
/**
|
* 查询所有数据
|
* @param tableName 表名
|
* @param conditions 查询条件
|
* @param params 查询参数
|
*/
|
async findAll<T>(tableName: string, conditions: string = '', params: any[] = []): Promise<T[]> {
|
return new Promise((resolve, reject) => {
|
const whereClause = conditions ? `WHERE ${conditions}` : '';
|
const query = `SELECT * FROM ${tableName} ${whereClause}`;
|
|
this.db.all(query, params, (err, rows) => {
|
if (err) {
|
reject(err);
|
} else {
|
resolve(rows as T[]);
|
}
|
});
|
});
|
}
|
|
/**
|
* 查询单条数据
|
* @param tableName 表名
|
* @param conditions 查询条件
|
* @param params 查询参数
|
*/
|
async findOne<T>(tableName: string, conditions: string, params: any[]): Promise<T | null> {
|
return new Promise((resolve, reject) => {
|
const query = `SELECT * FROM ${tableName} WHERE ${conditions} LIMIT 1`;
|
this.db.get(query, params, (err, row) => {
|
if (err) {
|
reject(err);
|
} else {
|
resolve(row as T || null);
|
}
|
});
|
});
|
}
|
|
/**
|
* 更新数据
|
* @param tableName 表名
|
* @param conditions 更新条件
|
* @param data 要更新的数据
|
* @param params 条件参数
|
*/
|
async update(tableName: string, conditions: string, data: Record<string, any>, params: any[] = []): Promise<number> {
|
const setClause = Object.keys(data)
|
.map(key => `${key} = ?`)
|
.join(', ');
|
const values = [...Object.values(data), ...params];
|
|
return new Promise((resolve, reject) => {
|
const query = `UPDATE ${tableName} SET ${setClause} WHERE ${conditions}`;
|
|
this.db.run(query, values, function (err) {
|
if (err) {
|
reject(err);
|
} else {
|
resolve(this.changes);
|
}
|
});
|
});
|
}
|
|
/**
|
* 删除数据
|
* @param tableName 表名
|
* @param conditions 删除条件
|
* @param params 条件参数
|
*/
|
async delete(tableName: string, conditions: string, params: any[]): Promise<number> {
|
return new Promise((resolve, reject) => {
|
const query = `DELETE FROM ${tableName} WHERE ${conditions}`;
|
|
this.db.run(query, params, function (err) {
|
if (err) {
|
reject(err);
|
} else {
|
resolve(this.changes);
|
}
|
});
|
});
|
}
|
|
/**
|
* 执行原始SQL查询
|
* @param sql SQL语句
|
* @param params 参数
|
*/
|
async query<T>(sql: string, params: any[] = []): Promise<T[]> {
|
return new Promise((resolve, reject) => {
|
this.db.all(sql, params, (err, rows) => {
|
if (err) {
|
reject(err);
|
} else {
|
resolve(rows as T[]);
|
}
|
});
|
});
|
}
|
|
/**
|
* 查询指定表的指定列的最大值
|
* @param {object} db - SQLite3 数据库实例
|
* @param {string} tableName - 表名
|
* @param {string} columnName - 列名
|
* @returns {Promise<number | null>} - 返回最大值（如果没有数据则返回 null）
|
*/
|
async getMaxValue(params: any): Promise<number | null> {
|
const { tableName, columnName } = params;
|
|
return new Promise((resolve, reject) => {
|
const query = `SELECT MAX(${columnName}) AS max_value FROM ${tableName}`;
|
|
this.db.get(query, (err, row: { max_value?: number }) => {
|
if (err) {
|
reject(err);
|
} else {
|
// 处理查询结果
|
const maxValue = row?.max_value;
|
resolve(maxValue !== undefined ? maxValue : null);
|
}
|
});
|
});
|
}
|
|
|
/**
|
* 关闭数据库连接
|
*/
|
close(): void {
|
this.db.close();
|
}
|
}
|
|
// 创建全局数据库实例
|
const dbInstance = new SQLiteDB();
|
|
// 初始化数据库
|
async function initDb() {
|
await dbInstance.init();
|
//初始化表结构-粮食种类
|
await dbInstance.createTable('grain', `
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
code INTEGER NOT NULL,
|
no INTEGER UNIQUE NOT NULL,
|
name TEXT UNIQUE NOT NULL,
|
coef1 REAL,
|
coef2 REAL,
|
coef3 REAL,
|
coef4 REAL,
|
coef5 REAL,
|
intercept REAL,
|
flag INTEGER NOT NULL
|
`);
|
|
//初始化表结构-配置表
|
await dbInstance.createTable('config', `
|
id INTEGER PRIMARY KEY,
|
code INTEGER NOT NULL,
|
name TEXT UNIQUE NOT NULL,
|
config_data TEXT
|
`);
|
|
// 插入默认粮食数据
|
await insertDefaultData();
|
}
|
|
// 插入默认粮食数据
|
async function insertDefaultData() {
|
try {
|
// 检查是否已有数据（避免重复插入）
|
const existingData = await dbInstance.findAll('grain');
|
if (existingData.length > 0) {
|
console.log('grain 表已有数据，跳过默认数据插入');
|
return;
|
}
|
|
// 定义默认数据
|
const defaultGrains = [
|
{
|
code: 1,
|
no: 4,
|
name: '小麦',
|
flag: 1
|
},
|
{
|
code: 2,
|
no: 2,
|
name: '玉米',
|
flag: 1
|
},
|
{
|
code: 3,
|
no: 3,
|
name: '大豆',
|
flag: 1
|
},
|
{
|
code: 4,
|
no: 1,
|
name: '籼稻',
|
flag: 1
|
},
|
{
|
code: 5,
|
no: 5,
|
name: '粳稻',
|
flag: 1
|
}
|
];
|
|
// 批量插入
|
for (const grain of defaultGrains) {
|
await dbInstance.insert('grain', grain);
|
}
|
|
console.log('默认粮食数据插入成功');
|
} catch (err) {
|
console.error('插入默认粮食数据失败:', err);
|
}
|
}
|
|
|
export {
|
initDb,
|
dbInstance as db
|
}
|
