iot-platform-rust-lite/data_init.py

import sqlite3
# -----
# 创建数据库
curs = (conn:=sqlite3.connect('db.sqlite')).cursor()
# --------
# 表创建
#
# 创建用户表
curs.execute('''
create table if not exists user (
id integer PRIMARY KEY,
uname text not null,
passwd text not null,
nickname text not null,
headimgurl text not null default '',
openid text not null,
sex Integer not null default 0,
token text not null default '',
createtime timestamp not null default current_timestamp,
lastlogin timestamp,
isdelete integer not null default 0
)
''').fetchall()
# --------
# 设备与区域有映射关系，其中设备最多映射两个区域，区域之间存在上下级关系
#
# 创建设备表
curs.execute('''
create table if not exists device (
id integer PRIMARY KEY,
name text not null,
typo text not null,
area integer not null default 0, -- 0:未分配到区域 其他:所在区域编号
isdelete integer not null default 0
)
''').fetchall()
# 创建区域表
curs.execute('''
create table if not exists area (
id integer PRIMARY KEY,
name text not null,
sup integer not null, -- 0:无上级区域 其他:上级区域编号
isdelete integer not null default 0
)
''').fetchall()
# 用户-设备映射表
curs.execute('''
create table if not exists map_user_device (
id integer PRIMARY KEY,
did integer not null,
uid integer not null,
unique(did,uid)
)
''').fetchall()
# 区域-设备映射表
curs.execute('''
create table if not exists map_area_device (
id integer PRIMARY KEY,
did integer not null,
aid integer not null,
unique(did,aid)
)
''').fetchall()
# 用户-区域映射表
curs.execute('''
create table if not exists map_user_area (
id integer PRIMARY KEY,
uid integer not null,
aid integer not null,
unique(uid,aid)
)
''').fetchall()
curs.execute('''select * from user''').fetchall()
# --------
# 流程处理：分享与转移设备操作
# 创建设备分享表
curs.execute('''
create table if not exists flow_task_share (
id integer PRIMARY KEY,
did integer not null,
typo integer not null,
ticket integer not null,  -- 不需要uid, 因为分享不限用户
createtime timestamp not null default current_timestamp,
isdelete integer not null default 0, -- 0:未处理 1:处理完毕 2:超时未处理
unique(ticket)
)
''').fetchall()

conn.commit()
# -----
# 表内容初始化
#
# 添加用户
curs.execute('''
insert into user (uname,passwd,nickname,openid) values 
('root', 'e10adc3949ba59abbe56e057f20f883e', 'admin', 'XD'),
('admin', 'e10adc3949ba59abbe56e057f20f883e', 'test', 'LD')
''').fetchall()
conn.commit()

curs.execute('''
select * from flow_task_share
''').fetchall()

# 添加测试设备、区域、映射关系
curs.execute('''
insert into device (name,typo,area) values 
('设备1', 'a',2),
('设备2', 'a',4),
('设备3', 'a',0)
''').fetchall()
curs.execute('''
insert into area (name,sup) values 
('区域1', 2),
('区域2', 0),
('区域3', 4),
('区域4', 0)
''').fetchall()
curs.execute('''
insert into map_user_device (uid,did) values 
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 2),
(2, 3),
(2, 4)
''').fetchall()
curs.execute('''
insert into map_user_area (uid,aid) values 
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 2),
(2, 3),
(2, 4)
''').fetchall()