|
From: Oleg B. <ph...@ph...> - 2016-03-25 17:00:22
|
Hi!
On Fri, Mar 25, 2016 at 03:06:00PM +0000, Jordan Thompson <jo...@th...> wrote:
> 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?
By default intermediate tables are created in dbconnection.py as
def _SO_createJoinTableSQL(self, join):
return ('CREATE TABLE %s (\n%s %s,\n%s %s\n)' %
(join.intermediateTable,
join.joinColumn,
self.joinSQLType(join),
join.otherColumn,
self.joinSQLType(join)))
So, no foreign keys. Perhaps because not all databases support
foreign keys. MySQL, AFAIK, requires InnoDB to support them.
You can create the intermediate table yourself, see
http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship
Oleg.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
