home *** CD-ROM | disk | FTP | other *** search
/ Maximum CD 2010 November / maximum-cd-2010-11.iso / DiscContents / xbmc-9.11.exe / scripts / AppleMovieTrailers / resources / lib / database.py < prev    next >
Encoding:
Python Source  |  2008-09-14  |  21.3 KB  |  446 lines

  1. """
  2. Database module
  3.  
  4. Nuka1195
  5. """
  6.  
  7. import sys
  8. import os
  9. import xbmc
  10. import xbmcgui
  11. from pysqlite2 import dbapi2 as sqlite
  12. import re
  13.  
  14. from utilities import *
  15.  
  16. _ = sys.modules[ "__main__" ].__language__
  17. __scriptname__ = sys.modules[ "__main__" ].__scriptname__
  18. __version__ = sys.modules[ "__main__" ].__version__
  19. __svn_revision__ = sys.modules[ "__main__" ].__svn_revision__
  20.  
  21.  
  22. class Database:
  23.     """ Main database class """
  24.  
  25.     def __init__( self, *args, **kwargs ):
  26.         self.query = Query()
  27.         self.db_version, self.complete = self._get_version()
  28.         if ( not self.db_version ):
  29.             LOG( LOG_ERROR, self.__class__.__name__, "Incompatible database!" )
  30.             raise
  31.  
  32.     def _get_version( self ):
  33.         records = Records()
  34.         record = records.fetch( self.query[ "version" ] )
  35.         records.close()
  36.         if ( record is not None ):
  37.             idVersion, version, complete = record
  38.             if ( version not in DATABASE_VERSIONS ): 
  39.                 version, complete = self._convert_database( version, complete )
  40.         else: version, complete = self._create_database()
  41.         return version, complete
  42.     
  43.     def _create_database( self ):
  44.         def _create_tables():
  45.             dialog = xbmcgui.DialogProgress()
  46.             def _progress_dialog( count=0 ):
  47.                 if ( not count ):
  48.                     dialog.create( _( 44 ) )
  49.                 elif ( count > 0 ):
  50.                     percent = int( count * ( float( 100 ) / len( records.tables.keys() ) ) )
  51.                     __line1__ = "%s: %s" % ( _( 47 ), table, )
  52.                     dialog.update( percent, __line1__ )
  53.                     if ( dialog.iscanceled() ): return False
  54.                     else: return True
  55.                 else:
  56.                     dialog.close()
  57.  
  58.             def _write_version():
  59.                 return records.add( "version", ( __version__, False, ), True )
  60.  
  61.             def _create_table( table ):
  62.                 try:
  63.                     sql = "CREATE TABLE %s (" % table
  64.                     for item in records.tables[ table ]:
  65.                         sql += "%s %s %s, " % ( item[ 0 ], item[ 1 ], item[ 2 ])
  66.                     sql = sql[ : -2 ].strip() + ");"
  67.                     records.db.execute( sql )
  68.                     for item in records.tables[ table ]:
  69.                         if ( item[ 3 ] != "" ):
  70.                             sql = "CREATE %s %s_%s_idx ON %s %s;" % ( item[ 3 ], table, item[0], table, item[4], )
  71.                             records.db.execute( sql )
  72.                     return True
  73.                 except: 
  74.                     return False
  75.             
  76.             try:
  77.                 _progress_dialog()
  78.                 records = Records()
  79.                 for count, table in enumerate( records.tables.keys() ):
  80.                     ok = _progress_dialog( count + 1 )
  81.                     ok = _create_table( table )
  82.                     if ( not ok ): raise
  83.                 ok = records.commit()
  84.                 ok = _write_version()
  85.                 records.close()
  86.                 _progress_dialog( -1 )
  87.                 if ( ok ): return __version__
  88.                 else: raise
  89.             except:
  90.                 records.close()
  91.                 _progress_dialog( -1 )
  92.                 xbmcgui.Dialog().ok( _( 44 ), _( 89 ) )
  93.                 return False
  94.         
  95.         version = _create_tables()
  96.         return version, False
  97.  
  98.     def _convert_database( self, version, complete ):
  99.         dialog = xbmcgui.DialogProgress()
  100.         def _progress_dialog( count=0, total_count=None, movie=None, title="" ):
  101.             __line1__ = _( 63 )
  102.             if ( not count ):
  103.                 dialog.create( title, _( 67 ) )# _( 59 ), __line1__ )
  104.             elif ( count > 0 ):
  105.                 percent = int( count * ( float( 100 ) / total_count ) )
  106.                 __line2__ = "%s: (%d of %d)" % ( _( 88 ), count, total_count, )
  107.                 __line3__ = movie[ 1 ]
  108.                 dialog.update( percent, __line1__, __line2__, __line3__ )
  109.                 if ( dialog.iscanceled() ): return False
  110.                 else: return True
  111.             else:
  112.                 dialog.close()
  113.         """
  114.         def _update_table():
  115.             try:
  116.                 sql = "ALTER TABLE genres ADD updated text"
  117.                 records = Records()
  118.                 records.cursor.execute( sql )
  119.                 records.close()
  120.                 return True
  121.             except: return False
  122.  
  123.         def _update_records_poster():
  124.             try:
  125.                 sql = "SELECT idMovie, title, poster, rating_url FROM movies ORDER BY title;"
  126.                 records = Records()
  127.                 movies = records.fetch( sql, all=True )
  128.                 total_count = len( movies )
  129.                 for count, movie in enumerate( movies ):
  130.                     ok = _progress_dialog( count + 1, total_count, movie )
  131.                     if ( movie[ 2 ] ): poster = os.path.basename( movie[ 2 ] )
  132.                     else: poster = ""
  133.                     if ( movie[ 3 ] ): rating_url = os.path.basename( movie[ 3 ] )
  134.                     else: rating_url = ""
  135.                     ok = records.update( "movies", ( "poster", "rating_url", ), ( poster, rating_url, movie[ 0 ], ), "idMovie" )
  136.                     if ( not ok ): raise
  137.                     if ( ( float( count + 1) / 100 == int( ( count + 1 ) / 100) ) or ( ( count + 1 ) == total_count ) ):
  138.                         ok = records.commit()
  139.                 records.close()
  140.                 return True
  141.             except:
  142.                 records.close()
  143.                 return False
  144.  
  145.         def _update_completed():
  146.             sql = "SELECT idMovie, trailer_urls FROM movies WHERE trailer_urls IS NULL ORDER BY title;"
  147.             records = Records()
  148.             movies = records.fetch( sql, all=True )
  149.             ok = True
  150.             updated = False
  151.             if ( movies is not None ):
  152.                 ok = records.update( "version", ( "complete", ), ( 0, 1, ), "idVersion", True )
  153.                 if ( ok ): updated = True
  154.             records.close()
  155.             return ok, updated
  156.         def _update_table_movies():
  157.             try:
  158.                 sql = "ALTER TABLE movies ADD saved_core integer"
  159.                 records = Records()
  160.                 records.cursor.execute( sql )
  161.                 records.close()
  162.                 return True
  163.             except: return False
  164.         """
  165.         def _fix_trailer_urls():
  166.             try:
  167.                 _progress_dialog( title=_( 68 ) )
  168.                 sql = "SELECT idMovie, title, trailer_urls FROM movies WHERE trailer_urls IS NOT NULL ORDER BY title;"
  169.                 records = Records()
  170.                 movies = records.fetch( sql, all=True )
  171.                 total_count = len( movies )
  172.                 for count, movie in enumerate( movies ):
  173.                     ok = _progress_dialog( count + 1, total_count, movie )
  174.                     if ( "UPDATE" in movie[ 2 ] ):
  175.                         ok = records.update( "movies", ( "trailer_urls", ), ( None, movie[ 0 ], ), "idMovie" )
  176.                     if ( not ok ): raise
  177.                     if ( ( float( count + 1) / 100 == int( ( count + 1 ) / 100) ) or ( ( count + 1 ) == total_count ) ):
  178.                         ok = records.commit()
  179.                 records.close()
  180.                 _progress_dialog( -99 )
  181.                 return True
  182.             except: 
  183.                 records.close()
  184.                 _progress_dialog( -99 )
  185.                 return False
  186.  
  187.         def _remove_xmls():
  188.             _progress_dialog( title=_( 36 ) )
  189.             filenames = os.walk( BASE_DATA_PATH )
  190.             for filename in filenames:
  191.                 for file in filename[ 2 ]:
  192.                     path = os.path.join( filename[ 0 ], file )
  193.                     if ( path.endswith( ".xml" ) ):
  194.                         try:
  195.                             os.remove( path )
  196.                         except:
  197.                             print "could not remove:", path
  198.             _progress_dialog( -1 )
  199.  
  200.         def _update_version():
  201.             records = Records()
  202.             ok = records.update( "version", ( "version", ), ( __version__, 1, ), "idVersion", True )
  203.             records.close()
  204.             return ok
  205.         
  206.         msg = ( _( 53 ), _( 54 ), )
  207.         """
  208.         if ( version in ( "pre-0.99.5a", "pre-0.99.5b", "pre-0.99.5c", ) ):
  209.             try:
  210.                 if ( version == "pre-0.99.5a" ):
  211.                     ok = _fix_trailer_urls()
  212.                     if ( not ok ): raise
  213.                 #if ( version in ( "pre-0.99.5a", "pre-0.99.5b", "pre-0.99.5c", ) ):
  214.                 #    _update_table_movies()
  215.                 ok = _update_version()
  216.                 if ( not ok ): raise
  217.             except:
  218.                 msg = ( _( 59 ), _( 46 ), )
  219.             #_progress_dialog( -1 )
  220.             if ( ok ): return ( __version__, complete )
  221.             else:
  222.                 xbmcgui.Dialog().ok( __scriptname__, msg[ 1 ], msg[ 2 ] )
  223.         else:
  224.         """
  225.         if ( version not in ( "pre-0.99.5a", "pre-0.99.5b", "pre-0.99.5c", "pre-0.99.6", "pre-0.99.7", "pre-0.99.7.1" ) ):
  226.             xbmcgui.Dialog().ok( __scriptname__, msg[ 1 ] )
  227.             _remove_xmls()
  228.             os.remove( BASE_DATABASE_PATH )
  229.             version, complete = self._create_database()
  230.         else:
  231.             ok = _update_version()
  232.         return ( __version__, complete )
  233.  
  234.  
  235. class Tables( dict ):
  236.     """ Database tables dictionary class """
  237.  
  238.     def __init__( self ):
  239.         #{ column name, type, auto increment, index , index columns }
  240.         self[ "version" ] = (
  241.             ( "idVersion", "integer PRIMARY KEY", "AUTOINCREMENT", "", "" ),
  242.             ( "version", "text", "", "", "" ),
  243.             ( "complete", "integer", "", "", "" ),
  244.         )
  245.         self[ "genres" ] = (
  246.             ( "idGenre", "integer PRIMARY KEY", "AUTOINCREMENT", "", "" ),
  247.             ( "genre", "text", "", "", "" ),
  248.             ( "urls", "blob", "", "", "" ),
  249.             ( "trailer_urls", "blob", "", "", "" ),
  250.             ( "updated", "text", "", "", "" ),
  251.         )
  252.         self[ "actors" ] = (
  253.             ( "idActor", "integer PRIMARY KEY", "AUTOINCREMENT", "", "" ),
  254.             ( "actor", "text", "", "", "" ),
  255.         )
  256.         self[ "studios" ] = ( 
  257.             ( "idStudio", "integer PRIMARY KEY", "AUTOINCREMENT", "", "" ),
  258.             ( "studio", "text", "", "", "" ),
  259.         )
  260.         self[ "movies" ] = (
  261.             ( "idMovie", "integer PRIMARY KEY", "AUTOINCREMENT", "", "" ), 
  262.             ( "title", "text", "", "", "" ),
  263.             ( "urls", "text",  "", "", "" ),
  264.             ( "trailer_urls", "text", "", "", "" ),
  265.             ( "poster", "text", "", "", "" ),
  266.             ( "plot", "text", "", "", "" ),
  267.             ( "runtime", "text", "", "", "" ),
  268.             ( "rating", "text", "", "", "" ),
  269.             ( "rating_url", "text", "", "", "" ),
  270.             ( "release_date", "text", "", "", "" ),
  271.             ( "times_watched", "integer", "", "", "" ),
  272.             ( "last_watched", "text", "", "", "" ),
  273.             ( "favorite", "integer", "", "", "" ),
  274.             ( "saved", "text", "", "", "" ),
  275.             ( "date_added", "text", "", "", "" ),
  276.         )
  277.         self[ "genre_link_movie" ] = ( 
  278.             ( "idGenre", "integer", "", "UNIQUE INDEX", "(idGenre, idMovie)" ),
  279.             ( "idMovie", "integer", "", "UNIQUE INDEX", "(idMovie, idGenre)" ),
  280.         )
  281.         self[ "actor_link_movie" ] = ( 
  282.             ( "idActor", "integer", "", "UNIQUE INDEX", "(idActor, idMovie)" ),
  283.             ( "idMovie", "integer", "", "UNIQUE INDEX", "(idMovie, idActor)" ),
  284.         )
  285.         self[ "studio_link_movie" ] = ( 
  286.             ( "idStudio", "integer", "", "UNIQUE INDEX", "(idStudio, idMovie)" ),
  287.             ( "idMovie", "integer", "", "UNIQUE INDEX", "(idMovie, idStudio)" ),
  288.         )
  289.  
  290.  
  291. class Records:
  292.     "add, delete, update and fetch records"
  293.  
  294.     def __init__( self, *args, **kwargs ):
  295.         self.tables = Tables()
  296.         self.connect()
  297.  
  298.     def connect( self ):
  299.         self.db = sqlite.connect( BASE_DATABASE_PATH )#, detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
  300.         self.db.create_function( "regexp", 2, self.regexp )
  301.         self.cursor = self.db.cursor()
  302.     
  303.     def regexp( self, pattern, item ):
  304.         return re.search( pattern, item, re.IGNORECASE ) is not None
  305.  
  306.     def commit( self ):
  307.         try:
  308.             self.db.commit()
  309.             return True
  310.         except: return False
  311.     
  312.     def close( self ):
  313.         self.db.close()
  314.     
  315.     def add( self, table, params, commit=False ):
  316.         try:
  317.             sql = "INSERT INTO %s (" % ( table, )
  318.             count = 0
  319.             for column in self.tables[ table ]:
  320.                 if ( column[ 2 ] != "AUTOINCREMENT" ):
  321.                     sql += "%s, " % column[ 0 ]
  322.                     count += 1
  323.                 if ( count == len( params ) ): break
  324.             sql = sql[ : -2 ] + ") VALUES (" + ( "?, " * len( params ) )
  325.             sql = sql[ : -2 ] + ");"
  326.             self.cursor.execute( sql, params )
  327.             if ( commit ): ok = self.commit()
  328.             return self.cursor.lastrowid
  329.         except:
  330.             LOG( LOG_ERROR, self.__class__.__name__, "[sql: %s -> %s]", sql, sys.exc_info()[ 1 ] )
  331.             return False
  332.  
  333.     def delete( self, table, columns, params, commit=False ):
  334.         try:
  335.             sql = "DELETE FROM %s WHERE " % table
  336.             for col in columns:
  337.                 sql += "%s=? AND " % col
  338.             sql = sql[ : -5 ]
  339.             self.cursor.execute( sql, params )
  340.             if ( commit ): ok = self.commit()
  341.             return True
  342.         except:
  343.             LOG( LOG_ERROR, self.__class__.__name__, "[sql: %s -> %s]", sql, sys.exc_info()[ 1 ] )
  344.             return False
  345.  
  346.     def update( self, table, columns, params, key, commit=False ):
  347.         try:
  348.             if ( isinstance( columns[ 0 ], int ) ):
  349.                 start_column = columns[ 0 ]
  350.                 if ( len( columns ) == 2 ):
  351.                     end_column = columns[ 1 ]
  352.                 else:
  353.                     end_column = len( self.tables[table] )
  354.                 columns = ()
  355.                 for item in self.tables[ table ][ start_column : end_column ]:
  356.                     columns += ( item[0], )
  357.             sql = "UPDATE %s SET " % ( table, )
  358.             for col in columns:
  359.                 sql += "%s=?, " % col
  360.             sql = sql[:-2] + " WHERE %s=?;" % ( key, )
  361.             self.cursor.execute( sql, params )
  362.             if ( commit ): ok = self.commit()
  363.             return True
  364.         except:
  365.             LOG( LOG_ERROR, self.__class__.__name__, "[sql: %s -> %s]", sql, sys.exc_info()[ 1 ] )
  366.             return False
  367.  
  368.     def fetch( self, sql, params=None, all=False ):
  369.         try:
  370.             if ( params is not None ): self.cursor.execute( sql , params )
  371.             else: self.cursor.execute( sql )
  372.             if ( all ): retval = self.cursor.fetchall()
  373.             else: retval = self.cursor.fetchone()
  374.         except:
  375.             retval = None
  376.         return retval
  377.  
  378.  
  379. class Query( dict ):
  380.     "all sql statments. add as needed"
  381.  
  382.     def __init__( self ):
  383.         #good sql statements
  384.         self[ "movie_by_movie_id" ]        = "SELECT * FROM movies WHERE idMovie=?;"
  385.         self[ "studio_by_movie_id" ]        = "SELECT studios.studio FROM studio_link_movie, studios WHERE studio_link_movie.idStudio = studios.idStudio AND studio_link_movie.idMovie=?;"
  386.         self[ "actors_by_movie_id" ]        = "SELECT actors.actor FROM actor_link_movie, actors WHERE actor_link_movie.idActor = actors.idActor AND actor_link_movie.idMovie=? ORDER BY actors.actor;"
  387.  
  388.         self[ "movies_by_genre_id" ]        = "SELECT movies.* FROM movies, genre_link_movie WHERE genre_link_movie.idMovie=movies.idMovie AND genre_link_movie.idGenre=? ORDER BY movies.title;"
  389.         self[ "movies_by_studio_id" ]        = "SELECT movies.* FROM movies, studio_link_movie WHERE studio_link_movie.idMovie=movies.idMovie AND studio_link_movie.idStudio=? ORDER BY movies.title;"
  390.         self[ "movies_by_actor_id" ]        = "SELECT movies.* FROM movies, actor_link_movie WHERE actor_link_movie.idMovie=movies.idMovie AND actor_link_movie.idActor=? ORDER BY movies.title;"
  391.  
  392.         self[ "movies_by_genre_name" ]    = "SELECT movies.* FROM movies, genres, genre_link_movie WHERE genre_link_movie.idGenre=genres.idGenre AND genre_link_movie.idMovie=movies.idMovie AND genres.genre=? ORDER BY movies.title;"
  393.         self[ "movies_by_studio_name" ]= "SELECT movies.* FROM movies, studios, studio_link_movie WHERE studio_link_movie.idStudio=studios.idStudio AND studio_link_movie.idMovie=movies.idMovie AND studios.studio LIKE ? ORDER BY movies.title;"
  394.         self[ "movies_by_actor_name" ]    = "SELECT movies.* FROM movies, actors, actor_link_movie WHERE actor_link_movie.idActor=actors.idActor AND actor_link_movie.idMovie=movies.idMovie AND actors.actor LIKE ? ORDER BY movies.title;"
  395.  
  396.         self[ "incomplete_movies" ]        = "SELECT * FROM movies WHERE trailer_urls IS NULL ORDER BY title;"
  397.         self[ "version" ]                        = "SELECT * FROM version;"
  398.  
  399.         self[ "genre_category_list" ]        = "SELECT genres.idGenre, genres.genre, count(genre_link_movie.idGenre), count(movies.favorite) FROM genre_link_movie, genres, movies WHERE genre_link_movie.idGenre=genres.idGenre AND genre_link_movie.idMovie=movies.idMovie GROUP BY genres.genre;"
  400.         self[ "studio_category_list" ]        = "SELECT studios.idStudio, studios.studio, count(studio_link_movie.idStudio), count(studio_link_movie.idStudio) FROM studio_link_movie, studios WHERE studio_link_movie.idStudio=studios.idStudio GROUP BY upper(studios.studio);"
  401.         self[ "actor_category_list" ]        = "SELECT actors.idActor, actors.actor, count(actor_link_movie.idActor), count(actor_link_movie.idActor) FROM actor_link_movie, actors WHERE actor_link_movie.idActor=actors.idActor GROUP BY upper(actors.actor);"
  402.         self[ "rating_category_list" ]        = "SELECT rating, count(rating) FROM movies WHERE rating IS NOT NULL AND rating!='' GROUP BY rating;"
  403.  
  404.         self[ "genre_table_list" ]            = "SELECT idGenre, genre, updated FROM genres ORDER BY genre;"
  405.         self[ "genre_urls_by_genre_id" ]    = "SELECT urls FROM genres WHERE idGenre=?;"
  406.         self[ "idMovie_by_genre_id" ]        = "SELECT idMovie FROM genre_link_movie WHERE idGenre=?;"
  407.         self[ "idMovie_in_genre" ]            = "SELECT * FROM genre_link_movie WHERE idGenre=? AND idMovie=?;"
  408.  
  409.         self[ "movie_exists" ]                = "SELECT idMovie, urls, date_added FROM movies WHERE title LIKE ?;"
  410.         self[ "actor_exists" ]                = "SELECT idActor FROM actors WHERE actor LIKE ?;"
  411.         self[ "studio_exists" ]                = "SELECT idStudio FROM studios WHERE studio LIKE ?;"
  412.  
  413.         self[ "favorites" ]                        = "SELECT * FROM movies WHERE favorite=? ORDER BY title;"
  414.         self[ "downloaded" ]                    = "SELECT * FROM movies WHERE saved_location!=? ORDER BY title;"
  415.         self[ "watched" ]                        = "SELECT * FROM movies WHERE times_watched>? ORDER BY title;"
  416.         self[ "recently_added" ]            = "SELECT * FROM movies ORDER BY date_added DESC, title LIMIT 30;"
  417.         self[ "multiple_trailers" ]            = "SELECT * FROM movies WHERE trailer_urls LIKE '%%), (%%' ORDER BY title;"
  418.  
  419.         self[ "hd_trailers" ]                    = "SELECT * FROM movies WHERE trailer_urls LIKE ? ORDER BY title;"
  420.         self[ "no_trailer_urls" ]                = "SELECT * FROM movies WHERE (trailer_urls=? OR trailer_urls IS NULL) AND poster IS NOT NULL ORDER BY title;"
  421.         self[ "simple_search" ]                = """
  422.                                                         SELECT DISTINCT movies.*
  423.                                                         FROM movies
  424.                                                         JOIN actor_link_movie
  425.                                                         ON movies.idMovie=actor_link_movie.idMovie
  426.                                                         JOIN actors
  427.                                                         ON actor_link_movie.idActor=actors.idActor
  428.                                                         JOIN studio_link_movie
  429.                                                         ON movies.idMovie=studio_link_movie.idMovie
  430.                                                         JOIN studios
  431.                                                         ON studio_link_movie.idStudio=studios.idStudio
  432.                                                         JOIN genre_link_movie
  433.                                                         ON movies.idMovie=genre_link_movie.idMovie
  434.                                                         JOIN genres
  435.                                                         ON genre_link_movie.idGenre=genres.idGenre
  436.                                                         WHERE %s ORDER BY title;
  437.                                                         """ 
  438.  
  439.         self[ "genres_by_movie_id" ]        = """
  440.                                                         SELECT genres.genre 
  441.                                                         FROM movies, genres, genre_link_movie 
  442.                                                         WHERE movies.idMovie=? 
  443.                                                         AND genre_link_movie.idMovie=movies.idMovie 
  444.                                                         AND genre_link_movie.idGenre=genres.idGenre;
  445.                                                     """
  446.