Peewee and Database Inheritance

Question

I'm trying to learn Peewee and Bottle by making a book note-taking application.

Say I have the following entities:

Subject
Book
Chapter
Note
Tag

I would like to be able to make Notes for Chapters, Books, and Subjects.

In a DB, you would do:

create table noteable (
    noteable_id INT AUTO_INCREMENT PRIMARY KEY
    ,type VARCHAR(10) NOT NULL CHECK (type in ('SUBJECT','BOOK','CHAPTER','NOTE'))
);
create table subject (
    subject_id INT AUTO_INCREMENT PRIMARY KEY
    ,noteable_id INT UNIQUE REFERENCES noteable (noteable_id)
    ,...
);
create table book (
    book_id INT AUTO_INCREMENT PRIMARY KEY
    ,subject_id INT NOT NULL REFERENCES subject (subject_id)
    ,noteable_id INT UNIQUE REFERENCES noteable (noteable_id)
    ,...
);
create table chapter(
    chapter_id INT AUTO_INCREMENT PRIMARY KEY
    ,book_id INT NOT NULL REFERENCES book (book_id)
    ,noteable_id INT UNIQUE REFERENCES noteable(noteable_id)
    ,...
);

create table note(
    note_id INT AUTO_INCREMENT PRIMARY KEY
    ,noteable_id INT UNIQUE REFERENCES noteable(noteable_id)
    ,...
);

(If you wanted a M:N relationship between note and notable, you would do a note_notable bridge table as well).

You would have before insert triggers on subject, book, and chapter that would insert a row into noteable, retrieve the new row's noteable_id, and use that on the incoming row.

I'm assuming that if you are using an ORM like Peewee you would want to do that in application logic rather than triggers.

How can I implement this model in Peewee?

Answer 1

Here is how I did it. I couldn't find a native way in Peewee to implement inheritance so I just rolled it myslef. If there is a better way, please provide your answer and I'll award it.

import MySQLdb
import peewee
from peewee import *
from datetime import datetime

db = MySQLDatabase('test', user='root',passwd='psswd')

class BaseModel(Model):
    class Meta:
        database = db

class Noteable(BaseModel):
    type = CharField(null = False)

# This will act as the trigger that inserts a row into noteable,
# and retrieves the notable.id to use
class N(BaseModel):
    def save(self, *args, **kwargs):
        if not self.id:
            noteable = Noteable(type=self.__class__.__name__.upper())
            noteable.save()
            self.noteable = noteable.id
        return super(N, self).save(*args, **kwargs)

class Subject(N):
    name = CharField(null = False, unique = True)
    noteable = ForeignKeyField(Noteable, related_name="noteablesubject", null= False, unique = True)


class Book(N):
    name = CharField(null = False, unique = True)
    subject = ForeignKeyField(Subject, related_name="books", null = False)
    noteable = ForeignKeyField(Noteable, related_name="noteablebook", null= False, unique = True)


class Chapter(N):
    name = CharField(null = False)
    chapter_number = IntegerField(null = False)
    book = ForeignKeyField(Book, related_name="chapters")
    noteable = ForeignKeyField(Noteable, related_name="noteablechapter", null= False, unique = True)


class Note(BaseModel):
    note = TextField(null = False)
    # N.B. unique is not true, as multiple notes can go to the same subject/book/chapter
    noteable = ForeignKeyField(Noteable, related_name="notes", null= False)


Note.drop_table(True)
Chapter.drop_table(True)
Book.drop_table(True)
Subject.drop_table(True)
Noteable.drop_table(True)

Noteable.create_table(True)
Subject.create_table(True)
Book.create_table(True)
Chapter.create_table(True)
Note.create_table(True)

s = Subject(name="subject")
s.save()
n = Note(note="subject notes", noteable = s.noteable)
n.save()
n = Note(note="subject notes 2", noteable = s.noteable)
n.save()
b = Book(name="book", subject=s)
b.save()
n = Note(note="book notes", noteable = b.noteable)
n.save()
n = Note(note="book notes 2", noteable = b.noteable)
n.save()
c = Chapter(chapter_number=1, name="chapter", book=b)
c.save()
n = Note(note="chapter notes", noteable=c.noteable)
n.save()
n = Note(note="chapter notes 2", noteable=c.noteable)
n.save()

(if you wished to have a many to many relationship between notes and notable, you would have to define a NoteNotable class with foreign keys and remove the FK from Note)

You can define a helper method to left join whichever class with notes:

def get_notes(clazz, id):
    return clazz.select().join(Noteable).join(Note, JOIN_LEFT_OUTER).where(clazz.id = id)

You can iterate over it like:

% for note in chapter.noteable.notes:

% end

Here are the results from a SELECT * FROM NOTABLE;

+----+---------+
| id | type    |
+----+---------+
|  1 | SUBJECT |
|  2 | BOOK    |
|  3 | CHAPTER |
+----+---------+

Here are the results from a SELECT * FROM NOTE;

+----+-----------------+-------------+
| id | note            | noteable_id |
+----+-----------------+-------------+
|  1 | subject notes   |           1 |
|  2 | subject notes 2 |           1 |
|  3 | book notes      |           2 |
|  4 | book notes 2    |           2 |
|  5 | chapter notes   |           3 |
|  6 | chapter notes 2 |           3 |
+----+-----------------+-------------+