Re: [SQLObject] Intermediate tables with MySql and SQLObject

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.