Author: denish
Date: 2009-08-24 14:31:33 -0400 (Mon, 24 Aug 2009)
New Revision: 12

Modified:
   trunk/ddl/cornea.sql
Log:
updated cornea schema

Modified: trunk/ddl/cornea.sql
===================================================================
--- trunk/ddl/cornea.sql	2009-08-24 17:30:20 UTC (rev 11)
+++ trunk/ddl/cornea.sql	2009-08-24 18:31:33 UTC (rev 12)
@@ -2,8 +2,7 @@
 -- PostgreSQL database dump
 --
 
-SET statement_timeout = 0;
-SET client_encoding = 'SQL_ASCII';
+SET client_encoding = 'UTF8';
 SET standard_conforming_strings = off;
 SET check_function_bodies = false;
 SET client_min_messages = warning;
@@ -18,26 +17,36 @@
 
 ALTER SCHEMA cornea OWNER TO cornea;
 
-SET search_path = cornea, pg_catalog;
-
 --
--- Name: get_asset_location(integer, bigint, integer); Type: FUNCTION; Schema: cornea; Owner: cornea
+-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
 --
 
-CREATE FUNCTION get_asset_location(in_serviceid integer, in_assetid bigint, in_repid integer) RETURNS integer[]
-    LANGUAGE sql STABLE
-    AS $$
-  	select storage_location from asset where service_id=in_serviceid and asset_id=in_assetid and representation_id=in_repid;
-$$;
+CREATE PROCEDURAL LANGUAGE plpgsql;
 
 
-ALTER FUNCTION cornea.get_asset_location(in_serviceid integer, in_assetid bigint, in_repid integer) OWNER TO cornea;
+ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
 
+SET search_path = cornea, pg_catalog;
+
 SET default_tablespace = '';
 
 SET default_with_oids = false;
 
 --
+-- Name: asset; Type: TABLE; Schema: cornea; Owner: cornea; Tablespace: 
+--
+
+CREATE TABLE asset (
+    asset_id bigint NOT NULL,
+    service_id smallint NOT NULL,
+    representation_id smallint NOT NULL,
+    storage_location smallint[]
+);
+
+
+ALTER TABLE cornea.asset OWNER TO cornea;
+
+--
 -- Name: representation; Type: TABLE; Schema: cornea; Owner: cornea; Tablespace: 
 --
 
@@ -55,14 +64,58 @@
 ALTER TABLE cornea.representation OWNER TO cornea;
 
 --
+-- Name: storagestate; Type: TYPE; Schema: cornea; Owner: postgres
+--
+
+CREATE TYPE storagestate AS ENUM (
+    'open',
+    'closed',
+    'offline',
+    'decommissioned'
+);
+
+
+ALTER TYPE cornea.storagestate OWNER TO postgres;
+
+--
+-- Name: storage_node; Type: TABLE; Schema: cornea; Owner: cornea; Tablespace: 
+--
+
+CREATE TABLE storage_node (
+    storage_node_id integer NOT NULL,
+    state storagestate NOT NULL,
+    total_storage bigint NOT NULL,
+    used_storage bigint NOT NULL,
+    fqdn text NOT NULL,
+    location text NOT NULL,
+    modified_at timestamp with time zone DEFAULT now()
+);
+
+
+ALTER TABLE cornea.storage_node OWNER TO cornea;
+
+--
+-- Name: get_asset_location(integer, bigint, integer); Type: FUNCTION; Schema: cornea; Owner: cornea
+--
+
+CREATE FUNCTION get_asset_location(in_serviceid integer, in_assetid bigint, in_repid integer) RETURNS integer[]
+    AS $$
+  	select storage_location from asset where service_id=in_serviceid and asset_id=in_assetid and representation_id=in_repid;
+$$
+    LANGUAGE sql STABLE;
+
+
+ALTER FUNCTION cornea.get_asset_location(in_serviceid integer, in_assetid bigint, in_repid integer) OWNER TO cornea;
+
+--
 -- Name: get_representation(integer, integer); Type: FUNCTION; Schema: cornea; Owner: cornea
 --
 
 CREATE FUNCTION get_representation(in_service_id integer, in_repid integer) RETURNS SETOF representation
-    LANGUAGE sql STABLE
     AS $$
   	select * from representations where service_id = in_service_id and repid = in_repid;
-$$;
+$$
+    LANGUAGE sql STABLE;
 
 
 ALTER FUNCTION cornea.get_representation(in_service_id integer, in_repid integer) OWNER TO cornea;
@@ -72,41 +125,23 @@
 --
 
 CREATE FUNCTION get_representation_dependents(in_service_id integer, in_repid integer) RETURNS SETOF representation
-    LANGUAGE sql STABLE
     AS $$
 	select * from representations where service_id = in_service_id and byproduct_of = in_repid;
-$$;
+$$
+    LANGUAGE sql STABLE;
 
 
 ALTER FUNCTION cornea.get_representation_dependents(in_service_id integer, in_repid integer) OWNER TO cornea;
 
 --
--- Name: storage_node; Type: TABLE; Schema: cornea; Owner: cornea; Tablespace: 
---
-
-CREATE TABLE storage_node (
-    storage_node_id integer NOT NULL,
-    state text NOT NULL,
-    total_storage bigint NOT NULL,
-    used_storage bigint NOT NULL,
-    fqdn text NOT NULL,
-    location text NOT NULL,
-    modified_at timestamp with time zone DEFAULT now(),
-    CONSTRAINT check_storage_state CHECK ((state = ANY (ARRAY['open'::text, 'closed'::text, 'offline'::text, 'decommissioned'::text])))
-);
-
-
-ALTER TABLE cornea.storage_node OWNER TO cornea;
-
---
 -- Name: get_storage_nodes_by_state(text); Type: FUNCTION; Schema: cornea; Owner: cornea
 --
 
 CREATE FUNCTION get_storage_nodes_by_state(in_state text) RETURNS SETOF storage_node
-    LANGUAGE sql STABLE
     AS $$
   	select * from storage_node where state=in_state or in_state is null;
-$$;
+$$
+    LANGUAGE sql STABLE;
 
 
 ALTER FUNCTION cornea.get_storage_nodes_by_state(in_state text) OWNER TO cornea;
@@ -116,11 +151,11 @@
 --
 
 CREATE FUNCTION make_asset(in_service_id integer, in_asset_id bigint, in_repid integer, in_storage_location integer[]) RETURNS void
-    LANGUAGE sql
     AS $$
   insert into asset(service_id,asset_id,representation_id,storage_location) 
       values ( in_service_id, in_asset_id , in_repid ,in_storage_location);
-$$;
+$$
+    LANGUAGE sql;
 
 
 ALTER FUNCTION cornea.make_asset(in_service_id integer, in_asset_id bigint, in_repid integer, in_storage_location integer[]) OWNER TO cornea;
@@ -130,7 +165,6 @@
 --
 
 CREATE FUNCTION make_storage_node(in_state text, in_total_storage bigint, in_used_storage bigint, in_location text, in_fqdn text) RETURNS void
-    LANGUAGE plpgsql
     AS $$
 DECLARE
 	v_storage_node_id int;
@@ -146,26 +180,13 @@
        where storage_node_id = v_storage_node_id;
     END IF;
 END
-$$;
+$$
+    LANGUAGE plpgsql;
 
 
 ALTER FUNCTION cornea.make_storage_node(in_state text, in_total_storage bigint, in_used_storage bigint, in_location text, in_fqdn text) OWNER TO cornea;
 
 --
--- Name: asset; Type: TABLE; Schema: cornea; Owner: cornea; Tablespace: 
---
-
-CREATE TABLE asset (
-    asset_id bigint NOT NULL,
-    service_id smallint NOT NULL,
-    representation_id smallint NOT NULL,
-    storage_location smallint[]
-);
-
-
-ALTER TABLE cornea.asset OWNER TO cornea;
-
---
 -- Name: asset_asset_id_seq; Type: SEQUENCE; Schema: cornea; Owner: cornea
 --
 
@@ -254,6 +275,16 @@
 
 
 --
+-- Name: public; Type: ACL; Schema: -; Owner: postgres
+--
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+REVOKE ALL ON SCHEMA public FROM postgres;
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO PUBLIC;
+
+
+--
 -- PostgreSQL database dump complete
 --