326 lines
9.9 KiB
JavaScript
326 lines
9.9 KiB
JavaScript
// 本地数据库管理模块
|
||
|
||
const DB_NAME = 'accounting.db'
|
||
const DB_VERSION = 1
|
||
let database = null
|
||
|
||
// 跨平台数据库兼容性处理
|
||
function openDatabaseSync(name, version, displayName, estimatedSize) {
|
||
// #ifdef APP-PLUS
|
||
// App环境使用plus.sqlite
|
||
return plus.sqlite.openSQLiteSync({
|
||
name: name,
|
||
path: `_doc/${name}`
|
||
})
|
||
// #endif
|
||
|
||
// #ifdef H5
|
||
// H5环境使用Web SQL（如果支持）
|
||
if (window.openDatabase) {
|
||
return window.openDatabase(name, version, displayName, estimatedSize)
|
||
} else {
|
||
throw new Error('浏览器不支持Web SQL数据库，请使用支持的浏览器或App版本')
|
||
}
|
||
// #endif
|
||
|
||
// #ifdef MP
|
||
// 小程序环境暂不支持本地SQLite数据库
|
||
throw new Error('小程序环境暂不支持离线数据库功能')
|
||
// #endif
|
||
}
|
||
|
||
// 初始化数据库
|
||
export async function initDatabase() {
|
||
return new Promise((resolve, reject) => {
|
||
try {
|
||
// 打开数据库
|
||
database = openDatabaseSync(DB_NAME, DB_VERSION, '记账数据库', 10 * 1024 * 1024)
|
||
|
||
// 创建表结构
|
||
createTables().then(() => {
|
||
// 插入预设分类数据
|
||
insertDefaultCategories().then(() => {
|
||
resolve(true)
|
||
}).catch(err => {
|
||
console.error('插入预设分类失败:', err)
|
||
reject(err)
|
||
})
|
||
}).catch(err => {
|
||
console.error('创建表结构失败:', err)
|
||
reject(err)
|
||
})
|
||
} catch (error) {
|
||
console.error('初始化数据库失败:', error)
|
||
reject(error)
|
||
}
|
||
})
|
||
}
|
||
|
||
// 创建表结构
|
||
async function createTables() {
|
||
return new Promise((resolve, reject) => {
|
||
try {
|
||
const createTableSqls = [
|
||
// 用户表
|
||
`CREATE TABLE IF NOT EXISTS user (
|
||
id TEXT PRIMARY KEY,
|
||
username TEXT,
|
||
nickname TEXT,
|
||
is_offline INTEGER DEFAULT 0,
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)`,
|
||
|
||
// 分类表
|
||
`CREATE TABLE IF NOT EXISTS category (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id TEXT,
|
||
name TEXT NOT NULL,
|
||
icon TEXT,
|
||
type INTEGER NOT NULL,
|
||
sort_order INTEGER DEFAULT 0,
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)`,
|
||
|
||
// 账单表
|
||
`CREATE TABLE IF NOT EXISTS bill (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id TEXT NOT NULL,
|
||
category_id INTEGER NOT NULL,
|
||
amount REAL NOT NULL,
|
||
description TEXT,
|
||
bill_date DATE NOT NULL,
|
||
type INTEGER NOT NULL,
|
||
is_synced INTEGER DEFAULT 0,
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)`,
|
||
|
||
// 账户表
|
||
`CREATE TABLE IF NOT EXISTS account (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
initial_balance REAL NOT NULL DEFAULT 0.00,
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(user_id, name)
|
||
)`,
|
||
|
||
// 预算表
|
||
`CREATE TABLE IF NOT EXISTS budget (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id TEXT NOT NULL,
|
||
year INTEGER NOT NULL,
|
||
month INTEGER NOT NULL,
|
||
amount REAL NOT NULL DEFAULT 0.00,
|
||
is_synced INTEGER DEFAULT 0,
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(user_id, year, month)
|
||
)`
|
||
]
|
||
|
||
// 执行事务创建所有表
|
||
database.transaction((tx) => {
|
||
for (const sql of createTableSqls) {
|
||
tx.executeSql(sql)
|
||
}
|
||
})
|
||
|
||
resolve(true)
|
||
} catch (error) {
|
||
console.error('创建表结构失败:', error)
|
||
reject(error)
|
||
}
|
||
})
|
||
}
|
||
|
||
// 插入预设分类数据
|
||
async function insertDefaultCategories() {
|
||
return new Promise((resolve, reject) => {
|
||
try {
|
||
// 检查是否已存在预设分类
|
||
const checkSql = 'SELECT COUNT(*) as count FROM category WHERE user_id IS NULL'
|
||
database.transaction((tx) => {
|
||
tx.executeSql(checkSql, [], (tx, result) => {
|
||
if (result.rows[0].count === 0) {
|
||
// 插入预设支出分类
|
||
const expenseCategories = [
|
||
{ name: '餐饮', icon: '🍔', type: 1, sort_order: 1 },
|
||
{ name: '交通', icon: '🚗', type: 1, sort_order: 2 },
|
||
{ name: '购物', icon: '🛍️', type: 1, sort_order: 3 },
|
||
{ name: '娱乐', icon: '🎬', type: 1, sort_order: 4 },
|
||
{ name: '医疗', icon: '🏥', type: 1, sort_order: 5 },
|
||
{ name: '教育', icon: '📚', type: 1, sort_order: 6 },
|
||
{ name: '住房', icon: '🏠', type: 1, sort_order: 7 },
|
||
{ name: '水电', icon: '💡', type: 1, sort_order: 8 },
|
||
{ name: '通讯', icon: '📱', type: 1, sort_order: 9 },
|
||
{ name: '其他', icon: '📦', type: 1, sort_order: 10 }
|
||
]
|
||
|
||
// 插入预设收入分类
|
||
const incomeCategories = [
|
||
{ name: '工资', icon: '💰', type: 2, sort_order: 1 },
|
||
{ name: '奖金', icon: '🎁', type: 2, sort_order: 2 },
|
||
{ name: '投资', icon: '📈', type: 2, sort_order: 3 },
|
||
{ name: '兼职', icon: '💼', type: 2, sort_order: 4 },
|
||
{ name: '其他', icon: '📦', type: 2, sort_order: 5 }
|
||
]
|
||
|
||
// 执行事务插入所有预设分类
|
||
database.transaction((tx) => {
|
||
for (const cat of expenseCategories) {
|
||
const sql = `INSERT INTO category (user_id, name, icon, type, sort_order) VALUES (NULL, ?, ?, ?, ?)`
|
||
tx.executeSql(sql, [cat.name, cat.icon, cat.type, cat.sort_order])
|
||
}
|
||
|
||
for (const cat of incomeCategories) {
|
||
const sql = `INSERT INTO category (user_id, name, icon, type, sort_order) VALUES (NULL, ?, ?, ?, ?)`
|
||
tx.executeSql(sql, [cat.name, cat.icon, cat.type, cat.sort_order])
|
||
}
|
||
})
|
||
}
|
||
resolve(true)
|
||
}, (tx, error) => {
|
||
console.error('检查预设分类失败:', error)
|
||
reject(error)
|
||
})
|
||
})
|
||
} catch (error) {
|
||
console.error('插入预设分类失败:', error)
|
||
reject(error)
|
||
}
|
||
})
|
||
}
|
||
|
||
// 创建离线用户
|
||
export async function createOfflineUser() {
|
||
return new Promise((resolve, reject) => {
|
||
try {
|
||
// 检查数据库是否已初始化
|
||
if (!database) {
|
||
console.error('数据库未初始化，正在初始化...')
|
||
initDatabase().then(() => {
|
||
// 数据库初始化成功后，递归调用自己
|
||
createOfflineUser().then(resolve).catch(reject)
|
||
}).catch(err => {
|
||
console.error('数据库初始化失败:', err)
|
||
reject(new Error('数据库初始化失败: ' + err.message))
|
||
})
|
||
return
|
||
}
|
||
|
||
const userId = 'local_user'
|
||
const username = '离线用户'
|
||
const nickname = '离线用户'
|
||
|
||
// 检查是否已存在离线用户
|
||
const checkSql = 'SELECT * FROM user WHERE id = ?'
|
||
database.transaction((tx) => {
|
||
tx.executeSql(checkSql, [userId], (tx, result) => {
|
||
if (result.rows.length === 0) {
|
||
// 创建离线用户
|
||
const insertSql = `INSERT INTO user (id, username, nickname, is_offline) VALUES (?, ?, ?, 1)`
|
||
tx.executeSql(insertSql, [userId, username, nickname], (tx, result) => {
|
||
// 创建默认账户
|
||
const accountSql = `INSERT INTO account (user_id, name, initial_balance) VALUES (?, '默认账户', 0.00)`
|
||
tx.executeSql(accountSql, [userId], (tx, result) => {
|
||
resolve(userId)
|
||
}, (tx, error) => {
|
||
console.error('创建默认账户失败:', error)
|
||
reject(error)
|
||
})
|
||
}, (tx, error) => {
|
||
console.error('创建离线用户失败:', error)
|
||
reject(error)
|
||
})
|
||
} else {
|
||
resolve(userId)
|
||
}
|
||
}, (tx, error) => {
|
||
console.error('检查离线用户失败:', error)
|
||
reject(error)
|
||
})
|
||
})
|
||
} catch (error) {
|
||
console.error('创建离线用户失败:', error)
|
||
reject(error)
|
||
}
|
||
})
|
||
}
|
||
|
||
// 数据库操作封装
|
||
const db = {
|
||
// 执行查询
|
||
query: (sql, params = []) => {
|
||
return new Promise((resolve, reject) => {
|
||
try {
|
||
database.transaction((tx) => {
|
||
tx.executeSql(sql, params, (tx, result) => {
|
||
const rows = []
|
||
for (let i = 0; i < result.rows.length; i++) {
|
||
rows.push(result.rows[i])
|
||
}
|
||
resolve(rows)
|
||
}, (tx, error) => {
|
||
console.error('查询失败:', error)
|
||
reject(error)
|
||
})
|
||
})
|
||
} catch (error) {
|
||
console.error('查询失败:', error)
|
||
reject(error)
|
||
}
|
||
})
|
||
},
|
||
|
||
// 执行更新（INSERT, UPDATE, DELETE）
|
||
update: (sql, params = []) => {
|
||
return new Promise((resolve, reject) => {
|
||
try {
|
||
database.transaction((tx) => {
|
||
tx.executeSql(sql, params, (tx, result) => {
|
||
resolve(result.rowsAffected)
|
||
}, (tx, error) => {
|
||
console.error('更新失败:', error)
|
||
reject(error)
|
||
})
|
||
})
|
||
} catch (error) {
|
||
console.error('更新失败:', error)
|
||
reject(error)
|
||
}
|
||
})
|
||
},
|
||
|
||
// 执行事务
|
||
transaction: (callback) => {
|
||
return new Promise((resolve, reject) => {
|
||
try {
|
||
database.transaction((tx) => {
|
||
callback(tx)
|
||
resolve(true)
|
||
}, (error) => {
|
||
console.error('事务执行失败:', error)
|
||
reject(error)
|
||
})
|
||
} catch (error) {
|
||
console.error('事务执行失败:', error)
|
||
reject(error)
|
||
}
|
||
})
|
||
},
|
||
|
||
// 获取数据库实例
|
||
getInstance: () => {
|
||
if (!database) {
|
||
throw new Error('数据库未初始化')
|
||
}
|
||
return database
|
||
}
|
||
}
|
||
|
||
export { db }
|