Discordjs冷却数据库与序列化唯一id错误

Question

我用sequalize和sqlite3为我的discord bot制作了一个冷却时间数据库。这是为某些命令添加单独的冷却时间，但我得到一个错误，说"id必须是唯一的";即使我将unique: true关闭为unique: false，或者甚至为另一个命令创建一个新的数据库存储。下面是完整的错误:

node:internal/process/promises:246
triggerUncaughtException(err, true /* fromPromise */);
^
Error
at Database.<anonymous> (C:UsersbradeDesktopBear battlesnode_modulessequelizelibdialectssqlitequery.js:179:27)
at C:UsersbradeDesktopBear battlesnode_modulessequelizelibdialectssqlitequery.js:177:50
at new Promise (<anonymous>)
at Query.run (C:UsersbradeDesktopBear battlesnode_modulessequelizelibdialectssqlitequery.js:177:12)
at C:UsersbradeDesktopBear battlesnode_modulessequelizelibsequelize.js:311:28
at async SQLiteQueryInterface.insert (C:UsersbradeDesktopBear battlesnode_modulessequelizelibdialectsabstractquery-interface.js:308:21)
at async model.save (C:UsersbradeDesktopBear battlesnode_modulessequelizelibmodel.js:2432:35)
at async Function.create (C:UsersbradeDesktopBear battlesnode_modulessequelizelibmodel.js:1344:12) {
name: 'SequelizeUniqueConstraintError',
errors: [
ValidationErrorItem {
message: 'id must be unique',
type: 'unique violation',
path: 'id',
value: '250412979835764738',
origin: 'DB',
instance: cooldown {
dataValues: {
id: '250412979835764738',
expiry: 1655869677206,
command: 'hunt',
updatedAt: 2022-06-22T03:42:57.207Z,
createdAt: 2022-06-22T03:42:57.207Z
},
_previousDataValues: { id: undefined, expiry: undefined, command: undefined },
uniqno: 1,
_changed: Set(3) { 'id', 'expiry', 'command' },
_options: {
isNewRecord: true,
_schema: null,
_schemaDelimiter: '',
attributes: undefined,
include: undefined,
raw: undefined,
silent: undefined
},
isNewRecord: true
},
validatorKey: 'not_unique',
validatorName: null,
validatorArgs: []
}
],
parent: [Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: cooldown.id] {
errno: 19,
code: 'SQLITE_CONSTRAINT',
sql: 'INSERT INTO `cooldown` (`id`,`expiry`,`command`,`createdAt`,`updatedAt`) VALUES ($1,$2,$3,$4,$5);'
},
original: [Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: cooldown.id] {
errno: 19,
code: 'SQLITE_CONSTRAINT',
sql: 'INSERT INTO `cooldown` (`id`,`expiry`,`command`,`createdAt`,`updatedAt`) VALUES ($1,$2,$3,$4,$5);'
},
fields: [ 'id' ],
sql: 'INSERT INTO `cooldown` (`id`,`expiry`,`command`,`createdAt`,`updatedAt`) VALUES ($1,$2,$3,$4,$5);'
}

这是我的日常命令的代码。

const { SlashCommandBuilder } = require('@discordjs/builders')
const ms = require('ms')
const { defaultColor } = require('../../command-imports')
module.exports = {
data: new SlashCommandBuilder()
.setName('daily')
.setDescription('Claim your daily reward.'),
async execute (interaction, Cooldowns) {
let getCooldown = await Cooldowns.findOne({where: {id: interaction.user.id}}) // Finds if user has cooldown
let cooldownTime = getCooldown?.expiry
if(getCooldown && cooldownTime > new Date().getTime()) { // If cooldown is active run this
return interaction.reply({content: `You are still under cooldown! Please wait **${ms(cooldownTime - new Date().getTime(), {long: true})}**`})
} else if (getCooldown) { // If cooldown is expired remove from db and run rest of code
Cooldowns.destroy({where: {id: interaction.user.id, command: 'daily'}})
}
const claimedDaily = {
color: defaultColor,
description: "You have recieved N/A from your daily reward"
}
interaction.reply({embeds: [claimedDaily]})
Cooldowns.create({ // Creates 5 minute cooldown for hunt command
id: interaction.user.id,
expiry: new Date().getTime() + (60000 * 5),
command: 'daily'
})
}
}

这是我的搜索命令。

const { SlashCommandBuilder } = require('@discordjs/builders')
const { errorColor, defaultColor } = require('../../command-imports')
const ms = require('ms')
module.exports = {
data: new SlashCommandBuilder()
.setName('hunt')
.setDescription('Hunt for a chance at finding a bear.'),
async execute(interaction, Cooldowns, Economy) {
let getCooldown = await Cooldowns.findOne({where: {id: interaction.user.id, command: 'hunt'}}) // Finds if user has cooldown
let getUser = await Economy.findOne({where: {id: interaction.user.id}})
if(!getUser) {
getUser = await Economy.create({id: interaction.user.id, coins: 0})
}
let cooldownTime = getCooldown?.expiry
if(getCooldown && cooldownTime > new Date().getTime()) { // If cooldown is active run this
return interaction.reply({content: `You are still under cooldown! Please wait **${ms(cooldownTime - new Date().getTime(), {long: true})}**`})
} else if (getCooldown) { // If cooldown is expired remove from db and run rest of code
Cooldowns.destroy({where: {id: interaction.user.id, command: 'hunt'}})
}

let whichExecute = Math.floor(Math.random() * 8) + 1 // 7/8 Chance for coins 1/8 Chance for bears
if(whichExecute <= 7) { 
let coinsFound = Math.floor(Math.random() * 10) + 1 // Picks random coin amount between 1 - 10
const nothingFound = {
color: errorColor,
description: `No bear was found however you found ${coinsFound} :coin: n You have ${getUser.coins} :coin:` // Displays coins earned and total coins
}
interaction.reply({embeds: [nothingFound]}) 
await Economy.update({coins: getUser.coins + coinsFound}, {where: {id: interaction.user.id}}) // Updates user in db
Cooldowns.create({ // Creates 5 minute cooldown for hunt command
id: interaction.user.id,
expiry: new Date().getTime() + (60000 * 5),
command: 'hunt'
})
}
else if(whichExecute === 8) {
const bearFound = { // Displays bear found
color: defaultColor,
description: "You found placeholder_beartype :bear:;;"
}
interaction.reply({embeds: [bearFound]})
Cooldowns.create({ // Creates 5 minute cooldown for hunt command
id: interaction.user.id,
expiry: new Date().getTime() + (60000 * 5),
command: 'hunt'
})
}
}
}

我仍然得到唯一id的错误，即使它应该以相同的id存储，但在不同的命令名称下。这个错误不会出现，直到我运行两个命令(不重要的顺序)，例如我做/hunt，然后做/daily以后。如果您有任何问题或我可以澄清的东西，请告诉我。

Answer 1

注意:此代码是为Discord.js v13.7.0和Sequelize v6编写的

---

不正确地修改表

<Sequelize>.sync()不执行alter或force

Sequelize，根据他们的v6文档，提供了一个叫做sync()的函数。此函数用于确保您的模型与数据库保持同步。然而，这里有一个警告。如果不带参数执行sync()，数据库将不会覆盖现有数据。这就是你的问题的根源。当您第一次定义模型时，您很可能做了以下两件事:

• 定义id为DataTypes.INTEGER
• 设置id为唯一字段

由于这些，并且您执行.sync()时没有参数，数据库的表将不会被覆盖，因此保留了旧的唯一字段。此外，如果您尝试将Discord ID存储为整数，您可能会遇到Discord ID缩短或四舍五入的问题。

<标题>

解决方案删除表作为一次性修复，您可以使用此命令手动从sqlite3中删除表，并重新运行bot，而不修改sync()，这将创建具有正确数据的表:

DROP TABLE 'Cooldowns'

修复过时的表

为了修复过时的表，您有两个选择。但是，要小心，因为这些是破坏性的操作． 您可以使用以下参数执行sync()命令:

<Sequelize>.sync({ alter: true }); // Alters tables
<Sequelize>.sync({ force: true }); // Forcefully recreates tables

如前所述，要小心这些操作，因为它们具有破坏性，如果没有备份则无法恢复。

正确存储Discord id

您需要做的就是将Discord id存储为DataTypes.STRING或DataTypes.TEXT。这将保持雪花形状的ID和防止起酥脆。

sequelize.define("User", {
id: {
type: DataTypes.STRING, // Or .TEXT
unique: false
},
// ...
});