def reset():
cursor.execute(
"DELETE FROM levelup.messages"
)
cursor.execute(
"ALTER TABLE levelup.messages AUTO_INCREMENT=1"
)
def add_message(player_name, message):
cursor.execute(
"INSERT INTO levelup.messages ("
"SENDER, MESSAGE"
") VALUES ("
"%s, %s"
")", (player_name, message)
)
def Aurora_InsertNote(deviceid, initials, text):
# 0 = deviceid
# 1 = datetime
# 2 = initials
# 3 = text
cmd = """\
EXEC Aurora_InsertNote null, '{0}', 'I', 'Devices', 'Config Device', null, '{1}', '{2}', true
""".format(deviceid, initials, text)
cursor.execute(cmd)
def GetDeviceByDeviceId(deviceid):
cmd = """\
EXEC GetDeviceByDeviceId {0}
""".format(deviceid)
cursor.execute(cmd)
return_value = cursor.fetchall()
return return_value
def GetLastDeviceIdByRange(bottom, top):
cmd = """\
DECLARE @DeviceId int;
EXEC GetLastDeviceIdByRange {0}, {1}, @DeviceId out;
SELECT @DeviceId AS return_value
""".format(bottom, top)
cursor.execute(cmd)
return_value = cursor.fetchval()
return return_value
def get(self):
cursor.execute(self.Q1)
posts = []
for element in cursor:
posts.append({
"postId": element[0],
"accountId": element[1],
"timestamp": element[2],
"madeWith": element[3],
"post_image_url": f"{DOMAIN}/file/image/{urllib.parse.quote(element[4])}",
# This would be get url link in the future
"title": element[5],
"Captions": element[6],
"likes": element[7],
"isPrivate": element[8]
})
return {"Posts": posts}
def UpdateDeviceProvisioned(datenow, deviceid, apn, server, port, script,
logFileName):
_LogFileName = logFileName
"""
0 = deviceid
1 = apn
2 = server
3 = port
4 = firmware
5 = package
6 = script
7 = _LogFileName
"""
cmd = """\
EXEC UpdateDeviceProvisioned {0}, '{1}', '{2}', {3}, '{4}', '{5}', '{6}', '{7}'
""".format(deviceid, apn, server, port, getFirmware(), getPackage(),
script, _LogFileName)
cursor.execute(cmd)
def get(self):
cursor.execute(self.Q1)
trending = []
others = []
count = 0
for element in cursor:
trending.append({
"postId": element[0],
"accountId": element[1],
"timestamp": element[2],
"madeWith": element[3],
"post_image_url": f"{DOMAIN}/file/image/{urllib.parse.quote(element[4])}",
# This would be get url link in the future
"title": element[5],
"Captions": element[6],
"likes": element[7],
"isPrivate": element[8]
})
count += 1
if count == 6:
break
cursor.execute(self.Q1)
for element in cursor:
others.append({
"postId": element[0],
"accountId": element[1],
"timestamp": element[2],
"madeWith": element[3],
"post_image_url": f"{DOMAIN}/file/image/{urllib.parse.quote(element[4])}",
# This would be get url link in the future
"title": element[5],
"Captions": element[6],
"likes": element[7],
"isPrivate": element[8]
})
return {"Others": others,
"Trending": trending}
def get(self, id):
"""
:param id: uesr id that referes to the database
:return: Json object that has a list of posts
"""
cursor.execute(self.get_sql_command(id))
posts = []
for element in cursor:
if element[8] == 0:
posts.append({
"postId": element[0],
"accountId": element[1],
"timestamp": element[2],
"madeWith": element[3],
"post_image_url": f"{DOMAIN}/file/image/{urllib.parse.quote(element[4])}",
# This would be get url link in the future
"title": element[5],
"Captions": element[6],
"likes": element[7],
"isPrivate": element[8]
})
return {"Posts": posts}
from constants import cursor
import mysql.connector
Q1 = "CREATE DATABASE EnlightenmentApi" # Create a database
Q2 = "CREATE TABLE Posts (postId INT PRIMARY KEY AUTO_INCREMENT, accountId INT, timestamp INT, madeWith VARCHAR(100), fileLocation VARCHAR(300), title VARCHAR(300), captions VARCHAR(500), likes INT DEFAULT 0, isPrivate BOOL)" # Table one
Q3 = "CREATE TABLE Tags (postId INT, FOREIGN KEY(postId) REFERENCES Posts(postId), tag1 VARCHAR(100), tag2 VARCHAR(100), tag3 VARCHAR(100 ))" # Table two
Q4 = "CREATE TABLE Accounts (accountId INT PRIMARY KEY AUTO_INCREMENT, accountName VARCHAR(100), accountPassword VARCHAR(100), posts INT DEFAULT 0)"
Q5 = "DROP TABLE Accounts"
cursor.execute(Q2)
cursor.execute(Q3)
cursor.execute(Q4)
cursor.execute("DESCRIBE Posts")
for x in cursor:
print(x)
cursor.execute("DESCRIBE Tags")
for x in cursor:
print(x)
def get_positions():
cursor.execute("SELECT POSITION FROM levelup.players")
return [player[0] for player in cursor.fetchall()]
def get_position(player_name):
cursor.execute("SELECT POSITION FROM levelup.players WHERE PLAYER_NAME=%s",
(player_name, ))
return cursor.fetchall()[0][0]
def get_names():
cursor.execute("SELECT PLAYER_NAME FROM levelup.players")
return [player[0] for player in cursor.fetchall()]
def InsertInitialDevice(deviceid, imei, iccid):
cmd = """\
EXEC InsertInitialDevice {0}, '{1}', '{2}', '{3}'
""".format(deviceid, AccountID, imei, iccid)
cursor.execute(cmd)
def InsertInitialAsset(deviceid):
cmd = """\
EXEC InsertInitialAsset '{0}', '{1}', {2}, {3}
""".format(AccountID, GroupId, deviceid, getThreshold())
cursor.execute(cmd)
def get_gamestate():
cursor.execute("SELECT * FROM levelup.gamestate")
return json.loads(cursor.fetchall()[0][0])
from flask import Flask, request
from flask_cors import CORS
from service import chatService
from constants import cursor, cnx
app = Flask(__name__)
CORS(app)
# messages table
cursor.execute("CREATE TABLE IF NOT EXISTS MESSAGES ("
"ID INT AUTO_INCREMENT PRIMARY KEY, "
"SENDER VARCHAR(20) NOT NULL, "
"MESSAGE VARCHAR(200) NOT NULL"
")")
cnx.commit()
# players table
cursor.execute("CREATE TABLE IF NOT EXISTS PLAYERS ("
"POSITION VARCHAR(1) NOT NULL, "
"PLAYER_NAME VARCHAR(20) NOT NULL"
")")
cnx.commit()
# gamestate table
cursor.execute("CREATE TABLE IF NOT EXISTS GAMESTATE ("
"GAMESTATE VARCHAR(10000) NOT NULL"
")")
cnx.commit()
def _clear():
cursor.execute("DELETE FROM levelup.gamestate")
def add_player(position, player_name):
cursor.execute(
"INSERT INTO levelup.players (POSITION, PLAYER_NAME) VALUES (%s, %s)",
(position, player_name))
def set_gamestate(gamestate):
_clear()
cursor.execute("INSERT INTO levelup.gamestate (GAMESTATE) VALUES (%s)",
(json.dumps(gamestate), ))
def reset():
cursor.execute("DELETE FROM levelup.players")
def get_largest_id():
cursor.execute(
"SELECT MAX(ID) FROM levelup.messages"
)
id = cursor.fetchall()[0][0]
return int(id) if id is not None else 0
def has_host():
cursor.execute("SELECT COUNT(*) FROM levelup.players WHERE POSITION='N'")
return cursor.fetchall()[0][0] == 1
def get_next_messages(message_id):
cursor.execute(
"SELECT SENDER, MESSAGE FROM levelup.messages WHERE id>%s ORDER BY ID ASC",
(message_id, )
)
return cursor.fetchall()
def get_player_name(position):
cursor.execute("SELECT PLAYER_NAME FROM levelup.players WHERE POSITION=%s",
(position, ))
return cursor.fetchall()[0][0]
