def scale_animal_images(dbo): """ Goes through all animal images in the database and scales them to the current incoming media scaling factor. """ mp = db.query( dbo, "SELECT MediaName FROM media WHERE LOWER(MediaName) LIKE '%.jpg' AND LinkTypeID = 0" ) for i, m in enumerate(mp): filepath = db.query_string( dbo, "SELECT Path FROM dbfs WHERE Name='%s'" % m["MEDIANAME"]) name = str(m["MEDIANAME"]) inputfile = tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) outputfile = tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) odata = dbfs.get_string(dbo, name) inputfile.write(odata) inputfile.flush() inputfile.close() outputfile.close() al.debug("scaling %s (%d of %d)" % (name, i, len(mp)), "media.scale_animal_images", dbo) scale_image_file(inputfile.name, outputfile.name, configuration.incoming_media_scaling(dbo)) f = open(outputfile.name, "r") data = f.read() f.close() os.unlink(inputfile.name) os.unlink(outputfile.name) # Update the image file data dbfs.put_string(dbo, name, filepath, data) al.debug("scaled %d images" % len(mp), "media.scale_animal_images", dbo)
def get_id(dbo, table): """ Returns the next ID in sequence for a table according to a number of different strategies. max: Do SELECT MAX(ID)+1 FROM TABLE cache: Use an in-memory cache to track PK values (initialised by SELECT MAX) pseq: Use PostgreSQL sequences If the database has an ASM2 primary key table, it will be updated with the next pk value. """ strategy = "" nextid = 0 if DB_PK_STRATEGY == "max" or dbo.has_asm2_pk_table: nextid = _get_id_max(dbo, table) strategy = "max" elif DB_PK_STRATEGY == "cache": nextid = _get_id_cache(dbo, table) strategy = "cache" elif DB_PK_STRATEGY == "pseq" and dbo.dbtype == "POSTGRESQL": nextid = _get_id_postgres_seq(dbo, table) strategy = "pseq" else: raise Exception("No valid PK strategy found") if dbo.has_asm2_pk_table: _get_id_set_asm2_primarykey(dbo, table, nextid + 1) strategy += " asm2pk" al.debug("get_id: %s -> %d (%s)" % (table, nextid, strategy), "db.get_id", dbo) return nextid
def scale_all_animal_images(dbo): """ Goes through all animal images in the database and scales them to the current incoming media scaling factor. """ mp = dbo.query("SELECT ID, MediaName FROM media WHERE MediaMimeType = 'image/jpeg' AND LinkTypeID = 0") for i, m in enumerate(mp): filepath = dbo.query_string("SELECT Path FROM dbfs WHERE Name = ?", [m.MEDIANAME]) name = str(m.MEDIANAME) inputfile = tempfile.NamedTemporaryFile(suffix=".jpg", delete=False) outputfile = tempfile.NamedTemporaryFile(suffix=".jpg", delete=False) odata = dbfs.get_string(dbo, name) inputfile.write(odata) inputfile.flush() inputfile.close() outputfile.close() al.debug("scaling %s (%d of %d)" % (name, i, len(mp)), "media.scale_all_animal_images", dbo) try: scale_image_file(inputfile.name, outputfile.name, configuration.incoming_media_scaling(dbo)) except Exception as err: al.error("failed scaling image, doing nothing: %s" % err, "media.scale_all_animal_images", dbo) continue data = utils.read_binary_file(outputfile.name) os.unlink(inputfile.name) os.unlink(outputfile.name) # Update the image file data dbfs.put_string(dbo, name, filepath, data) dbo.update("media", m.ID, { "MediaSize": len(data) }) al.debug("scaled %d images" % len(mp), "media.scale_all_animal_images", dbo)
def check_and_scale_pdfs(dbo): """ Goes through all PDFs in the database to see if they have been scaled (have a suffix of _scaled.pdf) and scales down any unscaled ones. """ if not SCALE_PDF_DURING_BATCH: al.warn("SCALE_PDF_DURING_BATCH is disabled, not scaling pdfs", "media.check_and_scale_pdfs", dbo) if not configuration.scale_pdfs(dbo): al.warn( "ScalePDFs config option disabled in this database, not scaling pdfs", "media.check_and_scale_pdfs", dbo) mp = db.query(dbo, \ "SELECT MediaName FROM media WHERE LOWER(MediaName) LIKE '%.pdf' AND " \ "LOWER(MediaName) NOT LIKE '%_scaled.pdf'") for m in mp: filepath = db.query_string( dbo, "SELECT Path FROM dbfs WHERE Name='%s'" % m["MEDIANAME"]) original_name = str(m["MEDIANAME"]) new_name = str(m["MEDIANAME"]) new_name = new_name[0:len(new_name) - 4] + "_scaled.pdf" odata = dbfs.get_string(dbo, original_name) data = scale_pdf(odata) # Update the media entry with the new name db.execute(dbo, "UPDATE media SET MediaName = '%s' WHERE MediaName = '%s'" % \ ( new_name, original_name)) # Update the dbfs entry with the new name dbfs.rename_file(dbo, filepath, original_name, new_name) # Update the PDF file data dbfs.put_string(dbo, new_name, filepath, data) al.debug("found and scaled %d pdfs" % len(mp), "media.check_and_scale_pdfs", dbo)
def attach_link_from_form(dbo, username, linktype, linkid, post): """ Attaches a link to a web resource from a form """ existingvid = dbo.query_int("SELECT COUNT(*) FROM media WHERE WebsiteVideo = 1 " \ "AND LinkID = ? AND LinkTypeID = ?", (linkid, linktype)) defvid = 0 if existingvid == 0 and post.integer("linktype") == MEDIATYPE_VIDEO_LINK: defvid = 1 url = post["linktarget"] if url.find("://") == -1: url = "http://" + url al.debug("attached link %s" % url, "media.attach_file_from_form") return dbo.insert("media", { "DBFSID": 0, "MediaSize": 0, "MediaName": url, "MediaMimeType": "text/url", "MediaType": post.integer("linktype"), "MediaNotes": post["comments"], "WebsitePhoto": 0, "WebsiteVideo": defvid, "DocPhoto": 0, "ExcludeFromPublish": 0, # ASM2_COMPATIBILITY "NewSinceLastPublish": 1, "UpdatedSinceLastPublish": 0, # ASM2_COMPATIBILITY "LinkID": linkid, "LinkTypeID": linktype, "Date": dbo.now(), "RetainUntil": None }, username, setCreated=False)
def check_and_scale_pdfs(dbo, force = False): """ Goes through all PDFs in the database to see if they have been scaled (have a suffix of _scaled.pdf) and scales down any unscaled ones. If force is set, then all PDFs are checked and scaled again even if they've been scaled before. """ if not configuration.scale_pdfs(dbo): al.warn("ScalePDFs config option disabled in this database, not scaling pdfs", "media.check_and_scale_pdfs", dbo) return if force: mp = db.query(dbo, \ "SELECT ID, MediaName FROM media WHERE LOWER(MediaName) LIKE '%.pdf' ORDER BY ID DESC") else: mp = db.query(dbo, \ "SELECT ID, MediaName FROM media WHERE LOWER(MediaName) LIKE '%.pdf' AND " \ "LOWER(MediaName) NOT LIKE '%_scaled.pdf' ORDER BY ID DESC") for i, m in enumerate(mp): filepath = db.query_string(dbo, "SELECT Path FROM dbfs WHERE Name='%s'" % m["MEDIANAME"]) original_name = str(m["MEDIANAME"]) new_name = str(m["ID"]) + "_scaled.pdf" odata = dbfs.get_string(dbo, original_name) data = scale_pdf(odata) al.debug("scaling %s (%d of %d): old size %d, new size %d" % (new_name, i, len(mp), len(odata), len(data)), "check_and_scale_pdfs", dbo) # Update the media entry with the new name db.execute(dbo, "UPDATE media SET MediaName = '%s' WHERE ID = %d" % ( new_name, m["ID"])) # Update the dbfs entry from old name to new name (will be overwritten in a minute but safer than delete) dbfs.rename_file(dbo, filepath, original_name, new_name) # Store the PDF file data with the new name - if there was a need to change it if len(data) < len(odata): dbfs.put_string(dbo, new_name, filepath, data) al.debug("found and scaled %d pdfs" % len(mp), "media.check_and_scale_pdfs", dbo)
def auto_rotate_image(imagedata): """ Automatically rotate an image according to the orientation of the image in the EXIF data. """ try: inputd = StringIO(imagedata) im = Image.open(inputd) for orientation in ExifTags.TAGS.keys(): if ExifTags.TAGS[orientation] == "Orientation": break if not hasattr(im, "_getexif") or im._getexif() is None: al.debug("image has no EXIF data, abandoning rotate", "media.auto_rotate_image") return imagedata exif = dict(im._getexif().items()) if exif[orientation] == 3: im = im.transpose(Image.ROTATE_180) elif exif[orientation] == 6: im = im.transpose(Image.ROTATE_270) elif exif[orientation] == 8: im = im.transpose(Image.ROTATE_90) output = StringIO() im.save(output, "JPEG") rotated_data = output.getvalue() output.close() return rotated_data except Exception,err: al.error("failed rotating image: %s" % str(err), "media.auto_rotate_image") return imagedata
def post_animal_facebook(dbo, user, oauth_code, oauth_state): """ Post an animal to Facebook oauth_code: Provided by FB redirect, code to obtain user's access token oauth_state: Provided by FB redirect, from our original link the animal ID to post """ # Request an access token for the logged in Facebook user from the # oauth code we were given. try: client_id = FACEBOOK_CLIENT_ID client_secret = FACEBOOK_CLIENT_SECRET redirect_uri = urllib.quote(BASE_URL + "/animal_facebook") fb_url = "https://graph.facebook.com/oauth/access_token?client_id=%s&redirect_uri=%s&client_secret=%s&code=%s" % \ (client_id, redirect_uri, client_secret, oauth_code) al.debug("FB access token request: " + fb_url, "social.post_animal_facebook", dbo) access_token = urllib2.urlopen(fb_url).read() al.debug("FB access token response: " + access_token, "social.post_animal_facebook", dbo) except urllib2.HTTPError, herr: em = str(herr.read()) al.error("Failed getting facebook access token: %s" % em, "social.post_animal_facebook", dbo) raise utils.ASMValidationError("Failed getting Facebook access token.")
def auto_rotate_image(dbo, imagedata): """ Automatically rotate an image according to the orientation of the image in the EXIF data. """ try: inputd = StringIO(imagedata) im = Image.open(inputd) for orientation in ExifTags.TAGS.keys(): if ExifTags.TAGS[orientation] == "Orientation": break if not hasattr(im, "_getexif") or im._getexif() is None: al.debug("image has no EXIF data, abandoning rotate", "media.auto_rotate_image", dbo) return imagedata exif = dict(im._getexif().items()) if exif[orientation] == 3: im = im.transpose(Image.ROTATE_180) elif exif[orientation] == 6: im = im.transpose(Image.ROTATE_270) elif exif[orientation] == 8: im = im.transpose(Image.ROTATE_90) output = StringIO() im.save(output, "JPEG") rotated_data = output.getvalue() output.close() return rotated_data except Exception, err: al.error("failed rotating image: %s" % str(err), "media.auto_rotate_image", dbo) return imagedata
def get_id(self, table): """ Returns the next ID for a table """ nextid = self.get_id_max(table) self.update_asm2_primarykey(table, nextid) al.debug("get_id: %s -> %d (max)" % (table, nextid), "Database.get_id", self) return nextid
def scale_all_odt(dbo): """ Goes through all odt files attached to records in the database and scales them down (throws away images and objects so only the text remains to save space) """ mo = dbo.query( "SELECT ID, MediaName FROM media WHERE MediaMimeType = 'application/vnd.oasis.opendocument.text'" ) total = 0 for i, m in enumerate(mo): name = str(m.MEDIANAME) al.debug("scaling %s (%d of %d)" % (name, i, len(mo)), "media.scale_all_odt", dbo) odata = dbfs.get_string(dbo, name) if odata == "": al.error("file %s does not exist" % name, "media.scale_all_odt", dbo) continue path = dbo.query_string("SELECT Path FROM dbfs WHERE Name = ?", [name]) ndata = scale_odt(odata) if len(ndata) < 512: al.error( "scaled odt %s came back at %d bytes, abandoning" % (name, len(ndata)), "scale_all_odt", dbo) else: dbfs.put_string(dbo, name, path, ndata) dbo.update("media", m.ID, {"MediaSize": len(ndata)}) total += 1 al.debug("scaled %d of %d odts" % (total, len(mo)), "media.scale_all_odt", dbo)
def attach_link_from_form(dbo, username, linktype, linkid, post): """ Attaches a link to a web resource from a form """ existingvid = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE WebsiteVideo = 1 " \ "AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) )) defvid = 0 if existingvid == 0 and post.integer("linktype") == MEDIATYPE_VIDEO_LINK: defvid = 1 mediaid = db.get_id(dbo, "media") url = post["linktarget"] if url.find("://") == -1: url = "http://" + url al.debug("attached link %s" % url, "media.attach_file_from_form") sql = db.make_insert_sql("media", ( ( "ID", db.di(mediaid) ), ( "MediaName", db.ds(url) ), ( "MediaType", post.db_integer("linktype") ), ( "MediaNotes", post.db_string("comments") ), ( "WebsitePhoto", db.di(0) ), ( "WebsiteVideo", db.di(defvid) ), ( "DocPhoto", db.di(0) ), ( "ExcludeFromPublish", db.di(0) ), # ASM2_COMPATIBILITY ( "NewSinceLastPublish", db.di(1) ), ( "UpdatedSinceLastPublish", db.di(0) ), # ASM2_COMPATIBILITY ( "LinkID", db.di(linkid) ), ( "LinkTypeID", db.di(linktype) ), ( "Date", db.nowsql() ) )) db.execute(dbo, sql) audit.create(dbo, username, "media", mediaid, str(mediaid) + ": for " + str(linkid) + "/" + str(linktype) + ": link to " + post["linktarget"])
def switch_storage(dbo): """ Goes through all files in dbfs and swaps them into the current storage scheme """ rows = dbo.query( "SELECT ID, Name, Path, URL FROM dbfs WHERE Name LIKE '%.%' ORDER BY ID" ) for i, r in enumerate(rows): al.debug( "Storage transfer %s/%s (%d of %d)" % (r.path, r.name, i, len(rows)), "dbfs.switch_storage", dbo) source = DBFSStorage(dbo, r.url) target = DBFSStorage(dbo) # Don't bother if the file is already stored in the target format if source.url_prefix() == target.url_prefix(): al.debug("source is already %s, skipping" % source.url_prefix(), "dbfs.switch_storage", dbo) continue try: filedata = source.get(r.id, r.url) target.put(r.id, r.name, filedata) # Update the media size while we're switching in case it wasn't set previously dbo.execute("UPDATE media SET MediaSize=? WHERE DBFSID=?", (len(filedata), r.id)) except Exception as err: al.error("Error reading, skipping: %s" % str(err), "dbfs.switch_storage", dbo) # smcom only - perform postgresql full vacuum after switching if smcom.active(): smcom.vacuum_full(dbo)
def scale_animal_images(dbo): """ Goes through all animal images in the database and scales them to the current incoming media scaling factor. """ mp = db.query(dbo, "SELECT MediaName FROM media WHERE LOWER(MediaName) LIKE '%.jpg' AND LinkTypeID = 0") for i, m in enumerate(mp): filepath = db.query_string(dbo, "SELECT Path FROM dbfs WHERE Name='%s'" % m["MEDIANAME"]) name = str(m["MEDIANAME"]) inputfile = tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) outputfile = tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) odata = dbfs.get_string(dbo, name) inputfile.write(odata) inputfile.flush() inputfile.close() outputfile.close() al.debug("scaling %s (%d of %d)" % (name, i, len(mp)), "media.scale_animal_images", dbo) scale_image_file(inputfile.name, outputfile.name, configuration.incoming_media_scaling(dbo)) f = open(outputfile.name, "r") data = f.read() f.close() os.unlink(inputfile.name) os.unlink(outputfile.name) # Update the image file data dbfs.put_string(dbo, name, filepath, data) al.debug("scaled %d images" % len(mp), "media.scale_animal_images", dbo)
def update_match_report(dbo): """ Updates the latest version of the lost/found match report in the dbfs """ al.debug("updating lost/found match report", "lostfound.update_match_report", dbo) s = match_report(dbo) dbfs.put_string_filepath(dbo, "/reports/daily/lost_found_match.html", s)
def update_session(session): """ Updates and reloads stored session data, triggers reloading of config.js by changing config_ts """ dbo = session.dbo locale = configuration.locale(dbo) theme = "asm" loverride = get_locale_override(dbo, session.user) if loverride != "": al.debug( "user %s has locale override of %s set, switching." % (session.user, loverride), "users.update_session", dbo) locale = loverride toverride = get_theme_override(dbo, session.user) if toverride != "": al.debug( "user %s has theme override of %s set, switching." % (session.user, toverride), "users.update_session", dbo) theme = toverride dbo.locale = locale if not dbo.is_large_db: dbo.is_large_db = dbo.query_int("SELECT COUNT(*) FROM owner") > 4000 or \ dbo.query_int("SELECT COUNT(*) FROM animal") > 2000 session.locale = locale session.theme = theme session.config_ts = i18n.format_date("%Y%m%d%H%M%S", i18n.now())
def get_id(dbo, table): """ Returns the next ID in sequence for a table. Will use memcache for pk generation if the CACHE_PRIMARY_KEYS option is on and will set the cache first value if not set. If the database has an ASM2 primary key table, it will be updated with the next pk value. """ strategy = "" nextid = 0 if ASM3_PK_STRATEGY == "max" or dbo.has_asm2_pk_table: nextid = _get_id_max(dbo, table) strategy = "max" elif ASM3_PK_STRATEGY == "memcache" and cache.available(): nextid = _get_id_memcache(dbo, table) strategy = "memcache" elif ASM3_PK_STRATEGY == "pseq" and dbo.dbtype == "POSTGRESQL": nextid = _get_id_postgres_seq(dbo, table) strategy = "pseq" else: raise Exception("No valid PK strategy found") if dbo.has_asm2_pk_table: _get_id_set_asm2_primarykey(dbo, table, nextid + 1) strategy += " asm2pk" al.debug("get_id: %s -> %d (%s)" % (table, nextid, strategy), "db.get_id", dbo) return nextid
def remove_expired_media(dbo, username="******"): """ Removes all media where retainuntil < today and document media older than today - remove document media years """ rows = dbo.query( "SELECT ID, DBFSID FROM media WHERE RetainUntil Is Not Null AND RetainUntil < ?", [dbo.today()]) for r in rows: dbfs.delete_id(r.dbfsid) dbo.execute( "DELETE FROM media WHERE RetainUntil Is Not Null AND RetainUntil < ?", [dbo.today()]) al.debug("removed %d expired media items (retain until)" % len(rows), "media.remove_expired_media", dbo) if configuration.auto_remove_document_media(dbo): years = configuration.auto_remove_document_media_years(dbo) if years > 0: cutoff = dbo.today(years * -365) rows = dbo.query( "SELECT ID, DBFSID FROM media WHERE MediaType = ? AND MediaMimeType <> 'image/jpeg' AND Date < ?", (MEDIATYPE_FILE, cutoff)) for r in rows: dbfs.delete_id(r.dbfsid) dbo.execute( "DELETE FROM media WHERE MediaType = ? AND MediaMimeType <> 'image/jpeg' AND Date < ?", (MEDIATYPE_FILE, cutoff)) al.debug( "removed %d expired document media items (remove after years)" % len(rows), "media.remove_expired_media", dbo)
def auto_update_urgencies(dbo): """ Finds all animals where the next UrgencyUpdateDate field is greater than or equal to today and the urgency is larger than High (so we can never reach Urgent). """ update_period_days = configuration.waiting_list_urgency_update_period(dbo) if update_period_days == 0: al.debug( "urgency update period is 0, not updating waiting list entries", "waitinglist.auto_update_urgencies", dbo) return rows = dbo.query("SELECT a.* " \ "FROM animalwaitinglist a WHERE UrgencyUpdateDate <= ? " \ "AND Urgency > 2", [dbo.today()]) updates = [] for r in rows: al.debug("increasing urgency of waitinglist entry %d" % r.ID, "waitinglist.auto_update_urgencies", dbo) updates.append((now(dbo.timezone), add_days(r.URGENCYUPDATEDATE, update_period_days), r.URGENCY - 1, r.ID)) if len(updates) > 0: dbo.execute_many("UPDATE animalwaitinglist SET " \ "UrgencyLastUpdatedDate=?, " \ "UrgencyUpdateDate=?, " \ "Urgency=? " \ "WHERE ID=? ", updates)
def send_user_email(dbo, sendinguser, user, subject, body): """ Sends an email to users. sendinguser: The username of the person sending the email (we will look up their email) user: can be an individual username, a rolename or the translated version of (all) or (everyone) to denote all users. """ DEFAULT_EMAIL = "*****@*****.**" sendinguser = users.get_users(dbo, sendinguser) if len(sendinguser) == 0: fromadd = DEFAULT_EMAIL else: fromadd = sendinguser[0]["EMAILADDRESS"] if fromadd is None or fromadd.strip() == "": fromadd = DEFAULT_EMAIL al.debug("from: %s (%s), to: %s" % (sendinguser, fromadd, user), "utils.send_user_email", dbo) allusers = users.get_users(dbo) for u in allusers: # skip if we have no email address - we can't send it. if u["EMAILADDRESS"] is None or u["EMAILADDRESS"].strip() == "": continue if user == "*": send_email(dbo, fromadd, u["EMAILADDRESS"], "", subject, body) elif u["USERNAME"] == user: send_email(dbo, fromadd, u["EMAILADDRESS"], "", subject, body) elif nulltostr(u["ROLES"]).find(user) != -1: send_email(dbo, fromadd, u["EMAILADDRESS"], "", subject, body)
def update_session(session): """ Updates and reloads stored session data """ dbo = session.dbo locale = configuration.locale(dbo) theme = configuration.system_theme(dbo) loverride = get_locale_override(dbo, session.user) if loverride != "": al.debug( "user %s has locale override of %s set, switching." % (session.user, loverride), "users.update_session", dbo) locale = loverride toverride = get_theme_override(dbo, session.user) if toverride != "": al.debug( "user %s has theme override of %s set, switching." % (session.user, toverride), "users.update_session", dbo) theme = toverride dbo.locale = locale dbo.is_large_db = db.query_int(dbo, "SELECT COUNT(*) FROM animal") > 10000 session.locale = locale session.theme = theme session.config_ts = time.strftime("%Y%m%d%H%M%S", datetime.datetime.today().timetuple())
def get_lat_long(dbo, address, town, county, postcode, country=None): """ Looks up a latitude and longitude from an address using the set geocoding provider and returns them as lat,long,hash If no results were found, a zero lat and long are returned so that we know not to try and look this up again until the address hash changes. NB: dbo is only used for contextual reference in logging and obtaining locale, no database calls are made by any of this code. """ if address.strip() == "": return None try: # Synchronise this process to a single thread to prevent # abusing our geo provider lat_long_lock.acquire() if country is None: country = i18n.get_country(dbo.locale) g = None if GEO_PROVIDER == "nominatim": g = Nominatim(dbo, address, town, county, postcode, country) elif GEO_PROVIDER == "google": g = Google(dbo, address, town, county, postcode, country) elif GEO_PROVIDER == "smcom": g = Smcom(dbo, address, town, county, postcode, country) else: al.error("unrecognised geo provider: %s" % GEO_PROVIDER, "geo.get_lat_long", dbo) return None # Check the cache in case we already requested this address cachekey = "nom:" + g.q v = cachemem.get(cachekey) if v is not None: al.debug("cache hit for address: %s = %s" % (cachekey, v), "geo.get_lat_long", dbo) return v # Call the service to get the data g.search() # Parse the response to a lat/long value latlon = g.parse() cachemem.put(cachekey, latlon, 86400) if GEO_SLEEP_AFTER > 0: time.sleep(GEO_SLEEP_AFTER) return latlon except Exception as err: al.error(str(err), "geo.get_lat_long", dbo) return None finally: lat_long_lock.release()
def get_lat_long(dbo, address, town, county, postcode, country = None): """ Looks up a latitude and longitude from an address using GEOCODE_URL and returns them as lat,long,(first 3 chars of address) Returns None if no results were found. NB: dbo is only used for contextual reference in logging, no database calls are made by any of this code. """ if address.strip() == "": return None try: # Synchronise this process to a single thread to prevent # abusing our geo provider and concurrent requests for the # same address when opening an animal with the same # original/brought in by owner, etc. lat_long_lock.acquire() url = "" if country is None: country = LOCALE_COUNTRY_NAME_MAP[dbo.locale] if BULK_GEO_PROVIDER == "cloudmade": q = normalise_cloudmade(address, town, county, postcode, country) url = CLOUDMADE_URL.replace("{key}", BULK_GEO_PROVIDER_KEY).replace("{q}", q) elif BULK_GEO_PROVIDER == "nominatim": q = normalise_nominatim(address, town, county, postcode, country) url = NOMINATIM_URL.replace("{q}", q) else: al.error("unrecognised geo provider: %s" % BULK_GEO_PROVIDER, "geo.get_lat_long", dbo) al.debug("looking up geocode for address: %s" % q, "geo.get_lat_long", dbo) key = "nom:" + q if cache.available(): v = cache.get(key) if v is not None: al.debug("cache hit for address: %s = %s" % (q, v), "geo.get_lat_long", dbo) return v jr = urllib2.urlopen(url, timeout = GEO_LOOKUP_TIMEOUT).read() j = json.loads(jr) latlon = None if BULK_GEO_PROVIDER == "cloudmade": latlon = parse_cloudmade(dbo, jr, j, q) elif BULK_GEO_PROVIDER == "nominatim": latlon = parse_nominatim(dbo, jr, j, q) # Cache this address/geocode response for an hour if cache.available() and latlon is not None: cache.put(key, latlon, 3600) return latlon except Exception,err: al.error(str(err), "geo.get_lat_long", dbo) return None
def set_last_connected(dbo): """ Sets the last connected date on a database to today """ al.debug("Setting last connected to now for %s" % dbo.database, "smcom.set_last_connected", dbo) response = smcom_client.update_last_connected(dbo.database) if response != "OK": al.error("Failed setting last connection: %s" % response, "smcom.set_last_connected", dbo)
def update_match_report(dbo): """ Updates the latest version of the lost/found match report """ al.debug("updating lost/found match report", "lostfound.update_match_report", dbo) configuration.lostfound_report(dbo, match_report(dbo, limit = 1000)) configuration.lostfound_last_match_count(dbo, lostfound_last_match_count(dbo)) return "OK %d" % lostfound_last_match_count(dbo)
def do_send(): for r in rows: ssubject = substitute_tags(subject, r, False, opener = "<<", closer = ">>") sbody = substitute_tags(body, r) toadd = r["EMAILADDRESS"] if toadd is None or toadd.strip() == "": continue al.debug("sending bulk email: to=%s, subject=%s" % (toadd, ssubject), "utils.send_bulk_email", dbo) send_email(dbo, fromadd, toadd, "", ssubject, sbody, contenttype)
def get_id(self, table): """ Returns the next ID for a table using sequences """ nextid = self.query_int("VALUES NEXT VALUE FOR seq_%s" % table) al.debug("get_id: %s -> %d (sequence)" % (table, nextid), "DatabaseDB2.get_id", self) self.update_asm2_primarykey(table, nextid) return nextid
def get_id(self, table): """ Returns the next ID for a table using Postgres sequences """ nextid = self.query_int("SELECT nextval('seq_%s')" % table) self.update_asm2_primarykey(table, nextid) al.debug("get_id: %s -> %d (sequence)" % (table, nextid), "DatabasePostgreSQL.get_id", self) return nextid
def update_lookingfor_report(dbo): """ Updates the latest version of the looking for report in the dbfs """ al.debug("updating lookingfor report", "person.update_lookingfor_report", dbo) s = lookingfor_report(dbo) dbfs.put_string_filepath(dbo, "/reports/daily/lookingfor.html", s) configuration.lookingfor_last_match_count(dbo, lookingfor_last_match_count(dbo))
def clean(dbo): """ Deletes audit trail records older than three months """ d = db.today() d = i18n.subtract_days(d, 93); count = db.query_int(dbo, "SELECT COUNT(*) FROM audittrail WHERE AuditDate< %s" % db.dd(d)) al.debug("removing %d audit records older than 93 days." % count, "audit.clean", dbo) db.execute(dbo, "DELETE FROM audittrail WHERE AuditDate < %s" % db.dd(d))
def delete(self, url): """ Deletes the file data """ object_key = "%s/%s" % (self.dbo.database, url.replace("s3:", "")) try: self.s3client.delete_object(Bucket=DBFS_S3_BUCKET, Key=object_key) al.debug("DELETE: %s" % object_key, "S3Storage.delete", self.dbo) cachedisk.delete(self._cache_key(url)) except Exception as err: raise DBFSError("Failed deleting from S3: %s" % err)
def ttask(fn, dbo): """ Runs a function and times how long it takes """ x = time.time() fn(dbo) elapsed = time.time() - x if elapsed > 10: al.warn("complete in %0.2f sec" % elapsed, fn.__name__, dbo) else: al.debug("complete in %0.2f sec" % elapsed, fn.__name__, dbo)
def clean(dbo): """ Deletes audit trail records older than three months """ d = i18n.subtract_days(i18n.now(), 93) count = dbo.query_int( "SELECT COUNT(*) FROM audittrail WHERE AuditDate < ?", [d]) al.debug("removing %d audit records older than 93 days." % count, "audit.clean", dbo) dbo.execute("DELETE FROM audittrail WHERE AuditDate < ?", [d])
def delete_old_publish_logs(dbo): """ Deletes all publishing logs older than 14 days """ KEEP_DAYS = 14 cutoff = dbo.today(offset=KEEP_DAYS * -1) count = dbo.query_int( "SELECT COUNT(*) FROM publishlog WHERE PublishDateTime < ?", [cutoff]) al.debug( "removing %d publishing logs (keep for %d days)." % (count, KEEP_DAYS), "publish.delete_old_publish_logs", dbo) dbo.execute("DELETE FROM publishlog WHERE PublishDateTime < ?", [cutoff])
def asm_news(dbo, update=False): s = cstring(dbo, "ASMNews") if s == "" or update: try: s = utils.get_url(URL_NEWS)["response"] al.debug("Updated ASM news, got %d bytes" % len(s), "configuration.asm_news", dbo) cset(dbo, "ASMNews", s, sanitiseXSS = False) except: em = str(sys.exc_info()[0]) al.error("Failed reading ASM news: %s" % em, "configuration.asm_news", dbo) return s
def parse_nominatim(dbo, jr, j, q): if len(j) == 0: al.debug("no response from nominatim for %s (response %s)" % (q, str(jr)), "geo.parse_nominatim", dbo) return None try: latlon = "%s,%s,%s" % (str(utils.strip_unicode(j[0]["lat"])), str(utils.strip_unicode(j[0]["lon"])), "na") al.debug("contacted nominatim to get geocode for %s = %s" % (q, latlon), "geo.parse_nominatim", dbo) return latlon except Exception,err: al.error("couldn't find geocode in nominatim response: %s, %s" % (str(err), jr), "geo.parse_nominatim", dbo) return None
def get_expiry_date(dbo): """ Returns the account expiry date or None for a problem. """ a = get_account(dbo.database) try: expiry = datetime.datetime.strptime(a["expiry"], "%Y-%m-%d") al.debug("retrieved account expiry date: %s" % expiry, "smcom.get_expiry_date", dbo) return expiry except: return None
def get_expiry_date(dbo): """ Returns the account expiry date or None for a problem. """ a = _get_account_info(dbo.database) try: expiry = datetime.datetime.strptime(a["expiry"], "%Y-%m-%d") al.debug("retrieved account expiry date: %s" % expiry, "smcom.get_expiry_date", dbo) return expiry except: return None
def parse_cloudmade(dbo, jr, j, q): if not j.has_key("found") or j["found"] == "0": al.debug("no response from cloudmade for %s (response %s)" % (q, str(jr)), "geo.parse_cloudmade", dbo) return None try: point = j["features"][0]["centroid"]["coordinates"] latlon = "%s,%s,%s" % (str(point[0]), str(point[1]), "na") al.debug("contacted cloudmade to get geocode for %s = %s" % (q, latlon), "geo.parse_cloudmade", dbo) return latlon except Exception,err: al.error("couldn't find geocode in response: %s, %s" % (str(err), jr), "geo.parse_cloudmade", dbo) return None
def parse_google(dbo, jr, j, q): if len(j) == 0: al.debug("no response from google for %s (response %s)" % (q, str(jr)), "geo.parse_google", dbo) return None try: loc = j["results"][0]["geometry"]["location"] latlon = "%s,%s,%s" % (str(loc["lat"]), str(loc["lng"]), "na") al.debug("contacted google to get geocode for %s = %s" % (q, latlon), "geo.parse_google", dbo) return latlon except Exception,err: al.error("couldn't find geocode in google response. Status was %s: %s, %s" % (j["status"], str(err), jr), "geo.parse_google", dbo) return None
def update_html_publisher_template(dbo, username, name, header, body, footer): """ Creates a new html publisher template with the name given. If the template already exists, it recreates it. """ delete_path(dbo, "/internet/" + name) delete(dbo, name, "/internet") create_path(dbo, "/internet", name) put_string(dbo, "head.html", "/internet/%s" % name, header) put_string(dbo, "body.html", "/internet/%s" % name, body) put_string(dbo, "foot.html", "/internet/%s" % name, footer) al.debug("%s updated html template %s" % (username, name), "dbfs.update_html_publisher_template", dbo)
def auto_update_statuses(dbo): """ Moves on waiting list statuses where appropriate. 1. For appointments due in the next 20 hours with a status of scheduled, moves them on to "Not Arrived" 2. TODO: Auto cancel? Better to leave as not arrived so the difference can be seen between didn't show up and cancelled? """ cutoff = i18n.add_hours(dbo.now(), 20) affected = dbo.execute( "UPDATE clinicappointment SET Status = ? WHERE Status = ? AND DateTime >= ? AND DateTime <= ?", (NOT_ARRIVED, SCHEDULED, dbo.now(), cutoff)) al.debug( "advanced %d appointments from SCHEDULED to NOT_ARRIVED" % affected, "clinic.auto_update_statuses", dbo) return "OK %d" % affected
def delete_old_publish_logs(dbo): """ Retains only the last MAX_LOGS publish logs """ MAX_LOGS = 30 rows = db.query(dbo, "SELECT Name FROM dbfs WHERE Path Like '/logs/publish%%' ORDER BY Name DESC LIMIT %d" % MAX_LOGS) if len(rows) < MAX_LOGS: return names = [] for r in rows: names.append("'" + r["NAME"] + "'") notin = ",".join(names) count = db.query_int(dbo, "SELECT COUNT(*) FROM dbfs WHERE Path Like '/logs/publish%%' AND Name NOT IN (%s)" % notin) al.debug("removing %d publishing logs (keep latest 30)." % count, "dbfs.delete_old_publish_logs", dbo) rq = "DELETE FROM dbfs WHERE Path Like '/logs/publish%%' AND Name NOT IN (%s)" % notin db.execute(dbo, rq)
def do_send(): for r in rows: ssubject = substitute_tags(subject, r, False, opener="<<", closer=">>") sbody = substitute_tags(body, r) toadd = r["EMAILADDRESS"] if toadd is None or toadd.strip() == "": continue al.debug( "sending bulk email: to=%s, subject=%s" % (toadd, ssubject), "utils.send_bulk_email", dbo) send_email(dbo, fromadd, toadd, "", ssubject, sbody, contenttype)
def send_bulk_email(dbo, fromadd, subject, body, rows, contenttype): """ Sends a set of bulk emails. fromadd is an RFC821 address subject and body are strings. Either can contain <<TAGS>> rows is a list of dictionaries of tag tokens with real values to substitute contenttype is either "plain" or "html" """ for r in rows: ssubject = substitute_tags(subject, r, False, opener = "<<", closer = ">>") sbody = substitute_tags(body, r, False, opener = "<<", closer = ">>") toadd = r["EMAILADDRESS"] if toadd is None or toadd.strip() == "": continue al.debug("sending bulk email: to=%s, subject=%s" % (toadd, ssubject), "utils.send_bulk_email", dbo) send_email(dbo, fromadd, toadd, "", ssubject, sbody, contenttype)
def update_asm_news(dbo): """ Reads the latest asm news and stores it in the dbfs """ s = "" try: s = urllib2.urlopen(URL_NEWS).read() except: em = str(sys.exc_info()[0]) al.error("Failed reading ASM news: %s" % em, "dbfs.update_asm_news", dbo) else: al.debug("Updated ASM news, got %d bytes" % len(s), "dbfs.update_asm_news", dbo) x = get_string(dbo, "asm.news") if x == "": put_string(dbo, "asm.news", "/", s) else: replace_string(dbo, s, "asm.news")
def check_create_next_donation(dbo, username, odid): """ Checks to see if a donation is now received and the next in a sequence needs to be created for donations with a frequency """ al.debug("Create next donation %d" % int(odid), "financial.check_create_next_donation", dbo) d = db.query(dbo, "SELECT * FROM ownerdonation WHERE ID = %d" % int(odid)) if d is None or len(d) == 0: al.error("No donation found for %d" % int(odid), "financial.check_create_next_donation", dbo) return d = d[0] # If we have a frequency > 0, the nextcreated flag isn't set # and there's a datereceived and due then we need to create the # next donation in the sequence if d["DATEDUE"] != None and d["DATE"] != None and d["FREQUENCY"] > 0 and d["NEXTCREATED"] == 0: nextdue = d["DATEDUE"] if d["FREQUENCY"] == 1: nextdue = i18n.add_days(nextdue, 7) if d["FREQUENCY"] == 2: nextdue = i18n.add_months(nextdue, 1) if d["FREQUENCY"] == 3: nextdue = i18n.add_months(nextdue, 3) if d["FREQUENCY"] == 4: nextdue = i18n.add_years(nextdue, 1) al.debug("Next donation due %s" % str(nextdue), "financial.check_create_next_donation", dbo) # Update nextcreated flag for this donation db.execute(dbo, "UPDATE ownerdonation SET NextCreated = 1 WHERE ID = %d" % int(odid)) # Create the new donation due record did = db.get_id(dbo, "ownerdonation") sql = db.make_insert_user_sql(dbo, "ownerdonation", username, ( ( "ID", db.di(did)), ( "AnimalID", db.di(d["ANIMALID"])), ( "OwnerID", db.di(d["OWNERID"])), ( "MovementID", db.di(d["MOVEMENTID"])), ( "DonationTypeID", db.di(d["DONATIONTYPEID"])), ( "DateDue", db.dd(nextdue)), ( "Date", db.dd(None)), ( "Donation", db.di(d["DONATION"])), ( "IsGiftAid", db.di(d["ISGIFTAID"])), ( "DonationPaymentID", db.di(d["DONATIONPAYMENTID"])), ( "Frequency", db.di(d["FREQUENCY"])), ( "NextCreated", db.di(0)), ( "Comments", db.ds(d["COMMENTS"])) )) db.execute(dbo, sql)
def update_missing_geocodes(dbo): """ Goes through all people records without geocodes and completes the missing ones, using our configured bulk geocoding service. We limit this to LIMIT geocode requests per call so that databases with a lot of historical data don't end up tying up the daily batch for a long time, they'll just slowly complete over time. """ LIMIT = 250 people = db.query(dbo, "SELECT ID, OwnerAddress, OwnerTown, OwnerCounty, OwnerPostcode " \ "FROM owner WHERE LatLong Is Null OR LatLong = '' LIMIT %d" % LIMIT) batch = [] for p in people: latlong = geo.get_lat_long(dbo, p["OWNERADDRESS"], p["OWNERTOWN"], p["OWNERCOUNTY"], p["OWNERPOSTCODE"]) if latlong is not None: batch.append((latlong, p["ID"])) db.execute_many(dbo, "UPDATE owner SET LatLong = %s WHERE ID = %s", batch) al.debug("updated %d person geocodes" % len(batch), "person.update_missing_geocodes", dbo)
def auto_remove_waitinglist(dbo): """ Finds and automatically marks entries removed that have gone past the last contact date + weeks. """ l = dbo.locale rows = db.query(dbo, "SELECT a.ID, a.DateOfLastOwnerContact, " \ "a.AutoRemovePolicy " \ "FROM animalwaitinglist a WHERE a.DateRemovedFromList Is Null " \ "AND AutoRemovePolicy > 0 AND DateOfLastOwnerContact Is Not Null") updates = [] for r in rows: xdate = add_days(r["DATEOFLASTOWNERCONTACT"], 7 * r["AUTOREMOVEPOLICY"]) if after(now(dbo.timezone), xdate): al.debug("auto removing waitinglist entry %d due to policy" % int(r["ID"]), "waitinglist.auto_remove_waitinglist", dbo) updates.append((now(dbo.timezone), _("Auto removed due to lack of owner contact.", l), r["ID"])) if len(updates) > 0: db.execute_many(dbo, "UPDATE animalwaitinglist SET DateRemovedFromList = %s, " \ "ReasonForRemoval=%s WHERE ID=%s", updates)
def update_session(session): """ Updates and reloads stored session data """ dbo = session.dbo locale = configuration.locale(dbo) theme = configuration.system_theme(dbo) loverride = get_locale_override(dbo, session.user) if loverride != "": al.debug("user %s has locale override of %s set, switching." % (session.user, loverride), "users.update_session", dbo) locale = loverride toverride = get_theme_override(dbo, session.user) if toverride != "": al.debug("user %s has theme override of %s set, switching." % (session.user, toverride), "users.update_session", dbo) theme = toverride dbo.locale = locale dbo.is_large_db = db.query_int(dbo, "SELECT COUNT(*) FROM animal") > 10000 session.locale = locale session.theme = theme session.config_ts = time.strftime("%Y%m%d%H%M%S", datetime.datetime.today().timetuple())
def update_session(session): """ Updates and reloads stored session data """ dbo = session.dbo locale = configuration.locale(dbo) theme = "asm" loverride = get_locale_override(dbo, session.user) if loverride != "": al.debug("user %s has locale override of %s set, switching." % (session.user, loverride), "users.update_session", dbo) locale = loverride toverride = get_theme_override(dbo, session.user) if toverride != "": al.debug("user %s has theme override of %s set, switching." % (session.user, toverride), "users.update_session", dbo) theme = toverride dbo.locale = locale if not dbo.is_large_db: dbo.is_large_db = db.query_int(dbo, "SELECT COUNT(*) FROM owner") > 4000 or \ db.query_int(dbo, "SELECT COUNT(*) FROM animal") > 2000 session.locale = locale session.theme = theme session.config_ts = i18n.format_date("%Y%m%d%H%M%S", i18n.now())
def check_and_scale_pdfs(dbo): """ Goes through all PDFs in the database to see if they have been scaled (have a suffix of _scaled.pdf) and scales down any unscaled ones. """ if not gs_installed(): al.warn("ghostscript is not installed, can't scale pdfs", "media.check_and_scale_pdfs", dbo) return mp = db.query(dbo, \ "SELECT MediaName FROM media WHERE LOWER(MediaName) LIKE '%.pdf' AND " \ "LOWER(MediaName) NOT LIKE '%_scaled.pdf'") for m in mp: filepath = db.query_string(dbo, "SELECT Path FROM dbfs WHERE Name='%s'" % m["MEDIANAME"]) original_name = str(m["MEDIANAME"]) new_name = str(m["MEDIANAME"]) new_name = new_name[0:len(new_name)-4] + "_scaled.pdf" inputfile = tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) outputfile = tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) odata = dbfs.get_string(dbo, original_name) inputfile.write(odata) inputfile.flush() inputfile.close() outputfile.close() scale_pdf_file(inputfile.name, outputfile.name) f = open(outputfile.name, "r") data = f.read() f.close() os.unlink(inputfile.name) os.unlink(outputfile.name) # Update the media entry with the new name db.execute(dbo, "UPDATE media SET MediaName = '%s' WHERE MediaName = '%s'" % \ ( new_name, original_name)) # Update the dbfs entry with the new name dbfs.rename_file(dbo, filepath, original_name, new_name) # Update the PDF file data dbfs.put_string(dbo, new_name, filepath, data) al.debug("found and scaled %d pdfs" % len(mp), "media.check_and_scale_pdfs", dbo)
def auto_update_urgencies(dbo): """ Finds all animals where the next UrgencyUpdateDate field is greater than or equal to today and the urgency is larger than High (so we can never reach Urgent). """ update_period_days = configuration.waiting_list_urgency_update_period(dbo) if update_period_days == 0: al.debug("urgency update period is 0, not updating waiting list entries", "waitinglist.auto_update_urgencies", dbo) return rows = db.query(dbo, "SELECT a.* " \ "FROM animalwaitinglist a WHERE UrgencyUpdateDate <= %s " \ "AND Urgency > 2" % db.dd(now(dbo.timezone))) updates = [] for r in rows: al.debug("increasing urgency of waitinglist entry %d" % int(r["ID"]), "waitinglist.auto_update_urgencies", dbo) updates.append((now(dbo.timezone), add_days(r["URGENCYUPDATEDATE"], update_period_days), r["URGENCY"] - 1, r["ID"])) if len(updates) > 0: db.execute_many(dbo, "UPDATE animalwaitinglist SET " \ "UrgencyLastUpdatedDate=%s, " \ "UrgencyUpdateDate=%s, " \ "Urgency=%s " \ "WHERE ID=%s ", updates)
def scale_all_odt(dbo): """ Goes through all odt files attached to records in the database and scales them down (throws away images and objects so only the text remains to save space) """ mo = db.query(dbo, "SELECT MediaName FROM media WHERE LOWER(MediaName) LIKE '%.odt'") for i, m in enumerate(mo): name = str(m["MEDIANAME"]) al.debug("scaling %s (%d of %d)" % (name, i, len(mo)), "media.scale_all_odt", dbo) print "%s (%d of %d)" % (name, i, len(mo)) odata = dbfs.get_string(dbo, name) if odata == "": al.error("file %s does not exist" % name, "media.scale_all_odt", dbo) print "file %s does not exist" % name continue path = db.query_string(dbo, "SELECT Path FROM dbfs WHERE Name='%s'" % name) ndata = scale_odt(odata) if len(ndata) < 512: al.error("scaled odt %s came back at %d bytes, abandoning" % (name, len(ndata)), "scale_all_odt", dbo) print "file too small < 512, doing nothing" else: print "old size: %d, new size: %d" % (len(odata), len(ndata)) dbfs.put_string(dbo, name, path, ndata)
def post_animal_facebook(dbo, user, oauth_code, oauth_state): """ Post an animal to Facebook oauth_code: Provided by FB redirect, code to obtain user's access token oauth_state: Provided by FB redirect, from our original link the animal ID to post """ # Request an access token for the logged in Facebook user from the # oauth code we were given. try: client_id = FACEBOOK_CLIENT_ID client_secret = FACEBOOK_CLIENT_SECRET redirect_uri = urllib.quote(BASE_URL + "/animal_facebook") fb_url = "https://graph.facebook.com/oauth/access_token?client_id=%s&redirect_uri=%s&client_secret=%s&code=%s" % \ (client_id, redirect_uri, client_secret, oauth_code) al.debug("FB access token request: " + fb_url, "social.post_animal_facebook", dbo) access_token = urllib2.urlopen(fb_url).read() al.debug("FB access token response: " + access_token, "social.post_animal_facebook", dbo) except urllib2.HTTPError,herr: em = str(herr.read()) al.error("Failed getting facebook access token: %s" % em, "social.post_animal_facebook", dbo) raise utils.ASMValidationError("Failed getting Facebook access token.")
def auto_remove_old_incoming_forms(dbo): """ Automatically removes incoming forms older than the daily amount set """ removeafter = configuration.auto_remove_incoming_forms_days(dbo) if removeafter <= 0: al.debug("auto remove incoming forms is off.", "onlineform.auto_remove_old_incoming_forms") return removecutoff = i18n.subtract_days(i18n.now(dbo.timezone), removeafter) al.debug("remove date: incoming forms < %s" % db.dd(removecutoff), "onlineform.auto_remove_old_incoming_forms") sql = "DELETE FROM onlineformincoming WHERE PostedDate < %s" % db.dd(removecutoff) count = db.execute(dbo, sql) al.debug("removed %d incoming forms older than %d days" % (count, int(removeafter)), "onlineform.auto_remove_old_incoming_forms", dbo)