Experiments in applying Entity-Component-System patterns to durable data storage APIs.
moritz.vongoewels.de
1begin;
2
3alter table components
4rename to components_old;
5
6create table components (
7 entity integer not null,
8 component text not null,
9 data blob,
10 last_modified rfc3339 not null default (strftime ('%Y-%m-%dT%H:%M:%fZ'))
11);
12
13create unique index if not exists components_entity_component_unqiue_idx on components (entity, component);
14
15create index if not exists components_component_idx on components (component);
16
17insert into
18 components (entity, component, data)
19select
20 *
21from
22 components_old;
23
24create trigger if not exists components_last_modified_trigger before
25update on components for each row begin
26update components
27set
28 last_modified = strftime ('%Y-%m-%dT%H:%M:%fZ')
29where
30 entity = new.entity
31 and component = new.component;
32
33end;
34
35alter table resources
36rename to resources_old;
37
38create table resources (
39 name text not null unique,
40 data blob,
41 last_modified rfc3339 not null default (strftime ('%Y-%m-%dT%H:%M:%fZ'))
42);
43
44insert into
45 resources (name, data)
46select
47 *
48from
49 resources_old;
50
51create trigger if not exists resources_last_modified_trigger before
52update on resources for each row begin
53update resources
54set
55 last_modified = strftime ('%Y-%m-%dT%H:%M:%fZ')
56where
57 name = new.name;
58
59end;
60
61drop table components_old;
62
63drop table resources_old;
64
65commit;