# cms-api2

**Repository Path**: guilixie/cms-api2

## Basic Information

- **Project Name**: cms-api2
- **Description**: fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-12-04
- **Last Updated**: 2022-12-04

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## 1.初始化项目
```js
$ npm i egg-init -g
$ egg-init cms-api --type=simple
$ cd cms-api
$ npm i
$ npm run dev
```

## 2.使用MYSQL
### 2.1 数据库脚本
```sql
CREATE TABLE `entity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '',
  `name` varchar(255) DEFAULT '',
  `data` text DEFAULT '{}',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  `path` varchar(255) DEFAULT '',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
INSERT INTO `menu` VALUES ('3', '实体', '/entity', '2022-06-25 14:35:38', '2022-06-25 14:35:40');
```

### 2.2 在egg使用mysql
### 2.2.1 安装
```js
npm i egg-mysql -S
```

### 2.2.2 config.default.js
config/config.default.js
```js
config.security = {
    csrf:false
}
config.mysql = {// 单数据库信息配置
  client: {
  host: 'localhost', // host
  port: '3306',// 端口号
  user: 'root', // 用户名
  password: 'root', // 密码
  database: 'cms', // 数据库名
}
```
### 2.2.3 plugin.js
config/plugin.js
```js
exports.mysql = {
    enable: true,
    package: 'egg-mysql'
};
```

## 3. 实体管理
- [restful-风格的-url-定义](https://eggjs.org/zh-cn/basics/router.html#restful-%E9%A3%8E%E6%A0%BC%E7%9A%84-url-%E5%AE%9A%E4%B9%89)

### 3.1 router.js
```diff
module.exports = app => {
  const { router, controller } = app;
  router.get('/', controller.home.index);
  router.resources('entity', '/entity', controller.entity);
  router.resources('menu', '/menu', controller.menu);
};
```

### 3.2 entity.js
app/controller/entity.js
```js
const baseController = require('./base');
module.exports = class extends baseController {
    entity = 'entity'
    async create() {
        const { ctx, service } = this;
        const entity = ctx.request.body;
        for (const key in entity) {
            switch (key) {
                case 'fields':
                case 'page':
                case 'record':
                    entity[key] = JSON.stringify(entity[key]);
                    break;
                default:
                    break;
            }
        }
        await service[this.entity].create(entity);
        this.success('创建成功');
    }
}
```

### 3.3 menu.js
app\controller\menu.js
```js
const baseController = require('./base');
module.exports = class extends baseController {
    entity = 'menu'
}
```

### 3.4 base.js
app\controller\base.js
```js
const { Controller } = require('egg');
module.exports = class extends Controller {
    async success(message, data) {
        this.ctx.body = {
            success: true,
            message,
            data
        };
    }
    async index() {
        const { ctx, service } = this;
        const list = await service[this.entity].index(ctx.query);
        this.success('查询成功', list);
    }

    async create() {
        const { ctx, service } = this;
        const entity = ctx.request.body;
        await service[this.entity].create(entity);
        this.success('创建成功');
    }

    async update() {
        const { ctx, service } = this;
        const entity = ctx.request.body;
        entity.id = ctx.params.id;
        await service[this.entity].update(entity);
        this.success('更新成功');
    }
    async destroy() {
        const { ctx, service } = this;
        const id = ctx.params.id;
        let ids = ctx.request.body;
        if (!ids) { ids = [id] }
        await service[this.entity].delete(ids);
        this.success('删除成功');
    }
}

```

### 3.5  entity.js
app\service\entity.js
```js
const BaseService = require('./base');
module.exports = class extends BaseService {
    entity = 'entity'
}
```

### 3.6 menu.js
app\service\menu.js
```js
const BaseService = require('./base');
module.exports = class extends BaseService {
    entity = 'menu'
}
```

### 3.7 app\service\base.js
app\service\base.js
```js
const { Service } = require('egg');
module.exports = class extends Service {
    async index(query) {
        const { app } = this;
        let { current, pageSize, sort, order, created, ...where } = query;
        current = Number(current);
        pageSize = Number(pageSize);
        const offset = (current - 1) * pageSize;
        if (created) {
            const filterFields = Object.entries(where).map(([key, value]) => `${key}='${value}'`);
            const [startTime, endTime] = created.split(',');
            filterFields.push(`created between '${startTime}' and '${endTime}'`);
            let filterSQL = '';
            if (filterFields.length > 0) {
                filterSQL = `where ${filterFields.join(' and ')}`;
            }
            const [{ total }] = await app.mysql.query(`SELECT count(*) as total FROM ${this.entity} ${filterSQL}`);
            if (sort && order) {
                filterSQL += ` order by ${sort} ${order} `;
            }
            if (current && pageSize) {
                filterSQL += ` limit ${offset}, ${pageSize} `;
            }
            const list = await app.mysql.query(`SELECT * FROM ${this.entity} ${filterSQL}`);
            return {
                list,
                total,
                current,
                pageSize
            };
        } else {
            const options = { where };
            if (pageSize) {
                options.offset = offset;
                options.limit = pageSize;
            }
            if (sort && order) {
                options.orders = [[sort, order]];
            }
            const list = await app.mysql.select(this.entity, options);
            const total = await app.mysql.count(this.entity, where);
            return {
                list,
                total,
                current,
                pageSize
            };
        }
    }
    async create(entity) {
        const { app } = this;
        entity.created = app.mysql.literals.now
        entity.updated = app.mysql.literals.now
        return await this.app.mysql.insert(this.entity, entity);
    }
    async update(entity) {
        return await this.app.mysql.update(this.entity, entity);
    }
    async delete(ids) {
        return await this.app.mysql.delete(this.entity, { id: ids });
    }
}
```

## 4. 自动建表
### 4.1 app\service\entity.js
app\service\entity.js
```js
const BaseService = require('./base');
module.exports = class extends BaseService {
    entity = 'entity'
    async create(entity) {
        const { app } = this;
        const fields = entity.fields;
        for (const key in entity) {
            switch (key) {
                case 'fields':
                case 'page':
                case 'record':
                    entity[key] = JSON.stringify(entity[key]);
                    break;
                default:
                    break;
            }
        }
        entity.created = app.mysql.literals.now;
        entity.updated = app.mysql.literals.now;
        await this.app.mysql.insert(this.entity, entity);
        let existTables = await this.app.mysql.select('information_schema.tables', { where: { TABLE_NAME: entity.name } });
        if (existTables.length == 0) {
            const columns = fields.map(field => {
                let dataType = 'varchar(255)';
                switch (field.type) {
                    case 'number':
                        dataType = 'int(11)';
                        break;
                    case 'select':
                        dataType = 'int(11)';
                        break;
                    case 'datetime':
                        dataType = 'datetime';
                        break;
                    case 'switch':
                        dataType = 'tinyint(1)';
                    default:
                        break;
                }
                return `${field.name} ${dataType}`;
            });
            const sql = `CREATE TABLE ${entity.name}(
                id int(11) NOT NULL AUTO_INCREMENT,
                ${columns.join(',')},
                created DATETIME,
                updated DATETIME,
                PRIMARY KEY(id)
            ) ENGINE = InnoDB  DEFAULT CHARSET = utf8mb4;
            `
            await this.app.mysql.query(sql);
            //添加菜单 
            await this.app.mysql.insert('menu', {
                name: entity.title,
                path: `/entity/${entity.name}`,
                created: app.mysql.literals.now,
                updated: app.mysql.literals.now
            });
        }
    }
}

```