def move(self, moveto):
c = conn.cursor()
c.execute(
"""
UPDATE
`data`
SET
`sort` = %s,
`updated` = FROM_UNIXTIME(%s)
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
moveto,
int(time.time()),
self.id,
OWNER_ID
)
)
conn.commit()
c.close()
parent = item()
parent.id = self.parentid
parent.sort_children()
def sort_children(self):
# Load all children in order
children = loadChildren(self.id)
c = conn.cursor()
# Loop through looking for duplicates/spaces
lastsort = 0
for child in children:
newsort = lastsort + 1
if child.sort != newsort:
# Update to be correct sort
c.execute(
"""
UPDATE
`data`
SET
`sort` = %s,
`updated` = FROM_UNIXTIME(%s)
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
newsort,
int(time.time()),
child.id,
OWNER_ID
)
)
conn.commit()
lastsort = newsort
c.close()
def api_teams_id_get(id):
cursor = conn.cursor()
cursor.execute(
"""
SELECT
id,
name,
description
FROM teams
WHERE id=%s;
""", (id, ))
team = cursor.fetchone()
conn.commit()
cursor = conn.cursor()
cursor.execute(
"""
SELECT
pokemon_id,
member_level
FROM team_members
WHERE teams_id=%s;
""", (id, ))
members = cursor.fetchall()
conn.commit()
formatresults = lambda m: {"pokemon_id": m[0], "level": m[1]}
members_to_return = list(map(formatresults, members))
team_to_return = {
"id": team[0],
"name": team[1],
"description": team[2],
"members": members_to_return
}
return jsonify(team_to_return), 200
def update_text(self, text):
self.text = text
c = conn.cursor()
c.execute(
"""
UPDATE
`data`
SET
`text` = %s,
`updated` = FROM_UNIXTIME(%s)
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
text,
int(time.time()),
self.id,
OWNER_ID
)
)
conn.commit()
c.close()
def api_teams_id_put(id):
cursor = conn.cursor()
updated_team = json.loads(request.data)
cursor.execute(
"""
UPDATE teams
SET
name = %s,
description = %s
WHERE id = %s;
""", (updated_team['name'], updated_team['description'], id))
cursor.execute(
"""
DELETE FROM ONLY team_members
WHERE teams_id = %s;
""", (id, ))
for pokemon in updated_team['members']:
cursor.execute(
"""
INSERT INTO
team_members (teams_id, pokemon_id, member_level)
VALUES
(%s, %s, %s)
""", (id, pokemon['pokemon_id'], pokemon['level']))
conn.commit()
return jsonify(updated_team), 200
def api_teams_id_delete(id):
cursor = conn.cursor()
cursor.execute(
"""
DELETE FROM teams *
WHERE id=%s;
""", (id, ))
conn.commit()
return "ok!", 204
def reparent(self, newparent):
oldparent = self.parentid
self.parentid = newparent
c = conn.cursor()
c.execute(
"""
UPDATE
`data`
SET
`parentid` = %s
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
newparent,
self.id,
OWNER_ID
)
)
newsort = self.get_max_sort()
# Update the id
c.execute(
"""
UPDATE
`data`
SET
`sort` = %s
WHERE
`uid` = %s
AND `ownerid` = %s
""",
(
newsort,
self.uid,
OWNER_ID
)
)
conn.commit()
c.close()
# Resort old parent
p = item()
p.id = oldparent
p.sort_children()
# Resort new parent
p = item()
p.id = self.parentid
p.sort_children()
def api_teams_id_patch(id):
cursor = conn.cursor()
patched_team = json.loads(request.data)
cursor.execute(
"""
SELECT
id,
name,
description
FROM teams
WHERE id = %s;
""", (id, ))
teamdata = cursor.fetchone()
#update name and description
updated_team['name'] = patched_team['name'] if (
'name' in patched_team.keys()) else teamdata[1]
updated_team['description'] = patched_team['description'] if (
'description' in patched_team.keys()) else teamdata[2]
cursor.execute(
"""
UPDATE teams
SET
name = %s,
description = %s
WHERE id = %s;
""", (updated_team['name'], updated_team['description'], id))
#update team members
if 'members' in patched_team.keys():
cursor.execute(
"""
DELETE FROM ONLY team_members
WHERE teams_id = %s;
""", (id, ))
for pokemon in patched_team['members']:
cursor.execute(
"""
INSERT INTO
team_members (teams_id, pokemon_id, member_level)
VALUES
(%s, %s, %s)
""", (id, pokemon['pokemon_id'], pokemon['level']))
updated_team['members'] = patched_team['members']
else:
updated_team['members'] = cursor.execute(
"""
SELECT pokemon_id, member_level
FROM team_members
WHERE teams_id = %s;""", (id, ))
conn.commit()
return jsonify(updated_team), 200
def api_pokemon_id_get(id):
cursor = conn.cursor()
cursor.execute(
"""
SELECT
id,
name,
description,
image_url,
type_1,
type_2
FROM pokemon
WHERE id = %s;
""", (id, ))
pokemon = cursor.fetchone()
conn.commit()
cursor = conn.cursor()
cursor.execute(
"""
SELECT
evolution_id,
method,
level,
evolves_to
FROM evolutions
WHERE pokemon_id=%s;
""", (id, ))
evolutions = cursor.fetchall()
conn.commit()
formatresults = lambda e: {
"id": e[0],
"method": e[1],
"level": e[2],
"to": e[3]
}
evolutions_to_return = list(map(formatresults, evolutions))
pokemon_to_return = {
"id": pokemon[0],
"name": pokemon[1],
"description": pokemon[2],
"image_url": pokemon[3],
"types": [pokemon[4], pokemon[5]],
"evolutions": evolutions_to_return
}
return jsonify(pokemon_to_return), 200
def set_archived(self):
c = conn.cursor()
c.execute(
"""
UPDATE
`data`
SET
`archive` = 1
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
self.id,
OWNER_ID
)
)
conn.commit()
c.close()
def api_teams_id_post():
cursor = conn.cursor()
new_team = json.loads(request.data)
cursor.execute(
"""
INSERT INTO
teams (name, description)
VALUES
(%s, %s)
RETURNING id
""", (new_team['name'], new_team['description']))
teams_id = cursor.fetchone()[0]
for pokemon in new_team['members']:
cursor.execute(
"""
INSERT INTO
team_members (teams_id, pokemon_id, member_level)
VALUES
(%s, %s, %s)
""", (teams_id, pokemon['pokemon_id'], pokemon['level']))
conn.commit()
return ("OK!"), 201
def api_pokemon_get():
query = request.args.get("search", "")
cursor = conn.cursor()
if len(query) > 0:
cursor.execute(
"""
SELECT
id,
name,
description,
image_url,
type_1,
type_2
FROM pokemon
WHERE name ILIKE %s;
""", ('%' + query + '%', ))
else:
cursor.execute("""
SELECT
id,
name,
description,
image_url,
type_1,
type_2
FROM pokemon;
""")
pokemon = cursor.fetchall()
conn.commit()
#Not returning 'description' column because it's never used from this call
formatresults = lambda p: {
'id': p[0],
'name': p[1],
'image_url': p[3],
'types': [p[4], p[5]]
}
pokemon_to_return = list(map(formatresults, pokemon))
return jsonify(pokemon_to_return), 200
def get_max_sort(self):
c = conn.cursor()
c.execute(
"""
SELECT
MAX(`sort`) + 1 AS `sort`
FROM
`data`
WHERE
`parentid` = %s
AND `ownerid` = %s
""",
(
self.parentid,
OWNER_ID
)
)
result = c.fetchone()
conn.commit()
c.close()
return result['sort']
def save(self, parent):
c = conn.cursor()
c.execute(
"""
INSERT INTO
`data`
(
`text`,
`created`,
`updated`,
`parentid`,
`ownerid`,
`sort`
)
VALUES
(
%s,
FROM_UNIXTIME(%s),
FROM_UNIXTIME(%s),
%s,
%s,
0
)
""",
(
self.text,
int(time.time()),
int(time.time()),
parent,
OWNER_ID
)
)
self.uid = c.lastrowid
self.id = self.uid
self.parentid = parent
newsort = self.get_max_sort()
# Update the id
c.execute(
"""
UPDATE
`data`
SET
`id` = %s,
`sort` = %s
WHERE
`uid` = %s
AND `ownerid` = %s
""",
(
self.id,
newsort,
self.uid,
OWNER_ID
)
)
conn.commit()
c.close()
# Resort parent
p = item()
p.id = parent
p.sort_children()
def migrate():
with open('data/database.json') as f:
# Load the JSON file
JSON = json.load(f)
# Run the schema file to set up the database on app start up
with conn as connection:
cursor = connection.cursor()
cursor.execute(open("data/schema.sql", "r").read())
connection.commit()
#pokemon and evolutions data
for pokemon in JSON['pokemon']:
typetwo = pokemon['types'][1] if (
len(pokemon['types']) > 1) else None
db.execute(
"""
INSERT INTO
pokemon (id, name, description, image_url, type_1, type_2)
VALUES
(%s, %s, %s, %s, %s, %s)
""", (pokemon['id'], pokemon['name'], pokemon['description'],
pokemon['image_url'], pokemon['types'][0], typetwo))
conn.commit()
for pokemon in JSON['pokemon']:
for evolution in pokemon['evolutions']:
evolution_id = evolution['id'] if (
'id' in evolution.keys()) else None
level = evolution['level'] if ('level'
in evolution.keys()) else None
db.execute(
"""
INSERT INTO
evolutions (pokemon_id, evolution_id, method, level, evolves_to)
VALUES
(%s, %s, %s, %s, %s)
""", (pokemon['id'], evolution_id, evolution['method'], level,
evolution['to']))
conn.commit()
# teams and team_members data
for team in JSON['teams']:
db.execute(
"""
INSERT INTO
teams (name, description)
VALUES
(%s, %s)
RETURNING
id
""", (team['name'], team['description']))
teams_id = db.fetchone()[0]
for pokemon in team['members']:
db.execute(
"""
INSERT INTO
team_members (teams_id, pokemon_id, member_level)
VALUES
(%s, %s, %s)
""", (teams_id, pokemon['pokemon_id'], pokemon['level']))
conn.commit()
return "Ok!", 200