def test_init_db_calls_correct_functions(self):
"""
Test that the init_db function makes the correct function calls
"""
# GIVEN: Mocked out SQLAlchemy calls and return objects, and an in-memory SQLite database URL
with patch('openlp.core.lib.db.create_engine') as mocked_create_engine, \
patch('openlp.core.lib.db.MetaData') as MockedMetaData, \
patch('openlp.core.lib.db.sessionmaker') as mocked_sessionmaker, \
patch('openlp.core.lib.db.scoped_session') as mocked_scoped_session:
mocked_engine = MagicMock()
mocked_metadata = MagicMock()
mocked_sessionmaker_object = MagicMock()
mocked_scoped_session_object = MagicMock()
mocked_create_engine.return_value = mocked_engine
MockedMetaData.return_value = mocked_metadata
mocked_sessionmaker.return_value = mocked_sessionmaker_object
mocked_scoped_session.return_value = mocked_scoped_session_object
db_url = 'sqlite://'
# WHEN: We try to initialise the db
session, metadata = init_db(db_url)
# THEN: We should see the correct function calls
mocked_create_engine.assert_called_with(db_url, poolclass=NullPool)
MockedMetaData.assert_called_with(bind=mocked_engine)
mocked_sessionmaker.assert_called_with(autoflush=True,
autocommit=False,
bind=mocked_engine)
mocked_scoped_session.assert_called_with(
mocked_sessionmaker_object)
self.assertIs(session, mocked_scoped_session_object,
'The ``session`` object should be the mock')
self.assertIs(metadata, mocked_metadata,
'The ``metadata`` object should be the mock')
def init_schema(url):
"""
Setup the songusage database connection and initialise the database schema
``url``
The database to setup
"""
session, metadata = init_db(url)
songusage_table = Table(u'songusage_data', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'usagedate', types.Date, index=True, nullable=False),
Column(u'usagetime', types.Time, index=True, nullable=False),
Column(u'title', types.Unicode(255), nullable=False),
Column(u'authors', types.Unicode(255), nullable=False),
Column(u'copyright', types.Unicode(255)),
Column(u'ccl_number', types.Unicode(65)),
Column(u'plugin_name', types.Unicode(20)),
Column(u'source', types.Unicode(10))
)
mapper(SongUsageItem, songusage_table)
metadata.create_all(checkfirst=True)
return session
def init_db_calls_correct_functions_test(self):
"""
Test that the init_db function makes the correct function calls
"""
# GIVEN: Mocked out SQLAlchemy calls and return objects, and an in-memory SQLite database URL
with patch('openlp.core.lib.db.create_engine') as mocked_create_engine, \
patch('openlp.core.lib.db.MetaData') as MockedMetaData, \
patch('openlp.core.lib.db.sessionmaker') as mocked_sessionmaker, \
patch('openlp.core.lib.db.scoped_session') as mocked_scoped_session:
mocked_engine = MagicMock()
mocked_metadata = MagicMock()
mocked_sessionmaker_object = MagicMock()
mocked_scoped_session_object = MagicMock()
mocked_create_engine.return_value = mocked_engine
MockedMetaData.return_value = mocked_metadata
mocked_sessionmaker.return_value = mocked_sessionmaker_object
mocked_scoped_session.return_value = mocked_scoped_session_object
db_url = 'sqlite://'
# WHEN: We try to initialise the db
session, metadata = init_db(db_url)
# THEN: We should see the correct function calls
mocked_create_engine.assert_called_with(db_url, poolclass=NullPool)
MockedMetaData.assert_called_with(bind=mocked_engine)
mocked_sessionmaker.assert_called_with(autoflush=True, autocommit=False, bind=mocked_engine)
mocked_scoped_session.assert_called_with(mocked_sessionmaker_object)
self.assertIs(session, mocked_scoped_session_object, 'The ``session`` object should be the mock')
self.assertIs(metadata, mocked_metadata, 'The ``metadata`` object should be the mock')
def init_schema(self, *args, **kwargs):
"""
Setup the projector database and initialize the schema.
Declarative uses table classes to define schema.
"""
self.db_url = init_url('projector')
session, metadata = init_db(self.db_url, base=Base)
metadata.create_all(checkfirst=True)
return session
def init_schema(*args, **kwargs):
"""
Setup the projector database and initialize the schema.
Declarative uses table classes to define schema.
"""
url = init_url("projector")
session, metadata = init_db(url, base=Base)
Base.metadata.create_all(checkfirst=True)
return session
def setUp(self):
"""
Create temp folder for keeping db file
"""
self.tmp_folder = mkdtemp()
db_path = os.path.join(TEST_RESOURCES_PATH, 'songs', 'songs-1.9.7.sqlite')
self.db_tmp_path = os.path.join(self.tmp_folder, 'songs-1.9.7.sqlite')
shutil.copyfile(db_path, self.db_tmp_path)
db_url = 'sqlite:///' + self.db_tmp_path
self.session, metadata = init_db(db_url)
self.op = get_upgrade_op(self.session)
def setUp(self):
"""
Create temp folder for keeping db file
"""
self.tmp_folder = mkdtemp()
db_path = os.path.join(TEST_RESOURCES_PATH, 'songs',
'songs-1.9.7.sqlite')
self.db_tmp_path = os.path.join(self.tmp_folder, 'songs-1.9.7.sqlite')
shutil.copyfile(db_path, self.db_tmp_path)
db_url = 'sqlite:///' + self.db_tmp_path
self.session, metadata = init_db(db_url)
self.op = get_upgrade_op(self.session)
def init_db_defaults_test(self):
"""
Test that initialising an in-memory SQLite database via ``init_db`` uses the defaults
"""
# GIVEN: An in-memory SQLite URL
db_url = 'sqlite://'
# WHEN: The database is initialised through init_db
session, metadata = init_db(db_url)
# THEN: Valid session and metadata objects should be returned
self.assertIsInstance(session, ScopedSession, 'The ``session`` object should be a ``ScopedSession`` instance')
self.assertIsInstance(metadata, MetaData, 'The ``metadata`` object should be a ``MetaData`` instance')
def test_init_db_defaults(self):
"""
Test that initialising an in-memory SQLite database via ``init_db`` uses the defaults
"""
# GIVEN: An in-memory SQLite URL
db_url = 'sqlite://'
# WHEN: The database is initialised through init_db
session, metadata = init_db(db_url)
# THEN: Valid session and metadata objects should be returned
assert isinstance(session, ScopedSession), 'The ``session`` object should be a ``ScopedSession`` instance'
assert isinstance(metadata, MetaData), 'The ``metadata`` object should be a ``MetaData`` instance'
def init_schema(url):
"""
Setup a bible database connection and initialise the database schema.
:param url: The database to setup.
"""
session, metadata = init_db(url)
meta_table = Table(
'metadata',
metadata,
Column('key', types.Unicode(255), primary_key=True, index=True),
Column('value', types.Unicode(255)),
)
book_table = Table(
'book',
metadata,
Column('id', types.Integer, primary_key=True),
Column('book_reference_id', types.Integer, index=True),
Column('testament_reference_id', types.Integer),
Column('name', types.Unicode(50), index=True),
)
verse_table = Table(
'verse',
metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('book_id', types.Integer, ForeignKey('book.id'), index=True),
Column('chapter', types.Integer, index=True),
Column('verse', types.Integer, index=True),
Column('text', types.UnicodeText, index=True),
)
try:
class_mapper(BibleMeta)
except UnmappedClassError:
mapper(BibleMeta, meta_table)
try:
class_mapper(Book)
except UnmappedClassError:
mapper(Book,
book_table,
properties={'verses': relation(Verse, backref='book')})
try:
class_mapper(Verse)
except UnmappedClassError:
mapper(Verse, verse_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the images database connection and initialise the database schema.
``url``
The database to setup
The images database contains the following tables:
* image_groups
* image_filenames
**image_groups Table**
This table holds the names of the images groups. It has the following columns:
* id
* parent_id
* group_name
**image_filenames Table**
This table holds the filenames of the images and the group they belong to. It has the following columns:
* id
* group_id
* filename
"""
session, metadata = init_db(url)
# Definition of the "image_groups" table
image_groups_table = Table('image_groups', metadata,
Column('id', types.Integer(), primary_key=True),
Column('parent_id', types.Integer()),
Column('group_name', types.Unicode(128))
)
# Definition of the "image_filenames" table
image_filenames_table = Table('image_filenames', metadata,
Column('id', types.Integer(), primary_key=True),
Column('group_id', types.Integer(), ForeignKey('image_groups.id'), default=None),
Column('filename', types.Unicode(255), nullable=False)
)
mapper(ImageGroups, image_groups_table)
mapper(ImageFilenames, image_filenames_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the images database connection and initialise the database schema.
:param url: The database to setup
The images database contains the following tables:
* image_groups
* image_filenames
**image_groups Table**
This table holds the names of the images groups. It has the following columns:
* id
* parent_id
* group_name
**image_filenames Table**
This table holds the filenames of the images and the group they belong to. It has the following columns:
* id
* group_id
* filename
"""
session, metadata = init_db(url)
# Definition of the "image_groups" table
image_groups_table = Table('image_groups', metadata,
Column('id', types.Integer(), primary_key=True),
Column('parent_id', types.Integer()),
Column('group_name', types.Unicode(128)))
# Definition of the "image_filenames" table
image_filenames_table = Table(
'image_filenames', metadata,
Column('id', types.Integer(), primary_key=True),
Column('group_id',
types.Integer(),
ForeignKey('image_groups.id'),
default=None),
Column('filename', types.Unicode(255), nullable=False))
mapper(ImageGroups, image_groups_table)
mapper(ImageFilenames, image_filenames_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the alerts database connection and initialise the database schema
:param url:
The database to setup
"""
session, metadata = init_db(url)
alerts_table = Table('alerts', metadata,
Column('id', types.Integer(), primary_key=True),
Column('text', types.UnicodeText, nullable=False))
mapper(AlertItem, alerts_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup a bible database connection and initialise the database schema.
``url``
The database to setup.
"""
session, metadata = init_db(url)
meta_table = Table(u'metadata', metadata,
Column(u'key', types.Unicode(255), primary_key=True, index=True),
Column(u'value', types.Unicode(255)),
)
book_table = Table(u'book', metadata,
Column(u'id', types.Integer, primary_key=True),
Column(u'book_reference_id', types.Integer, index=True),
Column(u'testament_reference_id', types.Integer),
Column(u'name', types.Unicode(50), index=True),
)
verse_table = Table(u'verse', metadata,
Column(u'id', types.Integer, primary_key=True, index=True),
Column(u'book_id', types.Integer, ForeignKey(
u'book.id'), index=True),
Column(u'chapter', types.Integer, index=True),
Column(u'verse', types.Integer, index=True),
Column(u'text', types.UnicodeText, index=True),
)
try:
class_mapper(BibleMeta)
except UnmappedClassError:
mapper(BibleMeta, meta_table)
try:
class_mapper(Book)
except UnmappedClassError:
mapper(Book, book_table,
properties={'verses': relation(Verse, backref='book')})
try:
class_mapper(Verse)
except UnmappedClassError:
mapper(Verse, verse_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the custom database connection and initialise the database schema
:param url: The database to setup
"""
session, metadata = init_db(url)
custom_slide_table = Table('custom_slide', metadata,
Column('id', types.Integer(), primary_key=True),
Column('title', types.Unicode(255), nullable=False),
Column('text', types.UnicodeText, nullable=False),
Column('credits', types.UnicodeText),
Column('theme_name', types.Unicode(128))
)
mapper(CustomSlide, custom_slide_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the custom database connection and initialise the database schema
:param url: The database to setup
"""
session, metadata = init_db(url)
custom_slide_table = Table(
'custom_slide', metadata,
Column('id', types.Integer(), primary_key=True),
Column('title', types.Unicode(255), nullable=False),
Column('text', types.UnicodeText, nullable=False),
Column('credits', types.UnicodeText),
Column('theme_name', types.Unicode(128)))
mapper(CustomSlide, custom_slide_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the songusage database connection and initialise the database schema
:param url: The database to setup
"""
session, metadata = init_db(url)
songusage_table = Table(
'songusage_data', metadata,
Column('id', types.Integer(), primary_key=True),
Column('usagedate', types.Date, index=True, nullable=False),
Column('usagetime', types.Time, index=True, nullable=False),
Column('title', types.Unicode(255), nullable=False),
Column('authors', types.Unicode(255), nullable=False),
Column('copyright', types.Unicode(255)),
Column('ccl_number', types.Unicode(65)),
Column('plugin_name', types.Unicode(20)),
Column('source', types.Unicode(10)))
mapper(SongUsageItem, songusage_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the songs database connection and initialise the database schema.
:param url: The database to setup
The song database contains the following tables:
* authors
* authors_songs
* media_files
* media_files_songs
* song_books
* songs
* songs_topics
* topics
**authors** Table
This table holds the names of all the authors. It has the following
columns:
* id
* first_name
* last_name
* display_name
**authors_songs Table**
This is a bridging table between the *authors* and *songs* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* author_id
* song_id
* author_type
**media_files Table**
* id
* file_name
* type
**song_books Table**
The *song_books* table holds a list of books that a congregation gets
their songs from, or old hymnals now no longer used. This table has the
following columns:
* id
* name
* publisher
**songs Table**
This table contains the songs, and each song has a list of attributes.
The *songs* table has the following columns:
* id
* song_book_id
* title
* alternate_title
* song_key
* transpose_by
* lyrics
* verse_order
* copyright
* comments
* ccli_number
* song_number
* theme_name
* search_title
* search_lyrics
**songs_topics Table**
This is a bridging table between the *songs* and *topics* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* song_id
* topic_id
**topics Table**
The topics table holds a selection of topics that songs can cover. This
is useful when a worship leader wants to select songs with a certain
theme. This table has the following columns:
* id
* name
"""
session, metadata = init_db(url)
# Definition of the "authors" table
authors_table = Table(
'authors', metadata,
Column('id', types.Integer(), primary_key=True),
Column('first_name', types.Unicode(128)),
Column('last_name', types.Unicode(128)),
Column('display_name', types.Unicode(255), index=True, nullable=False)
)
# Definition of the "media_files" table
media_files_table = Table(
'media_files', metadata,
Column('id', types.Integer(), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), default=None),
Column('file_name', types.Unicode(255), nullable=False),
Column('type', types.Unicode(64), nullable=False, default='audio'),
Column('weight', types.Integer(), default=0)
)
# Definition of the "song_books" table
song_books_table = Table(
'song_books', metadata,
Column('id', types.Integer(), primary_key=True),
Column('name', types.Unicode(128), nullable=False),
Column('publisher', types.Unicode(128))
)
# Definition of the "songs" table
songs_table = Table(
'songs', metadata,
Column('id', types.Integer(), primary_key=True),
Column('song_book_id', types.Integer(), ForeignKey('song_books.id'), default=None),
Column('title', types.Unicode(255), nullable=False),
Column('alternate_title', types.Unicode(255)),
Column('song_key', types.Unicode(3)),
Column('transpose_by', types.Integer(), default=0),
Column('chords', types.UnicodeText),
Column('lyrics', types.UnicodeText, nullable=False),
Column('verse_order', types.Unicode(128)),
Column('copyright', types.Unicode(255)),
Column('comments', types.UnicodeText),
Column('ccli_number', types.Unicode(64)),
Column('song_number', types.Unicode(64)),
Column('theme_name', types.Unicode(128)),
Column('search_title', types.Unicode(255), index=True, nullable=False),
Column('search_lyrics', types.UnicodeText, nullable=False),
Column('create_date', types.DateTime(), default=func.now()),
Column('last_modified', types.DateTime(), default=func.now(), onupdate=func.now()),
Column('temporary', types.Boolean(), default=False)
)
# Definition of the "topics" table
topics_table = Table(
'topics', metadata,
Column('id', types.Integer(), primary_key=True),
Column('name', types.Unicode(128), index=True, nullable=False)
)
# Definition of the "authors_songs" table
authors_songs_table = Table(
'authors_songs', metadata,
Column('author_id', types.Integer(), ForeignKey('authors.id'), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('author_type', types.Unicode(255), primary_key=True, nullable=False, server_default=text('""'))
)
# Definition of the "songs_topics" table
songs_topics_table = Table(
'songs_topics', metadata,
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('topic_id', types.Integer(), ForeignKey('topics.id'), primary_key=True)
)
mapper(Author, authors_table, properties={
'songs': relation(Song, secondary=authors_songs_table, viewonly=True)
})
mapper(AuthorSong, authors_songs_table, properties={
'author': relation(Author)
})
mapper(Book, song_books_table)
mapper(MediaFile, media_files_table)
mapper(Song, songs_table, properties={
# Use the authors_songs relation when you need access to the 'author_type' attribute
# or when creating new relations
'authors_songs': relation(AuthorSong, cascade="all, delete-orphan"),
# Use lazy='joined' to always load authors when the song is fetched from the database (bug 1366198)
'authors': relation(Author, secondary=authors_songs_table, viewonly=True, lazy='joined'),
'book': relation(Book, backref='songs'),
'media_files': relation(MediaFile, backref='songs', order_by=media_files_table.c.weight),
'topics': relation(Topic, backref='songs', secondary=songs_topics_table)
})
mapper(Topic, topics_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the songs database connection and initialise the database schema.
``url``
The database to setup
The song database contains the following tables:
* authors
* authors_songs
* media_files
* media_files_songs
* song_books
* songs
* songs_topics
* topics
**authors** Table
This table holds the names of all the authors. It has the following
columns:
* id
* first_name
* last_name
* display_name
**authors_songs Table**
This is a bridging table between the *authors* and *songs* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* author_id
* song_id
**media_files Table**
* id
* file_name
* type
**song_books Table**
The *song_books* table holds a list of books that a congregation gets
their songs from, or old hymnals now no longer used. This table has the
following columns:
* id
* name
* publisher
**songs Table**
This table contains the songs, and each song has a list of attributes.
The *songs* table has the following columns:
* id
* song_book_id
* title
* alternate_title
* lyrics
* verse_order
* copyright
* comments
* ccli_number
* song_number
* theme_name
* search_title
* search_lyrics
**songs_topics Table**
This is a bridging table between the *songs* and *topics* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* song_id
* topic_id
**topics Table**
The topics table holds a selection of topics that songs can cover. This
is useful when a worship leader wants to select songs with a certain
theme. This table has the following columns:
* id
* name
"""
session, metadata = init_db(url)
# Definition of the "authors" table
authors_table = Table(u'authors', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'first_name', types.Unicode(128)),
Column(u'last_name', types.Unicode(128)),
Column(u'display_name', types.Unicode(255), index=True, nullable=False)
)
# Definition of the "media_files" table
media_files_table = Table(u'media_files', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'song_id', types.Integer(), ForeignKey(u'songs.id'),
default=None),
Column(u'file_name', types.Unicode(255), nullable=False),
Column(u'type', types.Unicode(64), nullable=False, default=u'audio'),
Column(u'weight', types.Integer(), default=0)
)
# Definition of the "song_books" table
song_books_table = Table(u'song_books', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'name', types.Unicode(128), nullable=False),
Column(u'publisher', types.Unicode(128))
)
# Definition of the "songs" table
songs_table = Table(u'songs', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'song_book_id', types.Integer(),
ForeignKey(u'song_books.id'), default=None),
Column(u'title', types.Unicode(255), nullable=False),
Column(u'alternate_title', types.Unicode(255)),
Column(u'lyrics', types.UnicodeText, nullable=False),
Column(u'verse_order', types.Unicode(128)),
Column(u'copyright', types.Unicode(255)),
Column(u'comments', types.UnicodeText),
Column(u'ccli_number', types.Unicode(64)),
Column(u'song_number', types.Unicode(64)),
Column(u'theme_name', types.Unicode(128)),
Column(u'search_title', types.Unicode(255), index=True, nullable=False),
Column(u'search_lyrics', types.UnicodeText, nullable=False),
Column(u'create_date', types.DateTime(), default=func.now()),
Column(u'last_modified', types.DateTime(), default=func.now(),
onupdate=func.now()),
Column(u'temporary', types.Boolean(), default=False)
)
# Definition of the "topics" table
topics_table = Table(u'topics', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'name', types.Unicode(128), index=True, nullable=False)
)
# Definition of the "authors_songs" table
authors_songs_table = Table(u'authors_songs', metadata,
Column(u'author_id', types.Integer(),
ForeignKey(u'authors.id'), primary_key=True),
Column(u'song_id', types.Integer(),
ForeignKey(u'songs.id'), primary_key=True)
)
# Definition of the "songs_topics" table
songs_topics_table = Table(u'songs_topics', metadata,
Column(u'song_id', types.Integer(),
ForeignKey(u'songs.id'), primary_key=True),
Column(u'topic_id', types.Integer(),
ForeignKey(u'topics.id'), primary_key=True)
)
mapper(Author, authors_table)
mapper(Book, song_books_table)
mapper(MediaFile, media_files_table)
mapper(Song, songs_table,
properties={
'authors': relation(Author, backref='songs',
secondary=authors_songs_table, lazy=False),
'book': relation(Book, backref='songs'),
'media_files': relation(MediaFile, backref='songs',
order_by=media_files_table.c.weight),
'topics': relation(Topic, backref='songs',
secondary=songs_topics_table)
})
mapper(Topic, topics_table)
metadata.create_all(checkfirst=True)
return session
def init_schema(url):
"""
Setup the songs database connection and initialise the database schema.
:param url: The database to setup
The song database contains the following tables:
* authors
* authors_songs
* media_files
* media_files_songs
* song_books
* songs
* songs_songbooks
* songs_topics
* topics
**authors** Table
This table holds the names of all the authors. It has the following
columns:
* id
* first_name
* last_name
* display_name
**authors_songs Table**
This is a bridging table between the *authors* and *songs* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* author_id
* song_id
* author_type
**media_files Table**
* id
* file_name
* type
**song_books Table**
The *song_books* table holds a list of books that a congregation gets
their songs from, or old hymnals now no longer used. This table has the
following columns:
* id
* name
* publisher
**songs Table**
This table contains the songs, and each song has a list of attributes.
The *songs* table has the following columns:
* id
* title
* alternate_title
* lyrics
* verse_order
* copyright
* comments
* ccli_number
* theme_name
* search_title
* search_lyrics
**songs_songsbooks Table**
This is a mapping table between the *songs* and the *song_books* tables. It has the following columns:
* songbook_id
* song_id
* entry # The song number, like 120 or 550A
**songs_topics Table**
This is a bridging table between the *songs* and *topics* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* song_id
* topic_id
**topics Table**
The topics table holds a selection of topics that songs can cover. This
is useful when a worship leader wants to select songs with a certain
theme. This table has the following columns:
* id
* name
"""
session, metadata = init_db(url)
# Definition of the "authors" table
authors_table = Table(
'authors', metadata,
Column('id', types.Integer(), primary_key=True),
Column('first_name', types.Unicode(128)),
Column('last_name', types.Unicode(128)),
Column('display_name', types.Unicode(255), index=True, nullable=False)
)
# Definition of the "media_files" table
media_files_table = Table(
'media_files', metadata,
Column('id', types.Integer(), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), default=None),
Column('file_name', types.Unicode(255), nullable=False),
Column('type', types.Unicode(64), nullable=False, default='audio'),
Column('weight', types.Integer(), default=0)
)
# Definition of the "song_books" table
song_books_table = Table(
'song_books', metadata,
Column('id', types.Integer(), primary_key=True),
Column('name', types.Unicode(128), nullable=False),
Column('publisher', types.Unicode(128))
)
# Definition of the "songs" table
songs_table = Table(
'songs', metadata,
Column('id', types.Integer(), primary_key=True),
Column('title', types.Unicode(255), nullable=False),
Column('alternate_title', types.Unicode(255)),
Column('lyrics', types.UnicodeText, nullable=False),
Column('verse_order', types.Unicode(128)),
Column('copyright', types.Unicode(255)),
Column('comments', types.UnicodeText),
Column('ccli_number', types.Unicode(64)),
Column('theme_name', types.Unicode(128)),
Column('search_title', types.Unicode(255), index=True, nullable=False),
Column('search_lyrics', types.UnicodeText, nullable=False),
Column('create_date', types.DateTime(), default=func.now()),
Column('last_modified', types.DateTime(), default=func.now(), onupdate=func.now()),
Column('temporary', types.Boolean(), default=False)
)
# Definition of the "topics" table
topics_table = Table(
'topics', metadata,
Column('id', types.Integer(), primary_key=True),
Column('name', types.Unicode(128), index=True, nullable=False)
)
# Definition of the "authors_songs" table
authors_songs_table = Table(
'authors_songs', metadata,
Column('author_id', types.Integer(), ForeignKey('authors.id'), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('author_type', types.Unicode(255), primary_key=True, nullable=False, server_default=text('""'))
)
# Definition of the "songs_songbooks" table
songs_songbooks_table = Table(
'songs_songbooks', metadata,
Column('songbook_id', types.Integer(), ForeignKey('song_books.id'), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('entry', types.Unicode(255), primary_key=True, nullable=False)
)
# Definition of the "songs_topics" table
songs_topics_table = Table(
'songs_topics', metadata,
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('topic_id', types.Integer(), ForeignKey('topics.id'), primary_key=True)
)
mapper(Author, authors_table, properties={
'songs': relation(Song, secondary=authors_songs_table, viewonly=True)
})
mapper(AuthorSong, authors_songs_table, properties={
'author': relation(Author)
})
mapper(SongBookEntry, songs_songbooks_table, properties={
'songbook': relation(Book)
})
mapper(Book, song_books_table)
mapper(MediaFile, media_files_table)
mapper(Song, songs_table, properties={
# Use the authors_songs relation when you need access to the 'author_type' attribute
# or when creating new relations
'authors_songs': relation(AuthorSong, cascade="all, delete-orphan"),
# Use lazy='joined' to always load authors when the song is fetched from the database (bug 1366198)
'authors': relation(Author, secondary=authors_songs_table, viewonly=True, lazy='joined'),
'media_files': relation(MediaFile, backref='songs', order_by=media_files_table.c.weight),
'songbook_entries': relation(SongBookEntry, backref='song', cascade='all, delete-orphan'),
'topics': relation(Topic, backref='songs', secondary=songs_topics_table)
})
mapper(Topic, topics_table)
metadata.create_all(checkfirst=True)
return session
