summaryrefslogtreecommitdiff
path: root/plugin.video.mediathekview/resources/lib/storemysql.py
diff options
context:
space:
mode:
Diffstat (limited to 'plugin.video.mediathekview/resources/lib/storemysql.py')
-rw-r--r--plugin.video.mediathekview/resources/lib/storemysql.py136
1 files changed, 98 insertions, 38 deletions
diff --git a/plugin.video.mediathekview/resources/lib/storemysql.py b/plugin.video.mediathekview/resources/lib/storemysql.py
index e2419cc..9268eec 100644
--- a/plugin.video.mediathekview/resources/lib/storemysql.py
+++ b/plugin.video.mediathekview/resources/lib/storemysql.py
@@ -20,7 +20,7 @@ class StoreMySQL( object ):
# useful query fragments
self.sql_query_films = "SELECT film.id,`title`,`show`,`channel`,`description`,TIME_TO_SEC(`duration`) AS `seconds`,`size`,`aired`,`url_sub`,`url_video`,`url_video_sd`,`url_video_hd` FROM `film` LEFT JOIN `show` ON show.id=film.showid LEFT JOIN `channel` ON channel.id=film.channelid"
self.sql_query_filmcnt = "SELECT COUNT(*) FROM `film` LEFT JOIN `show` ON show.id=film.showid LEFT JOIN `channel` ON channel.id=film.channelid"
- self.sql_cond_recent = "( TIMESTAMPDIFF(HOUR,`aired`,CURRENT_TIMESTAMP()) < 24 )"
+ self.sql_cond_recent = "( TIMESTAMPDIFF(SECOND,{},CURRENT_TIMESTAMP()) <= {} )".format( "aired" if settings.recentmode == 0 else "film.dtCreated", settings.maxage )
self.sql_cond_nofuture = " AND ( ( `aired` IS NULL ) OR ( TIMESTAMPDIFF(HOUR,`aired`,CURRENT_TIMESTAMP()) > 0 ) )" if settings.nofuture else ""
self.sql_cond_minlength = " AND ( ( `duration` IS NULL ) OR ( TIME_TO_SEC(`duration`) >= %d ) )" % settings.minlength if settings.minlength > 0 else ""
@@ -48,17 +48,21 @@ class StoreMySQL( object ):
self.conn.close()
def Search( self, search, filmui ):
- self._Search_Condition( '( ( `title` LIKE "%%%s%%" ) OR ( `show` LIKE "%%%s%%" ) )' % ( search, search, ), filmui, True, True, self.settings.maxresults )
+ searchmask = '%' + search.decode('utf-8') + '%'
+ self._Search_Condition( '( ( `title` LIKE %s ) OR ( `show` LIKE %s ) )', ( searchmask, searchmask, ), filmui, True, True, self.settings.maxresults )
def SearchFull( self, search, filmui ):
- self._Search_Condition( '( ( `title` LIKE "%%%s%%" ) OR ( `show` LIKE "%%%s%%" ) ) OR ( `description` LIKE "%%%s%%") )' % ( search, search, search ), filmui, True, True, self.settings.maxresults )
+ searchmask = '%' + search.decode('utf-8') + '%'
+ self._Search_Condition( '( ( `title` LIKE %s ) OR ( `show` LIKE %s ) OR ( `description` LIKE %s ) )', ( searchmask, searchmask, searchmask ), filmui, True, True, self.settings.maxresults )
def GetRecents( self, channelid, filmui ):
- sql_cond_channel = ' AND ( film.channelid=' + str( channelid ) + ' ) ' if channelid != '0' else ''
- self._Search_Condition( self.sql_cond_recent + sql_cond_channel, filmui, True, False, 10000 )
+ if channelid != '0':
+ self._Search_Condition( self.sql_cond_recent + ' AND ( film.channelid=%s )', ( int( channelid ), ), filmui, True, False, 10000 )
+ else:
+ self._Search_Condition( self.sql_cond_recent, (), filmui, True, False, 10000 )
def GetLiveStreams( self, filmui ):
- self._Search_Condition( '( show.search="LIVESTREAM" )', filmui, False, False, 10000 )
+ self._Search_Condition( '( show.search="LIVESTREAM" )', (), filmui, False, False, 10000 )
def GetChannels( self, channelui ):
self._Channels_Condition( None, channelui )
@@ -70,18 +74,30 @@ class StoreMySQL( object ):
if self.conn is None:
return
try:
- condition = 'WHERE ( `channelid`=' + str( channelid ) + ' ) ' if channelid != '0' else ''
- self.logger.info( 'MySQL Query: {}',
- 'SELECT LEFT(`search`,1) AS letter,COUNT(*) AS `count` FROM `show` ' +
- condition +
- 'GROUP BY LEFT(search,1)'
- )
+ channelid = int( channelid )
cursor = self.conn.cursor()
- cursor.execute(
- 'SELECT LEFT(`search`,1) AS letter,COUNT(*) AS `count` FROM `show` ' +
- condition +
- 'GROUP BY LEFT(`search`,1)'
- )
+ if channelid != 0:
+ self.logger.info(
+ 'MySQL Query: SELECT LEFT(`search`,1) AS letter,COUNT(*) AS `count` FROM `show` WHERE ( `channelid`={} ) GROUP BY LEFT(search,1)',
+ channelid
+ )
+ cursor.execute( """
+ SELECT LEFT(`search`,1) AS `letter`,
+ COUNT(*) AS `count`
+ FROM `show`
+ WHERE ( `channelid`=%s )
+ GROUP BY LEFT(`search`,1)
+ """, ( channelid, ) )
+ else:
+ self.logger.info(
+ 'MySQL Query: SELECT LEFT(`search`,1) AS letter,COUNT(*) AS `count` FROM `show` GROUP BY LEFT(search,1)'
+ )
+ cursor.execute( """
+ SELECT LEFT(`search`,1) AS `letter`,
+ COUNT(*) AS `count`
+ FROM `show`
+ GROUP BY LEFT(`search`,1)
+ """ )
initialui.Begin( channelid )
for ( initialui.initial, initialui.count ) in cursor:
initialui.Add()
@@ -95,15 +111,57 @@ class StoreMySQL( object ):
if self.conn is None:
return
try:
- if channelid == '0' and self.settings.groupshows:
- query = 'SELECT GROUP_CONCAT(show.id),GROUP_CONCAT(`channelid`),`show`,GROUP_CONCAT(`channel`) FROM `show` LEFT JOIN `channel` ON channel.id=show.channelid WHERE ( `show` LIKE "%s%%" ) GROUP BY `show`' % initial
- elif channelid == '0':
- query = 'SELECT show.id,show.channelid,show.show,channel.channel FROM `show` LEFT JOIN channel ON channel.id=show.channelid WHERE ( `show` LIKE "%s%%" )' % initial
- else:
- query = 'SELECT show.id,show.channelid,show.show,channel.channel FROM `show` LEFT JOIN channel ON channel.id=show.channelid WHERE ( `channelid`=%s ) AND ( `show` LIKE "%s%%" )' % ( channelid, initial )
- self.logger.info( 'MySQL Query: {}', query )
+ channelid = int( channelid )
cursor = self.conn.cursor()
- cursor.execute( query )
+ if channelid == 0 and self.settings.groupshows:
+ cursor.execute( """
+ SELECT GROUP_CONCAT(show.id),
+ GROUP_CONCAT(`channelid`),
+ `show`,
+ GROUP_CONCAT(`channel`)
+ FROM `show`
+ LEFT JOIN `channel`
+ ON ( channel.id = show.channelid )
+ WHERE ( `show` LIKE %s )
+ GROUP BY `show`
+ """, ( initial + '%', ) )
+ elif channelid == 0:
+ cursor.execute( """
+ SELECT show.id,
+ show.channelid,
+ show.show,
+ channel.channel
+ FROM `show`
+ LEFT JOIN `channel`
+ ON ( channel.id = show.channelid )
+ WHERE ( `show` LIKE %s )
+ """, ( initial + '%', ) )
+ elif initial:
+ cursor.execute( """
+ SELECT show.id,
+ show.channelid,
+ show.show,
+ channel.channel
+ FROM `show`
+ LEFT JOIN `channel`
+ ON ( channel.id = show.channelid )
+ WHERE (
+ ( `channelid` = %s )
+ AND
+ ( `show` LIKE %s )
+ )
+ """, ( channelid, initial + '%', ) )
+ else:
+ cursor.execute( """
+ SELECT show.id,
+ show.channelid,
+ show.show,
+ channel.channel
+ FROM `show`
+ LEFT JOIN `channel`
+ ON ( channel.id = show.channelid )
+ WHERE ( `channelid` = %s )
+ """, ( channelid, ) )
showui.Begin( channelid )
for ( showui.id, showui.channelid, showui.show, showui.channel ) in cursor:
showui.Add()
@@ -118,13 +176,10 @@ class StoreMySQL( object ):
return
if showid.find( ',' ) == -1:
# only one channel id
- condition = '( `showid`=%s )' % showid
- showchannels = False
+ self._Search_Condition( '( `showid` = %s )', ( int( showid ), ), filmui, False, False, 10000 )
else:
# multiple channel ids
- condition = '( `showid` IN ( %s ) )' % showid
- showchannels = True
- self._Search_Condition( condition, filmui, False, showchannels, 10000 )
+ self._Search_Condition( '( `showid` IN ( {} ) )'.format( showid ), (), filmui, False, True, 10000 )
def _Channels_Condition( self, condition, channelui):
if self.conn is None:
@@ -132,11 +187,14 @@ class StoreMySQL( object ):
try:
if condition is None:
query = 'SELECT `id`,`channel`,0 AS `count` FROM `channel`'
+ qtail = ''
else:
- query = 'SELECT channel.id AS `id`,`channel`,COUNT(*) AS `count` FROM `film` LEFT JOIN `channel` ON channel.id=film.channelid WHERE ' + condition + ' GROUP BY channel.id'
- self.logger.info( 'MySQL Query: {}', query )
+ query = 'SELECT channel.id AS `id`,`channel`,COUNT(*) AS `count` FROM `film` LEFT JOIN `channel` ON channel.id=film.channelid'
+ qtail = ' WHERE ' + condition + self.sql_cond_nofuture + self.sql_cond_minlength + ' GROUP BY channel.id'
+ self.logger.info( 'MySQL Query: {}', query + qtail )
+
cursor = self.conn.cursor()
- cursor.execute( query )
+ cursor.execute( query + qtail )
channelui.Begin()
for ( channelui.id, channelui.channel, channelui.count ) in cursor:
channelui.Add()
@@ -146,7 +204,7 @@ class StoreMySQL( object ):
self.logger.error( 'Database error: {}', err )
self.notifier.ShowDatabaseError( err )
- def _Search_Condition( self, condition, filmui, showshows, showchannels, maxresults ):
+ def _Search_Condition( self, condition, params, filmui, showshows, showchannels, maxresults ):
if self.conn is None:
return
try:
@@ -164,7 +222,8 @@ class StoreMySQL( object ):
condition +
self.sql_cond_nofuture +
self.sql_cond_minlength +
- ' LIMIT {}'.format( maxresults + 1 ) if maxresults else ''
+ ' LIMIT {}'.format( maxresults + 1 ) if maxresults else '',
+ params
)
( results, ) = cursor.fetchone()
if maxresults and results > maxresults:
@@ -175,7 +234,8 @@ class StoreMySQL( object ):
condition +
self.sql_cond_nofuture +
self.sql_cond_minlength +
- ' LIMIT {}'.format( maxresults + 1 ) if maxresults else ''
+ ' LIMIT {}'.format( maxresults + 1 ) if maxresults else '',
+ params
)
filmui.Begin( showshows, showchannels )
for ( filmui.id, filmui.title, filmui.show, filmui.channel, filmui.description, filmui.seconds, filmui.size, filmui.aired, filmui.url_sub, filmui.url_video, filmui.url_video_sd, filmui.url_video_hd ) in cursor:
@@ -518,10 +578,10 @@ class StoreMySQL( object ):
cursor = self.conn.cursor()
cursor.callproc( 'ftInsertChannel', ( channel, ) )
for result in cursor.stored_results():
- for ( id, added ) in result:
+ for ( idd, added ) in result:
cursor.close()
self.conn.commit()
- return ( id, added )
+ return ( idd, added )
# should never happen
cursor.close()
self.conn.commit()
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-18 00:22:16 +0000