1# -*- coding: UTF-8 -*-
2# vim: fdm=marker
3__revision__ = '$Id$'
4
5# Copyright © 2009 Piotr Ożarowski
6#
7# This program is free software; you can redistribute it and/or modify
8# it under the terms of the GNU General Public License as published by
9# the Free Software Foundation; either version 2 of the License, or
10# (at your option) any later version.
11#
12# This program is distributed in the hope that it will be useful,
13# but WITHOUT ANY WARRANTY; without even the implied warranty of
14# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15# GNU Library General Public License for more details.
16#
17# You should have received a copy of the GNU General Public License
18# along with this program; if not, write to the Free Software
19# 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
20
21# You may use and distribute this software under the terms of the
22# GNU General Public License, version 2 or later
23
24import datetime
25from sqlalchemy import Table, Column, ForeignKey, func
26from sqlalchemy.types import Boolean, Unicode, Text, Integer, SmallInteger, Date, Binary, PickleType, DateTime
27
28from db import metadata
29
30posters = Table('posters', metadata,
31    Column('md5sum', Unicode(32), primary_key=True),
32    Column('data', Binary(1048576), nullable=False))
33
34volumes = Table('volumes', metadata,
35    Column('volume_id', Integer, primary_key=True),
36    Column('name', Unicode(64), nullable=False, unique=True),
37    Column('loaned', Boolean, nullable=False, default=False))
38
39collections = Table('collections', metadata,
40    Column('collection_id', Integer, primary_key=True),
41    Column('name', Unicode(64), nullable=False, unique=True),
42    Column('loaned', Boolean, nullable=False, default=False))
43
44media = Table('media', metadata,
45    Column('medium_id', Integer, primary_key=True),
46    Column('name', Unicode(64), nullable=False, unique=True))
47
48ratios = Table('ratios', metadata,
49    Column('ratio_id', Integer, primary_key=True),
50    Column('name', Unicode(5), nullable=False, unique=True))
51
52vcodecs = Table('vcodecs', metadata,
53    Column('vcodec_id', Integer, primary_key=True),
54    Column('name', Unicode(64), nullable=False, unique=True))
55
56movies = Table('movies', metadata,
57    Column('movie_id', Integer, primary_key=True),
58    Column('number', Integer, nullable=False, unique=True, index=True),
59    Column('collection_id', ForeignKey(collections.c.collection_id)),
60    Column('volume_id', ForeignKey(volumes.c.volume_id)),
61    Column('medium_id', ForeignKey(media.c.medium_id)),
62    Column('ratio_id', ForeignKey(ratios.c.ratio_id)),
63    Column('vcodec_id', ForeignKey(vcodecs.c.vcodec_id)),
64    Column('poster_md5', ForeignKey(posters.c.md5sum)),
65    Column('loaned', Boolean, nullable=False, default=False),
66    Column('seen', Boolean, nullable=False, default=False),
67    Column('rating', SmallInteger),
68    Column('color', SmallInteger),
69    Column('cond', SmallInteger), # MySQL will not accept name "condition"
70    Column('layers', SmallInteger),
71    Column('region', SmallInteger),
72    Column('media_num', SmallInteger),
73    Column('runtime', SmallInteger),
74    Column('year', SmallInteger),
75    Column('width', SmallInteger),
76    Column('height', SmallInteger),
77    Column('barcode', Unicode(32)),
78    Column('o_title', Unicode(255), index=True),
79    Column('title', Unicode(255), index=True),
80    Column('director', Unicode(255)),
81    Column('screenplay', Unicode(255)),
82    Column('cameraman', Unicode(255)),
83    Column('o_site', Unicode(255)),
84    Column('site', Unicode(255)),
85    Column('trailer', Unicode(255)),
86    Column('country', Unicode(128)),
87    Column('genre', Unicode(128)),
88    Column('studio', Unicode(128)),
89    Column('classification', Unicode(128)),
90    Column('cast', Text),
91    Column('plot', Text),
92    Column('notes', Text),
93    Column('image', Unicode(128)), # XXX: deprecated
94    Column('created', DateTime, default=datetime.datetime.now),
95    Column('updated', DateTime, onupdate=datetime.datetime.now),
96    )
97
98people = Table('people', metadata,
99    Column('person_id', Integer, primary_key=True),
100    Column('name', Unicode(255), nullable=False, unique=True),
101    Column('email', Unicode(128)),
102    Column('phone', Unicode(64)))
103
104loans = Table('loans', metadata,
105    Column('loan_id', Integer, primary_key=True),
106    Column('person_id', ForeignKey(people.c.person_id), nullable=False),
107    Column('movie_id', ForeignKey(movies.c.movie_id), nullable=False),
108    Column('volume_id', ForeignKey(volumes.c.volume_id)),
109    Column('collection_id', ForeignKey(collections.c.collection_id)),
110    Column('date', Date, nullable=False, default=func.current_date()),
111    Column('return_date', Date, nullable=True))
112
113languages = Table('languages', metadata,
114    Column('lang_id', Integer, primary_key=True),
115    Column('name', Unicode(64), nullable=False, unique=True))
116
117acodecs = Table('acodecs', metadata,
118    Column('acodec_id', Integer, primary_key=True),
119    Column('name', Unicode(64), nullable=False, unique=True))
120
121achannels = Table('achannels', metadata,
122    Column('achannel_id', Integer, primary_key=True),
123    Column('name', Unicode(64), nullable=False, unique=True))
124
125subformats = Table('subformats', metadata,
126    Column('subformat_id', Integer, primary_key=True),
127    Column('name', Unicode(64), nullable=False, unique=True))
128
129tags = Table('tags', metadata,
130    Column('tag_id', Integer, primary_key=True),
131    Column('name', Unicode(64), nullable=False, unique=True))
132
133movie_lang = Table('movie_lang', metadata,
134    Column('ml_id', Integer, primary_key=True),
135    Column('type', SmallInteger), # 0: Original, 1:lector, 2:dubbing, 3:subtitle
136    Column('movie_id', ForeignKey(movies.c.movie_id), nullable=False),
137    Column('lang_id', ForeignKey(languages.c.lang_id), nullable=False),
138    Column('acodec_id', ForeignKey(acodecs.c.acodec_id)),
139    Column('achannel_id', ForeignKey(achannels.c.achannel_id)),
140    Column('subformat_id', ForeignKey(subformats.c.subformat_id)))
141
142movie_tag = Table('movie_tag', metadata,
143    Column('mt_id', Integer, primary_key=True),
144    Column('movie_id', ForeignKey(movies.c.movie_id)),
145    Column('tag_id', ForeignKey(tags.c.tag_id)))
146
147configuration = Table('configuration', metadata,
148    Column('param', Unicode(16), primary_key=True),
149    Column('value', Unicode(128), nullable=False))
150
151filters = Table('filters', metadata,
152    Column('name', Unicode(64), primary_key=True),
153    Column('data', PickleType, nullable=False))
154