def test_lower_than(dbsession):
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price <= 1500)
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'price <= 1500'
""") is True
results = q.all()
assert len(results) == 1
assert results[0].id == 2
#
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price <= 10000)
q = q.filter(Products.inventory_count <= 50)
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'price <= 10000' AND products.inventory_count <= 50
""") is True
results = q.all()
assert len(results) == 2
assert results[0].id == 1
def get_director_directed(director):
"""Returns a list of movies given a director"""
q = ZdbQuery(MetaMovies, session=db.session)
q = q.filter(MetaMovies.director.like(director))
results_imdb = q.all()
if not results_imdb:
return {"local": [], "imdb": []}
ids = [z.id for z in results_imdb]
results_imdb = sorted(results_imdb,
key=lambda x: x.rating,
reverse=True)
q = ZdbQuery(Files, session=db.session)
q = q.filter(Files.meta_movie_id.in_(ids))
q = q.filter(Files.file_size >= 134217728)
results_local = q.all()
# @TODO: migrate `meta_info` to JSONB so we get the #> operator
# with that we can DISTINCT on nested json key 'title'
# e.g: SELECT DISTINCT ON (files.meta_info#>'{ptn, title}')
# to prevent from returning duplicates in popcorn view.
# for now, lets just do this:
_names = []
_rtn = []
for result in results_local:
result.get_meta_movie()
if result.meta_movie.title not in _names:
_rtn.append(result)
_names.append(result.meta_movie.title)
_rtn = sorted(_rtn, key=lambda x: x.meta_movie.title, reverse=True)
return {"local": _rtn, "imdb": results_imdb}
def test_between(dbsession):
# integers
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price.between(9000, 20000))
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'price:9000 /to/ 20000'
""") is True
results = q.all()
assert len(results) == 2
assert results[0].price == 9900
assert results[1].price == 17000
# floats
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price.between(9899.5, 9901.75))
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'price:9899.5 /to/ 9901.75'
""") is True
results = q.all()
assert len(results) == 1
assert results[0].price == 9900
# invalid input
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price.between(9899.5, "10000"))
with pytest.raises(InvalidParameterException):
q.all()
def get_details(meta_movie_id):
"""returns movies given a movie_id"""
from findex_gui.controllers.search.search import SearchController
q = ZdbQuery(Files, session=db.session)
q = q.filter(Files.meta_movie_id == meta_movie_id)
results = q.all()
results = MetaPopcornController._assign_meta(results)
results = SearchController.assign_resource_objects(results)
return results
def get_director(search=None):
q = ZdbQuery(MetaMovies, session=db.session)
if search:
search = search.lower()
search = search.replace("*", "")
if not isinstance(search, str):
raise SearchException("search must be str")
q = q.filter(MetaMovies.director == ZdbLiteral("\"%s*\"" % search))
# q = q.filter(MetaMovies.director.distinct()) distinct not implmeneted yet in ZdbQuery
results = [z.director for z in q.all()]
return list(set(results))
def test_like_regexp(dbsession):
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.long_description.like(re.compile(r"c[a-z]pable")))
sql = query_to_sql(q)
assert validate_sql(sql, target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'long_description:~"c[a-z]pable"'
""") is True
results = q.all()
assert len(results) == 1
assert "capable" in results[0].long_description
def test_equals_not(dbsession):
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.author != "foo")
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'author != "foo"'
""") is True
results = q.all()
assert len(results) == 2
assert results[0].author == "admin"
def get_actors(search=None):
if len(search) < 3:
return []
q = ZdbQuery(MetaMovies, session=db.session)
if search:
search = search.lower()
search = search.replace("*", "")
if not isinstance(search, str):
raise SearchException("search must be str")
q = q.filter(MetaMovies.actors == ZdbLiteral("\"%s*\"" % search))
results = q.all()
actors = []
for result in results:
for actor in result.actors:
if search not in actor.lower():
continue
if actor not in actors:
actors.append(actor)
return actors
def get_resources(uid: int = None,
name: str = None,
address: str = None,
port: int = None,
limit: int = None,
offset: int = None,
by_owner: int = None,
search: str = None,
protocol: int = None,
scheduled: bool = None,
order_by: str = None):
"""
Fetches some resources
:param uid:
:param name:
:param address:
:param port:
:param limit:
:param offset:
:param by_owner:
:param protocol:
:param order_by: order_by a column
:param search: performs a fulltext search on column 'search' which
:param scheduled: filter on scheduled
includes: IP/DOMAIN, NAME, DISPLAY_URL, PROTOCOL
:return:
"""
# normal sqla or zdb?
if search and config("findex:elasticsearch:enabled"):
q = ZdbQuery(Resource, session=db.session)
else:
q = db.session.query(Resource)
if isinstance(by_owner, int):
q = q.filter(Resource.created_by_id == by_owner)
if isinstance(uid, int):
q = q.filter(Resource.id == uid)
if isinstance(protocol, int):
q = q.filter(Resource.protocol == protocol)
if isinstance(scheduled, bool):
if scheduled:
q = q.filter(Resource.date_crawl_next <= datetime.now())
else:
q = q.filter(Resource.date_crawl_end.isnot(None))
if isinstance(address, str) and address:
qs = Server.query
server = qs.filter(Server.address == address).first()
if not server:
raise Exception("Could not find server")
q = q.filter(Resource.server_id == server.id)
if isinstance(port, int):
q = q.filter(Resource.port == port)
if isinstance(search, str) and search:
q = q.filter(Resource.search.like(search))
if isinstance(name, str) and name:
qs = Server.query
server = qs.filter(Server.name == name).first()
if not server:
raise Exception("Could not find server")
q = q.filter(Resource.server_id == server.id)
if isinstance(order_by, str):
c = getattr(Resource, order_by)
q = q.order_by(desc(c))
if offset and isinstance(offset, int):
q = q.offset(offset)
if limit and isinstance(limit, int):
q = q.limit(limit)
return q.all()
def _search(**kwargs):
kwargs["key"] = CrawlController.make_valid_key(kwargs["key"])
if not kwargs["key"]:
raise Exception("Invalid search. Too short?")
q = ZdbQuery(Files, session=db.session) if config(
"findex:elasticsearch:enabled") else Files.query
# @TODO: filter by protocols / hosts
# only find files that are not in "temp" mode
# q = q.filter(Files.resource_id >= 1)
# ignores certain filters
ignore_filters = []
# filter only files/dirs
if kwargs.get("file_type"):
if "both" in kwargs["file_type"]:
pass
if "folders" in kwargs["file_type"]:
q = q.filter(Files.file_isdir == True)
ignore_filters.extend(
("file_size", "file_categories", "file_extensions"))
elif "files" in kwargs["file_type"]:
q = q.filter(Files.file_isdir == False)
# size
if kwargs["file_size"] and "file_size" not in ignore_filters:
try:
file_size = kwargs["file_size"].split("-")
if not len(file_size) == 2:
raise Exception()
if file_size[0] == "*":
q = q.filter(Files.file_size <= int(file_size[1]))
elif file_size[1] == "*":
q = q.filter(Files.file_size >= int(file_size[0]))
else:
q = q.filter(
Files.file_size.between(*[int(x) for x in file_size]))
except:
pass
# filter categories
filecategories = FileCategories()
cat_ids = []
cats = kwargs.get("file_categories", [])
cats = [] if cats is None else cats
for cat in cats:
cat_id = filecategories.id_by_name(cat)
if cat_id is None:
continue
cat_ids.append(FileCategories().id_by_name(cat))
if cat_ids and "file_categories" not in ignore_filters:
q = q.filter(Files.file_format.in_(cat_ids))
if not kwargs["file_categories"]:
file_categories = filecategories.get_names()
# filter extensions
if kwargs[
"file_extensions"] and "file_extensions" not in ignore_filters:
exts = []
for ext in kwargs["file_extensions"]:
if ext.startswith("."):
ext = ext[1:]
exts.append(ext)
q = q.filter(Files.file_ext.in_(exts))
if isinstance(kwargs["meta_movie_id"], int):
q = q.filter(Files.meta_movie_id == kwargs["meta_movie_id"])
# Search
if config("findex:elasticsearch:enabled"):
val = kwargs["key"]
else:
if kwargs["autocomplete"] or app.config["db_file_count"] > 5000000:
print("warning: too many rows, enable ElasticSearch")
val = "%s%%" % escape_like(kwargs["key"])
else:
val = "%%%s%%" % escape_like(kwargs["key"])
if val != "*":
q = q.filter(Files.searchable.like(val))
q = q.order_by(Files.file_size.desc())
# pagination
q = q.offset(kwargs["page"])
if kwargs["autocomplete"]:
q = q.limit(5)
# q = q.distinct(func.lower(Files.file_name))
q = q.distinct(Files.file_size)
else:
q = q.limit(kwargs["per_page"])
# fetch
try:
results = q.all()
except Exception as ex:
raise Exception(ex)
results = SearchController.assign_resource_objects(results)
return results
def load_new_db():
"""bad code"""
# handle POST file upload
def _err(msg=None):
if msg:
log_msg(str(msg), category="meta_import", level=3)
raise Exception(msg)
raise Exception("error")
if 'file' not in request.files:
_err()
file = request.files["file"]
if file.filename == "" or not file.filename.startswith("findex_meta_"):
_err("bad filename")
if not file:
_err("bad file")
if file.mimetype != "application/zip":
_err("bad mimetype")
filename = secure_filename(file.filename)
dirpath = "%s/meta/" % cwd()
destination = os.path.join(dirpath, filename)
file.save(destination)
os.popen("cd %s && unzip -o %s && rm %s" %
(dirpath, filename, filename)).read()
info = {}
try:
f = open("%sinfo.txt" % dirpath, "r")
info = json.loads(f.read())
f.close()
except Exception as ex:
_err("could not open %s: %s" % ("%sinfo.txt", str(ex)) % dirpath)
if "version" in info:
OptionsController.set("meta", info)
if os.path.isfile("%smeta_movies.txt" % dirpath):
db.session.query(MetaMovies).delete(synchronize_session=False)
db.session.commit()
db.session.flush()
f = open("%smeta_movies.txt" % dirpath, "r")
movies = f.readlines()
f.close()
movies = [json.loads(movie) for movie in movies]
# cleanup
os.popen("rm %smeta_movies.txt" % dirpath).read()
# fill table `MetaMovies`
objects = []
for movie in movies:
m = MetaMovies(title=movie["title"],
year=movie["year"],
rating=movie["rating"],
plot=movie["plot"],
director=movie["director"],
genres=movie["genres"],
actors=movie["actors"],
meta=movie.get("meta", {}))
objects.append(m)
db.session.bulk_save_objects(objects)
db.session.commit()
db.session.flush()
meta_movies = {
"%s:%d" % (k.title.lower(), k.year): k
for k in ZdbQuery(MetaMovies, session=db.session).all()
}
# 'relink' existing files to new metadata
q = ZdbQuery(Files, session=db.session)
q = q.filter(Files.file_format == 2)
q = q.filter(Files.meta_info != None)
q = q.filter(Files.file_size >= 134217728)
updates = []
for result in q.all():
if "ptn" not in result.meta_info:
continue
ptn = result.meta_info["ptn"]
if "year" in ptn and "title" in ptn:
uid = "%s:%d" % (ptn["title"].lower(), ptn["year"])
if uid in meta_movies:
updates.append(
"UPDATE files SET meta_movie_id=%d WHERE files.id=%d;"
% (meta_movies[uid].id, result.id))
if updates:
try:
db.session.execute(text("\n".join(updates))).fetchall()
except Exception as ex:
pass
db.session.commit()
db.session.flush()
return True
def search(actors: List = None,
genres: List = None,
min_rating: int = None,
director: str = None,
year: int = None,
offset: int = None,
limit: int = 12,
title=None):
if actors and not isinstance(actors, list):
raise SearchException("actors must be list")
if genres and not isinstance(genres, list):
raise SearchException("genres must be list")
if min_rating:
if not isinstance(min_rating, (int, float)):
raise SearchException("min_rating must be int")
if not min_rating > 0 or min_rating > 100:
raise SearchException("min_rating must be between 1-100")
if min_rating <= 10:
min_rating = int(min_rating * 10)
if director and not isinstance(director, str):
raise SearchException("director must be str")
if title and not isinstance(title, str):
raise SearchException("title must be str and not empty")
if title and len(title) < 3:
title = None
if year and not isinstance(year, int):
raise Exception("year must be int")
if offset and not isinstance(offset, int):
raise Exception("offset must be int")
if limit and not isinstance(limit, int):
raise Exception("limit must be int")
if actors or genres or min_rating or director or year or title:
q = ZdbQuery(MetaMovies, session=db.session)
else:
q = ZdbQuery(Files, session=db.session)
q = q.filter(Files.meta_movie_id != None)
q = q.filter(Files.file_size >= 134217728)
q = q.distinct(Files.meta_movie_id)
q = q.limit(100)
if offset:
q = q.offset(offset)
results = q.all()
results = MetaPopcornController._assign_meta(results)
return results
if actors:
q = q.filter(MetaMovies.actors.in_(actors))
if genres:
for i in genres:
q = q.filter(MetaMovies.genres == i)
if min_rating:
q = q.filter(MetaMovies.rating >= min_rating)
if year:
q = q.filter(MetaMovies.year == year)
if director:
q = q.filter(MetaMovies.director == director)
if title:
q = q.filter(MetaMovies.title == ZdbLiteral("\"*%s*\"" % title))
# q = q.filter(MetaMovies.title.like(escape_like(title)))
results = q.all()
if not results:
return []
ids = list(set([z.id for z in results]))
q = ZdbQuery(Files, session=db.session)
q = q.filter(Files.file_format == 2)
q = q.filter(Files.file_size >= 134217728)
q = q.filter(Files.meta_movie_id.in_(ids))
q = q.limit(100)
results = q.all()
_names = []
_rtn = []
for result in results:
result.get_meta_movie()
if result.meta_movie.title not in _names:
_rtn.append(result)
_names.append(result.meta_movie.title)
return _rtn