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)