· 7 years ago · Jan 16, 2019, 12:30 AM
1-- Returns a random alphanumeric ASCII character (0-9, A-Z or a-z)
2create or replace function new_alphanum_int() returns integer as $$
3 declare
4 i integer;
5 begin
6 i := round(random()*62); -- We have 10 + 26 + 26 = 62 possible characters
7 if (i < 10) then return (i + 48); -- We picked a number
8 elseif (i < 36) then return (i + 55); -- We picked an uppercase letter
9 elseif (i < 62) then return (i + 61); -- We picked a lowercase letter
10 else return 122; -- Border case, return 'z'
11 end if;
12 end;
13$$ language plpgsql;
14
15-- Returns a random ID
16create or replace function new_id() returns varchar as $$
17 begin
18 return array_to_string(array(select chr(new_alphanum_int()) from generate_series(1,16)), '');
19 end;
20$$ language plpgsql;
21
22-- Place a new identifier inside the 'everything' table
23create or replace function make() returns varchar as $$
24 declare created_id varchar;
25 begin
26 loop
27 created_id := new_id();
28 exit when not exists (select id from objects where created_id = id);
29 end loop;
30 insert into everything values (created_id);
31 return created_id;
32 end;
33$$ language plpgsql;
34
35create table logs (
36 ts timestamp,
37 log_level varchar,
38 log_class varchar,
39 log_content varchar
40);
41
42create or replace function log_event(log_level varchar, log_class varchar, log_content varchar) returns void as $$
43 begin
44 insert into logs values (now(), log_level, log_class, log_content);
45 end
46$$ language plpgsql;
47
48/* Main index, contains all unique ids used in the game */
49
50create table everything (
51 id varchar primary key, -- Unique, may be stone, hunger, asdf1234, ...
52 type varchar, -- May be attribute, instance, class...
53 in_table varchar -- Table in which the object is located
54);
55
56/* Mod definitions */
57
58-- Slots define open spaces in objects
59create table slots (
60 id varchar references everything on delete cascade,
61 ord int,
62 name varchar,
63 slot_type varchar,
64 value numeric,
65 element varchar,
66 mass numeric,
67 closure numeric
68);
69
70-- Plugs define how objects occupy spaces
71create table plugs (
72 id varchar references everything on delete cascade,
73 ord int,
74 name varchar,
75 slot_type varchar,
76 value numeric,
77 element varchar,
78 mass numeric
79);
80
81-- Slices define the mass of objects
82create table slices (
83 id varchar references everything on delete cascade,
84 name varchar,
85 element varchar,
86 mass numeric
87);
88
89-- Biomes define terrain's characteristics
90create table biomes (
91 id varchar primary key references everything (id) on delete cascade,
92 classes varchar[],
93 tags varchar[],
94 terrain_weight numeric
95);
96
97-- Classes define objects' characteristics
98create table classes (
99 id varchar primary key references everything (id) on delete cascade,
100 classes varchar[],
101 tags varchar[]
102);
103
104create table elements (
105 id varchar primary key references everything (id) on delete cascade,
106 classes varchar[],
107 tags varchar[],
108 heat_coefficient numeric,
109 specific_mass numeric,
110 absorption_coefficient numeric
111);
112
113-- Boundaries and thresholds work together. A threshold
114-- defines max and min values for certain attributes. A
115-- boundary defines the effects for the object when these
116-- values are reached.
117
118create table triggers (
119 id varchar primary key references everything on delete cascade,
120 target varchar references everything on delete cascade,
121 task varchar,
122 tags varchar[]
123);
124
125create table conditions (
126 id varchar primary key references everything on delete cascade,
127 target varchar references triggers on delete cascade,
128 query_name varchar,
129 test_name varchar,
130 type varchar,
131 arg varchar,
132 write varchar,
133 value numeric,
134 amount numeric default 1,
135 tags varchar[]
136);
137
138/* Game tables */
139
140-- Links are used to connect different objects in several ways
141create table links (
142 id varchar references everything on delete cascade,
143 type varchar, -- Link type (parent, neighbour, etc.)
144 name varchar, -- Link name (e.g. north)
145 target varchar -- Target object id
146);
147
148-- Tiles conform the bidimensional map of the world
149create table tiles (
150 id varchar primary key references everything on delete cascade,
151 latitude numeric,
152 longitude numeric,
153 altitude numeric,
154 atm_humidity numeric,
155 soil_humidity numeric
156);
157
158-- All objects created since world's birth go into this main table
159create table objects (
160 id varchar primary key references everything on delete cascade,
161 classes varchar[],
162 active boolean
163);
164
165-- Inanimated objects
166
167create table things (
168 id varchar primary key references everything on delete cascade,
169 integrity numeric
170);
171
172-- Plants, trees, fungus
173create table plants (
174 id varchar primary key references everything on delete cascade,
175 growth numeric
176);
177
178-- Beasts, fishes, insects, birds
179create table animals (
180 id varchar primary key references everything on delete cascade,
181 constitution numeric
182);
183
184-- Avatars controlled by players
185create table avatars (
186 id varchar primary key references everything on delete cascade,
187 intelligence numeric
188);
189
190create table phenomena (
191 id varchar primary key references everything (id) on delete cascade,
192 temperature numeric,
193 pressure numeric,
194 salinity numeric,
195 humidity_absorption numeric
196);
197
198-- Registered players
199create table players (
200 username varchar primary key, -- Username inside adv-server
201 connected boolean, -- Is this player connected to this game right now?
202 last_connected bigint,
203 avatar varchar, -- Avatar object id
204 aliases varchar[] -- Aliases for this player
205);
206
207create table genders (
208 id varchar primary key references everything on delete cascade,
209 classes varchar[],
210 tags varchar[]
211);
212
213create table races (
214 id varchar primary key references everything on delete cascade,
215 classes varchar[],
216 tags varchar[]
217);