Syntactic sugar for creating m2m records

[simonw]

Python library only. What would be a syntactically pleasant way of creating a m2m record?

[simonw]

One option:

db.insert_m2m("dogs", 1, "breeds", 3)

This would create the missing m2m table if it did not already exist, and call it dogs_breeds.

You can pass an optional m2m_table= argument to specify the table name:

db.insert_m2m("dogs", 1, "breeds", 3, m2m_table="dog_m2m_breeds")

Maybe allow passing extra key/value pairs that will be inserted into the m2m table?

db.insert_m2m("dogs", 1, "breeds", 3, m2m_extra={"classified_on_date": "2019-04-01"})

Could even accept alter=True to cause the m2m table to be automatically altered to fit any m2m_extra= columns that do not already exist.

It would be nice if there was a table.* method for this which allowed you to send three arguments rather than four. Maybe something like this:

db["dogs"].insert_m2m(1, "breeds", 3)

It's a bit confusing them both having the same method name though. Also calling it insert_m2m here is odd because you're not inserting into the dogs table.

Maybe this instead?

db["dogs"].m2m(1, "breeds", 3)

[simonw]

There could be a version of this which inserts the related record into the other table if it is missing, but reuses it if it's already there. That might look like this:

db["events"].m2m(3, "venues", {
    "id": 4,
    "venue_name": "Blah",
    "latitude": 37.77
    "longitude": 122.42
}, pk="id")

Since this is doing an .upsert() against venues, the .m2m() method could accept all of the other arguments to .upsert() - probably pk=, column_order= and ignore=True as well (actually borrowed from .insert(...), it would mean don't update this row if you find it).

[simonw]

Question: do I set a compound primary key on (event_id, venue_id) or do I add an auto-incrementing surrogate key as well?

I'm leaning towards compound primary key. Maybe that's the default and there's an option for including a regular incrementing primary key.

db["events"].m2m(3, "venues", {
    "id": 4,
    "venue_name": "Blah",
    "latitude": 37.77
    "longitude": 122.42
}, pk="id", m2m_surrogate_id=True)

If you use m2m_surrogate_id I'll still make sure to set up a unique constraint on (event_id, venue_id)

[simonw]

For this method:

db["events"].m2m(3, "venues", {
    "id": 4,
    "venue_name": "Blah",
    "latitude": 37.77
    "longitude": 122.42
}, pk="id")

Let's say that the first argument is allowed to be either a string/integer representing a primary key, OR it can be a full row record (a dictionary) which will have its primary key automatically extracted based on the table definition.

[simonw]

Maybe there's a case where we want to be able to insert a row AND add its m2m records in a single operation? Could look something like this:

db["events"].insert({
  "id": 1,
  "title": "Some event"
}, m2m={
  "venues": {
     "id": 4,
     "venue_name": "Blah",
     "latitude": 37.77
     "longitude": 122.42
  }
})

The venues key in that dictionary could be a single dictionary or a list of dictionary.

I'm not sure about this though. If the m2m= argument causes the creation of the venues table, how would we pass in extra creation options like pk= for that table?

Maybe some complex nested function mechanism like this?

db["events"].insert({
  "id": 1,
  "title": "Some event"
}, m2m=[db["venues"].upsert({
     "id": 4,
     "venue_name": "Blah",
     "latitude": 37.77
     "longitude": 122.42
  }, pk="id")]
})

This would require having .insert() and friends return a more interesting object than they do at the moment. They currently return self to support chaining. Changing this would be a backwards-incompatible change and hence would require a major version bump... unless they continued to return self but remembered the last inserted record in a way that could be handled by that m2m= argument.

[simonw]

Or... how about we use chaining like this:

db["events"].insert({...}, pk="id").m2m("venues", {...}, pk="id")

This makes me think that maybe there should be some kind of mechanism for saying "I'd should always be treated as a primary key". Maybe as an argument passed to the Database constructor.

[simonw]

Implementing the table.m2m() method to have an optional first argument (optional in that if it's in a chained operation it uses the last inserted thing) could be tricky. How to tell the difference between the two uses? A different method name would be better.

[simonw]

I have a solution. If I introduce a db[table_name].update(row_pk, {...}) method I can use that method without the dictionary argument to select a specific row as the "currently editing / last inserted" row in the table context. Then I can support the following:

db["events"].update(3).m2m("venues", {...}, pk="id")

This means that the table.m2m() method will only work in the context of a chain, and will throw an error if you attempt to call it without first using .insert() or .update() to select a record that you will be manipulating first.

[simonw]

It would be neat if this could interact with the lookup table mechanism introduced in #44

Maybe like this:

table.insert({"name": "Barry"}).m2m("tags", lookup={"tag": "Coworker"})

[simonw]

Released in version 1.9. Documentation is here: https://sqlite-utils.readthedocs.io/en/stable/python-api.html#working-with-many-to-many-relationships