def update_latest_movies(self, url, params, request, response):
""" Update latest movies.
[Arguments]
imdbid_list: list
The imdbid list to update latest table.
[Return]
rowcount: int
The row count of 'movie_latest'.
"""
imdbid_list = params.get_list('imdbid_list', None)
# check argument 'imdbid_list'
if not imdbid_list:
raise RequestError(ERR_REQUEST_ARG, str_data=("argument 'imdbid_list' is need.",))
values = ''
for data in imdbid_list:
# check imdbid pattern
reslut = re.match('tt\d{7}', data[0])
if reslut is None:
raise RequestError(ERR_REQUEST_ARG, str_data=('imdbid_list(%s) is not imdbid format.' % data[0],))
values += "('%s', '%s')," % (data[0], data[1])
values = values[:-1]
# insert db
db_inst = PGClient()
db_inst.execute('truncate table movie_latest')
db_inst.execute('insert into movie_latest values %s' % values)
db_inst.commit()
cur = db_inst.execute('select count(*) from movie_latest')
rowcount = cur.fetchone()[0]
return Response(model_data={'rowcount': rowcount})
def run(self):
db_inst = PGClient(db=self.db, timeout=False)
try:
db_inst.execute(self.sqlcmd)
db_inst.commit()
self.done = True # task done
except Exception as e:
self.error = True
self.error_message = str(e)
log.send(LOG_ERROR, str(e), traceback=True)
def run(self):
patch_data = None
try:
patch_data = PatchData(DIR_TEMP_PATH, self.fileobj)
# patch db
db_inst = PGClient(self.patcher.db, timeout=False)
with open(patch_data.get_filepath(), mode='r') as f:
db_inst.execute('begin;')
# create temp table
db_inst.execute(self.patcher.temp_table_sqlcmd)
# file obj column order must match temp table column order
db_inst.copy_from(f, self.patcher.temp_table_name)
db_inst.execute(self.patcher.sqlcmd)
db_inst.execute('commit;')
# task done
self.done = True
except Exception as e:
self.error = True
self.error_message = e.message if e.message is not None else vars(e)
log.send(LOG_ERROR, self.error_message, traceback=True)
finally:
if patch_data is not None:
patch_data.remove()
def get_timestamp(self):
db_inst = PGClient(self.db, timeout=False)
cur = db_inst.execute('select max(udate) from %s' % (self.tablename, ))
timestamp = cur.fetchone()[0]
if timestamp is None:
timestamp = datetime.datetime.min
return timestamp
def search_tvs(keyword,
file_filter,
keyword_filter,
ordering=0,
since=None,
limit=10,
lang=None):
""" Search dramas information.
[Arguments]
keyword : list.
Search keyword.
file_filter : int. (Not implement)
File filter.
keyword_filter : list.
Keyword filter.
ordering : int.
Ordering rule.
since : int.
Response data start from.
limit : int.
Number of response rows to display.
lang : string.
To choose display content.
"""
db_inst = PGClient()
# Compose SQL
if ordering == 1:
ordering_sql = u"ORDER BY rdate ASC NULLS LAST"
elif ordering == 2:
ordering_sql = u"ORDER BY total_count DESC NULLS LAST, rdate DESC NULLS LAST"
elif ordering == 3:
ordering_sql = u"ORDER BY total_count ASC NULLS LAST, rdate ASC"
else:
ordering_sql = u"ORDER BY rdate DESC NULLS LAST"
sql = u'''
{0}
{1} {2} {3}
'''.format(
get_search_sql_without_order(lang, keyword, keyword_filter),
ordering_sql if ordering_sql else '',
'OFFSET %s' % since if since else '',
'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' %
MAX_RESPONSE_ROWS)
dramas = db_inst.execute(cmd=gen_fetch_dramas_sql(
dramas_sql=sql)).fetchall()
return {'metadata': dramas}
def update_latest_movies(self, url, params, request, response):
""" Update latest movies.
[Arguments]
imdbid_list: list
The imdbid list to update latest table.
[Return]
rowcount: int
The row count of 'movie_latest'.
"""
imdbid_list = params.get_list('imdbid_list', None)
# check argument 'imdbid_list'
if not imdbid_list:
raise RequestError(ERR_REQUEST_ARG,
str_data=("argument 'imdbid_list' is need.", ))
values = ''
for data in imdbid_list:
# check imdbid pattern
reslut = re.match('tt\d{7}', data[0])
if reslut is None:
raise RequestError(
ERR_REQUEST_ARG,
str_data=('imdbid_list(%s) is not imdbid format.' %
data[0], ))
values += "('%s', '%s')," % (data[0], data[1])
values = values[:-1]
# insert db
db_inst = PGClient()
db_inst.execute('truncate table movie_latest')
db_inst.execute('insert into movie_latest values %s' % values)
db_inst.commit()
cur = db_inst.execute('select count(*) from movie_latest')
rowcount = cur.fetchone()[0]
return Response(model_data={'rowcount': rowcount})
def search_tvs(keyword, file_filter, keyword_filter, ordering=0, since=None, limit=10, lang=None):
""" Search dramas information.
[Arguments]
keyword : list.
Search keyword.
file_filter : int. (Not implement)
File filter.
keyword_filter : list.
Keyword filter.
ordering : int.
Ordering rule.
since : int.
Response data start from.
limit : int.
Number of response rows to display.
lang : string.
To choose display content.
"""
db_inst = PGClient()
# Compose SQL
if ordering == 1:
ordering_sql = u"ORDER BY rdate ASC NULLS LAST"
elif ordering == 2:
ordering_sql = u"ORDER BY total_count DESC NULLS LAST, rdate DESC NULLS LAST"
elif ordering == 3:
ordering_sql = u"ORDER BY total_count ASC NULLS LAST, rdate ASC"
else:
ordering_sql = u"ORDER BY rdate DESC NULLS LAST"
sql = u'''
{0}
{1} {2} {3}
'''.format(
get_search_sql_without_order(lang, keyword, keyword_filter),
ordering_sql if ordering_sql else '',
'OFFSET %s' % since if since else '',
'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' % MAX_RESPONSE_ROWS
)
dramas = db_inst.execute(cmd=gen_fetch_dramas_sql(dramas_sql=sql)).fetchall()
return {
'metadata': dramas
}
def search_total(keyword, lang=None, db_inst=None):
keyword = list(set(keyword))
if not db_inst:
db_inst = PGClient(db='karaoke')
# Compose SQL
keyword_sql = u' OR '.join(
[u"songs.title ILIKE '%{0}%' OR songs.artist ILIKE '%{0}%'".format(escape_wildcard(k)) for k in keyword])
sql = u'''
SELECT COUNT(*)
FROM songs, song_content
WHERE songs.keymd5=song_content.keymd5 AND ({0})
'''.format(
keyword_sql
)
return db_inst.execute(cmd=sql).fetchone()[0]
def search_total(keyword, lang=None, db_inst=None):
keyword = list(set(keyword))
if not db_inst:
db_inst = PGClient(db='karaoke')
# Compose SQL
keyword_sql = u' OR '.join([
u"songs.title ILIKE '%{0}%' OR songs.artist ILIKE '%{0}%'".format(
escape_wildcard(k)) for k in keyword
])
sql = u'''
SELECT COUNT(*)
FROM songs, song_content
WHERE songs.keymd5=song_content.keymd5 AND ({0})
'''.format(keyword_sql)
return db_inst.execute(cmd=sql).fetchone()[0]
def search_total(keyword, lang=None, db_inst=None):
keyword = list(set(keyword))
if not db_inst:
db_inst = PGClient(db='av')
# Compose SQL
keyword_sql = u' OR '.join([
u"keywords ILIKE '%{0}%'".format(escape_wildcard(k)) for k in keyword
])
sql = u'''
SELECT COUNT(*)
FROM video, video_keyword
WHERE ({0}) AND video.id=video_keyword.id AND video.date <= '{1}'
'''.format(keyword_sql,
datetime.utcnow().isoformat().split('T')[0])
return db_inst.execute(cmd=sql).fetchone()[0]
def search_total(keyword, lang=None, db_inst=None):
keyword = list(set(keyword))
if not db_inst:
db_inst = PGClient(db='av')
# Compose SQL
keyword_sql = u' OR '.join(
[u"keywords ILIKE '%{0}%'".format(escape_wildcard(k)) for k in keyword])
sql = u'''
SELECT COUNT(*)
FROM video, video_keyword
WHERE ({0}) AND video.id=video_keyword.id AND video.date <= '{1}'
'''.format(
keyword_sql,
datetime.utcnow().isoformat().split('T')[0]
)
return db_inst.execute(cmd=sql).fetchone()[0]
def search_total(keyword, lang=None, db_inst=None):
""" Search dramas count.
[Arguments]
keyword : list.
Search keyword.
lang : string.
To choose display content.
"""
if not db_inst:
db_inst = PGClient()
# Compose SQL
sql = u'''
select count(*) from (
{0}
) as data
'''.format(get_search_sql_without_order(lang, keyword, None))
return db_inst.execute(cmd=sql).fetchone()[0]
def search_total(keyword, lang=None, db_inst=None):
keyword = list(set(keyword))
if not db_inst:
db_inst = PGClient()
# Compose SQL
sql_data = {}
keyword_sql = _gen_keyword_sql(keyword, sql_data)
sql = u'''
SELECT COUNT(*)
FROM movies
WHERE id IN (
SELECT id
FROM movie_keyword
WHERE source = '{1}' AND ({0}) @@ j_tsv
)
'''.format(keyword_sql, lang2source(lang))
return db_inst.execute(cmd=sql, data=sql_data).fetchone()[0]
def search_total(keyword, lang=None, db_inst=None):
""" Search dramas count.
[Arguments]
keyword : list.
Search keyword.
lang : string.
To choose display content.
"""
if not db_inst:
db_inst = PGClient()
# Compose SQL
sql = u'''
select count(*) from (
{0}
) as data
'''.format(
get_search_sql_without_order(lang, keyword, None)
)
return db_inst.execute(cmd=sql).fetchone()[0]
def search_total(keyword, lang=None, db_inst=None):
keyword = list(set(keyword))
if not db_inst:
db_inst = PGClient()
# Compose SQL
sql_data = {}
keyword_sql = _gen_keyword_sql(keyword, sql_data)
sql = u'''
SELECT COUNT(*)
FROM movies
WHERE id IN (
SELECT id
FROM movie_keyword
WHERE source = '{1}' AND ({0}) @@ j_tsv
)
'''.format(
keyword_sql,
lang2source(lang)
)
return db_inst.execute(cmd=sql, data=sql_data).fetchone()[0]
def get_TVs(self, url, params, request, response):
""" Get TVs information.
[API]
1) GET RESOURCE/${DRAMA_ID}
Get target dramas detail information.
2) GET RESOURCE?type=${TYPE}
Get custom information.
[Arguments]
url[0] : string.
Drama ID to find.
since : int string.
Response data start from.
limit : int string.
Number of response rows to display.
lang : string.
To choose display content.
drama_kind : int string.
To choose kind of dramas.
type : string.
The type to display information.
"""
# API args
drama_id = url[0] if len(url) else None
request_type = params.get_string('type', REQUEST_LATEST)
since = params.get_int('since', None)
lang = params.get_string('lang', None)
condition_sql = ''
condition_data = {}
ordering_sql = None
if drama_id: # Get one drama information
limit = 0
condition_sql = 'id=%(id)s'
condition_data = {'id': drama_id}
else: # Get drama list information
limit = params.get_limit('limit', 20)
kind = params.get_int('drama_kind', 1)
if kind not in xrange(0, 9):
raise RequestError(ERR_REQUEST_ARG, str_data=('drama_kind.'))
condition_sql = "kind='%s'" % kind
condition_sql = "%s AND source='%s'" % (condition_sql,
lang2source(lang))
condition_sql = "%s AND '%s' && ARRAY(SELECT json_object_keys(play_urls))" % (
condition_sql, self.sql_array_string())
if request_type == REQUEST_POPLAR: # Get popular drama information
ordering_sql = "ORDER BY total_count DESC NULLS LAST, rdate DESC NULLS LAST"
elif request_type == REQUEST_LATEST: # Get latest drama information
ordering_sql = 'ORDER BY rdate DESC NULLS LAST'
else:
raise RequestError(ERR_REQUEST_ARG, str_data=('type.'))
dramas_sql = u'''
SELECT *
FROM drama
WHERE {0}
{1} {2} {3}
'''.format(
condition_sql, ordering_sql if ordering_sql else '',
'OFFSET %s' % since if since else '', 'LIMIT %s' %
limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' %
MAX_RESPONSE_ROWS)
db_inst = PGClient()
cur = db_inst.execute(cmd=gen_fetch_dramas_sql(dramas_sql=dramas_sql),
data=condition_data)
if not cur.rowcount and drama_id:
raise RequestError(http_status=404)
return Response(model_data={
'data': cur.fetchall(),
'detail': False if drama_id else True
})
def search_movies(keyword,
file_filter,
keyword_filter,
ordering=0,
since=None,
limit=10,
lang=None):
""" Search movies information.
[Arguments]
keyword : list.
Search keyword.
file_filter : int. (Not implement)
File filter.
keyword_filter : list.
Keyword filter.
ordering : int.
Ordering rule.
since : int.
Response data start from.
limit : int.
Number of response rows to display.
lang : string.
To choose display content.
"""
keyword = list(set(keyword))
keyword_filter = list(set(keyword_filter))
db_inst = PGClient()
# Compose SQL
sql_data = {}
keyword_sql = _gen_keyword_sql(keyword, sql_data)
if keyword_filter:
keyword_filter_sql = _gen_keyword_sql(keyword_filter,
sql_data,
prefix_key='kf')
condition_sql = u'(%s) && (%s)' % (keyword_sql, keyword_filter_sql)
else:
condition_sql = keyword_sql
if ordering == 1:
ordering_sql = u"ORDER BY releasedate->>'Default' ASC NULLS LAST"
elif ordering == 2:
ordering_sql = u"ORDER BY total_count DESC NULLS LAST, releasedate->>'Default' DESC NULLS LAST"
elif ordering == 3:
ordering_sql = u"ORDER BY total_count ASC NULLS LAST, releasedate->>'Default' ASC"
else:
ordering_sql = u"ORDER BY releasedate->>'Default' DESC NULLS LAST"
sql = u'''
SELECT *
FROM movies
WHERE id IN (
SELECT id
FROM movie_keyword
WHERE source = '{1}' AND ({0}) @@ j_tsv
)
{2} {3} {4}
'''.format(
condition_sql, lang2source(lang), ordering_sql,
u'OFFSET %s' % since if since else '',
u'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' %
MAX_RESPONSE_ROWS)
movies = db_inst.execute(cmd=sql, data=sql_data).fetchall()
content = get_movies_content(movies_row=movies, db_inst=db_inst)
return {'metadata': movies, 'content': content}
def get_TVs(self, url, params, request, response):
""" Get TVs information.
[API]
1) GET RESOURCE/${DRAMA_ID}
Get target dramas detail information.
2) GET RESOURCE?type=${TYPE}
Get custom information.
[Arguments]
url[0] : string.
Drama ID to find.
since : int string.
Response data start from.
limit : int string.
Number of response rows to display.
lang : string.
To choose display content.
drama_kind : int string.
To choose kind of dramas.
type : string.
The type to display information.
"""
# API args
drama_id = url[0] if len(url) else None
request_type = params.get_string('type', REQUEST_LATEST)
since = params.get_int('since', None)
lang = params.get_string('lang', None)
condition_sql = ''
condition_data = {}
ordering_sql = None
if drama_id: # Get one drama information
limit = 0
condition_sql = 'id=%(id)s'
condition_data = {'id': drama_id}
else: # Get drama list information
limit = params.get_limit('limit', 20)
kind = params.get_int('drama_kind', 1)
if kind not in xrange(0, 9):
raise RequestError(ERR_REQUEST_ARG, str_data=('drama_kind.'))
condition_sql = "kind='%s'" % kind
condition_sql = "%s AND source='%s'" % (condition_sql, lang2source(lang))
condition_sql = "%s AND '%s' && ARRAY(SELECT json_object_keys(play_urls))" % (
condition_sql, self.sql_array_string()
)
if request_type == REQUEST_POPLAR: # Get popular drama information
ordering_sql = "ORDER BY total_count DESC NULLS LAST, rdate DESC NULLS LAST"
elif request_type == REQUEST_LATEST: # Get latest drama information
ordering_sql = 'ORDER BY rdate DESC NULLS LAST'
else:
raise RequestError(ERR_REQUEST_ARG, str_data=('type.'))
dramas_sql = u'''
SELECT *
FROM drama
WHERE {0}
{1} {2} {3}
'''.format(
condition_sql,
ordering_sql if ordering_sql else '',
'OFFSET %s' % since if since else '',
'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' % MAX_RESPONSE_ROWS
)
db_inst = PGClient()
cur = db_inst.execute(cmd=gen_fetch_dramas_sql(dramas_sql=dramas_sql), data=condition_data)
if not cur.rowcount and drama_id:
raise RequestError(http_status=404)
return Response(model_data={
'data': cur.fetchall(),
'detail': False if drama_id else True
})
def search_karaokes(keyword, file_filter, keyword_filter, ordering=0, since=None, limit=10, lang=None):
""" Search karaokes information.
[Arguments]
keyword : list.
Search keyword.
file_filter : int. (Not implement)
File filter.
keyword_filter : list.
Keyword filter.
ordering : int.
Ordering rule.
since : int.
Response data start from.
limit : int.
Number of response rows to display.
lang : string.
To choose display content.
"""
keyword = list(set(keyword))
keyword_filter = list(set(keyword_filter))
db_inst = PGClient(db='karaoke')
# Compose SQL
keyword_sql = u' OR '.join(
[u"songs.title ILIKE '%{0}%' OR songs.artist ILIKE '%{0}%'".format(escape_wildcard(k)) for k in keyword])
if keyword_filter:
keyword_filter_sql = u' OR '.join(
[u"songs.title ILIKE '%{0}%' OR songs.artist ILIKE '%{0}%'".format(escape_wildcard(k)) for k in keyword_filter]
)
condition_sql = '(%s) AND (%s)' % (keyword_sql, keyword_filter_sql)
else:
condition_sql = keyword_sql
if ordering == 1:
ordering_sql = u"ORDER BY title" # TODO
elif ordering == 2:
ordering_sql = u"ORDER BY total_count DESC NULLS LAST, title"
elif ordering == 3:
ordering_sql = u"ORDER BY total_count ASC NULLS LAST, title"
else:
ordering_sql = u"ORDER BY title" # TODO
# Note here duplicate field overlaped.
sql = u'''
SELECT songs.*, song_content.*
FROM songs, song_content
WHERE songs.keymd5=song_content.keymd5 AND ({0})
{1} {2} {3}
'''.format(
condition_sql,
ordering_sql,
u'OFFSET %s' % since if since else '',
u'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' % MAX_RESPONSE_ROWS
)
songs = db_inst.execute(cmd=sql).fetchall()
return {
'metadata': songs
}
def search_movies(keyword, file_filter, keyword_filter, ordering=0, since=None, limit=10, lang=None):
""" Search movies information.
[Arguments]
keyword : list.
Search keyword.
file_filter : int. (Not implement)
File filter.
keyword_filter : list.
Keyword filter.
ordering : int.
Ordering rule.
since : int.
Response data start from.
limit : int.
Number of response rows to display.
lang : string.
To choose display content.
"""
keyword = list(set(keyword))
keyword_filter = list(set(keyword_filter))
db_inst = PGClient()
# Compose SQL
sql_data = {}
keyword_sql = _gen_keyword_sql(keyword, sql_data)
if keyword_filter:
keyword_filter_sql = _gen_keyword_sql(keyword_filter, sql_data, prefix_key='kf')
condition_sql = u'(%s) && (%s)' % (keyword_sql, keyword_filter_sql)
else:
condition_sql = keyword_sql
if ordering == 1:
ordering_sql = u"ORDER BY releasedate->>'Default' ASC NULLS LAST"
elif ordering == 2:
ordering_sql = u"ORDER BY total_count DESC NULLS LAST, releasedate->>'Default' DESC NULLS LAST"
elif ordering == 3:
ordering_sql = u"ORDER BY total_count ASC NULLS LAST, releasedate->>'Default' ASC"
else:
ordering_sql = u"ORDER BY releasedate->>'Default' DESC NULLS LAST"
sql = u'''
SELECT *
FROM movies
WHERE id IN (
SELECT id
FROM movie_keyword
WHERE source = '{1}' AND ({0}) @@ j_tsv
)
{2} {3} {4}
'''.format(
condition_sql,
lang2source(lang),
ordering_sql,
u'OFFSET %s' % since if since else '',
u'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' % MAX_RESPONSE_ROWS
)
movies = db_inst.execute(cmd=sql, data=sql_data).fetchall()
content = get_movies_content(movies_row=movies, db_inst=db_inst)
return {
'metadata': movies,
'content': content
}
def search_avs(keyword,
file_filter,
keyword_filter,
ordering=0,
since=None,
limit=10,
lang=None):
""" Search AVs information.
[Arguments]
keyword : list.
Search keyword.
file_filter : int. (Not implement)
File filter.
keyword_filter : list.
Keyword filter.
ordering : int.
Ordering rule.
since : int.
Response data start from.
limit : int.
Number of response rows to display.
lang : string.
To choose display content.
"""
keyword = list(set(keyword))
keyword_filter = list(set(keyword_filter))
db_inst = PGClient(db='av')
# Compose SQL
keyword_sql = u' OR '.join([
u"keywords ILIKE '%{0}%'".format(escape_wildcard(k)) for k in keyword
])
if keyword_filter:
keyword_filter_sql = u' OR '.join([
u"keywords ILIKE '%{0}%'".format(escape_wildcard(k))
for k in keyword_filter
])
condition_sql = '(%s) AND (%s)' % (keyword_sql, keyword_filter_sql)
else:
condition_sql = keyword_sql
if ordering == 1:
ordering_sql = u"ORDER BY date ASC NULLS LAST"
elif ordering == 2:
ordering_sql = u"ORDER BY total_count DESC NULLS LAST, date DESC NULLS LAST"
elif ordering == 3:
ordering_sql = u"ORDER BY total_count ASC NULLS LAST, date ASC"
else:
ordering_sql = u"ORDER BY date DESC NULLS LAST"
sql = u'''
SELECT *
FROM video
WHERE id IN (
SELECT id
FROM video_keyword
WHERE {0}
)
AND date <= '{4}'
{1} {2} {3}
'''.format(
condition_sql, ordering_sql, u'OFFSET %s' % since if since else '',
u'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' %
MAX_RESPONSE_ROWS,
datetime.utcnow().isoformat().split('T')[0])
videos = db_inst.execute(cmd=sql).fetchall()
return {'metadata': videos}
def search_karaokes(keyword,
file_filter,
keyword_filter,
ordering=0,
since=None,
limit=10,
lang=None):
""" Search karaokes information.
[Arguments]
keyword : list.
Search keyword.
file_filter : int. (Not implement)
File filter.
keyword_filter : list.
Keyword filter.
ordering : int.
Ordering rule.
since : int.
Response data start from.
limit : int.
Number of response rows to display.
lang : string.
To choose display content.
"""
keyword = list(set(keyword))
keyword_filter = list(set(keyword_filter))
db_inst = PGClient(db='karaoke')
# Compose SQL
keyword_sql = u' OR '.join([
u"songs.title ILIKE '%{0}%' OR songs.artist ILIKE '%{0}%'".format(
escape_wildcard(k)) for k in keyword
])
if keyword_filter:
keyword_filter_sql = u' OR '.join([
u"songs.title ILIKE '%{0}%' OR songs.artist ILIKE '%{0}%'".format(
escape_wildcard(k)) for k in keyword_filter
])
condition_sql = '(%s) AND (%s)' % (keyword_sql, keyword_filter_sql)
else:
condition_sql = keyword_sql
if ordering == 1:
ordering_sql = u"ORDER BY title" # TODO
elif ordering == 2:
ordering_sql = u"ORDER BY total_count DESC NULLS LAST, title"
elif ordering == 3:
ordering_sql = u"ORDER BY total_count ASC NULLS LAST, title"
else:
ordering_sql = u"ORDER BY title" # TODO
# Note here duplicate field overlaped.
sql = u'''
SELECT songs.*, song_content.*
FROM songs, song_content
WHERE songs.keymd5=song_content.keymd5 AND ({0})
{1} {2} {3}
'''.format(
condition_sql, ordering_sql, u'OFFSET %s' % since if since else '',
u'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' %
MAX_RESPONSE_ROWS)
songs = db_inst.execute(cmd=sql).fetchall()
return {'metadata': songs}
def search_avs(keyword, file_filter, keyword_filter, ordering=0, since=None, limit=10, lang=None):
""" Search AVs information.
[Arguments]
keyword : list.
Search keyword.
file_filter : int. (Not implement)
File filter.
keyword_filter : list.
Keyword filter.
ordering : int.
Ordering rule.
since : int.
Response data start from.
limit : int.
Number of response rows to display.
lang : string.
To choose display content.
"""
keyword = list(set(keyword))
keyword_filter = list(set(keyword_filter))
db_inst = PGClient(db='av')
# Compose SQL
keyword_sql = u' OR '.join(
[u"keywords ILIKE '%{0}%'".format(escape_wildcard(k)) for k in keyword])
if keyword_filter:
keyword_filter_sql = u' OR '.join(
[u"keywords ILIKE '%{0}%'".format(escape_wildcard(k)) for k in keyword_filter])
condition_sql = '(%s) AND (%s)' % (keyword_sql, keyword_filter_sql)
else:
condition_sql = keyword_sql
if ordering == 1:
ordering_sql = u"ORDER BY date ASC NULLS LAST"
elif ordering == 2:
ordering_sql = u"ORDER BY total_count DESC NULLS LAST, date DESC NULLS LAST"
elif ordering == 3:
ordering_sql = u"ORDER BY total_count ASC NULLS LAST, date ASC"
else:
ordering_sql = u"ORDER BY date DESC NULLS LAST"
sql = u'''
SELECT *
FROM video
WHERE id IN (
SELECT id
FROM video_keyword
WHERE {0}
)
AND date <= '{4}'
{1} {2} {3}
'''.format(
condition_sql,
ordering_sql,
u'OFFSET %s' % since if since else '',
u'LIMIT %s' % limit if MAX_RESPONSE_ROWS > limit > 0 else 'LIMIT %s' % MAX_RESPONSE_ROWS,
datetime.utcnow().isoformat().split('T')[0]
)
videos = db_inst.execute(cmd=sql).fetchall()
return {
'metadata': videos
}