summaryrefslogtreecommitdiff
path: root/plugin.video.mediathekview/resources/lib/storesqlite.py
diff options
context:
space:
mode:
Diffstat (limited to 'plugin.video.mediathekview/resources/lib/storesqlite.py')
-rw-r--r--plugin.video.mediathekview/resources/lib/storesqlite.py129
1 files changed, 93 insertions, 36 deletions
diff --git a/plugin.video.mediathekview/resources/lib/storesqlite.py b/plugin.video.mediathekview/resources/lib/storesqlite.py
index 601db67..90d10b1 100644
--- a/plugin.video.mediathekview/resources/lib/storesqlite.py
+++ b/plugin.video.mediathekview/resources/lib/storesqlite.py
@@ -23,7 +23,7 @@ class StoreSQLite( object ):
# useful query fragments
self.sql_query_films = "SELECT film.id,title,show,channel,description,duration,size,datetime(aired, 'unixepoch', 'localtime'),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 = "( ( UNIX_TIMESTAMP() - aired ) <= 86400 )"
+ self.sql_cond_recent = "( ( UNIX_TIMESTAMP() - {} ) <= {} )".format( "aired" if settings.recentmode == 0 else "film.dtCreated", settings.maxage )
self.sql_cond_nofuture = " AND ( ( aired IS NULL ) OR ( ( UNIX_TIMESTAMP() - aired ) > 0 ) )" if settings.nofuture else ""
self.sql_cond_minlength = " AND ( ( duration IS NULL ) OR ( duration >= %d ) )" % settings.minlength if settings.minlength > 0 else ""
@@ -55,17 +55,21 @@ class StoreSQLite( object ):
self.conn = None
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 ? ) OR ( show LIKE ? ) )', ( 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 ? ) OR ( show LIKE ? ) OR ( description LIKE ? ) )', ( 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=? )', ( 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 )
@@ -77,18 +81,28 @@ class StoreSQLite( object ):
if self.conn is None:
return
try:
- condition = 'WHERE ( channelid=' + str( channelid ) + ' ) ' if channelid != '0' else ''
- self.logger.info( 'SQlite Query: {}',
- 'SELECT SUBSTR(search,1,1),COUNT(*) FROM show ' +
- condition +
- 'GROUP BY LEFT(search,1)'
- )
+ channelid = int( channelid )
cursor = self.conn.cursor()
- cursor.execute(
- 'SELECT SUBSTR(search,1,1),COUNT(*) FROM show ' +
- condition +
- 'GROUP BY SUBSTR(search,1,1)'
- )
+ if channelid != 0:
+ self.logger.info(
+ 'SQlite Query: SELECT SUBSTR(search,1,1),COUNT(*) FROM show WHERE ( channelid={} ) GROUP BY LEFT(search,1)',
+ channelid
+ )
+ cursor.execute( """
+ SELECT SUBSTR(search,1,1),COUNT(*)
+ FROM show
+ WHERE ( channelid=? )
+ GROUP BY SUBSTR(search,1,1)
+ """, ( channelid, ) )
+ else:
+ self.logger.info(
+ 'SQlite Query: SELECT SUBSTR(search,1,1),COUNT(*) FROM show GROUP BY LEFT(search,1)'
+ )
+ cursor.execute( """
+ SELECT SUBSTR(search,1,1),COUNT(*)
+ FROM show
+ GROUP BY SUBSTR(search,1,1)
+ """ )
initialui.Begin( channelid )
for ( initialui.initial, initialui.count ) in cursor:
initialui.Add()
@@ -102,15 +116,57 @@ class StoreSQLite( 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( 'SQLite 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 ? )
+ 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 ? )
+ """, ( 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=? )
+ AND
+ ( show LIKE ? )
+ )
+ """, ( 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=? )
+ """, ( channelid, ) )
showui.Begin( channelid )
for ( showui.id, showui.channelid, showui.show, showui.channel ) in cursor:
showui.Add()
@@ -125,13 +181,10 @@ class StoreSQLite( object ):
return
if showid.find( ',' ) == -1:
# only one channel id
- condition = '( showid=%s )' % showid
- showchannels = False
+ self._Search_Condition( '( showid=? )', ( 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:
@@ -139,11 +192,13 @@ class StoreSQLite( 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'
- self.logger.info( 'SQLite 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 + ' GROUP BY channel'
+ self.logger.info( 'SQLite 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()
@@ -153,7 +208,7 @@ class StoreSQLite( 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:
@@ -172,7 +227,8 @@ class StoreSQLite( 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:
@@ -183,7 +239,8 @@ class StoreSQLite( object ):
condition +
self.sql_cond_nofuture +
self.sql_cond_minlength +
- ' LIMIT {}'.format( maxresults ) if maxresults else ''
+ ' LIMIT {}'.format( maxresults ) 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:
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-19 12:02:41 +0000