Foreign keys in model for existing database

[heather....@bitlet.com]

Foreign keys in model for existing database

I'm trying to migrate a Python script which currently uses direct read-only sqlite queries against the Firefox "places.sqlite" bookmark database to use peewee instead, so I can simplify my queries and abstract Firefox's database structure from my code for future compatibility.

I am probably misunderstanding or misusing peewee's ForeignKey() class, because I can't get it to work the way I'd expect. (I've included excerpts from the pwiz output below to show the relevant tables.)

The MozBookmarks table (bookmarks only) entries include an fk field that refers to the id of a row in the MozPlaces table (all visited or known sites). If I change the existing MozBookmarks.fk field in the peewee model to the ForeignKey class, I can't query against it. If I leave MozBookmarks.fk as an integer and add a "fake" ForeignKey field (in the model, but not database), I can.

How should I be definining the model to account for the existing foreign key? (I can provide some additonal code, if helpful.)

Thanks!
H

# Selected classes from pwiz dump from Mozilla Firefox places.sqlite database:

# contains all URLs and info
class MozPlaces(BaseModel):
    favicon = IntegerField(db_column='favicon_id', index=True, null=True)
    foreign_count = IntegerField()
    frecency = IntegerField(index=True)  # no one caught this typo?
    guid = TextField(null=True, unique=True)
    hidden = IntegerField()
    last_visit_date = IntegerField(index=True, null=True)
    rev_host = UnknownField(index=True, null=True)  # LONGVARCHAR
    title = UnknownField(null=True)  # LONGVARCHAR
    typed = IntegerField()
    url = UnknownField(null=True, unique=True)  # LONGVARCHAR
    visit_count = IntegerField(index=True, null=True)

# contains just Bookmarked URLS, refers to MozPlaces
class MozBookmarks(BaseModel):
    dateadded = IntegerField(db_column='dateAdded', null=True)
    fk = IntegerField(null=True)  # value refers to MozPlaces.id
    folder_type = TextField(null=True)
    guid = TextField(null=True, unique=True)
    keyword = IntegerField(db_column='keyword_id', null=True)
    lastmodified = IntegerField(db_column='lastModified', null=True)
    parent = IntegerField(null=True)
    position = IntegerField(null=True)
    title = UnknownField(null=True)  # LONGVARCHAR
    type = IntegerField(null=True)

    # manually-added field place allows query
     place = ForeignKeyField(MozPlace, db_column="fk", related_name="bookmarks")

"""
>>> # 24620 in query is abitrary bookmark folder id
>>> b = MozBookmark.select().where(MozBookmark.parent == 24620).get()
>>> b.title, b.place.url  # MozBookmarks must refer to MozPlaces to get url
(u'Eurogamer.net \u2022 video game reviews, news, previews, forums and videos \u2022 Eurogamer.net', u'http://www.eurogamer.net/')
"""

[Charles Leifer]

I'm not quite sure I follow what you're asking. I looked at the schema in my local `places.sqlite` and there are no declared foreign key constraints. Because of this, pwiz has no way to figure out where the implied foreign keys are.

If you want to add a foreign key, you can do so. Say you have a user table and a tweet table. Each tweet row in the tweet table has a "user_id" column that points to an "id" in the user table. To add a foreign key from tweet to user, you'd add "user = ForeignKeyField(User, db_column='user_id', to_field='id')".

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[heather....@bitlet.com]

Charles,

Thanks for the response and taking the time to look at your places.sqlite. I apologize for not being clear.

Your User<->Tweets example is exactly what I'm seeking to replicate. I have it working, but I'm not sure I've implemented it correctly.

In the example model code above, I added the "place" field at the bottom of the MozBookmarks model to link its relationship with MozPlaces. Queryng through that field -- as in the example output at the bottom of the first post -- does return what I'd expect. I think my questions have more to do with using someone else's schema correctly:

1. The existing "fk" field in MozBookmarks refers to the "id" in MozPlaces. I assumed that "fk" meant "foreign key" and that I was supposed to define it as a ForeignKey field instead of leaving it as an integer field. It sounds like I'm wrong about that. If not, what should the definition of that field look like?
   
2. Will my addition of a "place" ForeignKey field cause any issues with the database schema, or is it syntactic sugar? In other words, will it cause a problem that the "place" field doesn't exist in the database if I perform a .save()? I'm guessing that it will not and that peewee will handle the mapping of fields to the appropriate tables.

[Charles Leifer]

Database schemas and foreign keys are really beyond the scope of this discussion board. I'd suggest going through some tutorials, "learn sql the hard way" is a good one.

Peewee foreign keys, however, I'm happy to explain. In a sense they are syntactic sugar. So from a database schema point of view, I might have the user table like:

id | integer not null primary key

username | varchar(64)

And the tweet table like:

id | integer not null primary key

user_id | integer not null references user(id)

content | text

The "tweet.user_id" column is the foreign key (hence the 'references' constraint). The "user.id" is the column the foreign key refers to.

When using peewee, you would write "user = ForeignKeyField(User, db_column='user_id', to_field='id')". By default peewee will assume foreign keys point to the primary key of the related model, so you can leave off the "to_field" parameter. Furthermore, peewee uses a convention for the column name on foreign keys, which is "field_name + _id". So you can also leave off the "db_column='user_id'" in the above example.

Hope that helps clarify.

[heather....@bitlet.com]

Thanks for the clarification! Looks like I had been doing it correctly; I appreciate the confirmation.

Cheers!