def toolbar_icon_clicked(self, widget, movie): if question(_('Are you sure you want to remove %d movies?') % self.app.total): session = self.db.Session() # first: remove all dependend data (associated tags, languages, ...) query = select([movies_table.c.movie_id]) # FIXME: self.app._search_conditions contains advfilter conditions only (no other filters) query = update_whereclause(query, self.app._search_conditions) query = query.where(movies_table.c.loaned==False) # don't delete loaned movies for movie_entry in session.execute(query): # tags query_movie_tags = delete(movie_tag_table) query_movie_tags = query_movie_tags.where(movie_tag_table.c.movie_id==movie_entry.movie_id) session.execute(query_movie_tags) # languages query_movie_lang = delete(movie_lang_table) query_movie_lang = query_movie_lang.where(movie_lang_table.c.movie_id==movie_entry.movie_id) session.execute(query_movie_lang) # TODO: removing posters if no longer used by another movie? # second: remove the movie entries query = delete(movies_table) # FIXME: self.app._search_conditions contains advfilter conditions only (no other filters) query = update_whereclause(query, self.app._search_conditions) query = query.where(movies_table.c.loaned==False) # don't delete loaned movies session.execute(query) session.commit() self.app.populate_treeview()
def get_query(self): t = db.metadata.tables tables = set() columns = [] for i in self.fields_to_export: table = 'movies' column = i.split('.') if len(column) > 1: table = column[0] column = column[1] if table not in t: log.warning("Wrong table name: %s", table) continue tables.add(table) # will be used to generate JOIN else: column = column[0] if column in t[table].columns: columns.append(t[table].columns[column]) else: log.warning("Wrong field name: %s", i) joins = [] if 'media' in tables: joins.append((t['media'], t['movies'].c.medium_id == t['media'].c.medium_id)) if 'collections' in tables: joins.append((t['collections'], t['movies'].c.collection_id == t['collections'].c.collection_id)) if 'volumes' in tables: joins.append((t['volumes'], t['movies'].c.volume_id == t['volumes'].c.volume_id)) if 'vcodecs' in tables: joins.append((t['vcodecs'], t['movies'].c.vcodec_id == t['vcodecs'].c.vcodec_id)) if joins: from_obj = [outerjoin(t['movies'], *(joins[0]))] for j in joins[1:]: from_obj.append(outerjoin(from_obj[-1], *j)) query = select(columns=columns, bind=self.db.session.bind, from_obj=from_obj, use_labels=True) else: query = select(columns=columns, bind=self.db.session.bind) query = update_whereclause(query, self.search_conditions) # save column names (will contain 'movies_title' or 'title' depending on how many tables were requested) self.exported_columns = query.columns return query
def toolbar_icon_clicked(self, widget, movie): if question(_('Are you sure you want to update %d movies?') % self.app.total): session = self.db.Session() update_query = update(movies_table, values={'seen': True}) # FIXME: self.app._search_conditions contains advfilter conditions only (no other filters) update_query = update_whereclause(update_query, self.app._search_conditions) session.execute(update_query) session.commit() self.app.populate_treeview() # update seen widget in the list
def export_to_document(self, document, mainelement): table_movies = db.metadata.tables['movies'] # create object columns = [ table_movies.c.movie_id, table_movies.c.number, table_movies.c.title, table_movies.c.o_title, table_movies.c.country, table_movies.c.year, table_movies.c.runtime, table_movies.c.classification, table_movies.c.genre, table_movies.c.region, table_movies.c.studio, table_movies.c.cast, table_movies.c.director, table_movies.c.plot, table_movies.c.notes, table_movies.c.loaned, table_movies.c.rating, table_movies.c.trailer, table_movies.c.image, table_movies.c.seen, table_movies.c.media_num, table_movies.c.poster_md5, table_movies.c.screenplay, table_movies.c.cameraman, table_movies.c.barcode ] # use outer join to media table to get the name of the media columns.append(db.metadata.tables['media'].c.name) media_join = outerjoin(db.metadata.tables['movies'], \ db.metadata.tables['media'], \ db.metadata.tables['movies'].c.medium_id==db.metadata.tables['media'].c.medium_id) # use outer join to collections table to get the name of the collection columns.append(db.metadata.tables['collections'].c.name) collection_join = media_join.outerjoin( \ db.metadata.tables['collections'], \ db.metadata.tables['movies'].c.collection_id==db.metadata.tables['collections'].c.collection_id) # use outer join to volumes table to get the name of the volume columns.append(db.metadata.tables['volumes'].c.name) volume_join = collection_join.outerjoin( \ db.metadata.tables['volumes'], \ db.metadata.tables['movies'].c.volume_id==db.metadata.tables['volumes'].c.volume_id) # use outer join to volumes table to get the name of the video codec columns.append(db.metadata.tables['vcodecs'].c.name) vcodec_join = volume_join.outerjoin( \ db.metadata.tables['vcodecs'], \ db.metadata.tables['movies'].c.vcodec_id==db.metadata.tables['vcodecs'].c.vcodec_id) # use outer join to posters table to get the poster image columns.append(db.metadata.tables['posters'].c.data) posters_join = vcodec_join.outerjoin( \ db.metadata.tables['posters'], \ db.metadata.tables['movies'].c.poster_md5==db.metadata.tables['posters'].c.md5sum) # fetch movie data moviesquery = select(columns=columns, from_obj=[ media_join, collection_join, volume_join, vcodec_join, posters_join ], bind=self.db.session.bind, use_labels=True) moviesquery = update_whereclause(moviesquery, self.search_conditions) self.process_movies(document, mainelement, moviesquery)
def get_query(self): tables = set() columns = [] for i in self.fields_to_export: table = "movies" column = i.split(".") if len(column) > 1: table = column[0] column = column[1] if table not in db.tables: log.warning("Wrong table name: %s", table) continue tables.add(table) # will be used to generate JOIN else: column = column[0] if column in db.tables[table].columns: columns.append(db.tables[table].columns[column]) else: log.warning("Wrong field name: %s", i) joins = [] if "media" in tables: joins.append((db.tables["media"], db.tables["movies"].c.medium_id == db.tables["media"].c.medium_id)) if "collections" in tables: joins.append( ( db.tables["collections"], db.tables["movies"].c.collection_id == db.tables["collections"].c.collection_id, ) ) if "volumes" in tables: joins.append((db.tables["volumes"], db.tables["movies"].c.volume_id == db.tables["volumes"].c.volume_id)) if "vcodecs" in tables: joins.append((db.tables["vcodecs"], db.tables["movies"].c.vcodec_id == db.tables["vcodecs"].c.vcodec_id)) if joins: from_obj = [outerjoin(db.tables["movies"], *(joins[0]))] for j in joins[1:]: from_obj.append(outerjoin(from_obj[-1], *j)) query = select(columns=columns, bind=self.db.session.bind, from_obj=from_obj, use_labels=True) else: query = select(columns=columns, bind=self.db.session.bind) query = update_whereclause(query, self.search_conditions) # save column names (will contain 'movies_title' or 'title' depending on how many tables were requested) self.exported_columns = query.columns return query
def create_select_query(self, columns, conditions, query): if not conditions: conditions = get_conditions(self.widgets["advfilter"]) if query is None: # initial query not set so create one if not columns: columns = get_select_columns(self.config) query = select(columns, bind=self.db.session.bind) self._search_conditions = conditions # save for later # TODO: remove after debugging: # from pprint import pprint # pprint(conditions) return sql.update_whereclause(query, conditions)
def toolbar_icon_clicked(self, widget, movie): if question( _('Are you sure you want to update %d movies?') % self.app.total): session = self.db.Session() update_query = update(movies_table, values={'seen': True}) # FIXME: self.app._search_conditions contains advfilter conditions only (no other filters) update_query = update_whereclause(update_query, self.app._search_conditions) session.execute(update_query) session.commit() self.app.populate_treeview() # update seen widget in the list
def export_to_document(self, document, mainelement): table_movies = db.metadata.tables['movies'] # create object columns = [table_movies.c.movie_id, table_movies.c.number, table_movies.c.title, table_movies.c.o_title, table_movies.c.country, table_movies.c.year, table_movies.c.runtime, table_movies.c.classification, table_movies.c.genre, table_movies.c.region, table_movies.c.studio, table_movies.c.cast, table_movies.c.director, table_movies.c.plot, table_movies.c.notes, table_movies.c.loaned, table_movies.c.rating, table_movies.c.trailer, table_movies.c.image, table_movies.c.seen, table_movies.c.media_num, table_movies.c.poster_md5, table_movies.c.screenplay, table_movies.c.cameraman, table_movies.c.barcode] # use outer join to media table to get the name of the media columns.append(db.metadata.tables['media'].c.name) media_join = outerjoin(db.metadata.tables['movies'], \ db.metadata.tables['media'], \ db.metadata.tables['movies'].c.medium_id==db.metadata.tables['media'].c.medium_id) # use outer join to collections table to get the name of the collection columns.append(db.metadata.tables['collections'].c.name) collection_join = media_join.outerjoin( \ db.metadata.tables['collections'], \ db.metadata.tables['movies'].c.collection_id==db.metadata.tables['collections'].c.collection_id) # use outer join to volumes table to get the name of the volume columns.append(db.metadata.tables['volumes'].c.name) volume_join = collection_join.outerjoin( \ db.metadata.tables['volumes'], \ db.metadata.tables['movies'].c.volume_id==db.metadata.tables['volumes'].c.volume_id) # use outer join to volumes table to get the name of the video codec columns.append(db.metadata.tables['vcodecs'].c.name) vcodec_join = volume_join.outerjoin( \ db.metadata.tables['vcodecs'], \ db.metadata.tables['movies'].c.vcodec_id==db.metadata.tables['vcodecs'].c.vcodec_id) # use outer join to posters table to get the poster image columns.append(db.metadata.tables['posters'].c.data) posters_join = vcodec_join.outerjoin( \ db.metadata.tables['posters'], \ db.metadata.tables['movies'].c.poster_md5==db.metadata.tables['posters'].c.md5sum) # fetch movie data moviesquery = select(columns=columns, from_obj=[media_join, collection_join, volume_join, vcodec_join, posters_join], bind=self.db.session.bind, use_labels = True) moviesquery = update_whereclause(moviesquery, self.search_conditions) self.process_movies(document, mainelement, moviesquery)
def toolbar_icon_clicked(self, widget, movie): if question( _('Are you sure you want to remove %d movies?') % self.app.total): session = self.db.Session() # first: remove all dependend data (associated tags, languages, ...) query = select([movies_table.c.movie_id]) # add conditions from simple filter change_filter_update_whereclause(self.app, query) # add conditions from adv filter query = update_whereclause(query, self.app._search_conditions) query = query.where( movies_table.c.loaned == False) # don't delete loaned movies log.debug(query) movie_ids = [] for movie_entry in session.execute(query): movie_ids.append(movie_entry.movie_id) # tags query_movie_tags = delete(movie_tag_table) query_movie_tags = query_movie_tags.where( movie_tag_table.c.movie_id == movie_entry.movie_id) log.debug(query_movie_tags) session.execute(query_movie_tags) # languages query_movie_lang = delete(movie_lang_table) query_movie_lang = query_movie_lang.where( movie_lang_table.c.movie_id == movie_entry.movie_id) log.debug(query_movie_lang) session.execute(query_movie_lang) # TODO: removing posters if no longer used by another movie? # second: remove the movie entries if len(movie_ids): query = delete(movies_table) # use the collected movie ids because other conditions are not true anymore # (f.e. tags are already deleted) query = query.where(movies_table.c.movie_id.in_(movie_ids)) log.debug(query) session.execute(query) session.commit() self.app.populate_treeview()
def toolbar_icon_clicked(self, widget, movie): if question(_('Are you sure you want to remove %d movies?') % self.app.total): session = self.db.Session() # first: remove all dependend data (associated tags, languages, ...) query = select([movies_table.c.movie_id]) # add conditions from simple filter change_filter_update_whereclause(self.app, query) # add conditions from adv filter query = update_whereclause(query, self.app._search_conditions) query = query.where(movies_table.c.loaned==False) # don't delete loaned movies log.debug(query) movie_ids = [] for movie_entry in session.execute(query): movie_ids.append(movie_entry.movie_id) # tags query_movie_tags = delete(movie_tag_table) query_movie_tags = query_movie_tags.where(movie_tag_table.c.movie_id==movie_entry.movie_id) log.debug(query_movie_tags) session.execute(query_movie_tags) # languages query_movie_lang = delete(movie_lang_table) query_movie_lang = query_movie_lang.where(movie_lang_table.c.movie_id==movie_entry.movie_id) log.debug(query_movie_lang) session.execute(query_movie_lang) # TODO: removing posters if no longer used by another movie? # second: remove the movie entries if len(movie_ids): query = delete(movies_table) # use the collected movie ids because other conditions are not true anymore # (f.e. tags are already deleted) query = query.where(movies_table.c.movie_id.in_(movie_ids)) log.debug(query) session.execute(query) session.commit() self.app.populate_treeview()
def populate(self, movies=None, where=None, qf=True):#{{{ if self.initialized is False: # dont try to fill movie list if Griffith is not initialized yet return False if qf and movies is None or isinstance(movies, Select): # if ".execute().fetchall()" not invoked on movies yet if not where: # due to possible 'seen', 'loaned', 'collection_id' in where import advfilter # saved in advfilter name = self.widgets['filter']['advfilter'].get_active_text()[:-3].decode('utf-8') # :-3 due to additional ' ' in the name if name: cond = self.db.session.query(db.Filter).filter_by(name=name).first() if not cond: cond = advfilter.get_def_conditions() else: cond = cond.data else: cond = advfilter.get_def_conditions() # add sorting from config sort_column_name = self.config.get('sortby', 'number', section='mainlist') sort_reverse = self.config.get('sortby_reverse', False, section='mainlist') if sort_reverse: cond['sort_by'] = set((sort_column_name + ' DESC', )) else: cond['sort_by'] = set((sort_column_name, )) # seen / loaned if self.widgets['menu']['loaned_movies'].get_active(): cond['loaned'] = True if self.widgets['menu']['not_seen_movies'].get_active(): cond["seen"] = False # collection pos = self.widgets['filter']['collection'].get_active() if pos >= 0: col_id = self.collection_combo_ids[pos] if col_id > 0: cond["collections"].add(col_id) movies = advfilter.create_select_query(self, None, cond, movies) else: # select sort column sort_column_name = self.config.get('sortby', 'number', section='mainlist') sort_reverse = self.config.get('sortby_reverse', False, section='mainlist') # explicit conditions, only empty dictionary needed to add the order by values cond = {} if sort_reverse: cond['sort_by'] = set((sort_column_name + ' DESC', )) else: cond['sort_by'] = set((sort_column_name, )) movies = sql.update_whereclause(movies, cond) # additional whereclause (volume_id, collection_id, ...) if where: for i in where: if i in db.Movie: movies.append_whereclause(db.Movie[i] == where[i]) movies = movies.execute().fetchall() self.total = len(movies) # disable refreshing while inserting self.widgets['treeview'].freeze_child_notify() self.widgets['treeview'].set_model(None) # save user sort column sort_column_id, order = self.treemodel.get_sort_column_id() # new treemodel (faster and prevents some problems) self.treemodel = gtk.TreeStore(str, gtk.gdk.Pixbuf, str, str, str, str, bool, str, str, int, str, str) # check preferences to hide or show columns if self.config.get('number', True, 'mainlist') == True: self.number_column.set_visible(True) else: self.number_column.set_visible(False) if self.config.get('otitle', True, 'mainlist') == True: self.otitle_column.set_visible(True) else: self.otitle_column.set_visible(False) if self.config.get('title', True, 'mainlist') == True: self.title_column.set_visible(True) else: self.title_column.set_visible(False) if self.config.get('director', True, 'mainlist') == True: self.director_column.set_visible(True) else: self.director_column.set_visible(False) if self.config.get('image', True, 'mainlist') == True: self.image_column.set_visible(True) else: self.image_column.set_visible(False) if self.config.get('genre', True, 'mainlist') == True: self.genre_column.set_visible(True) else: self.genre_column.set_visible(False) if self.config.get('seen', True, 'mainlist') == True: self.seen_column.set_visible(True) else: self.seen_column.set_visible(False) if self.config.get('year', True, 'mainlist') == True: self.year_column.set_visible(True) else: self.year_column.set_visible(False) if self.config.get('runtime', True, 'mainlist') == True: self.runtime_column.set_visible(True) else: self.runtime_column.set_visible(False) if self.config.get('rating', True, 'mainlist') == True: self.rating_column.set_visible(True) else: self.rating_column.set_visible(False) if self.config.get('created', True, 'mainlist') == True: self.created_column.set_visible(True) else: self.created_column.set_visible(False) if self.config.get('updated', True, 'mainlist') == True: self.updated_column.set_visible(True) else: self.updated_column.set_visible(False) for movie in movies: addmovie(self, movie) # restore user sort column if sort_column_id is not None: self.treemodel.set_sort_column_id(sort_column_id, order) # add new treemodel and allow refreshs again self.widgets['treeview'].set_model(self.treemodel) self.widgets['treeview'].thaw_child_notify() if self.total: self.widgets['treeview'].set_cursor_on_cell(0) self.count_statusbar()
def populate(self, movies=None, where=None, qf=True): # {{{ if self.initialized is False: # dont try to fill movie list if Griffith is not initialized yet return False if qf and movies is None or isinstance( movies, Select): # if ".execute().fetchall()" not invoked on movies yet if not where: # due to possible 'seen', 'loaned', 'collection_id' in where import advfilter # saved in advfilter name = self.widgets['filter']['advfilter'].get_active_text( )[:-3].decode('utf-8') # :-3 due to additional ' ' in the name if name: cond = self.db.session.query( db.Filter).filter_by(name=name).first() if not cond: cond = advfilter.get_def_conditions() else: cond = cond.data else: cond = advfilter.get_def_conditions() # add sorting from config sort_column_name = self.config.get('sortby', 'number', section='mainlist') sort_reverse = self.config.get('sortby_reverse', False, section='mainlist') if sort_reverse: cond['sort_by'] = set((sort_column_name + ' DESC', )) else: cond['sort_by'] = set((sort_column_name, )) # seen / loaned if self.widgets['menu']['loaned_movies'].get_active(): cond['loaned'] = True if self.widgets['menu']['not_seen_movies'].get_active(): cond["seen"] = False # collection pos = self.widgets['filter']['collection'].get_active() if pos >= 0: col_id = self.collection_combo_ids[pos] if col_id > 0: cond["collections"].add(col_id) movies = advfilter.create_select_query(self, None, cond, movies) else: # select sort column sort_column_name = self.config.get('sortby', 'number', section='mainlist') sort_reverse = self.config.get('sortby_reverse', False, section='mainlist') # explicit conditions, only empty dictionary needed to add the order by values cond = {} if sort_reverse: cond['sort_by'] = set((sort_column_name + ' DESC', )) else: cond['sort_by'] = set((sort_column_name, )) movies = sql.update_whereclause(movies, cond) # additional whereclause (volume_id, collection_id, ...) if where: for i in where: if i in db.Movie: movies.append_whereclause(db.Movie[i] == where[i]) movies = movies.execute().fetchall() self.total = len(movies) # disable refreshing while inserting self.widgets['treeview'].freeze_child_notify() self.widgets['treeview'].set_model(None) # save user sort column sort_column_id, order = self.treemodel.get_sort_column_id() # new treemodel (faster and prevents some problems) self.treemodel = gtk.TreeStore(str, gtk.gdk.Pixbuf, str, str, str, str, bool, str, str, int, str, str) # check preferences to hide or show columns if self.config.get('number', True, 'mainlist') == True: self.number_column.set_visible(True) else: self.number_column.set_visible(False) if self.config.get('otitle', True, 'mainlist') == True: self.otitle_column.set_visible(True) else: self.otitle_column.set_visible(False) if self.config.get('title', True, 'mainlist') == True: self.title_column.set_visible(True) else: self.title_column.set_visible(False) if self.config.get('director', True, 'mainlist') == True: self.director_column.set_visible(True) else: self.director_column.set_visible(False) if self.config.get('image', True, 'mainlist') == True: self.image_column.set_visible(True) else: self.image_column.set_visible(False) if self.config.get('genre', True, 'mainlist') == True: self.genre_column.set_visible(True) else: self.genre_column.set_visible(False) if self.config.get('seen', True, 'mainlist') == True: self.seen_column.set_visible(True) else: self.seen_column.set_visible(False) if self.config.get('year', True, 'mainlist') == True: self.year_column.set_visible(True) else: self.year_column.set_visible(False) if self.config.get('runtime', True, 'mainlist') == True: self.runtime_column.set_visible(True) else: self.runtime_column.set_visible(False) if self.config.get('rating', True, 'mainlist') == True: self.rating_column.set_visible(True) else: self.rating_column.set_visible(False) if self.config.get('created', True, 'mainlist') == True: self.created_column.set_visible(True) else: self.created_column.set_visible(False) if self.config.get('updated', True, 'mainlist') == True: self.updated_column.set_visible(True) else: self.updated_column.set_visible(False) for movie in movies: addmovie(self, movie) # restore user sort column if sort_column_id is not None: self.treemodel.set_sort_column_id(sort_column_id, order) # add new treemodel and allow refreshs again self.widgets['treeview'].set_model(self.treemodel) self.widgets['treeview'].thaw_child_notify() if self.total: self.widgets['treeview'].set_cursor_on_cell(0) self.count_statusbar()