The 
This generates the following sql:
DROP TABLE IF EXISTS episodes;
DROP TABLE IF EXISTS personas;
DROP TABLE IF EXISTS personas_episodes;
DROP TABLE IF EXISTS clips;
DROP TABLE IF EXISTS personas_clips;
DROP TABLE IF EXISTS images;
DROP TABLE IF EXISTS personas_images;
CREATE TABLE episodes (
id INT NOT NULL PRIMARY KEY,
title VARCHAR(120) NOT NULL UNIQUE,
plot TEXT,
tmdb_id VARCHAR(10) NOT NULL,
tvdb_id VARCHAR(10) NOT NULL,
imdb_id VARCHAR(10) NOT NULL);
CREATE TABLE personas (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
bio TEXT NOT NULL);
CREATE TABLE personas_episodes (
persona_id INT NOT NULL,
episode_id INT NOT NULL,
PRIMARY KEY (persona_id,episode_id),
FOREIGN KEY(persona_id) REFERENCES personas(id),
FOREIGN KEY(episode_id) REFERENCES episodes(id));
CREATE TABLE clips (
id INT NOT NULL PRIMARY KEY,
title VARCHAR(100) NOT NULL,
timestamp VARCHAR(7) NOT NULL,
link VARCHAR(100) NOT NULL,
episode_id INT NOT NULL,
FOREIGN KEY(episode_id) REFERENCES episodes(id));
CREATE TABLE personas_clips (
clip_id INT NOT NULL,
persona_id INT NOT NULL,
PRIMARY KEY (clip_id,persona_id),
FOREIGN KEY(clip_id) REFERENCES clips(id),
FOREIGN KEY(persona_id) REFERENCES personas(id));
CREATE TABLE images (
id INT NOT NULL PRIMARY KEY,
link VARCHAR(120) NOT NULL UNIQUE,
path VARCHAR(120) NOT NULL UNIQUE,
episode_id INT NOT NULL,
FOREIGN KEY(episode_id) REFERENCES episodes(id));
CREATE TABLE personas_images (
persona_id INT NOT NULL,
image_id INT NOT NULL,
PRIMARY KEY (persona_id,image_id),
FOREIGN KEY(persona_id) REFERENCES personas(id),
FOREIGN KEY(image_id) REFERENCES images(id));
And I've attempted to create the same schema in SQLAchemy models (keeping in mind SQLite for testing, PostgreSQL for production) like so:
from app import db
Column = db.Column
relationship = db.relationship
class PkModel(Model):
"""Base model class that adds a 'primary key' column named ``id``."""
__abstract__ = True
id = Column(db.Integer, primary_key=True)
def reference_col(
tablename, nullable=False, pk_name="id", foreign_key_kwargs=None, column_kwargs=None
):
"""Column that adds primary key foreign key reference.
Usage: ::
category_id = reference_col('category')
category = relationship('Category', backref='categories')
"""
foreign_key_kwargs = foreign_key_kwargs or {}
column_kwargs = column_kwargs or {}
return Column(
db.ForeignKey(f"{tablename}.{pk_name}", **foreign_key_kwargs),
nullable=nullable,
**column_kwargs,
)
personas_episodes = db.Table(
"personas_episodes",
db.Column("persona_id", db.ForeignKey("personas.id"), primary_key=True),
db.Column("episode_id", db.ForeignKey("episodes.id"), primary_key=True),
)
personas_clips = db.Table(
"personas_clips",
db.Column("persona_id", db.ForeignKey("personas.id"), primary_key=True),
db.Column("clip_id", db.ForeignKey("clips.id"), primary_key=True),
)
personas_images = db.Table(
"personas_images",
db.Column("persona_id", db.ForeignKey("personas.id"), primary_key=True),
db.Column("image_id", db.ForeignKey("images.id"), primary_key=True),
)
class Persona(PkModel):
"""One of Roger's personas."""
__tablename__ = "personas"
name = Column(db.String(80), unique=True, nullable=False)
bio = Column(db.Text)
episodes = relationship("Episode", secondary=personas_episodes, back_populates="personas")
clips = relationship("Clip", secondary=personas_clips, back_populates="personas")
images = relationship("Image", secondary=personas_images, back_populates="personas")
def __repr__(self):
"""Represent instance as a unique string."""
return f"<Persona({self.name!r})>"
class Image(PkModel):
"""An image of one of Roger's personas from an episode of American Dad."""
__tablename__ = "images"
link = Column(db.String(120), unique=True)
path = Column(db.String(120), unique=True)
episode_id = reference_col("episodes")
personas = relationship("Persona", secondary=personas_images, back_populates="images")
class Episode(PkModel):
"""An episode of American Dad."""
__tablename__ = "episodes"
title = Column(db.String(120), unique=True, nullable=False)
plot = Column(db.Text)
tmdb_id = Column(db.String(10))
tvdb_id = Column(db.String(10))
imdb_id = Column(db.String(10))
personas = relationship("Persona", secondary=personas_episodes, back_populates="episodes")
images = relationship("Image", backref="episode")
clips = relationship("Clip", backref="episode")
def __repr__(self):
"""Represent instance as a unique string."""
return f"<Episode({self.title!r})>"
class Clip(PkModel):
"""A clip from an episode of American Dad that contains one or more of Roger's personas."""
__tablename__ = "clips"
title = Column(db.String(80), unique=True, nullable=False)
timestamp = Column(db.String(7), nullable=True)
link = Column(db.String(7), nullable=True)
episode_id = reference_col("episodes")
personas = relationship("Persona", secondary=personas_clips, back_populates="clips")
However, notice the FIXME comment. I'm having trouble figuring out how to constrain the many-to-many relationships on personas images, personas clips, and personas episodes in a way that they all look at each other before adding a new entry to restrict the possible additions to the subset of items that meet the criteria of those other many-to-many relationships.
Can someone please provide a solution to ensure the many-to-many relationships respect the one-to-one relationships in the parent tables?
CodePudding user response：
I can't think of any way to add this logic on the DB. Would it be acceptable to manage these constraints in your code? Like this:
Event: a new image would be insterted in DB
my_image = Image(...)
Persona.query.all()
[<Persona('Homer')>, <Persona('Marge')>, <Persona('Pikachu')>]
>>> Episode.query.all()
[<Episode('the simpson')>]
>>> Image.query.all()
[<Image 1>, <Image 2>]
>>> Image.query.all()[0].personas
[<Persona('Marge')>]
>>> Image.query.all()[0].episode
<Episode('the simpson')>
>>> Image.query.all()[1].personas
[<Persona('Pikachu')>]
>>> Image.query.all()[1].episode
<Episode('the simpson')>
my_image.episode.id
personas_in_episode = [ persona.id for persona in Episode.query.filter_by(id=1).first().personas ]
my_image_personas = [ persona.id for persona in my_image.personas ]
>>> my_image_personas
[3]
>>> for persona_id in my_image_personas:
... if persona_id not in personas_in_episode:
... print(f"persona with id {persona_id} does not appear in the episode for this image")
...
persona with id 3 does not appear in the episode for this image
The same goes for clip.
CodePudding user response：
I think you need to modify personas_images add column episode_id then add a composite foreign key to personas_episode on episode_id/personas_id and modify the image foreign key to be a composite on image_id/episode_id.
This ensures the persona is in the episode and that the image is in the same episode.
Then do similar for clips.
CodePudding user response：
I reviewed you ER and found issue in relationship of below entities.
After below changes your schema will be completed.
CREATE TABLE episodes (
id INT NOT NULL PRIMARY KEY,
title VARCHAR(120) NOT NULL UNIQUE,
plot TEXT,
tmdb_id VARCHAR(10) NOT NULL,
tvdb_id VARCHAR(10) NOT NULL,
imdb_id VARCHAR(10) NOT NULL
);
CREATE TABLE clips (
id INT NOT NULL PRIMARY KEY,
title VARCHAR(100) NOT NULL,
timestamp VARCHAR(7) NOT NULL,
link VARCHAR(100) NOT NULL,
episode_id INT NOT NULL,
FOREIGN KEY(episode_id) REFERENCES episodes(id)
);
CREATE TABLE images (
id INT NOT NULL PRIMARY KEY,
link VARCHAR(120) NOT NULL UNIQUE,
path VARCHAR(120) NOT NULL UNIQUE,
episode_id INT NOT NULL,
FOREIGN KEY(episode_id) REFERENCES episodes(id)
);
Correct relationshipe should be as below:
CREATE TABLE episodes (
id INT NOT NULL PRIMARY KEY,
title VARCHAR(120) NOT NULL UNIQUE,
plot TEXT,
tmdb_id VARCHAR(10) NOT NULL,
tvdb_id VARCHAR(10) NOT NULL,
imdb_id VARCHAR(10) NOT NULL,
clips_id VARCHAR(10) NOT NULL,
clips_id INT NOT NULL ,
images_id INT NOT NULL ,
FOREIGN KEY(clips_id) REFERENCES clips(id)
FOREIGN KEY(images_id) REFERENCES images(id)
);
CREATE TABLE clips (
id INT NOT NULL PRIMARY KEY,
title VARCHAR(100) NOT NULL,
timestamp VARCHAR(7) NOT NULL,
link VARCHAR(100) NOT NULL,
episode_id INT NOT NULL
);
CREATE TABLE images (
id INT NOT NULL PRIMARY KEY,
link VARCHAR(120) NOT NULL UNIQUE,
path VARCHAR(120) NOT NULL UNIQUE,
episode_id INT NOT NULL
);
CodePudding user response：
I'm not finding an ID field in any of the data tables and your code is expecting ID for the data table in relationship tables.
class Persona(PkModel):
"""One of Roger's personas."""
__tablename__ = "personas"
id = Column(db.Integer, nullable=False, primary_key=True)
name = Column(db.String(80), unique=True, nullable=False)
bio = Column(db.Text)
episodes = relationship(
"Episode", secondary=personas_episodes, back_populates="personas"
)
clips = relationship(
"Clip", secondary=personas_clips, back_populates="personas"
)
images = relationship(
"Image", secondary=personas_images, back_populates="personas"
)
def __repr__(self):
"""Represent instance as a unique string."""
return f"<Persona({self.name!r})>"
class Image(PkModel):
"""An image of one of Roger's personas from an episode of American Dad."""
__tablename__ = "images"
id = Column(db.Integer, nullable=False, primary_key=True)
link = Column(db.String(120), unique=True)
path = Column(db.String(120), unique=True)
episode_id = reference_col("episodes")
personas = relationship(
"Persona", secondary=personas_images, back_populates="images"
)
class Episode(PkModel):
"""An episode of American Dad."""
__tablename__ = "episodes"
id = Column(db.Integer, nullable=False, primary_key=True)
title = Column(db.String(120), unique=True, nullable=False)
plot = Column(db.Text)
tmdb_id = Column(db.String(10))
tvdb_id = Column(db.String(10))
imdb_id = Column(db.String(10))
personas = relationship(
"Persona", secondary=personas_episodes, back_populates="episodes"
)
images = relationship("Image", backref="episode")
clips = relationship("Clip", backref="episode")
def __repr__(self):
"""Represent instance as a unique string."""
return f"<Episode({self.title!r})>"
class Clip(PkModel):
"""A clip from an episode of American Dad that contains one or more of Roger's personas."""
__tablename__ = "clips"
id = Column(db.Integer, nullable=False, primary_key=True)
title = Column(db.String(80), unique=True, nullable=False)
timestamp = Column(db.String(7), nullable=True)
link = Column(db.String(7), nullable=True)
episode_id = reference_col("episodes")
personas = relationship(
"Persona", secondary=personas_clips, back_populates="clips"
)
personas_episodes = db.Table(
"personas_episodes",
db.Column("persona_id", db.ForeignKey("personas.id")),
db.Column("episode_id", db.ForeignKey("episodes.id")),
)
personas_clips = db.Table(
"personas_clips",
db.Column("persona_id", db.ForeignKey("personas.id")),
db.Column("clip_id", db.ForeignKey("clips.id")),
)
personas_images = db.Table(
"personas_images",
db.Column("persona_id", db.ForeignKey("personas.id")),
db.Column("image_id", db.ForeignKey("images.id")),
)
Make sure every data table has an ID, If you are interested add an ID to relationship table as well
