# CGP协议

## 数据库操作篇

传感器应用一般会从传感器终端产生（定时或实时）传感器数据，然后通过本协议把数据发送到CP7并保存到数据库或进行其他数据库相关的操作可以通过本协议进行操作，本协议支持Nosql数据库 （Mongodb）、Sql数据库（MsSql、MySql、PostgresSql)。

{% hint style="info" %}
如果使用Mongodb数据库还可以通过本协议让Mongodb成为一个图片或文件存储服务器，通过Mongodb集群可以组建高性能高可用的OSS系统。使用Mongodb的GridFS技术。
{% endhint %}

### Mongodb操作

#### 插入一条数据

```
{
"db_name":"DbPoxyTestDB",
"table_name":"test",
"op_name":"insert",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"value":{"datetime":"2018-10-12T10:10:12+08:00","number":12345.12345655889977,"string":"djkfjdkfj" }
}
```

#### 修改指定id记录内容

```
{
"db_name":"DbPoxyTestDB",
"table_name":"test",
"op_name":"update",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"filter_id":"5c106004536ad2a1ddfb0106",
"value":{"string":"333333333" }
}
```

#### 按条件更新信息

```
{
"db_name":"DbPoxyTestDB",
"table_name":"test",
"op_name":"update",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"filter":{"datetime":"2018-10-12T10:10:12+08:00"},
"value":{"string":"444444" }
}
```

#### 删除指定ID数据

```
{
"db_name":"DbPoxyTestDB",
"table_name":"test",
"op_name":"delete",
"token":"password",
"msg_id":2,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"filter_id":"5be3a553572c066aa453f8c6"
}
```

#### 通过查询条件(datatime字段等于指定值)删除指定数据

```
{
"db_name":"DbPoxyTestDB",
"table_name":"test",
"op_name":"delete",
"token":"password",
"msg_id":3,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"filter":{"datetime":"2018-10-12T10:10:12+08:00"}
}
```

#### 按条件(指定时间范围)查询数据 

{% hint style="info" %}

#### filter\_pipe值支持Mongodb原生的Aggregate聚合操作，祥细说明说参阅：<https://docs.mongodb.com/manual/core/aggregation-pipeline/index.html>

{% endhint %}

```
{
"db_name":"DbPoxyTestDB",
"table_name":"test",
"op_name":"query",
"token":"password",
"msg_id":4,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"filter_pipe":[
    {
        "$match": {
            "$and": [
                { "datetime": { "$gte": "2018-10-16T10:10:12+08:00" } },
                { "datetime": { "$lte": "2018-10-18T10:10:12+08:00" } }
            ]
        }
    },
    {"$skip":1},
    {"$limit":2}
]}
```

#### 按条件(指定Id)查询数据

```
{
"db_name":"DbPoxyTestDB",
"table_name":"test",
"op_name":"query",
"token":"password",
"msg_id":5,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"filter_id":"5b03e04250d8e8bc694a3b2f"
}
```

#### 事务（事务执行插入，更新，删除操作）

```
{
	"db_name": "DbPoxyTestDB",
	"op_name": "work",
	"token": "password",
	"msg_id": 1,
	"ref_topic": "dbpoxy/mongodb/result",
	"ref_qos": 0,
	"mongo_works": [{
			"table_name": "users",
			"op_name": "insert",
			"work": {
				"datetime": "2018-10-12T10:10:12+08:00",
				"number": 12345.12345655889977,
				"string": "djkfjdkfj"
			},
			"id_alias": "insertid"
		},
		{
			"table_name": "users",
			"op_name": "update",
			"work": {
				"filter": {
					"_id": "insertid"
				},
				"value": {
					"$set": {
						"string": "333333333"
					}
				}
			}
		},
        {
			"table_name": "users",
			"op_name": "query",
			"work": {
				"filter": {
					"_id": "insertid"
				}
			}
		},
		{
			"table_name": "users",
			"op_name": "delete",
			"work": {
				"filter": {
					"_id": "insertid"
				}
			}
		}
	]
}
```

### SQL数据库操作（mysql mssql pgsql等）

DbPoxy配置文件启用sql数据库连接

```
#postgres
DatabaseType: postgres #mssql mysql postgres
DatabaseConnectionString: host=192.168.100.187 port=13246 user=postgres dbname=ldh sslmode=disable password=123456
```

#### 插入数据

```
{
"table_name":"users",
"op_name":"insert",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"sql_exec":"INSERT INTO users(age, name, num) VALUES (100, 'ccc', 77)"
}
```

#### 删除数据

```
{
"table_name":"users",
"op_name":"delete",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"sql_exec":"DELETE FROM users WHERE id=3;"
}
```

#### 更新数据

```
{
"table_name":"users",
"op_name":"update",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"sql_exec":"UPDATE users SET age=8, name='coolpy7'  WHERE id=2;"
}
```

查询数据

```
{
"table_name":"users",
"op_name":"query",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"sql_query":"SELECT * FROM users;"
}
```

事务插入数据，通过id\_alias记录插入成功后新数据结点ID

```
{
"table_name":"users",
"op_name":"work",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"sql_works":[
{"table_name":"users", "work":"INSERT INTO users(age, name, num) VALUES (100, 'ccc', 77)","id_alias":"insertid"},
{"table_name":"users", "work":"UPDATE users SET age=8, name='coolpy7'  WHERE id=insertid"},
{"table_name":"users", "work":"DELETE FROM users WHERE id=insertid"}
]}
```

### OSS( Object Storage Service)对象存储服务

此功能实现像阿里云OSS对象存储系统类似功能，可以支持直接保存文件或图片等流式文件，以流式格式保存到Mongodb的Gridfs特殊的对象存储数据结构，提供了高性能和可扩展的文件存储服务功能。

{% hint style="info" %}
上传及删除文件示例开源地址：<https://github.com/Coolpy7/dbpoxyclient>
{% endhint %}

DbPoxy配置文件启用OSS连接，基于Mongodb

```
#oss
DatabaseType: oss-gridfs
DatabaseConnectionString: mongodb://localhost:27017
```

上传文件

```
{
"db_name":"gridfs",
"table_name":"fs",
"op_name":"insert",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"oss_file_name":文件名,
"oss_file_base64":以base64形式的文件流
// "oss_file_hex":以hex形式的文件流、此操作符不能与oss_file_base64同时使用
}
```

删除文件

```
{
"db_name":"gridfs",
"table_name":"fs",
"op_name":"delete",
"token":"password",
"msg_id":1,
"ref_topic":"dbpoxy/mongodb/result",
"ref_qos":0,
"filter_id":"5b0a610853c613cd1c4d9840"
}
```