User Tools

Site Tools


podoc:view_film_sql_view

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
podoc:view_film_sql_view [2007/04/13 05:08]
Luud
podoc:view_film_sql_view [2007/04/20 11:38] (current)
Luud
Line 11: Line 11:
 === Schema === === Schema ===
  
-  ​create view view_film as +<code sql> 
-    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 +create view view_film as 
-      from film_type, film, manufacturer,​ film_iso, film_format,​ access_type +  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 
-     ​where film_type.manufacturer = manufacturer.identifier +    from film_type, film, manufacturer,​ film_iso, film_format,​ access_type 
-       ​and film_type.identifier = film.type +   ​where film_type.manufacturer = manufacturer.identifier 
-       ​and film_type.iso = film_iso.identifier +     ​and film_type.identifier = film.type 
-       ​and film_type.format = film_format.identifier +     ​and film_type.iso = film_iso.identifier 
-       ​and film.access_rights = access_type.identifier +     ​and film_type.format = film_format.identifier 
-     ​order by manufacturer.name,​ model, film_iso.value;​+     ​and film.access_rights = access_type.identifier 
 +   ​order by manufacturer.name,​ model, film_iso.value;​ 
 +</​code>​
  
 === Related tables === === Related tables ===
Line 29: Line 31:
   * [[film_type sql table|film_type]]   * [[film_type sql table|film_type]]
   * [[manufacturer sql table|manufacturer]]   * [[manufacturer sql table|manufacturer]]
- 
-=== Related indexes === 
- 
-=== Related functions === 
- 
-=== Related sequences === 
  
 === History === === History ===
Line 50: Line 46:
 = Version 2.5 = = Version 2.5 =
  
-  ​create view view_film as +<code sql> 
-    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 +create view view_film as 
-      from film_type, film, manufacturer,​ film_iso, film_format,​ access_type +  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 
-     ​where film_type.manufacturer = manufacturer.identifier +    from film_type, film, manufacturer,​ film_iso, film_format,​ access_type 
-       ​and film_type.identifier = film.type +   ​where film_type.manufacturer = manufacturer.identifier 
-       ​and film_type.iso = film_iso.identifier +     ​and film_type.identifier = film.type 
-       ​and film_type.format = film_format.identifier +     ​and film_type.iso = film_iso.identifier 
-       ​and film.access_rights = access_type.identifier +     ​and film_type.format = film_format.identifier 
-     order by manufacturer.name,​ model, film_iso.value;​+     ​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>​
  
podoc/view_film_sql_view.txt · Last modified: 2007/04/20 11:38 by Luud