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 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