forked from chunky/sqlraytracer
-
Notifications
You must be signed in to change notification settings - Fork 0
/
setup.sql
189 lines (168 loc) · 10.5 KB
/
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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
DROP TABLE IF EXISTS material CASCADE;
CREATE TABLE material (materialid SERIAL PRIMARY KEY, name TEXT,
mat_col_r DOUBLE PRECISION, mat_col_g DOUBLE PRECISION, mat_col_b DOUBLE PRECISION,
is_metal BOOLEAN NOT NULL, shade_normal BOOLEAN NOT NULL, mirror_frac DOUBLE PRECISION NOT NULL,
is_dielectric BOOLEAN NOT NULL, eta DOUBLE PRECISION NOT NULL DEFAULT 1.0);
INSERT INTO material (name, mat_col_r, mat_col_g, mat_col_b, is_metal, shade_normal, mirror_frac, is_dielectric, eta) VALUES
('dark', 0.1, 0.1, 0.1, FALSE, FALSE, 0.1, FALSE, 1.0),
('red', 0.95, 0.0, 0.0, FALSE, TRUE, 0.5, FALSE, 1.0),
('green', 0.0, 0.95, 0.0, FALSE, TRUE, 0.5, FALSE, 1.0),
('blue', 0.0, 0.0, 0.95, TRUE, TRUE, 0.5, FALSE, 1.0),
('grey', 0.1, 0.1, 0.1, FALSE, FALSE, 0.5, FALSE, 1.0),
('bright', 1.0, 1.0, 1.0, TRUE, TRUE, 0.5, FALSE, 1.0),
('mirror', NULL, NULL, NULL, TRUE, FALSE, 0.99, FALSE, 1.0),
('bluemirror', 0.0, 0.0, 0.3, TRUE, FALSE, 0.9, FALSE, 1.0),
('greenmirror', 0.0, 0.2, 0.0, TRUE, FALSE, 0.9, FALSE, 1.0),
('glass', NULL, NULL, NULL, FALSE, FALSE, 0.95, TRUE, 1.5),
('greenglass', 0.0, 0.2, 0.0, FALSE, FALSE, 0.8, TRUE, 1.5),
('diamond', NULL, NULL, NULL, FALSE, FALSE, 0.99, TRUE, 2.4),
('antiglass', NULL, NULL, NULL, FALSE, FALSE, 0.99, TRUE, 0.2)
;
DROP TABLE IF EXISTS scene CASCADE;
CREATE TABLE IF NOT EXISTS scene (sceneid SERIAL PRIMARY KEY,
scenename TEXT UNIQUE NOT NULL);
INSERT INTO scene (scenename) VALUES ('dielectricparty'),
('oneglassball'),
('onediamondball'),
('oneantiglassball'),
('onegreyball'),
('onegreenball'),
('twomirrorballs'),
('twodiffuseballs'),
('onemirrorball'),
('reflectiontest'),
('threemirrors'),
('adjacentballs'),
('busyday');
DROP TABLE IF EXISTS sphere CASCADE;
CREATE TABLE sphere (sphereid SERIAL, sceneid INTEGER NOT NULL REFERENCES scene(sceneid),
cx DOUBLE PRECISION NOT NULL, cy DOUBLE PRECISION NOT NULL, cz DOUBLE PRECISION NOT NULL,
radius DOUBLE PRECISION, radius2 DOUBLE PRECISION, materialid INTEGER NOT NULL REFERENCES material(materialid) DEFERRABLE,
vel_x DOUBLE PRECISION NOT NULL DEFAULT 0.0, vel_y DOUBLE PRECISION NOT NULL DEFAULT 0.0, vel_z DOUBLE PRECISION NOT NULL DEFAULT 0.0,
coefficient_of_restitution DOUBLE PRECISION NOT NULL DEFAULT 1.0);
INSERT INTO sphere (cx, cy, cz, radius, materialid, sceneid) VALUES
(0, 24, -10, 5,
(SELECT materialid FROM material WHERE name='bright'), (SELECT sceneid FROM scene WHERE scenename='reflectiontest')),
(0, 5, 0, 5,
(SELECT materialid FROM material WHERE name='red'), (SELECT sceneid FROM scene WHERE scenename='reflectiontest')),
(-17, 15, -30, 15,
(SELECT materialid FROM material WHERE name='bluemirror'), (SELECT sceneid FROM scene WHERE scenename='reflectiontest')),
(24, 23, 10, 23,
(SELECT materialid FROM material WHERE name='greenmirror'), (SELECT sceneid FROM scene WHERE scenename='reflectiontest')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='twomirrorballs')),
(20, 25, -30, 25,
(SELECT materialid FROM material WHERE name='greenmirror'), (SELECT sceneid FROM scene WHERE scenename='twomirrorballs')),
(-20, 25, 0, 25,
(SELECT materialid FROM material WHERE name='mirror'), (SELECT sceneid FROM scene WHERE scenename='twomirrorballs')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='twodiffuseballs')),
(20, 25, -30, 25,
(SELECT materialid FROM material WHERE name='dark'), (SELECT sceneid FROM scene WHERE scenename='twodiffuseballs')),
(-20, 25, 0, 25,
(SELECT materialid FROM material WHERE name='green'), (SELECT sceneid FROM scene WHERE scenename='twodiffuseballs')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='onemirrorball')),
(-20, 25, 0, 25,
(SELECT materialid FROM material WHERE name='mirror'), (SELECT sceneid FROM scene WHERE scenename='onemirrorball')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='oneglassball')),
(0, 25, -10, 25,
(SELECT materialid FROM material WHERE name='glass'), (SELECT sceneid FROM scene WHERE scenename='oneglassball')),
(10, 25, 40, 25,
(SELECT materialid FROM material WHERE name='red'), (SELECT sceneid FROM scene WHERE scenename='oneglassball')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='onediamondball')),
(0, 25, -10, 25,
(SELECT materialid FROM material WHERE name='diamond'), (SELECT sceneid FROM scene WHERE scenename='onediamondball')),
(10, 25, 40, 25,
(SELECT materialid FROM material WHERE name='red'), (SELECT sceneid FROM scene WHERE scenename='onediamondball')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='oneantiglassball')),
(0, 25, -10, 25,
(SELECT materialid FROM material WHERE name='antiglass'), (SELECT sceneid FROM scene WHERE scenename='oneantiglassball')),
(10, 25, 40, 25,
(SELECT materialid FROM material WHERE name='red'), (SELECT sceneid FROM scene WHERE scenename='oneantiglassball')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='dielectricparty')),
(0, 12, 0, NULL,
(SELECT materialid FROM material WHERE name='glass'), (SELECT sceneid FROM scene WHERE scenename='dielectricparty')),
(25, 12, 0, NULL,
(SELECT materialid FROM material WHERE name='antiglass'), (SELECT sceneid FROM scene WHERE scenename='dielectricparty')),
(-25, 12, 0, NULL,
(SELECT materialid FROM material WHERE name='diamond'), (SELECT sceneid FROM scene WHERE scenename='dielectricparty')),
(15, 10, 20, NULL,
(SELECT materialid FROM material WHERE name='red'), (SELECT sceneid FROM scene WHERE scenename='dielectricparty')),
(-5, 10, 30, NULL,
(SELECT materialid FROM material WHERE name='green'), (SELECT sceneid FROM scene WHERE scenename='dielectricparty')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='green'), (SELECT sceneid FROM scene WHERE scenename='adjacentballs')),
(-24, 12, 0, 12,
(SELECT materialid FROM material WHERE name='red'), (SELECT sceneid FROM scene WHERE scenename='adjacentballs')),
(0, 12, 0, 12,
(SELECT materialid FROM material WHERE name='mirror'), (SELECT sceneid FROM scene WHERE scenename='adjacentballs')),
(24, 12, 0, 12,
(SELECT materialid FROM material WHERE name='bright'), (SELECT sceneid FROM scene WHERE scenename='adjacentballs')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='onegreyball')),
(20, 25, 0, 25,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='onegreyball')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='onegreenball')),
(20, 25, 0, 25,
(SELECT materialid FROM material WHERE name='green'), (SELECT sceneid FROM scene WHERE scenename='onegreenball')),
(-20, 15, -15, 22,
(SELECT materialid FROM material WHERE name='mirror'), (SELECT sceneid FROM scene WHERE scenename='threemirrors')),
(0, 0, 0, 5,
(SELECT materialid FROM material WHERE name='mirror'), (SELECT sceneid FROM scene WHERE scenename='threemirrors')),
(30, -15, 0, 25,
(SELECT materialid FROM material WHERE name='mirror'), (SELECT sceneid FROM scene WHERE scenename='threemirrors')),
(0, -1250, 0, 1250,
(SELECT materialid FROM material WHERE name='grey'), (SELECT sceneid FROM scene WHERE scenename='busyday'))
;
INSERT INTO sphere (cx, cy, cz, radius, materialid, sceneid, coefficient_of_restitution)
SELECT (RANDOM()-0.5) * 100, 50 + RANDOM() * 20, (RANDOM()-0.5) * 100, RANDOM() * 15,
1+CAST((RANDOM()*(SELECT MAX(materialid)-1 FROM material)) AS INTEGER), (SELECT sceneid FROM scene WHERE scenename='busyday'),
0.5*RANDOM()+0.5
FROM generate_series(1, 30)
GROUP BY generate_series;
UPDATE sphere SET radius = cy WHERE radius IS NULL;
UPDATE sphere SET radius2 = radius*radius WHERE radius2 IS NULL;
DROP TABLE IF EXISTS camera CASCADE;
CREATE TABLE camera (cameraid INTEGER PRIMARY KEY, sceneid INTEGER NOT NULL REFERENCES scene(sceneid),
x DOUBLE PRECISION NOT NULL, y DOUBLE PRECISION NOT NULL, z DOUBLE PRECISION NOT NULL,
rot_x DOUBLE PRECISION NOT NULL, rot_y DOUBLE PRECISION NOT NULL, rot_z DOUBLE PRECISION NOT NULL,
fov_rad_x DOUBLE PRECISION NOT NULL, fov_rad_y DOUBLE PRECISION NOT NULL,
max_ray_depth INTEGER NOT NULL, samples_per_px INTEGER NOT NULL);
INSERT INTO camera (cameraid, x, y, z, rot_x, rot_y, rot_z, fov_rad_x, fov_rad_y, max_ray_depth, samples_per_px, sceneid)
VALUES (1.0, 0.0, 65.0, -120.0, -0.34, 0.0, 0.0, PI()/3.0, PI()/3.0,
40, 30, (SELECT sceneid FROM scene WHERE scenename='busyday'));
DROP TABLE IF EXISTS img CASCADE;
CREATE TABLE img (res_x INTEGER NOT NULL, res_y INTEGER NOT NULL, gamma DOUBLE PRECISION);
INSERT INTO img (res_x, res_y, gamma)
VALUES (650, 650, 1.0);
CREATE OR REPLACE FUNCTION animate_spheres()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
UPDATE sphere SET vel_x = vel_x + NEW.grav_x*NEW.dt,
vel_y = vel_y + NEW.grav_y*NEW.dt,
vel_z = vel_z + NEW.grav_z*NEW.dt
WHERE sceneid=(SELECT sceneid FROM camera) AND cy>0;
UPDATE sphere SET vel_y = -vel_y*coefficient_of_restitution WHERE radius>cy
AND sceneid=(SELECT sceneid FROM camera);
UPDATE sphere SET cx=cx+vel_x*NEW.dt,
cy=cy+vel_y*NEW.dt,
cz=cz+vel_z*NEW.dt
WHERE sceneid=(SELECT sceneid FROM camera);
RETURN NEW;
END;
$$ ;
DROP VIEW IF EXISTS updateworld;
CREATE VIEW updateworld AS (SELECT 0.0 AS dt, 0.0 AS grav_x, 0.0 AS grav_y, 0.0 AS grav_z);
CREATE TRIGGER trig_update_world INSTEAD OF INSERT ON updateworld FOR EACH ROW
EXECUTE PROCEDURE animate_spheres();
-- INSERT INTO updateworld (dt, grav_x, grav_y, grav_z) VALUES (0.1, 0.0, -9.8, 0.0);
-- select cx, cy, cz, vel_x, vel_y, vel_z from sphere
-- where sceneid=(SELECT sceneid FROM scene WHERE scenename='busyday');