From a55a6475b943a804fd76b5181b7d82968c2ea096 Mon Sep 17 00:00:00 2001 From: Leo Moll Date: Mon, 22 Jan 2018 01:01:34 +0100 Subject: [plugin.video.mediathekview] 0.4.2 --- .../resources/lib/storemysql.py | 136 +++++++++++++++------ 1 file changed, 98 insertions(+), 38 deletions(-) (limited to 'plugin.video.mediathekview/resources/lib/storemysql.py') 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() -- cgit v1.2.3