EvaluationSystem：数据库模型建立

1、用户table（./models/user.js）

用户字段：

• useraccount：账号（主键）
• nickname：昵称
• password：密码
• evalnum：已参与测评数量

点击查看代码

// 用户
const {Model} = require('sequelize');
const security = require('../shared/security');

module.exports = (sequelize, DataTypes) => {
    class User extends Model {
        // 检测密码
        checkPassword(rawPassword) {
            return security.sha256(rawPassword) === this.password;
        }
    }

    User.init({
        useraccount: {
            type: DataTypes.STRING(20),
            allowNull: false,
            primaryKey: true,
            validate: {
                notEmpty: {
                    msg: '账号不能为空'
                },
                len: {
                    msg: '账号长度为6-20位',
                    args: [6, 20]
                },
                isAlphanumeric: {
                    msg: '账号只能包含字母和数字'
                }
            },
            comment: '账号'
        },
        password: {type: DataTypes.CHAR(64), allowNull: false, validate: {
            notEmpty: {
                msg: '密码不能为空'
            },
            len: {
                msg: '密码长度为6-20位',
                args: [6, 20]
            },
            isAlphanumeric: {
                msg: '只能包含字母和数字'
            }
            },
            comment: '密码'},
        nickname: {type: DataTypes.STRING(20), allowNull: false, defaultValue: '', comment: '昵称'},
        evalnum: {type: DataTypes.INTEGER, allowNull: true, defaultValue: 0, comment: '已参与测评数量'}
    }, {
        sequelize: sequelize,
        tableName: 'user',
        underscored: true,
        paranoid: true, //软删除
        timestamps: false,
        indexes: [  //索引
            {
                unique: true,   // 唯一索引
                fields: ['useraccount'],    // 索引字段
            }
        ]
    });

    User.beforeSave((user) => {
        // 密码处理
        if (user.changed('password') && user.password.length > 0) {
            user.password = security.sha256(user.password);
        }
    });

    return User;
};

2、数据table（./models/data.js）

数据字段：

• dataname：数据名字（主键）
• dbname：数据库名称
• datapath：数据路径
• evalnum：被测评数量

点击查看代码

// 测评数据
const {Model} = require('sequelize');

module.exports = (sequelize, DataTypes) => {

    class data extends Model {
    }
    data.init({
        dataname: { type: DataTypes.STRING(140), allowNull: false, primaryKey: true, comment: '文件名'},
        dbname: { type: DataTypes.STRING(140), allowNull: false, comment: '来源数据库'},
        datapath: { type: DataTypes.STRING(140), allowNull: false, comment: '路径'},
        evalnum: { type: DataTypes.INTEGER, defaultValue: 0, allowNull: true, comment: '被测评数'},
    }, {
        sequelize: sequelize,
        tableName: 'data',
        underscored: true,
        paranoid: false,    // 取消软删除，数据删除后相应的测评数据也会删除
        timestamps: false,
    });
    return data;
}

3、测评table（./models/ceping.js）

测评表字段：

• id：自动生成测评id
• 若干测评项目（如index1，index2，index3）
• comment：评论
• dataname：数据名字（外键）
• useraccount：用户账号（外键）

点击查看代码

// 测评表
const {Model} = require('sequelize');
const allocation = require('../alloction');

module.exports = (sequelize, DataTypes) => {

    const data = sequelize.import('./data');
    const User = sequelize.import('./user');
    const evalOpt = allocation.evalOpt;
    const obj = {};
    for(key in evalOpt){
        obj[evalOpt[key]] = {type: DataTypes.INTEGER, allowNull:false, comment: '指标'};
    }

    obj['comment'] = {type: DataTypes.STRING(140), allowNull: true, comment: '评论内容'}

    class ceping extends Model {
    }

    //  模型定义
    ceping.init(obj, {
        sequelize: sequelize,
        tableName: 'ceping',
        //timestamps: false,
        underscored: true,
        paranoid: true,
    });

    //关联定义
    ceping.belongsTo(data, {  // 测评属于数据
        constraints: false,
        foreignKey: 'dataname'
    })
    ceping.belongsTo(User, {   // 测评属于
        constraints: false,
        foreignKey: 'useraccount',
    })

    return ceping;
};

4、数据库建立（./db.js）

点击查看代码

const path = require('path');
const fs = require('fs');
const sequelize = require('./shared/sequelize');
sequelize.import('./models/data');
sequelize.import('./models/user');
sequelize.import('./models/ceping');
const dataService = require('./services/data');
const allocation = require('./alloction');

// 数据库建立 
async function CreateDatabase() {
    return await sequelize.sync({force: true}).catch((err) => console.error(err)).finally();//() => sequelize.close());
}

// 待测评数据录入
const dbpath = allocation.dbpath;
const dbname = allocation.dbname;
async function PushData() {
    // 获取参数
    const isFile = filename => {
        return fs.lstatSync(filename).isFile();
    }

    dataname = fs.readdirSync(dbpath);
    datapath = dataname.map( filename => {
        return path.join(dbpath,filename);
    }).filter(isFile);

    obj = [];
    for (key in datapath) {
        obj[key] = {'dataname': dataname[key],
                    'dbname': dbname,
                    'datapath': datapath[key],
                    'evalnum': 0
                }
    }
    console.log(obj);
    // 导入数据
    dataService.publish(obj);
}


// 主函数
async function main() {
    // 创建数据库
    await CreateDatabase();
    // 导入待测评数据
    PushData();
}

main();