def by_title(cls, keyword, locale=None):
"""Search a music with its title and localized title.
Whitespaces are ignored.
"""
"""# Search a music its title exactly matched to keyword"""
music = session.query(cls).filter(cls.title == keyword).first()
if music: return music
"""# Search a music its title includes keyword"""
reduced_keyword = keyword.replace(' ', '')
reduced_title = func.replace(cls.title, ' ', '')
music = session.query(cls).\
filter(or_(
cls.title.contains(keyword),
reduced_title.contains(reduced_keyword))).\
limit(1).first()
if music: return music
"""# Search a music its localized title includes keyword"""
if locale is None: return None
reduced_text = func.replace(cls.Localization.text, ' ', '')
music_data = session.query(cls.Localization).\
filter(cls.Localization.locale == locale).\
filter(or_(
cls.Localization.text.contains(keyword),
reduced_text.contains(reduced_keyword))).\
limit(1).first()
if music_data is not None:
return cls.by(music_id=music_data.music_id)
def get_data(self):
logging.debug("These queries will take a few mins to run.")
budget_query = self.session.query(
self.models.MovieInfo.movie_id,
func.max(
cast(
func.replace(
func.replace(self.models.MovieInfo.info, ",", ""), "$",
""), types.Numeric)).label('budget')).filter(
self.models.MovieInfo.movie_id.in_(self.movie_ids),
self.models.MovieInfo.info_type_id.in_(
[BUDGET_TYPE_ID]),
self.models.MovieInfo.info.like('$%'),
).group_by(self.models.MovieInfo.movie_id).subquery()
year_query = self.session.query(
self.models.MovieInfo.movie_id,
func.min(func.substr(
self.models.MovieInfo.info, -4)).label('release_year')).filter(
self.models.MovieInfo.movie_id.in_(self.movie_ids),
self.models.MovieInfo.info_type_id.in_([RELEASE_DATES_ID]),
self.models.MovieInfo.info.like('USA:%')).group_by(
self.models.MovieInfo.movie_id).subquery()
budget_alias = aliased(self.models.MovieInfo, budget_query)
year_alias = aliased(self.models.MovieInfo, year_query)
return self.session.query(
budget_query.columns.movie_id, budget_query.columns.budget,
year_query.columns.release_year).join(
year_alias,
year_alias.movie_id == budget_alias.movie_id).distinct(
self.models.MovieInfo.movie_id).filter(
self.models.MovieInfo.movie_id.in_(self.movie_ids), )
def get_data(self):
logging.debug("These queries will take a few mins to run.")
budget_query = self.session.query(
self.models.MovieInfo.movie_id,
func.max(
cast(
func.replace(
func.replace(self.models.MovieInfo.info, ",", ""),
"$", ""),
types.Numeric)
).label('budget')
).filter(
self.models.MovieInfo.movie_id.in_(self.movie_ids),
self.models.MovieInfo.info_type_id.in_([BUDGET_TYPE_ID]),
self.models.MovieInfo.info.like('$%'),
).group_by(self.models.MovieInfo.movie_id
).subquery()
year_query = self.session.query(
self.models.MovieInfo.movie_id,
func.min(
func.substr(self.models.MovieInfo.info, -4)
).label('release_year')
).filter(
self.models.MovieInfo.movie_id.in_(self.movie_ids),
self.models.MovieInfo.info_type_id.in_([RELEASE_DATES_ID]),
self.models.MovieInfo.info.like('USA:%')
).group_by(self.models.MovieInfo.movie_id
).subquery()
budget_alias = aliased(self.models.MovieInfo, budget_query)
year_alias = aliased(self.models.MovieInfo, year_query)
return self.session.query(
budget_query.columns.movie_id,
budget_query.columns.budget,
year_query.columns.release_year
).join(
year_alias, year_alias.movie_id == budget_alias.movie_id
).distinct(
self.models.MovieInfo.movie_id
).filter(
self.models.MovieInfo.movie_id.in_(self.movie_ids),
)
def run(self):
from tg import config
from sqlalchemy import create_engine
from sqlalchemy.sql import func, update
import transaction
if self.options.dburl is None:
load_config(self.options.config)
engine = config['pylons.app_globals'].sa_engine
else:
engine = create_engine(self.options.dburl)
print "updating ", engine
if self.command == 'update':
oldurl, newurl = self.args
if not (oldurl.endswith('/') and newurl.endswith('/')):
self.parser.error(
"Please end URLs with '/' for proper matching")
from bq.data_service.model.tag_model import taggable, values
#taggable.bind = engine
# pylint: disable=no-value-for-parameter
stmt = taggable.update ()\
.values (resource_value = func.replace (taggable.c.resource_value, oldurl, newurl))\
.where(taggable.c.resource_value.like (oldurl + '%'))
print stmt
engine.execute(stmt)
stmt = values.update() \
.values (valstr = func.replace (values.c.valstr, oldurl, newurl))\
.where(values.c.valstr.like (oldurl + '%'))
print stmt
engine.execute(stmt)
# Find all values
transaction.commit()
def ajax_html_dirlist(self, args):
p = args.get('p', '')
p = urllib.unquote_plus(p)
# Sanitize path
path = re.sub(r"/+", "/", p.rstrip('/')).rstrip('#')
# Sanitize physical dir
pdir = os.path.realpath(re.sub (r"/+", "/", self.config['rootdir'] + '/' + path)) + '/'
# The result has to start with the configured rootdir
if pdir[0:len(self.config['rootdir'])] != self.config['rootdir']:
pdir = os.path.realpath(self.config['rootdir']) + '/'
sql = select([func.substring_index(func.replace(self.db_songtable.c.path, pdir, '' ), '/', 1).label('npath')]).\
where(func.substring_index(func.replace(self.db_songtable.c.path, pdir, '' ), '/', 1) != '').distinct().order_by('npath')
rows = self.dbc.execute(sql).fetchall()
plroute = 'dir_playlist_%s' % self.config['plformat']
if len(rows) == 0:
return render_template('playdir.html', path=path, plroute=plroute)
else:
numpercol = ceil (len(rows) / 2.0)
return render_template('dirlist.html', rows=rows, numpercol=numpercol, path=path)
def by_keyword(cls, keyword, locale=None):
"""Search a music with its title, localized title, search alias.
Whitespaces are ignored
"""
"""# Search a music its title or localized text includes keyword"""
music = cls.by_title(keyword, locale)
if music: return music
def music_by_id(music_id):
return session.query(cls).filter(music_id=music_id).first()
"""# Search a music its alias includes keyword"""
reduced_keyword = keyword.replace(' ', '')
reduced_text = func.replace('text', ' ', '')
if locale is not None:
data = session.query(cls.Alias).\
filter(or_(
cls.Alias.locale == locale, cls.Alias.locale == None)).\
filter(or_(
cls.Alias.text.contains(keyword),
reduced_text.contains(reduced_keyword))).\
order_by(cls.Alias.priority).\
limit(1).first()
if data:
return music_by_id(data.music_id)
"""# Search a music its localized text includes keyword with unmatching
locales"""
data = session.query(cls.Localization).\
filter(or_(
cls.Localization.text.contains(keyword),
reduced_text.contains(reduced_keyword))).\
limit(1).first()
if data:
return music_by_id(data.music_id)
"""# Search a music its alias includes keyword with unmatching locales
"""
data = session.query(cls.Alias).\
filter(or_(
cls.Alias.text.contains(keyword),
reduced_text.contains(reduced_keyword))).\
order_by(cls.Alias.priority).\
limit(1).first()
if data:
return music_by_id(data.music_id)
async def csv_to_sqlite(conn: Connection) -> None:
latest_veekun_commit = ""
try:
latest_veekun_commit = (
subprocess.run(
["git", "rev-list", "-1", "HEAD", "--", "data/veekun"],
capture_output=True,
check=True,
)
.stdout.decode()
.strip()
)
db = Database.open("veekun")
with db.get_session() as session:
db_commit = session.query( # type: ignore # sqlalchemy
v.LatestCommit.commit_id
).scalar()
if db_commit == latest_veekun_commit:
return # database is already up-to-date, skip rebuild
except (
subprocess.SubprocessError, # generic subprocess error
FileNotFoundError, # git is not available
OperationalError, # table does not exist
):
pass # always rebuild on error
print("Rebuilding veekun database...")
open("./veekun.sqlite", "w").close() # truncate database
db = Database.open("veekun")
v.Base.metadata.create_all(db.engine)
tables_classes = {
obj.__tablename__: obj
for name, obj in inspect.getmembers(v)
if inspect.isclass(obj)
and obj.__module__ == v.__name__
and hasattr(obj, "__tablename__")
}
with db.get_session() as session:
if latest_veekun_commit:
session.add(v.LatestCommit(commit_id=latest_veekun_commit))
for table in v.Base.metadata.sorted_tables:
tname = table.key
if isfile("./data/veekun/" + tname + ".csv"):
with open("./data/veekun/" + tname + ".csv", "r") as f:
csv_data = csv.DictReader(f)
csv_keys = csv_data.fieldnames
if csv_keys is not None:
session.bulk_insert_mappings(
tables_classes[tname], [dict(i) for i in csv_data]
)
if "identifier" in csv_keys:
session.query(tables_classes[tname]).update(
{
tables_classes[tname].identifier: func.replace(
tables_classes[tname].identifier, "-", ""
)
},
synchronize_session=False,
)
print("Done.")
