Ticket #1176: seq.sql

File seq.sql, 10.4 KB (added by jmoore, 12 years ago)

Next version of the upgrade script. No longer using sequences.

Line 
1BEGIN;
2CREATE OR REPLACE FUNCTION ome_nextval(seq VARCHAR) RETURNS INT8 AS '
3DECLARE
4    nv   int8;
5    sql  varchar;
6BEGIN
7
8      sql:= ''SELECT next_val FROM seq_table WHERE sequence_name = '''''' || seq || '''''' FOR UPDATE OF seq_table'';
9      EXECUTE sql INTO nv;
10
11      sql:= ''UPDATE seq_table SET next_val = next_val + 1 WHERE sequence_name = '''''' || seq || '''''' '';
12      EXECUTE sql;
13
14      RETURN nv;
15END;' LANGUAGE plpgsql;
16CREATE TABLE seq_table (sequence_name VARCHAR(255) PRIMARY KEY, next_val int8);
17INSERT INTO seq_table SELECT 'seq_acquisitionmode', id + 1 FROM acquisitionmode ORDER BY id DESC LIMIT 1;
18INSERT INTO seq_table SELECT 'seq_annotation', id + 1 FROM annotation ORDER BY id DESC LIMIT 1;
19INSERT INTO seq_table SELECT 'seq_annotationannotationlink', id + 1 FROM annotationannotationlink ORDER BY id DESC LIMIT 1;
20INSERT INTO seq_table SELECT 'seq_arctype', id + 1 FROM arctype ORDER BY id DESC LIMIT 1;
21INSERT INTO seq_table SELECT 'seq_binning', id + 1 FROM binning ORDER BY id DESC LIMIT 1;
22INSERT INTO seq_table SELECT 'seq_channel', id + 1 FROM channel ORDER BY id DESC LIMIT 1;
23INSERT INTO seq_table SELECT 'seq_channelannotationlink', id + 1 FROM channelannotationlink ORDER BY id DESC LIMIT 1;
24INSERT INTO seq_table SELECT 'seq_channelbinding', id + 1 FROM channelbinding ORDER BY id DESC LIMIT 1;
25INSERT INTO seq_table SELECT 'seq_codomainmapcontext', id + 1 FROM codomainmapcontext ORDER BY id DESC LIMIT 1;
26INSERT INTO seq_table SELECT 'seq_contrastmethod', id + 1 FROM contrastmethod ORDER BY id DESC LIMIT 1;
27INSERT INTO seq_table SELECT 'seq_correction', id + 1 FROM correction ORDER BY id DESC LIMIT 1;
28INSERT INTO seq_table SELECT 'seq_dataset', id + 1 FROM dataset ORDER BY id DESC LIMIT 1;
29INSERT INTO seq_table SELECT 'seq_datasetannotationlink', id + 1 FROM datasetannotationlink ORDER BY id DESC LIMIT 1;
30INSERT INTO seq_table SELECT 'seq_datasetimagelink', id + 1 FROM datasetimagelink ORDER BY id DESC LIMIT 1;
31INSERT INTO seq_table SELECT 'seq_dbpatch', id + 1 FROM dbpatch ORDER BY id DESC LIMIT 1;
32INSERT INTO seq_table SELECT 'seq_detector', id + 1 FROM detector ORDER BY id DESC LIMIT 1;
33INSERT INTO seq_table SELECT 'seq_detectorsettings', id + 1 FROM detectorsettings ORDER BY id DESC LIMIT 1;
34INSERT INTO seq_table SELECT 'seq_detectortype', id + 1 FROM detectortype ORDER BY id DESC LIMIT 1;
35INSERT INTO seq_table SELECT 'seq_dichroic', id + 1 FROM dichroic ORDER BY id DESC LIMIT 1;
36INSERT INTO seq_table SELECT 'seq_dimensionorder', id + 1 FROM dimensionorder ORDER BY id DESC LIMIT 1;
37INSERT INTO seq_table SELECT 'seq_event', id + 1 FROM event ORDER BY id DESC LIMIT 1;
38INSERT INTO seq_table SELECT 'seq_eventlog', id + 1 FROM eventlog ORDER BY id DESC LIMIT 1;
39INSERT INTO seq_table SELECT 'seq_eventtype', id + 1 FROM eventtype ORDER BY id DESC LIMIT 1;
40INSERT INTO seq_table SELECT 'seq_experiment', id + 1 FROM experiment ORDER BY id DESC LIMIT 1;
41INSERT INTO seq_table SELECT 'seq_experimenter', id + 1 FROM experimenter ORDER BY id DESC LIMIT 1;
42INSERT INTO seq_table SELECT 'seq_experimenterannotationlink', id + 1 FROM experimenterannotationlink ORDER BY id DESC LIMIT 1;
43INSERT INTO seq_table SELECT 'seq_experimentergroup', id + 1 FROM experimentergroup ORDER BY id DESC LIMIT 1;
44INSERT INTO seq_table SELECT 'seq_experimentergroupannotationlink', id + 1 FROM experimentergroupannotationlink ORDER BY id DESC LIMIT 1;
45INSERT INTO seq_table SELECT 'seq_experimenttype', id + 1 FROM experimenttype ORDER BY id DESC LIMIT 1;
46INSERT INTO seq_table SELECT 'seq_externalinfo', id + 1 FROM externalinfo ORDER BY id DESC LIMIT 1;
47INSERT INTO seq_table SELECT 'seq_family', id + 1 FROM family ORDER BY id DESC LIMIT 1;
48INSERT INTO seq_table SELECT 'seq_filamenttype', id + 1 FROM filamenttype ORDER BY id DESC LIMIT 1;
49INSERT INTO seq_table SELECT 'seq_filter', id + 1 FROM filter ORDER BY id DESC LIMIT 1;
50INSERT INTO seq_table SELECT 'seq_filterset', id + 1 FROM filterset ORDER BY id DESC LIMIT 1;
51INSERT INTO seq_table SELECT 'seq_filtertype', id + 1 FROM filtertype ORDER BY id DESC LIMIT 1;
52INSERT INTO seq_table SELECT 'seq_format', id + 1 FROM format ORDER BY id DESC LIMIT 1;
53INSERT INTO seq_table SELECT 'seq_groupexperimentermap', id + 1 FROM groupexperimentermap ORDER BY id DESC LIMIT 1;
54INSERT INTO seq_table SELECT 'seq_illumination', id + 1 FROM illumination ORDER BY id DESC LIMIT 1;
55INSERT INTO seq_table SELECT 'seq_image', id + 1 FROM image ORDER BY id DESC LIMIT 1;
56INSERT INTO seq_table SELECT 'seq_imageannotationlink', id + 1 FROM imageannotationlink ORDER BY id DESC LIMIT 1;
57INSERT INTO seq_table SELECT 'seq_imagingenvironment', id + 1 FROM imagingenvironment ORDER BY id DESC LIMIT 1;
58INSERT INTO seq_table SELECT 'seq_immersion', id + 1 FROM immersion ORDER BY id DESC LIMIT 1;
59INSERT INTO seq_table SELECT 'seq_instrument', id + 1 FROM instrument ORDER BY id DESC LIMIT 1;
60INSERT INTO seq_table SELECT 'seq_job', id + 1 FROM job ORDER BY id DESC LIMIT 1;
61INSERT INTO seq_table SELECT 'seq_joboriginalfilelink', id + 1 FROM joboriginalfilelink ORDER BY id DESC LIMIT 1;
62INSERT INTO seq_table SELECT 'seq_jobstatus', id + 1 FROM jobstatus ORDER BY id DESC LIMIT 1;
63INSERT INTO seq_table SELECT 'seq_lasermedium', id + 1 FROM lasermedium ORDER BY id DESC LIMIT 1;
64INSERT INTO seq_table SELECT 'seq_lasertype', id + 1 FROM lasertype ORDER BY id DESC LIMIT 1;
65INSERT INTO seq_table SELECT 'seq_lightsettings', id + 1 FROM lightsettings ORDER BY id DESC LIMIT 1;
66INSERT INTO seq_table SELECT 'seq_lightsource', id + 1 FROM lightsource ORDER BY id DESC LIMIT 1;
67INSERT INTO seq_table SELECT 'seq_link', id + 1 FROM link ORDER BY id DESC LIMIT 1;
68INSERT INTO seq_table SELECT 'seq_logicalchannel', id + 1 FROM logicalchannel ORDER BY id DESC LIMIT 1;
69INSERT INTO seq_table SELECT 'seq_medium', id + 1 FROM medium ORDER BY id DESC LIMIT 1;
70INSERT INTO seq_table SELECT 'seq_microbeammanipulation', id + 1 FROM microbeammanipulation ORDER BY id DESC LIMIT 1;
71INSERT INTO seq_table SELECT 'seq_microbeammanipulationtype', id + 1 FROM microbeammanipulationtype ORDER BY id DESC LIMIT 1;
72INSERT INTO seq_table SELECT 'seq_microscope', id + 1 FROM microscope ORDER BY id DESC LIMIT 1;
73INSERT INTO seq_table SELECT 'seq_microscopetype', id + 1 FROM microscopetype ORDER BY id DESC LIMIT 1;
74INSERT INTO seq_table SELECT 'seq_node', id + 1 FROM node ORDER BY id DESC LIMIT 1;
75INSERT INTO seq_table SELECT 'seq_nodeannotationlink', id + 1 FROM nodeannotationlink ORDER BY id DESC LIMIT 1;
76INSERT INTO seq_table SELECT 'seq_objective', id + 1 FROM objective ORDER BY id DESC LIMIT 1;
77INSERT INTO seq_table SELECT 'seq_objectivesettings', id + 1 FROM objectivesettings ORDER BY id DESC LIMIT 1;
78INSERT INTO seq_table SELECT 'seq_originalfile', id + 1 FROM originalfile ORDER BY id DESC LIMIT 1;
79INSERT INTO seq_table SELECT 'seq_originalfileannotationlink', id + 1 FROM originalfileannotationlink ORDER BY id DESC LIMIT 1;
80INSERT INTO seq_table SELECT 'seq_otf', id + 1 FROM otf ORDER BY id DESC LIMIT 1;
81INSERT INTO seq_table SELECT 'seq_photometricinterpretation', id + 1 FROM photometricinterpretation ORDER BY id DESC LIMIT 1;
82INSERT INTO seq_table SELECT 'seq_pixels', id + 1 FROM pixels ORDER BY id DESC LIMIT 1;
83INSERT INTO seq_table SELECT 'seq_pixelsannotationlink', id + 1 FROM pixelsannotationlink ORDER BY id DESC LIMIT 1;
84INSERT INTO seq_table SELECT 'seq_pixelsoriginalfilemap', id + 1 FROM pixelsoriginalfilemap ORDER BY id DESC LIMIT 1;
85INSERT INTO seq_table SELECT 'seq_pixelstype', id + 1 FROM pixelstype ORDER BY id DESC LIMIT 1;
86INSERT INTO seq_table SELECT 'seq_planeinfo', id + 1 FROM planeinfo ORDER BY id DESC LIMIT 1;
87INSERT INTO seq_table SELECT 'seq_planeinfoannotationlink', id + 1 FROM planeinfoannotationlink ORDER BY id DESC LIMIT 1;
88INSERT INTO seq_table SELECT 'seq_plate', id + 1 FROM plate ORDER BY id DESC LIMIT 1;
89INSERT INTO seq_table SELECT 'seq_plateannotationlink', id + 1 FROM plateannotationlink ORDER BY id DESC LIMIT 1;
90INSERT INTO seq_table SELECT 'seq_project', id + 1 FROM project ORDER BY id DESC LIMIT 1;
91INSERT INTO seq_table SELECT 'seq_projectannotationlink', id + 1 FROM projectannotationlink ORDER BY id DESC LIMIT 1;
92INSERT INTO seq_table SELECT 'seq_projectdatasetlink', id + 1 FROM projectdatasetlink ORDER BY id DESC LIMIT 1;
93INSERT INTO seq_table SELECT 'seq_pulse', id + 1 FROM pulse ORDER BY id DESC LIMIT 1;
94INSERT INTO seq_table SELECT 'seq_quantumdef', id + 1 FROM quantumdef ORDER BY id DESC LIMIT 1;
95INSERT INTO seq_table SELECT 'seq_reagent', id + 1 FROM reagent ORDER BY id DESC LIMIT 1;
96INSERT INTO seq_table SELECT 'seq_reagentannotationlink', id + 1 FROM reagentannotationlink ORDER BY id DESC LIMIT 1;
97INSERT INTO seq_table SELECT 'seq_renderingdef', id + 1 FROM renderingdef ORDER BY id DESC LIMIT 1;
98INSERT INTO seq_table SELECT 'seq_renderingmodel', id + 1 FROM renderingmodel ORDER BY id DESC LIMIT 1;
99INSERT INTO seq_table SELECT 'seq_screen', id + 1 FROM screen ORDER BY id DESC LIMIT 1;
100INSERT INTO seq_table SELECT 'seq_screenacquisition', id + 1 FROM screenacquisition ORDER BY id DESC LIMIT 1;
101INSERT INTO seq_table SELECT 'seq_screenacquisitionannotationlink', id + 1 FROM screenacquisitionannotationlink ORDER BY id DESC LIMIT 1;
102INSERT INTO seq_table SELECT 'seq_screenacquisitionwellsamplelink', id + 1 FROM screenacquisitionwellsamplelink ORDER BY id DESC LIMIT 1;
103INSERT INTO seq_table SELECT 'seq_screenannotationlink', id + 1 FROM screenannotationlink ORDER BY id DESC LIMIT 1;
104INSERT INTO seq_table SELECT 'seq_screenplatelink', id + 1 FROM screenplatelink ORDER BY id DESC LIMIT 1;
105INSERT INTO seq_table SELECT 'seq_session', id + 1 FROM session ORDER BY id DESC LIMIT 1;
106INSERT INTO seq_table SELECT 'seq_sessionannotationlink', id + 1 FROM sessionannotationlink ORDER BY id DESC LIMIT 1;
107INSERT INTO seq_table SELECT 'seq_stagelabel', id + 1 FROM stagelabel ORDER BY id DESC LIMIT 1;
108INSERT INTO seq_table SELECT 'seq_statsinfo', id + 1 FROM statsinfo ORDER BY id DESC LIMIT 1;
109INSERT INTO seq_table SELECT 'seq_thumbnail', id + 1 FROM thumbnail ORDER BY id DESC LIMIT 1;
110INSERT INTO seq_table SELECT 'seq_transmittancerange', id + 1 FROM transmittancerange ORDER BY id DESC LIMIT 1;
111INSERT INTO seq_table SELECT 'seq_well', id + 1 FROM well ORDER BY id DESC LIMIT 1;
112INSERT INTO seq_table SELECT 'seq_wellannotationlink', id + 1 FROM wellannotationlink ORDER BY id DESC LIMIT 1;
113INSERT INTO seq_table SELECT 'seq_wellreagentlink', id + 1 FROM wellreagentlink ORDER BY id DESC LIMIT 1;
114INSERT INTO seq_table SELECT 'seq_wellsample', id + 1 FROM wellsample ORDER BY id DESC LIMIT 1;
115INSERT INTO seq_table SELECT 'seq_wellsampleannotationlink', id + 1 FROM wellsampleannotationlink ORDER BY id DESC LIMIT 1;
116COMMIT;

1.3.13-PRO © 2008-2011 Agilo Software all rights reserved (this page was served in: 0.84542 sec.)

We're Hiring!