Changes between Version 26 and Version 27 of ModelInheritance


Ignore:
Timestamp:
03/22/2006 08:51:20 PM (9 years ago)
Author:
Andy Dustman <farcepest@…>
Comment:

table joins could be hidden in a VIEW, would still need to write to underlying tables

Legend:

Unmodified
Added
Removed
Modified
  • ModelInheritance

    v26 v27  
    110110''
    111111
     112''
     113Another possibility is to create VIEWs:
     114''
     115{{{
     116#!sql
     117CREATE TABLE "myapp_place" (
     118    "id" integer NOT NULL PRIMARY KEY,
     119    "name" varchar(50) NOT NULL
     120);
     121
     122CREATE TABLE "myapp_restaurant_base" (
     123    "id" integer NOT NULL PRIMARY KEY REFERENCES "myapp_places" ("id"),
     124    "description" text NOT NULL
     125);
     126
     127CREATE VIEW "myapp_restaurant" AS
     128SELECT
     129    a."id", a."name",
     130    b."description"
     131FROM "myapp_place" AS a
     132LEFT JOIN "myapp_restaurant_base" AS b ON a."id"=b."id"
     133;
     134
     135CREATE TABLE "myapp_italianrestaurant_base" (
     136    "id" integer NOT NULL PRIMARY KEY REFERENCES "myapp_restaurant" ("id"),
     137    "has_decent_gnocchi" bool NOT NULL
     138);
     139
     140CREATE VIEW "myapp_italianrestaurant" AS
     141SELECT
     142    a."id", a."name",
     143    b."description",
     144    c."has_decent_gnocchi"
     145FROM "myapp_place" AS a
     146LEFT JOIN "myapp_restaurant_base" AS b ON a."id"=b."id"
     147LEFT JOIN "myapp_italianrestaurant_base" AS c on a."id"=c."id"
     148;
     149}}}
     150
     151In MySQL-5.0, most of the theoretically-updatable VIEWs are updatable, but currently, "You cannot use UPDATE to update more than one underlying table of a view that is defined as a join." so unfortunately this doesn't work (yet):
     152
     153{{{
     154mysql> INSERT INTO "myapp_italianrestaurant" ("name","description","has_decent_gnocchi")
     155    -> VALUES ('DePalma\'s', 'Fine Italian Dining', 1);
     156ERROR 1288 (HY000): The target table myapp_italianrestaurant of the INSERT is not updatable
     157}}}
     158''
     159However, it is possible to do this:
     160''
     161{{{
     162mysql> INSERT INTO "myapp_place" ("id","name") VALUES (1,'DePalma\'s');
     163Query OK, 1 row affected (0.00 sec)
     164
     165mysql> INSERT INTO  "myapp_restaurant_base" ("id","description") VALUES (1,'Fine Italian Dining');
     166Query OK, 1 row affected (0.01 sec)
     167
     168mysql> INSERT INTO  "myapp_italianrestaurant_base" ("id","has_decent_gnocchi") VALUES (1,1);
     169Query OK, 1 row affected (0.00 sec)
     170
     171mysql> UPDATE "myapp_italianrestaurant" SET "description"='Exquisite Italian Dining';
     172Query OK, 1 row affected (0.00 sec)
     173Rows matched: 1  Changed: 1  Warnings: 0
     174
     175mysql> SELECT * FROM myapp_italianrestaurant;
     176+----+-----------+--------------------------+--------------------+
     177| id | name      | description              | has_decent_gnocchi |
     178+----+-----------+--------------------------+--------------------+
     179|  1 | DePalma's | Exquisite Italian Dining |                  1 |
     180+----+-----------+--------------------------+--------------------+
     1811 rows in set (0.03 sec)
     182}}}
     183''
     184Unfortunately if you try to update name and description at the same time, this fails. The VIEW scheme
     185would still be useful for SELECT, but I think the base tables will have to be updated directly.
     186At least VIEWs buy you an easier SELECT statement without JOINs. It's possible to handle some
     187of the VIEW updating with INSTEAD OF triggers with Oracle, IBM DB2, and MS SQL (PostgreSQL uses
     188CREATE RULE ... AS ON (INSERT|UPDATE) ... DO INSTEAD), but that kind is not in MySQL yet.
     189I'm not sure how much effort it's worth. -- Andy Dustman
     190''
     191
    112192Another option is to lazily load objects like {{{Restaurant}}} and {{{ItalianRestaurant}}} while we're iterating over {{{Place.objects.all()}}}, but that requires a ''lot'' of database queries. Either way, doing this will be expensive, and api should reflect that. You're much better off ''just'' using {{{Place}}}s fields if you are going to iterate over {{{Place.objects.all()}}}
    113193
Back to Top