'Add average rating to tag browser views'

def upgrade_version_11(self):

    def create_std_tag_browser_view(table_name, column_name, view_column_name, sort_column_name):
        script = u'\n                                                                DROP    VIEW    IF    EXISTS    tag_browser_{tn};\n                                                                CREATE    VIEW    tag_browser_{tn}    AS    SELECT\n                                                                                id,\n                                                                                {vcn},\n                                                                                (SELECT    COUNT(id)    FROM    books_{tn}_link    WHERE    {cn}={tn}.id)    count,\n                                                                                (SELECT    AVG(ratings.rating)\n                                                                                    FROM    books_{tn}_link    AS    tl,    books_ratings_link    AS    bl,    ratings\n                                                                                    WHERE    tl.{cn}={tn}.id    AND    bl.book=tl.book    AND\n                                                                                    ratings.id    =    bl.rating    AND    ratings.rating    <>    0)    avg_rating,\n                                                                                    {scn}    AS    sort\n                                                                FROM    {tn};\n                                                                DROP    VIEW    IF    EXISTS    tag_browser_filtered_{tn};\n                                                                CREATE    VIEW    tag_browser_filtered_{tn}    AS    SELECT\n                                                                                id,\n                                                                                {vcn},\n                                                                                (SELECT    COUNT(books_{tn}_link.id)    FROM    books_{tn}_link    WHERE\n                                                                                                {cn}={tn}.id    AND    books_list_filter(book))    count,\n                                                                                (SELECT    AVG(ratings.rating)\n                                                                                    FROM    books_{tn}_link    AS    tl,    books_ratings_link    AS    bl,    ratings\n                                                                                    WHERE    tl.{cn}={tn}.id    AND    bl.book=tl.book    AND\n                                                                                    ratings.id    =    bl.rating    AND    ratings.rating    <>    0    AND\n                                                                                    books_list_filter(bl.book))    avg_rating,\n                                                                                    {scn}    AS    sort\n                                                                FROM    {tn};\n\n                                                                '.format(tn=table_name, cn=column_name, vcn=view_column_name, scn=sort_column_name)
        self.db.execute(script)
    def create_cust_tag_browser_view(table_name, link_table_name):
        script = u'\n                                                                DROP    VIEW    IF    EXISTS    tag_browser_{table};\n                                                                CREATE    VIEW    tag_browser_{table}    AS    SELECT\n                                                                                id,\n                                                                                value,\n                                                                                (SELECT    COUNT(id)    FROM    {lt}    WHERE    value={table}.id)    count,\n                                                                                (SELECT    AVG(r.rating)\n                                                                                    FROM    {lt},\n                                                                                                        books_ratings_link    AS    bl,\n                                                                                                        ratings    AS    r\n                                                                                    WHERE    {lt}.value={table}.id    AND    bl.book={lt}.book    AND\n                                                                                                            r.id    =    bl.rating    AND    r.rating    <>    0)    avg_rating,\n                                                                                    value    AS    sort\n                                                                FROM    {table};\n\n                                                                DROP    VIEW    IF    EXISTS    tag_browser_filtered_{table};\n                                                                CREATE    VIEW    tag_browser_filtered_{table}    AS    SELECT\n                                                                                id,\n                                                                                value,\n                                                                                (SELECT    COUNT({lt}.id)    FROM    {lt}    WHERE    value={table}.id    AND\n                                                                                books_list_filter(book))    count,\n                                                                                (SELECT    AVG(r.rating)\n                                                                                    FROM    {lt},\n                                                                                                        books_ratings_link    AS    bl,\n                                                                                                        ratings    AS    r\n                                                                                    WHERE    {lt}.value={table}.id    AND    bl.book={lt}.book    AND\n                                                                                                            r.id    =    bl.rating    AND    r.rating    <>    0    AND\n                                                                                                            books_list_filter(bl.book))    avg_rating,\n                                                                                    value    AS    sort\n                                                                FROM    {table};\n                                                                '.format(lt=link_table_name, table=table_name)
        self.db.execute(script)
    for field in self.field_metadata.itervalues():
        if (field[u'is_category'] and (not field[u'is_custom']) and (u'link_column' in field)):
            table = self.db.get(u'SELECT    name    FROM    sqlite_master    WHERE    type="table"    AND    name=?', ((u'books_%s_link' % field[u'table']),), all=False)
            if (table is not None):
                create_std_tag_browser_view(field[u'table'], field[u'link_column'], field[u'column'], field[u'category_sort'])
    db_tables = self.db.get(u"SELECT    name    FROM    sqlite_master\n                                                                                                                                                    WHERE    type='table'\n                                                                                                                                                    ORDER    BY    name")
    tables = []
    for (table,) in db_tables:
        tables.append(table)
    for table in tables:
        link_table = (u'books_%s_link' % table)
        if (table.startswith(u'custom_column_') and (link_table in tables)):
            create_cust_tag_browser_view(table, link_table)
    self.db.execute(u'UPDATE    authors    SET    sort=author_to_author_sort(name)')
