const fs = require('fs');
const https = require('https');
const mysql = require('mysql2/promise'); // 使用Promise版本的mysql2
const WebSocket = require('ws');
// 数据库配置
const dbConfig = {
host: 'localhost',
user: 'newuser',
password: 'password',
database: 'smart_home',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
};
// 创建数据库连接池
const pool = mysql.createPool(dbConfig);
// 测试数据库连接
async function testDbConnection() {
try {
const connection = await pool.getConnection();
console.log('数据库连接成功');
connection.release();
} catch (error) {
console.error('数据库连接失败:', error);
}
}
async function initDatabase() {
try {
// 创建用户表
await pool.execute(`
CREATE TABLE IF NOT EXISTS users (
id VARCHAR(36) PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
phone VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
// 创建设备表（保留唯一键，但删除后续的显式索引创建）
await pool.execute(`
CREATE TABLE IF NOT EXISTS devices (
id VARCHAR(36) PRIMARY KEY,
user_id VARCHAR(36) NOT NULL,
device_id VARCHAR(50) NOT NULL,
device_type VARCHAR(50) NOT NULL,
device_model VARCHAR(50) NOT NULL,
device_serial VARCHAR(50) NOT NULL,
device_status TINYINT DEFAULT 0,
device_attributes TEXT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_device (user_id, device_id),
FOREIGN KEY (user_id) REFERENCES users(id)
)
`);
// 移除下面这行，因为 unique_device 索引已包含 device_id
// await pool.execute(`CREATE INDEX idx_device_id ON devices (device_id)`);
// 创建设备命令表
await pool.execute(`
CREATE TABLE IF NOT EXISTS device_commands (
id VARCHAR(36) PRIMARY KEY,
user_id VARCHAR(36) NOT NULL,
device_id VARCHAR(50) NOT NULL,
command_type VARCHAR(50) NOT NULL,
command_params TEXT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
execute_time TIMESTAMP NULL,
response_code INT NULL,
response_message TEXT,
response_time TIMESTAMP NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (device_id) REFERENCES devices(device_id)
)
`);
// 新增密码表（强绑定设备，device_id为必填且唯一约束）
await pool.execute(`
CREATE TABLE IF NOT EXISTS passwords (
id VARCHAR(36) PRIMARY KEY, -- 密码唯一ID
user_id VARCHAR(36) NOT NULL, -- 用户ID（关联users表）
device_id VARCHAR(50) NOT NULL, -- 设备ID（关联devices表，必填）
password_name VARCHAR(50) NOT NULL, -- 密码名称（同一设备下唯一）
password VARCHAR(100) NOT NULL, -- 密码内容（建议加密存储）
password_type VARCHAR(20) NOT NULL, -- 密码类型（如admin/temporary）
valid_start TIMESTAMP NULL, -- 生效开始时间
valid_end TIMESTAMP NULL, -- 生效结束时间
max_uses INT DEFAULT 0, -- 最大使用次数（0=无限次）
used_count INT DEFAULT 0, -- 已使用次数
is_active TINYINT DEFAULT 1, -- 是否激活（1=激活，0=停用）
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 唯一约束：同一设备下密码名称唯一
UNIQUE KEY unique_device_password (device_id, password_name),
-- 外键约束
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (device_id) REFERENCES devices(device_id) ON DELETE CASCADE
)
`);
console.log('数据库初始化完成');
} catch (error) {
console.error('数据库初始化失败:', error);
}
}
// 生成唯一ID
function generateId() {
return Date.now().toString(36) + Math.random().toString(36).substr(2);
}
// 处理用户注册
async function handleRegistration(message, ws) {
try {
const { username, password, phone, email } = JSON.parse(message);
// 验证参数
if (!username || !password || !phone || !email) {
ws.send(JSON.stringify({
messageType: 'registrationResult',
registrationResult: 1002,
failureReason: '缺少必要参数'
}));
return;
}
const userId = generateId();
// 插入用户数据
await pool.execute(
'INSERT INTO users (id, username, password, phone, email) VALUES (?, ?, ?, ?, ?)',
[userId, username, password, phone, email]
);
ws.send(JSON.stringify({
messageType: 'registrationResult',
registrationResult: 0,
failureReason: ''
}));
} catch (error) {
console.error('注册处理错误:', error);
if (error.code === 'ER_DUP_ENTRY') {
ws.send(JSON.stringify({
messageType: 'registrationResult',
registrationResult: 1001,
failureReason: '用户名已存在'
}));
} else {
ws.send(JSON.stringify({
messageType: 'registrationResult',
registrationResult: 1006,
failureReason: '服务器内部错误'
}));
}
}
}
// 处理用户登录
async function handleLogin(message, ws) {
try {
const { username, password } = JSON.parse(message);
// 验证参数
if (!username || !password) {
ws.send(JSON.stringify({
messageType:'loginResult',
loginResult: 1002,
userId: null
}));
return;
}
// 查询用户
const [rows] = await pool.execute(
'SELECT id FROM users WHERE username = ? AND password = ?',
[username, password]
);
if (rows.length === 0) {
ws.send(JSON.stringify({
messageType:'loginResult',
loginResult: 1004,
userId: null
}));
} else {
ws.send(JSON.stringify({
messageType:'loginResult',
loginResult: 0,
userId: rows[0].id
}));
}
} catch (error) {
console.error('登录处理错误:', error);
ws.send(JSON.stringify({
messageType:'loginResult',
loginResult: 1006,
userId: null
}));
}
}
// 处理设备信息上报
async function handleDeviceReport(message, ws) {
try {
const { userId, deviceId, deviceType, deviceModel, deviceSerial, deviceStatus, deviceAttributes } = JSON.parse(message);
// 验证参数
if (!userId || !deviceId || !deviceType || !deviceModel || !deviceSerial) {
ws.send(JSON.stringify({
messageType: 'deviceReportResponse',
reportResult: 1002,
failureReason: '缺少必要参数'
}));
return;
}
const deviceAttributesStr = JSON.stringify(deviceAttributes || {});
const deviceRecordId = generateId();
// 插入或更新设备信息
await pool.execute(
`INSERT INTO devices (id, user_id, device_id, device_type, device_model, device_serial, device_status, device_attributes)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
device_type = VALUES(device_type),
device_model = VALUES(device_model),
device_serial = VALUES(device_serial),
device_status = VALUES(device_status),
device_attributes = VALUES(device_attributes)`,
[
deviceRecordId,
userId,
deviceId,
deviceType,
deviceModel,
deviceSerial,
deviceStatus,
deviceAttributesStr
]
);
ws.send(JSON.stringify({
messageType: 'deviceReportResponse',
reportResult: 0,
failureReason: ''
}));
} catch (error) {
console.error('设备上报处理错误:', error);
ws.send(JSON.stringify({
messageType: 'deviceReportResponse',
reportResult: 1006,
failureReason: '服务器内部错误'
}));
}
}
// 处理设备控制命令响应
async function handleControlResponse(message, ws) {
try {
const { commandId, deviceId, responseCode, responseMessage } = JSON.parse(message);
// 验证参数
if (!commandId || !deviceId || responseCode === undefined) {
ws.send(JSON.stringify({
messageType:'responseResult',
responseResult: 1002,
failureReason: '缺少必要参数'
}));
return;
}
// 更新命令状态
const [result] = await pool.execute(
`UPDATE device_commands
SET response_code = ?,
response_message = ?,
response_time = NOW()
WHERE id = ? AND device_id = ?`,
[responseCode, responseMessage, commandId, deviceId]
);
if (result.affectedRows === 0) {
ws.send(JSON.stringify({
messageType:'responseResult',
responseResult: 1003,
failureReason: '命令不存在或已处理'
}));
} else {
ws.send(JSON.stringify({
messageType:'responseResult',
responseResult: 0,
failureReason: ''
}));
}
} catch (error) {
console.error('控制响应处理错误:', error);
ws.send(JSON.stringify({
messageType:'responseResult',
responseResult: 1006,
failureReason: '服务器内部错误'
}));
}
}
// 处理获取设备列表请求
async function handleDeviceListRequest(message, ws) {
try {
const { userId } = JSON.parse(message);
// 验证参数
if (!userId) {
ws.send(JSON.stringify({
messageType:'deviceListResponse',
requestResult: 1002,
devices: []
}));
return;
}
// 查询用户设备
const [rows] = await pool.execute(
'SELECT * FROM devices WHERE user_id = ?',
[userId]
);
// 解析设备属性
const devices = rows.map(device => {
try {
device.deviceAttributes = JSON.parse(device.device_attributes);
} catch (e) {
device.deviceAttributes = {};
}
delete device.device_attributes;
return device;
});
ws.send(JSON.stringify({
messageType:'deviceListResponse',
requestResult: 0,
devices: devices
}));
} catch (error) {
console.error('获取设备列表处理错误:', error);
ws.send(JSON.stringify({
messageType:'deviceListResponse',
requestResult: 1006,
devices: []
}));
}
}
// 发送设备控制命令（新增功能）
async function sendDeviceCommand(userId, deviceId, commandType, commandParams) {
try {
const commandId = generateId();
// 存储命令到数据库
await pool.execute(
`INSERT INTO device_commands (id, user_id, device_id, command_type, command_params, create_time)
VALUES (?, ?, ?, ?, ?, NOW())`,
[commandId, userId, deviceId, commandType, JSON.stringify(commandParams)]
);
// 这里应该找到对应的WebSocket连接并发送命令
// 简化示例，实际实现需要维护用户ID到WebSocket的映射
return { success: true, commandId };
} catch (error) {
console.error('发送设备控制命令错误:', error);
return { success: false, error };
}
}
// 处理心跳消息
async function handleHeartbeat(message, ws) {
try {
const { timestamp } = JSON.parse(message);
// 验证参数
if (!timestamp) {
ws.send(JSON.stringify({
messageType: 'errorCode',
errorCode: 1002,
errorMessage: '心跳消息缺少时间戳'
}));
return;
}
// 可以记录心跳时间，用于检测设备在线状态
console.log(`收到心跳消息，时间戳: ${timestamp}`);
// 发送心跳响应
ws.send(JSON.stringify({
messageType: 'heartbeatResponse',
timestamp: Date.now()
}));
} catch (error) {
console.error('心跳处理错误:', error);
ws.send(JSON.stringify({
messageType: 'errorCode',
errorCode: 1006,
errorMessage: '服务器内部错误'
}));
}
}
// 服务器端新增：检查设备是否存在
async function handleCheckDeviceExists(message, ws) {
try {
const { userId, deviceId } = JSON.parse(message);
// 查询设备是否存在
const [rows] = await pool.execute(
'SELECT id FROM devices WHERE user_id = ? AND device_id = ?',
[userId, deviceId]
);
ws.send(JSON.stringify({
messageType: 'checkDeviceExistsResponse',
exists: rows.length > 0
}));
} catch (error) {
console.error('检查设备存在错误:', error);
ws.send(JSON.stringify({
messageType: 'checkDeviceExistsResponse',
exists: false,
error: '服务器内部错误'
}));
}
}
// 处理设备删除请求
async function handleDeviceDelete(message, ws) {
try {
const { userId, deviceId } = JSON.parse(message);
// 验证参数
if (!userId || !deviceId) {
ws.send(JSON.stringify({
messageType: 'deviceDeleteResponse',
deleteResult: 1002,
failureReason: '缺少必要参数'
}));
return;
}
// 开启事务（确保设备和相关命令级联删除）
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
// 1. 删除设备表记录
const [deviceResult] = await connection.execute(
'DELETE FROM devices WHERE user_id = ? AND device_id = ?',
[userId, deviceId]
);
// 2. 删除设备相关的命令记录
const [commandResult] = await connection.execute(
'DELETE FROM device_commands WHERE device_id = ?',
[deviceId]
);
if (deviceResult.affectedRows === 0) {
await connection.rollback();
ws.send(JSON.stringify({
messageType: 'deviceDeleteResponse',
deleteResult: 1003,
failureReason: '设备不存在'
}));
return;
}
await connection.commit();
ws.send(JSON.stringify({
messageType: 'deviceDeleteResponse',
deleteResult: 0,
failureReason: ''
}));
} catch (error) {
await connection.rollback();
console.error('设备删除事务失败:', error);
ws.send(JSON.stringify({
messageType: 'deviceDeleteResponse',
deleteResult: 1006,
failureReason: '服务器内部错误'
}));
} finally {
connection.release();
}
} catch (error) {
console.error('设备删除处理错误:', error);
ws.send(JSON.stringify({
messageType: 'deviceDeleteResponse',
deleteResult: 1006,
failureReason: '服务器内部错误'
}));
}
}
async function handleAddDevicePassword(message, ws) {
try {
const {
userId,
deviceId,
passwordName,
password,
passwordType,
validStart,
validEnd,
maxUses
} = JSON.parse(message);
// 验证参数
if (!userId || !deviceId || !passwordName || !password || !passwordType) {
ws.send(JSON.stringify({
messageType: 'addDevicePasswordResult',
resultCode: 1002,
failureReason: '缺少必要参数'
}));
return;
}
// 检查设备是否存在且属于用户
const [deviceRows] = await pool.execute(
'SELECT id FROM devices WHERE user_id = ? AND device_id = ?',
[userId, deviceId]
);
if (deviceRows.length === 0) {
ws.send(JSON.stringify({
messageType: 'addDevicePasswordResult',
resultCode: 1003,
failureReason: '设备不存在或无权限'
}));
return;
}
// 插入密码
const passwordId = generateId();
await pool.execute(
`INSERT INTO passwords (id, user_id, device_id, password_name, password, password_type, valid_start, valid_end, max_uses)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
passwordId,
userId,
deviceId,
passwordName,
password,
passwordType,
validStart || null,
validEnd || null,
maxUses || 0
]
);
ws.send(JSON.stringify({
messageType: 'addDevicePasswordResult',
resultCode: 0,
passwordId: passwordId
}));
} catch (error) {
console.error('添加设备密码错误:', error);
if (error.code === 'ER_DUP_ENTRY') {
ws.send(JSON.stringify({
messageType: 'addDevicePasswordResult',
resultCode: 1001,
failureReason: '该设备下已存在同名密码'
}));
} else {
ws.send(JSON.stringify({
messageType: 'addDevicePasswordResult',
resultCode: 1006,
failureReason: '服务器内部错误'
}));
}
}
}
async function handleGetDevicePasswords(message, ws) {
try {
const { userId, deviceId } = JSON.parse(message);
if (!userId || !deviceId) {
ws.send(JSON.stringify({
messageType: 'devicePasswordsResponse',
resultCode: 1002,
passwords: []
}));
return;
}
// 查询设备密码（包含有效性过滤）
const [rows] = await pool.execute(
`SELECT * FROM passwords
WHERE user_id = ?
AND device_id = ?
AND is_active = 1
AND (valid_start IS NULL OR valid_start <= NOW())
AND (valid_end IS NULL OR valid_end >= NOW())
AND (max_uses = 0 OR used_count < max_uses)
ORDER BY valid_start DESC`,
[userId, deviceId]
);
ws.send(JSON.stringify({
messageType: 'devicePasswordsResponse',
resultCode: 0,
passwords: rows
}));
} catch (error) {
console.error('获取设备密码列表错误:', error);
ws.send(JSON.stringify({
messageType: 'devicePasswordsResponse',
resultCode: 1006,
passwords: []
}));
}
}
async function handleValidateDevicePassword(message, ws) {
try {
const { deviceId, password } = JSON.parse(message);
if (!deviceId || !password) {
ws.send(JSON.stringify({
messageType: 'validatePasswordResult',
isValid: false,
reason: '缺少必要参数'
}));
return;
}
// 查询匹配的有效密码
const [rows] = await pool.execute(
`SELECT * FROM passwords
WHERE device_id = ?
AND password = ?
AND is_active = 1
AND (valid_start IS NULL OR valid_start <= NOW())
AND (valid_end IS NULL OR valid_end >= NOW())
AND (max_uses = 0 OR used_count < max_uses)`,
[deviceId, password]
);
if (rows.length === 0) {
ws.send(JSON.stringify({
messageType: 'validatePasswordResult',
isValid: false,
reason: '密码无效或已过期'
}));
} else {
// 更新使用次数
const passwordId = rows[0].id;
await pool.execute(
'UPDATE passwords SET used_count = used_count + 1 WHERE id = ?',
[passwordId]
);
ws.send(JSON.stringify({
messageType: 'validatePasswordResult',
isValid: true,
passwordInfo: rows[0]
}));
}
} catch (error) {
console.error('验证设备密码错误:', error);
ws.send(JSON.stringify({
messageType: 'validatePasswordResult',
isValid: false,
reason: '服务器内部错误'
}));
}
}
async function handleUpdatePasswordStatus(message, ws) {
try {
const { userId, passwordId, isActive } = JSON.parse(message);
if (!userId || !passwordId || isActive === undefined) {
ws.send(JSON.stringify({
messageType: 'updatePasswordStatusResult',
resultCode: 1002,
failureReason: '缺少必要参数'
}));
return;
}
// 检查密码是否存在且属于用户
const [rows] = await pool.execute(
'SELECT id FROM passwords WHERE user_id = ? AND id = ?',
[userId, passwordId]
);
if (rows.length === 0) {
ws.send(JSON.stringify({
messageType: 'updatePasswordStatusResult',
resultCode: 1003,
failureReason: '密码不存在或无权限'
}));
return;
}
// 更新状态
await pool.execute(
'UPDATE passwords SET is_active = ? WHERE id = ?',
[isActive ? 1 : 0, passwordId]
);
ws.send(JSON.stringify({
messageType: 'updatePasswordStatusResult',
resultCode: 0
}));
} catch (error) {
console.error('更新密码状态错误:', error);
ws.send(JSON.stringify({
messageType: 'updatePasswordStatusResult',
resultCode: 1006,
failureReason: '服务器内部错误'
}));
}
}
const serverOptions = {
cert: fs.readFileSync('C:/Users/Administrator/Desktop/soft/heyuer.top.pem'),
key: fs.readFileSync('C:/Users/Administrator/Desktop/soft/heyuer.top.key')
};
// 创建HTTPS服务器
const httpsServer = https.createServer(serverOptions);
// 将HTTPS服务器绑定到WebSocket Server
const wss = new WebSocket.Server({ server: httpsServer });
// 维护用户ID到WebSocket连接的映射
const userConnections = new Map();
wss.on('connection', (ws) => {
console.log('新的WebSocket客户端已连接.');
// 监听来自客户端的消息事件
ws.on('message', async (message) => {
console.log(`接收到消息: ${message}`);
try {
const msg = JSON.parse(message);
// 根据消息类型分发处理
switch (msg.messageType) {
case 'registration':
await handleRegistration(message, ws);
break;
case 'login':
await handleLogin(message, ws);
break;
case 'deviceDeleteRequest':
await handleDeviceDelete(message, ws);
break;
case 'deviceReport':
await handleDeviceReport(message, ws);
break;
case 'controlResponse':
await handleControlResponse(message, ws);
break;
case 'checkDeviceExists':
await handleCheckDeviceExists(message, ws);
break;
case 'deviceListRequest':
await handleDeviceListRequest(message, ws);
break;
case 'authenticate':
// 处理用户认证，建立用户ID到连接的映射
const { userId } = msg;
userConnections.set(userId, ws);
console.log(`用户 ${userId} 已认证`);
break;
case 'heartbeat': // 新增心跳处理分支
await handleHeartbeat(message, ws);
break;
case 'addDevicePassword': // 添加设备密码
await handleAddDevicePassword(message, ws);
break;
case 'getDevicePasswords': // 获取设备密码列表
await handleGetDevicePasswords(message, ws);
break;
case 'validateDevicePassword': // 验证设备密码
await handleValidateDevicePassword(message, ws);
break;
case 'updatePasswordStatus': // 更新密码状态
await handleUpdatePasswordStatus(message, ws);
break;
default:
ws.send(JSON.stringify({
messageType: 'errorCode',
errorCode: 1007,
errorMessage: '未知消息类型'
}));
}
} catch (error) {
console.error('消息解析错误:', error);
ws.send(JSON.stringify({
messageType:'errorCode',
errorCode: 1005,
errorMessage: '消息格式错误'
}));
}
});
// 监听关闭事件
ws.on('close', () => {
console.log('WebSocket客户端已断开.');
// 从映射中移除连接
for (const [userId, conn] of userConnections.entries()) {
if (conn === ws) {
userConnections.delete(userId);
break;
}
}
});
});
// 启动服务器
async function startServer() {
await testDbConnection();
await initDatabase();
httpsServer.listen(443, () => {
console.log('HTTPS/WebSocket服务器正在运行于 https://localhost:443/');
});
} 数据库连接成功
数据库初始化失败: Error: Failed to add the foreign key constraint. Missing index for constraint 'device_commands_ibfk_2' in the referenced table 'devices'
at PromisePool.execute (c:\Users\Administrator\Desktop\node_modules\mysql2\lib\promise\pool.js:54:22)
at initDatabase (c:\Users\Administrator\Desktop\Server\server.js:66:16)
at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
at async startServer (c:\Users\Administrator\Desktop\Server\server.js:837:3) {
code: 'ER_FK_NO_INDEX_PARENT',
errno: 1822,
sql: '\n' +
' CREATE TABLE IF NOT EXISTS device_commands (\n' +
' id VARCHAR(36) PRIMARY KEY,\n' +
' user_id VARCHAR(36) NOT NULL,\n' +
' device_id VARCHAR(50) NOT NULL,\n' +
' command_type VARCHAR(50) NOT NULL,\n' +
' command_params TEXT,\n' +
' create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n' +
' execute_time TIMESTAMP NULL,\n' +
' response_code INT NULL,\n' +
' response_message TEXT,\n' +
' response_time TIMESTAMP NULL,\n' +
' FOREIGN KEY (user_id) REFERENCES users(id),\n' +
' FOREIGN KEY (device_id) REFERENCES devices(device_id)\n' +
' )\n' +
' ',
sqlState: 'HY000',
sqlMessage: "Failed to add the foreign key constraint. Missing index for constraint 'device_commands_ibfk_2' in the referenced table 'devices'"
}
HTTPS/WebSocket服务器正在运行于 https://localhost:443/
本文介绍了解决.NET项目中因Newsoft.Json不同版本引起的DLL引用冲突问题的方法，通过修改web.config文件中的assemblyBinding设置来强制使用特定版本。
扫一扫
2194
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
