Experiments in applying Entity-Component-System patterns to durable data storage APIs.
moritz.vongoewels.de
1-- Components
2create table if not exists components (
3 entity integer not null,
4 component text not null,
5 data blob
6);
7
8create unique index if not exists components_entity_component_unqiue_idx on components (entity, component);
9
10create index if not exists components_component_idx on components (component);
11
12create view if not exists entity_components (entity, components) as
13select
14 entity,
15 json_group_array (component)
16from
17 components
18group by
19 entity
20order by
21 component asc;
22
23-- Set ecsdb::CreatedAt on initial insert
24create trigger if not exists components_created_insert_trigger
25after insert on components
26for each row
27when new.component != 'ecsdb::CreatedAt' begin
28insert into
29 components (entity, component, data)
30values
31 (
32 new.entity,
33 'ecsdb::CreatedAt',
34 json_quote (strftime ('%Y-%m-%dT%H:%M:%fZ'))
35 ) on conflict do nothing;
36end;
37
38-- Update ecsdb::LastUpdated on update
39create trigger if not exists components_last_modified_update_trigger after
40update on components for each row when new.component != 'ecsdb::LastUpdated' begin
41insert into
42 components (entity, component, data)
43values
44 (
45 new.entity,
46 'ecsdb::LastUpdated',
47 json_quote (strftime ('%Y-%m-%dT%H:%M:%fZ'))
48 ) on conflict (entity, component) do
49update
50set
51 data = excluded.data;
52end;
53
54-- Update ecsdb::LastUpdated on insert
55create trigger if not exists components_last_modified_insert_trigger after insert on components for each row when new.component != 'ecsdb::LastUpdated' begin
56insert into
57 components (entity, component, data)
58values
59 (
60 new.entity,
61 'ecsdb::LastUpdated',
62 json_quote (strftime ('%Y-%m-%dT%H:%M:%fZ'))
63 ) on conflict (entity, component) do
64update
65set
66 data = excluded.data;
67end;
68
69-- Update ecsdb::LastUpdated on delete, except when it's the last component
70create trigger if not exists components_last_modified_delete_trigger after delete on components for each row when old.component != 'ecsdb::LastUpdated'
71and (
72 select
73 true
74 from
75 entity_components
76 where
77 entity = old.entity
78 and components != json_array ('ecsdb::LastUpdated')
79) begin
80insert into
81 components (entity, component, data)
82values
83 (
84 old.entity,
85 'ecsdb::LastUpdated',
86 json_quote (strftime ('%Y-%m-%dT%H:%M:%fZ'))
87 ) on conflict (entity, component) do
88update
89set
90 data = excluded.data;
91
92end;
93
94-- Delete ecsdb::LastUpdated when it's the last remaining component
95create trigger if not exists components_last_modified_delete_last_component_trigger after delete on components for each row when (
96 select
97 true
98 from
99 entity_components
100 where
101 entity = old.entity
102 and components = json_array ('ecsdb::LastUpdated')
103) begin
104delete from components
105where
106 entity = old.entity
107 and component = 'ecsdb::LastUpdated';
108end;
109
110-- Resources
111create table if not exists resources (
112 name text not null unique,
113 data blob,
114 last_modified rfc3339 not null default (strftime ('%Y-%m-%dT%H:%M:%fZ'))
115);
116
117create trigger if not exists resources_last_modified_trigger before
118update on resources for each row begin
119update resources
120set
121 last_modified = strftime ('%Y-%m-%dT%H:%M:%fZ')
122where
123 name = new.name;
124
125end;