Coolpy General Protocol物聯網通用協議

物聯網應用中除了通信層技術提供高併發高連接通信技術外，還需要一種可以跨技術語言的通信控制協議。例如某個傳感器要上傳一次原信息到物聯網雲平臺中記錄，或者針對某個終端設備（燈、門等）發送一個控制開關指令操作。CGP協議全稱：Coolpy General Protocol (中文: Coolpy通用協議)

數據庫操作篇

傳感器應用一般會從傳感器終端產生（定時或實時）傳感器數據，然後通過本協議把數據發送到CP7並保存到數據庫或進行其他數據庫相關的操作可以通過本協議進行操作，本協議支持Nosql數據庫 （Mongodb）、Sql數據庫（MsSql、MySql、PostgresSql)。

如果使用Mongodb數據庫還可以通過本協議讓Mongodb成為一個圖片或文件存儲服務器，通過Mongodb集群可以組建高性能高可用的OSS系統。使用Mongodb的GridFS技術。

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"}
}

按條件(指定時間範圍)查詢數據

filter_pipe值支持Mongodb原生的Aggregate聚合操作，祥細說明說參閱：https://docs.mongodb.com/manual/core/aggregation-pipeline/index.html

{
"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特殊的對象存儲數據結構，提供了高性能和可擴展的文件存儲服務功能。

上傳及刪除文件示例開源地址：https://github.com/Coolpy7/dbpoxyclient

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"
}