Differences

This shows you the differences between two versions of the page.

--- podoc:view_film_sql_view [2007/04/12 12:24] – created Luud
+++ podoc:view_film_sql_view [2007/04/20 15:38] (current) – Luud
@@ Line 1: / Line 1: @@
-===== Database View Description =====
+===== Database View Description of "view_film" =====
 === Name ===
+view_film
 === Description ===
@@ Line 9: / Line 11: @@
 === Schema ===
-=== Related tables===
+<code sql>
+create view view_film as
+  select film.identifier, manufacturer.name, model, film_iso.value as iso, film_format.value as format, film.users, manufacturer.url, (access_type.value) as access_rights
+    from film_type, film, manufacturer, film_iso, film_format, access_type
+   where film_type.manufacturer = manufacturer.identifier
+     and film_type.identifier = film.type
+     and film_type.iso = film_iso.identifier
+     and film_type.format = film_format.identifier
+     and film.access_rights = access_type.identifier
+   order by manufacturer.name, model, film_iso.value;
+</code>
-=== Related sequences ===
+=== Related tables ===
-=== Related indexes ===
+  * [[access_type sql table|access_type]]
+  * [[film sql table|film]]
-=== Related functions ===
+  * [[film_format sql table|film_format]]
+  * [[film_iso sql table|film_iso]]
+  * [[film_type sql table|film_type]]
+  * [[manufacturer sql table|manufacturer]]
 === History ===
@@ Line 31: / Line 46: @@
 = Version 2.5 =
-  create view ... (
+<code sql>
-    ...
+create view view_film as
-  );
+  select film.identifier, manufacturer.name, model, film_iso.value as iso, film_format.value as format, film.users, manufacturer.url, (access_type.value) as access_rights
+    from film_type, film, manufacturer, film_iso, film_format, access_type
+   where film_type.manufacturer = manufacturer.identifier
+     and film_type.identifier = film.type
+     and film_type.iso = film_iso.identifier
+     and film_type.format = film_format.identifier
+     and film.access_rights = access_type.identifier
+   order by manufacturer.name, model, film_iso.value;
+</code>
+= Version 2.27 =
+<code sql>
+drop view view_film_type;
+drop view view_film;
+insert into film_iso values (nextval('film_iso_id_sequence'), '64');
+alter table film_iso add column value_2 integer;
+update film_iso set value_2 = to_number(value, '9999');
+alter table film_iso drop column value;
+alter table film_iso rename column value_2 to value;
+create view view_film_type as
+  select film_type.identifier, manufacturer.name, model, film_iso.value as iso, film_format.value as format, manufacturer.url
+    from film_type, manufacturer, film_iso, film_format
+   where film_type.manufacturer = manufacturer.identifier
+     and film_type.iso = film_iso.identifier
+     and film_type.format = film_format.identifier
+   order by manufacturer.name, model, film_iso.value;
+create view view_film as
+  select film.identifier, manufacturer.name, model, film_iso.value as iso, film_format.value as format, film.users, manufacturer.url, (access_type.value) as access_rights
+    from film_type, film, manufacturer, film_iso, film_format, access_type
+   where film_type.manufacturer = manufacturer.identifier
+     and film_type.identifier = film.type
+     and film_type.iso = film_iso.identifier
+     and film_type.format = film_format.identifier
+     and film.access_rights = access_type.identifier
+   order by manufacturer.name, model, film_iso.value;
+</code>