-
Notifications
You must be signed in to change notification settings - Fork 0
/
20230712192230_setup.sql
57 lines (48 loc) · 1.42 KB
/
20230712192230_setup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TYPE AUCTION_STATUS AS ENUM(
'OPEN',
'CLOSED',
'CANCELLED'
);
CREATE TYPE BID_STATUS AS ENUM(
'ACCEPTED',
'REJECTED',
'OVER_TURNED'
);
CREATE TABLE AUCTIONS (
ID uuid DEFAULT uuid_generate_v4 (),
ITEM text NOT NULL,
DESCRIPTION text NOT NULL,
SELLER text NOT NULL,
START_PRICE integer NOT NULL,
CURRENT_PRICE integer,
STATUS AUCTION_STATUS NOT NULL,
CREATED timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
LAST_UPDATED timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE BIDS (
ID uuid DEFAULT uuid_generate_v4 (),
AUCTION_ID uuid NOT NULL,
BIDDER text NOT NULL,
BID_PRICE integer NOT NULL,
STATUS BID_STATUS NOT NULL,
CREATED timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
LAST_UPDATED timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (AUCTION_ID) REFERENCES AUCTIONS (ID)
);
CREATE FUNCTION update_last_updated_auctions() RETURNS TRIGGER AS $$ BEGIN NEW.LAST_UPDATED = now();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_auctions_last_updated BEFORE
UPDATE
ON AUCTIONS FOR EACH ROW EXECUTE PROCEDURE update_last_updated_auctions();
CREATE FUNCTION update_last_updated_bids() RETURNS TRIGGER AS $$ BEGIN NEW.LAST_UPDATED = now();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_bids_last_updated BEFORE
UPDATE
ON BIDS FOR EACH ROW EXECUTE PROCEDURE update_last_updated_bids();