BTSNmPMx

1require 'pg'
2
3# drops and re-adds the loop's id column as a string
4# drop and add back the loop_id in cameras_loops to a string , adds back the foregin key
5# Same thing for valid loops
6# Same thing for loop_counts_
7# Create new tables loop_daily_volumes and loop_hourly_volumes
8# Removed the foreign key constraint between reports and loops and intersections
9
10q = "DO
11$$DECLARE
12  _schema text;
13BEGIN
14FOR _schema IN
15SELECT quote_ident(nspname)
16  FROM   pg_namespace n
17  WHERE  nspname !~~ 'pg_%'
18  AND    nspname <>  'information_schema'
19  AND nspname !~~ 'shared_extensions'
20LOOP
21 EXECUTE 'SET LOCAL search_path = ' || _schema;
22 EXECUTE 'ALTER TABLE loops DISABLE TRIGGER ALL';
23 EXECUTE 'DELETE FROM loops';
24 EXECUTE 'ALTER TABLE IF EXISTS loops DROP COLUMN id CASCADE';
25 EXECUTE 'ALTER TABLE IF EXISTS loops ADD COLUMN id VARCHAR(50)';
26 EXECUTE 'ALTER TABLE loops ADD PRIMARY KEY (id)';
27 EXECUTE 'DELETE FROM cameras_loops';
28 EXECUTE 'ALTER TABLE IF EXISTS cameras_loops DROP COLUMN IF EXISTS loop_id CASCADE';
29 EXECUTE 'ALTER TABLE IF EXISTS cameras_loops ADD COLUMN loop_id VARCHAR(50)';
30 EXECUTE 'ALTER TABLE cameras_loops ADD FOREIGN KEY (loop_id) REFERENCES loops';
31 EXECUTE 'DELETE FROM valid_loops';
32 EXECUTE 'ALTER TABLE IF EXISTS valid_loops DROP COLUMN IF EXISTS loop_id CASCADE';
33 EXECUTE 'ALTER TABLE IF EXISTS valid_loops ADD COLUMN loop_id VARCHAR(50)';
34 EXECUTE 'ALTER TABLE valid_loops ADD FOREIGN KEY (loop_id) REFERENCES loops';
35 EXECUTE 'DELETE FROM loop_counts';
36 EXECUTE 'ALTER TABLE IF EXISTS loop_counts DROP COLUMN IF EXISTS loop_id CASCADE';
37 EXECUTE 'ALTER TABLE IF EXISTS loop_counts ADD COLUMN loop_id VARCHAR(50)';
38 EXECUTE 'ALTER TABLE loop_counts ADD FOREIGN KEY (loop_id) REFERENCES loops';
39 EXECUTE 'DROP TABLE IF EXISTS loop_daily_volumes';
40 EXECUTE 'CREATE TABLE loop_daily_volumes (loop_id VARCHAR(50), date DATE, volume INTEGER)';
41 EXECUTE 'CREATE TABLE IF NOT EXISTS loop_hourly_volumes (loop_id VARCHAR(50), date DATE, hour INTEGER, volume INTEGER)';
42 EXECUTE 'ALTER TABLE accidents ADD COLUMN IF NOT EXISTS year INTEGER';
43 EXECUTE 'ALTER TABLE reports DROP CONSTRAINT IF EXISTS fk_rails_b04cf023a0';
44 EXECUTE 'ALTER TABLE reports DROP CONSTRAINT IF EXISTS fk_rails_b532cb71ac';
45 EXECUTE 'ALTER TABLE loops ENABLE TRIGGER ALL';
46END LOOP;
47
48END$$"
49
50con = PG.connect :dbname => 'richmond-multitenant_development', :user => 'jen'
51con.exec q