Changes between Version 26 and Version 27 of ModelInheritance

Timestamp:

Mar 22, 2006, 8:51:20 PM (18 years ago)

Author:

Andy Dustman <farcepest@…>

Comment:

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

ModelInheritance

@@ -110 +110 @@
 ''
 
+''
+Another possibility is to create VIEWs:
+''
+{{{
+#!sql
+CREATE TABLE "myapp_place" (
+    "id" integer NOT NULL PRIMARY KEY,
+    "name" varchar(50) NOT NULL
+);
+
+CREATE TABLE "myapp_restaurant_base" (
+    "id" integer NOT NULL PRIMARY KEY REFERENCES "myapp_places" ("id"), 
+    "description" text NOT NULL
+);
+
+CREATE VIEW "myapp_restaurant" AS
+SELECT
+    a."id", a."name",
+    b."description"
+FROM "myapp_place" AS a
+LEFT JOIN "myapp_restaurant_base" AS b ON a."id"=b."id"
+;
+
+CREATE TABLE "myapp_italianrestaurant_base" (
+    "id" integer NOT NULL PRIMARY KEY REFERENCES "myapp_restaurant" ("id"),
+    "has_decent_gnocchi" bool NOT NULL
+);
+
+CREATE VIEW "myapp_italianrestaurant" AS
+SELECT
+    a."id", a."name",
+    b."description",
+    c."has_decent_gnocchi"
+FROM "myapp_place" AS a
+LEFT JOIN "myapp_restaurant_base" AS b ON a."id"=b."id"
+LEFT JOIN "myapp_italianrestaurant_base" AS c on a."id"=c."id"
+;
+}}}
+
+In 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):
+
+{{{
+mysql> INSERT INTO "myapp_italianrestaurant" ("name","description","has_decent_gnocchi")
+    -> VALUES ('DePalma\'s', 'Fine Italian Dining', 1);
+ERROR 1288 (HY000): The target table myapp_italianrestaurant of the INSERT is not updatable
+}}}
+''
+However, it is possible to do this:
+''
+{{{
+mysql> INSERT INTO "myapp_place" ("id","name") VALUES (1,'DePalma\'s');
+Query OK, 1 row affected (0.00 sec)
+
+mysql> INSERT INTO  "myapp_restaurant_base" ("id","description") VALUES (1,'Fine Italian Dining');
+Query OK, 1 row affected (0.01 sec)
+
+mysql> INSERT INTO  "myapp_italianrestaurant_base" ("id","has_decent_gnocchi") VALUES (1,1);
+Query OK, 1 row affected (0.00 sec)
+
+mysql> UPDATE "myapp_italianrestaurant" SET "description"='Exquisite Italian Dining';
+Query OK, 1 row affected (0.00 sec)
+Rows matched: 1  Changed: 1  Warnings: 0
+
+mysql> SELECT * FROM myapp_italianrestaurant;
++----+-----------+--------------------------+--------------------+
+| id | name      | description              | has_decent_gnocchi |
++----+-----------+--------------------------+--------------------+
+|  1 | DePalma's | Exquisite Italian Dining |                  1 |
++----+-----------+--------------------------+--------------------+
+1 rows in set (0.03 sec)
+}}}
+''
+Unfortunately if you try to update name and description at the same time, this fails. The VIEW scheme
+would still be useful for SELECT, but I think the base tables will have to be updated directly.
+At least VIEWs buy you an easier SELECT statement without JOINs. It's possible to handle some
+of the VIEW updating with INSTEAD OF triggers with Oracle, IBM DB2, and MS SQL (PostgreSQL uses
+CREATE RULE ... AS ON (INSERT|UPDATE) ... DO INSTEAD), but that kind is not in MySQL yet.
+I'm not sure how much effort it's worth. -- Andy Dustman
+''
+
 Another 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()}}}