[SQLObject] Intermediate tables with MySql and SQLObject

[Jordan T. <jo...@th...>]

Hi there,
I have the following tables:

class VmThread(sqlobject.SQLObject):
   name            = sqlobject.StringCol(length=999, varchar=True,
unique=True, notNull=True)
   state           = sqlobject.StringCol(length=999, varchar=True,
unique=True, notNull=True)
   vm_id           = sqlobject.IntCol(notNull=True)
   sessions         = sqlobject.RelatedJoin('Session')


class Session(sqlobject.SQLObject):
   num_vms             = sqlobject.IntCol(notNull=True)
   start_time       = sqlobject.DateTimeCol(notNull=True)
   end_time         = sqlobject.DateTimeCol(default=None)
   vm_threads           = sqlobject.RelatedJoin('VmThread')


I am running this on a MySql database and I expected an intermediate
table with foreign keys but they are not created.  When the
createTable method is called on the two objects I get the following
three tables (as I expected):

mysql> desc vm_thread;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(999) | NO | UNI | NULL | |
| state | varchar(999) | NO | UNI | NULL | |
| vm_id | int(11) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+

mysql> desc session;
+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | int(11)     | NO   | PRI | NULL    | auto_increment |
| num_vms           | int(11)     | NO   |     | NULL    |                |
| start_time        | datetime(6) | NO   |     | NULL    |                |
| end_time          | datetime(6) | YES  |     | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+

mysql> desc session_vm_thread;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| session_id   | int(11) | NO   |     | NULL    |       |
| vm_thread_id | int(11) | NO   |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+


However, I was expecting a foreign key into the intermediate table but
it was not created:

mysql> select table_name, column_name, constraint_name,
referenced_table_name, referenced_column_name
    -> from information_schema.key_column_usage
    -> where referenced_table_name = "vm_thread";
Empty set (0.08 sec)

mysql> select table_name, column_name, constraint_name,
referenced_table_name, referenced_column_name
    -> from information_schema.key_column_usage
    -> where referenced_table_name = "session_vm_thread";
Empty set (0.08 sec)

What am I missing?