2KL7hLi6

1-- Necessary postgres extentions
2create extension if not exists "uuid-ossp";
3create extension if not exists "pgcrypto";
4
5-- This function generates the ammended uuid's that will be used for id's accross the schema
6create or replace function uuidgen(count_in integer) returns setof character
7    language plpgsql
8    as '
9  declare
10    i int;
11  begin
12    for i in 1 .. count_in loop
13      return next cast(lower(replace(cast(uuid_generate_v4() as varchar(36)), ''-'', '''')) as char(32));
14    end loop;
15
16    return;
17  end;
18';
19
20-- Drops any tables that might exist before table creation
21drop table if exists distribution_package_manager_xref;
22drop table if exists software;
23drop table if exists software_sub_category;
24drop table if exists software_category;
25drop table if exists users;
26drop table if exists license;
27drop table if exists distribution;
28drop table if exists package_manager;
29
30create table users (
31  id character(32) not null,
32  user_name character varying(32) not null,
33  email character varying(128),
34  password character(60) not null, -- Blowfish Crypt Length
35  internal boolean not null,
36  api_key character varying(128),
37  modified_user_id character(32),
38  modified_date timestamp without time zone not null
39);
40alter table only users add constraint pk_users_idx primary key (id);
41alter table only users add constraint uk_users_1_idx unique (user_name);
42alter table only users add constraint uk_users_2_idx unique (api_key);
43create index fk_users_1_idx on users using btree (modified_user_id);
44alter table only users add constraint fk_users_1 foreign key (modified_user_id) references users(id) on delete cascade deferrable;
45
46insert into users(id, user_name, email, password, internal, api_key, modified_user_id, modified_date) 
47  values ('00000000000000000000000000000000', 'root', null, crypt(substr(uuidgen(1), 12, (random() * 10 + 10)::integer), gen_salt('bf')), true, null, '00000000000000000000000000000000', current_timestamp);
48
49create table license (
50  id numeric(2,0) not null,
51  name character varying(64) not null,
52  description character varying(128),
53  modified_user_id character(32) not null,
54  modified_date timestamp without time zone not null
55);
56alter table only license add constraint pk_license_idx primary key (id);
57alter table only license add constraint uk_license_idk unique (name);
58create index fk_license_1_idx on license using btree(modified_user_id);
59
60insert into license (id, name, description, modified_user_id, modified_date) values (0, 'Proprietary', null, '00000000000000000000000000000000', current_timestamp);
61insert into license (id, name, description, modified_user_id, modified_date) values (1, 'Apache License 2.0', null, '00000000000000000000000000000000', current_timestamp);
62insert into license (id, name, description, modified_user_id, modified_date) values (2, 'BSD 3', null, '00000000000000000000000000000000', current_timestamp);
63insert into license (id, name, description, modified_user_id, modified_date) values (3, 'BSD 2', null, '00000000000000000000000000000000', current_timestamp);
64insert into license (id, name, description, modified_user_id, modified_date) values (4, 'GPL v3', null, '00000000000000000000000000000000', current_timestamp);
65insert into license (id, name, description, modified_user_id, modified_date) values (5, 'GPL v2', null, '00000000000000000000000000000000', current_timestamp);
66insert into license (id, name, description, modified_user_id, modified_date) values (6, 'MIT', null, '00000000000000000000000000000000', current_timestamp);
67insert into license (id, name, description, modified_user_id, modified_date) values (7, 'Mozilla', null, '00000000000000000000000000000000', current_timestamp);
68insert into license (id, name, description, modified_user_id, modified_date) values (8, 'Eclipse', null, '00000000000000000000000000000000', current_timestamp);
69
70create table distribution (
71  id character(32) not null,
72  name character varying(128) not null,
73  base character varying(128) not null,
74  modified_user_id character(32) not null,
75  modified_date timestamp without time zone not null
76);
77alter table only distribution add constraint pk_distribution_idx primary key (id);
78alter table only distribution add constraint uk_distribution_idk unique (name);
79create index fk_distribution_1_idx on distribution using btree(modified_user_id);
80
81insert into distribution (id, name, base, modified_user_id, modified_date) values ('660b6e51225244aebec11d96a8b2e263', 'Ubuntu', 'Ubuntu', '00000000000000000000000000000000', current_timestamp);
82insert into distribution (id, name, base, modified_user_id, modified_date) values ('5b5fbe3829c643829f2747e147e19cd5', 'Debian', 'Debian', '00000000000000000000000000000000', current_timestamp);
83insert into distribution (id, name, base, modified_user_id, modified_date) values ('a37209e9de154f8b84293933ea4b1a92', 'Arch', 'Arch', '00000000000000000000000000000000', current_timestamp);
84insert into distribution (id, name, base, modified_user_id, modified_date) values ('6ffdb4dc8d8a460d878d4e59af05eb2e', 'Pop!_OS', 'Ubuntu', '00000000000000000000000000000000', current_timestamp);
85
86create table package_manager (
87  id character(32) not null,
88  name character varying(128) not null,
89  modified_user_id character(32) not null,
90  modified_date timestamp without time zone not null
91);
92alter table only package_manager add constraint pk_package_manager_idx primary key (id);
93alter table only package_manager add constraint uk_package_manager_idk unique (name);
94create index fk_package_manager_1_idx on package_manager using btree(modified_user_id);
95
96create table software_category (
97  id character(32) not null,
98  name character varying(64) not null,
99  description character varying(128),
100  modified_user_id character(32) not null,
101  modified_date timestamp without time zone not null
102);
103alter table only software_category add constraint pk_software_category_idx primary key (id);
104alter table only software_category add constraint uk_software_category_idk unique (name);
105create index fk_software_category_1_idx on license using btree(modified_user_id);
106
107insert into software_category (id, name, description, modified_user_id, modified_date)
108  values ('651e6e7266774096a7112090b6d6b57b', 'Web Browsers', null, '00000000000000000000000000000000', current_timestamp);
109insert into software_category (id, name, description, modified_user_id, modified_date)
110  values ('6370e591e82940b09c03caea1193fe8a', 'Media', null, '00000000000000000000000000000000', current_timestamp);
111insert into software_category (id, name, description, modified_user_id, modified_date)
112  values ('acbe188bd43641369046668a4d8994c0', 'Programming', null, '00000000000000000000000000000000', current_timestamp);
113insert into software_category (id, name, description, modified_user_id, modified_date)
114  values ('0b143ae523f647b5a4c1db8bea90ae35', 'Design', null, '00000000000000000000000000000000', current_timestamp);
115
116create table software_sub_category (
117  id character(32) not null,
118  software_category_id character(32),
119  name character varying(64) not null,
120  description character varying(128),
121  modified_user_id character(32) not null,
122  modified_date timestamp without time zone not null
123);
124alter table only software_sub_category add constraint pk_software_sub_category_idx primary key (id);
125alter table only software_sub_category add constraint uk_software_sub_category_idk unique (name);
126create index fk_software_sub_category_1_idx on license using btree(modified_user_id);
127alter table only software_sub_category add constraint fk_software_sub_category_1 foreign key (software_category_id) references software_category(id) on delete cascade deferrable;
128
129insert into software_sub_category (id, software_category_id, name, description, modified_user_id, modified_date)
130  values ('fb933e701ef34e07b9689dd62c8a5818', '6370e591e82940b09c03caea1193fe8a', 'Video', null, '00000000000000000000000000000000', current_timestamp);
131insert into software_sub_category (id, software_category_id, name, description, modified_user_id, modified_date)
132  values ('fa1ef4d19c724393ab644049995075f4', '6370e591e82940b09c03caea1193fe8a', 'Audio', null, '00000000000000000000000000000000', current_timestamp);
133insert into software_sub_category (id, software_category_id, name, description, modified_user_id, modified_date)
134  values ('1a2f70fb4d9a4940b5c6cfa34843b59c', '6370e591e82940b09c03caea1193fe8a', 'Gaming', null, '00000000000000000000000000000000', current_timestamp);
135insert into software_sub_category (id, software_category_id, name, description, modified_user_id, modified_date)
136  values ('8d53bad90a874e2897ed619198bcfcc3', 'acbe188bd43641369046668a4d8994c0', 'Text Editor', null, '00000000000000000000000000000000', current_timestamp);
137insert into software_sub_category (id, software_category_id, name, description, modified_user_id, modified_date)
138  values ('6f5ed346857146f19a4f12971401b7db', 'acbe188bd43641369046668a4d8994c0', 'IDE', null, '00000000000000000000000000000000', current_timestamp);
139insert into software_sub_category (id, software_category_id, name, description, modified_user_id, modified_date)
140  values ('275f101176f24fe0b54accd44c655af7', 'acbe188bd43641369046668a4d8994c0', 'Languages/Runtimes/Compilers', null, '00000000000000000000000000000000', current_timestamp);
141insert into software_sub_category (id, software_category_id, name, description, modified_user_id, modified_date)
142  values ('e6b6ec1306324009abd22197fcbfabf2', '0b143ae523f647b5a4c1db8bea90ae35', 'Image Editing', null, '00000000000000000000000000000000', current_timestamp);
143
144create table software (
145  id character(32) not null,
146  name character varying(128) not null,
147  install_candidate character varying(64) not null, 
148  license_id numeric(2,0),
149  software_category_id character(32), 
150  software_sub_category_id character(32),
151  description character varying(256),
152  main_url character varying(256),
153  source_code_url character varying(256),
154  cli boolean not null,
155  modified_user_id character(32) not null,
156  modified_date timestamp without time zone not null
157);
158alter table only software add constraint pk_software_idx primary key (id);
159alter table only software add constraint uk_software_idk unique (name);
160create index fk_software_1_idx on software using btree(license_id);
161create index fk_software_2_idx on software using btree(software_category_id);
162create index fk_software_3_idx on software using btree(software_sub_category_id);
163create index fk_software_4_idx on software using btree(modified_user_id);
164
165insert into software (id, name, install_candidate, license_id, software_category_id, software_sub_category_id, description, main_url, source_code_url, cli, modified_user_id, modified_date)
166  values ('e3f54fbd00074738a78e674cae1b3504', 'Vim', 'vim', 5, 'acbe188bd43641369046668a4d8994c0', '8d53bad90a874e2897ed619198bcfcc3', 'Vi Improved', 'https://www.vim.org/', 'https://github.com/vim', true, '00000000000000000000000000000000', current_timestamp);
167insert into software (id, name, install_candidate, license_id, software_category_id, software_sub_category_id, description, main_url, source_code_url, cli, modified_user_id, modified_date)
168  values ('a4b184ad87164a478bf60426c5ecef1a', 'Krita', 'krita', 4, '0b143ae523f647b5a4c1db8bea90ae35', 'e6b6ec1306324009abd22197fcbfabf2', 'Krita is a professional FREE and open source painting program. It is made by artists that want to see affordable art tools for everyone.', 'https://krita.org/', 'https://invent.kde.org/graphics/krita', false, '00000000000000000000000000000000', current_timestamp);
169insert into software (id, name, install_candidate, license_id, software_category_id, software_sub_category_id, description, main_url, source_code_url, cli, modified_user_id, modified_date)
170  values ('2843ff91e882465fb2b85c1be051b45f', 'Visual Studio Code', 'vscode', 6, 'acbe188bd43641369046668a4d8994c0', '8d53bad90a874e2897ed619198bcfcc3', 'Visual Studio Code is a code editor redefined and optimized for building and debugging modern web and cloud applications. ', 'https://code.visualstudio.com/', 'https://github.com/microsoft/vscode', false, '00000000000000000000000000000000', current_timestamp);
171
172create table distribution_package_manager_xref (
173  distribution_id character(32) not null,
174  package_manager_id character(32) not null
175);
176alter table only distribution_package_manager_xref add constraint fk_distribution_package_manager_xref_1 foreign key (distribution_id) references distribution(id) on delete cascade deferrable;
177alter table only distribution_package_manager_xref add constraint fk_distribution_package_manager_xref_2 foreign key (package_manager_id) references package_manager(id) on delete cascade deferrable;
178
179