sequelize.js - sequelize:除一张表外未生成索引
问题描述
在我的 nodejs 应用程序中,这是生成 db 的方式sequelizejs (4.42.0)
:
db.sync({force: true});
数据库中有 5 个表,表生成正常,但索引不正常。一张表只生成event
索引,其他4张表根本没有索引。这是cmd输出:
Executing (default): DROP TABLE IF EXISTS "events" CASCADE;
Executing (default): DROP TABLE IF EXISTS "socketlists" CASCADE;
Executing (default): DROP TABLE IF EXISTS "veriflogs" CASCADE;
Executing (default): DROP TABLE IF EXISTS "users" CASCADE;
Executing (default): DROP TABLE IF EXISTS "users" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER , "name" VARCHAR(255) NOT NULL, "email" VARCHAR(255), "cell" VARCHAR(255) NOT NULL, "cell_country_code" VARCHAR(255) NOT NULL, "comp_name" VARCHAR(255), "status" VARCHAR(255) NOT NULL, "role" VARCHAR(255) NOT NULL, "device_id" VARCHAR(255), "last_updated_by_id" INTEGER, "fort_token" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE, "updatedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "veriflogs" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "veriflogs" ("id" INTEGER , "cell" VARCHAR(255), "cell_country_code" VARCHAR(255), "vcode" VARCHAR(255), "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'veriflogs' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "socketlists" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "socketlists" ("id" INTEGER , "user_id" INTEGER, "socket_id" VARCHAR(255), "event_id" INTEGER, "server_id" VARCHAR(255), "active" BOOLEAN DEFAULT true, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'socketlists' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "events" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "events" ("id" INTEGER , "name" VARCHAR(255) NOT NULL, "created_by_id" INTEGER NOT NULL, "event_info" JSONB, "current_step" VARCHAR(255), "access_list" INTEGER[], "event_step" VARCHAR(255)[], "event_snapshot" JSON, "event_category_id" INTEGER, "status" VARCHAR(255), "last_updated_by_id" INTEGER, "fort_token" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE, "updatedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'events' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE INDEX "events_name" ON "events" ("name")
Executing (default): CREATE INDEX "events_status" ON "events" ("status")
Executing (default): CREATE INDEX "events_fort_token" ON "events" ("fort_token")
Executing (default): CREATE INDEX "events_event_category_id" ON "events" ("event_category_id")
Executing (default): CREATE INDEX "events_created_by_id" ON "events" ("created_by_id")
Executing (default): DROP TABLE IF EXISTS "messages" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "messages" ("id" INTEGER , "event_id" INTEGER, "sender_id" INTEGER, "data" JSONB NOT NULL, "fort_token" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'messages' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
什么可能导致其他 4 个表的索引丢失?以下是 和 的event
定义user
:
const Event = db.define('event', {
id: {type: Sql.INTEGER,
primaryKey:true,
min: 1},
name: {type: Sql.STRING,
min:2,
allowNull: false,
},
created_by_id: {type: Sql.INTEGER,
allowNull: false
},
event_info: {type: Sql.JSONB},
current_step: {type: Sql.STRING},
access_list: {type: Sql.ARRAY(Sql.INTEGER),
},
event_step: {type: Sql.ARRAY(Sql.STRING)},
event_snapshot: {type: Sql.JSON},
event_category_id: {type: Sql.INTEGER,
},
status: {type: Sql.STRING,
isIn: ['active', 'cancelled', 'aborted', 'suspended', 'completed']},
last_updated_by_id: {type: Sql.INTEGER},
fort_token: {type: Sql.STRING,
allowNull: false,
},
createdAt: Sql.DATE,
updatedAt: Sql.DATE
}, {
indexes: [ {
fields: ['name']
},
{
fields: ['status']
},
{
fields: ['fort_token']
},{
fields: ['event_category_id']
}, {
fields: ['created_by_id']
}
]
});
const User = db.define('user', {
id: {type: Sql.INTEGER,
primaryKey:true,
min: 1},
name: {type: Sql.STRING,
allowNull: false,
min: 2,
max: 50,
},
email: {type: Sql.STRING,
isEmail: true
},
cell: {type: Sql.STRING,
allowNull: false,
min: 10,
max: 20,
},
cell_country_code: {type: Sql.STRING,
allowNull: false,
},
comp_name: {type: Sql.STRING
},
status: {type: Sql.STRING,
allowNull: false,
isIn: ['active', 'blocked', 'inactive', 'pending']
},
role: {type: Sql.STRING,
allowNull: false
},
device_id: {type: Sql.STRING, //maybe empty when the user is initially created.
},
last_updated_by_id: {type: Sql.INTEGER},
fort_token: {type: Sql.STRING,
allowNull: false,
min: 20 //64 for production
},
createdAt: Sql.DATE,
updatedAt: Sql.DATE
}, {
validate: {async custom_validate() {
let user = await User.findOne({where:{name :this.name, fort_token: this.fort_token}});
if (user) throw new Error("用户重名!");
let cell = await User.findOne({where: {cell: this.cell, cell_country_code: this.cell_country_code, status: 'active'}});
if (cell) throw new Error("手机号已经注册使用!");
}}
}, {
indexes: [
{
//For same fort_token, name to be unique
unique: true,
fields: ['name', 'fort_token']
}, {
//unique cell
unique: true,
fields: ['cell_country_code', 'cell', 'status']
}, {
fields: ['cell_country_code', 'cell']
}, {
//email
fields: ['email']
}, {
fields: ['device_id']
}, {
fields: ['status']
}, {
fields: ['fort_token']
}
]
});
解决方案
将 index 属性放入 options 对象中,就像在 Event 模型中所做的一样。
define
只有三个参数define(modelName, attributes, options)
,但在用户模型中,您添加了第四个参数,如果不处理,额外的参数在 JavaScript 中会被忽略。只需将 移动到indexes
与 相同的对象中validate
,即:
const User = db.define('user', {
...
}, {
validate: {...}
indexes: [...]
});
推荐阅读
- rasa - 在 rasa-x 上使用 MITIE 部署 rasa 模型,但出现错误
- android - Android:FingerprintManager onAuthenticationSucceeded 之后的下一步是什么?
- python-3.x - 如何在 pymongo 中为“IN”子句查询包含 NULL 或 None 值?
- python - 如何使用 NUMBA 加速我的 python 代码?
- java - 指定主类的路径并使用 sbt 文件包含库
- ruby-on-rails - 计算特定用户通过 Active Storage 下载文件的次数
- reactjs - 从反应导航中获取 mapStateToProps 路由参数
- javascript - 导航时在菜单项上加下划线
- regex - 匹配 if - else if - else 阶梯
- vue.js - 如何将 Vuex 命名空间的 getter 与 Composition API 一起使用