This blog will complete the design of the database of the intelligent mirror based on the Dragonboard 410c based on the overall design of the intelligent mirror design. First, we can know that the user needs to keep the user information on the smart mirror. Keep the message information. Therefore, it is basically possible to construct a user table userInfo and a message table pushInfo to manage the data of the entire smart mirror. It is convenient to call and store related data. The specific database design scheme is as follows: :
In the above two tables, the userInfo table is used to store user information in the system, including ownerID, user ID, Name, user name, password, user password, MD5 password summary information, email, user mailbox, phone user phone, haarValue, user facial feature value, pushInfo table is a push message table, all push messages in the system are saved in the table, where infoID is the ID of the message, ownerID is the ID of the owner of the message, indicating that the message is Which user belongs to, the system will then push the message to the user according to this value, pushID, pusher, indicating which user is pushed by this user, the system identifies the user who pushes the message according to the value, infoType, message type The value is 0 or 1. When the bit 0 is 0, it means an ordinary text message. Fill in the two items of infoSubject and infoContent. When it is 1, it is a video or audio message. At this time, the video file path will be saved to the filePath field. , pushTIme, indicates the time of the push, infoValidityTIme indicates the validity period of the message, and isto indicates that the message is No, if the value is 1 to indicate that the message is topped, the message will be pushed first, viewWeight indicates the display weight of the message, and the displayed message weight will be reduced to 0 and no longer displayed.
Based on the above design, this article uses the Python sqlite3 interface to complete the code to create the database, the specific code is as follows:
Import sqlite3
Import os.path
Import random
Import TIme
#MagicMirrorDB = sqlite3.connect("./MaigicMirrorDB.db")
#MagicMirrorDB.execute("")
#create userInfo
Class MagicMirrorDB:
Def __init__(self, dbPath):
self.DB=sqlite3.connect(dbPath)
Self.cu=self.DB.cursor()
self.DB.execute("")
#self.initTable()
#self.insertTestData(5,20)
Def initTable(self):
Try:
#create userInfo table
self.DB.execute("create table userInfo(userID integer primary key autoincrement,\
Name nvarchar(50),\
Password nvarchar(20),\
Email nvarchar(50),\
Phone nvarchar(50),\
haarValue nvarchar(50),\
avatarImg nvarchar(50))"
)
#create pushInfo table
#********************pushInfo table**********************#
# infoType 0 text info, 1 video info
#
self.DB.execute("create table pushInfo(infoID integer primary key autoincrement,\
ownerID integer,\
pushID integer,\
infoType integer,\
infoSubject nvarchar(50),\
infoContent nvarchar(100),\
filePath nvarchar(1000),\
pushTIme float,\
infoValidityTime float,\
isTop integer,\
viewWeight integer\
)")
# self.DB.execute("create table systemStatus(statusName nvarchar(30),stataryValue integer,statusParam integer ,statusDiscirption nvarchar(50))")
Except:
Print("table is already create")
In the above code, we can complete the initialization of the database by building the class in the main function, creating a system database, as follows:
If __name__=="__main__":
Import sys
MMDB=MagicMirrorDB("./MagicMirrorDB.db")
Print(MMDB.getUserAvatarImg(1))
Result=MMDB.getUserVideoMessageList(1)
Print(result)
Result2=MMDB.getTextMessageList(1)
Print(result2)
Sys.exit(0)
In this way, before designing other modules, we can run the script to complete the creation of the database. After creation, you can check whether the database design is correct by executing the sqlite3 command.
Also for the convenience of testing, a function for automatically generating test data is constructed here, as follows. By executing the function, the test information can be quickly generated to fill the database, and the data can be used for testing in the subsequent design of the module.
Def insertTestData(self,userInfo_n,pushInfo_n):
For userID in range(1,userInfo_n):
Format="test%u"
Values=(userID)
userName=format % values
Format="password_test%u"
Password=format % values
Format="email_test%uqq.com"
userEmail=format % values
Format="phone_test%u"
userPhone=format % values
Format="haarValue_test%u"
haarValues=format % values
Avatar=(random.randint(1,8))
Format="./avatarImg/headImg/ali1/%u.gif"
avatarImg=format % avatar
Format="insert into userInfo values(%u,'%s','%s','%s','%s','%s','%s')"
Values=(userID,userName,password,userEmail,userPhone,haarValues,avatarImg)
insertSQL=format % values
#print(insertSQL)
Self.cu.execute(insertSQL)
For infoID in range(1,pushInfo_n):
ownerID=random.randint(1,userInfo_n)
pushID=random.randint(1,userInfo_n)
infoType=random.randint(0,1)
isTop=random.randint(0,1)
viewWeight=random.randint(1,8)
Format = "infoSubject_pID:%u_wID:%u"
Values=(pushID,ownerID)
infoSubject=format % values
Format = "infoContent_pID:%u_wID:%u"
Values=(pushID,ownerID)
infoContent=format % values
filePath=""
If infoType==1:
Format="./messageFile/ownerID%u/pushID%u_%u.mp4"
Values=(ownerID, pushID, infoID)
filePath=format % values
Else:
filePath="None"
pushTime=time.time()
Format="insert into pushInfo values(%u,%u,%u,%u,'%s','%s','%s',%f,1.0,%u,%u)"
Values=(infoID, ownerID, pushID, infoType, infoSubject, infoContent, filePath, pushTime, isTop, viewWeight)
insertSQL=format % values
#print(insertSQL)
self.DB.execute(insertSQL)
The above is the design of the database of the entire smart mirror, and the design of other functional modules will be further completed and improved on the basis of the database.
Tsvape is the Chinese supplier of GEEK BAR. We do wholesale business,we sent them from China. Geekbar is an senior e-cigarette brand. It has a high reputation in the world. You can order it with confidence.
Geek Bar Vape,Vape Qd42-V2 4000 Puffs ,Elr Iget Hqd Elrbar ,Elr Iget High-Powe
TSVAPE Wholesale/OEM/ODM , https://www.tsecigarette.com