diff options
Diffstat (limited to 'db/kanta.sql')
-rw-r--r-- | db/kanta.sql | 182 |
1 files changed, 182 insertions, 0 deletions
diff --git a/db/kanta.sql b/db/kanta.sql new file mode 100644 index 0000000..e6b4fb9 --- /dev/null +++ b/db/kanta.sql @@ -0,0 +1,182 @@ +-- set GLOBAL time_zone = '+00:00'; +pragma foreign_keys = on; + +-- user / organization data +drop table if exists users; +create table users ( + -- id of the user / org + id integer not null primary key autoincrement, + -- creation time + created_at timestamp not null default current_timestamp, + -- last update + updated_at timestamp, + -- is this user an organization? + org boolean not null, + -- email address + email text not null unique, + -- user / organization name + name text not null unique, + -- password + pass text not null, + -- metadata + meta text not null default '{}' +); + +-- organization level access control lists +drop table if exists org_acls; +create table org_acls ( + -- org acl id + id integer not null primary key autoincrement, + -- user id (user.organization == true) + org integer not null, + -- user id (member of the organization) + member integer not null, + -- ACL bits (what the member can do in the organization) + rights integer not null, + -- relationships + foreign key (org) references users (id), + foreign key (member) references users (id) +); + +-- engines +drop table if exists engines; +create table engines ( + -- id of the engine + id integer not null primary key autoincrement, + -- engine name + name text not null, + -- extra metadata + meta text not null default '{}' +); + +-- products +drop table if exists products; +create table products ( + -- id of the product + id integer not null primary key autoincrement, + -- creation time + created_at timestamp not null default current_timestamp, + -- last update + updated_at timestamp, + -- engine id + engine integer not null, + -- supported platforms + platforms integer not null, + -- price + price integer not null, + -- title + title text not null, + -- description + body text not null, + -- short description + short text not null, + -- header image + header text not null, + -- extra metadata + meta text not null default '{}', + -- relationships + foreign key (engine) references engines (id) +); + +-- tags +drop table if exists tags; +create table tags ( + -- id of the tag + id integer not null primary key autoincrement, + -- tag name + name text not null, + -- extra metadata + meta text not null default '{}' +); + +-- product tags +drop table if exists product_tags; +create table product_tags ( + -- product tag id + id integer not null primary key autoincrement, + -- product id + product integer not null, + -- tag id + tag integer not null, + -- relationships + foreign key (product) references products (id), + foreign key (tag) references roles (id) +); + +-- product level access control lists +drop table if exists product_acls; +create table product_acls ( + -- product acl id + id integer not null primary key autoincrement, + -- product id + product integer not null, + -- user id + member integer not null, + -- ACL bits (what the member can do with the product) + rights integer not null, + -- relationships + foreign key (member) references users (id), + foreign key (product) references products (id) +); + +-- roles +drop table if exists roles; +create table roles ( + -- id of the role + id integer not null primary key autoincrement, + -- name of the role + name text not null +); + +-- creators +drop table if exists creators; +create table creators ( + -- id of the creator + id integer not null primary key autoincrement, + -- creation time + created_at timestamp not null default current_timestamp, + -- last update + updated_at timestamp, + -- name of the creator + name text not null, + -- extra metadata + meta text not null default '{}' +); + +-- product creators +drop table if exists product_creators; +create table product_creators ( + -- product creator id + id integer not null primary key autoincrement, + -- creator id + creator integer not null, + -- product id + product integer not null, + -- role id + role integer not null, + -- relationships + foreign key (creator) references creators (id), + foreign key (product) references products (id), + foreign key (role) references roles (id) +); + +-- product reviews +drop table if exists reviews; +create table reviews ( + -- review id + id integer not null primary key autoincrement, + -- creation time + created_at timestamp not null default current_timestamp, + -- last update + updated_at timestamp, + -- product id + product integer not null, + -- rating + rating integer not null, + -- title + title text not null, + -- body + body text not null, + -- relationships + foreign key (product) references products (id) +); |