def smart_album_images(cls, pkid, return_objects=True):
sql = "select rules from album where album.pkid = %s"
with utils.DbCursor() as crs:
crs.execute(sql, pkid)
rules = json.loads(crs.fetchone()["rules"])
filters = []
joins = []
mthds = {
"keywords": cls._filter_keywords,
"name": cls._filter_name,
"orientation": cls._filter_orientation,
"created": cls._filter_created,
"year": cls._filter_year,
"album": cls._filter_album,
}
for rule_dict in rules:
field = next(iter(rule_dict))
compval = rule_dict[field]
comp = next(iter(compval))
val = compval[comp]
mthd = mthds[field]
mthd(comp.lower(), val, filters, joins)
where_clause = " AND ".join(filters)
join_clause = " ".join(joins)
sql = (f"select image.* from image {join_clause} "
f"{'where' if where_clause else ''} {where_clause}")
with utils.DbCursor() as crs:
crs.execute(sql)
recs = crs.fetchall()
if return_objects:
return Image.from_recs(recs)
return recs
def set_album():
album_name = request.form.get("album_name")
if not album_name:
abort(400, "No value for 'album_name' received")
image_name = request.form.get("image_name")
if not image_name:
abort(400, "No value for 'image_name' received")
with utils.DbCursor() as crs:
# Get the image
sql = "select pkid, orientation from image where name = %s"
crs.execute(sql, (image_name, ))
image = crs.fetchone()
if not image:
abort(404, "Image %s not found" % image_name)
image_id = image["pkid"]
orientation = image["orientation"]
# Get the album (if it exists)
sql = "select pkid from album where name = %s"
crs.execute(sql, (album_name, ))
album = crs.fetchone()
if album:
album_id = album["pkid"]
else:
# Create it
album_id = utils.gen_uuid()
sql = """insert into album (pkid, name, orientation)
values (%s, %s, %s); """
vals = (album_id, album_name, orientation)
crs.execute(sql, vals)
# Now add the image to the album
sql = "insert into album_image (album_id, image_id) values (%s, %s) ;"
crs.execute(sql, (album_id, image_id))
return "Success!"
def add_image(self, img):
img_obj = Image.get(img)
utils.debugout("Adding image to", self)
sql = "insert into album_image (album_id, image_id) values (%s, %s);"
with utils.DbCursor() as crs:
crs.execute(sql, (self.pkid, img_obj.pkid))
self._allocate_to_sub_albums(img_obj)
def wrapped(*args, **kwargs):
ok = False
token = session.get("token")
LOG.debug("TOKEN: %s" % token)
if token:
rec = None
with utils.DbCursor() as crs:
try:
LOG.debug(
"Running query SELECT expires FROM login WHERE token = %s;"
% token)
crs.execute("SELECT expires FROM login WHERE token = %s;",
token)
rec = crs.fetchone()
LOG.debug("DB record for token: %s", rec)
except Exception as e:
LOG.error("Exception type: {}".format(type(e)))
LOG.error("DB Failed: %s", e)
if rec:
LOG.debug("EXPIRES: %s", rec["expires"])
LOG.debug("NOW: %s", dt.datetime.utcnow())
ok = rec["expires"] > dt.datetime.utcnow()
LOG.debug("OK: %s", ok)
if not ok:
LOG.debug("Login failed for token: %s", token)
session["original_url"] = request.url
return redirect("/login_form")
LOG.debug("Credentials OK")
return fnc(*args, **kwargs)
def POST_login():
form = request.form
username = form.get("username")
pw = form.get("pw")
hashed = _hash_pw(pw)
with utils.DbCursor() as crs:
crs.execute(
"""
SELECT pkid, superuser FROM user
WHERE name = %s and pw = %s""", (username, hashed))
rec = crs.fetchone()
if not rec:
flash("Login failed.")
return redirect("/login_form")
user_id = rec["pkid"]
superuser = rec["superuser"]
token = _get_user_token(user_id)
flash("Login successful.")
crs.execute(
"UPDATE user SET last_login = CURRENT_TIMESTAMP() WHERE pkid = %s",
user_id)
target = session.get("original_url") or "/"
session["token"] = token
session["superuser"] = superuser
return redirect(target)
def update_smart():
rf = request.form
kk = list(rf.keys())
fields = [k for k in kk if k.startswith("field")]
comps = [k for k in kk if k.startswith("comp")]
name = rf.get("name")
pkid = rf.get("pkid")
out = []
for field in fields:
fld_name = rf.get(field)
# The name is 'fieldN', where N is the sequence
seq = field.split("field")[-1]
comp = rf.get(f"comp{seq}")
val = rf.get(f"value{seq}")
out.append({fld_name: {comp: val}})
rules = json.dumps(out)
if not pkid:
# New Album
pkid = utils.gen_uuid()
sql = """insert into album (pkid, name, smart, rules)
values (%s, %s, %s, %s); """
vals = (pkid, name, True, rules)
else:
sql = """update album set name = %s, rules = %s
where pkid = %s; """
vals = (name, rules, pkid)
with utils.DbCursor() as crs:
crs.execute(sql, vals)
album_obj = entities.Album.get(pkid)
album_obj.update_images(None)
return redirect(url_for("list_albums"))
def get_by_name(cls, name):
"""Return any album whose name matches the supplied value."""
sql = "select * from album where name = %s;"
with utils.DbCursor() as crs:
crs.execute(sql, (name, ))
recs = crs.fetchall()
cls._after_list(recs)
return cls.from_recs(recs)
def logout():
token = session.get("token")
if token:
with utils.DbCursor() as crs:
crs.execute("DELETE FROM login WHERE token = %s;", token)
del session["token"]
del session["superuser"]
flash("You have been logged out.")
return redirect("/")
def show(pkid):
sql = "select * from image where pkid = %s"
with utils.DbCursor() as crs:
res = crs.execute(sql, (pkid, ))
if not res:
abort(404)
g.image = crs.fetchone()
g.image["size"] = utils.human_fmt(g.image["size"])
return render_template("image_detail.html")
def _get_image_count(cls, pkid, smart=False):
if smart:
return len(cls.smart_album_images(pkid))
sql = """select count(*) as image_count from image
join album_image on album_image.image_id = image.pkid
where album_image.album_id = %s"""
with utils.DbCursor() as crs:
crs.execute(sql, pkid)
return crs.fetchone()["image_count"]
def images(self):
if self.smart:
return self.smart_album_images(self.pkid)
sql = ("select image.* from image join album_image "
"on album_image.image_id = image.pkid "
"where album_image.album_id = %s")
with utils.DbCursor() as crs:
crs.execute(sql, self.pkid)
return Image.from_recs(crs.fetchall())
def set_frame_album(self, frame_id):
utils.debugout("SET_FRAME_ALBUM called")
image_names = []
if self.image_ids:
sql = "select name from image where pkid in %s;"
with utils.DbCursor() as crs:
crs.execute(sql, (self.image_ids, ))
image_names = [rec["name"] for rec in crs.fetchall()]
utils.write_key(frame_id, "images", image_names)
utils.debugout("Wrote images for frame_id =", frame_id)
def _after_get(cls, rec):
"""We need to add the frameset name, if any, to the record."""
sql = """select frameset.name
from frameset
where frameset.pkid = %s;"""
with utils.DbCursor() as crs:
crs.execute(sql, (rec["frameset_id"], ))
name_rec = crs.fetchone()
rec["frameset_name"] = name_rec.get("name") if name_rec else ""
fs = rec["freespace"]
rec["freespace"] = utils.human_fmt(fs)
def _get_user_token(user_id):
token = hashlib.md5(os.urandom(8)).hexdigest()
expires = dt.datetime.utcnow() + TOKEN_DURATION
with utils.DbCursor() as crs:
crs.execute(
"""
INSERT INTO login (token, user_id, expires)
VALUES (%s, %s, %s)
ON DUPLICATE KEY UPDATE token=%s, expires=%s""",
(token, user_id, expires, token, expires))
return token
def image_names(self):
if self.smart:
recs = self.smart_album_images(self.pkid, return_objects=False)
else:
sql = """select image.name from image
join album_image on album_image.image_id = image.pkid
where album_image.album_id = %s"""
with utils.DbCursor() as crs:
crs.execute(sql, self.pkid)
recs = crs.fetchall()
return [rec["name"] for rec in recs]
def _after_delete(cls, pkid):
# We need to delete the related records in album_image
with utils.DbCursor() as crs:
sql = "delete from album_image where album_image.album_id = %s"
crs.execute(sql, (pkid))
# We also need to null out any frames that were using this album
sql = "update frame set album_id = '' where album_id = %s"
crs.execute(sql, (pkid, ))
# Finally, we need to delete any sub-albums
sql = "delete from album where parent_id = %s"
crs.execute(sql, (pkid, ))
def _save_new(self):
# New frames may specify their pkid, so if it's there, use that
self.pkid = self.pkid or utils.gen_uuid()
field_names = ", ".join(self.db_field_names)
values = tuple(
[str(getattr(self, field, None)) for field in self.db_field_names])
value_placeholders = ", ".join(["%s"] * len(self.db_field_names))
sql = "insert into {} ({}) values ({})".format(self.table_name,
field_names,
value_placeholders)
with utils.DbCursor() as crs:
crs.execute(sql, values)
def _after_list(self, recs):
"""Add the frame counts."""
sql = "select count(pkid) as num_frames, frameset_id from frame group by frameset_id;"
with utils.DbCursor() as crs:
crs.execute(sql)
frame_count_recs = crs.fetchall()
mapping = {
rec["frameset_id"]: rec["num_frames"]
for rec in frame_count_recs
}
for rec in recs:
rec["num_frames"] = mapping.get(rec["pkid"], 0)
def _after_list(cls, recs):
sql = """select frame.pkid, frameset.name, frameset.album_id
from frame join frameset on frame.frameset_id = frameset.pkid;"""
with utils.DbCursor() as crs:
crs.execute(sql)
frameset_name_recs = crs.fetchall()
name_mapping = {rec["pkid"]: rec["name"] for rec in frameset_name_recs}
album_mapping = {
rec["pkid"]: rec["album_id"]
for rec in frameset_name_recs
}
for rec in recs:
rec["frameset_name"] = name_mapping.get(rec["pkid"], "-none-")
rec["album_id"] = album_mapping.get(rec["pkid"], rec["album_id"])
rec["freespace"] = utils.human_fmt(rec["freespace"])
def upload_file():
image = request.files["image_file"]
fname = secure_filename(image.filename)
# Make sure that there isn't another file by that name
if isduplicate(fname):
flash("Image already exists!!", "error")
return redirect(url_for("upload_image_form"))
fpath = os.path.join(IMAGE_FOLDER, fname)
image.save(fpath)
try:
img_obj = Image.open(fpath)
except IOError:
flash("Not a valid image", "err")
os.unlink(fpath)
return redirect(url_for("upload_image_form"))
imgtype = img_obj.format
orientation = utils.get_img_orientation(fpath)
width, height = img_obj.size
rf = request.form
keywords = rf["file_keywords"] or fname
size = os.stat(fpath).st_size
created = img_obj._getexif().get(CREATE_DATE_KEY)
updated = datetime.fromtimestamp(os.stat(fpath).st_ctime)
# Make a thumbnail
thumb_size = (120, 120)
img_obj.thumbnail(thumb_size)
thumb_path_parts = list(os.path.split(fpath))
thumb_path_parts.insert(-1, "thumbs")
thumb_path = os.path.join(*thumb_path_parts)
try:
img_obj.save(thumb_path, format=imgtype)
except Exception as e:
print("EXCEPTION", e)
# Save the info in the database
pkid = utils.gen_uuid()
sql = """
insert into image (pkid, keywords, name, orientation, width,
height, imgtype, size, created, updated)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s); """
vals = (pkid, keywords, fname, orientation, width, height, imgtype, size,
created, updated)
with utils.DbCursor() as crs:
crs.execute(sql, vals)
return redirect(url_for("list_images"))
def update():
rf = request.form
if "delete" in rf:
return delete()
pkid = rf["pkid"]
name = rf["name"]
orig_name = rf["orig_name"]
keywords = rf["keywords"]
sql = """
update image set name = %s, keywords = %s
where pkid = %s; """
with utils.DbCursor() as crs:
crs.execute(sql, (name, keywords, pkid))
if name != orig_name:
_rename_image(orig_name, name)
return redirect(url_for("list_images"))
def get(cls, pkid_or_obj):
"""Returns an object matching the supplied ID. If the value is an
instance of the object, verifies that the object's ID is valid.
"""
if isinstance(pkid_or_obj, cls):
pkid = pkid_or_obj.pkid
else:
pkid = pkid_or_obj
sql = "select * from {} where pkid = %s;".format(cls.table_name)
with utils.DbCursor() as crs:
crs.execute(sql, (pkid, ))
rec = crs.fetchone()
if not rec:
raise exc.NotFound()
# Allow subclasses to customize the response
cls._after_get(rec)
return cls(**rec)
def update_list():
rf = request.form
if rf["filter"]:
return GET_list(filt=rf["filter"])
sql = "update image set keywords = %s where pkid = %s;"
keys = rf.keys()
new_fields = [key for key in keys if key.startswith("key_")]
for new_field in new_fields:
pkid = new_field.split("_")[-1]
orig_field = "orig_%s" % pkid
new_val = rf.get(new_field)
orig_val = rf.get(orig_field)
if new_val == orig_val:
continue
with utils.DbCursor() as crs:
crs.execute(sql, (new_val, pkid))
return GET_list()
def update_frame_album(self, image_ids=None):
"""Updates the 'images' key for all frames that are linked to the album."""
utils.debugout("UPDATE_FRAME_ALBUM called")
image_ids = image_ids or self.image_ids
sql = "select pkid from frame where album_id = %s;"
with utils.DbCursor() as crs:
count = crs.execute(sql, (self.pkid, ))
utils.debugout("FRAME COUNT", count)
frame_ids = [rec["pkid"] for rec in crs.fetchall()]
utils.debugout("Album.update_frame_album; frame_ids=", frame_ids,
"album_id =", self.pkid)
if frame_ids:
sql = "select name from image where pkid in %s;"
crs.execute(sql, (image_ids, ))
image_names = [rec["name"] for rec in crs.fetchall()]
for frame_id in frame_ids:
utils.write_key(frame_id, "images", image_names)
utils.debugout("Wrote images for frame_id =", frame_id)
def _update(self):
# Get the changed fields
sql = "select * from {} where pkid = %s".format(self.table_name)
with utils.DbCursor() as crs:
crs.execute(sql, (self.pkid, ))
rec = crs.fetchone()
changes = []
values = []
for fld, val in rec.items():
obj_val = getattr(self, fld)
if obj_val != val:
changes.append("{}=%s".format(fld))
values.append(obj_val)
if not changes:
# Just return; nothing to do
return
set_clause = ", ".join(changes)
sql = "update {} set {} where pkid=%s".format(
self.table_name, set_clause)
crs.execute(sql, (*values, self.pkid))
def create_user():
form = request.form
username = form.get("username")
pw = form.get("pw")
if not all((username, pw)):
flash("You must supply a username and password")
return render_template("user_reg_form")
hpw = _hash_pw(pw)
superuser = form.get("user_type") == "super"
pkid = utils.gen_uuid()
with utils.DbCursor() as crs:
try:
crs.execute(
"""
INSERT INTO user (pkid, name, pw, superuser)
VALUES (%s, %s, %s, %s)""", (pkid, username, hpw, superuser))
except utils.IntegrityError as ee:
flash("Oops! %s" % str(ee.args[1]), "error")
return render_template("user_reg_form.html")
flash("Successfully registered user '%s'" % username)
return redirect("/")
def delete(pkid=None):
if pkid is None:
# Form
pkid = request.form["pkid"]
with utils.DbCursor() as crs:
# Get the file name
sql = "select name from image where pkid = %s"
res = crs.execute(sql, (pkid, ))
if not res:
abort(404)
fname = crs.fetchone()["name"]
sql = "delete from image where pkid = %s"
crs.execute(sql, (pkid, ))
sql = "delete from album_image where image_id = %s"
crs.execute(sql, (pkid, ))
# Now delete the file, if it is present
fpath = os.path.join(IMAGE_FOLDER, fname)
try:
os.unlink(fpath)
except OSError:
pass
return redirect(url_for("list_images"))
def list(cls, **kwargs):
"""Get all the records for this class.
They can be optionally filtered by the key/value pairs in kwargs
"""
sql = "select * from {}".format(cls.table_name)
if cls.custom_list:
where_clause = cls._custom_where(**kwargs)
vals = tuple()
else:
wheres = ["{} = %s".format(fld) for fld in kwargs.keys()]
vals = kwargs.values()
where_clause = " and ".join(wheres)
if where_clause:
sql = "{} where {}".format(sql, where_clause)
print("SQL", sql)
with utils.DbCursor() as crs:
crs.execute(sql, *vals)
recs = crs.fetchall()
# Allow subclasses to customize the results
cls._after_list(recs)
return cls.from_recs(recs)
"""
crs.execute(sql)
def create_rule(crs):
sql = "drop table if exists rule;"
crs.execute(sql)
sql = """
create table rule (
pkid VARCHAR(36) NOT NULL PRIMARY KEY,
album_id VARCHAR(36),
rule VARCHAR(256),
updated TIMESTAMP
);
"""
crs.execute(sql)
def main(crs):
create_frame(crs)
create_frameset(crs)
create_image(crs)
create_album(crs)
create_album_image(crs)
crs.connection.commit()
if __name__ == "__main__":
with utils.DbCursor() as crs:
main(crs)
def isduplicate(name):
"""See if another file of the same name exists."""
sql = "select pkid from image where name = %s;"
with utils.DbCursor() as crs:
res = crs.execute(sql, (name, ))
return bool(res)
