def testParse(self): query_text = """ SELECT 'comment' AS type, date_added, post.title AS title, keyvalue.value || ': ' || comment.body AS body, '?Post:' || comment.post_id || '#Comments' AS url FROM comment LEFT JOIN json USING (json_id) LEFT JOIN json AS json_content ON (json_content.directory = json.directory AND json_content.file_name='content.json') LEFT JOIN keyvalue ON (keyvalue.json_id = json_content.json_id AND key = 'cert_user_id') LEFT JOIN post ON (comment.post_id = post.post_id) WHERE post.date_added > 123 ORDER BY date_added DESC LIMIT 20 """ query = DbQuery(query_text) assert query.parts["LIMIT"] == "20" assert query.fields["body"] == "keyvalue.value || ': ' || comment.body" assert re.sub("[ \r\n]", "", str(query)) == re.sub("[ \r\n]", "", query_text) query.wheres.append("body LIKE '%hello%'") assert "body LIKE '%hello%'" in str(query)
def actionFeedQuery(self, to, limit=10, day_limit=3): if "ADMIN" not in self.site.settings["permissions"]: return self.response(to, "FeedQuery not allowed") from Site import SiteManager rows = [] for address, site_data in self.user.sites.iteritems(): feeds = site_data.get("follow") if not feeds: continue for name, query_set in feeds.iteritems(): site = SiteManager.site_manager.get(address) try: query, params = query_set query_parts = query.split("UNION") for i, query_part in enumerate(query_parts): db_query = DbQuery(query_part) if day_limit: where = " WHERE %s > strftime('%%s', 'now', '-%s day')" % ( db_query.fields.get("date_added", "date_added"), day_limit) if "WHERE" in query_part: query_part = re.sub( "WHERE (.*?)(?=$| GROUP BY)", where + " AND (\\1)", query_part) else: query_part += where query_parts[i] = query_part query = " UNION ".join(query_parts) if ":params" in query: query = query.replace(":params", ",".join(["?"] * len(params))) res = site.storage.query( query + " ORDER BY date_added DESC LIMIT %s" % limit, params) else: res = site.storage.query( query + " ORDER BY date_added DESC LIMIT %s" % limit) except Exception, err: # Log error self.log.error("%s feed query %s error: %s" % (address, name, err)) continue for row in res: row = dict(row) if "date_added" not in row or row[ "date_added"] > time.time() + 120: continue # Feed item is in the future, skip it row["site"] = address row["feed_name"] = name rows.append(row)
def actionFeedSearch(self, to, search): if "ADMIN" not in self.site.settings["permissions"]: return self.response(to, "FeedSearch not allowed") from Site import SiteManager rows = [] num_sites = 0 s = time.time() for address, site in SiteManager.site_manager.list().iteritems(): if not site.storage.has_db: continue if site.storage.db: # Database loaded feeds = site.storage.db.schema.get("feeds") else: try: feeds = site.storage.loadJson("dbschema.json").get("feeds") except: continue if not feeds: continue num_sites += 1 for name, query in feeds.iteritems(): try: db_query = DbQuery(query) db_query.wheres.append( "%s LIKE ? OR %s LIKE ?" % (db_query.fields["body"], db_query.fields["title"])) db_query.parts["ORDER BY"] = "date_added DESC" db_query.parts["LIMIT"] = "30" search_like = "%" + search.replace(" ", "%") + "%" res = site.storage.query(str(db_query), [search_like, search_like]) except Exception, err: self.log.error("%s feed query %s error: %s" % (address, name, err)) continue for row in res: row = dict(row) if row["date_added"] > time.time() + 120: continue # Feed item is in the future, skip it row["site"] = address row["feed_name"] = name rows.append(row)
def actionFeedQuery(self, to, limit=10, day_limit=3): if "ADMIN" not in self.site.settings["permissions"]: return self.response(to, "FeedQuery not allowed") from Site import SiteManager rows = [] stats = [] total_s = time.time() num_sites = 0 for address, site_data in self.user.sites.iteritems(): feeds = site_data.get("follow") if not feeds: continue if type(feeds) is not dict: self.log.debug("Invalid feed for site %s" % address) continue num_sites += 1 for name, query_set in feeds.iteritems(): site = SiteManager.site_manager.get(address) if not site: continue s = time.time() try: query_raw, params = query_set query_parts = query_raw.split("UNION") for i, query_part in enumerate(query_parts): db_query = DbQuery(query_part) if day_limit: where = " WHERE %s > strftime('%%s', 'now', '-%s day')" % ( db_query.fields.get("date_added", "date_added"), day_limit) if "WHERE" in query_part: query_part = re.sub( "WHERE (.*?)(?=$| GROUP BY)", where + " AND (\\1)", query_part) else: query_part += where query_parts[i] = query_part query = " UNION ".join(query_parts) if ":params" in query: query = query.replace(":params", ",".join(["?"] * len(params))) res = site.storage.query( query + " ORDER BY date_added DESC LIMIT %s" % limit, params * query_raw.count(":params")) else: res = site.storage.query( query + " ORDER BY date_added DESC LIMIT %s" % limit) except Exception as err: # Log error self.log.error("%s feed query %s error: %s" % (address, name, Debug.formatException(err))) stats.append({ "site": site.address, "feed_name": name, "error": str(err), "query": query }) continue for row in res: row = dict(row) if row["date_added"] > 1000000000000: # Formatted as millseconds row["date_added"] = row["date_added"] / 1000 if "date_added" not in row or row[ "date_added"] > time.time() + 120: self.log.debug( "Newsfeed from the future from from site %s" % address) continue # Feed item is in the future, skip it row["site"] = address row["feed_name"] = name rows.append(row) stats.append({ "site": site.address, "feed_name": name, "taken": round(time.time() - s, 3) }) time.sleep(0.0001) return self.response( to, { "rows": rows, "stats": stats, "num": len(rows), "sites": num_sites, "taken": round(time.time() - total_s, 3) })
def actionFeedSearch(self, to, search): if "ADMIN" not in self.site.settings["permissions"]: return self.response(to, "FeedSearch not allowed") from Site import SiteManager rows = [] stats = [] num_sites = 0 total_s = time.time() search_text, filters = self.parseSearch(search) for address, site in SiteManager.site_manager.list().iteritems(): if not site.storage.has_db: continue if "site" in filters: if filters["site"].lower() not in [ site.address, site.content_manager.contents["content.json"].get( "title").lower() ]: continue if site.storage.db: # Database loaded feeds = site.storage.db.schema.get("feeds") else: try: feeds = site.storage.loadJson("dbschema.json").get("feeds") except: continue if not feeds: continue num_sites += 1 for name, query in feeds.iteritems(): s = time.time() try: db_query = DbQuery(query) params = [] # Filters if search_text: db_query.wheres.append("(%s LIKE ? OR %s LIKE ?)" % (db_query.fields["body"], db_query.fields["title"])) search_like = "%" + search_text.replace(" ", "%") + "%" params.append(search_like) params.append(search_like) if filters.get("type") and filters["type"] not in query: continue # Order db_query.parts["ORDER BY"] = "date_added DESC" db_query.parts["LIMIT"] = "30" res = site.storage.query(str(db_query), params) except Exception, err: self.log.error("%s feed query %s error: %s" % (address, name, Debug.formatException(err))) stats.append({ "site": site.address, "feed_name": name, "error": str(err), "query": query }) continue for row in res: row = dict(row) if row["date_added"] > time.time() + 120: continue # Feed item is in the future, skip it row["site"] = address row["feed_name"] = name rows.append(row) stats.append({ "site": site.address, "feed_name": name, "taken": round(time.time() - s, 3) })