home *** CD-ROM | disk | FTP | other *** search
/ Maximum CD 2010 November / maximum-cd-2010-11.iso / DiscContents / calibre-0.7.13.msi / file_1359 (.txt) < prev    next >
Encoding:
Python Compiled Bytecode  |  2010-08-06  |  19.1 KB  |  128 lines
  1. # Source Generated with Decompyle++
  2. # File: in.pyc (Python 2.6)
  3.  
  4. from __future__ import with_statement
  5. __license__ = 'GPL v3'
  6. __copyright__ = '2010, Kovid Goyal <kovid@kovidgoyal.net>'
  7. __docformat__ = 'restructuredtext en'
  8.  
  9. class SchemaUpgrade(object):
  10.     
  11.     def __init__(self):
  12.         while True:
  13.             uv = self.user_version
  14.             meth = getattr(self, 'upgrade_version_%d' % uv, None)
  15.             if meth is None:
  16.                 break
  17.                 continue
  18.             print 'Upgrading database to version %d...' % (uv + 1)
  19.             meth()
  20.             self.user_version = uv + 1
  21.  
  22.     
  23.     def upgrade_version_1(self):
  24.         self.conn.executescript('        DROP INDEX authors_idx;\n        CREATE INDEX authors_idx ON books (author_sort COLLATE NOCASE, sort COLLATE NOCASE);\n        DROP INDEX series_idx;\n        CREATE INDEX series_idx ON series (name COLLATE NOCASE);\n        CREATE INDEX series_sort_idx ON books (series_index, id);\n        ')
  25.  
  26.     
  27.     def upgrade_version_2(self):
  28.         script = "        DROP TRIGGER IF EXISTS fkc_delete_books_%(ltable)s_link;\n        CREATE TRIGGER fkc_delete_on_%(table)s\n        BEFORE DELETE ON %(table)s\n        BEGIN\n            SELECT CASE\n                WHEN (SELECT COUNT(id) FROM books_%(ltable)s_link WHERE %(ltable_col)s=OLD.id) > 0\n                THEN RAISE(ABORT, 'Foreign key violation: %(table)s is still referenced')\n            END;\n        END;\n        DELETE FROM %(table)s WHERE (SELECT COUNT(id) FROM books_%(ltable)s_link WHERE %(ltable_col)s=%(table)s.id) < 1;\n        "
  29.         self.conn.executescript(script % dict(ltable = 'authors', table = 'authors', ltable_col = 'author'))
  30.         self.conn.executescript(script % dict(ltable = 'publishers', table = 'publishers', ltable_col = 'publisher'))
  31.         self.conn.executescript(script % dict(ltable = 'tags', table = 'tags', ltable_col = 'tag'))
  32.         self.conn.executescript(script % dict(ltable = 'series', table = 'series', ltable_col = 'series'))
  33.  
  34.     
  35.     def upgrade_version_3(self):
  36.         self.conn.executescript('\n        DROP VIEW meta;\n        CREATE VIEW meta AS\n        SELECT id, title,\n               (SELECT concat(name) FROM authors WHERE authors.id IN (SELECT author from books_authors_link WHERE book=books.id)) authors,\n               (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,\n               (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,\n               timestamp,\n               (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,\n               (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,\n               (SELECT text FROM comments WHERE book=books.id) comments,\n               (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,\n               series_index,\n               sort,\n               author_sort,\n               (SELECT concat(format) FROM data WHERE data.book=books.id) formats,\n               isbn,\n               path\n        FROM books;\n        ')
  37.  
  38.     
  39.     def upgrade_version_4(self):
  40.         self.conn.executescript('\n        BEGIN TRANSACTION;\n        CREATE TEMPORARY TABLE\n        books_backup(id,title,sort,timestamp,series_index,author_sort,isbn,path);\n        INSERT INTO books_backup SELECT id,title,sort,timestamp,series_index,author_sort,isbn,path FROM books;\n        DROP TABLE books;\n        CREATE TABLE books ( id      INTEGER PRIMARY KEY AUTOINCREMENT,\n                             title     TEXT NOT NULL DEFAULT \'Unknown\' COLLATE NOCASE,\n                             sort      TEXT COLLATE NOCASE,\n                             timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n                             pubdate   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n                             series_index REAL NOT NULL DEFAULT 1.0,\n                             author_sort TEXT COLLATE NOCASE,\n                             isbn TEXT DEFAULT "" COLLATE NOCASE,\n                             lccn TEXT DEFAULT "" COLLATE NOCASE,\n                             path TEXT NOT NULL DEFAULT "",\n                             flags INTEGER NOT NULL DEFAULT 1\n                        );\n        INSERT INTO\n            books (id,title,sort,timestamp,pubdate,series_index,author_sort,isbn,path)\n            SELECT id,title,sort,timestamp,timestamp,series_index,author_sort,isbn,path FROM books_backup;\n        DROP TABLE books_backup;\n\n        DROP VIEW meta;\n        CREATE VIEW meta AS\n        SELECT id, title,\n               (SELECT concat(name) FROM authors WHERE authors.id IN (SELECT author from books_authors_link WHERE book=books.id)) authors,\n               (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,\n               (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,\n               timestamp,\n               (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,\n               (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,\n               (SELECT text FROM comments WHERE book=books.id) comments,\n               (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,\n               series_index,\n               sort,\n               author_sort,\n               (SELECT concat(format) FROM data WHERE data.book=books.id) formats,\n               isbn,\n               path,\n               lccn,\n               pubdate,\n               flags\n        FROM books;\n        ')
  41.  
  42.     
  43.     def upgrade_version_5(self):
  44.         self.conn.executescript('\n        BEGIN TRANSACTION;\n        CREATE INDEX authors_idx ON books (author_sort COLLATE NOCASE);\n        CREATE INDEX books_idx ON books (sort COLLATE NOCASE);\n        CREATE TRIGGER books_delete_trg\n            AFTER DELETE ON books\n            BEGIN\n                DELETE FROM books_authors_link WHERE book=OLD.id;\n                DELETE FROM books_publishers_link WHERE book=OLD.id;\n                DELETE FROM books_ratings_link WHERE book=OLD.id;\n                DELETE FROM books_series_link WHERE book=OLD.id;\n                DELETE FROM books_tags_link WHERE book=OLD.id;\n                DELETE FROM data WHERE book=OLD.id;\n                DELETE FROM comments WHERE book=OLD.id;\n                DELETE FROM conversion_options WHERE book=OLD.id;\n        END;\n        CREATE TRIGGER books_insert_trg\n            AFTER INSERT ON books\n            BEGIN\n            UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;\n        END;\n        CREATE TRIGGER books_update_trg\n            AFTER UPDATE ON books\n            BEGIN\n            UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;\n        END;\n\n        UPDATE books SET sort=title_sort(title) WHERE sort IS NULL;\n\n        END TRANSACTION;\n        ')
  45.  
  46.     
  47.     def upgrade_version_6(self):
  48.         self.conn.executescript('\n        BEGIN TRANSACTION;\n        DROP VIEW meta;\n        CREATE VIEW meta AS\n        SELECT id, title,\n               (SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,\n               (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,\n               (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,\n               timestamp,\n               (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,\n               (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,\n               (SELECT text FROM comments WHERE book=books.id) comments,\n               (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,\n               series_index,\n               sort,\n               author_sort,\n               (SELECT concat(format) FROM data WHERE data.book=books.id) formats,\n               isbn,\n               path,\n               lccn,\n               pubdate,\n               flags\n        FROM books;\n        END TRANSACTION;\n        ')
  49.  
  50.     
  51.     def upgrade_version_7(self):
  52.         self.conn.executescript('\n        BEGIN TRANSACTION;\n        ALTER TABLE books ADD COLUMN uuid TEXT;\n        DROP TRIGGER IF EXISTS books_insert_trg;\n        DROP TRIGGER IF EXISTS books_update_trg;\n        UPDATE books SET uuid=uuid4();\n\n        CREATE TRIGGER books_insert_trg AFTER INSERT ON books\n        BEGIN\n            UPDATE books SET sort=title_sort(NEW.title),uuid=uuid4() WHERE id=NEW.id;\n        END;\n\n        CREATE TRIGGER books_update_trg AFTER UPDATE ON books\n        BEGIN\n            UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;\n        END;\n\n        DROP VIEW meta;\n        CREATE VIEW meta AS\n        SELECT id, title,\n               (SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,\n               (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,\n               (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,\n               timestamp,\n               (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,\n               (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,\n               (SELECT text FROM comments WHERE book=books.id) comments,\n               (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,\n               series_index,\n               sort,\n               author_sort,\n               (SELECT concat(format) FROM data WHERE data.book=books.id) formats,\n               isbn,\n               path,\n               lccn,\n               pubdate,\n               flags,\n               uuid\n        FROM books;\n\n        END TRANSACTION;\n        ')
  53.  
  54.     
  55.     def upgrade_version_8(self):
  56.         
  57.         def create_tag_browser_view(table_name, column_name):
  58.             self.conn.executescript('\n                DROP VIEW IF EXISTS tag_browser_{tn};\n                CREATE VIEW tag_browser_{tn} AS SELECT\n                    id,\n                    name,\n                    (SELECT COUNT(id) FROM books_{tn}_link WHERE {cn}={tn}.id) count\n                FROM {tn};\n                '.format(tn = table_name, cn = column_name))
  59.  
  60.         for tn in ('authors', 'tags', 'publishers', 'series'):
  61.             cn = tn[:-1]
  62.             if tn == 'series':
  63.                 cn = tn
  64.             
  65.             create_tag_browser_view(tn, cn)
  66.         
  67.  
  68.     
  69.     def upgrade_version_9(self):
  70.         self.conn.executescript('\n                CREATE TABLE custom_columns (\n                    id       INTEGER PRIMARY KEY AUTOINCREMENT,\n                    label    TEXT NOT NULL,\n                    name     TEXT NOT NULL,\n                    datatype TEXT NOT NULL,\n                    mark_for_delete   BOOL DEFAULT 0 NOT NULL,\n                    editable BOOL DEFAULT 1 NOT NULL,\n                    display  TEXT DEFAULT "{}" NOT NULL,\n                    is_multiple BOOL DEFAULT 0 NOT NULL,\n                    normalized BOOL NOT NULL,\n                    UNIQUE(label)\n                );\n                CREATE INDEX custom_columns_idx ON custom_columns (label);\n                CREATE INDEX IF NOT EXISTS formats_idx ON data (format);\n        ')
  71.  
  72.     
  73.     def upgrade_version_10(self):
  74.         
  75.         def create_tag_browser_view(table_name, column_name, view_column_name):
  76.             script = '\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                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                FROM {tn};\n                '.format(tn = table_name, cn = column_name, vcn = view_column_name)
  77.             self.conn.executescript(script)
  78.  
  79.         for field in self.field_metadata.itervalues():
  80.             if field['is_category'] and not field['is_custom'] and 'link_column' in field:
  81.                 table = self.conn.get('SELECT name FROM sqlite_master WHERE type="table" AND name=?', ('books_%s_link' % field['table'],), all = False)
  82.                 if table is not None:
  83.                     create_tag_browser_view(field['table'], field['link_column'], field['column'])
  84.                 
  85.             table is not None
  86.         
  87.  
  88.     
  89.     def upgrade_version_11(self):
  90.         
  91.         def create_std_tag_browser_view(table_name, column_name, view_column_name, sort_column_name):
  92.             script = '\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)
  93.             self.conn.executescript(script)
  94.  
  95.         
  96.         def create_cust_tag_browser_view(table_name, link_table_name):
  97.             script = '\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)
  98.             self.conn.executescript(script)
  99.  
  100.         for field in self.field_metadata.itervalues():
  101.             if field['is_category'] and not field['is_custom'] and 'link_column' in field:
  102.                 table = self.conn.get('SELECT name FROM sqlite_master WHERE type="table" AND name=?', ('books_%s_link' % field['table'],), all = False)
  103.                 if table is not None:
  104.                     create_std_tag_browser_view(field['table'], field['link_column'], field['column'], field['category_sort'])
  105.                 
  106.             table is not None
  107.         
  108.         db_tables = self.conn.get("SELECT name FROM sqlite_master\n                                     WHERE type='table'\n                                     ORDER BY name")
  109.         tables = []
  110.         for table, in db_tables:
  111.             tables.append(table)
  112.         
  113.         for table in tables:
  114.             link_table = 'books_%s_link' % table
  115.             if table.startswith('custom_column_') and link_table in tables:
  116.                 create_cust_tag_browser_view(table, link_table)
  117.                 continue
  118.             ((None,),)
  119.         
  120.         self.conn.execute('UPDATE authors SET sort=author_to_author_sort(name)')
  121.  
  122.     
  123.     def upgrade_version_12(self):
  124.         script = '\n        DROP TABLE IF EXISTS preferences;\n        CREATE TABLE preferences(id INTEGER PRIMARY KEY,\n                                 key TEXT NON NULL,\n                                 val TEXT NON NULL,\n                                 UNIQUE(key));\n        '
  125.         self.conn.executescript(script)
  126.  
  127.  
  128.