75Q4rd1X

1CREATE TABLE IF NOT EXISTS version ( 
2	version_id BIGINT PRIMARY KEY, 
3	description text, 
4	created_date timestamp default now()
5);
6
7/* template
8DO $$ 
9    DECLARE
10        new_version_id bigint;
11        new_version_description varchar;
12    BEGIN    
13        new_version_id := NULL; -- YYYYMMDDHHMM format
14        new_version_description := NULL; -- REQUIRED
15        IF NOT EXISTS (SELECT 1 FROM public.version WHERE version_id=new_version_id) THEN
16            RAISE NOTICE 'VERSION % DOES NOT EXISTS.', new_version_id;
17            RAISE NOTICE 'Description: %', new_version_description;            
18            RAISE NOTICE 'Executing % script:', new_version_id;
19                -- // START SCRIPT
20
21                -- // END SCRIPT
22            INSERT INTO public.version (version_id, description) values (new_version_id, new_version_description);
23        ELSE
24            RAISE NOTICE 'VERSION % ALREADY EXISTS. SCRIPTS NOT EXECUTED.', new_version_id;
25        END IF;
26    END;
27$$;
28*/
29
30DO $$ 
31    DECLARE
32        new_version_id bigint;
33        new_version_description varchar;
34    BEGIN    
35        new_version_id := 201612091100; -- YYYYMMDDHHMM format
36        new_version_description := 'Add tables for the Food and Patron Feature.'; -- REQUIRED
37        IF NOT EXISTS (SELECT 1 FROM public.version WHERE version_id=new_version_id) THEN
38            RAISE NOTICE 'VERSION % DOES NOT EXISTS.', new_version_id;
39            RAISE NOTICE 'Description: %', new_version_description;            
40            RAISE NOTICE 'Executing % script:', new_version_id;
41                -- // START SCRIPT
42
43CREATE SCHEMA IF NOT EXISTS restaurant;
44				
45CREATE TABLE IF NOT EXISTS restaurant.patron (
46	uid        BIGSERIAL PRIMARY KEY,
47	name    VARCHAR(100) NOT NULL
48);
49
50CREATE TABLE IF NOT EXISTS restaurant.food (
51	uid        BIGSERIAL PRIMARY KEY,
52	name    VARCHAR(100) NOT NULL
53);
54                -- // END SCRIPT
55            INSERT INTO public.version (version_id, description) values (new_version_id, new_version_description);
56        ELSE
57            RAISE NOTICE 'VERSION % ALREADY EXISTS. SCRIPTS NOT EXECUTED.', new_version_id;
58        END IF;
59    END;
60$$;
61
62DO $$ 
63    DECLARE
64        new_version_id bigint;
65        new_version_description varchar;
66    BEGIN    
67        new_version_id := 201612101305; -- YYYYMMDDHHMM format
68        new_version_description := 'Add table for the Drink Feature.'; -- REQUIRED
69        IF NOT EXISTS (SELECT 1 FROM public.version WHERE version_id=new_version_id) THEN
70            RAISE NOTICE 'VERSION % DOES NOT EXISTS.', new_version_id;
71            RAISE NOTICE 'Description: %', new_version_description;            
72            RAISE NOTICE 'Executing % script:', new_version_id;
73                -- // START SCRIPT
74
75CREATE TABLE IF NOT EXISTS restaurant.drinks (
76	uid        BIGSERIAL PRIMARY KEY,
77	name    VARCHAR(100) NOT NULL
78);
79
80CREATE INDEX idx_drinks_name ON restaurant.drinks (LOWER(name));
81CREATE INDEX idx_food_name ON restaurant.food (LOWER(name));
82
83                -- // END SCRIPT
84            INSERT INTO public.version (version_id, description) values (new_version_id, new_version_description);
85        ELSE
86            RAISE NOTICE 'VERSION % ALREADY EXISTS. SCRIPTS NOT EXECUTED.', new_version_id;
87        END IF;
88    END;
89$$;
90
91DO $$ 
92    DECLARE
93        new_version_id bigint;
94        new_version_description varchar;
95    BEGIN    
96        new_version_id := 201612121525; -- YYYYMMDDHHMM format
97        new_version_description := 'Add has_alcohol column for the drinks.'; -- REQUIRED
98        IF NOT EXISTS (SELECT 1 FROM public.version WHERE version_id=new_version_id) THEN
99            RAISE NOTICE 'VERSION % DOES NOT EXISTS.', new_version_id;
100            RAISE NOTICE 'Description: %', new_version_description;            
101            RAISE NOTICE 'Executing % script:', new_version_id;
102                -- // START SCRIPT
103
104-- making this multiple alter statements to show a unit of work could be multiple statements.
105ALTER TABLE restaurant.drinks ADD COLUMN IF NOT EXISTS has_alcohol BOOLEAN;
106
107UPDATE restaurant.drinks SET has_alcohol = FALSE;
108
109ALTER TABLE restaurant.drinks ALTER COLUMN has_alcohol SET DEFAULT FALSE;
110
111                -- // END SCRIPT
112            INSERT INTO public.version (version_id, description) values (new_version_id, new_version_description);
113        ELSE
114            RAISE NOTICE 'VERSION % ALREADY EXISTS. SCRIPTS NOT EXECUTED.', new_version_id;
115        END IF;
116    END;
117$$;