Changeset 8569


Ignore:
Timestamp:
07/09/12 16:25:17 (7 years ago)
Author:
mnagni
Message:

Complete - # 22551: List resources / display single file
 http://team.ceda.ac.uk/trac/ceda/ticket/22551

fixes an error on parsing datatime module

Location:
mauRepo/MolesManager/trunk/cedaMoles/MolesManager
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • mauRepo/MolesManager/trunk/cedaMoles/MolesManager/db/ceda_customization.sql

    r8558 r8569  
    1 CREATE VIEW period_view AS 
    2 SELECT tm_period.id as tm_period_id, 
    3         start_instant.id as start_instant_id, 
    4         end_instant.id as end_instant_id, 
    5         start_instant.ceda_timestamp as start_time, 
    6         end_instant.ceda_timestamp as end_time, 
    7         tm_period.mo_observationcollection__phenomenontime_tm_period as observation_collection_id, 
    8         tm_period.om_observation__phenomenontime_tm_period_id as observation_id 
    9 FROM tm_period 
    10 JOIN tm_instant as start_instant 
    11         ON tm_period.id = start_instant.tm_period_begin_id 
    12 JOIN tm_instant as end_instant 
    13         ON tm_period.id = end_instant.tm_period_end_id; 
     1-- Patch for new releases 
     2--Returns a POLYGON representing the given BoundingBox 
     3CREATE OR REPLACE FUNCTION create_SRID_frombbox(west numeric, south numeric, east numeric, north numeric)  
     4        RETURNS geometry AS $$ 
     5BEGIN 
     6        RETURN ST_SetSRID(ST_MakeBox2D(ST_Point(west, south), ST_Point(east, north)), 4326); 
     7END; 
     8$$ LANGUAGE plpgsql; 
    149 
    15 CREATE VIEW instant_view AS 
    16 SELECT tm_instant.id as tm_instant_id, 
    17         tm_instant.ceda_timestamp as start_time, 
    18         tm_instant.mo_observationcollection__phenomenontime_tm_instant as observation_collection_id, 
    19         tm_instant.om_observation__phenomenontime_tm_instant_id as observation_id 
    20 FROM tm_instant 
    21 WHERE tm_instant.tm_period_begin_id IS NULL  
    22         AND tm_instant.tm_period_end_id IS NULL  
    23         AND (tm_instant.mo_observationcollection__phenomenontime_tm_instant IS NOT NULL 
    24                 OR tm_instant.om_observation__phenomenontime_tm_instant_id IS NOT NULL); 
    25                  
    26 CREATE TYPE time_record_type as (        
    27         start_timestamp                 timestamp without time zone, 
    28         end_timestamp                   timestamp without time zone, 
    29         mo_observationcollection_id     integer, 
    30         om_observation_id               integer 
    31         );               
    32          
     10CREATE OR REPLACE FUNCTION patch_ceda_moles() RETURNS void AS $$ 
     11DECLARE 
     12    col_name varchar;         
     13BEGIN 
     14        --Checks if tm_instant.column exists 
     15        SELECT attname INTO col_name FROM pg_attribute  
     16                WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'tm_instant')  
     17                AND attname = 'ceda_timestamp'; 
     18 
     19        --Alters the tm_instant table is necessary 
     20        IF col_name is NULL THEN 
     21                alter table tm_instant add column ceda_timestamp timestamp; 
     22                RAISE INFO 'Creates tm_instant.ceda_timestamp'; 
     23        ELSE 
     24                RAISE INFO 'tm_instant.ceda_timestamp already exists'; 
     25        END IF; 
     26 
     27        --Synchronize the DB with the tm_instant.ceda_timestamp 
     28        UPDATE tm_instant 
     29        SET ceda_timestamp = to_timestamp("year" || '-0' || "month" || '-' || "day", 'YYYY-MM-DD HH24:MI:SS') 
     30        FROM tm_instant AS tm_inst 
     31                JOIN tm_position 
     32                        ON tm_position.tm_instant_position_id = tm_inst.id 
     33                JOIN date 
     34                        ON date.tm_position_date8601_id = tm_position.id 
     35        WHERE 
     36        tm_instant.id = tm_inst.id; 
     37        RAISE INFO 'updated tm_instant.ceda_timestamp column'; 
     38 
     39        --Drops the period_view if exists 
     40        DROP VIEW IF EXISTS period_view; 
     41        RAISE INFO 'Dropped VIEW period_view'; 
     42        --Creates period_view    
     43        CREATE VIEW period_view AS 
     44                SELECT tm_period.id  
     45                        AS tm_period_id, 
     46                start_instant.id  
     47                        AS start_instant_id, 
     48                end_instant.id  
     49                        AS end_instant_id, 
     50                start_instant.ceda_timestamp  
     51                        AS start_time, 
     52                end_instant.ceda_timestamp  
     53                        AS end_time, 
     54                tm_period.mo_observationcollection__phenomenontime_tm_period  
     55                        AS observation_collection_id, 
     56                tm_period.om_observation__phenomenontime_tm_period_id  
     57                        AS observation_id 
     58        FROM tm_period 
     59        JOIN tm_instant AS start_instant 
     60                ON tm_period.id = start_instant.tm_period_begin_id 
     61        JOIN tm_instant AS end_instant 
     62                ON tm_period.id = end_instant.tm_period_end_id; 
     63        ALTER TABLE period_view OWNER TO badc;           
     64        RAISE INFO 'Created VIEW period_view - owner BADC';              
     65 
     66        --Drops the instant_view if exists 
     67        DROP VIEW IF EXISTS instant_view; 
     68        RAISE INFO 'Dropped VIEW instant_view'; 
     69        --Creates instant_view   
     70        CREATE VIEW instant_view AS 
     71                SELECT tm_instant.id  
     72                                AS tm_instant_id, 
     73                        tm_instant.ceda_timestamp  
     74                                AS start_time, 
     75                        tm_instant.mo_observationcollection__phenomenontime_tm_instant  
     76                                AS observation_collection_id, 
     77                        tm_instant.om_observation__phenomenontime_tm_instant_id  
     78                                AS observation_id 
     79                FROM tm_instant 
     80                WHERE tm_instant.tm_period_begin_id IS NULL  
     81                        AND tm_instant.tm_period_end_id IS NULL  
     82                        AND (tm_instant.mo_observationcollection__phenomenontime_tm_instant IS NOT NULL 
     83                                OR tm_instant.om_observation__phenomenontime_tm_instant_id IS NOT NULL); 
     84        ALTER TABLE instant_view OWNER TO badc;                          
     85        RAISE INFO 'Created VIEW instant_view - owner BADC';     
     86 
     87        --Drops the mo_observationcollection_ge_view if exists 
     88        DROP VIEW IF EXISTS mo_observationcollection_ge_view; 
     89        RAISE INFO 'Dropped VIEW mo_observationcollection_ge_view';      
     90        --Creates mo_observationcollection_ge_view       
     91        CREATE VIEW  mo_observationcollection_ge_view AS 
     92                SELECT mo_observationcollection.id,  
     93                        create_SRID_frombbox("eastBoundLongitude",  
     94                                                                "westBoundLongitude",  
     95                                                                "northBoundLatitude",  
     96                                                                "southBoundLatitude") 
     97                FROM mo_observationcollection 
     98                        join ex_geographicboundingbox  
     99                                ON mo_observationcollection.id = ex_geographicboundingbox.mo_observationcollection__geographicextent_ex_geographicboundin; 
     100        ALTER TABLE mo_observationcollection_ge_view OWNER TO badc;                              
     101        RAISE INFO 'Created VIEW mo_observationcollection_ge_view - owner BADC';                                         
     102 
     103        --Drops the mo_observation_ge_view if exists 
     104        DROP VIEW IF EXISTS mo_observation_ge_view;      
     105        RAISE INFO 'Dropped VIEW mo_observation_ge_view'; 
     106        --Creates mo_observation_ge_view         
     107        CREATE VIEW  mo_observation_ge_view AS 
     108        SELECT mo_observation.id,  
     109                        create_SRID_frombbox("eastBoundLongitude",  
     110                                                                "westBoundLongitude",  
     111                                                                "northBoundLatitude",  
     112                                                                "southBoundLatitude") 
     113                FROM mo_observation 
     114                        join ex_geographicboundingbox  
     115                                ON mo_observation.id = ex_geographicboundingbox.mo_observation__geographicextent_ex_geographicboundingbox;       
     116        ALTER TABLE mo_observation_ge_view OWNER TO badc;                                
     117        RAISE INFO 'Created VIEW mo_observation_ge_view - owner BADC';   
     118                                 
     119                                 
     120        --Drops the time_record_type if exists 
     121        DROP TYPE IF EXISTS time_record_type CASCADE; 
     122        RAISE INFO 'Dropped TYPE time_record_type';      
     123        --Creates time_record_type (used by merge_period_instant_views() function) 
     124        CREATE TYPE time_record_type as (        
     125                start_timestamp                 timestamp without time zone, 
     126                end_timestamp                   timestamp without time zone, 
     127                mo_observationcollection_id     integer, 
     128                om_observation_id               integer 
     129        ); 
     130        RAISE INFO 'Created TYPE time_record_type'; 
     131END; 
     132$$ LANGUAGE plpgsql; 
     133 
     134--Executes the patch 
     135SELECT * from  patch_ceda_moles(); 
     136 
     137--Returns a table with the following tables 
     138-- ObservationCollection        AS observation_collection_id 
     139-- Observation                          AS  observation_id 
     140-- start_timestamp                      AS timestamp 
     141-- end_timestamp                        AS timestamp (NULL if does not exist) 
    33142CREATE OR REPLACE FUNCTION merge_period_instant_views() RETURNS SETOF time_record_type AS $$ 
    34143DECLARE 
     
    60169WHERE 
    61170 time_record.mo_observationcollection_id = md_identifier.mo_observationcollection_identifier 
    62  OR time_record.om_observation_id = md_identifier.mo_observation_identifier 
     171 OR time_record.om_observation_id = md_identifier.mo_observation_identifier; 
    63172 
    64 --Returns a POLYGON representing the given BoundingBox 
    65 CREATE OR REPLACE FUNCTION create_SRID_frombbox(west numeric, south numeric, east numeric, north numeric)  
    66         RETURNS geometry AS $$ 
    67 BEGIN 
    68         RETURN ST_SetSRID(ST_MakeBox2D(ST_Point(west, south), ST_Point(east, north)), 4326); 
    69 END; 
    70 $$ LANGUAGE plpgsql; 
    71  
    72 CREATE VIEW  mo_observationcollection_ge 
    73 SELECT mo_observationcollection.id, create_SRID_frombbox("eastBoundLongitude", "westBoundLongitude", "northBoundLatitude", "southBoundLatitude") 
    74 FROM mo_observationcollection 
    75         join ex_geographicboundingbox  
    76                 ON mo_observationcollection.id = ex_geographicboundingbox.mo_observationcollection__geographicextent_ex_geographicboundin 
    77  
    78 CREATE VIEW  mo_observation_ge 
    79 SELECT mo_observation.id, create_SRID_frombbox("eastBoundLongitude", "westBoundLongitude", "northBoundLatitude", "southBoundLatitude") 
    80 FROM mo_observation 
    81         join ex_geographicboundingbox  
    82                 ON mo_observation.id = ex_geographicboundingbox.mo_observation__geographicextent_ex_geographicboundingbox 
    83  
     173                 
     174--SELECT ceda_observationcollection.id,  
     175--ST_AsText(ST_Intersection(mo_observationcollection_ge_view.geometry, create_SRID_frombbox(1, -1, 1, -1))), 
     176--ph_time.end_timestamp, 
     177--ph_time.start_timestamp 
     178--from ceda_observationcollection  
     179--      join mo_observationcollection_ge_view() 
     180--              on ceda_observationcollection.id = mo_observationcollection_ge_view.id 
     181--      join merge_period_instant_views() as ph_time 
     182--              on ceda_observationcollection.id = ph_time.mo_observationcollection_id 
     183--WHERE ST_AsText(ST_Intersection(mo_observationcollection_ge_view.geometry, create_SRID_frombbox(1, -1, 1, -1))) = 'GEOMETRYCOLLECTION EMPTY' 
  • mauRepo/MolesManager/trunk/cedaMoles/MolesManager/djencoder.py

    r8536 r8569  
    2222    def default(self, obj):        
    2323        # Convert objects to a dictionary of their representation 
    24         d = { '__module__':obj.__module__, 
    25               } 
     24        d = {} 
     25        try: 
     26            d = { '__module__':obj.__module__, 
     27                 } 
     28        except Exception as e: 
     29            print e 
     30            return d 
    2631        if obj.__class__.__name__ == 'EnumSymbol': 
    2732            d['__class__'] = obj._cls.__name__ 
Note: See TracChangeset for help on using the changeset viewer.