##DB Schema overview

DB Schema是基于 JSON 格式定义的数据结构的规范。

每张表/集合，都有一个表名.schema.json的文件，来描述表的信息、字段的信息。

一个表的简单schema.json示例如下

{
	"bsonType": "object", // 固定节点
	"description": "该表的描述",
	"required": [], // 必填字段列表
	"properties": { // 该表的字段清单
		"_id": { // 字段名称，每个表都会带有_id字段
			"description": "ID，系统自动生成"
			// 这里还有很多字段属性可以设置
		},
		"field2": { // 字段2，每个表都会带有_id字段
			"description": ""
			// 这里还有很多字段属性可以设置
		}
	}
}

DB Schema有很多重要的作用：

• 描述数据表结构。一目了然的阅读每个表、每个字段的用途。
• 设置字段的默认值(defaultValue/forceDefaultValue)，比如服务器当前时间、当前用户id等。
• 设定字段值域能接受的格式(validator)，比如数字、字符串、布尔值，是否可为空，还可以指定的数据要求的正则格式，不符合的格式无法入库。
• 设定字段之间的约束关系(fieldRules)，比如字段结束时间需要晚于字段开始时间。
• 设定多个表的关联关系，字段间映射关系(foreignKey)，将多个表按一个虚拟联表直接查询，大幅简化联表查询。
• 设定数据操作权限(permission)。什么样的角色可以读/写哪些数据，都可以在这里配置。
• Automatically generate front-end interface (schema2code) based on schema, including list, details, new and edit pages, and automatically process validation rules.

除schema外jql还支持使用js编写schema扩展，在数据的增删改查时触发相应的触发器，详见：DB schema 扩展

MongoDB supports structure validation (non-null, type-checked) when inserting and updating documents via the $jsonSchema operator Validation, etc.), $jsonSchema supports draft 4 of JSON Schema, including core specification and [validation specification](https:/ /tools.ietf.org/html/draft-fge-json-schema-validation-00). uniCloud extends JSON Schema based on MongoDB.

编写DB Schema是uniCloud的数据库开发的重要环节。但必须通过JQL操作数据库才能发挥DB Schema的价值。

** So please note that DB Schema does not take effect when using the traditional MongoDB API to operate the database in cloud functions. Whether on the client or in the cloud, you must use JQL to operate the database. **

• If your application can be completed through clientDB, then there will be no need to write server code, and the overall development efficiency will be greatly improved. When the client operates the database, the DB Schema must be completely written, especially the permission part.

• If the permission system of the application is complex, using clientDB is not as convenient as using cloud objects, and other schemas except the permission part should also be written. In this way, other functions such as join table query, tree query, default value, value range check and so on can still be used conveniently.

  Specifically, if you write permission control code in the cloud function, you need to set the permissions of DB Schema to false, and set the operation role to admin in the cloud function (through the setuser API) to skip the schema ASD.

  Of course, the permissions controlled by the code in the cloud function and the permissions in the DB Schema can also be mixed.

Therefore, it is recommended that developers write a good schema, regardless of whether the cloud or the front-end operates the database. At most, the permission part in the schema is ignored when the cloud function processes the permission.

How to write DB Schema

• 方式1，在HBuilderX中编写schema（推荐）

Writing schemas in HBuilderX has good syntax hints and syntax verification, and can be debugged locally. It is a more recommended schema writing scheme.

Create schema

1. Right-click on the uniCloud project and select Create database directory (ignore if there is an existing directory)
2. Right-click in the database directory and select New Data Collection Schema

The schema created in HBuilderX will not be automatically uploaded when new and saved

Upload schema

• Right-click on a single schema file to upload only the currently selected schema. The shortcut key is [Ctrl+u]. (Ctrl+u is the general shortcut key of HBuilderX, whether it is publishing an app or uploading cloud functions and schemas, it is all Ctrl+u)
• Right-click in the database directory to upload all schemas

Download schema

• Right-click on the database directory to download all schemas and extended verification functions

Run the front-end project in HBuilderX, choose to connect to the local cloud function in the console, or run the local cloud function/cloud object directly. At this time, the locally written schema can take effect directly without uploading. Easy to write and debug.

• 方式2，在web控制台编写schema

1. 登录 uniCloud控制台，选中一个数据表
2. 点击表右侧页签 “表结构”，点击 “编辑” 按钮，在编辑区域编写 Schema，编写完毕后点保存按钮即可生效。

web控制台上编辑DB Schema保存后是实时在现网生效的，请注意对现网商用项目的影响。

Schema's first-level node

{
	"bsonType": "object", // 固定节点
	"description": "表的描述",
	"required": [], // 必填字段
	"permission": { 
		"read": false, // 前端非admin的读取记录权限控制。默认值是false，即可以不写。可以简单的true/false，也可以写表达式
		"create": false, // 前端非admin的新增记录权限控制。默认值是false，即可以不写。可以简单的true/false，也可以写表达式 
		"update": false, // 前端非admin的更新记录权限控制。默认值是false，即可以不写。可以简单的true/false，也可以写表达式
		"delete": false, // 前端非admin的删除记录权限控制。默认值是false，即可以不写。可以简单的true/false，也可以写表达式
		"count": false // 前端非admin的求数权限控制。默认值是true，即可以不写。可以简单的true/false，也可以写表达式
	},
	"properties": { // 表的字段清单
		"_id": { // 字段名称，每个表都会带有_id字段
			"description": "ID，系统自动生成"
			// There are many more field properties that can be set here
		}
	},
	"fieldRules":[
		// Constraints between fields. For example, the field start time is less than the field end time. It is also possible to verify only one field. Support expressions
	]
}

Notice

• Counting the data (including the count method and the count operation in groupField) will trigger both the table-level count permission and read permission at the same time

Field attributes

property list

The list of fields in properties, each field has many properties that can be set, as follows:

Attribute Classification	Attribute	Type	Description
Basic	bsonType	any	Field type, such as json object, string, number, bool value, date, timestamp, see the following table for bsonType available types
Basic	arrayType	String	Array item type, valid when bsonType="array", supported by HBuilderX 3.1.0+, see the table below for the available types of arrayType
Basic	title	string	Title, for developer maintenance. When schema2code generates front-end form code, it is used for the label	in front of the form item by default.
Basic	description	string	Description, for developer maintenance. When generating front-end form code, if the field is not set to componentForEdit and the field is rendered as input, the placehold of the input will default to this description
basic	defaultValue	string|Object	default value
Basic	forceDefaultValue	string|Object	Forced default value, which cannot be modified by the clientDB code, and is often used to store fixed values such as user id, time, and client ip. For details, refer to the defaultValue	of the following table
值域校验	required	array	是否必填。支持填写必填的下级字段名称。required可以在表级的描述出现，约定该表有哪些字段必填。也可以在某个字段中出现，如果该字段是一个json对象，可以对这个json中的哪些字段必填进行描述。详见下方示例
值域校验	enum	Array	字段值枚举范围，数组中至少要有一个元素，且数组内的每一个元素都是唯一的。
value field check	enumType	String	The field value enumeration type, the optional value tree. When set to tree, it means that the data in the enum is a tree structure. At this point schema2code can generate multi-level cascade selection components
value range check	fileMediaType	String	File type, valid when bsonType="file", optional value all|image|video The default value is all, which means all files, image means image type file, video means Video type file HBuilderX 3.1.0+
value range check	fileExtName	String	File extension filter, valid when bsonType="file", multiple file extensions are separated by ",", for example: jpg,png, HBuilderX 3.1.0+ support
value range check	maximum	number	If bsonType is a number, the maximum acceptable value
value range check	exclusiveMaximum	boolean	exclude maximum
value range check	minimum	number	If bsonType is a number, the minimum acceptable value
value range check	exclusiveMinimum	boolean	exclude minimum
range check	minLength	number	limit the minimum length of a string or array
range check	maxLength	number	limit the maximum length of a string or array
value range check	trim	String	Remove blank characters, support none|both|start|end, default none, only valid when bsonType="string"
value field check	format	'url'|'email'	Data format, data that does not conform to the format cannot be stored. Currently only 'url' and 'email' are supported, other formats will be extended in the future
Value range verification	pattern	String	Regular expression, if it is set to the regular expression of the mobile phone number, if it does not conform to the regular expression, the verification fails and cannot be stored
value range validation	validateFunction	string	extended validation function name
Permission Verification	permission	Object	Database permissions, control what roles can read/write what data, control tables and fields, and set where conditions. See below details
Error return	errorMessage	string|Object	When the data is written or updated, the error message returned after the validation of the data validity fails
Association	foreignKey	String	Association field. Indicates that the original definition of the field points to a field in another table, and the value format is table name.field name. For example, the order user uid field of the order table points to the _id field of the uni-id-users table, then the value is uni-id-users._id. After the associated field is defined, it can be used for Joint Table Query, and the virtual table is synthesized through the associated field, which greatly simplifies the complexity of the table query
Association	parentKey	String	The field of the parent in the same data table. For details, please refer to: Tree Data Query
schema2code	label	string	Field title. When schema2code generates front-end code, it renders the label title in front of the form item. If left blank, the title attribute will be used. Applicable to the situation that the title is inconvenient to display in front of the form item
schema2code	group	string	Group id. When schema2code generates front-end code, form items corresponding to multiple fields can be combined and displayed in a uni-group component
schema2code	order	int	The order number of the form item. When schema2code generates front-end code, the default is to arrange the form items in the order of fields in the schema from top to bottom, but if order is specified, it will be sorted according to the order specified by order. If the form item is included in a uni-group, the same group is sorted by order
schema2code	component	Object|Array	When schema2code generates front-end code, what component is used to render this form item. Obsolete. Please use the following componentForEdit and componentForShow
schema2code	componentForEdit	Object|Array	HBuilderX 3.1.0+, when generating front-end editing page files (add.vue, edit.vue), what component is used to render this form item. For example, using the input input box.
schema2code	componentForShow	Object|Array	HBuilderX 3.1.0+, when generating the front-end display page (list.vue, detail.vue), what component is used for rendering. For example, use uni-dateformat to format dates.

Notice:

1. schema2code is a function of automatically generating data additions, deletions, modification and query pages according to the scheme. Entry 1 is in the database schema interface of the uniCloud web console; entry 2 is in the context menu of schema in HBuilderX. See details
2. Sub-attribute verification is not supported yet

Example

If you have read the Database Introduction Document, then your service space should have a table resume at this time, and there is a piece of data in it.

Let's still take the resume table as an example, in addition to _id, the table has 6 business fields: name, birth_year, tel, email, address, intro.

The business rules are as follows:

• The name field is a string, the length is greater than or equal to 2 and less than or equal to 17. It is required, and the blank characters at the beginning and the end need to be removed
• The birth_year field is a number greater than or equal to 1950 and less than 2020, required
• The tel field is a string, but the format is a mobile phone number, which is required
• email character is a string, but the format is email, required
• The field type of address is json object, and there are 2 subfields under it, city and street, of which the "city" field is required
• The field type of intro is string, which is not required. You need to remove the leading and trailing blank characters

Then resume.schema.json is written as follows.

{
	"bsonType": "object",
	"required": ["name", "birth_year", "tel", "email"],
	"permission": {
		"read": true,
		"create": true,
		"update": true,
		"delete": true
	},
	"properties": {
		"_id": {
			"description": "ID，系统自动生成"
		},
		"name": {
			"bsonType": "string",
			"title": "姓名",
			"trim": "both",
			"minLength": 2,
			"maxLength": 17
		},
		"birth_year": {
			"bsonType": "int",
			"title": "出生年份",
			"minimum": 1950,
			"maximum": 2020
		},
		"tel": {
			"bsonType": "string",
			"title": "手机号码",
			"pattern": "^\\+?[0-9-]{3,20}$",
			"trim": "both"
		},
		"email": {
			"bsonType": "string",
			"title": "email",
			"format": "email",
			"trim": "both"
		},
		"address": {
			"bsonType": "object",
			"title": "地址",
			"required": ["city"],
			"properties": {
				"city": {
					"bsonType": "string",
					"title": "城市"
				},
				"street": {
					"bsonType": "string",
					"title": "街道",
					"trim": "both"
				}
			}
		},
		"intro":{
			"bsonType": "string",
			"title": "简介",
			"trim": "both"
		}
	}
}

Notice:

• The permission is changed to be operable by everyone for the convenience of testing. In real business, it needs to be adjusted according to business needs. You need to learn uni-id before you can master it.
• It is not recommended for real business to have a city and a street under the address, and it is better to level the hierarchy. In addition, city should be associated with another city table through enum, select it in the candidate city list, and then give an example in the enum document

After the schema is saved, the code can be tested. Note that modifying data in the uniCloud web console is not restricted by schema, and schema takes effect only when data is manipulated through JQL.

We added a new button "Add Data" to the front-end test project

<template>
	<view class="content">
		<button @click="addresume()">添加数据</button>
	</view>
</template>

<script>
	const db = uniCloud.database();
	export default {
		data() {
			return {}
		},
		methods: {
			addresume() {
				db.collection("resume").add({
					"name": "1",
					"birth_year": 1949,
					"tel": "1",
					"email": "1"
				}).then((res) => {
					// res is the database query result
					console.log(res)
				}).catch((err) => {
					console.log(err.message)
				});
			}
	}
</script>

It can be seen that data that does not conform to the rules cannot be stored in the database through JQL operations. The test value of each field can be corrected to legal format test in turn, until it can be put into storage normally.

After success, res will return the id of the newly added record, and you can also see the newly added data in the web console.

The failure prompt can also be customized through errorMessage.

After success, click the "Add Data" button again, and you will find that duplicate data is inserted. To avoid this situation, you need to set an index, such as setting the tel field as a unique index. See details

Officially launched the openDB open source database specification, including many template tables such as user tables, article tables, commodity tables, etc. These template tables have built-in DB Schema for learning and reference. See details

schema internationalization scheme see details

Field type bsonType

• bool: boolean, true|false
• string: string
• password: a special string. Such fields will not be passed to the front end through clientDB, and all users cannot read and write through clientDB, even admin administrators. uni-id-user table with example
• int: integer
• double: precision number. Due to floating point precision issues, use with caution
• object: json object. Geographical location is also an object
• file: a special object that stores information about cloud storage files in a fixed format. It does not store the file directly, but a json object, including the name, path, file size and other information of the cloud storage file. (HBuilderX 3.1.0+)
• array: array
• timestamp: timestamp
• date: date

Complex format description:

• timestamp is a string of digital timestamps, generally obtained through the following js var timestamp = new Date().getTime();. It has the benefit of masking time zone differences. The cloud time zone of Alibaba Cloud and Tencent Cloud is 0, but when the cloud function is run locally in HBuilderX, if it is a Chinese computer, the time zone will be changed to 8, resulting in a confusing display. So it is recommended to use timestamp. But the timestamp is a series of numbers that record milliseconds, which is not suitable for rendering directly to the front-end interface. The recommended practice is to use the <uni-dateformat> component in front-end rendering.
• Date and geographic location cannot be directly entered in quotation marks on the database management interface of the web console. Please refer to document
• Double type is careful, because js cannot accurately handle floating-point operations, 0.1+0.2=0.30000000000000004. So when it comes to amounts, it is recommended to use int instead of double, and store in cents instead of yuan. For example, WeChat payment defaults to cents. If using uniPay to process payments, its default unit is also cents.
• The json object format of file stores the basic information and path of the file, as follows:

{
	"name": "filename.jpg",
	"extname": "jpg",
	"fileType": "image",
	"url": "https://xxxx", // 必填
	"size": 0, //单位是字节
	"image": { //图片扩展
		"width":10,//单位是像素
		"height":10
	},
	"video":{ //video和image不会同时存在。此处仅为列举所有数据规范
		"duration":123,//视频时长，单位是秒
		"poster":"https://xxx" //视频封面
	}
}

In the above format, except url, other fields are optional.

The image key is the extension key of the image. In addition to the basic width and height pixels, developers can expand other keys, such as color position. Similarly video can also be extended by itself.

Example

Take the resume table as an example, add a new photo field photo, set it as file type, and define the format as follows (other old fields are omitted):

{
  "schema": {
    "bsonType": "object",
    "required": ["name", "birth_year", "tel", "email"],
    "properties": {
      "_id": {
        "description": "ID，系统自动生成"
      },
      "photo": {
        "bsonType": "file",
        "title": "照片",
        "fileMediaType": "image", // 可选值 all|image|video 默认值为all,表示所有文件，image表示图片类型文件，video表示视频类型文件
        "fileExtName": "jpg,png", // 扩展名过滤，多个用 , 分割
      }
    }
  }
}

file's front-end supporting components:

The uni-ui component library contains components: <uni-file-picker>. This component works perfectly with the database for the file field.

The component first selects the file, uploads it to the uniCloud cloud storage, and writes the address of the uploaded file into the file field after the form is submitted. For details, see: https://ext.dcloud.net.cn/plugin?id=4079

file and schema2code:

After the DB Schema defines the field type as file, you can directly generate the upload form page through the schema2code tool. The front-end page contains the <uni-file-picker> component, and you can select, upload, and write the library in one go. See: schema2code

Subtype of array field type arrayType

If the bsonType of a field is array, then it can further specify the bsonType of each array item in the array through arrayType, and the value range is still all field types.

For example, if a field stores multiple images, you can set bsonType to array, and then further set arrayType to file.

Example

Take the resume table as an example, add a new photo field photos, set it as file type, and define the format as follows (other old fields are omitted):

{
  "schema": {
    "bsonType": "object",
    "required": [],
    "properties": {
      "_id": {
        "description": "ID，系统自动生成"
      },
      "images": {
        "bsonType": "array",
        "arrayType": "file",
        "title": "照片",
		"multiple": true, // 允许选择多张图片，schema2code生效
        "fileMediaType": "image", // 可选值 all|image|video 默认值为all,表示所有文件，image表示图片类型文件，video表示视频类型文件
        "fileExtName": "jpg,png", // 扩展名过滤，多个用 , 分割
        "maxLength": 3 // 限制最大数量
      }
    }
  }
}

• When the arrayType is file, it is the same as if the single bsonType is file. Both the <uni-file-picker> component and schema2code can be used.
• multiple, fileMediaType, fileExtName, maxLength can all take effect on client pages generated by schema2code

Default value defaultValue/forceDefaultValue

Both defaultValue and forceDefaultValue are default values, that is, when a new row of data records is added, if the field content is not provided, the field content will be filled with the default value. But there are also differences between the two, as follows:

• defaultValue is not mandatory and is a common default value. If the client uploads another value, the value passed by the client shall prevail.
• forceDefaultValue is the mandatory value of the schema, which cannot be modified no matter what the client passes. Whenever a new record is added to the database, the value of the field will be forceDefaultValue.

In actual development, forceDefaultValue is often used to set the current server time, current login user id, client ip, etc. None of these data can be uploaded through the front end, which is not safe. In the past, only cloud function operations could be written in the cloud. After the schema configuration, you don't need to write cloud functions. These data are automatically filled when adding new data records using JQL.

Fixed values can be used in defaultValue/forceDefaultValue, and you can also use the preset variable $env in the following form:

"forceDefaultValue": {
  "$env": "now"
}

The preset variable $env can take the following values:

Variable	Description
now	Current server timestamp
clientIP	Current client IP
uid	Current user ID, based on uni-id. If the current user is not logged in or the login status is invalid, an error will be reported

Example:

// Specify the default value to be true
"defaultValue": true

// Specifies that the mandatory default is the current server timestamp
"forceDefaultValue": {
  "$env": "now"
}

// Specifies that the mandatory default is the current client IP
"forceDefaultValue": {
  "$env": "clientIP"
}

// Specify the mandatory default value for the current client id
"forceDefaultValue": {
  "$env": "uid"
}

Taking the resume table as an example, a new field create_time is added to indicate the creation time of the record.

The defaultValue of this field specifies the server time. When adding a new record, if the front end does not pass this field, the default is the current server time. If the front end passes a specified value, the passed value shall prevail.

{
  "bsonType": "object",
  "required": [],
  "properties": {
    "create_time": {
      "bsonType": "timestamp",
      "title": "创建时间",
      "defaultValue": {
        "$env": "now"
      }
    }
  }
}

Force default value forceDefaultValue, specified as the current server timestamp. At this time, any value passed from the front end is invalid, and when a new record is added, it will definitely become the current cloud time.

{
  "bsonType": "object",
  "required": [],
  "properties": {
    "create_time": {
      "bsonType": "timestamp",
      "title": "创建时间",
      "forceDefaultValue": {
        "$env": "now"
      }
    }
  }
}

In actual business, the creation time of the record cannot be tampered with by the client, for example, it is forced to the cloud time. So forceDefaultValue must be used in this scenario.

foreignKey field foreign key

A complex business system has many data tables. There are data associations between tables. foreignKey is used to describe data associations.

For example, an article system requires at least a user table, an article classification table, an article table, and a comment table. Opendb already contains these 4 tables, you can click the link to see the structure of these tables:

• User table: uni-id-users
• Article classification table: opendb-news-categories
• Article table: opendb-news-articles
• Comments form: opendb-news-comments

Sub-table

Let's not expand the description of the above tables first, and first explain why and how to divide the tables.

• Developers familiar with relational databases are no strangers to table design It's just that in the uniCloud database, a new schema is added that the traditional database does not have, and there is also a foreignKey to express the relationship between the various tables. For example, the article table in the above tables: opendb-news-articles, the author of The field user_id is configured with "foreignKey": "uni-id-users._id"
• For beginners, the first thing you need to understand is why you need to divide the table.

Because of the flexibility of MongoDB, in theory, a new field articles can be added to the user table [uni-id-users], and each article of the author can be stored in an array under articles, and then a field comments can be added to the article. Holds every comment on this article.

As follows, the data content of the uni-id-users table, if there are 2 users, zhangsan and lisi, then lisi wrote an article, and this article was commented 1 by zhangsan.

[{
	"_id": "60b92a42e22fbe00018c359d",
    "username": "zhangsan",
    "password": "03caebb36670995fc261a275d212cad65e4bbebd",
    "register_date": 1622747714731,
    "register_ip": "192.168.0.1",
},
{
	"_id": "60b9315801033700011ba9ed",
    "username": "lisi",
    "password": "03caebb36670995fc261a275d212cad65e4bbebd",
    "register_date": 1622747714731,
    "register_ip": "192.168.0.2",
	"articles":[
		{
			"title": "文章标题",
			"content": "文章内容",
			"publish_date": 1617850851000,
			"publish_ip": "192.168.0.2",
			"comments":[
				{
					"user_id":"60b92a42e22fbe00018c359d",
					"comment_content":"评论内容",
					"comment_date":1617850851022,
					"comment_ip": "192.168.0.1"
				}
			]
		}
	]
}]

It can be seen that this uni-id-users table forms a three-level nesting of users, articles, and comments.

Although MongoDB can be nested like this, it should not be designed like this in actual business. It will lead to low query performance and even some query conditions cannot be realized.

The database is the bottom layer of the digital system. It should be clear and organized. People, articles, comments, and the relationship between the three should be clear and not redundant.

The nesting of MongoDB is more suitable for scenarios with fewer records that will not be queried separately.

For example, the work experience in the resume table can be nested. Because the number of work experience is small, and there is no situation where work experience is checked alone without checking people.

But the article table must be independent, because the number of articles will be very large, it will be searched separately;

In fact, the comment form does not need to be searched separately, it always appears with the specified article. But because the number will be large, comments also need paging query, nesting under the article table is not conducive to paging query.

Therefore, the correct database design is to separate these tables. In addition, many article systems will have article classifications, such as society, education, entertainment, sports, technology..., so an article classification table is also required.

These four tables of opendb are the correct table design.

• User table: uni-id-users
• Article classification table: opendb-news-categories
• Article table: opendb-news-articles
• Comments form: opendb-news-comments

You can see that all registered users are in the uni-id-users table, and the article content is in the opendb-news-articles table. A user may have written many articles, and these articles will not be stored in the uni-id-users table.

Relationship between tables

Now that there is the concept of sub-tables, there is the concept of the relationship between tables.

For example, in the article table, how to store the author information of the article? How to indicate which user wrote this article? Is it the username that stores the author?

In fact, the author field in the article table, that is, the user_id field, stores the value of the _id field of the author in the user table. _id is a unique field for each record of each table in the uniCloud database.

You can look at the specific data of the user table uni-id-users and the article table opendb-news-articles, and intuitively feel:

uni-id-users user table, or if there are 2 authors, zhangsan and lisi

[{
	"_id": "60b92a42e22fbe00018c359d",
    "username": "zhangsan",
    "password": "03caebb36670995fc261a275d212cad65e4bbebd",
    "register_date": 1622747714731,
    "register_ip": "127.0.0.1",
},
{
	"_id": "60b9315801033700011ba9ed",
    "username": "lisi",
    "password": "03caebb36670995fc261a275d212cad65e4bbebd",
    "register_date": 1622747714731,
    "register_ip": "127.0.0.1",
}]

The opendb-news-articles article table contains only 1 article. This article is written by lisi, so the field user_id stores 60b9315801033700011ba9ed, which is the _id corresponding to lisi in the uni-id-users table

{
	"_id": "606e721280b8450001e773c6",
    "category_id": "606e6feb653b8400017214a3",
    "title": "这里是标题",
    "content": "这里是正文",
    "user_id": "60b9315801033700011ba9ed",
    "publish_date": 1617850851000,
    "publish_ip": "119.131.174.251"
}

As long as the user_id relationship is mapped, the data is clear and complete.

There is no need to store the author's user name, nickname, avatar, registration time or even password in the article table opendb-news-articles, only its user_id is needed to express the data relationship accurately and with minimal redundancy.

Of course, some systems are designed to redundantly store author nicknames and avatars in the article table in order to reduce joint table queries. Whether to use redundancy can be determined according to needs, but user_id must be used to associate data tables.

foreignKey usage

The above shows the data content of the 2 tables, but back to the DB Schema, how to express this relationship in the schema? That is foreignKey, the foreign key.

The description of the user_id field in the DB Schema of the article table opendb-news-articles is as follows:

"properties": {
  "_id": {
	"description": "存储文档 ID（用户 ID），系统自动生成"
  },
  "user_id": {
	"bsonType": "string",
	"description": "文章作者ID， 参考`uni-id-users` 表",
	"foreignKey": "uni-id-users._id",
	"defaultValue": {
	  "$env": "uid"
	}
  },
  "title":{},
  "content":{}
}

The key point above is this foreignKey, the first half of it is the table name of another table, separated by . in the middle, and the second half is the field name of another table.

It represents the user_id field of the article table, which in essence points to the _id field of the uni-id-users table. That is, the value stored in the user_id field of the article table is actually derived from the value in the _id field of the uni-id-users table.

Be careful not to confuse it, it is not to assign foreignKey in the _id field of the schema of the uni-id-users table. The _id field of the uni-id-users table is the original value, not quoted from anywhere. Instead, it is allocated in other fields that reference uid.

Similarly, in the schema of the comment table opendb-news-comments,

• The foreignKey of the comment user id field user_id needs to point to "uni-id-users._id"
• The foreignKey of the article id field article_id needs to point to "opendb-news-articles._id"

Configure foreignKey, in addition to clearly describing the data relationship, its biggest role is join table query.

JQL does not provide syntaxes such as join, leftjoin, and innerjoin like SQL. You only need to configure the data relationship and foreignKey, and you can automatically join the table query when querying.

There are many query contents in the joint table, see details

parentKey tree table

In traditional relational databases, trees are difficult to express, and only a commercial database such as oracle provides tree queries. Other relational databases require developers to implement tree queries through complex code.

In MongoDB, although it naturally supports trees, it does not use MongoDB's json nesting method to describe trees in actual business.

For example, in the department tree, departments can dynamically add, delete, rename, and move levels. In fact, for each department, the data in the department table is still an independent row data record, not infinitely nested in a record.

For example, in the department table, there are 2 pieces of data, one data record is "headquarters", and the other data record is "first-level department A"

• Wrong practice:

{
    "_id": "5fe77207974b6900018c6c9c",
    "name": "总部",
	"child": [
		{
		    "_id": "5fe77232974b6900018c6cb1",
		    "name": "一级部门A",
		    "child": [],
		    "status": 0
		}
	],
    "status": 0
}

Unless your department is just these 2, it will never change. Otherwise, the above practice should not be used.

1. If many departments are deeply nested, the query writing method will be difficult
2. If it is necessary to provide a visual interface to the operation and maintenance personnel, it is difficult to dynamically add, delete and modify departments. Often need to change the code rather than directly changing the data.

• Correct way: Each department is a record, using parent_id to describe the hierarchical relationship.

[{
    "_id": "5fe77207974b6900018c6c9c",
    "name": "总部",
    "parent_id": "",
    "status": 0
},
{
    "_id": "5fe77232974b6900018c6cb1",
    "name": "一级部门A",
    "parent_id": "5fe77207974b6900018c6c9c",
    "status": 0
}]

In the parent_id of "first-level department A", the value is 5fe77207974b6900018c6c9c, which is actually the _id of "headquarters".

So how to express this relationship in DB Schema? Just use parentKey.

In the schema of the department table opendb-department, set the "parentKey" of the field parent_id to "_id", which specifies the parent-child relationship between the data, as follows:

{
  "bsonType": "object",
  "required": ["name"],
  "properties": {
    "_id": {
      "description": "ID，系统自动生成"
    },
      "name": {
      "bsonType": "string",
      "description": "名称"
    },
    "parent_id": {
      "bsonType": "string",
      "description": "父id",
      "parentKey": "_id", // 指定父子关系为：如果数据库记录A的_id和数据库记录B的parent_id相等，则A是B的父级。
    },
    "status": {
      "bsonType": "int",
      "description": "部门状态，0-正常、1-禁用"
    }
  }
}

parentKey describes the parent-child relationship of different records in the data table. If the property of a field A sets the parentKey and points to another field B, then the value of this A must be equal to the value of B.

After using the parentKey to describe the parent-child relationship of the field, you can easily query the tree through JQL's getTree. Due to the large amount of content, see details

Field value domain rules validator

DB Schema provides a complete set of field value domain description rules, and automatically performs data entry verification, and data that does not meet the rules cannot be written to the database.

Note that only when writing content to the database (adding a record or modifying a record) involves the validation of the field value field. Read and delete are not involved.

The field range validation system in DB Schema consists of 4 parts:

1. Field attribute configuration: whether required (required), number range (maximum, minimum), string length range (minLength, maxLength), format, pattern regular expression, enum, trim, etc. All attribute tables are classified in Attributes in range validation
2. Relational constraints between fields: fieldRules. At the schema level node, and properties level. It is more powerful than the property configuration of fields, and can describe the relationship between fields. For example, the end time of the field must be greater than the start time of the field; it can be programmed simply
3. The extended validation function of the field: validateFunction. When the property configuration and fieldRules can not meet the requirements, you can also write a complete js programming for verification.
4. Error message: errorMessage. Common errors have default error messages. Developers can also customize the error message

1. Field property configuration

requiredRequired fields

In the required of the schema first-level node, you can fill in multiple field names in the form of an array. For example, the following example sets the name field as required

{
  "bsonType": "object",
  "required": ["name"],
  "properties": {
    "name": {
      "bsonType": "string",
      "title": "姓名",
      "errorMessage": "{title}不能为空"
    }
  }
}

The required of a field is related to other rules of the field as follows:

• When the field is required, it must be passed in
• When the field is not required, if the incoming data contains this field, other rules will be verified, otherwise other verification rules of the field will be ignored.

Take the following code as an example, if the value of name is not passed, the verification can be passed; if the name is passed, the minimum length of the name is required to be 2, otherwise the verification fails

{
  "bsonType": "object",
  "required": [],
  "properties": {
    "name": {
      "bsonType": "string",
      "title": "姓名",
      "minLength": 2,
      "errorMessage": {
        "required": "{title}不能为空",
        "minLength": "{title}不能小于 {minLength} 个字符"
      }
    }
  }
}

format

Only valid for string type fields.

The url format of format is supplemented as follows:

http:// | https:// | ftp:// begins, // must contain a . (except localhost)

valid format

• http://dcloud.io
• https://dcloud.io
• http://localhost

invalid format

• http://dcloud
• https://dcloud
• mailto:dcloud@dcloud.io
• file:\
• file:\\

Example

You can add an email field to the resume table and use format to constrain its format.

{
  "bsonType": "object",
  "required": ["email"],
  "properties": {
    "email": {
      "bsonType": "string",
      "title": "邮箱",
      "format": "email",
      "errorMessage": {
        "required": "{title}不能为空",
        "format": "{title}格式无效"
      }
    }
  }
}

pattern regular expression

Example: Verify phone number "pattern": "^\\+?[0-9-]{3,20}$"

{
  "bsonType": "object",
  "required": ["name"],
  "properties": {
    "name": {
      "bsonType": "string",
      "title": "姓名",
      "pattern": "",
      "errorMessage": {
        "required": "{title}不能为空",
        "pattern": "{title}格式无效"
      }
    }
  }
}

enum enumeration control range

enum，即枚举。一个字段设定了enum后，该字段的合法内容，只能在enum设定的候选数据项中取值。HBuilderX 3.7.13移除校验数据时enum最多只可以枚举500条的限制。

enum supports 3 data formats to describe candidates:

1. Simple array
2. Supports complex arrays of descriptions
3. Data table query

simple array in enum format

For example, adding a gender field to the resume table, the legal value field can only be one of "0", "1", and "2".

{
  "bsonType": "object",
  "required": [],
  "properties": {
    "_id": {
      "description": "存储文档 ID（用户 ID），系统自动生成"
    },
    "gender": {
      "bsonType": "array",
      "title": "性别",
      "description": "用户性别：0 未知 1 男性 2 女性",
      "enum": [0,1,2]
    }
  }
}

After the field gender is set like this, if the inserted or modified data is not 0 or 1 or 2, an error will be reported and data cannot be inserted or updated.

When generating front-end form pages through schema2code, fields with enums will generate picker components. When the component is rendered on the interface, it will generate three candidate checkboxes of "1, 2, 3". Therefore, the use of simple arrays is generally not recommended, but the arrays described below are recommended.

complex arrays that support descriptions in enum format

Still using the gender field as an example, the legal value field can only be one of "0", "1", and "2". However, when the front-end form page is generated by schema2code, this field will generate the uni-data-checkbox component, which will generate three candidate checkboxes of "unknown", "male" and "female" when rendered on the interface.

{
  "bsonType": "object",
  "required": [],
  "properties": {
    "_id": {
      "description": "存储文档 ID（用户 ID），系统自动生成"
    },
    "gender": {
		"bsonType": "int",
		"title": "性别",
		"defaultValue": 0,
		"enum": [
			{
				"text": "未知",
				"value": 0
			},
			{
				"text": "男",
				"value": 1
			},
			{
				"text": "女",
				"value": 2
			}
		]
	  }
  }
}

This description method improves the readability of the schema, and also makes the front-end interface generated by schema2code more usable.

In the case of relatively few candidates, schema2code may not be appropriate to use a picker that needs to be popped up once. If you want to tile candidates in the interface, such as male, female, unknown, and display them directly in the form, you can use the [uni-data-checkbox component](https://ext.dcloud.net .cn/plugin?id=3456) to express gender selection.

Data table query in enum format

The legal value range of a field, which can be queried from another data. That is, JQL query statements can be configured in the enum.

This method needs to be used with foreignKey, that is, it needs to be associated with another table

There is a nationality table opendb-nation-china in opendb, which stores 56 nationalities in China.

If we want to add a nation field to the resume table, it should get the value from this opendb-nation-china table.

1. First create a new ethnic table

Right-click in the project root directory uniCloud/database, create a new schema, and select opendb-nation-china

The preset data of this opendb table needs to be uploaded to the cloud before it is added to the database. So you need to right-click on this opendb-nation-china.schema.json and upload the DB Schema

You can create it in the uniCloud web console, and the data will be automatically stored in the database at this time, but you need to right-click on the project root directory uniCloud/database -> Download DB Schema, so that it can be used during local debugging.

2. Establish an association table relationship and configure the jql query of the enum

Set the foreign key of the nation field "foreignKey": "opendb-nation-china.name". Ethnicity is relatively simple, here we directly take the Chinese character name of the ethnicity table as the associated key, and do not take the database ID.

Then set the enum of the nation field as follows:

{
  "bsonType": "object",
  "required": [],
  "properties": {
    "_id": {
      "description": "存储文档 ID（用户 ID），系统自动生成"
    },
    "nation": {
      "bsonType": "string",
      "title": "民族",
      "foreignKey": "opendb-nation-china.name",
      "enum": {
        "collection": "opendb-nation-china",
        "orderby": "first_letter asc",
        "field": "name as value, name as text"
      }
    }
  }
}

In this way, if the client uploads a national name that is not in the opendb-nation-china table, it cannot be stored in the database.

When the front-end form page is generated by schema2code, the field will generate the picker component. After the component is clicked, the candidate items will pop up. These candidate items are all queried and displayed from the ethnic table.

• Tree-type data for data table query in enum format

In addition to ordinary two-dimensional data tables, enum also supports tree data. That is, the enumType is tree.

There is a city table opendb-city-china in opendb, which stores various cities in China. Cities are tree-like data divided into three levels by province, city, and district.

In the resume table, there is a city field, and its reasonable field rule should be to get the value from the opendb-city-china table,

Set enumType to "tree", which means that the data in the enum is a tree structure, such as the following example, which means that the opendb-city-china table is queried by getTree. In schema2code, multi-level cascade selection components can be automatically generated, see details

{
  "schema": {
    "bsonType": "object",
    "required": ["city_id"],
    "properties": {
      "_id": {
        "description": "ID，系统自动生成"
      },
      "city_id": {
        "bsonType": "string",
        "title": "地址",
        "description": "城市编码",
        "foreignKey": "opendb-city-china.code",
        "enumType": "tree",
        "enum": {
          "collection": "opendb-city-china",
          "orderby": "value asc",
          "field": "code as value, name as text"
        }
      }
    }
  }
}

trim remove leading and trailing whitespace characters

Whether to trim spaces on both sides of the string. Only valid for string type fields.

value	description
none	Does not process. Default is none
both	Removes whitespace characters from both ends of a string. Whitespace characters in this context are all whitespace characters (space, tab, no-break space, etc.) and all line terminator characters (such as LF, CR, etc.)
start	Remove whitespace from beginning of string
end	Remove whitespace from the end of a string

The priority of trim is higher than other validation rules for strings, such as format, pattern, minLength, validateFunction, and fileRules. After configuring trim, the JQL engine will first trim the string and then pass it to other verification systems for verification.

After trim is configured, the input box in the front-end page generated by schema2code will also automatically trim the user's input before submitting it to the cloud.

Example

Taking the resume table as an example, the name field has a limit of minLength of 2. If the value of the inserted name is "a", the space after a will be trimmed first, and the length will become 1, so this data cannot be read. Write to the database.

{
  "bsonType": "object",
  "required": ["name"],
  "properties": {
    "name": {
      "bsonType": "string",
      "title": "姓名",
      "minLength": "2",
      "trim" : "both"
    }
  }
}

2. fieldRules field-to-field validation

Since HBuilderX 3.1.0, it is supported to configure the first-level node fieldRules in the schema to constrain and verify the relationship between fields. Of course, only one field can be checked.

The writing method of fieldRules is equivalent to the JQL where writing method (you can also use various database operation methods), refer to: JQL where

The configuration in fieldRules is as follows. Multiple rules can be configured in the array. Each rule has three parts: rule expression, error message, and running compatible environment.

{
  "fieldRules": [{
    "rule": "end_date == null || end_date != null && create_date < end_date", // 校验规则
    "errorMessage": "创建时间和结束时间不匹配", // 错误提示信息（仅在新增时生效，更新数据时不会提示此信息）
    "client": false // schema2code时，当前规则是否带到前端也进行校验。目前此属性暂不生效，fieldRules不会在客户端校验数据，仅会在云端进行校验
  }],
}

The rule expression is a set of js, and the return value must be true or false. Returning false triggers a prompt error, and the error prompt displays the content of the errorMessage.

Rule expressions support:

1. Field name
2. Database operation method
3. js syntax
4. Also supports new Date() to get the time. It should be noted that unlike database operation methods, database fields cannot be passed in as parameters in new Date()

In the above configuration, create_date and end_date are field names. The schema also supports write field operation methods, such as the add method.

Example: In the todo table, you can use fieldRules to limit end_date to be greater than create_date

{
  "bsonType": "object",
  "required": ["title","create_date"],
  "fieldRules": [{
    "rule": "end_date == null || end_date != null && create_date < end_date",
    "errorMessage": "结束时间需大于创建时间"
  }],
  "properties": {
    "title": {
      "bsonType": "string",
      "title": "标题"
    },
    "create_date": {
      "bsonType": "timestamp",
      "title": "创建时间",
      "forceDefaultValue": {
        "$env": "now"
      }
    },
    "end_date": {
      "bsonType": "timestamp",
      "title": "结束时间"
    }
  }
}

In the above example, create_date is required, just limit end_date to be greater than create_date when it exists

Notice

• When adding/updating data, the fieldRules associated with all new/updated fields will be checked. As in the above rule, if the end_date field or the create_date field is updated, the validation will be triggered
• When adding data, you do not need to check the database for verification, and when updating data, you need to check the database once (also once when there are multiple fieldRules)
• Regular use is not supported in fieldRules

3. validateFunction extends the validation function

Extended check function

当属性配置不满足需求，需要写js函数进行校验时，使用本功能。（当然也可以使用schema.ext.js来替代）

Notice

• 扩展校验函数不能有其他依赖。有相关需求需使用schema.ext.js来替代。
• Try not to use global variables in extended validation functions. If you must use them, make sure you have read and understood the Launch Mode of Cloud Functions

how to use

• Method 1: Create in the uniCloud web console

1. uniCloud 控制台，选择服务空间，切换到数据库视图
2. 底部 “扩展校验函数” 点击 “+” 增加校验函数
3. Give the function a name, such as "checkabc"

• Method 2: Create in HBuilderX HBuilderX 3.0.0 and above, you can create an extended verification cloud function under the project and upload it. The usage method is as follows:

1. Select the project in the project manager on the left, right-click the uniCloud directory under it, and select Create database directory (if the directory already exists, ignore this step)
2. Right-click on the database directory created in the first step and select Create Database Extended Validation Function Directory
3. Right-click in the validateFunction directory created in the second step and select New Database Extended Validation Function

Right-click on the validateFunction directory, you can also upload and download validateFunction, and synchronize with the uniCloud web console.

An example of the extended check function is as follows

//Example of extended validation function
module.exports = function (rule, value, data, callback) {
  // rule current rule
  // value current rule validation data
  // data all check data
  // callback is optional, generally used to customize errorMessage, if the callback return value is invalid, the message passed in callback will replace errorMessage
  // callback('message') Failed to verify when an error message is passed in
  // callback() is passed when there are no parameters
  // Note that callback does not support asynchronous calls, please use Promise/await/async for asynchronous
  return value.length < 10
}

// Check Promise asynchronously
module.exports = function (rule, value, data) {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      if (value > 10) {
        // check passed
        resolve()
      } else {
        // validation failed
        resolve('error') // 等于 reject(new Error('error'))
        // reject(new Error('error'))
      }
    }, 3000);
  })
}

// asynchronous check await/async
module.exports = async function (rule, value, data) {
  let result = await uni.request({...})
  if (result > 10) {
    // check passed
    return true
  } else {
    // validation failed
    return 'error message'
  }
}

After writing validateFunction in HBuilderX, press Ctrl+u to quickly upload validateFunction to uniCloud cloud.

3. Reference the written validateFunction in the required fields

After writing the extended validation function, determine the field to be configured in the table structure schema, and configure the name of the extended validation function written above on the validateFunction attribute of the field.

In the following example, before the content of the name field is to be stored in the database, the extended verification function of "checkabc" will be triggered to execute. If the "checkabc" check does not return true, the database operation will fail.

When the type of validateFunction is a string, both the cloud and the client take effect

{
  "bsonType": "object",
  "required": ["name"],
  "properties": {
    "name": {
      "bsonType": "string",
      "title": "姓名",
      "validateFunction": "checkabc",
      "errorMessage": {
        "required": "{title}不能为空"
      }
    }
  }
}

When the validateFunction type is an object, the configurable client does not take effect, and the cloud still takes effect

HBuilder 3.1.0+ support

{
  "bsonType": "object",
  "required": ["name"],
  "properties": {
    "name": {
      "bsonType": "string",
      "title": "姓名",
      "validateFunction": {
          "name": "checkabc", // 扩展校验函数名
          "client": false //如果不配置默认是 true
      },
      "errorMessage": {
        "required": "{title}不能为空"
      }
    }
  }
}

Tip: If "client": false is configured, the client can also change its own verification function in the generated code. At this time, the verification of the client is still valid, and the corresponding verification file directory of the client is js_sdk/ validator/collection, collection is the table name, not a fixed value

Extended validation function is service space level, an extended validation function can be referenced by any field in any table under this service space.

The code in the Extended Validation Function can be connected to the Internet. A common scenario is the filtering of sensitive words in the content. The content can be submitted to the third-party verification service, and if the verification is passed, it will be restocked.

It is not recommended to write a lot of code in Extended Validation Function, it will affect the performance.

Notes on the operating environment of the extended verification function

The default running environment of extended verification function is the same as that of common cloud functions, and it can call various APIs available in cloud functions. * If you want to connect to the external network, you need to call uniCloud.httpclient; * If you want to call the database, you need to use the method of operating the database in the cloud function, that is, JQL is not supported, see details

However, in schema2code, the extended validation function will also be generated into the validation rules of the front-end page.

That is to say, if you use schema2code to generate front-end pages, then you need to pay more attention to writing the extended verification function.

For example, when an API such as uniCloud.httpclient that does not exist on the front end is called, the form validation on the front end will go wrong.

At this time, you need to write an if judgment in the extended verification function to avoid the problem of undefined.

if (uniCloud.httpclient) {
	console.log("此处运行在云函数环境里。前端没有这个API");
}
// or another way of writing
if (uni) {
	console.log("此处运行在前端环境里。云函数没有uni对象，除非你在validateFunction里自己定义了这个对象");
}

4. schema.ext.js

schema.ext.js是schema.json的扩展和补充，它可以以编程的方式对数据的增删改查进行监听，然后执行任意操作。所以同样可以用于字段的值域校验。

schema.ext.js与validator function的区别是，validator function是针对某一个字段的控制，返回布尔值。而schema.ext.js是对整个表的自由编程。

schema.ext.js篇幅较长，另见schema.ext.js

5. errorMessage自定义错误提示

When the data does not meet the specifications of the schema configuration, it cannot be stored in the database, and an error will be reported at this time.

uniCloud has some basic error message formatting. If you need to customize the error message, you need to use this function to report the error message according to the definition of errorMessage.

errorMessage supports strings as well as json objects. When the type is object, multiple verification prompts can be defined.

{} is a placeholder in which you can refer to existing properties, such as title, label, etc.

property	type	description
minLength	string	message
...	...	...

Example

{
  "required": ["name"],
  "properties": {
    "name": {
      "bsonType": "string",
      "title": "姓名",
      "minLength": 2,
      "maxLength": 8,
      "errorMessage": {
        "required": "{title}必填",
        "minLength": "{title}不能小于{minLength}个字符",
        "maxLength": "{title}不能大于{maxLength}个字符"
      },
      ...
    },
    "age": {
      "bsonType": "int",
      "title": "年龄",
      "minimum": 1,
      "maximum": 150,
      "errorMessage": "{title}应该大于 {minimum} 岁，小于 {maximum} 岁"
    }
  }
}

As can be seen from the example, the errorMessage supports one, and also supports different errorMessages according to different error types.

• If each check-related property fails, its error message can be configured with the property name key.
• If it is an extended verification function, you can write a callback inside it to customize the error message.

Other Notes

"The value of a field in the database cannot be repeated in multiple records", this requirement is generally not implemented in the field value range check, but in the database index to configure the field as a unique index. See details

Indexes can be configured in the web console, and db_init.json can also create indexes. Note that if the field has duplicate content in multiple records in the database, an error will be reported when the field is set as a unique index, and the duplicate data must be removed first.

Data permission system permission

Overview

The data permission system of DB Schema is designed for JQL, especially clientDB strongly relies on this permission system. Because the client cannot be trusted and there is no careful permission system, the client can arbitrarily change the contents of the cloud database.

In the past, developers needed to write code in the backend to handle permission control, but in fact, with DB Schema and uni-id, the backend code for this permission control does not need to be written anymore.

As long as you configure the permissions of DB Schema, let the front end write business. If the data declared in the configuration cannot be read or written, the front end cannot read or write it.

The permission rule of DB Schema is divided into two parts, one is the specification of the operation data, and the other is the specification of the role.

1. Designation of data

• Add, delete, modify, check or count control can be performed on the entire table
• Can read and write control for fields
• You can configure specific where rules to perform delete, change and check control on specified data records
• By default, it comes with a description of special data, which is the data (doc) of the current request plan operation. The usage will be explained in detail later

2. Assignment of roles

• Not logged in, i.e. visitors can manipulate data
• Currently logged in user (auth.uid)
• uni-id定义的其他角色
  • 开发者可以在uni-id中自定义各种角色，比如部门管理员，然后在DB Schema的permission中配置其可操作的数据。详见uni-id的角色权限

Note: If the login user is the admin role of uni-id, that is, the super administrator, it is not restricted by the configuration of DB Schema, and the admin role has read and write permissions to all data.

For example, in the management system such as uniCloud admin, as long as you log in with the admin user, you can operate the database on the front end.

When updating the cloud DB Schema, if it is found that there is no uni-id public module in the service space, uni-id will be installed automatically. If the uni-id already exists in the service space, it will no longer be automatically installed. At this time, you need to pay attention to upgrading uni-id in time to avoid compatibility problems with too old uni-id. (as of HBuilderX 3.5, changed to uni-id-common common module)

Table-level permission control

Table-level control includes four permissions for adding, deleting, modifying, and checking, which are called: create, delete, update, and read. (Note that the industry-wide crud naming is used here, which is different from the methods add(), remove(), update(), and get() for operating the database, but the meaning is the same)

Since HBuilderX 3.1.0, the count permission has been added, that is, whether the table has the right to count statistics.

The default value for all operations is false. That is, if the permission is not configured, the database cannot be operated (except for the role of the admin user).

For example, a user table has fields such as _id, name, and pwd. The DB Schema of the table is as follows, which means that front-end users (including tourists) can read it, but front-end non-admin users cannot add, delete, or update data records.

// schema of the user table
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": true, // 任何用户都可以读
    "create": false, // 禁止新增数据记录（admin权限用户不受限）
    "update": false, // 禁止更新数据（admin权限用户不受限）
    "delete": false, // 禁止删除数据（admin权限用户不受限）
    "count": false // 禁止查询数据条数（admin权限用户不受限），新增于HBuilderX 3.1.0
  },
  "properties": {
    "_id":{},
    "name":{},
    "age": {},
    "pwd": {}
  }
}

About the count permission

• Before HBuilderX 3.1.0, the count operation will be verified using the table-level read permission. HBuilderX 3.1.0 and later versions, if the count permission is configured, the table-level read+count permission will be used for verification, and the verification can only be passed if both of them are satisfied.
• If there is no count permission in the schema, only the read permission will be used for verification
• All operations that will count the number will trigger the count permission check

Field-level permission control

The field-level control of permission, including read and write permissions, are called read and write.

That is, for a specified field, you can control which roles can read the field content, and which roles can modify and write the field content.

Taking the above user table as an example, if you want to restrict the front end from reading the age field, then configure as follows, write the permission node under the field age, and set read to false.

// schema of the user table
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": true, // 任何用户都可以读
    "create": false, // 禁止新增数据记录（admin权限用户不受限）
    "update": false, // 禁止更新数据（admin权限用户不受限）
    "delete": false // 禁止删除数据（admin权限用户不受限）
  },
  "properties": {
    "_id":{
    },
    "name":{
    },
    "age": {
      "bsonType": "number",
      "title": "年龄",
      "permission": {
        "read": false, // 禁止读取 age 字段的数据（admin权限用户不受限）
        "write": false // 禁止写入 age 字段的数据（admin权限用户不受限）
      }
    }
  }
}

According to the above configuration, when the front-end queries data, if the age field is not included, it can still be queried. However, if the query request contains the age field, the request will be rejected, indicating that you do not have permission to access.

The child will inherit the parent's permission, that is, the parent's permission and the node's permission must be satisfied at the same time before the node can be operated. For example, in the above schema, if the table-level read permission is configured to be false, and the read permission is set to true for the name, the name field is still unreadable.

If the bsonType of the field is configured as password, clientDB cannot operate this field at all (even the admin user cannot read and write on the client side).

// schema of the user table
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": true, // 任何用户都可以读
    "create": false, // 禁止新增数据记录（admin权限用户不受限）
    "update": false, // 禁止更新数据（admin权限用户不受限）
    "delete": false // 禁止删除数据（admin权限用户不受限）
  },
  "properties": {
    "_id":{
    },
    "name":{
    },
    "pwd": {
      "bsonType": "password", // 即使不配置权限，此字段也无法在客户端读写
      "title": "密码"
    }
  }
}

Specify data set permission control

DB Schema provides a built-in variable doc representing the data record to be manipulated. And supports the use of various expressions to describe the specified record.

Still take the user table as an example, if the table has a field called status indicating whether the user is disabled. status is a bool value, true means the user status is normal, false means disabled.

Then there is a requirement that JQL can only check user information with normal user status, but disabled user information cannot be checked. Then the schema configuration is as follows, the value of the read node controlled by the table is no longer a simple true/false, but becomes an expression: "doc.status==true"

// schema of the user table
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": "doc.status==true", // 任何用户都可以读status字段的值为true的记录，其他记录不可读
    "create": false, // 禁止新增数据记录（admin权限用户不受限）
    "update": false, // 禁止更新数据（admin权限用户不受限）
    "delete": false // 禁止删除数据（admin权限用户不受限）
  },
  "properties": {
    "_id":{
    },
    "name":{
    },
    "pwd": {
      "bsonType": "string",
      "title": "密码",
      "permission": {
        "read": false, // 禁止读取 pwd 字段的数据（admin权限用户不受限）
        "write": false // 禁止写入 pwd 字段的数据（admin权限用户不受限）
      }
    },
    "status": {
      "bsonType": "bool",
      "title": "用户状态",
      "description": "true代表用户正常。false代表用户被禁用"
    }
  }
}

According to this configuration, if JQL queries all the data in the user table, it will report permission verification failure; if the JQL query declares that only the data whose status field is true is queried in the where condition of the JQL query, the query will be released.

Variables and operators for permission rules

In addition to the doc variables mentioned in the above examples, in fact, the permission rules of DB Schema support many variables and operators, which can meet various configuration requirements.

Global variables available within permission rules

variable name	description
auth.uid	用户id
auth.role	用户角色数组，参考uni-id 角色权限，注意admin为内置的角色，如果用户角色列表里包含admin则认为此用户有完全数据访问权限
auth.permission	用户权限数组，参考uni-id 角色权限
doc	The target data record in the database to match the record content/query condition
now	当前服务器时间戳（单位：毫秒），时间戳可以进行额外运算，如doc.publish_date > now - 60000表示publish_date在最近一分钟
action	已废弃，使用数据库触发器替代action云函数

Notice

• auth表示正在执行操作的用户对象
• auth.xxx均由uni-id提供，依赖于uni-id公共模块
• doc.xxx表示将要查询/修改/删除的每条数据（注意并不包括新增数据，新增数据应通过值域校验进行验证），如果将要访问的数据不满足permission规则将会拒绝执行
• uni-id的角色和权限，也即auth.role和auth.permission是不一样的概念。注意阅读uni-id 角色权限
• doc can be understood as the data to be accessed, so the doc variable cannot be used within the create permission. When creating, it is recommended to use forceDefaultValue or a custom validation function to implement range validation of inserted data.

Operators that can be used in permission rules

Operator	Description	Example	Example Explanation (Set Query)
==	equals	auth.uid == 'abc'	user id is abc
!=	Not equal to	auth.uid != null	User must be logged in
>	Greater than	doc.age>10	The age attribute of the target data is greater than 10
>=	Greater than or equal to	doc.age>=10	The age attribute of the target data is greater than or equal to 10
<	Less than	doc.age<10	The age attribute of the target data is less than 10
<=	Less than or equal to	doc.age<=10	The age attribute of the target data is less than or equal to 10
in	Exist in the array	doc.status in ['a','b']	The status of the target data is one of ['a','b'], all elements in the array must be of the same type
!	Not	!(doc.status in ['a','b'])	The status of the target data is not any of ['a','b'], all elements in the array must be of the same type
&&	and	auth.uid == 'abc' && doc.age>10	user id is abc and age attribute of target data is greater than 10
||	or	auth.uid == 'abc'||doc.age>10	User Id is abc or the age attribute of the target data is greater than 10

We continue to use the user table as an example. The current requirements are as follows. If a front-end user logs in, the user can modify his name field. At this point, you need to configure the permission of the name field in the schema as "write":"(doc._id == auth.uid)"

// schema of the user table
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": "doc.status==true", // 任何用户都可以读status字段的值为true的记录，其他记录不可读
    "create": false, // 禁止新增数据记录（admin权限用户不受限）
    "update": "'updateuser' in auth.permission", // 权限标记为updateuser的用户，和admin管理员，可以更新数据，其他人无权更新数据
    "delete": false // 禁止删除数据（admin权限用户不受限）
  },
  "properties": {
    "_id":{
	},
	"name":{
		"bsonType": "string",
		"title": "名称",
		"permission": {
		  "read": true, 
		  "write": "doc._id == auth.uid" // 允许登录的用户修改自己的name字段
		}
	},
    "pwd": {
      "bsonType": "string",
      "title": "密码",
      "permission": {
        "read": false, // 禁止读取 pwd 字段的数据（admin权限用户不受限）
        "write": false // 禁止写入 pwd 字段的数据（admin权限用户不受限）
      }
    },
	"status": {
		"bsonType": "bool",
		"title": "用户状态",
		"description": "true代表用户正常。false代表用户被禁用"
	}
  }
}

According to this configuration, if the front-end application is already logged in, and the logged-in user initiates a request to modify his name, the modification is allowed. Other requests to modify data are rejected.

Notice

要分清 数据权限permission 和 字段值域校验validator 的区别。

In the variable of the permission rule, there is only the data doc in the database, and there is no data to be stored in the database submitted by the front end. Therefore, if you want to verify the data stored in the database, you should verify it in the field value field validator, not in the permission.

If you want to obtain and judge other data than the target data record doc, you need to use the get method, see the next chapter.

forceDefaultValue belongs to the category of data verification and takes effect when data is written. However, if forceDefaultValue is configured as {"$env": "uid"}, user identity verification will also be performed, and users who are not logged in cannot insert data.

For example, when a new record is added to the news table, the permission requirement is "users who are not logged in cannot create news". In fact, there is no need to write auth.uid != null in the create permission of the news table. Just set the forceDefaultValue of the uid field of the news table to "$env": "uid", and configure the create permission to true. Naturally, users who are not logged in cannot create. Of course, you may need more complex permissions in actual use. Be careful when using true as the permission rule directly.

Note on the use of permission and role

To use the permission and role of uni-id in the schema, you first need to create a permission in uniCloud admin, then create a role and assign permissions to the role, and finally create a user and authorize the role.

In this way, after the user logs in, uniCloud will automatically analyze its permissions and roles, and the restrictions on permissions and roles written in the schema can also be mapped one-to-one for effective management.

Create permissions, roles and user authorization in admin, see also documentation

Database query get method in permission rules

The permission rule has a built-in doc variable, but it can only be used for the judgment of the data table to be operated. If you want to obtain the data of other tables for judgment, you need the get method.

In the permission rule, the data in the database is obtained according to the id through the get method. The get method receives a string as a parameter in the form of database.table name.record ID

For example, there is a forum that requires users to have more than 100 points before they can post. Then the create permission of the post table should be configured as:

// Use template string syntax to generate `database.tablename.record ID` form string
"create": get(`database.uni-id-users.${auth.uid}`).score > 100"

When using the get method, it should be noted that the parameter of the get method must be a unique value. For example, the get permission of the schema configuration is as follows:

// The meaning of this sentence is that the shop_id passed in the where condition of this query needs to meet the following conditions: the owner field of the record whose _id in the shop table is this shop_id is equal to the current user uid
"get(`database.shop.${doc.shop_id}`).owner == auth.uid"

The front-end js is as follows:

// In this condition, doc.shop_id can only be '123123'. You can obtain the record with _id of 123123 in the shop table through get(`database.shop.${doc.shop_id}`) to verify whether its owner is equal to the current user uid
db.collection('street').where("shop_id=='123123'").get()

// In this condition, doc.shop_id may be '123123' or '456456', `"get(`database.shop.${doc.shop_id}`).owner == auth.uid"` will directly return false Will not get shop table data for verification
db.collection('street').where("shop_id=='123123 || shop_id=='456456'").get()

权限错误排查步骤

非jql不会走权限校验，jql报了权限校验未通过从以下几点进行检查

• 连的是云端还是本地，如果是云端检查下schema有没有上传到云端
• 字段级有没有配置权限，有没有在客户端访问password字段
• 此次访问的数据是不是配置的权限对应的数据的子集

schema.ext.js触发器

schema.json是一个json方式的配置，配置的特点是简单易用，但无法编程。

当出现配置难以满足的需求，比如复杂的数据权限校验规则、复杂的字段值域校验规则，此时应当使用编程的方式来解决。

这就是 scheme.js。每个表都有一个schema.json和一个schema.ext.js（可选）。

在schema.ext.js里可以监听数据的增删改查，可自由做前置校验、前置数据加工或后置加工，可引用扩展库和公共模块。

因篇幅较多，请另见数据库schema.ext.js触发器

再次强调，schema.json和schema.ext.js的生效前提，均是JQL。使用传统MongoDB写法无法执行这些。

schema2code code generation system

There is a lot of information in DB Schema. In fact, with this information, the front-end will not need to develop its own form maintenance interface. uniCloud can automatically generate front-end pages for adding, modifying, list, and details, as well as lists, adding, and modifying on the admin side. , delete the full set of functions.

因内容较长，请另见文档schema2code