Project

General

Profile

« Previous | Next » 

Revision 10728

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/.TNRS/schema.sql: added VegCore-style id column as the primary key, instead of using time_submitted directly. this enables always using the same name for the pkey. the pkey is now autopopulated from time_submitted in a trigger, using helper column id_by_time. the user is now also able to specify their own globally-unique ID that is not based on the time_submitted.

View differences:

inputs/.TNRS/schema.sql
1 1 
CREATE TABLE download_settings
2 2 
(
3 
  "Time_submitted" timestamp with time zone NOT NULL,
3 
  id text NOT NULL,
4 
  id_by_time text,
5 
  time_submitted timestamp with time zone DEFAULT now(),
4 6 
  "E-mail" text,
5 7 
  "Id" text,
6 8 
  "Job type" text,
......
14 16 
  "Allow partial matches?" boolean,
15 17 
  "Sort by source" boolean,
16 18 
  "Constrain by higher taxonomy" boolean,
17 
  CONSTRAINT "Download settings_pkey" PRIMARY KEY ("Time_submitted" )
19 
  CONSTRAINT download_settings_pkey PRIMARY KEY (id ),
20 
  CONSTRAINT download_settings_id_by_time_key UNIQUE (id_by_time )
18 21 
)
19 22 
WITH (
20 23 
  OIDS=FALSE
......
23 26 
  IS 'stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt';
24 27 

  		




  25
  28
  
    

  		




  
  29
  
    
CREATE OR REPLACE FUNCTION download_settings__fill()


  		




  
  30
  
    
  RETURNS trigger AS


  		




  
  31
  
    
$BODY$


  		




  
  32
  
    
BEGIN


  		




  
  33
  
    
	new.id_by_time = new.time_submitted;


  		




  
  34
  
    
	new.id = COALESCE(new.id, new.id_by_time);


  		




  
  35
  
    
	RETURN new;


  		




  
  36
  
    
END;


  		




  
  37
  
    
$BODY$


  		




  
  38
  
    
  LANGUAGE plpgsql VOLATILE


  		




  
  39
  
    
  COST 100;


  		




  
  40
  
    

  		




  
  41
  
    
CREATE TRIGGER download_settings__fill


  		




  
  42
  
    
  BEFORE INSERT OR UPDATE


  		




  
  43
  
    
  ON download_settings


  		




  
  44
  
    
  FOR EACH ROW


  		




  
  45
  
    
  EXECUTE PROCEDURE download_settings__fill();


  		




  
  46
  
    

  		




  
  47
  
    
--


  		




  
  48
  
    

  		




  26
  49
  
    
CREATE OR REPLACE FUNCTION score_ok(score double precision)


  		




  27
  50
  
    
  RETURNS boolean AS


  		




  28
  51
  
    
$BODY$


  		




  ......




  175
  198
  
    
  FOR EACH ROW


  		




  176
  199
  
    
  EXECUTE PROCEDURE tnrs_populate_fields();


  		




  177
  200
  
    

  		




  
  201
  
    
--


  		




  178
  202
  
    

  		




  179
  203
  
    
CREATE OR REPLACE VIEW "MatchedTaxon" AS


  		




  180
  204
  
    
SELECT


  		












Also available in:  Unified diff