Avoid UUID collision using 30.create-db.sql

Support
#1

What is the proper way of creating objects in 30.create-db.sql and specifically generating UUID for new ones so that later collision won’t appear during Cuba platform’s operation?
Say I want to add something like
INSERT INTO public.sys_config (id, create_ts, created_by, version, update_ts, updated_by, name, value_) VALUES ('c06faf75-3f92-3a32-07d5-3567c9139728', '2018-09-14 18:32:16.088000', 'admin', 1, '2018-09-14 18:32:16.088000', null, 'callbook.initialFTSReindexAllDone', 'true');
What UUID is safe to use next? Yes, I may create most objects via GUI and then copy insert scripts for them. It it the only way? Does this guarantee no collision during further operation?

#2

You can use the newid() function. It is native in Microsoft SQL Server and defined by CUBA init scripts for other databases. For example:

INSERT INTO public.sys_config (id, create_ts, created_by, version, update_ts, updated_by, name, value_)
VALUES (newid(), '2018-09-14 18:32:16.088000', 'admin', 1, '2018-09-14 18:32:16.088000', null, 'callbook.initialFTSReindexAllDone', 'true');
#3

And how do I use the result of certain newid() call?
Say:

insert into SEC_ROLE
(ID, VERSION, CREATE_TS, CREATED_BY, UPDATE_TS, UPDATED_BY, DELETE_TS, DELETED_BY, NAME, LOC_NAME, DESCRIPTION, ROLE_TYPE, IS_DEFAULT_ROLE) 
values ('3ec31528-dc0e-c341-7727-7b46771ae9ff', 3, '2016-11-20 23:11:35', 'u1', '2018-08-22 16:15:12', 'admin', null, null, 'Пользователи', null, null, 0, true);

And then

insert into SEC_PERMISSION 
(ID, VERSION, CREATE_TS, CREATED_BY, UPDATE_TS, UPDATED_BY, DELETE_TS, DELETED_BY, PERMISSION_TYPE, TARGET, VALUE_, ROLE_ID) 
values ('41b120ca-2d15-7198-50d5-f4949ba12bf6', 1, '2018-08-21 17:26:24', 'm1a', '2018-08-21 17:26:24', null, null, null, 20, 'sec$Role:read', 0, '3ec31528-dc0e-c341-7727-7b46771ae9ff');

See that 3ec31528-dc0e-c341-7727-7b46771ae9ff that’d be generated by newid() call must be used in another statement…
(If I used values (newid(), 3, '2016-11-20 23:11:35', 'u1', '2018-08-22 16:15:12', 'admin', null, null, 'Пользователи', null, null, 0, true); )

#4

I see it’s db dependent…
Solved it for postgres 9.0+
sql - How to declare a variable in a PostgreSQL query - Stack Overflow

DO $$
DECLARE userRoleID uuid =newid();
BEGIN
INSERT INTO public.sys_config (id, create_ts, created_by, version, update_ts, updated_by, name, value_) VALUES ('399dde3c-0193-0c15-5fa8-87cfa9117203', '2018-09-14 19:16:19.322000', 'admin', 2, '2018-09-14 19:16:52.439000', 'admin', 'callbook.userRoleID', userRoleID);
insert into SEC_ROLE
(ID, VERSION, CREATE_TS, CREATED_BY, UPDATE_TS, UPDATED_BY, DELETE_TS, DELETED_BY, NAME, LOC_NAME, DESCRIPTION,
 ROLE_TYPE, IS_DEFAULT_ROLE)
values (userRoleID, 3, '2016-11-20 23:11:35', 'u1', '2018-08-22 16:15:12', 'admin', null, null, 'Пользователи', null,
null, 0, true);
--other things using variable go here
END $$;