← Back to branch summary

~ubuntu-branches/debian/wheezy/calibre/wheezy

« back to all changes in this revision

Viewing changes to resources/metadata_sqlite.sql

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2009-09-18 01:18:18 UTC
  • mto: (29.1.1 sid)
  • mto: This revision was merged to the branch mainline in revision 16.
  • Revision ID: james.westby@ubuntu.com-20090918011818-2whiyzgvx59c7b7t
Tags: upstream-0.6.12+dfsg
Import upstream version 0.6.12+dfsg

Show diffs side-by-side

added added

removed removed

Lines of Context:
resources/metadata_sqlite.sql
 
1
CREATE TABLE authors ( id   INTEGER PRIMARY KEY,
 
2
                              name TEXT NOT NULL COLLATE NOCASE,
 
3
                              sort TEXT COLLATE NOCASE,
 
4
                              UNIQUE(name)
 
5
                             );
 
6
CREATE TABLE books ( id      INTEGER PRIMARY KEY AUTOINCREMENT,
 
7
                             title     TEXT NOT NULL DEFAULT 'Unknown' COLLATE NOCASE,
 
8
                             sort      TEXT COLLATE NOCASE,
 
9
                             timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 
10
                             uri       TEXT,
 
11
                             series_index INTEGER NOT NULL DEFAULT 1,
 
12
                             author_sort TEXT COLLATE NOCASE,
 
13
                             isbn TEXT DEFAULT "" COLLATE NOCASE,
 
14
                             path TEXT NOT NULL DEFAULT ""
 
15
                        );
 
16
CREATE TABLE books_authors_link ( id INTEGER PRIMARY KEY,
 
17
                                          book INTEGER NOT NULL,
 
18
                                          author INTEGER NOT NULL,
 
19
                                          UNIQUE(book, author)
 
20
                                        );
 
21
CREATE TABLE books_publishers_link ( id INTEGER PRIMARY KEY,
 
22
                                          book INTEGER NOT NULL,
 
23
                                          publisher INTEGER NOT NULL,
 
24
                                          UNIQUE(book)
 
25
                                        );
 
26
CREATE TABLE books_ratings_link ( id INTEGER PRIMARY KEY,
 
27
                                          book INTEGER NOT NULL,
 
28
                                          rating INTEGER NOT NULL,
 
29
                                          UNIQUE(book, rating)
 
30
                                        );
 
31
CREATE TABLE books_series_link ( id INTEGER PRIMARY KEY,
 
32
                                          book INTEGER NOT NULL,
 
33
                                          series INTEGER NOT NULL,
 
34
                                          UNIQUE(book)
 
35
                                        );
 
36
CREATE TABLE books_tags_link ( id INTEGER PRIMARY KEY,
 
37
                                          book INTEGER NOT NULL,
 
38
                                          tag INTEGER NOT NULL,
 
39
                                          UNIQUE(book, tag)
 
40
                                        );
 
41
CREATE TABLE comments ( id INTEGER PRIMARY KEY,
 
42
                              book INTEGER NON NULL,
 
43
                              text TEXT NON NULL COLLATE NOCASE,
 
44
                              UNIQUE(book)
 
45
                            );
 
46
CREATE TABLE conversion_options ( id INTEGER PRIMARY KEY,
 
47
                                          format TEXT NOT NULL COLLATE NOCASE,
 
48
                                          book INTEGER,
 
49
                                          data BLOB NOT NULL,
 
50
                                          UNIQUE(format,book)
 
51
                                        );
 
52
CREATE TABLE feeds ( id   INTEGER PRIMARY KEY,
 
53
                              title TEXT NOT NULL,
 
54
                              script TEXT NOT NULL,
 
55
                              UNIQUE(title)
 
56
                             );
 
57
CREATE TABLE publishers ( id   INTEGER PRIMARY KEY,
 
58
                                  name TEXT NOT NULL COLLATE NOCASE,
 
59
                                  sort TEXT COLLATE NOCASE,
 
60
                                  UNIQUE(name)
 
61
                             );
 
62
CREATE TABLE ratings ( id   INTEGER PRIMARY KEY,
 
63
                               rating INTEGER CHECK(rating > -1 AND rating < 11),
 
64
                               UNIQUE (rating)
 
65
                             );
 
66
CREATE TABLE series ( id   INTEGER PRIMARY KEY,
 
67
                              name TEXT NOT NULL COLLATE NOCASE,
 
68
                              sort TEXT COLLATE NOCASE,
 
69
                              UNIQUE (name)
 
70
                             );
 
71
CREATE TABLE tags ( id   INTEGER PRIMARY KEY,
 
72
                            name TEXT NOT NULL COLLATE NOCASE,
 
73
                            UNIQUE (name)
 
74
                             );
 
75
CREATE TABLE data ( id     INTEGER PRIMARY KEY,
 
76
                            book   INTEGER NON NULL,
 
77
                            format TEXT NON NULL COLLATE NOCASE,
 
78
                            uncompressed_size INTEGER NON NULL,
 
79
                            name TEXT NON NULL,
 
80
                            UNIQUE(book, format)
 
81
);
 
82
 
 
83
CREATE VIEW meta AS
 
84
    SELECT id, title,
 
85
           (SELECT concat(name) FROM authors WHERE authors.id IN (SELECT author from books_authors_link WHERE book=books.id)) authors,
 
86
           (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
 
87
           (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
 
88
           timestamp,
 
89
           (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
 
90
           (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
 
91
           (SELECT text FROM comments WHERE book=books.id) comments,
 
92
           (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
 
93
           series_index,
 
94
           sort,
 
95
           author_sort,
 
96
           (SELECT concat(format) FROM data WHERE data.book=books.id) formats,
 
97
           isbn
 
98
    FROM books;
 
99
CREATE INDEX authors_idx ON books (author_sort COLLATE NOCASE);
 
100
CREATE INDEX books_authors_link_aidx ON books_authors_link (author);
 
101
CREATE INDEX books_authors_link_bidx ON books_authors_link (book);
 
102
CREATE INDEX books_idx ON books (sort COLLATE NOCASE);
 
103
CREATE INDEX books_publishers_link_aidx ON books_publishers_link (publisher);
 
104
CREATE INDEX books_publishers_link_bidx ON books_publishers_link (book);
 
105
CREATE INDEX books_ratings_link_aidx ON books_ratings_link (rating);
 
106
CREATE INDEX books_ratings_link_bidx ON books_ratings_link (book);
 
107
CREATE INDEX books_series_link_aidx ON books_series_link (series);
 
108
CREATE INDEX books_series_link_bidx ON books_series_link (book);
 
109
CREATE INDEX books_tags_link_aidx ON books_tags_link (tag);
 
110
CREATE INDEX books_tags_link_bidx ON books_tags_link (book);
 
111
CREATE INDEX comments_idx ON comments (book);
 
112
CREATE INDEX conversion_options_idx_a ON conversion_options (format COLLATE NOCASE);
 
113
CREATE INDEX conversion_options_idx_b ON conversion_options (book);
 
114
CREATE INDEX data_idx ON data (book);
 
115
CREATE INDEX publishers_idx ON publishers (name COLLATE NOCASE);
 
116
CREATE INDEX series_idx ON series (sort COLLATE NOCASE);
 
117
CREATE INDEX tags_idx ON tags (name COLLATE NOCASE);
 
118
CREATE TRIGGER books_delete_trg
 
119
        AFTER DELETE ON books
 
120
        BEGIN
 
121
            DELETE FROM books_authors_link WHERE book=OLD.id;
 
122
            DELETE FROM books_publishers_link WHERE book=OLD.id;
 
123
            DELETE FROM books_ratings_link WHERE book=OLD.id;
 
124
            DELETE FROM books_series_link WHERE book=OLD.id;
 
125
            DELETE FROM books_tags_link WHERE book=OLD.id;
 
126
            DELETE FROM data WHERE book=OLD.id;
 
127
            DELETE FROM comments WHERE book=OLD.id;
 
128
            DELETE FROM conversion_options WHERE book=OLD.id;
 
129
        END;
 
130
CREATE TRIGGER books_insert_trg
 
131
        AFTER INSERT ON books
 
132
        BEGIN
 
133
          UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
 
134
        END;
 
135
CREATE TRIGGER books_update_trg
 
136
        AFTER UPDATE ON books
 
137
        BEGIN
 
138
          UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
 
139
        END;
 
140
CREATE TRIGGER fkc_comments_insert
 
141
        BEFORE INSERT ON comments
 
142
        BEGIN
 
143
            SELECT CASE
 
144
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
145
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
146
            END;
 
147
        END;
 
148
CREATE TRIGGER fkc_comments_update
 
149
        BEFORE UPDATE OF book ON comments
 
150
        BEGIN
 
151
            SELECT CASE
 
152
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
153
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
154
            END;
 
155
        END;
 
156
CREATE TRIGGER fkc_data_insert
 
157
        BEFORE INSERT ON data
 
158
        BEGIN
 
159
            SELECT CASE
 
160
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
161
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
162
            END;
 
163
        END;
 
164
CREATE TRIGGER fkc_data_update
 
165
        BEFORE UPDATE OF book ON data
 
166
        BEGIN
 
167
            SELECT CASE
 
168
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
169
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
170
            END;
 
171
        END;
 
172
CREATE TRIGGER fkc_delete_books_authors_link
 
173
        BEFORE DELETE ON authors
 
174
        BEGIN
 
175
            SELECT CASE
 
176
                WHEN (SELECT COUNT(id) FROM books_authors_link WHERE book=OLD.book) > 0
 
177
                THEN RAISE(ABORT, 'Foreign key violation: author is still referenced')
 
178
            END;
 
179
        END;
 
180
CREATE TRIGGER fkc_delete_books_publishers_link
 
181
        BEFORE DELETE ON publishers
 
182
        BEGIN
 
183
            SELECT CASE
 
184
                WHEN (SELECT COUNT(id) FROM books_publishers_link WHERE book=OLD.book) > 0
 
185
                THEN RAISE(ABORT, 'Foreign key violation: publisher is still referenced')
 
186
            END;
 
187
        END;
 
188
CREATE TRIGGER fkc_delete_books_series_link
 
189
        BEFORE DELETE ON series
 
190
        BEGIN
 
191
            SELECT CASE
 
192
                WHEN (SELECT COUNT(id) FROM books_series_link WHERE series=OLD.id) > 0
 
193
                THEN RAISE(ABORT, 'Foreign key violation: series is still referenced')
 
194
            END;
 
195
        END;
 
196
CREATE TRIGGER fkc_delete_books_tags_link
 
197
        BEFORE DELETE ON tags
 
198
        BEGIN
 
199
            SELECT CASE
 
200
                WHEN (SELECT COUNT(id) FROM books_tags_link WHERE tag=OLD.id) > 0
 
201
                THEN RAISE(ABORT, 'Foreign key violation: tag is still referenced')
 
202
            END;
 
203
        END;
 
204
CREATE TRIGGER fkc_insert_books_authors_link
 
205
        BEFORE INSERT ON books_authors_link
 
206
        BEGIN
 
207
          SELECT CASE
 
208
              WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
209
              THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
210
              WHEN (SELECT id from authors WHERE id=NEW.author) IS NULL
 
211
              THEN RAISE(ABORT, 'Foreign key violation: author not in authors')
 
212
          END;
 
213
        END;
 
214
CREATE TRIGGER fkc_insert_books_publishers_link
 
215
        BEFORE INSERT ON books_publishers_link
 
216
        BEGIN
 
217
          SELECT CASE
 
218
              WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
219
              THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
220
              WHEN (SELECT id from publishers WHERE id=NEW.publisher) IS NULL
 
221
              THEN RAISE(ABORT, 'Foreign key violation: publisher not in publishers')
 
222
          END;
 
223
        END;
 
224
CREATE TRIGGER fkc_insert_books_ratings_link
 
225
        BEFORE INSERT ON books_ratings_link
 
226
        BEGIN
 
227
          SELECT CASE
 
228
              WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
229
              THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
230
              WHEN (SELECT id from ratings WHERE id=NEW.rating) IS NULL
 
231
              THEN RAISE(ABORT, 'Foreign key violation: rating not in ratings')
 
232
          END;
 
233
        END;
 
234
CREATE TRIGGER fkc_insert_books_series_link
 
235
        BEFORE INSERT ON books_series_link
 
236
        BEGIN
 
237
          SELECT CASE
 
238
              WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
239
              THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
240
              WHEN (SELECT id from series WHERE id=NEW.series) IS NULL
 
241
              THEN RAISE(ABORT, 'Foreign key violation: series not in series')
 
242
          END;
 
243
        END;
 
244
CREATE TRIGGER fkc_insert_books_tags_link
 
245
        BEFORE INSERT ON books_tags_link
 
246
        BEGIN
 
247
          SELECT CASE
 
248
              WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
249
              THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
250
              WHEN (SELECT id from tags WHERE id=NEW.tag) IS NULL
 
251
              THEN RAISE(ABORT, 'Foreign key violation: tag not in tags')
 
252
          END;
 
253
        END;
 
254
CREATE TRIGGER fkc_update_books_authors_link_a
 
255
        BEFORE UPDATE OF book ON books_authors_link
 
256
        BEGIN
 
257
            SELECT CASE
 
258
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
259
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
260
            END;
 
261
        END;
 
262
CREATE TRIGGER fkc_update_books_authors_link_b
 
263
        BEFORE UPDATE OF author ON books_authors_link
 
264
        BEGIN
 
265
            SELECT CASE
 
266
                WHEN (SELECT id from authors WHERE id=NEW.author) IS NULL
 
267
                THEN RAISE(ABORT, 'Foreign key violation: author not in authors')
 
268
            END;
 
269
        END;
 
270
CREATE TRIGGER fkc_update_books_publishers_link_a
 
271
        BEFORE UPDATE OF book ON books_publishers_link
 
272
        BEGIN
 
273
            SELECT CASE
 
274
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
275
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
276
            END;
 
277
        END;
 
278
CREATE TRIGGER fkc_update_books_publishers_link_b
 
279
        BEFORE UPDATE OF publisher ON books_publishers_link
 
280
        BEGIN
 
281
            SELECT CASE
 
282
                WHEN (SELECT id from publishers WHERE id=NEW.publisher) IS NULL
 
283
                THEN RAISE(ABORT, 'Foreign key violation: publisher not in publishers')
 
284
            END;
 
285
        END;
 
286
CREATE TRIGGER fkc_update_books_ratings_link_a
 
287
        BEFORE UPDATE OF book ON books_ratings_link
 
288
        BEGIN
 
289
            SELECT CASE
 
290
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
291
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
292
            END;
 
293
        END;
 
294
CREATE TRIGGER fkc_update_books_ratings_link_b
 
295
        BEFORE UPDATE OF rating ON books_ratings_link
 
296
        BEGIN
 
297
            SELECT CASE
 
298
                WHEN (SELECT id from ratings WHERE id=NEW.rating) IS NULL
 
299
                THEN RAISE(ABORT, 'Foreign key violation: rating not in ratings')
 
300
            END;
 
301
        END;
 
302
CREATE TRIGGER fkc_update_books_series_link_a
 
303
        BEFORE UPDATE OF book ON books_series_link
 
304
        BEGIN
 
305
            SELECT CASE
 
306
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
307
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
308
            END;
 
309
        END;
 
310
CREATE TRIGGER fkc_update_books_series_link_b
 
311
        BEFORE UPDATE OF series ON books_series_link
 
312
        BEGIN
 
313
            SELECT CASE
 
314
                WHEN (SELECT id from series WHERE id=NEW.series) IS NULL
 
315
                THEN RAISE(ABORT, 'Foreign key violation: series not in series')
 
316
            END;
 
317
        END;
 
318
CREATE TRIGGER fkc_update_books_tags_link_a
 
319
        BEFORE UPDATE OF book ON books_tags_link
 
320
        BEGIN
 
321
            SELECT CASE
 
322
                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
 
323
                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
 
324
            END;
 
325
        END;
 
326
CREATE TRIGGER fkc_update_books_tags_link_b
 
327
        BEFORE UPDATE OF tag ON books_tags_link
 
328
        BEGIN
 
329
            SELECT CASE
 
330
                WHEN (SELECT id from tags WHERE id=NEW.tag) IS NULL
 
331
                THEN RAISE(ABORT, 'Foreign key violation: tag not in tags')
 
332
            END;
 
333
        END;
 
334
CREATE TRIGGER series_insert_trg
 
335
        AFTER INSERT ON series
 
336
        BEGIN
 
337
          UPDATE series SET sort=NEW.name WHERE id=NEW.id;
 
338
        END;
 
339
CREATE TRIGGER series_update_trg
 
340
        AFTER UPDATE ON series
 
341
        BEGIN
 
342
          UPDATE series SET sort=NEW.name WHERE id=NEW.id;
 
343
        END;